Today I wrote the final test for the 3.0 version of the Database module. I started this thinking it would take me only a couple of weeks, but it spanned for about 2 months due to the humongous amount of refactoring and rewriting from scratch. What we have now is a scalable and extendable database library which can be used even outside of PHPixie itself (so if you’re forced to use some other framework or CMS you’ll be able to bring your favourite database library with you).

I’ll release it into the master branch when I’m done with ORM and will alter existing module to work with updated libraries. But if you’d like a sneak peek you can take a look at the 3.0 branch on github (be warned that I haven’t added documentation for it yet, since I’m expecting some slight modifications to it while working on the ORM module). So now let’s take a look at what’s new.

From an average user standpoint:
I have already posted about the advanced support for MongoDB conditional queries here, but if you’re too lazy to click that link, here is a short explanation: MongoDB will not use indexes if you use complex logical queries like “(A or B) OR ( C and (D or E))”, so PHPixie will expand such queries to forms that will use indexes.

Different approaches to query building, to fit your needs:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
$query
    ->where('name', 'Trixie')
    ->or_where('name', 'Tinkerbell')
    ->where_not('id', '>', 7)
    ->having('count','<', 5)
    ->or_having('count', 7);
;

//Or a simpler way:
$query
    ->where('name', 'Trixie')
    ->_or('name', 'Tinkerbell')
    ->_and_not('id', '>', 7)
    ->having('count', '<', 5)
    ->_or('count', 7);
//Notice how the _or shorthand rememebrs the current context
//(e.g. it's used both for 'where' and 'having')
 

Different approaches to nested logic:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
$query
    ->where('name','Trixie')
    ->or(function($builder){
        $builder
            ->_and('id',7)
            ->_or('id',5)
    });
//WHERE name = 'Trixie' OR ( id = 7 OR id = 5 )

//Or if you don't like callbacks (also usefull for dynamic query building, e.g. grids)
$query->where('name','Trixie');
$query
    ->start_where_group('or')
        ->_and('id',7)
        ->_or('id',5)
    ->end_where_group();
 

Special operators for comparing columns with other columns:

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

Ability to add raw expressions while still using prepared statements:

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

Finding rows with NULL, without knowing it’s NULL beforehand:

1
2
3
$category_id = null;
$query->where('category_id', $category_id);
//WHERE category_id IS NULL
 

Complex conditions support for JOIN … ON queries using same syntax as where and having

1
2
3
4
5
6
7
$query->join('pixies');

//Using column comparison by default
$query->on('fairies.id','pixies.id');

//But we can also add some other conditions
$query->on('fairies.count','*>','pixies.count');
 

From the advanced developer standpoint:

• Amazingly light, 57 KB of code
• Query builders don’t contain any parsing logic by themselves, isntead each driver uses its own Parser service, thus making queries lightweight and memory efficient. Using parsers as services makes debugging and extending them much easier. Also since each parser is instantiated only once it makes them even more efficient.
• Parsers are decoupled into Condition Group parsers (like WHERE statements) and individual operator parsers. Operator parsers are easily extendible by adding methods to them, so you can add a bunch of your own frequently used snippets there.
• The dependency wireing is done in separate driver classes, so at no point (apart from raising exceptions) do any of the classes instantiate objects on their own using the new statement. This makes it so much easier to replace system classes with your own.
• MongoDB queries are parsed into special Runner instances, that are easy to inspect if an error occurs
• SQL databases that use PDO get their own Adapters and Parser instances, making adding support for other PDO databases trivial

Everything has been unit tested, resulting in 166 tests and 1151 assertions

I hope this will get you as excited for the new release as I am. Hopefully the updated ORM moduel is not far away =)