new Cisco ASR dict items
[racktables] / wwwroot / inc / upgrade.php
CommitLineData
6dc745d2 1<?php
fbbb74fb 2
cddbb9fd
DO
3# This file is a part of RackTables, a datacenter and server room management
4# framework. See accompanying file "COPYING" for the full copyright and
5# licensing information.
6
90b96ff6
DO
7$relnotes = array
8(
5ae6d365
DO
9 '0.17.0' => <<<ENDOFTEXT
10LDAP options have been moved to LDAP_options array. This means, that if you were
11using LDAP authentication for users in version 0.16.x, it will break right after
12upgrade to 0.17.0. To get things working again, adjust existing secret.php file
13according to secret-sample.php file provided with 0.17.0 release.
14
15This release is the first to take advantage of the foreign key support
16provided by the InnoDB storage engine in MySQL. The installer and
17upgrader scripts check for InnoDB support and cannot complete without it.
18If you have trouble, the first step is to make sure the 'skip-innodb'
19option in my.cnf is commented out.
20
21Another change is the addition of support for file uploads. Files are stored
22in the database. There are several settings in php.ini which you may need to modify:
23 file_uploads - needs to be On
24 upload_max_filesize - max size for uploaded files
25 post_max_size - max size of all form data submitted via POST (including files)
26
27User accounts used to have 'enabled' flag, which allowed individual blocking and
28unblocking of each. This flag was dropped in favor of existing mean of access
29setup (RackCode). An unconditional denying rule is automatically added into RackCode
30for such blocked account, so the effective security policy remains the same.
31ENDOFTEXT
32,
1679ddbd
DO
33 '0.18.2' => <<<ENDOFTEXT
34RackTables from its version 0.18.0 and later is not compatible with
35RHEL/CentOS (at least with versions up to 5.5) Linux distributions
36in their default installation. There are yet options to work around that:
371. Install RackTables on a server with a different distribution/OS.
382. Request Linux distribution vendor to fix the bug with PCRE.
393. Repair your RHEL/CentOS installation yourself by fixing its PCRE
40RPM as explained here: http://bugs.centos.org/view.php?id=3252
41ENDOFTEXT
9fb6900d
DO
42,
43 '0.19.0' => <<<ENDOFTEXT
841a5b54
DO
44The files, which are intended for the httpd (web-server) directory, are
45now in the "wwwroot" directory of the tar.gz archive. Files outside of
46that directory are not directly intended for httpd environment and should
47not be copied to the server.
9fb6900d 48
8489d2af
DO
49This release incorporates ObjectLog functionality, which used to be
50available as a separate plugin. For the best results it is advised to
51disable (through local.php) external ObjectLog plugin permanently before
52the new version is installed. All previously accumulated ObjectLog records
53will be available through the updated standard interface.
54
841a5b54
DO
55RackTables is now using PHP JSON extension which is included in the PHP
56core since 5.2.0.
57
58The barcode attribute was removed. The upgrade script attempts to
59preserve the data by moving it to either the 'OEM S/N 1' attribute or to
60a Log entry. You should backup your database beforehand anyway.
9fb6900d 61ENDOFTEXT
36efe434
DO
62,
63 '0.19.2' => <<<ENDOFTEXT
64This release is different in filesystem layout. The "gateways" directory
4f5aea8a
AA
65has been moved from "wwwroot" directory. This improves security a bit.
66You can also separate your local settings and add-ons from the core RackTables code.
67To do that, put a single index.php file into the DocumentRoot of your http server:
36efe434 68
4f5aea8a
AA
69<?php
70\$racktables_confdir='/directory/where/your/secret.php/and/local.php/files/are/stored';
71require '/directory_where_you_extracted_racktables_distro/wwwroot/index.php';
72?>
73
74No more files are needed to be available directly over the HTTP.
75Full list of filesystem paths which could be specified in custom index.php or secret.php:
76 \$racktables_gwdir: path to the gateways directory;
77 \$racktables_staticdir: path to the directory containing 'pix', 'js', 'css' dirs;
78 \$racktables_confdir: path where secret.php and local.php are located. It is not
79 recommended to define it in secret.php, cause only the path to
80 local.php will be affected;
81 \$path_to_secret_php: Ignore \$racktables_confdir when locating secret.php and use
82 the specified path;
83 \$path_to_local_php: idem for local.php.
36efe434 84ENDOFTEXT
5ae6d365 85,
86ff26ae
DO
86
87 '0.19.13' => <<<ENDOFTEXT
88A new "date" attribute type has been added. Existing date based fields ("HW warranty expiration",
89"support contract expiration" and "SW warranty expiration") will be converted to this new type but
90must be in the format "mm/dd/yyyy" otherwise the conversion will fail.
91ENDOFTEXT
dd14756b
AA
92,
93
94 '0.20.0' => <<<ENDOFTEXT
d1a9e5cf
AA
95WARNING: This release have too many internal changes, some of them were waiting more than a year
96to be released. So this release is considered "BETA" and is recommended only to curiuos users,
97who agree to sacrifice the stability to the progress.
98
99Racks and Rows are now stored in the database as Objects. The RackObject table
100was renamed to Object. SQL views were created to ease the migration of custom
dd14756b
AA
101reports and scripts.
102
103New plugins engine instead of local.php file. To make your own code stored in local.php work,
104you must move the local.php file into the plugins/ directory. The name of this file does not
105matter any more. You also can store multiple files in that dir, separate your plugins by features,
106share them and try the plugins from other people just placing them into plugins/ dir, no more merging.
d1a9e5cf 107\$path_to_local_php variable has no special meaning any more.
dd14756b
AA
108\$racktables_confdir variable is now used only to search for secret.php file.
109\$racktables_plugins_dir is a new overridable special variable pointing to plugins/ directory.
110
d1a9e5cf
AA
111Beginning with this version it is possible to delete IP prefixes, VLANs, Virtual services
112and RS pools from within theirs properties tab. So please inspect your permissions rules
113to assure there are no undesired allows for deletion of these objects. To ensure this, you could
114try this code in the beginning of permissions script:
115
116 allow {userid_1} and {\$op_del}
117 deny {\$op_del} and ({\$tab_edit} or {\$tab_properties})
118
119Hardware gateways engine was rewritten in this version of RackTables. This means that
120the file gateways/deviceconfig/switch.secrets.php is not used any more. To get information
121about configuring connection properties and credentials in a new way please visit
122http://wiki.racktables.org/index.php/Gateways
123
124This also means that recently added features based on old API (D-Link switches and Linux
125gateway support contributed by Ilya Evseev) are not working any more and waiting to be
126forward-ported to new gateways API. Sorry for that.
127
128Two new config variables appeared in this version:
129 - SEARCH_DOMAINS. Comma-separated list of DNS domains which are considered "base" for your
130 network. If RackTables search engine finds multiple objects based on your search input, but
131 there is only one which FQDN consists of your input and one of these search domains, you will
132 be redirected to this object and other results will be discarded. Such behavior was unconditional
133 since 0.19.3, which caused many objections from users. So welcome this config var.
134 - QUICK_LINK_PAGES. Comma-separated list of RackTables pages to display links to them on top.
135 Each user could have his own list.
136
137Also some of config variables have changed their default values in this version.
138This means that upgrade script will change their values if you have them in previous default state.
139This could be inconvenient, but it is the most effective way to encourage users to use new features.
140If this behavior is not what you want, simply revert these variables' values:
141 - SHOW_LAST_TAB no => yes
142 - IPV4_TREE_SHOW_USAGE yes =>no (networks' usage is still available by click)
143 - IPV4LB_LISTSRC {\$typeid_4} => false
144 - FILTER_DEFAULT_ANDOR or => and (this implicitly enables the feature of dynamic tree shrinking)
145 - FILTER_SUGGEST_EXTRA no => yes (yes, we have extra logical filters!)
146 - IPV4_TREE_RTR_AS_CELL yes => no (display routers as simple text, not cell)
147
148Also please note that variable IPV4_TREE_RTR_AS_CELL now has third special value
149besides 'yes' and 'no': 'none'. Use 'none' value if you are experiencing low performance
150on IP tree page. It will completely disable IP ranges scan for used/spare IPs and the
151speed of IP tree will increase radically. The price is you will not see the routers in
152IP tree at all.
153
dd14756b 154ENDOFTEXT
038739ac
AA
155,
156
157 '0.20.1' => <<<ENDOFTEXT
d76549bc
AA
158The 0.20.0 release includes bug which breaks IP networks' capacity displaying on 32-bit architecture machines. To fix this, this release makes use of PHP's BC Math module. It is a new reqiurement. Most PHP distributions have this module already enabled, but if yours does not - you need yo recompile PHP.
159
038739ac
AA
160Security context of 'ipaddress' page now includes tags from the network containing an IP address. This means that you should audit your permission rules to check there is no unintended allows of changing IPs based on network's tagset. Example:
161 allow {client network} and {New York}
162This rule now not only allows any operation on NY client networks, but also any operation with IP addresses included in those networks. To fix this, you should change the rule this way:
163 allow {client network} and {New York} and not {\$page_ipaddress}
86ff26ae 164
038739ac 165ENDOFTEXT
91c42724
DO
166,
167
168 '0.20.4' => <<<ENDOFTEXT
169Please note that some dictionary items of Cisco Catalyst 2960 series switches
170were renamed to meet official Cisco classification:
171 2960-48TT => 2960-48TT-L
172 2960-24TC => 2960-24TC-L
173 2960-24TT => 2960-24TT-L
174 2960-8TC => 2960-8TC-L
175 2960G-48TC => 2960G-48TC-L
176 2960G-24TC => 2960G-24TC-L
177 2960G-8TC => 2960G-8TC-L
178 C2960-24 => C2960-24-S
179 C2960G-24PC => C2960-24PC-L
2a943f85
DO
180
181The DATETIME_FORMAT configuration option used in setting date and time output
182format now uses a different [1] syntax. During upgrade the option is reset to
183the default value, which is now %Y-%m-%d (YYYY-MM-DD) per ISO 8601.
184
a7192a34
AA
185This release intoduces two new configuration options:
186REVERSED_RACKS_LISTSRC and NEAREST_RACKS_CHECKBOX.
187
2a943f85 188[1] http://php.net/manual/en/function.strftime.php
91c42724 189ENDOFTEXT
038739ac 190,
eacc0983
AA
191
192 '0.20.5' => <<<ENDOFTEXT
193This release introduces the VS groups feature. VS groups is a new way to store
194and display virtual services configuration. New realm 'ipvs' (VS group) is created.
195All the existing VS configuration is kept and displayed as-is, but user is free to convert
196it to the new format, which displays it in more natural way and allows to generate
ceea33a2 197virtual_server_group keepalived configs. To convert a virtual service to the new format,
eacc0983
AA
198you need to manually create the vs group object and assign IP addresses to it. Then, if you
199have the old-style VSes configured, the Migrate tab will be displayed on the particular VS group's
200page. After successfull migration, you can remove the old-style VS objects.
201
202Old-style VS configuration becomes DEPRECATED. Its support will be removed in one of the following
203major releases. So it is strongly recommended to convert it to the new format.
204ENDOFTEXT
205,
206
6a40d076
AD
207 '0.20.6' => <<<ENDOFTEXT
2080.20.6 uses database triggers for consistency measures. The database
209user account must have the 'TRIGGER' privilege, which was introduced in
210MySQL 5.1.7.
211
212Cable paths can be traced and displayed in a graphical format. This requires
213the Image_GraphViz PEAR module (http://pear.php.net/package/Image_GraphViz).
214ENDOFTEXT
215,
90b96ff6
DO
216);
217
fbbb74fb
DO
218// At the moment we assume, that for any two releases we can
219// sequentally execute all batches, that separate them, and
220// nothing will break. If this changes one day, the function
221// below will have to generate smarter upgrade paths, while
222// the upper layer will remain the same.
223// Returning an empty array means that no upgrade is necessary.
4114697d 224// Returning NULL indicates an error.
fbbb74fb
DO
225function getDBUpgradePath ($v1, $v2)
226{
a6f83a72
DO
227 $versionhistory = array
228 (
b3f866fc 229 '0.16.4',
64347dcf 230 '0.16.5',
90b96ff6 231 '0.16.6',
30d0a2a3 232 '0.17.0',
4563cecb 233 '0.17.1',
7b1a3a72 234 '0.17.2',
9e51318b 235 '0.17.3',
958ac06d 236 '0.17.4',
9f572fb5 237 '0.17.5',
63811a09 238 '0.17.6',
026a79ee 239 '0.17.7',
3540d15c 240 '0.17.8',
1e81ad97 241 '0.17.9',
f32167d2 242 '0.17.10',
4a4a5440 243 '0.17.11',
9013f05b 244 '0.18.0',
425fd829 245 '0.18.1',
298d2375 246 '0.18.2',
f6d1a7cc 247 '0.18.3',
1c5b7c84 248 '0.18.4',
92ee2b01 249 '0.18.5',
2f5e4db9 250 '0.18.6',
9fb6900d 251 '0.18.7',
16825cc8 252 '0.19.0',
1d5dd3a1 253 '0.19.1',
0abae5fb 254 '0.19.2',
03d86c03 255 '0.19.3',
1f02e311 256 '0.19.4',
2a3a1a6d
AA
257 '0.19.5',
258 '0.19.6',
86eaaa67 259 '0.19.7',
f7494e3c 260 '0.19.8',
5c0bd7de 261 '0.19.9',
f7494e3c 262 '0.19.10',
ea24fb69 263 '0.19.11',
28537080 264 '0.19.12',
86ff26ae 265 '0.19.13',
b55f913c 266 '0.19.14',
f7494e3c 267 '0.20.0',
e9893a88 268 '0.20.1',
6e58c2c4 269 '0.20.2',
56a28368 270 '0.20.3',
4dcd770e 271 '0.20.4',
f9fcce59 272 '0.20.5',
6a40d076 273 '0.20.6',
a6f83a72 274 );
120e9ddd
DO
275 if (!in_array ($v1, $versionhistory) or !in_array ($v2, $versionhistory))
276 return NULL;
fbbb74fb 277 $skip = TRUE;
4114697d 278 $path = NULL;
154a42e5
DO
279 // foreach() below cannot handle this specific case
280 if ($v1 == $v2)
281 return array();
fbbb74fb
DO
282 // Now collect all versions > $v1 and <= $v2
283 foreach ($versionhistory as $v)
284 {
4114697d 285 if ($skip and $v == $v1)
fbbb74fb
DO
286 {
287 $skip = FALSE;
4114697d 288 $path = array();
fbbb74fb
DO
289 continue;
290 }
291 if ($skip)
292 continue;
293 $path[] = $v;
294 if ($v == $v2)
295 break;
296 }
297 return $path;
298}
299
90b96ff6
DO
300// Upgrade batches are named exactly as the release where they first appear.
301// That is simple, but seems sufficient for beginning.
87ae30c5 302function getUpgradeBatch ($batchid)
fbbb74fb
DO
303{
304 $query = array();
ca3d68bd 305 global $dbxlink;
fbbb74fb
DO
306 switch ($batchid)
307 {
64347dcf
DO
308 case '0.16.5':
309 $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')";
310 $query[] = "update Config set varvalue = '0.16.5' where varname = 'DB_VERSION'";
311 break;
90b96ff6
DO
312 case '0.16.6':
313 $query[] = "update Config set varvalue = '0.16.6' where varname = 'DB_VERSION'";
314 break;
30d0a2a3 315 case '0.17.0':
e1ae3fb4
AD
316 // create tables for storing files (requires InnoDB support)
317 if (!isInnoDBSupported ())
318 {
71066ef1 319 showUpgradeError ("Cannot upgrade because InnoDB tables are not supported by your MySQL server. See the README for details.", __FUNCTION__);
f3c50166 320 die;
e1ae3fb4 321 }
f76c4197
DY
322
323 $query[] = "alter table Chapter change chapter_no id int(10) unsigned NOT NULL auto_increment";
324 $query[] = "alter table Chapter change chapter_name name char(128) NOT NULL";
325 $query[] = "alter table Chapter drop key chapter_name";
326 $query[] = "alter table Chapter add UNIQUE KEY name (name)";
327 $query[] = "alter table Attribute change attr_id id int(10) unsigned NOT NULL auto_increment";
328 $query[] = "alter table Attribute change attr_type type enum('string','uint','float','dict') default NULL";
329 $query[] = "alter table Attribute change attr_name name char(64) default NULL";
330 $query[] = "alter table Attribute drop key attr_name";
331 $query[] = "alter table Attribute add UNIQUE KEY name (name)";
332 $query[] = "alter table AttributeMap change chapter_no chapter_id int(10) unsigned NOT NULL";
333 $query[] = "alter table Dictionary change chapter_no chapter_id int(10) unsigned NOT NULL";
f3c50166 334 // schema changes for file management
e1ae3fb4
AD
335 $query[] = "
336CREATE TABLE `File` (
337 `id` int(10) unsigned NOT NULL auto_increment,
338 `name` char(255) NOT NULL,
339 `type` char(255) NOT NULL,
340 `size` int(10) unsigned NOT NULL,
341 `ctime` datetime NOT NULL,
342 `mtime` datetime NOT NULL,
343 `atime` datetime NOT NULL,
344 `contents` longblob NOT NULL,
345 `comment` text,
13edfa1c
AD
346 PRIMARY KEY (`id`),
347 UNIQUE KEY `name` (`name`)
e1ae3fb4
AD
348) ENGINE=InnoDB";
349 $query[] = "
350CREATE TABLE `FileLink` (
351 `id` int(10) unsigned NOT NULL auto_increment,
352 `file_id` int(10) unsigned NOT NULL,
353 `entity_type` enum('ipv4net','ipv4rspool','ipv4vs','object','rack','user') NOT NULL default 'object',
354 `entity_id` int(10) NOT NULL,
355 PRIMARY KEY (`id`),
af721881 356 UNIQUE KEY `FileLink-unique` (`file_id`,`entity_type`,`entity_id`),
e1ae3fb4
AD
357 KEY `FileLink-file_id` (`file_id`),
358 CONSTRAINT `FileLink-File_fkey` FOREIGN KEY (`file_id`) REFERENCES `File` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
359) ENGINE=InnoDB";
360 $query[] = "ALTER TABLE TagStorage MODIFY COLUMN target_realm enum('file','ipv4net','ipv4rspool','ipv4vs','object','rack','user') NOT NULL default 'object'";
f3c50166 361
f76c4197 362 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (24,'no','network security models')";
9730d09f 363 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (25,'no','wireless models')";
f76c4197
DY
364 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,1,0)";
365 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,2,24)";
366 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,3,0)";
367 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,5,0)";
368 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,14,0)";
369 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,16,0)";
370 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,17,0)";
371 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,18,0)";
372 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,20,0)";
373 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,21,0)";
374 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,22,0)";
375 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,24,0)";
9730d09f
DO
376 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (965,1,0)";
377 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (965,3,0)";
378 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (965,2,25)";
706ce117
DO
379 $query[] = 'alter table IPBonds rename to IPv4Allocation';
380 $query[] = 'alter table PortForwarding rename to IPv4NAT';
381 $query[] = 'alter table IPRanges rename to IPv4Network';
382 $query[] = 'alter table IPAddress rename to IPv4Address';
383 $query[] = 'alter table IPLoadBalancer rename to IPv4LB';
4114697d 384 $query[] = 'alter table IPRSPool rename to IPv4RSPool';
706ce117 385 $query[] = 'alter table IPRealServer rename to IPv4RS';
4114697d 386 $query[] = 'alter table IPVirtualService rename to IPv4VS';
120e9ddd
DO
387 $query[] = "alter table TagStorage change column target_realm entity_realm enum('file','ipv4net','ipv4vs','ipv4rspool','object','rack','user') NOT NULL default 'object'";
388 $query[] = 'alter table TagStorage change column target_id entity_id int(10) unsigned NOT NULL';
389 $query[] = 'alter table TagStorage drop key entity_tag';
390 $query[] = 'alter table TagStorage drop key target_id';
391 $query[] = 'alter table TagStorage add UNIQUE KEY `entity_tag` (`entity_realm`,`entity_id`,`tag_id`)';
392 $query[] = 'alter table TagStorage add KEY `entity_id` (`entity_id`)';
37e59768
DO
393 $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')";
394 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('PREVIEW_TEXT_ROWS','25','uint','yes','no','Rows for text file preview')";
395 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('PREVIEW_TEXT_COLS','80','uint','yes','no','Columns for text file preview')";
396 $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 397 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('VENDOR_SIEVE','','string','yes','no','Vendor sieve configuration')";
073ed463
DO
398 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('IPV4LB_LISTSRC','{\$typeid_4}','string','yes','no','List source: IPv4 load balancers')";
399 $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')";
400 $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')";
401 $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')";
402 $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 403 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('RACKS_PER_ROW','12','unit','yes','no','Racks per row')";
590e1281 404 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('FILTER_PREDICATE_SIEVE','','string','yes','no','Predicate sieve regex(7)')";
5496c89f
DO
405 $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)')";
406 $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')";
407 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('FILTER_SUGGEST_TAGS','yes','string','no','no','Suggest tags in list filter')";
408 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('FILTER_SUGGEST_PREDICATES','yes','string','no','no','Suggest predicates in list filter')";
409 $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
410 $query[] = "delete from Config where varname = 'USER_AUTH_SRC'";
411 $query[] = "delete from Config where varname = 'COOKIE_TTL'";
412 $query[] = "delete from Config where varname = 'rtwidth_0'";
413 $query[] = "delete from Config where varname = 'rtwidth_1'";
414 $query[] = "delete from Config where varname = 'rtwidth_2'";
c6bc0ac5
DO
415 $query[] = "delete from Config where varname = 'NAMEFUL_OBJTYPES'";
416 $query[] = "delete from Config where varname = 'REQUIRE_ASSET_TAG_FOR'";
417 $query[] = "delete from Config where varname = 'IPV4_PERFORMERS'";
418 $query[] = "delete from Config where varname = 'NATV4_PERFORMERS'";
dbb33805 419 $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
420 $result = $dbxlink->query ("select user_id, user_name, user_realname from UserAccount where user_enabled = 'no'");
421 while ($row = $result->fetch (PDO::FETCH_ASSOC))
422 $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'";
423 $query[] = "update Script set script_text = NULL where script_name = 'RackCodeCache'";
424 unset ($result);
79b8ad1e 425 $query[] = "alter table UserAccount drop column user_enabled";
f76c4197 426
10bac82a
DY
427 $query[] = "CREATE TABLE RackRow ( id int(10) unsigned NOT NULL auto_increment, name char(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM";
428
9f14a7ef
DY
429 $result = $dbxlink->query ("select dict_key, dict_value from Dictionary where chapter_no = 3");
430 while($row = $result->fetch(PDO::FETCH_NUM))
10bac82a 431 $query[] = "insert into RackRow set id=${row[0]}, name='${row[1]}'";
ee286837 432 unset ($result);
f76c4197 433 $query[] = "delete from Dictionary where chapter_id = 3";
c4d0dc30 434 $query[] = "delete from Chapter where id = 3";
9133d2c5
DO
435 $query[] = "
436CREATE TABLE `LDAPCache` (
437 `presented_username` char(64) NOT NULL,
438 `successful_hash` char(40) NOT NULL,
439 `first_success` timestamp NOT NULL default CURRENT_TIMESTAMP,
440 `last_retry` timestamp NOT NULL default '0000-00-00 00:00:00',
441 `displayed_name` char(128) default NULL,
442 `memberof` text,
443 UNIQUE KEY `presented_username` (`presented_username`),
444 KEY `scanidx` (`presented_username`,`successful_hash`)
445) ENGINE=InnoDB;";
3827da34 446 $query[] = "alter table UserAccount modify column user_password_hash char(40) NULL";
aa9a0fb4
DO
447 $query[] = 'ALTER TABLE Rack DROP COLUMN deleted';
448 $query[] = 'ALTER TABLE RackHistory DROP COLUMN deleted';
449 $query[] = 'ALTER TABLE RackObject DROP COLUMN deleted';
450 $query[] = 'ALTER TABLE RackObjectHistory DROP COLUMN deleted';
2fb9d280
DO
451 // Can't be added straight due to many duplicates, even in "dictbase" data.
452 $result = $dbxlink->query ('SELECT type1, type2, count(*) - 1 as excess FROM PortCompat GROUP BY type1, type2 HAVING excess > 0');
453 while ($row = $result->fetch (PDO::FETCH_ASSOC))
454 $query[] = "DELETE FROM PortCompat WHERE type1 = ${row['type1']} AND type2 = ${row['type2']} limit ${row['excess']}";
455 unset ($result);
456 $query[] = 'ALTER TABLE PortCompat DROP KEY type1';
67d8a969 457 $query[] = 'ALTER TABLE PortCompat ADD UNIQUE `type1_2` (type1, type2)';
f76c4197
DY
458 $query[] = "UPDATE Config SET varvalue = '0.17.0' WHERE varname = 'DB_VERSION'";
459
b3f866fc 460 break;
4563cecb 461 case '0.17.1':
8b200a9c 462 $query[] = "ALTER TABLE Dictionary DROP KEY `chap_to_key`";
4563cecb
DO
463 // Token set has changed, so the cache isn't valid any more.
464 $query[] = "UPDATE Script SET script_text = NULL WHERE script_name = 'RackCodeCache'";
465 $query[] = "UPDATE Config SET varvalue = '0.17.1' WHERE varname = 'DB_VERSION'";
7d4ea62b 466 break;
7b1a3a72 467 case '0.17.2':
7b1a3a72 468 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (26,'no','fibre channel switch models')";
7c537f33 469 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1055,2,26)";
49b605d9 470 $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
471 // wipe irrelevant records (ticket:250)
472 $query[] = "DELETE FROM TagStorage WHERE entity_realm = 'file' AND entity_id NOT IN (SELECT id FROM File)";
f06fe423 473 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('IPV4_ENABLE_KNIGHT','yes','string','no','no','Enable IPv4 knight feature')";
99ab184f 474 $query[] = "ALTER TABLE IPv4Network ADD COLUMN comment text AFTER name";
5163cd3a 475 $query[] = "ALTER TABLE Port ADD INDEX comment (reservation_comment)";
029a14bc
DO
476 $query[] = "ALTER TABLE Port DROP KEY l2address"; // UNIQUE
477 $query[] = "ALTER TABLE Port ADD KEY (l2address)"; // not UNIQUE
948666cc
DO
478 $query[] = "ALTER TABLE Port DROP KEY object_id";
479 $query[] = "ALTER TABLE Port ADD UNIQUE KEY per_object (object_id, name, type)";
74aee2dc
DO
480 $query[] = "INSERT INTO PortCompat (type1, type2) VALUES (20,1083)";
481 $query[] = "INSERT INTO PortCompat (type1, type2) VALUES (21,1083)";
482 $query[] = "INSERT INTO PortCompat (type1, type2) VALUES (1077,1077)";
483 $query[] = "INSERT INTO PortCompat (type1, type2) VALUES (1083,20)";
484 $query[] = "INSERT INTO PortCompat (type1, type2) VALUES (1083,21)";
485 $query[] = "INSERT INTO PortCompat (type1, type2) VALUES (1083,1083)";
1c4830dc 486 $query[] = "INSERT INTO PortCompat (type1, type2) VALUES (1087,1087)";
50e02490
DO
487 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (27,'no','PDU models')";
488 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (2,2,27)";
7b1a3a72
AD
489 $query[] = "UPDATE Config SET varvalue = '0.17.2' WHERE varname = 'DB_VERSION'";
490 break;
9e51318b
DO
491 case '0.17.3':
492 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('TAGS_TOPLIST_SIZE','50','uint','yes','no','Tags top list size')";
493 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('TAGS_QUICKLIST_SIZE','20','uint','no','no','Tags quick list size')";
494 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('TAGS_QUICKLIST_THRESHOLD','50','uint','yes','no','Tags quick list threshold')";
87601bbc 495 $query[] = "ALTER TABLE AttributeMap MODIFY COLUMN chapter_id int(10) unsigned NULL";
7028a42c 496 $query[] = "UPDATE AttributeMap SET chapter_id = NULL WHERE attr_id IN (SELECT id FROM Attribute WHERE type != 'dict')";
a013838b
DO
497 // ticket:239
498 $query[] = 'UPDATE AttributeValue SET uint_value = 1018 WHERE uint_value = 731 AND attr_id IN (SELECT attr_id FROM AttributeMap WHERE chapter_id = 12)';
499 $query[] = 'DELETE FROM Dictionary WHERE dict_key = 731';
084aca6c 500 $query[] = "UPDATE Config SET vartype='uint' WHERE varname='RACKS_PER_ROW'";
f44fdef9 501 $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
502 $query[] = "UPDATE Config SET varvalue = '0.17.3' WHERE varname = 'DB_VERSION'";
503 break;
958ac06d
DO
504 case '0.17.4':
505 $query[] = "ALTER TABLE Link ENGINE=InnoDB";
506 $query[] = "ALTER TABLE Port ENGINE=InnoDB";
4d87feaf
DO
507 $query[] = "ALTER TABLE IPv4RS ENGINE=InnoDB";
508 $query[] = "ALTER TABLE IPv4RSPool ENGINE=InnoDB";
cafd4cf3
DO
509 $query[] = "ALTER TABLE AttributeValue ENGINE=InnoDB";
510 $query[] = "ALTER TABLE RackObject ENGINE=InnoDB";
511 $query[] = "ALTER TABLE IPv4NAT ENGINE=InnoDB";
512 $query[] = "ALTER TABLE IPv4LB ENGINE=InnoDB";
513 $query[] = "ALTER TABLE IPv4VS ENGINE=InnoDB";
4d87feaf 514 $query[] = "DELETE FROM IPv4RS WHERE rspool_id NOT IN (SELECT id FROM IPv4RSPool)";
958ac06d 515 $query[] = "ALTER TABLE Link ADD CONSTRAINT `Link-FK-b` FOREIGN KEY (portb) REFERENCES Port (id)";
cafd4cf3 516 $query[] = "ALTER TABLE Link ADD CONSTRAINT `Link-FK-a` FOREIGN KEY (porta) REFERENCES Port (id)";
4d87feaf 517 $query[] = "ALTER TABLE IPv4RS ADD CONSTRAINT `IPv4RS-FK` FOREIGN KEY (rspool_id) REFERENCES IPv4RSPool (id) ON DELETE CASCADE";
cafd4cf3
DO
518 $query[] = "ALTER TABLE AttributeValue ADD CONSTRAINT `AttributeValue-FK-object_id` FOREIGN KEY (object_id) REFERENCES RackObject (id)";
519 $query[] = "ALTER TABLE IPv4NAT ADD CONSTRAINT `IPv4NAT-FK-object_id` FOREIGN KEY (object_id) REFERENCES RackObject (id)";
520 $query[] = "ALTER TABLE Port ADD CONSTRAINT `Port-FK-object_id` FOREIGN KEY (object_id) REFERENCES RackObject (id)";
521 $query[] = "ALTER TABLE IPv4LB ADD CONSTRAINT `IPv4LB-FK-rspool_id` FOREIGN KEY (rspool_id) REFERENCES IPv4RSPool (id)";
522 $query[] = "ALTER TABLE IPv4LB ADD CONSTRAINT `IPv4LB-FK-object_id` FOREIGN KEY (object_id) REFERENCES RackObject (id)";
523 $query[] = "ALTER TABLE IPv4LB ADD CONSTRAINT `IPv4LB-FK-vs_id` FOREIGN KEY (vs_id) REFERENCES IPv4VS (id)";
958ac06d
DO
524 $query[] = "UPDATE Config SET varvalue = '0.17.4' WHERE varname = 'DB_VERSION'";
525 break;
9f572fb5
DO
526 case '0.17.5':
527 $query[] = "ALTER TABLE TagTree ENGINE=InnoDB";
528 $query[] = "ALTER TABLE TagStorage ENGINE=InnoDB";
529 $query[] = "ALTER TABLE TagStorage ADD CONSTRAINT `TagStorage-FK-tag_id` FOREIGN KEY (tag_id) REFERENCES TagTree (id)";
530 $query[] = "ALTER TABLE TagTree ADD CONSTRAINT `TagTree-K-parent_id` FOREIGN KEY (parent_id) REFERENCES TagTree (id)";
2400d7ec
DO
531 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (21,1195)';
532 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (22,1196)';
533 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (23,1196)';
534 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (20,1195)';
535 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (25,1202)';
536 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (26,1202)';
537 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (27,1204)';
538 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (28,1204)';
539 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1083,1195)';
540 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1084,1084)';
541 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1195,20)';
542 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1195,21)';
543 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1195,1083)';
544 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1195,1195)';
545 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1196,22)';
546 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1196,23)';
547 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1196,1196)';
548 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1197,1197)';
549 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1198,1199)';
550 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1199,1198)';
551 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1200,1200)';
552 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1201,1201)';
553 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1202,25)';
554 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1202,26)';
555 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1202,1202)';
556 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1203,1203)';
557 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1204,27)';
558 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1204,28)';
559 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1204,1204)';
560 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1205,1205)';
561 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1206,1207)';
562 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1207,1206)';
93a83f51 563 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1316,1316)';
bdc91a5c
DO
564 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (16, 1322)';
565 $query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1322, 16)';
566 $query[] = 'DELETE FROM PortCompat WHERE type1 = 16 AND type2 = 16';
2400d7ec
DO
567 for ($i = 1209; $i <= 1300; $i++)
568 $query[] = "INSERT INTO PortCompat (type1, type2) VALUES (${i}, ${i})";
d9b88ea0 569 $query[] = "
09d6afa4 570CREATE TABLE `PortInnerInterface` (
9173281a 571 `id` int(10) unsigned NOT NULL,
d9b88ea0
DO
572 `iif_name` char(16) NOT NULL,
573 PRIMARY KEY (`id`),
9173281a 574 UNIQUE KEY `iif_name` (`iif_name`)
d9b88ea0 575) ENGINE=InnoDB";
2400d7ec
DO
576 $query[] = "INSERT INTO `PortInnerInterface` VALUES (1,'hardwired')";
577 $query[] = "INSERT INTO `PortInnerInterface` VALUES (2,'SFP-100')";
578 $query[] = "INSERT INTO `PortInnerInterface` VALUES (3,'GBIC')";
579 $query[] = "INSERT INTO `PortInnerInterface` VALUES (4,'SFP-1000')";
580 $query[] = "INSERT INTO `PortInnerInterface` VALUES (5,'XENPAK')";
581 $query[] = "INSERT INTO `PortInnerInterface` VALUES (6,'X2')";
582 $query[] = "INSERT INTO `PortInnerInterface` VALUES (7,'XPAK')";
583 $query[] = "INSERT INTO `PortInnerInterface` VALUES (8,'XFP')";
584 $query[] = "INSERT INTO `PortInnerInterface` VALUES (9,'SFP+')";
d9b88ea0 585 $query[] = "
09d6afa4 586CREATE TABLE `PortInterfaceCompat` (
d9b88ea0
DO
587 `iif_id` int(10) unsigned NOT NULL,
588 `oif_id` int(10) unsigned NOT NULL,
589 UNIQUE KEY `pair` (`iif_id`,`oif_id`),
2400d7ec 590 CONSTRAINT `PortInterfaceCompat-FK-iif_id` FOREIGN KEY (`iif_id`) REFERENCES `PortInnerInterface` (`id`)
d9b88ea0 591) ENGINE=InnoDB";
c76cfa0d
DO
592 $query[] = "ALTER TABLE Port ADD COLUMN iif_id int unsigned NOT NULL AFTER name"; // will set iif_id to 0
593 $query[] = "UPDATE Port SET iif_id = 2 WHERE type = 1208";
594 $query[] = "UPDATE Port SET iif_id = 3 WHERE type = 1078";
595 $query[] = "UPDATE Port SET iif_id = 4 WHERE type = 1077";
596 $query[] = "UPDATE Port SET iif_id = 5 WHERE type = 1079";
597 $query[] = "UPDATE Port SET iif_id = 6 WHERE type = 1080";
598 $query[] = "UPDATE Port SET iif_id = 7 WHERE type = 1081";
599 $query[] = "UPDATE Port SET iif_id = 8 WHERE type = 1082";
600 $query[] = "UPDATE Port SET iif_id = 9 WHERE type = 1084";
601 $query[] = "UPDATE Port SET iif_id = 1 WHERE iif_id = 0";
08aa3467
DO
602 $query[] = 'ALTER TABLE Port ADD UNIQUE `object_iif_oif_name` (object_id, iif_id, type, name)';
603 $query[] = 'ALTER TABLE Port DROP KEY `per_object`';
2400d7ec
DO
604 $base1000 = array (24, 34, 1202, 1203, 1204, 1205, 1206, 1207);
605 $base10000 = array (30, 35, 36, 37, 38, 39, 40);
606 $PICdata = array
607 (
bdc91a5c 608 1 => array (16, 19, 24, 29, 31, 33, 446, 681, 682, 1322),
2400d7ec
DO
609 2 => array (1208, 1195, 1196, 1197, 1198, 1199, 1200, 1201),
610 3 => array_merge (array (1078), $base1000),
611 4 => array_merge (array (1077), $base1000),
612 5 => array_merge (array (1079), $base10000),
613 6 => array_merge (array (1080), $base10000),
614 7 => array_merge (array (1081), $base10000),
615 8 => array_merge (array (1082), $base10000),
616 9 => array_merge (array (1084), $base10000),
617 );
618 // make sure all IIF/OIF pairs referenced from Port exist in PortInterfaceCompat before enabling FK
619 // iif_id doesn't exist at this point
620 $result = $dbxlink->query ('SELECT DISTINCT type FROM Port WHERE type NOT IN (1208, 1078, 1077, 1079, 1080, 1081, 1082, 1084)');
621 while ($row = $result->fetch (PDO::FETCH_ASSOC))
622 if (FALSE === array_search ($row['type'], $PICdata[1]))
623 array_push ($PICdata[1], $row['type']);
624 unset ($result);
625 foreach ($PICdata as $iif_id => $oif_ids)
626 foreach ($oif_ids as $oif_id)
627 $query[] = "INSERT INTO PortInterfaceCompat (iif_id, oif_id) VALUES (${iif_id}, ${oif_id})";
c76cfa0d 628 $query[] = "ALTER TABLE Port ADD CONSTRAINT `Port-FK-iif-oif` FOREIGN KEY (`iif_id`, `type`) REFERENCES `PortInterfaceCompat` (`iif_id`, `oif_id`)";
bdc91a5c 629 $query[] = 'UPDATE Port SET type = 1322 WHERE type = 16 AND (SELECT objtype_id FROM RackObject WHERE id = object_id) IN (2, 12)';
2400d7ec
DO
630 $query[] = "DELETE FROM Config WHERE varname = 'default_port_type'";
631 $query[] = "INSERT INTO Config VALUES ('DEFAULT_PORT_IIF_ID','1','uint','no','no','Default port inner interface ID')";
632 $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 633 $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 634 $query[] = "UPDATE Chapter SET name = 'PortOuterInterface' WHERE id = 2";
fbcbb4ee
DO
635 // remap refs to duplicate records, which will be discarded (ticket:286)
636 $query[] = 'UPDATE AttributeValue SET uint_value = 147 WHERE uint_value = 1020 AND attr_id = 2';
637 $query[] = 'UPDATE AttributeValue SET uint_value = 377 WHERE uint_value = 1021 AND attr_id = 2';
e8ab58e8 638 $query[] = 'INSERT INTO AttributeMap (objtype_id, attr_id) VALUES (2, 1), (2, 3), (2, 5)';
9f572fb5
DO
639 $query[] = "UPDATE Config SET varvalue = '0.17.5' WHERE varname = 'DB_VERSION'";
640 break;
63811a09
DO
641 case '0.17.6':
642 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (28,'no','Voice/video hardware')";
643 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1323,1,NULL)";
644 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1323,2,28)";
645 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1323,3,NULL)";
646 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1323,5,NULL)";
cd3775e9 647 $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)')";
63811a09
DO
648 $query[] = "UPDATE Config SET varvalue = '0.17.6' WHERE varname = 'DB_VERSION'";
649 break;
026a79ee
DO
650 case '0.17.7':
651 $query[] = "UPDATE Config SET varvalue = '0.17.7' WHERE varname = 'DB_VERSION'";
652 break;
3540d15c 653 case '0.17.8':
1e81ad97
DO
654 $query[] = "ALTER TABLE TagTree DROP COLUMN valid_realm";
655 $query[] = "UPDATE Config SET varvalue = '0.17.8' WHERE varname = 'DB_VERSION'";
656 break;
657 case '0.17.9':
45833307 658 $query[] = "ALTER table Config add `is_userdefined` enum('yes','no') NOT NULL default 'no' AFTER `is_hidden`";
3540d15c 659 $query[] = "
dec748f6
MH
660CREATE TABLE `UserConfig` (
661 `varname` char(32) NOT NULL,
662 `varvalue` char(255) NOT NULL,
663 `user` char(64) NOT NULL,
3540d15c
DY
664 UNIQUE KEY `user_varname` (`user`,`varname`)
665) TYPE=InnoDB";
1e81ad97
DO
666 $query[] = "UPDATE Config SET is_userdefined = 'yes' WHERE varname IN
667(
668'MASSCOUNT',
669'MAXSELSIZE',
670'ROW_SCALE',
671'PORTS_PER_ROW',
672'IPV4_ADDRS_PER_PAGE',
673'DEFAULT_RACK_HEIGHT',
674'DEFAULT_SLB_VS_PORT',
675'DEFAULT_SLB_RS_PORT',
676'DETECT_URLS',
677'RACK_PRESELECT_THRESHOLD',
678'DEFAULT_IPV4_RS_INSERVICE',
679'DEFAULT_OBJECT_TYPE',
680'SHOW_EXPLICIT_TAGS',
681'SHOW_IMPLICIT_TAGS',
682'SHOW_AUTOMATIC_TAGS',
683'IPV4_AUTO_RELEASE',
684'SHOW_LAST_TAB',
685'EXT_IPV4_VIEW',
686'TREE_THRESHOLD',
687'ADDNEW_AT_TOP',
688'IPV4_TREE_SHOW_USAGE',
689'PREVIEW_TEXT_MAXCHARS',
690'PREVIEW_TEXT_ROWS',
691'PREVIEW_TEXT_COLS',
692'PREVIEW_IMAGE_MAXPXS',
693'VENDOR_SIEVE',
694'RACKS_PER_ROW'
695)";
9013f05b
DO
696 $query[] = "UPDATE Config SET varvalue = '0.17.9' WHERE varname = 'DB_VERSION'";
697 break;
f32167d2 698 case '0.17.10':
4368cc45 699 $query[] = "ALTER TABLE MountOperation ADD KEY (object_id)";
2926b1cb 700 $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 701 $query[] = "UPDATE Config SET varvalue = '0.17.10' WHERE varname = 'DB_VERSION'";
d80036ba 702 break;
a1fc539a 703 case '0.17.11':
a1fc539a 704 $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 705 $query[] = "DELETE AttributeValue FROM AttributeValue JOIN Attribute where AttributeValue.attr_id = Attribute.id AND Attribute.type = 'dict' AND AttributeValue.uint_value = 0";
a1fc539a 706 $query[] = "UPDATE Config SET varvalue = '0.17.11' WHERE varname = 'DB_VERSION'";
59e0658a 707 break;
9013f05b 708 case '0.18.0':
8bb69a06
DO
709 $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')";
710 $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')";
711 $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')";
712 $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
713 $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')";
714 $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')";
715 $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 716 $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 717 $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 718 $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 719 $query[] = "ALTER TABLE IPv4Network ENGINE=InnoDB";
22fdebff
DO
720 $query[] = "SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0";
721 $query[] = "
722CREATE TABLE `CachedPAV` (
723 `object_id` int(10) unsigned NOT NULL,
724 `port_name` char(255) NOT NULL,
725 `vlan_id` int(10) unsigned NOT NULL default '0',
726 PRIMARY KEY (`object_id`,`port_name`,`vlan_id`),
727 KEY `vlan_id` (`vlan_id`),
728 CONSTRAINT `CachedPAV-FK-object-port` FOREIGN KEY (`object_id`, `port_name`) REFERENCES `CachedPVM` (`object_id`, `port_name`) ON DELETE CASCADE,
729 CONSTRAINT `CachedPAV-FK-vlan_id` FOREIGN KEY (`vlan_id`) REFERENCES `VLANValidID` (`vlan_id`)
730) ENGINE=InnoDB
731";
732 $query[] = "
733CREATE TABLE `CachedPNV` (
734 `object_id` int(10) unsigned NOT NULL,
735 `port_name` char(255) NOT NULL,
736 `vlan_id` int(10) unsigned NOT NULL default '0',
737 PRIMARY KEY (`object_id`,`port_name`,`vlan_id`),
738 UNIQUE KEY `port_id` (`object_id`,`port_name`),
739 CONSTRAINT `CachedPNV-FK-compound` FOREIGN KEY (`object_id`, `port_name`, `vlan_id`) REFERENCES `CachedPAV` (`object_id`, `port_name`, `vlan_id`) ON DELETE CASCADE
740) ENGINE=InnoDB
741";
742 $query[] = "
743CREATE TABLE `CachedPVM` (
744 `object_id` int(10) unsigned NOT NULL,
745 `port_name` char(255) NOT NULL,
746 `vlan_mode` enum('access','trunk') NOT NULL default 'access',
747 PRIMARY KEY (`object_id`,`port_name`),
748 CONSTRAINT `CachedPVM-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE
749) ENGINE=InnoDB
750";
751 $query[] = "
752CREATE TABLE `PortAllowedVLAN` (
753 `object_id` int(10) unsigned NOT NULL,
754 `port_name` char(255) NOT NULL,
755 `vlan_id` int(10) unsigned NOT NULL default '0',
756 PRIMARY KEY (`object_id`,`port_name`,`vlan_id`),
757 KEY `vlan_id` (`vlan_id`),
758 CONSTRAINT `PortAllowedVLAN-FK-object-port` FOREIGN KEY (`object_id`, `port_name`) REFERENCES `PortVLANMode` (`object_id`, `port_name`) ON DELETE CASCADE,
759 CONSTRAINT `PortAllowedVLAN-FK-vlan_id` FOREIGN KEY (`vlan_id`) REFERENCES `VLANValidID` (`vlan_id`)
760) ENGINE=InnoDB
761";
762 $query[] = "
763CREATE TABLE `PortNativeVLAN` (
764 `object_id` int(10) unsigned NOT NULL,
765 `port_name` char(255) NOT NULL,
766 `vlan_id` int(10) unsigned NOT NULL default '0',
767 PRIMARY KEY (`object_id`,`port_name`,`vlan_id`),
768 UNIQUE KEY `port_id` (`object_id`,`port_name`),
769 CONSTRAINT `PortNativeVLAN-FK-compound` FOREIGN KEY (`object_id`, `port_name`, `vlan_id`) REFERENCES `PortAllowedVLAN` (`object_id`, `port_name`, `vlan_id`) ON DELETE CASCADE
770) ENGINE=InnoDB
771";
772 $query[] = "
773CREATE TABLE `PortVLANMode` (
774 `object_id` int(10) unsigned NOT NULL,
775 `port_name` char(255) NOT NULL,
776 `vlan_mode` enum('access','trunk') NOT NULL default 'access',
777 PRIMARY KEY (`object_id`,`port_name`),
778 CONSTRAINT `PortVLANMode-FK-object-port` FOREIGN KEY (`object_id`, `port_name`) REFERENCES `CachedPVM` (`object_id`, `port_name`)
779) ENGINE=InnoDB
780";
781 $query[] = "
782CREATE TABLE `VLANDescription` (
783 `domain_id` int(10) unsigned NOT NULL,
784 `vlan_id` int(10) unsigned NOT NULL default '0',
785 `vlan_type` enum('ondemand','compulsory','alien') NOT NULL default 'ondemand',
786 `vlan_descr` char(255) default NULL,
787 PRIMARY KEY (`domain_id`,`vlan_id`),
788 KEY `vlan_id` (`vlan_id`),
219da133 789 CONSTRAINT `VLANDescription-FK-domain_id` FOREIGN KEY (`domain_id`) REFERENCES `VLANDomain` (`id`) ON DELETE CASCADE,
22fdebff
DO
790 CONSTRAINT `VLANDescription-FK-vlan_id` FOREIGN KEY (`vlan_id`) REFERENCES `VLANValidID` (`vlan_id`)
791) ENGINE=InnoDB
792";
793 $query[] = "
794CREATE TABLE `VLANDomain` (
795 `id` int(10) unsigned NOT NULL auto_increment,
796 `description` char(255) default NULL,
797 PRIMARY KEY (`id`),
798 UNIQUE KEY `description` (`description`)
799) ENGINE=InnoDB
800";
801 $query[] = "
802CREATE TABLE `VLANIPv4` (
803 `domain_id` int(10) unsigned NOT NULL,
804 `vlan_id` int(10) unsigned NOT NULL,
805 `ipv4net_id` int(10) unsigned NOT NULL,
806 UNIQUE KEY `network-domain` (`ipv4net_id`,`domain_id`),
807 KEY `VLANIPv4-FK-compound` (`domain_id`,`vlan_id`),
808 CONSTRAINT `VLANIPv4-FK-compound` FOREIGN KEY (`domain_id`, `vlan_id`) REFERENCES `VLANDescription` (`domain_id`, `vlan_id`) ON DELETE CASCADE,
809 CONSTRAINT `VLANIPv4-FK-ipv4net_id` FOREIGN KEY (`ipv4net_id`) REFERENCES `IPv4Network` (`id`) ON DELETE CASCADE
810) ENGINE=InnoDB
811";
812 $query[] = "
813CREATE TABLE `VLANSTRule` (
814 `vst_id` int(10) unsigned NOT NULL,
815 `rule_no` int(10) unsigned NOT NULL,
816 `port_pcre` char(255) NOT NULL,
817 `port_role` enum('access','trunk','uplink','downlink','none') NOT NULL default 'none',
818 `wrt_vlans` char(255) default NULL,
819 `description` char(255) default NULL,
820 UNIQUE KEY `vst-rule` (`vst_id`,`rule_no`),
821 CONSTRAINT `VLANSTRule-FK-vst_id` FOREIGN KEY (`vst_id`) REFERENCES `VLANSwitchTemplate` (`id`) ON DELETE CASCADE
822) ENGINE=InnoDB
823";
824 $query[] = "
825CREATE TABLE `VLANSwitch` (
826 `object_id` int(10) unsigned NOT NULL,
827 `domain_id` int(10) unsigned NOT NULL,
828 `template_id` int(10) unsigned NOT NULL,
829 `mutex_rev` int(10) unsigned NOT NULL default '0',
830 `out_of_sync` enum('yes','no') NOT NULL default 'yes',
831 `last_errno` int(10) unsigned NOT NULL default '0',
832 `last_change` timestamp NOT NULL default '0000-00-00 00:00:00',
833 `last_push_started` timestamp NOT NULL default '0000-00-00 00:00:00',
834 `last_push_finished` timestamp NOT NULL default '0000-00-00 00:00:00',
835 `last_error_ts` timestamp NOT NULL default '0000-00-00 00:00:00',
836 UNIQUE KEY `object_id` (`object_id`),
837 KEY `domain_id` (`domain_id`),
838 KEY `template_id` (`template_id`),
839 KEY `out_of_sync` (`out_of_sync`),
840 KEY `last_errno` (`last_errno`),
841 CONSTRAINT `VLANSwitch-FK-domain_id` FOREIGN KEY (`domain_id`) REFERENCES `VLANDomain` (`id`),
842 CONSTRAINT `VLANSwitch-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`),
843 CONSTRAINT `VLANSwitch-FK-template_id` FOREIGN KEY (`template_id`) REFERENCES `VLANSwitchTemplate` (`id`)
844) ENGINE=InnoDB
845";
846 $query[] = "
847CREATE TABLE `VLANSwitchTemplate` (
848 `id` int(10) unsigned NOT NULL auto_increment,
849 `max_local_vlans` int(10) unsigned default NULL,
850 `description` char(255) default NULL,
851 PRIMARY KEY (`id`),
852 UNIQUE KEY `description` (`description`)
853) ENGINE=InnoDB
854";
855 $query[] = "
856CREATE TABLE `VLANValidID` (
857 `vlan_id` int(10) unsigned NOT NULL default '1',
858 PRIMARY KEY (`vlan_id`)
859) ENGINE=InnoDB
860";
861 $query[] = "SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS";
fa11e5c7 862 for ($i = 1; $i <= 4094; $i++)
a401a680 863 $query[] = "INSERT INTO VLANValidID (vlan_id) VALUES (${i})";
9013f05b 864 $query[] = "UPDATE Config SET varvalue = '0.18.0' WHERE varname = 'DB_VERSION'";
08d1ef24 865 break;
425fd829 866 case '0.18.1':
b6978d62
DO
867 $query[] = "ALTER TABLE Atom ENGINE=InnoDB";
868 $query[] = "ALTER TABLE AttributeMap ENGINE=InnoDB";
869 $query[] = "ALTER TABLE Config ENGINE=InnoDB";
870 $query[] = "ALTER TABLE IPv4Address ENGINE=InnoDB";
871 $query[] = "ALTER TABLE IPv4Allocation ENGINE=InnoDB";
872 $query[] = "ALTER TABLE Molecule ENGINE=InnoDB";
873 $query[] = "ALTER TABLE MountOperation ENGINE=InnoDB";
874 $query[] = "ALTER TABLE PortCompat ENGINE=InnoDB";
875 $query[] = "ALTER TABLE Rack ENGINE=InnoDB";
876 $query[] = "ALTER TABLE RackHistory ENGINE=InnoDB";
877 $query[] = "ALTER TABLE RackObjectHistory ENGINE=InnoDB";
878 $query[] = "ALTER TABLE RackRow ENGINE=InnoDB";
879 $query[] = "ALTER TABLE RackSpace ENGINE=InnoDB";
880 $query[] = "ALTER TABLE Script ENGINE=InnoDB";
735f169f
DO
881 $query[] = "ALTER TABLE AttributeValue DROP FOREIGN KEY `AttributeValue-FK-object_id`";
882 $query[] = "ALTER TABLE AttributeValue ADD CONSTRAINT `AttributeValue-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE";
55267f67
DO
883 $query[] = "ALTER TABLE RackObjectHistory ADD KEY (id)";
884 $query[] = "ALTER TABLE RackObjectHistory ADD CONSTRAINT `RackObjectHistory-FK-object_id` FOREIGN KEY (id) REFERENCES `RackObject` (`id`) ON DELETE CASCADE";
f3552988 885 $query[] = "ALTER TABLE MountOperation ADD CONSTRAINT `MountOperation-FK-object_id` FOREIGN KEY (object_id) REFERENCES RackObject (id) ON DELETE CASCADE";
f7cec175 886 $query[] = "ALTER TABLE RackSpace ADD CONSTRAINT `RackSpace-FK-object_id` FOREIGN KEY (object_id) REFERENCES RackObject (id) ON DELETE CASCADE";
1bcfe894
DO
887 $query[] = "ALTER TABLE Link DROP FOREIGN KEY `Link-FK-a`";
888 $query[] = "ALTER TABLE Link ADD CONSTRAINT `Link-FK-a` FOREIGN KEY (`porta`) REFERENCES `Port` (`id`) ON DELETE CASCADE";
889 $query[] = "ALTER TABLE Link DROP FOREIGN KEY `Link-FK-b`";
890 $query[] = "ALTER TABLE Link ADD CONSTRAINT `Link-FK-b` FOREIGN KEY (`portb`) REFERENCES `Port` (`id`) ON DELETE CASCADE";
891 $query[] = "ALTER TABLE Port DROP FOREIGN KEY `Port-FK-object_id`";
892 $query[] = "ALTER TABLE Port ADD CONSTRAINT `Port-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE";
e7787961
DO
893 $query[] = "ALTER TABLE AttributeMap MODIFY `chapter_id` int(10) unsigned default NULL";
894 $query[] = "ALTER TABLE IPv4Address MODIFY `ip` int(10) unsigned NOT NULL default '0'";
895 $query[] = "ALTER TABLE IPv4Address MODIFY `name` char(255) NOT NULL default ''";
896 $query[] = "ALTER TABLE IPv4Allocation MODIFY `object_id` int(10) unsigned NOT NULL default '0'";
897 $query[] = "ALTER TABLE IPv4Allocation MODIFY `ip` int(10) unsigned NOT NULL default '0'";
898 $query[] = "ALTER TABLE IPv4Allocation MODIFY `name` char(255) NOT NULL default ''";
899 $query[] = "ALTER TABLE IPv4NAT MODIFY `object_id` int(10) unsigned NOT NULL default '0'";
900 $query[] = "ALTER TABLE IPv4NAT MODIFY `proto` enum('TCP','UDP') NOT NULL default 'TCP'";
901 $query[] = "ALTER TABLE IPv4NAT MODIFY `localip` int(10) unsigned NOT NULL default '0'";
902 $query[] = "ALTER TABLE IPv4NAT MODIFY `localport` smallint(5) unsigned NOT NULL default '0'";
903 $query[] = "ALTER TABLE IPv4NAT MODIFY `remoteip` int(10) unsigned NOT NULL default '0'";
904 $query[] = "ALTER TABLE IPv4NAT MODIFY `remoteport` smallint(5) unsigned NOT NULL default '0'";
905 $query[] = "ALTER TABLE IPv4Network MODIFY `ip` int(10) unsigned NOT NULL default '0'";
906 $query[] = "ALTER TABLE IPv4Network MODIFY `mask` int(10) unsigned NOT NULL default '0'";
907 $query[] = "ALTER TABLE Link MODIFY `porta` int(10) unsigned NOT NULL default '0'";
908 $query[] = "ALTER TABLE Link MODIFY `portb` int(10) unsigned NOT NULL default '0'";
909 $query[] = "ALTER TABLE MountOperation MODIFY `object_id` int(10) unsigned NOT NULL default '0'";
910 $query[] = "ALTER TABLE MountOperation MODIFY `ctime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP";
911 $query[] = "ALTER TABLE Port MODIFY `object_id` int(10) unsigned NOT NULL default '0'";
912 $query[] = "ALTER TABLE Port MODIFY `name` char(255) NOT NULL default ''";
913 $query[] = "ALTER TABLE Port MODIFY `type` int(10) unsigned NOT NULL default '0'";
914 $query[] = "ALTER TABLE PortCompat MODIFY `type1` int(10) unsigned NOT NULL default '0'";
915 $query[] = "ALTER TABLE PortCompat MODIFY `type2` int(10) unsigned NOT NULL default '0'";
916 $query[] = "ALTER TABLE RackHistory MODIFY `ctime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP";
917 $query[] = "ALTER TABLE RackObjectHistory MODIFY `ctime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP";
918 $query[] = "ALTER TABLE TagStorage MODIFY `tag_id` int(10) unsigned NOT NULL default '0'";
919 $query[] = "ALTER TABLE UserAccount MODIFY `user_name` char(64) NOT NULL default ''";
425fd829
DO
920 $query[] = "UPDATE Config SET varvalue = '0.18.1' WHERE varname = 'DB_VERSION'";
921 break;
298d2375 922 case '0.18.2':
298d2375 923 $query[] = "ALTER TABLE Rack ADD CONSTRAINT `Rack-FK-row_id` FOREIGN KEY (row_id) REFERENCES RackRow (id)";
b504972c 924 $query[] = "ALTER TABLE RackRow ADD UNIQUE KEY `name` (name)";
b49a479e
DO
925 $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')";
926 $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 927 $query[] = "UPDATE Config SET varvalue = '0.18.2' WHERE varname = 'DB_VERSION'";
298d2375 928 break;
2582446d
DO
929 case '0.18.3':
930 $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 931 $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
932 $query[] = "UPDATE Config SET varvalue = '0.18.3' WHERE varname = 'DB_VERSION'";
933 break;
3a387b0d 934 case '0.18.4':
ec523868 935 $query[] = "ALTER TABLE VLANSTRule MODIFY port_role enum('access','trunk','anymode','uplink','downlink','none') NOT NULL default 'none'";
3a387b0d
DO
936 $query[] = "UPDATE Config SET varvalue = '0.18.4' WHERE varname = 'DB_VERSION'";
937 break;
95857b5c
DO
938 case '0.18.5':
939 $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 940 $query[] = "ALTER TABLE `IPv4LB` ADD COLUMN `prio` int(10) unsigned DEFAULT NULL AFTER `vs_id`";
1ebbf889 941 $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 942 $query[] = "UPDATE Config SET varvalue = '0.18.5' WHERE varname = 'DB_VERSION'";
f2f95f99
JT
943 break;
944 case '0.18.6':
f2f95f99 945 $query[] = "UPDATE Config SET varvalue = '0.18.6' WHERE varname = 'DB_VERSION'";
95857b5c 946 break;
9fb6900d 947 case '0.18.7':
9fb6900d
DO
948 $query[] = "UPDATE Config SET varvalue = '0.18.7' WHERE varname = 'DB_VERSION'";
949 break;
d3346ce2
DO
950 case '0.19.0':
951 $query[] = 'ALTER TABLE `File` ADD `thumbnail` LONGBLOB NULL AFTER `atime`';
21ee3351
AA
952 $query[] = "
953CREATE TABLE `IPv6Address` (
954 `ip` binary(16) NOT NULL,
955 `name` char(255) NOT NULL default '',
956 `reserved` enum('yes','no') default NULL,
957 PRIMARY KEY (`ip`)
958) ENGINE=InnoDB
959";
960 $query[] = "
961CREATE TABLE `IPv6Allocation` (
962 `object_id` int(10) unsigned NOT NULL default '0',
963 `ip` binary(16) NOT NULL,
964 `name` char(255) NOT NULL default '',
965 `type` enum('regular','shared','virtual','router') default NULL,
8c7b7381
AA
966 PRIMARY KEY (`object_id`,`ip`),
967 CONSTRAINT `IPv6Allocation-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE
21ee3351
AA
968) ENGINE=InnoDB
969";
970 $query[] = "
971CREATE TABLE `IPv6Network` (
972 `id` int(10) unsigned NOT NULL auto_increment,
973 `ip` binary(16) NOT NULL,
974 `mask` int(10) unsigned NOT NULL,
975 `last_ip` binary(16) NOT NULL,
976 `name` char(255) default NULL,
977 `comment` text,
978 PRIMARY KEY (`id`),
979 UNIQUE KEY `ip` (`ip`,`mask`)
980) ENGINE=InnoDB
981";
982 $query[] = "
983CREATE TABLE `VLANIPv6` (
984 `domain_id` int(10) unsigned NOT NULL,
985 `vlan_id` int(10) unsigned NOT NULL,
986 `ipv6net_id` int(10) unsigned NOT NULL,
987 UNIQUE KEY `network-domain` (`ipv6net_id`,`domain_id`),
988 KEY `VLANIPv6-FK-compound` (`domain_id`,`vlan_id`),
989 CONSTRAINT `VLANIPv6-FK-compound` FOREIGN KEY (`domain_id`, `vlan_id`) REFERENCES `VLANDescription` (`domain_id`, `vlan_id`) ON DELETE CASCADE,
990 CONSTRAINT `VLANIPv6-FK-ipv6net_id` FOREIGN KEY (`ipv6net_id`) REFERENCES `IPv6Network` (`id`) ON DELETE CASCADE
991) ENGINE=InnoDB
9a90adc4
DO
992";
993 $query[] = "
8489d2af
DO
994CREATE TABLE IF NOT EXISTS `ObjectLog` (
995 `id` int(10) NOT NULL AUTO_INCREMENT,
996 `object_id` int(10) NOT NULL,
997 `user` varchar(64) NOT NULL,
9a90adc4
DO
998 `date` datetime NOT NULL,
999 `content` text NOT NULL,
8489d2af 1000 PRIMARY KEY (`id`)
9a90adc4 1001) ENGINE=InnoDB
0682218d 1002";
8489d2af
DO
1003 # Now we have the same structure of ObjectLog table, which objectlog.php
1004 # could have left. Subsequent column updates will handle any existing data.
1005 $query[] = "ALTER TABLE ObjectLog MODIFY COLUMN `id` int(10) unsigned NOT NULL AUTO_INCREMENT";
1006 $query[] = "ALTER TABLE ObjectLog MODIFY COLUMN `object_id` int(10) unsigned NOT NULL";
1007 $query[] = "ALTER TABLE ObjectLog MODIFY COLUMN `user` char(64) NOT NULL";
1008 $query[] = "ALTER TABLE ObjectLog ADD KEY `object_id` (`object_id`)";
1009 $query[] = "ALTER TABLE ObjectLog ADD KEY `date` (`date`)";
1010 $query[] = "ALTER TABLE ObjectLog ADD CONSTRAINT `ObjectLog-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE";
1011 # Now it's the way 0.19.0 is expecting it to be.
0682218d
AD
1012 $query[] = "
1013CREATE TABLE `ObjectParentCompat` (
1014 `parent_objtype_id` int(10) unsigned NOT NULL,
1015 `child_objtype_id` int(10) unsigned NOT NULL,
1016 UNIQUE KEY `parent_child` (`parent_objtype_id`,`child_objtype_id`)
1017) ENGINE=InnoDB
1018";
1019 $query[] = "
1020CREATE TABLE `EntityLink` (
1021 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
1022 `parent_entity_type` enum('ipv4net','ipv4rspool','ipv4vs','ipv6net','object','rack','user') NOT NULL,
1023 `parent_entity_id` int(10) unsigned NOT NULL,
1024 `child_entity_type` enum('file','object') NOT NULL,
1025 `child_entity_id` int(10) unsigned NOT NULL,
1026 PRIMARY KEY (`id`),
1027 UNIQUE KEY `EntityLink-unique` (`parent_entity_type`,`parent_entity_id`,`child_entity_type`,`child_entity_id`)
1028) ENGINE=InnoDB
21ee3351
AA
1029";
1030 $query[] = "ALTER TABLE `TagStorage` CHANGE COLUMN `entity_realm` `entity_realm` ENUM('file','ipv4net','ipv4vs','ipv4rspool','object','rack','user','ipv6net') NOT NULL DEFAULT 'object' FIRST";
1031 $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 1032 $query[] = 'ALTER TABLE Link ADD COLUMN cable char(64) NULL AFTER portb';
3fb61857 1033 $query[] = 'ALTER TABLE RackSpace ADD CONSTRAINT `RackSpace-FK-rack_id` FOREIGN KEY (rack_id) REFERENCES Rack (id)';
8c7b7381 1034 $query[] = "ALTER TABLE `IPv4Allocation` ADD CONSTRAINT `IPv4Allocation-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE";
61e79d63 1035 $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 1036 $query[] = "ALTER TABLE `VLANSwitchTemplate` ADD COLUMN `mutex_rev` int(10) NOT NULL AFTER `id`";
09ec2e59 1037 $query[] = "ALTER TABLE `VLANSwitchTemplate` ADD COLUMN `saved_by` char(64) NOT NULL AFTER `description`";
0682218d
AD
1038 $query[] = "INSERT INTO `Attribute` (`id`, `type`, `name`) VALUES (26,'dict','Hypervisor')";
1039 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (29,'no','Yes/No')";
1040 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (30,'no','network chassis models')";
1041 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (31,'no','server chassis models')";
1042 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (32,'no','virtual switch models')";
1043 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (33,'no','virtual switch OS type')";
1044 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (4,26,29)";
1045 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1502,1,NULL)";
1046 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1502,2,31)";
1047 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1502,3,NULL)";
1048 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1502,14,NULL)";
1049 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1502,20,NULL)";
1050 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1502,21,NULL)";
1051 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1502,22,NULL)";
1052 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,1,NULL)";
1053 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,2,30)";
1054 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,3,NULL)";
588c98f8
AD
1055 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,4,14)";
1056 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,5,NULL)";
0682218d 1057 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,14,NULL)";
588c98f8
AD
1058 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,16,NULL)";
1059 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,17,NULL)";
1060 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,18,NULL)";
0682218d
AD
1061 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,20,NULL)";
1062 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,21,NULL)";
1063 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,22,NULL)";
588c98f8 1064 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,24,NULL)";
0682218d
AD
1065 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1504,3,NULL)";
1066 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1504,4,13)";
1067 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1504,14,NULL)";
1068 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1504,24,NULL)";
1069 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1505,14,NULL)";
1070 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1506,14,NULL)";
1071 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1506,17,NULL)";
1072 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1506,18,NULL)";
1073 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,1,NULL)";
1074 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,2,32)";
1075 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,3,NULL)";
1076 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,4,33)";
1077 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,5,NULL)";
1078 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,14,NULL)";
1079 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,20,NULL)";
1080 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,21,NULL)";
1081 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,22,NULL)";
1082 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (3,13)";
1083 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (4,1504)";
1084 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (4,1507)";
1085 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1502,4)";
1086 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1503,8)";
1087 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1505,4)";
1088 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1505,1504)";
1089 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1505,1506)";
1090 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1505,1507)";
1091 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1506,1504)";
0682218d 1092 $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')";
322ce490 1093 $query[] = "UPDATE Config SET varvalue = CONCAT(varvalue, ' or {\$typeid_1502} or {\$typeid_1503} or {\$typeid_1504} or {\$typeid_1507}') WHERE varname = 'IPV4OBJ_LISTSRC'";
0682218d 1094 $query[] = "UPDATE Config SET varvalue = '8' WHERE varname = 'MASSCOUNT'";
993f5fa3 1095 $query[] = "UPDATE RackObject SET label = NULL WHERE label = ''";
81659c05
AD
1096 // Move barcode data so the column can be dropped
1097 $result = $dbxlink->query ('SELECT id, objtype_id, barcode FROM RackObject WHERE barcode IS NOT NULL');
a7978f7f
AD
1098 $rows = $result->fetchAll (PDO::FETCH_ASSOC);
1099 unset ($result);
1100 foreach ($rows as $row)
81659c05
AD
1101 {
1102 // Determine if this object type has the 'OEM S/N 1' attribute associated with it, and if it's set
1103 $sn_query = "SELECT (SELECT COUNT(*) FROM AttributeMap WHERE objtype_id=${row['objtype_id']} AND attr_id=1) AS AM_count, ";
1104 $sn_query .= "(SELECT COUNT(*) FROM AttributeValue WHERE object_id=${row['id']} AND attr_id=1) AS AV_count";
1105 $sn_result = $dbxlink->query ($sn_query);
1106 $sn_row = $sn_result->fetch (PDO::FETCH_ASSOC);
1107 if ($sn_row['AM_count'] == 1 && $sn_row['AV_count'] == 0)
1108 {
1109 // 'OEM S/N 1' attribute is mapped to this object type, but it is not set. Good!
1110 // Copy the barcode value to the attribute.
1111 $query[] = "INSERT INTO AttributeValue (`object_id`, `attr_id`, `string_value`) VALUES (${row['id']}, 1, '${row['barcode']}')";
1112 }
1113 else
1114 {
1115 // Some other set of circumstances. Not as good!
1116 // Copy the barcode value to a new ObjectLog record.
1117 $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']}')";
1118 }
1119 unset ($sn_query, $sn_result, $sn_row);
1120 }
81659c05
AD
1121 $query[] = 'ALTER TABLE RackObject DROP COLUMN `barcode`';
1122 $query[] = 'ALTER TABLE RackObjectHistory DROP COLUMN `barcode`';
fbeacc34 1123 $query[] = 'ALTER TABLE `VLANSwitchTemplate` DROP COLUMN `max_local_vlans`';
81659c05 1124 $query[] = "UPDATE Config SET varvalue = '0.19.0' WHERE varname = 'DB_VERSION'";
d3346ce2 1125 break;
6d42599c 1126 case '0.19.1':
1d5dd3a1
AD
1127 $query[] = "ALTER TABLE `Config` CHANGE COLUMN `varvalue` `varvalue` text NOT NULL";
1128 $query[] = "ALTER TABLE `UserConfig` CHANGE COLUMN `varvalue` `varvalue` text NOT NULL";
6d42599c 1129 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('FILTER_RACKLIST_BY_TAGS','yes','string','yes','no','yes','Rackspace: show only racks matching the current object\'s tags')";
782ad4cd
DO
1130 $result = $dbxlink->query ("SHOW TABLES LIKE 'Objectlog'");
1131 $rows = $result->fetchAll (PDO::FETCH_ASSOC);
1132 unset ($result);
1133 if (count ($rows))
1134 {
1135 # Now the ObjectLog merge... again, because the original table is named
1136 # "Objectlog". The job is to merge contents of Objectlog and ObjectLog
1137 # into the latter.
1138 $query[] = "INSERT INTO ObjectLog (object_id, user, date, content) SELECT object_id, user, date, content FROM Objectlog WHERE object_id IN(SELECT id FROM RackObject)";
1139 $query[] = "DELETE FROM Objectlog WHERE object_id IN(SELECT id FROM RackObject)";
1140 # Don't delete the old table, if the merge wasn't exhaustive.
1141 $result = $dbxlink->query ('SELECT COUNT(*) AS c FROM Objectlog WHERE object_id NOT IN(SELECT id FROM RackObject)');
1142 $row = $result->fetch (PDO::FETCH_ASSOC);
1143 unset ($result);
1144 if ($row['c'] == 0)
1145 $query[] = 'DROP TABLE Objectlog';
1146 else
1147 $query[] = 'ALTER TABLE Objectlog RENAME TO Objectlog_old_unmerged';
1148 }
6d42599c
AA
1149 $query[] = "UPDATE Config SET varvalue = '0.19.1' WHERE varname = 'DB_VERSION'";
1150 break;
f701420b 1151 case '0.19.2':
f701420b
AA
1152 $query[] = "ALTER TABLE IPv4Allocation ADD KEY `ip` (`ip`)";
1153 $query[] = "ALTER TABLE IPv6Allocation ADD KEY `ip` (`ip`)";
1154 $query[] = "ALTER TABLE IPv4VS ADD KEY `vip` (`vip`)";
1155 $query[] = "ALTER TABLE IPv4RS ADD KEY `rsip` (`rsip`)";
ae67fa11
AD
1156 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (34,'no','power supply chassis models')";
1157 $query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (35,'no','power supply models')";
1158 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1397,1,NULL)";
1159 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1397,2,34)";
1160 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1397,14,NULL)";
1161 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1397,21,NULL)";
1162 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1397,22,NULL)";
1163 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1398,1,NULL)";
1164 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1398,2,35)";
1165 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1398,14,NULL)";
1166 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1398,21,NULL)";
1167 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1398,22,NULL)";
1168 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1397,1398)";
1169 $query[] = "INSERT INTO `PortCompat` (`type1`, `type2`) VALUES (1399,1399)";
1170 $query[] = "INSERT INTO `PortInterfaceCompat` (`iif_id`, `oif_id`) VALUES (1,1399)";
1171 $query[] = "UPDATE Config SET varvalue = CONCAT(varvalue, ' or {\$typeid_1397}') WHERE varname = 'IPV4OBJ_LISTSRC'";
c4ad9ac0
AA
1172 $query[] = "ALTER TABLE AttributeValue ADD KEY `attr_id-uint_value` (`attr_id`,`uint_value`)";
1173 $query[] = "ALTER TABLE AttributeValue ADD KEY `attr_id-string_value` (`attr_id`,`string_value`(12))";
0abae5fb 1174 $query[] = "UPDATE Config SET varvalue = '0.19.2' WHERE varname = 'DB_VERSION'";
f701420b 1175 break;
6d2cc13e 1176 case '0.19.3':
6d2cc13e
DO
1177 $query[] = "DELETE FROM RackSpace WHERE object_id IS NULL AND state = 'T'";
1178 $query[] = "UPDATE Config SET varvalue = '0.19.3' WHERE varname = 'DB_VERSION'";
1179 break;
1f02e311 1180 case '0.19.4':
1f02e311
AD
1181 $query[] = "UPDATE Config SET varvalue = '0.19.4' WHERE varname = 'DB_VERSION'";
1182 break;
e1486971 1183 case '0.19.5':
e1486971
DO
1184 // Add 'virtual port' to 'virtual port' mapping
1185 $query[] = "INSERT INTO `PortCompat` (`type1`,`type2`) VALUES (1469,1469)";
1186 $query[] = "INSERT INTO `PortInterfaceCompat` (`iif_id`,`oif_id`) VALUES (1,1469)";
25b8a91f
AA
1187 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('SSH_OBJS_LISTSRC','none','string','yes','no','yes','Rackcode filter for SSH-managed objects')";
1188 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('TELNET_OBJS_LISTSRC','none','string','yes','no','yes','Rackcode filter for telnet-managed objects')";
7d91adc7 1189 $query[] = "UPDATE Link SET cable = NULL WHERE cable = ''";
42b4722b 1190 $query[] = "ALTER TABLE AttributeValue MODIFY string_value char(255) DEFAULT NULL";
e1486971
DO
1191 $query[] = "UPDATE Config SET varvalue = '0.19.5' WHERE varname = 'DB_VERSION'";
1192 break;
2a3a1a6d 1193 case '0.19.6':
2a3a1a6d
AA
1194 $query[] = "UPDATE Config SET varvalue = '0.19.6' WHERE varname = 'DB_VERSION'";
1195 break;
b1bde5f6 1196 case '0.19.7':
b1bde5f6
AD
1197 # A plain "ALTER TABLE Attribute" can leave AUTO_INCREMENT in an odd
1198 # state, hence the table swap.
1199 $query[] = "
1200CREATE TABLE `Attribute_new` (
1201 `id` int(10) unsigned NOT NULL auto_increment,
1202 `type` enum('string','uint','float','dict') default NULL,
1203 `name` char(64) default NULL,
1204 PRIMARY KEY (`id`),
1205 UNIQUE KEY `name` (`name`)
1206) ENGINE=InnoDB
1207";
1208 $query[] = "INSERT INTO Attribute_new SELECT * FROM Attribute";
1209 $query[] = "INSERT INTO Attribute_new VALUES (9999, 'string', 'base MAC address')";
1210 $query[] = "DROP TABLE Attribute";
1211 $query[] = "ALTER TABLE Attribute_new RENAME TO Attribute";
1212 $query[] = "ALTER TABLE AttributeMap ADD KEY (attr_id)";
1213 $query[] = "DELETE FROM AttributeMap WHERE attr_id NOT IN (SELECT id FROM Attribute)";
1214 $query[] = "ALTER TABLE AttributeMap ADD CONSTRAINT `AttributeMap-FK-attr_id` FOREIGN KEY (attr_id) REFERENCES Attribute (id)";
1215 $query[] = "DELETE FROM AttributeValue WHERE attr_id NOT IN (SELECT attr_id FROM AttributeMap)";
1216 $query[] = "ALTER TABLE AttributeValue ADD CONSTRAINT `AttributeValue-FK-attr_id` FOREIGN KEY (attr_id) REFERENCES AttributeMap (attr_id)";
1217 $query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1506,4)";
44cbeb2e
DO
1218 $query[] = "INSERT INTO PortInnerInterface (id, iif_name) VALUES (10, 'QSFP+')";
1219 $query[] = "INSERT INTO PortInterfaceCompat VALUES (10, 1588)";
b1bde5f6
AD
1220 $query[] = "UPDATE Config SET varvalue = '0.19.7' WHERE varname = 'DB_VERSION'";
1221 break;
a03332f7 1222 case '0.19.8':
3aca495a
DO
1223 for ($i = 1424; $i <= 1466; $i++) # CX, then 42 ER channels
1224 $query[] = "INSERT INTO `PortCompat` (`type1`, `type2`) VALUES (${i},${i})";
c7968df3
DO
1225 $query[] = "ALTER TABLE UserAccount ENGINE=InnoDB";
1226 $query[] = "DELETE FROM UserConfig WHERE user NOT IN (SELECT user_name FROM UserAccount)";
1227 $query[] = "ALTER TABLE UserConfig ADD CONSTRAINT `UserConfig-FK-user` FOREIGN KEY (user) REFERENCES UserAccount (user_name) ON DELETE CASCADE";
1228 $query[] = "DELETE FROM UserConfig WHERE varname NOT IN (SELECT varname FROM Config)";
1229 $query[] = "ALTER TABLE UserConfig ADD KEY (varname)";
1230 $query[] = "ALTER TABLE UserConfig ADD CONSTRAINT `UserConfig-FK-varname` FOREIGN KEY (varname) REFERENCES Config (varname) ON DELETE CASCADE";
a03332f7 1231 $query[] = "ALTER TABLE Dictionary ENGINE=InnoDB";
c7968df3
DO
1232 $query[] = "ALTER TABLE Chapter ENGINE=InnoDB";
1233 $query[] = "UPDATE Chapter SET id = 9999 WHERE id = 22";
1234 $query[] = "UPDATE AttributeMap SET chapter_id = 9999 WHERE chapter_id = 22";
1235 $query[] = "UPDATE Dictionary SET chapter_id = 9999 WHERE chapter_id = 22";
1236 $query[] = "DELETE FROM Dictionary WHERE chapter_id NOT IN (SELECT id FROM Chapter)";
1237 $query[] = "ALTER TABLE Dictionary ADD CONSTRAINT `Dictionary-FK-chapter_id` FOREIGN KEY (chapter_id) REFERENCES Chapter (id)";
1238 $query[] = "DELETE FROM AttributeMap WHERE chapter_id NOT IN (SELECT id FROM Chapter)";
1239 $query[] = "ALTER TABLE AttributeMap ADD KEY (chapter_id)";
1240 $query[] = "ALTER TABLE AttributeMap ADD CONSTRAINT `AttributeMap-FK-chapter_id` FOREIGN KEY (chapter_id) REFERENCES Chapter (id)";
c09757f6
DO
1241 $query[] = "
1242CREATE TABLE `CactiGraph` (
1243 `object_id` int(10) unsigned NOT NULL,
1244 `graph_id` int(10) unsigned NOT NULL,
1245 `caption` char(255) DEFAULT NULL,
1246 PRIMARY KEY (`graph_id`),
1247 KEY `object_id` (`object_id`),
4d082372 1248 CONSTRAINT `CactiGraph-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE
c09757f6
DO
1249) ENGINE=InnoDB;
1250";
1251 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('CACTI_LISTSRC','false','string','yes','no','no','List of object with Cacti graphs')";
1252 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('CACTI_URL','','string','yes','no','no','Cacti server base URL')";
1253 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('CACTI_USERNAME','','string','yes','no','no','Cacti user account')";
1254 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('CACTI_USERPASS','','string','yes','no','no','Cacti user password')";
a03332f7
DO
1255 $query[] = "UPDATE Config SET varvalue = '0.19.8' WHERE varname = 'DB_VERSION'";
1256 break;
5c0bd7de 1257 case '0.19.9':
5c0bd7de 1258 $query[] = "DELETE FROM Config WHERE varname = 'HNDP_RUNNERS_LISTSRC'";
8920f7f5
DO
1259 # Dismiss some overly-specific OIF types in favour of more generic counterparts.
1260 $squeeze = array
1261 (
1262 1202 => array # 1000Base-SX
1263 (
1264 25, # 1000Base-SX (SC)
1265 26, # 1000Base-SX (LC)
1266 ),
1267 1204 => array # 1000Base-LX
1268 (
1269 27, # 1000Base-LX (SC)
1270 28, # 1000Base-LX (LC)
1271 ),
1272 1196 => array # 100Base-SX
1273 (
1274 22, # 100Base-SX (SC)
1275 23, # 100Base-SX (LC)
1276 ),
1277 1195 => array # 100Base-FX
1278 (
1279 20, # 100Base-FX (SC)
1280 21, # 100Base-FX (LC)
1281 1083, # 100Base-FX (MT-RJ)
1282 ),
1283 );
1284 foreach ($squeeze as $stays => $leaves)
1285 {
1286 $csv = implode (', ', $leaves);
1287 $query[] = "DELETE FROM PortCompat WHERE type1 IN(${csv}) OR type2 IN(${csv})";
1288 $query[] = "INSERT IGNORE INTO PortInterfaceCompat (iif_id, oif_id) SELECT iif_id, ${stays} FROM Port WHERE type IN (${csv})";
1289 $query[] = "UPDATE Port SET type = ${stays} WHERE type IN(${csv})";
1290 $query[] = "DELETE FROM PortInterfaceCompat WHERE oif_id IN(${csv})";
1291 }
fd8de939
DO
1292 $query[] = "UPDATE Config SET varvalue = '0.19.9' WHERE varname = 'DB_VERSION'";
1293 break;
f7494e3c 1294 case '0.19.10':
f7494e3c
DO
1295 $query[] = "INSERT INTO `PortCompat` (`type1`, `type2`) VALUES (1603,1603)";
1296 $query[] = "UPDATE Config SET varvalue = '0.19.10' WHERE varname = 'DB_VERSION'";
1297 break;
d0004c46 1298 case '0.19.11':
8dc2a6c8 1299 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('VIRTUAL_OBJ_LISTSRC','1504,1505,1506,1507','string','no','no','no','List source: virtual objects')";
453cce7e
AD
1300 $query[] = "INSERT INTO `Attribute` (`id`,`type`,`name`) VALUES (28,'string','Slot number')";
1301 $query[] = 'INSERT INTO `AttributeMap` (`objtype_id`,`attr_id`,`chapter_id`) VALUES (4,28,NULL)';
1302 $query[] = 'INSERT INTO `AttributeMap` (`objtype_id`,`attr_id`,`chapter_id`) VALUES (8,28,NULL)';
1303 $query[] = 'INSERT INTO `AttributeMap` (`objtype_id`,`attr_id`,`chapter_id`) VALUES (798,28,NULL)';
1304 $query[] = 'INSERT INTO `AttributeMap` (`objtype_id`,`attr_id`,`chapter_id`) VALUES (1055,28,NULL)';
7d68a0fb 1305 $query[] = 'ALTER TABLE AttributeValue ADD COLUMN object_tid int(10) unsigned NOT NULL default 0 AFTER object_id';
4d082372 1306 $query[] = 'UPDATE AttributeValue SET object_tid = (SELECT objtype_id FROM RackObject WHERE id = object_id)';
7d68a0fb
DO
1307 $query[] = 'ALTER TABLE AttributeValue DROP FOREIGN KEY `AttributeValue-FK-attr_id`';
1308 $query[] = 'ALTER TABLE AttributeValue DROP FOREIGN KEY `AttributeValue-FK-object_id`';
1309 $query[] = 'ALTER TABLE AttributeValue ADD KEY `id-tid` (object_id, object_tid)';
1310 $query[] = 'ALTER TABLE AttributeValue ADD KEY `object_tid-attr_id` (`object_tid`,`attr_id`)';
4d082372
AD
1311 $query[] = 'ALTER TABLE RackObject ADD KEY `id-tid` (id, objtype_id)';
1312 $query[] = 'ALTER TABLE AttributeValue ADD CONSTRAINT `AttributeValue-FK-object` FOREIGN KEY (`object_id`, `object_tid`) REFERENCES `RackObject` (`id`, `objtype_id`) ON DELETE CASCADE ON UPDATE CASCADE';
7d68a0fb 1313 $query[] = 'ALTER TABLE AttributeValue ADD CONSTRAINT `AttributeValue-FK-map` FOREIGN KEY (`object_tid`, `attr_id`) REFERENCES `AttributeMap` (`objtype_id`, `attr_id`)';
9e906292
DO
1314 # 0.19.9 did it right, but kept the IDs in the dictionary. This time
1315 # the dictionary is reduced, but the procedure needs to be repeated,
1316 # in case the user had enough time to use the wrong IDs again.
1317 $squeeze = array
1318 (
1319 1202 => array # 1000Base-SX
1320 (
1321 25, # 1000Base-SX (SC)
1322 26, # 1000Base-SX (LC)
1323 ),
1324 1204 => array # 1000Base-LX
1325 (
1326 27, # 1000Base-LX (SC)
1327 28, # 1000Base-LX (LC)
1328 ),
1329 1196 => array # 100Base-SX
1330 (
1331 22, # 100Base-SX (SC)
1332 23, # 100Base-SX (LC)
1333 ),
1334 1195 => array # 100Base-FX
1335 (
1336 20, # 100Base-FX (SC)
1337 21, # 100Base-FX (LC)
1338 1083, # 100Base-FX (MT-RJ)
1339 ),
1340 );
1341 foreach ($squeeze as $stays => $leaves)
1342 {
1343 $csv = implode (', ', $leaves);
1344 $query[] = "DELETE FROM PortCompat WHERE type1 IN(${csv}) OR type2 IN(${csv})";
1345 $query[] = "INSERT IGNORE INTO PortInterfaceCompat (iif_id, oif_id) SELECT iif_id, ${stays} FROM Port WHERE type IN (${csv})";
1346 $query[] = "UPDATE Port SET type = ${stays} WHERE type IN(${csv})";
1347 $query[] = "DELETE FROM PortInterfaceCompat WHERE oif_id IN(${csv})";
1348 }
18680729 1349 $query[] = "INSERT INTO `PortCompat` (`type1`, `type2`) VALUES (1642,1642)";
d6e7a8e7 1350 $query[] = 'ALTER TABLE `EntityLink` ADD KEY `EntityLink-compound` (`parent_entity_type`,`child_entity_type`,`child_entity_id`)';
d0004c46
AD
1351 $query[] = "UPDATE Config SET varvalue = '0.19.11' WHERE varname = 'DB_VERSION'";
1352 break;
7cf0bc68
DO
1353 # Batch 0.19.12 contained minor, but annoying bugs and was modified after the
1354 # release of version 0.19.12 (which should be avoided if possible). The best
1355 # way to resolve this particular case was to recall RackTables-0.19.12.tar.gz
1356 # from the download area and provide RackTables-0.19.13.tar.gz containing the
1357 # modified 0.19.12 batch.
a4c50c50 1358 case '0.19.12':
f6252853 1359 $query[] = "DELETE FROM Config WHERE varname IN('color_F', 'color_A', 'color_U', 'color_T', 'color_Th', 'color_Tw', 'color_Thw')";
2ce1cbdb
DO
1360 $query[] = "INSERT INTO Chapter (id, sticky, name) VALUES (36,'no','serial console server models')";
1361 $query[] = "INSERT INTO AttributeMap (objtype_id, attr_id, chapter_id) VALUES (1644, 1, NULL), (1644, 2, 36), (1644, 3, NULL)";
01d821c9 1362 $query[] = "UPDATE Config SET varvalue = CONCAT(varvalue, '; 10=1588') WHERE varname = 'DEFAULT_PORT_OIF_IDS' AND 0 = INSTR(varvalue, '10=') ";
55732b46
DO
1363 $query[] = "INSERT INTO PortInterfaceCompat VALUES (10,1663), (10,1664)";
1364 $query[] = "INSERT INTO PortCompat VALUES (1588,1588), (1661,1661), (1663,1663), (1664,1664)";
833583b5
DO
1365 $query[] = "INSERT INTO PortInnerInterface (id, iif_name) VALUES (11, 'CFP')";
1366 $query[] = "INSERT INTO PortInterfaceCompat (iif_id, oif_id) VALUES (11,1668),(11,1669),(11,1670),(11,1671)";
1367 $query[] = "INSERT INTO PortCompat (type1, type2) VALUES (1668,1668), (1669,1669), (1670,1670), (1671,1671)";
01d821c9 1368 $query[] = "UPDATE Config SET varvalue = CONCAT(varvalue, '; 11=1668') WHERE varname = 'DEFAULT_PORT_OIF_IDS'";
0c652e58
DO
1369 $query[] = "INSERT INTO Chapter (id, sticky, name) VALUES (37, 'no', 'wireless OS type')";
1370 $query[] = "INSERT INTO AttributeMap (objtype_id, attr_id, chapter_id) VALUES (965, 4, 37)";
a4c50c50
DO
1371 $query[] = "UPDATE Config SET varvalue = '0.19.12' WHERE varname = 'DB_VERSION'";
1372 break;
86ff26ae
DO
1373 case '0.19.13':
1374 // add the date attribute type
1375 $query[] = "ALTER TABLE `Attribute` CHANGE COLUMN `type` `type` enum('string','uint','float','dict','date') DEFAULT NULL";
0ec44a91
AA
1376 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('DATETIME_ZONE','UTC','string','yes','no','yes','Timezone to use for displaying/calculating dates')";
1377 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('DATETIME_FORMAT','m/d/Y','string','no','no','yes','PHP date() format to use for date output')";
86ff26ae
DO
1378
1379 // port over existing fields to new date attr type
1380 $query[] = "UPDATE Attribute SET type='date' WHERE id IN (21,22,24)";
1381 $query[] = "UPDATE AttributeValue SET uint_value=UNIX_TIMESTAMP(STR_TO_DATE(string_value, '%m/%d/%Y')) WHERE attr_id IN(21,22,24)";
1382
0ec44a91
AA
1383 // some config variables should be configurable in per-user basis
1384 $query[] = "UPDATE `Config` SET `is_userdefined` = 'yes' WHERE `varname` IN ('ENABLE_MULTIPORT_FORM','FILTER_DEFAULT_ANDOR','FILTER_PREDICATE_SIEVE','FILTER_SUGGEST_ANDOR','FILTER_SUGGEST_EXTRA','FILTER_SUGGEST_PREDICATES','FILTER_SUGGEST_TAGS','IPV4_ENABLE_KNIGHT','IPV4_TREE_RTR_AS_CELL','TAGS_QUICKLIST_SIZE','TAGS_QUICKLIST_THRESHOLD','TAGS_TOPLIST_SIZE')";
1385
86ff26ae
DO
1386 $query[] = "UPDATE Config SET varvalue = '0.19.13' WHERE varname = 'DB_VERSION'";
1387 break;
b55f913c
DO
1388 case '0.19.14':
1389 $query[] = "ALTER TABLE AttributeValue CHANGE COLUMN `object_id` `object_id` INT(10) UNSIGNED NOT NULL";
1390 $query[] = "ALTER TABLE AttributeValue CHANGE COLUMN `attr_id` `attr_id` INT(10) UNSIGNED NOT NULL";
1391 $query[] = "ALTER TABLE AttributeValue ADD PRIMARY KEY (`object_id`, `attr_id`), DROP INDEX `object_id`";
70d6c4fd
DO
1392 $query[] = "ALTER TABLE Dictionary ADD COLUMN `dict_sticky` enum('yes','no') DEFAULT 'no' AFTER `dict_key`";
1393 $query[] = "UPDATE Dictionary SET dict_sticky = 'yes' WHERE dict_key < 50000";
1394 $query[] = "ALTER TABLE Dictionary ADD UNIQUE KEY dict_unique (chapter_id, dict_value, dict_sticky)";
1395 $query[] = "ALTER TABLE Dictionary DROP KEY `chap_to_val`";
b55f913c
DO
1396 $query[] = "UPDATE Config SET varvalue = '0.19.14' WHERE varname = 'DB_VERSION'";
1397 break;
fd8de939 1398 case '0.20.0':
fd8de939
DO
1399 $query[] = "
1400CREATE TABLE `PortLog` (
1401 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
1402 `port_id` int(10) unsigned NOT NULL,
1403 `date` datetime NOT NULL,
1404 `user` varchar(64) NOT NULL,
1405 `message` text NOT NULL,
1406 PRIMARY KEY (`id`),
1407 KEY `port_id-date` (`port_id`,`date`),
1408 CONSTRAINT `PortLog_ibfk_1` FOREIGN KEY (`port_id`) REFERENCES `Port` (`id`) ON DELETE CASCADE
1409) ENGINE=InnoDB;
1410";
1411 $query[] = "
1412CREATE TABLE `IPv4Log` (
1413 `id` int(10) NOT NULL AUTO_INCREMENT,
1414 `ip` int(10) unsigned NOT NULL,
1415 `date` datetime NOT NULL,
1416 `user` varchar(64) NOT NULL,
1417 `message` text NOT NULL,
1418 PRIMARY KEY (`id`),
1419 KEY `ip-date` (`ip`,`date`)
1420) ENGINE=InnoDB;
4318ced5
AA
1421";
1422 $query[] = "
1423CREATE TABLE `IPv6Log` (
1424 `id` int(10) NOT NULL AUTO_INCREMENT,
1425 `ip` binary(16) NOT NULL,
1426 `date` datetime NOT NULL,
1427 `user` varchar(64) NOT NULL,
1428 `message` text NOT NULL,
1429 PRIMARY KEY (`id`),
1430 KEY `ip-date` (`ip`,`date`)
1431) ENGINE=InnoDB;
fd8de939 1432";
e1add254 1433 $query[] = "ALTER TABLE `FileLink` MODIFY COLUMN `entity_type` ENUM('ipv4net','ipv4rspool','ipv4vs','ipv6net','location','object','rack','user') NOT NULL DEFAULT 'object'";
93b7c5d8 1434 $query[] = "ALTER TABLE `TagStorage` MODIFY COLUMN `entity_realm` ENUM('file','ipv4net','ipv4rspool','ipv4vs','ipv6net','location','object','rack','user','vst') NOT NULL default 'object'";
30bb83bd 1435 $query[] = "ALTER TABLE `TagStorage` ADD COLUMN `user` char(64) DEFAULT NULL, ADD COLUMN `date` datetime DEFAULT NULL";
9b8174d7 1436
e1add254 1437 // Rename object tables and keys, 'name' no longer needs to be unique
9b8174d7
AD
1438 $dbxlink->query ('ALTER TABLE `RackObject` RENAME TO `Object`');
1439 $dbxlink->query ('ALTER TABLE `RackObjectHistory` RENAME TO `ObjectHistory`');
e1add254 1440 $dbxlink->query ('ALTER TABLE `Object` DROP KEY `name`');
9b8174d7
AD
1441 $query[] = 'ALTER TABLE `Object` DROP KEY `RackObject_asset_no`';
1442 $query[] = 'ALTER TABLE `Object` ADD UNIQUE KEY `asset_no` (`asset_no`)';
bd7c95ce 1443 $query[] = 'ALTER TABLE `Object` ADD KEY `type_id` (`objtype_id`,`id`)';
9b8174d7
AD
1444 $query[] = 'ALTER TABLE `ObjectHistory` DROP FOREIGN KEY `RackObjectHistory-FK-object_id`';
1445 $query[] = 'ALTER TABLE `ObjectHistory` ADD CONSTRAINT `ObjectHistory-FK-object_id` FOREIGN KEY (`id`) REFERENCES `Object` (`id`) ON DELETE CASCADE';
1446 $query[] = 'ALTER TABLE `RackSpace` DROP FOREIGN KEY `RackSpace-FK-rack_id`';
e1add254 1447
9b8174d7 1448 // Rack height is now an attribute
a14f752c 1449 $query[] = "INSERT INTO `Attribute` (`id`,`type`,`name`) VALUES (27,'uint','Height, units')";
9b8174d7 1450 $query[] = 'INSERT INTO `AttributeMap` (`objtype_id`,`attr_id`,`chapter_id`) VALUES (1560,27,NULL)';
e1add254 1451
42504426
AD
1452 // Racks are now sorted using an attribute
1453 $query[] = "INSERT INTO `Attribute` (`id`,`type`,`name`) VALUES (29,'uint','Sort order')";
1454 $query[] = 'INSERT INTO `AttributeMap` (`objtype_id`,`attr_id`,`chapter_id`) VALUES (1560,29,NULL)';
1455
e1add254
AD
1456 // Relate 'contact person' with locations
1457 $query[] = 'INSERT INTO `AttributeMap` (`objtype_id`,`attr_id`,`chapter_id`) VALUES (1562,14,NULL)';
1458
1459 // Allow relationships between racks/rows/locations
1460 $query[] = "ALTER TABLE `EntityLink` MODIFY COLUMN `parent_entity_type` ENUM('ipv4net','ipv4rspool','ipv4vs','ipv6net','location','object','rack','row','user') NOT NULL";
1461 $query[] = "ALTER TABLE `EntityLink` MODIFY COLUMN `child_entity_type` ENUM('file','location','object','rack','row') NOT NULL";
1462
9b8174d7
AD
1463 // Turn rows into objects
1464 $result = $dbxlink->query ('SELECT * FROM RackRow');
1465 $rows = $result->fetchAll (PDO::FETCH_ASSOC);
1466 unset ($result);
1467 foreach ($rows as $row)
1468 {
e1add254 1469 $prepared = $dbxlink->prepare ('INSERT INTO `Object` (`name`,`objtype_id`) VALUES (?,?)');
9b8174d7
AD
1470 $prepared->execute (array($row['name'], 1561));
1471 $row_id = $dbxlink->lastInsertId();
1472 // Turn all racks in this row into objects
42504426 1473 $result = $dbxlink->query ("SELECT id, name, height, comment FROM Rack WHERE row_id=${row['id']} ORDER BY name");
9b8174d7
AD
1474 $racks = $result->fetchAll (PDO::FETCH_ASSOC);
1475 unset ($result);
42504426 1476 $sort_order = 1;
dec748f6 1477 foreach ($racks as $rack)
9b8174d7 1478 {
42504426 1479 // Add the rack as an object, set the height and sort order as attributes, link the rack to the row,
9374831b 1480 // update rackspace, tags and files to reflect new rack_id, move history
e1add254 1481 $prepared = $dbxlink->prepare ('INSERT INTO `Object` (`name`,`objtype_id`,`comment`) VALUES (?,?,?)');
9b8174d7
AD
1482 $prepared->execute (array($rack['name'], 1560, $rack['comment']));
1483 $rack_id = $dbxlink->lastInsertId();
e1add254 1484 $query[] = "INSERT INTO `AttributeValue` (`object_id`,`object_tid`,`attr_id`,`uint_value`) VALUES (${rack_id},1560,27,${rack['height']})";
42504426 1485 $query[] = "INSERT INTO `AttributeValue` (`object_id`,`object_tid`,`attr_id`,`uint_value`) VALUES (${rack_id},1560,29,${sort_order})";
e1add254 1486 $query[] = "INSERT INTO `EntityLink` (`parent_entity_type`,`parent_entity_id`,`child_entity_type`,`child_entity_id`) VALUES ('row',${row_id},'rack',${rack_id})";
9b8174d7 1487 $query[] = "UPDATE `RackSpace` SET `rack_id`=${rack_id} WHERE `rack_id`=${rack['id']}";
1f02e311 1488 $query[] = "UPDATE `Atom` SET `rack_id`=${rack_id} WHERE `rack_id`=${rack['id']}";
c718f1e2
AA
1489 $query[] = "UPDATE `TagStorage` SET `entity_id`=${rack_id} WHERE `entity_realm`='rack' AND `entity_id`=${rack['id']}";
1490 $query[] = "UPDATE `FileLink` SET `entity_id`=${rack_id} WHERE `entity_type`='rack' AND `entity_id`=${rack['id']}";
9b8174d7 1491 $query[] = "INSERT INTO `ObjectHistory` (`id`,`name`,`objtype_id`,`comment`,`ctime`,`user_name`) SELECT ${rack_id},`name`,1560,`comment`,`ctime`,`user_name` FROM `RackHistory` WHERE `id`=${rack['id']}";
42504426 1492 $sort_order++;
9b8174d7
AD
1493 }
1494 }
9374831b 1495 $query[] = 'ALTER TABLE `RackSpace` ADD CONSTRAINT `RackSpace-FK-rack_id` FOREIGN KEY (`rack_id`) REFERENCES `Object` (`id`)';
9b8174d7
AD
1496 $query[] = 'DROP TABLE `Rack`';
1497 $query[] = 'DROP TABLE `RackRow`';
1498 $query[] = 'DROP TABLE `RackHistory`';
1499 $query[] = "
1500CREATE TABLE `RackThumbnail` (
1501 `rack_id` int(10) unsigned NOT NULL,
1502 `thumb_data` blob,
1503 UNIQUE KEY `rack_id` (`rack_id`),
1504 CONSTRAINT `RackThumbnail-FK-rack_id` FOREIGN KEY (`rack_id`) REFERENCES `Object` (`id`) ON DELETE CASCADE
1505) ENGINE=InnoDB
1506";
1507 $query[] = "
69bd4a37
AA
1508CREATE VIEW `Location` AS SELECT O.id, O.name, O.has_problems, O.comment, P.id AS parent_id, P.name AS parent_name
1509FROM `Object` O
1510LEFT JOIN (
1511 `Object` P INNER JOIN `EntityLink` EL
1512 ON EL.parent_entity_id = P.id AND P.objtype_id = 1562 AND EL.parent_entity_type = 'location' AND EL.child_entity_type = 'location'
1513) ON EL.child_entity_id = O.id
1514WHERE O.objtype_id = 1562
e1add254
AD
1515";
1516 $query[] = "
1517CREATE VIEW `Row` AS SELECT O.id, O.name, L.id AS location_id, L.name AS location_name
1518 FROM `Object` O
1519 LEFT JOIN `EntityLink` EL ON O.id = EL.child_entity_id AND EL.parent_entity_type = 'location' AND EL.child_entity_type = 'row'
1520 LEFT JOIN `Object` L ON EL.parent_entity_id = L.id AND L.objtype_id = 1562
1521 WHERE O.objtype_id = 1561
9b8174d7
AD
1522";
1523 $query[] = "
9c55b126 1524CREATE VIEW `Rack` AS SELECT O.id, O.name AS name, O.asset_no, O.has_problems, O.comment,
42504426
AD
1525 AV_H.uint_value AS height,
1526 AV_S.uint_value AS sort_order,
1f02e311 1527 RT.thumb_data,
e1add254
AD
1528 R.id AS row_id,
1529 R.name AS row_name
1f02e311 1530 FROM `Object` O
42504426
AD
1531 LEFT JOIN `AttributeValue` AV_H ON O.id = AV_H.object_id AND AV_H.attr_id = 27
1532 LEFT JOIN `AttributeValue` AV_S ON O.id = AV_S.object_id AND AV_S.attr_id = 29
1f02e311 1533 LEFT JOIN `RackThumbnail` RT ON O.id = RT.rack_id
e1add254
AD
1534 LEFT JOIN `EntityLink` EL ON O.id = EL.child_entity_id AND EL.parent_entity_type = 'row' AND EL.child_entity_type = 'rack'
1535 INNER JOIN `Object` R ON R.id = EL.parent_entity_id
1536 WHERE O.objtype_id = 1560
9b8174d7
AD
1537";
1538 $query[] = "
d7e9e25b 1539CREATE VIEW `RackObject` AS SELECT id, name, label, objtype_id, asset_no, has_problems, comment FROM `Object`
9b8174d7
AD
1540 WHERE `objtype_id` NOT IN (1560, 1561, 1562)
1541";
1542 $query[] = "UPDATE `Chapter` SET `name` = 'ObjectType' WHERE `id` = 1";
1543 $query[] = "DELETE FROM RackSpace WHERE object_id IS NULL AND state = 'T'";
2265be00 1544
f1cdc9f1 1545 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('SYNC_802Q_LISTSRC','','string','yes','no','no','List of VLAN switches sync is enabled on')";
76a7ec36 1546 $query[] = "UPDATE `Config` SET is_userdefined='yes' WHERE varname='PROXIMITY_RANGE'";
9c64ccf8 1547 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('QUICK_LINK_PAGES','depot,ipv4space,rackspace','string','yes','no','yes','List of pages to dislay in quick links')";
6213dc9f 1548 $query[] = "ALTER TABLE `IPv4LB` MODIFY `prio` varchar(255) DEFAULT NULL";
71066ef1 1549
cc2fa820
AD
1550 $query[] = "ALTER TABLE `IPv4Address` ADD COLUMN `comment` char(255) NOT NULL default '' AFTER `name`";
1551 $query[] = "ALTER TABLE `IPv6Address` ADD COLUMN `comment` char(255) NOT NULL default '' AFTER `name`";
1552
71066ef1
AA
1553 // change IP address format of IPv4VS and IPv4RS tables
1554 convertSLBTablesToBinIPs();
1555
581e3b64
AA
1556 // do not allow NULL allocation type
1557 $query[] = "ALTER TABLE `IPv4Allocation` MODIFY `type` enum('regular','shared','virtual','router') NOT NULL DEFAULT 'regular'";
1558 $query[] = "ALTER TABLE `IPv6Allocation` MODIFY `type` enum('regular','shared','virtual','router') NOT NULL DEFAULT 'regular'";
1559
2481e17e
AA
1560 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('SEARCH_DOMAINS','','string','yes','no','yes','DNS domain list (comma-separated) to search in FQDN attributes')";
1561
9c64ccf8
AA
1562 // update some config variables which changed their defaults in this verison
1563 replaceConfigVarValue ('SHOW_LAST_TAB', 'yes');
1564 replaceConfigVarValue ('IPV4_TREE_SHOW_USAGE','no');
1565 replaceConfigVarValue ('IPV4LB_LISTSRC', 'false', '{$typeid_4}');
1566 replaceConfigVarValue ('FILTER_DEFAULT_ANDOR', 'and');
1567 replaceConfigVarValue ('FILTER_SUGGEST_EXTRA', 'yes');
1568 replaceConfigVarValue ('IPV4_TREE_RTR_AS_CELL', 'no');
1569 replaceConfigVarValue ('SSH_OBJS_LISTSRC', 'false', 'none');
1570 replaceConfigVarValue ('TELNET_OBJS_LISTSRC', 'false', 'none');
1571
85e868a0
AA
1572 $query[] = "UPDATE Config SET varvalue = '0.20.0' WHERE varname = 'DB_VERSION'";
1573 break;
e9893a88 1574 case '0.20.1':
4e221a3d
AD
1575 // some HW types were moved from the 'Network switch' chapter to the 'Network chassis' chapter
1576 // change the type of affected objects to 'Network chassis'
1577 $query[] = "UPDATE `Object` SET objtype_id = 1503 WHERE id IN (SELECT object_id FROM `AttributeValue` WHERE attr_id = 2 and uint_value IN (888,889,890,891))";
1578
e9893a88
AD
1579 // new 'management interface' object type
1580 $query[] = "INSERT INTO `Chapter` (`id`,`sticky`,`name`) VALUES (38,'no','management interface type')";
1581 $query[] = "INSERT INTO `Attribute` (`id`,`type`,`name`) VALUES (30,'dict','Mgmt type')";
1582 $query[] = "INSERT INTO `AttributeMap` (`objtype_id`,`attr_id`,`chapter_id`) VALUES (1787,3,NULL),(1787,14,NULL),(1787,30,38)";
1583 $query[] = "UPDATE `Config` SET varvalue = CONCAT(varvalue, ' or {\$typeid_1787}') WHERE varname = 'IPV4OBJ_LISTSRC'";
1584
2198b21a
DO
1585 $query[] = "INSERT INTO Config VALUES ('8021Q_EXTSYNC_LISTSRC','false','string','yes','no','no','List source: objects with extended 802.1Q sync')";
1586
6e5556bc
AD
1587 // constraints to prevent orphan records
1588 $query[] = "DELETE A FROM `Atom` A LEFT JOIN `Molecule` M ON A.molecule_id = M.id LEFT JOIN `Object` O ON A.rack_id = O.id WHERE M.id IS NULL OR O.id IS NULL";
1589 $query[] = "DELETE FROM `MountOperation` WHERE old_molecule_id NOT IN (SELECT id FROM `Molecule`) OR new_molecule_id NOT IN (SELECT id FROM `Molecule`)";
1590 $query[] = "ALTER TABLE `Atom` ADD CONSTRAINT `Atom-FK-molecule_id` FOREIGN KEY (`molecule_id`) REFERENCES `Molecule` (`id`) ON DELETE CASCADE";
1591 $query[] = "ALTER TABLE `Atom` ADD CONSTRAINT `Atom-FK-rack_id` FOREIGN KEY (`rack_id`) REFERENCES `Object` (`id`) ON DELETE CASCADE";
1592 $query[] = "ALTER TABLE `MountOperation` ADD CONSTRAINT `MountOperation-FK-old_molecule_id` FOREIGN KEY (`old_molecule_id`) REFERENCES `Molecule` (`id`) ON DELETE CASCADE";
1593 $query[] = "ALTER TABLE `MountOperation` ADD CONSTRAINT `MountOperation-FK-new_molecule_id` FOREIGN KEY (`new_molecule_id`) REFERENCES `Molecule` (`id`) ON DELETE CASCADE";
14a9812c
DO
1594 # multiple Cacti servers
1595 $query[] = "
1596CREATE TABLE `CactiServer` (
1597 `id` int(10) unsigned NOT NULL auto_increment,
1598 `base_url` char(255) DEFAULT NULL,
1599 `username` char(64) DEFAULT NULL,
1600 `password` char(64) DEFAULT NULL,
1601 PRIMARY KEY (`id`)
1602) ENGINE=InnoDB
1603";
1604 $query[] = "ALTER TABLE CactiGraph ADD COLUMN server_id int(10) unsigned NOT NULL AFTER object_id";
7b9f0379 1605
14a9812c 1606 $result = $dbxlink->query ('SELECT COUNT(*) AS cnt FROM CactiGraph');
a1c4ba2f 1607 $row = $result->fetch (PDO::FETCH_ASSOC);
14a9812c 1608 unset ($result);
7b9f0379
AA
1609
1610 $result = $dbxlink->query ("SELECT varvalue FROM Config WHERE varname = 'CACTI_URL'");
1611 $cacti_url_row = $result->fetch (PDO::FETCH_ASSOC);
1612 unset ($result);
1613
1614 if ($row['cnt'] != 0 || is_array ($cacti_url_row) && strlen ($cacti_url_row['varvalue']))
14a9812c
DO
1615 {
1616 $query[] = "INSERT INTO CactiServer (id) VALUES (1)";
1617 $query[] = "UPDATE CactiServer SET base_url = (SELECT varvalue FROM Config WHERE varname = 'CACTI_URL') WHERE id = 1";
1618 $query[] = "UPDATE CactiServer SET username = (SELECT varvalue FROM Config WHERE varname = 'CACTI_USERNAME') WHERE id = 1";
1619 $query[] = "UPDATE CactiServer SET password = (SELECT varvalue FROM Config WHERE varname = 'CACTI_USERPASS') WHERE id = 1";
1620 $query[] = "UPDATE CactiGraph SET server_id = 1";
1621 }
1622 $query[] = "ALTER TABLE CactiGraph DROP PRIMARY KEY";
1623 $query[] = "ALTER TABLE CactiGraph ADD PRIMARY KEY (server_id, graph_id)";
1624 $query[] = "ALTER TABLE CactiGraph ADD KEY (graph_id)";
1625 $query[] = "ALTER TABLE CactiGraph ADD CONSTRAINT `CactiGraph-FK-server_id` FOREIGN KEY (server_id) REFERENCES CactiServer (id)";
1626 $query[] = "DELETE FROM Config WHERE varname IN('CACTI_URL', 'CACTI_USERNAME', 'CACTI_USERPASS')";
e9893a88
AD
1627 $query[] = "UPDATE Config SET varvalue = '0.20.1' WHERE varname = 'DB_VERSION'";
1628 break;
6e58c2c4
DO
1629 case '0.20.2':
1630 $query[] = "ALTER TABLE TagStorage ADD COLUMN tag_is_assignable ENUM('yes', 'no') NOT NULL default 'yes' AFTER tag_id";
1631 $query[] = "ALTER TABLE TagStorage ADD KEY `tag_id-tag_is_assignable` (tag_id, tag_is_assignable)";
1632 $query[] = "ALTER TABLE TagTree ADD COLUMN is_assignable ENUM('yes', 'no') NOT NULL default 'yes' AFTER parent_id";
1633 $query[] = "ALTER TABLE TagTree ADD KEY `id-is_assignable` (id, is_assignable)";
1634 $query[] = "ALTER TABLE TagStorage DROP FOREIGN KEY `TagStorage-FK-tag_id`";
1635 $query[] = "ALTER TABLE TagStorage ADD CONSTRAINT `TagStorage-FK-TagTree` FOREIGN KEY (tag_id, tag_is_assignable) REFERENCES TagTree (id, is_assignable)";
2818e5d9 1636 $query[] = "UPDATE UserAccount SET user_realname = NULL WHERE user_realname = ''";
cc7f0b09 1637 $query[] = "UPDATE Object SET comment = NULL WHERE comment = ''";
2c691f71
MH
1638 $query[] = "
1639CREATE TABLE `MuninServer` (
1640 `id` int(10) unsigned NOT NULL auto_increment,
1641 `base_url` char(255) DEFAULT NULL,
1642 PRIMARY KEY (`id`)
48ecb468 1643) ENGINE=InnoDB
2c691f71
MH
1644";
1645 $query[] = "
1646CREATE TABLE `MuninGraph` (
1647 `object_id` int(10) unsigned NOT NULL,
1648 `server_id` int(10) unsigned NOT NULL,
1649 `graph` char(255) NOT NULL,
1650 `caption` char(255) DEFAULT NULL,
1651 PRIMARY KEY (`object_id`,`server_id`,`graph`),
1652 KEY `server_id` (`server_id`),
1653 KEY `graph` (`graph`),
1654 CONSTRAINT `MuninGraph-FK-server_id` FOREIGN KEY (`server_id`) REFERENCES `MuninServer` (`id`),
1655 CONSTRAINT `MuninGraph-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `Object` (`id`) ON DELETE CASCADE
48ecb468 1656) ENGINE=InnoDB
2c691f71
MH
1657";
1658 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('MUNIN_LISTSRC','false','string','yes','no','no','List of object with Munin graphs')";
11e3af31
DO
1659 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('8021Q_MULTILINK_LISTSRC','false','string','yes','no','no','List source: IPv4/IPv6 networks allowing multiple VLANs from same domain')";
1660 $query[] = "ALTER TABLE VLANIPv4 ADD UNIQUE `network-domain-vlan` (ipv4net_id, domain_id, vlan_id)";
1661 $query[] = "ALTER TABLE VLANIPv4 DROP KEY `network-domain`";
1662 $query[] = "ALTER TABLE VLANIPv6 ADD UNIQUE `network-domain-vlan` (ipv6net_id, domain_id, vlan_id)";
1663 $query[] = "ALTER TABLE VLANIPv6 DROP KEY `network-domain`";
48ecb468 1664 $query[] = "UPDATE Config SET varvalue = '0.20.2' WHERE varname = 'DB_VERSION'";
6e58c2c4 1665 break;
56a28368
AA
1666 case '0.20.3':
1667 $query[] = "UPDATE Config SET varvalue = '0.20.3' WHERE varname = 'DB_VERSION'";
1668 break;
4dcd770e 1669 case '0.20.4':
6ccfd4bd 1670 $query[] = "ALTER TABLE `FileLink` MODIFY COLUMN `entity_type` ENUM('ipv4net','ipv4rspool','ipv4vs','ipv6net','location','object','rack','row','user') NOT NULL DEFAULT 'object'";
c495997f 1671 $query[] = "ALTER TABLE `RackSpace` MODIFY COLUMN `state` ENUM('A','U','T') NOT NULL default 'A'";
4dcd770e 1672 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('REVERSED_RACKS_LISTSRC', 'false', 'string', 'yes', 'no', 'no', 'List of racks with reversed (top to bottom) units order')";
c13ecb48 1673 $query[] = "UPDATE `Config` SET varvalue = CONCAT(varvalue, ' or {\$typeid_965}') WHERE varname = 'IPV4OBJ_LISTSRC'";
ef550f27
AA
1674 $query[] = "UPDATE AttributeValue INNER JOIN AttributeMap USING (attr_id) SET AttributeValue.uint_value = 1572 WHERE chapter_id = 12 AND uint_value = 162";
1675 $query[] = "UPDATE AttributeValue INNER JOIN AttributeMap USING (attr_id) SET AttributeValue.uint_value = 1710 WHERE chapter_id = 12 AND uint_value = 163";
809d2ba9 1676 $query[] = "UPDATE Config SET varvalue = '%Y-%m-%d', description='PHP strftime() format to use for date output' WHERE varname = 'DATETIME_FORMAT'";
6b1ca530 1677 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('NEAREST_RACKS_CHECKBOX', 'yes', 'string', 'yes', 'no', 'yes', 'Enable nearest racks in port list filter by default')";
4dcd770e
AA
1678 $query[] = "UPDATE Config SET varvalue = '0.20.4' WHERE varname = 'DB_VERSION'";
1679 break;
de99667b 1680 case '0.20.5':
d016010a
AD
1681 $query[] = "
1682CREATE OR REPLACE VIEW `Rack` AS SELECT O.id, O.name AS name, O.asset_no, O.has_problems, O.comment,
1683 AV_H.uint_value AS height,
1684 AV_S.uint_value AS sort_order,
1685 RT.thumb_data,
1686 R.id AS row_id,
1687 R.name AS row_name,
1688 L.id AS location_id,
1689 L.name AS location_name
1690 FROM `Object` O
1691 LEFT JOIN `AttributeValue` AV_H ON O.id = AV_H.object_id AND AV_H.attr_id = 27
1692 LEFT JOIN `AttributeValue` AV_S ON O.id = AV_S.object_id AND AV_S.attr_id = 29
1693 LEFT JOIN `RackThumbnail` RT ON O.id = RT.rack_id
1694 LEFT JOIN `EntityLink` RL ON O.id = RL.child_entity_id AND RL.parent_entity_type = 'row' AND RL.child_entity_type = 'rack'
1695 INNER JOIN `Object` R ON R.id = RL.parent_entity_id
1696 LEFT JOIN `EntityLink` LL ON R.id = LL.child_entity_id AND LL.parent_entity_type = 'location' AND LL.child_entity_type = 'row'
1697 LEFT JOIN `Object` L ON L.id = LL.parent_entity_id
1698 WHERE O.objtype_id = 1560
1699";
1700
f9fcce59
AD
1701 // prevent some AttributeMap entries from being deleted
1702 $query[] = "ALTER TABLE AttributeMap ADD COLUMN sticky enum('yes','no') default 'no'";
1703 $query[] = "UPDATE AttributeMap SET sticky = 'yes' WHERE objtype_id = 4 AND attr_id IN (26,28)"; // Server -> Hypervisor, Slot number
1704 $query[] = "UPDATE AttributeMap SET sticky = 'yes' WHERE objtype_id = 8 AND attr_id IN (1,2,4,28)"; // Network switch -> OEM S/N 1, HW type, SW type, Slot number
1705 $query[] = "UPDATE AttributeMap SET sticky = 'yes' WHERE objtype_id = 798 AND attr_id = 28"; // Network security -> Slot number
1706 $query[] = "UPDATE AttributeMap SET sticky = 'yes' WHERE objtype_id = 1055 AND attr_id = 28"; // FC switch -> Slot number
1707 $query[] = "UPDATE AttributeMap SET sticky = 'yes' WHERE objtype_id = 1560 AND attr_id IN (27,29)"; // Rack -> Height, Sort order
1708 $query[] = "UPDATE AttributeMap SET sticky = 'yes' WHERE objtype_id = 1787 AND attr_id = 30"; // Management interface -> Mgmt type
1709
de99667b 1710 $query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('RDP_OBJS_LISTSRC','false','string','yes','no','yes','Rackcode filter for RDP-managed objects')";
eacc0983
AA
1711
1712 // SLB v2 tables
1713 $query[] = "
1714CREATE TABLE `VS` (
1715 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
1716 `name` char(255) DEFAULT NULL,
1717 `vsconfig` text,
1718 `rsconfig` text,
1719 PRIMARY KEY (`id`)
1720) ENGINE=InnoDB
1721";
1722 $query[] = "
1723CREATE TABLE `VSIPs` (
1724 `vs_id` int(10) unsigned NOT NULL,
1725 `vip` varbinary(16) NOT NULL,
1726 `vsconfig` text,
1727 `rsconfig` text,
1728 PRIMARY KEY (`vs_id`,`vip`),
1729 KEY `vip` (`vip`),
1730 CONSTRAINT `VSIPs-vs_id` FOREIGN KEY (`vs_id`) REFERENCES `VS` (`id`) ON DELETE CASCADE
1731) ENGINE=InnoDB
1732";
1733 $query[] = "
1734CREATE TABLE `VSPorts` (
1735 `vs_id` int(10) unsigned NOT NULL,
66844484 1736 `proto` enum('TCP','UDP','MARK') NOT NULL,
eacc0983
AA
1737 `vport` int(10) unsigned NOT NULL,
1738 `vsconfig` text,
1739 `rsconfig` text,
1740 PRIMARY KEY (`vs_id`,`proto`,`vport`),
66844484 1741 KEY `proto-vport` (`proto`,`vport`),
eacc0983
AA
1742 CONSTRAINT `VS-vs_id` FOREIGN KEY (`vs_id`) REFERENCES `VS` (`id`) ON DELETE CASCADE
1743) ENGINE=InnoDB
1744";
1745 $query[] = "
1746CREATE TABLE `VSEnabledIPs` (
1747 `object_id` int(10) unsigned NOT NULL,
1748 `vs_id` int(10) unsigned NOT NULL,
1749 `vip` varbinary(16) NOT NULL,
1750 `rspool_id` int(10) unsigned NOT NULL,
1751 `prio` varchar(255) DEFAULT NULL,
1752 `vsconfig` text,
1753 `rsconfig` text,
66844484 1754 PRIMARY KEY (`object_id`,`vs_id`,`vip`,`rspool_id`),
eacc0983
AA
1755 KEY `vip` (`vip`),
1756 KEY `VSEnabledIPs-FK-vs_id-vip` (`vs_id`,`vip`),
1757 KEY `VSEnabledIPs-FK-rspool_id` (`rspool_id`),
1758 CONSTRAINT `VSEnabledIPs-FK-rspool_id` FOREIGN KEY (`rspool_id`) REFERENCES `IPv4RSPool` (`id`) ON DELETE CASCADE,
1759 CONSTRAINT `VSEnabledIPs-FK-vs_id-vip` FOREIGN KEY (`vs_id`, `vip`) REFERENCES `VSIPs` (`vs_id`, `vip`) ON DELETE CASCADE
1760) ENGINE=InnoDB
1761";
1762 $query[] = "
1763CREATE TABLE `VSEnabledPorts` (
1764 `object_id` int(10) unsigned NOT NULL,
1765 `vs_id` int(10) unsigned NOT NULL,
66844484 1766 `proto` enum('TCP','UDP','MARK') NOT NULL,
eacc0983
AA
1767 `vport` int(10) unsigned NOT NULL,
1768 `rspool_id` int(10) unsigned NOT NULL,
1769 `vsconfig` text,
1770 `rsconfig` text,
66844484 1771 PRIMARY KEY (`object_id`,`vs_id`,`proto`,`vport`,`rspool_id`),
eacc0983
AA
1772 KEY `VSEnabledPorts-FK-vs_id-proto-vport` (`vs_id`,`proto`,`vport`),
1773 KEY `VSEnabledPorts-FK-rspool_id` (`rspool_id`),
1774 CONSTRAINT `VSEnabledPorts-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `Object` (`id`) ON DELETE CASCADE,
1775 CONSTRAINT `VSEnabledPorts-FK-rspool_id` FOREIGN KEY (`rspool_id`) REFERENCES `IPv4RSPool` (`id`) ON DELETE CASCADE,
1776 CONSTRAINT `VSEnabledPorts-FK-vs_id-proto-vport` FOREIGN KEY (`vs_id`, `proto`, `vport`) REFERENCES `VSPorts` (`vs_id`, `proto`, `vport`) ON DELETE CASCADE
1777) ENGINE=InnoDB
1778";
4b5da861
AA
1779 $query[] = "ALTER TABLE `EntityLink` MODIFY COLUMN `parent_entity_type` ENUM('ipv4net','ipv4rspool','ipv4vs','ipv6net','location','object','rack','row','user') NOT NULL";
1780 $query[] = "ALTER TABLE `FileLink` MODIFY COLUMN `entity_type` ENUM('ipv4net','ipv4rspool','ipv4vs','ipv6net','location','object','rack','row','user') NOT NULL DEFAULT 'object'";
1781 $query[] = "ALTER TABLE `TagStorage` MODIFY COLUMN `entity_realm` ENUM('file','ipv4net','ipv4rspool','ipv4vs','ipvs','ipv6net','location','object','rack','user','vst') NOT NULL DEFAULT 'object'";
55ec6226 1782 $query[] = "ALTER TABLE `UserConfig` DROP FOREIGN KEY `UserConfig-FK-user`";
f9fcce59 1783 $query[] = "UPDATE Config SET varvalue = '0.20.5' WHERE varname = 'DB_VERSION'";
de99667b 1784 break;
6a40d076 1785 case '0.20.6':
a7390942
AD
1786 if (!isInnoDBSupported ())
1787 {
1788 showUpgradeError ("Cannot upgrade because triggers are not supported by your MySQL server.", __FUNCTION__);
1789 die;
1790 }
1791 // allow one-to-many port links
1792 $query[] = "ALTER TABLE `Link` DROP FOREIGN KEY `Link-FK-a`, DROP FOREIGN KEY `Link-FK-b`";
1793 $query[] = "ALTER TABLE `Link` DROP PRIMARY KEY, DROP KEY `porta`, DROP KEY `portb`";
1794 $query[] = "ALTER TABLE `Link` ADD UNIQUE KEY `porta-portb-unique` (`porta`,`portb`), ADD KEY `porta` (`porta`), ADD KEY `portb` (`portb`)";
1795 $query[] = "ALTER TABLE `Link` ADD CONSTRAINT `Link-FK-a` FOREIGN KEY (`porta`) REFERENCES `Port` (`id`) ON DELETE CASCADE, ADD CONSTRAINT `Link-FK-b` FOREIGN KEY (`portb`) REFERENCES `Port` (`id`) ON DELETE CASCADE";
1796 $query[] = "ALTER TABLE `Link` ADD COLUMN `id` int(10) unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT FIRST";
1797 $query[] = "
1798CREATE TRIGGER `checkLinkBeforeInsert` BEFORE INSERT ON `Link`
1799 FOR EACH ROW
1800BEGIN
1801 DECLARE tmp, porta_type, portb_type, count INTEGER;
1802 IF NEW.porta = NEW.portb THEN
1803 SET NEW.porta = NULL;
1804 ELSEIF NEW.porta > NEW.portb THEN
1805 SET tmp = NEW.porta;
1806 SET NEW.porta = NEW.portb;
1807 SET NEW.portb = tmp;
1808 END IF;
1809 SELECT type INTO porta_type FROM Port WHERE id = NEW.porta;
1810 SELECT type INTO portb_type FROM Port WHERE id = NEW.portb;
1811 SELECT COUNT(*) INTO count FROM PortCompat WHERE (type1 = porta_type AND type2 = portb_type) OR (type1 = portb_type AND type2 = porta_type);
1812 IF count = 0 THEN
1813 SET NEW.porta = NULL;
1814 END IF;
1815END;
1816";
1817 $query[] = "
1818CREATE TRIGGER `checkLinkBeforeUpdate` BEFORE UPDATE ON `Link`
1819 FOR EACH ROW
1820BEGIN
1821 DECLARE tmp, porta_type, portb_type, count INTEGER;
1822 IF NEW.porta = NEW.portb THEN
1823 SET NEW.porta = NULL;
1824 ELSEIF NEW.porta > NEW.portb THEN
1825 SET tmp = NEW.porta;
1826 SET NEW.porta = NEW.portb;
1827 SET NEW.portb = tmp;
1828 END IF;
1829 SELECT type INTO porta_type FROM Port WHERE id = NEW.porta;
1830 SELECT type INTO portb_type FROM Port WHERE id = NEW.portb;
1831 SELECT COUNT(*) INTO count FROM PortCompat WHERE (type1 = porta_type AND type2 = portb_type) OR (type1 = portb_type AND type2 = porta_type);
1832 IF count = 0 THEN
1833 SET NEW.porta = NULL;
1834 END IF;
1835END;
1836";
1837 $query[] = "
1838CREATE TRIGGER `checkPortCompatBeforeDelete` BEFORE DELETE ON `PortCompat`
1839 FOR EACH ROW
1840BEGIN
1841 DECLARE count INTEGER;
1842 SELECT COUNT(*) INTO count FROM Link LEFT JOIN Port AS PortA ON Link.porta = PortA.id LEFT JOIN Port AS PortB ON Link.portb = PortB.id WHERE (PortA.type = OLD.type1 AND PortB.type = OLD.type2) OR (PortA.type = OLD.type2 AND PortB.type = OLD.type1);
1843 IF count > 0 THEN
1844 UPDATE `Cannot delete: rule still used` SET x = 1;
1845 END IF;
1846END;
1847";
1848 $query[] = "
1849CREATE TRIGGER `checkPortCompatBeforeUpdate` BEFORE UPDATE ON `PortCompat`
1850 FOR EACH ROW
1851BEGIN
1852 DECLARE count INTEGER;
1853 SELECT COUNT(*) INTO count FROM Link LEFT JOIN Port AS PortA ON Link.porta = PortA.id LEFT JOIN Port AS PortB ON Link.portb = PortB.id WHERE (PortA.type = OLD.type1 AND PortB.type = OLD.type2) OR (PortA.type = OLD.type2 AND PortB.type = OLD.type1);
1854 IF count > 0 THEN
1855 UPDATE `Cannot update: rule still used` SET x = 1;
1856 END IF;
1857END;
1858";
18ff3d26
AD
1859 // one HW type was moved from the 'Network switch' chapter to the 'Network chassis' chapter
1860 // change the type of affected objects to 'Network chassis'
1861 $query[] = "UPDATE `Object` SET objtype_id = 1503 WHERE id IN (SELECT object_id FROM `AttributeValue` WHERE attr_id = 2 and uint_value = 935)";
6a40d076 1862 $query[] = "UPDATE Config SET varvalue = '0.20.6' WHERE varname = 'DB_VERSION'";
a7390942 1863 break;
d15dae2f
DO
1864 case 'dictionary':
1865 $query = reloadDictionary();
1866 break;
fbbb74fb 1867 default:
87ae30c5
AD
1868 return NULL;
1869 }
1870 return $query;
1871}
1872
1873function executeUpgradeBatch ($batchid)
1874{
1875 global $dbxlink;
1876 $query = getUpgradeBatch($batchid);
a20a4e3c
DO
1877 if ($query === NULL)
1878 {
87ae30c5
AD
1879 showError ("unknown batch '${batchid}'", __FUNCTION__);
1880 die;
fbbb74fb 1881 }
fbbb74fb 1882 $failures = array();
4114697d 1883 echo "<tr><th>Executing batch '${batchid}'</th><td>";
fbbb74fb
DO
1884 foreach ($query as $q)
1885 {
babe4bf5
AA
1886 try
1887 {
1888 $result = $dbxlink->query ($q);
1889 }
1890 catch (PDOException $e)
758fe24c 1891 {
758fe24c
DO
1892 $errorInfo = $dbxlink->errorInfo();
1893 $failures[] = array ($q, $errorInfo[2]);
1894 }
fbbb74fb 1895 }
fbbb74fb 1896 if (!count ($failures))
4114697d 1897 echo "<strong><font color=green>done</font></strong>";
fbbb74fb
DO
1898 else
1899 {
4114697d 1900 echo "<strong><font color=red>The following queries failed:</font></strong><br><pre>";
fbbb74fb
DO
1901 foreach ($failures as $f)
1902 {
1903 list ($q, $i) = $f;
4114697d 1904 echo "${q} -- ${i}\n";
fbbb74fb 1905 }
4114697d 1906 echo "</pre>";
fbbb74fb 1907 }
4114697d 1908 echo '</td></tr>';
fbbb74fb
DO
1909}
1910
120e9ddd 1911function authenticate_admin ($username, $password)
a1f3710a 1912{
43c7895d 1913 global $dbxlink;
4dd08c61
DO
1914 $prepared = $dbxlink->prepare ('SELECT COUNT(*) FROM UserAccount WHERE user_id=1 AND user_name=? AND user_password_hash=?');
1915 if (!$prepared->execute (array ($username, sha1 ($password))))
a1f3710a 1916 die ('SQL query failed in ' . __FUNCTION__);
4dd08c61 1917 $rows = $prepared->fetchAll (PDO::FETCH_NUM);
43c7895d 1918 return $rows[0][0] == 1;
a1f3710a
DO
1919}
1920
c4ee2310
DO
1921// Database version detector. Should behave corretly on any
1922// working dataset a user might have.
1923function getDatabaseVersion ()
1924{
2f5e4db9
DO
1925 global $dbxlink;
1926 $prepared = $dbxlink->prepare ('SELECT varvalue FROM Config WHERE varname = "DB_VERSION" and vartype = "string"');
1927 if (! $prepared->execute())
c4ee2310 1928 {
c4ee2310 1929 $errorInfo = $dbxlink->errorInfo();
2f5e4db9 1930 die (__FUNCTION__ . ': SQL query failed with error ' . $errorInfo[2]);
c4ee2310 1931 }
2f5e4db9 1932 $rows = $prepared->fetchAll (PDO::FETCH_NUM);
c4ee2310
DO
1933 unset ($result);
1934 if (count ($rows) != 1 || !strlen ($rows[0][0]))
1935 die (__FUNCTION__ . ': Cannot guess database version. Config table is present, but DB_VERSION is missing or invalid. Giving up.');
1936 $ret = $rows[0][0];
1937 return $ret;
1938}
1939
71066ef1 1940function showUpgradeError ($info = '', $location = 'N/A')
c4ee2310
DO
1941{
1942 if (preg_match ('/\.php$/', $location))
1943 $location = basename ($location);
1944 elseif ($location != 'N/A')
1945 $location = $location . '()';
1946 echo "<div class=msg_error>An error has occured in [${location}]. ";
1947 if (!strlen ($info))
1948 echo 'No additional information is available.';
1949 else
1950 echo "Additional information:<br><p>\n<pre>\n${info}\n</pre></p>";
2f5e4db9 1951 echo "Go back or try starting from <a href='index.php'>index page</a>.<br></div>\n";
c4ee2310
DO
1952}
1953
9c64ccf8
AA
1954// changes the value of config variable. If $old_value_filter is set, value is changed only if current value equals to it.
1955function replaceConfigVarValue ($varname, $new_value, $old_value_filter = NULL)
1956{
1957 global $dbxlink;
1958 if (isset ($old_value_filter))
1959 {
1960 $result = $dbxlink->prepare ("SELECT varvalue FROM Config WHERE varname = ?");
1961 $result->execute (array ($varname));
1962 if ($row = $result->fetch (PDO::FETCH_ASSOC))
1963 if ($row['varvalue'] != $old_value_filter)
1964 return;
1965 unset ($result);
1966 }
1967 $result = $dbxlink->prepare ("UPDATE Config set varvalue = ? WHERE varname = ?");
1968 $result->execute (array ($new_value, $varname));
1969}
1970
964b0388 1971function renderUpgraderHTML()
99ee5479 1972{
b00cc78c
AA
1973 global $found_secret_file;
1974 if (! $found_secret_file)
1975 die ('<center>There is no working RackTables instance here, <a href="?module=installer">install</a>?</center>');
1976
1977 try
964b0388 1978 {
b00cc78c
AA
1979 connectDB();
1980 }
1981 catch (RackTablesError $e)
1982 {
1983 die ("Database connection failed:\n\n" . $e->getMessage());
1984 }
dec748f6 1985
b00cc78c
AA
1986 if
1987 (
1988 !isset ($_SERVER['PHP_AUTH_USER']) or
1989 !strlen ($_SERVER['PHP_AUTH_USER']) or
1990 !isset ($_SERVER['PHP_AUTH_PW']) or
1991 !strlen ($_SERVER['PHP_AUTH_PW']) or
1992 !authenticate_admin ($_SERVER['PHP_AUTH_USER'], $_SERVER['PHP_AUTH_PW'])
1993 )
1994 {
1995 header ('WWW-Authenticate: Basic realm="RackTables upgrade"');
1996 header ('HTTP/1.0 401 Unauthorized');
a931fc26
DO
1997?>
1998<h1>Trouble logging in?</h1>
f2b6ae86 1999You are trying to authenticate for the RackTables upgrade screen. This means that
a931fc26
DO
2000you must authenticate with the username and password of the main RackTables
2001administrator. There is only one such account in each installation, its default
2002username is "admin". RackTables wiki provides more information on this topic.
2003<?php
120e9ddd 2004 die;
964b0388 2005 }
fbbb74fb 2006
964b0388 2007?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
5f016d39 2008<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
2cf36003 2009<head><title>RackTables upgrade script</title>
964b0388
DO
2010<style type="text/css">
2011.tdleft {
2012 text-align: left;
2013}
2014
2015.trok {
2016 background-color: #80FF80;
2017}
2018
2019.trwarning {
2020 background-color: #FFFF80;
2021}
2022
2023.trerror {
2024 background-color: #FF8080;
2025}
2026</style>
5f016d39
DO
2027</head>
2028<body>
2029<h1>Platform check status</h1>
2030<?php
2031
2032if (!platform_is_ok())
50cc9dbe
DO
2033{
2034 echo '<h1>Please resolve the failed (red) item(s) above.</h1>';
5f016d39 2035 die ('</body></html>');
50cc9dbe 2036}
5f016d39
DO
2037
2038echo '<h1>Upgrade status</h1>';
fbbb74fb 2039$dbver = getDatabaseVersion();
5f016d39 2040echo '<table border=1 cellpadding=5>';
4114697d
DO
2041echo "<tr><th>Current status</th><td>Data version: ${dbver}<br>Code version: " . CODE_VERSION . "</td></tr>\n";
2042
2043$path = getDBUpgradePath ($dbver, CODE_VERSION);
2044if ($path === NULL)
758fe24c 2045{
4114697d 2046 echo "<tr><th>Upgrade path</th><td><font color=red>not found</font></td></tr>\n";
d74ae24c
DO
2047 echo "<tr><th>Summary</th><td>Check README for more information. RackTables releases prior to 0.16.4 ";
2048 echo "must be upgraded to 0.16.4 first.</td></tr>\n";
758fe24c 2049}
4114697d 2050else
5f4027b8 2051{
4114697d
DO
2052 if (!count ($path))
2053 echo "<tr><th>Summary</th><td>Come back later.</td></tr>\n";
2054 else
2055 {
2056 echo "<tr><th>Upgrade path</th><td>${dbver} &rarr; " . implode (' &rarr; ', $path) . "</td></tr>\n";
abc799c5 2057 global $relnotes;
4114697d 2058 foreach ($path as $batchid)
4114697d 2059 if (isset ($relnotes[$batchid]))
5ae6d365 2060 echo "<tr><th>Release notes for ${batchid}</th><td><pre>" . $relnotes[$batchid] . "</pre></td></tr>\n";
a12022a9
DO
2061 if (array_key_exists ('reallyreally', $_REQUEST))
2062 {
2063 foreach ($path as $batchid)
2064 executeUpgradeBatch ($batchid);
d15dae2f 2065 executeUpgradeBatch ('dictionary');
a12022a9
DO
2066 echo "<tr><th>Summary</th><td>Upgrade complete, it is Ok to ";
2067 echo "<a href='index.php'>enter</a> the system.</td></tr>\n";
2068 }
2069 else
2070 {
2071 echo '<form method=post action="index.php?module=upgrade"><tr><th>Wait!</th>';
2072 echo '<td><p>RackTables database upgrades sometimes go wrong because of assorted reasons. ';
2073 echo 'It is <strong>highly recommended</strong> to make a database backup before ';
2074 echo 'proceeding any further. <tt>mysqldump</tt> and <tt>PHPMyAdmin</tt> are convenient ';
2075 echo 'tools for doing this.</p>';
2076 echo '<p><input type=checkbox name=reallyreally id=reallyreally><label for=reallyreally>';
2077 echo 'I am ready to bear all risks of this upgrade. I am ready to roll it back in case of ';
2078 echo 'a failure.</label> <input type=submit value="Yes, I am."></p></td></tr></form>';
4114697d 2079 }
4114697d 2080 }
5f4027b8 2081}
4114697d 2082echo '</table>';
5f016d39 2083echo '</body></html>';
964b0388 2084}
fbbb74fb 2085
71066ef1
AA
2086function convertSLBTablesToBinIPs()
2087{
2088 global $dbxlink;
2089
2090 $dbxlink->query ("DROP TABLE IF EXISTS `IPv4VS_new`, `IPv4RS_new`, `IPv4VS_old`, `IPv4RS_old`");
2091
2092 $dbxlink->query (<<<END
2093CREATE TABLE `IPv4VS_new` (
2094 `id` int(10) unsigned NOT NULL auto_increment,
2095 `vip` varbinary(16) NOT NULL,
2096 `vport` smallint(5) unsigned default NULL,
2097 `proto` enum('TCP','UDP','MARK') NOT NULL default 'TCP',
2098 `name` char(255) default NULL,
2099 `vsconfig` text,
2100 `rsconfig` text,
2101 PRIMARY KEY (`id`),
2102 KEY `vip` (`vip`)
2103) ENGINE=InnoDB DEFAULT CHARSET=utf8
2104END
2105 );
2106 $result = $dbxlink->query ("SELECT * FROM IPv4VS");
2107 $rows = $result->fetchAll (PDO::FETCH_ASSOC);
2108 unset ($result);
2109 foreach ($rows as $row)
2110 {
2111 $row['vip'] = ip4_int2bin ($row['vip']);
2112 usePreparedInsertBlade ('IPv4VS_new', $row);
2113 }
dec748f6 2114
71066ef1
AA
2115 $dbxlink->query (<<<END
2116CREATE TABLE `IPv4RS_new` (
2117 `id` int(10) unsigned NOT NULL auto_increment,
2118 `inservice` enum('yes','no') NOT NULL default 'no',
2119 `rsip` varbinary(16) NOT NULL,
2120 `rsport` smallint(5) unsigned default NULL,
2121 `rspool_id` int(10) unsigned default NULL,
2122 `rsconfig` text,
2123 `comment` varchar(255) DEFAULT NULL,
2124 PRIMARY KEY (`id`),
2125 KEY `rsip` (`rsip`),
2126 UNIQUE KEY `pool-endpoint` (`rspool_id`,`rsip`,`rsport`),
2127 CONSTRAINT `IPRS-FK` FOREIGN KEY (`rspool_id`) REFERENCES `IPv4RSPool` (`id`) ON DELETE CASCADE
2128) ENGINE=InnoDB DEFAULT CHARSET=utf8
2129END
2130 );
2131 $result = $dbxlink->query ("SELECT * FROM IPv4RS");
2132 $rows = $result->fetchAll (PDO::FETCH_ASSOC);
2133 unset ($result);
2134 foreach ($rows as $row)
2135 {
2136 $row['rsip'] = ip4_int2bin ($row['rsip']);
2137 usePreparedInsertBlade ('IPv4RS_new', $row);
2138 }
2139
2140 $dbxlink->query (<<<END
2141RENAME TABLE
2142 `IPv4VS` TO `IPv4VS_old`,
2143 `IPv4VS_new` TO `IPv4VS`,
2144 `IPv4RS` TO `IPv4RS_old`,
2145 `IPv4RS_new` TO `IPv4RS`
2146END
2147 );
2148 // re-create foreign key in IPv4LB
2149 $dbxlink->query ("ALTER TABLE `IPv4LB` DROP FOREIGN KEY `IPv4LB-FK-vs_id`");
2150 $dbxlink->query ("ALTER TABLE `IPv4LB` ADD CONSTRAINT `IPv4LB-FK-vs_id` FOREIGN KEY (`vs_id`) REFERENCES `IPv4VS` (`id`)");
2151
2152 $dbxlink->query ("DROP TABLE `IPv4VS_old`, `IPv4RS_old`");
93b7c5d8
AA
2153
2154 // re-create foreign key in IPv4RS
2155 $dbxlink->query ("ALTER TABLE `IPv4RS` DROP FOREIGN KEY `IPRS-FK`");
2156 $dbxlink->query ("ALTER TABLE `IPv4RS` ADD CONSTRAINT `IPv4RS-FK` FOREIGN KEY (`rspool_id`) REFERENCES `IPv4RSPool` (`id`) ON DELETE CASCADE");
71066ef1
AA
2157}
2158
2159// This is a swiss-knife blade to insert a record into a table.
2160// The first argument is table name.
2161// The second argument is an array of "name" => "value" pairs.
2162// returns integer - affected rows count. Throws exception on error
2163function usePreparedInsertBlade ($tablename, $columns)
2164{
2165 global $dbxlink;
2166 $query = "INSERT INTO ${tablename} (" . implode (', ', array_keys ($columns));
2167 $query .= ') VALUES (' . questionMarks (count ($columns)) . ')';
2168 // Now the query should be as follows:
2169 // INSERT INTO table (c1, c2, c3) VALUES (?, ?, ?)
2170 try
2171 {
2172 $prepared = $dbxlink->prepare ($query);
2173 $prepared->execute (array_values ($columns));
2174 return $prepared->rowCount();
2175 }
2176 catch (PDOException $e)
2177 {
2178 throw convertPDOException ($e);
2179 }
2180}
2181
fbbb74fb 2182?>