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