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. --- .../Exceptions/TActiveRecordException.php | 39 +++ .../Data/ActiveRecord/Exceptions/messages.txt | 12 + framework/Data/ActiveRecord/TActiveRecord.php | 390 +++++++++++++++++++++ .../Data/ActiveRecord/TActiveRecordCriteria.php | 143 ++++++++ .../Data/ActiveRecord/TActiveRecordGateway.php | 305 ++++++++++++++++ .../Data/ActiveRecord/TActiveRecordManager.php | 213 +++++++++++ .../ActiveRecord/TActiveRecordStateRegistry.php | 262 ++++++++++++++ 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 +++++ framework/Data/TDbCommand.php | 10 +- 20 files changed, 2864 insertions(+), 3 deletions(-) create mode 100644 framework/Data/ActiveRecord/Exceptions/TActiveRecordException.php create mode 100644 framework/Data/ActiveRecord/Exceptions/messages.txt create mode 100644 framework/Data/ActiveRecord/TActiveRecord.php create mode 100644 framework/Data/ActiveRecord/TActiveRecordCriteria.php create mode 100644 framework/Data/ActiveRecord/TActiveRecordGateway.php create mode 100644 framework/Data/ActiveRecord/TActiveRecordManager.php create mode 100644 framework/Data/ActiveRecord/TActiveRecordStateRegistry.php 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') diff --git a/framework/Data/ActiveRecord/Exceptions/TActiveRecordException.php b/framework/Data/ActiveRecord/Exceptions/TActiveRecordException.php new file mode 100644 index 00000000..ec177a84 --- /dev/null +++ b/framework/Data/ActiveRecord/Exceptions/TActiveRecordException.php @@ -0,0 +1,39 @@ + + * @version $Id$ + * @package System.Data.ActiveRecord.Exceptions + */ + +/** + * Base exception class for Active Records. + * + * @author Wei Zhuo + * @version $Id$ + * @package System.Data.ActiveRecord.Exceptions + * @since 3.1 + */ +class TActiveRecordException extends TException +{ + /** + * @return string path to the error message file + */ + protected function getErrorMessageFile() + { + $lang=Prado::getPreferredLanguage(); + $path = dirname(__FILE__); + $msgFile=$path.'/messages-'.$lang.'.txt'; + if(!is_file($msgFile)) + $msgFile=$path.'/messages.txt'; + return $msgFile; + } +} + +class TActiveRecordConfigurationException extends TActiveRecordException +{ + +} + +?> \ No newline at end of file diff --git a/framework/Data/ActiveRecord/Exceptions/messages.txt b/framework/Data/ActiveRecord/Exceptions/messages.txt new file mode 100644 index 00000000..f77b2275 --- /dev/null +++ b/framework/Data/ActiveRecord/Exceptions/messages.txt @@ -0,0 +1,12 @@ +ar_readonly_exception = Active Record '{0}' is read only. +ar_object_must_be_retrieved_before_delete = Active Record must be retrieved first before deletion. +ar_object_must_not_be_null = Active record object must not be null. +ar_object_marked_for_removal = Active record object already marked for removed. +ar_column_meta_data_read_only = Column meta is read only. +ar_invalid_database_driver = Active Record does not support database '{0}'. +ar_invalid_finder_method = Unsupported Active Record finder method '{0}'. +ar_no_primary_key_found = Table '{0}' does not contain any primary key fields. +ar_primary_key_is_scalar = Primary key '{1}' in table '{0}' is NOT a composite key, invalid value '{2} used. +ar_invalid_db_connection = Missing or invalid default database connection for ActiveRecord class '{0}', default connection is set by the DbConnection property of TActiveRecordManager. +ar_mismatch_args_exception = ActiveRecord finder method '{0}' expects {1} parameters but found only {2} parameters instead. +ar_invalid_tablename_property = ActiveRecord tablename property '{0}::${1}' must be static and not null. \ No newline at end of file diff --git a/framework/Data/ActiveRecord/TActiveRecord.php b/framework/Data/ActiveRecord/TActiveRecord.php new file mode 100644 index 00000000..fd49e413 --- /dev/null +++ b/framework/Data/ActiveRecord/TActiveRecord.php @@ -0,0 +1,390 @@ + + * @version $Id$ + * @package System.Data.ActiveRecord + */ + +Prado::using('System.Data.ActiveRecord.TActiveRecordManager'); +Prado::using('System.Data.ActiveRecord.TActiveRecordCriteria'); + +/** + * Base class for active records. + * + * An active record creates an object that wraps a row in a database table + * or view, encapsulates the database access, and adds domain logic on that data. + * + * The essence of an Active Record is an object model of the + * domain (e.g. products, items) that incorporates both behavior and + * data in which the classes match very closely the record structure of an + * underlying database. Each Active Record is responsible for saving and + * loading to the database and also for any domain logic that acts on the data. + * + * The Active Record provides methods that do the following: + * 1. Construct an instance of the Active Record from a SQL result set row. + * 2. Construct a new instance for later insertion into the table. + * 3. Finder methods to wrap commonly used SQL queries and return Active Record objects. + * 4. Update the database and insert into it the data in the Active Record. + * + * Example: + * + * class UserRecord extends TActiveRecord + * { + * public $username; //corresponds to the fieldname in the table + * public $email; + * + * private static final $_tablename='users'; //optional table name. + * + * //returns active record finder instance + * public static function finder() + * { + * return self::getRecordFinder('UserRecord'); + * } + * } + * + * //create a connection and give it to the ActiveRecord manager. + * $conn = new TDbConnection('pgsql:host=localhost;dbname=test', 'dbuser','dbpass'); + * TActiveRecordManager::getInstance()->setDbConnection($conn); + * + * //load the user record with username (primary key) 'admin'. + * $user = UserRecord::finder()->findByPk('admin'); + * $user->email = 'admin@example.org'; + * $user->save(); //update the 'admin' record. + * + * + * @author Wei Zhuo + * @version $Id$ + * @package System.Data.ActiveRecord + * @since 3.1 + */ +abstract class TActiveRecord extends TComponent +{ + /** + * @var boolean true if this class is read only. + */ + private $_readOnly=false; + + /** + * @var TDbConnection database connection object. + */ + private $_connection; + + /** + * Prevent __call() method creating __sleep() when serializing. + */ + public function __sleep() + { + return array_keys(get_object_vars($this)); + } + + /** + * Prevent __call() method creating __wake() when unserializing. + */ + public function __wake(){} + + /** + * Create a new instance of an active record with given $data. The record + * can be saved to the database specified by the $connection object. + * + * @param array optional name value pair record data. + * @param TDbConnection optional database connection this object record use. + */ + public function __construct($data=array(), $connection=null) + { + foreach($data as $name=>$value) + $this->$name = $value; + if($connection!==null) + $this->_connection=$connection; + } + + /** + * Gets the current Db connection, the connection object is obtained from + * the TActiveRecordManager if connection is currently null. + * @return TDbConnection current db connection for this object. + */ + public function getDbConnection() + { + if($this->_connection===null) + $this->_connection=self::getRecordManager()->getDbConnection(); + if($this->_connection===null) //check it + throw new TActiveRecordException('ar_invalid_db_connection',get_class($this)); + return $this->_connection; + } + + /** + * @param TDbConnection db connection object for this record. + */ + public function setDbConnection($connection) + { + $this->_connection=$connection; + } + + /** + * Returns the instance of a active record finder for a particular class. + * @param string active record class name. + * @return TActiveRecord active record finder instance. + */ + public static function getRecordFinder($class) + { + static $finders = array(); + if(!isset($finders[$class])) + { + $f = Prado::createComponent($class); + $f->_readOnly=true; + $finders[$class]=$f; + } + return $finders[$class]; + } + + /** + * Gets the record manager for this object, the default is to call + * TActiveRecordManager::getInstance(). + * @return TActiveRecordManager default active record manager. + */ + public function getRecordManager() + { + return TActiveRecordManager::getInstance(); + } + + /** + * Saves the current record to the database, insert or update is automatically determined. + * @return boolean true if record was saved successfully, false otherwise. + */ + public function save() + { + $registry = $this->getRecordManager()->getObjectStateRegistry(); + $gateway = $this->getRecordManager()->getRecordGateway(); + if(!$this->_readOnly) + $this->_readOnly = $gateway->getMetaData($this)->getIsView(); + if($this->_readOnly) + throw new TActiveRecordException('ar_readonly_exception',get_class($this)); + return $registry->commit($this,$gateway); + } + + /** + * Deletes the current record from the database. Once deleted, this object + * can not be saved again in the same instance. + * @return boolean true if the record was deleted successfully, false otherwise. + */ + public function delete() + { + $registry = $this->getRecordManager()->getObjectStateRegistry(); + $registry->registerRemoved($this); + return $this->save(); + } + + /** + * Delete records by primary key. Usage: + * + * + * $finder->deleteByPk($primaryKey); //delete 1 record + * $finder->deleteByPk($key1,$key2,...); //delete multiple records + * $finder->deleteByPk(array($key1,$key2,...)); //delete multiple records + * + * + * For composite primary keys (determined from the table definitions): + * + * $finder->deleteByPk(array($key1,$key2)); //delete 1 record + * $finder->deleteByPk(array($key1,$key2), array($key3,$key4),...); //delete multiple records + * $finder->deleteByPk(array( array($key1,$key2), array($key3,$key4), .. )); //delete multiple records + * + * + * @param unknown_type $keys + * @return unknown + */ + public function deleteByPk($keys) + { + if(func_num_args() > 1) + $keys = func_get_args(); + $gateway = $this->getRecordManager()->getRecordGateway(); + return $gateway->deleteRecordsByPk($this,(array)$keys); + } + + /** + * Populate the record with data, registers the object as clean. + * @param string new record name + * @param array name value pair record data + * @return TActiveRecord object record, null if data is empty. + */ + protected function populateObject($type, $data) + { + if(empty($data)) return null; + $registry = $this->getRecordManager()->getObjectStateRegistry(); + + //try the cache (the cache object must be clean) + if(!is_null($obj = $registry->getCachedInstance($data))) + return $obj; + + //create and populate the object + $obj = Prado::createComponent($type); + foreach($data as $name => $value) + $obj->{$name} = $value; + + $gateway = $this->getRecordManager()->getRecordGateway(); + $obj->_readOnly = $gateway->getMetaData($this)->getIsView(); + + //cache it + return $registry->addCachedInstance($data,$obj); + } + + /** + * Find one single record that matches the criteria. + * + * Usage: + * + * $finder->find('username = :name AND password = :pass', array(':name'=>$name, ':pass'=>$pass)); + * $finder->find('username = ? AND password = ?', array($name, $pass)); + * $finder->find('username = ? AND password = ?', $name, $pass); + * //$criteria is of TActiveRecordCriteria + * $finder->find($criteria); //the 2nd parameter for find() is ignored. + * + * + * @param string|TActiveRecordCriteria SQL condition or criteria object. + * @param mixed parameter values. + * @return TActiveRecord matching record object. + */ + public function find($criteria,$parameters=array()) + { + if(is_string($criteria)) + { + if(!is_array($parameters) && func_num_args() > 1) + { + $parameters = func_get_args(); + array_shift($parameters); + } + $criteria=new TActiveRecordCriteria($criteria,$parameters); + } + $gateway = $this->getRecordManager()->getRecordGateway(); + $data = $gateway->findRecordsByCriteria($this,$criteria); + return $this->populateObject(get_class($this), $data); + } + + /** + * Same as find() but returns an array of objects. + * + * @param string|TActiveRecordCriteria SQL condition or criteria object. + * @param mixed parameter values. + * @return array matching record objects + */ + public function findAll($criteria=null,$parameters=array()) + { + if(is_string($criteria)) + { + if(!is_array($parameters) && func_num_args() > 1) + { + $parameters = func_get_args(); + array_shift($parameters); + } + $criteria=new TActiveRecordCriteria($criteria,$parameters); + } + $gateway = $this->getRecordManager()->getRecordGateway(); + $results = array(); + $class = get_class($this); + foreach($gateway->findRecordsByCriteria($this,$criteria,true) as $data) + $results[] = $this->populateObject($class,$data); + return $results; + } + + /** + * Find one record using only the primary key or composite primary keys. Usage: + * + * + * $finder->findByPk($primaryKey); + * $finder->findByPk($key1, $key2, ...); + * $finder->findByPk(array($key1,$key2,...)); + * + * + * @param mixed primary keys + * @return TActiveRecord + */ + public function findByPk($keys) + { + if(func_num_args() > 1 && !is_array($keys)) + $keys = func_get_args(); + $gateway = $this->getRecordManager()->getRecordGateway(); + $data = $gateway->findRecordByPK($this,$keys); + return $this->populateObject(get_class($this), $data); + } + + /** + * Find records using full SQL, returns corresponding record object. + * @param string select SQL + * @param array $parameters + * @return array matching active records. + */ + public function findBySql($sql,$parameters=array()) + { + $gateway = $this->getRecordManager()->getRecordGateway(); + $data = $gateway->findRecordsBySql($this,$sql,$parameters); + $results = array(); + $class = get_class($this); + foreach($gateway->findRecordsBySql($this,$sql,$parameters) as $data) + $results[] = $this->populateObject($class,$data); + return $results; + } + + /** + * Find the number of records. + * @param string|TActiveRecordCriteria SQL condition or criteria object. + * @param mixed parameter values. + * @return int number of records. + */ + public function count($criteria=null,$parameters=array()) + { + if(is_string($criteria)) + { + if(!is_array($parameters) && func_num_args() > 1) + { + $parameters = func_get_args(); + array_shift($parameters); + } + $criteria=new TActiveRecordCriteria($criteria,$parameters); + } + $gateway = $this->getRecordManager()->getRecordGateway(); + return $gateway->countRecords($this,$criteria); + } + + /** + * Dynamic find method using parts of method name as search criteria. + * Method name starting with "findBy" only returns 1 record. + * Method name starting with "findAllBy" returns 0 or more records. + * The condition is taken as part of the method name after "findBy" or "findAllBy". + * + * The following are equivalent: + * + * $finder->findByName($name) + * $finder->find('Name = ?', $name); + * + * + * $finder->findByUsernameAndPassword($name,$pass); + * $finder->findBy_Username_And_Password($name,$pass); + * $finder->find('Username = ? AND Password = ?', $name, $pass); + * + * + * $finder->findAllByAge($age); + * $finder->findAll('Age = ?', $age); + * + * @return mixed single record if method name starts with "findBy", 0 or more records + * if method name starts with "findAllBy" + */ + public function __call($method,$args) + { + if($findOne = substr(strtolower($method),0,6)==='findby') + $condition = $method[6]==='_' ? substr($method,7) : substr($method,6); + else if(substr(strtolower($method,0,9)==='findallby')) + $condition = $method[9]==='_' ? substr($method,10) : substr($method,9); + else + throw new TActiveRecordException('ar_invalid_finder_method',$method); + $fields = array(); + foreach(preg_split('/and|_and_/i',$condition) as $field) + $fields[] = $field.' = ?'; + $args=count($args) === 1 && is_array($args[0]) ? $args[0] : $args; + if(count($fields)>count($args)) + throw new TActiveRecordException('ar_mismatch_args_exception',$method,count($fields),count($args)); + $criteria = new TActiveRecordCriteria(implode(' AND ',$fields),$args); + return $findOne ? $this->find($criteria) : $this->findAll($criteria); + } +} +?> \ No newline at end of file diff --git a/framework/Data/ActiveRecord/TActiveRecordCriteria.php b/framework/Data/ActiveRecord/TActiveRecordCriteria.php new file mode 100644 index 00000000..8e7735a7 --- /dev/null +++ b/framework/Data/ActiveRecord/TActiveRecordCriteria.php @@ -0,0 +1,143 @@ + + * @version $Id$ + * @package System.Data.ActiveRecord + */ + +/** + * Search criteria for Active Record. + * + * Criteria object for active record finder methods. Usage: + * + * $criteria = new TActiveRecordCriteria; + * $criteria->Condition = 'username = :name AND password = :pass'; + * $criteria->Parameters[':name'] = 'admin'; + * $criteria->Parameters[':pass'] = 'prado'; + * $criteria->OrdersBy['level'] = 'desc'; + * $criteria->OrdersBy['name'] = 'asc'; + * $criteria->Limit = 10; + * $criteria->Offset = 20; + * + * + * @author Wei Zhuo + * @version $Id$ + * @package System.Data.ActiveRecord + * @since 3.1 + */ +class TActiveRecordCriteria extends TComponent +{ + private $_condition; + private $_parameters; + private $_ordersBy; + private $_limit; + private $_offset; + + /** + * Creates a new criteria with given condition; + */ + public function __construct($condition=null,$parameters=array()) + { + $this->setCondition($condition); + $this->_parameters=new TAttributeCollection((array)$parameters); + $this->_ordersBy=new TAttributeCollection; + } + + /** + * @return TAttributeCollection list of named parameters and values. + */ + public function getParameters() + { + return $this->_parameters; + } + + /** + * @param ArrayAccess named parameters. + */ + public function setParameters($value) + { + if(!(is_array($value) || $value instanceof ArrayAccess)) + throw new TException('value must be array or ArrayAccess'); + $this->_parameters->copyFrom($value); + } + + /** + * @return boolean true if the parameter index are string base, false otherwise. + */ + public function getIsNamedParameters() + { + foreach($this->getParameters() as $k=>$v) + return is_string($k); + } + + /** + * @return TAttributeCollection ordering clause. + */ + public function getOrdersBy() + { + return $this->_ordersBy; + } + + /** + * @param ArrayAccess ordering clause. + */ + public function setOrdersBy($value) + { + if(!(is_array($value) || $value instanceof ArrayAccess)) + throw new TException('value must be array or ArrayAccess'); + $this->_ordersBy->copyFrom($value); + } + + /** + * @return string search conditions. + */ + public function getCondition() + { + return $this->_condition; + } + + /** + * Sets the search conditions to be placed after the WHERE clause in the SQL. + * @param string search conditions. + */ + public function setCondition($value) + { + $this->_condition=$value; + } + + /** + * @return int maximum number of records to return. + */ + public function getLimit() + { + return $this->_limit; + } + + /** + * @param int maximum number of records to return. + */ + public function setLimit($value) + { + $this->_limit=$value; + } + + /** + * @return int record offset. + */ + public function getOffset() + { + return $this->_offset; + } + + /** + * @param int record offset. + */ + public function setOffset($value) + { + $this->_offset=$value; + } +} + +?> \ No newline at end of file diff --git a/framework/Data/ActiveRecord/TActiveRecordGateway.php b/framework/Data/ActiveRecord/TActiveRecordGateway.php new file mode 100644 index 00000000..829230fc --- /dev/null +++ b/framework/Data/ActiveRecord/TActiveRecordGateway.php @@ -0,0 +1,305 @@ + + * @version $Id$ + * @package System.Data.ActiveRecord + */ + +/** + * TActiveRecordGateway excutes the SQL command queries and returns the data + * record as arrays (for most finder methods). + * + * @author Wei Zhuo + * @version $Id$ + * @package System.Data.ActiveRecord + * @since 3.1 + */ +class TActiveRecordGateway extends TComponent +{ + private $_manager; + private $_tables=array(); + + /** + * Property name for optional table name in TActiveRecord. + */ + const PROPERTY_TABLE_NAME='_tablename'; + + /** + * Record gateway constructor. + * @param TActiveRecordManager $manager + */ + public function __construct(TActiveRecordManager $manager) + { + $this->_manager=$manager; + } + + /** + * @return TActiveRecordManager record manager. + */ + protected function getManager() + { + return $this->_manager; + } + + /** + * Gets the table name from the $_tablename property of the active record + * class if defined, otherwise use the class name as table name. + * @param TActiveRecord active record instance + * @return string table name for the given record class. + */ + public function getTableName(TActiveRecord $record) + { + $class = new ReflectionClass($record); + if($class->hasProperty(self::PROPERTY_TABLE_NAME)) + { + $value = $class->getProperty(self::PROPERTY_TABLE_NAME)->getValue(); + if($value===null) + throw new TActiveRecordException('ar_invalid_tablename_property', + get_class($record),self::PROPERTY_TABLE_NAME); + return $value; + } + else + return strtolower(get_class($record)); + } + + /** + * Gets the meta data for given database and table. + */ + public function getMetaData(TActiveRecord $record) + { + $type=get_class($record); + if(!isset($this->_tables[$type])) + { + $conn = $record->getDbConnection(); + $inspector = $this->getManager()->getTableInspector($conn); + $table = $this->getTableName($record); + $this->_tables[$type] = $inspector->getTableMetaData($table); + } + return $this->_tables[$type]; + } + + /** + * @param array table meta data. + */ + public function setAllMetaData($data) + { + $this->_tables=$data; + } + + /** + * @return array all table meta data. + */ + public function getAllMetaData() + { + return $this->_tables; + } + + /** + * Returns record data matching the given primary key(s). If the table uses + * composite key, specify the name value pairs as an array. + * @param TActiveRecord active record instance. + * @param array primary name value pairs + * @return array record data + */ + public function findRecordByPK(TActiveRecord $record,$keys) + { + $meta = $this->getMetaData($record); + $command = $meta->getFindByPkCommand($record->getDbConnection(),$keys); + $this->raiseCommandEvent(TActiveRecordStatementType::Select,$command,$record,$keys); + return $meta->postQueryRow($command->queryRow()); + } + + /** + * Returns record data matching the given critera. If $iterator is true, it will + * return multiple rows as TDbDataReader otherwise it returns the first row data. + * @param TActiveRecord active record finder instance. + * @param TActiveRecordCriteria search criteria. + * @param boolean true to return multiple rows as iterator, false returns first row. + * @return mixed matching data. + */ + public function findRecordsByCriteria(TActiveRecord $record, $criteria, $iterator=false) + { + $meta = $this->getMetaData($record); + $command = $meta->getFindByCriteriaCommand($record->getDbConnection(),$criteria); + $this->raiseCommandEvent(TActiveRecordStatementType::Select,$command,$record,$criteria); + return $iterator ? $meta->postQuery($command->query()) : $meta->postQueryRow($command->queryRow()); + } + + /** + * Return record data from sql query. + * @param TActiveRecord active record finder instance. + * @param string SQL string + * @param array query parameters. + * @return TDbDataReader result iterator. + */ + public function findRecordsBySql(TActiveRecord $record, $sql,$parameters=array()) + { + $meta = $this->getMetaData($record); + $command = $meta->getFindBySqlCommand($record->getDbConnection(),$sql,$parameters); + $this->raiseCommandEvent(TActiveRecordStatementType::Select,$command,$record,$parameters); + return $meta->postQuery($command->query()); + } + + /** + * Returns the number of records that match the given criteria. + * @param TActiveRecord active record finder instance. + * @param TActiveRecordCriteria search criteria + * @return int number of records. + */ + public function countRecords(TActiveRecord $record, $criteria) + { + $meta = $this->getMetaData($record); + $command = $meta->getCountRecordsCommand($record->getDbConnection(),$criteria); + $this->raiseCommandEvent(TActiveRecordStatementType::Select,$command,$record,$criteria); + return intval($command->queryScalar()); + } + + /** + * Insert a new record. + * @param TActiveRecord new record. + * @return int number of rows affected. + */ + public function insert(TActiveRecord $record) + { + $meta = $this->getMetaData($record); + $command = $meta->getInsertCommand($record->getDbConnection(),$record); + $this->raiseCommandEvent(TActiveRecordStatementType::Insert,$command,$record); + $rowsAffected = $command->execute(); + if($rowsAffected===1) + $meta->updatePostInsert($record->getDbConnection(),$record); + return $rowsAffected; + } + + /** + * Update the record. + * @param TActiveRecord dirty record. + * @return int number of rows affected. + */ + public function update(TActiveRecord $record) + { + $meta = $this->getMetaData($record); + $command = $meta->getUpdateCommand($record->getDbConnection(),$record); + $this->raiseCommandEvent(TActiveRecordStatementType::Update,$command,$record); + return $command->execute(); + } + + /** + * Delete the record. + * @param TActiveRecord record to be deleted. + * @return int number of rows affected. + */ + public function delete(TActiveRecord $record) + { + $meta = $this->getMetaData($record); + $command = $meta->getDeleteCommand($record->getDbConnection(),$record); + $this->raiseCommandEvent(TActiveRecordStatementType::Delete,$command,$record); + return $command->execute(); + } + + /** + * Delete multiple records using primary keys. + * @param TActiveRecord finder instance. + * @return int number of rows deleted. + */ + public function deleteRecordsByPk(TActiveRecord $record, $keys) + { + $meta = $this->getMetaData($record); + $command = $meta->getDeleteByPkCommand($record->getDBConnection(),$keys); + $this->raiseCommandEvent(TActiveRecordStatementType::Delete,$command,$record,$keys); + return $command->execute(); + } + + /** + * Raise the corresponding command event, insert, update, delete or select. + * @param string command type + * @param TDbCommand sql command to be executed. + * @param TActiveRecord active record + * @param mixed data for the command. + */ + protected function raiseCommandEvent($type,$command,$record=null,$data=null) + { + $param = new TActiveRecordGatewayEventParameter($type,$command,$record,$data); + $manager = $record->getRecordManager(); + $event = 'on'.$type; + $manager->{$event}($param); + } +} + +/** + * Command statement types. + * + * @author Wei Zhuo + * @version $Id$ + * @package System.Data.ActiveRecord + * @since 3.1 + */ +class TActiveRecordStatementType +{ + const Insert='Insert'; + const Update='Update'; + const Delete='Delete'; + const Select='Select'; +} + +/** + * Active Record command event parameter. + * + * @author Wei Zhuo + * @version $Id$ + * @package System.Data.ActiveRecord + * @since 3.1 + */ +class TActiveRecordGatewayEventParameter extends TActiveRecordEventParameter +{ + private $_type; + private $_command; + private $_record; + private $_data; + + /** + * New gateway command event parameter. + */ + public function __construct($type,$command,$record=null,$data=null) + { + $this->_type=$type; + $this->_command=$command; + $this->_data=$data; + $this->_record=$record; + } + + /** + * @return string TActiveRecordStateType + */ + public function getType() + { + return $this->_type; + } + + /** + * @return TDbCommand command to be executed. + */ + public function getCommand() + { + return $this->_command; + } + + /** + * @return TActiveRecord active record. + */ + public function getRecord() + { + return $this->_record; + } + + /** + * @return mixed command data. + */ + public function getData() + { + return $this->_data; + } +} + +?> \ No newline at end of file diff --git a/framework/Data/ActiveRecord/TActiveRecordManager.php b/framework/Data/ActiveRecord/TActiveRecordManager.php new file mode 100644 index 00000000..cff02988 --- /dev/null +++ b/framework/Data/ActiveRecord/TActiveRecordManager.php @@ -0,0 +1,213 @@ + + * @version $Id$ + * @package System.Data.ActiveRecord + */ + +Prado::using('System.Data.TDbConnection'); +Prado::using('System.Data.ActiveRecord.Exceptions.TActiveRecordException'); +Prado::using('System.Data.ActiveRecord.TActiveRecordGateway'); +Prado::using('System.Data.ActiveRecord.TActiveRecordStateRegistry'); + +/** + * TActiveRecordManager provides the default DB connection, default object state + * registry, default active record gateway, and table meta data inspector. + * + * You can provide a different registry by overriding the createObjectStateRegistry() method. + * Similarly, override createRecordGateway() for default gateway and override + * createMetaDataInspector() for meta data inspector. + * + * The default connection can be set as follows: + * + * TActiveRecordManager::getInstance()->setDbConnection($conn); + * + * All new active record created after setting the + * {@link DbConnection setDbConnection()} will use that connection. + * + * The {@link OnInsert onInsert()}, {@link OnUpdate onUpdate()}, + * {@link OnDelete onDelete()} and {@link onSelect onSelect()} events are raised + * before their respective command are executed. + * + * @author Wei Zhuo + * @version $Id$ + * @package System.Data.ActiveRecord + * @since 3.1 + */ +class TActiveRecordManager extends TComponent +{ + private $_objectRegistry; + private $_gateway; + private $_meta=array(); + private $_connection; + + /** + * @param TDbConnection default database connection + */ + public function setDbConnection($conn) + { + $this->_connection=$conn; + } + + /** + * @return TDbConnection default database connection + */ + public function getDbConnection() + { + return $this->_connection; + } + + /** + * @return TActiveRecordManager static instance of record manager. + */ + public static function getInstance() + { + static $instance; + if($instance===null) + $instance = new self; + return $instance; + } + + /** + * @return TActiveRecordStateRegistry record object registry. + */ + public function getObjectStateRegistry() + { + if(is_null($this->_objectRegistry)) + $this->_objectRegistry = $this->createObjectStateRegistry(); + return $this->_objectRegistry; + } + + /** + * @return TActiveRecordStateRegistry default object registry. + */ + protected function createObjectStateRegistry() + { + return new TActiveRecordStateRegistry(); + } + + /** + * @return TActiveRecordGateway record gateway. + */ + public function getRecordGateway() + { + if(is_null($this->_gateway)) + $this->_gateway = $this->createRecordGateway(); + return $this->_gateway; + } + + /** + * @return TActiveRecordGateway default record gateway. + */ + protected function createRecordGateway() + { + return new TActiveRecordGateway($this); + } + + /** + * Get table meta data for particular database and table. + * @param TDbConnection database connection. + * @return TDbMetaDataInspector table meta inspector + */ + public function getTableInspector(TDbConnection $conn) + { + $database = $conn->getConnectionString(); + if(!isset($this->_meta[$database])) + $this->_meta[$database] = $this->createMetaDataInspector($conn); + return $this->_meta[$database]; + } + + /** + * Create an instance of a database meta inspector corresponding to the + * given database vendor specified by the $driver parameter. + * @param TDbConnection database connection + * @return TDbMetaDataInspector table meta inspector + */ + protected function createMetaDataInspector($conn) + { + $conn->setActive(true); //must be connected before retrieving driver name! + $driver = $conn->getDriverName(); + switch(strtolower($driver)) + { + case 'pgsql': + Prado::using('System.Data.ActiveRecord.Vendor.TPgsqlMetaDataInspector'); + return new TPgsqlMetaDataInspector($conn); + case 'mysqli': + case 'mysql': + Prado::using('System.Data.ActiveRecord.Vendor.TMysqlMetaDataInspector'); + return new TMysqlMetaDataInspector($conn); + case 'sqlite': //sqlite 3 + case 'sqlite2': //sqlite 2 + Prado::using('System.Data.ActiveRecord.Vendor.TSqliteMetaDataInspector'); + return new TSqliteMetaDataInspector($conn); + default: + throw new TActiveRecordConfigurationException( + 'ar_invalid_database_driver',$driver); + } + } + + /** + * This method is invoked before the object is inserted into the database. + * The method raises 'OnInsert' event. + * If you override this method, be sure to call the parent implementation + * so that the event handlers can be invoked. + * @param TActiveRecordEventParameter event parameter to be passed to the event handlers + */ + public function onInsert($param) + { + $this->raiseEvent('OnInsert', $this, $param); + } + + /** + * This method is invoked before the object is deleted from the database. + * The method raises 'OnDelete' event. + * If you override this method, be sure to call the parent implementation + * so that the event handlers can be invoked. + * @param TActiveRecordEventParameter event parameter to be passed to the event handlers + */ + public function onDelete($param) + { + $this->raiseEvent('OnDelete', $this, $param); + } + + /** + * This method is invoked before the object data is updated in the database. + * The method raises 'OnUpdate' event. + * If you override this method, be sure to call the parent implementation + * so that the event handlers can be invoked. + * @param TActiveRecordEventParameter event parameter to be passed to the event handlers + */ + public function onUpdate($param) + { + $this->raiseEvent('OnUpdate', $this, $param); + } + + /** + * This method is invoked before any select query is executed on the database. + * The method raises 'OnSelect' event. + * If you override this method, be sure to call the parent implementation + * so that the event handlers can be invoked. + * @param TActiveRecordEventParameter event parameter to be passed to the event handlers + */ + public function onSelect($param) + { + $this->raiseEvent('OnSelect', $this, $param); + } +} + +/** + * TActiveRecordEventParameter class. + * + * @author Wei Zhuo + * @version $Id$ + * @package System.Data.ActiveRecord + * @since 3.1 + */ +class TActiveRecordEventParameter extends TEventParameter +{ + +} + +?> \ No newline at end of file diff --git a/framework/Data/ActiveRecord/TActiveRecordStateRegistry.php b/framework/Data/ActiveRecord/TActiveRecordStateRegistry.php new file mode 100644 index 00000000..4493d4cb --- /dev/null +++ b/framework/Data/ActiveRecord/TActiveRecordStateRegistry.php @@ -0,0 +1,262 @@ + + * @version $Id$ + * @package System.Data.ActiveRecord + */ + +/** + * Active record Unit of Work class and Identity Map. + * + * Maintains a list of objects affected by a business transaction and + * coordinates the writing out of changes and the resolution of concurrency problems. + * + * This registry keeps track of everything you do during a business transaction + * that can affect the database. When you're done, it figures out everything that + * needs to be done to alter the database as a result of your work. + * + * The object can only be in one of the four states: "new", "clean", "dirty" or "removed". + * A "new" object is one that is created not by loading the record from database. + * A "clean" object is one that is created by loading the record from datase. + * A "dirty" object is one that is marked as dirty or a "clean" object that has + * its internal state altered (done by using == object comparision). + * A "removed" object is one that is marked for deletion. + * + * See the "Active Record Object States.png" in the docs directory for state + * transition diagram. + * + * @author Wei Zhuo + * @version $Id$ + * @package System.Data.ActiveRecord + * @since 3.1 + */ +class TActiveRecordStateRegistry +{ + private $_cleanObjects=array(); + private $_removedObjects; + private $_cachedObjects=array(); + /** + * Initialize the registry. + */ + public function __construct() + { + $this->_removedObjects = new TList; + } + + /** + * Get the cached object for given type and row data. The cached object + * must also be clean. + * @param mixed row data fetched + * @return TActiveRecord cached object if found, null otherwise. + */ + public function getCachedInstance($data) + { + $key = $this->getObjectDataKey($data); + if(isset($this->_cachedObjects[$key])) + { + $obj = $this->_cachedObjects[$key]; + if($this->getIsCleanObject($obj)) + return $obj; + } + } + + /** + * Cache the object that corresponding to the fetched row data. + * @param mixed row data fetched. + * @param TActiveRecord object to be cached. + * @return TActiveRecord cached object. + */ + public function addCachedInstance($data,$obj) + { + $key = $this->getObjectDataKey($data); + $this->registerClean($obj); + $this->_cachedObjects[$key]=$obj; + return $obj; + } + + /** + * @return string hash of the data. + */ + protected function getObjectDataKey($data) + { + return sprintf('%x',crc32(serialize($data))); + } + + /** + * Ensure that object is not null. + */ + protected function assertNotNull($obj) + { + if(is_null($obj)) + throw new TActiveRecordException('ar_object_must_not_be_null'); + } + + /** + * Register the object for deletion, when the object invokes its delete() method + * the corresponding row in the database is deleted. + * @param TActiveRecord existing active record. + * @throws TActiveRecordException if object is null. + */ + public function registerRemoved($obj) + { + $this->assertNotNull($obj); + $found=false; + foreach($this->_cleanObjects as $i=>$cache) + { + if($cache[0]===$obj) + { + unset($this->_cleanObjects[$i]); + $found=true; + } + } + if(!$found) + throw new TActiveRecordException('ar_object_must_be_retrieved_before_delete'); + if(!$this->_removedObjects->contains($obj)) + $this->_removedObjects->add($obj); + } + + /** + * Register a clean object attached to a specific data that was used to + * populate the object. This acts as an object cache. + * @param TActiveRecord new clean object. + */ + public function registerClean($obj) + { + $this->removeCleanOrDirty($obj); + if($this->getIsRemovedObject($obj)) + throw new TActiveRecordException('ar_object_marked_for_removal'); + $this->_cleanObjects[] = array($obj, clone($obj)); + } + + /** + * Remove the object from dirty state. + * @param TActiveRecord object to remove. + */ + protected function removeDirty($obj) + { + $this->assertNotNull($obj); + foreach($this->_cleanObjects as $i=>$cache) + if($cache[0]===$obj && $obj != $cache[1]) + unset($this->_cleanObjects[$i]); + } + + /** + * Remove object from clean state. + * @param TActiveRecord object to remove. + */ + protected function removeClean($obj) + { + $this->assertNotNull($obj); + foreach($this->_cleanObjects as $i=>$cache) + if($cache[0]===$obj && $obj == $cache[1]) + unset($this->_cleanObjects[$i]); + } + + /** + * Remove object from dirty and clean state. + * @param TActiveRecord object to remove. + */ + protected function removeCleanOrDirty($obj) + { + $this->assertNotNull($obj); + foreach($this->_cleanObjects as $i=>$cache) + if($cache[0]===$obj) + unset($this->_cleanObjects[$i]); + } + + /** + * Remove object from removed state. + * @param TActiveRecord object to remove. + */ + protected function removeRemovedObject($obj) + { + $this->_removedObjects->remove($obj); + } + + /** + * Test whether an object is dirty or has been modified. + * @param TActiveRecord object to test. + * @return boolean true if the object is dirty, false otherwise. + */ + public function getIsDirtyObject($obj) + { + foreach($this->_cleanObjects as $cache) + if($cache[0] === $obj) + return $obj != $cache[1]; + return false; + } + + /** + * Test whether an object is in the clean state. + * @param TActiveRecord object to test. + * @return boolean true if object is clean, false otherwise. + */ + public function getIsCleanObject($obj) + { + foreach($this->_cleanObjects as $cache) + if($cache[0] === $obj) + return $obj == $cache[1]; + return false; + } + + /** + * Test whether an object is a new instance. + * @param TActiveRecord object to test. + * @return boolean true if object is newly created, false otherwise. + */ + public function getIsNewObject($obj) + { + if($this->getIsRemovedObject($obj)) return false; + foreach($this->_cleanObjects as $cache) + if($cache[0] === $obj) + return false; + return true; + } + + /** + * Test whether an object is marked for deletion. + * @param TActiveRecord object to test. + * @return boolean true if object is marked for deletion, false otherwise. + */ + public function getIsRemovedObject($obj) + { + return $this->_removedObjects->contains($obj); + } + + /** + * Commit the object to database: + * * a new record is inserted if the object is new, object becomes clean. + * * the record is updated if the object is dirty, object becomes clean. + * * the record is deleted if the object is marked for removal. + * + * @param TActiveRecord record object. + * @param TActiveRecordGateway database gateway + * @return boolean true if commit was successful, false otherwise. + */ + public function commit($record,$gateway) + { + $rowsAffected=0; + + if($this->getIsRemovedObject($record)) + { + $rowsAffected = $gateway->delete($record); + if($rowsAffected===1) + $this->removeRemovedObject($record); + } + else + { + if($this->getIsDirtyObject($record)) + $rowsAffected = $gateway->update($record); + else if($this->getIsNewObject($record)) + $rowsAffected = $gateway->insert($record); + + if($rowsAffected===1) + $this->registerClean($record); + } + return $rowsAffected===1; + } +} + +?> \ No newline at end of file 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 diff --git a/framework/Data/TDbCommand.php b/framework/Data/TDbCommand.php index 91b165fa..82e46a25 100644 --- a/framework/Data/TDbCommand.php +++ b/framework/Data/TDbCommand.php @@ -155,9 +155,9 @@ class TDbCommand extends TComponent { $this->prepare(); if($dataType===null) - $this->_statement->bindParam($name,$value); + $this->_statement->bindValue($name,$value); else - $this->_statement->bindParam($name,$value,$dataType); + $this->_statement->bindValue($name,$value,$dataType); } /** @@ -177,7 +177,11 @@ class TDbCommand extends TComponent return $this->_statement->rowCount(); } else - return $this->getConnection()->getPdoInstance()->exec($this->getText()); + { + $int = $this->getConnection()->getPdoInstance()->exec($this->getText()); + var_dump($int); + return $int; + } } catch(Exception $e) { -- cgit v1.2.3