/**
* Upgrade or patch the database with a selected upgrade/patch file.
*
* The following "functions" within the selected file will be processed:
*
* #IfNotTable
* argument: table_name
* behavior: if the table_name does not exist, the block will be executed
*
* #IfTable
* argument: table_name
* behavior: if the table_name does exist, the block will be executed
*
* #IfColumn
* arguments: table_name colname
* behavior: if the table and column exist, the block will be executed
*
* #IfMissingColumn
* arguments: table_name colname
* behavior: if the table exists but the column does not, the block will be executed
*
* #IfNotColumnType
* arguments: table_name colname value
* 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
*
* #IfNotRow
* arguments: table_name colname value
* behavior: If the table table_name does not have a row where colname = value, the block will be executed.
*
* #IfNotRow2D
* arguments: table_name colname value colname2 value2
* behavior: If the table table_name does not have a row where colname = value AND colname2 = value2, the block will be executed.
*
* #IfNotRow3D
* arguments: table_name colname value colname2 value2 colname3 value3
* 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.
*
* #IfNotRow4D
* arguments: table_name colname value colname2 value2 colname3 value3 colname4 value4
* 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.
*
* #IfNotRow2Dx2
* 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.
* arguments: table_name colname value colname2 value2 colname3 value3
* behavior: The block will be executed if both statements below are true:
* 1) The table table_name does not have a row where colname = value AND colname2 = value2.
* 2) The table table_name does not have a row where colname = value AND colname3 = value3.
*
* #IfRow2D
* arguments: table_name colname value colname2 value2
* behavior: If the table table_name does have a row where colname = value AND colname2 = value2, the block will be executed.
*
* #IfRow3D
* arguments: table_name colname value colname2 value2 colname3 value3
* behavior: If the table table_name does have a row where colname = value AND colname2 = value2 AND colname3 = value3, the block will be executed.
*
* #IfIndex
* desc: This function is most often used for dropping of indexes/keys.
* arguments: table_name colname
* behavior: If the table and index exist the relevant statements are executed, otherwise not.
*
* #IfNotIndex
* desc: This function will allow adding of indexes/keys.
* arguments: table_name colname
* behavior: If the index does not exist, it will be created
*
* #IfNotMigrateClickOptions
* Custom function for the importing of the Clickoptions settings (if exist) from the codebase into the database
*
* #IfNotListOccupation
* Custom function for creating Occupation List
*
* #IfNotListReaction
* Custom function for creating Reaction List
*
* #IfTextNullFixNeeded
* desc: convert all text fields without default null to have default null.
* arguments: none
*
* #IfTableEngine
* desc: Execute SQL if the table has been created with given engine specified.
* arguments: table_name engine
* behavior: Use when engine conversion requires more than one ALTER TABLE
*
* #IfInnoDBMigrationNeeded
* desc: find all MyISAM tables and convert them to InnoDB.
* arguments: none
* behavior: can take a long time.
*
* #EndIf
* all blocks are terminated with a #EndIf statement.
*
* @param string $filename Sql upgrade/patch filename
*/
function upgradeFromSqlFile($filename) {
global $webserver_root;
flush();
echo "<font color='green'>Processing $filename ...</font><br />\n";
$fullname = "$webserver_root/sql/$filename";
$fd = fopen($fullname, 'r');
if ($fd == FALSE) {
echo "ERROR. Could not open '$fullname'.\n";
flush();
return;
}
$query = "";
$line = "";
$skipping = false;
while (!feof ($fd)){
$line = fgets($fd, 2048);
$line = rtrim($line);
if (preg_match('/^\s*--/', $line)) continue;
if ($line == "") continue;
if (preg_match('/^#IfNotTable\s+(\S+)/', $line, $matches)) {
$skipping = tableExists($matches[1]);
if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
}
else if (preg_match('/^#IfTable\s+(\S+)/', $line, $matches)) {
$skipping = ! tableExists($matches[1]);
if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
}
else if (preg_match('/^#IfColumn\s+(\S+)\s+(\S+)/', $line, $matches)) {
if (tableExists($matches[1])) {
$skipping = !columnExists($matches[1], $matches[2]);
}
else {
// If no such table then the column is deemed "missing".
$skipping = true;
}
if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
}
else if (preg_match('/^#IfMissingColumn\s+(\S+)\s+(\S+)/', $line, $matches)) {
if (tableExists($matches[1])) {
$skipping = columnExists($matches[1], $matches[2]);
}
else {
// If no such table then the column is deemed not "missing".
$skipping = true;
}
if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
}
else if (preg_match('/^#IfNotColumnType\s+(\S+)\s+(\S+)\s+(\S+)/', $line, $matches)) {
if (tableExists($matches[1])) {
$skipping = columnHasType($matches[1], $matches[2], $matches[3]);
}
else {
// If no such table then the column type is deemed not "missing".
$skipping = true;
}
if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
}
else if (preg_match('/^#IfIndex\s+(\S+)\s+(\S+)/', $line, $matches)) {
if (tableExists($matches[1])) {
// If no such index then skip.
$skipping = !tableHasIndex($matches[1], $matches[2]);
}
else {
// If no such table then skip.
$skipping = true;
}
if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
}
else if (preg_match('/^#IfNotIndex\s+(\S+)\s+(\S+)/', $line, $matches)) {
if (tableExists($matches[1])) {
$skipping = tableHasIndex($matches[1], $matches[2]);
}
else {
// If no such table then the index is deemed not "missing".
$skipping = true;
}
if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
}
else if (preg_match('/^#IfNotRow\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
if (tableExists($matches[1])) {
$skipping = tableHasRow($matches[1], $matches[2], $matches[3]);
}
else {
// If no such table then the row is deemed not "missing".
$skipping = true;
}
if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
}
else if (preg_match('/^#IfNotRow2D\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
if (tableExists($matches[1])) {
$skipping = tableHasRow2D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5]);
}
else {
// If no such table then the row is deemed not "missing".
$skipping = true;
}
if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
}
else if (preg_match('/^#IfNotRow3D\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
if (tableExists($matches[1])) {
$skipping = tableHasRow3D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5], $matches[6], $matches[7]);
}
else {
// If no such table then the row is deemed not "missing".
$skipping = true;
}
if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
}
else if (preg_match('/^#IfNotRow4D\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
if (tableExists($matches[1])) {
$skipping = tableHasRow4D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5], $matches[6], $matches[7], $matches[8], $matches[9]);
}
else {
// If no such table then the row is deemed not "missing".
$skipping = true;
}
if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
}
else if (preg_match('/^#IfNotRow2Dx2\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
if (tableExists($matches[1])) {
// If either check exist, then will skip
$firstCheck = tableHasRow2D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5]);
$secondCheck = tableHasRow2D($matches[1], $matches[2], $matches[3], $matches[6], $matches[7]);
if ($firstCheck || $secondCheck) {
$skipping = true;
}
else {
$skipping = false;
}
}
else {
// If no such table then the row is deemed not "missing".
$skipping = true;
}
if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
}
else if (preg_match('/^#IfRow2D\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
if (tableExists($matches[1])) {
$skipping = !(tableHasRow2D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5]));
}
else {
// If no such table then should skip.
$skipping = true;
}
if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
}
else if (preg_match('/^#IfRow3D\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(.+)/', $line, $matches)) {
if (tableExists($matches[1])) {
$skipping = !(tableHasRow3D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5], $matches[6], $matches[7]));
}
else {
// If no such table then should skip.
$skipping = true;
}
if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
}
else if (preg_match('/^#IfNotMigrateClickOptions/', $line)) {
if (tableExists("issue_types")) {
$skipping = true;
}
else {
// Create issue_types table and import the Issue Types and clickoptions settings from codebase into the database
clickOptionsMigrate();
$skipping = false;
}
if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
}
else if (preg_match('/^#IfNotListOccupation/', $line)) {
if ( (listExists("Occupation")) || (!columnExists('patient_data','occupation')) ) {
$skipping = true;
}
else {
// Create Occupation list
CreateOccupationList();
$skipping = false;
echo "<font color='green'>Built Occupation List</font><br />\n";
}
if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
}
else if (preg_match('/^#IfNotListReaction/', $line)) {
if ( (listExists("reaction")) || (!columnExists('lists','reaction')) ) {
$skipping = true;
}
else {
// Create Reaction list
CreateReactionList();
$skipping = false;
echo "<font color='green'>Built Reaction List</font><br />\n";
}
if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
}
else if (preg_match('/^#IfNotListImmunizationManufacturer/', $line)){
if ( listExists("Immunization_Manufacturer") ) {
$skipping = true;
}
else {
// Create Immunization Manufacturer list
CreateImmunizationManufacturerList();
$skipping = false;
echo "<font color='green'>Built Immunization Manufacturer List</font><br />\n";
}
if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
}
// convert all *text types to use default null setting
else if (preg_match('/^#IfTextNullFixNeeded/', $line)) {
$items_to_convert = sqlStatement(
"SELECT col.`table_name`, col.`column_name`, col.`data_type`, col.`column_comment`
FROM `information_schema`.`columns` col INNER JOIN `information_schema`.`tables` tab
ON tab.TABLE_CATALOG=col.TABLE_CATALOG AND tab.table_schema=col.table_schema AND tab.table_name=col.table_name
WHERE col.`data_type` IN ('tinytext', 'text', 'mediumtext', 'longtext')
AND col.is_nullable='NO' AND col.table_schema=database() AND tab.table_type='BASE TABLE'");
if(sqlNumRows($items_to_convert) == 0) {
$skipping = true;
} else {
$skipping = false;
echo '<font color="black">Starting conversion of *TEXT types to use default NULL.</font><br />',"\n";
while($item = sqlFetchArray($items_to_convert)) {
if (!empty($item['column_comment'])) {
$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']) . "'");
}
else {
$res = sqlStatement("ALTER TABLE `" . add_escape_custom($item['table_name']) . "` MODIFY `" . add_escape_custom($item['column_name']) . "` " . add_escape_custom($item['data_type']));
}
// If above query didn't work, then error will be outputted via the sqlStatement function.
echo "<font color='green'>" . text($item['table_name']) . "." . text($item['column_name']) . " sql column was successfully converted to " . text($item['data_type']) . " with default NULL setting.</font><br />\n";
}
}
if($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
}
// perform special actions if table has specific engine
else if (preg_match('/^#IfTableEngine\s+(\S+)\s+(MyISAM|InnoDB)/', $line, $matches)) {
$skipping = !tableHasEngine( $matches[1], $matches[2] );
if ($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
}
// find MyISAM tables and attempt to convert them
else if (preg_match('/^#IfInnoDBMigrationNeeded/', $line)) {
//tables that need to skip InnoDB migration (stay at MyISAM for now)
$tables_skip_migration = array('form_eye_mag');
$tables_list = getTablesList( array('engine'=>'MyISAM'));
if( count($tables_list)==0 ) {
$skipping = true;
} else {
$skipping = false;
echo '<font color="black">Starting migration to InnoDB, please wait.</font><br />',"\n";
foreach( $tables_list as $k=>$t ) {
if (in_array($t,$tables_skip_migration)) {
printf( '<font color="green">Table %s was purposefully skipped and NOT migrated to InnoDB.</font><br />', $t );
continue;
}
$res = MigrateTableEngine( $t, 'InnoDB' );
if( $res === TRUE) {
printf( '<font color="green">Table %s migrated to InnoDB.</font><br />', $t );
} else {
printf( '<font color="red">Error migrating table %s to InnoDB</font><br />', $t );
error_log( sprintf( 'Error migrating table %s to InnoDB', $t ));
}
}
}
if($skipping) echo "<font color='green'>Skipping section $line</font><br />\n";
}
else if (preg_match('/^#EndIf/', $line)) {
$skipping = false;
}
if (preg_match('/^\s*#/', $line)) continue;
if ($skipping) continue;
$query = $query . $line;
if (substr($query, -1) == ';') {
$query = rtrim($query, ';');
echo "$query<br />\n";
if (!sqlStatement($query)) {
echo "<font color='red'>The above statement failed: " .
getSqlLastError() . "<br />Upgrading will continue.<br /></font>\n";
}
$query = '';
}
}
flush();
} // end function
/**
* Upgrade or patch the database with a selected upgrade/patch file.
*
* The following "functions" within the selected file will be processed:
*
* #IfNotTable
* argument: table_name
* behavior: if the table_name does not exist, the block will be executed
*
* #IfTable
* argument: table_name
* behavior: if the table_name does exist, the block will be executed
*
* #IfColumn
* arguments: table_name colname
* behavior: if the table and column exist, the block will be executed
*
* #IfMissingColumn
* arguments: table_name colname
* behavior: if the table exists but the column does not, the block will be executed
*
* #IfNotColumnType
* arguments: table_name colname value
* 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
*
* #IfNotRow
* arguments: table_name colname value
* behavior: If the table table_name does not have a row where colname = value, the block will be executed.
*
* #IfNotRow2D
* arguments: table_name colname value colname2 value2
* behavior: If the table table_name does not have a row where colname = value AND colname2 = value2, the block will be executed.
*
* #IfNotRow3D
* arguments: table_name colname value colname2 value2 colname3 value3
* 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.
*
* #IfNotRow4D
* arguments: table_name colname value colname2 value2 colname3 value3 colname4 value4
* 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.
*
* #IfNotRow2Dx2
* 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.
* arguments: table_name colname value colname2 value2 colname3 value3
* behavior: The block will be executed if both statements below are true:
* 1) The table table_name does not have a row where colname = value AND colname2 = value2.
* 2) The table table_name does not have a row where colname = value AND colname3 = value3.
*
* #IfRow2D
* arguments: table_name colname value colname2 value2
* behavior: If the table table_name does have a row where colname = value AND colname2 = value2, the block will be executed.
*
* #IfRow3D
* arguments: table_name colname value colname2 value2 colname3 value3
* behavior: If the table table_name does have a row where colname = value AND colname2 = value2 AND colname3 = value3, the block will be executed.
*
* #IfIndex
* desc: This function is most often used for dropping of indexes/keys.
* arguments: table_name colname
* behavior: If the table and index exist the relevant statements are executed, otherwise not.
*
* #IfNotIndex
* desc: This function will allow adding of indexes/keys.
* arguments: table_name colname
* behavior: If the index does not exist, it will be created
*
* #IfNotMigrateClickOptions
* Custom function for the importing of the Clickoptions settings (if exist) from the codebase into the database
*
* #IfNotListOccupation
* Custom function for creating Occupation List
*
* #IfNotListReaction
* Custom function for creating Reaction List
*
* #IfTextNullFixNeeded
* desc: convert all text fields without default null to have default null.
* arguments: none
*
* #IfTableEngine
* desc: Execute SQL if the table has been created with given engine specified.
* arguments: table_name engine
* behavior: Use when engine conversion requires more than one ALTER TABLE
*
* #IfInnoDBMigrationNeeded
* desc: find all MyISAM tables and convert them to InnoDB.
* arguments: none
* behavior: can take a long time.
*
* #EndIf
* all blocks are terminated with a #EndIf statement.
*
* @param string $filename Sql upgrade/patch filename
*/
function upgradeFromSqlFile($filename)
{
global $webserver_root;
flush();
echo "<font color='green'>Processing {$filename} ...</font><br />\n";
$fullname = "{$webserver_root}/sql/{$filename}";
$fd = fopen($fullname, 'r');
if ($fd == FALSE) {
echo "ERROR. Could not open '{$fullname}'.\n";
flush();
return;
}
$query = "";
$line = "";
$skipping = false;
while (!feof($fd)) {
$line = fgets($fd, 2048);
$line = rtrim($line);
if (preg_match('/^\\s*--/', $line)) {
continue;
}
if ($line == "") {
continue;
}
if (preg_match('/^#IfNotTable\\s+(\\S+)/', $line, $matches)) {
$skipping = tableExists($matches[1]);
if ($skipping) {
echo "<font color='green'>Skipping section {$line}</font><br />\n";
}
} else {
if (preg_match('/^#IfTable\\s+(\\S+)/', $line, $matches)) {
$skipping = !tableExists($matches[1]);
if ($skipping) {
echo "<font color='green'>Skipping section {$line}</font><br />\n";
}
} else {
if (preg_match('/^#IfColumn\\s+(\\S+)\\s+(\\S+)/', $line, $matches)) {
if (tableExists($matches[1])) {
$skipping = !columnExists($matches[1], $matches[2]);
} else {
// If no such table then the column is deemed "missing".
$skipping = true;
}
if ($skipping) {
echo "<font color='green'>Skipping section {$line}</font><br />\n";
}
} else {
if (preg_match('/^#IfMissingColumn\\s+(\\S+)\\s+(\\S+)/', $line, $matches)) {
if (tableExists($matches[1])) {
$skipping = columnExists($matches[1], $matches[2]);
} else {
// If no such table then the column is deemed not "missing".
$skipping = true;
}
if ($skipping) {
echo "<font color='green'>Skipping section {$line}</font><br />\n";
}
} else {
if (preg_match('/^#IfNotColumnType\\s+(\\S+)\\s+(\\S+)\\s+(\\S+)/', $line, $matches)) {
if (tableExists($matches[1])) {
$skipping = columnHasType($matches[1], $matches[2], $matches[3]);
} else {
// If no such table then the column type is deemed not "missing".
$skipping = true;
}
if ($skipping) {
echo "<font color='green'>Skipping section {$line}</font><br />\n";
}
} else {
if (preg_match('/^#IfIndex\\s+(\\S+)\\s+(\\S+)/', $line, $matches)) {
if (tableExists($matches[1])) {
// If no such index then skip.
$skipping = !tableHasIndex($matches[1], $matches[2]);
} else {
// If no such table then skip.
$skipping = true;
}
if ($skipping) {
echo "<font color='green'>Skipping section {$line}</font><br />\n";
}
} else {
if (preg_match('/^#IfNotIndex\\s+(\\S+)\\s+(\\S+)/', $line, $matches)) {
if (tableExists($matches[1])) {
$skipping = tableHasIndex($matches[1], $matches[2]);
} else {
// If no such table then the index is deemed not "missing".
$skipping = true;
}
if ($skipping) {
echo "<font color='green'>Skipping section {$line}</font><br />\n";
}
} else {
if (preg_match('/^#IfNotRow\\s+(\\S+)\\s+(\\S+)\\s+(.+)/', $line, $matches)) {
if (tableExists($matches[1])) {
$skipping = tableHasRow($matches[1], $matches[2], $matches[3]);
} else {
// If no such table then the row is deemed not "missing".
$skipping = true;
}
if ($skipping) {
echo "<font color='green'>Skipping section {$line}</font><br />\n";
}
} else {
if (preg_match('/^#IfNotRow2D\\s+(\\S+)\\s+(\\S+)\\s+(\\S+)\\s+(\\S+)\\s+(.+)/', $line, $matches)) {
if (tableExists($matches[1])) {
$skipping = tableHasRow2D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5]);
} else {
// If no such table then the row is deemed not "missing".
$skipping = true;
}
if ($skipping) {
echo "<font color='green'>Skipping section {$line}</font><br />\n";
}
} else {
if (preg_match('/^#IfNotRow3D\\s+(\\S+)\\s+(\\S+)\\s+(\\S+)\\s+(\\S+)\\s+(\\S+)\\s+(\\S+)\\s+(.+)/', $line, $matches)) {
if (tableExists($matches[1])) {
$skipping = tableHasRow3D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5], $matches[6], $matches[7]);
} else {
// If no such table then the row is deemed not "missing".
$skipping = true;
}
if ($skipping) {
echo "<font color='green'>Skipping section {$line}</font><br />\n";
}
} else {
if (preg_match('/^#IfNotRow4D\\s+(\\S+)\\s+(\\S+)\\s+(\\S+)\\s+(\\S+)\\s+(\\S+)\\s+(\\S+)\\s+(\\S+)\\s+(\\S+)\\s+(.+)/', $line, $matches)) {
if (tableExists($matches[1])) {
$skipping = tableHasRow4D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5], $matches[6], $matches[7], $matches[8], $matches[9]);
} else {
// If no such table then the row is deemed not "missing".
$skipping = true;
}
if ($skipping) {
echo "<font color='green'>Skipping section {$line}</font><br />\n";
}
} else {
if (preg_match('/^#IfNotRow2Dx2\\s+(\\S+)\\s+(\\S+)\\s+(\\S+)\\s+(\\S+)\\s+(\\S+)\\s+(\\S+)\\s+(.+)/', $line, $matches)) {
if (tableExists($matches[1])) {
// If either check exist, then will skip
$firstCheck = tableHasRow2D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5]);
$secondCheck = tableHasRow2D($matches[1], $matches[2], $matches[3], $matches[6], $matches[7]);
if ($firstCheck || $secondCheck) {
$skipping = true;
} else {
$skipping = false;
}
} else {
// If no such table then the row is deemed not "missing".
$skipping = true;
}
if ($skipping) {
echo "<font color='green'>Skipping section {$line}</font><br />\n";
}
} else {
if (preg_match('/^#IfRow2D\\s+(\\S+)\\s+(\\S+)\\s+(\\S+)\\s+(\\S+)\\s+(.+)/', $line, $matches)) {
if (tableExists($matches[1])) {
$skipping = !tableHasRow2D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5]);
} else {
// If no such table then should skip.
$skipping = true;
}
if ($skipping) {
echo "<font color='green'>Skipping section {$line}</font><br />\n";
}
} else {
if (preg_match('/^#IfRow3D\\s+(\\S+)\\s+(\\S+)\\s+(\\S+)\\s+(\\S+)\\s+(\\S+)\\s+(\\S+)\\s+(.+)/', $line, $matches)) {
if (tableExists($matches[1])) {
$skipping = !tableHasRow3D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5], $matches[6], $matches[7]);
} else {
// If no such table then should skip.
$skipping = true;
}
if ($skipping) {
echo "<font color='green'>Skipping section {$line}</font><br />\n";
}
} else {
if (preg_match('/^#IfNotMigrateClickOptions/', $line)) {
if (tableExists("issue_types")) {
$skipping = true;
} else {
// Create issue_types table and import the Issue Types and clickoptions settings from codebase into the database
clickOptionsMigrate();
$skipping = false;
}
if ($skipping) {
echo "<font color='green'>Skipping section {$line}</font><br />\n";
}
} else {
if (preg_match('/^#IfNotListOccupation/', $line)) {
if (listExists("Occupation") || !columnExists('patient_data', 'occupation')) {
$skipping = true;
} else {
// Create Occupation list
CreateOccupationList();
$skipping = false;
echo "<font color='green'>Built Occupation List</font><br />\n";
}
if ($skipping) {
echo "<font color='green'>Skipping section {$line}</font><br />\n";
}
} else {
if (preg_match('/^#IfNotListReaction/', $line)) {
if (listExists("reaction") || !columnExists('lists', 'reaction')) {
$skipping = true;
} else {
// Create Reaction list
CreateReactionList();
$skipping = false;
echo "<font color='green'>Built Reaction List</font><br />\n";
}
if ($skipping) {
echo "<font color='green'>Skipping section {$line}</font><br />\n";
}
} else {
if (preg_match('/^#IfNotListImmunizationManufacturer/', $line)) {
if (listExists("Immunization_Manufacturer")) {
$skipping = true;
} else {
// Create Immunization Manufacturer list
CreateImmunizationManufacturerList();
$skipping = false;
echo "<font color='green'>Built Immunization Manufacturer List</font><br />\n";
}
if ($skipping) {
echo "<font color='green'>Skipping section {$line}</font><br />\n";
}
} else {
if (preg_match('/^#IfTextNullFixNeeded/', $line)) {
$items_to_convert = sqlStatement("SELECT `table_name`, `column_name`, `data_type`, `column_comment` " . "FROM `information_schema`.`columns` " . "WHERE (`data_type`='tinytext' OR `data_type`='text' OR `data_type`='mediumtext' OR `data_type`='longtext' ) " . "AND is_nullable='NO' AND table_schema=database()");
if (sqlNumRows($items_to_convert) == 0) {
$skipping = true;
} else {
$skipping = false;
echo '<font color="black">Starting conversion of *TEXT types to use default NULL.</font><br />', "\n";
while ($item = sqlFetchArray($items_to_convert)) {
if (!empty($item['column_comment'])) {
$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']) . "'");
} else {
$res = sqlStatement("ALTER TABLE `" . add_escape_custom($item['table_name']) . "` MODIFY `" . add_escape_custom($item['column_name']) . "` " . add_escape_custom($item['data_type']));
}
// If above query didn't work, then error will be outputted via the sqlStatement function.
echo "<font color='green'>" . text($item['table_name']) . "." . text($item['column_name']) . " sql column was successfully converted to " . text($item['data_type']) . " with default NULL setting.</font><br />\n";
}
}
if ($skipping) {
echo "<font color='green'>Skipping section {$line}</font><br />\n";
}
} else {
if (preg_match('/^#IfTableEngine\\s+(\\S+)\\s+(MyISAM|InnoDB)/', $line, $matches)) {
$skipping = !tableHasEngine($matches[1], $matches[2]);
if ($skipping) {
echo "<font color='green'>Skipping section {$line}</font><br />\n";
}
} else {
if (preg_match('/^#IfInnoDBMigrationNeeded/', $line)) {
$tables_list = getTablesList(array('engine' => 'MyISAM'));
if (count($tables_list) == 0) {
$skipping = true;
} else {
$skipping = false;
echo '<font color="black">Starting migration to InnoDB, please wait.</font><br />', "\n";
foreach ($tables_list as $k => $t) {
$res = MigrateTableEngine($t, 'InnoDB');
if ($res === TRUE) {
printf('<font color="green">Table %s migrated to InnoDB.</font><br />', $t);
} else {
printf('<font color="red">Error migrating table %s to InnoDB</font><br />', $t);
error_log(sprintf('Error migrating table %s to InnoDB', $t));
}
}
}
if ($skipping) {
echo "<font color='green'>Skipping section {$line}</font><br />\n";
}
} else {
if (preg_match('/^#EndIf/', $line)) {
$skipping = false;
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
if (preg_match('/^\\s*#/', $line)) {
continue;
}
if ($skipping) {
continue;
}
$query = $query . $line;
if (substr($query, -1) == ';') {
$query = rtrim($query, ';');
echo "{$query}<br />\n";
if (!sqlStatement($query)) {
echo "<font color='red'>The above statement failed: " . getSqlLastError() . "<br />Upgrading will continue.<br /></font>\n";
}
$query = '';
}
}
flush();
}
/**
* Upgrade or patch the database with a selected upgrade/patch file.
*
* The following "functions" within the selected file will be processed:
*
* #IfNotTable
* argument: table_name
* behavior: if the table_name does not exist, the block will be executed
*
* #IfTable
* argument: table_name
* behavior: if the table_name does exist, the block will be executed
*
* #IfMissingColumn
* arguments: table_name colname
* behavior: if the table exists but the column does not, the block will be executed
*
* #IfNotColumnType
* arguments: table_name colname value
* 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
*
* #IfNotRow
* arguments: table_name colname value
* behavior: If the table table_name does not have a row where colname = value, the block will be executed.
*
* #IfNotRow2D
* arguments: table_name colname value colname2 value2
* behavior: If the table table_name does not have a row where colname = value AND colname2 = value2, the block will be executed.
*
* #IfNotRow3D
* arguments: table_name colname value colname2 value2 colname3 value3
* 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.
*
* #IfNotRow4D
* arguments: table_name colname value colname2 value2 colname3 value3 colname4 value4
* 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.
*
* #IfNotRow2Dx2
* 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.
* arguments: table_name colname value colname2 value2 colname3 value3
* behavior: The block will be executed if both statements below are true:
* 1) The table table_name does not have a row where colname = value AND colname2 = value2.
* 2) The table table_name does not have a row where colname = value AND colname3 = value3.
*
* #IfRow2D
* arguments: table_name colname value colname2 value2
* behavior: If the table table_name does have a row where colname = value AND colname2 = value2, the block will be executed.
*
* #IfRow3D
* arguments: table_name colname value colname2 value2 colname3 value3
* behavior: If the table table_name does have a row where colname = value AND colname2 = value2 AND colname3 = value3, the block will be executed.
*
* #IfIndex
* desc: This function is most often used for dropping of indexes/keys.
* arguments: table_name colname
* behavior: If the table and index exist the relevant statements are executed, otherwise not.
*
* #IfNotIndex
* desc: This function will allow adding of indexes/keys.
* arguments: table_name colname
* behavior: If the index does not exist, it will be created
*
* #IfNotMigrateClickOptions
* Custom function for the importing of the Clickoptions settings (if exist) from the codebase into the database
*
* #IfNotListOccupation
* Custom function for creating Occupation List
*
* #IfNotListReaction
* Custom function for creating Reaction List
*
* #EndIf
* all blocks are terminated with a #EndIf statement.
*
* @param string $filename Sql upgrade/patch filename
*/
function upgradeFromSqlFile($filename)
{
global $webserver_root;
flush();
echo "<font color='green'>Processing {$filename} ...</font><br />\n";
$fullname = "{$webserver_root}/sql/{$filename}";
$fd = fopen($fullname, 'r');
if ($fd == FALSE) {
echo "ERROR. Could not open '{$fullname}'.\n";
flush();
break;
}
$query = "";
$line = "";
$skipping = false;
while (!feof($fd)) {
$line = fgets($fd, 2048);
$line = rtrim($line);
if (preg_match('/^\\s*--/', $line)) {
continue;
}
if ($line == "") {
continue;
}
if (preg_match('/^#IfNotTable\\s+(\\S+)/', $line, $matches)) {
$skipping = tableExists($matches[1]);
if ($skipping) {
echo "<font color='green'>Skipping section {$line}</font><br />\n";
}
} else {
if (preg_match('/^#IfTable\\s+(\\S+)/', $line, $matches)) {
$skipping = !tableExists($matches[1]);
if ($skipping) {
echo "<font color='green'>Skipping section {$line}</font><br />\n";
}
} else {
if (preg_match('/^#IfMissingColumn\\s+(\\S+)\\s+(\\S+)/', $line, $matches)) {
if (tableExists($matches[1])) {
$skipping = columnExists($matches[1], $matches[2]);
} else {
// If no such table then the column is deemed not "missing".
$skipping = true;
}
if ($skipping) {
echo "<font color='green'>Skipping section {$line}</font><br />\n";
}
} else {
if (preg_match('/^#IfNotColumnType\\s+(\\S+)\\s+(\\S+)\\s+(\\S+)/', $line, $matches)) {
if (tableExists($matches[1])) {
$skipping = columnHasType($matches[1], $matches[2], $matches[3]);
} else {
// If no such table then the column type is deemed not "missing".
$skipping = true;
}
if ($skipping) {
echo "<font color='green'>Skipping section {$line}</font><br />\n";
}
} else {
if (preg_match('/^#IfIndex\\s+(\\S+)\\s+(\\S+)/', $line, $matches)) {
if (tableExists($matches[1])) {
// If no such index then skip.
$skipping = !tableHasIndex($matches[1], $matches[2]);
} else {
// If no such table then skip.
$skipping = true;
}
if ($skipping) {
echo "<font color='green'>Skipping section {$line}</font><br />\n";
}
} else {
if (preg_match('/^#IfNotIndex\\s+(\\S+)\\s+(\\S+)/', $line, $matches)) {
if (tableExists($matches[1])) {
$skipping = tableHasIndex($matches[1], $matches[2]);
} else {
// If no such table then the index is deemed not "missing".
$skipping = true;
}
if ($skipping) {
echo "<font color='green'>Skipping section {$line}</font><br />\n";
}
} else {
if (preg_match('/^#IfNotRow\\s+(\\S+)\\s+(\\S+)\\s+(.+)/', $line, $matches)) {
if (tableExists($matches[1])) {
$skipping = tableHasRow($matches[1], $matches[2], $matches[3]);
} else {
// If no such table then the row is deemed not "missing".
$skipping = true;
}
if ($skipping) {
echo "<font color='green'>Skipping section {$line}</font><br />\n";
}
} else {
if (preg_match('/^#IfNotRow2D\\s+(\\S+)\\s+(\\S+)\\s+(\\S+)\\s+(\\S+)\\s+(.+)/', $line, $matches)) {
if (tableExists($matches[1])) {
$skipping = tableHasRow2D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5]);
} else {
// If no such table then the row is deemed not "missing".
$skipping = true;
}
if ($skipping) {
echo "<font color='green'>Skipping section {$line}</font><br />\n";
}
} else {
if (preg_match('/^#IfNotRow3D\\s+(\\S+)\\s+(\\S+)\\s+(\\S+)\\s+(\\S+)\\s+(\\S+)\\s+(\\S+)\\s+(.+)/', $line, $matches)) {
if (tableExists($matches[1])) {
$skipping = tableHasRow3D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5], $matches[6], $matches[7]);
} else {
// If no such table then the row is deemed not "missing".
$skipping = true;
}
if ($skipping) {
echo "<font color='green'>Skipping section {$line}</font><br />\n";
}
} else {
if (preg_match('/^#IfNotRow4D\\s+(\\S+)\\s+(\\S+)\\s+(\\S+)\\s+(\\S+)\\s+(\\S+)\\s+(\\S+)\\s+(\\S+)\\s+(\\S+)\\s+(.+)/', $line, $matches)) {
if (tableExists($matches[1])) {
$skipping = tableHasRow4D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5], $matches[6], $matches[7], $matches[8], $matches[9]);
} else {
// If no such table then the row is deemed not "missing".
$skipping = true;
}
if ($skipping) {
echo "<font color='green'>Skipping section {$line}</font><br />\n";
}
} else {
if (preg_match('/^#IfNotRow2Dx2\\s+(\\S+)\\s+(\\S+)\\s+(\\S+)\\s+(\\S+)\\s+(\\S+)\\s+(\\S+)\\s+(.+)/', $line, $matches)) {
if (tableExists($matches[1])) {
// If either check exist, then will skip
$firstCheck = tableHasRow2D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5]);
$secondCheck = tableHasRow2D($matches[1], $matches[2], $matches[3], $matches[6], $matches[7]);
if ($firstCheck || $secondCheck) {
$skipping = true;
} else {
$skipping = false;
}
} else {
// If no such table then the row is deemed not "missing".
$skipping = true;
}
if ($skipping) {
echo "<font color='green'>Skipping section {$line}</font><br />\n";
}
} else {
if (preg_match('/^#IfRow2D\\s+(\\S+)\\s+(\\S+)\\s+(\\S+)\\s+(\\S+)\\s+(.+)/', $line, $matches)) {
if (tableExists($matches[1])) {
$skipping = !tableHasRow2D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5]);
} else {
// If no such table then should skip.
$skipping = true;
}
if ($skipping) {
echo "<font color='green'>Skipping section {$line}</font><br />\n";
}
} else {
if (preg_match('/^#IfRow3D\\s+(\\S+)\\s+(\\S+)\\s+(\\S+)\\s+(\\S+)\\s+(\\S+)\\s+(\\S+)\\s+(.+)/', $line, $matches)) {
if (tableExists($matches[1])) {
$skipping = !tableHasRow3D($matches[1], $matches[2], $matches[3], $matches[4], $matches[5], $matches[6], $matches[7]);
} else {
// If no such table then should skip.
$skipping = true;
}
if ($skipping) {
echo "<font color='green'>Skipping section {$line}</font><br />\n";
}
} else {
if (preg_match('/^#IfNotMigrateClickOptions/', $line)) {
if (tableExists("issue_types")) {
$skipping = true;
} else {
// Create issue_types table and import the Issue Types and clickoptions settings from codebase into the database
clickOptionsMigrate();
$skipping = false;
}
if ($skipping) {
echo "<font color='green'>Skipping section {$line}</font><br />\n";
}
} else {
if (preg_match('/^#IfNotListOccupation/', $line)) {
if (listExists("Occupation") || !columnExists('patient_data', 'occupation')) {
$skipping = true;
} else {
// Create Occupation list
CreateOccupationList();
$skipping = false;
echo "<font color='green'>Built Occupation List</font><br />\n";
}
if ($skipping) {
echo "<font color='green'>Skipping section {$line}</font><br />\n";
}
} else {
if (preg_match('/^#IfNotListReaction/', $line)) {
if (listExists("reaction") || !columnExists('lists', 'reaction')) {
$skipping = true;
} else {
// Create Reaction list
CreateReactionList();
$skipping = false;
echo "<font color='green'>Built Reaction List</font><br />\n";
}
if ($skipping) {
echo "<font color='green'>Skipping section {$line}</font><br />\n";
}
} else {
if (preg_match('/^#EndIf/', $line)) {
$skipping = false;
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
if (preg_match('/^\\s*#/', $line)) {
continue;
}
if ($skipping) {
continue;
}
$query = $query . $line;
if (substr($query, -1) == ';') {
$query = rtrim($query, ';');
echo "{$query}<br />\n";
if (!sqlStatement($query)) {
echo "<font color='red'>The above statement failed: " . mysql_error() . "<br />Upgrading will continue.<br /></font>\n";
}
$query = '';
}
}
flush();
}