path: root/framework/Data/Common/Mysql/TMysqlMetaData.php

<?php
/**
 * Load the base TDbMetaData class.
 */
Prado::using('System.Data.Common.TDbMetaData');
Prado::using('System.Data.Common.Mysql.TMysqlTableInfo');

class TMysqlMetaData extends TDbMetaData
{
	private $_serverVersion=0;

	/**
	 * Get the column definitions for given table.
	 * @param string table name.
	 * @return TMysqlTableInfo table information.
	 */
	protected function createTableInfo($table)
	{
		list($schemaName,$tableName) = $this->getSchemaTableName($table);
		$find = $schemaName===null ? "`{$tableName}`" : "`{$schemaName}`.`{$tableName}`";
		$this->getDbConnection()->setActive(true);
		$sql = "SHOW FULL FIELDS FROM {$find}";
		$command = $this->getDbConnection()->createCommand($sql);
		$tableInfo = $this->createNewTableInfo($table);
		$index=0;
		foreach($command->query() as $col)
		{
			$col['index'] = $index++;
			$this->processColumn($tableInfo,$col);
		}
		if($index===0)
			throw new TDbException('dbmetadata_invalid_table_view', $table);
		return $tableInfo;
	}

	/**
	 * @return float server version.
	 */
	protected function getServerVersion()
	{
		if(!$this->_serverVersion)
		{
			$version = $this->getDbConnection()->getAttribute(PDO::ATTR_SERVER_VERSION);
			$digits=array();
			preg_match('/(\d+)\.(\d+)\.(\d+)/', $version, $digits);
			$this->_serverVersion=floatval($digits[1].'.'.$digits[2].$digits[3]);
		}
		return $this->_serverVersion;
	}

	/**
	 * @param TMysqlTableInfo table information.
	 * @param array column information.
	 */
	protected function processColumn($tableInfo, $col)
	{
		$columnId = $col['Field'];

		$info['ColumnName'] = "`$columnId`"; //quote the column names!
		$info['ColumnId'] = $columnId;
		$info['ColumnIndex'] = $col['index'];
		if($col['Null']==='YES')
			$info['AllowNull'] = true;
		if(is_int(strpos(strtolower($col['Extra']), 'auto_increment')))
			$info['AutoIncrement']=true;
		if($col['Default']!=="")
			$info['DefaultValue'] = $col['Default'];

		if($col['Key']==='PRI' || in_array($columnId, $tableInfo->getPrimaryKeys()))
			$info['IsPrimaryKey'] = true;
		if($this->isForeignKeyColumn($columnId, $tableInfo))
			$info['IsForeignKey'] = true;

		$info['DbType'] = $col['Type'];
		$match=array();
		//find SET/ENUM values, column size, precision, and scale
		if(preg_match('/\((.*)\)/', $col['Type'], $match))
		{
			$info['DbType']= preg_replace('/\(.*\)/', '', $col['Type']);

			//find SET/ENUM values
			if($this->isEnumSetType($info['DbType']))
				$info['DbTypeValues'] = preg_split('/\s*,\s*|\s+/', preg_replace('/\'|"/', '', $match[1]));

			//find column size, precision and scale
			$pscale = array();
			if(preg_match('/(\d+)(?:,(\d+))?+/', $match[1], $pscale))
			{
				if($this->isPrecisionType($info['DbType']))
				{
					$info['NumericPrecision'] = intval($pscale[1]);
					if(count($pscale) > 2)
						$info['NumericScale'] = intval($pscale[2]);
				}
				else
					$info['ColumnSize'] = intval($pscale[1]);
			}
		}

		$tableInfo->Columns[$columnId] = new TMysqlTableColumn($info);
	}

	/**
	 * @return boolean true if column type if "numeric", "interval" or begins with "time".
	 */
	protected function isPrecisionType($type)
	{
		$type = strtolower(trim($type));
		return $type==='decimal' || $type==='dec'
				|| $type==='float' || $type==='double'
				|| $type==='double precision' || $type==='real';
	}

	/**
	 * @return boolean true if column type if "enum" or "set".
	 */
	protected function isEnumSetType($type)
	{
		$type = strtolower(trim($type));
		return $type==='set' || $type==='enum';
	}


	/**
	 * @param string table name, may be quoted with back-ticks and may contain database name.
	 * @return array tuple ($schema,$table), $schema may be null.
	 * @throws TDbException when table name contains invalid identifier bytes.
	 */
	protected function getSchemaTableName($table)
	{
		//remove the back ticks and separate out the "database.table"
		$result = explode('.', str_replace('`', '', $table));
		foreach($result as $name)
		{
			if(!$this->isValidIdentifier($name))
			{
				$ref = 'http://dev.mysql.com/doc/refman/5.0/en/identifiers.html';
				throw new TDbException('dbcommon_invalid_identifier_name', $table, $ref);
			}
		}
		return count($result) > 1 ? $result : array(null, $result[0]);
	}

	/**
	 * http://dev.mysql.com/doc/refman/5.0/en/identifiers.html
	 * @param string identifier name
	 * @param boolean true if valid identifier.
	 */
	protected function isValidIdentifier($name)
	{
		return !preg_match('#/|\\|.|\x00|\xFF#', $name);
	}

	/**
	 * @param string table schema name
	 * @param string table name.
	 * @return TMysqlTableInfo
	 */
	protected function createNewTableInfo($table)
	{
		list($schemaName,$tableName) = $this->getSchemaTableName($table);
		$info['SchemaName'] = $schemaName;
		$info['TableName'] = $tableName;
		if($this->getIsView($schemaName,$tableName))
			$info['IsView'] = true;
		list($primary, $foreign) = $this->getConstraintKeys($schemaName, $tableName);
		return new TMysqlTableInfo($info,$primary,$foreign);
	}

	/**
	 * For MySQL version 5.0.1 or later we can use SHOW FULL TABLES
	 * http://dev.mysql.com/doc/refman/5.0/en/show-tables.html
	 *
	 * For MySQL version 5.0.1 or ealier, this always return false.
	 * @param string database name, null to use default connection database.
	 * @param string table or view name.
	 * @return boolean true if is view, false otherwise.
	 * @throws TDbException if table or view does not exist.
	 */
	protected function getIsView($schemaName,$tableName)
	{
		if($this->getServerVersion()<5.01)
			return false;
		if($schemaName!==null)
			$sql = "SHOW FULL TABLES FROM `{$schemaName}` LIKE :table";
		else
			$sql = 'SHOW FULL TABLES LIKE :table';

		$command = $this->getDbConnection()->createCommand($sql);
		$command->bindValue(':table', $tableName);
		try
		{
			return count($result = $command->queryRow()) > 0 && $result['Table_type']==='VIEW';
		}
		catch(TDbException $e)
		{
			$table = $schemaName===null?$tableName:$schemaName.'.'.$tableName;
			throw new TDbException('dbcommon_invalid_table_name',$table,$e->getMessage());
		}
	}

	/**
	 * Gets the primary and foreign key column details for the given table.
	 * @param string schema name
	 * @param string table name.
	 * @return array tuple ($primary, $foreign)
	 */
	protected function getConstraintKeys($schemaName, $tableName)
	{
		$table = $schemaName===null ? "`{$tableName}`" : "`{$schemaName}`.`{$tableName}`";
		$sql = "SHOW INDEX FROM {$table}";
		$command = $this->getDbConnection()->createCommand($sql);
		$primary = array();
		foreach($command->query() as $row)
		{
			if($row['Key_name']==='PRIMARY')
				$primary[] = $row['Column_name'];
		}
		if($this->getServerVersion() > 5)
			$foreign = $this->getForeignConstraints($schemaName,$tableName);
		else
			$foreign = $this->findForeignConstraints($schemaName,$tableName);
		return array($primary,$foreign);
	}

	/**
	 * Gets foreign relationship constraint keys and table name
	 * @param string database name
	 * @param string table name
	 * @return array foreign relationship table name and keys.
	 */
	protected function getForeignConstraints($schemaName, $tableName)
	{
		$andSchema = $schemaName !== null ? 'AND TABLE_SCHEMA = :schema' : '';
		$sql = <<<EOD
			SELECT
				CONSTRAINT_NAME as con,
				COLUMN_NAME as col,
				REFERENCED_TABLE_SCHEMA as fkschema,
				REFERENCED_TABLE_NAME as fktable,
				REFERENCED_COLUMN_NAME as fkcol
			FROM
				 `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE`
			WHERE
				REFERENCED_TABLE_NAME IS NOT NULL
				AND TABLE_NAME = :table
				$andSchema
EOD;
		$command = $this->getDbConnection()->createCommand($sql);
		$command->bindValue(':table', $tableName);
		if($schemaName!==null)
			$command->bindValue(':schema', $schemaName);
		$fkeys=array();
		foreach($command->query() as $col)
		{
			$fkeys[$col['con']]['keys'][$col['col']] = $col['fkcol'];
			$fkeys[$col['con']]['table'] = $col['fktable'];
		}
		return count($fkeys) > 0 ? array_values($fkeys) : $fkeys;
	}

	/**
	 * @param string database name
	 * @param string table name
	 * @return string SQL command to create the table.
	 */
	protected function getShowCreateTable($schemaName, $tableName)
	{
		if($schemaName!==null)
			$sql = "SHOW CREATE TABLE `{$schemaName}`.`{$tableName}`";
		else
			$sql = "SHOW CREATE TABLE `{$tableName}`";
		$command = $this->getDbConnection()->createCommand($sql);
		$result = $command->queryRow();
		return $result['Create Table'];
	}

	/**
	 * Extract foreign key constraints by extracting the contraints from SHOW CREATE TABLE result.
	 * @param string database name
	 * @param string table name
	 * @return array foreign relationship table name and keys.
	 */
	protected function findForeignConstraints($schemaName, $tableName)
	{
		$sql = $this->getShowCreateTable($schemaName, $tableName);
		$matches =array();
		$regexp = '/FOREIGN KEY\s+\(([^\)]+)\)\s+REFERENCES\s+`?([^`]+)`?\s\(([^\)]+)\)/mi';
		preg_match_all($regexp,$sql,$matches,PREG_SET_ORDER);
		$foreign = array();
		foreach($matches as $match)
		{
			$fields = array_map('trim',explode(',',str_replace('`','',$match[1])));
			$fk_fields = array_map('trim',explode(',',str_replace('`','',$match[3])));
			$keys=array();
			foreach($fields as $k=>$v)
				$keys[$v] = $fk_fields[$k];
			$foreign[] = array('keys' => $keys, 'table' => trim($match[2]));
		}
		return $foreign;
	}

	/**
	 * @param string column name.
	 * @param TPgsqlTableInfo table information.
	 * @return boolean true if column is a foreign key.
	 */
	protected function isForeignKeyColumn($columnId, $tableInfo)
	{
		foreach($tableInfo->getForeignKeys() as $fk)
		{
			if(in_array($columnId, array_keys($fk['keys'])))
				return true;
		}
		return false;
	}
}

?>