
-- 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;

