85d18bf09d89de515517a1b51b4168b74abb7088
[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','router') 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 UNIQUE KEY `endpoint` (`vip`,`vport`,`proto`)
123 ) ENGINE=MyISAM;
124
125 CREATE TABLE `Link` (
126 `porta` int(10) unsigned NOT NULL,
127 `portb` int(10) unsigned NOT NULL,
128 PRIMARY KEY (`porta`,`portb`),
129 UNIQUE KEY `porta` (`porta`),
130 UNIQUE KEY `portb` (`portb`)
131 ) ENGINE=MyISAM;
132
133 CREATE TABLE `Molecule` (
134 `id` int(10) unsigned NOT NULL auto_increment,
135 PRIMARY KEY (`id`)
136 ) ENGINE=MyISAM;
137
138 CREATE TABLE `MountOperation` (
139 `id` int(10) unsigned NOT NULL auto_increment,
140 `object_id` int(10) unsigned NOT NULL,
141 `ctime` timestamp NOT NULL,
142 `user_name` char(64) default NULL,
143 `old_molecule_id` int(10) unsigned default NULL,
144 `new_molecule_id` int(10) unsigned default NULL,
145 `comment` text,
146 PRIMARY KEY (`id`)
147 ) ENGINE=MyISAM;
148
149 CREATE TABLE `Port` (
150 `id` int(10) unsigned NOT NULL auto_increment,
151 `object_id` int(10) unsigned NOT NULL,
152 `name` char(255) NOT NULL,
153 `type` int(10) unsigned NOT NULL,
154 `l2address` char(64) default NULL,
155 `reservation_comment` char(255) default NULL,
156 `label` char(255) default NULL,
157 PRIMARY KEY (`id`),
158 UNIQUE KEY `object_id` (`object_id`,`name`),
159 UNIQUE KEY `l2address` (`l2address`),
160 KEY `type` (`type`)
161 ) ENGINE=MyISAM;
162
163 CREATE TABLE `PortCompat` (
164 `type1` int(10) unsigned NOT NULL,
165 `type2` int(10) unsigned NOT NULL,
166 KEY `type1` (`type1`),
167 KEY `type2` (`type2`)
168 ) ENGINE=MyISAM;
169
170 CREATE TABLE `PortForwarding` (
171 `object_id` int(10) unsigned NOT NULL,
172 `proto` enum('TCP','UDP') not null default 'TCP',
173 `localip` int(10) unsigned NOT NULL,
174 `localport` smallint(5) unsigned NOT NULL,
175 `remoteip` int(10) unsigned NOT NULL,
176 `remoteport` smallint(5) unsigned NOT NULL,
177 `description` char(255) default NULL,
178 PRIMARY KEY (`object_id`,`proto`,`localip`,`localport`,`remoteip`,`remoteport`),
179 KEY `localip` (`localip`),
180 KEY `remoteip` (`remoteip`),
181 KEY `object_id` (`object_id`)
182 ) ENGINE=MyISAM;
183
184 CREATE TABLE `Rack` (
185 `id` int(10) unsigned NOT NULL auto_increment,
186 `name` char(255) default NULL,
187 `deleted` enum('yes','no') NOT NULL default 'no',
188 `row_id` int(10) unsigned NOT NULL default '1',
189 `height` tinyint(3) unsigned NOT NULL default '42',
190 `comment` text,
191 `thumb_data` blob,
192 PRIMARY KEY (`id`),
193 UNIQUE KEY `name_in_row` (`row_id`,`name`)
194 ) ENGINE=MyISAM;
195
196 CREATE TABLE `RackHistory` (
197 `id` int(10) unsigned default NULL,
198 `name` char(255) default NULL,
199 `deleted` enum('yes','no') default NULL,
200 `row_id` int(10) unsigned default NULL,
201 `height` tinyint(3) unsigned default NULL,
202 `comment` text,
203 `thumb_data` blob,
204 `ctime` timestamp NOT NULL,
205 `user_name` char(64) default NULL
206 ) ENGINE=MyISAM;
207
208 CREATE TABLE `RackObject` (
209 `id` int(10) unsigned NOT NULL auto_increment,
210 `name` char(255) default NULL,
211 `label` char(255) default NULL,
212 `barcode` char(16) default NULL,
213 `deleted` enum('yes','no') NOT NULL default 'no',
214 `objtype_id` int(10) unsigned NOT NULL default '1',
215 `asset_no` char(64) default NULL,
216 `has_problems` enum('yes','no') NOT NULL default 'no',
217 `comment` text,
218 PRIMARY KEY (`id`),
219 UNIQUE KEY `RackObject_asset_no` (`asset_no`),
220 UNIQUE KEY `name` (`name`),
221 UNIQUE KEY `barcode` (`barcode`)
222 ) ENGINE=MyISAM;
223
224 CREATE TABLE `RackObjectHistory` (
225 `id` int(10) unsigned default NULL,
226 `name` char(255) default NULL,
227 `label` char(255) default NULL,
228 `barcode` char(16) default NULL,
229 `deleted` enum('yes','no') default NULL,
230 `objtype_id` int(10) unsigned default NULL,
231 `asset_no` char(64) default NULL,
232 `has_problems` enum('yes','no') NOT NULL default 'no',
233 `comment` text,
234 `ctime` timestamp NOT NULL,
235 `user_name` char(64) default NULL
236 ) ENGINE=MyISAM;
237
238 CREATE TABLE `RackSpace` (
239 `rack_id` int(10) unsigned NOT NULL default '0',
240 `unit_no` int(10) unsigned NOT NULL default '0',
241 `atom` enum('front','interior','rear') NOT NULL default 'interior',
242 `state` enum('A','U','T','W') NOT NULL default 'A',
243 `object_id` int(10) unsigned default NULL,
244 PRIMARY KEY (`rack_id`,`unit_no`,`atom`)
245 ) ENGINE=MyISAM;
246
247 CREATE TABLE `Script` (
248 `script_name` char(64) NOT NULL,
249 `script_text` longtext,
250 PRIMARY KEY (`script_name`)
251 ) TYPE=MyISAM;
252
253 CREATE TABLE `TagStorage` (
254 `target_realm` enum('object','ipv4net','rack','ipv4vs','ipv4rspool','user') NOT NULL default 'object',
255 `target_id` int(10) unsigned NOT NULL,
256 `tag_id` int(10) unsigned NOT NULL,
257 UNIQUE KEY `entity_tag` (`target_realm`,`target_id`,`tag_id`),
258 KEY `target_id` (`target_id`)
259 ) TYPE=MyISAM;
260
261 CREATE TABLE `TagTree` (
262 `id` int(10) unsigned NOT NULL auto_increment,
263 `parent_id` int(10) unsigned default NULL,
264 `tag` char(255) default NULL,
265 PRIMARY KEY (`id`),
266 UNIQUE KEY `tag` (`tag`)
267 ) TYPE=MyISAM;
268
269 CREATE TABLE `UserAccount` (
270 `user_id` int(10) unsigned NOT NULL auto_increment,
271 `user_name` char(64) NOT NULL,
272 `user_enabled` enum('yes','no') NOT NULL default 'no',
273 `user_password_hash` char(128) default NULL,
274 `user_realname` char(64) default NULL,
275 PRIMARY KEY (`user_id`),
276 UNIQUE KEY `user_name` (`user_name`)
277 ) ENGINE=MyISAM AUTO_INCREMENT=10000;