r3064 - InnoDB: employ foreign keys in TagTree, TagStorage
[racktables] / install / init-structure.sql
CommitLineData
48329bfb
DO
1alter database character set utf8;
2set names 'utf8';
3
e673ee24
DO
4CREATE 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
e673ee24 11CREATE TABLE `Attribute` (
10bac82a
DY
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`)
e673ee24
DO
17) ENGINE=MyISAM AUTO_INCREMENT=10000;
18
e673ee24
DO
19CREATE TABLE `AttributeMap` (
20 `objtype_id` int(10) unsigned NOT NULL default '1',
21 `attr_id` int(10) unsigned NOT NULL default '1',
7028a42c 22 `chapter_id` int(10) unsigned NULL,
e673ee24
DO
23 UNIQUE KEY `objtype_id` (`objtype_id`,`attr_id`)
24) ENGINE=MyISAM;
25
cafd4cf3
DO
26CREATE 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
e673ee24
DO
41CREATE TABLE `AttributeValue` (
42 `object_id` int(10) unsigned default NULL,
43 `attr_id` int(10) unsigned default NULL,
c1682618 44 `string_value` char(128) default NULL,
e673ee24
DO
45 `uint_value` int(10) unsigned default NULL,
46 `float_value` float default NULL,
cafd4cf3
DO
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;
e673ee24 50
e673ee24 51CREATE TABLE `Chapter` (
10bac82a 52 `id` int(10) unsigned NOT NULL auto_increment,
e673ee24 53 `sticky` enum('yes','no') default 'no',
10bac82a
DY
54 `name` char(128) NOT NULL,
55 PRIMARY KEY (`id`),
56 UNIQUE KEY `name` (`name`)
e673ee24
DO
57) ENGINE=MyISAM AUTO_INCREMENT=10000;
58
42617841 59CREATE TABLE `Config` (
c1682618
DO
60 `varname` char(32) NOT NULL,
61 `varvalue` char(255) NOT NULL,
42617841
DO
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 `description` text,
66 PRIMARY KEY (`varname`)
67) ENGINE=MyISAM;
68
e673ee24 69CREATE TABLE `Dictionary` (
10bac82a 70 `chapter_id` int(10) unsigned NOT NULL,
e673ee24 71 `dict_key` int(10) unsigned NOT NULL auto_increment,
c1682618 72 `dict_value` char(255) default NULL,
71dcbe12 73 PRIMARY KEY (`dict_key`),
10bac82a 74 UNIQUE KEY `chap_to_val` (`chapter_id`,`dict_value`)
71dcbe12 75) ENGINE=MyISAM AUTO_INCREMENT=50000;
e673ee24 76
e1ae3fb4
AD
77CREATE TABLE `File` (
78 `id` int(10) unsigned NOT NULL auto_increment,
79 `name` char(255) NOT NULL,
80 `type` char(255) NOT NULL,
81 `size` int(10) unsigned NOT NULL,
82 `ctime` datetime NOT NULL,
83 `mtime` datetime NOT NULL,
84 `atime` datetime NOT NULL,
85 `contents` longblob NOT NULL,
86 `comment` text,
13edfa1c
AD
87 PRIMARY KEY (`id`),
88 UNIQUE KEY `name` (`name`)
e1ae3fb4
AD
89) ENGINE=InnoDB;
90
91CREATE TABLE `FileLink` (
92 `id` int(10) unsigned NOT NULL auto_increment,
93 `file_id` int(10) unsigned NOT NULL,
94 `entity_type` enum('ipv4net','ipv4rspool','ipv4vs','object','rack','user') NOT NULL default 'object',
95 `entity_id` int(10) NOT NULL,
96 PRIMARY KEY (`id`),
97 KEY `FileLink-file_id` (`file_id`),
9a44807e 98 UNIQUE KEY `FileLink-unique` (`file_id`,`entity_type`,`entity_id`),
e1ae3fb4
AD
99 CONSTRAINT `FileLink-File_fkey` FOREIGN KEY (`file_id`) REFERENCES `File` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
100) ENGINE=InnoDB;
101
706ce117 102CREATE TABLE `IPv4Address` (
e673ee24 103 `ip` int(10) unsigned NOT NULL,
c1682618 104 `name` char(255) NOT NULL,
e673ee24
DO
105 `reserved` enum('yes','no') default NULL,
106 PRIMARY KEY (`ip`)
107) ENGINE=MyISAM;
108
706ce117 109CREATE TABLE `IPv4Allocation` (
ba1c6d42 110 `object_id` int(10) unsigned NOT NULL,
e673ee24 111 `ip` int(10) unsigned NOT NULL,
c1682618 112 `name` char(255) NOT NULL,
52c836b1 113 `type` enum('regular','shared','virtual','router') default NULL,
e673ee24
DO
114 PRIMARY KEY (`object_id`,`ip`)
115) ENGINE=MyISAM;
116
cafd4cf3
DO
117CREATE TABLE `IPv4RSPool` (
118 `id` int(10) unsigned NOT NULL auto_increment,
119 `name` char(255) default NULL,
8d350975
DO
120 `vsconfig` text,
121 `rsconfig` text,
cafd4cf3
DO
122 PRIMARY KEY (`id`)
123) ENGINE=InnoDB;
192c95ce 124
cafd4cf3 125CREATE TABLE `IPv4VS` (
192c95ce 126 `id` int(10) unsigned NOT NULL auto_increment,
cafd4cf3
DO
127 `vip` int(10) unsigned default NULL,
128 `vport` smallint(5) unsigned default NULL,
129 `proto` enum('TCP','UDP') NOT NULL default 'TCP',
c1682618 130 `name` char(255) default NULL,
192c95ce
DO
131 `vsconfig` text,
132 `rsconfig` text,
133 PRIMARY KEY (`id`)
4d87feaf 134) ENGINE=InnoDB;
192c95ce 135
cafd4cf3
DO
136CREATE TABLE `IPv4LB` (
137 `object_id` int(10) unsigned default NULL,
138 `rspool_id` int(10) unsigned default NULL,
139 `vs_id` int(10) unsigned default NULL,
140 `vsconfig` text,
141 `rsconfig` text,
142 UNIQUE KEY `LB-VS` (`object_id`,`vs_id`),
143 KEY `IPv4LB-FK-rspool_id` (`rspool_id`),
144 KEY `IPv4LB-FK-vs_id` (`vs_id`),
145 CONSTRAINT `IPv4LB-FK-vs_id` FOREIGN KEY (`vs_id`) REFERENCES `IPv4VS` (`id`),
146 CONSTRAINT `IPv4LB-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`),
147 CONSTRAINT `IPv4LB-FK-rspool_id` FOREIGN KEY (`rspool_id`) REFERENCES `IPv4RSPool` (`id`)
148) ENGINE=InnoDB;
149
706ce117 150CREATE TABLE `IPv4Network` (
ba1c6d42 151 `id` int(10) unsigned NOT NULL auto_increment,
e673ee24 152 `ip` int(10) unsigned NOT NULL,
ba1c6d42 153 `mask` int(10) unsigned NOT NULL,
c1682618 154 `name` char(255) default NULL,
99ab184f 155 `comment` text,
568079c3
DO
156 PRIMARY KEY (`id`),
157 UNIQUE KEY `base-len` (`ip`,`mask`)
e673ee24
DO
158) ENGINE=MyISAM;
159
706ce117 160CREATE TABLE `IPv4RS` (
192c95ce 161 `id` int(10) unsigned NOT NULL auto_increment,
1f7d18fa 162 `inservice` enum('yes','no') NOT NULL default 'no',
192c95ce
DO
163 `rsip` int(10) unsigned default NULL,
164 `rsport` smallint(5) unsigned default NULL,
165 `rspool_id` int(10) unsigned default NULL,
ab82514d 166 `rsconfig` text,
4ab15209 167 PRIMARY KEY (`id`),
4d87feaf
DO
168 UNIQUE KEY `pool-endpoint` (`rspool_id`,`rsip`,`rsport`),
169 CONSTRAINT `IPv4RS-FK` FOREIGN KEY (`rspool_id`) REFERENCES `IPv4RSPool` (`id`) ON DELETE CASCADE
170) ENGINE=InnoDB;
192c95ce 171
9133d2c5
DO
172CREATE TABLE `LDAPCache` (
173 `presented_username` char(64) NOT NULL,
174 `successful_hash` char(40) NOT NULL,
175 `first_success` timestamp NOT NULL default CURRENT_TIMESTAMP,
176 `last_retry` timestamp NOT NULL default '0000-00-00 00:00:00',
177 `displayed_name` char(128) default NULL,
178 `memberof` text,
179 UNIQUE KEY `presented_username` (`presented_username`),
180 KEY `scanidx` (`presented_username`,`successful_hash`)
181) ENGINE=InnoDB;
182
e673ee24
DO
183CREATE TABLE `Molecule` (
184 `id` int(10) unsigned NOT NULL auto_increment,
185 PRIMARY KEY (`id`)
186) ENGINE=MyISAM;
187
e673ee24
DO
188CREATE TABLE `MountOperation` (
189 `id` int(10) unsigned NOT NULL auto_increment,
190 `object_id` int(10) unsigned NOT NULL,
191 `ctime` timestamp NOT NULL,
c1682618 192 `user_name` char(64) default NULL,
e673ee24
DO
193 `old_molecule_id` int(10) unsigned default NULL,
194 `new_molecule_id` int(10) unsigned default NULL,
195 `comment` text,
196 PRIMARY KEY (`id`)
197) ENGINE=MyISAM;
198
e673ee24 199CREATE TABLE `Port` (
167df29b
DO
200 `id` int(10) unsigned NOT NULL auto_increment,
201 `object_id` int(10) unsigned NOT NULL,
c1682618 202 `name` char(255) NOT NULL,
167df29b 203 `type` int(10) unsigned NOT NULL,
c1682618
DO
204 `l2address` char(64) default NULL,
205 `reservation_comment` char(255) default NULL,
206 `label` char(255) default NULL,
e673ee24 207 PRIMARY KEY (`id`),
948666cc 208 UNIQUE KEY `per_object` (`object_id`,`name`,`type`),
5163cd3a 209 KEY `type` (`type`),
029a14bc 210 KEY `comment` (`reservation_comment`),
cafd4cf3
DO
211 KEY `l2address` (`l2address`),
212 CONSTRAINT `Port-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`)
958ac06d 213) ENGINE=InnoDB;
e673ee24 214
f000e1cd
DO
215CREATE TABLE `Link` (
216 `porta` int(10) unsigned NOT NULL,
217 `portb` int(10) unsigned NOT NULL,
218 PRIMARY KEY (`porta`,`portb`),
219 UNIQUE KEY `porta` (`porta`),
220 UNIQUE KEY `portb` (`portb`),
221 CONSTRAINT `Link-FK-a` FOREIGN KEY (`porta`) REFERENCES `Port` (`id`),
222 CONSTRAINT `Link-FK-b` FOREIGN KEY (`portb`) REFERENCES `Port` (`id`)
223) ENGINE=InnoDB;
224
e673ee24 225CREATE TABLE `PortCompat` (
71dcbe12 226 `type1` int(10) unsigned NOT NULL,
78f44cbc 227 `type2` int(10) unsigned NOT NULL,
2fb9d280 228 UNIQUE KEY `type1_2` (`type1`,`type2`),
78f44cbc 229 KEY `type2` (`type2`)
e673ee24
DO
230) ENGINE=MyISAM;
231
706ce117 232CREATE TABLE `IPv4NAT` (
ba1c6d42 233 `object_id` int(10) unsigned NOT NULL,
eeb4a5d8 234 `proto` enum('TCP','UDP') not null default 'TCP',
e673ee24 235 `localip` int(10) unsigned NOT NULL,
ba1c6d42 236 `localport` smallint(5) unsigned NOT NULL,
e673ee24 237 `remoteip` int(10) unsigned NOT NULL,
ba1c6d42 238 `remoteport` smallint(5) unsigned NOT NULL,
c1682618 239 `description` char(255) default NULL,
e673ee24
DO
240 PRIMARY KEY (`object_id`,`proto`,`localip`,`localport`,`remoteip`,`remoteport`),
241 KEY `localip` (`localip`),
242 KEY `remoteip` (`remoteip`),
cafd4cf3
DO
243 KEY `object_id` (`object_id`),
244 CONSTRAINT `IPv4NAT-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`)
245) ENGINE=InnoDB;
e673ee24 246
10bac82a
DY
247CREATE TABLE `RackRow` (
248 `id` int(10) unsigned NOT NULL auto_increment,
249 `name` char(255) NOT NULL,
250 PRIMARY KEY (`id`)
251) ENGINE=MyISAM;
252
e673ee24
DO
253CREATE TABLE `Rack` (
254 `id` int(10) unsigned NOT NULL auto_increment,
c1682618 255 `name` char(255) default NULL,
e673ee24 256 `row_id` int(10) unsigned NOT NULL default '1',
0a7136d4 257 `height` tinyint(3) unsigned NOT NULL default '42',
e673ee24 258 `comment` text,
86f94102 259 `thumb_data` blob,
3526c7c6
DO
260 PRIMARY KEY (`id`),
261 UNIQUE KEY `name_in_row` (`row_id`,`name`)
e673ee24
DO
262) ENGINE=MyISAM;
263
e673ee24
DO
264CREATE TABLE `RackHistory` (
265 `id` int(10) unsigned default NULL,
c1682618 266 `name` char(255) default NULL,
e673ee24 267 `row_id` int(10) unsigned default NULL,
f187f2ec 268 `height` tinyint(3) unsigned default NULL,
e673ee24 269 `comment` text,
f187f2ec 270 `thumb_data` blob,
e673ee24 271 `ctime` timestamp NOT NULL,
c1682618 272 `user_name` char(64) default NULL
e673ee24
DO
273) ENGINE=MyISAM;
274
e673ee24
DO
275CREATE TABLE `RackObjectHistory` (
276 `id` int(10) unsigned default NULL,
c1682618
DO
277 `name` char(255) default NULL,
278 `label` char(255) default NULL,
279 `barcode` char(16) default NULL,
e673ee24 280 `objtype_id` int(10) unsigned default NULL,
c1682618 281 `asset_no` char(64) default NULL,
e673ee24
DO
282 `has_problems` enum('yes','no') NOT NULL default 'no',
283 `comment` text,
284 `ctime` timestamp NOT NULL,
c1682618 285 `user_name` char(64) default NULL
e673ee24
DO
286) ENGINE=MyISAM;
287
e673ee24
DO
288CREATE TABLE `RackSpace` (
289 `rack_id` int(10) unsigned NOT NULL default '0',
290 `unit_no` int(10) unsigned NOT NULL default '0',
291 `atom` enum('front','interior','rear') NOT NULL default 'interior',
292 `state` enum('A','U','T','W') NOT NULL default 'A',
293 `object_id` int(10) unsigned default NULL,
5d4fff58
DO
294 PRIMARY KEY (`rack_id`,`unit_no`,`atom`),
295 KEY `RackSpace_object_id` (`object_id`)
e673ee24
DO
296) ENGINE=MyISAM;
297
42617841 298CREATE TABLE `Script` (
c1682618 299 `script_name` char(64) NOT NULL,
4a6a28f1 300 `script_text` longtext,
42617841
DO
301 PRIMARY KEY (`script_name`)
302) TYPE=MyISAM;
303
36516fe5
DO
304CREATE TABLE `TagTree` (
305 `id` int(10) unsigned NOT NULL auto_increment,
306 `parent_id` int(10) unsigned default NULL,
dbb33805 307 `valid_realm` set('file','ipv4net','ipv4vs','ipv4rspool','object','rack','user') NOT NULL default 'file,ipv4net,ipv4vs,ipv4rspool,object,rack,user',
c1682618 308 `tag` char(255) default NULL,
36516fe5 309 PRIMARY KEY (`id`),
9f572fb5
DO
310 UNIQUE KEY `tag` (`tag`),
311 KEY `TagTree-K-parent_id` (`parent_id`),
312 CONSTRAINT `TagTree-K-parent_id` FOREIGN KEY (`parent_id`) REFERENCES `TagTree` (`id`)
313) TYPE=MyISAM;
314
315CREATE TABLE `TagStorage` (
316 `entity_realm` enum('file','ipv4net','ipv4vs','ipv4rspool','object','rack','user') NOT NULL default 'object',
317 `entity_id` int(10) unsigned NOT NULL,
318 `tag_id` int(10) unsigned NOT NULL,
319 UNIQUE KEY `entity_tag` (`entity_realm`,`entity_id`,`tag_id`),
320 KEY `entity_id` (`entity_id`),
321 KEY `TagStorage-FK-tag_id` (`tag_id`),
322 CONSTRAINT `TagStorage-FK-tag_id` FOREIGN KEY (`tag_id`) REFERENCES `TagTree` (`id`)
36516fe5
DO
323) TYPE=MyISAM;
324
e673ee24
DO
325CREATE TABLE `UserAccount` (
326 `user_id` int(10) unsigned NOT NULL auto_increment,
c1682618 327 `user_name` char(64) NOT NULL,
3827da34 328 `user_password_hash` char(40) default NULL,
c1682618 329 `user_realname` char(64) default NULL,
e673ee24
DO
330 PRIMARY KEY (`user_id`),
331 UNIQUE KEY `user_name` (`user_name`)
332) ENGINE=MyISAM AUTO_INCREMENT=10000;