r1352 + fix upgrade for MySQL-4
[racktables] / upgrade.php
index 2b92ac55704fa51673063a6c1da887a1d4c4f2e7..a2cb2cafe77d1d4e1911878b140b95154fed0e1b 100644 (file)
@@ -8,7 +8,7 @@
 // Returning an empty array means that no upgrade is necessary.
 function getDBUpgradePath ($v1, $v2)
 {
-       $versionhistory = array ('0.14.4', '0.14.5', '0.14.6');
+       $versionhistory = array ('0.14.4', '0.14.5', '0.14.6', '0.14.7');
        if (!in_array ($v1, $versionhistory) || !in_array ($v2, $versionhistory))
        {
                showError ("An upgrade path has been requested for versions '${v1}' and '${v2}', " .
@@ -139,16 +139,26 @@ CREATE TABLE `Config` (
                        // into 1-field one to employ AUTO_INCREMENT properly. This means
                        // renumbering lots of records in Dictionary and adjusting records
                        // in related tables. After that we can safely swap the tables.
-                       $query[] = 'create table Dictionary_0_14_7_new (chapter_no int(10) unsigned NOT NULL, dict_key int(10) unsigned NOT NULL auto_increment PRIMARY KEY, dict_value char(128) default NULL)';
-                       // Convert the fields to unsigned on occasion.
-                       $query[] = 'create table PortCompat_0_14_7_new (type1 int(10) unsigned NOT NULL, type2 int(10) unsigned NOT NULL)';
+                       $query[] = "
+CREATE TABLE `Dictionary_0_14_7_new` (
+  `chapter_no` int(10) unsigned NOT NULL,
+  `dict_key` int(10) unsigned NOT NULL auto_increment,
+  `dict_value` char(128) default NULL,
+  PRIMARY KEY  (`dict_key`),
+  UNIQUE KEY `chap_to_key` (`chapter_no`,`dict_key`),
+  UNIQUE KEY `chap_to_val` (`chapter_no`,`dict_value`)
+) TYPE=MyISAM AUTO_INCREMENT=50000
+";
 
+echo '<pre>';
                        // Find all chapter numbers, which will require AttributeValue adjustment.
                        $q2 = 'select distinct chapter_no from AttributeMap where chapter_no != 0';
                        $r2 = $dbxlink->query ($q2);
-                       $chaplist = $r2->fetchAll (PDO::FETCH_NUM);
+                       $chaplist = array();
+                       while ($row = $r2->fetch (PDO::FETCH_NUM))
+                               $chaplist[] = $row[0];
                        $r2->closeCursor();
-                       $chaplist = $chaplist[0];
+                       unset ($r2);
 
                        $stock = array();
                        // Below I list the records, which are known to be the stock
@@ -185,7 +195,7 @@ CREATE TABLE `Config` (
                                80, 81, 82, 83, 84, 85, 86, 87, 88, 89,
                                90, 91, 92, 93, 94, 95, 96, 97, 98, 99,
                                100, 101, 102, 103, 104, 105, 106, 107, 108, 109,
-                               110, 111, 112, 113
+                               110, 111, 112, 113, 114, 115
                        );
                        $stock[13] = array
                        (
@@ -227,7 +237,7 @@ CREATE TABLE `Config` (
                        // Load dictionary and transform into two tree structures for
                        // stock and user record sets.
                        $dict = array();
-                       $q3 = 'select chapter_no, dict_key, dict_value from Dictionary';
+                       $q3 = 'select chapter_no, dict_key, dict_value from Dictionary order by chapter_no, dict_key';
                        $r3 = $dbxlink->query ($q3);
 
                        while ($row = $r3->fetch (PDO::FETCH_ASSOC))
@@ -255,6 +265,7 @@ CREATE TABLE `Config` (
                                $dict[$tree][$chapter_no][$dict_key] = array ('value' => $row['dict_value']);
                        }
                        $r3->closeCursor();
+                       unset ($r3);
 
 
                        // Now we store stock dataset first, bump up key value and store
@@ -269,14 +280,14 @@ CREATE TABLE `Config` (
                                $new_dict[$chapter_no] = array();
                                foreach ($words as $dict_key => $entry)
                                {
-                                       $query[] = "insert into Dictionary_0_14_7_new " .
+                                       $query[] = "insert into Dictionary_0_14_7_new (chapter_no, dict_key, dict_value) " .
                                                "values (${chapter_no}, ${newkey}, '${entry['value']}')";
                                        $new_dict[$chapter_no][$dict_key] = $entry;
                                        $new_dict[$chapter_no][$dict_key]['newkey'] = $newkey;
                                        $newkey++;
                                }
                        }
-                       $newkey = 10000;
+                       $newkey = 50000;
                        foreach ($dict['user'] as $chapter_no => $words)
                        {
                                // Some chapters may appear on the user dataset only.
@@ -292,6 +303,9 @@ CREATE TABLE `Config` (
                                }
                        }
                        // The new table should now have adequate AUTO_INCREMENT w/o our care.
+                       // Install the new data.
+                       $query[] = 'drop table Dictionary';
+                       $query[] = 'alter table Dictionary_0_14_7_new rename to Dictionary';
 
                        // Now we iterate over the joint dataset, picking some chapters and
                        // performing additional processing:
@@ -308,6 +322,9 @@ CREATE TABLE `Config` (
                                {
                                        $value = $data['value'];
                                        $newkey = $data['newkey'];
+                                       // Even if the key doesn't change, go on to have
+                                       // AttributeMap regenerated completely.
+#echo "oldkey == ${oldkey} newkey == ${newkey} value == ${value}\n";
                                        if ($chapter_no == 1)
                                        {
                                                $q4 = "select id from RackObject where objtype_id = ${oldkey}";
@@ -315,50 +332,77 @@ CREATE TABLE `Config` (
                                                while ($row = $r4->fetch (PDO::FETCH_ASSOC))
                                                        $query[] = "update RackObject set objtype_id = ${newkey} where id = ${row['id']} limit 1";
                                                $r4->closeCursor();
+                                               unset ($r4);
 
-                                               $q4 = "select attr_id, chapter_no from AttributeMap where objtype_id = ${oldkey}";
-                                               $r4 = $dbxlink->query ($q4);
-                                               while ($row = $r4->fetch (PDO::FETCH_ASSOC))
+                                               $q5 = "select attr_id, chapter_no from AttributeMap where objtype_id = ${oldkey}";
+                                               $r5 = $dbxlink->query ($q5);
+                                               while ($row = $r5->fetch (PDO::FETCH_ASSOC))
                                                        $query[] = "insert into AttributeMap (objtype_id, attr_id, chapter_no) values (${newkey}, ${row['attr_id']}, ${row['chapter_no']})";
-                                               $r4->closeCursor();
+                                               $r5->closeCursor();
+                                               unset ($r5);
                                        }
                                        elseif ($chapter_no == 2)
                                        {
-                                               $q4 = "select id from Port where type = ${oldkey}";
-                                               $r4 = $dbxlink->query ($q4);
-                                               while ($row = $r4->fetch (PDO::FETCH_ASSOC))
+                                               $q46 = "select id from Port where type = ${oldkey}";
+                                               $r46 = $dbxlink->query ($q46);
+                                               if ($r46 == NULL)
+                                                       echo 'ERROR';
+                                               while ($row = $r46->fetch (PDO::FETCH_ASSOC))
                                                        $query[] = "update Port set type = ${newkey} where id = ${row['id']} limit 1";
-                                               $r4->closeCursor();
+                                               $r46->closeCursor();
+                                               unset ($r46);
                                        }
                                        elseif ($chapter_no == 3)
                                        {
-                                               $q4 = "select id from Rack where row_id = ${oldkey}";
-                                               $r4 = $dbxlink->query ($q4);
-                                               while ($row = $r4->fetch (PDO::FETCH_ASSOC))
+                                               $q7 = "select id from Rack where row_id = ${oldkey}";
+                                               $r7 = $dbxlink->query ($q7);
+                                               while ($row = $r7->fetch (PDO::FETCH_ASSOC))
                                                        $query[] = "update Rack set row_id = ${newkey} where id = ${row['id']} limit 1";
-                                               $r4->closeCursor();
+                                               $r7->closeCursor();
+                                               unset ($r7);
                                        }
                                        elseif ($chapter_no == 20)
                                        {
-                                               $q4 = "select id from Rack where row_id = ${oldkey}";
-                                               $r4 = $dbxlink->query ($q4);
-                                               while ($row = $r4->fetch (PDO::FETCH_ASSOC))
-                                                       $query[] = "update Rack set row_id = ${newkey} where id = ${row['id']} limit 1";
-                                               $r4->closeCursor();
+                                               $q8 = "select object_id, localip, localport, remoteip, remoteport from PortForwarding where proto = ${oldkey}";
+                                               $r8 = $dbxlink->query ($q8);
+                                               while ($row = $r8->fetch (PDO::FETCH_ASSOC))
+                                                       $query[] = "update PortForwarding set proto = ${newkey} where " .
+                                                       "object_id = ${row['object_id']} and localip = ${row['localip']} and " .
+                                                       "localport = ${row['localport']} and remoteip = ${row['remoteip']} and " .
+                                                       "remoteport = ${row['remoteport']} and proto = ${oldkey} limit 1";
+                                               $r8->closeCursor();
+                                               unset ($r8);
+                                       }
+                                       elseif (in_array ($chapter_no, $chaplist))
+                                       {
+                                               $q81 = "select object_id, AttributeValue.attr_id from " .
+                                               "AttributeValue natural join Attribute natural join AttributeMap " .
+                                               "inner join RackObject on RackObject.id = object_id and RackObject.objtype_id = AttributeMap.objtype_id " .
+                                               "where attr_type = 'dict' and chapter_no = ${chapter_no} and uint_value = ${oldkey}";
+                                               $r81 = $dbxlink->query ($q81);
+                                               while ($row = $r81->fetch (PDO::FETCH_ASSOC))
+                                                       $query[] = "update AttributeValue set uint_value = ${newkey} " .
+                                                       "where object_id = ${row['object_id']} and attr_id = ${row['attr_id']}";
+                                               $r81->closeCursor();
+                                               unset ($r81);
                                        }
                                }
                        }
                        // Now it's possible to schedule PortCompat regeneration.
-                       $query[] = "delete from PortCompat";
-                       $q5 = "select type1, type2 from PortCompat";
-                       $r5 = $dbxlink->query ($q5);
-                       while ($row = $r5->fetch (PDO::FETCH_ASSOC))
+                       // Convert the fields to unsigned on occasion.
+                       $query[] = 'drop table PortCompat';
+                       $query[] = 'create table PortCompat (type1 int(10) unsigned NOT NULL, type2 int(10) unsigned NOT NULL)';
+                       $q9 = "select type1, type2 from PortCompat";
+                       $r9 = $dbxlink->query ($q9);
+                       while ($row = $r9->fetch (PDO::FETCH_ASSOC))
                        {
                                $new_type1 = $new_dict[2][$row['type1']]['newkey'];
                                $new_type2 = $new_dict[2][$row['type2']]['newkey'];
                                $query[] = "insert into PortCompat (type1, type2) values (${new_type1}, ${new_type2})";
                        }
-                       $r5->closeCursor();
+                       $r9->closeCursor();
+                       unset ($r9);
+echo '</pre>';
 
                        // Give the configuration some finish
                        $query[] = "update Config set is_hidden = 'yes' where varname = 'color_F'";
@@ -374,9 +418,130 @@ CREATE TABLE `Config` (
                        $query[] = "update Config set description = 'Expect common name configured for the following object types' where varname = 'NAMEFUL_OBJTYPES'";
                        $query[] = "update Config set description = '&lt;SELECT&gt; lists height' where varname = 'MAXSELSIZE'";
                        $query[] = "update Config set description = '&quot;Fast&quot; form is this many records tall' where varname = 'MASSCOUNT'";
+                       $query[] = "update Config set is_hidden = 'no', description = 'Ports per row in VLANs tab' where varname = 'PORTS_PER_ROW'";
                        $query[] = "INSERT INTO `Config` VALUES ('IPV4_ADDRS_PER_PAGE','256','uint','no','no','IPv4 addresses per page')";
                        $query[] = "INSERT INTO `Config` VALUES ('DEFAULT_RACK_HEIGHT','42','uint','yes','no','Default rack height')";
+                       // After Dictionary transformation we ought to list 337 stock records in DB. Add more.
+                       $new_words = array();
+                       $new_words[338] = array (12 => 'Dell PowerConnect 2216');
+                       $new_words[] = array (12 => 'Dell PowerConnect 2224');
+                       $new_words[] = array (12 => 'Dell PowerConnect 2324');
+                       $new_words[] = array (12 => 'Dell PowerConnect 2708');
+                       $new_words[] = array (12 => 'Dell PowerConnect 2716');
+                       $new_words[] = array (12 => 'Dell PowerConnect 2724');
+                       $new_words[] = array (12 => 'Dell PowerConnect 2748');
+                       $new_words[] = array (12 => 'Dell PowerConnect 3424');
+                       $new_words[] = array (12 => 'Dell PowerConnect 3424P');
+                       $new_words[] = array (12 => 'Dell PowerConnect 3448');
+                       $new_words[] = array (12 => 'Dell PowerConnect 3448P');
+                       $new_words[] = array (12 => 'Dell PowerConnect 5324');
+                       $new_words[] = array (12 => 'Dell PowerConnect 6224');
+                       $new_words[] = array (12 => 'Dell PowerConnect 6224P');
+                       $new_words[] = array (12 => 'Dell PowerConnect 6224F');
+                       $new_words[] = array (12 => 'Dell PowerConnect 6248');
+                       $new_words[] = array (12 => 'Dell PowerConnect 6248P');
+                       $new_words[] = array (11 => 'Dell PowerEdge 6850');
+                       $new_words[] = array (11 => 'Dell PowerEdge 6950');
+                       $new_words[] = array (11 => 'Dell PowerEdge R900');
+                       $new_words[] = array (11 => 'Dell PowerEdge 4400');
+                       $new_words[] = array (11 => 'Dell PowerEdge 2650');
+                       $new_words[] = array (11 => 'Dell PowerEdge 2550');
+                       $new_words[] = array (11 => 'Dell PowerEdge 750');
+                       $new_words[] = array (11 => 'Dell PowerEdge 2450');
+                       $new_words[] = array (11 => 'Dell PowerEdge 850');
+                       $new_words[] = array (11 => 'Dell PowerEdge 1850');
+                       $new_words[] = array (11 => 'Dell PowerEdge 860');
+                       $new_words[] = array (11 => 'Dell PowerEdge 2900');
+                       $new_words[] = array (11 => 'Dell PowerEdge 2970');
+                       $new_words[] = array (11 => 'Dell PowerEdge SC1435');
+                       $new_words[] = array (12 => 'Cisco Catalyst 6509');
+                       $new_words[] = array (12 => 'Cisco ME 6524GS-8S');
+                       $new_words[] = array (12 => 'Cisco ME 6524GT-8S');
+                       $new_words[] = array (12 => 'Cisco Catalyst 4503-E');
+                       $new_words[] = array (12 => 'Cisco Catalyst 4506-E');
+                       $new_words[] = array (12 => 'Cisco Catalyst 4507R-E');
+                       $new_words[] = array (12 => 'Cisco Catalyst 4510R-E');
+                       $new_words[] = array (12 => 'Cisco Catalyst 3750-24TE-M');
+                       $new_words[] = array (12 => 'Cisco Catalyst 4948-10GE');
+                       $new_words[] = array (12 => 'Cisco ME 4924-10GE');
+                       $new_words[] = array (12 => 'Cisco Catalyst 2960-24');
+                       $new_words[] = array (12 => 'Cisco Catalyst 2950-24');
+                       $new_words[] = array (12 => 'Cisco Catalyst 2950-12');
+                       $new_words[] = array (12 => 'Cisco Catalyst 2950C-24');
+                       $new_words[] = array (12 => 'Cisco Catalyst 2950G-24-DC');
+                       $new_words[] = array (12 => 'Cisco Catalyst 2950SX-48');
+                       $new_words[] = array (12 => 'Cisco Catalyst 2950SX-24');
+                       $new_words[] = array (12 => 'Cisco Catalyst 2950T-24');
+                       $new_words[] = array (12 => 'Cisco Catalyst 2950T-48');
+                       $new_words[] = array (12 => 'Cisco Catalyst 2950G-12');
+                       $new_words[] = array (12 => 'Cisco Catalyst 2950G-24');
+                       $new_words[] = array (12 => 'Cisco Catalyst 2950G-48');
+                       $new_words[] = array (12 => 'Cisco Catalyst 3508G XL');
+                       $new_words[] = array (12 => 'Cisco Catalyst 3512 XL');
+                       $new_words[] = array (12 => 'Cisco Catalyst 3524 XL');
+                       $new_words[] = array (12 => 'Cisco Catalyst 3524 PWR XL');
+                       $new_words[] = array (12 => 'Cisco Catalyst 3548 XL');
+                       $new_words[] = array (12 => 'Cisco ME 2400-24TS-A');
+                       $new_words[] = array (12 => 'Cisco ME 2400-24TS-D');
+                       $new_words[] = array (12 => 'Cisco Catalyst 3550-12T');
+                       $new_words[] = array (12 => 'Cisco Catalyst 3550-12G');
+                       $new_words[] = array (12 => 'Cisco Catalyst 3550-24');
+                       $new_words[] = array (12 => 'Cisco Catalyst 3550-24 FX');
+                       $new_words[] = array (12 => 'Cisco Catalyst 3550-24 DC');
+                       $new_words[] = array (12 => 'Cisco Catalyst 3550-24 PWR');
+                       $new_words[] = array (12 => 'Cisco Catalyst 3550-48');
+                       $new_words[] = array (12 => 'Cisco ME 3400G-12CS-A');
+                       $new_words[] = array (12 => 'Cisco ME 3400G-12CS-D');
+                       $new_words[] = array (12 => 'Cisco ME 3400G-2CS-A');
+                       $new_words[] = array (12 => 'Cisco ME 3400-24TS-A');
+                       $new_words[] = array (12 => 'Cisco ME 3400-24TS-D');
+                       $new_words[] = array (12 => 'Cisco ME 3400-24FS-A');
+                       $new_words[] = array (12 => 'Foundry FastIron GS 624XGP');
+                       $new_words[] = array (12 => 'Foundry FastIron GS 624XGP-POE');
+                       $new_words[] = array (12 => 'Foundry FastIron LS 624');
+                       $new_words[] = array (12 => 'Foundry FastIron LS 648');
+                       $new_words[] = array (12 => 'Foundry NetIron M2404F');
+                       $new_words[] = array (12 => 'Foundry NetIron M2404C');
+                       $new_words[] = array (17 => 'Foundry BigIron RX-32');
+                       $new_words[] = array (13 => 'Debian 2.0 (hamm)');
+                       $new_words[] = array (13 => 'Debian 2.1 (slink)');
+                       $new_words[] = array (13 => 'Debian 2.2 (potato)');
+                       $new_words[] = array (13 => 'Debian 4.0 (etch)');
+                       $new_words[] = array (13 => 'ALTLinux Server 4.0');
+                       $new_words[] = array (13 => 'ALTLinux Sisyphus');
+                       $new_words[] = array (13 => 'openSUSE 10.0');
+                       $new_words[] = array (13 => 'openSUSE 10.1');
+                       $new_words[] = array (13 => 'openSUSE 10.2');
+                       $new_words[] = array (13 => 'openSUSE 10.3');
+                       $new_words[] = array (13 => 'Ubuntu 4.10');
+                       $new_words[] = array (13 => 'Ubuntu 5.04');
+                       $new_words[] = array (13 => 'Ubuntu 5.10');
+                       $new_words[] = array (13 => 'Ubuntu 6.06 LTS');
+                       $new_words[] = array (13 => 'Ubuntu 6.10');
+                       $new_words[] = array (13 => 'Ubuntu 7.04');
+                       $new_words[] = array (13 => 'Ubuntu 7.10');
+                       $new_words[] = array (13 => 'Ubuntu 8.04 LTS');
+                       $new_words[] = array (13 => 'RHEL5');
+                       $new_words[] = array (18 => 'Dell PowerVault 210S');
+                       $new_words[] = array (18 => 'Dell PowerVault 221S');
+                       $new_words[] = array (2 => 'dry contact');
+                       $new_words[] = array (2 => 'unknown');
+                       // Two above records ought to take keys 439 and 440.
+                       $query[] = "INSERT INTO `PortCompat` (`type1`, `type2`) VALUES (439,439)";
+                       $new_words[] = array (13 => 'CentOS-2');
+                       $new_words[] = array (13 => 'CentOS-3');
+                       $new_words[] = array (13 => 'CentOS-4');
+                       $new_words[] = array (13 => 'CentOS-5');
 
+
+
+                       foreach ($new_words as $dict_key => $tmp)
+                               foreach ($tmp as $chapter_no => $dict_value)
+                                       $query[] = 'INSERT INTO `Dictionary` (`chapter_no`, `dict_key`, `dict_value`) ' .
+                                               "VALUES (${chapter_no}, ${dict_key}, '${dict_value}')";
+                       // Resetting to defaults is worse, than remapping, but better than
+                       // leaving messed values.
+                       $query[] = "update Config set varvalue = '24' where varname = 'default_port_type' limit 1";
                        // We are done.
                        $query[] = "update Config set varvalue = '0.14.7' where varname = 'DB_VERSION'";
                        break; // --------------------------------------------
@@ -387,23 +552,24 @@ CREATE TABLE `Config` (
        }
        $failures = array();
        $ndots = 0;
-       echo "<pre>Executing database upgrade batch '${batchid}: ";
+       echo "<pre>Executing database upgrade batch '${batchid}:\n";
        foreach ($query as $q)
        {
                $result = $dbxlink->query ($q);
                if ($result != NULL)
-               {
                        echo '.';
-                       if (++$ndots == 50)
-                       {
-                               echo "\n";
-                               $ndots = 0;
-                       }
-                       continue;
+               else
+               {
+                       echo '!';
+                       $errorInfo = $dbxlink->errorInfo();
+                       $failures[] = array ($q, $errorInfo[2]);
+               }
+               if (++$ndots == 50)
+               {
+                       echo "\n";
+                       flush();
+                       $ndots = 0;
                }
-               echo '!';
-               $errorInfo = $dbxlink->errorInfo();
-               $failures[] = array ($q, $errorInfo[2]);
        }
        echo '<br>';
        if (!count ($failures))
@@ -429,7 +595,9 @@ CREATE TABLE `Config` (
 $root = (empty($_SERVER['HTTPS'])?'http':'https').
        '://'.
        (isset($_SERVER['HTTP_HOST'])?$_SERVER['HTTP_HOST']:($_SERVER['SERVER_NAME'].($_SERVER['SERVER_PORT']=='80'?'':$_SERVER['SERVER_PORT']))).
-       dirname($_SERVER['PHP_SELF']).'/';
+       dirname($_SERVER['PHP_SELF']);
+if (substr ($root, -1) != '/')
+       $root .= '/';
 
 // The below will be necessary as long as we rely on showError()
 require_once 'inc/interface.php';
@@ -477,9 +645,11 @@ $dbver = getDatabaseVersion();
 echo 'Code version == ' . CODE_VERSION;
 echo '<br>Database version == ' . $dbver;
 if ($dbver == CODE_VERSION)
+{
        die ("<p align=justify>Your database seems to be up-to-date. " .
                "Now the best thing to do would be to follow to the <a href='${root}'>main page</a> " .
                "and explore your data. Have a nice day.</p>");
+}
 
 foreach (getDBUpgradePath ($dbver, CODE_VERSION) as $batchid)
        executeUpgradeBatch ($batchid);