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