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

