| [ Index ] |
|
Code source de Mantis 1.1.0rc3 |
1 <html> 2 <style> 3 pre { 4 background-color: #eee; 5 padding: 0.75em 1.5em; 6 font-size: 12px; 7 border: 1px solid #ddd; 8 } 9 10 li,p { 11 font-family: Arial, Helvetica, sans-serif ; 12 } 13 </style> 14 <title>ADOdb Active Record</title> 15 <body> 16 <h1>ADOdb Active Record</h1> 17 <p> (c) 2000-2007 John Lim (jlim#natsoft.com)</p> 18 <p><font size="1">This software is dual licensed using BSD-Style and LGPL. This 19 means you can use it in compiled proprietary and commercial products.</font></p> 20 <p><hr> 21 <ol> 22 23 <h3><li>Introduction</h3> 24 <p> 25 ADOdb_Active_Record is an Object Relation Mapping (ORM) implementation using PHP. In an ORM system, the tables and rows of the database are abstracted into native PHP objects. This allows the programmer to focus more on manipulating the data and less on writing SQL queries. 26 <p> 27 This implementation differs from Zend Framework's implementation in the following ways: 28 <ul> 29 <li>Works with PHP4 and PHP5 and provides equivalent functionality in both versions of PHP.<p> 30 <li>ADOdb_Active_Record works when you are connected to multiple databases. Zend's only works when connected to a default database.<p> 31 <li>Support for $ADODB_ASSOC_CASE. The field names are upper-cased, lower-cased or left in natural case depending on this setting.<p> 32 <li>No field name conversion to camel-caps style, unlike Zend's implementation which will convert field names such as 'first_name' to 'firstName'.<p> 33 <li>NewADOConnection::GetActiveRecords() and ADOConnection::GetActiveRecordsClass() functions in adodb.inc.php.<p> 34 <li>Caching of table metadata so it is only queried once per table, no matter how many Active Records are created.<p> 35 <li>The additional functionality is described <a href=#additional>below</a>. 36 </ul> 37 <P> 38 ADOdb_Active_Record is designed upon the principles of the "ActiveRecord" design pattern, which was first described by Martin Fowler. The ActiveRecord pattern has been implemented in many forms across the spectrum of programming languages. ADOdb_Active_Record attempts to represent the database as closely to native PHP objects as possible. 39 <p> 40 ADOdb_Active_Record maps a database table to a PHP class, and each instance of that class represents a table row. Relations between tables can also be defined, allowing the ADOdb_Active_Record objects to be nested. 41 <p> 42 43 <h3><li>Setting the Database Connection</h3> 44 <p> 45 The first step to using ADOdb_Active_Record is to set the default connection that an ADOdb_Active_Record objects will use to connect to a database. 46 47 <pre> 48 require_once('adodb/adodb-active-record.inc.php'); 49 50 $db = NewADOConnection('mysql://root:pwd@localhost/dbname'); 51 ADOdb_Active_Record::SetDatabaseAdapter($db); 52 </pre> 53 54 <h3><li>Table Rows as Objects</h3> 55 <p> 56 First, let's create a temporary table in our MySQL database that we can use for demonstrative purposes throughout the rest of this tutorial. We can do this by sending a CREATE query: 57 58 <pre> 59 $db->Execute("CREATE TEMPORARY TABLE `persons` ( 60 `id` int(10) unsigned NOT NULL auto_increment, 61 `name_first` varchar(100) NOT NULL default '', 62 `name_last` varchar(100) NOT NULL default '', 63 `favorite_color` varchar(100) NOT NULL default '', 64 PRIMARY KEY (`id`) 65 ) ENGINE=MyISAM; 66 "); 67 </pre> 68 <p> 69 ADOdb_Active_Record's are object representations of table rows. Each table in the database is represented by a class in PHP. To begin working with a table as a ADOdb_Active_Record, a class that extends ADOdb_Active_Records needs to be created for it. 70 71 <pre> 72 class Person extends ADOdb_Active_Record{} 73 $person = new Person(); 74 </pre> 75 76 <p> 77 In the above example, a new ADOdb_Active_Record object $person was created to access the "persons" table. Zend_Db_DataObject takes the name of the class, pluralizes it (according to American English rules), and assumes that this is the name of the table in the database. 78 <p> 79 This kind of behavior is typical of ADOdb_Active_Record. It will assume as much as possible by convention rather than explicit configuration. In situations where it isn't possible to use the conventions that ADOdb_Active_Record expects, options can be overridden as we'll see later. 80 81 <h3><li>Table Columns as Object Properties</h3> 82 <p> 83 When the $person object was instantiated, ADOdb_Active_Record read the table metadata from the database itself, and then exposed the table's columns (fields) as object properties. 84 <p> 85 Our "persons" table has three fields: "name_first", "name_last", and "favorite_color". Each of these fields is now a property of the $person object. To see all these properties, use the ADOdb_Active_Record::getAttributeNames() method: 86 <pre> 87 var_dump($person->getAttributeNames()); 88 89 /** 90 * Outputs the following: 91 * array(4) { 92 * [0]=> 93 * string(2) "id" 94 * [1]=> 95 * string(9) "name_first" 96 * [2]=> 97 * string(8) "name_last" 98 * [3]=> 99 * string(13) "favorite_color" 100 * } 101 */ 102 </pre> 103 <p> 104 One big difference between ADOdb and Zend's implementation is we do not automatically convert to camelCaps style. 105 <p> 106 <h3><li>Inserting and Updating a Record</h3><p> 107 108 An ADOdb_Active_Record object is a representation of a single table row. However, when our $person object is instantiated, it does not reference any particular row. It is a blank record that does not yet exist in the database. An ADOdb_Active_Record object is considered blank when its primary key is NULL. The primary key in our persons table is "id". 109 <p> 110 To insert a new record into the database, change the object's properties and then call the ADOdb_Active_Record::save() method: 111 <pre> 112 $person = new Person(); 113 $person->nameFirst = 'Andi'; 114 $person->nameLast = 'Gutmans'; 115 $person->save(); 116 </pre> 117 <p> 118 Oh, no! The above code snippet does not insert a new record into the database. Instead, outputs an error: 119 <pre> 120 1048: Column 'name_first' cannot be null 121 </pre> 122 <p> 123 This error occurred because MySQL rejected the INSERT query that was generated by ADOdb_Active_Record. If exceptions are enabled in ADOdb and you are using PHP5, an error will be thrown. In the definition of our table, we specified all of the fields as NOT NULL; i.e., they must contain a value. 124 <p> 125 ADOdb_Active_Records are bound by the same contraints as the database tables they represent. If the field in the database cannot be NULL, the corresponding property in the ADOdb_Active_Record also cannot be NULL. In the example above, we failed to set the property $person->favoriteColor, which caused the INSERT to be rejected by MySQL. 126 <p> 127 To insert a new ADOdb_Active_Record in the database, populate all of ADOdb_Active_Record's properties so that they satisfy the constraints of the database table, and then call the save() method: 128 <pre> 129 /** 130 * Calling the save() method will successfully INSERT 131 * this $person into the database table. 132 */ 133 $person = new Person(); 134 $person->name_first = 'Andi'; 135 $person->name_last = 'Gutmans'; 136 $person->favorite_color = 'blue'; 137 $person->save(); 138 </pre> 139 <p> 140 Once this $person has been INSERTed into the database by calling save(), the primary key can now be read as a property. Since this is the first row inserted into our temporary table, its "id" will be 1: 141 <pre> 142 var_dump($person->id); 143 144 /** 145 * Outputs the following: 146 * string(1) 147 */ 148 </pre> 149 <p> 150 From this point on, updating it is simply a matter of changing the object's properties and calling the save() method again: 151 152 <pre> 153 $person->favorite_color = 'red'; 154 $person->save(); 155 </pre> 156 <p> 157 The code snippet above will change the favorite color to red, and then UPDATE the record in the database. 158 159 <a name=additional> 160 <h2>ADOdb Specific Functionality</h2> 161 <h3><li>Setting the Table Name</h3> 162 <p>The default behaviour on creating an ADOdb_Active_Record is to "pluralize" the class name and 163 use that as the table name. Often, this is not the case. For example, the Person class could be reading 164 from the "People" table. 165 <p>We provide two ways to define your own table: 166 <p>1. Use a constructor parameter to override the default table naming behaviour. 167 <pre> 168 class Person extends ADOdb_Active_Record{} 169 $person = new Person('People'); 170 </pre> 171 <p>2. Define it in a class declaration: 172 <pre> 173 class Person extends ADOdb_Active_Record 174 { 175 var $_table = 'People'; 176 } 177 $person = new Person(); 178 </pre> 179 180 <h3><li>$ADODB_ASSOC_CASE</h3> 181 <p>This allows you to control the case of field names and properties. For example, all field names in Oracle are upper-case by default. So you 182 can force field names to be lowercase using $ADODB_ASSOC_CASE. Legal values are as follows: 183 <pre> 184 0: lower-case 185 1: upper-case 186 2: native-case 187 </pre> 188 <p>So to force all Oracle field names to lower-case, use 189 <pre> 190 $ADODB_ASSOC_CASE = 0; 191 $person = new Person('People'); 192 $person->name = 'Lily'; 193 $ADODB_ASSOC_CASE = 2; 194 $person2 = new Person('People'); 195 $person2->NAME = 'Lily'; 196 </pre> 197 198 <p>Also see <a href=http://phplens.com/adodb/reference.constants.adodb_assoc_case.html>$ADODB_ASSOC_CASE</a>. 199 200 <h3><li>ADOdb_Active_Record::Save()</h3> 201 <p> 202 Saves a record by executing an INSERT or UPDATE SQL statement as appropriate. 203 <p>Returns false on unsuccessful INSERT, true if successsful INSERT. 204 <p>Returns 0 on failed UPDATE, and 1 on UPDATE if data has changed, and -1 if no data was changed, so no UPDATE statement was executed. 205 206 <h3><li>ADOdb_Active_Record::Replace()</h3> 207 <p> 208 ADOdb supports replace functionality, whereby the record is inserted if it does not exists, or updated otherwise. 209 <pre> 210 $rec = new ADOdb_Active_Record("product"); 211 $rec->name = 'John'; 212 $rec->tel_no = '34111145'; 213 $ok = $rec->replace(); // 0=failure, 1=update, 2=insert 214 </pre> 215 216 217 <h3><li>ADOdb_Active_Record::Load($where)</h3> 218 <p>Sometimes, we want to load a single record into an Active Record. We can do so using: 219 <pre> 220 $person->load("id=3"); 221 222 // or using bind parameters 223 224 $person->load("id=?", array(3)); 225 </pre> 226 <p>Returns false if an error occurs. 227 228 <h3><li>ADOdb_Active_Record::Find($whereOrderBy, $bindarr=false, $pkeyArr=false)</h3> 229 <p>We want to retrieve an array of active records based on some search criteria. For example: 230 <pre> 231 class Person extends ADOdb_Active_Record { 232 var $_table = 'people'; 233 } 234 235 $person = new Person(); 236 $peopleArray =& $person->Find("name like ? order by age", array('Sm%')); 237 </pre> 238 239 <h3><li>Error Handling and Debugging</h3> 240 <p> 241 In PHP5, if adodb-exceptions.inc.php is included, then errors are thrown. Otherwise errors are handled by returning a value. False by default means an error has occurred. You can get the last error message using the ErrorMsg() function. 242 <p> 243 To check for errors in ADOdb_Active_Record, do not poll ErrorMsg() as the last error message will always be returned, even if it occurred several operations ago. Do this instead: 244 <pre> 245 # right! 246 $ok = $rec->Save(); 247 if (!$ok) $err = $rec->ErrorMsg(); 248 249 # wrong :( 250 $rec->Save(); 251 if ($rec->ErrorMsg()) echo "Wrong way to detect error"; 252 </pre> 253 <p>The ADOConnection::Debug property is obeyed. So 254 if $db->debug is enabled, then ADOdb_Active_Record errors are also outputted to standard output and written to the browser. 255 256 <h3><li>ADOdb_Active_Record::Set()</h3> 257 <p>You can convert an array to an ADOdb_Active_Record using Set(). The array must be numerically indexed, and have all fields of the table defined in the array. The elements of the array must be in the table's natural order too. 258 <pre> 259 $row = $db->GetRow("select * from tablex where id=$id"); 260 261 # PHP4 or PHP5 without enabling exceptions 262 $obj =& new ADOdb_Active_Record('Products'); 263 if ($obj->ErrorMsg()){ 264 echo $obj->ErrorMsg(); 265 } else { 266 $obj->Set($row); 267 } 268 269 # in PHP5, with exceptions enabled: 270 271 include('adodb-exceptions.inc.php'); 272 try { 273 $obj =& new ADOdb_Active_Record('Products'); 274 $obj->Set($row); 275 } catch(exceptions $e) { 276 echo $e->getMessage(); 277 } 278 </pre> 279 <p> 280 <h3><li>Primary Keys</h3> 281 <p> 282 ADOdb_Active_Record does not require the table to have a primary key. You can insert records for such a table, but you will not be able to update nor delete. 283 <p>Sometimes you are retrieving data from a view or table that has no primary key, but has a unique index. You can dynamically set the primary key of a table through the constructor, or using ADOdb_Active_Record::SetPrimaryKeys(): 284 <pre> 285 $pkeys = array('category','prodcode'); 286 287 // set primary key using constructor 288 $rec = new ADOdb_Active_Record('Products', $pkeys); 289 290 // or use method 291 $rec->SetPrimaryKeys($pkeys); 292 </pre> 293 294 295 <h3><li>Retrieval of Auto-incrementing ID</h3> 296 When creating a new record, the retrieval of the last auto-incrementing ID is not reliable for databases that do not support the Insert_ID() function call (check $connection->hasInsertID). In this case we perform a <b>SELECT MAX($primarykey) FROM $table</b>, which will not work reliably in a multi-user environment. You can override the ADOdb_Active_Record::LastInsertID() function in this case. 297 298 <h3><li>Dealing with Multiple Databases</h3> 299 <p> 300 Sometimes we want to load data from one database and insert it into another using ActiveRecords. This can be done using the optional parameter of the ADOdb_Active_Record constructor. In the following example, we read data from db.table1 and store it in db2.table2: 301 <pre> 302 $db = NewADOConnection(...); 303 $db2 = NewADOConnection(...); 304 305 ADOdb_Active_Record::SetDatabaseAdapter($db2); 306 307 $activeRecs = $db->GetActiveRecords('table1'); 308 309 foreach($activeRecs as $rec) { 310 $rec2 = new ADOdb_Active_Record('table2',$db2); 311 $rec2->id = $rec->id; 312 $rec2->name = $rec->name; 313 314 $rec2->Save(); 315 } 316 </pre> 317 <p> 318 If you have to pass in a primary key called "id" and the 2nd db connection in the constructor, you can do so too: 319 <pre> 320 $rec = new ADOdb_Active_Record("table1",array("id"),$db2); 321 </pre> 322 323 <h3><li>$ADODB_ACTIVE_CACHESECS</h3> 324 <p>You can cache the table metadata (field names, types, and other info such primary keys) in $ADODB_CACHE_DIR (which defaults to /tmp) by setting 325 the global variable $ADODB_ACTIVE_CACHESECS to a value greater than 0. This will be the number of seconds to cache. 326 You should set this to a value of 30 seconds or greater for optimal performance. 327 328 <h3><li>Active Record Considered Bad?</h3> 329 <p>Although the Active Record concept is useful, you have to be aware of some pitfalls when using Active Record. The level of granularity of Active Record is individual records. It encourages code like the following, used to increase the price of all furniture products by 10%: 330 <pre> 331 $recs = $db->GetActiveRecords("Products","category='Furniture'"); 332 foreach($recs as $rec) { 333 $rec->price *= 1.1; // increase price by 10% for all Furniture products 334 $rec->save(); 335 } 336 </pre> 337 Of course a SELECT statement is superior because it's simpler and much more efficient (probably by a factor of x10 or more): 338 <pre> 339 $db->Execute("update Products set price = price * 1.1 where category='Furniture'"); 340 </pre> 341 <p>Another issue is performance. For performance sensitive code, using direct SQL will always be faster than using Active Records due to overhead and the fact that all fields in a row are retrieved (rather than only the subset you need) whenever an Active Record is loaded. 342 343 <h3><li>Transactions</h3> 344 <p> 345 The default transaction mode in ADOdb is autocommit. So that is the default with active record too. 346 The general rules for managing transactions still apply. Active Record to the database is a set of insert/update/delete statements, and the db has no knowledge of active records. 347 <p> 348 Smart transactions, that does an auto-rollback if an error occurs, is still the best method to multiple activities (inserts/updates/deletes) that need to be treated as a single transaction: 349 <pre> 350 $conn->StartTrans(); 351 $parent->save(); 352 $child->save(); 353 $conn->CompleteTrans(); 354 </pre> 355 356 <h2>ADOConnection Supplement</h2> 357 358 <h3><li>ADOConnection::GetActiveRecords()</h3> 359 <p> 360 This allows you to retrieve an array of ADOdb_Active_Records. Returns false if an error occurs. 361 <pre> 362 $table = 'products'; 363 $whereOrderBy = "name LIKE 'A%' ORDER BY Name"; 364 $activeRecArr = $db->GetActiveRecords($table, $whereOrderBy); 365 foreach($activeRecArr as $rec) { 366 $rec->id = rand(); 367 $rec->save(); 368 } 369 </pre> 370 <p> 371 And to retrieve all records ordered by specific fields: 372 <pre> 373 $whereOrderBy = "1=1 ORDER BY Name"; 374 $activeRecArr = $db->ADOdb_Active_Records($table); 375 </pre> 376 <p> 377 To use bind variables (assuming ? is the place-holder for your database): 378 <pre> 379 $activeRecArr = $db->GetActiveRecords($tableName, 'name LIKE ?', 380 array('A%')); 381 </pre> 382 <p>You can also define the primary keys of the table by passing an array of field names: 383 <pre> 384 $activeRecArr = $db->GetActiveRecords($tableName, 'name LIKE ?', 385 array('A%'), array('id')); 386 </pre> 387 388 <h3><li>ADOConnection::GetActiveRecordsClass()</h3> 389 <p> 390 This allows you to retrieve an array of objects derived from ADOdb_Active_Records. Returns false if an error occurs. 391 <pre> 392 class Product extends ADOdb_Active_Records{}; 393 $table = 'products'; 394 $whereOrderBy = "name LIKE 'A%' ORDER BY Name"; 395 $activeRecArr = $db->GetActiveRecordsClass('Product',$table, $whereOrderBy); 396 397 # the objects in $activeRecArr are of class 'Product' 398 foreach($activeRecArr as $rec) { 399 $rec->id = rand(); 400 $rec->save(); 401 } 402 </pre> 403 <p> 404 To use bind variables (assuming ? is the place-holder for your database): 405 <pre> 406 $activeRecArr = $db->GetActiveRecordsClass($className,$tableName, 'name LIKE ?', 407 array('A%')); 408 </pre> 409 <p>You can also define the primary keys of the table by passing an array of field names: 410 <pre> 411 $activeRecArr = $db->GetActiveRecordsClass($className,$tableName, 'name LIKE ?', 412 array('A%'), array('id')); 413 </pre> 414 415 </ol> 416 417 <h3><li>ADOConnection::ErrorMsg()</h3> 418 <p>Returns last error message. 419 <h3><li>ADOConnection::ErrorNo()</h3> 420 <p>Returns last error number. 421 <h2>Code Sample</h2> 422 <p>The following works with PHP4 and PHP5 423 <pre> 424 include ('../adodb.inc.php'); 425 include ('../adodb-active-record.inc.php'); 426 427 // uncomment the following if you want to test exceptions 428 #if (PHP_VERSION >= 5) include('../adodb-exceptions.inc.php'); 429 430 $db = NewADOConnection('mysql://root@localhost/northwind'); 431 $db->debug=1; 432 ADOdb_Active_Record::SetDatabaseAdapter($db); 433 434 $db->Execute("CREATE TEMPORARY TABLE `persons` ( 435 `id` int(10) unsigned NOT NULL auto_increment, 436 `name_first` varchar(100) NOT NULL default '', 437 `name_last` varchar(100) NOT NULL default '', 438 `favorite_color` varchar(100) NOT NULL default '', 439 PRIMARY KEY (`id`) 440 ) ENGINE=MyISAM; 441 "); 442 443 class Person extends ADOdb_Active_Record{} 444 $person = new Person(); 445 446 echo "<p>Output of getAttributeNames: "; 447 var_dump($person->getAttributeNames()); 448 449 /** 450 * Outputs the following: 451 * array(4) { 452 * [0]=> 453 * string(2) "id" 454 * [1]=> 455 * string(9) "name_first" 456 * [2]=> 457 * string(8) "name_last" 458 * [3]=> 459 * string(13) "favorite_color" 460 * } 461 */ 462 463 $person = new Person(); 464 $person->nameFirst = 'Andi'; 465 $person->nameLast = 'Gutmans'; 466 $person->save(); // this save() will fail on INSERT as favorite_color is a must fill... 467 468 469 $person = new Person(); 470 $person->name_first = 'Andi'; 471 $person->name_last = 'Gutmans'; 472 $person->favorite_color = 'blue'; 473 $person->save(); // this save will perform an INSERT successfully 474 475 echo "<p>The Insert ID generated:"; print_r($person->id); 476 477 $person->favorite_color = 'red'; 478 $person->save(); // this save() will perform an UPDATE 479 480 $person = new Person(); 481 $person->name_first = 'John'; 482 $person->name_last = 'Lim'; 483 $person->favorite_color = 'lavender'; 484 $person->save(); // this save will perform an INSERT successfully 485 486 // load record where id=2 into a new ADOdb_Active_Record 487 $person2 = new Person(); 488 $person2->Load('id=2'); 489 var_dump($person2); 490 491 // retrieve an array of records 492 $activeArr = $db->GetActiveRecordsClass($class = "Person",$table = "persons","id=".$db->Param(0),array(2)); 493 $person2 =& $activeArr[0]; 494 echo "<p>Name first (should be John): ",$person->name_first, "<br>Class = ",get_class($person2); 495 </pre> 496 497 <h3>Todo (Code Contributions welcome)</h3> 498 <p>Check _original and current field values before update, only update changes. Also if the primary key value is changed, then on update, we should save and use the original primary key values in the WHERE clause! 499 <p>Handle 1-to-many relationships. 500 <p>PHP5 specific: Make GetActiveRecords*() return an Iterator. 501 <p>PHP5 specific: Change PHP5 implementation of Active Record to use __get() and __set() for better performance. 502 503 <h3> Change Log</h3> 504 <p>0.08 505 Added support for assoc arrays in Set(). 506 507 <p>0.07 508 <p>$ADODB_ASSOC_CASE=2 did not work properly. Fixed. 509 <p>Added === check in ADODB_SetDatabaseAdapter for $db, adodb-active-record.inc.php. Thx Christian Affolter. 510 511 <p>0.06 512 <p>Added ErrorNo(). 513 <p>Fixed php 5.2.0 compat issues. 514 515 <p>0.05 516 <p>If inserting a record and the value of a primary key field is null, then we do not insert that field in as 517 we assume it is an auto-increment field. Needed by mssql. 518 519 <p>0.04 5 June 2006 <br> 520 <p>Added support for declaring table name in $_table in class declaration. Thx Bill Dueber for idea. 521 <p>Added find($where,$bindarr=false) method to retrieve an array of active record objects. 522 523 <p>0.03 <br> 524 - Now we only update fields that have changed, using $this->_original.<br> 525 - We do not include auto_increment fields in replace(). Thx Travis Cline<br> 526 - Added ADODB_ACTIVE_CACHESECS.<br> 527 528 <p>0.02 <br> 529 - Much better error handling. ErrorMsg() implemented. Throw implemented if adodb-exceptions.inc.php detected.<br> 530 - You can now define the primary keys of the view or table you are accessing manually.<br> 531 - The Active Record allows you to create an object which does not have a primary key. You can INSERT but not UPDATE in this case. 532 - Set() documented.<br> 533 - Fixed _pluralize bug with y suffix. 534 535 <p> 536 0.01 6 Mar 2006<br> 537 - Fixed handling of nulls when saving (it didn't save nulls, saved them as '').<br> 538 - Better error handling messages.<br> 539 - Factored out a new method GetPrimaryKeys().<br> 540 <p> 541 0.00 5 Mar 2006<br> 542 1st release 543 </body> 544 </html>
titre
Description
Corps
titre
Description
Corps
titre
Description
Corps
titre
Corps
| Généré le : Thu Nov 29 09:42:17 2007 | par Balluche grâce à PHPXref 0.7 |
|
|