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('/^#IfMissingColumn\s+(\S+)\s+(\S+)/', $line, $matches)) {
  82       if (tableExists($matches[1])) {
  83         $skipping = columnExists($matches[1], $matches[2]);
  84       }
  85       else {
  86         // If no such table then the column is deemed not "missing".
  87         $skipping = true;
  88       }
  89       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
  90     }
  91     else if (preg_match('/^#IfNotColumnType\s+(\S+)\s+(\S+)\s+(\S+)/', $line, $matches)) {
  92       if (tableExists($matches[1])) {
  93         $skipping = columnHasType($matches[1], $matches[2], $matches[3]);
  94       }
  95       else {
  96         // If no such table then the column type is deemed not "missing".
  97         $skipping = true;
  98       }
  99       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 100     }
 101     else if (preg_match('/^#IfNotRow\s+(\S+)\s+(\S+)\s+(\S+)/', $line, $matches)) {
 102       if (tableExists($matches[1])) {
 103         $skipping = tableHasRow($matches[1], $matches[2], $matches[3]);
 104       }
 105       else {
 106         // If no such table then the row is deemed not "missing".
 107         $skipping = true;
 108       }
 109       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 110     }
 111     else if (preg_match('/^#IfNotRow2D\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)/', $line, $matches)) {
 112       if (tableExists($matches[1])) {
 113         $skipping = tableHasRow2D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5]);
 114       }
 115       else {
 116         // If no such table then the row is deemed not "missing".
 117         $skipping = true;
 118       }
 119       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 120     }
 121
 122     else if (preg_match('/^#EndIf/', $line)) {
 123       $skipping = false;
 124     }
 125
 126     if (preg_match('/^\s*#/', $line)) continue;
 127     if ($skipping) continue;
 128
 129     $query = $query . $line;
 130     if (substr($query, -1) == ';') {
 131       $query = rtrim($query, ';');
 132       echo "$query<br />\n";
 133       if (!sqlStatement($query)) {
 134         echo "<font color='red'>The above statement failed: " .
 135           mysql_error() . "<br />Upgrading will continue.<br /></font>\n";
 136       }
 137       $query = '';
 138     }
 139   }
 140   flush();
 141 } // end function
 142
 143 $versions = array();
 144 $sqldir = "$webserver_root/sql";
 145 $dh = opendir($sqldir);
 146 if (! $dh) die("Cannot read $sqldir");
 147 while (false !== ($sfname = readdir($dh))) {
 148   if (substr($sfname, 0, 1) == '.') continue;
 149   if (preg_match('/^(\d+)_(\d+)_(\d+)-to-\d+_\d+_\d+_upgrade.sql$/', $sfname, $matches)) {
 150     $version = $matches[1] . '.' . $matches[2] . '.' . $matches[3];
 151     $versions[$version] = $sfname;
 152   }
 153 }
 154 closedir($dh);
 155 ksort($versions);
 156 ?>
 157 <html>
 158 <head>
 159 <title>OpenEMR Database Upgrade</title>
 160 <link rel='STYLESHEET' href='interface/themes/style_blue.css'>
 161 </head>
 162 <body>
 163 <center>
 164 <span class='title'>OpenEMR Database Upgrade</span>
 165 <br>
 166 </center>
 167 <?php
 168 if (!empty($_POST['form_submit'])) {
 169   $form_old_version = $_POST['form_old_version'];
 170
 171   foreach ($versions as $version => $filename) {
 172     if (strcmp($version, $form_old_version) < 0) continue;
 173     upgradeFromSqlFile($filename);
 174   }
 175
 176   if (!empty($GLOBALS['ippf_specific'])) {
 177     // Reload custom lists, layouts and codes for IPPF.
 178     upgradeFromSqlFile('ippf_layout.sql');
 179   }
 180
 181   echo "<p><font color='green'>Database upgrade finished.</font></p>\n";
 182   echo "</body></html>\n";
 183   exit();
 184 }
 185
 186 ?>
 187 <center>
 188 <form method='post' action='sql_upgrade.php'>
 189 <p>Please select the prior release you are converting from:
 190 <select name='form_old_version'>
 191 <?php
 192 foreach ($versions as $version => $filename) {
 193   echo " <option value='$version'";
 194   // Defaulting to 2.8.3 only because it was out there for a long time,
 195   // and nothing should go wrong if that's too old.
 196   if ($version === '2.8.3') echo " selected";
 197   echo ">$version</option>\n";
 198 }
 199 ?>
 200 </select>
 201 </p>
 202 <p>If you are unsure or were using a development version between two
 203 releases, then choose the older of possible releases.</p>
 204 <p><input type='submit' name='form_submit' value='Upgrade Database' /></p>
 205 </form>
 206 </center>
 207 </body>
 208 </html>