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 +++++++++++++++++++++
.../protected/pages/Database/SqlMap.page | 265 +++++++++++++++
.../protected/pages/Database/diagram.png | Bin 0 -> 30320 bytes
.../protected/pages/Database/object_states.png | Bin 0 -> 9596 bytes
.../pages/Database/sqlmap_active_record.png | Bin 0 -> 17351 bytes
5 files changed, 625 insertions(+)
create mode 100644 demos/quickstart/protected/pages/Database/ActiveRecord.page
create mode 100644 demos/quickstart/protected/pages/Database/SqlMap.page
create mode 100644 demos/quickstart/protected/pages/Database/diagram.png
create mode 100755 demos/quickstart/protected/pages/Database/object_states.png
create mode 100755 demos/quickstart/protected/pages/Database/sqlmap_active_record.png
(limited to 'demos/quickstart/protected/pages/Database')
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:
+
+
Construct an instance of the Active Record from a SQL result set row.
+
Construct a new instance for later insertion into the table.
+
Finder methods to wrap commonly used SQL queries and return Active Record objects.
+
Update existing records and insert new records into the database.
+
+
+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:
+
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();
+}
+
+
+
+
+
\ No newline at end of file
diff --git a/demos/quickstart/protected/pages/Database/SqlMap.page b/demos/quickstart/protected/pages/Database/SqlMap.page
new file mode 100644
index 00000000..2b70e8f5
--- /dev/null
+++ b/demos/quickstart/protected/pages/Database/SqlMap.page
@@ -0,0 +1,265 @@
+
+
+
+
Data Mapper
+
Data Mappers moves data between objects and a database while keeping them
+ independent of each other and the mapper itself. If you started with
+ Active Records, you may eventually
+ faced with more complex business
+ objects as your project progresses. When you build an object model with a
+ lot of business logic it's valuable to use these mechanisms to better organize
+ the data and the behavior that goes with it. Doing so leads to variant schemas;
+ that is, the object schema and the relational schema don't match up.
+
+
+
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.)
+
+
+
When to Use It
+
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.
+
+
+
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 existing databases, this is very valuable.
+
+
+
The price, of course, is the extra layer that you don't get with
+ Active Record,
+ so the test for using these patterns is the complexity of the business logic.
+ If you have fairly simple business logic, an Active Record
+ will probably work.
+ For more complicated logic a Data Mapper may be more suitable.
+
+
+
SqlMap Data Mapper
+
The SqlMap DataMapper framework makes it easier to use a database with a PHP application.
+ SqlMap DataMapper couples objects with stored procedures or SQL statements using
+ a XML descriptor. Simplicity is the biggest advantage of the SqlMap DataMapper over
+ object relational mapping tools. To use SqlMap DataMapper you rely on your own objects,
+ XML, and SQL. There is little to learn that you don't already know.
+ With SqlMap DataMapper you have the full power of both SQL and stored procedures at
+ your fingertip
+
+
+
+ alt="SqlMap Data Mapper Overview" id="fig:sqlmap.png" class="figure"/>
+
+ Here's a high level description of the work flow illustrated in the figure abov.
+ Provide a parameter, either as an object or a primitive type. The parameter can be
+ used to set runtime values in your SQL statement or stored procedure. If a runtime value
+ is not needed, the parameter can be omitted.
+
+
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.
+
+
+
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.
+
+
+
Setting up a database connection and initializing the SqlMap
+
+ A database connection for SqlMap 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 SqlMap manager.
+$dsn = 'pgsql:host=localhost;dbname=test'; //Postgres SQL
+$conn = new TDbConnection($dsn, 'dbuser','dbpass');
+$manager = new TSqlMapManager($conn);
+$manager->configureXml('my-sqlmap.xml');
+$sqlmap = $manager->getSqlMapGateway();
+
+
+
+
+ The TSqlMapManager is responsible for setting up the database connection
+ and configuring the SqlMap with given XML file(s). The configureXml()
+ method accepts a string that points to a SqlMap XML configuration file. Once
+ configured, call the getSqlMapGateway() method to obtain an instance
+ of the SqlMap gateway interface (use this object to insert/delete/find records).
+
+
+
+ SqlMap database connection can also be configured using a <module>
+ tag in the application.xml
+ or config.xml as follows.
+
+
+
+
+
+
+
+
+
+
+ The ConfigFile 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).
+
+
Tip:
+ The EnableCache attribute when set to "true" will cache the
+ parsed configuration. You must clear or disable the cache if you
+ make chanages your configuration file.
+ A cache
+ module must also be defined for the cache to function.
+
+
+
+
To obtain the SqlMap gateway interface from the <module> configuration, simply
+ do, for example,
+
+class MyPage extends TPage
+{
+ public function onLoad($param)
+ {
+ parent::onLoad($param);
+ $sqlmap = $this->Application->Modules['my-sqlmap']->Client;
+ $sqlmap->queryForObject(...); //query for some object
+ }
+}
+
+
+
+
A quick example
+
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 plain User class as follows. Notice that
+ the User is very simple.
+
+class User
+{
+ public $username;
+ public $email;
+}
+
+
+
+
+
Next, we need to define a SqlMap XMl configuration file, lets name
+ the file as my-sqlmap.xml
+
+
+
+
+
+
+
+
The <select> tag returns defines an SQL statement. The id
+ attribute will be used as the identifier for the query. The resultClass
+ attribute value is the name of the class the the objects to be returned.
+ We can now query the objects as follows:
+
+
+//assume that $sqlmap is an TSqlMapGateway instance
+$userList = $sqlmap->queryForList("SelectUsers");
+
+//Or just one, if that's all you need:
+$user = $sqlmap->queryForObject("SelectUsers");
+
+
+
+
The above example shows demonstrates only a fraction of the capabilities
+ of the SqlMap Data Mapper. Further details can be found in the
+ SqlMap Manual.
+
+
+
Combining SqlMap with Active Records
+
The above example may seem trival and it also seems that there is
+ alot work just to retrieve some data. However, notice that the User
+ class is totally unware of been stored in the database, and the database is
+ unware of the User class.
+
+
+ One of advantages of SqlMap is the
+ ability to map complex object relationship, collections from an existing
+ database. On the other hand, Active Record
+ provide a very simple way
+ to interact with the underlying database but unable to do more complicated
+ relationship or collections. A good compromise is to use SqlMap to retrieve
+ complicated relationships and collections as Active Record objects and then using
+ these Active Records to do the updates, inserts and deletes.
+
+
Continuing with the previous example, we change the definition of the
+ User class to become an Active Record.
+
+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');
+ }
+}
+
+
+
+
We also need to change the definition of the SqlMap XML configuration. We
+ just need to change the value of resultClass attribute to UserRecord.
+
+
+
+
+
+
+
+
+
+
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.
+
+
+//assume that $sqlmap is an TSqlMapGateway instance
+$user = $sqlmap->queryForObject("SelectUsers");
+
+$user->email = 'test@example.com'; //change data
+$user->save(); //save it using Active Record
+
+
+
+
\ No newline at end of file
diff --git a/demos/quickstart/protected/pages/Database/diagram.png b/demos/quickstart/protected/pages/Database/diagram.png
new file mode 100644
index 00000000..0a0ca73d
Binary files /dev/null and b/demos/quickstart/protected/pages/Database/diagram.png differ
diff --git a/demos/quickstart/protected/pages/Database/object_states.png b/demos/quickstart/protected/pages/Database/object_states.png
new file mode 100755
index 00000000..db194783
Binary files /dev/null and b/demos/quickstart/protected/pages/Database/object_states.png differ
diff --git a/demos/quickstart/protected/pages/Database/sqlmap_active_record.png b/demos/quickstart/protected/pages/Database/sqlmap_active_record.png
new file mode 100755
index 00000000..6d958d33
Binary files /dev/null and b/demos/quickstart/protected/pages/Database/sqlmap_active_record.png differ
--
cgit v1.2.3