4 * Upgrading and patching functions of database.
6 * Functions to allow safe database modifications
7 * during upgrading and patches.
9 * Copyright (C) 2008-2012 Rod Roark <rod@sunsetsystems.com>
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>.
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
29 use OpenEMR\Common\Uuid\UuidRegistry
;
32 * Check if a Sql table exists.
34 * @param string $tblname Sql Table Name
35 * @return boolean returns true if the sql table exists
37 function tableExists($tblname)
39 $row = sqlQuery("SHOW TABLES LIKE '$tblname'");
48 * Check if a Sql column exists in a selected table.
50 * @param string $tblname Sql Table Name
51 * @param string $colname Sql Column Name
52 * @return boolean returns true if the sql column exists
54 function columnExists($tblname, $colname)
56 $row = sqlQuery("SHOW COLUMNS FROM $tblname LIKE '$colname'");
65 * Check if a Sql column has a certain type.
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
72 function columnHasType($tblname, $colname, $coltype)
74 $row = sqlQuery("SHOW COLUMNS FROM $tblname LIKE '$colname'");
79 return (strcasecmp($row['Type'], $coltype) == 0);
83 * Check if a Sql column has a certain type and a certain default value.
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
91 function columnHasTypeDefault($tblname, $colname, $coltype, $coldefault)
93 $row = sqlQuery("SHOW COLUMNS FROM $tblname WHERE `Field` = ?", [$colname]);
98 // Check if the type matches
99 if (strcasecmp($row['Type'], $coltype) != 0) {
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));
113 // Standard case when checking if default is neither NULL or ""(blank)
114 return (strcasecmp($row['Default'], $coldefault) == 0);
119 * Check if a Sql row exists. (with one value)
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
126 function tableHasRow($tblname, $colname, $value)
128 $row = sqlQuery("SELECT COUNT(*) AS count FROM $tblname WHERE " .
129 "$colname LIKE '$value'");
130 return $row['count'] ?
true : false;
134 * Check if a Sql row exists. (with two values)
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
143 function tableHasRow2D($tblname, $colname, $value, $colname2, $value2)
145 $row = sqlQuery("SELECT COUNT(*) AS count FROM $tblname WHERE " .
146 "$colname LIKE '$value' AND $colname2 LIKE '$value2'");
147 return $row['count'] ?
true : false;
151 * Check if a Sql row exists. (with three values)
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
162 function tableHasRow3D($tblname, $colname, $value, $colname2, $value2, $colname3, $value3)
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;
170 * Check if a Sql row exists. (with four values)
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
183 function tableHasRow4D($tblname, $colname, $value, $colname2, $value2, $colname3, $value3, $colname4, $value4)
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;
191 * Check if a Sql table has a certain index/key.
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
197 function tableHasIndex($tblname, $colname)
199 $row = sqlQuery("SHOW INDEX FROM `$tblname` WHERE `Key_name` = '$colname'");
200 return (empty($row)) ?
false : true;
205 * Check if a table has a certain engine
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
211 function tableHasEngine($tblname, $engine)
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;
220 * Check if a list exists.
222 * @param string $option_id Sql List Option ID
223 * @return boolean returns true if the list exists
225 function listExists($option_id)
227 $row = sqlQuery("SELECT * FROM list_options WHERE list_id = 'lists' AND option_id = ?", array($option_id));
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.
239 function clickOptionsMigrate()
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");
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)) {
253 if ($line_of_text == "") {
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'));
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));
272 fclose($file_handle);
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
279 function CreateOccupationList()
281 $res = sqlStatement("SELECT DISTINCT occupation FROM patient_data WHERE occupation <> ''");
282 while ($row = sqlFetchArray($res)) {
283 $records[] = $row['occupation'];
286 sqlStatement("INSERT INTO list_options (list_id, option_id, title) VALUES('lists', 'Occupation', 'Occupation')");
287 if (count($records) > 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)));
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
299 function CreateReactionList()
301 $res = sqlStatement("SELECT DISTINCT reaction FROM lists WHERE reaction <> ''");
302 while ($row = sqlFetchArray($res)) {
303 $records[] = $row['reaction'];
306 sqlStatement("INSERT INTO list_options (list_id, option_id, title) VALUES('lists', 'reaction', 'Reaction')");
307 if (count($records) > 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)));
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
320 function CreateImmunizationManufacturerList()
322 $res = sqlStatement("SELECT DISTINCT manufacturer FROM immunizations WHERE manufacturer <> ''");
323 while ($row = sqlFetchArray($res)) {
324 $records[] = $row['manufacturer'];
327 sqlStatement("INSERT INTO list_options (list_id, option_id, title) VALUES ('lists','Immunization_Manufacturer','Immunization Manufacturer')");
328 if (count($records) > 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)));
338 * This function is to populate the weno drug table if the feature is enabled before upgrade.
340 function ImportDrugInformation()
342 if ($GLOBALS['weno_rx_enable']) {
343 $drugs = file_get_contents('contrib/weno/erx_weno_drugs.sql');
344 $drugsArray = preg_split('/;\R/', $drugs);
346 // Settings to drastically speed up import with InnoDB
347 sqlStatementNoLog("SET autocommit=0");
348 sqlStatementNoLog("START TRANSACTION");
350 foreach ($drugsArray as $drug) {
354 sqlStatementNoLog($drug);
357 // Settings to drastically speed up import with InnoDB
358 sqlStatementNoLog("COMMIT");
359 sqlStatementNoLog("SET autocommit=1");
364 * Request to information_schema
366 * @param array $arg possible arguments: engine, table_name
367 * @return SQLStatement
369 function getTablesList($arg = array())
372 $sql = 'SELECT table_name FROM information_schema.tables WHERE table_schema=database() AND table_type="BASE TABLE"';
374 if (!empty($arg['engine'])) {
375 $binds[] = $arg['engine'];
376 $sql .= ' AND engine=?';
379 if (!empty($arg['table_name'])) {
380 $binds[] = $arg['table_name'];
381 $sql .= ' AND table_name=?';
384 $res = sqlStatement($sql, $binds);
387 while ($row = sqlFetchArray($res)) {
388 $records[ $row['table_name'] ] = $row['table_name'];
396 * Convert table engine.
397 * @param string $table
398 * @param string $engine
399 * ADODB will fail if there was an error during conversion
401 function MigrateTableEngine($table, $engine)
403 $r = sqlStatement('ALTER TABLE `' . $table . '` ENGINE=?', $engine);
407 function convertLayoutProperties()
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'];
413 'title' => 'Unknown',
417 'option_value' => '0',
419 if (substr($form_id, 0, 3) == 'LBF') {
421 "SELECT title, mapping, notes, activity, option_value FROM list_options WHERE list_id = 'lbfnames' AND option_id = ?",
427 if (empty($props['mapping'])) {
428 $props['mapping'] = 'Clinical';
430 } elseif (substr($form_id, 0, 3) == 'LBT') {
432 "SELECT title, mapping, notes, activity, option_value FROM list_options WHERE list_id = 'transactions' AND option_id = ?",
438 if (empty($props['mapping'])) {
439 $props['mapping'] = 'Transactions';
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';
455 $query = "INSERT INTO layout_group_properties SET " .
456 "grp_form_id = ?, " .
457 "grp_group_id = '', " .
459 "grp_mapping = ?, " .
460 "grp_activity = ?, " .
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'];
469 if (isset($jobj['size'])) {
470 $query .= ", grp_size = ?";
471 $sqlvars[] = $jobj['size'];
473 if (isset($jobj['issue'])) {
474 $query .= ", grp_issue_type = ?";
475 $sqlvars[] = $jobj['issue'];
477 if (isset($jobj['aco'])) {
478 $query .= ", grp_aco_spec = ?";
479 $sqlvars[] = $jobj['aco'];
481 if (isset($jobj['services'])) {
482 $query .= ", grp_services = ?";
483 // if present but empty, means all services
484 $sqlvars[] = $jobj['services'] ?
$jobj['services'] : '*';
486 if (isset($jobj['products'])) {
487 $query .= ", grp_products = ?";
488 // if present but empty, means all products
489 $sqlvars[] = $jobj['products'] ?
$jobj['products'] : '*';
491 if (isset($jobj['diags'])) {
492 $query .= ", grp_diags = ?";
493 // if present but empty, means all diags
494 $sqlvars[] = $jobj['diags'] ?
$jobj['diags'] : '*';
497 sqlStatement($query, $sqlvars);
499 $gres = sqlStatement(
500 "SELECT DISTINCT group_name FROM layout_options WHERE form_id = ? ORDER BY group_name",
504 // For each group within this layout...
505 while ($grow = sqlFetchArray($gres)) {
506 $group_name = $grow['group_name'];
509 $a = explode('|', $group_name);
510 foreach ($a as $tmp) {
511 $group_id .= substr($tmp, 0, 1);
512 $title = substr($tmp, 1);
515 "UPDATE layout_options SET group_id = ? WHERE form_id = ? AND group_name = ?",
516 array($group_id, $form_id, $group_name)
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'];
530 if (isset($jobj['size'])) {
531 $query .= ", grp_size = ?";
532 $sqlvars[] = $jobj['size'];
535 ****************************************************************/
536 // echo $query; foreach ($sqlvars as $tmp) echo " '$tmp'"; echo "<br />\n"; // debugging
537 sqlStatement($query, $sqlvars);
543 * Upgrade or patch the database with a selected upgrade/patch file.
545 * The following "functions" within the selected file will be processed:
548 * argument: table_name
549 * behavior: if the table_name does not exist, the block will be executed
552 * argument: table_name
553 * behavior: if the table_name does exist, the block will be executed
556 * arguments: table_name colname
557 * behavior: if the table and column exist, the block will be executed
560 * arguments: table_name colname
561 * behavior: if the table exists but the column does not, the block will be executed
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
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
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.
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.
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.
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.
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.
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.
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.
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.
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.
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
617 * argument: table_name
618 * behavior: this will add and populate a uuid column into table
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
624 * #IfNotMigrateClickOptions
625 * Custom function for the importing of the Clickoptions settings (if exist) from the codebase into the database
627 * #IfNotListOccupation
628 * Custom function for creating Occupation List
631 * Custom function for creating Reaction List
634 * Custom function for importing new drug data
636 * #IfTextNullFixNeeded
637 * desc: convert all text fields without default null to have default null.
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
645 * #IfInnoDBMigrationNeeded
646 * desc: find all MyISAM tables and convert them to InnoDB.
648 * behavior: can take a long time.
651 * all blocks are terminated with a #EndIf statement.
653 * @param string $filename Sql upgrade/patch filename
655 function upgradeFromSqlFile($filename, $path = '')
657 global $webserver_root;
660 echo "<p class='text-success'>Processing $filename ...</p>\n";
662 $fullname = ( (!empty($path) && is_dir($path)) ?
$path : $webserver_root) . "/sql/$filename";
664 $fd = fopen($fullname, 'r');
666 echo "ERROR. Could not open '$fullname'.\n";
676 $line = fgets($fd, 2048);
677 $line = rtrim($line);
679 if (preg_match('/^\s*--/', $line)) {
687 if (preg_match('/^#IfNotTable\s+(\S+)/', $line, $matches)) {
688 $skipping = tableExists($matches[1]);
690 echo "<p class='text-success'>Skipping section $line</p>\n";
692 } elseif (preg_match('/^#IfTable\s+(\S+)/', $line, $matches)) {
693 $skipping = ! tableExists($matches[1]);
695 echo "<p class='text-success'>Skipping section $line</p>\n";
697 } elseif (preg_match('/^#IfColumn\s+(\S+)\s+(\S+)/', $line, $matches)) {
698 if (tableExists($matches[1])) {
699 $skipping = !columnExists($matches[1], $matches[2]);
701 // If no such table then the column is deemed "missing".
706 echo "<p class='text-success'>Skipping section $line</p>\n";
708 } elseif (preg_match('/^#IfMissingColumn\s+(\S+)\s+(\S+)/', $line, $matches)) {
709 if (tableExists($matches[1])) {
710 $skipping = columnExists($matches[1], $matches[2]);
712 // If no such table then the column is deemed not "missing".
717 echo "<p class='text-success'>Skipping section $line</p>\n";
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]);
724 // If no such table then the column type is deemed not "missing".
729 echo "<p class='text-success'>Skipping section $line</p>\n";
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]);
736 // If no such table then the column type is deemed not "missing".
741 echo "<p class='text-success'>Skipping section $line</p>\n";
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]);
747 // If no such table then the column type is deemed not "missing".
752 echo "<p class='text-success'>Skipping section $line</p>\n";
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]);
759 // If no such table then skip.
764 echo "<p class='text-success'>Skipping section $line</p>\n";
766 } elseif (preg_match('/^#IfNotIndex\s+(\S+)\s+(\S+)/', $line, $matches)) {
767 if (tableExists($matches[1])) {
768 $skipping = tableHasIndex($matches[1], $matches[2]);
770 // If no such table then the index is deemed not "missing".
775 echo "<p class='text-success'>Skipping section $line</p>\n";
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]);
781 // If no such table then the row is deemed not "missing".
786 echo "<p class='text-success'>Skipping section $line</p>\n";
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]);
792 // If no such table then the row is deemed not "missing".
797 echo "<p class='text-success'>Skipping section $line</p>\n";
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]);
803 // If no such table then the row is deemed not "missing".
808 echo "<p class='text-success'>Skipping section $line</p>\n";
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]);
814 // If no such table then the row is deemed not "missing".
819 echo "<p class='text-success'>Skipping section $line</p>\n";
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) {
832 // If no such table then the row is deemed not "missing".
837 echo "<p class='text-success'>Skipping section $line</p>\n";
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]));
843 // If no such table then should skip.
848 echo "<p class='text-success'>Skipping section $line</p>\n";
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]));
854 // If no such table then should skip.
859 echo "<p class='text-success'>Skipping section $line</p>\n";
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]));
865 // If no such table then should skip.
870 echo "<p class='text-success'>Skipping section $line</p>\n";
872 } elseif (preg_match('/^#IfNotMigrateClickOptions/', $line)) {
873 if (tableExists("issue_types")) {
876 // Create issue_types table and import the Issue Types and clickoptions settings from codebase into the database
877 clickOptionsMigrate();
882 echo "<p class='text-success'>Skipping section $line</p>\n";
884 } elseif (preg_match('/^#IfNotListOccupation/', $line)) {
885 if ((listExists("Occupation")) ||
(!columnExists('patient_data', 'occupation'))) {
888 // Create Occupation list
889 CreateOccupationList();
891 echo "<p class='text-success'>Built Occupation List</p>\n";
895 echo "<p class='text-success'>Skipping section $line</p>\n";
897 } elseif (preg_match('/^#IfNotListReaction/', $line)) {
898 if ((listExists("reaction")) ||
(!columnExists('lists', 'reaction'))) {
901 // Create Reaction list
902 CreateReactionList();
904 echo "<p class='text-success'>Built Reaction List</p>\n";
908 echo "<p class='text-success'>Skipping section $line</p>\n";
910 } elseif (preg_match('/^#IfNotListImmunizationManufacturer/', $line)) {
911 if (listExists("Immunization_Manufacturer")) {
914 // Create Immunization Manufacturer list
915 CreateImmunizationManufacturerList();
917 echo "<p class='text-success'>Built Immunization Manufacturer List</p>\n";
921 echo "<p class='text-success'>Skipping section $line</p>\n";
923 } elseif (preg_match('/^#IfNotWenoRx/', $line)) {
924 if (tableHasRow('erx_weno_drugs', "drug_id", '1008') == true) {
928 ImportDrugInformation();
930 echo "<p class='text-success'>Imported eRx Weno Drug Data</p>\n";
933 echo "<p class='text-success'>Skipping section $line</p>\n";
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'"
944 if (sqlNumRows($items_to_convert) == 0) {
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']) . "'");
953 $res = sqlStatement("ALTER TABLE `" . add_escape_custom($item['table_name']) . "` MODIFY `" . add_escape_custom($item['column_name']) . "` " . add_escape_custom($item['data_type']));
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";
962 echo "<p class='text-success'>Skipping section $line</p>\n";
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]);
968 echo "<p class='text-success'>Skipping section $line</p>\n";
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');
975 $tables_list = getTablesList(array('engine' => 'MyISAM'));
976 if (count($tables_list) == 0) {
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);
987 $res = MigrateTableEngine($t, 'InnoDB');
989 printf('<p class="text-success">Table %s migrated to InnoDB.</p>', $t);
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)));
998 echo "<p class='text-success'>Skipping section $line</p>\n";
1000 } elseif (preg_match('/^#IfUuidNeedUpdate\s+(\S+)/', $line, $matches)) {
1001 $uuidRegistry = new UuidRegistry(['table_name' => $matches[1]]);
1002 if (tableExists($matches[1]) && $uuidRegistry->tableNeedsUuidCreation()) {
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";
1011 echo "<p class='text-success'>Skipping section $line</p>\n";
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()) {
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";
1025 echo "<p class='text-success'>Skipping section $line</p>\n";
1027 } elseif (preg_match('/^#ConvertLayoutProperties/', $line)) {
1029 echo "<p class='text-success'>Skipping section $line</p>\n";
1031 echo "Converting layout properties ...<br />\n";
1032 convertLayoutProperties();
1034 } elseif (preg_match('/^#EndIf/', $line)) {
1038 if (preg_match('/^\s*#/', $line)) {
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";