summaryrefslogtreecommitdiff
path: root/demos/quickstart/protected/pages
diff options
context:
space:
mode:
authorwei <>2007-01-09 10:42:06 +0000
committerwei <>2007-01-09 10:42:06 +0000
commit03f362a40a8dd39f8c8b4bf816334922b7b264e4 (patch)
tree73efc71e946519d2086520102a6c8a022510463a /demos/quickstart/protected/pages
parenta59a458f9ae2b178d08854c112f34068b50ea243 (diff)
add TActiveRecord::findAllByPks()
Diffstat (limited to 'demos/quickstart/protected/pages')
-rw-r--r--demos/quickstart/protected/pages/Database/ActiveRecord.page280
-rw-r--r--demos/quickstart/protected/pages/Database/SqlMap.page236
2 files changed, 276 insertions, 240 deletions
diff --git a/demos/quickstart/protected/pages/Database/ActiveRecord.page b/demos/quickstart/protected/pages/Database/ActiveRecord.page
index e3da53c0..a1337ba3 100644
--- a/demos/quickstart/protected/pages/Database/ActiveRecord.page
+++ b/demos/quickstart/protected/pages/Database/ActiveRecord.page
@@ -2,53 +2,53 @@
<!-- $Id $ -->
<h1>Active Record</h1>
<p>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
- <tt>Products</tt> 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. </p>
+ 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
+ <tt>Products</tt> 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. </p>
<div class="info"><b class="note">Info:</b>
- The data structure of an Active Record should match exactly that of a table
- in the database.
- Each field in the class must correspond to one column in the table.
+ The data structure of an Active Record should match exactly that of a table
+ in the database.
+ Each field in the class must correspond to one column in the table.
</div>
<h2>When to Use It</h2>
<p>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.</p>
+ 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.</p>
- <p>However, as your business logic grows in complexity, you'll soon want
- to use your object's direct relationships, collections, inheritance, and so
+ <p>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.</p>
-
- <p>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
- <a href="?page=Database.SqlMap">SqlMap Data Mapper</a>.
- 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.
- The "relationship" between Active Records and <a href="?page=Database.SqlMap">SqlMap</a> is illustrated in the
- following diagram. More details regarding the SqlMap Data Mapper can be found in
- the <a href="http://www.pradosoft.com/demos/sqlmap/">SqlMap Manual</a>.
- <img src=<%~ sqlmap_active_record.png %> alt="Active Records and SqlMap DataMapper" id="fig:diagram.png" class="figure"/>
- </p>
-
- <p>
- The Active Record class has methods that do the following:
- <ul>
- <li>Construct an instance of the Active Record from a SQL result set row.</li>
- <li>Construct a new instance for later insertion into the table.</li>
- <li>Finder methods to wrap commonly used SQL queries and return Active Record objects.</li>
- <li>Update existing records and insert new records into the database.</li>
- </ul>
- </p>
-The Active Record implementation utilizes the <a href="?page=Database.DAO">Prado DAO</a> classes for data access.
+ 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.</p>
+
+ <p>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
+ <a href="?page=Database.SqlMap">SqlMap Data Mapper</a>.
+ 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.
+ The "relationship" between Active Records and <a href="?page=Database.SqlMap">SqlMap</a> is illustrated in the
+ following diagram. More details regarding the SqlMap Data Mapper can be found in
+ the <a href="http://www.pradosoft.com/demos/sqlmap/">SqlMap Manual</a>.
+ <img src=<%~ sqlmap_active_record.png %> alt="Active Records and SqlMap DataMapper" id="fig:diagram.png" class="figure"/>
+ </p>
+
+ <p>
+ The Active Record class has methods that do the following:
+ <ul>
+ <li>Construct an instance of the Active Record from a SQL result set row.</li>
+ <li>Construct a new instance for later insertion into the table.</li>
+ <li>Finder methods to wrap commonly used SQL queries and return Active Record objects.</li>
+ <li>Update existing records and insert new records into the database.</li>
+ </ul>
+ </p>
+The Active Record implementation utilizes the <a href="?page=Database.DAO">Prado DAO</a> classes for data access.
The current Active Record implementation supports
<a href="http://www.mysql.com">MySQL</a>,
<a href="http://www.postgres.com">Postgres SQL</a> and
@@ -56,14 +56,14 @@ The current Active Record implementation supports
Support for other databases can be provided when there are sufficient demand.
<h2>Defining an Active Record</h2>
<p>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.
<com:TTextHighlighter Language="sql" CssClass="source">
CREATE TABLE users
(
- username VARCHAR( 20 ) NOT NULL ,
- email VARCHAR( 200 ) ,
- PRIMARY KEY ( username )
+ username VARCHAR( 20 ) NOT NULL ,
+ email VARCHAR( 200 ) ,
+ PRIMARY KEY ( username )
);
</com:TTextHighlighter>
</p>
@@ -71,91 +71,98 @@ CREATE TABLE users
<com:TTextHighlighter Language="php" CssClass="source">
class UserRecord extends TActiveRecord
{
- public $username; //the column named "username" in the "users" table
- public $email;
-
- public static $_tablename='users'; //table name
-
- /**
- * @return TActiveRecord active record finder instance
- */
- public static function finder()
- {
- return self::getRecordFinder('UserRecord');
- }
+ public $username; //the column named "username" in the "users" table
+ public $email;
+
+ public static $_tablename='users'; //table name
+
+ /**
+ * @return TActiveRecord active record finder instance
+ */
+ public static function finder()
+ {
+ return self::getRecordFinder('UserRecord');
+ }
}
</com:TTextHighlighter>
</p>
<p>Each property of the <tt>UserRecord</tt> class must correspond to a
- column with the same name in the "users" table. The static class variable
- <tt>$_tablename</tt> (must be public) is optional when the class name is the same as
- the table name in the database, otherwise <tt>$_tablename</tt> must
- specify the table name that corresponds to your Active Record class.
+ column with the same name in the "users" table. The static class variable
+ <tt>$_tablename</tt> (must be public) is optional when the class name is the same as
+ the table name in the database, otherwise <tt>$_tablename</tt> must
+ specify the table name that corresponds to your Active Record class.
</p>
<div class="tip"><b class="note">Tip:</b>
- Since <tt>TActiveRecord</tt> extends <tt>TComponent</tt>, setter and
- getter methods can be defined to allow control over how variables
- are set and returned. For example, adding a <tt>$level</tt> property to the UserRecord class:
+ Since <tt>TActiveRecord</tt> extends <tt>TComponent</tt>, setter and
+ getter methods can be defined to allow control over how variables
+ are set and returned. For example, adding a <tt>$level</tt> property to the UserRecord class:
<com:TTextHighlighter Language="php" CssClass="source">
class UserRecord extends TActiveRecord {
- ... //existing definitions as above
-
- private $_level;
- public function setLevel($value) {
- $this->_level=TPropertyValue::ensureInteger($value,0);
- }
- public function getLevel($value){
- return $this->_level;
- }
+ ... //existing definitions as above
+
+ private $_level;
+ public function setLevel($value) {
+ $this->_level=TPropertyValue::ensureInteger($value,0);
+ }
+ public function getLevel($value){
+ return $this->_level;
+ }
}
</com:TTextHighlighter>
</div>
+<div class="note"><b class="note">Note:</b>
+<tt>TActiveRecord</tt> can also work with database views by specifying the value <tt>$_tablename</tt>
+corresponding to the view name. However, objects returned
+from views are read-only, calling the <tt>save()</tt> or <tt>delete()</tt> method
+will raise an exception.
+</div>
+
<p>
- The static method <tt>finder()</tt> returns an <tt>UserRecord</tt> 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 <tt>TActiveRecord::getRecordFinder()</tt>
- static method takes the name of the current Active Record class as parameter.
+ The static method <tt>finder()</tt> returns an <tt>UserRecord</tt> 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 <tt>TActiveRecord::getRecordFinder()</tt>
+ static method takes the name of the current Active Record class as parameter.
</p>
<h2>Setting up a database connection</h2>
<p>
- A default database connection for Active Record can be set as follows.
- See <a href="?page=Database.DAO">Establishing Database Connection</a> for
- futher details regarding creation of database connection in general.
+ A default database connection for Active Record can be set as follows.
+ See <a href="?page=Database.DAO">Establishing Database Connection</a> for
+ futher details regarding creation of database connection in general.
<com:TTextHighlighter Language="php" CssClass="source">
//create a connection and give it to the ActiveRecord manager.
$dsn = 'pgsql:host=localhost;dbname=test'; //Postgres SQL
$conn = new TDbConnection($dsn, 'dbuser','dbpass');
TActiveRecordManager::getInstance()->setDbConnection($conn);
-</com:TTextHighlighter>
+</com:TTextHighlighter>
</p>
<p>
- The default database connection can also be configured using a <tt>&lt;module&gt;</tt>
- tag in the <a href="?page=Configurations.AppConfig">application.xml</a>
- or <a href="?page=Configurations.PageConfig">config.xml</a> as follows.
+ The default database connection can also be configured using a <tt>&lt;module&gt;</tt>
+ tag in the <a href="?page=Configurations.AppConfig">application.xml</a>
+ or <a href="?page=Configurations.PageConfig">config.xml</a> as follows.
<com:TTextHighlighter Language="xml" CssClass="source">
<modules>
<module class="System.Data.ActiveRecord.TActiveRecordConfig" EnableCache="true">
<database ConnectionString="pgsql:host=localhost;dbname=test"
Username="dbuser" Password="dbpass" />
</module>
-</modules>
-</com:TTextHighlighter>
+</modules>
+</com:TTextHighlighter>
<div class="tip"><b class="note">Tip:</b>
- The <tt>EnableCache</tt> attribute when set to "true" will cache the table
- meta data, that is, the table columns names, indexes and constraints are
- saved in the cache and reused. You must clear or disable the cache if you
- wish to see chanages made to your table definitions. A <a href="?page=Advanced.Performance#6402">cache
- module</a> must also be defined for the cache to function.
+ The <tt>EnableCache</tt> attribute when set to "true" will cache the table
+ meta data, that is, the table columns names, indexes and constraints are
+ saved in the cache and reused. You must clear or disable the cache if you
+ wish to see chanages made to your table definitions. A <a href="?page=Advanced.Performance#6402">cache
+ module</a> must also be defined for the cache to function.
</div>
</p>
<p>A <tt>ConnectionID</tt> property can be specified with value corresponding
- to another <tt>TDataSourceConfig</tt> module configuration's ID value. This allows
- the same database connection to be used in other modules such as <a href="?page=Database.SqlMap">SqlMap</a>.
+ to another <tt>TDataSourceConfig</tt> module configuration's ID value. This allows
+ the same database connection to be used in other modules such as <a href="?page=Database.SqlMap">SqlMap</a>.
<com:TTextHighlighter Language="xml" CssClass="source">
<modules>
<module class="System.Data.TDataSourceConfig" ID="db1">
@@ -168,32 +175,52 @@ TActiveRecordManager::getInstance()->setDbConnection($conn);
<module class="System.Data.SqlMap.TSqlMapConfig"
ConnectionID="db1" ... />
-</modules>
-</com:TTextHighlighter>
+</modules>
+</com:TTextHighlighter>
</p>
<h2>Loading data from the database</h2>
<p>
- The <tt>TActiveRecord</tt> class provides many convenient methods to find
- records from the database. The simplest is finding records by matching primary keys.
- See the <com:DocLink ClassPath="System.Data.ActiveRecord.TActiveRecord" /> for
- more details.
+ The <tt>TActiveRecord</tt> class provides many convenient methods to find
+ records from the database. The simplest is finding records by matching primary keys.
+ See the <com:DocLink ClassPath="System.Data.ActiveRecord.TActiveRecord" /> for
+ more details.
</p>
- <h3><tt>findByPk()</tt></h3>
- <p>Finds one record using only the primary key or composite primary keys.
+ <h3><tt>findByPk()</tt></h3>
+ <p>Finds one record using only the primary key or composite primary keys.
<com:TTextHighlighter Language="php" CssClass="source">
$finder = UserRecord::finder();
$user = $finder->findByPk($primaryKey);
//when the table uses composite keys
-$record = $finder->findByPk($key1, $key2, ...); //for composite keys
-$record = $finder->findByPk(array($key1, $key2,...)); //same as above
+$record = $finder->findByPk($key1, $key2, ...);
+$record = $finder->findByPk(array($key1, $key2,...));
</com:TTextHighlighter>
</p>
+ <h3><tt>findAllByPks()</tt></h3>
+ <p>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).
+<com:TTextHighlighter Language="php" CssClass="source">
+$finder = UserRecord::finder();
+$users = $finder->findAllByPk($key1, $key2, ...);
+$users = $finder->findAllByPk(array($key1, $key2, ...));
+</com:TTextHighlighter>
+The following are equivalent for composite keys.
+<com:TTextHighlighter Language="php" CssClass="source">
+//when the table uses composite keys
+$record = $finder->findAllByPks(array($key1, $key2), array($key3, $key4), ...);
+
+$keys = array( array($key1, $key2), array($key3, $key4), ... );
+$record = $finder->findAllByPks($keys);
+
+</com:TTextHighlighter>
+</p>
+
+
<h3><tt>find()</tt></h3>
<p>Finds <b>one single record</b> that matches the criteria. The criteria
- can be a partial SQL string or a <tt>TActiveRecordCriteria</tt> object.
+ can be a partial SQL string or a <tt>TActiveRecordCriteria</tt> object.
<com:TTextHighlighter Language="php" CssClass="source">
$finder = UserRecord::finder();
@@ -212,13 +239,13 @@ $finder->find($criteria); //the 2nd parameter for find() is ignored.
</p>
<p>The <tt>TActiveRecordCriteria</tt> class has the following properties:
- <ul>
- <li><tt>Parameters</tt> -- name value parameter pairs.</li>
- <li><tt>OrderBy</tt> -- column name and ordering pairs.</li>
- <li><tt>Condition</tt> -- parts of the WHERE SQL conditions.</li>
- <li><tt>Limit</tt> -- maximum number of records to return.</li>
- <li><tt>Offset</tt> -- record offset in the table.</li>
- </ul>
+ <ul>
+ <li><tt>Parameters</tt> -- name value parameter pairs.</li>
+ <li><tt>OrderBy</tt> -- column name and ordering pairs.</li>
+ <li><tt>Condition</tt> -- parts of the WHERE SQL conditions.</li>
+ <li><tt>Limit</tt> -- maximum number of records to return.</li>
+ <li><tt>Offset</tt> -- record offset in the table.</li>
+ </ul>
</p>
<com:TTextHighlighter Language="php" CssClass="source">
@@ -258,8 +285,8 @@ $finder->find('Username = ? AND Password = ?', $name, $pass);
$finder->findAllByAge($age);
$finder->findAll('Age = ?', $age);
</com:TTextHighlighter>
-</p>
-
+</p>
+
<h3><tt>findBySql()</tt></h3>
<p>Finds records using full SQL, returns corresponding array of record objects.</p>
@@ -291,7 +318,7 @@ incremented values.</div>
<p>
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 <tt>save()</tt> method.
+call the <tt>save()</tt> method.
<com:TTextHighlighter Language="php" CssClass="source">
$user = UserRecord::finder()->findByName('admin');
@@ -303,14 +330,23 @@ $user->save(); //update it.
<p>
Active Record objects have a simple life-cycle illustrated in the following diagram.
<img src=<%~ object_states.png %> 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 <tt>find*()</tt>
+methods or using creating a new instance by using PHP's <tt>new</tt> keyword. Objects
+created by a <tt>find*()</tt> method starts with <tt>clean</tt> state. New instance of
+ActiveRecords created other than by a <tt>find*()</tt> method starts with <tt>new</tt> state.
+When ever you
+call the <tt>save()</tt> method on the ActiveRecord object, the object enters the <tt>clean</tt>
+state. Objects in the <tt>clean</tt> becomes <tt>dirty</tt> whenever one of more of its
+internal states are changed. Calling the <tt>delete()</tt> method on the object
+ends the object life-cycle, no futher actions can be performed on the object.
</p>
<h2>Deleting existing records</h2>
<p>
- To delete an existing record that is already loaded, just call the <tt>delete()</tt> method.
- You can also delete records in the database by primary keys without
- loading any records using the <tt>deleteByPk()</tt> method.
- For example, to delete one or records with tables having a scalar primary key.
+ To delete an existing record that is already loaded, just call the <tt>delete()</tt> method.
+ You can also delete records in the database by primary keys without
+ loading any records using the <tt>deleteByPk()</tt> method.
+ For example, to delete one or records with tables having a scalar primary key.
<com:TTextHighlighter Language="php" CssClass="source">
$finder->deleteByPk($primaryKey); //delete 1 record
$finder->deleteByPk($key1,$key2,...); //delete multiple records
@@ -333,7 +369,7 @@ $finder->deleteByPk(array( array($key1,$key2), array($key3,$key4), .. ));
<h2>Transactions</h2>
<p>All Active Record objects contains the property <tt>DbConnection</tt>
- that can be used to obtain a transaction object.
+ that can be used to obtain a transaction object.
<com:TTextHighlighter Language="php" CssClass="source">
$finder = UserRecord::finder();
@@ -345,7 +381,7 @@ try
$user->save();
$transaction->commit();
}
-catch(Exception $e) // an exception is raised if a query fails will be raised
+catch(Exception $e) // an exception is raised if a query fails
{
$transaction->rollBack();
}
@@ -353,8 +389,8 @@ catch(Exception $e) // an exception is raised if a query fails will be raised
<h2>References</h2>
<ul>
- <li>Fowler et. al. <i>Patterns of Enterprise Application Architecture</i>,
- Addison Wesley, 2002.</li>
+ <li>Fowler et. al. <i>Patterns of Enterprise Application Architecture</i>,
+ Addison Wesley, 2002.</li>
</ul>
</com:TContent> \ No newline at end of file
diff --git a/demos/quickstart/protected/pages/Database/SqlMap.page b/demos/quickstart/protected/pages/Database/SqlMap.page
index 2b70e8f5..4b462168 100644
--- a/demos/quickstart/protected/pages/Database/SqlMap.page
+++ b/demos/quickstart/protected/pages/Database/SqlMap.page
@@ -3,78 +3,78 @@
<h1>Data Mapper</h1>
<p>Data Mappers moves data between objects and a database while keeping them
- independent of each other and the mapper itself. If you started with
- <a href="?page=Database.ActiveRecord">Active Records</a>, you may eventually
- faced with more complex business
- objects as your project progresses. When you build an object model with a
- lot of business logic it's valuable to use these mechanisms to better organize
- the data and the behavior that goes with it. Doing so leads to variant schemas;
- that is, the object schema and the relational schema don't match up.
+ independent of each other and the mapper itself. If you started with
+ <a href="?page=Database.ActiveRecord">Active Records</a>, you may eventually
+ faced with more complex business
+ objects as your project progresses. When you build an object model with a
+ lot of business logic it's valuable to use these mechanisms to better organize
+ the data and the behavior that goes with it. Doing so leads to variant schemas;
+ that is, the object schema and the relational schema don't match up.
</p>
<p>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.)
+ 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.)
</p>
<h2>When to Use It</h2>
<p>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.
+ 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.
</p>
<p>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 <b>existing databases</b>, this is very valuable.
+ 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 <b>existing databases</b>, this is very valuable.
</p>
<p>The price, of course, is the extra layer that you don't get with
- <a href="?page=Database.ActiveRecord">Active Record</a>,
- so the test for using these patterns is the complexity of the business logic.
- If you have fairly simple business logic, an <a href="?page=Database.ActiveRecord">Active Record</a>
- will probably work.
- For more complicated logic a Data Mapper may be more suitable.
+ <a href="?page=Database.ActiveRecord">Active Record</a>,
+ so the test for using these patterns is the complexity of the business logic.
+ If you have fairly simple business logic, an <a href="?page=Database.ActiveRecord">Active Record</a>
+ will probably work.
+ For more complicated logic a Data Mapper may be more suitable.
</p>
<h2>SqlMap Data Mapper</h2>
<p>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
+ 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
</p>
<p>
- <img src=<%~ diagram.png %> 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.
- 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
- is not needed, the parameter can be omitted.
+ <img src=<%~ diagram.png %> 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.
+ 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
+ is not needed, the parameter can be omitted.
</p>
<p>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.
+ 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.
</p>
<p>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.
+ 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.
</p>
<h2>Setting up a database connection and initializing the SqlMap</h2>
<p>
- A database connection for SqlMap can be set as follows.
- See <a href="?page=Database.DAO">Establishing Database Connection</a> for
- futher details regarding creation of database connection in general.
+ A database connection for SqlMap can be set as follows.
+ See <a href="?page=Database.DAO">Establishing Database Connection</a> for
+ futher details regarding creation of database connection in general.
<com:TTextHighlighter Language="php" CssClass="source">
//create a connection and give it to the SqlMap manager.
$dsn = 'pgsql:host=localhost;dbname=test'; //Postgres SQL
@@ -82,21 +82,21 @@ $conn = new TDbConnection($dsn, 'dbuser','dbpass');
$manager = new TSqlMapManager($conn);
$manager->configureXml('my-sqlmap.xml');
$sqlmap = $manager->getSqlMapGateway();
-</com:TTextHighlighter>
+</com:TTextHighlighter>
</p>
<p>
- The <tt>TSqlMapManager</tt> is responsible for setting up the database connection
- and configuring the SqlMap with given XML file(s). The <tt>configureXml()</tt>
- method accepts a string that points to a SqlMap XML configuration file. Once
- configured, call the <tt>getSqlMapGateway()</tt> method to obtain an instance
- of the SqlMap gateway interface (use this object to insert/delete/find records).
+ The <tt>TSqlMapManager</tt> is responsible for setting up the database connection
+ and configuring the SqlMap with given XML file(s). The <tt>configureXml()</tt>
+ method accepts a string that points to a SqlMap XML configuration file. Once
+ configured, call the <tt>getSqlMapGateway()</tt> method to obtain an instance
+ of the SqlMap gateway interface (use this object to insert/delete/find records).
</p>
<p>
- SqlMap database connection can also be configured using a <tt>&lt;module&gt;</tt>
- tag in the <a href="?page=Configurations.AppConfig">application.xml</a>
- or <a href="?page=Configurations.PageConfig">config.xml</a> as follows.
+ SqlMap database connection can also be configured using a <tt>&lt;module&gt;</tt>
+ tag in the <a href="?page=Configurations.AppConfig">application.xml</a>
+ or <a href="?page=Configurations.PageConfig">config.xml</a> as follows.
<com:TTextHighlighter Language="xml" CssClass="source">
<modules>
<module id="my-sqlmap" class="System.Data.SqlMap.TSqlMapConfig"
@@ -104,66 +104,66 @@ $sqlmap = $manager->getSqlMapGateway();
<database ConnectionString="pgsql:host=localhost;dbname=test"
Username="dbuser" Password="dbpass" />
</module>
-</modules>
-</com:TTextHighlighter>
+</modules>
+</com:TTextHighlighter>
</p>
<p>
- The <tt>ConfigFile</tt> 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).
-
- <div class="tip"><b class="note">Tip:</b>
- The <tt>EnableCache</tt> attribute when set to "true" will cache the
- parsed configuration. You must clear or disable the cache if you
- make chanages your configuration file.
- A <a href="?page=Advanced.Performance#6402">cache
- module</a> must also be defined for the cache to function.
- </div>
+ The <tt>ConfigFile</tt> 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).
+
+ <div class="tip"><b class="note">Tip:</b>
+ The <tt>EnableCache</tt> attribute when set to "true" will cache the
+ parsed configuration. You must clear or disable the cache if you
+ make chanages your configuration file.
+ A <a href="?page=Advanced.Performance#6402">cache
+ module</a> must also be defined for the cache to function.
+ </div>
</p>
<p>To obtain the SqlMap gateway interface from the &lt;module&gt; configuration, simply
- do, for example,
+ do, for example,
<com:TTextHighlighter Language="php" CssClass="source">
class MyPage extends TPage
{
- public function onLoad($param)
- {
- parent::onLoad($param);
- $sqlmap = $this->Application->Modules['my-sqlmap']->Client;
- $sqlmap->queryForObject(...); //query for some object
- }
+ public function onLoad($param)
+ {
+ parent::onLoad($param);
+ $sqlmap = $this->Application->Modules['my-sqlmap']->Client;
+ $sqlmap->queryForObject(...); //query for some object
+ }
}
</com:TTextHighlighter>
</p>
<h2>A quick example</h2>
<p>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.
<com:TTextHighlighter Language="sql" CssClass="source">
CREATE TABLE users
(
- username VARCHAR( 20 ) NOT NULL ,
- email VARCHAR( 200 ) ,
- PRIMARY KEY ( username )
+ username VARCHAR( 20 ) NOT NULL ,
+ email VARCHAR( 200 ) ,
+ PRIMARY KEY ( username )
);
</com:TTextHighlighter>
</p>
<p>Next we define our plain <tt>User</tt> class as follows. Notice that
- the <tt>User</tt> is very simple.
+ the <tt>User</tt> is very simple.
<com:TTextHighlighter Language="php" CssClass="source">
class User
{
- public $username;
- public $email;
+ public $username;
+ public $email;
}
</com:TTextHighlighter>
</p>
</p>
<p>Next, we need to define a SqlMap XMl configuration file, lets name
- the file as <tt>my-sqlmap.xml</tt>
+ the file as <tt>my-sqlmap.xml</tt>
<com:TTextHighlighter Language="xml" CssClass="source">
<?xml version="1.0" encoding="utf-8" ?>
<sqlMapConfig>
@@ -174,10 +174,10 @@ class User
</com:TTextHighlighter>
</p>
<p>The &lt;select&gt; tag returns defines an SQL statement. The <tt>id</tt>
- attribute will be used as the identifier for the query. The <tt>resultClass</tt>
- attribute value is the name of the class the the objects to be returned.
- We can now query the objects as follows:
-
+ attribute will be used as the identifier for the query. The <tt>resultClass</tt>
+ attribute value is the name of the class the the objects to be returned.
+ We can now query the objects as follows:
+
<com:TTextHighlighter Language="php" CssClass="source">
//assume that $sqlmap is an TSqlMapGateway instance
$userList = $sqlmap->queryForList("SelectUsers");
@@ -188,49 +188,49 @@ $user = $sqlmap->queryForObject("SelectUsers");
</p>
<p>The above example shows demonstrates only a fraction of the capabilities
- of the SqlMap Data Mapper. Further details can be found in the
- <a href="http://www.pradosoft.com/demo/sqlamp/">SqlMap Manual</a>.
+ of the SqlMap Data Mapper. Further details can be found in the
+ <a href="http://www.pradosoft.com/demo/sqlamp/">SqlMap Manual</a>.
</p>
<h2>Combining SqlMap with Active Records</h2>
<p>The above example may seem trival and it also seems that there is
- alot work just to retrieve some data. However, notice that the <tt>User</tt>
- class is totally unware of been stored in the database, and the database is
- unware of the <tt>User</tt> class.
+ alot work just to retrieve some data. However, notice that the <tt>User</tt>
+ class is totally unware of been stored in the database, and the database is
+ unware of the <tt>User</tt> class.
</p>
<p>
- One of advantages of SqlMap is the
- ability to map complex object relationship, collections from an existing
- database. On the other hand, <a href="?page=Database.ActiveRecord">Active Record</a>
- 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
- complicated relationships and collections as Active Record objects and then using
- these Active Records to do the updates, inserts and deletes.
+ One of advantages of SqlMap is the
+ ability to map complex object relationship, collections from an existing
+ database. On the other hand, <a href="?page=Database.ActiveRecord">Active Record</a>
+ 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
+ complicated relationships and collections as Active Record objects and then using
+ these Active Records to do the updates, inserts and deletes.
</p>
<p>Continuing with the previous example, we change the definition of the
- <tt>User</tt> class to become an Active Record.
+ <tt>User</tt> class to become an Active Record.
<com:TTextHighlighter Language="php" CssClass="source">
class UserRecord extends TActiveRecord
{
- public $username; //the column named "username" in the "users" table
- public $email;
-
- private static $_tablename='users'; //table name
-
- /**
- * @return TActiveRecord active record finder instance
- */
- public static function finder()
- {
- return self::getRecordFinder('UserRecord');
- }
+ public $username; //the column named "username" in the "users" table
+ public $email;
+
+ private static $_tablename='users'; //table name
+
+ /**
+ * @return TActiveRecord active record finder instance
+ */
+ public static function finder()
+ {
+ return self::getRecordFinder('UserRecord');
+ }
}
</com:TTextHighlighter>
</p>
<p>We also need to change the definition of the SqlMap XML configuration. We
- just need to change the value of <tt>resultClass</tt> attribute to <tt>UserRecord</tt>.
+ just need to change the value of <tt>resultClass</tt> attribute to <tt>UserRecord</tt>.
<com:TTextHighlighter Language="xml" CssClass="source">
<?xml version="1.0" encoding="utf-8" ?>
<sqlMapConfig>
@@ -243,8 +243,8 @@ class UserRecord extends TActiveRecord
<p>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.
-
+ returns Active Records instead, and we can take advantage of the Active Record methods.
+
<com:TTextHighlighter Language="php" CssClass="source">
//assume that $sqlmap is an TSqlMapGateway instance
$user = $sqlmap->queryForObject("SelectUsers");
@@ -256,10 +256,10 @@ $user->save(); //save it using Active Record
<h2>References</h2>
<ul>
- <li>Fowler et. al. <i>Patterns of Enterprise Application Architecture</i>,
- Addison Wesley, 2002.</li>
- <li>xxxx. <i>iBatis Data Mapper</i>,
- <a href="http://www.apache.org/ibatis">http://www.apache.org/ibatis</a>.</li>
+ <li>Fowler et. al. <i>Patterns of Enterprise Application Architecture</i>,
+ Addison Wesley, 2002.</li>
+ <li>xxxx. <i>iBatis Data Mapper</i>,
+ <a href="http://www.apache.org/ibatis">http://www.apache.org/ibatis</a>.</li>
</ul>
</com:TContent> \ No newline at end of file