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


Go to End
Reply


13 Posts   33044 Views

Avatar
xeraa

Google Summer of Code Hacker, 58 Posts

13 September 2007 at 2:29am

Edited: 13/09/2007 2:30am

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

Forum Moderator, 801 Posts

13 September 2007 at 4:17am

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

Forum Moderator, 628 Posts

13 September 2007 at 7:55am

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

Avatar
Anonymous user

22 Posts

13 September 2007 at 8:41am

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

Google Summer of Code Hacker, 58 Posts

17 September 2007 at 7:26am

Edited: 17/09/2007 7:26am

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...

Go to Top