r1106 importing RackTables trunk
[racktables] / install / init-structure.sql
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
16 DROP TABLE IF EXISTS `Atom`;
17 CREATE 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
28 DROP TABLE IF EXISTS `Attribute`;
29 CREATE 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
41 DROP TABLE IF EXISTS `AttributeMap`;
42 CREATE 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
53 DROP TABLE IF EXISTS `AttributeValue`;
54 CREATE 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
67 DROP TABLE IF EXISTS `Chapter`;
68 CREATE 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
80 DROP TABLE IF EXISTS `Dictionary`;
81 CREATE 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,
85 PRIMARY KEY (`chapter_no`,`dict_key`),
86 UNIQUE KEY `chapter_no` (`chapter_no`,`dict_value`)
87 ) ENGINE=MyISAM AUTO_INCREMENT=10000;
88
89 --
90 -- Table structure for table `IPAddress`
91 --
92
93 DROP TABLE IF EXISTS `IPAddress`;
94 CREATE TABLE `IPAddress` (
95 `ip` int(10) unsigned NOT NULL,
96 `name` char(255) NOT NULL,
97 `reserved` enum('yes','no') default NULL,
98 PRIMARY KEY (`ip`)
99 ) ENGINE=MyISAM;
100
101 --
102 -- Table structure for table `IPBonds`
103 --
104
105 DROP TABLE IF EXISTS `IPBonds`;
106 CREATE TABLE `IPBonds` (
107 `object_id` int(11) NOT NULL,
108 `ip` int(10) unsigned NOT NULL,
109 `name` char(255) NOT NULL,
110 `type` enum('regular','shared','virtual') default NULL,
111 PRIMARY KEY (`object_id`,`ip`)
112 ) ENGINE=MyISAM;
113
114 --
115 -- Table structure for table `IPRanges`
116 --
117
118 DROP TABLE IF EXISTS `IPRanges`;
119 CREATE TABLE `IPRanges` (
120 `id` int(11) NOT NULL auto_increment,
121 `ip` int(10) unsigned NOT NULL,
122 `mask` int(11) NOT NULL,
123 `name` char(255) default NULL,
124 PRIMARY KEY (`id`)
125 ) ENGINE=MyISAM;
126
127 --
128 -- Table structure for table `Link`
129 --
130
131 DROP TABLE IF EXISTS `Link`;
132 CREATE TABLE `Link` (
133 `porta` int(11) NOT NULL,
134 `portb` int(11) NOT NULL,
135 PRIMARY KEY (`porta`,`portb`),
136 UNIQUE KEY `porta` (`porta`),
137 UNIQUE KEY `portb` (`portb`)
138 ) ENGINE=MyISAM;
139
140 --
141 -- Table structure for table `Molecule`
142 --
143
144 DROP TABLE IF EXISTS `Molecule`;
145 CREATE TABLE `Molecule` (
146 `id` int(10) unsigned NOT NULL auto_increment,
147 PRIMARY KEY (`id`)
148 ) ENGINE=MyISAM;
149
150 --
151 -- Table structure for table `MountOperation`
152 --
153
154 DROP TABLE IF EXISTS `MountOperation`;
155 CREATE TABLE `MountOperation` (
156 `id` int(10) unsigned NOT NULL auto_increment,
157 `object_id` int(10) unsigned NOT NULL,
158 `ctime` timestamp NOT NULL,
159 `user_name` char(64) default NULL,
160 `old_molecule_id` int(10) unsigned default NULL,
161 `new_molecule_id` int(10) unsigned default NULL,
162 `comment` text,
163 PRIMARY KEY (`id`)
164 ) ENGINE=MyISAM;
165
166 --
167 -- Table structure for table `Port`
168 --
169
170 DROP TABLE IF EXISTS `Port`;
171 CREATE TABLE `Port` (
172 `id` int(11) NOT NULL auto_increment,
173 `object_id` int(11) NOT NULL,
174 `name` char(255) NOT NULL,
175 `type` int(11) NOT NULL,
176 `l2address` char(64) default NULL,
177 `reservation_comment` char(255) default NULL,
178 `label` char(255) default NULL,
179 PRIMARY KEY (`id`),
180 UNIQUE KEY `object_id` (`object_id`,`name`),
181 UNIQUE KEY `l2address` (`l2address`)
182 ) ENGINE=MyISAM;
183
184 --
185 -- Table structure for table `PortCompat`
186 --
187
188 DROP TABLE IF EXISTS `PortCompat`;
189 CREATE TABLE `PortCompat` (
190 `type1` int(11) NOT NULL,
191 `type2` int(11) NOT NULL
192 ) ENGINE=MyISAM;
193
194 --
195 -- Table structure for table `PortForwarding`
196 --
197
198 DROP TABLE IF EXISTS `PortForwarding`;
199 CREATE TABLE `PortForwarding` (
200 `object_id` int(11) NOT NULL,
201 `proto` int(11) NOT NULL,
202 `localip` int(10) unsigned NOT NULL,
203 `localport` int(11) NOT NULL,
204 `remoteip` int(10) unsigned NOT NULL,
205 `remoteport` int(11) NOT NULL,
206 `description` char(255) default NULL,
207 PRIMARY KEY (`object_id`,`proto`,`localip`,`localport`,`remoteip`,`remoteport`),
208 KEY `localip` (`localip`),
209 KEY `remoteip` (`remoteip`),
210 KEY `object_id` (`object_id`)
211 ) ENGINE=MyISAM;
212
213 --
214 -- Table structure for table `Rack`
215 --
216
217 DROP TABLE IF EXISTS `Rack`;
218 CREATE TABLE `Rack` (
219 `id` int(10) unsigned NOT NULL auto_increment,
220 `name` char(255) default NULL,
221 `deleted` enum('yes','no') NOT NULL default 'no',
222 `row_id` int(10) unsigned NOT NULL default '1',
223 `height` int(10) unsigned NOT NULL default '42',
224 `comment` text,
225 PRIMARY KEY (`id`)
226 ) ENGINE=MyISAM;
227
228 --
229 -- Table structure for table `RackHistory`
230 --
231
232 DROP TABLE IF EXISTS `RackHistory`;
233 CREATE TABLE `RackHistory` (
234 `id` int(10) unsigned default NULL,
235 `name` char(255) default NULL,
236 `deleted` enum('yes','no') default NULL,
237 `row_id` int(10) unsigned default NULL,
238 `height` int(10) unsigned default NULL,
239 `comment` text,
240 `ctime` timestamp NOT NULL,
241 `user_name` char(64) default NULL
242 ) ENGINE=MyISAM;
243
244 --
245 -- Table structure for table `RackObject`
246 --
247
248 DROP TABLE IF EXISTS `RackObject`;
249 CREATE TABLE `RackObject` (
250 `id` int(10) unsigned NOT NULL auto_increment,
251 `name` char(255) default NULL,
252 `label` char(255) default NULL,
253 `barcode` char(16) default NULL,
254 `deleted` enum('yes','no') NOT NULL default 'no',
255 `objtype_id` int(10) unsigned NOT NULL default '1',
256 `asset_no` char(64) default NULL,
257 `has_problems` enum('yes','no') NOT NULL default 'no',
258 `comment` text,
259 PRIMARY KEY (`id`),
260 UNIQUE KEY `RackObject_asset_no` (`asset_no`),
261 UNIQUE KEY `name` (`name`),
262 UNIQUE KEY `barcode` (`barcode`)
263 ) ENGINE=MyISAM;
264
265 --
266 -- Table structure for table `RackObjectHistory`
267 --
268
269 DROP TABLE IF EXISTS `RackObjectHistory`;
270 CREATE TABLE `RackObjectHistory` (
271 `id` int(10) unsigned default NULL,
272 `name` char(255) default NULL,
273 `label` char(255) default NULL,
274 `barcode` char(16) default NULL,
275 `deleted` enum('yes','no') default NULL,
276 `objtype_id` int(10) unsigned default NULL,
277 `asset_no` char(64) default NULL,
278 `has_problems` enum('yes','no') NOT NULL default 'no',
279 `comment` text,
280 `ctime` timestamp NOT NULL,
281 `user_name` char(64) default NULL
282 ) ENGINE=MyISAM;
283
284 --
285 -- Table structure for table `RackSpace`
286 --
287
288 DROP TABLE IF EXISTS `RackSpace`;
289 CREATE TABLE `RackSpace` (
290 `rack_id` int(10) unsigned NOT NULL default '0',
291 `unit_no` int(10) unsigned NOT NULL default '0',
292 `atom` enum('front','interior','rear') NOT NULL default 'interior',
293 `state` enum('A','U','T','W') NOT NULL default 'A',
294 `object_id` int(10) unsigned default NULL,
295 `problem_id` int(10) unsigned default NULL,
296 PRIMARY KEY (`rack_id`,`unit_no`,`atom`)
297 ) ENGINE=MyISAM;
298
299 --
300 -- Table structure for table `UserAccount`
301 --
302
303 DROP TABLE IF EXISTS `UserAccount`;
304 CREATE TABLE `UserAccount` (
305 `user_id` int(10) unsigned NOT NULL auto_increment,
306 `user_name` char(64) NOT NULL,
307 `user_enabled` enum('yes','no') NOT NULL default 'no',
308 `user_password_hash` char(128) default NULL,
309 `user_realname` char(64) default NULL,
310 PRIMARY KEY (`user_id`),
311 UNIQUE KEY `user_name` (`user_name`)
312 ) ENGINE=MyISAM AUTO_INCREMENT=10000;
313
314 --
315 -- Table structure for table `UserPermission`
316 --
317
318 DROP TABLE IF EXISTS `UserPermission`;
319 CREATE TABLE `UserPermission` (
320 `user_id` int(10) unsigned NOT NULL default '0',
321 `page` char(64) NOT NULL default '%',
322 `tab` char(64) NOT NULL default '%',
323 `access` enum('yes','no') NOT NULL default 'no',
324 UNIQUE KEY `user_id` (`user_id`,`page`,`tab`)
325 ) ENGINE=MyISAM;
326 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
327
328 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
329 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
330 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
331 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
332 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
333 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
334 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;