RE: DBIx::DBH - Perl extension for simplifying database connectio ns
Tim Bunce wrote on 30 November 2004 23:32
> On Tue, Nov 30, 2004 at 09:38:47PM +, Nicholas Clark wrote:
> > On Tue, Nov 30, 2004 at 08:53:51PM +, Tim Bunce wrote:
> >
> > > I don't get it. Can someone give me some small but real examples
> > > of the problem that's being solved here?
> >
> > The one that I've hit - specifying port and host, Pg vs
> Mysql (and SQlite):
> >
> > if ($dbspec->{driver} eq 'DBI:Pg') {
> > # Aaargh. Why aren't these things standarised?
> > $dsn = "DBI:Pg:host=$dbspec->{domain}";
> > # Aargh. W.T.F. is this case sensitivity here? It fails
> to connect unless
> > # the name is all lowercase (as is stored within the
> non-case preserving
> > # pg DB)
> > $dsn .= lc ";dbname=$dbspec->{db_name}" if length
> $dbspec->{db_name};
> > $dsn .= ";port=$dbspec->{port}" if defined $dbspec->{port};
> > } else {
> > $dsn .= ":port=$dbspec->{port}" if defined $dbspec->{port};
> > }
>
> It seems to me that the problem is of your own making.
> Why have separate hash elements for all these things in the
> first place?
In my case because if I connect to the same database from two different
servers with two different versions of Sybase Open Client then DBD::Sybase
and the underlying drivers need connection strings that are totally
different (and not at all intuitive IMO) but contain basically the same
data. Thus I have an ini file that contains the appropriate info along with
a special entry that determines which style to use. Now only one thing needs
to be changed between the two machines.
This is part of the code that handles building the connection string from
the ini file:
if (lc($style) eq "short") {
$fmt="DRIVER={%s};UID=%s;PWD=%s;SRVR=%s;DB=%s";
@args=qw(driver username password server dbname);
} elsif (lc($style) eq "long") {
$fmt="DRIVER={%s};UID=%s;PWD=%s;NLN=%s;NA=%s;DB=%s";
@args=qw(driver username password protocol server dbname);
And here is what the inifile looks like:
[dsn]
; this is the setting for GOBS01 PRODUCTION
style= short
driver = Sybase ASE ODBC Driver
username = him_or_me
password = uhuh
server = that_server_there
dbname = funky_db
protocol = blahblah
Now if we used two connection strings we have all of that data duplicated in
the two strings, which as we all know is a scenario just crying out for
refactoring. We in fact did do this for some time, but it was always falling
out of synch when the data needed to change and the ops folks were forever
changing the wrong string.
Ive got other examples of this. If you use DBD::ODBC you need an entirely
different connection string than when using the faster and better
DBD::Sybase which has as I already mentioned different requirements
depending on local features. Since we had some weird compatibilitiy issue
with Sybase Open Client for a while in some places our code had to fallback
to using DBD::ODBC when DBD::Sybase diver was unavailable, resulting in even
more connection string variants.
IMO the idea of this module is great and frankly resolves something that ive
heard many a folk (here and elsewhere) bitch about being one of DBI's few
annoyances. (That and $dbh->selectall_arrayref($query,{Slice=>{}}) ;-)
I will say that I agree entirely with those who argue that DBIx::DBH is a
bad name. IMO DBI itself should have a more transparent way of handling this
so an additional module is not required. Surely there is base information
that pretty well all serious DB's will require to open a connection so
mandating a driver agnostic interface to providing those details and then
letting the driver do the rest would seem to make sense.
Yves
RE: DBIx::DBH - Perl extension for simplifying database connectio ns
Orton, Yves stupidly wrote on 01 December 2004 12:54 > Tim Bunce wrote on 30 November 2004 23:32 > > On Tue, Nov 30, 2004 at 09:38:47PM +, Nicholas Clark wrote: > > > On Tue, Nov 30, 2004 at 08:53:51PM +, Tim Bunce wrote: > > > > > > > I don't get it. Can someone give me some small but real examples > > > > of the problem that's being solved here? > And here is what the inifile looks like: > > [dsn] > style= short > driver = Sybase ASE ODBC Driver > username = him_or_me > password = uhuh > server = that_server_there > dbname = funky_db > protocol = blahblah > This particular example shows the scenario of the ODBC drivers requiring different strings based on the Open Client version installed. As I mentioned we have encountered this problem with DBD::Sybase as well, and in combination with fallbacks to ODBC. I grabbed the wrong example code, but I think the fact that this confusion was even possible just underlines the point here. :-) Yves
RE: DBIx::DBH - Perl extension for simplifying database connectio ns
> It'll always come down to the issue of "why not store complete DSNs?" > and so far that's not been well covered by the feedback I've got. Duplication of data in multiple places is the answer I think. The more DSN strings you have the more needs to be changed later on, and the bigger the chance that those changes include errors. Having a single transparent interface would reduce that error (and the frustration associated with it) Cheers, Yves
Re: DBIx::DBH - Perl extension for simplifying database connectio ns
On Wed, Dec 01, 2004 at 06:43:51PM -, Orton, Yves wrote: > > > It'll always come down to the issue of "why not store complete DSNs?" > > and so far that's not been well covered by the feedback I've got. > > Duplication of data in multiple places is the answer I think. The more DSN > strings you have the more needs to be changed later on, and the bigger the > chance that those changes include errors. Having a single transparent > interface would reduce that error (and the frustration associated with it) Can you modify the example you gave earlier to show how you'd use DBIx::DBH (or whatever it's called :) to do the same thing? Tim.
RE: DBIx::DBH - Perl extension for simplifying database connectio ns
Martyn J. Pearce skrev: > On Fri, Dec 03, 2004 at 11:25:53AM +, Tim Bunce wrote: ... >> The simplest fix is to standardize one set of driver DSN attribute >> names so that at least the host, port, and database (schema) can >> be specified in a portable way. > > Is that really the simplest? It occurs that the responses on this > thread, and in my experience, many people are comfortable & familiar > with the use of a hash (/ref) for this purpose. Maybe the number of responses on this thread come from people who have this itch to scratch. I have heard Tim Bunce (DBI, DBD::Oracle etc) and Jonathan Leffler (DBD::Informix) raise 'Beware, thing are much more complex than you think' warnings. I (DBD::Ingres) have'nt pitched in as Jonathan voiced my concerns quite precisely, saying: Beware - DBMS are more different than anyone would like. That's why DBI has the scheme it does have - it is flexible but not easily codified. Ingres, like Informix and (I think) Oracle, does'nt have the concept of 'host' or 'port', using other ways of adressing remote databases. It seems to me that you are trying to force an extension onto the DBI based on what a small number of RDBMSs accept. The people who want this seem to use only a few DBDs - perhaps it could be added to those? -- Henrik Tougaard, maintainer of DBD::Ingres
RE: DBIx::DBH - Perl extension for simplifying database connectio ns
Henrik Tougaard wrote on 07 December 2004 10:59 > Martyn J. Pearce skrev: > > On Fri, Dec 03, 2004 at 11:25:53AM +, Tim Bunce wrote: > ... > >> The simplest fix is to standardize one set of driver DSN attribute > >> names so that at least the host, port, and database (schema) can > >> be specified in a portable way. > > > > Is that really the simplest? It occurs that the responses on this > > thread, and in my experience, many people are comfortable & familiar > > with the use of a hash (/ref) for this purpose. > > Maybe the number of responses on this thread come from people who > have this itch to scratch. I have heard Tim Bunce (DBI, DBD::Oracle > etc) and Jonathan Leffler (DBD::Informix) raise 'Beware, thing are > much more complex than you think' warnings. > > I (DBD::Ingres) have'nt pitched in as Jonathan voiced my concerns > quite precisely, saying: > Beware - DBMS are more different than anyone would like. > That's why DBI has the scheme it does have - it is flexible > but not easily codified. I just looked at the DBD::Informix docs. According to them Informix takes a connections string like: "dbi:Informix:$database" where $database is constructed like this: dbase # 'Local' database //machine1/dbase# Database on remote machine [EMAIL PROTECTED] # Database on (remote) server (as defined in sqlhosts) @server1# Connection to (remote) server but no database /some/where/dbase # Connect to local SE database DBD::Ingres does something similar. DBD::Oracle appears to be closer to Sybase/MySQl: "dbi:Oracle:host=myhost.com;sid=ORCL" It doesn't seem like a stretch of the imagination to see the common fields "host" and "db" embedded in all three. Clearly any DBD driver that can connect to providers on a different host will have to in some way allow the user to specify which host that is. The fact that in some particular RDDBMS's culture this isnt called the "host" and that "port" is for some reason unnecessary is IMO a bit irrelevent. The fact still remains that the generic "Host" slot could be used for this purpose quite easily, as could the "DB" slot. Those parameter that make no sense could either be ignored, or somehow usefully overloaded. This would enable the establishment of a baseline set of connection details that all DBD drivers should know how to more or less deal with. At bare minimum this would mean one less trivial piece of knowledge to remember when working with multiple providers. > Ingres, like Informix and (I think) Oracle, does'nt have the concept > of 'host' or 'port', using other ways of adressing remote databases. > > It seems to me that you are trying to force an extension onto the DBI > based on what a small number of RDBMSs accept. The people who > want this seem to use only a few DBDs - perhaps it could be added to those? Coming up with common set of parameters that most DB's are going to require and then providing standardized names for them would seem to be useful in general. So far I havent seen anyone provide something that a given driver Has To Have that doesn't fit into the proposal. (Ie, Host,DB,Port). Which _mandatory_ parameter does Informix need that can't be shoehorned into one of those? Regards, yves
RE: DBIx::DBH - Perl extension for simplifying database connectio ns
On Tue, 7 Dec 2004, Henrik Tougaard wrote: Maybe the number of responses on this thread come from people who have this itch to scratch. Huh? I've only been seeing what got cross-posted on this to module-authors until today, but I just subscribed to dbi-usres n I have heard Tim Bunce (DBI, DBD::Oracle etc) and Jonathan Leffler (DBD::Informix) raise 'Beware, thing are much more complex than you think' warnings. Yeah. I (DBD::Ingres) have'nt pitched in as Jonathan voiced my concerns quite precisely, saying: Beware - DBMS are more different than anyone would like. That's why DBI has the scheme it does have - it is flexible but not easily codified. Even if its not easy a solution for migrating DSN creation out of code and into a config file would be very worthwhile. Ingres, like Informix and (I think) Oracle, does'nt have the concept of 'host' or 'port', using other ways of adressing remote databases. Given that for most folks the support is needed for the FOSS databases ignoring the strange proprietary ways would be better than not having DSN's externalized. It seems to me that you are trying to force an extension onto the DBI based on what a small number of RDBMSs accept. When that small number just happens to be "all the FOSS databases" its a large enough small number. The people who want this seem to use only a few DBDs - perhaps it could be added to those? Maybe. Personally I'd like to see a solution based on AppConfig. We have our database configs in AppConfig. The config files look something like: [global] connpriority=dot,skippy,marita dbd=mysql tablespace=finilever user=blah pass=blah [dot] host=dot.fini.net [skippy] host=skippy.fini.net user=override pass=override [marita] host=marita.fini.net dbd=pg I don't care about Oracle or any of the rest. Making this work with PG and MySQL will solve 90% of the world's problems. I don't see why it couldn't be extended to include whatever parameters where necessary for any of the proprietary databases, but if not could somebody explain how? -- "Fans of Mozilla's free, open-source Firefox browser make the ardent Apple faithful look like a bunch of slackers." - Rebecca Lieb at clickz.com
RE: DBIx::DBH - Perl extension for simplifying database connectio ns
On Tue, 7 Dec 2004, Orton, Yves wrote: To: 'Henrik Tougaard' <[EMAIL PROTECTED]>, 'Martyn J. Pearce' <[EMAIL PROTECTED]>, Tim Bunce <[EMAIL PROTECTED]> Cc: Sam Vilain <[EMAIL PROTECTED]>, Ken Williams <[EMAIL PROTECTED]>, Terrence Brannon <[EMAIL PROTECTED]>, [EMAIL PROTECTED], [EMAIL PROTECTED] Sheesh. If you're getting twelves copies of this because you're on both mailing lists, getting cc'd and impesonating some of the other cc'ers or something I'm sorry. :) DBD::Oracle appears to be closer to Sybase/MySQl: "dbi:Oracle:host=myhost.com;sid=ORCL" It doesn't seem like a stretch of the imagination to see the common fields "host" and "db" embedded in all three. Sure. The basics are pretty universal I suspect despite some protestations to the contrary. Things like Oracle's SID should be able to fit in the architecture without bothering the rest of the world which doesn't use that. The fact still remains that the generic "Host" slot could be used for this purpose quite easily, as could the "DB" slot. I really really object to the DB slot being called DB. Oracle's term "tablespace" is much less overused and confusing than database. Those parameter that make no sense could either be ignored, or somehow usefully overloaded. Absolutely. This would enable the establishment of a baseline set of connection details that all DBD drivers should know how to more or less deal with. At bare minimum this would mean one less trivial piece of knowledge to remember when working with multiple providers. Precisely. Why I do care to memorize twelves DSN creation styles. Bah. Coming up with common set of parameters that most DB's are going to require and then providing standardized names for them would seem to be useful in general. So far I havent seen anyone provide something that a given driver Has To Have that doesn't fit into the proposal. (Ie, Host,DB,Port). Which _mandatory_ parameter does Informix need that can't be shoehorned into one of those? What does it matter? Why can't we allow for "extra" bits like SID's without breaking the core "global" values? -- "Fans of Mozilla's free, open-source Firefox browser make the ardent Apple faithful look like a bunch of slackers." - Rebecca Lieb at clickz.com
RE: DBIx::DBH - Perl extension for simplifying database connectio ns
Christopher Hicks wrote: > > Coming up with common set of parameters that most DB's are going to > > require and then providing standardized names for them would seem to be > > useful in general. So far I havent seen anyone provide something that a > > given driver Has To Have that doesn't fit into the proposal. (Ie, > > Host,DB,Port). Which _mandatory_ parameter does Informix need that can't > > be shoehorned into one of those? > > What does it matter? Why can't we allow for "extra" bits like SID's > without breaking the core "global" values? I was given the Henrik or some other hypothetical respondant the benefit of the doubt. :-) I thought it was clear I think that this is both doable and worth doing. Anyway, Cheers, Yves
RE: DBIx::DBH - Perl extension for simplifying database connectio ns
On Tue, 7 Dec 2004, Orton, Yves wrote: I was given the Henrik or some other hypothetical respondant the benefit of the doubt. I figured that out by the end of reading your email. :-) :-] I thought it was clear I think that this is both doable and worth doing. Yes yes. I didn't think there was much disagreement. -- "Fans of Mozilla's free, open-source Firefox browser make the ardent Apple faithful look like a bunch of slackers." - Rebecca Lieb at clickz.com
Re: DBIx::DBH - Perl extension for simplifying database connectio ns
Orton, Yves wrote:
DBD::Ingres does something similar. DBD::Oracle appears to be closer to
Sybase/MySQl:
"dbi:Oracle:host=myhost.com;sid=ORCL"
Normally you don't bother with connection parameters with Oracle at all:
DBI->connect("dbi:Oracle:", "database", "password");
Instead, you configure which logical Oracle installation that you want
with an environment variable. The information about where that database
actually lives is in a config file in the Oracle client installation.
So, if you actually override the environment variable by specifying the
SID in the program, you run the risk of confusing the poor SysAdmins who
look after it, as every other program honours this convention.
> Clearly any DBD driver that can connect to providers on a different
> host will have to in some way allow the user to specify which host
> that is.
Not necessarily. There might be an ORB or some other name service
locator that finds those details out at run time given something that
isn't a host name.
I think that this information should be removed from most programs
altogether. They should just have to specify a logical data source
(possibly including a schema version), then a module with a config file
maps that to a set of connection parameters.
ie, if we're going to go ahead and try to make some sense of how you
specify the parameters to the DBI connect call, why don't we go the
whole way and think about where that connection information is coming
from?
--
Sam Vilain, sam /\T vilain |><>T net, PGP key ID: 0x05B52F13
(include my PGP key ID in personal replies to avoid spam filtering)
Re: DBIx::DBH - Perl extension for simplifying database connectio ns
On Wed, 08 Dec 2004 10:21:35 +1300, Sam Vilain <[EMAIL PROTECTED]> wrote: > I think that this information should be removed from most programs > altogether. They should just have to specify a logical data source > (possibly including a schema version), then a module with a config file > maps that to a set of connection parameters. That's what DBI wrappers do, and I have one of those too. But my DBI wrapper reads its connection information for each "logical" data source from a hash. Then there's a build_dsn() method that assembles the pieces according to the name of the driver. If each DBD did that for me, I could just pass a reference to my connection info hash directly to DBI->connect() and then remove the build_dsn() code from my wrapper. -John
Re: DBIx::DBH - Perl extension for simplifying database connectio ns
On Tue, Dec 07, 2004 at 11:51:41AM -0600, Chris Josephes wrote: > > Either way, does this traffic need to be replicated on both dbi-users and > module-authors?? I would think the DBI list would supercede the other. I agree. Can anyone replying to this thread please remove [EMAIL PROTECTED] from the CC list. Tim.
Re: DBIx::DBH - Perl extension for simplifying database connectio ns
On Tue, Dec 07, 2004 at 04:40:51PM -0500, John Siracusa wrote: > On Wed, 08 Dec 2004 10:21:35 +1300, Sam Vilain <[EMAIL PROTECTED]> wrote: > > I think that this information should be removed from most programs > > altogether. They should just have to specify a logical data source > > (possibly including a schema version), then a module with a config file > > maps that to a set of connection parameters. > > That's what DBI wrappers do, and I have one of those too. But my DBI > wrapper reads its connection information for each "logical" data source from > a hash. Then there's a build_dsn() method that assembles the pieces > according to the name of the driver. > > If each DBD did that for me, I could just pass a reference to my connection > info hash directly to DBI->connect() and then remove the build_dsn() code > from my wrapper. Assuming that each DBD used the same names for elements with the same meaning (database vs dbname vs db vs sid etc etc). Tim.
RE: DBIx::DBH - Perl extension for simplifying database connectio ns
On Tue, 7 Dec 2004, Christopher Hicks wrote: > I don't care about Oracle or any of the rest. Making this work with PG > and MySQL will solve 90% of the world's problems. I don't see why it > couldn't be extended to include whatever parameters where necessary for > any of the proprietary databases, but if not could somebody explain how? I thought the inspiration for this module started because of Sybase connectivity. If you're only going to try to accomodate 90% of all of the databases, then you're just going to get another guy out there that wants to fix the remaining 10%. I work with Oracle 91 RAC databases myself. Either way, does this traffic need to be replicated on both dbi-users and module-authors?? I would think the DBI list would supercede the other. Christopher Josephes [EMAIL PROTECTED]
Re: DBIx::DBH - Perl extension for simplifying database connectio ns
On Fri, Dec 17, 2004 at 03:17:25AM +, Terrence Brannon wrote: > Christopher Hicks <[EMAIL PROTECTED]> writes: > > > Personally I'd like to see a solution based on AppConfig. We have our > > database configs in AppConfig. The config files look something like: > > I did that two years ago: > > http://search.cpan.org/author/TBONE/DBIx-Connect-1.13/lib/DBIx/Connect.pm > > However I got sick of AppConfig because I found it unwieldy. I then > wrote a new module based on Config::ApacheFormat. Now, I decided to > write something based on pure Perl data structures so that people > could use whatever config module and nest/wrap/merge as they > please. Hence DBIx::DBH Funny how some things change over time. I wonder what'll be next... ;-) Tim.
Re: DBIx::DBH - Perl extension for simplifying database connectio ns
Christopher Hicks <[EMAIL PROTECTED]> writes: > Personally I'd like to see a solution based on AppConfig. We have our > database configs in AppConfig. The config files look something like: > I did that two years ago: http://search.cpan.org/author/TBONE/DBIx-Connect-1.13/lib/DBIx/Connect.pm However I got sick of AppConfig because I found it unwieldy. I then wrote a new module based on Config::ApacheFormat. Now, I decided to write something based on pure Perl data structures so that people could use whatever config module and nest/wrap/merge as they please. Hence DBIx::DBH
