summaryrefslogtreecommitdiff
path: root/support/dktools/dktools/itadmin
diff options
context:
space:
mode:
authorNorbert Preining <norbert@preining.info>2019-09-02 13:46:59 +0900
committerNorbert Preining <norbert@preining.info>2019-09-02 13:46:59 +0900
commite0c6872cf40896c7be36b11dcc744620f10adf1d (patch)
tree60335e10d2f4354b0674ec22d7b53f0f8abee672 /support/dktools/dktools/itadmin
Initial commit
Diffstat (limited to 'support/dktools/dktools/itadmin')
-rw-r--r--support/dktools/dktools/itadmin/it-de.sql314
-rw-r--r--support/dktools/dktools/itadmin/it-latin1.sql312
-rw-r--r--support/dktools/dktools/itadmin/it-utf8.sql312
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;
+