1Tie::RDBM(3)          User Contributed Perl Documentation         Tie::RDBM(3)
2
3
4

NAME

6       Tie::RDBM - Tie hashes to relational databases
7

SYNOPSIS

9         use Tie::RDBM;
10         tie %h,'Tie::RDBM','mysql:test',{table=>'Demo',create=>1,autocommit=>0};
11         $h{'key1'} = 'Some data here';
12         $h{'key2'} = 42;
13         $h{'key3'} = { complex=>['data','structure','here'],works=>'true' };
14         $h{'key4'} = new Foobar('Objects work too');
15         print $h{'key3'}->{complex}->[0];
16         tied(%h)->commit;
17         untie %h;
18

DESCRIPTION

20       This module allows you to tie Perl associative arrays (hashes) to SQL
21       databases using the DBI interface.  The tied hash is associated with a
22       table in a local or networked database.  One field of the table becomes
23       the hash key, and another becomes the value.  Once tied, all the
24       standard hash operations work, including iteration over keys and
25       values.
26
27       If you have the Storable module installed, you may store arbitrarily
28       complex Perl structures (including objects) into the hash and later
29       retrieve them.  When used in conjunction with a network-accessible
30       database, this provides a simple way to transmit data structures
31       between Perl programs on two different machines.
32

TIEING A DATABASE

34          tie %VARIABLE,Tie::RDBM,DSN [,\%OPTIONS]
35
36       You tie a variable to a database by providing the variable name, the
37       tie interface (always "Tie::RDBM"), the data source name, and an
38       optional hash reference containing various options to be passed to the
39       module and the underlying database driver.
40
41       The data source may be a valid DBI-style data source string of the form
42       "dbi:driver:database_name[:other information]", or a previously-opened
43       database handle.  See the documentation for DBI and your DBD driver for
44       details.  Because the initial "dbi" is always present in the data
45       source, Tie::RDBM will automatically add it for you.
46
47       The options array contains a set of option/value pairs.  If not
48       provided, defaults are assumed.  The options are:
49
50       user ['']
51           Account name to use for database authentication, if necessary.
52           Default is an empty string (no authentication necessary).
53
54       password ['']
55           Password to use for database authentication, if necessary.  Default
56           is an empty string (no authentication necessary).
57
58       db ['']
59           The data source, if not provided in the argument.  This allows an
60           alternative calling style:
61
62              tie(%h,Tie::RDBM,{db=>'dbi:mysql:test',create=>1};
63
64       table ['pdata']
65           The name of the table in which the hash key/value pairs will be
66           stored.
67
68       key ['pkey']
69           The name of the column in which the hash key will be found.  If not
70           provided, defaults to "pkey".
71
72       value ['pvalue']
73           The name of the column in which the hash value will be found.  If
74           not provided, defaults to "pvalue".
75
76       frozen ['pfrozen']
77           The name of the column that stores the boolean information
78           indicating that a complex data structure has been "frozen" using
79           Storable's freeze() function.  If not provided, defaults to
80           "pfrozen".
81
82           NOTE: if this field is not present in the database table, or if the
83           database is incapable of storing binary structures, Storable
84           features will be disabled.
85
86       create [0]
87           If set to a true value, allows the module to create the database
88           table if it does not already exist.  The module emits a CREATE
89           TABLE command and gives the key, value and frozen fields the data
90           types most appropriate for the database driver (from a lookup table
91           maintained in a package global, see DATATYPES below).
92
93           The success of table creation depends on whether you have table
94           create access for the database.
95
96           The default is not to create a table.  tie() will fail with a fatal
97           error.
98
99       drop [0]
100           If the indicated database table exists, but does not have the
101           required key and value fields, Tie::RDBM can try to add the
102           required fields to the table.  Currently it does this by the
103           drastic expedient of DROPPING the table entirely and creating a new
104           empty one.  If the drop option is set to true, Tie::RDBM will
105           perform this radical restructuring.  Otherwise tie() will fail with
106           a fatal error.  "drop" implies "create".  This option defaults to
107           false.
108
109           A future version of Tie::RDBM may implement a last radical
110           restructuring method; differences in DBI drivers and database
111           capabilities make this task harder than it would seem.
112
113       autocommit [1]
114           If set to a true value, the "autocommit" option causes the database
115           driver to commit after every store statement.  If set to a false
116           value, this option will not commit to the database until you
117           explicitly call the Tie::RDBM commit() method.
118
119           The autocommit option defaults to true.
120
121       DEBUG [0]
122           When the "DEBUG" option is set to a true value the module will echo
123           the contents of SQL statements and other debugging information to
124           standard error.
125

USING THE TIED ARRAY

127       The standard fetch, store, keys(), values() and each() functions will
128       work as expected on the tied array.  In addition, the following methods
129       are available on the underlying object, which you can obtain with the
130       standard tie() operator:
131
132       commit()
133              (tied %h)->commit();
134
135           When using a database with the autocommit option turned off, values
136           that are stored into the hash will not become permanent until
137           commit() is called.  Otherwise they are lost when the application
138           terminates or the hash is untied.
139
140           Some SQL databases don't support transactions, in which case you
141           will see a warning message if you attempt to use this function.
142
143       rollback()
144              (tied %h)->rollback();
145
146           When using a database with the autocommit option turned off, this
147           function will roll back changes to the database to the state they
148           were in at the last commit().  This function has no effect on
149           database that don't support transactions.
150

DATABASES AND DATATYPES

152       Perl is a weakly typed language.  Databases are strongly typed.  When
153       translating from databases to Perl there is inevitably some data type
154       conversion that you must worry about.  I have tried to keep the details
155       as transparent as possible without sacrificing power; this section
156       discusses the tradeoffs.
157
158       If you wish to tie a hash to a preexisting database, specify the
159       database name, the table within the database, and the fields you wish
160       to use for the keys and values.  These fields can be of any type that
161       you choose, but the data type will limit what can be stored there.  For
162       example, if the key field is of type "int", then any numeric value will
163       be a valid key, but an attempt to use a string as a key will result in
164       a run time error.  If a key or value is too long to fit into the data
165       field, it will be truncated silently.
166
167       For performance reasons, the key field should be a primary key, or at
168       least an indexed field.  It should also be unique.  If a key is present
169       more than once in a table, an attempt to fetch it will return the first
170       record found by the SQL select statement.
171
172       If you wish to store Perl references in the database, the module needs
173       an additional field in which it can store a flag indicating whether the
174       data value is a simple or a complex type.  This "frozen" field is
175       treated as a boolean value.  A "tinyint" data type is recommended, but
176       strings types will work as well.
177
178       In a future version of this module, the "frozen" field may be turned
179       into a general "datatype" field in order to minimize storage.  For
180       future compatibility, please use an integer for the frozen field.
181
182       If you use the "create" and/or "drop" options, the module will
183       automatically attempt to create a table for its own use in the database
184       if a suitable one isn't found.  It uses information defined in the
185       package variable %Tie::RDBM::Types to determine what kind of data types
186       to create.  This variable is indexed by database driver.  Each index
187       contains a four-element array indicating what data type to use for each
188       of the key, value and frozen fields, and whether the database can
189       support binary types.  Since I have access to only a limited number of
190       databases, the table is currently short:
191
192          Driver     Key Field      Value Field     Frozen Field  Binary?
193
194          mysq       varchar(127)   longblob        tinyint       1
195          mSQL       char(255)      char(255)       int           0
196          Sybase     varchar(255)   varbinary(255)  tinyint       1
197          default    varchar(255)   varbinary(255)  tinyint       1
198
199       The "default" entry is used for any driver not specifically mentioned.
200
201       You are free to add your own entries to this table, or make
202       corrections.  Please send me e-mail with any revisions you make so that
203       I can share the wisdom.
204

STORABLE CAVEATS

206       Because the Storable module packs Perl structures in a binary format,
207       only those databases that support a "varbinary" or "blob" type can
208       handle complex datatypes.  Furthermore, some databases have strict
209       limitations on the size of these structures.  For example, SyBase and
210       MS SQL Server have a "varbinary" type that maxes out at 255 bytes.  For
211       structures larger than this, the databases provide an "image" type in
212       which storage is allocated in 2K chunks!  Worse, access to this image
213       type uses a non-standard SQL extension that is not supported by DBI.
214
215       Databases that do not support binary fields cannot use the Storable
216       feature.  If you attempt to store a reference to a complex data type in
217       one of these databases it will be converted into strings like
218       "HASH(0x8222cf4)", just as it would be if you tried the same trick with
219       a conventional tied DBM hash.  If the database supports binary fields
220       of restricted length, large structures may be silently truncated.
221       Caveat emptor.
222
223       It's also important to realize the limitations of the Storable
224       mechanism.  You can store and retrieve entire data structures, but you
225       can't twiddle with individual substructures and expect them to persist
226       when the process exits.  To update a data structure, you must fetch it
227       from the hash, make the desired modifications, then store it back into
228       the hash, as the example below shows:
229
230       Process #1:
231          tie %h,'Tie::RDBM','mysql:Employees:host.somewhere.com',
232                          {table=>'employee',user=>'fred',password=>'xyzzy'};
233          $h{'Anne'} = { office=>'999 Infinity Drive, Rm 203',
234                         age    =>  29,
235                         salary =>  32100 };
236          $h{'Mark'} = { office=>'000 Iteration Circle, Rm -123',
237                         age    =>  32,
238                         salary =>  35000 };
239
240       Process #2:
241          tie %i,'Tie::RDBM','mysql:Employees:host.somewhere.com',
242                          {table=>'employee',user=>'george',
243                           password=>'kumquat2'};
244          foreach (keys %i) {
245             $info = $i{$_};
246             if ($info->{age} > 30) {
247                # Give the oldies a $1000 raise
248                $info->{salary} += 1000;
249                $i{$_} = $info;
250             }
251          }
252
253       This example also demonstrates how two Perl scripts running on
254       different machines can use Tie::RDBM to share complex data structures
255       (in this case, the employee record) without resorting to sockets,
256       remote procedure calls, shared memory, or other gadgets
257

PERFORMANCE

259       What is the performance hit when you use this module?  It can be
260       significant.  I used a simple benchmark in which Perl parsed a 6180
261       word text file into individual words and stored them into a database,
262       incrementing the word count with each store.  The benchmark then read
263       out the words and their counts in an each() loop.  The database driver
264       was mySQL, running on a 133 MHz Pentium laptop with Linux 2.0.30.  I
265       compared Tie::RDBM, to DB_File, and to the same task using vanilla DBI
266       SQL statements.  The results are shown below:
267
268                     STORE       EACH() LOOP
269         Tie::RDBM     28 s        2.7  s
270         Vanilla DBI   15 s        2.0  s
271         DB_File        3 s        1.08 s
272
273       During stores, there is an approximately 2X penalty compared to
274       straight DBI, and a 15X penalty over using DB_File databases.  For the
275       each() loop (which is dominated by reads), the performance is 2-3 times
276       worse than DB_File and much worse than a vanilla SQL statement.  I have
277       not investigated the bottlenecks.
278

TO DO LIST

280          - Store strings, numbers and data structures in separate
281            fields for space and performance efficiency.
282
283           - Expand data types table to other database engines.
284
285           - Catch internal changes to data structures and write them into
286             database automatically.
287

BUGS

289       Yes.
290

AUTHOR

292       Lincoln Stein, lstein@w3.org
293
295         Copyright (c) 1998, Lincoln D. Stein
296
297       This library is free software; you can redistribute it and/or modify it
298       under the same terms as Perl itself.
299

AVAILABILITY

301       The latest version can be obtained from:
302
303          http://www.genome.wi.mit.edu/~lstein/Tie-DBM/
304

SEE ALSO

306       perl(1), DBI(3), Storable(3)
307
308
309
310perl v5.34.0                      2021-07-23                      Tie::RDBM(3)
Impressum