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