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
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? -- /chris 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. -- /chris 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, 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
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
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.
