r3813 note the last bugfix
[racktables] / 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
5ae6d365 38,
90b96ff6
DO
39);
40
fbbb74fb
DO
41// At the moment we assume, that for any two releases we can
42// sequentally execute all batches, that separate them, and
43// nothing will break. If this changes one day, the function
44// below will have to generate smarter upgrade paths, while
45// the upper layer will remain the same.
46// Returning an empty array means that no upgrade is necessary.
4114697d 47// Returning NULL indicates an error.
fbbb74fb
DO
48function getDBUpgradePath ($v1, $v2)
49{
a6f83a72
DO
50 $versionhistory = array
51 (
b3f866fc 52 '0.16.4',
64347dcf 53 '0.16.5',
90b96ff6 54 '0.16.6',
30d0a2a3 55 '0.17.0',
4563cecb 56 '0.17.1',
7b1a3a72 57 '0.17.2',
9e51318b 58 '0.17.3',
958ac06d 59 '0.17.4',
9f572fb5 60 '0.17.5',
63811a09 61 '0.17.6',
026a79ee 62 '0.17.7',
3540d15c 63 '0.17.8',
1e81ad97 64 '0.17.9',
f32167d2 65 '0.17.10',
4a4a5440 66 '0.17.11',
9013f05b 67 '0.18.0',
425fd829 68 '0.18.1',
298d2375 69 '0.18.2',
a6f83a72 70 );
120e9ddd
DO
71 if (!in_array ($v1, $versionhistory) or !in_array ($v2, $versionhistory))
72 return NULL;
fbbb74fb 73 $skip = TRUE;
4114697d 74 $path = NULL;
fbbb74fb
DO
75 // Now collect all versions > $v1 and <= $v2
76 foreach ($versionhistory as $v)
77 {
4114697d 78 if ($skip and $v == $v1)
fbbb74fb
DO
79 {
80 $skip = FALSE;
4114697d 81 $path = array();
fbbb74fb
DO
82 continue;
83 }
84 if ($skip)
85 continue;
86 $path[] = $v;
87 if ($v == $v2)
88 break;
89 }
90 return $path;
91}
92
90b96ff6
DO
93// Upgrade batches are named exactly as the release where they first appear.
94// That is simple, but seems sufficient for beginning.
fbbb74fb
DO
95function executeUpgradeBatch ($batchid)
96{
97 $query = array();
ca3d68bd 98 global $dbxlink;
fbbb74fb
DO
99 switch ($batchid)
100 {
64347dcf
DO
101 case '0.16.5':
102 $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')";
103 $query[] = "update Config set varvalue = '0.16.5' where varname = 'DB_VERSION'";
104 break;
90b96ff6
DO
105 case '0.16.6':
106 $query[] = "update Config set varvalue = '0.16.6' where varname = 'DB_VERSION'";
107 break;
30d0a2a3 108 case '0.17.0':
e1ae3fb4
AD
109 // create tables for storing files (requires InnoDB support)
110 if (!isInnoDBSupported ())
111 {
5d168fde 112 showFailure ("Cannot upgrade because InnoDB tables are not supported by your MySQL server. See the README for details.", __FILE__);
f3c50166 113 die;
e1ae3fb4 114 }
f76c4197
DY
115
116 $query[] = "alter table Chapter change chapter_no id int(10) unsigned NOT NULL auto_increment";
117 $query[] = "alter table Chapter change chapter_name name char(128) NOT NULL";
118 $query[] = "alter table Chapter drop key chapter_name";
119 $query[] = "alter table Chapter add UNIQUE KEY name (name)";
120 $query[] = "alter table Attribute change attr_id id int(10) unsigned NOT NULL auto_increment";
121 $query[] = "alter table Attribute change attr_type type enum('string','uint','float','dict') default NULL";
122 $query[] = "alter table Attribute change attr_name name char(64) default NULL";
123 $query[] = "alter table Attribute drop key attr_name";
124 $query[] = "alter table Attribute add UNIQUE KEY name (name)";
125 $query[] = "alter table AttributeMap change chapter_no chapter_id int(10) unsigned NOT NULL";
126 $query[] = "alter table Dictionary change chapter_no chapter_id int(10) unsigned NOT NULL";
3fb336f6 127 // Only after the above call it is Ok to use reloadDictionary()
ca3d68bd 128 $query = array_merge ($query, reloadDictionary ($batchid));
f3c50166 129 // schema changes for file management
e1ae3fb4
AD
130 $query[] = "
131CREATE TABLE `File` (
132 `id` int(10) unsigned NOT NULL auto_increment,
133 `name` char(255) NOT NULL,
134 `type` char(255) NOT NULL,
135 `size` int(10) unsigned NOT NULL,
136 `ctime` datetime NOT NULL,
137 `mtime` datetime NOT NULL,
138 `atime` datetime NOT NULL,
139 `contents` longblob NOT NULL,
140 `comment` text,
13edfa1c
AD
141 PRIMARY KEY (`id`),
142 UNIQUE KEY `name` (`name`)
e1ae3fb4
AD
143) ENGINE=InnoDB";
144 $query[] = "
145CREATE TABLE `FileLink` (
146 `id` int(10) unsigned NOT NULL auto_increment,
147 `file_id` int(10) unsigned NOT NULL,
148 `entity_type` enum('ipv4net','ipv4rspool','ipv4vs','object','rack','user') NOT NULL default 'object',
149 `entity_id` int(10) NOT NULL,
150 PRIMARY KEY (`id`),
af721881 151 UNIQUE KEY `FileLink-unique` (`file_id`,`entity_type`,`entity_id`),
e1ae3fb4
AD
152 KEY `FileLink-file_id` (`file_id`),
153 CONSTRAINT `FileLink-File_fkey` FOREIGN KEY (`file_id`) REFERENCES `File` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
154) ENGINE=InnoDB";
155 $query[] = "ALTER TABLE TagStorage MODIFY COLUMN target_realm enum('file','ipv4net','ipv4rspool','ipv4vs','object','rack','user') NOT NULL default 'object'";
f3c50166 156
f76c4197 157 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (24,'no','network security models')";
9730d09f 158 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (25,'no','wireless models')";
f76c4197
DY
159 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,1,0)";
160 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,2,24)";
161 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,3,0)";
162 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,5,0)";
163 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,14,0)";
164 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,16,0)";
165 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,17,0)";
166 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,18,0)";
167 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,20,0)";
168 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,21,0)";
169 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,22,0)";
170 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,24,0)";
9730d09f
DO
171 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (965,1,0)";
172 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (965,3,0)";
173 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (965,2,25)";
706ce117
DO
174 $query[] = 'alter table IPBonds rename to IPv4Allocation';
175 $query[] = 'alter table PortForwarding rename to IPv4NAT';
176 $query[] = 'alter table IPRanges rename to IPv4Network';
177 $query[] = 'alter table IPAddress rename to IPv4Address';
178 $query[] = 'alter table IPLoadBalancer rename to IPv4LB';
4114697d 179 $query[] = 'alter table IPRSPool rename to IPv4RSPool';
706ce117 180 $query[] = 'alter table IPRealServer rename to IPv4RS';
4114697d 181 $query[] = 'alter table IPVirtualService rename to IPv4VS';
120e9ddd
DO
182 $query[] = "alter table TagStorage change column target_realm entity_realm enum('file','ipv4net','ipv4vs','ipv4rspool','object','rack','user') NOT NULL default 'object'";
183 $query[] = 'alter table TagStorage change column target_id entity_id int(10) unsigned NOT NULL';
184 $query[] = 'alter table TagStorage drop key entity_tag';
185 $query[] = 'alter table TagStorage drop key target_id';
186 $query[] = 'alter table TagStorage add UNIQUE KEY `entity_tag` (`entity_realm`,`entity_id`,`tag_id`)';
187 $query[] = 'alter table TagStorage add KEY `entity_id` (`entity_id`)';
37e59768
DO
188 $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')";
189 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('PREVIEW_TEXT_ROWS','25','uint','yes','no','Rows for text file preview')";
190 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('PREVIEW_TEXT_COLS','80','uint','yes','no','Columns for text file preview')";
191 $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 192 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('VENDOR_SIEVE','','string','yes','no','Vendor sieve configuration')";
073ed463
DO
193 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('IPV4LB_LISTSRC','{\$typeid_4}','string','yes','no','List source: IPv4 load balancers')";
194 $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')";
195 $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')";
196 $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')";
197 $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 198 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('RACKS_PER_ROW','12','unit','yes','no','Racks per row')";
590e1281 199 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('FILTER_PREDICATE_SIEVE','','string','yes','no','Predicate sieve regex(7)')";
5496c89f
DO
200 $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)')";
201 $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')";
202 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('FILTER_SUGGEST_TAGS','yes','string','no','no','Suggest tags in list filter')";
203 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('FILTER_SUGGEST_PREDICATES','yes','string','no','no','Suggest predicates in list filter')";
204 $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
205 $query[] = "delete from Config where varname = 'USER_AUTH_SRC'";
206 $query[] = "delete from Config where varname = 'COOKIE_TTL'";
207 $query[] = "delete from Config where varname = 'rtwidth_0'";
208 $query[] = "delete from Config where varname = 'rtwidth_1'";
209 $query[] = "delete from Config where varname = 'rtwidth_2'";
c6bc0ac5
DO
210 $query[] = "delete from Config where varname = 'NAMEFUL_OBJTYPES'";
211 $query[] = "delete from Config where varname = 'REQUIRE_ASSET_TAG_FOR'";
212 $query[] = "delete from Config where varname = 'IPV4_PERFORMERS'";
213 $query[] = "delete from Config where varname = 'NATV4_PERFORMERS'";
dbb33805 214 $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
215 $result = $dbxlink->query ("select user_id, user_name, user_realname from UserAccount where user_enabled = 'no'");
216 while ($row = $result->fetch (PDO::FETCH_ASSOC))
217 $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'";
218 $query[] = "update Script set script_text = NULL where script_name = 'RackCodeCache'";
219 unset ($result);
79b8ad1e 220 $query[] = "alter table UserAccount drop column user_enabled";
f76c4197 221
10bac82a
DY
222 $query[] = "CREATE TABLE RackRow ( id int(10) unsigned NOT NULL auto_increment, name char(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM";
223
9f14a7ef
DY
224 $result = $dbxlink->query ("select dict_key, dict_value from Dictionary where chapter_no = 3");
225 while($row = $result->fetch(PDO::FETCH_NUM))
10bac82a 226 $query[] = "insert into RackRow set id=${row[0]}, name='${row[1]}'";
ee286837 227 unset ($result);
f76c4197 228 $query[] = "delete from Dictionary where chapter_id = 3";
c4d0dc30 229 $query[] = "delete from Chapter where id = 3";
9133d2c5
DO
230 $query[] = "
231CREATE TABLE `LDAPCache` (
232 `presented_username` char(64) NOT NULL,
233 `successful_hash` char(40) NOT NULL,
234 `first_success` timestamp NOT NULL default CURRENT_TIMESTAMP,
235 `last_retry` timestamp NOT NULL default '0000-00-00 00:00:00',
236 `displayed_name` char(128) default NULL,
237 `memberof` text,
238 UNIQUE KEY `presented_username` (`presented_username`),
239 KEY `scanidx` (`presented_username`,`successful_hash`)
240) ENGINE=InnoDB;";
3827da34 241 $query[] = "alter table UserAccount modify column user_password_hash char(40) NULL";
aa9a0fb4
DO
242 $query[] = 'ALTER TABLE Rack DROP COLUMN deleted';
243 $query[] = 'ALTER TABLE RackHistory DROP COLUMN deleted';
244 $query[] = 'ALTER TABLE RackObject DROP COLUMN deleted';
245 $query[] = 'ALTER TABLE RackObjectHistory DROP COLUMN deleted';
2fb9d280
DO
246 // Can't be added straight due to many duplicates, even in "dictbase" data.
247 $result = $dbxlink->query ('SELECT type1, type2, count(*) - 1 as excess FROM PortCompat GROUP BY type1, type2 HAVING excess > 0');
248 while ($row = $result->fetch (PDO::FETCH_ASSOC))
249 $query[] = "DELETE FROM PortCompat WHERE type1 = ${row['type1']} AND type2 = ${row['type2']} limit ${row['excess']}";
250 unset ($result);
251 $query[] = 'ALTER TABLE PortCompat DROP KEY type1';
67d8a969 252 $query[] = 'ALTER TABLE PortCompat ADD UNIQUE `type1_2` (type1, type2)';
f76c4197
DY
253 $query[] = "UPDATE Config SET varvalue = '0.17.0' WHERE varname = 'DB_VERSION'";
254
b3f866fc 255 break;
4563cecb 256 case '0.17.1':
8b200a9c 257 $query[] = "ALTER TABLE Dictionary DROP KEY `chap_to_key`";
ca3d68bd 258 $query = array_merge ($query, reloadDictionary ($batchid));
4563cecb
DO
259 // Token set has changed, so the cache isn't valid any more.
260 $query[] = "UPDATE Script SET script_text = NULL WHERE script_name = 'RackCodeCache'";
261 $query[] = "UPDATE Config SET varvalue = '0.17.1' WHERE varname = 'DB_VERSION'";
7d4ea62b 262 break;
7b1a3a72 263 case '0.17.2':
7b1a3a72 264 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (26,'no','fibre channel switch models')";
ca3d68bd 265 $query = array_merge ($query, reloadDictionary ($batchid));
7c537f33 266 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1055,2,26)";
49b605d9 267 $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
268 // wipe irrelevant records (ticket:250)
269 $query[] = "DELETE FROM TagStorage WHERE entity_realm = 'file' AND entity_id NOT IN (SELECT id FROM File)";
f06fe423 270 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('IPV4_ENABLE_KNIGHT','yes','string','no','no','Enable IPv4 knight feature')";
99ab184f 271 $query[] = "ALTER TABLE IPv4Network ADD COLUMN comment text AFTER name";
5163cd3a 272 $query[] = "ALTER TABLE Port ADD INDEX comment (reservation_comment)";
029a14bc
DO
273 $query[] = "ALTER TABLE Port DROP KEY l2address"; // UNIQUE
274 $query[] = "ALTER TABLE Port ADD KEY (l2address)"; // not UNIQUE
948666cc
DO
275 $query[] = "ALTER TABLE Port DROP KEY object_id";
276 $query[] = "ALTER TABLE Port ADD UNIQUE KEY per_object (object_id, name, type)";
74aee2dc
DO
277 $query[] = "INSERT INTO PortCompat (type1, type2) VALUES (20,1083)";
278 $query[] = "INSERT INTO PortCompat (type1, type2) VALUES (21,1083)";
279 $query[] = "INSERT INTO PortCompat (type1, type2) VALUES (1077,1077)";
280 $query[] = "INSERT INTO PortCompat (type1, type2) VALUES (1083,20)";
281 $query[] = "INSERT INTO PortCompat (type1, type2) VALUES (1083,21)";
282 $query[] = "INSERT INTO PortCompat (type1, type2) VALUES (1083,1083)";
1c4830dc 283 $query[] = "INSERT INTO PortCompat (type1, type2) VALUES (1087,1087)";
50e02490
DO
284 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (27,'no','PDU models')";
285 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (2,2,27)";
7b1a3a72
AD
286 $query[] = "UPDATE Config SET varvalue = '0.17.2' WHERE varname = 'DB_VERSION'";
287 break;
9e51318b
DO
288 case '0.17.3':
289 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('TAGS_TOPLIST_SIZE','50','uint','yes','no','Tags top list size')";
290 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('TAGS_QUICKLIST_SIZE','20','uint','no','no','Tags quick list size')";
291 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('TAGS_QUICKLIST_THRESHOLD','50','uint','yes','no','Tags quick list threshold')";
87601bbc 292 $query[] = "ALTER TABLE AttributeMap MODIFY COLUMN chapter_id int(10) unsigned NULL";
7028a42c 293 $query[] = "UPDATE AttributeMap SET chapter_id = NULL WHERE attr_id IN (SELECT id FROM Attribute WHERE type != 'dict')";
a013838b
DO
294 // ticket:239
295 $query[] = 'UPDATE AttributeValue SET uint_value = 1018 WHERE uint_value = 731 AND attr_id IN (SELECT attr_id FROM AttributeMap WHERE chapter_id = 12)';
296 $query[] = 'DELETE FROM Dictionary WHERE dict_key = 731';
084aca6c
DO
297 $query = array_merge ($query, reloadDictionary ($batchid));
298 $query[] = "UPDATE Config SET vartype='uint' WHERE varname='RACKS_PER_ROW'";
f44fdef9 299 $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
300 $query[] = "UPDATE Config SET varvalue = '0.17.3' WHERE varname = 'DB_VERSION'";
301 break;
958ac06d
DO
302 case '0.17.4':
303 $query[] = "ALTER TABLE Link ENGINE=InnoDB";
304 $query[] = "ALTER TABLE Port ENGINE=InnoDB";
4d87feaf
DO
305 $query[] = "ALTER TABLE IPv4RS ENGINE=InnoDB";
306 $query[] = "ALTER TABLE IPv4RSPool ENGINE=InnoDB";
cafd4cf3
DO
307 $query[] = "ALTER TABLE AttributeValue ENGINE=InnoDB";
308 $query[] = "ALTER TABLE RackObject ENGINE=InnoDB";
309 $query[] = "ALTER TABLE IPv4NAT ENGINE=InnoDB";
310 $query[] = "ALTER TABLE IPv4LB ENGINE=InnoDB";
311 $query[] = "ALTER TABLE IPv4VS ENGINE=InnoDB";
4d87feaf 312 $query[] = "DELETE FROM IPv4RS WHERE rspool_id NOT IN (SELECT id FROM IPv4RSPool)";
958ac06d 313 $query[] = "ALTER TABLE Link ADD CONSTRAINT `Link-FK-b` FOREIGN KEY (portb) REFERENCES Port (id)";
cafd4cf3 314 $query[] = "ALTER TABLE Link ADD CONSTRAINT `Link-FK-a` FOREIGN KEY (porta) REFERENCES Port (id)";
4d87feaf 315 $query[] = "ALTER TABLE IPv4RS ADD CONSTRAINT `IPv4RS-FK` FOREIGN KEY (rspool_id) REFERENCES IPv4RSPool (id) ON DELETE CASCADE";
cafd4cf3
DO
316 $query[] = "ALTER TABLE AttributeValue ADD CONSTRAINT `AttributeValue-FK-object_id` FOREIGN KEY (object_id) REFERENCES RackObject (id)";
317 $query[] = "ALTER TABLE IPv4NAT ADD CONSTRAINT `IPv4NAT-FK-object_id` FOREIGN KEY (object_id) REFERENCES RackObject (id)";
318 $query[] = "ALTER TABLE Port ADD CONSTRAINT `Port-FK-object_id` FOREIGN KEY (object_id) REFERENCES RackObject (id)";
319 $query[] = "ALTER TABLE IPv4LB ADD CONSTRAINT `IPv4LB-FK-rspool_id` FOREIGN KEY (rspool_id) REFERENCES IPv4RSPool (id)";
320 $query[] = "ALTER TABLE IPv4LB ADD CONSTRAINT `IPv4LB-FK-object_id` FOREIGN KEY (object_id) REFERENCES RackObject (id)";
321 $query[] = "ALTER TABLE IPv4LB ADD CONSTRAINT `IPv4LB-FK-vs_id` FOREIGN KEY (vs_id) REFERENCES IPv4VS (id)";
e49d8a77 322 $query = array_merge ($query, reloadDictionary ($batchid));
958ac06d
DO
323 $query[] = "UPDATE Config SET varvalue = '0.17.4' WHERE varname = 'DB_VERSION'";
324 break;
9f572fb5
DO
325 case '0.17.5':
326 $query[] = "ALTER TABLE TagTree ENGINE=InnoDB";
327 $query[] = "ALTER TABLE TagStorage ENGINE=InnoDB";
328 $query[] = "ALTER TABLE TagStorage ADD CONSTRAINT `TagStorage-FK-tag_id` FOREIGN KEY (tag_id) REFERENCES TagTree (id)";
329 $query[] = "ALTER TABLE TagTree ADD CONSTRAINT `TagTree-K-parent_id` FOREIGN KEY (parent_id) REFERENCES TagTree (id)";
2400d7ec
DO
330 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (21,1195)';
331 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (22,1196)';
332 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (23,1196)';
333 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (20,1195)';
334 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (25,1202)';
335 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (26,1202)';
336 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (27,1204)';
337 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (28,1204)';
338 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1083,1195)';
339 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1084,1084)';
340 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1195,20)';
341 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1195,21)';
342 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1195,1083)';
343 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1195,1195)';
344 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1196,22)';
345 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1196,23)';
346 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1196,1196)';
347 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1197,1197)';
348 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1198,1199)';
349 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1199,1198)';
350 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1200,1200)';
351 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1201,1201)';
352 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1202,25)';
353 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1202,26)';
354 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1202,1202)';
355 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1203,1203)';
356 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1204,27)';
357 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1204,28)';
358 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1204,1204)';
359 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1205,1205)';
360 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1206,1207)';
361 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1207,1206)';
93a83f51 362 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1316,1316)';
bdc91a5c
DO
363 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (16, 1322)';
364 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1322, 16)';
365 $query[] = 'DELETE FROM PortCompat WHERE type1 = 16 AND type2 = 16';
2400d7ec
DO
366 for ($i = 1209; $i <= 1300; $i++)
367 $query[] = "INSERT INTO PortCompat (type1, type2) VALUES (${i}, ${i})";
d9b88ea0 368 $query[] = "
09d6afa4 369CREATE TABLE `PortInnerInterface` (
9173281a 370 `id` int(10) unsigned NOT NULL,
d9b88ea0
DO
371 `iif_name` char(16) NOT NULL,
372 PRIMARY KEY (`id`),
9173281a 373 UNIQUE KEY `iif_name` (`iif_name`)
d9b88ea0 374) ENGINE=InnoDB";
2400d7ec
DO
375 $query[] = "INSERT INTO `PortInnerInterface` VALUES (1,'hardwired')";
376 $query[] = "INSERT INTO `PortInnerInterface` VALUES (2,'SFP-100')";
377 $query[] = "INSERT INTO `PortInnerInterface` VALUES (3,'GBIC')";
378 $query[] = "INSERT INTO `PortInnerInterface` VALUES (4,'SFP-1000')";
379 $query[] = "INSERT INTO `PortInnerInterface` VALUES (5,'XENPAK')";
380 $query[] = "INSERT INTO `PortInnerInterface` VALUES (6,'X2')";
381 $query[] = "INSERT INTO `PortInnerInterface` VALUES (7,'XPAK')";
382 $query[] = "INSERT INTO `PortInnerInterface` VALUES (8,'XFP')";
383 $query[] = "INSERT INTO `PortInnerInterface` VALUES (9,'SFP+')";
d9b88ea0 384 $query[] = "
09d6afa4 385CREATE TABLE `PortInterfaceCompat` (
d9b88ea0
DO
386 `iif_id` int(10) unsigned NOT NULL,
387 `oif_id` int(10) unsigned NOT NULL,
388 UNIQUE KEY `pair` (`iif_id`,`oif_id`),
2400d7ec 389 CONSTRAINT `PortInterfaceCompat-FK-iif_id` FOREIGN KEY (`iif_id`) REFERENCES `PortInnerInterface` (`id`)
d9b88ea0 390) ENGINE=InnoDB";
c76cfa0d
DO
391 $query[] = "ALTER TABLE Port ADD COLUMN iif_id int unsigned NOT NULL AFTER name"; // will set iif_id to 0
392 $query[] = "UPDATE Port SET iif_id = 2 WHERE type = 1208";
393 $query[] = "UPDATE Port SET iif_id = 3 WHERE type = 1078";
394 $query[] = "UPDATE Port SET iif_id = 4 WHERE type = 1077";
395 $query[] = "UPDATE Port SET iif_id = 5 WHERE type = 1079";
396 $query[] = "UPDATE Port SET iif_id = 6 WHERE type = 1080";
397 $query[] = "UPDATE Port SET iif_id = 7 WHERE type = 1081";
398 $query[] = "UPDATE Port SET iif_id = 8 WHERE type = 1082";
399 $query[] = "UPDATE Port SET iif_id = 9 WHERE type = 1084";
400 $query[] = "UPDATE Port SET iif_id = 1 WHERE iif_id = 0";
08aa3467
DO
401 $query[] = 'ALTER TABLE Port ADD UNIQUE `object_iif_oif_name` (object_id, iif_id, type, name)';
402 $query[] = 'ALTER TABLE Port DROP KEY `per_object`';
2400d7ec
DO
403 $base1000 = array (24, 34, 1202, 1203, 1204, 1205, 1206, 1207);
404 $base10000 = array (30, 35, 36, 37, 38, 39, 40);
405 $PICdata = array
406 (
bdc91a5c 407 1 => array (16, 19, 24, 29, 31, 33, 446, 681, 682, 1322),
2400d7ec
DO
408 2 => array (1208, 1195, 1196, 1197, 1198, 1199, 1200, 1201),
409 3 => array_merge (array (1078), $base1000),
410 4 => array_merge (array (1077), $base1000),
411 5 => array_merge (array (1079), $base10000),
412 6 => array_merge (array (1080), $base10000),
413 7 => array_merge (array (1081), $base10000),
414 8 => array_merge (array (1082), $base10000),
415 9 => array_merge (array (1084), $base10000),
416 );
417 // make sure all IIF/OIF pairs referenced from Port exist in PortInterfaceCompat before enabling FK
418 // iif_id doesn't exist at this point
419 $result = $dbxlink->query ('SELECT DISTINCT type FROM Port WHERE type NOT IN (1208, 1078, 1077, 1079, 1080, 1081, 1082, 1084)');
420 while ($row = $result->fetch (PDO::FETCH_ASSOC))
421 if (FALSE === array_search ($row['type'], $PICdata[1]))
422 array_push ($PICdata[1], $row['type']);
423 unset ($result);
424 foreach ($PICdata as $iif_id => $oif_ids)
425 foreach ($oif_ids as $oif_id)
426 $query[] = "INSERT INTO PortInterfaceCompat (iif_id, oif_id) VALUES (${iif_id}, ${oif_id})";
c76cfa0d 427 $query[] = "ALTER TABLE Port ADD CONSTRAINT `Port-FK-iif-oif` FOREIGN KEY (`iif_id`, `type`) REFERENCES `PortInterfaceCompat` (`iif_id`, `oif_id`)";
bdc91a5c 428 $query[] = 'UPDATE Port SET type = 1322 WHERE type = 16 AND (SELECT objtype_id FROM RackObject WHERE id = object_id) IN (2, 12)';
2400d7ec
DO
429 $query = array_merge ($query, reloadDictionary ($batchid));
430 $query[] = "DELETE FROM Config WHERE varname = 'default_port_type'";
431 $query[] = "INSERT INTO Config VALUES ('DEFAULT_PORT_IIF_ID','1','uint','no','no','Default port inner interface ID')";
432 $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 433 $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 434 $query[] = "UPDATE Chapter SET name = 'PortOuterInterface' WHERE id = 2";
fbcbb4ee
DO
435 // remap refs to duplicate records, which will be discarded (ticket:286)
436 $query[] = 'UPDATE AttributeValue SET uint_value = 147 WHERE uint_value = 1020 AND attr_id = 2';
437 $query[] = 'UPDATE AttributeValue SET uint_value = 377 WHERE uint_value = 1021 AND attr_id = 2';
e8ab58e8 438 $query[] = 'INSERT INTO AttributeMap (objtype_id, attr_id) VALUES (2, 1), (2, 3), (2, 5)';
9f572fb5
DO
439 $query[] = "UPDATE Config SET varvalue = '0.17.5' WHERE varname = 'DB_VERSION'";
440 break;
63811a09
DO
441 case '0.17.6':
442 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (28,'no','Voice/video hardware')";
443 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1323,1,NULL)";
444 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1323,2,28)";
445 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1323,3,NULL)";
446 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1323,5,NULL)";
cd3775e9 447 $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 448 $query = array_merge ($query, reloadDictionary ($batchid));
63811a09
DO
449 $query[] = "UPDATE Config SET varvalue = '0.17.6' WHERE varname = 'DB_VERSION'";
450 break;
026a79ee
DO
451 case '0.17.7':
452 $query[] = "UPDATE Config SET varvalue = '0.17.7' WHERE varname = 'DB_VERSION'";
453 break;
3540d15c 454 case '0.17.8':
1e81ad97
DO
455 $query = array_merge ($query, reloadDictionary ($batchid));
456 $query[] = "ALTER TABLE TagTree DROP COLUMN valid_realm";
457 $query[] = "UPDATE Config SET varvalue = '0.17.8' WHERE varname = 'DB_VERSION'";
458 break;
459 case '0.17.9':
45833307 460 $query[] = "ALTER table Config add `is_userdefined` enum('yes','no') NOT NULL default 'no' AFTER `is_hidden`";
3540d15c
DY
461 $query[] = "
462CREATE TABLE `UserConfig` (
463 `varname` char(32) NOT NULL,
464 `varvalue` char(255) NOT NULL,
465 `user` char(64) NOT NULL,
466 UNIQUE KEY `user_varname` (`user`,`varname`)
467) TYPE=InnoDB";
1e81ad97
DO
468 $query[] = "UPDATE Config SET is_userdefined = 'yes' WHERE varname IN
469(
470'MASSCOUNT',
471'MAXSELSIZE',
472'ROW_SCALE',
473'PORTS_PER_ROW',
474'IPV4_ADDRS_PER_PAGE',
475'DEFAULT_RACK_HEIGHT',
476'DEFAULT_SLB_VS_PORT',
477'DEFAULT_SLB_RS_PORT',
478'DETECT_URLS',
479'RACK_PRESELECT_THRESHOLD',
480'DEFAULT_IPV4_RS_INSERVICE',
481'DEFAULT_OBJECT_TYPE',
482'SHOW_EXPLICIT_TAGS',
483'SHOW_IMPLICIT_TAGS',
484'SHOW_AUTOMATIC_TAGS',
485'IPV4_AUTO_RELEASE',
486'SHOW_LAST_TAB',
487'EXT_IPV4_VIEW',
488'TREE_THRESHOLD',
489'ADDNEW_AT_TOP',
490'IPV4_TREE_SHOW_USAGE',
491'PREVIEW_TEXT_MAXCHARS',
492'PREVIEW_TEXT_ROWS',
493'PREVIEW_TEXT_COLS',
494'PREVIEW_IMAGE_MAXPXS',
495'VENDOR_SIEVE',
496'RACKS_PER_ROW'
497)";
9013f05b
DO
498 $query[] = "UPDATE Config SET varvalue = '0.17.9' WHERE varname = 'DB_VERSION'";
499 break;
f32167d2
DO
500 case '0.17.10':
501 $query = array_merge ($query, reloadDictionary ($batchid));
4368cc45 502 $query[] = "ALTER TABLE MountOperation ADD KEY (object_id)";
2926b1cb 503 $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 504 $query[] = "UPDATE Config SET varvalue = '0.17.10' WHERE varname = 'DB_VERSION'";
d80036ba 505 break;
a1fc539a
RF
506 case '0.17.11':
507 $query = array_merge ($query, reloadDictionary ($batchid));
508 $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 509 $query[] = "DELETE AttributeValue FROM AttributeValue JOIN Attribute where AttributeValue.attr_id = Attribute.id AND Attribute.type = 'dict' AND AttributeValue.uint_value = 0";
a1fc539a 510 $query[] = "UPDATE Config SET varvalue = '0.17.11' WHERE varname = 'DB_VERSION'";
59e0658a 511 break;
9013f05b 512 case '0.18.0':
ad9da675 513 $query = array_merge ($query, reloadDictionary ($batchid));
8bb69a06
DO
514 $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')";
515 $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')";
516 $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')";
517 $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
518 $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')";
519 $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')";
520 $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 521 $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 522 $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 523 $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 524 $query[] = "ALTER TABLE IPv4Network ENGINE=InnoDB";
22fdebff
DO
525 $query[] = "SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0";
526 $query[] = "
527CREATE TABLE `CachedPAV` (
528 `object_id` int(10) unsigned NOT NULL,
529 `port_name` char(255) NOT NULL,
530 `vlan_id` int(10) unsigned NOT NULL default '0',
531 PRIMARY KEY (`object_id`,`port_name`,`vlan_id`),
532 KEY `vlan_id` (`vlan_id`),
533 CONSTRAINT `CachedPAV-FK-object-port` FOREIGN KEY (`object_id`, `port_name`) REFERENCES `CachedPVM` (`object_id`, `port_name`) ON DELETE CASCADE,
534 CONSTRAINT `CachedPAV-FK-vlan_id` FOREIGN KEY (`vlan_id`) REFERENCES `VLANValidID` (`vlan_id`)
535) ENGINE=InnoDB
536";
537 $query[] = "
538CREATE TABLE `CachedPNV` (
539 `object_id` int(10) unsigned NOT NULL,
540 `port_name` char(255) NOT NULL,
541 `vlan_id` int(10) unsigned NOT NULL default '0',
542 PRIMARY KEY (`object_id`,`port_name`,`vlan_id`),
543 UNIQUE KEY `port_id` (`object_id`,`port_name`),
544 CONSTRAINT `CachedPNV-FK-compound` FOREIGN KEY (`object_id`, `port_name`, `vlan_id`) REFERENCES `CachedPAV` (`object_id`, `port_name`, `vlan_id`) ON DELETE CASCADE
545) ENGINE=InnoDB
546";
547 $query[] = "
548CREATE TABLE `CachedPVM` (
549 `object_id` int(10) unsigned NOT NULL,
550 `port_name` char(255) NOT NULL,
551 `vlan_mode` enum('access','trunk') NOT NULL default 'access',
552 PRIMARY KEY (`object_id`,`port_name`),
553 CONSTRAINT `CachedPVM-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE
554) ENGINE=InnoDB
555";
556 $query[] = "
557CREATE TABLE `PortAllowedVLAN` (
558 `object_id` int(10) unsigned NOT NULL,
559 `port_name` char(255) NOT NULL,
560 `vlan_id` int(10) unsigned NOT NULL default '0',
561 PRIMARY KEY (`object_id`,`port_name`,`vlan_id`),
562 KEY `vlan_id` (`vlan_id`),
563 CONSTRAINT `PortAllowedVLAN-FK-object-port` FOREIGN KEY (`object_id`, `port_name`) REFERENCES `PortVLANMode` (`object_id`, `port_name`) ON DELETE CASCADE,
564 CONSTRAINT `PortAllowedVLAN-FK-vlan_id` FOREIGN KEY (`vlan_id`) REFERENCES `VLANValidID` (`vlan_id`)
565) ENGINE=InnoDB
566";
567 $query[] = "
568CREATE TABLE `PortNativeVLAN` (
569 `object_id` int(10) unsigned NOT NULL,
570 `port_name` char(255) NOT NULL,
571 `vlan_id` int(10) unsigned NOT NULL default '0',
572 PRIMARY KEY (`object_id`,`port_name`,`vlan_id`),
573 UNIQUE KEY `port_id` (`object_id`,`port_name`),
574 CONSTRAINT `PortNativeVLAN-FK-compound` FOREIGN KEY (`object_id`, `port_name`, `vlan_id`) REFERENCES `PortAllowedVLAN` (`object_id`, `port_name`, `vlan_id`) ON DELETE CASCADE
575) ENGINE=InnoDB
576";
577 $query[] = "
578CREATE TABLE `PortVLANMode` (
579 `object_id` int(10) unsigned NOT NULL,
580 `port_name` char(255) NOT NULL,
581 `vlan_mode` enum('access','trunk') NOT NULL default 'access',
582 PRIMARY KEY (`object_id`,`port_name`),
583 CONSTRAINT `PortVLANMode-FK-object-port` FOREIGN KEY (`object_id`, `port_name`) REFERENCES `CachedPVM` (`object_id`, `port_name`)
584) ENGINE=InnoDB
585";
586 $query[] = "
587CREATE TABLE `VLANDescription` (
588 `domain_id` int(10) unsigned NOT NULL,
589 `vlan_id` int(10) unsigned NOT NULL default '0',
590 `vlan_type` enum('ondemand','compulsory','alien') NOT NULL default 'ondemand',
591 `vlan_descr` char(255) default NULL,
592 PRIMARY KEY (`domain_id`,`vlan_id`),
593 KEY `vlan_id` (`vlan_id`),
219da133 594 CONSTRAINT `VLANDescription-FK-domain_id` FOREIGN KEY (`domain_id`) REFERENCES `VLANDomain` (`id`) ON DELETE CASCADE,
22fdebff
DO
595 CONSTRAINT `VLANDescription-FK-vlan_id` FOREIGN KEY (`vlan_id`) REFERENCES `VLANValidID` (`vlan_id`)
596) ENGINE=InnoDB
597";
598 $query[] = "
599CREATE TABLE `VLANDomain` (
600 `id` int(10) unsigned NOT NULL auto_increment,
601 `description` char(255) default NULL,
602 PRIMARY KEY (`id`),
603 UNIQUE KEY `description` (`description`)
604) ENGINE=InnoDB
605";
606 $query[] = "
607CREATE TABLE `VLANIPv4` (
608 `domain_id` int(10) unsigned NOT NULL,
609 `vlan_id` int(10) unsigned NOT NULL,
610 `ipv4net_id` int(10) unsigned NOT NULL,
611 UNIQUE KEY `network-domain` (`ipv4net_id`,`domain_id`),
612 KEY `VLANIPv4-FK-compound` (`domain_id`,`vlan_id`),
613 CONSTRAINT `VLANIPv4-FK-compound` FOREIGN KEY (`domain_id`, `vlan_id`) REFERENCES `VLANDescription` (`domain_id`, `vlan_id`) ON DELETE CASCADE,
614 CONSTRAINT `VLANIPv4-FK-ipv4net_id` FOREIGN KEY (`ipv4net_id`) REFERENCES `IPv4Network` (`id`) ON DELETE CASCADE
615) ENGINE=InnoDB
616";
617 $query[] = "
618CREATE TABLE `VLANSTRule` (
619 `vst_id` int(10) unsigned NOT NULL,
620 `rule_no` int(10) unsigned NOT NULL,
621 `port_pcre` char(255) NOT NULL,
622 `port_role` enum('access','trunk','uplink','downlink','none') NOT NULL default 'none',
623 `wrt_vlans` char(255) default NULL,
624 `description` char(255) default NULL,
625 UNIQUE KEY `vst-rule` (`vst_id`,`rule_no`),
626 CONSTRAINT `VLANSTRule-FK-vst_id` FOREIGN KEY (`vst_id`) REFERENCES `VLANSwitchTemplate` (`id`) ON DELETE CASCADE
627) ENGINE=InnoDB
628";
629 $query[] = "
630CREATE TABLE `VLANSwitch` (
631 `object_id` int(10) unsigned NOT NULL,
632 `domain_id` int(10) unsigned NOT NULL,
633 `template_id` int(10) unsigned NOT NULL,
634 `mutex_rev` int(10) unsigned NOT NULL default '0',
635 `out_of_sync` enum('yes','no') NOT NULL default 'yes',
636 `last_errno` int(10) unsigned NOT NULL default '0',
637 `last_change` timestamp NOT NULL default '0000-00-00 00:00:00',
638 `last_push_started` timestamp NOT NULL default '0000-00-00 00:00:00',
639 `last_push_finished` timestamp NOT NULL default '0000-00-00 00:00:00',
640 `last_error_ts` timestamp NOT NULL default '0000-00-00 00:00:00',
641 UNIQUE KEY `object_id` (`object_id`),
642 KEY `domain_id` (`domain_id`),
643 KEY `template_id` (`template_id`),
644 KEY `out_of_sync` (`out_of_sync`),
645 KEY `last_errno` (`last_errno`),
646 CONSTRAINT `VLANSwitch-FK-domain_id` FOREIGN KEY (`domain_id`) REFERENCES `VLANDomain` (`id`),
647 CONSTRAINT `VLANSwitch-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`),
648 CONSTRAINT `VLANSwitch-FK-template_id` FOREIGN KEY (`template_id`) REFERENCES `VLANSwitchTemplate` (`id`)
649) ENGINE=InnoDB
650";
651 $query[] = "
652CREATE TABLE `VLANSwitchTemplate` (
653 `id` int(10) unsigned NOT NULL auto_increment,
654 `max_local_vlans` int(10) unsigned default NULL,
655 `description` char(255) default NULL,
656 PRIMARY KEY (`id`),
657 UNIQUE KEY `description` (`description`)
658) ENGINE=InnoDB
659";
660 $query[] = "
661CREATE TABLE `VLANValidID` (
662 `vlan_id` int(10) unsigned NOT NULL default '1',
663 PRIMARY KEY (`vlan_id`)
664) ENGINE=InnoDB
665";
666 $query[] = "SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS";
fa11e5c7 667 for ($i = 1; $i <= 4094; $i++)
a401a680 668 $query[] = "INSERT INTO VLANValidID (vlan_id) VALUES (${i})";
9013f05b 669 $query[] = "UPDATE Config SET varvalue = '0.18.0' WHERE varname = 'DB_VERSION'";
08d1ef24 670 break;
425fd829
DO
671 case '0.18.1':
672 $query = array_merge ($query, reloadDictionary ($batchid));
b6978d62
DO
673 $query[] = "ALTER TABLE Atom ENGINE=InnoDB";
674 $query[] = "ALTER TABLE AttributeMap ENGINE=InnoDB";
675 $query[] = "ALTER TABLE Config ENGINE=InnoDB";
676 $query[] = "ALTER TABLE IPv4Address ENGINE=InnoDB";
677 $query[] = "ALTER TABLE IPv4Allocation ENGINE=InnoDB";
678 $query[] = "ALTER TABLE Molecule ENGINE=InnoDB";
679 $query[] = "ALTER TABLE MountOperation ENGINE=InnoDB";
680 $query[] = "ALTER TABLE PortCompat ENGINE=InnoDB";
681 $query[] = "ALTER TABLE Rack ENGINE=InnoDB";
682 $query[] = "ALTER TABLE RackHistory ENGINE=InnoDB";
683 $query[] = "ALTER TABLE RackObjectHistory ENGINE=InnoDB";
684 $query[] = "ALTER TABLE RackRow ENGINE=InnoDB";
685 $query[] = "ALTER TABLE RackSpace ENGINE=InnoDB";
686 $query[] = "ALTER TABLE Script ENGINE=InnoDB";
735f169f
DO
687 $query[] = "ALTER TABLE AttributeValue DROP FOREIGN KEY `AttributeValue-FK-object_id`";
688 $query[] = "ALTER TABLE AttributeValue ADD CONSTRAINT `AttributeValue-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE";
55267f67
DO
689 $query[] = "ALTER TABLE RackObjectHistory ADD KEY (id)";
690 $query[] = "ALTER TABLE RackObjectHistory ADD CONSTRAINT `RackObjectHistory-FK-object_id` FOREIGN KEY (id) REFERENCES `RackObject` (`id`) ON DELETE CASCADE";
f3552988 691 $query[] = "ALTER TABLE MountOperation ADD CONSTRAINT `MountOperation-FK-object_id` FOREIGN KEY (object_id) REFERENCES RackObject (id) ON DELETE CASCADE";
f7cec175 692 $query[] = "ALTER TABLE RackSpace ADD CONSTRAINT `RackSpace-FK-object_id` FOREIGN KEY (object_id) REFERENCES RackObject (id) ON DELETE CASCADE";
1bcfe894
DO
693 $query[] = "ALTER TABLE Link DROP FOREIGN KEY `Link-FK-a`";
694 $query[] = "ALTER TABLE Link ADD CONSTRAINT `Link-FK-a` FOREIGN KEY (`porta`) REFERENCES `Port` (`id`) ON DELETE CASCADE";
695 $query[] = "ALTER TABLE Link DROP FOREIGN KEY `Link-FK-b`";
696 $query[] = "ALTER TABLE Link ADD CONSTRAINT `Link-FK-b` FOREIGN KEY (`portb`) REFERENCES `Port` (`id`) ON DELETE CASCADE";
697 $query[] = "ALTER TABLE Port DROP FOREIGN KEY `Port-FK-object_id`";
698 $query[] = "ALTER TABLE Port ADD CONSTRAINT `Port-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE";
e7787961
DO
699 $query[] = "ALTER TABLE AttributeMap MODIFY `chapter_id` int(10) unsigned default NULL";
700 $query[] = "ALTER TABLE IPv4Address MODIFY `ip` int(10) unsigned NOT NULL default '0'";
701 $query[] = "ALTER TABLE IPv4Address MODIFY `name` char(255) NOT NULL default ''";
702 $query[] = "ALTER TABLE IPv4Allocation MODIFY `object_id` int(10) unsigned NOT NULL default '0'";
703 $query[] = "ALTER TABLE IPv4Allocation MODIFY `ip` int(10) unsigned NOT NULL default '0'";
704 $query[] = "ALTER TABLE IPv4Allocation MODIFY `name` char(255) NOT NULL default ''";
705 $query[] = "ALTER TABLE IPv4NAT MODIFY `object_id` int(10) unsigned NOT NULL default '0'";
706 $query[] = "ALTER TABLE IPv4NAT MODIFY `proto` enum('TCP','UDP') NOT NULL default 'TCP'";
707 $query[] = "ALTER TABLE IPv4NAT MODIFY `localip` int(10) unsigned NOT NULL default '0'";
708 $query[] = "ALTER TABLE IPv4NAT MODIFY `localport` smallint(5) unsigned NOT NULL default '0'";
709 $query[] = "ALTER TABLE IPv4NAT MODIFY `remoteip` int(10) unsigned NOT NULL default '0'";
710 $query[] = "ALTER TABLE IPv4NAT MODIFY `remoteport` smallint(5) unsigned NOT NULL default '0'";
711 $query[] = "ALTER TABLE IPv4Network MODIFY `ip` int(10) unsigned NOT NULL default '0'";
712 $query[] = "ALTER TABLE IPv4Network MODIFY `mask` int(10) unsigned NOT NULL default '0'";
713 $query[] = "ALTER TABLE Link MODIFY `porta` int(10) unsigned NOT NULL default '0'";
714 $query[] = "ALTER TABLE Link MODIFY `portb` int(10) unsigned NOT NULL default '0'";
715 $query[] = "ALTER TABLE MountOperation MODIFY `object_id` int(10) unsigned NOT NULL default '0'";
716 $query[] = "ALTER TABLE MountOperation MODIFY `ctime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP";
717 $query[] = "ALTER TABLE Port MODIFY `object_id` int(10) unsigned NOT NULL default '0'";
718 $query[] = "ALTER TABLE Port MODIFY `name` char(255) NOT NULL default ''";
719 $query[] = "ALTER TABLE Port MODIFY `type` int(10) unsigned NOT NULL default '0'";
720 $query[] = "ALTER TABLE PortCompat MODIFY `type1` int(10) unsigned NOT NULL default '0'";
721 $query[] = "ALTER TABLE PortCompat MODIFY `type2` int(10) unsigned NOT NULL default '0'";
722 $query[] = "ALTER TABLE RackHistory MODIFY `ctime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP";
723 $query[] = "ALTER TABLE RackObjectHistory MODIFY `ctime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP";
724 $query[] = "ALTER TABLE TagStorage MODIFY `tag_id` int(10) unsigned NOT NULL default '0'";
725 $query[] = "ALTER TABLE UserAccount MODIFY `user_name` char(64) NOT NULL default ''";
425fd829
DO
726 $query[] = "UPDATE Config SET varvalue = '0.18.1' WHERE varname = 'DB_VERSION'";
727 break;
298d2375
DO
728 case '0.18.2':
729 $query = array_merge ($query, reloadDictionary ($batchid));
730 $query[] = "ALTER TABLE Rack ADD CONSTRAINT `Rack-FK-row_id` FOREIGN KEY (row_id) REFERENCES RackRow (id)";
b504972c 731 $query[] = "ALTER TABLE RackRow ADD UNIQUE KEY `name` (name)";
b49a479e
DO
732 $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')";
733 $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 734 $query[] = "UPDATE Config SET varvalue = '0.18.2' WHERE varname = 'DB_VERSION'";
298d2375 735 break;
2582446d
DO
736 case '0.18.3':
737 $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'";
738 $query[] = "UPDATE Config SET varvalue = '0.18.3' WHERE varname = 'DB_VERSION'";
739 break;
fbbb74fb 740 default:
5d168fde 741 showFailure ("executeUpgradeBatch () failed, because batch '${batchid}' isn't defined", __FILE__);
fbbb74fb
DO
742 die;
743 break;
744 }
fbbb74fb 745 $failures = array();
4114697d 746 echo "<tr><th>Executing batch '${batchid}'</th><td>";
fbbb74fb
DO
747 foreach ($query as $q)
748 {
749 $result = $dbxlink->query ($q);
4114697d 750 if ($result == NULL)
758fe24c 751 {
758fe24c
DO
752 $errorInfo = $dbxlink->errorInfo();
753 $failures[] = array ($q, $errorInfo[2]);
754 }
fbbb74fb 755 }
fbbb74fb 756 if (!count ($failures))
4114697d 757 echo "<strong><font color=green>done</font></strong>";
fbbb74fb
DO
758 else
759 {
4114697d 760 echo "<strong><font color=red>The following queries failed:</font></strong><br><pre>";
fbbb74fb
DO
761 foreach ($failures as $f)
762 {
763 list ($q, $i) = $f;
4114697d 764 echo "${q} -- ${i}\n";
fbbb74fb 765 }
4114697d 766 echo "</pre>";
fbbb74fb 767 }
4114697d 768 echo '</td></tr>';
fbbb74fb
DO
769}
770
771// ******************************************************************
772//
773// Execution starts here
774//
775// ******************************************************************
776
5d168fde
DO
777// a clone of showError() to drop dependency on interface.php
778function showFailure ($info = '', $location = 'N/A')
779{
5d168fde
DO
780 if (preg_match ('/\.php$/', $location))
781 $location = basename ($location);
782 elseif ($location != 'N/A')
783 $location = $location . '()';
784 echo "<div class=msg_error>An error has occured in [${location}]. ";
785 if (empty ($info))
786 echo 'No additional information is available.';
787 else
788 echo "Additional information:<br><p>\n<pre>\n${info}\n</pre></p>";
789 echo "This failure is most probably fatal.<br></div>\n";
790}
791
792require_once 'inc/config.php'; // for CODE_VERSION
49293c68 793require_once 'inc/database.php'; // for getDatabaseVersion()
3fb336f6 794require_once 'inc/dictionary.php';
5d168fde
DO
795// Enforce default value for now, releases prior to 0.17.0 didn't support 'httpd' auth source.
796$user_auth_src = 'database';
fbbb74fb 797
80138748
DO
798if (file_exists ('inc/secret.php'))
799 require_once 'inc/secret.php';
fbbb74fb 800else
dbbb4f96 801 die ('<center>There is no working RackTables instance here, <a href="install.php">install</a>?</center>');
fbbb74fb
DO
802
803try
804{
805 $dbxlink = new PDO ($pdo_dsn, $db_username, $db_password);
806}
807catch (PDOException $e)
808{
809 die ("Database connection failed:\n\n" . $e->getMessage());
810}
811
fbbb74fb
DO
812// Now we need to be sure that the current user is the administrator.
813// The rest doesn't matter within this context.
fbbb74fb 814
120e9ddd 815function authenticate_admin ($username, $password)
a1f3710a 816{
43c7895d 817 global $dbxlink;
4dd08c61
DO
818 $prepared = $dbxlink->prepare ('SELECT COUNT(*) FROM UserAccount WHERE user_id=1 AND user_name=? AND user_password_hash=?');
819 if (!$prepared->execute (array ($username, sha1 ($password))))
a1f3710a 820 die ('SQL query failed in ' . __FUNCTION__);
4dd08c61 821 $rows = $prepared->fetchAll (PDO::FETCH_NUM);
43c7895d 822 return $rows[0][0] == 1;
a1f3710a
DO
823}
824
204284ba 825switch ($user_auth_src)
99ee5479 826{
120e9ddd
DO
827 case 'database':
828 case 'ldap': // authenticate against DB as well
829 if
830 (
831 !isset ($_SERVER['PHP_AUTH_USER']) or
832 !strlen ($_SERVER['PHP_AUTH_USER']) or
833 !isset ($_SERVER['PHP_AUTH_PW']) or
834 !strlen ($_SERVER['PHP_AUTH_PW']) or
3737ede3 835 !authenticate_admin ($_SERVER['PHP_AUTH_USER'], $_SERVER['PHP_AUTH_PW'])
120e9ddd
DO
836 )
837 {
838 header ('WWW-Authenticate: Basic realm="RackTables upgrade"');
839 header ('HTTP/1.0 401 Unauthorized');
33bbd712 840 showFailure ('You must be authenticated as an administrator to complete the upgrade.', __FILE__);
120e9ddd
DO
841 die;
842 }
843 break; // cleared
844 case 'httpd':
845 if
846 (
847 !isset ($_SERVER['REMOTE_USER']) or
848 !strlen ($_SERVER['REMOTE_USER'])
849 )
850 {
33bbd712 851 showFailure ('System misconfiguration. The web-server didn\'t authenticate the user, although ought to do.');
120e9ddd
DO
852 die;
853 }
854 break; // cleared
855 default:
33bbd712 856 showFailure ('authentication source misconfiguration', __FILE__);
120e9ddd 857 die;
99ee5479 858}
fbbb74fb 859
5f016d39
DO
860?>
861<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
862<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
2cf36003 863<head><title>RackTables upgrade script</title>
5f016d39
DO
864<link rel=stylesheet type='text/css' href='css/pi.css' />
865</head>
866<body>
867<h1>Platform check status</h1>
868<?php
869
870if (!platform_is_ok())
871 die ('</body></html>');
872
873echo '<h1>Upgrade status</h1>';
fbbb74fb 874$dbver = getDatabaseVersion();
5f016d39 875echo '<table border=1 cellpadding=5>';
4114697d
DO
876echo "<tr><th>Current status</th><td>Data version: ${dbver}<br>Code version: " . CODE_VERSION . "</td></tr>\n";
877
878$path = getDBUpgradePath ($dbver, CODE_VERSION);
879if ($path === NULL)
758fe24c 880{
4114697d 881 echo "<tr><th>Upgrade path</th><td><font color=red>not found</font></td></tr>\n";
d74ae24c
DO
882 echo "<tr><th>Summary</th><td>Check README for more information. RackTables releases prior to 0.16.4 ";
883 echo "must be upgraded to 0.16.4 first.</td></tr>\n";
758fe24c 884}
4114697d 885else
5f4027b8 886{
4114697d
DO
887 if (!count ($path))
888 echo "<tr><th>Summary</th><td>Come back later.</td></tr>\n";
889 else
890 {
891 echo "<tr><th>Upgrade path</th><td>${dbver} &rarr; " . implode (' &rarr; ', $path) . "</td></tr>\n";
892 foreach ($path as $batchid)
893 {
894 executeUpgradeBatch ($batchid);
895 if (isset ($relnotes[$batchid]))
5ae6d365 896 echo "<tr><th>Release notes for ${batchid}</th><td><pre>" . $relnotes[$batchid] . "</pre></td></tr>\n";
4114697d 897 }
790a60e8 898 echo "<tr><th>Summary</th><td>Upgrade complete, it is Ok to <a href='index.php'>enter</a> the system.</td></tr>\n";
4114697d 899 }
5f4027b8 900}
4114697d 901echo '</table>';
5f016d39 902echo '</body></html>';
fbbb74fb
DO
903
904?>