Jump to:

17452 Posts in 4473 Topics by 1971 members

Archive

SilverStripe Forums » Archive » How to deal with database specific queries

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

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

Page: 1 2
Go to End
Author Topic: 32943 Views
  • xeraa
    Avatar
    Google Summer of Code Hacker
    58 Posts

    How to deal with database specific queries Link to this post

    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?

  • Ingo
    Avatar
    Forum Moderator
    801 Posts

    Re: How to deal with database specific queries Link to this post

    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

  • xeraa
    Avatar
    Google Summer of Code Hacker
    58 Posts

    Re: How to deal with database specific queries Link to this post

    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.

  • Ingo
    Avatar
    Forum Moderator
    801 Posts

    Re: How to deal with database specific queries Link to this post

    > 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

  • Sam
    Avatar
    Administrator
    679 Posts

    Re: How to deal with database specific queries Link to this post

    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.

  • Sam
    Avatar
    Administrator
    679 Posts

    Re: How to deal with database specific queries Link to this post

    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.

  • Anonymous user
    Avatar
    22 Posts

    Re: How to deal with database specific queries Link to this post

    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.

  • Anonymous user
    Avatar
    22 Posts

    Re: How to deal with database specific queries Link to this post

    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.

    32943 Views
Page: 1 2
Go to Top

Want to know more about the company that brought you SilverStripe? Then check out SilverStripe.com

Comments on this website? Please give feedback.