2097f3758dba0a826426c1546956c51523bae7ec
[racktables] / wwwroot / inc / upgrade.php
1 <?php
2
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
7 $relnotes = array
8 (
9 '0.18.2' => <<<ENDOFTEXT
10 RackTables from its version 0.18.0 and later is not compatible with
11 RHEL/CentOS (at least with versions up to 5.5) Linux distributions
12 in their default installation. There are yet options to work around that:
13 1. Install RackTables on a server with a different distribution/OS.
14 2. Request Linux distribution vendor to fix the bug with PCRE.
15 3. Repair your RHEL/CentOS installation yourself by fixing its PCRE
16 RPM as explained here: http://bugs.centos.org/view.php?id=3252
17 ENDOFTEXT
18 ,
19 '0.19.0' => <<<ENDOFTEXT
20 The files, which are intended for the httpd (web-server) directory, are
21 now in the "wwwroot" directory of the tar.gz archive. Files outside of
22 that directory are not directly intended for httpd environment and should
23 not be copied to the server.
24
25 This release incorporates ObjectLog functionality, which used to be
26 available as a separate plugin. For the best results it is advised to
27 disable (through local.php) external ObjectLog plugin permanently before
28 the new version is installed. All previously accumulated ObjectLog records
29 will be available through the updated standard interface.
30
31 RackTables is now using PHP JSON extension which is included in the PHP
32 core since 5.2.0.
33
34 The barcode attribute was removed. The upgrade script attempts to
35 preserve the data by moving it to either the 'OEM S/N 1' attribute or to
36 a Log entry. You should backup your database beforehand anyway.
37 ENDOFTEXT
38 ,
39 '0.19.2' => <<<ENDOFTEXT
40 This release is different in filesystem layout. The "gateways" directory
41 has been moved from "wwwroot" directory. This improves security a bit.
42 You can also separate your local settings and add-ons from the core RackTables code.
43 To do that, put a single index.php file into the DocumentRoot of your http server:
44
45 <?php
46 \$racktables_confdir='/directory/where/your/secret.php/and/local.php/files/are/stored';
47 require '/directory_where_you_extracted_racktables_distro/wwwroot/index.php';
48 ?>
49
50 No more files are needed to be available directly over the HTTP.
51 Full 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.
60 ENDOFTEXT
61 ,
62
63 '0.19.13' => <<<ENDOFTEXT
64 A 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
66 must be in the format "mm/dd/yyyy" otherwise the conversion will fail.
67 ENDOFTEXT
68 ,
69
70 '0.20.0' => <<<ENDOFTEXT
71 WARNING: This release have too many internal changes, some of them were waiting more than a year
72 to be released. So this release is considered "BETA" and is recommended only to curiuos users,
73 who agree to sacrifice the stability to the progress.
74
75 Racks and Rows are now stored in the database as Objects. The RackObject table
76 was renamed to Object. SQL views were created to ease the migration of custom
77 reports and scripts.
78
79 New plugins engine instead of local.php file. To make your own code stored in local.php work,
80 you must move the local.php file into the plugins/ directory. The name of this file does not
81 matter any more. You also can store multiple files in that dir, separate your plugins by features,
82 share them and try the plugins from other people just placing them into plugins/ dir, no more merging.
83 \$path_to_local_php variable has no special meaning any more.
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
87 Beginning with this version it is possible to delete IP prefixes, VLANs, Virtual services
88 and RS pools from within theirs properties tab. So please inspect your permissions rules
89 to assure there are no undesired allows for deletion of these objects. To ensure this, you could
90 try 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
95 Hardware gateways engine was rewritten in this version of RackTables. This means that
96 the file gateways/deviceconfig/switch.secrets.php is not used any more. To get information
97 about configuring connection properties and credentials in a new way please visit
98 http://wiki.racktables.org/index.php/Gateways
99
100 This also means that recently added features based on old API (D-Link switches and Linux
101 gateway support contributed by Ilya Evseev) are not working any more and waiting to be
102 forward-ported to new gateways API. Sorry for that.
103
104 Two 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
113 Also some of config variables have changed their default values in this version.
114 This means that upgrade script will change their values if you have them in previous default state.
115 This could be inconvenient, but it is the most effective way to encourage users to use new features.
116 If 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
124 Also please note that variable IPV4_TREE_RTR_AS_CELL now has third special value
125 besides 'yes' and 'no': 'none'. Use 'none' value if you are experiencing low performance
126 on IP tree page. It will completely disable IP ranges scan for used/spare IPs and the
127 speed of IP tree will increase radically. The price is you will not see the routers in
128 IP tree at all.
129
130 ENDOFTEXT
131 ,
132
133 '0.20.1' => <<<ENDOFTEXT
134 The 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
136 Security 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}
138 This 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}
140
141 ENDOFTEXT
142 ,
143
144 '0.20.4' => <<<ENDOFTEXT
145 Please note that some dictionary items of Cisco Catalyst 2960 series switches
146 were 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
156
157 The DATETIME_FORMAT configuration option used in setting date and time output
158 format now uses a different [1] syntax. During upgrade the option is reset to
159 the default value, which is now %Y-%m-%d (YYYY-MM-DD) per ISO 8601.
160
161 This release intoduces two new configuration options:
162 REVERSED_RACKS_LISTSRC and NEAREST_RACKS_CHECKBOX.
163
164 [1] http://php.net/manual/en/function.strftime.php
165 ENDOFTEXT
166 ,
167
168 '0.20.5' => <<<ENDOFTEXT
169 This release introduces the VS groups feature. VS groups is a new way to store
170 and display virtual services configuration. There is a new "ipvs" (VS group)
171 realm. All previously existing VS configuration remains functional and user
172 is free to convert it to the new format, which displays it in a more natural way
173 and allows to generate virtual_server_group keepalived configs. To convert a
174 virtual service to the new format, it is necessary to manually create a VS group
175 object and assign IP addresses to it. The VS group will display a "Migrate" tab
176 to convert the old-style VS objects, which can be removed after a successful
177 conversion.
178
179 The old-style VS configuration becomes DEPRECATED. Its support will be removed
180 in a future major release. So it is strongly recommended to convert it to the
181 new format.
182 ENDOFTEXT
183 ,
184
185 '0.20.6' => <<<ENDOFTEXT
186 This release uses database triggers for consistency measures. The database
187 user account must have the 'TRIGGER' privilege, which was introduced in
188 MySQL 5.1.7.
189
190 Cable paths can be traced and displayed in a graphical format. This requires
191 the Image_GraphViz PEAR module (http://pear.php.net/package/Image_GraphViz).
192
193 New MGMT_PROTOS configuration option replaces the TELNET_OBJS_LISTSRC,
194 SSH_OBJS_LISTSRC and RDP_OBJS_LISTSRC options (converting existing settings as
195 necessary). MGMT_PROTOS allows to specify any management protocol for a
196 particular device list using a RackCode filter. The default value
197 ("ssh: {\$typeid_4}, telnet: {\$typeid_8}") produces "ssh://server.fqdn" for
198 servers and "telnet://switch.fqdn" for network switches.
199 ENDOFTEXT
200 ,
201 );
202
203 // At the moment we assume, that for any two releases we can
204 // sequentally execute all batches, that separate them, and
205 // nothing will break. If this changes one day, the function
206 // below will have to generate smarter upgrade paths, while
207 // the upper layer will remain the same.
208 // Returning an empty array means that no upgrade is necessary.
209 // Returning NULL indicates an error.
210 function getDBUpgradePath ($v1, $v2)
211 {
212 $versionhistory = array
213 (
214 '0.18.0',
215 '0.18.1',
216 '0.18.2',
217 '0.18.3',
218 '0.18.4',
219 '0.18.5',
220 '0.18.6',
221 '0.18.7',
222 '0.19.0',
223 '0.19.1',
224 '0.19.2',
225 '0.19.3',
226 '0.19.4',
227 '0.19.5',
228 '0.19.6',
229 '0.19.7',
230 '0.19.8',
231 '0.19.9',
232 '0.19.10',
233 '0.19.11',
234 '0.19.12',
235 '0.19.13',
236 '0.19.14',
237 '0.20.0',
238 '0.20.1',
239 '0.20.2',
240 '0.20.3',
241 '0.20.4',
242 '0.20.5',
243 '0.20.6',
244 );
245 if (!in_array ($v1, $versionhistory) or !in_array ($v2, $versionhistory))
246 return NULL;
247 $skip = TRUE;
248 $path = NULL;
249 // foreach() below cannot handle this specific case
250 if ($v1 == $v2)
251 return array();
252 // Now collect all versions > $v1 and <= $v2
253 foreach ($versionhistory as $v)
254 {
255 if ($skip and $v == $v1)
256 {
257 $skip = FALSE;
258 $path = array();
259 continue;
260 }
261 if ($skip)
262 continue;
263 $path[] = $v;
264 if ($v == $v2)
265 break;
266 }
267 return $path;
268 }
269
270 // Upgrade batches are named exactly as the release where they first appear.
271 // That is simple, but seems sufficient for beginning.
272 function getUpgradeBatch ($batchid)
273 {
274 $query = array();
275 global $dbxlink;
276 switch ($batchid)
277 {
278 case '0.18.0':
279 $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')";
280 $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')";
281 $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')";
282 $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')";
283 $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')";
284 $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')";
285 $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')";
286 $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')";
287 $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')";
288 $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')";
289 $query[] = "ALTER TABLE IPv4Network ENGINE=InnoDB";
290 $query[] = "SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0";
291 $query[] = "
292 CREATE TABLE `CachedPAV` (
293 `object_id` int(10) unsigned NOT NULL,
294 `port_name` char(255) NOT NULL,
295 `vlan_id` int(10) unsigned NOT NULL default '0',
296 PRIMARY KEY (`object_id`,`port_name`,`vlan_id`),
297 KEY `vlan_id` (`vlan_id`),
298 CONSTRAINT `CachedPAV-FK-object-port` FOREIGN KEY (`object_id`, `port_name`) REFERENCES `CachedPVM` (`object_id`, `port_name`) ON DELETE CASCADE,
299 CONSTRAINT `CachedPAV-FK-vlan_id` FOREIGN KEY (`vlan_id`) REFERENCES `VLANValidID` (`vlan_id`)
300 ) ENGINE=InnoDB
301 ";
302 $query[] = "
303 CREATE TABLE `CachedPNV` (
304 `object_id` int(10) unsigned NOT NULL,
305 `port_name` char(255) NOT NULL,
306 `vlan_id` int(10) unsigned NOT NULL default '0',
307 PRIMARY KEY (`object_id`,`port_name`,`vlan_id`),
308 UNIQUE KEY `port_id` (`object_id`,`port_name`),
309 CONSTRAINT `CachedPNV-FK-compound` FOREIGN KEY (`object_id`, `port_name`, `vlan_id`) REFERENCES `CachedPAV` (`object_id`, `port_name`, `vlan_id`) ON DELETE CASCADE
310 ) ENGINE=InnoDB
311 ";
312 $query[] = "
313 CREATE TABLE `CachedPVM` (
314 `object_id` int(10) unsigned NOT NULL,
315 `port_name` char(255) NOT NULL,
316 `vlan_mode` enum('access','trunk') NOT NULL default 'access',
317 PRIMARY KEY (`object_id`,`port_name`),
318 CONSTRAINT `CachedPVM-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE
319 ) ENGINE=InnoDB
320 ";
321 $query[] = "
322 CREATE TABLE `PortAllowedVLAN` (
323 `object_id` int(10) unsigned NOT NULL,
324 `port_name` char(255) NOT NULL,
325 `vlan_id` int(10) unsigned NOT NULL default '0',
326 PRIMARY KEY (`object_id`,`port_name`,`vlan_id`),
327 KEY `vlan_id` (`vlan_id`),
328 CONSTRAINT `PortAllowedVLAN-FK-object-port` FOREIGN KEY (`object_id`, `port_name`) REFERENCES `PortVLANMode` (`object_id`, `port_name`) ON DELETE CASCADE,
329 CONSTRAINT `PortAllowedVLAN-FK-vlan_id` FOREIGN KEY (`vlan_id`) REFERENCES `VLANValidID` (`vlan_id`)
330 ) ENGINE=InnoDB
331 ";
332 $query[] = "
333 CREATE TABLE `PortNativeVLAN` (
334 `object_id` int(10) unsigned NOT NULL,
335 `port_name` char(255) NOT NULL,
336 `vlan_id` int(10) unsigned NOT NULL default '0',
337 PRIMARY KEY (`object_id`,`port_name`,`vlan_id`),
338 UNIQUE KEY `port_id` (`object_id`,`port_name`),
339 CONSTRAINT `PortNativeVLAN-FK-compound` FOREIGN KEY (`object_id`, `port_name`, `vlan_id`) REFERENCES `PortAllowedVLAN` (`object_id`, `port_name`, `vlan_id`) ON DELETE CASCADE
340 ) ENGINE=InnoDB
341 ";
342 $query[] = "
343 CREATE TABLE `PortVLANMode` (
344 `object_id` int(10) unsigned NOT NULL,
345 `port_name` char(255) NOT NULL,
346 `vlan_mode` enum('access','trunk') NOT NULL default 'access',
347 PRIMARY KEY (`object_id`,`port_name`),
348 CONSTRAINT `PortVLANMode-FK-object-port` FOREIGN KEY (`object_id`, `port_name`) REFERENCES `CachedPVM` (`object_id`, `port_name`)
349 ) ENGINE=InnoDB
350 ";
351 $query[] = "
352 CREATE TABLE `VLANDescription` (
353 `domain_id` int(10) unsigned NOT NULL,
354 `vlan_id` int(10) unsigned NOT NULL default '0',
355 `vlan_type` enum('ondemand','compulsory','alien') NOT NULL default 'ondemand',
356 `vlan_descr` char(255) default NULL,
357 PRIMARY KEY (`domain_id`,`vlan_id`),
358 KEY `vlan_id` (`vlan_id`),
359 CONSTRAINT `VLANDescription-FK-domain_id` FOREIGN KEY (`domain_id`) REFERENCES `VLANDomain` (`id`) ON DELETE CASCADE,
360 CONSTRAINT `VLANDescription-FK-vlan_id` FOREIGN KEY (`vlan_id`) REFERENCES `VLANValidID` (`vlan_id`)
361 ) ENGINE=InnoDB
362 ";
363 $query[] = "
364 CREATE TABLE `VLANDomain` (
365 `id` int(10) unsigned NOT NULL auto_increment,
366 `description` char(255) default NULL,
367 PRIMARY KEY (`id`),
368 UNIQUE KEY `description` (`description`)
369 ) ENGINE=InnoDB
370 ";
371 $query[] = "
372 CREATE TABLE `VLANIPv4` (
373 `domain_id` int(10) unsigned NOT NULL,
374 `vlan_id` int(10) unsigned NOT NULL,
375 `ipv4net_id` int(10) unsigned NOT NULL,
376 UNIQUE KEY `network-domain` (`ipv4net_id`,`domain_id`),
377 KEY `VLANIPv4-FK-compound` (`domain_id`,`vlan_id`),
378 CONSTRAINT `VLANIPv4-FK-compound` FOREIGN KEY (`domain_id`, `vlan_id`) REFERENCES `VLANDescription` (`domain_id`, `vlan_id`) ON DELETE CASCADE,
379 CONSTRAINT `VLANIPv4-FK-ipv4net_id` FOREIGN KEY (`ipv4net_id`) REFERENCES `IPv4Network` (`id`) ON DELETE CASCADE
380 ) ENGINE=InnoDB
381 ";
382 $query[] = "
383 CREATE TABLE `VLANSTRule` (
384 `vst_id` int(10) unsigned NOT NULL,
385 `rule_no` int(10) unsigned NOT NULL,
386 `port_pcre` char(255) NOT NULL,
387 `port_role` enum('access','trunk','uplink','downlink','none') NOT NULL default 'none',
388 `wrt_vlans` char(255) default NULL,
389 `description` char(255) default NULL,
390 UNIQUE KEY `vst-rule` (`vst_id`,`rule_no`),
391 CONSTRAINT `VLANSTRule-FK-vst_id` FOREIGN KEY (`vst_id`) REFERENCES `VLANSwitchTemplate` (`id`) ON DELETE CASCADE
392 ) ENGINE=InnoDB
393 ";
394 $query[] = "
395 CREATE TABLE `VLANSwitch` (
396 `object_id` int(10) unsigned NOT NULL,
397 `domain_id` int(10) unsigned NOT NULL,
398 `template_id` int(10) unsigned NOT NULL,
399 `mutex_rev` int(10) unsigned NOT NULL default '0',
400 `out_of_sync` enum('yes','no') NOT NULL default 'yes',
401 `last_errno` int(10) unsigned NOT NULL default '0',
402 `last_change` timestamp NOT NULL default '0000-00-00 00:00:00',
403 `last_push_started` timestamp NOT NULL default '0000-00-00 00:00:00',
404 `last_push_finished` timestamp NOT NULL default '0000-00-00 00:00:00',
405 `last_error_ts` timestamp NOT NULL default '0000-00-00 00:00:00',
406 UNIQUE KEY `object_id` (`object_id`),
407 KEY `domain_id` (`domain_id`),
408 KEY `template_id` (`template_id`),
409 KEY `out_of_sync` (`out_of_sync`),
410 KEY `last_errno` (`last_errno`),
411 CONSTRAINT `VLANSwitch-FK-domain_id` FOREIGN KEY (`domain_id`) REFERENCES `VLANDomain` (`id`),
412 CONSTRAINT `VLANSwitch-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`),
413 CONSTRAINT `VLANSwitch-FK-template_id` FOREIGN KEY (`template_id`) REFERENCES `VLANSwitchTemplate` (`id`)
414 ) ENGINE=InnoDB
415 ";
416 $query[] = "
417 CREATE TABLE `VLANSwitchTemplate` (
418 `id` int(10) unsigned NOT NULL auto_increment,
419 `max_local_vlans` int(10) unsigned default NULL,
420 `description` char(255) default NULL,
421 PRIMARY KEY (`id`),
422 UNIQUE KEY `description` (`description`)
423 ) ENGINE=InnoDB
424 ";
425 $query[] = "
426 CREATE TABLE `VLANValidID` (
427 `vlan_id` int(10) unsigned NOT NULL default '1',
428 PRIMARY KEY (`vlan_id`)
429 ) ENGINE=InnoDB
430 ";
431 $query[] = "SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS";
432 for ($i = 1; $i <= 4094; $i++)
433 $query[] = "INSERT INTO VLANValidID (vlan_id) VALUES (${i})";
434 $query[] = "UPDATE Config SET varvalue = '0.18.0' WHERE varname = 'DB_VERSION'";
435 break;
436 case '0.18.1':
437 $query[] = "ALTER TABLE Atom ENGINE=InnoDB";
438 $query[] = "ALTER TABLE AttributeMap ENGINE=InnoDB";
439 $query[] = "ALTER TABLE Config ENGINE=InnoDB";
440 $query[] = "ALTER TABLE IPv4Address ENGINE=InnoDB";
441 $query[] = "ALTER TABLE IPv4Allocation ENGINE=InnoDB";
442 $query[] = "ALTER TABLE Molecule ENGINE=InnoDB";
443 $query[] = "ALTER TABLE MountOperation ENGINE=InnoDB";
444 $query[] = "ALTER TABLE PortCompat ENGINE=InnoDB";
445 $query[] = "ALTER TABLE Rack ENGINE=InnoDB";
446 $query[] = "ALTER TABLE RackHistory ENGINE=InnoDB";
447 $query[] = "ALTER TABLE RackObjectHistory ENGINE=InnoDB";
448 $query[] = "ALTER TABLE RackRow ENGINE=InnoDB";
449 $query[] = "ALTER TABLE RackSpace ENGINE=InnoDB";
450 $query[] = "ALTER TABLE Script ENGINE=InnoDB";
451 $query[] = "ALTER TABLE AttributeValue DROP FOREIGN KEY `AttributeValue-FK-object_id`";
452 $query[] = "ALTER TABLE AttributeValue ADD CONSTRAINT `AttributeValue-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE";
453 $query[] = "ALTER TABLE RackObjectHistory ADD KEY (id)";
454 $query[] = "ALTER TABLE RackObjectHistory ADD CONSTRAINT `RackObjectHistory-FK-object_id` FOREIGN KEY (id) REFERENCES `RackObject` (`id`) ON DELETE CASCADE";
455 $query[] = "ALTER TABLE MountOperation ADD CONSTRAINT `MountOperation-FK-object_id` FOREIGN KEY (object_id) REFERENCES RackObject (id) ON DELETE CASCADE";
456 $query[] = "ALTER TABLE RackSpace ADD CONSTRAINT `RackSpace-FK-object_id` FOREIGN KEY (object_id) REFERENCES RackObject (id) ON DELETE CASCADE";
457 $query[] = "ALTER TABLE Link DROP FOREIGN KEY `Link-FK-a`";
458 $query[] = "ALTER TABLE Link ADD CONSTRAINT `Link-FK-a` FOREIGN KEY (`porta`) REFERENCES `Port` (`id`) ON DELETE CASCADE";
459 $query[] = "ALTER TABLE Link DROP FOREIGN KEY `Link-FK-b`";
460 $query[] = "ALTER TABLE Link ADD CONSTRAINT `Link-FK-b` FOREIGN KEY (`portb`) REFERENCES `Port` (`id`) ON DELETE CASCADE";
461 $query[] = "ALTER TABLE Port DROP FOREIGN KEY `Port-FK-object_id`";
462 $query[] = "ALTER TABLE Port ADD CONSTRAINT `Port-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE";
463 $query[] = "ALTER TABLE AttributeMap MODIFY `chapter_id` int(10) unsigned default NULL";
464 $query[] = "ALTER TABLE IPv4Address MODIFY `ip` int(10) unsigned NOT NULL default '0'";
465 $query[] = "ALTER TABLE IPv4Address MODIFY `name` char(255) NOT NULL default ''";
466 $query[] = "ALTER TABLE IPv4Allocation MODIFY `object_id` int(10) unsigned NOT NULL default '0'";
467 $query[] = "ALTER TABLE IPv4Allocation MODIFY `ip` int(10) unsigned NOT NULL default '0'";
468 $query[] = "ALTER TABLE IPv4Allocation MODIFY `name` char(255) NOT NULL default ''";
469 $query[] = "ALTER TABLE IPv4NAT MODIFY `object_id` int(10) unsigned NOT NULL default '0'";
470 $query[] = "ALTER TABLE IPv4NAT MODIFY `proto` enum('TCP','UDP') NOT NULL default 'TCP'";
471 $query[] = "ALTER TABLE IPv4NAT MODIFY `localip` int(10) unsigned NOT NULL default '0'";
472 $query[] = "ALTER TABLE IPv4NAT MODIFY `localport` smallint(5) unsigned NOT NULL default '0'";
473 $query[] = "ALTER TABLE IPv4NAT MODIFY `remoteip` int(10) unsigned NOT NULL default '0'";
474 $query[] = "ALTER TABLE IPv4NAT MODIFY `remoteport` smallint(5) unsigned NOT NULL default '0'";
475 $query[] = "ALTER TABLE IPv4Network MODIFY `ip` int(10) unsigned NOT NULL default '0'";
476 $query[] = "ALTER TABLE IPv4Network MODIFY `mask` int(10) unsigned NOT NULL default '0'";
477 $query[] = "ALTER TABLE Link MODIFY `porta` int(10) unsigned NOT NULL default '0'";
478 $query[] = "ALTER TABLE Link MODIFY `portb` int(10) unsigned NOT NULL default '0'";
479 $query[] = "ALTER TABLE MountOperation MODIFY `object_id` int(10) unsigned NOT NULL default '0'";
480 $query[] = "ALTER TABLE MountOperation MODIFY `ctime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP";
481 $query[] = "ALTER TABLE Port MODIFY `object_id` int(10) unsigned NOT NULL default '0'";
482 $query[] = "ALTER TABLE Port MODIFY `name` char(255) NOT NULL default ''";
483 $query[] = "ALTER TABLE Port MODIFY `type` int(10) unsigned NOT NULL default '0'";
484 $query[] = "ALTER TABLE PortCompat MODIFY `type1` int(10) unsigned NOT NULL default '0'";
485 $query[] = "ALTER TABLE PortCompat MODIFY `type2` int(10) unsigned NOT NULL default '0'";
486 $query[] = "ALTER TABLE RackHistory MODIFY `ctime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP";
487 $query[] = "ALTER TABLE RackObjectHistory MODIFY `ctime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP";
488 $query[] = "ALTER TABLE TagStorage MODIFY `tag_id` int(10) unsigned NOT NULL default '0'";
489 $query[] = "ALTER TABLE UserAccount MODIFY `user_name` char(64) NOT NULL default ''";
490 $query[] = "UPDATE Config SET varvalue = '0.18.1' WHERE varname = 'DB_VERSION'";
491 break;
492 case '0.18.2':
493 $query[] = "ALTER TABLE Rack ADD CONSTRAINT `Rack-FK-row_id` FOREIGN KEY (row_id) REFERENCES RackRow (id)";
494 $query[] = "ALTER TABLE RackRow ADD UNIQUE KEY `name` (name)";
495 $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')";
496 $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')";
497 $query[] = "UPDATE Config SET varvalue = '0.18.2' WHERE varname = 'DB_VERSION'";
498 break;
499 case '0.18.3':
500 $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'";
501 $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)')";
502 $query[] = "UPDATE Config SET varvalue = '0.18.3' WHERE varname = 'DB_VERSION'";
503 break;
504 case '0.18.4':
505 $query[] = "ALTER TABLE VLANSTRule MODIFY port_role enum('access','trunk','anymode','uplink','downlink','none') NOT NULL default 'none'";
506 $query[] = "UPDATE Config SET varvalue = '0.18.4' WHERE varname = 'DB_VERSION'";
507 break;
508 case '0.18.5':
509 $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')";
510 $query[] = "ALTER TABLE `IPv4LB` ADD COLUMN `prio` int(10) unsigned DEFAULT NULL AFTER `vs_id`";
511 $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')";
512 $query[] = "UPDATE Config SET varvalue = '0.18.5' WHERE varname = 'DB_VERSION'";
513 break;
514 case '0.18.6':
515 $query[] = "UPDATE Config SET varvalue = '0.18.6' WHERE varname = 'DB_VERSION'";
516 break;
517 case '0.18.7':
518 $query[] = "UPDATE Config SET varvalue = '0.18.7' WHERE varname = 'DB_VERSION'";
519 break;
520 case '0.19.0':
521 $query[] = 'ALTER TABLE `File` ADD `thumbnail` LONGBLOB NULL AFTER `atime`';
522 $query[] = "
523 CREATE TABLE `IPv6Address` (
524 `ip` binary(16) NOT NULL,
525 `name` char(255) NOT NULL default '',
526 `reserved` enum('yes','no') default NULL,
527 PRIMARY KEY (`ip`)
528 ) ENGINE=InnoDB
529 ";
530 $query[] = "
531 CREATE TABLE `IPv6Allocation` (
532 `object_id` int(10) unsigned NOT NULL default '0',
533 `ip` binary(16) NOT NULL,
534 `name` char(255) NOT NULL default '',
535 `type` enum('regular','shared','virtual','router') default NULL,
536 PRIMARY KEY (`object_id`,`ip`),
537 CONSTRAINT `IPv6Allocation-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE
538 ) ENGINE=InnoDB
539 ";
540 $query[] = "
541 CREATE TABLE `IPv6Network` (
542 `id` int(10) unsigned NOT NULL auto_increment,
543 `ip` binary(16) NOT NULL,
544 `mask` int(10) unsigned NOT NULL,
545 `last_ip` binary(16) NOT NULL,
546 `name` char(255) default NULL,
547 `comment` text,
548 PRIMARY KEY (`id`),
549 UNIQUE KEY `ip` (`ip`,`mask`)
550 ) ENGINE=InnoDB
551 ";
552 $query[] = "
553 CREATE TABLE `VLANIPv6` (
554 `domain_id` int(10) unsigned NOT NULL,
555 `vlan_id` int(10) unsigned NOT NULL,
556 `ipv6net_id` int(10) unsigned NOT NULL,
557 UNIQUE KEY `network-domain` (`ipv6net_id`,`domain_id`),
558 KEY `VLANIPv6-FK-compound` (`domain_id`,`vlan_id`),
559 CONSTRAINT `VLANIPv6-FK-compound` FOREIGN KEY (`domain_id`, `vlan_id`) REFERENCES `VLANDescription` (`domain_id`, `vlan_id`) ON DELETE CASCADE,
560 CONSTRAINT `VLANIPv6-FK-ipv6net_id` FOREIGN KEY (`ipv6net_id`) REFERENCES `IPv6Network` (`id`) ON DELETE CASCADE
561 ) ENGINE=InnoDB
562 ";
563 $query[] = "
564 CREATE TABLE IF NOT EXISTS `ObjectLog` (
565 `id` int(10) NOT NULL AUTO_INCREMENT,
566 `object_id` int(10) NOT NULL,
567 `user` varchar(64) NOT NULL,
568 `date` datetime NOT NULL,
569 `content` text NOT NULL,
570 PRIMARY KEY (`id`)
571 ) ENGINE=InnoDB
572 ";
573 # Now we have the same structure of ObjectLog table, which objectlog.php
574 # could have left. Subsequent column updates will handle any existing data.
575 $query[] = "ALTER TABLE ObjectLog MODIFY COLUMN `id` int(10) unsigned NOT NULL AUTO_INCREMENT";
576 $query[] = "ALTER TABLE ObjectLog MODIFY COLUMN `object_id` int(10) unsigned NOT NULL";
577 $query[] = "ALTER TABLE ObjectLog MODIFY COLUMN `user` char(64) NOT NULL";
578 $query[] = "ALTER TABLE ObjectLog ADD KEY `object_id` (`object_id`)";
579 $query[] = "ALTER TABLE ObjectLog ADD KEY `date` (`date`)";
580 $query[] = "ALTER TABLE ObjectLog ADD CONSTRAINT `ObjectLog-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE";
581 # Now it's the way 0.19.0 is expecting it to be.
582 $query[] = "
583 CREATE TABLE `ObjectParentCompat` (
584 `parent_objtype_id` int(10) unsigned NOT NULL,
585 `child_objtype_id` int(10) unsigned NOT NULL,
586 UNIQUE KEY `parent_child` (`parent_objtype_id`,`child_objtype_id`)
587 ) ENGINE=InnoDB
588 ";
589 $query[] = "
590 CREATE TABLE `EntityLink` (
591 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
592 `parent_entity_type` enum('ipv4net','ipv4rspool','ipv4vs','ipv6net','object','rack','user') NOT NULL,
593 `parent_entity_id` int(10) unsigned NOT NULL,
594 `child_entity_type` enum('file','object') NOT NULL,
595 `child_entity_id` int(10) unsigned NOT NULL,
596 PRIMARY KEY (`id`),
597 UNIQUE KEY `EntityLink-unique` (`parent_entity_type`,`parent_entity_id`,`child_entity_type`,`child_entity_id`)
598 ) ENGINE=InnoDB
599 ";
600 $query[] = "ALTER TABLE `TagStorage` CHANGE COLUMN `entity_realm` `entity_realm` ENUM('file','ipv4net','ipv4vs','ipv4rspool','object','rack','user','ipv6net') NOT NULL DEFAULT 'object' FIRST";
601 $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`";
602 $query[] = 'ALTER TABLE Link ADD COLUMN cable char(64) NULL AFTER portb';
603 $query[] = 'ALTER TABLE RackSpace ADD CONSTRAINT `RackSpace-FK-rack_id` FOREIGN KEY (rack_id) REFERENCES Rack (id)';
604 $query[] = "ALTER TABLE `IPv4Allocation` ADD CONSTRAINT `IPv4Allocation-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE";
605 $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')";
606 $query[] = "ALTER TABLE `VLANSwitchTemplate` ADD COLUMN `mutex_rev` int(10) NOT NULL AFTER `id`";
607 $query[] = "ALTER TABLE `VLANSwitchTemplate` ADD COLUMN `saved_by` char(64) NOT NULL AFTER `description`";
608 $query[] = "INSERT INTO `Attribute` (`id`, `type`, `name`) VALUES (26,'dict','Hypervisor')";
609 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (29,'no','Yes/No')";
610 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (30,'no','network chassis models')";
611 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (31,'no','server chassis models')";
612 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (32,'no','virtual switch models')";
613 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (33,'no','virtual switch OS type')";
614 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (4,26,29)";
615 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1502,1,NULL)";
616 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1502,2,31)";
617 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1502,3,NULL)";
618 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1502,14,NULL)";
619 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1502,20,NULL)";
620 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1502,21,NULL)";
621 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1502,22,NULL)";
622 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,1,NULL)";
623 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,2,30)";
624 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,3,NULL)";
625 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,4,14)";
626 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,5,NULL)";
627 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,14,NULL)";
628 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,16,NULL)";
629 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,17,NULL)";
630 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,18,NULL)";
631 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,20,NULL)";
632 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,21,NULL)";
633 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,22,NULL)";
634 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,24,NULL)";
635 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1504,3,NULL)";
636 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1504,4,13)";
637 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1504,14,NULL)";
638 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1504,24,NULL)";
639 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1505,14,NULL)";
640 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1506,14,NULL)";
641 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1506,17,NULL)";
642 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1506,18,NULL)";
643 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,1,NULL)";
644 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,2,32)";
645 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,3,NULL)";
646 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,4,33)";
647 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,5,NULL)";
648 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,14,NULL)";
649 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,20,NULL)";
650 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,21,NULL)";
651 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,22,NULL)";
652 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (3,13)";
653 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (4,1504)";
654 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (4,1507)";
655 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1502,4)";
656 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1503,8)";
657 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1505,4)";
658 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1505,1504)";
659 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1505,1506)";
660 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1505,1507)";
661 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1506,1504)";
662 $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')";
663 $query[] = "UPDATE Config SET varvalue = CONCAT(varvalue, ' or {\$typeid_1502} or {\$typeid_1503} or {\$typeid_1504} or {\$typeid_1507}') WHERE varname = 'IPV4OBJ_LISTSRC'";
664 $query[] = "UPDATE Config SET varvalue = '8' WHERE varname = 'MASSCOUNT'";
665 $query[] = "UPDATE RackObject SET label = NULL WHERE label = ''";
666 // Move barcode data so the column can be dropped
667 $result = $dbxlink->query ('SELECT id, objtype_id, barcode FROM RackObject WHERE barcode IS NOT NULL');
668 $rows = $result->fetchAll (PDO::FETCH_ASSOC);
669 unset ($result);
670 foreach ($rows as $row)
671 {
672 // Determine if this object type has the 'OEM S/N 1' attribute associated with it, and if it's set
673 $sn_query = "SELECT (SELECT COUNT(*) FROM AttributeMap WHERE objtype_id=${row['objtype_id']} AND attr_id=1) AS AM_count, ";
674 $sn_query .= "(SELECT COUNT(*) FROM AttributeValue WHERE object_id=${row['id']} AND attr_id=1) AS AV_count";
675 $sn_result = $dbxlink->query ($sn_query);
676 $sn_row = $sn_result->fetch (PDO::FETCH_ASSOC);
677 if ($sn_row['AM_count'] == 1 && $sn_row['AV_count'] == 0)
678 {
679 // 'OEM S/N 1' attribute is mapped to this object type, but it is not set. Good!
680 // Copy the barcode value to the attribute.
681 $query[] = "INSERT INTO AttributeValue (`object_id`, `attr_id`, `string_value`) VALUES (${row['id']}, 1, '${row['barcode']}')";
682 }
683 else
684 {
685 // Some other set of circumstances. Not as good!
686 // Copy the barcode value to a new ObjectLog record.
687 $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']}')";
688 }
689 unset ($sn_query, $sn_result, $sn_row);
690 }
691 $query[] = 'ALTER TABLE RackObject DROP COLUMN `barcode`';
692 $query[] = 'ALTER TABLE RackObjectHistory DROP COLUMN `barcode`';
693 $query[] = 'ALTER TABLE `VLANSwitchTemplate` DROP COLUMN `max_local_vlans`';
694 $query[] = "UPDATE Config SET varvalue = '0.19.0' WHERE varname = 'DB_VERSION'";
695 break;
696 case '0.19.1':
697 $query[] = "ALTER TABLE `Config` CHANGE COLUMN `varvalue` `varvalue` text NOT NULL";
698 $query[] = "ALTER TABLE `UserConfig` CHANGE COLUMN `varvalue` `varvalue` text NOT NULL";
699 $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')";
700 $result = $dbxlink->query ("SHOW TABLES LIKE 'Objectlog'");
701 $rows = $result->fetchAll (PDO::FETCH_ASSOC);
702 unset ($result);
703 if (count ($rows))
704 {
705 # Now the ObjectLog merge... again, because the original table is named
706 # "Objectlog". The job is to merge contents of Objectlog and ObjectLog
707 # into the latter.
708 $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)";
709 $query[] = "DELETE FROM Objectlog WHERE object_id IN(SELECT id FROM RackObject)";
710 # Don't delete the old table, if the merge wasn't exhaustive.
711 $result = $dbxlink->query ('SELECT COUNT(*) AS c FROM Objectlog WHERE object_id NOT IN(SELECT id FROM RackObject)');
712 $row = $result->fetch (PDO::FETCH_ASSOC);
713 unset ($result);
714 if ($row['c'] == 0)
715 $query[] = 'DROP TABLE Objectlog';
716 else
717 $query[] = 'ALTER TABLE Objectlog RENAME TO Objectlog_old_unmerged';
718 }
719 $query[] = "UPDATE Config SET varvalue = '0.19.1' WHERE varname = 'DB_VERSION'";
720 break;
721 case '0.19.2':
722 $query[] = "ALTER TABLE IPv4Allocation ADD KEY `ip` (`ip`)";
723 $query[] = "ALTER TABLE IPv6Allocation ADD KEY `ip` (`ip`)";
724 $query[] = "ALTER TABLE IPv4VS ADD KEY `vip` (`vip`)";
725 $query[] = "ALTER TABLE IPv4RS ADD KEY `rsip` (`rsip`)";
726 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (34,'no','power supply chassis models')";
727 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (35,'no','power supply models')";
728 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1397,1,NULL)";
729 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1397,2,34)";
730 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1397,14,NULL)";
731 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1397,21,NULL)";
732 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1397,22,NULL)";
733 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1398,1,NULL)";
734 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1398,2,35)";
735 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1398,14,NULL)";
736 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1398,21,NULL)";
737 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1398,22,NULL)";
738 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1397,1398)";
739 $query[] = "INSERT INTO `PortCompat` (`type1`, `type2`) VALUES (1399,1399)";
740 $query[] = "INSERT INTO `PortInterfaceCompat` (`iif_id`, `oif_id`) VALUES (1,1399)";
741 $query[] = "UPDATE Config SET varvalue = CONCAT(varvalue, ' or {\$typeid_1397}') WHERE varname = 'IPV4OBJ_LISTSRC'";
742 $query[] = "ALTER TABLE AttributeValue ADD KEY `attr_id-uint_value` (`attr_id`,`uint_value`)";
743 $query[] = "ALTER TABLE AttributeValue ADD KEY `attr_id-string_value` (`attr_id`,`string_value`(12))";
744 $query[] = "UPDATE Config SET varvalue = '0.19.2' WHERE varname = 'DB_VERSION'";
745 break;
746 case '0.19.3':
747 $query[] = "DELETE FROM RackSpace WHERE object_id IS NULL AND state = 'T'";
748 $query[] = "UPDATE Config SET varvalue = '0.19.3' WHERE varname = 'DB_VERSION'";
749 break;
750 case '0.19.4':
751 $query[] = "UPDATE Config SET varvalue = '0.19.4' WHERE varname = 'DB_VERSION'";
752 break;
753 case '0.19.5':
754 // Add 'virtual port' to 'virtual port' mapping
755 $query[] = "INSERT INTO `PortCompat` (`type1`,`type2`) VALUES (1469,1469)";
756 $query[] = "INSERT INTO `PortInterfaceCompat` (`iif_id`,`oif_id`) VALUES (1,1469)";
757 $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')";
758 $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')";
759 $query[] = "UPDATE Link SET cable = NULL WHERE cable = ''";
760 $query[] = "ALTER TABLE AttributeValue MODIFY string_value char(255) DEFAULT NULL";
761 $query[] = "UPDATE Config SET varvalue = '0.19.5' WHERE varname = 'DB_VERSION'";
762 break;
763 case '0.19.6':
764 $query[] = "UPDATE Config SET varvalue = '0.19.6' WHERE varname = 'DB_VERSION'";
765 break;
766 case '0.19.7':
767 # A plain "ALTER TABLE Attribute" can leave AUTO_INCREMENT in an odd
768 # state, hence the table swap.
769 $query[] = "
770 CREATE TABLE `Attribute_new` (
771 `id` int(10) unsigned NOT NULL auto_increment,
772 `type` enum('string','uint','float','dict') default NULL,
773 `name` char(64) default NULL,
774 PRIMARY KEY (`id`),
775 UNIQUE KEY `name` (`name`)
776 ) ENGINE=InnoDB
777 ";
778 $query[] = "INSERT INTO Attribute_new SELECT * FROM Attribute";
779 $query[] = "INSERT INTO Attribute_new VALUES (9999, 'string', 'base MAC address')";
780 $query[] = "DROP TABLE Attribute";
781 $query[] = "ALTER TABLE Attribute_new RENAME TO Attribute";
782 $query[] = "ALTER TABLE AttributeMap ADD KEY (attr_id)";
783 $query[] = "DELETE FROM AttributeMap WHERE attr_id NOT IN (SELECT id FROM Attribute)";
784 $query[] = "ALTER TABLE AttributeMap ADD CONSTRAINT `AttributeMap-FK-attr_id` FOREIGN KEY (attr_id) REFERENCES Attribute (id)";
785 $query[] = "DELETE FROM AttributeValue WHERE attr_id NOT IN (SELECT attr_id FROM AttributeMap)";
786 $query[] = "ALTER TABLE AttributeValue ADD CONSTRAINT `AttributeValue-FK-attr_id` FOREIGN KEY (attr_id) REFERENCES AttributeMap (attr_id)";
787 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1506,4)";
788 $query[] = "INSERT INTO PortInnerInterface (id, iif_name) VALUES (10, 'QSFP+')";
789 $query[] = "INSERT INTO PortInterfaceCompat VALUES (10, 1588)";
790 $query[] = "UPDATE Config SET varvalue = '0.19.7' WHERE varname = 'DB_VERSION'";
791 break;
792 case '0.19.8':
793 for ($i = 1424; $i <= 1466; $i++) # CX, then 42 ER channels
794 $query[] = "INSERT INTO `PortCompat` (`type1`, `type2`) VALUES (${i},${i})";
795 $query[] = "ALTER TABLE UserAccount ENGINE=InnoDB";
796 $query[] = "DELETE FROM UserConfig WHERE user NOT IN (SELECT user_name FROM UserAccount)";
797 $query[] = "ALTER TABLE UserConfig ADD CONSTRAINT `UserConfig-FK-user` FOREIGN KEY (user) REFERENCES UserAccount (user_name) ON DELETE CASCADE";
798 $query[] = "DELETE FROM UserConfig WHERE varname NOT IN (SELECT varname FROM Config)";
799 $query[] = "ALTER TABLE UserConfig ADD KEY (varname)";
800 $query[] = "ALTER TABLE UserConfig ADD CONSTRAINT `UserConfig-FK-varname` FOREIGN KEY (varname) REFERENCES Config (varname) ON DELETE CASCADE";
801 $query[] = "ALTER TABLE Dictionary ENGINE=InnoDB";
802 $query[] = "ALTER TABLE Chapter ENGINE=InnoDB";
803 $query[] = "UPDATE Chapter SET id = 9999 WHERE id = 22";
804 $query[] = "UPDATE AttributeMap SET chapter_id = 9999 WHERE chapter_id = 22";
805 $query[] = "UPDATE Dictionary SET chapter_id = 9999 WHERE chapter_id = 22";
806 $query[] = "DELETE FROM Dictionary WHERE chapter_id NOT IN (SELECT id FROM Chapter)";
807 $query[] = "ALTER TABLE Dictionary ADD CONSTRAINT `Dictionary-FK-chapter_id` FOREIGN KEY (chapter_id) REFERENCES Chapter (id)";
808 $query[] = "DELETE FROM AttributeMap WHERE chapter_id NOT IN (SELECT id FROM Chapter)";
809 $query[] = "ALTER TABLE AttributeMap ADD KEY (chapter_id)";
810 $query[] = "ALTER TABLE AttributeMap ADD CONSTRAINT `AttributeMap-FK-chapter_id` FOREIGN KEY (chapter_id) REFERENCES Chapter (id)";
811 $query[] = "
812 CREATE TABLE `CactiGraph` (
813 `object_id` int(10) unsigned NOT NULL,
814 `graph_id` int(10) unsigned NOT NULL,
815 `caption` char(255) DEFAULT NULL,
816 PRIMARY KEY (`graph_id`),
817 KEY `object_id` (`object_id`),
818 CONSTRAINT `CactiGraph-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE
819 ) ENGINE=InnoDB;
820 ";
821 $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')";
822 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('CACTI_URL','','string','yes','no','no','Cacti server base URL')";
823 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('CACTI_USERNAME','','string','yes','no','no','Cacti user account')";
824 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('CACTI_USERPASS','','string','yes','no','no','Cacti user password')";
825 $query[] = "UPDATE Config SET varvalue = '0.19.8' WHERE varname = 'DB_VERSION'";
826 break;
827 case '0.19.9':
828 $query[] = "DELETE FROM Config WHERE varname = 'HNDP_RUNNERS_LISTSRC'";
829 # Dismiss some overly-specific OIF types in favour of more generic counterparts.
830 $squeeze = array
831 (
832 1202 => array # 1000Base-SX
833 (
834 25, # 1000Base-SX (SC)
835 26, # 1000Base-SX (LC)
836 ),
837 1204 => array # 1000Base-LX
838 (
839 27, # 1000Base-LX (SC)
840 28, # 1000Base-LX (LC)
841 ),
842 1196 => array # 100Base-SX
843 (
844 22, # 100Base-SX (SC)
845 23, # 100Base-SX (LC)
846 ),
847 1195 => array # 100Base-FX
848 (
849 20, # 100Base-FX (SC)
850 21, # 100Base-FX (LC)
851 1083, # 100Base-FX (MT-RJ)
852 ),
853 );
854 foreach ($squeeze as $stays => $leaves)
855 {
856 $csv = implode (', ', $leaves);
857 $query[] = "DELETE FROM PortCompat WHERE type1 IN(${csv}) OR type2 IN(${csv})";
858 $query[] = "INSERT IGNORE INTO PortInterfaceCompat (iif_id, oif_id) SELECT iif_id, ${stays} FROM Port WHERE type IN (${csv})";
859 $query[] = "UPDATE Port SET type = ${stays} WHERE type IN(${csv})";
860 $query[] = "DELETE FROM PortInterfaceCompat WHERE oif_id IN(${csv})";
861 }
862 $query[] = "UPDATE Config SET varvalue = '0.19.9' WHERE varname = 'DB_VERSION'";
863 break;
864 case '0.19.10':
865 $query[] = "INSERT INTO `PortCompat` (`type1`, `type2`) VALUES (1603,1603)";
866 $query[] = "UPDATE Config SET varvalue = '0.19.10' WHERE varname = 'DB_VERSION'";
867 break;
868 case '0.19.11':
869 $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')";
870 $query[] = "INSERT INTO `Attribute` (`id`,`type`,`name`) VALUES (28,'string','Slot number')";
871 $query[] = 'INSERT INTO `AttributeMap` (`objtype_id`,`attr_id`,`chapter_id`) VALUES (4,28,NULL)';
872 $query[] = 'INSERT INTO `AttributeMap` (`objtype_id`,`attr_id`,`chapter_id`) VALUES (8,28,NULL)';
873 $query[] = 'INSERT INTO `AttributeMap` (`objtype_id`,`attr_id`,`chapter_id`) VALUES (798,28,NULL)';
874 $query[] = 'INSERT INTO `AttributeMap` (`objtype_id`,`attr_id`,`chapter_id`) VALUES (1055,28,NULL)';
875 $query[] = 'ALTER TABLE AttributeValue ADD COLUMN object_tid int(10) unsigned NOT NULL default 0 AFTER object_id';
876 $query[] = 'UPDATE AttributeValue SET object_tid = (SELECT objtype_id FROM RackObject WHERE id = object_id)';
877 $query[] = 'ALTER TABLE AttributeValue DROP FOREIGN KEY `AttributeValue-FK-attr_id`';
878 $query[] = 'ALTER TABLE AttributeValue DROP FOREIGN KEY `AttributeValue-FK-object_id`';
879 $query[] = 'ALTER TABLE AttributeValue ADD KEY `id-tid` (object_id, object_tid)';
880 $query[] = 'ALTER TABLE AttributeValue ADD KEY `object_tid-attr_id` (`object_tid`,`attr_id`)';
881 $query[] = 'ALTER TABLE RackObject ADD KEY `id-tid` (id, objtype_id)';
882 $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';
883 $query[] = 'ALTER TABLE AttributeValue ADD CONSTRAINT `AttributeValue-FK-map` FOREIGN KEY (`object_tid`, `attr_id`) REFERENCES `AttributeMap` (`objtype_id`, `attr_id`)';
884 # 0.19.9 did it right, but kept the IDs in the dictionary. This time
885 # the dictionary is reduced, but the procedure needs to be repeated,
886 # in case the user had enough time to use the wrong IDs again.
887 $squeeze = array
888 (
889 1202 => array # 1000Base-SX
890 (
891 25, # 1000Base-SX (SC)
892 26, # 1000Base-SX (LC)
893 ),
894 1204 => array # 1000Base-LX
895 (
896 27, # 1000Base-LX (SC)
897 28, # 1000Base-LX (LC)
898 ),
899 1196 => array # 100Base-SX
900 (
901 22, # 100Base-SX (SC)
902 23, # 100Base-SX (LC)
903 ),
904 1195 => array # 100Base-FX
905 (
906 20, # 100Base-FX (SC)
907 21, # 100Base-FX (LC)
908 1083, # 100Base-FX (MT-RJ)
909 ),
910 );
911 foreach ($squeeze as $stays => $leaves)
912 {
913 $csv = implode (', ', $leaves);
914 $query[] = "DELETE FROM PortCompat WHERE type1 IN(${csv}) OR type2 IN(${csv})";
915 $query[] = "INSERT IGNORE INTO PortInterfaceCompat (iif_id, oif_id) SELECT iif_id, ${stays} FROM Port WHERE type IN (${csv})";
916 $query[] = "UPDATE Port SET type = ${stays} WHERE type IN(${csv})";
917 $query[] = "DELETE FROM PortInterfaceCompat WHERE oif_id IN(${csv})";
918 }
919 $query[] = "INSERT INTO `PortCompat` (`type1`, `type2`) VALUES (1642,1642)";
920 $query[] = 'ALTER TABLE `EntityLink` ADD KEY `EntityLink-compound` (`parent_entity_type`,`child_entity_type`,`child_entity_id`)';
921 $query[] = "UPDATE Config SET varvalue = '0.19.11' WHERE varname = 'DB_VERSION'";
922 break;
923 # Batch 0.19.12 contained minor, but annoying bugs and was modified after the
924 # release of version 0.19.12 (which should be avoided if possible). The best
925 # way to resolve this particular case was to recall RackTables-0.19.12.tar.gz
926 # from the download area and provide RackTables-0.19.13.tar.gz containing the
927 # modified 0.19.12 batch.
928 case '0.19.12':
929 $query[] = "DELETE FROM Config WHERE varname IN('color_F', 'color_A', 'color_U', 'color_T', 'color_Th', 'color_Tw', 'color_Thw')";
930 $query[] = "INSERT INTO Chapter (id, sticky, name) VALUES (36,'no','serial console server models')";
931 $query[] = "INSERT INTO AttributeMap (objtype_id, attr_id, chapter_id) VALUES (1644, 1, NULL), (1644, 2, 36), (1644, 3, NULL)";
932 $query[] = "UPDATE Config SET varvalue = CONCAT(varvalue, '; 10=1588') WHERE varname = 'DEFAULT_PORT_OIF_IDS' AND 0 = INSTR(varvalue, '10=') ";
933 $query[] = "INSERT INTO PortInterfaceCompat VALUES (10,1663), (10,1664)";
934 $query[] = "INSERT INTO PortCompat VALUES (1588,1588), (1661,1661), (1663,1663), (1664,1664)";
935 $query[] = "INSERT INTO PortInnerInterface (id, iif_name) VALUES (11, 'CFP')";
936 $query[] = "INSERT INTO PortInterfaceCompat (iif_id, oif_id) VALUES (11,1668),(11,1669),(11,1670),(11,1671)";
937 $query[] = "INSERT INTO PortCompat (type1, type2) VALUES (1668,1668), (1669,1669), (1670,1670), (1671,1671)";
938 $query[] = "UPDATE Config SET varvalue = CONCAT(varvalue, '; 11=1668') WHERE varname = 'DEFAULT_PORT_OIF_IDS'";
939 $query[] = "INSERT INTO Chapter (id, sticky, name) VALUES (37, 'no', 'wireless OS type')";
940 $query[] = "INSERT INTO AttributeMap (objtype_id, attr_id, chapter_id) VALUES (965, 4, 37)";
941 $query[] = "UPDATE Config SET varvalue = '0.19.12' WHERE varname = 'DB_VERSION'";
942 break;
943 case '0.19.13':
944 // add the date attribute type
945 $query[] = "ALTER TABLE `Attribute` CHANGE COLUMN `type` `type` enum('string','uint','float','dict','date') DEFAULT NULL";
946 $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')";
947 $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')";
948
949 // port over existing fields to new date attr type
950 $query[] = "UPDATE Attribute SET type='date' WHERE id IN (21,22,24)";
951 $query[] = "UPDATE AttributeValue SET uint_value=UNIX_TIMESTAMP(STR_TO_DATE(string_value, '%m/%d/%Y')) WHERE attr_id IN(21,22,24)";
952
953 // some config variables should be configurable in per-user basis
954 $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')";
955
956 $query[] = "UPDATE Config SET varvalue = '0.19.13' WHERE varname = 'DB_VERSION'";
957 break;
958 case '0.19.14':
959 $query[] = "ALTER TABLE AttributeValue CHANGE COLUMN `object_id` `object_id` INT(10) UNSIGNED NOT NULL";
960 $query[] = "ALTER TABLE AttributeValue CHANGE COLUMN `attr_id` `attr_id` INT(10) UNSIGNED NOT NULL";
961 $query[] = "ALTER TABLE AttributeValue ADD PRIMARY KEY (`object_id`, `attr_id`), DROP INDEX `object_id`";
962 $query[] = "ALTER TABLE Dictionary ADD COLUMN `dict_sticky` enum('yes','no') DEFAULT 'no' AFTER `dict_key`";
963 $query[] = "UPDATE Dictionary SET dict_sticky = 'yes' WHERE dict_key < 50000";
964 $query[] = "ALTER TABLE Dictionary ADD UNIQUE KEY dict_unique (chapter_id, dict_value, dict_sticky)";
965 $query[] = "ALTER TABLE Dictionary DROP KEY `chap_to_val`";
966 $query[] = "UPDATE Config SET varvalue = '0.19.14' WHERE varname = 'DB_VERSION'";
967 break;
968 case '0.20.0':
969 $query[] = "
970 CREATE TABLE `PortLog` (
971 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
972 `port_id` int(10) unsigned NOT NULL,
973 `date` datetime NOT NULL,
974 `user` varchar(64) NOT NULL,
975 `message` text NOT NULL,
976 PRIMARY KEY (`id`),
977 KEY `port_id-date` (`port_id`,`date`),
978 CONSTRAINT `PortLog_ibfk_1` FOREIGN KEY (`port_id`) REFERENCES `Port` (`id`) ON DELETE CASCADE
979 ) ENGINE=InnoDB;
980 ";
981 $query[] = "
982 CREATE TABLE `IPv4Log` (
983 `id` int(10) NOT NULL AUTO_INCREMENT,
984 `ip` int(10) unsigned NOT NULL,
985 `date` datetime NOT NULL,
986 `user` varchar(64) NOT NULL,
987 `message` text NOT NULL,
988 PRIMARY KEY (`id`),
989 KEY `ip-date` (`ip`,`date`)
990 ) ENGINE=InnoDB;
991 ";
992 $query[] = "
993 CREATE TABLE `IPv6Log` (
994 `id` int(10) NOT NULL AUTO_INCREMENT,
995 `ip` binary(16) NOT NULL,
996 `date` datetime NOT NULL,
997 `user` varchar(64) NOT NULL,
998 `message` text NOT NULL,
999 PRIMARY KEY (`id`),
1000 KEY `ip-date` (`ip`,`date`)
1001 ) ENGINE=InnoDB;
1002 ";
1003 $query[] = "ALTER TABLE `FileLink` MODIFY COLUMN `entity_type` ENUM('ipv4net','ipv4rspool','ipv4vs','ipv6net','location','object','rack','user') NOT NULL DEFAULT 'object'";
1004 $query[] = "ALTER TABLE `TagStorage` MODIFY COLUMN `entity_realm` ENUM('file','ipv4net','ipv4rspool','ipv4vs','ipv6net','location','object','rack','user','vst') NOT NULL default 'object'";
1005 $query[] = "ALTER TABLE `TagStorage` ADD COLUMN `user` char(64) DEFAULT NULL, ADD COLUMN `date` datetime DEFAULT NULL";
1006
1007 // Rename object tables and keys, 'name' no longer needs to be unique
1008 $dbxlink->query ('ALTER TABLE `RackObject` RENAME TO `Object`');
1009 $dbxlink->query ('ALTER TABLE `RackObjectHistory` RENAME TO `ObjectHistory`');
1010 $dbxlink->query ('ALTER TABLE `Object` DROP KEY `name`');
1011 $query[] = 'ALTER TABLE `Object` DROP KEY `RackObject_asset_no`';
1012 $query[] = 'ALTER TABLE `Object` ADD UNIQUE KEY `asset_no` (`asset_no`)';
1013 $query[] = 'ALTER TABLE `Object` ADD KEY `type_id` (`objtype_id`,`id`)';
1014 $query[] = 'ALTER TABLE `ObjectHistory` DROP FOREIGN KEY `RackObjectHistory-FK-object_id`';
1015 $query[] = 'ALTER TABLE `ObjectHistory` ADD CONSTRAINT `ObjectHistory-FK-object_id` FOREIGN KEY (`id`) REFERENCES `Object` (`id`) ON DELETE CASCADE';
1016 $query[] = 'ALTER TABLE `RackSpace` DROP FOREIGN KEY `RackSpace-FK-rack_id`';
1017
1018 // Rack height is now an attribute
1019 $query[] = "INSERT INTO `Attribute` (`id`,`type`,`name`) VALUES (27,'uint','Height, units')";
1020 $query[] = 'INSERT INTO `AttributeMap` (`objtype_id`,`attr_id`,`chapter_id`) VALUES (1560,27,NULL)';
1021
1022 // Racks are now sorted using an attribute
1023 $query[] = "INSERT INTO `Attribute` (`id`,`type`,`name`) VALUES (29,'uint','Sort order')";
1024 $query[] = 'INSERT INTO `AttributeMap` (`objtype_id`,`attr_id`,`chapter_id`) VALUES (1560,29,NULL)';
1025
1026 // Relate 'contact person' with locations
1027 $query[] = 'INSERT INTO `AttributeMap` (`objtype_id`,`attr_id`,`chapter_id`) VALUES (1562,14,NULL)';
1028
1029 // Allow relationships between racks/rows/locations
1030 $query[] = "ALTER TABLE `EntityLink` MODIFY COLUMN `parent_entity_type` ENUM('ipv4net','ipv4rspool','ipv4vs','ipv6net','location','object','rack','row','user') NOT NULL";
1031 $query[] = "ALTER TABLE `EntityLink` MODIFY COLUMN `child_entity_type` ENUM('file','location','object','rack','row') NOT NULL";
1032
1033 // Turn rows into objects
1034 $result = $dbxlink->query ('SELECT * FROM RackRow');
1035 $rows = $result->fetchAll (PDO::FETCH_ASSOC);
1036 unset ($result);
1037 foreach ($rows as $row)
1038 {
1039 $prepared = $dbxlink->prepare ('INSERT INTO `Object` (`name`,`objtype_id`) VALUES (?,?)');
1040 $prepared->execute (array($row['name'], 1561));
1041 $row_id = $dbxlink->lastInsertId();
1042 // Turn all racks in this row into objects
1043 $result = $dbxlink->query ("SELECT id, name, height, comment FROM Rack WHERE row_id=${row['id']} ORDER BY name");
1044 $racks = $result->fetchAll (PDO::FETCH_ASSOC);
1045 unset ($result);
1046 $sort_order = 1;
1047 foreach ($racks as $rack)
1048 {
1049 // Add the rack as an object, set the height and sort order as attributes, link the rack to the row,
1050 // update rackspace, tags and files to reflect new rack_id, move history
1051 $prepared = $dbxlink->prepare ('INSERT INTO `Object` (`name`,`objtype_id`,`comment`) VALUES (?,?,?)');
1052 $prepared->execute (array($rack['name'], 1560, $rack['comment']));
1053 $rack_id = $dbxlink->lastInsertId();
1054 $query[] = "INSERT INTO `AttributeValue` (`object_id`,`object_tid`,`attr_id`,`uint_value`) VALUES (${rack_id},1560,27,${rack['height']})";
1055 $query[] = "INSERT INTO `AttributeValue` (`object_id`,`object_tid`,`attr_id`,`uint_value`) VALUES (${rack_id},1560,29,${sort_order})";
1056 $query[] = "INSERT INTO `EntityLink` (`parent_entity_type`,`parent_entity_id`,`child_entity_type`,`child_entity_id`) VALUES ('row',${row_id},'rack',${rack_id})";
1057 $query[] = "UPDATE `RackSpace` SET `rack_id`=${rack_id} WHERE `rack_id`=${rack['id']}";
1058 $query[] = "UPDATE `Atom` SET `rack_id`=${rack_id} WHERE `rack_id`=${rack['id']}";
1059 $query[] = "UPDATE `TagStorage` SET `entity_id`=${rack_id} WHERE `entity_realm`='rack' AND `entity_id`=${rack['id']}";
1060 $query[] = "UPDATE `FileLink` SET `entity_id`=${rack_id} WHERE `entity_type`='rack' AND `entity_id`=${rack['id']}";
1061 $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']}";
1062 $sort_order++;
1063 }
1064 }
1065 $query[] = 'ALTER TABLE `RackSpace` ADD CONSTRAINT `RackSpace-FK-rack_id` FOREIGN KEY (`rack_id`) REFERENCES `Object` (`id`)';
1066 $query[] = 'DROP TABLE `Rack`';
1067 $query[] = 'DROP TABLE `RackRow`';
1068 $query[] = 'DROP TABLE `RackHistory`';
1069 $query[] = "
1070 CREATE TABLE `RackThumbnail` (
1071 `rack_id` int(10) unsigned NOT NULL,
1072 `thumb_data` blob,
1073 UNIQUE KEY `rack_id` (`rack_id`),
1074 CONSTRAINT `RackThumbnail-FK-rack_id` FOREIGN KEY (`rack_id`) REFERENCES `Object` (`id`) ON DELETE CASCADE
1075 ) ENGINE=InnoDB
1076 ";
1077 $query[] = "
1078 CREATE VIEW `Location` AS SELECT O.id, O.name, O.has_problems, O.comment, P.id AS parent_id, P.name AS parent_name
1079 FROM `Object` O
1080 LEFT JOIN (
1081 `Object` P INNER JOIN `EntityLink` EL
1082 ON EL.parent_entity_id = P.id AND P.objtype_id = 1562 AND EL.parent_entity_type = 'location' AND EL.child_entity_type = 'location'
1083 ) ON EL.child_entity_id = O.id
1084 WHERE O.objtype_id = 1562
1085 ";
1086 $query[] = "
1087 CREATE VIEW `Row` AS SELECT O.id, O.name, L.id AS location_id, L.name AS location_name
1088 FROM `Object` O
1089 LEFT JOIN `EntityLink` EL ON O.id = EL.child_entity_id AND EL.parent_entity_type = 'location' AND EL.child_entity_type = 'row'
1090 LEFT JOIN `Object` L ON EL.parent_entity_id = L.id AND L.objtype_id = 1562
1091 WHERE O.objtype_id = 1561
1092 ";
1093 $query[] = "
1094 CREATE VIEW `Rack` AS SELECT O.id, O.name AS name, O.asset_no, O.has_problems, O.comment,
1095 AV_H.uint_value AS height,
1096 AV_S.uint_value AS sort_order,
1097 RT.thumb_data,
1098 R.id AS row_id,
1099 R.name AS row_name
1100 FROM `Object` O
1101 LEFT JOIN `AttributeValue` AV_H ON O.id = AV_H.object_id AND AV_H.attr_id = 27
1102 LEFT JOIN `AttributeValue` AV_S ON O.id = AV_S.object_id AND AV_S.attr_id = 29
1103 LEFT JOIN `RackThumbnail` RT ON O.id = RT.rack_id
1104 LEFT JOIN `EntityLink` EL ON O.id = EL.child_entity_id AND EL.parent_entity_type = 'row' AND EL.child_entity_type = 'rack'
1105 INNER JOIN `Object` R ON R.id = EL.parent_entity_id
1106 WHERE O.objtype_id = 1560
1107 ";
1108 $query[] = "
1109 CREATE VIEW `RackObject` AS SELECT id, name, label, objtype_id, asset_no, has_problems, comment FROM `Object`
1110 WHERE `objtype_id` NOT IN (1560, 1561, 1562)
1111 ";
1112 $query[] = "UPDATE `Chapter` SET `name` = 'ObjectType' WHERE `id` = 1";
1113 $query[] = "DELETE FROM RackSpace WHERE object_id IS NULL AND state = 'T'";
1114
1115 $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')";
1116 $query[] = "UPDATE `Config` SET is_userdefined='yes' WHERE varname='PROXIMITY_RANGE'";
1117 $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')";
1118 $query[] = "ALTER TABLE `IPv4LB` MODIFY `prio` varchar(255) DEFAULT NULL";
1119
1120 $query[] = "ALTER TABLE `IPv4Address` ADD COLUMN `comment` char(255) NOT NULL default '' AFTER `name`";
1121 $query[] = "ALTER TABLE `IPv6Address` ADD COLUMN `comment` char(255) NOT NULL default '' AFTER `name`";
1122
1123 // change IP address format of IPv4VS and IPv4RS tables
1124 convertSLBTablesToBinIPs();
1125
1126 // do not allow NULL allocation type
1127 $query[] = "ALTER TABLE `IPv4Allocation` MODIFY `type` enum('regular','shared','virtual','router') NOT NULL DEFAULT 'regular'";
1128 $query[] = "ALTER TABLE `IPv6Allocation` MODIFY `type` enum('regular','shared','virtual','router') NOT NULL DEFAULT 'regular'";
1129
1130 $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')";
1131
1132 // update some config variables which changed their defaults in this verison
1133 replaceConfigVarValue ('SHOW_LAST_TAB', 'yes');
1134 replaceConfigVarValue ('IPV4_TREE_SHOW_USAGE','no');
1135 replaceConfigVarValue ('IPV4LB_LISTSRC', 'false', '{$typeid_4}');
1136 replaceConfigVarValue ('FILTER_DEFAULT_ANDOR', 'and');
1137 replaceConfigVarValue ('FILTER_SUGGEST_EXTRA', 'yes');
1138 replaceConfigVarValue ('IPV4_TREE_RTR_AS_CELL', 'no');
1139 replaceConfigVarValue ('SSH_OBJS_LISTSRC', 'false', 'none');
1140 replaceConfigVarValue ('TELNET_OBJS_LISTSRC', 'false', 'none');
1141
1142 $query[] = "UPDATE Config SET varvalue = '0.20.0' WHERE varname = 'DB_VERSION'";
1143 break;
1144 case '0.20.1':
1145 // some HW types were moved from the 'Network switch' chapter to the 'Network chassis' chapter
1146 // change the type of affected objects to 'Network chassis'
1147 $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))";
1148
1149 // new 'management interface' object type
1150 $query[] = "INSERT INTO `Chapter` (`id`,`sticky`,`name`) VALUES (38,'no','management interface type')";
1151 $query[] = "INSERT INTO `Attribute` (`id`,`type`,`name`) VALUES (30,'dict','Mgmt type')";
1152 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`,`attr_id`,`chapter_id`) VALUES (1787,3,NULL),(1787,14,NULL),(1787,30,38)";
1153 $query[] = "UPDATE `Config` SET varvalue = CONCAT(varvalue, ' or {\$typeid_1787}') WHERE varname = 'IPV4OBJ_LISTSRC'";
1154
1155 $query[] = "INSERT INTO Config VALUES ('8021Q_EXTSYNC_LISTSRC','false','string','yes','no','no','List source: objects with extended 802.1Q sync')";
1156
1157 // constraints to prevent orphan records
1158 $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";
1159 $query[] = "DELETE FROM `MountOperation` WHERE old_molecule_id NOT IN (SELECT id FROM `Molecule`) OR new_molecule_id NOT IN (SELECT id FROM `Molecule`)";
1160 $query[] = "ALTER TABLE `Atom` ADD CONSTRAINT `Atom-FK-molecule_id` FOREIGN KEY (`molecule_id`) REFERENCES `Molecule` (`id`) ON DELETE CASCADE";
1161 $query[] = "ALTER TABLE `Atom` ADD CONSTRAINT `Atom-FK-rack_id` FOREIGN KEY (`rack_id`) REFERENCES `Object` (`id`) ON DELETE CASCADE";
1162 $query[] = "ALTER TABLE `MountOperation` ADD CONSTRAINT `MountOperation-FK-old_molecule_id` FOREIGN KEY (`old_molecule_id`) REFERENCES `Molecule` (`id`) ON DELETE CASCADE";
1163 $query[] = "ALTER TABLE `MountOperation` ADD CONSTRAINT `MountOperation-FK-new_molecule_id` FOREIGN KEY (`new_molecule_id`) REFERENCES `Molecule` (`id`) ON DELETE CASCADE";
1164 # multiple Cacti servers
1165 $query[] = "
1166 CREATE TABLE `CactiServer` (
1167 `id` int(10) unsigned NOT NULL auto_increment,
1168 `base_url` char(255) DEFAULT NULL,
1169 `username` char(64) DEFAULT NULL,
1170 `password` char(64) DEFAULT NULL,
1171 PRIMARY KEY (`id`)
1172 ) ENGINE=InnoDB
1173 ";
1174 $query[] = "ALTER TABLE CactiGraph ADD COLUMN server_id int(10) unsigned NOT NULL AFTER object_id";
1175
1176 $result = $dbxlink->query ('SELECT COUNT(*) AS cnt FROM CactiGraph');
1177 $row = $result->fetch (PDO::FETCH_ASSOC);
1178 unset ($result);
1179
1180 $result = $dbxlink->query ("SELECT varvalue FROM Config WHERE varname = 'CACTI_URL'");
1181 $cacti_url_row = $result->fetch (PDO::FETCH_ASSOC);
1182 unset ($result);
1183
1184 if ($row['cnt'] != 0 || is_array ($cacti_url_row) && strlen ($cacti_url_row['varvalue']))
1185 {
1186 $query[] = "INSERT INTO CactiServer (id) VALUES (1)";
1187 $query[] = "UPDATE CactiServer SET base_url = (SELECT varvalue FROM Config WHERE varname = 'CACTI_URL') WHERE id = 1";
1188 $query[] = "UPDATE CactiServer SET username = (SELECT varvalue FROM Config WHERE varname = 'CACTI_USERNAME') WHERE id = 1";
1189 $query[] = "UPDATE CactiServer SET password = (SELECT varvalue FROM Config WHERE varname = 'CACTI_USERPASS') WHERE id = 1";
1190 $query[] = "UPDATE CactiGraph SET server_id = 1";
1191 }
1192 $query[] = "ALTER TABLE CactiGraph DROP PRIMARY KEY";
1193 $query[] = "ALTER TABLE CactiGraph ADD PRIMARY KEY (server_id, graph_id)";
1194 $query[] = "ALTER TABLE CactiGraph ADD KEY (graph_id)";
1195 $query[] = "ALTER TABLE CactiGraph ADD CONSTRAINT `CactiGraph-FK-server_id` FOREIGN KEY (server_id) REFERENCES CactiServer (id)";
1196 $query[] = "DELETE FROM Config WHERE varname IN('CACTI_URL', 'CACTI_USERNAME', 'CACTI_USERPASS')";
1197 $query[] = "UPDATE Config SET varvalue = '0.20.1' WHERE varname = 'DB_VERSION'";
1198 break;
1199 case '0.20.2':
1200 $query[] = "ALTER TABLE TagStorage ADD COLUMN tag_is_assignable ENUM('yes', 'no') NOT NULL default 'yes' AFTER tag_id";
1201 $query[] = "ALTER TABLE TagStorage ADD KEY `tag_id-tag_is_assignable` (tag_id, tag_is_assignable)";
1202 $query[] = "ALTER TABLE TagTree ADD COLUMN is_assignable ENUM('yes', 'no') NOT NULL default 'yes' AFTER parent_id";
1203 $query[] = "ALTER TABLE TagTree ADD KEY `id-is_assignable` (id, is_assignable)";
1204 $query[] = "ALTER TABLE TagStorage DROP FOREIGN KEY `TagStorage-FK-tag_id`";
1205 $query[] = "ALTER TABLE TagStorage ADD CONSTRAINT `TagStorage-FK-TagTree` FOREIGN KEY (tag_id, tag_is_assignable) REFERENCES TagTree (id, is_assignable)";
1206 $query[] = "UPDATE UserAccount SET user_realname = NULL WHERE user_realname = ''";
1207 $query[] = "UPDATE Object SET comment = NULL WHERE comment = ''";
1208 $query[] = "
1209 CREATE TABLE `MuninServer` (
1210 `id` int(10) unsigned NOT NULL auto_increment,
1211 `base_url` char(255) DEFAULT NULL,
1212 PRIMARY KEY (`id`)
1213 ) ENGINE=InnoDB
1214 ";
1215 $query[] = "
1216 CREATE TABLE `MuninGraph` (
1217 `object_id` int(10) unsigned NOT NULL,
1218 `server_id` int(10) unsigned NOT NULL,
1219 `graph` char(255) NOT NULL,
1220 `caption` char(255) DEFAULT NULL,
1221 PRIMARY KEY (`object_id`,`server_id`,`graph`),
1222 KEY `server_id` (`server_id`),
1223 KEY `graph` (`graph`),
1224 CONSTRAINT `MuninGraph-FK-server_id` FOREIGN KEY (`server_id`) REFERENCES `MuninServer` (`id`),
1225 CONSTRAINT `MuninGraph-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `Object` (`id`) ON DELETE CASCADE
1226 ) ENGINE=InnoDB
1227 ";
1228 $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')";
1229 $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')";
1230 $query[] = "ALTER TABLE VLANIPv4 ADD UNIQUE `network-domain-vlan` (ipv4net_id, domain_id, vlan_id)";
1231 $query[] = "ALTER TABLE VLANIPv4 DROP KEY `network-domain`";
1232 $query[] = "ALTER TABLE VLANIPv6 ADD UNIQUE `network-domain-vlan` (ipv6net_id, domain_id, vlan_id)";
1233 $query[] = "ALTER TABLE VLANIPv6 DROP KEY `network-domain`";
1234 $query[] = "UPDATE Config SET varvalue = '0.20.2' WHERE varname = 'DB_VERSION'";
1235 break;
1236 case '0.20.3':
1237 $query[] = "UPDATE Config SET varvalue = '0.20.3' WHERE varname = 'DB_VERSION'";
1238 break;
1239 case '0.20.4':
1240 $query[] = "ALTER TABLE `FileLink` MODIFY COLUMN `entity_type` ENUM('ipv4net','ipv4rspool','ipv4vs','ipv6net','location','object','rack','row','user') NOT NULL DEFAULT 'object'";
1241 $query[] = "ALTER TABLE `RackSpace` MODIFY COLUMN `state` ENUM('A','U','T') NOT NULL default 'A'";
1242 $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')";
1243 $query[] = "UPDATE `Config` SET varvalue = CONCAT(varvalue, ' or {\$typeid_965}') WHERE varname = 'IPV4OBJ_LISTSRC'";
1244 $query[] = "UPDATE AttributeValue INNER JOIN AttributeMap USING (attr_id) SET AttributeValue.uint_value = 1572 WHERE chapter_id = 12 AND uint_value = 162";
1245 $query[] = "UPDATE AttributeValue INNER JOIN AttributeMap USING (attr_id) SET AttributeValue.uint_value = 1710 WHERE chapter_id = 12 AND uint_value = 163";
1246 $query[] = "UPDATE Config SET varvalue = '%Y-%m-%d', description='PHP strftime() format to use for date output' WHERE varname = 'DATETIME_FORMAT'";
1247 $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')";
1248 $query[] = "UPDATE Config SET varvalue = '0.20.4' WHERE varname = 'DB_VERSION'";
1249 break;
1250 case '0.20.5':
1251 $query[] = "
1252 CREATE OR REPLACE VIEW `Rack` AS SELECT O.id, O.name AS name, O.asset_no, O.has_problems, O.comment,
1253 AV_H.uint_value AS height,
1254 AV_S.uint_value AS sort_order,
1255 RT.thumb_data,
1256 R.id AS row_id,
1257 R.name AS row_name,
1258 L.id AS location_id,
1259 L.name AS location_name
1260 FROM `Object` O
1261 LEFT JOIN `AttributeValue` AV_H ON O.id = AV_H.object_id AND AV_H.attr_id = 27
1262 LEFT JOIN `AttributeValue` AV_S ON O.id = AV_S.object_id AND AV_S.attr_id = 29
1263 LEFT JOIN `RackThumbnail` RT ON O.id = RT.rack_id
1264 LEFT JOIN `EntityLink` RL ON O.id = RL.child_entity_id AND RL.parent_entity_type = 'row' AND RL.child_entity_type = 'rack'
1265 INNER JOIN `Object` R ON R.id = RL.parent_entity_id
1266 LEFT JOIN `EntityLink` LL ON R.id = LL.child_entity_id AND LL.parent_entity_type = 'location' AND LL.child_entity_type = 'row'
1267 LEFT JOIN `Object` L ON L.id = LL.parent_entity_id
1268 WHERE O.objtype_id = 1560
1269 ";
1270
1271 // prevent some AttributeMap entries from being deleted
1272 $query[] = "ALTER TABLE AttributeMap ADD COLUMN sticky enum('yes','no') default 'no'";
1273 $query[] = "UPDATE AttributeMap SET sticky = 'yes' WHERE objtype_id = 4 AND attr_id IN (26,28)"; // Server -> Hypervisor, Slot number
1274 $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
1275 $query[] = "UPDATE AttributeMap SET sticky = 'yes' WHERE objtype_id = 798 AND attr_id = 28"; // Network security -> Slot number
1276 $query[] = "UPDATE AttributeMap SET sticky = 'yes' WHERE objtype_id = 1055 AND attr_id = 28"; // FC switch -> Slot number
1277 $query[] = "UPDATE AttributeMap SET sticky = 'yes' WHERE objtype_id = 1560 AND attr_id IN (27,29)"; // Rack -> Height, Sort order
1278 $query[] = "UPDATE AttributeMap SET sticky = 'yes' WHERE objtype_id = 1787 AND attr_id = 30"; // Management interface -> Mgmt type
1279
1280 $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')";
1281
1282 // SLB v2 tables
1283 $query[] = "
1284 CREATE TABLE `VS` (
1285 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
1286 `name` char(255) DEFAULT NULL,
1287 `vsconfig` text,
1288 `rsconfig` text,
1289 PRIMARY KEY (`id`)
1290 ) ENGINE=InnoDB
1291 ";
1292 $query[] = "
1293 CREATE TABLE `VSIPs` (
1294 `vs_id` int(10) unsigned NOT NULL,
1295 `vip` varbinary(16) NOT NULL,
1296 `vsconfig` text,
1297 `rsconfig` text,
1298 PRIMARY KEY (`vs_id`,`vip`),
1299 KEY `vip` (`vip`),
1300 CONSTRAINT `VSIPs-vs_id` FOREIGN KEY (`vs_id`) REFERENCES `VS` (`id`) ON DELETE CASCADE
1301 ) ENGINE=InnoDB
1302 ";
1303 $query[] = "
1304 CREATE TABLE `VSPorts` (
1305 `vs_id` int(10) unsigned NOT NULL,
1306 `proto` enum('TCP','UDP','MARK') NOT NULL,
1307 `vport` int(10) unsigned NOT NULL,
1308 `vsconfig` text,
1309 `rsconfig` text,
1310 PRIMARY KEY (`vs_id`,`proto`,`vport`),
1311 KEY `proto-vport` (`proto`,`vport`),
1312 CONSTRAINT `VS-vs_id` FOREIGN KEY (`vs_id`) REFERENCES `VS` (`id`) ON DELETE CASCADE
1313 ) ENGINE=InnoDB
1314 ";
1315 $query[] = "
1316 CREATE TABLE `VSEnabledIPs` (
1317 `object_id` int(10) unsigned NOT NULL,
1318 `vs_id` int(10) unsigned NOT NULL,
1319 `vip` varbinary(16) NOT NULL,
1320 `rspool_id` int(10) unsigned NOT NULL,
1321 `prio` varchar(255) DEFAULT NULL,
1322 `vsconfig` text,
1323 `rsconfig` text,
1324 PRIMARY KEY (`object_id`,`vs_id`,`vip`,`rspool_id`),
1325 KEY `vip` (`vip`),
1326 KEY `VSEnabledIPs-FK-vs_id-vip` (`vs_id`,`vip`),
1327 KEY `VSEnabledIPs-FK-rspool_id` (`rspool_id`),
1328 CONSTRAINT `VSEnabledIPs-FK-rspool_id` FOREIGN KEY (`rspool_id`) REFERENCES `IPv4RSPool` (`id`) ON DELETE CASCADE,
1329 CONSTRAINT `VSEnabledIPs-FK-vs_id-vip` FOREIGN KEY (`vs_id`, `vip`) REFERENCES `VSIPs` (`vs_id`, `vip`) ON DELETE CASCADE
1330 ) ENGINE=InnoDB
1331 ";
1332 $query[] = "
1333 CREATE TABLE `VSEnabledPorts` (
1334 `object_id` int(10) unsigned NOT NULL,
1335 `vs_id` int(10) unsigned NOT NULL,
1336 `proto` enum('TCP','UDP','MARK') NOT NULL,
1337 `vport` int(10) unsigned NOT NULL,
1338 `rspool_id` int(10) unsigned NOT NULL,
1339 `vsconfig` text,
1340 `rsconfig` text,
1341 PRIMARY KEY (`object_id`,`vs_id`,`proto`,`vport`,`rspool_id`),
1342 KEY `VSEnabledPorts-FK-vs_id-proto-vport` (`vs_id`,`proto`,`vport`),
1343 KEY `VSEnabledPorts-FK-rspool_id` (`rspool_id`),
1344 CONSTRAINT `VSEnabledPorts-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `Object` (`id`) ON DELETE CASCADE,
1345 CONSTRAINT `VSEnabledPorts-FK-rspool_id` FOREIGN KEY (`rspool_id`) REFERENCES `IPv4RSPool` (`id`) ON DELETE CASCADE,
1346 CONSTRAINT `VSEnabledPorts-FK-vs_id-proto-vport` FOREIGN KEY (`vs_id`, `proto`, `vport`) REFERENCES `VSPorts` (`vs_id`, `proto`, `vport`) ON DELETE CASCADE
1347 ) ENGINE=InnoDB
1348 ";
1349 $query[] = "ALTER TABLE `EntityLink` MODIFY COLUMN `parent_entity_type` ENUM('ipv4net','ipv4rspool','ipv4vs','ipv6net','location','object','rack','row','user') NOT NULL";
1350 $query[] = "ALTER TABLE `FileLink` MODIFY COLUMN `entity_type` ENUM('ipv4net','ipv4rspool','ipv4vs','ipv6net','location','object','rack','row','user') NOT NULL DEFAULT 'object'";
1351 $query[] = "ALTER TABLE `TagStorage` MODIFY COLUMN `entity_realm` ENUM('file','ipv4net','ipv4rspool','ipv4vs','ipvs','ipv6net','location','object','rack','user','vst') NOT NULL DEFAULT 'object'";
1352 $query[] = "ALTER TABLE `UserConfig` DROP FOREIGN KEY `UserConfig-FK-user`";
1353 $query[] = "UPDATE Config SET varvalue = '0.20.5' WHERE varname = 'DB_VERSION'";
1354 break;
1355 case '0.20.6':
1356 if (!isInnoDBSupported ())
1357 {
1358 showUpgradeError ("Cannot upgrade because triggers are not supported by your MySQL server.", __FUNCTION__);
1359 die;
1360 }
1361 // allow one-to-many port links
1362 $query[] = "ALTER TABLE `Link` DROP FOREIGN KEY `Link-FK-a`, DROP FOREIGN KEY `Link-FK-b`";
1363 $query[] = "ALTER TABLE `Link` DROP PRIMARY KEY, DROP KEY `porta`, DROP KEY `portb`";
1364 $query[] = "ALTER TABLE `Link` ADD UNIQUE KEY `porta-portb-unique` (`porta`,`portb`), ADD KEY `porta` (`porta`), ADD KEY `portb` (`portb`)";
1365 $query[] = "ALTER TABLE `Link` ADD CONSTRAINT `Link-FK-a` FOREIGN KEY (`porta`) REFERENCES `Port` (`id`) ON DELETE CASCADE, ADD CONSTRAINT `Link-FK-b` FOREIGN KEY (`portb`) REFERENCES `Port` (`id`) ON DELETE CASCADE";
1366 $query[] = "ALTER TABLE `Link` ADD COLUMN `id` int(10) unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT FIRST";
1367 $query[] = "
1368 CREATE TRIGGER `checkLinkBeforeInsert` BEFORE INSERT ON `Link`
1369 FOR EACH ROW
1370 BEGIN
1371 DECLARE tmp, porta_type, portb_type, count INTEGER;
1372 IF NEW.porta = NEW.portb THEN
1373 SET NEW.porta = NULL;
1374 ELSEIF NEW.porta > NEW.portb THEN
1375 SET tmp = NEW.porta;
1376 SET NEW.porta = NEW.portb;
1377 SET NEW.portb = tmp;
1378 END IF;
1379 SELECT type INTO porta_type FROM Port WHERE id = NEW.porta;
1380 SELECT type INTO portb_type FROM Port WHERE id = NEW.portb;
1381 SELECT COUNT(*) INTO count FROM PortCompat WHERE (type1 = porta_type AND type2 = portb_type) OR (type1 = portb_type AND type2 = porta_type);
1382 IF count = 0 THEN
1383 SET NEW.porta = NULL;
1384 END IF;
1385 END;
1386 ";
1387 $query[] = "
1388 CREATE TRIGGER `checkLinkBeforeUpdate` BEFORE UPDATE ON `Link`
1389 FOR EACH ROW
1390 BEGIN
1391 DECLARE tmp, porta_type, portb_type, count INTEGER;
1392 IF NEW.porta = NEW.portb THEN
1393 SET NEW.porta = NULL;
1394 ELSEIF NEW.porta > NEW.portb THEN
1395 SET tmp = NEW.porta;
1396 SET NEW.porta = NEW.portb;
1397 SET NEW.portb = tmp;
1398 END IF;
1399 SELECT type INTO porta_type FROM Port WHERE id = NEW.porta;
1400 SELECT type INTO portb_type FROM Port WHERE id = NEW.portb;
1401 SELECT COUNT(*) INTO count FROM PortCompat WHERE (type1 = porta_type AND type2 = portb_type) OR (type1 = portb_type AND type2 = porta_type);
1402 IF count = 0 THEN
1403 SET NEW.porta = NULL;
1404 END IF;
1405 END;
1406 ";
1407 $query[] = "
1408 CREATE TRIGGER `checkPortCompatBeforeDelete` BEFORE DELETE ON `PortCompat`
1409 FOR EACH ROW
1410 BEGIN
1411 DECLARE count INTEGER;
1412 SELECT COUNT(*) INTO count FROM Link LEFT JOIN Port AS PortA ON Link.porta = PortA.id LEFT JOIN Port AS PortB ON Link.portb = PortB.id WHERE (PortA.type = OLD.type1 AND PortB.type = OLD.type2) OR (PortA.type = OLD.type2 AND PortB.type = OLD.type1);
1413 IF count > 0 THEN
1414 UPDATE `Cannot delete: rule still used` SET x = 1;
1415 END IF;
1416 END;
1417 ";
1418 $query[] = "
1419 CREATE TRIGGER `checkPortCompatBeforeUpdate` BEFORE UPDATE ON `PortCompat`
1420 FOR EACH ROW
1421 BEGIN
1422 DECLARE count INTEGER;
1423 SELECT COUNT(*) INTO count FROM Link LEFT JOIN Port AS PortA ON Link.porta = PortA.id LEFT JOIN Port AS PortB ON Link.portb = PortB.id WHERE (PortA.type = OLD.type1 AND PortB.type = OLD.type2) OR (PortA.type = OLD.type2 AND PortB.type = OLD.type1);
1424 IF count > 0 THEN
1425 UPDATE `Cannot update: rule still used` SET x = 1;
1426 END IF;
1427 END;
1428 ";
1429 // one HW type was moved from the 'Network switch' chapter to the 'Network chassis' chapter
1430 // change the type of affected objects to 'Network chassis'
1431 $query[] = "UPDATE `Object` SET objtype_id = 1503 WHERE id IN (SELECT object_id FROM `AttributeValue` WHERE attr_id = 2 and uint_value = 935)";
1432
1433 // convert values of old 'TELNET_OBJS_LISTSRC' 'SSH_OBJS_LISTSRC', 'RDP_OBJS_LISTSRC' variables into 'MGMT_PROTOS'
1434 $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')";
1435 if ('' !== $mgmt_converted_var = convertMgmtConfigVars())
1436 $query[] = "UPDATE `Config` SET varvalue = '" . mysql_escape_string ($mgmt_converted_var) . "' WHERE varname = 'MGMT_PROTOS'"; // TODO: call of deprecated function
1437 $query[] = "DELETE `Config`,`UserConfig` FROM `Config` LEFT JOIN `UserConfig` USING (`varname`) WHERE `Config`.`varname` IN ('TELNET_OBJS_LISTSRC', 'SSH_OBJS_LISTSRC', 'RDP_OBJS_LISTSRC')";
1438
1439 $query[] = "ALTER TABLE `VSEnabledIPs` ADD CONSTRAINT `VSEnabledIPs-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `Object` (`id`) ON DELETE CASCADE";
1440
1441 $query[] = "UPDATE Config SET varvalue = '0.20.6' WHERE varname = 'DB_VERSION'";
1442 break;
1443 case 'dictionary':
1444 $query = reloadDictionary();
1445 break;
1446 default:
1447 return NULL;
1448 }
1449 return $query;
1450 }
1451
1452 function executeUpgradeBatch ($batchid)
1453 {
1454 global $dbxlink;
1455 $query = getUpgradeBatch($batchid);
1456 if ($query === NULL)
1457 {
1458 showError ("unknown batch '${batchid}'", __FUNCTION__);
1459 die;
1460 }
1461 $failures = array();
1462 echo "<tr><th>Executing batch '${batchid}'</th><td>";
1463 foreach ($query as $q)
1464 {
1465 try
1466 {
1467 $result = $dbxlink->query ($q);
1468 }
1469 catch (PDOException $e)
1470 {
1471 $errorInfo = $dbxlink->errorInfo();
1472 $failures[] = array ($q, $errorInfo[2]);
1473 }
1474 }
1475 if (!count ($failures))
1476 echo "<strong><font color=green>done</font></strong>";
1477 else
1478 {
1479 echo "<strong><font color=red>The following queries failed:</font></strong><br><pre>";
1480 foreach ($failures as $f)
1481 {
1482 list ($q, $i) = $f;
1483 echo "${q} -- ${i}\n";
1484 }
1485 echo "</pre>";
1486 }
1487 echo '</td></tr>';
1488 }
1489
1490 function authenticate_admin ($username, $password)
1491 {
1492 global $dbxlink;
1493 $prepared = $dbxlink->prepare ('SELECT COUNT(*) FROM UserAccount WHERE user_id=1 AND user_name=? AND user_password_hash=?');
1494 if (!$prepared->execute (array ($username, sha1 ($password))))
1495 die ('SQL query failed in ' . __FUNCTION__);
1496 $rows = $prepared->fetchAll (PDO::FETCH_NUM);
1497 return $rows[0][0] == 1;
1498 }
1499
1500 // Database version detector. Should behave corretly on any
1501 // working dataset a user might have.
1502 function getDatabaseVersion ()
1503 {
1504 global $dbxlink;
1505 $prepared = $dbxlink->prepare ('SELECT varvalue FROM Config WHERE varname = "DB_VERSION" and vartype = "string"');
1506 if (! $prepared->execute())
1507 {
1508 $errorInfo = $dbxlink->errorInfo();
1509 die (__FUNCTION__ . ': SQL query failed with error ' . $errorInfo[2]);
1510 }
1511 $rows = $prepared->fetchAll (PDO::FETCH_NUM);
1512 unset ($result);
1513 if (count ($rows) != 1 || !strlen ($rows[0][0]))
1514 die (__FUNCTION__ . ': Cannot guess database version. Config table is present, but DB_VERSION is missing or invalid. Giving up.');
1515 $ret = $rows[0][0];
1516 return $ret;
1517 }
1518
1519 function showUpgradeError ($info = '', $location = 'N/A')
1520 {
1521 if (preg_match ('/\.php$/', $location))
1522 $location = basename ($location);
1523 elseif ($location != 'N/A')
1524 $location = $location . '()';
1525 echo "<div class=msg_error>An error has occured in [${location}]. ";
1526 if (!strlen ($info))
1527 echo 'No additional information is available.';
1528 else
1529 echo "Additional information:<br><p>\n<pre>\n${info}\n</pre></p>";
1530 echo "Go back or try starting from <a href='index.php'>index page</a>.<br></div>\n";
1531 }
1532
1533 // changes the value of config variable. If $old_value_filter is set, value is changed only if current value equals to it.
1534 function replaceConfigVarValue ($varname, $new_value, $old_value_filter = NULL)
1535 {
1536 global $dbxlink;
1537 if (isset ($old_value_filter))
1538 {
1539 $result = $dbxlink->prepare ("SELECT varvalue FROM Config WHERE varname = ?");
1540 $result->execute (array ($varname));
1541 if ($row = $result->fetch (PDO::FETCH_ASSOC))
1542 if ($row['varvalue'] != $old_value_filter)
1543 return;
1544 unset ($result);
1545 }
1546 $result = $dbxlink->prepare ("UPDATE Config set varvalue = ? WHERE varname = ?");
1547 $result->execute (array ($new_value, $varname));
1548 }
1549
1550 function renderUpgraderHTML()
1551 {
1552 global $found_secret_file;
1553 if (! $found_secret_file)
1554 die ('<center>There is no working RackTables instance here, <a href="?module=installer">install</a>?</center>');
1555
1556 try
1557 {
1558 connectDB();
1559 }
1560 catch (RackTablesError $e)
1561 {
1562 die ("Database connection failed:\n\n" . $e->getMessage());
1563 }
1564
1565 if
1566 (
1567 !isset ($_SERVER['PHP_AUTH_USER']) or
1568 !strlen ($_SERVER['PHP_AUTH_USER']) or
1569 !isset ($_SERVER['PHP_AUTH_PW']) or
1570 !strlen ($_SERVER['PHP_AUTH_PW']) or
1571 !authenticate_admin ($_SERVER['PHP_AUTH_USER'], $_SERVER['PHP_AUTH_PW'])
1572 )
1573 {
1574 header ('WWW-Authenticate: Basic realm="RackTables upgrade"');
1575 header ('HTTP/1.0 401 Unauthorized');
1576 ?>
1577 <h1>Trouble logging in?</h1>
1578 You are trying to authenticate for the RackTables upgrade screen. This means that
1579 you must authenticate with the username and password of the main RackTables
1580 administrator. There is only one such account in each installation, its default
1581 username is "admin". RackTables wiki provides more information on this topic.
1582 <?php
1583 die;
1584 }
1585
1586 ?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
1587 <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
1588 <head><title>RackTables upgrade script</title>
1589 <style type="text/css">
1590 .tdleft {
1591 text-align: left;
1592 }
1593
1594 .trok {
1595 background-color: #80FF80;
1596 }
1597
1598 .trwarning {
1599 background-color: #FFFF80;
1600 }
1601
1602 .trerror {
1603 background-color: #FF8080;
1604 }
1605 </style>
1606 </head>
1607 <body>
1608 <h1>Platform check status</h1>
1609 <?php
1610
1611 if (!platform_is_ok())
1612 {
1613 echo '<h1>Please resolve the failed (red) item(s) above.</h1>';
1614 die ('</body></html>');
1615 }
1616
1617 echo '<h1>Upgrade status</h1>';
1618 $dbver = getDatabaseVersion();
1619 echo '<table border=1 cellpadding=5>';
1620 echo "<tr><th>Current status</th><td>Data version: ${dbver}<br>Code version: " . CODE_VERSION . "</td></tr>\n";
1621
1622 $path = getDBUpgradePath ($dbver, CODE_VERSION);
1623 if ($path === NULL)
1624 {
1625 echo "<tr><th>Upgrade path</th><td><font color=red>not found</font></td></tr>\n";
1626 echo "<tr><th>Summary</th><td>Check README for more information. RackTables releases prior to 0.18.0 ";
1627 echo "must be upgraded to 0.18.0 first.</td></tr>\n";
1628 }
1629 else
1630 {
1631 if (!count ($path))
1632 echo "<tr><th>Summary</th><td>Come back later.</td></tr>\n";
1633 else
1634 {
1635 echo "<tr><th>Upgrade path</th><td>${dbver} &rarr; " . implode (' &rarr; ', $path) . "</td></tr>\n";
1636 global $relnotes;
1637 foreach ($path as $batchid)
1638 if (isset ($relnotes[$batchid]))
1639 echo "<tr><th>Release notes for ${batchid}</th><td><pre>" . $relnotes[$batchid] . "</pre></td></tr>\n";
1640 if (array_key_exists ('reallyreally', $_REQUEST))
1641 {
1642 foreach ($path as $batchid)
1643 executeUpgradeBatch ($batchid);
1644 executeUpgradeBatch ('dictionary');
1645 echo "<tr><th>Summary</th><td>Upgrade complete, it is Ok to ";
1646 echo "<a href='index.php'>enter</a> the system.</td></tr>\n";
1647 }
1648 else
1649 {
1650 echo '<form method=post action="index.php?module=upgrade"><tr><th>Wait!</th>';
1651 echo '<td><p>RackTables database upgrades sometimes go wrong because of assorted reasons. ';
1652 echo 'It is <strong>highly recommended</strong> to make a database backup before ';
1653 echo 'proceeding any further. <tt>mysqldump</tt> and <tt>PHPMyAdmin</tt> are convenient ';
1654 echo 'tools for doing this.</p>';
1655 echo '<p><input type=checkbox name=reallyreally id=reallyreally><label for=reallyreally>';
1656 echo 'I am ready to bear all risks of this upgrade. I am ready to roll it back in case of ';
1657 echo 'a failure.</label> <input type=submit value="Yes, I am."></p></td></tr></form>';
1658 }
1659 }
1660 }
1661 echo '</table>';
1662 echo '</body></html>';
1663 }
1664
1665 function convertSLBTablesToBinIPs()
1666 {
1667 global $dbxlink;
1668
1669 $dbxlink->query ("DROP TABLE IF EXISTS `IPv4VS_new`, `IPv4RS_new`, `IPv4VS_old`, `IPv4RS_old`");
1670
1671 $dbxlink->query (<<<END
1672 CREATE TABLE `IPv4VS_new` (
1673 `id` int(10) unsigned NOT NULL auto_increment,
1674 `vip` varbinary(16) NOT NULL,
1675 `vport` smallint(5) unsigned default NULL,
1676 `proto` enum('TCP','UDP','MARK') NOT NULL default 'TCP',
1677 `name` char(255) default NULL,
1678 `vsconfig` text,
1679 `rsconfig` text,
1680 PRIMARY KEY (`id`),
1681 KEY `vip` (`vip`)
1682 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1683 END
1684 );
1685 $result = $dbxlink->query ("SELECT * FROM IPv4VS");
1686 $rows = $result->fetchAll (PDO::FETCH_ASSOC);
1687 unset ($result);
1688 foreach ($rows as $row)
1689 {
1690 $row['vip'] = ip4_int2bin ($row['vip']);
1691 usePreparedInsertBlade ('IPv4VS_new', $row);
1692 }
1693
1694 $dbxlink->query (<<<END
1695 CREATE TABLE `IPv4RS_new` (
1696 `id` int(10) unsigned NOT NULL auto_increment,
1697 `inservice` enum('yes','no') NOT NULL default 'no',
1698 `rsip` varbinary(16) NOT NULL,
1699 `rsport` smallint(5) unsigned default NULL,
1700 `rspool_id` int(10) unsigned default NULL,
1701 `rsconfig` text,
1702 `comment` varchar(255) DEFAULT NULL,
1703 PRIMARY KEY (`id`),
1704 KEY `rsip` (`rsip`),
1705 UNIQUE KEY `pool-endpoint` (`rspool_id`,`rsip`,`rsport`),
1706 CONSTRAINT `IPRS-FK` FOREIGN KEY (`rspool_id`) REFERENCES `IPv4RSPool` (`id`) ON DELETE CASCADE
1707 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1708 END
1709 );
1710 $result = $dbxlink->query ("SELECT * FROM IPv4RS");
1711 $rows = $result->fetchAll (PDO::FETCH_ASSOC);
1712 unset ($result);
1713 foreach ($rows as $row)
1714 {
1715 $row['rsip'] = ip4_int2bin ($row['rsip']);
1716 usePreparedInsertBlade ('IPv4RS_new', $row);
1717 }
1718
1719 $dbxlink->query (<<<END
1720 RENAME TABLE
1721 `IPv4VS` TO `IPv4VS_old`,
1722 `IPv4VS_new` TO `IPv4VS`,
1723 `IPv4RS` TO `IPv4RS_old`,
1724 `IPv4RS_new` TO `IPv4RS`
1725 END
1726 );
1727 // re-create foreign key in IPv4LB
1728 $dbxlink->query ("ALTER TABLE `IPv4LB` DROP FOREIGN KEY `IPv4LB-FK-vs_id`");
1729 $dbxlink->query ("ALTER TABLE `IPv4LB` ADD CONSTRAINT `IPv4LB-FK-vs_id` FOREIGN KEY (`vs_id`) REFERENCES `IPv4VS` (`id`)");
1730
1731 $dbxlink->query ("DROP TABLE `IPv4VS_old`, `IPv4RS_old`");
1732
1733 // re-create foreign key in IPv4RS
1734 $dbxlink->query ("ALTER TABLE `IPv4RS` DROP FOREIGN KEY `IPRS-FK`");
1735 $dbxlink->query ("ALTER TABLE `IPv4RS` ADD CONSTRAINT `IPv4RS-FK` FOREIGN KEY (`rspool_id`) REFERENCES `IPv4RSPool` (`id`) ON DELETE CASCADE");
1736 }
1737
1738 // This is a swiss-knife blade to insert a record into a table.
1739 // The first argument is table name.
1740 // The second argument is an array of "name" => "value" pairs.
1741 // returns integer - affected rows count. Throws exception on error
1742 function usePreparedInsertBlade ($tablename, $columns)
1743 {
1744 global $dbxlink;
1745 $query = "INSERT INTO ${tablename} (" . implode (', ', array_keys ($columns));
1746 $query .= ') VALUES (' . questionMarks (count ($columns)) . ')';
1747 // Now the query should be as follows:
1748 // INSERT INTO table (c1, c2, c3) VALUES (?, ?, ?)
1749 try
1750 {
1751 $prepared = $dbxlink->prepare ($query);
1752 $prepared->execute (array_values ($columns));
1753 return $prepared->rowCount();
1754 }
1755 catch (PDOException $e)
1756 {
1757 throw convertPDOException ($e);
1758 }
1759 }
1760
1761 // converts the values of old-style config vars TELNET_OBJS_LISTSRC, SSH_OBJS_LISTSRC, RDP_OBJS_LISTSRC
1762 // to the format of MGMT_PROTOS (comma-separated list of "proto: rackcode" pairs)
1763 function convertMgmtConfigVars()
1764 {
1765 global $dbxlink;
1766 $ret = array();
1767 foreach (array ('telnet' => 'TELNET_OBJS_LISTSRC', 'ssh' => 'SSH_OBJS_LISTSRC', 'rdp' => 'RDP_OBJS_LISTSRC') as $proto => $varname)
1768 {
1769 $result = $dbxlink->prepare ("SELECT varvalue FROM Config WHERE varname = ?");
1770 $result->execute (array ($varname));
1771 if ($row = $result->fetch (PDO::FETCH_ASSOC))
1772 if ($row['varvalue'] != 'false' && $row['varvalue'] != '')
1773 $ret[] = "$proto: " . $row['varvalue'];
1774 unset ($result);
1775 }
1776 return implode (',', $ret);
1777 }
1778
1779 ?>