library/sql_upgrade_fx.php

   1 <?php
   2 // Copyright (C) 2008-2011 Rod Roark <rod@sunsetsystems.com>
   3 //
   4 // This program is free software; you can redistribute it and/or
   5 // modify it under the terms of the GNU General Public License
   6 // as published by the Free Software Foundation; either version 2
   7 // of the License, or (at your option) any later version.
   8 //
   9 // Functions to allow safe database and global modifications
  10 // during upgrading and patches
  11 //
  12
  13 function tableExists($tblname) {
  14   $row = sqlQuery("SHOW TABLES LIKE '$tblname'");
  15   if (empty($row)) return false;
  16   return true;
  17 }
  18
  19 function columnExists($tblname, $colname) {
  20   $row = sqlQuery("SHOW COLUMNS FROM $tblname LIKE '$colname'");
  21   if (empty($row)) return false;
  22   return true;
  23 }
  24
  25 function columnHasType($tblname, $colname, $coltype) {
  26   $row = sqlQuery("SHOW COLUMNS FROM $tblname LIKE '$colname'");
  27   if (empty($row)) return true;
  28   return (strcasecmp($row['Type'], $coltype) == 0);
  29 }
  30
  31 function tableHasRow($tblname, $colname, $value) {
  32   $row = sqlQuery("SELECT COUNT(*) AS count FROM $tblname WHERE " .
  33     "$colname LIKE '$value'");
  34   return $row['count'] ? true : false;
  35 }
  36
  37 function tableHasRow2D($tblname, $colname, $value, $colname2, $value2) {
  38   $row = sqlQuery("SELECT COUNT(*) AS count FROM $tblname WHERE " .
  39     "$colname LIKE '$value' AND $colname2 LIKE '$value2'");
  40   return $row['count'] ? true : false;
  41 }
  42
  43 function tableHasRow3D($tblname, $colname, $value, $colname2, $value2, $colname3, $value3) {
  44   $row = sqlQuery("SELECT COUNT(*) AS count FROM $tblname WHERE " .
  45     "$colname LIKE '$value' AND $colname2 LIKE '$value2' AND $colname3 LIKE '$value3'");
  46   return $row['count'] ? true : false;
  47 }
  48
  49 function tableHasRow4D($tblname, $colname, $value, $colname2, $value2, $colname3, $value3, $colname4, $value4) {
  50   $row = sqlQuery("SELECT COUNT(*) AS count FROM $tblname WHERE " .
  51     "$colname LIKE '$value' AND $colname2 LIKE '$value2' AND $colname3 LIKE '$value3' AND $colname4 LIKE '$value4'");
  52   return $row['count'] ? true : false;
  53 }
  54
  55 function tableHasIndex($tblname, $colname) {
  56   $row = sqlQuery("SHOW INDEX FROM `$tblname` WHERE `Key_name` = '$colname'");
  57   return (empty($row)) ? false : true;
  58 }
  59
  60 function upgradeFromSqlFile($filename) {
  61   global $webserver_root;
  62
  63   flush();
  64   echo "<font color='green'>Processing $filename ...</font><br />\n";
  65
  66   $fullname = "$webserver_root/sql/$filename";
  67
  68   $fd = fopen($fullname, 'r');
  69   if ($fd == FALSE) {
  70     echo "ERROR.  Could not open '$fullname'.\n";
  71     flush();
  72     break;
  73   }
  74
  75   $query = "";
  76   $line = "";
  77   $skipping = false;
  78
  79   while (!feof ($fd)){
  80     $line = fgets($fd, 2048);
  81     $line = rtrim($line);
  82
  83     if (preg_match('/^\s*--/', $line)) continue;
  84     if ($line == "") continue;
  85
  86     if (preg_match('/^#IfNotTable\s+(\S+)/', $line, $matches)) {
  87       $skipping = tableExists($matches[1]);
  88       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
  89     }
  90     else if (preg_match('/^#IfTable\s+(\S+)/', $line, $matches)) {
  91       $skipping = ! tableExists($matches[1]);
  92       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
  93     }
  94     else if (preg_match('/^#IfMissingColumn\s+(\S+)\s+(\S+)/', $line, $matches)) {
  95       if (tableExists($matches[1])) {
  96         $skipping = columnExists($matches[1], $matches[2]);
  97       }
  98       else {
  99         // If no such table then the column is deemed not "missing".
 100         $skipping = true;
 101       }
 102       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 103     }
 104     else if (preg_match('/^#IfNotColumnType\s+(\S+)\s+(\S+)\s+(\S+)/', $line, $matches)) {
 105       if (tableExists($matches[1])) {
 106         $skipping = columnHasType($matches[1], $matches[2], $matches[3]);
 107       }
 108       else {
 109         // If no such table then the column type is deemed not "missing".
 110         $skipping = true;
 111       }
 112       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 113     }
 114     else if (preg_match('/^#IfNotIndex\s+(\S+)\s+(\S+)/', $line, $matches)) {
 115       if (tableExists($matches[1])) {
 116         $skipping = tableHasIndex($matches[1], $matches[2]);
 117       }
 118       else {
 119         // If no such table then the index is deemed not "missing".
 120         $skipping = true;
 121       }
 122       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 123     }
 124     else if (preg_match('/^#IfNotRow\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
 125       if (tableExists($matches[1])) {
 126         $skipping = tableHasRow($matches[1], $matches[2], $matches[3]);
 127       }
 128       else {
 129         // If no such table then the row is deemed not "missing".
 130         $skipping = true;
 131       }
 132       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 133     }
 134     else if (preg_match('/^#IfNotRow2D\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
 135       if (tableExists($matches[1])) {
 136         $skipping = tableHasRow2D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5]);
 137       }
 138       else {
 139         // If no such table then the row is deemed not "missing".
 140         $skipping = true;
 141       }
 142       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 143     }
 144     else if (preg_match('/^#IfNotRow3D\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
 145       if (tableExists($matches[1])) {
 146         $skipping = tableHasRow3D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5], $matches[6], $matches[7]);
 147       }
 148       else {
 149         // If no such table then the row is deemed not "missing".
 150         $skipping = true;
 151       }
 152       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 153     }
 154     else if (preg_match('/^#IfNotRow4D\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
 155       if (tableExists($matches[1])) {
 156         $skipping = tableHasRow4D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5], $matches[6], $matches[7], $matches[8], $matches[9]);
 157       }
 158       else {
 159         // If no such table then the row is deemed not "missing".
 160         $skipping = true;
 161       }
 162       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 163     }
 164     else if (preg_match('/^#IfNotRow2Dx2\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
 165       if (tableExists($matches[1])) {
 166         // If either check exist, then will skip
 167         $firstCheck = tableHasRow2D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5]);
 168         $secondCheck = tableHasRow2D($matches[1], $matches[2], $matches[3], $matches[6], $matches[7]);
 169         if ($firstCheck || $secondCheck) {
 170           $skipping = true;
 171         }
 172         else {
 173           $skipping = false;
 174         }
 175       }
 176       else {
 177         // If no such table then the row is deemed not "missing".
 178         $skipping = true;
 179       }
 180       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 181     }
 182     else if (preg_match('/^#EndIf/', $line)) {
 183       $skipping = false;
 184     }
 185
 186     if (preg_match('/^\s*#/', $line)) continue;
 187     if ($skipping) continue;
 188
 189     $query = $query . $line;
 190     if (substr($query, -1) == ';') {
 191       $query = rtrim($query, ';');
 192       echo "$query<br />\n";
 193       if (!sqlStatement($query)) {
 194         echo "<font color='red'>The above statement failed: " .
 195           mysql_error() . "<br />Upgrading will continue.<br /></font>\n";
 196       }
 197       $query = '';
 198     }
 199   }
 200   flush();
 201 } // end function
 202
 203 ?>