r2707 - make PortCompat pairs unique
[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 NOT NULL,
23 UNIQUE KEY `objtype_id` (`objtype_id`,`attr_id`)
24 ) ENGINE=MyISAM;
25
26 CREATE TABLE `AttributeValue` (
27 `object_id` int(10) unsigned default NULL,
28 `attr_id` int(10) unsigned default NULL,
29 `string_value` char(128) default NULL,
30 `uint_value` int(10) unsigned default NULL,
31 `float_value` float default NULL,
32 UNIQUE KEY `object_id` (`object_id`,`attr_id`)
33 ) ENGINE=MyISAM;
34
35 CREATE TABLE `Chapter` (
36 `id` int(10) unsigned NOT NULL auto_increment,
37 `sticky` enum('yes','no') default 'no',
38 `name` char(128) NOT NULL,
39 PRIMARY KEY (`id`),
40 UNIQUE KEY `name` (`name`)
41 ) ENGINE=MyISAM AUTO_INCREMENT=10000;
42
43 CREATE TABLE `Config` (
44 `varname` char(32) NOT NULL,
45 `varvalue` char(255) NOT NULL,
46 `vartype` enum('string','uint') NOT NULL default 'string',
47 `emptyok` enum('yes','no') NOT NULL default 'no',
48 `is_hidden` enum('yes','no') NOT NULL default 'yes',
49 `description` text,
50 PRIMARY KEY (`varname`)
51 ) ENGINE=MyISAM;
52
53 CREATE TABLE `Dictionary` (
54 `chapter_id` int(10) unsigned NOT NULL,
55 `dict_key` int(10) unsigned NOT NULL auto_increment,
56 `dict_value` char(255) default NULL,
57 PRIMARY KEY (`dict_key`),
58 UNIQUE KEY `chap_to_key` (`chapter_id`,`dict_key`),
59 UNIQUE KEY `chap_to_val` (`chapter_id`,`dict_value`)
60 ) ENGINE=MyISAM AUTO_INCREMENT=50000;
61
62 CREATE TABLE `File` (
63 `id` int(10) unsigned NOT NULL auto_increment,
64 `name` char(255) NOT NULL,
65 `type` char(255) NOT NULL,
66 `size` int(10) unsigned NOT NULL,
67 `ctime` datetime NOT NULL,
68 `mtime` datetime NOT NULL,
69 `atime` datetime NOT NULL,
70 `contents` longblob NOT NULL,
71 `comment` text,
72 PRIMARY KEY (`id`),
73 UNIQUE KEY `name` (`name`)
74 ) ENGINE=InnoDB;
75
76 CREATE TABLE `FileLink` (
77 `id` int(10) unsigned NOT NULL auto_increment,
78 `file_id` int(10) unsigned NOT NULL,
79 `entity_type` enum('ipv4net','ipv4rspool','ipv4vs','object','rack','user') NOT NULL default 'object',
80 `entity_id` int(10) NOT NULL,
81 PRIMARY KEY (`id`),
82 KEY `FileLink-file_id` (`file_id`),
83 UNIQUE KEY `FileLink-unique` (`file_id`,`entity_type`,`entity_id`),
84 CONSTRAINT `FileLink-File_fkey` FOREIGN KEY (`file_id`) REFERENCES `File` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
85 ) ENGINE=InnoDB;
86
87 CREATE TABLE `IPv4Address` (
88 `ip` int(10) unsigned NOT NULL,
89 `name` char(255) NOT NULL,
90 `reserved` enum('yes','no') default NULL,
91 PRIMARY KEY (`ip`)
92 ) ENGINE=MyISAM;
93
94 CREATE TABLE `IPv4Allocation` (
95 `object_id` int(10) unsigned NOT NULL,
96 `ip` int(10) unsigned NOT NULL,
97 `name` char(255) NOT NULL,
98 `type` enum('regular','shared','virtual','router') default NULL,
99 PRIMARY KEY (`object_id`,`ip`)
100 ) ENGINE=MyISAM;
101
102 CREATE TABLE `IPv4LB` (
103 `object_id` int(10) unsigned default NULL,
104 `rspool_id` int(10) unsigned default NULL,
105 `vs_id` int(10) unsigned default NULL,
106 `vsconfig` text,
107 `rsconfig` text,
108 UNIQUE KEY `LB-VS` (`object_id`,`vs_id`)
109 ) ENGINE=MyISAM;
110
111 CREATE TABLE `IPv4RSPool` (
112 `id` int(10) unsigned NOT NULL auto_increment,
113 `name` char(255) default NULL,
114 `vsconfig` text,
115 `rsconfig` text,
116 PRIMARY KEY (`id`)
117 ) ENGINE=MyISAM;
118
119 CREATE TABLE `IPv4Network` (
120 `id` int(10) unsigned NOT NULL auto_increment,
121 `ip` int(10) unsigned NOT NULL,
122 `mask` int(10) unsigned NOT NULL,
123 `name` char(255) default NULL,
124 PRIMARY KEY (`id`),
125 UNIQUE KEY `base-len` (`ip`,`mask`)
126 ) ENGINE=MyISAM;
127
128 CREATE TABLE `IPv4RS` (
129 `id` int(10) unsigned NOT NULL auto_increment,
130 `inservice` enum('yes','no') NOT NULL default 'no',
131 `rsip` int(10) unsigned default NULL,
132 `rsport` smallint(5) unsigned default NULL,
133 `rspool_id` int(10) unsigned default NULL,
134 `rsconfig` text,
135 PRIMARY KEY (`id`),
136 UNIQUE KEY `pool-endpoint` (`rspool_id`,`rsip`,`rsport`)
137 ) ENGINE=MyISAM;
138
139 CREATE TABLE `IPv4VS` (
140 `id` int(10) unsigned NOT NULL auto_increment,
141 `vip` int(10) unsigned default NULL,
142 `vport` smallint(5) unsigned default NULL,
143 `proto` enum('TCP','UDP') NOT NULL default 'TCP',
144 `name` char(255) default NULL,
145 `vsconfig` text,
146 `rsconfig` text,
147 PRIMARY KEY (`id`)
148 ) ENGINE=MyISAM;
149
150 CREATE TABLE `LDAPCache` (
151 `presented_username` char(64) NOT NULL,
152 `successful_hash` char(40) NOT NULL,
153 `first_success` timestamp NOT NULL default CURRENT_TIMESTAMP,
154 `last_retry` timestamp NOT NULL default '0000-00-00 00:00:00',
155 `displayed_name` char(128) default NULL,
156 `memberof` text,
157 UNIQUE KEY `presented_username` (`presented_username`),
158 KEY `scanidx` (`presented_username`,`successful_hash`)
159 ) ENGINE=InnoDB;
160
161 CREATE TABLE `Link` (
162 `porta` int(10) unsigned NOT NULL,
163 `portb` int(10) unsigned NOT NULL,
164 PRIMARY KEY (`porta`,`portb`),
165 UNIQUE KEY `porta` (`porta`),
166 UNIQUE KEY `portb` (`portb`)
167 ) ENGINE=MyISAM;
168
169 CREATE TABLE `Molecule` (
170 `id` int(10) unsigned NOT NULL auto_increment,
171 PRIMARY KEY (`id`)
172 ) ENGINE=MyISAM;
173
174 CREATE TABLE `MountOperation` (
175 `id` int(10) unsigned NOT NULL auto_increment,
176 `object_id` int(10) unsigned NOT NULL,
177 `ctime` timestamp NOT NULL,
178 `user_name` char(64) default NULL,
179 `old_molecule_id` int(10) unsigned default NULL,
180 `new_molecule_id` int(10) unsigned default NULL,
181 `comment` text,
182 PRIMARY KEY (`id`)
183 ) ENGINE=MyISAM;
184
185 CREATE TABLE `Port` (
186 `id` int(10) unsigned NOT NULL auto_increment,
187 `object_id` int(10) unsigned NOT NULL,
188 `name` char(255) NOT NULL,
189 `type` int(10) unsigned NOT NULL,
190 `l2address` char(64) default NULL,
191 `reservation_comment` char(255) default NULL,
192 `label` char(255) default NULL,
193 PRIMARY KEY (`id`),
194 UNIQUE KEY `object_id` (`object_id`,`name`),
195 UNIQUE KEY `l2address` (`l2address`),
196 KEY `type` (`type`)
197 ) ENGINE=MyISAM;
198
199 CREATE TABLE `PortCompat` (
200 `type1` int(10) unsigned NOT NULL,
201 `type2` int(10) unsigned NOT NULL,
202 UNIQUE KEY `type1_2` (`type1`,`type2`),
203 KEY `type2` (`type2`)
204 ) ENGINE=MyISAM;
205
206 CREATE TABLE `IPv4NAT` (
207 `object_id` int(10) unsigned NOT NULL,
208 `proto` enum('TCP','UDP') not null default 'TCP',
209 `localip` int(10) unsigned NOT NULL,
210 `localport` smallint(5) unsigned NOT NULL,
211 `remoteip` int(10) unsigned NOT NULL,
212 `remoteport` smallint(5) unsigned NOT NULL,
213 `description` char(255) default NULL,
214 PRIMARY KEY (`object_id`,`proto`,`localip`,`localport`,`remoteip`,`remoteport`),
215 KEY `localip` (`localip`),
216 KEY `remoteip` (`remoteip`),
217 KEY `object_id` (`object_id`)
218 ) ENGINE=MyISAM;
219
220 CREATE TABLE `RackRow` (
221 `id` int(10) unsigned NOT NULL auto_increment,
222 `name` char(255) NOT NULL,
223 PRIMARY KEY (`id`)
224 ) ENGINE=MyISAM;
225
226 CREATE TABLE `Rack` (
227 `id` int(10) unsigned NOT NULL auto_increment,
228 `name` char(255) default NULL,
229 `row_id` int(10) unsigned NOT NULL default '1',
230 `height` tinyint(3) unsigned NOT NULL default '42',
231 `comment` text,
232 `thumb_data` blob,
233 PRIMARY KEY (`id`),
234 UNIQUE KEY `name_in_row` (`row_id`,`name`)
235 ) ENGINE=MyISAM;
236
237 CREATE TABLE `RackHistory` (
238 `id` int(10) unsigned default NULL,
239 `name` char(255) default NULL,
240 `row_id` int(10) unsigned default NULL,
241 `height` tinyint(3) unsigned default NULL,
242 `comment` text,
243 `thumb_data` blob,
244 `ctime` timestamp NOT NULL,
245 `user_name` char(64) default NULL
246 ) ENGINE=MyISAM;
247
248 CREATE TABLE `RackObject` (
249 `id` int(10) unsigned NOT NULL auto_increment,
250 `name` char(255) default NULL,
251 `label` char(255) default NULL,
252 `barcode` char(16) default NULL,
253 `objtype_id` int(10) unsigned NOT NULL default '1',
254 `asset_no` char(64) default NULL,
255 `has_problems` enum('yes','no') NOT NULL default 'no',
256 `comment` text,
257 PRIMARY KEY (`id`),
258 UNIQUE KEY `RackObject_asset_no` (`asset_no`),
259 UNIQUE KEY `name` (`name`),
260 UNIQUE KEY `barcode` (`barcode`)
261 ) ENGINE=MyISAM;
262
263 CREATE TABLE `RackObjectHistory` (
264 `id` int(10) unsigned default NULL,
265 `name` char(255) default NULL,
266 `label` char(255) default NULL,
267 `barcode` char(16) default NULL,
268 `objtype_id` int(10) unsigned default NULL,
269 `asset_no` char(64) default NULL,
270 `has_problems` enum('yes','no') NOT NULL default 'no',
271 `comment` text,
272 `ctime` timestamp NOT NULL,
273 `user_name` char(64) default NULL
274 ) ENGINE=MyISAM;
275
276 CREATE TABLE `RackSpace` (
277 `rack_id` int(10) unsigned NOT NULL default '0',
278 `unit_no` int(10) unsigned NOT NULL default '0',
279 `atom` enum('front','interior','rear') NOT NULL default 'interior',
280 `state` enum('A','U','T','W') NOT NULL default 'A',
281 `object_id` int(10) unsigned default NULL,
282 PRIMARY KEY (`rack_id`,`unit_no`,`atom`),
283 KEY `RackSpace_object_id` (`object_id`)
284 ) ENGINE=MyISAM;
285
286 CREATE TABLE `Script` (
287 `script_name` char(64) NOT NULL,
288 `script_text` longtext,
289 PRIMARY KEY (`script_name`)
290 ) TYPE=MyISAM;
291
292 CREATE TABLE `TagStorage` (
293 `entity_realm` enum('file','ipv4net','ipv4vs','ipv4rspool','object','rack','user') NOT NULL default 'object',
294 `entity_id` int(10) unsigned NOT NULL,
295 `tag_id` int(10) unsigned NOT NULL,
296 UNIQUE KEY `entity_tag` (`entity_realm`,`entity_id`,`tag_id`),
297 KEY `entity_id` (`entity_id`)
298 ) TYPE=MyISAM;
299
300 CREATE TABLE `TagTree` (
301 `id` int(10) unsigned NOT NULL auto_increment,
302 `parent_id` int(10) unsigned default NULL,
303 `valid_realm` set('file','ipv4net','ipv4vs','ipv4rspool','object','rack','user') NOT NULL default 'file,ipv4net,ipv4vs,ipv4rspool,object,rack,user',
304 `tag` char(255) default NULL,
305 PRIMARY KEY (`id`),
306 UNIQUE KEY `tag` (`tag`)
307 ) TYPE=MyISAM;
308
309 CREATE TABLE `UserAccount` (
310 `user_id` int(10) unsigned NOT NULL auto_increment,
311 `user_name` char(64) NOT NULL,
312 `user_password_hash` char(40) default NULL,
313 `user_realname` char(64) default NULL,
314 PRIMARY KEY (`user_id`),
315 UNIQUE KEY `user_name` (`user_name`)
316 ) ENGINE=MyISAM AUTO_INCREMENT=10000;