/**
* 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
Beispiel #2
0
/**
* 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();
}
Beispiel #3
0
/**
* 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();
}