r4238 bugfix: image caching was broken with some browsers
[racktables] / wwwroot / upgrade.php
CommitLineData
6dc745d2 1<?php
fbbb74fb 2
90b96ff6
DO
3$relnotes = array
4(
5ae6d365
DO
5 '0.17.0' => <<<ENDOFTEXT
6LDAP options have been moved to LDAP_options array. This means, that if you were
7using LDAP authentication for users in version 0.16.x, it will break right after
8upgrade to 0.17.0. To get things working again, adjust existing secret.php file
9according to secret-sample.php file provided with 0.17.0 release.
10
11This release is the first to take advantage of the foreign key support
12provided by the InnoDB storage engine in MySQL. The installer and
13upgrader scripts check for InnoDB support and cannot complete without it.
14If you have trouble, the first step is to make sure the 'skip-innodb'
15option in my.cnf is commented out.
16
17Another change is the addition of support for file uploads. Files are stored
18in the database. There are several settings in php.ini which you may need to modify:
19 file_uploads - needs to be On
20 upload_max_filesize - max size for uploaded files
21 post_max_size - max size of all form data submitted via POST (including files)
22
23User accounts used to have 'enabled' flag, which allowed individual blocking and
24unblocking of each. This flag was dropped in favor of existing mean of access
25setup (RackCode). An unconditional denying rule is automatically added into RackCode
26for such blocked account, so the effective security policy remains the same.
27ENDOFTEXT
28,
1679ddbd
DO
29 '0.18.2' => <<<ENDOFTEXT
30RackTables from its version 0.18.0 and later is not compatible with
31RHEL/CentOS (at least with versions up to 5.5) Linux distributions
32in their default installation. There are yet options to work around that:
331. Install RackTables on a server with a different distribution/OS.
342. Request Linux distribution vendor to fix the bug with PCRE.
353. Repair your RHEL/CentOS installation yourself by fixing its PCRE
36RPM as explained here: http://bugs.centos.org/view.php?id=3252
37ENDOFTEXT
9fb6900d
DO
38,
39 '0.19.0' => <<<ENDOFTEXT
841a5b54
DO
40The files, which are intended for the httpd (web-server) directory, are
41now in the "wwwroot" directory of the tar.gz archive. Files outside of
42that directory are not directly intended for httpd environment and should
43not be copied to the server.
9fb6900d 44
8489d2af
DO
45This release incorporates ObjectLog functionality, which used to be
46available as a separate plugin. For the best results it is advised to
47disable (through local.php) external ObjectLog plugin permanently before
48the new version is installed. All previously accumulated ObjectLog records
49will be available through the updated standard interface.
50
841a5b54
DO
51RackTables is now using PHP JSON extension which is included in the PHP
52core since 5.2.0.
53
54The barcode attribute was removed. The upgrade script attempts to
55preserve the data by moving it to either the 'OEM S/N 1' attribute or to
56a Log entry. You should backup your database beforehand anyway.
9fb6900d 57ENDOFTEXT
5ae6d365 58,
90b96ff6
DO
59);
60
fbbb74fb
DO
61// At the moment we assume, that for any two releases we can
62// sequentally execute all batches, that separate them, and
63// nothing will break. If this changes one day, the function
64// below will have to generate smarter upgrade paths, while
65// the upper layer will remain the same.
66// Returning an empty array means that no upgrade is necessary.
4114697d 67// Returning NULL indicates an error.
fbbb74fb
DO
68function getDBUpgradePath ($v1, $v2)
69{
a6f83a72
DO
70 $versionhistory = array
71 (
b3f866fc 72 '0.16.4',
64347dcf 73 '0.16.5',
90b96ff6 74 '0.16.6',
30d0a2a3 75 '0.17.0',
4563cecb 76 '0.17.1',
7b1a3a72 77 '0.17.2',
9e51318b 78 '0.17.3',
958ac06d 79 '0.17.4',
9f572fb5 80 '0.17.5',
63811a09 81 '0.17.6',
026a79ee 82 '0.17.7',
3540d15c 83 '0.17.8',
1e81ad97 84 '0.17.9',
f32167d2 85 '0.17.10',
4a4a5440 86 '0.17.11',
9013f05b 87 '0.18.0',
425fd829 88 '0.18.1',
298d2375 89 '0.18.2',
f6d1a7cc 90 '0.18.3',
1c5b7c84 91 '0.18.4',
92ee2b01 92 '0.18.5',
2f5e4db9 93 '0.18.6',
9fb6900d 94 '0.18.7',
16825cc8 95 '0.19.0',
1d5dd3a1 96 '0.19.1',
a6f83a72 97 );
120e9ddd
DO
98 if (!in_array ($v1, $versionhistory) or !in_array ($v2, $versionhistory))
99 return NULL;
fbbb74fb 100 $skip = TRUE;
4114697d 101 $path = NULL;
fbbb74fb
DO
102 // Now collect all versions > $v1 and <= $v2
103 foreach ($versionhistory as $v)
104 {
4114697d 105 if ($skip and $v == $v1)
fbbb74fb
DO
106 {
107 $skip = FALSE;
4114697d 108 $path = array();
fbbb74fb
DO
109 continue;
110 }
111 if ($skip)
112 continue;
113 $path[] = $v;
114 if ($v == $v2)
115 break;
116 }
117 return $path;
118}
119
90b96ff6
DO
120// Upgrade batches are named exactly as the release where they first appear.
121// That is simple, but seems sufficient for beginning.
fbbb74fb
DO
122function executeUpgradeBatch ($batchid)
123{
124 $query = array();
ca3d68bd 125 global $dbxlink;
fbbb74fb
DO
126 switch ($batchid)
127 {
64347dcf
DO
128 case '0.16.5':
129 $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')";
130 $query[] = "update Config set varvalue = '0.16.5' where varname = 'DB_VERSION'";
131 break;
90b96ff6
DO
132 case '0.16.6':
133 $query[] = "update Config set varvalue = '0.16.6' where varname = 'DB_VERSION'";
134 break;
30d0a2a3 135 case '0.17.0':
e1ae3fb4
AD
136 // create tables for storing files (requires InnoDB support)
137 if (!isInnoDBSupported ())
138 {
c4ee2310 139 showError ("Cannot upgrade because InnoDB tables are not supported by your MySQL server. See the README for details.");
f3c50166 140 die;
e1ae3fb4 141 }
f76c4197
DY
142
143 $query[] = "alter table Chapter change chapter_no id int(10) unsigned NOT NULL auto_increment";
144 $query[] = "alter table Chapter change chapter_name name char(128) NOT NULL";
145 $query[] = "alter table Chapter drop key chapter_name";
146 $query[] = "alter table Chapter add UNIQUE KEY name (name)";
147 $query[] = "alter table Attribute change attr_id id int(10) unsigned NOT NULL auto_increment";
148 $query[] = "alter table Attribute change attr_type type enum('string','uint','float','dict') default NULL";
149 $query[] = "alter table Attribute change attr_name name char(64) default NULL";
150 $query[] = "alter table Attribute drop key attr_name";
151 $query[] = "alter table Attribute add UNIQUE KEY name (name)";
152 $query[] = "alter table AttributeMap change chapter_no chapter_id int(10) unsigned NOT NULL";
153 $query[] = "alter table Dictionary change chapter_no chapter_id int(10) unsigned NOT NULL";
3fb336f6 154 // Only after the above call it is Ok to use reloadDictionary()
ca3d68bd 155 $query = array_merge ($query, reloadDictionary ($batchid));
f3c50166 156 // schema changes for file management
e1ae3fb4
AD
157 $query[] = "
158CREATE TABLE `File` (
159 `id` int(10) unsigned NOT NULL auto_increment,
160 `name` char(255) NOT NULL,
161 `type` char(255) NOT NULL,
162 `size` int(10) unsigned NOT NULL,
163 `ctime` datetime NOT NULL,
164 `mtime` datetime NOT NULL,
165 `atime` datetime NOT NULL,
166 `contents` longblob NOT NULL,
167 `comment` text,
13edfa1c
AD
168 PRIMARY KEY (`id`),
169 UNIQUE KEY `name` (`name`)
e1ae3fb4
AD
170) ENGINE=InnoDB";
171 $query[] = "
172CREATE TABLE `FileLink` (
173 `id` int(10) unsigned NOT NULL auto_increment,
174 `file_id` int(10) unsigned NOT NULL,
175 `entity_type` enum('ipv4net','ipv4rspool','ipv4vs','object','rack','user') NOT NULL default 'object',
176 `entity_id` int(10) NOT NULL,
177 PRIMARY KEY (`id`),
af721881 178 UNIQUE KEY `FileLink-unique` (`file_id`,`entity_type`,`entity_id`),
e1ae3fb4
AD
179 KEY `FileLink-file_id` (`file_id`),
180 CONSTRAINT `FileLink-File_fkey` FOREIGN KEY (`file_id`) REFERENCES `File` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
181) ENGINE=InnoDB";
182 $query[] = "ALTER TABLE TagStorage MODIFY COLUMN target_realm enum('file','ipv4net','ipv4rspool','ipv4vs','object','rack','user') NOT NULL default 'object'";
f3c50166 183
f76c4197 184 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (24,'no','network security models')";
9730d09f 185 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (25,'no','wireless models')";
f76c4197
DY
186 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,1,0)";
187 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,2,24)";
188 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,3,0)";
189 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,5,0)";
190 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,14,0)";
191 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,16,0)";
192 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,17,0)";
193 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,18,0)";
194 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,20,0)";
195 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,21,0)";
196 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,22,0)";
197 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,24,0)";
9730d09f
DO
198 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (965,1,0)";
199 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (965,3,0)";
200 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (965,2,25)";
706ce117
DO
201 $query[] = 'alter table IPBonds rename to IPv4Allocation';
202 $query[] = 'alter table PortForwarding rename to IPv4NAT';
203 $query[] = 'alter table IPRanges rename to IPv4Network';
204 $query[] = 'alter table IPAddress rename to IPv4Address';
205 $query[] = 'alter table IPLoadBalancer rename to IPv4LB';
4114697d 206 $query[] = 'alter table IPRSPool rename to IPv4RSPool';
706ce117 207 $query[] = 'alter table IPRealServer rename to IPv4RS';
4114697d 208 $query[] = 'alter table IPVirtualService rename to IPv4VS';
120e9ddd
DO
209 $query[] = "alter table TagStorage change column target_realm entity_realm enum('file','ipv4net','ipv4vs','ipv4rspool','object','rack','user') NOT NULL default 'object'";
210 $query[] = 'alter table TagStorage change column target_id entity_id int(10) unsigned NOT NULL';
211 $query[] = 'alter table TagStorage drop key entity_tag';
212 $query[] = 'alter table TagStorage drop key target_id';
213 $query[] = 'alter table TagStorage add UNIQUE KEY `entity_tag` (`entity_realm`,`entity_id`,`tag_id`)';
214 $query[] = 'alter table TagStorage add KEY `entity_id` (`entity_id`)';
37e59768
DO
215 $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')";
216 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('PREVIEW_TEXT_ROWS','25','uint','yes','no','Rows for text file preview')";
217 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('PREVIEW_TEXT_COLS','80','uint','yes','no','Columns for text file preview')";
218 $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 219 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('VENDOR_SIEVE','','string','yes','no','Vendor sieve configuration')";
073ed463
DO
220 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('IPV4LB_LISTSRC','{\$typeid_4}','string','yes','no','List source: IPv4 load balancers')";
221 $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')";
222 $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')";
223 $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')";
224 $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 225 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('RACKS_PER_ROW','12','unit','yes','no','Racks per row')";
590e1281 226 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('FILTER_PREDICATE_SIEVE','','string','yes','no','Predicate sieve regex(7)')";
5496c89f
DO
227 $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)')";
228 $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')";
229 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('FILTER_SUGGEST_TAGS','yes','string','no','no','Suggest tags in list filter')";
230 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('FILTER_SUGGEST_PREDICATES','yes','string','no','no','Suggest predicates in list filter')";
231 $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
232 $query[] = "delete from Config where varname = 'USER_AUTH_SRC'";
233 $query[] = "delete from Config where varname = 'COOKIE_TTL'";
234 $query[] = "delete from Config where varname = 'rtwidth_0'";
235 $query[] = "delete from Config where varname = 'rtwidth_1'";
236 $query[] = "delete from Config where varname = 'rtwidth_2'";
c6bc0ac5
DO
237 $query[] = "delete from Config where varname = 'NAMEFUL_OBJTYPES'";
238 $query[] = "delete from Config where varname = 'REQUIRE_ASSET_TAG_FOR'";
239 $query[] = "delete from Config where varname = 'IPV4_PERFORMERS'";
240 $query[] = "delete from Config where varname = 'NATV4_PERFORMERS'";
dbb33805 241 $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
242 $result = $dbxlink->query ("select user_id, user_name, user_realname from UserAccount where user_enabled = 'no'");
243 while ($row = $result->fetch (PDO::FETCH_ASSOC))
244 $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'";
245 $query[] = "update Script set script_text = NULL where script_name = 'RackCodeCache'";
246 unset ($result);
79b8ad1e 247 $query[] = "alter table UserAccount drop column user_enabled";
f76c4197 248
10bac82a
DY
249 $query[] = "CREATE TABLE RackRow ( id int(10) unsigned NOT NULL auto_increment, name char(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM";
250
9f14a7ef
DY
251 $result = $dbxlink->query ("select dict_key, dict_value from Dictionary where chapter_no = 3");
252 while($row = $result->fetch(PDO::FETCH_NUM))
10bac82a 253 $query[] = "insert into RackRow set id=${row[0]}, name='${row[1]}'";
ee286837 254 unset ($result);
f76c4197 255 $query[] = "delete from Dictionary where chapter_id = 3";
c4d0dc30 256 $query[] = "delete from Chapter where id = 3";
9133d2c5
DO
257 $query[] = "
258CREATE TABLE `LDAPCache` (
259 `presented_username` char(64) NOT NULL,
260 `successful_hash` char(40) NOT NULL,
261 `first_success` timestamp NOT NULL default CURRENT_TIMESTAMP,
262 `last_retry` timestamp NOT NULL default '0000-00-00 00:00:00',
263 `displayed_name` char(128) default NULL,
264 `memberof` text,
265 UNIQUE KEY `presented_username` (`presented_username`),
266 KEY `scanidx` (`presented_username`,`successful_hash`)
267) ENGINE=InnoDB;";
3827da34 268 $query[] = "alter table UserAccount modify column user_password_hash char(40) NULL";
aa9a0fb4
DO
269 $query[] = 'ALTER TABLE Rack DROP COLUMN deleted';
270 $query[] = 'ALTER TABLE RackHistory DROP COLUMN deleted';
271 $query[] = 'ALTER TABLE RackObject DROP COLUMN deleted';
272 $query[] = 'ALTER TABLE RackObjectHistory DROP COLUMN deleted';
2fb9d280
DO
273 // Can't be added straight due to many duplicates, even in "dictbase" data.
274 $result = $dbxlink->query ('SELECT type1, type2, count(*) - 1 as excess FROM PortCompat GROUP BY type1, type2 HAVING excess > 0');
275 while ($row = $result->fetch (PDO::FETCH_ASSOC))
276 $query[] = "DELETE FROM PortCompat WHERE type1 = ${row['type1']} AND type2 = ${row['type2']} limit ${row['excess']}";
277 unset ($result);
278 $query[] = 'ALTER TABLE PortCompat DROP KEY type1';
67d8a969 279 $query[] = 'ALTER TABLE PortCompat ADD UNIQUE `type1_2` (type1, type2)';
f76c4197
DY
280 $query[] = "UPDATE Config SET varvalue = '0.17.0' WHERE varname = 'DB_VERSION'";
281
b3f866fc 282 break;
4563cecb 283 case '0.17.1':
8b200a9c 284 $query[] = "ALTER TABLE Dictionary DROP KEY `chap_to_key`";
ca3d68bd 285 $query = array_merge ($query, reloadDictionary ($batchid));
4563cecb
DO
286 // Token set has changed, so the cache isn't valid any more.
287 $query[] = "UPDATE Script SET script_text = NULL WHERE script_name = 'RackCodeCache'";
288 $query[] = "UPDATE Config SET varvalue = '0.17.1' WHERE varname = 'DB_VERSION'";
7d4ea62b 289 break;
7b1a3a72 290 case '0.17.2':
7b1a3a72 291 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (26,'no','fibre channel switch models')";
ca3d68bd 292 $query = array_merge ($query, reloadDictionary ($batchid));
7c537f33 293 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1055,2,26)";
49b605d9 294 $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
295 // wipe irrelevant records (ticket:250)
296 $query[] = "DELETE FROM TagStorage WHERE entity_realm = 'file' AND entity_id NOT IN (SELECT id FROM File)";
f06fe423 297 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('IPV4_ENABLE_KNIGHT','yes','string','no','no','Enable IPv4 knight feature')";
99ab184f 298 $query[] = "ALTER TABLE IPv4Network ADD COLUMN comment text AFTER name";
5163cd3a 299 $query[] = "ALTER TABLE Port ADD INDEX comment (reservation_comment)";
029a14bc
DO
300 $query[] = "ALTER TABLE Port DROP KEY l2address"; // UNIQUE
301 $query[] = "ALTER TABLE Port ADD KEY (l2address)"; // not UNIQUE
948666cc
DO
302 $query[] = "ALTER TABLE Port DROP KEY object_id";
303 $query[] = "ALTER TABLE Port ADD UNIQUE KEY per_object (object_id, name, type)";
74aee2dc
DO
304 $query[] = "INSERT INTO PortCompat (type1, type2) VALUES (20,1083)";
305 $query[] = "INSERT INTO PortCompat (type1, type2) VALUES (21,1083)";
306 $query[] = "INSERT INTO PortCompat (type1, type2) VALUES (1077,1077)";
307 $query[] = "INSERT INTO PortCompat (type1, type2) VALUES (1083,20)";
308 $query[] = "INSERT INTO PortCompat (type1, type2) VALUES (1083,21)";
309 $query[] = "INSERT INTO PortCompat (type1, type2) VALUES (1083,1083)";
1c4830dc 310 $query[] = "INSERT INTO PortCompat (type1, type2) VALUES (1087,1087)";
50e02490
DO
311 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (27,'no','PDU models')";
312 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (2,2,27)";
7b1a3a72
AD
313 $query[] = "UPDATE Config SET varvalue = '0.17.2' WHERE varname = 'DB_VERSION'";
314 break;
9e51318b
DO
315 case '0.17.3':
316 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('TAGS_TOPLIST_SIZE','50','uint','yes','no','Tags top list size')";
317 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('TAGS_QUICKLIST_SIZE','20','uint','no','no','Tags quick list size')";
318 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('TAGS_QUICKLIST_THRESHOLD','50','uint','yes','no','Tags quick list threshold')";
87601bbc 319 $query[] = "ALTER TABLE AttributeMap MODIFY COLUMN chapter_id int(10) unsigned NULL";
7028a42c 320 $query[] = "UPDATE AttributeMap SET chapter_id = NULL WHERE attr_id IN (SELECT id FROM Attribute WHERE type != 'dict')";
a013838b
DO
321 // ticket:239
322 $query[] = 'UPDATE AttributeValue SET uint_value = 1018 WHERE uint_value = 731 AND attr_id IN (SELECT attr_id FROM AttributeMap WHERE chapter_id = 12)';
323 $query[] = 'DELETE FROM Dictionary WHERE dict_key = 731';
084aca6c
DO
324 $query = array_merge ($query, reloadDictionary ($batchid));
325 $query[] = "UPDATE Config SET vartype='uint' WHERE varname='RACKS_PER_ROW'";
f44fdef9 326 $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
327 $query[] = "UPDATE Config SET varvalue = '0.17.3' WHERE varname = 'DB_VERSION'";
328 break;
958ac06d
DO
329 case '0.17.4':
330 $query[] = "ALTER TABLE Link ENGINE=InnoDB";
331 $query[] = "ALTER TABLE Port ENGINE=InnoDB";
4d87feaf
DO
332 $query[] = "ALTER TABLE IPv4RS ENGINE=InnoDB";
333 $query[] = "ALTER TABLE IPv4RSPool ENGINE=InnoDB";
cafd4cf3
DO
334 $query[] = "ALTER TABLE AttributeValue ENGINE=InnoDB";
335 $query[] = "ALTER TABLE RackObject ENGINE=InnoDB";
336 $query[] = "ALTER TABLE IPv4NAT ENGINE=InnoDB";
337 $query[] = "ALTER TABLE IPv4LB ENGINE=InnoDB";
338 $query[] = "ALTER TABLE IPv4VS ENGINE=InnoDB";
4d87feaf 339 $query[] = "DELETE FROM IPv4RS WHERE rspool_id NOT IN (SELECT id FROM IPv4RSPool)";
958ac06d 340 $query[] = "ALTER TABLE Link ADD CONSTRAINT `Link-FK-b` FOREIGN KEY (portb) REFERENCES Port (id)";
cafd4cf3 341 $query[] = "ALTER TABLE Link ADD CONSTRAINT `Link-FK-a` FOREIGN KEY (porta) REFERENCES Port (id)";
4d87feaf 342 $query[] = "ALTER TABLE IPv4RS ADD CONSTRAINT `IPv4RS-FK` FOREIGN KEY (rspool_id) REFERENCES IPv4RSPool (id) ON DELETE CASCADE";
cafd4cf3
DO
343 $query[] = "ALTER TABLE AttributeValue ADD CONSTRAINT `AttributeValue-FK-object_id` FOREIGN KEY (object_id) REFERENCES RackObject (id)";
344 $query[] = "ALTER TABLE IPv4NAT ADD CONSTRAINT `IPv4NAT-FK-object_id` FOREIGN KEY (object_id) REFERENCES RackObject (id)";
345 $query[] = "ALTER TABLE Port ADD CONSTRAINT `Port-FK-object_id` FOREIGN KEY (object_id) REFERENCES RackObject (id)";
346 $query[] = "ALTER TABLE IPv4LB ADD CONSTRAINT `IPv4LB-FK-rspool_id` FOREIGN KEY (rspool_id) REFERENCES IPv4RSPool (id)";
347 $query[] = "ALTER TABLE IPv4LB ADD CONSTRAINT `IPv4LB-FK-object_id` FOREIGN KEY (object_id) REFERENCES RackObject (id)";
348 $query[] = "ALTER TABLE IPv4LB ADD CONSTRAINT `IPv4LB-FK-vs_id` FOREIGN KEY (vs_id) REFERENCES IPv4VS (id)";
e49d8a77 349 $query = array_merge ($query, reloadDictionary ($batchid));
958ac06d
DO
350 $query[] = "UPDATE Config SET varvalue = '0.17.4' WHERE varname = 'DB_VERSION'";
351 break;
9f572fb5
DO
352 case '0.17.5':
353 $query[] = "ALTER TABLE TagTree ENGINE=InnoDB";
354 $query[] = "ALTER TABLE TagStorage ENGINE=InnoDB";
355 $query[] = "ALTER TABLE TagStorage ADD CONSTRAINT `TagStorage-FK-tag_id` FOREIGN KEY (tag_id) REFERENCES TagTree (id)";
356 $query[] = "ALTER TABLE TagTree ADD CONSTRAINT `TagTree-K-parent_id` FOREIGN KEY (parent_id) REFERENCES TagTree (id)";
2400d7ec
DO
357 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (21,1195)';
358 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (22,1196)';
359 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (23,1196)';
360 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (20,1195)';
361 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (25,1202)';
362 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (26,1202)';
363 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (27,1204)';
364 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (28,1204)';
365 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1083,1195)';
366 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1084,1084)';
367 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1195,20)';
368 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1195,21)';
369 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1195,1083)';
370 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1195,1195)';
371 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1196,22)';
372 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1196,23)';
373 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1196,1196)';
374 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1197,1197)';
375 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1198,1199)';
376 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1199,1198)';
377 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1200,1200)';
378 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1201,1201)';
379 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1202,25)';
380 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1202,26)';
381 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1202,1202)';
382 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1203,1203)';
383 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1204,27)';
384 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1204,28)';
385 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1204,1204)';
386 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1205,1205)';
387 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1206,1207)';
388 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1207,1206)';
93a83f51 389 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1316,1316)';
bdc91a5c
DO
390 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (16, 1322)';
391 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1322, 16)';
392 $query[] = 'DELETE FROM PortCompat WHERE type1 = 16 AND type2 = 16';
2400d7ec
DO
393 for ($i = 1209; $i <= 1300; $i++)
394 $query[] = "INSERT INTO PortCompat (type1, type2) VALUES (${i}, ${i})";
d9b88ea0 395 $query[] = "
09d6afa4 396CREATE TABLE `PortInnerInterface` (
9173281a 397 `id` int(10) unsigned NOT NULL,
d9b88ea0
DO
398 `iif_name` char(16) NOT NULL,
399 PRIMARY KEY (`id`),
9173281a 400 UNIQUE KEY `iif_name` (`iif_name`)
d9b88ea0 401) ENGINE=InnoDB";
2400d7ec
DO
402 $query[] = "INSERT INTO `PortInnerInterface` VALUES (1,'hardwired')";
403 $query[] = "INSERT INTO `PortInnerInterface` VALUES (2,'SFP-100')";
404 $query[] = "INSERT INTO `PortInnerInterface` VALUES (3,'GBIC')";
405 $query[] = "INSERT INTO `PortInnerInterface` VALUES (4,'SFP-1000')";
406 $query[] = "INSERT INTO `PortInnerInterface` VALUES (5,'XENPAK')";
407 $query[] = "INSERT INTO `PortInnerInterface` VALUES (6,'X2')";
408 $query[] = "INSERT INTO `PortInnerInterface` VALUES (7,'XPAK')";
409 $query[] = "INSERT INTO `PortInnerInterface` VALUES (8,'XFP')";
410 $query[] = "INSERT INTO `PortInnerInterface` VALUES (9,'SFP+')";
d9b88ea0 411 $query[] = "
09d6afa4 412CREATE TABLE `PortInterfaceCompat` (
d9b88ea0
DO
413 `iif_id` int(10) unsigned NOT NULL,
414 `oif_id` int(10) unsigned NOT NULL,
415 UNIQUE KEY `pair` (`iif_id`,`oif_id`),
2400d7ec 416 CONSTRAINT `PortInterfaceCompat-FK-iif_id` FOREIGN KEY (`iif_id`) REFERENCES `PortInnerInterface` (`id`)
d9b88ea0 417) ENGINE=InnoDB";
c76cfa0d
DO
418 $query[] = "ALTER TABLE Port ADD COLUMN iif_id int unsigned NOT NULL AFTER name"; // will set iif_id to 0
419 $query[] = "UPDATE Port SET iif_id = 2 WHERE type = 1208";
420 $query[] = "UPDATE Port SET iif_id = 3 WHERE type = 1078";
421 $query[] = "UPDATE Port SET iif_id = 4 WHERE type = 1077";
422 $query[] = "UPDATE Port SET iif_id = 5 WHERE type = 1079";
423 $query[] = "UPDATE Port SET iif_id = 6 WHERE type = 1080";
424 $query[] = "UPDATE Port SET iif_id = 7 WHERE type = 1081";
425 $query[] = "UPDATE Port SET iif_id = 8 WHERE type = 1082";
426 $query[] = "UPDATE Port SET iif_id = 9 WHERE type = 1084";
427 $query[] = "UPDATE Port SET iif_id = 1 WHERE iif_id = 0";
08aa3467
DO
428 $query[] = 'ALTER TABLE Port ADD UNIQUE `object_iif_oif_name` (object_id, iif_id, type, name)';
429 $query[] = 'ALTER TABLE Port DROP KEY `per_object`';
2400d7ec
DO
430 $base1000 = array (24, 34, 1202, 1203, 1204, 1205, 1206, 1207);
431 $base10000 = array (30, 35, 36, 37, 38, 39, 40);
432 $PICdata = array
433 (
bdc91a5c 434 1 => array (16, 19, 24, 29, 31, 33, 446, 681, 682, 1322),
2400d7ec
DO
435 2 => array (1208, 1195, 1196, 1197, 1198, 1199, 1200, 1201),
436 3 => array_merge (array (1078), $base1000),
437 4 => array_merge (array (1077), $base1000),
438 5 => array_merge (array (1079), $base10000),
439 6 => array_merge (array (1080), $base10000),
440 7 => array_merge (array (1081), $base10000),
441 8 => array_merge (array (1082), $base10000),
442 9 => array_merge (array (1084), $base10000),
443 );
444 // make sure all IIF/OIF pairs referenced from Port exist in PortInterfaceCompat before enabling FK
445 // iif_id doesn't exist at this point
446 $result = $dbxlink->query ('SELECT DISTINCT type FROM Port WHERE type NOT IN (1208, 1078, 1077, 1079, 1080, 1081, 1082, 1084)');
447 while ($row = $result->fetch (PDO::FETCH_ASSOC))
448 if (FALSE === array_search ($row['type'], $PICdata[1]))
449 array_push ($PICdata[1], $row['type']);
450 unset ($result);
451 foreach ($PICdata as $iif_id => $oif_ids)
452 foreach ($oif_ids as $oif_id)
453 $query[] = "INSERT INTO PortInterfaceCompat (iif_id, oif_id) VALUES (${iif_id}, ${oif_id})";
c76cfa0d 454 $query[] = "ALTER TABLE Port ADD CONSTRAINT `Port-FK-iif-oif` FOREIGN KEY (`iif_id`, `type`) REFERENCES `PortInterfaceCompat` (`iif_id`, `oif_id`)";
bdc91a5c 455 $query[] = 'UPDATE Port SET type = 1322 WHERE type = 16 AND (SELECT objtype_id FROM RackObject WHERE id = object_id) IN (2, 12)';
2400d7ec
DO
456 $query = array_merge ($query, reloadDictionary ($batchid));
457 $query[] = "DELETE FROM Config WHERE varname = 'default_port_type'";
458 $query[] = "INSERT INTO Config VALUES ('DEFAULT_PORT_IIF_ID','1','uint','no','no','Default port inner interface ID')";
459 $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 460 $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 461 $query[] = "UPDATE Chapter SET name = 'PortOuterInterface' WHERE id = 2";
fbcbb4ee
DO
462 // remap refs to duplicate records, which will be discarded (ticket:286)
463 $query[] = 'UPDATE AttributeValue SET uint_value = 147 WHERE uint_value = 1020 AND attr_id = 2';
464 $query[] = 'UPDATE AttributeValue SET uint_value = 377 WHERE uint_value = 1021 AND attr_id = 2';
e8ab58e8 465 $query[] = 'INSERT INTO AttributeMap (objtype_id, attr_id) VALUES (2, 1), (2, 3), (2, 5)';
9f572fb5
DO
466 $query[] = "UPDATE Config SET varvalue = '0.17.5' WHERE varname = 'DB_VERSION'";
467 break;
63811a09
DO
468 case '0.17.6':
469 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (28,'no','Voice/video hardware')";
470 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1323,1,NULL)";
471 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1323,2,28)";
472 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1323,3,NULL)";
473 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1323,5,NULL)";
cd3775e9 474 $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)')";
5c3bc8de 475 $query = array_merge ($query, reloadDictionary ($batchid));
63811a09
DO
476 $query[] = "UPDATE Config SET varvalue = '0.17.6' WHERE varname = 'DB_VERSION'";
477 break;
026a79ee
DO
478 case '0.17.7':
479 $query[] = "UPDATE Config SET varvalue = '0.17.7' WHERE varname = 'DB_VERSION'";
480 break;
3540d15c 481 case '0.17.8':
1e81ad97
DO
482 $query = array_merge ($query, reloadDictionary ($batchid));
483 $query[] = "ALTER TABLE TagTree DROP COLUMN valid_realm";
484 $query[] = "UPDATE Config SET varvalue = '0.17.8' WHERE varname = 'DB_VERSION'";
485 break;
486 case '0.17.9':
45833307 487 $query[] = "ALTER table Config add `is_userdefined` enum('yes','no') NOT NULL default 'no' AFTER `is_hidden`";
3540d15c
DY
488 $query[] = "
489CREATE TABLE `UserConfig` (
490 `varname` char(32) NOT NULL,
491 `varvalue` char(255) NOT NULL,
492 `user` char(64) NOT NULL,
493 UNIQUE KEY `user_varname` (`user`,`varname`)
494) TYPE=InnoDB";
1e81ad97
DO
495 $query[] = "UPDATE Config SET is_userdefined = 'yes' WHERE varname IN
496(
497'MASSCOUNT',
498'MAXSELSIZE',
499'ROW_SCALE',
500'PORTS_PER_ROW',
501'IPV4_ADDRS_PER_PAGE',
502'DEFAULT_RACK_HEIGHT',
503'DEFAULT_SLB_VS_PORT',
504'DEFAULT_SLB_RS_PORT',
505'DETECT_URLS',
506'RACK_PRESELECT_THRESHOLD',
507'DEFAULT_IPV4_RS_INSERVICE',
508'DEFAULT_OBJECT_TYPE',
509'SHOW_EXPLICIT_TAGS',
510'SHOW_IMPLICIT_TAGS',
511'SHOW_AUTOMATIC_TAGS',
512'IPV4_AUTO_RELEASE',
513'SHOW_LAST_TAB',
514'EXT_IPV4_VIEW',
515'TREE_THRESHOLD',
516'ADDNEW_AT_TOP',
517'IPV4_TREE_SHOW_USAGE',
518'PREVIEW_TEXT_MAXCHARS',
519'PREVIEW_TEXT_ROWS',
520'PREVIEW_TEXT_COLS',
521'PREVIEW_IMAGE_MAXPXS',
522'VENDOR_SIEVE',
523'RACKS_PER_ROW'
524)";
9013f05b
DO
525 $query[] = "UPDATE Config SET varvalue = '0.17.9' WHERE varname = 'DB_VERSION'";
526 break;
f32167d2
DO
527 case '0.17.10':
528 $query = array_merge ($query, reloadDictionary ($batchid));
4368cc45 529 $query[] = "ALTER TABLE MountOperation ADD KEY (object_id)";
2926b1cb 530 $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 531 $query[] = "UPDATE Config SET varvalue = '0.17.10' WHERE varname = 'DB_VERSION'";
d80036ba 532 break;
a1fc539a
RF
533 case '0.17.11':
534 $query = array_merge ($query, reloadDictionary ($batchid));
535 $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 536 $query[] = "DELETE AttributeValue FROM AttributeValue JOIN Attribute where AttributeValue.attr_id = Attribute.id AND Attribute.type = 'dict' AND AttributeValue.uint_value = 0";
a1fc539a 537 $query[] = "UPDATE Config SET varvalue = '0.17.11' WHERE varname = 'DB_VERSION'";
59e0658a 538 break;
9013f05b 539 case '0.18.0':
ad9da675 540 $query = array_merge ($query, reloadDictionary ($batchid));
8bb69a06
DO
541 $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')";
542 $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')";
543 $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')";
544 $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
545 $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')";
546 $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')";
547 $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 548 $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 549 $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 550 $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 551 $query[] = "ALTER TABLE IPv4Network ENGINE=InnoDB";
22fdebff
DO
552 $query[] = "SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0";
553 $query[] = "
554CREATE TABLE `CachedPAV` (
555 `object_id` int(10) unsigned NOT NULL,
556 `port_name` char(255) NOT NULL,
557 `vlan_id` int(10) unsigned NOT NULL default '0',
558 PRIMARY KEY (`object_id`,`port_name`,`vlan_id`),
559 KEY `vlan_id` (`vlan_id`),
560 CONSTRAINT `CachedPAV-FK-object-port` FOREIGN KEY (`object_id`, `port_name`) REFERENCES `CachedPVM` (`object_id`, `port_name`) ON DELETE CASCADE,
561 CONSTRAINT `CachedPAV-FK-vlan_id` FOREIGN KEY (`vlan_id`) REFERENCES `VLANValidID` (`vlan_id`)
562) ENGINE=InnoDB
563";
564 $query[] = "
565CREATE TABLE `CachedPNV` (
566 `object_id` int(10) unsigned NOT NULL,
567 `port_name` char(255) NOT NULL,
568 `vlan_id` int(10) unsigned NOT NULL default '0',
569 PRIMARY KEY (`object_id`,`port_name`,`vlan_id`),
570 UNIQUE KEY `port_id` (`object_id`,`port_name`),
571 CONSTRAINT `CachedPNV-FK-compound` FOREIGN KEY (`object_id`, `port_name`, `vlan_id`) REFERENCES `CachedPAV` (`object_id`, `port_name`, `vlan_id`) ON DELETE CASCADE
572) ENGINE=InnoDB
573";
574 $query[] = "
575CREATE TABLE `CachedPVM` (
576 `object_id` int(10) unsigned NOT NULL,
577 `port_name` char(255) NOT NULL,
578 `vlan_mode` enum('access','trunk') NOT NULL default 'access',
579 PRIMARY KEY (`object_id`,`port_name`),
580 CONSTRAINT `CachedPVM-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE
581) ENGINE=InnoDB
582";
583 $query[] = "
584CREATE TABLE `PortAllowedVLAN` (
585 `object_id` int(10) unsigned NOT NULL,
586 `port_name` char(255) NOT NULL,
587 `vlan_id` int(10) unsigned NOT NULL default '0',
588 PRIMARY KEY (`object_id`,`port_name`,`vlan_id`),
589 KEY `vlan_id` (`vlan_id`),
590 CONSTRAINT `PortAllowedVLAN-FK-object-port` FOREIGN KEY (`object_id`, `port_name`) REFERENCES `PortVLANMode` (`object_id`, `port_name`) ON DELETE CASCADE,
591 CONSTRAINT `PortAllowedVLAN-FK-vlan_id` FOREIGN KEY (`vlan_id`) REFERENCES `VLANValidID` (`vlan_id`)
592) ENGINE=InnoDB
593";
594 $query[] = "
595CREATE TABLE `PortNativeVLAN` (
596 `object_id` int(10) unsigned NOT NULL,
597 `port_name` char(255) NOT NULL,
598 `vlan_id` int(10) unsigned NOT NULL default '0',
599 PRIMARY KEY (`object_id`,`port_name`,`vlan_id`),
600 UNIQUE KEY `port_id` (`object_id`,`port_name`),
601 CONSTRAINT `PortNativeVLAN-FK-compound` FOREIGN KEY (`object_id`, `port_name`, `vlan_id`) REFERENCES `PortAllowedVLAN` (`object_id`, `port_name`, `vlan_id`) ON DELETE CASCADE
602) ENGINE=InnoDB
603";
604 $query[] = "
605CREATE TABLE `PortVLANMode` (
606 `object_id` int(10) unsigned NOT NULL,
607 `port_name` char(255) NOT NULL,
608 `vlan_mode` enum('access','trunk') NOT NULL default 'access',
609 PRIMARY KEY (`object_id`,`port_name`),
610 CONSTRAINT `PortVLANMode-FK-object-port` FOREIGN KEY (`object_id`, `port_name`) REFERENCES `CachedPVM` (`object_id`, `port_name`)
611) ENGINE=InnoDB
612";
613 $query[] = "
614CREATE TABLE `VLANDescription` (
615 `domain_id` int(10) unsigned NOT NULL,
616 `vlan_id` int(10) unsigned NOT NULL default '0',
617 `vlan_type` enum('ondemand','compulsory','alien') NOT NULL default 'ondemand',
618 `vlan_descr` char(255) default NULL,
619 PRIMARY KEY (`domain_id`,`vlan_id`),
620 KEY `vlan_id` (`vlan_id`),
219da133 621 CONSTRAINT `VLANDescription-FK-domain_id` FOREIGN KEY (`domain_id`) REFERENCES `VLANDomain` (`id`) ON DELETE CASCADE,
22fdebff
DO
622 CONSTRAINT `VLANDescription-FK-vlan_id` FOREIGN KEY (`vlan_id`) REFERENCES `VLANValidID` (`vlan_id`)
623) ENGINE=InnoDB
624";
625 $query[] = "
626CREATE TABLE `VLANDomain` (
627 `id` int(10) unsigned NOT NULL auto_increment,
628 `description` char(255) default NULL,
629 PRIMARY KEY (`id`),
630 UNIQUE KEY `description` (`description`)
631) ENGINE=InnoDB
632";
633 $query[] = "
634CREATE TABLE `VLANIPv4` (
635 `domain_id` int(10) unsigned NOT NULL,
636 `vlan_id` int(10) unsigned NOT NULL,
637 `ipv4net_id` int(10) unsigned NOT NULL,
638 UNIQUE KEY `network-domain` (`ipv4net_id`,`domain_id`),
639 KEY `VLANIPv4-FK-compound` (`domain_id`,`vlan_id`),
640 CONSTRAINT `VLANIPv4-FK-compound` FOREIGN KEY (`domain_id`, `vlan_id`) REFERENCES `VLANDescription` (`domain_id`, `vlan_id`) ON DELETE CASCADE,
641 CONSTRAINT `VLANIPv4-FK-ipv4net_id` FOREIGN KEY (`ipv4net_id`) REFERENCES `IPv4Network` (`id`) ON DELETE CASCADE
642) ENGINE=InnoDB
643";
644 $query[] = "
645CREATE TABLE `VLANSTRule` (
646 `vst_id` int(10) unsigned NOT NULL,
647 `rule_no` int(10) unsigned NOT NULL,
648 `port_pcre` char(255) NOT NULL,
649 `port_role` enum('access','trunk','uplink','downlink','none') NOT NULL default 'none',
650 `wrt_vlans` char(255) default NULL,
651 `description` char(255) default NULL,
652 UNIQUE KEY `vst-rule` (`vst_id`,`rule_no`),
653 CONSTRAINT `VLANSTRule-FK-vst_id` FOREIGN KEY (`vst_id`) REFERENCES `VLANSwitchTemplate` (`id`) ON DELETE CASCADE
654) ENGINE=InnoDB
655";
656 $query[] = "
657CREATE TABLE `VLANSwitch` (
658 `object_id` int(10) unsigned NOT NULL,
659 `domain_id` int(10) unsigned NOT NULL,
660 `template_id` int(10) unsigned NOT NULL,
661 `mutex_rev` int(10) unsigned NOT NULL default '0',
662 `out_of_sync` enum('yes','no') NOT NULL default 'yes',
663 `last_errno` int(10) unsigned NOT NULL default '0',
664 `last_change` timestamp NOT NULL default '0000-00-00 00:00:00',
665 `last_push_started` timestamp NOT NULL default '0000-00-00 00:00:00',
666 `last_push_finished` timestamp NOT NULL default '0000-00-00 00:00:00',
667 `last_error_ts` timestamp NOT NULL default '0000-00-00 00:00:00',
668 UNIQUE KEY `object_id` (`object_id`),
669 KEY `domain_id` (`domain_id`),
670 KEY `template_id` (`template_id`),
671 KEY `out_of_sync` (`out_of_sync`),
672 KEY `last_errno` (`last_errno`),
673 CONSTRAINT `VLANSwitch-FK-domain_id` FOREIGN KEY (`domain_id`) REFERENCES `VLANDomain` (`id`),
674 CONSTRAINT `VLANSwitch-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`),
675 CONSTRAINT `VLANSwitch-FK-template_id` FOREIGN KEY (`template_id`) REFERENCES `VLANSwitchTemplate` (`id`)
676) ENGINE=InnoDB
677";
678 $query[] = "
679CREATE TABLE `VLANSwitchTemplate` (
680 `id` int(10) unsigned NOT NULL auto_increment,
681 `max_local_vlans` int(10) unsigned default NULL,
682 `description` char(255) default NULL,
683 PRIMARY KEY (`id`),
684 UNIQUE KEY `description` (`description`)
685) ENGINE=InnoDB
686";
687 $query[] = "
688CREATE TABLE `VLANValidID` (
689 `vlan_id` int(10) unsigned NOT NULL default '1',
690 PRIMARY KEY (`vlan_id`)
691) ENGINE=InnoDB
692";
693 $query[] = "SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS";
fa11e5c7 694 for ($i = 1; $i <= 4094; $i++)
a401a680 695 $query[] = "INSERT INTO VLANValidID (vlan_id) VALUES (${i})";
9013f05b 696 $query[] = "UPDATE Config SET varvalue = '0.18.0' WHERE varname = 'DB_VERSION'";
08d1ef24 697 break;
425fd829
DO
698 case '0.18.1':
699 $query = array_merge ($query, reloadDictionary ($batchid));
b6978d62
DO
700 $query[] = "ALTER TABLE Atom ENGINE=InnoDB";
701 $query[] = "ALTER TABLE AttributeMap ENGINE=InnoDB";
702 $query[] = "ALTER TABLE Config ENGINE=InnoDB";
703 $query[] = "ALTER TABLE IPv4Address ENGINE=InnoDB";
704 $query[] = "ALTER TABLE IPv4Allocation ENGINE=InnoDB";
705 $query[] = "ALTER TABLE Molecule ENGINE=InnoDB";
706 $query[] = "ALTER TABLE MountOperation ENGINE=InnoDB";
707 $query[] = "ALTER TABLE PortCompat ENGINE=InnoDB";
708 $query[] = "ALTER TABLE Rack ENGINE=InnoDB";
709 $query[] = "ALTER TABLE RackHistory ENGINE=InnoDB";
710 $query[] = "ALTER TABLE RackObjectHistory ENGINE=InnoDB";
711 $query[] = "ALTER TABLE RackRow ENGINE=InnoDB";
712 $query[] = "ALTER TABLE RackSpace ENGINE=InnoDB";
713 $query[] = "ALTER TABLE Script ENGINE=InnoDB";
735f169f
DO
714 $query[] = "ALTER TABLE AttributeValue DROP FOREIGN KEY `AttributeValue-FK-object_id`";
715 $query[] = "ALTER TABLE AttributeValue ADD CONSTRAINT `AttributeValue-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE";
55267f67
DO
716 $query[] = "ALTER TABLE RackObjectHistory ADD KEY (id)";
717 $query[] = "ALTER TABLE RackObjectHistory ADD CONSTRAINT `RackObjectHistory-FK-object_id` FOREIGN KEY (id) REFERENCES `RackObject` (`id`) ON DELETE CASCADE";
f3552988 718 $query[] = "ALTER TABLE MountOperation ADD CONSTRAINT `MountOperation-FK-object_id` FOREIGN KEY (object_id) REFERENCES RackObject (id) ON DELETE CASCADE";
f7cec175 719 $query[] = "ALTER TABLE RackSpace ADD CONSTRAINT `RackSpace-FK-object_id` FOREIGN KEY (object_id) REFERENCES RackObject (id) ON DELETE CASCADE";
1bcfe894
DO
720 $query[] = "ALTER TABLE Link DROP FOREIGN KEY `Link-FK-a`";
721 $query[] = "ALTER TABLE Link ADD CONSTRAINT `Link-FK-a` FOREIGN KEY (`porta`) REFERENCES `Port` (`id`) ON DELETE CASCADE";
722 $query[] = "ALTER TABLE Link DROP FOREIGN KEY `Link-FK-b`";
723 $query[] = "ALTER TABLE Link ADD CONSTRAINT `Link-FK-b` FOREIGN KEY (`portb`) REFERENCES `Port` (`id`) ON DELETE CASCADE";
724 $query[] = "ALTER TABLE Port DROP FOREIGN KEY `Port-FK-object_id`";
725 $query[] = "ALTER TABLE Port ADD CONSTRAINT `Port-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE";
e7787961
DO
726 $query[] = "ALTER TABLE AttributeMap MODIFY `chapter_id` int(10) unsigned default NULL";
727 $query[] = "ALTER TABLE IPv4Address MODIFY `ip` int(10) unsigned NOT NULL default '0'";
728 $query[] = "ALTER TABLE IPv4Address MODIFY `name` char(255) NOT NULL default ''";
729 $query[] = "ALTER TABLE IPv4Allocation MODIFY `object_id` int(10) unsigned NOT NULL default '0'";
730 $query[] = "ALTER TABLE IPv4Allocation MODIFY `ip` int(10) unsigned NOT NULL default '0'";
731 $query[] = "ALTER TABLE IPv4Allocation MODIFY `name` char(255) NOT NULL default ''";
732 $query[] = "ALTER TABLE IPv4NAT MODIFY `object_id` int(10) unsigned NOT NULL default '0'";
733 $query[] = "ALTER TABLE IPv4NAT MODIFY `proto` enum('TCP','UDP') NOT NULL default 'TCP'";
734 $query[] = "ALTER TABLE IPv4NAT MODIFY `localip` int(10) unsigned NOT NULL default '0'";
735 $query[] = "ALTER TABLE IPv4NAT MODIFY `localport` smallint(5) unsigned NOT NULL default '0'";
736 $query[] = "ALTER TABLE IPv4NAT MODIFY `remoteip` int(10) unsigned NOT NULL default '0'";
737 $query[] = "ALTER TABLE IPv4NAT MODIFY `remoteport` smallint(5) unsigned NOT NULL default '0'";
738 $query[] = "ALTER TABLE IPv4Network MODIFY `ip` int(10) unsigned NOT NULL default '0'";
739 $query[] = "ALTER TABLE IPv4Network MODIFY `mask` int(10) unsigned NOT NULL default '0'";
740 $query[] = "ALTER TABLE Link MODIFY `porta` int(10) unsigned NOT NULL default '0'";
741 $query[] = "ALTER TABLE Link MODIFY `portb` int(10) unsigned NOT NULL default '0'";
742 $query[] = "ALTER TABLE MountOperation MODIFY `object_id` int(10) unsigned NOT NULL default '0'";
743 $query[] = "ALTER TABLE MountOperation MODIFY `ctime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP";
744 $query[] = "ALTER TABLE Port MODIFY `object_id` int(10) unsigned NOT NULL default '0'";
745 $query[] = "ALTER TABLE Port MODIFY `name` char(255) NOT NULL default ''";
746 $query[] = "ALTER TABLE Port MODIFY `type` int(10) unsigned NOT NULL default '0'";
747 $query[] = "ALTER TABLE PortCompat MODIFY `type1` int(10) unsigned NOT NULL default '0'";
748 $query[] = "ALTER TABLE PortCompat MODIFY `type2` int(10) unsigned NOT NULL default '0'";
749 $query[] = "ALTER TABLE RackHistory MODIFY `ctime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP";
750 $query[] = "ALTER TABLE RackObjectHistory MODIFY `ctime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP";
751 $query[] = "ALTER TABLE TagStorage MODIFY `tag_id` int(10) unsigned NOT NULL default '0'";
752 $query[] = "ALTER TABLE UserAccount MODIFY `user_name` char(64) NOT NULL default ''";
425fd829
DO
753 $query[] = "UPDATE Config SET varvalue = '0.18.1' WHERE varname = 'DB_VERSION'";
754 break;
298d2375
DO
755 case '0.18.2':
756 $query = array_merge ($query, reloadDictionary ($batchid));
757 $query[] = "ALTER TABLE Rack ADD CONSTRAINT `Rack-FK-row_id` FOREIGN KEY (row_id) REFERENCES RackRow (id)";
b504972c 758 $query[] = "ALTER TABLE RackRow ADD UNIQUE KEY `name` (name)";
b49a479e
DO
759 $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')";
760 $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 761 $query[] = "UPDATE Config SET varvalue = '0.18.2' WHERE varname = 'DB_VERSION'";
298d2375 762 break;
2582446d 763 case '0.18.3':
0891c041 764 $query = array_merge ($query, reloadDictionary ($batchid));
2582446d 765 $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 766 $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
767 $query[] = "UPDATE Config SET varvalue = '0.18.3' WHERE varname = 'DB_VERSION'";
768 break;
3a387b0d
DO
769 case '0.18.4':
770 $query = array_merge ($query, reloadDictionary ($batchid));
ec523868 771 $query[] = "ALTER TABLE VLANSTRule MODIFY port_role enum('access','trunk','anymode','uplink','downlink','none') NOT NULL default 'none'";
3a387b0d
DO
772 $query[] = "UPDATE Config SET varvalue = '0.18.4' WHERE varname = 'DB_VERSION'";
773 break;
95857b5c 774 case '0.18.5':
1f54e1ba 775 $query = array_merge ($query, reloadDictionary ($batchid));
95857b5c 776 $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 777 $query[] = "ALTER TABLE `IPv4LB` ADD COLUMN `prio` int(10) unsigned DEFAULT NULL AFTER `vs_id`";
1ebbf889 778 $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 779 $query[] = "UPDATE Config SET varvalue = '0.18.5' WHERE varname = 'DB_VERSION'";
f2f95f99
JT
780 break;
781 case '0.18.6':
782 $query = array_merge ($query, reloadDictionary ($batchid));
783 $query[] = "UPDATE Config SET varvalue = '0.18.6' WHERE varname = 'DB_VERSION'";
95857b5c 784 break;
9fb6900d
DO
785 case '0.18.7':
786 $query = array_merge ($query, reloadDictionary ($batchid));
787 $query[] = "UPDATE Config SET varvalue = '0.18.7' WHERE varname = 'DB_VERSION'";
788 break;
d3346ce2 789 case '0.19.0':
0682218d 790 $query = array_merge ($query, reloadDictionary ($batchid));
d3346ce2 791 $query[] = 'ALTER TABLE `File` ADD `thumbnail` LONGBLOB NULL AFTER `atime`';
21ee3351
AA
792 $query[] = "
793CREATE TABLE `IPv6Address` (
794 `ip` binary(16) NOT NULL,
795 `name` char(255) NOT NULL default '',
796 `reserved` enum('yes','no') default NULL,
797 PRIMARY KEY (`ip`)
798) ENGINE=InnoDB
799";
800 $query[] = "
801CREATE TABLE `IPv6Allocation` (
802 `object_id` int(10) unsigned NOT NULL default '0',
803 `ip` binary(16) NOT NULL,
804 `name` char(255) NOT NULL default '',
805 `type` enum('regular','shared','virtual','router') default NULL,
8c7b7381
AA
806 PRIMARY KEY (`object_id`,`ip`),
807 CONSTRAINT `IPv6Allocation-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE
21ee3351
AA
808) ENGINE=InnoDB
809";
810 $query[] = "
811CREATE TABLE `IPv6Network` (
812 `id` int(10) unsigned NOT NULL auto_increment,
813 `ip` binary(16) NOT NULL,
814 `mask` int(10) unsigned NOT NULL,
815 `last_ip` binary(16) NOT NULL,
816 `name` char(255) default NULL,
817 `comment` text,
818 PRIMARY KEY (`id`),
819 UNIQUE KEY `ip` (`ip`,`mask`)
820) ENGINE=InnoDB
821";
822 $query[] = "
823CREATE TABLE `VLANIPv6` (
824 `domain_id` int(10) unsigned NOT NULL,
825 `vlan_id` int(10) unsigned NOT NULL,
826 `ipv6net_id` int(10) unsigned NOT NULL,
827 UNIQUE KEY `network-domain` (`ipv6net_id`,`domain_id`),
828 KEY `VLANIPv6-FK-compound` (`domain_id`,`vlan_id`),
829 CONSTRAINT `VLANIPv6-FK-compound` FOREIGN KEY (`domain_id`, `vlan_id`) REFERENCES `VLANDescription` (`domain_id`, `vlan_id`) ON DELETE CASCADE,
830 CONSTRAINT `VLANIPv6-FK-ipv6net_id` FOREIGN KEY (`ipv6net_id`) REFERENCES `IPv6Network` (`id`) ON DELETE CASCADE
831) ENGINE=InnoDB
9a90adc4
DO
832";
833 $query[] = "
8489d2af
DO
834CREATE TABLE IF NOT EXISTS `ObjectLog` (
835 `id` int(10) NOT NULL AUTO_INCREMENT,
836 `object_id` int(10) NOT NULL,
837 `user` varchar(64) NOT NULL,
9a90adc4
DO
838 `date` datetime NOT NULL,
839 `content` text NOT NULL,
8489d2af 840 PRIMARY KEY (`id`)
9a90adc4 841) ENGINE=InnoDB
0682218d 842";
8489d2af
DO
843 # Now we have the same structure of ObjectLog table, which objectlog.php
844 # could have left. Subsequent column updates will handle any existing data.
845 $query[] = "ALTER TABLE ObjectLog MODIFY COLUMN `id` int(10) unsigned NOT NULL AUTO_INCREMENT";
846 $query[] = "ALTER TABLE ObjectLog MODIFY COLUMN `object_id` int(10) unsigned NOT NULL";
847 $query[] = "ALTER TABLE ObjectLog MODIFY COLUMN `user` char(64) NOT NULL";
848 $query[] = "ALTER TABLE ObjectLog ADD KEY `object_id` (`object_id`)";
849 $query[] = "ALTER TABLE ObjectLog ADD KEY `date` (`date`)";
850 $query[] = "ALTER TABLE ObjectLog ADD CONSTRAINT `ObjectLog-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE";
851 # Now it's the way 0.19.0 is expecting it to be.
0682218d
AD
852 $query[] = "
853CREATE TABLE `ObjectParentCompat` (
854 `parent_objtype_id` int(10) unsigned NOT NULL,
855 `child_objtype_id` int(10) unsigned NOT NULL,
856 UNIQUE KEY `parent_child` (`parent_objtype_id`,`child_objtype_id`)
857) ENGINE=InnoDB
858";
859 $query[] = "
860CREATE TABLE `EntityLink` (
861 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
862 `parent_entity_type` enum('ipv4net','ipv4rspool','ipv4vs','ipv6net','object','rack','user') NOT NULL,
863 `parent_entity_id` int(10) unsigned NOT NULL,
864 `child_entity_type` enum('file','object') NOT NULL,
865 `child_entity_id` int(10) unsigned NOT NULL,
866 PRIMARY KEY (`id`),
867 UNIQUE KEY `EntityLink-unique` (`parent_entity_type`,`parent_entity_id`,`child_entity_type`,`child_entity_id`)
868) ENGINE=InnoDB
21ee3351
AA
869";
870 $query[] = "ALTER TABLE `TagStorage` CHANGE COLUMN `entity_realm` `entity_realm` ENUM('file','ipv4net','ipv4vs','ipv4rspool','object','rack','user','ipv6net') NOT NULL DEFAULT 'object' FIRST";
871 $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 872 $query[] = 'ALTER TABLE Link ADD COLUMN cable char(64) NULL AFTER portb';
3fb61857 873 $query[] = 'ALTER TABLE RackSpace ADD CONSTRAINT `RackSpace-FK-rack_id` FOREIGN KEY (rack_id) REFERENCES Rack (id)';
8c7b7381 874 $query[] = "ALTER TABLE `IPv4Allocation` ADD CONSTRAINT `IPv4Allocation-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE";
61e79d63 875 $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 876 $query[] = "ALTER TABLE `VLANSwitchTemplate` ADD COLUMN `mutex_rev` int(10) NOT NULL AFTER `id`";
09ec2e59 877 $query[] = "ALTER TABLE `VLANSwitchTemplate` ADD COLUMN `saved_by` char(64) NOT NULL AFTER `description`";
0682218d
AD
878 $query[] = "INSERT INTO `Attribute` (`id`, `type`, `name`) VALUES (26,'dict','Hypervisor')";
879 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (29,'no','Yes/No')";
880 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (30,'no','network chassis models')";
881 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (31,'no','server chassis models')";
882 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (32,'no','virtual switch models')";
883 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (33,'no','virtual switch OS type')";
884 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (4,26,29)";
885 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1502,1,NULL)";
886 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1502,2,31)";
887 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1502,3,NULL)";
888 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1502,14,NULL)";
889 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1502,20,NULL)";
890 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1502,21,NULL)";
891 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1502,22,NULL)";
892 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,1,NULL)";
893 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,2,30)";
894 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,3,NULL)";
588c98f8
AD
895 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,4,14)";
896 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,5,NULL)";
0682218d 897 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,14,NULL)";
588c98f8
AD
898 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,16,NULL)";
899 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,17,NULL)";
900 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,18,NULL)";
0682218d
AD
901 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,20,NULL)";
902 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,21,NULL)";
903 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,22,NULL)";
588c98f8 904 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,24,NULL)";
0682218d
AD
905 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1504,3,NULL)";
906 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1504,4,13)";
907 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1504,14,NULL)";
908 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1504,24,NULL)";
909 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1505,14,NULL)";
910 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1506,14,NULL)";
911 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1506,17,NULL)";
912 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1506,18,NULL)";
913 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,1,NULL)";
914 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,2,32)";
915 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,3,NULL)";
916 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,4,33)";
917 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,5,NULL)";
918 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,14,NULL)";
919 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,20,NULL)";
920 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,21,NULL)";
921 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,22,NULL)";
922 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (3,13)";
923 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (4,1504)";
924 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (4,1507)";
925 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1502,4)";
926 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1503,8)";
927 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1505,4)";
928 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1505,1504)";
929 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1505,1506)";
930 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1505,1507)";
931 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1506,1504)";
0682218d 932 $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 933 $query[] = "UPDATE Config SET varvalue = CONCAT(varvalue, ' or {\$typeid_1502} or {\$typeid_1503} or {\$typeid_1504} or {\$typeid_1507}') WHERE varname = 'IPV4OBJ_LISTSRC'";
0682218d 934 $query[] = "UPDATE Config SET varvalue = '8' WHERE varname = 'MASSCOUNT'";
993f5fa3 935 $query[] = "UPDATE RackObject SET label = NULL WHERE label = ''";
81659c05
AD
936 // Move barcode data so the column can be dropped
937 $result = $dbxlink->query ('SELECT id, objtype_id, barcode FROM RackObject WHERE barcode IS NOT NULL');
938 while ($row = $result->fetch (PDO::FETCH_ASSOC))
939 {
940 // Determine if this object type has the 'OEM S/N 1' attribute associated with it, and if it's set
941 $sn_query = "SELECT (SELECT COUNT(*) FROM AttributeMap WHERE objtype_id=${row['objtype_id']} AND attr_id=1) AS AM_count, ";
942 $sn_query .= "(SELECT COUNT(*) FROM AttributeValue WHERE object_id=${row['id']} AND attr_id=1) AS AV_count";
943 $sn_result = $dbxlink->query ($sn_query);
944 $sn_row = $sn_result->fetch (PDO::FETCH_ASSOC);
945 if ($sn_row['AM_count'] == 1 && $sn_row['AV_count'] == 0)
946 {
947 // 'OEM S/N 1' attribute is mapped to this object type, but it is not set. Good!
948 // Copy the barcode value to the attribute.
949 $query[] = "INSERT INTO AttributeValue (`object_id`, `attr_id`, `string_value`) VALUES (${row['id']}, 1, '${row['barcode']}')";
950 }
951 else
952 {
953 // Some other set of circumstances. Not as good!
954 // Copy the barcode value to a new ObjectLog record.
955 $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']}')";
956 }
957 unset ($sn_query, $sn_result, $sn_row);
958 }
959 unset ($result);
960 $query[] = 'ALTER TABLE RackObject DROP COLUMN `barcode`';
961 $query[] = 'ALTER TABLE RackObjectHistory DROP COLUMN `barcode`';
fbeacc34 962 $query[] = 'ALTER TABLE `VLANSwitchTemplate` DROP COLUMN `max_local_vlans`';
81659c05 963 $query[] = "UPDATE Config SET varvalue = '0.19.0' WHERE varname = 'DB_VERSION'";
d3346ce2 964 break;
6d42599c
AA
965 case '0.19.1':
966 $query = array_merge ($query, reloadDictionary ($batchid));
1d5dd3a1
AD
967 $query[] = "ALTER TABLE `Config` CHANGE COLUMN `varvalue` `varvalue` text NOT NULL";
968 $query[] = "ALTER TABLE `UserConfig` CHANGE COLUMN `varvalue` `varvalue` text NOT NULL";
6d42599c
AA
969 $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')";
970 $query[] = "UPDATE Config SET varvalue = '0.19.1' WHERE varname = 'DB_VERSION'";
971 break;
fbbb74fb 972 default:
c4ee2310 973 showError ("executeUpgradeBatch () failed, because batch '${batchid}' isn't defined");
fbbb74fb
DO
974 die;
975 break;
976 }
fbbb74fb 977 $failures = array();
4114697d 978 echo "<tr><th>Executing batch '${batchid}'</th><td>";
fbbb74fb
DO
979 foreach ($query as $q)
980 {
981 $result = $dbxlink->query ($q);
4114697d 982 if ($result == NULL)
758fe24c 983 {
758fe24c
DO
984 $errorInfo = $dbxlink->errorInfo();
985 $failures[] = array ($q, $errorInfo[2]);
986 }
fbbb74fb 987 }
fbbb74fb 988 if (!count ($failures))
4114697d 989 echo "<strong><font color=green>done</font></strong>";
fbbb74fb
DO
990 else
991 {
4114697d 992 echo "<strong><font color=red>The following queries failed:</font></strong><br><pre>";
fbbb74fb
DO
993 foreach ($failures as $f)
994 {
995 list ($q, $i) = $f;
4114697d 996 echo "${q} -- ${i}\n";
fbbb74fb 997 }
4114697d 998 echo "</pre>";
fbbb74fb 999 }
4114697d 1000 echo '</td></tr>';
fbbb74fb
DO
1001}
1002
1003// ******************************************************************
1004//
1005// Execution starts here
1006//
1007// ******************************************************************
1008
5d168fde 1009require_once 'inc/config.php'; // for CODE_VERSION
3fb336f6 1010require_once 'inc/dictionary.php';
5d168fde
DO
1011// Enforce default value for now, releases prior to 0.17.0 didn't support 'httpd' auth source.
1012$user_auth_src = 'database';
fbbb74fb 1013
80138748
DO
1014if (file_exists ('inc/secret.php'))
1015 require_once 'inc/secret.php';
fbbb74fb 1016else
dbbb4f96 1017 die ('<center>There is no working RackTables instance here, <a href="install.php">install</a>?</center>');
fbbb74fb
DO
1018
1019try
1020{
1021 $dbxlink = new PDO ($pdo_dsn, $db_username, $db_password);
1022}
1023catch (PDOException $e)
1024{
1025 die ("Database connection failed:\n\n" . $e->getMessage());
1026}
1027
fbbb74fb
DO
1028// Now we need to be sure that the current user is the administrator.
1029// The rest doesn't matter within this context.
fbbb74fb 1030
120e9ddd 1031function authenticate_admin ($username, $password)
a1f3710a 1032{
43c7895d 1033 global $dbxlink;
4dd08c61
DO
1034 $prepared = $dbxlink->prepare ('SELECT COUNT(*) FROM UserAccount WHERE user_id=1 AND user_name=? AND user_password_hash=?');
1035 if (!$prepared->execute (array ($username, sha1 ($password))))
a1f3710a 1036 die ('SQL query failed in ' . __FUNCTION__);
4dd08c61 1037 $rows = $prepared->fetchAll (PDO::FETCH_NUM);
43c7895d 1038 return $rows[0][0] == 1;
a1f3710a
DO
1039}
1040
c4ee2310
DO
1041// Database version detector. Should behave corretly on any
1042// working dataset a user might have.
1043function getDatabaseVersion ()
1044{
2f5e4db9
DO
1045 global $dbxlink;
1046 $prepared = $dbxlink->prepare ('SELECT varvalue FROM Config WHERE varname = "DB_VERSION" and vartype = "string"');
1047 if (! $prepared->execute())
c4ee2310 1048 {
c4ee2310 1049 $errorInfo = $dbxlink->errorInfo();
2f5e4db9 1050 die (__FUNCTION__ . ': SQL query failed with error ' . $errorInfo[2]);
c4ee2310 1051 }
2f5e4db9 1052 $rows = $prepared->fetchAll (PDO::FETCH_NUM);
c4ee2310
DO
1053 unset ($result);
1054 if (count ($rows) != 1 || !strlen ($rows[0][0]))
1055 die (__FUNCTION__ . ': Cannot guess database version. Config table is present, but DB_VERSION is missing or invalid. Giving up.');
1056 $ret = $rows[0][0];
1057 return $ret;
1058}
1059
1060function showError ($info = '', $location = 'upgrade.php')
1061{
1062 if (preg_match ('/\.php$/', $location))
1063 $location = basename ($location);
1064 elseif ($location != 'N/A')
1065 $location = $location . '()';
1066 echo "<div class=msg_error>An error has occured in [${location}]. ";
1067 if (!strlen ($info))
1068 echo 'No additional information is available.';
1069 else
1070 echo "Additional information:<br><p>\n<pre>\n${info}\n</pre></p>";
2f5e4db9 1071 echo "Go back or try starting from <a href='index.php'>index page</a>.<br></div>\n";
c4ee2310
DO
1072}
1073
204284ba 1074switch ($user_auth_src)
99ee5479 1075{
120e9ddd
DO
1076 case 'database':
1077 case 'ldap': // authenticate against DB as well
1078 if
1079 (
1080 !isset ($_SERVER['PHP_AUTH_USER']) or
1081 !strlen ($_SERVER['PHP_AUTH_USER']) or
1082 !isset ($_SERVER['PHP_AUTH_PW']) or
1083 !strlen ($_SERVER['PHP_AUTH_PW']) or
3737ede3 1084 !authenticate_admin ($_SERVER['PHP_AUTH_USER'], $_SERVER['PHP_AUTH_PW'])
120e9ddd
DO
1085 )
1086 {
1087 header ('WWW-Authenticate: Basic realm="RackTables upgrade"');
1088 header ('HTTP/1.0 401 Unauthorized');
c4ee2310 1089 showError ('You must be authenticated as an administrator to complete the upgrade.');
120e9ddd
DO
1090 die;
1091 }
1092 break; // cleared
1093 case 'httpd':
1094 if
1095 (
1096 !isset ($_SERVER['REMOTE_USER']) or
1097 !strlen ($_SERVER['REMOTE_USER'])
1098 )
1099 {
c4ee2310 1100 showError ('System misconfiguration. The web-server didn\'t authenticate the user, although ought to do.');
120e9ddd
DO
1101 die;
1102 }
1103 break; // cleared
1104 default:
c4ee2310 1105 showError ('authentication source misconfiguration');
120e9ddd 1106 die;
99ee5479 1107}
fbbb74fb 1108
5f016d39
DO
1109?>
1110<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
1111<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
2cf36003 1112<head><title>RackTables upgrade script</title>
2f5e4db9 1113<link rel=stylesheet type='text/css' href='css/pi.css' />
5f016d39
DO
1114</head>
1115<body>
1116<h1>Platform check status</h1>
1117<?php
1118
1119if (!platform_is_ok())
1120 die ('</body></html>');
1121
1122echo '<h1>Upgrade status</h1>';
fbbb74fb 1123$dbver = getDatabaseVersion();
5f016d39 1124echo '<table border=1 cellpadding=5>';
4114697d
DO
1125echo "<tr><th>Current status</th><td>Data version: ${dbver}<br>Code version: " . CODE_VERSION . "</td></tr>\n";
1126
1127$path = getDBUpgradePath ($dbver, CODE_VERSION);
1128if ($path === NULL)
758fe24c 1129{
4114697d 1130 echo "<tr><th>Upgrade path</th><td><font color=red>not found</font></td></tr>\n";
d74ae24c
DO
1131 echo "<tr><th>Summary</th><td>Check README for more information. RackTables releases prior to 0.16.4 ";
1132 echo "must be upgraded to 0.16.4 first.</td></tr>\n";
758fe24c 1133}
4114697d 1134else
5f4027b8 1135{
4114697d
DO
1136 if (!count ($path))
1137 echo "<tr><th>Summary</th><td>Come back later.</td></tr>\n";
1138 else
1139 {
1140 echo "<tr><th>Upgrade path</th><td>${dbver} &rarr; " . implode (' &rarr; ', $path) . "</td></tr>\n";
1141 foreach ($path as $batchid)
1142 {
1143 executeUpgradeBatch ($batchid);
1144 if (isset ($relnotes[$batchid]))
5ae6d365 1145 echo "<tr><th>Release notes for ${batchid}</th><td><pre>" . $relnotes[$batchid] . "</pre></td></tr>\n";
4114697d 1146 }
790a60e8 1147 echo "<tr><th>Summary</th><td>Upgrade complete, it is Ok to <a href='index.php'>enter</a> the system.</td></tr>\n";
4114697d 1148 }
5f4027b8 1149}
4114697d 1150echo '</table>';
5f016d39 1151echo '</body></html>';
fbbb74fb
DO
1152
1153?>