From d5eb713888715e8f18d2ccf508a8eb0b1a483ad1 Mon Sep 17 00:00:00 2001 From: wei <> Date: Tue, 24 Apr 2007 06:14:56 +0000 Subject: add active record Relations --- .gitattributes | 9 +- build.xml | 4 +- .../Data/ActiveRecord/Exceptions/messages.txt | 2 + .../Relations/TActiveRecordBelongsTo.php | 43 ++++++ .../Relations/TActiveRecordHasMany.php | 96 ++++++++++++ .../Relations/TActiveRecordHasManyAssociation.php | 149 ++++++++++++++++++ .../ActiveRecord/Relations/TActiveRecordHasOne.php | 42 +++++ .../Relations/TActiveRecordRelation.php | 172 +++++++++++++++++++++ .../Relations/TActiveRecordRelationContext.php | 104 +++++++++++++ framework/Data/ActiveRecord/TActiveRecord.php | 45 +++--- .../Data/ActiveRecord/TActiveRecordRelation.php | 124 --------------- framework/Data/Common/TDbCommandBuilder.php | 13 +- framework/Data/DataGateway/TDataGatewayCommand.php | 9 +- .../ActiveRecord/ForeignKeyTestCase.php | 144 +++++++++++------ tests/simple_unit/ActiveRecord/fk_tests.db | Bin 0 -> 9216 bytes tests/simple_unit/ActiveRecord/sqlite.sql | 46 ++++++ .../DbCommon/CommandBuilderMysqlTest.php | 2 +- tests/simple_unit/unit.php | 1 - 18 files changed, 797 insertions(+), 208 deletions(-) create mode 100644 framework/Data/ActiveRecord/Relations/TActiveRecordBelongsTo.php create mode 100644 framework/Data/ActiveRecord/Relations/TActiveRecordHasMany.php create mode 100644 framework/Data/ActiveRecord/Relations/TActiveRecordHasManyAssociation.php create mode 100644 framework/Data/ActiveRecord/Relations/TActiveRecordHasOne.php create mode 100644 framework/Data/ActiveRecord/Relations/TActiveRecordRelation.php create mode 100644 framework/Data/ActiveRecord/Relations/TActiveRecordRelationContext.php delete mode 100644 framework/Data/ActiveRecord/TActiveRecordRelation.php create mode 100644 tests/simple_unit/ActiveRecord/fk_tests.db create mode 100644 tests/simple_unit/ActiveRecord/sqlite.sql diff --git a/.gitattributes b/.gitattributes index e1cbbc69..eee67148 100644 --- a/.gitattributes +++ b/.gitattributes @@ -1604,6 +1604,12 @@ framework/Configuration/Provider/TProviderException.php -text framework/Configuration/TProtectedConfiguration.php -text framework/Data/ActiveRecord/Exceptions/TActiveRecordException.php -text framework/Data/ActiveRecord/Exceptions/messages.txt -text +framework/Data/ActiveRecord/Relations/TActiveRecordBelongsTo.php -text +framework/Data/ActiveRecord/Relations/TActiveRecordHasMany.php -text +framework/Data/ActiveRecord/Relations/TActiveRecordHasManyAssociation.php -text +framework/Data/ActiveRecord/Relations/TActiveRecordHasOne.php -text +framework/Data/ActiveRecord/Relations/TActiveRecordRelation.php -text +framework/Data/ActiveRecord/Relations/TActiveRecordRelationContext.php -text framework/Data/ActiveRecord/Scaffold/InputBuilder/TIbmScaffoldInput.php -text framework/Data/ActiveRecord/Scaffold/InputBuilder/TMssqlScaffoldInput.php -text framework/Data/ActiveRecord/Scaffold/InputBuilder/TMysqlScaffoldInput.php -text @@ -1626,7 +1632,6 @@ framework/Data/ActiveRecord/TActiveRecordConfig.php -text framework/Data/ActiveRecord/TActiveRecordCriteria.php -text framework/Data/ActiveRecord/TActiveRecordGateway.php -text framework/Data/ActiveRecord/TActiveRecordManager.php -text -framework/Data/ActiveRecord/TActiveRecordRelation.php -text framework/Data/ActiveRecord/TActiveRecordStateRegistry.php -text framework/Data/Common/IbmDb2/TIbmColumnMetaData.php -text framework/Data/Common/IbmDb2/TIbmMetaData.php -text @@ -2638,6 +2643,7 @@ tests/simple_unit/ActiveRecord/UserRecordTestCase.php -text tests/simple_unit/ActiveRecord/ViewRecordTestCase.php -text tests/simple_unit/ActiveRecord/ar_test.db -text tests/simple_unit/ActiveRecord/blog.db -text +tests/simple_unit/ActiveRecord/fk_tests.db -text tests/simple_unit/ActiveRecord/mysql4text.sql -text tests/simple_unit/ActiveRecord/records/Blogs.php -text tests/simple_unit/ActiveRecord/records/DepSections.php -text @@ -2645,6 +2651,7 @@ tests/simple_unit/ActiveRecord/records/DepartmentRecord.php -text tests/simple_unit/ActiveRecord/records/SimpleUser.php -text tests/simple_unit/ActiveRecord/records/SqliteUsers.php -text tests/simple_unit/ActiveRecord/records/UserRecord.php -text +tests/simple_unit/ActiveRecord/sqlite.sql -text tests/simple_unit/DbCommon/CommandBuilderMssqlTest.php -text tests/simple_unit/DbCommon/CommandBuilderMysqlTest.php -text tests/simple_unit/DbCommon/CommandBuilderPgsqlTest.php -text diff --git a/build.xml b/build.xml index 385bf272..978f751a 100644 --- a/build.xml +++ b/build.xml @@ -214,7 +214,9 @@ + files="TActiveRecord.php,TActiveRecordManager.php,Exceptions/TActiveRecordException.php,TActiveRecordCriteria.php,TActiveRecordGateway.php,TActiveRecordStateRegistry.php" /> + diff --git a/framework/Data/ActiveRecord/Exceptions/messages.txt b/framework/Data/ActiveRecord/Exceptions/messages.txt index b3d567e4..f9979e12 100644 --- a/framework/Data/ActiveRecord/Exceptions/messages.txt +++ b/framework/Data/ActiveRecord/Exceptions/messages.txt @@ -18,3 +18,5 @@ ar_mismatch_column_names = In dynamic __call() method '{0}', no matching col ar_invalid_table = Missing, invalid or no permission for table/view '{0}'. ar_invalid_finder_class_name = Class name for finder($className) method must not be 'TActiveRecord', you should override the finder() method in your record class or pass in a valid record class name. ar_invalid_criteria = Invalid criteria object, must be a string or instance of TSqlCriteria. +ar_relations_undefined = Unable to determine Active Record relationships because static array property {0}::${1} is not defined. +ar_undefined_relation_prop = Unable to find {1}::${2}['{0}'], Active Record relationship definition for property "{0}" not found in entries of {1}::${2}. \ No newline at end of file diff --git a/framework/Data/ActiveRecord/Relations/TActiveRecordBelongsTo.php b/framework/Data/ActiveRecord/Relations/TActiveRecordBelongsTo.php new file mode 100644 index 00000000..3bb1a74b --- /dev/null +++ b/framework/Data/ActiveRecord/Relations/TActiveRecordBelongsTo.php @@ -0,0 +1,43 @@ +getContext()->getForeignRecordFinder(); + $fkeys = $this->findForeignKeys($this->getSourceRecord(),$fkObject); + + $properties = array_keys($fkeys); + $fields = array_values($fkeys); + + $indexValues = $this->getIndexValues($properties, $results); + $fkObjects = $this->findForeignObjects($fields, $indexValues); + $this->populateResult($results,$properties,$fkObjects,$fields); + } + + /** + * Sets the foreign objects to the given property on the source object. + * @param TActiveRecord source object. + * @param array foreign objects. + */ + protected function setObjectProperty($source, $properties, &$collections) + { + $hash = $this->getObjectHash($source, $properties); + $prop = $this->getContext()->getProperty(); + if(isset($collections[$hash]) && count($collections[$hash]) > 0) + { + if(count($collections[$hash]) > 1) + throw new TActiveRecordException('ar_belongs_to_multiple_result'); + $source->{$prop} = $collections[$hash][0]; + } + } +} + +?> \ No newline at end of file diff --git a/framework/Data/ActiveRecord/Relations/TActiveRecordHasMany.php b/framework/Data/ActiveRecord/Relations/TActiveRecordHasMany.php new file mode 100644 index 00000000..27ffd194 --- /dev/null +++ b/framework/Data/ActiveRecord/Relations/TActiveRecordHasMany.php @@ -0,0 +1,96 @@ + + * @link http://www.pradosoft.com/ + * @copyright Copyright © 2005-2007 PradoSoft + * @license http://www.pradosoft.com/license/ + * @version $Id$ + * @package System.Data.ActiveRecord.Relations + */ + +/** + * Loads base active record relations class. + */ +Prado::using('System.Data.ActiveRecord.Relations.TActiveRecordRelation'); + +/** + * Implements TActiveRecord::HAS_MANY relationship between the source object having zero or + * more foreign objects. Consider the relationship between a Team and a Player. + * + * +------+ +--------+ + * | Team | 1 -----> * | Player | + * +------+ +--------+ + * + * Where one team may have 0 or more players and each player belongs to only + * one team. We may model Team-Player relationship as active record as follows. + * + * class TeamRecord extends TActiveRecord + * { + * const TABLE='team'; + * public $name; //primary key + * public $location; + * + * public $players=array(); //list of players + * + * protected static $RELATIONS=array( + * 'players' => array(self::HAS_MANY, 'PlayerRecord')); + * + * public static function finder($className=__CLASS__) + * { + * return parent::finder($className); + * } + * } + * class PlayerRecord extends TActiveRecord + * { + * const TABLE='player'; + * public $player_id; //primary key + * public $team_name; //foreign key player.team_name <-> team.name + * public $age; + * + * public static function finder($className=__CLASS__) + * { + * return parent::finder($className); + * } + * } + * + * The $RELATIONS static property of TeamRecord defines that the + * property $players has many PlayerRecords. + * + * The players list may be fetched as follows. + * + * $team = TeamRecord::finder()->with_players()->findAll(); + * + * The method with_xxx() (where xxx is the relationship property + * name, in this case, players) fetchs the corresponding PlayerRecords using + * a second query (not by using a join). The with_xxx() accepts the same + * arguments as other finder methods of TActiveRecord, e.g. with_player('age < ?', 35). + * + * @author Wei Zhuo + * @version $Id$ + * @package System.Data.ActiveRecord.Relations + * @since 3.1 + */ +class TActiveRecordHasMany extends TActiveRecordRelation +{ + /** + * Get the foreign key index values from the results and make calls to the + * database to find the corresponding foreign objects. + * @param array original results. + */ + protected function collectForeignObjects(&$results) + { + $fkObject = $this->getContext()->getForeignRecordFinder(); + $fkeys = $this->findForeignKeys($fkObject, $this->getSourceRecord()); + + $properties = array_values($fkeys); + $fields = array_keys($fkeys); + + $indexValues = $this->getIndexValues($properties, $results); + $fkObjects = $this->findForeignObjects($fields,$indexValues); + $this->populateResult($results,$properties,$fkObjects,$fields); + } +} + +?> \ No newline at end of file diff --git a/framework/Data/ActiveRecord/Relations/TActiveRecordHasManyAssociation.php b/framework/Data/ActiveRecord/Relations/TActiveRecordHasManyAssociation.php new file mode 100644 index 00000000..a86fdffd --- /dev/null +++ b/framework/Data/ActiveRecord/Relations/TActiveRecordHasManyAssociation.php @@ -0,0 +1,149 @@ +getAssociationTable(); + $sourceKeys = $this->findForeignKeys($association, $this->getSourceRecord()); + + $properties = array_values($sourceKeys); + + $indexValues = $this->getIndexValues($properties, $results); + + $fkObject = $this->getContext()->getForeignRecordFinder(); + $foreignKeys = $this->findForeignKeys($association, $fkObject); + + $this->fetchForeignObjects($results, $foreignKeys,$indexValues,$sourceKeys); + } + + protected function getAssociationTable() + { + if($this->_association===null) + { + $gateway = $this->getSourceRecord()->getRecordGateway(); + $conn = $this->getSourceRecord()->getDbConnection(); + $table = $this->getContext()->getAssociationTable(); + $this->_association = $gateway->getTableInfo($conn, $table); + } + return $this->_association; + } + + protected function getSourceTable() + { + if($this->_sourceTable===null) + { + $gateway = $this->getSourceRecord()->getRecordGateway(); + $this->_sourceTable = $gateway->getRecordTableInfo($this->getSourceRecord()); + } + return $this->_sourceTable; + } + + protected function getForeignTable() + { + if($this->_foreignTable===null) + { + $gateway = $this->getSourceRecord()->getRecordGateway(); + $fkObject = $this->getContext()->getForeignRecordFinder(); + $this->_foreignTable = $gateway->getRecordTableInfo($fkObject); + } + return $this->_foreignTable; + } + + protected function getCommandBuilder() + { + return $this->getSourceRecord()->getRecordGateway()->getCommand($this->getSourceRecord()); + } + + /** + * Fetches the foreign objects using TActiveRecord::findAllByIndex() + * @param array field names + * @param array foreign key index values. + */ + protected function fetchForeignObjects(&$results,$foreignKeys,$indexValues,$sourceKeys) + { + $criteria = $this->getContext()->getCriteria(); + $finder = $this->getContext()->getForeignRecordFinder(); + $command = $this->createCommand($criteria, $foreignKeys,$indexValues,$sourceKeys); + $srcProps = array_keys($sourceKeys); + $type = get_class($finder); + $collections=array(); + foreach($command->query() as $row) + { + $hash = $this->getObjectHash($row, $srcProps); + foreach($srcProps as $column) + unset($row[$column]); + $collections[$hash][] = $finder->populateObject($type,$row); + } + + $this->setResultCollection($results, $collections, array_values($sourceKeys)); + } + + /** + * @param TSqlCriteria + * @param TTableInfo association table info + * @param array field names + * @param array field values + */ + public function createCommand($criteria, $foreignKeys,$indexValues,$sourceKeys) + { + $innerJoin = $this->getAssociationJoin($foreignKeys,$indexValues,$sourceKeys); + $fkTable = $this->getForeignTable()->getTableFullName(); + $srcColumns = $this->getSourceColumns($sourceKeys); + if(($where=$criteria->getCondition())===null) + $where='1=1'; + $sql = "SELECT {$fkTable}.*, {$srcColumns} FROM {$fkTable} {$innerJoin} WHERE {$where}"; + + $parameters = $criteria->getParameters()->toArray(); + $ordering = $criteria->getOrdersBy(); + $limit = $criteria->getLimit(); + $offset = $criteria->getOffset(); + + $builder = $this->getCommandBuilder()->getBuilder(); + $command = $builder->applyCriterias($sql,$parameters,$ordering,$limit,$offset); + $this->getCommandBuilder()->onCreateCommand($command, $criteria); + return $command; + } + + protected function getSourceColumns($sourceKeys) + { + $columns=array(); + $table = $this->getAssociationTable(); + $tableName = $table->getTableFullName(); + foreach($sourceKeys as $name=>$fkName) + $columns[] = $tableName.'.'.$table->getColumn($name)->getColumnName(); + return implode(', ', $columns); + } + + protected function getAssociationJoin($foreignKeys,$indexValues,$sourceKeys) + { + $refInfo= $this->getAssociationTable(); + $fkInfo = $this->getForeignTable(); + + $refTable = $refInfo->getTableFullName(); + $fkTable = $fkInfo->getTableFullName(); + + $joins = array(); + foreach($foreignKeys as $ref=>$fk) + { + $refField = $refInfo->getColumn($ref)->getColumnName(); + $fkField = $fkInfo->getColumn($fk)->getColumnName(); + $joins[] = "{$fkTable}.{$fkField} = {$refTable}.{$refField}"; + } + $joinCondition = implode(' AND ', $joins); + + $index = $this->getCommandBuilder()->getIndexKeyCondition($refInfo,array_keys($sourceKeys), $indexValues); + + return "INNER JOIN {$refTable} ON ({$joinCondition}) AND {$index}"; + } +} +?> \ No newline at end of file diff --git a/framework/Data/ActiveRecord/Relations/TActiveRecordHasOne.php b/framework/Data/ActiveRecord/Relations/TActiveRecordHasOne.php new file mode 100644 index 00000000..048f776b --- /dev/null +++ b/framework/Data/ActiveRecord/Relations/TActiveRecordHasOne.php @@ -0,0 +1,42 @@ +getContext()->getForeignRecordFinder(); + $fkeys = $this->findForeignKeys($fkObject, $this->getSourceRecord()); + + $properties = array_values($fkeys); + $fields = array_keys($fkeys); + + $indexValues = $this->getIndexValues($properties, $results); + $fkObjects = $this->findForeignObjects($fields,$indexValues); + $this->populateResult($results,$properties,$fkObjects,$fields); + } + + /** + * Sets the foreign objects to the given property on the source object. + * @param TActiveRecord source object. + * @param array foreign objects. + */ + protected function setObjectProperty($source, $properties, &$collections) + { + $hash = $this->getObjectHash($source, $properties); + $prop = $this->getContext()->getProperty(); + if(isset($collections[$hash]) && count($collections[$hash]) > 0) + { + if(count($collections[$hash]) > 1) + throw new TActiveRecordException('ar_belongs_to_multiple_result'); + $source->{$prop} = $collections[$hash][0]; + } + } +} + +?> \ No newline at end of file diff --git a/framework/Data/ActiveRecord/Relations/TActiveRecordRelation.php b/framework/Data/ActiveRecord/Relations/TActiveRecordRelation.php new file mode 100644 index 00000000..f1e8fa60 --- /dev/null +++ b/framework/Data/ActiveRecord/Relations/TActiveRecordRelation.php @@ -0,0 +1,172 @@ +_context = $context; + } + + /** + * @return TActiveRecordRelationContext + */ + protected function getContext() + { + return $this->_context; + } + + /** + * @return TActiveRecord + */ + protected function getSourceRecord() + { + return $this->getContext()->getSourceRecord(); + } + + /** + * Dispatch the method calls to the source record finder object. When + * the results are returned as array or is an instance of TActiveRecord we + * will fetch the corresponding foreign objects with an sql query and populate + * the results obtained earlier. + * + * Allows chaining multiple relation handlers. + * + * @param string method name called + * @param array method arguments + * @return mixed TActiveRecord or array of TActiveRecord results depending on the method called. + */ + public function __call($method,$args) + { + static $stack=array(); + + $results = call_user_func_array(array($this->getSourceRecord(),$method),$args); + if(is_array($results) || $results instanceof TActiveRecord) + { + $this->collectForeignObjects($results); + while($obj = array_pop($stack)) + $obj->collectForeignObjects($results); + } + else if($results instanceof TActiveRecordRelation) + array_push($stack,$this); //call it later + return $results; + } + + /** + * Returns foreign keys in $fromRecord with source column names as key + * and foreign column names in the corresponding $matchesRecord as value. + * The method returns the first matching foreign key between these 2 records. + * @param TActiveRecord $fromRecord + * @param TActiveRecord $matchesRecord + * @return array foreign keys with source column names as key and foreign column names as value. + */ + protected function findForeignKeys($from, $matchesRecord) + { + $gateway = $matchesRecord->getRecordGateway(); + $matchingTableName = $gateway->getRecordTableInfo($matchesRecord)->getTableName(); + $tableInfo=$from; + if($from instanceof TActiveRecord) + $tableInfo = $gateway->getRecordTableInfo($from); + foreach($tableInfo->getForeignKeys() as $fkeys) + { + if($fkeys['table']===$matchingTableName) + return $fkeys['keys']; + } + throw new TActiveRecordException('no fk defined for '.$tableInfo->getTableFullName()); + } + + /** + * @param mixed object or array to be hashed + * @param array name of property for hashing the properties. + * @return string object hash using crc32 and serialize. + */ + protected function getObjectHash($obj, $properties) + { + $ids=array(); + foreach($properties as $property) + $ids[] = is_object($obj) ? $obj->{$property} : $obj[$property]; + return sprintf('%x',crc32(serialize($ids))); + } + + /** + * Fetches the foreign objects using TActiveRecord::findAllByIndex() + * @param array field names + * @param array foreign key index values. + * @return TActiveRecord[] foreign objects. + */ + protected function findForeignObjects($fields, $indexValues) + { + $criteria = $this->getContext()->getCriteria(); + $finder = $this->getContext()->getForeignRecordFinder(); + return $finder->findAllByIndex($criteria, $fields, $indexValues); + } + + /** + * Obtain the foreign key index values from the results. + * @param array property names + * @param array|TActiveRecord TActiveRecord results + * @return array foreign key index values. + */ + protected function getIndexValues($keys, $results) + { + if(!is_array($results)) + $results = array($results); + foreach($results as $result) + { + $value = array(); + foreach($keys as $name) + $value[] = $result->{$name}; + $values[] = $value; + } + return $values; + } + + /** + * Populate the results with the foreign objects found. + * @param array source results + * @param array source property names + * @param array foreign objects + * @param array foreign object field names. + */ + protected function populateResult(&$results,$properties,&$fkObjects,$fields) + { + $collections=array(); + foreach($fkObjects as $fkObject) + { + $hash = $this->getObjectHash($fkObject, $fields); + $collections[$hash][]=$fkObject; + } + + $this->setResultCollection($results, $collections, $properties); + } + + protected function setResultCollection(&$results, &$collections, $properties) + { + if(is_array($results)) + { + for($i=0,$k=count($results);$i<$k;$i++) + $this->setObjectProperty($results[$i], $properties, $collections); + } + else + { + $this->setObjectProperty($results, $properties, $collections); + } + } + + /** + * Sets the foreign objects to the given property on the source object. + * @param TActiveRecord source object. + * @param array source properties + * @param array foreign objects. + */ + protected function setObjectProperty($source, $properties, &$collections) + { + $hash = $this->getObjectHash($source, $properties); + $prop = $this->getContext()->getProperty(); + $source->{$prop} = isset($collections[$hash]) ? $collections[$hash] : array(); + } +} + +?> \ No newline at end of file diff --git a/framework/Data/ActiveRecord/Relations/TActiveRecordRelationContext.php b/framework/Data/ActiveRecord/Relations/TActiveRecordRelationContext.php new file mode 100644 index 00000000..03dc4cd5 --- /dev/null +++ b/framework/Data/ActiveRecord/Relations/TActiveRecordRelationContext.php @@ -0,0 +1,104 @@ +_sourceRecord=$source; + $this->_property=$property; + $this->_criteria=$criteria; + $this->_relation = $this->getSourceRecordRelation($property); + } + /** + * @param string relation property name + * @return array relation definition. + */ + protected function getSourceRecordRelation($property) + { + $class = new ReflectionClass($this->_sourceRecord); + $statics = $class->getStaticProperties(); + if(!isset($statics[self::RELATIONS_CONST])) + throw new TActiveRecordException('ar_relations_undefined', + get_class($this->_sourceRecord), self::RELATIONS_CONST); + if(isset($statics[self::RELATIONS_CONST][$property])) + return $statics[self::RELATIONS_CONST][$property]; + else + throw new TActiveRecordException('ar_undefined_relation_prop', + $property, get_class($this->_sourceRecord), self::RELATIONS_CONST); + } + + public function getProperty() + { + return $this->_property; + } + + public function getCriteria() + { + return $this->_criteria; + } + + public function getSourceRecord() + { + return $this->_sourceRecord; + } + + public function getForeignRecordClass() + { + return $this->_relation[1]; + } + + public function getRelationType() + { + return $this->_relation[0]; + } + + public function getAssociationTable() + { + return $this->_relation[2]; + } + + public function hasAssociationTable() + { + return isset($this->_relation[2]); + } + + public function getForeignRecordFinder() + { + return TActiveRecord::finder($this->getForeignRecordClass()); + } + + public function getRelationHandler() + { + switch($this->getRelationType()) + { + case TActiveRecord::HAS_MANY: + if(!$this->hasAssociationTable()) + { + Prado::using('System.Data.ActiveRecord.Relations.TActiveRecordHasMany'); + return new TActiveRecordHasMany($this); + } + else + { + Prado::using('System.Data.ActiveRecord.Relations.TActiveRecordHasManyAssociation'); + return new TActiveRecordHasManyAssociation($this); + } + case TActiveRecord::HAS_ONE: + Prado::using('System.Data.ActiveRecord.Relations.TActiveRecordHasOne'); + return new TActiveRecordHasOne($this); + case TActiveRecord::BELONGS_TO: + Prado::using('System.Data.ActiveRecord.Relations.TActiveRecordBelongsTo'); + return new TActiveRecordBelongsTo($this); + default: + throw new TException('Not done yet'); + } + } +} + +?> \ No newline at end of file diff --git a/framework/Data/ActiveRecord/TActiveRecord.php b/framework/Data/ActiveRecord/TActiveRecord.php index 509d23f6..67057554 100644 --- a/framework/Data/ActiveRecord/TActiveRecord.php +++ b/framework/Data/ActiveRecord/TActiveRecord.php @@ -10,9 +10,12 @@ * @package System.Data.ActiveRecord */ +/** + * Load record manager, criteria and relations. + */ Prado::using('System.Data.ActiveRecord.TActiveRecordManager'); Prado::using('System.Data.ActiveRecord.TActiveRecordCriteria'); -Prado::using('System.Data.ActiveRecord.TActiveRecordRelation'); +Prado::using('System.Data.ActiveRecord.Relations.TActiveRecordRelationContext'); /** * Base class for active records. @@ -279,7 +282,7 @@ abstract class TActiveRecord extends TComponent * @param array name value pair record data * @return TActiveRecord object record, null if data is empty. */ - protected function populateObject($type, $data) + public function populateObject($type, $data) { if(empty($data)) return null; $registry = $this->getRecordManager()->getObjectStateRegistry(); @@ -308,7 +311,7 @@ abstract class TActiveRecord extends TComponent /** * @param TDbDataReader data reader */ - protected function collectObjects($reader) + public function collectObjects($reader) { $result=array(); $class = get_class($this); @@ -425,8 +428,16 @@ abstract class TActiveRecord extends TComponent } /** - * - * + * Fetches records using the sql clause "(fields) IN (values)", where + * fields is an array of column names and values is an array of values that + * the columns must have. + * + * This method is to be used by the relationship handler. + * + * @param TActiveRecordCriteria additional criteria + * @param array field names to match with "(fields) IN (values)" sql clause. + * @param array matching field values. + * @return array matching active records. */ public function findAllByIndex($criteria,$fields,$values) { @@ -450,22 +461,18 @@ abstract class TActiveRecord extends TComponent return $gateway->countRecords($this,$criteria); } - /** - * + /** + * Returns the active record relationship handler for $RELATION with key + * value equal to the $property value. + * @param string relationship property name. + * @param array method call arguments. * @return TActiveRecordRelation */ - protected function getRecordRelation($property,$args) + protected function getRelationHandler($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'); - } + $criteria = $this->getCriteria(count($args)>0 ? $args[0] : null, array_slice($args,1)); + $context = new TActiveRecordRelationContext($this, $property, $criteria); + return $context->getRelationHandler(); } /** @@ -503,7 +510,7 @@ abstract class TActiveRecord extends TComponent if(substr(strtolower($method),0,4)==='with') { $property= $method[4]==='_' ? substr($method,5) : substr($method,4); - return $this->getRecordRelation($property, $args); + return $this->getRelationHandler($property, $args); } else if($findOne = substr(strtolower($method),0,6)==='findby') $condition = $method[6]==='_' ? substr($method,7) : substr($method,6); diff --git a/framework/Data/ActiveRecord/TActiveRecordRelation.php b/framework/Data/ActiveRecord/TActiveRecordRelation.php deleted file mode 100644 index 6fae5499..00000000 --- a/framework/Data/ActiveRecord/TActiveRecordRelation.php +++ /dev/null @@ -1,124 +0,0 @@ -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/TDbCommandBuilder.php b/framework/Data/Common/TDbCommandBuilder.php index c90b913c..3a08d890 100644 --- a/framework/Data/Common/TDbCommandBuilder.php +++ b/framework/Data/Common/TDbCommandBuilder.php @@ -140,6 +140,11 @@ class TDbCommandBuilder extends TComponent $where='1=1'; $table = $this->getTableInfo()->getTableFullName(); $sql = "SELECT * FROM {$table} WHERE {$where}"; + return $this->applyCriterias($sql, $parameters, $ordering, $limit, $offset); + } + + public function applyCriterias($sql, $parameters=array(),$ordering=array(), $limit=-1, $offset=-1) + { if(count($ordering) > 0) $sql = $this->applyOrdering($sql, $ordering); if($limit>=0 || $offset>=0) @@ -161,13 +166,7 @@ class TDbCommandBuilder extends TComponent $where='1=1'; $table = $this->getTableInfo()->getTableFullName(); $sql = "SELECT COUNT(*) FROM {$table} WHERE {$where}"; - if(count($ordering) > 0) - $sql = $this->applyOrdering($sql, $ordering); - if($limit>=0 || $offset>=0) - $sql = $this->applyLimitOffset($sql, $limit, $offset); - $command = $this->createCommand($sql); - $this->bindArrayValues($command, $parameters); - return $command; + return $this->applyCriterias($sql, $parameters, $ordering, $limit, $offset); } /** diff --git a/framework/Data/DataGateway/TDataGatewayCommand.php b/framework/Data/DataGateway/TDataGatewayCommand.php index 43a57aa7..80856a0e 100644 --- a/framework/Data/DataGateway/TDataGatewayCommand.php +++ b/framework/Data/DataGateway/TDataGatewayCommand.php @@ -179,7 +179,7 @@ class TDataGatewayCommand extends TComponent public function findAllByIndex($criteria,$fields,$values) { - $index = $this->getIndexKeyCondition($fields,$values); + $index = $this->getIndexKeyCondition($this->getTableInfo(),$fields,$values); if(strlen($where = $criteria->getCondition())>0) $criteria->setCondition("({$index}) AND ({$where})"); else @@ -202,11 +202,12 @@ class TDataGatewayCommand extends TComponent return $this->onExecuteCommand($command,$command->execute()); } - protected function getIndexKeyCondition($fields,$values) + public function getIndexKeyCondition($table,$fields,$values) { $columns = array(); + $tableName = $table->getTableFullName(); foreach($fields as $field) - $columns[] = $this->getTableInfo()->getColumn($field)->getColumnName(); + $columns[] = $tableName.'.'.$table->getColumn($field)->getColumnName(); return '('.implode(', ',$columns).') IN '.$this->quoteTuple($values); } @@ -236,7 +237,7 @@ class TDataGatewayCommand extends TComponent throw new TDbException('dbtablegateway_pk_value_count_mismatch', $this->getTableInfo()->getTableFullName()); } - return $this->getIndexKeyCondition($primary, $values); + return $this->getIndexKeyCondition($this->getTableInfo(),$primary, $values); } /** diff --git a/tests/simple_unit/ActiveRecord/ForeignKeyTestCase.php b/tests/simple_unit/ActiveRecord/ForeignKeyTestCase.php index 6c240b2f..8c39a797 100644 --- a/tests/simple_unit/ActiveRecord/ForeignKeyTestCase.php +++ b/tests/simple_unit/ActiveRecord/ForeignKeyTestCase.php @@ -2,24 +2,32 @@ Prado::using('System.Data.ActiveRecord.TActiveRecord'); -abstract class Mysql4Record extends TActiveRecord +abstract class SqliteRecord extends TActiveRecord { protected static $conn; public function getDbConnection() { if(self::$conn===null) - self::$conn = new TDbConnection('mysql:host=localhost;port=3306;dbname=tests', 'test4', 'test4'); + self::$conn = new TDbConnection('sqlite:'.dirname(__FILE__).'/fk_tests.db'); return self::$conn; } } -class Album extends Mysql4Record +class Album extends SqliteRecord { public $title; - public $Tracks = array(self::HAS_MANY, 'Track'); - public $Artists = array(self::HAS_MANY, 'Artist', 'album_artist'); + public $Tracks = array(); + public $Artists = array(); + + public $cover; + + protected static $RELATIONS = array( + 'Tracks' => array(self::HAS_MANY, 'Track'), + 'Artists' => array(self::HAS_MANY, 'Artist', 'album_artists'), + 'cover' => array(self::HAS_ONE, 'Cover') + ); public static function finder($class=__CLASS__) { @@ -27,11 +35,15 @@ class Album extends Mysql4Record } } -class Artist extends Mysql4Record +class Artist extends SqliteRecord { public $name; - public $Albums = array(self::HAS_MANY, 'Album', 'album_artist'); + public $Albums = array(); + + protected static $RELATIONS=array( + 'Albums' => array(self::HAS_MANY, 'Album', 'album_artists') + ); public static function finder($class=__CLASS__) { @@ -39,13 +51,17 @@ class Artist extends Mysql4Record } } -class Track extends Mysql4Record +class Track extends SqliteRecord { public $id; public $song_name; public $album_id; //FK -> Album.id - public $Album = array(self::BELONGS_TO, 'Album'); + public $Album; + + protected static $RELATIONS = array( + 'Album' => array(self::BELONGS_TO, 'Album'), + ); public static function finder($class=__CLASS__) { @@ -53,61 +69,89 @@ class Track extends Mysql4Record } } -abstract class SqliteRecord extends TActiveRecord +class Cover extends SqliteRecord { - protected static $conn; - - public function getDbConnection() - { - if(self::$conn===null) - self::$conn = new TDbConnection('sqlite:'.dirname(__FILE__).'/blog.db'); - return self::$conn; - } + public $album; + public $content; } -class PostRecord extends SqliteRecord +class ForeignKeyTestCase extends UnitTestCase { - const TABLE='posts'; - public $post_id; - public $author; - public $create_time; - public $title; - public $content; - public $status; + function test_has_many() + { + $albums = Album::finder()->withTracks()->findAll(); + $this->assertEqual(count($albums), 2); + + $this->assertEqual($albums[0]->title, 'Album 1'); + $this->assertEqual($albums[1]->title, 'Album 2'); + + $this->assertEqual(count($albums[0]->Artists), 0); + $this->assertEqual(count($albums[1]->Artists), 0); - public $authorRecord = array(self::HAS_ONE, 'BlogUserRecord'); + $this->assertEqual(count($albums[0]->Tracks), 3); + $this->assertEqual(count($albums[1]->Tracks), 2); - public static function finder($className=__CLASS__) + $this->assertEqual($albums[0]->Tracks[0]->song_name, 'Track 1'); + $this->assertEqual($albums[0]->Tracks[1]->song_name, 'Song 2'); + $this->assertEqual($albums[0]->Tracks[2]->song_name, 'Song 3'); + + $this->assertEqual($albums[1]->Tracks[0]->song_name, 'Track A'); + $this->assertEqual($albums[1]->Tracks[1]->song_name, 'Track B'); + } + + function test_has_one() { - return parent::finder($className); + $albums = Album::finder()->with_cover()->findAll(); + $this->assertEqual(count($albums), 2); + + $this->assertEqual($albums[0]->title, 'Album 1'); + $this->assertEqual($albums[1]->title, 'Album 2'); + + $this->assertEqual($albums[0]->cover->content, 'lalala'); + $this->assertEqual($albums[1]->cover->content, 'conver content'); + + $this->assertEqual(count($albums[0]->Artists), 0); + $this->assertEqual(count($albums[1]->Artists), 0); + + $this->assertEqual(count($albums[0]->Tracks), 0); + $this->assertEqual(count($albums[1]->Tracks), 0); } -} -class BlogUserRecord extends SqliteRecord -{ - const TABLE='users'; - public $username; - public $email; - public $password; - public $role; - public $first_name; - public $last_name; - public $posts = array(self::HAS_MANY, 'PostRecord'); + function test_belongs_to() + { + $track = Track::finder()->withAlbum()->find('id = ?', 1); + + $this->assertEqual($track->id, "1"); + $this->assertEqual($track->song_name, "Track 1"); + $this->assertEqual($track->Album->title, "Album 1"); + } - public static function finder($className=__CLASS__) + function test_has_many_associate() { - return parent::finder($className); + $album = Album::finder()->withArtists()->find('title = ?', 'Album 2'); + $this->assertEqual($album->title, 'Album 2'); + $this->assertEqual(count($album->Artists), 3); + + $this->assertEqual($album->Artists[0]->name, 'Dan'); + $this->assertEqual($album->Artists[1]->name, 'Karl'); + $this->assertEqual($album->Artists[2]->name, 'Tom'); } -} -class ForeignKeyTestCase extends UnitTestCase -{ - function test() + function test_multiple_fk() { - $album = Album::finder()->withTracks()->findAll(); - //print_r($album); - //print_r(PostRecord::finder()->findAll()); - //print_r(BlogUserRecord::finder()->with_posts()->findAll()); + $album = Album::finder()->withArtists()->withTracks()->with_cover()->find('title = ?', 'Album 1'); + + $this->assertEqual($album->title, 'Album 1'); + $this->assertEqual(count($album->Artists), 2); + + $this->assertEqual($album->Artists[0]->name, 'Dan'); + $this->assertEqual($album->Artists[1]->name, 'Jenny'); + + $this->assertEqual($album->Tracks[0]->song_name, 'Track 1'); + $this->assertEqual($album->Tracks[1]->song_name, 'Song 2'); + $this->assertEqual($album->Tracks[2]->song_name, 'Song 3'); + + $this->assertEqual($album->cover->content, 'lalala'); } } diff --git a/tests/simple_unit/ActiveRecord/fk_tests.db b/tests/simple_unit/ActiveRecord/fk_tests.db new file mode 100644 index 00000000..87835c84 Binary files /dev/null and b/tests/simple_unit/ActiveRecord/fk_tests.db differ diff --git a/tests/simple_unit/ActiveRecord/sqlite.sql b/tests/simple_unit/ActiveRecord/sqlite.sql new file mode 100644 index 00000000..03e4a1ab --- /dev/null +++ b/tests/simple_unit/ActiveRecord/sqlite.sql @@ -0,0 +1,46 @@ +CREATE TABLE album ( + title varchar(100) NOT NULL PRIMARY KEY +); + +CREATE TABLE artist ( + name varchar(25) NOT NULL PRIMARY KEY +); + +CREATE TABLE album_artists ( + album_title varchar(100) NOT NULL CONSTRAINT fk_album REFERENCES album(title) ON DELETE CASCADE, + artist_name varchar(25) NOT NULL CONSTRAINT fk_artist REFERENCES artist(name) ON DELETE CASCADE +); + +CREATE TABLE track ( + id INTEGER NOT NULL PRIMARY KEY, + song_name varchar(200) NOT NULL default '', + album_id varchar(100) NOT NULL CONSTRAINT fk_album_1 REFERENCES album(title) ON DELETE CASCADE +); + +CREATE TABLE cover( + album varchar(200) NOT NULL CONSTRAINT fk_album_2 REFERENCES album(title) ON DELETE CASCADE, + content text +); + +INSERT INTO album (title) VALUES ('Album 1'); +INSERT INTO album (title) VALUES ('Album 2'); + +INSERT INTO cover(album,content) VALUES ('Album 1', 'lalala'); +INSERT INTO cover(album,content) VALUES ('Album 2', 'conver content'); + +INSERT INTO artist (name) VALUES ('Dan'); +INSERT INTO artist (name) VALUES ('Jenny'); +INSERT INTO artist (name) VALUES ('Karl'); +INSERT INTO artist (name) VALUES ('Tom'); + +INSERT INTO album_artists (album_title, artist_name) VALUES ('Album 1', 'Dan'); +INSERT INTO album_artists (album_title, artist_name) VALUES ('Album 2', 'Dan'); +INSERT INTO album_artists (album_title, artist_name) VALUES ('Album 1', 'Jenny'); +INSERT INTO album_artists (album_title, artist_name) VALUES ('Album 2', 'Karl'); +INSERT INTO album_artists (album_title, artist_name) VALUES ('Album 2', 'Tom'); + +INSERT INTO track (id, song_name, album_id) VALUES (1, 'Track 1', 'Album 1'); +INSERT INTO track (id, song_name, album_id) VALUES (2, 'Song 2', 'Album 1'); +INSERT INTO track (id, song_name, album_id) VALUES (3, 'Track A', 'Album 2'); +INSERT INTO track (id, song_name, album_id) VALUES (4, 'Track B', 'Album 2'); +INSERT INTO track (id, song_name, album_id) VALUES (5, 'Song 3', 'Album 1'); \ No newline at end of file diff --git a/tests/simple_unit/DbCommon/CommandBuilderMysqlTest.php b/tests/simple_unit/DbCommon/CommandBuilderMysqlTest.php index 500d5277..6b744a4b 100644 --- a/tests/simple_unit/DbCommon/CommandBuilderMysqlTest.php +++ b/tests/simple_unit/DbCommon/CommandBuilderMysqlTest.php @@ -6,7 +6,7 @@ class CommandBuilderMysqlTest extends UnitTestCase { function mysql_meta_data() { - $conn = new TDbConnection('mysql:host=localhost;dbname=tests', 'test','test'); + $conn = new TDbConnection('mysql:host=localhost;dbname=tests;port=3307', 'test5','test5'); return new TMysqlMetaData($conn); } diff --git a/tests/simple_unit/unit.php b/tests/simple_unit/unit.php index b58c98c5..7e86e925 100644 --- a/tests/simple_unit/unit.php +++ b/tests/simple_unit/unit.php @@ -1,7 +1,6 @@