summaryrefslogtreecommitdiff
path: root/framework/Data/Common
diff options
context:
space:
mode:
authorwei <>2007-04-12 12:43:49 +0000
committerwei <>2007-04-12 12:43:49 +0000
commit32b73a0b1dfcd33e07e7fd2b2cf2fd7d42cf48a0 (patch)
treebf2b348fa854d82dcf8a9f43ffd43df3eba5a1e8 /framework/Data/Common
parent815ce4bcc616c4b0618046651174062efc93fd06 (diff)
Add Sqlite driver.
Diffstat (limited to 'framework/Data/Common')
-rw-r--r--framework/Data/Common/Mysql/TMysqlMetaData.php17
-rw-r--r--framework/Data/Common/Mysql/TMysqlTableColumn.php42
-rw-r--r--framework/Data/Common/Pgsql/TPgsqlMetaData.php47
-rw-r--r--framework/Data/Common/Pgsql/TPgsqlTableColumn.php20
-rw-r--r--framework/Data/Common/Sqlite/TSqliteMetaData.php150
-rw-r--r--framework/Data/Common/Sqlite/TSqliteTableColumn.php62
-rw-r--r--framework/Data/Common/Sqlite/TSqliteTableInfo.php31
-rw-r--r--framework/Data/Common/TDbCommandBuilder.php4
-rw-r--r--framework/Data/Common/TDbTableColumn.php11
-rw-r--r--framework/Data/Common/TDbTableInfo.php16
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()