Location: PHPKode > scripts > Database metadata > class.mysql.db.maint.inc.php
<?php
/**
 * See class DocBlock
 *
 * @author H.F.N. den Boer <hide@address.com>
 * @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 <hide@address.com>
 */
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();

?>
Return current item: Database metadata