r3268 another portion of work on VLANs
[racktables] / install / init-structure.sql
1 alter database character set utf8;
2 set names 'utf8';
3
4 CREATE TABLE `Atom` (
5 `molecule_id` int(10) unsigned default NULL,
6 `rack_id` int(10) unsigned default NULL,
7 `unit_no` int(10) unsigned default NULL,
8 `atom` enum('front','interior','rear') default NULL
9 ) ENGINE=MyISAM;
10
11 CREATE TABLE `Attribute` (
12 `id` int(10) unsigned NOT NULL auto_increment,
13 `type` enum('string','uint','float','dict') default NULL,
14 `name` char(64) default NULL,
15 PRIMARY KEY (`id`),
16 UNIQUE KEY `name` (`name`)
17 ) ENGINE=MyISAM AUTO_INCREMENT=10000;
18
19 CREATE TABLE `AttributeMap` (
20 `objtype_id` int(10) unsigned NOT NULL default '1',
21 `attr_id` int(10) unsigned NOT NULL default '1',
22 `chapter_id` int(10) unsigned NULL,
23 UNIQUE KEY `objtype_id` (`objtype_id`,`attr_id`)
24 ) ENGINE=MyISAM;
25
26 CREATE TABLE `RackObject` (
27 `id` int(10) unsigned NOT NULL auto_increment,
28 `name` char(255) default NULL,
29 `label` char(255) default NULL,
30 `barcode` char(16) default NULL,
31 `objtype_id` int(10) unsigned NOT NULL default '1',
32 `asset_no` char(64) default NULL,
33 `has_problems` enum('yes','no') NOT NULL default 'no',
34 `comment` text,
35 PRIMARY KEY (`id`),
36 UNIQUE KEY `RackObject_asset_no` (`asset_no`),
37 UNIQUE KEY `name` (`name`),
38 UNIQUE KEY `barcode` (`barcode`)
39 ) ENGINE=InnoDB;
40
41 CREATE TABLE `AttributeValue` (
42 `object_id` int(10) unsigned default NULL,
43 `attr_id` int(10) unsigned default NULL,
44 `string_value` char(128) default NULL,
45 `uint_value` int(10) unsigned default NULL,
46 `float_value` float default NULL,
47 UNIQUE KEY `object_id` (`object_id`,`attr_id`),
48 CONSTRAINT `AttributeValue-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`)
49 ) ENGINE=InnoDB;
50
51 CREATE TABLE `Chapter` (
52 `id` int(10) unsigned NOT NULL auto_increment,
53 `sticky` enum('yes','no') default 'no',
54 `name` char(128) NOT NULL,
55 PRIMARY KEY (`id`),
56 UNIQUE KEY `name` (`name`)
57 ) ENGINE=MyISAM AUTO_INCREMENT=10000;
58
59 CREATE TABLE `Config` (
60 `varname` char(32) NOT NULL,
61 `varvalue` char(255) NOT NULL,
62 `vartype` enum('string','uint') NOT NULL default 'string',
63 `emptyok` enum('yes','no') NOT NULL default 'no',
64 `is_hidden` enum('yes','no') NOT NULL default 'yes',
65 `is_userdefined` enum('yes','no') NOT NULL default 'yes',
66 `description` text,
67 PRIMARY KEY (`varname`)
68 ) ENGINE=MyISAM;
69
70 CREATE TABLE `Dictionary` (
71 `chapter_id` int(10) unsigned NOT NULL,
72 `dict_key` int(10) unsigned NOT NULL auto_increment,
73 `dict_value` char(255) default NULL,
74 PRIMARY KEY (`dict_key`),
75 UNIQUE KEY `chap_to_val` (`chapter_id`,`dict_value`)
76 ) ENGINE=MyISAM AUTO_INCREMENT=50000;
77
78 CREATE TABLE `File` (
79 `id` int(10) unsigned NOT NULL auto_increment,
80 `name` char(255) NOT NULL,
81 `type` char(255) NOT NULL,
82 `size` int(10) unsigned NOT NULL,
83 `ctime` datetime NOT NULL,
84 `mtime` datetime NOT NULL,
85 `atime` datetime NOT NULL,
86 `contents` longblob NOT NULL,
87 `comment` text,
88 PRIMARY KEY (`id`),
89 UNIQUE KEY `name` (`name`)
90 ) ENGINE=InnoDB;
91
92 CREATE TABLE `FileLink` (
93 `id` int(10) unsigned NOT NULL auto_increment,
94 `file_id` int(10) unsigned NOT NULL,
95 `entity_type` enum('ipv4net','ipv4rspool','ipv4vs','object','rack','user') NOT NULL default 'object',
96 `entity_id` int(10) NOT NULL,
97 PRIMARY KEY (`id`),
98 KEY `FileLink-file_id` (`file_id`),
99 UNIQUE KEY `FileLink-unique` (`file_id`,`entity_type`,`entity_id`),
100 CONSTRAINT `FileLink-File_fkey` FOREIGN KEY (`file_id`) REFERENCES `File` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
101 ) ENGINE=InnoDB;
102
103 CREATE TABLE `IPv4Address` (
104 `ip` int(10) unsigned NOT NULL,
105 `name` char(255) NOT NULL,
106 `reserved` enum('yes','no') default NULL,
107 PRIMARY KEY (`ip`)
108 ) ENGINE=MyISAM;
109
110 CREATE TABLE `IPv4Allocation` (
111 `object_id` int(10) unsigned NOT NULL,
112 `ip` int(10) unsigned NOT NULL,
113 `name` char(255) NOT NULL,
114 `type` enum('regular','shared','virtual','router') default NULL,
115 PRIMARY KEY (`object_id`,`ip`)
116 ) ENGINE=MyISAM;
117
118 CREATE TABLE `IPv4RSPool` (
119 `id` int(10) unsigned NOT NULL auto_increment,
120 `name` char(255) default NULL,
121 `vsconfig` text,
122 `rsconfig` text,
123 PRIMARY KEY (`id`)
124 ) ENGINE=InnoDB;
125
126 CREATE TABLE `IPv4VS` (
127 `id` int(10) unsigned NOT NULL auto_increment,
128 `vip` int(10) unsigned default NULL,
129 `vport` smallint(5) unsigned default NULL,
130 `proto` enum('TCP','UDP') NOT NULL default 'TCP',
131 `name` char(255) default NULL,
132 `vsconfig` text,
133 `rsconfig` text,
134 PRIMARY KEY (`id`)
135 ) ENGINE=InnoDB;
136
137 CREATE TABLE `IPv4LB` (
138 `object_id` int(10) unsigned default NULL,
139 `rspool_id` int(10) unsigned default NULL,
140 `vs_id` int(10) unsigned default NULL,
141 `vsconfig` text,
142 `rsconfig` text,
143 UNIQUE KEY `LB-VS` (`object_id`,`vs_id`),
144 KEY `IPv4LB-FK-rspool_id` (`rspool_id`),
145 KEY `IPv4LB-FK-vs_id` (`vs_id`),
146 CONSTRAINT `IPv4LB-FK-vs_id` FOREIGN KEY (`vs_id`) REFERENCES `IPv4VS` (`id`),
147 CONSTRAINT `IPv4LB-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`),
148 CONSTRAINT `IPv4LB-FK-rspool_id` FOREIGN KEY (`rspool_id`) REFERENCES `IPv4RSPool` (`id`)
149 ) ENGINE=InnoDB;
150
151 CREATE TABLE `IPv4Network` (
152 `id` int(10) unsigned NOT NULL auto_increment,
153 `ip` int(10) unsigned NOT NULL,
154 `mask` int(10) unsigned NOT NULL,
155 `name` char(255) default NULL,
156 `comment` text,
157 PRIMARY KEY (`id`),
158 UNIQUE KEY `base-len` (`ip`,`mask`)
159 ) ENGINE=InnoDB;
160
161 CREATE TABLE `IPv4RS` (
162 `id` int(10) unsigned NOT NULL auto_increment,
163 `inservice` enum('yes','no') NOT NULL default 'no',
164 `rsip` int(10) unsigned default NULL,
165 `rsport` smallint(5) unsigned default NULL,
166 `rspool_id` int(10) unsigned default NULL,
167 `rsconfig` text,
168 PRIMARY KEY (`id`),
169 UNIQUE KEY `pool-endpoint` (`rspool_id`,`rsip`,`rsport`),
170 CONSTRAINT `IPv4RS-FK` FOREIGN KEY (`rspool_id`) REFERENCES `IPv4RSPool` (`id`) ON DELETE CASCADE
171 ) ENGINE=InnoDB;
172
173 CREATE TABLE `LDAPCache` (
174 `presented_username` char(64) NOT NULL,
175 `successful_hash` char(40) NOT NULL,
176 `first_success` timestamp NOT NULL default CURRENT_TIMESTAMP,
177 `last_retry` timestamp NOT NULL default '0000-00-00 00:00:00',
178 `displayed_name` char(128) default NULL,
179 `memberof` text,
180 UNIQUE KEY `presented_username` (`presented_username`),
181 KEY `scanidx` (`presented_username`,`successful_hash`)
182 ) ENGINE=InnoDB;
183
184 CREATE TABLE `Molecule` (
185 `id` int(10) unsigned NOT NULL auto_increment,
186 PRIMARY KEY (`id`)
187 ) ENGINE=MyISAM;
188
189 CREATE TABLE `MountOperation` (
190 `id` int(10) unsigned NOT NULL auto_increment,
191 `object_id` int(10) unsigned NOT NULL,
192 `ctime` timestamp NOT NULL,
193 `user_name` char(64) default NULL,
194 `old_molecule_id` int(10) unsigned default NULL,
195 `new_molecule_id` int(10) unsigned default NULL,
196 `comment` text,
197 PRIMARY KEY (`id`)
198 ) ENGINE=MyISAM;
199
200 CREATE TABLE `PortInnerInterface` (
201 `id` int(10) unsigned NOT NULL,
202 `iif_name` char(16) NOT NULL,
203 PRIMARY KEY (`id`),
204 UNIQUE KEY `iif_name` (`iif_name`)
205 ) ENGINE=InnoDB;
206
207 CREATE TABLE `PortInterfaceCompat` (
208 `iif_id` int(10) unsigned NOT NULL,
209 `oif_id` int(10) unsigned NOT NULL,
210 UNIQUE KEY `pair` (`iif_id`,`oif_id`),
211 CONSTRAINT `PortInterfaceCompat-FK-iif_id` FOREIGN KEY (`iif_id`) REFERENCES `PortInnerInterface` (`id`)
212 ) ENGINE=InnoDB;
213
214 CREATE TABLE `Port` (
215 `id` int(10) unsigned NOT NULL auto_increment,
216 `object_id` int(10) unsigned NOT NULL,
217 `name` char(255) NOT NULL,
218 `iif_id` int(10) unsigned NOT NULL,
219 `type` int(10) unsigned NOT NULL,
220 `l2address` char(64) default NULL,
221 `reservation_comment` char(255) default NULL,
222 `label` char(255) default NULL,
223 PRIMARY KEY (`id`),
224 UNIQUE KEY `object_iif_oif_name` (`object_id`,`iif_id`,`type`,`name`),
225 KEY `type` (`type`),
226 KEY `comment` (`reservation_comment`),
227 KEY `l2address` (`l2address`),
228 KEY `Port-FK-iif-oif` (`iif_id`,`type`),
229 CONSTRAINT `Port-FK-iif-oif` FOREIGN KEY (`iif_id`, `type`) REFERENCES `PortInterfaceCompat` (`iif_id`, `oif_id`),
230 CONSTRAINT `Port-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`)
231 ) ENGINE=InnoDB;
232
233 CREATE TABLE `Link` (
234 `porta` int(10) unsigned NOT NULL,
235 `portb` int(10) unsigned NOT NULL,
236 PRIMARY KEY (`porta`,`portb`),
237 UNIQUE KEY `porta` (`porta`),
238 UNIQUE KEY `portb` (`portb`),
239 CONSTRAINT `Link-FK-a` FOREIGN KEY (`porta`) REFERENCES `Port` (`id`),
240 CONSTRAINT `Link-FK-b` FOREIGN KEY (`portb`) REFERENCES `Port` (`id`)
241 ) ENGINE=InnoDB;
242
243 CREATE TABLE `PortCompat` (
244 `type1` int(10) unsigned NOT NULL,
245 `type2` int(10) unsigned NOT NULL,
246 UNIQUE KEY `type1_2` (`type1`,`type2`),
247 KEY `type2` (`type2`)
248 ) ENGINE=MyISAM;
249
250 CREATE TABLE `IPv4NAT` (
251 `object_id` int(10) unsigned NOT NULL,
252 `proto` enum('TCP','UDP') not null default 'TCP',
253 `localip` int(10) unsigned NOT NULL,
254 `localport` smallint(5) unsigned NOT NULL,
255 `remoteip` int(10) unsigned NOT NULL,
256 `remoteport` smallint(5) unsigned NOT NULL,
257 `description` char(255) default NULL,
258 PRIMARY KEY (`object_id`,`proto`,`localip`,`localport`,`remoteip`,`remoteport`),
259 KEY `localip` (`localip`),
260 KEY `remoteip` (`remoteip`),
261 KEY `object_id` (`object_id`),
262 CONSTRAINT `IPv4NAT-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`)
263 ) ENGINE=InnoDB;
264
265 CREATE TABLE `RackRow` (
266 `id` int(10) unsigned NOT NULL auto_increment,
267 `name` char(255) NOT NULL,
268 PRIMARY KEY (`id`)
269 ) ENGINE=MyISAM;
270
271 CREATE TABLE `Rack` (
272 `id` int(10) unsigned NOT NULL auto_increment,
273 `name` char(255) default NULL,
274 `row_id` int(10) unsigned NOT NULL default '1',
275 `height` tinyint(3) unsigned NOT NULL default '42',
276 `comment` text,
277 `thumb_data` blob,
278 PRIMARY KEY (`id`),
279 UNIQUE KEY `name_in_row` (`row_id`,`name`)
280 ) ENGINE=MyISAM;
281
282 CREATE TABLE `RackHistory` (
283 `id` int(10) unsigned default NULL,
284 `name` char(255) default NULL,
285 `row_id` int(10) unsigned default NULL,
286 `height` tinyint(3) unsigned default NULL,
287 `comment` text,
288 `thumb_data` blob,
289 `ctime` timestamp NOT NULL,
290 `user_name` char(64) default NULL
291 ) ENGINE=MyISAM;
292
293 CREATE TABLE `RackObjectHistory` (
294 `id` int(10) unsigned default NULL,
295 `name` char(255) default NULL,
296 `label` char(255) default NULL,
297 `barcode` char(16) default NULL,
298 `objtype_id` int(10) unsigned default NULL,
299 `asset_no` char(64) default NULL,
300 `has_problems` enum('yes','no') NOT NULL default 'no',
301 `comment` text,
302 `ctime` timestamp NOT NULL,
303 `user_name` char(64) default NULL
304 ) ENGINE=MyISAM;
305
306 CREATE TABLE `RackSpace` (
307 `rack_id` int(10) unsigned NOT NULL default '0',
308 `unit_no` int(10) unsigned NOT NULL default '0',
309 `atom` enum('front','interior','rear') NOT NULL default 'interior',
310 `state` enum('A','U','T','W') NOT NULL default 'A',
311 `object_id` int(10) unsigned default NULL,
312 PRIMARY KEY (`rack_id`,`unit_no`,`atom`),
313 KEY `RackSpace_object_id` (`object_id`)
314 ) ENGINE=MyISAM;
315
316 CREATE TABLE `Script` (
317 `script_name` char(64) NOT NULL,
318 `script_text` longtext,
319 PRIMARY KEY (`script_name`)
320 ) TYPE=MyISAM;
321
322 CREATE TABLE `TagTree` (
323 `id` int(10) unsigned NOT NULL auto_increment,
324 `parent_id` int(10) unsigned default NULL,
325 `tag` char(255) default NULL,
326 PRIMARY KEY (`id`),
327 UNIQUE KEY `tag` (`tag`),
328 KEY `TagTree-K-parent_id` (`parent_id`),
329 CONSTRAINT `TagTree-K-parent_id` FOREIGN KEY (`parent_id`) REFERENCES `TagTree` (`id`)
330 ) TYPE=InnoDB;
331
332 CREATE TABLE `TagStorage` (
333 `entity_realm` enum('file','ipv4net','ipv4vs','ipv4rspool','object','rack','user') NOT NULL default 'object',
334 `entity_id` int(10) unsigned NOT NULL,
335 `tag_id` int(10) unsigned NOT NULL,
336 UNIQUE KEY `entity_tag` (`entity_realm`,`entity_id`,`tag_id`),
337 KEY `entity_id` (`entity_id`),
338 KEY `TagStorage-FK-tag_id` (`tag_id`),
339 CONSTRAINT `TagStorage-FK-tag_id` FOREIGN KEY (`tag_id`) REFERENCES `TagTree` (`id`)
340 ) TYPE=InnoDB;
341
342 CREATE TABLE `UserAccount` (
343 `user_id` int(10) unsigned NOT NULL auto_increment,
344 `user_name` char(64) NOT NULL,
345 `user_password_hash` char(40) default NULL,
346 `user_realname` char(64) default NULL,
347 PRIMARY KEY (`user_id`),
348 UNIQUE KEY `user_name` (`user_name`)
349 ) ENGINE=MyISAM AUTO_INCREMENT=10000;
350
351 CREATE TABLE `UserConfig` (
352 `varname` char(32) NOT NULL,
353 `varvalue` char(255) NOT NULL,
354 `user` char(64) NOT NULL,
355 UNIQUE KEY `user_varname` (`user`,`varname`)
356 ) TYPE=InnoDB;
357
358 CREATE TABLE `VLANDomain` (
359 `id` int(10) unsigned NOT NULL auto_increment,
360 `description` char(255) default NULL,
361 `last_reset` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
362 `last_pull` timestamp NOT NULL default '0000-00-00 00:00:00',
363 `last_push` timestamp NOT NULL default '0000-00-00 00:00:00',
364 `allow_pull` enum('yes','no') NOT NULL default 'no',
365 PRIMARY KEY (`id`),
366 UNIQUE KEY `description` (`description`)
367 ) ENGINE=InnoDB;
368
369 CREATE TABLE `VLANEligibleOIF` (
370 `oif_id` int(10) unsigned NOT NULL default '0',
371 PRIMARY KEY (`oif_id`)
372 ) ENGINE=InnoDB;
373
374 CREATE TABLE `VLANValidID` (
375 `vlan_id` int(10) unsigned NOT NULL default '1',
376 PRIMARY KEY (`vlan_id`)
377 ) ENGINE=InnoDB;
378
379 CREATE TABLE `VLANDescription` (
380 `domain_id` int(10) unsigned NOT NULL,
381 `vlan_id` int(10) unsigned NOT NULL default '0',
382 `vlan_type` enum('ondemand','compulsory','alien') NOT NULL default 'ondemand',
383 `vlan_descr` char(255) default NULL,
384 PRIMARY KEY (`domain_id`,`vlan_id`),
385 KEY `vlan_id` (`vlan_id`),
386 CONSTRAINT `VLANDescription-FK-domain_id` FOREIGN KEY (`domain_id`) REFERENCES `VLANDomain` (`id`),
387 CONSTRAINT `VLANDescription-FK-vlan_id` FOREIGN KEY (`vlan_id`) REFERENCES `VLANValidID` (`vlan_id`)
388 ) ENGINE=InnoDB;
389
390 CREATE TABLE `VLANIPv4` (
391 `domain_id` int(10) unsigned NOT NULL,
392 `vlan_id` int(10) unsigned NOT NULL,
393 `ipv4net_id` int(10) unsigned NOT NULL,
394 UNIQUE KEY `ipv4net_id` (`ipv4net_id`),
395 KEY `VLANIPv4-FK-compound` (`domain_id`,`vlan_id`),
396 CONSTRAINT `VLANIPv4-FK-compound` FOREIGN KEY (`domain_id`, `vlan_id`) REFERENCES `VLANDescription` (`domain_id`, `vlan_id`) ON DELETE CASCADE,
397 CONSTRAINT `VLANIPv4-FK-ipv4net_id` FOREIGN KEY (`ipv4net_id`) REFERENCES `IPv4Network` (`id`) ON DELETE CASCADE
398 ) ENGINE=InnoDB;
399
400 CREATE TABLE `VLANSwitch` (
401 `object_id` int(10) unsigned NOT NULL,
402 `domain_id` int(10) unsigned NOT NULL,
403 `last_reset` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
404 `last_pull` timestamp NOT NULL default '0000-00-00 00:00:00',
405 `last_push` timestamp NOT NULL default '0000-00-00 00:00:00',
406 UNIQUE KEY `object_id` (`object_id`),
407 KEY `domain_id` (`domain_id`),
408 CONSTRAINT `VLANSwitch--FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`),
409 CONSTRAINT `VLANSwitch-FK-domain_id` FOREIGN KEY (`domain_id`) REFERENCES `VLANDomain` (`id`)
410 ) ENGINE=InnoDB;
411
412 CREATE TABLE `PortAllowedVLAN` (
413 `port_id` int(10) unsigned NOT NULL default '0',
414 `vlan_id` int(10) unsigned NOT NULL default '0',
415 PRIMARY KEY (`port_id`,`vlan_id`),
416 KEY `vlan_id` (`vlan_id`),
417 CONSTRAINT `PortAllowedVLAN-FK-port_id` FOREIGN KEY (`port_id`) REFERENCES `Port` (`id`) ON DELETE CASCADE,
418 CONSTRAINT `PortAllowedVLAN-FK-vlan_id` FOREIGN KEY (`vlan_id`) REFERENCES `VLANValidID` (`vlan_id`)
419 ) ENGINE=InnoDB;
420
421 CREATE TABLE `PortNativeVLAN` (
422 `port_id` int(10) unsigned NOT NULL default '0',
423 `vlan_id` int(10) unsigned NOT NULL default '0',
424 PRIMARY KEY (`port_id`,`vlan_id`),
425 UNIQUE KEY `port_id` (`port_id`),
426 CONSTRAINT `PortNativeVLAN-FK-compound` FOREIGN KEY (`port_id`, `vlan_id`) REFERENCES `PortAllowedVLAN` (`port_id`, `vlan_id`) ON DELETE CASCADE
427 ) ENGINE=InnoDB;
428