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