LinkTriggerTest: adjust code style
[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
1382 $link_trigger_body = <<<ENDOFTRIGGER
1383BEGIN
1384 DECLARE tmp, porta_type, portb_type, count INTEGER;
1385
1386 IF NEW.porta = NEW.portb THEN
1387 # forbid connecting a port to itself
1388 SET NEW.porta = NULL;
1389 ELSEIF NEW.porta > NEW.portb THEN
1390 # force porta < portb
1391 SET tmp = NEW.porta;
1392 SET NEW.porta = NEW.portb;
1393 SET NEW.portb = tmp;
1394 END IF;
1395
1396 # lock ports to prevent concurrent link establishment
1397 SELECT type INTO porta_type FROM Port WHERE id = NEW.porta FOR UPDATE;
1398 SELECT type INTO portb_type FROM Port WHERE id = NEW.portb FOR UPDATE;
1399
1400 # only permit the link if ports are compatibile
1401 SELECT COUNT(*) INTO count FROM PortCompat WHERE (type1 = porta_type AND type2 = portb_type) OR (type1 = portb_type AND type2 = porta_type);
1402 IF count = 0 THEN
1403 SET NEW.porta = NULL;
1404 END IF;
1405END;
1406ENDOFTRIGGER;
1407 $query[] = "CREATE TRIGGER `Link-before-insert` BEFORE INSERT ON `Link` FOR EACH ROW $link_trigger_body";
1408 $query[] = "CREATE TRIGGER `Link-before-update` BEFORE UPDATE ON `Link` FOR EACH ROW $link_trigger_body";
1409
25355e1d
AD
1410 // enable IP addressing for all object types unless specifically excluded
1411 $query[] = "UPDATE `Config` SET varvalue = 'not ({\$typeid_3} or {\$typeid_9} or {\$typeid_10} or {\$typeid_11})' WHERE varname = 'IPV4OBJ_LISTSRC'";
2437f548 1412
42fb3aa2
AD
1413 $query[] = "ALTER TABLE `EntityLink` MODIFY COLUMN `parent_entity_type` ENUM('location','object','rack','row') NOT NULL";
1414 $query[] = "ALTER TABLE `EntityLink` MODIFY COLUMN `child_entity_type` ENUM('location','object','rack','row') NOT NULL";
1415
75e7c0c6
DO
1416 $query[] = "UPDATE Config SET description = 'List source: objects for that asset tag should be set' WHERE varname = 'ASSETWARN_LISTSRC'";
1417 $query[] = "UPDATE Config SET description = 'List source: objects for that common name should be set' WHERE varname = 'NAMEWARN_LISTSRC'";
2437f548 1418 $query[] = "UPDATE Config SET varvalue = '0.20.7' WHERE varname = 'DB_VERSION'";
25355e1d 1419 break;
d15dae2f
DO
1420 case 'dictionary':
1421 $query = reloadDictionary();
1422 break;
fbbb74fb 1423 default:
87ae30c5
AD
1424 return NULL;
1425 }
1426 return $query;
1427}
1428
1429function executeUpgradeBatch ($batchid)
1430{
1431 global $dbxlink;
1432 $query = getUpgradeBatch($batchid);
a20a4e3c
DO
1433 if ($query === NULL)
1434 {
87ae30c5
AD
1435 showError ("unknown batch '${batchid}'", __FUNCTION__);
1436 die;
fbbb74fb 1437 }
fbbb74fb 1438 $failures = array();
4114697d 1439 echo "<tr><th>Executing batch '${batchid}'</th><td>";
fbbb74fb
DO
1440 foreach ($query as $q)
1441 {
babe4bf5
AA
1442 try
1443 {
1444 $result = $dbxlink->query ($q);
1445 }
1446 catch (PDOException $e)
758fe24c 1447 {
758fe24c
DO
1448 $errorInfo = $dbxlink->errorInfo();
1449 $failures[] = array ($q, $errorInfo[2]);
1450 }
fbbb74fb 1451 }
fbbb74fb 1452 if (!count ($failures))
4114697d 1453 echo "<strong><font color=green>done</font></strong>";
fbbb74fb
DO
1454 else
1455 {
4114697d 1456 echo "<strong><font color=red>The following queries failed:</font></strong><br><pre>";
fbbb74fb
DO
1457 foreach ($failures as $f)
1458 {
1459 list ($q, $i) = $f;
4114697d 1460 echo "${q} -- ${i}\n";
fbbb74fb 1461 }
4114697d 1462 echo "</pre>";
fbbb74fb 1463 }
4114697d 1464 echo '</td></tr>';
fbbb74fb
DO
1465}
1466
120e9ddd 1467function authenticate_admin ($username, $password)
a1f3710a 1468{
43c7895d 1469 global $dbxlink;
4dd08c61
DO
1470 $prepared = $dbxlink->prepare ('SELECT COUNT(*) FROM UserAccount WHERE user_id=1 AND user_name=? AND user_password_hash=?');
1471 if (!$prepared->execute (array ($username, sha1 ($password))))
a1f3710a 1472 die ('SQL query failed in ' . __FUNCTION__);
4dd08c61 1473 $rows = $prepared->fetchAll (PDO::FETCH_NUM);
43c7895d 1474 return $rows[0][0] == 1;
a1f3710a
DO
1475}
1476
c4ee2310
DO
1477// Database version detector. Should behave corretly on any
1478// working dataset a user might have.
1479function getDatabaseVersion ()
1480{
2f5e4db9
DO
1481 global $dbxlink;
1482 $prepared = $dbxlink->prepare ('SELECT varvalue FROM Config WHERE varname = "DB_VERSION" and vartype = "string"');
1483 if (! $prepared->execute())
c4ee2310 1484 {
c4ee2310 1485 $errorInfo = $dbxlink->errorInfo();
2f5e4db9 1486 die (__FUNCTION__ . ': SQL query failed with error ' . $errorInfo[2]);
c4ee2310 1487 }
2f5e4db9 1488 $rows = $prepared->fetchAll (PDO::FETCH_NUM);
c4ee2310
DO
1489 if (count ($rows) != 1 || !strlen ($rows[0][0]))
1490 die (__FUNCTION__ . ': Cannot guess database version. Config table is present, but DB_VERSION is missing or invalid. Giving up.');
1491 $ret = $rows[0][0];
1492 return $ret;
1493}
1494
71066ef1 1495function showUpgradeError ($info = '', $location = 'N/A')
c4ee2310
DO
1496{
1497 if (preg_match ('/\.php$/', $location))
1498 $location = basename ($location);
1499 elseif ($location != 'N/A')
1500 $location = $location . '()';
1501 echo "<div class=msg_error>An error has occured in [${location}]. ";
1502 if (!strlen ($info))
1503 echo 'No additional information is available.';
1504 else
1505 echo "Additional information:<br><p>\n<pre>\n${info}\n</pre></p>";
2f5e4db9 1506 echo "Go back or try starting from <a href='index.php'>index page</a>.<br></div>\n";
c4ee2310
DO
1507}
1508
9c64ccf8
AA
1509// changes the value of config variable. If $old_value_filter is set, value is changed only if current value equals to it.
1510function replaceConfigVarValue ($varname, $new_value, $old_value_filter = NULL)
1511{
1512 global $dbxlink;
1513 if (isset ($old_value_filter))
1514 {
1515 $result = $dbxlink->prepare ("SELECT varvalue FROM Config WHERE varname = ?");
1516 $result->execute (array ($varname));
1517 if ($row = $result->fetch (PDO::FETCH_ASSOC))
1518 if ($row['varvalue'] != $old_value_filter)
1519 return;
1520 unset ($result);
1521 }
1522 $result = $dbxlink->prepare ("UPDATE Config set varvalue = ? WHERE varname = ?");
1523 $result->execute (array ($new_value, $varname));
1524}
1525
964b0388 1526function renderUpgraderHTML()
99ee5479 1527{
b00cc78c
AA
1528 global $found_secret_file;
1529 if (! $found_secret_file)
1530 die ('<center>There is no working RackTables instance here, <a href="?module=installer">install</a>?</center>');
1531
1532 try
964b0388 1533 {
b00cc78c
AA
1534 connectDB();
1535 }
1536 catch (RackTablesError $e)
1537 {
1538 die ("Database connection failed:\n\n" . $e->getMessage());
1539 }
dec748f6 1540
b00cc78c
AA
1541 if
1542 (
1543 !isset ($_SERVER['PHP_AUTH_USER']) or
1544 !strlen ($_SERVER['PHP_AUTH_USER']) or
1545 !isset ($_SERVER['PHP_AUTH_PW']) or
1546 !strlen ($_SERVER['PHP_AUTH_PW']) or
1547 !authenticate_admin ($_SERVER['PHP_AUTH_USER'], $_SERVER['PHP_AUTH_PW'])
1548 )
1549 {
1550 header ('WWW-Authenticate: Basic realm="RackTables upgrade"');
1551 header ('HTTP/1.0 401 Unauthorized');
a931fc26
DO
1552?>
1553<h1>Trouble logging in?</h1>
f2b6ae86 1554You are trying to authenticate for the RackTables upgrade screen. This means that
a931fc26
DO
1555you must authenticate with the username and password of the main RackTables
1556administrator. There is only one such account in each installation, its default
1557username is "admin". RackTables wiki provides more information on this topic.
1558<?php
120e9ddd 1559 die;
964b0388 1560 }
fbbb74fb 1561
964b0388 1562?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
5f016d39 1563<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
2cf36003 1564<head><title>RackTables upgrade script</title>
964b0388
DO
1565<style type="text/css">
1566.tdleft {
1567 text-align: left;
1568}
1569
1570.trok {
1571 background-color: #80FF80;
1572}
1573
1574.trwarning {
1575 background-color: #FFFF80;
1576}
1577
1578.trerror {
1579 background-color: #FF8080;
1580}
1581</style>
5f016d39
DO
1582</head>
1583<body>
1584<h1>Platform check status</h1>
1585<?php
1586
1587if (!platform_is_ok())
50cc9dbe
DO
1588{
1589 echo '<h1>Please resolve the failed (red) item(s) above.</h1>';
5f016d39 1590 die ('</body></html>');
50cc9dbe 1591}
5f016d39
DO
1592
1593echo '<h1>Upgrade status</h1>';
fbbb74fb 1594$dbver = getDatabaseVersion();
5f016d39 1595echo '<table border=1 cellpadding=5>';
4114697d
DO
1596echo "<tr><th>Current status</th><td>Data version: ${dbver}<br>Code version: " . CODE_VERSION . "</td></tr>\n";
1597
1598$path = getDBUpgradePath ($dbver, CODE_VERSION);
1599if ($path === NULL)
758fe24c 1600{
4114697d 1601 echo "<tr><th>Upgrade path</th><td><font color=red>not found</font></td></tr>\n";
e4d00763
DO
1602 echo "<tr><th>Summary</th><td>Check README for more information. RackTables releases prior to 0.18.0 ";
1603 echo "must be upgraded to 0.18.0 first.</td></tr>\n";
758fe24c 1604}
4114697d 1605else
5f4027b8 1606{
4114697d
DO
1607 if (!count ($path))
1608 echo "<tr><th>Summary</th><td>Come back later.</td></tr>\n";
1609 else
1610 {
1611 echo "<tr><th>Upgrade path</th><td>${dbver} &rarr; " . implode (' &rarr; ', $path) . "</td></tr>\n";
abc799c5 1612 global $relnotes;
4114697d 1613 foreach ($path as $batchid)
4114697d 1614 if (isset ($relnotes[$batchid]))
5ae6d365 1615 echo "<tr><th>Release notes for ${batchid}</th><td><pre>" . $relnotes[$batchid] . "</pre></td></tr>\n";
a12022a9
DO
1616 if (array_key_exists ('reallyreally', $_REQUEST))
1617 {
1618 foreach ($path as $batchid)
1619 executeUpgradeBatch ($batchid);
d15dae2f 1620 executeUpgradeBatch ('dictionary');
a12022a9
DO
1621 echo "<tr><th>Summary</th><td>Upgrade complete, it is Ok to ";
1622 echo "<a href='index.php'>enter</a> the system.</td></tr>\n";
1623 }
1624 else
1625 {
1626 echo '<form method=post action="index.php?module=upgrade"><tr><th>Wait!</th>';
1627 echo '<td><p>RackTables database upgrades sometimes go wrong because of assorted reasons. ';
1628 echo 'It is <strong>highly recommended</strong> to make a database backup before ';
1629 echo 'proceeding any further. <tt>mysqldump</tt> and <tt>PHPMyAdmin</tt> are convenient ';
1630 echo 'tools for doing this.</p>';
1631 echo '<p><input type=checkbox name=reallyreally id=reallyreally><label for=reallyreally>';
1632 echo 'I am ready to bear all risks of this upgrade. I am ready to roll it back in case of ';
1633 echo 'a failure.</label> <input type=submit value="Yes, I am."></p></td></tr></form>';
4114697d 1634 }
4114697d 1635 }
5f4027b8 1636}
4114697d 1637echo '</table>';
5f016d39 1638echo '</body></html>';
964b0388 1639}
fbbb74fb 1640
71066ef1
AA
1641function convertSLBTablesToBinIPs()
1642{
1643 global $dbxlink;
1644
1645 $dbxlink->query ("DROP TABLE IF EXISTS `IPv4VS_new`, `IPv4RS_new`, `IPv4VS_old`, `IPv4RS_old`");
1646
1647 $dbxlink->query (<<<END
1648CREATE TABLE `IPv4VS_new` (
1649 `id` int(10) unsigned NOT NULL auto_increment,
1650 `vip` varbinary(16) NOT NULL,
1651 `vport` smallint(5) unsigned default NULL,
1652 `proto` enum('TCP','UDP','MARK') NOT NULL default 'TCP',
1653 `name` char(255) default NULL,
1654 `vsconfig` text,
1655 `rsconfig` text,
1656 PRIMARY KEY (`id`),
1657 KEY `vip` (`vip`)
1658) ENGINE=InnoDB DEFAULT CHARSET=utf8
1659END
1660 );
1661 $result = $dbxlink->query ("SELECT * FROM IPv4VS");
1662 $rows = $result->fetchAll (PDO::FETCH_ASSOC);
1663 unset ($result);
1664 foreach ($rows as $row)
1665 {
1666 $row['vip'] = ip4_int2bin ($row['vip']);
1667 usePreparedInsertBlade ('IPv4VS_new', $row);
1668 }
dec748f6 1669
71066ef1
AA
1670 $dbxlink->query (<<<END
1671CREATE TABLE `IPv4RS_new` (
1672 `id` int(10) unsigned NOT NULL auto_increment,
1673 `inservice` enum('yes','no') NOT NULL default 'no',
1674 `rsip` varbinary(16) NOT NULL,
1675 `rsport` smallint(5) unsigned default NULL,
1676 `rspool_id` int(10) unsigned default NULL,
1677 `rsconfig` text,
1678 `comment` varchar(255) DEFAULT NULL,
1679 PRIMARY KEY (`id`),
1680 KEY `rsip` (`rsip`),
1681 UNIQUE KEY `pool-endpoint` (`rspool_id`,`rsip`,`rsport`),
1682 CONSTRAINT `IPRS-FK` FOREIGN KEY (`rspool_id`) REFERENCES `IPv4RSPool` (`id`) ON DELETE CASCADE
1683) ENGINE=InnoDB DEFAULT CHARSET=utf8
1684END
1685 );
1686 $result = $dbxlink->query ("SELECT * FROM IPv4RS");
1687 $rows = $result->fetchAll (PDO::FETCH_ASSOC);
1688 unset ($result);
1689 foreach ($rows as $row)
1690 {
1691 $row['rsip'] = ip4_int2bin ($row['rsip']);
1692 usePreparedInsertBlade ('IPv4RS_new', $row);
1693 }
1694
1695 $dbxlink->query (<<<END
1696RENAME TABLE
1697 `IPv4VS` TO `IPv4VS_old`,
1698 `IPv4VS_new` TO `IPv4VS`,
1699 `IPv4RS` TO `IPv4RS_old`,
1700 `IPv4RS_new` TO `IPv4RS`
1701END
1702 );
1703 // re-create foreign key in IPv4LB
1704 $dbxlink->query ("ALTER TABLE `IPv4LB` DROP FOREIGN KEY `IPv4LB-FK-vs_id`");
1705 $dbxlink->query ("ALTER TABLE `IPv4LB` ADD CONSTRAINT `IPv4LB-FK-vs_id` FOREIGN KEY (`vs_id`) REFERENCES `IPv4VS` (`id`)");
1706
1707 $dbxlink->query ("DROP TABLE `IPv4VS_old`, `IPv4RS_old`");
93b7c5d8
AA
1708
1709 // re-create foreign key in IPv4RS
1710 $dbxlink->query ("ALTER TABLE `IPv4RS` DROP FOREIGN KEY `IPRS-FK`");
1711 $dbxlink->query ("ALTER TABLE `IPv4RS` ADD CONSTRAINT `IPv4RS-FK` FOREIGN KEY (`rspool_id`) REFERENCES `IPv4RSPool` (`id`) ON DELETE CASCADE");
71066ef1
AA
1712}
1713
1714// This is a swiss-knife blade to insert a record into a table.
1715// The first argument is table name.
1716// The second argument is an array of "name" => "value" pairs.
1717// returns integer - affected rows count. Throws exception on error
1718function usePreparedInsertBlade ($tablename, $columns)
1719{
1720 global $dbxlink;
1721 $query = "INSERT INTO ${tablename} (" . implode (', ', array_keys ($columns));
1722 $query .= ') VALUES (' . questionMarks (count ($columns)) . ')';
1723 // Now the query should be as follows:
1724 // INSERT INTO table (c1, c2, c3) VALUES (?, ?, ?)
1725 try
1726 {
1727 $prepared = $dbxlink->prepare ($query);
1728 $prepared->execute (array_values ($columns));
1729 return $prepared->rowCount();
1730 }
1731 catch (PDOException $e)
1732 {
1733 throw convertPDOException ($e);
1734 }
1735}
1736
fd3ef4e7
AA
1737// converts the values of old-style config vars TELNET_OBJS_LISTSRC, SSH_OBJS_LISTSRC, RDP_OBJS_LISTSRC
1738// to the format of MGMT_PROTOS (comma-separated list of "proto: rackcode" pairs)
1739function convertMgmtConfigVars()
1740{
1741 global $dbxlink;
1742 $ret = array();
1743 foreach (array ('telnet' => 'TELNET_OBJS_LISTSRC', 'ssh' => 'SSH_OBJS_LISTSRC', 'rdp' => 'RDP_OBJS_LISTSRC') as $proto => $varname)
1744 {
1745 $result = $dbxlink->prepare ("SELECT varvalue FROM Config WHERE varname = ?");
1746 $result->execute (array ($varname));
1747 if ($row = $result->fetch (PDO::FETCH_ASSOC))
1748 if ($row['varvalue'] != 'false' && $row['varvalue'] != '')
1749 $ret[] = "$proto: " . $row['varvalue'];
1750 unset ($result);
1751 }
1752 return implode (',', $ret);
1753}
1754
fbbb74fb 1755?>