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