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