Tie::RDBM(3pm)

📦
version 1.08 · 5 versions
, in ubuntu noble, ubuntu resolute, ubuntu stonking

💖 Tie::RDBM 💖

okay bestie, welcome to Tie::RDBM — straight out of the User Contributed Perl Documentation section of the manual (yeah, the 3pm shelf, you know the one 👑). this is the module that makes your plain old Perl hashes live inside a relational database. like, actually live there. rent-free. slay.

✨ NAME ✨

Tie::RDBM — it ties hashes to relational databases. that's it. that's the whole vibe. 💅

✨ SYNOPSIS ✨

here's the whole thing in action, no cap:

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

look at that. complex data structures. objects. in a database. iconic behaviour fr fr. 🦋

✨ DESCRIPTION ✨

so here's the tea, bestie: this module lets you tie Perl associative arrays — hashes, the loves of our lives — to SQL databases using the DBI interface. the tied hash gets associated with a table in a local or networked database. one field of that table becomes the hash key, and another becomes the value. and once it's tied? all your standard hash operations just work. iteration over keys and values? works. everything works. periodt. ✨

and if you have the Storable module installed, you can store arbitrarily complex Perl structures — objects included, yes ma'am — right into the hash and pull them back out later. when you pair that with a network-accessible database, it's genuinely a simple way to yeet data structures between Perl programs running on two totally different machines. main character networking energy. 💖

🌸 TIEING A DATABASE 🌸

tie %VARIABLE,Tie::RDBM,DSN [,\%OPTIONS]

to tie a variable to a database you hand over the variable name, the tie interface (always "Tie::RDBM", no substitutions), the data source name, and optionally a hash ref full of options that get passed down to the module and the underlying database driver.

the data source can be a valid DBI-style data source string shaped like "dbi:driver:database_name[:other information]", or a database handle you already opened earlier. go read the docs for DBI and your DBD driver for the deets. and because the leading "dbi" is always present in a data source, Tie::RDBM will just add it for you automatically. considerate king. 👑

the options array is a big pile of option/value pairs. if you don't provide them, defaults get assumed. here's every single one:

  • user [''] — the account name for database authentication, if you need it. default is an empty string, meaning no authentication necessary. we love a no-login moment. 🙈
  • password [''] — the password for database authentication, if necessary. default is an empty string (again: no authentication necessary).
  • db [''] — the data source, in case you didn't pass it in the argument. this unlocks an alternate calling style:
tie(%h,Tie::RDBM,{db=>'dbi:mysql:test',create=>1};
  • table ['pdata'] — the name of the table where all your hash key/value pairs are gonna be stored.
  • key ['pkey'] — the name of the column where the hash key lives. if you don't provide one, it defaults to pkey.
  • value ['pvalue'] — the name of the column where the hash value lives. no value provided? defaults to pvalue. yes the default is literally called that. it ate.
  • frozen ['pfrozen'] — the name of the column that stores the boolean info telling us whether a complex data structure has been "frozen" using Storable's freeze() function. defaults to pfrozen if you don't say otherwise.
💀 NOTE: if this field isn't present in the database table, or if the database can't store binary structures, then Storable features get disabled. no complex data for you. sad.
  • create [0] — set it to a true value and the module is allowed to create the database table if it doesn't already exist. it emits a CREATE TABLE command and gives the key, value and frozen fields the data types most appropriate for that database driver (pulled from a lookup table kept in a package global — see DATATYPES below). whether the table creation actually succeeds depends on whether you have table create access on the database. that's on you bestie. default is not to create a table. and heads up: tie() will fail with a fatal error. 😭
  • drop [0] — so, if the indicated database table exists but doesn't have the required key and value fields, Tie::RDBM can try to add the required fields. how does it do that, you ask? by the drastic expedient of DROPPING the table entirely and creating a brand new empty one. 💀 yeah. if drop is true, it performs this radical restructuring. otherwise tie() fails with a fatal error. also "drop" implies "create". this option defaults to false.
a future version of Tie::RDBM may implement a less radical restructuring method; differences in DBI drivers and database capabilities make this task harder than it would seem. so no promises.
  • autocommit [1] — true value means the "autocommit" option makes the database driver commit after every single store statement. false value means it won't commit to the database until you explicitly call the Tie::RDBM commit() method yourself. the autocommit option defaults to true.
  • DEBUG [0] — set "DEBUG" to a true value and the module will echo the contents of SQL statements and other debugging information to standard error. it's giving surveillance but make it helpful. 🔥

🎀 USING THE TIED ARRAY 🎀

the standard fetch, store, keys(), values() and each() functions all work as expected on the tied array. and in addition to that, these methods are available on the underlying object — which you get with the standard tie() operator. obsessed. 🌈

commit()

(tied %h)->commit();

when you're using a database with the autocommit option turned off, values stored into the hash won't become permanent until commit() is called. otherwise? they are lost when the application terminates or the hash gets untied. gone. poof. 💀

some SQL databases don't support transactions, in which case you'll see a warning message if you try to use this function. not my fault, not your fault, it's the database's fault.

rollback()

(tied %h)->rollback();

when you're using a database with the autocommit option turned off, this function rolls back changes to the database to the state they were in at the last commit(). undoing your mistakes, we love a redemption arc. ✨

this function has no effect on databases that don't support transactions. soz.

🌟 DATABASES AND DATATYPES 🌟

okay so here's the classic struggle: Perl is a weakly typed language. databases are strongly typed. 💅 when you're translating from databases to Perl there's inevitably some data type conversion you have to worry about. the author tried to keep the details as transparent as possible without sacrificing power, and this section is where the tradeoffs get discussed.

if you want to tie a hash to a preexisting database, you specify the database name, the table inside the database, and the fields you wanna use for keys and values. those fields can be any type you choose, but the data type limits what can be stored there. for example: if the key field is of type "int", then any numeric value is a valid key, but trying to use a string as a key results in a run time error. and if a key or value is too long to fit into the data field, it gets truncated silently. no warning, no drama, just gone. 🙈

for performance reasons, the key field should be a primary key, or at the very least an indexed field. it should also be unique. if a key shows up more than once in a table, an attempt to fetch it returns the first record found by the SQL select statement. first come, first served, iykyk.

if you want to store Perl references in the database, the module needs an extra field where it can stash a flag saying whether the data value is a simple or a complex type. this "frozen" field is treated as a boolean value. a "tinyint" data type is recommended, but string types work as well. flexible queen. 👑

in a future version of this module, the "frozen" field may be turned into a general "datatype" field in order to minimize storage. for future compatibility, please use an integer for the frozen field. do it for future-you.

if you use the "create" and/or "drop" options, the module will automatically try to create a table for its own use in the database when a suitable one isn't found. it uses the info defined in the package variable %Tie::RDBM::Types to figure out what kind of data types to create. that variable is indexed by database driver, and each index holds a four-element array telling it what data type to use for each of the key, value and frozen fields — plus whether the database can support binary types. since the author only had access to a limited number of databases, the table is currently short. and here it is, faithfully, in all its glory:

  • mysq — key field varchar(127), value field longblob, frozen field tinyint, binary support: 1
  • mSQL — key field char(255), value field char(255), frozen field int, binary support: 0
  • Sybase — key field varchar(255), value field varbinary(255), frozen field tinyint, binary support: 1
  • default — key field varchar(255), value field varbinary(255), frozen field tinyint, binary support: 1

the "default" entry gets used for any driver that isn't specifically mentioned. everybody else, this is your lane. 💜

and you are free to add your own entries to this table, or make corrections! please send the author e-mail with any revisions you make so that the wisdom can be shared. community effort bestie, we love to see it. 🫶

p.s. this page is long so it got split — part 2 is coming right up, don't go anywhere. 🎀

💾 STORABLE CAVEATS 💾

okay bestie, this is the part where we get real about vibes and limitations 🫶

So here's the thing: Storable packs Perl structures into a binary format, right? Which means only the databases that support a varbinary or blob type can even handle complex datatypes. Like, if your DB doesn't speak blob, it simply cannot sit with us 💅

And it gets messier. Some databases have strict limitations on the size of these structures. Example: SyBase and MS SQL Server have a varbinary type that maxes out at 255 bytes. For structures bigger than that, those databases give you an image type where storage gets allocated in 2K chunks! 😭 And worse — access to this image type uses a non-standard SQL extension that DBI just does not support. It's giving incompatible, fr.

Databases that don't support binary fields cannot use the Storable feature at all. If you try to store a reference to a complex data type in one of these databases, it will be converted into strings like "HASH(0x8222cf4)" — literally the same cursed outcome you'd get if you tried the same trick with a conventional tied DBM hash. And if the database supports binary fields of restricted length, large structures may be silently truncated. Caveat emptor, bestie. You've been warned 👑

Also super important — you gotta realize the limitations of the Storable mechanism itself. You can store and retrieve entire data structures, but you can't twiddle with individual substructures and expect them to persist when the process exits. Nah. To update a data structure you have to fetch it from the hash, make the desired modifications, then store it back into the hash. Like the example below shows:

Process #1:

tie %h,'Tie::RDBM','mysql:Employees:host.somewhere.com',
                {table=>'employee',user=>'fred',password=>'xyzzy'};
$h{'Anne'} = { office=>'999 Infinity Drive, Rm 203',
               age    =>  29,
               salary =>  32100 };
$h{'Mark'} = { office=>'000 Iteration Circle, Rm -123',
               age    =>  32,
               salary =>  35000 };

Process #2:

tie %i,'Tie::RDBM','mysql:Employees:host.somewhere.com',
                {table=>'employee',user=>'george',
                 password=>'kumquat2'};
foreach (keys %i) {
   $info = $i{$_};
   if ($info->{age} > 30) {
      # Give the oldies a $1000 raise
      $info->{salary} += 1000;
      $i{$_} = $info;
   }
}

And this example is also lowkey a flex, because it demonstrates how two Perl scripts running on different machines can use Tie::RDBM to share complex data structures (in this case, the employee record) without resorting to sockets, remote procedure calls, shared memory, or other gadgets ✨ iconic behavior honestly.

⚡ PERFORMANCE ⚡

So like… what's the performance hit when you use this module? 👀

It can be significant. Let me tell you about the benchmark I used: Perl parsed a 6180 word text file into individual words and stored them into a database, incrementing the word count with each store. Then the benchmark read out the words and their counts in an each() loop. The database driver was mySQL, running on a 133 MHz Pentium laptop with Linux 2.0.30. I compared Tie::RDBM, to DB_File, and to the same task using vanilla DBI SQL statements. Results right here:

                     STORE       EACH() LOOP
         Tie::RDBM     28 s        2.7  s
         Vanilla DBI   15 s        2.0  s
         DB_File        3 s        1.08 s

So during stores, there's an approximately 2X penalty compared to straight DBI, and a 15X penalty over using DB_File databases 😬 For the each() loop (which is dominated by reads), the performance is 2-3 times worse than DB_File and much worse than a vanilla SQL statement. I have not investigated the bottlenecks. It is what it is ngl 🙈

🌸 TO DO LIST 🌸

bestie the author has receipts on what's still unfinished:

  • Store strings, numbers and data structures in separate fields for space and performance efficiency.
  • Expand data types table to other database engines.
  • Catch internal changes to data structures and write them into database automatically.

💀 BUGS 💀

Yes.

(that's it. that's the whole section. no notes. periodt.)

👑 AUTHOR 👑

Lincoln Stein, lstein@w3.org

Copyright (c) 1998, Lincoln D. Stein

This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself.

🦋 AVAILABILITY 🦋

The latest version can be obtained from:

http://www.genome.wi.mit.edu/~lstein/Tie-DBM/

🌈 SEE ALSO 🌈

and that's a wrap on Tie::RDBM, bestie 💖 this whole thing is perl v5.36.0, dated 2022-10-13, and yeah — it's Tie::RDBM(3pm). go forth and tie responsibly, mwah 🫶✨

📦 where this page comes from