summaryrefslogtreecommitdiff
path: root/demos/quickstart/protected/pages/Database/ActiveRecord.page
blob: 406a39fe1ce55fd74450eb98035a6aeac4f270a4 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
<com:TContent ID="body" >
<!-- $Id $ -->
<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
    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.
</div>

<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 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 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>. 
        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 id="690482" class="block-content">
        The Active Record class has methods that do the following:
	</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 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.
</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 block-content" id="code_690147">
CREATE TABLE users
(
    username VARCHAR( 20 ) NOT NULL ,
    email VARCHAR( 200 ) ,
    PRIMARY KEY ( username )
);
</com:TTextHighlighter>
</p>
<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
    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 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
    specify the table name that corresponds to your Active Record class.
</p>

<div class="note"><b class="note">Note:</b>
You may need to quote (specific to your database) the value of the <tt>$_tablename</tt>. 
E.g. MySQL uses back-ticks, <tt>$_tablename = "`database1`.`table1`"</tt>
</div>

<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:
<com:TTextHighlighter Language="php" CssClass="source block-content" id="code_690149">
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;
    }
}
</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 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 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 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');
TActiveRecordManager::getInstance()->setDbConnection($conn);
</com:TTextHighlighter> 
</p>

<p id="690488" class="block-content">
    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 block-content" id="code_690151">
<modules>
  <module class="System.Data.ActiveRecord.TActiveRecordConfig" EnableCache="true">
    <database ConnectionString="pgsql:host=localhost;dbname=test"
        Username="dbuser" Password="dbpass" />
  </module>
</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.
</div>
</p>

<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 block-content" id="code_690152">
<modules>
  <module class="System.Data.TDataSourceConfig" ID="db1">
    <database ConnectionString="pgsql:host=localhost;dbname=test"
        Username="dbuser" Password="dbpass" />
  </module>

  <module class="System.Data.ActiveRecord.TActiveRecordConfig" 
        ConnectionID="db1" EnableCache="true"  />

  <module class="System.Data.SqlMap.TSqlMapConfig"
        ConnectionID="db1"  ... />
</modules>  
</com:TTextHighlighter>     
</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 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);

//when the table uses composite keys
$record = $finder->findByPk($key1, $key2, ...);
$record = $finder->findByPk(array($key1, $key2,...));
</com:TTextHighlighter>
</p>

    <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 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 block-content" id="code_690155">
//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 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
$finder->find('username = :name AND password = :pass',
                          array(':name'=>$name, ':pass'=>$pass));

//using position place holders
$finder->find('username = ? AND password = ?', array($name, $pass));
//same as above
$finder->find('username = ? AND password = ?', $name, $pass);

//$criteria is of TActiveRecordCriteria
$finder->find($criteria); //the 2nd parameter for find() is ignored.
</com:TTextHighlighter>

<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>OrdersBy</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>

<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';
$criteria->Parameters[':pass'] = 'prado';
$criteria->OrdersBy['level'] = 'desc';
$criteria->OrdersBy['name'] = 'asc';
$criteria->Limit = 10;
$criteria->Offset = 20;
</com:TTextHighlighter>

<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 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 block-content" id="code_690158">
$finder->findByName($name)
$finder->find('Name = ?', $name);
</com:TTextHighlighter>

<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 block-content" id="code_690160">
$finder->findAllByAge($age);
$finder->findAll('Age = ?', $age);
</com:TTextHighlighter>
</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 id="138061"><tt>count()</tt></h3>
<p id="690498" class="block-content">Find the number of matchings records.</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 block-content" id="code_690161">
$user1 = new UserRecord();
$user1->username = "admin"
$user1->email = "admin@example.com";
$user1->save(); //insert a new record

$data = array('username'=>'admin', 'email'=>'admin@example.com');
$user2 = new UserRecord($data); //create by passing some existing data
$user2->save(); //insert a new record
</com:TTextHighlighter>
<div class="tip"><b class="note">Tip:</b>
The objects are update with the primary key of those the tables that contains 
definitions that automatically creates a primary key for the newly insert records.
For example, if you insert a new record into a MySQL table that has columns
defined with "autoincrement", the Active Record objects will be updated with the new 
incremented values.</div>
</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 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 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>
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 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 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 id="690503" class="block-content">
For composite primary keys (determined automatically from the table definitions):
<com:TTextHighlighter Language="php" CssClass="source block-content" id="code_690164">
$finder->deleteByPk(array($key1,$key2)); //delete 1 record

//delete multiple records
$finder->deleteByPk(array($key1,$key2), array($key3,$key4),...);

//delete multiple records
$finder->deleteByPk(array( array($key1,$key2), array($key3,$key4), .. ));
</com:TTextHighlighter>
</p>

<h3 id="138052a"><tt>deleteAll()</tt> and <tt>deleteBy*()</tt></h3>
<p id="690502a" class="block-content">
To delete by a criteria, use <tt>deleteAll($criteria)</tt> and <tt>deleteBy*()</tt>
with similar synatx to <tt>findAll($criteria)</tt> and <tt>findAllBy*()</tt> as
described above.
</p>
<com:TTextHighlighter Language="php" CssClass="source block-content" id="code_690163a">
//delete all records with matching Name
$finder->deleteAll('Name = ?', $name);
$finder->deleteByName($name);

//delete by username and password
$finder->deleteBy_Username_And_Password($name,$pass);
</com:TTextHighlighter>
</p>



<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 block-content" id="code_690165">
$finder = UserRecord::finder();

$transaction = $finder->DbConnection->beginTransaction();
try
{
    $user = $finder->findByPk('admin');
    $user->email = 'test@example.com'; //alter the $user object
    $user->save();
    $transaction->commit();
}
catch(Exception $e) // an exception is raised if a query fails
{
    $transaction->rollBack();
}
</com:TTextHighlighter>

<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>

<div class="last-modified">$Id$</div></com:TContent>