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