Re: elegent way to handle pluggable backend servers?
Any opinions? Is there another approach that I should think about? Has
anyone encountered this situation before?
For most selects it isn't that hard to turn metadata
about the query into vendor SQL. The simplest fix may
be hiding the queries behind another object that takes
in generic selects and spits out SQL appropriate for
the particular DBD::Foobar.
I should have an alpha version of Plugin::Installer and
Plugin::Language::DML avaialble in a week. The plugins
could be various collections of metadata indexed by common
names (a.k.a. hash keys). You then use something like:
my $dblink = $dbhandler_class-construct;
...
$dblink-frobnicate( @argz );
With the various handler classes defining the
metadata-to-query handler for that particular
database. You only store the metadata once,
the if-logic is isolated into a single place.
That can easily be shoved into a single factory
class:
sub construct
{
shift;
my $connect_meta = shift;
my $handler_class =
do
{
if( ... )
{
'Oracle::Query'
}
elsif( ... )
{
'MySQL::Query'
}
else
{
croak Bogus query: unable to determine class
from:,
@$connect_meta;
}
};
# caller gets back whatever the appropriate
# class constructs with the connection data.
$handler_class-construct( @$connect_meta );
}
The handler classes all implement a few hooks like
run_query and commit:
my $handler = Factory-construct;
...
$handler-run_query( query_name = [ query argz ] );
$handler-commit;
The run_query could either construct SQL from scratch
or munge ANSI to handle special cases.
--
Steven Lembark 85-09 90th Street
Workhorse ComputingWoodhaven, NY 11421
[EMAIL PROTECTED] 1 888 359 3508
Re: elegent way to handle pluggable backend servers?
CAMPBELL, BRIAN D (BRIAN) wrote:
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).
Hmmm, that is an interesting idea.
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.
Yes, I see what you mean. This works great for the boolean case.
My concern would be that some of the differences in database backend
capabilities (ie: subqueries, cascade on delete, etc) actually force the
entire SQL statement to be re-written, or (worse) to become multiple SQL
statements. Unfortunately, sometimes it is not just as simple as the
BOOLEAN case I used in my example.
I probably should have used a subquery example in my original email. :)
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.
Yes, this would be great. The ability for a new backend-version to
inherit most of the SQL from a common class would be very powerful, and
save a lot of time in writing the new subclass.
Thanks for the ideas, I need to mull...
-Jon
--
-**-*-*---*-*---*-*---*-*-*-*---*-*---*-*-*-*-*---
Jon Lapham [EMAIL PROTECTED]Rio de Janeiro, Brasil
Personal: http://www.jandr.org/
***-*--**---***---
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 v4 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 [EMAIL PROTECTED]Rio de Janeiro, Brasil
Personal: http://www.jandr.org/
***-*--**---***---
Re: [EMAIL PROTECTED]: elegent way to handle pluggable backend servers?]
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 v4 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
RE: elegent way to handle pluggable backend servers?
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:[EMAIL PROTECTED]
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 v4 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 [EMAIL PROTECTED]Rio de Janeiro, Brasil
Personal: http://www.jandr.org/
***-*--**---***---
