r1317 + fixed 2 lost stock records
[racktables] / upgrade.php
index 886516360823553d04bbb2e839dc7385dc1dd5db..4f376e8d31c7682d37eb58c122848bf35eab3b0f 100644 (file)
@@ -39,6 +39,7 @@ function getDBUpgradePath ($v1, $v2)
 function executeUpgradeBatch ($batchid)
 {
        $query = array();
+       global $dbxlink;
        switch ($batchid)
        {
                case '0.14.5':
@@ -130,27 +131,321 @@ CREATE TABLE `Config` (
                        $query[] = "INSERT INTO `Config` VALUES ('DB_VERSION','0.14.6','string','no','yes','Database version.')";
                        break; // --------------------------------------------
                case '0.14.7':
+                       // IPAddress is hopefully fixed now finally.
                        $query[] = "delete from IPAddress where name = '' and reserved != 'yes'";
+
+                       // Now rebuild the dictionary into a new table with the same data,
+                       // but proper indexing. We are going to convert compound index
+                       // 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)';
+
+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 = array();
+                       while ($row = $r2->fetch (PDO::FETCH_NUM))
+                               $chaplist[] = $row[0];
+print_r ($chaplist);
+                       $r2->closeCursor();
+                       unset ($r2);
+
+                       $stock = array();
+                       // Below I list the records, which are known to be the stock
+                       // dictionary records of 0.14.6 release.
+                       $stock[1] = array
+                       (
+                               1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 14, 15, 16
+                       );
+                       $stock[2] = array
+                       (
+                               3, 4, 5, 6, 7, 8, 9,
+                               10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20,
+                               20, 21, 22, 23, 24, 25, 26, 27, 28
+                       );
+                       $stock[11] = array
+                       (
+                               1, 3, 4, 5, 6, 7, 8, 9,
+                               10, 11, 12, 13, 14, 15, 16, 17, 18, 19,
+                               21, 22, 24, 25, 26, 27, 28, 29,
+                               30, 31, 32, 33, 34, 35, 36, 37, 38, 39,
+                               40, 41, 42, 43, 44, 45, 46, 47, 48, 49,
+                               50, 51, 52, 53, 54, 55, 56, 57, 58, 59,
+                               60, 61, 62, 63, 64, 65, 66, 67, 68, 69,
+                               70, 71, 72, 73, 74, 75, 76
+                       );
+                       $stock[12] = array
+                       (
+                               1, 11, 13, 14, 15, 16, 17, 18, 19, 20, 26, 29,
+                               31, 32, 33, 34, 35, 36, 37, 38, 39,
+                               40, 41, 42, 43, 44, 45, 46, 47, 48, 49,
+                               50, 51, 52, 53, 54, 55, 56, 57, 58, 59,
+                               60, 61, 62, 63, 64, 65, 66, 67, 68, 69,
+                               70, 71, 72, 73, 74, 75, 76, 77, 78, 79,
+                               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, 114, 115
+                       );
+                       $stock[13] = array
+                       (
+                               1, 2, 3, 4, 5, 6, 7, 8, 9,
+                               10, 11, 12, 13, 14, 15, 16, 17, 18, 19,
+                               20, 21, 22, 23, 24, 25, 26, 27, 28, 29,
+                               30, 31, 32
+                       );
+                       $stock[14] = array
+                       (
+                               1, 2, 9, 11, 13, 15, 19, 20, 21, 22
+                       );
+                       $stock[16] = array
+                       (
+                               1, 2, 3, 4, 5, 6, 7, 8
+                       );
+                       $stock[17] = array
+                       (
+                               1, 2, 3, 4, 5, 6, 7, 8, 9,
+                               10, 11, 12, 13, 14, 15, 16, 17, 18, 19,
+                               20, 21, 22, 23, 24, 25, 26, 27, 28, 29,
+                               30, 31, 32, 33, 34, 35, 36, 37, 38, 39,
+                               40, 41, 42, 43, 44, 45, 46, 47, 48, 49,
+                               50
+                       );
+                       $stock[18] = array
+                       (
+                               1, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14
+                       );
+                       $stock[19] = array
+                       (
+                               1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11
+                       );
+                       $stock[20] = array
+                       (
+                               1, 2
+                       );
+
+                       // 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 order by chapter_no, dict_key';
+                       $r3 = $dbxlink->query ($q3);
+
+                       while ($row = $r3->fetch (PDO::FETCH_ASSOC))
+                       {
+                               $tree = 'user';
+                               $dict_key = $row['dict_key'];
+                               $chapter_no = $row['chapter_no'];
+                               switch ($chapter_no)
+                               {
+                                       case 1: // RackObjectType
+                                       case 2: // PortType
+                                       case 11: // server models
+                                       case 12: // network switch models
+                                       case 13: // server OS type
+                                       case 14: // network switch OS type
+                                       case 16: // router OS type
+                                       case 17: // router models
+                                       case 18: // disk array models
+                                       case 19: // tape library models
+                                       case 20: // Protocols
+                                               if (in_array ($dict_key, $stock[$chapter_no]))
+                                                       $tree = 'stock';
+                                               break;
+                               }
+                               $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
+                       // user's data. After that we will know the new dict_key for all
+                       // records.
+                       // The result of both datasets processing is saved in $new_dict.
+                       // Save on calling LAST_ISERT_ID() each time by keeping own key.
+                       $newkey = 1;
+                       $new_dict = array();
+                       foreach ($dict['stock'] as $chapter_no => $words)
+                       {
+                               $new_dict[$chapter_no] = array();
+                               foreach ($words as $dict_key => $entry)
+                               {
+                                       $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;
+                       foreach ($dict['user'] as $chapter_no => $words)
+                       {
+                               // Some chapters may appear on the user dataset only.
+                               if (!isset ($new_dict[$chapter_no]))
+                                       $new_dict[$chapter_no] = array();
+                               foreach ($words as $dict_key => $entry)
+                               {
+                                       $query[] = "insert into Dictionary_0_14_7_new " .
+                                               "values (${chapter_no}, ${newkey}, '${entry['value']}')";
+                                       $new_dict[$chapter_no][$dict_key] = $entry;
+                                       $new_dict[$chapter_no][$dict_key]['newkey'] = $newkey;
+                                       $newkey++;
+                               }
+                       }
+                       // 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:
+                       // 1 (RackObjectType) --- adjust RackObject and regenerate AttributeMap
+                       // 2 (PortType) --- adjust Port and regenerate PortCompat (at a latter point)
+                       // 3 (RackRow) --- adjust Rack
+                       // 20 (Protocols) --- adjust PortForwarding
+                       // All other chapters listed in $chaplist --- adjust AttributeValue
+                       
+                       $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}";
+                                               $r4 = $dbxlink->query ($q4);
+                                               while ($row = $r4->fetch (PDO::FETCH_ASSOC))
+                                                       $query[] = "update RackObject set objtype_id = ${newkey} where id = ${row['id']} limit 1";
+                                               $r4->closeCursor();
+                                               unset ($r4);
+
+                                               $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']})";
+                                               $r5->closeCursor();
+                                               unset ($r5);
+                                       }
+                                       elseif ($chapter_no == 2)
+                                       {
+                                               $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";
+                                               $r46->closeCursor();
+                                               unset ($r46);
+                                       }
+                                       elseif ($chapter_no == 3)
+                                       {
+                                               $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";
+                                               $r7->closeCursor();
+                                               unset ($r7);
+                                       }
+                                       elseif ($chapter_no == 20)
+                                       {
+                                               $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.
+                       // 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})";
+                       }
+                       $r9->closeCursor();
+                       unset ($r9);
+echo '</pre>';
+
+                       // Give the configuration some finish
+                       $query[] = "update Config set is_hidden = 'yes' where varname = 'color_F'";
+                       $query[] = "update Config set is_hidden = 'yes' where varname = 'color_A'";
+                       $query[] = "update Config set is_hidden = 'yes' where varname = 'color_U'";
+                       $query[] = "update Config set is_hidden = 'yes' where varname = 'color_T'";
+                       $query[] = "update Config set is_hidden = 'yes' where varname = 'color_Th'";
+                       $query[] = "update Config set is_hidden = 'yes' where varname = 'color_Tw'";
+                       $query[] = "update Config set is_hidden = 'yes' where varname = 'color_Thw'";
+                       $query[] = "update Config set description = 'Default port type' where varname = 'default_port_type'";
+                       $query[] = "update Config set description = 'Picture scale for rack row display' where varname = 'ROW_SCALE'";
+                       $query[] = "update Config set description = 'Organization name' where varname = 'enterprise'";
+                       $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 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'";
                        break; // --------------------------------------------
                default:
                        showError ("executeUpgradeBatch () failed, because batch '${batchid}' isn't defined");
                        die;
                        break;
        }
-       global $dbxlink;
        $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 '.';
-                       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))
@@ -224,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);