function applyTables()
{
if (empty($this->schema['TABLE'])) {
return;
}
foreach ($this->schema['TABLE'] as $table => $columns) {
if (empty($table)) {
continue;
}
if (!DB_TableExists($table)) {
$sql = "CREATE TABLE \"{$table}\" ()";
$this->applyOrEchoOnce($sql, $stmt = __METHOD__ . $table);
}
foreach ($columns as $column => $modification) {
if ($this->currSchema['TABLE'][$table][$column]['ADD'] != $modification['ADD']) {
$rename = "";
if (DB_ColExists($table, $column)) {
/* The column exists, but it looks different!
Solution: Delete the column! */
$rename = $column . '_old';
$sql = "ALTER TABLE \"{$table}\" RENAME COLUMN \"{$column}\" TO \"{$rename}\"";
$this->applyOrEchoOnce($sql);
}
$sql = $modification['ADD'];
if ($this->debug) {
print "{$sql}\n";
} else {
// Add the new column which sets the default value
$this->dbman->queryOnce($sql);
}
if (!empty($rename)) {
/* copy over the old data */
$this->applyOrEchoOnce($sql = "UPDATE \"{$table}\" SET \"{$column}\" = \"{$rename}\"");
$this->applyOrEchoOnce($sql = "ALTER TABLE \"{$table}\" DROP COLUMN \"{$rename}\"");
}
}
if ($this->currSchema['TABLE'][$table][$column]['ALTER'] != $modification['ALTER'] && isset($modification['ALTER'])) {
$sql = $modification['ALTER'];
if ($this->debug) {
print "{$sql}\n";
} else {
if (!empty($sql)) {
$this->dbman->queryOnce($sql);
}
}
}
if ($this->currSchema['TABLE'][$table][$column]['DESC'] != $modification['DESC']) {
$sql = empty($modification['DESC']) ? "COMMENT ON COLUMN \"{$table}\".\"{$column}\" IS ''" : $modification['DESC'];
$this->applyOrEchoOnce($sql, $stmt = __METHOD__ . "{$table}.{$column}.comment");
}
}
}
}
/**
* @brief Make schema match $Filename. This is a single transaction.
* @param $Filename Schema file written by schema-export.php
* @param $Debug Turn on debugging (echo sql as it is being executed)
* @param $Catalog Optional database name
* @return false=success, on error return string with error message.
**/
function ApplySchema($Filename = NULL, $Debug = false, $Catalog = 'fossology')
{
global $PG_CONN;
if (!file_exists($Filename)) {
$ErrMsg = $Filename . " does not exist.";
return $ErrMsg;
}
require_once $Filename;
/* this will DIE if the file does not exist. */
/* Very basic sanity check (so we don't delete everything!) */
if (count($Schema['TABLE']) < 5 || count($Schema['SEQUENCE']) < 5 || count($Schema['INDEX']) < 5 || count($Schema['CONSTRAINT']) < 5) {
$ErrMsg = "Schema from '{$Filename}' appears invalid.";
return $ErrMsg;
}
/* get the current statement timeout */
$SQL = "show statement_timeout";
$result = pg_query($PG_CONN, $SQL);
DBCheckResult($result, $SQL, __FILE__, __LINE__);
$Results = pg_fetch_all($result);
$Statement_Timeout = $Results[0]['statement_timeout'];
pg_free_result($result);
pg_query($PG_CONN, "SET statement_timeout = 0;");
/* turn off DB timeouts */
pg_query($PG_CONN, "BEGIN;");
$Curr = GetSchema();
/* The gameplan: Make $Curr look like $Schema. */
// print "<pre>"; print_r($Schema); print "</pre>";
/* turn off E_NOTICE so this stops reporting undefined index */
$errlev = error_reporting(E_ERROR | E_WARNING | E_PARSE);
/************************************/
/* Add sequences */
/************************************/
if (!empty($Schema['SEQUENCE'])) {
foreach ($Schema['SEQUENCE'] as $Name => $SQL) {
if (empty($Name)) {
echo "warning empty sequence in .dat\n";
continue;
}
if ($Curr['SEQUENCE'][$Name] == $SQL) {
continue;
}
if ($Debug) {
print "{$SQL}\n";
} else {
$result = pg_query($PG_CONN, $SQL);
DBCheckResult($result, $SQL, __FILE__, __LINE__);
}
}
}
/************************************/
/* Add tables/columns (dependent on sequences for default values) */
/************************************/
if (!empty($Schema['TABLE'])) {
foreach ($Schema['TABLE'] as $Table => $Columns) {
if (empty($Table)) {
continue;
}
if (!DB_TableExists($Table)) {
$SQL = "CREATE TABLE \"{$Table}\" ();";
if ($Debug) {
print "{$SQL}\n";
} else {
$result = pg_query($PG_CONN, $SQL);
DBCheckResult($result, $SQL, __FILE__, __LINE__);
}
}
foreach ($Columns as $Column => $Val) {
if ($Curr['TABLE'][$Table][$Column]['ADD'] != $Val['ADD']) {
$Rename = "";
if (DB_ColExists($Table, $Column)) {
/* The column exists, but it looks different!
Solution: Delete the column! */
$Rename = $Column . "_old";
$SQL = "ALTER TABLE \"{$Table}\" RENAME COLUMN \"{$Column}\" TO \"{$Rename}\";";
if ($Debug) {
print "{$SQL}\n";
} else {
$result = pg_query($PG_CONN, $SQL);
DBCheckResult($result, $SQL, __FILE__, __LINE__);
}
}
$SQL = $Val['ADD'];
if ($Debug) {
print "{$SQL}\n";
} else {
// Add the new column, then set the default value with update
$result = pg_query($PG_CONN, $SQL);
DBCheckResult($result, $SQL, __FILE__, __LINE__);
if (!empty($Val['UPDATE'])) {
$SQL = $Val['UPDATE'];
$result = pg_query($PG_CONN, $SQL);
DBCheckResult($result, $SQL, __FILE__, __LINE__);
}
}
if (!empty($Rename)) {
/* copy over the old data */
$SQL = "UPDATE \"{$Table}\" SET \"{$Column}\" = \"{$Rename}\";";
if ($Debug) {
print "{$SQL}\n";
} else {
$result = pg_query($PG_CONN, $SQL);
DBCheckResult($result, $SQL, __FILE__, __LINE__);
}
$SQL = "ALTER TABLE \"{$Table}\" DROP COLUMN \"{$Rename}\";";
if ($Debug) {
print "{$SQL}\n";
} else {
$result = pg_query($PG_CONN, $SQL);
DBCheckResult($result, $SQL, __FILE__, __LINE__);
}
}
}
// if
if ($Curr['TABLE'][$Table][$Column]['ALTER'] != $Val['ALTER']) {
if ($Debug) {
print $Val['ALTER'] . "\n";
} else {
$SQL = $Val['ALTER'];
$result = pg_query($PG_CONN, $SQL);
DBCheckResult($result, $SQL, __FILE__, __LINE__);
$SQL = $Val['UPDATE'];
if (!empty($Val['UPDATE'])) {
$SQL = $Val['UPDATE'];
$result = pg_query($PG_CONN, $SQL);
DBCheckResult($result, $SQL, __FILE__, __LINE__);
}
}
}
if ($Curr['TABLE'][$Table][$Column]['DESC'] != $Val['DESC']) {
if (empty($Val['DESC'])) {
$SQL = "COMMENT ON COLUMN \"{$Table}\".\"{$Column}\" IS '';";
} else {
$SQL = $Val['DESC'];
}
if ($Debug) {
print "{$SQL}\n";
} else {
$result = pg_query($PG_CONN, $SQL);
DBCheckResult($result, $SQL, __FILE__, __LINE__);
}
}
}
}
}
/************************************/
/* Add views (dependent on columns) */
/************************************/
if (!empty($Schema['VIEW'])) {
foreach ($Schema['VIEW'] as $Name => $SQL) {
if (empty($Name)) {
continue;
}
if ($Curr['VIEW'][$Name] == $SQL) {
continue;
}
if (!empty($Curr['VIEW'][$Name])) {
/* Delete it if it exists and looks different */
$SQL1 = "DROP VIEW {$Name};";
if ($Debug) {
print "{$SQL1}\n";
} else {
$result = pg_query($PG_CONN, $SQL1);
DBCheckResult($result, $SQL1, __FILE__, __LINE__);
}
}
/* Create the view */
if ($Debug) {
print "{$SQL}\n";
} else {
$result = pg_query($PG_CONN, $SQL);
DBCheckResult($result, $SQL, __FILE__, __LINE__);
}
}
}
/************************************/
/* Delete constraints */
/* Delete now, so they won't interfere with migrations. */
/************************************/
if (!empty($Curr['CONSTRAINT'])) {
foreach ($Curr['CONSTRAINT'] as $Name => $SQL) {
if (empty($Name)) {
continue;
}
/* Only process tables that I know about */
$Table = preg_replace("/^ALTER TABLE \"(.*)\" ADD CONSTRAINT.*/", '${1}', $SQL);
$TableFk = preg_replace("/^.*FOREIGN KEY .* REFERENCES \"(.*)\" \\(.*/", '${1}', $SQL);
if ($TableFk == $SQL) {
$TableFk = $Table;
}
/* If I don't know the primary or foreign table... */
if (empty($Schema['TABLE'][$Table]) && empty($Schema['TABLE'][$TableFk])) {
continue;
}
/* If it is already set correctly, then skip it. */
if ($Schema['CONSTRAINT'][$Name] == $SQL) {
continue;
}
$SQL = "ALTER TABLE \"{$Table}\" DROP CONSTRAINT \"{$Name}\" CASCADE;";
if ($Debug) {
print "{$SQL}\n";
} else {
$result = @pg_query($PG_CONN, $SQL);
DBCheckResult($result, $SQL, __FILE__, __LINE__);
}
}
}
/* Reload current since the CASCADE may have changed things */
/************************************/
/* Delete indexes */
/************************************/
$Curr = GetSchema();
/* constraints and indexes are linked, recheck */
if (!empty($Curr['INDEX'])) {
foreach ($Curr['INDEX'] as $Table => $IndexInfo) {
if (empty($Table)) {
continue;
}
/* Only delete indexes on known tables */
if (empty($Schema['TABLE'][$Table])) {
continue;
}
foreach ($IndexInfo as $Name => $SQL) {
if (empty($Name)) {
continue;
}
/* Only delete indexes that are different */
if ($Schema['INDEX'][$Table][$Name] == $SQL) {
continue;
}
$SQL = "DROP INDEX \"{$Name}\";";
if ($Debug) {
print "{$SQL}\n";
} else {
$result = pg_query($PG_CONN, $SQL);
DBCheckResult($result, $SQL, __FILE__, __LINE__);
}
}
}
}
/************************************/
/* Add indexes (dependent on columns) */
/************************************/
if (!empty($Schema['INDEX'])) {
foreach ($Schema['INDEX'] as $Table => $IndexInfo) {
if (empty($Table)) {
continue;
}
// bobg
if (!array_key_exists($Table, $Schema["TABLE"])) {
echo "skipping orphan table: {$Table}\n";
continue;
}
foreach ($IndexInfo as $Name => $SQL) {
if (empty($Name)) {
continue;
}
if ($Curr['INDEX'][$Table][$Name] == $SQL) {
continue;
}
if ($Debug) {
print "{$SQL}\n";
} else {
$result = pg_query($PG_CONN, $SQL);
DBCheckResult($result, $SQL, __FILE__, __LINE__);
}
$SQL = "REINDEX INDEX \"{$Name}\";";
if ($Debug) {
print "{$SQL}\n";
} else {
$result = pg_query($PG_CONN, $SQL);
DBCheckResult($result, $SQL, __FILE__, __LINE__);
}
}
}
}
/************************************/
/* Add constraints (dependent on columns, views, and indexes) */
/************************************/
$Curr = GetSchema();
/* constraints and indexes are linked, recheck */
if (!empty($Schema['CONSTRAINT'])) {
/* Constraints must be added in the correct order! */
/* CONSTRAINT: PRIMARY KEY */
foreach ($Schema['CONSTRAINT'] as $Name => $SQL) {
if (empty($Name)) {
continue;
}
if ($Curr['CONSTRAINT'][$Name] == $SQL) {
continue;
}
if (!preg_match("/PRIMARY KEY/", $SQL)) {
continue;
}
if ($Debug) {
print "{$SQL}\n";
} else {
$result = pg_query($PG_CONN, $SQL);
DBCheckResult($result, $SQL, __FILE__, __LINE__);
}
}
/* CONSTRAINT: UNIQUE */
foreach ($Schema['CONSTRAINT'] as $Name => $SQL) {
if (empty($Name)) {
continue;
}
if ($Curr['CONSTRAINT'][$Name] == $SQL) {
continue;
}
if (!preg_match("/UNIQUE/", $SQL)) {
continue;
}
if ($Debug) {
print "{$SQL}\n";
} else {
$result = pg_query($PG_CONN, $SQL);
DBCheckResult($result, $SQL, __FILE__, __LINE__);
}
}
/* CONSTRAINT: FOREIGN KEY */
foreach ($Schema['CONSTRAINT'] as $Name => $SQL) {
if (empty($Name)) {
continue;
}
if ($Curr['CONSTRAINT'][$Name] == $SQL) {
continue;
}
if (!preg_match("/FOREIGN KEY/", $SQL)) {
continue;
}
if ($Debug) {
print "{$SQL}\n";
} else {
$result = pg_query($PG_CONN, $SQL);
DBCheckResult($result, $SQL, __FILE__, __LINE__);
}
}
/* All other constraints */
foreach ($Schema['CONSTRAINT'] as $Name => $SQL) {
if (empty($Name)) {
continue;
}
if ($Curr['CONSTRAINT'][$Name] == $SQL) {
continue;
}
if (preg_match("/PRIMARY KEY/", $SQL)) {
continue;
}
if (preg_match("/UNIQUE/", $SQL)) {
continue;
}
if (preg_match("/FOREIGN KEY/", $SQL)) {
continue;
}
if ($Debug) {
print "{$SQL}\n";
} else {
$result = pg_query($PG_CONN, $SQL);
DBCheckResult($result, $SQL, __FILE__, __LINE__);
}
}
}
/* Add constraints */
error_reporting($errlev);
/* return to previous error reporting level */
/************************************/
/* CREATE FUNCTIONS */
/************************************/
MakeFunctions($Debug);
/* Reload current since CASCADE during migration may have changed things */
$Curr = GetSchema();
/************************************/
/* Delete views */
/************************************/
print " Removing obsolete views\n";
flush();
/* Get current tables and columns used by all views */
/* Delete if: uses table I know and column I do not know. */
/* Without this delete, we won't be able to drop columns. */
$SQL = "SELECT view_name,table_name,column_name\n FROM information_schema.view_column_usage\n WHERE table_catalog='{$Catalog}'\n ORDER BY view_name,table_name,column_name;";
$result = pg_query($PG_CONN, $SQL);
DBCheckResult($result, $SQL, __FILE__, __LINE__);
$Results = pg_fetch_all($result);
for ($i = 0; !empty($Results[$i]['view_name']); $i++) {
$View = $Results[$i]['view_name'];
$Table = $Results[$i]['table_name'];
if (empty($Schema['TABLE'][$Table])) {
continue;
}
$Column = $Results[$i]['column_name'];
if (empty($Schema['TABLE'][$Table][$Column])) {
$SQL = "DROP VIEW \"{$View}\";";
if ($Debug) {
print "{$SQL}\n";
} else {
$results = pg_query($PG_CONN, $SQL);
DBCheckResult($results, $SQL, __FILE__, __LINE__);
}
}
}
/************************************/
/* Delete columns/tables */
/************************************/
print " Removing obsolete columns\n";
flush();
if (!empty($Curr['TABLE'])) {
foreach ($Curr['TABLE'] as $Table => $Columns) {
if (empty($Table)) {
continue;
}
/* only delete from tables I know */
if (empty($Schema['TABLE'][$Table])) {
continue;
}
foreach ($Columns as $Column => $Val) {
if (empty($Column)) {
continue;
}
if (empty($Schema['TABLE'][$Table][$Column])) {
$SQL = "ALTER TABLE \"{$Table}\" DROP COLUMN \"{$Column}\";";
if ($Debug) {
print "{$SQL}\n";
} else {
$results = pg_query($PG_CONN, $SQL);
DBCheckResult($results, $SQL, __FILE__, __LINE__);
}
}
}
}
}
/************************************/
/* Commit changes */
/************************************/
$results = pg_query($PG_CONN, "COMMIT;");
DBCheckResult($results, $SQL, __FILE__, __LINE__);
/************************************/
/* Flush any cached data. */
/************************************/
ReportCachePurgeAll();
/* restore DB timeouts */
pg_query($PG_CONN, "SET statement_timeout = {$Statement_Timeout};");
return false;
}
