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
38 modules 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
45 supported by different sorts of perl modules such as SDBM_File and
46 MLDBM. This module supports all of the flavors that perl supports and,
47 when used with MLDBM, supports tables with any number of columns and
48 insertion 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
91 the type of DBM you are using. For example, if you use SDBM_File, a
92 table called "fruit" will be stored in two files called "fruit.pag" and
93 "fruit.dir". You should never specify the file extensions in your SQL
94 statements.
95 However, I am not aware (and therefore DBD::DBM is not aware) of all
97 possible extensions for various DBM types. If your DBM type uses an
98 extension other than .pag and .dir, you should set the dbm_ext
99 attribute to the extension. And you should write me with the name of
100 the implementation and extension so I can add it to DBD::DBM! Thanks
101 in advance for that :-).
102 $dbh = DBI->connect('dbi:DBM:ext=.db'); # .db extension is used
104 $dbh = DBI->connect('dbi:DBM:ext='); # no extension is used
105 or
107 $dbh->{dbm_ext}='.db'; # global setting
109 $dbh->{dbm_tables}->{'qux'}->{ext}='.db'; # setting for table 'qux'
110 By default files are assumed to be in the current working directory.
112 To have the module look in a different directory, specify the f_dir
113 attribute in either the connect string or by setting the database
114 handle attribute.
115 For example, this will look for the file /foo/bar/fruit (or
117 /foo/bar/fruit.pag for DBM types that use that extension)
118 my $dbh = DBI->connect('dbi:DBM:f_dir=/foo/bar');
120 my $ary = $dbh->selectall_arrayref(q{ SELECT * FROM fruit });
121 And this will too:
123 my $dbh = DBI->connect('dbi:DBM:');
125 $dbh->{f_dir} = '/foo/bar';
126 my $ary = $dbh->selectall_arrayref(q{ SELECT x FROM fruit });
127 You can also use delimited identifiers to specify paths directly in SQL
129 statements. This looks in the same place as the two examples above but
130 without setting f_dir:
131 my $dbh = DBI->connect('dbi:DBM:');
133 my $ary = $dbh->selectall_arrayref(q{
134 SELECT x FROM "/foo/bar/fruit"
135 });
136 If you have SQL::Statement installed, you can use table aliases:
138 my $dbh = DBI->connect('dbi:DBM:');
140 my $ary = $dbh->selectall_arrayref(q{
141 SELECT f.x FROM "/foo/bar/fruit" AS f
142 });
143 See the "GOTCHAS AND WARNINGS" for using DROP on tables.
145 Table locking and flock()
147 Table locking is accomplished using a lockfile which has the same name
148 as the table's file but with the file extension '.lck' (or a lockfile
149 extension that you suppy, see belwo). This file is created along with
150 the table during a CREATE and removed during a DROP. Every time the
151 table itself is opened, the lockfile is flocked(). For SELECT, this is
152 an shared lock. For all other operations, it is an exclusive lock.
153 Since the locking depends on flock(), it only works on operating
155 systems that support flock(). In cases where flock() is not
156 implemented, DBD::DBM will not complain, it will simply behave as if
157 the flock() had occurred although no actual locking will happen. Read
158 the documentation for flock() if you need to understand this.
159 Even on those systems that do support flock(), the locking is only
161 advisory - as is allways the case with flock(). This means that if
162 some other program tries to access the table while DBD::DBM has the
163 table locked, that other program will *succeed* at opening the table.
164 DBD::DBM's locking only applies to DBD::DBM. An exception to this
165 would be the situation in which you use a lockfile with the other
166 program that has the same name as the lockfile used in DBD::DBM and
167 that program also uses flock() on that lockfile. In that case,
168 DBD::DBM and your other program will respect each other's locks.
169 If you wish to use a lockfile extension other than '.lck', simply
171 specify the dbm_lockfile attribute:
172 $dbh = DBI->connect('dbi:DBM:lockfile=.foo');
174 $dbh->{dbm_lockfile} = '.foo';
175 $dbh->{dbm_tables}->{qux}->{lockfile} = '.foo';
176 If you wish to disable locking, set the dbm_lockfile equal to 0.
178 $dbh = DBI->connect('dbi:DBM:lockfile=0');
180 $dbh->{dbm_lockfile} = 0;
181 $dbh->{dbm_tables}->{qux}->{lockfile} = 0;
182 Specifying the DBM type
184 Each "flavor" of DBM stores its files in a different format and has
185 different capabilities and different limitations. See AnyDBM_File for
186 a comparison of DBM types.
187 By default, DBD::DBM uses the SDBM_File type of storage since SDBM_File
189 comes with Perl itself. But if you have other types of DBM storage
190 available, you can use any of them with DBD::DBM also.
191 You can specify the DBM type using the "dbm_type" attribute which can
193 be set in the connection string or with the $dbh->{dbm_type} attribute
194 for global settings or with the
195 $dbh->{dbm_tables}->{$table_name}->{type} attribute for per-table
196 settings in cases where a single script is accessing more than one kind
197 of DBM file.
198 In the connection string, just set type=TYPENAME where TYPENAME is any
200 DBM type such as GDBM_File, DB_File, etc. Do not use MLDBM as your
201 dbm_type, that is set differently, see below.
202 my $dbh=DBI->connect('dbi:DBM:'); # uses the default SDBM_File
204 my $dbh=DBI->connect('dbi:DBM:type=GDBM_File'); # uses the GDBM_File
205 You can also use $dbh->{dbm_type} to set global DBM type:
207 $dbh->{dbm_type} = 'GDBM_File'; # set the global DBM type
209 print $dbh->{dbm_type}; # display the global DBM type
210 If you are going to have several tables in your script that come from
212 different DBM types, you can use the $dbh->{dbm_tables} hash to store
213 different settings for the various tables. You can even use this to
214 perform joins on files that have completely different storage
215 mechanisms.
216 my $dbh->('dbi:DBM:type=GDBM_File');
218 #
219 # sets global default of GDBM_File
220 my $dbh->{dbm_tables}->{foo}->{type} = 'DB_File';
222 #
223 # over-rides the global setting, but only for the table called "foo"
224 print $dbh->{dbm_tables}->{foo}->{type};
226 #
227 # prints the dbm_type for the table "foo"
228 Adding multi-column support with MLDBM
230 Most of the DBM types only support two columns. However a CPAN module
231 called MLDBM overcomes this limitation by allowing more than two
232 columns. It does this by serializing the data - basically it puts a
233 reference to an array into the second column. It can also put almost
234 any kind of Perl object or even Perl coderefs into columns.
235 If you want more than two columns, you must install MLDBM. It's
237 available for many platforms and is easy to install.
238 MLDBM can use three different modules to serialize the column -
240 Data::Dumper, Storable, and FreezeThaw. Data::Dumper is the default,
241 Storable is the fastest. MLDBM can also make use of user-defined
242 serialization methods. All of this is available to you through
243 DBD::DBM with just one attribute setting.
244 To use MLDBM with DBD::DBM, you need to set the dbm_mldbm attribute to
246 the name of the serialization module.
247 Some examples:
249 $dbh=DBI->connect('dbi:DBM:mldbm=Storable'); # use MLDBM with Storable
251 $dbh=DBI->connect(
252 'dbi:DBM:mldbm=MySerializer' # use MLDBM with a user defined module
253 );
254 $dbh->{dbm_mldbm} = 'MySerializer'; # same as above
255 print $dbh->{dbm_mldbm} # show the MLDBM serializer
256 $dbh->{dbm_tables}->{foo}->{mldbm}='Data::Dumper'; # set Data::Dumper for table "foo"
257 print $dbh->{dbm_tables}->{foo}->{mldbm}; # show serializer for table "foo"
258 MLDBM works on top of other DBM modules so you can also set a DBM type
260 along with setting dbm_mldbm. The examples above would default to
261 using SDBM_File with MLDBM. If you wanted GDBM_File instead, here's
262 how:
263 $dbh = DBI->connect('dbi:DBM:type=GDBM_File;mldbm=Storable');
265 #
266 # uses GDBM_File with MLDBM and Storable
267 SDBM_File, the default file type is quite limited, so if you are going
269 to use MLDBM, you should probably use a different type, see
270 AnyDBM_File.
271 See below for some "GOTCHAS AND WARNINGS" about MLDBM.
273 Support for Berkeley DB
275 The Berkeley DB storage type is supported through two different Perl
276 modules - DB_File (which supports only features in old versions of
277 Berkeley DB) and BerkeleyDB (which supports all versions). DBD::DBM
278 supports specifying either "DB_File" or "BerkeleyDB" as a dbm_type,
279 with or without MLDBM support.
280 The "BerkeleyDB" dbm_type is experimental and its interface is likely
282 to chagne. It currently defaults to BerkeleyDB::Hash and does not
283 currently support ::Btree or ::Recno.
284 With BerkeleyDB, you can specify initialization flags by setting them
286 in your script like this:
287 my $dbh = DBI->connect('dbi:DBM:type=BerkeleyDB;mldbm=Storable');
289 use BerkeleyDB;
290 my $env = new BerkeleyDB::Env -Home => $dir; # and/or other Env flags
291 $dbh->{dbm_berkeley_flags} = {
292 'DB_CREATE' => DB_CREATE # pass in constants
293 , 'DB_RDONLY' => DB_RDONLY # pass in constants
294 , '-Cachesize' => 1000 # set a ::Hash flag
295 , '-Env' => $env # pass in an environment
296 };
297 Do not set the -Flags or -Filename flags, those are determined by the
299 SQL (e.g. -Flags => DB_RDONLY is set automatically when you issue a
300 SELECT statement).
301 Time has not permitted me to provide support in this release of
303 DBD::DBM for further Berkeley DB features such as transactions,
304 concurrency, locking, etc. I will be working on these in the future
305 and would value suggestions, patches, etc.
306 See DB_File and BerkeleyDB for further details.
308 Supported SQL syntax
310 DBD::DBM uses a subset of SQL. The robustness of that subset depends
311 on what other modules you have installed. Both options support basic
312 SQL operations including CREATE TABLE, DROP TABLE, INSERT, DELETE,
313 UPDATE, and SELECT.
314 Option #1: By default, this module inherits its SQL support from
316 DBI::SQL::Nano that comes with DBI. Nano is, as its name implies, a
317 *very* small SQL engine. Although limited in scope, it is faster than
318 option #2 for some operations. See DBI::SQL::Nano for a description of
319 the SQL it supports and comparisons of it with option #2.
320 Option #2: If you install the pure Perl CPAN module SQL::Statement,
322 DBD::DBM will use it instead of Nano. This adds support for table
323 aliases, for functions, for joins, and much more. If you're going to
324 use DBD::DBM for anything other than very simple tables and queries,
325 you should install SQL::Statement. You don't have to change DBD::DBM
326 or your scripts in any way, simply installing SQL::Statement will give
327 you the more robust SQL capabilities without breaking scripts written
328 for DBI::SQL::Nano. See SQL::Statement for a description of the SQL it
329 supports.
330 To find out which SQL module is working in a given script, you can use
332 the dbm_versions() method or, if you don't need the full output and
333 version numbers, just do this:
334 print $dbh->{sql_handler};
336 That will print out either "SQL::Statement" or "DBI::SQL::Nano".
338 Optimizing use of key fields
340 Most "flavors" of DBM have only two physical columns (but can contain
341 multiple logical columns as explained below). They work similarly to a
342 Perl hash with the first column serving as the key. Like a Perl hash,
343 DBM files permit you to do quick lookups by specifying the key and thus
344 avoid looping through all records. Also like a Perl hash, the keys
345 must be unique. It is impossible to create two records with the same
346 key. To put this all more simply and in SQL terms, the key column
347 functions as the PRIMARY KEY.
348 In DBD::DBM, you can take advantage of the speed of keyed lookups by
350 using a WHERE clause with a single equal comparison on the key field.
351 For example, the following SQL statements are optimized for keyed
352 lookup:
353 CREATE TABLE user ( user_name TEXT, phone TEXT);
355 INSERT INTO user VALUES ('Fred Bloggs','233-7777');
356 # ... many more inserts
357 SELECT phone FROM user WHERE user_name='Fred Bloggs';
358 The "user_name" column is the key column since it is the first column.
360 The SELECT statement uses the key column in a single equal comparision
361 - "user_name='Fred Bloggs' - so the search will find it very quickly
362 without having to loop through however many names were inserted into
363 the table.
364 In contrast, thes searches on the same table are not optimized:
366 1. SELECT phone FROM user WHERE user_name < 'Fred';
368 2. SELECT user_name FROM user WHERE phone = '233-7777';
369 In #1, the operation uses a less-than (<) comparison rather than an
371 equals comparison, so it will not be optimized for key searching. In
372 #2, the key field "user_name" is not specified in the WHERE clause, and
373 therefore the search will need to loop through all rows to find the
374 desired result.
375 Specifying Column Names
377 DBM files don't have a standard way to store column names. DBD::DBM
378 gets around this issue with a DBD::DBM specific way of storing the
379 column names. If you are working only with DBD::DBM and not using
380 files created by or accessed with other DBM programs, you can ignore
381 this section.
382 DBD::DBM stores column names as a row in the file with the key
384 _metadata \0. So this code
385 my $dbh = DBI->connect('dbi:DBM:');
387 $dbh->do("CREATE TABLE baz (foo CHAR(10), bar INTEGER)");
388 $dbh->do("INSERT INTO baz (foo,bar) VALUES ('zippy',1)");
389 Will create a file that has a structure something like this:
391 _metadata \0 | foo,bar
393 zippy | 1
394 The next time you access this table with DBD::DBM, it will treat the
396 _metadata row as a header rather than as data and will pull the column
397 names from there. However, if you access the file with something other
398 than DBD::DBM, the row will be treated as a regular data row.
399 If you do not want the column names stored as a data row in the table
401 you can set the dbm_store_metadata attribute to 0.
402 my $dbh = DBI->connect('dbi:DBM:store_metadata=0');
404 or
406 $dbh->{dbm_store_metadata} = 0;
408 or, for per-table setting
410 $dbh->{dbm_tables}->{qux}->{store_metadata} = 0;
412 By default, DBD::DBM assumes that you have two columns named "k" and
414 "v" (short for "key" and "value"). So if you have dbm_store_metadata
415 set to 1 and you want to use alternate column names, you need to
416 specify the column names like this:
417 my $dbh = DBI->connect('dbi:DBM:store_metadata=0;cols=foo,bar');
419 or
421 $dbh->{dbm_store_metadata} = 0;
423 $dbh->{dbm_cols} = 'foo,bar';
424 To set the column names on per-table basis, do this:
426 $dbh->{dbm_tables}->{qux}->{store_metadata} = 0;
428 $dbh->{dbm_tables}->{qux}->{cols} = 'foo,bar';
429 #
430 # sets the column names only for table "qux"
431 If you have a file that was created by another DBM program or created
433 with dbm_store_metadata set to zero and you want to convert it to using
434 DBD::DBM's column name storage, just use one of the methods above to
435 name the columns but *without* specifying dbm_store_metadata as zero.
436 You only have to do that once - thereafter you can get by without
437 setting either dbm_store_metadata or setting dbm_cols because the names
438 will be stored in the file.
439 Statement handle ($sth) attributes and methods
441 Most statement handle attributes such as NAME, NUM_OF_FIELDS, etc. are
442 available only after an execute. The same is true of $sth->rows which
443 is available after the execute but does not require a fetch.
444 The $dbh->dbm_versions() method
446 The private method dbm_versions() presents a summary of what other
447 modules are being used at any given time. DBD::DBM can work with or
448 without many other modules - it can use either SQL::Statement or
449 DBI::SQL::Nano as its SQL engine, it can be run with DBI or
450 DBI::PurePerl, it can use many kinds of DBM modules, and many kinds of
451 serializers when run with MLDBM. The dbm_versions() method reports on
452 all of that and more.
453 print $dbh->dbm_versions; # displays global settings
455 print $dbh->dbm_versions($table_name); # displays per table settings
456 An important thing to note about this method is that when called with
458 no arguments, it displays the *global* settings. If you over-ride
459 these by setting per-table attributes, these will not be shown unless
460 you specifiy a table name as an argument to the method call.
461 Storing Objects
463 If you are using MLDBM, you can use DBD::DBM to take advantage of its
464 serializing abilities to serialize any Perl object that MLDBM can
465 handle. To store objects in columns, you should (but don't absolutely
466 need to) declare it as a column of type BLOB (the type is *currently*
467 ignored by the SQL engine, but heh, it's good form).
468 You *must* use placeholders to insert or refer to the data.
470
472 Using the SQL DROP command will remove any file that has the name
473 specified in the command with either '.pag' or '.dir' or your {dbm_ext}
474 appended to it. So this be dangerous if you aren't sure what file it
475 refers to:
476 $dbh->do(qq{DROP TABLE "/path/to/any/file"});
478 Each DBM type has limitations. SDBM_File, for example, can only store
480 values of less than 1,000 characters. *You* as the script author must
481 ensure that you don't exceed those bounds. If you try to insert a
482 value that is bigger than the DBM can store, the results will be
483 unpredictable. See the documentation for whatever DBM you are using
484 for details.
485 Different DBM implementations return records in different orders. That
487 means that you can not depend on the order of records unless you use an
488 ORDER BY statement. DBI::SQL::Nano does not currently support ORDER BY
489 (though it may soon) so if you need ordering, you'll have to install
490 SQL::Statement.
491 DBM data files are platform-specific. To move them from one platform
493 to another, you'll need to do something along the lines of dumping your
494 data to CSV on platform #1 and then dumping from CSV to DBM on platform
495 #2. DBD::AnyData and DBD::CSV can help with that. There may also be
496 DBM conversion tools for your platforms which would probably be
497 quickest.
498 When using MLDBM, there is a very powerful serializer - it will allow
500 you to store Perl code or objects in database columns. When these get
501 de-serialized, they may be evaled - in other words MLDBM (or actually
502 Data::Dumper when used by MLDBM) may take the values and try to execute
503 them in Perl. Obviously, this can present dangers, so if you don't
504 know what's in a file, be careful before you access it with MLDBM
505 turned on!
506 See the entire section on "Table locking and flock()" for gotchas and
508 warnings about the use of flock().
509
511 If you need help installing or using DBD::DBM, please write to the DBI
512 users mailing list at dbi-users@perl.org or to the
513 comp.lang.perl.modules newsgroup on usenet. I'm afraid I can't always
514 answer these kinds of questions quickly and there are many on the
515 mailing list or in the newsgroup who can.
516 If you have suggestions, ideas for improvements, or bugs to report,
518 please write me directly at the email shown below.
519 When reporting bugs, please send the output of
521 $dbh->dbm_versions($table) for a table that exhibits the bug and, if
522 possible, as small a sample as you can make of the code that produces
523 the bug. And of course, patches are welcome too :-).
524
526 Many, many thanks to Tim Bunce for prodding me to write this, and for
527 copious, wise, and patient suggestions all along the way.
528
530 This module is written and maintained by
531 Jeff Zucker < jzucker AT cpan.org >
533 Copyright (c) 2004 by Jeff Zucker, all rights reserved.
535 You may freely distribute and/or modify this module under the terms of
537 either the GNU General Public License (GPL) or the Artistic License, as
538 specified in the Perl README file.
539
541 DBI, SQL::Statement, DBI::SQL::Nano, AnyDBM_File, MLDBM
542perl v5.10.1 2008-01-31 DBD::DBM(3)