From 38c18b2d740f61e342f00bc33791f0f3c014e126 Mon Sep 17 00:00:00 2001 From: wei <> Date: Sun, 22 Apr 2007 00:28:36 +0000 Subject: Update to Active Record to use Mysql 4. Add TActiveRecordRelation --- framework/Data/ActiveRecord/TActiveRecord.php | 156 +++++++++++++-------- .../Data/ActiveRecord/TActiveRecordGateway.php | 5 + .../Data/ActiveRecord/TActiveRecordRelation.php | 124 ++++++++++++++++ framework/Data/Common/Mssql/TMssqlMetaData.php | 2 +- framework/Data/Common/Mysql/TMysqlMetaData.php | 71 +++++++++- framework/Data/Common/Pgsql/TPgsqlMetaData.php | 2 +- framework/Data/Common/Sqlite/TSqliteMetaData.php | 2 +- framework/Data/DataGateway/TDataGatewayCommand.php | 26 +++- 8 files changed, 317 insertions(+), 71 deletions(-) create mode 100644 framework/Data/ActiveRecord/TActiveRecordRelation.php (limited to 'framework/Data') diff --git a/framework/Data/ActiveRecord/TActiveRecord.php b/framework/Data/ActiveRecord/TActiveRecord.php index 1ea06a60..509d23f6 100644 --- a/framework/Data/ActiveRecord/TActiveRecord.php +++ b/framework/Data/ActiveRecord/TActiveRecord.php @@ -12,14 +12,15 @@ Prado::using('System.Data.ActiveRecord.TActiveRecordManager'); Prado::using('System.Data.ActiveRecord.TActiveRecordCriteria'); +Prado::using('System.Data.ActiveRecord.TActiveRecordRelation'); /** * 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. - * - * Active record objects are stateful, this is main difference between the + * or view, encapsulates the database access, and adds domain logic on that data. + * + * Active record objects are stateful, this is main difference between the * TActiveRecord implementation and the TTableGateway implementation. * * The essence of an Active Record is an object model of the @@ -68,6 +69,10 @@ Prado::using('System.Data.ActiveRecord.TActiveRecordCriteria'); */ abstract class TActiveRecord extends TComponent { + const HAS_MANY='HAS_MANY'; + const BELONGS_TO='BELONGS_TO'; + const HAS_ONE='HAS_ONE'; + /** * @var boolean true if this class is read only. */ @@ -143,10 +148,10 @@ abstract class TActiveRecord extends TComponent } /** - * Returns the instance of a active record finder for a particular class. - * The finder objects are static instances for each ActiveRecord class. - * This means that event handlers bound to these finder instances are class wide. - * Create a new instance of the ActiveRecord class if you wish to bound the + * Returns the instance of a active record finder for a particular class. + * The finder objects are static instances for each ActiveRecord class. + * This means that event handlers bound to these finder instances are class wide. + * Create a new instance of the ActiveRecord class if you wish to bound the * event handlers to object instance. * @param string active record class name. * @return TActiveRecord active record finder instance. @@ -175,14 +180,14 @@ abstract class TActiveRecord extends TComponent public function getRecordManager() { return TActiveRecordManager::getInstance(); - } - - /** - * @return TActiveRecordGateway record table gateway. - */ - public function getRecordGateway() - { - return $this->getRecordManager()->getRecordGateway(); + } + + /** + * @return TActiveRecordGateway record table gateway. + */ + public function getRecordGateway() + { + return $this->getRecordManager()->getRecordGateway(); } /** @@ -281,16 +286,16 @@ abstract class TActiveRecord extends TComponent //try the cache (the cache object must be clean) if(!is_null($obj = $registry->getCachedInstance($data))) - return $obj; - - $gateway = $this->getRecordManager()->getRecordGateway(); + return $obj; + + $gateway = $this->getRecordManager()->getRecordGateway(); //create and populate the object $obj = Prado::createComponent($type); - $tableInfo = $gateway->getRecordTableInfo($obj); - foreach($tableInfo->getColumns()->getKeys() as $name) - { - if(isset($data[$name])) + $tableInfo = $gateway->getRecordTableInfo($obj); + foreach($tableInfo->getColumns()->getKeys() as $name) + { + if(isset($data[$name])) $obj->{$name} = $data[$name]; } @@ -419,6 +424,17 @@ abstract class TActiveRecord extends TComponent return $this->collectObjects($result); } + /** + * + * + */ + public function findAllByIndex($criteria,$fields,$values) + { + $gateway = $this->getRecordManager()->getRecordGateway(); + $result = $gateway->findRecordsByIndex($this,$criteria,$fields,$values); + return $this->collectObjects($result); + } + /** * Find the number of records. * @param string|TActiveRecordCriteria SQL condition or criteria object. @@ -434,6 +450,24 @@ abstract class TActiveRecord extends TComponent return $gateway->countRecords($this,$criteria); } + /** + * + * @return TActiveRecordRelation + */ + protected function getRecordRelation($property,$args) + { + $criteria = $this->getCriteria(count($args)>0 ? $args[0] : null, array_slice($args,1)); + $relation = $this->{$property}; + switch($relation[0]) + { + case self::HAS_MANY: + $finder = self::finder($relation[1]); + return new TActiveRecordHasMany($this, $criteria, $finder, $property); + default: + throw new TException('Not done yet'); + } + } + /** * Dynamic find method using parts of method name as search criteria. * Method name starting with "findBy" only returns 1 record. @@ -466,7 +500,12 @@ abstract class TActiveRecord extends TComponent public function __call($method,$args) { $delete =false; - if($findOne = substr(strtolower($method),0,6)==='findby') + if(substr(strtolower($method),0,4)==='with') + { + $property= $method[4]==='_' ? substr($method,5) : substr($method,4); + return $this->getRecordRelation($property, $args); + } + else 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); @@ -492,7 +531,7 @@ abstract class TActiveRecord extends TComponent * @param array additional parameters obtained from function_get_args(). * @return TSqlCriteria criteria object. */ - protected function getCriteria($criteria, $parameters, $args) + protected function getCriteria($criteria, $parameters, $args=array()) { if(is_string($criteria)) { @@ -502,40 +541,41 @@ abstract class TActiveRecord extends TComponent else if($criteria instanceof TSqlCriteria) return $criteria; else - throw new TActiveRecordException('ar_invalid_criteria'); + return new TActiveRecordCriteria(); + //throw new TActiveRecordException('ar_invalid_criteria'); + } + + /** + * Raised when a command is prepared and parameter binding is completed. + * The parameter object is TDataGatewayEventParameter of which the + * {@link TDataGatewayEventParameter::getCommand Command} property can be + * inspected to obtain the sql query to be executed. + * + * Note well that the finder objects obtained from ActiveRecord::finder() + * method are static objects. This means that the event handlers are + * bound to a static finder object and not to each distinct active record object. + * @param TDataGatewayEventParameter + */ + public function onCreateCommand($param) + { + $this->raiseEvent('OnCreateCommand', $this, $param); + } + + /** + * Raised when a command is executed and the result from the database was returned. + * The parameter object is TDataGatewayResultEventParameter of which the + * {@link TDataGatewayEventParameter::getResult Result} property contains + * the data return from the database. The data returned can be changed + * by setting the {@link TDataGatewayEventParameter::setResult Result} property. + * + * Note well that the finder objects obtained from ActiveRecord::finder() + * method are static objects. This means that the event handlers are + * bound to a static finder object and not to each distinct active record object. + * @param TDataGatewayResultEventParameter + */ + public function onExecuteCommand($param) + { + $this->raiseEvent('OnExecuteCommand', $this, $param); } - - /** - * Raised when a command is prepared and parameter binding is completed. - * The parameter object is TDataGatewayEventParameter of which the - * {@link TDataGatewayEventParameter::getCommand Command} property can be - * inspected to obtain the sql query to be executed. - * - * Note well that the finder objects obtained from ActiveRecord::finder() - * method are static objects. This means that the event handlers are - * bound to a static finder object and not to each distinct active record object. - * @param TDataGatewayEventParameter - */ - public function onCreateCommand($param) - { - $this->raiseEvent('OnCreateCommand', $this, $param); - } - - /** - * Raised when a command is executed and the result from the database was returned. - * The parameter object is TDataGatewayResultEventParameter of which the - * {@link TDataGatewayEventParameter::getResult Result} property contains - * the data return from the database. The data returned can be changed - * by setting the {@link TDataGatewayEventParameter::setResult Result} property. - * - * Note well that the finder objects obtained from ActiveRecord::finder() - * method are static objects. This means that the event handlers are - * bound to a static finder object and not to each distinct active record object. - * @param TDataGatewayResultEventParameter - */ - public function onExecuteCommand($param) - { - $this->raiseEvent('OnExecuteCommand', $this, $param); - } } ?> diff --git a/framework/Data/ActiveRecord/TActiveRecordGateway.php b/framework/Data/ActiveRecord/TActiveRecordGateway.php index c3239c5c..40948999 100644 --- a/framework/Data/ActiveRecord/TActiveRecordGateway.php +++ b/framework/Data/ActiveRecord/TActiveRecordGateway.php @@ -221,6 +221,11 @@ class TActiveRecordGateway extends TComponent return $this->getCommand($record)->findBySql($criteria); } + public function findRecordsByIndex(TActiveRecord $record, $criteria, $fields, $values) + { + return $this->getCommand($record)->findAllByIndex($criteria,$fields,$values); + } + /** * Returns the number of records that match the given criteria. * @param TActiveRecord active record finder instance. diff --git a/framework/Data/ActiveRecord/TActiveRecordRelation.php b/framework/Data/ActiveRecord/TActiveRecordRelation.php new file mode 100644 index 00000000..6fae5499 --- /dev/null +++ b/framework/Data/ActiveRecord/TActiveRecordRelation.php @@ -0,0 +1,124 @@ +source=$source; + $this->criteria=$criteria; + $this->dependent=$dependent; + $this->property=$property; + } + + public function __call($method,$args) + { + $results = call_user_func_array(array($this->source,$method),$args); + $fkResults = $this->getForeignIndexResults($results); + $this->matchResultCollection($results,$fkResults); + return $results; + } + protected function getForeignIndexResults($results) + { + if(!is_array($results)) + $results = array($results); + $fkeys = $this->getForeignKeys(); + $values = $this->getForeignKeyIndices($results, $fkeys); + $fields = array_keys($fkeys); + return $this->dependent->findAllByIndex($this->criteria, $fields, $values); + } + + protected function matchResultCollection(&$results,&$fkResults) + { + $keys = $this->getForeignKeys(); + $collections=array(); + foreach($fkResults as $fkObject) + { + $objId=array(); + foreach($keys as $fkName=>$name) + $objId[] = $fkObject->{$fkName}; + $collections[$this->getObjectId($objId)][]=$fkObject; + } + if(is_array($results)) + { + for($i=0,$k=count($results);$i<$k;$i++) + { + $this->setFkObjectProperty($results[$i], $collections); + } + } + else + { + $this->setFkObjectProperty($results, $collections); + } + } + + function setFKObjectProperty($source, &$collections) + { + $objId=array(); + foreach($this->getForeignKeys() as $fkName=>$name) + $objId[] = $source->{$name}; + $key = $this->getObjectId($objId); + $source->{$this->property} = isset($collections[$key]) ? $collections[$key] : array(); + } + + protected function getObjectId($objId) + { + return sprintf('%x',crc32(serialize($objId))); + } + + protected function getForeignKeys() + { + if($this->fkeys===null) + { + $gateway = $this->dependent->getRecordGateway(); + $depTableInfo = $gateway->getRecordTableInfo($this->dependent); + $fks = $depTableInfo->getForeignKeys(); + $sourceTable = $gateway->getRecordTableInfo($this->source)->getTableName(); + foreach($fks as $relation) + { + if($relation['table']===$sourceTable) + { + $this->fkeys=$relation['keys']; + break; + } + } + if(!$this->fkeys) + throw new TActiveRecordException('no fk defined for '.$depTableInfo->getTableFullName()); + } + return $this->fkeys; + } + + protected function getForeignKeyIndices($results,$keys) + { + $values = array(); + foreach($results as $result) + { + $value = array(); + foreach($keys as $name) + $value[] = $result->{$name}; + $values[] = $value; + } + return $values; + } +} + +class TActiveRecordHasOne extends TActiveRecordRelation +{ +} + +class TActiveRecordBelongsTo extends TActiveRecordRelation +{ + +} + +?> \ No newline at end of file diff --git a/framework/Data/Common/Mssql/TMssqlMetaData.php b/framework/Data/Common/Mssql/TMssqlMetaData.php index 5d14aac1..72d297f7 100644 --- a/framework/Data/Common/Mssql/TMssqlMetaData.php +++ b/framework/Data/Common/Mssql/TMssqlMetaData.php @@ -202,7 +202,7 @@ EOD; foreach($command->query() as $info) { $fkeys[$info['FK_CONSTRAINT_NAME']]['keys'][$info['FK_COLUMN_NAME']] = $info['UQ_COLUMN_NAME']; - $fkeys[$info['FK_CONSTRAINT_NAME']]['table'] = "{$catalogSchema}.[{$info['UQ_TABLE_NAME']}]"; + $fkeys[$info['FK_CONSTRAINT_NAME']]['table'] = $info['UQ_TABLE_NAME']; } return count($fkeys) > 0 ? array_values($fkeys) : $fkeys; } diff --git a/framework/Data/Common/Mysql/TMysqlMetaData.php b/framework/Data/Common/Mysql/TMysqlMetaData.php index ca1efaaa..ebe851b6 100644 --- a/framework/Data/Common/Mysql/TMysqlMetaData.php +++ b/framework/Data/Common/Mysql/TMysqlMetaData.php @@ -7,6 +7,8 @@ Prado::using('System.Data.Common.Mysql.TMysqlTableInfo'); class TMysqlMetaData extends TDbMetaData { + private $_serverVersion=0; + /** * Get the column definitions for given table. * @param string table name. @@ -31,6 +33,21 @@ class TMysqlMetaData extends TDbMetaData return $tableInfo; } + /** + * @return float server version. + */ + protected function getServerVersion() + { + if(!$this->_serverVersion) + { + $version = $this->getDbConnection()->getAttribute(PDO::ATTR_SERVER_VERSION); + $digits=array(); + preg_match('/(\d+)\.(\d+)\.(\d+)/', $version, $digits); + $this->_serverVersion=floatval($digits[1].'.'.$digits[2].$digits[3]); + } + return $this->_serverVersion; + } + /** * @param TMysqlTableInfo table information. * @param array column information. @@ -42,7 +59,7 @@ class TMysqlMetaData extends TDbMetaData $info['ColumnName'] = "`$columnId`"; //quote the column names! $info['ColumnId'] = $columnId; $info['ColumnIndex'] = $col['index']; - if($col['Null']!=='NO') + if($col['Null']==='YES') $info['AllowNull'] = true; if(is_int(strpos(strtolower($col['Extra']), 'auto_increment'))) $info['AutoIncrement']=true; @@ -154,7 +171,7 @@ class TMysqlMetaData extends TDbMetaData * For MySQL version 5.0.1 or later we can use SHOW FULL TABLES * http://dev.mysql.com/doc/refman/5.0/en/show-tables.html * - * For MySQL version 5.0.0 or ealier, this always return false. + * For MySQL version 5.0.1 or ealier, this always return false. * @param string database name, null to use default connection database. * @param string table or view name. * @return boolean true if is view, false otherwise. @@ -162,7 +179,7 @@ class TMysqlMetaData extends TDbMetaData */ protected function getIsView($schemaName,$tableName) { - if(intval($this->getDbConnection()->getAttribute(PDO::ATTR_SERVER_VERSION))<5) + if($this->getServerVersion()<5.01) return false; if($schemaName!==null) $sql = "SHOW FULL TABLES FROM `{$schemaName}` LIKE :table"; @@ -194,12 +211,15 @@ class TMysqlMetaData extends TDbMetaData $sql = "SHOW INDEX FROM {$table}"; $command = $this->getDbConnection()->createCommand($sql); $primary = array(); - $foreign = $this->getForeignConstraints($schemaName,$tableName); foreach($command->query() as $row) { if($row['Key_name']==='PRIMARY') $primary[] = $row['Column_name']; } + if($this->getServerVersion() > 5) + $foreign = $this->getForeignConstraints($schemaName,$tableName); + else + $foreign = $this->findForeignConstraints($schemaName,$tableName); return array($primary,$foreign); } @@ -234,11 +254,52 @@ EOD; foreach($command->query() as $col) { $fkeys[$col['con']]['keys'][$col['col']] = $col['fkcol']; - $fkeys[$col['con']]['table'] = "`{$col['fkschema']}`.`{$col['fktable']}`"; + $fkeys[$col['con']]['table'] = $col['fktable']; } return count($fkeys) > 0 ? array_values($fkeys) : $fkeys; } + /** + * @param string database name + * @param string table name + * @return string SQL command to create the table. + */ + protected function getShowCreateTable($schemaName, $tableName) + { + if($schemaName!==null) + $sql = "SHOW CREATE TABLE `{$schemaName}`.`{$tableName}`"; + else + $sql = "SHOW CREATE TABLE `{$tableName}`"; + $command = $this->getDbConnection()->createCommand($sql); + $result = $command->queryRow(); + return $result['Create Table']; + } + + /** + * Extract foreign key constraints by extracting the contraints from SHOW CREATE TABLE result. + * @param string database name + * @param string table name + * @return array foreign relationship table name and keys. + */ + protected function findForeignConstraints($schemaName, $tableName) + { + $sql = $this->getShowCreateTable($schemaName, $tableName); + $matches =array(); + $regexp = '/FOREIGN KEY\s+\(([^\)]+)\)\s+REFERENCES\s+`?([^`]+)`?\s\(([^\)]+)\)/mi'; + preg_match_all($regexp,$sql,$matches,PREG_SET_ORDER); + $foreign = array(); + foreach($matches as $match) + { + $fields = array_map('trim',explode(',',str_replace('`','',$match[1]))); + $fk_fields = array_map('trim',explode(',',str_replace('`','',$match[3]))); + $keys=array(); + foreach($fields as $k=>$v) + $keys[$v] = $fk_fields[$k]; + $foreign[] = array('keys' => $keys, 'table' => trim($match[2])); + } + return $foreign; + } + /** * @param string column name. * @param TPgsqlTableInfo table information. diff --git a/framework/Data/Common/Pgsql/TPgsqlMetaData.php b/framework/Data/Common/Pgsql/TPgsqlMetaData.php index bb03b6cd..c68bbaa0 100644 --- a/framework/Data/Common/Pgsql/TPgsqlMetaData.php +++ b/framework/Data/Common/Pgsql/TPgsqlMetaData.php @@ -306,7 +306,7 @@ EOD; $fkeys = array(); foreach(preg_split('/,\s+/', $matches[3]) as $i => $fkey) $fkeys[$keys[$i]] = $fkey; - return array('table' => $matches[2], 'keys' => $fkeys); + return array('table' => str_replace('"','',$matches[2]), 'keys' => $fkeys); } } diff --git a/framework/Data/Common/Sqlite/TSqliteMetaData.php b/framework/Data/Common/Sqlite/TSqliteMetaData.php index 6fcf7b7f..ef33f968 100644 --- a/framework/Data/Common/Sqlite/TSqliteMetaData.php +++ b/framework/Data/Common/Sqlite/TSqliteMetaData.php @@ -49,7 +49,7 @@ class TSqliteMetaData extends TDbMetaData if($column->getIsPrimaryKey()) $primary[] = $col['name']; } - $info['TableName'] = $table; + $info['TableName'] = $tableName; if($this->getIsView($tableName)) $info['IsView'] = true; if(count($columns)===0) diff --git a/framework/Data/DataGateway/TDataGatewayCommand.php b/framework/Data/DataGateway/TDataGatewayCommand.php index dbabd2b7..43a57aa7 100644 --- a/framework/Data/DataGateway/TDataGatewayCommand.php +++ b/framework/Data/DataGateway/TDataGatewayCommand.php @@ -177,6 +177,18 @@ class TDataGatewayCommand extends TComponent return $this->onExecuteCommand($command,$command->query()); } + public function findAllByIndex($criteria,$fields,$values) + { + $index = $this->getIndexKeyCondition($fields,$values); + if(strlen($where = $criteria->getCondition())>0) + $criteria->setCondition("({$index}) AND ({$where})"); + else + $criteria->setCondition($index); + $command = $this->getFindCommand($criteria); + $this->onCreateCommand($command, $criteria); + return $this->onExecuteCommand($command,$command->query()); + } + /** * @param array multiple primary key values or composite value arrays * @return integer number of rows affected. @@ -190,6 +202,14 @@ class TDataGatewayCommand extends TComponent return $this->onExecuteCommand($command,$command->execute()); } + protected function getIndexKeyCondition($fields,$values) + { + $columns = array(); + foreach($fields as $field) + $columns[] = $this->getTableInfo()->getColumn($field)->getColumnName(); + return '('.implode(', ',$columns).') IN '.$this->quoteTuple($values); + } + /** * Construct a "pk IN ('key1', 'key2', ...)" criteria. * @param array values for IN predicate @@ -216,11 +236,7 @@ class TDataGatewayCommand extends TComponent throw new TDbException('dbtablegateway_pk_value_count_mismatch', $this->getTableInfo()->getTableFullName()); } - - $columns = array(); - foreach($primary as $key) - $columns[] = $this->getTableInfo()->getColumn($key)->getColumnName(); - return '('.implode(', ',$columns).') IN '.$this->quoteTuple($values); + return $this->getIndexKeyCondition($primary, $values); } /** -- cgit v1.2.3