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