use SDBM_File like a database

Plankton has asked for the wisdom of the Perl Monks concerning the following question:

Friends,

For reason beyond my control I must use SDBM_File ( and no other database like package ) to track "request" information. I don't seem to be catching on to how SDBM_File is suppose to work. I am trying to make a package that mimics a RDBMS. (Yes I know I am having to reinvent the wheel, but my boss is a ignorant bureaucrat impervious to logic). So I am trying to write subs like create, insert, select, update, delete ... Here's what I got so far.

#!/usr/local/bin/perl -w
use strict;
use Fcntl;   # For O_RDWR, O_CREAT, etc.
use SDBM_File;


# Name                                      Null?    Type
# ----------------------------------------- -------- -----------------
+-----------
# REQ                                       NOT NULL NUMBER
# INQFILE                                   NOT NULL VARCHAR2(200)
# SENT                                      NOT NULL DATE
# RECVD                                              DATE
# STATUS                                             NUMBER
 
sub create_table {
    my $table_name = shift;
    my %h;
    tie(%h, 'SDBM_File', $table_name, O_CREAT, 0666) 
        or die "Couldn't tie SDBM file $table_name: $!; aborting";
    untie %h;    
}


sub insert {
    my $table_name = shift;
    my $cols_vals    = shift; # ref to hash
    my %h;
    tie(%h, 'SDBM_File', $table_name, O_WRONLY , 0666) 
        or die "Couldn't tie SDBM file $table_name: $!; aborting";
    %h = %$cols_vals;;
    untie %h;    
}


sub select_all {
    my $table_name = shift;
    my %h;
    tie(%h, 'SDBM_File', $table_name, O_RDONLY  , 0666) 
        or die "Couldn't tie SDBM file $table_name: $!; aborting";
    for my $i ( keys %h ) {
        print "$i -> $h{$i}\n";
    }
}


my $table = shift;
if (!$table) { 
    print "usage: $0 <tablename>\n";
    exit 1;
}


create_table( $table );


my $rec = {
    'REQ'     => 123,                        #  NOT NULL NUMBER
    'INQFILE' => 'HAHA_THE_FILE.txt',          #  NOT NULL VARCHAR2(20
+0)
    'SENT'    => '20031202121200' ,            #  NOT NULL DATE
    'RECVD'   => undef ,                       #  DATE
    'STATUS'  => 0                            #  NUMBER
};


insert( $table, $rec );
#select_all ( $table );
$rec = {
    'REQ'     => 124,                        #  NOT NULL NUMBER
    'INQFILE' => '124.txt',          #  NOT NULL VARCHAR2(200)
    'SENT'    => '20031202121124' ,            #  NOT NULL DATE
    'RECVD'   => undef ,                       #  DATE
    'STATUS'  => 0                            #  NUMBER
};
insert( $table, $rec );
#select_all ( $table );


$rec = {
    'REQ'     => 125,                  #  NOT NULL NUMBER
    'INQFILE' => '125.txt',          #  NOT NULL VARCHAR2(200)
    'SENT'    => '20031202121125' ,  #  NOT NULL DATE
    'RECVD'   => '20030808080000' ,  #  DATE
    'STATUS'  => 1                   #  NUMBER
};
insert( $table, $rec );
select_all ( $table );
[download]

The problem I am having is that everytime I do a insert the previously inserted record get over written. I just don't understand what I am suppose to do here.

Plankton: 1% Evil, 99% Hot Gas.

Comment on use SDBM_File like a database Download Code

Replies are listed 'Best First'.
Re: use SDBM_File like a database by mpeppler (Vicar) on Dec 02, 2003 at 23:52 UTC
An SDBM_file is like a hash - it works in a key/value combination. I'm guessing that your REQ column is the primary key for your data. So for the "insert" you need to do something like this: `@cols = qw(INQFILE SENT RECVD STATS); $h{$col_vals->{REQ}} = join('\|', @$col_vals{@cols});` [download] Here I just store the data in pipe-delimitted format, which might break depending on what you have in the data. Retrieving the data is left as an exercise :-) Michael	[reply] [d/l]
Re: use SDBM_File like a database by perrin (Chancellor) on Dec 03, 2003 at 06:36 UTC
If you find using a dbm file constraining, you might look at MLDBM, which allows you to store complex Perl data structures in one, or DBD::SQLite, which gives you an embedded SQL database. Also, keep in mind that SDBM_File has a rather small limitation on the size of a record.	[reply]
Re: use SDBM_File like a database by bl0rf (Pilgrim) on Dec 02, 2003 at 23:55 UTC
Plankton, although I don't have experience with SDBM_file after inspecting your code ( and POD ) I'll make a suggestion: The SDBM_file works by having a key and a value stored in a hash - and on file. In insert() you are basically overwriting the keys REQ,INQFILE etc. with keys that have the same names but diff values. I suggest that you do this: $h{uniqval} = $req. That way your whole reference is the value and you will have some key which represents it. The main thing is that your reference should only be the value Now, the unique hash key concept is completely overkill and in this case you just need a tied array. I don't know of any modules that do this ( maybe storable/dumper) but perhaps $hash{mainkey} = \@array-of-references will work... I hope I helped! My site	[reply]
Re: use SDBM_File like a database by hiseldl (Priest) on Dec 03, 2003 at 21:20 UTC
If you can use Storable you can try the following: #!/usr/local/bin/perl -w use strict; use Fcntl; # For O_RDWR, O_CREAT, etc. use SDBM_File; use Storable qw/freeze thaw/; ... sub insert { my $table_name = shift; my $col_vals = shift; # ref to hash my %h; tie(%h, 'SDBM_File', $table_name, O_WRONLY , 0666) or die "Couldn't tie SDBM file $table_name: $!; aborting"; $h{$col_vals->{REQ}} = freeze $col_vals; untie %h; } sub select_all { my $table_name = shift; my %h; tie(%h, 'SDBM_File', $table_name, O_RDONLY , 0666) or die "Couldn't tie SDBM file $table_name: $!; aborting"; for my $i ( keys %h ) { my $row = thaw $h{$i}; print $i," -> "; while (my ($key,$val) = each %$row) { $val = 'undef' unless $val; print "\t", $key, ' = ', $val, "\n"; } } } ... [download] this uses the value of the key 'REQ' as the primary key, and then freezes the hash so it can be stored in the SDBM_File. The select_all sub uses thaw to deserialize the hash into a hash ref containing the 'row' of values. HTH! -- hiseldl What time is it? It's Camel Time!	[reply] [d/l]