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 function convertLayoutProperties()
 343 {
 344     $res = sqlStatement("SELECT DISTINCT form_id FROM layout_options ORDER BY form_id");
 345     while ($row = sqlFetchArray($res)) {
 346         $form_id = $row['form_id'];
 347         $props = array(
 348         'title'    => 'Unknown',
 349         'mapping'  => 'Core',
 350         'notes'    => '',
 351         'activity' => '1',
 352         'option_value' => '0',
 353         );
 354         if (substr($form_id, 0, 3) == 'LBF') {
 355             $props = sqlQuery(
 356                 "SELECT title, mapping, notes, activity, option_value FROM list_options WHERE list_id = 'lbfnames' AND option_id = ?",
 357                 array($form_id)
 358             );
 359             if (empty($props)) {
 360                 continue;
 361             }
 362             if (empty($props['mapping'])) {
 363                 $props['mapping'] = 'Clinical';
 364             }
 365         } else if (substr($form_id, 0, 3) == 'LBT') {
 366             $props = sqlQuery(
 367                 "SELECT title, mapping, notes, activity, option_value FROM list_options WHERE list_id = 'transactions' AND option_id = ?",
 368                 array($form_id)
 369             );
 370             if (empty($props)) {
 371                 continue;
 372             }
 373             if (empty($props['mapping'])) {
 374                 $props['mapping'] = 'Transactions';
 375             }
 376         } else if ($form_id == 'DEM') {
 377             $props['title'] = 'Demographics';
 378         } else if ($form_id == 'HIS') {
 379             $props['title'] = 'History';
 380         } else if ($form_id == 'FACUSR') {
 381             $props['title'] = 'Facility Specific User Information';
 382         } else if ($form_id == 'CON') {
 383             $props['title'] = 'Contraception Issues';
 384         } else if ($form_id == 'GCA') {
 385             $props['title'] = 'Abortion Issues';
 386         } else if ($form_id == 'SRH') {
 387             $props['title'] = 'IPPF SRH Data';
 388         }
 389
 390         $query = "INSERT INTO layout_group_properties SET " .
 391         "grp_form_id = ?, " .
 392         "grp_group_id = '', " .
 393         "grp_title = ?, " .
 394         "grp_mapping = ?, " .
 395         "grp_activity = ?, " .
 396         "grp_repeats = ?";
 397         $sqlvars = array($form_id, $props['title'], $props['mapping'], $props['activity'], $props['option_value']);
 398         if ($props['notes']) {
 399             $jobj = json_decode($props['notes'], true);
 400             if (isset($jobj['columns'])) {
 401                 $query .= ", grp_columns = ?";
 402                 $sqlvars[] = $jobj['columns'];
 403             }
 404             if (isset($jobj['size'])) {
 405                 $query .= ", grp_size = ?";
 406                 $sqlvars[] = $jobj['size'];
 407             }
 408             if (isset($jobj['issue'])) {
 409                 $query .= ", grp_issue_type = ?";
 410                 $sqlvars[] = $jobj['issue'];
 411             }
 412             if (isset($jobj['aco'])) {
 413                 $query .= ", grp_aco_spec = ?";
 414                 $sqlvars[] = $jobj['aco'];
 415             }
 416             if (isset($jobj['services'])) {
 417                 $query .= ", grp_services = ?";
 418               // if present but empty, means all services
 419                 $sqlvars[] = $jobj['services'] ? $jobj['services'] : '*';
 420             }
 421             if (isset($jobj['products'])) {
 422                 $query .= ", grp_products = ?";
 423               // if present but empty, means all products
 424                 $sqlvars[] = $jobj['products'] ? $jobj['products'] : '*';
 425             }
 426             if (isset($jobj['diags'])) {
 427                 $query .= ", grp_diags = ?";
 428               // if present but empty, means all diags
 429                 $sqlvars[] = $jobj['diags'] ? $jobj['diags'] : '*';
 430             }
 431         }
 432         sqlStatement($query, $sqlvars);
 433
 434         $gres = sqlStatement(
 435             "SELECT DISTINCT group_name FROM layout_options WHERE form_id = ? ORDER BY group_name",
 436             array($form_id)
 437         );
 438
 439       // For each group within this layout...
 440         while ($grow = sqlFetchArray($gres)) {
 441             $group_name = $grow['group_name'];
 442             $group_id = '';
 443             $title = '';
 444             $a = explode('|', $group_name);
 445             foreach ($a as $tmp) {
 446                 $group_id .= substr($tmp, 0, 1);
 447                 $title = substr($tmp, 1);
 448             }
 449             sqlStatement(
 450                 "UPDATE layout_options SET group_id = ? WHERE form_id = ? AND group_name = ?",
 451                 array($group_id, $form_id, $group_name)
 452             );
 453             $query = "INSERT IGNORE INTO layout_group_properties SET " .
 454             "grp_form_id = ?, " .
 455             "grp_group_id = ?, " .
 456             "grp_title = '" . add_escape_custom($title) . "'";
 457           // grp_title not using $sqlvars because of a bug causing '' to become '0'.
 458             $sqlvars = array($form_id, $group_id);
 459           /****************************************************************
 460           if ($props['notes']) {
 461             if (isset($jobj['columns'])) {
 462               $query .= ", grp_columns = ?";
 463               $sqlvars[] = $jobj['columns'];
 464             }
 465             if (isset($jobj['size'])) {
 466               $query .= ", grp_size = ?";
 467               $sqlvars[] = $jobj['size'];
 468             }
 469           }
 470           ****************************************************************/
 471           // echo $query; foreach ($sqlvars as $tmp) echo " '$tmp'"; echo "<br />\n"; // debugging
 472             sqlStatement($query, $sqlvars);
 473         } // end group
 474     } // end form
 475 }
 476
 477 /**
 478 * Upgrade or patch the database with a selected upgrade/patch file.
 479 *
 480 * The following "functions" within the selected file will be processed:
 481 *
 482 * #IfNotTable
 483 *   argument: table_name
 484 *   behavior: if the table_name does not exist,  the block will be executed
 485 *
 486 * #IfTable
 487 *   argument: table_name
 488 *   behavior: if the table_name does exist, the block will be executed
 489 *
 490 * #IfColumn
 491 *   arguments: table_name colname
 492 *   behavior:  if the table and column exist,  the block will be executed
 493 *
 494 * #IfMissingColumn
 495 *   arguments: table_name colname
 496 *   behavior:  if the table exists but the column does not,  the block will be executed
 497 *
 498 * #IfNotColumnType
 499 *   arguments: table_name colname value
 500 *   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
 501 *
 502 * #IfNotRow
 503 *   arguments: table_name colname value
 504 *   behavior:  If the table table_name does not have a row where colname = value, the block will be executed.
 505 *
 506 * #IfNotRow2D
 507 *   arguments: table_name colname value colname2 value2
 508 *   behavior:  If the table table_name does not have a row where colname = value AND colname2 = value2, the block will be executed.
 509 *
 510 * #IfNotRow3D
 511 *   arguments: table_name colname value colname2 value2 colname3 value3
 512 *   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.
 513 *
 514 * #IfNotRow4D
 515 *   arguments: table_name colname value colname2 value2 colname3 value3 colname4 value4
 516 *   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.
 517 *
 518 * #IfNotRow2Dx2
 519 *   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.
 520 *   arguments: table_name colname value colname2 value2 colname3 value3
 521 *   behavior:  The block will be executed if both statements below are true:
 522 *              1) The table table_name does not have a row where colname = value AND colname2 = value2.
 523 *              2) The table table_name does not have a row where colname = value AND colname3 = value3.
 524 *
 525 * #IfRow2D
 526 *   arguments: table_name colname value colname2 value2
 527 *   behavior:  If the table table_name does have a row where colname = value AND colname2 = value2, the block will be executed.
 528 *
 529 * #IfRow3D
 530 *   arguments: table_name colname value colname2 value2 colname3 value3
 531 *   behavior:  If the table table_name does have a row where colname = value AND colname2 = value2 AND colname3 = value3, the block will be executed.
 532 *
 533 * #IfIndex
 534 *   desc:      This function is most often used for dropping of indexes/keys.
 535 *   arguments: table_name colname
 536 *   behavior:  If the table and index exist the relevant statements are executed, otherwise not.
 537 *
 538 * #IfNotIndex
 539 *   desc:      This function will allow adding of indexes/keys.
 540 *   arguments: table_name colname
 541 *   behavior:  If the index does not exist, it will be created
 542 *
 543 * #IfNotMigrateClickOptions
 544 *   Custom function for the importing of the Clickoptions settings (if exist) from the codebase into the database
 545 *
 546 * #IfNotListOccupation
 547 * Custom function for creating Occupation List
 548 *
 549 * #IfNotListReaction
 550 * Custom function for creating Reaction List
 551 *
 552 * #IfTextNullFixNeeded
 553 *   desc: convert all text fields without default null to have default null.
 554 *   arguments: none
 555 *
 556 * #IfTableEngine
 557 *   desc:      Execute SQL if the table has been created with given engine specified.
 558 *   arguments: table_name engine
 559 *   behavior:  Use when engine conversion requires more than one ALTER TABLE
 560 *
 561 * #IfInnoDBMigrationNeeded
 562 *   desc: find all MyISAM tables and convert them to InnoDB.
 563 *   arguments: none
 564 *   behavior: can take a long time.
 565 *
 566 * #EndIf
 567 *   all blocks are terminated with a #EndIf statement.
 568 *
 569 * @param  string  $filename  Sql upgrade/patch filename
 570 */
 571 function upgradeFromSqlFile($filename)
 572 {
 573     global $webserver_root;
 574
 575     flush();
 576     echo "<font color='green'>Processing $filename ...</font><br />\n";
 577
 578     $fullname = "$webserver_root/sql/$filename";
 579
 580     $fd = fopen($fullname, 'r');
 581     if ($fd == false) {
 582         echo "ERROR.  Could not open '$fullname'.\n";
 583         flush();
 584         return;
 585     }
 586
 587     $query = "";
 588     $line = "";
 589     $skipping = false;
 590
 591     while (!feof($fd)) {
 592         $line = fgets($fd, 2048);
 593         $line = rtrim($line);
 594
 595         if (preg_match('/^\s*--/', $line)) {
 596             continue;
 597         }
 598
 599         if ($line == "") {
 600             continue;
 601         }
 602
 603         if (preg_match('/^#IfNotTable\s+(\S+)/', $line, $matches)) {
 604             $skipping = tableExists($matches[1]);
 605             if ($skipping) {
 606                 echo "<font color='green'>Skipping section $line</font><br />\n";
 607             }
 608         } else if (preg_match('/^#IfTable\s+(\S+)/', $line, $matches)) {
 609             $skipping = ! tableExists($matches[1]);
 610             if ($skipping) {
 611                 echo "<font color='green'>Skipping section $line</font><br />\n";
 612             }
 613         } else if (preg_match('/^#IfColumn\s+(\S+)\s+(\S+)/', $line, $matches)) {
 614             if (tableExists($matches[1])) {
 615                 $skipping = !columnExists($matches[1], $matches[2]);
 616             } else {
 617                 // If no such table then the column is deemed "missing".
 618                 $skipping = true;
 619             }
 620
 621             if ($skipping) {
 622                 echo "<font color='green'>Skipping section $line</font><br />\n";
 623             }
 624         } else if (preg_match('/^#IfMissingColumn\s+(\S+)\s+(\S+)/', $line, $matches)) {
 625             if (tableExists($matches[1])) {
 626                 $skipping = columnExists($matches[1], $matches[2]);
 627             } else {
 628                 // If no such table then the column is deemed not "missing".
 629                 $skipping = true;
 630             }
 631
 632             if ($skipping) {
 633                 echo "<font color='green'>Skipping section $line</font><br />\n";
 634             }
 635         } else if (preg_match('/^#IfNotColumnType\s+(\S+)\s+(\S+)\s+(\S+)/', $line, $matches)) {
 636             if (tableExists($matches[1])) {
 637                 $skipping = columnHasType($matches[1], $matches[2], $matches[3]);
 638             } else {
 639                 // If no such table then the column type is deemed not "missing".
 640                 $skipping = true;
 641             }
 642
 643             if ($skipping) {
 644                 echo "<font color='green'>Skipping section $line</font><br />\n";
 645             }
 646         } else if (preg_match('/^#IfIndex\s+(\S+)\s+(\S+)/', $line, $matches)) {
 647             if (tableExists($matches[1])) {
 648                 // If no such index then skip.
 649                 $skipping = !tableHasIndex($matches[1], $matches[2]);
 650             } else {
 651                 // If no such table then skip.
 652                 $skipping = true;
 653             }
 654
 655             if ($skipping) {
 656                 echo "<font color='green'>Skipping section $line</font><br />\n";
 657             }
 658         } else if (preg_match('/^#IfNotIndex\s+(\S+)\s+(\S+)/', $line, $matches)) {
 659             if (tableExists($matches[1])) {
 660                 $skipping = tableHasIndex($matches[1], $matches[2]);
 661             } else {
 662                 // If no such table then the index is deemed not "missing".
 663                 $skipping = true;
 664             }
 665
 666             if ($skipping) {
 667                 echo "<font color='green'>Skipping section $line</font><br />\n";
 668             }
 669         } else if (preg_match('/^#IfNotRow\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
 670             if (tableExists($matches[1])) {
 671                 $skipping = tableHasRow($matches[1], $matches[2], $matches[3]);
 672             } else {
 673                 // If no such table then the row is deemed not "missing".
 674                 $skipping = true;
 675             }
 676
 677             if ($skipping) {
 678                 echo "<font color='green'>Skipping section $line</font><br />\n";
 679             }
 680         } else if (preg_match('/^#IfNotRow2D\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
 681             if (tableExists($matches[1])) {
 682                 $skipping = tableHasRow2D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5]);
 683             } else {
 684                 // If no such table then the row is deemed not "missing".
 685                 $skipping = true;
 686             }
 687
 688             if ($skipping) {
 689                 echo "<font color='green'>Skipping section $line</font><br />\n";
 690             }
 691         } else if (preg_match('/^#IfNotRow3D\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
 692             if (tableExists($matches[1])) {
 693                 $skipping = tableHasRow3D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5], $matches[6], $matches[7]);
 694             } else {
 695                 // If no such table then the row is deemed not "missing".
 696                 $skipping = true;
 697             }
 698
 699             if ($skipping) {
 700                 echo "<font color='green'>Skipping section $line</font><br />\n";
 701             }
 702         } else if (preg_match('/^#IfNotRow4D\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
 703             if (tableExists($matches[1])) {
 704                 $skipping = tableHasRow4D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5], $matches[6], $matches[7], $matches[8], $matches[9]);
 705             } else {
 706                 // If no such table then the row is deemed not "missing".
 707                 $skipping = true;
 708             }
 709
 710             if ($skipping) {
 711                 echo "<font color='green'>Skipping section $line</font><br />\n";
 712             }
 713         } else if (preg_match('/^#IfNotRow2Dx2\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
 714             if (tableExists($matches[1])) {
 715             // If either check exist, then will skip
 716                 $firstCheck = tableHasRow2D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5]);
 717                 $secondCheck = tableHasRow2D($matches[1], $matches[2], $matches[3], $matches[6], $matches[7]);
 718                 if ($firstCheck || $secondCheck) {
 719                     $skipping = true;
 720                 } else {
 721                       $skipping = false;
 722                 }
 723             } else {
 724                 // If no such table then the row is deemed not "missing".
 725                 $skipping = true;
 726             }
 727
 728             if ($skipping) {
 729                 echo "<font color='green'>Skipping section $line</font><br />\n";
 730             }
 731         } else if (preg_match('/^#IfRow2D\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
 732             if (tableExists($matches[1])) {
 733                 $skipping = !(tableHasRow2D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5]));
 734             } else {
 735                 // If no such table then should skip.
 736                 $skipping = true;
 737             }
 738
 739             if ($skipping) {
 740                 echo "<font color='green'>Skipping section $line</font><br />\n";
 741             }
 742         } else if (preg_match('/^#IfRow3D\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
 743             if (tableExists($matches[1])) {
 744                 $skipping = !(tableHasRow3D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5], $matches[6], $matches[7]));
 745             } else {
 746                 // If no such table then should skip.
 747                 $skipping = true;
 748             }
 749
 750             if ($skipping) {
 751                 echo "<font color='green'>Skipping section $line</font><br />\n";
 752             }
 753         } else if (preg_match('/^#IfNotMigrateClickOptions/', $line)) {
 754             if (tableExists("issue_types")) {
 755                 $skipping = true;
 756             } else {
 757                 // Create issue_types table and import the Issue Types and clickoptions settings from codebase into the database
 758                 clickOptionsMigrate();
 759                 $skipping = false;
 760             }
 761
 762             if ($skipping) {
 763                 echo "<font color='green'>Skipping section $line</font><br />\n";
 764             }
 765         } else if (preg_match('/^#IfNotListOccupation/', $line)) {
 766             if ((listExists("Occupation")) || (!columnExists('patient_data', 'occupation'))) {
 767                 $skipping = true;
 768             } else {
 769                 // Create Occupation list
 770                 CreateOccupationList();
 771                 $skipping = false;
 772                 echo "<font color='green'>Built Occupation List</font><br />\n";
 773             }
 774
 775             if ($skipping) {
 776                 echo "<font color='green'>Skipping section $line</font><br />\n";
 777             }
 778         } else if (preg_match('/^#IfNotListReaction/', $line)) {
 779             if ((listExists("reaction")) || (!columnExists('lists', 'reaction'))) {
 780                 $skipping = true;
 781             } else {
 782                 // Create Reaction list
 783                 CreateReactionList();
 784                 $skipping = false;
 785                 echo "<font color='green'>Built Reaction List</font><br />\n";
 786             }
 787
 788             if ($skipping) {
 789                 echo "<font color='green'>Skipping section $line</font><br />\n";
 790             }
 791         } else if (preg_match('/^#IfNotListImmunizationManufacturer/', $line)) {
 792             if (listExists("Immunization_Manufacturer")) {
 793                 $skipping = true;
 794             } else {
 795                 // Create Immunization Manufacturer list
 796                 CreateImmunizationManufacturerList();
 797                 $skipping = false;
 798                 echo "<font color='green'>Built Immunization Manufacturer List</font><br />\n";
 799             }
 800
 801             if ($skipping) {
 802                 echo "<font color='green'>Skipping section $line</font><br />\n";
 803             }
 804         } // convert all *text types to use default null setting
 805         else if (preg_match('/^#IfTextNullFixNeeded/', $line)) {
 806             $items_to_convert = sqlStatement(
 807                 "SELECT col.`table_name`, col.`column_name`, col.`data_type`, col.`column_comment`
 808           FROM `information_schema`.`columns` col INNER JOIN `information_schema`.`tables` tab
 809           ON tab.TABLE_CATALOG=col.TABLE_CATALOG AND tab.table_schema=col.table_schema AND tab.table_name=col.table_name
 810           WHERE col.`data_type` IN ('tinytext', 'text', 'mediumtext', 'longtext')
 811           AND col.is_nullable='NO' AND col.table_schema=database() AND tab.table_type='BASE TABLE'"
 812             );
 813             if (sqlNumRows($items_to_convert) == 0) {
 814                 $skipping = true;
 815             } else {
 816                 $skipping = false;
 817                 echo '<font color="black">Starting conversion of *TEXT types to use default NULL.</font><br />',"\n";
 818                 while ($item = sqlFetchArray($items_to_convert)) {
 819                     if (!empty($item['column_comment'])) {
 820                         $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']) . "'");
 821                     } else {
 822                         $res = sqlStatement("ALTER TABLE `" . add_escape_custom($item['table_name']) . "` MODIFY `" . add_escape_custom($item['column_name']) . "` " . add_escape_custom($item['data_type']));
 823                     }
 824
 825                     // If above query didn't work, then error will be outputted via the sqlStatement function.
 826                     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";
 827                 }
 828             }
 829
 830             if ($skipping) {
 831                 echo "<font color='green'>Skipping section $line</font><br />\n";
 832             }
 833         } // perform special actions if table has specific engine
 834         else if (preg_match('/^#IfTableEngine\s+(\S+)\s+(MyISAM|InnoDB)/', $line, $matches)) {
 835             $skipping = !tableHasEngine($matches[1], $matches[2]);
 836             if ($skipping) {
 837                 echo "<font color='green'>Skipping section $line</font><br />\n";
 838             }
 839         } // find MyISAM tables and attempt to convert them
 840         else if (preg_match('/^#IfInnoDBMigrationNeeded/', $line)) {
 841             //tables that need to skip InnoDB migration (stay at MyISAM for now)
 842             $tables_skip_migration = array('form_eye_mag');
 843
 844             $tables_list = getTablesList(array('engine'=>'MyISAM'));
 845             if (count($tables_list)==0) {
 846                 $skipping = true;
 847             } else {
 848                 $skipping = false;
 849                 echo '<font color="black">Starting migration to InnoDB, please wait.</font><br />',"\n";
 850                 foreach ($tables_list as $k => $t) {
 851                     if (in_array($t, $tables_skip_migration)) {
 852                         printf('<font color="green">Table %s was purposefully skipped and NOT migrated to InnoDB.</font><br />', $t);
 853                         continue;
 854                     }
 855
 856                     $res = MigrateTableEngine($t, 'InnoDB');
 857                     if ($res === true) {
 858                         printf('<font color="green">Table %s migrated to InnoDB.</font><br />', $t);
 859                     } else {
 860                         printf('<font color="red">Error migrating table %s to InnoDB</font><br />', $t);
 861                         error_log(sprintf('Error migrating table %s to InnoDB', $t));
 862                     }
 863                 }
 864             }
 865
 866             if ($skipping) {
 867                 echo "<font color='green'>Skipping section $line</font><br />\n";
 868             }
 869         } else if (preg_match('/^#ConvertLayoutProperties/', $line)) {
 870             if ($skipping) {
 871                 echo "<font color='green'>Skipping section $line</font><br />\n";
 872             } else {
 873                 echo "Converting layout properties ...<br />\n";
 874                 convertLayoutProperties();
 875             }
 876         } else if (preg_match('/^#EndIf/', $line)) {
 877             $skipping = false;
 878         }
 879
 880         if (preg_match('/^\s*#/', $line)) {
 881             continue;
 882         }
 883
 884         if ($skipping) {
 885             continue;
 886         }
 887
 888         $query = $query . $line;
 889         if (substr($query, -1) == ';') {
 890             $query = rtrim($query, ';');
 891             echo "$query<br />\n";
 892             if (!sqlStatement($query)) {
 893                 echo "<font color='red'>The above statement failed: " .
 894                 getSqlLastError() . "<br />Upgrading will continue.<br /></font>\n";
 895             }
 896
 897             $query = '';
 898         }
 899     }
 900
 901     flush();
 902 } // end function