From 6f7fdef0f500cd4bb540affd3bc1482243f337c1 Mon Sep 17 00:00:00 2001 From: emkael Date: Wed, 24 Feb 2016 23:18:07 +0100 Subject: * Prado 3.3.0 --- .../Data/Common/Mssql/TMssqlCommandBuilder.php | 171 ++++++++++++ .../framework/Data/Common/Mssql/TMssqlMetaData.php | 290 +++++++++++++++++++++ .../Data/Common/Mssql/TMssqlTableColumn.php | 62 +++++ .../Data/Common/Mssql/TMssqlTableInfo.php | 62 +++++ 4 files changed, 585 insertions(+) create mode 100644 lib/prado/framework/Data/Common/Mssql/TMssqlCommandBuilder.php create mode 100644 lib/prado/framework/Data/Common/Mssql/TMssqlMetaData.php create mode 100644 lib/prado/framework/Data/Common/Mssql/TMssqlTableColumn.php create mode 100644 lib/prado/framework/Data/Common/Mssql/TMssqlTableInfo.php (limited to 'lib/prado/framework/Data/Common/Mssql') 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 @@ + + * @link https://github.com/pradosoft/prado + * @copyright Copyright © 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 + * @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 + * + * + * 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) + * + * + * Regular expressions are used to alter the SQL query. The resulting SQL query + * may be malformed for complex queries. The following restrictions apply + * + * + * + * @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 @@ + + * @link https://github.com/pradosoft/prado + * @copyright Copyright © 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 + * @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 = <<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; + } +} + 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 @@ + + * @link https://github.com/pradosoft/prado + * @copyright Copyright © 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 + * @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 @@ + + * @link https://github.com/pradosoft/prado + * @copyright Copyright © 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 + * @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); + } +} + -- cgit v1.2.3