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