diff options
Diffstat (limited to 'lib/prado/framework/Data/Common/Mysql')
4 files changed, 555 insertions, 0 deletions
diff --git a/lib/prado/framework/Data/Common/Mysql/TMysqlCommandBuilder.php b/lib/prado/framework/Data/Common/Mysql/TMysqlCommandBuilder.php new file mode 100644 index 0000000..b5bdc28 --- /dev/null +++ b/lib/prado/framework/Data/Common/Mysql/TMysqlCommandBuilder.php @@ -0,0 +1,24 @@ +<?php +/** + * TMysqlCommandBuilder class file. + * + * @author Wei Zhuo <weizhuo[at]gmail[dot]com> + * @link https://github.com/pradosoft/prado + * @copyright Copyright © 2005-2015 The PRADO Group + * @license https://github.com/pradosoft/prado/blob/master/COPYRIGHT + * @package System.Data.Common + */ + +Prado::using('System.Data.Common.TDbCommandBuilder'); + +/** + * TMysqlCommandBuilder implements default TDbCommandBuilder + * + * @author Wei Zhuo <weizho[at]gmail[dot]com> + * @package System.Data.Common + * @since 3.1 + */ +class TMysqlCommandBuilder extends TDbCommandBuilder +{ +} + diff --git a/lib/prado/framework/Data/Common/Mysql/TMysqlMetaData.php b/lib/prado/framework/Data/Common/Mysql/TMysqlMetaData.php new file mode 100644 index 0000000..e7747b7 --- /dev/null +++ b/lib/prado/framework/Data/Common/Mysql/TMysqlMetaData.php @@ -0,0 +1,405 @@ +<?php +/** + * TMysqlMetaData class file. + * + * @author Wei Zhuo <weizhuo[at]gmail[dot]com> + * @link https://github.com/pradosoft/prado + * @copyright Copyright © 2005-2015 The PRADO Group + * @license https://github.com/pradosoft/prado/blob/master/COPYRIGHT + * @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> + * @package System.Data.Common.Mysql + * @since 3.1 + */ +class TMysqlMetaData extends TDbMetaData +{ + private $_serverVersion=0; + + /** + * @return string TDbTableInfo class name. + */ + protected function getTableInfoClass() + { + return 'TMysqlTableInfo'; + } + + /** + * Quotes a table name for use in a query. + * @param string $name table name + * @return string the properly quoted table name + */ + public function quoteTableName($name) + { + return parent::quoteTableName($name, '`', '`'); + } + + /** + * Quotes a column name for use in a query. + * @param string $name column name + * @return string the properly quoted column name + */ + public function quoteColumnName($name) + { + return parent::quoteColumnName($name, '`', '`'); + } + + /** + * Quotes a column alias for use in a query. + * @param string $name column alias + * @return string the properly quoted column alias + */ + public function quoteColumnAlias($name) + { + return parent::quoteColumnAlias($name, '`', '`'); + } + + /** + * 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}`"; + $colCase = $this->getDbConnection()->getColumnCase(); + if($colCase != TDbColumnCaseMode::Preserved) + $this->getDbConnection()->setColumnCase('Preserved'); + $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); + if($colCase != TDbColumnCaseMode::Preserved) + $this->getDbConnection()->setColumnCase($colCase); + 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", $match[1], -1, PREG_SPLIT_NO_EMPTY); + + //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 LIKE :schema' : 'AND TABLE_SCHEMA LIKE DATABASE()'; + $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 LIKE :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 isset($result['Create Table']) ? $result['Create Table'] : (isset($result['Create View']) ? $result['Create View'] : ''); + } + + /** + * 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; + } + + /** + * Returns all table names in the database. + * @param string $schema the schema of the tables. Defaults to empty string, meaning the current or default schema. + * If not empty, the returned table names will be prefixed with the schema name. + * @return array all table names in the database. + */ + public function findTableNames($schema='') + { + if($schema==='') + return $this->getDbConnection()->createCommand('SHOW TABLES')->queryColumn(); + $names=$this->getDbConnection()->createCommand('SHOW TABLES FROM '.$this->quoteTableName($schema))->queryColumn(); + foreach($names as &$name) + $name=$schema.'.'.$name; + return $names; + } +} + diff --git a/lib/prado/framework/Data/Common/Mysql/TMysqlTableColumn.php b/lib/prado/framework/Data/Common/Mysql/TMysqlTableColumn.php new file mode 100644 index 0000000..5b84f80 --- /dev/null +++ b/lib/prado/framework/Data/Common/Mysql/TMysqlTableColumn.php @@ -0,0 +1,70 @@ +<?php +/** + * TMysqlTableColumn class file. + * + * @author Wei Zhuo <weizhuo[at]gmail[dot]com> + * @link https://github.com/pradosoft/prado + * @copyright Copyright © 2005-2015 The PRADO Group + * @license https://github.com/pradosoft/prado/blob/master/COPYRIGHT + * @package System.Data.Common.Mysql + */ + +/** + * Load common TDbTableCommon class. + */ +Prado::using('System.Data.Common.TDbTableColumn'); + +/** + * Describes the column metadata of the schema for a Mysql database table. + * + * @author Wei Zhuo <weizho[at]gmail[dot]com> + * @package System.Data.Common.Mysql + * @since 3.1 + */ +class TMysqlTableColumn extends TDbTableColumn +{ + private static $types = array( + 'integer' => array('bit', 'tinyint', 'smallint', 'mediumint', 'int', 'integer', 'bigint'), + 'boolean' => array('boolean', 'bool'), + 'float' => array('float', 'double', 'double precision', 'decimal', 'dec', 'numeric', 'fixed') + ); + + /** + * Overrides parent implementation, returns PHP type from the db type. + * @return boolean derived PHP primitive type from the column db type. + */ + public function getPHPType() + { + $dbtype = trim(str_replace(array('unsigned', 'zerofill'),array('','',),strtolower($this->getDbType()))); + if($dbtype==='tinyint' && $this->getColumnSize()===1) + return 'boolean'; + foreach(self::$types as $type => $dbtypes) + { + if(in_array($dbtype, $dbtypes)) + return $type; + } + return 'string'; + } + + /** + * @return boolean true if column will auto-increment when the column value is inserted as null. + */ + public function getAutoIncrement() + { + return $this->getInfo('AutoIncrement', false); + } + + /** + * @return boolean true if auto increment is true. + */ + public function hasSequence() + { + return $this->getAutoIncrement(); + } + + public function getDbTypeValues() + { + return $this->getInfo('DbTypeValues'); + } +} + diff --git a/lib/prado/framework/Data/Common/Mysql/TMysqlTableInfo.php b/lib/prado/framework/Data/Common/Mysql/TMysqlTableInfo.php new file mode 100644 index 0000000..bc5dc4e --- /dev/null +++ b/lib/prado/framework/Data/Common/Mysql/TMysqlTableInfo.php @@ -0,0 +1,56 @@ +<?php +/** + * TMysqlTableInfo class file. + * + * @author Wei Zhuo <weizhuo[at]gmail[dot]com> + * @link https://github.com/pradosoft/prado + * @copyright Copyright © 2005-2015 The PRADO Group + * @license https://github.com/pradosoft/prado/blob/master/COPYRIGHT + * @package System.Data.Common.Mysql + */ + +/** + * Loads the base TDbTableInfo class and TMysqlTableColumn class. + */ +Prado::using('System.Data.Common.TDbTableInfo'); +Prado::using('System.Data.Common.Mysql.TMysqlTableColumn'); + +/** + * TMysqlTableInfo class provides additional table information for MySQL database. + * + * @author Wei Zhuo <weizho[at]gmail[dot]com> + * @package System.Data.Common.Mysql + * @since 3.1 + */ +class TMysqlTableInfo extends TDbTableInfo +{ + /** + * @return string name of the schema this column belongs to. + */ + public function getSchemaName() + { + return $this->getInfo('SchemaName'); + } + + /** + * @return string full name of the table, database dependent. + */ + public function getTableFullName() + { + if(($schema=$this->getSchemaName())!==null) + return '`'.$schema.'`.`'.$this->getTableName().'`'; + else + return '`'.$this->getTableName().'`'; + } + + /** + * @param TDbConnection database connection. + * @return TDbCommandBuilder new command builder + */ + public function createCommandBuilder($connection) + { + Prado::using('System.Data.Common.Mysql.TMysqlCommandBuilder'); + return new TMysqlCommandBuilder($connection,$this); + } +} + |