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