packages/ssoinabox-webui/root/usr/local/share/ssoinabox/db/install.sql
author Dan Fuhry <dan@enanocms.org>
Sat, 23 Feb 2013 14:26:38 -0500
changeset 9 f4bf6556fb9f
parent 8 f68fdcc18df9
permissions -rw-r--r--
Merged
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
8
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
     1
DROP TABLE IF EXISTS addresses;
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
     2
DROP TABLE IF EXISTS interfaces;
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
     3
DROP TABLE IF EXISTS hosts;
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
     4
DROP TABLE IF EXISTS pools;
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
     5
DROP TABLE IF EXISTS dns_records;
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
     6
DROP TABLE IF EXISTS networks;
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
     7
DROP TABLE IF EXISTS sites;
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
     8
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
     9
CREATE TABLE sites(
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    10
	id int(12) unsigned NOT NULL auto_increment,
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    11
	name varchar(64) NOT NULL DEFAULT 'Default site',
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    12
	domain varchar(64) NOT NULL DEFAULT 'example.com',
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    13
	PRIMARY KEY ( id )
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    14
);
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    15
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    16
CREATE TABLE networks(
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    17
	id int(12) unsigned NOT NULL auto_increment,
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    18
	site_id int(12) unsigned NOT NULL,
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    19
	name varchar(64) NOT NULL DEFAULT 'Main network',
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    20
	subdomain_name varchar(16) NOT NULL DEFAULT 'hq',
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    21
	ipv4_subnet int(8) unsigned DEFAULT NULL,
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    22
	ipv4_subnet_mask int(8) unsigned DEFAULT NULL,
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    23
	ipv6_prefix varbinary(32) NOT NULL DEFAULT 0,
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    24
	PRIMARY KEY ( id ),
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    25
	CONSTRAINT FOREIGN KEY ( site_id ) REFERENCES sites(id)
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    26
	  ON DELETE CASCADE
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    27
);
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    28
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    29
CREATE TABLE pools(
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    30
	id int(12) unsigned NOT NULL auto_increment,
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    31
	network_id int(12) unsigned NOT NULL,
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    32
	address_class ENUM('inet4', 'inet6', 'eui48') NOT NULL DEFAULT 'inet4',
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    33
	address_start varbinary(32),
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    34
	address_end varbinary(32),
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    35
	name varchar(64) NOT NULL DEFAULT 'A pool',
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    36
	PRIMARY KEY ( id ),
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    37
	CONSTRAINT FOREIGN KEY ( network_id ) REFERENCES networks(id)
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    38
	  ON DELETE CASCADE
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    39
);
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    40
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    41
CREATE TABLE hosts(
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    42
	id int(12) unsigned NOT NULL auto_increment,
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    43
	site_id int(12) unsigned NOT NULL,
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    44
	hostname varchar(32) NOT NULL DEFAULT 'myhost',
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    45
	owner varchar(32) NOT NULL DEFAULT 'root',
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    46
	created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    47
	PRIMARY KEY ( id ),
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    48
	CONSTRAINT FOREIGN KEY ( site_id ) REFERENCES sites(id)
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    49
	  ON DELETE CASCADE
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    50
);
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    51
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    52
CREATE TABLE interfaces(
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    53
	id int(12) unsigned NOT NULL auto_increment,
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    54
	host_id int(12) unsigned NOT NULL,
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    55
	network_id int(12) unsigned NOT NULL,
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    56
	name varchar(16) NOT NULL DEFAULT 'eth0',
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    57
	description varchar(255) NOT NULL DEFAULT 'eth0',
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    58
	PRIMARY KEY ( id ),
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    59
	CONSTRAINT FOREIGN KEY ( host_id ) REFERENCES hosts(id)
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    60
	  ON DELETE CASCADE,
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    61
	CONSTRAINT FOREIGN KEY ( network_id ) REFERENCES networks(id)
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    62
	  ON DELETE CASCADE
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    63
);
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    64
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    65
CREATE TABLE addresses(
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    66
	id int(12) unsigned NOT NULL auto_increment,
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    67
	interface_id int(12) unsigned NOT NULL,
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    68
	pool_id int(12) unsigned DEFAULT NULL,
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    69
	address_class ENUM('inet4', 'inet6', 'eui48') NOT NULL DEFAULT 'inet4',
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    70
	address_type ENUM('static', 'dynamic', 'automatic') NOT NULL DEFAULT 'dynamic',
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    71
	address_value varbinary(32) DEFAULT NULL,
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    72
	PRIMARY KEY ( id ),
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    73
	CONSTRAINT FOREIGN KEY ( interface_id ) REFERENCES interfaces(id)
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    74
	  ON DELETE CASCADE,
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    75
	FOREIGN KEY ( pool_id ) REFERENCES pools(id)
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    76
	  ON DELETE CASCADE
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    77
);
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    78
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    79
CREATE TABLE dns_records (
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    80
	id int(12) unsigned NOT NULL auto_increment,
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    81
	network_id int(12) unsigned NOT NULL,
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    82
	owner varchar(64) NOT NULL DEFAULT 'root',
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    83
	rname varchar(128) NOT NULL,
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    84
	type ENUM('A','AAAA','MX','CNAME','NS','SRV','TXT','SSHFP') DEFAULT NULL,
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    85
	ttl int(6) unsigned DEFAULT NULL,
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    86
	rdata mediumtext,
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    87
	PRIMARY KEY (id),
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    88
	CONSTRAINT FOREIGN KEY ( network_id ) REFERENCES networks(id)
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    89
		ON DELETE CASCADE
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    90
);
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    91
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    92
INSERT INTO sites(name, domain) VALUES
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    93
	('Rochester', 'tits123.com'),
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    94
	('Cleveland', 'tits123.com');
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    95
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    96
INSERT INTO networks(site_id, subdomain_name, name, ipv4_subnet, ipv4_subnet_mask, ipv6_prefix) VALUES
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    97
	(1, 'roc', 'Main VLAN', 0x0a010000, 0xffff0000, 0x20010470e18f0000),
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    98
	(1, 'oe', 'Guest VLAN', 0x0a028000, 0xffff8000, 0x20010470e18f0001),
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
    99
	(2, 'cle', 'Main VLAN', 0x0a000000, 0xffff0000, 0x20010470e0d80000);
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
   100
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
   101
INSERT INTO pools(network_id, address_class, address_start, address_end, name) VALUES
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
   102
	(1, 'inet4', 0x0a010001, 0x0a0100ff, 'Servers'),
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
   103
	(1, 'inet4', 0x0a010100, 0x0a01017f, 'User machines'),
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
   104
	(1, 'inet4', 0x0a010180, 0x0a0101ff, 'VMs'),
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
   105
	(2, 'inet4', 0x0a028003, 0x0a02fffe, 'OpenEars clients'),
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
   106
	(3, 'inet4', 0x0a000001, 0x0a0000ff, 'Servers'),
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
   107
	(3, 'inet4', 0x0a000100, 0x0a0003ff, 'User machines'),
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
   108
	(1, 'inet6', 0x20010470e18f0000020000fffe000000, 0x20010470e18f0000fffffffffeffffff, 'IPv6 autoconfig hosts'),
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
   109
	(3, 'inet6', 0x20010470e0d80000020000fffe000000, 0x20010470e0d80000fffffffffeffffff, 'IPv6 autoconfig hosts');
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
   110
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
   111
INSERT INTO hosts(site_id, hostname, owner) VALUES
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
   112
	(1, 'xombie', 'root'),
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
   113
	(1, 'nighthawk', 'root'),
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
   114
	(1, 'ratsalad', 'dan');
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
   115
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
   116
INSERT INTO interfaces(host_id, network_id, name, description) VALUES
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
   117
	(1, 1, 'vlan1', 'Internal interface'),
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
   118
	(1, 2, 'vlan5', 'OpenEars interface'),
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
   119
	(2, 1, 'vl-xx0r', 'Main interface'),
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
   120
	(3, 1, 'eth0', 'Main interface');
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
   121
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
   122
INSERT INTO addresses(interface_id, pool_id, address_class, address_type, address_value) VALUES
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
   123
	(1, NULL, 'eui48', 'static', 0x001b21c2092c),
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
   124
	(1, 1	, 'inet4', 'static', 0x0a010001),
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
   125
	(1, 7	, 'inet6', 'static', 0x20010470e18f00000000000000000001),
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
   126
	(2, NULL, 'eui48', 'static', 0x001b21c2092c),
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
   127
	(2, 1	, 'inet4', 'static', 0x0a027f01),
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
   128
	(3, NULL, 'eui48', 'static', 0x001b21c4f583),
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
   129
	(3, 1	, 'inet4', 'static', 0x0a010003),
f68fdcc18df9 Started on the database
Dan Fuhry <dan@enanocms.org>
parents:
diff changeset
   130
	(4, 2	, 'inet4', 'dynamic', NULL);