generalize IIF/OIF compatibility viewer and editor
[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(
1679ddbd
DO
9 '0.18.2' => <<<ENDOFTEXT
10RackTables from its version 0.18.0 and later is not compatible with
11RHEL/CentOS (at least with versions up to 5.5) Linux distributions
12in their default installation. There are yet options to work around that:
131. Install RackTables on a server with a different distribution/OS.
142. Request Linux distribution vendor to fix the bug with PCRE.
153. Repair your RHEL/CentOS installation yourself by fixing its PCRE
16RPM as explained here: http://bugs.centos.org/view.php?id=3252
17ENDOFTEXT
9fb6900d
DO
18,
19 '0.19.0' => <<<ENDOFTEXT
841a5b54
DO
20The files, which are intended for the httpd (web-server) directory, are
21now in the "wwwroot" directory of the tar.gz archive. Files outside of
22that directory are not directly intended for httpd environment and should
23not be copied to the server.
9fb6900d 24
8489d2af
DO
25This release incorporates ObjectLog functionality, which used to be
26available as a separate plugin. For the best results it is advised to
27disable (through local.php) external ObjectLog plugin permanently before
28the new version is installed. All previously accumulated ObjectLog records
29will be available through the updated standard interface.
30
841a5b54
DO
31RackTables is now using PHP JSON extension which is included in the PHP
32core since 5.2.0.
33
34The barcode attribute was removed. The upgrade script attempts to
35preserve the data by moving it to either the 'OEM S/N 1' attribute or to
36a Log entry. You should backup your database beforehand anyway.
9fb6900d 37ENDOFTEXT
36efe434
DO
38,
39 '0.19.2' => <<<ENDOFTEXT
40This release is different in filesystem layout. The "gateways" directory
4f5aea8a
AA
41has been moved from "wwwroot" directory. This improves security a bit.
42You can also separate your local settings and add-ons from the core RackTables code.
43To do that, put a single index.php file into the DocumentRoot of your http server:
36efe434 44
4f5aea8a
AA
45<?php
46\$racktables_confdir='/directory/where/your/secret.php/and/local.php/files/are/stored';
47require '/directory_where_you_extracted_racktables_distro/wwwroot/index.php';
48?>
49
50No more files are needed to be available directly over the HTTP.
51Full list of filesystem paths which could be specified in custom index.php or secret.php:
52 \$racktables_gwdir: path to the gateways directory;
53 \$racktables_staticdir: path to the directory containing 'pix', 'js', 'css' dirs;
54 \$racktables_confdir: path where secret.php and local.php are located. It is not
55 recommended to define it in secret.php, cause only the path to
56 local.php will be affected;
57 \$path_to_secret_php: Ignore \$racktables_confdir when locating secret.php and use
58 the specified path;
59 \$path_to_local_php: idem for local.php.
36efe434 60ENDOFTEXT
5ae6d365 61,
86ff26ae
DO
62
63 '0.19.13' => <<<ENDOFTEXT
64A new "date" attribute type has been added. Existing date based fields ("HW warranty expiration",
65"support contract expiration" and "SW warranty expiration") will be converted to this new type but
66must be in the format "mm/dd/yyyy" otherwise the conversion will fail.
67ENDOFTEXT
dd14756b
AA
68,
69
70 '0.20.0' => <<<ENDOFTEXT
90bf1755
DO
71WARNING: This release have too many internal changes, some of them were waiting more than a year
72to be released. So this release is considered "BETA" and is recommended only to curiuos users,
73who agree to sacrifice the stability to the progress.
74
d1a9e5cf
AA
75Racks and Rows are now stored in the database as Objects. The RackObject table
76was renamed to Object. SQL views were created to ease the migration of custom
dd14756b
AA
77reports and scripts.
78
79New plugins engine instead of local.php file. To make your own code stored in local.php work,
80you must move the local.php file into the plugins/ directory. The name of this file does not
81matter any more. You also can store multiple files in that dir, separate your plugins by features,
82share them and try the plugins from other people just placing them into plugins/ dir, no more merging.
d1a9e5cf 83\$path_to_local_php variable has no special meaning any more.
dd14756b
AA
84\$racktables_confdir variable is now used only to search for secret.php file.
85\$racktables_plugins_dir is a new overridable special variable pointing to plugins/ directory.
86
d1a9e5cf
AA
87Beginning with this version it is possible to delete IP prefixes, VLANs, Virtual services
88and RS pools from within theirs properties tab. So please inspect your permissions rules
89to assure there are no undesired allows for deletion of these objects. To ensure this, you could
90try this code in the beginning of permissions script:
91
92 allow {userid_1} and {\$op_del}
93 deny {\$op_del} and ({\$tab_edit} or {\$tab_properties})
94
95Hardware gateways engine was rewritten in this version of RackTables. This means that
96the file gateways/deviceconfig/switch.secrets.php is not used any more. To get information
97about configuring connection properties and credentials in a new way please visit
98http://wiki.racktables.org/index.php/Gateways
99
100This also means that recently added features based on old API (D-Link switches and Linux
101gateway support contributed by Ilya Evseev) are not working any more and waiting to be
102forward-ported to new gateways API. Sorry for that.
103
104Two new config variables appeared in this version:
105 - SEARCH_DOMAINS. Comma-separated list of DNS domains which are considered "base" for your
106 network. If RackTables search engine finds multiple objects based on your search input, but
107 there is only one which FQDN consists of your input and one of these search domains, you will
108 be redirected to this object and other results will be discarded. Such behavior was unconditional
109 since 0.19.3, which caused many objections from users. So welcome this config var.
110 - QUICK_LINK_PAGES. Comma-separated list of RackTables pages to display links to them on top.
111 Each user could have his own list.
112
113Also some of config variables have changed their default values in this version.
114This means that upgrade script will change their values if you have them in previous default state.
115This could be inconvenient, but it is the most effective way to encourage users to use new features.
116If this behavior is not what you want, simply revert these variables' values:
117 - SHOW_LAST_TAB no => yes
118 - IPV4_TREE_SHOW_USAGE yes =>no (networks' usage is still available by click)
119 - IPV4LB_LISTSRC {\$typeid_4} => false
120 - FILTER_DEFAULT_ANDOR or => and (this implicitly enables the feature of dynamic tree shrinking)
121 - FILTER_SUGGEST_EXTRA no => yes (yes, we have extra logical filters!)
122 - IPV4_TREE_RTR_AS_CELL yes => no (display routers as simple text, not cell)
123
124Also please note that variable IPV4_TREE_RTR_AS_CELL now has third special value
125besides 'yes' and 'no': 'none'. Use 'none' value if you are experiencing low performance
126on IP tree page. It will completely disable IP ranges scan for used/spare IPs and the
127speed of IP tree will increase radically. The price is you will not see the routers in
128IP tree at all.
129
dd14756b 130ENDOFTEXT
038739ac
AA
131,
132
133 '0.20.1' => <<<ENDOFTEXT
d76549bc
AA
134The 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.
135
038739ac
AA
136Security 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:
137 allow {client network} and {New York}
138This 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:
139 allow {client network} and {New York} and not {\$page_ipaddress}
86ff26ae 140
038739ac 141ENDOFTEXT
91c42724
DO
142,
143
144 '0.20.4' => <<<ENDOFTEXT
145Please note that some dictionary items of Cisco Catalyst 2960 series switches
146were renamed to meet official Cisco classification:
147 2960-48TT => 2960-48TT-L
148 2960-24TC => 2960-24TC-L
149 2960-24TT => 2960-24TT-L
150 2960-8TC => 2960-8TC-L
151 2960G-48TC => 2960G-48TC-L
152 2960G-24TC => 2960G-24TC-L
153 2960G-8TC => 2960G-8TC-L
154 C2960-24 => C2960-24-S
155 C2960G-24PC => C2960-24PC-L
2a943f85
DO
156
157The DATETIME_FORMAT configuration option used in setting date and time output
158format now uses a different [1] syntax. During upgrade the option is reset to
159the default value, which is now %Y-%m-%d (YYYY-MM-DD) per ISO 8601.
160
a7192a34
AA
161This release intoduces two new configuration options:
162REVERSED_RACKS_LISTSRC and NEAREST_RACKS_CHECKBOX.
163
2a943f85 164[1] http://php.net/manual/en/function.strftime.php
91c42724 165ENDOFTEXT
038739ac 166,
eacc0983
AA
167
168 '0.20.5' => <<<ENDOFTEXT
169This release introduces the VS groups feature. VS groups is a new way to store
b8433971
DO
170and display virtual services configuration. There is a new "ipvs" (VS group)
171realm. All previously existing VS configuration remains functional and user
172is free to convert it to the new format, which displays it in a more natural way
173and allows to generate virtual_server_group keepalived configs. To convert a
174virtual service to the new format, it is necessary to manually create a VS group
175object and assign IP addresses to it. The VS group will display a "Migrate" tab
176to convert the old-style VS objects, which can be removed after a successful
177conversion.
178
179The old-style VS configuration becomes DEPRECATED. Its support will be removed
180in a future major release. So it is strongly recommended to convert it to the
181new format.
eacc0983
AA
182ENDOFTEXT
183,
184
6a40d076 185 '0.20.6' => <<<ENDOFTEXT
b8433971
DO
186New MGMT_PROTOS configuration option replaces the TELNET_OBJS_LISTSRC,
187SSH_OBJS_LISTSRC and RDP_OBJS_LISTSRC options (converting existing settings as
188necessary). MGMT_PROTOS allows to specify any management protocol for a
189particular device list using a RackCode filter. The default value
190("ssh: {\$typeid_4}, telnet: {\$typeid_8}") produces "ssh://server.fqdn" for
191servers and "telnet://switch.fqdn" for network switches.
6a40d076 192ENDOFTEXT
25355e1d
AD
193,
194
195 '0.20.7' => <<<ENDOFTEXT
ae9ada40
DO
196From now on the minimum (oldest) release of PHP that can run RackTables is
1975.2.10. In particular, to continue running RackTables on CentOS 5 it is
198necessary to replace its php* RPM packages with respective php53* packages
199before the upgrade (except the JSON package, which PHP 5.3 provides internally).
200
7740234b
AD
201Database triggers are used for some data consistency measures. The database
202user account must have the 'TRIGGER' privilege, which was introduced in
203MySQL 5.1.7.
204
25355e1d
AD
205The IPV4OBJ_LISTSRC configuration option is reset to an expression which enables
206the IP addressing feature for all object types except those listed.
23e52760
AA
207
208Tags could now be assigned on the Edit/Properties tab using a text input with
209auto-completion. Type a star '*' to view full tag tree in auto-complete menu.
210It is worth to add the following line to the permissions script if the
211old-fashioned 'Tags' tab is not needed any more:
212 deny {\$tab_tags} # this hides 'Tags' tab
b4bc5582
TU
213
214This release converts collation of all DB fields to the utf8_unicode_ci. This
215procedure may take some time, and could fail if there are rows that differ only
216by letter case. If this happen, you'll see the failed SQL query in upgrade report
217with the "Duplicate entry" error message. Feel free to continue using your
218installation. If desired so, you could eliminate the case-duplicating rows
219and re-apply the failed query.
25355e1d 220ENDOFTEXT
6a40d076 221,
90b96ff6
DO
222);
223
fbbb74fb
DO
224// At the moment we assume, that for any two releases we can
225// sequentally execute all batches, that separate them, and
226// nothing will break. If this changes one day, the function
227// below will have to generate smarter upgrade paths, while
228// the upper layer will remain the same.
229// Returning an empty array means that no upgrade is necessary.
4114697d 230// Returning NULL indicates an error.
fbbb74fb
DO
231function getDBUpgradePath ($v1, $v2)
232{
a6f83a72
DO
233 $versionhistory = array
234 (
9013f05b 235 '0.18.0',
425fd829 236 '0.18.1',
298d2375 237 '0.18.2',
f6d1a7cc 238 '0.18.3',
1c5b7c84 239 '0.18.4',
92ee2b01 240 '0.18.5',
2f5e4db9 241 '0.18.6',
9fb6900d 242 '0.18.7',
16825cc8 243 '0.19.0',
1d5dd3a1 244 '0.19.1',
0abae5fb 245 '0.19.2',
03d86c03 246 '0.19.3',
1f02e311 247 '0.19.4',
2a3a1a6d
AA
248 '0.19.5',
249 '0.19.6',
86eaaa67 250 '0.19.7',
f7494e3c 251 '0.19.8',
5c0bd7de 252 '0.19.9',
f7494e3c 253 '0.19.10',
ea24fb69 254 '0.19.11',
28537080 255 '0.19.12',
86ff26ae 256 '0.19.13',
b55f913c 257 '0.19.14',
f7494e3c 258 '0.20.0',
e9893a88 259 '0.20.1',
6e58c2c4 260 '0.20.2',
56a28368 261 '0.20.3',
4dcd770e 262 '0.20.4',
f9fcce59 263 '0.20.5',
ceebebb9 264 '0.20.6',
25355e1d 265 '0.20.7',
5c8f1469 266 '0.20.8',
a6f83a72 267 );
120e9ddd
DO
268 if (!in_array ($v1, $versionhistory) or !in_array ($v2, $versionhistory))
269 return NULL;
fbbb74fb 270 $skip = TRUE;
4114697d 271 $path = NULL;
154a42e5
DO
272 // foreach() below cannot handle this specific case
273 if ($v1 == $v2)
274 return array();
fbbb74fb
DO
275 // Now collect all versions > $v1 and <= $v2
276 foreach ($versionhistory as $v)
277 {
4114697d 278 if ($skip and $v == $v1)
fbbb74fb
DO
279 {
280 $skip = FALSE;
4114697d 281 $path = array();
fbbb74fb
DO
282 continue;
283 }
284 if ($skip)
285 continue;
286 $path[] = $v;
287 if ($v == $v2)
288 break;
289 }
290 return $path;
291}
292
90b96ff6
DO
293// Upgrade batches are named exactly as the release where they first appear.
294// That is simple, but seems sufficient for beginning.
87ae30c5 295function getUpgradeBatch ($batchid)
fbbb74fb
DO
296{
297 $query = array();
ca3d68bd 298 global $dbxlink;
fbbb74fb
DO
299 switch ($batchid)
300 {
9013f05b 301 case '0.18.0':
8bb69a06
DO
302 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('VLANSWITCH_LISTSRC', '', 'string', 'yes', 'no', 'yes', 'List of VLAN running switches')";
303 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('VLANIPV4NET_LISTSRC', '', 'string', 'yes', 'no', 'yes', 'List of VLAN-based IPv4 networks')";
304 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('DEFAULT_VDOM_ID','','uint','yes','no','yes','Default VLAN domain ID')";
305 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('DEFAULT_VST_ID','','uint','yes','no','yes','Default VLAN switch template ID')";
37cb9e18
DO
306 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('8021Q_DEPLOY_MINAGE','300','uint','no','no','no','802.1Q deploy minimum age')";
307 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('8021Q_DEPLOY_MAXAGE','3600','uint','no','no','no','802.1Q deploy maximum age')";
308 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('8021Q_DEPLOY_RETRY','10800','uint','no','no','no','802.1Q deploy retry timer')";
407ed7bb 309 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('8021Q_WRI_AFTER_CONFT','no','string','no','no','no','802.1Q: save device configuration after deploy')";
4492050b 310 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('8021Q_INSTANT_DEPLOY','no','string','no','no','yes','802.1Q: instant deploy')";
97c0a54e 311 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('IPV4_TREE_SHOW_VLAN','yes','string','no','no','yes','Show VLAN for each network in IPv4 tree')";
1768cc35 312 $query[] = "ALTER TABLE IPv4Network ENGINE=InnoDB";
22fdebff
DO
313 $query[] = "SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0";
314 $query[] = "
315CREATE TABLE `CachedPAV` (
316 `object_id` int(10) unsigned NOT NULL,
317 `port_name` char(255) NOT NULL,
318 `vlan_id` int(10) unsigned NOT NULL default '0',
319 PRIMARY KEY (`object_id`,`port_name`,`vlan_id`),
320 KEY `vlan_id` (`vlan_id`),
321 CONSTRAINT `CachedPAV-FK-object-port` FOREIGN KEY (`object_id`, `port_name`) REFERENCES `CachedPVM` (`object_id`, `port_name`) ON DELETE CASCADE,
322 CONSTRAINT `CachedPAV-FK-vlan_id` FOREIGN KEY (`vlan_id`) REFERENCES `VLANValidID` (`vlan_id`)
323) ENGINE=InnoDB
324";
325 $query[] = "
326CREATE TABLE `CachedPNV` (
327 `object_id` int(10) unsigned NOT NULL,
328 `port_name` char(255) NOT NULL,
329 `vlan_id` int(10) unsigned NOT NULL default '0',
330 PRIMARY KEY (`object_id`,`port_name`,`vlan_id`),
331 UNIQUE KEY `port_id` (`object_id`,`port_name`),
332 CONSTRAINT `CachedPNV-FK-compound` FOREIGN KEY (`object_id`, `port_name`, `vlan_id`) REFERENCES `CachedPAV` (`object_id`, `port_name`, `vlan_id`) ON DELETE CASCADE
333) ENGINE=InnoDB
334";
335 $query[] = "
336CREATE TABLE `CachedPVM` (
337 `object_id` int(10) unsigned NOT NULL,
338 `port_name` char(255) NOT NULL,
339 `vlan_mode` enum('access','trunk') NOT NULL default 'access',
340 PRIMARY KEY (`object_id`,`port_name`),
341 CONSTRAINT `CachedPVM-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE
342) ENGINE=InnoDB
343";
344 $query[] = "
345CREATE TABLE `PortAllowedVLAN` (
346 `object_id` int(10) unsigned NOT NULL,
347 `port_name` char(255) NOT NULL,
348 `vlan_id` int(10) unsigned NOT NULL default '0',
349 PRIMARY KEY (`object_id`,`port_name`,`vlan_id`),
350 KEY `vlan_id` (`vlan_id`),
351 CONSTRAINT `PortAllowedVLAN-FK-object-port` FOREIGN KEY (`object_id`, `port_name`) REFERENCES `PortVLANMode` (`object_id`, `port_name`) ON DELETE CASCADE,
352 CONSTRAINT `PortAllowedVLAN-FK-vlan_id` FOREIGN KEY (`vlan_id`) REFERENCES `VLANValidID` (`vlan_id`)
353) ENGINE=InnoDB
354";
355 $query[] = "
356CREATE TABLE `PortNativeVLAN` (
357 `object_id` int(10) unsigned NOT NULL,
358 `port_name` char(255) NOT NULL,
359 `vlan_id` int(10) unsigned NOT NULL default '0',
360 PRIMARY KEY (`object_id`,`port_name`,`vlan_id`),
361 UNIQUE KEY `port_id` (`object_id`,`port_name`),
362 CONSTRAINT `PortNativeVLAN-FK-compound` FOREIGN KEY (`object_id`, `port_name`, `vlan_id`) REFERENCES `PortAllowedVLAN` (`object_id`, `port_name`, `vlan_id`) ON DELETE CASCADE
363) ENGINE=InnoDB
364";
365 $query[] = "
366CREATE TABLE `PortVLANMode` (
367 `object_id` int(10) unsigned NOT NULL,
368 `port_name` char(255) NOT NULL,
369 `vlan_mode` enum('access','trunk') NOT NULL default 'access',
370 PRIMARY KEY (`object_id`,`port_name`),
371 CONSTRAINT `PortVLANMode-FK-object-port` FOREIGN KEY (`object_id`, `port_name`) REFERENCES `CachedPVM` (`object_id`, `port_name`)
372) ENGINE=InnoDB
373";
374 $query[] = "
375CREATE TABLE `VLANDescription` (
376 `domain_id` int(10) unsigned NOT NULL,
377 `vlan_id` int(10) unsigned NOT NULL default '0',
378 `vlan_type` enum('ondemand','compulsory','alien') NOT NULL default 'ondemand',
379 `vlan_descr` char(255) default NULL,
380 PRIMARY KEY (`domain_id`,`vlan_id`),
381 KEY `vlan_id` (`vlan_id`),
219da133 382 CONSTRAINT `VLANDescription-FK-domain_id` FOREIGN KEY (`domain_id`) REFERENCES `VLANDomain` (`id`) ON DELETE CASCADE,
22fdebff
DO
383 CONSTRAINT `VLANDescription-FK-vlan_id` FOREIGN KEY (`vlan_id`) REFERENCES `VLANValidID` (`vlan_id`)
384) ENGINE=InnoDB
385";
386 $query[] = "
387CREATE TABLE `VLANDomain` (
388 `id` int(10) unsigned NOT NULL auto_increment,
389 `description` char(255) default NULL,
390 PRIMARY KEY (`id`),
391 UNIQUE KEY `description` (`description`)
392) ENGINE=InnoDB
393";
394 $query[] = "
395CREATE TABLE `VLANIPv4` (
396 `domain_id` int(10) unsigned NOT NULL,
397 `vlan_id` int(10) unsigned NOT NULL,
398 `ipv4net_id` int(10) unsigned NOT NULL,
399 UNIQUE KEY `network-domain` (`ipv4net_id`,`domain_id`),
400 KEY `VLANIPv4-FK-compound` (`domain_id`,`vlan_id`),
401 CONSTRAINT `VLANIPv4-FK-compound` FOREIGN KEY (`domain_id`, `vlan_id`) REFERENCES `VLANDescription` (`domain_id`, `vlan_id`) ON DELETE CASCADE,
402 CONSTRAINT `VLANIPv4-FK-ipv4net_id` FOREIGN KEY (`ipv4net_id`) REFERENCES `IPv4Network` (`id`) ON DELETE CASCADE
403) ENGINE=InnoDB
404";
405 $query[] = "
406CREATE TABLE `VLANSTRule` (
407 `vst_id` int(10) unsigned NOT NULL,
408 `rule_no` int(10) unsigned NOT NULL,
409 `port_pcre` char(255) NOT NULL,
410 `port_role` enum('access','trunk','uplink','downlink','none') NOT NULL default 'none',
411 `wrt_vlans` char(255) default NULL,
412 `description` char(255) default NULL,
413 UNIQUE KEY `vst-rule` (`vst_id`,`rule_no`),
414 CONSTRAINT `VLANSTRule-FK-vst_id` FOREIGN KEY (`vst_id`) REFERENCES `VLANSwitchTemplate` (`id`) ON DELETE CASCADE
415) ENGINE=InnoDB
416";
417 $query[] = "
418CREATE TABLE `VLANSwitch` (
419 `object_id` int(10) unsigned NOT NULL,
420 `domain_id` int(10) unsigned NOT NULL,
421 `template_id` int(10) unsigned NOT NULL,
422 `mutex_rev` int(10) unsigned NOT NULL default '0',
423 `out_of_sync` enum('yes','no') NOT NULL default 'yes',
424 `last_errno` int(10) unsigned NOT NULL default '0',
425 `last_change` timestamp NOT NULL default '0000-00-00 00:00:00',
426 `last_push_started` timestamp NOT NULL default '0000-00-00 00:00:00',
427 `last_push_finished` timestamp NOT NULL default '0000-00-00 00:00:00',
428 `last_error_ts` timestamp NOT NULL default '0000-00-00 00:00:00',
429 UNIQUE KEY `object_id` (`object_id`),
430 KEY `domain_id` (`domain_id`),
431 KEY `template_id` (`template_id`),
432 KEY `out_of_sync` (`out_of_sync`),
433 KEY `last_errno` (`last_errno`),
434 CONSTRAINT `VLANSwitch-FK-domain_id` FOREIGN KEY (`domain_id`) REFERENCES `VLANDomain` (`id`),
435 CONSTRAINT `VLANSwitch-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`),
436 CONSTRAINT `VLANSwitch-FK-template_id` FOREIGN KEY (`template_id`) REFERENCES `VLANSwitchTemplate` (`id`)
437) ENGINE=InnoDB
438";
439 $query[] = "
440CREATE TABLE `VLANSwitchTemplate` (
441 `id` int(10) unsigned NOT NULL auto_increment,
442 `max_local_vlans` int(10) unsigned default NULL,
443 `description` char(255) default NULL,
444 PRIMARY KEY (`id`),
445 UNIQUE KEY `description` (`description`)
446) ENGINE=InnoDB
447";
448 $query[] = "
449CREATE TABLE `VLANValidID` (
450 `vlan_id` int(10) unsigned NOT NULL default '1',
451 PRIMARY KEY (`vlan_id`)
452) ENGINE=InnoDB
453";
454 $query[] = "SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS";
fa11e5c7 455 for ($i = 1; $i <= 4094; $i++)
a401a680 456 $query[] = "INSERT INTO VLANValidID (vlan_id) VALUES (${i})";
9013f05b 457 $query[] = "UPDATE Config SET varvalue = '0.18.0' WHERE varname = 'DB_VERSION'";
08d1ef24 458 break;
425fd829 459 case '0.18.1':
b6978d62
DO
460 $query[] = "ALTER TABLE Atom ENGINE=InnoDB";
461 $query[] = "ALTER TABLE AttributeMap ENGINE=InnoDB";
462 $query[] = "ALTER TABLE Config ENGINE=InnoDB";
463 $query[] = "ALTER TABLE IPv4Address ENGINE=InnoDB";
464 $query[] = "ALTER TABLE IPv4Allocation ENGINE=InnoDB";
465 $query[] = "ALTER TABLE Molecule ENGINE=InnoDB";
466 $query[] = "ALTER TABLE MountOperation ENGINE=InnoDB";
467 $query[] = "ALTER TABLE PortCompat ENGINE=InnoDB";
468 $query[] = "ALTER TABLE Rack ENGINE=InnoDB";
469 $query[] = "ALTER TABLE RackHistory ENGINE=InnoDB";
470 $query[] = "ALTER TABLE RackObjectHistory ENGINE=InnoDB";
471 $query[] = "ALTER TABLE RackRow ENGINE=InnoDB";
472 $query[] = "ALTER TABLE RackSpace ENGINE=InnoDB";
473 $query[] = "ALTER TABLE Script ENGINE=InnoDB";
735f169f
DO
474 $query[] = "ALTER TABLE AttributeValue DROP FOREIGN KEY `AttributeValue-FK-object_id`";
475 $query[] = "ALTER TABLE AttributeValue ADD CONSTRAINT `AttributeValue-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE";
55267f67
DO
476 $query[] = "ALTER TABLE RackObjectHistory ADD KEY (id)";
477 $query[] = "ALTER TABLE RackObjectHistory ADD CONSTRAINT `RackObjectHistory-FK-object_id` FOREIGN KEY (id) REFERENCES `RackObject` (`id`) ON DELETE CASCADE";
f3552988 478 $query[] = "ALTER TABLE MountOperation ADD CONSTRAINT `MountOperation-FK-object_id` FOREIGN KEY (object_id) REFERENCES RackObject (id) ON DELETE CASCADE";
f7cec175 479 $query[] = "ALTER TABLE RackSpace ADD CONSTRAINT `RackSpace-FK-object_id` FOREIGN KEY (object_id) REFERENCES RackObject (id) ON DELETE CASCADE";
1bcfe894
DO
480 $query[] = "ALTER TABLE Link DROP FOREIGN KEY `Link-FK-a`";
481 $query[] = "ALTER TABLE Link ADD CONSTRAINT `Link-FK-a` FOREIGN KEY (`porta`) REFERENCES `Port` (`id`) ON DELETE CASCADE";
482 $query[] = "ALTER TABLE Link DROP FOREIGN KEY `Link-FK-b`";
483 $query[] = "ALTER TABLE Link ADD CONSTRAINT `Link-FK-b` FOREIGN KEY (`portb`) REFERENCES `Port` (`id`) ON DELETE CASCADE";
484 $query[] = "ALTER TABLE Port DROP FOREIGN KEY `Port-FK-object_id`";
485 $query[] = "ALTER TABLE Port ADD CONSTRAINT `Port-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE";
e7787961
DO
486 $query[] = "ALTER TABLE AttributeMap MODIFY `chapter_id` int(10) unsigned default NULL";
487 $query[] = "ALTER TABLE IPv4Address MODIFY `ip` int(10) unsigned NOT NULL default '0'";
488 $query[] = "ALTER TABLE IPv4Address MODIFY `name` char(255) NOT NULL default ''";
489 $query[] = "ALTER TABLE IPv4Allocation MODIFY `object_id` int(10) unsigned NOT NULL default '0'";
490 $query[] = "ALTER TABLE IPv4Allocation MODIFY `ip` int(10) unsigned NOT NULL default '0'";
491 $query[] = "ALTER TABLE IPv4Allocation MODIFY `name` char(255) NOT NULL default ''";
492 $query[] = "ALTER TABLE IPv4NAT MODIFY `object_id` int(10) unsigned NOT NULL default '0'";
493 $query[] = "ALTER TABLE IPv4NAT MODIFY `proto` enum('TCP','UDP') NOT NULL default 'TCP'";
494 $query[] = "ALTER TABLE IPv4NAT MODIFY `localip` int(10) unsigned NOT NULL default '0'";
495 $query[] = "ALTER TABLE IPv4NAT MODIFY `localport` smallint(5) unsigned NOT NULL default '0'";
496 $query[] = "ALTER TABLE IPv4NAT MODIFY `remoteip` int(10) unsigned NOT NULL default '0'";
497 $query[] = "ALTER TABLE IPv4NAT MODIFY `remoteport` smallint(5) unsigned NOT NULL default '0'";
498 $query[] = "ALTER TABLE IPv4Network MODIFY `ip` int(10) unsigned NOT NULL default '0'";
499 $query[] = "ALTER TABLE IPv4Network MODIFY `mask` int(10) unsigned NOT NULL default '0'";
500 $query[] = "ALTER TABLE Link MODIFY `porta` int(10) unsigned NOT NULL default '0'";
501 $query[] = "ALTER TABLE Link MODIFY `portb` int(10) unsigned NOT NULL default '0'";
502 $query[] = "ALTER TABLE MountOperation MODIFY `object_id` int(10) unsigned NOT NULL default '0'";
503 $query[] = "ALTER TABLE MountOperation MODIFY `ctime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP";
504 $query[] = "ALTER TABLE Port MODIFY `object_id` int(10) unsigned NOT NULL default '0'";
505 $query[] = "ALTER TABLE Port MODIFY `name` char(255) NOT NULL default ''";
506 $query[] = "ALTER TABLE Port MODIFY `type` int(10) unsigned NOT NULL default '0'";
507 $query[] = "ALTER TABLE PortCompat MODIFY `type1` int(10) unsigned NOT NULL default '0'";
508 $query[] = "ALTER TABLE PortCompat MODIFY `type2` int(10) unsigned NOT NULL default '0'";
509 $query[] = "ALTER TABLE RackHistory MODIFY `ctime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP";
510 $query[] = "ALTER TABLE RackObjectHistory MODIFY `ctime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP";
511 $query[] = "ALTER TABLE TagStorage MODIFY `tag_id` int(10) unsigned NOT NULL default '0'";
512 $query[] = "ALTER TABLE UserAccount MODIFY `user_name` char(64) NOT NULL default ''";
425fd829
DO
513 $query[] = "UPDATE Config SET varvalue = '0.18.1' WHERE varname = 'DB_VERSION'";
514 break;
298d2375 515 case '0.18.2':
298d2375 516 $query[] = "ALTER TABLE Rack ADD CONSTRAINT `Rack-FK-row_id` FOREIGN KEY (row_id) REFERENCES RackRow (id)";
b504972c 517 $query[] = "ALTER TABLE RackRow ADD UNIQUE KEY `name` (name)";
b49a479e
DO
518 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('CDP_RUNNERS_LISTSRC', '', 'string', 'yes', 'no', 'no', 'List of devices running CDP')";
519 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('LLDP_RUNNERS_LISTSRC', '', 'string', 'yes', 'no', 'no', 'List of devices running LLDP')";
10eadad9 520 $query[] = "UPDATE Config SET varvalue = '0.18.2' WHERE varname = 'DB_VERSION'";
298d2375 521 break;
2582446d
DO
522 case '0.18.3':
523 $query[] = "UPDATE Config SET varname='8021Q_WRI_AFTER_CONFT_LISTSRC', varvalue='false', description='802.1Q: save device configuration after deploy (RackCode)' WHERE varname='8021Q_WRI_AFTER_CONFT'";
0328f6d6 524 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('HNDP_RUNNERS_LISTSRC', '', 'string', 'yes', 'no', 'no', 'List of devices running HNDP (RackCode)')";
2582446d
DO
525 $query[] = "UPDATE Config SET varvalue = '0.18.3' WHERE varname = 'DB_VERSION'";
526 break;
3a387b0d 527 case '0.18.4':
ec523868 528 $query[] = "ALTER TABLE VLANSTRule MODIFY port_role enum('access','trunk','anymode','uplink','downlink','none') NOT NULL default 'none'";
3a387b0d
DO
529 $query[] = "UPDATE Config SET varvalue = '0.18.4' WHERE varname = 'DB_VERSION'";
530 break;
95857b5c
DO
531 case '0.18.5':
532 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('SHRINK_TAG_TREE_ON_CLICK','yes','string','no','no','yes','Dynamically hide useless tags in tagtree')";
1f54e1ba 533 $query[] = "ALTER TABLE `IPv4LB` ADD COLUMN `prio` int(10) unsigned DEFAULT NULL AFTER `vs_id`";
1ebbf889 534 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('MAX_UNFILTERED_ENTITIES','0','uint','no','no','yes','Max item count to display on unfiltered result page')";
95857b5c 535 $query[] = "UPDATE Config SET varvalue = '0.18.5' WHERE varname = 'DB_VERSION'";
f2f95f99
JT
536 break;
537 case '0.18.6':
f2f95f99 538 $query[] = "UPDATE Config SET varvalue = '0.18.6' WHERE varname = 'DB_VERSION'";
95857b5c 539 break;
9fb6900d 540 case '0.18.7':
9fb6900d
DO
541 $query[] = "UPDATE Config SET varvalue = '0.18.7' WHERE varname = 'DB_VERSION'";
542 break;
d3346ce2
DO
543 case '0.19.0':
544 $query[] = 'ALTER TABLE `File` ADD `thumbnail` LONGBLOB NULL AFTER `atime`';
21ee3351
AA
545 $query[] = "
546CREATE TABLE `IPv6Address` (
547 `ip` binary(16) NOT NULL,
548 `name` char(255) NOT NULL default '',
549 `reserved` enum('yes','no') default NULL,
550 PRIMARY KEY (`ip`)
551) ENGINE=InnoDB
552";
553 $query[] = "
554CREATE TABLE `IPv6Allocation` (
555 `object_id` int(10) unsigned NOT NULL default '0',
556 `ip` binary(16) NOT NULL,
557 `name` char(255) NOT NULL default '',
558 `type` enum('regular','shared','virtual','router') default NULL,
8c7b7381
AA
559 PRIMARY KEY (`object_id`,`ip`),
560 CONSTRAINT `IPv6Allocation-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE
21ee3351
AA
561) ENGINE=InnoDB
562";
563 $query[] = "
564CREATE TABLE `IPv6Network` (
565 `id` int(10) unsigned NOT NULL auto_increment,
566 `ip` binary(16) NOT NULL,
567 `mask` int(10) unsigned NOT NULL,
568 `last_ip` binary(16) NOT NULL,
569 `name` char(255) default NULL,
570 `comment` text,
571 PRIMARY KEY (`id`),
572 UNIQUE KEY `ip` (`ip`,`mask`)
573) ENGINE=InnoDB
574";
575 $query[] = "
576CREATE TABLE `VLANIPv6` (
577 `domain_id` int(10) unsigned NOT NULL,
578 `vlan_id` int(10) unsigned NOT NULL,
579 `ipv6net_id` int(10) unsigned NOT NULL,
580 UNIQUE KEY `network-domain` (`ipv6net_id`,`domain_id`),
581 KEY `VLANIPv6-FK-compound` (`domain_id`,`vlan_id`),
582 CONSTRAINT `VLANIPv6-FK-compound` FOREIGN KEY (`domain_id`, `vlan_id`) REFERENCES `VLANDescription` (`domain_id`, `vlan_id`) ON DELETE CASCADE,
583 CONSTRAINT `VLANIPv6-FK-ipv6net_id` FOREIGN KEY (`ipv6net_id`) REFERENCES `IPv6Network` (`id`) ON DELETE CASCADE
584) ENGINE=InnoDB
9a90adc4
DO
585";
586 $query[] = "
8489d2af
DO
587CREATE TABLE IF NOT EXISTS `ObjectLog` (
588 `id` int(10) NOT NULL AUTO_INCREMENT,
589 `object_id` int(10) NOT NULL,
590 `user` varchar(64) NOT NULL,
9a90adc4
DO
591 `date` datetime NOT NULL,
592 `content` text NOT NULL,
8489d2af 593 PRIMARY KEY (`id`)
9a90adc4 594) ENGINE=InnoDB
0682218d 595";
8489d2af
DO
596 # Now we have the same structure of ObjectLog table, which objectlog.php
597 # could have left. Subsequent column updates will handle any existing data.
598 $query[] = "ALTER TABLE ObjectLog MODIFY COLUMN `id` int(10) unsigned NOT NULL AUTO_INCREMENT";
599 $query[] = "ALTER TABLE ObjectLog MODIFY COLUMN `object_id` int(10) unsigned NOT NULL";
600 $query[] = "ALTER TABLE ObjectLog MODIFY COLUMN `user` char(64) NOT NULL";
601 $query[] = "ALTER TABLE ObjectLog ADD KEY `object_id` (`object_id`)";
602 $query[] = "ALTER TABLE ObjectLog ADD KEY `date` (`date`)";
603 $query[] = "ALTER TABLE ObjectLog ADD CONSTRAINT `ObjectLog-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE";
604 # Now it's the way 0.19.0 is expecting it to be.
0682218d
AD
605 $query[] = "
606CREATE TABLE `ObjectParentCompat` (
607 `parent_objtype_id` int(10) unsigned NOT NULL,
608 `child_objtype_id` int(10) unsigned NOT NULL,
609 UNIQUE KEY `parent_child` (`parent_objtype_id`,`child_objtype_id`)
610) ENGINE=InnoDB
611";
612 $query[] = "
613CREATE TABLE `EntityLink` (
614 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
615 `parent_entity_type` enum('ipv4net','ipv4rspool','ipv4vs','ipv6net','object','rack','user') NOT NULL,
616 `parent_entity_id` int(10) unsigned NOT NULL,
617 `child_entity_type` enum('file','object') NOT NULL,
618 `child_entity_id` int(10) unsigned NOT NULL,
619 PRIMARY KEY (`id`),
620 UNIQUE KEY `EntityLink-unique` (`parent_entity_type`,`parent_entity_id`,`child_entity_type`,`child_entity_id`)
621) ENGINE=InnoDB
21ee3351
AA
622";
623 $query[] = "ALTER TABLE `TagStorage` CHANGE COLUMN `entity_realm` `entity_realm` ENUM('file','ipv4net','ipv4vs','ipv4rspool','object','rack','user','ipv6net') NOT NULL DEFAULT 'object' FIRST";
624 $query[] = "ALTER TABLE `FileLink` CHANGE COLUMN `entity_type` `entity_type` ENUM('ipv4net','ipv4rspool','ipv4vs','object','rack','user','ipv6net') NOT NULL DEFAULT 'object' AFTER `file_id`";
0c7c9f8b 625 $query[] = 'ALTER TABLE Link ADD COLUMN cable char(64) NULL AFTER portb';
3fb61857 626 $query[] = 'ALTER TABLE RackSpace ADD CONSTRAINT `RackSpace-FK-rack_id` FOREIGN KEY (rack_id) REFERENCES Rack (id)';
8c7b7381 627 $query[] = "ALTER TABLE `IPv4Allocation` ADD CONSTRAINT `IPv4Allocation-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE";
61e79d63 628 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('SYNCDOMAIN_MAX_PROCESSES','0','uint','yes','no', 'How many worker proceses syncdomain cron script should create')";
fbeacc34 629 $query[] = "ALTER TABLE `VLANSwitchTemplate` ADD COLUMN `mutex_rev` int(10) NOT NULL AFTER `id`";
09ec2e59 630 $query[] = "ALTER TABLE `VLANSwitchTemplate` ADD COLUMN `saved_by` char(64) NOT NULL AFTER `description`";
0682218d
AD
631 $query[] = "INSERT INTO `Attribute` (`id`, `type`, `name`) VALUES (26,'dict','Hypervisor')";
632 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (29,'no','Yes/No')";
633 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (30,'no','network chassis models')";
634 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (31,'no','server chassis models')";
635 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (32,'no','virtual switch models')";
636 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (33,'no','virtual switch OS type')";
637 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (4,26,29)";
638 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1502,1,NULL)";
639 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1502,2,31)";
640 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1502,3,NULL)";
641 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1502,14,NULL)";
642 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1502,20,NULL)";
643 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1502,21,NULL)";
644 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1502,22,NULL)";
645 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,1,NULL)";
646 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,2,30)";
647 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,3,NULL)";
588c98f8
AD
648 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,4,14)";
649 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,5,NULL)";
0682218d 650 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,14,NULL)";
588c98f8
AD
651 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,16,NULL)";
652 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,17,NULL)";
653 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,18,NULL)";
0682218d
AD
654 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,20,NULL)";
655 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,21,NULL)";
656 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,22,NULL)";
588c98f8 657 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,24,NULL)";
0682218d
AD
658 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1504,3,NULL)";
659 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1504,4,13)";
660 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1504,14,NULL)";
661 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1504,24,NULL)";
662 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1505,14,NULL)";
663 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1506,14,NULL)";
664 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1506,17,NULL)";
665 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1506,18,NULL)";
666 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,1,NULL)";
667 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,2,32)";
668 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,3,NULL)";
669 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,4,33)";
670 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,5,NULL)";
671 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,14,NULL)";
672 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,20,NULL)";
673 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,21,NULL)";
674 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,22,NULL)";
675 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (3,13)";
676 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (4,1504)";
677 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (4,1507)";
678 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1502,4)";
679 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1503,8)";
680 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1505,4)";
681 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1505,1504)";
682 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1505,1506)";
683 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1505,1507)";
684 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1506,1504)";
0682218d 685 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('PORT_EXCLUSION_LISTSRC','{\$typeid_3} or {\$typeid_10} or {\$typeid_11} or {\$typeid_1505} or {\$typeid_1506}','string','yes','no','no','List source: objects without ports')";
322ce490 686 $query[] = "UPDATE Config SET varvalue = CONCAT(varvalue, ' or {\$typeid_1502} or {\$typeid_1503} or {\$typeid_1504} or {\$typeid_1507}') WHERE varname = 'IPV4OBJ_LISTSRC'";
0682218d 687 $query[] = "UPDATE Config SET varvalue = '8' WHERE varname = 'MASSCOUNT'";
993f5fa3 688 $query[] = "UPDATE RackObject SET label = NULL WHERE label = ''";
81659c05
AD
689 // Move barcode data so the column can be dropped
690 $result = $dbxlink->query ('SELECT id, objtype_id, barcode FROM RackObject WHERE barcode IS NOT NULL');
a7978f7f
AD
691 $rows = $result->fetchAll (PDO::FETCH_ASSOC);
692 unset ($result);
693 foreach ($rows as $row)
81659c05
AD
694 {
695 // Determine if this object type has the 'OEM S/N 1' attribute associated with it, and if it's set
696 $sn_query = "SELECT (SELECT COUNT(*) FROM AttributeMap WHERE objtype_id=${row['objtype_id']} AND attr_id=1) AS AM_count, ";
697 $sn_query .= "(SELECT COUNT(*) FROM AttributeValue WHERE object_id=${row['id']} AND attr_id=1) AS AV_count";
698 $sn_result = $dbxlink->query ($sn_query);
699 $sn_row = $sn_result->fetch (PDO::FETCH_ASSOC);
700 if ($sn_row['AM_count'] == 1 && $sn_row['AV_count'] == 0)
701 {
702 // 'OEM S/N 1' attribute is mapped to this object type, but it is not set. Good!
703 // Copy the barcode value to the attribute.
704 $query[] = "INSERT INTO AttributeValue (`object_id`, `attr_id`, `string_value`) VALUES (${row['id']}, 1, '${row['barcode']}')";
705 }
706 else
707 {
708 // Some other set of circumstances. Not as good!
709 // Copy the barcode value to a new ObjectLog record.
710 $query[] = "INSERT INTO ObjectLog (`object_id`, `user`, `date`, `content`) VALUES (${row['id']}, '${_SERVER['PHP_AUTH_USER']}', NOW(), 'Upgrade to 0.19 dropped the barcode column. Value was: ${row['barcode']}')";
711 }
712 unset ($sn_query, $sn_result, $sn_row);
713 }
81659c05
AD
714 $query[] = 'ALTER TABLE RackObject DROP COLUMN `barcode`';
715 $query[] = 'ALTER TABLE RackObjectHistory DROP COLUMN `barcode`';
fbeacc34 716 $query[] = 'ALTER TABLE `VLANSwitchTemplate` DROP COLUMN `max_local_vlans`';
81659c05 717 $query[] = "UPDATE Config SET varvalue = '0.19.0' WHERE varname = 'DB_VERSION'";
d3346ce2 718 break;
6d42599c 719 case '0.19.1':
1d5dd3a1
AD
720 $query[] = "ALTER TABLE `Config` CHANGE COLUMN `varvalue` `varvalue` text NOT NULL";
721 $query[] = "ALTER TABLE `UserConfig` CHANGE COLUMN `varvalue` `varvalue` text NOT NULL";
6d42599c 722 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('FILTER_RACKLIST_BY_TAGS','yes','string','yes','no','yes','Rackspace: show only racks matching the current object\'s tags')";
782ad4cd
DO
723 $result = $dbxlink->query ("SHOW TABLES LIKE 'Objectlog'");
724 $rows = $result->fetchAll (PDO::FETCH_ASSOC);
725 unset ($result);
726 if (count ($rows))
727 {
728 # Now the ObjectLog merge... again, because the original table is named
729 # "Objectlog". The job is to merge contents of Objectlog and ObjectLog
730 # into the latter.
731 $query[] = "INSERT INTO ObjectLog (object_id, user, date, content) SELECT object_id, user, date, content FROM Objectlog WHERE object_id IN(SELECT id FROM RackObject)";
732 $query[] = "DELETE FROM Objectlog WHERE object_id IN(SELECT id FROM RackObject)";
733 # Don't delete the old table, if the merge wasn't exhaustive.
734 $result = $dbxlink->query ('SELECT COUNT(*) AS c FROM Objectlog WHERE object_id NOT IN(SELECT id FROM RackObject)');
735 $row = $result->fetch (PDO::FETCH_ASSOC);
736 unset ($result);
737 if ($row['c'] == 0)
738 $query[] = 'DROP TABLE Objectlog';
739 else
740 $query[] = 'ALTER TABLE Objectlog RENAME TO Objectlog_old_unmerged';
741 }
6d42599c
AA
742 $query[] = "UPDATE Config SET varvalue = '0.19.1' WHERE varname = 'DB_VERSION'";
743 break;
f701420b 744 case '0.19.2':
f701420b
AA
745 $query[] = "ALTER TABLE IPv4Allocation ADD KEY `ip` (`ip`)";
746 $query[] = "ALTER TABLE IPv6Allocation ADD KEY `ip` (`ip`)";
747 $query[] = "ALTER TABLE IPv4VS ADD KEY `vip` (`vip`)";
748 $query[] = "ALTER TABLE IPv4RS ADD KEY `rsip` (`rsip`)";
ae67fa11
AD
749 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (34,'no','power supply chassis models')";
750 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (35,'no','power supply models')";
751 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1397,1,NULL)";
752 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1397,2,34)";
753 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1397,14,NULL)";
754 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1397,21,NULL)";
755 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1397,22,NULL)";
756 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1398,1,NULL)";
757 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1398,2,35)";
758 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1398,14,NULL)";
759 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1398,21,NULL)";
760 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1398,22,NULL)";
761 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1397,1398)";
762 $query[] = "INSERT INTO `PortCompat` (`type1`, `type2`) VALUES (1399,1399)";
763 $query[] = "INSERT INTO `PortInterfaceCompat` (`iif_id`, `oif_id`) VALUES (1,1399)";
764 $query[] = "UPDATE Config SET varvalue = CONCAT(varvalue, ' or {\$typeid_1397}') WHERE varname = 'IPV4OBJ_LISTSRC'";
c4ad9ac0
AA
765 $query[] = "ALTER TABLE AttributeValue ADD KEY `attr_id-uint_value` (`attr_id`,`uint_value`)";
766 $query[] = "ALTER TABLE AttributeValue ADD KEY `attr_id-string_value` (`attr_id`,`string_value`(12))";
0abae5fb 767 $query[] = "UPDATE Config SET varvalue = '0.19.2' WHERE varname = 'DB_VERSION'";
f701420b 768 break;
6d2cc13e 769 case '0.19.3':
6d2cc13e
DO
770 $query[] = "DELETE FROM RackSpace WHERE object_id IS NULL AND state = 'T'";
771 $query[] = "UPDATE Config SET varvalue = '0.19.3' WHERE varname = 'DB_VERSION'";
772 break;
1f02e311 773 case '0.19.4':
1f02e311
AD
774 $query[] = "UPDATE Config SET varvalue = '0.19.4' WHERE varname = 'DB_VERSION'";
775 break;
e1486971 776 case '0.19.5':
e1486971
DO
777 // Add 'virtual port' to 'virtual port' mapping
778 $query[] = "INSERT INTO `PortCompat` (`type1`,`type2`) VALUES (1469,1469)";
779 $query[] = "INSERT INTO `PortInterfaceCompat` (`iif_id`,`oif_id`) VALUES (1,1469)";
25b8a91f
AA
780 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('SSH_OBJS_LISTSRC','none','string','yes','no','yes','Rackcode filter for SSH-managed objects')";
781 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('TELNET_OBJS_LISTSRC','none','string','yes','no','yes','Rackcode filter for telnet-managed objects')";
7d91adc7 782 $query[] = "UPDATE Link SET cable = NULL WHERE cable = ''";
42b4722b 783 $query[] = "ALTER TABLE AttributeValue MODIFY string_value char(255) DEFAULT NULL";
e1486971
DO
784 $query[] = "UPDATE Config SET varvalue = '0.19.5' WHERE varname = 'DB_VERSION'";
785 break;
2a3a1a6d 786 case '0.19.6':
2a3a1a6d
AA
787 $query[] = "UPDATE Config SET varvalue = '0.19.6' WHERE varname = 'DB_VERSION'";
788 break;
b1bde5f6 789 case '0.19.7':
b1bde5f6
AD
790 # A plain "ALTER TABLE Attribute" can leave AUTO_INCREMENT in an odd
791 # state, hence the table swap.
792 $query[] = "
793CREATE TABLE `Attribute_new` (
794 `id` int(10) unsigned NOT NULL auto_increment,
795 `type` enum('string','uint','float','dict') default NULL,
796 `name` char(64) default NULL,
797 PRIMARY KEY (`id`),
798 UNIQUE KEY `name` (`name`)
799) ENGINE=InnoDB
800";
801 $query[] = "INSERT INTO Attribute_new SELECT * FROM Attribute";
802 $query[] = "INSERT INTO Attribute_new VALUES (9999, 'string', 'base MAC address')";
803 $query[] = "DROP TABLE Attribute";
804 $query[] = "ALTER TABLE Attribute_new RENAME TO Attribute";
805 $query[] = "ALTER TABLE AttributeMap ADD KEY (attr_id)";
806 $query[] = "DELETE FROM AttributeMap WHERE attr_id NOT IN (SELECT id FROM Attribute)";
807 $query[] = "ALTER TABLE AttributeMap ADD CONSTRAINT `AttributeMap-FK-attr_id` FOREIGN KEY (attr_id) REFERENCES Attribute (id)";
808 $query[] = "DELETE FROM AttributeValue WHERE attr_id NOT IN (SELECT attr_id FROM AttributeMap)";
809 $query[] = "ALTER TABLE AttributeValue ADD CONSTRAINT `AttributeValue-FK-attr_id` FOREIGN KEY (attr_id) REFERENCES AttributeMap (attr_id)";
810 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1506,4)";
44cbeb2e
DO
811 $query[] = "INSERT INTO PortInnerInterface (id, iif_name) VALUES (10, 'QSFP+')";
812 $query[] = "INSERT INTO PortInterfaceCompat VALUES (10, 1588)";
b1bde5f6
AD
813 $query[] = "UPDATE Config SET varvalue = '0.19.7' WHERE varname = 'DB_VERSION'";
814 break;
a03332f7 815 case '0.19.8':
3aca495a
DO
816 for ($i = 1424; $i <= 1466; $i++) # CX, then 42 ER channels
817 $query[] = "INSERT INTO `PortCompat` (`type1`, `type2`) VALUES (${i},${i})";
c7968df3
DO
818 $query[] = "ALTER TABLE UserAccount ENGINE=InnoDB";
819 $query[] = "DELETE FROM UserConfig WHERE user NOT IN (SELECT user_name FROM UserAccount)";
820 $query[] = "ALTER TABLE UserConfig ADD CONSTRAINT `UserConfig-FK-user` FOREIGN KEY (user) REFERENCES UserAccount (user_name) ON DELETE CASCADE";
821 $query[] = "DELETE FROM UserConfig WHERE varname NOT IN (SELECT varname FROM Config)";
822 $query[] = "ALTER TABLE UserConfig ADD KEY (varname)";
823 $query[] = "ALTER TABLE UserConfig ADD CONSTRAINT `UserConfig-FK-varname` FOREIGN KEY (varname) REFERENCES Config (varname) ON DELETE CASCADE";
a03332f7 824 $query[] = "ALTER TABLE Dictionary ENGINE=InnoDB";
c7968df3
DO
825 $query[] = "ALTER TABLE Chapter ENGINE=InnoDB";
826 $query[] = "UPDATE Chapter SET id = 9999 WHERE id = 22";
827 $query[] = "UPDATE AttributeMap SET chapter_id = 9999 WHERE chapter_id = 22";
828 $query[] = "UPDATE Dictionary SET chapter_id = 9999 WHERE chapter_id = 22";
829 $query[] = "DELETE FROM Dictionary WHERE chapter_id NOT IN (SELECT id FROM Chapter)";
830 $query[] = "ALTER TABLE Dictionary ADD CONSTRAINT `Dictionary-FK-chapter_id` FOREIGN KEY (chapter_id) REFERENCES Chapter (id)";
831 $query[] = "DELETE FROM AttributeMap WHERE chapter_id NOT IN (SELECT id FROM Chapter)";
832 $query[] = "ALTER TABLE AttributeMap ADD KEY (chapter_id)";
833 $query[] = "ALTER TABLE AttributeMap ADD CONSTRAINT `AttributeMap-FK-chapter_id` FOREIGN KEY (chapter_id) REFERENCES Chapter (id)";
c09757f6
DO
834 $query[] = "
835CREATE TABLE `CactiGraph` (
836 `object_id` int(10) unsigned NOT NULL,
837 `graph_id` int(10) unsigned NOT NULL,
838 `caption` char(255) DEFAULT NULL,
839 PRIMARY KEY (`graph_id`),
840 KEY `object_id` (`object_id`),
4d082372 841 CONSTRAINT `CactiGraph-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE
c09757f6
DO
842) ENGINE=InnoDB;
843";
844 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('CACTI_LISTSRC','false','string','yes','no','no','List of object with Cacti graphs')";
845 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('CACTI_URL','','string','yes','no','no','Cacti server base URL')";
846 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('CACTI_USERNAME','','string','yes','no','no','Cacti user account')";
847 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('CACTI_USERPASS','','string','yes','no','no','Cacti user password')";
a03332f7
DO
848 $query[] = "UPDATE Config SET varvalue = '0.19.8' WHERE varname = 'DB_VERSION'";
849 break;
5c0bd7de 850 case '0.19.9':
5c0bd7de 851 $query[] = "DELETE FROM Config WHERE varname = 'HNDP_RUNNERS_LISTSRC'";
8920f7f5
DO
852 # Dismiss some overly-specific OIF types in favour of more generic counterparts.
853 $squeeze = array
854 (
855 1202 => array # 1000Base-SX
856 (
857 25, # 1000Base-SX (SC)
858 26, # 1000Base-SX (LC)
859 ),
860 1204 => array # 1000Base-LX
861 (
862 27, # 1000Base-LX (SC)
863 28, # 1000Base-LX (LC)
864 ),
865 1196 => array # 100Base-SX
866 (
867 22, # 100Base-SX (SC)
868 23, # 100Base-SX (LC)
869 ),
870 1195 => array # 100Base-FX
871 (
872 20, # 100Base-FX (SC)
873 21, # 100Base-FX (LC)
874 1083, # 100Base-FX (MT-RJ)
875 ),
876 );
877 foreach ($squeeze as $stays => $leaves)
878 {
879 $csv = implode (', ', $leaves);
880 $query[] = "DELETE FROM PortCompat WHERE type1 IN(${csv}) OR type2 IN(${csv})";
881 $query[] = "INSERT IGNORE INTO PortInterfaceCompat (iif_id, oif_id) SELECT iif_id, ${stays} FROM Port WHERE type IN (${csv})";
882 $query[] = "UPDATE Port SET type = ${stays} WHERE type IN(${csv})";
883 $query[] = "DELETE FROM PortInterfaceCompat WHERE oif_id IN(${csv})";
884 }
fd8de939
DO
885 $query[] = "UPDATE Config SET varvalue = '0.19.9' WHERE varname = 'DB_VERSION'";
886 break;
f7494e3c 887 case '0.19.10':
f7494e3c
DO
888 $query[] = "INSERT INTO `PortCompat` (`type1`, `type2`) VALUES (1603,1603)";
889 $query[] = "UPDATE Config SET varvalue = '0.19.10' WHERE varname = 'DB_VERSION'";
890 break;
d0004c46 891 case '0.19.11':
8dc2a6c8 892 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('VIRTUAL_OBJ_LISTSRC','1504,1505,1506,1507','string','no','no','no','List source: virtual objects')";
453cce7e
AD
893 $query[] = "INSERT INTO `Attribute` (`id`,`type`,`name`) VALUES (28,'string','Slot number')";
894 $query[] = 'INSERT INTO `AttributeMap` (`objtype_id`,`attr_id`,`chapter_id`) VALUES (4,28,NULL)';
895 $query[] = 'INSERT INTO `AttributeMap` (`objtype_id`,`attr_id`,`chapter_id`) VALUES (8,28,NULL)';
896 $query[] = 'INSERT INTO `AttributeMap` (`objtype_id`,`attr_id`,`chapter_id`) VALUES (798,28,NULL)';
897 $query[] = 'INSERT INTO `AttributeMap` (`objtype_id`,`attr_id`,`chapter_id`) VALUES (1055,28,NULL)';
7d68a0fb 898 $query[] = 'ALTER TABLE AttributeValue ADD COLUMN object_tid int(10) unsigned NOT NULL default 0 AFTER object_id';
4d082372 899 $query[] = 'UPDATE AttributeValue SET object_tid = (SELECT objtype_id FROM RackObject WHERE id = object_id)';
7d68a0fb
DO
900 $query[] = 'ALTER TABLE AttributeValue DROP FOREIGN KEY `AttributeValue-FK-attr_id`';
901 $query[] = 'ALTER TABLE AttributeValue DROP FOREIGN KEY `AttributeValue-FK-object_id`';
902 $query[] = 'ALTER TABLE AttributeValue ADD KEY `id-tid` (object_id, object_tid)';
903 $query[] = 'ALTER TABLE AttributeValue ADD KEY `object_tid-attr_id` (`object_tid`,`attr_id`)';
4d082372
AD
904 $query[] = 'ALTER TABLE RackObject ADD KEY `id-tid` (id, objtype_id)';
905 $query[] = 'ALTER TABLE AttributeValue ADD CONSTRAINT `AttributeValue-FK-object` FOREIGN KEY (`object_id`, `object_tid`) REFERENCES `RackObject` (`id`, `objtype_id`) ON DELETE CASCADE ON UPDATE CASCADE';
7d68a0fb 906 $query[] = 'ALTER TABLE AttributeValue ADD CONSTRAINT `AttributeValue-FK-map` FOREIGN KEY (`object_tid`, `attr_id`) REFERENCES `AttributeMap` (`objtype_id`, `attr_id`)';
9e906292
DO
907 # 0.19.9 did it right, but kept the IDs in the dictionary. This time
908 # the dictionary is reduced, but the procedure needs to be repeated,
909 # in case the user had enough time to use the wrong IDs again.
910 $squeeze = array
911 (
912 1202 => array # 1000Base-SX
913 (
914 25, # 1000Base-SX (SC)
915 26, # 1000Base-SX (LC)
916 ),
917 1204 => array # 1000Base-LX
918 (
919 27, # 1000Base-LX (SC)
920 28, # 1000Base-LX (LC)
921 ),
922 1196 => array # 100Base-SX
923 (
924 22, # 100Base-SX (SC)
925 23, # 100Base-SX (LC)
926 ),
927 1195 => array # 100Base-FX
928 (
929 20, # 100Base-FX (SC)
930 21, # 100Base-FX (LC)
931 1083, # 100Base-FX (MT-RJ)
932 ),
933 );
934 foreach ($squeeze as $stays => $leaves)
935 {
936 $csv = implode (', ', $leaves);
937 $query[] = "DELETE FROM PortCompat WHERE type1 IN(${csv}) OR type2 IN(${csv})";
938 $query[] = "INSERT IGNORE INTO PortInterfaceCompat (iif_id, oif_id) SELECT iif_id, ${stays} FROM Port WHERE type IN (${csv})";
939 $query[] = "UPDATE Port SET type = ${stays} WHERE type IN(${csv})";
940 $query[] = "DELETE FROM PortInterfaceCompat WHERE oif_id IN(${csv})";
941 }
18680729 942 $query[] = "INSERT INTO `PortCompat` (`type1`, `type2`) VALUES (1642,1642)";
d6e7a8e7 943 $query[] = 'ALTER TABLE `EntityLink` ADD KEY `EntityLink-compound` (`parent_entity_type`,`child_entity_type`,`child_entity_id`)';
d0004c46
AD
944 $query[] = "UPDATE Config SET varvalue = '0.19.11' WHERE varname = 'DB_VERSION'";
945 break;
7cf0bc68
DO
946 # Batch 0.19.12 contained minor, but annoying bugs and was modified after the
947 # release of version 0.19.12 (which should be avoided if possible). The best
948 # way to resolve this particular case was to recall RackTables-0.19.12.tar.gz
949 # from the download area and provide RackTables-0.19.13.tar.gz containing the
950 # modified 0.19.12 batch.
a4c50c50 951 case '0.19.12':
f6252853 952 $query[] = "DELETE FROM Config WHERE varname IN('color_F', 'color_A', 'color_U', 'color_T', 'color_Th', 'color_Tw', 'color_Thw')";
2ce1cbdb
DO
953 $query[] = "INSERT INTO Chapter (id, sticky, name) VALUES (36,'no','serial console server models')";
954 $query[] = "INSERT INTO AttributeMap (objtype_id, attr_id, chapter_id) VALUES (1644, 1, NULL), (1644, 2, 36), (1644, 3, NULL)";
01d821c9 955 $query[] = "UPDATE Config SET varvalue = CONCAT(varvalue, '; 10=1588') WHERE varname = 'DEFAULT_PORT_OIF_IDS' AND 0 = INSTR(varvalue, '10=') ";
55732b46
DO
956 $query[] = "INSERT INTO PortInterfaceCompat VALUES (10,1663), (10,1664)";
957 $query[] = "INSERT INTO PortCompat VALUES (1588,1588), (1661,1661), (1663,1663), (1664,1664)";
833583b5
DO
958 $query[] = "INSERT INTO PortInnerInterface (id, iif_name) VALUES (11, 'CFP')";
959 $query[] = "INSERT INTO PortInterfaceCompat (iif_id, oif_id) VALUES (11,1668),(11,1669),(11,1670),(11,1671)";
960 $query[] = "INSERT INTO PortCompat (type1, type2) VALUES (1668,1668), (1669,1669), (1670,1670), (1671,1671)";
01d821c9 961 $query[] = "UPDATE Config SET varvalue = CONCAT(varvalue, '; 11=1668') WHERE varname = 'DEFAULT_PORT_OIF_IDS'";
0c652e58
DO
962 $query[] = "INSERT INTO Chapter (id, sticky, name) VALUES (37, 'no', 'wireless OS type')";
963 $query[] = "INSERT INTO AttributeMap (objtype_id, attr_id, chapter_id) VALUES (965, 4, 37)";
a4c50c50
DO
964 $query[] = "UPDATE Config SET varvalue = '0.19.12' WHERE varname = 'DB_VERSION'";
965 break;
86ff26ae
DO
966 case '0.19.13':
967 // add the date attribute type
968 $query[] = "ALTER TABLE `Attribute` CHANGE COLUMN `type` `type` enum('string','uint','float','dict','date') DEFAULT NULL";
0ec44a91
AA
969 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('DATETIME_ZONE','UTC','string','yes','no','yes','Timezone to use for displaying/calculating dates')";
970 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('DATETIME_FORMAT','m/d/Y','string','no','no','yes','PHP date() format to use for date output')";
86ff26ae
DO
971
972 // port over existing fields to new date attr type
973 $query[] = "UPDATE Attribute SET type='date' WHERE id IN (21,22,24)";
974 $query[] = "UPDATE AttributeValue SET uint_value=UNIX_TIMESTAMP(STR_TO_DATE(string_value, '%m/%d/%Y')) WHERE attr_id IN(21,22,24)";
975
0ec44a91
AA
976 // some config variables should be configurable in per-user basis
977 $query[] = "UPDATE `Config` SET `is_userdefined` = 'yes' WHERE `varname` IN ('ENABLE_MULTIPORT_FORM','FILTER_DEFAULT_ANDOR','FILTER_PREDICATE_SIEVE','FILTER_SUGGEST_ANDOR','FILTER_SUGGEST_EXTRA','FILTER_SUGGEST_PREDICATES','FILTER_SUGGEST_TAGS','IPV4_ENABLE_KNIGHT','IPV4_TREE_RTR_AS_CELL','TAGS_QUICKLIST_SIZE','TAGS_QUICKLIST_THRESHOLD','TAGS_TOPLIST_SIZE')";
978
86ff26ae
DO
979 $query[] = "UPDATE Config SET varvalue = '0.19.13' WHERE varname = 'DB_VERSION'";
980 break;
b55f913c
DO
981 case '0.19.14':
982 $query[] = "ALTER TABLE AttributeValue CHANGE COLUMN `object_id` `object_id` INT(10) UNSIGNED NOT NULL";
983 $query[] = "ALTER TABLE AttributeValue CHANGE COLUMN `attr_id` `attr_id` INT(10) UNSIGNED NOT NULL";
984 $query[] = "ALTER TABLE AttributeValue ADD PRIMARY KEY (`object_id`, `attr_id`), DROP INDEX `object_id`";
70d6c4fd
DO
985 $query[] = "ALTER TABLE Dictionary ADD COLUMN `dict_sticky` enum('yes','no') DEFAULT 'no' AFTER `dict_key`";
986 $query[] = "UPDATE Dictionary SET dict_sticky = 'yes' WHERE dict_key < 50000";
987 $query[] = "ALTER TABLE Dictionary ADD UNIQUE KEY dict_unique (chapter_id, dict_value, dict_sticky)";
988 $query[] = "ALTER TABLE Dictionary DROP KEY `chap_to_val`";
b55f913c
DO
989 $query[] = "UPDATE Config SET varvalue = '0.19.14' WHERE varname = 'DB_VERSION'";
990 break;
fd8de939 991 case '0.20.0':
fd8de939
DO
992 $query[] = "
993CREATE TABLE `PortLog` (
994 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
995 `port_id` int(10) unsigned NOT NULL,
996 `date` datetime NOT NULL,
997 `user` varchar(64) NOT NULL,
998 `message` text NOT NULL,
999 PRIMARY KEY (`id`),
1000 KEY `port_id-date` (`port_id`,`date`),
1001 CONSTRAINT `PortLog_ibfk_1` FOREIGN KEY (`port_id`) REFERENCES `Port` (`id`) ON DELETE CASCADE
1002) ENGINE=InnoDB;
1003";
1004 $query[] = "
1005CREATE TABLE `IPv4Log` (
1006 `id` int(10) NOT NULL AUTO_INCREMENT,
1007 `ip` int(10) unsigned NOT NULL,
1008 `date` datetime NOT NULL,
1009 `user` varchar(64) NOT NULL,
1010 `message` text NOT NULL,
1011 PRIMARY KEY (`id`),
1012 KEY `ip-date` (`ip`,`date`)
1013) ENGINE=InnoDB;
4318ced5
AA
1014";
1015 $query[] = "
1016CREATE TABLE `IPv6Log` (
1017 `id` int(10) NOT NULL AUTO_INCREMENT,
1018 `ip` binary(16) NOT NULL,
1019 `date` datetime NOT NULL,
1020 `user` varchar(64) NOT NULL,
1021 `message` text NOT NULL,
1022 PRIMARY KEY (`id`),
1023 KEY `ip-date` (`ip`,`date`)
1024) ENGINE=InnoDB;
fd8de939 1025";
e1add254 1026 $query[] = "ALTER TABLE `FileLink` MODIFY COLUMN `entity_type` ENUM('ipv4net','ipv4rspool','ipv4vs','ipv6net','location','object','rack','user') NOT NULL DEFAULT 'object'";
93b7c5d8 1027 $query[] = "ALTER TABLE `TagStorage` MODIFY COLUMN `entity_realm` ENUM('file','ipv4net','ipv4rspool','ipv4vs','ipv6net','location','object','rack','user','vst') NOT NULL default 'object'";
30bb83bd 1028 $query[] = "ALTER TABLE `TagStorage` ADD COLUMN `user` char(64) DEFAULT NULL, ADD COLUMN `date` datetime DEFAULT NULL";
9b8174d7 1029
e1add254 1030 // Rename object tables and keys, 'name' no longer needs to be unique
9b8174d7
AD
1031 $dbxlink->query ('ALTER TABLE `RackObject` RENAME TO `Object`');
1032 $dbxlink->query ('ALTER TABLE `RackObjectHistory` RENAME TO `ObjectHistory`');
e1add254 1033 $dbxlink->query ('ALTER TABLE `Object` DROP KEY `name`');
9b8174d7
AD
1034 $query[] = 'ALTER TABLE `Object` DROP KEY `RackObject_asset_no`';
1035 $query[] = 'ALTER TABLE `Object` ADD UNIQUE KEY `asset_no` (`asset_no`)';
bd7c95ce 1036 $query[] = 'ALTER TABLE `Object` ADD KEY `type_id` (`objtype_id`,`id`)';
9b8174d7
AD
1037 $query[] = 'ALTER TABLE `ObjectHistory` DROP FOREIGN KEY `RackObjectHistory-FK-object_id`';
1038 $query[] = 'ALTER TABLE `ObjectHistory` ADD CONSTRAINT `ObjectHistory-FK-object_id` FOREIGN KEY (`id`) REFERENCES `Object` (`id`) ON DELETE CASCADE';
1039 $query[] = 'ALTER TABLE `RackSpace` DROP FOREIGN KEY `RackSpace-FK-rack_id`';
e1add254 1040
9b8174d7 1041 // Rack height is now an attribute
a14f752c 1042 $query[] = "INSERT INTO `Attribute` (`id`,`type`,`name`) VALUES (27,'uint','Height, units')";
9b8174d7 1043 $query[] = 'INSERT INTO `AttributeMap` (`objtype_id`,`attr_id`,`chapter_id`) VALUES (1560,27,NULL)';
e1add254 1044
42504426
AD
1045 // Racks are now sorted using an attribute
1046 $query[] = "INSERT INTO `Attribute` (`id`,`type`,`name`) VALUES (29,'uint','Sort order')";
1047 $query[] = 'INSERT INTO `AttributeMap` (`objtype_id`,`attr_id`,`chapter_id`) VALUES (1560,29,NULL)';
1048
e1add254
AD
1049 // Relate 'contact person' with locations
1050 $query[] = 'INSERT INTO `AttributeMap` (`objtype_id`,`attr_id`,`chapter_id`) VALUES (1562,14,NULL)';
1051
1052 // Allow relationships between racks/rows/locations
1053 $query[] = "ALTER TABLE `EntityLink` MODIFY COLUMN `parent_entity_type` ENUM('ipv4net','ipv4rspool','ipv4vs','ipv6net','location','object','rack','row','user') NOT NULL";
1054 $query[] = "ALTER TABLE `EntityLink` MODIFY COLUMN `child_entity_type` ENUM('file','location','object','rack','row') NOT NULL";
1055
9b8174d7
AD
1056 // Turn rows into objects
1057 $result = $dbxlink->query ('SELECT * FROM RackRow');
1058 $rows = $result->fetchAll (PDO::FETCH_ASSOC);
1059 unset ($result);
1060 foreach ($rows as $row)
1061 {
e1add254 1062 $prepared = $dbxlink->prepare ('INSERT INTO `Object` (`name`,`objtype_id`) VALUES (?,?)');
9b8174d7
AD
1063 $prepared->execute (array($row['name'], 1561));
1064 $row_id = $dbxlink->lastInsertId();
1065 // Turn all racks in this row into objects
42504426 1066 $result = $dbxlink->query ("SELECT id, name, height, comment FROM Rack WHERE row_id=${row['id']} ORDER BY name");
9b8174d7
AD
1067 $racks = $result->fetchAll (PDO::FETCH_ASSOC);
1068 unset ($result);
42504426 1069 $sort_order = 1;
dec748f6 1070 foreach ($racks as $rack)
9b8174d7 1071 {
42504426 1072 // Add the rack as an object, set the height and sort order as attributes, link the rack to the row,
9374831b 1073 // update rackspace, tags and files to reflect new rack_id, move history
e1add254 1074 $prepared = $dbxlink->prepare ('INSERT INTO `Object` (`name`,`objtype_id`,`comment`) VALUES (?,?,?)');
9b8174d7
AD
1075 $prepared->execute (array($rack['name'], 1560, $rack['comment']));
1076 $rack_id = $dbxlink->lastInsertId();
e1add254 1077 $query[] = "INSERT INTO `AttributeValue` (`object_id`,`object_tid`,`attr_id`,`uint_value`) VALUES (${rack_id},1560,27,${rack['height']})";
42504426 1078 $query[] = "INSERT INTO `AttributeValue` (`object_id`,`object_tid`,`attr_id`,`uint_value`) VALUES (${rack_id},1560,29,${sort_order})";
e1add254 1079 $query[] = "INSERT INTO `EntityLink` (`parent_entity_type`,`parent_entity_id`,`child_entity_type`,`child_entity_id`) VALUES ('row',${row_id},'rack',${rack_id})";
9b8174d7 1080 $query[] = "UPDATE `RackSpace` SET `rack_id`=${rack_id} WHERE `rack_id`=${rack['id']}";
1f02e311 1081 $query[] = "UPDATE `Atom` SET `rack_id`=${rack_id} WHERE `rack_id`=${rack['id']}";
c718f1e2
AA
1082 $query[] = "UPDATE `TagStorage` SET `entity_id`=${rack_id} WHERE `entity_realm`='rack' AND `entity_id`=${rack['id']}";
1083 $query[] = "UPDATE `FileLink` SET `entity_id`=${rack_id} WHERE `entity_type`='rack' AND `entity_id`=${rack['id']}";
9b8174d7 1084 $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 1085 $sort_order++;
9b8174d7
AD
1086 }
1087 }
9374831b 1088 $query[] = 'ALTER TABLE `RackSpace` ADD CONSTRAINT `RackSpace-FK-rack_id` FOREIGN KEY (`rack_id`) REFERENCES `Object` (`id`)';
9b8174d7
AD
1089 $query[] = 'DROP TABLE `Rack`';
1090 $query[] = 'DROP TABLE `RackRow`';
1091 $query[] = 'DROP TABLE `RackHistory`';
1092 $query[] = "
1093CREATE TABLE `RackThumbnail` (
1094 `rack_id` int(10) unsigned NOT NULL,
1095 `thumb_data` blob,
1096 UNIQUE KEY `rack_id` (`rack_id`),
1097 CONSTRAINT `RackThumbnail-FK-rack_id` FOREIGN KEY (`rack_id`) REFERENCES `Object` (`id`) ON DELETE CASCADE
1098) ENGINE=InnoDB
1099";
1100 $query[] = "
69bd4a37
AA
1101CREATE VIEW `Location` AS SELECT O.id, O.name, O.has_problems, O.comment, P.id AS parent_id, P.name AS parent_name
1102FROM `Object` O
1103LEFT JOIN (
1104 `Object` P INNER JOIN `EntityLink` EL
1105 ON EL.parent_entity_id = P.id AND P.objtype_id = 1562 AND EL.parent_entity_type = 'location' AND EL.child_entity_type = 'location'
1106) ON EL.child_entity_id = O.id
1107WHERE O.objtype_id = 1562
e1add254
AD
1108";
1109 $query[] = "
1110CREATE VIEW `Row` AS SELECT O.id, O.name, L.id AS location_id, L.name AS location_name
1111 FROM `Object` O
1112 LEFT JOIN `EntityLink` EL ON O.id = EL.child_entity_id AND EL.parent_entity_type = 'location' AND EL.child_entity_type = 'row'
1113 LEFT JOIN `Object` L ON EL.parent_entity_id = L.id AND L.objtype_id = 1562
1114 WHERE O.objtype_id = 1561
9b8174d7
AD
1115";
1116 $query[] = "
9c55b126 1117CREATE VIEW `Rack` AS SELECT O.id, O.name AS name, O.asset_no, O.has_problems, O.comment,
42504426
AD
1118 AV_H.uint_value AS height,
1119 AV_S.uint_value AS sort_order,
1f02e311 1120 RT.thumb_data,
e1add254
AD
1121 R.id AS row_id,
1122 R.name AS row_name
1f02e311 1123 FROM `Object` O
42504426
AD
1124 LEFT JOIN `AttributeValue` AV_H ON O.id = AV_H.object_id AND AV_H.attr_id = 27
1125 LEFT JOIN `AttributeValue` AV_S ON O.id = AV_S.object_id AND AV_S.attr_id = 29
1f02e311 1126 LEFT JOIN `RackThumbnail` RT ON O.id = RT.rack_id
e1add254
AD
1127 LEFT JOIN `EntityLink` EL ON O.id = EL.child_entity_id AND EL.parent_entity_type = 'row' AND EL.child_entity_type = 'rack'
1128 INNER JOIN `Object` R ON R.id = EL.parent_entity_id
1129 WHERE O.objtype_id = 1560
9b8174d7
AD
1130";
1131 $query[] = "
d7e9e25b 1132CREATE VIEW `RackObject` AS SELECT id, name, label, objtype_id, asset_no, has_problems, comment FROM `Object`
9b8174d7
AD
1133 WHERE `objtype_id` NOT IN (1560, 1561, 1562)
1134";
1135 $query[] = "UPDATE `Chapter` SET `name` = 'ObjectType' WHERE `id` = 1";
1136 $query[] = "DELETE FROM RackSpace WHERE object_id IS NULL AND state = 'T'";
2265be00 1137
f1cdc9f1 1138 $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 1139 $query[] = "UPDATE `Config` SET is_userdefined='yes' WHERE varname='PROXIMITY_RANGE'";
9c64ccf8 1140 $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 1141 $query[] = "ALTER TABLE `IPv4LB` MODIFY `prio` varchar(255) DEFAULT NULL";
71066ef1 1142
cc2fa820
AD
1143 $query[] = "ALTER TABLE `IPv4Address` ADD COLUMN `comment` char(255) NOT NULL default '' AFTER `name`";
1144 $query[] = "ALTER TABLE `IPv6Address` ADD COLUMN `comment` char(255) NOT NULL default '' AFTER `name`";
1145
71066ef1
AA
1146 // change IP address format of IPv4VS and IPv4RS tables
1147 convertSLBTablesToBinIPs();
1148
581e3b64
AA
1149 // do not allow NULL allocation type
1150 $query[] = "ALTER TABLE `IPv4Allocation` MODIFY `type` enum('regular','shared','virtual','router') NOT NULL DEFAULT 'regular'";
1151 $query[] = "ALTER TABLE `IPv6Allocation` MODIFY `type` enum('regular','shared','virtual','router') NOT NULL DEFAULT 'regular'";
1152
2481e17e
AA
1153 $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')";
1154
9c64ccf8
AA
1155 // update some config variables which changed their defaults in this verison
1156 replaceConfigVarValue ('SHOW_LAST_TAB', 'yes');
1157 replaceConfigVarValue ('IPV4_TREE_SHOW_USAGE','no');
1158 replaceConfigVarValue ('IPV4LB_LISTSRC', 'false', '{$typeid_4}');
1159 replaceConfigVarValue ('FILTER_DEFAULT_ANDOR', 'and');
1160 replaceConfigVarValue ('FILTER_SUGGEST_EXTRA', 'yes');
1161 replaceConfigVarValue ('IPV4_TREE_RTR_AS_CELL', 'no');
1162 replaceConfigVarValue ('SSH_OBJS_LISTSRC', 'false', 'none');
1163 replaceConfigVarValue ('TELNET_OBJS_LISTSRC', 'false', 'none');
1164
85e868a0
AA
1165 $query[] = "UPDATE Config SET varvalue = '0.20.0' WHERE varname = 'DB_VERSION'";
1166 break;
e9893a88 1167 case '0.20.1':
4e221a3d
AD
1168 // some HW types were moved from the 'Network switch' chapter to the 'Network chassis' chapter
1169 // change the type of affected objects to 'Network chassis'
1170 $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))";
1171
e9893a88
AD
1172 // new 'management interface' object type
1173 $query[] = "INSERT INTO `Chapter` (`id`,`sticky`,`name`) VALUES (38,'no','management interface type')";
1174 $query[] = "INSERT INTO `Attribute` (`id`,`type`,`name`) VALUES (30,'dict','Mgmt type')";
1175 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`,`attr_id`,`chapter_id`) VALUES (1787,3,NULL),(1787,14,NULL),(1787,30,38)";
1176 $query[] = "UPDATE `Config` SET varvalue = CONCAT(varvalue, ' or {\$typeid_1787}') WHERE varname = 'IPV4OBJ_LISTSRC'";
1177
2198b21a
DO
1178 $query[] = "INSERT INTO Config VALUES ('8021Q_EXTSYNC_LISTSRC','false','string','yes','no','no','List source: objects with extended 802.1Q sync')";
1179
6e5556bc
AD
1180 // constraints to prevent orphan records
1181 $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";
1182 $query[] = "DELETE FROM `MountOperation` WHERE old_molecule_id NOT IN (SELECT id FROM `Molecule`) OR new_molecule_id NOT IN (SELECT id FROM `Molecule`)";
1183 $query[] = "ALTER TABLE `Atom` ADD CONSTRAINT `Atom-FK-molecule_id` FOREIGN KEY (`molecule_id`) REFERENCES `Molecule` (`id`) ON DELETE CASCADE";
1184 $query[] = "ALTER TABLE `Atom` ADD CONSTRAINT `Atom-FK-rack_id` FOREIGN KEY (`rack_id`) REFERENCES `Object` (`id`) ON DELETE CASCADE";
1185 $query[] = "ALTER TABLE `MountOperation` ADD CONSTRAINT `MountOperation-FK-old_molecule_id` FOREIGN KEY (`old_molecule_id`) REFERENCES `Molecule` (`id`) ON DELETE CASCADE";
1186 $query[] = "ALTER TABLE `MountOperation` ADD CONSTRAINT `MountOperation-FK-new_molecule_id` FOREIGN KEY (`new_molecule_id`) REFERENCES `Molecule` (`id`) ON DELETE CASCADE";
14a9812c
DO
1187 # multiple Cacti servers
1188 $query[] = "
1189CREATE TABLE `CactiServer` (
1190 `id` int(10) unsigned NOT NULL auto_increment,
1191 `base_url` char(255) DEFAULT NULL,
1192 `username` char(64) DEFAULT NULL,
1193 `password` char(64) DEFAULT NULL,
1194 PRIMARY KEY (`id`)
1195) ENGINE=InnoDB
1196";
1197 $query[] = "ALTER TABLE CactiGraph ADD COLUMN server_id int(10) unsigned NOT NULL AFTER object_id";
7b9f0379 1198
14a9812c 1199 $result = $dbxlink->query ('SELECT COUNT(*) AS cnt FROM CactiGraph');
a1c4ba2f 1200 $row = $result->fetch (PDO::FETCH_ASSOC);
14a9812c 1201 unset ($result);
7b9f0379
AA
1202
1203 $result = $dbxlink->query ("SELECT varvalue FROM Config WHERE varname = 'CACTI_URL'");
1204 $cacti_url_row = $result->fetch (PDO::FETCH_ASSOC);
1205 unset ($result);
1206
1207 if ($row['cnt'] != 0 || is_array ($cacti_url_row) && strlen ($cacti_url_row['varvalue']))
14a9812c
DO
1208 {
1209 $query[] = "INSERT INTO CactiServer (id) VALUES (1)";
1210 $query[] = "UPDATE CactiServer SET base_url = (SELECT varvalue FROM Config WHERE varname = 'CACTI_URL') WHERE id = 1";
1211 $query[] = "UPDATE CactiServer SET username = (SELECT varvalue FROM Config WHERE varname = 'CACTI_USERNAME') WHERE id = 1";
1212 $query[] = "UPDATE CactiServer SET password = (SELECT varvalue FROM Config WHERE varname = 'CACTI_USERPASS') WHERE id = 1";
1213 $query[] = "UPDATE CactiGraph SET server_id = 1";
1214 }
1215 $query[] = "ALTER TABLE CactiGraph DROP PRIMARY KEY";
1216 $query[] = "ALTER TABLE CactiGraph ADD PRIMARY KEY (server_id, graph_id)";
1217 $query[] = "ALTER TABLE CactiGraph ADD KEY (graph_id)";
1218 $query[] = "ALTER TABLE CactiGraph ADD CONSTRAINT `CactiGraph-FK-server_id` FOREIGN KEY (server_id) REFERENCES CactiServer (id)";
1219 $query[] = "DELETE FROM Config WHERE varname IN('CACTI_URL', 'CACTI_USERNAME', 'CACTI_USERPASS')";
e9893a88
AD
1220 $query[] = "UPDATE Config SET varvalue = '0.20.1' WHERE varname = 'DB_VERSION'";
1221 break;
6e58c2c4
DO
1222 case '0.20.2':
1223 $query[] = "ALTER TABLE TagStorage ADD COLUMN tag_is_assignable ENUM('yes', 'no') NOT NULL default 'yes' AFTER tag_id";
1224 $query[] = "ALTER TABLE TagStorage ADD KEY `tag_id-tag_is_assignable` (tag_id, tag_is_assignable)";
1225 $query[] = "ALTER TABLE TagTree ADD COLUMN is_assignable ENUM('yes', 'no') NOT NULL default 'yes' AFTER parent_id";
1226 $query[] = "ALTER TABLE TagTree ADD KEY `id-is_assignable` (id, is_assignable)";
1227 $query[] = "ALTER TABLE TagStorage DROP FOREIGN KEY `TagStorage-FK-tag_id`";
1228 $query[] = "ALTER TABLE TagStorage ADD CONSTRAINT `TagStorage-FK-TagTree` FOREIGN KEY (tag_id, tag_is_assignable) REFERENCES TagTree (id, is_assignable)";
2818e5d9 1229 $query[] = "UPDATE UserAccount SET user_realname = NULL WHERE user_realname = ''";
cc7f0b09 1230 $query[] = "UPDATE Object SET comment = NULL WHERE comment = ''";
2c691f71
MH
1231 $query[] = "
1232CREATE TABLE `MuninServer` (
1233 `id` int(10) unsigned NOT NULL auto_increment,
1234 `base_url` char(255) DEFAULT NULL,
1235 PRIMARY KEY (`id`)
48ecb468 1236) ENGINE=InnoDB
2c691f71
MH
1237";
1238 $query[] = "
1239CREATE TABLE `MuninGraph` (
1240 `object_id` int(10) unsigned NOT NULL,
1241 `server_id` int(10) unsigned NOT NULL,
1242 `graph` char(255) NOT NULL,
1243 `caption` char(255) DEFAULT NULL,
1244 PRIMARY KEY (`object_id`,`server_id`,`graph`),
1245 KEY `server_id` (`server_id`),
1246 KEY `graph` (`graph`),
1247 CONSTRAINT `MuninGraph-FK-server_id` FOREIGN KEY (`server_id`) REFERENCES `MuninServer` (`id`),
1248 CONSTRAINT `MuninGraph-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `Object` (`id`) ON DELETE CASCADE
48ecb468 1249) ENGINE=InnoDB
2c691f71
MH
1250";
1251 $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
1252 $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')";
1253 $query[] = "ALTER TABLE VLANIPv4 ADD UNIQUE `network-domain-vlan` (ipv4net_id, domain_id, vlan_id)";
1254 $query[] = "ALTER TABLE VLANIPv4 DROP KEY `network-domain`";
1255 $query[] = "ALTER TABLE VLANIPv6 ADD UNIQUE `network-domain-vlan` (ipv6net_id, domain_id, vlan_id)";
1256 $query[] = "ALTER TABLE VLANIPv6 DROP KEY `network-domain`";
48ecb468 1257 $query[] = "UPDATE Config SET varvalue = '0.20.2' WHERE varname = 'DB_VERSION'";
6e58c2c4 1258 break;
56a28368
AA
1259 case '0.20.3':
1260 $query[] = "UPDATE Config SET varvalue = '0.20.3' WHERE varname = 'DB_VERSION'";
1261 break;
4dcd770e 1262 case '0.20.4':
6ccfd4bd 1263 $query[] = "ALTER TABLE `FileLink` MODIFY COLUMN `entity_type` ENUM('ipv4net','ipv4rspool','ipv4vs','ipv6net','location','object','rack','row','user') NOT NULL DEFAULT 'object'";
c495997f 1264 $query[] = "ALTER TABLE `RackSpace` MODIFY COLUMN `state` ENUM('A','U','T') NOT NULL default 'A'";
4dcd770e 1265 $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 1266 $query[] = "UPDATE `Config` SET varvalue = CONCAT(varvalue, ' or {\$typeid_965}') WHERE varname = 'IPV4OBJ_LISTSRC'";
ef550f27
AA
1267 $query[] = "UPDATE AttributeValue INNER JOIN AttributeMap USING (attr_id) SET AttributeValue.uint_value = 1572 WHERE chapter_id = 12 AND uint_value = 162";
1268 $query[] = "UPDATE AttributeValue INNER JOIN AttributeMap USING (attr_id) SET AttributeValue.uint_value = 1710 WHERE chapter_id = 12 AND uint_value = 163";
809d2ba9 1269 $query[] = "UPDATE Config SET varvalue = '%Y-%m-%d', description='PHP strftime() format to use for date output' WHERE varname = 'DATETIME_FORMAT'";
6b1ca530 1270 $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
1271 $query[] = "UPDATE Config SET varvalue = '0.20.4' WHERE varname = 'DB_VERSION'";
1272 break;
de99667b 1273 case '0.20.5':
d016010a
AD
1274 $query[] = "
1275CREATE OR REPLACE VIEW `Rack` AS SELECT O.id, O.name AS name, O.asset_no, O.has_problems, O.comment,
1276 AV_H.uint_value AS height,
1277 AV_S.uint_value AS sort_order,
1278 RT.thumb_data,
1279 R.id AS row_id,
1280 R.name AS row_name,
1281 L.id AS location_id,
1282 L.name AS location_name
1283 FROM `Object` O
1284 LEFT JOIN `AttributeValue` AV_H ON O.id = AV_H.object_id AND AV_H.attr_id = 27
1285 LEFT JOIN `AttributeValue` AV_S ON O.id = AV_S.object_id AND AV_S.attr_id = 29
1286 LEFT JOIN `RackThumbnail` RT ON O.id = RT.rack_id
1287 LEFT JOIN `EntityLink` RL ON O.id = RL.child_entity_id AND RL.parent_entity_type = 'row' AND RL.child_entity_type = 'rack'
1288 INNER JOIN `Object` R ON R.id = RL.parent_entity_id
1289 LEFT JOIN `EntityLink` LL ON R.id = LL.child_entity_id AND LL.parent_entity_type = 'location' AND LL.child_entity_type = 'row'
1290 LEFT JOIN `Object` L ON L.id = LL.parent_entity_id
1291 WHERE O.objtype_id = 1560
1292";
1293
f9fcce59
AD
1294 // prevent some AttributeMap entries from being deleted
1295 $query[] = "ALTER TABLE AttributeMap ADD COLUMN sticky enum('yes','no') default 'no'";
1296 $query[] = "UPDATE AttributeMap SET sticky = 'yes' WHERE objtype_id = 4 AND attr_id IN (26,28)"; // Server -> Hypervisor, Slot number
1297 $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
1298 $query[] = "UPDATE AttributeMap SET sticky = 'yes' WHERE objtype_id = 798 AND attr_id = 28"; // Network security -> Slot number
1299 $query[] = "UPDATE AttributeMap SET sticky = 'yes' WHERE objtype_id = 1055 AND attr_id = 28"; // FC switch -> Slot number
1300 $query[] = "UPDATE AttributeMap SET sticky = 'yes' WHERE objtype_id = 1560 AND attr_id IN (27,29)"; // Rack -> Height, Sort order
1301 $query[] = "UPDATE AttributeMap SET sticky = 'yes' WHERE objtype_id = 1787 AND attr_id = 30"; // Management interface -> Mgmt type
1302
de99667b 1303 $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
1304
1305 // SLB v2 tables
1306 $query[] = "
1307CREATE TABLE `VS` (
1308 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
1309 `name` char(255) DEFAULT NULL,
1310 `vsconfig` text,
1311 `rsconfig` text,
1312 PRIMARY KEY (`id`)
1313) ENGINE=InnoDB
1314";
1315 $query[] = "
1316CREATE TABLE `VSIPs` (
1317 `vs_id` int(10) unsigned NOT NULL,
1318 `vip` varbinary(16) NOT NULL,
1319 `vsconfig` text,
1320 `rsconfig` text,
1321 PRIMARY KEY (`vs_id`,`vip`),
1322 KEY `vip` (`vip`),
1323 CONSTRAINT `VSIPs-vs_id` FOREIGN KEY (`vs_id`) REFERENCES `VS` (`id`) ON DELETE CASCADE
1324) ENGINE=InnoDB
1325";
1326 $query[] = "
1327CREATE TABLE `VSPorts` (
1328 `vs_id` int(10) unsigned NOT NULL,
66844484 1329 `proto` enum('TCP','UDP','MARK') NOT NULL,
eacc0983
AA
1330 `vport` int(10) unsigned NOT NULL,
1331 `vsconfig` text,
1332 `rsconfig` text,
1333 PRIMARY KEY (`vs_id`,`proto`,`vport`),
66844484 1334 KEY `proto-vport` (`proto`,`vport`),
eacc0983
AA
1335 CONSTRAINT `VS-vs_id` FOREIGN KEY (`vs_id`) REFERENCES `VS` (`id`) ON DELETE CASCADE
1336) ENGINE=InnoDB
1337";
1338 $query[] = "
1339CREATE TABLE `VSEnabledIPs` (
1340 `object_id` int(10) unsigned NOT NULL,
1341 `vs_id` int(10) unsigned NOT NULL,
1342 `vip` varbinary(16) NOT NULL,
1343 `rspool_id` int(10) unsigned NOT NULL,
1344 `prio` varchar(255) DEFAULT NULL,
1345 `vsconfig` text,
1346 `rsconfig` text,
66844484 1347 PRIMARY KEY (`object_id`,`vs_id`,`vip`,`rspool_id`),
eacc0983
AA
1348 KEY `vip` (`vip`),
1349 KEY `VSEnabledIPs-FK-vs_id-vip` (`vs_id`,`vip`),
1350 KEY `VSEnabledIPs-FK-rspool_id` (`rspool_id`),
1351 CONSTRAINT `VSEnabledIPs-FK-rspool_id` FOREIGN KEY (`rspool_id`) REFERENCES `IPv4RSPool` (`id`) ON DELETE CASCADE,
1352 CONSTRAINT `VSEnabledIPs-FK-vs_id-vip` FOREIGN KEY (`vs_id`, `vip`) REFERENCES `VSIPs` (`vs_id`, `vip`) ON DELETE CASCADE
1353) ENGINE=InnoDB
1354";
1355 $query[] = "
1356CREATE TABLE `VSEnabledPorts` (
1357 `object_id` int(10) unsigned NOT NULL,
1358 `vs_id` int(10) unsigned NOT NULL,
66844484 1359 `proto` enum('TCP','UDP','MARK') NOT NULL,
eacc0983
AA
1360 `vport` int(10) unsigned NOT NULL,
1361 `rspool_id` int(10) unsigned NOT NULL,
1362 `vsconfig` text,
1363 `rsconfig` text,
66844484 1364 PRIMARY KEY (`object_id`,`vs_id`,`proto`,`vport`,`rspool_id`),
eacc0983
AA
1365 KEY `VSEnabledPorts-FK-vs_id-proto-vport` (`vs_id`,`proto`,`vport`),
1366 KEY `VSEnabledPorts-FK-rspool_id` (`rspool_id`),
1367 CONSTRAINT `VSEnabledPorts-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `Object` (`id`) ON DELETE CASCADE,
1368 CONSTRAINT `VSEnabledPorts-FK-rspool_id` FOREIGN KEY (`rspool_id`) REFERENCES `IPv4RSPool` (`id`) ON DELETE CASCADE,
1369 CONSTRAINT `VSEnabledPorts-FK-vs_id-proto-vport` FOREIGN KEY (`vs_id`, `proto`, `vport`) REFERENCES `VSPorts` (`vs_id`, `proto`, `vport`) ON DELETE CASCADE
1370) ENGINE=InnoDB
1371";
4b5da861
AA
1372 $query[] = "ALTER TABLE `EntityLink` MODIFY COLUMN `parent_entity_type` ENUM('ipv4net','ipv4rspool','ipv4vs','ipv6net','location','object','rack','row','user') NOT NULL";
1373 $query[] = "ALTER TABLE `FileLink` MODIFY COLUMN `entity_type` ENUM('ipv4net','ipv4rspool','ipv4vs','ipv6net','location','object','rack','row','user') NOT NULL DEFAULT 'object'";
1374 $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 1375 $query[] = "ALTER TABLE `UserConfig` DROP FOREIGN KEY `UserConfig-FK-user`";
f9fcce59 1376 $query[] = "UPDATE Config SET varvalue = '0.20.5' WHERE varname = 'DB_VERSION'";
de99667b 1377 break;
ceebebb9 1378 case '0.20.6':
18ff3d26
AD
1379 // one HW type was moved from the 'Network switch' chapter to the 'Network chassis' chapter
1380 // change the type of affected objects to 'Network chassis'
1381 $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
1382
1383 // convert values of old 'TELNET_OBJS_LISTSRC' 'SSH_OBJS_LISTSRC', 'RDP_OBJS_LISTSRC' variables into 'MGMT_PROTOS'
1384 $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')";
1385 if ('' !== $mgmt_converted_var = convertMgmtConfigVars())
1386 $query[] = "UPDATE `Config` SET varvalue = '" . mysql_escape_string ($mgmt_converted_var) . "' WHERE varname = 'MGMT_PROTOS'"; // TODO: call of deprecated function
1387 $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
1388
1389 $query[] = "ALTER TABLE `VSEnabledIPs` ADD CONSTRAINT `VSEnabledIPs-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `Object` (`id`) ON DELETE CASCADE";
1390
5d8afb66 1391 $query[] = "DELETE FROM Config WHERE varname = 'PORTS_PER_ROW'";
ceebebb9 1392 $query[] = "UPDATE Config SET varvalue = '0.20.6' WHERE varname = 'DB_VERSION'";
a7390942 1393 break;
25355e1d 1394 case '0.20.7':
7740234b
AD
1395 if (! isInnoDBSupported ())
1396 {
1397 showUpgradeError ('Cannot upgrade because triggers are not supported by your MySQL server.', __FUNCTION__);
1398 die;
1399 }
1400
18733c4a
AD
1401 // for the UNIQUE key to work, portb needs to be > porta
1402 $result = $dbxlink->query ('SELECT porta, portb FROM `Link` WHERE porta > portb');
1403 $links = $result->fetchAll (PDO::FETCH_ASSOC);
1404 unset ($result);
1405 foreach ($links as $link)
1406 $query[] = "UPDATE `Link` SET `porta`=${link['portb']}, `portb`=${link['porta']} WHERE `porta`=${link['porta']} AND `portb`=${link['portb']}";
1407
1408 // add triggers
645a682a
AD
1409 $query[] = "
1410CREATE TRIGGER `EntityLink-before-insert` BEFORE INSERT ON `EntityLink` FOR EACH ROW
18733c4a
AD
1411EntityLinkTrigger:BEGIN
1412 DECLARE parent_objtype, child_objtype, count INTEGER;
1413
1414 # forbid linking an entity to itself
1415 IF NEW.parent_entity_type = NEW.child_entity_type AND NEW.parent_entity_id = NEW.child_entity_id THEN
1416 SET NEW.parent_entity_id = NULL;
1417 LEAVE EntityLinkTrigger;
1418 END IF;
1419
1420 # in some scenarios, only one parent is allowed
1421 CASE CONCAT(NEW.parent_entity_type, '.', NEW.child_entity_type)
1422 WHEN 'location.location' THEN
1423 SELECT COUNT(*) INTO count FROM EntityLink WHERE parent_entity_type = 'location' AND child_entity_type = 'location' AND child_entity_id = NEW.child_entity_id;
1424 WHEN 'location.row' THEN
1425 SELECT COUNT(*) INTO count FROM EntityLink WHERE parent_entity_type = 'location' AND child_entity_type = 'row' AND child_entity_id = NEW.child_entity_id;
1426 WHEN 'row.rack' THEN
1427 SELECT COUNT(*) INTO count FROM EntityLink WHERE parent_entity_type = 'row' AND child_entity_type = 'rack' AND child_entity_id = NEW.child_entity_id;
1428 ELSE
1429 # some other scenario, assume it is valid
1430 SET count = 0;
1431 END CASE;
1432 IF count > 0 THEN
1433 SET NEW.parent_entity_id = NULL;
1434 LEAVE EntityLinkTrigger;
1435 END IF;
1436
1437 IF NEW.parent_entity_type = 'object' AND NEW.child_entity_type = 'object' THEN
1438 # lock objects to prevent concurrent link establishment
1439 SELECT objtype_id INTO parent_objtype FROM Object WHERE id = NEW.parent_entity_id FOR UPDATE;
1440 SELECT objtype_id INTO child_objtype FROM Object WHERE id = NEW.child_entity_id FOR UPDATE;
1441
1442 # only permit the link if object types are compatibile
1443 SELECT COUNT(*) INTO count FROM ObjectParentCompat WHERE parent_objtype_id = parent_objtype AND child_objtype_id = child_objtype;
1444 IF count = 0 THEN
1445 SET NEW.parent_entity_id = NULL;
1446 END IF;
1447 END IF;
1448END;
645a682a
AD
1449";
1450 $query[] = "
1451CREATE TRIGGER `EntityLink-before-update` BEFORE UPDATE ON `EntityLink` FOR EACH ROW
1452EntityLinkTrigger:BEGIN
1453 DECLARE parent_objtype, child_objtype, count INTEGER;
1454
1455 # forbid linking an entity to itself
1456 IF NEW.parent_entity_type = NEW.child_entity_type AND NEW.parent_entity_id = NEW.child_entity_id THEN
1457 SET NEW.parent_entity_id = NULL;
1458 LEAVE EntityLinkTrigger;
1459 END IF;
1460
1461 # in some scenarios, only one parent is allowed
1462 CASE CONCAT(NEW.parent_entity_type, '.', NEW.child_entity_type)
1463 WHEN 'location.location' THEN
1464 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;
1465 WHEN 'location.row' THEN
1466 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;
1467 WHEN 'row.rack' THEN
1468 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;
1469 ELSE
1470 # some other scenario, assume it is valid
1471 SET count = 0;
1472 END CASE;
1473 IF count > 0 THEN
1474 SET NEW.parent_entity_id = NULL;
1475 LEAVE EntityLinkTrigger;
1476 END IF;
1477
1478 IF NEW.parent_entity_type = 'object' AND NEW.child_entity_type = 'object' THEN
1479 # lock objects to prevent concurrent link establishment
1480 SELECT objtype_id INTO parent_objtype FROM Object WHERE id = NEW.parent_entity_id FOR UPDATE;
1481 SELECT objtype_id INTO child_objtype FROM Object WHERE id = NEW.child_entity_id FOR UPDATE;
1482
1483 # only permit the link if object types are compatibile
1484 SELECT COUNT(*) INTO count FROM ObjectParentCompat WHERE parent_objtype_id = parent_objtype AND child_objtype_id = child_objtype;
1485 IF count = 0 THEN
1486 SET NEW.parent_entity_id = NULL;
1487 END IF;
1488 END IF;
1489END;
1490";
7740234b 1491 $link_trigger_body = <<<ENDOFTRIGGER
18733c4a 1492LinkTrigger:BEGIN
7740234b
AD
1493 DECLARE tmp, porta_type, portb_type, count INTEGER;
1494
1495 IF NEW.porta = NEW.portb THEN
1496 # forbid connecting a port to itself
1497 SET NEW.porta = NULL;
18733c4a 1498 LEAVE LinkTrigger;
7740234b
AD
1499 ELSEIF NEW.porta > NEW.portb THEN
1500 # force porta < portb
1501 SET tmp = NEW.porta;
1502 SET NEW.porta = NEW.portb;
1503 SET NEW.portb = tmp;
1504 END IF;
1505
1506 # lock ports to prevent concurrent link establishment
1507 SELECT type INTO porta_type FROM Port WHERE id = NEW.porta FOR UPDATE;
1508 SELECT type INTO portb_type FROM Port WHERE id = NEW.portb FOR UPDATE;
1509
1510 # only permit the link if ports are compatibile
1511 SELECT COUNT(*) INTO count FROM PortCompat WHERE (type1 = porta_type AND type2 = portb_type) OR (type1 = portb_type AND type2 = porta_type);
1512 IF count = 0 THEN
1513 SET NEW.porta = NULL;
1514 END IF;
1515END;
1516ENDOFTRIGGER;
1517 $query[] = "CREATE TRIGGER `Link-before-insert` BEFORE INSERT ON `Link` FOR EACH ROW $link_trigger_body";
1518 $query[] = "CREATE TRIGGER `Link-before-update` BEFORE UPDATE ON `Link` FOR EACH ROW $link_trigger_body";
1519
25355e1d
AD
1520 // enable IP addressing for all object types unless specifically excluded
1521 $query[] = "UPDATE `Config` SET varvalue = 'not ({\$typeid_3} or {\$typeid_9} or {\$typeid_10} or {\$typeid_11})' WHERE varname = 'IPV4OBJ_LISTSRC'";
2437f548 1522
42fb3aa2
AD
1523 $query[] = "ALTER TABLE `EntityLink` MODIFY COLUMN `parent_entity_type` ENUM('location','object','rack','row') NOT NULL";
1524 $query[] = "ALTER TABLE `EntityLink` MODIFY COLUMN `child_entity_type` ENUM('location','object','rack','row') NOT NULL";
1525
75e7c0c6
DO
1526 $query[] = "UPDATE Config SET description = 'List source: objects for that asset tag should be set' WHERE varname = 'ASSETWARN_LISTSRC'";
1527 $query[] = "UPDATE Config SET description = 'List source: objects for that common name should be set' WHERE varname = 'NAMEWARN_LISTSRC'";
9067c07f 1528 $query[] = "ALTER TABLE `IPv4NAT` MODIFY COLUMN `proto` ENUM('TCP','UDP','ALL')";
2f96a91a
AA
1529
1530 // add new 'point2point' alloc type
1531 $query[] = "ALTER TABLE `IPv4Allocation` MODIFY `type` enum('regular','shared','virtual','router','point2point') NOT NULL DEFAULT 'regular'";
1532 $query[] = "ALTER TABLE `IPv6Allocation` MODIFY `type` enum('regular','shared','virtual','router','point2point') NOT NULL DEFAULT 'regular'";
1533
b4bc5582
TU
1534 // update to use utf8_unicode_ci collation
1535 // http://bugs.racktables.org/view.php?id=837
1536 $query[] = "ALTER DATABASE DEFAULT CHARACTER SET UTF8 COLLATE utf8_unicode_ci";
1537
1538 $query[] = "SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0";
1539 $query[] = "ALTER TABLE `Atom` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
1540 $query[] = "ALTER TABLE `Attribute` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
1541 $query[] = "ALTER TABLE `AttributeMap` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
1542 $query[] = "ALTER TABLE `AttributeValue` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
1543 $query[] = "ALTER TABLE `CachedPAV` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
1544 $query[] = "ALTER TABLE `CachedPNV` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
1545 $query[] = "ALTER TABLE `CachedPVM` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
1546 $query[] = "ALTER TABLE `CactiGraph` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
1547 $query[] = "ALTER TABLE `CactiServer` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
1548 $query[] = "ALTER TABLE `Chapter` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
1549 $query[] = "ALTER TABLE `Config` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
1550 $query[] = "ALTER TABLE `Dictionary` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
1551 $query[] = "ALTER TABLE `EntityLink` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
1552 $query[] = "ALTER TABLE `File` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
1553 $query[] = "ALTER TABLE `FileLink` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
1554 $query[] = "ALTER TABLE `IPv4Address` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
1555 $query[] = "ALTER TABLE `IPv4Allocation` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
1556 $query[] = "ALTER TABLE `IPv4LB` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
1557 $query[] = "ALTER TABLE `IPv4Log` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
1558 $query[] = "ALTER TABLE `IPv6Log` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
1559 $query[] = "ALTER TABLE `IPv4NAT` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
1560 $query[] = "ALTER TABLE `IPv4Network` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
1561 $query[] = "ALTER TABLE `IPv4RS` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
1562 $query[] = "ALTER TABLE `IPv4RSPool` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
1563 $query[] = "ALTER TABLE `IPv4VS` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
1564 $query[] = "ALTER TABLE `IPv6Address` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
1565 $query[] = "ALTER TABLE `IPv6Allocation` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
1566 $query[] = "ALTER TABLE `IPv6Network` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
1567 $query[] = "ALTER TABLE `LDAPCache` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
1568 $query[] = "ALTER TABLE `Link` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
1569 $query[] = "ALTER TABLE `Molecule` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
1570 $query[] = "ALTER TABLE `MountOperation` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
1571 $query[] = "ALTER TABLE `MuninGraph` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
1572 $query[] = "ALTER TABLE `MuninServer` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
1573 $query[] = "ALTER TABLE `ObjectLog` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
1574 $query[] = "ALTER TABLE `ObjectParentCompat` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
1575 $query[] = "ALTER TABLE `Port` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
1576 $query[] = "ALTER TABLE `PortAllowedVLAN` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
1577 $query[] = "ALTER TABLE `PortCompat` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
1578 $query[] = "ALTER TABLE `PortInnerInterface` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
1579 $query[] = "ALTER TABLE `PortInterfaceCompat` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
1580 $query[] = "ALTER TABLE `PortLog` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
1581 $query[] = "ALTER TABLE `PortNativeVLAN` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
1582 $query[] = "ALTER TABLE `PortVLANMode` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
1583 $query[] = "ALTER TABLE `Object` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
1584 $query[] = "ALTER TABLE `ObjectHistory` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
1585 $query[] = "ALTER TABLE `RackSpace` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
1586 $query[] = "ALTER TABLE `RackThumbnail` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
1587 $query[] = "ALTER TABLE `Script` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
1588 $query[] = "ALTER TABLE `TagStorage` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
1589 $query[] = "ALTER TABLE `TagTree` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
1590 $query[] = "ALTER TABLE `UserAccount` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
1591 $query[] = "ALTER TABLE `UserConfig` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
1592 $query[] = "ALTER TABLE `VLANDescription` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
1593 $query[] = "ALTER TABLE `VLANDomain` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
1594 $query[] = "ALTER TABLE `VLANIPv4` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
1595 $query[] = "ALTER TABLE `VLANIPv6` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
1596 $query[] = "ALTER TABLE `VLANSTRule` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
1597 $query[] = "ALTER TABLE `VLANSwitch` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
1598 $query[] = "ALTER TABLE `VLANSwitchTemplate` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
1599 $query[] = "ALTER TABLE `VLANValidID` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
1600 $query[] = "ALTER TABLE `VS` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
1601 $query[] = "ALTER TABLE `VSIPs` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
1602 $query[] = "ALTER TABLE `VSPorts` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
1603 $query[] = "ALTER TABLE `VSEnabledIPs` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
1604 $query[] = "ALTER TABLE `VSEnabledPorts` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
1605 $query[] = "SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS";
1606
2437f548 1607 $query[] = "UPDATE Config SET varvalue = '0.20.7' WHERE varname = 'DB_VERSION'";
25355e1d 1608 break;
5c8f1469
DO
1609 case '0.20.8':
1610 $query[] = "
1611CREATE TABLE `PortOuterInterface` (
1612 `id` int(10) unsigned NOT NULL auto_increment,
1613 `oif_name` char(48) NOT NULL,
1614 PRIMARY KEY (`id`),
1615 UNIQUE KEY `oif_name` (`oif_name`)
4d02ca1a 1616) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
5c8f1469
DO
1617";
1618 $query[] = "INSERT INTO PortOuterInterface SELECT dict_key, dict_value FROM Dictionary WHERE chapter_id = 2";
1619 // Previously listed 10GBase-Kx actually means two standards: 10GBase-KX4
1620 // and 10GBase-KR. Make respective changes and make primary key auto
1621 // increment start at 2000.
1622 $query[] = "UPDATE PortOuterInterface SET oif_name = '10GBase-KX4' WHERE id = 41";
1623 $query[] = "INSERT INTO PortOuterInterface (id, oif_name) VALUES (1999, '10GBase-KR')";
1624 $query[] = "INSERT INTO PortCompat (type1, type2) VALUES (1999, 1999)";
1625 $query[] = "DELETE FROM Dictionary WHERE chapter_id = 2";
1626 $query[] = "DELETE FROM Chapter WHERE id = 2";
1627 $query[] = "ALTER TABLE PortInterfaceCompat ADD CONSTRAINT `PortInterfaceCompat-FK-oif_id` FOREIGN KEY (oif_id) REFERENCES PortOuterInterface (id)";
1628 $query[] = "ALTER TABLE PortCompat ADD CONSTRAINT `PortCompat-FK-oif_id1` FOREIGN KEY (type1) REFERENCES PortOuterInterface (id)";
1629 $query[] = "ALTER TABLE PortCompat ADD CONSTRAINT `PortCompat-FK-oif_id2` FOREIGN KEY (type2) REFERENCES PortOuterInterface (id)";
a3a3bc1a
DO
1630 // Add more 40G and 100G standards.
1631 $query[] = "INSERT INTO PortOuterInterface (id, oif_name) VALUES
1632(1660,'40GBase-FR'),
1633(1662,'40GBase-ER4'),
1634(1672,'100GBase-SR4'),
1635(1673,'100GBase-KR4'),
1636(1674,'100GBase-KP4')";
1637 $query[] = "INSERT INTO PortInterfaceCompat (iif_id, oif_id) VALUES
1638(10,1660),
1639(10,1662),
1640(11,1672),
1641(11,1673),
1642(11,1674)";
1643 $query[] = "INSERT INTO PortCompat (type1, type2) VALUES
1644(1660,1660),
1645(1662,1662),
1646(1672,1672),
1647(1673,1673),
1648(1674,1674)";
cafe2cee
DO
1649 // Refine 1G OIF list: fix spelling and add a new standard.
1650 $query[] = "UPDATE PortOuterInterface SET oif_name = '1000Base-LX10' WHERE id = 1205";
1651 $query[] = "INSERT INTO PortOuterInterface (id, oif_name) VALUES (42, '1000Base-EX')";
1652 $query[] = "INSERT INTO PortCompat (type1, type2) VALUES (42, 42)";
1653 $query[] = "INSERT INTO PortInterfaceCompat (iif_id, oif_id) VALUES (3, 42), (4,42)";
5c8f1469
DO
1654 $query[] = "UPDATE Config SET varvalue = '0.20.8' WHERE varname = 'DB_VERSION'";
1655 break;
d15dae2f
DO
1656 case 'dictionary':
1657 $query = reloadDictionary();
1658 break;
fbbb74fb 1659 default:
87ae30c5
AD
1660 return NULL;
1661 }
1662 return $query;
1663}
1664
1665function executeUpgradeBatch ($batchid)
1666{
1667 global $dbxlink;
1668 $query = getUpgradeBatch($batchid);
a20a4e3c
DO
1669 if ($query === NULL)
1670 {
87ae30c5
AD
1671 showError ("unknown batch '${batchid}'", __FUNCTION__);
1672 die;
fbbb74fb 1673 }
fbbb74fb 1674 $failures = array();
4114697d 1675 echo "<tr><th>Executing batch '${batchid}'</th><td>";
fbbb74fb
DO
1676 foreach ($query as $q)
1677 {
babe4bf5
AA
1678 try
1679 {
1680 $result = $dbxlink->query ($q);
1681 }
1682 catch (PDOException $e)
758fe24c 1683 {
758fe24c
DO
1684 $errorInfo = $dbxlink->errorInfo();
1685 $failures[] = array ($q, $errorInfo[2]);
1686 }
fbbb74fb 1687 }
fbbb74fb 1688 if (!count ($failures))
4114697d 1689 echo "<strong><font color=green>done</font></strong>";
fbbb74fb
DO
1690 else
1691 {
4114697d 1692 echo "<strong><font color=red>The following queries failed:</font></strong><br><pre>";
fbbb74fb
DO
1693 foreach ($failures as $f)
1694 {
1695 list ($q, $i) = $f;
4114697d 1696 echo "${q} -- ${i}\n";
fbbb74fb 1697 }
4114697d 1698 echo "</pre>";
fbbb74fb 1699 }
4114697d 1700 echo '</td></tr>';
fbbb74fb
DO
1701}
1702
120e9ddd 1703function authenticate_admin ($username, $password)
a1f3710a 1704{
43c7895d 1705 global $dbxlink;
4dd08c61
DO
1706 $prepared = $dbxlink->prepare ('SELECT COUNT(*) FROM UserAccount WHERE user_id=1 AND user_name=? AND user_password_hash=?');
1707 if (!$prepared->execute (array ($username, sha1 ($password))))
a1f3710a 1708 die ('SQL query failed in ' . __FUNCTION__);
4dd08c61 1709 $rows = $prepared->fetchAll (PDO::FETCH_NUM);
43c7895d 1710 return $rows[0][0] == 1;
a1f3710a
DO
1711}
1712
c4ee2310
DO
1713// Database version detector. Should behave corretly on any
1714// working dataset a user might have.
1715function getDatabaseVersion ()
1716{
2f5e4db9
DO
1717 global $dbxlink;
1718 $prepared = $dbxlink->prepare ('SELECT varvalue FROM Config WHERE varname = "DB_VERSION" and vartype = "string"');
1719 if (! $prepared->execute())
c4ee2310 1720 {
c4ee2310 1721 $errorInfo = $dbxlink->errorInfo();
2f5e4db9 1722 die (__FUNCTION__ . ': SQL query failed with error ' . $errorInfo[2]);
c4ee2310 1723 }
2f5e4db9 1724 $rows = $prepared->fetchAll (PDO::FETCH_NUM);
c4ee2310
DO
1725 if (count ($rows) != 1 || !strlen ($rows[0][0]))
1726 die (__FUNCTION__ . ': Cannot guess database version. Config table is present, but DB_VERSION is missing or invalid. Giving up.');
1727 $ret = $rows[0][0];
1728 return $ret;
1729}
1730
71066ef1 1731function showUpgradeError ($info = '', $location = 'N/A')
c4ee2310
DO
1732{
1733 if (preg_match ('/\.php$/', $location))
1734 $location = basename ($location);
1735 elseif ($location != 'N/A')
1736 $location = $location . '()';
1737 echo "<div class=msg_error>An error has occured in [${location}]. ";
1738 if (!strlen ($info))
1739 echo 'No additional information is available.';
1740 else
1741 echo "Additional information:<br><p>\n<pre>\n${info}\n</pre></p>";
2f5e4db9 1742 echo "Go back or try starting from <a href='index.php'>index page</a>.<br></div>\n";
c4ee2310
DO
1743}
1744
9c64ccf8
AA
1745// changes the value of config variable. If $old_value_filter is set, value is changed only if current value equals to it.
1746function replaceConfigVarValue ($varname, $new_value, $old_value_filter = NULL)
1747{
1748 global $dbxlink;
1749 if (isset ($old_value_filter))
1750 {
1751 $result = $dbxlink->prepare ("SELECT varvalue FROM Config WHERE varname = ?");
1752 $result->execute (array ($varname));
1753 if ($row = $result->fetch (PDO::FETCH_ASSOC))
1754 if ($row['varvalue'] != $old_value_filter)
1755 return;
1756 unset ($result);
1757 }
1758 $result = $dbxlink->prepare ("UPDATE Config set varvalue = ? WHERE varname = ?");
1759 $result->execute (array ($new_value, $varname));
1760}
1761
964b0388 1762function renderUpgraderHTML()
99ee5479 1763{
b00cc78c
AA
1764 global $found_secret_file;
1765 if (! $found_secret_file)
1766 die ('<center>There is no working RackTables instance here, <a href="?module=installer">install</a>?</center>');
1767
1768 try
964b0388 1769 {
b00cc78c
AA
1770 connectDB();
1771 }
1772 catch (RackTablesError $e)
1773 {
1774 die ("Database connection failed:\n\n" . $e->getMessage());
1775 }
dec748f6 1776
b00cc78c
AA
1777 if
1778 (
1779 !isset ($_SERVER['PHP_AUTH_USER']) or
1780 !strlen ($_SERVER['PHP_AUTH_USER']) or
1781 !isset ($_SERVER['PHP_AUTH_PW']) or
1782 !strlen ($_SERVER['PHP_AUTH_PW']) or
1783 !authenticate_admin ($_SERVER['PHP_AUTH_USER'], $_SERVER['PHP_AUTH_PW'])
1784 )
1785 {
1786 header ('WWW-Authenticate: Basic realm="RackTables upgrade"');
1787 header ('HTTP/1.0 401 Unauthorized');
a931fc26
DO
1788?>
1789<h1>Trouble logging in?</h1>
f2b6ae86 1790You are trying to authenticate for the RackTables upgrade screen. This means that
a931fc26
DO
1791you must authenticate with the username and password of the main RackTables
1792administrator. There is only one such account in each installation, its default
1793username is "admin". RackTables wiki provides more information on this topic.
1794<?php
120e9ddd 1795 die;
964b0388 1796 }
fbbb74fb 1797
964b0388 1798?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
5f016d39 1799<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
2cf36003 1800<head><title>RackTables upgrade script</title>
964b0388
DO
1801<style type="text/css">
1802.tdleft {
1803 text-align: left;
1804}
1805
1806.trok {
1807 background-color: #80FF80;
1808}
1809
1810.trwarning {
1811 background-color: #FFFF80;
1812}
1813
1814.trerror {
1815 background-color: #FF8080;
1816}
1817</style>
5f016d39
DO
1818</head>
1819<body>
1820<h1>Platform check status</h1>
1821<?php
1822
1823if (!platform_is_ok())
50cc9dbe
DO
1824{
1825 echo '<h1>Please resolve the failed (red) item(s) above.</h1>';
5f016d39 1826 die ('</body></html>');
50cc9dbe 1827}
5f016d39
DO
1828
1829echo '<h1>Upgrade status</h1>';
fbbb74fb 1830$dbver = getDatabaseVersion();
5f016d39 1831echo '<table border=1 cellpadding=5>';
4114697d
DO
1832echo "<tr><th>Current status</th><td>Data version: ${dbver}<br>Code version: " . CODE_VERSION . "</td></tr>\n";
1833
1834$path = getDBUpgradePath ($dbver, CODE_VERSION);
1835if ($path === NULL)
758fe24c 1836{
4114697d 1837 echo "<tr><th>Upgrade path</th><td><font color=red>not found</font></td></tr>\n";
e4d00763
DO
1838 echo "<tr><th>Summary</th><td>Check README for more information. RackTables releases prior to 0.18.0 ";
1839 echo "must be upgraded to 0.18.0 first.</td></tr>\n";
758fe24c 1840}
4114697d 1841else
5f4027b8 1842{
4114697d
DO
1843 if (!count ($path))
1844 echo "<tr><th>Summary</th><td>Come back later.</td></tr>\n";
1845 else
1846 {
1847 echo "<tr><th>Upgrade path</th><td>${dbver} &rarr; " . implode (' &rarr; ', $path) . "</td></tr>\n";
abc799c5 1848 global $relnotes;
4114697d 1849 foreach ($path as $batchid)
4114697d 1850 if (isset ($relnotes[$batchid]))
5ae6d365 1851 echo "<tr><th>Release notes for ${batchid}</th><td><pre>" . $relnotes[$batchid] . "</pre></td></tr>\n";
a12022a9
DO
1852 if (array_key_exists ('reallyreally', $_REQUEST))
1853 {
1854 foreach ($path as $batchid)
1855 executeUpgradeBatch ($batchid);
d15dae2f 1856 executeUpgradeBatch ('dictionary');
a12022a9
DO
1857 echo "<tr><th>Summary</th><td>Upgrade complete, it is Ok to ";
1858 echo "<a href='index.php'>enter</a> the system.</td></tr>\n";
1859 }
1860 else
1861 {
1862 echo '<form method=post action="index.php?module=upgrade"><tr><th>Wait!</th>';
1863 echo '<td><p>RackTables database upgrades sometimes go wrong because of assorted reasons. ';
1864 echo 'It is <strong>highly recommended</strong> to make a database backup before ';
1865 echo 'proceeding any further. <tt>mysqldump</tt> and <tt>PHPMyAdmin</tt> are convenient ';
1866 echo 'tools for doing this.</p>';
1867 echo '<p><input type=checkbox name=reallyreally id=reallyreally><label for=reallyreally>';
1868 echo 'I am ready to bear all risks of this upgrade. I am ready to roll it back in case of ';
1869 echo 'a failure.</label> <input type=submit value="Yes, I am."></p></td></tr></form>';
4114697d 1870 }
4114697d 1871 }
5f4027b8 1872}
4114697d 1873echo '</table>';
5f016d39 1874echo '</body></html>';
964b0388 1875}
fbbb74fb 1876
71066ef1
AA
1877function convertSLBTablesToBinIPs()
1878{
1879 global $dbxlink;
1880
1881 $dbxlink->query ("DROP TABLE IF EXISTS `IPv4VS_new`, `IPv4RS_new`, `IPv4VS_old`, `IPv4RS_old`");
1882
1883 $dbxlink->query (<<<END
1884CREATE TABLE `IPv4VS_new` (
1885 `id` int(10) unsigned NOT NULL auto_increment,
1886 `vip` varbinary(16) NOT NULL,
1887 `vport` smallint(5) unsigned default NULL,
1888 `proto` enum('TCP','UDP','MARK') NOT NULL default 'TCP',
1889 `name` char(255) default NULL,
1890 `vsconfig` text,
1891 `rsconfig` text,
1892 PRIMARY KEY (`id`),
1893 KEY `vip` (`vip`)
1894) ENGINE=InnoDB DEFAULT CHARSET=utf8
1895END
1896 );
1897 $result = $dbxlink->query ("SELECT * FROM IPv4VS");
1898 $rows = $result->fetchAll (PDO::FETCH_ASSOC);
1899 unset ($result);
1900 foreach ($rows as $row)
1901 {
1902 $row['vip'] = ip4_int2bin ($row['vip']);
1903 usePreparedInsertBlade ('IPv4VS_new', $row);
1904 }
dec748f6 1905
71066ef1
AA
1906 $dbxlink->query (<<<END
1907CREATE TABLE `IPv4RS_new` (
1908 `id` int(10) unsigned NOT NULL auto_increment,
1909 `inservice` enum('yes','no') NOT NULL default 'no',
1910 `rsip` varbinary(16) NOT NULL,
1911 `rsport` smallint(5) unsigned default NULL,
1912 `rspool_id` int(10) unsigned default NULL,
1913 `rsconfig` text,
1914 `comment` varchar(255) DEFAULT NULL,
1915 PRIMARY KEY (`id`),
1916 KEY `rsip` (`rsip`),
1917 UNIQUE KEY `pool-endpoint` (`rspool_id`,`rsip`,`rsport`),
1918 CONSTRAINT `IPRS-FK` FOREIGN KEY (`rspool_id`) REFERENCES `IPv4RSPool` (`id`) ON DELETE CASCADE
1919) ENGINE=InnoDB DEFAULT CHARSET=utf8
1920END
1921 );
1922 $result = $dbxlink->query ("SELECT * FROM IPv4RS");
1923 $rows = $result->fetchAll (PDO::FETCH_ASSOC);
1924 unset ($result);
1925 foreach ($rows as $row)
1926 {
1927 $row['rsip'] = ip4_int2bin ($row['rsip']);
1928 usePreparedInsertBlade ('IPv4RS_new', $row);
1929 }
1930
1931 $dbxlink->query (<<<END
1932RENAME TABLE
1933 `IPv4VS` TO `IPv4VS_old`,
1934 `IPv4VS_new` TO `IPv4VS`,
1935 `IPv4RS` TO `IPv4RS_old`,
1936 `IPv4RS_new` TO `IPv4RS`
1937END
1938 );
1939 // re-create foreign key in IPv4LB
1940 $dbxlink->query ("ALTER TABLE `IPv4LB` DROP FOREIGN KEY `IPv4LB-FK-vs_id`");
1941 $dbxlink->query ("ALTER TABLE `IPv4LB` ADD CONSTRAINT `IPv4LB-FK-vs_id` FOREIGN KEY (`vs_id`) REFERENCES `IPv4VS` (`id`)");
1942
1943 $dbxlink->query ("DROP TABLE `IPv4VS_old`, `IPv4RS_old`");
93b7c5d8
AA
1944
1945 // re-create foreign key in IPv4RS
1946 $dbxlink->query ("ALTER TABLE `IPv4RS` DROP FOREIGN KEY `IPRS-FK`");
1947 $dbxlink->query ("ALTER TABLE `IPv4RS` ADD CONSTRAINT `IPv4RS-FK` FOREIGN KEY (`rspool_id`) REFERENCES `IPv4RSPool` (`id`) ON DELETE CASCADE");
71066ef1
AA
1948}
1949
1950// This is a swiss-knife blade to insert a record into a table.
1951// The first argument is table name.
1952// The second argument is an array of "name" => "value" pairs.
1953// returns integer - affected rows count. Throws exception on error
1954function usePreparedInsertBlade ($tablename, $columns)
1955{
1956 global $dbxlink;
1957 $query = "INSERT INTO ${tablename} (" . implode (', ', array_keys ($columns));
1958 $query .= ') VALUES (' . questionMarks (count ($columns)) . ')';
1959 // Now the query should be as follows:
1960 // INSERT INTO table (c1, c2, c3) VALUES (?, ?, ?)
1961 try
1962 {
1963 $prepared = $dbxlink->prepare ($query);
1964 $prepared->execute (array_values ($columns));
1965 return $prepared->rowCount();
1966 }
1967 catch (PDOException $e)
1968 {
1969 throw convertPDOException ($e);
1970 }
1971}
1972
fd3ef4e7
AA
1973// converts the values of old-style config vars TELNET_OBJS_LISTSRC, SSH_OBJS_LISTSRC, RDP_OBJS_LISTSRC
1974// to the format of MGMT_PROTOS (comma-separated list of "proto: rackcode" pairs)
1975function convertMgmtConfigVars()
1976{
1977 global $dbxlink;
1978 $ret = array();
1979 foreach (array ('telnet' => 'TELNET_OBJS_LISTSRC', 'ssh' => 'SSH_OBJS_LISTSRC', 'rdp' => 'RDP_OBJS_LISTSRC') as $proto => $varname)
1980 {
1981 $result = $dbxlink->prepare ("SELECT varvalue FROM Config WHERE varname = ?");
1982 $result->execute (array ($varname));
1983 if ($row = $result->fetch (PDO::FETCH_ASSOC))
1984 if ($row['varvalue'] != 'false' && $row['varvalue'] != '')
1985 $ret[] = "$proto: " . $row['varvalue'];
1986 unset ($result);
1987 }
1988 return implode (',', $ret);
1989}
1990
fbbb74fb 1991?>