Author: REHSACK
Date: Tue May 25 12:15:23 2010
New Revision: 14044
Modified:
dbi/trunk/lib/DBD/DBM.pm
Log:
- massive documentation updates, fixes and warnings added
- small pieces of code adapted to behave like documented
Modified: dbi/trunk/lib/DBD/DBM.pm
==============================================================================
--- dbi/trunk/lib/DBD/DBM.pm (original)
+++ dbi/trunk/lib/DBD/DBM.pm Tue May 25 12:15:23 2010
@@ -79,7 +79,7 @@
my $this = $drh->SUPER::connect( $dbname, $user, $auth, $attr );
$this->STORE( 'f_lockfile', '.lck' );
- $this->STORE( 'Active', 1 );
+ $this->STORE( 'Active', 1 );
return $this;
}
@@ -111,21 +111,21 @@
#
if ( ( $attrib eq lc($attrib) ) && ( -1 == index( $attrib, "_" ) ) )
{
- # carp "Usage of '$attrib' is depreciated, use 'dbm_$attrib' instead" if( $^W );
+ # carp "Usage of '$attrib' is depreciated, use 'dbm_$attrib' instead" if( $^W );
$attrib = "dbm_" . $attrib; # backward compatibility - would like to carp here
}
- if( $attrib eq "dbm_ext" or $attrib eq "dbm_lockfile" )
+ if ( $attrib eq "dbm_ext" or $attrib eq "dbm_lockfile" )
{
- ( my $newattrib = $attrib ) =~ s/^dbm_/f_/g;
- # carp "Attribute '$attrib' is depreciated, use '$newattrib' instead" if( $^W );
- $attrib = $newattrib;
+ ( my $newattrib = $attrib ) =~ s/^dbm_/f_/g;
+ # carp "Attribute '$attrib' is depreciated, use '$newattrib' instead" if( $^W );
+ $attrib = $newattrib;
}
return $dbh->SUPER::STORE( $attrib, $value ) unless ( 0 == index( $attrib, 'dbm_' ) );
# throw an error if it has our prefix but isn't a valid attr name
#
if (
- $dbh->{dbm_valid_attrs}->{$attrib}
+ $dbh->{dbm_valid_attrs}->{$attrib}
or $attrib eq 'dbm_valid_attrs' # gotta start somewhere :-)
)
{
@@ -137,7 +137,7 @@
}
else
{
- # throw an error if it has our prefix but isn't a valid attr name
+ # throw an error if it has our prefix but isn't a valid attr name
return $dbh->set_err( $DBI::stderr, "Invalid attribute '$attrib'!" );
}
}
@@ -152,16 +152,16 @@
{
$attrib = "dbm_" . $attrib; # backward compatibility - would like to carp here
}
- if( $attrib eq "dbm_ext" or $attrib eq "dbm_lockfile" )
+ if ( $attrib eq "dbm_ext" or $attrib eq "dbm_lockfile" )
{
- ( my $newattrib = $attrib ) =~ s/^dbm_/f_/g;
- # carp "Attribute '$attrib' is depreciated, use '$newattrib' instead" if( $^W );
- $attrib = $newattrib;
+ ( my $newattrib = $attrib ) =~ s/^dbm_/f_/g;
+ # carp "Attribute '$attrib' is depreciated, use '$newattrib' instead" if( $^W );
+ $attrib = $newattrib;
}
return $dbh->SUPER::FETCH($attrib) unless ( 0 == index( $attrib, 'dbm_' ) );
if (
- $dbh->{dbm_valid_attrs}->{$attrib}
+ $dbh->{dbm_valid_attrs}->{$attrib}
or $attrib eq 'dbm_valid_attrs' # gotta start somewhere :-)
)
{
@@ -172,7 +172,7 @@
}
else
{
- # throw an error if it has our prefix but isn't a valid attr name
+ # throw an error if it has our prefix but isn't a valid attr name
return $dbh->set_err( $DBI::stderr, "Invalid attribute '$attrib'" );
}
}
@@ -336,6 +336,8 @@
$meta->{dbm_mldbm} ||= $dbh->{dbm_mldbm} if ( $dbh->{dbm_mldbm} );
$meta->{dbm_berkeley_flags} ||= $dbh->{dbm_berkeley_flags};
+ defined $meta->{f_ext}
+ or $meta->{f_ext} = $dbh->{f_ext};
unless ( defined( $meta->{f_ext} ) )
{
my $ext;
@@ -469,8 +471,16 @@
}
$col_names ||= $meta->{col_names} || [ 'k', 'v' ];
$col_names = [ split /,/, $col_names ] if ( ref $col_names ne 'ARRAY' );
- $meta->{schema} = $schema;
+ if ( $meta->{dbm_store_metadata} and not $meta->{hash}->{"_metadata \0"} )
+ {
+ $schema or $schema = '';
+ $meta->{hash}->{"_metadata \0"} = join( "",
+ "<dbd_metadata>", "<schema>$schema</schema>",
+ "<col_names>", join( ",", @{$col_names} ) . "</col_names>",
+ "</dbd_metadata>" );
+ }
+ $meta->{schema} = $schema;
$meta->{col_names} = $col_names;
}
}
@@ -656,37 +666,47 @@
=head1 SYNOPSIS
use DBI;
- $dbh = DBI->connect('dbi:DBM:'); # defaults to SDBM_File
- $dbh = DBI->connect('DBI:DBM(RaiseError=1):'); # defaults to SDBM_File
- $dbh = DBI->connect('dbi:DBM:type=GDBM_File'); # defaults to GDBM_File
- $dbh = DBI->connect('dbi:DBM:mldbm=Storable'); # MLDBM with SDBM_File
- # and Storable
-
-or
+ $dbh = DBI->connect('dbi:DBM:'); # defaults to SDBM_File
+ $dbh = DBI->connect('DBI:DBM(RaiseError=1):'); # defaults to SDBM_File
+ $dbh = DBI->connect('dbi:DBM:dbm_type=DB_File'); # defaults to DB_File
+ $dbh = DBI->connect('dbi:DBM:dbm_mldbm=Storable'); # MLDBM with SDBM_File
+ # or
$dbh = DBI->connect('dbi:DBM:', undef, undef);
- $dbh = DBI->connect('dbi:DBM:', undef, undef, { dbm_type => 'ODBM_File' });
-
-and other variations on connect() as shown in the DBI docs and with
-the dbm_ attributes shown below
+ $dbh = DBI->connect('dbi:DBM:', undef, undef, {
+ f_ext => '.db/r',
+ f_dir => '/path/to/dbfiles/',
+ f_lockfile => '.lck',
+ dbm_type => 'BerkeleyDB',
+ dbm_mldbm => 'FreezeThaw',
+ dbm_store_metadata => 1,
+ dbm_berkeley_flags => {
+ '-Cachesize' => 1000, # set a ::Hash flag
+ },
+ });
+
+and other variations on connect() as shown in the L<DBI> docs, the
+L<DBD::File/Metadata|DBD::File metadata> and with the L</Metadata>
+shown below.
-... and then use standard DBI prepare, execute, fetch, placeholders, etc.,
+Use standard DBI prepare, execute, fetch, placeholders, etc.,
see L<QUICK START> for an example
=head1 DESCRIPTION
DBD::DBM is a database management sytem that can work right out of the box.
If you have a standard installation of Perl and a standard installation of
-DBI, you can begin creating, accessing, and modifying database tables
-without any further installation. You can also add some other modules to
-it for more robust capabilities if you wish.
+DBI, you can begin creating, accessing, and modifying simple database tables
+without any further installation. You can (and should) also add some other
+modules to it for more robust capabilities if you wish.
The module uses a DBM file storage layer. DBM file storage is common on
-many platforms and files can be created with it in many languages. That
-means that, in addition to creating files with DBI/SQL, you can also use
-DBI/SQL to access and modify files created by other DBM modules and
-programs. You can also use those programs to access files created with
-DBD::DBM.
+many platforms and files can be created with it in many programming
+languages using different APIs. That means that, in addition to creating
+files with DBI/SQL, you can also use DBI/SQL to access and modify files
+created by other DBM modules and programs and vice versa. B<Note> that
+in those cases it might be necessary to use a common subset of the
+provided features.
DBM files are stored in binary format optimized for quick retrieval when
using a key field. That optimization can be used advantageously to make
@@ -699,7 +719,8 @@
DBD::DBM has been tested with the following DBM types: SDBM_File,
NDBM_File, ODBM_File, GDBM_File, DB_File, BerekeleyDB. Each type was
-tested both with and without MLDBM.
+tested both with and without MLDBM and serializers Data::Dumper, Storable,
+FreezeThaw and YAML.
=head1 QUICK START
@@ -737,28 +758,53 @@
=head1 USAGE
+This chapter will explain some use cases a bit deeper. To get an overview
+about the available attributes, see L</Metadata>.
+
=head2 Specifiying Files and Directories
DBD::DBM will automatically supply an appropriate file extension for the
type of DBM you are using. For example, if you use SDBM_File, a table
called "fruit" will be stored in two files called "fruit.pag" and
-"fruit.dir". You should I<never> specify the file extensions in your SQL
+"fruit.dir". You should B<never> specify the file extensions in your SQL
statements.
-However, I am not aware (and therefore DBD::DBM is not aware) of all
-possible extensions for various DBM types. If your DBM type uses an
-extension other than .pag and .dir, you should set the I<f_ext> attribute
-to the extension. B<And> you should write me with the name of the
-implementation and extension so I can add it to DBD::DBM! Thanks in advance
-for that :-).
+DBD::DBM recognizes following default extensions for following types:
+
+=over 4
- $dbh = DBI->connect('dbi:DBM:f_ext=.db'); # .db extension is used
- $dbh = DBI->connect('dbi:DBM:f_ext='); # no extension is used
+=item .pag/r
-or
+Chosen for dbm_type C<< SDBM_File >>, C<< ODBM_File >> and C<< NDBM_File >>
+when an implementation is detected which wraps C<< -ldbm >> for
+C<< NDBM_File >> (e.g. Solaris, AIX, ...).
- $dbh->{f_ext}='.db'; # global setting
- $dbh->{f_meta}->{'qux'}->{f_ext}='.db'; # setting for table 'qux'
+For those types, the C<< .dir >> extension is recognized, too (for being
+deleted when dropping a table).
+
+=item .db/r
+
+Chosen for dbm_type C<< NDBM_File >> when an implementation is detected
+which wraps BerkeleyDB 1.x for C<< NDBM_File >> (typically BSD's, Darwin).
+
+=back
+
+C<< GDBM_File >>, C<< DB_File >> and C<< BerkeleyDB >> are known to
+avoid adding own file extension.
+
+However, we are not aware (and therefore DBD::DBM is not aware) of all
+possible extensions for various DBM types. If your DBM type uses an
+extension other than recognized types and extensions, you should set the
+I<f_ext> attribute to the extension. B<And> you should fill a bug report
+as described in DBI with the name of the implementation and extension so
+we can add it to DBD::DBM! Thanks in advance for that :-).
+
+ $dbh = DBI->connect('dbi:DBM:f_ext=.db'); # .db extension is used
+ $dbh = DBI->connect('dbi:DBM:f_ext='); # no extension is used
+
+ # or
+ $dbh->{f_ext}='.db'; # global setting
+ $dbh->{f_meta}->{'qux'}->{f_ext}='.db'; # setting for table 'qux'
By default files are assumed to be in the current working directory. To
have the module look in a different directory, specify the I<f_dir>
@@ -768,14 +814,15 @@
For example, this will look for the file /foo/bar/fruit (or
/foo/bar/fruit.pag for DBM types that use that extension)
- my $dbh = DBI->connect('dbi:DBM:f_dir=/foo/bar');
- my $ary = $dbh->selectall_arrayref(q{ SELECT * FROM fruit });
-
-And this will too:
+ my $dbh = DBI->connect('dbi:DBM:f_dir=/foo/bar');
+ # and this will do, too:
+ my $dbh = DBI->connect('dbi:DBM:');
+ $dbh->{f_dir} = '/foo/bar';
+ # even if only this is recommended
+ my $dbh = DBI->connect('dbi:DBM:', undef, undef, { f_dir => '/foo/bar' } );
- my $dbh = DBI->connect('dbi:DBM:');
- $dbh->{f_dir} = '/foo/bar';
- my $ary = $dbh->selectall_arrayref(q{ SELECT x FROM fruit });
+ # now you can do
+ my $ary = $dbh->selectall_arrayref(q{ SELECT x FROM fruit });
You can also use delimited identifiers to specify paths directly in SQL
statements. This looks in the same place as the two examples above but
@@ -797,12 +844,13 @@
=head2 Table locking and flock()
-Table locking is accomplished using a lockfile which has the same name as
-the table's file but with the file extension '.lck' (or a lockfile
+Table locking is accomplished using a lockfile which has the same basename
+as the table's file but with the file extension '.lck' (or a lockfile
extension that you supply, see below). This file is created along with the
table during a CREATE and removed during a DROP. Every time the table
itself is opened, the lockfile is flocked(). For SELECT, this is an shared
-lock. For all other operations, it is an exclusive lock.
+lock. For all other operations, it is an exclusive lock (except you specify
+something different using the I<f_lock> attribute).
Since the locking depends on flock(), it only works on operating systems
that support flock(). In cases where flock() is not implemented, DBD::DBM
@@ -823,29 +871,31 @@
=head2 Specifying the DBM type
Each "flavor" of DBM stores its files in a different format and has
-different capabilities and different limitations. See L<AnyDBM_File> for a
+different capabilities and different limitations. See L<AnyDBM_File> for a
comparison of DBM types.
-By default, DBD::DBM uses the SDBM_File type of storage since SDBM_File
-comes with Perl itself. But if you have other types of DBM storage
-available, you can use any of them with DBD::DBM also.
-
-You can specify the DBM type using the "dbm_type" attribute which can be
-set in the connection string or with the $dbh->{dbm_type} attribute for
-global settings or with the $dbh->{dbm_tables}->{$table_name}->{type}
+By default, DBD::DBM uses the C<< SDBM_File >> type of storage since
+C<< SDBM_File >> comes with Perl itself. But if you have other types of DBM
+storage available, you can use any of them with DBD::DBM also. It is
+strongly recommended to use at least C<< DB_File >>, because C<< SDBM_File >>
+has quirks and limitations and C<< ODBM_file >>, C<< NDBM_File >> and
+C<< GDBM_File >> are not always available.
+
+You can specify the DBM type using the I<dbm_type> attribute which can be
+set in the connection string or with the C<< $dbh->{dbm_type} >> attribute
+for global settings or with the C<< $dbh->{f_meta}->{$table_name}->{type} >>
attribute for per-table settings in cases where a single script is
accessing more than one kind of DBM file.
-In the connection string, just set type=TYPENAME where TYPENAME is any DBM
-type such as GDBM_File, DB_File, etc. Do I<not> use MLDBM as your
-dbm_type, that is set differently, see below.
-
- my $dbh=DBI->connect('dbi:DBM:'); # uses the default SDBM_File
- my $dbh=DBI->connect('dbi:DBM:type=GDBM_File'); # uses the GDBM_File
+In the connection string, just set C<< dbm_type=TYPENAME >> where
+C<< TYPENAME >> is any DBM type such as GDBM_File, DB_File, etc. Do I<not>
+use MLDBM as your I<dbm_type>, that is set differently, see below.
-You can also use $dbh->{dbm_type} to set global DBM type:
+ my $dbh=DBI->connect('dbi:DBM:'); # uses the default SDBM_File
+ my $dbh=DBI->connect('dbi:DBM:dbm_type=GDBM_File'); # uses the GDBM_File
- $dbh->{dbm_type} = 'GDBM_File'; # set the global DBM type
+ # You can also use $dbh->{dbm_type} to set global DBM type:
+ $dbh->{dbm_type} = 'DB_File'; # set the global DBM type
print $dbh->{dbm_type}; # display the global DBM type
If you are going to have several tables in your script that come from
@@ -853,58 +903,62 @@
different settings for the various tables. You can even use this to
perform joins on files that have completely different storage mechanisms.
- my $dbh->('dbi:DBM:type=GDBM_File');
- #
# sets global default of GDBM_File
+ my $dbh->('dbi:DBM:type=GDBM_File');
- my $dbh->{dbm_tables}->{foo}->{type} = 'DB_File';
- #
- # over-rides the global setting, but only for the table called "foo"
+ # over-rides the global setting, but only for the tables called
+ # I<foo> and I<bar>
+ my $dbh->{f_meta}->{foo}->{dbm_type} = 'DB_File';
+ my $dbh->{f_meta}->{bar}->{dbm_type} = 'BerkeleyDB';
- print $dbh->{dbm_tables}->{foo}->{type};
- #
# prints the dbm_type for the table "foo"
+ print $dbh->{f_meta}->{foo}->{dbm_type};
+
+B<Note> that you must change the I<dbm_type> of a table before you access
+it first time.
=head2 Adding multi-column support with MLDBM
-Most of the DBM types only support two columns. However a CPAN module
-called MLDBM overcomes this limitation by allowing more than two columns.
-It does this by serializing the data - basically it puts a reference to an
-array into the second column. It can also put almost any kind of Perl
-object or even Perl coderefs into columns.
+Most of the DBM types only support two columns and even if it would support
+more, DBD::DBM would only use two. However a CPAN module called MLDBM
+overcomes this limitation by allowing more than two columns. It does this
+by serializing the data - basically it puts a reference to an array into
+the second column. It can also put almost any kind of Perl object or even
+B<Perl coderefs> into columns.
-If you want more than two columns, you must install MLDBM. It's available
+If you want more than two columns, you B<must> install MLDBM. It's available
for many platforms and is easy to install.
-MLDBM can use three different modules to serialize the column -
-Data::Dumper, Storable, and FreezeThaw. Data::Dumper is the default,
-Storable is the fastest. MLDBM can also make use of user-defined
-serialization methods. All of this is available to you through DBD::DBM
-with just one attribute setting.
-
-To use MLDBM with DBD::DBM, you need to set the dbm_mldbm attribute to the
-name of the serialization module.
+MLDBM is defaultly distributed with three serializers - Data::Dumper,
+Storable, and FreezeThaw. Data::Dumper is the default, Storable is the
+fastest. MLDBM can also make use of user-defined serialization methods or
+other serialization modules (e.g. L<YAML::MLDBM>). All of this is available
+to you through DBD::DBM with just one attribute setting: I<dbm_mldbm>.
Some examples:
- $dbh=DBI->connect('dbi:DBM:mldbm=Storable'); # use MLDBM with Storable
+ $dbh=DBI->connect('dbi:DBM:dbm_mldbm=Storable'); # use MLDBM with Storable
$dbh=DBI->connect(
- 'dbi:DBM:mldbm=MySerializer' # use MLDBM with a user defined module
+ 'dbi:DBM:dbm_mldbm=MySerializer' # use MLDBM with a user defined module
);
- $dbh->{dbm_mldbm} = 'MySerializer'; # same as above
- print $dbh->{dbm_mldbm} # show the MLDBM serializer
- $dbh->{dbm_tables}->{foo}->{mldbm}='Data::Dumper'; # set Data::Dumper for table "foo"
- print $dbh->{dbm_tables}->{foo}->{mldbm}; # show serializer for table "foo"
+ $dbh=DBI->connect('dbi::dbm:', undef,
+ undef, { dbm_mldbm => 'YAML' }); # use 3rd party serializer
+ $dbh->{dbm_mldbm} = 'YAML'; # same as above
+ print $dbh->{dbm_mldbm} # show the MLDBM serializer
+ $dbh->{f_meta}->{foo}->{dbm_mldbm}='Data::Dumper'; # set Data::Dumper for table "foo"
+ print $dbh->{f_meta}->{foo}->{mldbm}; # show serializer for table "foo"
MLDBM works on top of other DBM modules so you can also set a DBM type
along with setting dbm_mldbm. The examples above would default to using
SDBM_File with MLDBM. If you wanted GDBM_File instead, here's how:
- $dbh = DBI->connect('dbi:DBM:type=GDBM_File;mldbm=Storable');
- #
- # uses GDBM_File with MLDBM and Storable
+ # uses DB_File with MLDBM and Storable
+ $dbh = DBI->connect('dbi:DBM:', undef, undef, {
+ dbm_type => 'DB_File',
+ dbm_mldbm => 'Storable',
+ });
-SDBM_File, the default file type is quite limited, so if you are going to
+SDBM_File, the default I<dbm_type> is quite limited, so if you are going to
use MLDBM, you should probably use a different type, see L<AnyDBM_File>.
See below for some L<GOTCHAS AND WARNINGS> about MLDBM.
@@ -924,27 +978,79 @@
With BerkeleyDB, you can specify initialization flags by setting them in
your script like this:
- my $dbh = DBI->connect('dbi:DBM:type=BerkeleyDB;mldbm=Storable');
use BerkeleyDB;
my $env = new BerkeleyDB::Env -Home => $dir; # and/or other Env flags
- $dbh->{dbm_berkeley_flags} = {
- 'DB_CREATE' => DB_CREATE # pass in constants
- , 'DB_RDONLY' => DB_RDONLY # pass in constants
- , '-Cachesize' => 1000 # set a ::Hash flag
- , '-Env' => $env # pass in an environment
- };
-
-Do I<not> set the -Flags or -Filename flags, those are determined by the
-SQL (e.g. -Flags => DB_RDONLY is set automatically when you issue a SELECT
-statement).
+ $dbh = DBI->connect('dbi:DBM:', undef, undef, {
+ dbm_type => 'BerkeleyDB',
+ dbm_mldbm => 'Storable',
+ dbm_berkeley_flags => {
+ 'DB_CREATE' => DB_CREATE, # pass in constants
+ 'DB_RDONLY' => DB_RDONLY, # pass in constants
+ '-Cachesize' => 1000, # set a ::Hash flag
+ '-Env' => $env, # pass in an environment
+ },
+ });
+
+Do I<not> set the -Flags or -Filename flags, those are determined and
+overwritten by the SQL (e.g. -Flags => DB_RDONLY is set automatically
+when you issue a SELECT statement).
-Time has not permitted me to provide support in this release of DBD::DBM
+Time has not permitted us to provide support in this release of DBD::DBM
for further Berkeley DB features such as transactions, concurrency,
-locking, etc. I will be working on these in the future and would value
+locking, etc. We will be working on these in the future and would value
suggestions, patches, etc.
See L<DB_File> and L<BerkeleyDB> for further details.
+=head2 Optimizing use of key fields
+
+Most "flavors" of DBM have only two physical columns (but can contain
+multiple logical columns as explained above in
+L<Adding multi-column support with MLDBM>). They work similarly to a
+Perl hash with the first column serving as the key. Like a Perl hash, DBM
+files permit you to do quick lookups by specifying the key and thus avoid
+looping through all records (supported by DBI::SQL::Nano only). Also like
+a Perl hash, the keys must be unique. It is impossible to create two
+records with the same key. To put this all more simply and in SQL terms,
+the key column functions as the I<PRIMARY KEY>.
+
+In DBD::DBM, you can take advantage of the speed of keyed lookups by using
+DBI::SQL::Nano and a WHERE clause with a single equal comparison on the key
+field. For example, the following SQL statements are optimized for keyed
+lookup:
+
+ CREATE TABLE user ( user_name TEXT, phone TEXT);
+ INSERT INTO user VALUES ('Fred Bloggs','233-7777');
+ # ... many more inserts
+ SELECT phone FROM user WHERE user_name='Fred Bloggs';
+
+The "user_name" column is the key column since it is the first column. The
+SELECT statement uses the key column in a single equal comparison -
+"user_name='Fred Bloggs' - so the search will find it very quickly without
+having to loop through however many names were inserted into the table.
+
+In contrast, thes searches on the same table are not optimized:
+
+ 1. SELECT phone FROM user WHERE user_name < 'Fred';
+ 2. SELECT user_name FROM user WHERE phone = '233-7777';
+
+In #1, the operation uses a less-than (<) comparison rather than an equals
+comparison, so it will not be optimized for key searching. In #2, the key
+field "user_name" is not specified in the WHERE clause, and therefore the
+search will need to loop through all rows to find the desired result.
+
+B<Note> that the underlying DBM storage needs to loop over all I<key/value>
+pairs when the optimized fetch is used. SQL::Statement has a massively
+improved where clause evaluation which costs around 15% of the evaluation
+in DBI::SQL::Nano - combined with the loop in the DBM storage the speed
+improvement isn't so impressive.
+
+Even if lookups are sped up by circa 50%, DBI::SQL::Nano and SQL::Statement
+can benefit from the key field optimizations on updating and deleting some
+rows - and here the improved where clause evaluation of SQL::Statement
+might beat DBI::SQL::Nano every time the where clause contains not only
+the key field (or more than one).
+
=head2 Supported SQL syntax
DBD::DBM uses a subset of SQL. The robustness of that subset depends on
@@ -955,8 +1061,9 @@
B<Option #1:> By default, this module inherits its SQL support from
DBI::SQL::Nano that comes with DBI. Nano is, as its name implies, a *very*
small SQL engine. Although limited in scope, it is faster than option #2
-for some operations. See L<DBI::SQL::Nano> for a description of the SQL it
-supports and comparisons of it with option #2.
+for some operations (especially single I<primary key> lookups). See
+L<DBI::SQL::Nano> for a description of the SQL it supports and comparisons
+of it with option #2.
B<Option #2:> If you install the pure Perl CPAN module SQL::Statement,
DBD::DBM will use it instead of Nano. This adds support for table aliases,
@@ -971,44 +1078,44 @@
dbm_versions() method or, if you don't need the full output and version
numbers, just do this:
- print $dbh->{sql_handler};
+ say $dbh->{sql_handler};
That will print out either "SQL::Statement" or "DBI::SQL::Nano".
-=head2 Optimizing use of key fields
-
-Most "flavors" of DBM have only two physical columns (but can contain
-multiple logical columns as explained below). They work similarly to a
-Perl hash with the first column serving as the key. Like a Perl hash, DBM
-files permit you to do quick lookups by specifying the key and thus avoid
-looping through all records. Also like a Perl hash, the keys must be
-unique. It is impossible to create two records with the same key. To put
-this all more simply and in SQL terms, the key column functions as the
-PRIMARY KEY.
-
-In DBD::DBM, you can take advantage of the speed of keyed lookups by using
-a WHERE clause with a single equal comparison on the key field. For
-example, the following SQL statements are optimized for keyed lookup:
-
- CREATE TABLE user ( user_name TEXT, phone TEXT);
- INSERT INTO user VALUES ('Fred Bloggs','233-7777');
- # ... many more inserts
- SELECT phone FROM user WHERE user_name='Fred Bloggs';
-
-The "user_name" column is the key column since it is the first column. The
-SELECT statement uses the key column in a single equal comparison -
-"user_name='Fred Bloggs' - so the search will find it very quickly without
-having to loop through however many names were inserted into the table.
-
-In contrast, thes searches on the same table are not optimized:
-
- 1. SELECT phone FROM user WHERE user_name < 'Fred';
- 2. SELECT user_name FROM user WHERE phone = '233-7777';
+Having the section about optimized access to the DBM storage in mind,
+comparing the benefits of both engines:
-In #1, the operation uses a less-than (<) comparison rather than an equals
-comparison, so it will not be optimized for key searching. In #2, the key
-field "user_name" is not specified in the WHERE clause, and therefore the
-search will need to loop through all rows to find the desired result.
+ # DBI::SQL::Nano is faster
+ $sth = $dbh->prepare( "update foo set value='new' where key=15" );
+ $sth->execute();
+ $sth = $dbh->prepare( "delete from foo where key=27" );
+ $sth->execute();
+ $sth = $dbh->prepare( "select * from foo where key='abc'" );
+
+ # SQL::Statement might faster (depending on DB size)
+ $sth = $dbh->prepare( "update foo set value='new' where key=?" );
+ $sth->execute(15);
+ $sth = $dbh->prepare( "update foo set value=? where key=15" );
+ $sth->execute('new');
+ $sth = $dbh->prepare( "delete from foo where key=?" );
+ $sth->execute(27);
+
+ # SQL::Statement is faster
+ $sth = $dbh->prepare( "update foo set value='new' where value='old'" );
+ $sth->execute();
+ # must be expressed using "where key = 15 or key = 27 or key = 42 or key = 'abc'"
+ # in DBI::SQL::Nano
+ $sth = $dbh->prepare( "delete from foo where key in (15,27,42,'abc')" );
+ $sth->execute();
+ # must be expressed using "where key > 10 and key < 90" in DBI::SQL::Nano
+ $sth = $dbh->prepare( "select * from foo where key between (10,90)" );
+ $sth->execute();
+
+ # only SQL::Statement can handle
+ $sth->prepare( "select * from foo,bar where foo.name = bar.name" );
+ $sth->execute();
+ $sth->prepare( "insert into foo values ( 1, 'foo' ), ( 2, 'bar' )" );
+ $sth->execute();
=head2 Specifying Column Names
@@ -1026,45 +1133,43 @@
Will create a file that has a structure something like this:
- _metadata \0 | foo,bar
+ _metadata \0 | <dbd_metadata><schema></schema><col_names>foo,bar</col_names></dbd_metadata>
zippy | 1
The next time you access this table with DBD::DBM, it will treat the
-_metadata row as a header rather than as data and will pull the column
+I<_metadata \0> row as a header rather than as data and will pull the column
names from there. However, if you access the file with something other
than DBD::DBM, the row will be treated as a regular data row.
If you do not want the column names stored as a data row in the table you
can set the I<dbm_store_metadata> attribute to 0.
- my $dbh = DBI->connect('dbi:DBM:store_metadata=0');
-
-or
+ my $dbh = DBI->connect('dbi:DBM:', undef, undef, { dbm_store_metadata => 0 });
+ # or
$dbh->{dbm_store_metadata} = 0;
-or, for per-table setting
-
- $dbh->{dbm_tables}->{qux}->{store_metadata} = 0;
+ # or for per-table setting
+ $dbh->{f_meta}->{qux}->{dbm_store_metadata} = 0;
By default, DBD::DBM assumes that you have two columns named "k" and "v"
(short for "key" and "value"). So if you have I<dbm_store_metadata> set to
1 and you want to use alternate column names, you need to specify the
column names like this:
- my $dbh = DBI->connect('dbi:DBM:store_metadata=0;cols=foo,bar');
-
-or
+ my $dbh = DBI->connect('dbi:DBM:', undef, undef, {
+ dbm_store_metadata => 0,
+ dbm_cols => [ qw(foo bar) ],
+ });
+ # or
$dbh->{dbm_store_metadata} = 0;
$dbh->{dbm_cols} = 'foo,bar';
-To set the column names on per-table basis, do this:
-
- $dbh->{dbm_tables}->{qux}->{store_metadata} = 0;
- $dbh->{dbm_tables}->{qux}->{cols} = 'foo,bar';
- #
+ # or to set the column names on per-table basis, do this:
# sets the column names only for table "qux"
+ $dbh->{f_meta}->{qux}->{dbm_store_metadata} = 0;
+ $dbh->{f_meta}->{qux}->{col_names} = [qw(foo bar)];
If you have a file that was created by another DBM program or created with
I<dbm_store_metadata> set to zero and you want to convert it to using
@@ -1074,12 +1179,82 @@
either I<dbm_store_metadata> or setting I<dbm_cols> because the names will
be stored in the file.
+=head1 DBI database handle attributes
+
=head2 Statement handle ($sth) attributes and methods
Most statement handle attributes such as NAME, NUM_OF_FIELDS, etc. are
available only after an execute. The same is true of $sth->rows which is
available after the execute but does I<not> require a fetch.
+=head2 Metadata
+
+=over 4
+
+=item dbm_cols
+
+Contains a comma separated list of column names or an array reference to
+a column names.
+
+=item dbm_type
+
+Contains the DBM storage type. Currently know supported ones are
+C<< ODBM_File >>, C<< NDBM_File >>, C<< SDBM_File >>, C<< GDBM_File >>,
+C<< DB_File >> and C<< BerkeleyDB >>. It's not recommended to use one
+of the first three types - even if C<< SDBM_File >> is the most common
+available I<dbm_type>.
+
+=item dbm_mldbm
+
+Contains the serializer for DBM storage (value column). Requires the
+CPAN module L<MLDBM> installed. Currently know supported serializers are:
+
+=over 8
+
+=item Data::Dumper
+
+Default serializer. Deployed with Perl core.
+
+=item Storable
+
+Faster serializer. Deployed with Perl core.
+
+=item FreezeThaw
+
+Pure Perl serializer, requires L<FreezeThaw> being installed.
+
+=item YAML
+
+Very portable serializer (between languages, not between archirectures).
+Requires L<YAML::MLDBM> being installed.
+
+=back
+
+=item dbm_store_metadata
+
+Boolean value whether to store some metadata in DBM storage or not.
+
+=item dbm_berkeley_flags
+
+Hash reference with additional flags for BerkeleyDB::Hash instantiation.
+
+=item dbm_version
+
+Readonly attribute containing this version of DBD::DBM.
+
+=item f_meta
+
+In addition to the attributes L<DBD::File> recognizes, DBD::DBM cares about
+the (public) attributes col_names (B<Note> not I<dbm_cols> here!), dbm_type,
+dbm_mldbm, dbm_store_metadata and dbm_berkeley_flags. There are, like in
+DBD::File undocumented, internally used attributes. Be very careful when
+modifying attributes you do not know, the consequence might a destroyed
+table.
+
+=back
+
+=head1 DBI database handle methods
+
=head2 The $dbh->dbm_versions() method
The private method dbm_versions() presents a summary of what other modules
@@ -1105,13 +1280,11 @@
declare it as a column of type BLOB (the type is *currently* ignored by the
SQL engine, but heh, it's good form).
-You *must* use placeholders to insert or refer to the data.
-
=head1 GOTCHAS AND WARNINGS
Using the SQL DROP command will remove any file that has the name specified
-in the command with either '.pag' or '.dir' or your {f_ext} appended to
-it. So this be dangerous if you aren't sure what file it refers to:
+in the command with either '.pag' and '.dir', '.db' or your {f_ext} appended
+to it. So this be dangerous if you aren't sure what file it refers to:
$dbh->do(qq{DROP TABLE "/path/to/any/file"});
@@ -1123,9 +1296,7 @@
Different DBM implementations return records in different orders. That
means that you can I<not> depend on the order of records unless you use an
-ORDER BY statement. DBI::SQL::Nano does not currently support ORDER BY
-(though it may soon) so if you need ordering, you'll have to install
-SQL::Statement.
+ORDER BY statement.
DBM data files are platform-specific. To move them from one platform to
another, you'll need to do something along the lines of dumping your data
@@ -1151,24 +1322,45 @@
questions quickly and there are many on the mailing list or in the
newsgroup who can.
+DBD developers for DBD's which rely on DBD::File or DBD::DBM or use one
+of them as example are enforced to join the DBI developers mailing list at
[email protected] and strongly encouraged to join our IRC channel at
+L<irc://irc.perl.org/dbi>.
+
If you have suggestions, ideas for improvements, or bugs to report, please
-write me directly at the email shown below.
+report a bug as described in DBI. Do not mail any of the authors directly,
+you might not get an answer.
When reporting bugs, please send the output of $dbh->dbm_versions($table)
-for a table that exhibits the bug and, if possible, as small a sample as
-you can make of the code that produces the bug. And of course, patches are
-welcome too :-).
+for a table that exhibits the bug and a small sample as you can make of
+the code that produces the bug. And of course, patches are welcome, too
+:-).
+
+If you need enhancements quickly, you can get commercial support like
+described at L<http://dbi.perl.org/support/> or you can contact Jens Rehsack
+at [email protected] for commercial support in Germany.
+
+Please don't bother Jochen Wiedmann or Jeff Zucker for support - they
+hand over the further maintaining to H.Merijn Brand and Jens Rehsack.
=head1 ACKNOWLEDGEMENTS
Many, many thanks to Tim Bunce for prodding me to write this, and for
-copious, wise, and patient suggestions all along the way.
+copious, wise, and patient suggestions all along the way. (Jeff Zucker)
+
+I send my thanks and acknowledgements to H.Merijn Brand for his initial
+refactoring of DBD::File and his strong and ongoing support of
+SQL::Statement. Without him, the current progress never had been reached.
+And I have to name Martin J. Evans for each laugh (and correction) of all
+those funny word creations I (as non-native speaker) made to the
+documentation. And - of course - I have to thank all those unnamed
+contributors and testers from the Perl community. (Jens Rehsack)
=head1 AUTHOR AND COPYRIGHT
This module is written by Jeff Zucker < jzucker AT cpan.org >, who also
-maintained it till 2010. After that, Jens Rehsack & H.Merijn Brand took
-over maintenance.
+maintained it till 2007. After that, in 2010, Jens Rehsack & H.Merijn Brand
+took over maintenance.
Copyright (c) 2004 by Jeff Zucker, all rights reserved.
Copyright (c) 2010 by Jens Rehsack & H.Merijn Brand, all rights reserved.
lmpx.com only provides a reader for public news (NNTP) servers. It is not
affiliated with the servers or forums shown here and is not responsible for
the content of articles, which is written by their respective authors.