library/sql_upgrade_fx.php

   1 <?php
   2 // Copyright (C) 2008-2012 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('/^#IfIndex\s+(\S+)\s+(\S+)/', $line, $matches)) {
 115       if (tableExists($matches[1])) {
 116         // If no such index then skip.
 117         $skipping = !tableHasIndex($matches[1], $matches[2]);
 118       }
 119       else {
 120         // If no such table then skip.
 121         $skipping = true;
 122       }
 123       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 124     }
 125     else if (preg_match('/^#IfNotIndex\s+(\S+)\s+(\S+)/', $line, $matches)) {
 126       if (tableExists($matches[1])) {
 127         $skipping = tableHasIndex($matches[1], $matches[2]);
 128       }
 129       else {
 130         // If no such table then the index is deemed not "missing".
 131         $skipping = true;
 132       }
 133       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 134     }
 135     else if (preg_match('/^#IfNotRow\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
 136       if (tableExists($matches[1])) {
 137         $skipping = tableHasRow($matches[1], $matches[2], $matches[3]);
 138       }
 139       else {
 140         // If no such table then the row is deemed not "missing".
 141         $skipping = true;
 142       }
 143       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 144     }
 145     else if (preg_match('/^#IfNotRow2D\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
 146       if (tableExists($matches[1])) {
 147         $skipping = tableHasRow2D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5]);
 148       }
 149       else {
 150         // If no such table then the row is deemed not "missing".
 151         $skipping = true;
 152       }
 153       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 154     }
 155     else if (preg_match('/^#IfNotRow3D\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
 156       if (tableExists($matches[1])) {
 157         $skipping = tableHasRow3D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5], $matches[6], $matches[7]);
 158       }
 159       else {
 160         // If no such table then the row is deemed not "missing".
 161         $skipping = true;
 162       }
 163       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 164     }
 165     else if (preg_match('/^#IfNotRow4D\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
 166       if (tableExists($matches[1])) {
 167         $skipping = tableHasRow4D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5], $matches[6], $matches[7], $matches[8], $matches[9]);
 168       }
 169       else {
 170         // If no such table then the row is deemed not "missing".
 171         $skipping = true;
 172       }
 173       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 174     }
 175     else if (preg_match('/^#IfNotRow2Dx2\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
 176       if (tableExists($matches[1])) {
 177         // If either check exist, then will skip
 178         $firstCheck = tableHasRow2D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5]);
 179         $secondCheck = tableHasRow2D($matches[1], $matches[2], $matches[3], $matches[6], $matches[7]);
 180         if ($firstCheck || $secondCheck) {
 181           $skipping = true;
 182         }
 183         else {
 184           $skipping = false;
 185         }
 186       }
 187       else {
 188         // If no such table then the row is deemed not "missing".
 189         $skipping = true;
 190       }
 191       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 192     }
 193     else if (preg_match('/^#IfRow2D\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
 194       if (tableExists($matches[1])) {
 195         $skipping = !(tableHasRow2D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5]));
 196       }
 197       else {
 198         // If no such table then should skip.
 199         $skipping = true;
 200       }
 201       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 202     }
 203     else if (preg_match('/^#EndIf/', $line)) {
 204       $skipping = false;
 205     }
 206
 207     if (preg_match('/^\s*#/', $line)) continue;
 208     if ($skipping) continue;
 209
 210     $query = $query . $line;
 211     if (substr($query, -1) == ';') {
 212       $query = rtrim($query, ';');
 213       echo "$query<br />\n";
 214       if (!sqlStatement($query)) {
 215         echo "<font color='red'>The above statement failed: " .
 216           mysql_error() . "<br />Upgrading will continue.<br /></font>\n";
 217       }
 218       $query = '';
 219     }
 220   }
 221   flush();
 222 } // end function
 223
 224 ?>