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   33039 Views

Avatar
xeraa

Google Summer of Code Hacker, 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

Google Summer of Code Hacker, 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, 685 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, 685 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
Anonymous user

22 Posts

6 September 2007 at 9:25am

Ingo, great comments. I agree with you.

Sam, good ideas, but I think you went a little too far :-) Specifically, I think the right approach is your first code example above. Why? SilverStripe is not in the business of coming up with a compliant SQL syntax. People have been trying to do that for decades. Literally. I think the best approach for us is to identify the database and leave it at that.

I do think we need to optionally specify a version number as a "minimum required version" for that query. I guarantee we will quickly run into a situation where a developer will require a specific version of a database and will want to specify that particular need in the code.

Avatar
Anonymous user

22 Posts

6 September 2007 at 9:27am

Sam, your comments about finding patterns in the code is correct as a goal. We (Philipp mostly :-) are trying to identify patterns where possible, but the bigger job is to cover the syntactic basics at this point.

Go to Top