22

✏️ Editing: DBD::DBM.3pm

.\" Automatically generated by Pod::Man 4.11 (Pod::Simple 3.35)
.\"
.\" Standard preamble:
.\" ========================================================================
.de Sp \" Vertical space (when we can't use .PP)
.if t .sp .5v
.if n .sp
..
.de Vb \" Begin verbatim text
.ft CW
.nf
.ne \\$1
..
.de Ve \" End verbatim text
.ft R
.fi
..
.\" Set up some character translations and predefined strings.  \*(-- will
.\" give an unbreakable dash, \*(PI will give pi, \*(L" will give a left
.\" double quote, and \*(R" will give a right double quote.  \*(C+ will
.\" give a nicer C++.  Capital omega is used to do unbreakable dashes and
.\" therefore won't be available.  \*(C` and \*(C' expand to `' in nroff,
.\" nothing in troff, for use with C<>.
.tr \(*W-
.ds C+ C\v'-.1v'\h'-1p'\s-2+\h'-1p'+\s0\v'.1v'\h'-1p'
.ie n \{\
.    ds -- \(*W-
.    ds PI pi
.    if (\n(.H=4u)&(1m=24u) .ds -- \(*W\h'-12u'\(*W\h'-12u'-\" diablo 10 pitch
.    if (\n(.H=4u)&(1m=20u) .ds -- \(*W\h'-12u'\(*W\h'-8u'-\"  diablo 12 pitch
.    ds L" ""
.    ds R" ""
.    ds C` ""
.    ds C' ""
'br\}
.el\{\
.    ds -- \|\(em\|
.    ds PI \(*p
.    ds L" ``
.    ds R" ''
.    ds C`
.    ds C'
'br\}
.\"
.\" Escape single quotes in literal strings from groff's Unicode transform.
.ie \n(.g .ds Aq \(aq
.el       .ds Aq '
.\"
.\" If the F register is >0, we'll generate index entries on stderr for
.\" titles (.TH), headers (.SH), subsections (.SS), items (.Ip), and index
.\" entries marked with X<> in POD.  Of course, you'll have to process the
.\" output yourself in some meaningful fashion.
.\"
.\" Avoid warning from groff about undefined register 'F'.
.de IX
..
.nr rF 0
.if \n(.g .if rF .nr rF 1
.if (\n(rF:(\n(.g==0)) \{\
.    if \nF \{\
.        de IX
.        tm Index:\\$1\t\\n%\t"\\$2"
..
.        if !\nF==2 \{\
.            nr % 0
.            nr F 2
.        \}
.    \}
.\}
.rr rF
.\" ========================================================================
.\"
.IX Title "DBD::DBM 3"
.TH DBD::DBM 3 "2013-09-08" "perl v5.26.3" "User Contributed Perl Documentation"
.\" For nroff, turn off justification.  Always turn off hyphenation; it makes
.\" way too many mistakes in technical documents.
.if n .ad l
.nh
.SH "NAME"
DBD::DBM \- a DBI driver for DBM & MLDBM files
.SH "SYNOPSIS"
.IX Header "SYNOPSIS"
.Vb 5
\& use DBI;
\& $dbh = DBI\->connect(\*(Aqdbi:DBM:\*(Aq);                    # defaults to SDBM_File
\& $dbh = DBI\->connect(\*(AqDBI:DBM(RaiseError=1):\*(Aq);      # defaults to SDBM_File
\& $dbh = DBI\->connect(\*(Aqdbi:DBM:dbm_type=DB_File\*(Aq);    # defaults to DB_File
\& $dbh = DBI\->connect(\*(Aqdbi:DBM:dbm_mldbm=Storable\*(Aq);  # MLDBM with SDBM_File
\&
\& # or
\& $dbh = DBI\->connect(\*(Aqdbi:DBM:\*(Aq, undef, undef);
\& $dbh = DBI\->connect(\*(Aqdbi:DBM:\*(Aq, undef, undef, {
\&     f_ext              => \*(Aq.db/r\*(Aq,
\&     f_dir              => \*(Aq/path/to/dbfiles/\*(Aq,
\&     f_lockfile         => \*(Aq.lck\*(Aq,
\&     dbm_type           => \*(AqBerkeleyDB\*(Aq,
\&     dbm_mldbm          => \*(AqFreezeThaw\*(Aq,
\&     dbm_store_metadata => 1,
\&     dbm_berkeley_flags => {
\&         \*(Aq\-Cachesize\*(Aq => 1000, # set a ::Hash flag
\&     },
\& });
.Ve
.PP
and other variations on \fBconnect()\fR as shown in the \s-1DBI\s0 docs,
DBD::File metadata and \*(L"Metadata\*(R"
shown below.
.PP
Use standard \s-1DBI\s0 prepare, execute, fetch, placeholders, etc.,
see \*(L"\s-1QUICK START\*(R"\s0 for an example.
.SH "DESCRIPTION"
.IX Header "DESCRIPTION"
\&\s-1DBD::DBM\s0 is a database management system that works right out of the
box.  If you have a standard installation of Perl and \s-1DBI\s0 you can
begin creating, accessing, and modifying simple database tables
without any further modules.  You can add other modules (e.g.,
SQL::Statement, DB_File etc) for improved functionality.
.PP
The module uses a \s-1DBM\s0 file storage layer.  \s-1DBM\s0 file storage is common on
many platforms and files can be created with it in many programming
languages using different APIs. That means, in addition to creating
files with \s-1DBI/SQL,\s0 you can also use \s-1DBI/SQL\s0 to access and modify files
created by other \s-1DBM\s0 modules and programs and vice versa. \fBNote\fR that
in those cases it might be necessary to use a common subset of the
provided features.
.PP
\&\s-1DBM\s0 files are stored in binary format optimized for quick retrieval
when using a key field.  That optimization can be used advantageously
to make \s-1DBD::DBM SQL\s0 operations that use key fields very fast.  There
are several different \*(L"flavors\*(R" of \s-1DBM\s0 which use different storage
formats supported by perl modules such as SDBM_File and \s-1MLDBM.\s0  This
module supports all of the flavors that perl supports and, when used
with \s-1MLDBM,\s0 supports tables with any number of columns and insertion
of Perl objects into tables.
.PP
\&\s-1DBD::DBM\s0 has been tested with the following \s-1DBM\s0 types: SDBM_File,
NDBM_File, ODBM_File, GDBM_File, DB_File, BerkeleyDB.  Each type was
tested both with and without \s-1MLDBM\s0 and with the Data::Dumper,
Storable, FreezeThaw, \s-1YAML\s0 and \s-1JSON\s0 serializers using the DBI::SQL::Nano
or the SQL::Statement engines.
.SH "QUICK START"
.IX Header "QUICK START"
\&\s-1DBD::DBM\s0 operates like all other \s-1DBD\s0 drivers \- it's basic syntax and
operation is specified by \s-1DBI.\s0  If you're not familiar with \s-1DBI,\s0 you should
start by reading \s-1DBI\s0 and the documents it points to and then come back
and read this file.  If you are familiar with \s-1DBI,\s0 you already know most of
what you need to know to operate this module.  Just jump in and create a
test script something like the one shown below.
.PP
You should be aware that there are several options for the \s-1SQL\s0 engine
underlying \s-1DBD::DBM,\s0 see \*(L"Supported \s-1SQL\s0 syntax\*(R".  There are also many
options for \s-1DBM\s0 support, see especially the section on \*(L"Adding
multi-column support with \s-1MLDBM\*(R"\s0.
.PP
But here's a sample to get you started.
.PP
.Vb 10
\& use DBI;
\& my $dbh = DBI\->connect(\*(Aqdbi:DBM:\*(Aq);
\& $dbh\->{RaiseError} = 1;
\& for my $sql( split /;\en+/,"
\&     CREATE TABLE user ( user_name TEXT, phone TEXT );
\&     INSERT INTO user VALUES (\*(AqFred Bloggs\*(Aq,\*(Aq233\-7777\*(Aq);
\&     INSERT INTO user VALUES (\*(AqSanjay Patel\*(Aq,\*(Aq777\-3333\*(Aq);
\&     INSERT INTO user VALUES (\*(AqJunk\*(Aq,\*(Aqxxx\-xxxx\*(Aq);
\&     DELETE FROM user WHERE user_name = \*(AqJunk\*(Aq;
\&     UPDATE user SET phone = \*(Aq999\-4444\*(Aq WHERE user_name = \*(AqSanjay Patel\*(Aq;
\&     SELECT * FROM user
\& "){
\&     my $sth = $dbh\->prepare($sql);
\&     $sth\->execute;
\&     $sth\->dump_results if $sth\->{NUM_OF_FIELDS};
\& }
\& $dbh\->disconnect;
.Ve
.SH "USAGE"
.IX Header "USAGE"
This section will explain some usage cases in more detail. To get an
overview about the available attributes, see \*(L"Metadata\*(R".
.SS "Specifying Files and Directories"
.IX Subsection "Specifying Files and Directories"
\&\s-1DBD::DBM\s0 will automatically supply an appropriate file extension for the
type of \s-1DBM\s0 you are using.  For example, if you use SDBM_File, a table
called \*(L"fruit\*(R" will be stored in two files called \*(L"fruit.pag\*(R" and
\&\*(L"fruit.dir\*(R".  You should \fBnever\fR specify the file extensions in your \s-1SQL\s0
statements.
.PP
\&\s-1DBD::DBM\s0 recognizes following default extensions for following types:
.IP ".pag/r" 4
.IX Item ".pag/r"
Chosen for dbm_type \f(CW\*(C`SDBM_File\*(C'\fR, \f(CW\*(C`ODBM_File\*(C'\fR and \f(CW\*(C`NDBM_File\*(C'\fR
when an implementation is detected which wraps \f(CW\*(C`\-ldbm\*(C'\fR for
\&\f(CW\*(C`NDBM_File\*(C'\fR (e.g. Solaris, \s-1AIX, ...\s0).
.Sp
For those types, the \f(CW\*(C`.dir\*(C'\fR extension is recognized, too (for being
deleted when dropping a table).
.IP ".db/r" 4
.IX Item ".db/r"
Chosen for dbm_type \f(CW\*(C`NDBM_File\*(C'\fR when an implementation is detected
which wraps BerkeleyDB 1.x for \f(CW\*(C`NDBM_File\*(C'\fR (typically \s-1BSD\s0's, Darwin).
.PP
\&\f(CW\*(C`GDBM_File\*(C'\fR, \f(CW\*(C`DB_File\*(C'\fR and \f(CW\*(C`BerkeleyDB\*(C'\fR don't usually
use a file extension.
.PP
If your \s-1DBM\s0 type uses an extension other than one of the recognized
types of extensions, you should set the \fIf_ext\fR attribute to the
extension \fBand\fR file a bug report as described in \s-1DBI\s0 with the name
of the implementation and extension so we can add it to \s-1DBD::DBM.\s0
Thanks in advance for that :\-).
.PP
.Vb 2
\&  $dbh = DBI\->connect(\*(Aqdbi:DBM:f_ext=.db\*(Aq);  # .db extension is used
\&  $dbh = DBI\->connect(\*(Aqdbi:DBM:f_ext=\*(Aq);     # no extension is used
\&
\&  # or
\&  $dbh\->{f_ext}=\*(Aq.db\*(Aq;                       # global setting
\&  $dbh\->{f_meta}\->{\*(Aqqux\*(Aq}\->{f_ext}=\*(Aq.db\*(Aq;    # setting for table \*(Aqqux\*(Aq
.Ve
.PP
By default files are assumed to be in the current working directory.
To use other directories specify the \fIf_dir\fR attribute in either the
connect string or by setting the database handle attribute.
.PP
For example, this will look for the file /foo/bar/fruit (or
/foo/bar/fruit.pag for \s-1DBM\s0 types that use that extension)
.PP
.Vb 6
\&  my $dbh = DBI\->connect(\*(Aqdbi:DBM:f_dir=/foo/bar\*(Aq);
\&  # and this will too:
\&  my $dbh = DBI\->connect(\*(Aqdbi:DBM:\*(Aq);
\&  $dbh\->{f_dir} = \*(Aq/foo/bar\*(Aq;
\&  # but this is recommended
\&  my $dbh = DBI\->connect(\*(Aqdbi:DBM:\*(Aq, undef, undef, { f_dir => \*(Aq/foo/bar\*(Aq } );
\&
\&  # now you can do
\&  my $ary = $dbh\->selectall_arrayref(q{ SELECT x FROM fruit });
.Ve
.PP
You can also use delimited identifiers to specify paths directly in \s-1SQL\s0
statements.  This looks in the same place as the two examples above but
without setting \fIf_dir\fR:
.PP
.Vb 4
\&   my $dbh = DBI\->connect(\*(Aqdbi:DBM:\*(Aq);
\&   my $ary = $dbh\->selectall_arrayref(q{
\&       SELECT x FROM "/foo/bar/fruit"
\&   });
.Ve
.PP
You can also tell \s-1DBD::DBM\s0 to use a specified path for a specific table:
.PP
.Vb 1
\&  $dbh\->{dbm_tables}\->{f}\->{file} = q(/foo/bar/fruit);
.Ve
.PP
Please be aware that you cannot specify this during connection.
.PP
If you have SQL::Statement installed, you can use table aliases:
.PP
.Vb 4
\&   my $dbh = DBI\->connect(\*(Aqdbi:DBM:\*(Aq);
\&   my $ary = $dbh\->selectall_arrayref(q{
\&       SELECT f.x FROM "/foo/bar/fruit" AS f
\&   });
.Ve
.PP
See the \*(L"\s-1GOTCHAS AND WARNINGS\*(R"\s0 for using \s-1DROP\s0 on tables.
.SS "Table locking and \fBflock()\fP"
.IX Subsection "Table locking and flock()"
Table locking is accomplished using a lockfile which has the same
basename as the table's file but with the file extension '.lck' (or a
lockfile extension that you supply, see below).  This lock file is
created with the table during a \s-1CREATE\s0 and removed during a \s-1DROP.\s0
Every time the table itself is opened, the lockfile is \fBflocked()\fR.  For
\&\s-1SELECT,\s0 this is a shared lock.  For all other operations, it is an
exclusive lock (except when you specify something different using the
\&\fIf_lock\fR attribute).
.PP
Since the locking depends on \fBflock()\fR, it only works on operating
systems that support \fBflock()\fR.  In cases where \fBflock()\fR is not
implemented, \s-1DBD::DBM\s0 will simply behave as if the \fBflock()\fR had
occurred although no actual locking will happen.  Read the
documentation for \fBflock()\fR for more information.
.PP
Even on those systems that do support \fBflock()\fR, locking is only
advisory \- as is always the case with \fBflock()\fR.  This means that if
another program tries to access the table file while \s-1DBD::DBM\s0 has the
table locked, that other program will *succeed* at opening unless
it is also using flock on the '.lck' file.  As a result \s-1DBD::DBM\s0's
locking only really applies to other programs using \s-1DBD::DBM\s0 or other
program written to cooperate with \s-1DBD::DBM\s0 locking.
.SS "Specifying the \s-1DBM\s0 type"
.IX Subsection "Specifying the DBM type"
Each \*(L"flavor\*(R" of \s-1DBM\s0 stores its files in a different format and has
different capabilities and limitations. See AnyDBM_File for a
comparison of \s-1DBM\s0 types.
.PP
By default, \s-1DBD::DBM\s0 uses the \f(CW\*(C`SDBM_File\*(C'\fR type of storage since
\&\f(CW\*(C`SDBM_File\*(C'\fR comes with Perl itself. If you have other types of
\&\s-1DBM\s0 storage available, you can use any of them with \s-1DBD::DBM.\s0 It is
strongly recommended to use at least \f(CW\*(C`DB_File\*(C'\fR, because \f(CW\*(C`SDBM_File\*(C'\fR has quirks and limitations and \f(CW\*(C`ODBM_file\*(C'\fR, \f(CW\*(C`NDBM_File\*(C'\fR and \f(CW\*(C`GDBM_File\*(C'\fR are not always available.
.PP
You can specify the \s-1DBM\s0 type using the \fIdbm_type\fR attribute which can
be set in the connection string or with \f(CW\*(C`$dbh\->{dbm_type}\*(C'\fR and
\&\f(CW\*(C`$dbh\->{f_meta}\->{$table_name}\->{type}\*(C'\fR for per-table settings in
cases where a single script is accessing more than one kind of \s-1DBM\s0
file.
.PP
In the connection string, just set \f(CW\*(C`dbm_type=TYPENAME\*(C'\fR where
\&\f(CW\*(C`TYPENAME\*(C'\fR is any \s-1DBM\s0 type such as GDBM_File, DB_File, etc. Do \fInot\fR
use \s-1MLDBM\s0 as your \fIdbm_type\fR as that is set differently, see below.
.PP
.Vb 2
\& my $dbh=DBI\->connect(\*(Aqdbi:DBM:\*(Aq);                # uses the default SDBM_File
\& my $dbh=DBI\->connect(\*(Aqdbi:DBM:dbm_type=GDBM_File\*(Aq); # uses the GDBM_File
\&
\& # You can also use $dbh\->{dbm_type} to set the DBM type for the connection:
\& $dbh\->{dbm_type} = \*(AqDB_File\*(Aq;    # set the global DBM type
\& print $dbh\->{dbm_type};          # display the global DBM type
.Ve
.PP
If you have several tables in your script that use different \s-1DBM\s0
types, you can use the \f(CW$dbh\fR\->{dbm_tables} hash to store different
settings for the various tables.  You can even use this to perform
joins on files that have completely different storage mechanisms.
.PP
.Vb 2
\& # sets global default of GDBM_File
\& my $dbh\->(\*(Aqdbi:DBM:type=GDBM_File\*(Aq);
\&
\& # overrides the global setting, but only for the tables called
\& # I<foo> and I<bar>
\& my $dbh\->{f_meta}\->{foo}\->{dbm_type} = \*(AqDB_File\*(Aq;
\& my $dbh\->{f_meta}\->{bar}\->{dbm_type} = \*(AqBerkeleyDB\*(Aq;
\&
\& # prints the dbm_type for the table "foo"
\& print $dbh\->{f_meta}\->{foo}\->{dbm_type};
.Ve
.PP
\&\fBNote\fR that you must change the \fIdbm_type\fR of a table before you access
it for first time.
.SS "Adding multi-column support with \s-1MLDBM\s0"
.IX Subsection "Adding multi-column support with MLDBM"
Most of the \s-1DBM\s0 types only support two columns and even if it would
support more, \s-1DBD::DBM\s0 would only use two. However a \s-1CPAN\s0 module
called \s-1MLDBM\s0 overcomes this limitation by allowing more than two
columns.  \s-1MLDBM\s0 does this by serializing the data \- basically it puts
a reference to an array into the second column. It can also put almost
any kind of Perl object or even \fBPerl coderefs\fR into columns.
.PP
If you want more than two columns, you \fBmust\fR install \s-1MLDBM.\s0 It's available
for many platforms and is easy to install.
.PP
\&\s-1MLDBM\s0 is by default distributed with three serializers \- Data::Dumper,
Storable, and FreezeThaw. Data::Dumper is the default and Storable is the
fastest. \s-1MLDBM\s0 can also make use of user-defined serialization methods or
other serialization modules (e.g. \s-1YAML::MLDBM\s0 or
MLDBM::Serializer::JSON. You select the serializer using the
\&\fIdbm_mldbm\fR attribute.
.PP
Some examples:
.PP
.Vb 10
\& $dbh=DBI\->connect(\*(Aqdbi:DBM:dbm_mldbm=Storable\*(Aq);  # use MLDBM with Storable
\& $dbh=DBI\->connect(
\&    \*(Aqdbi:DBM:dbm_mldbm=MySerializer\*(Aq # use MLDBM with a user defined module
\& );
\& $dbh=DBI\->connect(\*(Aqdbi::dbm:\*(Aq, undef,
\&     undef, { dbm_mldbm => \*(AqYAML\*(Aq }); # use 3rd party serializer
\& $dbh\->{dbm_mldbm} = \*(AqYAML\*(Aq; # same as above
\& print $dbh\->{dbm_mldbm} # show the MLDBM serializer
\& $dbh\->{f_meta}\->{foo}\->{dbm_mldbm}=\*(AqData::Dumper\*(Aq;   # set Data::Dumper for table "foo"
\& print $dbh\->{f_meta}\->{foo}\->{mldbm}; # show serializer for table "foo"
.Ve
.PP
\&\s-1MLDBM\s0 works on top of other \s-1DBM\s0 modules so you can also set a \s-1DBM\s0 type
along with setting dbm_mldbm.  The examples above would default to using
SDBM_File with \s-1MLDBM.\s0  If you wanted GDBM_File instead, here's how:
.PP
.Vb 5
\& # uses DB_File with MLDBM and Storable
\& $dbh = DBI\->connect(\*(Aqdbi:DBM:\*(Aq, undef, undef, {
\&     dbm_type  => \*(AqDB_File\*(Aq,
\&     dbm_mldbm => \*(AqStorable\*(Aq,
\& });
.Ve
.PP
SDBM_File, the default \fIdbm_type\fR is quite limited, so if you are going to
use \s-1MLDBM,\s0 you should probably use a different type, see AnyDBM_File.
.PP
See below for some \*(L"\s-1GOTCHAS AND WARNINGS\*(R"\s0 about \s-1MLDBM.\s0
.SS "Support for Berkeley \s-1DB\s0"
.IX Subsection "Support for Berkeley DB"
The Berkeley \s-1DB\s0 storage type is supported through two different Perl
modules \- DB_File (which supports only features in old versions of Berkeley
\&\s-1DB\s0) and BerkeleyDB (which supports all versions).  \s-1DBD::DBM\s0 supports
specifying either \*(L"DB_File\*(R" or \*(L"BerkeleyDB\*(R" as a \fIdbm_type\fR, with or
without \s-1MLDBM\s0 support.
.PP
The \*(L"BerkeleyDB\*(R" dbm_type is experimental and it's interface is likely to
change.  It currently defaults to BerkeleyDB::Hash and does not currently
support ::Btree or ::Recno.
.PP
With BerkeleyDB, you can specify initialization flags by setting them in
your script like this:
.PP
.Vb 12
\& use BerkeleyDB;
\& my $env = new BerkeleyDB::Env \-Home => $dir;  # and/or other Env flags
\& $dbh = DBI\->connect(\*(Aqdbi:DBM:\*(Aq, undef, undef, {
\&     dbm_type  => \*(AqBerkeleyDB\*(Aq,
\&     dbm_mldbm => \*(AqStorable\*(Aq,
\&     dbm_berkeley_flags => {
\&         \*(AqDB_CREATE\*(Aq  => DB_CREATE,  # pass in constants
\&         \*(AqDB_RDONLY\*(Aq  => DB_RDONLY,  # pass in constants
\&         \*(Aq\-Cachesize\*(Aq => 1000,       # set a ::Hash flag
\&         \*(Aq\-Env\*(Aq       => $env,       # pass in an environment
\&     },
\& });
.Ve
.PP
Do \fInot\fR set the \-Flags or \-Filename flags as those are determined and
overwritten by the \s-1SQL\s0 (e.g. \-Flags => \s-1DB_RDONLY\s0 is set automatically
when you issue a \s-1SELECT\s0 statement).
.PP
Time has not permitted us to provide support in this release of \s-1DBD::DBM\s0
for further Berkeley \s-1DB\s0 features such as transactions, concurrency,
locking, etc. We will be working on these in the future and would value
suggestions, patches, etc.
.PP
See DB_File and BerkeleyDB for further details.
.SS "Optimizing the use of key fields"
.IX Subsection "Optimizing the use of key fields"
Most \*(L"flavors\*(R" of \s-1DBM\s0 have only two physical columns (but can contain
multiple logical columns as explained above in
\&\*(L"Adding multi-column support with \s-1MLDBM\*(R"\s0). They work similarly to a
Perl hash with the first column serving as the key. Like a Perl hash, \s-1DBM\s0
files permit you to do quick lookups by specifying the key and thus avoid
looping through all records (supported by DBI::SQL::Nano only). Also like
a Perl hash, the keys must be unique. It is impossible to create two
records with the same key.  To put this more simply and in \s-1SQL\s0 terms,
the key column functions as the \fI\s-1PRIMARY KEY\s0\fR or \s-1UNIQUE INDEX.\s0
.PP
In \s-1DBD::DBM,\s0 you can take advantage of the speed of keyed lookups by using
DBI::SQL::Nano and a \s-1WHERE\s0 clause with a single equal comparison on the key
field. For example, the following \s-1SQL\s0 statements are optimized for keyed
lookup:
.PP
.Vb 4
\& CREATE TABLE user ( user_name TEXT, phone TEXT);
\& INSERT INTO user VALUES (\*(AqFred Bloggs\*(Aq,\*(Aq233\-7777\*(Aq);
\& # ... many more inserts
\& SELECT phone FROM user WHERE user_name=\*(AqFred Bloggs\*(Aq;
.Ve
.PP
The \*(L"user_name\*(R" column is the key column since it is the first
column. The \s-1SELECT\s0 statement uses the key column in a single equal
comparison \- \*(L"user_name='Fred Bloggs'\*(R" \- so the search will find it
very quickly without having to loop through all the names which were
inserted into the table.
.PP
In contrast, these searches on the same table are not optimized:
.PP
.Vb 2
\& 1. SELECT phone FROM user WHERE user_name < \*(AqFred\*(Aq;
\& 2. SELECT user_name FROM user WHERE phone = \*(Aq233\-7777\*(Aq;
.Ve
.PP
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 \*(L"user_name\*(R" is not specified in the \s-1WHERE\s0 clause, and therefore the
search will need to loop through all rows to find the requested row(s).
.PP
\&\fBNote\fR that the underlying \s-1DBM\s0 storage needs to loop over all \fIkey/value\fR
pairs when the optimized fetch is used. SQL::Statement has a massively
improved where clause evaluation which costs around 15% of the evaluation
in DBI::SQL::Nano \- combined with the loop in the \s-1DBM\s0 storage the speed
improvement isn't so impressive.
.PP
Even if lookups are faster by around 50%, DBI::SQL::Nano and
SQL::Statement can benefit from the key field optimizations on
updating and deleting rows \- and here the improved where clause
evaluation of SQL::Statement might beat DBI::SQL::Nano every time the
where clause contains not only the key field (or more than one).
.SS "Supported \s-1SQL\s0 syntax"
.IX Subsection "Supported SQL syntax"
\&\s-1DBD::DBM\s0 uses a subset of \s-1SQL.\s0  The robustness of that subset depends on
what other modules you have installed. Both options support basic \s-1SQL\s0
operations including \s-1CREATE TABLE, DROP TABLE, INSERT, DELETE, UPDATE,\s0 and
\&\s-1SELECT.\s0
.PP
\&\fBOption #1:\fR By default, this module inherits its \s-1SQL\s0 support from
DBI::SQL::Nano that comes with \s-1DBI.\s0  Nano is, as its name implies, a *very*
small \s-1SQL\s0 engine.  Although limited in scope, it is faster than option #2
for some operations (especially single \fIprimary key\fR lookups). See
DBI::SQL::Nano for a description of the \s-1SQL\s0 it supports and comparisons
of it with option #2.
.PP
\&\fBOption #2:\fR If you install the pure Perl \s-1CPAN\s0 module SQL::Statement,
\&\s-1DBD::DBM\s0 will use it instead of Nano.  This adds support for table aliases,
functions, joins, and much more.  If you're going to use \s-1DBD::DBM\s0
for anything other than very simple tables and queries, you should install
SQL::Statement.  You don't have to change \s-1DBD::DBM\s0 or your scripts in any
way, simply installing SQL::Statement will give you the more robust \s-1SQL\s0
capabilities without breaking scripts written for DBI::SQL::Nano.  See
SQL::Statement for a description of the \s-1SQL\s0 it supports.
.PP
To find out which \s-1SQL\s0 module is working in a given script, you can use the
\&\fBdbm_versions()\fR method or, if you don't need the full output and version
numbers, just do this:
.PP
.Vb 1
\& print $dbh\->{sql_handler}, "\en";
.Ve
.PP
That will print out either \*(L"SQL::Statement\*(R" or \*(L"DBI::SQL::Nano\*(R".
.PP
Baring the section about optimized access to the \s-1DBM\s0 storage in mind,
comparing the benefits of both engines:
.PP
.Vb 6
\&  # DBI::SQL::Nano is faster
\&  $sth = $dbh\->prepare( "update foo set value=\*(Aqnew\*(Aq where key=15" );
\&  $sth\->execute();
\&  $sth = $dbh\->prepare( "delete from foo where key=27" );
\&  $sth\->execute();
\&  $sth = $dbh\->prepare( "select * from foo where key=\*(Aqabc\*(Aq" );
\&
\&  # SQL::Statement might faster (depending on DB size)
\&  $sth = $dbh\->prepare( "update foo set value=\*(Aqnew\*(Aq where key=?" );
\&  $sth\->execute(15);
\&  $sth = $dbh\->prepare( "update foo set value=? where key=15" );
\&  $sth\->execute(\*(Aqnew\*(Aq);
\&  $sth = $dbh\->prepare( "delete from foo where key=?" );
\&  $sth\->execute(27);
\&
\&  # SQL::Statement is faster
\&  $sth = $dbh\->prepare( "update foo set value=\*(Aqnew\*(Aq where value=\*(Aqold\*(Aq" );
\&  $sth\->execute();
\&  # must be expressed using "where key = 15 or key = 27 or key = 42 or key = \*(Aqabc\*(Aq"
\&  # in DBI::SQL::Nano
\&  $sth = $dbh\->prepare( "delete from foo where key in (15,27,42,\*(Aqabc\*(Aq)" );
\&  $sth\->execute();
\&  # must be expressed using "where key > 10 and key < 90" in DBI::SQL::Nano
\&  $sth = $dbh\->prepare( "select * from foo where key between (10,90)" );
\&  $sth\->execute();
\&
\&  # only SQL::Statement can handle
\&  $sth\->prepare( "select * from foo,bar where foo.name = bar.name" );
\&  $sth\->execute();
\&  $sth\->prepare( "insert into foo values ( 1, \*(Aqfoo\*(Aq ), ( 2, \*(Aqbar\*(Aq )" );
\&  $sth\->execute();
.Ve
.SS "Specifying Column Names"
.IX Subsection "Specifying Column Names"
\&\s-1DBM\s0 files don't have a standard way to store column names.   \s-1DBD::DBM\s0 gets
around this issue with a \s-1DBD::DBM\s0 specific way of storing the column names.
\&\fBIf you are working only with \s-1DBD::DBM\s0 and not using files created by or
accessed with other \s-1DBM\s0 programs, you can ignore this section.\fR
.PP
\&\s-1DBD::DBM\s0 stores column names as a row in the file with the key \fI_metadata
\&\e0\fR.  So this code
.PP
.Vb 3
\& my $dbh = DBI\->connect(\*(Aqdbi:DBM:\*(Aq);
\& $dbh\->do("CREATE TABLE baz (foo CHAR(10), bar INTEGER)");
\& $dbh\->do("INSERT INTO baz (foo,bar) VALUES (\*(Aqzippy\*(Aq,1)");
.Ve
.PP
Will create a file that has a structure something like this:
.PP
.Vb 2
\&  _metadata \e0 | <dbd_metadata><schema></schema><col_names>foo,bar</col_names></dbd_metadata>
\&  zippy        | 1
.Ve
.PP
The next time you access this table with \s-1DBD::DBM,\s0 it will treat the
\&\fI_metadata \e0\fR 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 \s-1DBD::DBM,\s0 the row will be treated as a regular data row.
.PP
If you do not want the column names stored as a data row in the table you
can set the \fIdbm_store_metadata\fR attribute to 0.
.PP
.Vb 1
\& my $dbh = DBI\->connect(\*(Aqdbi:DBM:\*(Aq, undef, undef, { dbm_store_metadata => 0 });
\&
\& # or
\& $dbh\->{dbm_store_metadata} = 0;
\&
\& # or for per\-table setting
\& $dbh\->{f_meta}\->{qux}\->{dbm_store_metadata} = 0;
.Ve
.PP
By default, \s-1DBD::DBM\s0 assumes that you have two columns named \*(L"k\*(R" and \*(L"v\*(R"
(short for \*(L"key\*(R" and \*(L"value\*(R").  So if you have \fIdbm_store_metadata\fR set to
1 and you want to use alternate column names, you need to specify the
column names like this:
.PP
.Vb 4
\& my $dbh = DBI\->connect(\*(Aqdbi:DBM:\*(Aq, undef, undef, {
\&     dbm_store_metadata => 0,
\&     dbm_cols => [ qw(foo bar) ],
\& });
\&
\& # or
\& $dbh\->{dbm_store_metadata} = 0;
\& $dbh\->{dbm_cols}           = \*(Aqfoo,bar\*(Aq;
\&
\& # or to set the column names on per\-table basis, do this:
\& # sets the column names only for table "qux"
\& $dbh\->{f_meta}\->{qux}\->{dbm_store_metadata} = 0;
\& $dbh\->{f_meta}\->{qux}\->{col_names}          = [qw(foo bar)];
.Ve
.PP
If you have a file that was created by another \s-1DBM\s0 program or created with
\&\fIdbm_store_metadata\fR set to zero and you want to convert it to using
\&\s-1DBD::DBM\s0's column name storage, just use one of the methods above to name
the columns but *without* specifying \fIdbm_store_metadata\fR as zero.  You
only have to do that once \- thereafter you can get by without setting
either \fIdbm_store_metadata\fR or setting \fIdbm_cols\fR because the names will
be stored in the file.
.SH "DBI database handle attributes"
.IX Header "DBI database handle attributes"
.SS "Metadata"
.IX Subsection "Metadata"
\fIStatement handle ($sth) attributes and methods\fR
.IX Subsection "Statement handle ($sth) attributes and methods"
.PP
Most statement handle attributes such as \s-1NAME, NUM_OF_FIELDS,\s0 etc. are
available only after an execute.  The same is true of \f(CW$sth\fR\->rows which is
available after the execute but does \fInot\fR require a fetch.
.PP
\fIDriver handle ($dbh) attributes\fR
.IX Subsection "Driver handle ($dbh) attributes"
.PP
It is not supported anymore to use dbm-attributes without the dbm_\-prefix.
Currently, if an \s-1DBD::DBM\s0 private attribute is accessed without an
underscore in it's name, dbm_ is prepended to that attribute and it's
processed further. If the resulting attribute name is invalid, an error is
thrown.
.PP
dbm_cols
.IX Subsection "dbm_cols"
.PP
Contains a comma separated list of column names or an array reference to
the column names.
.PP
dbm_type
.IX Subsection "dbm_type"
.PP
Contains the \s-1DBM\s0 storage type. Currently known supported type are
\&\f(CW\*(C`ODBM_File\*(C'\fR, \f(CW\*(C`NDBM_File\*(C'\fR, \f(CW\*(C`SDBM_File\*(C'\fR, \f(CW\*(C`GDBM_File\*(C'\fR,
\&\f(CW\*(C`DB_File\*(C'\fR and \f(CW\*(C`BerkeleyDB\*(C'\fR. It is not recommended to use one
of the first three types \- even if \f(CW\*(C`SDBM_File\*(C'\fR is the most commonly
available \fIdbm_type\fR.
.PP
dbm_mldbm
.IX Subsection "dbm_mldbm"
.PP
Contains the serializer for \s-1DBM\s0 storage (value column). Requires the
\&\s-1CPAN\s0 module \s-1MLDBM\s0 installed.  Currently known supported serializers
are:
.IP "Data::Dumper" 8
.IX Item "Data::Dumper"
Default serializer. Deployed with Perl core.
.IP "Storable" 8
.IX Item "Storable"
Faster serializer. Deployed with Perl core.
.IP "FreezeThaw" 8
.IX Item "FreezeThaw"
Pure Perl serializer, requires FreezeThaw to be installed.
.IP "\s-1YAML\s0" 8
.IX Item "YAML"
Portable serializer (between languages but not architectures).
Requires \s-1YAML::MLDBM\s0 installation.
.IP "\s-1JSON\s0" 8
.IX Item "JSON"
Portable, fast serializer (between languages but not architectures).
Requires MLDBM::Serializer::JSON installation.
.PP
dbm_store_metadata
.IX Subsection "dbm_store_metadata"
.PP
Boolean value which determines if the metadata in \s-1DBM\s0 is stored or not.
.PP
dbm_berkeley_flags
.IX Subsection "dbm_berkeley_flags"
.PP
Hash reference with additional flags for BerkeleyDB::Hash instantiation.
.PP
dbm_version
.IX Subsection "dbm_version"
.PP
Readonly attribute containing the version of \s-1DBD::DBM.\s0
.PP
f_meta
.IX Subsection "f_meta"
.PP
In addition to the attributes DBD::File recognizes, \s-1DBD::DBM\s0 knows
about the (public) attributes \f(CW\*(C`col_names\*(C'\fR (\fBNote\fR not \fIdbm_cols\fR
here!), \f(CW\*(C`dbm_type\*(C'\fR, \f(CW\*(C`dbm_mldbm\*(C'\fR, \f(CW\*(C`dbm_store_metadata\*(C'\fR and
\&\f(CW\*(C`dbm_berkeley_flags\*(C'\fR.  As in DBD::File, there are undocumented,
internal attributes in \s-1DBD::DBM.\s0  Be very careful when modifying
attributes you do not know; the consequence might a destroyed or
corrupted table.
.PP
dbm_tables
.IX Subsection "dbm_tables"
.PP
This attribute provides restricted access to the table meta data. See
f_meta and \*(L"f_meta\*(R" in DBD::File for attribute details.
.PP
dbm_tables is a tied hash providing the internal table names as keys
(accessing unknown tables might create an entry) and their meta
data as another tied hash. The table meta storage is obtained via
the \f(CW\*(C`get_table_meta\*(C'\fR method from the table implementation (see
DBD::File::Developers). Attribute setting and getting within the
table meta data is handled via the methods \f(CW\*(C`set_table_meta_attr\*(C'\fR and
\&\f(CW\*(C`get_table_meta_attr\*(C'\fR.
.PP
\fIFollowing attributes are no longer handled by \s-1DBD::DBM:\s0\fR
.IX Subsection "Following attributes are no longer handled by DBD::DBM:"
.PP
dbm_ext
.IX Subsection "dbm_ext"
.PP
This attribute is silently mapped to DBD::File's attribute \fIf_ext\fR.
Later versions of \s-1DBI\s0 might show a depreciated warning when this attribute
is used and eventually it will be removed.
.PP
dbm_lockfile
.IX Subsection "dbm_lockfile"
.PP
This attribute is silently mapped to DBD::File's attribute \fIf_lockfile\fR.
Later versions of \s-1DBI\s0 might show a depreciated warning when this attribute
is used and eventually it will be removed.
.SH "DBI database handle methods"
.IX Header "DBI database handle methods"
.ie n .SS "The $dbh\->\fBdbm_versions()\fP method"
.el .SS "The \f(CW$dbh\fP\->\fBdbm_versions()\fP method"
.IX Subsection "The $dbh->dbm_versions() method"
The private method \fBdbm_versions()\fR returns a summary of what other modules
are being used at any given time.  \s-1DBD::DBM\s0 can work with or without many
other modules \- it can use either SQL::Statement or DBI::SQL::Nano as its
\&\s-1SQL\s0 engine, it can be run with \s-1DBI\s0 or DBI::PurePerl, it can use many kinds
of \s-1DBM\s0 modules, and many kinds of serializers when run with \s-1MLDBM.\s0  The
\&\fBdbm_versions()\fR method reports all of that and more.
.PP
.Vb 2
\&  print $dbh\->dbm_versions;               # displays global settings
\&  print $dbh\->dbm_versions($table_name);  # displays per table settings
.Ve
.PP
An important thing to note about this method is that when it called
with no arguments, it displays the *global* settings.  If you override
these by setting per-table attributes, these will \fInot\fR be shown
unless you specify a table name as an argument to the method call.
.SS "Storing Objects"
.IX Subsection "Storing Objects"
If you are using \s-1MLDBM,\s0 you can use \s-1DBD::DBM\s0 to take advantage of its
serializing abilities to serialize any Perl object that \s-1MLDBM\s0 can handle.
To store objects in columns, you should (but don't absolutely need to)
declare it as a column of type \s-1BLOB\s0 (the type is *currently* ignored by
the \s-1SQL\s0 engine, but it's good form).
.SH "EXTENSIBILITY"
.IX Header "EXTENSIBILITY"
.ie n .IP """SQL::Statement""" 8
.el .IP "\f(CWSQL::Statement\fR" 8
.IX Item "SQL::Statement"
Improved \s-1SQL\s0 engine compared to the built-in DBI::SQL::Nano \- see
\&\*(L"Supported \s-1SQL\s0 syntax\*(R".
.ie n .IP """DB_File""" 8
.el .IP "\f(CWDB_File\fR" 8
.IX Item "DB_File"
Berkeley \s-1DB\s0 version 1. This database library is available on many
systems without additional installation and most systems are
supported.
.ie n .IP """GDBM_File""" 8
.el .IP "\f(CWGDBM_File\fR" 8
.IX Item "GDBM_File"
Simple dbm type (comparable to \f(CW\*(C`DB_File\*(C'\fR) under the \s-1GNU\s0 license.
Typically not available (or requires extra installation) on non-GNU
operating systems.
.ie n .IP """BerkeleyDB""" 8
.el .IP "\f(CWBerkeleyDB\fR" 8
.IX Item "BerkeleyDB"
Berkeley \s-1DB\s0 version up to v4 (and maybe higher) \- requires additional
installation but is easier than GDBM_File on non-GNU systems.
.Sp
db4 comes with a many tools which allow repairing and migrating
databases.  This is the \fBrecommended\fR dbm type for production use.
.ie n .IP """MLDBM""" 8
.el .IP "\f(CWMLDBM\fR" 8
.IX Item "MLDBM"
Serializer wrapper to support more than one column for the files.
Comes with serializers using \f(CW\*(C`Data::Dumper\*(C'\fR, \f(CW\*(C`FreezeThaw\*(C'\fR and
\&\f(CW\*(C`Storable\*(C'\fR.
.ie n .IP """YAML::MLDBM""" 8
.el .IP "\f(CWYAML::MLDBM\fR" 8
.IX Item "YAML::MLDBM"
Additional serializer for \s-1MLDBM. YAML\s0 is very portable between languages.
.ie n .IP """MLDBM::Serializer::JSON""" 8
.el .IP "\f(CWMLDBM::Serializer::JSON\fR" 8
.IX Item "MLDBM::Serializer::JSON"
Additional serializer for \s-1MLDBM. JSON\s0 is very portable between languages,
probably more than \s-1YAML.\s0
.SH "GOTCHAS AND WARNINGS"
.IX Header "GOTCHAS AND WARNINGS"
Using the \s-1SQL DROP\s0 command will remove any file that has the name specified
in the command with either '.pag' and '.dir', '.db' or your {f_ext} appended
to it.  So this be dangerous if you aren't sure what file it refers to:
.PP
.Vb 1
\& $dbh\->do(qq{DROP TABLE "/path/to/any/file"});
.Ve
.PP
Each \s-1DBM\s0 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 larger than \s-1DBM\s0 can store, the results will be unpredictable.
See the documentation for whatever \s-1DBM\s0 you are using for details.
.PP
Different \s-1DBM\s0 implementations return records in different orders.
That means that you \fIshould not\fR rely on the order of records unless
you use an \s-1ORDER BY\s0 statement.
.PP
\&\s-1DBM\s0 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 \s-1CSV\s0 on platform #1 and then dumping from \s-1CSV\s0 to \s-1DBM\s0 on platform #2.
DBD::AnyData and \s-1DBD::CSV\s0 can help with that.  There may also be \s-1DBM\s0
conversion tools for your platforms which would probably be quicker.
.PP
When using \s-1MLDBM,\s0 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 eval'ed \- in other words \s-1MLDBM\s0 (or actually
Data::Dumper when used by \s-1MLDBM\s0) may take the values and try to
execute them in Perl.  Obviously, this can present dangers, so if you
do not know what is in a file, be careful before you access it with
\&\s-1MLDBM\s0 turned on!
.PP
See the entire section on \*(L"Table locking and \fBflock()\fR\*(R" for gotchas and
warnings about the use of \fBflock()\fR.
.SH "BUGS AND LIMITATIONS"
.IX Header "BUGS AND LIMITATIONS"
This module uses hash interfaces of two column file databases. While
none of supported \s-1SQL\s0 engines have support for indices, the following
statements really do the same (even if they mean something completely
different) for each dbm type which lacks \f(CW\*(C`EXISTS\*(C'\fR support:
.PP
.Vb 1
\&  $sth\->do( "insert into foo values (1, \*(Aqhello\*(Aq)" );
\&
\&  # this statement does ...
\&  $sth\->do( "update foo set v=\*(Aqworld\*(Aq where k=1" );
\&  # ... the same as this statement
\&  $sth\->do( "insert into foo values (1, \*(Aqworld\*(Aq)" );
.Ve
.PP
This is considered to be a bug and might change in a future release.
.PP
Known affected dbm types are \f(CW\*(C`ODBM_File\*(C'\fR and \f(CW\*(C`NDBM_File\*(C'\fR. We highly
recommended you use a more modern dbm type such as \f(CW\*(C`DB_File\*(C'\fR.
.SH "GETTING HELP, MAKING SUGGESTIONS, AND REPORTING BUGS"
.IX Header "GETTING HELP, MAKING SUGGESTIONS, AND REPORTING BUGS"
If you need help installing or using \s-1DBD::DBM,\s0 please write to the \s-1DBI\s0
users mailing list at dbi\-users@perl.org or to the
comp.lang.perl.modules newsgroup on usenet.  I cannot always answer
every question quickly but there are many on the mailing list or in
the newsgroup who can.
.PP
\&\s-1DBD\s0 developers for \s-1DBD\s0's which rely on DBD::File or \s-1DBD::DBM\s0 or use
one of them as an example are suggested to join the \s-1DBI\s0 developers
mailing list at dbi\-dev@perl.org and strongly encouraged to join our
\&\s-1IRC\s0 channel at <irc://irc.perl.org/dbi>.
.PP
If you have suggestions, ideas for improvements, or bugs to report, please
report a bug as described in \s-1DBI.\s0 Do not mail any of the authors directly,
you might not get an answer.
.PP
When reporting bugs, please send the output of \f(CW$dbh\fR\->dbm_versions($table)
for a table that exhibits the bug and as small a sample as you can make of
the code that produces the bug.  And of course, patches are welcome, too
:\-).
.PP
If you need enhancements quickly, you can get commercial support as
described at <http://dbi.perl.org/support/> or you can contact Jens Rehsack
at rehsack@cpan.org for commercial support in Germany.
.PP
Please don't bother Jochen Wiedmann or Jeff Zucker for support \- they
handed over further maintenance to H.Merijn Brand and Jens Rehsack.
.SH "ACKNOWLEDGEMENTS"
.IX Header "ACKNOWLEDGEMENTS"
Many, many thanks to Tim Bunce for prodding me to write this, and for
copious, wise, and patient suggestions all along the way. (Jeff Zucker)
.PP
I send my thanks and acknowledgements to H.Merijn Brand for his
initial refactoring of DBD::File and his strong and ongoing support of
SQL::Statement. Without him, the current progress would never have
been made.  And I have to name Martin J. Evans for each laugh (and
correction) of all those funny word creations I (as non-native
speaker) made to the documentation. And \- of course \- I have to thank
all those unnamed contributors and testers from the Perl
community. (Jens Rehsack)
.SH "AUTHOR AND COPYRIGHT"
.IX Header "AUTHOR AND COPYRIGHT"
This module is written by Jeff Zucker < jzucker \s-1AT\s0 cpan.org >, who also
maintained it till 2007. After that, in 2010, Jens Rehsack & H.Merijn Brand
took over maintenance.
.PP
.Vb 2
\& Copyright (c) 2004 by Jeff Zucker, all rights reserved.
\& Copyright (c) 2010\-2013 by Jens Rehsack & H.Merijn Brand, all rights reserved.
.Ve
.PP
You may freely distribute and/or modify this module under the terms of
either the \s-1GNU\s0 General Public License (\s-1GPL\s0) or the Artistic License, as
specified in the Perl \s-1README\s0 file.
.SH "SEE ALSO"
.IX Header "SEE ALSO"
\&\s-1DBI\s0,
SQL::Statement, DBI::SQL::Nano,
AnyDBM_File, DB_File, BerkeleyDB,
\&\s-1MLDBM\s0, \s-1YAML::MLDBM\s0, MLDBM::Serializer::JSON

MR SKB