1DBD::DBM(3) User Contributed Perl Documentation DBD::DBM(3)
2
6 DBD::DBM - a DBI driver for DBM & MLDBM files
7
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 # 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 and other variations on connect() as shown in the DBI docs, DBD::File
30 metadata and "Metadata" shown below.
31 Use standard DBI prepare, execute, fetch, placeholders, etc., see
33 "QUICK START" for an example.
34
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 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 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 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
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 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 But here's a sample to get you started.
80 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
100 This section will explain some usage cases in more detail. To get an
101 overview about the available attributes, see "Metadata".
102 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 DBD::DBM recognizes following default extensions for following types:
111 .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 For those types, the ".dir" extension is recognized, too (for being
118 deleted when dropping a table).
119 .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 "GDBM_File", "DB_File" and "BerkeleyDB" don't usually use a file
126 extension.
127 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 $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 # or
138 $dbh->{f_ext}='.db'; # global setting
139 $dbh->{f_meta}->{'qux'}->{f_ext}='.db'; # setting for table 'qux'
140 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 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 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 # now you can do
156 my $ary = $dbh->selectall_arrayref(q{ SELECT x FROM fruit });
157 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 my $dbh = DBI->connect('dbi:DBM:');
163 my $ary = $dbh->selectall_arrayref(q{
164 SELECT x FROM "/foo/bar/fruit"
165 });
166 You can also tell DBD::DBM to use a specified path for a specific
168 table:
169 $dbh->{dbm_tables}->{f}->{file} = q(/foo/bar/fruit);
171 Please be aware that you cannot specify this during connection.
173 If you have SQL::Statement installed, you can use table aliases:
175 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 See the "GOTCHAS AND WARNINGS" for using DROP on tables.
182 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 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 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 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 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 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 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 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 # 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 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 # sets global default of GDBM_File
241 my $dbh->('dbi:DBM:type=GDBM_File');
242 # 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 # prints the dbm_type for the table "foo"
249 print $dbh->{f_meta}->{foo}->{dbm_type};
250 Note that you must change the dbm_type of a table before you access it
252 for first time.
253 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 If you want more than two columns, you must install MLDBM. It's
263 available for many platforms and is easy to install.
264 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 Some examples:
273 $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 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 # 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 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 See below for some "GOTCHAS AND WARNINGS" about MLDBM.
301 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 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 With BerkeleyDB, you can specify initialization flags by setting them
314 in your script like this:
315 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 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 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 See DB_File and BerkeleyDB for further details.
339 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 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 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 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 In contrast, these searches on the same table are not optimized:
368 1. SELECT phone FROM user WHERE user_name < 'Fred';
370 2. SELECT user_name FROM user WHERE phone = '233-7777';
371 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 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 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 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 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 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 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 print $dbh->{sql_handler}, "\n";
418 That will print out either "SQL::Statement" or "DBI::SQL::Nano".
420 Baring the section about optimized access to the DBM storage in mind,
422 comparing the benefits of both engines:
423 # 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 # 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 # 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 # 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 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 DBD::DBM stores column names as a row in the file with the key
464 _metadata \0. So this code
465 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 Will create a file that has a structure something like this:
471 _metadata \0 | <dbd_metadata><schema></schema><col_names>foo,bar</col_names></dbd_metadata>
473 zippy | 1
474 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 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 my $dbh = DBI->connect('dbi:DBM:', undef, undef, { dbm_store_metadata => 0 });
485 # or
487 $dbh->{dbm_store_metadata} = 0;
488 # or for per-table setting
490 $dbh->{f_meta}->{qux}->{dbm_store_metadata} = 0;
491 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 my $dbh = DBI->connect('dbi:DBM:', undef, undef, {
498 dbm_store_metadata => 0,
499 dbm_cols => [ qw(foo bar) ],
500 });
501 # or
503 $dbh->{dbm_store_metadata} = 0;
504 $dbh->{dbm_cols} = 'foo,bar';
505 # 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 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
520 Metadata
521 Statement handle ($sth) attributes and methods
522 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 Driver handle ($dbh) attributes
528 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 dbm_cols
536 Contains a comma separated list of column names or an array reference
538 to the column names.
539 dbm_type
541 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 dbm_mldbm
548 Contains the serializer for DBM storage (value column). Requires the
550 CPAN module MLDBM installed. Currently known supported serializers
551 are:
552 Data::Dumper
554 Default serializer. Deployed with Perl core.
555 Storable
557 Faster serializer. Deployed with Perl core.
558 FreezeThaw
560 Pure Perl serializer, requires FreezeThaw to be installed.
561 YAML Portable serializer (between languages but not architectures).
563 Requires YAML::MLDBM installation.
564 JSON Portable, fast serializer (between languages but not
566 architectures). Requires MLDBM::Serializer::JSON installation.
567 dbm_store_metadata
569 Boolean value which determines if the metadata in DBM is stored or not.
571 dbm_berkeley_flags
573 Hash reference with additional flags for BerkeleyDB::Hash
575 instantiation.
576 dbm_version
578 Readonly attribute containing the version of DBD::DBM.
580 f_meta
582 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 dbm_tables
591 This attribute provides restricted access to the table meta data. See
593 f_meta and "f_meta" in DBD::File for attribute details.
594 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 Following attributes are no longer handled by DBD::DBM:
604 dbm_ext
606 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 dbm_lockfile
612 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
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 print $dbh->dbm_versions; # displays global settings
628 print $dbh->dbm_versions($table_name); # displays per table settings
629 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 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
643 "SQL::Statement"
644 Improved SQL engine compared to the built-in DBI::SQL::Nano -
645 see "Supported SQL syntax".
646 "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 "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 "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 db4 comes with a many tools which allow repairing and migrating
663 databases. This is the recommended dbm type for production
664 use.
665 "MLDBM" Serializer wrapper to support more than one column for the
667 files. Comes with serializers using "Data::Dumper",
668 "FreezeThaw" and "Storable".
669 "YAML::MLDBM"
671 Additional serializer for MLDBM. YAML is very portable between
672 languages.
673 "MLDBM::Serializer::JSON"
675 Additional serializer for MLDBM. JSON is very portable between
676 languages, probably more than YAML.
677
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 $dbh->do(qq{DROP TABLE "/path/to/any/file"});
685 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 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 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 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 See the entire section on "Table locking and flock()" for gotchas and
713 warnings about the use of flock().
714
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 $sth->do( "insert into foo values (1, 'hello')" );
722 # 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 This is considered to be a bug and might change in a future release.
729 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
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 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 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 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 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 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
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 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 Copyright (c) 2004 by Jeff Zucker, all rights reserved.
780 Copyright (c) 2010-2013 by Jens Rehsack & H.Merijn Brand, all rights reserved.
781 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
787 DBI, SQL::Statement, DBI::SQL::Nano, AnyDBM_File, DB_File, BerkeleyDB,
788 MLDBM, YAML::MLDBM, MLDBM::Serializer::JSON
789perl v5.32.1 2021-01-27 DBD::DBM(3)