diff options
Diffstat (limited to 'lib/prado/framework/Data/Common')
24 files changed, 3901 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 © 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 © 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 © 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 © 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); + } +} + diff --git a/lib/prado/framework/Data/Common/Mysql/TMysqlCommandBuilder.php b/lib/prado/framework/Data/Common/Mysql/TMysqlCommandBuilder.php new file mode 100644 index 0000000..b5bdc28 --- /dev/null +++ b/lib/prado/framework/Data/Common/Mysql/TMysqlCommandBuilder.php @@ -0,0 +1,24 @@ +<?php +/** + * TMysqlCommandBuilder class file. + * + * @author Wei Zhuo <weizhuo[at]gmail[dot]com> + * @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'); + +/** + * TMysqlCommandBuilder implements default TDbCommandBuilder + * + * @author Wei Zhuo <weizho[at]gmail[dot]com> + * @package System.Data.Common + * @since 3.1 + */ +class TMysqlCommandBuilder extends TDbCommandBuilder +{ +} + diff --git a/lib/prado/framework/Data/Common/Mysql/TMysqlMetaData.php b/lib/prado/framework/Data/Common/Mysql/TMysqlMetaData.php new file mode 100644 index 0000000..e7747b7 --- /dev/null +++ b/lib/prado/framework/Data/Common/Mysql/TMysqlMetaData.php @@ -0,0 +1,405 @@ +<?php +/** + * TMysqlMetaData class file. + * + * @author Wei Zhuo <weizhuo[at]gmail[dot]com> + * @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.Mysql + */ + +/** + * Load the base TDbMetaData class. + */ +Prado::using('System.Data.Common.TDbMetaData'); +Prado::using('System.Data.Common.Mysql.TMysqlTableInfo'); + +/** + * TMysqlMetaData loads Mysql version 4.1.x and 5.x database table and column information. + * + * For Mysql version 4.1.x, PHP 5.1.3 or later is required. + * See http://netevil.org/node.php?nid=795&SC=1 + * + * @author Wei Zhuo <weizho[at]gmail[dot]com> + * @package System.Data.Common.Mysql + * @since 3.1 + */ +class TMysqlMetaData extends TDbMetaData +{ + private $_serverVersion=0; + + /** + * @return string TDbTableInfo class name. + */ + protected function getTableInfoClass() + { + return 'TMysqlTableInfo'; + } + + /** + * 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 TMysqlTableInfo table information. + */ + protected function createTableInfo($table) + { + list($schemaName,$tableName) = $this->getSchemaTableName($table); + $find = $schemaName===null ? "`{$tableName}`" : "`{$schemaName}`.`{$tableName}`"; + $colCase = $this->getDbConnection()->getColumnCase(); + if($colCase != TDbColumnCaseMode::Preserved) + $this->getDbConnection()->setColumnCase('Preserved'); + $this->getDbConnection()->setActive(true); + $sql = "SHOW FULL FIELDS FROM {$find}"; + $command = $this->getDbConnection()->createCommand($sql); + $tableInfo = $this->createNewTableInfo($table); + $index=0; + foreach($command->query() as $col) + { + $col['index'] = $index++; + $this->processColumn($tableInfo,$col); + } + if($index===0) + throw new TDbException('dbmetadata_invalid_table_view', $table); + if($colCase != TDbColumnCaseMode::Preserved) + $this->getDbConnection()->setColumnCase($colCase); + return $tableInfo; + } + + /** + * @return float server version. + */ + protected function getServerVersion() + { + if(!$this->_serverVersion) + { + $version = $this->getDbConnection()->getAttribute(PDO::ATTR_SERVER_VERSION); + $digits=array(); + preg_match('/(\d+)\.(\d+)\.(\d+)/', $version, $digits); + $this->_serverVersion=floatval($digits[1].'.'.$digits[2].$digits[3]); + } + return $this->_serverVersion; + } + + /** + * @param TMysqlTableInfo table information. + * @param array column information. + */ + protected function processColumn($tableInfo, $col) + { + $columnId = $col['Field']; + + $info['ColumnName'] = "`$columnId`"; //quote the column names! + $info['ColumnId'] = $columnId; + $info['ColumnIndex'] = $col['index']; + if($col['Null']==='YES') + $info['AllowNull'] = true; + if(is_int(strpos(strtolower($col['Extra']), 'auto_increment'))) + $info['AutoIncrement']=true; + if($col['Default']!=="") + $info['DefaultValue'] = $col['Default']; + + if($col['Key']==='PRI' || in_array($columnId, $tableInfo->getPrimaryKeys())) + $info['IsPrimaryKey'] = true; + if($this->isForeignKeyColumn($columnId, $tableInfo)) + $info['IsForeignKey'] = true; + + $info['DbType'] = $col['Type']; + $match=array(); + //find SET/ENUM values, column size, precision, and scale + if(preg_match('/\((.*)\)/', $col['Type'], $match)) + { + $info['DbType']= preg_replace('/\(.*\)/', '', $col['Type']); + + //find SET/ENUM values + if($this->isEnumSetType($info['DbType'])) + $info['DbTypeValues'] = preg_split("/[',]/S", $match[1], -1, PREG_SPLIT_NO_EMPTY); + + //find column size, precision and scale + $pscale = array(); + if(preg_match('/(\d+)(?:,(\d+))?+/', $match[1], $pscale)) + { + if($this->isPrecisionType($info['DbType'])) + { + $info['NumericPrecision'] = intval($pscale[1]); + if(count($pscale) > 2) + $info['NumericScale'] = intval($pscale[2]); + } + else + $info['ColumnSize'] = intval($pscale[1]); + } + } + + $tableInfo->Columns[$columnId] = new TMysqlTableColumn($info); + } + + /** + * @return boolean true if column type if "numeric", "interval" or begins with "time". + */ + protected function isPrecisionType($type) + { + $type = strtolower(trim($type)); + return $type==='decimal' || $type==='dec' + || $type==='float' || $type==='double' + || $type==='double precision' || $type==='real'; + } + + /** + * @return boolean true if column type if "enum" or "set". + */ + protected function isEnumSetType($type) + { + $type = strtolower(trim($type)); + return $type==='set' || $type==='enum'; + } + + /** + * @param string table name, may be quoted with back-ticks and may contain database name. + * @return array tuple ($schema,$table), $schema may be null. + * @throws TDbException when table name contains invalid identifier bytes. + */ + protected function getSchemaTableName($table) + { + //remove the back ticks and separate out the "database.table" + $result = explode('.', str_replace('`', '', $table)); + foreach($result as $name) + { + if(!$this->isValidIdentifier($name)) + { + $ref = 'http://dev.mysql.com/doc/refman/5.0/en/identifiers.html'; + throw new TDbException('dbcommon_invalid_identifier_name', $table, $ref); + } + } + return count($result) > 1 ? $result : array(null, $result[0]); + } + + /** + * http://dev.mysql.com/doc/refman/5.0/en/identifiers.html + * @param string identifier name + * @param boolean true if valid identifier. + */ + protected function isValidIdentifier($name) + { + return !preg_match('#/|\\|.|\x00|\xFF#', $name); + } + + /** + * @param string table schema name + * @param string table name. + * @return TMysqlTableInfo + */ + protected function createNewTableInfo($table) + { + list($schemaName,$tableName) = $this->getSchemaTableName($table); + $info['SchemaName'] = $schemaName; + $info['TableName'] = $tableName; + if($this->getIsView($schemaName,$tableName)) + $info['IsView'] = true; + list($primary, $foreign) = $this->getConstraintKeys($schemaName, $tableName); + $class = $this->getTableInfoClass(); + return new $class($info,$primary,$foreign); + } + + /** + * For MySQL version 5.0.1 or later we can use SHOW FULL TABLES + * http://dev.mysql.com/doc/refman/5.0/en/show-tables.html + * + * For MySQL version 5.0.1 or ealier, this always return false. + * @param string database name, null to use default connection database. + * @param string table or view name. + * @return boolean true if is view, false otherwise. + * @throws TDbException if table or view does not exist. + */ + protected function getIsView($schemaName,$tableName) + { + if($this->getServerVersion()<5.01) + return false; + if($schemaName!==null) + $sql = "SHOW FULL TABLES FROM `{$schemaName}` LIKE :table"; + else + $sql = "SHOW FULL TABLES LIKE :table"; + + $command = $this->getDbConnection()->createCommand($sql); + $command->bindValue(':table', $tableName); + try + { + return count($result = $command->queryRow()) > 0 && $result['Table_type']==='VIEW'; + } + catch(TDbException $e) + { + $table = $schemaName===null?$tableName:$schemaName.'.'.$tableName; + throw new TDbException('dbcommon_invalid_table_name',$table,$e->getMessage()); + } + } + + /** + * 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($schemaName, $tableName) + { + $table = $schemaName===null ? "`{$tableName}`" : "`{$schemaName}`.`{$tableName}`"; + $sql = "SHOW INDEX FROM {$table}"; + $command = $this->getDbConnection()->createCommand($sql); + $primary = array(); + foreach($command->query() as $row) + { + if($row['Key_name']==='PRIMARY') + $primary[] = $row['Column_name']; + } + // MySQL version was increased to >=5.1.21 instead of 5.x + // due to a MySQL bug (http://bugs.mysql.com/bug.php?id=19588) + if($this->getServerVersion() >= 5.121) + $foreign = $this->getForeignConstraints($schemaName,$tableName); + else + $foreign = $this->findForeignConstraints($schemaName,$tableName); + 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($schemaName, $tableName) + { + $andSchema = $schemaName !== null ? 'AND TABLE_SCHEMA LIKE :schema' : 'AND TABLE_SCHEMA LIKE DATABASE()'; + $sql = <<<EOD + SELECT + CONSTRAINT_NAME as con, + COLUMN_NAME as col, + REFERENCED_TABLE_SCHEMA as fkschema, + REFERENCED_TABLE_NAME as fktable, + REFERENCED_COLUMN_NAME as fkcol + FROM + `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE` + WHERE + REFERENCED_TABLE_NAME IS NOT NULL + AND TABLE_NAME LIKE :table + $andSchema +EOD; + $command = $this->getDbConnection()->createCommand($sql); + $command->bindValue(':table', $tableName); + if($schemaName!==null) + $command->bindValue(':schema', $schemaName); + $fkeys=array(); + foreach($command->query() as $col) + { + $fkeys[$col['con']]['keys'][$col['col']] = $col['fkcol']; + $fkeys[$col['con']]['table'] = $col['fktable']; + } + return count($fkeys) > 0 ? array_values($fkeys) : $fkeys; + } + + /** + * @param string database name + * @param string table name + * @return string SQL command to create the table. + * @throws TDbException if PHP version is less than 5.1.3 + */ + protected function getShowCreateTable($schemaName, $tableName) + { + if(version_compare(PHP_VERSION,'5.1.3','<')) + throw new TDbException('dbmetadata_requires_php_version', 'Mysql 4.1.x', '5.1.3'); + + //See http://netevil.org/node.php?nid=795&SC=1 + $this->getDbConnection()->setAttribute(PDO::ATTR_EMULATE_PREPARES, true); + if($schemaName!==null) + $sql = "SHOW CREATE TABLE `{$schemaName}`.`{$tableName}`"; + else + $sql = "SHOW CREATE TABLE `{$tableName}`"; + $command = $this->getDbConnection()->createCommand($sql); + $result = $command->queryRow(); + return isset($result['Create Table']) ? $result['Create Table'] : (isset($result['Create View']) ? $result['Create View'] : ''); + } + + /** + * Extract foreign key constraints by extracting the contraints from SHOW CREATE TABLE result. + * @param string database name + * @param string table name + * @return array foreign relationship table name and keys. + */ + protected function findForeignConstraints($schemaName, $tableName) + { + $sql = $this->getShowCreateTable($schemaName, $tableName); + $matches =array(); + $regexp = '/FOREIGN KEY\s+\(([^\)]+)\)\s+REFERENCES\s+`?([^`]+)`?\s\(([^\)]+)\)/mi'; + preg_match_all($regexp,$sql,$matches,PREG_SET_ORDER); + $foreign = array(); + foreach($matches as $match) + { + $fields = array_map('trim',explode(',',str_replace('`','',$match[1]))); + $fk_fields = array_map('trim',explode(',',str_replace('`','',$match[3]))); + $keys=array(); + foreach($fields as $k=>$v) + $keys[$v] = $fk_fields[$k]; + $foreign[] = array('keys' => $keys, 'table' => trim($match[2])); + } + return $foreign; + } + + /** + * @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='') + { + if($schema==='') + return $this->getDbConnection()->createCommand('SHOW TABLES')->queryColumn(); + $names=$this->getDbConnection()->createCommand('SHOW TABLES FROM '.$this->quoteTableName($schema))->queryColumn(); + foreach($names as &$name) + $name=$schema.'.'.$name; + return $names; + } +} + diff --git a/lib/prado/framework/Data/Common/Mysql/TMysqlTableColumn.php b/lib/prado/framework/Data/Common/Mysql/TMysqlTableColumn.php new file mode 100644 index 0000000..5b84f80 --- /dev/null +++ b/lib/prado/framework/Data/Common/Mysql/TMysqlTableColumn.php @@ -0,0 +1,70 @@ +<?php +/** + * TMysqlTableColumn class file. + * + * @author Wei Zhuo <weizhuo[at]gmail[dot]com> + * @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.Mysql + */ + +/** + * Load common TDbTableCommon class. + */ +Prado::using('System.Data.Common.TDbTableColumn'); + +/** + * Describes the column metadata of the schema for a Mysql database table. + * + * @author Wei Zhuo <weizho[at]gmail[dot]com> + * @package System.Data.Common.Mysql + * @since 3.1 + */ +class TMysqlTableColumn extends TDbTableColumn +{ + private static $types = array( + 'integer' => array('bit', 'tinyint', 'smallint', 'mediumint', 'int', 'integer', 'bigint'), + 'boolean' => array('boolean', 'bool'), + 'float' => array('float', 'double', 'double precision', 'decimal', 'dec', 'numeric', 'fixed') + ); + + /** + * Overrides parent implementation, returns PHP type from the db type. + * @return boolean derived PHP primitive type from the column db type. + */ + public function getPHPType() + { + $dbtype = trim(str_replace(array('unsigned', 'zerofill'),array('','',),strtolower($this->getDbType()))); + if($dbtype==='tinyint' && $this->getColumnSize()===1) + return 'boolean'; + foreach(self::$types as $type => $dbtypes) + { + if(in_array($dbtype, $dbtypes)) + return $type; + } + return 'string'; + } + + /** + * @return boolean true if column will auto-increment when the column value is inserted as null. + */ + public function getAutoIncrement() + { + return $this->getInfo('AutoIncrement', false); + } + + /** + * @return boolean true if auto increment is true. + */ + public function hasSequence() + { + return $this->getAutoIncrement(); + } + + public function getDbTypeValues() + { + return $this->getInfo('DbTypeValues'); + } +} + diff --git a/lib/prado/framework/Data/Common/Mysql/TMysqlTableInfo.php b/lib/prado/framework/Data/Common/Mysql/TMysqlTableInfo.php new file mode 100644 index 0000000..bc5dc4e --- /dev/null +++ b/lib/prado/framework/Data/Common/Mysql/TMysqlTableInfo.php @@ -0,0 +1,56 @@ +<?php +/** + * TMysqlTableInfo class file. + * + * @author Wei Zhuo <weizhuo[at]gmail[dot]com> + * @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.Mysql + */ + +/** + * Loads the base TDbTableInfo class and TMysqlTableColumn class. + */ +Prado::using('System.Data.Common.TDbTableInfo'); +Prado::using('System.Data.Common.Mysql.TMysqlTableColumn'); + +/** + * TMysqlTableInfo class provides additional table information for MySQL database. + * + * @author Wei Zhuo <weizho[at]gmail[dot]com> + * @package System.Data.Common.Mysql + * @since 3.1 + */ +class TMysqlTableInfo extends TDbTableInfo +{ + /** + * @return string name of the schema this column belongs to. + */ + public function getSchemaName() + { + return $this->getInfo('SchemaName'); + } + + /** + * @return string full name of the table, database dependent. + */ + public function getTableFullName() + { + if(($schema=$this->getSchemaName())!==null) + return '`'.$schema.'`.`'.$this->getTableName().'`'; + else + return '`'.$this->getTableName().'`'; + } + + /** + * @param TDbConnection database connection. + * @return TDbCommandBuilder new command builder + */ + public function createCommandBuilder($connection) + { + Prado::using('System.Data.Common.Mysql.TMysqlCommandBuilder'); + return new TMysqlCommandBuilder($connection,$this); + } +} + diff --git a/lib/prado/framework/Data/Common/Oracle/TOracleCommandBuilder.php b/lib/prado/framework/Data/Common/Oracle/TOracleCommandBuilder.php new file mode 100644 index 0000000..ad4c9f4 --- /dev/null +++ b/lib/prado/framework/Data/Common/Oracle/TOracleCommandBuilder.php @@ -0,0 +1,153 @@ +<?php + +/** + * TOracleCommandBuilder class file. + * + * @author Marcos Nobre <marconobre[at]gmail[dot]com> + * @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'); + +/** + * TOracleCommandBuilder provides specifics methods to create limit/offset query commands + * for Oracle database. + * + * @author Marcos Nobre <marconobre[at]gmail[dot]com> + * @package System.Data.Common + * @since 3.1 + */ +class TOracleCommandBuilder extends TDbCommandBuilder { + + /** + * Overrides parent implementation. Only column of type text or character (and its variants) + * accepts the LIKE criteria. + * @param array list of column id for potential search condition. + * @param string string of keywords + * @return string SQL search condition matching on a set of columns. + */ + public function getSearchExpression($fields, $keywords) { + $columns = array (); + foreach ($fields as $field) { + if ($this->isSearchableColumn($this->getTableInfo()->getColumn($field))) + $columns[] = $field; + } + return parent :: getSearchExpression($columns, $keywords); + } + /** + * + * @return boolean true if column can be used for LIKE searching. + */ + protected function isSearchableColumn($column) { + $type = strtolower($column->getDbType()); + return $type === 'character varying' || $type === 'varchar2' || $type === 'character' || $type === 'char' || $type === 'text'; + } + + /** + * Overrides parent implementation to use PostgreSQL's ILIKE instead of LIKE (case-sensitive). + * @param string column name. + * @param array keywords + * @return string search condition for all words in one column. + */ + /* + * + * how Oracle don't implements ILIKE, this method won't be overrided + * + protected function getSearchCondition($column, $words) + { + $conditions=array(); + foreach($words as $word) + $conditions[] = $column.' LIKE '.$this->getDbConnection()->quoteString('%'.$word.'%'); + return '('.implode(' AND ', $conditions).')'; + } + */ + + /** + * Overrides parent implementation to use Oracle way of get paginated RecordSet instead of using LIMIT sql clause. + * @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 in Oracle way. + */ + public function applyLimitOffset($sql, $limit = -1, $offset = -1) { + if ((int) $limit <= 0 && (int) $offset <= 0) + return $sql; + + $pradoNUMLIN = 'pradoNUMLIN'; + $fieldsALIAS = 'xyz'; + + $nfimDaSQL = strlen($sql); + $nfimDoWhere = (strpos($sql, 'ORDER') !== false ? strpos($sql, 'ORDER') : $nfimDaSQL); + $niniDoSelect = strpos($sql, 'SELECT') + 6; + $nfimDoSelect = (strpos($sql, 'FROM') !== false ? strpos($sql, 'FROM') : $nfimDaSQL); + + $WhereInSubSelect=""; + if(strpos($sql, 'WHERE')!==false) + $WhereInSubSelect = "WHERE " .substr($sql, strpos($sql, 'WHERE')+5, $nfimDoWhere - $niniDoWhere); + + $sORDERBY = ''; + if (stripos($sql, 'ORDER') !== false) { + $p = stripos($sql, 'ORDER'); + $sORDERBY = substr($sql, $p +8); + } + + $fields = substr($sql, 0, $nfimDoSelect); + $fields = trim(substr($fields, $niniDoSelect)); + $aliasedFields = ', '; + + if (trim($fields) == '*') { + $aliasedFields = ", {$fieldsALIAS}.{$fields}"; + $fields = ''; + $arr = $this->getTableInfo()->getColumns(); + foreach ($arr as $field) { + $fields .= strtolower($field->getColumnName()) . ', '; + } + $fields = str_replace('"', '', $fields); + $fields = trim($fields); + $fields = substr($fields, 0, strlen($fields) - 1); + } else { + if (strpos($fields, ',') !== false) { + $arr = $this->getTableInfo()->getColumns(); + foreach ($arr as $field) { + $field = strtolower($field); + $existAS = str_ireplace(' as ', '-as-', $field); + if (strpos($existAS, '-as-') === false) + $aliasedFields .= "{$fieldsALIAS}." . trim($field) . ", "; + else + $aliasedFields .= "{$field}, "; + } + $aliasedFields = trim($aliasedFields); + $aliasedFields = substr($aliasedFields, 0, strlen($aliasedFields) - 1); + } + } + if ($aliasedFields == ', ') + $aliasedFields = " , $fieldsALIAS.* "; + + /* ************************ + $newSql = " SELECT $fields FROM ". + "( ". + " SELECT rownum as {$pradoNUMLIN} {$aliasedFields} FROM ". + " ($sql) {$fieldsALIAS} WHERE rownum <= {$limit} ". + ") WHERE {$pradoNUMLIN} >= {$offset} "; + + ************************* */ + $offset=(int)$offset; + $toReg = $offset + $limit ; + $fullTableName = $this->getTableInfo()->getTableFullName(); + if (empty ($sORDERBY)) + $sORDERBY="ROWNUM"; + + $newSql = " SELECT $fields FROM " . + "( " . + " SELECT ROW_NUMBER() OVER ( ORDER BY {$sORDERBY} ) -1 as {$pradoNUMLIN} {$aliasedFields} " . + " FROM {$fullTableName} {$fieldsALIAS} $WhereInSubSelect" . + ") nn " . + " WHERE nn.{$pradoNUMLIN} >= {$offset} AND nn.{$pradoNUMLIN} < {$toReg} "; + //echo $newSql."\n<br>\n"; + return $newSql; + } + +} diff --git a/lib/prado/framework/Data/Common/Oracle/TOracleMetaData.php b/lib/prado/framework/Data/Common/Oracle/TOracleMetaData.php new file mode 100644 index 0000000..7fd3d1f --- /dev/null +++ b/lib/prado/framework/Data/Common/Oracle/TOracleMetaData.php @@ -0,0 +1,374 @@ +<?php +/** + * TOracleMetaData class file. + * + * @author Marcos Nobre <marconobre[at]gmail[dot]com> + * @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.Oracle + */ + +/** + * Load the base TDbMetaData class. + */ +Prado::using('System.Data.Common.TDbMetaData'); +Prado::using('System.Data.Common.Oracle.TOracleTableInfo'); +Prado::using('System.Data.Common.Oracle.TOracleTableColumn'); + +/** + * TOracleMetaData loads Oracle database table and column information. + * + * @author Marcos Nobre <marconobre[at]gmail[dot]com> + * @package System.Data.Common.Oracle + * @since 3.1 + */ +class TOracleMetaData extends TDbMetaData +{ + private $_defaultSchema = 'system'; + + + /** + * @return string TDbTableInfo class name. + */ + protected function getTableInfoClass() + { + return 'TOracleTableInfo'; + } + + /** + * @param string default schema. + */ + public function setDefaultSchema($schema) + { + $this->_defaultSchema=$schema; + } + + /** + * @return string default schema. + */ + public function getDefaultSchema() + { + return $this->_defaultSchema; + } + + /** + * @param string table name with optional schema name prefix, uses default schema name prefix is not provided. + * @return array tuple as ($schemaName,$tableName) + */ + protected function getSchemaTableName($table) + { + if(count($parts= explode('.', str_replace('"','',$table))) > 1) + return array($parts[0], $parts[1]); + else + return array($this->getDefaultSchema(),$parts[0]); + } + + /** + * Get the column definitions for given table. + * @param string table name. + * @return TOracleTableInfo table information. + */ + protected function createTableInfo($table) + { + list($schemaName,$tableName) = $this->getSchemaTableName($table); + + // This query is made much more complex by the addition of the 'attisserial' field. + // The subquery to get that field checks to see if there is an internally dependent + // sequence on the field. + $sql = +<<<EOD + SELECT + a.COLUMN_ID, + LOWER(a.COLUMN_NAME) as attname, + a.DATA_TYPE || DECODE( a.DATA_TYPE, 'NUMBER', '('||a.DATA_PRECISION||','||DATA_SCALE||')' , '') as type, + a.DATA_LENGTH as atttypmod, + DECODE(a.NULLABLE, 'Y', '0', '1') as attnotnull, + DECODE(a.DEFAULT_LENGTH, NULL, '0', '1') as atthasdef, + DATA_DEFAULT as adsrc, + '0' AS attisserial + FROM + ALL_TAB_COLUMNS a + WHERE + TABLE_NAME = '{$tableName}' + AND OWNER = '{$schemaName}' + ORDER BY a.COLUMN_ID +EOD; + $this->getDbConnection()->setActive(true); + $this->getDbConnection()->setAttribute(PDO::ATTR_CASE, PDO::CASE_LOWER); + $command = $this->getDbConnection()->createCommand($sql); + //$command->bindValue(':table', $tableName); + //$command->bindValue(':schema', $schemaName); + $tableInfo = $this->createNewTableInfo($schemaName, $tableName); + $index=0; + foreach($command->query() as $col) + { + $col['index'] = $index++; + $this->processColumn($tableInfo, $col); + } + if($index===0) + throw new TDbException('dbmetadata_invalid_table_view', $table); + return $tableInfo; + } + + /** + * @param string table schema name + * @param string table name. + * @return TOracleTableInfo + */ + protected function createNewTableInfo($schemaName,$tableName) + { + $info['SchemaName'] = $this->assertIdentifier($schemaName); + $info['TableName'] = $this->assertIdentifier($tableName); + $info['IsView'] = false; + if($this->getIsView($schemaName,$tableName)) $info['IsView'] = true; + list($primary, $foreign) = $this->getConstraintKeys($schemaName, $tableName); + $class = $this->getTableInfoClass(); + return new $class($info,$primary,$foreign); + } + + /** + * @param string table name, schema name or column name. + * @return string a valid identifier. + * @throws TDbException when table name contains a double quote ("). + */ + protected function assertIdentifier($name) + { + if(strpos($name, '"')!==false) + { + $ref = 'http://www.oracle.com'; + throw new TDbException('dbcommon_invalid_identifier_name', $name, $ref); + } + return $name; + } + + /** + * @param string table schema name + * @param string table name. + * @return boolean true if the table is a view. + */ + protected function getIsView($schemaName,$tableName) + { + $this->getDbConnection()->setAttribute(PDO::ATTR_CASE, PDO::CASE_LOWER); + $sql = +<<<EOD + select OBJECT_TYPE + from ALL_OBJECTS + where OBJECT_NAME = '{$tableName}' + and OWNER = '{$schemaName}' +EOD; + $this->getDbConnection()->setActive(true); + $command = $this->getDbConnection()->createCommand($sql); + //$command->bindValue(':schema',$schemaName); + //$command->bindValue(':table', $tableName); + return intval($command->queryScalar() === 'VIEW'); + } + + /** + * @param TOracleTableInfo table information. + * @param array column information. + */ + protected function processColumn($tableInfo, $col) + { + $columnId = strtolower($col['attname']); //use column name as column Id + + //$info['ColumnName'] = '"'.$columnId.'"'; //quote the column names! + $info['ColumnName'] = $columnId; //NOT quote the column names! + $info['ColumnId'] = $columnId; + $info['ColumnIndex'] = $col['index']; + if(! (bool)$col['attnotnull'] ) $info['AllowNull'] = true; + if(in_array($columnId, $tableInfo->getPrimaryKeys())) $info['IsPrimaryKey'] = true; + if($this->isForeignKeyColumn($columnId, $tableInfo)) $info['IsForeignKey'] = true; + if( (int)$col['atttypmod'] > 0 ) $info['ColumnSize'] = $col['atttypmod']; // - 4; + if( (bool)$col['atthasdef'] ) $info['DefaultValue'] = $col['adsrc']; + // + // For a while Oracle Tables has no associated AutoIncrement Triggers + // + /* + if( $col['attisserial'] ) + { + if(($sequence = $this->getSequenceName($tableInfo, $col['adsrc']))!==null) + { + $info['SequenceName'] = $sequence; + unset($info['DefaultValue']); + } + } + */ + $matches = array(); + if(preg_match('/\((\d+)(?:,(\d+))?+\)/', $col['type'], $matches)) + { + $info['DbType'] = preg_replace('/\(\d+(?:,\d+)?\)/','',$col['type']); + if($this->isPrecisionType($info['DbType'])) + { + $info['NumericPrecision'] = intval($matches[1]); + if(count($matches) > 2) + $info['NumericScale'] = intval($matches[2]); + } + else + $info['ColumnSize'] = intval($matches[1]); + } + else + $info['DbType'] = $col['type']; + $tableInfo->Columns[$columnId] = new TOracleTableColumn($info); + } + + /** + * @return string serial name if found, null otherwise. + */ + protected function getSequenceName($tableInfo,$src) + { + $matches = array(); + if(preg_match('/nextval\([^\']*\'([^\']+)\'[^\)]*\)/i',$src,$matches)) + { + if(is_int(strpos($matches[1], '.'))) + return $matches[1]; + else + return $tableInfo->getSchemaName().'.'.$matches[1]; + } + } + + /** + * @return boolean true if column type if "numeric", "interval" or begins with "time". + */ + protected function isPrecisionType($type) + { + $type = strtolower(trim($type)); + return $type==='number'; // || $type==='interval' || strpos($type, 'time')===0; + } + + /** + * 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($schemaName, $tableName) + { + $this->getDbConnection()->setAttribute(PDO::ATTR_CASE, PDO::CASE_LOWER); +// select decode( a.CONSTRAINT_TYPE, 'P', 'PRIMARY KEY (', 'FOREIGN KEY (' )||b.COLUMN_NAME||')' as consrc, + $sql = +<<<EOD + select b.COLUMN_NAME as consrc, + a.CONSTRAINT_TYPE as contype + from ALL_CONSTRAINTS a, ALL_CONS_COLUMNS b + where (a.constraint_name = b.constraint_name AND a.table_name = b.table_name AND a.owner = b.owner) + and a.TABLE_NAME = '{$tableName}' + and a.OWNER = '{$schemaName}' + and a.CONSTRAINT_TYPE in ('P','R') +EOD; + $this->getDbConnection()->setActive(true); + $command = $this->getDbConnection()->createCommand($sql); + //$command->bindValue(':table', $tableName); + //$command->bindValue(':schema', $schemaName); + $primary = array(); + $foreign = array(); + foreach($command->query() as $row) + { + switch( strtolower( $row['contype'] ) ) + { + case 'p': + $primary = array_merge( $primary, array(strtolower( $row['consrc'] )) ); + /* + $arr = $this->getPrimaryKeys($row['consrc']); + $primary = array_merge( $primary, array(strtolower( $arr[0] )) ); + */ + break; + case 'r': + $foreign = array_merge( $foreign, array(strtolower( $row['consrc'] )) ); + /* + // if(($fkey = $this->getForeignKeys($row['consrc']))!==null) + $fkey = $this->getForeignKeys( $row['consrc'] ); + $foreign = array_merge( $foreign, array(strtolower( $fkey )) ); + */ + break; + } + } + return array($primary,$foreign); + } + + /** + * Gets the primary key field names + * @param string Oracle primary key definition + * @return array primary key field names. + */ + protected function getPrimaryKeys($src) + { + $matches = array(); + if(preg_match('/PRIMARY\s+KEY\s+\(([^\)]+)\)/i', $src, $matches)) + return preg_split('/,\s+/',$matches[1]); + return array(); + } + + /** + * Gets foreign relationship constraint keys and table name + * @param string Oracle foreign key definition + * @return array foreign relationship table name and keys, null otherwise + */ + protected function getForeignKeys($src) + { + $matches = array(); + $brackets = '\(([^\)]+)\)'; + $find = "/FOREIGN\s+KEY\s+{$brackets}\s+REFERENCES\s+([^\(]+){$brackets}/i"; + if(preg_match($find, $src, $matches)) + { + $keys = preg_split('/,\s+/', $matches[1]); + $fkeys = array(); + foreach(preg_split('/,\s+/', $matches[3]) as $i => $fkey) + $fkeys[$keys[$i]] = $fkey; + return array('table' => str_replace('"','',$matches[2]), 'keys' => $fkeys); + } + } + + /** + * @param string column name. + * @param TOracleTableInfo table information. + * @return boolean true if column is a foreign key. + */ + protected function isForeignKeyColumn($columnId, $tableInfo) + { + foreach($tableInfo->getForeignKeys() as $fk) + { + if( $fk==$columnId ) + //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='') + { + if($schema==='') + { + $sql=<<<EOD +SELECT table_name, '{$schema}' as table_schema FROM user_tables +EOD; + $command=$this->getDbConnection()->createCommand($sql); + } + else + { + $sql=<<<EOD +SELECT object_name as table_name, owner as table_schema FROM all_objects +WHERE object_type = 'TABLE' AND owner=:schema +EOD; + $command=$this->getDbConnection()->createCommand($sql); + $command->bindParam(':schema',$schema); + } + + $rows=$command->queryAll(); + $names=array(); + foreach($rows as $row) + { + if($schema===$this->getDefaultSchema() || $schema==='') + $names[]=$row['TABLE_NAME']; + else + $names[]=$row['TABLE_SCHEMA'].'.'.$row['TABLE_NAME']; + } + return $names; + } +}
\ No newline at end of file diff --git a/lib/prado/framework/Data/Common/Oracle/TOracleTableColumn.php b/lib/prado/framework/Data/Common/Oracle/TOracleTableColumn.php new file mode 100644 index 0000000..31a5e6c --- /dev/null +++ b/lib/prado/framework/Data/Common/Oracle/TOracleTableColumn.php @@ -0,0 +1,48 @@ +<?php +/** + * TOracleTableColumn class file. + * + * @author Marcos Nobre <marconobre[at]gmail[dot]com> + * @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.Oracle + */ + +/** + * Load common TDbTableCommon class. + */ +Prado::using('System.Data.Common.TDbTableColumn'); + +/** + * Describes the column metadata of the schema for a PostgreSQL database table. + * + * @author Marcos Nobre <marconobre[at]gmail[dot]com> + * @package System.Data.Common.Oracle + * @since 3.1 + */ +class TOracleTableColumn extends TDbTableColumn +{ + private static $types=array( + 'numeric' => array( 'numeric' ) +// 'integer' => array('bit', 'bit varying', 'real', 'serial', 'int', 'integer'), +// 'boolean' => array('boolean'), +// 'float' => array('bigint', 'bigserial', 'double precision', 'money', 'numeric') + ); + + /** + * Overrides parent implementation, returns PHP type from the db type. + * @return boolean derived PHP primitive type from the column db type. + */ + public function getPHPType() + { + $dbtype = strtolower($this->getDbType()); + foreach(self::$types as $type => $dbtypes) + { + if(in_array($dbtype, $dbtypes)) + return $type; + } + return 'string'; + } +} + diff --git a/lib/prado/framework/Data/Common/Oracle/TOracleTableInfo.php b/lib/prado/framework/Data/Common/Oracle/TOracleTableInfo.php new file mode 100644 index 0000000..f226562 --- /dev/null +++ b/lib/prado/framework/Data/Common/Oracle/TOracleTableInfo.php @@ -0,0 +1,156 @@ +<?php + +/** + * TOracleTableInfo class file. + * + * @author Marcos Nobre <marconobre[at]gmail[dot]com> + * @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 + */ + +/** + * TDbTableInfo class describes the meta data of a database table. + * + * @author Wei Zhuo <weizho[at]gmail[dot]com> + * @package System.Data.Common + * @since 3.1 + */ +class TOracleTableInfo extends TComponent +{ + private $_info=array(); + + private $_primaryKeys; + private $_foreignKeys; + + private $_columns; + + private $_lowercase; + + /** + * Sets the database table meta data information. + * @param array table column information. + */ + public function __construct($tableInfo=array(),$primary=array(),$foreign=array()) + { + $this->_info=$tableInfo; + $this->_primaryKeys=$primary; + $this->_foreignKeys=$foreign; + $this->_columns=new TMap; + } + + /** + * @param TDbConnection database connection. + * @return TDbCommandBuilder new command builder + */ + public function createCommandBuilder($connection) + { + Prado::using('System.Data.Common.Oracle.TOracleCommandBuilder'); + return new TOracleCommandBuilder($connection,$this); + } + + /** + * @param string information array key name + * @param mixed default value if information array value is null + * @return mixed information array value. + */ + public function getInfo($name,$default=null) + { + return isset($this->_info[$name]) ? $this->_info[$name] : $default; + } + + /** + * @param string information array key name + * @param mixed new information array value. + */ + protected function setInfo($name,$value) + { + $this->_info[$name]=$value; + } + + /** + * @return string name of the table this column belongs to. + */ + public function getTableName() + { + return $this->getInfo('TableName'); + } + + /** + * @return string full name of the table, database dependent. + */ + public function getTableFullName() + { + return $this->_info['SchemaName'].'.'.$this->getTableName(); + } + + /** + * @return boolean whether the table is a view, default is false. + */ + public function getIsView() + { + return $this->getInfo('IsView',false); + } + + /** + * @return TMap TDbTableColumn column meta data. + */ + public function getColumns() + { + return $this->_columns; + } + + /** + * @param string column id + * @return TDbTableColumn column information. + */ + public function getColumn($name) + { + if(($column = $this->_columns->itemAt($name))!==null) + return $column; + throw new TDbException('dbtableinfo_invalid_column_name', $name, $this->getTableFullName()); + } + + /** + * @param array list of column Id, empty to get all columns. + * @return array table column names (identifier quoted) + */ + public function getColumnNames() + { + foreach($this->getColumns() as $column) + $names[] = $column->getColumnName(); + return $names; + } + + /** + * @return string[] names of primary key columns. + */ + public function getPrimaryKeys() + { + return $this->_primaryKeys; + } + + /** + * @return array tuples of foreign table and column name. + */ + public function getForeignKeys() + { + return $this->_foreignKeys; + } + + /** + * @return array lowercased column key names mapped to normal column ids. + */ + public function getLowerCaseColumnNames() + { + if($this->_lowercase===null) + { + $this->_lowercase=array(); + foreach($this->getColumns()->getKeys() as $key) + $this->_lowercase[strtolower($key)] = $key; + } + return $this->_lowercase; + } +} + diff --git a/lib/prado/framework/Data/Common/Pgsql/TPgsqlCommandBuilder.php b/lib/prado/framework/Data/Common/Pgsql/TPgsqlCommandBuilder.php new file mode 100644 index 0000000..5e219ad --- /dev/null +++ b/lib/prado/framework/Data/Common/Pgsql/TPgsqlCommandBuilder.php @@ -0,0 +1,67 @@ +<?php +/** + * TPgsqlCommandBuilder class file. + * + * @author Wei Zhuo <weizhuo[at]gmail[dot]com> + * @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'); + +/** + * TPgsqlCommandBuilder provides specifics methods to create limit/offset query commands + * for Pgsql database. + * + * @author Wei Zhuo <weizho[at]gmail[dot]com> + * @package System.Data.Common + * @since 3.1 + */ +class TPgsqlCommandBuilder extends TDbCommandBuilder +{ + /** + * Overrides parent implementation. Only column of type text or character (and its variants) + * accepts the LIKE criteria. + * @param array list of column id for potential search condition. + * @param string string of keywords + * @return string SQL search condition matching on a set of columns. + */ + public function getSearchExpression($fields, $keywords) + { + $columns = array(); + foreach($fields as $field) + { + if($this->isSearchableColumn($this->getTableInfo()->getColumn($field))) + $columns[] = $field; + } + return parent::getSearchExpression($columns, $keywords); + } + /** + * + * @return boolean true if column can be used for LIKE searching. + */ + protected function isSearchableColumn($column) + { + $type = strtolower($column->getDbType()); + return $type === 'character varying' || $type === 'varchar' || + $type === 'character' || $type === 'char' || $type === 'text'; + } + + /** + * Overrides parent implementation to use PostgreSQL's ILIKE instead of LIKE (case-sensitive). + * @param string column name. + * @param array keywords + * @return string search condition for all words in one column. + */ + protected function getSearchCondition($column, $words) + { + $conditions=array(); + foreach($words as $word) + $conditions[] = $column.' ILIKE '.$this->getDbConnection()->quoteString('%'.$word.'%'); + return '('.implode(' AND ', $conditions).')'; + } + +} + diff --git a/lib/prado/framework/Data/Common/Pgsql/TPgsqlMetaData.php b/lib/prado/framework/Data/Common/Pgsql/TPgsqlMetaData.php new file mode 100644 index 0000000..3001cf4 --- /dev/null +++ b/lib/prado/framework/Data/Common/Pgsql/TPgsqlMetaData.php @@ -0,0 +1,448 @@ +<?php +/** + * TPgsqlMetaData class file. + * + * @author Wei Zhuo <weizhuo[at]gmail[dot]com> + * @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.Pgsql + */ + +/** + * Load the base TDbMetaData class. + */ +Prado::using('System.Data.Common.TDbMetaData'); +Prado::using('System.Data.Common.Pgsql.TPgsqlTableInfo'); + +/** + * TPgsqlMetaData loads PostgreSQL database table and column information. + * + * @author Wei Zhuo <weizho[at]gmail[dot]com> + * @package System.Data.Common.Pgsql + * @since 3.1 + */ +class TPgsqlMetaData extends TDbMetaData +{ + private $_defaultSchema = 'public'; + + /** + * @return string TDbTableInfo class name. + */ + protected function getTableInfoClass() + { + return 'TPgsqlTableInfo'; + } + + /** + * 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, '"', '"'); + } + + /** + * @param string default schema. + */ + public function setDefaultSchema($schema) + { + $this->_defaultSchema=$schema; + } + + /** + * @return string default schema. + */ + public function getDefaultSchema() + { + return $this->_defaultSchema; + } + + /** + * @param string table name with optional schema name prefix, uses default schema name prefix is not provided. + * @return array tuple as ($schemaName,$tableName) + */ + protected function getSchemaTableName($table) + { + if(count($parts= explode('.', str_replace('"','',$table))) > 1) + return array($parts[0], $parts[1]); + else + return array($this->getDefaultSchema(),$parts[0]); + } + + /** + * Get the column definitions for given table. + * @param string table name. + * @return TPgsqlTableInfo table information. + */ + protected function createTableInfo($table) + { + list($schemaName,$tableName) = $this->getSchemaTableName($table); + + // This query is made much more complex by the addition of the 'attisserial' field. + // The subquery to get that field checks to see if there is an internally dependent + // sequence on the field. + $sql = +<<<EOD + SELECT + a.attname, + pg_catalog.format_type(a.atttypid, a.atttypmod) as type, + a.atttypmod, + a.attnotnull, a.atthasdef, adef.adsrc, + ( + SELECT 1 FROM pg_catalog.pg_depend pd, pg_catalog.pg_class pc + WHERE pd.objid=pc.oid + AND pd.classid=pc.tableoid + AND pd.refclassid=pc.tableoid + AND pd.refobjid=a.attrelid + AND pd.refobjsubid=a.attnum + AND pd.deptype='i' + AND pc.relkind='S' + ) IS NOT NULL AS attisserial + + FROM + pg_catalog.pg_attribute a LEFT JOIN pg_catalog.pg_attrdef adef + ON a.attrelid=adef.adrelid + AND a.attnum=adef.adnum + LEFT JOIN pg_catalog.pg_type t ON a.atttypid=t.oid + WHERE + a.attrelid = (SELECT oid FROM pg_catalog.pg_class WHERE relname=:table + AND relnamespace = (SELECT oid FROM pg_catalog.pg_namespace WHERE + nspname = :schema)) + AND a.attnum > 0 AND NOT a.attisdropped + ORDER BY a.attnum +EOD; + $this->getDbConnection()->setActive(true); + $command = $this->getDbConnection()->createCommand($sql); + $command->bindValue(':table', $tableName); + $command->bindValue(':schema', $schemaName); + $tableInfo = $this->createNewTableInfo($schemaName, $tableName); + $index=0; + foreach($command->query() as $col) + { + $col['index'] = $index++; + $this->processColumn($tableInfo, $col); + } + if($index===0) + throw new TDbException('dbmetadata_invalid_table_view', $table); + return $tableInfo; + } + + /** + * @param string table schema name + * @param string table name. + * @return TPgsqlTableInfo + */ + protected function createNewTableInfo($schemaName,$tableName) + { + $info['SchemaName'] = $this->assertIdentifier($schemaName); + $info['TableName'] = $this->assertIdentifier($tableName); + if($this->getIsView($schemaName,$tableName)) + $info['IsView'] = true; + list($primary, $foreign) = $this->getConstraintKeys($schemaName, $tableName); + $class = $this->getTableInfoClass(); + return new $class($info,$primary,$foreign); + } + + /** + * @param string table name, schema name or column name. + * @return string a valid identifier. + * @throws TDbException when table name contains a double quote ("). + */ + protected function assertIdentifier($name) + { + if(strpos($name, '"')!==false) + { + $ref = 'http://www.postgresql.org/docs/7.4/static/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS'; + throw new TDbException('dbcommon_invalid_identifier_name', $name, $ref); + } + return $name; + } + + /** + * @param string table schema name + * @param string table name. + * @return boolean true if the table is a view. + */ + protected function getIsView($schemaName,$tableName) + { + $sql = +<<<EOD + SELECT count(c.relname) FROM pg_catalog.pg_class c + LEFT JOIN pg_catalog.pg_namespace n ON (n.oid = c.relnamespace) + WHERE (n.nspname=:schema) AND (c.relkind = 'v'::"char") AND c.relname = :table +EOD; + $this->getDbConnection()->setActive(true); + $command = $this->getDbConnection()->createCommand($sql); + $command->bindValue(':schema',$schemaName); + $command->bindValue(':table', $tableName); + return intval($command->queryScalar()) === 1; + } + + /** + * @param TPgsqlTableInfo table information. + * @param array column information. + */ + protected function processColumn($tableInfo, $col) + { + $columnId = $col['attname']; //use column name as column Id + + $info['ColumnName'] = '"'.$columnId.'"'; //quote the column names! + $info['ColumnId'] = $columnId; + $info['ColumnIndex'] = $col['index']; + if(!$col['attnotnull']) + $info['AllowNull'] = true; + if(in_array($columnId, $tableInfo->getPrimaryKeys())) + $info['IsPrimaryKey'] = true; + if($this->isForeignKeyColumn($columnId, $tableInfo)) + $info['IsForeignKey'] = true; + + if($col['atttypmod'] > 0) + $info['ColumnSize'] = $col['atttypmod'] - 4; + if($col['atthasdef']) + $info['DefaultValue'] = $col['adsrc']; + if($col['attisserial'] || substr($col['adsrc'],0,8) === 'nextval(') + { + if(($sequence = $this->getSequenceName($tableInfo, $col['adsrc']))!==null) + { + $info['SequenceName'] = $sequence; + unset($info['DefaultValue']); + } + } + $matches = array(); + if(preg_match('/\((\d+)(?:,(\d+))?+\)/', $col['type'], $matches)) + { + $info['DbType'] = preg_replace('/\(\d+(?:,\d+)?\)/','',$col['type']); + if($this->isPrecisionType($info['DbType'])) + { + $info['NumericPrecision'] = intval($matches[1]); + if(count($matches) > 2) + $info['NumericScale'] = intval($matches[2]); + } + else + $info['ColumnSize'] = intval($matches[1]); + } + else + $info['DbType'] = $col['type']; + + $tableInfo->Columns[$columnId] = new TPgsqlTableColumn($info); + } + + /** + * @return string serial name if found, null otherwise. + */ + protected function getSequenceName($tableInfo,$src) + { + $matches = array(); + if(preg_match('/nextval\([^\']*\'([^\']+)\'[^\)]*\)/i',$src,$matches)) + { + if(is_int(strpos($matches[1], '.'))) + return $matches[1]; + else + return $tableInfo->getSchemaName().'.'.$matches[1]; + } + } + + /** + * @return boolean true if column type if "numeric", "interval" or begins with "time". + */ + protected function isPrecisionType($type) + { + $type = strtolower(trim($type)); + return $type==='numeric' || $type==='interval' || strpos($type, 'time')===0; + } + + /** + * 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($schemaName, $tableName) + { + $sql = +<<<EOD + SELECT conname, consrc, contype, indkey, indisclustered FROM ( + SELECT + conname, + CASE WHEN contype='f' THEN + pg_catalog.pg_get_constraintdef(oid) + ELSE + 'CHECK (' || consrc || ')' + END AS consrc, + contype, + conrelid AS relid, + NULL AS indkey, + FALSE AS indisclustered + FROM + pg_catalog.pg_constraint + WHERE + contype IN ('f', 'c') + UNION ALL + SELECT + pc.relname, + NULL, + CASE WHEN indisprimary THEN + 'p' + ELSE + 'u' + END, + pi.indrelid, + indkey, + pi.indisclustered + FROM + pg_catalog.pg_class pc, + pg_catalog.pg_index pi + WHERE + pc.oid=pi.indexrelid + AND EXISTS ( + SELECT 1 FROM pg_catalog.pg_depend d JOIN pg_catalog.pg_constraint c + ON (d.refclassid = c.tableoid AND d.refobjid = c.oid) + WHERE d.classid = pc.tableoid AND d.objid = pc.oid AND d.deptype = 'i' AND c.contype IN ('u', 'p') + ) + ) AS sub + WHERE relid = (SELECT oid FROM pg_catalog.pg_class WHERE relname=:table + AND relnamespace = (SELECT oid FROM pg_catalog.pg_namespace + WHERE nspname=:schema)) + ORDER BY + 1 +EOD; + $this->getDbConnection()->setActive(true); + $command = $this->getDbConnection()->createCommand($sql); + $command->bindValue(':table', $tableName); + $command->bindValue(':schema', $schemaName); + $primary = array(); + $foreign = array(); + foreach($command->query() as $row) + { + switch($row['contype']) + { + case 'p': + $primary = $this->getPrimaryKeys($tableName, $schemaName, $row['indkey']); + break; + case 'f': + if(($fkey = $this->getForeignKeys($row['consrc']))!==null) + $foreign[] = $fkey; + break; + } + } + return array($primary,$foreign); + } + + /** + * Gets the primary key field names + * @param string pgsql primary key definition + * @return array primary key field names. + */ + protected function getPrimaryKeys($tableName, $schemaName, $columnIndex) + { + $index = join(', ', explode(' ', $columnIndex)); + $sql = +<<<EOD + SELECT attnum, attname FROM pg_catalog.pg_attribute WHERE + attrelid=( + SELECT oid FROM pg_catalog.pg_class WHERE relname=:table AND relnamespace=( + SELECT oid FROM pg_catalog.pg_namespace WHERE nspname=:schema + ) + ) + AND attnum IN ({$index}) +EOD; + $command = $this->getDbConnection()->createCommand($sql); + $command->bindValue(':table', $tableName); + $command->bindValue(':schema', $schemaName); +// $command->bindValue(':columnIndex', join(', ', explode(' ', $columnIndex))); + $primary = array(); + foreach($command->query() as $row) + { + $primary[] = $row['attname']; + } + + return $primary; + } + + /** + * Gets foreign relationship constraint keys and table name + * @param string pgsql foreign key definition + * @return array foreign relationship table name and keys, null otherwise + */ + protected function getForeignKeys($src) + { + $matches = array(); + $brackets = '\(([^\)]+)\)'; + $find = "/FOREIGN\s+KEY\s+{$brackets}\s+REFERENCES\s+([^\(]+){$brackets}/i"; + if(preg_match($find, $src, $matches)) + { + $keys = preg_split('/,\s+/', $matches[1]); + $fkeys = array(); + foreach(preg_split('/,\s+/', $matches[3]) as $i => $fkey) + $fkeys[$keys[$i]] = $fkey; + return array('table' => str_replace('"','',$matches[2]), 'keys' => $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='public') + { + if($schema==='') + $schema=self::DEFAULT_SCHEMA; + $sql=<<<EOD +SELECT table_name, table_schema FROM information_schema.tables +WHERE table_schema=:schema AND table_type='BASE TABLE' +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[]=$row['table_schema'].'.'.$row['table_name']; + } + return $names; + } +} + diff --git a/lib/prado/framework/Data/Common/Pgsql/TPgsqlTableColumn.php b/lib/prado/framework/Data/Common/Pgsql/TPgsqlTableColumn.php new file mode 100644 index 0000000..efe516e --- /dev/null +++ b/lib/prado/framework/Data/Common/Pgsql/TPgsqlTableColumn.php @@ -0,0 +1,47 @@ +<?php +/** + * TPgsqlTableColumn class file. + * + * @author Wei Zhuo <weizhuo[at]gmail[dot]com> + * @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.Pgsql + */ + +/** + * Load common TDbTableCommon class. + */ +Prado::using('System.Data.Common.TDbTableColumn'); + +/** + * Describes the column metadata of the schema for a PostgreSQL database table. + * + * @author Wei Zhuo <weizho[at]gmail[dot]com> + * @package System.Data.Common.Pgsql + * @since 3.1 + */ +class TPgsqlTableColumn extends TDbTableColumn +{ + private static $types=array( + 'integer' => array('bit', 'bit varying', 'real', 'serial', 'int', 'integer'), + 'boolean' => array('boolean'), + 'float' => array('bigint', 'bigserial', 'double precision', 'money', 'numeric') + ); + + /** + * Overrides parent implementation, returns PHP type from the db type. + * @return boolean derived PHP primitive type from the column db type. + */ + public function getPHPType() + { + $dbtype = strtolower($this->getDbType()); + foreach(self::$types as $type => $dbtypes) + { + if(in_array($dbtype, $dbtypes)) + return $type; + } + return 'string'; + } +} + diff --git a/lib/prado/framework/Data/Common/Pgsql/TPgsqlTableInfo.php b/lib/prado/framework/Data/Common/Pgsql/TPgsqlTableInfo.php new file mode 100644 index 0000000..aef09fa --- /dev/null +++ b/lib/prado/framework/Data/Common/Pgsql/TPgsqlTableInfo.php @@ -0,0 +1,56 @@ +<?php +/** + * TPgsqlTableInfo class file. + * + * @author Wei Zhuo <weizhuo[at]gmail[dot]com> + * @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.Pgsql + */ + +/** + * Loads the base TDbTableInfo class and TPgsqlTableColumn class. + */ +Prado::using('System.Data.Common.TDbTableInfo'); +Prado::using('System.Data.Common.Pgsql.TPgsqlTableColumn'); + +/** + * TPgsqlTableInfo class provides additional table information for PostgreSQL database. + * + * @author Wei Zhuo <weizho[at]gmail[dot]com> + * @package System.Data.Common.Pgsql + * @since 3.1 + */ +class TPgsqlTableInfo extends TDbTableInfo +{ + /** + * @return string name of the schema this column belongs to. + */ + public function getSchemaName() + { + return $this->getInfo('SchemaName'); + } + + /** + * @return string full name of the table, database dependent. + */ + public function getTableFullName() + { + if(($schema=$this->getSchemaName())!==null) + return $schema.'.'.$this->getTableName(); + else + return $this->getTableName(); + } + + /** + * @param TDbConnection database connection. + * @return TDbCommandBuilder new command builder + */ + public function createCommandBuilder($connection) + { + Prado::using('System.Data.Common.Pgsql.TPgsqlCommandBuilder'); + return new TPgsqlCommandBuilder($connection,$this); + } +} + diff --git a/lib/prado/framework/Data/Common/Sqlite/TSqliteCommandBuilder.php b/lib/prado/framework/Data/Common/Sqlite/TSqliteCommandBuilder.php new file mode 100644 index 0000000..970ede4 --- /dev/null +++ b/lib/prado/framework/Data/Common/Sqlite/TSqliteCommandBuilder.php @@ -0,0 +1,45 @@ +<?php +/** + * TSqliteCommandBuilder class file. + * + * @author Wei Zhuo <weizhuo[at]gmail[dot]com> + * @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'); + +/** + * TSqliteCommandBuilder provides specifics methods to create limit/offset query commands + * for Sqlite database. + * + * @author Wei Zhuo <weizho[at]gmail[dot]com> + * @package System.Data.Common + * @since 3.1 + */ +class TSqliteCommandBuilder extends TDbCommandBuilder +{ + /** + * Alters the sql to apply $limit and $offset. + * @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) + { + $limitStr = ' LIMIT '.$limit; + $offsetStr = $offset >= 0 ? ' OFFSET '.$offset : ''; + return $sql.$limitStr.$offsetStr; + } + else + return $sql; + } +} + diff --git a/lib/prado/framework/Data/Common/Sqlite/TSqliteMetaData.php b/lib/prado/framework/Data/Common/Sqlite/TSqliteMetaData.php new file mode 100644 index 0000000..a2fe870 --- /dev/null +++ b/lib/prado/framework/Data/Common/Sqlite/TSqliteMetaData.php @@ -0,0 +1,202 @@ +<?php +/** + * TSqliteMetaData class file. + * + * @author Wei Zhuo <weizhuo[at]gmail[dot]com> + * @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.Sqlite + */ + +/** + * Load the base TDbMetaData class. + */ +Prado::using('System.Data.Common.TDbMetaData'); +Prado::using('System.Data.Common.Sqlite.TSqliteTableInfo'); + +/** + * TSqliteMetaData loads SQLite database table and column information. + * + * @author Wei Zhuo <weizho[at]gmail[dot]com> + * @package System.Data.Commom.Sqlite + * @since 3.1 + */ +class TSqliteMetaData extends TDbMetaData +{ + /** + * @return string TDbTableInfo class name. + */ + protected function getTableInfoClass() + { + return 'TSqliteTableInfo'; + } + + /** + * 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 TPgsqlTableInfo table information. + */ + protected function createTableInfo($tableName) + { + $tableName = str_replace("'",'',$tableName); + $this->getDbConnection()->setActive(true); + $table = $this->getDbConnection()->quoteString($tableName); + $sql = "PRAGMA table_info({$table})"; + $command = $this->getDbConnection()->createCommand($sql); + $foreign = $this->getForeignKeys($table); + $index=0; + $columns=array(); + $primary=array(); + foreach($command->query() as $col) + { + $col['index'] = $index++; + $column = $this->processColumn($col, $foreign); + $columns[$col['name']] = $column; + if($column->getIsPrimaryKey()) + $primary[] = $col['name']; + } + $info['TableName'] = $tableName; + if($this->getIsView($tableName)) + $info['IsView'] = true; + if(count($columns)===0) + throw new TDbException('dbmetadata_invalid_table_view', $tableName); + $class = $this->getTableInfoClass(); + $tableInfo = new $class($info,$primary,$foreign); + $tableInfo->getColumns()->copyFrom($columns); + return $tableInfo; + } + + /** + * @param string table name. + * @return boolean true if the table is a view. + */ + protected function getIsView($tableName) + { + $sql = 'SELECT count(*) FROM sqlite_master WHERE type="view" AND name= :table'; + $this->getDbConnection()->setActive(true); + $command = $this->getDbConnection()->createCommand($sql); + $command->bindValue(':table', $tableName); + return intval($command->queryScalar()) === 1; + } + + /** + * @param array column information. + * @param array foreign key details. + * @return TSqliteTableColumn column details. + */ + protected function processColumn($col, $foreign) + { + $columnId = $col['name']; //use column name as column Id + + $info['ColumnName'] = '"'.$columnId.'"'; //quote the column names! + $info['ColumnId'] = $columnId; + $info['ColumnIndex'] = $col['index']; + + if($col['notnull']!=='99') + $info['AllowNull'] = true; + + if($col['pk']==='1') + $info['IsPrimaryKey'] = true; + if($this->isForeignKeyColumn($columnId, $foreign)) + $info['IsForeignKey'] = true; + + if($col['dflt_value']!==null) + $info['DefaultValue'] = $col['dflt_value']; + + $type = strtolower($col['type']); + $info['AutoIncrement'] = $type==='integer' && $col['pk']==='1'; + + $info['DbType'] = $type; + $match=array(); + if(is_int($pos=strpos($type, '(')) && preg_match('/\((.*)\)/', $type, $match)) + { + $ps = explode(',', $match[1]); + if(count($ps)===2) + { + $info['NumericPrecision'] = intval($ps[0]); + $info['NumericScale'] = intval($ps[1]); + } + else + $info['ColumnSize']=intval($match[1]); + $info['DbType'] = substr($type,0,$pos); + } + + return new TSqliteTableColumn($info); + } + + /** + * + * + * @param string quoted table name. + * @return array foreign key details. + */ + protected function getForeignKeys($table) + { + $sql = "PRAGMA foreign_key_list({$table})"; + $command = $this->getDbConnection()->createCommand($sql); + $fkeys = array(); + foreach($command->query() as $col) + { + $fkeys[$col['table']]['keys'][$col['from']] = $col['to']; + $fkeys[$col['table']]['table'] = $col['table']; + } + return count($fkeys) > 0 ? array_values($fkeys) : $fkeys; + } + + /** + * @param string column name. + * @param array foreign key column names. + * @return boolean true if column is a foreign key. + */ + protected function isForeignKeyColumn($columnId, $foreign) + { + foreach($foreign 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. This is not used for sqlite database. + * @return array all table names in the database. + */ + public function findTableNames($schema='') + { + $sql="SELECT DISTINCT tbl_name FROM sqlite_master WHERE tbl_name<>'sqlite_sequence'"; + return $this->getDbConnection()->createCommand($sql)->queryColumn(); + } +}
\ No newline at end of file diff --git a/lib/prado/framework/Data/Common/Sqlite/TSqliteTableColumn.php b/lib/prado/framework/Data/Common/Sqlite/TSqliteTableColumn.php new file mode 100644 index 0000000..e58ec85 --- /dev/null +++ b/lib/prado/framework/Data/Common/Sqlite/TSqliteTableColumn.php @@ -0,0 +1,62 @@ +<?php +/** + * TSqliteTableColumn class file. + * + * @author Wei Zhuo <weizhuo[at]gmail[dot]com> + * @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.Sqlite + */ + +/** + * Load common TDbTableCommon class. + */ +Prado::using('System.Data.Common.TDbTableColumn'); + +/** + * Describes the column metadata of the schema for a PostgreSQL database table. + * + * @author Wei Zhuo <weizho[at]gmail[dot]com> + * @package System.Data.Common.Sqlite + * @since 3.1 + */ +class TSqliteTableColumn extends TDbTableColumn +{ + /** + * @TODO add sqlite types. + */ + 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() + { + $dbtype = strtolower($this->getDbType()); + foreach(self::$types as $type => $dbtypes) + { + if(in_array($dbtype, $dbtypes)) + return $type; + } + return 'string'; + } + + /** + * @return boolean true if column will auto-increment when the column value is inserted as null. + */ + public function getAutoIncrement() + { + return $this->getInfo('AutoIncrement', false); + } + + /** + * @return boolean true if auto increment is true. + */ + public function hasSequence() + { + return $this->getAutoIncrement(); + } +} + diff --git a/lib/prado/framework/Data/Common/Sqlite/TSqliteTableInfo.php b/lib/prado/framework/Data/Common/Sqlite/TSqliteTableInfo.php new file mode 100644 index 0000000..6c047e4 --- /dev/null +++ b/lib/prado/framework/Data/Common/Sqlite/TSqliteTableInfo.php @@ -0,0 +1,45 @@ +<?php +/** + * TSqliteTableInfo class file. + * + * @author Wei Zhuo <weizhuo[at]gmail[dot]com> + * @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.Sqlite + */ + +/** + * Loads the base TDbTableInfo class and TSqliteTableColumn class. + */ +Prado::using('System.Data.Common.TDbTableInfo'); +Prado::using('System.Data.Common.Sqlite.TSqliteTableColumn'); + +/** + * TSqliteTableInfo class provides additional table information for PostgreSQL database. + * + * @author Wei Zhuo <weizho[at]gmail[dot]com> + * @package System.Data.Common.Sqlite + * @since 3.1 + */ +class TSqliteTableInfo extends TDbTableInfo +{ + /** + * @param TDbConnection database connection. + * @return TDbCommandBuilder new command builder + */ + public function createCommandBuilder($connection) + { + Prado::using('System.Data.Common.Sqlite.TSqliteCommandBuilder'); + return new TSqliteCommandBuilder($connection,$this); + } + + /** + * @return string full name of the table, database dependent. + */ + public function getTableFullName() + { + return "'".$this->getTableName()."'"; + } +} + diff --git a/lib/prado/framework/Data/Common/TDbCommandBuilder.php b/lib/prado/framework/Data/Common/TDbCommandBuilder.php new file mode 100644 index 0000000..e1d476e --- /dev/null +++ b/lib/prado/framework/Data/Common/TDbCommandBuilder.php @@ -0,0 +1,505 @@ +<?php +/** + * TDbCommandBuilder class file. + * + * @author Wei Zhuo <weizhuo[at]gmail[dot]com> + * @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 + */ + +/** + * 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> + * @package System.Data.Common + * @since 3.1 + */ +class TDbCommandBuilder extends TComponent +{ + private $_connection; + private $_tableInfo; + + /** + * @param TDbConnection database connection. + * @param TDbTableInfo table information. + */ + public function __construct($connection=null, $tableInfo=null) + { + $this->setDbConnection($connection); + $this->setTableInfo($tableInfo); + } + + /** + * @return TDbConnection database connection. + */ + public function getDbConnection() + { + return $this->_connection; + } + + /** + * @param TDbConnection database connection. + */ + public function setDbConnection($value) + { + $this->_connection=$value; + } + + /** + * @param TDbTableInfo table information. + */ + public function setTableInfo($value) + { + $this->_tableInfo=$value; + } + + /** + * @param TDbTableInfo table information. + */ + public function getTableInfo() + { + return $this->_tableInfo; + } + + /** + * Iterate through all the columns and returns the last insert id of the + * first column that has a sequence or serial. + * @return mixed last insert id, null if none is found. + */ + public function getLastInsertID() + { + foreach($this->getTableInfo()->getColumns() as $column) + { + if($column->hasSequence()) + return $this->getDbConnection()->getLastInsertID($column->getSequenceName()); + } + } + + /** + * Alters the sql to apply $limit and $offset. Default implementation is applicable + * for PostgreSQL, MySQL and SQLite. + * @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 ? (int)$limit : -1; + $offset = $offset!==null ? (int)$offset : -1; + $limitStr = $limit >= 0 ? ' LIMIT '.$limit : ''; + $offsetStr = $offset >= 0 ? ' OFFSET '.$offset : ''; + return $sql.$limitStr.$offsetStr; + } + + /** + * @param string SQL string without existing ordering. + * @param array pairs of column names as key and direction as value. + * @return string modified SQL applied with ORDER BY. + */ + public function applyOrdering($sql, $ordering) + { + $orders=array(); + foreach($ordering as $name => $direction) + { + $direction = strtolower($direction) == 'desc' ? 'DESC' : 'ASC'; + if(false !== strpos($name, '(') && false !== strpos($name, ')')) { + // key is a function (bad practice, but we need to handle it) + $key = $name; + } else { + // key is a column + $key = $this->getTableInfo()->getColumn($name)->getColumnName(); + } + $orders[] = $key.' '.$direction; + } + if(count($orders) > 0) + $sql .= ' ORDER BY '.implode(', ', $orders); + return $sql; + } + + /** + * Computes the SQL condition for search a set of column using regular expression + * (or LIKE, depending on database implementation) to match a string of + * keywords (default matches all keywords). + * @param array list of column id for potential search condition. + * @param string string of keywords + * @return string SQL search condition matching on a set of columns. + */ + public function getSearchExpression($fields, $keywords) + { + if(strlen(trim($keywords)) == 0) return ''; + $words = preg_split('/\s/u', $keywords); + $conditions = array(); + foreach($fields as $field) + { + $column = $this->getTableInfo()->getColumn($field)->getColumnName(); + $conditions[] = $this->getSearchCondition($column, $words); + } + return '('.implode(' OR ', $conditions).')'; + } + + /** + * @param string column name. + * @param array keywords + * @return string search condition for all words in one column. + */ + protected function getSearchCondition($column, $words) + { + $conditions=array(); + foreach($words as $word) + $conditions[] = $column.' LIKE '.$this->getDbConnection()->quoteString('%'.$word.'%'); + return '('.implode(' AND ', $conditions).')'; + } + + /** + * + * Different behavior depends on type of passed data + * string + * usage without modification + * + * null + * will be expanded to full list of quoted table column names (quoting depends on database) + * + * array + * - Column names will be quoted if used as key or value of array + * <code> + * array('col1', 'col2', 'col2') + * // SELECT `col1`, `col2`, `col3` FROM... + * </code> + * + * - Column aliasing + * <code> + * array('mycol1' => 'col1', 'mycol2' => 'COUNT(*)') + * // SELECT `col1` AS mycol1, COUNT(*) AS mycol2 FROM... + * </code> + * + * - NULL and scalar values (strings will be quoted depending on database) + * <code> + * array('col1' => 'my custom string', 'col2' => 1.0, 'col3' => 'NULL') + * // SELECT "my custom string" AS `col1`, 1.0 AS `col2`, NULL AS `col3` FROM... + * </code> + * + * - If the *-wildcard char is used as key or value, add the full list of quoted table column names + * <code> + * array('col1' => 'NULL', '*') + * // SELECT `col1`, `col2`, `col3`, NULL AS `col1` FROM... + * </code> + * @param mixed $value + * @return array of generated fields - use implode(', ', $selectfieldlist) to collapse field list for usage + * @since 3.1.7 + * @todo add support for table aliasing + * @todo add support for quoting of column aliasing + */ + public function getSelectFieldList($data='*') { + if(is_scalar($data)) { + $tmp = explode(',', $data); + $result = array(); + foreach($tmp as $v) + $result[] = trim($v); + return $result; + } + + $bHasWildcard = false; + $result = array(); + if(is_array($data) || $data instanceof Traversable) { + $columns = $this->getTableInfo()->getColumns(); + foreach($data as $key=>$value) { + if($key==='*' || $value==='*') { + $bHasWildcard = true; + continue; + } + + if(strToUpper($key)==='NULL') { + $result[] = 'NULL'; + continue; + } + + if(strpos($key, '(')!==false && strpos($key, ')')!==false) { + $result[] = $key; + continue; + } + + if(stripos($key, 'AS')!==false) { + $result[] = $key; + continue; + } + + if(stripos($value, 'AS')!==false) { + $result[] = $value; + continue; + } + + $v = isset($columns[$value]); + $k = isset($columns[$key]); + if(is_integer($key) && $v) { + $key = $value; + $k = $v; + } + + if(strToUpper($value)==='NULL') { + if($k) + $result[] = 'NULL AS ' . $columns[$key]->getColumnName(); + else + $result[] = 'NULL' . (is_string($key) ? (' AS ' . (string)$key) : ''); + continue; + } + + if(strpos($value, '(')!==false && strpos($value, ')')!==false) { + if($k) + $result[] = $value . ' AS ' . $columns[$key]->getColumnName(); + else + $result[] = $value . (is_string($key) ? (' AS ' . (string)$key) : ''); + continue; + } + + if($v && $key==$value) { + $result[] = $columns[$value]->getColumnName(); + continue; + } + + if($k && $value==null) { + $result[] = $columns[$key]->getColumnName(); + continue; + } + + if(is_string($key) && $v) { + $result[] = $columns[$value]->getColumnName() . ' AS ' . $key; + continue; + } + + if(is_numeric($value) && $k) { + $result[] = $value . ' AS ' . $columns[$key]->getColumnName(); + continue; + } + + if(is_string($value) && $k) { + $result[] = $this->getDbConnection()->quoteString($value) . ' AS ' . $columns[$key]->getColumnName(); + continue; + } + + if(!$v && !$k && is_integer($key)) { + $result[] = is_numeric($value) ? $value : $this->getDbConnection()->quoteString((string)$value); + continue; + } + + $result[] = (is_numeric($value) ? $value : $this->getDbConnection()->quoteString((string)$value)) . ' AS ' . $key; + } + } + + if($data===null || count($result) == 0 || $bHasWildcard) + $result = $result = array_merge($this->getTableInfo()->getColumnNames(), $result); + + return $result; + } + + /** + * Appends the $where condition to the string "SELECT * FROM tableName WHERE ". + * The tableName is obtained from the {@link setTableInfo TableInfo} property. + * @param string query condition + * @param array condition parameters. + * @return TDbCommand query command. + */ + public function createFindCommand($where='1=1', $parameters=array(), $ordering=array(), $limit=-1, $offset=-1, $select='*') + { + $table = $this->getTableInfo()->getTableFullName(); + $fields = implode(', ', $this -> getSelectFieldList($select)); + $sql = "SELECT {$fields} FROM {$table}"; + if(!empty($where)) + $sql .= " WHERE {$where}"; + return $this->applyCriterias($sql, $parameters, $ordering, $limit, $offset); + } + + public function applyCriterias($sql, $parameters=array(),$ordering=array(), $limit=-1, $offset=-1) + { + if(count($ordering) > 0) + $sql = $this->applyOrdering($sql, $ordering); + if($limit>=0 || $offset>=0) + $sql = $this->applyLimitOffset($sql, $limit, $offset); + $command = $this->createCommand($sql); + $this->bindArrayValues($command, $parameters); + return $command; + } + + /** + * Creates a count(*) command for the table described in {@link setTableInfo TableInfo}. + * @param string count condition. + * @param array binding parameters. + * @return TDbCommand count command. + */ + public function createCountCommand($where='1=1', $parameters=array(),$ordering=array(), $limit=-1, $offset=-1) + { + return $this->createFindCommand($where, $parameters, $ordering, $limit, $offset, 'COUNT(*)'); + } + + /** + * Creates a delete command for the table described in {@link setTableInfo TableInfo}. + * The conditions for delete is given by the $where argument and the parameters + * for the condition is given by $parameters. + * @param string delete condition. + * @param array delete parameters. + * @return TDbCommand delete command. + */ + public function createDeleteCommand($where,$parameters=array()) + { + $table = $this->getTableInfo()->getTableFullName(); + if (!empty($where)) + $where = ' WHERE '.$where; + $command = $this->createCommand("DELETE FROM {$table}".$where); + $this->bindArrayValues($command, $parameters); + return $command; + } + + /** + * Creates an insert command for the table described in {@link setTableInfo TableInfo} for the given data. + * Each array key in the $data array must correspond to the column name of the table + * (if a column allows to be null, it may be omitted) to be inserted with + * the corresponding array value. + * @param array name-value pairs of new data to be inserted. + * @return TDbCommand insert command + */ + public function createInsertCommand($data) + { + $table = $this->getTableInfo()->getTableFullName(); + list($fields, $bindings) = $this->getInsertFieldBindings($data); + $command = $this->createCommand("INSERT INTO {$table}({$fields}) VALUES ($bindings)"); + $this->bindColumnValues($command, $data); + return $command; + } + + /** + * Creates an update command for the table described in {@link setTableInfo TableInfo} for the given data. + * Each array key in the $data array must correspond to the column name to be updated with the corresponding array value. + * @param array name-value pairs of data to be updated. + * @param string update condition. + * @param array update parameters. + * @return TDbCommand update command. + */ + public function createUpdateCommand($data, $where, $parameters=array()) + { + $table = $this->getTableInfo()->getTableFullName(); + if($this->hasIntegerKey($parameters)) + $fields = implode(', ', $this->getColumnBindings($data, true)); + else + $fields = implode(', ', $this->getColumnBindings($data)); + + if (!empty($where)) + $where = ' WHERE '.$where; + $command = $this->createCommand("UPDATE {$table} SET {$fields}".$where); + $this->bindArrayValues($command, array_merge($data, $parameters)); + return $command; + } + + /** + * Returns a list of insert field name and a list of binding names. + * @param object array or object to be inserted. + * @return array tuple ($fields, $bindings) + */ + protected function getInsertFieldBindings($values) + { + $fields = array(); $bindings=array(); + foreach(array_keys($values) as $name) + { + $fields[] = $this->getTableInfo()->getColumn($name)->getColumnName(); + $bindings[] = ':'.$name; + } + return array(implode(', ',$fields), implode(', ', $bindings)); + } + + /** + * Create a name-value or position-value if $position=true binding strings. + * @param array data for binding. + * @param boolean true to bind as position values. + * @return string update column names with corresponding binding substrings. + */ + protected function getColumnBindings($values, $position=false) + { + $bindings=array(); + foreach(array_keys($values) as $name) + { + $column = $this->getTableInfo()->getColumn($name)->getColumnName(); + $bindings[] = $position ? $column.' = ?' : $column.' = :'.$name; + } + return $bindings; + } + + /** + * @param string SQL query string. + * @return TDbCommand corresponding database command. + */ + public function createCommand($sql) + { + $this->getDbConnection()->setActive(true); + return $this->getDbConnection()->createCommand($sql); + } + + /** + * Bind the name-value pairs of $values where the array keys correspond to column names. + * @param TDbCommand database command. + * @param array name-value pairs. + */ + public function bindColumnValues($command, $values) + { + foreach($values as $name=>$value) + { + $column = $this->getTableInfo()->getColumn($name); + if($value === null && $column->getAllowNull()) + $command->bindValue(':'.$name, null, PDO::PARAM_NULL); + else + $command->bindValue(':'.$name, $value, $column->getPdoType()); + } + } + + /** + * @param TDbCommand database command + * @param array values for binding. + */ + public function bindArrayValues($command, $values) + { + if($this->hasIntegerKey($values)) + { + $values = array_values($values); + for($i = 0, $max=count($values); $i<$max; $i++) + $command->bindValue($i+1, $values[$i], $this->getPdoType($values[$i])); + } + else + { + foreach($values as $name=>$value) + { + $prop = $name[0]===':' ? $name : ':'.$name; + $command->bindValue($prop, $value, $this->getPdoType($value)); + } + } + } + + /** + * @param mixed PHP value + * @return integer PDO parameter types. + */ + public static function getPdoType($value) + { + switch(gettype($value)) + { + case 'boolean': return PDO::PARAM_BOOL; + case 'integer': return PDO::PARAM_INT; + case 'string' : return PDO::PARAM_STR; + case 'NULL' : return PDO::PARAM_NULL; + } + } + + /** + * @param array + * @return boolean true if any array key is an integer. + */ + protected function hasIntegerKey($array) + { + foreach($array as $k=>$v) + { + if(gettype($k)==='integer') + return true; + } + return false; + } +} diff --git a/lib/prado/framework/Data/Common/TDbMetaData.php b/lib/prado/framework/Data/Common/TDbMetaData.php new file mode 100644 index 0000000..0ec9ce4 --- /dev/null +++ b/lib/prado/framework/Data/Common/TDbMetaData.php @@ -0,0 +1,192 @@ +<?php +/** + * TDbMetaData class file. + * + * @author Wei Zhuo <weizhuo[at]gmail[dot]com> + * @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 + */ + +/** + * TDbMetaData is the base class for retrieving metadata information, such as + * table and columns information, from a database connection. + * + * Use the {@link getTableInfo} method to retrieve a table information. + * + * @author Wei Zhuo <weizho[at]gmail[dot]com> + * @package System.Data.Common + * @since 3.1 + */ +abstract class TDbMetaData extends TComponent +{ + private $_tableInfoCache=array(); + private $_connection; + + /** + * @var array + */ + protected static $delimiterIdentifier = array('[', ']', '"', '`', "'"); + + /** + * @param TDbConnection database connection. + */ + public function __construct($conn) + { + $this->_connection=$conn; + } + + /** + * @return TDbConnection database connection. + */ + public function getDbConnection() + { + return $this->_connection; + } + + /** + * Obtain database specific TDbMetaData class using the driver name of the database connection. + * @param TDbConnection database connection. + * @return TDbMetaData database specific TDbMetaData. + */ + public static function getInstance($conn) + { + $conn->setActive(true); //must be connected before retrieving driver name + $driver = $conn->getDriverName(); + switch(strtolower($driver)) + { + case 'pgsql': + Prado::using('System.Data.Common.Pgsql.TPgsqlMetaData'); + return new TPgsqlMetaData($conn); + case 'mysqli': + case 'mysql': + Prado::using('System.Data.Common.Mysql.TMysqlMetaData'); + return new TMysqlMetaData($conn); + case 'sqlite': //sqlite 3 + case 'sqlite2': //sqlite 2 + Prado::using('System.Data.Common.Sqlite.TSqliteMetaData'); + return new TSqliteMetaData($conn); + case 'mssql': // Mssql driver on windows hosts + case 'sqlsrv': // sqlsrv driver on windows hosts + case 'dblib': // dblib drivers on linux (and maybe others os) hosts + Prado::using('System.Data.Common.Mssql.TMssqlMetaData'); + return new TMssqlMetaData($conn); + case 'oci': + Prado::using('System.Data.Common.Oracle.TOracleMetaData'); + return new TOracleMetaData($conn); +// case 'ibm': +// Prado::using('System.Data.Common.IbmDb2.TIbmDb2MetaData'); +// return new TIbmDb2MetaData($conn); + default: + throw new TDbException('ar_invalid_database_driver',$driver); + } + } + + /** + * Obtains table meta data information for the current connection and given table name. + * @param string table or view name + * @return TDbTableInfo table information. + */ + public function getTableInfo($tableName=null) + { + $key = $tableName===null?$this->getDbConnection()->getConnectionString():$tableName; + if(!isset($this->_tableInfoCache[$key])) + { + $class = $this->getTableInfoClass(); + $tableInfo = $tableName===null ? new $class : $this->createTableInfo($tableName); + $this->_tableInfoCache[$key] = $tableInfo; + } + return $this->_tableInfoCache[$key]; + } + + /** + * Creates a command builder for a given table name. + * @param string table name. + * @return TDbCommandBuilder command builder instance for the given table. + */ + public function createCommandBuilder($tableName=null) + { + return $this->getTableInfo($tableName)->createCommandBuilder($this->getDbConnection()); + } + + /** + * This method should be implemented by decendent classes. + * @return TDbTableInfo driver dependent create builder. + */ + abstract protected function createTableInfo($tableName); + + /** + * @return string TDbTableInfo class name. + */ + protected function getTableInfoClass() + { + return 'TDbTableInfo'; + } + + /** + * Quotes a table name for use in a query. + * @param string $name table name + * @param string $lft left delimiter + * @param string $rgt right delimiter + * @return string the properly quoted table name + */ + public function quoteTableName($name) + { + $name = str_replace(self::$delimiterIdentifier, '', $name); + + $args = func_get_args(); + $rgt = $lft = isset($args[1]) ? $args[1] : ''; + $rgt = isset($args[2]) ? $args[2] : $rgt; + + if(strpos($name, '.')===false) + return $lft . $name . $rgt; + $names=explode('.', $name); + foreach($names as &$n) + $n = $lft . $n . $rgt; + return implode('.', $names); + } + + /** + * Quotes a column name for use in a query. + * @param string $name column name + * @param string $lft left delimiter + * @param string $rgt right delimiter + * @return string the properly quoted column name + */ + public function quoteColumnName($name) + { + $args = func_get_args(); + $rgt = $lft = isset($args[1]) ? $args[1] : ''; + $rgt = isset($args[2]) ? $args[2] : $rgt; + + return $lft . str_replace(self::$delimiterIdentifier, '', $name) . $rgt; + } + + /** + * Quotes a column alias for use in a query. + * @param string $name column alias + * @param string $lft left delimiter + * @param string $rgt right delimiter + * @return string the properly quoted column alias + */ + public function quoteColumnAlias($name) + { + $args = func_get_args(); + $rgt = $lft = isset($args[1]) ? $args[1] : ''; + $rgt = isset($args[2]) ? $args[2] : $rgt; + + return $lft . str_replace(self::$delimiterIdentifier, '', $name) . $rgt; + } + + /** + * Returns all table names in the database. + * This method should be overridden by child classes in order to support this feature + * because the default implementation simply throws an exception. + * @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. + */ + abstract public function findTableNames($schema=''); +} + diff --git a/lib/prado/framework/Data/Common/TDbTableColumn.php b/lib/prado/framework/Data/Common/TDbTableColumn.php new file mode 100644 index 0000000..9987197 --- /dev/null +++ b/lib/prado/framework/Data/Common/TDbTableColumn.php @@ -0,0 +1,197 @@ +<?php +/** + * TDbTableColumn class file. + * + * @author Wei Zhuo <weizhuo[at]gmail[dot]com> + * @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 + */ + +/** + * TDbTableColumn class describes the column meta data of the schema for a database table. + * + * @author Wei Zhuo <weizho[at]gmail[dot]com> + * @package System.Data.Common + * @since 3.1 + */ +class TDbTableColumn extends TComponent +{ + const UNDEFINED_VALUE= INF; //use infinity for undefined value + + private $_info=array(); + + /** + * Sets the table column meta data. + * @param array table column information. + */ + public function __construct($columnInfo) + { + $this->_info=$columnInfo; + } + + /** + * @param string information array key name + * @param mixed default value if information array value is null + * @return mixed information array value. + */ + protected function getInfo($name,$default=null) + { + return isset($this->_info[$name]) ? $this->_info[$name] : $default; + } + + /** + * @param string information array key name + * @param mixed new information array value. + */ + protected function setInfo($name,$value) + { + $this->_info[$name]=$value; + } + + /** + * Returns the derived PHP primitive type from the db type. Default returns 'string'. + * @return string derived PHP primitive type from the column db type. + */ + public function getPHPType() + { + return 'string'; + } + + /** + * @param integer PDO bind param/value types, default returns string. + */ + public function getPdoType() + { + switch($this->getPHPType()) + { + case 'boolean': return PDO::PARAM_BOOL; + case 'integer': return PDO::PARAM_INT; + case 'string' : return PDO::PARAM_STR; + } + return PDO::PARAM_STR; + } + + /** + * @return string name of the column in the table (identifier quoted). + */ + public function getColumnName() + { + return $this->getInfo('ColumnName'); + } + + /** + * @return string name of the column with quoted identifier. + */ + public function getColumnId() + { + return $this->getInfo('ColumnId'); + } + + /** + * @return string size of the column. + */ + public function getColumnSize() + { + return $this->getInfo('ColumnSize'); + } + + /** + * @return integer zero-based ordinal position of the column in the table. + */ + public function getColumnIndex() + { + return $this->getInfo('ColumnIndex'); + } + + /** + * @return string column type. + */ + public function getDbType() + { + return $this->getInfo('DbType'); + } + + /** + * @return boolean specifies whether value Null is allowed, default is false. + */ + public function getAllowNull() + { + return $this->getInfo('AllowNull',false); + } + + /** + * @return mixed default column value if column value was null. + */ + public function getDefaultValue() + { + return $this->getInfo('DefaultValue', self::UNDEFINED_VALUE); + } + + /** + * @return string precision of the column data, if the data is numeric. + */ + public function getNumericPrecision() + { + return $this->getInfo('NumericPrecision'); + } + + /** + * @return string scale of the column data, if the data is numeric. + */ + public function getNumericScale() + { + return $this->getInfo('NumericScale'); + } + + public function getMaxiumNumericConstraint() + { + if(($precision=$this->getNumericPrecision())!==null) + { + $scale=$this->getNumericScale(); + return $scale===null ? pow(10,$precision) : pow(10,$precision-$scale); + } + } + + /** + * @return boolean whether this column is a primary key for the table, default is false. + */ + public function getIsPrimaryKey() + { + return $this->getInfo('IsPrimaryKey',false); + } + + /** + * @return boolean whether this column is a foreign key, default is false. + */ + public function getIsForeignKey() + { + return $this->getInfo('IsForeignKey',false); + } + + /** + * @param string sequence name, only applicable if column is a sequence + */ + public function getSequenceName() + { + return $this->getInfo('SequenceName'); + } + + /** + * @return boolean whether the column is a sequence. + */ + public function hasSequence() + { + return $this->getSequenceName()!==null; + } + + /** + * @return boolean whether this column is excluded from insert and update. + */ + public function getIsExcluded() + { + return false; + } +} + diff --git a/lib/prado/framework/Data/Common/TDbTableInfo.php b/lib/prado/framework/Data/Common/TDbTableInfo.php new file mode 100644 index 0000000..8e60157 --- /dev/null +++ b/lib/prado/framework/Data/Common/TDbTableInfo.php @@ -0,0 +1,164 @@ +<?php +/** + * TDbTableInfo class file. + * + * @author Wei Zhuo <weizhuo[at]gmail[dot]com> + * @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 + */ + +/** + * TDbTableInfo class describes the meta data of a database table. + * + * @author Wei Zhuo <weizho[at]gmail[dot]com> + * @package System.Data.Common + * @since 3.1 + */ +class TDbTableInfo extends TComponent +{ + private $_info=array(); + + private $_primaryKeys; + private $_foreignKeys; + + private $_columns; + + private $_lowercase; + + /** + * @var null|array + * @since 3.1.7 + */ + private $_names = null; + + /** + * Sets the database table meta data information. + * @param array table column information. + */ + public function __construct($tableInfo=array(),$primary=array(),$foreign=array()) + { + $this->_info=$tableInfo; + $this->_primaryKeys=$primary; + $this->_foreignKeys=$foreign; + $this->_columns=new TMap; + } + + /** + * @param TDbConnection database connection. + * @return TDbCommandBuilder new command builder + */ + public function createCommandBuilder($connection) + { + Prado::using('System.Data.Common.TDbCommandBuilder'); + return new TDbCommandBuilder($connection,$this); + } + + /** + * @param string information array key name + * @param mixed default value if information array value is null + * @return mixed information array value. + */ + protected function getInfo($name,$default=null) + { + return isset($this->_info[$name]) ? $this->_info[$name] : $default; + } + + /** + * @param string information array key name + * @param mixed new information array value. + */ + protected function setInfo($name,$value) + { + $this->_info[$name]=$value; + } + + /** + * @return string name of the table this column belongs to. + */ + public function getTableName() + { + return $this->getInfo('TableName'); + } + + /** + * @return string full name of the table, database dependent. + */ + public function getTableFullName() + { + return $this->getTableName(); + } + + /** + * @return boolean whether the table is a view, default is false. + */ + public function getIsView() + { + return $this->getInfo('IsView',false); + } + + /** + * @return TMap TDbTableColumn column meta data. + */ + public function getColumns() + { + return $this->_columns; + } + + /** + * @param string column id + * @return TDbTableColumn column information. + */ + public function getColumn($name) + { + if(($column = $this->_columns->itemAt($name))!==null) + return $column; + throw new TDbException('dbtableinfo_invalid_column_name', $name, $this->getTableFullName()); + } + + /** + * @param array list of column Id, empty to get all columns. + * @return array table column names (identifier quoted) + */ + public function getColumnNames() + { + if($this->_names===null) + { + $this->_names=array(); + foreach($this->getColumns() as $column) + $this->_names[] = $column->getColumnName(); + } + return $this->_names; + } + + /** + * @return string[] names of primary key columns. + */ + public function getPrimaryKeys() + { + return $this->_primaryKeys; + } + + /** + * @return array tuples of foreign table and column name. + */ + public function getForeignKeys() + { + return $this->_foreignKeys; + } + + /** + * @return array lowercased column key names mapped to normal column ids. + */ + public function getLowerCaseColumnNames() + { + if($this->_lowercase===null) + { + $this->_lowercase=array(); + foreach($this->getColumns()->getKeys() as $key) + $this->_lowercase[strtolower($key)] = $key; + } + return $this->_lowercase; + } +}
\ No newline at end of file |