RE: elegent way to handle "pluggable" backend servers?

• Previous message: Ron Stewart: "DBD::Oracle 1.16 on OSX 10.3 running Oracle 10.1 (ORA 00600 error)"
• Maybe in reply to: Jon Lapham: "elegent way to handle "pluggable" backend servers?"
• Next in thread: Jon Lapham: "Re: elegent way to handle "pluggable" backend servers?"
• Reply: Jon Lapham: "Re: elegent way to handle "pluggable" backend servers?"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

To: lapham@jandr.org
Date: Fri, 18 Feb 2005 16:05:21 -0600

This is a good question. Perhaps a tiny bit off topic for the mailing list but I'll take a stab at giving you my 2 cents worth, anyway.

Yes, I've done this kind of thing before but only with two or three drivers and in a different language and using a different data access model (that is, not with Perl and not using DBI). But I believe the same principle could apply here. I'm assuming that you have lots of database related code and that there are a number of driver differences through out your code.

I suggest that you want an OO approach and encapsulate the driver differences in several "subclasses". I'll assume you know how to do OO in Perl (i.e., create OO classes and methods with modules and subs).

Near the beginning of your Perl program, you'll do a one time if/elsif to assign the driver/version object (we'll call it $d).

if ($dbserver eq 'MySQL' and $dbversion == 3.23) {$d = Mysql_3_23::new()}
elsif ($dbserver eq 'PgSQL' and $dbversion == 7.4) {$d = Pgsql_7_4::new()} elsif (etc...)

At all locations in your code, where there is a driver difference, factor that out into the subclass. So far this sounds a bit like your second suggestion, but let me suggest that you don't do:

$SQL = $d->some_query();
$result = $conn->prepare($SQL);
$result->execute();

But rather factor out the minimal amount that truly relates to differences in your database and driver, and not your domain. In other words...

$SQL = 'SELECT * FROM blah WHERE ' . $d->Boolean('active');
$result = $conn->prepare($SQL);
$result->execute();

In this example, Boolean is a method that all subclasses implement and returns the appropriate Boolean syntax for the given column name.

Where ever you have commonality between several subclasses, you can factor the common functionality into a base class that they share. With the number of subclasses you are talking about, and I can guess at their nature, I'd venture that you could create quite an inheritance tree, depending on the mix of common and different behaviors in your databases. But that's OK.

Hope this helps.

-----Original Message-----
From: Jon Lapham [mailto:lapham@jandr.org]
Sent: Friday, February 18, 2005 11:37 AM
To: dbi-users@perl.org
Subject: elegent way to handle "pluggable" backend servers?

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/
***-*--*----*-------*------------*--------------------*---------------

• Previous message: Ron Stewart: "DBD::Oracle 1.16 on OSX 10.3 running Oracle 10.1 (ORA 00600 error)"
• Maybe in reply to: Jon Lapham: "elegent way to handle "pluggable" backend servers?"
• Next in thread: Jon Lapham: "Re: elegent way to handle "pluggable" backend servers?"
• Reply: Jon Lapham: "Re: elegent way to handle "pluggable" backend servers?"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]