DB.php patch - error handling, flexible WHERE clauses, fix bug

[plb]

Here's a patch:

http://pastebin.com/0rhnxLB7

This allows someone to add Config::get('mysql/options') and control the options used for the PDO driver.

Specifically I have set mine to this:

Code:

$GLOBALS['config'] = array(

Code:

'mysql' => array('host'         => 'localhost',

Code:

'username'     => 'root',

Code:

'password'     => '(secret)',

Code:

'db'           => 'imok',

Code:

'options'      => array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET SESSION sql_mode = ''",

Code:

PDO::ATTR_ERRMODE => PDO::ERRMODE_WARNING),

Code:

),

(Sorry, I can't figure out how to make code blocks work in multiple lines... The forum always seems to strip the newlines when I mark it as code.)

This PDO::ERRMODE_WARNING makes the database respond with an exception when it has an error - otherwise the default case is to just keep on going and don't say anything. I, personally, find this pretty important in my development environment to avoid errors that might otherwise go completely unnoticed.

Setting this ERRMODE_WARNING allowed me to see the bug in DB.php where a fetchALL() was being done even if the query was an INSERT or UPDATE or DELETE. This bug is now fixed in this patch.

Finally, the WHERE clauses on the get() and delete() functions were quite limited, allowing a maximum of a single condition unless you wanted to construct your own query. This patch includes a recursive build of the WHERE clause which allows nested arrays and the use of 'AND' and 'OR' and does the same for the existing triad-type of arrays as well as the duple-type of arrays that people from CakePHP might be more familiar with. Importantly, it is completely backward compatible and so anybody who has used the existing functionality will not have to change anything.

One could argue that simply writing your own query for anything more complicated than a single condition is more readable. I wouldn't argue. This gives more flexibility for those who may prefer the array-type of WHERE-clause construction.

For example, these would all work now:

Using current triads to construct the conditions:
$db->get('users', [ 'id', '=', '2' ]); // backwards compatible
$db->get('users', [ 'and', [ 'id', '=', '2' ], ['fname', '=', 'Sam' ]]) // WHERE id = 2 AND fname = 'Sam'
$db->get('users', [ 'or', [ 'fname', '=', 'Sally' ], ['fname', '=', 'Sam' ]]) // WHERE fname = 'Sally' OR fname = 'Sam'

Using CakePHP style dupals to make the same conditions:
$db->get('users', [ 'id' => '2' ]); // WHERE id = 2
$db->get('users', [ 'and' => [ 'id' => '2', 'fname' => 'Sam' ]]) // WHERE id = 2 AND fname = 'Sam'
$db->get('users', [ 'or' => [ 'fname' => 'Sally', 'fname =' => 'Sam' ]]) // WHERE fname = 'Sally' OR fname = 'Sam'

Note the need to have unique keys in the dupal style - I had to use 'fname' for one and 'fname =' in the other. I can't remember how you're supposed to solve this in Cake. Maybe just nesting?

Do be aware that I've done some relatively surface level testing on this recursive WHERE construction, but I haven't done any deep construction. Let me know if you come across bugs and I'll try to fix it.