library/sql_upgrade_fx.php

   1 <?php
   2 /**
   3 * Upgrading and patching functions of database.
   4 *
   5 * Functions to allow safe database modifications
   6 * during upgrading and patches.
   7 *
   8 * Copyright (C) 2008-2012 Rod Roark <rod@sunsetsystems.com>
   9 *
  10 * LICENSE: This program is free software; you can redistribute it and/or
  11 * modify it under the terms of the GNU General Public License
  12 * as published by the Free Software Foundation; either version 2
  13 * of the License, or (at your option) any later version.
  14 * This program is distributed in the hope that it will be useful,
  15 * but WITHOUT ANY WARRANTY; without even the implied warranty of
  16 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  17 * GNU General Public License for more details.
  18 * You should have received a copy of the GNU General Public License
  19 * along with this program.  If not, see <http://opensource.org/licenses/gpl-license.php>.
  20 *
  21 * @package   OpenEMR
  22 * @author    Rod Roark <rod@sunsetsystems.com>
  23 * @author    Brady Miller <brady@sparmy.com>
  24 * @author  Teny <teny@zhservices.com>
  25 * @link      http://www.open-emr.org
  26 */
  27
  28 /**
  29 * Check if a Sql table exists.
  30 *
  31 * @param  string  $tblname  Sql Table Name
  32 * @return boolean           returns true if the sql table exists
  33 */
  34 function tableExists($tblname) {
  35   $row = sqlQuery("SHOW TABLES LIKE '$tblname'");
  36   if (empty($row)) return false;
  37   return true;
  38 }
  39
  40 /**
  41 * Check if a Sql column exists in a selected table.
  42 *
  43 * @param  string  $tblname  Sql Table Name
  44 * @param  string  $colname  Sql Column Name
  45 * @return boolean           returns true if the sql column exists
  46 */
  47 function columnExists($tblname, $colname) {
  48   $row = sqlQuery("SHOW COLUMNS FROM $tblname LIKE '$colname'");
  49   if (empty($row)) return false;
  50   return true;
  51 }
  52
  53 /**
  54 * Check if a Sql column has a certain type.
  55 *
  56 * @param  string  $tblname  Sql Table Name
  57 * @param  string  $colname  Sql Column Name
  58 * @param  string  $coltype  Sql Column Type
  59 * @return boolean           returns true if the sql column is of the specified type
  60 */
  61 function columnHasType($tblname, $colname, $coltype) {
  62   $row = sqlQuery("SHOW COLUMNS FROM $tblname LIKE '$colname'");
  63   if (empty($row)) return true;
  64   return (strcasecmp($row['Type'], $coltype) == 0);
  65 }
  66
  67 /**
  68 * Check if a Sql row exists. (with one value)
  69 *
  70 * @param  string  $tblname  Sql Table Name
  71 * @param  string  $colname  Sql Column Name
  72 * @param  string  $value    Sql value
  73 * @return boolean           returns true if the sql row does exist
  74 */
  75 function tableHasRow($tblname, $colname, $value) {
  76   $row = sqlQuery("SELECT COUNT(*) AS count FROM $tblname WHERE " .
  77     "$colname LIKE '$value'");
  78   return $row['count'] ? true : false;
  79 }
  80
  81 /**
  82 * Check if a Sql row exists. (with two values)
  83 *
  84 * @param  string  $tblname   Sql Table Name
  85 * @param  string  $colname   Sql Column Name 1
  86 * @param  string  $value     Sql value 1
  87 * @param  string  $colname2  Sql Column Name 2
  88 * @param  string  $value2    Sql value 2
  89 * @return boolean            returns true if the sql row does exist
  90 */
  91 function tableHasRow2D($tblname, $colname, $value, $colname2, $value2) {
  92   $row = sqlQuery("SELECT COUNT(*) AS count FROM $tblname WHERE " .
  93     "$colname LIKE '$value' AND $colname2 LIKE '$value2'");
  94   return $row['count'] ? true : false;
  95 }
  96
  97 /**
  98 * Check if a Sql row exists. (with three values)
  99 *
 100 * @param  string  $tblname   Sql Table Name
 101 * @param  string  $colname   Sql Column Name 1
 102 * @param  string  $value     Sql value 1
 103 * @param  string  $colname2  Sql Column Name 2
 104 * @param  string  $value2    Sql value 2
 105 * @param  string  $colname3  Sql Column Name 3
 106 * @param  string  $value3    Sql value 3
 107 * @return boolean            returns true if the sql row does exist
 108 */
 109 function tableHasRow3D($tblname, $colname, $value, $colname2, $value2, $colname3, $value3) {
 110   $row = sqlQuery("SELECT COUNT(*) AS count FROM $tblname WHERE " .
 111     "$colname LIKE '$value' AND $colname2 LIKE '$value2' AND $colname3 LIKE '$value3'");
 112   return $row['count'] ? true : false;
 113 }
 114
 115 /**
 116 * Check if a Sql row exists. (with four values)
 117 *
 118 * @param  string  $tblname   Sql Table Name
 119 * @param  string  $colname   Sql Column Name 1
 120 * @param  string  $value     Sql value 1
 121 * @param  string  $colname2  Sql Column Name 2
 122 * @param  string  $value2    Sql value 2
 123 * @param  string  $colname3  Sql Column Name 3
 124 * @param  string  $value3    Sql value 3
 125 * @param  string  $colname4  Sql Column Name 4
 126 * @param  string  $value4    Sql value 4
 127 * @return boolean            returns true if the sql row does exist
 128 */
 129 function tableHasRow4D($tblname, $colname, $value, $colname2, $value2, $colname3, $value3, $colname4, $value4) {
 130   $row = sqlQuery("SELECT COUNT(*) AS count FROM $tblname WHERE " .
 131     "$colname LIKE '$value' AND $colname2 LIKE '$value2' AND $colname3 LIKE '$value3' AND $colname4 LIKE '$value4'");
 132   return $row['count'] ? true : false;
 133 }
 134
 135 /**
 136 * Check if a Sql table has a certain index/key.
 137 *
 138 * @param  string  $tblname  Sql Table Name
 139 * @param  string  $colname  Sql Index/Key
 140 * @return boolean           returns true if the sql tables has the specified index/key
 141 */
 142 function tableHasIndex($tblname, $colname) {
 143   $row = sqlQuery("SHOW INDEX FROM `$tblname` WHERE `Key_name` = '$colname'");
 144   return (empty($row)) ? false : true;
 145 }
 146
 147 /**
 148 * Function to migrate the Clickoptions settings (if exist) from the codebase into the database.
 149 *  Note this function is only run once in the sql upgrade script (from 4.1.1 to 4.1.2) if the
 150 *  issue_types sql table does not exist.
 151 */
 152 function clickOptionsMigrate() {
 153   // If the clickoptions.txt file exist, then import it.
 154   if (file_exists(dirname(__FILE__)."/../sites/".$_SESSION['site_id']."/clickoptions.txt")) {
 155     $file_handle = fopen(dirname(__FILE__)."/../sites/".$_SESSION['site_id']."/clickoptions.txt", "rb");
 156     $seq  = 10;
 157     $prev = '';
 158     echo "Importing clickoption setting<br>";
 159     while (!feof($file_handle) ) {
 160       $line_of_text = fgets($file_handle);
 161       if (preg_match('/^#/', $line_of_text)) continue;
 162       if ($line_of_text == "") continue;
 163       $parts = explode('::', $line_of_text);
 164       $parts[0] = trim(str_replace("\r\n","",$parts[0]));
 165       $parts[1] = trim(str_replace("\r\n","",$parts[1]));
 166       if ($parts[0] != $prev) {
 167         $sql1 = "INSERT INTO list_options (`list_id`,`option_id`,`title`) VALUES (?,?,?)";
 168         SqlStatement($sql1, array('lists',$parts[0].'_issue_list',ucwords(str_replace("_"," ",$parts[0])).' Issue List') );
 169         $seq = 10;
 170       }
 171       $sql2 = "INSERT INTO list_options (`list_id`,`option_id`,`title`,`seq`) VALUES (?,?,?,?)";
 172       SqlStatement($sql2, array($parts[0].'_issue_list', $parts[1], $parts[1], $seq) );
 173       $seq = $seq + 10;
 174       $prev = $parts[0];
 175     }
 176     fclose($file_handle);
 177   }
 178 }
 179
 180 /**
 181 * Upgrade or patch the database with a selected upgrade/patch file.
 182 *
 183 * The following "functions" within the selected file will be processed:
 184 *
 185 * #IfNotTable
 186 *   argument: table_name
 187 *   behavior: if the table_name does not exist,  the block will be executed
 188 *
 189 * #IfTable
 190 *   argument: table_name
 191 *   behavior: if the table_name does exist, the block will be executed
 192 *
 193 * #IfMissingColumn
 194 *   arguments: table_name colname
 195 *   behavior:  if the table exists but the column does not,  the block will be executed
 196 *
 197 * #IfNotColumnType
 198 *   arguments: table_name colname value
 199 *   behavior:  If the table table_name does not have a column colname with a data type equal to value, then the block will be executed
 200 *
 201 * #IfNotRow
 202 *   arguments: table_name colname value
 203 *   behavior:  If the table table_name does not have a row where colname = value, the block will be executed.
 204 *
 205 * #IfNotRow2D
 206 *   arguments: table_name colname value colname2 value2
 207 *   behavior:  If the table table_name does not have a row where colname = value AND colname2 = value2, the block will be executed.
 208 *
 209 * #IfNotRow3D
 210 *   arguments: table_name colname value colname2 value2 colname3 value3
 211 *   behavior:  If the table table_name does not have a row where colname = value AND colname2 = value2 AND colname3 = value3, the block will be executed.
 212 *
 213 * #IfNotRow4D
 214 *   arguments: table_name colname value colname2 value2 colname3 value3 colname4 value4
 215 *   behavior:  If the table table_name does not have a row where colname = value AND colname2 = value2 AND colname3 = value3 AND colname4 = value4, the block will be executed.
 216 *
 217 * #IfNotRow2Dx2
 218 *   desc:      This is a very specialized function to allow adding items to the list_options table to avoid both redundant option_id and title in each element.
 219 *   arguments: table_name colname value colname2 value2 colname3 value3
 220 *   behavior:  The block will be executed if both statements below are true:
 221 *              1) The table table_name does not have a row where colname = value AND colname2 = value2.
 222 *              2) The table table_name does not have a row where colname = value AND colname3 = value3.
 223 *
 224 * #IfRow2D
 225 *   arguments: table_name colname value colname2 value2
 226 *   behavior:  If the table table_name does have a row where colname = value AND colname2 = value2, the block will be executed.
 227 *
 228 * #IfRow3D
 229 *   arguments: table_name colname value colname2 value2 colname3 value3
 230 *   behavior:  If the table table_name does have a row where colname = value AND colname2 = value2 AND colname3 = value3, the block will be executed.
 231 *
 232 * #IfIndex
 233 *   desc:      This function is most often used for dropping of indexes/keys.
 234 *   arguments: table_name colname
 235 *   behavior:  If the table and index exist the relevant statements are executed, otherwise not.
 236 *
 237 * #IfNotIndex
 238 *   desc:      This function will allow adding of indexes/keys.
 239 *   arguments: table_name colname
 240 *   behavior:  If the index does not exist, it will be created
 241 *
 242 * #IfNotMigrateClickOptions
 243 *   Custom function for the importing of the Clickoptions settings (if exist) from the codebase into the database
 244 *
 245 * #EndIf
 246 *   all blocks are terminated with a #EndIf statement.
 247 *
 248 * @param  string  $filename  Sql upgrade/patch filename
 249 */
 250 function upgradeFromSqlFile($filename) {
 251   global $webserver_root;
 252
 253   flush();
 254   echo "<font color='green'>Processing $filename ...</font><br />\n";
 255
 256   $fullname = "$webserver_root/sql/$filename";
 257
 258   $fd = fopen($fullname, 'r');
 259   if ($fd == FALSE) {
 260     echo "ERROR.  Could not open '$fullname'.\n";
 261     flush();
 262     break;
 263   }
 264
 265   $query = "";
 266   $line = "";
 267   $skipping = false;
 268
 269   while (!feof ($fd)){
 270     $line = fgets($fd, 2048);
 271     $line = rtrim($line);
 272
 273     if (preg_match('/^\s*--/', $line)) continue;
 274     if ($line == "") continue;
 275
 276     if (preg_match('/^#IfNotTable\s+(\S+)/', $line, $matches)) {
 277       $skipping = tableExists($matches[1]);
 278       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 279     }
 280     else if (preg_match('/^#IfTable\s+(\S+)/', $line, $matches)) {
 281       $skipping = ! tableExists($matches[1]);
 282       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 283     }
 284     else if (preg_match('/^#IfMissingColumn\s+(\S+)\s+(\S+)/', $line, $matches)) {
 285       if (tableExists($matches[1])) {
 286         $skipping = columnExists($matches[1], $matches[2]);
 287       }
 288       else {
 289         // If no such table then the column is deemed not "missing".
 290         $skipping = true;
 291       }
 292       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 293     }
 294     else if (preg_match('/^#IfNotColumnType\s+(\S+)\s+(\S+)\s+(\S+)/', $line, $matches)) {
 295       if (tableExists($matches[1])) {
 296         $skipping = columnHasType($matches[1], $matches[2], $matches[3]);
 297       }
 298       else {
 299         // If no such table then the column type is deemed not "missing".
 300         $skipping = true;
 301       }
 302       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 303     }
 304     else if (preg_match('/^#IfIndex\s+(\S+)\s+(\S+)/', $line, $matches)) {
 305       if (tableExists($matches[1])) {
 306         // If no such index then skip.
 307         $skipping = !tableHasIndex($matches[1], $matches[2]);
 308       }
 309       else {
 310         // If no such table then skip.
 311         $skipping = true;
 312       }
 313       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 314     }
 315     else if (preg_match('/^#IfNotIndex\s+(\S+)\s+(\S+)/', $line, $matches)) {
 316       if (tableExists($matches[1])) {
 317         $skipping = tableHasIndex($matches[1], $matches[2]);
 318       }
 319       else {
 320         // If no such table then the index is deemed not "missing".
 321         $skipping = true;
 322       }
 323       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 324     }
 325     else if (preg_match('/^#IfNotRow\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
 326       if (tableExists($matches[1])) {
 327         $skipping = tableHasRow($matches[1], $matches[2], $matches[3]);
 328       }
 329       else {
 330         // If no such table then the row is deemed not "missing".
 331         $skipping = true;
 332       }
 333       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 334     }
 335     else if (preg_match('/^#IfNotRow2D\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
 336       if (tableExists($matches[1])) {
 337         $skipping = tableHasRow2D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5]);
 338       }
 339       else {
 340         // If no such table then the row is deemed not "missing".
 341         $skipping = true;
 342       }
 343       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 344     }
 345     else if (preg_match('/^#IfNotRow3D\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
 346       if (tableExists($matches[1])) {
 347         $skipping = tableHasRow3D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5], $matches[6], $matches[7]);
 348       }
 349       else {
 350         // If no such table then the row is deemed not "missing".
 351         $skipping = true;
 352       }
 353       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 354     }
 355     else if (preg_match('/^#IfNotRow4D\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
 356       if (tableExists($matches[1])) {
 357         $skipping = tableHasRow4D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5], $matches[6], $matches[7], $matches[8], $matches[9]);
 358       }
 359       else {
 360         // If no such table then the row is deemed not "missing".
 361         $skipping = true;
 362       }
 363       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 364     }
 365     else if (preg_match('/^#IfNotRow2Dx2\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
 366       if (tableExists($matches[1])) {
 367         // If either check exist, then will skip
 368         $firstCheck = tableHasRow2D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5]);
 369         $secondCheck = tableHasRow2D($matches[1], $matches[2], $matches[3], $matches[6], $matches[7]);
 370         if ($firstCheck || $secondCheck) {
 371           $skipping = true;
 372         }
 373         else {
 374           $skipping = false;
 375         }
 376       }
 377       else {
 378         // If no such table then the row is deemed not "missing".
 379         $skipping = true;
 380       }
 381       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 382     }
 383     else if (preg_match('/^#IfRow2D\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
 384       if (tableExists($matches[1])) {
 385         $skipping = !(tableHasRow2D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5]));
 386       }
 387       else {
 388         // If no such table then should skip.
 389         $skipping = true;
 390       }
 391       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 392     }
 393     else if (preg_match('/^#IfRow3D\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
 394         if (tableExists($matches[1])) {
 395                 $skipping = !(tableHasRow3D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5], $matches[6], $matches[7]));
 396         }
 397         else {
 398                 // If no such table then should skip.
 399                 $skipping = true;
 400         }
 401         if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 402     }
 403     else if (preg_match('/^#IfNotMigrateClickOptions/', $line)) {
 404       if (tableExists("issue_types")) {
 405         $skipping = true;
 406       }
 407       else {
 408         // Create issue_types table and import the Issue Types and clickoptions settings from codebase into the database
 409         clickOptionsMigrate();
 410         $skipping = false;
 411       }
 412       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 413     }
 414     else if (preg_match('/^#EndIf/', $line)) {
 415       $skipping = false;
 416     }
 417
 418     if (preg_match('/^\s*#/', $line)) continue;
 419     if ($skipping) continue;
 420
 421     $query = $query . $line;
 422     if (substr($query, -1) == ';') {
 423       $query = rtrim($query, ';');
 424       echo "$query<br />\n";
 425       if (!sqlStatement($query)) {
 426         echo "<font color='red'>The above statement failed: " .
 427           mysql_error() . "<br />Upgrading will continue.<br /></font>\n";
 428       }
 429       $query = '';
 430     }
 431   }
 432   flush();
 433 } // end function
 434
 435 ?>