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