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