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