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 * Check if a list exists.
 148 *
 149 * @param  string  $option_id  Sql List Option ID
 150 * @return boolean           returns true if the list exists
 151 */
 152 function listExists($option_id) {
 153   $row = sqlQuery("SELECT * FROM list_options WHERE list_id = 'lists' AND option_id = ?", array($option_id));
 154   if (empty($row)) return false;
 155   return true;
 156 }
 157 /**
 158 * Function to migrate the Clickoptions settings (if exist) from the codebase into the database.
 159 *  Note this function is only run once in the sql upgrade script (from 4.1.1 to 4.1.2) if the
 160 *  issue_types sql table does not exist.
 161 */
 162 function clickOptionsMigrate() {
 163   // If the clickoptions.txt file exist, then import it.
 164   if (file_exists(dirname(__FILE__)."/../sites/".$_SESSION['site_id']."/clickoptions.txt")) {
 165     $file_handle = fopen(dirname(__FILE__)."/../sites/".$_SESSION['site_id']."/clickoptions.txt", "rb");
 166     $seq  = 10;
 167     $prev = '';
 168     echo "Importing clickoption setting<br>";
 169     while (!feof($file_handle) ) {
 170       $line_of_text = fgets($file_handle);
 171       if (preg_match('/^#/', $line_of_text)) continue;
 172       if ($line_of_text == "") continue;
 173       $parts = explode('::', $line_of_text);
 174       $parts[0] = trim(str_replace("\r\n","",$parts[0]));
 175       $parts[1] = trim(str_replace("\r\n","",$parts[1]));
 176       if ($parts[0] != $prev) {
 177         $sql1 = "INSERT INTO list_options (`list_id`,`option_id`,`title`) VALUES (?,?,?)";
 178         SqlStatement($sql1, array('lists',$parts[0].'_issue_list',ucwords(str_replace("_"," ",$parts[0])).' Issue List') );
 179         $seq = 10;
 180       }
 181       $sql2 = "INSERT INTO list_options (`list_id`,`option_id`,`title`,`seq`) VALUES (?,?,?,?)";
 182       SqlStatement($sql2, array($parts[0].'_issue_list', $parts[1], $parts[1], $seq) );
 183       $seq = $seq + 10;
 184       $prev = $parts[0];
 185     }
 186     fclose($file_handle);
 187   }
 188 }
 189 /**
 190 *  Function to create list Occupation.
 191 *  Note this function is only run once in the sql upgrade script  if the list Occupation does not exist
 192 */
 193 function CreateOccupationList() {
 194    $res = sqlStatement("SELECT DISTINCT occupation FROM patient_data WHERE occupation <> ''");
 195    while($row = sqlFetchArray($res)) {
 196     $records[] = $row['occupation'];
 197    }
 198    sqlStatement("INSERT INTO list_options (list_id, option_id, title) VALUES('lists', 'Occupation', 'Occupation')");
 199    if(count($records)>0) {
 200     $seq = 0;
 201     foreach ($records as $key => $value) {
 202      sqlStatement("INSERT INTO list_options ( list_id, option_id, title, seq) VALUES ('Occupation', ?, ?, ?)", array($value, $value, ($seq+10)));
 203      $seq = $seq + 10;
 204     }
 205    }
 206 }
 207 /**
 208 *  Function to create list reaction.
 209 *  Note this function is only run once in the sql upgrade script  if the list reaction does not exist
 210 */
 211 function CreateReactionList() {
 212    $res = sqlStatement("SELECT DISTINCT reaction FROM lists WHERE reaction <> ''");
 213    while($row = sqlFetchArray($res)) {
 214     $records[] = $row['reaction'];
 215    }
 216    sqlStatement("INSERT INTO list_options (list_id, option_id, title) VALUES('lists', 'reaction', 'Reaction')");
 217    if(count($records)>0) {
 218     $seq = 0;
 219     foreach ($records as $key => $value) {
 220      sqlStatement("INSERT INTO list_options ( list_id, option_id, title, seq) VALUES ('reaction', ?, ?, ?)", array($value, $value, ($seq+10)));
 221      $seq = $seq + 10;
 222     }
 223    }
 224 }
 225
 226 /**
 227 * Upgrade or patch the database with a selected upgrade/patch file.
 228 *
 229 * The following "functions" within the selected file will be processed:
 230 *
 231 * #IfNotTable
 232 *   argument: table_name
 233 *   behavior: if the table_name does not exist,  the block will be executed
 234 *
 235 * #IfTable
 236 *   argument: table_name
 237 *   behavior: if the table_name does exist, the block will be executed
 238 *
 239 * #IfMissingColumn
 240 *   arguments: table_name colname
 241 *   behavior:  if the table exists but the column does not,  the block will be executed
 242 *
 243 * #IfNotColumnType
 244 *   arguments: table_name colname value
 245 *   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
 246 *
 247 * #IfNotRow
 248 *   arguments: table_name colname value
 249 *   behavior:  If the table table_name does not have a row where colname = value, the block will be executed.
 250 *
 251 * #IfNotRow2D
 252 *   arguments: table_name colname value colname2 value2
 253 *   behavior:  If the table table_name does not have a row where colname = value AND colname2 = value2, the block will be executed.
 254 *
 255 * #IfNotRow3D
 256 *   arguments: table_name colname value colname2 value2 colname3 value3
 257 *   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.
 258 *
 259 * #IfNotRow4D
 260 *   arguments: table_name colname value colname2 value2 colname3 value3 colname4 value4
 261 *   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.
 262 *
 263 * #IfNotRow2Dx2
 264 *   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.
 265 *   arguments: table_name colname value colname2 value2 colname3 value3
 266 *   behavior:  The block will be executed if both statements below are true:
 267 *              1) The table table_name does not have a row where colname = value AND colname2 = value2.
 268 *              2) The table table_name does not have a row where colname = value AND colname3 = value3.
 269 *
 270 * #IfRow2D
 271 *   arguments: table_name colname value colname2 value2
 272 *   behavior:  If the table table_name does have a row where colname = value AND colname2 = value2, the block will be executed.
 273 *
 274 * #IfRow3D
 275 *   arguments: table_name colname value colname2 value2 colname3 value3
 276 *   behavior:  If the table table_name does have a row where colname = value AND colname2 = value2 AND colname3 = value3, the block will be executed.
 277 *
 278 * #IfIndex
 279 *   desc:      This function is most often used for dropping of indexes/keys.
 280 *   arguments: table_name colname
 281 *   behavior:  If the table and index exist the relevant statements are executed, otherwise not.
 282 *
 283 * #IfNotIndex
 284 *   desc:      This function will allow adding of indexes/keys.
 285 *   arguments: table_name colname
 286 *   behavior:  If the index does not exist, it will be created
 287 *
 288 * #IfNotMigrateClickOptions
 289 *   Custom function for the importing of the Clickoptions settings (if exist) from the codebase into the database
 290 *
 291 * #IfNotListOccupation
 292 * Custom function for creating Occupation List
 293 *
 294 * #IfNotListReaction
 295 * Custom function for creating Reaction List
 296 *
 297 * #EndIf
 298 *   all blocks are terminated with a #EndIf statement.
 299 *
 300 * @param  string  $filename  Sql upgrade/patch filename
 301 */
 302 function upgradeFromSqlFile($filename) {
 303   global $webserver_root;
 304
 305   flush();
 306   echo "<font color='green'>Processing $filename ...</font><br />\n";
 307
 308   $fullname = "$webserver_root/sql/$filename";
 309
 310   $fd = fopen($fullname, 'r');
 311   if ($fd == FALSE) {
 312     echo "ERROR.  Could not open '$fullname'.\n";
 313     flush();
 314     break;
 315   }
 316
 317   $query = "";
 318   $line = "";
 319   $skipping = false;
 320
 321   while (!feof ($fd)){
 322     $line = fgets($fd, 2048);
 323     $line = rtrim($line);
 324
 325     if (preg_match('/^\s*--/', $line)) continue;
 326     if ($line == "") continue;
 327
 328     if (preg_match('/^#IfNotTable\s+(\S+)/', $line, $matches)) {
 329       $skipping = tableExists($matches[1]);
 330       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 331     }
 332     else if (preg_match('/^#IfTable\s+(\S+)/', $line, $matches)) {
 333       $skipping = ! tableExists($matches[1]);
 334       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 335     }
 336     else if (preg_match('/^#IfMissingColumn\s+(\S+)\s+(\S+)/', $line, $matches)) {
 337       if (tableExists($matches[1])) {
 338         $skipping = columnExists($matches[1], $matches[2]);
 339       }
 340       else {
 341         // If no such table then the column is deemed not "missing".
 342         $skipping = true;
 343       }
 344       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 345     }
 346     else if (preg_match('/^#IfNotColumnType\s+(\S+)\s+(\S+)\s+(\S+)/', $line, $matches)) {
 347       if (tableExists($matches[1])) {
 348         $skipping = columnHasType($matches[1], $matches[2], $matches[3]);
 349       }
 350       else {
 351         // If no such table then the column type is deemed not "missing".
 352         $skipping = true;
 353       }
 354       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 355     }
 356     else if (preg_match('/^#IfIndex\s+(\S+)\s+(\S+)/', $line, $matches)) {
 357       if (tableExists($matches[1])) {
 358         // If no such index then skip.
 359         $skipping = !tableHasIndex($matches[1], $matches[2]);
 360       }
 361       else {
 362         // If no such table then skip.
 363         $skipping = true;
 364       }
 365       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 366     }
 367     else if (preg_match('/^#IfNotIndex\s+(\S+)\s+(\S+)/', $line, $matches)) {
 368       if (tableExists($matches[1])) {
 369         $skipping = tableHasIndex($matches[1], $matches[2]);
 370       }
 371       else {
 372         // If no such table then the index is deemed not "missing".
 373         $skipping = true;
 374       }
 375       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 376     }
 377     else if (preg_match('/^#IfNotRow\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
 378       if (tableExists($matches[1])) {
 379         $skipping = tableHasRow($matches[1], $matches[2], $matches[3]);
 380       }
 381       else {
 382         // If no such table then the row is deemed not "missing".
 383         $skipping = true;
 384       }
 385       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 386     }
 387     else if (preg_match('/^#IfNotRow2D\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
 388       if (tableExists($matches[1])) {
 389         $skipping = tableHasRow2D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5]);
 390       }
 391       else {
 392         // If no such table then the row is deemed not "missing".
 393         $skipping = true;
 394       }
 395       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 396     }
 397     else if (preg_match('/^#IfNotRow3D\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
 398       if (tableExists($matches[1])) {
 399         $skipping = tableHasRow3D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5], $matches[6], $matches[7]);
 400       }
 401       else {
 402         // If no such table then the row is deemed not "missing".
 403         $skipping = true;
 404       }
 405       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 406     }
 407     else if (preg_match('/^#IfNotRow4D\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
 408       if (tableExists($matches[1])) {
 409         $skipping = tableHasRow4D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5], $matches[6], $matches[7], $matches[8], $matches[9]);
 410       }
 411       else {
 412         // If no such table then the row is deemed not "missing".
 413         $skipping = true;
 414       }
 415       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 416     }
 417     else if (preg_match('/^#IfNotRow2Dx2\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
 418       if (tableExists($matches[1])) {
 419         // If either check exist, then will skip
 420         $firstCheck = tableHasRow2D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5]);
 421         $secondCheck = tableHasRow2D($matches[1], $matches[2], $matches[3], $matches[6], $matches[7]);
 422         if ($firstCheck || $secondCheck) {
 423           $skipping = true;
 424         }
 425         else {
 426           $skipping = false;
 427         }
 428       }
 429       else {
 430         // If no such table then the row is deemed not "missing".
 431         $skipping = true;
 432       }
 433       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 434     }
 435     else if (preg_match('/^#IfRow2D\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
 436       if (tableExists($matches[1])) {
 437         $skipping = !(tableHasRow2D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5]));
 438       }
 439       else {
 440         // If no such table then should skip.
 441         $skipping = true;
 442       }
 443       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 444     }
 445     else if (preg_match('/^#IfRow3D\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
 446         if (tableExists($matches[1])) {
 447                 $skipping = !(tableHasRow3D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5], $matches[6], $matches[7]));
 448         }
 449         else {
 450                 // If no such table then should skip.
 451                 $skipping = true;
 452         }
 453         if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 454     }
 455     else if (preg_match('/^#IfNotMigrateClickOptions/', $line)) {
 456       if (tableExists("issue_types")) {
 457         $skipping = true;
 458       }
 459       else {
 460         // Create issue_types table and import the Issue Types and clickoptions settings from codebase into the database
 461         clickOptionsMigrate();
 462         $skipping = false;
 463       }
 464       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 465     }
 466     else if (preg_match('/^#IfNotListOccupation/', $line)) {
 467       if (listExists("Occupation")) {
 468         $skipping = true;
 469       }
 470       else {
 471         // Create Occupation list
 472         CreateOccupationList();
 473         $skipping = false;
 474         echo "<font color='green'>Built Occupation List</font><br />\n";
 475       }
 476       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 477     }
 478     else if (preg_match('/^#IfNotListReaction/', $line)) {
 479       if (listExists("reaction")) {
 480         $skipping = true;
 481       }
 482       else {
 483         // Create Reaction list
 484         CreateReactionList();
 485         $skipping = false;
 486         echo "<font color='green'>Built Reaction List</font><br />\n";
 487       }
 488       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 489     }
 490     else if (preg_match('/^#EndIf/', $line)) {
 491       $skipping = false;
 492     }
 493
 494     if (preg_match('/^\s*#/', $line)) continue;
 495     if ($skipping) continue;
 496
 497     $query = $query . $line;
 498     if (substr($query, -1) == ';') {
 499       $query = rtrim($query, ';');
 500       echo "$query<br />\n";
 501       if (!sqlStatement($query)) {
 502         echo "<font color='red'>The above statement failed: " .
 503           mysql_error() . "<br />Upgrading will continue.<br /></font>\n";
 504       }
 505       $query = '';
 506     }
 507   }
 508   flush();
 509 } // end function
 510
 511 ?>