diff options
Diffstat (limited to 'framework/Data/Common/Mysql')
| -rw-r--r-- | framework/Data/Common/Mysql/TMysqlMetaData.php | 251 | ||||
| -rw-r--r-- | framework/Data/Common/Mysql/TMysqlTableColumn.php | 69 | ||||
| -rw-r--r-- | framework/Data/Common/Mysql/TMysqlTableInfo.php | 46 |
3 files changed, 366 insertions, 0 deletions
diff --git a/framework/Data/Common/Mysql/TMysqlMetaData.php b/framework/Data/Common/Mysql/TMysqlMetaData.php new file mode 100644 index 00000000..ee3bd2e0 --- /dev/null +++ b/framework/Data/Common/Mysql/TMysqlMetaData.php @@ -0,0 +1,251 @@ +<?php +
+/**
+ * Load the base TDbMetaData class.
+ */
+Prado::using('System.Data.Common.TDbMetaData');
+Prado::using('System.Data.Common.Mysql.TMysqlTableInfo');
+
+class TMysqlMetaData extends TDbMetaData
+{
+ /**
+ * Get the column definitions for given table.
+ * @param string table name.
+ * @return TMysqlTableInfo table information.
+ */
+ protected function createTableInfo($table)
+ {
+ $this->getDbConnection()->setActive(true);
+ $sql = "SHOW FULL FIELDS FROM {$table}";
+ $command = $this->getDbConnection()->createCommand($sql);
+ $tableInfo = $this->createNewTableInfo($table);
+ $index=0;
+ foreach($command->query() as $col)
+ {
+ $col['index'] = $index++;
+ $this->processColumn($tableInfo,$col);
+ }
+ return $tableInfo;
+ }
+
+ /**
+ * @param TMysqlTableInfo table information.
+ * @param array column information.
+ */
+ protected function processColumn($tableInfo, $col)
+ {
+ $columnId = $col['Field'];
+
+ $info['ColumnName'] = "`$columnId`"; //quote the column names!
+ $info['ColumnIndex'] = $col['index'];
+ if($col['Null']!=='NO')
+ $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;
+ if(in_array($columnId, $tableInfo->getUniqueKeys()))
+ $info['IsUnique'] = 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 unknown_type $name
+ */
+ 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;
+ $info['IsView'] = $this->getIsView($schemaName,$tableName);
+ list($primary, $foreign, $unique) = $this->getConstraintKeys($schemaName, $tableName);
+ return new TMysqlTableInfo($info,$primary,$foreign, $unique);
+ }
+
+ /**
+ * @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($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, foreign key, and unique column details for the given table.
+ * @param string schema name
+ * @param string table name.
+ * @return array tuple ($primary, $foreign, $unique)
+ */
+ protected function getConstraintKeys($schemaName, $tableName)
+ {
+ $table = $schemaName===null ? "`{$tableName}`" : "`{$schemaName}`.`{$tableName}`";
+ $sql = "SHOW INDEX FROM {$table}";
+ $command = $this->getDbConnection()->createCommand($sql);
+ $primary = array();
+ $foreign = $this->getForeignConstraints($schemaName,$tableName);
+ $unique = array();
+ foreach($command->query() as $row)
+ {
+ if($row['Key_name']==='PRIMARY')
+ $primary[] = $row['Column_name'];
+ else if(intval($row['Non_unique'])===0)
+ $unique[] = $row['Column_name'];
+ }
+ return array($primary,$foreign,$unique);
+ }
+
+ /**
+ * 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['fkschema']}`.`{$col['fktable']}`";
+ }
+ return array_values($fkeys);
+ }
+
+ /**
+ * @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;
+ }
+}
+ +?>
\ No newline at end of file diff --git a/framework/Data/Common/Mysql/TMysqlTableColumn.php b/framework/Data/Common/Mysql/TMysqlTableColumn.php new file mode 100644 index 00000000..0f013acd --- /dev/null +++ b/framework/Data/Common/Mysql/TMysqlTableColumn.php @@ -0,0 +1,69 @@ +<?php
+/**
+ * TMysqlTableColumn class file.
+ *
+ * @author Wei Zhuo <weizhuo[at]gmail[dot]com>
+ * @link http://www.pradosoft.com/
+ * @copyright Copyright © 2005-2007 PradoSoft
+ * @license http://www.pradosoft.com/license/
+ * @version $Id$
+ * @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>
+ * @version $Id$
+ * @package System.Data.Common.Mysql
+ * @since 3.1
+ */
+class TMysqlTableColumn extends TDbTableColumn
+{
+ /**
+ * @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 getHasSequence()
+ {
+ return $this->getAutoIncrement();
+ }
+
+ public function getDbTypeValues()
+ {
+ return $this->getInfo('DbTypeValues');
+ }
+
+ /**
+ * Overrides parent implementation, returns PHP type from the db type.
+ * @return boolean derived PHP primitive type from the column db type.
+ */
+ public function getPHPType()
+ {
+ switch(strtolower($this->getDbType()))
+ {
+ case 'bit': case 'bit varying': case 'real': case 'serial': case 'int': case 'integer':
+ return 'integer';
+ case 'boolean':
+ return 'boolean';
+ case 'bigint': case 'bigserial': case 'double precision': case 'money': case 'numeric':
+ return 'float';
+ default:
+ return 'string';
+ }
+ }
+}
+
+?>
\ No newline at end of file diff --git a/framework/Data/Common/Mysql/TMysqlTableInfo.php b/framework/Data/Common/Mysql/TMysqlTableInfo.php new file mode 100644 index 00000000..e8585730 --- /dev/null +++ b/framework/Data/Common/Mysql/TMysqlTableInfo.php @@ -0,0 +1,46 @@ +<?php
+/**
+ * TMysqlTableInfo class file.
+ *
+ * @author Wei Zhuo <weizhuo[at]gmail[dot]com>
+ * @link http://www.pradosoft.com/
+ * @copyright Copyright © 2005-2007 PradoSoft
+ * @license http://www.pradosoft.com/license/
+ * @version $Id$
+ * @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>
+ * @version $Id$
+ * @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()
+ {
+ return '`'.$this->getSchemaName().'`.`'.$this->getTableName().'`';
+ }
+}
+
+?>
\ No newline at end of file |