1cfbd95f9b75162772851b15d462ed11d7d88d78
[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=MyISAM;
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 NULL,
24 UNIQUE KEY `objtype_id` (`objtype_id`,`attr_id`)
25 ) ENGINE=MyISAM;
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`)
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=MyISAM;
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,
118 `name` char(255) NOT NULL,
119 `reserved` enum('yes','no') default NULL,
120 PRIMARY KEY (`ip`)
121 ) ENGINE=MyISAM;
122
123 CREATE TABLE `IPv4Allocation` (
124 `object_id` int(10) unsigned NOT NULL,
125 `ip` int(10) unsigned NOT NULL,
126 `name` char(255) NOT NULL,
127 `type` enum('regular','shared','virtual','router') default NULL,
128 PRIMARY KEY (`object_id`,`ip`)
129 ) ENGINE=MyISAM;
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,
147 `proto` enum('TCP','UDP') not null default 'TCP',
148 `localip` int(10) unsigned NOT NULL,
149 `localport` smallint(5) unsigned NOT NULL,
150 `remoteip` int(10) unsigned NOT NULL,
151 `remoteport` smallint(5) unsigned NOT NULL,
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,
163 `mask` int(10) unsigned NOT NULL,
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,
214 `portb` int(10) unsigned NOT NULL,
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`),
219 CONSTRAINT `Link-FK-b` FOREIGN KEY (`portb`) REFERENCES `Port` (`id`)
220 ) ENGINE=InnoDB;
221
222 CREATE TABLE `Molecule` (
223 `id` int(10) unsigned NOT NULL auto_increment,
224 PRIMARY KEY (`id`)
225 ) ENGINE=MyISAM;
226
227 CREATE TABLE `MountOperation` (
228 `id` int(10) unsigned NOT NULL auto_increment,
229 `object_id` int(10) unsigned NOT NULL,
230 `ctime` timestamp NOT NULL,
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 ) ENGINE=MyISAM;
238
239 CREATE TABLE `Port` (
240 `id` int(10) unsigned NOT NULL auto_increment,
241 `object_id` int(10) unsigned NOT NULL,
242 `name` char(255) NOT NULL,
243 `iif_id` int(10) unsigned NOT NULL,
244 `type` int(10) unsigned NOT NULL,
245 `l2address` char(64) default NULL,
246 `reservation_comment` char(255) default NULL,
247 `label` char(255) default NULL,
248 PRIMARY KEY (`id`),
249 UNIQUE KEY `object_iif_oif_name` (`object_id`,`iif_id`,`type`,`name`),
250 KEY `type` (`type`),
251 KEY `comment` (`reservation_comment`),
252 KEY `l2address` (`l2address`),
253 KEY `Port-FK-iif-oif` (`iif_id`,`type`),
254 CONSTRAINT `Port-FK-iif-oif` FOREIGN KEY (`iif_id`, `type`) REFERENCES `PortInterfaceCompat` (`iif_id`, `oif_id`),
255 CONSTRAINT `Port-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`)
256 ) ENGINE=InnoDB;
257
258 CREATE TABLE `PortAllowedVLAN` (
259 `object_id` int(10) unsigned NOT NULL,
260 `port_name` char(255) NOT NULL,
261 `vlan_id` int(10) unsigned NOT NULL default '0',
262 PRIMARY KEY (`object_id`,`port_name`,`vlan_id`),
263 KEY `vlan_id` (`vlan_id`),
264 CONSTRAINT `PortAllowedVLAN-FK-object-port` FOREIGN KEY (`object_id`, `port_name`) REFERENCES `PortVLANMode` (`object_id`, `port_name`) ON DELETE CASCADE,
265 CONSTRAINT `PortAllowedVLAN-FK-vlan_id` FOREIGN KEY (`vlan_id`) REFERENCES `VLANValidID` (`vlan_id`)
266 ) ENGINE=InnoDB;
267
268 CREATE TABLE `PortCompat` (
269 `type1` int(10) unsigned NOT NULL,
270 `type2` int(10) unsigned NOT NULL,
271 UNIQUE KEY `type1_2` (`type1`,`type2`),
272 KEY `type2` (`type2`)
273 ) ENGINE=MyISAM;
274
275 CREATE TABLE `PortInnerInterface` (
276 `id` int(10) unsigned NOT NULL,
277 `iif_name` char(16) NOT NULL,
278 PRIMARY KEY (`id`),
279 UNIQUE KEY `iif_name` (`iif_name`)
280 ) ENGINE=InnoDB;
281
282 CREATE TABLE `PortInterfaceCompat` (
283 `iif_id` int(10) unsigned NOT NULL,
284 `oif_id` int(10) unsigned NOT NULL,
285 UNIQUE KEY `pair` (`iif_id`,`oif_id`),
286 CONSTRAINT `PortInterfaceCompat-FK-iif_id` FOREIGN KEY (`iif_id`) REFERENCES `PortInnerInterface` (`id`)
287 ) ENGINE=InnoDB;
288
289 CREATE TABLE `PortNativeVLAN` (
290 `object_id` int(10) unsigned NOT NULL,
291 `port_name` char(255) NOT NULL,
292 `vlan_id` int(10) unsigned NOT NULL default '0',
293 PRIMARY KEY (`object_id`,`port_name`,`vlan_id`),
294 UNIQUE KEY `port_id` (`object_id`,`port_name`),
295 CONSTRAINT `PortNativeVLAN-FK-compound` FOREIGN KEY (`object_id`, `port_name`, `vlan_id`) REFERENCES `PortAllowedVLAN` (`object_id`, `port_name`, `vlan_id`) ON DELETE CASCADE
296 ) ENGINE=InnoDB;
297
298 CREATE TABLE `PortVLANMode` (
299 `object_id` int(10) unsigned NOT NULL,
300 `port_name` char(255) NOT NULL,
301 `vlan_mode` enum('access','trunk') NOT NULL default 'access',
302 PRIMARY KEY (`object_id`,`port_name`),
303 CONSTRAINT `PortVLANMode-FK-object-port` FOREIGN KEY (`object_id`, `port_name`) REFERENCES `CachedPVM` (`object_id`, `port_name`)
304 ) ENGINE=InnoDB;
305
306 CREATE TABLE `Rack` (
307 `id` int(10) unsigned NOT NULL auto_increment,
308 `name` char(255) default NULL,
309 `row_id` int(10) unsigned NOT NULL default '1',
310 `height` tinyint(3) unsigned NOT NULL default '42',
311 `comment` text,
312 `thumb_data` blob,
313 PRIMARY KEY (`id`),
314 UNIQUE KEY `name_in_row` (`row_id`,`name`)
315 ) ENGINE=MyISAM;
316
317 CREATE TABLE `RackHistory` (
318 `id` int(10) unsigned default NULL,
319 `name` char(255) default NULL,
320 `row_id` int(10) unsigned default NULL,
321 `height` tinyint(3) unsigned default NULL,
322 `comment` text,
323 `thumb_data` blob,
324 `ctime` timestamp NOT NULL,
325 `user_name` char(64) default NULL
326 ) ENGINE=MyISAM;
327
328 CREATE TABLE `RackObject` (
329 `id` int(10) unsigned NOT NULL auto_increment,
330 `name` char(255) default NULL,
331 `label` char(255) default NULL,
332 `barcode` char(16) default NULL,
333 `objtype_id` int(10) unsigned NOT NULL default '1',
334 `asset_no` char(64) default NULL,
335 `has_problems` enum('yes','no') NOT NULL default 'no',
336 `comment` text,
337 PRIMARY KEY (`id`),
338 UNIQUE KEY `RackObject_asset_no` (`asset_no`),
339 UNIQUE KEY `name` (`name`),
340 UNIQUE KEY `barcode` (`barcode`)
341 ) ENGINE=InnoDB;
342
343 CREATE TABLE `RackObjectHistory` (
344 `id` int(10) unsigned default NULL,
345 `name` char(255) default NULL,
346 `label` char(255) default NULL,
347 `barcode` char(16) default NULL,
348 `objtype_id` int(10) unsigned default NULL,
349 `asset_no` char(64) default NULL,
350 `has_problems` enum('yes','no') NOT NULL default 'no',
351 `comment` text,
352 `ctime` timestamp NOT NULL,
353 `user_name` char(64) default NULL
354 ) ENGINE=MyISAM;
355
356 CREATE TABLE `RackRow` (
357 `id` int(10) unsigned NOT NULL auto_increment,
358 `name` char(255) NOT NULL,
359 PRIMARY KEY (`id`)
360 ) ENGINE=MyISAM;
361
362 CREATE TABLE `RackSpace` (
363 `rack_id` int(10) unsigned NOT NULL default '0',
364 `unit_no` int(10) unsigned NOT NULL default '0',
365 `atom` enum('front','interior','rear') NOT NULL default 'interior',
366 `state` enum('A','U','T','W') NOT NULL default 'A',
367 `object_id` int(10) unsigned default NULL,
368 PRIMARY KEY (`rack_id`,`unit_no`,`atom`),
369 KEY `RackSpace_object_id` (`object_id`)
370 ) ENGINE=MyISAM;
371
372 CREATE TABLE `Script` (
373 `script_name` char(64) NOT NULL,
374 `script_text` longtext,
375 PRIMARY KEY (`script_name`)
376 ) ENGINE=MyISAM;
377
378 CREATE TABLE `TagStorage` (
379 `entity_realm` enum('file','ipv4net','ipv4vs','ipv4rspool','object','rack','user') NOT NULL default 'object',
380 `entity_id` int(10) unsigned NOT NULL,
381 `tag_id` int(10) unsigned NOT NULL,
382 UNIQUE KEY `entity_tag` (`entity_realm`,`entity_id`,`tag_id`),
383 KEY `entity_id` (`entity_id`),
384 KEY `TagStorage-FK-tag_id` (`tag_id`),
385 CONSTRAINT `TagStorage-FK-tag_id` FOREIGN KEY (`tag_id`) REFERENCES `TagTree` (`id`)
386 ) ENGINE=InnoDB;
387
388 CREATE TABLE `TagTree` (
389 `id` int(10) unsigned NOT NULL auto_increment,
390 `parent_id` int(10) unsigned default NULL,
391 `tag` char(255) default NULL,
392 PRIMARY KEY (`id`),
393 UNIQUE KEY `tag` (`tag`),
394 KEY `TagTree-K-parent_id` (`parent_id`),
395 CONSTRAINT `TagTree-K-parent_id` FOREIGN KEY (`parent_id`) REFERENCES `TagTree` (`id`)
396 ) ENGINE=InnoDB;
397
398 CREATE TABLE `UserAccount` (
399 `user_id` int(10) unsigned NOT NULL auto_increment,
400 `user_name` char(64) NOT NULL,
401 `user_password_hash` char(40) default NULL,
402 `user_realname` char(64) default NULL,
403 PRIMARY KEY (`user_id`),
404 UNIQUE KEY `user_name` (`user_name`)
405 ) ENGINE=MyISAM AUTO_INCREMENT=10000;
406
407 CREATE TABLE `UserConfig` (
408 `varname` char(32) NOT NULL,
409 `varvalue` char(255) NOT NULL,
410 `user` char(64) NOT NULL,
411 UNIQUE KEY `user_varname` (`user`,`varname`)
412 ) ENGINE=InnoDB;
413
414 CREATE TABLE `VLANDescription` (
415 `domain_id` int(10) unsigned NOT NULL,
416 `vlan_id` int(10) unsigned NOT NULL default '0',
417 `vlan_type` enum('ondemand','compulsory','alien') NOT NULL default 'ondemand',
418 `vlan_descr` char(255) default NULL,
419 PRIMARY KEY (`domain_id`,`vlan_id`),
420 KEY `vlan_id` (`vlan_id`),
421 CONSTRAINT `VLANDescription-FK-domain_id` FOREIGN KEY (`domain_id`) REFERENCES `VLANDomain` (`id`),
422 CONSTRAINT `VLANDescription-FK-vlan_id` FOREIGN KEY (`vlan_id`) REFERENCES `VLANValidID` (`vlan_id`)
423 ) ENGINE=InnoDB;
424
425 CREATE TABLE `VLANDomain` (
426 `id` int(10) unsigned NOT NULL auto_increment,
427 `description` char(255) default NULL,
428 PRIMARY KEY (`id`),
429 UNIQUE KEY `description` (`description`)
430 ) ENGINE=InnoDB;
431
432 CREATE TABLE `VLANEligibleOIF` (
433 `oif_id` int(10) unsigned NOT NULL default '0',
434 PRIMARY KEY (`oif_id`)
435 ) ENGINE=InnoDB;
436
437 CREATE TABLE `VLANIPv4` (
438 `domain_id` int(10) unsigned NOT NULL,
439 `vlan_id` int(10) unsigned NOT NULL,
440 `ipv4net_id` int(10) unsigned NOT NULL,
441 UNIQUE KEY `network-domain` (`ipv4net_id`,`domain_id`),
442 KEY `VLANIPv4-FK-compound` (`domain_id`,`vlan_id`),
443 CONSTRAINT `VLANIPv4-FK-compound` FOREIGN KEY (`domain_id`, `vlan_id`) REFERENCES `VLANDescription` (`domain_id`, `vlan_id`) ON DELETE CASCADE,
444 CONSTRAINT `VLANIPv4-FK-ipv4net_id` FOREIGN KEY (`ipv4net_id`) REFERENCES `IPv4Network` (`id`) ON DELETE CASCADE
445 ) ENGINE=InnoDB;
446
447 CREATE TABLE `VLANSTRule` (
448 `vst_id` int(10) unsigned NOT NULL,
449 `rule_no` int(10) unsigned NOT NULL,
450 `port_pcre` char(255) NOT NULL,
451 `port_role` enum('access','trunk','uplink','downlink') NOT NULL default 'access',
452 `wrt_vlans` char(255) default NULL,
453 UNIQUE KEY `vst-rule` (`vst_id`,`rule_no`),
454 CONSTRAINT `VLANSTRule-FK-vst_id` FOREIGN KEY (`vst_id`) REFERENCES `VLANSwitchTemplate` (`id`) ON DELETE CASCADE
455 ) ENGINE=InnoDB;
456
457 CREATE TABLE `VLANSwitch` (
458 `object_id` int(10) unsigned NOT NULL,
459 `domain_id` int(10) unsigned NOT NULL,
460 `template_id` int(10) unsigned NOT NULL,
461 `mutex_rev` int(10) unsigned NOT NULL default '0',
462 `out_of_sync` enum('yes','no') NOT NULL default 'yes',
463 `last_errno` int(10) unsigned NOT NULL default '0',
464 `last_change` timestamp NOT NULL default '0000-00-00 00:00:00',
465 `last_push_started` timestamp NOT NULL default '0000-00-00 00:00:00',
466 `last_push_finished` timestamp NOT NULL default '0000-00-00 00:00:00',
467 `last_error_ts` timestamp NOT NULL default '0000-00-00 00:00:00',
468 UNIQUE KEY `object_id` (`object_id`),
469 KEY `domain_id` (`domain_id`),
470 KEY `template_id` (`template_id`),
471 KEY `out_of_sync` (`out_of_sync`),
472 KEY `last_errno` (`last_errno`),
473 CONSTRAINT `VLANSwitch-FK-domain_id` FOREIGN KEY (`domain_id`) REFERENCES `VLANDomain` (`id`),
474 CONSTRAINT `VLANSwitch-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`),
475 CONSTRAINT `VLANSwitch-FK-template_id` FOREIGN KEY (`template_id`) REFERENCES `VLANSwitchTemplate` (`id`)
476 ) ENGINE=InnoDB;
477
478 CREATE TABLE `VLANSwitchTemplate` (
479 `id` int(10) unsigned NOT NULL auto_increment,
480 `max_local_vlans` int(10) unsigned default NULL,
481 `description` char(255) default NULL,
482 PRIMARY KEY (`id`),
483 UNIQUE KEY `description` (`description`)
484 ) ENGINE=InnoDB;
485
486 CREATE TABLE `VLANValidID` (
487 `vlan_id` int(10) unsigned NOT NULL default '1',
488 PRIMARY KEY (`vlan_id`)
489 ) ENGINE=InnoDB;
490
491 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
492