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