From 45b0fe42a979d444d547a5248eb2e9e915aaf16a Mon Sep 17 00:00:00 2001
From: wei <>
Date: Sun, 14 Jan 2007 02:10:24 +0000
Subject: Add "block-content" to allow user comments on block level elements in
quickstart docs.
---
.../protected/pages/Database/ActiveRecord.page | 139 +++++++++++----------
demos/quickstart/protected/pages/Database/DAO.page | 70 +++++------
.../protected/pages/Database/SqlMap.page | 84 ++++++-------
3 files changed, 147 insertions(+), 146 deletions(-)
(limited to 'demos/quickstart/protected/pages/Database')
diff --git a/demos/quickstart/protected/pages/Database/ActiveRecord.page b/demos/quickstart/protected/pages/Database/ActiveRecord.page
index 4dd70608..bc1d2359 100644
--- a/demos/quickstart/protected/pages/Database/ActiveRecord.page
+++ b/demos/quickstart/protected/pages/Database/ActiveRecord.page
@@ -1,7 +1,7 @@
Active Records are objects that wrap a row in a database table or view,
+ Active Records are objects that wrap a row in a database table or view,
encapsulates the database access and adds domain logic on that data.
The basics of an Active Record is a business object class, e.g., a
Products class, that match very closely the record structure
@@ -13,21 +13,21 @@
Each field in the class must correspond to one column in the table.
- 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
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
gets very messy.
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
+ 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.
@@ -39,26 +39,28 @@
+
The Active Record class has methods that do the following:
- Active Record
-Active Record
+When to Use It
-When to Use It
+ alt="Active Records and SqlMap DataMapper" id="fig:diagram.png" class="figure"/>
+
The Active Record implementation utilizes the Prado DAO classes for data access. The current Active Record implementation supports MySQL, Postgres SQL and SQLite databases. Support for other databases can be provided when there are sufficient demand. -
Let us +
+Let us
consider the following "users" table that contains two columns named "username" and "email",
where "username" is also the primary key.
-
Next we define our Active Record class that corresponds to the "users" table.
- Next we define our Active Record class that corresponds to the "users" table.
+ Each property of the UserRecord class must correspond to a
+ Each property of the UserRecord class must correspond to a
column with the same name in the "users" table. The static class variable
$_tablename (must be public) is optional when the class name is the same as
the table name in the database, otherwise $_tablename must
@@ -102,7 +104,7 @@ E.g. MySQL uses back-ticks, $_tablename = "`database1`.`table1`"
Since TActiveRecord extends TComponent, setter and
getter methods can be defined to allow control over how variables
are set and returned. For example, adding a $level property to the UserRecord class:
-
+
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 discuss a little later. The TActiveRecord::getRecordFinder()
static method takes the name of the current Active Record class as parameter.
+
A default database connection for Active Record can be set as follows.
See Establishing Database Connection for
futher details regarding creation of database connection in general.
-
+
The default database connection can also be configured using a <module>
tag in the application.xml
or config.xml as follows.
- A ConnectionID property can be specified with value corresponding
+ A ConnectionID property can be specified with value corresponding
to another TDataSourceConfig module configuration's ID value. This allows
the same database connection to be used in other modules such as SqlMap.
-
+
The TActiveRecord class provides many convenient methods to find
records from the database. The simplest is finding records by matching primary keys.
See the Finds one record using only the primary key or composite primary keys.
- Finds one record using only the primary key or composite primary keys.
+ Finds multiple records using a list of primary keys or composite primary keys.
+ Finds multiple records using a list of primary keys or composite primary keys.
The following are equivalent for scalar primary keys (primary key consisting of only one column/field).
- Finds one single record that matches the criteria. The criteria
- can be a partial SQL string or a TActiveRecordCriteria object.
- Finds one single record that matches the criteria. The criteria
+ can be a partial SQL string or a TActiveRecordCriteria object. The TActiveRecordCriteria class has the following properties:
- The TActiveRecordCriteria class has the following properties:
+ Same as find() but returns an array of objects. Same as find() but returns an array of objects. Dynamic find method using parts of method name as search criteria.
+ Dynamic find method using parts of method name as search criteria.
Method names starting with findBy return 1 record only.
Method names starting with findAllBy return an array of records.
The condition is taken as part of the method name after findBy or findAllBy.
The following blocks of code are equivalent:
- Finds records using full SQL, returns corresponding array of record objects. Finds records using full SQL, returns corresponding array of record objects. Find the number of matchings records. Find the number of matchings records.
+
Add a new record using TActiveRecord is very simple, just create a new Active
Record object and call the save() method. E.g.
-
+
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.
-
+
Active Record objects have a simple life-cycle illustrated in the following diagram.
+
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.
For example, to delete one or records with tables having a scalar primary key.
-
+
For composite primary keys (determined automatically from the table definitions):
- All Active Record objects contains the property DbConnection
+ All Active Record objects contains the property DbConnection
that can be used to obtain a transaction object.
-
+
Data Access Objects (DAO) separates a data resource's client interface from its data access mechanisms. It adapts a specific data resource's access API to a generic client interface. As a result, data access mechanisms can be changed independently of the code that uses the data.
+
Since version 3.1, PRADO starts to provide a DAO that is a thin wrap around PHP Data Objects (PDO). Although PDO has a nice feature set and good APIs, we choose to implement the PRADO DAO on top of PDO because the PRADO DAO classes are component classes and are thus configurable in a PRADO application. Users can use these DAO classes in a more PRADO-preferred way.
+
The PRADO DAO mainly consists of the following four classes (in contrast to PDO which uses only two classes, PDO and PDOStatement):
+
In the following, we introduce the usage of PRADO DAO in different scenarios.
+
To establish a database connection, one creates a TDbConnection instance and activate it. A data source name (DSN) is needed to specify the information required to connect to the database. The database username and password may need to be supplied to establish the connection.
+
Complete specification of DSN may be found in the PDO documentation. Below is a list of commonly used DSN formats:
+
In case any error occurs when establishing the connection (such as bad DSN or username/password), a TDbException will be raised.
+
Once a database connection is established, SQL statements can be executed through TDbCommand. One creates a TDbCommand by calling TDbConnection.createCommand() with the specified SQL statement:
+
An SQL statement is executed via TDbCommand in one of the following two ways:
+
In case an error occurs during the execution of SQL statements, a TDbException will be raised.
+
After TDbCommand.query() generates the TDbDataReader instance, one can retrieve rows of resulting data by calling TDbDataReader.read() repeatedly. One can also use TDbDataReader in PHP's foreach language construct to retrieve row by row.
+
When an application executes a few queries, each reading and/or writing information in the database, it is important to be sure that the database is not left with only some of the queries carried out. A transaction, represented as a TDbTransaction instance in PRADO, may be initiated in this case:
+
To avoid SQL injection attacks and to improve performance of executing repeatedly used SQL statements, one can "prepare" an SQL statement with optional parameter placeholders that are to be replaced with the actual parameters during the parameter binding process.
+
The parameter placeholders can be either named (represented as unique tokens) or unnamed (represented as question marks). Call TDbCommand.bindParameter() or TDbCommand.bindValue() to replace these placeholders with the actual parameters. The parameters do not need to be quoted: the underlying database driver does it for you. Parameter binding must be done before the SQL statement is executed.
+
The methods bindParameter() and bindValue() are very similar. The only difference is that the former binds a parameter with a PHP variable reference while the latter with a value. For parameters that represent large block of data memory, the former is preferred for performance consideration.
+
For more details about binding parameters, see the relevant PHP documentation.
+
When fetching query results, one can also bind columns with PHP variables so that they are automatically populated with the latest data each time a row is fetched.
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
@@ -12,28 +12,28 @@
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
+ 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.)
The primary occasion for using Data Mapper is when you want the database schema
+ The primary occasion for using Data Mapper is when you want the database schema
and the object model to evolve independently. Data Mapper's primary benefit is
that when working on the business (or domain) objects you can ignore the database, both in
design and in the build and testing process. The domain objects have no idea
what the database structure is, because all the correspondence is done by the mappers.
This helps you in the code because you can understand and work with the domain objects
+ 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
+ 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
@@ -41,8 +41,8 @@
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.
+ 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,
@@ -51,7 +51,7 @@
your fingertip
+
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
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
+ 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,
the result object, or collection of objects, can be a plain-old object or a primitive PHP type.
+
A database connection for SqlMap can be set as follows.
See Establishing Database Connection for
futher details regarding creation of database connection in general.
-
+
The TSqlMapManager is responsible for setting up the database connection
and configuring the SqlMap with given XML file(s). The configureXml()
method accepts a string that points to a SqlMap XML configuration file. Once
@@ -93,11 +93,11 @@ $sqlmap = $manager->getSqlMapGateway();
of the SqlMap gateway interface (use this object to insert/delete/find records).
+
SqlMap database connection can also be configured using a <module>
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
Prado's namespace dot notation path (must omit the ".xml" extension).
@@ -122,9 +122,9 @@ $sqlmap = $manager->getSqlMapGateway();
To obtain the SqlMap gateway interface from the <module> configuration, simply
+ To obtain the SqlMap gateway interface from the <module> configuration, simply
do, for example,
- Let us
+ Let us
consider the following "users" table that contains two columns named "username" and "email",
where "username" is also the primary key.
- Next we define our plain User class as follows. Notice that
+ Next we define our plain User class as follows. Notice that
the User is very simple.
- Next, we need to define a SqlMap XMl configuration file, lets name
+ Next, we need to define a SqlMap XMl configuration file, lets name
the file as my-sqlmap.xml
- The <select> tag returns defines an SQL statement. The id
+ The <select> tag returns defines an SQL statement. The id
attribute will be used as the identifier for the query. The resultClass
attribute value is the name of the class the the objects to be returned.
We can now query the objects as follows:
- The above example shows demonstrates only a fraction of the capabilities
+ The above example shows demonstrates only a fraction of the capabilities
of the SqlMap Data Mapper. Further details can be found in the
SqlMap Manual.
The above example may seem trival and it also seems that there is
+ 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.
+
One of advantages of SqlMap is the
ability to map complex object relationship, collections from an existing
database. On the other hand, Active Record
@@ -208,9 +208,9 @@ $user = $sqlmap->queryForObject("SelectUsers");
complicated relationships and collections as Active Record objects and then using
these Active Records to do the updates, inserts and deletes.
Continuing with the previous example, we change the definition of the
+ Continuing with the previous example, we change the definition of the
User class to become an Active Record.
- We also need to change the definition of the SqlMap XML configuration. We
+ We also need to change the definition of the SqlMap XML configuration. We
just need to change the value of resultClass attribute to UserRecord.
- The PHP code for retrieving the users remains the same, but SqlMap
+ 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.
-Setting up a database connection
-Setting up a database connection
+Loading data from the database
-Loading data from the database
+findByPk()
- findByPk()
+ findAllByPks()
- findAllByPks()
+ find()
-find()
+
+
-findAll()
-findAll()
+findBy*() and findAllBy*()
-findBy*() and findAllBy*()
+findBySql()
-findBySql()
+count()
-count()
+Inserting and updating records
-Inserting and updating records
+ alt="Active Records Life Cycle" id="fig:cycle.png" class="figure"/>
We see that new ActiveRecord objects are created by either using one of the find*()
@@ -346,22 +347,22 @@ internal states are changed. Calling the delete() method on the object
ends the object life-cycle, no futher actions can be performed on the object.
Deleting existing records
-Deleting existing records
+Transactions
-Transactions
+References
-
+
References
+
diff --git a/demos/quickstart/protected/pages/Database/DAO.page b/demos/quickstart/protected/pages/Database/DAO.page
index ec16aec5..63f00706 100644
--- a/demos/quickstart/protected/pages/Database/DAO.page
+++ b/demos/quickstart/protected/pages/Database/DAO.page
@@ -1,33 +1,33 @@
Data Access Objects (DAO)
-Data Access Objects (DAO)
+
+
-Establishing Database Connection
-Establishing Database Connection
+
+
-Executing SQL Statements
-Executing SQL Statements
+
+
-Fetching Query Results
-Fetching Query Results
+Using Transactions
-Using Transactions
+
+
-Binding Parameters
-Binding Parameters
+Binding Columns
-Binding Columns
+Data Mapper
-Data Mapper
+When to Use It
-When to Use It
+SqlMap Data Mapper
-SqlMap Data Mapper
+ 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.
@@ -59,23 +59,23 @@
used to set runtime values in your SQL statement or stored procedure. If a runtime value
is not needed, the parameter can be omitted.
Setting up a database connection and initializing the SqlMap
-Setting up a database connection and initializing the SqlMap
+A quick example
-A quick example
+Combining SqlMap with Active Records
-Combining SqlMap with Active Records
+References
-
+
References
+