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
 126     else if (preg_match('/^#EndIf/', $line)) {
 127       $skipping = false;
 128     }
 129
 130     if (preg_match('/^\s*#/', $line)) continue;
 131     if ($skipping) continue;
 132
 133     $query = $query . $line;
 134     if (substr($query, -1) == ';') {
 135       $query = rtrim($query, ';');
 136       echo "$query<br />\n";
 137       if (!sqlStatement($query)) {
 138         echo "<font color='red'>The above statement failed: " .
 139           mysql_error() . "<br />Upgrading will continue.<br /></font>\n";
 140       }
 141       $query = '';
 142     }
 143   }
 144   flush();
 145 } // end function
 146
 147 $versions = array();
 148 $sqldir = "$webserver_root/sql";
 149 $dh = opendir($sqldir);
 150 if (! $dh) die("Cannot read $sqldir");
 151 while (false !== ($sfname = readdir($dh))) {
 152   if (substr($sfname, 0, 1) == '.') continue;
 153   if (preg_match('/^(\d+)_(\d+)_(\d+)-to-\d+_\d+_\d+_upgrade.sql$/', $sfname, $matches)) {
 154     $version = $matches[1] . '.' . $matches[2] . '.' . $matches[3];
 155     $versions[$version] = $sfname;
 156   }
 157 }
 158 closedir($dh);
 159 ksort($versions);
 160 ?>
 161 <html>
 162 <head>
 163 <title>OpenEMR Database Upgrade</title>
 164 <link rel='STYLESHEET' href='interface/themes/style_blue.css'>
 165 </head>
 166 <body>
 167 <center>
 168 <span class='title'>OpenEMR Database Upgrade</span>
 169 <br>
 170 </center>
 171 <?php
 172 if (!empty($_POST['form_submit'])) {
 173   $form_old_version = $_POST['form_old_version'];
 174
 175   foreach ($versions as $version => $filename) {
 176     if (strcmp($version, $form_old_version) < 0) continue;
 177     upgradeFromSqlFile($filename);
 178   }
 179
 180   if (!empty($GLOBALS['ippf_specific'])) {
 181     // Upgrade custom stuff for IPPF.
 182     upgradeFromSqlFile('ippf_upgrade.sql');
 183   }
 184
 185   flush();
 186   echo "<font color='green'>Updating global configuration defaults...</font><br />\n";
 187   require_once("library/globals.inc.php");
 188   foreach ($GLOBALS_METADATA as $grpname => $grparr) {
 189     foreach ($grparr as $fldid => $fldarr) {
 190       list($fldname, $fldtype, $flddef, $flddesc) = $fldarr;
 191       if (substr($fldtype, 0, 2) !== 'm_') {
 192         $row = sqlQuery("SELECT count(*) AS count FROM globals WHERE gl_name = '$fldid'");
 193         if (empty($row['count'])) {
 194           sqlStatement("INSERT INTO globals ( gl_name, gl_index, gl_value ) " .
 195             "VALUES ( '$fldid', '0', '$flddef' )");
 196         }
 197       }
 198     }
 199   }
 200
 201   echo "<p><font color='green'>Database upgrade finished.</font></p>\n";
 202   echo "</body></html>\n";
 203   exit();
 204 }
 205
 206 ?>
 207 <center>
 208 <form method='post' action='sql_upgrade.php'>
 209 <p>Please select the prior release you are converting from:
 210 <select name='form_old_version'>
 211 <?php
 212 foreach ($versions as $version => $filename) {
 213   echo " <option value='$version'";
 214   // Defaulting to 2.8.3 only because it was out there for a long time,
 215   // and nothing should go wrong if that's too old.
 216   if ($version === '2.8.3') echo " selected";
 217   echo ">$version</option>\n";
 218 }
 219 ?>
 220 </select>
 221 </p>
 222 <p>If you are unsure or were using a development version between two
 223 releases, then choose the older of possible releases.</p>
 224 <p><input type='submit' name='form_submit' value='Upgrade Database' /></p>
 225 </form>
 226 </center>
 227 </body>
 228 </html>