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