
-- 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(64) 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(16) NOT NULL,		-- the alias
  co_s	char(16) default NULL,		-- alias target
  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
  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 '',			-- Auftragsnummer der Bestellung
  sw_s	char(16) default '',			-- Software-Produkt
  us_s	char(12) default '-',			-- User Login Name
  co_s	char(16) default '-',			-- 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;


