Hi Jon,
Instead of asking "where should I put the SQL?", you might ask "should I write the raw final SQL?"?
If you have 50 queries, and 10 platforms, you probably don't want to to maintain 500 text strings in your program. Ideally you want to write 50 queries in some generic format, and get the benefits of your solution #1, and then have 10 pieces of logic to translate a general query into a platform's specific SQL, to get the benefits of solution #2.
What we use internally here represents each query (basically) as a table and a series of key-value pairs. One layer expands the key-value pairs to do things like add required joins, and a second takes that and writes an actual SQL string.
Writing a good layer like that can be complicated, depending on how hairy your SQL gets. There are a ton of modules on CPAN which let you work with a database w/o writing the raw SQL, like Class::DBI. There are a few which try to re-write SQL from one platform to another, which might give you what you need with minimal code changes.
If you do write your own, the DBI module has a lot of catalog methods to let you examine your database. You could have your core subroutine check the data type of fields passed-in and handle boolean values specially, for instance, in a generic way. We use these and actual queries to the data dictionary of the given database to make the SQL writer "smart".
Scott Smith Informatics Genome Sequencing Center Washington University School of Medicine
David Dooling wrote:
Care to respond?
----- Forwarded message from Jon Lapham <[EMAIL PROTECTED]> -----
Date: Fri, 18 Feb 2005 16:37:16 -0200 From: Jon Lapham <[EMAIL PROTECTED]> Subject: elegent way to handle "pluggable" backend servers? To: dbi-users@perl.org Reply-To: [EMAIL PROTECTED]
My DBI perl application needs to support many database servers and versions, but will only use one database server per installation. IE: one customer may be using MySQL v3.xx, while another has PgSQL v7.4, while another has Oracle... etc. My application needs to work for them all, with minimal fuss.
As an example of why this is necessary, MySQL v<4 does not support boolean variables, and date handling between databases (and different versions of the same database) are often different.
I would like to ask your opinion(s) on which approach to take for coding this backend server "plugablility". It seems to me that there are a number of approaches.
1) The first idea would be to simple wrap all SQL in some conditional in the main body of the code, [or pseudocode in this case :) ]
if ($dbserver eq 'MySQL' and $dbversion == 3.23) { $SQL = 'SELECT * FROM blah WHERE active=1'; } elsif ($dbserver eq 'PgSQL' and $dbversion == 7.4) { $SQL = 'SELECT * FROM blah WHERE active'; } elsif (etc...) { ... } $result = $conn->prepare($SQL); $result->execute();
Okay, this has the advantage that all the SQL for all the supported backends sits easy to see in the main code body. Disadvantage is that it is ugly as sin making your main body code harder to follow, imagine if you wanted to support 20 database backend-version combinations?
2) But the SQL into a subroutine, held in an external module. One module for each database-version combo you want to support. Then, your main body code would look like:
$SQL = &db_some_query(); $result = $conn->prepare($SQL); $result->execute();
...where the subroutine &db_some_query() exists in a bunch of modules (ie: SQLpgsql_7_4.pm, SQLmysql_3_23.pm, SQLoracle_x_xx, etc) with only the appropriate on loaded earier in the app.
The advantage here is that it is extensible, more database-versions supported simply means creating more SQL* modules. The disadvantage is that these modules could become easily out of sync... nightmare in terms of maintainablity. Another disadvantage is that since the main body code contains a subroutine call rather than the actual SQL code, it is hard to see what is happening in the main body code, you would continually need to refer to the appropriate module.
Any opinions? Is there another approach that I should think about? Has anyone encountered this situation before?
Thanks!!!! -Jon
