elegent way to handle "pluggable" backend servers?
From: Jon Lapham (lapham_at_jandr.org)
Date: 02/18/05
- Next message: Nancy McMullin: "Can't save edited values to database"
- Previous message: Tim Bunce: "Re: DBD::Oracle problem"
- Next in thread: Brian D Campbell: "RE: elegent way to handle "pluggable" backend servers?"
- Maybe reply: Brian D Campbell: "RE: elegent way to handle "pluggable" backend servers?"
- Reply: Steven Lembark: "Re: elegent way to handle "pluggable" backend servers?"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Date: Fri, 18 Feb 2005 16:37:16 -0200 To: dbi-users@perl.org
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
-- -**-*-*---*-*---*-*---*-----*-*-----*---*-*---*-----*-----*-*-----*--- Jon Lapham <lapham@jandr.org> Rio de Janeiro, Brasil Personal: http://www.jandr.org/ ***-*--*----*-------*------------*--------------------*---------------
- Next message: Nancy McMullin: "Can't save edited values to database"
- Previous message: Tim Bunce: "Re: DBD::Oracle problem"
- Next in thread: Brian D Campbell: "RE: elegent way to handle "pluggable" backend servers?"
- Maybe reply: Brian D Campbell: "RE: elegent way to handle "pluggable" backend servers?"
- Reply: Steven Lembark: "Re: elegent way to handle "pluggable" backend servers?"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Relevant Pages
|
