1DBD::DBM(3)           User Contributed Perl Documentation          DBD::DBM(3)
2
3
4

NAME

6       DBD::DBM - a DBI driver for DBM & MLDBM files
7

SYNOPSIS

9        use DBI;
10        $dbh = DBI->connect('dbi:DBM:');                    # defaults to SDBM_File
11        $dbh = DBI->connect('DBI:DBM(RaiseError=1):');      # defaults to SDBM_File
12        $dbh = DBI->connect('dbi:DBM:dbm_type=DB_File');    # defaults to DB_File
13        $dbh = DBI->connect('dbi:DBM:dbm_mldbm=Storable');  # MLDBM with SDBM_File
14
15        # or
16        $dbh = DBI->connect('dbi:DBM:', undef, undef);
17        $dbh = DBI->connect('dbi:DBM:', undef, undef, {
18            f_ext              => '.db/r',
19            f_dir              => '/path/to/dbfiles/',
20            f_lockfile         => '.lck',
21            dbm_type           => 'BerkeleyDB',
22            dbm_mldbm          => 'FreezeThaw',
23            dbm_store_metadata => 1,
24            dbm_berkeley_flags => {
25                '-Cachesize' => 1000, # set a ::Hash flag
26            },
27        });
28
29       and other variations on connect() as shown in the DBI docs, DBD::File
30       metadata and "Metadata" shown below.
31
32       Use standard DBI prepare, execute, fetch, placeholders, etc., see
33       "QUICK START" for an example.
34

DESCRIPTION

36       DBD::DBM is a database management system that works right out of the
37       box.  If you have a standard installation of Perl and DBI you can begin
38       creating, accessing, and modifying simple database tables without any
39       further modules.  You can add other modules (e.g., SQL::Statement,
40       DB_File etc) for improved functionality.
41
42       The module uses a DBM file storage layer.  DBM file storage is common
43       on many platforms and files can be created with it in many programming
44       languages using different APIs. That means, in addition to creating
45       files with DBI/SQL, you can also use DBI/SQL to access and modify files
46       created by other DBM modules and programs and vice versa. Note that in
47       those cases it might be necessary to use a common subset of the
48       provided features.
49
50       DBM files are stored in binary format optimized for quick retrieval
51       when using a key field.  That optimization can be used advantageously
52       to make DBD::DBM SQL operations that use key fields very fast.  There
53       are several different "flavors" of DBM which use different storage
54       formats supported by perl modules such as SDBM_File and MLDBM.  This
55       module supports all of the flavors that perl supports and, when used
56       with MLDBM, supports tables with any number of columns and insertion of
57       Perl objects into tables.
58
59       DBD::DBM has been tested with the following DBM types: SDBM_File,
60       NDBM_File, ODBM_File, GDBM_File, DB_File, BerkeleyDB.  Each type was
61       tested both with and without MLDBM and with the Data::Dumper, Storable,
62       FreezeThaw, YAML and JSON serializers using the DBI::SQL::Nano or the
63       SQL::Statement engines.
64

QUICK START

66       DBD::DBM operates like all other DBD drivers - it's basic syntax and
67       operation is specified by DBI.  If you're not familiar with DBI, you
68       should start by reading DBI and the documents it points to and then
69       come back and read this file.  If you are familiar with DBI, you
70       already know most of what you need to know to operate this module.
71       Just jump in and create a test script something like the one shown
72       below.
73
74       You should be aware that there are several options for the SQL engine
75       underlying DBD::DBM, see "Supported SQL syntax".  There are also many
76       options for DBM support, see especially the section on "Adding multi-
77       column support with MLDBM".
78
79       But here's a sample to get you started.
80
81        use DBI;
82        my $dbh = DBI->connect('dbi:DBM:');
83        $dbh->{RaiseError} = 1;
84        for my $sql( split /;\n+/,"
85            CREATE TABLE user ( user_name TEXT, phone TEXT );
86            INSERT INTO user VALUES ('Fred Bloggs','233-7777');
87            INSERT INTO user VALUES ('Sanjay Patel','777-3333');
88            INSERT INTO user VALUES ('Junk','xxx-xxxx');
89            DELETE FROM user WHERE user_name = 'Junk';
90            UPDATE user SET phone = '999-4444' WHERE user_name = 'Sanjay Patel';
91            SELECT * FROM user
92        "){
93            my $sth = $dbh->prepare($sql);
94            $sth->execute;
95            $sth->dump_results if $sth->{NUM_OF_FIELDS};
96        }
97        $dbh->disconnect;
98

USAGE

100       This section will explain some usage cases in more detail. To get an
101       overview about the available attributes, see "Metadata".
102
103   Specifying Files and Directories
104       DBD::DBM will automatically supply an appropriate file extension for
105       the type of DBM you are using.  For example, if you use SDBM_File, a
106       table called "fruit" will be stored in two files called "fruit.pag" and
107       "fruit.dir".  You should never specify the file extensions in your SQL
108       statements.
109
110       DBD::DBM recognizes following default extensions for following types:
111
112       .pag/r
113           Chosen for dbm_type "SDBM_File", "ODBM_File" and "NDBM_File" when
114           an implementation is detected which wraps "-ldbm" for "NDBM_File"
115           (e.g. Solaris, AIX, ...).
116
117           For those types, the ".dir" extension is recognized, too (for being
118           deleted when dropping a table).
119
120       .db/r
121           Chosen for dbm_type "NDBM_File" when an implementation is detected
122           which wraps BerkeleyDB 1.x for "NDBM_File" (typically BSD's,
123           Darwin).
124
125       "GDBM_File", "DB_File" and "BerkeleyDB" don't usually use a file
126       extension.
127
128       If your DBM type uses an extension other than one of the recognized
129       types of extensions, you should set the f_ext attribute to the
130       extension and file a bug report as described in DBI with the name of
131       the implementation and extension so we can add it to DBD::DBM.  Thanks
132       in advance for that :-).
133
134         $dbh = DBI->connect('dbi:DBM:f_ext=.db');  # .db extension is used
135         $dbh = DBI->connect('dbi:DBM:f_ext=');     # no extension is used
136
137         # or
138         $dbh->{f_ext}='.db';                       # global setting
139         $dbh->{f_meta}->{'qux'}->{f_ext}='.db';    # setting for table 'qux'
140
141       By default files are assumed to be in the current working directory.
142       To use other directories specify the f_dir attribute in either the
143       connect string or by setting the database handle attribute.
144
145       For example, this will look for the file /foo/bar/fruit (or
146       /foo/bar/fruit.pag for DBM types that use that extension)
147
148         my $dbh = DBI->connect('dbi:DBM:f_dir=/foo/bar');
149         # and this will too:
150         my $dbh = DBI->connect('dbi:DBM:');
151         $dbh->{f_dir} = '/foo/bar';
152         # but this is recommended
153         my $dbh = DBI->connect('dbi:DBM:', undef, undef, { f_dir => '/foo/bar' } );
154
155         # now you can do
156         my $ary = $dbh->selectall_arrayref(q{ SELECT x FROM fruit });
157
158       You can also use delimited identifiers to specify paths directly in SQL
159       statements.  This looks in the same place as the two examples above but
160       without setting f_dir:
161
162          my $dbh = DBI->connect('dbi:DBM:');
163          my $ary = $dbh->selectall_arrayref(q{
164              SELECT x FROM "/foo/bar/fruit"
165          });
166
167       You can also tell DBD::DBM to use a specified path for a specific
168       table:
169
170         $dbh->{dbm_tables}->{f}->{file} = q(/foo/bar/fruit);
171
172       Please be aware that you cannot specify this during connection.
173
174       If you have SQL::Statement installed, you can use table aliases:
175
176          my $dbh = DBI->connect('dbi:DBM:');
177          my $ary = $dbh->selectall_arrayref(q{
178              SELECT f.x FROM "/foo/bar/fruit" AS f
179          });
180
181       See the "GOTCHAS AND WARNINGS" for using DROP on tables.
182
183   Table locking and flock()
184       Table locking is accomplished using a lockfile which has the same
185       basename as the table's file but with the file extension '.lck' (or a
186       lockfile extension that you supply, see below).  This lock file is
187       created with the table during a CREATE and removed during a DROP.
188       Every time the table itself is opened, the lockfile is flocked().  For
189       SELECT, this is a shared lock.  For all other operations, it is an
190       exclusive lock (except when you specify something different using the
191       f_lock attribute).
192
193       Since the locking depends on flock(), it only works on operating
194       systems that support flock().  In cases where flock() is not
195       implemented, DBD::DBM will simply behave as if the flock() had occurred
196       although no actual locking will happen.  Read the documentation for
197       flock() for more information.
198
199       Even on those systems that do support flock(), locking is only advisory
200       - as is always the case with flock().  This means that if another
201       program tries to access the table file while DBD::DBM has the table
202       locked, that other program will *succeed* at opening unless it is also
203       using flock on the '.lck' file.  As a result DBD::DBM's locking only
204       really applies to other programs using DBD::DBM or other program
205       written to cooperate with DBD::DBM locking.
206
207   Specifying the DBM type
208       Each "flavor" of DBM stores its files in a different format and has
209       different capabilities and limitations. See AnyDBM_File for a
210       comparison of DBM types.
211
212       By default, DBD::DBM uses the "SDBM_File" type of storage since
213       "SDBM_File" comes with Perl itself. If you have other types of DBM
214       storage available, you can use any of them with DBD::DBM. It is
215       strongly recommended to use at least "DB_File", because "SDBM_File" has
216       quirks and limitations and "ODBM_file", "NDBM_File" and "GDBM_File" are
217       not always available.
218
219       You can specify the DBM type using the dbm_type attribute which can be
220       set in the connection string or with "$dbh->{dbm_type}" and
221       "$dbh->{f_meta}->{$table_name}->{type}" for per-table settings in cases
222       where a single script is accessing more than one kind of DBM file.
223
224       In the connection string, just set "dbm_type=TYPENAME" where "TYPENAME"
225       is any DBM type such as GDBM_File, DB_File, etc. Do not use MLDBM as
226       your dbm_type as that is set differently, see below.
227
228        my $dbh=DBI->connect('dbi:DBM:');                # uses the default SDBM_File
229        my $dbh=DBI->connect('dbi:DBM:dbm_type=GDBM_File'); # uses the GDBM_File
230
231        # You can also use $dbh->{dbm_type} to set the DBM type for the connection:
232        $dbh->{dbm_type} = 'DB_File';    # set the global DBM type
233        print $dbh->{dbm_type};          # display the global DBM type
234
235       If you have several tables in your script that use different DBM types,
236       you can use the $dbh->{dbm_tables} hash to store different settings for
237       the various tables.  You can even use this to perform joins on files
238       that have completely different storage mechanisms.
239
240        # sets global default of GDBM_File
241        my $dbh->('dbi:DBM:type=GDBM_File');
242
243        # overrides the global setting, but only for the tables called
244        # I<foo> and I<bar>
245        my $dbh->{f_meta}->{foo}->{dbm_type} = 'DB_File';
246        my $dbh->{f_meta}->{bar}->{dbm_type} = 'BerkeleyDB';
247
248        # prints the dbm_type for the table "foo"
249        print $dbh->{f_meta}->{foo}->{dbm_type};
250
251       Note that you must change the dbm_type of a table before you access it
252       for first time.
253
254   Adding multi-column support with MLDBM
255       Most of the DBM types only support two columns and even if it would
256       support more, DBD::DBM would only use two. However a CPAN module called
257       MLDBM overcomes this limitation by allowing more than two columns.
258       MLDBM does this by serializing the data - basically it puts a reference
259       to an array into the second column. It can also put almost any kind of
260       Perl object or even Perl coderefs into columns.
261
262       If you want more than two columns, you must install MLDBM. It's
263       available for many platforms and is easy to install.
264
265       MLDBM is by default distributed with three serializers - Data::Dumper,
266       Storable, and FreezeThaw. Data::Dumper is the default and Storable is
267       the fastest. MLDBM can also make use of user-defined serialization
268       methods or other serialization modules (e.g. YAML::MLDBM or
269       MLDBM::Serializer::JSON. You select the serializer using the dbm_mldbm
270       attribute.
271
272       Some examples:
273
274        $dbh=DBI->connect('dbi:DBM:dbm_mldbm=Storable');  # use MLDBM with Storable
275        $dbh=DBI->connect(
276           'dbi:DBM:dbm_mldbm=MySerializer' # use MLDBM with a user defined module
277        );
278        $dbh=DBI->connect('dbi::dbm:', undef,
279            undef, { dbm_mldbm => 'YAML' }); # use 3rd party serializer
280        $dbh->{dbm_mldbm} = 'YAML'; # same as above
281        print $dbh->{dbm_mldbm} # show the MLDBM serializer
282        $dbh->{f_meta}->{foo}->{dbm_mldbm}='Data::Dumper';   # set Data::Dumper for table "foo"
283        print $dbh->{f_meta}->{foo}->{mldbm}; # show serializer for table "foo"
284
285       MLDBM works on top of other DBM modules so you can also set a DBM type
286       along with setting dbm_mldbm.  The examples above would default to
287       using SDBM_File with MLDBM.  If you wanted GDBM_File instead, here's
288       how:
289
290        # uses DB_File with MLDBM and Storable
291        $dbh = DBI->connect('dbi:DBM:', undef, undef, {
292            dbm_type  => 'DB_File',
293            dbm_mldbm => 'Storable',
294        });
295
296       SDBM_File, the default dbm_type is quite limited, so if you are going
297       to use MLDBM, you should probably use a different type, see
298       AnyDBM_File.
299
300       See below for some "GOTCHAS AND WARNINGS" about MLDBM.
301
302   Support for Berkeley DB
303       The Berkeley DB storage type is supported through two different Perl
304       modules - DB_File (which supports only features in old versions of
305       Berkeley DB) and BerkeleyDB (which supports all versions).  DBD::DBM
306       supports specifying either "DB_File" or "BerkeleyDB" as a dbm_type,
307       with or without MLDBM support.
308
309       The "BerkeleyDB" dbm_type is experimental and it's interface is likely
310       to change.  It currently defaults to BerkeleyDB::Hash and does not
311       currently support ::Btree or ::Recno.
312
313       With BerkeleyDB, you can specify initialization flags by setting them
314       in your script like this:
315
316        use BerkeleyDB;
317        my $env = new BerkeleyDB::Env -Home => $dir;  # and/or other Env flags
318        $dbh = DBI->connect('dbi:DBM:', undef, undef, {
319            dbm_type  => 'BerkeleyDB',
320            dbm_mldbm => 'Storable',
321            dbm_berkeley_flags => {
322                'DB_CREATE'  => DB_CREATE,  # pass in constants
323                'DB_RDONLY'  => DB_RDONLY,  # pass in constants
324                '-Cachesize' => 1000,       # set a ::Hash flag
325                '-Env'       => $env,       # pass in an environment
326            },
327        });
328
329       Do not set the -Flags or -Filename flags as those are determined and
330       overwritten by the SQL (e.g. -Flags => DB_RDONLY is set automatically
331       when you issue a SELECT statement).
332
333       Time has not permitted us to provide support in this release of
334       DBD::DBM for further Berkeley DB features such as transactions,
335       concurrency, locking, etc. We will be working on these in the future
336       and would value suggestions, patches, etc.
337
338       See DB_File and BerkeleyDB for further details.
339
340   Optimizing the use of key fields
341       Most "flavors" of DBM have only two physical columns (but can contain
342       multiple logical columns as explained above in "Adding multi-column
343       support with MLDBM"). They work similarly to a Perl hash with the first
344       column serving as the key. Like a Perl hash, DBM files permit you to do
345       quick lookups by specifying the key and thus avoid looping through all
346       records (supported by DBI::SQL::Nano only). Also like a Perl hash, the
347       keys must be unique. It is impossible to create two records with the
348       same key.  To put this more simply and in SQL terms, the key column
349       functions as the PRIMARY KEY or UNIQUE INDEX.
350
351       In DBD::DBM, you can take advantage of the speed of keyed lookups by
352       using DBI::SQL::Nano and a WHERE clause with a single equal comparison
353       on the key field. For example, the following SQL statements are
354       optimized for keyed lookup:
355
356        CREATE TABLE user ( user_name TEXT, phone TEXT);
357        INSERT INTO user VALUES ('Fred Bloggs','233-7777');
358        # ... many more inserts
359        SELECT phone FROM user WHERE user_name='Fred Bloggs';
360
361       The "user_name" column is the key column since it is the first column.
362       The SELECT statement uses the key column in a single equal comparison -
363       "user_name='Fred Bloggs'" - so the search will find it very quickly
364       without having to loop through all the names which were inserted into
365       the table.
366
367       In contrast, these searches on the same table are not optimized:
368
369        1. SELECT phone FROM user WHERE user_name < 'Fred';
370        2. SELECT user_name FROM user WHERE phone = '233-7777';
371
372       In #1, the operation uses a less-than (<) comparison rather than an
373       equals comparison, so it will not be optimized for key searching.  In
374       #2, the key field "user_name" is not specified in the WHERE clause, and
375       therefore the search will need to loop through all rows to find the
376       requested row(s).
377
378       Note that the underlying DBM storage needs to loop over all key/value
379       pairs when the optimized fetch is used. SQL::Statement has a massively
380       improved where clause evaluation which costs around 15% of the
381       evaluation in DBI::SQL::Nano - combined with the loop in the DBM
382       storage the speed improvement isn't so impressive.
383
384       Even if lookups are faster by around 50%, DBI::SQL::Nano and
385       SQL::Statement can benefit from the key field optimizations on updating
386       and deleting rows - and here the improved where clause evaluation of
387       SQL::Statement might beat DBI::SQL::Nano every time the where clause
388       contains not only the key field (or more than one).
389
390   Supported SQL syntax
391       DBD::DBM uses a subset of SQL.  The robustness of that subset depends
392       on what other modules you have installed. Both options support basic
393       SQL operations including CREATE TABLE, DROP TABLE, INSERT, DELETE,
394       UPDATE, and SELECT.
395
396       Option #1: By default, this module inherits its SQL support from
397       DBI::SQL::Nano that comes with DBI.  Nano is, as its name implies, a
398       *very* small SQL engine.  Although limited in scope, it is faster than
399       option #2 for some operations (especially single primary key lookups).
400       See DBI::SQL::Nano for a description of the SQL it supports and
401       comparisons of it with option #2.
402
403       Option #2: If you install the pure Perl CPAN module SQL::Statement,
404       DBD::DBM will use it instead of Nano.  This adds support for table
405       aliases, functions, joins, and much more.  If you're going to use
406       DBD::DBM for anything other than very simple tables and queries, you
407       should install SQL::Statement.  You don't have to change DBD::DBM or
408       your scripts in any way, simply installing SQL::Statement will give you
409       the more robust SQL capabilities without breaking scripts written for
410       DBI::SQL::Nano.  See SQL::Statement for a description of the SQL it
411       supports.
412
413       To find out which SQL module is working in a given script, you can use
414       the dbm_versions() method or, if you don't need the full output and
415       version numbers, just do this:
416
417        print $dbh->{sql_handler}, "\n";
418
419       That will print out either "SQL::Statement" or "DBI::SQL::Nano".
420
421       Baring the section about optimized access to the DBM storage in mind,
422       comparing the benefits of both engines:
423
424         # DBI::SQL::Nano is faster
425         $sth = $dbh->prepare( "update foo set value='new' where key=15" );
426         $sth->execute();
427         $sth = $dbh->prepare( "delete from foo where key=27" );
428         $sth->execute();
429         $sth = $dbh->prepare( "select * from foo where key='abc'" );
430
431         # SQL::Statement might faster (depending on DB size)
432         $sth = $dbh->prepare( "update foo set value='new' where key=?" );
433         $sth->execute(15);
434         $sth = $dbh->prepare( "update foo set value=? where key=15" );
435         $sth->execute('new');
436         $sth = $dbh->prepare( "delete from foo where key=?" );
437         $sth->execute(27);
438
439         # SQL::Statement is faster
440         $sth = $dbh->prepare( "update foo set value='new' where value='old'" );
441         $sth->execute();
442         # must be expressed using "where key = 15 or key = 27 or key = 42 or key = 'abc'"
443         # in DBI::SQL::Nano
444         $sth = $dbh->prepare( "delete from foo where key in (15,27,42,'abc')" );
445         $sth->execute();
446         # must be expressed using "where key > 10 and key < 90" in DBI::SQL::Nano
447         $sth = $dbh->prepare( "select * from foo where key between (10,90)" );
448         $sth->execute();
449
450         # only SQL::Statement can handle
451         $sth->prepare( "select * from foo,bar where foo.name = bar.name" );
452         $sth->execute();
453         $sth->prepare( "insert into foo values ( 1, 'foo' ), ( 2, 'bar' )" );
454         $sth->execute();
455
456   Specifying Column Names
457       DBM files don't have a standard way to store column names.   DBD::DBM
458       gets around this issue with a DBD::DBM specific way of storing the
459       column names.  If you are working only with DBD::DBM and not using
460       files created by or accessed with other DBM programs, you can ignore
461       this section.
462
463       DBD::DBM stores column names as a row in the file with the key
464       _metadata \0.  So this code
465
466        my $dbh = DBI->connect('dbi:DBM:');
467        $dbh->do("CREATE TABLE baz (foo CHAR(10), bar INTEGER)");
468        $dbh->do("INSERT INTO baz (foo,bar) VALUES ('zippy',1)");
469
470       Will create a file that has a structure something like this:
471
472         _metadata \0 | <dbd_metadata><schema></schema><col_names>foo,bar</col_names></dbd_metadata>
473         zippy        | 1
474
475       The next time you access this table with DBD::DBM, it will treat the
476       _metadata \0 row as a header rather than as data and will pull the
477       column names from there.  However, if you access the file with
478       something other than DBD::DBM, the row will be treated as a regular
479       data row.
480
481       If you do not want the column names stored as a data row in the table
482       you can set the dbm_store_metadata attribute to 0.
483
484        my $dbh = DBI->connect('dbi:DBM:', undef, undef, { dbm_store_metadata => 0 });
485
486        # or
487        $dbh->{dbm_store_metadata} = 0;
488
489        # or for per-table setting
490        $dbh->{f_meta}->{qux}->{dbm_store_metadata} = 0;
491
492       By default, DBD::DBM assumes that you have two columns named "k" and
493       "v" (short for "key" and "value").  So if you have dbm_store_metadata
494       set to 1 and you want to use alternate column names, you need to
495       specify the column names like this:
496
497        my $dbh = DBI->connect('dbi:DBM:', undef, undef, {
498            dbm_store_metadata => 0,
499            dbm_cols => [ qw(foo bar) ],
500        });
501
502        # or
503        $dbh->{dbm_store_metadata} = 0;
504        $dbh->{dbm_cols}           = 'foo,bar';
505
506        # or to set the column names on per-table basis, do this:
507        # sets the column names only for table "qux"
508        $dbh->{f_meta}->{qux}->{dbm_store_metadata} = 0;
509        $dbh->{f_meta}->{qux}->{col_names}          = [qw(foo bar)];
510
511       If you have a file that was created by another DBM program or created
512       with dbm_store_metadata set to zero and you want to convert it to using
513       DBD::DBM's column name storage, just use one of the methods above to
514       name the columns but *without* specifying dbm_store_metadata as zero.
515       You only have to do that once - thereafter you can get by without
516       setting either dbm_store_metadata or setting dbm_cols because the names
517       will be stored in the file.
518

DBI database handle attributes

520   Metadata
521       Statement handle ($sth) attributes and methods
522
523       Most statement handle attributes such as NAME, NUM_OF_FIELDS, etc. are
524       available only after an execute.  The same is true of $sth->rows which
525       is available after the execute but does not require a fetch.
526
527       Driver handle ($dbh) attributes
528
529       It is not supported anymore to use dbm-attributes without the
530       dbm_-prefix.  Currently, if an DBD::DBM private attribute is accessed
531       without an underscore in it's name, dbm_ is prepended to that attribute
532       and it's processed further. If the resulting attribute name is invalid,
533       an error is thrown.
534
535       dbm_cols
536
537       Contains a comma separated list of column names or an array reference
538       to the column names.
539
540       dbm_type
541
542       Contains the DBM storage type. Currently known supported type are
543       "ODBM_File", "NDBM_File", "SDBM_File", "GDBM_File", "DB_File" and
544       "BerkeleyDB". It is not recommended to use one of the first three types
545       - even if "SDBM_File" is the most commonly available dbm_type.
546
547       dbm_mldbm
548
549       Contains the serializer for DBM storage (value column). Requires the
550       CPAN module MLDBM installed.  Currently known supported serializers
551       are:
552
553       Data::Dumper
554               Default serializer. Deployed with Perl core.
555
556       Storable
557               Faster serializer. Deployed with Perl core.
558
559       FreezeThaw
560               Pure Perl serializer, requires FreezeThaw to be installed.
561
562       YAML    Portable serializer (between languages but not architectures).
563               Requires YAML::MLDBM installation.
564
565       JSON    Portable, fast serializer (between languages but not
566               architectures).  Requires MLDBM::Serializer::JSON installation.
567
568       dbm_store_metadata
569
570       Boolean value which determines if the metadata in DBM is stored or not.
571
572       dbm_berkeley_flags
573
574       Hash reference with additional flags for BerkeleyDB::Hash
575       instantiation.
576
577       dbm_version
578
579       Readonly attribute containing the version of DBD::DBM.
580
581       f_meta
582
583       In addition to the attributes DBD::File recognizes, DBD::DBM knows
584       about the (public) attributes "col_names" (Note not dbm_cols here!),
585       "dbm_type", "dbm_mldbm", "dbm_store_metadata" and "dbm_berkeley_flags".
586       As in DBD::File, there are undocumented, internal attributes in
587       DBD::DBM.  Be very careful when modifying attributes you do not know;
588       the consequence might a destroyed or corrupted table.
589
590       dbm_tables
591
592       This attribute provides restricted access to the table meta data. See
593       f_meta and "f_meta" in DBD::File for attribute details.
594
595       dbm_tables is a tied hash providing the internal table names as keys
596       (accessing unknown tables might create an entry) and their meta data as
597       another tied hash. The table meta storage is obtained via the
598       "get_table_meta" method from the table implementation (see
599       DBD::File::Developers). Attribute setting and getting within the table
600       meta data is handled via the methods "set_table_meta_attr" and
601       "get_table_meta_attr".
602
603       Following attributes are no longer handled by DBD::DBM:
604
605       dbm_ext
606
607       This attribute is silently mapped to DBD::File's attribute f_ext.
608       Later versions of DBI might show a depreciated warning when this
609       attribute is used and eventually it will be removed.
610
611       dbm_lockfile
612
613       This attribute is silently mapped to DBD::File's attribute f_lockfile.
614       Later versions of DBI might show a depreciated warning when this
615       attribute is used and eventually it will be removed.
616

DBI database handle methods

618   The $dbh->dbm_versions() method
619       The private method dbm_versions() returns a summary of what other
620       modules are being used at any given time.  DBD::DBM can work with or
621       without many other modules - it can use either SQL::Statement or
622       DBI::SQL::Nano as its SQL engine, it can be run with DBI or
623       DBI::PurePerl, it can use many kinds of DBM modules, and many kinds of
624       serializers when run with MLDBM.  The dbm_versions() method reports all
625       of that and more.
626
627         print $dbh->dbm_versions;               # displays global settings
628         print $dbh->dbm_versions($table_name);  # displays per table settings
629
630       An important thing to note about this method is that when it called
631       with no arguments, it displays the *global* settings.  If you override
632       these by setting per-table attributes, these will not be shown unless
633       you specify a table name as an argument to the method call.
634
635   Storing Objects
636       If you are using MLDBM, you can use DBD::DBM to take advantage of its
637       serializing abilities to serialize any Perl object that MLDBM can
638       handle.  To store objects in columns, you should (but don't absolutely
639       need to) declare it as a column of type BLOB (the type is *currently*
640       ignored by the SQL engine, but it's good form).
641

EXTENSIBILITY

643       "SQL::Statement"
644               Improved SQL engine compared to the built-in DBI::SQL::Nano -
645               see "Supported SQL syntax".
646
647       "DB_File"
648               Berkeley DB version 1. This database library is available on
649               many systems without additional installation and most systems
650               are supported.
651
652       "GDBM_File"
653               Simple dbm type (comparable to "DB_File") under the GNU
654               license.  Typically not available (or requires extra
655               installation) on non-GNU operating systems.
656
657       "BerkeleyDB"
658               Berkeley DB version up to v4 (and maybe higher) - requires
659               additional installation but is easier than GDBM_File on non-GNU
660               systems.
661
662               db4 comes with a many tools which allow repairing and migrating
663               databases.  This is the recommended dbm type for production
664               use.
665
666       "MLDBM" Serializer wrapper to support more than one column for the
667               files.  Comes with serializers using "Data::Dumper",
668               "FreezeThaw" and "Storable".
669
670       "YAML::MLDBM"
671               Additional serializer for MLDBM. YAML is very portable between
672               languages.
673
674       "MLDBM::Serializer::JSON"
675               Additional serializer for MLDBM. JSON is very portable between
676               languages, probably more than YAML.
677

GOTCHAS AND WARNINGS

679       Using the SQL DROP command will remove any file that has the name
680       specified in the command with either '.pag' and '.dir', '.db' or your
681       {f_ext} appended to it.  So this be dangerous if you aren't sure what
682       file it refers to:
683
684        $dbh->do(qq{DROP TABLE "/path/to/any/file"});
685
686       Each DBM type has limitations.  SDBM_File, for example, can only store
687       values of less than 1,000 characters.  *You* as the script author must
688       ensure that you don't exceed those bounds.  If you try to insert a
689       value that is larger than DBM can store, the results will be
690       unpredictable.  See the documentation for whatever DBM you are using
691       for details.
692
693       Different DBM implementations return records in different orders.  That
694       means that you should not rely on the order of records unless you use
695       an ORDER BY statement.
696
697       DBM data files are platform-specific.  To move them from one platform
698       to another, you'll need to do something along the lines of dumping your
699       data to CSV on platform #1 and then dumping from CSV to DBM on platform
700       #2.  DBD::AnyData and DBD::CSV can help with that.  There may also be
701       DBM conversion tools for your platforms which would probably be
702       quicker.
703
704       When using MLDBM, there is a very powerful serializer - it will allow
705       you to store Perl code or objects in database columns.  When these get
706       de-serialized, they may be eval'ed - in other words MLDBM (or actually
707       Data::Dumper when used by MLDBM) may take the values and try to execute
708       them in Perl.  Obviously, this can present dangers, so if you do not
709       know what is in a file, be careful before you access it with MLDBM
710       turned on!
711
712       See the entire section on "Table locking and flock()" for gotchas and
713       warnings about the use of flock().
714

BUGS AND LIMITATIONS

716       This module uses hash interfaces of two column file databases. While
717       none of supported SQL engines have support for indices, the following
718       statements really do the same (even if they mean something completely
719       different) for each dbm type which lacks "EXISTS" support:
720
721         $sth->do( "insert into foo values (1, 'hello')" );
722
723         # this statement does ...
724         $sth->do( "update foo set v='world' where k=1" );
725         # ... the same as this statement
726         $sth->do( "insert into foo values (1, 'world')" );
727
728       This is considered to be a bug and might change in a future release.
729
730       Known affected dbm types are "ODBM_File" and "NDBM_File". We highly
731       recommended you use a more modern dbm type such as "DB_File".
732

GETTING HELP, MAKING SUGGESTIONS, AND REPORTING BUGS

734       If you need help installing or using DBD::DBM, please write to the DBI
735       users mailing list at dbi-users@perl.org or to the
736       comp.lang.perl.modules newsgroup on usenet.  I cannot always answer
737       every question quickly but there are many on the mailing list or in the
738       newsgroup who can.
739
740       DBD developers for DBD's which rely on DBD::File or DBD::DBM or use one
741       of them as an example are suggested to join the DBI developers mailing
742       list at dbi-dev@perl.org and strongly encouraged to join our IRC
743       channel at <irc://irc.perl.org/dbi>.
744
745       If you have suggestions, ideas for improvements, or bugs to report,
746       please report a bug as described in DBI. Do not mail any of the authors
747       directly, you might not get an answer.
748
749       When reporting bugs, please send the output of
750       $dbh->dbm_versions($table) for a table that exhibits the bug and as
751       small a sample as you can make of the code that produces the bug.  And
752       of course, patches are welcome, too :-).
753
754       If you need enhancements quickly, you can get commercial support as
755       described at <http://dbi.perl.org/support/> or you can contact Jens
756       Rehsack at rehsack@cpan.org for commercial support in Germany.
757
758       Please don't bother Jochen Wiedmann or Jeff Zucker for support - they
759       handed over further maintenance to H.Merijn Brand and Jens Rehsack.
760

ACKNOWLEDGEMENTS

762       Many, many thanks to Tim Bunce for prodding me to write this, and for
763       copious, wise, and patient suggestions all along the way. (Jeff Zucker)
764
765       I send my thanks and acknowledgements to H.Merijn Brand for his initial
766       refactoring of DBD::File and his strong and ongoing support of
767       SQL::Statement. Without him, the current progress would never have been
768       made.  And I have to name Martin J. Evans for each laugh (and
769       correction) of all those funny word creations I (as non-native speaker)
770       made to the documentation. And - of course - I have to thank all those
771       unnamed contributors and testers from the Perl community. (Jens
772       Rehsack)
773
775       This module is written by Jeff Zucker < jzucker AT cpan.org >, who also
776       maintained it till 2007. After that, in 2010, Jens Rehsack & H.Merijn
777       Brand took over maintenance.
778
779        Copyright (c) 2004 by Jeff Zucker, all rights reserved.
780        Copyright (c) 2010-2013 by Jens Rehsack & H.Merijn Brand, all rights reserved.
781
782       You may freely distribute and/or modify this module under the terms of
783       either the GNU General Public License (GPL) or the Artistic License, as
784       specified in the Perl README file.
785

SEE ALSO

787       DBI, SQL::Statement, DBI::SQL::Nano, AnyDBM_File, DB_File, BerkeleyDB,
788       MLDBM, YAML::MLDBM, MLDBM::Serializer::JSON
789
790
791
792perl v5.32.1                      2021-01-27                       DBD::DBM(3)
Impressum