r1588 + strip comments and SQL vars from the file, ship pure CREATE TABLE queries
[racktables] / install / init-structure.sql
CommitLineData
e673ee24
DO
1CREATE TABLE `Atom` (
2 `molecule_id` int(10) unsigned default NULL,
3 `rack_id` int(10) unsigned default NULL,
4 `unit_no` int(10) unsigned default NULL,
5 `atom` enum('front','interior','rear') default NULL
6) ENGINE=MyISAM;
7
e673ee24
DO
8CREATE TABLE `Attribute` (
9 `attr_id` int(10) unsigned NOT NULL auto_increment,
10 `attr_type` enum('string','uint','float','dict') default NULL,
11 `attr_name` char(64) default NULL,
12 PRIMARY KEY (`attr_id`),
13 UNIQUE KEY `attr_name` (`attr_name`)
14) ENGINE=MyISAM AUTO_INCREMENT=10000;
15
e673ee24
DO
16CREATE TABLE `AttributeMap` (
17 `objtype_id` int(10) unsigned NOT NULL default '1',
18 `attr_id` int(10) unsigned NOT NULL default '1',
19 `chapter_no` int(10) unsigned NOT NULL,
20 UNIQUE KEY `objtype_id` (`objtype_id`,`attr_id`)
21) ENGINE=MyISAM;
22
e673ee24
DO
23CREATE TABLE `AttributeValue` (
24 `object_id` int(10) unsigned default NULL,
25 `attr_id` int(10) unsigned default NULL,
26 `string_value` char(128) default NULL,
27 `uint_value` int(10) unsigned default NULL,
28 `float_value` float default NULL,
29 UNIQUE KEY `object_id` (`object_id`,`attr_id`)
30) ENGINE=MyISAM;
31
e673ee24
DO
32CREATE TABLE `Chapter` (
33 `chapter_no` int(10) unsigned NOT NULL auto_increment,
34 `sticky` enum('yes','no') default 'no',
35 `chapter_name` char(128) NOT NULL,
36 PRIMARY KEY (`chapter_no`),
37 UNIQUE KEY `chapter_name` (`chapter_name`)
38) ENGINE=MyISAM AUTO_INCREMENT=10000;
39
e673ee24
DO
40CREATE TABLE `Dictionary` (
41 `chapter_no` int(10) unsigned NOT NULL,
42 `dict_key` int(10) unsigned NOT NULL auto_increment,
43 `dict_value` char(128) default NULL,
71dcbe12
DO
44 PRIMARY KEY (`dict_key`),
45 UNIQUE KEY `chap_to_key` (`chapter_no`,`dict_key`),
46 UNIQUE KEY `chap_to_val` (`chapter_no`,`dict_value`)
47) ENGINE=MyISAM AUTO_INCREMENT=50000;
e673ee24 48
e673ee24
DO
49CREATE TABLE `IPAddress` (
50 `ip` int(10) unsigned NOT NULL,
51 `name` char(255) NOT NULL,
52 `reserved` enum('yes','no') default NULL,
53 PRIMARY KEY (`ip`)
54) ENGINE=MyISAM;
55
e673ee24 56CREATE TABLE `IPBonds` (
ba1c6d42 57 `object_id` int(10) unsigned NOT NULL,
e673ee24
DO
58 `ip` int(10) unsigned NOT NULL,
59 `name` char(255) NOT NULL,
60 `type` enum('regular','shared','virtual') default NULL,
61 PRIMARY KEY (`object_id`,`ip`)
62) ENGINE=MyISAM;
63
192c95ce
DO
64CREATE TABLE `IPLoadBalancer` (
65 `object_id` int(10) unsigned default NULL,
66 `rspool_id` int(10) unsigned default NULL,
70c24883 67 `vs_id` int(10) unsigned default NULL,
8d350975
DO
68 `vsconfig` text,
69 `rsconfig` text,
70c24883 70 UNIQUE KEY `LB-VS` (`object_id`,`vs_id`)
192c95ce
DO
71) ENGINE=MyISAM;
72
192c95ce
DO
73CREATE TABLE `IPRSPool` (
74 `id` int(10) unsigned NOT NULL auto_increment,
192c95ce
DO
75 `name` char(255) default NULL,
76 `vsconfig` text,
77 `rsconfig` text,
78 PRIMARY KEY (`id`)
79) ENGINE=MyISAM;
80
e673ee24 81CREATE TABLE `IPRanges` (
ba1c6d42 82 `id` int(10) unsigned NOT NULL auto_increment,
e673ee24 83 `ip` int(10) unsigned NOT NULL,
ba1c6d42 84 `mask` int(10) unsigned NOT NULL,
e673ee24
DO
85 `name` char(255) default NULL,
86 PRIMARY KEY (`id`)
87) ENGINE=MyISAM;
88
192c95ce
DO
89CREATE TABLE `IPRealServer` (
90 `id` int(10) unsigned NOT NULL auto_increment,
1f7d18fa 91 `inservice` enum('yes','no') NOT NULL default 'no',
192c95ce
DO
92 `rsip` int(10) unsigned default NULL,
93 `rsport` smallint(5) unsigned default NULL,
94 `rspool_id` int(10) unsigned default NULL,
ab82514d 95 `rsconfig` text,
4ab15209 96 PRIMARY KEY (`id`),
70c24883 97 UNIQUE KEY `pool-endpoint` (`rspool_id`,`rsip`,`rsport`)
192c95ce
DO
98) ENGINE=MyISAM;
99
192c95ce
DO
100CREATE TABLE `IPVirtualService` (
101 `id` int(10) unsigned NOT NULL auto_increment,
102 `vip` int(10) unsigned default NULL,
103 `vport` smallint(5) unsigned default NULL,
104 `proto` enum('TCP','UDP') NOT NULL default 'TCP',
105 `name` char(255) default NULL,
9c3043f3
DO
106 `vsconfig` text,
107 `rsconfig` text,
70c24883 108 PRIMARY KEY (`id`)
192c95ce
DO
109) ENGINE=MyISAM;
110
e673ee24 111CREATE TABLE `Link` (
167df29b
DO
112 `porta` int(10) unsigned NOT NULL,
113 `portb` int(10) unsigned NOT NULL,
e673ee24
DO
114 PRIMARY KEY (`porta`,`portb`),
115 UNIQUE KEY `porta` (`porta`),
116 UNIQUE KEY `portb` (`portb`)
117) ENGINE=MyISAM;
118
e673ee24
DO
119CREATE TABLE `Molecule` (
120 `id` int(10) unsigned NOT NULL auto_increment,
121 PRIMARY KEY (`id`)
122) ENGINE=MyISAM;
123
e673ee24
DO
124CREATE TABLE `MountOperation` (
125 `id` int(10) unsigned NOT NULL auto_increment,
126 `object_id` int(10) unsigned NOT NULL,
127 `ctime` timestamp NOT NULL,
128 `user_name` char(64) default NULL,
129 `old_molecule_id` int(10) unsigned default NULL,
130 `new_molecule_id` int(10) unsigned default NULL,
131 `comment` text,
132 PRIMARY KEY (`id`)
133) ENGINE=MyISAM;
134
e673ee24 135CREATE TABLE `Port` (
167df29b
DO
136 `id` int(10) unsigned NOT NULL auto_increment,
137 `object_id` int(10) unsigned NOT NULL,
e673ee24 138 `name` char(255) NOT NULL,
167df29b 139 `type` int(10) unsigned NOT NULL,
e673ee24
DO
140 `l2address` char(64) default NULL,
141 `reservation_comment` char(255) default NULL,
142 `label` char(255) default NULL,
143 PRIMARY KEY (`id`),
144 UNIQUE KEY `object_id` (`object_id`,`name`),
78f44cbc
DO
145 UNIQUE KEY `l2address` (`l2address`),
146 KEY `type` (`type`)
e673ee24
DO
147) ENGINE=MyISAM;
148
e673ee24 149CREATE TABLE `PortCompat` (
71dcbe12 150 `type1` int(10) unsigned NOT NULL,
78f44cbc
DO
151 `type2` int(10) unsigned NOT NULL,
152 KEY `type1` (`type1`),
153 KEY `type2` (`type2`)
e673ee24
DO
154) ENGINE=MyISAM;
155
e673ee24 156CREATE TABLE `PortForwarding` (
ba1c6d42 157 `object_id` int(10) unsigned NOT NULL,
e673ee24
DO
158 `proto` int(11) NOT NULL,
159 `localip` int(10) unsigned NOT NULL,
ba1c6d42 160 `localport` smallint(5) unsigned NOT NULL,
e673ee24 161 `remoteip` int(10) unsigned NOT NULL,
ba1c6d42 162 `remoteport` smallint(5) unsigned NOT NULL,
e673ee24
DO
163 `description` char(255) default NULL,
164 PRIMARY KEY (`object_id`,`proto`,`localip`,`localport`,`remoteip`,`remoteport`),
165 KEY `localip` (`localip`),
166 KEY `remoteip` (`remoteip`),
167 KEY `object_id` (`object_id`)
168) ENGINE=MyISAM;
169
e673ee24
DO
170CREATE TABLE `Rack` (
171 `id` int(10) unsigned NOT NULL auto_increment,
172 `name` char(255) default NULL,
173 `deleted` enum('yes','no') NOT NULL default 'no',
174 `row_id` int(10) unsigned NOT NULL default '1',
0a7136d4 175 `height` tinyint(3) unsigned NOT NULL default '42',
e673ee24 176 `comment` text,
86f94102 177 `thumb_data` blob,
e673ee24
DO
178 PRIMARY KEY (`id`)
179) ENGINE=MyISAM;
180
e673ee24
DO
181CREATE TABLE `RackHistory` (
182 `id` int(10) unsigned default NULL,
183 `name` char(255) default NULL,
184 `deleted` enum('yes','no') default NULL,
185 `row_id` int(10) unsigned default NULL,
186 `height` int(10) unsigned default NULL,
187 `comment` text,
188 `ctime` timestamp NOT NULL,
189 `user_name` char(64) default NULL
190) ENGINE=MyISAM;
191
e673ee24
DO
192CREATE TABLE `RackObject` (
193 `id` int(10) unsigned NOT NULL auto_increment,
194 `name` char(255) default NULL,
195 `label` char(255) default NULL,
196 `barcode` char(16) default NULL,
197 `deleted` enum('yes','no') NOT NULL default 'no',
198 `objtype_id` int(10) unsigned NOT NULL default '1',
199 `asset_no` char(64) default NULL,
200 `has_problems` enum('yes','no') NOT NULL default 'no',
201 `comment` text,
202 PRIMARY KEY (`id`),
203 UNIQUE KEY `RackObject_asset_no` (`asset_no`),
204 UNIQUE KEY `name` (`name`),
205 UNIQUE KEY `barcode` (`barcode`)
206) ENGINE=MyISAM;
207
e673ee24
DO
208CREATE TABLE `RackObjectHistory` (
209 `id` int(10) unsigned default NULL,
210 `name` char(255) default NULL,
211 `label` char(255) default NULL,
212 `barcode` char(16) default NULL,
213 `deleted` enum('yes','no') default NULL,
214 `objtype_id` int(10) unsigned default NULL,
215 `asset_no` char(64) default NULL,
216 `has_problems` enum('yes','no') NOT NULL default 'no',
217 `comment` text,
218 `ctime` timestamp NOT NULL,
219 `user_name` char(64) default NULL
220) ENGINE=MyISAM;
221
e673ee24
DO
222CREATE TABLE `RackSpace` (
223 `rack_id` int(10) unsigned NOT NULL default '0',
224 `unit_no` int(10) unsigned NOT NULL default '0',
225 `atom` enum('front','interior','rear') NOT NULL default 'interior',
226 `state` enum('A','U','T','W') NOT NULL default 'A',
227 `object_id` int(10) unsigned default NULL,
228 `problem_id` int(10) unsigned default NULL,
229 PRIMARY KEY (`rack_id`,`unit_no`,`atom`)
230) ENGINE=MyISAM;
231
e673ee24
DO
232CREATE TABLE `UserAccount` (
233 `user_id` int(10) unsigned NOT NULL auto_increment,
234 `user_name` char(64) NOT NULL,
235 `user_enabled` enum('yes','no') NOT NULL default 'no',
236 `user_password_hash` char(128) default NULL,
237 `user_realname` char(64) default NULL,
238 PRIMARY KEY (`user_id`),
239 UNIQUE KEY `user_name` (`user_name`)
240) ENGINE=MyISAM AUTO_INCREMENT=10000;
241
e673ee24
DO
242CREATE TABLE `UserPermission` (
243 `user_id` int(10) unsigned NOT NULL default '0',
244 `page` char(64) NOT NULL default '%',
245 `tab` char(64) NOT NULL default '%',
246 `access` enum('yes','no') NOT NULL default 'no',
247 UNIQUE KEY `user_id` (`user_id`,`page`,`tab`)
248) ENGINE=MyISAM;
8d068153 249
8d068153
DO
250CREATE TABLE `Config` (
251 `varname` char(32) NOT NULL,
252 `varvalue` char(64) NOT NULL,
253 `vartype` enum('string','uint') NOT NULL default 'string',
254 `emptyok` enum('yes','no') NOT NULL default 'no',
255 `is_hidden` enum('yes','no') NOT NULL default 'yes',
256 `description` text,
257 PRIMARY KEY (`varname`)
258) ENGINE=MyISAM;