Add Sqlite driver.

10 files changed, 313 insertions, 87 deletions

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);

 	}

 

 	/**

@@ -280,17 +288,6 @@ EOD;
 	}

 

 	/**

-	 * @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

 	 * @return array foreign relationship table name and keys, null otherwise

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 @@
+<?php
+/**

+ * TSqliteMetaData class file.

+ *

+ * @author Wei Zhuo <weizhuo[at]gmail[dot]com>

+ * @link http://www.pradosoft.com/

+ * @copyright Copyright &copy; 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 <weizho[at]gmail[dot]com>

+ * @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 @@
+<?php

+/**

+ * TSqliteTableColumn class file.

+ *

+ * @author Wei Zhuo <weizhuo[at]gmail[dot]com>

+ * @link http://www.pradosoft.com/

+ * @copyright Copyright &copy; 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 <weizho[at]gmail[dot]com>

+ * @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 @@
+<?php

+/**

+ * TSqliteTableInfo class file.

+ *

+ * @author Wei Zhuo <weizhuo[at]gmail[dot]com>

+ * @link http://www.pradosoft.com/

+ * @copyright Copyright &copy; 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 <weizho[at]gmail[dot]com>

+ * @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;

 	}

 

 	/**

@@ -155,14 +156,6 @@ class TDbTableColumn extends TComponent
 	}

 

 	/**

-	 * @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

 	 */

 	public function getSequenceName()

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());

 	}

 

 	/**

@@ -142,14 +142,6 @@ class TDbTableInfo extends TComponent
 	}

 

 	/**

-	 * @return array unique column ids.

-	 */

-	public function getUniqueKeys()

-	{

-		return $this->_uniqueKeys;

-	}

-

-	/**

 	 * @return array lowercased column key names mapped to normal column ids.

 	 */

 	public function getLowerCaseColumnNames()