--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/packages/ssoinabox-webui/root/usr/local/share/ssoinabox/db/install.sql	Sat Feb 23 14:26:05 2013 -0500
@@ -0,0 +1,130 @@
+DROP TABLE IF EXISTS addresses;
+DROP TABLE IF EXISTS interfaces;
+DROP TABLE IF EXISTS hosts;
+DROP TABLE IF EXISTS pools;
+DROP TABLE IF EXISTS dns_records;
+DROP TABLE IF EXISTS networks;
+DROP TABLE IF EXISTS sites;
+
+CREATE TABLE sites(
+	id int(12) unsigned NOT NULL auto_increment,
+	name varchar(64) NOT NULL DEFAULT 'Default site',
+	domain varchar(64) NOT NULL DEFAULT 'example.com',
+	PRIMARY KEY ( id )
+);
+
+CREATE TABLE networks(
+	id int(12) unsigned NOT NULL auto_increment,
+	site_id int(12) unsigned NOT NULL,
+	name varchar(64) NOT NULL DEFAULT 'Main network',
+	subdomain_name varchar(16) NOT NULL DEFAULT 'hq',
+	ipv4_subnet int(8) unsigned DEFAULT NULL,
+	ipv4_subnet_mask int(8) unsigned DEFAULT NULL,
+	ipv6_prefix varbinary(32) NOT NULL DEFAULT 0,
+	PRIMARY KEY ( id ),
+	CONSTRAINT FOREIGN KEY ( site_id ) REFERENCES sites(id)
+	  ON DELETE CASCADE
+);
+
+CREATE TABLE pools(
+	id int(12) unsigned NOT NULL auto_increment,
+	network_id int(12) unsigned NOT NULL,
+	address_class ENUM('inet4', 'inet6', 'eui48') NOT NULL DEFAULT 'inet4',
+	address_start varbinary(32),
+	address_end varbinary(32),
+	name varchar(64) NOT NULL DEFAULT 'A pool',
+	PRIMARY KEY ( id ),
+	CONSTRAINT FOREIGN KEY ( network_id ) REFERENCES networks(id)
+	  ON DELETE CASCADE
+);
+
+CREATE TABLE hosts(
+	id int(12) unsigned NOT NULL auto_increment,
+	site_id int(12) unsigned NOT NULL,
+	hostname varchar(32) NOT NULL DEFAULT 'myhost',
+	owner varchar(32) NOT NULL DEFAULT 'root',
+	created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+	PRIMARY KEY ( id ),
+	CONSTRAINT FOREIGN KEY ( site_id ) REFERENCES sites(id)
+	  ON DELETE CASCADE
+);
+
+CREATE TABLE interfaces(
+	id int(12) unsigned NOT NULL auto_increment,
+	host_id int(12) unsigned NOT NULL,
+	network_id int(12) unsigned NOT NULL,
+	name varchar(16) NOT NULL DEFAULT 'eth0',
+	description varchar(255) NOT NULL DEFAULT 'eth0',
+	PRIMARY KEY ( id ),
+	CONSTRAINT FOREIGN KEY ( host_id ) REFERENCES hosts(id)
+	  ON DELETE CASCADE,
+	CONSTRAINT FOREIGN KEY ( network_id ) REFERENCES networks(id)
+	  ON DELETE CASCADE
+);
+
+CREATE TABLE addresses(
+	id int(12) unsigned NOT NULL auto_increment,
+	interface_id int(12) unsigned NOT NULL,
+	pool_id int(12) unsigned DEFAULT NULL,
+	address_class ENUM('inet4', 'inet6', 'eui48') NOT NULL DEFAULT 'inet4',
+	address_type ENUM('static', 'dynamic', 'automatic') NOT NULL DEFAULT 'dynamic',
+	address_value varbinary(32) DEFAULT NULL,
+	PRIMARY KEY ( id ),
+	CONSTRAINT FOREIGN KEY ( interface_id ) REFERENCES interfaces(id)
+	  ON DELETE CASCADE,
+	FOREIGN KEY ( pool_id ) REFERENCES pools(id)
+	  ON DELETE CASCADE
+);
+
+CREATE TABLE dns_records (
+	id int(12) unsigned NOT NULL auto_increment,
+	network_id int(12) unsigned NOT NULL,
+	owner varchar(64) NOT NULL DEFAULT 'root',
+	rname varchar(128) NOT NULL,
+	type ENUM('A','AAAA','MX','CNAME','NS','SRV','TXT','SSHFP') DEFAULT NULL,
+	ttl int(6) unsigned DEFAULT NULL,
+	rdata mediumtext,
+	PRIMARY KEY (id),
+	CONSTRAINT FOREIGN KEY ( network_id ) REFERENCES networks(id)
+		ON DELETE CASCADE
+);
+
+INSERT INTO sites(name, domain) VALUES
+	('Rochester', 'tits123.com'),
+	('Cleveland', 'tits123.com');
+
+INSERT INTO networks(site_id, subdomain_name, name, ipv4_subnet, ipv4_subnet_mask, ipv6_prefix) VALUES
+	(1, 'roc', 'Main VLAN', 0x0a010000, 0xffff0000, 0x20010470e18f0000),
+	(1, 'oe', 'Guest VLAN', 0x0a028000, 0xffff8000, 0x20010470e18f0001),
+	(2, 'cle', 'Main VLAN', 0x0a000000, 0xffff0000, 0x20010470e0d80000);
+
+INSERT INTO pools(network_id, address_class, address_start, address_end, name) VALUES
+	(1, 'inet4', 0x0a010001, 0x0a0100ff, 'Servers'),
+	(1, 'inet4', 0x0a010100, 0x0a01017f, 'User machines'),
+	(1, 'inet4', 0x0a010180, 0x0a0101ff, 'VMs'),
+	(2, 'inet4', 0x0a028003, 0x0a02fffe, 'OpenEars clients'),
+	(3, 'inet4', 0x0a000001, 0x0a0000ff, 'Servers'),
+	(3, 'inet4', 0x0a000100, 0x0a0003ff, 'User machines'),
+	(1, 'inet6', 0x20010470e18f0000020000fffe000000, 0x20010470e18f0000fffffffffeffffff, 'IPv6 autoconfig hosts'),
+	(3, 'inet6', 0x20010470e0d80000020000fffe000000, 0x20010470e0d80000fffffffffeffffff, 'IPv6 autoconfig hosts');
+
+INSERT INTO hosts(site_id, hostname, owner) VALUES
+	(1, 'xombie', 'root'),
+	(1, 'nighthawk', 'root'),
+	(1, 'ratsalad', 'dan');
+
+INSERT INTO interfaces(host_id, network_id, name, description) VALUES
+	(1, 1, 'vlan1', 'Internal interface'),
+	(1, 2, 'vlan5', 'OpenEars interface'),
+	(2, 1, 'vl-xx0r', 'Main interface'),
+	(3, 1, 'eth0', 'Main interface');
+
+INSERT INTO addresses(interface_id, pool_id, address_class, address_type, address_value) VALUES
+	(1, NULL, 'eui48', 'static', 0x001b21c2092c),
+	(1, 1	, 'inet4', 'static', 0x0a010001),
+	(1, 7	, 'inet6', 'static', 0x20010470e18f00000000000000000001),
+	(2, NULL, 'eui48', 'static', 0x001b21c2092c),
+	(2, 1	, 'inet4', 'static', 0x0a027f01),
+	(3, NULL, 'eui48', 'static', 0x001b21c4f583),
+	(3, 1	, 'inet4', 'static', 0x0a010003),
+	(4, 2	, 'inet4', 'dynamic', NULL);