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