Active Records are objects that wrap a row in a database table or view, encapsulate the database access and add domain logic on that data. - The basics of an Active Record are business classes, e.g., a + The basics of an Active Record are business classes, e.g., a Products class, that match very closely the record structure of an underlying database table. Each Active Record will be responsible for saving and loading data to and from the database.
Active Record is a good choice for domain logic that isn't too complex, +
Active Record is a good choice for domain logic that isn't too complex, such as creates, reads, updates, and deletes. Derivations and validations based on a single record work well in this structure. Active Record has the - primary advantage of simplicity. It's easy to build + primary advantage of simplicity. It's easy to build Active Records, and they are easy to understand.
-However, as your business logic grows in complexity, you'll soon want +
However, as your business logic grows in complexity, you'll soon want to use your object's direct relationships, collections, inheritance, and so - forth. These don't map easily onto Active Record, and adding them piecemeal + forth. These don't map easily onto Active Record, and adding them piecemeal gets very messy. - Another argument against Active Record is the fact that it couples the object + Another argument against Active Record is the fact that it couples the object design to the database design. This makes it more difficult to refactor as a project goes forward.
- +The alternative is to use a Data Mapper that separates the roles of the
- business object and how these objects are stored.
- Prado provides a complimentary choice between Active Record and
- SqlMap Data Mapper.
+ business object and how these objects are stored.
+ Prado provides a complimentary choice between Active Record and
+ SqlMap Data Mapper.
A SqlMap Data Mapper can be used to load Active Record objects, in turn; these
- Active Record objects can be used to update the database.
+ Active Record objects can be used to update the database.
The "relationship" between Active Records and SqlMap is illustrated in the
following diagram. More details regarding the SqlMap Data Mapper can be found in
the SqlMap Manual.
alt="Active Records and SqlMap DataMapper" id="fig:diagram.png" class="figure"/>
The Active Record class has functionality to perform the following tasks.
@@ -52,8 +52,8 @@-The Active Record implementation utilizes the Prado DAO classes for data access. -The current Active Record implementation supports the following database. +The Active Record implementation utilizes the Prado DAO classes for data access. +The current Active Record implementation supports the following database.
Let us
- consider the following "users" table that contains two columns named "username" and "email",
- where "username" is also the primary key.
+ consider the following "users" table that contains two columns named "username" and "email",
+ where "username" is also the primary key.
Each column of the "users" table must have corresponding +
Each column of the "users" table must have corresponding
property of the same name as the column name in the UserRecord class.
Of course, you also define additional member variables or properties that does not exist in the table structure.
The class constant
@@ -117,7 +117,7 @@ You may specify qualified table names. E.g. for MySQL, TABLE = "`database1`.
The static method finder() returns an UserRecord instance
that can be used to load records from the database. The loading of records
- using the finer methods is discussed a little later. The TActiveRecord::finder()
+ using the finder methods is discussed a little later. The TActiveRecord::finder()
static method takes the name of an Active Record class as parameter.
Alternatively, you can create a base class and override the getDbConnection() method to return a database connection. This is a simple way to permit multiple -connections and multiple databases. The following code demonstrates defining +connections and multiple databases. The following code demonstrates defining the database connection in a base class (not need to set the DB connection anywhere else).
The default database connection can also be configured using a <module>
- tag in the application.xml
+ tag in the application.xml
or config.xml as follows.
The TActiveRecord class provides many convenient methods to find
- records from the database. The simplest is finding one record by matching a primary key or a
+ records from the database. The simplest is finding one record by matching a primary key or a
composite key (primary keys that consists of multiple columns).
See the
Finds records using full SQL where findBySql() return an Active Record and findAllBySql()returns an array of record objects. @@ -391,16 +391,16 @@ $user2 = new UserRecord($data); //create by passing some existing data $user2->save(); //insert a new record
-To update a record in the database, just change one or more properties of
+To update a record in the database, just change one or more properties of
the Active Record object that has been loaded from the database and then
-call the save() method.
+call the save() method.
We see that new TActiveRecord objects are created by either using one of the find*()
methods or using creating a new instance by using PHP's new keyword. Objects
-created by a find*() method starts with clean state. New instance of
+created by a find*() method starts with clean state. New instance of
TActiveRecord created other than by a find*() method starts with new state.
-Whenever you
+Whenever you
call the save() method on the TActiveRecord object, the object enters the clean
state. Objects in the clean becomes dirty whenever one of more of its
internal states are changed. Calling the delete() method on the object
@@ -429,8 +429,8 @@ ends the object life-cycle, no further actions can be performed on the object.
To delete an existing record that is already loaded, just call the delete() method.
You can also delete records in the database by primary keys without
- loading any records using the deleteByPk() method (and equivalently the deleteAllByPks() method).
- For example, to delete one or records with tables using one or more primary keys.
+ loading any records using the deleteByPk() method (and equivalently the deleteAllByPks() method).
+ For example, to delete one or several records with tables using one or more primary keys.
The OnCreateCommand event is raised when a command is prepared and +
The OnCreateCommand event is raised when a command is prepared and parameter binding is completed. The parameter object is TDataGatewayEventParameter of which the Command property can be inspected to obtain the SQL query to be executed.
-The OnExecuteCommand event is raised when a command is executed and the -result from the database was returned. The parameter object is TDataGatewayResultEventParameter -of which the Result property contains the data return from the database. +The OnExecuteCommand event is raised when a command is executed and the +result from the database was returned. The parameter object is TDataGatewayResultEventParameter +of which the Result property contains the data return from the database. The data returned can be changed by setting the Result property.
Using the OnExecuteCommand we can attach an event handler to log +
Using the OnExecuteCommand we can attach an event handler to log the entire SQL query executed for a given TActiveRecord class or instance. For example, we define a base class and override either the getDbConnection() or the constructor.
@@ -534,7 +534,7 @@ function logger($sender,$param) } TActiveRecord::finder('MyRecord')->OnExecuteCommand[] = 'logger'; $obj->OnExecuteCommand[] = array($logger, 'log'); //any valid PHP callback. - +-In the following sections we shall consider the following table relationships between +In the following sections we will consider the following table relationships between Teams, Players, Skills and Profiles.
The goal is to obtain object models that represent to some degree the entity -relationships in the above figure. +relationships in the above figure.
-There is a mismatch between relationships with objects and table relationships. -First there's a difference in representation. Objects handle links by storing references -that are held by the runtime memory-managed environment. Relational databases handle -links by forming a key into another table. Second, objects can easily use collections -to handle multiple references from a single field, while normalization forces -all entity relation links to be single valued. This leads to reversals of the data -structure between objects and tables. The approach taken in the Prado Active Record +There is a mismatch between relationships with objects and table relationships. +First there's a difference in representation. Objects handle links by storing references +that are held by the runtime memory-managed environment. Relational databases handle +links by forming a key into another table. Second, objects can easily use collections +to handle multiple references from a single field, while normalization forces +all entity relation links to be single valued. This leads to reversals of the data +structure between objects and tables. The approach taken in the Prado Active Record design is to use the table foreign key constraints to derive object relationships. This implies that the underlying database must support foreign key constraints.
@@ -589,9 +589,9 @@ CREATE TABLE barThe entity relationship between the Teams and Players table is what is known -as an 1-M relationship. That is, one Team may contain 0 or more Players. In terms of -object relationships, we say that a TeamRecord object has many PlayerRecord objects. -(Notice the reversal of the reversal of the direction of relationships between tables and objects.) +as an 1-M relationship. That is, one Team may contain 0 or more Players. In terms of +object relationships, we say that a TeamRecord object has many PlayerRecord objects. +(Notice the reversal of the direction of relationships between tables and objects.)
@@ -603,7 +603,7 @@ class TeamRecord extends TActiveRecord const TABLE='Teams'; public $name; public $location; - + public $players=array(); //define the $player member having has many relationship with PlayerRecord @@ -644,7 +644,7 @@ have other relationships that corresponds to other tables (including the Pla
The "has many" relationship is not fetched automatically when you use any of the Active Record finder methods. -You will need to explicitly fetch the related objects as follows. In the code below, both lines +You will need to explicitly fetch the related objects as follows. In the code below, both lines are equivalent and the method names are case insensitive.
The static $RELATIONS property of PlayerRecord defines that the
-property $team belongs to a TeamRecord.
+property $team belongs to a TeamRecord.
The $RELATIONS array also defines two other relationships that we
-shall examine in later sections below.
+shall examine in later sections below.
In array(self::BELONGS_TO, 'TeamRecord'), the first element defines the
-relationship type, in this case self::BELONGS_TO and
+relationship type, in this case self::BELONGS_TO and
the second element is a string 'TeamRecord' that corresponds to the
class name of the TeamRecord class.
A player object with the corresponding team object may be fetched as follows.
@@ -732,7 +732,7 @@ example demonstrates.
-Objects can handle multivalued fields quite easily by using collections as field values. -Relational databases don't have this feature and are constrained to single-valued fields only. -When you're mapping a one-to-many association you can handle this using has many relationships, -essentially using a foreign key for the single-valued end of the association. -But a many-to-many association can't do this because there is no single-valued end to +Objects can handle multivalued fields quite easily by using collections as field values. +Relational databases don't have this feature and are constrained to single-valued fields only. +When you're mapping a one-to-many association you can handle this using has many relationships, +essentially using a foreign key for the single-valued end of the association. +But a many-to-many association can't do this because there is no single-valued end to hold the foreign key.
-The answer is the classic resolution that's been used by relational data people -for decades: create an extra table (an association table) to record the relationship. -The basic idea is using an association table to store the association. This table -has only the foreign key IDs for the two tables that are linked together, it has one +The answer is the classic resolution that's been used by relational data people +for decades: create an extra table (an association table) to record the relationship. +The basic idea is using an association table to store the association. This table +has only the foreign key IDs for the two tables that are linked together, it has one row for each pair of associated objects.
-The association table has no corresponding in-memory object and its primary key is the +The association table has no corresponding in-memory object and its primary key is the compound of the two primary keys of the tables that are associated. -In simple terms, to load data from the association table you perform two queries (in general, it may also be achieved using one query consisting of joins). -Consider loading the SkillRecord collection for a list PlayerRecord objects. -In this case, you do queries in two stages. -The first stage queries the Players table to find all the rows of the players you want. -The second stage finds the SkillRecord object for the related player ID for each row +In simple terms, to load data from the association table you perform two queries (in general, it may also be achieved using one query consisting of joins). +Consider loading the SkillRecord collection for a list PlayerRecord objects. +In this case, you do queries in two stages. +The first stage queries the Players table to find all the rows of the players you want. +The second stage finds the SkillRecord object for the related player ID for each row in the Player_Skills association table using an inner join.
-The Prado Active Record design implements the two stage approach. For the +
The Prado Active Record design implements the two stage approach. For the Players-Skills M-N (many-to-many) entity relationship, we need to define a has many relationship in the PlayerRecord class and in addition define a has many relationship in the SkillRecord class as well. @@ -866,7 +866,7 @@ In array(self::HAS_MANY, 'PlayerRecord', 'Player_Skills'), the first el relationship type, in this case self::HAS_MANY, the second element is a string 'PlayerRecord' that corresponds to the class name of the PlayerRecord class, and the third element is the name -of the association table name. +of the association table name.
A list of player objects with the corresponding collection of skill objects may be fetched as follows.
@@ -889,22 +889,22 @@ item via the related_items association table. The syntax in the followi
example is valid for a PostgreSQL database. For other database, consult their respective documentation for
defining the foreign key constraints.
The association table name in third element of the relationship array may
contain the foreign table column names. The columns defined in the association
table must also be defined in the record class (e.g. the $related_item_id property
-corresponds to the related_item_id column in the related_items table).
+corresponds to the related_item_id column in the related_items table).
Tip:
-Compound keys in the foreign table can
+Compound keys in the foreign table can
be specified as comma separated values between brackets. E.g.
-'related_items.(id1,id2)'.
+'related_items.(id1,id2)'.
Adding/Removing/Updating Related Objects
@@ -959,7 +959,7 @@ $team->save();
Since the TeamRecord class contains a has many relationship with the PlayerRecord, -then saving a TeamRecord object will also update the corresponding foreign objects in $players array. +then saving a TeamRecord object will also update the corresponding foreign objects in $players array. That is, the objects in $players are inserted/updated in the database and the $team_name property of those objects will contain the foreign key value that corresponds to the $team object's primary key value.
@@ -969,7 +969,7 @@ the object's delete() method. You may setup the database table's foreig when deleting a particular data in the database it will delete the referenced data as well (it may also be achieved using database triggers). E.g. such as having a "ON DELETE CASCADE" constraint. Deleting foreign objects by either setting the property value to null or removing the object from an array will NOT -remove the corresponding data in the database. +remove the corresponding data in the database.To remove associations for the many-to-many relationships via an association table, an Active Record @@ -983,7 +983,7 @@ PlayerSkillAssocation::finder()->deleteByPk(array('fk1','fk2'));
Using the with_xxx() methods will load the relationship record on demand. Retrieving the -related record using lazy loading (that is, only when those related objects are accessed) can be +related record using lazy loading (that is, only when those related objects are accessed) can be achieved by using a feature of the TComponent that provides accessor methods. In particular, we define a pair of getter and setter methods where the getter method will retrieve the relationship conditionally. The following example illustrates that the PlayerRecord can retrieve its @@ -995,7 +995,7 @@ class PlayerRecord extends BaseFkRecord //... other properties and methods as before private $_skills; //change to private and default as null - + public function getSkills() { if($this->_skills===null && $this->player_id !==null) @@ -1018,12 +1018,12 @@ class PlayerRecord extends BaseFkRecord } }
We first need to change the $skills=array() declaration to a private property +
We first need to change the $skills=array() declaration to a private property $_skills (notice the underscore) and set it to null instead. This allows us -to define the skills property using getter/setter methods +to define the skills property using getter/setter methods (see Components for details). The getSkills() getter method for the skills property will lazy load the corresponding skills foreign record -when it is used as follows. Notice that we only do a lazy load when its $player_id is +when it is used as follows. Notice that we only do a lazy load when its $player_id is not null (that is, when the record is already fetched from the database or player id was already set).
The setSkills() ensures that the skills property will always be a TList. Using a TList allows us to set the elements of the skills property as if they were arrays. E.g. $player->skills[] = new SkillRecord(). If array was used, a PHP error -will be thrown. +will be thrown.
Data Mappers moves data between objects and a database while keeping them +
Data Mappers moves data between objects and a database while keeping them independent of each other and the mapper itself. If you started with Active Records, you may eventually faced with more complex business @@ -13,11 +13,11 @@ that is, the object schema and the relational schema don't match up.
-The Data Mapper separates the in-memory objects from the database. Its responsibility - is to transfer data between the two and also to isolate them from each other. - With Data Mapper the in-memory objects needn't know even that there's a database +
The Data Mapper separates the in-memory objects from the database. Its responsibility + is to transfer data between the two and also to isolate them from each other. + With Data Mapper the in-memory objects needn't know even that there's a database present; they need no SQL interface code, and certainly no knowledge of the - database schema. (The database schema is always ignorant of the objects that use it.) + database schema. (The database schema is always ignorant of the objects that use it.)
This helps you in the code because you can understand and work with the domain objects - without having to understand how they're stored in the database. You can modify the +
This helps you in the code because you can understand and work with the domain objects + without having to understand how they're stored in the database. You can modify the business models or the database without having to alter either. With complicated mappings, particularly those involving existing databases, this is very valuable.
-The price, of course, is the extra layer that you don't get with - Active Record, - so the test for using these patterns is the complexity of the business logic. - If you have fairly simple business logic, an Active Record - will probably work. +
The price, of course, is the extra layer that you don't get with + Active Record, + so the test for using these patterns is the complexity of the business logic. + If you have fairly simple business logic, an Active Record + will probably work. For more complicated logic a Data Mapper may be more suitable.
The SqlMap DataMapper framework makes it easier to use a database with a PHP application. - SqlMap DataMapper couples objects with stored procedures or SQL statements using - a XML descriptor. Simplicity is the biggest advantage of the SqlMap DataMapper over - object relational mapping tools. To use SqlMap DataMapper you rely on your own objects, - XML, and SQL. There is little to learn that you don't already know. +
The SqlMap DataMapper framework makes it easier to use a database with a PHP application. + SqlMap DataMapper couples objects with stored procedures or SQL statements using + a XML descriptor. Simplicity is the biggest advantage of the SqlMap DataMapper over + object relational mapping tools. To use SqlMap DataMapper you rely on your own objects, + XML, and SQL. There is little to learn that you don't already know. With SqlMap DataMapper you have the full power of both SQL and stored procedures at your fingertip
alt="SqlMap Data Mapper Overview" id="fig:sqlmap.png" class="figure"/>
-
- Here's a high level description of the work flow illustrated in the figure abov.
+
+ Here's a high level description of the work flow illustrated in the figure above.
Provide a parameter, either as an object or a primitive type. The parameter can be
- used to set runtime values in your SQL statement or stored procedure. If a runtime value
+ used to set runtime values in your SQL statement or stored procedure. If a runtime value
is not needed, the parameter can be omitted.
Execute the mapping by passing the parameter and the name you gave the statement or +
Execute the mapping by passing the parameter and the name you gave the statement or procedure in your XML descriptor. This step is where the magic happens. The framework - will prepare the SQL statement or stored procedure, set any runtime values using your + will prepare the SQL statement or stored procedure, set any runtime values using your parameter, execute the procedure or statement, and return the result.
In the case of an update, the number of rows affected is returned. In the case of a - query, a single object, or a collection of objects is returned. Like the parameter, + query, a single object, or a collection of objects is returned. Like the parameter, the result object, or collection of objects, can be a plain-old object or a primitive PHP type.
@@ -82,8 +82,8 @@ $dsn = 'pgsql:host=localhost;dbname=test'; //Postgres SQL $conn = new TDbConnection($dsn, 'dbuser','dbpass'); $manager = new TSqlMapManager($conn); $manager->configureXml('my-sqlmap.xml'); -$sqlmap = $manager->getSqlMapGateway(); -@@ -96,31 +96,31 @@ $sqlmap = $manager->getSqlMapGateway();
SqlMap database connection can also be configured using a <module>
- tag in the application.xml
+ tag in the application.xml
or config.xml as follows.
The ConfigFile attribute should point to a SqlMap configuration file - (to be detailed later) either using absolute path, relative path or the + (to be detailed later) either using absolute path, relative path or the Prado's namespace dot notation path (must omit the ".xml" extension). - +
To obtain the SqlMap gateway interface from the <module> configuration, simply @@ -140,8 +140,8 @@ class MyPage extends TPage
Let us
- consider the following "users" table that contains two columns named "username" and "email",
- where "username" is also the primary key.
+ consider the following "users" table that contains two columns named "username" and "email",
+ where "username" is also the primary key.
The above example shows demonstrates only a fraction of the capabilities - of the SqlMap Data Mapper. Further details can be found in the + of the SqlMap Data Mapper. Further details can be found in the SqlMap Manual.
@@ -197,12 +197,12 @@ $user = $sqlmap->queryForObject("SelectUsers");The above example may seem trival and it also seems that there is alot work just to retrieve some data. However, notice that the User class is totally unware of been stored in the database, and the database is - unware of the User class. + unware of the User class.
One of advantages of SqlMap is the
ability to map complex object relationship, collections from an existing
- database. On the other hand, Active Record
+ database. On the other hand, Active Record
provide a very simple way
to interact with the underlying database but unable to do more complicated
relationship or collections. A good compromise is to use SqlMap to retrieve
@@ -214,11 +214,11 @@ $user = $sqlmap->queryForObject("SelectUsers");
The PHP code for retrieving the users remains the same, but SqlMap
returns Active Records instead, and we can take advantage of the Active Record methods.
-
+
--
cgit v1.2.3