diff options
Diffstat (limited to 'demos/quickstart/protected/pages/Database')
3 files changed, 147 insertions, 146 deletions
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 @@ <com:TContent ID="body" > <!-- $Id $ --> -<h1>Active Record</h1> -<p>Active Records are objects that wrap a row in a database table or view, +<h1 id="138046">Active Record</h1> +<p id="690478" class="block-content">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 @@ -13,21 +13,21 @@ 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, +<h2 id="138047">When to Use It</h2> +<p id="690479" class="block-content">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> - <p>However, as your business logic grows in complexity, you'll soon want + <p id="690480" class="block-content">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 + <p id="690481" class="block-content">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>. @@ -39,26 +39,28 @@ <img src=<%~ sqlmap_active_record.png %> alt="Active Records and SqlMap DataMapper" id="fig:diagram.png" class="figure"/> </p> - <p> + <p id="690482" class="block-content"> The Active Record class has methods that do the following: - <ul> + </p> + <ul id="u1" class="block-content"> <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> +<p id="p1" class="block-content"> 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 <a href="http://www.sqlite.org">SQLite</a> databases. Support for other databases can be provided when there are sufficient demand. -<h2>Defining an Active Record</h2> -<p>Let us +</p> +<h2 id="138048">Defining an Active Record</h2> +<p id="690483" class="block-content">Let us 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"> +<com:TTextHighlighter Language="sql" CssClass="source block-content" id="code_690147"> CREATE TABLE users ( username VARCHAR( 20 ) NOT NULL , @@ -67,8 +69,8 @@ CREATE TABLE users ); </com:TTextHighlighter> </p> -<p>Next we define our Active Record class that corresponds to the "users" table. -<com:TTextHighlighter Language="php" CssClass="source"> +<p id="690484" class="block-content">Next we define our Active Record class that corresponds to the "users" table. +<com:TTextHighlighter Language="php" CssClass="source block-content" id="code_690148"> class UserRecord extends TActiveRecord { public $username; //the column named "username" in the "users" table @@ -86,7 +88,7 @@ class UserRecord extends TActiveRecord } </com:TTextHighlighter> </p> -<p>Each property of the <tt>UserRecord</tt> class must correspond to a +<p id="690485" class="block-content">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 @@ -102,7 +104,7 @@ E.g. MySQL uses back-ticks, <tt>$_tablename = "`database1`.`table1`"</tt> 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"> +<com:TTextHighlighter Language="php" CssClass="source block-content" id="code_690149"> class UserRecord extends TActiveRecord { ... //existing definitions as above @@ -124,19 +126,19 @@ from views are read-only, calling the <tt>save()</tt> or <tt>delete()</tt> metho will raise an exception. </div> -<p> +<p id="690486" class="block-content"> 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> +<h2 id="138049">Setting up a database connection</h2> +<p id="690487" class="block-content"> 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"> +<com:TTextHighlighter Language="php" CssClass="source block-content" id="code_690150"> //create a connection and give it to the ActiveRecord manager. $dsn = 'pgsql:host=localhost;dbname=test'; //Postgres SQL $conn = new TDbConnection($dsn, 'dbuser','dbpass'); @@ -144,11 +146,11 @@ TActiveRecordManager::getInstance()->setDbConnection($conn); </com:TTextHighlighter> </p> -<p> +<p id="690488" class="block-content"> The default database connection can also be configured using a <tt><module></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"> +<com:TTextHighlighter Language="xml" CssClass="source block-content" id="code_690151"> <modules> <module class="System.Data.ActiveRecord.TActiveRecordConfig" EnableCache="true"> <database ConnectionString="pgsql:host=localhost;dbname=test" @@ -165,10 +167,10 @@ TActiveRecordManager::getInstance()->setDbConnection($conn); </div> </p> -<p>A <tt>ConnectionID</tt> property can be specified with value corresponding +<p id="690489" class="block-content">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>. -<com:TTextHighlighter Language="xml" CssClass="source"> +<com:TTextHighlighter Language="xml" CssClass="source block-content" id="code_690152"> <modules> <module class="System.Data.TDataSourceConfig" ID="db1"> <database ConnectionString="pgsql:host=localhost;dbname=test" @@ -184,16 +186,16 @@ TActiveRecordManager::getInstance()->setDbConnection($conn); </com:TTextHighlighter> </p> -<h2>Loading data from the database</h2> -<p> +<h2 id="138050">Loading data from the database</h2> +<p id="690490" class="block-content"> 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. -<com:TTextHighlighter Language="php" CssClass="source"> + <h3 id="138055"><tt>findByPk()</tt></h3> + <p id="690491" class="block-content">Finds one record using only the primary key or composite primary keys. +<com:TTextHighlighter Language="php" CssClass="source block-content" id="code_690153"> $finder = UserRecord::finder(); $user = $finder->findByPk($primaryKey); @@ -203,16 +205,16 @@ $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. + <h3 id="138056"><tt>findAllByPks()</tt></h3> + <p id="690492" class="block-content">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"> +<com:TTextHighlighter Language="php" CssClass="source block-content" id="code_690154"> $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"> +<com:TTextHighlighter Language="php" CssClass="source block-content" id="code_690155"> //when the table uses composite keys $record = $finder->findAllByPks(array($key1, $key2), array($key3, $key4), ...); @@ -223,10 +225,10 @@ $record = $finder->findAllByPks($keys); </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. -<com:TTextHighlighter Language="php" CssClass="source"> +<h3 id="138057"><tt>find()</tt></h3> +<p id="690493" class="block-content">Finds <b>one single record</b> that matches the criteria. The criteria + can be a partial SQL string or a <tt>TActiveRecordCriteria</tt> object.</p> +<com:TTextHighlighter Language="php" CssClass="source block-content" id="code_690156"> $finder = UserRecord::finder(); //:name and :pass are place holders for specific values of $name and $pass @@ -241,19 +243,18 @@ $finder->find('username = ? AND password = ?', $name, $pass); //$criteria is of TActiveRecordCriteria $finder->find($criteria); //the 2nd parameter for find() is ignored. </com:TTextHighlighter> -</p> -<p>The <tt>TActiveRecordCriteria</tt> class has the following properties: - <ul> +<p id="690494" class="block-content">The <tt>TActiveRecordCriteria</tt> class has the following properties: +</p> + <ul id="u2" class="block-content"> <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"> +<com:TTextHighlighter Language="php" CssClass="source block-content" id="code_690157"> $criteria = new TActiveRecordCriteria; $criteria->Condition = 'username = :name AND password = :pass'; $criteria->Parameters[':name'] = 'admin'; @@ -264,45 +265,45 @@ $criteria->Limit = 10; $criteria->Offset = 20; </com:TTextHighlighter> -<h3><tt>findAll()</tt></h3> -<p>Same as <tt>find()</tt> but returns an array of objects.</p> +<h3 id="138058"><tt>findAll()</tt></h3> +<p id="690495" class="block-content">Same as <tt>find()</tt> but returns an array of objects.</p> -<h3><tt>findBy*()</tt> and <tt>findAllBy*()</tt></h3> -<p>Dynamic find method using parts of method name as search criteria. +<h3 id="138059"><tt>findBy*()</tt> and <tt>findAllBy*()</tt></h3> +<p id="690496" class="block-content">Dynamic find method using parts of method name as search criteria. Method names starting with <tt>findBy</tt> return 1 record only. Method names starting with <tt>findAllBy</tt> return an array of records. The condition is taken as part of the method name after <tt>findBy</tt> or <tt>findAllBy</tt>. The following blocks of code are equivalent: -<com:TTextHighlighter Language="php" CssClass="source"> +<com:TTextHighlighter Language="php" CssClass="source block-content" id="code_690158"> $finder->findByName($name) $finder->find('Name = ?', $name); </com:TTextHighlighter> -<com:TTextHighlighter Language="php" CssClass="source"> +<com:TTextHighlighter Language="php" CssClass="source block-content" id="code_690159"> $finder->findByUsernameAndPassword($name,$pass); $finder->findBy_Username_And_Password($name,$pass); $finder->find('Username = ? AND Password = ?', $name, $pass); </com:TTextHighlighter> -<com:TTextHighlighter Language="php" CssClass="source"> +<com:TTextHighlighter Language="php" CssClass="source block-content" id="code_690160"> $finder->findAllByAge($age); $finder->findAll('Age = ?', $age); </com:TTextHighlighter> </p> -<h3><tt>findBySql()</tt></h3> -<p>Finds records using full SQL, returns corresponding array of record objects.</p> +<h3 id="138060"><tt>findBySql()</tt></h3> +<p id="690497" class="block-content">Finds records using full SQL, returns corresponding array of record objects.</p> -<h3><tt>count()</tt></h3> -<p>Find the number of matchings records.</p> +<h3 id="138061"><tt>count()</tt></h3> +<p id="690498" class="block-content">Find the number of matchings records.</p> -<h2>Inserting and updating records</h2> -<p> +<h2 id="138051">Inserting and updating records</h2> +<p id="690499" class="block-content"> Add a new record using TActiveRecord is very simple, just create a new Active Record object and call the <tt>save()</tt> method. E.g. -<com:TTextHighlighter Language="php" CssClass="source"> +<com:TTextHighlighter Language="php" CssClass="source block-content" id="code_690161"> $user1 = new UserRecord(); $user1->username = "admin" $user1->email = "admin@example.com"; @@ -320,19 +321,19 @@ defined with "autoincrement", the Active Record objects will be updated with the incremented values.</div> </p> -<p> +<p id="690500" class="block-content"> 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. -<com:TTextHighlighter Language="php" CssClass="source"> +<com:TTextHighlighter Language="php" CssClass="source block-content" id="code_690162"> $user = UserRecord::finder()->findByName('admin'); $user->email="test@example.com"; //change property $user->save(); //update it. </com:TTextHighlighter> </p> -<p> +<p id="690501" class="block-content"> 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> @@ -346,22 +347,22 @@ 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> +<h2 id="138052">Deleting existing records</h2> +<p id="690502" class="block-content"> 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"> +<com:TTextHighlighter Language="php" CssClass="source block-content" id="code_690163"> $finder->deleteByPk($primaryKey); //delete 1 record $finder->deleteByPk($key1,$key2,...); //delete multiple records $finder->deleteByPk(array($key1,$key2,...)); //delete multiple records </com:TTextHighlighter> </p> -<p> +<p id="690503" class="block-content"> For composite primary keys (determined automatically from the table definitions): -<com:TTextHighlighter Language="php" CssClass="source"> +<com:TTextHighlighter Language="php" CssClass="source block-content" id="code_690164"> $finder->deleteByPk(array($key1,$key2)); //delete 1 record //delete multiple records @@ -372,10 +373,10 @@ $finder->deleteByPk(array( array($key1,$key2), array($key3,$key4), .. )); </com:TTextHighlighter> </p> -<h2>Transactions</h2> -<p>All Active Record objects contains the property <tt>DbConnection</tt> +<h2 id="138053">Transactions</h2> +<p id="690504" class="block-content">All Active Record objects contains the property <tt>DbConnection</tt> that can be used to obtain a transaction object. -<com:TTextHighlighter Language="php" CssClass="source"> +<com:TTextHighlighter Language="php" CssClass="source block-content" id="code_690165"> $finder = UserRecord::finder(); $transaction = $finder->DbConnection->beginTransaction(); @@ -392,8 +393,8 @@ catch(Exception $e) // an exception is raised if a query fails } </com:TTextHighlighter> -<h2>References</h2> -<ul> +<h2 id="138054">References</h2> +<ul id="u3" class="block-content"> <li>Fowler et. al. <i>Patterns of Enterprise Application Architecture</i>, Addison Wesley, 2002.</li> </ul> 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 @@ <com:TContent ID="body" >
<!-- $Id $ -->
-<h1>Data Access Objects (DAO)</h1>
-<p>
+<h1 id="136039">Data Access Objects (DAO)</h1>
+<p id="680461" class="block-content">
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.
</p>
-<p>
+<p id="680462" class="block-content">
Since version 3.1, PRADO starts to provide a DAO that is a thin wrap around <a href="http://www.php.net/manual/en/ref.pdo.php">PHP Data Objects (PDO)</a>. 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.
</p>
<div class="note"><b class="tip">Note:</b>
Since the PRADO DAO is based on PDO, the PDO PHP extension needs to be installed. In addition, you need to install the corresponding PDO driver for the database to be used in your application. See more details in the <a href="http://www.php.net/manual/en/ref.pdo.php">PHP Manual</a>.
</div>
-<p>
+<p id="680463" class="block-content">
The PRADO DAO mainly consists of the following four classes (in contrast to PDO which uses only two classes, <tt>PDO</tt> and <tt>PDOStatement</tt>):
</p>
-<ul>
+<ul id="u1" class="block-content">
<li><tt>TDbConnection</tt> - represents a connection to a database.</li>
<li><tt>TDbCommand</tt> - represents an SQL statement to execute against a database.</li>
<li><tt>TDbDataReader</tt> - represents a forward-only stream of rows from a query result set.</li>
<li><tt>TDbTransaction</tt> - represents a DB transaction.</li>
</ul>
-<p>
+<p id="680464" class="block-content">
In the following, we introduce the usage of PRADO DAO in different scenarios.
</p>
-<h2>Establishing Database Connection</h2>
-<p>
+<h2 id="136040">Establishing Database Connection</h2>
+<p id="680465" class="block-content">
To establish a database connection, one creates a <tt>TDbConnection</tt> 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.
</p>
-<com:TTextHighlighter Language="php" CssClass="source">
+<com:TTextHighlighter Language="php" CssClass="source block-content" id="code_680140">
$connection=new TDbConnection($dsn,$username,$password);
// call setAttribute() to pass in additional connection parameters
// $connection->Persistent=true; // use persistent connection
@@ -35,51 +35,51 @@ $connection->Active=true; // connection is established ....
$connection->Active=false; // connection is closed
</com:TTextHighlighter>
-<p>
+<p id="680466" class="block-content">
Complete specification of DSN may be found in the <a href="http://www.php.net/manual/en/ref.pdo.php#pdo.drivers">PDO documentation</a>. Below is a list of commonly used DSN formats:
</p>
-<ul>
+<ul id="u2" class="block-content">
<li>MySQL - <tt>mysql:host=localhost;dbname=test</tt></li>
<li>SQLite - <tt>sqlite:/path/to/dbfile</tt></li>
<li>ODBC - <tt>odbc:SAMPLE</tt>
</ul>
-<p>
+<p id="680467" class="block-content">
In case any error occurs when establishing the connection (such as bad DSN or username/password), a <tt>TDbException</tt> will be raised.
</p>
-<h2>Executing SQL Statements</h2>
-<p>
+<h2 id="136041">Executing SQL Statements</h2>
+<p id="680468" class="block-content">
Once a database connection is established, SQL statements can be executed through <tt>TDbCommand</tt>. One creates a <tt>TDbCommand</tt> by calling <tt>TDbConnection.createCommand()</tt> with the specified SQL statement:
</p>
-<com:TTextHighlighter Language="php" CssClass="source">
+<com:TTextHighlighter Language="php" CssClass="source block-content" id="code_680141">
$command=$connection->createCommand($sqlStatement);
// if needed, the SQL statement may be updated as follows:
$command->Text=$newSqlStatement;
</com:TTextHighlighter>
-<p>
+<p id="680469" class="block-content">
An SQL statement is executed via <tt>TDbCommand</tt> in one of the following two ways:
</p>
-<ul>
+<ul id="u4" class="block-content">
<li><tt>execute()</tt> - performs a non-query SQL statement, such as <tt>INSERT</tt>, <tt>UPDATE</tt> and <tt>DELETE</tt>. If successful, it returns the number of rows that are affected by the execution.</li>
<li><tt>query()</tt> - performs an SQL statement that returns rows of data, such as <tt>SELECT</tt>. If successful, it returns a <tt>TDbDataReader</tt> instance from which one can fetch the resulting rows of data.
</li>
</ul>
-<com:TTextHighlighter Language="php" CssClass="source">
+<com:TTextHighlighter Language="php" CssClass="source block-content" id="code_680142">
$affectedRowCount=$command->execute(); // execute the non-query SQL
$dataReader=$command->query(); // execute a query SQL
$row=$command->queryRow(); // execute a query SQL and return the first row of result
$value=$command->queryScalar(); // execute a query SQL and return the first column value
</com:TTextHighlighter>
-<p>
+<p id="680470" class="block-content">
In case an error occurs during the execution of SQL statements, a <tt>TDbException</tt> will be raised.
</p>
-<h2>Fetching Query Results</h2>
-<p>
+<h2 id="136042">Fetching Query Results</h2>
+<p id="680471" class="block-content">
After <tt>TDbCommand.query()</tt> generates the <tt>TDbDataReader</tt> instance, one can retrieve rows of resulting data by calling <tt>TDbDataReader.read()</tt> repeatedly. One can also use <tt>TDbDataReader</tt> in PHP's <tt>foreach</tt> language construct to retrieve row by row.
</p>
-<com:TTextHighlighter Language="php" CssClass="source">
+<com:TTextHighlighter Language="php" CssClass="source block-content" id="code_680143">
// calling read() repeatedly until it returns false
while(($row=$dataReader->read())!==false) { ... }
// using foreach to traverse through every row of data
@@ -88,17 +88,17 @@ foreach($dataReader as $row) { ... } $rows=$dataReader->readAll();
</com:TTextHighlighter>
-<h2>Using Transactions</h2>
-<p>
+<h2 id="136043">Using Transactions</h2>
+<p id="680472" class="block-content">
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 <tt>TDbTransaction</tt> instance in PRADO, may be initiated in this case:
</p>
-<ul>
+<ul id="u5" class="block-content">
<li>Begin the transaction.</li>
<li>Execute queries one by one. Any updates to the database are not visible to the outside world.</li>
<li>Commit the transaction. Updates become visible if the transaction is successful.</li>
<li>If one of the queries fails, the entire transaction is rolled back.</li>
</ul>
-<com:TTextHighlighter Language="php" CssClass="source">
+<com:TTextHighlighter Language="php" CssClass="source block-content" id="code_680144">
$transaction=$connection->beginTransaction();
try
{
@@ -114,14 +114,14 @@ catch(Exception $e) // an exception is raised if a query fails will be raised </com:TTextHighlighter>
-<h2>Binding Parameters</h2>
-<p>
+<h2 id="136044">Binding Parameters</h2>
+<p id="680473" class="block-content">
To avoid <a href="http://en.wikipedia.org/wiki/SQL_injection">SQL injection attacks</a> 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.
</p>
-<p>
+<p id="680474" class="block-content">
The parameter placeholders can be either named (represented as unique tokens) or unnamed (represented as question marks). Call <tt>TDbCommand.bindParameter()</tt> or <tt>TDbCommand.bindValue()</tt> 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.
</p>
-<com:TTextHighlighter Language="php" CssClass="source">
+<com:TTextHighlighter Language="php" CssClass="source block-content" id="code_680145">
// an SQL with two placeholders ":username" and ":email"
$sql="INSERT INTO users(username, email) VALUES(:username,:email)";
$command=$connection->createCommand($sql);
@@ -135,19 +135,19 @@ $command->bindParameter(":username",$username2,PDO::PARAM_STR); $command->bindParameter(":email",$email2,PDO::PARAM_STR);
$command->execute();
</com:TTextHighlighter>
-<p>
+<p id="680475" class="block-content">
The methods <tt>bindParameter()</tt> and <tt>bindValue()</tt> 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.
</p>
-<p>
+<p id="680476" class="block-content">
For more details about binding parameters, see the <a href="http://www.php.net/manual/en/function.pdostatement-bindparam.php">relevant PHP documentation</a>.
</p>
-<h2>Binding Columns</h2>
-<p>
+<h2 id="136045">Binding Columns</h2>
+<p id="680477" class="block-content">
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.
</p>
-<com:TTextHighlighter Language="php" CssClass="source">
+<com:TTextHighlighter Language="php" CssClass="source block-content" id="code_680146">
$sql="SELECT username, email FROM users";
$dataReader=$connection->createCommand($sql)->query();
// bind the 1st column (username) with the $username variable
diff --git a/demos/quickstart/protected/pages/Database/SqlMap.page b/demos/quickstart/protected/pages/Database/SqlMap.page index 4b462168..bac1ba44 100644 --- a/demos/quickstart/protected/pages/Database/SqlMap.page +++ b/demos/quickstart/protected/pages/Database/SqlMap.page @@ -1,8 +1,8 @@ <com:TContent ID="body"> <!-- $Id $ --> -<h1>Data Mapper</h1> -<p>Data Mappers moves data between objects and a database while keeping them +<h1 id="140062">Data Mapper</h1> +<p id="700505" class="block-content">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 @@ -12,28 +12,28 @@ 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 +<p id="700506" class="block-content">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.) </p> -<h2>When to Use It</h2> -<p>The primary occasion for using Data Mapper is when you want the database schema +<h2 id="140063">When to Use It</h2> +<p id="700507" class="block-content">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. </p> -<p>This helps you in the code because you can understand and work with the domain objects +<p id="700508" class="block-content">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. </p> -<p>The price, of course, is the extra layer that you don't get with +<p id="700509" class="block-content">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> @@ -41,8 +41,8 @@ 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. +<h2 id="140064">SqlMap Data Mapper</h2> +<p id="700510" class="block-content">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 </p> -<p> +<p id="700511" class="block-content"> <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. @@ -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. </p> -<p>Execute the mapping by passing the parameter and the name you gave the statement or +<p id="700512" class="block-content">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. </p> -<p>In the case of an update, the number of rows affected is returned. In the case of a +<p id="700513" class="block-content">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. </p> -<h2>Setting up a database connection and initializing the SqlMap</h2> -<p> +<h2 id="140065">Setting up a database connection and initializing the SqlMap</h2> +<p id="700514" class="block-content"> 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"> +<com:TTextHighlighter Language="php" CssClass="source block-content" id="code_700166"> //create a connection and give it to the SqlMap manager. $dsn = 'pgsql:host=localhost;dbname=test'; //Postgres SQL $conn = new TDbConnection($dsn, 'dbuser','dbpass'); @@ -85,7 +85,7 @@ $sqlmap = $manager->getSqlMapGateway(); </com:TTextHighlighter> </p> -<p> +<p id="700515" class="block-content"> 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 @@ -93,11 +93,11 @@ $sqlmap = $manager->getSqlMapGateway(); of the SqlMap gateway interface (use this object to insert/delete/find records). </p> -<p> +<p id="700516" class="block-content"> SqlMap database connection can also be configured using a <tt><module></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"> +<com:TTextHighlighter Language="xml" CssClass="source block-content" id="code_700167"> <modules> <module id="my-sqlmap" class="System.Data.SqlMap.TSqlMapConfig" EnableCache="true" ConfigFile="my-sqlmap.xml" > @@ -108,7 +108,7 @@ $sqlmap = $manager->getSqlMapGateway(); </com:TTextHighlighter> </p> -<p> +<p id="700517" class="block-content"> 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). @@ -122,9 +122,9 @@ $sqlmap = $manager->getSqlMapGateway(); </div> </p> -<p>To obtain the SqlMap gateway interface from the <module> configuration, simply +<p id="700518" class="block-content">To obtain the SqlMap gateway interface from the <module> configuration, simply do, for example, -<com:TTextHighlighter Language="php" CssClass="source"> +<com:TTextHighlighter Language="php" CssClass="source block-content" id="code_700168"> class MyPage extends TPage { public function onLoad($param) @@ -137,11 +137,11 @@ class MyPage extends TPage </com:TTextHighlighter> </p> -<h2>A quick example</h2> -<p>Let us +<h2 id="140066">A quick example</h2> +<p id="700519" class="block-content">Let us 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"> +<com:TTextHighlighter Language="sql" CssClass="source block-content" id="code_700169"> CREATE TABLE users ( username VARCHAR( 20 ) NOT NULL , @@ -150,9 +150,9 @@ CREATE TABLE users ); </com:TTextHighlighter> </p> -<p>Next we define our plain <tt>User</tt> class as follows. Notice that +<p id="700520" class="block-content">Next we define our plain <tt>User</tt> class as follows. Notice that the <tt>User</tt> is very simple. -<com:TTextHighlighter Language="php" CssClass="source"> +<com:TTextHighlighter Language="php" CssClass="source block-content" id="code_700170"> class User { public $username; @@ -162,9 +162,9 @@ class User </p> </p> -<p>Next, we need to define a SqlMap XMl configuration file, lets name +<p id="700521" class="block-content">Next, we need to define a SqlMap XMl configuration file, lets name the file as <tt>my-sqlmap.xml</tt> -<com:TTextHighlighter Language="xml" CssClass="source"> +<com:TTextHighlighter Language="xml" CssClass="source block-content" id="code_700171"> <?xml version="1.0" encoding="utf-8" ?> <sqlMapConfig> <select id="SelectUsers" resultClass="User"> @@ -173,12 +173,12 @@ class User </sqlMapConfig> </com:TTextHighlighter> </p> -<p>The <select> tag returns defines an SQL statement. The <tt>id</tt> +<p id="700522" class="block-content">The <select> 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: -<com:TTextHighlighter Language="php" CssClass="source"> +<com:TTextHighlighter Language="php" CssClass="source block-content" id="code_700172"> //assume that $sqlmap is an TSqlMapGateway instance $userList = $sqlmap->queryForList("SelectUsers"); @@ -187,18 +187,18 @@ $user = $sqlmap->queryForObject("SelectUsers"); </com:TTextHighlighter> </p> -<p>The above example shows demonstrates only a fraction of the capabilities +<p id="700523" class="block-content">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>. </p> -<h2>Combining SqlMap with Active Records</h2> -<p>The above example may seem trival and it also seems that there is +<h2 id="140067">Combining SqlMap with Active Records</h2> +<p id="700524" class="block-content">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. </p> -<p> +<p id="700525" class="block-content"> 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> @@ -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. </p> -<p>Continuing with the previous example, we change the definition of the +<p id="700526" class="block-content">Continuing with the previous example, we change the definition of the <tt>User</tt> class to become an Active Record. -<com:TTextHighlighter Language="php" CssClass="source"> +<com:TTextHighlighter Language="php" CssClass="source block-content" id="code_700173"> class UserRecord extends TActiveRecord { public $username; //the column named "username" in the "users" table @@ -229,9 +229,9 @@ class UserRecord extends TActiveRecord </com:TTextHighlighter> </p> -<p>We also need to change the definition of the SqlMap XML configuration. We +<p id="700527" class="block-content">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>. -<com:TTextHighlighter Language="xml" CssClass="source"> +<com:TTextHighlighter Language="xml" CssClass="source block-content" id="code_700174"> <?xml version="1.0" encoding="utf-8" ?> <sqlMapConfig> <select id="SelectUsers" resultClass="UserRecord"> @@ -242,10 +242,10 @@ class UserRecord extends TActiveRecord </p> -<p>The PHP code for retrieving the users remains the same, but SqlMap +<p id="700528" class="block-content">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. -<com:TTextHighlighter Language="php" CssClass="source"> +<com:TTextHighlighter Language="php" CssClass="source block-content" id="code_700175"> //assume that $sqlmap is an TSqlMapGateway instance $user = $sqlmap->queryForObject("SelectUsers"); @@ -254,8 +254,8 @@ $user->save(); //save it using Active Record </com:TTextHighlighter> </p> -<h2>References</h2> -<ul> +<h2 id="140068">References</h2> +<ul id="u1" class="block-content"> <li>Fowler et. al. <i>Patterns of Enterprise Application Architecture</i>, Addison Wesley, 2002.</li> <li>xxxx. <i>iBatis Data Mapper</i>, |