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