SDBM_File, the default file type is quite limited, so if you are going to use MLDBM, you should probably use a different type, see AnyDBM_File.

See below for some GOTCHAS AND WARNINGS about MLDBM.

Support for Berkeley DB

The Berkeley DB storage type is supported through two different Perl modules - DB_File (which supports only features in old versions of Berkeley DB) and BerkeleyDB (which supports all versions). DBD::DBM supports specifying either ``DB_File'' or ``BerkeleyDB'' as a dbm_type, with or without MLDBM support.

The ``BerkeleyDB'' dbm_type is experimental and its interface is likely to chagne. It currently defaults to BerkeleyDB::Hash and does not currently support ::Btree or ::Recno.

With BerkeleyDB, you can specify initialization flags by setting them in your script like this:

 my $dbh = DBI->connect('dbi:DBM:type=BerkeleyDB;mldbm=Storable');
 use BerkeleyDB;
 my $env = new BerkeleyDB::Env -Home => $dir;  # and/or other Env flags
 $dbh->{dbm_berkeley_flags} = {
      'DB_CREATE'  => DB_CREATE  # pass in constants
    , 'DB_RDONLY'  => DB_RDONLY  # pass in constants
    , '-Cachesize' => 1000       # set a ::Hash flag
    , '-Env'       => $env       # pass in an environment
 };

Do not set the -Flags or -Filename flags, those are determined by the SQL (e.g. -Flags => DB_RDONLY is set automatically when you issue a SELECT statement).

Time has not permitted me to provide support in this release of DBD::DBM for further Berkeley DB features such as transactions, concurrency, locking, etc. I will be working on these in the future and would value suggestions, patches, etc.

perldoc2tree.cgi: /usr/lib/perl5/vendor_perl/5.8.8/i386-linux-thread-multi/DBD/DBM.pm: cannot resolve L in paragraph 138. perldoc2tree.cgi: /usr/lib/perl5/vendor_perl/5.8.8/i386-linux-thread-multi/DBD/DBM.pm: cannot resolve L in paragraph 138.

See DB_File and BerkeleyDB for further details.

Supported SQL syntax

DBD::DBM uses a subset of SQL. The robustness of that subset depends on what other modules you have installed. Both options support basic SQL operations including CREATE TABLE, DROP TABLE, INSERT, DELETE, UPDATE, and SELECT.

To find out which SQL module is working in a given script, you can use the dbm_versions() method or, if you don't need the full output and version numbers, just do this:

 print $dbh->{sql_handler};

That will print out either ``SQL::Statement'' or ``DBI::SQL::Nano''.

Optimizing use of key fields

Most ``flavors'' of DBM have only two physical columns (but can contain multiple logical columns as explained below). They work similarly to a Perl hash with the first column serving as the key. Like a Perl hash, DBM files permit you to do quick lookups by specifying the key and thus avoid looping through all records. Also like a Perl hash, the keys must be unique. It is impossible to create two records with the same key. To put this all more simply and in SQL terms, the key column functions as the PRIMARY KEY.

In DBD::DBM, you can take advantage of the speed of keyed lookups by using a WHERE clause with a single equal comparison on the key field. For example, the following SQL statements are optimized for keyed lookup:

 CREATE TABLE user ( user_name TEXT, phone TEXT);
 INSERT INTO user VALUES ('Fred Bloggs','233-7777');
 # ... many more inserts
 SELECT phone FROM user WHERE user_name='Fred Bloggs';

The ``user_name'' column is the key column since it is the first column. The SELECT statement uses the key column in a single equal comparision - ``user_name='Fred Bloggs' - so the search will find it very quickly without having to loop through however many names were inserted into the table.

In contrast, thes searches on the same table are not optimized:

 1. SELECT phone FROM user WHERE user_name < 'Fred';
 2. SELECT user_name FROM user WHERE phone = '233-7777';

In #1, the operation uses a less-than (<) comparison rather than an equals comparison, so it will not be optimized for key searching. In #2, the key field ``user_name'' is not specified in the WHERE clause, and therefore the search will need to loop through all rows to find the desired result.

Specifying Column Names

DBM files don't have a standard way to store column names. DBD::DBM gets around this issue with a DBD::DBM specific way of storing the column names. If you are working only with DBD::DBM and not using files created by or accessed with other DBM programs, you can ignore this section.

DBD::DBM stores column names as a row in the file with the key _metadata \0. So this code

 my $dbh = DBI->connect('dbi:DBM:');
 $dbh->do("CREATE TABLE baz (foo CHAR(10), bar INTEGER)");
 $dbh->do("INSERT INTO baz (foo,bar) VALUES ('zippy',1)");

Will create a file that has a structure something like this:

  _metadata \0 | foo,bar
  zippy        | 1

The next time you access this table with DBD::DBM, it will treat the _metadata row as a header rather than as data and will pull the column names from there. However, if you access the file with something other than DBD::DBM, the row will be treated as a regular data row.

If you do not want the column names stored as a data row in the table you can set the dbm_store_metadata attribute to 0.

 my $dbh = DBI->connect('dbi:DBM:store_metadata=0');

or

 $dbh->{dbm_store_metadata} = 0;

or, for per-table setting

 $dbh->{dbm_tables}->{qux}->{store_metadata} = 0;

By default, DBD::DBM assumes that you have two columns named ``k'' and ``v'' (short for ``key'' and ``value''). So if you have dbm_store_metadata set to 1 and you want to use alternate column names, you need to specify the column names like this:

 my $dbh = DBI->connect('dbi:DBM:store_metadata=0;cols=foo,bar');

or

 $dbh->{dbm_store_metadata} = 0;
 $dbh->{dbm_cols}           = 'foo,bar';

To set the column names on per-table basis, do this:

 $dbh->{dbm_tables}->{qux}->{store_metadata} = 0;
 $dbh->{dbm_tables}->{qux}->{cols}           = 'foo,bar';
 #
 # sets the column names only for table "qux"

If you have a file that was created by another DBM program or created with dbm_store_metadata set to zero and you want to convert it to using DBD::DBM's column name storage, just use one of the methods above to name the columns but *without* specifying dbm_store_metadata as zero. You only have to do that once - thereafter you can get by without setting either dbm_store_metadata or setting dbm_cols because the names will be stored in the file.

Statement handle ($sth) attributes and methods

Most statement handle attributes such as NAME, NUM_OF_FIELDS, etc. are available only after an execute. The same is true of $sth->rows which is available after the execute but does not require a fetch.

The $dbh->dbm_versions() method

The private method dbm_versions() presents a summary of what other modules are being used at any given time. DBD::DBM can work with or without many other modules - it can use either SQL::Statement or DBI::SQL::Nano as its SQL engine, it can be run with DBI or DBI::PurePerl, it can use many kinds of DBM modules, and many kinds of serializers when run with MLDBM. The dbm_versions() method reports on all of that and more.

  print $dbh->dbm_versions;               # displays global settings
  print $dbh->dbm_versions($table_name);  # displays per table settings

An important thing to note about this method is that when called with no arguments, it displays the *global* settings. If you over-ride these by setting per-table attributes, these will not be shown unless you specifiy a table name as an argument to the method call.

Storing Objects

If you are using MLDBM, you can use DBD::DBM to take advantage of its serializing abilities to serialize any Perl object that MLDBM can handle. To store objects in columns, you should (but don't absolutely need to) declare it as a column of type BLOB (the type is *currently* ignored by the SQL engine, but heh, it's good form).

You *must* use placeholders to insert or refer to the data.

GOTCHAS AND WARNINGS

Using the SQL DROP command will remove any file that has the name specified in the command with either '.pag' or '.dir' or your {dbm_ext} appended to it. So this be dangerous if you aren't sure what file it refers to:

 $dbh->do(qq{DROP TABLE "/path/to/any/file"});

Each DBM type has limitations. SDBM_File, for example, can only store values of less than 1,000 characters. *You* as the script author must ensure that you don't exceed those bounds. If you try to insert a value that is bigger than the DBM can store, the results will be unpredictable. See the documentation for whatever DBM you are using for details.

Different DBM implementations return records in different orders. That means that you can not depend on the order of records unless you use an ORDER BY statement. DBI::SQL::Nano does not currently support ORDER BY (though it may soon) so if you need ordering, you'll have to install SQL::Statement.

DBM data files are platform-specific. To move them from one platform to another, you'll need to do something along the lines of dumping your data to CSV on platform #1 and then dumping from CSV to DBM on platform #2. DBD::AnyData and DBD::CSV can help with that. There may also be DBM conversion tools for your platforms which would probably be quickest.

When using MLDBM, there is a very powerful serializer - it will allow you to store Perl code or objects in database columns. When these get de-serialized, they may be evaled - in other words MLDBM (or actually Data::Dumper when used by MLDBM) may take the values and try to execute them in Perl. Obviously, this can present dangers, so if you don't know what's in a file, be careful before you access it with MLDBM turned on!

See the entire section on Table locking and flock() for gotchas and warnings about the use of flock().

GETTING HELP, MAKING SUGGESTIONS, AND REPORTING BUGS

If you need help installing or using DBD::DBM, please write to the DBI users mailing list at dbi-users@perl.org or to the comp.lang.perl.modules newsgroup on usenet. I'm afraid I can't always answer these kinds of questions quickly and there are many on the mailing list or in the newsgroup who can.

If you have suggestions, ideas for improvements, or bugs to report, please write me directly at the email shown below.

When reporting bugs, please send the output of $dbh->dbm_versions($table) for a table that exhibits the bug and, if possible, as small a sample as you can make of the code that produces the bug. And of course, patches are welcome too :-).

ACKNOWLEDGEMENTS

Many, many thanks to Tim Bunce for prodding me to write this, and for copious, wise, and patient suggestions all along the way.

AUTHOR AND COPYRIGHT

This module is written and maintained by

Jeff Zucker < jzucker AT cpan.org >

Copyright (c) 2004 by Jeff Zucker, all rights reserved.

You may freely distribute and/or modify this module under the terms of either the GNU General Public License (GPL) or the Artistic License, as specified in the Perl README file.

SEE ALSO