fix spelling in release notes
[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.17.0' => <<<ENDOFTEXT
10 LDAP options have been moved to LDAP_options array. This means, that if you were
11 using LDAP authentication for users in version 0.16.x, it will break right after
12 upgrade to 0.17.0. To get things working again, adjust existing secret.php file
13 according to secret-sample.php file provided with 0.17.0 release.
14
15 This release is the first to take advantage of the foreign key support
16 provided by the InnoDB storage engine in MySQL. The installer and
17 upgrader scripts check for InnoDB support and cannot complete without it.
18 If you have trouble, the first step is to make sure the 'skip-innodb'
19 option in my.cnf is commented out.
20
21 Another change is the addition of support for file uploads. Files are stored
22 in the database. There are several settings in php.ini which you may need to modify:
23 file_uploads - needs to be On
24 upload_max_filesize - max size for uploaded files
25 post_max_size - max size of all form data submitted via POST (including files)
26
27 User accounts used to have 'enabled' flag, which allowed individual blocking and
28 unblocking of each. This flag was dropped in favor of existing mean of access
29 setup (RackCode). An unconditional denying rule is automatically added into RackCode
30 for such blocked account, so the effective security policy remains the same.
31 ENDOFTEXT
32 ,
33 '0.18.2' => <<<ENDOFTEXT
34 RackTables from its version 0.18.0 and later is not compatible with
35 RHEL/CentOS (at least with versions up to 5.5) Linux distributions
36 in their default installation. There are yet options to work around that:
37 1. Install RackTables on a server with a different distribution/OS.
38 2. Request Linux distribution vendor to fix the bug with PCRE.
39 3. Repair your RHEL/CentOS installation yourself by fixing its PCRE
40 RPM as explained here: http://bugs.centos.org/view.php?id=3252
41 ENDOFTEXT
42 ,
43 '0.19.0' => <<<ENDOFTEXT
44 The files, which are intended for the httpd (web-server) directory, are
45 now in the "wwwroot" directory of the tar.gz archive. Files outside of
46 that directory are not directly intended for httpd environment and should
47 not be copied to the server.
48
49 This release incorporates ObjectLog functionality, which used to be
50 available as a separate plugin. For the best results it is advised to
51 disable (through local.php) external ObjectLog plugin permanently before
52 the new version is installed. All previously accumulated ObjectLog records
53 will be available through the updated standard interface.
54
55 RackTables is now using PHP JSON extension which is included in the PHP
56 core since 5.2.0.
57
58 The barcode attribute was removed. The upgrade script attempts to
59 preserve the data by moving it to either the 'OEM S/N 1' attribute or to
60 a Log entry. You should backup your database beforehand anyway.
61 ENDOFTEXT
62 ,
63 '0.19.2' => <<<ENDOFTEXT
64 This release is different in filesystem layout. The "gateways" directory
65 has been moved from "wwwroot" directory. This improves security a bit.
66 You can also separate your local settings and add-ons from the core RackTables code.
67 To do that, put a single index.php file into the DocumentRoot of your http server:
68
69 <?php
70 \$racktables_confdir='/directory/where/your/secret.php/and/local.php/files/are/stored';
71 require '/directory_where_you_extracted_racktables_distro/wwwroot/index.php';
72 ?>
73
74 No more files are needed to be available directly over the HTTP.
75 Full list of filesystem paths which could be specified in custom index.php or secret.php:
76 \$racktables_gwdir: path to the gateways directory;
77 \$racktables_staticdir: path to the directory containing 'pix', 'js', 'css' dirs;
78 \$racktables_confdir: path where secret.php and local.php are located. It is not
79 recommended to define it in secret.php, cause only the path to
80 local.php will be affected;
81 \$path_to_secret_php: Ignore \$racktables_confdir when locating secret.php and use
82 the specified path;
83 \$path_to_local_php: idem for local.php.
84 ENDOFTEXT
85 ,
86
87 '0.19.13' => <<<ENDOFTEXT
88 A new "date" attribute type has been added. Existing date based fields ("HW warranty expiration",
89 "support contract expiration" and "SW warranty expiration") will be converted to this new type but
90 must be in the format "mm/dd/yyyy" otherwise the conversion will fail.
91 ENDOFTEXT
92 ,
93
94 '0.20.0' => <<<ENDOFTEXT
95 WARNING: This release have too many internal changes, some of them were waiting more than a year
96 to be released. So this release is considered "BETA" and is recommended only to curiuos users,
97 who agree to sacrifice the stability to the progress.
98
99 Racks and Rows are now stored in the database as Objects. The RackObject table
100 was renamed to Object. SQL views were created to ease the migration of custom
101 reports and scripts.
102
103 New plugins engine instead of local.php file. To make your own code stored in local.php work,
104 you must move the local.php file into the plugins/ directory. The name of this file does not
105 matter any more. You also can store multiple files in that dir, separate your plugins by features,
106 share them and try the plugins from other people just placing them into plugins/ dir, no more merging.
107 \$path_to_local_php variable has no special meaning any more.
108 \$racktables_confdir variable is now used only to search for secret.php file.
109 \$racktables_plugins_dir is a new overridable special variable pointing to plugins/ directory.
110
111 Beginning with this version it is possible to delete IP prefixes, VLANs, Virtual services
112 and RS pools from within theirs properties tab. So please inspect your permissions rules
113 to assure there are no undesired allows for deletion of these objects. To ensure this, you could
114 try this code in the beginning of permissions script:
115
116 allow {userid_1} and {\$op_del}
117 deny {\$op_del} and ({\$tab_edit} or {\$tab_properties})
118
119 Hardware gateways engine was rewritten in this version of RackTables. This means that
120 the file gateways/deviceconfig/switch.secrets.php is not used any more. To get information
121 about configuring connection properties and credentials in a new way please visit
122 http://wiki.racktables.org/index.php/Gateways
123
124 This also means that recently added features based on old API (D-Link switches and Linux
125 gateway support contributed by Ilya Evseev) are not working any more and waiting to be
126 forward-ported to new gateways API. Sorry for that.
127
128 Two new config variables appeared in this version:
129 - SEARCH_DOMAINS. Comma-separated list of DNS domains which are considered "base" for your
130 network. If RackTables search engine finds multiple objects based on your search input, but
131 there is only one which FQDN consists of your input and one of these search domains, you will
132 be redirected to this object and other results will be discarded. Such behavior was unconditional
133 since 0.19.3, which caused many objections from users. So welcome this config var.
134 - QUICK_LINK_PAGES. Comma-separated list of RackTables pages to display links to them on top.
135 Each user could have his own list.
136
137 Also some of config variables have changed their default values in this version.
138 This means that upgrade script will change their values if you have them in previous default state.
139 This could be inconvenient, but it is the most effective way to encourage users to use new features.
140 If this behavior is not what you want, simply revert these variables' values:
141 - SHOW_LAST_TAB no => yes
142 - IPV4_TREE_SHOW_USAGE yes =>no (networks' usage is still available by click)
143 - IPV4LB_LISTSRC {\$typeid_4} => false
144 - FILTER_DEFAULT_ANDOR or => and (this implicitly enables the feature of dynamic tree shrinking)
145 - FILTER_SUGGEST_EXTRA no => yes (yes, we have extra logical filters!)
146 - IPV4_TREE_RTR_AS_CELL yes => no (display routers as simple text, not cell)
147
148 Also please note that variable IPV4_TREE_RTR_AS_CELL now has third special value
149 besides 'yes' and 'no': 'none'. Use 'none' value if you are experiencing low performance
150 on IP tree page. It will completely disable IP ranges scan for used/spare IPs and the
151 speed of IP tree will increase radically. The price is you will not see the routers in
152 IP tree at all.
153
154 ENDOFTEXT
155 ,
156
157 '0.20.1' => <<<ENDOFTEXT
158 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.
159
160 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:
161 allow {client network} and {New York}
162 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:
163 allow {client network} and {New York} and not {\$page_ipaddress}
164
165 ENDOFTEXT
166 ,
167
168 '0.20.4' => <<<ENDOFTEXT
169 Please note that some dictionary items of Cisco Catalyst 2960 series switches
170 were renamed to meet official Cisco classification:
171 2960-48TT => 2960-48TT-L
172 2960-24TC => 2960-24TC-L
173 2960-24TT => 2960-24TT-L
174 2960-8TC => 2960-8TC-L
175 2960G-48TC => 2960G-48TC-L
176 2960G-24TC => 2960G-24TC-L
177 2960G-8TC => 2960G-8TC-L
178 C2960-24 => C2960-24-S
179 C2960G-24PC => C2960-24PC-L
180
181 The DATETIME_FORMAT configuration option used in setting date and time output
182 format now uses a different [1] syntax. During upgrade the option is reset to
183 the default value, which is now %Y-%m-%d (YYYY-MM-DD) per ISO 8601.
184
185 This release intoduces two new configuration options:
186 REVERSED_RACKS_LISTSRC and NEAREST_RACKS_CHECKBOX.
187
188 [1] http://php.net/manual/en/function.strftime.php
189 ENDOFTEXT
190 ,
191
192 '0.20.5' => <<<ENDOFTEXT
193 This release introduces the VS groups feature. VS groups is a new way to store
194 and display virtual services configuration. New realm 'ipvs' (VS group) is created.
195 All the existing VS configuration is kept and displayed as-is, but user is free to convert
196 it to the new format, which displays it in more natural way and allows to generate
197 virtual_server_group keepalived configs. To convert a virtual service to the new format,
198 you need to manually create the vs group object and assign IP addresses to it. Then, if you
199 have the old-style VSes configured, the Migrate tab will be displayed on the particular VS group's
200 page. After successfull migration, you can remove the old-style VS objects.
201
202 Old-style VS configuration becomes DEPRECATED. Its support will be removed in one of the following
203 major releases. So it is strongly recommended to convert it to the new format.
204 ENDOFTEXT
205 ,
206
207 );
208
209 // At the moment we assume, that for any two releases we can
210 // sequentally execute all batches, that separate them, and
211 // nothing will break. If this changes one day, the function
212 // below will have to generate smarter upgrade paths, while
213 // the upper layer will remain the same.
214 // Returning an empty array means that no upgrade is necessary.
215 // Returning NULL indicates an error.
216 function getDBUpgradePath ($v1, $v2)
217 {
218 $versionhistory = array
219 (
220 '0.16.4',
221 '0.16.5',
222 '0.16.6',
223 '0.17.0',
224 '0.17.1',
225 '0.17.2',
226 '0.17.3',
227 '0.17.4',
228 '0.17.5',
229 '0.17.6',
230 '0.17.7',
231 '0.17.8',
232 '0.17.9',
233 '0.17.10',
234 '0.17.11',
235 '0.18.0',
236 '0.18.1',
237 '0.18.2',
238 '0.18.3',
239 '0.18.4',
240 '0.18.5',
241 '0.18.6',
242 '0.18.7',
243 '0.19.0',
244 '0.19.1',
245 '0.19.2',
246 '0.19.3',
247 '0.19.4',
248 '0.19.5',
249 '0.19.6',
250 '0.19.7',
251 '0.19.8',
252 '0.19.9',
253 '0.19.10',
254 '0.19.11',
255 '0.19.12',
256 '0.19.13',
257 '0.19.14',
258 '0.20.0',
259 '0.20.1',
260 '0.20.2',
261 '0.20.3',
262 '0.20.4',
263 '0.20.5',
264 );
265 if (!in_array ($v1, $versionhistory) or !in_array ($v2, $versionhistory))
266 return NULL;
267 $skip = TRUE;
268 $path = NULL;
269 // foreach() below cannot handle this specific case
270 if ($v1 == $v2)
271 return array();
272 // Now collect all versions > $v1 and <= $v2
273 foreach ($versionhistory as $v)
274 {
275 if ($skip and $v == $v1)
276 {
277 $skip = FALSE;
278 $path = array();
279 continue;
280 }
281 if ($skip)
282 continue;
283 $path[] = $v;
284 if ($v == $v2)
285 break;
286 }
287 return $path;
288 }
289
290 // Upgrade batches are named exactly as the release where they first appear.
291 // That is simple, but seems sufficient for beginning.
292 function getUpgradeBatch ($batchid)
293 {
294 $query = array();
295 global $dbxlink;
296 switch ($batchid)
297 {
298 case '0.16.5':
299 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('IPV4_TREE_SHOW_USAGE','yes','string','no','no','Show address usage in IPv4 tree')";
300 $query[] = "update Config set varvalue = '0.16.5' where varname = 'DB_VERSION'";
301 break;
302 case '0.16.6':
303 $query[] = "update Config set varvalue = '0.16.6' where varname = 'DB_VERSION'";
304 break;
305 case '0.17.0':
306 // create tables for storing files (requires InnoDB support)
307 if (!isInnoDBSupported ())
308 {
309 showUpgradeError ("Cannot upgrade because InnoDB tables are not supported by your MySQL server. See the README for details.", __FUNCTION__);
310 die;
311 }
312
313 $query[] = "alter table Chapter change chapter_no id int(10) unsigned NOT NULL auto_increment";
314 $query[] = "alter table Chapter change chapter_name name char(128) NOT NULL";
315 $query[] = "alter table Chapter drop key chapter_name";
316 $query[] = "alter table Chapter add UNIQUE KEY name (name)";
317 $query[] = "alter table Attribute change attr_id id int(10) unsigned NOT NULL auto_increment";
318 $query[] = "alter table Attribute change attr_type type enum('string','uint','float','dict') default NULL";
319 $query[] = "alter table Attribute change attr_name name char(64) default NULL";
320 $query[] = "alter table Attribute drop key attr_name";
321 $query[] = "alter table Attribute add UNIQUE KEY name (name)";
322 $query[] = "alter table AttributeMap change chapter_no chapter_id int(10) unsigned NOT NULL";
323 $query[] = "alter table Dictionary change chapter_no chapter_id int(10) unsigned NOT NULL";
324 // schema changes for file management
325 $query[] = "
326 CREATE TABLE `File` (
327 `id` int(10) unsigned NOT NULL auto_increment,
328 `name` char(255) NOT NULL,
329 `type` char(255) NOT NULL,
330 `size` int(10) unsigned NOT NULL,
331 `ctime` datetime NOT NULL,
332 `mtime` datetime NOT NULL,
333 `atime` datetime NOT NULL,
334 `contents` longblob NOT NULL,
335 `comment` text,
336 PRIMARY KEY (`id`),
337 UNIQUE KEY `name` (`name`)
338 ) ENGINE=InnoDB";
339 $query[] = "
340 CREATE TABLE `FileLink` (
341 `id` int(10) unsigned NOT NULL auto_increment,
342 `file_id` int(10) unsigned NOT NULL,
343 `entity_type` enum('ipv4net','ipv4rspool','ipv4vs','object','rack','user') NOT NULL default 'object',
344 `entity_id` int(10) NOT NULL,
345 PRIMARY KEY (`id`),
346 UNIQUE KEY `FileLink-unique` (`file_id`,`entity_type`,`entity_id`),
347 KEY `FileLink-file_id` (`file_id`),
348 CONSTRAINT `FileLink-File_fkey` FOREIGN KEY (`file_id`) REFERENCES `File` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
349 ) ENGINE=InnoDB";
350 $query[] = "ALTER TABLE TagStorage MODIFY COLUMN target_realm enum('file','ipv4net','ipv4rspool','ipv4vs','object','rack','user') NOT NULL default 'object'";
351
352 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (24,'no','network security models')";
353 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (25,'no','wireless models')";
354 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,1,0)";
355 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,2,24)";
356 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,3,0)";
357 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,5,0)";
358 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,14,0)";
359 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,16,0)";
360 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,17,0)";
361 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,18,0)";
362 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,20,0)";
363 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,21,0)";
364 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,22,0)";
365 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,24,0)";
366 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (965,1,0)";
367 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (965,3,0)";
368 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (965,2,25)";
369 $query[] = 'alter table IPBonds rename to IPv4Allocation';
370 $query[] = 'alter table PortForwarding rename to IPv4NAT';
371 $query[] = 'alter table IPRanges rename to IPv4Network';
372 $query[] = 'alter table IPAddress rename to IPv4Address';
373 $query[] = 'alter table IPLoadBalancer rename to IPv4LB';
374 $query[] = 'alter table IPRSPool rename to IPv4RSPool';
375 $query[] = 'alter table IPRealServer rename to IPv4RS';
376 $query[] = 'alter table IPVirtualService rename to IPv4VS';
377 $query[] = "alter table TagStorage change column target_realm entity_realm enum('file','ipv4net','ipv4vs','ipv4rspool','object','rack','user') NOT NULL default 'object'";
378 $query[] = 'alter table TagStorage change column target_id entity_id int(10) unsigned NOT NULL';
379 $query[] = 'alter table TagStorage drop key entity_tag';
380 $query[] = 'alter table TagStorage drop key target_id';
381 $query[] = 'alter table TagStorage add UNIQUE KEY `entity_tag` (`entity_realm`,`entity_id`,`tag_id`)';
382 $query[] = 'alter table TagStorage add KEY `entity_id` (`entity_id`)';
383 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('PREVIEW_TEXT_MAXCHARS','10240','uint','yes','no','Max chars for text file preview')";
384 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('PREVIEW_TEXT_ROWS','25','uint','yes','no','Rows for text file preview')";
385 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('PREVIEW_TEXT_COLS','80','uint','yes','no','Columns for text file preview')";
386 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('PREVIEW_IMAGE_MAXPXS','320','uint','yes','no','Max pixels per axis for image file preview')";
387 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('VENDOR_SIEVE','','string','yes','no','Vendor sieve configuration')";
388 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('IPV4LB_LISTSRC','{\$typeid_4}','string','yes','no','List source: IPv4 load balancers')";
389 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('IPV4OBJ_LISTSRC','{\$typeid_4} or {\$typeid_7} or {\$typeid_8} or {\$typeid_12} or {\$typeid_445} or {\$typeid_447}','string','yes','no','List source: IPv4-enabled objects')";
390 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('IPV4NAT_LISTSRC','{\$typeid_4} or {\$typeid_7} or {\$typeid_8}','string','yes','no','List source: IPv4 NAT performers')";
391 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('ASSETWARN_LISTSRC','{\$typeid_4} or {\$typeid_7} or {\$typeid_8}','string','yes','no','List source: object, for which asset tag should be set')";
392 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('NAMEWARN_LISTSRC','{\$typeid_4} or {\$typeid_7} or {\$typeid_8}','string','yes','no','List source: object, for which common name should be set')";
393 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('RACKS_PER_ROW','12','unit','yes','no','Racks per row')";
394 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('FILTER_PREDICATE_SIEVE','','string','yes','no','Predicate sieve regex(7)')";
395 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('FILTER_DEFAULT_ANDOR','or','string','no','no','Default list filter boolean operation (or/and)')";
396 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('FILTER_SUGGEST_ANDOR','yes','string','no','no','Suggest and/or selector in list filter')";
397 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('FILTER_SUGGEST_TAGS','yes','string','no','no','Suggest tags in list filter')";
398 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('FILTER_SUGGEST_PREDICATES','yes','string','no','no','Suggest predicates in list filter')";
399 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('FILTER_SUGGEST_EXTRA','no','string','no','no','Suggest extra expression in list filter')";
400 $query[] = "delete from Config where varname = 'USER_AUTH_SRC'";
401 $query[] = "delete from Config where varname = 'COOKIE_TTL'";
402 $query[] = "delete from Config where varname = 'rtwidth_0'";
403 $query[] = "delete from Config where varname = 'rtwidth_1'";
404 $query[] = "delete from Config where varname = 'rtwidth_2'";
405 $query[] = "delete from Config where varname = 'NAMEFUL_OBJTYPES'";
406 $query[] = "delete from Config where varname = 'REQUIRE_ASSET_TAG_FOR'";
407 $query[] = "delete from Config where varname = 'IPV4_PERFORMERS'";
408 $query[] = "delete from Config where varname = 'NATV4_PERFORMERS'";
409 $query[] = "alter table TagTree add column valid_realm set('file','ipv4net','ipv4vs','ipv4rspool','object','rack','user') not null default 'file,ipv4net,ipv4vs,ipv4rspool,object,rack,user' after parent_id";
410 $result = $dbxlink->query ("select user_id, user_name, user_realname from UserAccount where user_enabled = 'no'");
411 while ($row = $result->fetch (PDO::FETCH_ASSOC))
412 $query[] = "update Script set script_text = concat('deny {\$userid_${row['user_id']}} # ${row['user_name']} (${row['user_realname']})\n', script_text) where script_name = 'RackCode'";
413 $query[] = "update Script set script_text = NULL where script_name = 'RackCodeCache'";
414 unset ($result);
415 $query[] = "alter table UserAccount drop column user_enabled";
416
417 $query[] = "CREATE TABLE RackRow ( id int(10) unsigned NOT NULL auto_increment, name char(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM";
418
419 $result = $dbxlink->query ("select dict_key, dict_value from Dictionary where chapter_no = 3");
420 while($row = $result->fetch(PDO::FETCH_NUM))
421 $query[] = "insert into RackRow set id=${row[0]}, name='${row[1]}'";
422 unset ($result);
423 $query[] = "delete from Dictionary where chapter_id = 3";
424 $query[] = "delete from Chapter where id = 3";
425 $query[] = "
426 CREATE TABLE `LDAPCache` (
427 `presented_username` char(64) NOT NULL,
428 `successful_hash` char(40) NOT NULL,
429 `first_success` timestamp NOT NULL default CURRENT_TIMESTAMP,
430 `last_retry` timestamp NOT NULL default '0000-00-00 00:00:00',
431 `displayed_name` char(128) default NULL,
432 `memberof` text,
433 UNIQUE KEY `presented_username` (`presented_username`),
434 KEY `scanidx` (`presented_username`,`successful_hash`)
435 ) ENGINE=InnoDB;";
436 $query[] = "alter table UserAccount modify column user_password_hash char(40) NULL";
437 $query[] = 'ALTER TABLE Rack DROP COLUMN deleted';
438 $query[] = 'ALTER TABLE RackHistory DROP COLUMN deleted';
439 $query[] = 'ALTER TABLE RackObject DROP COLUMN deleted';
440 $query[] = 'ALTER TABLE RackObjectHistory DROP COLUMN deleted';
441 // Can't be added straight due to many duplicates, even in "dictbase" data.
442 $result = $dbxlink->query ('SELECT type1, type2, count(*) - 1 as excess FROM PortCompat GROUP BY type1, type2 HAVING excess > 0');
443 while ($row = $result->fetch (PDO::FETCH_ASSOC))
444 $query[] = "DELETE FROM PortCompat WHERE type1 = ${row['type1']} AND type2 = ${row['type2']} limit ${row['excess']}";
445 unset ($result);
446 $query[] = 'ALTER TABLE PortCompat DROP KEY type1';
447 $query[] = 'ALTER TABLE PortCompat ADD UNIQUE `type1_2` (type1, type2)';
448 $query[] = "UPDATE Config SET varvalue = '0.17.0' WHERE varname = 'DB_VERSION'";
449
450 break;
451 case '0.17.1':
452 $query[] = "ALTER TABLE Dictionary DROP KEY `chap_to_key`";
453 // Token set has changed, so the cache isn't valid any more.
454 $query[] = "UPDATE Script SET script_text = NULL WHERE script_name = 'RackCodeCache'";
455 $query[] = "UPDATE Config SET varvalue = '0.17.1' WHERE varname = 'DB_VERSION'";
456 break;
457 case '0.17.2':
458 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (26,'no','fibre channel switch models')";
459 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1055,2,26)";
460 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('DEFAULT_SNMP_COMMUNITY','public','string','no','no','Default SNMP Community string')";
461 // wipe irrelevant records (ticket:250)
462 $query[] = "DELETE FROM TagStorage WHERE entity_realm = 'file' AND entity_id NOT IN (SELECT id FROM File)";
463 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('IPV4_ENABLE_KNIGHT','yes','string','no','no','Enable IPv4 knight feature')";
464 $query[] = "ALTER TABLE IPv4Network ADD COLUMN comment text AFTER name";
465 $query[] = "ALTER TABLE Port ADD INDEX comment (reservation_comment)";
466 $query[] = "ALTER TABLE Port DROP KEY l2address"; // UNIQUE
467 $query[] = "ALTER TABLE Port ADD KEY (l2address)"; // not UNIQUE
468 $query[] = "ALTER TABLE Port DROP KEY object_id";
469 $query[] = "ALTER TABLE Port ADD UNIQUE KEY per_object (object_id, name, type)";
470 $query[] = "INSERT INTO PortCompat (type1, type2) VALUES (20,1083)";
471 $query[] = "INSERT INTO PortCompat (type1, type2) VALUES (21,1083)";
472 $query[] = "INSERT INTO PortCompat (type1, type2) VALUES (1077,1077)";
473 $query[] = "INSERT INTO PortCompat (type1, type2) VALUES (1083,20)";
474 $query[] = "INSERT INTO PortCompat (type1, type2) VALUES (1083,21)";
475 $query[] = "INSERT INTO PortCompat (type1, type2) VALUES (1083,1083)";
476 $query[] = "INSERT INTO PortCompat (type1, type2) VALUES (1087,1087)";
477 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (27,'no','PDU models')";
478 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (2,2,27)";
479 $query[] = "UPDATE Config SET varvalue = '0.17.2' WHERE varname = 'DB_VERSION'";
480 break;
481 case '0.17.3':
482 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('TAGS_TOPLIST_SIZE','50','uint','yes','no','Tags top list size')";
483 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('TAGS_QUICKLIST_SIZE','20','uint','no','no','Tags quick list size')";
484 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('TAGS_QUICKLIST_THRESHOLD','50','uint','yes','no','Tags quick list threshold')";
485 $query[] = "ALTER TABLE AttributeMap MODIFY COLUMN chapter_id int(10) unsigned NULL";
486 $query[] = "UPDATE AttributeMap SET chapter_id = NULL WHERE attr_id IN (SELECT id FROM Attribute WHERE type != 'dict')";
487 // ticket:239
488 $query[] = 'UPDATE AttributeValue SET uint_value = 1018 WHERE uint_value = 731 AND attr_id IN (SELECT attr_id FROM AttributeMap WHERE chapter_id = 12)';
489 $query[] = 'DELETE FROM Dictionary WHERE dict_key = 731';
490 $query[] = "UPDATE Config SET vartype='uint' WHERE varname='RACKS_PER_ROW'";
491 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('ENABLE_MULTIPORT_FORM','no','string','no','no','Enable \"Add/update multiple ports\" form')";
492 $query[] = "UPDATE Config SET varvalue = '0.17.3' WHERE varname = 'DB_VERSION'";
493 break;
494 case '0.17.4':
495 $query[] = "ALTER TABLE Link ENGINE=InnoDB";
496 $query[] = "ALTER TABLE Port ENGINE=InnoDB";
497 $query[] = "ALTER TABLE IPv4RS ENGINE=InnoDB";
498 $query[] = "ALTER TABLE IPv4RSPool ENGINE=InnoDB";
499 $query[] = "ALTER TABLE AttributeValue ENGINE=InnoDB";
500 $query[] = "ALTER TABLE RackObject ENGINE=InnoDB";
501 $query[] = "ALTER TABLE IPv4NAT ENGINE=InnoDB";
502 $query[] = "ALTER TABLE IPv4LB ENGINE=InnoDB";
503 $query[] = "ALTER TABLE IPv4VS ENGINE=InnoDB";
504 $query[] = "DELETE FROM IPv4RS WHERE rspool_id NOT IN (SELECT id FROM IPv4RSPool)";
505 $query[] = "ALTER TABLE Link ADD CONSTRAINT `Link-FK-b` FOREIGN KEY (portb) REFERENCES Port (id)";
506 $query[] = "ALTER TABLE Link ADD CONSTRAINT `Link-FK-a` FOREIGN KEY (porta) REFERENCES Port (id)";
507 $query[] = "ALTER TABLE IPv4RS ADD CONSTRAINT `IPv4RS-FK` FOREIGN KEY (rspool_id) REFERENCES IPv4RSPool (id) ON DELETE CASCADE";
508 $query[] = "ALTER TABLE AttributeValue ADD CONSTRAINT `AttributeValue-FK-object_id` FOREIGN KEY (object_id) REFERENCES RackObject (id)";
509 $query[] = "ALTER TABLE IPv4NAT ADD CONSTRAINT `IPv4NAT-FK-object_id` FOREIGN KEY (object_id) REFERENCES RackObject (id)";
510 $query[] = "ALTER TABLE Port ADD CONSTRAINT `Port-FK-object_id` FOREIGN KEY (object_id) REFERENCES RackObject (id)";
511 $query[] = "ALTER TABLE IPv4LB ADD CONSTRAINT `IPv4LB-FK-rspool_id` FOREIGN KEY (rspool_id) REFERENCES IPv4RSPool (id)";
512 $query[] = "ALTER TABLE IPv4LB ADD CONSTRAINT `IPv4LB-FK-object_id` FOREIGN KEY (object_id) REFERENCES RackObject (id)";
513 $query[] = "ALTER TABLE IPv4LB ADD CONSTRAINT `IPv4LB-FK-vs_id` FOREIGN KEY (vs_id) REFERENCES IPv4VS (id)";
514 $query[] = "UPDATE Config SET varvalue = '0.17.4' WHERE varname = 'DB_VERSION'";
515 break;
516 case '0.17.5':
517 $query[] = "ALTER TABLE TagTree ENGINE=InnoDB";
518 $query[] = "ALTER TABLE TagStorage ENGINE=InnoDB";
519 $query[] = "ALTER TABLE TagStorage ADD CONSTRAINT `TagStorage-FK-tag_id` FOREIGN KEY (tag_id) REFERENCES TagTree (id)";
520 $query[] = "ALTER TABLE TagTree ADD CONSTRAINT `TagTree-K-parent_id` FOREIGN KEY (parent_id) REFERENCES TagTree (id)";
521 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (21,1195)';
522 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (22,1196)';
523 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (23,1196)';
524 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (20,1195)';
525 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (25,1202)';
526 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (26,1202)';
527 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (27,1204)';
528 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (28,1204)';
529 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1083,1195)';
530 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1084,1084)';
531 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1195,20)';
532 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1195,21)';
533 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1195,1083)';
534 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1195,1195)';
535 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1196,22)';
536 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1196,23)';
537 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1196,1196)';
538 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1197,1197)';
539 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1198,1199)';
540 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1199,1198)';
541 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1200,1200)';
542 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1201,1201)';
543 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1202,25)';
544 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1202,26)';
545 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1202,1202)';
546 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1203,1203)';
547 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1204,27)';
548 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1204,28)';
549 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1204,1204)';
550 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1205,1205)';
551 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1206,1207)';
552 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1207,1206)';
553 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1316,1316)';
554 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (16, 1322)';
555 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1322, 16)';
556 $query[] = 'DELETE FROM PortCompat WHERE type1 = 16 AND type2 = 16';
557 for ($i = 1209; $i <= 1300; $i++)
558 $query[] = "INSERT INTO PortCompat (type1, type2) VALUES (${i}, ${i})";
559 $query[] = "
560 CREATE TABLE `PortInnerInterface` (
561 `id` int(10) unsigned NOT NULL,
562 `iif_name` char(16) NOT NULL,
563 PRIMARY KEY (`id`),
564 UNIQUE KEY `iif_name` (`iif_name`)
565 ) ENGINE=InnoDB";
566 $query[] = "INSERT INTO `PortInnerInterface` VALUES (1,'hardwired')";
567 $query[] = "INSERT INTO `PortInnerInterface` VALUES (2,'SFP-100')";
568 $query[] = "INSERT INTO `PortInnerInterface` VALUES (3,'GBIC')";
569 $query[] = "INSERT INTO `PortInnerInterface` VALUES (4,'SFP-1000')";
570 $query[] = "INSERT INTO `PortInnerInterface` VALUES (5,'XENPAK')";
571 $query[] = "INSERT INTO `PortInnerInterface` VALUES (6,'X2')";
572 $query[] = "INSERT INTO `PortInnerInterface` VALUES (7,'XPAK')";
573 $query[] = "INSERT INTO `PortInnerInterface` VALUES (8,'XFP')";
574 $query[] = "INSERT INTO `PortInnerInterface` VALUES (9,'SFP+')";
575 $query[] = "
576 CREATE TABLE `PortInterfaceCompat` (
577 `iif_id` int(10) unsigned NOT NULL,
578 `oif_id` int(10) unsigned NOT NULL,
579 UNIQUE KEY `pair` (`iif_id`,`oif_id`),
580 CONSTRAINT `PortInterfaceCompat-FK-iif_id` FOREIGN KEY (`iif_id`) REFERENCES `PortInnerInterface` (`id`)
581 ) ENGINE=InnoDB";
582 $query[] = "ALTER TABLE Port ADD COLUMN iif_id int unsigned NOT NULL AFTER name"; // will set iif_id to 0
583 $query[] = "UPDATE Port SET iif_id = 2 WHERE type = 1208";
584 $query[] = "UPDATE Port SET iif_id = 3 WHERE type = 1078";
585 $query[] = "UPDATE Port SET iif_id = 4 WHERE type = 1077";
586 $query[] = "UPDATE Port SET iif_id = 5 WHERE type = 1079";
587 $query[] = "UPDATE Port SET iif_id = 6 WHERE type = 1080";
588 $query[] = "UPDATE Port SET iif_id = 7 WHERE type = 1081";
589 $query[] = "UPDATE Port SET iif_id = 8 WHERE type = 1082";
590 $query[] = "UPDATE Port SET iif_id = 9 WHERE type = 1084";
591 $query[] = "UPDATE Port SET iif_id = 1 WHERE iif_id = 0";
592 $query[] = 'ALTER TABLE Port ADD UNIQUE `object_iif_oif_name` (object_id, iif_id, type, name)';
593 $query[] = 'ALTER TABLE Port DROP KEY `per_object`';
594 $base1000 = array (24, 34, 1202, 1203, 1204, 1205, 1206, 1207);
595 $base10000 = array (30, 35, 36, 37, 38, 39, 40);
596 $PICdata = array
597 (
598 1 => array (16, 19, 24, 29, 31, 33, 446, 681, 682, 1322),
599 2 => array (1208, 1195, 1196, 1197, 1198, 1199, 1200, 1201),
600 3 => array_merge (array (1078), $base1000),
601 4 => array_merge (array (1077), $base1000),
602 5 => array_merge (array (1079), $base10000),
603 6 => array_merge (array (1080), $base10000),
604 7 => array_merge (array (1081), $base10000),
605 8 => array_merge (array (1082), $base10000),
606 9 => array_merge (array (1084), $base10000),
607 );
608 // make sure all IIF/OIF pairs referenced from Port exist in PortInterfaceCompat before enabling FK
609 // iif_id doesn't exist at this point
610 $result = $dbxlink->query ('SELECT DISTINCT type FROM Port WHERE type NOT IN (1208, 1078, 1077, 1079, 1080, 1081, 1082, 1084)');
611 while ($row = $result->fetch (PDO::FETCH_ASSOC))
612 if (FALSE === array_search ($row['type'], $PICdata[1]))
613 array_push ($PICdata[1], $row['type']);
614 unset ($result);
615 foreach ($PICdata as $iif_id => $oif_ids)
616 foreach ($oif_ids as $oif_id)
617 $query[] = "INSERT INTO PortInterfaceCompat (iif_id, oif_id) VALUES (${iif_id}, ${oif_id})";
618 $query[] = "ALTER TABLE Port ADD CONSTRAINT `Port-FK-iif-oif` FOREIGN KEY (`iif_id`, `type`) REFERENCES `PortInterfaceCompat` (`iif_id`, `oif_id`)";
619 $query[] = 'UPDATE Port SET type = 1322 WHERE type = 16 AND (SELECT objtype_id FROM RackObject WHERE id = object_id) IN (2, 12)';
620 $query[] = "DELETE FROM Config WHERE varname = 'default_port_type'";
621 $query[] = "INSERT INTO Config VALUES ('DEFAULT_PORT_IIF_ID','1','uint','no','no','Default port inner interface ID')";
622 $query[] = "INSERT INTO Config VALUES ('DEFAULT_PORT_OIF_IDS','1=24; 3=1078; 4=1077; 5=1079; 6=1080; 8=1082; 9=1084','string','no','no','Default port outer interface IDs')";
623 $query[] = "INSERT INTO Config VALUES ('IPV4_TREE_RTR_AS_CELL','yes','string','no','no','Show full router info for each network in IPv4 tree view')";
624 $query[] = "UPDATE Chapter SET name = 'PortOuterInterface' WHERE id = 2";
625 // remap refs to duplicate records, which will be discarded (ticket:286)
626 $query[] = 'UPDATE AttributeValue SET uint_value = 147 WHERE uint_value = 1020 AND attr_id = 2';
627 $query[] = 'UPDATE AttributeValue SET uint_value = 377 WHERE uint_value = 1021 AND attr_id = 2';
628 $query[] = 'INSERT INTO AttributeMap (objtype_id, attr_id) VALUES (2, 1), (2, 3), (2, 5)';
629 $query[] = "UPDATE Config SET varvalue = '0.17.5' WHERE varname = 'DB_VERSION'";
630 break;
631 case '0.17.6':
632 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (28,'no','Voice/video hardware')";
633 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1323,1,NULL)";
634 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1323,2,28)";
635 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1323,3,NULL)";
636 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1323,5,NULL)";
637 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('PROXIMITY_RANGE','0','uint','yes','no','Proximity range (0 is current rack only)')";
638 $query[] = "UPDATE Config SET varvalue = '0.17.6' WHERE varname = 'DB_VERSION'";
639 break;
640 case '0.17.7':
641 $query[] = "UPDATE Config SET varvalue = '0.17.7' WHERE varname = 'DB_VERSION'";
642 break;
643 case '0.17.8':
644 $query[] = "ALTER TABLE TagTree DROP COLUMN valid_realm";
645 $query[] = "UPDATE Config SET varvalue = '0.17.8' WHERE varname = 'DB_VERSION'";
646 break;
647 case '0.17.9':
648 $query[] = "ALTER table Config add `is_userdefined` enum('yes','no') NOT NULL default 'no' AFTER `is_hidden`";
649 $query[] = "
650 CREATE TABLE `UserConfig` (
651 `varname` char(32) NOT NULL,
652 `varvalue` char(255) NOT NULL,
653 `user` char(64) NOT NULL,
654 UNIQUE KEY `user_varname` (`user`,`varname`)
655 ) TYPE=InnoDB";
656 $query[] = "UPDATE Config SET is_userdefined = 'yes' WHERE varname IN
657 (
658 'MASSCOUNT',
659 'MAXSELSIZE',
660 'ROW_SCALE',
661 'PORTS_PER_ROW',
662 'IPV4_ADDRS_PER_PAGE',
663 'DEFAULT_RACK_HEIGHT',
664 'DEFAULT_SLB_VS_PORT',
665 'DEFAULT_SLB_RS_PORT',
666 'DETECT_URLS',
667 'RACK_PRESELECT_THRESHOLD',
668 'DEFAULT_IPV4_RS_INSERVICE',
669 'DEFAULT_OBJECT_TYPE',
670 'SHOW_EXPLICIT_TAGS',
671 'SHOW_IMPLICIT_TAGS',
672 'SHOW_AUTOMATIC_TAGS',
673 'IPV4_AUTO_RELEASE',
674 'SHOW_LAST_TAB',
675 'EXT_IPV4_VIEW',
676 'TREE_THRESHOLD',
677 'ADDNEW_AT_TOP',
678 'IPV4_TREE_SHOW_USAGE',
679 'PREVIEW_TEXT_MAXCHARS',
680 'PREVIEW_TEXT_ROWS',
681 'PREVIEW_TEXT_COLS',
682 'PREVIEW_IMAGE_MAXPXS',
683 'VENDOR_SIEVE',
684 'RACKS_PER_ROW'
685 )";
686 $query[] = "UPDATE Config SET varvalue = '0.17.9' WHERE varname = 'DB_VERSION'";
687 break;
688 case '0.17.10':
689 $query[] = "ALTER TABLE MountOperation ADD KEY (object_id)";
690 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('STATIC_FILTER','yes','string','no','no','yes','Enable Filter Caching');";
691 $query[] = "UPDATE Config SET varvalue = '0.17.10' WHERE varname = 'DB_VERSION'";
692 break;
693 case '0.17.11':
694 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('ENABLE_BULKPORT_FORM','yes','string','no','no','yes','Enable \"Bulk Port\" form');";
695 $query[] = "DELETE AttributeValue FROM AttributeValue JOIN Attribute where AttributeValue.attr_id = Attribute.id AND Attribute.type = 'dict' AND AttributeValue.uint_value = 0";
696 $query[] = "UPDATE Config SET varvalue = '0.17.11' WHERE varname = 'DB_VERSION'";
697 break;
698 case '0.18.0':
699 $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')";
700 $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')";
701 $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')";
702 $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')";
703 $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')";
704 $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')";
705 $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')";
706 $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')";
707 $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')";
708 $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')";
709 $query[] = "ALTER TABLE IPv4Network ENGINE=InnoDB";
710 $query[] = "SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0";
711 $query[] = "
712 CREATE TABLE `CachedPAV` (
713 `object_id` int(10) unsigned NOT NULL,
714 `port_name` char(255) NOT NULL,
715 `vlan_id` int(10) unsigned NOT NULL default '0',
716 PRIMARY KEY (`object_id`,`port_name`,`vlan_id`),
717 KEY `vlan_id` (`vlan_id`),
718 CONSTRAINT `CachedPAV-FK-object-port` FOREIGN KEY (`object_id`, `port_name`) REFERENCES `CachedPVM` (`object_id`, `port_name`) ON DELETE CASCADE,
719 CONSTRAINT `CachedPAV-FK-vlan_id` FOREIGN KEY (`vlan_id`) REFERENCES `VLANValidID` (`vlan_id`)
720 ) ENGINE=InnoDB
721 ";
722 $query[] = "
723 CREATE TABLE `CachedPNV` (
724 `object_id` int(10) unsigned NOT NULL,
725 `port_name` char(255) NOT NULL,
726 `vlan_id` int(10) unsigned NOT NULL default '0',
727 PRIMARY KEY (`object_id`,`port_name`,`vlan_id`),
728 UNIQUE KEY `port_id` (`object_id`,`port_name`),
729 CONSTRAINT `CachedPNV-FK-compound` FOREIGN KEY (`object_id`, `port_name`, `vlan_id`) REFERENCES `CachedPAV` (`object_id`, `port_name`, `vlan_id`) ON DELETE CASCADE
730 ) ENGINE=InnoDB
731 ";
732 $query[] = "
733 CREATE TABLE `CachedPVM` (
734 `object_id` int(10) unsigned NOT NULL,
735 `port_name` char(255) NOT NULL,
736 `vlan_mode` enum('access','trunk') NOT NULL default 'access',
737 PRIMARY KEY (`object_id`,`port_name`),
738 CONSTRAINT `CachedPVM-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE
739 ) ENGINE=InnoDB
740 ";
741 $query[] = "
742 CREATE TABLE `PortAllowedVLAN` (
743 `object_id` int(10) unsigned NOT NULL,
744 `port_name` char(255) NOT NULL,
745 `vlan_id` int(10) unsigned NOT NULL default '0',
746 PRIMARY KEY (`object_id`,`port_name`,`vlan_id`),
747 KEY `vlan_id` (`vlan_id`),
748 CONSTRAINT `PortAllowedVLAN-FK-object-port` FOREIGN KEY (`object_id`, `port_name`) REFERENCES `PortVLANMode` (`object_id`, `port_name`) ON DELETE CASCADE,
749 CONSTRAINT `PortAllowedVLAN-FK-vlan_id` FOREIGN KEY (`vlan_id`) REFERENCES `VLANValidID` (`vlan_id`)
750 ) ENGINE=InnoDB
751 ";
752 $query[] = "
753 CREATE TABLE `PortNativeVLAN` (
754 `object_id` int(10) unsigned NOT NULL,
755 `port_name` char(255) NOT NULL,
756 `vlan_id` int(10) unsigned NOT NULL default '0',
757 PRIMARY KEY (`object_id`,`port_name`,`vlan_id`),
758 UNIQUE KEY `port_id` (`object_id`,`port_name`),
759 CONSTRAINT `PortNativeVLAN-FK-compound` FOREIGN KEY (`object_id`, `port_name`, `vlan_id`) REFERENCES `PortAllowedVLAN` (`object_id`, `port_name`, `vlan_id`) ON DELETE CASCADE
760 ) ENGINE=InnoDB
761 ";
762 $query[] = "
763 CREATE TABLE `PortVLANMode` (
764 `object_id` int(10) unsigned NOT NULL,
765 `port_name` char(255) NOT NULL,
766 `vlan_mode` enum('access','trunk') NOT NULL default 'access',
767 PRIMARY KEY (`object_id`,`port_name`),
768 CONSTRAINT `PortVLANMode-FK-object-port` FOREIGN KEY (`object_id`, `port_name`) REFERENCES `CachedPVM` (`object_id`, `port_name`)
769 ) ENGINE=InnoDB
770 ";
771 $query[] = "
772 CREATE TABLE `VLANDescription` (
773 `domain_id` int(10) unsigned NOT NULL,
774 `vlan_id` int(10) unsigned NOT NULL default '0',
775 `vlan_type` enum('ondemand','compulsory','alien') NOT NULL default 'ondemand',
776 `vlan_descr` char(255) default NULL,
777 PRIMARY KEY (`domain_id`,`vlan_id`),
778 KEY `vlan_id` (`vlan_id`),
779 CONSTRAINT `VLANDescription-FK-domain_id` FOREIGN KEY (`domain_id`) REFERENCES `VLANDomain` (`id`) ON DELETE CASCADE,
780 CONSTRAINT `VLANDescription-FK-vlan_id` FOREIGN KEY (`vlan_id`) REFERENCES `VLANValidID` (`vlan_id`)
781 ) ENGINE=InnoDB
782 ";
783 $query[] = "
784 CREATE TABLE `VLANDomain` (
785 `id` int(10) unsigned NOT NULL auto_increment,
786 `description` char(255) default NULL,
787 PRIMARY KEY (`id`),
788 UNIQUE KEY `description` (`description`)
789 ) ENGINE=InnoDB
790 ";
791 $query[] = "
792 CREATE TABLE `VLANIPv4` (
793 `domain_id` int(10) unsigned NOT NULL,
794 `vlan_id` int(10) unsigned NOT NULL,
795 `ipv4net_id` int(10) unsigned NOT NULL,
796 UNIQUE KEY `network-domain` (`ipv4net_id`,`domain_id`),
797 KEY `VLANIPv4-FK-compound` (`domain_id`,`vlan_id`),
798 CONSTRAINT `VLANIPv4-FK-compound` FOREIGN KEY (`domain_id`, `vlan_id`) REFERENCES `VLANDescription` (`domain_id`, `vlan_id`) ON DELETE CASCADE,
799 CONSTRAINT `VLANIPv4-FK-ipv4net_id` FOREIGN KEY (`ipv4net_id`) REFERENCES `IPv4Network` (`id`) ON DELETE CASCADE
800 ) ENGINE=InnoDB
801 ";
802 $query[] = "
803 CREATE TABLE `VLANSTRule` (
804 `vst_id` int(10) unsigned NOT NULL,
805 `rule_no` int(10) unsigned NOT NULL,
806 `port_pcre` char(255) NOT NULL,
807 `port_role` enum('access','trunk','uplink','downlink','none') NOT NULL default 'none',
808 `wrt_vlans` char(255) default NULL,
809 `description` char(255) default NULL,
810 UNIQUE KEY `vst-rule` (`vst_id`,`rule_no`),
811 CONSTRAINT `VLANSTRule-FK-vst_id` FOREIGN KEY (`vst_id`) REFERENCES `VLANSwitchTemplate` (`id`) ON DELETE CASCADE
812 ) ENGINE=InnoDB
813 ";
814 $query[] = "
815 CREATE TABLE `VLANSwitch` (
816 `object_id` int(10) unsigned NOT NULL,
817 `domain_id` int(10) unsigned NOT NULL,
818 `template_id` int(10) unsigned NOT NULL,
819 `mutex_rev` int(10) unsigned NOT NULL default '0',
820 `out_of_sync` enum('yes','no') NOT NULL default 'yes',
821 `last_errno` int(10) unsigned NOT NULL default '0',
822 `last_change` timestamp NOT NULL default '0000-00-00 00:00:00',
823 `last_push_started` timestamp NOT NULL default '0000-00-00 00:00:00',
824 `last_push_finished` timestamp NOT NULL default '0000-00-00 00:00:00',
825 `last_error_ts` timestamp NOT NULL default '0000-00-00 00:00:00',
826 UNIQUE KEY `object_id` (`object_id`),
827 KEY `domain_id` (`domain_id`),
828 KEY `template_id` (`template_id`),
829 KEY `out_of_sync` (`out_of_sync`),
830 KEY `last_errno` (`last_errno`),
831 CONSTRAINT `VLANSwitch-FK-domain_id` FOREIGN KEY (`domain_id`) REFERENCES `VLANDomain` (`id`),
832 CONSTRAINT `VLANSwitch-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`),
833 CONSTRAINT `VLANSwitch-FK-template_id` FOREIGN KEY (`template_id`) REFERENCES `VLANSwitchTemplate` (`id`)
834 ) ENGINE=InnoDB
835 ";
836 $query[] = "
837 CREATE TABLE `VLANSwitchTemplate` (
838 `id` int(10) unsigned NOT NULL auto_increment,
839 `max_local_vlans` int(10) unsigned default NULL,
840 `description` char(255) default NULL,
841 PRIMARY KEY (`id`),
842 UNIQUE KEY `description` (`description`)
843 ) ENGINE=InnoDB
844 ";
845 $query[] = "
846 CREATE TABLE `VLANValidID` (
847 `vlan_id` int(10) unsigned NOT NULL default '1',
848 PRIMARY KEY (`vlan_id`)
849 ) ENGINE=InnoDB
850 ";
851 $query[] = "SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS";
852 for ($i = 1; $i <= 4094; $i++)
853 $query[] = "INSERT INTO VLANValidID (vlan_id) VALUES (${i})";
854 $query[] = "UPDATE Config SET varvalue = '0.18.0' WHERE varname = 'DB_VERSION'";
855 break;
856 case '0.18.1':
857 $query[] = "ALTER TABLE Atom ENGINE=InnoDB";
858 $query[] = "ALTER TABLE AttributeMap ENGINE=InnoDB";
859 $query[] = "ALTER TABLE Config ENGINE=InnoDB";
860 $query[] = "ALTER TABLE IPv4Address ENGINE=InnoDB";
861 $query[] = "ALTER TABLE IPv4Allocation ENGINE=InnoDB";
862 $query[] = "ALTER TABLE Molecule ENGINE=InnoDB";
863 $query[] = "ALTER TABLE MountOperation ENGINE=InnoDB";
864 $query[] = "ALTER TABLE PortCompat ENGINE=InnoDB";
865 $query[] = "ALTER TABLE Rack ENGINE=InnoDB";
866 $query[] = "ALTER TABLE RackHistory ENGINE=InnoDB";
867 $query[] = "ALTER TABLE RackObjectHistory ENGINE=InnoDB";
868 $query[] = "ALTER TABLE RackRow ENGINE=InnoDB";
869 $query[] = "ALTER TABLE RackSpace ENGINE=InnoDB";
870 $query[] = "ALTER TABLE Script ENGINE=InnoDB";
871 $query[] = "ALTER TABLE AttributeValue DROP FOREIGN KEY `AttributeValue-FK-object_id`";
872 $query[] = "ALTER TABLE AttributeValue ADD CONSTRAINT `AttributeValue-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE";
873 $query[] = "ALTER TABLE RackObjectHistory ADD KEY (id)";
874 $query[] = "ALTER TABLE RackObjectHistory ADD CONSTRAINT `RackObjectHistory-FK-object_id` FOREIGN KEY (id) REFERENCES `RackObject` (`id`) ON DELETE CASCADE";
875 $query[] = "ALTER TABLE MountOperation ADD CONSTRAINT `MountOperation-FK-object_id` FOREIGN KEY (object_id) REFERENCES RackObject (id) ON DELETE CASCADE";
876 $query[] = "ALTER TABLE RackSpace ADD CONSTRAINT `RackSpace-FK-object_id` FOREIGN KEY (object_id) REFERENCES RackObject (id) ON DELETE CASCADE";
877 $query[] = "ALTER TABLE Link DROP FOREIGN KEY `Link-FK-a`";
878 $query[] = "ALTER TABLE Link ADD CONSTRAINT `Link-FK-a` FOREIGN KEY (`porta`) REFERENCES `Port` (`id`) ON DELETE CASCADE";
879 $query[] = "ALTER TABLE Link DROP FOREIGN KEY `Link-FK-b`";
880 $query[] = "ALTER TABLE Link ADD CONSTRAINT `Link-FK-b` FOREIGN KEY (`portb`) REFERENCES `Port` (`id`) ON DELETE CASCADE";
881 $query[] = "ALTER TABLE Port DROP FOREIGN KEY `Port-FK-object_id`";
882 $query[] = "ALTER TABLE Port ADD CONSTRAINT `Port-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE";
883 $query[] = "ALTER TABLE AttributeMap MODIFY `chapter_id` int(10) unsigned default NULL";
884 $query[] = "ALTER TABLE IPv4Address MODIFY `ip` int(10) unsigned NOT NULL default '0'";
885 $query[] = "ALTER TABLE IPv4Address MODIFY `name` char(255) NOT NULL default ''";
886 $query[] = "ALTER TABLE IPv4Allocation MODIFY `object_id` int(10) unsigned NOT NULL default '0'";
887 $query[] = "ALTER TABLE IPv4Allocation MODIFY `ip` int(10) unsigned NOT NULL default '0'";
888 $query[] = "ALTER TABLE IPv4Allocation MODIFY `name` char(255) NOT NULL default ''";
889 $query[] = "ALTER TABLE IPv4NAT MODIFY `object_id` int(10) unsigned NOT NULL default '0'";
890 $query[] = "ALTER TABLE IPv4NAT MODIFY `proto` enum('TCP','UDP') NOT NULL default 'TCP'";
891 $query[] = "ALTER TABLE IPv4NAT MODIFY `localip` int(10) unsigned NOT NULL default '0'";
892 $query[] = "ALTER TABLE IPv4NAT MODIFY `localport` smallint(5) unsigned NOT NULL default '0'";
893 $query[] = "ALTER TABLE IPv4NAT MODIFY `remoteip` int(10) unsigned NOT NULL default '0'";
894 $query[] = "ALTER TABLE IPv4NAT MODIFY `remoteport` smallint(5) unsigned NOT NULL default '0'";
895 $query[] = "ALTER TABLE IPv4Network MODIFY `ip` int(10) unsigned NOT NULL default '0'";
896 $query[] = "ALTER TABLE IPv4Network MODIFY `mask` int(10) unsigned NOT NULL default '0'";
897 $query[] = "ALTER TABLE Link MODIFY `porta` int(10) unsigned NOT NULL default '0'";
898 $query[] = "ALTER TABLE Link MODIFY `portb` int(10) unsigned NOT NULL default '0'";
899 $query[] = "ALTER TABLE MountOperation MODIFY `object_id` int(10) unsigned NOT NULL default '0'";
900 $query[] = "ALTER TABLE MountOperation MODIFY `ctime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP";
901 $query[] = "ALTER TABLE Port MODIFY `object_id` int(10) unsigned NOT NULL default '0'";
902 $query[] = "ALTER TABLE Port MODIFY `name` char(255) NOT NULL default ''";
903 $query[] = "ALTER TABLE Port MODIFY `type` int(10) unsigned NOT NULL default '0'";
904 $query[] = "ALTER TABLE PortCompat MODIFY `type1` int(10) unsigned NOT NULL default '0'";
905 $query[] = "ALTER TABLE PortCompat MODIFY `type2` int(10) unsigned NOT NULL default '0'";
906 $query[] = "ALTER TABLE RackHistory MODIFY `ctime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP";
907 $query[] = "ALTER TABLE RackObjectHistory MODIFY `ctime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP";
908 $query[] = "ALTER TABLE TagStorage MODIFY `tag_id` int(10) unsigned NOT NULL default '0'";
909 $query[] = "ALTER TABLE UserAccount MODIFY `user_name` char(64) NOT NULL default ''";
910 $query[] = "UPDATE Config SET varvalue = '0.18.1' WHERE varname = 'DB_VERSION'";
911 break;
912 case '0.18.2':
913 $query[] = "ALTER TABLE Rack ADD CONSTRAINT `Rack-FK-row_id` FOREIGN KEY (row_id) REFERENCES RackRow (id)";
914 $query[] = "ALTER TABLE RackRow ADD UNIQUE KEY `name` (name)";
915 $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')";
916 $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')";
917 $query[] = "UPDATE Config SET varvalue = '0.18.2' WHERE varname = 'DB_VERSION'";
918 break;
919 case '0.18.3':
920 $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'";
921 $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)')";
922 $query[] = "UPDATE Config SET varvalue = '0.18.3' WHERE varname = 'DB_VERSION'";
923 break;
924 case '0.18.4':
925 $query[] = "ALTER TABLE VLANSTRule MODIFY port_role enum('access','trunk','anymode','uplink','downlink','none') NOT NULL default 'none'";
926 $query[] = "UPDATE Config SET varvalue = '0.18.4' WHERE varname = 'DB_VERSION'";
927 break;
928 case '0.18.5':
929 $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')";
930 $query[] = "ALTER TABLE `IPv4LB` ADD COLUMN `prio` int(10) unsigned DEFAULT NULL AFTER `vs_id`";
931 $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')";
932 $query[] = "UPDATE Config SET varvalue = '0.18.5' WHERE varname = 'DB_VERSION'";
933 break;
934 case '0.18.6':
935 $query[] = "UPDATE Config SET varvalue = '0.18.6' WHERE varname = 'DB_VERSION'";
936 break;
937 case '0.18.7':
938 $query[] = "UPDATE Config SET varvalue = '0.18.7' WHERE varname = 'DB_VERSION'";
939 break;
940 case '0.19.0':
941 $query[] = 'ALTER TABLE `File` ADD `thumbnail` LONGBLOB NULL AFTER `atime`';
942 $query[] = "
943 CREATE TABLE `IPv6Address` (
944 `ip` binary(16) NOT NULL,
945 `name` char(255) NOT NULL default '',
946 `reserved` enum('yes','no') default NULL,
947 PRIMARY KEY (`ip`)
948 ) ENGINE=InnoDB
949 ";
950 $query[] = "
951 CREATE TABLE `IPv6Allocation` (
952 `object_id` int(10) unsigned NOT NULL default '0',
953 `ip` binary(16) NOT NULL,
954 `name` char(255) NOT NULL default '',
955 `type` enum('regular','shared','virtual','router') default NULL,
956 PRIMARY KEY (`object_id`,`ip`),
957 CONSTRAINT `IPv6Allocation-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE
958 ) ENGINE=InnoDB
959 ";
960 $query[] = "
961 CREATE TABLE `IPv6Network` (
962 `id` int(10) unsigned NOT NULL auto_increment,
963 `ip` binary(16) NOT NULL,
964 `mask` int(10) unsigned NOT NULL,
965 `last_ip` binary(16) NOT NULL,
966 `name` char(255) default NULL,
967 `comment` text,
968 PRIMARY KEY (`id`),
969 UNIQUE KEY `ip` (`ip`,`mask`)
970 ) ENGINE=InnoDB
971 ";
972 $query[] = "
973 CREATE TABLE `VLANIPv6` (
974 `domain_id` int(10) unsigned NOT NULL,
975 `vlan_id` int(10) unsigned NOT NULL,
976 `ipv6net_id` int(10) unsigned NOT NULL,
977 UNIQUE KEY `network-domain` (`ipv6net_id`,`domain_id`),
978 KEY `VLANIPv6-FK-compound` (`domain_id`,`vlan_id`),
979 CONSTRAINT `VLANIPv6-FK-compound` FOREIGN KEY (`domain_id`, `vlan_id`) REFERENCES `VLANDescription` (`domain_id`, `vlan_id`) ON DELETE CASCADE,
980 CONSTRAINT `VLANIPv6-FK-ipv6net_id` FOREIGN KEY (`ipv6net_id`) REFERENCES `IPv6Network` (`id`) ON DELETE CASCADE
981 ) ENGINE=InnoDB
982 ";
983 $query[] = "
984 CREATE TABLE IF NOT EXISTS `ObjectLog` (
985 `id` int(10) NOT NULL AUTO_INCREMENT,
986 `object_id` int(10) NOT NULL,
987 `user` varchar(64) NOT NULL,
988 `date` datetime NOT NULL,
989 `content` text NOT NULL,
990 PRIMARY KEY (`id`)
991 ) ENGINE=InnoDB
992 ";
993 # Now we have the same structure of ObjectLog table, which objectlog.php
994 # could have left. Subsequent column updates will handle any existing data.
995 $query[] = "ALTER TABLE ObjectLog MODIFY COLUMN `id` int(10) unsigned NOT NULL AUTO_INCREMENT";
996 $query[] = "ALTER TABLE ObjectLog MODIFY COLUMN `object_id` int(10) unsigned NOT NULL";
997 $query[] = "ALTER TABLE ObjectLog MODIFY COLUMN `user` char(64) NOT NULL";
998 $query[] = "ALTER TABLE ObjectLog ADD KEY `object_id` (`object_id`)";
999 $query[] = "ALTER TABLE ObjectLog ADD KEY `date` (`date`)";
1000 $query[] = "ALTER TABLE ObjectLog ADD CONSTRAINT `ObjectLog-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE";
1001 # Now it's the way 0.19.0 is expecting it to be.
1002 $query[] = "
1003 CREATE TABLE `ObjectParentCompat` (
1004 `parent_objtype_id` int(10) unsigned NOT NULL,
1005 `child_objtype_id` int(10) unsigned NOT NULL,
1006 UNIQUE KEY `parent_child` (`parent_objtype_id`,`child_objtype_id`)
1007 ) ENGINE=InnoDB
1008 ";
1009 $query[] = "
1010 CREATE TABLE `EntityLink` (
1011 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
1012 `parent_entity_type` enum('ipv4net','ipv4rspool','ipv4vs','ipv6net','object','rack','user') NOT NULL,
1013 `parent_entity_id` int(10) unsigned NOT NULL,
1014 `child_entity_type` enum('file','object') NOT NULL,
1015 `child_entity_id` int(10) unsigned NOT NULL,
1016 PRIMARY KEY (`id`),
1017 UNIQUE KEY `EntityLink-unique` (`parent_entity_type`,`parent_entity_id`,`child_entity_type`,`child_entity_id`)
1018 ) ENGINE=InnoDB
1019 ";
1020 $query[] = "ALTER TABLE `TagStorage` CHANGE COLUMN `entity_realm` `entity_realm` ENUM('file','ipv4net','ipv4vs','ipv4rspool','object','rack','user','ipv6net') NOT NULL DEFAULT 'object' FIRST";
1021 $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`";
1022 $query[] = 'ALTER TABLE Link ADD COLUMN cable char(64) NULL AFTER portb';
1023 $query[] = 'ALTER TABLE RackSpace ADD CONSTRAINT `RackSpace-FK-rack_id` FOREIGN KEY (rack_id) REFERENCES Rack (id)';
1024 $query[] = "ALTER TABLE `IPv4Allocation` ADD CONSTRAINT `IPv4Allocation-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE";
1025 $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')";
1026 $query[] = "ALTER TABLE `VLANSwitchTemplate` ADD COLUMN `mutex_rev` int(10) NOT NULL AFTER `id`";
1027 $query[] = "ALTER TABLE `VLANSwitchTemplate` ADD COLUMN `saved_by` char(64) NOT NULL AFTER `description`";
1028 $query[] = "INSERT INTO `Attribute` (`id`, `type`, `name`) VALUES (26,'dict','Hypervisor')";
1029 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (29,'no','Yes/No')";
1030 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (30,'no','network chassis models')";
1031 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (31,'no','server chassis models')";
1032 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (32,'no','virtual switch models')";
1033 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (33,'no','virtual switch OS type')";
1034 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (4,26,29)";
1035 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1502,1,NULL)";
1036 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1502,2,31)";
1037 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1502,3,NULL)";
1038 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1502,14,NULL)";
1039 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1502,20,NULL)";
1040 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1502,21,NULL)";
1041 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1502,22,NULL)";
1042 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,1,NULL)";
1043 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,2,30)";
1044 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,3,NULL)";
1045 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,4,14)";
1046 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,5,NULL)";
1047 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,14,NULL)";
1048 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,16,NULL)";
1049 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,17,NULL)";
1050 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,18,NULL)";
1051 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,20,NULL)";
1052 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,21,NULL)";
1053 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,22,NULL)";
1054 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,24,NULL)";
1055 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1504,3,NULL)";
1056 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1504,4,13)";
1057 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1504,14,NULL)";
1058 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1504,24,NULL)";
1059 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1505,14,NULL)";
1060 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1506,14,NULL)";
1061 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1506,17,NULL)";
1062 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1506,18,NULL)";
1063 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,1,NULL)";
1064 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,2,32)";
1065 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,3,NULL)";
1066 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,4,33)";
1067 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,5,NULL)";
1068 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,14,NULL)";
1069 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,20,NULL)";
1070 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,21,NULL)";
1071 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,22,NULL)";
1072 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (3,13)";
1073 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (4,1504)";
1074 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (4,1507)";
1075 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1502,4)";
1076 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1503,8)";
1077 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1505,4)";
1078 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1505,1504)";
1079 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1505,1506)";
1080 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1505,1507)";
1081 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1506,1504)";
1082 $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')";
1083 $query[] = "UPDATE Config SET varvalue = CONCAT(varvalue, ' or {\$typeid_1502} or {\$typeid_1503} or {\$typeid_1504} or {\$typeid_1507}') WHERE varname = 'IPV4OBJ_LISTSRC'";
1084 $query[] = "UPDATE Config SET varvalue = '8' WHERE varname = 'MASSCOUNT'";
1085 $query[] = "UPDATE RackObject SET label = NULL WHERE label = ''";
1086 // Move barcode data so the column can be dropped
1087 $result = $dbxlink->query ('SELECT id, objtype_id, barcode FROM RackObject WHERE barcode IS NOT NULL');
1088 $rows = $result->fetchAll (PDO::FETCH_ASSOC);
1089 unset ($result);
1090 foreach ($rows as $row)
1091 {
1092 // Determine if this object type has the 'OEM S/N 1' attribute associated with it, and if it's set
1093 $sn_query = "SELECT (SELECT COUNT(*) FROM AttributeMap WHERE objtype_id=${row['objtype_id']} AND attr_id=1) AS AM_count, ";
1094 $sn_query .= "(SELECT COUNT(*) FROM AttributeValue WHERE object_id=${row['id']} AND attr_id=1) AS AV_count";
1095 $sn_result = $dbxlink->query ($sn_query);
1096 $sn_row = $sn_result->fetch (PDO::FETCH_ASSOC);
1097 if ($sn_row['AM_count'] == 1 && $sn_row['AV_count'] == 0)
1098 {
1099 // 'OEM S/N 1' attribute is mapped to this object type, but it is not set. Good!
1100 // Copy the barcode value to the attribute.
1101 $query[] = "INSERT INTO AttributeValue (`object_id`, `attr_id`, `string_value`) VALUES (${row['id']}, 1, '${row['barcode']}')";
1102 }
1103 else
1104 {
1105 // Some other set of circumstances. Not as good!
1106 // Copy the barcode value to a new ObjectLog record.
1107 $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']}')";
1108 }
1109 unset ($sn_query, $sn_result, $sn_row);
1110 }
1111 $query[] = 'ALTER TABLE RackObject DROP COLUMN `barcode`';
1112 $query[] = 'ALTER TABLE RackObjectHistory DROP COLUMN `barcode`';
1113 $query[] = 'ALTER TABLE `VLANSwitchTemplate` DROP COLUMN `max_local_vlans`';
1114 $query[] = "UPDATE Config SET varvalue = '0.19.0' WHERE varname = 'DB_VERSION'";
1115 break;
1116 case '0.19.1':
1117 $query[] = "ALTER TABLE `Config` CHANGE COLUMN `varvalue` `varvalue` text NOT NULL";
1118 $query[] = "ALTER TABLE `UserConfig` CHANGE COLUMN `varvalue` `varvalue` text NOT NULL";
1119 $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')";
1120 $result = $dbxlink->query ("SHOW TABLES LIKE 'Objectlog'");
1121 $rows = $result->fetchAll (PDO::FETCH_ASSOC);
1122 unset ($result);
1123 if (count ($rows))
1124 {
1125 # Now the ObjectLog merge... again, because the original table is named
1126 # "Objectlog". The job is to merge contents of Objectlog and ObjectLog
1127 # into the latter.
1128 $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)";
1129 $query[] = "DELETE FROM Objectlog WHERE object_id IN(SELECT id FROM RackObject)";
1130 # Don't delete the old table, if the merge wasn't exhaustive.
1131 $result = $dbxlink->query ('SELECT COUNT(*) AS c FROM Objectlog WHERE object_id NOT IN(SELECT id FROM RackObject)');
1132 $row = $result->fetch (PDO::FETCH_ASSOC);
1133 unset ($result);
1134 if ($row['c'] == 0)
1135 $query[] = 'DROP TABLE Objectlog';
1136 else
1137 $query[] = 'ALTER TABLE Objectlog RENAME TO Objectlog_old_unmerged';
1138 }
1139 $query[] = "UPDATE Config SET varvalue = '0.19.1' WHERE varname = 'DB_VERSION'";
1140 break;
1141 case '0.19.2':
1142 $query[] = "ALTER TABLE IPv4Allocation ADD KEY `ip` (`ip`)";
1143 $query[] = "ALTER TABLE IPv6Allocation ADD KEY `ip` (`ip`)";
1144 $query[] = "ALTER TABLE IPv4VS ADD KEY `vip` (`vip`)";
1145 $query[] = "ALTER TABLE IPv4RS ADD KEY `rsip` (`rsip`)";
1146 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (34,'no','power supply chassis models')";
1147 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (35,'no','power supply models')";
1148 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1397,1,NULL)";
1149 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1397,2,34)";
1150 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1397,14,NULL)";
1151 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1397,21,NULL)";
1152 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1397,22,NULL)";
1153 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1398,1,NULL)";
1154 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1398,2,35)";
1155 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1398,14,NULL)";
1156 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1398,21,NULL)";
1157 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1398,22,NULL)";
1158 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1397,1398)";
1159 $query[] = "INSERT INTO `PortCompat` (`type1`, `type2`) VALUES (1399,1399)";
1160 $query[] = "INSERT INTO `PortInterfaceCompat` (`iif_id`, `oif_id`) VALUES (1,1399)";
1161 $query[] = "UPDATE Config SET varvalue = CONCAT(varvalue, ' or {\$typeid_1397}') WHERE varname = 'IPV4OBJ_LISTSRC'";
1162 $query[] = "ALTER TABLE AttributeValue ADD KEY `attr_id-uint_value` (`attr_id`,`uint_value`)";
1163 $query[] = "ALTER TABLE AttributeValue ADD KEY `attr_id-string_value` (`attr_id`,`string_value`(12))";
1164 $query[] = "UPDATE Config SET varvalue = '0.19.2' WHERE varname = 'DB_VERSION'";
1165 break;
1166 case '0.19.3':
1167 $query[] = "DELETE FROM RackSpace WHERE object_id IS NULL AND state = 'T'";
1168 $query[] = "UPDATE Config SET varvalue = '0.19.3' WHERE varname = 'DB_VERSION'";
1169 break;
1170 case '0.19.4':
1171 $query[] = "UPDATE Config SET varvalue = '0.19.4' WHERE varname = 'DB_VERSION'";
1172 break;
1173 case '0.19.5':
1174 // Add 'virtual port' to 'virtual port' mapping
1175 $query[] = "INSERT INTO `PortCompat` (`type1`,`type2`) VALUES (1469,1469)";
1176 $query[] = "INSERT INTO `PortInterfaceCompat` (`iif_id`,`oif_id`) VALUES (1,1469)";
1177 $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')";
1178 $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')";
1179 $query[] = "UPDATE Link SET cable = NULL WHERE cable = ''";
1180 $query[] = "ALTER TABLE AttributeValue MODIFY string_value char(255) DEFAULT NULL";
1181 $query[] = "UPDATE Config SET varvalue = '0.19.5' WHERE varname = 'DB_VERSION'";
1182 break;
1183 case '0.19.6':
1184 $query[] = "UPDATE Config SET varvalue = '0.19.6' WHERE varname = 'DB_VERSION'";
1185 break;
1186 case '0.19.7':
1187 # A plain "ALTER TABLE Attribute" can leave AUTO_INCREMENT in an odd
1188 # state, hence the table swap.
1189 $query[] = "
1190 CREATE TABLE `Attribute_new` (
1191 `id` int(10) unsigned NOT NULL auto_increment,
1192 `type` enum('string','uint','float','dict') default NULL,
1193 `name` char(64) default NULL,
1194 PRIMARY KEY (`id`),
1195 UNIQUE KEY `name` (`name`)
1196 ) ENGINE=InnoDB
1197 ";
1198 $query[] = "INSERT INTO Attribute_new SELECT * FROM Attribute";
1199 $query[] = "INSERT INTO Attribute_new VALUES (9999, 'string', 'base MAC address')";
1200 $query[] = "DROP TABLE Attribute";
1201 $query[] = "ALTER TABLE Attribute_new RENAME TO Attribute";
1202 $query[] = "ALTER TABLE AttributeMap ADD KEY (attr_id)";
1203 $query[] = "DELETE FROM AttributeMap WHERE attr_id NOT IN (SELECT id FROM Attribute)";
1204 $query[] = "ALTER TABLE AttributeMap ADD CONSTRAINT `AttributeMap-FK-attr_id` FOREIGN KEY (attr_id) REFERENCES Attribute (id)";
1205 $query[] = "DELETE FROM AttributeValue WHERE attr_id NOT IN (SELECT attr_id FROM AttributeMap)";
1206 $query[] = "ALTER TABLE AttributeValue ADD CONSTRAINT `AttributeValue-FK-attr_id` FOREIGN KEY (attr_id) REFERENCES AttributeMap (attr_id)";
1207 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1506,4)";
1208 $query[] = "INSERT INTO PortInnerInterface (id, iif_name) VALUES (10, 'QSFP+')";
1209 $query[] = "INSERT INTO PortInterfaceCompat VALUES (10, 1588)";
1210 $query[] = "UPDATE Config SET varvalue = '0.19.7' WHERE varname = 'DB_VERSION'";
1211 break;
1212 case '0.19.8':
1213 for ($i = 1424; $i <= 1466; $i++) # CX, then 42 ER channels
1214 $query[] = "INSERT INTO `PortCompat` (`type1`, `type2`) VALUES (${i},${i})";
1215 $query[] = "ALTER TABLE UserAccount ENGINE=InnoDB";
1216 $query[] = "DELETE FROM UserConfig WHERE user NOT IN (SELECT user_name FROM UserAccount)";
1217 $query[] = "ALTER TABLE UserConfig ADD CONSTRAINT `UserConfig-FK-user` FOREIGN KEY (user) REFERENCES UserAccount (user_name) ON DELETE CASCADE";
1218 $query[] = "DELETE FROM UserConfig WHERE varname NOT IN (SELECT varname FROM Config)";
1219 $query[] = "ALTER TABLE UserConfig ADD KEY (varname)";
1220 $query[] = "ALTER TABLE UserConfig ADD CONSTRAINT `UserConfig-FK-varname` FOREIGN KEY (varname) REFERENCES Config (varname) ON DELETE CASCADE";
1221 $query[] = "ALTER TABLE Dictionary ENGINE=InnoDB";
1222 $query[] = "ALTER TABLE Chapter ENGINE=InnoDB";
1223 $query[] = "UPDATE Chapter SET id = 9999 WHERE id = 22";
1224 $query[] = "UPDATE AttributeMap SET chapter_id = 9999 WHERE chapter_id = 22";
1225 $query[] = "UPDATE Dictionary SET chapter_id = 9999 WHERE chapter_id = 22";
1226 $query[] = "DELETE FROM Dictionary WHERE chapter_id NOT IN (SELECT id FROM Chapter)";
1227 $query[] = "ALTER TABLE Dictionary ADD CONSTRAINT `Dictionary-FK-chapter_id` FOREIGN KEY (chapter_id) REFERENCES Chapter (id)";
1228 $query[] = "DELETE FROM AttributeMap WHERE chapter_id NOT IN (SELECT id FROM Chapter)";
1229 $query[] = "ALTER TABLE AttributeMap ADD KEY (chapter_id)";
1230 $query[] = "ALTER TABLE AttributeMap ADD CONSTRAINT `AttributeMap-FK-chapter_id` FOREIGN KEY (chapter_id) REFERENCES Chapter (id)";
1231 $query[] = "
1232 CREATE TABLE `CactiGraph` (
1233 `object_id` int(10) unsigned NOT NULL,
1234 `graph_id` int(10) unsigned NOT NULL,
1235 `caption` char(255) DEFAULT NULL,
1236 PRIMARY KEY (`graph_id`),
1237 KEY `object_id` (`object_id`),
1238 CONSTRAINT `CactiGraph-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE
1239 ) ENGINE=InnoDB;
1240 ";
1241 $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')";
1242 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('CACTI_URL','','string','yes','no','no','Cacti server base URL')";
1243 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('CACTI_USERNAME','','string','yes','no','no','Cacti user account')";
1244 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('CACTI_USERPASS','','string','yes','no','no','Cacti user password')";
1245 $query[] = "UPDATE Config SET varvalue = '0.19.8' WHERE varname = 'DB_VERSION'";
1246 break;
1247 case '0.19.9':
1248 $query[] = "DELETE FROM Config WHERE varname = 'HNDP_RUNNERS_LISTSRC'";
1249 # Dismiss some overly-specific OIF types in favour of more generic counterparts.
1250 $squeeze = array
1251 (
1252 1202 => array # 1000Base-SX
1253 (
1254 25, # 1000Base-SX (SC)
1255 26, # 1000Base-SX (LC)
1256 ),
1257 1204 => array # 1000Base-LX
1258 (
1259 27, # 1000Base-LX (SC)
1260 28, # 1000Base-LX (LC)
1261 ),
1262 1196 => array # 100Base-SX
1263 (
1264 22, # 100Base-SX (SC)
1265 23, # 100Base-SX (LC)
1266 ),
1267 1195 => array # 100Base-FX
1268 (
1269 20, # 100Base-FX (SC)
1270 21, # 100Base-FX (LC)
1271 1083, # 100Base-FX (MT-RJ)
1272 ),
1273 );
1274 foreach ($squeeze as $stays => $leaves)
1275 {
1276 $csv = implode (', ', $leaves);
1277 $query[] = "DELETE FROM PortCompat WHERE type1 IN(${csv}) OR type2 IN(${csv})";
1278 $query[] = "INSERT IGNORE INTO PortInterfaceCompat (iif_id, oif_id) SELECT iif_id, ${stays} FROM Port WHERE type IN (${csv})";
1279 $query[] = "UPDATE Port SET type = ${stays} WHERE type IN(${csv})";
1280 $query[] = "DELETE FROM PortInterfaceCompat WHERE oif_id IN(${csv})";
1281 }
1282 $query[] = "UPDATE Config SET varvalue = '0.19.9' WHERE varname = 'DB_VERSION'";
1283 break;
1284 case '0.19.10':
1285 $query[] = "INSERT INTO `PortCompat` (`type1`, `type2`) VALUES (1603,1603)";
1286 $query[] = "UPDATE Config SET varvalue = '0.19.10' WHERE varname = 'DB_VERSION'";
1287 break;
1288 case '0.19.11':
1289 $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')";
1290 $query[] = "INSERT INTO `Attribute` (`id`,`type`,`name`) VALUES (28,'string','Slot number')";
1291 $query[] = 'INSERT INTO `AttributeMap` (`objtype_id`,`attr_id`,`chapter_id`) VALUES (4,28,NULL)';
1292 $query[] = 'INSERT INTO `AttributeMap` (`objtype_id`,`attr_id`,`chapter_id`) VALUES (8,28,NULL)';
1293 $query[] = 'INSERT INTO `AttributeMap` (`objtype_id`,`attr_id`,`chapter_id`) VALUES (798,28,NULL)';
1294 $query[] = 'INSERT INTO `AttributeMap` (`objtype_id`,`attr_id`,`chapter_id`) VALUES (1055,28,NULL)';
1295 $query[] = 'ALTER TABLE AttributeValue ADD COLUMN object_tid int(10) unsigned NOT NULL default 0 AFTER object_id';
1296 $query[] = 'UPDATE AttributeValue SET object_tid = (SELECT objtype_id FROM RackObject WHERE id = object_id)';
1297 $query[] = 'ALTER TABLE AttributeValue DROP FOREIGN KEY `AttributeValue-FK-attr_id`';
1298 $query[] = 'ALTER TABLE AttributeValue DROP FOREIGN KEY `AttributeValue-FK-object_id`';
1299 $query[] = 'ALTER TABLE AttributeValue ADD KEY `id-tid` (object_id, object_tid)';
1300 $query[] = 'ALTER TABLE AttributeValue ADD KEY `object_tid-attr_id` (`object_tid`,`attr_id`)';
1301 $query[] = 'ALTER TABLE RackObject ADD KEY `id-tid` (id, objtype_id)';
1302 $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';
1303 $query[] = 'ALTER TABLE AttributeValue ADD CONSTRAINT `AttributeValue-FK-map` FOREIGN KEY (`object_tid`, `attr_id`) REFERENCES `AttributeMap` (`objtype_id`, `attr_id`)';
1304 # 0.19.9 did it right, but kept the IDs in the dictionary. This time
1305 # the dictionary is reduced, but the procedure needs to be repeated,
1306 # in case the user had enough time to use the wrong IDs again.
1307 $squeeze = array
1308 (
1309 1202 => array # 1000Base-SX
1310 (
1311 25, # 1000Base-SX (SC)
1312 26, # 1000Base-SX (LC)
1313 ),
1314 1204 => array # 1000Base-LX
1315 (
1316 27, # 1000Base-LX (SC)
1317 28, # 1000Base-LX (LC)
1318 ),
1319 1196 => array # 100Base-SX
1320 (
1321 22, # 100Base-SX (SC)
1322 23, # 100Base-SX (LC)
1323 ),
1324 1195 => array # 100Base-FX
1325 (
1326 20, # 100Base-FX (SC)
1327 21, # 100Base-FX (LC)
1328 1083, # 100Base-FX (MT-RJ)
1329 ),
1330 );
1331 foreach ($squeeze as $stays => $leaves)
1332 {
1333 $csv = implode (', ', $leaves);
1334 $query[] = "DELETE FROM PortCompat WHERE type1 IN(${csv}) OR type2 IN(${csv})";
1335 $query[] = "INSERT IGNORE INTO PortInterfaceCompat (iif_id, oif_id) SELECT iif_id, ${stays} FROM Port WHERE type IN (${csv})";
1336 $query[] = "UPDATE Port SET type = ${stays} WHERE type IN(${csv})";
1337 $query[] = "DELETE FROM PortInterfaceCompat WHERE oif_id IN(${csv})";
1338 }
1339 $query[] = "INSERT INTO `PortCompat` (`type1`, `type2`) VALUES (1642,1642)";
1340 $query[] = 'ALTER TABLE `EntityLink` ADD KEY `EntityLink-compound` (`parent_entity_type`,`child_entity_type`,`child_entity_id`)';
1341 $query[] = "UPDATE Config SET varvalue = '0.19.11' WHERE varname = 'DB_VERSION'";
1342 break;
1343 # Batch 0.19.12 contained minor, but annoying bugs and was modified after the
1344 # release of version 0.19.12 (which should be avoided if possible). The best
1345 # way to resolve this particular case was to recall RackTables-0.19.12.tar.gz
1346 # from the download area and provide RackTables-0.19.13.tar.gz containing the
1347 # modified 0.19.12 batch.
1348 case '0.19.12':
1349 $query[] = "DELETE FROM Config WHERE varname IN('color_F', 'color_A', 'color_U', 'color_T', 'color_Th', 'color_Tw', 'color_Thw')";
1350 $query[] = "INSERT INTO Chapter (id, sticky, name) VALUES (36,'no','serial console server models')";
1351 $query[] = "INSERT INTO AttributeMap (objtype_id, attr_id, chapter_id) VALUES (1644, 1, NULL), (1644, 2, 36), (1644, 3, NULL)";
1352 $query[] = "UPDATE Config SET varvalue = CONCAT(varvalue, '; 10=1588') WHERE varname = 'DEFAULT_PORT_OIF_IDS' AND 0 = INSTR(varvalue, '10=') ";
1353 $query[] = "INSERT INTO PortInterfaceCompat VALUES (10,1663), (10,1664)";
1354 $query[] = "INSERT INTO PortCompat VALUES (1588,1588), (1661,1661), (1663,1663), (1664,1664)";
1355 $query[] = "INSERT INTO PortInnerInterface (id, iif_name) VALUES (11, 'CFP')";
1356 $query[] = "INSERT INTO PortInterfaceCompat (iif_id, oif_id) VALUES (11,1668),(11,1669),(11,1670),(11,1671)";
1357 $query[] = "INSERT INTO PortCompat (type1, type2) VALUES (1668,1668), (1669,1669), (1670,1670), (1671,1671)";
1358 $query[] = "UPDATE Config SET varvalue = CONCAT(varvalue, '; 11=1668') WHERE varname = 'DEFAULT_PORT_OIF_IDS'";
1359 $query[] = "INSERT INTO Chapter (id, sticky, name) VALUES (37, 'no', 'wireless OS type')";
1360 $query[] = "INSERT INTO AttributeMap (objtype_id, attr_id, chapter_id) VALUES (965, 4, 37)";
1361 $query[] = "UPDATE Config SET varvalue = '0.19.12' WHERE varname = 'DB_VERSION'";
1362 break;
1363 case '0.19.13':
1364 // add the date attribute type
1365 $query[] = "ALTER TABLE `Attribute` CHANGE COLUMN `type` `type` enum('string','uint','float','dict','date') DEFAULT NULL";
1366 $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')";
1367 $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')";
1368
1369 // port over existing fields to new date attr type
1370 $query[] = "UPDATE Attribute SET type='date' WHERE id IN (21,22,24)";
1371 $query[] = "UPDATE AttributeValue SET uint_value=UNIX_TIMESTAMP(STR_TO_DATE(string_value, '%m/%d/%Y')) WHERE attr_id IN(21,22,24)";
1372
1373 // some config variables should be configurable in per-user basis
1374 $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')";
1375
1376 $query[] = "UPDATE Config SET varvalue = '0.19.13' WHERE varname = 'DB_VERSION'";
1377 break;
1378 case '0.19.14':
1379 $query[] = "ALTER TABLE AttributeValue CHANGE COLUMN `object_id` `object_id` INT(10) UNSIGNED NOT NULL";
1380 $query[] = "ALTER TABLE AttributeValue CHANGE COLUMN `attr_id` `attr_id` INT(10) UNSIGNED NOT NULL";
1381 $query[] = "ALTER TABLE AttributeValue ADD PRIMARY KEY (`object_id`, `attr_id`), DROP INDEX `object_id`";
1382 $query[] = "ALTER TABLE Dictionary ADD COLUMN `dict_sticky` enum('yes','no') DEFAULT 'no' AFTER `dict_key`";
1383 $query[] = "UPDATE Dictionary SET dict_sticky = 'yes' WHERE dict_key < 50000";
1384 $query[] = "ALTER TABLE Dictionary ADD UNIQUE KEY dict_unique (chapter_id, dict_value, dict_sticky)";
1385 $query[] = "ALTER TABLE Dictionary DROP KEY `chap_to_val`";
1386 $query[] = "UPDATE Config SET varvalue = '0.19.14' WHERE varname = 'DB_VERSION'";
1387 break;
1388 case '0.20.0':
1389 $query[] = "
1390 CREATE TABLE `PortLog` (
1391 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
1392 `port_id` int(10) unsigned NOT NULL,
1393 `date` datetime NOT NULL,
1394 `user` varchar(64) NOT NULL,
1395 `message` text NOT NULL,
1396 PRIMARY KEY (`id`),
1397 KEY `port_id-date` (`port_id`,`date`),
1398 CONSTRAINT `PortLog_ibfk_1` FOREIGN KEY (`port_id`) REFERENCES `Port` (`id`) ON DELETE CASCADE
1399 ) ENGINE=InnoDB;
1400 ";
1401 $query[] = "
1402 CREATE TABLE `IPv4Log` (
1403 `id` int(10) NOT NULL AUTO_INCREMENT,
1404 `ip` int(10) unsigned NOT NULL,
1405 `date` datetime NOT NULL,
1406 `user` varchar(64) NOT NULL,
1407 `message` text NOT NULL,
1408 PRIMARY KEY (`id`),
1409 KEY `ip-date` (`ip`,`date`)
1410 ) ENGINE=InnoDB;
1411 ";
1412 $query[] = "
1413 CREATE TABLE `IPv6Log` (
1414 `id` int(10) NOT NULL AUTO_INCREMENT,
1415 `ip` binary(16) NOT NULL,
1416 `date` datetime NOT NULL,
1417 `user` varchar(64) NOT NULL,
1418 `message` text NOT NULL,
1419 PRIMARY KEY (`id`),
1420 KEY `ip-date` (`ip`,`date`)
1421 ) ENGINE=InnoDB;
1422 ";
1423 $query[] = "ALTER TABLE `FileLink` MODIFY COLUMN `entity_type` ENUM('ipv4net','ipv4rspool','ipv4vs','ipv6net','location','object','rack','user') NOT NULL DEFAULT 'object'";
1424 $query[] = "ALTER TABLE `TagStorage` MODIFY COLUMN `entity_realm` ENUM('file','ipv4net','ipv4rspool','ipv4vs','ipv6net','location','object','rack','user','vst') NOT NULL default 'object'";
1425 $query[] = "ALTER TABLE `TagStorage` ADD COLUMN `user` char(64) DEFAULT NULL, ADD COLUMN `date` datetime DEFAULT NULL";
1426
1427 // Rename object tables and keys, 'name' no longer needs to be unique
1428 $dbxlink->query ('ALTER TABLE `RackObject` RENAME TO `Object`');
1429 $dbxlink->query ('ALTER TABLE `RackObjectHistory` RENAME TO `ObjectHistory`');
1430 $dbxlink->query ('ALTER TABLE `Object` DROP KEY `name`');
1431 $query[] = 'ALTER TABLE `Object` DROP KEY `RackObject_asset_no`';
1432 $query[] = 'ALTER TABLE `Object` ADD UNIQUE KEY `asset_no` (`asset_no`)';
1433 $query[] = 'ALTER TABLE `Object` ADD KEY `type_id` (`objtype_id`,`id`)';
1434 $query[] = 'ALTER TABLE `ObjectHistory` DROP FOREIGN KEY `RackObjectHistory-FK-object_id`';
1435 $query[] = 'ALTER TABLE `ObjectHistory` ADD CONSTRAINT `ObjectHistory-FK-object_id` FOREIGN KEY (`id`) REFERENCES `Object` (`id`) ON DELETE CASCADE';
1436 $query[] = 'ALTER TABLE `RackSpace` DROP FOREIGN KEY `RackSpace-FK-rack_id`';
1437
1438 // Rack height is now an attribute
1439 $query[] = "INSERT INTO `Attribute` (`id`,`type`,`name`) VALUES (27,'uint','Height, units')";
1440 $query[] = 'INSERT INTO `AttributeMap` (`objtype_id`,`attr_id`,`chapter_id`) VALUES (1560,27,NULL)';
1441
1442 // Racks are now sorted using an attribute
1443 $query[] = "INSERT INTO `Attribute` (`id`,`type`,`name`) VALUES (29,'uint','Sort order')";
1444 $query[] = 'INSERT INTO `AttributeMap` (`objtype_id`,`attr_id`,`chapter_id`) VALUES (1560,29,NULL)';
1445
1446 // Relate 'contact person' with locations
1447 $query[] = 'INSERT INTO `AttributeMap` (`objtype_id`,`attr_id`,`chapter_id`) VALUES (1562,14,NULL)';
1448
1449 // Allow relationships between racks/rows/locations
1450 $query[] = "ALTER TABLE `EntityLink` MODIFY COLUMN `parent_entity_type` ENUM('ipv4net','ipv4rspool','ipv4vs','ipv6net','location','object','rack','row','user') NOT NULL";
1451 $query[] = "ALTER TABLE `EntityLink` MODIFY COLUMN `child_entity_type` ENUM('file','location','object','rack','row') NOT NULL";
1452
1453 // Turn rows into objects
1454 $result = $dbxlink->query ('SELECT * FROM RackRow');
1455 $rows = $result->fetchAll (PDO::FETCH_ASSOC);
1456 unset ($result);
1457 foreach ($rows as $row)
1458 {
1459 $prepared = $dbxlink->prepare ('INSERT INTO `Object` (`name`,`objtype_id`) VALUES (?,?)');
1460 $prepared->execute (array($row['name'], 1561));
1461 $row_id = $dbxlink->lastInsertId();
1462 // Turn all racks in this row into objects
1463 $result = $dbxlink->query ("SELECT id, name, height, comment FROM Rack WHERE row_id=${row['id']} ORDER BY name");
1464 $racks = $result->fetchAll (PDO::FETCH_ASSOC);
1465 unset ($result);
1466 $sort_order = 1;
1467 foreach ($racks as $rack)
1468 {
1469 // Add the rack as an object, set the height and sort order as attributes, link the rack to the row,
1470 // update rackspace, tags and files to reflect new rack_id, move history
1471 $prepared = $dbxlink->prepare ('INSERT INTO `Object` (`name`,`objtype_id`,`comment`) VALUES (?,?,?)');
1472 $prepared->execute (array($rack['name'], 1560, $rack['comment']));
1473 $rack_id = $dbxlink->lastInsertId();
1474 $query[] = "INSERT INTO `AttributeValue` (`object_id`,`object_tid`,`attr_id`,`uint_value`) VALUES (${rack_id},1560,27,${rack['height']})";
1475 $query[] = "INSERT INTO `AttributeValue` (`object_id`,`object_tid`,`attr_id`,`uint_value`) VALUES (${rack_id},1560,29,${sort_order})";
1476 $query[] = "INSERT INTO `EntityLink` (`parent_entity_type`,`parent_entity_id`,`child_entity_type`,`child_entity_id`) VALUES ('row',${row_id},'rack',${rack_id})";
1477 $query[] = "UPDATE `RackSpace` SET `rack_id`=${rack_id} WHERE `rack_id`=${rack['id']}";
1478 $query[] = "UPDATE `Atom` SET `rack_id`=${rack_id} WHERE `rack_id`=${rack['id']}";
1479 $query[] = "UPDATE `TagStorage` SET `entity_id`=${rack_id} WHERE `entity_realm`='rack' AND `entity_id`=${rack['id']}";
1480 $query[] = "UPDATE `FileLink` SET `entity_id`=${rack_id} WHERE `entity_type`='rack' AND `entity_id`=${rack['id']}";
1481 $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']}";
1482 $sort_order++;
1483 }
1484 }
1485 $query[] = 'ALTER TABLE `RackSpace` ADD CONSTRAINT `RackSpace-FK-rack_id` FOREIGN KEY (`rack_id`) REFERENCES `Object` (`id`)';
1486 $query[] = 'DROP TABLE `Rack`';
1487 $query[] = 'DROP TABLE `RackRow`';
1488 $query[] = 'DROP TABLE `RackHistory`';
1489 $query[] = "
1490 CREATE TABLE `RackThumbnail` (
1491 `rack_id` int(10) unsigned NOT NULL,
1492 `thumb_data` blob,
1493 UNIQUE KEY `rack_id` (`rack_id`),
1494 CONSTRAINT `RackThumbnail-FK-rack_id` FOREIGN KEY (`rack_id`) REFERENCES `Object` (`id`) ON DELETE CASCADE
1495 ) ENGINE=InnoDB
1496 ";
1497 $query[] = "
1498 CREATE VIEW `Location` AS SELECT O.id, O.name, O.has_problems, O.comment, P.id AS parent_id, P.name AS parent_name
1499 FROM `Object` O
1500 LEFT JOIN (
1501 `Object` P INNER JOIN `EntityLink` EL
1502 ON EL.parent_entity_id = P.id AND P.objtype_id = 1562 AND EL.parent_entity_type = 'location' AND EL.child_entity_type = 'location'
1503 ) ON EL.child_entity_id = O.id
1504 WHERE O.objtype_id = 1562
1505 ";
1506 $query[] = "
1507 CREATE VIEW `Row` AS SELECT O.id, O.name, L.id AS location_id, L.name AS location_name
1508 FROM `Object` O
1509 LEFT JOIN `EntityLink` EL ON O.id = EL.child_entity_id AND EL.parent_entity_type = 'location' AND EL.child_entity_type = 'row'
1510 LEFT JOIN `Object` L ON EL.parent_entity_id = L.id AND L.objtype_id = 1562
1511 WHERE O.objtype_id = 1561
1512 ";
1513 $query[] = "
1514 CREATE VIEW `Rack` AS SELECT O.id, O.name AS name, O.asset_no, O.has_problems, O.comment,
1515 AV_H.uint_value AS height,
1516 AV_S.uint_value AS sort_order,
1517 RT.thumb_data,
1518 R.id AS row_id,
1519 R.name AS row_name
1520 FROM `Object` O
1521 LEFT JOIN `AttributeValue` AV_H ON O.id = AV_H.object_id AND AV_H.attr_id = 27
1522 LEFT JOIN `AttributeValue` AV_S ON O.id = AV_S.object_id AND AV_S.attr_id = 29
1523 LEFT JOIN `RackThumbnail` RT ON O.id = RT.rack_id
1524 LEFT JOIN `EntityLink` EL ON O.id = EL.child_entity_id AND EL.parent_entity_type = 'row' AND EL.child_entity_type = 'rack'
1525 INNER JOIN `Object` R ON R.id = EL.parent_entity_id
1526 WHERE O.objtype_id = 1560
1527 ";
1528 $query[] = "
1529 CREATE VIEW `RackObject` AS SELECT id, name, label, objtype_id, asset_no, has_problems, comment FROM `Object`
1530 WHERE `objtype_id` NOT IN (1560, 1561, 1562)
1531 ";
1532 $query[] = "UPDATE `Chapter` SET `name` = 'ObjectType' WHERE `id` = 1";
1533 $query[] = "DELETE FROM RackSpace WHERE object_id IS NULL AND state = 'T'";
1534
1535 $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')";
1536 $query[] = "UPDATE `Config` SET is_userdefined='yes' WHERE varname='PROXIMITY_RANGE'";
1537 $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')";
1538 $query[] = "ALTER TABLE `IPv4LB` MODIFY `prio` varchar(255) DEFAULT NULL";
1539
1540 $query[] = "ALTER TABLE `IPv4Address` ADD COLUMN `comment` char(255) NOT NULL default '' AFTER `name`";
1541 $query[] = "ALTER TABLE `IPv6Address` ADD COLUMN `comment` char(255) NOT NULL default '' AFTER `name`";
1542
1543 // change IP address format of IPv4VS and IPv4RS tables
1544 convertSLBTablesToBinIPs();
1545
1546 // do not allow NULL allocation type
1547 $query[] = "ALTER TABLE `IPv4Allocation` MODIFY `type` enum('regular','shared','virtual','router') NOT NULL DEFAULT 'regular'";
1548 $query[] = "ALTER TABLE `IPv6Allocation` MODIFY `type` enum('regular','shared','virtual','router') NOT NULL DEFAULT 'regular'";
1549
1550 $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')";
1551
1552 // update some config variables which changed their defaults in this verison
1553 replaceConfigVarValue ('SHOW_LAST_TAB', 'yes');
1554 replaceConfigVarValue ('IPV4_TREE_SHOW_USAGE','no');
1555 replaceConfigVarValue ('IPV4LB_LISTSRC', 'false', '{$typeid_4}');
1556 replaceConfigVarValue ('FILTER_DEFAULT_ANDOR', 'and');
1557 replaceConfigVarValue ('FILTER_SUGGEST_EXTRA', 'yes');
1558 replaceConfigVarValue ('IPV4_TREE_RTR_AS_CELL', 'no');
1559 replaceConfigVarValue ('SSH_OBJS_LISTSRC', 'false', 'none');
1560 replaceConfigVarValue ('TELNET_OBJS_LISTSRC', 'false', 'none');
1561
1562 $query[] = "UPDATE Config SET varvalue = '0.20.0' WHERE varname = 'DB_VERSION'";
1563 break;
1564 case '0.20.1':
1565 // some HW types were moved from the 'Network switch' chapter to the 'Network chassis' chapter
1566 // change the type of affected objects to 'Network chassis'
1567 $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))";
1568
1569 // new 'management interface' object type
1570 $query[] = "INSERT INTO `Chapter` (`id`,`sticky`,`name`) VALUES (38,'no','management interface type')";
1571 $query[] = "INSERT INTO `Attribute` (`id`,`type`,`name`) VALUES (30,'dict','Mgmt type')";
1572 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`,`attr_id`,`chapter_id`) VALUES (1787,3,NULL),(1787,14,NULL),(1787,30,38)";
1573 $query[] = "UPDATE `Config` SET varvalue = CONCAT(varvalue, ' or {\$typeid_1787}') WHERE varname = 'IPV4OBJ_LISTSRC'";
1574
1575 $query[] = "INSERT INTO Config VALUES ('8021Q_EXTSYNC_LISTSRC','false','string','yes','no','no','List source: objects with extended 802.1Q sync')";
1576
1577 // constraints to prevent orphan records
1578 $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";
1579 $query[] = "DELETE FROM `MountOperation` WHERE old_molecule_id NOT IN (SELECT id FROM `Molecule`) OR new_molecule_id NOT IN (SELECT id FROM `Molecule`)";
1580 $query[] = "ALTER TABLE `Atom` ADD CONSTRAINT `Atom-FK-molecule_id` FOREIGN KEY (`molecule_id`) REFERENCES `Molecule` (`id`) ON DELETE CASCADE";
1581 $query[] = "ALTER TABLE `Atom` ADD CONSTRAINT `Atom-FK-rack_id` FOREIGN KEY (`rack_id`) REFERENCES `Object` (`id`) ON DELETE CASCADE";
1582 $query[] = "ALTER TABLE `MountOperation` ADD CONSTRAINT `MountOperation-FK-old_molecule_id` FOREIGN KEY (`old_molecule_id`) REFERENCES `Molecule` (`id`) ON DELETE CASCADE";
1583 $query[] = "ALTER TABLE `MountOperation` ADD CONSTRAINT `MountOperation-FK-new_molecule_id` FOREIGN KEY (`new_molecule_id`) REFERENCES `Molecule` (`id`) ON DELETE CASCADE";
1584 # multiple Cacti servers
1585 $query[] = "
1586 CREATE TABLE `CactiServer` (
1587 `id` int(10) unsigned NOT NULL auto_increment,
1588 `base_url` char(255) DEFAULT NULL,
1589 `username` char(64) DEFAULT NULL,
1590 `password` char(64) DEFAULT NULL,
1591 PRIMARY KEY (`id`)
1592 ) ENGINE=InnoDB
1593 ";
1594 $query[] = "ALTER TABLE CactiGraph ADD COLUMN server_id int(10) unsigned NOT NULL AFTER object_id";
1595
1596 $result = $dbxlink->query ('SELECT COUNT(*) AS cnt FROM CactiGraph');
1597 $row = $result->fetch (PDO::FETCH_ASSOC);
1598 unset ($result);
1599
1600 $result = $dbxlink->query ("SELECT varvalue FROM Config WHERE varname = 'CACTI_URL'");
1601 $cacti_url_row = $result->fetch (PDO::FETCH_ASSOC);
1602 unset ($result);
1603
1604 if ($row['cnt'] != 0 || is_array ($cacti_url_row) && strlen ($cacti_url_row['varvalue']))
1605 {
1606 $query[] = "INSERT INTO CactiServer (id) VALUES (1)";
1607 $query[] = "UPDATE CactiServer SET base_url = (SELECT varvalue FROM Config WHERE varname = 'CACTI_URL') WHERE id = 1";
1608 $query[] = "UPDATE CactiServer SET username = (SELECT varvalue FROM Config WHERE varname = 'CACTI_USERNAME') WHERE id = 1";
1609 $query[] = "UPDATE CactiServer SET password = (SELECT varvalue FROM Config WHERE varname = 'CACTI_USERPASS') WHERE id = 1";
1610 $query[] = "UPDATE CactiGraph SET server_id = 1";
1611 }
1612 $query[] = "ALTER TABLE CactiGraph DROP PRIMARY KEY";
1613 $query[] = "ALTER TABLE CactiGraph ADD PRIMARY KEY (server_id, graph_id)";
1614 $query[] = "ALTER TABLE CactiGraph ADD KEY (graph_id)";
1615 $query[] = "ALTER TABLE CactiGraph ADD CONSTRAINT `CactiGraph-FK-server_id` FOREIGN KEY (server_id) REFERENCES CactiServer (id)";
1616 $query[] = "DELETE FROM Config WHERE varname IN('CACTI_URL', 'CACTI_USERNAME', 'CACTI_USERPASS')";
1617 $query[] = "UPDATE Config SET varvalue = '0.20.1' WHERE varname = 'DB_VERSION'";
1618 break;
1619 case '0.20.2':
1620 $query[] = "ALTER TABLE TagStorage ADD COLUMN tag_is_assignable ENUM('yes', 'no') NOT NULL default 'yes' AFTER tag_id";
1621 $query[] = "ALTER TABLE TagStorage ADD KEY `tag_id-tag_is_assignable` (tag_id, tag_is_assignable)";
1622 $query[] = "ALTER TABLE TagTree ADD COLUMN is_assignable ENUM('yes', 'no') NOT NULL default 'yes' AFTER parent_id";
1623 $query[] = "ALTER TABLE TagTree ADD KEY `id-is_assignable` (id, is_assignable)";
1624 $query[] = "ALTER TABLE TagStorage DROP FOREIGN KEY `TagStorage-FK-tag_id`";
1625 $query[] = "ALTER TABLE TagStorage ADD CONSTRAINT `TagStorage-FK-TagTree` FOREIGN KEY (tag_id, tag_is_assignable) REFERENCES TagTree (id, is_assignable)";
1626 $query[] = "UPDATE UserAccount SET user_realname = NULL WHERE user_realname = ''";
1627 $query[] = "UPDATE Object SET comment = NULL WHERE comment = ''";
1628 $query[] = "
1629 CREATE TABLE `MuninServer` (
1630 `id` int(10) unsigned NOT NULL auto_increment,
1631 `base_url` char(255) DEFAULT NULL,
1632 PRIMARY KEY (`id`)
1633 ) ENGINE=InnoDB
1634 ";
1635 $query[] = "
1636 CREATE TABLE `MuninGraph` (
1637 `object_id` int(10) unsigned NOT NULL,
1638 `server_id` int(10) unsigned NOT NULL,
1639 `graph` char(255) NOT NULL,
1640 `caption` char(255) DEFAULT NULL,
1641 PRIMARY KEY (`object_id`,`server_id`,`graph`),
1642 KEY `server_id` (`server_id`),
1643 KEY `graph` (`graph`),
1644 CONSTRAINT `MuninGraph-FK-server_id` FOREIGN KEY (`server_id`) REFERENCES `MuninServer` (`id`),
1645 CONSTRAINT `MuninGraph-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `Object` (`id`) ON DELETE CASCADE
1646 ) ENGINE=InnoDB
1647 ";
1648 $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')";
1649 $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')";
1650 $query[] = "ALTER TABLE VLANIPv4 ADD UNIQUE `network-domain-vlan` (ipv4net_id, domain_id, vlan_id)";
1651 $query[] = "ALTER TABLE VLANIPv4 DROP KEY `network-domain`";
1652 $query[] = "ALTER TABLE VLANIPv6 ADD UNIQUE `network-domain-vlan` (ipv6net_id, domain_id, vlan_id)";
1653 $query[] = "ALTER TABLE VLANIPv6 DROP KEY `network-domain`";
1654 $query[] = "UPDATE Config SET varvalue = '0.20.2' WHERE varname = 'DB_VERSION'";
1655 break;
1656 case '0.20.3':
1657 $query[] = "UPDATE Config SET varvalue = '0.20.3' WHERE varname = 'DB_VERSION'";
1658 break;
1659 case '0.20.4':
1660 $query[] = "ALTER TABLE `FileLink` MODIFY COLUMN `entity_type` ENUM('ipv4net','ipv4rspool','ipv4vs','ipv6net','location','object','rack','row','user') NOT NULL DEFAULT 'object'";
1661 $query[] = "ALTER TABLE `RackSpace` MODIFY COLUMN `state` ENUM('A','U','T') NOT NULL default 'A'";
1662 $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')";
1663 $query[] = "UPDATE `Config` SET varvalue = CONCAT(varvalue, ' or {\$typeid_965}') WHERE varname = 'IPV4OBJ_LISTSRC'";
1664 $query[] = "UPDATE AttributeValue INNER JOIN AttributeMap USING (attr_id) SET AttributeValue.uint_value = 1572 WHERE chapter_id = 12 AND uint_value = 162";
1665 $query[] = "UPDATE AttributeValue INNER JOIN AttributeMap USING (attr_id) SET AttributeValue.uint_value = 1710 WHERE chapter_id = 12 AND uint_value = 163";
1666 $query[] = "UPDATE Config SET varvalue = '%Y-%m-%d', description='PHP strftime() format to use for date output' WHERE varname = 'DATETIME_FORMAT'";
1667 $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')";
1668 $query[] = "UPDATE Config SET varvalue = '0.20.4' WHERE varname = 'DB_VERSION'";
1669 break;
1670 case '0.20.5':
1671 $query[] = "
1672 CREATE OR REPLACE VIEW `Rack` AS SELECT O.id, O.name AS name, O.asset_no, O.has_problems, O.comment,
1673 AV_H.uint_value AS height,
1674 AV_S.uint_value AS sort_order,
1675 RT.thumb_data,
1676 R.id AS row_id,
1677 R.name AS row_name,
1678 L.id AS location_id,
1679 L.name AS location_name
1680 FROM `Object` O
1681 LEFT JOIN `AttributeValue` AV_H ON O.id = AV_H.object_id AND AV_H.attr_id = 27
1682 LEFT JOIN `AttributeValue` AV_S ON O.id = AV_S.object_id AND AV_S.attr_id = 29
1683 LEFT JOIN `RackThumbnail` RT ON O.id = RT.rack_id
1684 LEFT JOIN `EntityLink` RL ON O.id = RL.child_entity_id AND RL.parent_entity_type = 'row' AND RL.child_entity_type = 'rack'
1685 INNER JOIN `Object` R ON R.id = RL.parent_entity_id
1686 LEFT JOIN `EntityLink` LL ON R.id = LL.child_entity_id AND LL.parent_entity_type = 'location' AND LL.child_entity_type = 'row'
1687 LEFT JOIN `Object` L ON L.id = LL.parent_entity_id
1688 WHERE O.objtype_id = 1560
1689 ";
1690
1691 // prevent some AttributeMap entries from being deleted
1692 $query[] = "ALTER TABLE AttributeMap ADD COLUMN sticky enum('yes','no') default 'no'";
1693 $query[] = "UPDATE AttributeMap SET sticky = 'yes' WHERE objtype_id = 4 AND attr_id IN (26,28)"; // Server -> Hypervisor, Slot number
1694 $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
1695 $query[] = "UPDATE AttributeMap SET sticky = 'yes' WHERE objtype_id = 798 AND attr_id = 28"; // Network security -> Slot number
1696 $query[] = "UPDATE AttributeMap SET sticky = 'yes' WHERE objtype_id = 1055 AND attr_id = 28"; // FC switch -> Slot number
1697 $query[] = "UPDATE AttributeMap SET sticky = 'yes' WHERE objtype_id = 1560 AND attr_id IN (27,29)"; // Rack -> Height, Sort order
1698 $query[] = "UPDATE AttributeMap SET sticky = 'yes' WHERE objtype_id = 1787 AND attr_id = 30"; // Management interface -> Mgmt type
1699
1700 $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')";
1701
1702 // SLB v2 tables
1703 $query[] = "
1704 CREATE TABLE `VS` (
1705 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
1706 `name` char(255) DEFAULT NULL,
1707 `vsconfig` text,
1708 `rsconfig` text,
1709 PRIMARY KEY (`id`)
1710 ) ENGINE=InnoDB
1711 ";
1712 $query[] = "
1713 CREATE TABLE `VSIPs` (
1714 `vs_id` int(10) unsigned NOT NULL,
1715 `vip` varbinary(16) NOT NULL,
1716 `vsconfig` text,
1717 `rsconfig` text,
1718 PRIMARY KEY (`vs_id`,`vip`),
1719 KEY `vip` (`vip`),
1720 CONSTRAINT `VSIPs-vs_id` FOREIGN KEY (`vs_id`) REFERENCES `VS` (`id`) ON DELETE CASCADE
1721 ) ENGINE=InnoDB
1722 ";
1723 $query[] = "
1724 CREATE TABLE `VSPorts` (
1725 `vs_id` int(10) unsigned NOT NULL,
1726 `proto` enum('TCP','UDP','MARK') NOT NULL,
1727 `vport` int(10) unsigned NOT NULL,
1728 `vsconfig` text,
1729 `rsconfig` text,
1730 PRIMARY KEY (`vs_id`,`proto`,`vport`),
1731 KEY `proto-vport` (`proto`,`vport`),
1732 CONSTRAINT `VS-vs_id` FOREIGN KEY (`vs_id`) REFERENCES `VS` (`id`) ON DELETE CASCADE
1733 ) ENGINE=InnoDB
1734 ";
1735 $query[] = "
1736 CREATE TABLE `VSEnabledIPs` (
1737 `object_id` int(10) unsigned NOT NULL,
1738 `vs_id` int(10) unsigned NOT NULL,
1739 `vip` varbinary(16) NOT NULL,
1740 `rspool_id` int(10) unsigned NOT NULL,
1741 `prio` varchar(255) DEFAULT NULL,
1742 `vsconfig` text,
1743 `rsconfig` text,
1744 PRIMARY KEY (`object_id`,`vs_id`,`vip`,`rspool_id`),
1745 KEY `vip` (`vip`),
1746 KEY `VSEnabledIPs-FK-vs_id-vip` (`vs_id`,`vip`),
1747 KEY `VSEnabledIPs-FK-rspool_id` (`rspool_id`),
1748 CONSTRAINT `VSEnabledIPs-FK-rspool_id` FOREIGN KEY (`rspool_id`) REFERENCES `IPv4RSPool` (`id`) ON DELETE CASCADE,
1749 CONSTRAINT `VSEnabledIPs-FK-vs_id-vip` FOREIGN KEY (`vs_id`, `vip`) REFERENCES `VSIPs` (`vs_id`, `vip`) ON DELETE CASCADE
1750 ) ENGINE=InnoDB
1751 ";
1752 $query[] = "
1753 CREATE TABLE `VSEnabledPorts` (
1754 `object_id` int(10) unsigned NOT NULL,
1755 `vs_id` int(10) unsigned NOT NULL,
1756 `proto` enum('TCP','UDP','MARK') NOT NULL,
1757 `vport` int(10) unsigned NOT NULL,
1758 `rspool_id` int(10) unsigned NOT NULL,
1759 `vsconfig` text,
1760 `rsconfig` text,
1761 PRIMARY KEY (`object_id`,`vs_id`,`proto`,`vport`,`rspool_id`),
1762 KEY `VSEnabledPorts-FK-vs_id-proto-vport` (`vs_id`,`proto`,`vport`),
1763 KEY `VSEnabledPorts-FK-rspool_id` (`rspool_id`),
1764 CONSTRAINT `VSEnabledPorts-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `Object` (`id`) ON DELETE CASCADE,
1765 CONSTRAINT `VSEnabledPorts-FK-rspool_id` FOREIGN KEY (`rspool_id`) REFERENCES `IPv4RSPool` (`id`) ON DELETE CASCADE,
1766 CONSTRAINT `VSEnabledPorts-FK-vs_id-proto-vport` FOREIGN KEY (`vs_id`, `proto`, `vport`) REFERENCES `VSPorts` (`vs_id`, `proto`, `vport`) ON DELETE CASCADE
1767 ) ENGINE=InnoDB
1768 ";
1769 $query[] = "ALTER TABLE `EntityLink` MODIFY COLUMN `parent_entity_type` ENUM('ipv4net','ipv4rspool','ipv4vs','ipv6net','location','object','rack','row','user') NOT NULL";
1770 $query[] = "ALTER TABLE `FileLink` MODIFY COLUMN `entity_type` ENUM('ipv4net','ipv4rspool','ipv4vs','ipv6net','location','object','rack','row','user') NOT NULL DEFAULT 'object'";
1771 $query[] = "ALTER TABLE `TagStorage` MODIFY COLUMN `entity_realm` ENUM('file','ipv4net','ipv4rspool','ipv4vs','ipvs','ipv6net','location','object','rack','user','vst') NOT NULL DEFAULT 'object'";
1772 $query[] = "ALTER TABLE `UserConfig` DROP FOREIGN KEY `UserConfig-FK-user`";
1773 $query[] = "UPDATE Config SET varvalue = '0.20.5' WHERE varname = 'DB_VERSION'";
1774 break;
1775 case 'dictionary':
1776 $query = reloadDictionary();
1777 break;
1778 default:
1779 return NULL;
1780 }
1781 return $query;
1782 }
1783
1784 function executeUpgradeBatch ($batchid)
1785 {
1786 global $dbxlink;
1787 $query = getUpgradeBatch($batchid);
1788 if ($query === NULL)
1789 {
1790 showError ("unknown batch '${batchid}'", __FUNCTION__);
1791 die;
1792 }
1793 $failures = array();
1794 echo "<tr><th>Executing batch '${batchid}'</th><td>";
1795 foreach ($query as $q)
1796 {
1797 try
1798 {
1799 $result = $dbxlink->query ($q);
1800 }
1801 catch (PDOException $e)
1802 {
1803 $errorInfo = $dbxlink->errorInfo();
1804 $failures[] = array ($q, $errorInfo[2]);
1805 }
1806 }
1807 if (!count ($failures))
1808 echo "<strong><font color=green>done</font></strong>";
1809 else
1810 {
1811 echo "<strong><font color=red>The following queries failed:</font></strong><br><pre>";
1812 foreach ($failures as $f)
1813 {
1814 list ($q, $i) = $f;
1815 echo "${q} -- ${i}\n";
1816 }
1817 echo "</pre>";
1818 }
1819 echo '</td></tr>';
1820 }
1821
1822 function authenticate_admin ($username, $password)
1823 {
1824 global $dbxlink;
1825 $prepared = $dbxlink->prepare ('SELECT COUNT(*) FROM UserAccount WHERE user_id=1 AND user_name=? AND user_password_hash=?');
1826 if (!$prepared->execute (array ($username, sha1 ($password))))
1827 die ('SQL query failed in ' . __FUNCTION__);
1828 $rows = $prepared->fetchAll (PDO::FETCH_NUM);
1829 return $rows[0][0] == 1;
1830 }
1831
1832 // Database version detector. Should behave corretly on any
1833 // working dataset a user might have.
1834 function getDatabaseVersion ()
1835 {
1836 global $dbxlink;
1837 $prepared = $dbxlink->prepare ('SELECT varvalue FROM Config WHERE varname = "DB_VERSION" and vartype = "string"');
1838 if (! $prepared->execute())
1839 {
1840 $errorInfo = $dbxlink->errorInfo();
1841 die (__FUNCTION__ . ': SQL query failed with error ' . $errorInfo[2]);
1842 }
1843 $rows = $prepared->fetchAll (PDO::FETCH_NUM);
1844 unset ($result);
1845 if (count ($rows) != 1 || !strlen ($rows[0][0]))
1846 die (__FUNCTION__ . ': Cannot guess database version. Config table is present, but DB_VERSION is missing or invalid. Giving up.');
1847 $ret = $rows[0][0];
1848 return $ret;
1849 }
1850
1851 function showUpgradeError ($info = '', $location = 'N/A')
1852 {
1853 if (preg_match ('/\.php$/', $location))
1854 $location = basename ($location);
1855 elseif ($location != 'N/A')
1856 $location = $location . '()';
1857 echo "<div class=msg_error>An error has occured in [${location}]. ";
1858 if (!strlen ($info))
1859 echo 'No additional information is available.';
1860 else
1861 echo "Additional information:<br><p>\n<pre>\n${info}\n</pre></p>";
1862 echo "Go back or try starting from <a href='index.php'>index page</a>.<br></div>\n";
1863 }
1864
1865 // changes the value of config variable. If $old_value_filter is set, value is changed only if current value equals to it.
1866 function replaceConfigVarValue ($varname, $new_value, $old_value_filter = NULL)
1867 {
1868 global $dbxlink;
1869 if (isset ($old_value_filter))
1870 {
1871 $result = $dbxlink->prepare ("SELECT varvalue FROM Config WHERE varname = ?");
1872 $result->execute (array ($varname));
1873 if ($row = $result->fetch (PDO::FETCH_ASSOC))
1874 if ($row['varvalue'] != $old_value_filter)
1875 return;
1876 unset ($result);
1877 }
1878 $result = $dbxlink->prepare ("UPDATE Config set varvalue = ? WHERE varname = ?");
1879 $result->execute (array ($new_value, $varname));
1880 }
1881
1882 function renderUpgraderHTML()
1883 {
1884 global $found_secret_file;
1885 if (! $found_secret_file)
1886 die ('<center>There is no working RackTables instance here, <a href="?module=installer">install</a>?</center>');
1887
1888 try
1889 {
1890 connectDB();
1891 }
1892 catch (RackTablesError $e)
1893 {
1894 die ("Database connection failed:\n\n" . $e->getMessage());
1895 }
1896
1897 if
1898 (
1899 !isset ($_SERVER['PHP_AUTH_USER']) or
1900 !strlen ($_SERVER['PHP_AUTH_USER']) or
1901 !isset ($_SERVER['PHP_AUTH_PW']) or
1902 !strlen ($_SERVER['PHP_AUTH_PW']) or
1903 !authenticate_admin ($_SERVER['PHP_AUTH_USER'], $_SERVER['PHP_AUTH_PW'])
1904 )
1905 {
1906 header ('WWW-Authenticate: Basic realm="RackTables upgrade"');
1907 header ('HTTP/1.0 401 Unauthorized');
1908 ?>
1909 <h1>Trouble logging in?</h1>
1910 You are trying to authenticate for the RackTables upgrade screen. This means that
1911 you must authenticate with the username and password of the main RackTables
1912 administrator. There is only one such account in each installation, its default
1913 username is "admin". RackTables wiki provides more information on this topic.
1914 <?php
1915 die;
1916 }
1917
1918 ?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
1919 <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
1920 <head><title>RackTables upgrade script</title>
1921 <style type="text/css">
1922 .tdleft {
1923 text-align: left;
1924 }
1925
1926 .trok {
1927 background-color: #80FF80;
1928 }
1929
1930 .trwarning {
1931 background-color: #FFFF80;
1932 }
1933
1934 .trerror {
1935 background-color: #FF8080;
1936 }
1937 </style>
1938 </head>
1939 <body>
1940 <h1>Platform check status</h1>
1941 <?php
1942
1943 if (!platform_is_ok())
1944 die ('</body></html>');
1945
1946 echo '<h1>Upgrade status</h1>';
1947 $dbver = getDatabaseVersion();
1948 echo '<table border=1 cellpadding=5>';
1949 echo "<tr><th>Current status</th><td>Data version: ${dbver}<br>Code version: " . CODE_VERSION . "</td></tr>\n";
1950
1951 $path = getDBUpgradePath ($dbver, CODE_VERSION);
1952 if ($path === NULL)
1953 {
1954 echo "<tr><th>Upgrade path</th><td><font color=red>not found</font></td></tr>\n";
1955 echo "<tr><th>Summary</th><td>Check README for more information. RackTables releases prior to 0.16.4 ";
1956 echo "must be upgraded to 0.16.4 first.</td></tr>\n";
1957 }
1958 else
1959 {
1960 if (!count ($path))
1961 echo "<tr><th>Summary</th><td>Come back later.</td></tr>\n";
1962 else
1963 {
1964 echo "<tr><th>Upgrade path</th><td>${dbver} &rarr; " . implode (' &rarr; ', $path) . "</td></tr>\n";
1965 global $relnotes;
1966 foreach ($path as $batchid)
1967 if (isset ($relnotes[$batchid]))
1968 echo "<tr><th>Release notes for ${batchid}</th><td><pre>" . $relnotes[$batchid] . "</pre></td></tr>\n";
1969 if (array_key_exists ('reallyreally', $_REQUEST))
1970 {
1971 foreach ($path as $batchid)
1972 executeUpgradeBatch ($batchid);
1973 executeUpgradeBatch ('dictionary');
1974 echo "<tr><th>Summary</th><td>Upgrade complete, it is Ok to ";
1975 echo "<a href='index.php'>enter</a> the system.</td></tr>\n";
1976 }
1977 else
1978 {
1979 echo '<form method=post action="index.php?module=upgrade"><tr><th>Wait!</th>';
1980 echo '<td><p>RackTables database upgrades sometimes go wrong because of assorted reasons. ';
1981 echo 'It is <strong>highly recommended</strong> to make a database backup before ';
1982 echo 'proceeding any further. <tt>mysqldump</tt> and <tt>PHPMyAdmin</tt> are convenient ';
1983 echo 'tools for doing this.</p>';
1984 echo '<p><input type=checkbox name=reallyreally id=reallyreally><label for=reallyreally>';
1985 echo 'I am ready to bear all risks of this upgrade. I am ready to roll it back in case of ';
1986 echo 'a failure.</label> <input type=submit value="Yes, I am."></p></td></tr></form>';
1987 }
1988 }
1989 }
1990 echo '</table>';
1991 echo '</body></html>';
1992 }
1993
1994 function convertSLBTablesToBinIPs()
1995 {
1996 global $dbxlink;
1997
1998 $dbxlink->query ("DROP TABLE IF EXISTS `IPv4VS_new`, `IPv4RS_new`, `IPv4VS_old`, `IPv4RS_old`");
1999
2000 $dbxlink->query (<<<END
2001 CREATE TABLE `IPv4VS_new` (
2002 `id` int(10) unsigned NOT NULL auto_increment,
2003 `vip` varbinary(16) NOT NULL,
2004 `vport` smallint(5) unsigned default NULL,
2005 `proto` enum('TCP','UDP','MARK') NOT NULL default 'TCP',
2006 `name` char(255) default NULL,
2007 `vsconfig` text,
2008 `rsconfig` text,
2009 PRIMARY KEY (`id`),
2010 KEY `vip` (`vip`)
2011 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
2012 END
2013 );
2014 $result = $dbxlink->query ("SELECT * FROM IPv4VS");
2015 $rows = $result->fetchAll (PDO::FETCH_ASSOC);
2016 unset ($result);
2017 foreach ($rows as $row)
2018 {
2019 $row['vip'] = ip4_int2bin ($row['vip']);
2020 usePreparedInsertBlade ('IPv4VS_new', $row);
2021 }
2022
2023 $dbxlink->query (<<<END
2024 CREATE TABLE `IPv4RS_new` (
2025 `id` int(10) unsigned NOT NULL auto_increment,
2026 `inservice` enum('yes','no') NOT NULL default 'no',
2027 `rsip` varbinary(16) NOT NULL,
2028 `rsport` smallint(5) unsigned default NULL,
2029 `rspool_id` int(10) unsigned default NULL,
2030 `rsconfig` text,
2031 `comment` varchar(255) DEFAULT NULL,
2032 PRIMARY KEY (`id`),
2033 KEY `rsip` (`rsip`),
2034 UNIQUE KEY `pool-endpoint` (`rspool_id`,`rsip`,`rsport`),
2035 CONSTRAINT `IPRS-FK` FOREIGN KEY (`rspool_id`) REFERENCES `IPv4RSPool` (`id`) ON DELETE CASCADE
2036 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
2037 END
2038 );
2039 $result = $dbxlink->query ("SELECT * FROM IPv4RS");
2040 $rows = $result->fetchAll (PDO::FETCH_ASSOC);
2041 unset ($result);
2042 foreach ($rows as $row)
2043 {
2044 $row['rsip'] = ip4_int2bin ($row['rsip']);
2045 usePreparedInsertBlade ('IPv4RS_new', $row);
2046 }
2047
2048 $dbxlink->query (<<<END
2049 RENAME TABLE
2050 `IPv4VS` TO `IPv4VS_old`,
2051 `IPv4VS_new` TO `IPv4VS`,
2052 `IPv4RS` TO `IPv4RS_old`,
2053 `IPv4RS_new` TO `IPv4RS`
2054 END
2055 );
2056 // re-create foreign key in IPv4LB
2057 $dbxlink->query ("ALTER TABLE `IPv4LB` DROP FOREIGN KEY `IPv4LB-FK-vs_id`");
2058 $dbxlink->query ("ALTER TABLE `IPv4LB` ADD CONSTRAINT `IPv4LB-FK-vs_id` FOREIGN KEY (`vs_id`) REFERENCES `IPv4VS` (`id`)");
2059
2060 $dbxlink->query ("DROP TABLE `IPv4VS_old`, `IPv4RS_old`");
2061
2062 // re-create foreign key in IPv4RS
2063 $dbxlink->query ("ALTER TABLE `IPv4RS` DROP FOREIGN KEY `IPRS-FK`");
2064 $dbxlink->query ("ALTER TABLE `IPv4RS` ADD CONSTRAINT `IPv4RS-FK` FOREIGN KEY (`rspool_id`) REFERENCES `IPv4RSPool` (`id`) ON DELETE CASCADE");
2065 }
2066
2067 // This is a swiss-knife blade to insert a record into a table.
2068 // The first argument is table name.
2069 // The second argument is an array of "name" => "value" pairs.
2070 // returns integer - affected rows count. Throws exception on error
2071 function usePreparedInsertBlade ($tablename, $columns)
2072 {
2073 global $dbxlink;
2074 $query = "INSERT INTO ${tablename} (" . implode (', ', array_keys ($columns));
2075 $query .= ') VALUES (' . questionMarks (count ($columns)) . ')';
2076 // Now the query should be as follows:
2077 // INSERT INTO table (c1, c2, c3) VALUES (?, ?, ?)
2078 try
2079 {
2080 $prepared = $dbxlink->prepare ($query);
2081 $prepared->execute (array_values ($columns));
2082 return $prepared->rowCount();
2083 }
2084 catch (PDOException $e)
2085 {
2086 throw convertPDOException ($e);
2087 }
2088 }
2089
2090 ?>