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.

[mudmin]

I forgot to say...THANK you for this! I have it on the list.

[mudmin]

How is your testing going on this? I'm playing with it. I haven't found any particular issues. I just want to pound on it for a while before pushing it out.

[plb]

I'm using it somewhat extensively, but repetitively. In other words, the 15 or 20 queries that I'm using regularly are working great, but they aren't particularly complicated.

I fully agree it needs lots of testing before release - it's got big potential problems if something isn't working... (Although I suppose the big issue is to make sure it works 100{3bc1fe685386cc4c3ab89a3f76566d8931e181ad17f08aed9ad73b30bf28114d} on existing functionality - if something breaks on extending functionality at least that won't break anything that someone has already written)

[mudmin]

Right. I'm toying with it for 4.2/5.0.

[brian]

I'll wait to update US5 until I hear more back here. Once that is ready, I just need a summary to understand what was fixed where.

[faguss]

I've applied this patch and now with:

Code:

echo $db->errorString();

I can check error message on a failed query. Thanks!

To get this working I modified a line in users/init.php:

Code:

require_once $abs_us_root.$us_url_root.'usersc/classes/DB.php';

Changing this line in usersc/init.php will not do anything.

[faguss]

There is a syntax error. Should be

Code:

_calcWhere

instead of

Code:

calcWhere

. That's line 144 in the original paste and 169 in my snippet (already fixed).

Figured out a way to find multiple records with more than one condition on each one: https://pastebin.com/XmJR0Jk5

[mudmin]

Hmm. I have to check that out. Thanks for this!

[plb]

Oops - you're right! Looks like I didn't get a whole lot of testing done on the recursive aspect of this in its final version, huh?

Good catch!