diff options
author | Norbert Preining <norbert@preining.info> | 2019-09-02 13:46:59 +0900 |
---|---|---|
committer | Norbert Preining <norbert@preining.info> | 2019-09-02 13:46:59 +0900 |
commit | e0c6872cf40896c7be36b11dcc744620f10adf1d (patch) | |
tree | 60335e10d2f4354b0674ec22d7b53f0f8abee672 /support/dktools/dktools/itadmin |
Initial commit
Diffstat (limited to 'support/dktools/dktools/itadmin')
-rw-r--r-- | support/dktools/dktools/itadmin/it-de.sql | 314 | ||||
-rw-r--r-- | support/dktools/dktools/itadmin/it-latin1.sql | 312 | ||||
-rw-r--r-- | support/dktools/dktools/itadmin/it-utf8.sql | 312 |
3 files changed, 938 insertions, 0 deletions
diff --git a/support/dktools/dktools/itadmin/it-de.sql b/support/dktools/dktools/itadmin/it-de.sql new file mode 100644 index 0000000000..ab58d7e4da --- /dev/null +++ b/support/dktools/dktools/itadmin/it-de.sql @@ -0,0 +1,314 @@ + +-- The database is named it + +create database it; + +use it; + +-- VLANs + +CREATE TABLE vlans ( + vl_s char(8) NOT NULL default '', -- short VLAN type, i.e. 500 + vl_l char(64) default 'mein Netzwerk', -- long type + vl_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + PRIMARY KEY (vl_s) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +CREATE TABLE networks( + nw_ip char(18) NOT NULL default '', -- IP + nw_ma char(18) default '', -- Maske + nw_gw char(18) default '', -- Gateway + nw_bc char(18) default '', -- Broadcast-Adresse + vl_s char(8) default '', -- VLAN + nw_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + CONSTRAINT cf FOREIGN KEY(vl_s) REFERENCES vlans(vl_s) ON UPDATE CASCADE ON DELETE RESTRICT, + PRIMARY KEY(nw_ip), + INDEX(vl_s) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +-- Datendosen-Speed + +CREATE TABLE ddspeed ( + sp_s char(12) NOT NULL default '', -- Kurze Bezeichnung + sp_l char(64) default NULL, -- ausfuerhliche Bezeichnung + sp_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + PRIMARY KEY(sp_s) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +INSERT INTO ddspeed (sp_s, sp_l ) VALUES +('auto', 'Autonegotiation'), +('10', '10 MBit/s halbduplex'), +('100', '100 MBit/s halbduplex'), +('10fd', '10 MBit/s vollduplex'), +('100fd', '100 MBit/s vollduplex'); + +-- Gebaeude + +CREATE TABLE buildings ( + gb_s char(4) NOT NULL default '', -- Name kurz + gb_l char(64) default NULL, -- Name komplett + gb_a1 char(64) default NULL, -- Adresszeile 1 + gb_a2 char(64) default NULL, -- Adresszeile 2 + gb_a3 char(64) default NULL, -- Adresszeile 3 + gb_a4 char(64) default NULL, -- Adresszeile 4 + gb_plz char(32) default '12345', -- PLZ + gb_ort char(64) default 'Irgendwo', -- Ort + gb_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + PRIMARY KEY(gb_s) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +-- Datendosen + +CREATE TABLE patches ( + dd_n char(16) NOT NULL default '', -- Kurze Bezeichnung der Dose, Aufdruck + sp_s char(12) default 'auto', -- Speed + vl_s char(8) default '-', -- VLAN type + dd_p char(16) default NULL, -- Port am Hub + gb_s char(4) default '', -- Haus + dd_r char(8) default NULL, -- Raumnummer + dd_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + CONSTRAINT c2 FOREIGN KEY (sp_s) REFERENCES ddspeed(sp_s) ON UPDATE CASCADE ON DELETE RESTRICT, + CONSTRAINT c3 FOREIGN KEY (vl_s) REFERENCES vlans(vl_s) ON UPDATE CASCADE ON DELETE RESTRICT, + CONSTRAINT c4 FOREIGN KEY (gb_s) REFERENCES buildings(gb_s) ON UPDATE CASCADE ON DELETE RESTRICT, + PRIMARY KEY(dd_n), + INDEX(sp_s), + INDEX(vl_s), + INDEX(gb_s) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + + +-- Personen (Systemverantwortliche, Lizenzinhaber...) + +CREATE TABLE users ( + us_s char(12) NOT NULL default '-', -- Loginname + us_t char(32) default NULL, -- Titel + us_sn char(64) default '', -- Vorname + us_fn char(64) default '', -- Familienname + us_em char(64) default NULL, -- E-Mail-Adresse + us_ko char(16) default NULL, -- Kostenstelle + us_se char(16) default NULL, -- SE 1... + us_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + PRIMARY KEY(us_s) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +-- Netgroups + +CREATE TABLE netgroups ( + ng_s char(12) NOT NULL default '-', -- Netgroup Name + ng_l char(64) default '', -- Beschreibung + ng_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + PRIMARY KEY(ng_s) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +-- Beziehungen zwischen Netgroups + +CREATE TABLE ngdeps ( + nd_pk int(10) unsigned NOT NULL auto_increment, + nd_p char(12) default NULL, -- Elterngruppe + nd_c char(12) default NULL, -- Kind (in Eltern enthalten) + nd_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + CONSTRAINT c5 FOREIGN KEY (nd_p) REFERENCES netgroups(ng_s) ON UPDATE CASCADE ON DELETE RESTRICT, + CONSTRAINT c6 FOREIGN KEY (nd_c) REFERENCES netgroups(ng_s) ON UPDATE CASCADE ON DELETE RESTRICT, + PRIMARY KEY(nd_pk), + INDEX(nd_p), + INDEX(nd_c) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +-- DHCP-Klassen + +CREATE TABLE dhcpclasses ( + dc_s char(16) NOT NULL default '-', -- DHCP class name + dc_d char(64) default NULL, -- description + nw_ip char(18) NOT NULL default '0.0.0.0', -- IP address range for class + dc_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + CONSTRAINT cm FOREIGN KEY (nw_ip) REFERENCES networks(nw_ip) ON UPDATE CASCADE ON DELETE RESTRICT; + PRIMARY KEY(dc_s), + INDEX(nw_ip) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +-- DHCP-Pools + +CREATE TABLE dhcppools( + dp_pk int(10) unsigned NOT NULL auto_increment, -- primary key + dp_st char(18) default NULL, -- start address + dp_en char(18) default NULL, -- end address + nw_ip char(18) default NULL, -- network + dp_al integer default 0, -- allow unknown clients + dp_dn integer default 1, -- deny unknown clients + dc_s char(16) default NULL, -- allowed DHCP class + dp_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + CONSTRAINT ci FOREIGN KEY(nw_ip) REFERENCES networks(nw_ip) ON UPDATE CASCADE ON DELETE RESTRICT, + CONSTRAINT cj FOREIGN KEY(dc_s) REFERENCES dhcpclasses(dc_s) ON UPDATE CASCADE ON DELETE RESTRICT, + PRIMARY KEY(dp_pk), + INDEX(nw_ip) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +-- DHCP-Groups +CREATE TABLE dhcpgroups( + dg_s char(16) NOT NULL, -- Group name, primary key + dg_l char(64) default NULL, -- description + dg_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + PRIMARY KEY(dg_s) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +-- DHCP-Options +CREATE TABLE dhcpoptions( + do_pk int(10) unsigned NOT NULL auto_increment, -- primary key + do_sc ENUM('server', 'vlan', 'network', 'group', 'pool', 'host') default 'server', + do_sn char(32) default NULL, -- scope name + do_n char(64) NOT NULL, -- option name + do_v char(128) NOT NULL, -- option value + do_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + PRIMARY KEY(do_pk), + INDEX(do_sc), + INDEX(do_sn), + INDEX(do_n) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +-- Hosts + +CREATE TABLE computers ( + co_s char(16) NOT NULL default 'e__', -- Computername + us_s char(12) default '-', -- Systemverantwortlicher + co_ip char(18) default '192.168.101.1', -- IP-Adresse + co_mc char(18) default NULL, -- MAC-Adresse + ng_s char(12) default NULL, -- Netzgruppe + co_co char(64) default NULL, -- Kommentar + dc_s char(16) default NULL, -- DHCP-Klasse + dg_s char(16) default NULL, -- DHCP-Gruppe + dd_n char(16) default NULL, -- Datendose + gb_s char(4) default 'B', -- Gebaeude + co_r char(8) default '', -- Raumnummer + co_in char(12) default '0700000', -- Inventar-Nummer + co_sn char(64) default NULL, -- Seriennummer + co_dd char(64) default NULL, -- DNS Domain + co_ff integer default 0, -- full name first + co_gu integer default 0, -- guest PC + co_hi char(32) default NULL, -- Host-ID + co_ex DATE default NULL, -- Ablaufdatum Studenten-Laptops + co_nn integer default 0, -- skip when creating network docu + co_na integer default 0, -- skip in arpwatch + co_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + CONSTRAINT c7 FOREIGN KEY (us_s) REFERENCES users(us_s) ON UPDATE CASCADE ON DELETE RESTRICT, + CONSTRAINT c8 FOREIGN KEY (ng_s) REFERENCES netgroups(ng_s) ON UPDATE CASCADE ON DELETE RESTRICT, + CONSTRAINT cg FOREIGN KEY (gb_s) REFERENCES buildings(gb_s) ON UPDATE CASCADE ON DELETE RESTRICT, + CONSTRAINT ch FOREIGN KEY (dc_s) REFERENCES dhcpclasses(dc_s) ON UPDATE CASCADE ON DELETE RESTRICT, + CONSTRAINT ck FOREIGN KEY (dg_s) REFERENCES dhcpgroups(dg_s) ON UPDATE CASCADE ON DELETE RESTRICT, + CONSTRAINT cm FOREIGN KEY (dd_n) REFERENCES patches(dd_n) ON UPDATE CASCADE ON DELETE RESTRICT, + PRIMARY KEY (co_s), + INDEX(us_s), + INDEX(co_ip), + INDEX(ng_s), + INDEX(dc_s), + INDEX(dg_s), + INDEX(dd_n), + INDEX(gb_s), + INDEX(co_r), + INDEX(co_in), + INDEX(co_sn) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +-- Aliases + +CREATE TABLE aliases ( + al_s char(80) NOT NULL, -- the alias + co_s char(16) default NULL, -- alias target + al_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + CONSTRAINT co FOREIGN KEY(co_s) REFERENCES computers(co_s) ON UPDATE CASCADE ON DELETE RESTRICT, + PRIMARY KEY(al_s), + INDEX(co_s) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +-- Software-Hersteller + +CREATE TABLE swmanufacturers ( + sm_s char(16) NOT NULL, -- Hersteller kurz + sm_l char(64) default '', -- Hersteller lang + sm_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + PRIMARY KEY(sm_s) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +-- Software-Produkte + +CREATE TABLE swproducts ( + sw_s char(16) NOT NULL, -- Produkt, kurz + sw_l char(64) default '', -- Produkt lang + sm_s char(16) default '', -- Hersteller kurz + sw_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + CONSTRAINT c9 FOREIGN KEY (sm_s) REFERENCES swmanufacturers(sm_s) ON UPDATE CASCADE ON DELETE RESTRICT, + PRIMARY KEY(sw_s), + INDEX(sm_s) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +-- Software-Verkaeufer + +CREATE TABLE swresellers ( + sr_s char(16) NOT NULL, -- Verkaeufer kurz + sr_l char(64) default '', -- Voller Name + sr_a1 char(64) default NULL, -- Addresszeile 1 + sr_a2 char(64) default NULL, -- Addresszeile 2 + sr_a3 char(64) default NULL, -- Addresszeile 3 + sr_a4 char(64) default NULL, -- Addresszeile 4 + sr_plz char(8) default NULL, -- PLZ + sr_ort char(64) default NULL, -- Ort + sr_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + PRIMARY KEY(sr_s) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +-- Lizenztypen + +CREATE TABLE licensetypes ( + lt_s char(16) NOT NULL, -- Kurzbezeichnung + lt_l char(64) default '', -- vollstaendige Bezeichnung + lt_i int default 1, -- Anzahl der Plaetze + lt_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + PRIMARY KEY(lt_s), + INDEX(lt_l) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +INSERT INTO licensetype(lt_s, lt_l, lt_i) VALUES +('e', 'Einzelplatzlizenz (1 Nutzer auf 1 Computer)', 1), +('c', 'Concurrent-Lizenz für 1 Nutzer', 1), +('flexlm' 'Flexlm-Client-Installation', 1), +('gpl2', 'GNU General Public License, Version 2', 1), +('lgpl2', 'GNU Lesser General Public License, Version 2', 1), +('gpl3', 'GNU General Public License, Version 3', 1), +('lgpl3', 'GNU Lesser General Public License, Version 3', 1), +('bsd', 'BSD-Lizenz', 1), +('flexlm-s5', 'Flexlm-Lizenz-Server, 5 Nutzer', 5); + +-- Nachweis ueber erworbene und eingesetzte Lizenzen + +CREATE TABLE licenses ( + li_pk int(10) unsigned NOT NULL auto_increment, + li_on char(24) default NULL, -- Auftragsnummer der Bestellung + sw_s char(16) default NULL, -- Software-Produkt + us_s char(12) default NULL, -- User Login Name + co_s char(16) default NULL, -- Computer, auf dem eingesetzt + lt_s char(16) default 's', -- Lizenztyp + li_no char(64) default NULL, -- Notes (Bemerkungen) + sr_s char(16) default NULL, -- Verkaeufer + li_dd char(16) default NULL, -- Datum Lieferschein + li_nd char(16) default NULL, -- Lieferschein-Nr + li_di char(16) default NULL, -- Rechnungsdatum + li_ni char(16) default NULL, -- Rechnungsnummer + li_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + CONSTRAINT ca FOREIGN KEY (sw_s) REFERENCES swproducts(sw_s) ON UPDATE CASCADE ON DELETE RESTRICT, + CONSTRAINT cb FOREIGN KEY (sr_s) REFERENCES swresellers(sr_s) ON UPDATE CASCADE ON DELETE RESTRICT, + CONSTRAINT cc FOREIGN KEY (lt_s) REFERENCES licensetypes(lt_s) ON UPDATE CASCADE ON DELETE RESTRICT, + CONSTRAINT cd FOREIGN KEY (us_s) REFERENCES users(us_s) ON UPDATE CASCADE ON DELETE RESTRICT, + CONSTRAINT ce FOREIGN KEY (co_s) REFERENCES computers(co_s) ON UPDATE CASCADE ON DELETE RESTRICT, + PRIMARY KEY(li_pk), + INDEX(li_on), + INDEX(sw_s), + INDEX(us_s), + INDEX(co_s), + INDEX(lt_s), + INDEX(sr_s), + INDEX(li_dd), + INDEX(li_nd), + INDEX(li_di), + INDEX(li_ni) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + + diff --git a/support/dktools/dktools/itadmin/it-latin1.sql b/support/dktools/dktools/itadmin/it-latin1.sql new file mode 100644 index 0000000000..039e3eeac9 --- /dev/null +++ b/support/dktools/dktools/itadmin/it-latin1.sql @@ -0,0 +1,312 @@ + +-- The database is named it + +create database it; + +use it; + +-- VLANs + +CREATE TABLE vlans ( + vl_s char(8) NOT NULL default '', -- short VLAN type, i.e. 500 + vl_l char(64) default 'my network', -- long type, description + vl_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + PRIMARY KEY (vl_s) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +CREATE TABLE networks( + nw_ip char(18) NOT NULL default '', -- IP + nw_ma char(18) default '', -- network mask + nw_gw char(18) default '', -- default gateway + nw_bc char(18) default '', -- broadcast address + vl_s char(8) default '', -- VLAN + nw_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + PRIMARY KEY(nw_ip), + INDEX(vl_s), + CONSTRAINT cf FOREIGN KEY(vl_s) REFERENCES vlans(vl_s) ON UPDATE CASCADE ON DELETE RESTRICT +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +-- network connection speed setup + +CREATE TABLE ddspeed ( + sp_s char(12) NOT NULL default '', -- short name (abbreviation) + sp_l char(64) default NULL, -- long name, description + sp_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + PRIMARY KEY(sp_s) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +INSERT INTO ddspeed (sp_s, sp_l ) VALUES +('auto', 'Autonegotiation'), +('10', '10 MBit/s half-duplex'), +('100', '100 MBit/s half-duplex'), +('10fd', '10 MBit/s full-duplex'), +('100fd', '100 MBit/s full-duplex'); + +-- buildings + +CREATE TABLE buildings ( + gb_s char(4) NOT NULL default '', -- short name (abbreviation) + gb_l char(64) default NULL, -- complete name + gb_a1 char(64) default NULL, -- address line 1 + gb_a2 char(64) default NULL, -- address line 2 + gb_a3 char(64) default NULL, -- address line 3 + gb_a4 char(64) default NULL, -- address line 4 + gb_plz char(32) default '12345', -- ZIP code + gb_ort char(64) default 'Somewhere', -- location (town or city name) + gb_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + PRIMARY KEY(gb_s) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +-- network connections + +CREATE TABLE patches ( + dd_n char(16) NOT NULL default '', -- short name, label on socket + sp_s char(12) default 'auto', -- speed setup + vl_s char(8) default '-', -- VLAN + dd_p char(16) default NULL, -- hub port used for socket + gb_s char(4) default '', -- building + dd_r char(8) default NULL, -- room + dd_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + PRIMARY KEY(dd_n), + INDEX(sp_s), + INDEX(vl_s), + INDEX(gb_s), + CONSTRAINT c2 FOREIGN KEY (sp_s) REFERENCES ddspeed(sp_s) ON UPDATE CASCADE ON DELETE RESTRICT, + CONSTRAINT c3 FOREIGN KEY (vl_s) REFERENCES vlans(vl_s) ON UPDATE CASCADE ON DELETE RESTRICT, + CONSTRAINT c4 FOREIGN KEY (gb_s) REFERENCES buildings(gb_s) ON UPDATE CASCADE ON DELETE RESTRICT +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + + +-- persons (computer owners, license owners) + +CREATE TABLE users ( + us_s char(12) NOT NULL default '-', -- short name, i.e. login name + us_t char(32) default NULL, -- title + us_sn char(64) default '', -- surname + us_fn char(64) default '', -- name + us_em char(64) default NULL, -- e-mail address + us_ko char(16) default NULL, -- department (costs controlling ID) + us_se char(16) default NULL, -- staff ID number + us_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + PRIMARY KEY(us_s) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +-- netgroups + +CREATE TABLE netgroups ( + ng_s char(12) NOT NULL default '-', -- name + ng_l char(64) default '', -- description + ng_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + PRIMARY KEY(ng_s) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +-- netgroup relations + +CREATE TABLE ngdeps ( + nd_pk int(10) unsigned NOT NULL auto_increment, + nd_p char(12) default NULL, -- parent group + nd_c char(12) default NULL, -- child group + nd_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + PRIMARY KEY(nd_pk), + INDEX(nd_p), + INDEX(nd_c), + CONSTRAINT c5 FOREIGN KEY (nd_p) REFERENCES netgroups(ng_s) ON UPDATE CASCADE ON DELETE RESTRICT, + CONSTRAINT c6 FOREIGN KEY (nd_c) REFERENCES netgroups(ng_s) ON UPDATE CASCADE ON DELETE RESTRICT +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +-- DHCP classes + +CREATE TABLE dhcpclasses ( + dc_s char(16) NOT NULL default '-', -- DHCP class name + dc_d char(64) default NULL, -- description + nw_ip char(18) NOT NULL default '0.0.0.0', -- IP network for class + dc_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + PRIMARY KEY(dc_s), + INDEX(nw_ip), + CONSTRAINT cm FOREIGN KEY (nw_ip) REFERENCES networks(nw_ip) ON UPDATE CASCADE ON DELETE RESTRICT +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +-- DHCP pools + +CREATE TABLE dhcppools( + dp_pk int(10) unsigned NOT NULL auto_increment, -- primary key + dp_st char(18) default NULL, -- start address + dp_en char(18) default NULL, -- end address + nw_ip char(18) default NULL, -- network + dp_al integer default 0, -- allow unknown clients + dp_dn integer default 1, -- deny unknown clients + dc_s char(16) default NULL, -- allowed DHCP class + dp_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + PRIMARY KEY(dp_pk), + INDEX(nw_ip), + CONSTRAINT ci FOREIGN KEY(nw_ip) REFERENCES networks(nw_ip) ON UPDATE CASCADE ON DELETE RESTRICT, + CONSTRAINT cj FOREIGN KEY(dc_s) REFERENCES dhcpclasses(dc_s) ON UPDATE CASCADE ON DELETE RESTRICT +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +-- DHCP groups +CREATE TABLE dhcpgroups( + dg_s char(16) NOT NULL, -- group name, primary key + dg_l char(64) default NULL, -- description + dg_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + PRIMARY KEY(dg_s) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +-- DHCP options +CREATE TABLE dhcpoptions( + do_pk int(10) unsigned NOT NULL auto_increment, -- primary key + do_sc ENUM('server', 'vlan', 'network', 'group', 'pool', 'host') default 'server', -- scope type + do_sn char(32) default NULL, -- scope name + do_n char(64) NOT NULL, -- option name + do_v char(128) NOT NULL, -- option value + do_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + PRIMARY KEY(do_pk), + INDEX(do_sc), + INDEX(do_sn), + INDEX(do_n) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +-- hosts + +CREATE TABLE computers ( + co_s char(16) NOT NULL default 'e__', -- hostname + us_s char(12) default NULL, -- system owner + co_ip char(18) default NULL, -- IP address + co_mc char(18) default NULL, -- MAC address + ng_s char(12) default NULL, -- netgroup + co_co char(64) default NULL, -- comment, description + dc_s char(16) default NULL, -- DHCP class + dg_s char(16) default NULL, -- DHCP group + dd_n char(16) default NULL, -- network patch socket + gb_s char(4) default NULL, -- building + co_r char(8) default NULL, -- room + co_in char(12) default NULL, -- inventory number + co_sn char(64) default NULL, -- serial number + co_dd char(64) default NULL, -- DNS domain + co_ff integer default 0, -- full name first flag + co_gu integer default 0, -- guest PC flag + co_hi char(32) default NULL, -- host ID + co_ex DATE default NULL, -- expiration date (for guest PCs) + co_nn integer default 0, -- skip host when creating network docu + co_na integer default 0, -- skip in arpwatch + co_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + PRIMARY KEY (co_s), + INDEX(us_s), + INDEX(co_ip), + INDEX(ng_s), + INDEX(dc_s), + INDEX(dg_s), + INDEX(dd_n), + INDEX(gb_s), + INDEX(co_r), + INDEX(co_in), + INDEX(co_sn), + CONSTRAINT c7 FOREIGN KEY (us_s) REFERENCES users(us_s) ON UPDATE CASCADE ON DELETE RESTRICT, + CONSTRAINT c8 FOREIGN KEY (ng_s) REFERENCES netgroups(ng_s) ON UPDATE CASCADE ON DELETE RESTRICT, + CONSTRAINT cg FOREIGN KEY (gb_s) REFERENCES buildings(gb_s) ON UPDATE CASCADE ON DELETE RESTRICT, + CONSTRAINT ch FOREIGN KEY (dc_s) REFERENCES dhcpclasses(dc_s) ON UPDATE CASCADE ON DELETE RESTRICT, + CONSTRAINT ck FOREIGN KEY (dg_s) REFERENCES dhcpgroups(dg_s) ON UPDATE CASCADE ON DELETE RESTRICT, + CONSTRAINT cn FOREIGN KEY (dd_n) REFERENCES patches(dd_n) ON UPDATE CASCADE ON DELETE RESTRICT +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +CREATE TABLE aliases ( + al_s char(80) NOT NULL, -- the alias + co_s char(16) default NULL, -- alias target + co_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + PRIMARY KEY(al_s), + INDEX(co_s), + CONSTRAINT co FOREIGN KEY(co_s) REFERENCES computers(co_s) ON UPDATE CASCADE ON DELETE RESTRICT +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +-- software manufacturers + +CREATE TABLE swmanufacturers ( + sm_s char(16) NOT NULL, -- short name (abbreviation, key) + sm_l char(64) default '', -- full name + sm_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + PRIMARY KEY(sm_s) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +-- software products + +CREATE TABLE swproducts ( + sw_s char(16) NOT NULL, -- short name (order key or abbreviation) + sw_l char(64) default '', -- full product name + sm_s char(16) default '', -- software manufacturer + sw_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + PRIMARY KEY(sw_s), + INDEX(sm_s), + CONSTRAINT c9 FOREIGN KEY (sm_s) REFERENCES swmanufacturers(sm_s) ON UPDATE CASCADE ON DELETE RESTRICT +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +-- software resellers + +CREATE TABLE swresellers ( + sr_s char(16) NOT NULL, -- short name (abbreviation, key) + sr_l char(64) default '', -- full name + sr_a1 char(64) default NULL, -- address line 1 + sr_a2 char(64) default NULL, -- address line 2 + sr_a3 char(64) default NULL, -- address line 3 + sr_a4 char(64) default NULL, -- address line 4 + sr_plz char(8) default NULL, -- ZIP code + sr_ort char(64) default NULL, -- location (town or city name) + sr_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + PRIMARY KEY(sr_s) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +-- license types + +CREATE TABLE licensetypes ( + lt_s char(16) NOT NULL, -- short name (abbreviation, key) + lt_l char(64) default '', -- full name of license type + lt_i int default 1, -- number of places for concurrent use + lt_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + PRIMARY KEY(lt_s), + INDEX(lt_l) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +INSERT INTO licensetypes (lt_s, lt_l, lt_i) VALUES +('s', 'Single user user license', 1), +('c', 'Single user concurrent license', 1), +('flexlm', 'Flexlm controlled client installation', 1), +('gpl2', 'GNU General Public License, version 2', 1), +('lgpl2', 'GNU Lesser General Public License, version 2', 1), +('gpl3', 'GNU General Public License, version 3', 1), +('lgpl3', 'GNU Lesser General Public License, version 3', 1), +('bsd', 'BSD style license', 1), +('flexlm-s5', 'Flexlm license server, 5 users', 5); + + +-- list of obtained licenses/installations + +CREATE TABLE licenses ( + li_pk int(10) unsigned NOT NULL auto_increment, + li_on char(24) default NULL, -- internal order ID + sw_s char(16) default NULL, -- software product + us_s char(12) default NULL, -- user name of license owner + co_s char(16) default NULL, -- computer where license is used + lt_s char(16) default 's', -- license type + li_no char(64) default NULL, -- notes + sr_s char(16) default NULL, -- software reseller + li_dd char(16) default NULL, -- date of delivery + li_nd char(16) default NULL, -- delivery letter ID + li_di char(16) default NULL, -- date of invoice + li_ni char(16) default NULL, -- invoice ID + li_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + PRIMARY KEY(li_pk), + INDEX(li_on), + INDEX(sw_s), + INDEX(us_s), + INDEX(co_s), + INDEX(lt_s), + INDEX(sr_s), + INDEX(li_dd), + INDEX(li_nd), + INDEX(li_di), + INDEX(li_ni), + CONSTRAINT ca FOREIGN KEY (sw_s) REFERENCES swproducts(sw_s) ON UPDATE CASCADE ON DELETE RESTRICT, + CONSTRAINT cb FOREIGN KEY (sr_s) REFERENCES swresellers(sr_s) ON UPDATE CASCADE ON DELETE RESTRICT, + CONSTRAINT cc FOREIGN KEY (lt_s) REFERENCES licensetypes(lt_s) ON UPDATE CASCADE ON DELETE RESTRICT, + CONSTRAINT cd FOREIGN KEY (us_s) REFERENCES users(us_s) ON UPDATE CASCADE ON DELETE RESTRICT, + CONSTRAINT ce FOREIGN KEY (co_s) REFERENCES computers(co_s) ON UPDATE CASCADE ON DELETE RESTRICT +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + diff --git a/support/dktools/dktools/itadmin/it-utf8.sql b/support/dktools/dktools/itadmin/it-utf8.sql new file mode 100644 index 0000000000..fe6c9158ff --- /dev/null +++ b/support/dktools/dktools/itadmin/it-utf8.sql @@ -0,0 +1,312 @@ + +-- The database is named it + +create database it; + +use it; + +-- VLANs + +CREATE TABLE vlans ( + vl_s varchar(8) NOT NULL default '', -- short VLAN type, i.e. 500 + vl_l varchar(64) default 'my network', -- long type, description + vl_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + PRIMARY KEY (vl_s) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +CREATE TABLE networks( + nw_ip varchar(18) NOT NULL default '', -- IP + nw_ma varchar(18) default '', -- network mask + nw_gw varchar(18) default '', -- default gateway + nw_bc varchar(18) default '', -- broadcast address + vl_s varchar(8) default '', -- VLAN + nw_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + PRIMARY KEY(nw_ip), + INDEX(vl_s), + CONSTRAINT cf FOREIGN KEY(vl_s) REFERENCES vlans(vl_s) ON UPDATE CASCADE ON DELETE RESTRICT +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- network connection speed setup + +CREATE TABLE ddspeed ( + sp_s varchar(12) NOT NULL default '', -- short name (abbreviation) + sp_l varchar(64) default NULL, -- long name, description + sp_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + PRIMARY KEY(sp_s) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +INSERT INTO ddspeed (sp_s, sp_l ) VALUES +('auto', 'Autonegotiation'), +('10', '10 MBit/s half-duplex'), +('100', '100 MBit/s half-duplex'), +('10fd', '10 MBit/s full-duplex'), +('100fd', '100 MBit/s full-duplex'); + +-- buildings + +CREATE TABLE buildings ( + gb_s varchar(4) NOT NULL default '', -- short name (abbreviation) + gb_l varchar(64) default NULL, -- complete name + gb_a1 varchar(64) default NULL, -- address line 1 + gb_a2 varchar(64) default NULL, -- address line 2 + gb_a3 varchar(64) default NULL, -- address line 3 + gb_a4 varchar(64) default NULL, -- address line 4 + gb_plz varchar(32) default '12345', -- ZIP code + gb_ort varchar(64) default 'Somewhere', -- location (town or city name) + gb_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + PRIMARY KEY(gb_s) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- network connections + +CREATE TABLE patches ( + dd_n varchar(16) NOT NULL default '', -- short name, label on socket + sp_s varchar(12) default 'auto', -- speed setup + vl_s varchar(8) default '-', -- VLAN + dd_p varchar(16) default NULL, -- hub port used for socket + gb_s varchar(4) default '', -- building + dd_r varchar(8) default NULL, -- room + dd_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + PRIMARY KEY(dd_n), + INDEX(sp_s), + INDEX(vl_s), + INDEX(gb_s), + CONSTRAINT c2 FOREIGN KEY (sp_s) REFERENCES ddspeed(sp_s) ON UPDATE CASCADE ON DELETE RESTRICT, + CONSTRAINT c3 FOREIGN KEY (vl_s) REFERENCES vlans(vl_s) ON UPDATE CASCADE ON DELETE RESTRICT, + CONSTRAINT c4 FOREIGN KEY (gb_s) REFERENCES buildings(gb_s) ON UPDATE CASCADE ON DELETE RESTRICT +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + + +-- persons (computer owners, license owners) + +CREATE TABLE users ( + us_s varchar(12) NOT NULL default '-', -- short name, i.e. login name + us_t varchar(32) default NULL, -- title + us_sn varchar(64) default '', -- surname + us_fn varchar(64) default '', -- name + us_em varchar(64) default NULL, -- e-mail address + us_ko varchar(16) default NULL, -- department (costs controlling ID) + us_se varchar(16) default NULL, -- staff ID number + us_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + PRIMARY KEY(us_s) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- netgroups + +CREATE TABLE netgroups ( + ng_s varchar(12) NOT NULL default '-', -- name + ng_l varchar(64) default '', -- description + ng_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + PRIMARY KEY(ng_s) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- netgroup relations + +CREATE TABLE ngdeps ( + nd_pk int(10) unsigned NOT NULL auto_increment, + nd_p varchar(12) default NULL, -- parent group + nd_c varchar(12) default NULL, -- child group + nd_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + PRIMARY KEY(nd_pk), + INDEX(nd_p), + INDEX(nd_c), + CONSTRAINT c5 FOREIGN KEY (nd_p) REFERENCES netgroups(ng_s) ON UPDATE CASCADE ON DELETE RESTRICT, + CONSTRAINT c6 FOREIGN KEY (nd_c) REFERENCES netgroups(ng_s) ON UPDATE CASCADE ON DELETE RESTRICT +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- DHCP classes + +CREATE TABLE dhcpclasses ( + dc_s varchar(16) NOT NULL default '-', -- DHCP class name + dc_d varchar(64) default NULL, -- description + nw_ip varchar(18) NOT NULL default '0.0.0.0', -- IP network for class + dc_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + PRIMARY KEY(dc_s), + INDEX(nw_ip), + CONSTRAINT cm FOREIGN KEY (nw_ip) REFERENCES networks(nw_ip) ON UPDATE CASCADE ON DELETE RESTRICT +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- DHCP pools + +CREATE TABLE dhcppools( + dp_pk int(10) unsigned NOT NULL auto_increment, -- primary key + dp_st varchar(18) default NULL, -- start address + dp_en varchar(18) default NULL, -- end address + nw_ip varchar(18) default NULL, -- network + dp_al integer default 0, -- allow unknown clients + dp_dn integer default 1, -- deny unknown clients + dc_s varchar(16) default NULL, -- allowed DHCP class + dp_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + PRIMARY KEY(dp_pk), + INDEX(nw_ip), + CONSTRAINT ci FOREIGN KEY(nw_ip) REFERENCES networks(nw_ip) ON UPDATE CASCADE ON DELETE RESTRICT, + CONSTRAINT cj FOREIGN KEY(dc_s) REFERENCES dhcpclasses(dc_s) ON UPDATE CASCADE ON DELETE RESTRICT +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- DHCP groups +CREATE TABLE dhcpgroups( + dg_s varchar(16) NOT NULL, -- group name, primary key + dg_l varchar(64) default NULL, -- description + dg_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + PRIMARY KEY(dg_s) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- DHCP options +CREATE TABLE dhcpoptions( + do_pk int(10) unsigned NOT NULL auto_increment, -- primary key + do_sc ENUM('server', 'vlan', 'network', 'group', 'pool', 'host') default 'server', -- scope type + do_sn varchar(32) default NULL, -- scope name + do_n varchar(64) NOT NULL, -- option name + do_v varchar(128) NOT NULL, -- option value + do_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + PRIMARY KEY(do_pk), + INDEX(do_sc), + INDEX(do_sn), + INDEX(do_n) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- hosts + +CREATE TABLE computers ( + co_s varchar(16) NOT NULL default 'e__', -- hostname + us_s varchar(12) default NULL, -- system owner + co_ip varchar(18) default NULL, -- IP address + co_mc varchar(18) default NULL, -- MAC address + ng_s varchar(12) default NULL, -- netgroup + co_co varchar(64) default NULL, -- comment, description + dc_s varchar(16) default NULL, -- DHCP class + dg_s varchar(16) default NULL, -- DHCP group + dd_n varchar(16) default NULL, -- network patch socket + gb_s varchar(4) default NULL, -- building + co_r varchar(8) default NULL, -- room + co_in varchar(12) default NULL, -- inventory number + co_sn varchar(64) default NULL, -- serial number + co_dd varchar(64) default NULL, -- DNS domain + co_ff integer default 0, -- full name first flag + co_gu integer default 0, -- guest PC flag + co_hi varchar(32) default NULL, -- host ID + co_ex DATE default NULL, -- expiration date (for guest PCs) + co_nn integer default 0, -- skip host when creating network docu + co_na integer default 0, -- skip in arpwatch + co_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + PRIMARY KEY (co_s), + INDEX(us_s), + INDEX(co_ip), + INDEX(ng_s), + INDEX(dc_s), + INDEX(dg_s), + INDEX(dd_n), + INDEX(gb_s), + INDEX(co_r), + INDEX(co_in), + INDEX(co_sn), + CONSTRAINT c7 FOREIGN KEY (us_s) REFERENCES users(us_s) ON UPDATE CASCADE ON DELETE RESTRICT, + CONSTRAINT c8 FOREIGN KEY (ng_s) REFERENCES netgroups(ng_s) ON UPDATE CASCADE ON DELETE RESTRICT, + CONSTRAINT cg FOREIGN KEY (gb_s) REFERENCES buildings(gb_s) ON UPDATE CASCADE ON DELETE RESTRICT, + CONSTRAINT ch FOREIGN KEY (dc_s) REFERENCES dhcpclasses(dc_s) ON UPDATE CASCADE ON DELETE RESTRICT, + CONSTRAINT ck FOREIGN KEY (dg_s) REFERENCES dhcpgroups(dg_s) ON UPDATE CASCADE ON DELETE RESTRICT, + CONSTRAINT cn FOREIGN KEY (dd_n) REFERENCES patches(dd_n) ON UPDATE CASCADE ON DELETE RESTRICT +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +CREATE TABLE aliases ( + al_s varchar(80) NOT NULL, -- the alias + co_s varchar(16) default NULL, -- alias target + co_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + PRIMARY KEY(al_s), + INDEX(co_s), + CONSTRAINT co FOREIGN KEY(co_s) REFERENCES computers(co_s) ON UPDATE CASCADE ON DELETE RESTRICT +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- software manufacturers + +CREATE TABLE swmanufacturers ( + sm_s varchar(16) NOT NULL, -- short name (abbreviation, key) + sm_l varchar(64) default '', -- full name + sm_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + PRIMARY KEY(sm_s) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- software products + +CREATE TABLE swproducts ( + sw_s varchar(16) NOT NULL, -- short name (order key or abbreviation) + sw_l varchar(64) default '', -- full product name + sm_s varchar(16) default '', -- software manufacturer + sw_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + PRIMARY KEY(sw_s), + INDEX(sm_s), + CONSTRAINT c9 FOREIGN KEY (sm_s) REFERENCES swmanufacturers(sm_s) ON UPDATE CASCADE ON DELETE RESTRICT +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- software resellers + +CREATE TABLE swresellers ( + sr_s varchar(16) NOT NULL, -- short name (abbreviation, key) + sr_l varchar(64) default '', -- full name + sr_a1 varchar(64) default NULL, -- address line 1 + sr_a2 varchar(64) default NULL, -- address line 2 + sr_a3 varchar(64) default NULL, -- address line 3 + sr_a4 varchar(64) default NULL, -- address line 4 + sr_plz varchar(8) default NULL, -- ZIP code + sr_ort varchar(64) default NULL, -- location (town or city name) + sr_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + PRIMARY KEY(sr_s) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- license types + +CREATE TABLE licensetypes ( + lt_s varchar(16) NOT NULL, -- short name (abbreviation, key) + lt_l varchar(64) default '', -- full name of license type + lt_i int default 1, -- number of places for concurrent use + lt_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + PRIMARY KEY(lt_s), + INDEX(lt_l) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +INSERT INTO licensetypes (lt_s, lt_l, lt_i) VALUES +('s', 'Single user user license', 1), +('c', 'Single user concurrent license', 1), +('flexlm', 'Flexlm controlled client installation', 1), +('gpl2', 'GNU General Public License, version 2', 1), +('lgpl2', 'GNU Lesser General Public License, version 2', 1), +('gpl3', 'GNU General Public License, version 3', 1), +('lgpl3', 'GNU Lesser General Public License, version 3', 1), +('bsd', 'BSD style license', 1), +('flexlm-s5', 'Flexlm license server, 5 users', 5); + + +-- list of obtained licenses/installations + +CREATE TABLE licenses ( + li_pk int(10) unsigned NOT NULL auto_increment, + li_on varchar(24) default NULL, -- internal order ID + sw_s varchar(16) default NULL, -- software product + us_s varchar(12) default NULL, -- user name of license owner + co_s varchar(16) default NULL, -- computer where license is used + lt_s varchar(16) default 's', -- license type + li_no varchar(64) default NULL, -- notes + sr_s varchar(16) default NULL, -- software reseller + li_dd varchar(16) default NULL, -- date of delivery + li_nd varchar(16) default NULL, -- delivery letter ID + li_di varchar(16) default NULL, -- date of invoice + li_ni varchar(16) default NULL, -- invoice ID + li_m timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + PRIMARY KEY(li_pk), + INDEX(li_on), + INDEX(sw_s), + INDEX(us_s), + INDEX(co_s), + INDEX(lt_s), + INDEX(sr_s), + INDEX(li_dd), + INDEX(li_nd), + INDEX(li_di), + INDEX(li_ni), + CONSTRAINT ca FOREIGN KEY (sw_s) REFERENCES swproducts(sw_s) ON UPDATE CASCADE ON DELETE RESTRICT, + CONSTRAINT cb FOREIGN KEY (sr_s) REFERENCES swresellers(sr_s) ON UPDATE CASCADE ON DELETE RESTRICT, + CONSTRAINT cc FOREIGN KEY (lt_s) REFERENCES licensetypes(lt_s) ON UPDATE CASCADE ON DELETE RESTRICT, + CONSTRAINT cd FOREIGN KEY (us_s) REFERENCES users(us_s) ON UPDATE CASCADE ON DELETE RESTRICT, + CONSTRAINT ce FOREIGN KEY (co_s) REFERENCES computers(co_s) ON UPDATE CASCADE ON DELETE RESTRICT +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + |