<?php
/**
* See class DocBlock
*
* @author H.F.N. den Boer <nico@denboer-ims.nl>
* @version 1.1.0
* @package nl.denboer-ims.imslib.admin
*/
global $imslib_Backbone;
// First check if access of this page is allowed
if (!isset($imslib_Backbone))
die("In order to call this file, the backbone must be included first...");
/**
* Class to automatically update the database structure based on available data.
*
* First written 25-05-2006
*
* Changelog:
* <ol>
* <li></li>
* </ol>
*
* @package nl.denboer-ims.imslib.admin
* @author H.F.N. den Boer <nico@denboer-ims.nl>
*/
class imslib_mysql_db_maint
{
/**
* Update database
*
* @param int $task
* @return unknown
*/
public function update()
{
global $imslib_Backbone;
$dname = $imslib_Backbone->database["Catalog"];
$mayDrop = true;
$scriptOnly = isset($imslib_Backbone->database["scriptOnly"]) && $imslib_Backbone->database["scriptOnly"];
/**
* First check what is now in the database,
* drop tables, indexes and fields which are obsolete,
* alter fields which are changed
*/
$dataReader = null;
$tables = array();
/* @var $dataReader imslib_dataReader */
$sql = "SHOW TABLES";
$dataReader = null;
$imslib_Backbone->getDataSet($sql, $dataReader);
while ($dataReader->hasNext())
{
$row = $dataReader->getNext();
reset($row);
$table = current($row);
$id = $imslib_Backbone->getOneRow("SELECT id FROM db_tables WHERE db_table LIKE '$table'", "id");
if (!isset($id) || (int)$id <= 0)
{
// Table does not exist in metadata
$sql = "DROP TABLE $table;";
if (!$mayDrop)
$sql = "/* $sql */";
if ($scriptOnly)
echo "<br />$sql";
elseif ($mayDrop)
$imslib_Backbone->execSql($sql);
}
else
{
$tables[$table] = array();
$tables[$table]["indexes"] = array();
}
}
reset($tables);
$value = current($tables);
$table = key($tables);
while ($table)
{
$dataReader->dispose();
$dataReader = null;
// Read indexes
$sql = "SHOW INDEXES FROM $table";
$dataReader = null;
$imslib_Backbone->getDataSet($sql, $dataReader);
while ($dataReader->hasNext())
{
$row = $dataReader->getNext();
reset($row);
$key = trim($row["Key_name"]);
$field = trim($row["Column_name"]);
$info = (int)$imslib_Backbone->getOneRow("
SELECT id FROM db_indexes
WHERE db_table LIKE '$table'
AND idx_expr LIKE '$field'", "id");
if (!isset($info) || $info <= 0)
{
// Field does not exist in metadata
$sql = "DROP INDEX $key ON $table;";
if (!$mayDrop)
$sql = "/* $sql */";
if ($scriptOnly)
echo "<br />$sql";
elseif ($mayDrop)
$imslib_Backbone->execSql($sql);
}
else
$tables[$table]["indexes"][$key]["done"] = true;
}
$tables[$table]["finished"] = true;
$value = mysql_list_fields($dname, $table); // get resource
for ($i = 0; $i < mysql_num_fields($value); $i++)
{
$decimals = 0;
$field = mysql_field_name($value, $i);
$info = $imslib_Backbone->getOneRow("
SELECT * FROM db_fields
WHERE db_table LIKE '$table'
AND db_field LIKE '$field'");
if (!isset($info))
{
// Field does not exist in metadata
$sql = "ALTER TABLE $table DROP $field;";
if (!$mayDrop)
$sql = "/* $sql */";
if ($scriptOnly)
echo "<br />$sql";
elseif ($mayDrop)
$imslib_Backbone->execSql($sql);
continue;
}
$type = mysql_field_type($value, $i);
$len = mysql_field_len($value, $i);
if (strcmp($type, "int") == 0 && (int)$len < 10)
$type = "tinyint";
elseif (strcmp($type, "string") == 0)
$type = "varchar";
elseif (strcmp($type, "real") == 0)
{
// Amount here
$len -= 2;
$decimals += 2;
$type = "numeric";
}
elseif (strcmp($type, "blob") == 0)
$type = "text";
if (strcmp($type, "int") == 0 ||
strcmp($type, "date") == 0 ||
strcmp($type, "datetime") == 0 ||
strcmp($type, "tinyint") == 0 ||
strcmp($type, "text") == 0)
{
$len = 0;
}
$tables[$table][$field] = array();
$tables[$table][$field]["finished"] = true;
// Alter table if ness.
if (strcmp($type, $dataReader->getCleanDbString($info["ftype"])) != 0 ||
(int)$len != (int)$info["width"] ||
(int)$decimals != (int)$info["decimals"])
{
$create = $this->_getFieldCreate($info);
$sql = "ALTER TABLE $table CHANGE COLUMN $field $create;";
if ($scriptOnly)
echo "<br />$sql";
else
$imslib_Backbone->execSql($sql);
}
}
$value = next($tables);
$table = key($tables);
}
$dataReader->dispose();
$dataReader = null;
require_once("class.metadata.inc.php");
/**
* Now go trough metadata,
* add new tables
*/
$metadata = new imslib_metadata();
/* @var $metadata imslib_metadata */
$meta = $metadata->getTables();
reset($meta);
$value = current($meta);
$table = key($meta);
while ($table)
{
if (isset($tables[$table]) &&
isset($tables[$table]["finished"]) &&
$tables[$table]["finished"])
{
$value = next($meta);
$table = key($meta);
continue;
}
$sql = $this->_getTableCreate($table);
if ($scriptOnly)
echo "<br /><pre>$sql</pre>";
else
$imslib_Backbone->execSql($sql);
$tables[$table] = array();
$tables[$table]["new"] = true;
$value = next($meta);
$table = key($meta);
}
unset($meta);
/**
* Now go trough metadata,
* add new fields
*/
$dataReader = null;
$imslib_Backbone->getDataSet("SELECT * FROM db_fields", $dataReader);
while ($dataReader->hasNext())
{
$row = $dataReader->getNext();
$table = $row["db_table"];
if (isset($tables[$table]["new"]) &&
$tables[$table]["new"])
{
// Full table just created
continue;
}
$field = $row["db_field"];
if (isset($tables[$table][$field]) &&
isset($tables[$table][$field]["finished"]) &&
$tables[$table][$field]["finished"]
)
{
// Field just altered
continue;
}
// Add field
$create = $this->_getFieldCreate($row);
$sql = "ALTER TABLE $table ADD COLUMN $create;";
if ($scriptOnly)
echo "<br />$sql";
else
$imslib_Backbone->execSql($sql);
}
$dataReader->dispose();
$dataReader = null;
/**
* Now go trough metadata,
* add new indexes
*/
reset($tables);
$value = current($tables);
$table = key($tables);
while ($table)
{
if (isset($tables[$table]["new"]) &&
$tables[$table]["new"])
{
// Full table just created
$value = next($tables);
$table = key($tables);
continue;
}
$imslib_Backbone->getDataSet("SELECT * FROM db_indexes WHERE db_table LIKE '$table'", $dataReader);
while ($dataReader->hasNext())
{
$row = $dataReader->getNext();
$key = $row["idx_name"];
$expr = $row["idx_expr"];
// print_r($tables[$table]["indexes"][$key]["done"]);
if (strcmp(strtolower($expr), "id") == 0)
continue;
// print_r($tables[$table]["indexes"]);
// exit();
if (isset($tables[$table]["indexes"][$key]["done"]) &&
$tables[$table]["indexes"][$key]["done"])
{
// Index already checked
continue;
}
$sql = "CREATE INDEX $key on $table ($expr);";
if ($scriptOnly)
echo "<br />$sql";
else
$imslib_Backbone->execSql($sql);
}
$dataReader->dispose();
$dataReader = null;
$value = next($tables);
$table = key($tables);
}
}
private function _getFieldCreate($row)
{
$nullValues = true;
$remarks = "";
$isPrimary = false;
$isForeignKey = false;
$decimals = $row["decimals"];
$name = $row["db_field"];
if (strcmp($name, "id") == 0)
{
$isPrimary = true;
$nullValues = false;
$remarks = "keyfield";
}
elseif (strpos($name, "_id") > 0)
{
$isForeignKey = true;
$nullValues = false;
$remarks = "foreign key";
}
if (strlen($remarks) > 0) $remarks = "($remarks)";
$type = $row["ftype"];
$len = $row["width"];
$create = "\n\t$name $type";
if (strcmp($type, "int") == 0 ||
strcmp($type, "tinyint") == 0)
{
$create .= " unsigned";
if ($isPrimary)
$create .= " auto_increment";
}
elseif (strcmp($type, "varchar") == 0)
$create .= "($len)";
if (strcmp($type, "numeric") == 0)
{
// $len += $decimals;
$create .= "($len, $decimals)";
}
if ($nullValues)
$create .= " NULL";
else
$create .= " NOT NULL";
if ($isForeignKey)
$create .= " DEFAULT '0'";
elseif (strcmp($type, "varchar") == 0)
$create .= " DEFAULT NULL";
return $create;
}
private function _getTableCreate($table)
{
global $imslib_Backbone;
$dataReader = null;
$retVal = "\nCREATE TABLE $table (";
$imslib_Backbone->getDataSet("SELECT * FROM db_fields WHERE db_table LIKE '$table'", $dataReader);
while ($dataReader->hasNext())
{
$row = $dataReader->getNext();
$retVal .= $this->_getFieldCreate($row);
if ($dataReader->hasNext())
$retVal .= ",";
}
$retVal .= ",\n\tPRIMARY KEY (id)";
$dataReader->dispose();
$dataReader = null;
$imslib_Backbone->getDataSet("SELECT * FROM db_indexes WHERE db_table LIKE '$table'", $dataReader);
while ($dataReader->hasNext())
{
$row = $dataReader->getNext();
$key = $row["idx_name"];
$expr = $row["idx_expr"];
if (strcmp(strtolower($expr), "id") == 0)
{
if ($dataReader->hasNext())
$retVal .= ",";
continue;
}
$retVal .= "\n\tKEY $key ($expr)";
if ($dataReader->hasNext())
$retVal .= ",";
}
$retVal .= "\n) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
return $retVal;
}
}
$imslib_DataMaintenance = new imslib_mysql_db_maint();
?>
|
Download
