r3064 - InnoDB: employ foreign keys in TagTree, TagStorage
[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 `description` text,
66 PRIMARY KEY (`varname`)
67 ) ENGINE=MyISAM;
68
69 CREATE TABLE `Dictionary` (
70 `chapter_id` int(10) unsigned NOT NULL,
71 `dict_key` int(10) unsigned NOT NULL auto_increment,
72 `dict_value` char(255) default NULL,
73 PRIMARY KEY (`dict_key`),
74 UNIQUE KEY `chap_to_val` (`chapter_id`,`dict_value`)
75 ) ENGINE=MyISAM AUTO_INCREMENT=50000;
76
77 CREATE 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,
87 PRIMARY KEY (`id`),
88 UNIQUE KEY `name` (`name`)
89 ) ENGINE=InnoDB;
90
91 CREATE 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`),
98 UNIQUE KEY `FileLink-unique` (`file_id`,`entity_type`,`entity_id`),
99 CONSTRAINT `FileLink-File_fkey` FOREIGN KEY (`file_id`) REFERENCES `File` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
100 ) ENGINE=InnoDB;
101
102 CREATE TABLE `IPv4Address` (
103 `ip` int(10) unsigned NOT NULL,
104 `name` char(255) NOT NULL,
105 `reserved` enum('yes','no') default NULL,
106 PRIMARY KEY (`ip`)
107 ) ENGINE=MyISAM;
108
109 CREATE TABLE `IPv4Allocation` (
110 `object_id` int(10) unsigned NOT NULL,
111 `ip` int(10) unsigned NOT NULL,
112 `name` char(255) NOT NULL,
113 `type` enum('regular','shared','virtual','router') default NULL,
114 PRIMARY KEY (`object_id`,`ip`)
115 ) ENGINE=MyISAM;
116
117 CREATE TABLE `IPv4RSPool` (
118 `id` int(10) unsigned NOT NULL auto_increment,
119 `name` char(255) default NULL,
120 `vsconfig` text,
121 `rsconfig` text,
122 PRIMARY KEY (`id`)
123 ) ENGINE=InnoDB;
124
125 CREATE TABLE `IPv4VS` (
126 `id` int(10) unsigned NOT NULL auto_increment,
127 `vip` int(10) unsigned default NULL,
128 `vport` smallint(5) unsigned default NULL,
129 `proto` enum('TCP','UDP') NOT NULL default 'TCP',
130 `name` char(255) default NULL,
131 `vsconfig` text,
132 `rsconfig` text,
133 PRIMARY KEY (`id`)
134 ) ENGINE=InnoDB;
135
136 CREATE 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
150 CREATE TABLE `IPv4Network` (
151 `id` int(10) unsigned NOT NULL auto_increment,
152 `ip` int(10) unsigned NOT NULL,
153 `mask` int(10) unsigned NOT NULL,
154 `name` char(255) default NULL,
155 `comment` text,
156 PRIMARY KEY (`id`),
157 UNIQUE KEY `base-len` (`ip`,`mask`)
158 ) ENGINE=MyISAM;
159
160 CREATE TABLE `IPv4RS` (
161 `id` int(10) unsigned NOT NULL auto_increment,
162 `inservice` enum('yes','no') NOT NULL default 'no',
163 `rsip` int(10) unsigned default NULL,
164 `rsport` smallint(5) unsigned default NULL,
165 `rspool_id` int(10) unsigned default NULL,
166 `rsconfig` text,
167 PRIMARY KEY (`id`),
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;
171
172 CREATE 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
183 CREATE TABLE `Molecule` (
184 `id` int(10) unsigned NOT NULL auto_increment,
185 PRIMARY KEY (`id`)
186 ) ENGINE=MyISAM;
187
188 CREATE TABLE `MountOperation` (
189 `id` int(10) unsigned NOT NULL auto_increment,
190 `object_id` int(10) unsigned NOT NULL,
191 `ctime` timestamp NOT NULL,
192 `user_name` char(64) default NULL,
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
199 CREATE TABLE `Port` (
200 `id` int(10) unsigned NOT NULL auto_increment,
201 `object_id` int(10) unsigned NOT NULL,
202 `name` char(255) NOT NULL,
203 `type` int(10) unsigned NOT NULL,
204 `l2address` char(64) default NULL,
205 `reservation_comment` char(255) default NULL,
206 `label` char(255) default NULL,
207 PRIMARY KEY (`id`),
208 UNIQUE KEY `per_object` (`object_id`,`name`,`type`),
209 KEY `type` (`type`),
210 KEY `comment` (`reservation_comment`),
211 KEY `l2address` (`l2address`),
212 CONSTRAINT `Port-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`)
213 ) ENGINE=InnoDB;
214
215 CREATE 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
225 CREATE TABLE `PortCompat` (
226 `type1` int(10) unsigned NOT NULL,
227 `type2` int(10) unsigned NOT NULL,
228 UNIQUE KEY `type1_2` (`type1`,`type2`),
229 KEY `type2` (`type2`)
230 ) ENGINE=MyISAM;
231
232 CREATE TABLE `IPv4NAT` (
233 `object_id` int(10) unsigned NOT NULL,
234 `proto` enum('TCP','UDP') not null default 'TCP',
235 `localip` int(10) unsigned NOT NULL,
236 `localport` smallint(5) unsigned NOT NULL,
237 `remoteip` int(10) unsigned NOT NULL,
238 `remoteport` smallint(5) unsigned NOT NULL,
239 `description` char(255) default NULL,
240 PRIMARY KEY (`object_id`,`proto`,`localip`,`localport`,`remoteip`,`remoteport`),
241 KEY `localip` (`localip`),
242 KEY `remoteip` (`remoteip`),
243 KEY `object_id` (`object_id`),
244 CONSTRAINT `IPv4NAT-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`)
245 ) ENGINE=InnoDB;
246
247 CREATE 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
253 CREATE TABLE `Rack` (
254 `id` int(10) unsigned NOT NULL auto_increment,
255 `name` char(255) default NULL,
256 `row_id` int(10) unsigned NOT NULL default '1',
257 `height` tinyint(3) unsigned NOT NULL default '42',
258 `comment` text,
259 `thumb_data` blob,
260 PRIMARY KEY (`id`),
261 UNIQUE KEY `name_in_row` (`row_id`,`name`)
262 ) ENGINE=MyISAM;
263
264 CREATE TABLE `RackHistory` (
265 `id` int(10) unsigned default NULL,
266 `name` char(255) default NULL,
267 `row_id` int(10) unsigned default NULL,
268 `height` tinyint(3) unsigned default NULL,
269 `comment` text,
270 `thumb_data` blob,
271 `ctime` timestamp NOT NULL,
272 `user_name` char(64) default NULL
273 ) ENGINE=MyISAM;
274
275 CREATE TABLE `RackObjectHistory` (
276 `id` int(10) unsigned default NULL,
277 `name` char(255) default NULL,
278 `label` char(255) default NULL,
279 `barcode` char(16) default NULL,
280 `objtype_id` int(10) unsigned default NULL,
281 `asset_no` char(64) default NULL,
282 `has_problems` enum('yes','no') NOT NULL default 'no',
283 `comment` text,
284 `ctime` timestamp NOT NULL,
285 `user_name` char(64) default NULL
286 ) ENGINE=MyISAM;
287
288 CREATE 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,
294 PRIMARY KEY (`rack_id`,`unit_no`,`atom`),
295 KEY `RackSpace_object_id` (`object_id`)
296 ) ENGINE=MyISAM;
297
298 CREATE TABLE `Script` (
299 `script_name` char(64) NOT NULL,
300 `script_text` longtext,
301 PRIMARY KEY (`script_name`)
302 ) TYPE=MyISAM;
303
304 CREATE TABLE `TagTree` (
305 `id` int(10) unsigned NOT NULL auto_increment,
306 `parent_id` int(10) unsigned default NULL,
307 `valid_realm` set('file','ipv4net','ipv4vs','ipv4rspool','object','rack','user') NOT NULL default 'file,ipv4net,ipv4vs,ipv4rspool,object,rack,user',
308 `tag` char(255) default NULL,
309 PRIMARY KEY (`id`),
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
315 CREATE 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`)
323 ) TYPE=MyISAM;
324
325 CREATE TABLE `UserAccount` (
326 `user_id` int(10) unsigned NOT NULL auto_increment,
327 `user_name` char(64) NOT NULL,
328 `user_password_hash` char(40) default NULL,
329 `user_realname` char(64) default NULL,
330 PRIMARY KEY (`user_id`),
331 UNIQUE KEY `user_name` (`user_name`)
332 ) ENGINE=MyISAM AUTO_INCREMENT=10000;