From 0f0d3c62e608287cdf77f1a3239371b521ecb40b Mon Sep 17 00:00:00 2001
From: wei <>
Date: Sat, 14 Apr 2007 05:02:29 +0000
Subject: Refactor ActiveRecordGateway to use TDataGatewayCommand

---
 framework/Data/Common/Mssql/TMssqlMetaData.php | 202 +++++++++++++++++++++++++
 1 file changed, 202 insertions(+)
 create mode 100644 framework/Data/Common/Mssql/TMssqlMetaData.php

(limited to 'framework/Data/Common/Mssql/TMssqlMetaData.php')

diff --git a/framework/Data/Common/Mssql/TMssqlMetaData.php b/framework/Data/Common/Mssql/TMssqlMetaData.php
new file mode 100644
index 00000000..c70bc349
--- /dev/null
+++ b/framework/Data/Common/Mssql/TMssqlMetaData.php
@@ -0,0 +1,202 @@
+<?php
+
+/**
+ * Load the base TDbMetaData class.
+ */
+Prado::using('System.Data.Common.TDbMetaData');
+Prado::using('System.Data.Common.Mssql.TMssqlTableInfo');
+
+class TMssqlMetaData extends TDbMetaData
+{
+	/**
+	 * 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 = <<<EOD
+				SELECT t.*,
+                         c.*,
+					columnproperty(object_id(c.table_schema + '.' + c.table_name), c.column_name,'IsIdentity') as IsIdentity
+                    FROM INFORMATION_SCHEMA.TABLES t,
+                         INFORMATION_SCHEMA.COLUMNS c
+                   WHERE t.table_name = c.table_name
+                     AND t.table_name = :table
+EOD;
+		if($schemaName!==null)
+			$sql .= ' AND t.schema_name = :schema';
+		if($catalogName!==null)
+			$sql .= ' AND t.catalog_name = :catalog';
+
+		$command = $this->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 = <<<EOD
+		SELECT k.column_name field_name
+		    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE k
+		    LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS c
+		      ON k.table_name = c.table_name
+		     AND k.constraint_name = c.constraint_name
+		   WHERE k.constraint_catalog = DB_NAME()
+		AND
+			c.constraint_type ='PRIMARY KEY'
+		    AND k.table_name = :table
+EOD;
+		$command = $this->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 = <<<EOD
+		SELECT
+		     KCU1.CONSTRAINT_NAME AS 'FK_CONSTRAINT_NAME'
+		   , KCU1.TABLE_NAME AS 'FK_TABLE_NAME'
+		   , KCU1.COLUMN_NAME AS 'FK_COLUMN_NAME'
+		   , KCU1.ORDINAL_POSITION AS 'FK_ORDINAL_POSITION'
+		   , KCU2.CONSTRAINT_NAME AS 'UQ_CONSTRAINT_NAME'
+		   , KCU2.TABLE_NAME AS 'UQ_TABLE_NAME'
+		   , KCU2.COLUMN_NAME AS 'UQ_COLUMN_NAME'
+		   , KCU2.ORDINAL_POSITION AS 'UQ_ORDINAL_POSITION'
+		FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
+		JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU1
+		ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG
+		   AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA
+		   AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
+		JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2
+		ON KCU2.CONSTRAINT_CATALOG =
+		RC.UNIQUE_CONSTRAINT_CATALOG
+		   AND KCU2.CONSTRAINT_SCHEMA =
+		RC.UNIQUE_CONSTRAINT_SCHEMA
+		   AND KCU2.CONSTRAINT_NAME =
+		RC.UNIQUE_CONSTRAINT_NAME
+		   AND KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION
+		WHERE KCU1.TABLE_NAME = :table
+EOD;
+		$command = $this->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;
+	}
+}
+
+?>
\ No newline at end of file
-- 
cgit v1.2.3