Relational databases and tied hashes?

[Lee Goddard]

Is there a perl module to make life easier with relational,
linked databases?

For example, three tables, where each has a column acting as
a unique identifier (*.uid). The "item" table references the
"stock" table in the item.stock column using the stock.uid column;
the "item" table references the "image" table in the item.image
column using the item.uid column.

ITEM TABLE STOCK TABLE IMAGE TABLE
item.uid stock.uid image.uid
item.name stock.quantity image.href
item.price stock.others image.others
item.stock
item.image

I'd like a hash for every record I pull out of the "item" table,
and one that doesn't just give me the "item.stock" uid that is
there, but does the look-up and give me a hash of the relevant record:

$item->{
uid => 12,
name => "My Name",
price => '',
stock => {
uid => 103,
quantity=> 1,
others => 'other stuff',
},
image => {
uid => 43,
href => 'http://foobar/baz/121231.html',
others => 'some other stuff',
},
};

Since the perl world is full of great labour saving devices, it seems
natural that this one is out there somewhere - could you please tell
me if this is so, and if so, where? I've been looking on CPAN and
either found nothing or not undertood what I have seen....

Many thanks in anticipation
Lee

(I posted this to *.moderated too, but not sure it got there, thanks to IE)

 

[Sherm Pendley]

Is there a perl module to make life easier with relational,
linked databases?

For example, three tables, where each has a column acting as
a unique identifier (*.uid). The "item" table references the
"stock" table in the item.stock column using the stock.uid column;
the "item" table references the "image" table in the item.image
column using the item.uid column.

ITEM TABLE STOCK TABLE IMAGE TABLE
item.uid stock.uid image.uid
item.name stock.quantity image.href
item.price stock.others image.others
item.stock
item.image

I'd like a hash for every record I pull out of the "item" table,
and one that doesn't just give me the "item.stock" uid that is
there, but does the look-up and give me a hash of the relevant record:

$item->{
uid => 12,
name => "My Name",
price => '',
stock => {
uid => 103,
quantity=> 1,
others => 'other stuff',
},
image => {
uid => 43,
href => 'http://foobar/baz/121231.html',
others => 'some other stuff',
},
};

If you're thinking this would take three separate SQL queries, you can
simplify things by taking advantage of SQL's ability to join multiple
tables in a single query:

select item.uid, item.name, item.price, item.stock, item.image,
stock.uid, stock.quantity, stock.others,
image.uid, image.href, image.others
from item, stock, image
where item.uid=? AND
item.stock = stock.uid AND
item.image = image.uid

That will give you "flattened" results, rather than giving you a set of
nested hashes like in your example. You could get nested hashes like this:

# Assume a previously prepare() and execute()d statement in $sth
my $row = $sth->fetchrow_hashref();
my $item = {
'uid' => $row->{'item.uid'},
'name' => $row->{'item.name'},
'price' => $row->{'item.price'},
'stock' => {
'uid' => $row->{'stock.uid'},
'quantity' => $row->{'stock.quantity'},
'others' => $row->{'stock.others'},
},
'image' => {
'uid' => $row->{'image.uid'},
'href' => $row->{'image.href'},
'others' => $row->{'image.others'},
},
};

sherm--

 

[Matija Papec]

X-Ftn-To: Lee Goddard

$item->{
uid => 12,
name => "My Name",
price => '',
stock => {
uid => 103,
quantity=> 1,
others => 'other stuff',
},
image => {
uid => 43,
href => 'http://foobar/baz/121231.html',
others => 'some other stuff',
},
};

Since the perl world is full of great labour saving devices, it seems
natural that this one is out there somewhere - could you please tell
me if this is so, and if so, where? I've been looking on CPAN and
either found nothing or not undertood what I have seen....

Check for Tie:BI, it does even table updating for you but avoid it if you
have concerns about the speed.