function getUpgradeBatch($batchid)
{
$query = array();
global $dbver, $dbxlink;
switch ($batchid) {
case '0.18.0':
$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')";
$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')";
$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')";
$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')";
$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')";
$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')";
$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')";
$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')";
$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')";
$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')";
$query[] = "ALTER TABLE IPv4Network ENGINE=InnoDB";
$query[] = "SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0";
$query[] = "\nCREATE TABLE `CachedPAV` (\n `object_id` int(10) unsigned NOT NULL,\n `port_name` char(255) NOT NULL,\n `vlan_id` int(10) unsigned NOT NULL default '0',\n PRIMARY KEY (`object_id`,`port_name`,`vlan_id`),\n KEY `vlan_id` (`vlan_id`),\n CONSTRAINT `CachedPAV-FK-object-port` FOREIGN KEY (`object_id`, `port_name`) REFERENCES `CachedPVM` (`object_id`, `port_name`) ON DELETE CASCADE,\n CONSTRAINT `CachedPAV-FK-vlan_id` FOREIGN KEY (`vlan_id`) REFERENCES `VLANValidID` (`vlan_id`)\n) ENGINE=InnoDB\n";
$query[] = "\nCREATE TABLE `CachedPNV` (\n `object_id` int(10) unsigned NOT NULL,\n `port_name` char(255) NOT NULL,\n `vlan_id` int(10) unsigned NOT NULL default '0',\n PRIMARY KEY (`object_id`,`port_name`,`vlan_id`),\n UNIQUE KEY `port_id` (`object_id`,`port_name`),\n CONSTRAINT `CachedPNV-FK-compound` FOREIGN KEY (`object_id`, `port_name`, `vlan_id`) REFERENCES `CachedPAV` (`object_id`, `port_name`, `vlan_id`) ON DELETE CASCADE\n) ENGINE=InnoDB\n";
$query[] = "\nCREATE TABLE `CachedPVM` (\n `object_id` int(10) unsigned NOT NULL,\n `port_name` char(255) NOT NULL,\n `vlan_mode` enum('access','trunk') NOT NULL default 'access',\n PRIMARY KEY (`object_id`,`port_name`),\n CONSTRAINT `CachedPVM-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE\n) ENGINE=InnoDB\n";
$query[] = "\nCREATE TABLE `PortAllowedVLAN` (\n `object_id` int(10) unsigned NOT NULL,\n `port_name` char(255) NOT NULL,\n `vlan_id` int(10) unsigned NOT NULL default '0',\n PRIMARY KEY (`object_id`,`port_name`,`vlan_id`),\n KEY `vlan_id` (`vlan_id`),\n CONSTRAINT `PortAllowedVLAN-FK-object-port` FOREIGN KEY (`object_id`, `port_name`) REFERENCES `PortVLANMode` (`object_id`, `port_name`) ON DELETE CASCADE,\n CONSTRAINT `PortAllowedVLAN-FK-vlan_id` FOREIGN KEY (`vlan_id`) REFERENCES `VLANValidID` (`vlan_id`)\n) ENGINE=InnoDB\n";
$query[] = "\nCREATE TABLE `PortNativeVLAN` (\n `object_id` int(10) unsigned NOT NULL,\n `port_name` char(255) NOT NULL,\n `vlan_id` int(10) unsigned NOT NULL default '0',\n PRIMARY KEY (`object_id`,`port_name`,`vlan_id`),\n UNIQUE KEY `port_id` (`object_id`,`port_name`),\n CONSTRAINT `PortNativeVLAN-FK-compound` FOREIGN KEY (`object_id`, `port_name`, `vlan_id`) REFERENCES `PortAllowedVLAN` (`object_id`, `port_name`, `vlan_id`) ON DELETE CASCADE\n) ENGINE=InnoDB\n";
$query[] = "\nCREATE TABLE `PortVLANMode` (\n `object_id` int(10) unsigned NOT NULL,\n `port_name` char(255) NOT NULL,\n `vlan_mode` enum('access','trunk') NOT NULL default 'access',\n PRIMARY KEY (`object_id`,`port_name`),\n CONSTRAINT `PortVLANMode-FK-object-port` FOREIGN KEY (`object_id`, `port_name`) REFERENCES `CachedPVM` (`object_id`, `port_name`)\n) ENGINE=InnoDB\n";
$query[] = "\nCREATE TABLE `VLANDescription` (\n `domain_id` int(10) unsigned NOT NULL,\n `vlan_id` int(10) unsigned NOT NULL default '0',\n `vlan_type` enum('ondemand','compulsory','alien') NOT NULL default 'ondemand',\n `vlan_descr` char(255) default NULL,\n PRIMARY KEY (`domain_id`,`vlan_id`),\n KEY `vlan_id` (`vlan_id`),\n CONSTRAINT `VLANDescription-FK-domain_id` FOREIGN KEY (`domain_id`) REFERENCES `VLANDomain` (`id`) ON DELETE CASCADE,\n CONSTRAINT `VLANDescription-FK-vlan_id` FOREIGN KEY (`vlan_id`) REFERENCES `VLANValidID` (`vlan_id`)\n) ENGINE=InnoDB\n";
$query[] = "\nCREATE TABLE `VLANDomain` (\n `id` int(10) unsigned NOT NULL auto_increment,\n `description` char(255) default NULL,\n PRIMARY KEY (`id`),\n UNIQUE KEY `description` (`description`)\n) ENGINE=InnoDB\n";
$query[] = "\nCREATE TABLE `VLANIPv4` (\n `domain_id` int(10) unsigned NOT NULL,\n `vlan_id` int(10) unsigned NOT NULL,\n `ipv4net_id` int(10) unsigned NOT NULL,\n UNIQUE KEY `network-domain` (`ipv4net_id`,`domain_id`),\n KEY `VLANIPv4-FK-compound` (`domain_id`,`vlan_id`),\n CONSTRAINT `VLANIPv4-FK-compound` FOREIGN KEY (`domain_id`, `vlan_id`) REFERENCES `VLANDescription` (`domain_id`, `vlan_id`) ON DELETE CASCADE,\n CONSTRAINT `VLANIPv4-FK-ipv4net_id` FOREIGN KEY (`ipv4net_id`) REFERENCES `IPv4Network` (`id`) ON DELETE CASCADE\n) ENGINE=InnoDB\n";
$query[] = "\nCREATE TABLE `VLANSTRule` (\n `vst_id` int(10) unsigned NOT NULL,\n `rule_no` int(10) unsigned NOT NULL,\n `port_pcre` char(255) NOT NULL,\n `port_role` enum('access','trunk','uplink','downlink','none') NOT NULL default 'none',\n `wrt_vlans` char(255) default NULL,\n `description` char(255) default NULL,\n UNIQUE KEY `vst-rule` (`vst_id`,`rule_no`),\n CONSTRAINT `VLANSTRule-FK-vst_id` FOREIGN KEY (`vst_id`) REFERENCES `VLANSwitchTemplate` (`id`) ON DELETE CASCADE\n) ENGINE=InnoDB\n";
$query[] = "\nCREATE TABLE `VLANSwitch` (\n `object_id` int(10) unsigned NOT NULL,\n `domain_id` int(10) unsigned NOT NULL,\n `template_id` int(10) unsigned NOT NULL,\n `mutex_rev` int(10) unsigned NOT NULL default '0',\n `out_of_sync` enum('yes','no') NOT NULL default 'yes',\n `last_errno` int(10) unsigned NOT NULL default '0',\n `last_change` timestamp NOT NULL default '0000-00-00 00:00:00',\n `last_push_started` timestamp NOT NULL default '0000-00-00 00:00:00',\n `last_push_finished` timestamp NOT NULL default '0000-00-00 00:00:00',\n `last_error_ts` timestamp NOT NULL default '0000-00-00 00:00:00',\n UNIQUE KEY `object_id` (`object_id`),\n KEY `domain_id` (`domain_id`),\n KEY `template_id` (`template_id`),\n KEY `out_of_sync` (`out_of_sync`),\n KEY `last_errno` (`last_errno`),\n CONSTRAINT `VLANSwitch-FK-domain_id` FOREIGN KEY (`domain_id`) REFERENCES `VLANDomain` (`id`),\n CONSTRAINT `VLANSwitch-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`),\n CONSTRAINT `VLANSwitch-FK-template_id` FOREIGN KEY (`template_id`) REFERENCES `VLANSwitchTemplate` (`id`)\n) ENGINE=InnoDB\n";
$query[] = "\nCREATE TABLE `VLANSwitchTemplate` (\n `id` int(10) unsigned NOT NULL auto_increment,\n `max_local_vlans` int(10) unsigned default NULL,\n `description` char(255) default NULL,\n PRIMARY KEY (`id`),\n UNIQUE KEY `description` (`description`)\n) ENGINE=InnoDB\n";
$query[] = "\nCREATE TABLE `VLANValidID` (\n `vlan_id` int(10) unsigned NOT NULL default '1',\n PRIMARY KEY (`vlan_id`)\n) ENGINE=InnoDB\n";
$query[] = "SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS";
for ($i = 1; $i <= 4094; $i++) {
$query[] = "INSERT INTO VLANValidID (vlan_id) VALUES ({$i})";
}
$query[] = "UPDATE Config SET varvalue = '0.18.0' WHERE varname = 'DB_VERSION'";
break;
case '0.18.1':
$query[] = "ALTER TABLE Atom ENGINE=InnoDB";
$query[] = "ALTER TABLE AttributeMap ENGINE=InnoDB";
$query[] = "ALTER TABLE Config ENGINE=InnoDB";
$query[] = "ALTER TABLE IPv4Address ENGINE=InnoDB";
$query[] = "ALTER TABLE IPv4Allocation ENGINE=InnoDB";
$query[] = "ALTER TABLE Molecule ENGINE=InnoDB";
$query[] = "ALTER TABLE MountOperation ENGINE=InnoDB";
$query[] = "ALTER TABLE PortCompat ENGINE=InnoDB";
$query[] = "ALTER TABLE Rack ENGINE=InnoDB";
$query[] = "ALTER TABLE RackHistory ENGINE=InnoDB";
$query[] = "ALTER TABLE RackObjectHistory ENGINE=InnoDB";
$query[] = "ALTER TABLE RackRow ENGINE=InnoDB";
$query[] = "ALTER TABLE RackSpace ENGINE=InnoDB";
$query[] = "ALTER TABLE Script ENGINE=InnoDB";
$query[] = "ALTER TABLE AttributeValue DROP FOREIGN KEY `AttributeValue-FK-object_id`";
$query[] = "ALTER TABLE AttributeValue ADD CONSTRAINT `AttributeValue-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE";
$query[] = "ALTER TABLE RackObjectHistory ADD KEY (id)";
$query[] = "ALTER TABLE RackObjectHistory ADD CONSTRAINT `RackObjectHistory-FK-object_id` FOREIGN KEY (id) REFERENCES `RackObject` (`id`) ON DELETE CASCADE";
$query[] = "ALTER TABLE MountOperation ADD CONSTRAINT `MountOperation-FK-object_id` FOREIGN KEY (object_id) REFERENCES RackObject (id) ON DELETE CASCADE";
$query[] = "ALTER TABLE RackSpace ADD CONSTRAINT `RackSpace-FK-object_id` FOREIGN KEY (object_id) REFERENCES RackObject (id) ON DELETE CASCADE";
$query[] = "ALTER TABLE Link DROP FOREIGN KEY `Link-FK-a`";
$query[] = "ALTER TABLE Link ADD CONSTRAINT `Link-FK-a` FOREIGN KEY (`porta`) REFERENCES `Port` (`id`) ON DELETE CASCADE";
$query[] = "ALTER TABLE Link DROP FOREIGN KEY `Link-FK-b`";
$query[] = "ALTER TABLE Link ADD CONSTRAINT `Link-FK-b` FOREIGN KEY (`portb`) REFERENCES `Port` (`id`) ON DELETE CASCADE";
$query[] = "ALTER TABLE Port DROP FOREIGN KEY `Port-FK-object_id`";
$query[] = "ALTER TABLE Port ADD CONSTRAINT `Port-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE";
$query[] = "ALTER TABLE AttributeMap MODIFY `chapter_id` int(10) unsigned default NULL";
$query[] = "ALTER TABLE IPv4Address MODIFY `ip` int(10) unsigned NOT NULL default '0'";
$query[] = "ALTER TABLE IPv4Address MODIFY `name` char(255) NOT NULL default ''";
$query[] = "ALTER TABLE IPv4Allocation MODIFY `object_id` int(10) unsigned NOT NULL default '0'";
$query[] = "ALTER TABLE IPv4Allocation MODIFY `ip` int(10) unsigned NOT NULL default '0'";
$query[] = "ALTER TABLE IPv4Allocation MODIFY `name` char(255) NOT NULL default ''";
$query[] = "ALTER TABLE IPv4NAT MODIFY `object_id` int(10) unsigned NOT NULL default '0'";
$query[] = "ALTER TABLE IPv4NAT MODIFY `proto` enum('TCP','UDP') NOT NULL default 'TCP'";
$query[] = "ALTER TABLE IPv4NAT MODIFY `localip` int(10) unsigned NOT NULL default '0'";
$query[] = "ALTER TABLE IPv4NAT MODIFY `localport` smallint(5) unsigned NOT NULL default '0'";
$query[] = "ALTER TABLE IPv4NAT MODIFY `remoteip` int(10) unsigned NOT NULL default '0'";
$query[] = "ALTER TABLE IPv4NAT MODIFY `remoteport` smallint(5) unsigned NOT NULL default '0'";
$query[] = "ALTER TABLE IPv4Network MODIFY `ip` int(10) unsigned NOT NULL default '0'";
$query[] = "ALTER TABLE IPv4Network MODIFY `mask` int(10) unsigned NOT NULL default '0'";
$query[] = "ALTER TABLE Link MODIFY `porta` int(10) unsigned NOT NULL default '0'";
$query[] = "ALTER TABLE Link MODIFY `portb` int(10) unsigned NOT NULL default '0'";
$query[] = "ALTER TABLE MountOperation MODIFY `object_id` int(10) unsigned NOT NULL default '0'";
$query[] = "ALTER TABLE MountOperation MODIFY `ctime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP";
$query[] = "ALTER TABLE Port MODIFY `object_id` int(10) unsigned NOT NULL default '0'";
$query[] = "ALTER TABLE Port MODIFY `name` char(255) NOT NULL default ''";
$query[] = "ALTER TABLE Port MODIFY `type` int(10) unsigned NOT NULL default '0'";
$query[] = "ALTER TABLE PortCompat MODIFY `type1` int(10) unsigned NOT NULL default '0'";
$query[] = "ALTER TABLE PortCompat MODIFY `type2` int(10) unsigned NOT NULL default '0'";
$query[] = "ALTER TABLE RackHistory MODIFY `ctime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP";
$query[] = "ALTER TABLE RackObjectHistory MODIFY `ctime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP";
$query[] = "ALTER TABLE TagStorage MODIFY `tag_id` int(10) unsigned NOT NULL default '0'";
$query[] = "ALTER TABLE UserAccount MODIFY `user_name` char(64) NOT NULL default ''";
$query[] = "UPDATE Config SET varvalue = '0.18.1' WHERE varname = 'DB_VERSION'";
break;
case '0.18.2':
$query[] = "ALTER TABLE Rack ADD CONSTRAINT `Rack-FK-row_id` FOREIGN KEY (row_id) REFERENCES RackRow (id)";
$query[] = "ALTER TABLE RackRow ADD UNIQUE KEY `name` (name)";
$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')";
$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')";
$query[] = "UPDATE Config SET varvalue = '0.18.2' WHERE varname = 'DB_VERSION'";
break;
case '0.18.3':
$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'";
$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)')";
$query[] = "UPDATE Config SET varvalue = '0.18.3' WHERE varname = 'DB_VERSION'";
break;
case '0.18.4':
$query[] = "ALTER TABLE VLANSTRule MODIFY port_role enum('access','trunk','anymode','uplink','downlink','none') NOT NULL default 'none'";
$query[] = "UPDATE Config SET varvalue = '0.18.4' WHERE varname = 'DB_VERSION'";
break;
case '0.18.5':
$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')";
$query[] = "ALTER TABLE `IPv4LB` ADD COLUMN `prio` int(10) unsigned DEFAULT NULL AFTER `vs_id`";
$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')";
$query[] = "UPDATE Config SET varvalue = '0.18.5' WHERE varname = 'DB_VERSION'";
break;
case '0.18.6':
$query[] = "UPDATE Config SET varvalue = '0.18.6' WHERE varname = 'DB_VERSION'";
break;
case '0.18.7':
$query[] = "UPDATE Config SET varvalue = '0.18.7' WHERE varname = 'DB_VERSION'";
break;
case '0.19.0':
$query[] = 'ALTER TABLE `File` ADD `thumbnail` LONGBLOB NULL AFTER `atime`';
$query[] = "\nCREATE TABLE `IPv6Address` (\n `ip` binary(16) NOT NULL,\n `name` char(255) NOT NULL default '',\n `reserved` enum('yes','no') default NULL,\n PRIMARY KEY (`ip`)\n) ENGINE=InnoDB\n";
$query[] = "\nCREATE TABLE `IPv6Allocation` (\n `object_id` int(10) unsigned NOT NULL default '0',\n `ip` binary(16) NOT NULL,\n `name` char(255) NOT NULL default '',\n `type` enum('regular','shared','virtual','router') default NULL,\n PRIMARY KEY (`object_id`,`ip`),\n CONSTRAINT `IPv6Allocation-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE\n) ENGINE=InnoDB\n";
$query[] = "\nCREATE TABLE `IPv6Network` (\n `id` int(10) unsigned NOT NULL auto_increment,\n `ip` binary(16) NOT NULL,\n `mask` int(10) unsigned NOT NULL,\n `last_ip` binary(16) NOT NULL,\n `name` char(255) default NULL,\n `comment` text,\n PRIMARY KEY (`id`),\n UNIQUE KEY `ip` (`ip`,`mask`)\n) ENGINE=InnoDB\n";
$query[] = "\nCREATE TABLE `VLANIPv6` (\n `domain_id` int(10) unsigned NOT NULL,\n `vlan_id` int(10) unsigned NOT NULL,\n `ipv6net_id` int(10) unsigned NOT NULL,\n UNIQUE KEY `network-domain` (`ipv6net_id`,`domain_id`),\n KEY `VLANIPv6-FK-compound` (`domain_id`,`vlan_id`),\n CONSTRAINT `VLANIPv6-FK-compound` FOREIGN KEY (`domain_id`, `vlan_id`) REFERENCES `VLANDescription` (`domain_id`, `vlan_id`) ON DELETE CASCADE,\n CONSTRAINT `VLANIPv6-FK-ipv6net_id` FOREIGN KEY (`ipv6net_id`) REFERENCES `IPv6Network` (`id`) ON DELETE CASCADE\n) ENGINE=InnoDB\n";
$query[] = "\nCREATE TABLE IF NOT EXISTS `ObjectLog` (\n `id` int(10) NOT NULL AUTO_INCREMENT,\n `object_id` int(10) NOT NULL,\n `user` varchar(64) NOT NULL,\n `date` datetime NOT NULL,\n `content` text NOT NULL,\n PRIMARY KEY (`id`)\n) ENGINE=InnoDB\n";
# Now we have the same structure of ObjectLog table, which objectlog.php
# could have left. Subsequent column updates will handle any existing data.
$query[] = "ALTER TABLE ObjectLog MODIFY COLUMN `id` int(10) unsigned NOT NULL AUTO_INCREMENT";
$query[] = "ALTER TABLE ObjectLog MODIFY COLUMN `object_id` int(10) unsigned NOT NULL";
$query[] = "ALTER TABLE ObjectLog MODIFY COLUMN `user` char(64) NOT NULL";
$query[] = "ALTER TABLE ObjectLog ADD KEY `object_id` (`object_id`)";
$query[] = "ALTER TABLE ObjectLog ADD KEY `date` (`date`)";
$query[] = "ALTER TABLE ObjectLog ADD CONSTRAINT `ObjectLog-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE";
# Now it's the way 0.19.0 is expecting it to be.
$query[] = "\nCREATE TABLE `ObjectParentCompat` (\n `parent_objtype_id` int(10) unsigned NOT NULL,\n `child_objtype_id` int(10) unsigned NOT NULL,\n UNIQUE KEY `parent_child` (`parent_objtype_id`,`child_objtype_id`)\n) ENGINE=InnoDB\n";
$query[] = "\nCREATE TABLE `EntityLink` (\n `id` int(10) unsigned NOT NULL AUTO_INCREMENT,\n `parent_entity_type` enum('ipv4net','ipv4rspool','ipv4vs','ipv6net','object','rack','user') NOT NULL,\n `parent_entity_id` int(10) unsigned NOT NULL,\n `child_entity_type` enum('file','object') NOT NULL,\n `child_entity_id` int(10) unsigned NOT NULL,\n PRIMARY KEY (`id`),\n UNIQUE KEY `EntityLink-unique` (`parent_entity_type`,`parent_entity_id`,`child_entity_type`,`child_entity_id`)\n) ENGINE=InnoDB\n";
$query[] = "ALTER TABLE `TagStorage` CHANGE COLUMN `entity_realm` `entity_realm` ENUM('file','ipv4net','ipv4vs','ipv4rspool','object','rack','user','ipv6net') NOT NULL DEFAULT 'object' FIRST";
$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`";
$query[] = 'ALTER TABLE Link ADD COLUMN cable char(64) NULL AFTER portb';
$query[] = 'ALTER TABLE RackSpace ADD CONSTRAINT `RackSpace-FK-rack_id` FOREIGN KEY (rack_id) REFERENCES Rack (id)';
$query[] = "ALTER TABLE `IPv4Allocation` ADD CONSTRAINT `IPv4Allocation-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE";
$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')";
$query[] = "ALTER TABLE `VLANSwitchTemplate` ADD COLUMN `mutex_rev` int(10) NOT NULL AFTER `id`";
$query[] = "ALTER TABLE `VLANSwitchTemplate` ADD COLUMN `saved_by` char(64) NOT NULL AFTER `description`";
$query[] = "INSERT INTO `Attribute` (`id`, `type`, `name`) VALUES (26,'dict','Hypervisor')";
$query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (29,'no','Yes/No')";
$query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (30,'no','network chassis models')";
$query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (31,'no','server chassis models')";
$query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (32,'no','virtual switch models')";
$query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (33,'no','virtual switch OS type')";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (4,26,29)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1502,1,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1502,2,31)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1502,3,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1502,14,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1502,20,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1502,21,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1502,22,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,1,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,2,30)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,3,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,4,14)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,5,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,14,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,16,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,17,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,18,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,20,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,21,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,22,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,24,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1504,3,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1504,4,13)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1504,14,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1504,24,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1505,14,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1506,14,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1506,17,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1506,18,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,1,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,2,32)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,3,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,4,33)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,5,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,14,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,20,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,21,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,22,NULL)";
$query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (3,13)";
$query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (4,1504)";
$query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (4,1507)";
$query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1502,4)";
$query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1503,8)";
$query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1505,4)";
$query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1505,1504)";
$query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1505,1506)";
$query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1505,1507)";
$query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1506,1504)";
$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')";
$query[] = "UPDATE Config SET varvalue = CONCAT(varvalue, ' or {\$typeid_1502} or {\$typeid_1503} or {\$typeid_1504} or {\$typeid_1507}') WHERE varname = 'IPV4OBJ_LISTSRC'";
$query[] = "UPDATE Config SET varvalue = '8' WHERE varname = 'MASSCOUNT'";
$query[] = "UPDATE RackObject SET label = NULL WHERE label = ''";
// Move barcode data so the column can be dropped
$result = $dbxlink->query('SELECT id, objtype_id, barcode FROM RackObject WHERE barcode IS NOT NULL');
$rows = $result->fetchAll(PDO::FETCH_ASSOC);
unset($result);
foreach ($rows as $row) {
// Determine if this object type has the 'OEM S/N 1' attribute associated with it, and if it's set
$sn_query = "SELECT (SELECT COUNT(*) FROM AttributeMap WHERE objtype_id={$row['objtype_id']} AND attr_id=1) AS AM_count, ";
$sn_query .= "(SELECT COUNT(*) FROM AttributeValue WHERE object_id={$row['id']} AND attr_id=1) AS AV_count";
$sn_result = $dbxlink->query($sn_query);
$sn_row = $sn_result->fetch(PDO::FETCH_ASSOC);
if ($sn_row['AM_count'] == 1 && $sn_row['AV_count'] == 0) {
// 'OEM S/N 1' attribute is mapped to this object type, but it is not set. Good!
// Copy the barcode value to the attribute.
$query[] = "INSERT INTO AttributeValue (`object_id`, `attr_id`, `string_value`) VALUES ({$row['id']}, 1, '{$row['barcode']}')";
} else {
// Some other set of circumstances. Not as good!
// Copy the barcode value to a new ObjectLog record.
$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']}')";
}
unset($sn_query, $sn_result, $sn_row);
}
$query[] = 'ALTER TABLE RackObject DROP COLUMN `barcode`';
$query[] = 'ALTER TABLE RackObjectHistory DROP COLUMN `barcode`';
$query[] = 'ALTER TABLE `VLANSwitchTemplate` DROP COLUMN `max_local_vlans`';
$query[] = "UPDATE Config SET varvalue = '0.19.0' WHERE varname = 'DB_VERSION'";
break;
case '0.19.1':
$query[] = "ALTER TABLE `Config` CHANGE COLUMN `varvalue` `varvalue` text NOT NULL";
$query[] = "ALTER TABLE `UserConfig` CHANGE COLUMN `varvalue` `varvalue` text NOT NULL";
$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')";
$result = $dbxlink->query("SHOW TABLES LIKE 'Objectlog'");
$rows = $result->fetchAll(PDO::FETCH_ASSOC);
unset($result);
if (count($rows)) {
# Now the ObjectLog merge... again, because the original table is named
# "Objectlog". The job is to merge contents of Objectlog and ObjectLog
# into the latter.
$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)";
$query[] = "DELETE FROM Objectlog WHERE object_id IN(SELECT id FROM RackObject)";
# Don't delete the old table, if the merge wasn't exhaustive.
$result = $dbxlink->query('SELECT COUNT(*) AS c FROM Objectlog WHERE object_id NOT IN(SELECT id FROM RackObject)');
$row = $result->fetch(PDO::FETCH_ASSOC);
unset($result);
if ($row['c'] == 0) {
$query[] = 'DROP TABLE Objectlog';
} else {
$query[] = 'ALTER TABLE Objectlog RENAME TO Objectlog_old_unmerged';
}
}
$query[] = "UPDATE Config SET varvalue = '0.19.1' WHERE varname = 'DB_VERSION'";
break;
case '0.19.2':
$query[] = "ALTER TABLE IPv4Allocation ADD KEY `ip` (`ip`)";
$query[] = "ALTER TABLE IPv6Allocation ADD KEY `ip` (`ip`)";
$query[] = "ALTER TABLE IPv4VS ADD KEY `vip` (`vip`)";
$query[] = "ALTER TABLE IPv4RS ADD KEY `rsip` (`rsip`)";
$query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (34,'no','power supply chassis models')";
$query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (35,'no','power supply models')";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1397,1,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1397,2,34)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1397,14,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1397,21,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1397,22,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1398,1,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1398,2,35)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1398,14,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1398,21,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1398,22,NULL)";
$query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1397,1398)";
$query[] = "INSERT INTO `PortCompat` (`type1`, `type2`) VALUES (1399,1399)";
$query[] = "INSERT INTO `PortInterfaceCompat` (`iif_id`, `oif_id`) VALUES (1,1399)";
$query[] = "UPDATE Config SET varvalue = CONCAT(varvalue, ' or {\$typeid_1397}') WHERE varname = 'IPV4OBJ_LISTSRC'";
$query[] = "ALTER TABLE AttributeValue ADD KEY `attr_id-uint_value` (`attr_id`,`uint_value`)";
$query[] = "ALTER TABLE AttributeValue ADD KEY `attr_id-string_value` (`attr_id`,`string_value`(12))";
$query[] = "UPDATE Config SET varvalue = '0.19.2' WHERE varname = 'DB_VERSION'";
break;
case '0.19.3':
$query[] = "DELETE FROM RackSpace WHERE object_id IS NULL AND state = 'T'";
$query[] = "UPDATE Config SET varvalue = '0.19.3' WHERE varname = 'DB_VERSION'";
break;
case '0.19.4':
$query[] = "UPDATE Config SET varvalue = '0.19.4' WHERE varname = 'DB_VERSION'";
break;
case '0.19.5':
// Add 'virtual port' to 'virtual port' mapping
$query[] = "INSERT INTO `PortCompat` (`type1`,`type2`) VALUES (1469,1469)";
$query[] = "INSERT INTO `PortInterfaceCompat` (`iif_id`,`oif_id`) VALUES (1,1469)";
$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')";
$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')";
$query[] = "UPDATE Link SET cable = NULL WHERE cable = ''";
$query[] = "ALTER TABLE AttributeValue MODIFY string_value char(255) DEFAULT NULL";
$query[] = "UPDATE Config SET varvalue = '0.19.5' WHERE varname = 'DB_VERSION'";
break;
case '0.19.6':
$query[] = "UPDATE Config SET varvalue = '0.19.6' WHERE varname = 'DB_VERSION'";
break;
case '0.19.7':
# A plain "ALTER TABLE Attribute" can leave AUTO_INCREMENT in an odd
# state, hence the table swap.
$query[] = "\nCREATE TABLE `Attribute_new` (\n `id` int(10) unsigned NOT NULL auto_increment,\n `type` enum('string','uint','float','dict') default NULL,\n `name` char(64) default NULL,\n PRIMARY KEY (`id`),\n UNIQUE KEY `name` (`name`)\n) ENGINE=InnoDB\n";
$query[] = "INSERT INTO Attribute_new SELECT * FROM Attribute";
$query[] = "INSERT INTO Attribute_new VALUES (9999, 'string', 'base MAC address')";
$query[] = "DROP TABLE Attribute";
$query[] = "ALTER TABLE Attribute_new RENAME TO Attribute";
$query[] = "ALTER TABLE AttributeMap ADD KEY (attr_id)";
$query[] = "DELETE FROM AttributeMap WHERE attr_id NOT IN (SELECT id FROM Attribute)";
$query[] = "ALTER TABLE AttributeMap ADD CONSTRAINT `AttributeMap-FK-attr_id` FOREIGN KEY (attr_id) REFERENCES Attribute (id)";
$query[] = "DELETE FROM AttributeValue WHERE attr_id NOT IN (SELECT attr_id FROM AttributeMap)";
$query[] = "ALTER TABLE AttributeValue ADD CONSTRAINT `AttributeValue-FK-attr_id` FOREIGN KEY (attr_id) REFERENCES AttributeMap (attr_id)";
$query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1506,4)";
$query[] = "INSERT INTO PortInnerInterface (id, iif_name) VALUES (10, 'QSFP+')";
$query[] = "INSERT INTO PortInterfaceCompat VALUES (10, 1588)";
$query[] = "UPDATE Config SET varvalue = '0.19.7' WHERE varname = 'DB_VERSION'";
break;
case '0.19.8':
for ($i = 1424; $i <= 1466; $i++) {
# CX, then 42 ER channels
$query[] = "INSERT INTO `PortCompat` (`type1`, `type2`) VALUES ({$i},{$i})";
}
$query[] = "ALTER TABLE UserAccount ENGINE=InnoDB";
$query[] = "DELETE FROM UserConfig WHERE user NOT IN (SELECT user_name FROM UserAccount)";
$query[] = "ALTER TABLE UserConfig ADD CONSTRAINT `UserConfig-FK-user` FOREIGN KEY (user) REFERENCES UserAccount (user_name) ON DELETE CASCADE";
$query[] = "DELETE FROM UserConfig WHERE varname NOT IN (SELECT varname FROM Config)";
$query[] = "ALTER TABLE UserConfig ADD KEY (varname)";
$query[] = "ALTER TABLE UserConfig ADD CONSTRAINT `UserConfig-FK-varname` FOREIGN KEY (varname) REFERENCES Config (varname) ON DELETE CASCADE";
$query[] = "ALTER TABLE Dictionary ENGINE=InnoDB";
$query[] = "ALTER TABLE Chapter ENGINE=InnoDB";
$query[] = "UPDATE Chapter SET id = 9999 WHERE id = 22";
$query[] = "UPDATE AttributeMap SET chapter_id = 9999 WHERE chapter_id = 22";
$query[] = "UPDATE Dictionary SET chapter_id = 9999 WHERE chapter_id = 22";
$query[] = "DELETE FROM Dictionary WHERE chapter_id NOT IN (SELECT id FROM Chapter)";
$query[] = "ALTER TABLE Dictionary ADD CONSTRAINT `Dictionary-FK-chapter_id` FOREIGN KEY (chapter_id) REFERENCES Chapter (id)";
$query[] = "DELETE FROM AttributeMap WHERE chapter_id NOT IN (SELECT id FROM Chapter)";
$query[] = "ALTER TABLE AttributeMap ADD KEY (chapter_id)";
$query[] = "ALTER TABLE AttributeMap ADD CONSTRAINT `AttributeMap-FK-chapter_id` FOREIGN KEY (chapter_id) REFERENCES Chapter (id)";
$query[] = "\nCREATE TABLE `CactiGraph` (\n `object_id` int(10) unsigned NOT NULL,\n `graph_id` int(10) unsigned NOT NULL,\n `caption` char(255) DEFAULT NULL,\n PRIMARY KEY (`graph_id`),\n KEY `object_id` (`object_id`),\n CONSTRAINT `CactiGraph-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE\n) ENGINE=InnoDB;\n";
$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')";
$query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('CACTI_URL','','string','yes','no','no','Cacti server base URL')";
$query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('CACTI_USERNAME','','string','yes','no','no','Cacti user account')";
$query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('CACTI_USERPASS','','string','yes','no','no','Cacti user password')";
$query[] = "UPDATE Config SET varvalue = '0.19.8' WHERE varname = 'DB_VERSION'";
break;
case '0.19.9':
$query[] = "DELETE FROM Config WHERE varname = 'HNDP_RUNNERS_LISTSRC'";
# Dismiss some overly-specific OIF types in favour of more generic counterparts.
$squeeze = array(1202 => array(25, 26), 1204 => array(27, 28), 1196 => array(22, 23), 1195 => array(20, 21, 1083));
foreach ($squeeze as $stays => $leaves) {
$csv = implode(', ', $leaves);
$query[] = "DELETE FROM PortCompat WHERE type1 IN({$csv}) OR type2 IN({$csv})";
$query[] = "INSERT IGNORE INTO PortInterfaceCompat (iif_id, oif_id) SELECT iif_id, {$stays} FROM Port WHERE type IN ({$csv})";
$query[] = "UPDATE Port SET type = {$stays} WHERE type IN({$csv})";
$query[] = "DELETE FROM PortInterfaceCompat WHERE oif_id IN({$csv})";
}
$query[] = "UPDATE Config SET varvalue = '0.19.9' WHERE varname = 'DB_VERSION'";
break;
case '0.19.10':
$query[] = "INSERT INTO `PortCompat` (`type1`, `type2`) VALUES (1603,1603)";
$query[] = "UPDATE Config SET varvalue = '0.19.10' WHERE varname = 'DB_VERSION'";
break;
case '0.19.11':
$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')";
$query[] = "INSERT INTO `Attribute` (`id`,`type`,`name`) VALUES (28,'string','Slot number')";
$query[] = 'INSERT INTO `AttributeMap` (`objtype_id`,`attr_id`,`chapter_id`) VALUES (4,28,NULL)';
$query[] = 'INSERT INTO `AttributeMap` (`objtype_id`,`attr_id`,`chapter_id`) VALUES (8,28,NULL)';
$query[] = 'INSERT INTO `AttributeMap` (`objtype_id`,`attr_id`,`chapter_id`) VALUES (798,28,NULL)';
$query[] = 'INSERT INTO `AttributeMap` (`objtype_id`,`attr_id`,`chapter_id`) VALUES (1055,28,NULL)';
$query[] = 'ALTER TABLE AttributeValue ADD COLUMN object_tid int(10) unsigned NOT NULL default 0 AFTER object_id';
$query[] = 'UPDATE AttributeValue SET object_tid = (SELECT objtype_id FROM RackObject WHERE id = object_id)';
$query[] = 'ALTER TABLE AttributeValue DROP FOREIGN KEY `AttributeValue-FK-attr_id`';
$query[] = 'ALTER TABLE AttributeValue DROP FOREIGN KEY `AttributeValue-FK-object_id`';
$query[] = 'ALTER TABLE AttributeValue ADD KEY `id-tid` (object_id, object_tid)';
$query[] = 'ALTER TABLE AttributeValue ADD KEY `object_tid-attr_id` (`object_tid`,`attr_id`)';
$query[] = 'ALTER TABLE RackObject ADD KEY `id-tid` (id, objtype_id)';
$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';
$query[] = 'ALTER TABLE AttributeValue ADD CONSTRAINT `AttributeValue-FK-map` FOREIGN KEY (`object_tid`, `attr_id`) REFERENCES `AttributeMap` (`objtype_id`, `attr_id`)';
# 0.19.9 did it right, but kept the IDs in the dictionary. This time
# the dictionary is reduced, but the procedure needs to be repeated,
# in case the user had enough time to use the wrong IDs again.
$squeeze = array(1202 => array(25, 26), 1204 => array(27, 28), 1196 => array(22, 23), 1195 => array(20, 21, 1083));
foreach ($squeeze as $stays => $leaves) {
$csv = implode(', ', $leaves);
$query[] = "DELETE FROM PortCompat WHERE type1 IN({$csv}) OR type2 IN({$csv})";
$query[] = "INSERT IGNORE INTO PortInterfaceCompat (iif_id, oif_id) SELECT iif_id, {$stays} FROM Port WHERE type IN ({$csv})";
$query[] = "UPDATE Port SET type = {$stays} WHERE type IN({$csv})";
$query[] = "DELETE FROM PortInterfaceCompat WHERE oif_id IN({$csv})";
}
$query[] = "INSERT INTO `PortCompat` (`type1`, `type2`) VALUES (1642,1642)";
$query[] = 'ALTER TABLE `EntityLink` ADD KEY `EntityLink-compound` (`parent_entity_type`,`child_entity_type`,`child_entity_id`)';
$query[] = "UPDATE Config SET varvalue = '0.19.11' WHERE varname = 'DB_VERSION'";
break;
# Batch 0.19.12 contained minor, but annoying bugs and was modified after the
# release of version 0.19.12 (which should be avoided if possible). The best
# way to resolve this particular case was to recall RackTables-0.19.12.tar.gz
# from the download area and provide RackTables-0.19.13.tar.gz containing the
# modified 0.19.12 batch.
# Batch 0.19.12 contained minor, but annoying bugs and was modified after the
# release of version 0.19.12 (which should be avoided if possible). The best
# way to resolve this particular case was to recall RackTables-0.19.12.tar.gz
# from the download area and provide RackTables-0.19.13.tar.gz containing the
# modified 0.19.12 batch.
case '0.19.12':
$query[] = "DELETE FROM Config WHERE varname IN('color_F', 'color_A', 'color_U', 'color_T', 'color_Th', 'color_Tw', 'color_Thw')";
$query[] = "INSERT INTO Chapter (id, sticky, name) VALUES (36,'no','serial console server models')";
$query[] = "INSERT INTO AttributeMap (objtype_id, attr_id, chapter_id) VALUES (1644, 1, NULL), (1644, 2, 36), (1644, 3, NULL)";
$query[] = "UPDATE Config SET varvalue = CONCAT(varvalue, '; 10=1588') WHERE varname = 'DEFAULT_PORT_OIF_IDS' AND 0 = INSTR(varvalue, '10=') ";
$query[] = "INSERT INTO PortInterfaceCompat VALUES (10,1663), (10,1664)";
$query[] = "INSERT INTO PortCompat VALUES (1588,1588), (1661,1661), (1663,1663), (1664,1664)";
$query[] = "INSERT INTO PortInnerInterface (id, iif_name) VALUES (11, 'CFP')";
$query[] = "INSERT INTO PortInterfaceCompat (iif_id, oif_id) VALUES (11,1668),(11,1669),(11,1670),(11,1671)";
$query[] = "INSERT INTO PortCompat (type1, type2) VALUES (1668,1668), (1669,1669), (1670,1670), (1671,1671)";
$query[] = "UPDATE Config SET varvalue = CONCAT(varvalue, '; 11=1668') WHERE varname = 'DEFAULT_PORT_OIF_IDS'";
$query[] = "INSERT INTO Chapter (id, sticky, name) VALUES (37, 'no', 'wireless OS type')";
$query[] = "INSERT INTO AttributeMap (objtype_id, attr_id, chapter_id) VALUES (965, 4, 37)";
$query[] = "UPDATE Config SET varvalue = '0.19.12' WHERE varname = 'DB_VERSION'";
break;
case '0.19.13':
// add the date attribute type
$query[] = "ALTER TABLE `Attribute` CHANGE COLUMN `type` `type` enum('string','uint','float','dict','date') DEFAULT NULL";
$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')";
$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')";
// port over existing fields to new date attr type
$query[] = "UPDATE Attribute SET type='date' WHERE id IN (21,22,24)";
$query[] = "UPDATE AttributeValue SET uint_value=UNIX_TIMESTAMP(STR_TO_DATE(string_value, '%m/%d/%Y')) WHERE attr_id IN(21,22,24)";
// some config variables should be configurable in per-user basis
$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')";
$query[] = "UPDATE Config SET varvalue = '0.19.13' WHERE varname = 'DB_VERSION'";
break;
case '0.19.14':
$query[] = "ALTER TABLE AttributeValue CHANGE COLUMN `object_id` `object_id` INT(10) UNSIGNED NOT NULL";
$query[] = "ALTER TABLE AttributeValue CHANGE COLUMN `attr_id` `attr_id` INT(10) UNSIGNED NOT NULL";
$query[] = "ALTER TABLE AttributeValue ADD PRIMARY KEY (`object_id`, `attr_id`), DROP INDEX `object_id`";
$query[] = "ALTER TABLE Dictionary ADD COLUMN `dict_sticky` enum('yes','no') DEFAULT 'no' AFTER `dict_key`";
$query[] = "UPDATE Dictionary SET dict_sticky = 'yes' WHERE dict_key < 50000";
$query[] = "ALTER TABLE Dictionary ADD UNIQUE KEY dict_unique (chapter_id, dict_value, dict_sticky)";
$query[] = "ALTER TABLE Dictionary DROP KEY `chap_to_val`";
$query[] = "UPDATE Config SET varvalue = '0.19.14' WHERE varname = 'DB_VERSION'";
break;
case '0.20.0':
$query[] = "\nCREATE TABLE `PortLog` (\n `id` int(10) unsigned NOT NULL AUTO_INCREMENT,\n `port_id` int(10) unsigned NOT NULL,\n `date` datetime NOT NULL,\n `user` varchar(64) NOT NULL,\n `message` text NOT NULL,\n PRIMARY KEY (`id`),\n KEY `port_id-date` (`port_id`,`date`),\n CONSTRAINT `PortLog_ibfk_1` FOREIGN KEY (`port_id`) REFERENCES `Port` (`id`) ON DELETE CASCADE\n) ENGINE=InnoDB;\n";
$query[] = "\nCREATE TABLE `IPv4Log` (\n `id` int(10) NOT NULL AUTO_INCREMENT,\n `ip` int(10) unsigned NOT NULL,\n `date` datetime NOT NULL,\n `user` varchar(64) NOT NULL,\n `message` text NOT NULL,\n PRIMARY KEY (`id`),\n KEY `ip-date` (`ip`,`date`)\n) ENGINE=InnoDB;\n";
$query[] = "\nCREATE TABLE `IPv6Log` (\n `id` int(10) NOT NULL AUTO_INCREMENT,\n `ip` binary(16) NOT NULL,\n `date` datetime NOT NULL,\n `user` varchar(64) NOT NULL,\n `message` text NOT NULL,\n PRIMARY KEY (`id`),\n KEY `ip-date` (`ip`,`date`)\n) ENGINE=InnoDB;\n";
$query[] = "ALTER TABLE `FileLink` MODIFY COLUMN `entity_type` ENUM('ipv4net','ipv4rspool','ipv4vs','ipv6net','location','object','rack','user') NOT NULL DEFAULT 'object'";
$query[] = "ALTER TABLE `TagStorage` MODIFY COLUMN `entity_realm` ENUM('file','ipv4net','ipv4rspool','ipv4vs','ipv6net','location','object','rack','user','vst') NOT NULL default 'object'";
$query[] = "ALTER TABLE `TagStorage` ADD COLUMN `user` char(64) DEFAULT NULL, ADD COLUMN `date` datetime DEFAULT NULL";
// Rename object tables and keys, 'name' no longer needs to be unique
$dbxlink->query('ALTER TABLE `RackObject` RENAME TO `Object`');
$dbxlink->query('ALTER TABLE `RackObjectHistory` RENAME TO `ObjectHistory`');
$dbxlink->query('ALTER TABLE `Object` DROP KEY `name`');
$query[] = 'ALTER TABLE `Object` DROP KEY `RackObject_asset_no`';
$query[] = 'ALTER TABLE `Object` ADD UNIQUE KEY `asset_no` (`asset_no`)';
$query[] = 'ALTER TABLE `Object` ADD KEY `type_id` (`objtype_id`,`id`)';
$query[] = 'ALTER TABLE `ObjectHistory` DROP FOREIGN KEY `RackObjectHistory-FK-object_id`';
$query[] = 'ALTER TABLE `ObjectHistory` ADD CONSTRAINT `ObjectHistory-FK-object_id` FOREIGN KEY (`id`) REFERENCES `Object` (`id`) ON DELETE CASCADE';
$query[] = 'ALTER TABLE `RackSpace` DROP FOREIGN KEY `RackSpace-FK-rack_id`';
// Rack height is now an attribute
$query[] = "INSERT INTO `Attribute` (`id`,`type`,`name`) VALUES (27,'uint','Height, units')";
$query[] = 'INSERT INTO `AttributeMap` (`objtype_id`,`attr_id`,`chapter_id`) VALUES (1560,27,NULL)';
// Racks are now sorted using an attribute
$query[] = "INSERT INTO `Attribute` (`id`,`type`,`name`) VALUES (29,'uint','Sort order')";
$query[] = 'INSERT INTO `AttributeMap` (`objtype_id`,`attr_id`,`chapter_id`) VALUES (1560,29,NULL)';
// Relate 'contact person' with locations
$query[] = 'INSERT INTO `AttributeMap` (`objtype_id`,`attr_id`,`chapter_id`) VALUES (1562,14,NULL)';
// Allow relationships between racks/rows/locations
$query[] = "ALTER TABLE `EntityLink` MODIFY COLUMN `parent_entity_type` ENUM('ipv4net','ipv4rspool','ipv4vs','ipv6net','location','object','rack','row','user') NOT NULL";
$query[] = "ALTER TABLE `EntityLink` MODIFY COLUMN `child_entity_type` ENUM('file','location','object','rack','row') NOT NULL";
// Turn rows into objects
$result = $dbxlink->query('SELECT * FROM RackRow');
$rows = $result->fetchAll(PDO::FETCH_ASSOC);
unset($result);
foreach ($rows as $row) {
$prepared = $dbxlink->prepare('INSERT INTO `Object` (`name`,`objtype_id`) VALUES (?,?)');
$prepared->execute(array($row['name'], 1561));
$row_id = $dbxlink->lastInsertId();
// Turn all racks in this row into objects
$result = $dbxlink->query("SELECT id, name, height, comment FROM Rack WHERE row_id={$row['id']} ORDER BY name");
$racks = $result->fetchAll(PDO::FETCH_ASSOC);
unset($result);
$sort_order = 1;
foreach ($racks as $rack) {
// Add the rack as an object, set the height and sort order as attributes, link the rack to the row,
// update rackspace, tags and files to reflect new rack_id, move history
$prepared = $dbxlink->prepare('INSERT INTO `Object` (`name`,`objtype_id`,`comment`) VALUES (?,?,?)');
$prepared->execute(array($rack['name'], 1560, $rack['comment']));
$rack_id = $dbxlink->lastInsertId();
$query[] = "INSERT INTO `AttributeValue` (`object_id`,`object_tid`,`attr_id`,`uint_value`) VALUES ({$rack_id},1560,27,{$rack['height']})";
$query[] = "INSERT INTO `AttributeValue` (`object_id`,`object_tid`,`attr_id`,`uint_value`) VALUES ({$rack_id},1560,29,{$sort_order})";
$query[] = "INSERT INTO `EntityLink` (`parent_entity_type`,`parent_entity_id`,`child_entity_type`,`child_entity_id`) VALUES ('row',{$row_id},'rack',{$rack_id})";
$query[] = "UPDATE `RackSpace` SET `rack_id`={$rack_id} WHERE `rack_id`={$rack['id']}";
$query[] = "UPDATE `Atom` SET `rack_id`={$rack_id} WHERE `rack_id`={$rack['id']}";
$query[] = "UPDATE `TagStorage` SET `entity_id`={$rack_id} WHERE `entity_realm`='rack' AND `entity_id`={$rack['id']}";
$query[] = "UPDATE `FileLink` SET `entity_id`={$rack_id} WHERE `entity_type`='rack' AND `entity_id`={$rack['id']}";
$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']}";
$sort_order++;
}
}
$query[] = 'ALTER TABLE `RackSpace` ADD CONSTRAINT `RackSpace-FK-rack_id` FOREIGN KEY (`rack_id`) REFERENCES `Object` (`id`)';
$query[] = 'DROP TABLE `Rack`';
$query[] = 'DROP TABLE `RackRow`';
$query[] = 'DROP TABLE `RackHistory`';
$query[] = "\nCREATE TABLE `RackThumbnail` (\n `rack_id` int(10) unsigned NOT NULL,\n `thumb_data` blob,\n UNIQUE KEY `rack_id` (`rack_id`),\n CONSTRAINT `RackThumbnail-FK-rack_id` FOREIGN KEY (`rack_id`) REFERENCES `Object` (`id`) ON DELETE CASCADE\n) ENGINE=InnoDB\n";
$query[] = "\nCREATE VIEW `Location` AS SELECT O.id, O.name, O.has_problems, O.comment, P.id AS parent_id, P.name AS parent_name\nFROM `Object` O\nLEFT JOIN (\n `Object` P INNER JOIN `EntityLink` EL\n ON EL.parent_entity_id = P.id AND P.objtype_id = 1562 AND EL.parent_entity_type = 'location' AND EL.child_entity_type = 'location'\n) ON EL.child_entity_id = O.id\nWHERE O.objtype_id = 1562\n";
$query[] = "\nCREATE VIEW `Row` AS SELECT O.id, O.name, L.id AS location_id, L.name AS location_name\n FROM `Object` O\n LEFT JOIN `EntityLink` EL ON O.id = EL.child_entity_id AND EL.parent_entity_type = 'location' AND EL.child_entity_type = 'row'\n LEFT JOIN `Object` L ON EL.parent_entity_id = L.id AND L.objtype_id = 1562\n WHERE O.objtype_id = 1561\n";
$query[] = "\nCREATE VIEW `Rack` AS SELECT O.id, O.name AS name, O.asset_no, O.has_problems, O.comment,\n AV_H.uint_value AS height,\n AV_S.uint_value AS sort_order,\n RT.thumb_data,\n R.id AS row_id,\n R.name AS row_name\n FROM `Object` O\n LEFT JOIN `AttributeValue` AV_H ON O.id = AV_H.object_id AND AV_H.attr_id = 27\n LEFT JOIN `AttributeValue` AV_S ON O.id = AV_S.object_id AND AV_S.attr_id = 29\n LEFT JOIN `RackThumbnail` RT ON O.id = RT.rack_id\n LEFT JOIN `EntityLink` EL ON O.id = EL.child_entity_id AND EL.parent_entity_type = 'row' AND EL.child_entity_type = 'rack'\n INNER JOIN `Object` R ON R.id = EL.parent_entity_id\n WHERE O.objtype_id = 1560\n";
$query[] = "\nCREATE VIEW `RackObject` AS SELECT id, name, label, objtype_id, asset_no, has_problems, comment FROM `Object`\n WHERE `objtype_id` NOT IN (1560, 1561, 1562)\n";
$query[] = "UPDATE `Chapter` SET `name` = 'ObjectType' WHERE `id` = 1";
$query[] = "DELETE FROM RackSpace WHERE object_id IS NULL AND state = 'T'";
$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')";
$query[] = "UPDATE `Config` SET is_userdefined='yes' WHERE varname='PROXIMITY_RANGE'";
$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')";
$query[] = "ALTER TABLE `IPv4LB` MODIFY `prio` varchar(255) DEFAULT NULL";
$query[] = "ALTER TABLE `IPv4Address` ADD COLUMN `comment` char(255) NOT NULL default '' AFTER `name`";
$query[] = "ALTER TABLE `IPv6Address` ADD COLUMN `comment` char(255) NOT NULL default '' AFTER `name`";
// change IP address format of IPv4VS and IPv4RS tables
convertSLBTablesToBinIPs();
// do not allow NULL allocation type
$query[] = "ALTER TABLE `IPv4Allocation` MODIFY `type` enum('regular','shared','virtual','router') NOT NULL DEFAULT 'regular'";
$query[] = "ALTER TABLE `IPv6Allocation` MODIFY `type` enum('regular','shared','virtual','router') NOT NULL DEFAULT 'regular'";
$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')";
// update some config variables which changed their defaults in this verison
replaceConfigVarValue('SHOW_LAST_TAB', 'yes');
replaceConfigVarValue('IPV4_TREE_SHOW_USAGE', 'no');
replaceConfigVarValue('IPV4LB_LISTSRC', 'false', '{$typeid_4}');
replaceConfigVarValue('FILTER_DEFAULT_ANDOR', 'and');
replaceConfigVarValue('FILTER_SUGGEST_EXTRA', 'yes');
replaceConfigVarValue('IPV4_TREE_RTR_AS_CELL', 'no');
replaceConfigVarValue('SSH_OBJS_LISTSRC', 'false', 'none');
replaceConfigVarValue('TELNET_OBJS_LISTSRC', 'false', 'none');
$query[] = "UPDATE Config SET varvalue = '0.20.0' WHERE varname = 'DB_VERSION'";
break;
case '0.20.1':
// some HW types were moved from the 'Network switch' chapter to the 'Network chassis' chapter
// change the type of affected objects to 'Network chassis'
$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))";
// new 'management interface' object type
$query[] = "INSERT INTO `Chapter` (`id`,`sticky`,`name`) VALUES (38,'no','management interface type')";
$query[] = "INSERT INTO `Attribute` (`id`,`type`,`name`) VALUES (30,'dict','Mgmt type')";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`,`attr_id`,`chapter_id`) VALUES (1787,3,NULL),(1787,14,NULL),(1787,30,38)";
$query[] = "UPDATE `Config` SET varvalue = CONCAT(varvalue, ' or {\$typeid_1787}') WHERE varname = 'IPV4OBJ_LISTSRC'";
$query[] = "INSERT INTO Config VALUES ('8021Q_EXTSYNC_LISTSRC','false','string','yes','no','no','List source: objects with extended 802.1Q sync')";
// constraints to prevent orphan records
$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";
$query[] = "DELETE FROM `MountOperation` WHERE old_molecule_id NOT IN (SELECT id FROM `Molecule`) OR new_molecule_id NOT IN (SELECT id FROM `Molecule`)";
$query[] = "ALTER TABLE `Atom` ADD CONSTRAINT `Atom-FK-molecule_id` FOREIGN KEY (`molecule_id`) REFERENCES `Molecule` (`id`) ON DELETE CASCADE";
$query[] = "ALTER TABLE `Atom` ADD CONSTRAINT `Atom-FK-rack_id` FOREIGN KEY (`rack_id`) REFERENCES `Object` (`id`) ON DELETE CASCADE";
$query[] = "ALTER TABLE `MountOperation` ADD CONSTRAINT `MountOperation-FK-old_molecule_id` FOREIGN KEY (`old_molecule_id`) REFERENCES `Molecule` (`id`) ON DELETE CASCADE";
$query[] = "ALTER TABLE `MountOperation` ADD CONSTRAINT `MountOperation-FK-new_molecule_id` FOREIGN KEY (`new_molecule_id`) REFERENCES `Molecule` (`id`) ON DELETE CASCADE";
# multiple Cacti servers
$query[] = "\nCREATE TABLE `CactiServer` (\n `id` int(10) unsigned NOT NULL auto_increment,\n `base_url` char(255) DEFAULT NULL,\n `username` char(64) DEFAULT NULL,\n `password` char(64) DEFAULT NULL,\n PRIMARY KEY (`id`)\n) ENGINE=InnoDB\n";
$query[] = "ALTER TABLE CactiGraph ADD COLUMN server_id int(10) unsigned NOT NULL AFTER object_id";
$result = $dbxlink->query('SELECT COUNT(*) AS cnt FROM CactiGraph');
$row = $result->fetch(PDO::FETCH_ASSOC);
unset($result);
$result = $dbxlink->query("SELECT varvalue FROM Config WHERE varname = 'CACTI_URL'");
$cacti_url_row = $result->fetch(PDO::FETCH_ASSOC);
unset($result);
if ($row['cnt'] != 0 || is_array($cacti_url_row) && strlen($cacti_url_row['varvalue'])) {
$query[] = "INSERT INTO CactiServer (id) VALUES (1)";
$query[] = "UPDATE CactiServer SET base_url = (SELECT varvalue FROM Config WHERE varname = 'CACTI_URL') WHERE id = 1";
$query[] = "UPDATE CactiServer SET username = (SELECT varvalue FROM Config WHERE varname = 'CACTI_USERNAME') WHERE id = 1";
$query[] = "UPDATE CactiServer SET password = (SELECT varvalue FROM Config WHERE varname = 'CACTI_USERPASS') WHERE id = 1";
$query[] = "UPDATE CactiGraph SET server_id = 1";
}
$query[] = "ALTER TABLE CactiGraph DROP PRIMARY KEY";
$query[] = "ALTER TABLE CactiGraph ADD PRIMARY KEY (server_id, graph_id)";
$query[] = "ALTER TABLE CactiGraph ADD KEY (graph_id)";
$query[] = "ALTER TABLE CactiGraph ADD CONSTRAINT `CactiGraph-FK-server_id` FOREIGN KEY (server_id) REFERENCES CactiServer (id)";
$query[] = "DELETE FROM Config WHERE varname IN('CACTI_URL', 'CACTI_USERNAME', 'CACTI_USERPASS')";
$query[] = "UPDATE Config SET varvalue = '0.20.1' WHERE varname = 'DB_VERSION'";
break;
case '0.20.2':
$query[] = "ALTER TABLE TagStorage ADD COLUMN tag_is_assignable ENUM('yes', 'no') NOT NULL default 'yes' AFTER tag_id";
$query[] = "ALTER TABLE TagStorage ADD KEY `tag_id-tag_is_assignable` (tag_id, tag_is_assignable)";
$query[] = "ALTER TABLE TagTree ADD COLUMN is_assignable ENUM('yes', 'no') NOT NULL default 'yes' AFTER parent_id";
$query[] = "ALTER TABLE TagTree ADD KEY `id-is_assignable` (id, is_assignable)";
$query[] = "ALTER TABLE TagStorage DROP FOREIGN KEY `TagStorage-FK-tag_id`";
$query[] = "ALTER TABLE TagStorage ADD CONSTRAINT `TagStorage-FK-TagTree` FOREIGN KEY (tag_id, tag_is_assignable) REFERENCES TagTree (id, is_assignable)";
$query[] = "UPDATE UserAccount SET user_realname = NULL WHERE user_realname = ''";
$query[] = "UPDATE Object SET comment = NULL WHERE comment = ''";
$query[] = "\nCREATE TABLE `MuninServer` (\n `id` int(10) unsigned NOT NULL auto_increment,\n `base_url` char(255) DEFAULT NULL,\n PRIMARY KEY (`id`)\n) ENGINE=InnoDB\n";
$query[] = "\nCREATE TABLE `MuninGraph` (\n `object_id` int(10) unsigned NOT NULL,\n `server_id` int(10) unsigned NOT NULL,\n `graph` char(255) NOT NULL,\n `caption` char(255) DEFAULT NULL,\n PRIMARY KEY (`object_id`,`server_id`,`graph`),\n KEY `server_id` (`server_id`),\n KEY `graph` (`graph`),\n CONSTRAINT `MuninGraph-FK-server_id` FOREIGN KEY (`server_id`) REFERENCES `MuninServer` (`id`),\n CONSTRAINT `MuninGraph-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `Object` (`id`) ON DELETE CASCADE\n) ENGINE=InnoDB\n";
$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')";
$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')";
$query[] = "ALTER TABLE VLANIPv4 ADD UNIQUE `network-domain-vlan` (ipv4net_id, domain_id, vlan_id)";
$query[] = "ALTER TABLE VLANIPv4 DROP KEY `network-domain`";
$query[] = "ALTER TABLE VLANIPv6 ADD UNIQUE `network-domain-vlan` (ipv6net_id, domain_id, vlan_id)";
$query[] = "ALTER TABLE VLANIPv6 DROP KEY `network-domain`";
$query[] = "UPDATE Config SET varvalue = '0.20.2' WHERE varname = 'DB_VERSION'";
break;
case '0.20.3':
$query[] = "UPDATE Config SET varvalue = '0.20.3' WHERE varname = 'DB_VERSION'";
break;
case '0.20.4':
$query[] = "ALTER TABLE `FileLink` MODIFY COLUMN `entity_type` ENUM('ipv4net','ipv4rspool','ipv4vs','ipv6net','location','object','rack','row','user') NOT NULL DEFAULT 'object'";
$query[] = "ALTER TABLE `RackSpace` MODIFY COLUMN `state` ENUM('A','U','T') NOT NULL default 'A'";
$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')";
$query[] = "UPDATE `Config` SET varvalue = CONCAT(varvalue, ' or {\$typeid_965}') WHERE varname = 'IPV4OBJ_LISTSRC'";
$query[] = "UPDATE AttributeValue INNER JOIN AttributeMap USING (attr_id) SET AttributeValue.uint_value = 1572 WHERE chapter_id = 12 AND uint_value = 162";
$query[] = "UPDATE AttributeValue INNER JOIN AttributeMap USING (attr_id) SET AttributeValue.uint_value = 1710 WHERE chapter_id = 12 AND uint_value = 163";
$query[] = "UPDATE Config SET varvalue = '%Y-%m-%d', description='PHP strftime() format to use for date output' WHERE varname = 'DATETIME_FORMAT'";
$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')";
$query[] = "UPDATE Config SET varvalue = '0.20.4' WHERE varname = 'DB_VERSION'";
break;
case '0.20.5':
$query[] = "\nCREATE OR REPLACE VIEW `Rack` AS SELECT O.id, O.name AS name, O.asset_no, O.has_problems, O.comment,\n AV_H.uint_value AS height,\n AV_S.uint_value AS sort_order,\n RT.thumb_data,\n R.id AS row_id,\n R.name AS row_name,\n L.id AS location_id,\n L.name AS location_name\n FROM `Object` O\n LEFT JOIN `AttributeValue` AV_H ON O.id = AV_H.object_id AND AV_H.attr_id = 27\n LEFT JOIN `AttributeValue` AV_S ON O.id = AV_S.object_id AND AV_S.attr_id = 29\n LEFT JOIN `RackThumbnail` RT ON O.id = RT.rack_id\n LEFT JOIN `EntityLink` RL ON O.id = RL.child_entity_id AND RL.parent_entity_type = 'row' AND RL.child_entity_type = 'rack'\n INNER JOIN `Object` R ON R.id = RL.parent_entity_id\n LEFT JOIN `EntityLink` LL ON R.id = LL.child_entity_id AND LL.parent_entity_type = 'location' AND LL.child_entity_type = 'row'\n LEFT JOIN `Object` L ON L.id = LL.parent_entity_id\n WHERE O.objtype_id = 1560\n";
// prevent some AttributeMap entries from being deleted
$query[] = "ALTER TABLE AttributeMap ADD COLUMN sticky enum('yes','no') default 'no'";
$query[] = "UPDATE AttributeMap SET sticky = 'yes' WHERE objtype_id = 4 AND attr_id IN (26,28)";
// Server -> Hypervisor, Slot number
$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
$query[] = "UPDATE AttributeMap SET sticky = 'yes' WHERE objtype_id = 798 AND attr_id = 28";
// Network security -> Slot number
$query[] = "UPDATE AttributeMap SET sticky = 'yes' WHERE objtype_id = 1055 AND attr_id = 28";
// FC switch -> Slot number
$query[] = "UPDATE AttributeMap SET sticky = 'yes' WHERE objtype_id = 1560 AND attr_id IN (27,29)";
// Rack -> Height, Sort order
$query[] = "UPDATE AttributeMap SET sticky = 'yes' WHERE objtype_id = 1787 AND attr_id = 30";
// Management interface -> Mgmt type
$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')";
// SLB v2 tables
$query[] = "\nCREATE TABLE `VS` (\n `id` int(10) unsigned NOT NULL AUTO_INCREMENT,\n `name` char(255) DEFAULT NULL,\n `vsconfig` text,\n `rsconfig` text,\n PRIMARY KEY (`id`)\n) ENGINE=InnoDB\n";
$query[] = "\nCREATE TABLE `VSIPs` (\n `vs_id` int(10) unsigned NOT NULL,\n `vip` varbinary(16) NOT NULL,\n `vsconfig` text,\n `rsconfig` text,\n PRIMARY KEY (`vs_id`,`vip`),\n KEY `vip` (`vip`),\n CONSTRAINT `VSIPs-vs_id` FOREIGN KEY (`vs_id`) REFERENCES `VS` (`id`) ON DELETE CASCADE\n) ENGINE=InnoDB\n";
$query[] = "\nCREATE TABLE `VSPorts` (\n `vs_id` int(10) unsigned NOT NULL,\n `proto` enum('TCP','UDP','MARK') NOT NULL,\n `vport` int(10) unsigned NOT NULL,\n `vsconfig` text,\n `rsconfig` text,\n PRIMARY KEY (`vs_id`,`proto`,`vport`),\n KEY `proto-vport` (`proto`,`vport`),\n CONSTRAINT `VS-vs_id` FOREIGN KEY (`vs_id`) REFERENCES `VS` (`id`) ON DELETE CASCADE\n) ENGINE=InnoDB\n";
$query[] = "\nCREATE TABLE `VSEnabledIPs` (\n `object_id` int(10) unsigned NOT NULL,\n `vs_id` int(10) unsigned NOT NULL,\n `vip` varbinary(16) NOT NULL,\n `rspool_id` int(10) unsigned NOT NULL,\n `prio` varchar(255) DEFAULT NULL,\n `vsconfig` text,\n `rsconfig` text,\n PRIMARY KEY (`object_id`,`vs_id`,`vip`,`rspool_id`),\n KEY `vip` (`vip`),\n KEY `VSEnabledIPs-FK-vs_id-vip` (`vs_id`,`vip`),\n KEY `VSEnabledIPs-FK-rspool_id` (`rspool_id`),\n CONSTRAINT `VSEnabledIPs-FK-rspool_id` FOREIGN KEY (`rspool_id`) REFERENCES `IPv4RSPool` (`id`) ON DELETE CASCADE,\n CONSTRAINT `VSEnabledIPs-FK-vs_id-vip` FOREIGN KEY (`vs_id`, `vip`) REFERENCES `VSIPs` (`vs_id`, `vip`) ON DELETE CASCADE\n) ENGINE=InnoDB\n";
$query[] = "\nCREATE TABLE `VSEnabledPorts` (\n `object_id` int(10) unsigned NOT NULL,\n `vs_id` int(10) unsigned NOT NULL,\n `proto` enum('TCP','UDP','MARK') NOT NULL,\n `vport` int(10) unsigned NOT NULL,\n `rspool_id` int(10) unsigned NOT NULL,\n `vsconfig` text,\n `rsconfig` text,\n PRIMARY KEY (`object_id`,`vs_id`,`proto`,`vport`,`rspool_id`),\n KEY `VSEnabledPorts-FK-vs_id-proto-vport` (`vs_id`,`proto`,`vport`),\n KEY `VSEnabledPorts-FK-rspool_id` (`rspool_id`),\n CONSTRAINT `VSEnabledPorts-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `Object` (`id`) ON DELETE CASCADE,\n CONSTRAINT `VSEnabledPorts-FK-rspool_id` FOREIGN KEY (`rspool_id`) REFERENCES `IPv4RSPool` (`id`) ON DELETE CASCADE,\n CONSTRAINT `VSEnabledPorts-FK-vs_id-proto-vport` FOREIGN KEY (`vs_id`, `proto`, `vport`) REFERENCES `VSPorts` (`vs_id`, `proto`, `vport`) ON DELETE CASCADE\n) ENGINE=InnoDB\n";
$query[] = "ALTER TABLE `EntityLink` MODIFY COLUMN `parent_entity_type` ENUM('ipv4net','ipv4rspool','ipv4vs','ipv6net','location','object','rack','row','user') NOT NULL";
$query[] = "ALTER TABLE `FileLink` MODIFY COLUMN `entity_type` ENUM('ipv4net','ipv4rspool','ipv4vs','ipv6net','location','object','rack','row','user') NOT NULL DEFAULT 'object'";
$query[] = "ALTER TABLE `TagStorage` MODIFY COLUMN `entity_realm` ENUM('file','ipv4net','ipv4rspool','ipv4vs','ipvs','ipv6net','location','object','rack','user','vst') NOT NULL DEFAULT 'object'";
$query[] = "ALTER TABLE `UserConfig` DROP FOREIGN KEY `UserConfig-FK-user`";
$query[] = "UPDATE Config SET varvalue = '0.20.5' WHERE varname = 'DB_VERSION'";
break;
case '0.20.6':
// one HW type was moved from the 'Network switch' chapter to the 'Network chassis' chapter
// change the type of affected objects to 'Network chassis'
$query[] = "UPDATE `Object` SET objtype_id = 1503 WHERE id IN (SELECT object_id FROM `AttributeValue` WHERE attr_id = 2 and uint_value = 935)";
// convert values of old 'TELNET_OBJS_LISTSRC' 'SSH_OBJS_LISTSRC', 'RDP_OBJS_LISTSRC' variables into 'MGMT_PROTOS'
$query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('MGMT_PROTOS','ssh: {\$typeid_4}; telnet: {\$typeid_8}','string','yes','no','yes','Mapping of management protocol to devices')";
if ('' !== ($mgmt_converted_var = convertMgmtConfigVars())) {
$query[] = "UPDATE `Config` SET varvalue = '" . mysql_escape_string($mgmt_converted_var) . "' WHERE varname = 'MGMT_PROTOS'";
}
// TODO: call of deprecated function
$query[] = "DELETE `Config`,`UserConfig` FROM `Config` LEFT JOIN `UserConfig` USING (`varname`) WHERE `Config`.`varname` IN ('TELNET_OBJS_LISTSRC', 'SSH_OBJS_LISTSRC', 'RDP_OBJS_LISTSRC')";
$query[] = "ALTER TABLE `VSEnabledIPs` ADD CONSTRAINT `VSEnabledIPs-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `Object` (`id`) ON DELETE CASCADE";
$query[] = "DELETE FROM Config WHERE varname = 'PORTS_PER_ROW'";
$query[] = "UPDATE Config SET varvalue = '0.20.6' WHERE varname = 'DB_VERSION'";
break;
case '0.20.7':
if (!isInnoDBSupported()) {
showUpgradeError('Cannot upgrade because triggers are not supported by your MySQL server.', __FUNCTION__);
die;
}
// for the UNIQUE key to work, portb needs to be > porta
$result = $dbxlink->query('SELECT porta, portb FROM `Link` WHERE porta > portb');
$links = $result->fetchAll(PDO::FETCH_ASSOC);
unset($result);
foreach ($links as $link) {
$query[] = "UPDATE `Link` SET `porta`={$link['portb']}, `portb`={$link['porta']} WHERE `porta`={$link['porta']} AND `portb`={$link['portb']}";
}
// add triggers
$query[] = "\nCREATE TRIGGER `EntityLink-before-insert` BEFORE INSERT ON `EntityLink` FOR EACH ROW\nEntityLinkTrigger:BEGIN\n DECLARE parent_objtype, child_objtype, count INTEGER;\n\n # forbid linking an entity to itself\n IF NEW.parent_entity_type = NEW.child_entity_type AND NEW.parent_entity_id = NEW.child_entity_id THEN\n SET NEW.parent_entity_id = NULL;\n LEAVE EntityLinkTrigger;\n END IF;\n\n # in some scenarios, only one parent is allowed\n CASE CONCAT(NEW.parent_entity_type, '.', NEW.child_entity_type)\n WHEN 'location.location' THEN\n SELECT COUNT(*) INTO count FROM EntityLink WHERE parent_entity_type = 'location' AND child_entity_type = 'location' AND child_entity_id = NEW.child_entity_id;\n WHEN 'location.row' THEN\n SELECT COUNT(*) INTO count FROM EntityLink WHERE parent_entity_type = 'location' AND child_entity_type = 'row' AND child_entity_id = NEW.child_entity_id;\n WHEN 'row.rack' THEN\n SELECT COUNT(*) INTO count FROM EntityLink WHERE parent_entity_type = 'row' AND child_entity_type = 'rack' AND child_entity_id = NEW.child_entity_id;\n ELSE\n # some other scenario, assume it is valid\n SET count = 0;\n END CASE; \n IF count > 0 THEN\n SET NEW.parent_entity_id = NULL;\n LEAVE EntityLinkTrigger;\n END IF;\n\n IF NEW.parent_entity_type = 'object' AND NEW.child_entity_type = 'object' THEN\n # lock objects to prevent concurrent link establishment\n SELECT objtype_id INTO parent_objtype FROM Object WHERE id = NEW.parent_entity_id FOR UPDATE;\n SELECT objtype_id INTO child_objtype FROM Object WHERE id = NEW.child_entity_id FOR UPDATE;\n\n # only permit the link if object types are compatibile\n SELECT COUNT(*) INTO count FROM ObjectParentCompat WHERE parent_objtype_id = parent_objtype AND child_objtype_id = child_objtype;\n IF count = 0 THEN\n SET NEW.parent_entity_id = NULL;\n END IF;\n END IF;\nEND;\n";
$query[] = "\nCREATE TRIGGER `EntityLink-before-update` BEFORE UPDATE ON `EntityLink` FOR EACH ROW\nEntityLinkTrigger:BEGIN\n DECLARE parent_objtype, child_objtype, count INTEGER;\n\n # forbid linking an entity to itself\n IF NEW.parent_entity_type = NEW.child_entity_type AND NEW.parent_entity_id = NEW.child_entity_id THEN\n SET NEW.parent_entity_id = NULL;\n LEAVE EntityLinkTrigger;\n END IF;\n\n # in some scenarios, only one parent is allowed\n CASE CONCAT(NEW.parent_entity_type, '.', NEW.child_entity_type)\n WHEN 'location.location' THEN\n SELECT COUNT(*) INTO count FROM EntityLink WHERE parent_entity_type = 'location' AND child_entity_type = 'location' AND child_entity_id = NEW.child_entity_id AND id != NEW.id;\n WHEN 'location.row' THEN\n SELECT COUNT(*) INTO count FROM EntityLink WHERE parent_entity_type = 'location' AND child_entity_type = 'row' AND child_entity_id = NEW.child_entity_id AND id != NEW.id;\n WHEN 'row.rack' THEN\n SELECT COUNT(*) INTO count FROM EntityLink WHERE parent_entity_type = 'row' AND child_entity_type = 'rack' AND child_entity_id = NEW.child_entity_id AND id != NEW.id;\n ELSE\n # some other scenario, assume it is valid\n SET count = 0;\n END CASE; \n IF count > 0 THEN\n SET NEW.parent_entity_id = NULL;\n LEAVE EntityLinkTrigger;\n END IF;\n\n IF NEW.parent_entity_type = 'object' AND NEW.child_entity_type = 'object' THEN\n # lock objects to prevent concurrent link establishment\n SELECT objtype_id INTO parent_objtype FROM Object WHERE id = NEW.parent_entity_id FOR UPDATE;\n SELECT objtype_id INTO child_objtype FROM Object WHERE id = NEW.child_entity_id FOR UPDATE;\n\n # only permit the link if object types are compatibile\n SELECT COUNT(*) INTO count FROM ObjectParentCompat WHERE parent_objtype_id = parent_objtype AND child_objtype_id = child_objtype;\n IF count = 0 THEN\n SET NEW.parent_entity_id = NULL;\n END IF;\n END IF;\nEND;\n";
$link_trigger_body = <<<ENDOFTRIGGER
LinkTrigger:BEGIN
DECLARE tmp, porta_type, portb_type, count INTEGER;
IF NEW.porta = NEW.portb THEN
# forbid connecting a port to itself
SET NEW.porta = NULL;
LEAVE LinkTrigger;
ELSEIF NEW.porta > NEW.portb THEN
# force porta < portb
SET tmp = NEW.porta;
SET NEW.porta = NEW.portb;
SET NEW.portb = tmp;
END IF;
# lock ports to prevent concurrent link establishment
SELECT type INTO porta_type FROM Port WHERE id = NEW.porta FOR UPDATE;
SELECT type INTO portb_type FROM Port WHERE id = NEW.portb FOR UPDATE;
# only permit the link if ports are compatibile
SELECT COUNT(*) INTO count FROM PortCompat WHERE (type1 = porta_type AND type2 = portb_type) OR (type1 = portb_type AND type2 = porta_type);
IF count = 0 THEN
SET NEW.porta = NULL;
END IF;
END;
ENDOFTRIGGER;
$query[] = "CREATE TRIGGER `Link-before-insert` BEFORE INSERT ON `Link` FOR EACH ROW {$link_trigger_body}";
$query[] = "CREATE TRIGGER `Link-before-update` BEFORE UPDATE ON `Link` FOR EACH ROW {$link_trigger_body}";
// enable IP addressing for all object types unless specifically excluded
$query[] = "UPDATE `Config` SET varvalue = 'not ({\$typeid_3} or {\$typeid_9} or {\$typeid_10} or {\$typeid_11})' WHERE varname = 'IPV4OBJ_LISTSRC'";
$query[] = "ALTER TABLE `EntityLink` MODIFY COLUMN `parent_entity_type` ENUM('location','object','rack','row') NOT NULL";
$query[] = "ALTER TABLE `EntityLink` MODIFY COLUMN `child_entity_type` ENUM('location','object','rack','row') NOT NULL";
$query[] = "UPDATE Config SET description = 'List source: objects for that asset tag should be set' WHERE varname = 'ASSETWARN_LISTSRC'";
$query[] = "UPDATE Config SET description = 'List source: objects for that common name should be set' WHERE varname = 'NAMEWARN_LISTSRC'";
$query[] = "ALTER TABLE `IPv4NAT` MODIFY COLUMN `proto` ENUM('TCP','UDP','ALL')";
// add new 'point2point' alloc type
$query[] = "ALTER TABLE `IPv4Allocation` MODIFY `type` enum('regular','shared','virtual','router','point2point') NOT NULL DEFAULT 'regular'";
$query[] = "ALTER TABLE `IPv6Allocation` MODIFY `type` enum('regular','shared','virtual','router','point2point') NOT NULL DEFAULT 'regular'";
// update to use utf8_unicode_ci collation
// http://bugs.racktables.org/view.php?id=837
$query[] = "ALTER DATABASE DEFAULT CHARACTER SET UTF8 COLLATE utf8_unicode_ci";
$query[] = "SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0";
$query[] = "ALTER TABLE `Atom` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
$query[] = "ALTER TABLE `Attribute` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
$query[] = "ALTER TABLE `AttributeMap` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
$query[] = "ALTER TABLE `AttributeValue` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
$query[] = "ALTER TABLE `CachedPAV` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
$query[] = "ALTER TABLE `CachedPNV` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
$query[] = "ALTER TABLE `CachedPVM` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
$query[] = "ALTER TABLE `CactiGraph` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
$query[] = "ALTER TABLE `CactiServer` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
$query[] = "ALTER TABLE `Chapter` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
$query[] = "ALTER TABLE `Config` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
$query[] = "ALTER TABLE `Dictionary` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
$query[] = "ALTER TABLE `EntityLink` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
$query[] = "ALTER TABLE `File` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
$query[] = "ALTER TABLE `FileLink` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
$query[] = "ALTER TABLE `IPv4Address` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
$query[] = "ALTER TABLE `IPv4Allocation` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
$query[] = "ALTER TABLE `IPv4LB` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
$query[] = "ALTER TABLE `IPv4Log` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
$query[] = "ALTER TABLE `IPv6Log` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
$query[] = "ALTER TABLE `IPv4NAT` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
$query[] = "ALTER TABLE `IPv4Network` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
$query[] = "ALTER TABLE `IPv4RS` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
$query[] = "ALTER TABLE `IPv4RSPool` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
$query[] = "ALTER TABLE `IPv4VS` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
$query[] = "ALTER TABLE `IPv6Address` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
$query[] = "ALTER TABLE `IPv6Allocation` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
$query[] = "ALTER TABLE `IPv6Network` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
$query[] = "ALTER TABLE `LDAPCache` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
$query[] = "ALTER TABLE `Link` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
$query[] = "ALTER TABLE `Molecule` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
$query[] = "ALTER TABLE `MountOperation` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
$query[] = "ALTER TABLE `MuninGraph` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
$query[] = "ALTER TABLE `MuninServer` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
$query[] = "ALTER TABLE `ObjectLog` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
$query[] = "ALTER TABLE `ObjectParentCompat` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
$query[] = "ALTER TABLE `Port` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
$query[] = "ALTER TABLE `PortAllowedVLAN` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
$query[] = "ALTER TABLE `PortCompat` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
$query[] = "ALTER TABLE `PortInnerInterface` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
$query[] = "ALTER TABLE `PortInterfaceCompat` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
$query[] = "ALTER TABLE `PortLog` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
$query[] = "ALTER TABLE `PortNativeVLAN` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
$query[] = "ALTER TABLE `PortVLANMode` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
$query[] = "ALTER TABLE `Object` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
$query[] = "ALTER TABLE `ObjectHistory` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
$query[] = "ALTER TABLE `RackSpace` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
$query[] = "ALTER TABLE `RackThumbnail` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
$query[] = "ALTER TABLE `Script` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
$query[] = "ALTER TABLE `TagStorage` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
$query[] = "ALTER TABLE `TagTree` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
$query[] = "ALTER TABLE `UserAccount` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
$query[] = "ALTER TABLE `UserConfig` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
$query[] = "ALTER TABLE `VLANDescription` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
$query[] = "ALTER TABLE `VLANDomain` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
$query[] = "ALTER TABLE `VLANIPv4` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
$query[] = "ALTER TABLE `VLANIPv6` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
$query[] = "ALTER TABLE `VLANSTRule` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
$query[] = "ALTER TABLE `VLANSwitch` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
$query[] = "ALTER TABLE `VLANSwitchTemplate` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
$query[] = "ALTER TABLE `VLANValidID` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
$query[] = "ALTER TABLE `VS` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
$query[] = "ALTER TABLE `VSIPs` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
$query[] = "ALTER TABLE `VSPorts` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
$query[] = "ALTER TABLE `VSEnabledIPs` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
$query[] = "ALTER TABLE `VSEnabledPorts` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
$query[] = "SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS";
$query[] = "UPDATE Config SET varvalue = '0.20.7' WHERE varname = 'DB_VERSION'";
break;
case '0.20.8':
$query[] = "ALTER TABLE `VLANSTRule` CHANGE COLUMN `wrt_vlans` `wrt_vlans` text";
$query[] = "\nCREATE TABLE `PortOuterInterface` (\n `id` int(10) unsigned NOT NULL auto_increment,\n `oif_name` char(48) NOT NULL,\n PRIMARY KEY (`id`),\n UNIQUE KEY `oif_name` (`oif_name`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci\n";
// if upgrading from certain older releases, inject some data to satisfy foreign keys
if (version_compare($dbver, '0.19.2', '<')) {
$query[] = "INSERT INTO Dictionary (chapter_id,dict_key,dict_value) VALUES (2,1399,'DC')";
}
if (version_compare($dbver, '0.19.5', '<')) {
$query[] = "INSERT INTO Dictionary (chapter_id,dict_key,dict_value) VALUES (2,1469,'virtual port')";
}
if (version_compare($dbver, '0.19.7', '<')) {
$query[] = "INSERT INTO Dictionary (chapter_id,dict_key,dict_value) VALUES (2,1588,'empty QSFP+')";
}
if (version_compare($dbver, '0.19.8', '<')) {
$query[] = "INSERT INTO Dictionary (chapter_id,dict_key,dict_value) VALUES\n(2,1424,'1000Base-CX'),\n(2,1425,'10GBase-ER-DWDM40-61.42 (ITU 20)'),\n(2,1426,'10GBase-ER-DWDM40-60.61 (ITU 21)'),\n(2,1427,'10GBase-ER-DWDM40-59.79 (ITU 22)'),\n(2,1428,'10GBase-ER-DWDM40-58.98 (ITU 23)'),\n(2,1429,'10GBase-ER-DWDM40-58.17 (ITU 24)'),\n(2,1430,'10GBase-ER-DWDM40-57.36 (ITU 25)'),\n(2,1431,'10GBase-ER-DWDM40-56.55 (ITU 26)'),\n(2,1432,'10GBase-ER-DWDM40-55.75 (ITU 27)'),\n(2,1433,'10GBase-ER-DWDM40-54.94 (ITU 28)'),\n(2,1434,'10GBase-ER-DWDM40-54.13 (ITU 29)'),\n(2,1435,'10GBase-ER-DWDM40-53.33 (ITU 30)'),\n(2,1436,'10GBase-ER-DWDM40-52.52 (ITU 31)'),\n(2,1437,'10GBase-ER-DWDM40-51.72 (ITU 32)'),\n(2,1438,'10GBase-ER-DWDM40-50.92 (ITU 33)'),\n(2,1439,'10GBase-ER-DWDM40-50.12 (ITU 34)'),\n(2,1440,'10GBase-ER-DWDM40-49.32 (ITU 35)'),\n(2,1441,'10GBase-ER-DWDM40-48.51 (ITU 36)'),\n(2,1442,'10GBase-ER-DWDM40-47.72 (ITU 37)'),\n(2,1443,'10GBase-ER-DWDM40-46.92 (ITU 38)'),\n(2,1444,'10GBase-ER-DWDM40-46.12 (ITU 39)'),\n(2,1445,'10GBase-ER-DWDM40-45.32 (ITU 40)'),\n(2,1446,'10GBase-ER-DWDM40-44.53 (ITU 41)'),\n(2,1447,'10GBase-ER-DWDM40-43.73 (ITU 42)'),\n(2,1448,'10GBase-ER-DWDM40-42.94 (ITU 43)'),\n(2,1449,'10GBase-ER-DWDM40-42.14 (ITU 44)'),\n(2,1450,'10GBase-ER-DWDM40-41.35 (ITU 45)'),\n(2,1451,'10GBase-ER-DWDM40-40.56 (ITU 46)'),\n(2,1452,'10GBase-ER-DWDM40-39.77 (ITU 47)'),\n(2,1453,'10GBase-ER-DWDM40-38.98 (ITU 48)'),\n(2,1454,'10GBase-ER-DWDM40-38.19 (ITU 49)'),\n(2,1455,'10GBase-ER-DWDM40-37.40 (ITU 50)'),\n(2,1456,'10GBase-ER-DWDM40-36.61 (ITU 51)'),\n(2,1457,'10GBase-ER-DWDM40-35.82 (ITU 52)'),\n(2,1458,'10GBase-ER-DWDM40-35.04 (ITU 53)'),\n(2,1459,'10GBase-ER-DWDM40-34.25 (ITU 54)'),\n(2,1460,'10GBase-ER-DWDM40-33.47 (ITU 55)'),\n(2,1461,'10GBase-ER-DWDM40-32.68 (ITU 56)'),\n(2,1462,'10GBase-ER-DWDM40-31.90 (ITU 57)'),\n(2,1463,'10GBase-ER-DWDM40-31.12 (ITU 58)'),\n(2,1464,'10GBase-ER-DWDM40-30.33 (ITU 59)'),\n(2,1465,'10GBase-ER-DWDM40-29.55 (ITU 60)'),\n(2,1466,'10GBase-ER-DWDM40-28.77 (ITU 61)')";
}
if (version_compare($dbver, '0.19.10', '<')) {
$query[] = "INSERT INTO Dictionary (chapter_id,dict_key,dict_value) VALUES (2,1603,'1000Base-T (HP c-Class)')";
}
if (version_compare($dbver, '0.19.11', '<')) {
$query[] = "INSERT INTO Dictionary (chapter_id,dict_key,dict_value) VALUES (2,1642,'10GBase-T')";
}
if (version_compare($dbver, '0.19.12', '<')) {
$query[] = "INSERT INTO Dictionary (chapter_id,dict_key,dict_value) VALUES\n(2,1661,'40GBase-KR4'),\n(2,1663,'40GBase-SR4'),\n(2,1664,'40GBase-LR4'),\n(2,1668,'empty CFP'),\n(2,1669,'100GBase-SR10'),\n(2,1670,'100GBase-LR4'),\n(2,1671,'100GBase-ER4')";
}
$query[] = "INSERT INTO PortOuterInterface SELECT dict_key, dict_value FROM Dictionary WHERE chapter_id = 2";
// Previously listed 10GBase-Kx actually means two standards: 10GBase-KX4
// and 10GBase-KR. Make respective changes and make primary key auto
// increment start at 2000.
$query[] = "UPDATE PortOuterInterface SET oif_name = '10GBase-KX4' WHERE id = 41";
$query[] = "INSERT INTO PortOuterInterface (id, oif_name) VALUES (1999, '10GBase-KR')";
$query[] = "INSERT INTO PortCompat (type1, type2) VALUES (1999, 1999)";
$query[] = "DELETE FROM Dictionary WHERE chapter_id = 2";
$query[] = "DELETE FROM Chapter WHERE id = 2";
$query[] = "ALTER TABLE PortInterfaceCompat ADD CONSTRAINT `PortInterfaceCompat-FK-oif_id` FOREIGN KEY (oif_id) REFERENCES PortOuterInterface (id)";
$query[] = "ALTER TABLE PortCompat ADD CONSTRAINT `PortCompat-FK-oif_id1` FOREIGN KEY (type1) REFERENCES PortOuterInterface (id)";
$query[] = "ALTER TABLE PortCompat ADD CONSTRAINT `PortCompat-FK-oif_id2` FOREIGN KEY (type2) REFERENCES PortOuterInterface (id)";
// Add more 40G and 100G standards.
$query[] = "INSERT INTO PortOuterInterface (id, oif_name) VALUES\n(1660,'40GBase-FR'),\n(1662,'40GBase-ER4'),\n(1672,'100GBase-SR4'),\n(1673,'100GBase-KR4'),\n(1674,'100GBase-KP4')";
$query[] = "INSERT INTO PortInterfaceCompat (iif_id, oif_id) VALUES\n(10,1660),\n(10,1662),\n(11,1672),\n(11,1673),\n(11,1674)";
$query[] = "INSERT INTO PortCompat (type1, type2) VALUES\n(1660,1660),\n(1662,1662),\n(1672,1672),\n(1673,1673),\n(1674,1674)";
// Refine 1G OIF list: fix spelling and add a new standard.
$query[] = "UPDATE PortOuterInterface SET oif_name = '1000Base-LX10' WHERE id = 1205";
$query[] = "INSERT INTO PortOuterInterface (id, oif_name) VALUES (42, '1000Base-EX')";
$query[] = "INSERT INTO PortCompat (type1, type2) VALUES (42, 42)";
$query[] = "INSERT INTO PortInterfaceCompat (iif_id, oif_id) VALUES (3, 42), (4,42)";
// patch cables
$query[] = "\nCREATE TABLE `PatchCableConnector` (\n `id` int(10) unsigned NOT NULL AUTO_INCREMENT,\n `origin` enum('default','custom') NOT NULL DEFAULT 'custom',\n `connector` char(32) NOT NULL,\n PRIMARY KEY (`id`),\n UNIQUE KEY `connector_per_origin` (`connector`,`origin`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";
$query[] = "\nCREATE TABLE `PatchCableType` (\n `id` int(10) unsigned NOT NULL AUTO_INCREMENT,\n `origin` enum('default','custom') NOT NULL DEFAULT 'custom',\n `pctype` char(64) NOT NULL,\n PRIMARY KEY (`id`),\n UNIQUE KEY `pctype_per_origin` (`pctype`,`origin`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";
$query[] = "\nCREATE TABLE `PatchCableConnectorCompat` (\n `pctype_id` int(10) unsigned NOT NULL,\n `connector_id` int(10) unsigned NOT NULL,\n PRIMARY KEY (`pctype_id`,`connector_id`),\n KEY `connector_id` (`connector_id`),\n CONSTRAINT `PatchCableConnectorCompat-FK-connector_id` FOREIGN KEY (`connector_id`) REFERENCES `PatchCableConnector` (`id`),\n CONSTRAINT `PatchCableConnectorCompat-FK-pctype_id` FOREIGN KEY (`pctype_id`) REFERENCES `PatchCableType` (`id`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";
$query[] = "\nCREATE TABLE `PatchCableHeap` (\n `id` int(10) unsigned NOT NULL AUTO_INCREMENT,\n `pctype_id` int(10) unsigned NOT NULL,\n `end1_conn_id` int(10) unsigned NOT NULL,\n `end2_conn_id` int(10) unsigned NOT NULL,\n `amount` smallint(5) unsigned NOT NULL DEFAULT '0',\n `length` decimal(5,2) unsigned NOT NULL DEFAULT '1.00',\n `description` char(255) DEFAULT NULL,\n PRIMARY KEY (`id`),\n KEY `compat1` (`pctype_id`,`end1_conn_id`),\n KEY `compat2` (`pctype_id`,`end2_conn_id`),\n CONSTRAINT `PatchCableHeap-FK-compat1` FOREIGN KEY (`pctype_id`, `end1_conn_id`) REFERENCES `PatchCableConnectorCompat` (`pctype_id`, `connector_id`),\n CONSTRAINT `PatchCableHeap-FK-compat2` FOREIGN KEY (`pctype_id`, `end2_conn_id`) REFERENCES `PatchCableConnectorCompat` (`pctype_id`, `connector_id`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";
$query[] = "\nCREATE TABLE `PatchCableHeapLog` (\n `id` int(10) unsigned NOT NULL AUTO_INCREMENT,\n `heap_id` int(10) unsigned NOT NULL,\n `date` datetime NOT NULL,\n `user` char(64) NOT NULL,\n `message` char(255) NOT NULL,\n PRIMARY KEY (`id`),\n KEY `heap_id-date` (`heap_id`,`date`),\n CONSTRAINT `PatchCableHeapLog-FK-heap_id` FOREIGN KEY (`heap_id`) REFERENCES `PatchCableHeap` (`id`) ON DELETE CASCADE\n) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";
$query[] = "\nCREATE TABLE `PatchCableOIFCompat` (\n `pctype_id` int(10) unsigned NOT NULL,\n `oif_id` int(10) unsigned NOT NULL,\n PRIMARY KEY (`pctype_id`,`oif_id`),\n KEY `oif_id` (`oif_id`),\n CONSTRAINT `PatchCableOIFCompat-FK-oif_id` FOREIGN KEY (`oif_id`) REFERENCES `PortOuterInterface` (`id`),\n CONSTRAINT `PatchCableOIFCompat-FK-pctype_id` FOREIGN KEY (`pctype_id`) REFERENCES `PatchCableType` (`id`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";
$query[] = "INSERT INTO PatchCableConnector (id, origin, connector) VALUES\n(1,'default','FC/PC'),(2,'default','FC/APC'),\n(3,'default','LC/PC'),(4,'default','LC/APC'),\n(5,'default','MPO-12/PC'),(6,'default','MPO-12/APC'),\n(7,'default','MPO-24/PC'),(8,'default','MPO-24/APC'),\n(9,'default','SC/PC'),(10,'default','SC/APC'),\n(11,'default','ST/PC'),(12,'default','ST/APC'),\n(13,'default','T568/8P8C/RJ45'),\n(14,'default','SFP-1000'),\n(15,'default','SFP+'),\n(999,'default','CX4/SFF-8470')";
$query[] = "INSERT INTO PatchCableType (id, origin, pctype) VALUES\n(1,'default','duplex OM1'),\n(2,'default','duplex OM2'),\n(3,'default','duplex OM3'),\n(4,'default','duplex OM4'),\n(5,'default','duplex OS1'),\n(6,'default','duplex OS2'),\n(7,'default','simplex OM1'),\n(8,'default','simplex OM2'),\n(9,'default','simplex OM3'),\n(10,'default','simplex OM4'),\n(11,'default','simplex OS1'),\n(12,'default','simplex OS2'),\n(13,'default','Cat.5 TP'),\n(14,'default','Cat.6 TP'),\n(15,'default','Cat.6a TP'),\n(16,'default','Cat.7 TP'),\n(17,'default','Cat.7a TP'),\n(18,'default','12-fiber OM3'),\n(19,'default','12-fiber OM4'),\n(20,'default','10Gb/s CX4 coax'),\n(21,'default','24-fiber OM3'),\n(22,'default','24-fiber OM4'),\n(23,'default','1Gb/s 50cm shielded'),\n(24,'default','10Gb/s 24AWG twinax'),\n(25,'default','10Gb/s 26AWG twinax'),\n(26,'default','10Gb/s 28AWG twinax'),\n(27,'default','10Gb/s 30AWG twinax'),\n(999,'default','Cat.3 TP')";
$query[] = "INSERT INTO PatchCableOIFCompat (pctype_id, oif_id) VALUES\n(13,18),(14,18),(15,18),(16,18),(17,18),(999,18), -- 10Base-T: Cat.3+ TP\n(11,1198),(12,1198),(11,1199),(12,1199), -- 100Base-BX10: 1xSMF\n(5,1197),(6,1197), -- 100Base-LX10: 2xSMF\n(5,1200),(6,1200), -- 100Base-EX: 2xSMF\n(5,1201),(6,1201), -- 100Base-ZX: 2xSMF\n(1,1195),(2,1195),(3,1195),(4,1195), -- 100Base-FX: 2xMMF\n(1,1196),(2,1196),(3,1196),(4,1196), -- 100Base-SX: 2xMMF\n(13,19),(14,19),(15,19),(16,19),(17,19), -- 100Base-TX: Cat.5+ TP\n(11,1206),(12,1206),(11,1207),(12,1207), -- 1000Base-BX10: 1xSMF\n(5,1204),(6,1204), -- 1000Base-LX: 2xSMF\n(5,1205),(6,1205), -- 1000Base-LX10: 2xSMF\n(1,1202),(2,1202),(3,1202),(4,1202), -- 1000Base-SX: 2xMMF\n(1,1203),(2,1203),(3,1203),(4,1203), -- 1000Base-SX+: 2xMMF\n(13,24),(14,24),(15,24),(16,24),(17,24), -- 1000Base-T: Cat.5+ TP\n(5,34),(6,34), -- 1000Base-ZX: 2xSMF\n(23,1077), -- 1000Base direct attach: shielded\n(1,30),(2,30),(3,30),(4,30), -- 10GBase-SR: 2xMMF\n(5,36),(6,36), -- 10GBase-LR: 2xSMF\n(5,35),(6,35), -- 10GBase-ER: 2xSMF\n(5,38),(6,38), -- 10GBase-ZR: 2xSMF\n(1,39),(2,39),(3,39),(4,39),(5,39),(6,39), -- 10GBase-LX4: 2xMMF/2xSMF\n(1,37),(2,37),(3,37),(4,37), -- 10GBase-LRM: 2xMMF\n(14,1642),(15,1642),(16,1642),(17,1642), -- 10GBase-T: Cat.6+ TP\n(20,40), -- 10GBase-CX4: coax\n(24,1084),(25,1084),(26,1084),(27,1084), -- 10GBase direct attach: twinax\n(18,1663),(19,1663), -- 40GBase-SR4: 8xMMF\n(5,1664),(6,1664), -- 40GBase-LR4: 2xSMF\n(5,1662),(6,1662), -- 40GBase-ER4: 2xSMF\n(5,1660),(6,1660), -- 40GBase-FR: 2xSMF\n(21,1669),(22,1669), -- 100GBase-SR10: 20xMMF\n(18,1672),(19,1672), -- 100GBase-SR4: 8xMMF\n(5,1670),(6,1670), -- 100GBase-LR4: 2xSMF\n(5,1671),(6,1671) -- 100GBase-ER4: 2xSMF";
$query[] = "INSERT INTO PatchCableConnectorCompat (pctype_id, connector_id) VALUES\n(1,1),(2,1),(3,1),(4,1),(5,1),(6,1),(7,1),(8,1),(9,1),(10,1),(11,1),(12,1), -- FC/PC\n(1,2),(2,2),(3,2),(4,2),(5,2),(6,2),(7,2),(8,2),(9,2),(10,2),(11,2),(12,2), -- FC/APC\n(1,3),(2,3),(3,3),(4,3),(5,3),(6,3),(7,3),(8,3),(9,3),(10,3),(11,3),(12,3), -- LC/PC\n(1,4),(2,4),(3,4),(4,4),(5,4),(6,4),(7,4),(8,4),(9,4),(10,4),(11,4),(12,4), -- LC/APC\n(1,9),(2,9),(3,9),(4,9),(5,9),(6,9),(7,9),(8,9),(9,9),(10,9),(11,9),(12,9), -- SC/PC\n(1,10),(2,10),(3,10),(4,10),(5,10),(6,10),(7,10),(8,10),(9,10),(10,10),(11,10),(12,10), -- SC/APC\n(1,11),(2,11),(3,11),(4,11),(5,11),(6,11),(7,11),(8,11),(9,11),(10,11),(11,11),(12,11), -- ST/PC\n(1,12),(2,12),(3,12),(4,12),(5,12),(6,12),(7,12),(8,12),(9,12),(10,12),(11,12),(12,12), -- ST/APC\n(13,13),(14,13),(15,13),(16,13),(17,13),(999,13), -- T568\n(18,5),(19,5), -- MPO-12/PC\n(18,6),(19,6), -- MPO-12/APC\n(20,999), -- CX4\n(21,7),(22,7), -- MPO-24/PC\n(21,8),(22,8), -- MPO-24/APC\n(23,14), -- SFP-1000\n(24,15),(25,15),(26,15),(27,15) -- SFP+";
// add rules for Cisco UCS objects
$query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1787,8),(1787,1502)";
$query[] = "UPDATE Config SET varvalue = '0.20.8' WHERE varname = 'DB_VERSION'";
break;
case '0.20.9':
$query[] = "ALTER TABLE CactiGraph ADD KEY (server_id)";
$query[] = "ALTER TABLE CactiGraph DROP PRIMARY KEY";
$query[] = "ALTER TABLE CactiGraph ADD PRIMARY KEY (object_id, server_id, graph_id)";
$query[] = "ALTER TABLE CactiGraph DROP KEY `object_id`";
$query[] = "UPDATE Config SET description = 'List of pages to display in quick links' WHERE varname = 'QUICK_LINK_PAGES'";
$query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('CACTI_RRA_ID','1','uint','no','no','yes','RRA ID for Cacti graphs displayed in RackTables')";
$query[] = "INSERT INTO `Config` (`varname`,`varvalue`,`vartype`,`emptyok`,`is_hidden`,`is_userdefined`,`description`)\nVALUES ('SHOW_OBJECTTYPE', 'no', 'string', 'no', 'no', 'yes', 'Show object type column on depot page.')";
$query[] = "INSERT INTO PortInnerInterface (id, iif_name) VALUES (12, 'CFP2'),(13,'CPAK')";
$query[] = "INSERT INTO PortOuterInterface (id, oif_name) VALUES (1589, 'empty CFP2'),(1590,'empty CPAK')";
$query[] = "INSERT INTO PortInterfaceCompat (iif_id, oif_id) VALUES\n\t\t\t\t(12,1589),(12,1669),(12,1670),(12,1671),(12,1672),(12,1673),(12,1674),\n\t\t\t\t(13,1590),(13,1669),(13,1670),(13,1671),(13,1672),(13,1673),(13,1674)";
$query[] = "INSERT INTO PortCompat (type1, type2) VALUES (1588,1589),(1588,1590),(1589,1589),(1589,1590),(1590,1590)";
$query[] = "UPDATE Config SET varvalue = CONCAT(varvalue, '; 12=1589; 13=1590') WHERE varname = 'DEFAULT_PORT_OIF_IDS'";
$query[] = extendPortCompatQuery();
$query[] = "UPDATE Config SET varvalue = '0.20.9' WHERE varname = 'DB_VERSION'";
break;
case '0.20.10':
$query[] = "UPDATE Config SET varvalue = '0.20.10' WHERE varname = 'DB_VERSION'";
break;
case '0.20.11':
$query[] = "ALTER TABLE VLANDomain ADD COLUMN `group_id` int(10) UNSIGNED DEFAULT NULL AFTER `id`, " . "ADD CONSTRAINT `VLANDomain-FK-group_id` FOREIGN KEY (`group_id`) REFERENCES `VLANDomain` (`id`) ON DELETE SET NULL";
// new 100GBase port types
$query[] = "INSERT INTO `PortInnerInterface` (`id`, `iif_name`) VALUES (14,'CXP')";
$query[] = "INSERT INTO `PortOuterInterface` (`id`, `oif_name`) VALUES\n\t\t\t\t(1591,'empty CXP'),\n\t\t\t\t(1675,'100GBase-LR10'),\n\t\t\t\t(1676,'100GBase-ER10'),\n\t\t\t\t(1677,'100GBase-CR4'),\n\t\t\t\t(1678,'100GBase-CR10')";
$query[] = "INSERT INTO `PatchCableOIFCompat` (`pctype_id`, `oif_id`) VALUES\n\t\t\t\t(5,1675),(6,1675), -- 100GBase-LR10: 2xSMF\n\t\t\t\t(5,1676),(6,1676) -- 100GBase-ER10: 2xSMF";
$query[] = "INSERT INTO `PortInterfaceCompat` (`iif_id`, `oif_id`) VALUES\n\t\t\t\t(11,1675),(11,1676),\n\t\t\t\t(12,1675),(12,1676),\n\t\t\t\t(13,1675),(13,1676),\n\t\t\t\t(14,1591),(14,1677),(14,1678)";
$query[] = "INSERT INTO `PortCompat` (`type1`, `type2`) VALUES\n\t\t\t\t(1591,1591),\n\t\t\t\t(1675,1675),\n\t\t\t\t(1676,1676),\n\t\t\t\t(1677,1677),\n\t\t\t\t(1678,1678)";
$query[] = "UPDATE Config SET varvalue = CONCAT(varvalue, '; 14=1591') WHERE varname = 'DEFAULT_PORT_OIF_IDS'";
// ABI_ver = 2, invalidate RackCode cache
$query[] = "DELETE FROM Script WHERE script_name='RackCodeCache'";
$query[] = "UPDATE Config SET varvalue = '0.20.11' WHERE varname = 'DB_VERSION'";
break;
case 'dictionary':
$query = reloadDictionary();
break;
default:
return NULL;
}
return $query;
}
function getUpgradeBatch($batchid)
{
$query = array();
global $dbxlink;
switch ($batchid) {
case '0.16.5':
$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')";
$query[] = "update Config set varvalue = '0.16.5' where varname = 'DB_VERSION'";
break;
case '0.16.6':
$query[] = "update Config set varvalue = '0.16.6' where varname = 'DB_VERSION'";
break;
case '0.17.0':
// create tables for storing files (requires InnoDB support)
if (!isInnoDBSupported()) {
showUpgradeError("Cannot upgrade because InnoDB tables are not supported by your MySQL server. See the README for details.", __FUNCTION__);
die;
}
$query[] = "alter table Chapter change chapter_no id int(10) unsigned NOT NULL auto_increment";
$query[] = "alter table Chapter change chapter_name name char(128) NOT NULL";
$query[] = "alter table Chapter drop key chapter_name";
$query[] = "alter table Chapter add UNIQUE KEY name (name)";
$query[] = "alter table Attribute change attr_id id int(10) unsigned NOT NULL auto_increment";
$query[] = "alter table Attribute change attr_type type enum('string','uint','float','dict') default NULL";
$query[] = "alter table Attribute change attr_name name char(64) default NULL";
$query[] = "alter table Attribute drop key attr_name";
$query[] = "alter table Attribute add UNIQUE KEY name (name)";
$query[] = "alter table AttributeMap change chapter_no chapter_id int(10) unsigned NOT NULL";
$query[] = "alter table Dictionary change chapter_no chapter_id int(10) unsigned NOT NULL";
// schema changes for file management
$query[] = "\nCREATE TABLE `File` (\n `id` int(10) unsigned NOT NULL auto_increment,\n `name` char(255) NOT NULL,\n `type` char(255) NOT NULL,\n `size` int(10) unsigned NOT NULL,\n `ctime` datetime NOT NULL,\n `mtime` datetime NOT NULL,\n `atime` datetime NOT NULL,\n `contents` longblob NOT NULL,\n `comment` text,\n PRIMARY KEY (`id`),\n UNIQUE KEY `name` (`name`)\n) ENGINE=InnoDB";
$query[] = "\nCREATE TABLE `FileLink` (\n `id` int(10) unsigned NOT NULL auto_increment,\n `file_id` int(10) unsigned NOT NULL,\n `entity_type` enum('ipv4net','ipv4rspool','ipv4vs','object','rack','user') NOT NULL default 'object',\n `entity_id` int(10) NOT NULL,\n PRIMARY KEY (`id`),\n UNIQUE KEY `FileLink-unique` (`file_id`,`entity_type`,`entity_id`),\n KEY `FileLink-file_id` (`file_id`),\n CONSTRAINT `FileLink-File_fkey` FOREIGN KEY (`file_id`) REFERENCES `File` (`id`) ON DELETE CASCADE ON UPDATE CASCADE\n) ENGINE=InnoDB";
$query[] = "ALTER TABLE TagStorage MODIFY COLUMN target_realm enum('file','ipv4net','ipv4rspool','ipv4vs','object','rack','user') NOT NULL default 'object'";
$query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (24,'no','network security models')";
$query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (25,'no','wireless models')";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,1,0)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,2,24)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,3,0)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,5,0)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,14,0)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,16,0)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,17,0)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,18,0)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,20,0)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,21,0)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,22,0)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (798,24,0)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (965,1,0)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (965,3,0)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (965,2,25)";
$query[] = 'alter table IPBonds rename to IPv4Allocation';
$query[] = 'alter table PortForwarding rename to IPv4NAT';
$query[] = 'alter table IPRanges rename to IPv4Network';
$query[] = 'alter table IPAddress rename to IPv4Address';
$query[] = 'alter table IPLoadBalancer rename to IPv4LB';
$query[] = 'alter table IPRSPool rename to IPv4RSPool';
$query[] = 'alter table IPRealServer rename to IPv4RS';
$query[] = 'alter table IPVirtualService rename to IPv4VS';
$query[] = "alter table TagStorage change column target_realm entity_realm enum('file','ipv4net','ipv4vs','ipv4rspool','object','rack','user') NOT NULL default 'object'";
$query[] = 'alter table TagStorage change column target_id entity_id int(10) unsigned NOT NULL';
$query[] = 'alter table TagStorage drop key entity_tag';
$query[] = 'alter table TagStorage drop key target_id';
$query[] = 'alter table TagStorage add UNIQUE KEY `entity_tag` (`entity_realm`,`entity_id`,`tag_id`)';
$query[] = 'alter table TagStorage add KEY `entity_id` (`entity_id`)';
$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')";
$query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('PREVIEW_TEXT_ROWS','25','uint','yes','no','Rows for text file preview')";
$query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('PREVIEW_TEXT_COLS','80','uint','yes','no','Columns for text file preview')";
$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')";
$query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('VENDOR_SIEVE','','string','yes','no','Vendor sieve configuration')";
$query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('IPV4LB_LISTSRC','{\$typeid_4}','string','yes','no','List source: IPv4 load balancers')";
$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')";
$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')";
$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')";
$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')";
$query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('RACKS_PER_ROW','12','unit','yes','no','Racks per row')";
$query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('FILTER_PREDICATE_SIEVE','','string','yes','no','Predicate sieve regex(7)')";
$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)')";
$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')";
$query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('FILTER_SUGGEST_TAGS','yes','string','no','no','Suggest tags in list filter')";
$query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('FILTER_SUGGEST_PREDICATES','yes','string','no','no','Suggest predicates in list filter')";
$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')";
$query[] = "delete from Config where varname = 'USER_AUTH_SRC'";
$query[] = "delete from Config where varname = 'COOKIE_TTL'";
$query[] = "delete from Config where varname = 'rtwidth_0'";
$query[] = "delete from Config where varname = 'rtwidth_1'";
$query[] = "delete from Config where varname = 'rtwidth_2'";
$query[] = "delete from Config where varname = 'NAMEFUL_OBJTYPES'";
$query[] = "delete from Config where varname = 'REQUIRE_ASSET_TAG_FOR'";
$query[] = "delete from Config where varname = 'IPV4_PERFORMERS'";
$query[] = "delete from Config where varname = 'NATV4_PERFORMERS'";
$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";
$result = $dbxlink->query("select user_id, user_name, user_realname from UserAccount where user_enabled = 'no'");
while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
$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'";
}
$query[] = "update Script set script_text = NULL where script_name = 'RackCodeCache'";
unset($result);
$query[] = "alter table UserAccount drop column user_enabled";
$query[] = "CREATE TABLE RackRow ( id int(10) unsigned NOT NULL auto_increment, name char(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM";
$result = $dbxlink->query("select dict_key, dict_value from Dictionary where chapter_no = 3");
while ($row = $result->fetch(PDO::FETCH_NUM)) {
$query[] = "insert into RackRow set id={$row[0]}, name='{$row[1]}'";
}
unset($result);
$query[] = "delete from Dictionary where chapter_id = 3";
$query[] = "delete from Chapter where id = 3";
$query[] = "\nCREATE TABLE `LDAPCache` (\n `presented_username` char(64) NOT NULL,\n `successful_hash` char(40) NOT NULL,\n `first_success` timestamp NOT NULL default CURRENT_TIMESTAMP,\n `last_retry` timestamp NOT NULL default '0000-00-00 00:00:00',\n `displayed_name` char(128) default NULL,\n `memberof` text,\n UNIQUE KEY `presented_username` (`presented_username`),\n KEY `scanidx` (`presented_username`,`successful_hash`)\n) ENGINE=InnoDB;";
$query[] = "alter table UserAccount modify column user_password_hash char(40) NULL";
$query[] = 'ALTER TABLE Rack DROP COLUMN deleted';
$query[] = 'ALTER TABLE RackHistory DROP COLUMN deleted';
$query[] = 'ALTER TABLE RackObject DROP COLUMN deleted';
$query[] = 'ALTER TABLE RackObjectHistory DROP COLUMN deleted';
// Can't be added straight due to many duplicates, even in "dictbase" data.
$result = $dbxlink->query('SELECT type1, type2, count(*) - 1 as excess FROM PortCompat GROUP BY type1, type2 HAVING excess > 0');
while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
$query[] = "DELETE FROM PortCompat WHERE type1 = {$row['type1']} AND type2 = {$row['type2']} limit {$row['excess']}";
}
unset($result);
$query[] = 'ALTER TABLE PortCompat DROP KEY type1';
$query[] = 'ALTER TABLE PortCompat ADD UNIQUE `type1_2` (type1, type2)';
$query[] = "UPDATE Config SET varvalue = '0.17.0' WHERE varname = 'DB_VERSION'";
break;
case '0.17.1':
$query[] = "ALTER TABLE Dictionary DROP KEY `chap_to_key`";
// Token set has changed, so the cache isn't valid any more.
$query[] = "UPDATE Script SET script_text = NULL WHERE script_name = 'RackCodeCache'";
$query[] = "UPDATE Config SET varvalue = '0.17.1' WHERE varname = 'DB_VERSION'";
break;
case '0.17.2':
$query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (26,'no','fibre channel switch models')";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1055,2,26)";
$query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('DEFAULT_SNMP_COMMUNITY','public','string','no','no','Default SNMP Community string')";
// wipe irrelevant records (ticket:250)
$query[] = "DELETE FROM TagStorage WHERE entity_realm = 'file' AND entity_id NOT IN (SELECT id FROM File)";
$query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('IPV4_ENABLE_KNIGHT','yes','string','no','no','Enable IPv4 knight feature')";
$query[] = "ALTER TABLE IPv4Network ADD COLUMN comment text AFTER name";
$query[] = "ALTER TABLE Port ADD INDEX comment (reservation_comment)";
$query[] = "ALTER TABLE Port DROP KEY l2address";
// UNIQUE
$query[] = "ALTER TABLE Port ADD KEY (l2address)";
// not UNIQUE
$query[] = "ALTER TABLE Port DROP KEY object_id";
$query[] = "ALTER TABLE Port ADD UNIQUE KEY per_object (object_id, name, type)";
$query[] = "INSERT INTO PortCompat (type1, type2) VALUES (20,1083)";
$query[] = "INSERT INTO PortCompat (type1, type2) VALUES (21,1083)";
$query[] = "INSERT INTO PortCompat (type1, type2) VALUES (1077,1077)";
$query[] = "INSERT INTO PortCompat (type1, type2) VALUES (1083,20)";
$query[] = "INSERT INTO PortCompat (type1, type2) VALUES (1083,21)";
$query[] = "INSERT INTO PortCompat (type1, type2) VALUES (1083,1083)";
$query[] = "INSERT INTO PortCompat (type1, type2) VALUES (1087,1087)";
$query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (27,'no','PDU models')";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (2,2,27)";
$query[] = "UPDATE Config SET varvalue = '0.17.2' WHERE varname = 'DB_VERSION'";
break;
case '0.17.3':
$query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('TAGS_TOPLIST_SIZE','50','uint','yes','no','Tags top list size')";
$query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('TAGS_QUICKLIST_SIZE','20','uint','no','no','Tags quick list size')";
$query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, description) VALUES ('TAGS_QUICKLIST_THRESHOLD','50','uint','yes','no','Tags quick list threshold')";
$query[] = "ALTER TABLE AttributeMap MODIFY COLUMN chapter_id int(10) unsigned NULL";
$query[] = "UPDATE AttributeMap SET chapter_id = NULL WHERE attr_id IN (SELECT id FROM Attribute WHERE type != 'dict')";
// ticket:239
$query[] = 'UPDATE AttributeValue SET uint_value = 1018 WHERE uint_value = 731 AND attr_id IN (SELECT attr_id FROM AttributeMap WHERE chapter_id = 12)';
$query[] = 'DELETE FROM Dictionary WHERE dict_key = 731';
$query[] = "UPDATE Config SET vartype='uint' WHERE varname='RACKS_PER_ROW'";
$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')";
$query[] = "UPDATE Config SET varvalue = '0.17.3' WHERE varname = 'DB_VERSION'";
break;
case '0.17.4':
$query[] = "ALTER TABLE Link ENGINE=InnoDB";
$query[] = "ALTER TABLE Port ENGINE=InnoDB";
$query[] = "ALTER TABLE IPv4RS ENGINE=InnoDB";
$query[] = "ALTER TABLE IPv4RSPool ENGINE=InnoDB";
$query[] = "ALTER TABLE AttributeValue ENGINE=InnoDB";
$query[] = "ALTER TABLE RackObject ENGINE=InnoDB";
$query[] = "ALTER TABLE IPv4NAT ENGINE=InnoDB";
$query[] = "ALTER TABLE IPv4LB ENGINE=InnoDB";
$query[] = "ALTER TABLE IPv4VS ENGINE=InnoDB";
$query[] = "DELETE FROM IPv4RS WHERE rspool_id NOT IN (SELECT id FROM IPv4RSPool)";
$query[] = "ALTER TABLE Link ADD CONSTRAINT `Link-FK-b` FOREIGN KEY (portb) REFERENCES Port (id)";
$query[] = "ALTER TABLE Link ADD CONSTRAINT `Link-FK-a` FOREIGN KEY (porta) REFERENCES Port (id)";
$query[] = "ALTER TABLE IPv4RS ADD CONSTRAINT `IPv4RS-FK` FOREIGN KEY (rspool_id) REFERENCES IPv4RSPool (id) ON DELETE CASCADE";
$query[] = "ALTER TABLE AttributeValue ADD CONSTRAINT `AttributeValue-FK-object_id` FOREIGN KEY (object_id) REFERENCES RackObject (id)";
$query[] = "ALTER TABLE IPv4NAT ADD CONSTRAINT `IPv4NAT-FK-object_id` FOREIGN KEY (object_id) REFERENCES RackObject (id)";
$query[] = "ALTER TABLE Port ADD CONSTRAINT `Port-FK-object_id` FOREIGN KEY (object_id) REFERENCES RackObject (id)";
$query[] = "ALTER TABLE IPv4LB ADD CONSTRAINT `IPv4LB-FK-rspool_id` FOREIGN KEY (rspool_id) REFERENCES IPv4RSPool (id)";
$query[] = "ALTER TABLE IPv4LB ADD CONSTRAINT `IPv4LB-FK-object_id` FOREIGN KEY (object_id) REFERENCES RackObject (id)";
$query[] = "ALTER TABLE IPv4LB ADD CONSTRAINT `IPv4LB-FK-vs_id` FOREIGN KEY (vs_id) REFERENCES IPv4VS (id)";
$query[] = "UPDATE Config SET varvalue = '0.17.4' WHERE varname = 'DB_VERSION'";
break;
case '0.17.5':
$query[] = "ALTER TABLE TagTree ENGINE=InnoDB";
$query[] = "ALTER TABLE TagStorage ENGINE=InnoDB";
$query[] = "ALTER TABLE TagStorage ADD CONSTRAINT `TagStorage-FK-tag_id` FOREIGN KEY (tag_id) REFERENCES TagTree (id)";
$query[] = "ALTER TABLE TagTree ADD CONSTRAINT `TagTree-K-parent_id` FOREIGN KEY (parent_id) REFERENCES TagTree (id)";
$query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (21,1195)';
$query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (22,1196)';
$query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (23,1196)';
$query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (20,1195)';
$query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (25,1202)';
$query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (26,1202)';
$query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (27,1204)';
$query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (28,1204)';
$query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1083,1195)';
$query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1084,1084)';
$query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1195,20)';
$query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1195,21)';
$query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1195,1083)';
$query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1195,1195)';
$query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1196,22)';
$query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1196,23)';
$query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1196,1196)';
$query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1197,1197)';
$query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1198,1199)';
$query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1199,1198)';
$query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1200,1200)';
$query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1201,1201)';
$query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1202,25)';
$query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1202,26)';
$query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1202,1202)';
$query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1203,1203)';
$query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1204,27)';
$query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1204,28)';
$query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1204,1204)';
$query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1205,1205)';
$query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1206,1207)';
$query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1207,1206)';
$query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1316,1316)';
$query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (16, 1322)';
$query[] = 'INSERT INTO PortCompat (type1, type2) VALUES (1322, 16)';
$query[] = 'DELETE FROM PortCompat WHERE type1 = 16 AND type2 = 16';
for ($i = 1209; $i <= 1300; $i++) {
$query[] = "INSERT INTO PortCompat (type1, type2) VALUES ({$i}, {$i})";
}
$query[] = "\nCREATE TABLE `PortInnerInterface` (\n `id` int(10) unsigned NOT NULL,\n `iif_name` char(16) NOT NULL,\n PRIMARY KEY (`id`),\n UNIQUE KEY `iif_name` (`iif_name`)\n) ENGINE=InnoDB";
$query[] = "INSERT INTO `PortInnerInterface` VALUES (1,'hardwired')";
$query[] = "INSERT INTO `PortInnerInterface` VALUES (2,'SFP-100')";
$query[] = "INSERT INTO `PortInnerInterface` VALUES (3,'GBIC')";
$query[] = "INSERT INTO `PortInnerInterface` VALUES (4,'SFP-1000')";
$query[] = "INSERT INTO `PortInnerInterface` VALUES (5,'XENPAK')";
$query[] = "INSERT INTO `PortInnerInterface` VALUES (6,'X2')";
$query[] = "INSERT INTO `PortInnerInterface` VALUES (7,'XPAK')";
$query[] = "INSERT INTO `PortInnerInterface` VALUES (8,'XFP')";
$query[] = "INSERT INTO `PortInnerInterface` VALUES (9,'SFP+')";
$query[] = "\nCREATE TABLE `PortInterfaceCompat` (\n `iif_id` int(10) unsigned NOT NULL,\n `oif_id` int(10) unsigned NOT NULL,\n UNIQUE KEY `pair` (`iif_id`,`oif_id`),\n CONSTRAINT `PortInterfaceCompat-FK-iif_id` FOREIGN KEY (`iif_id`) REFERENCES `PortInnerInterface` (`id`)\n) ENGINE=InnoDB";
$query[] = "ALTER TABLE Port ADD COLUMN iif_id int unsigned NOT NULL AFTER name";
// will set iif_id to 0
$query[] = "UPDATE Port SET iif_id = 2 WHERE type = 1208";
$query[] = "UPDATE Port SET iif_id = 3 WHERE type = 1078";
$query[] = "UPDATE Port SET iif_id = 4 WHERE type = 1077";
$query[] = "UPDATE Port SET iif_id = 5 WHERE type = 1079";
$query[] = "UPDATE Port SET iif_id = 6 WHERE type = 1080";
$query[] = "UPDATE Port SET iif_id = 7 WHERE type = 1081";
$query[] = "UPDATE Port SET iif_id = 8 WHERE type = 1082";
$query[] = "UPDATE Port SET iif_id = 9 WHERE type = 1084";
$query[] = "UPDATE Port SET iif_id = 1 WHERE iif_id = 0";
$query[] = 'ALTER TABLE Port ADD UNIQUE `object_iif_oif_name` (object_id, iif_id, type, name)';
$query[] = 'ALTER TABLE Port DROP KEY `per_object`';
$base1000 = array(24, 34, 1202, 1203, 1204, 1205, 1206, 1207);
$base10000 = array(30, 35, 36, 37, 38, 39, 40);
$PICdata = array(1 => array(16, 19, 24, 29, 31, 33, 446, 681, 682, 1322), 2 => array(1208, 1195, 1196, 1197, 1198, 1199, 1200, 1201), 3 => array_merge(array(1078), $base1000), 4 => array_merge(array(1077), $base1000), 5 => array_merge(array(1079), $base10000), 6 => array_merge(array(1080), $base10000), 7 => array_merge(array(1081), $base10000), 8 => array_merge(array(1082), $base10000), 9 => array_merge(array(1084), $base10000));
// make sure all IIF/OIF pairs referenced from Port exist in PortInterfaceCompat before enabling FK
// iif_id doesn't exist at this point
$result = $dbxlink->query('SELECT DISTINCT type FROM Port WHERE type NOT IN (1208, 1078, 1077, 1079, 1080, 1081, 1082, 1084)');
while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
if (FALSE === array_search($row['type'], $PICdata[1])) {
array_push($PICdata[1], $row['type']);
}
}
unset($result);
foreach ($PICdata as $iif_id => $oif_ids) {
foreach ($oif_ids as $oif_id) {
$query[] = "INSERT INTO PortInterfaceCompat (iif_id, oif_id) VALUES ({$iif_id}, {$oif_id})";
}
}
$query[] = "ALTER TABLE Port ADD CONSTRAINT `Port-FK-iif-oif` FOREIGN KEY (`iif_id`, `type`) REFERENCES `PortInterfaceCompat` (`iif_id`, `oif_id`)";
$query[] = 'UPDATE Port SET type = 1322 WHERE type = 16 AND (SELECT objtype_id FROM RackObject WHERE id = object_id) IN (2, 12)';
$query[] = "DELETE FROM Config WHERE varname = 'default_port_type'";
$query[] = "INSERT INTO Config VALUES ('DEFAULT_PORT_IIF_ID','1','uint','no','no','Default port inner interface ID')";
$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')";
$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')";
$query[] = "UPDATE Chapter SET name = 'PortOuterInterface' WHERE id = 2";
// remap refs to duplicate records, which will be discarded (ticket:286)
$query[] = 'UPDATE AttributeValue SET uint_value = 147 WHERE uint_value = 1020 AND attr_id = 2';
$query[] = 'UPDATE AttributeValue SET uint_value = 377 WHERE uint_value = 1021 AND attr_id = 2';
$query[] = 'INSERT INTO AttributeMap (objtype_id, attr_id) VALUES (2, 1), (2, 3), (2, 5)';
$query[] = "UPDATE Config SET varvalue = '0.17.5' WHERE varname = 'DB_VERSION'";
break;
case '0.17.6':
$query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (28,'no','Voice/video hardware')";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1323,1,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1323,2,28)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1323,3,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1323,5,NULL)";
$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)')";
$query[] = "UPDATE Config SET varvalue = '0.17.6' WHERE varname = 'DB_VERSION'";
break;
case '0.17.7':
$query[] = "UPDATE Config SET varvalue = '0.17.7' WHERE varname = 'DB_VERSION'";
break;
case '0.17.8':
$query[] = "ALTER TABLE TagTree DROP COLUMN valid_realm";
$query[] = "UPDATE Config SET varvalue = '0.17.8' WHERE varname = 'DB_VERSION'";
break;
case '0.17.9':
$query[] = "ALTER table Config add `is_userdefined` enum('yes','no') NOT NULL default 'no' AFTER `is_hidden`";
$query[] = "\nCREATE TABLE `UserConfig` (\n\t`varname` char(32) NOT NULL,\n\t`varvalue` char(255) NOT NULL,\n\t`user` char(64) NOT NULL,\n\tUNIQUE KEY `user_varname` (`user`,`varname`)\n) TYPE=InnoDB";
$query[] = "UPDATE Config SET is_userdefined = 'yes' WHERE varname IN\n(\n'MASSCOUNT',\n'MAXSELSIZE',\n'ROW_SCALE',\n'PORTS_PER_ROW',\n'IPV4_ADDRS_PER_PAGE',\n'DEFAULT_RACK_HEIGHT',\n'DEFAULT_SLB_VS_PORT',\n'DEFAULT_SLB_RS_PORT',\n'DETECT_URLS',\n'RACK_PRESELECT_THRESHOLD',\n'DEFAULT_IPV4_RS_INSERVICE',\n'DEFAULT_OBJECT_TYPE',\n'SHOW_EXPLICIT_TAGS',\n'SHOW_IMPLICIT_TAGS',\n'SHOW_AUTOMATIC_TAGS',\n'IPV4_AUTO_RELEASE',\n'SHOW_LAST_TAB',\n'EXT_IPV4_VIEW',\n'TREE_THRESHOLD',\n'ADDNEW_AT_TOP',\n'IPV4_TREE_SHOW_USAGE',\n'PREVIEW_TEXT_MAXCHARS',\n'PREVIEW_TEXT_ROWS',\n'PREVIEW_TEXT_COLS',\n'PREVIEW_IMAGE_MAXPXS',\n'VENDOR_SIEVE',\n'RACKS_PER_ROW'\n)";
$query[] = "UPDATE Config SET varvalue = '0.17.9' WHERE varname = 'DB_VERSION'";
break;
case '0.17.10':
$query[] = "ALTER TABLE MountOperation ADD KEY (object_id)";
$query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('STATIC_FILTER','yes','string','no','no','yes','Enable Filter Caching');";
$query[] = "UPDATE Config SET varvalue = '0.17.10' WHERE varname = 'DB_VERSION'";
break;
case '0.17.11':
$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');";
$query[] = "DELETE AttributeValue FROM AttributeValue JOIN Attribute where AttributeValue.attr_id = Attribute.id AND Attribute.type = 'dict' AND AttributeValue.uint_value = 0";
$query[] = "UPDATE Config SET varvalue = '0.17.11' WHERE varname = 'DB_VERSION'";
break;
case '0.18.0':
$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')";
$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')";
$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')";
$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')";
$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')";
$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')";
$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')";
$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')";
$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')";
$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')";
$query[] = "ALTER TABLE IPv4Network ENGINE=InnoDB";
$query[] = "SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0";
$query[] = "\nCREATE TABLE `CachedPAV` (\n `object_id` int(10) unsigned NOT NULL,\n `port_name` char(255) NOT NULL,\n `vlan_id` int(10) unsigned NOT NULL default '0',\n PRIMARY KEY (`object_id`,`port_name`,`vlan_id`),\n KEY `vlan_id` (`vlan_id`),\n CONSTRAINT `CachedPAV-FK-object-port` FOREIGN KEY (`object_id`, `port_name`) REFERENCES `CachedPVM` (`object_id`, `port_name`) ON DELETE CASCADE,\n CONSTRAINT `CachedPAV-FK-vlan_id` FOREIGN KEY (`vlan_id`) REFERENCES `VLANValidID` (`vlan_id`)\n) ENGINE=InnoDB\n";
$query[] = "\nCREATE TABLE `CachedPNV` (\n `object_id` int(10) unsigned NOT NULL,\n `port_name` char(255) NOT NULL,\n `vlan_id` int(10) unsigned NOT NULL default '0',\n PRIMARY KEY (`object_id`,`port_name`,`vlan_id`),\n UNIQUE KEY `port_id` (`object_id`,`port_name`),\n CONSTRAINT `CachedPNV-FK-compound` FOREIGN KEY (`object_id`, `port_name`, `vlan_id`) REFERENCES `CachedPAV` (`object_id`, `port_name`, `vlan_id`) ON DELETE CASCADE\n) ENGINE=InnoDB\n";
$query[] = "\nCREATE TABLE `CachedPVM` (\n `object_id` int(10) unsigned NOT NULL,\n `port_name` char(255) NOT NULL,\n `vlan_mode` enum('access','trunk') NOT NULL default 'access',\n PRIMARY KEY (`object_id`,`port_name`),\n CONSTRAINT `CachedPVM-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE\n) ENGINE=InnoDB\n";
$query[] = "\nCREATE TABLE `PortAllowedVLAN` (\n `object_id` int(10) unsigned NOT NULL,\n `port_name` char(255) NOT NULL,\n `vlan_id` int(10) unsigned NOT NULL default '0',\n PRIMARY KEY (`object_id`,`port_name`,`vlan_id`),\n KEY `vlan_id` (`vlan_id`),\n CONSTRAINT `PortAllowedVLAN-FK-object-port` FOREIGN KEY (`object_id`, `port_name`) REFERENCES `PortVLANMode` (`object_id`, `port_name`) ON DELETE CASCADE,\n CONSTRAINT `PortAllowedVLAN-FK-vlan_id` FOREIGN KEY (`vlan_id`) REFERENCES `VLANValidID` (`vlan_id`)\n) ENGINE=InnoDB\n";
$query[] = "\nCREATE TABLE `PortNativeVLAN` (\n `object_id` int(10) unsigned NOT NULL,\n `port_name` char(255) NOT NULL,\n `vlan_id` int(10) unsigned NOT NULL default '0',\n PRIMARY KEY (`object_id`,`port_name`,`vlan_id`),\n UNIQUE KEY `port_id` (`object_id`,`port_name`),\n CONSTRAINT `PortNativeVLAN-FK-compound` FOREIGN KEY (`object_id`, `port_name`, `vlan_id`) REFERENCES `PortAllowedVLAN` (`object_id`, `port_name`, `vlan_id`) ON DELETE CASCADE\n) ENGINE=InnoDB\n";
$query[] = "\nCREATE TABLE `PortVLANMode` (\n `object_id` int(10) unsigned NOT NULL,\n `port_name` char(255) NOT NULL,\n `vlan_mode` enum('access','trunk') NOT NULL default 'access',\n PRIMARY KEY (`object_id`,`port_name`),\n CONSTRAINT `PortVLANMode-FK-object-port` FOREIGN KEY (`object_id`, `port_name`) REFERENCES `CachedPVM` (`object_id`, `port_name`)\n) ENGINE=InnoDB\n";
$query[] = "\nCREATE TABLE `VLANDescription` (\n `domain_id` int(10) unsigned NOT NULL,\n `vlan_id` int(10) unsigned NOT NULL default '0',\n `vlan_type` enum('ondemand','compulsory','alien') NOT NULL default 'ondemand',\n `vlan_descr` char(255) default NULL,\n PRIMARY KEY (`domain_id`,`vlan_id`),\n KEY `vlan_id` (`vlan_id`),\n CONSTRAINT `VLANDescription-FK-domain_id` FOREIGN KEY (`domain_id`) REFERENCES `VLANDomain` (`id`) ON DELETE CASCADE,\n CONSTRAINT `VLANDescription-FK-vlan_id` FOREIGN KEY (`vlan_id`) REFERENCES `VLANValidID` (`vlan_id`)\n) ENGINE=InnoDB\n";
$query[] = "\nCREATE TABLE `VLANDomain` (\n `id` int(10) unsigned NOT NULL auto_increment,\n `description` char(255) default NULL,\n PRIMARY KEY (`id`),\n UNIQUE KEY `description` (`description`)\n) ENGINE=InnoDB\n";
$query[] = "\nCREATE TABLE `VLANIPv4` (\n `domain_id` int(10) unsigned NOT NULL,\n `vlan_id` int(10) unsigned NOT NULL,\n `ipv4net_id` int(10) unsigned NOT NULL,\n UNIQUE KEY `network-domain` (`ipv4net_id`,`domain_id`),\n KEY `VLANIPv4-FK-compound` (`domain_id`,`vlan_id`),\n CONSTRAINT `VLANIPv4-FK-compound` FOREIGN KEY (`domain_id`, `vlan_id`) REFERENCES `VLANDescription` (`domain_id`, `vlan_id`) ON DELETE CASCADE,\n CONSTRAINT `VLANIPv4-FK-ipv4net_id` FOREIGN KEY (`ipv4net_id`) REFERENCES `IPv4Network` (`id`) ON DELETE CASCADE\n) ENGINE=InnoDB\n";
$query[] = "\nCREATE TABLE `VLANSTRule` (\n `vst_id` int(10) unsigned NOT NULL,\n `rule_no` int(10) unsigned NOT NULL,\n `port_pcre` char(255) NOT NULL,\n `port_role` enum('access','trunk','uplink','downlink','none') NOT NULL default 'none',\n `wrt_vlans` char(255) default NULL,\n `description` char(255) default NULL,\n UNIQUE KEY `vst-rule` (`vst_id`,`rule_no`),\n CONSTRAINT `VLANSTRule-FK-vst_id` FOREIGN KEY (`vst_id`) REFERENCES `VLANSwitchTemplate` (`id`) ON DELETE CASCADE\n) ENGINE=InnoDB\n";
$query[] = "\nCREATE TABLE `VLANSwitch` (\n `object_id` int(10) unsigned NOT NULL,\n `domain_id` int(10) unsigned NOT NULL,\n `template_id` int(10) unsigned NOT NULL,\n `mutex_rev` int(10) unsigned NOT NULL default '0',\n `out_of_sync` enum('yes','no') NOT NULL default 'yes',\n `last_errno` int(10) unsigned NOT NULL default '0',\n `last_change` timestamp NOT NULL default '0000-00-00 00:00:00',\n `last_push_started` timestamp NOT NULL default '0000-00-00 00:00:00',\n `last_push_finished` timestamp NOT NULL default '0000-00-00 00:00:00',\n `last_error_ts` timestamp NOT NULL default '0000-00-00 00:00:00',\n UNIQUE KEY `object_id` (`object_id`),\n KEY `domain_id` (`domain_id`),\n KEY `template_id` (`template_id`),\n KEY `out_of_sync` (`out_of_sync`),\n KEY `last_errno` (`last_errno`),\n CONSTRAINT `VLANSwitch-FK-domain_id` FOREIGN KEY (`domain_id`) REFERENCES `VLANDomain` (`id`),\n CONSTRAINT `VLANSwitch-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`),\n CONSTRAINT `VLANSwitch-FK-template_id` FOREIGN KEY (`template_id`) REFERENCES `VLANSwitchTemplate` (`id`)\n) ENGINE=InnoDB\n";
$query[] = "\nCREATE TABLE `VLANSwitchTemplate` (\n `id` int(10) unsigned NOT NULL auto_increment,\n `max_local_vlans` int(10) unsigned default NULL,\n `description` char(255) default NULL,\n PRIMARY KEY (`id`),\n UNIQUE KEY `description` (`description`)\n) ENGINE=InnoDB\n";
$query[] = "\nCREATE TABLE `VLANValidID` (\n `vlan_id` int(10) unsigned NOT NULL default '1',\n PRIMARY KEY (`vlan_id`)\n) ENGINE=InnoDB\n";
$query[] = "SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS";
for ($i = 1; $i <= 4094; $i++) {
$query[] = "INSERT INTO VLANValidID (vlan_id) VALUES ({$i})";
}
$query[] = "UPDATE Config SET varvalue = '0.18.0' WHERE varname = 'DB_VERSION'";
break;
case '0.18.1':
$query[] = "ALTER TABLE Atom ENGINE=InnoDB";
$query[] = "ALTER TABLE AttributeMap ENGINE=InnoDB";
$query[] = "ALTER TABLE Config ENGINE=InnoDB";
$query[] = "ALTER TABLE IPv4Address ENGINE=InnoDB";
$query[] = "ALTER TABLE IPv4Allocation ENGINE=InnoDB";
$query[] = "ALTER TABLE Molecule ENGINE=InnoDB";
$query[] = "ALTER TABLE MountOperation ENGINE=InnoDB";
$query[] = "ALTER TABLE PortCompat ENGINE=InnoDB";
$query[] = "ALTER TABLE Rack ENGINE=InnoDB";
$query[] = "ALTER TABLE RackHistory ENGINE=InnoDB";
$query[] = "ALTER TABLE RackObjectHistory ENGINE=InnoDB";
$query[] = "ALTER TABLE RackRow ENGINE=InnoDB";
$query[] = "ALTER TABLE RackSpace ENGINE=InnoDB";
$query[] = "ALTER TABLE Script ENGINE=InnoDB";
$query[] = "ALTER TABLE AttributeValue DROP FOREIGN KEY `AttributeValue-FK-object_id`";
$query[] = "ALTER TABLE AttributeValue ADD CONSTRAINT `AttributeValue-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE";
$query[] = "ALTER TABLE RackObjectHistory ADD KEY (id)";
$query[] = "ALTER TABLE RackObjectHistory ADD CONSTRAINT `RackObjectHistory-FK-object_id` FOREIGN KEY (id) REFERENCES `RackObject` (`id`) ON DELETE CASCADE";
$query[] = "ALTER TABLE MountOperation ADD CONSTRAINT `MountOperation-FK-object_id` FOREIGN KEY (object_id) REFERENCES RackObject (id) ON DELETE CASCADE";
$query[] = "ALTER TABLE RackSpace ADD CONSTRAINT `RackSpace-FK-object_id` FOREIGN KEY (object_id) REFERENCES RackObject (id) ON DELETE CASCADE";
$query[] = "ALTER TABLE Link DROP FOREIGN KEY `Link-FK-a`";
$query[] = "ALTER TABLE Link ADD CONSTRAINT `Link-FK-a` FOREIGN KEY (`porta`) REFERENCES `Port` (`id`) ON DELETE CASCADE";
$query[] = "ALTER TABLE Link DROP FOREIGN KEY `Link-FK-b`";
$query[] = "ALTER TABLE Link ADD CONSTRAINT `Link-FK-b` FOREIGN KEY (`portb`) REFERENCES `Port` (`id`) ON DELETE CASCADE";
$query[] = "ALTER TABLE Port DROP FOREIGN KEY `Port-FK-object_id`";
$query[] = "ALTER TABLE Port ADD CONSTRAINT `Port-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE";
$query[] = "ALTER TABLE AttributeMap MODIFY `chapter_id` int(10) unsigned default NULL";
$query[] = "ALTER TABLE IPv4Address MODIFY `ip` int(10) unsigned NOT NULL default '0'";
$query[] = "ALTER TABLE IPv4Address MODIFY `name` char(255) NOT NULL default ''";
$query[] = "ALTER TABLE IPv4Allocation MODIFY `object_id` int(10) unsigned NOT NULL default '0'";
$query[] = "ALTER TABLE IPv4Allocation MODIFY `ip` int(10) unsigned NOT NULL default '0'";
$query[] = "ALTER TABLE IPv4Allocation MODIFY `name` char(255) NOT NULL default ''";
$query[] = "ALTER TABLE IPv4NAT MODIFY `object_id` int(10) unsigned NOT NULL default '0'";
$query[] = "ALTER TABLE IPv4NAT MODIFY `proto` enum('TCP','UDP') NOT NULL default 'TCP'";
$query[] = "ALTER TABLE IPv4NAT MODIFY `localip` int(10) unsigned NOT NULL default '0'";
$query[] = "ALTER TABLE IPv4NAT MODIFY `localport` smallint(5) unsigned NOT NULL default '0'";
$query[] = "ALTER TABLE IPv4NAT MODIFY `remoteip` int(10) unsigned NOT NULL default '0'";
$query[] = "ALTER TABLE IPv4NAT MODIFY `remoteport` smallint(5) unsigned NOT NULL default '0'";
$query[] = "ALTER TABLE IPv4Network MODIFY `ip` int(10) unsigned NOT NULL default '0'";
$query[] = "ALTER TABLE IPv4Network MODIFY `mask` int(10) unsigned NOT NULL default '0'";
$query[] = "ALTER TABLE Link MODIFY `porta` int(10) unsigned NOT NULL default '0'";
$query[] = "ALTER TABLE Link MODIFY `portb` int(10) unsigned NOT NULL default '0'";
$query[] = "ALTER TABLE MountOperation MODIFY `object_id` int(10) unsigned NOT NULL default '0'";
$query[] = "ALTER TABLE MountOperation MODIFY `ctime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP";
$query[] = "ALTER TABLE Port MODIFY `object_id` int(10) unsigned NOT NULL default '0'";
$query[] = "ALTER TABLE Port MODIFY `name` char(255) NOT NULL default ''";
$query[] = "ALTER TABLE Port MODIFY `type` int(10) unsigned NOT NULL default '0'";
$query[] = "ALTER TABLE PortCompat MODIFY `type1` int(10) unsigned NOT NULL default '0'";
$query[] = "ALTER TABLE PortCompat MODIFY `type2` int(10) unsigned NOT NULL default '0'";
$query[] = "ALTER TABLE RackHistory MODIFY `ctime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP";
$query[] = "ALTER TABLE RackObjectHistory MODIFY `ctime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP";
$query[] = "ALTER TABLE TagStorage MODIFY `tag_id` int(10) unsigned NOT NULL default '0'";
$query[] = "ALTER TABLE UserAccount MODIFY `user_name` char(64) NOT NULL default ''";
$query[] = "UPDATE Config SET varvalue = '0.18.1' WHERE varname = 'DB_VERSION'";
break;
case '0.18.2':
$query[] = "ALTER TABLE Rack ADD CONSTRAINT `Rack-FK-row_id` FOREIGN KEY (row_id) REFERENCES RackRow (id)";
$query[] = "ALTER TABLE RackRow ADD UNIQUE KEY `name` (name)";
$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')";
$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')";
$query[] = "UPDATE Config SET varvalue = '0.18.2' WHERE varname = 'DB_VERSION'";
break;
case '0.18.3':
$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'";
$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)')";
$query[] = "UPDATE Config SET varvalue = '0.18.3' WHERE varname = 'DB_VERSION'";
break;
case '0.18.4':
$query[] = "ALTER TABLE VLANSTRule MODIFY port_role enum('access','trunk','anymode','uplink','downlink','none') NOT NULL default 'none'";
$query[] = "UPDATE Config SET varvalue = '0.18.4' WHERE varname = 'DB_VERSION'";
break;
case '0.18.5':
$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')";
$query[] = "ALTER TABLE `IPv4LB` ADD COLUMN `prio` int(10) unsigned DEFAULT NULL AFTER `vs_id`";
$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')";
$query[] = "UPDATE Config SET varvalue = '0.18.5' WHERE varname = 'DB_VERSION'";
break;
case '0.18.6':
$query[] = "UPDATE Config SET varvalue = '0.18.6' WHERE varname = 'DB_VERSION'";
break;
case '0.18.7':
$query[] = "UPDATE Config SET varvalue = '0.18.7' WHERE varname = 'DB_VERSION'";
break;
case '0.19.0':
$query[] = 'ALTER TABLE `File` ADD `thumbnail` LONGBLOB NULL AFTER `atime`';
$query[] = "\nCREATE TABLE `IPv6Address` (\n `ip` binary(16) NOT NULL,\n `name` char(255) NOT NULL default '',\n `reserved` enum('yes','no') default NULL,\n PRIMARY KEY (`ip`)\n) ENGINE=InnoDB\n";
$query[] = "\nCREATE TABLE `IPv6Allocation` (\n `object_id` int(10) unsigned NOT NULL default '0',\n `ip` binary(16) NOT NULL,\n `name` char(255) NOT NULL default '',\n `type` enum('regular','shared','virtual','router') default NULL,\n PRIMARY KEY (`object_id`,`ip`),\n CONSTRAINT `IPv6Allocation-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE\n) ENGINE=InnoDB\n";
$query[] = "\nCREATE TABLE `IPv6Network` (\n `id` int(10) unsigned NOT NULL auto_increment,\n `ip` binary(16) NOT NULL,\n `mask` int(10) unsigned NOT NULL,\n `last_ip` binary(16) NOT NULL,\n `name` char(255) default NULL,\n `comment` text,\n PRIMARY KEY (`id`),\n UNIQUE KEY `ip` (`ip`,`mask`)\n) ENGINE=InnoDB\n";
$query[] = "\nCREATE TABLE `VLANIPv6` (\n `domain_id` int(10) unsigned NOT NULL,\n `vlan_id` int(10) unsigned NOT NULL,\n `ipv6net_id` int(10) unsigned NOT NULL,\n UNIQUE KEY `network-domain` (`ipv6net_id`,`domain_id`),\n KEY `VLANIPv6-FK-compound` (`domain_id`,`vlan_id`),\n CONSTRAINT `VLANIPv6-FK-compound` FOREIGN KEY (`domain_id`, `vlan_id`) REFERENCES `VLANDescription` (`domain_id`, `vlan_id`) ON DELETE CASCADE,\n CONSTRAINT `VLANIPv6-FK-ipv6net_id` FOREIGN KEY (`ipv6net_id`) REFERENCES `IPv6Network` (`id`) ON DELETE CASCADE\n) ENGINE=InnoDB\n";
$query[] = "\nCREATE TABLE IF NOT EXISTS `ObjectLog` (\n `id` int(10) NOT NULL AUTO_INCREMENT,\n `object_id` int(10) NOT NULL,\n `user` varchar(64) NOT NULL,\n `date` datetime NOT NULL,\n `content` text NOT NULL,\n PRIMARY KEY (`id`)\n) ENGINE=InnoDB\n";
# Now we have the same structure of ObjectLog table, which objectlog.php
# could have left. Subsequent column updates will handle any existing data.
$query[] = "ALTER TABLE ObjectLog MODIFY COLUMN `id` int(10) unsigned NOT NULL AUTO_INCREMENT";
$query[] = "ALTER TABLE ObjectLog MODIFY COLUMN `object_id` int(10) unsigned NOT NULL";
$query[] = "ALTER TABLE ObjectLog MODIFY COLUMN `user` char(64) NOT NULL";
$query[] = "ALTER TABLE ObjectLog ADD KEY `object_id` (`object_id`)";
$query[] = "ALTER TABLE ObjectLog ADD KEY `date` (`date`)";
$query[] = "ALTER TABLE ObjectLog ADD CONSTRAINT `ObjectLog-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE";
# Now it's the way 0.19.0 is expecting it to be.
$query[] = "\nCREATE TABLE `ObjectParentCompat` (\n `parent_objtype_id` int(10) unsigned NOT NULL,\n `child_objtype_id` int(10) unsigned NOT NULL,\n UNIQUE KEY `parent_child` (`parent_objtype_id`,`child_objtype_id`)\n) ENGINE=InnoDB\n";
$query[] = "\nCREATE TABLE `EntityLink` (\n `id` int(10) unsigned NOT NULL AUTO_INCREMENT,\n `parent_entity_type` enum('ipv4net','ipv4rspool','ipv4vs','ipv6net','object','rack','user') NOT NULL,\n `parent_entity_id` int(10) unsigned NOT NULL,\n `child_entity_type` enum('file','object') NOT NULL,\n `child_entity_id` int(10) unsigned NOT NULL,\n PRIMARY KEY (`id`),\n UNIQUE KEY `EntityLink-unique` (`parent_entity_type`,`parent_entity_id`,`child_entity_type`,`child_entity_id`)\n) ENGINE=InnoDB\n";
$query[] = "ALTER TABLE `TagStorage` CHANGE COLUMN `entity_realm` `entity_realm` ENUM('file','ipv4net','ipv4vs','ipv4rspool','object','rack','user','ipv6net') NOT NULL DEFAULT 'object' FIRST";
$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`";
$query[] = 'ALTER TABLE Link ADD COLUMN cable char(64) NULL AFTER portb';
$query[] = 'ALTER TABLE RackSpace ADD CONSTRAINT `RackSpace-FK-rack_id` FOREIGN KEY (rack_id) REFERENCES Rack (id)';
$query[] = "ALTER TABLE `IPv4Allocation` ADD CONSTRAINT `IPv4Allocation-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE";
$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')";
$query[] = "ALTER TABLE `VLANSwitchTemplate` ADD COLUMN `mutex_rev` int(10) NOT NULL AFTER `id`";
$query[] = "ALTER TABLE `VLANSwitchTemplate` ADD COLUMN `saved_by` char(64) NOT NULL AFTER `description`";
$query[] = "INSERT INTO `Attribute` (`id`, `type`, `name`) VALUES (26,'dict','Hypervisor')";
$query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (29,'no','Yes/No')";
$query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (30,'no','network chassis models')";
$query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (31,'no','server chassis models')";
$query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (32,'no','virtual switch models')";
$query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (33,'no','virtual switch OS type')";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (4,26,29)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1502,1,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1502,2,31)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1502,3,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1502,14,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1502,20,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1502,21,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1502,22,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,1,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,2,30)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,3,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,4,14)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,5,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,14,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,16,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,17,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,18,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,20,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,21,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,22,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1503,24,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1504,3,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1504,4,13)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1504,14,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1504,24,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1505,14,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1506,14,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1506,17,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1506,18,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,1,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,2,32)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,3,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,4,33)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,5,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,14,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,20,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,21,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1507,22,NULL)";
$query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (3,13)";
$query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (4,1504)";
$query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (4,1507)";
$query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1502,4)";
$query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1503,8)";
$query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1505,4)";
$query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1505,1504)";
$query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1505,1506)";
$query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1505,1507)";
$query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1506,1504)";
$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')";
$query[] = "UPDATE Config SET varvalue = CONCAT(varvalue, ' or {\$typeid_1502} or {\$typeid_1503} or {\$typeid_1504} or {\$typeid_1507}') WHERE varname = 'IPV4OBJ_LISTSRC'";
$query[] = "UPDATE Config SET varvalue = '8' WHERE varname = 'MASSCOUNT'";
$query[] = "UPDATE RackObject SET label = NULL WHERE label = ''";
// Move barcode data so the column can be dropped
$result = $dbxlink->query('SELECT id, objtype_id, barcode FROM RackObject WHERE barcode IS NOT NULL');
$rows = $result->fetchAll(PDO::FETCH_ASSOC);
unset($result);
foreach ($rows as $row) {
// Determine if this object type has the 'OEM S/N 1' attribute associated with it, and if it's set
$sn_query = "SELECT (SELECT COUNT(*) FROM AttributeMap WHERE objtype_id={$row['objtype_id']} AND attr_id=1) AS AM_count, ";
$sn_query .= "(SELECT COUNT(*) FROM AttributeValue WHERE object_id={$row['id']} AND attr_id=1) AS AV_count";
$sn_result = $dbxlink->query($sn_query);
$sn_row = $sn_result->fetch(PDO::FETCH_ASSOC);
if ($sn_row['AM_count'] == 1 && $sn_row['AV_count'] == 0) {
// 'OEM S/N 1' attribute is mapped to this object type, but it is not set. Good!
// Copy the barcode value to the attribute.
$query[] = "INSERT INTO AttributeValue (`object_id`, `attr_id`, `string_value`) VALUES ({$row['id']}, 1, '{$row['barcode']}')";
} else {
// Some other set of circumstances. Not as good!
// Copy the barcode value to a new ObjectLog record.
$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']}')";
}
unset($sn_query, $sn_result, $sn_row);
}
$query[] = 'ALTER TABLE RackObject DROP COLUMN `barcode`';
$query[] = 'ALTER TABLE RackObjectHistory DROP COLUMN `barcode`';
$query[] = 'ALTER TABLE `VLANSwitchTemplate` DROP COLUMN `max_local_vlans`';
$query[] = "UPDATE Config SET varvalue = '0.19.0' WHERE varname = 'DB_VERSION'";
break;
case '0.19.1':
$query[] = "ALTER TABLE `Config` CHANGE COLUMN `varvalue` `varvalue` text NOT NULL";
$query[] = "ALTER TABLE `UserConfig` CHANGE COLUMN `varvalue` `varvalue` text NOT NULL";
$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')";
$result = $dbxlink->query("SHOW TABLES LIKE 'Objectlog'");
$rows = $result->fetchAll(PDO::FETCH_ASSOC);
unset($result);
if (count($rows)) {
# Now the ObjectLog merge... again, because the original table is named
# "Objectlog". The job is to merge contents of Objectlog and ObjectLog
# into the latter.
$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)";
$query[] = "DELETE FROM Objectlog WHERE object_id IN(SELECT id FROM RackObject)";
# Don't delete the old table, if the merge wasn't exhaustive.
$result = $dbxlink->query('SELECT COUNT(*) AS c FROM Objectlog WHERE object_id NOT IN(SELECT id FROM RackObject)');
$row = $result->fetch(PDO::FETCH_ASSOC);
unset($result);
if ($row['c'] == 0) {
$query[] = 'DROP TABLE Objectlog';
} else {
$query[] = 'ALTER TABLE Objectlog RENAME TO Objectlog_old_unmerged';
}
}
$query[] = "UPDATE Config SET varvalue = '0.19.1' WHERE varname = 'DB_VERSION'";
break;
case '0.19.2':
$query[] = "ALTER TABLE IPv4Allocation ADD KEY `ip` (`ip`)";
$query[] = "ALTER TABLE IPv6Allocation ADD KEY `ip` (`ip`)";
$query[] = "ALTER TABLE IPv4VS ADD KEY `vip` (`vip`)";
$query[] = "ALTER TABLE IPv4RS ADD KEY `rsip` (`rsip`)";
$query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (34,'no','power supply chassis models')";
$query[] = "INSERT INTO `Chapter` (`id`, `sticky`, `name`) VALUES (35,'no','power supply models')";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1397,1,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1397,2,34)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1397,14,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1397,21,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1397,22,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1398,1,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1398,2,35)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1398,14,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1398,21,NULL)";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`, `attr_id`, `chapter_id`) VALUES (1398,22,NULL)";
$query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1397,1398)";
$query[] = "INSERT INTO `PortCompat` (`type1`, `type2`) VALUES (1399,1399)";
$query[] = "INSERT INTO `PortInterfaceCompat` (`iif_id`, `oif_id`) VALUES (1,1399)";
$query[] = "UPDATE Config SET varvalue = CONCAT(varvalue, ' or {\$typeid_1397}') WHERE varname = 'IPV4OBJ_LISTSRC'";
$query[] = "ALTER TABLE AttributeValue ADD KEY `attr_id-uint_value` (`attr_id`,`uint_value`)";
$query[] = "ALTER TABLE AttributeValue ADD KEY `attr_id-string_value` (`attr_id`,`string_value`(12))";
$query[] = "UPDATE Config SET varvalue = '0.19.2' WHERE varname = 'DB_VERSION'";
break;
case '0.19.3':
$query[] = "DELETE FROM RackSpace WHERE object_id IS NULL AND state = 'T'";
$query[] = "UPDATE Config SET varvalue = '0.19.3' WHERE varname = 'DB_VERSION'";
break;
case '0.19.4':
$query[] = "UPDATE Config SET varvalue = '0.19.4' WHERE varname = 'DB_VERSION'";
break;
case '0.19.5':
// Add 'virtual port' to 'virtual port' mapping
$query[] = "INSERT INTO `PortCompat` (`type1`,`type2`) VALUES (1469,1469)";
$query[] = "INSERT INTO `PortInterfaceCompat` (`iif_id`,`oif_id`) VALUES (1,1469)";
$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')";
$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')";
$query[] = "UPDATE Link SET cable = NULL WHERE cable = ''";
$query[] = "ALTER TABLE AttributeValue MODIFY string_value char(255) DEFAULT NULL";
$query[] = "UPDATE Config SET varvalue = '0.19.5' WHERE varname = 'DB_VERSION'";
break;
case '0.19.6':
$query[] = "UPDATE Config SET varvalue = '0.19.6' WHERE varname = 'DB_VERSION'";
break;
case '0.19.7':
# A plain "ALTER TABLE Attribute" can leave AUTO_INCREMENT in an odd
# state, hence the table swap.
$query[] = "\nCREATE TABLE `Attribute_new` (\n `id` int(10) unsigned NOT NULL auto_increment,\n `type` enum('string','uint','float','dict') default NULL,\n `name` char(64) default NULL,\n PRIMARY KEY (`id`),\n UNIQUE KEY `name` (`name`)\n) ENGINE=InnoDB\n";
$query[] = "INSERT INTO Attribute_new SELECT * FROM Attribute";
$query[] = "INSERT INTO Attribute_new VALUES (9999, 'string', 'base MAC address')";
$query[] = "DROP TABLE Attribute";
$query[] = "ALTER TABLE Attribute_new RENAME TO Attribute";
$query[] = "ALTER TABLE AttributeMap ADD KEY (attr_id)";
$query[] = "DELETE FROM AttributeMap WHERE attr_id NOT IN (SELECT id FROM Attribute)";
$query[] = "ALTER TABLE AttributeMap ADD CONSTRAINT `AttributeMap-FK-attr_id` FOREIGN KEY (attr_id) REFERENCES Attribute (id)";
$query[] = "DELETE FROM AttributeValue WHERE attr_id NOT IN (SELECT attr_id FROM AttributeMap)";
$query[] = "ALTER TABLE AttributeValue ADD CONSTRAINT `AttributeValue-FK-attr_id` FOREIGN KEY (attr_id) REFERENCES AttributeMap (attr_id)";
$query[] = "INSERT INTO `ObjectParentCompat` (`parent_objtype_id`, `child_objtype_id`) VALUES (1506,4)";
$query[] = "INSERT INTO PortInnerInterface (id, iif_name) VALUES (10, 'QSFP+')";
$query[] = "INSERT INTO PortInterfaceCompat VALUES (10, 1588)";
$query[] = "UPDATE Config SET varvalue = '0.19.7' WHERE varname = 'DB_VERSION'";
break;
case '0.19.8':
for ($i = 1424; $i <= 1466; $i++) {
# CX, then 42 ER channels
$query[] = "INSERT INTO `PortCompat` (`type1`, `type2`) VALUES ({$i},{$i})";
}
$query[] = "ALTER TABLE UserAccount ENGINE=InnoDB";
$query[] = "DELETE FROM UserConfig WHERE user NOT IN (SELECT user_name FROM UserAccount)";
$query[] = "ALTER TABLE UserConfig ADD CONSTRAINT `UserConfig-FK-user` FOREIGN KEY (user) REFERENCES UserAccount (user_name) ON DELETE CASCADE";
$query[] = "DELETE FROM UserConfig WHERE varname NOT IN (SELECT varname FROM Config)";
$query[] = "ALTER TABLE UserConfig ADD KEY (varname)";
$query[] = "ALTER TABLE UserConfig ADD CONSTRAINT `UserConfig-FK-varname` FOREIGN KEY (varname) REFERENCES Config (varname) ON DELETE CASCADE";
$query[] = "ALTER TABLE Dictionary ENGINE=InnoDB";
$query[] = "ALTER TABLE Chapter ENGINE=InnoDB";
$query[] = "UPDATE Chapter SET id = 9999 WHERE id = 22";
$query[] = "UPDATE AttributeMap SET chapter_id = 9999 WHERE chapter_id = 22";
$query[] = "UPDATE Dictionary SET chapter_id = 9999 WHERE chapter_id = 22";
$query[] = "DELETE FROM Dictionary WHERE chapter_id NOT IN (SELECT id FROM Chapter)";
$query[] = "ALTER TABLE Dictionary ADD CONSTRAINT `Dictionary-FK-chapter_id` FOREIGN KEY (chapter_id) REFERENCES Chapter (id)";
$query[] = "DELETE FROM AttributeMap WHERE chapter_id NOT IN (SELECT id FROM Chapter)";
$query[] = "ALTER TABLE AttributeMap ADD KEY (chapter_id)";
$query[] = "ALTER TABLE AttributeMap ADD CONSTRAINT `AttributeMap-FK-chapter_id` FOREIGN KEY (chapter_id) REFERENCES Chapter (id)";
$query[] = "\nCREATE TABLE `CactiGraph` (\n `object_id` int(10) unsigned NOT NULL,\n `graph_id` int(10) unsigned NOT NULL,\n `caption` char(255) DEFAULT NULL,\n PRIMARY KEY (`graph_id`),\n KEY `object_id` (`object_id`),\n CONSTRAINT `CactiGraph-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE\n) ENGINE=InnoDB;\n";
$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')";
$query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('CACTI_URL','','string','yes','no','no','Cacti server base URL')";
$query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('CACTI_USERNAME','','string','yes','no','no','Cacti user account')";
$query[] = "INSERT INTO `Config` (varname, varvalue, vartype, emptyok, is_hidden, is_userdefined, description) VALUES ('CACTI_USERPASS','','string','yes','no','no','Cacti user password')";
$query[] = "UPDATE Config SET varvalue = '0.19.8' WHERE varname = 'DB_VERSION'";
break;
case '0.19.9':
$query[] = "DELETE FROM Config WHERE varname = 'HNDP_RUNNERS_LISTSRC'";
# Dismiss some overly-specific OIF types in favour of more generic counterparts.
$squeeze = array(1202 => array(25, 26), 1204 => array(27, 28), 1196 => array(22, 23), 1195 => array(20, 21, 1083));
foreach ($squeeze as $stays => $leaves) {
$csv = implode(', ', $leaves);
$query[] = "DELETE FROM PortCompat WHERE type1 IN({$csv}) OR type2 IN({$csv})";
$query[] = "INSERT IGNORE INTO PortInterfaceCompat (iif_id, oif_id) SELECT iif_id, {$stays} FROM Port WHERE type IN ({$csv})";
$query[] = "UPDATE Port SET type = {$stays} WHERE type IN({$csv})";
$query[] = "DELETE FROM PortInterfaceCompat WHERE oif_id IN({$csv})";
}
$query[] = "UPDATE Config SET varvalue = '0.19.9' WHERE varname = 'DB_VERSION'";
break;
case '0.19.10':
$query[] = "INSERT INTO `PortCompat` (`type1`, `type2`) VALUES (1603,1603)";
$query[] = "UPDATE Config SET varvalue = '0.19.10' WHERE varname = 'DB_VERSION'";
break;
case '0.19.11':
$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')";
$query[] = "INSERT INTO `Attribute` (`id`,`type`,`name`) VALUES (28,'string','Slot number')";
$query[] = 'INSERT INTO `AttributeMap` (`objtype_id`,`attr_id`,`chapter_id`) VALUES (4,28,NULL)';
$query[] = 'INSERT INTO `AttributeMap` (`objtype_id`,`attr_id`,`chapter_id`) VALUES (8,28,NULL)';
$query[] = 'INSERT INTO `AttributeMap` (`objtype_id`,`attr_id`,`chapter_id`) VALUES (798,28,NULL)';
$query[] = 'INSERT INTO `AttributeMap` (`objtype_id`,`attr_id`,`chapter_id`) VALUES (1055,28,NULL)';
$query[] = 'ALTER TABLE AttributeValue ADD COLUMN object_tid int(10) unsigned NOT NULL default 0 AFTER object_id';
$query[] = 'UPDATE AttributeValue SET object_tid = (SELECT objtype_id FROM RackObject WHERE id = object_id)';
$query[] = 'ALTER TABLE AttributeValue DROP FOREIGN KEY `AttributeValue-FK-attr_id`';
$query[] = 'ALTER TABLE AttributeValue DROP FOREIGN KEY `AttributeValue-FK-object_id`';
$query[] = 'ALTER TABLE AttributeValue ADD KEY `id-tid` (object_id, object_tid)';
$query[] = 'ALTER TABLE AttributeValue ADD KEY `object_tid-attr_id` (`object_tid`,`attr_id`)';
$query[] = 'ALTER TABLE RackObject ADD KEY `id-tid` (id, objtype_id)';
$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';
$query[] = 'ALTER TABLE AttributeValue ADD CONSTRAINT `AttributeValue-FK-map` FOREIGN KEY (`object_tid`, `attr_id`) REFERENCES `AttributeMap` (`objtype_id`, `attr_id`)';
# 0.19.9 did it right, but kept the IDs in the dictionary. This time
# the dictionary is reduced, but the procedure needs to be repeated,
# in case the user had enough time to use the wrong IDs again.
$squeeze = array(1202 => array(25, 26), 1204 => array(27, 28), 1196 => array(22, 23), 1195 => array(20, 21, 1083));
foreach ($squeeze as $stays => $leaves) {
$csv = implode(', ', $leaves);
$query[] = "DELETE FROM PortCompat WHERE type1 IN({$csv}) OR type2 IN({$csv})";
$query[] = "INSERT IGNORE INTO PortInterfaceCompat (iif_id, oif_id) SELECT iif_id, {$stays} FROM Port WHERE type IN ({$csv})";
$query[] = "UPDATE Port SET type = {$stays} WHERE type IN({$csv})";
$query[] = "DELETE FROM PortInterfaceCompat WHERE oif_id IN({$csv})";
}
$query[] = "INSERT INTO `PortCompat` (`type1`, `type2`) VALUES (1642,1642)";
$query[] = 'ALTER TABLE `EntityLink` ADD KEY `EntityLink-compound` (`parent_entity_type`,`child_entity_type`,`child_entity_id`)';
$query[] = "UPDATE Config SET varvalue = '0.19.11' WHERE varname = 'DB_VERSION'";
break;
# Batch 0.19.12 contained minor, but annoying bugs and was modified after the
# release of version 0.19.12 (which should be avoided if possible). The best
# way to resolve this particular case was to recall RackTables-0.19.12.tar.gz
# from the download area and provide RackTables-0.19.13.tar.gz containing the
# modified 0.19.12 batch.
# Batch 0.19.12 contained minor, but annoying bugs and was modified after the
# release of version 0.19.12 (which should be avoided if possible). The best
# way to resolve this particular case was to recall RackTables-0.19.12.tar.gz
# from the download area and provide RackTables-0.19.13.tar.gz containing the
# modified 0.19.12 batch.
case '0.19.12':
$query[] = "DELETE FROM Config WHERE varname IN('color_F', 'color_A', 'color_U', 'color_T', 'color_Th', 'color_Tw', 'color_Thw')";
$query[] = "INSERT INTO Chapter (id, sticky, name) VALUES (36,'no','serial console server models')";
$query[] = "INSERT INTO AttributeMap (objtype_id, attr_id, chapter_id) VALUES (1644, 1, NULL), (1644, 2, 36), (1644, 3, NULL)";
$query[] = "UPDATE Config SET varvalue = CONCAT(varvalue, '; 10=1588') WHERE varname = 'DEFAULT_PORT_OIF_IDS' AND 0 = INSTR(varvalue, '10=') ";
$query[] = "INSERT INTO PortInterfaceCompat VALUES (10,1663), (10,1664)";
$query[] = "INSERT INTO PortCompat VALUES (1588,1588), (1661,1661), (1663,1663), (1664,1664)";
$query[] = "INSERT INTO PortInnerInterface (id, iif_name) VALUES (11, 'CFP')";
$query[] = "INSERT INTO PortInterfaceCompat (iif_id, oif_id) VALUES (11,1668),(11,1669),(11,1670),(11,1671)";
$query[] = "INSERT INTO PortCompat (type1, type2) VALUES (1668,1668), (1669,1669), (1670,1670), (1671,1671)";
$query[] = "UPDATE Config SET varvalue = CONCAT(varvalue, '; 11=1668') WHERE varname = 'DEFAULT_PORT_OIF_IDS'";
$query[] = "INSERT INTO Chapter (id, sticky, name) VALUES (37, 'no', 'wireless OS type')";
$query[] = "INSERT INTO AttributeMap (objtype_id, attr_id, chapter_id) VALUES (965, 4, 37)";
$query[] = "UPDATE Config SET varvalue = '0.19.12' WHERE varname = 'DB_VERSION'";
break;
case '0.19.13':
// add the date attribute type
$query[] = "ALTER TABLE `Attribute` CHANGE COLUMN `type` `type` enum('string','uint','float','dict','date') DEFAULT NULL";
$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')";
$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')";
// port over existing fields to new date attr type
$query[] = "UPDATE Attribute SET type='date' WHERE id IN (21,22,24)";
$query[] = "UPDATE AttributeValue SET uint_value=UNIX_TIMESTAMP(STR_TO_DATE(string_value, '%m/%d/%Y')) WHERE attr_id IN(21,22,24)";
// some config variables should be configurable in per-user basis
$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')";
$query[] = "UPDATE Config SET varvalue = '0.19.13' WHERE varname = 'DB_VERSION'";
break;
case '0.19.14':
$query[] = "ALTER TABLE AttributeValue CHANGE COLUMN `object_id` `object_id` INT(10) UNSIGNED NOT NULL";
$query[] = "ALTER TABLE AttributeValue CHANGE COLUMN `attr_id` `attr_id` INT(10) UNSIGNED NOT NULL";
$query[] = "ALTER TABLE AttributeValue ADD PRIMARY KEY (`object_id`, `attr_id`), DROP INDEX `object_id`";
$query[] = "ALTER TABLE Dictionary ADD COLUMN `dict_sticky` enum('yes','no') DEFAULT 'no' AFTER `dict_key`";
$query[] = "UPDATE Dictionary SET dict_sticky = 'yes' WHERE dict_key < 50000";
$query[] = "ALTER TABLE Dictionary ADD UNIQUE KEY dict_unique (chapter_id, dict_value, dict_sticky)";
$query[] = "ALTER TABLE Dictionary DROP KEY `chap_to_val`";
$query[] = "UPDATE Config SET varvalue = '0.19.14' WHERE varname = 'DB_VERSION'";
break;
case '0.20.0':
$query[] = "\nCREATE TABLE `PortLog` (\n `id` int(10) unsigned NOT NULL AUTO_INCREMENT,\n `port_id` int(10) unsigned NOT NULL,\n `date` datetime NOT NULL,\n `user` varchar(64) NOT NULL,\n `message` text NOT NULL,\n PRIMARY KEY (`id`),\n KEY `port_id-date` (`port_id`,`date`),\n CONSTRAINT `PortLog_ibfk_1` FOREIGN KEY (`port_id`) REFERENCES `Port` (`id`) ON DELETE CASCADE\n) ENGINE=InnoDB;\n";
$query[] = "\nCREATE TABLE `IPv4Log` (\n `id` int(10) NOT NULL AUTO_INCREMENT,\n `ip` int(10) unsigned NOT NULL,\n `date` datetime NOT NULL,\n `user` varchar(64) NOT NULL,\n `message` text NOT NULL,\n PRIMARY KEY (`id`),\n KEY `ip-date` (`ip`,`date`)\n) ENGINE=InnoDB;\n";
$query[] = "\nCREATE TABLE `IPv6Log` (\n `id` int(10) NOT NULL AUTO_INCREMENT,\n `ip` binary(16) NOT NULL,\n `date` datetime NOT NULL,\n `user` varchar(64) NOT NULL,\n `message` text NOT NULL,\n PRIMARY KEY (`id`),\n KEY `ip-date` (`ip`,`date`)\n) ENGINE=InnoDB;\n";
$query[] = "ALTER TABLE `FileLink` MODIFY COLUMN `entity_type` ENUM('ipv4net','ipv4rspool','ipv4vs','ipv6net','location','object','rack','user') NOT NULL DEFAULT 'object'";
$query[] = "ALTER TABLE `TagStorage` MODIFY COLUMN `entity_realm` ENUM('file','ipv4net','ipv4rspool','ipv4vs','ipv6net','location','object','rack','user','vst') NOT NULL default 'object'";
$query[] = "ALTER TABLE `TagStorage` ADD COLUMN `user` char(64) DEFAULT NULL, ADD COLUMN `date` datetime DEFAULT NULL";
// Rename object tables and keys, 'name' no longer needs to be unique
$dbxlink->query('ALTER TABLE `RackObject` RENAME TO `Object`');
$dbxlink->query('ALTER TABLE `RackObjectHistory` RENAME TO `ObjectHistory`');
$dbxlink->query('ALTER TABLE `Object` DROP KEY `name`');
$query[] = 'ALTER TABLE `Object` DROP KEY `RackObject_asset_no`';
$query[] = 'ALTER TABLE `Object` ADD UNIQUE KEY `asset_no` (`asset_no`)';
$query[] = 'ALTER TABLE `Object` ADD KEY `type_id` (`objtype_id`,`id`)';
$query[] = 'ALTER TABLE `ObjectHistory` DROP FOREIGN KEY `RackObjectHistory-FK-object_id`';
$query[] = 'ALTER TABLE `ObjectHistory` ADD CONSTRAINT `ObjectHistory-FK-object_id` FOREIGN KEY (`id`) REFERENCES `Object` (`id`) ON DELETE CASCADE';
$query[] = 'ALTER TABLE `RackSpace` DROP FOREIGN KEY `RackSpace-FK-rack_id`';
// Rack height is now an attribute
$query[] = "INSERT INTO `Attribute` (`id`,`type`,`name`) VALUES (27,'uint','Height, units')";
$query[] = 'INSERT INTO `AttributeMap` (`objtype_id`,`attr_id`,`chapter_id`) VALUES (1560,27,NULL)';
// Racks are now sorted using an attribute
$query[] = "INSERT INTO `Attribute` (`id`,`type`,`name`) VALUES (29,'uint','Sort order')";
$query[] = 'INSERT INTO `AttributeMap` (`objtype_id`,`attr_id`,`chapter_id`) VALUES (1560,29,NULL)';
// Relate 'contact person' with locations
$query[] = 'INSERT INTO `AttributeMap` (`objtype_id`,`attr_id`,`chapter_id`) VALUES (1562,14,NULL)';
// Allow relationships between racks/rows/locations
$query[] = "ALTER TABLE `EntityLink` MODIFY COLUMN `parent_entity_type` ENUM('ipv4net','ipv4rspool','ipv4vs','ipv6net','location','object','rack','row','user') NOT NULL";
$query[] = "ALTER TABLE `EntityLink` MODIFY COLUMN `child_entity_type` ENUM('file','location','object','rack','row') NOT NULL";
// Turn rows into objects
$result = $dbxlink->query('SELECT * FROM RackRow');
$rows = $result->fetchAll(PDO::FETCH_ASSOC);
unset($result);
foreach ($rows as $row) {
$prepared = $dbxlink->prepare('INSERT INTO `Object` (`name`,`objtype_id`) VALUES (?,?)');
$prepared->execute(array($row['name'], 1561));
$row_id = $dbxlink->lastInsertId();
// Turn all racks in this row into objects
$result = $dbxlink->query("SELECT id, name, height, comment FROM Rack WHERE row_id={$row['id']} ORDER BY name");
$racks = $result->fetchAll(PDO::FETCH_ASSOC);
unset($result);
$sort_order = 1;
foreach ($racks as $rack) {
// Add the rack as an object, set the height and sort order as attributes, link the rack to the row,
// update rackspace, tags and files to reflect new rack_id, move history
$prepared = $dbxlink->prepare('INSERT INTO `Object` (`name`,`objtype_id`,`comment`) VALUES (?,?,?)');
$prepared->execute(array($rack['name'], 1560, $rack['comment']));
$rack_id = $dbxlink->lastInsertId();
$query[] = "INSERT INTO `AttributeValue` (`object_id`,`object_tid`,`attr_id`,`uint_value`) VALUES ({$rack_id},1560,27,{$rack['height']})";
$query[] = "INSERT INTO `AttributeValue` (`object_id`,`object_tid`,`attr_id`,`uint_value`) VALUES ({$rack_id},1560,29,{$sort_order})";
$query[] = "INSERT INTO `EntityLink` (`parent_entity_type`,`parent_entity_id`,`child_entity_type`,`child_entity_id`) VALUES ('row',{$row_id},'rack',{$rack_id})";
$query[] = "UPDATE `RackSpace` SET `rack_id`={$rack_id} WHERE `rack_id`={$rack['id']}";
$query[] = "UPDATE `Atom` SET `rack_id`={$rack_id} WHERE `rack_id`={$rack['id']}";
$query[] = "UPDATE `TagStorage` SET `entity_id`={$rack_id} WHERE `entity_realm`='rack' AND `entity_id`={$rack['id']}";
$query[] = "UPDATE `FileLink` SET `entity_id`={$rack_id} WHERE `entity_type`='rack' AND `entity_id`={$rack['id']}";
$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']}";
$sort_order++;
}
}
$query[] = 'ALTER TABLE `RackSpace` ADD CONSTRAINT `RackSpace-FK-rack_id` FOREIGN KEY (`rack_id`) REFERENCES `Object` (`id`)';
$query[] = 'DROP TABLE `Rack`';
$query[] = 'DROP TABLE `RackRow`';
$query[] = 'DROP TABLE `RackHistory`';
$query[] = "\nCREATE TABLE `RackThumbnail` (\n `rack_id` int(10) unsigned NOT NULL,\n `thumb_data` blob,\n UNIQUE KEY `rack_id` (`rack_id`),\n CONSTRAINT `RackThumbnail-FK-rack_id` FOREIGN KEY (`rack_id`) REFERENCES `Object` (`id`) ON DELETE CASCADE\n) ENGINE=InnoDB\n";
$query[] = "\nCREATE VIEW `Location` AS SELECT O.id, O.name, O.has_problems, O.comment, P.id AS parent_id, P.name AS parent_name\nFROM `Object` O\nLEFT JOIN (\n `Object` P INNER JOIN `EntityLink` EL\n ON EL.parent_entity_id = P.id AND P.objtype_id = 1562 AND EL.parent_entity_type = 'location' AND EL.child_entity_type = 'location'\n) ON EL.child_entity_id = O.id\nWHERE O.objtype_id = 1562\n";
$query[] = "\nCREATE VIEW `Row` AS SELECT O.id, O.name, L.id AS location_id, L.name AS location_name\n FROM `Object` O\n LEFT JOIN `EntityLink` EL ON O.id = EL.child_entity_id AND EL.parent_entity_type = 'location' AND EL.child_entity_type = 'row'\n LEFT JOIN `Object` L ON EL.parent_entity_id = L.id AND L.objtype_id = 1562\n WHERE O.objtype_id = 1561\n";
$query[] = "\nCREATE VIEW `Rack` AS SELECT O.id, O.name AS name, O.asset_no, O.has_problems, O.comment,\n AV_H.uint_value AS height,\n AV_S.uint_value AS sort_order,\n RT.thumb_data,\n R.id AS row_id,\n R.name AS row_name\n FROM `Object` O\n LEFT JOIN `AttributeValue` AV_H ON O.id = AV_H.object_id AND AV_H.attr_id = 27\n LEFT JOIN `AttributeValue` AV_S ON O.id = AV_S.object_id AND AV_S.attr_id = 29\n LEFT JOIN `RackThumbnail` RT ON O.id = RT.rack_id\n LEFT JOIN `EntityLink` EL ON O.id = EL.child_entity_id AND EL.parent_entity_type = 'row' AND EL.child_entity_type = 'rack'\n INNER JOIN `Object` R ON R.id = EL.parent_entity_id\n WHERE O.objtype_id = 1560\n";
$query[] = "\nCREATE VIEW `RackObject` AS SELECT id, name, label, objtype_id, asset_no, has_problems, comment FROM `Object`\n WHERE `objtype_id` NOT IN (1560, 1561, 1562)\n";
$query[] = "UPDATE `Chapter` SET `name` = 'ObjectType' WHERE `id` = 1";
$query[] = "DELETE FROM RackSpace WHERE object_id IS NULL AND state = 'T'";
$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')";
$query[] = "UPDATE `Config` SET is_userdefined='yes' WHERE varname='PROXIMITY_RANGE'";
$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')";
$query[] = "ALTER TABLE `IPv4LB` MODIFY `prio` varchar(255) DEFAULT NULL";
$query[] = "ALTER TABLE `IPv4Address` ADD COLUMN `comment` char(255) NOT NULL default '' AFTER `name`";
$query[] = "ALTER TABLE `IPv6Address` ADD COLUMN `comment` char(255) NOT NULL default '' AFTER `name`";
// change IP address format of IPv4VS and IPv4RS tables
convertSLBTablesToBinIPs();
// do not allow NULL allocation type
$query[] = "ALTER TABLE `IPv4Allocation` MODIFY `type` enum('regular','shared','virtual','router') NOT NULL DEFAULT 'regular'";
$query[] = "ALTER TABLE `IPv6Allocation` MODIFY `type` enum('regular','shared','virtual','router') NOT NULL DEFAULT 'regular'";
$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')";
// update some config variables which changed their defaults in this verison
replaceConfigVarValue('SHOW_LAST_TAB', 'yes');
replaceConfigVarValue('IPV4_TREE_SHOW_USAGE', 'no');
replaceConfigVarValue('IPV4LB_LISTSRC', 'false', '{$typeid_4}');
replaceConfigVarValue('FILTER_DEFAULT_ANDOR', 'and');
replaceConfigVarValue('FILTER_SUGGEST_EXTRA', 'yes');
replaceConfigVarValue('IPV4_TREE_RTR_AS_CELL', 'no');
replaceConfigVarValue('SSH_OBJS_LISTSRC', 'false', 'none');
replaceConfigVarValue('TELNET_OBJS_LISTSRC', 'false', 'none');
$query[] = "UPDATE Config SET varvalue = '0.20.0' WHERE varname = 'DB_VERSION'";
break;
case '0.20.1':
// some HW types were moved from the 'Network switch' chapter to the 'Network chassis' chapter
// change the type of affected objects to 'Network chassis'
$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))";
// new 'management interface' object type
$query[] = "INSERT INTO `Chapter` (`id`,`sticky`,`name`) VALUES (38,'no','management interface type')";
$query[] = "INSERT INTO `Attribute` (`id`,`type`,`name`) VALUES (30,'dict','Mgmt type')";
$query[] = "INSERT INTO `AttributeMap` (`objtype_id`,`attr_id`,`chapter_id`) VALUES (1787,3,NULL),(1787,14,NULL),(1787,30,38)";
$query[] = "UPDATE `Config` SET varvalue = CONCAT(varvalue, ' or {\$typeid_1787}') WHERE varname = 'IPV4OBJ_LISTSRC'";
$query[] = "INSERT INTO Config VALUES ('8021Q_EXTSYNC_LISTSRC','false','string','yes','no','no','List source: objects with extended 802.1Q sync')";
// constraints to prevent orphan records
$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";
$query[] = "DELETE FROM `MountOperation` WHERE old_molecule_id NOT IN (SELECT id FROM `Molecule`) OR new_molecule_id NOT IN (SELECT id FROM `Molecule`)";
$query[] = "ALTER TABLE `Atom` ADD CONSTRAINT `Atom-FK-molecule_id` FOREIGN KEY (`molecule_id`) REFERENCES `Molecule` (`id`) ON DELETE CASCADE";
$query[] = "ALTER TABLE `Atom` ADD CONSTRAINT `Atom-FK-rack_id` FOREIGN KEY (`rack_id`) REFERENCES `Object` (`id`) ON DELETE CASCADE";
$query[] = "ALTER TABLE `MountOperation` ADD CONSTRAINT `MountOperation-FK-old_molecule_id` FOREIGN KEY (`old_molecule_id`) REFERENCES `Molecule` (`id`) ON DELETE CASCADE";
$query[] = "ALTER TABLE `MountOperation` ADD CONSTRAINT `MountOperation-FK-new_molecule_id` FOREIGN KEY (`new_molecule_id`) REFERENCES `Molecule` (`id`) ON DELETE CASCADE";
# multiple Cacti servers
$query[] = "\nCREATE TABLE `CactiServer` (\n `id` int(10) unsigned NOT NULL auto_increment,\n `base_url` char(255) DEFAULT NULL,\n `username` char(64) DEFAULT NULL,\n `password` char(64) DEFAULT NULL,\n PRIMARY KEY (`id`)\n) ENGINE=InnoDB\n";
$query[] = "ALTER TABLE CactiGraph ADD COLUMN server_id int(10) unsigned NOT NULL AFTER object_id";
$result = $dbxlink->query('SELECT COUNT(*) AS cnt FROM CactiGraph');
$row = $result->fetch(PDO::FETCH_ASSOC);
unset($result);
$result = $dbxlink->query("SELECT varvalue FROM Config WHERE varname = 'CACTI_URL'");
$cacti_url_row = $result->fetch(PDO::FETCH_ASSOC);
unset($result);
if ($row['cnt'] != 0 || is_array($cacti_url_row) && strlen($cacti_url_row['varvalue'])) {
$query[] = "INSERT INTO CactiServer (id) VALUES (1)";
$query[] = "UPDATE CactiServer SET base_url = (SELECT varvalue FROM Config WHERE varname = 'CACTI_URL') WHERE id = 1";
$query[] = "UPDATE CactiServer SET username = (SELECT varvalue FROM Config WHERE varname = 'CACTI_USERNAME') WHERE id = 1";
$query[] = "UPDATE CactiServer SET password = (SELECT varvalue FROM Config WHERE varname = 'CACTI_USERPASS') WHERE id = 1";
$query[] = "UPDATE CactiGraph SET server_id = 1";
}
$query[] = "ALTER TABLE CactiGraph DROP PRIMARY KEY";
$query[] = "ALTER TABLE CactiGraph ADD PRIMARY KEY (server_id, graph_id)";
$query[] = "ALTER TABLE CactiGraph ADD KEY (graph_id)";
$query[] = "ALTER TABLE CactiGraph ADD CONSTRAINT `CactiGraph-FK-server_id` FOREIGN KEY (server_id) REFERENCES CactiServer (id)";
$query[] = "DELETE FROM Config WHERE varname IN('CACTI_URL', 'CACTI_USERNAME', 'CACTI_USERPASS')";
$query[] = "UPDATE Config SET varvalue = '0.20.1' WHERE varname = 'DB_VERSION'";
break;
case '0.20.2':
$query[] = "ALTER TABLE TagStorage ADD COLUMN tag_is_assignable ENUM('yes', 'no') NOT NULL default 'yes' AFTER tag_id";
$query[] = "ALTER TABLE TagStorage ADD KEY `tag_id-tag_is_assignable` (tag_id, tag_is_assignable)";
$query[] = "ALTER TABLE TagTree ADD COLUMN is_assignable ENUM('yes', 'no') NOT NULL default 'yes' AFTER parent_id";
$query[] = "ALTER TABLE TagTree ADD KEY `id-is_assignable` (id, is_assignable)";
$query[] = "ALTER TABLE TagStorage DROP FOREIGN KEY `TagStorage-FK-tag_id`";
$query[] = "ALTER TABLE TagStorage ADD CONSTRAINT `TagStorage-FK-TagTree` FOREIGN KEY (tag_id, tag_is_assignable) REFERENCES TagTree (id, is_assignable)";
$query[] = "UPDATE UserAccount SET user_realname = NULL WHERE user_realname = ''";
$query[] = "UPDATE Object SET comment = NULL WHERE comment = ''";
$query[] = "\nCREATE TABLE `MuninServer` (\n `id` int(10) unsigned NOT NULL auto_increment,\n `base_url` char(255) DEFAULT NULL,\n PRIMARY KEY (`id`)\n) ENGINE=InnoDB\n";
$query[] = "\nCREATE TABLE `MuninGraph` (\n `object_id` int(10) unsigned NOT NULL,\n `server_id` int(10) unsigned NOT NULL,\n `graph` char(255) NOT NULL,\n `caption` char(255) DEFAULT NULL,\n PRIMARY KEY (`object_id`,`server_id`,`graph`),\n KEY `server_id` (`server_id`),\n KEY `graph` (`graph`),\n CONSTRAINT `MuninGraph-FK-server_id` FOREIGN KEY (`server_id`) REFERENCES `MuninServer` (`id`),\n CONSTRAINT `MuninGraph-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `Object` (`id`) ON DELETE CASCADE\n) ENGINE=InnoDB\n";
$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')";
$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')";
$query[] = "ALTER TABLE VLANIPv4 ADD UNIQUE `network-domain-vlan` (ipv4net_id, domain_id, vlan_id)";
$query[] = "ALTER TABLE VLANIPv4 DROP KEY `network-domain`";
$query[] = "ALTER TABLE VLANIPv6 ADD UNIQUE `network-domain-vlan` (ipv6net_id, domain_id, vlan_id)";
$query[] = "ALTER TABLE VLANIPv6 DROP KEY `network-domain`";
$query[] = "UPDATE Config SET varvalue = '0.20.2' WHERE varname = 'DB_VERSION'";
break;
case '0.20.3':
$query[] = "UPDATE Config SET varvalue = '0.20.3' WHERE varname = 'DB_VERSION'";
break;
case '0.20.4':
$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')";
$query[] = "UPDATE AttributeValue INNER JOIN AttributeMap USING (attr_id) SET AttributeValue.uint_value = 1572 WHERE chapter_id = 12 AND uint_value = 162";
$query[] = "UPDATE AttributeValue INNER JOIN AttributeMap USING (attr_id) SET AttributeValue.uint_value = 1710 WHERE chapter_id = 12 AND uint_value = 163";
$query[] = "UPDATE Config SET varvalue = '0.20.4' WHERE varname = 'DB_VERSION'";
break;
case 'dictionary':
$query = reloadDictionary();
break;
default:
return NULL;
}
return $query;
}