* Prado 3.3.0

4 files changed, 585 insertions, 0 deletions

diff --git a/lib/prado/framework/Data/Common/Mssql/TMssqlCommandBuilder.php b/lib/prado/framework/Data/Common/Mssql/TMssqlCommandBuilder.php
new file mode 100644
index 0000000..e3f1f5c
--- /dev/null
+++ b/lib/prado/framework/Data/Common/Mssql/TMssqlCommandBuilder.php
@@ -0,0 +1,171 @@
+<?php
+/**
+ * TMsssqlCommandBuilder class file.
+ *
+ * @author Wei Zhuo <weizhuo[at]gmail[dot]com>
+ * @link https://github.com/pradosoft/prado
+ * @copyright Copyright &copy; 2005-2015 The PRADO Group
+ * @license https://github.com/pradosoft/prado/blob/master/COPYRIGHT
+ * @package System.Data.Common
+ */
+
+Prado::using('System.Data.Common.TDbCommandBuilder');
+
+/**
+ * TMssqlCommandBuilder provides specifics methods to create limit/offset query commands
+ * for MSSQL servers.
+ *
+ * @author Wei Zhuo <weizho[at]gmail[dot]com>
+ * @package System.Data.Common
+ * @since 3.1
+ */
+class TMssqlCommandBuilder extends TDbCommandBuilder
+{
+	/**
+	 * Overrides parent implementation. Uses "SELECT @@Identity".
+	 * @return integer last insert id, null if none is found.
+	 */
+	public function getLastInsertID()
+	{
+		foreach($this->getTableInfo()->getColumns() as $column)
+		{
+			if($column->hasSequence())
+			{
+				$command = $this->getDbConnection()->createCommand('SELECT @@Identity');
+				return intval($command->queryScalar());
+			}
+		}
+	}
+
+	/**
+	 * Overrides parent implementation. Alters the sql to apply $limit and $offset.
+	 * The idea for limit with offset is done by modifying the sql on the fly
+	 * with numerous assumptions on the structure of the sql string.
+	 * The modification is done with reference to the notes from
+	 * http://troels.arvin.dk/db/rdbms/#select-limit-offset
+	 *
+	 * <code>
+	 * SELECT * FROM (
+	 *  SELECT TOP n * FROM (
+	 *    SELECT TOP z columns      -- (z=n+skip)
+	 *    FROM tablename
+	 *    ORDER BY key ASC
+	 *  ) AS FOO ORDER BY key DESC -- ('FOO' may be anything)
+	 * ) AS BAR ORDER BY key ASC    -- ('BAR' may be anything)
+	 * </code>
+	 *
+	 * <b>Regular expressions are used to alter the SQL query. The resulting SQL query
+	 * may be malformed for complex queries.</b> The following restrictions apply
+	 *
+	 * <ul>
+	 *   <li>
+	 * In particular, <b>commas</b> should <b>NOT</b>
+	 * be used as part of the ordering expression or identifier. Commas must only be
+	 * used for separating the ordering clauses.
+	 *  </li>
+	 *  <li>
+	 * In the ORDER BY clause, the column name should NOT be be qualified
+	 * with a table name or view name. Alias the column names or use column index.
+	 * </li>
+	 * <li>
+	 * No clauses should follow the ORDER BY clause, e.g. no COMPUTE or FOR clauses.
+	 * </li>
+	 * </ul>
+	 *
+	 * @param string SQL query string.
+	 * @param integer maximum number of rows, -1 to ignore limit.
+	 * @param integer row offset, -1 to ignore offset.
+	 * @return string SQL with limit and offset.
+	 */
+	public function applyLimitOffset($sql, $limit=-1, $offset=-1)
+	{
+		$limit = $limit!==null ? intval($limit) : -1;
+		$offset = $offset!==null ? intval($offset) : -1;
+		if ($limit > 0 && $offset <= 0) //just limit
+			$sql = preg_replace('/^([\s(])*SELECT( DISTINCT)?(?!\s*TOP\s*\()/i',"\\1SELECT\\2 TOP $limit", $sql);
+		else if($limit > 0 && $offset > 0)
+			$sql = $this->rewriteLimitOffsetSql($sql, $limit,$offset);
+		return $sql;
+	}
+
+	/**
+	 * Rewrite sql to apply $limit > and $offset > 0 for MSSQL database.
+	 * See http://troels.arvin.dk/db/rdbms/#select-limit-offset
+	 * @param string sql query
+	 * @param integer $limit > 0
+	 * @param integer $offset > 0
+	 * @return sql modified sql query applied with limit and offset.
+	 */
+	protected function rewriteLimitOffsetSql($sql, $limit, $offset)
+	{
+		$fetch = $limit+$offset;
+		$sql = preg_replace('/^([\s(])*SELECT( DISTINCT)?(?!\s*TOP\s*\()/i',"\\1SELECT\\2 TOP $fetch", $sql);
+		$ordering = $this->findOrdering($sql);
+
+		$orginalOrdering = $this->joinOrdering($ordering);
+		$reverseOrdering = $this->joinOrdering($this->reverseDirection($ordering));
+		$sql = "SELECT * FROM (SELECT TOP {$limit} * FROM ($sql) as [__inner top table__] {$reverseOrdering}) as [__outer top table__] {$orginalOrdering}";
+		return $sql;
+	}
+
+	/**
+	 * Base on simplified syntax http://msdn2.microsoft.com/en-us/library/aa259187(SQL.80).aspx
+	 *
+	 * @param string $sql
+	 * @return array ordering expression as key and ordering direction as value
+	 */
+	protected function findOrdering($sql)
+	{
+		if(!preg_match('/ORDER BY/i', $sql))
+			return array();
+		$matches=array();
+		$ordering=array();
+		preg_match_all('/(ORDER BY)[\s"\[](.*)(ASC|DESC)?(?:[\s"\[]|$|COMPUTE|FOR)/i', $sql, $matches);
+		if(count($matches)>1 && count($matches[2]) > 0)
+		{
+			$parts = explode(',', $matches[2][0]);
+			foreach($parts as $part)
+			{
+				$subs=array();
+				if(preg_match_all('/(.*)[\s"\]](ASC|DESC)$/i', trim($part), $subs))
+				{
+					if(count($subs) > 1 && count($subs[2]) > 0)
+					{
+						$ordering[$subs[1][0]] = $subs[2][0];
+					}
+					//else what?
+				}
+				else
+					$ordering[trim($part)] = 'ASC';
+			}
+		}
+		return $ordering;
+	}
+
+	/**
+	 * @param array ordering obtained from findOrdering()
+	 * @return string concat the orderings
+	 */
+	protected function joinOrdering($orders)
+	{
+		if(count($orders)>0)
+		{
+			$str=array();
+			foreach($orders as $column => $direction)
+				$str[] = $column.' '.$direction;
+			return 'ORDER BY '.implode(', ', $str);
+		}
+	}
+
+	/**
+	 * @param array original ordering
+	 * @return array ordering with reversed direction.
+	 */
+	protected function reverseDirection($orders)
+	{
+		foreach($orders as $column => $direction)
+			$orders[$column] = strtolower(trim($direction))==='desc' ? 'ASC' : 'DESC';
+		return $orders;
+	}
+}
+
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 @@
+<?php
+/**
+ * TMssqlMetaData class file.
+ *
+ * @author Wei Zhuo <weizhuo[at]gmail[dot]com>
+ * @link https://github.com/pradosoft/prado
+ * @copyright Copyright &copy; 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 <weizho[at]gmail[dot]com>
+ * @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 = <<<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.table_schema = :schema';
+		if($catalogName!==null)
+			$sql .= ' AND t.table_catalog = :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);
+		}
+		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 = <<<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'] = $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=<<<EOD
+SELECT TABLE_NAME, TABLE_SCHEMA FROM [INFORMATION_SCHEMA].[TABLES]
+WHERE TABLE_SCHEMA=:schema AND $condition
+EOD;
+		$command=$this->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;
+	}
+}
+
diff --git a/lib/prado/framework/Data/Common/Mssql/TMssqlTableColumn.php b/lib/prado/framework/Data/Common/Mssql/TMssqlTableColumn.php
new file mode 100644
index 0000000..a02ebf0
--- /dev/null
+++ b/lib/prado/framework/Data/Common/Mssql/TMssqlTableColumn.php
@@ -0,0 +1,62 @@
+<?php
+/**
+ * TMssqlTableColumn class file.
+ *
+ * @author Wei Zhuo <weizhuo[at]gmail[dot]com>
+ * @link https://github.com/pradosoft/prado
+ * @copyright Copyright &copy; 2005-2015 The PRADO Group
+ * @license https://github.com/pradosoft/prado/blob/master/COPYRIGHT
+ * @package System.Data.Common.Mssql
+ */
+
+/**
+ * Load common TDbTableCommon class.
+ */
+Prado::using('System.Data.Common.TDbTableColumn');
+
+/**
+ * Describes the column metadata of the schema for a Mssql database table.
+ *
+ * @author Wei Zhuo <weizho[at]gmail[dot]com>
+ * @package System.Data.Common.Mssql
+ * @since 3.1
+ */
+class TMssqlTableColumn extends TDbTableColumn
+{
+	private static $types = array();
+
+	/**
+	 * Overrides parent implementation, returns PHP type from the db type.
+	 * @return boolean derived PHP primitive type from the column db type.
+	 */
+	public function getPHPType()
+	{
+
+		return 'string';
+	}
+
+	/**
+	 * @return boolean true if the column has identity (auto-increment)
+	 */
+	public function getAutoIncrement()
+	{
+		return $this->getInfo('AutoIncrement',false);
+	}
+
+	/**
+	 * @return boolean true if auto increments.
+	 */
+	public function hasSequence()
+	{
+		return $this->getAutoIncrement();
+	}
+
+	/**
+	 * @return boolean true if db type is 'timestamp'.
+	 */
+	public function getIsExcluded()
+	{
+		return strtolower($this->getDbType())==='timestamp';
+	}
+}
+
diff --git a/lib/prado/framework/Data/Common/Mssql/TMssqlTableInfo.php b/lib/prado/framework/Data/Common/Mssql/TMssqlTableInfo.php
new file mode 100644
index 0000000..bee4730
--- /dev/null
+++ b/lib/prado/framework/Data/Common/Mssql/TMssqlTableInfo.php
@@ -0,0 +1,62 @@
+<?php
+/**
+ * TMssqlTableInfo class file.
+ *
+ * @author Wei Zhuo <weizhuo[at]gmail[dot]com>
+ * @link https://github.com/pradosoft/prado
+ * @copyright Copyright &copy; 2005-2015 The PRADO Group
+ * @license https://github.com/pradosoft/prado/blob/master/COPYRIGHT
+ * @package System.Data.Common.Mssql
+ */
+
+/**
+ * Loads the base TDbTableInfo class and TMssqlTableColumn class.
+ */
+Prado::using('System.Data.Common.TDbTableInfo');
+Prado::using('System.Data.Common.Mssql.TMssqlTableColumn');
+
+/**
+ * TMssqlTableInfo class provides additional table information for Mssql database.
+ *
+ * @author Wei Zhuo <weizho[at]gmail[dot]com>
+ * @package System.Data.Common.Mssql
+ * @since 3.1
+ */
+class TMssqlTableInfo extends TDbTableInfo
+{
+	/**
+	 * @return string name of the schema this column belongs to.
+	 */
+	public function getSchemaName()
+	{
+		return $this->getInfo('SchemaName');
+	}
+
+	/**
+	 * @return string catalog name (database name)
+	 */
+	public function getCatalogName()
+	{
+		return $this->getInfo('CatalogName');
+	}
+
+	/**
+	 * @return string full name of the table, database dependent.
+	 */
+	public function getTableFullName()
+	{
+		//MSSQL alway returns the catalog, schem and table names.
+		return '['.$this->getCatalogName().'].['.$this->getSchemaName().'].['.$this->getTableName().']';
+	}
+
+	/**
+	 * @param TDbConnection database connection.
+	 * @return TDbCommandBuilder new command builder
+	 */
+	public function createCommandBuilder($connection)
+	{
+		Prado::using('System.Data.Common.Mssql.TMssqlCommandBuilder');
+		return new TMssqlCommandBuilder($connection,$this);
+	}
+}
+