Skip to main content

This site requires you to update your browser. Your browsing experience maybe affected by not having the most up to date version.

Archive

Our old forums are still available as a read-only archive.

Moderators: martimiz, Sean, biapar, Willr, Ingo, simon_w

How to deal with database specific queries


Reply

13 Posts   32999 Views

Avatar
xeraa

13 September 2007 at 2:29am (Last edited: 13 September 2007 2:30am), Google Summer of Code Hacker, 58 Posts

After working on persistence issues and doing some documentation, I'm finally back to this thread ;-).

I tried to document the syntactic basics Brian is referring to here: [url]http://doc.silverstripe.com/doku.php?id=sql-queries[/url] - comments are of course very welcome.

Now to the thing to be used instead of the original switch: Would the following be what you intend? So you can (optionally) use version numbers and there's the option "other", so you won't have to duplicate queries (I wouldn't use compliant as that is sometimes not true, even if it is working across two databases).

<?php

DB::switched_query(array(
"mysql" => array(
4.0.2 => "<query1>",
5.0.3 => "<query2>",
),
"pgsql" => "<query3>",
"mssql" => "<query4>"
));

DB::switched_query(array(
"mssql" => array(
2000 => "<query1>",
2005 => "<query2>",
),
"other" => "<query3>"
));

DB::switched_query(array(
"pgsql" => "<query1>",
"other" => "<query2>"
));

?>

Avatar
Ingo

13 September 2007 at 4:17am Forum Moderator, 801 Posts

hey cool documentation, very comprehensive!

i like the implementation of version-numbers, its unobtrusive for developers, but available if needed. how about optionally accepting query-objects?

DB::switched_query(array(
"mysql" => array(
4.0.2 => $mysqlQueryObj,
5.0.3 => "<query2>",
),
"default" => "<query3>"
));


also, i think "default" is more accepted as a name for the fallback-query than "other" - its not really important, but easier to recognize IMO.

Avatar
Sigurd

13 September 2007 at 7:55am Forum Moderator, 628 Posts

Cool, well done!
I agree with Ingo; version numbers are nice, and 'default' is a good self-explanatory phrase.

Avatar
Anonymous user

13 September 2007 at 8:41am 22 Posts

Yes, I agree with Ingo, and this looks good, but please ensure the following cases are covered (referring to the first example in this thread):

if mysql = 4.0.1 we should throw an error (NOTE: I'm pointing out the implication in the example above that 4.0.2 is implied as a minimum version number needed. If we want to indicate that "we don't care" about version number for a particular query, we should probably have a "default" query that can be specified on a database-specific level. That means mysql would have 4.0.2, 5.0.3, and default queries as array elements.)

if mysql = 5.0.1 we should use the query specified in the 4.0.2 array element.

cheers,

Brian

Avatar
xeraa

17 September 2007 at 7:26am (Last edited: 17 September 2007 7:26am), Google Summer of Code Hacker, 58 Posts

To finish this off, I just did the documentation for this at [url]http://doc.silverstripe.com/doku.php?id=database-support[/url] - hope it's how you expected it...