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