* @link http://www.pradosoft.com/ * @copyright Copyright © 2005-2014 PradoSoft * @license http://www.pradosoft.com/license/ * @package Prado\Data\Common\Mssql */ namespace Prado\Data\Common\Mssql; /** * Load the base TDbMetaData class. */ use Prado\Data\Common\TDbMetaData; use Prado\Exceptions\TDbException; use Prado\Prado; 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 Prado\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; } }