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.

We've moved the forum!

Please use forum.silverstripe.org for any new questions (announcement).
The forum archive will stick around, but will be read only.

You can also use our Slack channel or StackOverflow to ask for help.
Check out our community overview for more options to contribute.

Archive /

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

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

How to deal with database specific queries


Go to End


10 Posts   34408 Views

Avatar
xeraa

Community Member, 58 Posts

31 August 2007 at 7:50am

Without going into any details, there are points where we need database specific queries.
I'm currently handling these via switches, something like this:
switch (DB::getDatabaseServer()) {
case "mysql":
$sql = ...
break;
case "pgsql":
$sql = ...
break;
case "mssql":
$sql = ...
break;
}

I'm wondering if I should use a method instead which returns the desired string, something like:
$sql = getDBQuery($mysqlQuery, $pgsqlQuery, $mssqlQuery);

Any thoughts or comments on this?

Avatar
Ingo

Forum Moderator, 801 Posts

2 September 2007 at 11:51pm

Edited: 02/09/2007 11:54pm

i'd prefer the second approach, because it adds more flexibility than custom switch-statements (developers can already customize when building the different queries).
it also leaves the current style of querying intact, no big API change.
how about making the function-naming and context more obvious by having sql() as a static method on the usual query-object? also, explicitly setting the type of query might be preferred to having fixed arguments (e.g. first argument *must* be mysql). can anybody think of a more concise name than setType()?

$mysqlQuery = new Query();
$mysqlQuery->setType('mysql','4.0.2'); // with optional minimum required version
// ...
$sql = DBQuery::sql($mysqlQuery, $pgsqlQuery, $mssqlQuery); 

btw, i find the naming of DB::getDatabaseServer() a bit misleading, would rather expect to get a socket-connection or something - perhaps DB::get_type()? note the underscore for static methods (as stated in our coding conventions) - even if the DB class doesn't fully adhere to it, we shouldn't add new damage :)

Avatar
xeraa

Community Member, 58 Posts

3 September 2007 at 8:25am

Edited: 03/09/2007 8:27am

Thanks for the reply Ingo

1) I'll rename DB::getDatabaseServer() to get_type(). Originally I wanted to follow the exisiting code (like getVersion(),...) to keep the naming within the class consistent, but if we should do it like that I will stick to that ;)

2) I guess we are heading for different directions on the switch issue (maybe I should have given a more in depth example ;) ). Here's some code from my database testing and how I intended it to be used:

<?php
	$sqlString = DB::getQuery(
		"UPDATE tablea INNER JOIN tableb ON tablea.ID = tableb.ID SET tablea.Age = tableb.Size WHERE tablea.ID = 3", //mysql
		"UPDATE tablea SET Age = tableb.Size FROM tableb WHERE tablea.ID = 3 AND tableb.ID = tablea.ID", //pgsql
		"UPDATE tablea SET tablea.Age = tableb.Size FROM tablea INNER JOIN tableb ON tablea.ID = tableb.ID  WHERE tablea.ID = 3", //mssql
	);
	$result = DB::query($sqlString);
?>

I actually wanted to use it just to get an SQL string and leave the actual querying, regardless if it is DB::query, extendedSQL,... the same. This should specifically target DB::query("..."), but could be used elsewhere as well. For example it could be used on how "LIMIT" is handled in sql() in the class SQLQuery (as MS SQL does not know this command), etc.

3) Do you really think version dependent queries are necessary? So far I have only seen a getVersion() call for collations and I'm generally trying to find queries which will work on any version of each database (as having up to three queries is already a royal pain ;) ). Of course we can add this if you think this will be needed in the future, but I'm not too convinced yet.

Avatar
Ingo

Forum Moderator, 801 Posts

3 September 2007 at 9:16am

> I guess we are heading for different directions on the switch issue
not necessarily, you're just using sql-strings instead of an object which can produce the sql.
your approach with multiple arguments depends on argument order, right?
if you want to provide just mysql as default and "myfunkydbms"-specific queries (leaving out specific mssql/postgres), you've got ugly calls like:

sqlString = DB::getQuery( 
        "UPDATE ...", //mysql 
        null, //pgsql 
        null, //mssql 
        "UPDATE ...", // myfunkydbms
);

when defining the type in the query-object like my initial example, you'd still have two arguments instead of four.
silverstripe best practice for queries is building query-objects instead of raw sql anyway :)
most of the time, you can simply copy a generic query-object and customize a small part dbms-specific.

> Do you really think version dependent queries are necessary?
generally i'd opt for extensibility when implementing a new API, rather have more possibilities defined than later patching-sessions (we had some ugly API-changes in DataObject where we had to preserve backwards-compatibility, and had to mess up argument-orders etc). example use case for versioned queries: transactions in mysql 5.
finding out the version-number of a database should hopefully be just a simple query (which could be cached), so i don't see any reasons against it :)

Avatar
Sam

Administrator, 690 Posts

3 September 2007 at 6:06pm

I would say that minimising the number of places in which we need to specifically lay out different queries for each database is going to be a very good idea. For instance, using DB::manipulate() or something to abstract away the SQL-syntax for performing an update might be useful. You may find that you need to improve the expressive power of DB::manipulate(), or create other, similar, functions to be implemented in the Database sub-class, but that would be better than peppering these "switched queries" throughout the system.

Have you thought about using a map of db-classname => query value to specify the different queries? This would make it easier to add new database types in the future.

DB::switched_query(array(
  "MySQLDatabase" => "UPDATE tablea INNER JOIN tableb ON tablea.ID = tableb.ID SET tablea.Age = tableb.Size WHERE tablea.ID = 3",
  "PostgresDatabase => "UPDATE tablea SET Age = tableb.Size FROM tableb WHERE tablea.ID = 3 AND tableb.ID = tablea.ID",
  "MSSQLDatabase" => "UPDATE tablea SET tablea.Age = tableb.Size FROM tablea INNER JOIN tableb ON
tablea.ID = tableb.ID  WHERE tablea.ID = 3", 
));

Alternatively, we could use some kind of "tags" system, where a database connection object could be given a number of tags. For example, the MS SQL database could be tagged with both "compliant" and "mssql".

In this example, a MS SQL database would use $array['mssql'];

DB::switched_query(array(
  "mysql" => "UPDATE tablea INNER JOIN tableb ON tablea.ID = tableb.ID SET tablea.Age = tableb.Size WHERE tablea.ID = 3",
  "mssql" => "UPDATE tablea SET tablea.Age = tableb.Size FROM tablea INNER JOIN tableb ON
tablea.ID = tableb.ID  WHERE tablea.ID = 3", 
  "compliant => "UPDATE tablea SET Age = tableb.Size FROM tableb WHERE tablea.ID = 3 AND tableb.ID = tablea.ID",
));

But in this example, the same database would use $array['compliant']

DB::switched_query(array(
  "mysql" => "UPDATE tablea INNER JOIN tableb ON tablea.ID = tableb.ID SET tablea.Age = tableb.Size WHERE tablea.ID = 3",
  "compliant => "UPDATE tablea SET Age = tableb.Size FROM tableb WHERE tablea.ID = 3 AND tableb.ID = tablea.ID",
));

This "tags" system makes it easier to define a "standards compliant SQL" format that we can assume new databases that are introduced would use, unless we specifically needed to re-engineer the query for that database server.

That said, rather than making a fully-generic tags system, it might be better performance just to fail over to a "default" or "compliant" option if one for the specific database wasn't found.

Avatar
Sam

Administrator, 690 Posts

3 September 2007 at 6:12pm

An alternative solution that would simplify the process of adding new database to the system, would be to:

* Look at the places where customised queries need to be written.
* Find patterns in them.
* Define a method on Database that is reponsible for implementing that pattern.

DB::manipulate() is an example of one such pattern - it can be used to define, in a db-abstracted way, a large number of insert & update queries.

It's uncertain whether this would be a feasible strategy, but it wuold help minimise the number of database-specific queries that are written outside of the Database sub-class.

Avatar
xeraa

Community Member, 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: http://doc.silverstripe.com/doku.php?id=sql-queries - 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.

Go to Top