r1317 + fixed 2 lost stock records
[racktables] / upgrade.php
index 1ae59bc96f3cb55d5e3ea435b2a82ebf0cd3d13e..4f376e8d31c7682d37eb58c122848bf35eab3b0f 100644 (file)
@@ -140,15 +140,17 @@ CREATE TABLE `Config` (
                        // 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)';
 
+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];
+print_r ($chaplist);
                        $r2->closeCursor();
-                       $chaplist = $chaplist[0];
+                       unset ($r2);
 
                        $stock = array();
                        // Below I list the records, which are known to be the stock
@@ -185,7 +187,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,10 +229,10 @@ 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)
+                       while ($row = $r3->fetch (PDO::FETCH_ASSOC))
                        {
                                $tree = 'user';
                                $dict_key = $row['dict_key'];
@@ -255,6 +257,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,7 +272,7 @@ 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;
@@ -292,6 +295,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:
@@ -304,10 +310,14 @@ CREATE TABLE `Config` (
                        $query[] = "delete from AttributeMap";
                        foreach ($new_dict as $chapter_no => $words)
                        {
+echo "Processing chapter ${chapter_no}\n";
                                foreach ($words as $oldkey => $data)
                                {
                                        $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 +325,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, 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,11 +411,15 @@ 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');";
+                       $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 there.
+                       $query[] = "INSERT INTO `Dictionary` (`chapter_no`, `dict_key`, `dict_value`) VALUES (12,338,'Cisco Catalyst 4948-10GE')";
+                       $query[] = "INSERT INTO `Dictionary` (`chapter_no`, `dict_key`, `dict_value`) VALUES (12,339,'Cisco Catalyst 2950T-48-SI')";
 
                        // We are done.
-                       $query[] = "update Config set varvalue = '0.14.7' where varname = 'DB_VERSION'";
+#                      $query[] = "update Config set varvalue = '0.14.7' where varname = 'DB_VERSION'";
                        break; // --------------------------------------------
                default:
                        showError ("executeUpgradeBatch () failed, because batch '${batchid}' isn't defined");
@@ -388,22 +429,23 @@ CREATE TABLE `Config` (
        $failures = array();
        $ndots = 0;
        echo "<pre>Executing database upgrade batch '${batchid}: ";
+print_r ($query);
        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";
+                       $ndots = 0;
                }
-               echo '!';
-               $errorInfo = $dbxlink->errorInfo();
-               $failures[] = array ($q, $errorInfo[2]);
        }
        echo '<br>';
        if (!count ($failures))
@@ -477,9 +519,12 @@ $dbver = getDatabaseVersion();
 echo 'Code version == ' . CODE_VERSION;
 echo '<br>Database version == ' . $dbver;
 if ($dbver == CODE_VERSION)
+{
+       executeUpgradeBatch ('0.14.7');
        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);