From a8b3ebe8f62c3888b216d827c1c5dcba8a47d4e1 Mon Sep 17 00:00:00 2001 From: wei <> Date: Sun, 26 Nov 2006 22:15:58 +0000 Subject: Adding active record implementation. --- framework/Data/ActiveRecord/Vendor/TDbMetaData.php | 363 +++++++++++++++++++++ .../Data/ActiveRecord/Vendor/TDbMetaDataCommon.php | 175 ++++++++++ .../ActiveRecord/Vendor/TDbMetaDataInspector.php | 79 +++++ .../ActiveRecord/Vendor/TMysqlColumnMetaData.php | 105 ++++++ .../Data/ActiveRecord/Vendor/TMysqlMetaData.php | 47 +++ .../Vendor/TMysqlMetaDataInspector.php | 80 +++++ .../ActiveRecord/Vendor/TPgsqlColumnMetaData.php | 121 +++++++ .../Data/ActiveRecord/Vendor/TPgsqlMetaData.php | 46 +++ .../Vendor/TPgsqlMetaDataInspector.php | 223 +++++++++++++ .../ActiveRecord/Vendor/TSqliteColumnMetaData.php | 96 ++++++ .../Data/ActiveRecord/Vendor/TSqliteMetaData.php | 73 +++++ .../Vendor/TSqliteMetaDataInspector.php | 85 +++++ 12 files changed, 1493 insertions(+) create mode 100644 framework/Data/ActiveRecord/Vendor/TDbMetaData.php create mode 100644 framework/Data/ActiveRecord/Vendor/TDbMetaDataCommon.php create mode 100644 framework/Data/ActiveRecord/Vendor/TDbMetaDataInspector.php create mode 100644 framework/Data/ActiveRecord/Vendor/TMysqlColumnMetaData.php create mode 100644 framework/Data/ActiveRecord/Vendor/TMysqlMetaData.php create mode 100644 framework/Data/ActiveRecord/Vendor/TMysqlMetaDataInspector.php create mode 100644 framework/Data/ActiveRecord/Vendor/TPgsqlColumnMetaData.php create mode 100644 framework/Data/ActiveRecord/Vendor/TPgsqlMetaData.php create mode 100644 framework/Data/ActiveRecord/Vendor/TPgsqlMetaDataInspector.php create mode 100644 framework/Data/ActiveRecord/Vendor/TSqliteColumnMetaData.php create mode 100644 framework/Data/ActiveRecord/Vendor/TSqliteMetaData.php create mode 100644 framework/Data/ActiveRecord/Vendor/TSqliteMetaDataInspector.php (limited to 'framework/Data/ActiveRecord/Vendor') diff --git a/framework/Data/ActiveRecord/Vendor/TDbMetaData.php b/framework/Data/ActiveRecord/Vendor/TDbMetaData.php new file mode 100644 index 00000000..490515f6 --- /dev/null +++ b/framework/Data/ActiveRecord/Vendor/TDbMetaData.php @@ -0,0 +1,363 @@ + + * @version $Id$ + * @package System.Data.ActiveRecord.Vendor + */ + +/** + * Table meta data for Active Record. + * + * TDbMetaData is the base class for database vendor specific that builds + * the appropriate database commands for active record finder and commit methods. + * + * @author Wei Zhuo + * @version $Id$ + * @package System.Data.ActiveRecord.Vendor + * @since 3.1 + */ +abstract class TDbMetaData extends TComponent +{ + private $_primaryKeys=array(); + private $_foreignKeys=array(); + private $_columns=array(); + private $_table; + private $_isView=false; + + /** + * Initialize the meta data. + * @param string table name + * @param array name value pair of column meta data in the table + * @param array primary key field names + * @param array foriegn key field meta data. + */ + public function __construct($table, $cols, $pk, $fk=array(),$view=false) + { + $this->_table=$table; + $this->_columns=$cols; + $this->_primaryKeys=$pk; + $this->_foreignKeys=$fk; + $this->_isView=$view; + } + + public function getIsView() + { + return $this->_isView; + } + + /** + * @return string table name + */ + public function getTableName() + { + return $this->_table; + } + + /** + * @return array primary key field names. + */ + public function getPrimaryKeys() + { + return $this->_primaryKeys; + } + + /** + * @return array foreign key meta data. + */ + public function getForeignKeys() + { + return $this->_foreignKeys; + } + + /** + * @return array name value pair column meta data + */ + public function getColumns() + { + return $this->_columns; + } + + /** + * @param unknown_type $name + */ + public function getColumn($name) + { + return $this->_columns[$name]; + } + + /** + * Post process the rows after returning from a 1 row query. + * @param mixed row data, may be null. + * @return mixed processed rows. + */ + public function postQueryRow($row) + { + return $row; + } + + /** + * Post process the rows after returning from a 1 row query. + * @param TDbDataReader multiple row data + * @return array post processed data. + */ + public function postQuery($rows) + { + return $rows; + } + + /** + * @return string command separated list of all fields in the table, field names are quoted. + */ + protected function getSelectionColumns() + { + $columns = array(); + foreach($this->getColumns() as $column) + $columns[] = $column->getName(); + return implode(', ', $columns); + } + + /** + * Construct search criteria using primary key names + * @return string SQL string for used after WHERE statement. + */ + protected function getPrimaryKeyCriteria() + { + if(count($this->getPrimaryKeys())===0) + throw new TActiveRecordException('ar_no_primary_key_found',$this->getTableName()); + $criteria=array(); + foreach($this->getPrimaryKeys() as $key) + $criteria[] = $this->getColumn($key)->getName(). ' = :'.$key; + return implode(' AND ', $criteria); + } + + /** + * Bind a list of variables in the command. The named parameters is taken + * from the values of the $keys parameter. The bind value is taken from the + * $values parameter using the index taken from the each value of $keys array. + * @param TDbCommand SQL database command + * @param array named parameters + * @param array binding values (index should match that of $keys) + */ + protected function bindArrayKeyValues($command, $keys, $values) + { + if(!is_array($values)) $values = array($values); + foreach($keys as $i => $key) + { + $value = isset($values[$i]) ? $values[$i] : $values[$key]; + $command->bindValue(':'.$key, $value); + } + $command->prepare(); + } + + /** + * Returns a list of name value pairs from the object. + * @param array named parameters + * @param TActiveRecord record object + * @return array name value pairs. + */ + protected function getObjectKeyValues($keys, $object) + { + $properties = array(); + foreach($keys as $key) + $properties[$key] = $object->{$key}; + return $properties; + } + + /** + * Gets the columns that can be inserted into the database. + * @param TActiveRecord record object to be inserted. + * @return array name value pairs of fields to be added. + */ + protected function getInsertableColumns($record) + { + $columns = array(); + foreach($this->getColumns() as $name=>$column) + { + $value = $record->{$name}; + if($column->getNotNull() && $value===null && !$column->getIsPrimaryKey()) + { + throw new TActiveRecordException( + 'ar_value_must_not_be_null', get_class($record), + $this->getTableName(), $name); + } + if($value!==null) + $columns[$name] = $value; + } + return $columns; + } + + /** + * Gets the columns that will be updated, it exculdes primary key columns + * and record properties that are null. + * @param TActiveRecord record object with new data for update. + * @return array name value pairs of fields to be updated. + */ + protected function getUpdatableColumns($record) + { + $columns = array(); + foreach($this->getColumns() as $name => $column) + { + $value = $record->{$name}; + if(!$column->getIsPrimaryKey() && $value !== null) + $columns[$name] = $value; + } + return $columns; + } + + /** + * Gets a comma delimited string of name parameters for update. +x * @param array name value pairs of columns for update. + * @return string update named parameter string. + */ + protected function getUpdateBindings($columns) + { + $fields = array(); + foreach($columns as $name=>$value) + $fields[] = $this->getColumn($name)->getName(). '= :'.$name; + return implode(', ', $fields); + } + + /** + * Create a new database command based on the given $sql and bind the + * named parameters given by $names with values corresponding in $values. + * @param TDbConnection database connection. + * @param string SQL string. + * @param array named parameters + * @param array matching named parameter values + * @return TDbCommand binded command, ready for execution. + */ + protected function createBindedCommand($conn, $sql, $names,$values) + { + $conn->setActive(true); + $command = $conn->createCommand($sql); + $this->bindArrayKeyValues($command,$names,$values); + return $command; + } + + /** + * Creates a new database command and bind the values from the criteria object. + * + * @param TDbConnection database connection. + * @param string SQL string. + * @param TActiveRecordCriteria search criteria + * @return TDbCommand binded command. + */ + protected function createCriteriaBindedCommand($conn,$sql,$criteria) + { + $conn->setActive(true); + $command = $conn->createCommand($sql); + if($criteria!==null) + { + if($criteria->getIsNamedParameters()) + { + foreach($criteria->getParameters() as $name=>$value) + $command->bindValue($name,$value); + } + else + { + $index=1; + foreach($criteria->getParameters() as $value) + $command->bindValue($index++,$value); + } + } + $command->prepare(); + return $command; + } + + /** + * Bind parameter values. + */ + protected function bindParameterValues($conn,$command,$parameters) + { + $index=1; + foreach($parameters as $key=>$value) + { + if(is_string($key)) + $command->bindValue($key,$value); + else + $command->bindValue($index++,$value); + } + $command->prepare(); + } + + /** + * Gets the comma delimited string of fields name for insert command. + */ + protected function getInsertColumNames($columns) + { + $fields = array(); + foreach($columns as $name=>$column) + $fields[] = $this->getColumn($name)->getName(); + return implode(', ', $fields); + } + + /** + * Gets the comma delimited string of name bindings for insert command. + */ + protected function getInsertColumnValues($columns) + { + $fields = array(); + foreach(array_keys($columns) as $column) + $fields[] = ':'.$column; + return implode(', ', $fields); + } + + /** + * @param TDbConnection database connection + * @param array primary key values. + * @return string delete criteria for multiple scalar primary keys. + */ + protected function getDeleteInPkCriteria($conn, $keys) + { + $pk = $this->getPrimaryKeys(); + $column = $this->getColumn($pk[0])->getName(); + $values = array(); + foreach($keys as $key) + { + if(is_array($key)) + { + throw new TActiveRecordException('ar_primary_key_is_scalar', + $this->getTableName(),$column,'array('.implode(', ',$key).')'); + } + $values[] = $conn->quoteString($key); + } + $pks = implode(', ', $values); + return "$column IN ($pks)"; + } + + /** + * @param TDbConnection database connection + * @param array primary key values. + * @return string delete criteria for multiple composite primary keys. + */ + protected function getDeleteMultiplePkCriteria($conn,$pks) + { + //check for 1 set composite keys + if(count($pks)>0 && !is_array($pks[0])) + $pks = array($pks); + $conditions=array(); + foreach($pks as $keys) + $conditions[] = $this->getDeleteCompositeKeyCondition($conn,$keys); + return implode(' OR ', $conditions); + } + + /** + * @return string delete criteria for 1 composite key. + */ + protected function getDeleteCompositeKeyCondition($conn,$keys) + { + $condition=array(); + $index = 0; + foreach($this->getPrimarykeys() as $pk) + { + $name = $this->getColumn($pk)->getName(); + $value = isset($keys[$pk]) ? $keys[$pk] : $keys[$index]; + $condition[] = "$name = ".$conn->quoteString($value); + $index++; + } + return '('.implode(' AND ', $condition).')'; + } +} +?> \ No newline at end of file diff --git a/framework/Data/ActiveRecord/Vendor/TDbMetaDataCommon.php b/framework/Data/ActiveRecord/Vendor/TDbMetaDataCommon.php new file mode 100644 index 00000000..69f49dc1 --- /dev/null +++ b/framework/Data/ActiveRecord/Vendor/TDbMetaDataCommon.php @@ -0,0 +1,175 @@ + + * @version $Id$ + * @package System.Data.ActiveRecord.Vendor + */ + +Prado::using('System.Data.ActiveRecord.Vendor.TDbMetaData'); + +/** + * Common database command: insert, update, select and delete. + * + * Base class for database specific insert, update, select and delete command builder. + * + * @author Wei Zhuo + * @version $Id$ + * @package System.Data.ActiveRecord.Vendor + * @since 3.1 + */ +abstract class TDbMetaDataCommon extends TDbMetaData +{ + /** + * SQL database command for finding the record by primary keys. + * @param TDbConnection database connection. + * @param array primary keys name value pairs. + * @return TDbCommand find by primary key command. + */ + public function getFindByPkCommand($conn,$keys) + { + $columns = $this->getSelectionColumns(); + $primaryKeys = $this->getPrimaryKeyCriteria(); + $table = $this->getTableName(); + $sql = "SELECT {$columns} FROM {$table} WHERE {$primaryKeys}"; + $command = $this->createBindedCommand($conn, $sql, $this->getPrimaryKeys(), $keys); + return $command; + } + + /** + * SQL database command for finding records using a criteria object. + * @param TDbConnection database connection. + * @param TActiveRecordCriteria criteria object + * @return TDbCommand find by criteria command. + */ + public function getFindByCriteriaCommand($conn, $criteria=null) + { + $columns = $this->getSelectionColumns(); + $conditions = $criteria!==null?$this->getSqlFromCriteria($criteria) : ''; + $table = $this->getTableName(); + $sql = "SELECT {$columns} FROM {$table} {$conditions}"; + return $this->createCriteriaBindedCommand($conn,$sql, $criteria); + } + + /** + * Command to count the number of record matching the criteria. + * @param TDbConnection database connection. + * @param TActiveRecordCriteria criteria object + * @return TDbCommand count command. + * */ + public function getCountRecordsCommand($conn, $criteria) + { + $columns = $this->getSelectionColumns(); + $conditions = $this->getSqlFromCriteria($criteria); + $table = $this->getTableName(); + $sql = "SELECT count(*) FROM {$table} {$conditions}"; + return $this->createCriteriaBindedCommand($conn,$sql, $criteria); + } + + abstract protected function getSqlFromCriteria(TActiveRecordCriteria $criteria); + + /** + * Sql command with parameters binded. + * @param TDbConnection database connection. + * @param string sql query. + * @param array parameters to be bound + * @return TDbCommand sql command. + */ + public function getFindBySqlCommand($conn,$sql,$parameters) + { + $conn->setActive(true); + $command = $conn->createCommand($sql); + $this->bindParameterValues($conn,$command,$parameters); + return $command; + } + + /** + * SQL database command for insert a new record. + * @param TDbConnection database connection. + * @param TActiveRecord new record to be inserted. + * @return TDbCommand active record insert command + */ + public function getInsertCommand($conn, $record) + { + $columns = $this->getInsertableColumns($record); + $fields = $this->getInsertColumNames($columns); + $inserts = $this->getInsertColumnValues($columns); + $table = $this->getTableName(); + $sql = "INSERT INTO {$table} ({$fields}) VALUES ({$inserts})"; + return $this->createBindedCommand($conn, $sql, array_keys($columns), $columns); + } + + /** + * Update the record object's sequence values after insert. + * @param TDbConnection database connection. + * @param TActiveRecord record object. + */ + public function updatePostInsert($conn, $record) + { + foreach($this->getColumns() as $name => $column) + { + if($column->hasSequence()) + $record->{$name} = $conn->getLastInsertID($column->getSequenceName()); + } + } + + /** + * SQL database command to update an active record. + * @param TDbConnection database connection. + * @param TActiveRecord record for update. + * @return TDbCommand update command. + */ + public function getUpdateCommand($conn,$record) + { + $primaryKeys = $this->getPrimaryKeyCriteria(); + $columns = $this->getUpdatableColumns($record); + $updates = $this->getUpdateBindings($columns); + $table = $this->getTableName(); + $sql = "UPDATE {$table} SET {$updates} WHERE {$primaryKeys}"; + $primaryKeyValues = $this->getObjectKeyValues($this->getPrimaryKeys(), $record); + $values = array_merge($columns, $primaryKeyValues); + return $this->createBindedCommand($conn, $sql, array_keys($values), $values); + } + + /** + * SQL database command to delete an active record. + * @param TDbConnection database connection. + * @param TActiveRecord record for deletion. + * @return TDbCommand delete command. + */ + public function getDeleteCommand($conn,$record) + { + $primaryKeys = $this->getPrimaryKeyCriteria(); + $table = $this->getTableName(); + $sql = "DELETE FROM {$table} WHERE {$primaryKeys}"; + $keys = $this->getPrimaryKeys(); + $values = $this->getObjectKeyValues($keys, $record); + return $this->createBindedCommand($conn,$sql, $keys, $values); + } + + /** + * SQL command to delete records by primary keys. + * @param TDbConnection database connection. + * @param array list of primary keys + * @return TDbCommand delete command. + */ + public function getDeleteByPkCommand($conn,$keys) + { + $conn->setActive(true); + $numKeys = count($this->getPrimaryKeys()); + if($numKeys===0) + throw new TActiveRecordException('ar_no_primary_key_found',$this->getTableName()); + $table = $this->getTableName(); + if($numKeys===1) + $criteria = $this->getDeleteInPkCriteria($conn,$keys); + else + $criteria = $this->getDeleteMultiplePkCriteria($conn,$keys); + $sql = "DELETE FROM {$table} WHERE {$criteria}"; + $command = $conn->createCommand($sql); + $command->prepare(); + return $command; + } +} + +?> \ No newline at end of file diff --git a/framework/Data/ActiveRecord/Vendor/TDbMetaDataInspector.php b/framework/Data/ActiveRecord/Vendor/TDbMetaDataInspector.php new file mode 100644 index 00000000..ee7f339e --- /dev/null +++ b/framework/Data/ActiveRecord/Vendor/TDbMetaDataInspector.php @@ -0,0 +1,79 @@ + + * @version $Id$ + * @package System.Data.ActiveRecord.Vendor + */ + +/** + * Base class for database meta data inspectors. + * + * @author Wei Zhuo + * @version $Id$ + * @package System.Data.ActiveRecord.Vendor + * @since 3.1 + */ +abstract class TDbMetaDataInspector +{ + private $_connection; + + public function __construct($conn) + { + $this->setDbConnection($conn); + } + + /** + * @param TDbConnection database connection. + */ + public function setDbConnection($conn) + { + $this->_connection=$conn; + } + + /** + * @return TDbConnection database connection. + */ + public function getDbConnection() + { + return $this->_connection; + } + + /** + * @param string table name + * @return TDbMetaData table meta data. + */ + public function getTableMetaData($table) + { + $keys = $this->getConstraintKeys($table); + $columns = $this->getColumnDefinitions($table); + return $this->createMetaData($table,$columns,$keys['primary'], $keys['foreign']); + } + + /** + * Get the column definitions for given table. + * @param string table name. + * @return array column name value pairs of column meta data. + */ + abstract protected function getColumnDefinitions($table); + + /** + * Gets the primary and foreign key details for the given table. + * @param string table name. + * @return array key value pairs with keys 'primary' and 'foreign'. + */ + abstract protected function getConstraintKeys($table); + + /** + * Create a new instance of meta data. + * @param string table name + * @param array column meta data + * @param array primary key meta data + * @param array foreign key meta data. + * @return TDbMetaData table meta data. + */ + abstract protected function createMetaData($table, $columns, $primary, $foreign); +} + +?> \ No newline at end of file diff --git a/framework/Data/ActiveRecord/Vendor/TMysqlColumnMetaData.php b/framework/Data/ActiveRecord/Vendor/TMysqlColumnMetaData.php new file mode 100644 index 00000000..8f4abf99 --- /dev/null +++ b/framework/Data/ActiveRecord/Vendor/TMysqlColumnMetaData.php @@ -0,0 +1,105 @@ + + * @version $Id$ + * @package System.Data.ActiveRecord.Vendor + */ + +/** + * Column meta data for Mysql database. + * + * @author Wei Zhuo + * @version $Id$ + * @package System.Data.ActiveRecord.Vendor + * @since 3.1 + */ +class TMysqlColumnMetaData extends TComponent +{ + private $_name; + private $_type; + private $_autoIncrement; + private $_default; + private $_notNull=true; + + private $_isPrimary=null; + + /** + * Initialize column meta data. + * + * @param string column name. + * @param string column data type. + * @param string column data length. + * @param boolean column can not be null. + * @param string serial name. + * @param string default value. + */ + public function __construct($name,$type,$notNull,$autoIncrement,$default,$primary) + { + $this->_name=$name; + $this->_type=$type; + $this->_notNull=$notNull; + $this->_autoIncrement=$autoIncrement; + $this->_default=$default; + $this->_isPrimary=$primary; + } + + /** + * @return string quoted column name. + */ + public function getName() + { + return $this->_name; + } + + /** + * @return boolean true if column is a sequence, false otherwise. + */ + public function hasSequence() + { + return $this->_autoIncrement; + } + + /** + * @return null no sequence name. + */ + public function getSequenceName() + { + return null; + } + + /** + * @return boolean true if the column is a primary key, or part of a composite primary key. + */ + public function getIsPrimaryKey() + { + return $this->_isPrimary; + } + + public function getType() + { + return $this->_type; + } + + + public function getNotNull() + { + return $this->_notNull; + } + + /** + * @return boolean true if column has default value, false otherwise. + */ + public function hasDefault() + { + return $this->_default !== null; + } + + public function getDefaultValue() + { + return $this->_default; + } +} + +?> \ No newline at end of file diff --git a/framework/Data/ActiveRecord/Vendor/TMysqlMetaData.php b/framework/Data/ActiveRecord/Vendor/TMysqlMetaData.php new file mode 100644 index 00000000..7902146d --- /dev/null +++ b/framework/Data/ActiveRecord/Vendor/TMysqlMetaData.php @@ -0,0 +1,47 @@ + + * @version $Id$ + * @package System.Data.ActiveRecord.Vendor + */ + +Prado::using('System.Data.ActiveRecord.Vendor.TDbMetaDataCommon'); + +/** + * TMysqlMetaData specialized command builder for Mysql database. + * + * @author Wei Zhuo + * @version $Id$ + * @package System.Data.ActiveRecord.Vendor + * @since 3.1 + */ +class TMysqlMetaData extends TDbMetaDataCommon +{ + /** + * Build the SQL search string from the criteria object for Postgress database. + * @param TActiveRecordCriteria search criteria. + * @return string SQL search. + */ + protected function getSqlFromCriteria(TActiveRecordCriteria $criteria) + { + $sql = ''; + if(($condition = $criteria->getCondition())!==null) + $sql .= $condition; + $orders=array(); + foreach($criteria->getOrdersBy() as $by=>$ordering) + $orders[] = $by.' '.$ordering; + if(count($orders) > 0) + $sql .= ' ORDER BY '.implode(', ', $orders); + if(($limit = $criteria->getLimit())!==null) + { + $offset = $criteria->getOffset(); + $offset = $offset===null?0:$offset; + $sql .= ' LIMIT '.$offset.', '.$limit; + } + return strlen($sql) > 0 ? ' WHERE '.$sql : ''; + } +} + +?> \ No newline at end of file diff --git a/framework/Data/ActiveRecord/Vendor/TMysqlMetaDataInspector.php b/framework/Data/ActiveRecord/Vendor/TMysqlMetaDataInspector.php new file mode 100644 index 00000000..6075d2bc --- /dev/null +++ b/framework/Data/ActiveRecord/Vendor/TMysqlMetaDataInspector.php @@ -0,0 +1,80 @@ + + * @version $Id$ + * @package System.Data.ActiveRecord.Vendor + */ + +Prado::using('System.Data.ActiveRecord.Vendor.TDbMetaDataInspector'); +Prado::using('System.Data.ActiveRecord.Vendor.TMysqlColumnMetaData'); +Prado::using('System.Data.ActiveRecord.Vendor.TMysqlMetaData'); + +/** + * TMysqlMetaDataInspector class. + * + * Gathers table column properties for Mysql database. + * + * @author Wei Zhuo + * @version $Id$ + * @package System.Data.ActiveRecord.Vendor + * @since 3.1 + */ +class TMysqlMetaDataInspector extends TDbMetaDataInspector +{ + /** + * Get the column definitions for given table. + * @param string table name. + * @return array column name value pairs of column meta data. + */ + protected function getColumnDefinitions($table) + { + $sql="SHOW FULL FIELDS FROM `{$table}`"; + $conn = $this->getDbConnection(); + $conn->setActive(true); + $command = $conn->createCommand($sql); + $command->prepare(); + foreach($command->query() as $col) + $cols[$col['Field']] = $this->getColumnMetaData($col); + return $cols; + } + + protected function getColumnMetaData($col) + { + $name = '`'.$col['Field'].'`'; //quote the column names! + $type = $col['Type']; + $notNull = $col['Null']==='NO'; + $autoIncrement=is_int(strpos(strtolower($col['Extra']), 'auto_increment')); + $default = $col['Default']; + $primaryKey = $col['Key']==='PRI'; + return new TMysqlColumnMetaData($name,$type,$notNull,$autoIncrement,$default,$primaryKey); + } + + /** + * Not implemented, Mysql does not always have foreign key constraints. + */ + protected function getConstraintKeys($table) + { + return array('primary'=>array(), 'foreign'=>array()); + } + + /** + * Create a new instance of meta data. + * @param string table name + * @param array column meta data + * @param array primary key meta data + * @param array foreign key meta data. + * @return TDbMetaData table meta data. + */ + protected function createMetaData($table, $columns, $primary, $foreign) + { + $pks = array(); + foreach($columns as $name=>$column) + if($column->getIsPrimaryKey()) + $pks[] = $name; + return new TMysqlMetaData($table,$columns,$pks); + } +} + +?> \ No newline at end of file diff --git a/framework/Data/ActiveRecord/Vendor/TPgsqlColumnMetaData.php b/framework/Data/ActiveRecord/Vendor/TPgsqlColumnMetaData.php new file mode 100644 index 00000000..2b801b09 --- /dev/null +++ b/framework/Data/ActiveRecord/Vendor/TPgsqlColumnMetaData.php @@ -0,0 +1,121 @@ + + * @version $Id$ + * @package System.Data.ActiveRecord.Vendor + */ + +/** + * Column meta data for Postgre 7.3 or later. + * + * @author Wei Zhuo + * @version $Id$ + * @package System.Data.ActiveRecord.Vendor + * @since 3.1 + */ +class TPgsqlColumnMetaData extends TComponent +{ + private $_name; + private $_type; + private $_sequenceName; + private $_default; + private $_length; + private $_notNull=true; + + private $_isPrimary=null; + + /** + * Initialize column meta data. + * + * @param string column name. + * @param string column data type. + * @param string column data length. + * @param boolean column can not be null. + * @param string serial name. + * @param string default value. + */ + public function __construct($name,$type,$length,$notNull,$serial,$default) + { + $this->_name=$name; + $this->_type=$type; + $this->_length=$length; + $this->_notNull=$notNull; + $this->_sequenceName=$serial; + $this->_default=$default; + } + + /** + * @return string quoted column name. + */ + public function getName() + { + return $this->_name; + } + + /** + * @return boolean true if column is a sequence, false otherwise. + */ + public function hasSequence() + { + return $this->_sequenceName != null; + } + + /** + * @return string sequence name, only applicable if column is a sequence. + */ + public function getSequenceName() + { + return $this->_sequenceName; + } + + /** + * Set the column as primary key + */ + public function setIsPrimaryKey($value) + { + if($this->_isPrimary===null) + $this->_isPrimary=$value; + else + throw new TActiveRecordException('ar_column_meta_data_read_only'); + } + + /** + * @return boolean true if the column is a primary key, or part of a composite primary key. + */ + public function getIsPrimaryKey() + { + return $this->_isPrimary===null? false : $this->_isPrimary; + } + + public function getType() + { + return $this->_type; + } + + public function getLength() + { + return $this->_length; + } + + public function getNotNull() + { + return $this->_notNull; + } + + /** + * @return boolean true if column has default value, false otherwise. + */ + public function hasDefault() + { + return $this->_default !== null; + } + + public function getDefaultValue() + { + return $this->_default; + } +} + +?> \ No newline at end of file diff --git a/framework/Data/ActiveRecord/Vendor/TPgsqlMetaData.php b/framework/Data/ActiveRecord/Vendor/TPgsqlMetaData.php new file mode 100644 index 00000000..7f4f1f82 --- /dev/null +++ b/framework/Data/ActiveRecord/Vendor/TPgsqlMetaData.php @@ -0,0 +1,46 @@ + + * @version $Id$ + * @package System.Data.ActiveRecord.Vendor + */ + +Prado::using('System.Data.ActiveRecord.Vendor.TDbMetaDataCommon'); + +/** + * TPgsqlMetaData class. + * + * Command builder for Postgres database + * + * @author Wei Zhuo + * @version $Id$ + * @package System.Data.ActiveRecord.Vendor + * @since 3.1 + */ +class TPgsqlMetaData extends TDbMetaDataCommon +{ + /** + * Build the SQL search string from the criteria object for Postgress database. + * @param TActiveRecordCriteria search criteria. + * @return string SQL search. + */ + protected function getSqlFromCriteria(TActiveRecordCriteria $criteria) + { + $sql = ''; + if(($condition = $criteria->getCondition())!==null) + $sql .= $condition; + $orders=array(); + foreach($criteria->getOrdersBy() as $by=>$ordering) + $orders[] = $by.' '.$ordering; + if(count($orders) > 0) + $sql .= ' ORDER BY '.implode(', ', $orders); + if(($limit = $criteria->getLimit())!==null) + $sql .= ' LIMIT '.$limit; + if(($offset = $criteria->getOffset())!==null) + $sql .= ' OFFSET '.$offset; + return strlen($sql) > 0 ? ' WHERE '.$sql : ''; + } +} +?> \ No newline at end of file diff --git a/framework/Data/ActiveRecord/Vendor/TPgsqlMetaDataInspector.php b/framework/Data/ActiveRecord/Vendor/TPgsqlMetaDataInspector.php new file mode 100644 index 00000000..df31b9c0 --- /dev/null +++ b/framework/Data/ActiveRecord/Vendor/TPgsqlMetaDataInspector.php @@ -0,0 +1,223 @@ + + * @version $Id$ + * @package System.Data.ActiveRecord.Vendor + */ + +Prado::using('System.Data.ActiveRecord.Vendor.TDbMetaDataInspector'); +Prado::using('System.Data.ActiveRecord.Vendor.TPgsqlColumnMetaData'); +Prado::using('System.Data.ActiveRecord.Vendor.TPgsqlMetaData'); + +/** + * Table meta data inspector for Postgres database 7.3 or later. + * + * @author Wei Zhuo + * @version $Id$ + * @package System.Data.ActiveRecord.Vendor + * @since 3.1 + */ +class TPgsqlMetaDataInspector extends TDbMetaDataInspector +{ + private $_schema = 'public'; + + /** + * @param string default schema. + */ + public function setDefaultSchema($schema) + { + $this->_schema=$schema; + } + + /** + * @return string default schema. + */ + public function getDefaultSchema() + { + return $this->_schema; + } + + /** + * Create a new instance of meta data. + * @param string table name + * @param array column meta data + * @param array primary key meta data + * @param array foreign key meta data. + * @return TDbMetaData table meta data. + */ + protected function createMetaData($table, $columns, $primary, $foreign) + { + foreach($primary as $column) + $columns[$column]->setIsPrimaryKey(true); + return new TPgsqlMetaData($table,$columns,$primary,$foreign,$this->getIsView($table)); + } + + protected function getIsView($table) + { + $sql = +<<getDbConnection(); + $conn->setActive(true); + $command=$conn->createCommand($sql); + $command->bindValue(':schema',$this->getDefaultSchema()); + $command->bindValue(':table', $table); + return intval($command->queryScalar()) === 1; + } + + /** + * Get the column definitions for given table. + * @param string table name. + * @return array column name value pairs of column meta data. + */ + protected function getColumnDefinitions($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 = +<< 0 AND NOT a.attisdropped + ORDER BY a.attnum +EOD; + $conn = $this->getDbConnection(); + $conn->setActive(true); + $command = $conn->createCommand($sql); + $command->bindValue(':table', $table); + $command->bindValue(':schema', $this->getDefaultSchema()); + $cols = array(); + foreach($command->query() as $col) + $cols[$col['attname']] = $this->getColumnMetaData($col); + return $cols; + } + + /** + * Returns the column details. + * @param array column details. + * @return TPgsqlColumnMetaData column meta data. + */ + protected function getColumnMetaData($col) + { + $name = '"'.$col['attname'].'"'; //quote the column names! + $type = $col['type']; + + // A specific constant in the 7.0 source, the length is offset by 4. + $length = $col['atttypmod'] > 0 ? $col['atttypmod'] - 4 : -1; + $notNull = $col['attnotnull']; + $serial = $col['attisserial'] ? $this->getSerialName($col['adsrc']) : null; + $default = $serial === null && $col['atthasdef'] ? $col['adsrc'] : null; + return new TPgsqlColumnMetaData($name,$type,$length,$notNull,$serial,$default); + } + + /** + * @return string serial name if found, null otherwise. + */ + protected function getSerialName($src) + { + $matches = array(); + if(preg_match('/nextval\(\'([^\']+)\'::regclass\)/i',$src,$matches)) + return $matches[1]; + } + + /** + * Gets the primary and foreign key details for the given table. + * @param string table name. + * @return array key value pairs with keys 'primary' and 'foreign'. + */ + protected function getConstraintKeys($table) + { + $sql = 'SELECT + pg_catalog.pg_get_constraintdef(pc.oid, true) AS consrc, + pc.contype + FROM + pg_catalog.pg_constraint pc + WHERE + pc.conrelid = (SELECT oid FROM pg_catalog.pg_class WHERE relname=:table + AND relnamespace = (SELECT oid FROM pg_catalog.pg_namespace + WHERE nspname=:schema)) + '; + $this->getDbConnection()->setActive(true); + $command = $this->getDbConnection()->createCommand($sql); + $command->bindValue(':table', $table); + $command->bindValue(':schema', $this->getDefaultSchema()); + $keys['primary'] = array(); + $keys['foreign'] = array(); + foreach($command->query() as $row) + { + if($row['contype']==='p') + $keys['primary'] = $this->getPrimaryKeys($row['consrc']); + else if($row['contype'] === 'f') + { + $fkey = $this->getForeignKeys($row['consrc']); + if($fkey!==null) + $keys['foreign'][] = $fkey; + } + } + return $keys; + } + + /** + * Gets the primary key field names + * @param string pgsql 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 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' => $matches[2], 'keys' => $fkeys); + } + } +} + +?> \ No newline at end of file diff --git a/framework/Data/ActiveRecord/Vendor/TSqliteColumnMetaData.php b/framework/Data/ActiveRecord/Vendor/TSqliteColumnMetaData.php new file mode 100644 index 00000000..94029cfa --- /dev/null +++ b/framework/Data/ActiveRecord/Vendor/TSqliteColumnMetaData.php @@ -0,0 +1,96 @@ + + * @version $Id$ + * @package System.Data.ActiveRecord.Vendor + */ + +/** + * TSqliteColumnMetaData class. + * + * Column details for SQLite version 2.x or 3.x. database. + * + * @author Wei Zhuo + * @version $Id$ + * @package System.Data.ActiveRecord.Vendor + * @since 3.1 + */ +class TSqliteColumnMetaData extends TComponent +{ + private $_name; + private $_type; + private $_notNull; + private $_autoIncrement; + private $_default; + private $_primary=false; + + public function __construct($name,$type,$notNull,$autoIncrement,$default,$primary) + { + $this->_name=$name; + $this->_type=$type; + $this->_notNull=$notNull; + $this->_autoIncrement=$autoIncrement; + $this->_default=$default; + $this->_primary=$primary; + } + + /** + * @return string quoted column name. + */ + public function getName() + { + return $this->_name; + } + + /** + * @return boolean true if column is a sequence, false otherwise. + */ + public function hasSequence() + { + return $this->_autoIncrement; + } + + /** + * @return null no sequence name. + */ + public function getSequenceName() + { + return null; + } + + /** + * @return boolean true if the column is a primary key, or part of a composite primary key. + */ + public function getIsPrimaryKey() + { + return $this->_primary; + } + + public function getType() + { + return $this->_type; + } + + + public function getNotNull() + { + return $this->_notNull; + } + + /** + * @return boolean true if column has default value, false otherwise. + */ + public function hasDefault() + { + return $this->_default !== null; + } + + public function getDefaultValue() + { + return $this->_default; + } +} + +?> \ No newline at end of file diff --git a/framework/Data/ActiveRecord/Vendor/TSqliteMetaData.php b/framework/Data/ActiveRecord/Vendor/TSqliteMetaData.php new file mode 100644 index 00000000..a5f45090 --- /dev/null +++ b/framework/Data/ActiveRecord/Vendor/TSqliteMetaData.php @@ -0,0 +1,73 @@ + + * @version $Id$ + * @package System.Data.ActiveRecord.Vendor + */ + +Prado::using('System.Data.ActiveRecord.Vendor.TDbMetaDataCommon'); + +/** + * TSqliteMetaData specialized command builder for SQLite database. + * + * @author Wei Zhuo + * @version $Id$ + * @package System.Data.ActiveRecord.Vendor + * @since 3.1 + */ +class TSqliteMetaData extends TDbMetaDataCommon +{ + /** + * Build the SQL search string from the criteria object for Postgress database. + * @param TActiveRecordCriteria search criteria. + * @return string SQL search. + */ + protected function getSqlFromCriteria(TActiveRecordCriteria $criteria) + { + $sql = ''; + if(($condition = $criteria->getCondition())!==null) + $sql .= $condition; + $orders=array(); + foreach($criteria->getOrdersBy() as $by=>$ordering) + $orders[] = $by.' '.$ordering; + if(count($orders) > 0) + $sql .= ' ORDER BY '.implode(', ', $orders); + if(($limit = $criteria->getLimit())!==null) + { + $offset = $criteria->getOffset(); + $offset = $offset===null?0:$offset; + $sql .= ' LIMIT '.$offset.', '.$limit; + } + return strlen($sql) > 0 ? ' WHERE '.$sql : ''; + } + + /** + * Remove quote from the keys in the data. + * @param mixed record row + * @return array record row + */ + public function postQueryRow($row) + { + if(!is_array($row)) return $row; + $result=array(); + foreach($row as $k=>$v) + $result[str_replace('"','',$k)]=$v; + return $result; + } + + /** + * Remove quote from the keys in the data. + * @param mixed record row + * @return array record row + */ + public function postQuery($rows) + { + foreach($rows as $k=>$v) + $rows[$k] = $this->postQueryRow($v); + return $rows; + } +} + +?> \ No newline at end of file diff --git a/framework/Data/ActiveRecord/Vendor/TSqliteMetaDataInspector.php b/framework/Data/ActiveRecord/Vendor/TSqliteMetaDataInspector.php new file mode 100644 index 00000000..07fa3187 --- /dev/null +++ b/framework/Data/ActiveRecord/Vendor/TSqliteMetaDataInspector.php @@ -0,0 +1,85 @@ + + * @version $Id$ + * @package System.Data.ActiveRecord.Vendor + */ + +Prado::using('System.Data.ActiveRecord.Vendor.TDbMetaDataInspector'); +Prado::using('System.Data.ActiveRecord.Vendor.TSqliteColumnMetaData'); +Prado::using('System.Data.ActiveRecord.Vendor.TSqliteMetaData'); + +/** + * Table meta data inspector for Sqlite database. + * + * @author Wei Zhuo + * @version $Id$ + * @package System.Data.ActiveRecord.Vendor + * @since 3.1 + */ +class TSqliteMetaDataInspector extends TDbMetaDataInspector +{ + /** + * Create a new instance of meta data. + * @param string table name + * @param array column meta data + * @param array primary key meta data + * @param array foreign key meta data. + * @return TDbMetaData table meta data. + */ + protected function createMetaData($table, $columns, $primary, $foreign) + { + $pks = array(); + foreach($columns as $name=>$column) + if($column->getIsPrimaryKey()) + $pks[] = $name; + return new TSqliteMetaData($table,$columns,$pks); + } + + /** + * Get the column definitions for given table. + * @param string table name. + * @return array column name value pairs of column meta data. + */ + protected function getColumnDefinitions($table) + { + $conn=$this->getDbConnection(); + $conn->setActive(true); + $table = $conn->quoteString($table); + $command = $conn->createCommand("PRAGMA table_info({$table})"); + $command->prepare(); + $cols = array(); + foreach($command->query() as $col) + $cols[$col['name']] = $this->getColumnMetaData($col); + return $cols; + } + + /** + * Returns the column details. + * @param array column details. + * @return TPgsqlColumnMetaData column meta data. + */ + protected function getColumnMetaData($col) + { + $name = '"'.$col['name'].'"'; //quote the column names! + $type = $col['type']; + + $notNull = $col['notnull']==='99'; + $primary = $col['pk']==='1'; + $autoIncrement = strtolower($type)==='integer' && $primary; + $default = $col['dflt_value']; + return new TSqliteColumnMetaData($name,$type,$notNull,$autoIncrement,$default,$primary); + } + + /** + * Not implemented, sqlite does not have foreign key constraints. + */ + protected function getConstraintKeys($table) + { + return array('primary'=>array(), 'foreign'=>array()); + } +} + +?> \ No newline at end of file -- cgit v1.2.3