New Database Module for PHPixie Completed

Today I wrote the latest test for version 3.0 of the database access module for PHPixie . When I started, it seemed like it would take only a few weeks, but in fact, it lasted for about 2 months due to the huge amount of refactoring and rewriting at first. But now we have an expandable library that can be used even outside the framework itself (that is, if you are forced to write on some other framework or CMS you can take your favorite library with you).

I will make a release after I finish the ORM module and adjust the existing modules to work with the new API. But if you are interested, you can look into the 3.0 branch on the github(however, it should be warned that there is no documentation there yet; it will appear along with ORM). Now let's look at the new features.

From the point of view of the average user

I already wrote about advanced query support with logical conditions for MongoDB here , but if you are too lazy to click on this link, here is a brief explanation: MongoDB does not use indexes when using queries like “ (A or B) OR (C and (D or E )) ”, So that PHPixie itself will lead the requests in such forms that will use them.

Several approaches for building queries have become available:

$query
    ->where('name', 'Trixie')
    ->or_where('name', 'Tinkerbell')
    ->where_not('id', '>', 7)
    ->having('count','<', 5)
    ->or_having('count', 7);
//Или попроще:
$query
    ->where('name', 'Trixie')
    ->_or('name', 'Tinkerbell')
    ->_and_not('id', '>', 7)
    ->having('count', '<', 5)
    ->_or('count', 7);
//Заметьте как метод _or запоминает контекст в котором используется
//(то есть работает как для  'where' так и для 'having')

Several approaches to nested logic:

$query
    ->where('name','Trixie')
    ->_or(function($builder){
        $builder
            ->_and('id',7)
            ->_or('id',5)
    });
//WHERE name = 'Trixie' OR ( id = 7 OR id = 5 )
//Или если вам не нравятся коллбеки 
//(также удобно при динамическом формировании запроса, например с EXT JS гридом)
$query->where('name','Trixie');
$query
    ->start_where_group('or')
        ->_and('id',7)
        ->_or('id',5)
    ->end_where_group();

Special operators for comparing columns:

$query->where('fairies.id','*=','pixies.id');

Ability to use SQL code inserts with separate parameters:

$expr = $this->db->expr('concat(name, ?)', array('test'));
$query->where($expr, 'Trixietest');
//WHERE concat(name, ?) = 'Trixietest';

Search for strings by NULL value, without knowing in advance that it is NULL:

$category_id = null;
$query->where('category_id', $category_id);
//даст WHERE category_id IS NULL
//а не WHERE category_id = NULL

Difficult conditions for JOIN .... ON using the familiar syntax:

$query->join('pixies');
//По умолчанию сравниваться будут колонки
$query->on('fairies.id','pixies.id');
//Можем добавить  условий и посложнее
$query->on('fairies.count','*>','pixies.count');

From the point of view of an advanced developer

• Incredibly lightweight, only 57 kilobytes of code
• Query builders do not contain any logic related to their parsing (for example, in SQL code). Instead, each driver uses its own Parser service, making query objects themselves light and cheap in terms of memory. Using parsers as services makes debugging and extension their trivial tasks. By the way, each parser will be created in memory only once for all requests.
• Each parser is divided into parsers for individual conditions and condition groups (Condition Group Parser). This separation makes it easy to add custom operators.
• Dependency binding is carried out in separate driver classes, that is, in no case (except throwing exceptions), no class creates objects by itself using the new command . This makes it easy to replace any class with its implementation.
• Requests to MongoDB are parsed into special Runner objects that are easy to debug in case of an error.
• SQL databases that work through PDO have their own separate adapters and parsers, which makes it easy to add support for any database with which PDO works using a minimum of code.

The code is fully covered by PHPUnit tests, which gives 166 tests and 1151 comparisons.

I hope that this post will convey my joy with the work done and that the final release of the ORM module is not far away.