r2952 - update: same port name can be repeated for an object with different types
[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 58 UNIQUE KEY `chap_to_val` (`chapter_id`,`dict_value`)
71dcbe12 59) ENGINE=MyISAM AUTO_INCREMENT=50000;
e673ee24 60
e1ae3fb4
AD
61CREATE TABLE `File` (
62 `id` int(10) unsigned NOT NULL auto_increment,
63 `name` char(255) NOT NULL,
64 `type` char(255) NOT NULL,
65 `size` int(10) unsigned NOT NULL,
66 `ctime` datetime NOT NULL,
67 `mtime` datetime NOT NULL,
68 `atime` datetime NOT NULL,
69 `contents` longblob NOT NULL,
70 `comment` text,
13edfa1c
AD
71 PRIMARY KEY (`id`),
72 UNIQUE KEY `name` (`name`)
e1ae3fb4
AD
73) ENGINE=InnoDB;
74
75CREATE TABLE `FileLink` (
76 `id` int(10) unsigned NOT NULL auto_increment,
77 `file_id` int(10) unsigned NOT NULL,
78 `entity_type` enum('ipv4net','ipv4rspool','ipv4vs','object','rack','user') NOT NULL default 'object',
79 `entity_id` int(10) NOT NULL,
80 PRIMARY KEY (`id`),
81 KEY `FileLink-file_id` (`file_id`),
9a44807e 82 UNIQUE KEY `FileLink-unique` (`file_id`,`entity_type`,`entity_id`),
e1ae3fb4
AD
83 CONSTRAINT `FileLink-File_fkey` FOREIGN KEY (`file_id`) REFERENCES `File` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
84) ENGINE=InnoDB;
85
706ce117 86CREATE TABLE `IPv4Address` (
e673ee24 87 `ip` int(10) unsigned NOT NULL,
c1682618 88 `name` char(255) NOT NULL,
e673ee24
DO
89 `reserved` enum('yes','no') default NULL,
90 PRIMARY KEY (`ip`)
91) ENGINE=MyISAM;
92
706ce117 93CREATE TABLE `IPv4Allocation` (
ba1c6d42 94 `object_id` int(10) unsigned NOT NULL,
e673ee24 95 `ip` int(10) unsigned NOT NULL,
c1682618 96 `name` char(255) NOT NULL,
52c836b1 97 `type` enum('regular','shared','virtual','router') default NULL,
e673ee24
DO
98 PRIMARY KEY (`object_id`,`ip`)
99) ENGINE=MyISAM;
100
706ce117 101CREATE TABLE `IPv4LB` (
192c95ce
DO
102 `object_id` int(10) unsigned default NULL,
103 `rspool_id` int(10) unsigned default NULL,
70c24883 104 `vs_id` int(10) unsigned default NULL,
8d350975
DO
105 `vsconfig` text,
106 `rsconfig` text,
70c24883 107 UNIQUE KEY `LB-VS` (`object_id`,`vs_id`)
192c95ce
DO
108) ENGINE=MyISAM;
109
706ce117 110CREATE TABLE `IPv4RSPool` (
192c95ce 111 `id` int(10) unsigned NOT NULL auto_increment,
c1682618 112 `name` char(255) default NULL,
192c95ce
DO
113 `vsconfig` text,
114 `rsconfig` text,
115 PRIMARY KEY (`id`)
116) ENGINE=MyISAM;
117
706ce117 118CREATE TABLE `IPv4Network` (
ba1c6d42 119 `id` int(10) unsigned NOT NULL auto_increment,
e673ee24 120 `ip` int(10) unsigned NOT NULL,
ba1c6d42 121 `mask` int(10) unsigned NOT NULL,
c1682618 122 `name` char(255) default NULL,
568079c3
DO
123 PRIMARY KEY (`id`),
124 UNIQUE KEY `base-len` (`ip`,`mask`)
e673ee24
DO
125) ENGINE=MyISAM;
126
706ce117 127CREATE TABLE `IPv4RS` (
192c95ce 128 `id` int(10) unsigned NOT NULL auto_increment,
1f7d18fa 129 `inservice` enum('yes','no') NOT NULL default 'no',
192c95ce
DO
130 `rsip` int(10) unsigned default NULL,
131 `rsport` smallint(5) unsigned default NULL,
132 `rspool_id` int(10) unsigned default NULL,
ab82514d 133 `rsconfig` text,
4ab15209 134 PRIMARY KEY (`id`),
70c24883 135 UNIQUE KEY `pool-endpoint` (`rspool_id`,`rsip`,`rsport`)
192c95ce
DO
136) ENGINE=MyISAM;
137
706ce117 138CREATE TABLE `IPv4VS` (
192c95ce
DO
139 `id` int(10) unsigned NOT NULL auto_increment,
140 `vip` int(10) unsigned default NULL,
141 `vport` smallint(5) unsigned default NULL,
142 `proto` enum('TCP','UDP') NOT NULL default 'TCP',
c1682618 143 `name` char(255) default NULL,
9c3043f3
DO
144 `vsconfig` text,
145 `rsconfig` text,
f85f4db0 146 PRIMARY KEY (`id`)
192c95ce
DO
147) ENGINE=MyISAM;
148
9133d2c5
DO
149CREATE TABLE `LDAPCache` (
150 `presented_username` char(64) NOT NULL,
151 `successful_hash` char(40) NOT NULL,
152 `first_success` timestamp NOT NULL default CURRENT_TIMESTAMP,
153 `last_retry` timestamp NOT NULL default '0000-00-00 00:00:00',
154 `displayed_name` char(128) default NULL,
155 `memberof` text,
156 UNIQUE KEY `presented_username` (`presented_username`),
157 KEY `scanidx` (`presented_username`,`successful_hash`)
158) ENGINE=InnoDB;
159
e673ee24 160CREATE TABLE `Link` (
167df29b
DO
161 `porta` int(10) unsigned NOT NULL,
162 `portb` int(10) unsigned NOT NULL,
e673ee24
DO
163 PRIMARY KEY (`porta`,`portb`),
164 UNIQUE KEY `porta` (`porta`),
165 UNIQUE KEY `portb` (`portb`)
166) ENGINE=MyISAM;
167
e673ee24
DO
168CREATE TABLE `Molecule` (
169 `id` int(10) unsigned NOT NULL auto_increment,
170 PRIMARY KEY (`id`)
171) ENGINE=MyISAM;
172
e673ee24
DO
173CREATE TABLE `MountOperation` (
174 `id` int(10) unsigned NOT NULL auto_increment,
175 `object_id` int(10) unsigned NOT NULL,
176 `ctime` timestamp NOT NULL,
c1682618 177 `user_name` char(64) default NULL,
e673ee24
DO
178 `old_molecule_id` int(10) unsigned default NULL,
179 `new_molecule_id` int(10) unsigned default NULL,
180 `comment` text,
181 PRIMARY KEY (`id`)
182) ENGINE=MyISAM;
183
e673ee24 184CREATE TABLE `Port` (
167df29b
DO
185 `id` int(10) unsigned NOT NULL auto_increment,
186 `object_id` int(10) unsigned NOT NULL,
c1682618 187 `name` char(255) NOT NULL,
167df29b 188 `type` int(10) unsigned NOT NULL,
c1682618
DO
189 `l2address` char(64) default NULL,
190 `reservation_comment` char(255) default NULL,
191 `label` char(255) default NULL,
e673ee24 192 PRIMARY KEY (`id`),
948666cc 193 UNIQUE KEY `per_object` (`object_id`,`name`,`type`),
5163cd3a 194 KEY `type` (`type`),
029a14bc
DO
195 KEY `comment` (`reservation_comment`),
196 KEY `l2address` (`l2address`)
e673ee24
DO
197) ENGINE=MyISAM;
198
e673ee24 199CREATE TABLE `PortCompat` (
71dcbe12 200 `type1` int(10) unsigned NOT NULL,
78f44cbc 201 `type2` int(10) unsigned NOT NULL,
2fb9d280 202 UNIQUE KEY `type1_2` (`type1`,`type2`),
78f44cbc 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 229 `row_id` int(10) unsigned NOT NULL default '1',
0a7136d4 230 `height` tinyint(3) unsigned NOT NULL default '42',
e673ee24 231 `comment` text,
86f94102 232 `thumb_data` blob,
3526c7c6
DO
233 PRIMARY KEY (`id`),
234 UNIQUE KEY `name_in_row` (`row_id`,`name`)
e673ee24
DO
235) ENGINE=MyISAM;
236
e673ee24
DO
237CREATE TABLE `RackHistory` (
238 `id` int(10) unsigned default NULL,
c1682618 239 `name` char(255) default NULL,
e673ee24 240 `row_id` int(10) unsigned default NULL,
f187f2ec 241 `height` tinyint(3) unsigned default NULL,
e673ee24 242 `comment` text,
f187f2ec 243 `thumb_data` blob,
e673ee24 244 `ctime` timestamp NOT NULL,
c1682618 245 `user_name` char(64) default NULL
e673ee24
DO
246) ENGINE=MyISAM;
247
e673ee24
DO
248CREATE TABLE `RackObject` (
249 `id` int(10) unsigned NOT NULL auto_increment,
c1682618
DO
250 `name` char(255) default NULL,
251 `label` char(255) default NULL,
252 `barcode` char(16) default NULL,
e673ee24 253 `objtype_id` int(10) unsigned NOT NULL default '1',
c1682618 254 `asset_no` char(64) default NULL,
e673ee24
DO
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
e673ee24
DO
263CREATE TABLE `RackObjectHistory` (
264 `id` int(10) unsigned default NULL,
c1682618
DO
265 `name` char(255) default NULL,
266 `label` char(255) default NULL,
267 `barcode` char(16) default NULL,
e673ee24 268 `objtype_id` int(10) unsigned default NULL,
c1682618 269 `asset_no` char(64) default NULL,
e673ee24
DO
270 `has_problems` enum('yes','no') NOT NULL default 'no',
271 `comment` text,
272 `ctime` timestamp NOT NULL,
c1682618 273 `user_name` char(64) default NULL
e673ee24
DO
274) ENGINE=MyISAM;
275
e673ee24
DO
276CREATE 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,
5d4fff58
DO
282 PRIMARY KEY (`rack_id`,`unit_no`,`atom`),
283 KEY `RackSpace_object_id` (`object_id`)
e673ee24
DO
284) ENGINE=MyISAM;
285
42617841 286CREATE TABLE `Script` (
c1682618 287 `script_name` char(64) NOT NULL,
4a6a28f1 288 `script_text` longtext,
42617841
DO
289 PRIMARY KEY (`script_name`)
290) TYPE=MyISAM;
291
f9bc186f 292CREATE TABLE `TagStorage` (
120e9ddd
DO
293 `entity_realm` enum('file','ipv4net','ipv4vs','ipv4rspool','object','rack','user') NOT NULL default 'object',
294 `entity_id` int(10) unsigned NOT NULL,
42617841 295 `tag_id` int(10) unsigned NOT NULL,
120e9ddd
DO
296 UNIQUE KEY `entity_tag` (`entity_realm`,`entity_id`,`tag_id`),
297 KEY `entity_id` (`entity_id`)
f9bc186f
DO
298) TYPE=MyISAM;
299
36516fe5
DO
300CREATE TABLE `TagTree` (
301 `id` int(10) unsigned NOT NULL auto_increment,
302 `parent_id` int(10) unsigned default NULL,
dbb33805 303 `valid_realm` set('file','ipv4net','ipv4vs','ipv4rspool','object','rack','user') NOT NULL default 'file,ipv4net,ipv4vs,ipv4rspool,object,rack,user',
c1682618 304 `tag` char(255) default NULL,
36516fe5
DO
305 PRIMARY KEY (`id`),
306 UNIQUE KEY `tag` (`tag`)
307) TYPE=MyISAM;
308
e673ee24
DO
309CREATE TABLE `UserAccount` (
310 `user_id` int(10) unsigned NOT NULL auto_increment,
c1682618 311 `user_name` char(64) NOT NULL,
3827da34 312 `user_password_hash` char(40) default NULL,
c1682618 313 `user_realname` char(64) default NULL,
e673ee24
DO
314 PRIMARY KEY (`user_id`),
315 UNIQUE KEY `user_name` (`user_name`)
316) ENGINE=MyISAM AUTO_INCREMENT=10000;