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]
58 Tie a variable to a database by providing the variable name, the tie
60 interface (always "Tie::DBI"), the data source name, the table to tie
61 to, and the column to use as the hash key. You may also pass various
62 flags to the interface in an associative array.
63 database
65 The database may either be a valid DBI-style data source string of
66 the form "dbi:driver:database_name[:other information]", or a
67 database handle that has previously been opened. See the
68 documentation for DBI and your DBD driver for details. Because the
69 initial "dbi" is always present in the data source, Tie::DBI will
70 add it for you if necessary.
71 Note that some drivers (Oracle in particular) have an irritating
73 habit of appending blanks to the end of fixed-length fields. This
74 will screw up Tie::DBI's routines for getting key names. To avoid
75 this you should create the database handle with a ChopBlanks option
76 of TRUE. You should also use a PrintError option of true to avoid
77 complaints during STORE and LISTFIELD calls.
78 table
80 The table in the database to bind to. The table must previously
81 have been created with a SQL CREATE statement. This module will
82 not create tables for you or modify the schema of the database.
83 key The column to use as the hash key. This column must prevoiusly
85 have been defined when the table was created. In order for this
86 module to work correctly, the key column must be declared unique
87 and not nullable. For best performance, the column should be also
88 be declared a key. These three requirements are automatically
89 satisfied for primary keys.
90 It is possible to omit the database, table and keycolumn arguments, in
92 which case the module tries to retrieve the values from the options
93 array. The options array contains a set of option/value pairs. If not
94 provided, defaults are assumed. The options are:
95 user
97 Account name to use for database authentication, if necessary.
98 Default is an empty string (no authentication necessary).
99 password
101 Password to use for database authentication, if necessary. Default
102 is an empty string (no authentication necessary).
103 db The database to bind to the hash, if not provided in the argument
105 list. It may be a DBI-style data source string, or a previously-
106 opened database handle.
107 table
109 The name of the table to bind to the hash, if not provided in the
110 argument list.
111 key The name of the column to use as the hash key, if not provided in
113 the argument list.
114 CLOBBER (default 0)
116 This controls whether the database is writable via the bound hash.
117 A zero value (the default) makes the database essentially read
118 only. An attempt to store to the hash will result in a fatal
119 error. A CLOBBER value of 1 will allow you to change individual
120 fields in the database, and to insert new records, but not to
121 delete entire records. A CLOBBER value of 2 allows you to delete
122 records, but not to erase the entire table. A CLOBBER value of 3
123 or higher will allow you to erase the entire table.
124 Operation Clobber Comment
126 $i = $h{strawberries}->{price} 0 All read operations
128 $h{strawberries}->{price} += 5 1 Update fields
129 $h{bananas}={price=>23,quant=>3} 1 Add records
130 delete $h{strawberries} 2 Delete records
131 %h = () 3 Clear entire table
132 undef %h 3 Another clear operation
133 All database operations are contingent upon your access privileges.
135 If your account does not have write permission to the database,
136 hash store operations will fail despite the setting of CLOBBER.
137 AUTOCOMMIT (default 1)
139 If set to a true value, the "autocommit" option causes the database
140 driver to commit after every store statement. If set to a false
141 value, this option will not commit to the database until you
142 explicitly call the Tie::DBI commit() method.
143 The autocommit option defaults to true.
145 DEBUG (default 0)
147 When the DEBUG option is set to a non-zero value the module will
148 echo the contents of SQL statements and other debugging information
149 to standard error. Higher values of DEBUG result in more verbose
150 (and annoying) output.
151 WARN (default 1)
153 If set to a non-zero value, warns of illegal operations, such as
154 attempting to delete the value of the key column. If set to a zero
155 value, these errors will be ignored silently.
156 CASESENSITIV (default 0)
158 If set to a non-zero value, all Fieldnames are casesensitiv. Keep
159 in mind, that your database has to support casesensitiv Fields if
160 you want to use it.
161
163 The tied array represents the database table. Each entry in the hash
164 is a record, keyed on the column chosen in the tie() statement.
165 Ordinarily this will be the table's primary key, although any unique
166 column will do.
167 Fetching an individual record returns a reference to a hash of field
169 names and values. This hash reference is itself a tied object, so that
170 operations on it directly affect the database.
171 Fetching information
173 In the following examples, we will assume a database table structured
174 like this one:
175 -produce-
177 produce_id price quantity description
178 strawberries 1.20 8 Fresh Maine strawberries
180 apricots 0.85 2 Ripe Norwegian apricots
181 bananas 1.30 28 Sweet Alaskan bananas
182 kiwis 1.50 9 Juicy New York kiwi fruits
183 eggs 1.00 12 Farm-fresh Atlantic eggs
184 We tie the variable %produce to the table in this way:
186 tie %produce,'Tie::DBI',{db => 'mysql:stock',
188 table => 'produce',
189 key => 'produce_id',
190 CLOBBER => 2 # allow most updates
191 };
192 We can get the list of keys this way:
194 print join(",",keys %produce);
196 => strawberries,apricots,bananas,kiwis
197 Or get the price of eggs thusly:
199 $price = $produce{eggs}->{price};
201 print "The price of eggs = $price";
202 => The price of eggs = 1.2
203 String interpolation works as you would expect:
205 print "The price of eggs is still $produce{eggs}->{price}"
207 => The price of eggs is still 1.2
208 Various types of syntactic sugar are allowed. For example, you can
210 refer to $produce{eggs}{price} rather than $produce{eggs}->{price}.
211 Array slices are fully supported as well:
212 ($apricots,$kiwis) = @produce{apricots,kiwis};
214 print "Kiwis are $kiwis->{description};
215 => Kiwis are Juicy New York kiwi fruits
216 ($price,$description) = @{$produce{eggs}}{price,description};
218 => (2.4,'Farm-fresh Atlantic eggs')
219 If you provide the tied hash with a comma-delimited set of record
221 names, and you are not requesting an array slice, then the module does
222 something interesting. It generates a single SQL statement that
223 fetches the records from the database in a single pass (rather than the
224 multiple passes required for an array slice) and returns the result as
225 a reference to an array. For many records, this can be much faster.
226 For example:
227 $result = $produce{apricots,bananas};
229 => ARRAY(0x828a8ac)
230 ($apricots,$bananas) = @$result;
232 print "The price of apricots is $apricots->{price}";
233 => The price of apricots is 0.85
234 Field names work in much the same way:
236 ($price,$quantity) = @{$produce{apricots}{price,quantity}};
238 print "There are $quantity apricots at $price each";
239 => There are 2 apricots at 0.85 each";
240 Note that this takes advantage of a bit of Perl syntactic sugar which
242 automagically treats $h{'a','b','c'} as if the keys were packed
243 together with the $; pack character. Be careful not to fall into this
244 trap:
245 $result = $h{join( ',', 'apricots', 'bananas' )};
247 => undefined
248 What you really want is this:
250 $result = $h{join( $;, 'apricots', 'bananas' )};
252 => ARRAY(0x828a8ac)
253 Updating information
255 If CLOBBER is set to a non-zero value (and the underlying database
256 privileges allow it), you can update the database with new values. You
257 can operate on entire records at once or on individual fields within a
258 record.
259 To insert a new record or update an existing one, assign a hash
261 reference to the record. For example, you can create a new record in
262 %produce with the key "avocados" in this manner:
263 $produce{avocados} = { price => 2.00,
265 quantity => 8,
266 description => 'Choice Irish avocados' };
267 This will work with any type of hash reference, including records
269 extracted from another table or database.
270 Only keys that correspond to valid fields in the table will be
272 accepted. You will be warned if you attempt to set a field that
273 doesn't exist, but the other fields will be correctly set. Likewise,
274 you will be warned if you attempt to set the key field. These warnings
275 can be turned off by setting the WARN option to a zero value. It is
276 not currently possible to add new columns to the table. You must do
277 this manually with the appropriate SQL commands.
278 The same syntax can be used to update an existing record. The fields
280 given in the hash reference replace those in the record. Fields that
281 aren't explicitly listed in the hash retain their previous values. In
282 the following example, the price and quantity of the "kiwis" record are
283 updated, but the description remains the same:
284 $produce{kiwis} = { price=>1.25,quantity=>20 };
286 You may update existing records on a field-by-field manner in the
288 natural way:
289 $produce{eggs}{price} = 1.30;
291 $produce{eggs}{price} *= 2;
292 print "The price of eggs is now $produce{eggs}{price}";
293 => The price of eggs is now 2.6.
294 Obligingly enough, you can use this syntax to insert new records too,
296 as in $produce{mangoes}{description}="Sun-ripened Idaho mangoes".
297 However, this type of update is inefficient because a separate SQL
298 statement is generated for each field. If you need to update more than
299 one field at a time, use the record-oriented syntax shown earlier.
300 It's much more efficient because it gets the work done with a single
301 SQL command.
302 Insertions and updates may fail for any of a number of reasons, most
304 commonly:
305 1. You do not have sufficient privileges to update the database
307 2. The update would violate an integrity constraint, such as making a
308 non-nullable field null, overflowing a numeric field, storing a string
309 value in a numeric field, or violating a uniqueness constraint.
310 The module dies with an error message when it encounters an error
312 during an update. To trap these erorrs and continue processing, wrap
313 the update an eval().
314 Other functions
316 The tie object supports several useful methods. In order to call these
317 methods, you must either save the function result from the tie() call
318 (which returns the object), or call tied() on the tie variable to
319 recover the object.
320 connect(), error(), errstr()
322 These are low-level class methods. Connect() is responsible for
323 establishing the connection with the DBI database. Errstr() and
324 error() return $DBI::errstr and $DBI::error respectively. You may
325 may override these methods in subclasses if you wish. For example,
326 replace connect() with this code in order to use persistent
327 database connections in Apache modules:
328 use Apache::DBI; # somewhere in the declarations
330 sub connect {
331 my ($class,$dsn,$user,$password,$options) = @_;
332 return Apache::DBI->connect($dsn,$user,
333 $password,$options);
334 }
335 commit()
337 (tied %produce)->commit();
338 When using a database with the autocommit option turned off, values
340 that are stored into the hash will not become permanent until
341 commit() is called. Otherwise they are lost when the application
342 terminates or the hash is untied.
343 Some SQL databases don't support transactions, in which case you
345 will see a warning message if you attempt to use this function.
346 rollback()
348 (tied %produce)->rollback();
349 When using a database with the autocommit option turned off, this
351 function will roll back changes to the database to the state they
352 were in at the last commit(). This function has no effect on
353 database that don't support transactions.
354 select_where()
356 @keys=(tied %produce)->select_where('price > 1.00 and quantity < 10');
357 This executes a limited form of select statement on the tied table
359 and returns a list of records that satisfy the conditions. The
360 argument you provide should be the contents of a SQL WHERE clause,
361 minus the keyword "WHERE" and everything that ordinarily precedes
362 it. Anything that is legal in the WHERE clause is allowed,
363 including function calls, ordering specifications, and sub-selects.
364 The keys to those records that meet the specified conditions are
365 returned as an array, in the order in which the select statement
366 returned them.
367 Don't expect too much from this function. If you want to execute a
369 complex query, you're better off using the database handle (see
370 below) to make the SQL query yourself with the DBI interface.
371 dbh()
373 $dbh = (tied %produce)->dbh();
374 This returns the tied hash's underlying database handle. You can
376 use this handle to create and execute your own SQL queries.
377 CLOBBER, DEBUG, WARN
379 You can get and set the values of CLOBBER, DEBUG and WARN by
380 directly accessing the object's hash:
381 (tied %produce)->{DEBUG}++;
383 This lets you change the behavior of the tied hash on the fly, such
385 as temporarily granting your program write permission.
386 There are other variables there too, such as the name of the key
388 column and database table. Change them at your own risk!
389
391 What is the performance hit when you use this module rather than the
392 direct DBI interface? It can be significant. To measure the overhead,
393 I used a simple benchmark in which Perl parsed a 6180 word text file
394 into individual words and stored them into a database, incrementing the
395 word count with each store. The benchmark then read out the words and
396 their counts in an each() loop. The database driver was mySQL, running
397 on a 133 MHz Pentium laptop with Linux 2.0.30. I compared Tie::RDBM,
398 to DB_File, and to the same task using vanilla DBI SQL statements. The
399 results are shown below:
400 UPDATE FETCH
402 Tie::DBI 70 s 6.1 s
403 Vanilla DBI 14 s 2.0 s
404 DB_File 3 s 1.06 s
405 There is about a five-fold penalty for updates, and a three-fold
407 penalty for fetches when using this interface. Some of the penalty is
408 due to the overhead for creating sub-objects to handle individual
409 fields, and some of it is due to the inefficient way the store and
410 fetch operations are implemented. For example, using the tie
411 interface, a statement like $h{record}{field}++ requires as much as
412 four trips to the database: one to verify that the record exists, one
413 to fetch the field, and one to store the incremented field back. If
414 the record doesn't already exist, an additional statement is required
415 to perform the insertion. I have experimented with cacheing schemes to
416 reduce the number of trips to the database, but the overhead of
417 maintaining the cache is nearly equal to the performance improvement,
418 and cacheing raises a number of potential concurrency problems.
419 Clearly you would not want to use this interface for applications that
421 require a large number of updates to be processed rapidly.
422
425 The each() call produces a fatal error when used with the Sybase driver
426 to access Microsoft SQL server. This is because this server only allows
427 one query to be active at a given time. A workaround is to use keys()
428 to fetch all the keys yourself. It is not known whether real Sybase
429 databases suffer from the same problem.
430 The delete() operator will not work correctly for setting field values
432 to null with DBD::CSV or with DBD::Pg. CSV files do not have a good
433 conception of database nulls. Instead you will set the field to an
434 empty string. DBD::Pg just seems to be broken in this regard.
435
437 Lincoln Stein, lstein@cshl.org
438
440 Copyright (c) 1998, Lincoln D. Stein
441 This library is free software; you can redistribute it and/or modify it
443 under the same terms as Perl itself.
444
446 The latest version can be obtained from:
447 http://www.genome.wi.mit.edu/~lstein/Tie-DBI/
449
451 perl(1), DBI(3), Tie::RDBM(3)
452perl v5.30.0 2019-07-26 Tie::DBI(3)