r1833 + expand varvalue column to hold more data
[racktables] / install / init-structure.sql
CommitLineData
48329bfb
DO
1alter database character set utf8;
2set names 'utf8';
3
e673ee24
DO
4CREATE 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
e673ee24
DO
11CREATE 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
e673ee24
DO
19CREATE 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
e673ee24
DO
26CREATE 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
e673ee24
DO
35CREATE 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
e673ee24
DO
43CREATE TABLE `Dictionary` (
44 `chapter_no` int(10) unsigned NOT NULL,
45 `dict_key` int(10) unsigned NOT NULL auto_increment,
b83abd29 46 `dict_value` char(255) default NULL,
71dcbe12
DO
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;
e673ee24 51
e673ee24
DO
52CREATE 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
e673ee24 59CREATE TABLE `IPBonds` (
ba1c6d42 60 `object_id` int(10) unsigned NOT NULL,
e673ee24
DO
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
192c95ce
DO
67CREATE TABLE `IPLoadBalancer` (
68 `object_id` int(10) unsigned default NULL,
69 `rspool_id` int(10) unsigned default NULL,
70c24883 70 `vs_id` int(10) unsigned default NULL,
8d350975
DO
71 `vsconfig` text,
72 `rsconfig` text,
70c24883 73 UNIQUE KEY `LB-VS` (`object_id`,`vs_id`)
192c95ce
DO
74) ENGINE=MyISAM;
75
192c95ce
DO
76CREATE TABLE `IPRSPool` (
77 `id` int(10) unsigned NOT NULL auto_increment,
192c95ce
DO
78 `name` char(255) default NULL,
79 `vsconfig` text,
80 `rsconfig` text,
81 PRIMARY KEY (`id`)
82) ENGINE=MyISAM;
83
e673ee24 84CREATE TABLE `IPRanges` (
ba1c6d42 85 `id` int(10) unsigned NOT NULL auto_increment,
e673ee24 86 `ip` int(10) unsigned NOT NULL,
ba1c6d42 87 `mask` int(10) unsigned NOT NULL,
e673ee24
DO
88 `name` char(255) default NULL,
89 PRIMARY KEY (`id`)
90) ENGINE=MyISAM;
91
192c95ce
DO
92CREATE TABLE `IPRealServer` (
93 `id` int(10) unsigned NOT NULL auto_increment,
1f7d18fa 94 `inservice` enum('yes','no') NOT NULL default 'no',
192c95ce
DO
95 `rsip` int(10) unsigned default NULL,
96 `rsport` smallint(5) unsigned default NULL,
97 `rspool_id` int(10) unsigned default NULL,
ab82514d 98 `rsconfig` text,
4ab15209 99 PRIMARY KEY (`id`),
70c24883 100 UNIQUE KEY `pool-endpoint` (`rspool_id`,`rsip`,`rsport`)
192c95ce
DO
101) ENGINE=MyISAM;
102
192c95ce
DO
103CREATE 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,
9c3043f3
DO
109 `vsconfig` text,
110 `rsconfig` text,
70c24883 111 PRIMARY KEY (`id`)
192c95ce
DO
112) ENGINE=MyISAM;
113
e673ee24 114CREATE TABLE `Link` (
167df29b
DO
115 `porta` int(10) unsigned NOT NULL,
116 `portb` int(10) unsigned NOT NULL,
e673ee24
DO
117 PRIMARY KEY (`porta`,`portb`),
118 UNIQUE KEY `porta` (`porta`),
119 UNIQUE KEY `portb` (`portb`)
120) ENGINE=MyISAM;
121
e673ee24
DO
122CREATE TABLE `Molecule` (
123 `id` int(10) unsigned NOT NULL auto_increment,
124 PRIMARY KEY (`id`)
125) ENGINE=MyISAM;
126
e673ee24
DO
127CREATE 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
e673ee24 138CREATE TABLE `Port` (
167df29b
DO
139 `id` int(10) unsigned NOT NULL auto_increment,
140 `object_id` int(10) unsigned NOT NULL,
e673ee24 141 `name` char(255) NOT NULL,
167df29b 142 `type` int(10) unsigned NOT NULL,
e673ee24
DO
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`),
78f44cbc
DO
148 UNIQUE KEY `l2address` (`l2address`),
149 KEY `type` (`type`)
e673ee24
DO
150) ENGINE=MyISAM;
151
e673ee24 152CREATE TABLE `PortCompat` (
71dcbe12 153 `type1` int(10) unsigned NOT NULL,
78f44cbc
DO
154 `type2` int(10) unsigned NOT NULL,
155 KEY `type1` (`type1`),
156 KEY `type2` (`type2`)
e673ee24
DO
157) ENGINE=MyISAM;
158
e673ee24 159CREATE TABLE `PortForwarding` (
ba1c6d42 160 `object_id` int(10) unsigned NOT NULL,
e673ee24
DO
161 `proto` int(11) NOT NULL,
162 `localip` int(10) unsigned NOT NULL,
ba1c6d42 163 `localport` smallint(5) unsigned NOT NULL,
e673ee24 164 `remoteip` int(10) unsigned NOT NULL,
ba1c6d42 165 `remoteport` smallint(5) unsigned NOT NULL,
e673ee24
DO
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
e673ee24
DO
173CREATE 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',
0a7136d4 178 `height` tinyint(3) unsigned NOT NULL default '42',
e673ee24 179 `comment` text,
86f94102 180 `thumb_data` blob,
3526c7c6
DO
181 PRIMARY KEY (`id`),
182 UNIQUE KEY `name_in_row` (`row_id`,`name`)
e673ee24
DO
183) ENGINE=MyISAM;
184
e673ee24
DO
185CREATE 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,
f187f2ec 190 `height` tinyint(3) unsigned default NULL,
e673ee24 191 `comment` text,
f187f2ec 192 `thumb_data` blob,
e673ee24
DO
193 `ctime` timestamp NOT NULL,
194 `user_name` char(64) default NULL
195) ENGINE=MyISAM;
196
e673ee24
DO
197CREATE 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
e673ee24
DO
213CREATE 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
e673ee24
DO
227CREATE 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,
e673ee24
DO
233 PRIMARY KEY (`rack_id`,`unit_no`,`atom`)
234) ENGINE=MyISAM;
235
f9bc186f
DO
236CREATE 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
36516fe5
DO
244CREATE 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
e673ee24
DO
252CREATE 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
e673ee24
DO
262CREATE 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;
8d068153 269
8d068153
DO
270CREATE TABLE `Config` (
271 `varname` char(32) NOT NULL,
5cffef42 272 `varvalue` char(255) NOT NULL,
8d068153
DO
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;