894d3bf846035f1968f640605e60c0cec015bcac
[racktables] / wwwroot / inc / upgrade.php
1 <?php
2
3 # This file is a part of RackTables, a datacenter and server room management
4 # framework. See accompanying file "COPYING" for the full copyright and
5 # licensing information.
6
7 $relnotes = array
8 (
9 '0.20.0' => <<<'ENDOFTEXT'
10 WARNING: This release have too many internal changes, some of them were waiting more than a year
11 to be released. So this release is considered "BETA" and is recommended only to curiuos users,
12 who agree to sacrifice the stability to the progress.
13
14 Racks and Rows are now stored in the database as Objects. The RackObject table
15 was renamed to Object. SQL views were created to ease the migration of custom
16 reports and scripts.
17
18 New plugins engine instead of local.php file. To make your own code stored in local.php work,
19 you must move the local.php file into the plugins/ directory. The name of this file does not
20 matter any more. You also can store multiple files in that dir, separate your plugins by features,
21 share them and try the plugins from other people just placing them into plugins/ dir, no more merging.
22 $path_to_local_php variable has no special meaning any more.
23 $racktables_confdir variable is now used only to search for secret.php file.
24 $racktables_plugins_dir is a new overridable special variable pointing to plugins/ directory.
25
26 Beginning with this version it is possible to delete IP prefixes, VLANs, Virtual services
27 and RS pools from within theirs properties tab. So please inspect your permissions rules
28 to assure there are no undesired allows for deletion of these objects. To ensure this, you could
29 try this code in the beginning of permissions script:
30
31 allow {userid_1} and {$op_del}
32 deny {$op_del} and ({$tab_edit} or {$tab_properties})
33
34 Hardware gateways engine was rewritten in this version of RackTables. This means that
35 the file gateways/deviceconfig/switch.secrets.php is not used any more. To get information
36 about configuring connection properties and credentials in a new way please visit
37 http://wiki.racktables.org/index.php/Gateways
38
39 This also means that recently added features based on old API (D-Link switches and Linux
40 gateway support contributed by Ilya Evseev) are not working any more and waiting to be
41 forward-ported to new gateways API. Sorry for that.
42
43 Two new config variables appeared in this version:
44 - SEARCH_DOMAINS. Comma-separated list of DNS domains that are considered "base" for your
45 network. If RackTables search engine finds multiple objects based on your search input, but
46 there is only one that has FQDN consisting of your input and one of these search domains, you will
47 be redirected to this object and other results will be discarded. Such behavior was unconditional
48 since 0.19.3, which caused many objections from users. So welcome this config var.
49 - QUICK_LINK_PAGES. Comma-separated list of RackTables pages to display links to them on top.
50 Each user could have his own list.
51
52 Also some of config variables have changed their default values in this version.
53 This means that upgrade script will change their values if you have them in previous default state.
54 This could be inconvenient, but it is the most effective way to encourage users to use new features.
55 If this behavior is not what you want, simply revert these variables' values:
56 - SHOW_LAST_TAB no => yes
57 - IPV4_TREE_SHOW_USAGE yes =>no (networks' usage is still available by click)
58 - IPV4LB_LISTSRC {$typeid_4} => false
59 - FILTER_DEFAULT_ANDOR or => and (this implicitly enables the feature of dynamic tree shrinking)
60 - FILTER_SUGGEST_EXTRA no => yes (yes, we have extra logical filters!)
61 - IPV4_TREE_RTR_AS_CELL yes => no (display routers as simple text, not cell)
62
63 Also please note that variable IPV4_TREE_RTR_AS_CELL now has third special value
64 besides 'yes' and 'no': 'none'. Use 'none' value if you are experiencing low performance
65 on IP tree page. It will completely disable IP ranges scan for used/spare IPs and the
66 speed of IP tree will increase radically. The price is you will not see the routers in
67 IP tree at all.
68
69 ENDOFTEXT
70 ,
71
72 '0.20.1' => <<<'ENDOFTEXT'
73 The 0.20.0 release includes a bug that breaks IP networks' capacity displaying on 32-bit architecture machines. To fix this, this release makes use of PHP's BC Math module. It is a new reqiurement. Most PHP distributions have this module already enabled, but if yours does not - you need yo recompile PHP.
74
75 Security context of 'ipaddress' page now includes tags from the network containing an IP address. This means that you should audit your permission rules to check there is no unintended allows of changing IPs based on network's tagset. Example:
76 allow {client network} and {New York}
77 This rule now not only allows any operation on NY client networks, but also any operation with IP addresses included in those networks. To fix this, you should change the rule this way:
78 allow {client network} and {New York} and not {$page_ipaddress}
79
80 ENDOFTEXT
81 ,
82
83 '0.20.4' => <<<'ENDOFTEXT'
84 Please note that some dictionary items of Cisco Catalyst 2960 series switches
85 were renamed to meet official Cisco classification:
86 2960-48TT => 2960-48TT-L
87 2960-24TC => 2960-24TC-L
88 2960-24TT => 2960-24TT-L
89 2960-8TC => 2960-8TC-L
90 2960G-48TC => 2960G-48TC-L
91 2960G-24TC => 2960G-24TC-L
92 2960G-8TC => 2960G-8TC-L
93 C2960-24 => C2960-24-S
94 C2960G-24PC => C2960-24PC-L
95
96 The DATETIME_FORMAT configuration option used in setting date and time output
97 format now uses a different [1] syntax. During upgrade the option is reset to
98 the default value, which is now %Y-%m-%d (YYYY-MM-DD) per ISO 8601.
99
100 This release intoduces two new configuration options:
101 REVERSED_RACKS_LISTSRC and NEAREST_RACKS_CHECKBOX.
102
103 [1] http://php.net/manual/en/function.strftime.php
104 ENDOFTEXT
105 ,
106
107 '0.20.5' => <<<'ENDOFTEXT'
108 This release introduces the VS groups feature. VS groups is a new way to store
109 and display virtual services configuration. There is a new "ipvs" (VS group)
110 realm. All previously existing VS configuration remains functional and user
111 is free to convert it to the new format, which displays it in a more natural way
112 and allows to generate virtual_server_group keepalived configs. To convert a
113 virtual service to the new format, it is necessary to manually create a VS group
114 object and assign IP addresses to it. The VS group will display a "Migrate" tab
115 to convert the old-style VS objects, which can be removed after a successful
116 conversion.
117
118 The old-style VS configuration becomes DEPRECATED. Its support will be removed
119 in a future major release. So it is strongly recommended to convert it to the
120 new format.
121 ENDOFTEXT
122 ,
123
124 '0.20.6' => <<<'ENDOFTEXT'
125 New MGMT_PROTOS configuration option replaces the TELNET_OBJS_LISTSRC,
126 SSH_OBJS_LISTSRC and RDP_OBJS_LISTSRC options (converting existing settings as
127 necessary). MGMT_PROTOS allows to specify any management protocol for a
128 particular device list using a RackCode filter. The default value
129 ("ssh: {$typeid_4}, telnet: {$typeid_8}") produces "ssh://server.fqdn" for
130 servers and "telnet://switch.fqdn" for network switches.
131 ENDOFTEXT
132 ,
133
134 '0.20.7' => <<<'ENDOFTEXT'
135 From now on the minimum (oldest) release of PHP that can run RackTables is
136 5.2.10. In particular, to continue running RackTables on CentOS 5 it is
137 necessary to replace its php* RPM packages with respective php53* packages
138 before the upgrade (except the JSON package, which PHP 5.3 provides internally).
139
140 Database triggers are used for some data consistency measures. The database
141 user account must have the 'TRIGGER' privilege, which was introduced in
142 MySQL 5.1.7.
143
144 The IPV4OBJ_LISTSRC configuration option is reset to an expression that enables
145 the IP addressing feature for all object types except those listed.
146
147 Tags could now be assigned on the Edit/Properties tab using a text input with
148 auto-completion. Type a star '*' to view full tag tree in auto-complete menu.
149 It is worth to add the following line to the permissions script if the
150 old-fashioned 'Tags' tab is not needed any more:
151 deny {$tab_tags} # this hides 'Tags' tab
152
153 This release converts collation of all DB fields to the utf8_unicode_ci. This
154 procedure may take some time, and could fail if there are rows that differ only
155 by letter case. If this happen, you'll see the failed SQL query in upgrade report
156 with the "Duplicate entry" error message. Feel free to continue using your
157 installation. If desired so, you could eliminate the case-duplicating rows
158 and re-apply the failed query.
159 ENDOFTEXT
160 ,
161 '0.20.11' => <<<'ENDOFTEXT'
162 New IPV4_TREE_SHOW_UNALLOCATED configuration option introduced to disable
163 dsplaying unallocated networks in IPv4 space tree. Setting it also disables
164 the "knight" feature.
165 ENDOFTEXT
166 ,
167 '0.21.0' => <<<'ENDOFTEXT'
168 From now on the minimum (oldest) release of PHP that can run RackTables is
169 5.5.0.
170
171 This release introduces a new plugin architecture. If you experience issues
172 after the upgrade, try disabling plugins.
173 Refer to <a href="http://wiki.racktables.org/index.php/Plugins">the wiki</a> for more information.
174 ENDOFTEXT
175 ,
176 '0.21.2' => <<<'ENDOFTEXT'
177 "Shared router" allocation type introduced, useful for documenting VRRP-protected addresses.
178
179 This version drops support for the $localreports global variable, which is
180 trivial to replace in a local plugin if necessary.
181
182 The "addJS()" function is now deprecated in favour of "addJSText()", "addJSInternal()",
183 and "addJSExternal()" functions. The "addJS()" function will likely be removed in 0.22.0.
184
185 The "addCSS()" function is now deprecated in favour of "addCSSText()", "addCSSInternal()",
186 and "addCSSExternal()" functions. The "addCSS()" function will likely be removed in 0.22.0.
187
188 For more information on the "addJS()" and "addCSS()" changes see the README.md
189 ENDOFTEXT
190 ,
191
192 '0.22.0' => <<<'ENDOFTEXT'
193 As of this release the minimum supported PHP version is 7.0.
194 ENDOFTEXT
195 ,
196 );
197
198 // This function currently implements a convention that to upgrade any release R1
199 // to a release R2 (such that R2 > R1) it takes to apply the sequence of batches
200 // (R1, R2] to database R1.
201 // Returning an empty array means that no upgrade is necessary.
202 // Returning NULL indicates an error.
203 function getDBUpgradePath ($v1, $v2)
204 {
205 $versionhistory = array
206 (
207 '0.20.0',
208 '0.20.1',
209 '0.20.2',
210 '0.20.3',
211 '0.20.4',
212 '0.20.5',
213 '0.20.6',
214 '0.20.7',
215 '0.20.8',
216 '0.20.9',
217 '0.20.10',
218 '0.20.11',
219 '0.20.12',
220 '0.20.13',
221 '0.20.14',
222 '0.21.0',
223 '0.21.1',
224 '0.21.2',
225 '0.21.3',
226 '0.21.4',
227 '0.21.5',
228 '0.22.0',
229 );
230 if (! in_array ($v1, $versionhistory) || ! in_array ($v2, $versionhistory))
231 return NULL;
232 $skip = TRUE;
233 $path = NULL;
234 // foreach() below cannot handle this specific case
235 if ($v1 == $v2)
236 return array();
237 // Now collect all versions > $v1 and <= $v2
238 foreach ($versionhistory as $v)
239 {
240 if ($skip && $v == $v1)
241 {
242 $skip = FALSE;
243 $path = array();
244 continue;
245 }
246 if ($skip)
247 continue;
248 $path[] = $v;
249 if ($v == $v2)
250 break;
251 }
252 return $path;
253 }
254
255 // Upgrade batches are named exactly as the release where they first appear.
256 // That is simple, but seems sufficient for beginning.
257 function getUpgradeBatch ($batchid)
258 {
259 $query = array();
260 global $dbver, $dbxlink;
261 switch ($batchid)
262 {
263 case '0.20.0':
264 $query[] = "
265 CREATE TABLE `PortLog` (
266 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
267 `port_id` int(10) unsigned NOT NULL,
268 `date` datetime NOT NULL,
269 `user` varchar(64) NOT NULL,
270 `message` text NOT NULL,
271 PRIMARY KEY (`id`),
272 KEY `port_id-date` (`port_id`,`date`),
273 CONSTRAINT `PortLog_ibfk_1` FOREIGN KEY (`port_id`) REFERENCES `Port` (`id`) ON DELETE CASCADE
274 ) ENGINE=InnoDB;
275 ";
276 $query[] = "
277 CREATE TABLE `IPv4Log` (
278 `id` int(10) NOT NULL AUTO_INCREMENT,
279 `ip` int(10) unsigned NOT NULL,
280 `date` datetime NOT NULL,
281 `user` varchar(64) NOT NULL,
282 `message` text NOT NULL,
283 PRIMARY KEY (`id`),
284 KEY `ip-date` (`ip`,`date`)
285 ) ENGINE=InnoDB;
286 ";
287 $query[] = "
288 CREATE TABLE `IPv6Log` (
289 `id` int(10) NOT NULL AUTO_INCREMENT,
290 `ip` binary(16) NOT NULL,
291 `date` datetime NOT NULL,
292 `user` varchar(64) NOT NULL,
293 `message` text NOT NULL,
294 PRIMARY KEY (`id`),
295 KEY `ip-date` (`ip`,`date`)
296 ) ENGINE=InnoDB;
297 ";
298 $query[] = "ALTER TABLE `FileLink` MODIFY COLUMN `entity_type` ENUM('ipv4net','ipv4rspool','ipv4vs','ipv6net','location','object','rack','user') NOT NULL DEFAULT 'object'";
299 $query[] = "ALTER TABLE `TagStorage` MODIFY COLUMN `entity_realm` ENUM('file','ipv4net','ipv4rspool','ipv4vs','ipv6net','location','object','rack','user','vst') NOT NULL default 'object'";
300 $query[] = "ALTER TABLE `TagStorage` ADD COLUMN `user` char(64) DEFAULT NULL, ADD COLUMN `date` datetime DEFAULT NULL";
301
302 // Rename object tables and keys, 'name' no longer needs to be unique
303 $dbxlink->query ('ALTER TABLE `RackObject` RENAME TO `Object`');
304 $dbxlink->query ('ALTER TABLE `RackObjectHistory` RENAME TO `ObjectHistory`');
305 $dbxlink->query ('ALTER TABLE `Object` DROP KEY `name`');
306 $query[] = 'ALTER TABLE `Object` DROP KEY `RackObject_asset_no`';
307 $query[] = 'ALTER TABLE `Object` ADD UNIQUE KEY `asset_no` (`asset_no`)';
308 $query[] = 'ALTER TABLE `Object` ADD KEY `type_id` (`objtype_id`,`id`)';
309 $query[] = 'ALTER TABLE `ObjectHistory` DROP FOREIGN KEY `RackObjectHistory-FK-object_id`';
310 $query[] = 'ALTER TABLE `ObjectHistory` ADD CONSTRAINT `ObjectHistory-FK-object_id` FOREIGN KEY (`id`) REFERENCES `Object` (`id`) ON DELETE CASCADE';
311 $query[] = 'ALTER TABLE `RackSpace` DROP FOREIGN KEY `RackSpace-FK-rack_id`';
312
313 // Rack height is now an attribute
314 $query[] = "INSERT INTO `Attribute` (`id`,`type`,`name`) VALUES (27,'uint','Height, units')";
315 $query[] = 'INSERT INTO `AttributeMap` (`objtype_id`,`attr_id`,`chapter_id`) VALUES (1560,27,NULL)';
316
317 // Racks are now sorted using an attribute
318 $query[] = "INSERT INTO `Attribute` (`id`,`type`,`name`) VALUES (29,'uint','Sort order')";
319 $query[] = 'INSERT INTO `AttributeMap` (`objtype_id`,`attr_id`,`chapter_id`) VALUES (1560,29,NULL)';
320
321 // Relate 'contact person' with locations
322 $query[] = 'INSERT INTO `AttributeMap` (`objtype_id`,`attr_id`,`chapter_id`) VALUES (1562,14,NULL)';
323
324 // Allow relationships between racks/rows/locations
325 $query[] = "ALTER TABLE `EntityLink` MODIFY COLUMN `parent_entity_type` ENUM('ipv4net','ipv4rspool','ipv4vs','ipv6net','location','object','rack','row','user') NOT NULL";
326 $query[] = "ALTER TABLE `EntityLink` MODIFY COLUMN `child_entity_type` ENUM('file','location','object','rack','row') NOT NULL";
327
328 // Turn rows into objects
329 $result = $dbxlink->query ('SELECT id, name FROM RackRow');
330 $rows = $result->fetchAll (PDO::FETCH_ASSOC);
331 unset ($result);
332 foreach ($rows as $row)
333 {
334 $prepared = $dbxlink->prepare ('INSERT INTO `Object` (`name`,`objtype_id`) VALUES (?,?)');
335 $prepared->execute (array($row['name'], 1561));
336 $row_id = $dbxlink->lastInsertID();
337 // Turn all racks in this row into objects
338 $result = $dbxlink->query ("SELECT id, name, height, comment FROM Rack WHERE row_id=${row['id']} ORDER BY name");
339 $racks = $result->fetchAll (PDO::FETCH_ASSOC);
340 unset ($result);
341 $sort_order = 1;
342 foreach ($racks as $rack)
343 {
344 // Add the rack as an object, set the height and sort order as attributes, link the rack to the row,
345 // update rackspace, tags and files to reflect new rack_id, move history
346 $prepared = $dbxlink->prepare ('INSERT INTO `Object` (`name`,`objtype_id`,`comment`) VALUES (?,?,?)');
347 $prepared->execute (array($rack['name'], 1560, $rack['comment']));
348 $rack_id = $dbxlink->lastInsertID();
349 $query[] = "INSERT INTO `AttributeValue` (`object_id`,`object_tid`,`attr_id`,`uint_value`) VALUES (${rack_id},1560,27,${rack['height']})";
350 $query[] = "INSERT INTO `AttributeValue` (`object_id`,`object_tid`,`attr_id`,`uint_value`) VALUES (${rack_id},1560,29,${sort_order})";
351 $query[] = "INSERT INTO `EntityLink` (`parent_entity_type`,`parent_entity_id`,`child_entity_type`,`child_entity_id`) VALUES ('row',${row_id},'rack',${rack_id})";
352 $query[] = "UPDATE `RackSpace` SET `rack_id`=${rack_id} WHERE `rack_id`=${rack['id']}";
353 $query[] = "UPDATE `Atom` SET `rack_id`=${rack_id} WHERE `rack_id`=${rack['id']}";
354 $query[] = "UPDATE `TagStorage` SET `entity_id`=${rack_id} WHERE `entity_realm`='rack' AND `entity_id`=${rack['id']}";
355 $query[] = "UPDATE `FileLink` SET `entity_id`=${rack_id} WHERE `entity_type`='rack' AND `entity_id`=${rack['id']}";
356 $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']}";
357 $sort_order++;
358 }
359 }
360 $query[] = 'ALTER TABLE `RackSpace` ADD CONSTRAINT `RackSpace-FK-rack_id` FOREIGN KEY (`rack_id`) REFERENCES `Object` (`id`)';
361 $query[] = 'DROP TABLE `Rack`';
362 $query[] = 'DROP TABLE `RackRow`';
363 $query[] = 'DROP TABLE `RackHistory`';
364 $query[] = "
365 CREATE TABLE `RackThumbnail` (
366 `rack_id` int(10) unsigned NOT NULL,
367 `thumb_data` blob,
368 UNIQUE KEY `rack_id` (`rack_id`),
369 CONSTRAINT `RackThumbnail-FK-rack_id` FOREIGN KEY (`rack_id`) REFERENCES `Object` (`id`) ON DELETE CASCADE
370 ) ENGINE=InnoDB
371 ";
372 $query[] = "
373 CREATE VIEW `Location` AS SELECT O.id, O.name, O.has_problems, O.comment, P.id AS parent_id, P.name AS parent_name
374 FROM `Object` O
375 LEFT JOIN (
376 `Object` P INNER JOIN `EntityLink` EL
377 ON EL.parent_entity_id = P.id AND P.objtype_id = 1562 AND EL.parent_entity_type = 'location' AND EL.child_entity_type = 'location'
378 ) ON EL.child_entity_id = O.id
379 WHERE O.objtype_id = 1562
380 ";
381 $query[] = "
382 CREATE VIEW `Row` AS SELECT O.id, O.name, L.id AS location_id, L.name AS location_name
383 FROM `Object` O
384 LEFT JOIN `EntityLink` EL ON O.id = EL.child_entity_id AND EL.parent_entity_type = 'location' AND EL.child_entity_type = 'row'
385 LEFT JOIN `Object` L ON EL.parent_entity_id = L.id AND L.objtype_id = 1562
386 WHERE O.objtype_id = 1561
387 ";
388 $query[] = "
389 CREATE VIEW `Rack` AS SELECT O.id, O.name AS name, O.asset_no, O.has_problems, O.comment,
390 AV_H.uint_value AS height,
391 AV_S.uint_value AS sort_order,
392 RT.thumb_data,
393 R.id AS row_id,
394 R.name AS row_name
395 FROM `Object` O
396 LEFT JOIN `AttributeValue` AV_H ON O.id = AV_H.object_id AND AV_H.attr_id = 27
397 LEFT JOIN `AttributeValue` AV_S ON O.id = AV_S.object_id AND AV_S.attr_id = 29
398 LEFT JOIN `RackThumbnail` RT ON O.id = RT.rack_id
399 LEFT JOIN `EntityLink` EL ON O.id = EL.child_entity_id AND EL.parent_entity_type = 'row' AND EL.child_entity_type = 'rack'
400 INNER JOIN `Object` R ON R.id = EL.parent_entity_id
401 WHERE O.objtype_id = 1560
402 ";
403 $query[] = "
404 CREATE VIEW `RackObject` AS SELECT id, name, label, objtype_id, asset_no, has_problems, comment FROM `Object`
405 WHERE `objtype_id` NOT IN (1560, 1561, 1562)
406 ";
407 $query[] = "UPDATE `Chapter` SET `name` = 'ObjectType' WHERE `id` = 1";
408 $query[] = "DELETE FROM RackSpace WHERE object_id IS NULL AND state = 'T'";
409
410 $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')";
411 $query[] = "UPDATE `Config` SET is_userdefined='yes' WHERE varname='PROXIMITY_RANGE'";
412 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('QUICK_LINK_PAGES','depot,ipv4space,rackspace','string','yes','no','yes','List of pages to dislay in quick links')";
413 $query[] = "ALTER TABLE `IPv4LB` MODIFY `prio` varchar(255) DEFAULT NULL";
414
415 $query[] = "ALTER TABLE `IPv4Address` ADD COLUMN `comment` char(255) NOT NULL default '' AFTER `name`";
416 $query[] = "ALTER TABLE `IPv6Address` ADD COLUMN `comment` char(255) NOT NULL default '' AFTER `name`";
417
418 // change IP address format of IPv4VS and IPv4RS tables
419 convertSLBTablesToBinIPs();
420
421 // do not allow NULL allocation type
422 $query[] = "ALTER TABLE `IPv4Allocation` MODIFY `type` enum('regular','shared','virtual','router') NOT NULL DEFAULT 'regular'";
423 $query[] = "ALTER TABLE `IPv6Allocation` MODIFY `type` enum('regular','shared','virtual','router') NOT NULL DEFAULT 'regular'";
424
425 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('SEARCH_DOMAINS','','string','yes','no','yes','DNS domain list (comma-separated) to search in FQDN attributes')";
426
427 // update some config variables that changed their defaults in this version
428 replaceConfigVarValue ('SHOW_LAST_TAB', 'yes');
429 replaceConfigVarValue ('IPV4_TREE_SHOW_USAGE','no');
430 replaceConfigVarValue ('IPV4LB_LISTSRC', 'false', '{$typeid_4}');
431 replaceConfigVarValue ('FILTER_DEFAULT_ANDOR', 'and');
432 replaceConfigVarValue ('FILTER_SUGGEST_EXTRA', 'yes');
433 replaceConfigVarValue ('IPV4_TREE_RTR_AS_CELL', 'no');
434 replaceConfigVarValue ('SSH_OBJS_LISTSRC', 'false', 'none');
435 replaceConfigVarValue ('TELNET_OBJS_LISTSRC', 'false', 'none');
436
437 $query[] = "UPDATE Config SET varvalue = '0.20.0' WHERE varname = 'DB_VERSION'";
438 break;
439 case '0.20.1':
440 // some HW types were moved from the 'Network switch' chapter to the 'Network chassis' chapter
441 // change the type of affected objects to 'Network chassis'
442 $query[] = "UPDATE `Object` SET objtype_id = 1503 WHERE id IN (SELECT object_id FROM `AttributeValue` WHERE attr_id = 2 and uint_value IN (888,889,890,891))";
443
444 // new 'management interface' object type
445 $query[] = "INSERT INTO `Chapter` (`id`,`sticky`,`name`) VALUES (38,'no','management interface type')";
446 $query[] = "INSERT INTO `Attribute` (`id`,`type`,`name`) VALUES (30,'dict','Mgmt type')";
447 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`,`attr_id`,`chapter_id`) VALUES (1787,3,NULL),(1787,14,NULL),(1787,30,38)";
448 $query[] = "UPDATE `Config` SET varvalue = CONCAT(varvalue, ' or {\$typeid_1787}') WHERE varname = 'IPV4OBJ_LISTSRC'";
449
450 $query[] = "INSERT INTO Config VALUES ('8021Q_EXTSYNC_LISTSRC','false','string','yes','no','no','List source: objects with extended 802.1Q sync')";
451
452 // constraints to prevent orphan records
453 $query[] = "DELETE A FROM `Atom` A LEFT JOIN `Molecule` M ON A.molecule_id = M.id LEFT JOIN `Object` O ON A.rack_id = O.id WHERE M.id IS NULL OR O.id IS NULL";
454 $query[] = "DELETE FROM `MountOperation` WHERE old_molecule_id NOT IN (SELECT id FROM `Molecule`) OR new_molecule_id NOT IN (SELECT id FROM `Molecule`)";
455 $query[] = "ALTER TABLE `Atom` ADD CONSTRAINT `Atom-FK-molecule_id` FOREIGN KEY (`molecule_id`) REFERENCES `Molecule` (`id`) ON DELETE CASCADE";
456 $query[] = "ALTER TABLE `Atom` ADD CONSTRAINT `Atom-FK-rack_id` FOREIGN KEY (`rack_id`) REFERENCES `Object` (`id`) ON DELETE CASCADE";
457 $query[] = "ALTER TABLE `MountOperation` ADD CONSTRAINT `MountOperation-FK-old_molecule_id` FOREIGN KEY (`old_molecule_id`) REFERENCES `Molecule` (`id`) ON DELETE CASCADE";
458 $query[] = "ALTER TABLE `MountOperation` ADD CONSTRAINT `MountOperation-FK-new_molecule_id` FOREIGN KEY (`new_molecule_id`) REFERENCES `Molecule` (`id`) ON DELETE CASCADE";
459 # multiple Cacti servers
460 $query[] = "
461 CREATE TABLE `CactiServer` (
462 `id` int(10) unsigned NOT NULL auto_increment,
463 `base_url` char(255) DEFAULT NULL,
464 `username` char(64) DEFAULT NULL,
465 `password` char(64) DEFAULT NULL,
466 PRIMARY KEY (`id`)
467 ) ENGINE=InnoDB
468 ";
469 $query[] = "ALTER TABLE CactiGraph ADD COLUMN server_id int(10) unsigned NOT NULL AFTER object_id";
470
471 $result = $dbxlink->query ('SELECT COUNT(*) AS cnt FROM CactiGraph');
472 $row = $result->fetch (PDO::FETCH_ASSOC);
473 unset ($result);
474
475 $result = $dbxlink->query ("SELECT varvalue FROM Config WHERE varname = 'CACTI_URL'");
476 $cacti_url_row = $result->fetch (PDO::FETCH_ASSOC);
477 unset ($result);
478
479 if ($row['cnt'] != 0 || is_array ($cacti_url_row) && $cacti_url_row['varvalue'] != '')
480 {
481 $query[] = "INSERT INTO CactiServer (id) VALUES (1)";
482 $query[] = "UPDATE CactiServer SET base_url = (SELECT varvalue FROM Config WHERE varname = 'CACTI_URL') WHERE id = 1";
483 $query[] = "UPDATE CactiServer SET username = (SELECT varvalue FROM Config WHERE varname = 'CACTI_USERNAME') WHERE id = 1";
484 $query[] = "UPDATE CactiServer SET password = (SELECT varvalue FROM Config WHERE varname = 'CACTI_USERPASS') WHERE id = 1";
485 $query[] = "UPDATE CactiGraph SET server_id = 1";
486 }
487 $query[] = "ALTER TABLE CactiGraph DROP PRIMARY KEY";
488 $query[] = "ALTER TABLE CactiGraph ADD PRIMARY KEY (server_id, graph_id)";
489 $query[] = "ALTER TABLE CactiGraph ADD KEY (graph_id)";
490 $query[] = "ALTER TABLE CactiGraph ADD CONSTRAINT `CactiGraph-FK-server_id` FOREIGN KEY (server_id) REFERENCES CactiServer (id)";
491 $query[] = "DELETE FROM Config WHERE varname IN('CACTI_URL', 'CACTI_USERNAME', 'CACTI_USERPASS')";
492 $query[] = "UPDATE Config SET varvalue = '0.20.1' WHERE varname = 'DB_VERSION'";
493 break;
494 case '0.20.2':
495 $query[] = "ALTER TABLE TagStorage ADD COLUMN tag_is_assignable ENUM('yes', 'no') NOT NULL default 'yes' AFTER tag_id";
496 $query[] = "ALTER TABLE TagStorage ADD KEY `tag_id-tag_is_assignable` (tag_id, tag_is_assignable)";
497 $query[] = "ALTER TABLE TagTree ADD COLUMN is_assignable ENUM('yes', 'no') NOT NULL default 'yes' AFTER parent_id";
498 $query[] = "ALTER TABLE TagTree ADD KEY `id-is_assignable` (id, is_assignable)";
499 $query[] = "ALTER TABLE TagStorage DROP FOREIGN KEY `TagStorage-FK-tag_id`";
500 $query[] = "ALTER TABLE TagStorage ADD CONSTRAINT `TagStorage-FK-TagTree` FOREIGN KEY (tag_id, tag_is_assignable) REFERENCES TagTree (id, is_assignable)";
501 $query[] = "UPDATE UserAccount SET user_realname = NULL WHERE user_realname = ''";
502 $query[] = "UPDATE Object SET comment = NULL WHERE comment = ''";
503 $query[] = "
504 CREATE TABLE `MuninServer` (
505 `id` int(10) unsigned NOT NULL auto_increment,
506 `base_url` char(255) DEFAULT NULL,
507 PRIMARY KEY (`id`)
508 ) ENGINE=InnoDB
509 ";
510 $query[] = "
511 CREATE TABLE `MuninGraph` (
512 `object_id` int(10) unsigned NOT NULL,
513 `server_id` int(10) unsigned NOT NULL,
514 `graph` char(255) NOT NULL,
515 `caption` char(255) DEFAULT NULL,
516 PRIMARY KEY (`object_id`,`server_id`,`graph`),
517 KEY `server_id` (`server_id`),
518 KEY `graph` (`graph`),
519 CONSTRAINT `MuninGraph-FK-server_id` FOREIGN KEY (`server_id`) REFERENCES `MuninServer` (`id`),
520 CONSTRAINT `MuninGraph-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `Object` (`id`) ON DELETE CASCADE
521 ) ENGINE=InnoDB
522 ";
523 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('MUNIN_LISTSRC','false','string','yes','no','no','List of object with Munin graphs')";
524 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('8021Q_MULTILINK_LISTSRC','false','string','yes','no','no','List source: IPv4/IPv6 networks allowing multiple VLANs from same domain')";
525 $query[] = "ALTER TABLE VLANIPv4 ADD UNIQUE `network-domain-vlan` (ipv4net_id, domain_id, vlan_id)";
526 $query[] = "ALTER TABLE VLANIPv4 DROP KEY `network-domain`";
527 $query[] = "ALTER TABLE VLANIPv6 ADD UNIQUE `network-domain-vlan` (ipv6net_id, domain_id, vlan_id)";
528 $query[] = "ALTER TABLE VLANIPv6 DROP KEY `network-domain`";
529 $query[] = "UPDATE Config SET varvalue = '0.20.2' WHERE varname = 'DB_VERSION'";
530 break;
531 case '0.20.3':
532 $query[] = "UPDATE Config SET varvalue = '0.20.3' WHERE varname = 'DB_VERSION'";
533 break;
534 case '0.20.4':
535 $query[] = "ALTER TABLE `FileLink` MODIFY COLUMN `entity_type` ENUM('ipv4net','ipv4rspool','ipv4vs','ipv6net','location','object','rack','row','user') NOT NULL DEFAULT 'object'";
536 $query[] = "ALTER TABLE `RackSpace` MODIFY COLUMN `state` ENUM('A','U','T') NOT NULL default 'A'";
537 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('REVERSED_RACKS_LISTSRC', 'false', 'string', 'yes', 'no', 'no', 'List of racks with reversed (top to bottom) units order')";
538 $query[] = "UPDATE `Config` SET varvalue = CONCAT(varvalue, ' or {\$typeid_965}') WHERE varname = 'IPV4OBJ_LISTSRC'";
539 $query[] = "UPDATE AttributeValue INNER JOIN AttributeMap USING (attr_id) SET AttributeValue.uint_value = 1572 WHERE chapter_id = 12 AND uint_value = 162";
540 $query[] = "UPDATE AttributeValue INNER JOIN AttributeMap USING (attr_id) SET AttributeValue.uint_value = 1710 WHERE chapter_id = 12 AND uint_value = 163";
541 $query[] = "UPDATE Config SET varvalue = '%Y-%m-%d', description='PHP strftime() format to use for date output' WHERE varname = 'DATETIME_FORMAT'";
542 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('NEAREST_RACKS_CHECKBOX', 'yes', 'string', 'yes', 'no', 'yes', 'Enable nearest racks in port list filter by default')";
543 $query[] = "UPDATE Config SET varvalue = '0.20.4' WHERE varname = 'DB_VERSION'";
544 break;
545 case '0.20.5':
546 $query[] = "
547 CREATE OR REPLACE VIEW `Rack` AS SELECT O.id, O.name AS name, O.asset_no, O.has_problems, O.comment,
548 AV_H.uint_value AS height,
549 AV_S.uint_value AS sort_order,
550 RT.thumb_data,
551 R.id AS row_id,
552 R.name AS row_name,
553 L.id AS location_id,
554 L.name AS location_name
555 FROM `Object` O
556 LEFT JOIN `AttributeValue` AV_H ON O.id = AV_H.object_id AND AV_H.attr_id = 27
557 LEFT JOIN `AttributeValue` AV_S ON O.id = AV_S.object_id AND AV_S.attr_id = 29
558 LEFT JOIN `RackThumbnail` RT ON O.id = RT.rack_id
559 LEFT JOIN `EntityLink` RL ON O.id = RL.child_entity_id AND RL.parent_entity_type = 'row' AND RL.child_entity_type = 'rack'
560 INNER JOIN `Object` R ON R.id = RL.parent_entity_id
561 LEFT JOIN `EntityLink` LL ON R.id = LL.child_entity_id AND LL.parent_entity_type = 'location' AND LL.child_entity_type = 'row'
562 LEFT JOIN `Object` L ON L.id = LL.parent_entity_id
563 WHERE O.objtype_id = 1560
564 ";
565
566 // prevent some AttributeMap entries from being deleted
567 $query[] = "ALTER TABLE AttributeMap ADD COLUMN sticky enum('yes','no') default 'no'";
568 $query[] = "UPDATE AttributeMap SET sticky = 'yes' WHERE objtype_id = 4 AND attr_id IN (26,28)"; // Server -> Hypervisor, Slot number
569 $query[] = "UPDATE AttributeMap SET sticky = 'yes' WHERE objtype_id = 8 AND attr_id IN (1,2,4,28)"; // Network switch -> OEM S/N 1, HW type, SW type, Slot number
570 $query[] = "UPDATE AttributeMap SET sticky = 'yes' WHERE objtype_id = 798 AND attr_id = 28"; // Network security -> Slot number
571 $query[] = "UPDATE AttributeMap SET sticky = 'yes' WHERE objtype_id = 1055 AND attr_id = 28"; // FC switch -> Slot number
572 $query[] = "UPDATE AttributeMap SET sticky = 'yes' WHERE objtype_id = 1560 AND attr_id IN (27,29)"; // Rack -> Height, Sort order
573 $query[] = "UPDATE AttributeMap SET sticky = 'yes' WHERE objtype_id = 1787 AND attr_id = 30"; // Management interface -> Mgmt type
574
575 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('RDP_OBJS_LISTSRC','false','string','yes','no','yes','Rackcode filter for RDP-managed objects')";
576
577 // SLB v2 tables
578 $query[] = "
579 CREATE TABLE `VS` (
580 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
581 `name` char(255) DEFAULT NULL,
582 `vsconfig` text,
583 `rsconfig` text,
584 PRIMARY KEY (`id`)
585 ) ENGINE=InnoDB
586 ";
587 $query[] = "
588 CREATE TABLE `VSIPs` (
589 `vs_id` int(10) unsigned NOT NULL,
590 `vip` varbinary(16) NOT NULL,
591 `vsconfig` text,
592 `rsconfig` text,
593 PRIMARY KEY (`vs_id`,`vip`),
594 KEY `vip` (`vip`),
595 CONSTRAINT `VSIPs-vs_id` FOREIGN KEY (`vs_id`) REFERENCES `VS` (`id`) ON DELETE CASCADE
596 ) ENGINE=InnoDB
597 ";
598 $query[] = "
599 CREATE TABLE `VSPorts` (
600 `vs_id` int(10) unsigned NOT NULL,
601 `proto` enum('TCP','UDP','MARK') NOT NULL,
602 `vport` int(10) unsigned NOT NULL,
603 `vsconfig` text,
604 `rsconfig` text,
605 PRIMARY KEY (`vs_id`,`proto`,`vport`),
606 KEY `proto-vport` (`proto`,`vport`),
607 CONSTRAINT `VS-vs_id` FOREIGN KEY (`vs_id`) REFERENCES `VS` (`id`) ON DELETE CASCADE
608 ) ENGINE=InnoDB
609 ";
610 $query[] = "
611 CREATE TABLE `VSEnabledIPs` (
612 `object_id` int(10) unsigned NOT NULL,
613 `vs_id` int(10) unsigned NOT NULL,
614 `vip` varbinary(16) NOT NULL,
615 `rspool_id` int(10) unsigned NOT NULL,
616 `prio` varchar(255) DEFAULT NULL,
617 `vsconfig` text,
618 `rsconfig` text,
619 PRIMARY KEY (`object_id`,`vs_id`,`vip`,`rspool_id`),
620 KEY `vip` (`vip`),
621 KEY `VSEnabledIPs-FK-vs_id-vip` (`vs_id`,`vip`),
622 KEY `VSEnabledIPs-FK-rspool_id` (`rspool_id`),
623 CONSTRAINT `VSEnabledIPs-FK-rspool_id` FOREIGN KEY (`rspool_id`) REFERENCES `IPv4RSPool` (`id`) ON DELETE CASCADE,
624 CONSTRAINT `VSEnabledIPs-FK-vs_id-vip` FOREIGN KEY (`vs_id`, `vip`) REFERENCES `VSIPs` (`vs_id`, `vip`) ON DELETE CASCADE
625 ) ENGINE=InnoDB
626 ";
627 $query[] = "
628 CREATE TABLE `VSEnabledPorts` (
629 `object_id` int(10) unsigned NOT NULL,
630 `vs_id` int(10) unsigned NOT NULL,
631 `proto` enum('TCP','UDP','MARK') NOT NULL,
632 `vport` int(10) unsigned NOT NULL,
633 `rspool_id` int(10) unsigned NOT NULL,
634 `vsconfig` text,
635 `rsconfig` text,
636 PRIMARY KEY (`object_id`,`vs_id`,`proto`,`vport`,`rspool_id`),
637 KEY `VSEnabledPorts-FK-vs_id-proto-vport` (`vs_id`,`proto`,`vport`),
638 KEY `VSEnabledPorts-FK-rspool_id` (`rspool_id`),
639 CONSTRAINT `VSEnabledPorts-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `Object` (`id`) ON DELETE CASCADE,
640 CONSTRAINT `VSEnabledPorts-FK-rspool_id` FOREIGN KEY (`rspool_id`) REFERENCES `IPv4RSPool` (`id`) ON DELETE CASCADE,
641 CONSTRAINT `VSEnabledPorts-FK-vs_id-proto-vport` FOREIGN KEY (`vs_id`, `proto`, `vport`) REFERENCES `VSPorts` (`vs_id`, `proto`, `vport`) ON DELETE CASCADE
642 ) ENGINE=InnoDB
643 ";
644 $query[] = "ALTER TABLE `EntityLink` MODIFY COLUMN `parent_entity_type` ENUM('ipv4net','ipv4rspool','ipv4vs','ipv6net','location','object','rack','row','user') NOT NULL";
645 $query[] = "ALTER TABLE `FileLink` MODIFY COLUMN `entity_type` ENUM('ipv4net','ipv4rspool','ipv4vs','ipv6net','location','object','rack','row','user') NOT NULL DEFAULT 'object'";
646 $query[] = "ALTER TABLE `TagStorage` MODIFY COLUMN `entity_realm` ENUM('file','ipv4net','ipv4rspool','ipv4vs','ipvs','ipv6net','location','object','rack','user','vst') NOT NULL DEFAULT 'object'";
647 $query[] = "ALTER TABLE `UserConfig` DROP FOREIGN KEY `UserConfig-FK-user`";
648 $query[] = "UPDATE Config SET varvalue = '0.20.5' WHERE varname = 'DB_VERSION'";
649 break;
650 case '0.20.6':
651 // one HW type was moved from the 'Network switch' chapter to the 'Network chassis' chapter
652 // change the type of affected objects to 'Network chassis'
653 $query[] = "UPDATE `Object` SET objtype_id = 1503 WHERE id IN (SELECT object_id FROM `AttributeValue` WHERE attr_id = 2 and uint_value = 935)";
654
655 // convert values of old 'TELNET_OBJS_LISTSRC' 'SSH_OBJS_LISTSRC', 'RDP_OBJS_LISTSRC' variables into 'MGMT_PROTOS'
656 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('MGMT_PROTOS','ssh: {\$typeid_4}; telnet: {\$typeid_8}','string','yes','no','yes','Mapping of management protocol to devices')";
657 if ('' !== $mgmt_converted_var = convertMgmtConfigVars())
658 $query[] = "UPDATE `Config` SET varvalue = '" . mysql_escape_string ($mgmt_converted_var) . "' WHERE varname = 'MGMT_PROTOS'"; // TODO: call of deprecated function
659 $query[] = "DELETE `Config`,`UserConfig` FROM `Config` LEFT JOIN `UserConfig` USING (`varname`) WHERE `Config`.`varname` IN ('TELNET_OBJS_LISTSRC', 'SSH_OBJS_LISTSRC', 'RDP_OBJS_LISTSRC')";
660
661 $query[] = "ALTER TABLE `VSEnabledIPs` ADD CONSTRAINT `VSEnabledIPs-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `Object` (`id`) ON DELETE CASCADE";
662
663 $query[] = "DELETE FROM Config WHERE varname = 'PORTS_PER_ROW'";
664 $query[] = "UPDATE Config SET varvalue = '0.20.6' WHERE varname = 'DB_VERSION'";
665 break;
666 case '0.20.7':
667 if (! isInnoDBSupported ())
668 {
669 showUpgradeError ('Cannot upgrade because triggers are not supported by your MySQL server.', __FUNCTION__);
670 die;
671 }
672
673 // for the UNIQUE key to work, portb needs to be > porta
674 $result = $dbxlink->query ('SELECT porta, portb FROM `Link` WHERE porta > portb');
675 $links = $result->fetchAll (PDO::FETCH_ASSOC);
676 unset ($result);
677 foreach ($links as $link)
678 $query[] = "UPDATE `Link` SET `porta`=${link['portb']}, `portb`=${link['porta']} WHERE `porta`=${link['porta']} AND `portb`=${link['portb']}";
679
680 // add triggers
681 $query[] = "
682 CREATE TRIGGER `EntityLink-before-insert` BEFORE INSERT ON `EntityLink` FOR EACH ROW
683 EntityLinkTrigger:BEGIN
684 DECLARE parent_objtype, child_objtype, count INTEGER;
685
686 # forbid linking an entity to itself
687 IF NEW.parent_entity_type = NEW.child_entity_type AND NEW.parent_entity_id = NEW.child_entity_id THEN
688 SET NEW.parent_entity_id = NULL;
689 LEAVE EntityLinkTrigger;
690 END IF;
691
692 # in some scenarios, only one parent is allowed
693 CASE CONCAT(NEW.parent_entity_type, '.', NEW.child_entity_type)
694 WHEN 'location.location' THEN
695 SELECT COUNT(*) INTO count FROM EntityLink WHERE parent_entity_type = 'location' AND child_entity_type = 'location' AND child_entity_id = NEW.child_entity_id;
696 WHEN 'location.row' THEN
697 SELECT COUNT(*) INTO count FROM EntityLink WHERE parent_entity_type = 'location' AND child_entity_type = 'row' AND child_entity_id = NEW.child_entity_id;
698 WHEN 'row.rack' THEN
699 SELECT COUNT(*) INTO count FROM EntityLink WHERE parent_entity_type = 'row' AND child_entity_type = 'rack' AND child_entity_id = NEW.child_entity_id;
700 ELSE
701 # some other scenario, assume it is valid
702 SET count = 0;
703 END CASE;
704 IF count > 0 THEN
705 SET NEW.parent_entity_id = NULL;
706 LEAVE EntityLinkTrigger;
707 END IF;
708
709 IF NEW.parent_entity_type = 'object' AND NEW.child_entity_type = 'object' THEN
710 # lock objects to prevent concurrent link establishment
711 SELECT objtype_id INTO parent_objtype FROM Object WHERE id = NEW.parent_entity_id FOR UPDATE;
712 SELECT objtype_id INTO child_objtype FROM Object WHERE id = NEW.child_entity_id FOR UPDATE;
713
714 # only permit the link if object types are compatibile
715 SELECT COUNT(*) INTO count FROM ObjectParentCompat WHERE parent_objtype_id = parent_objtype AND child_objtype_id = child_objtype;
716 IF count = 0 THEN
717 SET NEW.parent_entity_id = NULL;
718 END IF;
719 END IF;
720 END;
721 ";
722 $query[] = "
723 CREATE TRIGGER `EntityLink-before-update` BEFORE UPDATE ON `EntityLink` FOR EACH ROW
724 EntityLinkTrigger:BEGIN
725 DECLARE parent_objtype, child_objtype, count INTEGER;
726
727 # forbid linking an entity to itself
728 IF NEW.parent_entity_type = NEW.child_entity_type AND NEW.parent_entity_id = NEW.child_entity_id THEN
729 SET NEW.parent_entity_id = NULL;
730 LEAVE EntityLinkTrigger;
731 END IF;
732
733 # in some scenarios, only one parent is allowed
734 CASE CONCAT(NEW.parent_entity_type, '.', NEW.child_entity_type)
735 WHEN 'location.location' THEN
736 SELECT COUNT(*) INTO count FROM EntityLink WHERE parent_entity_type = 'location' AND child_entity_type = 'location' AND child_entity_id = NEW.child_entity_id AND id != NEW.id;
737 WHEN 'location.row' THEN
738 SELECT COUNT(*) INTO count FROM EntityLink WHERE parent_entity_type = 'location' AND child_entity_type = 'row' AND child_entity_id = NEW.child_entity_id AND id != NEW.id;
739 WHEN 'row.rack' THEN
740 SELECT COUNT(*) INTO count FROM EntityLink WHERE parent_entity_type = 'row' AND child_entity_type = 'rack' AND child_entity_id = NEW.child_entity_id AND id != NEW.id;
741 ELSE
742 # some other scenario, assume it is valid
743 SET count = 0;
744 END CASE;
745 IF count > 0 THEN
746 SET NEW.parent_entity_id = NULL;
747 LEAVE EntityLinkTrigger;
748 END IF;
749
750 IF NEW.parent_entity_type = 'object' AND NEW.child_entity_type = 'object' THEN
751 # lock objects to prevent concurrent link establishment
752 SELECT objtype_id INTO parent_objtype FROM Object WHERE id = NEW.parent_entity_id FOR UPDATE;
753 SELECT objtype_id INTO child_objtype FROM Object WHERE id = NEW.child_entity_id FOR UPDATE;
754
755 # only permit the link if object types are compatibile
756 SELECT COUNT(*) INTO count FROM ObjectParentCompat WHERE parent_objtype_id = parent_objtype AND child_objtype_id = child_objtype;
757 IF count = 0 THEN
758 SET NEW.parent_entity_id = NULL;
759 END IF;
760 END IF;
761 END;
762 ";
763 $link_trigger_body = <<<'ENDOFTRIGGER'
764 LinkTrigger:BEGIN
765 DECLARE tmp, porta_type, portb_type, count INTEGER;
766
767 IF NEW.porta = NEW.portb THEN
768 # forbid connecting a port to itself
769 SET NEW.porta = NULL;
770 LEAVE LinkTrigger;
771 ELSEIF NEW.porta > NEW.portb THEN
772 # force porta < portb
773 SET tmp = NEW.porta;
774 SET NEW.porta = NEW.portb;
775 SET NEW.portb = tmp;
776 END IF;
777
778 # lock ports to prevent concurrent link establishment
779 SELECT type INTO porta_type FROM Port WHERE id = NEW.porta FOR UPDATE;
780 SELECT type INTO portb_type FROM Port WHERE id = NEW.portb FOR UPDATE;
781
782 # only permit the link if ports are compatibile
783 SELECT COUNT(*) INTO count FROM PortCompat WHERE (type1 = porta_type AND type2 = portb_type) OR (type1 = portb_type AND type2 = porta_type);
784 IF count = 0 THEN
785 SET NEW.porta = NULL;
786 END IF;
787 END;
788 ENDOFTRIGGER;
789 $query[] = "CREATE TRIGGER `Link-before-insert` BEFORE INSERT ON `Link` FOR EACH ROW $link_trigger_body";
790 $query[] = "CREATE TRIGGER `Link-before-update` BEFORE UPDATE ON `Link` FOR EACH ROW $link_trigger_body";
791
792 // enable IP addressing for all object types unless specifically excluded
793 $query[] = "UPDATE `Config` SET varvalue = 'not ({\$typeid_3} or {\$typeid_9} or {\$typeid_10} or {\$typeid_11})' WHERE varname = 'IPV4OBJ_LISTSRC'";
794
795 $query[] = "ALTER TABLE `EntityLink` MODIFY COLUMN `parent_entity_type` ENUM('location','object','rack','row') NOT NULL";
796 $query[] = "ALTER TABLE `EntityLink` MODIFY COLUMN `child_entity_type` ENUM('location','object','rack','row') NOT NULL";
797
798 $query[] = "UPDATE Config SET description = 'List source: objects for that asset tag should be set' WHERE varname = 'ASSETWARN_LISTSRC'";
799 $query[] = "UPDATE Config SET description = 'List source: objects for that common name should be set' WHERE varname = 'NAMEWARN_LISTSRC'";
800 $query[] = "ALTER TABLE `IPv4NAT` MODIFY COLUMN `proto` ENUM('TCP','UDP','ALL')";
801
802 // add new 'point2point' alloc type
803 $query[] = "ALTER TABLE `IPv4Allocation` MODIFY `type` enum('regular','shared','virtual','router','point2point') NOT NULL DEFAULT 'regular'";
804 $query[] = "ALTER TABLE `IPv6Allocation` MODIFY `type` enum('regular','shared','virtual','router','point2point') NOT NULL DEFAULT 'regular'";
805
806 // update to use utf8_unicode_ci collation
807 // http://bugs.racktables.org/view.php?id=837
808 $query[] = "ALTER DATABASE DEFAULT CHARACTER SET UTF8 COLLATE utf8_unicode_ci";
809
810 $query[] = "SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0";
811 $query[] = "ALTER TABLE `Atom` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
812 $query[] = "ALTER TABLE `Attribute` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
813 $query[] = "ALTER TABLE `AttributeMap` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
814 $query[] = "ALTER TABLE `AttributeValue` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
815 $query[] = "ALTER TABLE `CachedPAV` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
816 $query[] = "ALTER TABLE `CachedPNV` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
817 $query[] = "ALTER TABLE `CachedPVM` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
818 $query[] = "ALTER TABLE `CactiGraph` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
819 $query[] = "ALTER TABLE `CactiServer` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
820 $query[] = "ALTER TABLE `Chapter` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
821 $query[] = "ALTER TABLE `Config` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
822 $query[] = "ALTER TABLE `Dictionary` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
823 $query[] = "ALTER TABLE `EntityLink` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
824 $query[] = "ALTER TABLE `File` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
825 $query[] = "ALTER TABLE `FileLink` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
826 $query[] = "ALTER TABLE `IPv4Address` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
827 $query[] = "ALTER TABLE `IPv4Allocation` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
828 $query[] = "ALTER TABLE `IPv4LB` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
829 $query[] = "ALTER TABLE `IPv4Log` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
830 $query[] = "ALTER TABLE `IPv6Log` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
831 $query[] = "ALTER TABLE `IPv4NAT` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
832 $query[] = "ALTER TABLE `IPv4Network` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
833 $query[] = "ALTER TABLE `IPv4RS` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
834 $query[] = "ALTER TABLE `IPv4RSPool` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
835 $query[] = "ALTER TABLE `IPv4VS` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
836 $query[] = "ALTER TABLE `IPv6Address` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
837 $query[] = "ALTER TABLE `IPv6Allocation` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
838 $query[] = "ALTER TABLE `IPv6Network` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
839 $query[] = "ALTER TABLE `LDAPCache` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
840 $query[] = "ALTER TABLE `Link` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
841 $query[] = "ALTER TABLE `Molecule` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
842 $query[] = "ALTER TABLE `MountOperation` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
843 $query[] = "ALTER TABLE `MuninGraph` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
844 $query[] = "ALTER TABLE `MuninServer` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
845 $query[] = "ALTER TABLE `ObjectLog` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
846 $query[] = "ALTER TABLE `ObjectParentCompat` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
847 $query[] = "ALTER TABLE `Port` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
848 $query[] = "ALTER TABLE `PortAllowedVLAN` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
849 $query[] = "ALTER TABLE `PortCompat` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
850 $query[] = "ALTER TABLE `PortInnerInterface` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
851 $query[] = "ALTER TABLE `PortInterfaceCompat` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
852 $query[] = "ALTER TABLE `PortLog` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
853 $query[] = "ALTER TABLE `PortNativeVLAN` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
854 $query[] = "ALTER TABLE `PortVLANMode` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
855 $query[] = "ALTER TABLE `Object` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
856 $query[] = "ALTER TABLE `ObjectHistory` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
857 $query[] = "ALTER TABLE `RackSpace` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
858 $query[] = "ALTER TABLE `RackThumbnail` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
859 $query[] = "ALTER TABLE `Script` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
860 $query[] = "ALTER TABLE `TagStorage` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
861 $query[] = "ALTER TABLE `TagTree` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
862 $query[] = "ALTER TABLE `UserAccount` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
863 $query[] = "ALTER TABLE `UserConfig` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
864 $query[] = "ALTER TABLE `VLANDescription` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
865 $query[] = "ALTER TABLE `VLANDomain` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
866 $query[] = "ALTER TABLE `VLANIPv4` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
867 $query[] = "ALTER TABLE `VLANIPv6` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
868 $query[] = "ALTER TABLE `VLANSTRule` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
869 $query[] = "ALTER TABLE `VLANSwitch` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
870 $query[] = "ALTER TABLE `VLANSwitchTemplate` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
871 $query[] = "ALTER TABLE `VLANValidID` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
872 $query[] = "ALTER TABLE `VS` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
873 $query[] = "ALTER TABLE `VSIPs` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
874 $query[] = "ALTER TABLE `VSPorts` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
875 $query[] = "ALTER TABLE `VSEnabledIPs` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
876 $query[] = "ALTER TABLE `VSEnabledPorts` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
877 $query[] = "SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS";
878
879 $query[] = "UPDATE Config SET varvalue = '0.20.7' WHERE varname = 'DB_VERSION'";
880 break;
881 case '0.20.8':
882 $query[] = "ALTER TABLE `VLANSTRule` CHANGE COLUMN `wrt_vlans` `wrt_vlans` text";
883
884 $query[] = "
885 CREATE TABLE `PortOuterInterface` (
886 `id` int(10) unsigned NOT NULL auto_increment,
887 `oif_name` char(48) NOT NULL,
888 PRIMARY KEY (`id`),
889 UNIQUE KEY `oif_name` (`oif_name`)
890 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
891 ";
892 // if upgrading from certain older releases, inject some data to satisfy foreign keys
893 if (version_compare ($dbver, '0.19.2', '<'))
894 $query[] = "INSERT INTO Dictionary (chapter_id,dict_key,dict_value) VALUES (2,1399,'DC')";
895 if (version_compare ($dbver, '0.19.5', '<'))
896 $query[] = "INSERT INTO Dictionary (chapter_id,dict_key,dict_value) VALUES (2,1469,'virtual port')";
897 if (version_compare ($dbver, '0.19.7', '<'))
898 $query[] = "INSERT INTO Dictionary (chapter_id,dict_key,dict_value) VALUES (2,1588,'empty QSFP+')";
899 if (version_compare ($dbver, '0.19.8', '<'))
900 $query[] = "INSERT INTO Dictionary (chapter_id,dict_key,dict_value) VALUES
901 (2,1424,'1000Base-CX'),
902 (2,1425,'10GBase-ER-DWDM40-61.42 (ITU 20)'),
903 (2,1426,'10GBase-ER-DWDM40-60.61 (ITU 21)'),
904 (2,1427,'10GBase-ER-DWDM40-59.79 (ITU 22)'),
905 (2,1428,'10GBase-ER-DWDM40-58.98 (ITU 23)'),
906 (2,1429,'10GBase-ER-DWDM40-58.17 (ITU 24)'),
907 (2,1430,'10GBase-ER-DWDM40-57.36 (ITU 25)'),
908 (2,1431,'10GBase-ER-DWDM40-56.55 (ITU 26)'),
909 (2,1432,'10GBase-ER-DWDM40-55.75 (ITU 27)'),
910 (2,1433,'10GBase-ER-DWDM40-54.94 (ITU 28)'),
911 (2,1434,'10GBase-ER-DWDM40-54.13 (ITU 29)'),
912 (2,1435,'10GBase-ER-DWDM40-53.33 (ITU 30)'),
913 (2,1436,'10GBase-ER-DWDM40-52.52 (ITU 31)'),
914 (2,1437,'10GBase-ER-DWDM40-51.72 (ITU 32)'),
915 (2,1438,'10GBase-ER-DWDM40-50.92 (ITU 33)'),
916 (2,1439,'10GBase-ER-DWDM40-50.12 (ITU 34)'),
917 (2,1440,'10GBase-ER-DWDM40-49.32 (ITU 35)'),
918 (2,1441,'10GBase-ER-DWDM40-48.51 (ITU 36)'),
919 (2,1442,'10GBase-ER-DWDM40-47.72 (ITU 37)'),
920 (2,1443,'10GBase-ER-DWDM40-46.92 (ITU 38)'),
921 (2,1444,'10GBase-ER-DWDM40-46.12 (ITU 39)'),
922 (2,1445,'10GBase-ER-DWDM40-45.32 (ITU 40)'),
923 (2,1446,'10GBase-ER-DWDM40-44.53 (ITU 41)'),
924 (2,1447,'10GBase-ER-DWDM40-43.73 (ITU 42)'),
925 (2,1448,'10GBase-ER-DWDM40-42.94 (ITU 43)'),
926 (2,1449,'10GBase-ER-DWDM40-42.14 (ITU 44)'),
927 (2,1450,'10GBase-ER-DWDM40-41.35 (ITU 45)'),
928 (2,1451,'10GBase-ER-DWDM40-40.56 (ITU 46)'),
929 (2,1452,'10GBase-ER-DWDM40-39.77 (ITU 47)'),
930 (2,1453,'10GBase-ER-DWDM40-38.98 (ITU 48)'),
931 (2,1454,'10GBase-ER-DWDM40-38.19 (ITU 49)'),
932 (2,1455,'10GBase-ER-DWDM40-37.40 (ITU 50)'),
933 (2,1456,'10GBase-ER-DWDM40-36.61 (ITU 51)'),
934 (2,1457,'10GBase-ER-DWDM40-35.82 (ITU 52)'),
935 (2,1458,'10GBase-ER-DWDM40-35.04 (ITU 53)'),
936 (2,1459,'10GBase-ER-DWDM40-34.25 (ITU 54)'),
937 (2,1460,'10GBase-ER-DWDM40-33.47 (ITU 55)'),
938 (2,1461,'10GBase-ER-DWDM40-32.68 (ITU 56)'),
939 (2,1462,'10GBase-ER-DWDM40-31.90 (ITU 57)'),
940 (2,1463,'10GBase-ER-DWDM40-31.12 (ITU 58)'),
941 (2,1464,'10GBase-ER-DWDM40-30.33 (ITU 59)'),
942 (2,1465,'10GBase-ER-DWDM40-29.55 (ITU 60)'),
943 (2,1466,'10GBase-ER-DWDM40-28.77 (ITU 61)')";
944 if (version_compare ($dbver, '0.19.10', '<'))
945 $query[] = "INSERT INTO Dictionary (chapter_id,dict_key,dict_value) VALUES (2,1603,'1000Base-T (HP c-Class)')";
946 if (version_compare ($dbver, '0.19.11', '<'))
947 $query[] = "INSERT INTO Dictionary (chapter_id,dict_key,dict_value) VALUES (2,1642,'10GBase-T')";
948 if (version_compare ($dbver, '0.19.12', '<'))
949 $query[] = "INSERT INTO Dictionary (chapter_id,dict_key,dict_value) VALUES
950 (2,1661,'40GBase-KR4'),
951 (2,1663,'40GBase-SR4'),
952 (2,1664,'40GBase-LR4'),
953 (2,1668,'empty CFP'),
954 (2,1669,'100GBase-SR10'),
955 (2,1670,'100GBase-LR4'),
956 (2,1671,'100GBase-ER4')";
957 $query[] = "INSERT INTO PortOuterInterface SELECT dict_key, dict_value FROM Dictionary WHERE chapter_id = 2";
958 // Previously listed 10GBase-Kx actually means two standards: 10GBase-KX4
959 // and 10GBase-KR. Make respective changes and make primary key auto
960 // increment start at 2000.
961 $query[] = "UPDATE PortOuterInterface SET oif_name = '10GBase-KX4' WHERE id = 41";
962 $query[] = "INSERT INTO PortOuterInterface (id, oif_name) VALUES (1999, '10GBase-KR')";
963 $query[] = "INSERT INTO PortCompat (type1, type2) VALUES (1999, 1999)";
964 $query[] = "DELETE FROM Dictionary WHERE chapter_id = 2";
965 $query[] = "DELETE FROM Chapter WHERE id = 2";
966 $query[] = "ALTER TABLE PortInterfaceCompat ADD CONSTRAINT `PortInterfaceCompat-FK-oif_id` FOREIGN KEY (oif_id) REFERENCES PortOuterInterface (id)";
967 $query[] = "ALTER TABLE PortCompat ADD CONSTRAINT `PortCompat-FK-oif_id1` FOREIGN KEY (type1) REFERENCES PortOuterInterface (id)";
968 $query[] = "ALTER TABLE PortCompat ADD CONSTRAINT `PortCompat-FK-oif_id2` FOREIGN KEY (type2) REFERENCES PortOuterInterface (id)";
969 // Add more 40G and 100G standards.
970 $query[] = "INSERT INTO PortOuterInterface (id, oif_name) VALUES
971 (1660,'40GBase-FR'),
972 (1662,'40GBase-ER4'),
973 (1672,'100GBase-SR4'),
974 (1673,'100GBase-KR4'),
975 (1674,'100GBase-KP4')";
976 $query[] = "INSERT INTO PortInterfaceCompat (iif_id, oif_id) VALUES
977 (10,1660),
978 (10,1662),
979 (11,1672),
980 (11,1673),
981 (11,1674)";
982 $query[] = "INSERT INTO PortCompat (type1, type2) VALUES
983 (1660,1660),
984 (1662,1662),
985 (1672,1672),
986 (1673,1673),
987 (1674,1674)";
988 // Refine 1G OIF list: fix spelling and add a new standard.
989 $query[] = "UPDATE PortOuterInterface SET oif_name = '1000Base-LX10' WHERE id = 1205";
990 $query[] = "INSERT INTO PortOuterInterface (id, oif_name) VALUES (42, '1000Base-EX')";
991 $query[] = "INSERT INTO PortCompat (type1, type2) VALUES (42, 42)";
992 $query[] = "INSERT INTO PortInterfaceCompat (iif_id, oif_id) VALUES (3, 42), (4,42)";
993 // patch cables
994 $query[] = "
995 CREATE TABLE `PatchCableConnector` (
996 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
997 `origin` enum('default','custom') NOT NULL DEFAULT 'custom',
998 `connector` char(32) NOT NULL,
999 PRIMARY KEY (`id`),
1000 UNIQUE KEY `connector_per_origin` (`connector`,`origin`)
1001 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";
1002 $query[] = "
1003 CREATE TABLE `PatchCableType` (
1004 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
1005 `origin` enum('default','custom') NOT NULL DEFAULT 'custom',
1006 `pctype` char(64) NOT NULL,
1007 PRIMARY KEY (`id`),
1008 UNIQUE KEY `pctype_per_origin` (`pctype`,`origin`)
1009 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";
1010 $query[] = "
1011 CREATE TABLE `PatchCableConnectorCompat` (
1012 `pctype_id` int(10) unsigned NOT NULL,
1013 `connector_id` int(10) unsigned NOT NULL,
1014 PRIMARY KEY (`pctype_id`,`connector_id`),
1015 KEY `connector_id` (`connector_id`),
1016 CONSTRAINT `PatchCableConnectorCompat-FK-connector_id` FOREIGN KEY (`connector_id`) REFERENCES `PatchCableConnector` (`id`),
1017 CONSTRAINT `PatchCableConnectorCompat-FK-pctype_id` FOREIGN KEY (`pctype_id`) REFERENCES `PatchCableType` (`id`)
1018 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";
1019 $query[] = "
1020 CREATE TABLE `PatchCableHeap` (
1021 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
1022 `pctype_id` int(10) unsigned NOT NULL,
1023 `end1_conn_id` int(10) unsigned NOT NULL,
1024 `end2_conn_id` int(10) unsigned NOT NULL,
1025 `amount` smallint(5) unsigned NOT NULL DEFAULT '0',
1026 `length` decimal(5,2) unsigned NOT NULL DEFAULT '1.00',
1027 `description` char(255) DEFAULT NULL,
1028 PRIMARY KEY (`id`),
1029 KEY `compat1` (`pctype_id`,`end1_conn_id`),
1030 KEY `compat2` (`pctype_id`,`end2_conn_id`),
1031 CONSTRAINT `PatchCableHeap-FK-compat1` FOREIGN KEY (`pctype_id`, `end1_conn_id`) REFERENCES `PatchCableConnectorCompat` (`pctype_id`, `connector_id`),
1032 CONSTRAINT `PatchCableHeap-FK-compat2` FOREIGN KEY (`pctype_id`, `end2_conn_id`) REFERENCES `PatchCableConnectorCompat` (`pctype_id`, `connector_id`)
1033 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";
1034 $query[] = "
1035 CREATE TABLE `PatchCableHeapLog` (
1036 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
1037 `heap_id` int(10) unsigned NOT NULL,
1038 `date` datetime NOT NULL,
1039 `user` char(64) NOT NULL,
1040 `message` char(255) NOT NULL,
1041 PRIMARY KEY (`id`),
1042 KEY `heap_id-date` (`heap_id`,`date`),
1043 CONSTRAINT `PatchCableHeapLog-FK-heap_id` FOREIGN KEY (`heap_id`) REFERENCES `PatchCableHeap` (`id`) ON DELETE CASCADE
1044 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";
1045 $query[] = "
1046 CREATE TABLE `PatchCableOIFCompat` (
1047 `pctype_id` int(10) unsigned NOT NULL,
1048 `oif_id` int(10) unsigned NOT NULL,
1049 PRIMARY KEY (`pctype_id`,`oif_id`),
1050 KEY `oif_id` (`oif_id`),
1051 CONSTRAINT `PatchCableOIFCompat-FK-oif_id` FOREIGN KEY (`oif_id`) REFERENCES `PortOuterInterface` (`id`),
1052 CONSTRAINT `PatchCableOIFCompat-FK-pctype_id` FOREIGN KEY (`pctype_id`) REFERENCES `PatchCableType` (`id`)
1053 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";
1054 $query[] = "INSERT INTO PatchCableConnector (id, origin, connector) VALUES
1055 (1,'default','FC/PC'),(2,'default','FC/APC'),
1056 (3,'default','LC/PC'),(4,'default','LC/APC'),
1057 (5,'default','MPO-12/PC'),(6,'default','MPO-12/APC'),
1058 (7,'default','MPO-24/PC'),(8,'default','MPO-24/APC'),
1059 (9,'default','SC/PC'),(10,'default','SC/APC'),
1060 (11,'default','ST/PC'),(12,'default','ST/APC'),
1061 (13,'default','T568/8P8C/RJ45'),
1062 (14,'default','SFP-1000'),
1063 (15,'default','SFP+'),
1064 (999,'default','CX4/SFF-8470')";
1065 $query[] = "INSERT INTO PatchCableType (id, origin, pctype) VALUES
1066 (1,'default','duplex OM1'),
1067 (2,'default','duplex OM2'),
1068 (3,'default','duplex OM3'),
1069 (4,'default','duplex OM4'),
1070 (5,'default','duplex OS1'),
1071 (6,'default','duplex OS2'),
1072 (7,'default','simplex OM1'),
1073 (8,'default','simplex OM2'),
1074 (9,'default','simplex OM3'),
1075 (10,'default','simplex OM4'),
1076 (11,'default','simplex OS1'),
1077 (12,'default','simplex OS2'),
1078 (13,'default','Cat.5 TP'),
1079 (14,'default','Cat.6 TP'),
1080 (15,'default','Cat.6a TP'),
1081 (16,'default','Cat.7 TP'),
1082 (17,'default','Cat.7a TP'),
1083 (18,'default','12-fiber OM3'),
1084 (19,'default','12-fiber OM4'),
1085 (20,'default','10Gb/s CX4 coax'),
1086 (21,'default','24-fiber OM3'),
1087 (22,'default','24-fiber OM4'),
1088 (23,'default','1Gb/s 50cm shielded'),
1089 (24,'default','10Gb/s 24AWG twinax'),
1090 (25,'default','10Gb/s 26AWG twinax'),
1091 (26,'default','10Gb/s 28AWG twinax'),
1092 (27,'default','10Gb/s 30AWG twinax'),
1093 (999,'default','Cat.3 TP')";
1094 $query[] = "INSERT INTO PatchCableOIFCompat (pctype_id, oif_id) VALUES
1095 (13,18),(14,18),(15,18),(16,18),(17,18),(999,18), -- 10Base-T: Cat.3+ TP
1096 (11,1198),(12,1198),(11,1199),(12,1199), -- 100Base-BX10: 1xSMF
1097 (5,1197),(6,1197), -- 100Base-LX10: 2xSMF
1098 (5,1200),(6,1200), -- 100Base-EX: 2xSMF
1099 (5,1201),(6,1201), -- 100Base-ZX: 2xSMF
1100 (1,1195),(2,1195),(3,1195),(4,1195), -- 100Base-FX: 2xMMF
1101 (1,1196),(2,1196),(3,1196),(4,1196), -- 100Base-SX: 2xMMF
1102 (13,19),(14,19),(15,19),(16,19),(17,19), -- 100Base-TX: Cat.5+ TP
1103 (11,1206),(12,1206),(11,1207),(12,1207), -- 1000Base-BX10: 1xSMF
1104 (5,1204),(6,1204), -- 1000Base-LX: 2xSMF
1105 (5,1205),(6,1205), -- 1000Base-LX10: 2xSMF
1106 (1,1202),(2,1202),(3,1202),(4,1202), -- 1000Base-SX: 2xMMF
1107 (1,1203),(2,1203),(3,1203),(4,1203), -- 1000Base-SX+: 2xMMF
1108 (13,24),(14,24),(15,24),(16,24),(17,24), -- 1000Base-T: Cat.5+ TP
1109 (5,34),(6,34), -- 1000Base-ZX: 2xSMF
1110 (23,1077), -- 1000Base direct attach: shielded
1111 (1,30),(2,30),(3,30),(4,30), -- 10GBase-SR: 2xMMF
1112 (5,36),(6,36), -- 10GBase-LR: 2xSMF
1113 (5,35),(6,35), -- 10GBase-ER: 2xSMF
1114 (5,38),(6,38), -- 10GBase-ZR: 2xSMF
1115 (1,39),(2,39),(3,39),(4,39),(5,39),(6,39), -- 10GBase-LX4: 2xMMF/2xSMF
1116 (1,37),(2,37),(3,37),(4,37), -- 10GBase-LRM: 2xMMF
1117 (14,1642),(15,1642),(16,1642),(17,1642), -- 10GBase-T: Cat.6+ TP
1118 (20,40), -- 10GBase-CX4: coax
1119 (24,1084),(25,1084),(26,1084),(27,1084), -- 10GBase direct attach: twinax
1120 (18,1663),(19,1663), -- 40GBase-SR4: 8xMMF
1121 (5,1664),(6,1664), -- 40GBase-LR4: 2xSMF
1122 (5,1662),(6,1662), -- 40GBase-ER4: 2xSMF
1123 (5,1660),(6,1660), -- 40GBase-FR: 2xSMF
1124 (21,1669),(22,1669), -- 100GBase-SR10: 20xMMF
1125 (18,1672),(19,1672), -- 100GBase-SR4: 8xMMF
1126 (5,1670),(6,1670), -- 100GBase-LR4: 2xSMF
1127 (5,1671),(6,1671) -- 100GBase-ER4: 2xSMF";
1128 $query[] = "INSERT INTO PatchCableConnectorCompat (pctype_id, connector_id) VALUES
1129 (1,1),(2,1),(3,1),(4,1),(5,1),(6,1),(7,1),(8,1),(9,1),(10,1),(11,1),(12,1), -- FC/PC
1130 (1,2),(2,2),(3,2),(4,2),(5,2),(6,2),(7,2),(8,2),(9,2),(10,2),(11,2),(12,2), -- FC/APC
1131 (1,3),(2,3),(3,3),(4,3),(5,3),(6,3),(7,3),(8,3),(9,3),(10,3),(11,3),(12,3), -- LC/PC
1132 (1,4),(2,4),(3,4),(4,4),(5,4),(6,4),(7,4),(8,4),(9,4),(10,4),(11,4),(12,4), -- LC/APC
1133 (1,9),(2,9),(3,9),(4,9),(5,9),(6,9),(7,9),(8,9),(9,9),(10,9),(11,9),(12,9), -- SC/PC
1134 (1,10),(2,10),(3,10),(4,10),(5,10),(6,10),(7,10),(8,10),(9,10),(10,10),(11,10),(12,10), -- SC/APC
1135 (1,11),(2,11),(3,11),(4,11),(5,11),(6,11),(7,11),(8,11),(9,11),(10,11),(11,11),(12,11), -- ST/PC
1136 (1,12),(2,12),(3,12),(4,12),(5,12),(6,12),(7,12),(8,12),(9,12),(10,12),(11,12),(12,12), -- ST/APC
1137 (13,13),(14,13),(15,13),(16,13),(17,13),(999,13), -- T568
1138 (18,5),(19,5), -- MPO-12/PC
1139 (18,6),(19,6), -- MPO-12/APC
1140 (20,999), -- CX4
1141 (21,7),(22,7), -- MPO-24/PC
1142 (21,8),(22,8), -- MPO-24/APC
1143 (23,14), -- SFP-1000
1144 (24,15),(25,15),(26,15),(27,15) -- SFP+";
1145 // add rules for Cisco UCS objects
1146 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1787,8),(1787,1502)";
1147 $query[] = "UPDATE Config SET varvalue = '0.20.8' WHERE varname = 'DB_VERSION'";
1148 break;
1149 case '0.20.9':
1150 $query[] = "ALTER TABLE CactiGraph ADD KEY (server_id)";
1151 $query[] = "ALTER TABLE CactiGraph DROP PRIMARY KEY";
1152 $query[] = "ALTER TABLE CactiGraph ADD PRIMARY KEY (object_id, server_id, graph_id)";
1153 $query[] = "ALTER TABLE CactiGraph DROP KEY `object_id`";
1154 $query[] = "UPDATE Config SET description = 'List of pages to display in quick links' WHERE varname = 'QUICK_LINK_PAGES'";
1155 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('CACTI_RRA_ID','1','uint','no','no','yes','RRA ID for Cacti graphs displayed in RackTables')";
1156 $query[] = "INSERT INTO `Config` (`varname`,`varvalue`,`vartype`,`emptyok`,`is_hidden`,`is_userdefined`,`description`)
1157 VALUES ('SHOW_OBJECTTYPE', 'no', 'string', 'no', 'no', 'yes', 'Show object type column on depot page.')";
1158
1159 $query[] = "INSERT INTO PortInnerInterface (id, iif_name) VALUES (12, 'CFP2'),(13,'CPAK')";
1160 $query[] = "INSERT INTO PortOuterInterface (id, oif_name) VALUES (1589, 'empty CFP2'),(1590,'empty CPAK')";
1161 $query[] = "INSERT INTO PortInterfaceCompat (iif_id, oif_id) VALUES
1162 (12,1589),(12,1669),(12,1670),(12,1671),(12,1672),(12,1673),(12,1674),
1163 (13,1590),(13,1669),(13,1670),(13,1671),(13,1672),(13,1673),(13,1674)";
1164 $query[] = "INSERT INTO PortCompat (type1, type2) VALUES (1588,1589),(1588,1590),(1589,1589),(1589,1590),(1590,1590)";
1165 $query[] = "UPDATE Config SET varvalue = CONCAT(varvalue, '; 12=1589; 13=1590') WHERE varname = 'DEFAULT_PORT_OIF_IDS'";
1166 $query[] = extendPortCompatQuery();
1167
1168 $query[] = "UPDATE Config SET varvalue = '0.20.9' WHERE varname = 'DB_VERSION'";
1169 break;
1170 case '0.20.10':
1171 $query[] = "UPDATE Config SET varvalue = '0.20.10' WHERE varname = 'DB_VERSION'";
1172 break;
1173 case '0.20.11':
1174 $query[] = "ALTER TABLE VLANDomain ADD COLUMN `group_id` int(10) UNSIGNED DEFAULT NULL AFTER `id`, " .
1175 "ADD CONSTRAINT `VLANDomain-FK-group_id` FOREIGN KEY (`group_id`) REFERENCES `VLANDomain` (`id`) ON DELETE SET NULL";
1176
1177 // new 100GBase port types
1178 $query[] = "INSERT INTO `PortInnerInterface` (`id`, `iif_name`) VALUES (14,'CXP')";
1179 $query[] = "INSERT INTO `PortOuterInterface` (`id`, `oif_name`) VALUES
1180 (1591,'empty CXP'),
1181 (1675,'100GBase-LR10'),
1182 (1676,'100GBase-ER10'),
1183 (1677,'100GBase-CR4'),
1184 (1678,'100GBase-CR10')";
1185 $query[] = "INSERT INTO `PatchCableOIFCompat` (`pctype_id`, `oif_id`) VALUES
1186 (5,1675),(6,1675), -- 100GBase-LR10: 2xSMF
1187 (5,1676),(6,1676) -- 100GBase-ER10: 2xSMF";
1188 $query[] = "INSERT INTO `PortInterfaceCompat` (`iif_id`, `oif_id`) VALUES
1189 (11,1675),(11,1676),
1190 (12,1675),(12,1676),
1191 (13,1675),(13,1676),
1192 (14,1591),(14,1677),(14,1678)";
1193 $query[] = "INSERT INTO `PortCompat` (`type1`, `type2`) VALUES
1194 (1591,1591),
1195 (1675,1675),
1196 (1676,1676),
1197 (1677,1677),
1198 (1678,1678)";
1199 $query[] = "UPDATE Config SET varvalue = CONCAT(varvalue, '; 14=1591') WHERE varname = 'DEFAULT_PORT_OIF_IDS'";
1200
1201 // ABI_ver = 2, invalidate RackCode cache
1202 $query[] = "DELETE FROM Script WHERE script_name='RackCodeCache'";
1203
1204 $query[] = "INSERT INTO Config (varname, varvalue, is_hidden, is_userdefined, description) VALUES ('IPV4_TREE_SHOW_UNALLOCATED', 'yes', 'no', 'yes', 'Show unallocated networks in IPv4 tree'); ";
1205 $query[] = "UPDATE Config SET varvalue = '0.20.11' WHERE varname = 'DB_VERSION'";
1206
1207 break;
1208 case '0.20.12':
1209 // NO_ZERO_DATE
1210 $query[] = "ALTER TABLE LDAPCache MODIFY COLUMN last_retry timestamp NULL DEFAULT NULL";
1211 $query[] = "DELETE FROM LDAPCache";
1212
1213 $query[] = "INSERT INTO ObjectParentCompat (parent_objtype_id, child_objtype_id) VALUES (1787,4)";
1214
1215 $port_trigger_body = <<<'ENDOFTRIGGER'
1216 PortTrigger:BEGIN
1217 IF (NEW.`l2address` IS NOT NULL AND (SELECT COUNT(*) FROM `Port` WHERE `l2address` = NEW.`l2address` AND `object_id` != NEW.`object_id`) > 0) THEN
1218 CALL `Port-l2address-already-exists-on-another-object`;
1219 END IF;
1220 END;
1221 ENDOFTRIGGER;
1222 $query[] = "CREATE TRIGGER `Port-before-insert` BEFORE INSERT ON `Port` FOR EACH ROW $port_trigger_body";
1223 $query[] = "CREATE TRIGGER `Port-before-update` BEFORE UPDATE ON `Port` FOR EACH ROW $port_trigger_body";
1224
1225 $query[] = "ALTER TABLE UserConfig DROP FOREIGN KEY `UserConfig-FK-varname`";
1226 $query[] = "ALTER TABLE UserConfig ADD CONSTRAINT `UserConfig-FK-varname` FOREIGN KEY (`varname`) REFERENCES `Config` (`varname`) ON DELETE CASCADE ON UPDATE CASCADE";
1227 $query[] = "UPDATE Config SET varname = 'SYNC_8021Q_LISTSRC' WHERE varname = 'SYNC_802Q_LISTSRC'";
1228
1229 // new iif_type 'QSFP28'
1230 $query[] = "INSERT INTO `PortInnerInterface` (`id`, `iif_name`) VALUES (15,'QSFP28')";
1231 $query[] = "UPDATE Config SET varvalue = CONCAT(varvalue, '; 15=1588') WHERE varname = 'DEFAULT_PORT_OIF_IDS' AND 0 = INSTR(varvalue, '15=')";
1232 // rename 'empty QSFP+' to 'empty QSFP'
1233 $query[] = "UPDATE PortOuterInterface SET oif_name='empty QSFP' WHERE id=1588";
1234
1235 $query[] = "INSERT INTO PortOuterInterface (id, oif_name) VALUES
1236 (1088,'1000Base-BX40-D'),
1237 (1089,'1000Base-BX40-U'),
1238 (1090,'1000Base-BX80-D'),
1239 (1091,'1000Base-BX80-U')";
1240 $query[] = "INSERT INTO PortCompat (type1, type2) VALUES
1241 (1088,1089),
1242 (1089,1088),
1243 (1090,1091),
1244 (1091,1090)";
1245 $query[] = "INSERT INTO PortInterfaceCompat (iif_id, oif_id) VALUES
1246 (4,1088), (4,1089), (4,1090), (4,1091),
1247 (15,1588),(15,1660),(15,1662),(15,1663),(15,1664),(15,1670),(15,1671),(15,1672),(15,1673),(15,1674)";
1248 $query[] = "INSERT INTO PatchCableOIFCompat (pctype_id, oif_id) VALUES
1249 (11,1088), (12,1088), (11,1089), (12,1089),
1250 (11,1090), (12,1090), (11,1091), (12,1091)";
1251
1252 $query[] = "UPDATE Config SET varvalue = '0.20.12' WHERE varname = 'DB_VERSION'";
1253 break;
1254 case '0.20.13':
1255 $query[] = "DROP TRIGGER IF EXISTS `Port-before-insert`";
1256 $query[] = "DROP TRIGGER IF EXISTS `Port-before-update`";
1257 $query[] = "UPDATE Config SET varvalue = '0.20.13' WHERE varname = 'DB_VERSION'";
1258 break;
1259 case '0.20.14':
1260 $query[] = "UPDATE Object SET has_problems = 'no' WHERE objtype_id = 1561 AND has_problems = ''";
1261 $query[] = "UPDATE ObjectHistory SET ctime = ctime, has_problems = 'no' WHERE objtype_id = 1561 AND has_problems = ''";
1262 $query[] = "UPDATE Config SET varvalue = '0.20.14' WHERE varname = 'DB_VERSION'";
1263 break;
1264 case '0.21.0':
1265 $query[] = "UPDATE Port SET label = NULL WHERE label = ''";
1266 $query[] = "DELETE FROM RackThumbnail";
1267 $query[] = "ALTER TABLE TagTree ADD COLUMN color mediumint unsigned DEFAULT NULL AFTER tag";
1268
1269 $query[] = "
1270 CREATE TABLE `Plugin` (
1271 `name` char(255) NOT NULL,
1272 `longname` char(255) NOT NULL,
1273 `version` char(64) NOT NULL,
1274 `home_url` char(255) NOT NULL,
1275 `state` enum('disabled','enabled') NOT NULL default 'disabled',
1276 PRIMARY KEY (`name`)
1277 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";
1278
1279 // if the Cacti or Munin plugins are being used, mark them as enabled, otherwise uninstall them
1280 // from now on, upgrades will be handled by the plugins themselves
1281 $result = $dbxlink->query ('SELECT COUNT(*) FROM CactiServer');
1282 if ($result->fetchColumn() > 0)
1283 $query[] = "INSERT INTO Plugin VALUES ('cacti','Cacti','1.0','http://www.racktables.org/','enabled')";
1284 else
1285 {
1286 $query[] = "DELETE FROM Config WHERE varname IN ('CACTI_LISTSRC','CACTI_RRA_ID')";
1287 $query[] = "UPDATE Config SET varname = 'VLANNET_LISTSRC' WHERE varname = 'VLANIPV4NET_LISTSRC'";
1288 $query[] = "DROP TABLE `CactiGraph`";
1289 $query[] = "DROP TABLE `CactiServer`";
1290 }
1291 unset ($result);
1292 $result = $dbxlink->query ('SELECT COUNT(*) FROM MuninServer');
1293 if ($result->fetchColumn() > 0)
1294 $query[] = "INSERT INTO Plugin VALUES ('munin','Munin','1.0','http://www.racktables.org/','enabled')";
1295 else
1296 {
1297 $query[] = "DELETE FROM Config WHERE varname = 'MUNIN_LISTSRC'";
1298 $query[] = "DROP TABLE `MuninGraph`";
1299 $query[] = "DROP TABLE `MuninServer`";
1300 }
1301 unset ($result);
1302
1303 $query[] = "UPDATE Config SET description = 'PHP strftime() format for date+time' WHERE varname = 'DATETIME_FORMAT'";
1304 $query[] = "
1305 INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES
1306 ('DATEONLY_FORMAT','%Y-%m-%d','string','no','no','yes','PHP strftime() format for dates')";
1307
1308 $query[] = "UPDATE Config SET varvalue = '0.21.0' WHERE varname = 'DB_VERSION'";
1309 break;
1310 case '0.21.1':
1311 // The query below was conditional in the 0.20.0 section, which was wrong.
1312 $query[] = "UPDATE Config SET varname = 'VLANNET_LISTSRC' WHERE varname = 'VLANIPV4NET_LISTSRC'";
1313 $query[] = "UPDATE Config SET description = 'List of VLAN-related IPv4/IPv6 networks' WHERE varname = 'VLANNET_LISTSRC'";
1314 $query[] = "ALTER TABLE ObjectHistory ADD COLUMN event_id int(10) unsigned NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (event_id)";
1315 $query[] = "DELETE FROM Atom WHERE molecule_id IS NULL OR rack_id IS NULL OR unit_no IS NULL OR atom IS NULL";
1316 // Modify the columns explicitly before the primary key addition does it implicitly.
1317 $query[] = "ALTER TABLE Atom MODIFY COLUMN molecule_id int(10) unsigned NOT NULL";
1318 $query[] = "ALTER TABLE Atom MODIFY COLUMN rack_id int(10) unsigned NOT NULL";
1319 $query[] = "ALTER TABLE Atom MODIFY COLUMN unit_no int(10) unsigned NOT NULL";
1320 $query[] = "ALTER TABLE Atom MODIFY COLUMN atom enum('front','interior','rear') NOT NULL";
1321 $query[] = "ALTER TABLE Atom ADD PRIMARY KEY (molecule_id, rack_id, unit_no, atom)";
1322 $query[] = "ALTER TABLE Atom DROP KEY `Atom-FK-molecule_id`";
1323 $query[] = "UPDATE Config SET varvalue = '0.21.1' WHERE varname = 'DB_VERSION'";
1324 break;
1325 case '0.21.2':
1326 $query[] = "ALTER TABLE MountOperation ADD UNIQUE KEY `old_molecule_id` (old_molecule_id)";
1327 $query[] = "ALTER TABLE MountOperation ADD UNIQUE KEY `new_molecule_id` (new_molecule_id)";
1328 $query[] = "ALTER TABLE MountOperation DROP KEY `MountOperation-FK-old_molecule_id`";
1329 $query[] = "ALTER TABLE MountOperation DROP KEY `MountOperation-FK-new_molecule_id`";
1330 $query[] = "ALTER TABLE IPv4Allocation MODIFY type ENUM('regular','shared','virtual','router','point2point','sharedrouter') NOT NULL DEFAULT 'regular'";
1331 $query[] = "ALTER TABLE IPv6Allocation MODIFY type ENUM('regular','shared','virtual','router','point2point','sharedrouter') NOT NULL DEFAULT 'regular'";
1332 $query[] = "INSERT INTO Chapter (`id`, `sticky`, `name`) VALUES (39,'no','UPS models')";
1333 $query[] = "INSERT INTO AttributeMap (`objtype_id`,`attr_id`,`chapter_id`) VALUES (12,2,39)"; // UPS (UPS models) -> HW type
1334 $query[] = "ALTER TABLE TagTree ADD COLUMN description char(255) DEFAULT NULL AFTER color";
1335
1336 // new iif_type SFP28
1337 $query[] = "INSERT INTO PortInnerInterface VALUES (16, 'SFP28')";
1338 $query[] = "INSERT INTO PortOuterInterface VALUES
1339 (1592, 'empty SFP28'),
1340 (1651, '25GBase-KR'),
1341 (1652, '25GBase-T'),
1342 (1653, '25GBase-CR'),
1343 (1654, '25GBase-SR'),
1344 (1655, '25GBase-LR'),
1345 (1656, '25Gbase-ER')";
1346 $query[] = "INSERT INTO PortInterfaceCompat VALUES
1347 (16,1592),(16,1651),(16,1653),(16,1654),(16,1655),(16,1656),
1348 (16,30),(16,35),(16,36),(16,37),(16,38),(16,39),(16,40)";
1349 $query[] = "INSERT INTO PortCompat VALUES
1350 (1592, 1592),
1351 (1592, 1084),
1352 (1084, 1592),
1353 (1651, 1651),
1354 (1652, 1652),
1355 (1653, 1653),
1356 (1654, 1654),
1357 (1655, 1655),
1358 (1656, 1656)";
1359 $query[] = "UPDATE Config SET varvalue = CONCAT(varvalue, '; 16=1592')
1360 WHERE varname = 'DEFAULT_PORT_OIF_IDS' AND 0 = INSTR(varvalue, '16=')";
1361 $query[] = "UPDATE Config SET varname = 'VIRTUAL_OBJ_CSV' WHERE varname = 'VIRTUAL_OBJ_LISTSRC'";
1362 $query[] = "UPDATE Config SET varvalue = '0.21.2' WHERE varname = 'DB_VERSION'";
1363 break;
1364 case '0.21.3':
1365 $query[] = "INSERT INTO Config VALUES ('OBJECTLOG_PREVIEW_ENTRIES','5','uint','no','no','yes','Object log preview maximum entries (0 disables the preview)')";
1366 $query[] = "UPDATE Config SET varvalue = '0.21.3' WHERE varname = 'DB_VERSION'";
1367 break;
1368 case '0.21.4':
1369 $query[] = "UPDATE Config SET varvalue = '0.21.4' WHERE varname = 'DB_VERSION'";
1370 break;
1371 case '0.21.5':
1372 $query[] = "UPDATE Config SET varvalue = '0.21.5' WHERE varname = 'DB_VERSION'";
1373 break;
1374 case '0.22.0':
1375 $query[] = "UPDATE Config SET varvalue = '0.22.0' WHERE varname = 'DB_VERSION'";
1376 break;
1377 case 'dictionary':
1378 $query = reloadDictionary();
1379 break;
1380 default:
1381 return NULL;
1382 }
1383 return $query;
1384 }
1385
1386 function executeUpgradeBatch ($batchid)
1387 {
1388 global $dbxlink;
1389 $query = getUpgradeBatch($batchid);
1390 if ($query === NULL)
1391 {
1392 showError ("unknown batch '${batchid}'", __FUNCTION__);
1393 die;
1394 }
1395 $failures = array();
1396 echo "<tr><th>Executing batch '${batchid}'</th><td>";
1397 foreach ($query as $q)
1398 {
1399 try
1400 {
1401 $result = $dbxlink->query ($q);
1402 }
1403 catch (PDOException $e)
1404 {
1405 $errorInfo = $dbxlink->errorInfo();
1406 $failures[] = array ($q, $errorInfo[2]);
1407 }
1408 }
1409 if (!count ($failures))
1410 echo "<strong><font color=green>done</font></strong>";
1411 else
1412 {
1413 echo "<strong><font color=red>The following queries failed:</font></strong><br><pre>";
1414 foreach ($failures as $f)
1415 {
1416 list ($q, $i) = $f;
1417 echo "${q} -- ${i}\n";
1418 }
1419 echo "</pre>";
1420 }
1421 echo '</td></tr>';
1422 }
1423
1424 function authenticate_admin ($username, $password)
1425 {
1426 global $dbxlink;
1427 $prepared = $dbxlink->prepare ('SELECT COUNT(*) FROM UserAccount WHERE user_id=1 AND user_name=? AND user_password_hash=?');
1428 if (!$prepared->execute (array ($username, sha1 ($password))))
1429 die ('SQL query failed in ' . __FUNCTION__);
1430 $rows = $prepared->fetchAll (PDO::FETCH_NUM);
1431 return $rows[0][0] == 1;
1432 }
1433
1434 // Database version detector. Should behave corretly on any
1435 // working dataset a user might have.
1436 function getDatabaseVersion ()
1437 {
1438 global $dbxlink;
1439 $prepared = $dbxlink->prepare ('SELECT varvalue FROM Config WHERE varname = "DB_VERSION" and vartype = "string"');
1440 if (! $prepared->execute())
1441 {
1442 $errorInfo = $dbxlink->errorInfo();
1443 die (__FUNCTION__ . ': SQL query failed with error ' . $errorInfo[2]);
1444 }
1445 $rows = $prepared->fetchAll (PDO::FETCH_NUM);
1446 if (count ($rows) != 1 || $rows[0][0] == '')
1447 die (__FUNCTION__ . ': Cannot guess database version. Config table is present, but DB_VERSION is missing or invalid. Giving up.');
1448 $ret = $rows[0][0];
1449 return $ret;
1450 }
1451
1452 function showUpgradeError ($info = '', $location = 'N/A')
1453 {
1454 if (preg_match ('/\.php$/', $location))
1455 $location = basename ($location);
1456 elseif ($location != 'N/A')
1457 $location = $location . '()';
1458 echo "<div class=msg_error>An error has occurred in [${location}]. ";
1459 if ($info == '')
1460 echo 'No additional information is available.';
1461 else
1462 echo "Additional information:<br><p>\n<pre>\n${info}\n</pre></p>";
1463 echo "Go back or try starting from <a href='index.php'>index page</a>.<br></div>\n";
1464 }
1465
1466 // changes the value of config variable. If $old_value_filter is set, value is changed only if current value equals to it.
1467 function replaceConfigVarValue ($varname, $new_value, $old_value_filter = NULL)
1468 {
1469 global $dbxlink;
1470 if (isset ($old_value_filter))
1471 {
1472 $result = $dbxlink->prepare ("SELECT varvalue FROM Config WHERE varname = ?");
1473 $result->execute (array ($varname));
1474 if ($row = $result->fetch (PDO::FETCH_ASSOC))
1475 if ($row['varvalue'] != $old_value_filter)
1476 return;
1477 unset ($result);
1478 }
1479 $result = $dbxlink->prepare ("UPDATE Config set varvalue = ? WHERE varname = ?");
1480 $result->execute (array ($new_value, $varname));
1481 }
1482
1483 function renderUpgraderHTML()
1484 {
1485 global $found_secret_file;
1486 if (! $found_secret_file)
1487 die ('<center>There is no working RackTables instance here, <a href="?module=installer">install</a>?</center>');
1488
1489 try
1490 {
1491 connectDB();
1492 }
1493 catch (RackTablesError $e)
1494 {
1495 die ("Database connection failed:\n\n" . $e->getMessage());
1496 }
1497
1498 if
1499 (
1500 ! isset ($_SERVER['PHP_AUTH_USER']) ||
1501 $_SERVER['PHP_AUTH_USER'] == '' ||
1502 ! isset ($_SERVER['PHP_AUTH_PW']) ||
1503 $_SERVER['PHP_AUTH_PW'] == '' ||
1504 !authenticate_admin ($_SERVER['PHP_AUTH_USER'], $_SERVER['PHP_AUTH_PW'])
1505 )
1506 {
1507 header ('WWW-Authenticate: Basic realm="RackTables upgrade"');
1508 header ('HTTP/1.0 401 Unauthorized');
1509 echo <<<'ENDOFTEXT'
1510 <h1>Trouble logging in?</h1>
1511 You are trying to authenticate for the RackTables upgrade screen. This means that
1512 you must authenticate with the username and password of the main RackTables
1513 administrator. There is only one such account in each installation, its default
1514 username is "admin". RackTables wiki provides more information on this topic.
1515 ENDOFTEXT;
1516 die;
1517 }
1518
1519 echo <<<'ENDOFTEXT'
1520 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
1521 <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
1522 <head><title>RackTables upgrade script</title>
1523 <style type="text/css">
1524 .tdleft {
1525 text-align: left;
1526 }
1527
1528 .trok {
1529 background-color: #80FF80;
1530 }
1531
1532 .trwarning {
1533 background-color: #FFFF80;
1534 }
1535
1536 .trerror {
1537 background-color: #FF8080;
1538 }
1539 </style>
1540 </head>
1541 <body>
1542 <h1>Platform check status</h1>
1543 ENDOFTEXT;
1544
1545 if (! platform_is_ok (TRUE))
1546 {
1547 echo '<h1>Please resolve the failed (red) item(s) above.</h1>';
1548 die ('</body></html>');
1549 }
1550
1551 echo '<h1>Upgrade status</h1>';
1552 global $dbver;
1553 $dbver = getDatabaseVersion();
1554 echo '<table border=1 cellpadding=5>';
1555 echo "<tr><th>Current status</th><td>Data version: ${dbver}<br>Code version: " . CODE_VERSION . "</td></tr>\n";
1556
1557 $path = getDBUpgradePath ($dbver, CODE_VERSION);
1558 if ($path === NULL)
1559 {
1560 echo "<tr><th>Upgrade path</th><td><font color=red>not found</font></td></tr>\n";
1561 echo "<tr><th>Summary</th><td>Check README for more information. RackTables releases prior to 0.18.0 ";
1562 echo "must be upgraded to 0.18.0 first. RackTables releases prior to 0.20.0 must be upgraded to 0.20.0 first.</td></tr>\n";
1563 }
1564 else
1565 {
1566 if (!count ($path))
1567 echo "<tr><th>Summary</th><td>Come back later.</td></tr>\n";
1568 else
1569 {
1570 echo "<tr><th>Upgrade path</th><td>${dbver} &rarr; " . implode (' &rarr; ', $path) . "</td></tr>\n";
1571 global $relnotes;
1572 foreach ($path as $batchid)
1573 if (isset ($relnotes[$batchid]))
1574 echo "<tr><th>Release notes for ${batchid}</th><td><pre>" . $relnotes[$batchid] . "</pre></td></tr>\n";
1575 if (array_key_exists ('reallyreally', $_REQUEST))
1576 {
1577 foreach ($path as $batchid)
1578 executeUpgradeBatch ($batchid);
1579 executeUpgradeBatch ('dictionary');
1580 echo "<tr><th>Summary</th><td>Upgrade complete, it is Ok to ";
1581 echo "<a href='index.php'>enter</a> the system.</td></tr>\n";
1582 }
1583 else
1584 {
1585 echo '<form method=post action="index.php?module=upgrade"><tr><th>Wait!</th>';
1586 echo '<td><p>RackTables database upgrades sometimes go wrong because of assorted reasons. ';
1587 echo 'It is <strong>highly recommended</strong> to make a database backup before ';
1588 echo 'proceeding any further. <tt>mysqldump</tt> and <tt>PHPMyAdmin</tt> are convenient ';
1589 echo 'tools for doing this.</p>';
1590 echo '<p><label><input type=checkbox name=reallyreally>';
1591 echo 'I am ready to bear all risks of this upgrade. I am ready to roll it back in case of ';
1592 echo 'a failure.</label> <input type=submit value="Yes, I am."></p></td></tr></form>';
1593 }
1594 }
1595 }
1596 echo '</table>';
1597 echo '</body></html>';
1598 }
1599
1600 // returns SQL query to make PortCompat symmetric (insert missing reversed-order pairs).
1601 // It should be called each time after the PortCompat table pairs being added during upgrade.
1602 function extendPortCompatQuery()
1603 {
1604 return "INSERT INTO PortCompat SELECT pc1.type2, pc1.type1 FROM PortCompat pc1 LEFT JOIN PortCompat pc2 ON pc1.type1 = pc2.type2 AND pc1.type2 = pc2.type1 WHERE pc2.type1 IS NULL";
1605 }
1606
1607 function convertSLBTablesToBinIPs()
1608 {
1609 global $dbxlink;
1610
1611 $dbxlink->query ("DROP TABLE IF EXISTS `IPv4VS_new`, `IPv4RS_new`, `IPv4VS_old`, `IPv4RS_old`");
1612
1613 $dbxlink->query (<<<'END'
1614 CREATE TABLE `IPv4VS_new` (
1615 `id` int(10) unsigned NOT NULL auto_increment,
1616 `vip` varbinary(16) NOT NULL,
1617 `vport` smallint(5) unsigned default NULL,
1618 `proto` enum('TCP','UDP','MARK') NOT NULL default 'TCP',
1619 `name` char(255) default NULL,
1620 `vsconfig` text,
1621 `rsconfig` text,
1622 PRIMARY KEY (`id`),
1623 KEY `vip` (`vip`)
1624 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1625 END
1626 );
1627 $result = $dbxlink->query ("SELECT id, vip, vport, proto, name, vsconfig, rsconfig FROM IPv4VS");
1628 $rows = $result->fetchAll (PDO::FETCH_ASSOC);
1629 unset ($result);
1630 foreach ($rows as $row)
1631 {
1632 $row['vip'] = ip4_int2bin ($row['vip']);
1633 usePreparedInsertBlade ('IPv4VS_new', $row);
1634 }
1635
1636 $dbxlink->query (<<<'END'
1637 CREATE TABLE `IPv4RS_new` (
1638 `id` int(10) unsigned NOT NULL auto_increment,
1639 `inservice` enum('yes','no') NOT NULL default 'no',
1640 `rsip` varbinary(16) NOT NULL,
1641 `rsport` smallint(5) unsigned default NULL,
1642 `rspool_id` int(10) unsigned default NULL,
1643 `rsconfig` text,
1644 `comment` varchar(255) DEFAULT NULL,
1645 PRIMARY KEY (`id`),
1646 KEY `rsip` (`rsip`),
1647 UNIQUE KEY `pool-endpoint` (`rspool_id`,`rsip`,`rsport`),
1648 CONSTRAINT `IPRS-FK` FOREIGN KEY (`rspool_id`) REFERENCES `IPv4RSPool` (`id`) ON DELETE CASCADE
1649 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1650 END
1651 );
1652 $result = $dbxlink->query ("SELECT id, inservice, rsip, rsport, rspool_id, rsconfig FROM IPv4RS");
1653 $rows = $result->fetchAll (PDO::FETCH_ASSOC);
1654 unset ($result);
1655 foreach ($rows as $row)
1656 {
1657 $row['rsip'] = ip4_int2bin ($row['rsip']);
1658 usePreparedInsertBlade ('IPv4RS_new', $row);
1659 }
1660
1661 $dbxlink->query (<<<'END'
1662 RENAME TABLE
1663 `IPv4VS` TO `IPv4VS_old`,
1664 `IPv4VS_new` TO `IPv4VS`,
1665 `IPv4RS` TO `IPv4RS_old`,
1666 `IPv4RS_new` TO `IPv4RS`
1667 END
1668 );
1669 // re-create foreign key in IPv4LB
1670 $dbxlink->query ("ALTER TABLE `IPv4LB` DROP FOREIGN KEY `IPv4LB-FK-vs_id`");
1671 $dbxlink->query ("ALTER TABLE `IPv4LB` ADD CONSTRAINT `IPv4LB-FK-vs_id` FOREIGN KEY (`vs_id`) REFERENCES `IPv4VS` (`id`)");
1672
1673 $dbxlink->query ("DROP TABLE `IPv4VS_old`, `IPv4RS_old`");
1674
1675 // re-create foreign key in IPv4RS
1676 $dbxlink->query ("ALTER TABLE `IPv4RS` DROP FOREIGN KEY `IPRS-FK`");
1677 $dbxlink->query ("ALTER TABLE `IPv4RS` ADD CONSTRAINT `IPv4RS-FK` FOREIGN KEY (`rspool_id`) REFERENCES `IPv4RSPool` (`id`) ON DELETE CASCADE");
1678 }
1679
1680 // converts the values of old-style config vars TELNET_OBJS_LISTSRC, SSH_OBJS_LISTSRC, RDP_OBJS_LISTSRC
1681 // to the format of MGMT_PROTOS (comma-separated list of "proto: rackcode" pairs)
1682 function convertMgmtConfigVars()
1683 {
1684 global $dbxlink;
1685 $ret = array();
1686 foreach (array ('telnet' => 'TELNET_OBJS_LISTSRC', 'ssh' => 'SSH_OBJS_LISTSRC', 'rdp' => 'RDP_OBJS_LISTSRC') as $proto => $varname)
1687 {
1688 $result = $dbxlink->prepare ("SELECT varvalue FROM Config WHERE varname = ?");
1689 $result->execute (array ($varname));
1690 if ($row = $result->fetch (PDO::FETCH_ASSOC))
1691 if ($row['varvalue'] != 'false' && $row['varvalue'] != '')
1692 $ret[] = "$proto: " . $row['varvalue'];
1693 unset ($result);
1694 }
1695 return implode (',', $ret);
1696 }