1Tie::DBI(3) User Contributed Perl Documentation Tie::DBI(3)
2
6 Tie::DBI - Tie hashes to DBI relational databases
7
9 use Tie::DBI;
10 tie %h,'Tie::DBI','mysql:test','test','id',{CLOBBER=>1};
11 tie %h,'Tie::DBI',{db => 'mysql:test',
13 table => 'test',
14 key => 'id',
15 user => 'nobody',
16 password => 'ghost',
17 CLOBBER => 1};
18 # fetching keys and values
20 @keys = keys %h;
21 @fields = keys %{$h{$keys[0]}};
22 print $h{'id1'}->{'field1'};
23 while (($key,$value) = each %h) {
24 print "Key = $key:\n";
25 foreach (sort keys %$value) {
26 print "\t$_ => $value->{$_}\n";
27 }
28 }
29 # changing data
31 $h{'id1'}->{'field1'} = 'new value';
32 $h{'id1'} = { field1 => 'newer value',
33 field2 => 'even newer value',
34 field3 => "so new it's squeaky clean" };
35 # other functions
37 tied(%h)->commit;
38 tied(%h)->rollback;
39 tied(%h)->select_where('price > 1.20');
40 @fieldnames = tied(%h)->fields;
41 $dbh = tied(%h)->dbh;
42
44 This module allows you to tie Perl associative arrays (hashes) to SQL
45 databases using the DBI interface. The tied hash is associated with a
46 table in a local or networked database. One column becomes the hash
47 key. Each row of the table becomes an associative array, from which
48 individual fields can be set or retrieved.
49
51 To use this module, you must have the DBI interface and at least one
52 DBD (database driver) installed. Make sure that your database is up
53 and running, and that you can connect to it and execute queries using
54 DBI.
55 Creating the tie
57 tie %var,'Tie::DBI',[database,table,keycolumn] [,\%options]
59 Tie a variable to a database by providing the variable name, the tie
61 interface (always "Tie::DBI"), the data source name, the table to tie
62 to, and the column to use as the hash key. You may also pass various
63 flags to the interface in an associative array.
64 database
66 The database may either be a valid DBI-style data source string of
67 the form "dbi:driver:database_name[:other information]", or a data‐
68 base handle that has previously been opened. See the documentation
69 for DBI and your DBD driver for details. Because the initial "dbi"
70 is always present in the data source, Tie::DBI will add it for you
71 if necessary.
72 Note that some drivers (Oracle in particular) have an irritating
74 habit of appending blanks to the end of fixed-length fields. This
75 will screw up Tie::DBI's routines for getting key names. To avoid
76 this you should create the database handle with a ChopBlanks option
77 of TRUE. You should also use a PrintError option of true to avoid
78 complaints during STORE and LISTFIELD calls.
79 table
81 The table in the database to bind to. The table must previously
82 have been created with a SQL CREATE statement. This module will
83 not create tables for you or modify the schema of the database.
84 key The column to use as the hash key. This column must prevoiusly
86 have been defined when the table was created. In order for this
87 module to work correctly, the key column must be declared unique
88 and not nullable. For best performance, the column should be also
89 be declared a key. These three requirements are automatically sat‐
90 isfied for primary keys.
91 It is possible to omit the database, table and keycolumn arguments, in
93 which case the module tries to retrieve the values from the options
94 array. The options array contains a set of option/value pairs. If not
95 provided, defaults are assumed. The options are:
96 user
98 Account name to use for database authentication, if necessary.
99 Default is an empty string (no authentication necessary).
100 password
102 Password to use for database authentication, if necessary. Default
103 is an empty string (no authentication necessary).
104 db The database to bind to the hash, if not provided in the argument
106 list. It may be a DBI-style data source string, or a previously-
107 opened database handle.
108 table
110 The name of the table to bind to the hash, if not provided in the
111 argument list.
112 key The name of the column to use as the hash key, if not provided in
114 the argument list.
115 CLOBBER (default 0)
117 This controls whether the database is writable via the bound hash.
118 A zero value (the default) makes the database essentially read
119 only. An attempt to store to the hash will result in a fatal
120 error. A CLOBBER value of 1 will allow you to change individual
121 fields in the database, and to insert new records, but not to
122 delete entire records. A CLOBBER value of 2 allows you to delete
123 records, but not to erase the entire table. A CLOBBER value of 3
124 or higher will allow you to erase the entire table.
125 Operation Clobber Comment
127 $i = $h{strawberries}->{price} 0 All read operations
129 $h{strawberries}->{price} += 5 1 Update fields
130 $h{bananas}={price=>23,quant=>3} 1 Add records
131 delete $h{strawberries} 2 Delete records
132 %h = () 3 Clear entire table
133 undef %h 3 Another clear operation
134 All database operations are contingent upon your access privileges.
136 If your account does not have write permission to the database,
137 hash store operations will fail despite the setting of CLOBBER.
138 AUTOCOMMIT (default 1)
140 If set to a true value, the "autocommit" option causes the database
141 driver to commit after every store statement. If set to a false
142 value, this option will not commit to the database until you
143 explicitly call the Tie::DBI commit() method.
144 The autocommit option defaults to true.
146 DEBUG (default 0)
148 When the DEBUG option is set to a non-zero value the module will
149 echo the contents of SQL statements and other debugging information
150 to standard error. Higher values of DEBUG result in more verbose
151 (and annoying) output.
152 WARN (default 1)
154 If set to a non-zero value, warns of illegal operations, such as
155 attempting to delete the value of the key column. If set to a zero
156 value, these errors will be ignored silently.
157 CASESENSITIV (default 0)
159 If set to a non-zero value, all Fieldnames are casesensitiv. Keep
160 in mind, that your database has to support casesensitiv Fields if
161 you want to use it.
162
164 The tied array represents the database table. Each entry in the hash
165 is a record, keyed on the column chosen in the tie() statement. Ordi‐
166 narily this will be the table's primary key, although any unique column
167 will do.
168 Fetching an individual record returns a reference to a hash of field
170 names and values. This hash reference is itself a tied object, so that
171 operations on it directly affect the database.
172 Fetching information
174 In the following examples, we will assume a database table structured
176 like this one:
177 -produce-
179 produce_id price quantity description
180 strawberries 1.20 8 Fresh Maine strawberries
182 apricots 0.85 2 Ripe Norwegian apricots
183 bananas 1.30 28 Sweet Alaskan bananas
184 kiwis 1.50 9 Juicy New York kiwi fruits
185 eggs 1.00 12 Farm-fresh Atlantic eggs
186 We tie the variable %produce to the table in this way:
188 tie %produce,'Tie::DBI',{db => 'mysql:stock',
190 table => 'produce',
191 key => 'produce_id',
192 CLOBBER => 2 # allow most updates
193 };
194 We can get the list of keys this way:
196 print join(",",keys %produce);
198 => strawberries,apricots,bananas,kiwis
199 Or get the price of eggs thusly:
201 $price = $produce{eggs}->{price};
203 print "The price of eggs = $price";
204 => The price of eggs = 1.2
205 String interpolation works as you would expect:
207 print "The price of eggs is still $produce{eggs}->{price}"
209 => The price of eggs is still 1.2
210 Various types of syntactic sugar are allowed. For example, you can
212 refer to $produce{eggs}{price} rather than $produce{eggs}->{price}.
213 Array slices are fully supported as well:
214 ($apricots,$kiwis) = @produce{apricots,kiwis};
216 print "Kiwis are $kiwis->{description};
217 => Kiwis are Juicy New York kiwi fruits
218 ($price,$description) = @{$produce{eggs}}{price,description};
220 => (2.4,'Farm-fresh Atlantic eggs')
221 If you provide the tied hash with a comma-delimited set of record
223 names, and you are not requesting an array slice, then the module does
224 something interesting. It generates a single SQL statement that
225 fetches the records from the database in a single pass (rather than the
226 multiple passes required for an array slice) and returns the result as
227 a reference to an array. For many records, this can be much faster.
228 For example:
229 $result = $produce{apricots,bananas};
231 => ARRAY(0x828a8ac)
232 ($apricots,$bananas) = @$result;
234 print "The price of apricots is $apricots->{price}";
235 => The price of apricots is 0.85
236 Field names work in much the same way:
238 ($price,$quantity) = @{$produce{apricots}{price,quantity}};
240 print "There are $quantity apricots at $price each";
241 => There are 2 apricots at 0.85 each";
242 Note that this takes advantage of a bit of Perl syntactic sugar which
244 automagically treats $h{'a','b','c'} as if the keys were packed
245 together with the $; pack character. Be careful not to fall into this
246 trap:
247 $result = $h{join( ',', 'apricots', 'bananas' )};
249 => undefined
250 What you really want is this:
252 $result = $h{join( $;, 'apricots', 'bananas' )};
254 => ARRAY(0x828a8ac)
255 Updating information
257 If CLOBBER is set to a non-zero value (and the underlying database
259 privileges allow it), you can update the database with new values. You
260 can operate on entire records at once or on individual fields within a
261 record.
262 To insert a new record or update an existing one, assign a hash refer‐
264 ence to the record. For example, you can create a new record in %pro‐
265 duce with the key "avocados" in this manner:
266 $produce{avocados} = { price => 2.00,
268 quantity => 8,
269 description => 'Choice Irish avocados' };
270 This will work with any type of hash reference, including records
272 extracted from another table or database.
273 Only keys that correspond to valid fields in the table will be
275 accepted. You will be warned if you attempt to set a field that
276 doesn't exist, but the other fields will be correctly set. Likewise,
277 you will be warned if you attempt to set the key field. These warnings
278 can be turned off by setting the WARN option to a zero value. It is
279 not currently possible to add new columns to the table. You must do
280 this manually with the appropriate SQL commands.
281 The same syntax can be used to update an existing record. The fields
283 given in the hash reference replace those in the record. Fields that
284 aren't explicitly listed in the hash retain their previous values. In
285 the following example, the price and quantity of the "kiwis" record are
286 updated, but the description remains the same:
287 $produce{kiwis} = { price=>1.25,quantity=>20 };
289 You may update existing records on a field-by-field manner in the natu‐
291 ral way:
292 $produce{eggs}{price} = 1.30;
294 $produce{eggs}{price} *= 2;
295 print "The price of eggs is now $produce{eggs}{price}";
296 => The price of eggs is now 2.6.
297 Obligingly enough, you can use this syntax to insert new records too,
299 as in $produce{mangoes}{description}="Sun-ripened Idaho mangoes". How‐
300 ever, this type of update is inefficient because a separate SQL state‐
301 ment is generated for each field. If you need to update more than one
302 field at a time, use the record-oriented syntax shown earlier. It's
303 much more efficient because it gets the work done with a single SQL
304 command.
305 Insertions and updates may fail for any of a number of reasons, most
307 commonly:
308 1. You do not have sufficient privileges to update the database
310 2. The update would violate an integrity constraint, such as making a
311 non-nullable field null, overflowing a numeric field, storing a string
312 value in a numeric field, or violating a uniqueness constraint.
313 The module dies with an error message when it encounters an error dur‐
315 ing an update. To trap these erorrs and continue processing, wrap the
316 update an eval().
317 Other functions
319 The tie object supports several useful methods. In order to call these
321 methods, you must either save the function result from the tie() call
322 (which returns the object), or call tied() on the tie variable to
323 recover the object.
324 connect(), error(), errstr()
326 These are low-level class methods. Connect() is responsible for
327 establishing the connection with the DBI database. Errstr() and
328 error() return $DBI::errstr and $DBI::error respectively. You may
329 may override these methods in subclasses if you wish. For example,
330 replace connect() with this code in order to use persistent data‐
331 base connections in Apache modules:
332 use Apache::DBI; # somewhere in the declarations
334 sub connect {
335 my ($class,$dsn,$user,$password,$options) = @_;
336 return Apache::DBI->connect($dsn,$user,
337 $password,$options);
338 }
339 commit()
341 (tied %produce)->commit();
342 When using a database with the autocommit option turned off, values
344 that are stored into the hash will not become permanent until com‐
345 mit() is called. Otherwise they are lost when the application ter‐
346 minates or the hash is untied.
347 Some SQL databases don't support transactions, in which case you
349 will see a warning message if you attempt to use this function.
350 rollback()
352 (tied %produce)->rollback();
353 When using a database with the autocommit option turned off, this
355 function will roll back changes to the database to the state they
356 were in at the last commit(). This function has no effect on data‐
357 base that don't support transactions.
358 select_where()
360 @keys=(tied %produce)->select_where('price > 1.00 and quantity < 10');
361 This executes a limited form of select statement on the tied table
363 and returns a list of records that satisfy the conditions. The
364 argument you provide should be the contents of a SQL WHERE clause,
365 minus the keyword "WHERE" and everything that ordinarily precedes
366 it. Anything that is legal in the WHERE clause is allowed, includ‐
367 ing function calls, ordering specifications, and sub-selects. The
368 keys to those records that meet the specified conditions are
369 returned as an array, in the order in which the select statement
370 returned them.
371 Don't expect too much from this function. If you want to execute a
373 complex query, you're better off using the database handle (see
374 below) to make the SQL query yourself with the DBI interface.
375 dbh()
377 $dbh = (tied %produce)->dbh();
378 This returns the tied hash's underlying database handle. You can
380 use this handle to create and execute your own SQL queries.
381 CLOBBER, DEBUG, WARN
383 You can get and set the values of CLOBBER, DEBUG and WARN by
384 directly accessing the object's hash:
385 (tied %produce)->{DEBUG}++;
387 This lets you change the behavior of the tied hash on the fly, such
389 as temporarily granting your program write permission.
390 There are other variables there too, such as the name of the key
392 column and database table. Change them at your own risk!
393
395 What is the performance hit when you use this module rather than the
396 direct DBI interface? It can be significant. To measure the overhead,
397 I used a simple benchmark in which Perl parsed a 6180 word text file
398 into individual words and stored them into a database, incrementing the
399 word count with each store. The benchmark then read out the words and
400 their counts in an each() loop. The database driver was mySQL, running
401 on a 133 MHz Pentium laptop with Linux 2.0.30. I compared Tie::RDBM,
402 to DB_File, and to the same task using vanilla DBI SQL statements. The
403 results are shown below:
404 UPDATE FETCH
406 Tie::DBI 70 s 6.1 s
407 Vanilla DBI 14 s 2.0 s
408 DB_File 3 s 1.06 s
409 There is about a five-fold penalty for updates, and a three-fold
411 penalty for fetches when using this interface. Some of the penalty is
412 due to the overhead for creating sub-objects to handle individual
413 fields, and some of it is due to the inefficient way the store and
414 fetch operations are implemented. For example, using the tie inter‐
415 face, a statement like $h{record}{field}++ requires as much as four
416 trips to the database: one to verify that the record exists, one to
417 fetch the field, and one to store the incremented field back. If the
418 record doesn't already exist, an additional statement is required to
419 perform the insertion. I have experimented with cacheing schemes to
420 reduce the number of trips to the database, but the overhead of main‐
421 taining the cache is nearly equal to the performance improvement, and
422 cacheing raises a number of potential concurrency problems.
423 Clearly you would not want to use this interface for applications that
425 require a large number of updates to be processed rapidly.
426
429 The each() call produces a fatal error when used with the Sybase driver
430 to access Microsoft SQL server. This is because this server only allows
431 one query to be active at a given time. A workaround is to use keys()
432 to fetch all the keys yourself. It is not known whether real Sybase
433 databases suffer from the same problem.
434 The delete() operator will not work correctly for setting field values
436 to null with DBD::CSV or with DBD::Pg. CSV files do not have a good
437 conception of database nulls. Instead you will set the field to an
438 empty string. DBD::Pg just seems to be broken in this regard.
439
441 Lincoln Stein, lstein@cshl.org
442
444 Copyright (c) 1998, Lincoln D. Stein
445 This library is free software; you can redistribute it and/or modify it
447 under the same terms as Perl itself.
448
450 The latest version can be obtained from:
451 http://www.genome.wi.mit.edu/~lstein/Tie-DBI/
453
455 perl(1), DBI(3), Tie::RDBM(3)
456perl v5.8.8 2005-12-28 Tie::DBI(3)