r2666 - introduced LDAPCache table (ticket:193)
[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 KEY `type1` (`type1`),
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 `deleted` enum('yes','no') NOT NULL default 'no',
230 `row_id` int(10) unsigned NOT NULL default '1',
231 `height` tinyint(3) unsigned NOT NULL default '42',
232 `comment` text,
233 `thumb_data` blob,
234 PRIMARY KEY (`id`),
235 UNIQUE KEY `name_in_row` (`row_id`,`name`)
236 ) ENGINE=MyISAM;
237
238 CREATE TABLE `RackHistory` (
239 `id` int(10) unsigned default NULL,
240 `name` char(255) default NULL,
241 `deleted` enum('yes','no') default NULL,
242 `row_id` int(10) unsigned default NULL,
243 `height` tinyint(3) unsigned default NULL,
244 `comment` text,
245 `thumb_data` blob,
246 `ctime` timestamp NOT NULL,
247 `user_name` char(64) default NULL
248 ) ENGINE=MyISAM;
249
250 CREATE TABLE `RackObject` (
251 `id` int(10) unsigned NOT NULL auto_increment,
252 `name` char(255) default NULL,
253 `label` char(255) default NULL,
254 `barcode` char(16) default NULL,
255 `deleted` enum('yes','no') NOT NULL default 'no',
256 `objtype_id` int(10) unsigned NOT NULL default '1',
257 `asset_no` char(64) default NULL,
258 `has_problems` enum('yes','no') NOT NULL default 'no',
259 `comment` text,
260 PRIMARY KEY (`id`),
261 UNIQUE KEY `RackObject_asset_no` (`asset_no`),
262 UNIQUE KEY `name` (`name`),
263 UNIQUE KEY `barcode` (`barcode`)
264 ) ENGINE=MyISAM;
265
266 CREATE TABLE `RackObjectHistory` (
267 `id` int(10) unsigned default NULL,
268 `name` char(255) default NULL,
269 `label` char(255) default NULL,
270 `barcode` char(16) default NULL,
271 `deleted` enum('yes','no') default NULL,
272 `objtype_id` int(10) unsigned default NULL,
273 `asset_no` char(64) default NULL,
274 `has_problems` enum('yes','no') NOT NULL default 'no',
275 `comment` text,
276 `ctime` timestamp NOT NULL,
277 `user_name` char(64) default NULL
278 ) ENGINE=MyISAM;
279
280 CREATE TABLE `RackSpace` (
281 `rack_id` int(10) unsigned NOT NULL default '0',
282 `unit_no` int(10) unsigned NOT NULL default '0',
283 `atom` enum('front','interior','rear') NOT NULL default 'interior',
284 `state` enum('A','U','T','W') NOT NULL default 'A',
285 `object_id` int(10) unsigned default NULL,
286 PRIMARY KEY (`rack_id`,`unit_no`,`atom`),
287 KEY `RackSpace_object_id` (`object_id`)
288 ) ENGINE=MyISAM;
289
290 CREATE TABLE `Script` (
291 `script_name` char(64) NOT NULL,
292 `script_text` longtext,
293 PRIMARY KEY (`script_name`)
294 ) TYPE=MyISAM;
295
296 CREATE TABLE `TagStorage` (
297 `entity_realm` enum('file','ipv4net','ipv4vs','ipv4rspool','object','rack','user') NOT NULL default 'object',
298 `entity_id` int(10) unsigned NOT NULL,
299 `tag_id` int(10) unsigned NOT NULL,
300 UNIQUE KEY `entity_tag` (`entity_realm`,`entity_id`,`tag_id`),
301 KEY `entity_id` (`entity_id`)
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 ) TYPE=MyISAM;
312
313 CREATE TABLE `UserAccount` (
314 `user_id` int(10) unsigned NOT NULL auto_increment,
315 `user_name` char(64) NOT NULL,
316 `user_password_hash` char(128) default NULL,
317 `user_realname` char(64) default NULL,
318 PRIMARY KEY (`user_id`),
319 UNIQUE KEY `user_name` (`user_name`)
320 ) ENGINE=MyISAM AUTO_INCREMENT=10000;