Weirdness when reading from DBM file

[mojodaddy]

Hello Monk-tacular ones,

I just discovered DBM files (yes, I'm that slow), and thought I'd see if I could convert a MySQL database to a DBM file and hopefully simplify my life somewhat.

I have a MySQL database like this:

+---------------+-------------+------+-----+------------+-------+
| Field         | Type        | Null | Key | Default    | Extra |
+---------------+-------------+------+-----+------------+-------+
| employee_id   | varchar(9)  |      | PRI |            |       |
| firstname     | varchar(50) |      |     |            |       |
| last_name     | varchar(50) |      |     |            |       |
| title         | varchar(10) |      |     |            |       |
| dept          | varchar(10) |      |     |            |       |
| salary        | mediumint   |      |     |            |       |
| date_hired    | date        |      |     |            |       |
+---------------+-------------+------+-----+------------+-------+

So I wrote this perfectly horrible code to get the data out of MySQL and into a DBM file:

#!/usr/bin/perl -w

use strict;

use DB_File;
use Mojo::DB qw(get_dbh); # this is just my shortcut for accessing DBI

$| = 1;

my $dbh = get_dbh();
unless @ARGV { die "No filename argument provided"; }
my $file = shift @ARGV;

unlink $file;
my %IDS;

# note this line right here:
tie %IDS, "DB_File", $file or die "Can't open $file: $!";

my @keys = 
    qw(employee_id firstname lastname title dept salary date_hired);

my $keys = join(', ', @keys);
my $query = "select $keys from employee_data";
my @values = @{$dbh->selectall_arrayref($query)};

for my $v (@values){
    my @vals = @$v; # one ID per array
    for (@vals){
        $_ = '-' unless $_; # in case of empty fields
    }
    # here's where I clumsily interpolate the column headings
    my @ary;
    for (@keys){
        push @ary, $_;
        push @ary, shift @vals; 
    }
    my @bry;
    shift @ary;    # lose the first column heading since the employee 
+IDs are going the be the keys now... I know, it's cringe-worthy...
    my $key = shift @ary;
    while (@ary){
        my $k = shift @ary;
        my $v = shift @ary;
        push @bry, { $k => $v };
    }
    $IDS{$key} = \@bry;
}

# and then because I don't trust anything unless I see it for myself:
for (keys %IDS){
    print "\n\n$_:\n";
    for my $key (@{$IDS{$_}}){
        while (my($k, $v) = each %{$key}){
            print "$k => $v\n";
        }
    }
}
untie %IDS;

When I run it, I get output like this:

12345678:
firstame => Fritz
lastname => Froemming
title => analyst
dept => research
salary => peanuts
date_hired => 2006-01-20

which, incredibly, is actually what I expected; I take it to mean that my data is in that hash somewhere, and is thus retrieveable. As opposed to most of the time, when I find that I'm storing strings like "ARRAY(0x84d90b8)" or worse, nothing at all.

The trouble starts when I want to see if I can actually read the data back from the DBM. For that I just use this code:

#!/usr/bin/perl -w

use strict;
use DB_File;

$| = 1;

unless @ARGV { die "No filename argument provided"; }
my $file = shift @ARGV;

# here's this line again:
tie my %IDS, "DB_File", $file or die "Can't open $file: $!";

# and this block is the same as I used for testing above
for (keys %IDS){
    print "\n\n$_:\n";
    for my $key (@{$IDS{$_}}){
        while (my($k, $v) = each %{$key}){
            print "$k => $v\n";
        }
    }
}

Only now I get:

12345678:
Can't use string ("ARRAY(0x84d90b8)") as an ARRAY ref while "strict re
+fs" in
        use at read_db.pl line 18 (#1)
    (F) Only hard references are allowed by "strict refs".  Symbolic
    references are disallowed.  See perlref.
    
Uncaught exception from user code:
        Can't use string ("ARRAY(0x84d90b8)") as an ARRAY ref while "s
+trict refs" in use at read_db.pl line 18.
 at read_db.pl line 18

Weird, right? Then as I'm thinking things through in prepration for posting here, I realize that it's possible that while testing, I didn't actually write to the DBM file the first time. Meaning, in the first script, I had the line tie %IDS, "DB_File", $file or die "Can't open $file: $!"; commented out. But since I was getting my nice output:

12345678:
firstame => Fritz
lastname => Froemming
title => analyst
dept => research
salary => peanuts
date_hired => 2006-01-20

I didn't think anything was amiss. So in summary, with that line uncommented I get the error about using a string as an array reference. With it commented, I get the nice output, but get the error when trying to read back from the DBM file.

Let me also say that I'm somewhat mortified to be posting actual code here, as it's no fun displaying my mediocre skills to the world; but if in addition to pointing out the source of my immediate troubles, if you see anything utterly ridiculous that I'm doing, please feel free to point that out as well. That whole business of shifting the arrays and all was a bit gnarley. I have no doubt it could be done better.

Many thanks...

[rdfield]

Have a look at MLDBM for storing stuctures in a DBM file. Basically you have to freeze/thaw the structures to store them in the DBM file and MLDBM makes it easy.

[mojodaddy]

Basically you have to freeze/thaw the structures to store them in the DBM file

(mojodaddy can't help smiling: to think he was actually trying to simplify his life... )

Seriously, though, thanks for that. I'll check it out.

[dragonchild]

Warning: Completely and utterly untested.

use DBM::Deep;

my $dbh = get_dbh();

# This is the magic. :-)
my $db = DBM::Deep->new( 'db_file.db' );

my @keys = 
    qw(employee_id firstname lastname title dept salary date_hired);
my @values = @{$dbh->selectall_arrayref($query)};

for my $v (@values){
    my @vals = @$v; # one ID per array
    for (@vals){
        $_ = '-' unless $_; # in case of empty fields
    }

    # Map is a wonderful piece of magic. Look really really hard at
    # this code before freaking out.
    $db->{$vals[0]} = {
        (map {
            $keys[$_] => $vals[$_];
        } 0 .. $#keys)
    };
}

[mojodaddy]

Just wanted to report back on my results, which were phenomenal! This script writes the MySQL database to the DBM file:

#!/usr/bin/perl -w

use strict;

use Mojo::DB;
use DBM::Deep;

$| = 1;

my $dbh = get_dbh();
die "No filename argument provided" unless @ARGV;
my $file = shift @ARGV;

unlink $file;

# This is the magic. :-)
my $db = DBM::Deep->new( $file );

tie my %IDS, 'DBM::Deep', $file or die "$!";

my @keys = qw(employee_id firstname lastname title dept salary date_hi
+red);

my $keys = join(', ', @keys);
my $query = "select $keys from employee_data";
my @values = @{$dbh->selectall_arrayref($query)};

for my $v (@values){
    my @vals = @$v; # one ID per array
    for (@vals){
        $_ = '-' unless $_;
    }
    $db->{$vals[0]} = {
        (map {
            $keys[$_] => $vals[$_];
        } 0 .. $#keys)
    };
}

and this script reads the data back out:

#!/usr/bin/perl -w

use strict;
use DBM::Deep;

$| = 1;

die "No filename argument provided" unless @ARGV;
my $file = shift @ARGV;

tie my %IDS, 'DBM::Deep', $file or die "$!";

while (my($k, $v) = each %IDS){
    print "\n$k :\n";
    while (my ($k, $v) = each %{$v}){
        print "$k => $v\n";
    }
}

It really works like a dream, thank you so much. The nice thing, too, is that I almost understand how it works! I'm even going to leave your comment about "this is the magic" in there to remind me. : )

[dragonchild]

*smiles* I'm glad it worked out for you. If you run into any problems with DBM::Deep, please let me know.

As for map, it's conceptually pretty simple. You're used to functions taking a single thing as an argument and returning a single thing as a result. Map takes an action and a list of things as arguments, does the action to each of the things in turn, and returns the list of actioned things as a result.

In the code above, what I'm doing is taking a list of indices and inflating each one into a key-value pair. Then, I'm using the fact that a hash can be assigned a list of key-value pairs to create my hash. Does that help make more sense?

[mojodaddy]

Very nice, thank you for that. I really do think of map as a kind of magic. It reminds me of this quote:

"Any technology sufficiently advanced is indistinguishable from a Perl script."

Hey, I think I just found my clever signature line. But if someone else is already using it, let me know...