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