Database Integration

Agile Toolkit features two basic classes for model creation. The class "Model" is a lightweight implementation for NoSQL and non-relational databases. "Model_Table" class provides a more powerful model implementation which can rely on the power of relational database.

Philosophy of Agile Toolkit Models

In some other frameworks you might have seen that Models are used to simplify database and unify it's basic features across different vendor. Agile Toolkit does not attempt to do that. Instead Agile Toolkit highlights the powerful features relational databases offer developers and relies on their power

This is best visible when you compare number of requests a typical Agile Toolkit application will send to your database while rendering the page. While an average application in other frameworks may perform as many as 60 queries per page, Agile Toolkit keeps it down to 10-15 queries for dynamic pages with complex structures. This is achieved by building a sophisticated and optimized queries and letting your database engine collect all the data.

The Example

Let's assume you have a two table structure: "book" and "author". Your business requirements request you to build a list of book which also displays the author.

In a conventional frameworks it already provides you with the model class for "book" and "author" and also means to iterate. While you retrieve data for the 50 books in the database, you need to traverse the reference from "book" to "author" model to fetch author's name. This is where some frameworks require to perform caching in order to remain scalable

Agile Toolkit allows you to take a "book" model and add a join into it as well as define additional fields from the "author" table. This is done without exposing the developer to the SQL code.

$book->join('author')->addField('author_name','name');

Next if the $book model is used in any lister or grid it will also contain the field for the author. This will be provided by the query generated by the model and with no additional queries.

Expressions

This is a good start, but there are other features your favorite database engine has — functions, expressions and stored procedures. Agile Toolkit allows you to embed expressions into your model in a very reliable way. Here are just a few ways how to define a expression

$book->addExpression('random_number')->set('rand()');

$book->addExpression('records_in_test_table')->set(function($m,$q){
  return 
$q->dsql()->table('test')->field('count(*)');
});

Building Queries Based on Model

Any model defined in Agile Toolkit knows how to create a custom query. In example above I have referenced table 'test' explicitly, however I can also use the model object to do the same:

$sold_books $book->count()->where('is_sold',1)->getOne();

The count() method returns dynamic select based on a book model which then is dynamically modified to include additional condition and executed.

Adding Conditions Through Traversing

After you load a record into a model, you may traverse dependencies using the rel() method

$book->load(1);
$author $book->rel('author_id');

The $author will now contain model of pre-loaded "Author" model. However you may also traverse it in the other way:

$author->load(8);
$books $author->rel('Book');

This time we are traversing one to many relation, therefore "$books" model will not contain a pre-loaded entry. Instead it will have a condition which will limit the range of accessible records to the books written by author with id=8. You may also generate a custom queries which will be applied to the same range of books:

$author->load(8);
$books $author->rel('Book');
$books -> dsql() -> set('is_sold',true) -> update();

This will mark all the books "sold" for that particular author. Agile Toolkit takes extra care not to mark any other books with the update.

The Significance of Conditions

Consider you are writing a large application. Throughout your code you are using a "Book" model which maps directly into "book" table. Then there is that requirement to implement soft-delete in the "Book" model. All you need to do is to change the definition of a "Book" model by adding this code:

function init(){
  
parent::init();
  
$this->addField('is_deleted')->type('boolean');
  
$this->addCondition('is_deleted',false);
}
function 
delete($id=null){
  if(
$id)$this->load($id);
  if(!
$this->loaded())throw $this->exception('Unable to determine which record to delete');
  
$this->set('is_deleted',true);
  
$this->saveAndUnload();
}

This is all the code you need to implement soft-delete. All of your code will now respect the condition and deletion method will now flip the flag instead of doing a physical delete. Other frameworks struggle heavily and require hundreds of lines of code to implement soft-delete

Assume that the next requirement is to introduce "state" of the book with two possible values "draft" and "active". All of your code now must not see the "draft" books. How wil you do that?

class Model_Book extends Model_Table {
  function 
init(){
    
parent::init();
    
$this->addField('status')->enum(array('active','draft'))->defaultValue('active');
    
$this->setConditions();
  }
  function 
setConditions(){
    
$this->addCondition('status','active');
  }
}

class 
Model_Book_Draft extends Model_Book {
  function 
setConditions(){
    
$this->addCondition('status','draft');
  }
  function 
publish(){
    
$this->set('status','active');
    return 
$this->saveAs('Book');
  }
}

I have successfully altered my model to only show active books but also I have created a separate model which only works with draft books.

The Conditioning support allows you to define models as per your business rules and not as per your database structure.

Loaded Record versus Accessible Records

There are two characteristics of any model in Agile Toolkit. Firstly model can load some of the records from a respective database table as long as all conditions match. Modal may also save a record into table but similarly — all the conditions must match on the newly added record. In other words if you have access to a model with condition, you can't bypass that condition easily.

In practice this introduces incredible layer of logical consistency which significantly reduces possibility of a human mistake. If previously you might have forgotten condition in some of your code and would have left a hard-to-find back-door in your application.

Optimizing Queries

The biggest rule of of a high-performance and scalable web application is that you must limit operations with high latency. The native PHP code is relatively fast to execute, but if you are sending query to MySQL, it might take a while for query to be executed and results to be delivered back.

addExpression is one tool allowing to introduce expressions into your main query. This way you won't require to perform additional queries for every row of your data. Try to make it your golden rule - to never perform query while iterating through another query.

Agile Toolkit has another curious method similar to ref() called refSQl(). This method, however does not assume that you have a single record loaded. Instead, the model it returns will produce a query specifically to be used as a sub-query. It's probably best to explain this with example.

$authors $this->add('Model_Author'); // selects ALL authors
$authors -> addCondition('gender','F'); // limits only to female authors
$authors -> addExpression('books_sold')->set(function($m,$q){
  return 
$m->refSQL('Book')->addCondition('is_sold',true)->count();
});

foreach(
$authors as $junk){
  echo 
$authors['name'].': sold '.$authors['books_sold']."\n";
}

This code will add new column to the model which will use a "Book" model to calculate number of books sold per author. The closure function is executed while query is being built.

Using Field References in DSQL

DSQL is a model class in Agile Toolkit which is a object-oriented model for SQL queries. By calling methods of a DSQL object you can change the SQL query.

Relational Model relies heavily on DSQL. One instance of a DSQL object is stored in a protected property of a model. This object collect information about joins, conditions, sorting etc. When you call addCondition() this condition is being applied to the master DSQL object

By calling $model->dsql() you receive a clone of a master DSQL object which you can adopt to your own needs. This typically is very good for multi-row updates or using in custom expressions. This is also handy when using in sub-selects.

The DSQL objects are pretty much independent, however they recognize model field definitions and can use them as a valid objects. This is very useful in sub-queries and this is how refSQL works. It creates condition but passes a field as an argument instead of the actual field value.

$book -> addCondition$this->dsql()->fx('length'$book->getElement('name')),'>',5);

The condition produced will properly reference the field. This works for fields which are physically present in a book table and also for fields being added through joins such as author_name.

class Model_User extends Model_Table {
  ...

  
// delete purchases of certain item, and next items too.
  
function purgeDeletedUsers(){

    
$m=$this->add('Model_Table',array('table'=>$this->table));
    
$m->addField('is_deleted')->type('boolean');
    
$m->addCondition('is_deleted',true);

    
$p=$this->add('Model_Purchase');
    
$p->addCondition('user_id','in',$m->dsql()->field('id'));
    
$p->deleteAll();

    
$m->deleteAll();
  }
  
}

The example above implements for a soft-deleted model "User" a purge functionality. A new model is constructed selecting all deleted entries and then it's used to build a sub-query for instant deletion of related purchases. Next all the user records are also permanently disposed of. All of that is done by two operations only.

Classes and Objects

As you develop complex model interactions with Agile Toolkit, you must be mindful of what you are operating with - objects (and which type of object) or scalar variables. Below is a summary of different classes introduced and used in this chapter.

  • Model - Implements a Generic Model. Model's elements are fields, which describe type and behaviour of each field.
  • Field - Implements a Model Field and it's meta-data.
  • Model_Table - Implements a Relational Model. Fields of relational model may be used in DSQL expressions. Each model has a master "DSQL" object (private). You can request a clone of that object through dsql() method.
  • DB_dsql - Dynamic SQL Interface. Each object represent a query or expression. You can specify arguments to a query. You may pass an another DSQL object in place of most arguments. You can also use model fields.
  • SQL_Relation - When calling model->join() it returns instance of SQL_Relation class. Thys provides syntactic sugar assigning fields to the proper table but also will add necessary hooks your model to update related tables when saving.