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