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:type=GDBM_File'); # defaults to GDBM_File
13 $dbh = DBI->connect('dbi:DBM:mldbm=Storable'); # MLDBM with SDBM_File
14 # and Storable
15 or
17 $dbh = DBI->connect('dbi:DBM:', undef, undef);
19 $dbh = DBI->connect('dbi:DBM:', undef, undef, { dbm_type => 'ODBM_File' });
20 and other variations on connect() as shown in the DBI docs and with the
22 dbm_ attributes shown below
23 ... and then use standard DBI prepare, execute, fetch, placeholders,
25 etc., see "QUICK START" for an example
26
28 DBD::DBM is a database management sytem that can work right out of the
29 box. If you have a standard installation of Perl and a standard
30 installation of DBI, you can begin creating, accessing, and modifying
31 database tables without any further installation. You can also add
32 some other modules to it for more robust capabilities if you wish.
33 The module uses a DBM file storage layer. DBM file storage is common
35 on many platforms and files can be created with it in many languges.
36 That means that, in addition to creating files with DBI/SQL, you can
37 also use DBI/SQL to access and modify files created by other DBM mod‐
38 ules and programs. You can also use those programs to access files
39 created with DBD::DBM.
40 DBM files are stored in binary format optimized for quick retrieval
42 when using a key field. That optimization can be used advantageously
43 to make DBD::DBM SQL operations that use key fields very fast. There
44 are several different "flavors" of DBM - different storage formats sup‐
45 ported by different sorts of perl modules such as SDBM_File and MLDBM.
46 This module supports all of the flavors that perl supports and, when
47 used with MLDBM, supports tables with any number of columns and inser‐
48 tion of Perl objects into tables.
49 DBD::DBM has been tested with the following DBM types: SDBM_File,
51 NDBM_File, ODBM_File, GDBM_File, DB_File, BerekeleyDB. Each type was
52 tested both with and without MLDBM.
53
55 DBD::DBM operates like all other DBD drivers - it's basic syntax and
56 operation is specified by DBI. If you're not familiar with DBI, you
57 should start by reading DBI and the documents it points to and then
58 come back and read this file. If you are familiar with DBI, you
59 already know most of what you need to know to operate this module.
60 Just jump in and create a test script something like the one shown
61 below.
62 You should be aware that there are several options for the SQL engine
64 underlying DBD::DBM, see "Supported SQL syntax". There are also many
65 options for DBM support, see especially the section on "Adding multi-
66 column support with MLDBM".
67 But here's a sample to get you started.
69 use DBI;
71 my $dbh = DBI->connect('dbi:DBM:');
72 $dbh->{RaiseError} = 1;
73 for my $sql( split /;\n+/,"
74 CREATE TABLE user ( user_name TEXT, phone TEXT );
75 INSERT INTO user VALUES ('Fred Bloggs','233-7777');
76 INSERT INTO user VALUES ('Sanjay Patel','777-3333');
77 INSERT INTO user VALUES ('Junk','xxx-xxxx');
78 DELETE FROM user WHERE user_name = 'Junk';
79 UPDATE user SET phone = '999-4444' WHERE user_name = 'Sanjay Patel';
80 SELECT * FROM user
81 "){
82 my $sth = $dbh->prepare($sql);
83 $sth->execute;
84 $sth->dump_results if $sth->{NUM_OF_FIELDS};
85 }
86 $dbh->disconnect;
87
89 Specifiying Files and Directories
90 DBD::DBM will automatically supply an appropriate file extension for
92 the type of DBM you are using. For example, if you use SDBM_File, a
93 table called "fruit" will be stored in two files called "fruit.pag" and
94 "fruit.dir". You should never specify the file extensions in your SQL
95 statements.
96 However, I am not aware (and therefore DBD::DBM is not aware) of all
98 possible extensions for various DBM types. If your DBM type uses an
99 extension other than .pag and .dir, you should set the dbm_ext
100 attribute to the extension. And you should write me with the name of
101 the implementation and extension so I can add it to DBD::DBM! Thanks
102 in advance for that :-).
103 $dbh = DBI->connect('dbi:DBM:ext=.db'); # .db extension is used
105 $dbh = DBI->connect('dbi:DBM:ext='); # no extension is used
106 or
108 $dbh->{dbm_ext}='.db'; # global setting
110 $dbh->{dbm_tables}->{'qux'}->{ext}='.db'; # setting for table 'qux'
111 By default files are assumed to be in the current working directory.
113 To have the module look in a different directory, specify the f_dir
114 attribute in either the connect string or by setting the database han‐
115 dle attribute.
116 For example, this will look for the file /foo/bar/fruit (or
118 /foo/bar/fruit.pag for DBM types that use that extension)
119 my $dbh = DBI->connect('dbi:DBM:f_dir=/foo/bar');
121 my $ary = $dbh->selectall_arrayref(q{ SELECT * FROM fruit });
122 And this will too:
124 my $dbh = DBI->connect('dbi:DBM:');
126 $dbh->{f_dir} = '/foo/bar';
127 my $ary = $dbh->selectall_arrayref(q{ SELECT x FROM fruit });
128 You can also use delimited identifiers to specify paths directly in SQL
130 statements. This looks in the same place as the two examples above but
131 without setting f_dir:
132 my $dbh = DBI->connect('dbi:DBM:');
134 my $ary = $dbh->selectall_arrayref(q{
135 SELECT x FROM "/foo/bar/fruit"
136 });
137 If you have SQL::Statement installed, you can use table aliases:
139 my $dbh = DBI->connect('dbi:DBM:');
141 my $ary = $dbh->selectall_arrayref(q{
142 SELECT f.x FROM "/foo/bar/fruit" AS f
143 });
144 See the "GOTCHAS AND WARNINGS" for using DROP on tables.
146 Table locking and flock()
148 Table locking is accomplished using a lockfile which has the same name
150 as the table's file but with the file extension '.lck' (or a lockfile
151 extension that you suppy, see belwo). This file is created along with
152 the table during a CREATE and removed during a DROP. Every time the
153 table itself is opened, the lockfile is flocked(). For SELECT, this is
154 an shared lock. For all other operations, it is an exclusive lock.
155 Since the locking depends on flock(), it only works on operating sys‐
157 tems that support flock(). In cases where flock() is not implemented,
158 DBD::DBM will not complain, it will simply behave as if the flock() had
159 occurred although no actual locking will happen. Read the documenta‐
160 tion for flock() if you need to understand this.
161 Even on those systems that do support flock(), the locking is only
163 advisory - as is allways the case with flock(). This means that if
164 some other program tries to access the table while DBD::DBM has the ta‐
165 ble locked, that other program will *succeed* at opening the table.
166 DBD::DBM's locking only applies to DBD::DBM. An exception to this
167 would be the situation in which you use a lockfile with the other pro‐
168 gram that has the same name as the lockfile used in DBD::DBM and that
169 program also uses flock() on that lockfile. In that case, DBD::DBM and
170 your other program will respect each other's locks.
171 If you wish to use a lockfile extension other than '.lck', simply spec‐
173 ify the dbm_lockfile attribute:
174 $dbh = DBI->connect('dbi:DBM:lockfile=.foo');
176 $dbh->{dbm_lockfile} = '.foo';
177 $dbh->{dbm_tables}->{qux}->{lockfile} = '.foo';
178 If you wish to disable locking, set the dbm_lockfile equal to 0.
180 $dbh = DBI->connect('dbi:DBM:lockfile=0');
182 $dbh->{dbm_lockfile} = 0;
183 $dbh->{dbm_tables}->{qux}->{lockfile} = 0;
184 Specifying the DBM type
186 Each "flavor" of DBM stores its files in a different format and has
188 different capabilities and different limitations. See AnyDBM_File for
189 a comparison of DBM types.
190 By default, DBD::DBM uses the SDBM_File type of storage since SDBM_File
192 comes with Perl itself. But if you have other types of DBM storage
193 available, you can use any of them with DBD::DBM also.
194 You can specify the DBM type using the "dbm_type" attribute which can
196 be set in the connection string or with the $dbh->{dbm_type} attribute
197 for global settings or with the $dbh->{dbm_tables}->{$ta‐
198 ble_name}->{type} attribute for per-table settings in cases where a
199 single script is accessing more than one kind of DBM file.
200 In the connection string, just set type=TYPENAME where TYPENAME is any
202 DBM type such as GDBM_File, DB_File, etc. Do not use MLDBM as your
203 dbm_type, that is set differently, see below.
204 my $dbh=DBI->connect('dbi:DBM:'); # uses the default SDBM_File
206 my $dbh=DBI->connect('dbi:DBM:type=GDBM_File'); # uses the GDBM_File
207 You can also use $dbh->{dbm_type} to set global DBM type:
209 $dbh->{dbm_type} = 'GDBM_File'; # set the global DBM type
211 print $dbh->{dbm_type}; # display the global DBM type
212 If you are going to have several tables in your script that come from
214 different DBM types, you can use the $dbh->{dbm_tables} hash to store
215 different settings for the various tables. You can even use this to
216 perform joins on files that have completely different storage mecha‐
217 nisms.
218 my $dbh->('dbi:DBM:type=GDBM_File');
220 #
221 # sets global default of GDBM_File
222 my $dbh->{dbm_tables}->{foo}->{type} = 'DB_File';
224 #
225 # over-rides the global setting, but only for the table called "foo"
226 print $dbh->{dbm_tables}->{foo}->{type};
228 #
229 # prints the dbm_type for the table "foo"
230 Adding multi-column support with MLDBM
232 Most of the DBM types only support two columns. However a CPAN module
234 called MLDBM overcomes this limitation by allowing more than two col‐
235 umns. It does this by serializing the data - basically it puts a ref‐
236 erence to an array into the second column. It can also put almost any
237 kind of Perl object or even Perl coderefs into columns.
238 If you want more than two columns, you must install MLDBM. It's avail‐
240 able for many platforms and is easy to install.
241 MLDBM can use three different modules to serialize the column -
243 Data::Dumper, Storable, and FreezeThaw. Data::Dumper is the default,
244 Storable is the fastest. MLDBM can also make use of user-defined seri‐
245 alization methods. All of this is available to you through DBD::DBM
246 with just one attribute setting.
247 To use MLDBM with DBD::DBM, you need to set the dbm_mldbm attribute to
249 the name of the serialization module.
250 Some examples:
252 $dbh=DBI->connect('dbi:DBM:mldbm=Storable'); # use MLDBM with Storable
254 $dbh=DBI->connect(
255 'dbi:DBM:mldbm=MySerializer' # use MLDBM with a user defined module
256 );
257 $dbh->{dbm_mldbm} = 'MySerializer'; # same as above
258 print $dbh->{dbm_mldbm} # show the MLDBM serializer
259 $dbh->{dbm_tables}->{foo}->{mldbm}='Data::Dumper'; # set Data::Dumper for table "foo"
260 print $dbh->{dbm_tables}->{foo}->{mldbm}; # show serializer for table "foo"
261 MLDBM works on top of other DBM modules so you can also set a DBM type
263 along with setting dbm_mldbm. The examples above would default to
264 using SDBM_File with MLDBM. If you wanted GDBM_File instead, here's
265 how:
266 $dbh = DBI->connect('dbi:DBM:type=GDBM_File;mldbm=Storable');
268 #
269 # uses GDBM_File with MLDBM and Storable
270 SDBM_File, the default file type is quite limited, so if you are going
272 to use MLDBM, you should probably use a different type, see Any‐
273 DBM_File.
274 See below for some "GOTCHAS AND WARNINGS" about MLDBM.
276 Support for Berkeley DB
278 The Berkeley DB storage type is supported through two different Perl
280 modules - DB_File (which supports only features in old versions of
281 Berkeley DB) and BerkeleyDB (which supports all versions). DBD::DBM
282 supports specifying either "DB_File" or "BerkeleyDB" as a dbm_type,
283 with or without MLDBM support.
284 The "BerkeleyDB" dbm_type is experimental and its interface is likely
286 to chagne. It currently defaults to BerkeleyDB::Hash and does not cur‐
287 rently support ::Btree or ::Recno.
288 With BerkeleyDB, you can specify initialization flags by setting them
290 in your script like this:
291 my $dbh = DBI->connect('dbi:DBM:type=BerkeleyDB;mldbm=Storable');
293 use BerkeleyDB;
294 my $env = new BerkeleyDB::Env -Home => $dir; # and/or other Env flags
295 $dbh->{dbm_berkeley_flags} = {
296 'DB_CREATE' => DB_CREATE # pass in constants
297 , 'DB_RDONLY' => DB_RDONLY # pass in constants
298 , '-Cachesize' => 1000 # set a ::Hash flag
299 , '-Env' => $env # pass in an environment
300 };
301 Do not set the -Flags or -Filename flags, those are determined by the
303 SQL (e.g. -Flags => DB_RDONLY is set automatically when you issue a
304 SELECT statement).
305 Time has not permitted me to provide support in this release of
307 DBD::DBM for further Berkeley DB features such as transactions, concur‐
308 rency, locking, etc. I will be working on these in the future and
309 would value suggestions, patches, etc.
310 See DB_File and BerkeleyDB for further details.
312 Supported SQL syntax
314 DBD::DBM uses a subset of SQL. The robustness of that subset depends
316 on what other modules you have installed. Both options support basic
317 SQL operations including CREATE TABLE, DROP TABLE, INSERT, DELETE,
318 UPDATE, and SELECT.
319 Option #1: By default, this module inherits its SQL support from
321 DBI::SQL::Nano that comes with DBI. Nano is, as its name implies, a
322 *very* small SQL engine. Although limited in scope, it is faster than
323 option #2 for some operations. See DBI::SQL::Nano for a description of
324 the SQL it supports and comparisons of it with option #2.
325 Option #2: If you install the pure Perl CPAN module SQL::Statement,
327 DBD::DBM will use it instead of Nano. This adds support for table
328 aliases, for functions, for joins, and much more. If you're going to
329 use DBD::DBM for anything other than very simple tables and queries,
330 you should install SQL::Statement. You don't have to change DBD::DBM
331 or your scripts in any way, simply installing SQL::Statement will give
332 you the more robust SQL capabilities without breaking scripts written
333 for DBI::SQL::Nano. See SQL::Statement for a description of the SQL it
334 supports.
335 To find out which SQL module is working in a given script, you can use
337 the dbm_versions() method or, if you don't need the full output and
338 version numbers, just do this:
339 print $dbh->{sql_handler};
341 That will print out either "SQL::Statement" or "DBI::SQL::Nano".
343 Optimizing use of key fields
345 Most "flavors" of DBM have only two physical columns (but can contain
347 multiple logical columns as explained below). They work similarly to a
348 Perl hash with the first column serving as the key. Like a Perl hash,
349 DBM files permit you to do quick lookups by specifying the key and thus
350 avoid looping through all records. Also like a Perl hash, the keys
351 must be unique. It is impossible to create two records with the same
352 key. To put this all more simply and in SQL terms, the key column
353 functions as the PRIMARY KEY.
354 In DBD::DBM, you can take advantage of the speed of keyed lookups by
356 using a WHERE clause with a single equal comparison on the key field.
357 For example, the following SQL statements are optimized for keyed
358 lookup:
359 CREATE TABLE user ( user_name TEXT, phone TEXT);
361 INSERT INTO user VALUES ('Fred Bloggs','233-7777');
362 # ... many more inserts
363 SELECT phone FROM user WHERE user_name='Fred Bloggs';
364 The "user_name" column is the key column since it is the first column.
366 The SELECT statement uses the key column in a single equal comparision
367 - "user_name='Fred Bloggs' - so the search will find it very quickly
368 without having to loop through however many names were inserted into
369 the table.
370 In contrast, thes searches on the same table are not optimized:
372 1. SELECT phone FROM user WHERE user_name < 'Fred';
374 2. SELECT user_name FROM user WHERE phone = '233-7777';
375 In #1, the operation uses a less-than (<) comparison rather than an
377 equals comparison, so it will not be optimized for key searching. In
378 #2, the key field "user_name" is not specified in the WHERE clause, and
379 therefore the search will need to loop through all rows to find the
380 desired result.
381 Specifying Column Names
383 DBM files don't have a standard way to store column names. DBD::DBM
385 gets around this issue with a DBD::DBM specific way of storing the col‐
386 umn names. If you are working only with DBD::DBM and not using files
387 created by or accessed with other DBM programs, you can ignore this
388 section.
389 DBD::DBM stores column names as a row in the file with the key _meta‐
391 data \0. So this code
392 my $dbh = DBI->connect('dbi:DBM:');
394 $dbh->do("CREATE TABLE baz (foo CHAR(10), bar INTEGER)");
395 $dbh->do("INSERT INTO baz (foo,bar) VALUES ('zippy',1)");
396 Will create a file that has a structure something like this:
398 _metadata \0 ⎪ foo,bar
400 zippy ⎪ 1
401 The next time you access this table with DBD::DBM, it will treat the
403 _metadata row as a header rather than as data and will pull the column
404 names from there. However, if you access the file with something other
405 than DBD::DBM, the row will be treated as a regular data row.
406 If you do not want the column names stored as a data row in the table
408 you can set the dbm_store_metadata attribute to 0.
409 my $dbh = DBI->connect('dbi:DBM:store_metadata=0');
411 or
413 $dbh->{dbm_store_metadata} = 0;
415 or, for per-table setting
417 $dbh->{dbm_tables}->{qux}->{store_metadata} = 0;
419 By default, DBD::DBM assumes that you have two columns named "k" and
421 "v" (short for "key" and "value"). So if you have dbm_store_metadata
422 set to 1 and you want to use alternate column names, you need to spec‐
423 ify the column names like this:
424 my $dbh = DBI->connect('dbi:DBM:store_metadata=0;cols=foo,bar');
426 or
428 $dbh->{dbm_store_metadata} = 0;
430 $dbh->{dbm_cols} = 'foo,bar';
431 To set the column names on per-table basis, do this:
433 $dbh->{dbm_tables}->{qux}->{store_metadata} = 0;
435 $dbh->{dbm_tables}->{qux}->{cols} = 'foo,bar';
436 #
437 # sets the column names only for table "qux"
438 If you have a file that was created by another DBM program or created
440 with dbm_store_metadata set to zero and you want to convert it to using
441 DBD::DBM's column name storage, just use one of the methods above to
442 name the columns but *without* specifying dbm_store_metadata as zero.
443 You only have to do that once - thereafter you can get by without set‐
444 ting either dbm_store_metadata or setting dbm_cols because the names
445 will be stored in the file.
446 Statement handle ($sth) attributes and methods
448 Most statement handle attributes such as NAME, NUM_OF_FIELDS, etc. are
450 available only after an execute. The same is true of $sth->rows which
451 is available after the execute but does not require a fetch.
452 The $dbh->dbm_versions() method
454 The private method dbm_versions() presents a summary of what other mod‐
456 ules are being used at any given time. DBD::DBM can work with or with‐
457 out many other modules - it can use either SQL::Statement or
458 DBI::SQL::Nano as its SQL engine, it can be run with DBI or
459 DBI::PurePerl, it can use many kinds of DBM modules, and many kinds of
460 serializers when run with MLDBM. The dbm_versions() method reports on
461 all of that and more.
462 print $dbh->dbm_versions; # displays global settings
464 print $dbh->dbm_versions($table_name); # displays per table settings
465 An important thing to note about this method is that when called with
467 no arguments, it displays the *global* settings. If you over-ride
468 these by setting per-table attributes, these will not be shown unless
469 you specifiy a table name as an argument to the method call.
470 Storing Objects
472 If you are using MLDBM, you can use DBD::DBM to take advantage of its
474 serializing abilities to serialize any Perl object that MLDBM can han‐
475 dle. To store objects in columns, you should (but don't absolutely
476 need to) declare it as a column of type BLOB (the type is *currently*
477 ignored by the SQL engine, but heh, it's good form).
478 You *must* use placeholders to insert or refer to the data.
480
482 Using the SQL DROP command will remove any file that has the name spec‐
483 ified in the command with either '.pag' or '.dir' or your {dbm_ext}
484 appended to it. So this be dangerous if you aren't sure what file it
485 refers to:
486 $dbh->do(qq{DROP TABLE "/path/to/any/file"});
488 Each DBM type has limitations. SDBM_File, for example, can only store
490 values of less than 1,000 characters. *You* as the script author must
491 ensure that you don't exceed those bounds. If you try to insert a
492 value that is bigger than the DBM can store, the results will be unpre‐
493 dictable. See the documentation for whatever DBM you are using for
494 details.
495 Different DBM implementations return records in different orders. That
497 means that you can not depend on the order of records unless you use an
498 ORDER BY statement. DBI::SQL::Nano does not currently support ORDER BY
499 (though it may soon) so if you need ordering, you'll have to install
500 SQL::Statement.
501 DBM data files are platform-specific. To move them from one platform
503 to another, you'll need to do something along the lines of dumping your
504 data to CSV on platform #1 and then dumping from CSV to DBM on platform
505 #2. DBD::AnyData and DBD::CSV can help with that. There may also be
506 DBM conversion tools for your platforms which would probably be quick‐
507 est.
508 When using MLDBM, there is a very powerful serializer - it will allow
510 you to store Perl code or objects in database columns. When these get
511 de-serialized, they may be evaled - in other words MLDBM (or actually
512 Data::Dumper when used by MLDBM) may take the values and try to execute
513 them in Perl. Obviously, this can present dangers, so if you don't
514 know what's in a file, be careful before you access it with MLDBM
515 turned on!
516 See the entire section on "Table locking and flock()" for gotchas and
518 warnings about the use of flock().
519
521 If you need help installing or using DBD::DBM, please write to the DBI
522 users mailing list at dbi-users@perl.org or to the comp.lang.perl.mod‐
523 ules newsgroup on usenet. I'm afraid I can't always answer these kinds
524 of questions quickly and there are many on the mailing list or in the
525 newsgroup who can.
526 If you have suggestions, ideas for improvements, or bugs to report,
528 please write me directly at the email shown below.
529 When reporting bugs, please send the output of $dbh->dbm_versions($ta‐
531 ble) for a table that exhibits the bug and, if possible, as small a
532 sample as you can make of the code that produces the bug. And of
533 course, patches are welcome too :-).
534
536 Many, many thanks to Tim Bunce for prodding me to write this, and for
537 copious, wise, and patient suggestions all along the way.
538
540 This module is written and maintained by
541 Jeff Zucker < jzucker AT cpan.org >
543 Copyright (c) 2004 by Jeff Zucker, all rights reserved.
545 You may freely distribute and/or modify this module under the terms of
547 either the GNU General Public License (GPL) or the Artistic License, as
548 specified in the Perl README file.
549
551 DBI, SQL::Statement, DBI::SQL::Nano, AnyDBM_File, MLDBM
552perl v5.8.8 2006-02-07 DBD::DBM(3)