7c680b907327577c0f32f6488e88999c1159ddf1
[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 `attr_id` int(10) unsigned NOT NULL auto_increment,
13 `attr_type` enum('string','uint','float','dict') default NULL,
14 `attr_name` char(64) default NULL,
15 PRIMARY KEY (`attr_id`),
16 UNIQUE KEY `attr_name` (`attr_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_no` 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 `chapter_no` int(10) unsigned NOT NULL auto_increment,
37 `sticky` enum('yes','no') default 'no',
38 `chapter_name` char(128) NOT NULL,
39 PRIMARY KEY (`chapter_no`),
40 UNIQUE KEY `chapter_name` (`chapter_name`)
41 ) ENGINE=MyISAM AUTO_INCREMENT=10000;
42
43 CREATE TABLE `Dictionary` (
44 `chapter_no` int(10) unsigned NOT NULL,
45 `dict_key` int(10) unsigned NOT NULL auto_increment,
46 `dict_value` char(255) default NULL,
47 PRIMARY KEY (`dict_key`),
48 UNIQUE KEY `chap_to_key` (`chapter_no`,`dict_key`),
49 UNIQUE KEY `chap_to_val` (`chapter_no`,`dict_value`)
50 ) ENGINE=MyISAM AUTO_INCREMENT=50000;
51
52 CREATE TABLE `IPAddress` (
53 `ip` int(10) unsigned NOT NULL,
54 `name` char(255) NOT NULL,
55 `reserved` enum('yes','no') default NULL,
56 PRIMARY KEY (`ip`)
57 ) ENGINE=MyISAM;
58
59 CREATE TABLE `IPBonds` (
60 `object_id` int(10) unsigned NOT NULL,
61 `ip` int(10) unsigned NOT NULL,
62 `name` char(255) NOT NULL,
63 `type` enum('regular','shared','virtual') default NULL,
64 PRIMARY KEY (`object_id`,`ip`)
65 ) ENGINE=MyISAM;
66
67 CREATE TABLE `IPLoadBalancer` (
68 `object_id` int(10) unsigned default NULL,
69 `rspool_id` int(10) unsigned default NULL,
70 `vs_id` int(10) unsigned default NULL,
71 `vsconfig` text,
72 `rsconfig` text,
73 UNIQUE KEY `LB-VS` (`object_id`,`vs_id`)
74 ) ENGINE=MyISAM;
75
76 CREATE TABLE `IPRSPool` (
77 `id` int(10) unsigned NOT NULL auto_increment,
78 `name` char(255) default NULL,
79 `vsconfig` text,
80 `rsconfig` text,
81 PRIMARY KEY (`id`)
82 ) ENGINE=MyISAM;
83
84 CREATE TABLE `IPRanges` (
85 `id` int(10) unsigned NOT NULL auto_increment,
86 `ip` int(10) unsigned NOT NULL,
87 `mask` int(10) unsigned NOT NULL,
88 `name` char(255) default NULL,
89 PRIMARY KEY (`id`)
90 ) ENGINE=MyISAM;
91
92 CREATE TABLE `IPRealServer` (
93 `id` int(10) unsigned NOT NULL auto_increment,
94 `inservice` enum('yes','no') NOT NULL default 'no',
95 `rsip` int(10) unsigned default NULL,
96 `rsport` smallint(5) unsigned default NULL,
97 `rspool_id` int(10) unsigned default NULL,
98 `rsconfig` text,
99 PRIMARY KEY (`id`),
100 UNIQUE KEY `pool-endpoint` (`rspool_id`,`rsip`,`rsport`)
101 ) ENGINE=MyISAM;
102
103 CREATE TABLE `IPVirtualService` (
104 `id` int(10) unsigned NOT NULL auto_increment,
105 `vip` int(10) unsigned default NULL,
106 `vport` smallint(5) unsigned default NULL,
107 `proto` enum('TCP','UDP') NOT NULL default 'TCP',
108 `name` char(255) default NULL,
109 `vsconfig` text,
110 `rsconfig` text,
111 PRIMARY KEY (`id`)
112 ) ENGINE=MyISAM;
113
114 CREATE TABLE `Link` (
115 `porta` int(10) unsigned NOT NULL,
116 `portb` int(10) unsigned NOT NULL,
117 PRIMARY KEY (`porta`,`portb`),
118 UNIQUE KEY `porta` (`porta`),
119 UNIQUE KEY `portb` (`portb`)
120 ) ENGINE=MyISAM;
121
122 CREATE TABLE `Molecule` (
123 `id` int(10) unsigned NOT NULL auto_increment,
124 PRIMARY KEY (`id`)
125 ) ENGINE=MyISAM;
126
127 CREATE TABLE `MountOperation` (
128 `id` int(10) unsigned NOT NULL auto_increment,
129 `object_id` int(10) unsigned NOT NULL,
130 `ctime` timestamp NOT NULL,
131 `user_name` char(64) default NULL,
132 `old_molecule_id` int(10) unsigned default NULL,
133 `new_molecule_id` int(10) unsigned default NULL,
134 `comment` text,
135 PRIMARY KEY (`id`)
136 ) ENGINE=MyISAM;
137
138 CREATE TABLE `Port` (
139 `id` int(10) unsigned NOT NULL auto_increment,
140 `object_id` int(10) unsigned NOT NULL,
141 `name` char(255) NOT NULL,
142 `type` int(10) unsigned NOT NULL,
143 `l2address` char(64) default NULL,
144 `reservation_comment` char(255) default NULL,
145 `label` char(255) default NULL,
146 PRIMARY KEY (`id`),
147 UNIQUE KEY `object_id` (`object_id`,`name`),
148 UNIQUE KEY `l2address` (`l2address`),
149 KEY `type` (`type`)
150 ) ENGINE=MyISAM;
151
152 CREATE TABLE `PortCompat` (
153 `type1` int(10) unsigned NOT NULL,
154 `type2` int(10) unsigned NOT NULL,
155 KEY `type1` (`type1`),
156 KEY `type2` (`type2`)
157 ) ENGINE=MyISAM;
158
159 CREATE TABLE `PortForwarding` (
160 `object_id` int(10) unsigned NOT NULL,
161 `proto` int(11) NOT NULL,
162 `localip` int(10) unsigned NOT NULL,
163 `localport` smallint(5) unsigned NOT NULL,
164 `remoteip` int(10) unsigned NOT NULL,
165 `remoteport` smallint(5) unsigned NOT NULL,
166 `description` char(255) default NULL,
167 PRIMARY KEY (`object_id`,`proto`,`localip`,`localport`,`remoteip`,`remoteport`),
168 KEY `localip` (`localip`),
169 KEY `remoteip` (`remoteip`),
170 KEY `object_id` (`object_id`)
171 ) ENGINE=MyISAM;
172
173 CREATE TABLE `Rack` (
174 `id` int(10) unsigned NOT NULL auto_increment,
175 `name` char(255) default NULL,
176 `deleted` enum('yes','no') NOT NULL default 'no',
177 `row_id` int(10) unsigned NOT NULL default '1',
178 `height` tinyint(3) unsigned NOT NULL default '42',
179 `comment` text,
180 `thumb_data` blob,
181 PRIMARY KEY (`id`),
182 UNIQUE KEY `name_in_row` (`row_id`,`name`)
183 ) ENGINE=MyISAM;
184
185 CREATE TABLE `RackHistory` (
186 `id` int(10) unsigned default NULL,
187 `name` char(255) default NULL,
188 `deleted` enum('yes','no') default NULL,
189 `row_id` int(10) unsigned default NULL,
190 `height` tinyint(3) unsigned default NULL,
191 `comment` text,
192 `thumb_data` blob,
193 `ctime` timestamp NOT NULL,
194 `user_name` char(64) default NULL
195 ) ENGINE=MyISAM;
196
197 CREATE TABLE `RackObject` (
198 `id` int(10) unsigned NOT NULL auto_increment,
199 `name` char(255) default NULL,
200 `label` char(255) default NULL,
201 `barcode` char(16) default NULL,
202 `deleted` enum('yes','no') NOT NULL default 'no',
203 `objtype_id` int(10) unsigned NOT NULL default '1',
204 `asset_no` char(64) default NULL,
205 `has_problems` enum('yes','no') NOT NULL default 'no',
206 `comment` text,
207 PRIMARY KEY (`id`),
208 UNIQUE KEY `RackObject_asset_no` (`asset_no`),
209 UNIQUE KEY `name` (`name`),
210 UNIQUE KEY `barcode` (`barcode`)
211 ) ENGINE=MyISAM;
212
213 CREATE TABLE `RackObjectHistory` (
214 `id` int(10) unsigned default NULL,
215 `name` char(255) default NULL,
216 `label` char(255) default NULL,
217 `barcode` char(16) default NULL,
218 `deleted` enum('yes','no') default NULL,
219 `objtype_id` int(10) unsigned default NULL,
220 `asset_no` char(64) default NULL,
221 `has_problems` enum('yes','no') NOT NULL default 'no',
222 `comment` text,
223 `ctime` timestamp NOT NULL,
224 `user_name` char(64) default NULL
225 ) ENGINE=MyISAM;
226
227 CREATE TABLE `RackObjectTags` (
228 `object_id` int(10) unsigned default NULL,
229 `tag_id` int(10) unsigned default NULL,
230 UNIQUE KEY `object_tag` (`object_id`,`tag_id`)
231 ) TYPE=MyISAM;
232
233 CREATE TABLE `RackSpace` (
234 `rack_id` int(10) unsigned NOT NULL default '0',
235 `unit_no` int(10) unsigned NOT NULL default '0',
236 `atom` enum('front','interior','rear') NOT NULL default 'interior',
237 `state` enum('A','U','T','W') NOT NULL default 'A',
238 `object_id` int(10) unsigned default NULL,
239 PRIMARY KEY (`rack_id`,`unit_no`,`atom`)
240 ) ENGINE=MyISAM;
241
242 CREATE TABLE `TagTree` (
243 `id` int(10) unsigned NOT NULL auto_increment,
244 `parent_id` int(10) unsigned default NULL,
245 `tag` char(255) default NULL,
246 PRIMARY KEY (`id`),
247 UNIQUE KEY `tag` (`tag`)
248 ) TYPE=MyISAM;
249
250 CREATE TABLE `UserAccount` (
251 `user_id` int(10) unsigned NOT NULL auto_increment,
252 `user_name` char(64) NOT NULL,
253 `user_enabled` enum('yes','no') NOT NULL default 'no',
254 `user_password_hash` char(128) default NULL,
255 `user_realname` char(64) default NULL,
256 PRIMARY KEY (`user_id`),
257 UNIQUE KEY `user_name` (`user_name`)
258 ) ENGINE=MyISAM AUTO_INCREMENT=10000;
259
260 CREATE TABLE `UserPermission` (
261 `user_id` int(10) unsigned NOT NULL default '0',
262 `page` char(64) NOT NULL default '%',
263 `tab` char(64) NOT NULL default '%',
264 `access` enum('yes','no') NOT NULL default 'no',
265 UNIQUE KEY `user_id` (`user_id`,`page`,`tab`)
266 ) ENGINE=MyISAM;
267
268 CREATE TABLE `Config` (
269 `varname` char(32) NOT NULL,
270 `varvalue` char(64) NOT NULL,
271 `vartype` enum('string','uint') NOT NULL default 'string',
272 `emptyok` enum('yes','no') NOT NULL default 'no',
273 `is_hidden` enum('yes','no') NOT NULL default 'yes',
274 `description` text,
275 PRIMARY KEY (`varname`)
276 ) ENGINE=MyISAM;