diff options
Diffstat (limited to 'framework/Data/Common/Mssql')
| -rw-r--r-- | framework/Data/Common/Mssql/TMssqlCommandBuilder.php | 173 | ||||
| -rw-r--r-- | framework/Data/Common/Mssql/TMssqlMetaData.php | 202 | ||||
| -rw-r--r-- | framework/Data/Common/Mssql/TMssqlTableColumn.php | 56 | ||||
| -rw-r--r-- | framework/Data/Common/Mssql/TMssqlTableInfo.php | 62 |
4 files changed, 493 insertions, 0 deletions
diff --git a/framework/Data/Common/Mssql/TMssqlCommandBuilder.php b/framework/Data/Common/Mssql/TMssqlCommandBuilder.php new file mode 100644 index 00000000..b22c08a5 --- /dev/null +++ b/framework/Data/Common/Mssql/TMssqlCommandBuilder.php @@ -0,0 +1,173 @@ +<?php
+/**
+ * TDbCommandBuilder class file.
+ *
+ * @author Wei Zhuo <weizhuo[at]gmail[dot]com>
+ * @link http://www.pradosoft.com/
+ * @copyright Copyright © 2005-2007 PradoSoft
+ * @license http://www.pradosoft.com/license/
+ * @version $Id: TDbCommandBuilder.php 1863 2007-04-12 12:43:49Z wei $
+ * @package System.Data.Common
+ */
+
+Prado::using('System.Data.Common.TDbCommandBuilder');
+
+/**
+ * TDbCommandBuilder provides basic methods to create query commands for tables
+ * giving by {@link setTableInfo TableInfo} the property.
+ *
+ * @author Wei Zhuo <weizho[at]gmail[dot]com>
+ * @version $Id: TDbCommandBuilder.php 1863 2007-04-12 12:43:49Z wei $
+ * @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>
+ *
+ * @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;
+ }
+}
+
+?>
\ No newline at end of file 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 diff --git a/framework/Data/Common/Mssql/TMssqlTableColumn.php b/framework/Data/Common/Mssql/TMssqlTableColumn.php new file mode 100644 index 00000000..77c115e5 --- /dev/null +++ b/framework/Data/Common/Mssql/TMssqlTableColumn.php @@ -0,0 +1,56 @@ +<?php
+/**
+ * TMssqlTableColumn class file.
+ *
+ * @author Wei Zhuo <weizhuo[at]gmail[dot]com>
+ * @link http://www.pradosoft.com/
+ * @copyright Copyright © 2005-2007 PradoSoft
+ * @license http://www.pradosoft.com/license/
+ * @version $Id: TMssqlTableColumn.php 1863 2007-04-12 12:43:49Z wei $
+ * @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>
+ * @version $Id: TMssqlTableColumn.php 1863 2007-04-12 12:43:49Z wei $
+ * @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';
+ }
+
+ public function getAutoIncrement()
+ {
+ return $this->getInfo('AutoIncrement',false);
+ }
+
+ public function hasSequence()
+ {
+ return $this->getAutoIncrement();
+ }
+
+ public function getIsExcluded()
+ {
+ return strtolower($this->getDbType())==='timestamp';
+ }
+}
+
+?>
\ No newline at end of file diff --git a/framework/Data/Common/Mssql/TMssqlTableInfo.php b/framework/Data/Common/Mssql/TMssqlTableInfo.php new file mode 100644 index 00000000..b407ad44 --- /dev/null +++ b/framework/Data/Common/Mssql/TMssqlTableInfo.php @@ -0,0 +1,62 @@ +<?php
+/**
+ * TMssqlTableInfo class file.
+ *
+ * @author Wei Zhuo <weizhuo[at]gmail[dot]com>
+ * @link http://www.pradosoft.com/
+ * @copyright Copyright © 2005-2007 PradoSoft
+ * @license http://www.pradosoft.com/license/
+ * @version $Id: TMssqlTableInfo.php 1861 2007-04-12 08:05:03Z wei $
+ * @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>
+ * @version $Id: TMssqlTableInfo.php 1861 2007-04-12 08:05:03Z wei $
+ * @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');
+ }
+
+ public function getCatalogName()
+ {
+ return $this->getInfo('CatalogName');
+ }
+
+ /**
+ * @return string full name of the table, database dependent.
+ */
+ public function getTableFullName()
+ {
+ 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);
+ }
+
+}
+
+?>
\ No newline at end of file |