r2666 - introduced LDAPCache table (ticket:193)
[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',
10bac82a 22 `chapter_id` int(10) unsigned NOT NULL,
e673ee24
DO
23 UNIQUE KEY `objtype_id` (`objtype_id`,`attr_id`)
24) ENGINE=MyISAM;
25
e673ee24
DO
26CREATE TABLE `AttributeValue` (
27 `object_id` int(10) unsigned default NULL,
28 `attr_id` int(10) unsigned default NULL,
c1682618 29 `string_value` char(128) default NULL,
e673ee24
DO
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
e673ee24 35CREATE TABLE `Chapter` (
10bac82a 36 `id` int(10) unsigned NOT NULL auto_increment,
e673ee24 37 `sticky` enum('yes','no') default 'no',
10bac82a
DY
38 `name` char(128) NOT NULL,
39 PRIMARY KEY (`id`),
40 UNIQUE KEY `name` (`name`)
e673ee24
DO
41) ENGINE=MyISAM AUTO_INCREMENT=10000;
42
42617841 43CREATE TABLE `Config` (
c1682618
DO
44 `varname` char(32) NOT NULL,
45 `varvalue` char(255) NOT NULL,
42617841
DO
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
e673ee24 53CREATE TABLE `Dictionary` (
10bac82a 54 `chapter_id` int(10) unsigned NOT NULL,
e673ee24 55 `dict_key` int(10) unsigned NOT NULL auto_increment,
c1682618 56 `dict_value` char(255) default NULL,
71dcbe12 57 PRIMARY KEY (`dict_key`),
10bac82a
DY
58 UNIQUE KEY `chap_to_key` (`chapter_id`,`dict_key`),
59 UNIQUE KEY `chap_to_val` (`chapter_id`,`dict_value`)
71dcbe12 60) ENGINE=MyISAM AUTO_INCREMENT=50000;
e673ee24 61
e1ae3fb4
AD
62CREATE 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,
13edfa1c
AD
72 PRIMARY KEY (`id`),
73 UNIQUE KEY `name` (`name`)
e1ae3fb4
AD
74) ENGINE=InnoDB;
75
76CREATE 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`),
9a44807e 83 UNIQUE KEY `FileLink-unique` (`file_id`,`entity_type`,`entity_id`),
e1ae3fb4
AD
84 CONSTRAINT `FileLink-File_fkey` FOREIGN KEY (`file_id`) REFERENCES `File` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
85) ENGINE=InnoDB;
86
706ce117 87CREATE TABLE `IPv4Address` (
e673ee24 88 `ip` int(10) unsigned NOT NULL,
c1682618 89 `name` char(255) NOT NULL,
e673ee24
DO
90 `reserved` enum('yes','no') default NULL,
91 PRIMARY KEY (`ip`)
92) ENGINE=MyISAM;
93
706ce117 94CREATE TABLE `IPv4Allocation` (
ba1c6d42 95 `object_id` int(10) unsigned NOT NULL,
e673ee24 96 `ip` int(10) unsigned NOT NULL,
c1682618 97 `name` char(255) NOT NULL,
52c836b1 98 `type` enum('regular','shared','virtual','router') default NULL,
e673ee24
DO
99 PRIMARY KEY (`object_id`,`ip`)
100) ENGINE=MyISAM;
101
706ce117 102CREATE TABLE `IPv4LB` (
192c95ce
DO
103 `object_id` int(10) unsigned default NULL,
104 `rspool_id` int(10) unsigned default NULL,
70c24883 105 `vs_id` int(10) unsigned default NULL,
8d350975
DO
106 `vsconfig` text,
107 `rsconfig` text,
70c24883 108 UNIQUE KEY `LB-VS` (`object_id`,`vs_id`)
192c95ce
DO
109) ENGINE=MyISAM;
110
706ce117 111CREATE TABLE `IPv4RSPool` (
192c95ce 112 `id` int(10) unsigned NOT NULL auto_increment,
c1682618 113 `name` char(255) default NULL,
192c95ce
DO
114 `vsconfig` text,
115 `rsconfig` text,
116 PRIMARY KEY (`id`)
117) ENGINE=MyISAM;
118
706ce117 119CREATE TABLE `IPv4Network` (
ba1c6d42 120 `id` int(10) unsigned NOT NULL auto_increment,
e673ee24 121 `ip` int(10) unsigned NOT NULL,
ba1c6d42 122 `mask` int(10) unsigned NOT NULL,
c1682618 123 `name` char(255) default NULL,
568079c3
DO
124 PRIMARY KEY (`id`),
125 UNIQUE KEY `base-len` (`ip`,`mask`)
e673ee24
DO
126) ENGINE=MyISAM;
127
706ce117 128CREATE TABLE `IPv4RS` (
192c95ce 129 `id` int(10) unsigned NOT NULL auto_increment,
1f7d18fa 130 `inservice` enum('yes','no') NOT NULL default 'no',
192c95ce
DO
131 `rsip` int(10) unsigned default NULL,
132 `rsport` smallint(5) unsigned default NULL,
133 `rspool_id` int(10) unsigned default NULL,
ab82514d 134 `rsconfig` text,
4ab15209 135 PRIMARY KEY (`id`),
70c24883 136 UNIQUE KEY `pool-endpoint` (`rspool_id`,`rsip`,`rsport`)
192c95ce
DO
137) ENGINE=MyISAM;
138
706ce117 139CREATE TABLE `IPv4VS` (
192c95ce
DO
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',
c1682618 144 `name` char(255) default NULL,
9c3043f3
DO
145 `vsconfig` text,
146 `rsconfig` text,
f85f4db0 147 PRIMARY KEY (`id`)
192c95ce
DO
148) ENGINE=MyISAM;
149
9133d2c5
DO
150CREATE 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
e673ee24 161CREATE TABLE `Link` (
167df29b
DO
162 `porta` int(10) unsigned NOT NULL,
163 `portb` int(10) unsigned NOT NULL,
e673ee24
DO
164 PRIMARY KEY (`porta`,`portb`),
165 UNIQUE KEY `porta` (`porta`),
166 UNIQUE KEY `portb` (`portb`)
167) ENGINE=MyISAM;
168
e673ee24
DO
169CREATE TABLE `Molecule` (
170 `id` int(10) unsigned NOT NULL auto_increment,
171 PRIMARY KEY (`id`)
172) ENGINE=MyISAM;
173
e673ee24
DO
174CREATE TABLE `MountOperation` (
175 `id` int(10) unsigned NOT NULL auto_increment,
176 `object_id` int(10) unsigned NOT NULL,
177 `ctime` timestamp NOT NULL,
c1682618 178 `user_name` char(64) default NULL,
e673ee24
DO
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
e673ee24 185CREATE TABLE `Port` (
167df29b
DO
186 `id` int(10) unsigned NOT NULL auto_increment,
187 `object_id` int(10) unsigned NOT NULL,
c1682618 188 `name` char(255) NOT NULL,
167df29b 189 `type` int(10) unsigned NOT NULL,
c1682618
DO
190 `l2address` char(64) default NULL,
191 `reservation_comment` char(255) default NULL,
192 `label` char(255) default NULL,
e673ee24
DO
193 PRIMARY KEY (`id`),
194 UNIQUE KEY `object_id` (`object_id`,`name`),
78f44cbc
DO
195 UNIQUE KEY `l2address` (`l2address`),
196 KEY `type` (`type`)
e673ee24
DO
197) ENGINE=MyISAM;
198
e673ee24 199CREATE TABLE `PortCompat` (
71dcbe12 200 `type1` int(10) unsigned NOT NULL,
78f44cbc
DO
201 `type2` int(10) unsigned NOT NULL,
202 KEY `type1` (`type1`),
203 KEY `type2` (`type2`)
e673ee24
DO
204) ENGINE=MyISAM;
205
706ce117 206CREATE TABLE `IPv4NAT` (
ba1c6d42 207 `object_id` int(10) unsigned NOT NULL,
eeb4a5d8 208 `proto` enum('TCP','UDP') not null default 'TCP',
e673ee24 209 `localip` int(10) unsigned NOT NULL,
ba1c6d42 210 `localport` smallint(5) unsigned NOT NULL,
e673ee24 211 `remoteip` int(10) unsigned NOT NULL,
ba1c6d42 212 `remoteport` smallint(5) unsigned NOT NULL,
c1682618 213 `description` char(255) default NULL,
e673ee24
DO
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
10bac82a
DY
220CREATE 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
e673ee24
DO
226CREATE TABLE `Rack` (
227 `id` int(10) unsigned NOT NULL auto_increment,
c1682618 228 `name` char(255) default NULL,
e673ee24
DO
229 `deleted` enum('yes','no') NOT NULL default 'no',
230 `row_id` int(10) unsigned NOT NULL default '1',
0a7136d4 231 `height` tinyint(3) unsigned NOT NULL default '42',
e673ee24 232 `comment` text,
86f94102 233 `thumb_data` blob,
3526c7c6
DO
234 PRIMARY KEY (`id`),
235 UNIQUE KEY `name_in_row` (`row_id`,`name`)
e673ee24
DO
236) ENGINE=MyISAM;
237
e673ee24
DO
238CREATE TABLE `RackHistory` (
239 `id` int(10) unsigned default NULL,
c1682618 240 `name` char(255) default NULL,
e673ee24
DO
241 `deleted` enum('yes','no') default NULL,
242 `row_id` int(10) unsigned default NULL,
f187f2ec 243 `height` tinyint(3) unsigned default NULL,
e673ee24 244 `comment` text,
f187f2ec 245 `thumb_data` blob,
e673ee24 246 `ctime` timestamp NOT NULL,
c1682618 247 `user_name` char(64) default NULL
e673ee24
DO
248) ENGINE=MyISAM;
249
e673ee24
DO
250CREATE TABLE `RackObject` (
251 `id` int(10) unsigned NOT NULL auto_increment,
c1682618
DO
252 `name` char(255) default NULL,
253 `label` char(255) default NULL,
254 `barcode` char(16) default NULL,
e673ee24
DO
255 `deleted` enum('yes','no') NOT NULL default 'no',
256 `objtype_id` int(10) unsigned NOT NULL default '1',
c1682618 257 `asset_no` char(64) default NULL,
e673ee24
DO
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
e673ee24
DO
266CREATE TABLE `RackObjectHistory` (
267 `id` int(10) unsigned default NULL,
c1682618
DO
268 `name` char(255) default NULL,
269 `label` char(255) default NULL,
270 `barcode` char(16) default NULL,
e673ee24
DO
271 `deleted` enum('yes','no') default NULL,
272 `objtype_id` int(10) unsigned default NULL,
c1682618 273 `asset_no` char(64) default NULL,
e673ee24
DO
274 `has_problems` enum('yes','no') NOT NULL default 'no',
275 `comment` text,
276 `ctime` timestamp NOT NULL,
c1682618 277 `user_name` char(64) default NULL
e673ee24
DO
278) ENGINE=MyISAM;
279
e673ee24
DO
280CREATE 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,
5d4fff58
DO
286 PRIMARY KEY (`rack_id`,`unit_no`,`atom`),
287 KEY `RackSpace_object_id` (`object_id`)
e673ee24
DO
288) ENGINE=MyISAM;
289
42617841 290CREATE TABLE `Script` (
c1682618 291 `script_name` char(64) NOT NULL,
4a6a28f1 292 `script_text` longtext,
42617841
DO
293 PRIMARY KEY (`script_name`)
294) TYPE=MyISAM;
295
f9bc186f 296CREATE TABLE `TagStorage` (
120e9ddd
DO
297 `entity_realm` enum('file','ipv4net','ipv4vs','ipv4rspool','object','rack','user') NOT NULL default 'object',
298 `entity_id` int(10) unsigned NOT NULL,
42617841 299 `tag_id` int(10) unsigned NOT NULL,
120e9ddd
DO
300 UNIQUE KEY `entity_tag` (`entity_realm`,`entity_id`,`tag_id`),
301 KEY `entity_id` (`entity_id`)
f9bc186f
DO
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
DO
309 PRIMARY KEY (`id`),
310 UNIQUE KEY `tag` (`tag`)
311) TYPE=MyISAM;
312
e673ee24
DO
313CREATE TABLE `UserAccount` (
314 `user_id` int(10) unsigned NOT NULL auto_increment,
c1682618 315 `user_name` char(64) NOT NULL,
c1682618
DO
316 `user_password_hash` char(128) default NULL,
317 `user_realname` char(64) default NULL,
e673ee24
DO
318 PRIMARY KEY (`user_id`),
319 UNIQUE KEY `user_name` (`user_name`)
320) ENGINE=MyISAM AUTO_INCREMENT=10000;