by Hans Schou
DBURL is a notation for accessing a database in a similar way as accessing a website on Internet. In one line a website can be uniqued identified - sometimes with a port number - sometimes with a user name and password.
The goal with DBURL is to provide exactly the same.
DBURL could be implemented with database command line utilities like psql for PostgreSQL or mysql for MySQL. Here DBURL could be provided in the environment or on the command line. An example for connecting to a local PostgreSQL database via TCP/IP on port 1234 could go like:
$ export DBURL=pgsql://localhost:1234/ $ psql
Or the DBURL could be written on the command line:
$ psql dburl=pgsql://localhost:1234/
The same notation could be used for MySQL or other DBMS.
For PostgreSQL or MySQL it is not necessary to specify the scheme as this is defined within the program. The only exception is when one wants to use SSL encryption with PostgreSQL it is mandatory to specify the scheme 'pgsqls'.
If DBURL is to be used with Perl DBI it is mandatory to specify the scheme as DBI by default do not know which DBMS it should use. Because of that there is a difference of the minimum requirements in the syntax of the DBURL depending on the used program.
The DBURL syntax for DBI should as minimum provide the scheme for the DBMS to be used. All other options will have a default value. For DBI the syntax is:
[sql:]vendor://[[user][:password]@][host][:port]/[database][?sqlquery] \___scheme___/
DBI should either take DBURL as an argument or from the environment. An example where the environment variable DBURL is used:
$dbh = DBI->connect();
An example of accessing PostgreSQL with default parameters:
$dbh = DBI->connect("DBURL=pgsql://");
A full example with all parameters used:
$dbh = DBI->connect("DBURL=pgsql://user:pass@host:5432/mydb/");
The same connect string as above for MySQL:
$dbh = DBI->connect("DBURL=mysql://user:pass@host:3306/mydb/");
When using psql from the command line some default parameters can be set in the environment. These variables are: PGPORT, PGHOST, PGDATABASE, PGUSER, and PGPASSWORD. When one want to use another host it is often necessary to change several of these variables to get the right connection parameters. With a DBURL there will be only line to change and the same line could be shared with Perl DBI.
The syntax for PostgreSQL is:
[scheme://][[user[:[password]]@][host][:port][/[database[/[[table[/[column[,column...]*]]]|sql]]]]
The most simpel use of DBURL with psql is an empty string. As this can not be shared with Perl DBI it is not recommended. The simple example:
$ export DBURL="" $ psql
As minimum the scheme should be specified. In that way DBURL can be used both by psql and Perl DBI:
$ export DBURL=pgsql:// $ psql
A full example with all parameters used:
$ export DBURL=pgsql://user:pass@host:5432/mydb/ $ psql
A command line option could look like:
$ psql --dburl="pgsql://user:pass@host:5432/mydb/SELECT * FROM mytable"
When using a UNIX socket the hostname should be omitted. It looks a little strange but there is no problem with parsing it:
$ psql --dburl="pgsql://user:pass@:5432/mydb/SELECT * FROM mytable"
psql could be extended to look for both the environment variable PGURL and DBURL but that might not be recommended. In this situation PGURL would have the same syntax as DBURL.
In C the method for connecting is pset.db = PQsetdb(...). A similar method could be pset.db = PQsetdburl("pgsql://example.com/mydb/"); or pset.db = PQsetdburl(getenv("DBURL")); .
If special delimiter characters is needed in one of parameter fields they should first be splitted and then decoded. In this it is possible to use both ':' and '@' in user name and password. If the password is 'a@b' it should be encoded as 'a%40b'.
For further information see Uniform Resource Identifier (URI): Generic Syntax - Percent-Encoding.
The inspiration to the formal description of DBURL comes from PhpWiki. The syntax is not exactly the same regarding UNIX sockets.