Velocity Reviews

Velocity Reviews (http://www.velocityreviews.com/forums/index.php)
-   Perl Misc (http://www.velocityreviews.com/forums/f67-perl-misc.html)
-   -   Relational databases and tied hashes? (http://www.velocityreviews.com/forums/t889651-relational-databases-and-tied-hashes.html)

Lee Goddard 12-14-2004 03:26 PM

Relational databases and tied hashes?
 
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 12-14-2004 04:26 PM

Re: Relational databases and tied hashes?
 
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

Matija Papec 12-14-2004 04:46 PM

Re: Relational databases and tied hashes?
 
X-Ftn-To: Lee Goddard

leegee@gmail.com (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::DBI, it does even table updating for you but avoid it if you
have concerns about the speed.



--
Matija


All times are GMT. The time now is 10:00 PM.

Powered by vBulletin®. Copyright ©2000 - 2014, vBulletin Solutions, Inc.
SEO by vBSEO ©2010, Crawlability, Inc.