library/sql_upgrade_fx.php

   1 <?php
   2
   3 /**
   4  * Upgrading and patching functions of database.
   5  *
   6  * Functions to allow safe database modifications
   7  * during upgrading and patches.
   8  *
   9  * Copyright (C) 2008-2012 Rod Roark <rod@sunsetsystems.com>
  10  *
  11  * LICENSE: This program is free software; you can redistribute it and/or
  12  * modify it under the terms of the GNU General Public License
  13  * as published by the Free Software Foundation; either version 2
  14  * of the License, or (at your option) any later version.
  15  * This program is distributed in the hope that it will be useful,
  16  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  17  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  18  * GNU General Public License for more details.
  19  * You should have received a copy of the GNU General Public License
  20  * along with this program.  If not, see <http://opensource.org/licenses/gpl-license.php>.
  21  *
  22  * @package   OpenEMR
  23  * @author    Rod Roark <rod@sunsetsystems.com>
  24  * @author    Brady Miller <brady.g.miller@gmail.com>
  25  * @author    Teny <teny@zhservices.com>
  26  * @author    Jerry Padgett <sjpadgett@gmail.com>
  27  * @link      https://www.open-emr.org
  28  */
  29
  30 use OpenEMR\Common\Uuid\UuidRegistry;
  31
  32 /**
  33  * Return the name of the OpenEMR database.
  34  *
  35  * @return string
  36  */
  37 function databaseName()
  38 {
  39     $row = sqlQuery("SELECT database() AS dbname");
  40     return $row['dbname'];
  41 }
  42
  43 /**
  44  * Check if a Sql table exists.
  45  *
  46  * @param string $tblname Sql Table Name
  47  * @return boolean           returns true if the sql table exists
  48  */
  49 function tableExists($tblname)
  50 {
  51     $row = sqlQuery("SHOW TABLES LIKE '$tblname'");
  52     if (empty($row)) {
  53         return false;
  54     }
  55
  56     return true;
  57 }
  58
  59 /**
  60  * Check if a Sql column exists in a selected table.
  61  *
  62  * @param string $tblname Sql Table Name
  63  * @param string $colname Sql Column Name
  64  * @return boolean           returns true if the sql column exists
  65  */
  66 function columnExists($tblname, $colname)
  67 {
  68     $row = sqlQuery("SHOW COLUMNS FROM $tblname LIKE '$colname'");
  69     if (empty($row)) {
  70         return false;
  71     }
  72
  73     return true;
  74 }
  75
  76 /**
  77  * Check if a Sql column has a certain type.
  78  *
  79  * @param string $tblname Sql Table Name
  80  * @param string $colname Sql Column Name
  81  * @param string $coltype Sql Column Type
  82  * @return boolean           returns true if the sql column is of the specified type
  83  */
  84 function columnHasType($tblname, $colname, $coltype)
  85 {
  86     $row = sqlQuery("SHOW COLUMNS FROM $tblname LIKE '$colname'");
  87     if (empty($row)) {
  88         return true;
  89     }
  90
  91     return (strcasecmp($row['Type'], $coltype) == 0);
  92 }
  93
  94 /**
  95  * Check if a Sql column has a certain type and a certain default value.
  96  *
  97  * @param string $tblname    Sql Table Name
  98  * @param string $colname    Sql Column Name
  99  * @param string $coltype    Sql Column Type
 100  * @param string $coldefault Sql Column Default
 101  * @return boolean              returns true if the sql column is of the specified type and default
 102  */
 103 function columnHasTypeDefault($tblname, $colname, $coltype, $coldefault)
 104 {
 105     $row = sqlQuery("SHOW COLUMNS FROM $tblname WHERE `Field` = ?", [$colname]);
 106     if (empty($row)) {
 107         return true;
 108     }
 109
 110     // Check if the type matches
 111     if (strcasecmp($row['Type'], $coltype) != 0) {
 112         return false;
 113     }
 114
 115     // Now for the more difficult check for if the default matches
 116     if ($coldefault == "NULL") {
 117         // Special case when checking if default is NULL
 118         $row = sqlQuery("SHOW COLUMNS FROM $tblname WHERE `Field` = ? AND `Default` IS NULL", [$colname]);
 119         return (!empty($row));
 120     } elseif ($coldefault == "") {
 121         // Special case when checking if default is ""(blank)
 122         $row = sqlQuery("SHOW COLUMNS FROM $tblname WHERE `Field` = ? AND `Default` IS NOT NULL AND `Default` = ''", [$colname]);
 123         return (!empty($row));
 124     } else {
 125         // Standard case when checking if default is neither NULL or ""(blank)
 126         return (strcasecmp($row['Default'], $coldefault) == 0);
 127     }
 128 }
 129
 130 /**
 131  * Check if a Sql row exists. (with one value)
 132  *
 133  * @param string $tblname Sql Table Name
 134  * @param string $colname Sql Column Name
 135  * @param string $value   Sql value
 136  * @return boolean           returns true if the sql row does exist
 137  */
 138 function tableHasRow($tblname, $colname, $value)
 139 {
 140     $row = sqlQuery("SELECT COUNT(*) AS count FROM $tblname WHERE " .
 141         "$colname LIKE '$value'");
 142     return $row['count'] ? true : false;
 143 }
 144
 145 /**
 146  * Check if a Sql row exists. (with two values)
 147  *
 148  * @param string $tblname  Sql Table Name
 149  * @param string $colname  Sql Column Name 1
 150  * @param string $value    Sql value 1
 151  * @param string $colname2 Sql Column Name 2
 152  * @param string $value2   Sql value 2
 153  * @return boolean            returns true if the sql row does exist
 154  */
 155 function tableHasRow2D($tblname, $colname, $value, $colname2, $value2)
 156 {
 157     $row = sqlQuery("SELECT COUNT(*) AS count FROM $tblname WHERE " .
 158         "$colname LIKE '$value' AND $colname2 LIKE '$value2'");
 159     return $row['count'] ? true : false;
 160 }
 161
 162 /**
 163  * Check if a Sql row exists. (with three values)
 164  *
 165  * @param string $tblname  Sql Table Name
 166  * @param string $colname  Sql Column Name 1
 167  * @param string $value    Sql value 1
 168  * @param string $colname2 Sql Column Name 2
 169  * @param string $value2   Sql value 2
 170  * @param string $colname3 Sql Column Name 3
 171  * @param string $value3   Sql value 3
 172  * @return boolean            returns true if the sql row does exist
 173  */
 174 function tableHasRow3D($tblname, $colname, $value, $colname2, $value2, $colname3, $value3)
 175 {
 176     $row = sqlQuery("SELECT COUNT(*) AS count FROM $tblname WHERE " .
 177         "$colname LIKE '$value' AND $colname2 LIKE '$value2' AND $colname3 LIKE '$value3'");
 178     return $row['count'] ? true : false;
 179 }
 180
 181 /**
 182  * Check if a Sql row exists. (with four values)
 183  *
 184  * @param string $tblname  Sql Table Name
 185  * @param string $colname  Sql Column Name 1
 186  * @param string $value    Sql value 1
 187  * @param string $colname2 Sql Column Name 2
 188  * @param string $value2   Sql value 2
 189  * @param string $colname3 Sql Column Name 3
 190  * @param string $value3   Sql value 3
 191  * @param string $colname4 Sql Column Name 4
 192  * @param string $value4   Sql value 4
 193  * @return boolean            returns true if the sql row does exist
 194  */
 195 function tableHasRow4D($tblname, $colname, $value, $colname2, $value2, $colname3, $value3, $colname4, $value4)
 196 {
 197     $row = sqlQuery("SELECT COUNT(*) AS count FROM $tblname WHERE " .
 198         "$colname LIKE '$value' AND $colname2 LIKE '$value2' AND $colname3 LIKE '$value3' AND $colname4 LIKE '$value4'");
 199     return $row['count'] ? true : false;
 200 }
 201
 202 /**
 203  * Check if a Sql table has a certain index/key.
 204  *
 205  * @param string $tblname Sql Table Name
 206  * @param string $colname Sql Index/Key
 207  * @return boolean           returns true if the sql tables has the specified index/key
 208  */
 209 function tableHasIndex($tblname, $colname)
 210 {
 211     $row = sqlQuery("SHOW INDEX FROM `$tblname` WHERE `Key_name` = '$colname'");
 212     return (empty($row)) ? false : true;
 213 }
 214
 215
 216 /**
 217  * Check if a table has a certain engine
 218  *
 219  * @param string $tblname database table Name
 220  * @param string $engine  engine name ( myisam, memory, innodb )...
 221  * @return boolean true if the table has been created using specified engine
 222  */
 223 function tableHasEngine($tblname, $engine)
 224 {
 225     $row = sqlQuery('SELECT 1 FROM information_schema.tables WHERE table_name=? AND engine=? AND table_type="BASE TABLE"', array($tblname, $engine));
 226     return (empty($row)) ? false : true;
 227 }
 228
 229
 230 /**
 231  * Check if a list exists.
 232  *
 233  * @param string $option_id Sql List Option ID
 234  * @return boolean           returns true if the list exists
 235  */
 236 function listExists($option_id)
 237 {
 238     $row = sqlQuery("SELECT * FROM list_options WHERE list_id = 'lists' AND option_id = ?", array($option_id));
 239     if (empty($row)) {
 240         return false;
 241     }
 242
 243     return true;
 244 }
 245
 246 /**
 247  * Function to migrate the Clickoptions settings (if exist) from the codebase into the database.
 248  *  Note this function is only run once in the sql upgrade script (from 4.1.1 to 4.1.2) if the
 249  *  issue_types sql table does not exist.
 250  */
 251 function clickOptionsMigrate()
 252 {
 253     // If the clickoptions.txt file exist, then import it.
 254     if (file_exists(dirname(__FILE__) . "/../sites/" . $_SESSION['site_id'] . "/clickoptions.txt")) {
 255         $file_handle = fopen(dirname(__FILE__) . "/../sites/" . $_SESSION['site_id'] . "/clickoptions.txt", "rb");
 256         $seq = 10;
 257         $prev = '';
 258         echo "Importing clickoption setting<br />";
 259         while (!feof($file_handle)) {
 260             $line_of_text = fgets($file_handle);
 261             if (preg_match('/^#/', $line_of_text)) {
 262                 continue;
 263             }
 264
 265             if ($line_of_text == "") {
 266                 continue;
 267             }
 268
 269             $parts = explode('::', $line_of_text);
 270             $parts[0] = trim(str_replace("\r\n", "", $parts[0]));
 271             $parts[1] = trim(str_replace("\r\n", "", $parts[1]));
 272             if ($parts[0] != $prev) {
 273                 $sql1 = "INSERT INTO list_options (`list_id`,`option_id`,`title`) VALUES (?,?,?)";
 274                 SqlStatement($sql1, array('lists', $parts[0] . '_issue_list', ucwords(str_replace("_", " ", $parts[0])) . ' Issue List'));
 275                 $seq = 10;
 276             }
 277
 278             $sql2 = "INSERT INTO list_options (`list_id`,`option_id`,`title`,`seq`) VALUES (?,?,?,?)";
 279             SqlStatement($sql2, array($parts[0] . '_issue_list', $parts[1], $parts[1], $seq));
 280             $seq = $seq + 10;
 281             $prev = $parts[0];
 282         }
 283
 284         fclose($file_handle);
 285     }
 286 }
 287
 288 /**
 289  *  Function to create list Occupation.
 290  *  Note this function is only run once in the sql upgrade script  if the list Occupation does not exist
 291  */
 292 function CreateOccupationList()
 293 {
 294     $res = sqlStatement("SELECT DISTINCT occupation FROM patient_data WHERE occupation <> ''");
 295     while ($row = sqlFetchArray($res)) {
 296         $records[] = $row['occupation'];
 297     }
 298
 299     sqlStatement("INSERT INTO list_options (list_id, option_id, title) VALUES('lists', 'Occupation', 'Occupation')");
 300     if (count($records) > 0) {
 301         $seq = 0;
 302         foreach ($records as $key => $value) {
 303             sqlStatement("INSERT INTO list_options ( list_id, option_id, title, seq) VALUES ('Occupation', ?, ?, ?)", array($value, $value, ($seq + 10)));
 304             $seq = $seq + 10;
 305         }
 306     }
 307 }
 308
 309 /**
 310  *  Function to create list reaction.
 311  *  Note this function is only run once in the sql upgrade script  if the list reaction does not exist
 312  */
 313 function CreateReactionList()
 314 {
 315     $res = sqlStatement("SELECT DISTINCT reaction FROM lists WHERE reaction <> ''");
 316     while ($row = sqlFetchArray($res)) {
 317         $records[] = $row['reaction'];
 318     }
 319
 320     sqlStatement("INSERT INTO list_options (list_id, option_id, title) VALUES('lists', 'reaction', 'Reaction')");
 321     if (count($records) > 0) {
 322         $seq = 0;
 323         foreach ($records as $key => $value) {
 324             sqlStatement("INSERT INTO list_options ( list_id, option_id, title, seq) VALUES ('reaction', ?, ?, ?)", array($value, $value, ($seq + 10)));
 325             $seq = $seq + 10;
 326         }
 327     }
 328 }
 329
 330 /*
 331 * Function to add existing values in the immunization table to the new immunization manufacturer list
 332 * This function will be executed always, but only missing values will ne inserted to the list
 333 */
 334 function CreateImmunizationManufacturerList()
 335 {
 336     $res = sqlStatement("SELECT DISTINCT manufacturer FROM immunizations WHERE manufacturer <> ''");
 337     while ($row = sqlFetchArray($res)) {
 338         $records[] = $row['manufacturer'];
 339     }
 340
 341     sqlStatement("INSERT INTO list_options (list_id, option_id, title) VALUES ('lists','Immunization_Manufacturer','Immunization Manufacturer')");
 342     if (count($records) > 0) {
 343         $seq = 0;
 344         foreach ($records as $key => $value) {
 345             sqlStatement("INSERT INTO list_options ( list_id, option_id, title, seq) VALUES ('Immunization_Manufacturer', ?, ?, ?)", array($value, $value, ($seq + 10)));
 346             $seq = $seq + 10;
 347         }
 348     }
 349 }
 350
 351 /*
 352  *  This function is to populate the weno drug table if the feature is enabled before upgrade.
 353  */
 354 function ImportDrugInformation()
 355 {
 356     if ($GLOBALS['weno_rx_enable']) {
 357         $drugs = file_get_contents('contrib/weno/erx_weno_drugs.sql');
 358         $drugsArray = preg_split('/;\R/', $drugs);
 359
 360         // Settings to drastically speed up import with InnoDB
 361         sqlStatementNoLog("SET autocommit=0");
 362         sqlStatementNoLog("START TRANSACTION");
 363
 364         foreach ($drugsArray as $drug) {
 365             if (empty($drug)) {
 366                 continue;
 367             }
 368             sqlStatementNoLog($drug);
 369         }
 370
 371         // Settings to drastically speed up import with InnoDB
 372         sqlStatementNoLog("COMMIT");
 373         sqlStatementNoLog("SET autocommit=1");
 374     }
 375 }
 376
 377 /**
 378  * Request to information_schema
 379  *
 380  * @param array $arg possible arguments: engine, table_name
 381  * @return SQLStatement
 382  */
 383 function getTablesList($arg = array())
 384 {
 385     $binds = array(databaseName());
 386     $sql = 'SELECT TABLE_NAME AS table_name FROM information_schema.tables WHERE table_schema = ? AND table_type = "BASE TABLE"';
 387
 388     if (!empty($arg['engine'])) {
 389         $binds[] = $arg['engine'];
 390         $sql .= ' AND engine=?';
 391     }
 392
 393     if (!empty($arg['table_name'])) {
 394         $binds[] = $arg['table_name'];
 395         $sql .= ' AND table_name=?';
 396     }
 397
 398     $res = sqlStatement($sql, $binds);
 399
 400     $records = array();
 401     while ($row = sqlFetchArray($res)) {
 402         $records[$row['table_name']] = $row['table_name'];
 403     }
 404
 405     return $records;
 406 }
 407
 408
 409 /**
 410  * Convert table engine.
 411  * @param string $table
 412  * @param string $engine
 413  * ADODB will fail if there was an error during conversion
 414  */
 415 function MigrateTableEngine($table, $engine)
 416 {
 417     $r = sqlStatement('ALTER TABLE `' . $table . '` ENGINE=?', $engine);
 418     return true;
 419 }
 420
 421 function convertLayoutProperties()
 422 {
 423     $res = sqlStatement("SELECT DISTINCT form_id FROM layout_options ORDER BY form_id");
 424     while ($row = sqlFetchArray($res)) {
 425         $form_id = $row['form_id'];
 426         $props = array(
 427             'title' => 'Unknown',
 428             'mapping' => 'Core',
 429             'notes' => '',
 430             'activity' => '1',
 431             'option_value' => '0',
 432         );
 433         if (substr($form_id, 0, 3) == 'LBF') {
 434             $props = sqlQuery(
 435                 "SELECT title, mapping, notes, activity, option_value FROM list_options WHERE list_id = 'lbfnames' AND option_id = ?",
 436                 array($form_id)
 437             );
 438             if (empty($props)) {
 439                 continue;
 440             }
 441             if (empty($props['mapping'])) {
 442                 $props['mapping'] = 'Clinical';
 443             }
 444         } elseif (substr($form_id, 0, 3) == 'LBT') {
 445             $props = sqlQuery(
 446                 "SELECT title, mapping, notes, activity, option_value FROM list_options WHERE list_id = 'transactions' AND option_id = ?",
 447                 array($form_id)
 448             );
 449             if (empty($props)) {
 450                 continue;
 451             }
 452             if (empty($props['mapping'])) {
 453                 $props['mapping'] = 'Transactions';
 454             }
 455         } elseif ($form_id == 'DEM') {
 456             $props['title'] = 'Demographics';
 457         } elseif ($form_id == 'HIS') {
 458             $props['title'] = 'History';
 459         } elseif ($form_id == 'FACUSR') {
 460             $props['title'] = 'Facility Specific User Information';
 461         } elseif ($form_id == 'CON') {
 462             $props['title'] = 'Contraception Issues';
 463         } elseif ($form_id == 'GCA') {
 464             $props['title'] = 'Abortion Issues';
 465         } elseif ($form_id == 'SRH') {
 466             $props['title'] = 'IPPF SRH Data';
 467         }
 468
 469         $query = "INSERT INTO layout_group_properties SET " .
 470             "grp_form_id = ?, " .
 471             "grp_group_id = '', " .
 472             "grp_title = ?, " .
 473             "grp_mapping = ?, " .
 474             "grp_activity = ?, " .
 475             "grp_repeats = ?";
 476         $sqlvars = array($form_id, $props['title'], $props['mapping'], $props['activity'], $props['option_value']);
 477         if ($props['notes']) {
 478             $jobj = json_decode($props['notes'], true);
 479             if (isset($jobj['columns'])) {
 480                 $query .= ", grp_columns = ?";
 481                 $sqlvars[] = $jobj['columns'];
 482             }
 483             if (isset($jobj['size'])) {
 484                 $query .= ", grp_size = ?";
 485                 $sqlvars[] = $jobj['size'];
 486             }
 487             if (isset($jobj['issue'])) {
 488                 $query .= ", grp_issue_type = ?";
 489                 $sqlvars[] = $jobj['issue'];
 490             }
 491             if (isset($jobj['aco'])) {
 492                 $query .= ", grp_aco_spec = ?";
 493                 $sqlvars[] = $jobj['aco'];
 494             }
 495             if (isset($jobj['services'])) {
 496                 $query .= ", grp_services = ?";
 497                 // if present but empty, means all services
 498                 $sqlvars[] = $jobj['services'] ? $jobj['services'] : '*';
 499             }
 500             if (isset($jobj['products'])) {
 501                 $query .= ", grp_products = ?";
 502                 // if present but empty, means all products
 503                 $sqlvars[] = $jobj['products'] ? $jobj['products'] : '*';
 504             }
 505             if (isset($jobj['diags'])) {
 506                 $query .= ", grp_diags = ?";
 507                 // if present but empty, means all diags
 508                 $sqlvars[] = $jobj['diags'] ? $jobj['diags'] : '*';
 509             }
 510         }
 511         sqlStatement($query, $sqlvars);
 512
 513         $gres = sqlStatement(
 514             "SELECT DISTINCT group_name FROM layout_options WHERE form_id = ? ORDER BY group_name",
 515             array($form_id)
 516         );
 517
 518         // For each group within this layout...
 519         while ($grow = sqlFetchArray($gres)) {
 520             $group_name = $grow['group_name'];
 521             $group_id = '';
 522             $title = '';
 523             $a = explode('|', $group_name);
 524             foreach ($a as $tmp) {
 525                 $group_id .= substr($tmp, 0, 1);
 526                 $title = substr($tmp, 1);
 527             }
 528             sqlStatement(
 529                 "UPDATE layout_options SET group_id = ? WHERE form_id = ? AND group_name = ?",
 530                 array($group_id, $form_id, $group_name)
 531             );
 532             $query = "INSERT IGNORE INTO layout_group_properties SET " .
 533                 "grp_form_id = ?, " .
 534                 "grp_group_id = ?, " .
 535                 "grp_title = '" . add_escape_custom($title) . "'";
 536             // grp_title not using $sqlvars because of a bug causing '' to become '0'.
 537             $sqlvars = array($form_id, $group_id);
 538             /****************************************************************
 539           if ($props['notes']) {
 540             if (isset($jobj['columns'])) {
 541               $query .= ", grp_columns = ?";
 542               $sqlvars[] = $jobj['columns'];
 543             }
 544             if (isset($jobj['size'])) {
 545               $query .= ", grp_size = ?";
 546               $sqlvars[] = $jobj['size'];
 547             }
 548           }
 549              ****************************************************************/
 550             // echo $query; foreach ($sqlvars as $tmp) echo " '$tmp'"; echo "<br />\n"; // debugging
 551             sqlStatement($query, $sqlvars);
 552         } // end group
 553     } // end form
 554 }
 555
 556 function flush_echo($string = '')
 557 {
 558     if ($string) {
 559         echo $string;
 560     }
 561     // now flush to force browser to pay attention.
 562     if (empty($GLOBALS['force_simple_sql_upgrade'])) {
 563         // this is skipped when running sql upgrade from command line
 564         echo str_pad('', 4096) . "\n";
 565     }
 566     ob_flush();
 567     flush();
 568 }
 569
 570 /**
 571  * Upgrade or patch the database with a selected upgrade/patch file.
 572  *
 573  * The following "functions" within the selected file will be processed:
 574  *
 575  * #IfNotTable
 576  *   argument: table_name
 577  *   behavior: if the table_name does not exist,  the block will be executed
 578  *
 579  * #IfTable
 580  *   argument: table_name
 581  *   behavior: if the table_name does exist, the block will be executed
 582  *
 583  * #IfColumn
 584  *   arguments: table_name colname
 585  *   behavior:  if the table and column exist,  the block will be executed
 586  *
 587  * #IfMissingColumn
 588  *   arguments: table_name colname
 589  *   behavior:  if the table exists but the column does not,  the block will be executed
 590  *
 591  * #IfNotColumnType
 592  *   arguments: table_name colname value
 593  *   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
 594  *
 595  * #IfNotColumnTypeDefault
 596  *   arguments: table_name colname value value2
 597  *   behavior:  If the table table_name does not have a column colname with a data type equal to value and a default equal to value2, then the block will be executed
 598  *
 599  * #IfNotRow
 600  *   arguments: table_name colname value
 601  *   behavior:  If the table table_name does not have a row where colname = value, the block will be executed.
 602  *
 603  * #IfNotRow2D
 604  *   arguments: table_name colname value colname2 value2
 605  *   behavior:  If the table table_name does not have a row where colname = value AND colname2 = value2, the block will be executed.
 606  *
 607  * #IfNotRow3D
 608  *   arguments: table_name colname value colname2 value2 colname3 value3
 609  *   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.
 610  *
 611  * #IfNotRow4D
 612  *   arguments: table_name colname value colname2 value2 colname3 value3 colname4 value4
 613  *   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.
 614  *
 615  * #IfNotRow2Dx2
 616  *   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.
 617  *   arguments: table_name colname value colname2 value2 colname3 value3
 618  *   behavior:  The block will be executed if both statements below are true:
 619  *              1) The table table_name does not have a row where colname = value AND colname2 = value2.
 620  *              2) The table table_name does not have a row where colname = value AND colname3 = value3.
 621  *
 622  * #IfRow
 623  *   arguments: table_name colname value
 624  *   behavior:  If the table table_name does have a row where colname = value, the block will be executed.
 625  *
 626  * #IfRow2D
 627  *   arguments: table_name colname value colname2 value2
 628  *   behavior:  If the table table_name does have a row where colname = value AND colname2 = value2, the block will be executed.
 629  *
 630  * #IfRow3D
 631  *   arguments: table_name colname value colname2 value2 colname3 value3
 632  *   behavior:  If the table table_name does have a row where colname = value AND colname2 = value2 AND colname3 = value3, the block will be executed.
 633  *
 634  * #IfIndex
 635  *   desc:      This function is most often used for dropping of indexes/keys.
 636  *   arguments: table_name colname
 637  *   behavior:  If the table and index exist the relevant statements are executed, otherwise not.
 638  *
 639  * #IfNotIndex
 640  *   desc:      This function will allow adding of indexes/keys.
 641  *   arguments: table_name colname
 642  *   behavior:  If the index does not exist, it will be created
 643  *
 644  * #IfUuidNeedUpdate
 645  *   argument: table_name
 646  *   behavior: this will add and populate a uuid column into table
 647  *
 648  *  #IfUuidNeedUpdateId
 649  *   argument: table_name primary_id
 650  *   behavior: this will add and populate a uuid column into table
 651  *
 652  * #IfUuidNeedUpdateVertical
 653  *   argument: table_name table_columns
 654  *   behavior: this will add and populate a uuid column into vertical table for combinations of table_columns given
 655  *
 656  * #IfNotMigrateClickOptions
 657  *   Custom function for the importing of the Clickoptions settings (if exist) from the codebase into the database
 658  *
 659  * #IfNotListOccupation
 660  * Custom function for creating Occupation List
 661  *
 662  * #IfNotListReaction
 663  * Custom function for creating Reaction List
 664  *
 665  * #IfNotWenoRx
 666  * Custom function for importing new drug data
 667  *
 668  * #IfTextNullFixNeeded
 669  *   desc: convert all text fields without default null to have default null.
 670  *   arguments: none
 671  *
 672  * #IfTableEngine
 673  *   desc:      Execute SQL if the table has been created with given engine specified.
 674  *   arguments: table_name engine
 675  *   behavior:  Use when engine conversion requires more than one ALTER TABLE
 676  *
 677  * #IfInnoDBMigrationNeeded
 678  *   desc: find all MyISAM tables and convert them to InnoDB.
 679  *   arguments: none
 680  *   behavior: can take a long time.
 681  *
 682 * #IfDocumentNamingNeeded
 683 *  desc: populate name field with document names.
 684 *  arguments: none
 685 *
 686  * #EndIf
 687  *   all blocks are terminated with a #EndIf statement.
 688  *
 689  * @param string $filename Sql upgrade/patch filename
 690  */
 691 function upgradeFromSqlFile($filename, $path = '')
 692 {
 693     global $webserver_root;
 694     $skip_msg = xlt("Skipping section");
 695
 696     flush();
 697     echo "<p class='text-success'>" . xlt("Processing") . " " . $filename . "...</p>\n";
 698
 699     $fullname = ((!empty($path) && is_dir($path)) ? $path : $webserver_root) . "/sql/$filename";
 700     $file_size = filesize($fullname);
 701
 702     $fd = fopen($fullname, 'r');
 703     if ($fd == false) {
 704         echo xlt("ERROR. Could not open") . " " . $fullname . ".\n";
 705         flush();
 706         return;
 707     }
 708
 709     $query = "";
 710     $line = "";
 711     $skipping = false;
 712     $special = false;
 713     $trim = true;
 714     $progress = 0;
 715
 716     while (!feof($fd)) {
 717         $line = fgets($fd, 2048);
 718         $line = rtrim($line);
 719
 720         $progress += strlen($line);
 721
 722         if (preg_match('/^\s*--/', $line)) {
 723             continue;
 724         }
 725
 726         if ($line == "") {
 727             continue;
 728         }
 729
 730         if (empty($GLOBALS['force_simple_sql_upgrade'])) {
 731             // this is skipped when running sql upgrade from command line
 732             $progress_stat = 100 - round((($file_size - $progress) / $file_size) * 100, 0);
 733             $progress_stat = $progress_stat > 100 ? 100 : $progress_stat;
 734             echo "<script>processProgress = $progress_stat;progressStatus();</script>";
 735         }
 736
 737         if (preg_match('/^#IfNotTable\s+(\S+)/', $line, $matches)) {
 738             $skipping = tableExists($matches[1]);
 739             if ($skipping) {
 740                 echo "<p class='text-success'>$skip_msg $line</p>\n";
 741             }
 742         } elseif (preg_match('/^#IfTable\s+(\S+)/', $line, $matches)) {
 743             $skipping = !tableExists($matches[1]);
 744             if ($skipping) {
 745                 echo "<p class='text-success'>$skip_msg $line</p>\n";
 746             }
 747         } elseif (preg_match('/^#IfColumn\s+(\S+)\s+(\S+)/', $line, $matches)) {
 748             if (tableExists($matches[1])) {
 749                 $skipping = !columnExists($matches[1], $matches[2]);
 750             } else {
 751                 // If no such table then the column is deemed "missing".
 752                 $skipping = true;
 753             }
 754
 755             if ($skipping) {
 756                 echo "<p class='text-success'>$skip_msg $line</p>\n";
 757             }
 758         } elseif (preg_match('/^#IfMissingColumn\s+(\S+)\s+(\S+)/', $line, $matches)) {
 759             if (tableExists($matches[1])) {
 760                 $skipping = columnExists($matches[1], $matches[2]);
 761             } else {
 762                 // If no such table then the column is deemed not "missing".
 763                 $skipping = true;
 764             }
 765
 766             if ($skipping) {
 767                 echo "<p class='text-success'>$skip_msg $line</p>\n";
 768             }
 769         } elseif (preg_match('/^#IfNotColumnTypeDefault\s+(\S+)\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
 770             // This allows capturing a default setting that is not blank
 771             if (tableExists($matches[1])) {
 772                 $skipping = columnHasTypeDefault($matches[1], $matches[2], $matches[3], $matches[4]);
 773             } else {
 774                 // If no such table then the column type is deemed not "missing".
 775                 $skipping = true;
 776             }
 777
 778             if ($skipping) {
 779                 echo "<p class='text-success'>$skip_msg $line</p>\n";
 780             }
 781         } elseif (preg_match('/^#IfNotColumnTypeDefault\s+(\S+)\s+(\S+)\s+(\S+)/', $line, $matches)) {
 782             // This allows capturing a default setting that is blank
 783             if (tableExists($matches[1])) {
 784                 $skipping = columnHasTypeDefault($matches[1], $matches[2], $matches[3], $matches[4]);
 785             } else {
 786                 // If no such table then the column type is deemed not "missing".
 787                 $skipping = true;
 788             }
 789
 790             if ($skipping) {
 791                 echo "<p class='text-success'>$skip_msg $line</p>\n";
 792             }
 793         } elseif (preg_match('/^#IfNotColumnType\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
 794             if (tableExists($matches[1])) {
 795                 $skipping = columnHasType($matches[1], $matches[2], $matches[3]);
 796             } else {
 797                 // If no such table then the column type is deemed not "missing".
 798                 $skipping = true;
 799             }
 800
 801             if ($skipping) {
 802                 echo "<p class='text-success'>$skip_msg $line</p>\n";
 803             }
 804         } elseif (preg_match('/^#IfIndex\s+(\S+)\s+(\S+)/', $line, $matches)) {
 805             if (tableExists($matches[1])) {
 806                 // If no such index then skip.
 807                 $skipping = !tableHasIndex($matches[1], $matches[2]);
 808             } else {
 809                 // If no such table then skip.
 810                 $skipping = true;
 811             }
 812
 813             if ($skipping) {
 814                 echo "<p class='text-success'>$skip_msg $line</p>\n";
 815             }
 816         } elseif (preg_match('/^#IfNotIndex\s+(\S+)\s+(\S+)/', $line, $matches)) {
 817             if (tableExists($matches[1])) {
 818                 $skipping = tableHasIndex($matches[1], $matches[2]);
 819             } else {
 820                 // If no such table then the index is deemed not "missing".
 821                 $skipping = true;
 822             }
 823
 824             if ($skipping) {
 825                 echo "<p class='text-success'>$skip_msg $line</p>\n";
 826             }
 827         } elseif (preg_match('/^#IfNotRow\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
 828             if (tableExists($matches[1])) {
 829                 $skipping = tableHasRow($matches[1], $matches[2], $matches[3]);
 830             } else {
 831                 // If no such table then the row is deemed not "missing".
 832                 $skipping = true;
 833             }
 834
 835             if ($skipping) {
 836                 echo "<p class='text-success'>$skip_msg $line</p>\n";
 837             }
 838         } elseif (preg_match('/^#IfNotRow2D\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
 839             if (tableExists($matches[1])) {
 840                 $skipping = tableHasRow2D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5]);
 841             } else {
 842                 // If no such table then the row is deemed not "missing".
 843                 $skipping = true;
 844             }
 845
 846             if ($skipping) {
 847                 echo "<p class='text-success'>$skip_msg $line</p>\n";
 848             }
 849         } elseif (preg_match('/^#IfNotRow3D\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
 850             if (tableExists($matches[1])) {
 851                 $skipping = tableHasRow3D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5], $matches[6], $matches[7]);
 852             } else {
 853                 // If no such table then the row is deemed not "missing".
 854                 $skipping = true;
 855             }
 856
 857             if ($skipping) {
 858                 echo "<p class='text-success'>$skip_msg $line</p>\n";
 859             }
 860         } elseif (preg_match('/^#IfNotRow4D\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
 861             if (tableExists($matches[1])) {
 862                 $skipping = tableHasRow4D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5], $matches[6], $matches[7], $matches[8], $matches[9]);
 863             } else {
 864                 // If no such table then the row is deemed not "missing".
 865                 $skipping = true;
 866             }
 867
 868             if ($skipping) {
 869                 echo "<p class='text-success'>$skip_msg $line</p>\n";
 870             }
 871         } elseif (preg_match('/^#IfNotRow2Dx2\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
 872             if (tableExists($matches[1])) {
 873                 // If either check exist, then will skip
 874                 $firstCheck = tableHasRow2D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5]);
 875                 $secondCheck = tableHasRow2D($matches[1], $matches[2], $matches[3], $matches[6], $matches[7]);
 876                 if ($firstCheck || $secondCheck) {
 877                     $skipping = true;
 878                 } else {
 879                     $skipping = false;
 880                 }
 881             } else {
 882                 // If no such table then the row is deemed not "missing".
 883                 $skipping = true;
 884             }
 885
 886             if ($skipping) {
 887                 echo "<p class='text-success'>$skip_msg $line</p>\n";
 888             }
 889         } elseif (preg_match('/^#IfRow2D\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
 890             if (tableExists($matches[1])) {
 891                 $skipping = !(tableHasRow2D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5]));
 892             } else {
 893                 // If no such table then should skip.
 894                 $skipping = true;
 895             }
 896
 897             if ($skipping) {
 898                 echo "<p class='text-success'>$skip_msg $line</p>\n";
 899             }
 900         } elseif (preg_match('/^#IfRow3D\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
 901             if (tableExists($matches[1])) {
 902                 $skipping = !(tableHasRow3D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5], $matches[6], $matches[7]));
 903             } else {
 904                 // If no such table then should skip.
 905                 $skipping = true;
 906             }
 907
 908             if ($skipping) {
 909                 echo "<p class='text-success'>$skip_msg $line</p>\n";
 910             }
 911         } elseif (preg_match('/^#IfRow\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
 912             if (tableExists($matches[1])) {
 913                 $skipping = !(tableHasRow($matches[1], $matches[2], $matches[3]));
 914             } else {
 915                 // If no such table then should skip.
 916                 $skipping = true;
 917             }
 918
 919             if ($skipping) {
 920                 echo "<p class='text-success'>$skip_msg $line</p>\n";
 921             }
 922         } elseif (preg_match('/^#IfNotMigrateClickOptions/', $line)) {
 923             if (tableExists("issue_types")) {
 924                 $skipping = true;
 925             } else {
 926                 // Create issue_types table and import the Issue Types and clickoptions settings from codebase into the database
 927                 clickOptionsMigrate();
 928                 $skipping = false;
 929             }
 930
 931             if ($skipping) {
 932                 echo "<p class='text-success'>$skip_msg $line</p>\n";
 933             }
 934         } elseif (preg_match('/^#IfNotListOccupation/', $line)) {
 935             if ((listExists("Occupation")) || (!columnExists('patient_data', 'occupation'))) {
 936                 $skipping = true;
 937             } else {
 938                 // Create Occupation list
 939                 CreateOccupationList();
 940                 $skipping = false;
 941                 echo "<p class='text-success'>Built Occupation List</p>\n";
 942             }
 943
 944             if ($skipping) {
 945                 echo "<p class='text-success'>$skip_msg $line</p>\n";
 946             }
 947         } elseif (preg_match('/^#IfNotListReaction/', $line)) {
 948             if ((listExists("reaction")) || (!columnExists('lists', 'reaction'))) {
 949                 $skipping = true;
 950             } else {
 951                 // Create Reaction list
 952                 CreateReactionList();
 953                 $skipping = false;
 954                 echo "<p class='text-success'>Built Reaction List</p>\n";
 955             }
 956
 957             if ($skipping) {
 958                 echo "<p class='text-success'>$skip_msg $line</p>\n";
 959             }
 960         } elseif (preg_match('/^#IfNotListImmunizationManufacturer/', $line)) {
 961             if (listExists("Immunization_Manufacturer")) {
 962                 $skipping = true;
 963             } else {
 964                 // Create Immunization Manufacturer list
 965                 CreateImmunizationManufacturerList();
 966                 $skipping = false;
 967                 echo "<p class='text-success'>Built Immunization Manufacturer List</p>\n";
 968             }
 969
 970             if ($skipping) {
 971                 echo "<p class='text-success'>$skip_msg $line</p>\n";
 972             }
 973         } elseif (preg_match('/^#IfNotWenoRx/', $line)) {
 974             if (tableHasRow('erx_weno_drugs', "drug_id", '1008') == true) {
 975                 $skipping = true;
 976             } else {
 977                 //import drug data
 978                 ImportDrugInformation();
 979                 $skipping = false;
 980                 echo "<p class='text-success'>Imported eRx Weno Drug Data</p>\n";
 981             }
 982             if ($skipping) {
 983                 echo "<p class='text-success'>$skip_msg $line</p>\n";
 984             }
 985             // convert all *text types to use default null setting
 986         } elseif (preg_match('/^#IfTextNullFixNeeded/', $line)) {
 987             $items_to_convert = sqlStatement(
 988                 "SELECT col.`table_name` AS table_name, col.`column_name` AS column_name,
 989       col.`data_type` AS data_type, col.`column_comment` AS column_comment
 990       FROM `information_schema`.`columns` col INNER JOIN `information_schema`.`tables` tab
 991       ON tab.TABLE_CATALOG=col.TABLE_CATALOG AND tab.table_schema=col.table_schema AND tab.table_name=col.table_name
 992       WHERE col.`data_type` IN ('tinytext', 'text', 'mediumtext', 'longtext')
 993       AND col.is_nullable = 'NO' AND col.table_schema = ? AND tab.table_type = 'BASE TABLE'",
 994                 array(databaseName())
 995             );
 996             $skipping = true;
 997             while ($item = sqlFetchArray($items_to_convert)) {
 998                 if (empty($item['table_name'])) {
 999                     continue;
1000                 }
1001                 if ($skipping) {
1002                     $skipping = false;
1003                     echo '<p>Starting conversion of *TEXT types to use default NULL.</p>', "\n";
1004                     flush_echo();
1005                 }
1006                 if (!empty($item['column_comment'])) {
1007                     $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']) . "'");
1008                 } else {
1009                     $res = sqlStatement("ALTER TABLE `" . add_escape_custom($item['table_name']) . "` MODIFY `" . add_escape_custom($item['column_name']) . "` " . add_escape_custom($item['data_type']));
1010                 }
1011                 // If above query didn't work, then error will be outputted via the sqlStatement function.
1012                 echo "<p class='text-success'>" . text($item['table_name']) . "." . text($item['column_name']) . " sql column was successfully converted to " . text($item['data_type']) . " with default NULL setting.</p>\n";
1013                 flush_echo();
1014             }
1015             if ($skipping) {
1016                 echo "<p class='text-success'>$skip_msg $line</p>\n";
1017             }
1018         } elseif (preg_match('/^#IfTableEngine\s+(\S+)\s+(MyISAM|InnoDB)/', $line, $matches)) {
1019             // perform special actions if table has specific engine
1020             $skipping = !tableHasEngine($matches[1], $matches[2]);
1021             if ($skipping) {
1022                 echo "<p class='text-success'>$skip_msg $line</p>\n";
1023             }
1024         } elseif (preg_match('/^#IfInnoDBMigrationNeeded/', $line)) {
1025             // find MyISAM tables and attempt to convert them
1026             //tables that need to skip InnoDB migration (stay at MyISAM for now)
1027             $tables_skip_migration = array('form_eye_mag');
1028
1029             $tables_list = getTablesList(array('engine' => 'MyISAM'));
1030
1031             $skipping = true;
1032             foreach ($tables_list as $k => $t) {
1033                 if (empty($t)) {
1034                     continue;
1035                 }
1036                 if ($skipping) {
1037                     $skipping = false;
1038                     echo '<p>Starting migration to InnoDB, please wait.</p>', "\n";
1039                     flush_echo();
1040                 }
1041
1042                 if (in_array($t, $tables_skip_migration)) {
1043                     printf('<p class="text-success">Table %s was purposefully skipped and NOT migrated to InnoDB.</p>', $t);
1044                     continue;
1045                 }
1046
1047                 $res = MigrateTableEngine($t, 'InnoDB');
1048                 if ($res === true) {
1049                     printf('<p class="text-success">Table %s migrated to InnoDB.</p>', $t);
1050                 } else {
1051                     printf('<p class="text-danger">Error migrating table %s to InnoDB</p>', $t);
1052                     error_log(sprintf('Error migrating table %s to InnoDB', errorLogEscape($t)));
1053                 }
1054             }
1055
1056             if ($skipping) {
1057                 echo "<p class='text-success'>$skip_msg $line</p>\n";
1058             }
1059         } elseif (preg_match('/^#IfUuidNeedUpdate\s+(\S+)/', $line, $matches)) {
1060             $uuidRegistry = new UuidRegistry(['table_name' => $matches[1]]);
1061             if (tableExists($matches[1]) && $uuidRegistry->tableNeedsUuidCreation()) {
1062                 $skipping = false;
1063                 echo "<p>Going to add UUIDs to " . $matches[1] . " table</p>\n";
1064                 flush_echo();
1065                 $number = $uuidRegistry->createMissingUuids();
1066                 echo "<p class='text-success'>Successfully completed added " . $number . " UUIDs to " . $matches[1] . " table</p>\n";
1067                 flush_echo();
1068             } else {
1069                 $skipping = true;
1070             }
1071             if ($skipping) {
1072                 echo "<p class='text-success'>$skip_msg $line</p>\n";
1073             }
1074         } elseif (preg_match('/^#IfUuidNeedUpdateId\s+(\S+)\s+(\S+)/', $line, $matches)) {
1075             $uuidRegistry = new UuidRegistry([
1076                 'table_name' => $matches[1],
1077                 'table_id' => $matches[2]
1078             ]);
1079             if (
1080                 tableExists($matches[1]) &&
1081                 columnExists($matches[1], $matches[2]) &&
1082                 $uuidRegistry->tableNeedsUuidCreation()
1083             ) {
1084                 $skipping = false;
1085                 echo "<p>Going to add UUIDs to " . $matches[1] . " table</p>\n";
1086                 flush_echo();
1087                 $number = $uuidRegistry->createMissingUuids();
1088                 echo "<p class='text-success'>Successfully completed added " . $number . " UUIDs to " . $matches[1] . " table</p>\n";
1089                 flush_echo();
1090             } else {
1091                 $skipping = true;
1092             }
1093             if ($skipping) {
1094                 echo "<p class='text-success'>$skip_msg $line</p>\n";
1095             }
1096         } elseif (preg_match('/^#IfUuidNeedUpdateVertical\s+(\S+)\s+(\S+)/', $line, $matches)) {
1097             $vertical_table_columns = explode(":", $matches[2]);
1098             $uuidRegistry = new UuidRegistry(['table_name' => $matches[1], 'table_vertical' => $vertical_table_columns]);
1099             if (tableExists($matches[1]) && $uuidRegistry->tableNeedsUuidCreation()) {
1100                 $skipping = false;
1101                 echo "<p>Going to add UUIDs to " . $matches[1] . " vertical table</p>\n";
1102                 flush_echo();
1103                 $number = $uuidRegistry->createMissingUuids();
1104                 echo "<p class='text-success'>Successfully completed added " . $number . " UUIDs to " . $matches[1] . " vertical table</p>\n";
1105                 flush_echo();
1106             } else {
1107                 $skipping = true;
1108             }
1109             if ($skipping) {
1110                 echo "<p class='text-success'>$skip_msg $line</p>\n";
1111             }
1112         } elseif (preg_match('/^#ConvertLayoutProperties/', $line)) {
1113             if ($skipping) {
1114                 echo "<p class='text-success'>$skip_msg $line</p>\n";
1115             } else {
1116                 echo "Converting layout properties ...<br />\n";
1117                 flush_echo();
1118                 convertLayoutProperties();
1119             }
1120         } elseif (preg_match('/^#IfDocumentNamingNeeded/', $line)) {
1121             $emptyNames = sqlStatementNoLog("SELECT `id`, `url`, `name`, `couch_docid` FROM `documents` WHERE `name` = '' OR `name` IS NULL");
1122             if (sqlNumRows($emptyNames) > 0) {
1123                 echo "<p>Converting document names.</p>\n";
1124                 flush_echo();
1125                 while ($row = sqlFetchArray($emptyNames)) {
1126                     if (!empty($row['couch_docid'])) {
1127                         sqlStatementNoLog("UPDATE `documents` SET `name` = ? WHERE `id` = ?", [$row['url'], $row['id']]);
1128                     } else {
1129                         sqlStatementNoLog("UPDATE `documents` SET `name` = ? WHERE `id` = ?", [basename_international($row['url']), $row['id']]);
1130                     }
1131                 }
1132                 echo "<p class='text-success'>Completed conversion of document names</p>\n";
1133                 flush_echo();
1134                 $skipping = false;
1135             } else {
1136                 $skipping = true;
1137             }
1138             if ($skipping) {
1139                 echo "<p class='text-success'>$skip_msg $line</p>\n";
1140             }
1141         } elseif (preg_match('/^#EndIf/', $line)) {
1142             $skipping = false;
1143         }
1144         if (preg_match('/^#SpecialSql/', $line)) {
1145             $special = true;
1146             $line = " ";
1147         } elseif (preg_match('/^#EndSpecialSql/', $line)) {
1148             $special = false;
1149             $trim = false;
1150             $line = " ";
1151         } elseif (preg_match('/^\s*#/', $line)) {
1152             continue;
1153         }
1154
1155         if ($skipping) {
1156             continue;
1157         }
1158
1159         if ($special) {
1160             $query = $query . " " . $line;
1161             continue;
1162         }
1163
1164         $query = $query . $line;
1165
1166         if (substr(trim($query), -1) == ';') {
1167             if ($trim) {
1168                 $query = rtrim($query, ';');
1169             } else {
1170                 $trim = true;
1171             }
1172
1173             flush_echo("$query<br />\n");
1174
1175             if (!sqlStatement($query)) {
1176                 echo "<p class='text-danger'>The above statement failed: " .
1177                     getSqlLastError() . "<br />Upgrading will continue.<br /></p>\n";
1178                 flush_echo();
1179             }
1180
1181             $query = '';
1182         }
1183     }
1184
1185     flush();
1186 } // end function