r3553 VLANEligibleOIF: dismiss
[racktables] / install / init-structure.sql
CommitLineData
48329bfb
DO
1alter database character set utf8;
2set names 'utf8';
dd21c414 3SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
48329bfb 4
e673ee24
DO
5CREATE 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
e673ee24 12CREATE TABLE `Attribute` (
10bac82a
DY
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`)
e673ee24
DO
18) ENGINE=MyISAM AUTO_INCREMENT=10000;
19
e673ee24
DO
20CREATE TABLE `AttributeMap` (
21 `objtype_id` int(10) unsigned NOT NULL default '1',
22 `attr_id` int(10) unsigned NOT NULL default '1',
7028a42c 23 `chapter_id` int(10) unsigned NULL,
e673ee24
DO
24 UNIQUE KEY `objtype_id` (`objtype_id`,`attr_id`)
25) ENGINE=MyISAM;
26
e673ee24
DO
27CREATE TABLE `AttributeValue` (
28 `object_id` int(10) unsigned default NULL,
29 `attr_id` int(10) unsigned default NULL,
c1682618 30 `string_value` char(128) default NULL,
e673ee24
DO
31 `uint_value` int(10) unsigned default NULL,
32 `float_value` float default NULL,
cafd4cf3
DO
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;
e673ee24 36
dd21c414
DO
37CREATE 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`),
a548fea8
DO
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`)
dd21c414
DO
45) ENGINE=InnoDB;
46
47CREATE 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
56CREATE 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
e673ee24 64CREATE TABLE `Chapter` (
10bac82a 65 `id` int(10) unsigned NOT NULL auto_increment,
e673ee24 66 `sticky` enum('yes','no') default 'no',
10bac82a
DY
67 `name` char(128) NOT NULL,
68 PRIMARY KEY (`id`),
69 UNIQUE KEY `name` (`name`)
e673ee24
DO
70) ENGINE=MyISAM AUTO_INCREMENT=10000;
71
42617841 72CREATE TABLE `Config` (
c1682618
DO
73 `varname` char(32) NOT NULL,
74 `varvalue` char(255) NOT NULL,
42617841
DO
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',
b67862c6 78 `is_userdefined` enum('yes','no') NOT NULL default 'no',
42617841
DO
79 `description` text,
80 PRIMARY KEY (`varname`)
81) ENGINE=MyISAM;
82
e673ee24 83CREATE TABLE `Dictionary` (
10bac82a 84 `chapter_id` int(10) unsigned NOT NULL,
e673ee24 85 `dict_key` int(10) unsigned NOT NULL auto_increment,
c1682618 86 `dict_value` char(255) default NULL,
71dcbe12 87 PRIMARY KEY (`dict_key`),
10bac82a 88 UNIQUE KEY `chap_to_val` (`chapter_id`,`dict_value`)
71dcbe12 89) ENGINE=MyISAM AUTO_INCREMENT=50000;
e673ee24 90
e1ae3fb4
AD
91CREATE 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,
13edfa1c
AD
101 PRIMARY KEY (`id`),
102 UNIQUE KEY `name` (`name`)
e1ae3fb4
AD
103) ENGINE=InnoDB;
104
105CREATE 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`),
9a44807e 112 UNIQUE KEY `FileLink-unique` (`file_id`,`entity_type`,`entity_id`),
e1ae3fb4
AD
113 CONSTRAINT `FileLink-File_fkey` FOREIGN KEY (`file_id`) REFERENCES `File` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
114) ENGINE=InnoDB;
115
706ce117 116CREATE TABLE `IPv4Address` (
e673ee24 117 `ip` int(10) unsigned NOT NULL,
c1682618 118 `name` char(255) NOT NULL,
e673ee24
DO
119 `reserved` enum('yes','no') default NULL,
120 PRIMARY KEY (`ip`)
121) ENGINE=MyISAM;
122
706ce117 123CREATE TABLE `IPv4Allocation` (
ba1c6d42 124 `object_id` int(10) unsigned NOT NULL,
e673ee24 125 `ip` int(10) unsigned NOT NULL,
c1682618 126 `name` char(255) NOT NULL,
52c836b1 127 `type` enum('regular','shared','virtual','router') default NULL,
e673ee24
DO
128 PRIMARY KEY (`object_id`,`ip`)
129) ENGINE=MyISAM;
130
cafd4cf3
DO
131CREATE 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
dd21c414
DO
145CREATE 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
706ce117 160CREATE TABLE `IPv4Network` (
ba1c6d42 161 `id` int(10) unsigned NOT NULL auto_increment,
e673ee24 162 `ip` int(10) unsigned NOT NULL,
ba1c6d42 163 `mask` int(10) unsigned NOT NULL,
c1682618 164 `name` char(255) default NULL,
99ab184f 165 `comment` text,
568079c3
DO
166 PRIMARY KEY (`id`),
167 UNIQUE KEY `base-len` (`ip`,`mask`)
1768cc35 168) ENGINE=InnoDB;
e673ee24 169
706ce117 170CREATE TABLE `IPv4RS` (
192c95ce 171 `id` int(10) unsigned NOT NULL auto_increment,
1f7d18fa 172 `inservice` enum('yes','no') NOT NULL default 'no',
192c95ce
DO
173 `rsip` int(10) unsigned default NULL,
174 `rsport` smallint(5) unsigned default NULL,
175 `rspool_id` int(10) unsigned default NULL,
ab82514d 176 `rsconfig` text,
4ab15209 177 PRIMARY KEY (`id`),
4d87feaf
DO
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;
192c95ce 181
dd21c414
DO
182CREATE 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
190CREATE 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
9133d2c5
DO
201CREATE 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
dd21c414
DO
212CREATE 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
e673ee24
DO
222CREATE TABLE `Molecule` (
223 `id` int(10) unsigned NOT NULL auto_increment,
224 PRIMARY KEY (`id`)
225) ENGINE=MyISAM;
226
e673ee24
DO
227CREATE TABLE `MountOperation` (
228 `id` int(10) unsigned NOT NULL auto_increment,
229 `object_id` int(10) unsigned NOT NULL,
230 `ctime` timestamp NOT NULL,
c1682618 231 `user_name` char(64) default NULL,
e673ee24
DO
232 `old_molecule_id` int(10) unsigned default NULL,
233 `new_molecule_id` int(10) unsigned default NULL,
234 `comment` text,
4368cc45
DO
235 PRIMARY KEY (`id`),
236 KEY `object_id` (`object_id`)
e673ee24
DO
237) ENGINE=MyISAM;
238
e673ee24 239CREATE TABLE `Port` (
167df29b
DO
240 `id` int(10) unsigned NOT NULL auto_increment,
241 `object_id` int(10) unsigned NOT NULL,
c1682618 242 `name` char(255) NOT NULL,
09d6afa4 243 `iif_id` int(10) unsigned NOT NULL,
167df29b 244 `type` int(10) unsigned NOT NULL,
c1682618
DO
245 `l2address` char(64) default NULL,
246 `reservation_comment` char(255) default NULL,
247 `label` char(255) default NULL,
e673ee24 248 PRIMARY KEY (`id`),
08aa3467 249 UNIQUE KEY `object_iif_oif_name` (`object_id`,`iif_id`,`type`,`name`),
5163cd3a 250 KEY `type` (`type`),
029a14bc 251 KEY `comment` (`reservation_comment`),
cafd4cf3 252 KEY `l2address` (`l2address`),
9d800849
DO
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`),
cafd4cf3 255 CONSTRAINT `Port-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`)
958ac06d 256) ENGINE=InnoDB;
e673ee24 257
dd21c414
DO
258CREATE 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`)
f000e1cd
DO
266) ENGINE=InnoDB;
267
e673ee24 268CREATE TABLE `PortCompat` (
71dcbe12 269 `type1` int(10) unsigned NOT NULL,
78f44cbc 270 `type2` int(10) unsigned NOT NULL,
2fb9d280 271 UNIQUE KEY `type1_2` (`type1`,`type2`),
78f44cbc 272 KEY `type2` (`type2`)
e673ee24
DO
273) ENGINE=MyISAM;
274
dd21c414
DO
275CREATE 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
282CREATE 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
289CREATE TABLE `PortNativeVLAN` (
ba1c6d42 290 `object_id` int(10) unsigned NOT NULL,
dd21c414
DO
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
cafd4cf3 296) ENGINE=InnoDB;
e673ee24 297
dd21c414
DO
298CREATE 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;
10bac82a 305
e673ee24
DO
306CREATE TABLE `Rack` (
307 `id` int(10) unsigned NOT NULL auto_increment,
c1682618 308 `name` char(255) default NULL,
e673ee24 309 `row_id` int(10) unsigned NOT NULL default '1',
0a7136d4 310 `height` tinyint(3) unsigned NOT NULL default '42',
e673ee24 311 `comment` text,
86f94102 312 `thumb_data` blob,
3526c7c6
DO
313 PRIMARY KEY (`id`),
314 UNIQUE KEY `name_in_row` (`row_id`,`name`)
e673ee24
DO
315) ENGINE=MyISAM;
316
e673ee24
DO
317CREATE TABLE `RackHistory` (
318 `id` int(10) unsigned default NULL,
c1682618 319 `name` char(255) default NULL,
e673ee24 320 `row_id` int(10) unsigned default NULL,
f187f2ec 321 `height` tinyint(3) unsigned default NULL,
e673ee24 322 `comment` text,
f187f2ec 323 `thumb_data` blob,
e673ee24 324 `ctime` timestamp NOT NULL,
c1682618 325 `user_name` char(64) default NULL
e673ee24
DO
326) ENGINE=MyISAM;
327
dd21c414
DO
328CREATE 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
e673ee24
DO
343CREATE TABLE `RackObjectHistory` (
344 `id` int(10) unsigned default NULL,
c1682618
DO
345 `name` char(255) default NULL,
346 `label` char(255) default NULL,
347 `barcode` char(16) default NULL,
e673ee24 348 `objtype_id` int(10) unsigned default NULL,
c1682618 349 `asset_no` char(64) default NULL,
e673ee24
DO
350 `has_problems` enum('yes','no') NOT NULL default 'no',
351 `comment` text,
352 `ctime` timestamp NOT NULL,
c1682618 353 `user_name` char(64) default NULL
e673ee24
DO
354) ENGINE=MyISAM;
355
dd21c414
DO
356CREATE 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
e673ee24
DO
362CREATE 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,
5d4fff58
DO
368 PRIMARY KEY (`rack_id`,`unit_no`,`atom`),
369 KEY `RackSpace_object_id` (`object_id`)
e673ee24
DO
370) ENGINE=MyISAM;
371
42617841 372CREATE TABLE `Script` (
c1682618 373 `script_name` char(64) NOT NULL,
4a6a28f1 374 `script_text` longtext,
42617841 375 PRIMARY KEY (`script_name`)
dd21c414 376) ENGINE=MyISAM;
9f572fb5
DO
377
378CREATE 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`)
dd21c414
DO
386) ENGINE=InnoDB;
387
388CREATE 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;
36516fe5 397
e673ee24
DO
398CREATE TABLE `UserAccount` (
399 `user_id` int(10) unsigned NOT NULL auto_increment,
c1682618 400 `user_name` char(64) NOT NULL,
3827da34 401 `user_password_hash` char(40) default NULL,
c1682618 402 `user_realname` char(64) default NULL,
e673ee24
DO
403 PRIMARY KEY (`user_id`),
404 UNIQUE KEY `user_name` (`user_name`)
405) ENGINE=MyISAM AUTO_INCREMENT=10000;
3540d15c
DY
406
407CREATE 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`)
75e2bc61
DO
412) ENGINE=InnoDB;
413
414CREATE TABLE `VLANDescription` (
415 `domain_id` int(10) unsigned NOT NULL,
416 `vlan_id` int(10) unsigned NOT NULL default '0',
0dabdc53 417 `vlan_type` enum('ondemand','compulsory','alien') NOT NULL default 'ondemand',
75e2bc61
DO
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
dd21c414
DO
425CREATE 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
8846b060
DO
432CREATE TABLE `VLANIPv4` (
433 `domain_id` int(10) unsigned NOT NULL,
434 `vlan_id` int(10) unsigned NOT NULL,
435 `ipv4net_id` int(10) unsigned NOT NULL,
a5f9d1ca 436 UNIQUE KEY `network-domain` (`ipv4net_id`,`domain_id`),
8846b060
DO
437 KEY `VLANIPv4-FK-compound` (`domain_id`,`vlan_id`),
438 CONSTRAINT `VLANIPv4-FK-compound` FOREIGN KEY (`domain_id`, `vlan_id`) REFERENCES `VLANDescription` (`domain_id`, `vlan_id`) ON DELETE CASCADE,
439 CONSTRAINT `VLANIPv4-FK-ipv4net_id` FOREIGN KEY (`ipv4net_id`) REFERENCES `IPv4Network` (`id`) ON DELETE CASCADE
440) ENGINE=InnoDB;
441
e0d188ef
DO
442CREATE TABLE `VLANSTRule` (
443 `vst_id` int(10) unsigned NOT NULL,
444 `rule_no` int(10) unsigned NOT NULL,
445 `port_pcre` char(255) NOT NULL,
f6d4f285 446 `port_role` enum('access','trunk','uplink','downlink') NOT NULL default 'access',
e0d188ef
DO
447 `wrt_vlans` char(255) default NULL,
448 UNIQUE KEY `vst-rule` (`vst_id`,`rule_no`),
449 CONSTRAINT `VLANSTRule-FK-vst_id` FOREIGN KEY (`vst_id`) REFERENCES `VLANSwitchTemplate` (`id`) ON DELETE CASCADE
450) ENGINE=InnoDB;
451
8198f2c6
DO
452CREATE TABLE `VLANSwitch` (
453 `object_id` int(10) unsigned NOT NULL,
454 `domain_id` int(10) unsigned NOT NULL,
e0d188ef 455 `template_id` int(10) unsigned NOT NULL,
ad32096e 456 `mutex_rev` int(10) unsigned NOT NULL default '0',
d973196a
DO
457 `out_of_sync` enum('yes','no') NOT NULL default 'yes',
458 `last_errno` int(10) unsigned NOT NULL default '0',
459 `last_change` timestamp NOT NULL default '0000-00-00 00:00:00',
460 `last_push_started` timestamp NOT NULL default '0000-00-00 00:00:00',
461 `last_push_finished` timestamp NOT NULL default '0000-00-00 00:00:00',
462 `last_error_ts` timestamp NOT NULL default '0000-00-00 00:00:00',
8198f2c6
DO
463 UNIQUE KEY `object_id` (`object_id`),
464 KEY `domain_id` (`domain_id`),
e0d188ef 465 KEY `template_id` (`template_id`),
d973196a
DO
466 KEY `out_of_sync` (`out_of_sync`),
467 KEY `last_errno` (`last_errno`),
468 CONSTRAINT `VLANSwitch-FK-domain_id` FOREIGN KEY (`domain_id`) REFERENCES `VLANDomain` (`id`),
9845c328 469 CONSTRAINT `VLANSwitch-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`),
d973196a 470 CONSTRAINT `VLANSwitch-FK-template_id` FOREIGN KEY (`template_id`) REFERENCES `VLANSwitchTemplate` (`id`)
8198f2c6
DO
471) ENGINE=InnoDB;
472
dd21c414
DO
473CREATE TABLE `VLANSwitchTemplate` (
474 `id` int(10) unsigned NOT NULL auto_increment,
475 `max_local_vlans` int(10) unsigned default NULL,
476 `description` char(255) default NULL,
477 PRIMARY KEY (`id`),
478 UNIQUE KEY `description` (`description`)
c506a67e
DO
479) ENGINE=InnoDB;
480
dd21c414
DO
481CREATE TABLE `VLANValidID` (
482 `vlan_id` int(10) unsigned NOT NULL default '1',
483 PRIMARY KEY (`vlan_id`)
75e2bc61
DO
484) ENGINE=InnoDB;
485
dd21c414 486SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
8198f2c6 487