Re: DBIx::DBH - Perl extension for simplifying database connections
On Mon, 6 Dec 2004 17:23:46 +, Martyn J. Pearce
[EMAIL PROTECTED] wrote:
On Fri, Dec 03, 2004 at 11:25:53AM +, Tim Bunce wrote:
Yes. It's finely balanced though. I can see merit on both sides.
The fill in a form situation is relatively common. But beyond the
MySQL/Pg model of how servers, ports, databases work there's also
the question of specifying attributes. Taking mysql as an example,
specifying mysql_client_found_rows=1 is common (or should be :)
and mysql_local_infile=1 can be needed (at least for some versions).
Then there's SSL:
mysql_ssl=1
mysql_ssl_client_key=...
mysql_ssl_client_cert=...
mysql_ssl_ca_file=...
mysql_ssl_ca_path=...
mysql_ssl_cipher=...
I wouldn't like to be filling in all that on a form. (And there's
no need to tell me there are ways to avoid specifying all those
through config files, I'm just making a general point here :)
I try to make the DBI design hit the sweet spot of general utility
(while still enabling driver-specific features in a way that's portable).
It's clear to me that the DBI hasn't quite hit the sweet spot here.
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. If the connect call were to accept a hashref as
an *alternative* to the current simple string, then there would be no danger
of backwards-compatibility issues cropping up, and would fit in well with what
people are already using. That so many people have individually come to the
same solution suggests that there's something right with it.
my $dbh = DBI-connect({db = 'postgres', dbname = 'foo', port = 6767});
Mx.
And this, in fact is what I do at my place too.
my $dbh = connectDBI(driver = 'Oracle', user = 'foo', server =
'bar', database = 'baz'
RaiseError = 1, PrintError =0, AutoCommit = 0, driver_special =
{syb_show_sql =1, syb_show_eed=1});
I pull together the various bits in the call (and I support driver =
oracle too) and just call DBI-connect in the end. Since user defines
tablespace in ORacle, I ignore database- when driver eq 'Oracle'.
At the top of this thread and at various other entries, the point is
made that many times, the parameters for connection are pulled from a
source that has them listed individually. Why make the
client/programmer synthesize them - let the call do it. Yes? Make it a
requirement of the DBD implementor to take the hashed args and build
the right string and then call connect.
I do it w/o
--
Matthew O. Persico
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 connections
On Tue, Dec 07, 2004 at 12:38:12PM +, 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. Most drivers already support the foo=bar;... style in the DSN string. They'd just need to support alternative names for some attributes. As for what names to use, host and port are easy choices. For the database there's db, dbname, and database. I'd probably go with db. --- So now what all you zelots out there need to do is (gently) nag the authors of your favorite drivers to implement this change. The most gentle and effective way of doing that is to send them a patch. Shouldn't DBI proper at least have a documentation patch explaining what which parts of this should be considered 'standard'? I see that each DBD module can translate the hash into an old-fashioned DSN string themselves. Mark
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]
