r4264 fix bits specific to 0.19.2
[racktables] / wwwroot / upgrade.php
1 <?php
2
3 $relnotes = array
4 (
5 '0.17.0' => <<<ENDOFTEXT
6 LDAP options have been moved to LDAP_options array. This means, that if you were
7 using LDAP authentication for users in version 0.16.x, it will break right after
8 upgrade to 0.17.0. To get things working again, adjust existing secret.php file
9 according to secret-sample.php file provided with 0.17.0 release.
10
11 This release is the first to take advantage of the foreign key support
12 provided by the InnoDB storage engine in MySQL. The installer and
13 upgrader scripts check for InnoDB support and cannot complete without it.
14 If you have trouble, the first step is to make sure the 'skip-innodb'
15 option in my.cnf is commented out.
16
17 Another change is the addition of support for file uploads. Files are stored
18 in the database. There are several settings in php.ini which you may need to modify:
19 file_uploads - needs to be On
20 upload_max_filesize - max size for uploaded files
21 post_max_size - max size of all form data submitted via POST (including files)
22
23 User accounts used to have 'enabled' flag, which allowed individual blocking and
24 unblocking of each. This flag was dropped in favor of existing mean of access
25 setup (RackCode). An unconditional denying rule is automatically added into RackCode
26 for such blocked account, so the effective security policy remains the same.
27 ENDOFTEXT
28 ,
29 '0.18.2' => <<<ENDOFTEXT
30 RackTables from its version 0.18.0 and later is not compatible with
31 RHEL/CentOS (at least with versions up to 5.5) Linux distributions
32 in their default installation. There are yet options to work around that:
33 1. Install RackTables on a server with a different distribution/OS.
34 2. Request Linux distribution vendor to fix the bug with PCRE.
35 3. Repair your RHEL/CentOS installation yourself by fixing its PCRE
36 RPM as explained here: http://bugs.centos.org/view.php?id=3252
37 ENDOFTEXT
38 ,
39 '0.19.0' => <<<ENDOFTEXT
40 The files, which are intended for the httpd (web-server) directory, are
41 now in the "wwwroot" directory of the tar.gz archive. Files outside of
42 that directory are not directly intended for httpd environment and should
43 not be copied to the server.
44
45 This release incorporates ObjectLog functionality, which used to be
46 available as a separate plugin. For the best results it is advised to
47 disable (through local.php) external ObjectLog plugin permanently before
48 the new version is installed. All previously accumulated ObjectLog records
49 will be available through the updated standard interface.
50
51 RackTables is now using PHP JSON extension which is included in the PHP
52 core since 5.2.0.
53
54 The barcode attribute was removed. The upgrade script attempts to
55 preserve the data by moving it to either the 'OEM S/N 1' attribute or to
56 a Log entry. You should backup your database beforehand anyway.
57 ENDOFTEXT
58 ,
59 '0.19.2' => <<<ENDOFTEXT
60 This release is different in filesystem layout. The "gateways" directory
61 has been moved from "wwwroot" directory. This improves security, but may
62 lead to gateways extension not working by default. If you need any of the
63 gateway-dependent features (802.1Q, live ports, sendfile) working, the
64 recommended way is to put "racktables_gwdir" variable to secret.php file
65 with the full filesystem path to "gateways" directory on the server, e.g.:
66
67 \$racktables_gwdir = '/usr/local/racktables/gateways';
68 ENDOFTEXT
69 ,
70 );
71
72 // At the moment we assume, that for any two releases we can
73 // sequentally execute all batches, that separate them, and
74 // nothing will break. If this changes one day, the function
75 // below will have to generate smarter upgrade paths, while
76 // the upper layer will remain the same.
77 // Returning an empty array means that no upgrade is necessary.
78 // Returning NULL indicates an error.
79 function getDBUpgradePath ($v1, $v2)
80 {
81 $versionhistory = array
82 (
83 '0.16.4',
84 '0.16.5',
85 '0.16.6',
86 '0.17.0',
87 '0.17.1',
88 '0.17.2',
89 '0.17.3',
90 '0.17.4',
91 '0.17.5',
92 '0.17.6',
93 '0.17.7',
94 '0.17.8',
95 '0.17.9',
96 '0.17.10',
97 '0.17.11',
98 '0.18.0',
99 '0.18.1',
100 '0.18.2',
101 '0.18.3',
102 '0.18.4',
103 '0.18.5',
104 '0.18.6',
105 '0.18.7',
106 '0.19.0',
107 '0.19.1',
108 '0.19.2',
109 );
110 if (!in_array ($v1, $versionhistory) or !in_array ($v2, $versionhistory))
111 return NULL;
112 $skip = TRUE;
113 $path = NULL;
114 // Now collect all versions > $v1 and <= $v2
115 foreach ($versionhistory as $v)
116 {
117 if ($skip and $v == $v1)
118 {
119 $skip = FALSE;
120 $path = array();
121 continue;
122 }
123 if ($skip)
124 continue;
125 $path[] = $v;
126 if ($v == $v2)
127 break;
128 }
129 return $path;
130 }
131
132 // Upgrade batches are named exactly as the release where they first appear.
133 // That is simple, but seems sufficient for beginning.
134 function executeUpgradeBatch ($batchid)
135 {
136 $query = array();
137 global $dbxlink;
138 switch ($batchid)
139 {
140 case '0.16.5':
141 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('IPV4_TREE_SHOW_USAGE','yes','string','no','no','Show address usage in IPv4 tree')";
142 $query[] = "update Config set varvalue = '0.16.5' where varname = 'DB_VERSION'";
143 break;
144 case '0.16.6':
145 $query[] = "update Config set varvalue = '0.16.6' where varname = 'DB_VERSION'";
146 break;
147 case '0.17.0':
148 // create tables for storing files (requires InnoDB support)
149 if (!isInnoDBSupported ())
150 {
151 showError ("Cannot upgrade because InnoDB tables are not supported by your MySQL server. See the README for details.");
152 die;
153 }
154
155 $query[] = "alter table Chapter change chapter_no id int(10) unsigned NOT NULL auto_increment";
156 $query[] = "alter table Chapter change chapter_name name char(128) NOT NULL";
157 $query[] = "alter table Chapter drop key chapter_name";
158 $query[] = "alter table Chapter add UNIQUE KEY name (name)";
159 $query[] = "alter table Attribute change attr_id id int(10) unsigned NOT NULL auto_increment";
160 $query[] = "alter table Attribute change attr_type type enum('string','uint','float','dict') default NULL";
161 $query[] = "alter table Attribute change attr_name name char(64) default NULL";
162 $query[] = "alter table Attribute drop key attr_name";
163 $query[] = "alter table Attribute add UNIQUE KEY name (name)";
164 $query[] = "alter table AttributeMap change chapter_no chapter_id int(10) unsigned NOT NULL";
165 $query[] = "alter table Dictionary change chapter_no chapter_id int(10) unsigned NOT NULL";
166 // Only after the above call it is Ok to use reloadDictionary()
167 $query = array_merge ($query, reloadDictionary ($batchid));
168 // schema changes for file management
169 $query[] = "
170 CREATE TABLE `File` (
171 `id` int(10) unsigned NOT NULL auto_increment,
172 `name` char(255) NOT NULL,
173 `type` char(255) NOT NULL,
174 `size` int(10) unsigned NOT NULL,
175 `ctime` datetime NOT NULL,
176 `mtime` datetime NOT NULL,
177 `atime` datetime NOT NULL,
178 `contents` longblob NOT NULL,
179 `comment` text,
180 PRIMARY KEY (`id`),
181 UNIQUE KEY `name` (`name`)
182 ) ENGINE=InnoDB";
183 $query[] = "
184 CREATE TABLE `FileLink` (
185 `id` int(10) unsigned NOT NULL auto_increment,
186 `file_id` int(10) unsigned NOT NULL,
187 `entity_type` enum('ipv4net','ipv4rspool','ipv4vs','object','rack','user') NOT NULL default 'object',
188 `entity_id` int(10) NOT NULL,
189 PRIMARY KEY (`id`),
190 UNIQUE KEY `FileLink-unique` (`file_id`,`entity_type`,`entity_id`),
191 KEY `FileLink-file_id` (`file_id`),
192 CONSTRAINT `FileLink-File_fkey` FOREIGN KEY (`file_id`) REFERENCES `File` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
193 ) ENGINE=InnoDB";
194 $query[] = "ALTER TABLE TagStorage MODIFY COLUMN target_realm enum('file','ipv4net','ipv4rspool','ipv4vs','object','rack','user') NOT NULL default 'object'";
195
196 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (24,'no','network security models')";
197 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (25,'no','wireless models')";
198 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,1,0)";
199 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,2,24)";
200 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,3,0)";
201 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,5,0)";
202 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,14,0)";
203 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,16,0)";
204 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,17,0)";
205 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,18,0)";
206 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,20,0)";
207 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,21,0)";
208 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,22,0)";
209 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,24,0)";
210 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (965,1,0)";
211 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (965,3,0)";
212 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (965,2,25)";
213 $query[] = 'alter table IPBonds rename to IPv4Allocation';
214 $query[] = 'alter table PortForwarding rename to IPv4NAT';
215 $query[] = 'alter table IPRanges rename to IPv4Network';
216 $query[] = 'alter table IPAddress rename to IPv4Address';
217 $query[] = 'alter table IPLoadBalancer rename to IPv4LB';
218 $query[] = 'alter table IPRSPool rename to IPv4RSPool';
219 $query[] = 'alter table IPRealServer rename to IPv4RS';
220 $query[] = 'alter table IPVirtualService rename to IPv4VS';
221 $query[] = "alter table TagStorage change column target_realm entity_realm enum('file','ipv4net','ipv4vs','ipv4rspool','object','rack','user') NOT NULL default 'object'";
222 $query[] = 'alter table TagStorage change column target_id entity_id int(10) unsigned NOT NULL';
223 $query[] = 'alter table TagStorage drop key entity_tag';
224 $query[] = 'alter table TagStorage drop key target_id';
225 $query[] = 'alter table TagStorage add UNIQUE KEY `entity_tag` (`entity_realm`,`entity_id`,`tag_id`)';
226 $query[] = 'alter table TagStorage add KEY `entity_id` (`entity_id`)';
227 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('PREVIEW_TEXT_MAXCHARS','10240','uint','yes','no','Max chars for text file preview')";
228 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('PREVIEW_TEXT_ROWS','25','uint','yes','no','Rows for text file preview')";
229 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('PREVIEW_TEXT_COLS','80','uint','yes','no','Columns for text file preview')";
230 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('PREVIEW_IMAGE_MAXPXS','320','uint','yes','no','Max pixels per axis for image file preview')";
231 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('VENDOR_SIEVE','','string','yes','no','Vendor sieve configuration')";
232 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('IPV4LB_LISTSRC','{\$typeid_4}','string','yes','no','List source: IPv4 load balancers')";
233 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('IPV4OBJ_LISTSRC','{\$typeid_4} or {\$typeid_7} or {\$typeid_8} or {\$typeid_12} or {\$typeid_445} or {\$typeid_447}','string','yes','no','List source: IPv4-enabled objects')";
234 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('IPV4NAT_LISTSRC','{\$typeid_4} or {\$typeid_7} or {\$typeid_8}','string','yes','no','List source: IPv4 NAT performers')";
235 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('ASSETWARN_LISTSRC','{\$typeid_4} or {\$typeid_7} or {\$typeid_8}','string','yes','no','List source: object, for which asset tag should be set')";
236 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('NAMEWARN_LISTSRC','{\$typeid_4} or {\$typeid_7} or {\$typeid_8}','string','yes','no','List source: object, for which common name should be set')";
237 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('RACKS_PER_ROW','12','unit','yes','no','Racks per row')";
238 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('FILTER_PREDICATE_SIEVE','','string','yes','no','Predicate sieve regex(7)')";
239 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('FILTER_DEFAULT_ANDOR','or','string','no','no','Default list filter boolean operation (or/and)')";
240 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('FILTER_SUGGEST_ANDOR','yes','string','no','no','Suggest and/or selector in list filter')";
241 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('FILTER_SUGGEST_TAGS','yes','string','no','no','Suggest tags in list filter')";
242 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('FILTER_SUGGEST_PREDICATES','yes','string','no','no','Suggest predicates in list filter')";
243 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('FILTER_SUGGEST_EXTRA','no','string','no','no','Suggest extra expression in list filter')";
244 $query[] = "delete from Config where varname = 'USER_AUTH_SRC'";
245 $query[] = "delete from Config where varname = 'COOKIE_TTL'";
246 $query[] = "delete from Config where varname = 'rtwidth_0'";
247 $query[] = "delete from Config where varname = 'rtwidth_1'";
248 $query[] = "delete from Config where varname = 'rtwidth_2'";
249 $query[] = "delete from Config where varname = 'NAMEFUL_OBJTYPES'";
250 $query[] = "delete from Config where varname = 'REQUIRE_ASSET_TAG_FOR'";
251 $query[] = "delete from Config where varname = 'IPV4_PERFORMERS'";
252 $query[] = "delete from Config where varname = 'NATV4_PERFORMERS'";
253 $query[] = "alter table TagTree add column valid_realm set('file','ipv4net','ipv4vs','ipv4rspool','object','rack','user') not null default 'file,ipv4net,ipv4vs,ipv4rspool,object,rack,user' after parent_id";
254 $result = $dbxlink->query ("select user_id, user_name, user_realname from UserAccount where user_enabled = 'no'");
255 while ($row = $result->fetch (PDO::FETCH_ASSOC))
256 $query[] = "update Script set script_text = concat('deny {\$userid_${row['user_id']}} # ${row['user_name']} (${row['user_realname']})\n', script_text) where script_name = 'RackCode'";
257 $query[] = "update Script set script_text = NULL where script_name = 'RackCodeCache'";
258 unset ($result);
259 $query[] = "alter table UserAccount drop column user_enabled";
260
261 $query[] = "CREATE TABLE RackRow ( id int(10) unsigned NOT NULL auto_increment, name char(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM";
262
263 $result = $dbxlink->query ("select dict_key, dict_value from Dictionary where chapter_no = 3");
264 while($row = $result->fetch(PDO::FETCH_NUM))
265 $query[] = "insert into RackRow set id=${row[0]}, name='${row[1]}'";
266 unset ($result);
267 $query[] = "delete from Dictionary where chapter_id = 3";
268 $query[] = "delete from Chapter where id = 3";
269 $query[] = "
270 CREATE TABLE `LDAPCache` (
271 `presented_username` char(64) NOT NULL,
272 `successful_hash` char(40) NOT NULL,
273 `first_success` timestamp NOT NULL default CURRENT_TIMESTAMP,
274 `last_retry` timestamp NOT NULL default '0000-00-00 00:00:00',
275 `displayed_name` char(128) default NULL,
276 `memberof` text,
277 UNIQUE KEY `presented_username` (`presented_username`),
278 KEY `scanidx` (`presented_username`,`successful_hash`)
279 ) ENGINE=InnoDB;";
280 $query[] = "alter table UserAccount modify column user_password_hash char(40) NULL";
281 $query[] = 'ALTER TABLE Rack DROP COLUMN deleted';
282 $query[] = 'ALTER TABLE RackHistory DROP COLUMN deleted';
283 $query[] = 'ALTER TABLE RackObject DROP COLUMN deleted';
284 $query[] = 'ALTER TABLE RackObjectHistory DROP COLUMN deleted';
285 // Can't be added straight due to many duplicates, even in "dictbase" data.
286 $result = $dbxlink->query ('SELECT type1, type2, count(*) - 1 as excess FROM PortCompat GROUP BY type1, type2 HAVING excess > 0');
287 while ($row = $result->fetch (PDO::FETCH_ASSOC))
288 $query[] = "DELETE FROM PortCompat WHERE type1 = ${row['type1']} AND type2 = ${row['type2']} limit ${row['excess']}";
289 unset ($result);
290 $query[] = 'ALTER TABLE PortCompat DROP KEY type1';
291 $query[] = 'ALTER TABLE PortCompat ADD UNIQUE `type1_2` (type1, type2)';
292 $query[] = "UPDATE Config SET varvalue = '0.17.0' WHERE varname = 'DB_VERSION'";
293
294 break;
295 case '0.17.1':
296 $query[] = "ALTER TABLE Dictionary DROP KEY `chap_to_key`";
297 $query = array_merge ($query, reloadDictionary ($batchid));
298 // Token set has changed, so the cache isn't valid any more.
299 $query[] = "UPDATE Script SET script_text = NULL WHERE script_name = 'RackCodeCache'";
300 $query[] = "UPDATE Config SET varvalue = '0.17.1' WHERE varname = 'DB_VERSION'";
301 break;
302 case '0.17.2':
303 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (26,'no','fibre channel switch models')";
304 $query = array_merge ($query, reloadDictionary ($batchid));
305 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1055,2,26)";
306 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('DEFAULT_SNMP_COMMUNITY','public','string','no','no','Default SNMP Community string')";
307 // wipe irrelevant records (ticket:250)
308 $query[] = "DELETE FROM TagStorage WHERE entity_realm = 'file' AND entity_id NOT IN (SELECT id FROM File)";
309 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('IPV4_ENABLE_KNIGHT','yes','string','no','no','Enable IPv4 knight feature')";
310 $query[] = "ALTER TABLE IPv4Network ADD COLUMN comment text AFTER name";
311 $query[] = "ALTER TABLE Port ADD INDEX comment (reservation_comment)";
312 $query[] = "ALTER TABLE Port DROP KEY l2address"; // UNIQUE
313 $query[] = "ALTER TABLE Port ADD KEY (l2address)"; // not UNIQUE
314 $query[] = "ALTER TABLE Port DROP KEY object_id";
315 $query[] = "ALTER TABLE Port ADD UNIQUE KEY per_object (object_id, name, type)";
316 $query[] = "INSERT INTO PortCompat (type1, type2) VALUES (20,1083)";
317 $query[] = "INSERT INTO PortCompat (type1, type2) VALUES (21,1083)";
318 $query[] = "INSERT INTO PortCompat (type1, type2) VALUES (1077,1077)";
319 $query[] = "INSERT INTO PortCompat (type1, type2) VALUES (1083,20)";
320 $query[] = "INSERT INTO PortCompat (type1, type2) VALUES (1083,21)";
321 $query[] = "INSERT INTO PortCompat (type1, type2) VALUES (1083,1083)";
322 $query[] = "INSERT INTO PortCompat (type1, type2) VALUES (1087,1087)";
323 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (27,'no','PDU models')";
324 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (2,2,27)";
325 $query[] = "UPDATE Config SET varvalue = '0.17.2' WHERE varname = 'DB_VERSION'";
326 break;
327 case '0.17.3':
328 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('TAGS_TOPLIST_SIZE','50','uint','yes','no','Tags top list size')";
329 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('TAGS_QUICKLIST_SIZE','20','uint','no','no','Tags quick list size')";
330 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('TAGS_QUICKLIST_THRESHOLD','50','uint','yes','no','Tags quick list threshold')";
331 $query[] = "ALTER TABLE AttributeMap MODIFY COLUMN chapter_id int(10) unsigned NULL";
332 $query[] = "UPDATE AttributeMap SET chapter_id = NULL WHERE attr_id IN (SELECT id FROM Attribute WHERE type != 'dict')";
333 // ticket:239
334 $query[] = 'UPDATE AttributeValue SET uint_value = 1018 WHERE uint_value = 731 AND attr_id IN (SELECT attr_id FROM AttributeMap WHERE chapter_id = 12)';
335 $query[] = 'DELETE FROM Dictionary WHERE dict_key = 731';
336 $query = array_merge ($query, reloadDictionary ($batchid));
337 $query[] = "UPDATE Config SET vartype='uint' WHERE varname='RACKS_PER_ROW'";
338 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('ENABLE_MULTIPORT_FORM','no','string','no','no','Enable \"Add/update multiple ports\" form')";
339 $query[] = "UPDATE Config SET varvalue = '0.17.3' WHERE varname = 'DB_VERSION'";
340 break;
341 case '0.17.4':
342 $query[] = "ALTER TABLE Link ENGINE=InnoDB";
343 $query[] = "ALTER TABLE Port ENGINE=InnoDB";
344 $query[] = "ALTER TABLE IPv4RS ENGINE=InnoDB";
345 $query[] = "ALTER TABLE IPv4RSPool ENGINE=InnoDB";
346 $query[] = "ALTER TABLE AttributeValue ENGINE=InnoDB";
347 $query[] = "ALTER TABLE RackObject ENGINE=InnoDB";
348 $query[] = "ALTER TABLE IPv4NAT ENGINE=InnoDB";
349 $query[] = "ALTER TABLE IPv4LB ENGINE=InnoDB";
350 $query[] = "ALTER TABLE IPv4VS ENGINE=InnoDB";
351 $query[] = "DELETE FROM IPv4RS WHERE rspool_id NOT IN (SELECT id FROM IPv4RSPool)";
352 $query[] = "ALTER TABLE Link ADD CONSTRAINT `Link-FK-b` FOREIGN KEY (portb) REFERENCES Port (id)";
353 $query[] = "ALTER TABLE Link ADD CONSTRAINT `Link-FK-a` FOREIGN KEY (porta) REFERENCES Port (id)";
354 $query[] = "ALTER TABLE IPv4RS ADD CONSTRAINT `IPv4RS-FK` FOREIGN KEY (rspool_id) REFERENCES IPv4RSPool (id) ON DELETE CASCADE";
355 $query[] = "ALTER TABLE AttributeValue ADD CONSTRAINT `AttributeValue-FK-object_id` FOREIGN KEY (object_id) REFERENCES RackObject (id)";
356 $query[] = "ALTER TABLE IPv4NAT ADD CONSTRAINT `IPv4NAT-FK-object_id` FOREIGN KEY (object_id) REFERENCES RackObject (id)";
357 $query[] = "ALTER TABLE Port ADD CONSTRAINT `Port-FK-object_id` FOREIGN KEY (object_id) REFERENCES RackObject (id)";
358 $query[] = "ALTER TABLE IPv4LB ADD CONSTRAINT `IPv4LB-FK-rspool_id` FOREIGN KEY (rspool_id) REFERENCES IPv4RSPool (id)";
359 $query[] = "ALTER TABLE IPv4LB ADD CONSTRAINT `IPv4LB-FK-object_id` FOREIGN KEY (object_id) REFERENCES RackObject (id)";
360 $query[] = "ALTER TABLE IPv4LB ADD CONSTRAINT `IPv4LB-FK-vs_id` FOREIGN KEY (vs_id) REFERENCES IPv4VS (id)";
361 $query = array_merge ($query, reloadDictionary ($batchid));
362 $query[] = "UPDATE Config SET varvalue = '0.17.4' WHERE varname = 'DB_VERSION'";
363 break;
364 case '0.17.5':
365 $query[] = "ALTER TABLE TagTree ENGINE=InnoDB";
366 $query[] = "ALTER TABLE TagStorage ENGINE=InnoDB";
367 $query[] = "ALTER TABLE TagStorage ADD CONSTRAINT `TagStorage-FK-tag_id` FOREIGN KEY (tag_id) REFERENCES TagTree (id)";
368 $query[] = "ALTER TABLE TagTree ADD CONSTRAINT `TagTree-K-parent_id` FOREIGN KEY (parent_id) REFERENCES TagTree (id)";
369 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (21,1195)';
370 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (22,1196)';
371 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (23,1196)';
372 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (20,1195)';
373 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (25,1202)';
374 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (26,1202)';
375 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (27,1204)';
376 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (28,1204)';
377 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1083,1195)';
378 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1084,1084)';
379 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1195,20)';
380 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1195,21)';
381 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1195,1083)';
382 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1195,1195)';
383 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1196,22)';
384 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1196,23)';
385 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1196,1196)';
386 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1197,1197)';
387 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1198,1199)';
388 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1199,1198)';
389 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1200,1200)';
390 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1201,1201)';
391 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1202,25)';
392 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1202,26)';
393 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1202,1202)';
394 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1203,1203)';
395 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1204,27)';
396 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1204,28)';
397 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1204,1204)';
398 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1205,1205)';
399 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1206,1207)';
400 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1207,1206)';
401 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1316,1316)';
402 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (16, 1322)';
403 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1322, 16)';
404 $query[] = 'DELETE FROM PortCompat WHERE type1 = 16 AND type2 = 16';
405 for ($i = 1209; $i <= 1300; $i++)
406 $query[] = "INSERT INTO PortCompat (type1, type2) VALUES (${i}, ${i})";
407 $query[] = "
408 CREATE TABLE `PortInnerInterface` (
409 `id` int(10) unsigned NOT NULL,
410 `iif_name` char(16) NOT NULL,
411 PRIMARY KEY (`id`),
412 UNIQUE KEY `iif_name` (`iif_name`)
413 ) ENGINE=InnoDB";
414 $query[] = "INSERT INTO `PortInnerInterface` VALUES (1,'hardwired')";
415 $query[] = "INSERT INTO `PortInnerInterface` VALUES (2,'SFP-100')";
416 $query[] = "INSERT INTO `PortInnerInterface` VALUES (3,'GBIC')";
417 $query[] = "INSERT INTO `PortInnerInterface` VALUES (4,'SFP-1000')";
418 $query[] = "INSERT INTO `PortInnerInterface` VALUES (5,'XENPAK')";
419 $query[] = "INSERT INTO `PortInnerInterface` VALUES (6,'X2')";
420 $query[] = "INSERT INTO `PortInnerInterface` VALUES (7,'XPAK')";
421 $query[] = "INSERT INTO `PortInnerInterface` VALUES (8,'XFP')";
422 $query[] = "INSERT INTO `PortInnerInterface` VALUES (9,'SFP+')";
423 $query[] = "
424 CREATE TABLE `PortInterfaceCompat` (
425 `iif_id` int(10) unsigned NOT NULL,
426 `oif_id` int(10) unsigned NOT NULL,
427 UNIQUE KEY `pair` (`iif_id`,`oif_id`),
428 CONSTRAINT `PortInterfaceCompat-FK-iif_id` FOREIGN KEY (`iif_id`) REFERENCES `PortInnerInterface` (`id`)
429 ) ENGINE=InnoDB";
430 $query[] = "ALTER TABLE Port ADD COLUMN iif_id int unsigned NOT NULL AFTER name"; // will set iif_id to 0
431 $query[] = "UPDATE Port SET iif_id = 2 WHERE type = 1208";
432 $query[] = "UPDATE Port SET iif_id = 3 WHERE type = 1078";
433 $query[] = "UPDATE Port SET iif_id = 4 WHERE type = 1077";
434 $query[] = "UPDATE Port SET iif_id = 5 WHERE type = 1079";
435 $query[] = "UPDATE Port SET iif_id = 6 WHERE type = 1080";
436 $query[] = "UPDATE Port SET iif_id = 7 WHERE type = 1081";
437 $query[] = "UPDATE Port SET iif_id = 8 WHERE type = 1082";
438 $query[] = "UPDATE Port SET iif_id = 9 WHERE type = 1084";
439 $query[] = "UPDATE Port SET iif_id = 1 WHERE iif_id = 0";
440 $query[] = 'ALTER TABLE Port ADD UNIQUE `object_iif_oif_name` (object_id, iif_id, type, name)';
441 $query[] = 'ALTER TABLE Port DROP KEY `per_object`';
442 $base1000 = array (24, 34, 1202, 1203, 1204, 1205, 1206, 1207);
443 $base10000 = array (30, 35, 36, 37, 38, 39, 40);
444 $PICdata = array
445 (
446 1 => array (16, 19, 24, 29, 31, 33, 446, 681, 682, 1322),
447 2 => array (1208, 1195, 1196, 1197, 1198, 1199, 1200, 1201),
448 3 => array_merge (array (1078), $base1000),
449 4 => array_merge (array (1077), $base1000),
450 5 => array_merge (array (1079), $base10000),
451 6 => array_merge (array (1080), $base10000),
452 7 => array_merge (array (1081), $base10000),
453 8 => array_merge (array (1082), $base10000),
454 9 => array_merge (array (1084), $base10000),
455 );
456 // make sure all IIF/OIF pairs referenced from Port exist in PortInterfaceCompat before enabling FK
457 // iif_id doesn't exist at this point
458 $result = $dbxlink->query ('SELECT DISTINCT type FROM Port WHERE type NOT IN (1208, 1078, 1077, 1079, 1080, 1081, 1082, 1084)');
459 while ($row = $result->fetch (PDO::FETCH_ASSOC))
460 if (FALSE === array_search ($row['type'], $PICdata[1]))
461 array_push ($PICdata[1], $row['type']);
462 unset ($result);
463 foreach ($PICdata as $iif_id => $oif_ids)
464 foreach ($oif_ids as $oif_id)
465 $query[] = "INSERT INTO PortInterfaceCompat (iif_id, oif_id) VALUES (${iif_id}, ${oif_id})";
466 $query[] = "ALTER TABLE Port ADD CONSTRAINT `Port-FK-iif-oif` FOREIGN KEY (`iif_id`, `type`) REFERENCES `PortInterfaceCompat` (`iif_id`, `oif_id`)";
467 $query[] = 'UPDATE Port SET type = 1322 WHERE type = 16 AND (SELECT objtype_id FROM RackObject WHERE id = object_id) IN (2, 12)';
468 $query = array_merge ($query, reloadDictionary ($batchid));
469 $query[] = "DELETE FROM Config WHERE varname = 'default_port_type'";
470 $query[] = "INSERT INTO Config VALUES ('DEFAULT_PORT_IIF_ID','1','uint','no','no','Default port inner interface ID')";
471 $query[] = "INSERT INTO Config VALUES ('DEFAULT_PORT_OIF_IDS','1=24; 3=1078; 4=1077; 5=1079; 6=1080; 8=1082; 9=1084','string','no','no','Default port outer interface IDs')";
472 $query[] = "INSERT INTO Config VALUES ('IPV4_TREE_RTR_AS_CELL','yes','string','no','no','Show full router info for each network in IPv4 tree view')";
473 $query[] = "UPDATE Chapter SET name = 'PortOuterInterface' WHERE id = 2";
474 // remap refs to duplicate records, which will be discarded (ticket:286)
475 $query[] = 'UPDATE AttributeValue SET uint_value = 147 WHERE uint_value = 1020 AND attr_id = 2';
476 $query[] = 'UPDATE AttributeValue SET uint_value = 377 WHERE uint_value = 1021 AND attr_id = 2';
477 $query[] = 'INSERT INTO AttributeMap (objtype_id, attr_id) VALUES (2, 1), (2, 3), (2, 5)';
478 $query[] = "UPDATE Config SET varvalue = '0.17.5' WHERE varname = 'DB_VERSION'";
479 break;
480 case '0.17.6':
481 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (28,'no','Voice/video hardware')";
482 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1323,1,NULL)";
483 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1323,2,28)";
484 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1323,3,NULL)";
485 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1323,5,NULL)";
486 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('PROXIMITY_RANGE','0','uint','yes','no','Proximity range (0 is current rack only)')";
487 $query = array_merge ($query, reloadDictionary ($batchid));
488 $query[] = "UPDATE Config SET varvalue = '0.17.6' WHERE varname = 'DB_VERSION'";
489 break;
490 case '0.17.7':
491 $query[] = "UPDATE Config SET varvalue = '0.17.7' WHERE varname = 'DB_VERSION'";
492 break;
493 case '0.17.8':
494 $query = array_merge ($query, reloadDictionary ($batchid));
495 $query[] = "ALTER TABLE TagTree DROP COLUMN valid_realm";
496 $query[] = "UPDATE Config SET varvalue = '0.17.8' WHERE varname = 'DB_VERSION'";
497 break;
498 case '0.17.9':
499 $query[] = "ALTER table Config add `is_userdefined` enum('yes','no') NOT NULL default 'no' AFTER `is_hidden`";
500 $query[] = "
501 CREATE TABLE `UserConfig` (
502 `varname` char(32) NOT NULL,
503 `varvalue` char(255) NOT NULL,
504 `user` char(64) NOT NULL,
505 UNIQUE KEY `user_varname` (`user`,`varname`)
506 ) TYPE=InnoDB";
507 $query[] = "UPDATE Config SET is_userdefined = 'yes' WHERE varname IN
508 (
509 'MASSCOUNT',
510 'MAXSELSIZE',
511 'ROW_SCALE',
512 'PORTS_PER_ROW',
513 'IPV4_ADDRS_PER_PAGE',
514 'DEFAULT_RACK_HEIGHT',
515 'DEFAULT_SLB_VS_PORT',
516 'DEFAULT_SLB_RS_PORT',
517 'DETECT_URLS',
518 'RACK_PRESELECT_THRESHOLD',
519 'DEFAULT_IPV4_RS_INSERVICE',
520 'DEFAULT_OBJECT_TYPE',
521 'SHOW_EXPLICIT_TAGS',
522 'SHOW_IMPLICIT_TAGS',
523 'SHOW_AUTOMATIC_TAGS',
524 'IPV4_AUTO_RELEASE',
525 'SHOW_LAST_TAB',
526 'EXT_IPV4_VIEW',
527 'TREE_THRESHOLD',
528 'ADDNEW_AT_TOP',
529 'IPV4_TREE_SHOW_USAGE',
530 'PREVIEW_TEXT_MAXCHARS',
531 'PREVIEW_TEXT_ROWS',
532 'PREVIEW_TEXT_COLS',
533 'PREVIEW_IMAGE_MAXPXS',
534 'VENDOR_SIEVE',
535 'RACKS_PER_ROW'
536 )";
537 $query[] = "UPDATE Config SET varvalue = '0.17.9' WHERE varname = 'DB_VERSION'";
538 break;
539 case '0.17.10':
540 $query = array_merge ($query, reloadDictionary ($batchid));
541 $query[] = "ALTER TABLE MountOperation ADD KEY (object_id)";
542 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('STATIC_FILTER','yes','string','no','no','yes','Enable Filter Caching');";
543 $query[] = "UPDATE Config SET varvalue = '0.17.10' WHERE varname = 'DB_VERSION'";
544 break;
545 case '0.17.11':
546 $query = array_merge ($query, reloadDictionary ($batchid));
547 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('ENABLE_BULKPORT_FORM','yes','string','no','no','yes','Enable \"Bulk Port\" form');";
548 $query[] = "DELETE AttributeValue FROM AttributeValue JOIN Attribute where AttributeValue.attr_id = Attribute.id AND Attribute.type = 'dict' AND AttributeValue.uint_value = 0";
549 $query[] = "UPDATE Config SET varvalue = '0.17.11' WHERE varname = 'DB_VERSION'";
550 break;
551 case '0.18.0':
552 $query = array_merge ($query, reloadDictionary ($batchid));
553 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('VLANSWITCH_LISTSRC', '', 'string', 'yes', 'no', 'yes', 'List of VLAN running switches')";
554 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('VLANIPV4NET_LISTSRC', '', 'string', 'yes', 'no', 'yes', 'List of VLAN-based IPv4 networks')";
555 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('DEFAULT_VDOM_ID','','uint','yes','no','yes','Default VLAN domain ID')";
556 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('DEFAULT_VST_ID','','uint','yes','no','yes','Default VLAN switch template ID')";
557 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('8021Q_DEPLOY_MINAGE','300','uint','no','no','no','802.1Q deploy minimum age')";
558 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('8021Q_DEPLOY_MAXAGE','3600','uint','no','no','no','802.1Q deploy maximum age')";
559 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('8021Q_DEPLOY_RETRY','10800','uint','no','no','no','802.1Q deploy retry timer')";
560 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('8021Q_WRI_AFTER_CONFT','no','string','no','no','no','802.1Q: save device configuration after deploy')";
561 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('8021Q_INSTANT_DEPLOY','no','string','no','no','yes','802.1Q: instant deploy')";
562 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('IPV4_TREE_SHOW_VLAN','yes','string','no','no','yes','Show VLAN for each network in IPv4 tree')";
563 $query[] = "ALTER TABLE IPv4Network ENGINE=InnoDB";
564 $query[] = "SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0";
565 $query[] = "
566 CREATE TABLE `CachedPAV` (
567 `object_id` int(10) unsigned NOT NULL,
568 `port_name` char(255) NOT NULL,
569 `vlan_id` int(10) unsigned NOT NULL default '0',
570 PRIMARY KEY (`object_id`,`port_name`,`vlan_id`),
571 KEY `vlan_id` (`vlan_id`),
572 CONSTRAINT `CachedPAV-FK-object-port` FOREIGN KEY (`object_id`, `port_name`) REFERENCES `CachedPVM` (`object_id`, `port_name`) ON DELETE CASCADE,
573 CONSTRAINT `CachedPAV-FK-vlan_id` FOREIGN KEY (`vlan_id`) REFERENCES `VLANValidID` (`vlan_id`)
574 ) ENGINE=InnoDB
575 ";
576 $query[] = "
577 CREATE TABLE `CachedPNV` (
578 `object_id` int(10) unsigned NOT NULL,
579 `port_name` char(255) NOT NULL,
580 `vlan_id` int(10) unsigned NOT NULL default '0',
581 PRIMARY KEY (`object_id`,`port_name`,`vlan_id`),
582 UNIQUE KEY `port_id` (`object_id`,`port_name`),
583 CONSTRAINT `CachedPNV-FK-compound` FOREIGN KEY (`object_id`, `port_name`, `vlan_id`) REFERENCES `CachedPAV` (`object_id`, `port_name`, `vlan_id`) ON DELETE CASCADE
584 ) ENGINE=InnoDB
585 ";
586 $query[] = "
587 CREATE TABLE `CachedPVM` (
588 `object_id` int(10) unsigned NOT NULL,
589 `port_name` char(255) NOT NULL,
590 `vlan_mode` enum('access','trunk') NOT NULL default 'access',
591 PRIMARY KEY (`object_id`,`port_name`),
592 CONSTRAINT `CachedPVM-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE
593 ) ENGINE=InnoDB
594 ";
595 $query[] = "
596 CREATE TABLE `PortAllowedVLAN` (
597 `object_id` int(10) unsigned NOT NULL,
598 `port_name` char(255) NOT NULL,
599 `vlan_id` int(10) unsigned NOT NULL default '0',
600 PRIMARY KEY (`object_id`,`port_name`,`vlan_id`),
601 KEY `vlan_id` (`vlan_id`),
602 CONSTRAINT `PortAllowedVLAN-FK-object-port` FOREIGN KEY (`object_id`, `port_name`) REFERENCES `PortVLANMode` (`object_id`, `port_name`) ON DELETE CASCADE,
603 CONSTRAINT `PortAllowedVLAN-FK-vlan_id` FOREIGN KEY (`vlan_id`) REFERENCES `VLANValidID` (`vlan_id`)
604 ) ENGINE=InnoDB
605 ";
606 $query[] = "
607 CREATE TABLE `PortNativeVLAN` (
608 `object_id` int(10) unsigned NOT NULL,
609 `port_name` char(255) NOT NULL,
610 `vlan_id` int(10) unsigned NOT NULL default '0',
611 PRIMARY KEY (`object_id`,`port_name`,`vlan_id`),
612 UNIQUE KEY `port_id` (`object_id`,`port_name`),
613 CONSTRAINT `PortNativeVLAN-FK-compound` FOREIGN KEY (`object_id`, `port_name`, `vlan_id`) REFERENCES `PortAllowedVLAN` (`object_id`, `port_name`, `vlan_id`) ON DELETE CASCADE
614 ) ENGINE=InnoDB
615 ";
616 $query[] = "
617 CREATE TABLE `PortVLANMode` (
618 `object_id` int(10) unsigned NOT NULL,
619 `port_name` char(255) NOT NULL,
620 `vlan_mode` enum('access','trunk') NOT NULL default 'access',
621 PRIMARY KEY (`object_id`,`port_name`),
622 CONSTRAINT `PortVLANMode-FK-object-port` FOREIGN KEY (`object_id`, `port_name`) REFERENCES `CachedPVM` (`object_id`, `port_name`)
623 ) ENGINE=InnoDB
624 ";
625 $query[] = "
626 CREATE TABLE `VLANDescription` (
627 `domain_id` int(10) unsigned NOT NULL,
628 `vlan_id` int(10) unsigned NOT NULL default '0',
629 `vlan_type` enum('ondemand','compulsory','alien') NOT NULL default 'ondemand',
630 `vlan_descr` char(255) default NULL,
631 PRIMARY KEY (`domain_id`,`vlan_id`),
632 KEY `vlan_id` (`vlan_id`),
633 CONSTRAINT `VLANDescription-FK-domain_id` FOREIGN KEY (`domain_id`) REFERENCES `VLANDomain` (`id`) ON DELETE CASCADE,
634 CONSTRAINT `VLANDescription-FK-vlan_id` FOREIGN KEY (`vlan_id`) REFERENCES `VLANValidID` (`vlan_id`)
635 ) ENGINE=InnoDB
636 ";
637 $query[] = "
638 CREATE TABLE `VLANDomain` (
639 `id` int(10) unsigned NOT NULL auto_increment,
640 `description` char(255) default NULL,
641 PRIMARY KEY (`id`),
642 UNIQUE KEY `description` (`description`)
643 ) ENGINE=InnoDB
644 ";
645 $query[] = "
646 CREATE TABLE `VLANIPv4` (
647 `domain_id` int(10) unsigned NOT NULL,
648 `vlan_id` int(10) unsigned NOT NULL,
649 `ipv4net_id` int(10) unsigned NOT NULL,
650 UNIQUE KEY `network-domain` (`ipv4net_id`,`domain_id`),
651 KEY `VLANIPv4-FK-compound` (`domain_id`,`vlan_id`),
652 CONSTRAINT `VLANIPv4-FK-compound` FOREIGN KEY (`domain_id`, `vlan_id`) REFERENCES `VLANDescription` (`domain_id`, `vlan_id`) ON DELETE CASCADE,
653 CONSTRAINT `VLANIPv4-FK-ipv4net_id` FOREIGN KEY (`ipv4net_id`) REFERENCES `IPv4Network` (`id`) ON DELETE CASCADE
654 ) ENGINE=InnoDB
655 ";
656 $query[] = "
657 CREATE TABLE `VLANSTRule` (
658 `vst_id` int(10) unsigned NOT NULL,
659 `rule_no` int(10) unsigned NOT NULL,
660 `port_pcre` char(255) NOT NULL,
661 `port_role` enum('access','trunk','uplink','downlink','none') NOT NULL default 'none',
662 `wrt_vlans` char(255) default NULL,
663 `description` char(255) default NULL,
664 UNIQUE KEY `vst-rule` (`vst_id`,`rule_no`),
665 CONSTRAINT `VLANSTRule-FK-vst_id` FOREIGN KEY (`vst_id`) REFERENCES `VLANSwitchTemplate` (`id`) ON DELETE CASCADE
666 ) ENGINE=InnoDB
667 ";
668 $query[] = "
669 CREATE TABLE `VLANSwitch` (
670 `object_id` int(10) unsigned NOT NULL,
671 `domain_id` int(10) unsigned NOT NULL,
672 `template_id` int(10) unsigned NOT NULL,
673 `mutex_rev` int(10) unsigned NOT NULL default '0',
674 `out_of_sync` enum('yes','no') NOT NULL default 'yes',
675 `last_errno` int(10) unsigned NOT NULL default '0',
676 `last_change` timestamp NOT NULL default '0000-00-00 00:00:00',
677 `last_push_started` timestamp NOT NULL default '0000-00-00 00:00:00',
678 `last_push_finished` timestamp NOT NULL default '0000-00-00 00:00:00',
679 `last_error_ts` timestamp NOT NULL default '0000-00-00 00:00:00',
680 UNIQUE KEY `object_id` (`object_id`),
681 KEY `domain_id` (`domain_id`),
682 KEY `template_id` (`template_id`),
683 KEY `out_of_sync` (`out_of_sync`),
684 KEY `last_errno` (`last_errno`),
685 CONSTRAINT `VLANSwitch-FK-domain_id` FOREIGN KEY (`domain_id`) REFERENCES `VLANDomain` (`id`),
686 CONSTRAINT `VLANSwitch-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`),
687 CONSTRAINT `VLANSwitch-FK-template_id` FOREIGN KEY (`template_id`) REFERENCES `VLANSwitchTemplate` (`id`)
688 ) ENGINE=InnoDB
689 ";
690 $query[] = "
691 CREATE TABLE `VLANSwitchTemplate` (
692 `id` int(10) unsigned NOT NULL auto_increment,
693 `max_local_vlans` int(10) unsigned default NULL,
694 `description` char(255) default NULL,
695 PRIMARY KEY (`id`),
696 UNIQUE KEY `description` (`description`)
697 ) ENGINE=InnoDB
698 ";
699 $query[] = "
700 CREATE TABLE `VLANValidID` (
701 `vlan_id` int(10) unsigned NOT NULL default '1',
702 PRIMARY KEY (`vlan_id`)
703 ) ENGINE=InnoDB
704 ";
705 $query[] = "SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS";
706 for ($i = 1; $i <= 4094; $i++)
707 $query[] = "INSERT INTO VLANValidID (vlan_id) VALUES (${i})";
708 $query[] = "UPDATE Config SET varvalue = '0.18.0' WHERE varname = 'DB_VERSION'";
709 break;
710 case '0.18.1':
711 $query = array_merge ($query, reloadDictionary ($batchid));
712 $query[] = "ALTER TABLE Atom ENGINE=InnoDB";
713 $query[] = "ALTER TABLE AttributeMap ENGINE=InnoDB";
714 $query[] = "ALTER TABLE Config ENGINE=InnoDB";
715 $query[] = "ALTER TABLE IPv4Address ENGINE=InnoDB";
716 $query[] = "ALTER TABLE IPv4Allocation ENGINE=InnoDB";
717 $query[] = "ALTER TABLE Molecule ENGINE=InnoDB";
718 $query[] = "ALTER TABLE MountOperation ENGINE=InnoDB";
719 $query[] = "ALTER TABLE PortCompat ENGINE=InnoDB";
720 $query[] = "ALTER TABLE Rack ENGINE=InnoDB";
721 $query[] = "ALTER TABLE RackHistory ENGINE=InnoDB";
722 $query[] = "ALTER TABLE RackObjectHistory ENGINE=InnoDB";
723 $query[] = "ALTER TABLE RackRow ENGINE=InnoDB";
724 $query[] = "ALTER TABLE RackSpace ENGINE=InnoDB";
725 $query[] = "ALTER TABLE Script ENGINE=InnoDB";
726 $query[] = "ALTER TABLE AttributeValue DROP FOREIGN KEY `AttributeValue-FK-object_id`";
727 $query[] = "ALTER TABLE AttributeValue ADD CONSTRAINT `AttributeValue-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE";
728 $query[] = "ALTER TABLE RackObjectHistory ADD KEY (id)";
729 $query[] = "ALTER TABLE RackObjectHistory ADD CONSTRAINT `RackObjectHistory-FK-object_id` FOREIGN KEY (id) REFERENCES `RackObject` (`id`) ON DELETE CASCADE";
730 $query[] = "ALTER TABLE MountOperation ADD CONSTRAINT `MountOperation-FK-object_id` FOREIGN KEY (object_id) REFERENCES RackObject (id) ON DELETE CASCADE";
731 $query[] = "ALTER TABLE RackSpace ADD CONSTRAINT `RackSpace-FK-object_id` FOREIGN KEY (object_id) REFERENCES RackObject (id) ON DELETE CASCADE";
732 $query[] = "ALTER TABLE Link DROP FOREIGN KEY `Link-FK-a`";
733 $query[] = "ALTER TABLE Link ADD CONSTRAINT `Link-FK-a` FOREIGN KEY (`porta`) REFERENCES `Port` (`id`) ON DELETE CASCADE";
734 $query[] = "ALTER TABLE Link DROP FOREIGN KEY `Link-FK-b`";
735 $query[] = "ALTER TABLE Link ADD CONSTRAINT `Link-FK-b` FOREIGN KEY (`portb`) REFERENCES `Port` (`id`) ON DELETE CASCADE";
736 $query[] = "ALTER TABLE Port DROP FOREIGN KEY `Port-FK-object_id`";
737 $query[] = "ALTER TABLE Port ADD CONSTRAINT `Port-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE";
738 $query[] = "ALTER TABLE AttributeMap MODIFY `chapter_id` int(10) unsigned default NULL";
739 $query[] = "ALTER TABLE IPv4Address MODIFY `ip` int(10) unsigned NOT NULL default '0'";
740 $query[] = "ALTER TABLE IPv4Address MODIFY `name` char(255) NOT NULL default ''";
741 $query[] = "ALTER TABLE IPv4Allocation MODIFY `object_id` int(10) unsigned NOT NULL default '0'";
742 $query[] = "ALTER TABLE IPv4Allocation MODIFY `ip` int(10) unsigned NOT NULL default '0'";
743 $query[] = "ALTER TABLE IPv4Allocation MODIFY `name` char(255) NOT NULL default ''";
744 $query[] = "ALTER TABLE IPv4NAT MODIFY `object_id` int(10) unsigned NOT NULL default '0'";
745 $query[] = "ALTER TABLE IPv4NAT MODIFY `proto` enum('TCP','UDP') NOT NULL default 'TCP'";
746 $query[] = "ALTER TABLE IPv4NAT MODIFY `localip` int(10) unsigned NOT NULL default '0'";
747 $query[] = "ALTER TABLE IPv4NAT MODIFY `localport` smallint(5) unsigned NOT NULL default '0'";
748 $query[] = "ALTER TABLE IPv4NAT MODIFY `remoteip` int(10) unsigned NOT NULL default '0'";
749 $query[] = "ALTER TABLE IPv4NAT MODIFY `remoteport` smallint(5) unsigned NOT NULL default '0'";
750 $query[] = "ALTER TABLE IPv4Network MODIFY `ip` int(10) unsigned NOT NULL default '0'";
751 $query[] = "ALTER TABLE IPv4Network MODIFY `mask` int(10) unsigned NOT NULL default '0'";
752 $query[] = "ALTER TABLE Link MODIFY `porta` int(10) unsigned NOT NULL default '0'";
753 $query[] = "ALTER TABLE Link MODIFY `portb` int(10) unsigned NOT NULL default '0'";
754 $query[] = "ALTER TABLE MountOperation MODIFY `object_id` int(10) unsigned NOT NULL default '0'";
755 $query[] = "ALTER TABLE MountOperation MODIFY `ctime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP";
756 $query[] = "ALTER TABLE Port MODIFY `object_id` int(10) unsigned NOT NULL default '0'";
757 $query[] = "ALTER TABLE Port MODIFY `name` char(255) NOT NULL default ''";
758 $query[] = "ALTER TABLE Port MODIFY `type` int(10) unsigned NOT NULL default '0'";
759 $query[] = "ALTER TABLE PortCompat MODIFY `type1` int(10) unsigned NOT NULL default '0'";
760 $query[] = "ALTER TABLE PortCompat MODIFY `type2` int(10) unsigned NOT NULL default '0'";
761 $query[] = "ALTER TABLE RackHistory MODIFY `ctime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP";
762 $query[] = "ALTER TABLE RackObjectHistory MODIFY `ctime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP";
763 $query[] = "ALTER TABLE TagStorage MODIFY `tag_id` int(10) unsigned NOT NULL default '0'";
764 $query[] = "ALTER TABLE UserAccount MODIFY `user_name` char(64) NOT NULL default ''";
765 $query[] = "UPDATE Config SET varvalue = '0.18.1' WHERE varname = 'DB_VERSION'";
766 break;
767 case '0.18.2':
768 $query = array_merge ($query, reloadDictionary ($batchid));
769 $query[] = "ALTER TABLE Rack ADD CONSTRAINT `Rack-FK-row_id` FOREIGN KEY (row_id) REFERENCES RackRow (id)";
770 $query[] = "ALTER TABLE RackRow ADD UNIQUE KEY `name` (name)";
771 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('CDP_RUNNERS_LISTSRC', '', 'string', 'yes', 'no', 'no', 'List of devices running CDP')";
772 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('LLDP_RUNNERS_LISTSRC', '', 'string', 'yes', 'no', 'no', 'List of devices running LLDP')";
773 $query[] = "UPDATE Config SET varvalue = '0.18.2' WHERE varname = 'DB_VERSION'";
774 break;
775 case '0.18.3':
776 $query = array_merge ($query, reloadDictionary ($batchid));
777 $query[] = "UPDATE Config SET varname='8021Q_WRI_AFTER_CONFT_LISTSRC', varvalue='false', description='802.1Q: save device configuration after deploy (RackCode)' WHERE varname='8021Q_WRI_AFTER_CONFT'";
778 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('HNDP_RUNNERS_LISTSRC', '', 'string', 'yes', 'no', 'no', 'List of devices running HNDP (RackCode)')";
779 $query[] = "UPDATE Config SET varvalue = '0.18.3' WHERE varname = 'DB_VERSION'";
780 break;
781 case '0.18.4':
782 $query = array_merge ($query, reloadDictionary ($batchid));
783 $query[] = "ALTER TABLE VLANSTRule MODIFY port_role enum('access','trunk','anymode','uplink','downlink','none') NOT NULL default 'none'";
784 $query[] = "UPDATE Config SET varvalue = '0.18.4' WHERE varname = 'DB_VERSION'";
785 break;
786 case '0.18.5':
787 $query = array_merge ($query, reloadDictionary ($batchid));
788 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('SHRINK_TAG_TREE_ON_CLICK','yes','string','no','no','yes','Dynamically hide useless tags in tagtree')";
789 $query[] = "ALTER TABLE `IPv4LB` ADD COLUMN `prio` int(10) unsigned DEFAULT NULL AFTER `vs_id`";
790 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('MAX_UNFILTERED_ENTITIES','0','uint','no','no','yes','Max item count to display on unfiltered result page')";
791 $query[] = "UPDATE Config SET varvalue = '0.18.5' WHERE varname = 'DB_VERSION'";
792 break;
793 case '0.18.6':
794 $query = array_merge ($query, reloadDictionary ($batchid));
795 $query[] = "UPDATE Config SET varvalue = '0.18.6' WHERE varname = 'DB_VERSION'";
796 break;
797 case '0.18.7':
798 $query = array_merge ($query, reloadDictionary ($batchid));
799 $query[] = "UPDATE Config SET varvalue = '0.18.7' WHERE varname = 'DB_VERSION'";
800 break;
801 case '0.19.0':
802 $query = array_merge ($query, reloadDictionary ($batchid));
803 $query[] = 'ALTER TABLE `File` ADD `thumbnail` LONGBLOB NULL AFTER `atime`';
804 $query[] = "
805 CREATE TABLE `IPv6Address` (
806 `ip` binary(16) NOT NULL,
807 `name` char(255) NOT NULL default '',
808 `reserved` enum('yes','no') default NULL,
809 PRIMARY KEY (`ip`)
810 ) ENGINE=InnoDB
811 ";
812 $query[] = "
813 CREATE TABLE `IPv6Allocation` (
814 `object_id` int(10) unsigned NOT NULL default '0',
815 `ip` binary(16) NOT NULL,
816 `name` char(255) NOT NULL default '',
817 `type` enum('regular','shared','virtual','router') default NULL,
818 PRIMARY KEY (`object_id`,`ip`),
819 CONSTRAINT `IPv6Allocation-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE
820 ) ENGINE=InnoDB
821 ";
822 $query[] = "
823 CREATE TABLE `IPv6Network` (
824 `id` int(10) unsigned NOT NULL auto_increment,
825 `ip` binary(16) NOT NULL,
826 `mask` int(10) unsigned NOT NULL,
827 `last_ip` binary(16) NOT NULL,
828 `name` char(255) default NULL,
829 `comment` text,
830 PRIMARY KEY (`id`),
831 UNIQUE KEY `ip` (`ip`,`mask`)
832 ) ENGINE=InnoDB
833 ";
834 $query[] = "
835 CREATE TABLE `VLANIPv6` (
836 `domain_id` int(10) unsigned NOT NULL,
837 `vlan_id` int(10) unsigned NOT NULL,
838 `ipv6net_id` int(10) unsigned NOT NULL,
839 UNIQUE KEY `network-domain` (`ipv6net_id`,`domain_id`),
840 KEY `VLANIPv6-FK-compound` (`domain_id`,`vlan_id`),
841 CONSTRAINT `VLANIPv6-FK-compound` FOREIGN KEY (`domain_id`, `vlan_id`) REFERENCES `VLANDescription` (`domain_id`, `vlan_id`) ON DELETE CASCADE,
842 CONSTRAINT `VLANIPv6-FK-ipv6net_id` FOREIGN KEY (`ipv6net_id`) REFERENCES `IPv6Network` (`id`) ON DELETE CASCADE
843 ) ENGINE=InnoDB
844 ";
845 $query[] = "
846 CREATE TABLE IF NOT EXISTS `ObjectLog` (
847 `id` int(10) NOT NULL AUTO_INCREMENT,
848 `object_id` int(10) NOT NULL,
849 `user` varchar(64) NOT NULL,
850 `date` datetime NOT NULL,
851 `content` text NOT NULL,
852 PRIMARY KEY (`id`)
853 ) ENGINE=InnoDB
854 ";
855 # Now we have the same structure of ObjectLog table, which objectlog.php
856 # could have left. Subsequent column updates will handle any existing data.
857 $query[] = "ALTER TABLE ObjectLog MODIFY COLUMN `id` int(10) unsigned NOT NULL AUTO_INCREMENT";
858 $query[] = "ALTER TABLE ObjectLog MODIFY COLUMN `object_id` int(10) unsigned NOT NULL";
859 $query[] = "ALTER TABLE ObjectLog MODIFY COLUMN `user` char(64) NOT NULL";
860 $query[] = "ALTER TABLE ObjectLog ADD KEY `object_id` (`object_id`)";
861 $query[] = "ALTER TABLE ObjectLog ADD KEY `date` (`date`)";
862 $query[] = "ALTER TABLE ObjectLog ADD CONSTRAINT `ObjectLog-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE";
863 # Now it's the way 0.19.0 is expecting it to be.
864 $query[] = "
865 CREATE TABLE `ObjectParentCompat` (
866 `parent_objtype_id` int(10) unsigned NOT NULL,
867 `child_objtype_id` int(10) unsigned NOT NULL,
868 UNIQUE KEY `parent_child` (`parent_objtype_id`,`child_objtype_id`)
869 ) ENGINE=InnoDB
870 ";
871 $query[] = "
872 CREATE TABLE `EntityLink` (
873 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
874 `parent_entity_type` enum('ipv4net','ipv4rspool','ipv4vs','ipv6net','object','rack','user') NOT NULL,
875 `parent_entity_id` int(10) unsigned NOT NULL,
876 `child_entity_type` enum('file','object') NOT NULL,
877 `child_entity_id` int(10) unsigned NOT NULL,
878 PRIMARY KEY (`id`),
879 UNIQUE KEY `EntityLink-unique` (`parent_entity_type`,`parent_entity_id`,`child_entity_type`,`child_entity_id`)
880 ) ENGINE=InnoDB
881 ";
882 $query[] = "ALTER TABLE `TagStorage` CHANGE COLUMN `entity_realm` `entity_realm` ENUM('file','ipv4net','ipv4vs','ipv4rspool','object','rack','user','ipv6net') NOT NULL DEFAULT 'object' FIRST";
883 $query[] = "ALTER TABLE `FileLink` CHANGE COLUMN `entity_type` `entity_type` ENUM('ipv4net','ipv4rspool','ipv4vs','object','rack','user','ipv6net') NOT NULL DEFAULT 'object' AFTER `file_id`";
884 $query[] = 'ALTER TABLE Link ADD COLUMN cable char(64) NULL AFTER portb';
885 $query[] = 'ALTER TABLE RackSpace ADD CONSTRAINT `RackSpace-FK-rack_id` FOREIGN KEY (rack_id) REFERENCES Rack (id)';
886 $query[] = "ALTER TABLE `IPv4Allocation` ADD CONSTRAINT `IPv4Allocation-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE";
887 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('SYNCDOMAIN_MAX_PROCESSES','0','uint','yes','no', 'How many worker proceses syncdomain cron script should create')";
888 $query[] = "ALTER TABLE `VLANSwitchTemplate` ADD COLUMN `mutex_rev` int(10) NOT NULL AFTER `id`";
889 $query[] = "ALTER TABLE `VLANSwitchTemplate` ADD COLUMN `saved_by` char(64) NOT NULL AFTER `description`";
890 $query[] = "INSERT INTO `Attribute` (`id`, `type`, `name`) VALUES (26,'dict','Hypervisor')";
891 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (29,'no','Yes/No')";
892 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (30,'no','network chassis models')";
893 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (31,'no','server chassis models')";
894 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (32,'no','virtual switch models')";
895 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (33,'no','virtual switch OS type')";
896 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (4,26,29)";
897 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1502,1,NULL)";
898 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1502,2,31)";
899 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1502,3,NULL)";
900 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1502,14,NULL)";
901 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1502,20,NULL)";
902 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1502,21,NULL)";
903 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1502,22,NULL)";
904 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,1,NULL)";
905 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,2,30)";
906 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,3,NULL)";
907 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,4,14)";
908 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,5,NULL)";
909 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,14,NULL)";
910 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,16,NULL)";
911 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,17,NULL)";
912 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,18,NULL)";
913 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,20,NULL)";
914 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,21,NULL)";
915 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,22,NULL)";
916 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,24,NULL)";
917 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1504,3,NULL)";
918 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1504,4,13)";
919 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1504,14,NULL)";
920 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1504,24,NULL)";
921 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1505,14,NULL)";
922 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1506,14,NULL)";
923 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1506,17,NULL)";
924 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1506,18,NULL)";
925 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,1,NULL)";
926 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,2,32)";
927 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,3,NULL)";
928 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,4,33)";
929 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,5,NULL)";
930 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,14,NULL)";
931 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,20,NULL)";
932 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,21,NULL)";
933 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,22,NULL)";
934 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (3,13)";
935 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (4,1504)";
936 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (4,1507)";
937 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1502,4)";
938 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1503,8)";
939 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1505,4)";
940 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1505,1504)";
941 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1505,1506)";
942 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1505,1507)";
943 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1506,1504)";
944 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('PORT_EXCLUSION_LISTSRC','{\$typeid_3} or {\$typeid_10} or {\$typeid_11} or {\$typeid_1505} or {\$typeid_1506}','string','yes','no','no','List source: objects without ports')";
945 $query[] = "UPDATE Config SET varvalue = CONCAT(varvalue, ' or {\$typeid_1502} or {\$typeid_1503} or {\$typeid_1504} or {\$typeid_1507}') WHERE varname = 'IPV4OBJ_LISTSRC'";
946 $query[] = "UPDATE Config SET varvalue = '8' WHERE varname = 'MASSCOUNT'";
947 $query[] = "UPDATE RackObject SET label = NULL WHERE label = ''";
948 // Move barcode data so the column can be dropped
949 $result = $dbxlink->query ('SELECT id, objtype_id, barcode FROM RackObject WHERE barcode IS NOT NULL');
950 $rows = $result->fetchAll (PDO::FETCH_ASSOC);
951 unset ($result);
952 foreach ($rows as $row)
953 {
954 // Determine if this object type has the 'OEM S/N 1' attribute associated with it, and if it's set
955 $sn_query = "SELECT (SELECT COUNT(*) FROM AttributeMap WHERE objtype_id=${row['objtype_id']} AND attr_id=1) AS AM_count, ";
956 $sn_query .= "(SELECT COUNT(*) FROM AttributeValue WHERE object_id=${row['id']} AND attr_id=1) AS AV_count";
957 $sn_result = $dbxlink->query ($sn_query);
958 $sn_row = $sn_result->fetch (PDO::FETCH_ASSOC);
959 if ($sn_row['AM_count'] == 1 && $sn_row['AV_count'] == 0)
960 {
961 // 'OEM S/N 1' attribute is mapped to this object type, but it is not set. Good!
962 // Copy the barcode value to the attribute.
963 $query[] = "INSERT INTO AttributeValue (`object_id`, `attr_id`, `string_value`) VALUES (${row['id']}, 1, '${row['barcode']}')";
964 }
965 else
966 {
967 // Some other set of circumstances. Not as good!
968 // Copy the barcode value to a new ObjectLog record.
969 $query[] = "INSERT INTO ObjectLog (`object_id`, `user`, `date`, `content`) VALUES (${row['id']}, '${_SERVER['PHP_AUTH_USER']}', NOW(), 'Upgrade to 0.19 dropped the barcode column. Value was: ${row['barcode']}')";
970 }
971 unset ($sn_query, $sn_result, $sn_row);
972 }
973 $query[] = 'ALTER TABLE RackObject DROP COLUMN `barcode`';
974 $query[] = 'ALTER TABLE RackObjectHistory DROP COLUMN `barcode`';
975 $query[] = 'ALTER TABLE `VLANSwitchTemplate` DROP COLUMN `max_local_vlans`';
976 $query[] = "UPDATE Config SET varvalue = '0.19.0' WHERE varname = 'DB_VERSION'";
977 break;
978 case '0.19.1':
979 $query = array_merge ($query, reloadDictionary ($batchid));
980 $query[] = "ALTER TABLE `Config` CHANGE COLUMN `varvalue` `varvalue` text NOT NULL";
981 $query[] = "ALTER TABLE `UserConfig` CHANGE COLUMN `varvalue` `varvalue` text NOT NULL";
982 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('FILTER_RACKLIST_BY_TAGS','yes','string','yes','no','yes','Rackspace: show only racks matching the current object\'s tags')";
983 $result = $dbxlink->query ("SHOW TABLES LIKE 'Objectlog'");
984 $rows = $result->fetchAll (PDO::FETCH_ASSOC);
985 unset ($result);
986 if (count ($rows))
987 {
988 # Now the ObjectLog merge... again, because the original table is named
989 # "Objectlog". The job is to merge contents of Objectlog and ObjectLog
990 # into the latter.
991 $query[] = "INSERT INTO ObjectLog (object_id, user, date, content) SELECT object_id, user, date, content FROM Objectlog WHERE object_id IN(SELECT id FROM RackObject)";
992 $query[] = "DELETE FROM Objectlog WHERE object_id IN(SELECT id FROM RackObject)";
993 # Don't delete the old table, if the merge wasn't exhaustive.
994 $result = $dbxlink->query ('SELECT COUNT(*) AS c FROM Objectlog WHERE object_id NOT IN(SELECT id FROM RackObject)');
995 $row = $result->fetch (PDO::FETCH_ASSOC);
996 unset ($result);
997 if ($row['c'] == 0)
998 $query[] = 'DROP TABLE Objectlog';
999 else
1000 $query[] = 'ALTER TABLE Objectlog RENAME TO Objectlog_old_unmerged';
1001 }
1002 $query[] = "UPDATE Config SET varvalue = '0.19.1' WHERE varname = 'DB_VERSION'";
1003 break;
1004 case '0.19.2':
1005 $query = array_merge ($query, reloadDictionary ($batchid));
1006 $query[] = "ALTER TABLE IPv4Allocation ADD KEY `ip` (`ip`)";
1007 $query[] = "ALTER TABLE IPv6Allocation ADD KEY `ip` (`ip`)";
1008 $query[] = "ALTER TABLE IPv4VS ADD KEY `vip` (`vip`)";
1009 $query[] = "ALTER TABLE IPv4RS ADD KEY `rsip` (`rsip`)";
1010 $query[] = "UPDATE Config SET varvalue = '0.19.2' WHERE varname = 'DB_VERSION'";
1011 break;
1012 default:
1013 showError ("executeUpgradeBatch () failed, because batch '${batchid}' isn't defined");
1014 die;
1015 break;
1016 }
1017 $failures = array();
1018 echo "<tr><th>Executing batch '${batchid}'</th><td>";
1019 foreach ($query as $q)
1020 {
1021 $result = $dbxlink->query ($q);
1022 if ($result == NULL)
1023 {
1024 $errorInfo = $dbxlink->errorInfo();
1025 $failures[] = array ($q, $errorInfo[2]);
1026 }
1027 }
1028 if (!count ($failures))
1029 echo "<strong><font color=green>done</font></strong>";
1030 else
1031 {
1032 echo "<strong><font color=red>The following queries failed:</font></strong><br><pre>";
1033 foreach ($failures as $f)
1034 {
1035 list ($q, $i) = $f;
1036 echo "${q} -- ${i}\n";
1037 }
1038 echo "</pre>";
1039 }
1040 echo '</td></tr>';
1041 }
1042
1043 // ******************************************************************
1044 //
1045 // Execution starts here
1046 //
1047 // ******************************************************************
1048
1049 require_once 'inc/config.php'; // for CODE_VERSION
1050 require_once 'inc/dictionary.php';
1051 // Enforce default value for now, releases prior to 0.17.0 didn't support 'httpd' auth source.
1052 $user_auth_src = 'database';
1053
1054 if (file_exists ('inc/secret.php'))
1055 require_once 'inc/secret.php';
1056 else
1057 die ('<center>There is no working RackTables instance here, <a href="install.php">install</a>?</center>');
1058
1059 try
1060 {
1061 $dbxlink = new PDO ($pdo_dsn, $db_username, $db_password);
1062 }
1063 catch (PDOException $e)
1064 {
1065 die ("Database connection failed:\n\n" . $e->getMessage());
1066 }
1067
1068 // Now we need to be sure that the current user is the administrator.
1069 // The rest doesn't matter within this context.
1070
1071 function authenticate_admin ($username, $password)
1072 {
1073 global $dbxlink;
1074 $prepared = $dbxlink->prepare ('SELECT COUNT(*) FROM UserAccount WHERE user_id=1 AND user_name=? AND user_password_hash=?');
1075 if (!$prepared->execute (array ($username, sha1 ($password))))
1076 die ('SQL query failed in ' . __FUNCTION__);
1077 $rows = $prepared->fetchAll (PDO::FETCH_NUM);
1078 return $rows[0][0] == 1;
1079 }
1080
1081 // Database version detector. Should behave corretly on any
1082 // working dataset a user might have.
1083 function getDatabaseVersion ()
1084 {
1085 global $dbxlink;
1086 $prepared = $dbxlink->prepare ('SELECT varvalue FROM Config WHERE varname = "DB_VERSION" and vartype = "string"');
1087 if (! $prepared->execute())
1088 {
1089 $errorInfo = $dbxlink->errorInfo();
1090 die (__FUNCTION__ . ': SQL query failed with error ' . $errorInfo[2]);
1091 }
1092 $rows = $prepared->fetchAll (PDO::FETCH_NUM);
1093 unset ($result);
1094 if (count ($rows) != 1 || !strlen ($rows[0][0]))
1095 die (__FUNCTION__ . ': Cannot guess database version. Config table is present, but DB_VERSION is missing or invalid. Giving up.');
1096 $ret = $rows[0][0];
1097 return $ret;
1098 }
1099
1100 function showError ($info = '', $location = 'upgrade.php')
1101 {
1102 if (preg_match ('/\.php$/', $location))
1103 $location = basename ($location);
1104 elseif ($location != 'N/A')
1105 $location = $location . '()';
1106 echo "<div class=msg_error>An error has occured in [${location}]. ";
1107 if (!strlen ($info))
1108 echo 'No additional information is available.';
1109 else
1110 echo "Additional information:<br><p>\n<pre>\n${info}\n</pre></p>";
1111 echo "Go back or try starting from <a href='index.php'>index page</a>.<br></div>\n";
1112 }
1113
1114 switch ($user_auth_src)
1115 {
1116 case 'database':
1117 case 'ldap': // authenticate against DB as well
1118 if
1119 (
1120 !isset ($_SERVER['PHP_AUTH_USER']) or
1121 !strlen ($_SERVER['PHP_AUTH_USER']) or
1122 !isset ($_SERVER['PHP_AUTH_PW']) or
1123 !strlen ($_SERVER['PHP_AUTH_PW']) or
1124 !authenticate_admin ($_SERVER['PHP_AUTH_USER'], $_SERVER['PHP_AUTH_PW'])
1125 )
1126 {
1127 header ('WWW-Authenticate: Basic realm="RackTables upgrade"');
1128 header ('HTTP/1.0 401 Unauthorized');
1129 showError ('You must be authenticated as an administrator to complete the upgrade.');
1130 die;
1131 }
1132 break; // cleared
1133 case 'httpd':
1134 if
1135 (
1136 !isset ($_SERVER['REMOTE_USER']) or
1137 !strlen ($_SERVER['REMOTE_USER'])
1138 )
1139 {
1140 showError ('System misconfiguration. The web-server didn\'t authenticate the user, although ought to do.');
1141 die;
1142 }
1143 break; // cleared
1144 default:
1145 showError ('authentication source misconfiguration');
1146 die;
1147 }
1148
1149 ?>
1150 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
1151 <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
1152 <head><title>RackTables upgrade script</title>
1153 <link rel=stylesheet type='text/css' href='css/pi.css' />
1154 </head>
1155 <body>
1156 <h1>Platform check status</h1>
1157 <?php
1158
1159 if (!platform_is_ok())
1160 die ('</body></html>');
1161
1162 echo '<h1>Upgrade status</h1>';
1163 $dbver = getDatabaseVersion();
1164 echo '<table border=1 cellpadding=5>';
1165 echo "<tr><th>Current status</th><td>Data version: ${dbver}<br>Code version: " . CODE_VERSION . "</td></tr>\n";
1166
1167 $path = getDBUpgradePath ($dbver, CODE_VERSION);
1168 if ($path === NULL)
1169 {
1170 echo "<tr><th>Upgrade path</th><td><font color=red>not found</font></td></tr>\n";
1171 echo "<tr><th>Summary</th><td>Check README for more information. RackTables releases prior to 0.16.4 ";
1172 echo "must be upgraded to 0.16.4 first.</td></tr>\n";
1173 }
1174 else
1175 {
1176 if (!count ($path))
1177 echo "<tr><th>Summary</th><td>Come back later.</td></tr>\n";
1178 else
1179 {
1180 echo "<tr><th>Upgrade path</th><td>${dbver} &rarr; " . implode (' &rarr; ', $path) . "</td></tr>\n";
1181 foreach ($path as $batchid)
1182 {
1183 executeUpgradeBatch ($batchid);
1184 if (isset ($relnotes[$batchid]))
1185 echo "<tr><th>Release notes for ${batchid}</th><td><pre>" . $relnotes[$batchid] . "</pre></td></tr>\n";
1186 }
1187 echo "<tr><th>Summary</th><td>Upgrade complete, it is Ok to <a href='index.php'>enter</a> the system.</td></tr>\n";
1188 }
1189 }
1190 echo '</table>';
1191 echo '</body></html>';
1192
1193 ?>