Subversion-Projekte lars-tiefland.prado

<?php

/**

 * TMysqlMetaData class file.

 *

 * @author Wei Zhuo <weizhuo[at]gmail[dot]com>

 * @link http://www.pradosoft.com/

 * @copyright Copyright © 2005-2008 PradoSoft

 * @license http://www.pradosoft.com/license/

 * @version $Id: TMysqlMetaData.php 2541 2008-10-21 15:05:13Z qiang.xue $

 * @package System.Data.Common.Mysql

 */

/**

 * Load the base TDbMetaData class.

 */

Prado::using('System.Data.Common.TDbMetaData');

Prado::using('System.Data.Common.Mysql.TMysqlTableInfo');

/**

 * TMysqlMetaData loads Mysql version 4.1.x and 5.x database table and column information.

 *

 * For Mysql version 4.1.x, PHP 5.1.3 or later is required.

 * See http://netevil.org/node.php?nid=795&SC=1

 *

 * @author Wei Zhuo <weizho[at]gmail[dot]com>

 * @version $Id: TMysqlMetaData.php 2541 2008-10-21 15:05:13Z qiang.xue $

 * @package System.Data.Commom.Sqlite

 * @since 3.1

 */

class TMysqlMetaData extends TDbMetaData

{

	private $_serverVersion=0;

	/**

	 * @return string TDbTableInfo class name.

	 */

	protected function getTableInfoClass()

	{

		return 'TMysqlTableInfo';

	}

	/**

	 * 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);

		$class = $this->getTableInfoClass();

		return new $class($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'];

		}

        // MySQL version was increased to >=5.1.21 instead of 5.x

        // due to a MySQL bug (http://bugs.mysql.com/bug.php?id=19588)

		if($this->getServerVersion() >= 5.121)

			$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.

	 * @throws TDbException if PHP version is less than 5.1.3

	 */

	protected function getShowCreateTable($schemaName, $tableName)

	{

		if(version_compare(PHP_VERSION,'5.1.3','<'))

			throw new TDbException('dbmetadata_requires_php_version', 'Mysql 4.1.x', '5.1.3');

		//See http://netevil.org/node.php?nid=795&SC=1

		$this->getDbConnection()->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);

		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;

	}

}