1Tie::RDBM(3) User Contributed Perl Documentation Tie::RDBM(3)
2
6 Tie::RDBM - Tie hashes to relational databases
7
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
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 stan‐
24 dard hash operations work, including iteration over keys and values.
25 If you have the Storable module installed, you may store arbitrarily
27 complex Perl structures (including objects) into the hash and later
28 retrieve them. When used in conjunction with a network-accessible
29 database, this provides a simple way to transmit data structures
30 between Perl programs on two different machines.
31
33 tie %VARIABLE,Tie::RDBM,DSN [,\%OPTIONS]
34 You tie a variable to a database by providing the variable name, the
36 tie interface (always "Tie::RDBM"), the data source name, and an
37 optional hash reference containing various options to be passed to the
38 module and the underlying database driver.
39 The data source may be a valid DBI-style data source string of the form
41 "dbi:driver:database_name[:other information]", or a previously-opened
42 database handle. See the documentation for DBI and your DBD driver for
43 details. Because the initial "dbi" is always present in the data
44 source, Tie::RDBM will automatically add it for you.
45 The options array contains a set of option/value pairs. If not pro‐
47 vided, defaults are assumed. The options are:
48 user ['']
50 Account name to use for database authentication, if necessary.
51 Default is an empty string (no authentication necessary).
52 password ['']
54 Password to use for database authentication, if necessary. Default
55 is an empty string (no authentication necessary).
56 db ['']
58 The data source, if not provided in the argument. This allows an
59 alternative calling style:
60 tie(%h,Tie::RDBM,{db=>'dbi:mysql:test',create=>1};
62 table ['pdata']
64 The name of the table in which the hash key/value pairs will be
65 stored.
66 key ['pkey']
68 The name of the column in which the hash key will be found. If not
69 provided, defaults to "pkey".
70 value ['pvalue']
72 The name of the column in which the hash value will be found. If
73 not provided, defaults to "pvalue".
74 frozen ['pfrozen']
76 The name of the column that stores the boolean information indicat‐
77 ing that a complex data structure has been "frozen" using
78 Storable's freeze() function. If not provided, defaults to
79 "pfrozen".
80 NOTE: if this field is not present in the database table, or if the
82 database is incapable of storing binary structures, Storable fea‐
83 tures will be disabled.
84 create [0]
86 If set to a true value, allows the module to create the database
87 table if it does not already exist. The module emits a CREATE TA‐
88 BLE command and gives the key, value and frozen fields the data
89 types most appropriate for the database driver (from a lookup table
90 maintained in a package global, see DATATYPES below).
91 The success of table creation depends on whether you have table
93 create access for the database.
94 The default is not to create a table. tie() will fail with a fatal
96 error.
97 drop [0]
99 If the indicated database table exists, but does not have the
100 required key and value fields, Tie::RDBM can try to add the
101 required fields to the table. Currently it does this by the dras‐
102 tic expedient of DROPPING the table entirely and creating a new
103 empty one. If the drop option is set to true, Tie::RDBM will per‐
104 form this radical restructuring. Otherwise tie() will fail with a
105 fatal error. "drop" implies "create". This option defaults to
106 false.
107 A future version of Tie::RDBM may implement a last radical restruc‐
109 turing method; differences in DBI drivers and database capabilities
110 make this task harder than it would seem.
111 autocommit [1]
113 If set to a true value, the "autocommit" option causes the database
114 driver to commit after every store statement. If set to a false
115 value, this option will not commit to the database until you
116 explicitly call the Tie::RDBM commit() method.
117 The autocommit option defaults to true.
119 DEBUG [0]
121 When the "DEBUG" option is set to a true value the module will echo
122 the contents of SQL statements and other debugging information to
123 standard error.
124
126 The standard fetch, store, keys(), values() and each() functions will
127 work as expected on the tied array. In addition, the following methods
128 are available on the underlying object, which you can obtain with the
129 standard tie() operator:
130 commit()
132 (tied %h)->commit();
133 When using a database with the autocommit option turned off, values
135 that are stored into the hash will not become permanent until com‐
136 mit() is called. Otherwise they are lost when the application ter‐
137 minates or the hash is untied.
138 Some SQL databases don't support transactions, in which case you
140 will see a warning message if you attempt to use this function.
141 rollback()
143 (tied %h)->rollback();
144 When using a database with the autocommit option turned off, this
146 function will roll back changes to the database to the state they
147 were in at the last commit(). This function has no effect on data‐
148 base that don't support transactions.
149
151 Perl is a weakly typed language. Databases are strongly typed. When
152 translating from databases to Perl there is inevitably some data type
153 conversion that you must worry about. I have tried to keep the details
154 as transparent as possible without sacrificing power; this section dis‐
155 cusses the tradeoffs.
156 If you wish to tie a hash to a preexisting database, specify the data‐
158 base name, the table within the database, and the fields you wish to
159 use for the keys and values. These fields can be of any type that you
160 choose, but the data type will limit what can be stored there. For
161 example, if the key field is of type "int", then any numeric value will
162 be a valid key, but an attempt to use a string as a key will result in
163 a run time error. If a key or value is too long to fit into the data
164 field, it will be truncated silently.
165 For performance reasons, the key field should be a primary key, or at
167 least an indexed field. It should also be unique. If a key is present
168 more than once in a table, an attempt to fetch it will return the first
169 record found by the SQL select statement.
170 If you wish to store Perl references in the database, the module needs
172 an additional field in which it can store a flag indicating whether the
173 data value is a simple or a complex type. This "frozen" field is
174 treated as a boolean value. A "tinyint" data type is recommended, but
175 strings types will work as well.
176 In a future version of this module, the "frozen" field may be turned
178 into a general "datatype" field in order to minimize storage. For
179 future compatability, please use an integer for the frozen field.
180 If you use the "create" and/or "drop" options, the module will automat‐
182 ically attempt to create a table for its own use in the database if a
183 suitable one isn't found. It uses information defined in the package
184 variable %Tie::RDBM::Types to determine what kind of data types to cre‐
185 ate. This variable is indexed by database driver. Each index contains
186 a four-element array indicating what data type to use for each of the
187 key, value and frozen fields, and whether the database can support
188 binary types. Since I have access to only a limited number of data‐
189 bases, the table is currently short:
190 Driver Key Field Value Field Frozen Field Binary?
192 mysq varchar(127) longblob tinyint 1
194 mSQL char(255) char(255) int 0
195 Sybase varchar(255) varbinary(255) tinyint 1
196 default varchar(255) varbinary(255) tinyint 1
197 The "default" entry is used for any driver not specifically mentioned.
199 You are free to add your own entries to this table, or make correc‐
201 tions. Please send me e-mail with any revisions you make so that I can
202 share the wisdom.
203
205 Because the Storable module packs Perl structures in a binary format,
206 only those databases that support a "varbinary" or "blob" type can han‐
207 dle complex datatypes. Furthermore, some databases have strict limita‐
208 tions on the size of these structures. For example, SyBase and MS SQL
209 Server have a "varbinary" type that maxes out at 255 bytes. For struc‐
210 tures larger than this, the databases provide an "image" type in which
211 storage is allocated in 2K chunks! Worse, access to this image type
212 uses a non-standard SQL extension that is not supported by DBI.
213 Databases that do not support binary fields cannot use the Storable
215 feature. If you attempt to store a reference to a complex data type in
216 one of these databases it will be converted into strings like
217 "HASH(0x8222cf4)", just as it would be if you tried the same trick with
218 a conventional tied DBM hash. If the database supports binary fields
219 of restricted length, large structures may be silently truncated.
220 Caveat emptor.
221 It's also important to realize the limitations of the Storable mecha‐
223 nism. You can store and retrieve entire data structures, but you can't
224 twiddle with individual substructures and expect them to persist when
225 the process exits. To update a data structure, you must fetch it from
226 the hash, make the desired modifications, then store it back into the
227 hash, as the example below shows:
228 Process #1:
230 tie %h,'Tie::RDBM','mysql:Employees:host.somewhere.com',
231 {table=>'employee',user=>'fred',password=>'xyzzy'};
232 $h{'Anne'} = { office=>'999 Infinity Drive, Rm 203',
233 age => 29,
234 salary => 32100 };
235 $h{'Mark'} = { office=>'000 Iteration Circle, Rm -123',
236 age => 32,
237 salary => 35000 };
238 Process #2:
240 tie %i,'Tie::RDBM','mysql:Employees:host.somewhere.com',
241 {table=>'employee',user=>'george',pass‐
242 word=>'kumquat2'};
243 foreach (keys %i) {
244 $info = $i{$_};
245 if ($info->{age} > 30) {
246 # Give the oldies a $1000 raise
247 $info->{salary} += 1000;
248 $i{$_} = $info;
249 }
250 }
251 This example also demonstrates how two Perl scripts running on differ‐
253 ent machines can use Tie::RDBM to share complex data structures (in
254 this case, the employee record) without resorting to sockets, remote
255 procedure calls, shared memory, or other gadgets
256
258 What is the performance hit when you use this module? It can be sig‐
259 nificant. I used a simple benchmark in which Perl parsed a 6180 word
260 text file into individual words and stored them into a database, incre‐
261 menting the word count with each store. The benchmark then read out
262 the words and their counts in an each() loop. The database driver was
263 mySQL, running on a 133 MHz Pentium laptop with Linux 2.0.30. I com‐
264 pared Tie::RDBM, to DB_File, and to the same task using vanilla DBI SQL
265 statements. The results are shown below:
266 STORE EACH() LOOP
268 Tie::RDBM 28 s 2.7 s
269 Vanilla DBI 15 s 2.0 s
270 DB_File 3 s 1.08 s
271 During stores, there is an approximately 2X penalty compared to
273 straight DBI, and a 15X penalty over using DB_File databases. For the
274 each() loop (which is dominated by reads), the performance is 2-3 times
275 worse than DB_File and much worse than a vanilla SQL statement. I have
276 not investigated the bottlenecks.
277
279 - Store strings, numbers and data structures in separate
280 fields for space and performance efficiency.
281 - Expand data types table to other database engines.
283 - Catch internal changes to data structures and write them into
285 database automatically.
286
288 Yes.
289
291 Lincoln Stein, lstein@w3.org
292
294 Copyright (c) 1998, Lincoln D. Stein
295 This library is free software; you can redistribute it and/or modify it
297 under the same terms as Perl itself.
298
300 The latest version can be obtained from:
301 http://www.genome.wi.mit.edu/~lstein/Tie-DBM/
303
305 perl(1), DBI(3), Storable(3)
306perl v5.8.8 2005-12-28 Tie::RDBM(3)