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