.\" Automatically generated by Pod::Man 2.27 (Pod::Simple 3.28)
.\"
.\" Standard preamble:
.\" ========================================================================
.de Sp \" Vertical space (when we can't use .PP)
.if t .sp .5v
.if n .sp
..
.de Vb \" Begin verbatim text
.ft CW
.nf
.ne \\$1
..
.de Ve \" End verbatim text
.ft R
.fi
..
.\" Set up some character translations and predefined strings.  \*(-- will
.\" give an unbreakable dash, \*(PI will give pi, \*(L" will give a left
.\" double quote, and \*(R" will give a right double quote.  \*(C+ will
.\" give a nicer C++.  Capital omega is used to do unbreakable dashes and
.\" therefore won't be available.  \*(C` and \*(C' expand to `' in nroff,
.\" nothing in troff, for use with C<>.
.tr \(*W-
.ds C+ C\v'-.1v'\h'-1p'\s-2+\h'-1p'+\s0\v'.1v'\h'-1p'
.ie n \{\
.    ds -- \(*W-
.    ds PI pi
.    if (\n(.H=4u)&(1m=24u) .ds -- \(*W\h'-12u'\(*W\h'-12u'-\" diablo 10 pitch
.    if (\n(.H=4u)&(1m=20u) .ds -- \(*W\h'-12u'\(*W\h'-8u'-\"  diablo 12 pitch
.    ds L" ""
.    ds R" ""
.    ds C` ""
.    ds C' ""
'br\}
.el\{\
.    ds -- \|\(em\|
.    ds PI \(*p
.    ds L" ``
.    ds R" ''
.    ds C`
.    ds C'
'br\}
.\"
.\" Escape single quotes in literal strings from groff's Unicode transform.
.ie \n(.g .ds Aq \(aq
.el       .ds Aq '
.\"
.\" If the F register is turned on, we'll generate index entries on stderr for
.\" titles (.TH), headers (.SH), subsections (.SS), items (.Ip), and index
.\" entries marked with X<> in POD.  Of course, you'll have to process the
.\" output yourself in some meaningful fashion.
.\"
.\" Avoid warning from groff about undefined register 'F'.
.de IX
..
.nr rF 0
.if \n(.g .if rF .nr rF 1
.if (\n(rF:(\n(.g==0)) \{
.    if \nF \{
.        de IX
.        tm Index:\\$1\t\\n%\t"\\$2"
..
.        if !\nF==2 \{
.            nr % 0
.            nr F 2
.        \}
.    \}
.\}
.rr rF
.\"
.\" Accent mark definitions (@(#)ms.acc 1.5 88/02/08 SMI; from UCB 4.2).
.\" Fear.  Run.  Save yourself.  No user-serviceable parts.
.    \" fudge factors for nroff and troff
.if n \{\
.    ds #H 0
.    ds #V .8m
.    ds #F .3m
.    ds #[ \f1
.    ds #] \fP
.\}
.if t \{\
.    ds #H ((1u-(\\\\n(.fu%2u))*.13m)
.    ds #V .6m
.    ds #F 0
.    ds #[ \&
.    ds #] \&
.\}
.    \" simple accents for nroff and troff
.if n \{\
.    ds ' \&
.    ds ` \&
.    ds ^ \&
.    ds , \&
.    ds ~ ~
.    ds /
.\}
.if t \{\
.    ds ' \\k:\h'-(\\n(.wu*8/10-\*(#H)'\'\h"|\\n:u"
.    ds ` \\k:\h'-(\\n(.wu*8/10-\*(#H)'\`\h'|\\n:u'
.    ds ^ \\k:\h'-(\\n(.wu*10/11-\*(#H)'^\h'|\\n:u'
.    ds , \\k:\h'-(\\n(.wu*8/10)',\h'|\\n:u'
.    ds ~ \\k:\h'-(\\n(.wu-\*(#H-.1m)'~\h'|\\n:u'
.    ds / \\k:\h'-(\\n(.wu*8/10-\*(#H)'\z\(sl\h'|\\n:u'
.\}
.    \" troff and (daisy-wheel) nroff accents
.ds : \\k:\h'-(\\n(.wu*8/10-\*(#H+.1m+\*(#F)'\v'-\*(#V'\z.\h'.2m+\*(#F'.\h'|\\n:u'\v'\*(#V'
.ds 8 \h'\*(#H'\(*b\h'-\*(#H'
.ds o \\k:\h'-(\\n(.wu+\w'\(de'u-\*(#H)/2u'\v'-.3n'\*(#[\z\(de\v'.3n'\h'|\\n:u'\*(#]
.ds d- \h'\*(#H'\(pd\h'-\w'~'u'\v'-.25m'\f2\(hy\fP\v'.25m'\h'-\*(#H'
.ds D- D\\k:\h'-\w'D'u'\v'-.11m'\z\(hy\v'.11m'\h'|\\n:u'
.ds th \*(#[\v'.3m'\s+1I\s-1\v'-.3m'\h'-(\w'I'u*2/3)'\s-1o\s+1\*(#]
.ds Th \*(#[\s+2I\s-2\h'-\w'I'u*3/5'\v'-.3m'o\v'.3m'\*(#]
.ds ae a\h'-(\w'a'u*4/10)'e
.ds Ae A\h'-(\w'A'u*4/10)'E
.    \" corrections for vroff
.if v .ds ~ \\k:\h'-(\\n(.wu*9/10-\*(#H)'\s-2\u~\d\s+2\h'|\\n:u'
.if v .ds ^ \\k:\h'-(\\n(.wu*10/11-\*(#H)'\v'-.4m'^\v'.4m'\h'|\\n:u'
.    \" for low resolution devices (crt and lpr)
.if \n(.H>23 .if \n(.V>19 \
\{\
.    ds : e
.    ds 8 ss
.    ds o a
.    ds d- d\h'-1'\(ga
.    ds D- D\h'-1'\(hy
.    ds th \o'bp'
.    ds Th \o'LP'
.    ds ae ae
.    ds Ae AE
.\}
.rm #[ #] #H #V #F C
.\" ========================================================================
.\"
.IX Title "Tie::RDBM 3"
.TH Tie::RDBM 3 "2013-04-05" "perl v5.16.3" "User Contributed Perl Documentation"
.\" For nroff, turn off justification.  Always turn off hyphenation; it makes
.\" way too many mistakes in technical documents.
.if n .ad l
.nh
.SH "NAME"
Tie::RDBM \- Tie hashes to relational databases
.SH "SYNOPSIS"
.IX Header "SYNOPSIS"
.Vb 9
\&  use Tie::RDBM;
\&  tie %h,\*(AqTie::RDBM\*(Aq,\*(Aqmysql:test\*(Aq,{table=>\*(AqDemo\*(Aq,create=>1,autocommit=>0};
\&  $h{\*(Aqkey1\*(Aq} = \*(AqSome data here\*(Aq;
\&  $h{\*(Aqkey2\*(Aq} = 42;
\&  $h{\*(Aqkey3\*(Aq} = { complex=>[\*(Aqdata\*(Aq,\*(Aqstructure\*(Aq,\*(Aqhere\*(Aq],works=>\*(Aqtrue\*(Aq };
\&  $h{\*(Aqkey4\*(Aq} = new Foobar(\*(AqObjects work too\*(Aq);
\&  print $h{\*(Aqkey3\*(Aq}\->{complex}\->[0];
\&  tied(%h)\->commit;
\&  untie %h;
.Ve
.SH "DESCRIPTION"
.IX Header "DESCRIPTION"
This module allows you to tie Perl associative arrays (hashes) to \s-1SQL\s0
databases using the \s-1DBI\s0 interface.  The tied hash is associated with a
table in a local or networked database.  One field of the table becomes the
hash key, and another becomes the value.  Once tied, all the standard
hash operations work, including iteration over keys and values.
.PP
If you have the Storable module installed, you may store arbitrarily
complex Perl structures (including objects) into the hash and later
retrieve them.  When used in conjunction with a network-accessible
database, this provides a simple way to transmit data structures
between Perl programs on two different machines.
.SH "TIEING A DATABASE"
.IX Header "TIEING A DATABASE"
.Vb 1
\&   tie %VARIABLE,Tie::RDBM,DSN [,\e%OPTIONS]
.Ve
.PP
You tie a variable to a database by providing the variable name, the
tie interface (always \*(L"Tie::RDBM\*(R"), the data source name, and an
optional hash reference containing various options to be passed to the
module and the underlying database driver.
.PP
The data source may be a valid DBI-style data source string of the
form \*(L"dbi:driver:database_name[:other information]\*(R", or a
previously-opened database handle.  See the documentation for \s-1DBI\s0 and
your \s-1DBD\s0 driver for details.  Because the initial \*(L"dbi\*(R" is always
present in the data source, Tie::RDBM will automatically add it for
you.
.PP
The options array contains a set of option/value pairs.  If not
provided, defaults are assumed.  The options are:
.IP "user ['']" 4
.IX Item "user ['']"
Account name to use for database authentication, if necessary.
Default is an empty string (no authentication necessary).
.IP "password ['']" 4
.IX Item "password ['']"
Password to use for database authentication, if necessary.  Default is
an empty string (no authentication necessary).
.IP "db ['']" 4
.IX Item "db ['']"
The data source, if not provided in the argument.  This allows an
alternative calling style:
.Sp
.Vb 1
\&   tie(%h,Tie::RDBM,{db=>\*(Aqdbi:mysql:test\*(Aq,create=>1};
.Ve
.IP "table ['pdata']" 4
.IX Item "table ['pdata']"
The name of the table in which the hash key/value pairs will be
stored.
.IP "key ['pkey']" 4
.IX Item "key ['pkey']"
The name of the column in which the hash key will be found.  If not
provided, defaults to \*(L"pkey\*(R".
.IP "value ['pvalue']" 4
.IX Item "value ['pvalue']"
The name of the column in which the hash value will be found.  If not
provided, defaults to \*(L"pvalue\*(R".
.IP "frozen ['pfrozen']" 4
.IX Item "frozen ['pfrozen']"
The name of the column that stores the boolean information indicating
that a complex data structure has been \*(L"frozen\*(R" using Storable's
\&\fIfreeze()\fR function.  If not provided, defaults to \*(L"pfrozen\*(R".
.Sp
\&\s-1NOTE:\s0 if this field is not present in the database table, or if the
database is incapable of storing binary structures, Storable features
will be disabled.
.IP "create [0]" 4
.IX Item "create [0]"
If set to a true value, allows the module to create the database table
if it does not already exist.  The module emits a \s-1CREATE TABLE\s0 command
and gives the key, value and frozen fields the data types most
appropriate for the database driver (from a lookup table maintained in
a package global, see \s-1DATATYPES\s0 below).
.Sp
The success of table creation depends on whether you have table create
access for the database.
.Sp
The default is not to create a table.  \fItie()\fR will fail with a fatal
error.
.IP "drop [0]" 4
.IX Item "drop [0]"
If the indicated database table exists, but does not have the required
key and value fields, Tie::RDBM can try to add the required fields to
the table.  Currently it does this by the drastic expedient of
\&\s-1DROPPING\s0 the table entirely and creating a new empty one.  If the drop
option is set to true, Tie::RDBM will perform this radical
restructuring.  Otherwise \fItie()\fR will fail with a fatal error.  \*(L"drop\*(R"
implies \*(L"create\*(R".  This option defaults to false.
.Sp
A future version of Tie::RDBM may implement a last radical
restructuring method; differences in \s-1DBI\s0 drivers and database
capabilities make this task harder than it would seem.
.IP "autocommit [1]" 4
.IX Item "autocommit [1]"
If set to a true value, the \*(L"autocommit\*(R" option causes the database
driver to commit after every store statement.  If set to a false
value, this option will not commit to the database until you
explicitly call the Tie::RDBM \fIcommit()\fR method.
.Sp
The autocommit option defaults to true.
.IP "\s-1DEBUG\s0 [0]" 4
.IX Item "DEBUG [0]"
When the \*(L"\s-1DEBUG\*(R"\s0 option is set to a true value the module will echo
the contents of \s-1SQL\s0 statements and other debugging information to
standard error.
.SH "USING THE TIED ARRAY"
.IX Header "USING THE TIED ARRAY"
The standard fetch, store, \fIkeys()\fR, \fIvalues()\fR and \fIeach()\fR functions will
work as expected on the tied array.  In addition, the following
methods are available on the underlying object, which you can obtain
with the standard \fItie()\fR operator:
.IP "\fIcommit()\fR" 4
.IX Item "commit()"
.Vb 1
\&   (tied %h)\->commit();
.Ve
.Sp
When using a database with the autocommit option turned off, values
that are stored into the hash will not become permanent until \fIcommit()\fR
is called.  Otherwise they are lost when the application terminates or
the hash is untied.
.Sp
Some \s-1SQL\s0 databases don't support transactions, in which case you will
see a warning message if you attempt to use this function.
.IP "\fIrollback()\fR" 4
.IX Item "rollback()"
.Vb 1
\&   (tied %h)\->rollback();
.Ve
.Sp
When using a database with the autocommit option turned off, this
function will roll back changes to the database to the state they were
in at the last \fIcommit()\fR.  This function has no effect on database that
don't support transactions.
.SH "DATABASES AND DATATYPES"
.IX Header "DATABASES AND DATATYPES"
Perl is a weakly typed language.  Databases are strongly typed.  When
translating from databases to Perl there is inevitably some data type
conversion that you must worry about.  I have tried to keep the
details as transparent as possible without sacrificing power; this
section discusses the tradeoffs.
.PP
If you wish to tie a hash to a preexisting database, specify the
database name, the table within the database, and the fields you wish
to use for the keys and values.  These fields can be of any type that
you choose, but the data type will limit what can be stored there.
For example, if the key field is of type \*(L"int\*(R", then any numeric value
will be a valid key, but an attempt to use a string as a key will
result in a run time error.  If a key or value is too long to fit into
the data field, it will be truncated silently.
.PP
For performance reasons, the key field should be a primary key, or at
least an indexed field.  It should also be unique.  If a key is
present more than once in a table, an attempt to fetch it will return
the first record found by the \s-1SQL\s0 select statement.
.PP
If you wish to store Perl references in the database, the module needs
an additional field in which it can store a flag indicating whether
the data value is a simple or a complex type.  This \*(L"frozen\*(R" field is
treated as a boolean value.  A \*(L"tinyint\*(R" data type is recommended, but
strings types will work as well.
.PP
In a future version of this module, the \*(L"frozen\*(R" field may be turned
into a general \*(L"datatype\*(R" field in order to minimize storage.  For
future compatability, please use an integer for the frozen field.
.PP
If you use the \*(L"create\*(R" and/or \*(L"drop\*(R" options, the module will
automatically attempt to create a table for its own use in the
database if a suitable one isn't found.  It uses information defined
in the package variable \f(CW%Tie::RDBM::Types\fR to determine what kind of
data types to create.  This variable is indexed by database driver.
Each index contains a four-element array indicating what data type to
use for each of the key, value and frozen fields, and whether the
database can support binary types.  Since I have access to only a
limited number of databases, the table is currently short:
.PP
.Vb 1
\&   Driver     Key Field      Value Field     Frozen Field  Binary?
\&
\&   mysq       varchar(127)   longblob        tinyint       1
\&   mSQL       char(255)      char(255)       int           0
\&   Sybase     varchar(255)   varbinary(255)  tinyint       1
\&   default    varchar(255)   varbinary(255)  tinyint       1
.Ve
.PP
The \*(L"default\*(R" entry is used for any driver not specifically
mentioned.
.PP
You are free to add your own entries to this table, or make
corrections.  Please send me e\-mail with any revisions you make so
that I can share the wisdom.
.SH "STORABLE CAVEATS"
.IX Header "STORABLE CAVEATS"
Because the Storable module packs Perl structures in a binary format,
only those databases that support a \*(L"varbinary\*(R" or \*(L"blob\*(R" type can
handle complex datatypes.  Furthermore, some databases have strict
limitations on the size of these structures.  For example, SyBase and
\&\s-1MS SQL\s0 Server have a \*(L"varbinary\*(R" type that maxes out at 255 bytes.
For structures larger than this, the databases provide an \*(L"image\*(R" type
in which storage is allocated in 2K chunks!  Worse, access to this
image type uses a non-standard \s-1SQL\s0 extension that is not supported by
\&\s-1DBI.\s0
.PP
Databases that do not support binary fields cannot use the Storable
feature.  If you attempt to store a reference to a complex data type
in one of these databases it will be converted into strings like
\&\*(L"\s-1HASH\s0(0x8222cf4)\*(R", just as it would be if you tried the same trick
with a conventional tied \s-1DBM\s0 hash.  If the database supports binary
fields of restricted length, large structures may be silently
truncated.  Caveat emptor.
.PP
It's also important to realize the limitations of the Storable
mechanism.  You can store and retrieve entire data structures, but you
can't twiddle with individual substructures and expect them to persist
when the process exits.  To update a data structure, you must fetch it
from the hash, make the desired modifications, then store it back into
the hash, as the example below shows:
.PP
\&\fBProcess #1:\fR
   tie \f(CW%h\fR,'Tie::RDBM','mysql:Employees:host.somewhere.com',
                   {table=>'employee',user=>'fred',password=>'xyzzy'};
   \f(CW$h\fR{'Anne'} = { office=>'999 Infinity Drive, Rm 203',
                  age    =>  29,
                  salary =>  32100 };
   \f(CW$h\fR{'Mark'} = { office=>'000 Iteration Circle, Rm \-123',
                  age    =>  32,
                  salary =>  35000 };
.PP
\&\fBProcess #2:\fR
   tie \f(CW%i\fR,'Tie::RDBM','mysql:Employees:host.somewhere.com',
                   {table=>'employee',user=>'george',password=>'kumquat2'};
   foreach (keys \f(CW%i\fR) {
      \f(CW$info\fR = \f(CW$i\fR{$_};
      if ($info\->{age} > 30) {
         # Give the oldies a \f(CW$1000\fR raise
         \f(CW$info\fR\->{salary} += 1000;  
         \f(CW$i\fR{$_} = \f(CW$info\fR;
      }
   }
.PP
This example also demonstrates how two Perl scripts running on
different machines can use Tie::RDBM to share complex data structures
(in this case, the employee record) without resorting to sockets,
remote procedure calls, shared memory, or other gadgets
.SH "PERFORMANCE"
.IX Header "PERFORMANCE"
What is the performance hit when you use this module?  It can be
significant.  I used a simple benchmark in which Perl parsed a 6180
word text file into individual words and stored them into a database,
incrementing the word count with each store.  The benchmark then read
out the words and their counts in an \fIeach()\fR loop.  The database driver
was mySQL, running on a 133 MHz Pentium laptop with Linux 2.0.30.  I
compared Tie::RDBM, to DB_File, and to the same task using vanilla \s-1DBI
SQL\s0 statements.  The results are shown below:
.PP
.Vb 4
\&              STORE       EACH() LOOP
\&  Tie::RDBM     28 s        2.7  s
\&  Vanilla DBI   15 s        2.0  s
\&  DB_File        3 s        1.08 s
.Ve
.PP
During stores, there is an approximately 2X penalty compared to
straight \s-1DBI,\s0 and a 15X penalty over using DB_File databases.  For the
\&\fIeach()\fR loop (which is dominated by reads), the performance is 2\-3
times worse than DB_File and much worse than a vanilla \s-1SQL\s0 statement.
I have not investigated the bottlenecks.
.SH "TO DO LIST"
.IX Header "TO DO LIST"
.Vb 2
\&   \- Store strings, numbers and data structures in separate
\&     fields for space and performance efficiency.
\&
\&    \- Expand data types table to other database engines.
\&
\&    \- Catch internal changes to data structures and write them into
\&      database automatically.
.Ve
.SH "BUGS"
.IX Header "BUGS"
Yes.
.SH "AUTHOR"
.IX Header "AUTHOR"
Lincoln Stein, lstein@w3.org
.SH "COPYRIGHT"
.IX Header "COPYRIGHT"
.Vb 1
\&  Copyright (c) 1998, Lincoln D. Stein
.Ve
.PP
This library is free software; you can redistribute it and/or
modify it under the same terms as Perl itself.
.SH "AVAILABILITY"
.IX Header "AVAILABILITY"
The latest version can be obtained from:
.PP
.Vb 1
\&   http://www.genome.wi.mit.edu/~lstein/Tie\-DBM/
.Ve
.SH "SEE ALSO"
.IX Header "SEE ALSO"
\&\fIperl\fR\|(1), \s-1\fIDBI\s0\fR\|(3), \fIStorable\fR\|(3)