A recent survey of CPAN DBI connect colutions and an even more recent rehash of the subject prompts me to mention what I have found out with daily use of DBIx::Connect. One day someone came up to me and stated that I need to do processing across 7 new databases on the same mysql server. Ideally, a form of inheritance or defaulting would have kicked in and I should have been able to say : for all databases on this machine, use this username and password. But instead, I had to copy and paste the same uname and passwd 6 times and create a total of 7 labels.

I want to exemplify the weakness of DBIx::Connect. Let's say that you have a dev and production database: 

[basic]
 user= postgres
 pass   = <STDIN>
 dsn= dbi:Pg:dbname=mydb
 attr RaiseError =  0
 attr PrintError =  0
 attr Taint      =  1

 [dev_db]
 user = root
 pass = w00t!
 dsn = dbi:mysql:database=mysqldb;host=localhost
 attr RaiseError = 1
 attr PrintError = 1

[download]

Once you are setup like that you can simply connect to the database you want via DBIx::Connect->to('dev_db');

But now imagine that you come back from a refreshing 2-week vacation in the Bahamas and dev_db now has 7 databases that you must process. With the current use of AppConfig as the underlying config module, there does not appear to be an easy way to create user and passdefaults for all databases at a certain host. So, you have to copy and paste the same user and password information for 7 new entries and anyone that has been programming is very leery of cutting and pasting anything --- there should always be some programmatic means of reuse, be it a subroutine or inheritance of a default or whatever.

When dragonchild says that he has lots and lots of machines to deal with, such defaulting and inheritance is important and it is why I must use Resources for any future version of database conection package that I develop. The author of this package is AWOL and I have upgraded it to pass the test suite on new versions of Perl and eliminated all warnings... I want to add in some DBI connection support and rename it Config::Resources so that more people looking for a config package can find it.

Carter's compass: I know I'm on the right track when by deleting something, I'm adding functionality.

Replies are listed 'Best First'.
Re: I swim in a sea of databases and DBIx::Connect is barely adequate
by perrin (Chancellor) on Oct 16, 2003 at 15:22 UTC
    It seems to me that you are creating this problem yourself by using AppConfig. You should either extend AppConfig to support inheritance between sections, or use something else. For example, I would do something like this:

    my %db_common = ( user     => 'a',
                  pass     => 'b',
                  host     => 'c',
                  database => 'd',
                );

my %db_foo = ( %db_common, database => 'e' );
my %db_bar = ( %db_common, database => 'f' );

    [download]
[reply]
[d/l]
      Having reflected on your comments for several days, I have realized the following:
      1. I was hoping to have all means of facilitating database connections doable in configuration, not application. This is the mantra and motivation for Net::FTP::Common.
      2. I believe the keyword in your above comments is extend. If DBIx::Connect is properly designed then there should be someway to subclass it with Local::DBIx::Connectso that the assorted functions which retrieve connection information and use it to mske dbhs can be specialized for the specifics of my current work domain.

        This is not a bad option at all... I just need to look into my API (which has no documentation on subclassing) and

        1. document the means of subclassing and its motivation and
        2. make sure that it can be done easily via AppConfig

      Carter's compass: I know I'm on the right track when by deleting something, I'm adding functionality.

[reply]
[d/l]
[select]
        I think you can do it all just by switching config modules. If you use Config::ApacheFormat, for example, you can have defaults and override them: 
         # default
 user postgres
 pass <STDIN>
 dsn  dbi:Pg:dbname=mydb
 attr RaiseError=0
 attr PrintError=0
 attr Taint=1

 <Database dev_db>
 user root
 # the rest is inherited from defaults
 </Database>

        [download]
[reply]
[d/l]
Re: I swim in a sea of databases and DBIx::Connect is barely adequate
by Abigail-II (Bishop) on Oct 16, 2003 at 14:54 UTC
    I really can't see this as an important issue. And frankly, I would copy the username and password seven times. Because I would treat the fact that the username and passwords are the same as a coincidence. If the usernames and passwords were different (which, if I were the database admin, they would), you had to write down seven usernames and passwords anyway.

    I've been a database administrator, and I had to deal with over 20 database servers with hundreds of databases, with new servers popping up and old ones disappearing all the time. All configuration information I needed for my admin scripts was kept in a single Perl datastructure (a nested hash), and used whenever needed. Adding an entry for a new server or database was a matter of less than a minute (just copy a subhash, change the values), so even if you had a couple of new servers a day, you still didn't need to spend a lot of time updating the configuration.

    Abigail

[reply]
[d/l]
Re: I swim in a sea of databases and DBIx::Connect is barely adequate
by dragonchild (Archbishop) on Oct 16, 2003 at 14:51 UTC
    Here's another question - I have a bunch of databases, but I also have the situation where, depending on what I'm doing, I need to connect to DB foo as bar one time, but baz another. I wasn't able to find in DBIx::Connect's docs how that would happen, programatically. (Nearly every Perl action at the place I'm at happens in batch mode ...)

    ------
    We are the carpenters and bricklayers of the Information Age.

    The idea is a little like C++ templates, except not quite so brain-meltingly complicated. -- TheDamian, Exegesis 6

    ... strings and arrays will suffice. As they are easily available as native data types in any sane language, ... - blokhead, speaking on evolutionary algorithms

    Please remember that I'm crufty and crochety. All opinions are purely mine and all code is untested, unless otherwise specified.

[reply]
      I have a bunch of databases, but I also have the situation where, depending on what I'm doing, I need to connect to DB foo as bar one time, but baz another. I wasn't able to find in DBIx::Connect's docs how that would happen, programatically.
      You're right, programmatically is out the window. You would have to have a label for each case, e.g: 
      [hohoho_as_foo]
dsn=dbi:mysql:dbname=hohoho
user=foo
pass=foozle

[hohoho_as_bar
dsn=dbi:mysql:dbname=hohoho
user=bar
pass=baz

      [download]
      Of course, again, with Resources, you would be able to default and inherit as one jolly well pleases, which is why I have my eyes set on it for a rewrite/forking of DBIx::Connect

      Carter's compass: I know I'm on the right track when by deleting something, I'm adding functionality.

[reply]
[d/l]

Back to Meditations