From 32b73a0b1dfcd33e07e7fd2b2cf2fd7d42cf48a0 Mon Sep 17 00:00:00 2001 From: wei <> Date: Thu, 12 Apr 2007 12:43:49 +0000 Subject: Add Sqlite driver. --- framework/Data/Common/Mysql/TMysqlMetaData.php | 17 +-- framework/Data/Common/Mysql/TMysqlTableColumn.php | 42 +++--- framework/Data/Common/Pgsql/TPgsqlMetaData.php | 47 +++---- framework/Data/Common/Pgsql/TPgsqlTableColumn.php | 20 +-- framework/Data/Common/Sqlite/TSqliteMetaData.php | 150 +++++++++++++++++++++ .../Data/Common/Sqlite/TSqliteTableColumn.php | 62 +++++++++ framework/Data/Common/Sqlite/TSqliteTableInfo.php | 31 +++++ framework/Data/Common/TDbCommandBuilder.php | 4 +- framework/Data/Common/TDbTableColumn.php | 11 +- framework/Data/Common/TDbTableInfo.php | 16 +-- framework/Data/DataGateway/TDataGatewayCommand.php | 2 + framework/Data/DataGateway/TTableGateway.php | 5 +- 12 files changed, 318 insertions(+), 89 deletions(-) create mode 100644 framework/Data/Common/Sqlite/TSqliteMetaData.php create mode 100644 framework/Data/Common/Sqlite/TSqliteTableColumn.php create mode 100644 framework/Data/Common/Sqlite/TSqliteTableInfo.php (limited to 'framework/Data') diff --git a/framework/Data/Common/Mysql/TMysqlMetaData.php b/framework/Data/Common/Mysql/TMysqlMetaData.php index ee3bd2e0..2a6d80bd 100644 --- a/framework/Data/Common/Mysql/TMysqlMetaData.php +++ b/framework/Data/Common/Mysql/TMysqlMetaData.php @@ -49,8 +49,6 @@ class TMysqlMetaData extends TDbMetaData $info['IsPrimaryKey'] = true; if($this->isForeignKeyColumn($columnId, $tableInfo)) $info['IsForeignKey'] = true; - if(in_array($columnId, $tableInfo->getUniqueKeys())) - $info['IsUnique'] = true; $info['DbType'] = $col['Type']; $match=array(); @@ -142,8 +140,8 @@ class TMysqlMetaData extends TDbMetaData $info['SchemaName'] = $schemaName; $info['TableName'] = $tableName; $info['IsView'] = $this->getIsView($schemaName,$tableName); - list($primary, $foreign, $unique) = $this->getConstraintKeys($schemaName, $tableName); - return new TMysqlTableInfo($info,$primary,$foreign, $unique); + list($primary, $foreign) = $this->getConstraintKeys($schemaName, $tableName); + return new TMysqlTableInfo($info,$primary,$foreign); } /** @@ -173,10 +171,10 @@ class TMysqlMetaData extends TDbMetaData } /** - * Gets the primary, foreign key, and unique column details for the given table. + * 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, $unique) + * @return array tuple ($primary, $foreign) */ protected function getConstraintKeys($schemaName, $tableName) { @@ -185,15 +183,12 @@ class TMysqlMetaData extends TDbMetaData $command = $this->getDbConnection()->createCommand($sql); $primary = array(); $foreign = $this->getForeignConstraints($schemaName,$tableName); - $unique = array(); foreach($command->query() as $row) { if($row['Key_name']==='PRIMARY') $primary[] = $row['Column_name']; - else if(intval($row['Non_unique'])===0) - $unique[] = $row['Column_name']; } - return array($primary,$foreign,$unique); + return array($primary,$foreign); } /** @@ -229,7 +224,7 @@ EOD; $fkeys[$col['con']]['keys'][$col['col']] = $col['fkcol']; $fkeys[$col['con']]['table'] = "`{$col['fkschema']}`.`{$col['fktable']}`"; } - return array_values($fkeys); + return count($fkeys) > 0 ? array_values($fkeys) : $fkeys; } /** diff --git a/framework/Data/Common/Mysql/TMysqlTableColumn.php b/framework/Data/Common/Mysql/TMysqlTableColumn.php index 0f013acd..4f0f7da2 100644 --- a/framework/Data/Common/Mysql/TMysqlTableColumn.php +++ b/framework/Data/Common/Mysql/TMysqlTableColumn.php @@ -25,6 +25,29 @@ Prado::using('System.Data.Common.TDbTableColumn'); */ 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. */ @@ -45,25 +68,6 @@ class TMysqlTableColumn extends TDbTableColumn { return $this->getInfo('DbTypeValues'); } - - /** - * Overrides parent implementation, returns PHP type from the db type. - * @return boolean derived PHP primitive type from the column db type. - */ - public function getPHPType() - { - switch(strtolower($this->getDbType())) - { - case 'bit': case 'bit varying': case 'real': case 'serial': case 'int': case 'integer': - return 'integer'; - case 'boolean': - return 'boolean'; - case 'bigint': case 'bigserial': case 'double precision': case 'money': case 'numeric': - return 'float'; - default: - return 'string'; - } - } } ?> \ No newline at end of file diff --git a/framework/Data/Common/Pgsql/TPgsqlMetaData.php b/framework/Data/Common/Pgsql/TPgsqlMetaData.php index b789192f..e964cfe7 100644 --- a/framework/Data/Common/Pgsql/TPgsqlMetaData.php +++ b/framework/Data/Common/Pgsql/TPgsqlMetaData.php @@ -119,12 +119,27 @@ EOD; */ protected function createNewTableInfo($schemaName,$tableName) { - $info['SchemaName'] = $schemaName; - $info['TableName'] = $tableName; + $info['SchemaName'] = $this->assertIdentifier($schemaName); + $info['TableName'] = $this->assertIdentifier($tableName); if($this->getIsView($schemaName,$tableName)) $info['IsView'] = true; - list($primary, $foreign, $unique) = $this->getConstraintKeys($schemaName, $tableName); - return new TPgsqlTableInfo($info,$primary,$foreign, $unique); + list($primary, $foreign) = $this->getConstraintKeys($schemaName, $tableName); + return new TPgsqlTableInfo($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; } /** @@ -163,8 +178,6 @@ EOD; $info['IsPrimaryKey'] = true; if($this->isForeignKeyColumn($columnId, $tableInfo)) $info['IsForeignKey'] = true; - if(in_array($columnId, $tableInfo->getUniqueKeys())) - $info['IsUnique'] = true; if($col['atttypmod'] > 0) $info['ColumnSize'] = $col['atttypmod'] - 4; @@ -222,10 +235,10 @@ EOD; } /** - * Gets the primary, foreign key, and unique column details for the given table. + * 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, $unique) + * @return array tuple ($primary, $foreign) */ protected function getConstraintKeys($schemaName, $tableName) { @@ -245,7 +258,6 @@ EOD; $command->bindValue(':schema', $schemaName); $primary = array(); $foreign = array(); - $unique = array(); foreach($command->query() as $row) { switch($row['contype']) @@ -257,13 +269,9 @@ EOD; if(($fkey = $this->getForeignKeys($row['consrc']))!==null) $foreign[] = $fkey; break; - case 'u': - if(($ukey = $this->getUniqueKey($row['consrc']))!==null) - $unique[] = $ukey; - break; } } - return array($primary,$foreign,$unique); + return array($primary,$foreign); } /** @@ -279,17 +287,6 @@ EOD; return array(); } - /** - * @param string pgsql unique constraint definition - * @return string column id if found, null otherwise. - */ - protected function getUniqueKey($src) - { - $matches=array(); - if(preg_match('/UNIQUE\s+\(([^\)]+)\)/i', $src, $matches)) - return $matches[1]; - } - /** * Gets foreign relationship constraint keys and table name * @param string pgsql foreign key definition diff --git a/framework/Data/Common/Pgsql/TPgsqlTableColumn.php b/framework/Data/Common/Pgsql/TPgsqlTableColumn.php index 66053a63..a00f4a8f 100644 --- a/framework/Data/Common/Pgsql/TPgsqlTableColumn.php +++ b/framework/Data/Common/Pgsql/TPgsqlTableColumn.php @@ -25,23 +25,25 @@ Prado::using('System.Data.Common.TDbTableColumn'); */ 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() { - switch(strtolower($this->getDbType())) + $dbtype = strtolower($this->getDbType()); + foreach(self::$types as $type => $dbtypes) { - case 'bit': case 'bit varying': case 'real': case 'serial': case 'int': case 'integer': - return 'integer'; - case 'boolean': - return 'boolean'; - case 'bigint': case 'bigserial': case 'double precision': case 'money': case 'numeric': - return 'float'; - default: - return 'string'; + if(in_array($dbtype, $dbtypes)) + return $type; } + return 'string'; } } diff --git a/framework/Data/Common/Sqlite/TSqliteMetaData.php b/framework/Data/Common/Sqlite/TSqliteMetaData.php new file mode 100644 index 00000000..68734046 --- /dev/null +++ b/framework/Data/Common/Sqlite/TSqliteMetaData.php @@ -0,0 +1,150 @@ + + * @link http://www.pradosoft.com/ + * @copyright Copyright © 2005-2007 PradoSoft + * @license http://www.pradosoft.com/license/ + * @version $Id: TSqliteMetaData.php 1861 2007-04-12 08:05:03Z wei $ + * @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 PostgreSQL database table and column information. + * + * @author Wei Zhuo + * @version $Id: TSqliteMetaData.php 1861 2007-04-12 08:05:03Z wei $ + * @package System.Data.Commom.Sqlite + * @since 3.1 + */ +class TSqliteMetaData extends TDbMetaData +{ + /** + * Get the column definitions for given table. + * @param string table name. + * @return TPgsqlTableInfo table information. + */ + protected function createTableInfo($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'] = $table; + if($this->getIsView($tableName)) + $info['IsView'] = true; + $tableInfo = new TSqliteTableInfo($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['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; + } + +} +?> \ No newline at end of file diff --git a/framework/Data/Common/Sqlite/TSqliteTableColumn.php b/framework/Data/Common/Sqlite/TSqliteTableColumn.php new file mode 100644 index 00000000..cb379bfd --- /dev/null +++ b/framework/Data/Common/Sqlite/TSqliteTableColumn.php @@ -0,0 +1,62 @@ + + * @link http://www.pradosoft.com/ + * @copyright Copyright © 2005-2007 PradoSoft + * @license http://www.pradosoft.com/license/ + * @version $Id: TSqliteTableColumn.php 1861 2007-04-12 08:05:03Z wei $ + * @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 + * @version $Id: TSqliteTableColumn.php 1861 2007-04-12 08:05:03Z wei $ + * @package System.Data.Common.Sqlite + * @since 3.1 + */ +class TSqliteTableColumn 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() + { + $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 getHasSequence() + { + return $this->getAutoIncrement(); + } +} + +?> \ No newline at end of file diff --git a/framework/Data/Common/Sqlite/TSqliteTableInfo.php b/framework/Data/Common/Sqlite/TSqliteTableInfo.php new file mode 100644 index 00000000..1581c3cc --- /dev/null +++ b/framework/Data/Common/Sqlite/TSqliteTableInfo.php @@ -0,0 +1,31 @@ + + * @link http://www.pradosoft.com/ + * @copyright Copyright © 2005-2007 PradoSoft + * @license http://www.pradosoft.com/license/ + * @version $Id: TSqliteTableInfo.php 1861 2007-04-12 08:05:03Z wei $ + * @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 + * @version $Id: TSqliteTableInfo.php 1861 2007-04-12 08:05:03Z wei $ + * @package System.Data.Common.Sqlite + * @since 3.1 + */ +class TSqliteTableInfo extends TDbTableInfo +{ +} + +?> \ No newline at end of file diff --git a/framework/Data/Common/TDbCommandBuilder.php b/framework/Data/Common/TDbCommandBuilder.php index 5238a045..2a8f93ee 100644 --- a/framework/Data/Common/TDbCommandBuilder.php +++ b/framework/Data/Common/TDbCommandBuilder.php @@ -120,7 +120,7 @@ class TDbCommandBuilder extends TComponent * @param array condition parameters. * @return TDbCommand query command. */ - public function createFindCommand($where, $parameters=array(), $ordering=array(), $limit=-1, $offset=-1) + public function createFindCommand($where='1=1', $parameters=array(), $ordering=array(), $limit=-1, $offset=-1) { $table = $this->getTableInfo()->getTableFullName(); $sql = "SELECT * FROM {$table} WHERE {$where}"; @@ -139,7 +139,7 @@ class TDbCommandBuilder extends TComponent * @param array binding parameters. * @return TDbCommand count command. */ - public function createCountCommand($where='true', $parameters=array(),$ordering=array(), $limit=-1, $offset=-1) + public function createCountCommand($where='1=1', $parameters=array(),$ordering=array(), $limit=-1, $offset=-1) { $table = $this->getTableInfo()->getTableFullName(); $sql = "SELECT COUNT(*) FROM {$table} WHERE {$where}"; diff --git a/framework/Data/Common/TDbTableColumn.php b/framework/Data/Common/TDbTableColumn.php index 4d9bd8a0..c5c9ac80 100644 --- a/framework/Data/Common/TDbTableColumn.php +++ b/framework/Data/Common/TDbTableColumn.php @@ -62,7 +62,7 @@ class TDbTableColumn extends TComponent } /** - * @param integer PDO bind param/value types. + * @param integer PDO bind param/value types, default returns string. */ public function getPdoType() { @@ -72,6 +72,7 @@ class TDbTableColumn extends TComponent case 'integer': return PDO::PARAM_INT; case 'string' : return PDO::PARAM_STR; } + return PDO::PARAM_STR; } /** @@ -154,14 +155,6 @@ class TDbTableColumn extends TComponent return $this->getInfo('IsForeignKey',false); } - /** - * @return boolean whether a unique constraint applies to this column, default is false. - */ - public function getIsUnique() - { - return $this->getInfo('IsUnique', false); - } - /** * @param string sequence name, only applicable if column is a sequence */ diff --git a/framework/Data/Common/TDbTableInfo.php b/framework/Data/Common/TDbTableInfo.php index 9b7f4392..9689630f 100644 --- a/framework/Data/Common/TDbTableInfo.php +++ b/framework/Data/Common/TDbTableInfo.php @@ -24,7 +24,6 @@ class TDbTableInfo extends TComponent private $_primaryKeys; private $_foreignKeys; - private $_uniqueKeys; private $_columns; @@ -34,12 +33,11 @@ class TDbTableInfo extends TComponent * Sets the database table meta data information. * @param array table column information. */ - public function __construct($tableInfo,$primary=array(),$foreign=array(), $unique=array()) + public function __construct($tableInfo,$primary=array(),$foreign=array()) { $this->_info=$tableInfo; $this->_primaryKeys=$primary; $this->_foreignKeys=$foreign; - $this->_uniqueKeys=$unique; $this->_columns=new TMap; } @@ -110,7 +108,9 @@ class TDbTableInfo extends TComponent */ public function getColumn($name) { - return $this->_columns->itemAt($name); + if(($column = $this->_columns->itemAt($name))!==null) + return $column; + throw new TDbException('dbtableinfo_invalid_column_name', $name, $this->getTableFullName()); } /** @@ -141,14 +141,6 @@ class TDbTableInfo extends TComponent return $this->_foreignKeys; } - /** - * @return array unique column ids. - */ - public function getUniqueKeys() - { - return $this->_uniqueKeys; - } - /** * @return array lowercased column key names mapped to normal column ids. */ diff --git a/framework/Data/DataGateway/TDataGatewayCommand.php b/framework/Data/DataGateway/TDataGatewayCommand.php index 25666059..a5f729d8 100644 --- a/framework/Data/DataGateway/TDataGatewayCommand.php +++ b/framework/Data/DataGateway/TDataGatewayCommand.php @@ -228,6 +228,8 @@ class TDataGatewayCommand extends TComponent */ protected function getFindCommand($criteria) { + if($criteria===null) + return $this->getBuilder()->createFindCommand(); $where = $criteria->getCondition(); $parameters = $criteria->getParameters()->toArray(); $ordering = $criteria->getOrdersBy(); diff --git a/framework/Data/DataGateway/TTableGateway.php b/framework/Data/DataGateway/TTableGateway.php index 6acdb837..6a77c064 100644 --- a/framework/Data/DataGateway/TTableGateway.php +++ b/framework/Data/DataGateway/TTableGateway.php @@ -78,10 +78,11 @@ class TTableGateway extends TComponent * @param mixed parameter values. * @return TDbDataReader matching records. */ - public function findAll($criteria, $parameters=array()) + public function findAll($criteria=null, $parameters=array()) { $args = func_num_args() > 1 ? array_slice(func_get_args(),1) : null; - $criteria = $this->getCriteria($criteria,$parameters, $args); + if($criteria!==null) + $criteria = $this->getCriteria($criteria,$parameters, $args); return $this->getCommand()->findAll($criteria); } -- cgit v1.2.3