r1362 + fix Port and Link tables: use unsigned integers for foreign key reference...
[racktables] / install / init-structure.sql
CommitLineData
e673ee24
DO
1/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
2/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
3/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
4/*!40101 SET NAMES utf8 */;
5/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
6/*!40103 SET TIME_ZONE='+00:00' */;
7/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
8/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
9/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
10/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
11
12--
13-- Table structure for table `Atom`
14--
15
16DROP TABLE IF EXISTS `Atom`;
17CREATE TABLE `Atom` (
18 `molecule_id` int(10) unsigned default NULL,
19 `rack_id` int(10) unsigned default NULL,
20 `unit_no` int(10) unsigned default NULL,
21 `atom` enum('front','interior','rear') default NULL
22) ENGINE=MyISAM;
23
24--
25-- Table structure for table `Attribute`
26--
27
28DROP TABLE IF EXISTS `Attribute`;
29CREATE TABLE `Attribute` (
30 `attr_id` int(10) unsigned NOT NULL auto_increment,
31 `attr_type` enum('string','uint','float','dict') default NULL,
32 `attr_name` char(64) default NULL,
33 PRIMARY KEY (`attr_id`),
34 UNIQUE KEY `attr_name` (`attr_name`)
35) ENGINE=MyISAM AUTO_INCREMENT=10000;
36
37--
38-- Table structure for table `AttributeMap`
39--
40
41DROP TABLE IF EXISTS `AttributeMap`;
42CREATE TABLE `AttributeMap` (
43 `objtype_id` int(10) unsigned NOT NULL default '1',
44 `attr_id` int(10) unsigned NOT NULL default '1',
45 `chapter_no` int(10) unsigned NOT NULL,
46 UNIQUE KEY `objtype_id` (`objtype_id`,`attr_id`)
47) ENGINE=MyISAM;
48
49--
50-- Table structure for table `AttributeValue`
51--
52
53DROP TABLE IF EXISTS `AttributeValue`;
54CREATE TABLE `AttributeValue` (
55 `object_id` int(10) unsigned default NULL,
56 `attr_id` int(10) unsigned default NULL,
57 `string_value` char(128) default NULL,
58 `uint_value` int(10) unsigned default NULL,
59 `float_value` float default NULL,
60 UNIQUE KEY `object_id` (`object_id`,`attr_id`)
61) ENGINE=MyISAM;
62
63--
64-- Table structure for table `Chapter`
65--
66
67DROP TABLE IF EXISTS `Chapter`;
68CREATE TABLE `Chapter` (
69 `chapter_no` int(10) unsigned NOT NULL auto_increment,
70 `sticky` enum('yes','no') default 'no',
71 `chapter_name` char(128) NOT NULL,
72 PRIMARY KEY (`chapter_no`),
73 UNIQUE KEY `chapter_name` (`chapter_name`)
74) ENGINE=MyISAM AUTO_INCREMENT=10000;
75
76--
77-- Table structure for table `Dictionary`
78--
79
80DROP TABLE IF EXISTS `Dictionary`;
81CREATE TABLE `Dictionary` (
82 `chapter_no` int(10) unsigned NOT NULL,
83 `dict_key` int(10) unsigned NOT NULL auto_increment,
84 `dict_value` char(128) default NULL,
71dcbe12
DO
85 PRIMARY KEY (`dict_key`),
86 UNIQUE KEY `chap_to_key` (`chapter_no`,`dict_key`),
87 UNIQUE KEY `chap_to_val` (`chapter_no`,`dict_value`)
88) ENGINE=MyISAM AUTO_INCREMENT=50000;
e673ee24
DO
89
90--
91-- Table structure for table `IPAddress`
92--
93
94DROP TABLE IF EXISTS `IPAddress`;
95CREATE TABLE `IPAddress` (
96 `ip` int(10) unsigned NOT NULL,
97 `name` char(255) NOT NULL,
98 `reserved` enum('yes','no') default NULL,
99 PRIMARY KEY (`ip`)
100) ENGINE=MyISAM;
101
102--
103-- Table structure for table `IPBonds`
104--
105
106DROP TABLE IF EXISTS `IPBonds`;
107CREATE TABLE `IPBonds` (
108 `object_id` int(11) NOT NULL,
109 `ip` int(10) unsigned NOT NULL,
110 `name` char(255) NOT NULL,
111 `type` enum('regular','shared','virtual') default NULL,
112 PRIMARY KEY (`object_id`,`ip`)
113) ENGINE=MyISAM;
114
115--
116-- Table structure for table `IPRanges`
117--
118
119DROP TABLE IF EXISTS `IPRanges`;
120CREATE TABLE `IPRanges` (
121 `id` int(11) NOT NULL auto_increment,
122 `ip` int(10) unsigned NOT NULL,
123 `mask` int(11) NOT NULL,
124 `name` char(255) default NULL,
125 PRIMARY KEY (`id`)
126) ENGINE=MyISAM;
127
128--
129-- Table structure for table `Link`
130--
131
132DROP TABLE IF EXISTS `Link`;
133CREATE TABLE `Link` (
167df29b
DO
134 `porta` int(10) unsigned NOT NULL,
135 `portb` int(10) unsigned NOT NULL,
e673ee24
DO
136 PRIMARY KEY (`porta`,`portb`),
137 UNIQUE KEY `porta` (`porta`),
138 UNIQUE KEY `portb` (`portb`)
139) ENGINE=MyISAM;
140
141--
142-- Table structure for table `Molecule`
143--
144
145DROP TABLE IF EXISTS `Molecule`;
146CREATE TABLE `Molecule` (
147 `id` int(10) unsigned NOT NULL auto_increment,
148 PRIMARY KEY (`id`)
149) ENGINE=MyISAM;
150
151--
152-- Table structure for table `MountOperation`
153--
154
155DROP TABLE IF EXISTS `MountOperation`;
156CREATE TABLE `MountOperation` (
157 `id` int(10) unsigned NOT NULL auto_increment,
158 `object_id` int(10) unsigned NOT NULL,
159 `ctime` timestamp NOT NULL,
160 `user_name` char(64) default NULL,
161 `old_molecule_id` int(10) unsigned default NULL,
162 `new_molecule_id` int(10) unsigned default NULL,
163 `comment` text,
164 PRIMARY KEY (`id`)
165) ENGINE=MyISAM;
166
167--
168-- Table structure for table `Port`
169--
170
171DROP TABLE IF EXISTS `Port`;
172CREATE TABLE `Port` (
167df29b
DO
173 `id` int(10) unsigned NOT NULL auto_increment,
174 `object_id` int(10) unsigned NOT NULL,
e673ee24 175 `name` char(255) NOT NULL,
167df29b 176 `type` int(10) unsigned NOT NULL,
e673ee24
DO
177 `l2address` char(64) default NULL,
178 `reservation_comment` char(255) default NULL,
179 `label` char(255) default NULL,
180 PRIMARY KEY (`id`),
181 UNIQUE KEY `object_id` (`object_id`,`name`),
182 UNIQUE KEY `l2address` (`l2address`)
183) ENGINE=MyISAM;
184
185--
186-- Table structure for table `PortCompat`
187--
188
189DROP TABLE IF EXISTS `PortCompat`;
190CREATE TABLE `PortCompat` (
71dcbe12
DO
191 `type1` int(10) unsigned NOT NULL,
192 `type2` int(10) unsigned NOT NULL
e673ee24
DO
193) ENGINE=MyISAM;
194
195--
196-- Table structure for table `PortForwarding`
197--
198
199DROP TABLE IF EXISTS `PortForwarding`;
200CREATE TABLE `PortForwarding` (
201 `object_id` int(11) NOT NULL,
202 `proto` int(11) NOT NULL,
203 `localip` int(10) unsigned NOT NULL,
204 `localport` int(11) NOT NULL,
205 `remoteip` int(10) unsigned NOT NULL,
206 `remoteport` int(11) NOT NULL,
207 `description` char(255) default NULL,
208 PRIMARY KEY (`object_id`,`proto`,`localip`,`localport`,`remoteip`,`remoteport`),
209 KEY `localip` (`localip`),
210 KEY `remoteip` (`remoteip`),
211 KEY `object_id` (`object_id`)
212) ENGINE=MyISAM;
213
214--
215-- Table structure for table `Rack`
216--
217
218DROP TABLE IF EXISTS `Rack`;
219CREATE TABLE `Rack` (
220 `id` int(10) unsigned NOT NULL auto_increment,
221 `name` char(255) default NULL,
222 `deleted` enum('yes','no') NOT NULL default 'no',
223 `row_id` int(10) unsigned NOT NULL default '1',
224 `height` int(10) unsigned NOT NULL default '42',
225 `comment` text,
226 PRIMARY KEY (`id`)
227) ENGINE=MyISAM;
228
229--
230-- Table structure for table `RackHistory`
231--
232
233DROP TABLE IF EXISTS `RackHistory`;
234CREATE TABLE `RackHistory` (
235 `id` int(10) unsigned default NULL,
236 `name` char(255) default NULL,
237 `deleted` enum('yes','no') default NULL,
238 `row_id` int(10) unsigned default NULL,
239 `height` int(10) unsigned default NULL,
240 `comment` text,
241 `ctime` timestamp NOT NULL,
242 `user_name` char(64) default NULL
243) ENGINE=MyISAM;
244
245--
246-- Table structure for table `RackObject`
247--
248
249DROP TABLE IF EXISTS `RackObject`;
250CREATE 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--
267-- Table structure for table `RackObjectHistory`
268--
269
270DROP TABLE IF EXISTS `RackObjectHistory`;
271CREATE TABLE `RackObjectHistory` (
272 `id` int(10) unsigned default NULL,
273 `name` char(255) default NULL,
274 `label` char(255) default NULL,
275 `barcode` char(16) default NULL,
276 `deleted` enum('yes','no') default NULL,
277 `objtype_id` int(10) unsigned default NULL,
278 `asset_no` char(64) default NULL,
279 `has_problems` enum('yes','no') NOT NULL default 'no',
280 `comment` text,
281 `ctime` timestamp NOT NULL,
282 `user_name` char(64) default NULL
283) ENGINE=MyISAM;
284
285--
286-- Table structure for table `RackSpace`
287--
288
289DROP TABLE IF EXISTS `RackSpace`;
290CREATE TABLE `RackSpace` (
291 `rack_id` int(10) unsigned NOT NULL default '0',
292 `unit_no` int(10) unsigned NOT NULL default '0',
293 `atom` enum('front','interior','rear') NOT NULL default 'interior',
294 `state` enum('A','U','T','W') NOT NULL default 'A',
295 `object_id` int(10) unsigned default NULL,
296 `problem_id` int(10) unsigned default NULL,
297 PRIMARY KEY (`rack_id`,`unit_no`,`atom`)
298) ENGINE=MyISAM;
299
300--
301-- Table structure for table `UserAccount`
302--
303
304DROP TABLE IF EXISTS `UserAccount`;
305CREATE TABLE `UserAccount` (
306 `user_id` int(10) unsigned NOT NULL auto_increment,
307 `user_name` char(64) NOT NULL,
308 `user_enabled` enum('yes','no') NOT NULL default 'no',
309 `user_password_hash` char(128) default NULL,
310 `user_realname` char(64) default NULL,
311 PRIMARY KEY (`user_id`),
312 UNIQUE KEY `user_name` (`user_name`)
313) ENGINE=MyISAM AUTO_INCREMENT=10000;
314
315--
316-- Table structure for table `UserPermission`
317--
318
319DROP TABLE IF EXISTS `UserPermission`;
320CREATE TABLE `UserPermission` (
321 `user_id` int(10) unsigned NOT NULL default '0',
322 `page` char(64) NOT NULL default '%',
323 `tab` char(64) NOT NULL default '%',
324 `access` enum('yes','no') NOT NULL default 'no',
325 UNIQUE KEY `user_id` (`user_id`,`page`,`tab`)
326) ENGINE=MyISAM;
8d068153
DO
327
328--
329-- Table structure for table `Config`
330--
331
332DROP TABLE IF EXISTS `Config`;
333CREATE TABLE `Config` (
334 `varname` char(32) NOT NULL,
335 `varvalue` char(64) NOT NULL,
336 `vartype` enum('string','uint') NOT NULL default 'string',
337 `emptyok` enum('yes','no') NOT NULL default 'no',
338 `is_hidden` enum('yes','no') NOT NULL default 'yes',
339 `description` text,
340 PRIMARY KEY (`varname`)
341) ENGINE=MyISAM;
342
e673ee24
DO
343/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
344
345/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
346/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
347/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
348/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
349/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
350/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
351/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;