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