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/Sqlite/TSqliteMetaData.php | 150 +++++++++++++++++++++ .../Data/Common/Sqlite/TSqliteTableColumn.php | 62 +++++++++ framework/Data/Common/Sqlite/TSqliteTableInfo.php | 31 +++++ 3 files changed, 243 insertions(+) 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/Common/Sqlite') 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 -- cgit v1.2.3