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
24 standard hash operations work, including iteration over keys and
25 values.
26 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
34 tie %VARIABLE,Tie::RDBM,DSN [,\%OPTIONS]
35 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 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 The options array contains a set of option/value pairs. If not
48 provided, defaults are assumed. The options are:
49 user ['']
51 Account name to use for database authentication, if necessary.
52 Default is an empty string (no authentication necessary).
53 password ['']
55 Password to use for database authentication, if necessary. Default
56 is an empty string (no authentication necessary).
57 db ['']
59 The data source, if not provided in the argument. This allows an
60 alternative calling style:
61 tie(%h,Tie::RDBM,{db=>'dbi:mysql:test',create=>1};
63 table ['pdata']
65 The name of the table in which the hash key/value pairs will be
66 stored.
67 key ['pkey']
69 The name of the column in which the hash key will be found. If not
70 provided, defaults to "pkey".
71 value ['pvalue']
73 The name of the column in which the hash value will be found. If
74 not provided, defaults to "pvalue".
75 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 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 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 The success of table creation depends on whether you have table
94 create access for the database.
95 The default is not to create a table. tie() will fail with a fatal
97 error.
98 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 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 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 The autocommit option defaults to true.
120 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
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 commit()
133 (tied %h)->commit();
134 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 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 rollback()
144 (tied %h)->rollback();
145 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
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 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 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 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 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 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 Driver Key Field Value Field Frozen Field Binary?
193 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 The "default" entry is used for any driver not specifically mentioned.
200 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
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 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 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 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 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 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
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 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 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
280 - Store strings, numbers and data structures in separate
281 fields for space and performance efficiency.
282 - Expand data types table to other database engines.
284 - Catch internal changes to data structures and write them into
286 database automatically.
287
289 Yes.
290
292 Lincoln Stein, lstein@w3.org
293
295 Copyright (c) 1998, Lincoln D. Stein
296 This library is free software; you can redistribute it and/or modify it
298 under the same terms as Perl itself.
299
301 The latest version can be obtained from:
302 http://www.genome.wi.mit.edu/~lstein/Tie-DBM/
304
306 perl(1), DBI(3), Storable(3)
307perl v5.36.0 2022-07-22 Tie::RDBM(3)