With Object Relational Mapping, the enhanced XItem classes (see chapter Object meta data) can be automatically loaded from a database or saved back. The majority of the required information to make this happen is already in place. In the following example, the classes have been prepared with meta information. Let's see how they can be automatically preserved in a database.
class Author extends XItem { public $Name; public static function FillMeta(XMeta $m){ $m->Name = XMeta::String(); } } class Book extends XItem { public $Title; public $idAuthor; public $NumberOfPages; public static function FillMeta(XMeta $m){ $m->Title = XMeta::String(); $m->idAuthor = XMeta::ID(); $m->NumberOfPages = XMeta::IntegerOrNull(); } }
First of all, we have to define which database tables are going to be used. This can be done with the SetDBTableName method. Then each field has to be linked to a table column, with the WithDBAlias method. This is optional; if omitted, the field's name is going to be used. It is even possible to use an alias for the inherited id field.
class Author extends XItem { public $Name; public static function FillMeta(XMeta $m){ $m->SetDBTableName('app_authors'); $m->Name = XMeta::String(); } } class Book extends XItem { public $Title; public $idAuthor; public $NumberOfPages; public static function FillMeta(XMeta $m){ $m->SetDBTableName('app_books'); $m->id->WithDBAlias('book_id'); $m->Title = XMeta::String(); $m->idAuthor = XMeta::ID(); $m->NumberOfPages = XMeta::IntegerOrNull()->WithDBAlias('NumPages'); } }
That's all! The classes are now ready. All we need is a connection to the database (see the chapter Setup).
The easiest way to load an object from the database is the Pick function, by passing its id.
$id = Http::$GET['id']->AsID(); $book = Book::Pick( $id ); echo $book->Title;
The Pick function will do a lot of things behind the scene. First of all, it will see in the cache if the object is already loaded and if so, it will save the query to the database. Then it will scan the fields of the meta object and will generate the required sql code. Finally, it will retrieve the columns from the result of the query, it will convert them to the appropriate type and it will assign them to a newly created Book object.
If the book was not found it the database, the function will return null.
Once loaded, the object can be modified by the code and saved back to the database, with the Save method:
$book->Title = 'The old man and the sea'; $book->Save();
Or, it can be deleted, with the Kill method:
$book->Kill();
Once killed, the record from the database and the entry in the cache will be removed. However, the object itself will remain available and can be used:
$book->Kill(); echo 'The book ' . $bool->Title . ' has been deleted';
The Make function creates new instances of an object:
$book = Book::Make(); $book->Title = 'Alice in wonderland'; $book->Save();
The Make function, behind the scenes, will reserve an id and will add an entry in the cache. Therefore, the object is ready to be used directly, even before saving, as the id will not change.
$author = Author::Make(); $author->Name = 'Lewis Carroll'; $book = Book::Make(); $book->Title = 'Alice in wonderland'; $book->idAuthor = $author->id; $author->Save(); $book->Save();
If the object is temporary and you don't want to spend a permanent id for it, then you can use the function Temp. This function will create an object and will assign an id to it, but the id is a temporary one. In addition, the new item will not be stored in the cache. The temporary id is guaranteed to be unique for the duration of the request.
$author = Author::Temp(); $author->Name = 'Lewis Carroll';
Temporary object cannot be saved. If you want to save a temporary object, you have to get a copy with a permanent id.
$author = Author::Temp(); $author->Name = 'Lewis Carroll'; ... $author_copy = $author->Copy(true); $author->Save();
The Pick can also be used for linking objects together, as it is very cheap after the first call. To link a book to its author, it is sufficient to add a simple method in the Book class.
class Book extends XItem { ... public function GetAuthor(){ return Author::Pick( $this->idAuthor ); } ... }
With this method, the author of the book can be found naturally from the code. However, as the Pick function may return null, an extra check must be placed:
$author = $book->GetAuthor(); if (!is_null($author)) echo $author->Name;
As seen in the chapter Object meta data, the XList class holds a list of XItems. Now, the Seek function automatically fills a list from the database.
foreach (Book::Seek() as $book) { echo '<li>'.$book->Title.'</li>'; }
The Seek function also does many things behind the scenes. First of all, it acts lazily in the sense that it will not query the database unless really needed. Then, it creates the query but retrieves only the ids of the the objects without loading the objects themselves. Finally, it loads lazily every object needed, with a high possibility to load the object from the cache without hitting the database.
In addition, Seek will take into account the default ordering specified in the meta object. So, if it is defined that the books are to be sorted by title by default, then the generated query will contain an ORDER BY clause.
The lazy approach comes with a cost if it is known that all the objects of the list will be loaded and that no object is already in the cache. This is because it will execute 1+N light queries: one query to retrieve the ids and one query for each of the objects. The alternative approach is to load everything at once in one big query with the cost of reloading data that may exist already in the cache. This is possible with the Aggressively method:
foreach (Book::Seek()->Aggressively() as $book) { echo '<li>'.$book->Title.'</li>'; }
It starts to become evident that lists have two distinct states. Either they are ready to be used directly, or there are pending queries the have to be executed before the list is ready to be used. If the list is ready, then it can be accessed as a traditional PHP array, or through the methods of LINQ. If, however, the list is in the pending state, any access to the array will automatically cause the execution of the queries.
While the list rests in the pending state, it is possible to modify the enclosed query. This is done by calling the same LINQ methods. As the list is aware of its state, it is going to translate the calls in a diferrent way according to it.
Let's see an example. The following code will find the first 10 books of 2001 when sorted by title.
$m = Book::Meta(); $list = Book::Seek() ->Where( $m->Year->Eq( 2011 ) ) ->OrderBy( $m->Title ) ->Take( 10 );
The rule of thumb here is that the list will not execute any query, unless it is needed to. So, the call to the function Book::Seek() will create a list with a pending query, but it will not execute the query. Then, the Where() method will add a predicate to that query. The predicate knows how it can be translated to SQL, but for the time being it is not necessary to. Then OrderBy() method will add an ordering instruction and the Take() will add a limit. None of these require the actual execution of the query.
The query is going to be executed when there is access to the items of the list. This happens usually upon iteration with foreach. Until then, the list is open to any manipulation and LINQ methods are translated as modification to the query. Any consequent call will be applied on the objects of the list in memory without hitting the database again.
foreach ($list as $book) { // the SQL query is executed silently before the iteration. echo $book->Title; } foreach ($list->Skip(5) as $book){ // no SQL query will be executed here. LINQ will be executed in memory. echo $book->Title; }
The smart predicates of the the meta model know how to translate themselfs into SQL so that they can postpone and transfer their execution to the database server. If we had used closures, the code would have worked without problem, however the execution time would be longer. Consider the following example, which is the same as the example above with the predicate object have been replaced by a predicate closure:
$m = Book::Meta(); $list = Book::Seek() ->Where( function($book){ return $book->Year == 2011; } ) ->OrderBy( $m->Title ) ->Take( 10 );
The predicate closure does not know how to translate itself in SQL and so it is bound to be executed in memory. That would mean that all books from the database will be first loaded in memory and then be filtered and ordered before the first 10 are taken. Apart from the fact that the loading of all books will take much time, even the queries in memory will take longer than the equivalent in database because the use no precalculated indices.
The following example is the same as the first one, except that this time a closure is used for ordering. This is still slower that the first one, but faster that the second. This is because this time, the filtering predicate can be executed in the database. So, instead of loading every book in memory, only the books of 2011 will be loaded. The sorting, however, will be executed in memory.
$m = Book::Meta(); $list = Book::Seek() ->Where( $m->Year->Eq( 2011 ) ) ->OrderBy( function($book){ return $m->Title; } ) ->Take( 10 );
In general, although both approaches work, it is better to assign the execution of the queries to the database server because it is more optimised. Having said that, there are cases where the use of closures gives a flexibility that cannot be achieved with SQL. For instance, the following code will select the books of 2011 for which there exists a cover as a jpg file in the hard disk, ordered by title.
$m = Book::Meta(); $list = Book::Seek() ->Where( $m->Year->Eq( 2011 ) ) ->OrderBy( $m->Title; ) ->Where( function($book){ return file_exists('covers/'.$book->id->AsHex().'.jpg'); } );
What is nice with the above code is that the form of the query is not changed. What can be executed in the database will be transferred there. The ordering of the calls here is important. Everything will be transferred to the database until something that cannot be transferred is encountered. In this case, the last filter function cannot be executed in the database. This is why, the ordering method is placed before it. Had it been placed after the filter, it would have been executed in memory instead.
The predicates are essentially small elements of code with high reusability value because they can be easily combined together to create complex queries. Therefore, it is wise to structure the code around predicates. The other functions, which will give the final API, can combine predicates to produce the result. This approach have endless possibilities because it is expandable without having to rewrite the basic building blocks.
In the following example, it is agreed that no book is deleted from the database and that searching with a string implies searching in the Title, the Author and the Summary fields of the book. These two facts are coded as predicates, and can be used anywhere in the code to filter books, instead of repeating them. What's more, if, sometime in the future, the definition of what is active changes, then it will be enough to change the predicate without having to touch every book query in the application.
class Book extends XItem { ... // // Predicates // public static function PredIsActive(){ return self::Meta()->DateDeleted->IsNotNull(); } public static function PredBySearchString($search_string){ $m = self::Meta(); $s = '%'.$search_string.'%'; return XPred::Any( $m->Title->Like( $s ) ,$m->Author->Like( $s ) ,$m->Summary->Like( $s ) ); } // // API functions // public static function SeekActive(){ return self::Seek()->Where( static::PredIsActive() ); } public static function SearchActive($search_string){ return self::SeekActive()->Where( self::PredBySearchString($search_string) ); } ... }
It's often the case that a database contain triggers. Those triggers are necessary to maintain referencial integrity which is fine. However, they are usually misused it the sence that part of the application logic and the business intelligence is encoded there. This is simply not the right place for many reasons. First of all, it is easier to maintain the code if the logic is not scattered around. In addition, even if the trigger enforces referencial integrity, the messages returned to the user in case of breaking the integrity are part of the business logic because they should be handled and translated in a message meaningful to him.
For these reasons, Oxygen offers a set of object level events, which are to be executed in PHP. These events include OnInit, OnLoad OnBeforeSave, OnAfterSave, OnBeforeKill and OnBeforeKill. All events can be handled by overriding the protected method with the same name.
OnInit is very often used to provide default values for the objects fields, as PHP dows not allow field initializers to be anything else than scalar literals.
class Book extends XItem { ... protected function OnInit(){ $this->DateCreated = XDateTime::Now(); } ... }
The events on loading and saving can be used to calculate fields derived from other fields, usually for the shake of speed. Finally, the delete events can be used to enforce referencial integrity by throwing exceptions with meaningful messages.