Re: RFI: Database URIs

On Nov 26, 2013, at 12:42 AM, Tim Bunce <[email protected]> wrote:

> Why not define a direct translation from a URL to a DBI DSN?
> A translation that doesn't require knowledge of any driver-specifics.

Because I want to the onus of connecting to the database to be on the developer, not the end-user. I personally don't want to have to look up the format of the URI for every database I connect to, and why the DBI DSN and JDBC URL formats annoy me. I would rather have one solid, standard way to handle most of the stuff needed for a connection string (host, port, username, password, database name) and then pass additional stuff in a standard way (a GET query string).

So I have implemented a `dbi_dsn` method on the database URI classes I’ve written. The base class uses the most commonly-used format, which DBD::Pg, for one, relies on:

    $ perl-MURI -E 'say URI->new("db:pg://me:[email protected]/foo.db")->dbi_dsn'
    dbi:Pg:dbname=me:secret.com/foo.db

And then in subclasses I overrode appropriate methods to change the format. Here’s Ingres, for example:

    $ perl -MURI -E 'say URI->new("db:ingres://me:[email protected]/foo.db")->dbi_dsn'
    dbi:Ingres:foo.db

And here’s SQL Server:

    $ perl -Ilib -MURI -E 'say URI->new("db:sqlserver://me:[email protected]/foo.db")->dbi_dsn' 
    dbi:ODBC:Driver={SQL Server};Server=me:secret.com;Database=foo.db

Note that the URI is the same for all of these, except for the "engine" part of the scheme. Additional Engine-specific parameters can be passed in the GET string, and will simply be passed, through, e.g.,:

    $ perl -Ilib -MURI -E 'say URI->new("db:pg://me:[email protected]/foo.db??connect_timeout=10&application_name=myapp")->dbi_dsn'
    dbi:Pg:host=me:secret.com;dbname=foo.db;?connect_timeout=10;application_name=myapp

This puts the most of the onus on translating from the URL to the DBI DSN on the developer of the URI class (me in this case) rather than on the user.

Best,

David