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 /**
 149  * Check if a table has a certain engine
 150  *
 151  * @param string $tblname database table Name
 152  * @param string $engine engine name ( myisam, memory, innodb )...
 153  * @return boolean true if the table has been created using specified engine
 154  */
 155 function tableHasEngine($tblname, $engine) {
 156   $row = sqlQuery( 'SELECT 1 FROM information_schema.tables WHERE table_name=? AND engine=? AND table_type="BASE TABLE"', array($tblname,$engine ) );
 157   return (empty($row)) ? false : true;
 158 }
 159
 160
 161
 162 /**
 163 * Check if a list exists.
 164 *
 165 * @param  string  $option_id  Sql List Option ID
 166 * @return boolean           returns true if the list exists
 167 */
 168 function listExists($option_id) {
 169   $row = sqlQuery("SELECT * FROM list_options WHERE list_id = 'lists' AND option_id = ?", array($option_id));
 170   if (empty($row)) return false;
 171   return true;
 172 }
 173 /**
 174 * Function to migrate the Clickoptions settings (if exist) from the codebase into the database.
 175 *  Note this function is only run once in the sql upgrade script (from 4.1.1 to 4.1.2) if the
 176 *  issue_types sql table does not exist.
 177 */
 178 function clickOptionsMigrate() {
 179   // If the clickoptions.txt file exist, then import it.
 180   if (file_exists(dirname(__FILE__)."/../sites/".$_SESSION['site_id']."/clickoptions.txt")) {
 181     $file_handle = fopen(dirname(__FILE__)."/../sites/".$_SESSION['site_id']."/clickoptions.txt", "rb");
 182     $seq  = 10;
 183     $prev = '';
 184     echo "Importing clickoption setting<br>";
 185     while (!feof($file_handle) ) {
 186       $line_of_text = fgets($file_handle);
 187       if (preg_match('/^#/', $line_of_text)) continue;
 188       if ($line_of_text == "") continue;
 189       $parts = explode('::', $line_of_text);
 190       $parts[0] = trim(str_replace("\r\n","",$parts[0]));
 191       $parts[1] = trim(str_replace("\r\n","",$parts[1]));
 192       if ($parts[0] != $prev) {
 193         $sql1 = "INSERT INTO list_options (`list_id`,`option_id`,`title`) VALUES (?,?,?)";
 194         SqlStatement($sql1, array('lists',$parts[0].'_issue_list',ucwords(str_replace("_"," ",$parts[0])).' Issue List') );
 195         $seq = 10;
 196       }
 197       $sql2 = "INSERT INTO list_options (`list_id`,`option_id`,`title`,`seq`) VALUES (?,?,?,?)";
 198       SqlStatement($sql2, array($parts[0].'_issue_list', $parts[1], $parts[1], $seq) );
 199       $seq = $seq + 10;
 200       $prev = $parts[0];
 201     }
 202     fclose($file_handle);
 203   }
 204 }
 205 /**
 206 *  Function to create list Occupation.
 207 *  Note this function is only run once in the sql upgrade script  if the list Occupation does not exist
 208 */
 209 function CreateOccupationList() {
 210    $res = sqlStatement("SELECT DISTINCT occupation FROM patient_data WHERE occupation <> ''");
 211    while($row = sqlFetchArray($res)) {
 212     $records[] = $row['occupation'];
 213    }
 214    sqlStatement("INSERT INTO list_options (list_id, option_id, title) VALUES('lists', 'Occupation', 'Occupation')");
 215    if(count($records)>0) {
 216     $seq = 0;
 217     foreach ($records as $key => $value) {
 218      sqlStatement("INSERT INTO list_options ( list_id, option_id, title, seq) VALUES ('Occupation', ?, ?, ?)", array($value, $value, ($seq+10)));
 219      $seq = $seq + 10;
 220     }
 221    }
 222 }
 223 /**
 224 *  Function to create list reaction.
 225 *  Note this function is only run once in the sql upgrade script  if the list reaction does not exist
 226 */
 227 function CreateReactionList() {
 228    $res = sqlStatement("SELECT DISTINCT reaction FROM lists WHERE reaction <> ''");
 229    while($row = sqlFetchArray($res)) {
 230     $records[] = $row['reaction'];
 231    }
 232    sqlStatement("INSERT INTO list_options (list_id, option_id, title) VALUES('lists', 'reaction', 'Reaction')");
 233    if(count($records)>0) {
 234     $seq = 0;
 235     foreach ($records as $key => $value) {
 236      sqlStatement("INSERT INTO list_options ( list_id, option_id, title, seq) VALUES ('reaction', ?, ?, ?)", array($value, $value, ($seq+10)));
 237      $seq = $seq + 10;
 238     }
 239    }
 240 }
 241
 242 /*
 243 * Function to add existing values in the immunization table to the new immunization manufacturer list
 244 * This function will be executed always, but only missing values will ne inserted to the list
 245 */
 246 function CreateImmunizationManufacturerList() {
 247   $res = sqlStatement("SELECT DISTINCT manufacturer FROM immunizations WHERE manufacturer <> ''");
 248   while($row = sqlFetchArray($res)) {
 249     $records[] = $row['manufacturer'];
 250   }
 251   sqlStatement("INSERT INTO list_options (list_id, option_id, title) VALUES ('lists','Immunization_Manufacturer','Immunization Manufacturer')");
 252   if(count($records)>0) {
 253     $seq = 0;
 254     foreach ($records as $key => $value) {
 255       sqlStatement("INSERT INTO list_options ( list_id, option_id, title, seq) VALUES ('Immunization_Manufacturer', ?, ?, ?)", array($value, $value, ($seq+10)));
 256       $seq = $seq + 10;
 257     }
 258   }
 259 }
 260
 261 /**
 262  * Request to information_schema
 263  *
 264  * @param array $arg possible arguments: engine, table_name
 265  * @return SQLStatement
 266  */
 267 function getTablesList( $arg = array() ) {
 268     $binds = array();
 269     $sql = 'SELECT table_name FROM information_schema.tables WHERE table_schema=database() AND table_type="BASE TABLE"';
 270
 271     if( !empty($arg['engine'])) {
 272         $binds[] = $arg['engine'];
 273         $sql .= ' AND engine=?';
 274     }
 275
 276     if( !empty($arg['table_name'])) {
 277         $binds[] = $arg['table_name'];
 278         $sql .= ' AND table_name=?';
 279     }
 280     $res = sqlStatement( $sql, $binds );
 281
 282     $records = array();
 283     while($row = sqlFetchArray($res)) {
 284         $records[ $row['table_name'] ] = $row['table_name'];
 285     }
 286     return $records;
 287 }
 288
 289
 290 /**
 291  * Convert table engine.
 292  * @param string $table
 293  * @param string $engine
 294  * ADODB will fail if there was an error during conversion
 295  */
 296 function MigrateTableEngine( $table, $engine ) {
 297   $r = sqlStatement('ALTER TABLE `'.$table.'` ENGINE=?', $engine );
 298   return true;
 299 }
 300
 301
 302 /**
 303 * Upgrade or patch the database with a selected upgrade/patch file.
 304 *
 305 * The following "functions" within the selected file will be processed:
 306 *
 307 * #IfNotTable
 308 *   argument: table_name
 309 *   behavior: if the table_name does not exist,  the block will be executed
 310 *
 311 * #IfTable
 312 *   argument: table_name
 313 *   behavior: if the table_name does exist, the block will be executed
 314 *
 315 * #IfColumn
 316 *   arguments: table_name colname
 317 *   behavior:  if the table and column exist,  the block will be executed
 318 *
 319 * #IfMissingColumn
 320 *   arguments: table_name colname
 321 *   behavior:  if the table exists but the column does not,  the block will be executed
 322 *
 323 * #IfNotColumnType
 324 *   arguments: table_name colname value
 325 *   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
 326 *
 327 * #IfNotRow
 328 *   arguments: table_name colname value
 329 *   behavior:  If the table table_name does not have a row where colname = value, the block will be executed.
 330 *
 331 * #IfNotRow2D
 332 *   arguments: table_name colname value colname2 value2
 333 *   behavior:  If the table table_name does not have a row where colname = value AND colname2 = value2, the block will be executed.
 334 *
 335 * #IfNotRow3D
 336 *   arguments: table_name colname value colname2 value2 colname3 value3
 337 *   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.
 338 *
 339 * #IfNotRow4D
 340 *   arguments: table_name colname value colname2 value2 colname3 value3 colname4 value4
 341 *   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.
 342 *
 343 * #IfNotRow2Dx2
 344 *   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.
 345 *   arguments: table_name colname value colname2 value2 colname3 value3
 346 *   behavior:  The block will be executed if both statements below are true:
 347 *              1) The table table_name does not have a row where colname = value AND colname2 = value2.
 348 *              2) The table table_name does not have a row where colname = value AND colname3 = value3.
 349 *
 350 * #IfRow2D
 351 *   arguments: table_name colname value colname2 value2
 352 *   behavior:  If the table table_name does have a row where colname = value AND colname2 = value2, the block will be executed.
 353 *
 354 * #IfRow3D
 355 *   arguments: table_name colname value colname2 value2 colname3 value3
 356 *   behavior:  If the table table_name does have a row where colname = value AND colname2 = value2 AND colname3 = value3, the block will be executed.
 357 *
 358 * #IfIndex
 359 *   desc:      This function is most often used for dropping of indexes/keys.
 360 *   arguments: table_name colname
 361 *   behavior:  If the table and index exist the relevant statements are executed, otherwise not.
 362 *
 363 * #IfNotIndex
 364 *   desc:      This function will allow adding of indexes/keys.
 365 *   arguments: table_name colname
 366 *   behavior:  If the index does not exist, it will be created
 367 *
 368 * #IfNotMigrateClickOptions
 369 *   Custom function for the importing of the Clickoptions settings (if exist) from the codebase into the database
 370 *
 371 * #IfNotListOccupation
 372 * Custom function for creating Occupation List
 373 *
 374 * #IfNotListReaction
 375 * Custom function for creating Reaction List
 376 *
 377 * #IfTextNullFixNeeded
 378 *   desc: convert all text fields without default null to have default null.
 379 *   arguments: none
 380 *
 381 * #IfTableEngine
 382 *   desc:      Execute SQL if the table has been created with given engine specified.
 383 *   arguments: table_name engine
 384 *   behavior:  Use when engine conversion requires more than one ALTER TABLE
 385 *
 386 * #IfInnoDBMigrationNeeded
 387 *   desc: find all MyISAM tables and convert them to InnoDB.
 388 *   arguments: none
 389 *   behavior: can take a long time.
 390 *
 391 * #EndIf
 392 *   all blocks are terminated with a #EndIf statement.
 393 *
 394 * @param  string  $filename  Sql upgrade/patch filename
 395 */
 396 function upgradeFromSqlFile($filename) {
 397   global $webserver_root;
 398
 399   flush();
 400   echo "<font color='green'>Processing $filename ...</font><br />\n";
 401
 402   $fullname = "$webserver_root/sql/$filename";
 403
 404   $fd = fopen($fullname, 'r');
 405   if ($fd == FALSE) {
 406     echo "ERROR.  Could not open '$fullname'.\n";
 407     flush();
 408     return;
 409   }
 410
 411   $query = "";
 412   $line = "";
 413   $skipping = false;
 414
 415   while (!feof ($fd)){
 416     $line = fgets($fd, 2048);
 417     $line = rtrim($line);
 418
 419     if (preg_match('/^\s*--/', $line)) continue;
 420     if ($line == "") continue;
 421
 422     if (preg_match('/^#IfNotTable\s+(\S+)/', $line, $matches)) {
 423       $skipping = tableExists($matches[1]);
 424       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 425     }
 426     else if (preg_match('/^#IfTable\s+(\S+)/', $line, $matches)) {
 427       $skipping = ! tableExists($matches[1]);
 428       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 429     }
 430     else if (preg_match('/^#IfColumn\s+(\S+)\s+(\S+)/', $line, $matches)) {
 431       if (tableExists($matches[1])) {
 432         $skipping = !columnExists($matches[1], $matches[2]);
 433       }
 434       else {
 435         // If no such table then the column is deemed "missing".
 436         $skipping = true;
 437       }
 438       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 439     }
 440     else if (preg_match('/^#IfMissingColumn\s+(\S+)\s+(\S+)/', $line, $matches)) {
 441       if (tableExists($matches[1])) {
 442         $skipping = columnExists($matches[1], $matches[2]);
 443       }
 444       else {
 445         // If no such table then the column is deemed not "missing".
 446         $skipping = true;
 447       }
 448       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 449     }
 450     else if (preg_match('/^#IfNotColumnType\s+(\S+)\s+(\S+)\s+(\S+)/', $line, $matches)) {
 451       if (tableExists($matches[1])) {
 452         $skipping = columnHasType($matches[1], $matches[2], $matches[3]);
 453       }
 454       else {
 455         // If no such table then the column type is deemed not "missing".
 456         $skipping = true;
 457       }
 458       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 459     }
 460     else if (preg_match('/^#IfIndex\s+(\S+)\s+(\S+)/', $line, $matches)) {
 461       if (tableExists($matches[1])) {
 462         // If no such index then skip.
 463         $skipping = !tableHasIndex($matches[1], $matches[2]);
 464       }
 465       else {
 466         // If no such table then skip.
 467         $skipping = true;
 468       }
 469       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 470     }
 471     else if (preg_match('/^#IfNotIndex\s+(\S+)\s+(\S+)/', $line, $matches)) {
 472       if (tableExists($matches[1])) {
 473         $skipping = tableHasIndex($matches[1], $matches[2]);
 474       }
 475       else {
 476         // If no such table then the index is deemed not "missing".
 477         $skipping = true;
 478       }
 479       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 480     }
 481     else if (preg_match('/^#IfNotRow\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
 482       if (tableExists($matches[1])) {
 483         $skipping = tableHasRow($matches[1], $matches[2], $matches[3]);
 484       }
 485       else {
 486         // If no such table then the row is deemed not "missing".
 487         $skipping = true;
 488       }
 489       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 490     }
 491     else if (preg_match('/^#IfNotRow2D\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
 492       if (tableExists($matches[1])) {
 493         $skipping = tableHasRow2D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5]);
 494       }
 495       else {
 496         // If no such table then the row is deemed not "missing".
 497         $skipping = true;
 498       }
 499       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 500     }
 501     else if (preg_match('/^#IfNotRow3D\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
 502       if (tableExists($matches[1])) {
 503         $skipping = tableHasRow3D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5], $matches[6], $matches[7]);
 504       }
 505       else {
 506         // If no such table then the row is deemed not "missing".
 507         $skipping = true;
 508       }
 509       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 510     }
 511     else if (preg_match('/^#IfNotRow4D\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
 512       if (tableExists($matches[1])) {
 513         $skipping = tableHasRow4D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5], $matches[6], $matches[7], $matches[8], $matches[9]);
 514       }
 515       else {
 516         // If no such table then the row is deemed not "missing".
 517         $skipping = true;
 518       }
 519       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 520     }
 521     else if (preg_match('/^#IfNotRow2Dx2\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
 522       if (tableExists($matches[1])) {
 523         // If either check exist, then will skip
 524         $firstCheck = tableHasRow2D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5]);
 525         $secondCheck = tableHasRow2D($matches[1], $matches[2], $matches[3], $matches[6], $matches[7]);
 526         if ($firstCheck || $secondCheck) {
 527           $skipping = true;
 528         }
 529         else {
 530           $skipping = false;
 531         }
 532       }
 533       else {
 534         // If no such table then the row is deemed not "missing".
 535         $skipping = true;
 536       }
 537       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 538     }
 539     else if (preg_match('/^#IfRow2D\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
 540       if (tableExists($matches[1])) {
 541         $skipping = !(tableHasRow2D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5]));
 542       }
 543       else {
 544         // If no such table then should skip.
 545         $skipping = true;
 546       }
 547       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 548     }
 549     else if (preg_match('/^#IfRow3D\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
 550         if (tableExists($matches[1])) {
 551                 $skipping = !(tableHasRow3D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5], $matches[6], $matches[7]));
 552         }
 553         else {
 554                 // If no such table then should skip.
 555                 $skipping = true;
 556         }
 557         if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 558     }
 559     else if (preg_match('/^#IfNotMigrateClickOptions/', $line)) {
 560       if (tableExists("issue_types")) {
 561         $skipping = true;
 562       }
 563       else {
 564         // Create issue_types table and import the Issue Types and clickoptions settings from codebase into the database
 565         clickOptionsMigrate();
 566         $skipping = false;
 567       }
 568       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 569     }
 570     else if (preg_match('/^#IfNotListOccupation/', $line)) {
 571       if ( (listExists("Occupation")) || (!columnExists('patient_data','occupation')) ) {
 572         $skipping = true;
 573       }
 574       else {
 575         // Create Occupation list
 576         CreateOccupationList();
 577         $skipping = false;
 578         echo "<font color='green'>Built Occupation List</font><br />\n";
 579       }
 580       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 581     }
 582     else if (preg_match('/^#IfNotListReaction/', $line)) {
 583       if ( (listExists("reaction")) || (!columnExists('lists','reaction')) ) {
 584         $skipping = true;
 585       }
 586       else {
 587         // Create Reaction list
 588         CreateReactionList();
 589         $skipping = false;
 590         echo "<font color='green'>Built Reaction List</font><br />\n";
 591       }
 592       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 593     }
 594     else if (preg_match('/^#IfNotListImmunizationManufacturer/', $line)){
 595       if ( listExists("Immunization_Manufacturer") ) {
 596         $skipping = true;
 597       }
 598       else {
 599         // Create Immunization Manufacturer list
 600         CreateImmunizationManufacturerList();
 601         $skipping = false;
 602         echo "<font color='green'>Built Immunization Manufacturer List</font><br />\n";
 603       }
 604       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 605     }
 606     // convert all *text types to use default null setting
 607     else if (preg_match('/^#IfTextNullFixNeeded/', $line)) {
 608       $items_to_convert = sqlStatement(
 609         "SELECT col.`table_name`, col.`column_name`, col.`data_type`, col.`column_comment`
 610           FROM `information_schema`.`columns` col INNER JOIN `information_schema`.`tables` tab
 611           ON tab.TABLE_CATALOG=col.TABLE_CATALOG AND tab.table_schema=col.table_schema AND tab.table_name=col.table_name
 612           WHERE col.`data_type` IN ('tinytext', 'text', 'mediumtext', 'longtext')
 613           AND col.is_nullable='NO' AND col.table_schema=database() AND tab.table_type='BASE TABLE'");
 614       if(sqlNumRows($items_to_convert) == 0) {
 615         $skipping = true;
 616       } else {
 617         $skipping = false;
 618         echo '<font color="black">Starting conversion of *TEXT types to use default NULL.</font><br />',"\n";
 619         while($item = sqlFetchArray($items_to_convert)) {
 620           if (!empty($item['column_comment'])) {
 621             $res = sqlStatement("ALTER TABLE `" . add_escape_custom($item['table_name']) . "` MODIFY `" . add_escape_custom($item['column_name']) . "` " . add_escape_custom($item['data_type'])  . " COMMENT '" . add_escape_custom($item['column_comment']) . "'");
 622           }
 623           else {
 624             $res = sqlStatement("ALTER TABLE `" . add_escape_custom($item['table_name']) . "` MODIFY `" . add_escape_custom($item['column_name']) . "` " . add_escape_custom($item['data_type']));
 625           }
 626           // If above query didn't work, then error will be outputted via the sqlStatement function.
 627           echo "<font color='green'>" . text($item['table_name']) . "." . text($item['column_name'])  . " sql column was successfully converted to " . text($item['data_type']) . " with default NULL setting.</font><br />\n";
 628         }
 629       }
 630       if($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 631     }
 632     // perform special actions if table has specific engine
 633     else if (preg_match('/^#IfTableEngine\s+(\S+)\s+(MyISAM|InnoDB)/', $line, $matches)) {
 634       $skipping = !tableHasEngine( $matches[1], $matches[2] );
 635       if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 636     }
 637     // find MyISAM tables and attempt to convert them
 638     else if (preg_match('/^#IfInnoDBMigrationNeeded/', $line)) {
 639       //tables that need to skip InnoDB migration (stay at MyISAM for now)
 640       $tables_skip_migration = array('form_eye_mag');
 641
 642       $tables_list = getTablesList( array('engine'=>'MyISAM'));
 643       if( count($tables_list)==0 ) {
 644         $skipping = true;
 645       } else {
 646         $skipping = false;
 647         echo '<font color="black">Starting migration to InnoDB, please wait.</font><br />',"\n";
 648         foreach( $tables_list as $k=>$t ) {
 649           if (in_array($t,$tables_skip_migration)) {
 650             printf( '<font color="green">Table %s was purposefully skipped and NOT migrated to InnoDB.</font><br />', $t );
 651             continue;
 652           }
 653           $res = MigrateTableEngine( $t, 'InnoDB' );
 654           if( $res === TRUE) {
 655             printf( '<font color="green">Table %s migrated to InnoDB.</font><br />', $t );
 656           } else {
 657             printf( '<font color="red">Error migrating table %s to InnoDB</font><br />', $t );
 658             error_log( sprintf( 'Error migrating table %s to InnoDB', $t ));
 659           }
 660         }
 661       }
 662       if($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
 663     }
 664     else if (preg_match('/^#EndIf/', $line)) {
 665       $skipping = false;
 666     }
 667
 668     if (preg_match('/^\s*#/', $line)) continue;
 669     if ($skipping) continue;
 670
 671     $query = $query . $line;
 672     if (substr($query, -1) == ';') {
 673       $query = rtrim($query, ';');
 674       echo "$query<br />\n";
 675       if (!sqlStatement($query)) {
 676         echo "<font color='red'>The above statement failed: " .
 677           getSqlLastError() . "<br />Upgrading will continue.<br /></font>\n";
 678       }
 679       $query = '';
 680     }
 681   }
 682   flush();
 683 } // end function
 684
 685 ?>