From 2c221ea67d0512961beea8fbcb30b23865c16bb0 Mon Sep 17 00:00:00 2001 From: wei <> Date: Wed, 20 Dec 2006 03:15:04 +0000 Subject: Add quickstart docs for Active Record and SqlMap --- .../protected/pages/Database/ActiveRecord.page | 360 +++++++++++++++++++++ 1 file changed, 360 insertions(+) create mode 100644 demos/quickstart/protected/pages/Database/ActiveRecord.page (limited to 'demos/quickstart/protected/pages/Database/ActiveRecord.page') diff --git a/demos/quickstart/protected/pages/Database/ActiveRecord.page b/demos/quickstart/protected/pages/Database/ActiveRecord.page new file mode 100644 index 00000000..017b8d45 --- /dev/null +++ b/demos/quickstart/protected/pages/Database/ActiveRecord.page @@ -0,0 +1,360 @@ + + +

Active Record

+

Active Records are objects that wrap a row in a database table or view, + encapsulates the database access and adds domain logic on that data. + The basics of an Active Record is a business object class, e.g., a + Products class, that match very closely the record structure + of an underlying database table. Each Active Record will be responsible for + saving and loading data to and from the database.

+
Info: + 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. +
+ +

When to Use It

+

Active Record is a good choice for domain logic that isn't too complex, + such as creates, reads, updates, and deletes. Derivations and validations + based on a single record work well in this structure. Active Record has the + primary advantage of simplicity. It's easy to build + Active Records, and they are easy to understand.

+ +

However, as your business logic grows in complexity, you'll soon want + to use your object's direct relationships, collections, inheritance, and so + forth. These don't map easily onto Active Record, and adding them piecemeal + gets very messy. + Another argument against Active Record is the fact that it couples the object + design to the database design. This makes it more difficult to refactor as a project goes forward.

+ +

The alternative is to use a Data Mapper that separates the roles of the + business object and how these objects are stored. + Prado provides a complimentary choice between Active Record and + SqlMap Data Mapper. + 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 SqlMap is illustrated in the + following diagram. More details regarding the SqlMap Data Mapper can be found in + the SqlMap Manual. + alt="Active Records and SqlMap DataMapper" id="fig:diagram.png" class="figure"/> +

+ +

+ The Active Record class has methods that do the following: +

+

+The Active Record implementation utilizes the Prado DAO classes for data access. +The current Active Record implementation supports +MySQL, +Postgres SQL and +SQLite databases. +Support for other databases can be provided when there are sufficient demand. +

Defining an Active Record

+

Let us + consider the following "users" table that contains two columns named "username" and "email", + where "username" is also the primary key. + +CREATE TABLE users +( + username VARCHAR( 20 ) NOT NULL , + email VARCHAR( 200 ) , + PRIMARY KEY ( username ) +); + +

+

Next we define our Active Record class that corresponds to the "users" table. + +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'); + } +} + +

+

Each property of the UserRecord class must correspond to a + column with the same name in the "users" table. The static class variable + $_tablename is optional when the class name is the same as + the table name in the database, otherwise $_tablename must + specify the table name that corresponds to your Active Record class. +

+ +
Tip: + Since TActiveRecord extends TComponent, setter and + getter methods can be defined to allow control over how variables + are set and returned. For example, adding a $level property to the UserRecord class: + +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; + } +} + +
+ +

+ The static method finder() returns an UserRecord instance + that can be used to load records from the database. The loading of records + using the finer methods is discuss a little later. The TActiveRecord::getRecordFinder() + static method takes the name of the current Active Record class as parameter. +

+ +

Setting up a database connection

+

+ A default database connection for Active Record can be set as follows. + See Establishing Database Connection for + futher details regarding creation of database connection in general. + +//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); + +

+ +

+ The default database connection can also be configured using a <module> + tag in the application.xml + or config.xml as follows. + + + + + + + +

Tip: + The EnableCache 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 cache + module must also be defined for the cache to function. +
+

+ +

A ConnectionID property can be specified with value corresponding + to another TDataSourceConfig module configuration's ID value. This allows + the same database connection to be used in other modules such as SqlMap. + + + + + + + + + + + +

+ +

Loading data from the database

+

+ The TActiveRecord class provides many convenient methods to find + records from the database. The simplest is finding records by matching primary keys. + See the for + more details. +

+

findByPk()

+

Finds one record using only the primary key or composite primary keys. + +$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 + +

+ +

find()

+

Finds one single record that matches the criteria. The criteria + can be a partial SQL string or a TActiveRecordCriteria object. + +$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. + +

+ +

The TActiveRecordCriteria class has the following properties: +

+

+ + +$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; + + +

findAll()

+

Same as find() but returns an array of objects.

+ +

findBy*() and findAllBy*()

+

Dynamic find method using parts of method name as search criteria. +Method names starting with findBy return 1 record only. +Method names starting with findAllBy return an array of records. +The condition is taken as part of the method name after findBy or findAllBy. + +The following blocks of code are equivalent: + + +$finder->findByName($name) +$finder->find('Name = ?', $name); + + + +$finder->findByUsernameAndPassword($name,$pass); +$finder->findBy_Username_And_Password($name,$pass); +$finder->find('Username = ? AND Password = ?', $name, $pass); + + + +$finder->findAllByAge($age); +$finder->findAll('Age = ?', $age); + +

+ +

findBySql()

+

Finds records using full SQL, returns corresponding array of record objects.

+ +

count()

+

Find the number of matchings records.

+ +

Inserting and updating records

+

+Add a new record using TActiveRecord is very simple, just create a new Active +Record object and call the save() method. E.g. + +$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 + +

Tip: +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.
+

+ +

+To update a record in the database, just change one or more properties of +the Active Record object that has been loaded from the database and then +call the save() method. + + +$user = UserRecord::finder()->findByName('admin'); +$user->email="test@example.com"; //change property +$user->save(); //update it. + +

+ +

+Active Record objects have a simple life-cycle illustrated in the following diagram. + alt="Active Records Life Cycle" id="fig:cycle.png" class="figure"/> +

+ +

Deleting existing records

+

+ To delete an existing record that is already loaded, just call the delete() method. + You can also delete records in the database by primary keys without + loading any records using the deleteByPk() method. + For example, to delete one or records with tables having a scalar primary key. + +$finder->deleteByPk($primaryKey); //delete 1 record +$finder->deleteByPk($key1,$key2,...); //delete multiple records +$finder->deleteByPk(array($key1,$key2,...)); //delete multiple records + +

+ +

+For composite primary keys (determined automatically from the table definitions): + +$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), .. )); + +

+ +

Transactions

+

All Active Record objects contains the property DbConnection + that can be used to obtain a transaction object. + +$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 will be raised +{ + $transaction->rollBack(); +} + + +

References

+ + + \ No newline at end of file -- cgit v1.2.3