sql_upgrade.php

   1 <?php
   2 // Copyright (C) 2008-2009 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 // This may be run after an upgraded OpenEMR has been installed.
  10 // Its purpose is to upgrade the MySQL OpenEMR database as needed
  11 // for the new release.
  12
  13 // Disable PHP timeout.  This will not work in safe mode.
  14 ini_set('max_execution_time', '0');
  15
  16 $ignoreAuth = true; // no login required
  17
  18 require_once('interface/globals.php');
  19 require_once('library/sql.inc');
  20
  21 function tableExists($tblname) {
  22   $row = sqlQuery("SHOW TABLES LIKE '$tblname'");
  23   if (empty($row)) return false;
  24   return true;
  25 }
  26
  27 function columnExists($tblname, $colname) {
  28   $row = sqlQuery("SHOW COLUMNS FROM $tblname LIKE '$colname'");
  29   if (empty($row)) return false;
  30   return true;
  31 }
  32
  33 function columnHasType($tblname, $colname, $coltype) {
  34   $row = sqlQuery("SHOW COLUMNS FROM $tblname LIKE '$colname'");
  35   if (empty($row)) return true;
  36   return (strcasecmp($row['Type'], $coltype) == 0);
  37 }
  38
  39 function tableHasRow($tblname, $colname, $value) {
  40   $row = sqlQuery("SELECT COUNT(*) AS count FROM $tblname WHERE " .
  41     "$colname LIKE '$value'");
  42   return $row['count'] ? true : false;
  43 }
  44
  45 function tableHasRow2D($tblname, $colname, $value, $colname2, $value2) {
  46   $row = sqlQuery("SELECT COUNT(*) AS count FROM $tblname WHERE " .
  47     "$colname LIKE '$value' AND $colname2 LIKE '$value2'");
  48   return $row['count'] ? true : false;
  49 }
  50
  51 function upgradeFromSqlFile($filename) {
  52   global $webserver_root;
  53
  54   flush();
  55   echo "<font color='green'>Processing $filename ...</font><br />\n";
  56
  57   $fullname = "$webserver_root/sql/$filename";
  58
  59   $fd = fopen($fullname, 'r');
  60   if ($fd == FALSE) {
  61     echo "ERROR.  Could not open '$fullname'.\n";
  62     flush();
  63     break;
  64   }
  65
  66   $query = "";
  67   $line = "";
  68   $skipping = false;
  69
  70   while (!feof ($fd)){
  71     $line = fgets($fd, 2048);
  72     $line = rtrim($line);
  73
  74     if (preg_match('/^\s*--/', $line)) continue;
  75     if ($line == "") continue;
  76
  77     if (preg_match('/^#IfNotTable\s+(\S+)/', $line, $matches)) {
  78       $skipping = tableExists($matches[1]);
  79       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
  80     }
  81     else if (preg_match('/^#IfTable\s+(\S+)/', $line, $matches)) {
  82       $skipping = ! tableExists($matches[1]);
  83       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
  84     }
  85     else if (preg_match('/^#IfMissingColumn\s+(\S+)\s+(\S+)/', $line, $matches)) {
  86       if (tableExists($matches[1])) {
  87         $skipping = columnExists($matches[1], $matches[2]);
  88       }
  89       else {
  90         // If no such table then the column is deemed not "missing".
  91         $skipping = true;
  92       }
  93       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
  94     }
  95     else if (preg_match('/^#IfNotColumnType\s+(\S+)\s+(\S+)\s+(\S+)/', $line, $matches)) {
  96       if (tableExists($matches[1])) {
  97         $skipping = columnHasType($matches[1], $matches[2], $matches[3]);
  98       }
  99       else {
 100         // If no such table then the column type is deemed not "missing".
 101         $skipping = true;
 102       }
 103       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 104     }
 105     else if (preg_match('/^#IfNotRow\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
 106       if (tableExists($matches[1])) {
 107         $skipping = tableHasRow($matches[1], $matches[2], $matches[3]);
 108       }
 109       else {
 110         // If no such table then the row is deemed not "missing".
 111         $skipping = true;
 112       }
 113       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 114     }
 115     else if (preg_match('/^#IfNotRow2D\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
 116       if (tableExists($matches[1])) {
 117         $skipping = tableHasRow2D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5]);
 118       }
 119       else {
 120         // If no such table then the row is deemed not "missing".
 121         $skipping = true;
 122       }
 123       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 124     }
 125     else if (preg_match('/^#IfNotRow2Dx2\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
 126       if (tableExists($matches[1])) {
 127         // If either check exist, then will skip
 128         $firstCheck = tableHasRow2D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5]);
 129         $secondCheck = tableHasRow2D($matches[1], $matches[2], $matches[3], $matches[6], $matches[7]);
 130         if ($firstCheck || $secondCheck) {
 131           $skipping = true;
 132         }
 133         else {
 134           $skipping = false;
 135         }
 136       }
 137       else {
 138         // If no such table then the row is deemed not "missing".
 139         $skipping = true;
 140       }
 141       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 142     }
 143     else if (preg_match('/^#EndIf/', $line)) {
 144       $skipping = false;
 145     }
 146
 147     if (preg_match('/^\s*#/', $line)) continue;
 148     if ($skipping) continue;
 149
 150     $query = $query . $line;
 151     if (substr($query, -1) == ';') {
 152       $query = rtrim($query, ';');
 153       echo "$query<br />\n";
 154       if (!sqlStatement($query)) {
 155         echo "<font color='red'>The above statement failed: " .
 156           mysql_error() . "<br />Upgrading will continue.<br /></font>\n";
 157       }
 158       $query = '';
 159     }
 160   }
 161   flush();
 162 } // end function
 163
 164 $versions = array();
 165 $sqldir = "$webserver_root/sql";
 166 $dh = opendir($sqldir);
 167 if (! $dh) die("Cannot read $sqldir");
 168 while (false !== ($sfname = readdir($dh))) {
 169   if (substr($sfname, 0, 1) == '.') continue;
 170   if (preg_match('/^(\d+)_(\d+)_(\d+)-to-\d+_\d+_\d+_upgrade.sql$/', $sfname, $matches)) {
 171     $version = $matches[1] . '.' . $matches[2] . '.' . $matches[3];
 172     $versions[$version] = $sfname;
 173   }
 174 }
 175 closedir($dh);
 176 ksort($versions);
 177 ?>
 178 <html>
 179 <head>
 180 <title>OpenEMR Database Upgrade</title>
 181 <link rel='STYLESHEET' href='interface/themes/style_blue.css'>
 182 </head>
 183 <body>
 184 <center>
 185 <span class='title'>OpenEMR Database Upgrade</span>
 186 <br>
 187 </center>
 188 <?php
 189 if (!empty($_POST['form_submit'])) {
 190   $form_old_version = $_POST['form_old_version'];
 191
 192   foreach ($versions as $version => $filename) {
 193     if (strcmp($version, $form_old_version) < 0) continue;
 194     upgradeFromSqlFile($filename);
 195   }
 196
 197   if (!empty($GLOBALS['ippf_specific'])) {
 198     // Upgrade custom stuff for IPPF.
 199     upgradeFromSqlFile('ippf_upgrade.sql');
 200   }
 201
 202   flush();
 203   echo "<font color='green'>Updating global configuration defaults...</font><br />\n";
 204   require_once("library/globals.inc.php");
 205   foreach ($GLOBALS_METADATA as $grpname => $grparr) {
 206     foreach ($grparr as $fldid => $fldarr) {
 207       list($fldname, $fldtype, $flddef, $flddesc) = $fldarr;
 208       if (substr($fldtype, 0, 2) !== 'm_') {
 209         $row = sqlQuery("SELECT count(*) AS count FROM globals WHERE gl_name = '$fldid'");
 210         if (empty($row['count'])) {
 211           sqlStatement("INSERT INTO globals ( gl_name, gl_index, gl_value ) " .
 212             "VALUES ( '$fldid', '0', '$flddef' )");
 213         }
 214       }
 215     }
 216   }
 217
 218   echo "<p><font color='green'>Database upgrade finished.</font></p>\n";
 219   echo "</body></html>\n";
 220   exit();
 221 }
 222
 223 ?>
 224 <center>
 225 <form method='post' action='sql_upgrade.php'>
 226 <p>Please select the prior release you are converting from:
 227 <select name='form_old_version'>
 228 <?php
 229 foreach ($versions as $version => $filename) {
 230   echo " <option value='$version'";
 231   // Defaulting to most recent version, which is now 3.2.0.
 232   if ($version === '3.2.0') echo " selected";
 233   echo ">$version</option>\n";
 234 }
 235 ?>
 236 </select>
 237 </p>
 238 <p>If you are unsure or were using a development version between two
 239 releases, then choose the older of possible releases.</p>
 240 <p><input type='submit' name='form_submit' value='Upgrade Database' /></p>
 241 </form>
 242 </center>
 243 </body>
 244 </html>