From 6f7fdef0f500cd4bb540affd3bc1482243f337c1 Mon Sep 17 00:00:00 2001 From: emkael Date: Wed, 24 Feb 2016 23:18:07 +0100 Subject: * Prado 3.3.0 --- .../framework/Data/Common/Mssql/TMssqlMetaData.php | 290 +++++++++++++++++++++ 1 file changed, 290 insertions(+) create mode 100644 lib/prado/framework/Data/Common/Mssql/TMssqlMetaData.php (limited to 'lib/prado/framework/Data/Common/Mssql/TMssqlMetaData.php') diff --git a/lib/prado/framework/Data/Common/Mssql/TMssqlMetaData.php b/lib/prado/framework/Data/Common/Mssql/TMssqlMetaData.php new file mode 100644 index 0000000..d0fc167 --- /dev/null +++ b/lib/prado/framework/Data/Common/Mssql/TMssqlMetaData.php @@ -0,0 +1,290 @@ + + * @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.Mssql + */ + +/** + * Load the base TDbMetaData class. + */ +Prado::using('System.Data.Common.TDbMetaData'); +Prado::using('System.Data.Common.Mssql.TMssqlTableInfo'); + +/** + * TMssqlMetaData loads MSSQL database table and column information. + * + * @author Wei Zhuo + * @package System.Data.Common.Mssql + * @since 3.1 + */ +class TMssqlMetaData extends TDbMetaData +{ + /** + * @return string TDbTableInfo class name. + */ + protected function getTableInfoClass() + { + return 'TMssqlTableInfo'; + } + + /** + * 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 TMssqlTableInfo table information. + */ + protected function createTableInfo($table) + { + list($catalogName,$schemaName,$tableName) = $this->getCatalogSchemaTableName($table); + $this->getDbConnection()->setActive(true); + $sql = <<getDbConnection()->createCommand($sql); + $command->bindValue(':table', $tableName); + if($schemaName!==null) + $command->bindValue(':schema', $schemaName); + if($catalogName!==null) + $command->bindValue(':catalog', $catalogName); + + $tableInfo=null; + foreach($command->query() as $col) + { + if($tableInfo===null) + $tableInfo = $this->createNewTableInfo($col); + $this->processColumn($tableInfo,$col); + } + if($tableInfo===null) + throw new TDbException('dbmetadata_invalid_table_view', $table); + return $tableInfo; + } + + /** + * @param string table name + * @return array tuple($catalogName,$schemaName,$tableName) + */ + protected function getCatalogSchemaTableName($table) + { + //remove possible delimiters + $result = explode('.', preg_replace('/\[|\]|"/', '', $table)); + if(count($result)===1) + return array(null,null,$result[0]); + if(count($result)===2) + return array(null,$result[0],$result[1]); + if(count($result)>2) + return array($result[0],$result[1],$result[2]); + } + + /** + * @param TMssqlTableInfo table information. + * @param array column information. + */ + protected function processColumn($tableInfo, $col) + { + $columnId = $col['COLUMN_NAME']; + + $info['ColumnName'] = "[$columnId]"; //quote the column names! + $info['ColumnId'] = $columnId; + $info['ColumnIndex'] = intval($col['ORDINAL_POSITION'])-1; //zero-based index + if($col['IS_NULLABLE']!=='NO') + $info['AllowNull'] = true; + if($col['COLUMN_DEFAULT']!==null) + $info['DefaultValue'] = $col['COLUMN_DEFAULT']; + + if(in_array($columnId, $tableInfo->getPrimaryKeys())) + $info['IsPrimaryKey'] = true; + if($this->isForeignKeyColumn($columnId, $tableInfo)) + $info['IsForeignKey'] = true; + + if($col['IsIdentity']==='1') + $info['AutoIncrement'] = true; + $info['DbType'] = $col['DATA_TYPE']; + if($col['CHARACTER_MAXIMUM_LENGTH']!==null) + $info['ColumnSize'] = intval($col['CHARACTER_MAXIMUM_LENGTH']); + if($col['NUMERIC_PRECISION'] !== null) + $info['NumericPrecision'] = intval($col['NUMERIC_PRECISION']); + if($col['NUMERIC_SCALE']!==null) + $info['NumericScale'] = intval($col['NUMERIC_SCALE']); + $tableInfo->Columns[$columnId] = new TMssqlTableColumn($info); + } + + /** + * @param string table schema name + * @param string table name. + * @return TMssqlTableInfo + */ + protected function createNewTableInfo($col) + { + $info['CatalogName'] = $col['TABLE_CATALOG']; + $info['SchemaName'] = $col['TABLE_SCHEMA']; + $info['TableName'] = $col['TABLE_NAME']; + if($col['TABLE_TYPE']==='VIEW') + $info['IsView'] = true; + list($primary, $foreign) = $this->getConstraintKeys($col); + $class = $this->getTableInfoClass(); + return new $class($info,$primary,$foreign); + } + + /** + * 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($col) + { + $sql = <<getDbConnection()->createCommand($sql); + $command->bindValue(':table', $col['TABLE_NAME']); + $primary = array(); + foreach($command->query()->readAll() as $field) + $primary[] = $field['field_name']; + $foreign = $this->getForeignConstraints($col); + 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($col) + { + //From http://msdn2.microsoft.com/en-us/library/aa175805(SQL.80).aspx + $sql = <<getDbConnection()->createCommand($sql); + $command->bindValue(':table', $col['TABLE_NAME']); + $fkeys=array(); + $catalogSchema = "[{$col['TABLE_CATALOG']}].[{$col['TABLE_SCHEMA']}]"; + foreach($command->query() as $info) + { + $fkeys[$info['FK_CONSTRAINT_NAME']]['keys'][$info['FK_COLUMN_NAME']] = $info['UQ_COLUMN_NAME']; + $fkeys[$info['FK_CONSTRAINT_NAME']]['table'] = $info['UQ_TABLE_NAME']; + } + return count($fkeys) > 0 ? array_values($fkeys) : $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; + } + + /** + * 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='dbo') + { + $condition="TABLE_TYPE='BASE TABLE'"; + $sql=<<getDbConnection()->createCommand($sql); + $command->bindParam(":schema", $schema); + $rows=$command->queryAll(); + $names=array(); + foreach ($rows as $row) + { + if ($schema == self::DEFAULT_SCHEMA) + $names[]=$row['TABLE_NAME']; + else + $names[]=$schema.'.'.$row['TABLE_SCHEMA'].'.'.$row['TABLE_NAME']; + } + + return $names; + } +} + -- cgit v1.2.3