r1812 + generalise tag storage and handling; prepare to introduce tags for other...
[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 `RackSpace` (
228 `rack_id` int(10) unsigned NOT NULL default '0',
229 `unit_no` int(10) unsigned NOT NULL default '0',
230 `atom` enum('front','interior','rear') NOT NULL default 'interior',
231 `state` enum('A','U','T','W') NOT NULL default 'A',
232 `object_id` int(10) unsigned default NULL,
233 PRIMARY KEY (`rack_id`,`unit_no`,`atom`)
234 ) ENGINE=MyISAM;
235
236 CREATE TABLE `TagStorage` (
237 `target_realm` enum('object','ipv4net','rack','ipv4vs','ipv4rspool') NOT NULL default 'object',
238 `target_id` int(10) unsigned NOT NULL,
239 `tag_id` int(10) unsigned default NULL,
240 UNIQUE KEY `entity_tag` (`target_realm`,`target_id`,`tag_id`),
241 KEY `target_id` (`target_id`)
242 ) TYPE=MyISAM;
243
244 CREATE TABLE `TagTree` (
245 `id` int(10) unsigned NOT NULL auto_increment,
246 `parent_id` int(10) unsigned default NULL,
247 `tag` char(255) default NULL,
248 PRIMARY KEY (`id`),
249 UNIQUE KEY `tag` (`tag`)
250 ) TYPE=MyISAM;
251
252 CREATE TABLE `UserAccount` (
253 `user_id` int(10) unsigned NOT NULL auto_increment,
254 `user_name` char(64) NOT NULL,
255 `user_enabled` enum('yes','no') NOT NULL default 'no',
256 `user_password_hash` char(128) default NULL,
257 `user_realname` char(64) default NULL,
258 PRIMARY KEY (`user_id`),
259 UNIQUE KEY `user_name` (`user_name`)
260 ) ENGINE=MyISAM AUTO_INCREMENT=10000;
261
262 CREATE TABLE `UserPermission` (
263 `user_id` int(10) unsigned NOT NULL default '0',
264 `page` char(64) NOT NULL default '%',
265 `tab` char(64) NOT NULL default '%',
266 `access` enum('yes','no') NOT NULL default 'no',
267 UNIQUE KEY `user_id` (`user_id`,`page`,`tab`)
268 ) ENGINE=MyISAM;
269
270 CREATE TABLE `Config` (
271 `varname` char(32) NOT NULL,
272 `varvalue` char(64) NOT NULL,
273 `vartype` enum('string','uint') NOT NULL default 'string',
274 `emptyok` enum('yes','no') NOT NULL default 'no',
275 `is_hidden` enum('yes','no') NOT NULL default 'yes',
276 `description` text,
277 PRIMARY KEY (`varname`)
278 ) ENGINE=MyISAM;