in reply to Re: Use a hashref as a key in another hashref?
in thread Use a hashref as a key in another hashref?

Or 'Relational' like this perhaps
#!perl use strict; use warnings; use DBI; #create db my $dbfile = 'database.sqlite'; unlink($dbfile); my $dbh = DBI->connect('dbi:SQLite:dbname='.$dbfile , undef , undef , {RaiseError =>1, AutoCommit =>0}) or die $DBI::errstr; $dbh->do('CREATE TABLE STATS (ID integer, DTIME time, PRIMARY KEY (ID))'); $dbh->do('CREATE TABLE SALES (ID integer, PRODUCT, VOLUME integer, PRIMARY KEY (ID,PRODUCT))'); $dbh->do('CREATE TABLE PRODUCT (PRODUCT, SECTOR, PRIMARY KEY (PRODUCT))'); $dbh->commit; # load data my $sth = $dbh->prepare('INSERT INTO STATS VALUES (?,?)'); while (<DATA>){ chomp; last if $_ eq 'PRODUCT'; my ($id,@f) = split ','; my $dt = sprintf "%04d-%02d-%02d %02d:%02d:00",@f[2,1,0,3,4]; $sth->execute($id,$dt) if ($id); print "STATS INSERT $id,$dt\n"; } $dbh->commit; $sth = $dbh->prepare('INSERT INTO PRODUCT VALUES (?,?)'); while (<DATA>){ chomp; last if $_ eq 'SALES'; my (@f) = split ','; $sth->execute(@f) if (@f==2 ); print "PRODUCT INSERT @f\n"; } $dbh->commit; $sth = $dbh->prepare('INSERT INTO SALES VALUES (?,?,?)'); while (<DATA>){ chomp; my (@f) = split ','; $sth->execute(@f) if (@f==3); print "SALES INSERT @f\n"; } $dbh->commit; # report Sales in Sector By Year print "\n"; my $sql = "SELECT strftime('%Y',DTIME) as year,SECTOR,SUM(VOLUME) AS t +otal FROM SALES AS sa LEFT JOIN STATS AS st ON sa.ID = st.ID LEFT JOIN PRODUCT AS pr ON sa.PRODUCT = pr.PRODUCT GROUP BY year,SECTOR ORDER BY total"; my $ar = $dbh->selectall_arrayref($sql); print "REPORT\n"; printf "%4s %-10s %6s\n",qw(Year Sector Volume); printf "---- ---------- ------\n"; for (@$ar){ printf "%4d %-10s %6d\n",@$_; } __DATA__ 1,1,1,2000,4,44 2,1,1,2000,4,45 PRODUCT burgers, food fries, food sodas, drink SALES 1,burgers,5 1,fries,3 1,sodas,11 2,burgers,2 2,fries,4 2,sodas,7

Produces

REPORT Year Sector Volume ---- ---------- ------ 2000 food 14 2000 drink 18
poj