Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Perl > Perl Misc > Relational databases and tied hashes?

Reply
Thread Tools

Relational databases and tied hashes?

 
 
Lee Goddard
Guest
Posts: n/a
 
      12-14-2004
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)
 
Reply With Quote
 
 
 
 
Sherm Pendley
Guest
Posts: n/a
 
      12-14-2004
Lee Goddard wrote:

> 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--

--
Cocoa programming in Perl: http://camelbones.sourceforge.net
Hire me! My resume: http://www.dot-app.org
 
Reply With Quote
 
 
 
 
Matija Papec
Guest
Posts: n/a
 
      12-14-2004
X-Ftn-To: Lee Goddard

http://www.velocityreviews.com/forums/(E-Mail Removed) (Lee Goddard) wrote:
>$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.



--
Matija
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
36 years of relational databases Lawrence D'Oliveiro NZ Computing 26 07-25-2006 10:55 AM
Xindice VS Relational Databases Eric Frigot XML 1 12-21-2004 02:01 AM
Relational databases and tied hashes? Lee Goddard Perl Misc 2 12-17-2004 05:22 PM
Re: Any pure-python relational databases? David McNab Python 2 07-14-2003 12:56 AM
Re: Any pure-python relational databases? Thomas Weholt Python 1 07-13-2003 02:06 AM



Advertisments