r4724 Chapter: switch to InnoDB, add FKeys
[racktables] / wwwroot / inc / 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 a bit.
62 You can also separate your local settings and add-ons from the core RackTables code.
63 To do that, put a single index.php file into the DocumentRoot of your http server:
64
65 <?php
66 \$racktables_confdir='/directory/where/your/secret.php/and/local.php/files/are/stored';
67 require '/directory_where_you_extracted_racktables_distro/wwwroot/index.php';
68 ?>
69
70 No more files are needed to be available directly over the HTTP.
71 Full list of filesystem paths which could be specified in custom index.php or secret.php:
72 \$racktables_gwdir: path to the gateways directory;
73 \$racktables_staticdir: path to the directory containing 'pix', 'js', 'css' dirs;
74 \$racktables_confdir: path where secret.php and local.php are located. It is not
75 recommended to define it in secret.php, cause only the path to
76 local.php will be affected;
77 \$path_to_secret_php: Ignore \$racktables_confdir when locating secret.php and use
78 the specified path;
79 \$path_to_local_php: idem for local.php.
80 ENDOFTEXT
81 ,
82 );
83
84 // At the moment we assume, that for any two releases we can
85 // sequentally execute all batches, that separate them, and
86 // nothing will break. If this changes one day, the function
87 // below will have to generate smarter upgrade paths, while
88 // the upper layer will remain the same.
89 // Returning an empty array means that no upgrade is necessary.
90 // Returning NULL indicates an error.
91 function getDBUpgradePath ($v1, $v2)
92 {
93 $versionhistory = array
94 (
95 '0.16.4',
96 '0.16.5',
97 '0.16.6',
98 '0.17.0',
99 '0.17.1',
100 '0.17.2',
101 '0.17.3',
102 '0.17.4',
103 '0.17.5',
104 '0.17.6',
105 '0.17.7',
106 '0.17.8',
107 '0.17.9',
108 '0.17.10',
109 '0.17.11',
110 '0.18.0',
111 '0.18.1',
112 '0.18.2',
113 '0.18.3',
114 '0.18.4',
115 '0.18.5',
116 '0.18.6',
117 '0.18.7',
118 '0.19.0',
119 '0.19.1',
120 '0.19.2',
121 '0.19.3',
122 '0.19.4',
123 '0.19.5',
124 '0.19.6',
125 '0.19.7',
126 '0.20.0',
127 );
128 if (!in_array ($v1, $versionhistory) or !in_array ($v2, $versionhistory))
129 return NULL;
130 $skip = TRUE;
131 $path = NULL;
132 // foreach() below cannot handle this specific case
133 if ($v1 == $v2)
134 return array();
135 // Now collect all versions > $v1 and <= $v2
136 foreach ($versionhistory as $v)
137 {
138 if ($skip and $v == $v1)
139 {
140 $skip = FALSE;
141 $path = array();
142 continue;
143 }
144 if ($skip)
145 continue;
146 $path[] = $v;
147 if ($v == $v2)
148 break;
149 }
150 return $path;
151 }
152
153 // Upgrade batches are named exactly as the release where they first appear.
154 // That is simple, but seems sufficient for beginning.
155 function executeUpgradeBatch ($batchid)
156 {
157 $query = array();
158 global $dbxlink;
159 switch ($batchid)
160 {
161 case '0.16.5':
162 $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')";
163 $query[] = "update Config set varvalue = '0.16.5' where varname = 'DB_VERSION'";
164 break;
165 case '0.16.6':
166 $query[] = "update Config set varvalue = '0.16.6' where varname = 'DB_VERSION'";
167 break;
168 case '0.17.0':
169 // create tables for storing files (requires InnoDB support)
170 if (!isInnoDBSupported ())
171 {
172 showError ("Cannot upgrade because InnoDB tables are not supported by your MySQL server. See the README for details.", __FUNCTION__);
173 die;
174 }
175
176 $query[] = "alter table Chapter change chapter_no id int(10) unsigned NOT NULL auto_increment";
177 $query[] = "alter table Chapter change chapter_name name char(128) NOT NULL";
178 $query[] = "alter table Chapter drop key chapter_name";
179 $query[] = "alter table Chapter add UNIQUE KEY name (name)";
180 $query[] = "alter table Attribute change attr_id id int(10) unsigned NOT NULL auto_increment";
181 $query[] = "alter table Attribute change attr_type type enum('string','uint','float','dict') default NULL";
182 $query[] = "alter table Attribute change attr_name name char(64) default NULL";
183 $query[] = "alter table Attribute drop key attr_name";
184 $query[] = "alter table Attribute add UNIQUE KEY name (name)";
185 $query[] = "alter table AttributeMap change chapter_no chapter_id int(10) unsigned NOT NULL";
186 $query[] = "alter table Dictionary change chapter_no chapter_id int(10) unsigned NOT NULL";
187 // Only after the above call it is Ok to use reloadDictionary()
188 $query = array_merge ($query, reloadDictionary ($batchid));
189 // schema changes for file management
190 $query[] = "
191 CREATE TABLE `File` (
192 `id` int(10) unsigned NOT NULL auto_increment,
193 `name` char(255) NOT NULL,
194 `type` char(255) NOT NULL,
195 `size` int(10) unsigned NOT NULL,
196 `ctime` datetime NOT NULL,
197 `mtime` datetime NOT NULL,
198 `atime` datetime NOT NULL,
199 `contents` longblob NOT NULL,
200 `comment` text,
201 PRIMARY KEY (`id`),
202 UNIQUE KEY `name` (`name`)
203 ) ENGINE=InnoDB";
204 $query[] = "
205 CREATE TABLE `FileLink` (
206 `id` int(10) unsigned NOT NULL auto_increment,
207 `file_id` int(10) unsigned NOT NULL,
208 `entity_type` enum('ipv4net','ipv4rspool','ipv4vs','object','rack','user') NOT NULL default 'object',
209 `entity_id` int(10) NOT NULL,
210 PRIMARY KEY (`id`),
211 UNIQUE KEY `FileLink-unique` (`file_id`,`entity_type`,`entity_id`),
212 KEY `FileLink-file_id` (`file_id`),
213 CONSTRAINT `FileLink-File_fkey` FOREIGN KEY (`file_id`) REFERENCES `File` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
214 ) ENGINE=InnoDB";
215 $query[] = "ALTER TABLE TagStorage MODIFY COLUMN target_realm enum('file','ipv4net','ipv4rspool','ipv4vs','object','rack','user') NOT NULL default 'object'";
216
217 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (24,'no','network security models')";
218 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (25,'no','wireless models')";
219 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,1,0)";
220 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,2,24)";
221 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,3,0)";
222 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,5,0)";
223 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,14,0)";
224 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,16,0)";
225 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,17,0)";
226 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,18,0)";
227 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,20,0)";
228 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,21,0)";
229 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,22,0)";
230 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,24,0)";
231 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (965,1,0)";
232 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (965,3,0)";
233 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (965,2,25)";
234 $query[] = 'alter table IPBonds rename to IPv4Allocation';
235 $query[] = 'alter table PortForwarding rename to IPv4NAT';
236 $query[] = 'alter table IPRanges rename to IPv4Network';
237 $query[] = 'alter table IPAddress rename to IPv4Address';
238 $query[] = 'alter table IPLoadBalancer rename to IPv4LB';
239 $query[] = 'alter table IPRSPool rename to IPv4RSPool';
240 $query[] = 'alter table IPRealServer rename to IPv4RS';
241 $query[] = 'alter table IPVirtualService rename to IPv4VS';
242 $query[] = "alter table TagStorage change column target_realm entity_realm enum('file','ipv4net','ipv4vs','ipv4rspool','object','rack','user') NOT NULL default 'object'";
243 $query[] = 'alter table TagStorage change column target_id entity_id int(10) unsigned NOT NULL';
244 $query[] = 'alter table TagStorage drop key entity_tag';
245 $query[] = 'alter table TagStorage drop key target_id';
246 $query[] = 'alter table TagStorage add UNIQUE KEY `entity_tag` (`entity_realm`,`entity_id`,`tag_id`)';
247 $query[] = 'alter table TagStorage add KEY `entity_id` (`entity_id`)';
248 $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')";
249 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('PREVIEW_TEXT_ROWS','25','uint','yes','no','Rows for text file preview')";
250 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('PREVIEW_TEXT_COLS','80','uint','yes','no','Columns for text file preview')";
251 $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')";
252 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('VENDOR_SIEVE','','string','yes','no','Vendor sieve configuration')";
253 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('IPV4LB_LISTSRC','{\$typeid_4}','string','yes','no','List source: IPv4 load balancers')";
254 $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')";
255 $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')";
256 $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')";
257 $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')";
258 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('RACKS_PER_ROW','12','unit','yes','no','Racks per row')";
259 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('FILTER_PREDICATE_SIEVE','','string','yes','no','Predicate sieve regex(7)')";
260 $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)')";
261 $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')";
262 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('FILTER_SUGGEST_TAGS','yes','string','no','no','Suggest tags in list filter')";
263 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('FILTER_SUGGEST_PREDICATES','yes','string','no','no','Suggest predicates in list filter')";
264 $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')";
265 $query[] = "delete from Config where varname = 'USER_AUTH_SRC'";
266 $query[] = "delete from Config where varname = 'COOKIE_TTL'";
267 $query[] = "delete from Config where varname = 'rtwidth_0'";
268 $query[] = "delete from Config where varname = 'rtwidth_1'";
269 $query[] = "delete from Config where varname = 'rtwidth_2'";
270 $query[] = "delete from Config where varname = 'NAMEFUL_OBJTYPES'";
271 $query[] = "delete from Config where varname = 'REQUIRE_ASSET_TAG_FOR'";
272 $query[] = "delete from Config where varname = 'IPV4_PERFORMERS'";
273 $query[] = "delete from Config where varname = 'NATV4_PERFORMERS'";
274 $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";
275 $result = $dbxlink->query ("select user_id, user_name, user_realname from UserAccount where user_enabled = 'no'");
276 while ($row = $result->fetch (PDO::FETCH_ASSOC))
277 $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'";
278 $query[] = "update Script set script_text = NULL where script_name = 'RackCodeCache'";
279 unset ($result);
280 $query[] = "alter table UserAccount drop column user_enabled";
281
282 $query[] = "CREATE TABLE RackRow ( id int(10) unsigned NOT NULL auto_increment, name char(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM";
283
284 $result = $dbxlink->query ("select dict_key, dict_value from Dictionary where chapter_no = 3");
285 while($row = $result->fetch(PDO::FETCH_NUM))
286 $query[] = "insert into RackRow set id=${row[0]}, name='${row[1]}'";
287 unset ($result);
288 $query[] = "delete from Dictionary where chapter_id = 3";
289 $query[] = "delete from Chapter where id = 3";
290 $query[] = "
291 CREATE TABLE `LDAPCache` (
292 `presented_username` char(64) NOT NULL,
293 `successful_hash` char(40) NOT NULL,
294 `first_success` timestamp NOT NULL default CURRENT_TIMESTAMP,
295 `last_retry` timestamp NOT NULL default '0000-00-00 00:00:00',
296 `displayed_name` char(128) default NULL,
297 `memberof` text,
298 UNIQUE KEY `presented_username` (`presented_username`),
299 KEY `scanidx` (`presented_username`,`successful_hash`)
300 ) ENGINE=InnoDB;";
301 $query[] = "alter table UserAccount modify column user_password_hash char(40) NULL";
302 $query[] = 'ALTER TABLE Rack DROP COLUMN deleted';
303 $query[] = 'ALTER TABLE RackHistory DROP COLUMN deleted';
304 $query[] = 'ALTER TABLE RackObject DROP COLUMN deleted';
305 $query[] = 'ALTER TABLE RackObjectHistory DROP COLUMN deleted';
306 // Can't be added straight due to many duplicates, even in "dictbase" data.
307 $result = $dbxlink->query ('SELECT type1, type2, count(*) - 1 as excess FROM PortCompat GROUP BY type1, type2 HAVING excess > 0');
308 while ($row = $result->fetch (PDO::FETCH_ASSOC))
309 $query[] = "DELETE FROM PortCompat WHERE type1 = ${row['type1']} AND type2 = ${row['type2']} limit ${row['excess']}";
310 unset ($result);
311 $query[] = 'ALTER TABLE PortCompat DROP KEY type1';
312 $query[] = 'ALTER TABLE PortCompat ADD UNIQUE `type1_2` (type1, type2)';
313 $query[] = "UPDATE Config SET varvalue = '0.17.0' WHERE varname = 'DB_VERSION'";
314
315 break;
316 case '0.17.1':
317 $query[] = "ALTER TABLE Dictionary DROP KEY `chap_to_key`";
318 $query = array_merge ($query, reloadDictionary ($batchid));
319 // Token set has changed, so the cache isn't valid any more.
320 $query[] = "UPDATE Script SET script_text = NULL WHERE script_name = 'RackCodeCache'";
321 $query[] = "UPDATE Config SET varvalue = '0.17.1' WHERE varname = 'DB_VERSION'";
322 break;
323 case '0.17.2':
324 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (26,'no','fibre channel switch models')";
325 $query = array_merge ($query, reloadDictionary ($batchid));
326 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1055,2,26)";
327 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('DEFAULT_SNMP_COMMUNITY','public','string','no','no','Default SNMP Community string')";
328 // wipe irrelevant records (ticket:250)
329 $query[] = "DELETE FROM TagStorage WHERE entity_realm = 'file' AND entity_id NOT IN (SELECT id FROM File)";
330 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('IPV4_ENABLE_KNIGHT','yes','string','no','no','Enable IPv4 knight feature')";
331 $query[] = "ALTER TABLE IPv4Network ADD COLUMN comment text AFTER name";
332 $query[] = "ALTER TABLE Port ADD INDEX comment (reservation_comment)";
333 $query[] = "ALTER TABLE Port DROP KEY l2address"; // UNIQUE
334 $query[] = "ALTER TABLE Port ADD KEY (l2address)"; // not UNIQUE
335 $query[] = "ALTER TABLE Port DROP KEY object_id";
336 $query[] = "ALTER TABLE Port ADD UNIQUE KEY per_object (object_id, name, type)";
337 $query[] = "INSERT INTO PortCompat (type1, type2) VALUES (20,1083)";
338 $query[] = "INSERT INTO PortCompat (type1, type2) VALUES (21,1083)";
339 $query[] = "INSERT INTO PortCompat (type1, type2) VALUES (1077,1077)";
340 $query[] = "INSERT INTO PortCompat (type1, type2) VALUES (1083,20)";
341 $query[] = "INSERT INTO PortCompat (type1, type2) VALUES (1083,21)";
342 $query[] = "INSERT INTO PortCompat (type1, type2) VALUES (1083,1083)";
343 $query[] = "INSERT INTO PortCompat (type1, type2) VALUES (1087,1087)";
344 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (27,'no','PDU models')";
345 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (2,2,27)";
346 $query[] = "UPDATE Config SET varvalue = '0.17.2' WHERE varname = 'DB_VERSION'";
347 break;
348 case '0.17.3':
349 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('TAGS_TOPLIST_SIZE','50','uint','yes','no','Tags top list size')";
350 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('TAGS_QUICKLIST_SIZE','20','uint','no','no','Tags quick list size')";
351 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('TAGS_QUICKLIST_THRESHOLD','50','uint','yes','no','Tags quick list threshold')";
352 $query[] = "ALTER TABLE AttributeMap MODIFY COLUMN chapter_id int(10) unsigned NULL";
353 $query[] = "UPDATE AttributeMap SET chapter_id = NULL WHERE attr_id IN (SELECT id FROM Attribute WHERE type != 'dict')";
354 // ticket:239
355 $query[] = 'UPDATE AttributeValue SET uint_value = 1018 WHERE uint_value = 731 AND attr_id IN (SELECT attr_id FROM AttributeMap WHERE chapter_id = 12)';
356 $query[] = 'DELETE FROM Dictionary WHERE dict_key = 731';
357 $query = array_merge ($query, reloadDictionary ($batchid));
358 $query[] = "UPDATE Config SET vartype='uint' WHERE varname='RACKS_PER_ROW'";
359 $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')";
360 $query[] = "UPDATE Config SET varvalue = '0.17.3' WHERE varname = 'DB_VERSION'";
361 break;
362 case '0.17.4':
363 $query[] = "ALTER TABLE Link ENGINE=InnoDB";
364 $query[] = "ALTER TABLE Port ENGINE=InnoDB";
365 $query[] = "ALTER TABLE IPv4RS ENGINE=InnoDB";
366 $query[] = "ALTER TABLE IPv4RSPool ENGINE=InnoDB";
367 $query[] = "ALTER TABLE AttributeValue ENGINE=InnoDB";
368 $query[] = "ALTER TABLE RackObject ENGINE=InnoDB";
369 $query[] = "ALTER TABLE IPv4NAT ENGINE=InnoDB";
370 $query[] = "ALTER TABLE IPv4LB ENGINE=InnoDB";
371 $query[] = "ALTER TABLE IPv4VS ENGINE=InnoDB";
372 $query[] = "DELETE FROM IPv4RS WHERE rspool_id NOT IN (SELECT id FROM IPv4RSPool)";
373 $query[] = "ALTER TABLE Link ADD CONSTRAINT `Link-FK-b` FOREIGN KEY (portb) REFERENCES Port (id)";
374 $query[] = "ALTER TABLE Link ADD CONSTRAINT `Link-FK-a` FOREIGN KEY (porta) REFERENCES Port (id)";
375 $query[] = "ALTER TABLE IPv4RS ADD CONSTRAINT `IPv4RS-FK` FOREIGN KEY (rspool_id) REFERENCES IPv4RSPool (id) ON DELETE CASCADE";
376 $query[] = "ALTER TABLE AttributeValue ADD CONSTRAINT `AttributeValue-FK-object_id` FOREIGN KEY (object_id) REFERENCES RackObject (id)";
377 $query[] = "ALTER TABLE IPv4NAT ADD CONSTRAINT `IPv4NAT-FK-object_id` FOREIGN KEY (object_id) REFERENCES RackObject (id)";
378 $query[] = "ALTER TABLE Port ADD CONSTRAINT `Port-FK-object_id` FOREIGN KEY (object_id) REFERENCES RackObject (id)";
379 $query[] = "ALTER TABLE IPv4LB ADD CONSTRAINT `IPv4LB-FK-rspool_id` FOREIGN KEY (rspool_id) REFERENCES IPv4RSPool (id)";
380 $query[] = "ALTER TABLE IPv4LB ADD CONSTRAINT `IPv4LB-FK-object_id` FOREIGN KEY (object_id) REFERENCES RackObject (id)";
381 $query[] = "ALTER TABLE IPv4LB ADD CONSTRAINT `IPv4LB-FK-vs_id` FOREIGN KEY (vs_id) REFERENCES IPv4VS (id)";
382 $query = array_merge ($query, reloadDictionary ($batchid));
383 $query[] = "UPDATE Config SET varvalue = '0.17.4' WHERE varname = 'DB_VERSION'";
384 break;
385 case '0.17.5':
386 $query[] = "ALTER TABLE TagTree ENGINE=InnoDB";
387 $query[] = "ALTER TABLE TagStorage ENGINE=InnoDB";
388 $query[] = "ALTER TABLE TagStorage ADD CONSTRAINT `TagStorage-FK-tag_id` FOREIGN KEY (tag_id) REFERENCES TagTree (id)";
389 $query[] = "ALTER TABLE TagTree ADD CONSTRAINT `TagTree-K-parent_id` FOREIGN KEY (parent_id) REFERENCES TagTree (id)";
390 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (21,1195)';
391 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (22,1196)';
392 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (23,1196)';
393 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (20,1195)';
394 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (25,1202)';
395 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (26,1202)';
396 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (27,1204)';
397 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (28,1204)';
398 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1083,1195)';
399 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1084,1084)';
400 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1195,20)';
401 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1195,21)';
402 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1195,1083)';
403 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1195,1195)';
404 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1196,22)';
405 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1196,23)';
406 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1196,1196)';
407 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1197,1197)';
408 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1198,1199)';
409 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1199,1198)';
410 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1200,1200)';
411 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1201,1201)';
412 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1202,25)';
413 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1202,26)';
414 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1202,1202)';
415 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1203,1203)';
416 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1204,27)';
417 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1204,28)';
418 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1204,1204)';
419 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1205,1205)';
420 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1206,1207)';
421 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1207,1206)';
422 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1316,1316)';
423 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (16, 1322)';
424 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1322, 16)';
425 $query[] = 'DELETE FROM PortCompat WHERE type1 = 16 AND type2 = 16';
426 for ($i = 1209; $i <= 1300; $i++)
427 $query[] = "INSERT INTO PortCompat (type1, type2) VALUES (${i}, ${i})";
428 $query[] = "
429 CREATE TABLE `PortInnerInterface` (
430 `id` int(10) unsigned NOT NULL,
431 `iif_name` char(16) NOT NULL,
432 PRIMARY KEY (`id`),
433 UNIQUE KEY `iif_name` (`iif_name`)
434 ) ENGINE=InnoDB";
435 $query[] = "INSERT INTO `PortInnerInterface` VALUES (1,'hardwired')";
436 $query[] = "INSERT INTO `PortInnerInterface` VALUES (2,'SFP-100')";
437 $query[] = "INSERT INTO `PortInnerInterface` VALUES (3,'GBIC')";
438 $query[] = "INSERT INTO `PortInnerInterface` VALUES (4,'SFP-1000')";
439 $query[] = "INSERT INTO `PortInnerInterface` VALUES (5,'XENPAK')";
440 $query[] = "INSERT INTO `PortInnerInterface` VALUES (6,'X2')";
441 $query[] = "INSERT INTO `PortInnerInterface` VALUES (7,'XPAK')";
442 $query[] = "INSERT INTO `PortInnerInterface` VALUES (8,'XFP')";
443 $query[] = "INSERT INTO `PortInnerInterface` VALUES (9,'SFP+')";
444 $query[] = "
445 CREATE TABLE `PortInterfaceCompat` (
446 `iif_id` int(10) unsigned NOT NULL,
447 `oif_id` int(10) unsigned NOT NULL,
448 UNIQUE KEY `pair` (`iif_id`,`oif_id`),
449 CONSTRAINT `PortInterfaceCompat-FK-iif_id` FOREIGN KEY (`iif_id`) REFERENCES `PortInnerInterface` (`id`)
450 ) ENGINE=InnoDB";
451 $query[] = "ALTER TABLE Port ADD COLUMN iif_id int unsigned NOT NULL AFTER name"; // will set iif_id to 0
452 $query[] = "UPDATE Port SET iif_id = 2 WHERE type = 1208";
453 $query[] = "UPDATE Port SET iif_id = 3 WHERE type = 1078";
454 $query[] = "UPDATE Port SET iif_id = 4 WHERE type = 1077";
455 $query[] = "UPDATE Port SET iif_id = 5 WHERE type = 1079";
456 $query[] = "UPDATE Port SET iif_id = 6 WHERE type = 1080";
457 $query[] = "UPDATE Port SET iif_id = 7 WHERE type = 1081";
458 $query[] = "UPDATE Port SET iif_id = 8 WHERE type = 1082";
459 $query[] = "UPDATE Port SET iif_id = 9 WHERE type = 1084";
460 $query[] = "UPDATE Port SET iif_id = 1 WHERE iif_id = 0";
461 $query[] = 'ALTER TABLE Port ADD UNIQUE `object_iif_oif_name` (object_id, iif_id, type, name)';
462 $query[] = 'ALTER TABLE Port DROP KEY `per_object`';
463 $base1000 = array (24, 34, 1202, 1203, 1204, 1205, 1206, 1207);
464 $base10000 = array (30, 35, 36, 37, 38, 39, 40);
465 $PICdata = array
466 (
467 1 => array (16, 19, 24, 29, 31, 33, 446, 681, 682, 1322),
468 2 => array (1208, 1195, 1196, 1197, 1198, 1199, 1200, 1201),
469 3 => array_merge (array (1078), $base1000),
470 4 => array_merge (array (1077), $base1000),
471 5 => array_merge (array (1079), $base10000),
472 6 => array_merge (array (1080), $base10000),
473 7 => array_merge (array (1081), $base10000),
474 8 => array_merge (array (1082), $base10000),
475 9 => array_merge (array (1084), $base10000),
476 );
477 // make sure all IIF/OIF pairs referenced from Port exist in PortInterfaceCompat before enabling FK
478 // iif_id doesn't exist at this point
479 $result = $dbxlink->query ('SELECT DISTINCT type FROM Port WHERE type NOT IN (1208, 1078, 1077, 1079, 1080, 1081, 1082, 1084)');
480 while ($row = $result->fetch (PDO::FETCH_ASSOC))
481 if (FALSE === array_search ($row['type'], $PICdata[1]))
482 array_push ($PICdata[1], $row['type']);
483 unset ($result);
484 foreach ($PICdata as $iif_id => $oif_ids)
485 foreach ($oif_ids as $oif_id)
486 $query[] = "INSERT INTO PortInterfaceCompat (iif_id, oif_id) VALUES (${iif_id}, ${oif_id})";
487 $query[] = "ALTER TABLE Port ADD CONSTRAINT `Port-FK-iif-oif` FOREIGN KEY (`iif_id`, `type`) REFERENCES `PortInterfaceCompat` (`iif_id`, `oif_id`)";
488 $query[] = 'UPDATE Port SET type = 1322 WHERE type = 16 AND (SELECT objtype_id FROM RackObject WHERE id = object_id) IN (2, 12)';
489 $query = array_merge ($query, reloadDictionary ($batchid));
490 $query[] = "DELETE FROM Config WHERE varname = 'default_port_type'";
491 $query[] = "INSERT INTO Config VALUES ('DEFAULT_PORT_IIF_ID','1','uint','no','no','Default port inner interface ID')";
492 $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')";
493 $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')";
494 $query[] = "UPDATE Chapter SET name = 'PortOuterInterface' WHERE id = 2";
495 // remap refs to duplicate records, which will be discarded (ticket:286)
496 $query[] = 'UPDATE AttributeValue SET uint_value = 147 WHERE uint_value = 1020 AND attr_id = 2';
497 $query[] = 'UPDATE AttributeValue SET uint_value = 377 WHERE uint_value = 1021 AND attr_id = 2';
498 $query[] = 'INSERT INTO AttributeMap (objtype_id, attr_id) VALUES (2, 1), (2, 3), (2, 5)';
499 $query[] = "UPDATE Config SET varvalue = '0.17.5' WHERE varname = 'DB_VERSION'";
500 break;
501 case '0.17.6':
502 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (28,'no','Voice/video hardware')";
503 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1323,1,NULL)";
504 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1323,2,28)";
505 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1323,3,NULL)";
506 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1323,5,NULL)";
507 $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)')";
508 $query = array_merge ($query, reloadDictionary ($batchid));
509 $query[] = "UPDATE Config SET varvalue = '0.17.6' WHERE varname = 'DB_VERSION'";
510 break;
511 case '0.17.7':
512 $query[] = "UPDATE Config SET varvalue = '0.17.7' WHERE varname = 'DB_VERSION'";
513 break;
514 case '0.17.8':
515 $query = array_merge ($query, reloadDictionary ($batchid));
516 $query[] = "ALTER TABLE TagTree DROP COLUMN valid_realm";
517 $query[] = "UPDATE Config SET varvalue = '0.17.8' WHERE varname = 'DB_VERSION'";
518 break;
519 case '0.17.9':
520 $query[] = "ALTER table Config add `is_userdefined` enum('yes','no') NOT NULL default 'no' AFTER `is_hidden`";
521 $query[] = "
522 CREATE TABLE `UserConfig` (
523 `varname` char(32) NOT NULL,
524 `varvalue` char(255) NOT NULL,
525 `user` char(64) NOT NULL,
526 UNIQUE KEY `user_varname` (`user`,`varname`)
527 ) TYPE=InnoDB";
528 $query[] = "UPDATE Config SET is_userdefined = 'yes' WHERE varname IN
529 (
530 'MASSCOUNT',
531 'MAXSELSIZE',
532 'ROW_SCALE',
533 'PORTS_PER_ROW',
534 'IPV4_ADDRS_PER_PAGE',
535 'DEFAULT_RACK_HEIGHT',
536 'DEFAULT_SLB_VS_PORT',
537 'DEFAULT_SLB_RS_PORT',
538 'DETECT_URLS',
539 'RACK_PRESELECT_THRESHOLD',
540 'DEFAULT_IPV4_RS_INSERVICE',
541 'DEFAULT_OBJECT_TYPE',
542 'SHOW_EXPLICIT_TAGS',
543 'SHOW_IMPLICIT_TAGS',
544 'SHOW_AUTOMATIC_TAGS',
545 'IPV4_AUTO_RELEASE',
546 'SHOW_LAST_TAB',
547 'EXT_IPV4_VIEW',
548 'TREE_THRESHOLD',
549 'ADDNEW_AT_TOP',
550 'IPV4_TREE_SHOW_USAGE',
551 'PREVIEW_TEXT_MAXCHARS',
552 'PREVIEW_TEXT_ROWS',
553 'PREVIEW_TEXT_COLS',
554 'PREVIEW_IMAGE_MAXPXS',
555 'VENDOR_SIEVE',
556 'RACKS_PER_ROW'
557 )";
558 $query[] = "UPDATE Config SET varvalue = '0.17.9' WHERE varname = 'DB_VERSION'";
559 break;
560 case '0.17.10':
561 $query = array_merge ($query, reloadDictionary ($batchid));
562 $query[] = "ALTER TABLE MountOperation ADD KEY (object_id)";
563 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('STATIC_FILTER','yes','string','no','no','yes','Enable Filter Caching');";
564 $query[] = "UPDATE Config SET varvalue = '0.17.10' WHERE varname = 'DB_VERSION'";
565 break;
566 case '0.17.11':
567 $query = array_merge ($query, reloadDictionary ($batchid));
568 $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');";
569 $query[] = "DELETE AttributeValue FROM AttributeValue JOIN Attribute where AttributeValue.attr_id = Attribute.id AND Attribute.type = 'dict' AND AttributeValue.uint_value = 0";
570 $query[] = "UPDATE Config SET varvalue = '0.17.11' WHERE varname = 'DB_VERSION'";
571 break;
572 case '0.18.0':
573 $query = array_merge ($query, reloadDictionary ($batchid));
574 $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')";
575 $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')";
576 $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')";
577 $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')";
578 $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')";
579 $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')";
580 $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')";
581 $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')";
582 $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')";
583 $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')";
584 $query[] = "ALTER TABLE IPv4Network ENGINE=InnoDB";
585 $query[] = "SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0";
586 $query[] = "
587 CREATE TABLE `CachedPAV` (
588 `object_id` int(10) unsigned NOT NULL,
589 `port_name` char(255) NOT NULL,
590 `vlan_id` int(10) unsigned NOT NULL default '0',
591 PRIMARY KEY (`object_id`,`port_name`,`vlan_id`),
592 KEY `vlan_id` (`vlan_id`),
593 CONSTRAINT `CachedPAV-FK-object-port` FOREIGN KEY (`object_id`, `port_name`) REFERENCES `CachedPVM` (`object_id`, `port_name`) ON DELETE CASCADE,
594 CONSTRAINT `CachedPAV-FK-vlan_id` FOREIGN KEY (`vlan_id`) REFERENCES `VLANValidID` (`vlan_id`)
595 ) ENGINE=InnoDB
596 ";
597 $query[] = "
598 CREATE TABLE `CachedPNV` (
599 `object_id` int(10) unsigned NOT NULL,
600 `port_name` char(255) NOT NULL,
601 `vlan_id` int(10) unsigned NOT NULL default '0',
602 PRIMARY KEY (`object_id`,`port_name`,`vlan_id`),
603 UNIQUE KEY `port_id` (`object_id`,`port_name`),
604 CONSTRAINT `CachedPNV-FK-compound` FOREIGN KEY (`object_id`, `port_name`, `vlan_id`) REFERENCES `CachedPAV` (`object_id`, `port_name`, `vlan_id`) ON DELETE CASCADE
605 ) ENGINE=InnoDB
606 ";
607 $query[] = "
608 CREATE TABLE `CachedPVM` (
609 `object_id` int(10) unsigned NOT NULL,
610 `port_name` char(255) NOT NULL,
611 `vlan_mode` enum('access','trunk') NOT NULL default 'access',
612 PRIMARY KEY (`object_id`,`port_name`),
613 CONSTRAINT `CachedPVM-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE
614 ) ENGINE=InnoDB
615 ";
616 $query[] = "
617 CREATE TABLE `PortAllowedVLAN` (
618 `object_id` int(10) unsigned NOT NULL,
619 `port_name` char(255) NOT NULL,
620 `vlan_id` int(10) unsigned NOT NULL default '0',
621 PRIMARY KEY (`object_id`,`port_name`,`vlan_id`),
622 KEY `vlan_id` (`vlan_id`),
623 CONSTRAINT `PortAllowedVLAN-FK-object-port` FOREIGN KEY (`object_id`, `port_name`) REFERENCES `PortVLANMode` (`object_id`, `port_name`) ON DELETE CASCADE,
624 CONSTRAINT `PortAllowedVLAN-FK-vlan_id` FOREIGN KEY (`vlan_id`) REFERENCES `VLANValidID` (`vlan_id`)
625 ) ENGINE=InnoDB
626 ";
627 $query[] = "
628 CREATE TABLE `PortNativeVLAN` (
629 `object_id` int(10) unsigned NOT NULL,
630 `port_name` char(255) NOT NULL,
631 `vlan_id` int(10) unsigned NOT NULL default '0',
632 PRIMARY KEY (`object_id`,`port_name`,`vlan_id`),
633 UNIQUE KEY `port_id` (`object_id`,`port_name`),
634 CONSTRAINT `PortNativeVLAN-FK-compound` FOREIGN KEY (`object_id`, `port_name`, `vlan_id`) REFERENCES `PortAllowedVLAN` (`object_id`, `port_name`, `vlan_id`) ON DELETE CASCADE
635 ) ENGINE=InnoDB
636 ";
637 $query[] = "
638 CREATE TABLE `PortVLANMode` (
639 `object_id` int(10) unsigned NOT NULL,
640 `port_name` char(255) NOT NULL,
641 `vlan_mode` enum('access','trunk') NOT NULL default 'access',
642 PRIMARY KEY (`object_id`,`port_name`),
643 CONSTRAINT `PortVLANMode-FK-object-port` FOREIGN KEY (`object_id`, `port_name`) REFERENCES `CachedPVM` (`object_id`, `port_name`)
644 ) ENGINE=InnoDB
645 ";
646 $query[] = "
647 CREATE TABLE `VLANDescription` (
648 `domain_id` int(10) unsigned NOT NULL,
649 `vlan_id` int(10) unsigned NOT NULL default '0',
650 `vlan_type` enum('ondemand','compulsory','alien') NOT NULL default 'ondemand',
651 `vlan_descr` char(255) default NULL,
652 PRIMARY KEY (`domain_id`,`vlan_id`),
653 KEY `vlan_id` (`vlan_id`),
654 CONSTRAINT `VLANDescription-FK-domain_id` FOREIGN KEY (`domain_id`) REFERENCES `VLANDomain` (`id`) ON DELETE CASCADE,
655 CONSTRAINT `VLANDescription-FK-vlan_id` FOREIGN KEY (`vlan_id`) REFERENCES `VLANValidID` (`vlan_id`)
656 ) ENGINE=InnoDB
657 ";
658 $query[] = "
659 CREATE TABLE `VLANDomain` (
660 `id` int(10) unsigned NOT NULL auto_increment,
661 `description` char(255) default NULL,
662 PRIMARY KEY (`id`),
663 UNIQUE KEY `description` (`description`)
664 ) ENGINE=InnoDB
665 ";
666 $query[] = "
667 CREATE TABLE `VLANIPv4` (
668 `domain_id` int(10) unsigned NOT NULL,
669 `vlan_id` int(10) unsigned NOT NULL,
670 `ipv4net_id` int(10) unsigned NOT NULL,
671 UNIQUE KEY `network-domain` (`ipv4net_id`,`domain_id`),
672 KEY `VLANIPv4-FK-compound` (`domain_id`,`vlan_id`),
673 CONSTRAINT `VLANIPv4-FK-compound` FOREIGN KEY (`domain_id`, `vlan_id`) REFERENCES `VLANDescription` (`domain_id`, `vlan_id`) ON DELETE CASCADE,
674 CONSTRAINT `VLANIPv4-FK-ipv4net_id` FOREIGN KEY (`ipv4net_id`) REFERENCES `IPv4Network` (`id`) ON DELETE CASCADE
675 ) ENGINE=InnoDB
676 ";
677 $query[] = "
678 CREATE TABLE `VLANSTRule` (
679 `vst_id` int(10) unsigned NOT NULL,
680 `rule_no` int(10) unsigned NOT NULL,
681 `port_pcre` char(255) NOT NULL,
682 `port_role` enum('access','trunk','uplink','downlink','none') NOT NULL default 'none',
683 `wrt_vlans` char(255) default NULL,
684 `description` char(255) default NULL,
685 UNIQUE KEY `vst-rule` (`vst_id`,`rule_no`),
686 CONSTRAINT `VLANSTRule-FK-vst_id` FOREIGN KEY (`vst_id`) REFERENCES `VLANSwitchTemplate` (`id`) ON DELETE CASCADE
687 ) ENGINE=InnoDB
688 ";
689 $query[] = "
690 CREATE TABLE `VLANSwitch` (
691 `object_id` int(10) unsigned NOT NULL,
692 `domain_id` int(10) unsigned NOT NULL,
693 `template_id` int(10) unsigned NOT NULL,
694 `mutex_rev` int(10) unsigned NOT NULL default '0',
695 `out_of_sync` enum('yes','no') NOT NULL default 'yes',
696 `last_errno` int(10) unsigned NOT NULL default '0',
697 `last_change` timestamp NOT NULL default '0000-00-00 00:00:00',
698 `last_push_started` timestamp NOT NULL default '0000-00-00 00:00:00',
699 `last_push_finished` timestamp NOT NULL default '0000-00-00 00:00:00',
700 `last_error_ts` timestamp NOT NULL default '0000-00-00 00:00:00',
701 UNIQUE KEY `object_id` (`object_id`),
702 KEY `domain_id` (`domain_id`),
703 KEY `template_id` (`template_id`),
704 KEY `out_of_sync` (`out_of_sync`),
705 KEY `last_errno` (`last_errno`),
706 CONSTRAINT `VLANSwitch-FK-domain_id` FOREIGN KEY (`domain_id`) REFERENCES `VLANDomain` (`id`),
707 CONSTRAINT `VLANSwitch-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`),
708 CONSTRAINT `VLANSwitch-FK-template_id` FOREIGN KEY (`template_id`) REFERENCES `VLANSwitchTemplate` (`id`)
709 ) ENGINE=InnoDB
710 ";
711 $query[] = "
712 CREATE TABLE `VLANSwitchTemplate` (
713 `id` int(10) unsigned NOT NULL auto_increment,
714 `max_local_vlans` int(10) unsigned default NULL,
715 `description` char(255) default NULL,
716 PRIMARY KEY (`id`),
717 UNIQUE KEY `description` (`description`)
718 ) ENGINE=InnoDB
719 ";
720 $query[] = "
721 CREATE TABLE `VLANValidID` (
722 `vlan_id` int(10) unsigned NOT NULL default '1',
723 PRIMARY KEY (`vlan_id`)
724 ) ENGINE=InnoDB
725 ";
726 $query[] = "SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS";
727 for ($i = 1; $i <= 4094; $i++)
728 $query[] = "INSERT INTO VLANValidID (vlan_id) VALUES (${i})";
729 $query[] = "UPDATE Config SET varvalue = '0.18.0' WHERE varname = 'DB_VERSION'";
730 break;
731 case '0.18.1':
732 $query = array_merge ($query, reloadDictionary ($batchid));
733 $query[] = "ALTER TABLE Atom ENGINE=InnoDB";
734 $query[] = "ALTER TABLE AttributeMap ENGINE=InnoDB";
735 $query[] = "ALTER TABLE Config ENGINE=InnoDB";
736 $query[] = "ALTER TABLE IPv4Address ENGINE=InnoDB";
737 $query[] = "ALTER TABLE IPv4Allocation ENGINE=InnoDB";
738 $query[] = "ALTER TABLE Molecule ENGINE=InnoDB";
739 $query[] = "ALTER TABLE MountOperation ENGINE=InnoDB";
740 $query[] = "ALTER TABLE PortCompat ENGINE=InnoDB";
741 $query[] = "ALTER TABLE Rack ENGINE=InnoDB";
742 $query[] = "ALTER TABLE RackHistory ENGINE=InnoDB";
743 $query[] = "ALTER TABLE RackObjectHistory ENGINE=InnoDB";
744 $query[] = "ALTER TABLE RackRow ENGINE=InnoDB";
745 $query[] = "ALTER TABLE RackSpace ENGINE=InnoDB";
746 $query[] = "ALTER TABLE Script ENGINE=InnoDB";
747 $query[] = "ALTER TABLE AttributeValue DROP FOREIGN KEY `AttributeValue-FK-object_id`";
748 $query[] = "ALTER TABLE AttributeValue ADD CONSTRAINT `AttributeValue-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE";
749 $query[] = "ALTER TABLE RackObjectHistory ADD KEY (id)";
750 $query[] = "ALTER TABLE RackObjectHistory ADD CONSTRAINT `RackObjectHistory-FK-object_id` FOREIGN KEY (id) REFERENCES `RackObject` (`id`) ON DELETE CASCADE";
751 $query[] = "ALTER TABLE MountOperation ADD CONSTRAINT `MountOperation-FK-object_id` FOREIGN KEY (object_id) REFERENCES RackObject (id) ON DELETE CASCADE";
752 $query[] = "ALTER TABLE RackSpace ADD CONSTRAINT `RackSpace-FK-object_id` FOREIGN KEY (object_id) REFERENCES RackObject (id) ON DELETE CASCADE";
753 $query[] = "ALTER TABLE Link DROP FOREIGN KEY `Link-FK-a`";
754 $query[] = "ALTER TABLE Link ADD CONSTRAINT `Link-FK-a` FOREIGN KEY (`porta`) REFERENCES `Port` (`id`) ON DELETE CASCADE";
755 $query[] = "ALTER TABLE Link DROP FOREIGN KEY `Link-FK-b`";
756 $query[] = "ALTER TABLE Link ADD CONSTRAINT `Link-FK-b` FOREIGN KEY (`portb`) REFERENCES `Port` (`id`) ON DELETE CASCADE";
757 $query[] = "ALTER TABLE Port DROP FOREIGN KEY `Port-FK-object_id`";
758 $query[] = "ALTER TABLE Port ADD CONSTRAINT `Port-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE";
759 $query[] = "ALTER TABLE AttributeMap MODIFY `chapter_id` int(10) unsigned default NULL";
760 $query[] = "ALTER TABLE IPv4Address MODIFY `ip` int(10) unsigned NOT NULL default '0'";
761 $query[] = "ALTER TABLE IPv4Address MODIFY `name` char(255) NOT NULL default ''";
762 $query[] = "ALTER TABLE IPv4Allocation MODIFY `object_id` int(10) unsigned NOT NULL default '0'";
763 $query[] = "ALTER TABLE IPv4Allocation MODIFY `ip` int(10) unsigned NOT NULL default '0'";
764 $query[] = "ALTER TABLE IPv4Allocation MODIFY `name` char(255) NOT NULL default ''";
765 $query[] = "ALTER TABLE IPv4NAT MODIFY `object_id` int(10) unsigned NOT NULL default '0'";
766 $query[] = "ALTER TABLE IPv4NAT MODIFY `proto` enum('TCP','UDP') NOT NULL default 'TCP'";
767 $query[] = "ALTER TABLE IPv4NAT MODIFY `localip` int(10) unsigned NOT NULL default '0'";
768 $query[] = "ALTER TABLE IPv4NAT MODIFY `localport` smallint(5) unsigned NOT NULL default '0'";
769 $query[] = "ALTER TABLE IPv4NAT MODIFY `remoteip` int(10) unsigned NOT NULL default '0'";
770 $query[] = "ALTER TABLE IPv4NAT MODIFY `remoteport` smallint(5) unsigned NOT NULL default '0'";
771 $query[] = "ALTER TABLE IPv4Network MODIFY `ip` int(10) unsigned NOT NULL default '0'";
772 $query[] = "ALTER TABLE IPv4Network MODIFY `mask` int(10) unsigned NOT NULL default '0'";
773 $query[] = "ALTER TABLE Link MODIFY `porta` int(10) unsigned NOT NULL default '0'";
774 $query[] = "ALTER TABLE Link MODIFY `portb` int(10) unsigned NOT NULL default '0'";
775 $query[] = "ALTER TABLE MountOperation MODIFY `object_id` int(10) unsigned NOT NULL default '0'";
776 $query[] = "ALTER TABLE MountOperation MODIFY `ctime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP";
777 $query[] = "ALTER TABLE Port MODIFY `object_id` int(10) unsigned NOT NULL default '0'";
778 $query[] = "ALTER TABLE Port MODIFY `name` char(255) NOT NULL default ''";
779 $query[] = "ALTER TABLE Port MODIFY `type` int(10) unsigned NOT NULL default '0'";
780 $query[] = "ALTER TABLE PortCompat MODIFY `type1` int(10) unsigned NOT NULL default '0'";
781 $query[] = "ALTER TABLE PortCompat MODIFY `type2` int(10) unsigned NOT NULL default '0'";
782 $query[] = "ALTER TABLE RackHistory MODIFY `ctime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP";
783 $query[] = "ALTER TABLE RackObjectHistory MODIFY `ctime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP";
784 $query[] = "ALTER TABLE TagStorage MODIFY `tag_id` int(10) unsigned NOT NULL default '0'";
785 $query[] = "ALTER TABLE UserAccount MODIFY `user_name` char(64) NOT NULL default ''";
786 $query[] = "UPDATE Config SET varvalue = '0.18.1' WHERE varname = 'DB_VERSION'";
787 break;
788 case '0.18.2':
789 $query = array_merge ($query, reloadDictionary ($batchid));
790 $query[] = "ALTER TABLE Rack ADD CONSTRAINT `Rack-FK-row_id` FOREIGN KEY (row_id) REFERENCES RackRow (id)";
791 $query[] = "ALTER TABLE RackRow ADD UNIQUE KEY `name` (name)";
792 $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')";
793 $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')";
794 $query[] = "UPDATE Config SET varvalue = '0.18.2' WHERE varname = 'DB_VERSION'";
795 break;
796 case '0.18.3':
797 $query = array_merge ($query, reloadDictionary ($batchid));
798 $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'";
799 $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)')";
800 $query[] = "UPDATE Config SET varvalue = '0.18.3' WHERE varname = 'DB_VERSION'";
801 break;
802 case '0.18.4':
803 $query = array_merge ($query, reloadDictionary ($batchid));
804 $query[] = "ALTER TABLE VLANSTRule MODIFY port_role enum('access','trunk','anymode','uplink','downlink','none') NOT NULL default 'none'";
805 $query[] = "UPDATE Config SET varvalue = '0.18.4' WHERE varname = 'DB_VERSION'";
806 break;
807 case '0.18.5':
808 $query = array_merge ($query, reloadDictionary ($batchid));
809 $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')";
810 $query[] = "ALTER TABLE `IPv4LB` ADD COLUMN `prio` int(10) unsigned DEFAULT NULL AFTER `vs_id`";
811 $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')";
812 $query[] = "UPDATE Config SET varvalue = '0.18.5' WHERE varname = 'DB_VERSION'";
813 break;
814 case '0.18.6':
815 $query = array_merge ($query, reloadDictionary ($batchid));
816 $query[] = "UPDATE Config SET varvalue = '0.18.6' WHERE varname = 'DB_VERSION'";
817 break;
818 case '0.18.7':
819 $query = array_merge ($query, reloadDictionary ($batchid));
820 $query[] = "UPDATE Config SET varvalue = '0.18.7' WHERE varname = 'DB_VERSION'";
821 break;
822 case '0.19.0':
823 $query = array_merge ($query, reloadDictionary ($batchid));
824 $query[] = 'ALTER TABLE `File` ADD `thumbnail` LONGBLOB NULL AFTER `atime`';
825 $query[] = "
826 CREATE TABLE `IPv6Address` (
827 `ip` binary(16) NOT NULL,
828 `name` char(255) NOT NULL default '',
829 `reserved` enum('yes','no') default NULL,
830 PRIMARY KEY (`ip`)
831 ) ENGINE=InnoDB
832 ";
833 $query[] = "
834 CREATE TABLE `IPv6Allocation` (
835 `object_id` int(10) unsigned NOT NULL default '0',
836 `ip` binary(16) NOT NULL,
837 `name` char(255) NOT NULL default '',
838 `type` enum('regular','shared','virtual','router') default NULL,
839 PRIMARY KEY (`object_id`,`ip`),
840 CONSTRAINT `IPv6Allocation-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE
841 ) ENGINE=InnoDB
842 ";
843 $query[] = "
844 CREATE TABLE `IPv6Network` (
845 `id` int(10) unsigned NOT NULL auto_increment,
846 `ip` binary(16) NOT NULL,
847 `mask` int(10) unsigned NOT NULL,
848 `last_ip` binary(16) NOT NULL,
849 `name` char(255) default NULL,
850 `comment` text,
851 PRIMARY KEY (`id`),
852 UNIQUE KEY `ip` (`ip`,`mask`)
853 ) ENGINE=InnoDB
854 ";
855 $query[] = "
856 CREATE TABLE `VLANIPv6` (
857 `domain_id` int(10) unsigned NOT NULL,
858 `vlan_id` int(10) unsigned NOT NULL,
859 `ipv6net_id` int(10) unsigned NOT NULL,
860 UNIQUE KEY `network-domain` (`ipv6net_id`,`domain_id`),
861 KEY `VLANIPv6-FK-compound` (`domain_id`,`vlan_id`),
862 CONSTRAINT `VLANIPv6-FK-compound` FOREIGN KEY (`domain_id`, `vlan_id`) REFERENCES `VLANDescription` (`domain_id`, `vlan_id`) ON DELETE CASCADE,
863 CONSTRAINT `VLANIPv6-FK-ipv6net_id` FOREIGN KEY (`ipv6net_id`) REFERENCES `IPv6Network` (`id`) ON DELETE CASCADE
864 ) ENGINE=InnoDB
865 ";
866 $query[] = "
867 CREATE TABLE IF NOT EXISTS `ObjectLog` (
868 `id` int(10) NOT NULL AUTO_INCREMENT,
869 `object_id` int(10) NOT NULL,
870 `user` varchar(64) NOT NULL,
871 `date` datetime NOT NULL,
872 `content` text NOT NULL,
873 PRIMARY KEY (`id`)
874 ) ENGINE=InnoDB
875 ";
876 # Now we have the same structure of ObjectLog table, which objectlog.php
877 # could have left. Subsequent column updates will handle any existing data.
878 $query[] = "ALTER TABLE ObjectLog MODIFY COLUMN `id` int(10) unsigned NOT NULL AUTO_INCREMENT";
879 $query[] = "ALTER TABLE ObjectLog MODIFY COLUMN `object_id` int(10) unsigned NOT NULL";
880 $query[] = "ALTER TABLE ObjectLog MODIFY COLUMN `user` char(64) NOT NULL";
881 $query[] = "ALTER TABLE ObjectLog ADD KEY `object_id` (`object_id`)";
882 $query[] = "ALTER TABLE ObjectLog ADD KEY `date` (`date`)";
883 $query[] = "ALTER TABLE ObjectLog ADD CONSTRAINT `ObjectLog-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE";
884 # Now it's the way 0.19.0 is expecting it to be.
885 $query[] = "
886 CREATE TABLE `ObjectParentCompat` (
887 `parent_objtype_id` int(10) unsigned NOT NULL,
888 `child_objtype_id` int(10) unsigned NOT NULL,
889 UNIQUE KEY `parent_child` (`parent_objtype_id`,`child_objtype_id`)
890 ) ENGINE=InnoDB
891 ";
892 $query[] = "
893 CREATE TABLE `EntityLink` (
894 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
895 `parent_entity_type` enum('ipv4net','ipv4rspool','ipv4vs','ipv6net','object','rack','user') NOT NULL,
896 `parent_entity_id` int(10) unsigned NOT NULL,
897 `child_entity_type` enum('file','object') NOT NULL,
898 `child_entity_id` int(10) unsigned NOT NULL,
899 PRIMARY KEY (`id`),
900 UNIQUE KEY `EntityLink-unique` (`parent_entity_type`,`parent_entity_id`,`child_entity_type`,`child_entity_id`)
901 ) ENGINE=InnoDB
902 ";
903 $query[] = "ALTER TABLE `TagStorage` CHANGE COLUMN `entity_realm` `entity_realm` ENUM('file','ipv4net','ipv4vs','ipv4rspool','object','rack','user','ipv6net') NOT NULL DEFAULT 'object' FIRST";
904 $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`";
905 $query[] = 'ALTER TABLE Link ADD COLUMN cable char(64) NULL AFTER portb';
906 $query[] = 'ALTER TABLE RackSpace ADD CONSTRAINT `RackSpace-FK-rack_id` FOREIGN KEY (rack_id) REFERENCES Rack (id)';
907 $query[] = "ALTER TABLE `IPv4Allocation` ADD CONSTRAINT `IPv4Allocation-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE";
908 $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')";
909 $query[] = "ALTER TABLE `VLANSwitchTemplate` ADD COLUMN `mutex_rev` int(10) NOT NULL AFTER `id`";
910 $query[] = "ALTER TABLE `VLANSwitchTemplate` ADD COLUMN `saved_by` char(64) NOT NULL AFTER `description`";
911 $query[] = "INSERT INTO `Attribute` (`id`, `type`, `name`) VALUES (26,'dict','Hypervisor')";
912 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (29,'no','Yes/No')";
913 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (30,'no','network chassis models')";
914 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (31,'no','server chassis models')";
915 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (32,'no','virtual switch models')";
916 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (33,'no','virtual switch OS type')";
917 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (4,26,29)";
918 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1502,1,NULL)";
919 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1502,2,31)";
920 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1502,3,NULL)";
921 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1502,14,NULL)";
922 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1502,20,NULL)";
923 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1502,21,NULL)";
924 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1502,22,NULL)";
925 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,1,NULL)";
926 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,2,30)";
927 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,3,NULL)";
928 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,4,14)";
929 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,5,NULL)";
930 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,14,NULL)";
931 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,16,NULL)";
932 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,17,NULL)";
933 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,18,NULL)";
934 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,20,NULL)";
935 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,21,NULL)";
936 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,22,NULL)";
937 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,24,NULL)";
938 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1504,3,NULL)";
939 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1504,4,13)";
940 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1504,14,NULL)";
941 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1504,24,NULL)";
942 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1505,14,NULL)";
943 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1506,14,NULL)";
944 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1506,17,NULL)";
945 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1506,18,NULL)";
946 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,1,NULL)";
947 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,2,32)";
948 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,3,NULL)";
949 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,4,33)";
950 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,5,NULL)";
951 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,14,NULL)";
952 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,20,NULL)";
953 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,21,NULL)";
954 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,22,NULL)";
955 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (3,13)";
956 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (4,1504)";
957 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (4,1507)";
958 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1502,4)";
959 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1503,8)";
960 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1505,4)";
961 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1505,1504)";
962 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1505,1506)";
963 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1505,1507)";
964 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1506,1504)";
965 $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')";
966 $query[] = "UPDATE Config SET varvalue = CONCAT(varvalue, ' or {\$typeid_1502} or {\$typeid_1503} or {\$typeid_1504} or {\$typeid_1507}') WHERE varname = 'IPV4OBJ_LISTSRC'";
967 $query[] = "UPDATE Config SET varvalue = '8' WHERE varname = 'MASSCOUNT'";
968 $query[] = "UPDATE RackObject SET label = NULL WHERE label = ''";
969 // Move barcode data so the column can be dropped
970 $result = $dbxlink->query ('SELECT id, objtype_id, barcode FROM RackObject WHERE barcode IS NOT NULL');
971 $rows = $result->fetchAll (PDO::FETCH_ASSOC);
972 unset ($result);
973 foreach ($rows as $row)
974 {
975 // Determine if this object type has the 'OEM S/N 1' attribute associated with it, and if it's set
976 $sn_query = "SELECT (SELECT COUNT(*) FROM AttributeMap WHERE objtype_id=${row['objtype_id']} AND attr_id=1) AS AM_count, ";
977 $sn_query .= "(SELECT COUNT(*) FROM AttributeValue WHERE object_id=${row['id']} AND attr_id=1) AS AV_count";
978 $sn_result = $dbxlink->query ($sn_query);
979 $sn_row = $sn_result->fetch (PDO::FETCH_ASSOC);
980 if ($sn_row['AM_count'] == 1 && $sn_row['AV_count'] == 0)
981 {
982 // 'OEM S/N 1' attribute is mapped to this object type, but it is not set. Good!
983 // Copy the barcode value to the attribute.
984 $query[] = "INSERT INTO AttributeValue (`object_id`, `attr_id`, `string_value`) VALUES (${row['id']}, 1, '${row['barcode']}')";
985 }
986 else
987 {
988 // Some other set of circumstances. Not as good!
989 // Copy the barcode value to a new ObjectLog record.
990 $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']}')";
991 }
992 unset ($sn_query, $sn_result, $sn_row);
993 }
994 $query[] = 'ALTER TABLE RackObject DROP COLUMN `barcode`';
995 $query[] = 'ALTER TABLE RackObjectHistory DROP COLUMN `barcode`';
996 $query[] = 'ALTER TABLE `VLANSwitchTemplate` DROP COLUMN `max_local_vlans`';
997 $query[] = "UPDATE Config SET varvalue = '0.19.0' WHERE varname = 'DB_VERSION'";
998 break;
999 case '0.19.1':
1000 $query = array_merge ($query, reloadDictionary ($batchid));
1001 $query[] = "ALTER TABLE `Config` CHANGE COLUMN `varvalue` `varvalue` text NOT NULL";
1002 $query[] = "ALTER TABLE `UserConfig` CHANGE COLUMN `varvalue` `varvalue` text NOT NULL";
1003 $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')";
1004 $result = $dbxlink->query ("SHOW TABLES LIKE 'Objectlog'");
1005 $rows = $result->fetchAll (PDO::FETCH_ASSOC);
1006 unset ($result);
1007 if (count ($rows))
1008 {
1009 # Now the ObjectLog merge... again, because the original table is named
1010 # "Objectlog". The job is to merge contents of Objectlog and ObjectLog
1011 # into the latter.
1012 $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)";
1013 $query[] = "DELETE FROM Objectlog WHERE object_id IN(SELECT id FROM RackObject)";
1014 # Don't delete the old table, if the merge wasn't exhaustive.
1015 $result = $dbxlink->query ('SELECT COUNT(*) AS c FROM Objectlog WHERE object_id NOT IN(SELECT id FROM RackObject)');
1016 $row = $result->fetch (PDO::FETCH_ASSOC);
1017 unset ($result);
1018 if ($row['c'] == 0)
1019 $query[] = 'DROP TABLE Objectlog';
1020 else
1021 $query[] = 'ALTER TABLE Objectlog RENAME TO Objectlog_old_unmerged';
1022 }
1023 $query[] = "UPDATE Config SET varvalue = '0.19.1' WHERE varname = 'DB_VERSION'";
1024 break;
1025 case '0.19.2':
1026 $query = array_merge ($query, reloadDictionary ($batchid));
1027 $query[] = "ALTER TABLE IPv4Allocation ADD KEY `ip` (`ip`)";
1028 $query[] = "ALTER TABLE IPv6Allocation ADD KEY `ip` (`ip`)";
1029 $query[] = "ALTER TABLE IPv4VS ADD KEY `vip` (`vip`)";
1030 $query[] = "ALTER TABLE IPv4RS ADD KEY `rsip` (`rsip`)";
1031 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (34,'no','power supply chassis models')";
1032 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (35,'no','power supply models')";
1033 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1397,1,NULL)";
1034 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1397,2,34)";
1035 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1397,14,NULL)";
1036 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1397,21,NULL)";
1037 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1397,22,NULL)";
1038 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1398,1,NULL)";
1039 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1398,2,35)";
1040 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1398,14,NULL)";
1041 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1398,21,NULL)";
1042 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1398,22,NULL)";
1043 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1397,1398)";
1044 $query[] = "INSERT INTO `PortCompat` (`type1`, `type2`) VALUES (1399,1399)";
1045 $query[] = "INSERT INTO `PortInterfaceCompat` (`iif_id`, `oif_id`) VALUES (1,1399)";
1046 $query[] = "UPDATE Config SET varvalue = CONCAT(varvalue, ' or {\$typeid_1397}') WHERE varname = 'IPV4OBJ_LISTSRC'";
1047 $query[] = "ALTER TABLE AttributeValue ADD KEY `attr_id-uint_value` (`attr_id`,`uint_value`)";
1048 $query[] = "ALTER TABLE AttributeValue ADD KEY `attr_id-string_value` (`attr_id`,`string_value`(12))";
1049 $query[] = "UPDATE Config SET varvalue = '0.19.2' WHERE varname = 'DB_VERSION'";
1050 break;
1051 case '0.19.3':
1052 $query = array_merge ($query, reloadDictionary ($batchid));
1053 $query[] = "DELETE FROM RackSpace WHERE object_id IS NULL AND state = 'T'";
1054 $query[] = "UPDATE Config SET varvalue = '0.19.3' WHERE varname = 'DB_VERSION'";
1055 break;
1056 case '0.19.4':
1057 $query = array_merge ($query, reloadDictionary ($batchid));
1058 $query[] = "UPDATE Config SET varvalue = '0.19.4' WHERE varname = 'DB_VERSION'";
1059 break;
1060 case '0.19.5':
1061 $query = array_merge ($query, reloadDictionary ($batchid));
1062 // Add 'virtual port' to 'virtual port' mapping
1063 $query[] = "INSERT INTO `PortCompat` (`type1`,`type2`) VALUES (1469,1469)";
1064 $query[] = "INSERT INTO `PortInterfaceCompat` (`iif_id`,`oif_id`) VALUES (1,1469)";
1065 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('SSH_OBJS_LISTSRC','none','string','yes','no','yes','Rackcode filter for SSH-managed objects')";
1066 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('TELNET_OBJS_LISTSRC','none','string','yes','no','yes','Rackcode filter for telnet-managed objects')";
1067 $query[] = "UPDATE Link SET cable = NULL WHERE cable = ''";
1068 $query[] = "ALTER TABLE AttributeValue MODIFY string_value char(255) DEFAULT NULL";
1069 $query[] = "UPDATE Config SET varvalue = '0.19.5' WHERE varname = 'DB_VERSION'";
1070 break;
1071 case '0.19.6':
1072 $query = array_merge ($query, reloadDictionary ($batchid));
1073 $query[] = "UPDATE Config SET varvalue = '0.19.6' WHERE varname = 'DB_VERSION'";
1074 break;
1075 case '0.19.7':
1076 $query = array_merge ($query, reloadDictionary ($batchid));
1077 # A plain "ALTER TABLE Attribute" can leave AUTO_INCREMENT in an odd
1078 # state, hence the table swap.
1079 $query[] = "
1080 CREATE TABLE `Attribute_new` (
1081 `id` int(10) unsigned NOT NULL auto_increment,
1082 `type` enum('string','uint','float','dict') default NULL,
1083 `name` char(64) default NULL,
1084 PRIMARY KEY (`id`),
1085 UNIQUE KEY `name` (`name`)
1086 ) ENGINE=InnoDB
1087 ";
1088 $query[] = "INSERT INTO Attribute_new SELECT * FROM Attribute";
1089 $query[] = "INSERT INTO Attribute_new VALUES (9999, 'string', 'base MAC address')";
1090 $query[] = "DROP TABLE Attribute";
1091 $query[] = "ALTER TABLE Attribute_new RENAME TO Attribute";
1092 $query[] = "ALTER TABLE AttributeMap ADD KEY (attr_id)";
1093 $query[] = "DELETE FROM AttributeMap WHERE attr_id NOT IN (SELECT id FROM Attribute)";
1094 $query[] = "ALTER TABLE AttributeMap ADD CONSTRAINT `AttributeMap-FK-attr_id` FOREIGN KEY (attr_id) REFERENCES Attribute (id)";
1095 $query[] = "DELETE FROM AttributeValue WHERE attr_id NOT IN (SELECT attr_id FROM AttributeMap)";
1096 $query[] = "ALTER TABLE AttributeValue ADD CONSTRAINT `AttributeValue-FK-attr_id` FOREIGN KEY (attr_id) REFERENCES AttributeMap (attr_id)";
1097 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1506,4)";
1098 $query[] = "INSERT INTO PortInnerInterface (id, iif_name) VALUES (10, 'QSFP+')";
1099 $query[] = "INSERT INTO PortInterfaceCompat VALUES (10, 1588)";
1100 $query[] = "UPDATE Config SET varvalue = '0.19.7' WHERE varname = 'DB_VERSION'";
1101 break;
1102 case '0.19.8':
1103 $query = array_merge ($query, reloadDictionary ($batchid));
1104 $query[] = "ALTER TABLE UserAccount ENGINE=InnoDB";
1105 $query[] = "DELETE FROM UserConfig WHERE user NOT IN (SELECT user_name FROM UserAccount)";
1106 $query[] = "ALTER TABLE UserConfig ADD CONSTRAINT `UserConfig-FK-user` FOREIGN KEY (user) REFERENCES UserAccount (user_name) ON DELETE CASCADE";
1107 $query[] = "DELETE FROM UserConfig WHERE varname NOT IN (SELECT varname FROM Config)";
1108 $query[] = "ALTER TABLE UserConfig ADD KEY (varname)";
1109 $query[] = "ALTER TABLE UserConfig ADD CONSTRAINT `UserConfig-FK-varname` FOREIGN KEY (varname) REFERENCES Config (varname) ON DELETE CASCADE";
1110 $query[] = "ALTER TABLE Dictionary ENGINE=InnoDB";
1111 $query[] = "ALTER TABLE Chapter ENGINE=InnoDB";
1112 $query[] = "UPDATE Chapter SET id = 9999 WHERE id = 22";
1113 $query[] = "UPDATE AttributeMap SET chapter_id = 9999 WHERE chapter_id = 22";
1114 $query[] = "UPDATE Dictionary SET chapter_id = 9999 WHERE chapter_id = 22";
1115 $query[] = "DELETE FROM Dictionary WHERE chapter_id NOT IN (SELECT id FROM Chapter)";
1116 $query[] = "ALTER TABLE Dictionary ADD CONSTRAINT `Dictionary-FK-chapter_id` FOREIGN KEY (chapter_id) REFERENCES Chapter (id)";
1117 $query[] = "DELETE FROM AttributeMap WHERE chapter_id NOT IN (SELECT id FROM Chapter)";
1118 $query[] = "ALTER TABLE AttributeMap ADD KEY (chapter_id)";
1119 $query[] = "ALTER TABLE AttributeMap ADD CONSTRAINT `AttributeMap-FK-chapter_id` FOREIGN KEY (chapter_id) REFERENCES Chapter (id)";
1120 $query[] = "UPDATE Config SET varvalue = '0.19.8' WHERE varname = 'DB_VERSION'";
1121 break;
1122 case '0.20.0':
1123 $query = array_merge ($query, reloadDictionary ($batchid));
1124 $query[] = "
1125 CREATE TABLE `PortLog` (
1126 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
1127 `port_id` int(10) unsigned NOT NULL,
1128 `date` datetime NOT NULL,
1129 `user` varchar(64) NOT NULL,
1130 `message` text NOT NULL,
1131 PRIMARY KEY (`id`),
1132 KEY `port_id-date` (`port_id`,`date`),
1133 CONSTRAINT `PortLog_ibfk_1` FOREIGN KEY (`port_id`) REFERENCES `Port` (`id`) ON DELETE CASCADE
1134 ) ENGINE=InnoDB;
1135 ";
1136 $query[] = "
1137 CREATE TABLE `IPv4Log` (
1138 `id` int(10) NOT NULL AUTO_INCREMENT,
1139 `ip` int(10) unsigned NOT NULL,
1140 `date` datetime NOT NULL,
1141 `user` varchar(64) NOT NULL,
1142 `message` text NOT NULL,
1143 PRIMARY KEY (`id`),
1144 KEY `ip-date` (`ip`,`date`)
1145 ) ENGINE=InnoDB;
1146 ";
1147 for ($i = 1424; $i <= 1466; $i++) # CX, then 42 ER channels
1148 $query[] = "INSERT INTO `PortCompat` (`type1`, `type2`) VALUES (${i},${i})";
1149 # Dismiss some overly-specific OIF types in favour of more generic counterparts.
1150 $squeeze = array
1151 (
1152 1202 => array # 1000Base-SX
1153 (
1154 25, # 1000Base-SX (SC)
1155 26, # 1000Base-SX (LC)
1156 ),
1157 1204 => array # 1000Base-LX
1158 (
1159 27, # 1000Base-LX (SC)
1160 28, # 1000Base-LX (LC)
1161 ),
1162 1196 => array # 100Base-SX
1163 (
1164 22, # 100Base-SX (SC)
1165 23, # 100Base-SX (LC)
1166 ),
1167 1195 => array # 100Base-FX
1168 (
1169 20, # 100Base-FX (SC)
1170 21, # 100Base-FX (LC)
1171 1083, # 100Base-FX (MT-RJ)
1172 ),
1173 );
1174 foreach ($squeeze as $stays => $leaves)
1175 {
1176 $csv = implode (', ', $leaves);
1177 $query[] = "DELETE FROM PortCompat WHERE type1 IN(${csv}) OR type2 IN(${csv})";
1178 $query[] = "INSERT IGNORE INTO PortInterfaceCompat (iif_id, oif_id) SELECT iif_id, ${stays} FROM Port WHERE type IN (${csv})";
1179 $query[] = "UPDATE Port SET type = ${stays} WHERE type IN(${csv})";
1180 $query[] = "DELETE FROM PortInterfaceCompat WHERE oif_id IN(${csv})";
1181 }
1182 $query[] = "DELETE FROM Config WHERE varname = 'HNDP_RUNNERS_LISTSRC'";
1183 $query[] = "ALTER TABLE TagStorage MODIFY COLUMN entity_realm ENUM('file','ipv4net','ipv4vs','ipv4rspool','object','rack','user','ipv6net','vst') NOT NULL default 'object'";
1184 $query[] = "ALTER TABLE `TagStorage` ADD COLUMN `user` char(64) DEFAULT NULL, ADD COLUMN `date` datetime DEFAULT NULL";
1185
1186 // Rename object tables and keys
1187 $dbxlink->query ('ALTER TABLE `RackObject` RENAME TO `Object`');
1188 $dbxlink->query ('ALTER TABLE `RackObjectHistory` RENAME TO `ObjectHistory`');
1189 $query[] = 'ALTER TABLE `Object` DROP KEY `RackObject_asset_no`';
1190 $query[] = 'ALTER TABLE `Object` ADD UNIQUE KEY `asset_no` (`asset_no`)';
1191 $query[] = 'ALTER TABLE `Object` ADD KEY `type_id` (`objtype_id`,`id`)';
1192 $query[] = 'ALTER TABLE `ObjectHistory` DROP FOREIGN KEY `RackObjectHistory-FK-object_id`';
1193 $query[] = 'ALTER TABLE `ObjectHistory` ADD CONSTRAINT `ObjectHistory-FK-object_id` FOREIGN KEY (`id`) REFERENCES `Object` (`id`) ON DELETE CASCADE';
1194 $query[] = 'ALTER TABLE `RackSpace` DROP FOREIGN KEY `RackSpace-FK-rack_id`';
1195 // Rack height is now an attribute
1196 $query[] = "INSERT INTO `Attribute` (`id`,`type`,`name`) VALUES (27,'uint','Height, units')";
1197 $query[] = 'INSERT INTO `AttributeMap` (`objtype_id`,`attr_id`,`chapter_id`) VALUES (1560,27,NULL)';
1198 // Turn rows into objects
1199 $result = $dbxlink->query ('SELECT * FROM RackRow');
1200 $rows = $result->fetchAll (PDO::FETCH_ASSOC);
1201 unset ($result);
1202 foreach ($rows as $row)
1203 {
1204 $prepared = $dbxlink->prepare ('INSERT INTO `Object` (`label`,`objtype_id`) VALUES (?,?)');
1205 $prepared->execute (array($row['name'], 1561));
1206 $row_id = $dbxlink->lastInsertId();
1207 // Turn all racks in this row into objects
1208 $result = $dbxlink->query ("SELECT id, name, height, comment FROM Rack WHERE row_id=${row['id']}");
1209 $racks = $result->fetchAll (PDO::FETCH_ASSOC);
1210 unset ($result);
1211 foreach ($racks as $rack)
1212 {
1213 // Add the rack as an object, set the height as an attribute, link the rack to the row,
1214 // update rackspace, tags and files to reflect new rack_id, move history
1215 $prepared = $dbxlink->prepare ('INSERT INTO `Object` (`label`,`objtype_id`,`comment`) VALUES (?,?,?)');
1216 $prepared->execute (array($rack['name'], 1560, $rack['comment']));
1217 $rack_id = $dbxlink->lastInsertId();
1218 $query[] = "INSERT INTO `AttributeValue` (`object_id`,`attr_id`,`uint_value`) VALUES (${rack_id},27,${rack['height']})";
1219 $query[] = "INSERT INTO `EntityLink` (`parent_entity_type`,`parent_entity_id`,`child_entity_type`,`child_entity_id`) VALUES ('object',${row_id},'object',${rack_id})";
1220 $query[] = "UPDATE `RackSpace` SET `rack_id`=${rack_id} WHERE `rack_id`=${rack['id']}";
1221 $query[] = "UPDATE `Atom` SET `rack_id`=${rack_id} WHERE `rack_id`=${rack['id']}";
1222 $query[] = "UPDATE `TagStorage` SET `entity_id`=${rack_id} WHERE `entity_realm`='rack' AND `entity_id`=${rack['id']}";
1223 $query[] = "UPDATE `FileLink` SET `entity_id`=${rack_id} WHERE `entity_type`='rack' AND `entity_id`=${rack['id']}";
1224 $query[] = "INSERT INTO `ObjectHistory` (`id`,`name`,`objtype_id`,`comment`,`ctime`,`user_name`) SELECT ${rack_id},`name`,1560,`comment`,`ctime`,`user_name` FROM `RackHistory` WHERE `id`=${rack['id']}";
1225 }
1226 }
1227 $query[] = 'ALTER TABLE `RackSpace` ADD CONSTRAINT `RackSpace-FK-rack_id` FOREIGN KEY (`rack_id`) REFERENCES `Object` (`id`)';
1228 $query[] = 'DROP TABLE `Rack`';
1229 $query[] = 'DROP TABLE `RackRow`';
1230 $query[] = 'DROP TABLE `RackHistory`';
1231 $query[] = "
1232 CREATE TABLE `RackThumbnail` (
1233 `rack_id` int(10) unsigned NOT NULL,
1234 `thumb_data` blob,
1235 UNIQUE KEY `rack_id` (`rack_id`),
1236 CONSTRAINT `RackThumbnail-FK-rack_id` FOREIGN KEY (`rack_id`) REFERENCES `Object` (`id`) ON DELETE CASCADE
1237 ) ENGINE=InnoDB
1238 ";
1239 $query[] = "
1240 CREATE VIEW `Row` AS SELECT id, label AS name
1241 FROM `Object`
1242 WHERE objtype_id = 1561
1243 ";
1244 $query[] = "
1245 CREATE VIEW `Rack` AS SELECT O.id, O.label AS name, O.asset_no, O.has_problems, O.comment,
1246 AV.uint_value AS height,
1247 RT.thumb_data,
1248 Row.id AS row_id,
1249 Row.name AS row_name
1250 FROM `Object` O
1251 LEFT JOIN `AttributeValue` AV ON O.id = AV.object_id AND AV.attr_id = 27
1252 LEFT JOIN `RackThumbnail` RT ON O.id = RT.rack_id
1253 LEFT JOIN `EntityLink` EL ON O.id = EL.child_entity_id AND EL.parent_entity_type = 'object' AND EL.child_entity_type = 'object'
1254 INNER JOIN `Row` ON EL.parent_entity_id = Row.id
1255 WHERE O.objtype_id = 1560;
1256 ";
1257 $query[] = "
1258 CREATE VIEW `RackObject` AS SELECT * FROM `Object`
1259 WHERE `objtype_id` NOT IN (1560, 1561, 1562)
1260 ";
1261 $query[] = 'ALTER TABLE `EntityLink` ADD KEY `EntityLink-compound` (`parent_entity_type`,`child_entity_type`,`child_entity_id`)';
1262 $query[] = "UPDATE `Chapter` SET `name` = 'ObjectType' WHERE `id` = 1";
1263 $query[] = "DELETE FROM RackSpace WHERE object_id IS NULL AND state = 'T'";
1264
1265 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('SYNC_802Q_LISTSRC','','string','yes','no','no','List of VLAN switches sync is enabled on')";
1266 $query[] = "UPDATE `Config` SET is_userdefined='yes' WHERE varname='PROXIMITY_RANGE'";
1267 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('QUICK_LINK_PAGES','','string','yes','no','yes','List of pages to dislay in quick links')";
1268 $query[] = "ALTER TABLE `IPv4LB` MODIFY `prio` varchar(255) DEFAULT NULL";
1269 $query[] = "UPDATE Config SET varvalue = '0.20.0' WHERE varname = 'DB_VERSION'";
1270 break;
1271 default:
1272 showError ("executeUpgradeBatch () failed, because batch '${batchid}' isn't defined", __FUNCTION__);
1273 die;
1274 break;
1275 }
1276 $failures = array();
1277 echo "<tr><th>Executing batch '${batchid}'</th><td>";
1278 foreach ($query as $q)
1279 {
1280 try
1281 {
1282 $result = $dbxlink->query ($q);
1283 }
1284 catch (PDOException $e)
1285 {
1286 $errorInfo = $dbxlink->errorInfo();
1287 $failures[] = array ($q, $errorInfo[2]);
1288 }
1289 }
1290 if (!count ($failures))
1291 echo "<strong><font color=green>done</font></strong>";
1292 else
1293 {
1294 echo "<strong><font color=red>The following queries failed:</font></strong><br><pre>";
1295 foreach ($failures as $f)
1296 {
1297 list ($q, $i) = $f;
1298 echo "${q} -- ${i}\n";
1299 }
1300 echo "</pre>";
1301 }
1302 echo '</td></tr>';
1303 }
1304
1305 function authenticate_admin ($username, $password)
1306 {
1307 global $dbxlink;
1308 $prepared = $dbxlink->prepare ('SELECT COUNT(*) FROM UserAccount WHERE user_id=1 AND user_name=? AND user_password_hash=?');
1309 if (!$prepared->execute (array ($username, sha1 ($password))))
1310 die ('SQL query failed in ' . __FUNCTION__);
1311 $rows = $prepared->fetchAll (PDO::FETCH_NUM);
1312 return $rows[0][0] == 1;
1313 }
1314
1315 // Database version detector. Should behave corretly on any
1316 // working dataset a user might have.
1317 function getDatabaseVersion ()
1318 {
1319 global $dbxlink;
1320 $prepared = $dbxlink->prepare ('SELECT varvalue FROM Config WHERE varname = "DB_VERSION" and vartype = "string"');
1321 if (! $prepared->execute())
1322 {
1323 $errorInfo = $dbxlink->errorInfo();
1324 die (__FUNCTION__ . ': SQL query failed with error ' . $errorInfo[2]);
1325 }
1326 $rows = $prepared->fetchAll (PDO::FETCH_NUM);
1327 unset ($result);
1328 if (count ($rows) != 1 || !strlen ($rows[0][0]))
1329 die (__FUNCTION__ . ': Cannot guess database version. Config table is present, but DB_VERSION is missing or invalid. Giving up.');
1330 $ret = $rows[0][0];
1331 return $ret;
1332 }
1333
1334 function showError ($info = '', $location = 'N/A')
1335 {
1336 if (preg_match ('/\.php$/', $location))
1337 $location = basename ($location);
1338 elseif ($location != 'N/A')
1339 $location = $location . '()';
1340 echo "<div class=msg_error>An error has occured in [${location}]. ";
1341 if (!strlen ($info))
1342 echo 'No additional information is available.';
1343 else
1344 echo "Additional information:<br><p>\n<pre>\n${info}\n</pre></p>";
1345 echo "Go back or try starting from <a href='index.php'>index page</a>.<br></div>\n";
1346 }
1347
1348 function renderUpgraderHTML()
1349 {
1350 global $found_secret_file;
1351 if (! $found_secret_file)
1352 die ('<center>There is no working RackTables instance here, <a href="?module=installer">install</a>?</center>');
1353
1354 try
1355 {
1356 connectDB();
1357 }
1358 catch (RackTablesError $e)
1359 {
1360 die ("Database connection failed:\n\n" . $e->getMessage());
1361 }
1362
1363 if
1364 (
1365 !isset ($_SERVER['PHP_AUTH_USER']) or
1366 !strlen ($_SERVER['PHP_AUTH_USER']) or
1367 !isset ($_SERVER['PHP_AUTH_PW']) or
1368 !strlen ($_SERVER['PHP_AUTH_PW']) or
1369 !authenticate_admin ($_SERVER['PHP_AUTH_USER'], $_SERVER['PHP_AUTH_PW'])
1370 )
1371 {
1372 header ('WWW-Authenticate: Basic realm="RackTables upgrade"');
1373 header ('HTTP/1.0 401 Unauthorized');
1374 showError ('You must be authenticated as an administrator to complete the upgrade.', __FUNCTION__);
1375 die;
1376 }
1377
1378 ?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
1379 <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
1380 <head><title>RackTables upgrade script</title>
1381 <style type="text/css">
1382 .tdleft {
1383 text-align: left;
1384 }
1385
1386 .trok {
1387 background-color: #80FF80;
1388 }
1389
1390 .trwarning {
1391 background-color: #FFFF80;
1392 }
1393
1394 .trerror {
1395 background-color: #FF8080;
1396 }
1397 </style>
1398 </head>
1399 <body>
1400 <h1>Platform check status</h1>
1401 <?php
1402
1403 if (!platform_is_ok())
1404 die ('</body></html>');
1405
1406 echo '<h1>Upgrade status</h1>';
1407 $dbver = getDatabaseVersion();
1408 echo '<table border=1 cellpadding=5>';
1409 echo "<tr><th>Current status</th><td>Data version: ${dbver}<br>Code version: " . CODE_VERSION . "</td></tr>\n";
1410
1411 $path = getDBUpgradePath ($dbver, CODE_VERSION);
1412 if ($path === NULL)
1413 {
1414 echo "<tr><th>Upgrade path</th><td><font color=red>not found</font></td></tr>\n";
1415 echo "<tr><th>Summary</th><td>Check README for more information. RackTables releases prior to 0.16.4 ";
1416 echo "must be upgraded to 0.16.4 first.</td></tr>\n";
1417 }
1418 else
1419 {
1420 if (!count ($path))
1421 echo "<tr><th>Summary</th><td>Come back later.</td></tr>\n";
1422 else
1423 {
1424 echo "<tr><th>Upgrade path</th><td>${dbver} &rarr; " . implode (' &rarr; ', $path) . "</td></tr>\n";
1425 foreach ($path as $batchid)
1426 {
1427 executeUpgradeBatch ($batchid);
1428 if (isset ($relnotes[$batchid]))
1429 echo "<tr><th>Release notes for ${batchid}</th><td><pre>" . $relnotes[$batchid] . "</pre></td></tr>\n";
1430 }
1431 echo "<tr><th>Summary</th><td>Upgrade complete, it is Ok to <a href='index.php'>enter</a> the system.</td></tr>\n";
1432 }
1433 }
1434 echo '</table>';
1435 echo '</body></html>';
1436 }
1437
1438 ?>