415 lines
14 KiB
SQL
415 lines
14 KiB
SQL
/* SPDX-License-Identifier: WTFPL */
|
|
|
|
/* MariaDB Scheme
|
|
Version: 1 */
|
|
|
|
/* TODO
|
|
- check all values if unsigned can be used or not
|
|
- implement foreign keys where possible
|
|
- rename provisions to provision(?)
|
|
*/
|
|
|
|
/* User
|
|
userid is signed because nevative values are required for settings
|
|
The user table must not be writeable by the webgui user!
|
|
*/
|
|
|
|
CREATE TABLE user (
|
|
userid smallint(6) NOT NULL AUTO_INCREMENT,
|
|
login varchar(20) NOT NULL,
|
|
pass binary(60) NOT NULL,
|
|
displayname varchar(100) NOT NULL,
|
|
role enum('vessel','captain','helmsman','sailor') NOT NULL DEFAULT 'sailor',
|
|
flags set('deleted','locked'),
|
|
language char(2) NOT NULL DEFAULT 'en',
|
|
PRIMARY KEY (userid),
|
|
UNIQUE INDEX ix_login (login)
|
|
) ENGINE=InnoDB;
|
|
|
|
/* Settings
|
|
E.g. the currently selected boat. Users logged in via the web are allowed
|
|
to write to this table. Therefore a separate table and not additional
|
|
fields in the user data record.
|
|
|
|
userid=0: Global program settings
|
|
sno=0: db_scheme_version
|
|
sno=1: doc_base_path
|
|
|
|
userid = -1: description of user settings for development and admin use
|
|
|
|
userid >=1: setings per user
|
|
sno=1 current vessel
|
|
|
|
*/
|
|
CREATE TABLE settings (
|
|
userid smallint(6) NOT NULL,
|
|
sno smallint(6) NOT NULL,
|
|
valstr varchar(200) DEFAULT NULL,
|
|
valint int(10) DEFAULT NULL,
|
|
PRIMARY KEY (userid, sno)
|
|
) ENGINE=InnoDB;
|
|
|
|
/* ddid=0 is list of lists, not editable
|
|
flags: fixed=not deletable, value not changeable
|
|
locked=not editable
|
|
*/
|
|
CREATE TABLE dropdown (
|
|
ddid tinyint(3) UNSIGNED NOT NULL,
|
|
ddval tinyint(3) NOT NULL,
|
|
sort tinyint(3) DEFAULT NULL,
|
|
ddtext varchar(50) NOT NULL,
|
|
ddshort varchar(4) DEFAULT NULL,
|
|
color char(6) DEFAULT NULL,
|
|
flags enum('locked','fixed') DEFAULT NULL,
|
|
PRIMARY KEY (ddid, ddval)
|
|
) ENGINE=InnoDB;
|
|
|
|
CREATE TABLE company (
|
|
compid smallint(6) NOT NULL AUTO_INCREMENT,
|
|
compname varchar(60) NOT NULL,
|
|
comptype tinyint(3) UNSIGNED NOT NULL DEFAULT 1,
|
|
comptype2 tinyint(3) UNSIGNED DEFAULT NULL,
|
|
shortname varchar(20) DEFAULT NULL,
|
|
street varchar(40),
|
|
zip varchar(10),
|
|
city varchar(30),
|
|
country varchar(30),
|
|
contact varchar(30),
|
|
phone varchar(30),
|
|
email varchar(50),
|
|
web varchar(40),
|
|
customerno varchar(20),
|
|
contractno varchar(20),
|
|
remarks varchar(150) DEFAULT NULL,
|
|
flags set('deleted','historic') DEFAULT NULL,
|
|
PRIMARY KEY (compid),
|
|
UNIQUE INDEX ix_compname (compname, comptype)
|
|
) ENGINE=InnoDB;
|
|
|
|
CREATE TABLE vessel (
|
|
vid smallint(6) NOT NULL AUTO_INCREMENT,
|
|
vtype enum('mono','cat','tri') NOT NULL DEFAULT 'mono',
|
|
vesselname varchar(40) NOT NULL,
|
|
model varchar(20) DEFAULT NULL,
|
|
shipyard smallint(6) DEFAULT NULL,
|
|
loa float(4,2) UNSIGNED DEFAULT NULL,
|
|
lwl float(4,2) UNSIGNED DEFAULT NULL,
|
|
beam float(4,2) UNSIGNED DEFAULT NULL,
|
|
draught float(3,2) UNSIGNED DEFAULT NULL,
|
|
draught_min float(3,2) UNSIGNED DEFAULT NULL,
|
|
displacement smallint(6) UNSIGNED DEFAULT NULL,
|
|
ballast smallint(6) UNSIGNED DEFAULT NULL,
|
|
beltpos_aft float(4,2) UNSIGNED DEFAULT NULL,
|
|
beltpos_bow float(4,2) UNSIGNED DEFAULT NULL,
|
|
shapefile varchar(40) DEFAULT NULL,
|
|
sid_default smallint(6) UNSIGNED DEFAULT NULL, -- default storage
|
|
remarks varchar(150) DEFAULT NULL,
|
|
PRIMARY KEY (vid),
|
|
FOREIGN KEY fk_vessel_shipyard (shipyard)
|
|
REFERENCES company(compid)
|
|
) ENGINE=InnoDB;
|
|
|
|
CREATE TABLE storage (
|
|
sid smallint(6) UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
vid smallint(6) NOT NULL DEFAULT 1,
|
|
sname varchar(40) NOT NULL,
|
|
stype tinyint(3) NOT NULL DEFAULT 0, -- ddid=3
|
|
capacity smallint(6) DEFAULT NULL,
|
|
capaunit enum('kg','l') DEFAULT NULL,
|
|
x smallint(6) UNSIGNED NOT NULL DEFAULT 0,
|
|
y smallint(6) NOT NULL DEFAULT 0,
|
|
z smallint(6) NOT NULL DEFAULT 0,
|
|
wx smallint(6) UNSIGNED NOT NULL DEFAULT 0,
|
|
wy smallint(6) UNSIGNED NOT NULL DEFAULT 0,
|
|
wz smallint(6) UNSIGNED NOT NULL DEFAULT 0,
|
|
angle smallint(4) NOT NULL DEFAULT 0,
|
|
color char(6) DEFAULT NULL,
|
|
remarks varchar(150) DEFAULT NULL,
|
|
PRIMARY KEY (sid)
|
|
) ENGINE=InnoDB;
|
|
|
|
CREATE TABLE tag (
|
|
tagid smallint(6) UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
tagname varchar(20) NOT NULL,
|
|
color char(6) DEFAULT NULL,
|
|
description varchar(80) DEFAULT NULL,
|
|
PRIMARY KEY (tagid),
|
|
UNIQUE INDEX ix_tagname (tagname)
|
|
) ENGINE=InnoDB;
|
|
|
|
CREATE TABLE tagref (
|
|
tagid smallint(6) UNSIGNED NOT NULL,
|
|
objid int(10) UNSIGNED NOT NULL,
|
|
objtype enum('equip','inv','prov','doc', 'proj', 'task', 'maint') NOT NULL,
|
|
PRIMARY KEY (tagid, objid, objtype)
|
|
) ENGINE=InnoDB;
|
|
|
|
/* TODO Boxes can be nested via parent
|
|
TODO boxsize: (LxWxH) in cm? */
|
|
CREATE TABLE box (
|
|
boxid smallint(6) UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
sid smallint(6) UNSIGNED NOT NULL,
|
|
boxtype tinyint(3) DEFAULT NULL,
|
|
parent smallint(6) UNSIGNED DEFAULT NULL,
|
|
label varchar(20) DEFAULT NULL,
|
|
color char(6) DEFAULT NULL,
|
|
content varchar(60) DEFAULT NULL,
|
|
weight float(5,2) UNSIGNED DEFAULT NULL,
|
|
remarks varchar(150) DEFAULT NULL,
|
|
PRIMARY KEY (boxid),
|
|
INDEX ix_label (label)
|
|
) ENGINE=InnoDB;
|
|
|
|
CREATE TABLE equipment (
|
|
eid smallint(6) UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
vid smallint(6) NOT NULL DEFAULT 1,
|
|
ename varchar(80) NOT NULL,
|
|
shortname varchar(20) DEFAULT NULL,
|
|
model varchar(40),
|
|
ecat tinyint(3) DEFAULT NULL,
|
|
serial varchar(30) DEFAULT NULL,
|
|
supplier smallint(6) DEFAULT NULL,
|
|
manufacturer smallint(6) DEFAULT NULL, -- -1: unknown; -3: own built
|
|
purchdate date DEFAULT NULL,
|
|
price decimal(12,2) DEFAULT NULL,
|
|
weight float(5,2) UNSIGNED DEFAULT NULL,
|
|
remarks varchar(150) DEFAULT NULL,
|
|
flags set('ordered','removed','deleted') DEFAULT NULL,
|
|
PRIMARY KEY(eid)
|
|
) ENGINE=InnoDB;
|
|
|
|
/*
|
|
convert to
|
|
sid SMALLINT UNSIGNED NULL,
|
|
boxid SMALLINT UNSIGNED NULL,
|
|
CHECK (
|
|
(sid IS NOT NULL AND boxid IS NULL)
|
|
OR
|
|
(sid IS NULL AND boxid IS NOT NULL)
|
|
)
|
|
*/
|
|
CREATE TABLE inventory (
|
|
invid int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
invname varchar(80) NOT NULL,
|
|
conttype enum('storage','box') NOT NULL DEFAULT 'storage',
|
|
sid smallint(6) UNSIGNED NOT NULL DEFAULT 1,
|
|
boxid smallint(6) UNSIGNED NOT NULL DEFAULT 1,
|
|
eid smallint(6) UNSIGNED DEFAULT NULL,
|
|
number smallint(6) UNSIGNED NOT NULL DEFAULT 1,
|
|
weight float(5,2) UNSIGNED DEFAULT NULL,
|
|
price decimal(12,2) DEFAULT NULL,
|
|
purchdate date DEFAULT NULL,
|
|
invcond enum('unknown','excellent','good','fair','bad','repairable','defect') NOT NULL default 'unknown',
|
|
remarks varchar(150) DEFAULT NULL,
|
|
flags set('ordered','removed','deleted') DEFAULT NULL,
|
|
PRIMARY KEY (invid),
|
|
INDEX ix_invname (invname)
|
|
) ENGINE=InnoDB;
|
|
|
|
CREATE TABLE cable (
|
|
cableid smallint(6) UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
vid smallint(6) NOT NULL DEFAULT 1,
|
|
cablename varchar(80) NOT NULL,
|
|
cabletype varchar(40) DEFAULT NULL, -- e.g. H07RN-F
|
|
length float(5,2) UNSIGNED NOT NULL DEFAULT 0, -- total length in m
|
|
conductors TINYINT(3) UNSIGNED NOT NULL DEFAULT 1,
|
|
diameter float(5,2) UNSIGNED DEFAULT NULL, -- outer diameter in mm
|
|
xsection float(5,2) UNSIGNED DEFAULT NULL, -- cross section in mm²
|
|
weight float(5,2) UNSIGNED DEFAULT NULL, -- kg/meter
|
|
color char(6) DEFAULT NULL,
|
|
supplier smallint(6) DEFAULT NULL,
|
|
manufacturer smallint(6) DEFAULT NULL,
|
|
cablecond enum('unknown','new','excellent','good','fair','bad','repairable','defect') NOT NULL default 'unknown',
|
|
remarks varchar(150) DEFAULT NULL,
|
|
PRIMARY KEY (cableid),
|
|
INDEX ix_cablename (vid, cablename)
|
|
) ENGINE=InnoDB;
|
|
|
|
CREATE table fuse (
|
|
fuseid smallint(6) UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
vid smallint(6) NOT NULL DEFAULT 1,
|
|
fnumber smallint(6) NOT NULL,
|
|
ftype tinyint(3) DEFAULT 0, -- from dropdown id 7
|
|
current float(5,2) UNSIGNED NOT NULL, -- in A
|
|
cableid smallint(6) UNSIGNED DEFAULT NULL,
|
|
eid smallint(6) UNSIGNED DEFAULT NULL,
|
|
location tinyint(3) DEFAULT 0, -- from dropdown id 10
|
|
description varchar(60) DEFAULT NULL,
|
|
remarks varchar(150) DEFAULT NULL,
|
|
PRIMARY KEY (fuseid),
|
|
UNIQUE INDEX ix_fusenumber (vid, fnumber)
|
|
) ENGINE=InnoDB;
|
|
|
|
-- change vals to decimal(12,4)?
|
|
CREATE TABLE measurement (
|
|
mid smallint(6) NOT NULL AUTO_INCREMENT,
|
|
mname varchar(80) NOT NULL,
|
|
vid smallint(6) NOT NULL DEFAULT 1,
|
|
nval tinyint(1) UNSIGNED NOT NULL DEFAULT 1,
|
|
val1 int(10) NOT NULL,
|
|
val2 int(10) DEFAULT NULL,
|
|
val3 int(10) DEFAULT NULL,
|
|
unit tinyint(3) NOT NULL DEFAULT 1,
|
|
accuracy enum('unknown','precise','normal','rough','estimated') NOT NULL DEFAULT 'unknown',
|
|
mdate date DEFAULT NULL,
|
|
note varchar(80) DEFAULT NULL,
|
|
eid smallint(6) UNSIGNED DEFAULT NULL,
|
|
PRIMARY KEY (mid)
|
|
) ENGINE=InnoDB;
|
|
|
|
CREATE TABLE provisions (
|
|
provid int(10) NOT NULL AUTO_INCREMENT,
|
|
provname varchar(40) NOT NULL,
|
|
provcat tinyint(3) DEFAULT NULL,
|
|
sid smallint(6) UNSIGNED DEFAULT NULL,
|
|
boxid smallint(6) UNSIGNED NOT NULL DEFAULT 1,
|
|
number smallint(6) UNSIGNED NOT NULL DEFAULT 1,
|
|
target smallint(6) UNSIGNED DEFAULT NULL, -- for shopping list
|
|
weight float(5,2) UNSIGNED DEFAULT NULL,
|
|
weight_net float(5,2) UNSIGNED DEFAULT NULL,
|
|
unit tinyint(3) DEFAULT NULL,
|
|
calories smallint(6) UNSIGNED DEFAULT NULL,
|
|
storedate date DEFAULT CURRENT_DATE,
|
|
shelflife date DEFAULT NULL,
|
|
price decimal(12,2) DEFAULT NULL,
|
|
remarks varchar(150) DEFAULT NULL,
|
|
PRIMARY KEY (provid)
|
|
) ENGINE=InnoDB;
|
|
|
|
/* TODO
|
|
- distinction between
|
|
docdate - e.g. invoicedate
|
|
doctime -> uploadtime
|
|
*/
|
|
CREATE TABLE document (
|
|
docid smallint(6) NOT NULL AUTO_INCREMENT,
|
|
doctype enum('generic','manual','invoice','drawing','picture') NOT NULL DEFAULT 'generic',
|
|
mimetype varchar(80) NOT NULL DEFAULT 'application/octet-stream',
|
|
filename varchar(200) NOT NULL,
|
|
extension char(3) NOT NULL,
|
|
hash varchar(32) NOT NULL,
|
|
docsize int(10) unsigned NOT NULL,
|
|
doctime datetime DEFAULT NULL,
|
|
docdate date DEFAULT NULL, -- e.g. invoice date
|
|
title varchar(40) DEFAULT NULL,
|
|
pages smallint(6) unsigned NOT NULL DEFAULT 1,
|
|
remarks varchar(150) DEFAULT NULL,
|
|
roleaccess enum('captain','helmsman','sailor','unauth') NOT NULL DEFAULT 'captain',
|
|
PRIMARY KEY (docid),
|
|
UNIQUE INDEX ix_hash (hash)
|
|
) ENGINE=InnoDB;
|
|
|
|
CREATE TABLE docref (
|
|
drid int(10) NOT NULL AUTO_INCREMENT,
|
|
reftype enum('vessel','box','equipment','project','task','user','company') NOT NULL DEFAULT 'vessel',
|
|
docid smallint(6) NOT NULL,
|
|
refid int(10) NOT NULL,
|
|
PRIMARY KEY (drid),
|
|
UNIQUE INDEX ix_docref (docid, refid, reftype),
|
|
FOREIGN KEY fk_docref_doc (docid)
|
|
REFERENCES document (docid)
|
|
ON DELETE RESTRICT
|
|
ON UPDATE CASCADE
|
|
) ENGINE=InnoDB;
|
|
|
|
/* TODO optional fields: plan date, target date, completion date
|
|
interval_hours, last_hours
|
|
signalk: operating hours current vs maint every n operating hours in separate
|
|
*/
|
|
CREATE TABLE maintenance (
|
|
maintid int(10) NOT NULL AUTO_INCREMENT,
|
|
vid smallint(6) NOT NULL DEFAULT 1,
|
|
eid smallint(6) UNSIGNED DEFAULT NULL,
|
|
series smallint(6) DEFAULT NULL,
|
|
activities varchar(40) NOT NULL,
|
|
remarks varchar(150) DEFAULT NULL,
|
|
maintstate enum('new','planned','ongoing','done') NOT NULL DEFAULT 'new',
|
|
PRIMARY KEY (maintid)
|
|
) ENGINE=InnoDB;
|
|
|
|
/* TODO? time recording */
|
|
|
|
CREATE TABLE project (
|
|
projid smallint(6) NOT NULL AUTO_INCREMENT,
|
|
projname varchar(40) NOT NULL,
|
|
vid smallint(6) NOT NULL DEFAULT 1,
|
|
responsible smallint(6) NOT NULL,
|
|
startdate date DEFAULT NULL,
|
|
duration smallint(6) UNSIGNED DEFAULT NULL,
|
|
costs_plan decimal(12,2) DEFAULT NULL,
|
|
costs_final decimal(12,2) DEFAULT NULL,
|
|
remarks varchar(150) DEFAULT NULL,
|
|
projstate enum('new','plan','ongoing','paused','finished') NOT NULL DEFAULT 'new',
|
|
PRIMARY KEY (projid)
|
|
) ENGINE=InnoDB;
|
|
|
|
CREATE TABLE task (
|
|
taskid int(10) NOT NULL AUTO_INCREMENT,
|
|
vid smallint(6) DEFAULT NULL,
|
|
projid smallint(6) DEFAULT NULL,
|
|
taskname varchar(60) NOT NULL,
|
|
priority enum('high','medium','low') DEFAULT NULL,
|
|
taskstate enum('pending','active','waiting','deleted','completed') NOT NULL DEFAULT 'pending',
|
|
state_ts timestamp DEFAULT NULL,
|
|
plandate date DEFAULT NULL,
|
|
duedate date DEFAULT NULL,
|
|
started datetime DEFAULT NULL,
|
|
finished datetime DEFAULT NULL,
|
|
responsible smallint(6) NOT NULL,
|
|
/* TODO executed_by : company or user? or both? */
|
|
PRIMARY KEY (taskid),
|
|
FOREIGN KEY fk_task_user (responsible)
|
|
REFERENCES user(userid)
|
|
) ENGINE=InnoDB;
|
|
|
|
/* Notes can be assigned to tasks,maintenances and equipment */
|
|
CREATE TABLE note (
|
|
noteid int(10) NOT NULL AUTO_INCREMENT,
|
|
notetype enum('task','maint','equip') NOT NULL DEFAULT 'task',
|
|
refid int(10) NOT NULL,
|
|
annotation text NOT NULL,
|
|
PRIMARY KEY (noteid)
|
|
) ENGINE=InnoDB;
|
|
|
|
CREATE TABLE checklist (
|
|
clid smallint(6) NOT NULL AUTO_INCREMENT,
|
|
title varchar(30) NOT NULL,
|
|
parent smallint(6) DEFAULT NULL,
|
|
clstate enum('new','open','closed') NOT NULL DEFAULT 'new',
|
|
PRIMARY KEY (clid)
|
|
) ENGINE=InnoDB;
|
|
|
|
CREATE TABLE checkitem (
|
|
checkid int(10) NOT NULL AUTO_INCREMENT,
|
|
checkname varchar(60) NOT NULL,
|
|
groupid tinyint(3) DEFAULT NULL,
|
|
checkresult enum('unchecked','checked','unused') NOT NULL DEFAULT 'unused',
|
|
PRIMARY KEY (checkid)
|
|
) ENGINE=InnoDB;
|
|
|
|
CREATE TABLE checkref (
|
|
clid smallint(6) NOT NULL,
|
|
checkid int(10) NOT NULL,
|
|
sort smallint(6) DEFAULT NULL,
|
|
PRIMARY KEY (clid, checkid)
|
|
) ENGINE=InnoDB;
|
|
|
|
CREATE TABLE checkgroup (
|
|
groupid int(10) NOT NULL,
|
|
title varchar(30) NOT NULL,
|
|
PRIMARY KEY (groupid)
|
|
) ENGINE=InnoDB;
|
|
|
|
/* Expansion for future use: signalk mapping (just an idea) */
|
|
CREATE TABLE signalk (
|
|
skid int(10) NOT NULL AUTO_INCREMENT,
|
|
vid smallint(6) NOT NULL,
|
|
objtype enum('equipment','storage') NOT NULL,
|
|
objid int(10) NOT NULL,
|
|
datatype enum('engine_hours','tank_level') NOT NULL,
|
|
skpath varchar(255) NOT NULL,
|
|
PRIMARY KEY (skid),
|
|
UNIQUE KEY ix_signalk
|
|
(vid, objtype, objid, datatype)
|
|
) ENGINE=InnoDB;
|