some pages would not load because PHP's maximum execution time was exceeded, caused...
[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
7740234b
AD
196Database triggers are used for some data consistency measures. The database
197user account must have the 'TRIGGER' privilege, which was introduced in
198MySQL 5.1.7.
199
25355e1d
AD
200The IPV4OBJ_LISTSRC configuration option is reset to an expression which enables
201the IP addressing feature for all object types except those listed.
202ENDOFTEXT
6a40d076 203,
90b96ff6
DO
204);
205
fbbb74fb
DO
206// At the moment we assume, that for any two releases we can
207// sequentally execute all batches, that separate them, and
208// nothing will break. If this changes one day, the function
209// below will have to generate smarter upgrade paths, while
210// the upper layer will remain the same.
211// Returning an empty array means that no upgrade is necessary.
4114697d 212// Returning NULL indicates an error.
fbbb74fb
DO
213function getDBUpgradePath ($v1, $v2)
214{
a6f83a72
DO
215 $versionhistory = array
216 (
9013f05b 217 '0.18.0',
425fd829 218 '0.18.1',
298d2375 219 '0.18.2',
f6d1a7cc 220 '0.18.3',
1c5b7c84 221 '0.18.4',
92ee2b01 222 '0.18.5',
2f5e4db9 223 '0.18.6',
9fb6900d 224 '0.18.7',
16825cc8 225 '0.19.0',
1d5dd3a1 226 '0.19.1',
0abae5fb 227 '0.19.2',
03d86c03 228 '0.19.3',
1f02e311 229 '0.19.4',
2a3a1a6d
AA
230 '0.19.5',
231 '0.19.6',
86eaaa67 232 '0.19.7',
f7494e3c 233 '0.19.8',
5c0bd7de 234 '0.19.9',
f7494e3c 235 '0.19.10',
ea24fb69 236 '0.19.11',
28537080 237 '0.19.12',
86ff26ae 238 '0.19.13',
b55f913c 239 '0.19.14',
f7494e3c 240 '0.20.0',
e9893a88 241 '0.20.1',
6e58c2c4 242 '0.20.2',
56a28368 243 '0.20.3',
4dcd770e 244 '0.20.4',
f9fcce59 245 '0.20.5',
ceebebb9 246 '0.20.6',
25355e1d 247 '0.20.7',
a6f83a72 248 );
120e9ddd
DO
249 if (!in_array ($v1, $versionhistory) or !in_array ($v2, $versionhistory))
250 return NULL;
fbbb74fb 251 $skip = TRUE;
4114697d 252 $path = NULL;
154a42e5
DO
253 // foreach() below cannot handle this specific case
254 if ($v1 == $v2)
255 return array();
fbbb74fb
DO
256 // Now collect all versions > $v1 and <= $v2
257 foreach ($versionhistory as $v)
258 {
4114697d 259 if ($skip and $v == $v1)
fbbb74fb
DO
260 {
261 $skip = FALSE;
4114697d 262 $path = array();
fbbb74fb
DO
263 continue;
264 }
265 if ($skip)
266 continue;
267 $path[] = $v;
268 if ($v == $v2)
269 break;
270 }
271 return $path;
272}
273
90b96ff6
DO
274// Upgrade batches are named exactly as the release where they first appear.
275// That is simple, but seems sufficient for beginning.
87ae30c5 276function getUpgradeBatch ($batchid)
fbbb74fb
DO
277{
278 $query = array();
ca3d68bd 279 global $dbxlink;
fbbb74fb
DO
280 switch ($batchid)
281 {
9013f05b 282 case '0.18.0':
8bb69a06
DO
283 $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')";
284 $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')";
285 $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')";
286 $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
287 $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')";
288 $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')";
289 $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 290 $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 291 $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 292 $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 293 $query[] = "ALTER TABLE IPv4Network ENGINE=InnoDB";
22fdebff
DO
294 $query[] = "SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0";
295 $query[] = "
296CREATE TABLE `CachedPAV` (
297 `object_id` int(10) unsigned NOT NULL,
298 `port_name` char(255) NOT NULL,
299 `vlan_id` int(10) unsigned NOT NULL default '0',
300 PRIMARY KEY (`object_id`,`port_name`,`vlan_id`),
301 KEY `vlan_id` (`vlan_id`),
302 CONSTRAINT `CachedPAV-FK-object-port` FOREIGN KEY (`object_id`, `port_name`) REFERENCES `CachedPVM` (`object_id`, `port_name`) ON DELETE CASCADE,
303 CONSTRAINT `CachedPAV-FK-vlan_id` FOREIGN KEY (`vlan_id`) REFERENCES `VLANValidID` (`vlan_id`)
304) ENGINE=InnoDB
305";
306 $query[] = "
307CREATE TABLE `CachedPNV` (
308 `object_id` int(10) unsigned NOT NULL,
309 `port_name` char(255) NOT NULL,
310 `vlan_id` int(10) unsigned NOT NULL default '0',
311 PRIMARY KEY (`object_id`,`port_name`,`vlan_id`),
312 UNIQUE KEY `port_id` (`object_id`,`port_name`),
313 CONSTRAINT `CachedPNV-FK-compound` FOREIGN KEY (`object_id`, `port_name`, `vlan_id`) REFERENCES `CachedPAV` (`object_id`, `port_name`, `vlan_id`) ON DELETE CASCADE
314) ENGINE=InnoDB
315";
316 $query[] = "
317CREATE TABLE `CachedPVM` (
318 `object_id` int(10) unsigned NOT NULL,
319 `port_name` char(255) NOT NULL,
320 `vlan_mode` enum('access','trunk') NOT NULL default 'access',
321 PRIMARY KEY (`object_id`,`port_name`),
322 CONSTRAINT `CachedPVM-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE
323) ENGINE=InnoDB
324";
325 $query[] = "
326CREATE TABLE `PortAllowedVLAN` (
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 KEY `vlan_id` (`vlan_id`),
332 CONSTRAINT `PortAllowedVLAN-FK-object-port` FOREIGN KEY (`object_id`, `port_name`) REFERENCES `PortVLANMode` (`object_id`, `port_name`) ON DELETE CASCADE,
333 CONSTRAINT `PortAllowedVLAN-FK-vlan_id` FOREIGN KEY (`vlan_id`) REFERENCES `VLANValidID` (`vlan_id`)
334) ENGINE=InnoDB
335";
336 $query[] = "
337CREATE TABLE `PortNativeVLAN` (
338 `object_id` int(10) unsigned NOT NULL,
339 `port_name` char(255) NOT NULL,
340 `vlan_id` int(10) unsigned NOT NULL default '0',
341 PRIMARY KEY (`object_id`,`port_name`,`vlan_id`),
342 UNIQUE KEY `port_id` (`object_id`,`port_name`),
343 CONSTRAINT `PortNativeVLAN-FK-compound` FOREIGN KEY (`object_id`, `port_name`, `vlan_id`) REFERENCES `PortAllowedVLAN` (`object_id`, `port_name`, `vlan_id`) ON DELETE CASCADE
344) ENGINE=InnoDB
345";
346 $query[] = "
347CREATE TABLE `PortVLANMode` (
348 `object_id` int(10) unsigned NOT NULL,
349 `port_name` char(255) NOT NULL,
350 `vlan_mode` enum('access','trunk') NOT NULL default 'access',
351 PRIMARY KEY (`object_id`,`port_name`),
352 CONSTRAINT `PortVLANMode-FK-object-port` FOREIGN KEY (`object_id`, `port_name`) REFERENCES `CachedPVM` (`object_id`, `port_name`)
353) ENGINE=InnoDB
354";
355 $query[] = "
356CREATE TABLE `VLANDescription` (
357 `domain_id` int(10) unsigned NOT NULL,
358 `vlan_id` int(10) unsigned NOT NULL default '0',
359 `vlan_type` enum('ondemand','compulsory','alien') NOT NULL default 'ondemand',
360 `vlan_descr` char(255) default NULL,
361 PRIMARY KEY (`domain_id`,`vlan_id`),
362 KEY `vlan_id` (`vlan_id`),
219da133 363 CONSTRAINT `VLANDescription-FK-domain_id` FOREIGN KEY (`domain_id`) REFERENCES `VLANDomain` (`id`) ON DELETE CASCADE,
22fdebff
DO
364 CONSTRAINT `VLANDescription-FK-vlan_id` FOREIGN KEY (`vlan_id`) REFERENCES `VLANValidID` (`vlan_id`)
365) ENGINE=InnoDB
366";
367 $query[] = "
368CREATE TABLE `VLANDomain` (
369 `id` int(10) unsigned NOT NULL auto_increment,
370 `description` char(255) default NULL,
371 PRIMARY KEY (`id`),
372 UNIQUE KEY `description` (`description`)
373) ENGINE=InnoDB
374";
375 $query[] = "
376CREATE TABLE `VLANIPv4` (
377 `domain_id` int(10) unsigned NOT NULL,
378 `vlan_id` int(10) unsigned NOT NULL,
379 `ipv4net_id` int(10) unsigned NOT NULL,
380 UNIQUE KEY `network-domain` (`ipv4net_id`,`domain_id`),
381 KEY `VLANIPv4-FK-compound` (`domain_id`,`vlan_id`),
382 CONSTRAINT `VLANIPv4-FK-compound` FOREIGN KEY (`domain_id`, `vlan_id`) REFERENCES `VLANDescription` (`domain_id`, `vlan_id`) ON DELETE CASCADE,
383 CONSTRAINT `VLANIPv4-FK-ipv4net_id` FOREIGN KEY (`ipv4net_id`) REFERENCES `IPv4Network` (`id`) ON DELETE CASCADE
384) ENGINE=InnoDB
385";
386 $query[] = "
387CREATE TABLE `VLANSTRule` (
388 `vst_id` int(10) unsigned NOT NULL,
389 `rule_no` int(10) unsigned NOT NULL,
390 `port_pcre` char(255) NOT NULL,
391 `port_role` enum('access','trunk','uplink','downlink','none') NOT NULL default 'none',
392 `wrt_vlans` char(255) default NULL,
393 `description` char(255) default NULL,
394 UNIQUE KEY `vst-rule` (`vst_id`,`rule_no`),
395 CONSTRAINT `VLANSTRule-FK-vst_id` FOREIGN KEY (`vst_id`) REFERENCES `VLANSwitchTemplate` (`id`) ON DELETE CASCADE
396) ENGINE=InnoDB
397";
398 $query[] = "
399CREATE TABLE `VLANSwitch` (
400 `object_id` int(10) unsigned NOT NULL,
401 `domain_id` int(10) unsigned NOT NULL,
402 `template_id` int(10) unsigned NOT NULL,
403 `mutex_rev` int(10) unsigned NOT NULL default '0',
404 `out_of_sync` enum('yes','no') NOT NULL default 'yes',
405 `last_errno` int(10) unsigned NOT NULL default '0',
406 `last_change` timestamp NOT NULL default '0000-00-00 00:00:00',
407 `last_push_started` timestamp NOT NULL default '0000-00-00 00:00:00',
408 `last_push_finished` timestamp NOT NULL default '0000-00-00 00:00:00',
409 `last_error_ts` timestamp NOT NULL default '0000-00-00 00:00:00',
410 UNIQUE KEY `object_id` (`object_id`),
411 KEY `domain_id` (`domain_id`),
412 KEY `template_id` (`template_id`),
413 KEY `out_of_sync` (`out_of_sync`),
414 KEY `last_errno` (`last_errno`),
415 CONSTRAINT `VLANSwitch-FK-domain_id` FOREIGN KEY (`domain_id`) REFERENCES `VLANDomain` (`id`),
416 CONSTRAINT `VLANSwitch-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`),
417 CONSTRAINT `VLANSwitch-FK-template_id` FOREIGN KEY (`template_id`) REFERENCES `VLANSwitchTemplate` (`id`)
418) ENGINE=InnoDB
419";
420 $query[] = "
421CREATE TABLE `VLANSwitchTemplate` (
422 `id` int(10) unsigned NOT NULL auto_increment,
423 `max_local_vlans` int(10) unsigned default NULL,
424 `description` char(255) default NULL,
425 PRIMARY KEY (`id`),
426 UNIQUE KEY `description` (`description`)
427) ENGINE=InnoDB
428";
429 $query[] = "
430CREATE TABLE `VLANValidID` (
431 `vlan_id` int(10) unsigned NOT NULL default '1',
432 PRIMARY KEY (`vlan_id`)
433) ENGINE=InnoDB
434";
435 $query[] = "SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS";
fa11e5c7 436 for ($i = 1; $i <= 4094; $i++)
a401a680 437 $query[] = "INSERT INTO VLANValidID (vlan_id) VALUES (${i})";
9013f05b 438 $query[] = "UPDATE Config SET varvalue = '0.18.0' WHERE varname = 'DB_VERSION'";
08d1ef24 439 break;
425fd829 440 case '0.18.1':
b6978d62
DO
441 $query[] = "ALTER TABLE Atom ENGINE=InnoDB";
442 $query[] = "ALTER TABLE AttributeMap ENGINE=InnoDB";
443 $query[] = "ALTER TABLE Config ENGINE=InnoDB";
444 $query[] = "ALTER TABLE IPv4Address ENGINE=InnoDB";
445 $query[] = "ALTER TABLE IPv4Allocation ENGINE=InnoDB";
446 $query[] = "ALTER TABLE Molecule ENGINE=InnoDB";
447 $query[] = "ALTER TABLE MountOperation ENGINE=InnoDB";
448 $query[] = "ALTER TABLE PortCompat ENGINE=InnoDB";
449 $query[] = "ALTER TABLE Rack ENGINE=InnoDB";
450 $query[] = "ALTER TABLE RackHistory ENGINE=InnoDB";
451 $query[] = "ALTER TABLE RackObjectHistory ENGINE=InnoDB";
452 $query[] = "ALTER TABLE RackRow ENGINE=InnoDB";
453 $query[] = "ALTER TABLE RackSpace ENGINE=InnoDB";
454 $query[] = "ALTER TABLE Script ENGINE=InnoDB";
735f169f
DO
455 $query[] = "ALTER TABLE AttributeValue DROP FOREIGN KEY `AttributeValue-FK-object_id`";
456 $query[] = "ALTER TABLE AttributeValue ADD CONSTRAINT `AttributeValue-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE";
55267f67
DO
457 $query[] = "ALTER TABLE RackObjectHistory ADD KEY (id)";
458 $query[] = "ALTER TABLE RackObjectHistory ADD CONSTRAINT `RackObjectHistory-FK-object_id` FOREIGN KEY (id) REFERENCES `RackObject` (`id`) ON DELETE CASCADE";
f3552988 459 $query[] = "ALTER TABLE MountOperation ADD CONSTRAINT `MountOperation-FK-object_id` FOREIGN KEY (object_id) REFERENCES RackObject (id) ON DELETE CASCADE";
f7cec175 460 $query[] = "ALTER TABLE RackSpace ADD CONSTRAINT `RackSpace-FK-object_id` FOREIGN KEY (object_id) REFERENCES RackObject (id) ON DELETE CASCADE";
1bcfe894
DO
461 $query[] = "ALTER TABLE Link DROP FOREIGN KEY `Link-FK-a`";
462 $query[] = "ALTER TABLE Link ADD CONSTRAINT `Link-FK-a` FOREIGN KEY (`porta`) REFERENCES `Port` (`id`) ON DELETE CASCADE";
463 $query[] = "ALTER TABLE Link DROP FOREIGN KEY `Link-FK-b`";
464 $query[] = "ALTER TABLE Link ADD CONSTRAINT `Link-FK-b` FOREIGN KEY (`portb`) REFERENCES `Port` (`id`) ON DELETE CASCADE";
465 $query[] = "ALTER TABLE Port DROP FOREIGN KEY `Port-FK-object_id`";
466 $query[] = "ALTER TABLE Port ADD CONSTRAINT `Port-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE";
e7787961
DO
467 $query[] = "ALTER TABLE AttributeMap MODIFY `chapter_id` int(10) unsigned default NULL";
468 $query[] = "ALTER TABLE IPv4Address MODIFY `ip` int(10) unsigned NOT NULL default '0'";
469 $query[] = "ALTER TABLE IPv4Address MODIFY `name` char(255) NOT NULL default ''";
470 $query[] = "ALTER TABLE IPv4Allocation MODIFY `object_id` int(10) unsigned NOT NULL default '0'";
471 $query[] = "ALTER TABLE IPv4Allocation MODIFY `ip` int(10) unsigned NOT NULL default '0'";
472 $query[] = "ALTER TABLE IPv4Allocation MODIFY `name` char(255) NOT NULL default ''";
473 $query[] = "ALTER TABLE IPv4NAT MODIFY `object_id` int(10) unsigned NOT NULL default '0'";
474 $query[] = "ALTER TABLE IPv4NAT MODIFY `proto` enum('TCP','UDP') NOT NULL default 'TCP'";
475 $query[] = "ALTER TABLE IPv4NAT MODIFY `localip` int(10) unsigned NOT NULL default '0'";
476 $query[] = "ALTER TABLE IPv4NAT MODIFY `localport` smallint(5) unsigned NOT NULL default '0'";
477 $query[] = "ALTER TABLE IPv4NAT MODIFY `remoteip` int(10) unsigned NOT NULL default '0'";
478 $query[] = "ALTER TABLE IPv4NAT MODIFY `remoteport` smallint(5) unsigned NOT NULL default '0'";
479 $query[] = "ALTER TABLE IPv4Network MODIFY `ip` int(10) unsigned NOT NULL default '0'";
480 $query[] = "ALTER TABLE IPv4Network MODIFY `mask` int(10) unsigned NOT NULL default '0'";
481 $query[] = "ALTER TABLE Link MODIFY `porta` int(10) unsigned NOT NULL default '0'";
482 $query[] = "ALTER TABLE Link MODIFY `portb` int(10) unsigned NOT NULL default '0'";
483 $query[] = "ALTER TABLE MountOperation MODIFY `object_id` int(10) unsigned NOT NULL default '0'";
484 $query[] = "ALTER TABLE MountOperation MODIFY `ctime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP";
485 $query[] = "ALTER TABLE Port MODIFY `object_id` int(10) unsigned NOT NULL default '0'";
486 $query[] = "ALTER TABLE Port MODIFY `name` char(255) NOT NULL default ''";
487 $query[] = "ALTER TABLE Port MODIFY `type` int(10) unsigned NOT NULL default '0'";
488 $query[] = "ALTER TABLE PortCompat MODIFY `type1` int(10) unsigned NOT NULL default '0'";
489 $query[] = "ALTER TABLE PortCompat MODIFY `type2` int(10) unsigned NOT NULL default '0'";
490 $query[] = "ALTER TABLE RackHistory MODIFY `ctime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP";
491 $query[] = "ALTER TABLE RackObjectHistory MODIFY `ctime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP";
492 $query[] = "ALTER TABLE TagStorage MODIFY `tag_id` int(10) unsigned NOT NULL default '0'";
493 $query[] = "ALTER TABLE UserAccount MODIFY `user_name` char(64) NOT NULL default ''";
425fd829
DO
494 $query[] = "UPDATE Config SET varvalue = '0.18.1' WHERE varname = 'DB_VERSION'";
495 break;
298d2375 496 case '0.18.2':
298d2375 497 $query[] = "ALTER TABLE Rack ADD CONSTRAINT `Rack-FK-row_id` FOREIGN KEY (row_id) REFERENCES RackRow (id)";
b504972c 498 $query[] = "ALTER TABLE RackRow ADD UNIQUE KEY `name` (name)";
b49a479e
DO
499 $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')";
500 $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 501 $query[] = "UPDATE Config SET varvalue = '0.18.2' WHERE varname = 'DB_VERSION'";
298d2375 502 break;
2582446d
DO
503 case '0.18.3':
504 $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 505 $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
506 $query[] = "UPDATE Config SET varvalue = '0.18.3' WHERE varname = 'DB_VERSION'";
507 break;
3a387b0d 508 case '0.18.4':
ec523868 509 $query[] = "ALTER TABLE VLANSTRule MODIFY port_role enum('access','trunk','anymode','uplink','downlink','none') NOT NULL default 'none'";
3a387b0d
DO
510 $query[] = "UPDATE Config SET varvalue = '0.18.4' WHERE varname = 'DB_VERSION'";
511 break;
95857b5c
DO
512 case '0.18.5':
513 $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 514 $query[] = "ALTER TABLE `IPv4LB` ADD COLUMN `prio` int(10) unsigned DEFAULT NULL AFTER `vs_id`";
1ebbf889 515 $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 516 $query[] = "UPDATE Config SET varvalue = '0.18.5' WHERE varname = 'DB_VERSION'";
f2f95f99
JT
517 break;
518 case '0.18.6':
f2f95f99 519 $query[] = "UPDATE Config SET varvalue = '0.18.6' WHERE varname = 'DB_VERSION'";
95857b5c 520 break;
9fb6900d 521 case '0.18.7':
9fb6900d
DO
522 $query[] = "UPDATE Config SET varvalue = '0.18.7' WHERE varname = 'DB_VERSION'";
523 break;
d3346ce2
DO
524 case '0.19.0':
525 $query[] = 'ALTER TABLE `File` ADD `thumbnail` LONGBLOB NULL AFTER `atime`';
21ee3351
AA
526 $query[] = "
527CREATE TABLE `IPv6Address` (
528 `ip` binary(16) NOT NULL,
529 `name` char(255) NOT NULL default '',
530 `reserved` enum('yes','no') default NULL,
531 PRIMARY KEY (`ip`)
532) ENGINE=InnoDB
533";
534 $query[] = "
535CREATE TABLE `IPv6Allocation` (
536 `object_id` int(10) unsigned NOT NULL default '0',
537 `ip` binary(16) NOT NULL,
538 `name` char(255) NOT NULL default '',
539 `type` enum('regular','shared','virtual','router') default NULL,
8c7b7381
AA
540 PRIMARY KEY (`object_id`,`ip`),
541 CONSTRAINT `IPv6Allocation-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE
21ee3351
AA
542) ENGINE=InnoDB
543";
544 $query[] = "
545CREATE TABLE `IPv6Network` (
546 `id` int(10) unsigned NOT NULL auto_increment,
547 `ip` binary(16) NOT NULL,
548 `mask` int(10) unsigned NOT NULL,
549 `last_ip` binary(16) NOT NULL,
550 `name` char(255) default NULL,
551 `comment` text,
552 PRIMARY KEY (`id`),
553 UNIQUE KEY `ip` (`ip`,`mask`)
554) ENGINE=InnoDB
555";
556 $query[] = "
557CREATE TABLE `VLANIPv6` (
558 `domain_id` int(10) unsigned NOT NULL,
559 `vlan_id` int(10) unsigned NOT NULL,
560 `ipv6net_id` int(10) unsigned NOT NULL,
561 UNIQUE KEY `network-domain` (`ipv6net_id`,`domain_id`),
562 KEY `VLANIPv6-FK-compound` (`domain_id`,`vlan_id`),
563 CONSTRAINT `VLANIPv6-FK-compound` FOREIGN KEY (`domain_id`, `vlan_id`) REFERENCES `VLANDescription` (`domain_id`, `vlan_id`) ON DELETE CASCADE,
564 CONSTRAINT `VLANIPv6-FK-ipv6net_id` FOREIGN KEY (`ipv6net_id`) REFERENCES `IPv6Network` (`id`) ON DELETE CASCADE
565) ENGINE=InnoDB
9a90adc4
DO
566";
567 $query[] = "
8489d2af
DO
568CREATE TABLE IF NOT EXISTS `ObjectLog` (
569 `id` int(10) NOT NULL AUTO_INCREMENT,
570 `object_id` int(10) NOT NULL,
571 `user` varchar(64) NOT NULL,
9a90adc4
DO
572 `date` datetime NOT NULL,
573 `content` text NOT NULL,
8489d2af 574 PRIMARY KEY (`id`)
9a90adc4 575) ENGINE=InnoDB
0682218d 576";
8489d2af
DO
577 # Now we have the same structure of ObjectLog table, which objectlog.php
578 # could have left. Subsequent column updates will handle any existing data.
579 $query[] = "ALTER TABLE ObjectLog MODIFY COLUMN `id` int(10) unsigned NOT NULL AUTO_INCREMENT";
580 $query[] = "ALTER TABLE ObjectLog MODIFY COLUMN `object_id` int(10) unsigned NOT NULL";
581 $query[] = "ALTER TABLE ObjectLog MODIFY COLUMN `user` char(64) NOT NULL";
582 $query[] = "ALTER TABLE ObjectLog ADD KEY `object_id` (`object_id`)";
583 $query[] = "ALTER TABLE ObjectLog ADD KEY `date` (`date`)";
584 $query[] = "ALTER TABLE ObjectLog ADD CONSTRAINT `ObjectLog-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE";
585 # Now it's the way 0.19.0 is expecting it to be.
0682218d
AD
586 $query[] = "
587CREATE TABLE `ObjectParentCompat` (
588 `parent_objtype_id` int(10) unsigned NOT NULL,
589 `child_objtype_id` int(10) unsigned NOT NULL,
590 UNIQUE KEY `parent_child` (`parent_objtype_id`,`child_objtype_id`)
591) ENGINE=InnoDB
592";
593 $query[] = "
594CREATE TABLE `EntityLink` (
595 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
596 `parent_entity_type` enum('ipv4net','ipv4rspool','ipv4vs','ipv6net','object','rack','user') NOT NULL,
597 `parent_entity_id` int(10) unsigned NOT NULL,
598 `child_entity_type` enum('file','object') NOT NULL,
599 `child_entity_id` int(10) unsigned NOT NULL,
600 PRIMARY KEY (`id`),
601 UNIQUE KEY `EntityLink-unique` (`parent_entity_type`,`parent_entity_id`,`child_entity_type`,`child_entity_id`)
602) ENGINE=InnoDB
21ee3351
AA
603";
604 $query[] = "ALTER TABLE `TagStorage` CHANGE COLUMN `entity_realm` `entity_realm` ENUM('file','ipv4net','ipv4vs','ipv4rspool','object','rack','user','ipv6net') NOT NULL DEFAULT 'object' FIRST";
605 $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 606 $query[] = 'ALTER TABLE Link ADD COLUMN cable char(64) NULL AFTER portb';
3fb61857 607 $query[] = 'ALTER TABLE RackSpace ADD CONSTRAINT `RackSpace-FK-rack_id` FOREIGN KEY (rack_id) REFERENCES Rack (id)';
8c7b7381 608 $query[] = "ALTER TABLE `IPv4Allocation` ADD CONSTRAINT `IPv4Allocation-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE";
61e79d63 609 $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 610 $query[] = "ALTER TABLE `VLANSwitchTemplate` ADD COLUMN `mutex_rev` int(10) NOT NULL AFTER `id`";
09ec2e59 611 $query[] = "ALTER TABLE `VLANSwitchTemplate` ADD COLUMN `saved_by` char(64) NOT NULL AFTER `description`";
0682218d
AD
612 $query[] = "INSERT INTO `Attribute` (`id`, `type`, `name`) VALUES (26,'dict','Hypervisor')";
613 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (29,'no','Yes/No')";
614 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (30,'no','network chassis models')";
615 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (31,'no','server chassis models')";
616 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (32,'no','virtual switch models')";
617 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (33,'no','virtual switch OS type')";
618 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (4,26,29)";
619 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1502,1,NULL)";
620 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1502,2,31)";
621 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1502,3,NULL)";
622 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1502,14,NULL)";
623 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1502,20,NULL)";
624 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1502,21,NULL)";
625 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1502,22,NULL)";
626 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,1,NULL)";
627 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,2,30)";
628 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,3,NULL)";
588c98f8
AD
629 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,4,14)";
630 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,5,NULL)";
0682218d 631 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,14,NULL)";
588c98f8
AD
632 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,16,NULL)";
633 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,17,NULL)";
634 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,18,NULL)";
0682218d
AD
635 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,20,NULL)";
636 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,21,NULL)";
637 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,22,NULL)";
588c98f8 638 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,24,NULL)";
0682218d
AD
639 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1504,3,NULL)";
640 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1504,4,13)";
641 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1504,14,NULL)";
642 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1504,24,NULL)";
643 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1505,14,NULL)";
644 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1506,14,NULL)";
645 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1506,17,NULL)";
646 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1506,18,NULL)";
647 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,1,NULL)";
648 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,2,32)";
649 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,3,NULL)";
650 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,4,33)";
651 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,5,NULL)";
652 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,14,NULL)";
653 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,20,NULL)";
654 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,21,NULL)";
655 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,22,NULL)";
656 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (3,13)";
657 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (4,1504)";
658 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (4,1507)";
659 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1502,4)";
660 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1503,8)";
661 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1505,4)";
662 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1505,1504)";
663 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1505,1506)";
664 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1505,1507)";
665 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1506,1504)";
0682218d 666 $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 667 $query[] = "UPDATE Config SET varvalue = CONCAT(varvalue, ' or {\$typeid_1502} or {\$typeid_1503} or {\$typeid_1504} or {\$typeid_1507}') WHERE varname = 'IPV4OBJ_LISTSRC'";
0682218d 668 $query[] = "UPDATE Config SET varvalue = '8' WHERE varname = 'MASSCOUNT'";
993f5fa3 669 $query[] = "UPDATE RackObject SET label = NULL WHERE label = ''";
81659c05
AD
670 // Move barcode data so the column can be dropped
671 $result = $dbxlink->query ('SELECT id, objtype_id, barcode FROM RackObject WHERE barcode IS NOT NULL');
a7978f7f
AD
672 $rows = $result->fetchAll (PDO::FETCH_ASSOC);
673 unset ($result);
674 foreach ($rows as $row)
81659c05
AD
675 {
676 // Determine if this object type has the 'OEM S/N 1' attribute associated with it, and if it's set
677 $sn_query = "SELECT (SELECT COUNT(*) FROM AttributeMap WHERE objtype_id=${row['objtype_id']} AND attr_id=1) AS AM_count, ";
678 $sn_query .= "(SELECT COUNT(*) FROM AttributeValue WHERE object_id=${row['id']} AND attr_id=1) AS AV_count";
679 $sn_result = $dbxlink->query ($sn_query);
680 $sn_row = $sn_result->fetch (PDO::FETCH_ASSOC);
681 if ($sn_row['AM_count'] == 1 && $sn_row['AV_count'] == 0)
682 {
683 // 'OEM S/N 1' attribute is mapped to this object type, but it is not set. Good!
684 // Copy the barcode value to the attribute.
685 $query[] = "INSERT INTO AttributeValue (`object_id`, `attr_id`, `string_value`) VALUES (${row['id']}, 1, '${row['barcode']}')";
686 }
687 else
688 {
689 // Some other set of circumstances. Not as good!
690 // Copy the barcode value to a new ObjectLog record.
691 $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']}')";
692 }
693 unset ($sn_query, $sn_result, $sn_row);
694 }
81659c05
AD
695 $query[] = 'ALTER TABLE RackObject DROP COLUMN `barcode`';
696 $query[] = 'ALTER TABLE RackObjectHistory DROP COLUMN `barcode`';
fbeacc34 697 $query[] = 'ALTER TABLE `VLANSwitchTemplate` DROP COLUMN `max_local_vlans`';
81659c05 698 $query[] = "UPDATE Config SET varvalue = '0.19.0' WHERE varname = 'DB_VERSION'";
d3346ce2 699 break;
6d42599c 700 case '0.19.1':
1d5dd3a1
AD
701 $query[] = "ALTER TABLE `Config` CHANGE COLUMN `varvalue` `varvalue` text NOT NULL";
702 $query[] = "ALTER TABLE `UserConfig` CHANGE COLUMN `varvalue` `varvalue` text NOT NULL";
6d42599c 703 $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
704 $result = $dbxlink->query ("SHOW TABLES LIKE 'Objectlog'");
705 $rows = $result->fetchAll (PDO::FETCH_ASSOC);
706 unset ($result);
707 if (count ($rows))
708 {
709 # Now the ObjectLog merge... again, because the original table is named
710 # "Objectlog". The job is to merge contents of Objectlog and ObjectLog
711 # into the latter.
712 $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)";
713 $query[] = "DELETE FROM Objectlog WHERE object_id IN(SELECT id FROM RackObject)";
714 # Don't delete the old table, if the merge wasn't exhaustive.
715 $result = $dbxlink->query ('SELECT COUNT(*) AS c FROM Objectlog WHERE object_id NOT IN(SELECT id FROM RackObject)');
716 $row = $result->fetch (PDO::FETCH_ASSOC);
717 unset ($result);
718 if ($row['c'] == 0)
719 $query[] = 'DROP TABLE Objectlog';
720 else
721 $query[] = 'ALTER TABLE Objectlog RENAME TO Objectlog_old_unmerged';
722 }
6d42599c
AA
723 $query[] = "UPDATE Config SET varvalue = '0.19.1' WHERE varname = 'DB_VERSION'";
724 break;
f701420b 725 case '0.19.2':
f701420b
AA
726 $query[] = "ALTER TABLE IPv4Allocation ADD KEY `ip` (`ip`)";
727 $query[] = "ALTER TABLE IPv6Allocation ADD KEY `ip` (`ip`)";
728 $query[] = "ALTER TABLE IPv4VS ADD KEY `vip` (`vip`)";
729 $query[] = "ALTER TABLE IPv4RS ADD KEY `rsip` (`rsip`)";
ae67fa11
AD
730 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (34,'no','power supply chassis models')";
731 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (35,'no','power supply models')";
732 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1397,1,NULL)";
733 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1397,2,34)";
734 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1397,14,NULL)";
735 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1397,21,NULL)";
736 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1397,22,NULL)";
737 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1398,1,NULL)";
738 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1398,2,35)";
739 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1398,14,NULL)";
740 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1398,21,NULL)";
741 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1398,22,NULL)";
742 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1397,1398)";
743 $query[] = "INSERT INTO `PortCompat` (`type1`, `type2`) VALUES (1399,1399)";
744 $query[] = "INSERT INTO `PortInterfaceCompat` (`iif_id`, `oif_id`) VALUES (1,1399)";
745 $query[] = "UPDATE Config SET varvalue = CONCAT(varvalue, ' or {\$typeid_1397}') WHERE varname = 'IPV4OBJ_LISTSRC'";
c4ad9ac0
AA
746 $query[] = "ALTER TABLE AttributeValue ADD KEY `attr_id-uint_value` (`attr_id`,`uint_value`)";
747 $query[] = "ALTER TABLE AttributeValue ADD KEY `attr_id-string_value` (`attr_id`,`string_value`(12))";
0abae5fb 748 $query[] = "UPDATE Config SET varvalue = '0.19.2' WHERE varname = 'DB_VERSION'";
f701420b 749 break;
6d2cc13e 750 case '0.19.3':
6d2cc13e
DO
751 $query[] = "DELETE FROM RackSpace WHERE object_id IS NULL AND state = 'T'";
752 $query[] = "UPDATE Config SET varvalue = '0.19.3' WHERE varname = 'DB_VERSION'";
753 break;
1f02e311 754 case '0.19.4':
1f02e311
AD
755 $query[] = "UPDATE Config SET varvalue = '0.19.4' WHERE varname = 'DB_VERSION'";
756 break;
e1486971 757 case '0.19.5':
e1486971
DO
758 // Add 'virtual port' to 'virtual port' mapping
759 $query[] = "INSERT INTO `PortCompat` (`type1`,`type2`) VALUES (1469,1469)";
760 $query[] = "INSERT INTO `PortInterfaceCompat` (`iif_id`,`oif_id`) VALUES (1,1469)";
25b8a91f
AA
761 $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')";
762 $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 763 $query[] = "UPDATE Link SET cable = NULL WHERE cable = ''";
42b4722b 764 $query[] = "ALTER TABLE AttributeValue MODIFY string_value char(255) DEFAULT NULL";
e1486971
DO
765 $query[] = "UPDATE Config SET varvalue = '0.19.5' WHERE varname = 'DB_VERSION'";
766 break;
2a3a1a6d 767 case '0.19.6':
2a3a1a6d
AA
768 $query[] = "UPDATE Config SET varvalue = '0.19.6' WHERE varname = 'DB_VERSION'";
769 break;
b1bde5f6 770 case '0.19.7':
b1bde5f6
AD
771 # A plain "ALTER TABLE Attribute" can leave AUTO_INCREMENT in an odd
772 # state, hence the table swap.
773 $query[] = "
774CREATE TABLE `Attribute_new` (
775 `id` int(10) unsigned NOT NULL auto_increment,
776 `type` enum('string','uint','float','dict') default NULL,
777 `name` char(64) default NULL,
778 PRIMARY KEY (`id`),
779 UNIQUE KEY `name` (`name`)
780) ENGINE=InnoDB
781";
782 $query[] = "INSERT INTO Attribute_new SELECT * FROM Attribute";
783 $query[] = "INSERT INTO Attribute_new VALUES (9999, 'string', 'base MAC address')";
784 $query[] = "DROP TABLE Attribute";
785 $query[] = "ALTER TABLE Attribute_new RENAME TO Attribute";
786 $query[] = "ALTER TABLE AttributeMap ADD KEY (attr_id)";
787 $query[] = "DELETE FROM AttributeMap WHERE attr_id NOT IN (SELECT id FROM Attribute)";
788 $query[] = "ALTER TABLE AttributeMap ADD CONSTRAINT `AttributeMap-FK-attr_id` FOREIGN KEY (attr_id) REFERENCES Attribute (id)";
789 $query[] = "DELETE FROM AttributeValue WHERE attr_id NOT IN (SELECT attr_id FROM AttributeMap)";
790 $query[] = "ALTER TABLE AttributeValue ADD CONSTRAINT `AttributeValue-FK-attr_id` FOREIGN KEY (attr_id) REFERENCES AttributeMap (attr_id)";
791 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1506,4)";
44cbeb2e
DO
792 $query[] = "INSERT INTO PortInnerInterface (id, iif_name) VALUES (10, 'QSFP+')";
793 $query[] = "INSERT INTO PortInterfaceCompat VALUES (10, 1588)";
b1bde5f6
AD
794 $query[] = "UPDATE Config SET varvalue = '0.19.7' WHERE varname = 'DB_VERSION'";
795 break;
a03332f7 796 case '0.19.8':
3aca495a
DO
797 for ($i = 1424; $i <= 1466; $i++) # CX, then 42 ER channels
798 $query[] = "INSERT INTO `PortCompat` (`type1`, `type2`) VALUES (${i},${i})";
c7968df3
DO
799 $query[] = "ALTER TABLE UserAccount ENGINE=InnoDB";
800 $query[] = "DELETE FROM UserConfig WHERE user NOT IN (SELECT user_name FROM UserAccount)";
801 $query[] = "ALTER TABLE UserConfig ADD CONSTRAINT `UserConfig-FK-user` FOREIGN KEY (user) REFERENCES UserAccount (user_name) ON DELETE CASCADE";
802 $query[] = "DELETE FROM UserConfig WHERE varname NOT IN (SELECT varname FROM Config)";
803 $query[] = "ALTER TABLE UserConfig ADD KEY (varname)";
804 $query[] = "ALTER TABLE UserConfig ADD CONSTRAINT `UserConfig-FK-varname` FOREIGN KEY (varname) REFERENCES Config (varname) ON DELETE CASCADE";
a03332f7 805 $query[] = "ALTER TABLE Dictionary ENGINE=InnoDB";
c7968df3
DO
806 $query[] = "ALTER TABLE Chapter ENGINE=InnoDB";
807 $query[] = "UPDATE Chapter SET id = 9999 WHERE id = 22";
808 $query[] = "UPDATE AttributeMap SET chapter_id = 9999 WHERE chapter_id = 22";
809 $query[] = "UPDATE Dictionary SET chapter_id = 9999 WHERE chapter_id = 22";
810 $query[] = "DELETE FROM Dictionary WHERE chapter_id NOT IN (SELECT id FROM Chapter)";
811 $query[] = "ALTER TABLE Dictionary ADD CONSTRAINT `Dictionary-FK-chapter_id` FOREIGN KEY (chapter_id) REFERENCES Chapter (id)";
812 $query[] = "DELETE FROM AttributeMap WHERE chapter_id NOT IN (SELECT id FROM Chapter)";
813 $query[] = "ALTER TABLE AttributeMap ADD KEY (chapter_id)";
814 $query[] = "ALTER TABLE AttributeMap ADD CONSTRAINT `AttributeMap-FK-chapter_id` FOREIGN KEY (chapter_id) REFERENCES Chapter (id)";
c09757f6
DO
815 $query[] = "
816CREATE TABLE `CactiGraph` (
817 `object_id` int(10) unsigned NOT NULL,
818 `graph_id` int(10) unsigned NOT NULL,
819 `caption` char(255) DEFAULT NULL,
820 PRIMARY KEY (`graph_id`),
821 KEY `object_id` (`object_id`),
4d082372 822 CONSTRAINT `CactiGraph-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE
c09757f6
DO
823) ENGINE=InnoDB;
824";
825 $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')";
826 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('CACTI_URL','','string','yes','no','no','Cacti server base URL')";
827 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('CACTI_USERNAME','','string','yes','no','no','Cacti user account')";
828 $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
829 $query[] = "UPDATE Config SET varvalue = '0.19.8' WHERE varname = 'DB_VERSION'";
830 break;
5c0bd7de 831 case '0.19.9':
5c0bd7de 832 $query[] = "DELETE FROM Config WHERE varname = 'HNDP_RUNNERS_LISTSRC'";
8920f7f5
DO
833 # Dismiss some overly-specific OIF types in favour of more generic counterparts.
834 $squeeze = array
835 (
836 1202 => array # 1000Base-SX
837 (
838 25, # 1000Base-SX (SC)
839 26, # 1000Base-SX (LC)
840 ),
841 1204 => array # 1000Base-LX
842 (
843 27, # 1000Base-LX (SC)
844 28, # 1000Base-LX (LC)
845 ),
846 1196 => array # 100Base-SX
847 (
848 22, # 100Base-SX (SC)
849 23, # 100Base-SX (LC)
850 ),
851 1195 => array # 100Base-FX
852 (
853 20, # 100Base-FX (SC)
854 21, # 100Base-FX (LC)
855 1083, # 100Base-FX (MT-RJ)
856 ),
857 );
858 foreach ($squeeze as $stays => $leaves)
859 {
860 $csv = implode (', ', $leaves);
861 $query[] = "DELETE FROM PortCompat WHERE type1 IN(${csv}) OR type2 IN(${csv})";
862 $query[] = "INSERT IGNORE INTO PortInterfaceCompat (iif_id, oif_id) SELECT iif_id, ${stays} FROM Port WHERE type IN (${csv})";
863 $query[] = "UPDATE Port SET type = ${stays} WHERE type IN(${csv})";
864 $query[] = "DELETE FROM PortInterfaceCompat WHERE oif_id IN(${csv})";
865 }
fd8de939
DO
866 $query[] = "UPDATE Config SET varvalue = '0.19.9' WHERE varname = 'DB_VERSION'";
867 break;
f7494e3c 868 case '0.19.10':
f7494e3c
DO
869 $query[] = "INSERT INTO `PortCompat` (`type1`, `type2`) VALUES (1603,1603)";
870 $query[] = "UPDATE Config SET varvalue = '0.19.10' WHERE varname = 'DB_VERSION'";
871 break;
d0004c46 872 case '0.19.11':
8dc2a6c8 873 $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
874 $query[] = "INSERT INTO `Attribute` (`id`,`type`,`name`) VALUES (28,'string','Slot number')";
875 $query[] = 'INSERT INTO `AttributeMap` (`objtype_id`,`attr_id`,`chapter_id`) VALUES (4,28,NULL)';
876 $query[] = 'INSERT INTO `AttributeMap` (`objtype_id`,`attr_id`,`chapter_id`) VALUES (8,28,NULL)';
877 $query[] = 'INSERT INTO `AttributeMap` (`objtype_id`,`attr_id`,`chapter_id`) VALUES (798,28,NULL)';
878 $query[] = 'INSERT INTO `AttributeMap` (`objtype_id`,`attr_id`,`chapter_id`) VALUES (1055,28,NULL)';
7d68a0fb 879 $query[] = 'ALTER TABLE AttributeValue ADD COLUMN object_tid int(10) unsigned NOT NULL default 0 AFTER object_id';
4d082372 880 $query[] = 'UPDATE AttributeValue SET object_tid = (SELECT objtype_id FROM RackObject WHERE id = object_id)';
7d68a0fb
DO
881 $query[] = 'ALTER TABLE AttributeValue DROP FOREIGN KEY `AttributeValue-FK-attr_id`';
882 $query[] = 'ALTER TABLE AttributeValue DROP FOREIGN KEY `AttributeValue-FK-object_id`';
883 $query[] = 'ALTER TABLE AttributeValue ADD KEY `id-tid` (object_id, object_tid)';
884 $query[] = 'ALTER TABLE AttributeValue ADD KEY `object_tid-attr_id` (`object_tid`,`attr_id`)';
4d082372
AD
885 $query[] = 'ALTER TABLE RackObject ADD KEY `id-tid` (id, objtype_id)';
886 $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 887 $query[] = 'ALTER TABLE AttributeValue ADD CONSTRAINT `AttributeValue-FK-map` FOREIGN KEY (`object_tid`, `attr_id`) REFERENCES `AttributeMap` (`objtype_id`, `attr_id`)';
9e906292
DO
888 # 0.19.9 did it right, but kept the IDs in the dictionary. This time
889 # the dictionary is reduced, but the procedure needs to be repeated,
890 # in case the user had enough time to use the wrong IDs again.
891 $squeeze = array
892 (
893 1202 => array # 1000Base-SX
894 (
895 25, # 1000Base-SX (SC)
896 26, # 1000Base-SX (LC)
897 ),
898 1204 => array # 1000Base-LX
899 (
900 27, # 1000Base-LX (SC)
901 28, # 1000Base-LX (LC)
902 ),
903 1196 => array # 100Base-SX
904 (
905 22, # 100Base-SX (SC)
906 23, # 100Base-SX (LC)
907 ),
908 1195 => array # 100Base-FX
909 (
910 20, # 100Base-FX (SC)
911 21, # 100Base-FX (LC)
912 1083, # 100Base-FX (MT-RJ)
913 ),
914 );
915 foreach ($squeeze as $stays => $leaves)
916 {
917 $csv = implode (', ', $leaves);
918 $query[] = "DELETE FROM PortCompat WHERE type1 IN(${csv}) OR type2 IN(${csv})";
919 $query[] = "INSERT IGNORE INTO PortInterfaceCompat (iif_id, oif_id) SELECT iif_id, ${stays} FROM Port WHERE type IN (${csv})";
920 $query[] = "UPDATE Port SET type = ${stays} WHERE type IN(${csv})";
921 $query[] = "DELETE FROM PortInterfaceCompat WHERE oif_id IN(${csv})";
922 }
18680729 923 $query[] = "INSERT INTO `PortCompat` (`type1`, `type2`) VALUES (1642,1642)";
d6e7a8e7 924 $query[] = 'ALTER TABLE `EntityLink` ADD KEY `EntityLink-compound` (`parent_entity_type`,`child_entity_type`,`child_entity_id`)';
d0004c46
AD
925 $query[] = "UPDATE Config SET varvalue = '0.19.11' WHERE varname = 'DB_VERSION'";
926 break;
7cf0bc68
DO
927 # Batch 0.19.12 contained minor, but annoying bugs and was modified after the
928 # release of version 0.19.12 (which should be avoided if possible). The best
929 # way to resolve this particular case was to recall RackTables-0.19.12.tar.gz
930 # from the download area and provide RackTables-0.19.13.tar.gz containing the
931 # modified 0.19.12 batch.
a4c50c50 932 case '0.19.12':
f6252853 933 $query[] = "DELETE FROM Config WHERE varname IN('color_F', 'color_A', 'color_U', 'color_T', 'color_Th', 'color_Tw', 'color_Thw')";
2ce1cbdb
DO
934 $query[] = "INSERT INTO Chapter (id, sticky, name) VALUES (36,'no','serial console server models')";
935 $query[] = "INSERT INTO AttributeMap (objtype_id, attr_id, chapter_id) VALUES (1644, 1, NULL), (1644, 2, 36), (1644, 3, NULL)";
01d821c9 936 $query[] = "UPDATE Config SET varvalue = CONCAT(varvalue, '; 10=1588') WHERE varname = 'DEFAULT_PORT_OIF_IDS' AND 0 = INSTR(varvalue, '10=') ";
55732b46
DO
937 $query[] = "INSERT INTO PortInterfaceCompat VALUES (10,1663), (10,1664)";
938 $query[] = "INSERT INTO PortCompat VALUES (1588,1588), (1661,1661), (1663,1663), (1664,1664)";
833583b5
DO
939 $query[] = "INSERT INTO PortInnerInterface (id, iif_name) VALUES (11, 'CFP')";
940 $query[] = "INSERT INTO PortInterfaceCompat (iif_id, oif_id) VALUES (11,1668),(11,1669),(11,1670),(11,1671)";
941 $query[] = "INSERT INTO PortCompat (type1, type2) VALUES (1668,1668), (1669,1669), (1670,1670), (1671,1671)";
01d821c9 942 $query[] = "UPDATE Config SET varvalue = CONCAT(varvalue, '; 11=1668') WHERE varname = 'DEFAULT_PORT_OIF_IDS'";
0c652e58
DO
943 $query[] = "INSERT INTO Chapter (id, sticky, name) VALUES (37, 'no', 'wireless OS type')";
944 $query[] = "INSERT INTO AttributeMap (objtype_id, attr_id, chapter_id) VALUES (965, 4, 37)";
a4c50c50
DO
945 $query[] = "UPDATE Config SET varvalue = '0.19.12' WHERE varname = 'DB_VERSION'";
946 break;
86ff26ae
DO
947 case '0.19.13':
948 // add the date attribute type
949 $query[] = "ALTER TABLE `Attribute` CHANGE COLUMN `type` `type` enum('string','uint','float','dict','date') DEFAULT NULL";
0ec44a91
AA
950 $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')";
951 $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
952
953 // port over existing fields to new date attr type
954 $query[] = "UPDATE Attribute SET type='date' WHERE id IN (21,22,24)";
955 $query[] = "UPDATE AttributeValue SET uint_value=UNIX_TIMESTAMP(STR_TO_DATE(string_value, '%m/%d/%Y')) WHERE attr_id IN(21,22,24)";
956
0ec44a91
AA
957 // some config variables should be configurable in per-user basis
958 $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')";
959
86ff26ae
DO
960 $query[] = "UPDATE Config SET varvalue = '0.19.13' WHERE varname = 'DB_VERSION'";
961 break;
b55f913c
DO
962 case '0.19.14':
963 $query[] = "ALTER TABLE AttributeValue CHANGE COLUMN `object_id` `object_id` INT(10) UNSIGNED NOT NULL";
964 $query[] = "ALTER TABLE AttributeValue CHANGE COLUMN `attr_id` `attr_id` INT(10) UNSIGNED NOT NULL";
965 $query[] = "ALTER TABLE AttributeValue ADD PRIMARY KEY (`object_id`, `attr_id`), DROP INDEX `object_id`";
70d6c4fd
DO
966 $query[] = "ALTER TABLE Dictionary ADD COLUMN `dict_sticky` enum('yes','no') DEFAULT 'no' AFTER `dict_key`";
967 $query[] = "UPDATE Dictionary SET dict_sticky = 'yes' WHERE dict_key < 50000";
968 $query[] = "ALTER TABLE Dictionary ADD UNIQUE KEY dict_unique (chapter_id, dict_value, dict_sticky)";
969 $query[] = "ALTER TABLE Dictionary DROP KEY `chap_to_val`";
b55f913c
DO
970 $query[] = "UPDATE Config SET varvalue = '0.19.14' WHERE varname = 'DB_VERSION'";
971 break;
fd8de939 972 case '0.20.0':
fd8de939
DO
973 $query[] = "
974CREATE TABLE `PortLog` (
975 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
976 `port_id` int(10) unsigned NOT NULL,
977 `date` datetime NOT NULL,
978 `user` varchar(64) NOT NULL,
979 `message` text NOT NULL,
980 PRIMARY KEY (`id`),
981 KEY `port_id-date` (`port_id`,`date`),
982 CONSTRAINT `PortLog_ibfk_1` FOREIGN KEY (`port_id`) REFERENCES `Port` (`id`) ON DELETE CASCADE
983) ENGINE=InnoDB;
984";
985 $query[] = "
986CREATE TABLE `IPv4Log` (
987 `id` int(10) NOT NULL AUTO_INCREMENT,
988 `ip` int(10) unsigned NOT NULL,
989 `date` datetime NOT NULL,
990 `user` varchar(64) NOT NULL,
991 `message` text NOT NULL,
992 PRIMARY KEY (`id`),
993 KEY `ip-date` (`ip`,`date`)
994) ENGINE=InnoDB;
4318ced5
AA
995";
996 $query[] = "
997CREATE TABLE `IPv6Log` (
998 `id` int(10) NOT NULL AUTO_INCREMENT,
999 `ip` binary(16) NOT NULL,
1000 `date` datetime NOT NULL,
1001 `user` varchar(64) NOT NULL,
1002 `message` text NOT NULL,
1003 PRIMARY KEY (`id`),
1004 KEY `ip-date` (`ip`,`date`)
1005) ENGINE=InnoDB;
fd8de939 1006";
e1add254 1007 $query[] = "ALTER TABLE `FileLink` MODIFY COLUMN `entity_type` ENUM('ipv4net','ipv4rspool','ipv4vs','ipv6net','location','object','rack','user') NOT NULL DEFAULT 'object'";
93b7c5d8 1008 $query[] = "ALTER TABLE `TagStorage` MODIFY COLUMN `entity_realm` ENUM('file','ipv4net','ipv4rspool','ipv4vs','ipv6net','location','object','rack','user','vst') NOT NULL default 'object'";
30bb83bd 1009 $query[] = "ALTER TABLE `TagStorage` ADD COLUMN `user` char(64) DEFAULT NULL, ADD COLUMN `date` datetime DEFAULT NULL";
9b8174d7 1010
e1add254 1011 // Rename object tables and keys, 'name' no longer needs to be unique
9b8174d7
AD
1012 $dbxlink->query ('ALTER TABLE `RackObject` RENAME TO `Object`');
1013 $dbxlink->query ('ALTER TABLE `RackObjectHistory` RENAME TO `ObjectHistory`');
e1add254 1014 $dbxlink->query ('ALTER TABLE `Object` DROP KEY `name`');
9b8174d7
AD
1015 $query[] = 'ALTER TABLE `Object` DROP KEY `RackObject_asset_no`';
1016 $query[] = 'ALTER TABLE `Object` ADD UNIQUE KEY `asset_no` (`asset_no`)';
bd7c95ce 1017 $query[] = 'ALTER TABLE `Object` ADD KEY `type_id` (`objtype_id`,`id`)';
9b8174d7
AD
1018 $query[] = 'ALTER TABLE `ObjectHistory` DROP FOREIGN KEY `RackObjectHistory-FK-object_id`';
1019 $query[] = 'ALTER TABLE `ObjectHistory` ADD CONSTRAINT `ObjectHistory-FK-object_id` FOREIGN KEY (`id`) REFERENCES `Object` (`id`) ON DELETE CASCADE';
1020 $query[] = 'ALTER TABLE `RackSpace` DROP FOREIGN KEY `RackSpace-FK-rack_id`';
e1add254 1021
9b8174d7 1022 // Rack height is now an attribute
a14f752c 1023 $query[] = "INSERT INTO `Attribute` (`id`,`type`,`name`) VALUES (27,'uint','Height, units')";
9b8174d7 1024 $query[] = 'INSERT INTO `AttributeMap` (`objtype_id`,`attr_id`,`chapter_id`) VALUES (1560,27,NULL)';
e1add254 1025
42504426
AD
1026 // Racks are now sorted using an attribute
1027 $query[] = "INSERT INTO `Attribute` (`id`,`type`,`name`) VALUES (29,'uint','Sort order')";
1028 $query[] = 'INSERT INTO `AttributeMap` (`objtype_id`,`attr_id`,`chapter_id`) VALUES (1560,29,NULL)';
1029
e1add254
AD
1030 // Relate 'contact person' with locations
1031 $query[] = 'INSERT INTO `AttributeMap` (`objtype_id`,`attr_id`,`chapter_id`) VALUES (1562,14,NULL)';
1032
1033 // Allow relationships between racks/rows/locations
1034 $query[] = "ALTER TABLE `EntityLink` MODIFY COLUMN `parent_entity_type` ENUM('ipv4net','ipv4rspool','ipv4vs','ipv6net','location','object','rack','row','user') NOT NULL";
1035 $query[] = "ALTER TABLE `EntityLink` MODIFY COLUMN `child_entity_type` ENUM('file','location','object','rack','row') NOT NULL";
1036
9b8174d7
AD
1037 // Turn rows into objects
1038 $result = $dbxlink->query ('SELECT * FROM RackRow');
1039 $rows = $result->fetchAll (PDO::FETCH_ASSOC);
1040 unset ($result);
1041 foreach ($rows as $row)
1042 {
e1add254 1043 $prepared = $dbxlink->prepare ('INSERT INTO `Object` (`name`,`objtype_id`) VALUES (?,?)');
9b8174d7
AD
1044 $prepared->execute (array($row['name'], 1561));
1045 $row_id = $dbxlink->lastInsertId();
1046 // Turn all racks in this row into objects
42504426 1047 $result = $dbxlink->query ("SELECT id, name, height, comment FROM Rack WHERE row_id=${row['id']} ORDER BY name");
9b8174d7
AD
1048 $racks = $result->fetchAll (PDO::FETCH_ASSOC);
1049 unset ($result);
42504426 1050 $sort_order = 1;
dec748f6 1051 foreach ($racks as $rack)
9b8174d7 1052 {
42504426 1053 // Add the rack as an object, set the height and sort order as attributes, link the rack to the row,
9374831b 1054 // update rackspace, tags and files to reflect new rack_id, move history
e1add254 1055 $prepared = $dbxlink->prepare ('INSERT INTO `Object` (`name`,`objtype_id`,`comment`) VALUES (?,?,?)');
9b8174d7
AD
1056 $prepared->execute (array($rack['name'], 1560, $rack['comment']));
1057 $rack_id = $dbxlink->lastInsertId();
e1add254 1058 $query[] = "INSERT INTO `AttributeValue` (`object_id`,`object_tid`,`attr_id`,`uint_value`) VALUES (${rack_id},1560,27,${rack['height']})";
42504426 1059 $query[] = "INSERT INTO `AttributeValue` (`object_id`,`object_tid`,`attr_id`,`uint_value`) VALUES (${rack_id},1560,29,${sort_order})";
e1add254 1060 $query[] = "INSERT INTO `EntityLink` (`parent_entity_type`,`parent_entity_id`,`child_entity_type`,`child_entity_id`) VALUES ('row',${row_id},'rack',${rack_id})";
9b8174d7 1061 $query[] = "UPDATE `RackSpace` SET `rack_id`=${rack_id} WHERE `rack_id`=${rack['id']}";
1f02e311 1062 $query[] = "UPDATE `Atom` SET `rack_id`=${rack_id} WHERE `rack_id`=${rack['id']}";
c718f1e2
AA
1063 $query[] = "UPDATE `TagStorage` SET `entity_id`=${rack_id} WHERE `entity_realm`='rack' AND `entity_id`=${rack['id']}";
1064 $query[] = "UPDATE `FileLink` SET `entity_id`=${rack_id} WHERE `entity_type`='rack' AND `entity_id`=${rack['id']}";
9b8174d7 1065 $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 1066 $sort_order++;
9b8174d7
AD
1067 }
1068 }
9374831b 1069 $query[] = 'ALTER TABLE `RackSpace` ADD CONSTRAINT `RackSpace-FK-rack_id` FOREIGN KEY (`rack_id`) REFERENCES `Object` (`id`)';
9b8174d7
AD
1070 $query[] = 'DROP TABLE `Rack`';
1071 $query[] = 'DROP TABLE `RackRow`';
1072 $query[] = 'DROP TABLE `RackHistory`';
1073 $query[] = "
1074CREATE TABLE `RackThumbnail` (
1075 `rack_id` int(10) unsigned NOT NULL,
1076 `thumb_data` blob,
1077 UNIQUE KEY `rack_id` (`rack_id`),
1078 CONSTRAINT `RackThumbnail-FK-rack_id` FOREIGN KEY (`rack_id`) REFERENCES `Object` (`id`) ON DELETE CASCADE
1079) ENGINE=InnoDB
1080";
1081 $query[] = "
69bd4a37
AA
1082CREATE VIEW `Location` AS SELECT O.id, O.name, O.has_problems, O.comment, P.id AS parent_id, P.name AS parent_name
1083FROM `Object` O
1084LEFT JOIN (
1085 `Object` P INNER JOIN `EntityLink` EL
1086 ON EL.parent_entity_id = P.id AND P.objtype_id = 1562 AND EL.parent_entity_type = 'location' AND EL.child_entity_type = 'location'
1087) ON EL.child_entity_id = O.id
1088WHERE O.objtype_id = 1562
e1add254
AD
1089";
1090 $query[] = "
1091CREATE VIEW `Row` AS SELECT O.id, O.name, L.id AS location_id, L.name AS location_name
1092 FROM `Object` O
1093 LEFT JOIN `EntityLink` EL ON O.id = EL.child_entity_id AND EL.parent_entity_type = 'location' AND EL.child_entity_type = 'row'
1094 LEFT JOIN `Object` L ON EL.parent_entity_id = L.id AND L.objtype_id = 1562
1095 WHERE O.objtype_id = 1561
9b8174d7
AD
1096";
1097 $query[] = "
9c55b126 1098CREATE VIEW `Rack` AS SELECT O.id, O.name AS name, O.asset_no, O.has_problems, O.comment,
42504426
AD
1099 AV_H.uint_value AS height,
1100 AV_S.uint_value AS sort_order,
1f02e311 1101 RT.thumb_data,
e1add254
AD
1102 R.id AS row_id,
1103 R.name AS row_name
1f02e311 1104 FROM `Object` O
42504426
AD
1105 LEFT JOIN `AttributeValue` AV_H ON O.id = AV_H.object_id AND AV_H.attr_id = 27
1106 LEFT JOIN `AttributeValue` AV_S ON O.id = AV_S.object_id AND AV_S.attr_id = 29
1f02e311 1107 LEFT JOIN `RackThumbnail` RT ON O.id = RT.rack_id
e1add254
AD
1108 LEFT JOIN `EntityLink` EL ON O.id = EL.child_entity_id AND EL.parent_entity_type = 'row' AND EL.child_entity_type = 'rack'
1109 INNER JOIN `Object` R ON R.id = EL.parent_entity_id
1110 WHERE O.objtype_id = 1560
9b8174d7
AD
1111";
1112 $query[] = "
d7e9e25b 1113CREATE VIEW `RackObject` AS SELECT id, name, label, objtype_id, asset_no, has_problems, comment FROM `Object`
9b8174d7
AD
1114 WHERE `objtype_id` NOT IN (1560, 1561, 1562)
1115";
1116 $query[] = "UPDATE `Chapter` SET `name` = 'ObjectType' WHERE `id` = 1";
1117 $query[] = "DELETE FROM RackSpace WHERE object_id IS NULL AND state = 'T'";
2265be00 1118
f1cdc9f1 1119 $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 1120 $query[] = "UPDATE `Config` SET is_userdefined='yes' WHERE varname='PROXIMITY_RANGE'";
9c64ccf8 1121 $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 1122 $query[] = "ALTER TABLE `IPv4LB` MODIFY `prio` varchar(255) DEFAULT NULL";
71066ef1 1123
cc2fa820
AD
1124 $query[] = "ALTER TABLE `IPv4Address` ADD COLUMN `comment` char(255) NOT NULL default '' AFTER `name`";
1125 $query[] = "ALTER TABLE `IPv6Address` ADD COLUMN `comment` char(255) NOT NULL default '' AFTER `name`";
1126
71066ef1
AA
1127 // change IP address format of IPv4VS and IPv4RS tables
1128 convertSLBTablesToBinIPs();
1129
581e3b64
AA
1130 // do not allow NULL allocation type
1131 $query[] = "ALTER TABLE `IPv4Allocation` MODIFY `type` enum('regular','shared','virtual','router') NOT NULL DEFAULT 'regular'";
1132 $query[] = "ALTER TABLE `IPv6Allocation` MODIFY `type` enum('regular','shared','virtual','router') NOT NULL DEFAULT 'regular'";
1133
2481e17e
AA
1134 $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')";
1135
9c64ccf8
AA
1136 // update some config variables which changed their defaults in this verison
1137 replaceConfigVarValue ('SHOW_LAST_TAB', 'yes');
1138 replaceConfigVarValue ('IPV4_TREE_SHOW_USAGE','no');
1139 replaceConfigVarValue ('IPV4LB_LISTSRC', 'false', '{$typeid_4}');
1140 replaceConfigVarValue ('FILTER_DEFAULT_ANDOR', 'and');
1141 replaceConfigVarValue ('FILTER_SUGGEST_EXTRA', 'yes');
1142 replaceConfigVarValue ('IPV4_TREE_RTR_AS_CELL', 'no');
1143 replaceConfigVarValue ('SSH_OBJS_LISTSRC', 'false', 'none');
1144 replaceConfigVarValue ('TELNET_OBJS_LISTSRC', 'false', 'none');
1145
85e868a0
AA
1146 $query[] = "UPDATE Config SET varvalue = '0.20.0' WHERE varname = 'DB_VERSION'";
1147 break;
e9893a88 1148 case '0.20.1':
4e221a3d
AD
1149 // some HW types were moved from the 'Network switch' chapter to the 'Network chassis' chapter
1150 // change the type of affected objects to 'Network chassis'
1151 $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))";
1152
e9893a88
AD
1153 // new 'management interface' object type
1154 $query[] = "INSERT INTO `Chapter` (`id`,`sticky`,`name`) VALUES (38,'no','management interface type')";
1155 $query[] = "INSERT INTO `Attribute` (`id`,`type`,`name`) VALUES (30,'dict','Mgmt type')";
1156 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`,`attr_id`,`chapter_id`) VALUES (1787,3,NULL),(1787,14,NULL),(1787,30,38)";
1157 $query[] = "UPDATE `Config` SET varvalue = CONCAT(varvalue, ' or {\$typeid_1787}') WHERE varname = 'IPV4OBJ_LISTSRC'";
1158
2198b21a
DO
1159 $query[] = "INSERT INTO Config VALUES ('8021Q_EXTSYNC_LISTSRC','false','string','yes','no','no','List source: objects with extended 802.1Q sync')";
1160
6e5556bc
AD
1161 // constraints to prevent orphan records
1162 $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";
1163 $query[] = "DELETE FROM `MountOperation` WHERE old_molecule_id NOT IN (SELECT id FROM `Molecule`) OR new_molecule_id NOT IN (SELECT id FROM `Molecule`)";
1164 $query[] = "ALTER TABLE `Atom` ADD CONSTRAINT `Atom-FK-molecule_id` FOREIGN KEY (`molecule_id`) REFERENCES `Molecule` (`id`) ON DELETE CASCADE";
1165 $query[] = "ALTER TABLE `Atom` ADD CONSTRAINT `Atom-FK-rack_id` FOREIGN KEY (`rack_id`) REFERENCES `Object` (`id`) ON DELETE CASCADE";
1166 $query[] = "ALTER TABLE `MountOperation` ADD CONSTRAINT `MountOperation-FK-old_molecule_id` FOREIGN KEY (`old_molecule_id`) REFERENCES `Molecule` (`id`) ON DELETE CASCADE";
1167 $query[] = "ALTER TABLE `MountOperation` ADD CONSTRAINT `MountOperation-FK-new_molecule_id` FOREIGN KEY (`new_molecule_id`) REFERENCES `Molecule` (`id`) ON DELETE CASCADE";
14a9812c
DO
1168 # multiple Cacti servers
1169 $query[] = "
1170CREATE TABLE `CactiServer` (
1171 `id` int(10) unsigned NOT NULL auto_increment,
1172 `base_url` char(255) DEFAULT NULL,
1173 `username` char(64) DEFAULT NULL,
1174 `password` char(64) DEFAULT NULL,
1175 PRIMARY KEY (`id`)
1176) ENGINE=InnoDB
1177";
1178 $query[] = "ALTER TABLE CactiGraph ADD COLUMN server_id int(10) unsigned NOT NULL AFTER object_id";
7b9f0379 1179
14a9812c 1180 $result = $dbxlink->query ('SELECT COUNT(*) AS cnt FROM CactiGraph');
a1c4ba2f 1181 $row = $result->fetch (PDO::FETCH_ASSOC);
14a9812c 1182 unset ($result);
7b9f0379
AA
1183
1184 $result = $dbxlink->query ("SELECT varvalue FROM Config WHERE varname = 'CACTI_URL'");
1185 $cacti_url_row = $result->fetch (PDO::FETCH_ASSOC);
1186 unset ($result);
1187
1188 if ($row['cnt'] != 0 || is_array ($cacti_url_row) && strlen ($cacti_url_row['varvalue']))
14a9812c
DO
1189 {
1190 $query[] = "INSERT INTO CactiServer (id) VALUES (1)";
1191 $query[] = "UPDATE CactiServer SET base_url = (SELECT varvalue FROM Config WHERE varname = 'CACTI_URL') WHERE id = 1";
1192 $query[] = "UPDATE CactiServer SET username = (SELECT varvalue FROM Config WHERE varname = 'CACTI_USERNAME') WHERE id = 1";
1193 $query[] = "UPDATE CactiServer SET password = (SELECT varvalue FROM Config WHERE varname = 'CACTI_USERPASS') WHERE id = 1";
1194 $query[] = "UPDATE CactiGraph SET server_id = 1";
1195 }
1196 $query[] = "ALTER TABLE CactiGraph DROP PRIMARY KEY";
1197 $query[] = "ALTER TABLE CactiGraph ADD PRIMARY KEY (server_id, graph_id)";
1198 $query[] = "ALTER TABLE CactiGraph ADD KEY (graph_id)";
1199 $query[] = "ALTER TABLE CactiGraph ADD CONSTRAINT `CactiGraph-FK-server_id` FOREIGN KEY (server_id) REFERENCES CactiServer (id)";
1200 $query[] = "DELETE FROM Config WHERE varname IN('CACTI_URL', 'CACTI_USERNAME', 'CACTI_USERPASS')";
e9893a88
AD
1201 $query[] = "UPDATE Config SET varvalue = '0.20.1' WHERE varname = 'DB_VERSION'";
1202 break;
6e58c2c4
DO
1203 case '0.20.2':
1204 $query[] = "ALTER TABLE TagStorage ADD COLUMN tag_is_assignable ENUM('yes', 'no') NOT NULL default 'yes' AFTER tag_id";
1205 $query[] = "ALTER TABLE TagStorage ADD KEY `tag_id-tag_is_assignable` (tag_id, tag_is_assignable)";
1206 $query[] = "ALTER TABLE TagTree ADD COLUMN is_assignable ENUM('yes', 'no') NOT NULL default 'yes' AFTER parent_id";
1207 $query[] = "ALTER TABLE TagTree ADD KEY `id-is_assignable` (id, is_assignable)";
1208 $query[] = "ALTER TABLE TagStorage DROP FOREIGN KEY `TagStorage-FK-tag_id`";
1209 $query[] = "ALTER TABLE TagStorage ADD CONSTRAINT `TagStorage-FK-TagTree` FOREIGN KEY (tag_id, tag_is_assignable) REFERENCES TagTree (id, is_assignable)";
2818e5d9 1210 $query[] = "UPDATE UserAccount SET user_realname = NULL WHERE user_realname = ''";
cc7f0b09 1211 $query[] = "UPDATE Object SET comment = NULL WHERE comment = ''";
2c691f71
MH
1212 $query[] = "
1213CREATE TABLE `MuninServer` (
1214 `id` int(10) unsigned NOT NULL auto_increment,
1215 `base_url` char(255) DEFAULT NULL,
1216 PRIMARY KEY (`id`)
48ecb468 1217) ENGINE=InnoDB
2c691f71
MH
1218";
1219 $query[] = "
1220CREATE TABLE `MuninGraph` (
1221 `object_id` int(10) unsigned NOT NULL,
1222 `server_id` int(10) unsigned NOT NULL,
1223 `graph` char(255) NOT NULL,
1224 `caption` char(255) DEFAULT NULL,
1225 PRIMARY KEY (`object_id`,`server_id`,`graph`),
1226 KEY `server_id` (`server_id`),
1227 KEY `graph` (`graph`),
1228 CONSTRAINT `MuninGraph-FK-server_id` FOREIGN KEY (`server_id`) REFERENCES `MuninServer` (`id`),
1229 CONSTRAINT `MuninGraph-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `Object` (`id`) ON DELETE CASCADE
48ecb468 1230) ENGINE=InnoDB
2c691f71
MH
1231";
1232 $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
1233 $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')";
1234 $query[] = "ALTER TABLE VLANIPv4 ADD UNIQUE `network-domain-vlan` (ipv4net_id, domain_id, vlan_id)";
1235 $query[] = "ALTER TABLE VLANIPv4 DROP KEY `network-domain`";
1236 $query[] = "ALTER TABLE VLANIPv6 ADD UNIQUE `network-domain-vlan` (ipv6net_id, domain_id, vlan_id)";
1237 $query[] = "ALTER TABLE VLANIPv6 DROP KEY `network-domain`";
48ecb468 1238 $query[] = "UPDATE Config SET varvalue = '0.20.2' WHERE varname = 'DB_VERSION'";
6e58c2c4 1239 break;
56a28368
AA
1240 case '0.20.3':
1241 $query[] = "UPDATE Config SET varvalue = '0.20.3' WHERE varname = 'DB_VERSION'";
1242 break;
4dcd770e 1243 case '0.20.4':
6ccfd4bd 1244 $query[] = "ALTER TABLE `FileLink` MODIFY COLUMN `entity_type` ENUM('ipv4net','ipv4rspool','ipv4vs','ipv6net','location','object','rack','row','user') NOT NULL DEFAULT 'object'";
c495997f 1245 $query[] = "ALTER TABLE `RackSpace` MODIFY COLUMN `state` ENUM('A','U','T') NOT NULL default 'A'";
4dcd770e 1246 $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 1247 $query[] = "UPDATE `Config` SET varvalue = CONCAT(varvalue, ' or {\$typeid_965}') WHERE varname = 'IPV4OBJ_LISTSRC'";
ef550f27
AA
1248 $query[] = "UPDATE AttributeValue INNER JOIN AttributeMap USING (attr_id) SET AttributeValue.uint_value = 1572 WHERE chapter_id = 12 AND uint_value = 162";
1249 $query[] = "UPDATE AttributeValue INNER JOIN AttributeMap USING (attr_id) SET AttributeValue.uint_value = 1710 WHERE chapter_id = 12 AND uint_value = 163";
809d2ba9 1250 $query[] = "UPDATE Config SET varvalue = '%Y-%m-%d', description='PHP strftime() format to use for date output' WHERE varname = 'DATETIME_FORMAT'";
6b1ca530 1251 $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
1252 $query[] = "UPDATE Config SET varvalue = '0.20.4' WHERE varname = 'DB_VERSION'";
1253 break;
de99667b 1254 case '0.20.5':
d016010a
AD
1255 $query[] = "
1256CREATE OR REPLACE VIEW `Rack` AS SELECT O.id, O.name AS name, O.asset_no, O.has_problems, O.comment,
1257 AV_H.uint_value AS height,
1258 AV_S.uint_value AS sort_order,
1259 RT.thumb_data,
1260 R.id AS row_id,
1261 R.name AS row_name,
1262 L.id AS location_id,
1263 L.name AS location_name
1264 FROM `Object` O
1265 LEFT JOIN `AttributeValue` AV_H ON O.id = AV_H.object_id AND AV_H.attr_id = 27
1266 LEFT JOIN `AttributeValue` AV_S ON O.id = AV_S.object_id AND AV_S.attr_id = 29
1267 LEFT JOIN `RackThumbnail` RT ON O.id = RT.rack_id
1268 LEFT JOIN `EntityLink` RL ON O.id = RL.child_entity_id AND RL.parent_entity_type = 'row' AND RL.child_entity_type = 'rack'
1269 INNER JOIN `Object` R ON R.id = RL.parent_entity_id
1270 LEFT JOIN `EntityLink` LL ON R.id = LL.child_entity_id AND LL.parent_entity_type = 'location' AND LL.child_entity_type = 'row'
1271 LEFT JOIN `Object` L ON L.id = LL.parent_entity_id
1272 WHERE O.objtype_id = 1560
1273";
1274
f9fcce59
AD
1275 // prevent some AttributeMap entries from being deleted
1276 $query[] = "ALTER TABLE AttributeMap ADD COLUMN sticky enum('yes','no') default 'no'";
1277 $query[] = "UPDATE AttributeMap SET sticky = 'yes' WHERE objtype_id = 4 AND attr_id IN (26,28)"; // Server -> Hypervisor, Slot number
1278 $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
1279 $query[] = "UPDATE AttributeMap SET sticky = 'yes' WHERE objtype_id = 798 AND attr_id = 28"; // Network security -> Slot number
1280 $query[] = "UPDATE AttributeMap SET sticky = 'yes' WHERE objtype_id = 1055 AND attr_id = 28"; // FC switch -> Slot number
1281 $query[] = "UPDATE AttributeMap SET sticky = 'yes' WHERE objtype_id = 1560 AND attr_id IN (27,29)"; // Rack -> Height, Sort order
1282 $query[] = "UPDATE AttributeMap SET sticky = 'yes' WHERE objtype_id = 1787 AND attr_id = 30"; // Management interface -> Mgmt type
1283
de99667b 1284 $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
1285
1286 // SLB v2 tables
1287 $query[] = "
1288CREATE TABLE `VS` (
1289 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
1290 `name` char(255) DEFAULT NULL,
1291 `vsconfig` text,
1292 `rsconfig` text,
1293 PRIMARY KEY (`id`)
1294) ENGINE=InnoDB
1295";
1296 $query[] = "
1297CREATE TABLE `VSIPs` (
1298 `vs_id` int(10) unsigned NOT NULL,
1299 `vip` varbinary(16) NOT NULL,
1300 `vsconfig` text,
1301 `rsconfig` text,
1302 PRIMARY KEY (`vs_id`,`vip`),
1303 KEY `vip` (`vip`),
1304 CONSTRAINT `VSIPs-vs_id` FOREIGN KEY (`vs_id`) REFERENCES `VS` (`id`) ON DELETE CASCADE
1305) ENGINE=InnoDB
1306";
1307 $query[] = "
1308CREATE TABLE `VSPorts` (
1309 `vs_id` int(10) unsigned NOT NULL,
66844484 1310 `proto` enum('TCP','UDP','MARK') NOT NULL,
eacc0983
AA
1311 `vport` int(10) unsigned NOT NULL,
1312 `vsconfig` text,
1313 `rsconfig` text,
1314 PRIMARY KEY (`vs_id`,`proto`,`vport`),
66844484 1315 KEY `proto-vport` (`proto`,`vport`),
eacc0983
AA
1316 CONSTRAINT `VS-vs_id` FOREIGN KEY (`vs_id`) REFERENCES `VS` (`id`) ON DELETE CASCADE
1317) ENGINE=InnoDB
1318";
1319 $query[] = "
1320CREATE TABLE `VSEnabledIPs` (
1321 `object_id` int(10) unsigned NOT NULL,
1322 `vs_id` int(10) unsigned NOT NULL,
1323 `vip` varbinary(16) NOT NULL,
1324 `rspool_id` int(10) unsigned NOT NULL,
1325 `prio` varchar(255) DEFAULT NULL,
1326 `vsconfig` text,
1327 `rsconfig` text,
66844484 1328 PRIMARY KEY (`object_id`,`vs_id`,`vip`,`rspool_id`),
eacc0983
AA
1329 KEY `vip` (`vip`),
1330 KEY `VSEnabledIPs-FK-vs_id-vip` (`vs_id`,`vip`),
1331 KEY `VSEnabledIPs-FK-rspool_id` (`rspool_id`),
1332 CONSTRAINT `VSEnabledIPs-FK-rspool_id` FOREIGN KEY (`rspool_id`) REFERENCES `IPv4RSPool` (`id`) ON DELETE CASCADE,
1333 CONSTRAINT `VSEnabledIPs-FK-vs_id-vip` FOREIGN KEY (`vs_id`, `vip`) REFERENCES `VSIPs` (`vs_id`, `vip`) ON DELETE CASCADE
1334) ENGINE=InnoDB
1335";
1336 $query[] = "
1337CREATE TABLE `VSEnabledPorts` (
1338 `object_id` int(10) unsigned NOT NULL,
1339 `vs_id` int(10) unsigned NOT NULL,
66844484 1340 `proto` enum('TCP','UDP','MARK') NOT NULL,
eacc0983
AA
1341 `vport` int(10) unsigned NOT NULL,
1342 `rspool_id` int(10) unsigned NOT NULL,
1343 `vsconfig` text,
1344 `rsconfig` text,
66844484 1345 PRIMARY KEY (`object_id`,`vs_id`,`proto`,`vport`,`rspool_id`),
eacc0983
AA
1346 KEY `VSEnabledPorts-FK-vs_id-proto-vport` (`vs_id`,`proto`,`vport`),
1347 KEY `VSEnabledPorts-FK-rspool_id` (`rspool_id`),
1348 CONSTRAINT `VSEnabledPorts-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `Object` (`id`) ON DELETE CASCADE,
1349 CONSTRAINT `VSEnabledPorts-FK-rspool_id` FOREIGN KEY (`rspool_id`) REFERENCES `IPv4RSPool` (`id`) ON DELETE CASCADE,
1350 CONSTRAINT `VSEnabledPorts-FK-vs_id-proto-vport` FOREIGN KEY (`vs_id`, `proto`, `vport`) REFERENCES `VSPorts` (`vs_id`, `proto`, `vport`) ON DELETE CASCADE
1351) ENGINE=InnoDB
1352";
4b5da861
AA
1353 $query[] = "ALTER TABLE `EntityLink` MODIFY COLUMN `parent_entity_type` ENUM('ipv4net','ipv4rspool','ipv4vs','ipv6net','location','object','rack','row','user') NOT NULL";
1354 $query[] = "ALTER TABLE `FileLink` MODIFY COLUMN `entity_type` ENUM('ipv4net','ipv4rspool','ipv4vs','ipv6net','location','object','rack','row','user') NOT NULL DEFAULT 'object'";
1355 $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 1356 $query[] = "ALTER TABLE `UserConfig` DROP FOREIGN KEY `UserConfig-FK-user`";
f9fcce59 1357 $query[] = "UPDATE Config SET varvalue = '0.20.5' WHERE varname = 'DB_VERSION'";
de99667b 1358 break;
ceebebb9 1359 case '0.20.6':
18ff3d26
AD
1360 // one HW type was moved from the 'Network switch' chapter to the 'Network chassis' chapter
1361 // change the type of affected objects to 'Network chassis'
1362 $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
1363
1364 // convert values of old 'TELNET_OBJS_LISTSRC' 'SSH_OBJS_LISTSRC', 'RDP_OBJS_LISTSRC' variables into 'MGMT_PROTOS'
1365 $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')";
1366 if ('' !== $mgmt_converted_var = convertMgmtConfigVars())
1367 $query[] = "UPDATE `Config` SET varvalue = '" . mysql_escape_string ($mgmt_converted_var) . "' WHERE varname = 'MGMT_PROTOS'"; // TODO: call of deprecated function
1368 $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
1369
1370 $query[] = "ALTER TABLE `VSEnabledIPs` ADD CONSTRAINT `VSEnabledIPs-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `Object` (`id`) ON DELETE CASCADE";
1371
5d8afb66 1372 $query[] = "DELETE FROM Config WHERE varname = 'PORTS_PER_ROW'";
ceebebb9 1373 $query[] = "UPDATE Config SET varvalue = '0.20.6' WHERE varname = 'DB_VERSION'";
a7390942 1374 break;
25355e1d 1375 case '0.20.7':
7740234b
AD
1376 if (! isInnoDBSupported ())
1377 {
1378 showUpgradeError ('Cannot upgrade because triggers are not supported by your MySQL server.', __FUNCTION__);
1379 die;
1380 }
1381
18733c4a
AD
1382 // for the UNIQUE key to work, portb needs to be > porta
1383 $result = $dbxlink->query ('SELECT porta, portb FROM `Link` WHERE porta > portb');
1384 $links = $result->fetchAll (PDO::FETCH_ASSOC);
1385 unset ($result);
1386 foreach ($links as $link)
1387 $query[] = "UPDATE `Link` SET `porta`=${link['portb']}, `portb`=${link['porta']} WHERE `porta`=${link['porta']} AND `portb`=${link['portb']}";
1388
1389 // add triggers
1390 $entitylink_trigger_body = <<<ENDOFTRIGGER
1391EntityLinkTrigger:BEGIN
1392 DECLARE parent_objtype, child_objtype, count INTEGER;
1393
1394 # forbid linking an entity to itself
1395 IF NEW.parent_entity_type = NEW.child_entity_type AND NEW.parent_entity_id = NEW.child_entity_id THEN
1396 SET NEW.parent_entity_id = NULL;
1397 LEAVE EntityLinkTrigger;
1398 END IF;
1399
1400 # in some scenarios, only one parent is allowed
1401 CASE CONCAT(NEW.parent_entity_type, '.', NEW.child_entity_type)
1402 WHEN 'location.location' THEN
1403 SELECT COUNT(*) INTO count FROM EntityLink WHERE parent_entity_type = 'location' AND child_entity_type = 'location' AND child_entity_id = NEW.child_entity_id;
1404 WHEN 'location.row' THEN
1405 SELECT COUNT(*) INTO count FROM EntityLink WHERE parent_entity_type = 'location' AND child_entity_type = 'row' AND child_entity_id = NEW.child_entity_id;
1406 WHEN 'row.rack' THEN
1407 SELECT COUNT(*) INTO count FROM EntityLink WHERE parent_entity_type = 'row' AND child_entity_type = 'rack' AND child_entity_id = NEW.child_entity_id;
1408 ELSE
1409 # some other scenario, assume it is valid
1410 SET count = 0;
1411 END CASE;
1412 IF count > 0 THEN
1413 SET NEW.parent_entity_id = NULL;
1414 LEAVE EntityLinkTrigger;
1415 END IF;
1416
1417 IF NEW.parent_entity_type = 'object' AND NEW.child_entity_type = 'object' THEN
1418 # lock objects to prevent concurrent link establishment
1419 SELECT objtype_id INTO parent_objtype FROM Object WHERE id = NEW.parent_entity_id FOR UPDATE;
1420 SELECT objtype_id INTO child_objtype FROM Object WHERE id = NEW.child_entity_id FOR UPDATE;
1421
1422 # only permit the link if object types are compatibile
1423 SELECT COUNT(*) INTO count FROM ObjectParentCompat WHERE parent_objtype_id = parent_objtype AND child_objtype_id = child_objtype;
1424 IF count = 0 THEN
1425 SET NEW.parent_entity_id = NULL;
1426 END IF;
1427 END IF;
1428END;
1429ENDOFTRIGGER;
1430 $query[] = "CREATE TRIGGER `EntityLink-before-insert` BEFORE INSERT ON `EntityLink` FOR EACH ROW $entitylink_trigger_body";
1431 $query[] = "CREATE TRIGGER `EntityLink-before-update` BEFORE UPDATE ON `EntityLink` FOR EACH ROW $entitylink_trigger_body";
7740234b 1432 $link_trigger_body = <<<ENDOFTRIGGER
18733c4a 1433LinkTrigger:BEGIN
7740234b
AD
1434 DECLARE tmp, porta_type, portb_type, count INTEGER;
1435
1436 IF NEW.porta = NEW.portb THEN
1437 # forbid connecting a port to itself
1438 SET NEW.porta = NULL;
18733c4a 1439 LEAVE LinkTrigger;
7740234b
AD
1440 ELSEIF NEW.porta > NEW.portb THEN
1441 # force porta < portb
1442 SET tmp = NEW.porta;
1443 SET NEW.porta = NEW.portb;
1444 SET NEW.portb = tmp;
1445 END IF;
1446
1447 # lock ports to prevent concurrent link establishment
1448 SELECT type INTO porta_type FROM Port WHERE id = NEW.porta FOR UPDATE;
1449 SELECT type INTO portb_type FROM Port WHERE id = NEW.portb FOR UPDATE;
1450
1451 # only permit the link if ports are compatibile
1452 SELECT COUNT(*) INTO count FROM PortCompat WHERE (type1 = porta_type AND type2 = portb_type) OR (type1 = portb_type AND type2 = porta_type);
1453 IF count = 0 THEN
1454 SET NEW.porta = NULL;
1455 END IF;
1456END;
1457ENDOFTRIGGER;
1458 $query[] = "CREATE TRIGGER `Link-before-insert` BEFORE INSERT ON `Link` FOR EACH ROW $link_trigger_body";
1459 $query[] = "CREATE TRIGGER `Link-before-update` BEFORE UPDATE ON `Link` FOR EACH ROW $link_trigger_body";
1460
25355e1d
AD
1461 // enable IP addressing for all object types unless specifically excluded
1462 $query[] = "UPDATE `Config` SET varvalue = 'not ({\$typeid_3} or {\$typeid_9} or {\$typeid_10} or {\$typeid_11})' WHERE varname = 'IPV4OBJ_LISTSRC'";
2437f548 1463
42fb3aa2
AD
1464 $query[] = "ALTER TABLE `EntityLink` MODIFY COLUMN `parent_entity_type` ENUM('location','object','rack','row') NOT NULL";
1465 $query[] = "ALTER TABLE `EntityLink` MODIFY COLUMN `child_entity_type` ENUM('location','object','rack','row') NOT NULL";
1466
75e7c0c6
DO
1467 $query[] = "UPDATE Config SET description = 'List source: objects for that asset tag should be set' WHERE varname = 'ASSETWARN_LISTSRC'";
1468 $query[] = "UPDATE Config SET description = 'List source: objects for that common name should be set' WHERE varname = 'NAMEWARN_LISTSRC'";
2437f548 1469 $query[] = "UPDATE Config SET varvalue = '0.20.7' WHERE varname = 'DB_VERSION'";
25355e1d 1470 break;
d15dae2f
DO
1471 case 'dictionary':
1472 $query = reloadDictionary();
1473 break;
fbbb74fb 1474 default:
87ae30c5
AD
1475 return NULL;
1476 }
1477 return $query;
1478}
1479
1480function executeUpgradeBatch ($batchid)
1481{
1482 global $dbxlink;
1483 $query = getUpgradeBatch($batchid);
a20a4e3c
DO
1484 if ($query === NULL)
1485 {
87ae30c5
AD
1486 showError ("unknown batch '${batchid}'", __FUNCTION__);
1487 die;
fbbb74fb 1488 }
fbbb74fb 1489 $failures = array();
4114697d 1490 echo "<tr><th>Executing batch '${batchid}'</th><td>";
fbbb74fb
DO
1491 foreach ($query as $q)
1492 {
babe4bf5
AA
1493 try
1494 {
1495 $result = $dbxlink->query ($q);
1496 }
1497 catch (PDOException $e)
758fe24c 1498 {
758fe24c
DO
1499 $errorInfo = $dbxlink->errorInfo();
1500 $failures[] = array ($q, $errorInfo[2]);
1501 }
fbbb74fb 1502 }
fbbb74fb 1503 if (!count ($failures))
4114697d 1504 echo "<strong><font color=green>done</font></strong>";
fbbb74fb
DO
1505 else
1506 {
4114697d 1507 echo "<strong><font color=red>The following queries failed:</font></strong><br><pre>";
fbbb74fb
DO
1508 foreach ($failures as $f)
1509 {
1510 list ($q, $i) = $f;
4114697d 1511 echo "${q} -- ${i}\n";
fbbb74fb 1512 }
4114697d 1513 echo "</pre>";
fbbb74fb 1514 }
4114697d 1515 echo '</td></tr>';
fbbb74fb
DO
1516}
1517
120e9ddd 1518function authenticate_admin ($username, $password)
a1f3710a 1519{
43c7895d 1520 global $dbxlink;
4dd08c61
DO
1521 $prepared = $dbxlink->prepare ('SELECT COUNT(*) FROM UserAccount WHERE user_id=1 AND user_name=? AND user_password_hash=?');
1522 if (!$prepared->execute (array ($username, sha1 ($password))))
a1f3710a 1523 die ('SQL query failed in ' . __FUNCTION__);
4dd08c61 1524 $rows = $prepared->fetchAll (PDO::FETCH_NUM);
43c7895d 1525 return $rows[0][0] == 1;
a1f3710a
DO
1526}
1527
c4ee2310
DO
1528// Database version detector. Should behave corretly on any
1529// working dataset a user might have.
1530function getDatabaseVersion ()
1531{
2f5e4db9
DO
1532 global $dbxlink;
1533 $prepared = $dbxlink->prepare ('SELECT varvalue FROM Config WHERE varname = "DB_VERSION" and vartype = "string"');
1534 if (! $prepared->execute())
c4ee2310 1535 {
c4ee2310 1536 $errorInfo = $dbxlink->errorInfo();
2f5e4db9 1537 die (__FUNCTION__ . ': SQL query failed with error ' . $errorInfo[2]);
c4ee2310 1538 }
2f5e4db9 1539 $rows = $prepared->fetchAll (PDO::FETCH_NUM);
c4ee2310
DO
1540 if (count ($rows) != 1 || !strlen ($rows[0][0]))
1541 die (__FUNCTION__ . ': Cannot guess database version. Config table is present, but DB_VERSION is missing or invalid. Giving up.');
1542 $ret = $rows[0][0];
1543 return $ret;
1544}
1545
71066ef1 1546function showUpgradeError ($info = '', $location = 'N/A')
c4ee2310
DO
1547{
1548 if (preg_match ('/\.php$/', $location))
1549 $location = basename ($location);
1550 elseif ($location != 'N/A')
1551 $location = $location . '()';
1552 echo "<div class=msg_error>An error has occured in [${location}]. ";
1553 if (!strlen ($info))
1554 echo 'No additional information is available.';
1555 else
1556 echo "Additional information:<br><p>\n<pre>\n${info}\n</pre></p>";
2f5e4db9 1557 echo "Go back or try starting from <a href='index.php'>index page</a>.<br></div>\n";
c4ee2310
DO
1558}
1559
9c64ccf8
AA
1560// changes the value of config variable. If $old_value_filter is set, value is changed only if current value equals to it.
1561function replaceConfigVarValue ($varname, $new_value, $old_value_filter = NULL)
1562{
1563 global $dbxlink;
1564 if (isset ($old_value_filter))
1565 {
1566 $result = $dbxlink->prepare ("SELECT varvalue FROM Config WHERE varname = ?");
1567 $result->execute (array ($varname));
1568 if ($row = $result->fetch (PDO::FETCH_ASSOC))
1569 if ($row['varvalue'] != $old_value_filter)
1570 return;
1571 unset ($result);
1572 }
1573 $result = $dbxlink->prepare ("UPDATE Config set varvalue = ? WHERE varname = ?");
1574 $result->execute (array ($new_value, $varname));
1575}
1576
964b0388 1577function renderUpgraderHTML()
99ee5479 1578{
b00cc78c
AA
1579 global $found_secret_file;
1580 if (! $found_secret_file)
1581 die ('<center>There is no working RackTables instance here, <a href="?module=installer">install</a>?</center>');
1582
1583 try
964b0388 1584 {
b00cc78c
AA
1585 connectDB();
1586 }
1587 catch (RackTablesError $e)
1588 {
1589 die ("Database connection failed:\n\n" . $e->getMessage());
1590 }
dec748f6 1591
b00cc78c
AA
1592 if
1593 (
1594 !isset ($_SERVER['PHP_AUTH_USER']) or
1595 !strlen ($_SERVER['PHP_AUTH_USER']) or
1596 !isset ($_SERVER['PHP_AUTH_PW']) or
1597 !strlen ($_SERVER['PHP_AUTH_PW']) or
1598 !authenticate_admin ($_SERVER['PHP_AUTH_USER'], $_SERVER['PHP_AUTH_PW'])
1599 )
1600 {
1601 header ('WWW-Authenticate: Basic realm="RackTables upgrade"');
1602 header ('HTTP/1.0 401 Unauthorized');
a931fc26
DO
1603?>
1604<h1>Trouble logging in?</h1>
f2b6ae86 1605You are trying to authenticate for the RackTables upgrade screen. This means that
a931fc26
DO
1606you must authenticate with the username and password of the main RackTables
1607administrator. There is only one such account in each installation, its default
1608username is "admin". RackTables wiki provides more information on this topic.
1609<?php
120e9ddd 1610 die;
964b0388 1611 }
fbbb74fb 1612
964b0388 1613?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
5f016d39 1614<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
2cf36003 1615<head><title>RackTables upgrade script</title>
964b0388
DO
1616<style type="text/css">
1617.tdleft {
1618 text-align: left;
1619}
1620
1621.trok {
1622 background-color: #80FF80;
1623}
1624
1625.trwarning {
1626 background-color: #FFFF80;
1627}
1628
1629.trerror {
1630 background-color: #FF8080;
1631}
1632</style>
5f016d39
DO
1633</head>
1634<body>
1635<h1>Platform check status</h1>
1636<?php
1637
1638if (!platform_is_ok())
50cc9dbe
DO
1639{
1640 echo '<h1>Please resolve the failed (red) item(s) above.</h1>';
5f016d39 1641 die ('</body></html>');
50cc9dbe 1642}
5f016d39
DO
1643
1644echo '<h1>Upgrade status</h1>';
fbbb74fb 1645$dbver = getDatabaseVersion();
5f016d39 1646echo '<table border=1 cellpadding=5>';
4114697d
DO
1647echo "<tr><th>Current status</th><td>Data version: ${dbver}<br>Code version: " . CODE_VERSION . "</td></tr>\n";
1648
1649$path = getDBUpgradePath ($dbver, CODE_VERSION);
1650if ($path === NULL)
758fe24c 1651{
4114697d 1652 echo "<tr><th>Upgrade path</th><td><font color=red>not found</font></td></tr>\n";
e4d00763
DO
1653 echo "<tr><th>Summary</th><td>Check README for more information. RackTables releases prior to 0.18.0 ";
1654 echo "must be upgraded to 0.18.0 first.</td></tr>\n";
758fe24c 1655}
4114697d 1656else
5f4027b8 1657{
4114697d
DO
1658 if (!count ($path))
1659 echo "<tr><th>Summary</th><td>Come back later.</td></tr>\n";
1660 else
1661 {
1662 echo "<tr><th>Upgrade path</th><td>${dbver} &rarr; " . implode (' &rarr; ', $path) . "</td></tr>\n";
abc799c5 1663 global $relnotes;
4114697d 1664 foreach ($path as $batchid)
4114697d 1665 if (isset ($relnotes[$batchid]))
5ae6d365 1666 echo "<tr><th>Release notes for ${batchid}</th><td><pre>" . $relnotes[$batchid] . "</pre></td></tr>\n";
a12022a9
DO
1667 if (array_key_exists ('reallyreally', $_REQUEST))
1668 {
1669 foreach ($path as $batchid)
1670 executeUpgradeBatch ($batchid);
d15dae2f 1671 executeUpgradeBatch ('dictionary');
a12022a9
DO
1672 echo "<tr><th>Summary</th><td>Upgrade complete, it is Ok to ";
1673 echo "<a href='index.php'>enter</a> the system.</td></tr>\n";
1674 }
1675 else
1676 {
1677 echo '<form method=post action="index.php?module=upgrade"><tr><th>Wait!</th>';
1678 echo '<td><p>RackTables database upgrades sometimes go wrong because of assorted reasons. ';
1679 echo 'It is <strong>highly recommended</strong> to make a database backup before ';
1680 echo 'proceeding any further. <tt>mysqldump</tt> and <tt>PHPMyAdmin</tt> are convenient ';
1681 echo 'tools for doing this.</p>';
1682 echo '<p><input type=checkbox name=reallyreally id=reallyreally><label for=reallyreally>';
1683 echo 'I am ready to bear all risks of this upgrade. I am ready to roll it back in case of ';
1684 echo 'a failure.</label> <input type=submit value="Yes, I am."></p></td></tr></form>';
4114697d 1685 }
4114697d 1686 }
5f4027b8 1687}
4114697d 1688echo '</table>';
5f016d39 1689echo '</body></html>';
964b0388 1690}
fbbb74fb 1691
71066ef1
AA
1692function convertSLBTablesToBinIPs()
1693{
1694 global $dbxlink;
1695
1696 $dbxlink->query ("DROP TABLE IF EXISTS `IPv4VS_new`, `IPv4RS_new`, `IPv4VS_old`, `IPv4RS_old`");
1697
1698 $dbxlink->query (<<<END
1699CREATE TABLE `IPv4VS_new` (
1700 `id` int(10) unsigned NOT NULL auto_increment,
1701 `vip` varbinary(16) NOT NULL,
1702 `vport` smallint(5) unsigned default NULL,
1703 `proto` enum('TCP','UDP','MARK') NOT NULL default 'TCP',
1704 `name` char(255) default NULL,
1705 `vsconfig` text,
1706 `rsconfig` text,
1707 PRIMARY KEY (`id`),
1708 KEY `vip` (`vip`)
1709) ENGINE=InnoDB DEFAULT CHARSET=utf8
1710END
1711 );
1712 $result = $dbxlink->query ("SELECT * FROM IPv4VS");
1713 $rows = $result->fetchAll (PDO::FETCH_ASSOC);
1714 unset ($result);
1715 foreach ($rows as $row)
1716 {
1717 $row['vip'] = ip4_int2bin ($row['vip']);
1718 usePreparedInsertBlade ('IPv4VS_new', $row);
1719 }
dec748f6 1720
71066ef1
AA
1721 $dbxlink->query (<<<END
1722CREATE TABLE `IPv4RS_new` (
1723 `id` int(10) unsigned NOT NULL auto_increment,
1724 `inservice` enum('yes','no') NOT NULL default 'no',
1725 `rsip` varbinary(16) NOT NULL,
1726 `rsport` smallint(5) unsigned default NULL,
1727 `rspool_id` int(10) unsigned default NULL,
1728 `rsconfig` text,
1729 `comment` varchar(255) DEFAULT NULL,
1730 PRIMARY KEY (`id`),
1731 KEY `rsip` (`rsip`),
1732 UNIQUE KEY `pool-endpoint` (`rspool_id`,`rsip`,`rsport`),
1733 CONSTRAINT `IPRS-FK` FOREIGN KEY (`rspool_id`) REFERENCES `IPv4RSPool` (`id`) ON DELETE CASCADE
1734) ENGINE=InnoDB DEFAULT CHARSET=utf8
1735END
1736 );
1737 $result = $dbxlink->query ("SELECT * FROM IPv4RS");
1738 $rows = $result->fetchAll (PDO::FETCH_ASSOC);
1739 unset ($result);
1740 foreach ($rows as $row)
1741 {
1742 $row['rsip'] = ip4_int2bin ($row['rsip']);
1743 usePreparedInsertBlade ('IPv4RS_new', $row);
1744 }
1745
1746 $dbxlink->query (<<<END
1747RENAME TABLE
1748 `IPv4VS` TO `IPv4VS_old`,
1749 `IPv4VS_new` TO `IPv4VS`,
1750 `IPv4RS` TO `IPv4RS_old`,
1751 `IPv4RS_new` TO `IPv4RS`
1752END
1753 );
1754 // re-create foreign key in IPv4LB
1755 $dbxlink->query ("ALTER TABLE `IPv4LB` DROP FOREIGN KEY `IPv4LB-FK-vs_id`");
1756 $dbxlink->query ("ALTER TABLE `IPv4LB` ADD CONSTRAINT `IPv4LB-FK-vs_id` FOREIGN KEY (`vs_id`) REFERENCES `IPv4VS` (`id`)");
1757
1758 $dbxlink->query ("DROP TABLE `IPv4VS_old`, `IPv4RS_old`");
93b7c5d8
AA
1759
1760 // re-create foreign key in IPv4RS
1761 $dbxlink->query ("ALTER TABLE `IPv4RS` DROP FOREIGN KEY `IPRS-FK`");
1762 $dbxlink->query ("ALTER TABLE `IPv4RS` ADD CONSTRAINT `IPv4RS-FK` FOREIGN KEY (`rspool_id`) REFERENCES `IPv4RSPool` (`id`) ON DELETE CASCADE");
71066ef1
AA
1763}
1764
1765// This is a swiss-knife blade to insert a record into a table.
1766// The first argument is table name.
1767// The second argument is an array of "name" => "value" pairs.
1768// returns integer - affected rows count. Throws exception on error
1769function usePreparedInsertBlade ($tablename, $columns)
1770{
1771 global $dbxlink;
1772 $query = "INSERT INTO ${tablename} (" . implode (', ', array_keys ($columns));
1773 $query .= ') VALUES (' . questionMarks (count ($columns)) . ')';
1774 // Now the query should be as follows:
1775 // INSERT INTO table (c1, c2, c3) VALUES (?, ?, ?)
1776 try
1777 {
1778 $prepared = $dbxlink->prepare ($query);
1779 $prepared->execute (array_values ($columns));
1780 return $prepared->rowCount();
1781 }
1782 catch (PDOException $e)
1783 {
1784 throw convertPDOException ($e);
1785 }
1786}
1787
fd3ef4e7
AA
1788// converts the values of old-style config vars TELNET_OBJS_LISTSRC, SSH_OBJS_LISTSRC, RDP_OBJS_LISTSRC
1789// to the format of MGMT_PROTOS (comma-separated list of "proto: rackcode" pairs)
1790function convertMgmtConfigVars()
1791{
1792 global $dbxlink;
1793 $ret = array();
1794 foreach (array ('telnet' => 'TELNET_OBJS_LISTSRC', 'ssh' => 'SSH_OBJS_LISTSRC', 'rdp' => 'RDP_OBJS_LISTSRC') as $proto => $varname)
1795 {
1796 $result = $dbxlink->prepare ("SELECT varvalue FROM Config WHERE varname = ?");
1797 $result->execute (array ($varname));
1798 if ($row = $result->fetch (PDO::FETCH_ASSOC))
1799 if ($row['varvalue'] != 'false' && $row['varvalue'] != '')
1800 $ret[] = "$proto: " . $row['varvalue'];
1801 unset ($result);
1802 }
1803 return implode (',', $ret);
1804}
1805
fbbb74fb 1806?>