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