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); } return $tableInfo; } 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); return new TMssqlTableInfo($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'] = "{$catalogSchema}.[{$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; } } ?>