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