Relational databases and tied hashes?

Discussion in 'Perl Misc' started by Lee Goddard, Dec 14, 2004.

  1. Lee Goddard

    Lee Goddard Guest

    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)
    Lee Goddard, Dec 14, 2004
    #1
    1. Advertising

  2. 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
    Sherm Pendley, Dec 14, 2004
    #2
    1. Advertising

  3. Lee Goddard

    Matija Papec Guest

    X-Ftn-To: Lee Goddard

    (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
    Matija Papec, Dec 14, 2004
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Eric Frigot

    Xindice VS Relational Databases

    Eric Frigot, Dec 20, 2004, in forum: XML
    Replies:
    1
    Views:
    452
    Jim Kennedy
    Dec 21, 2004
  2. Tom

    tied multi dimensional hashes

    Tom, Sep 17, 2003, in forum: Perl Misc
    Replies:
    1
    Views:
    71
    Bob Walton
    Sep 18, 2003
  3. Lee Goddard

    Relational databases and tied hashes?

    Lee Goddard, Dec 15, 2004, in forum: Perl Misc
    Replies:
    2
    Views:
    81
    Matija Papec
    Dec 17, 2004
  4. Tim O'Donovan

    Hash of hashes, of hashes, of arrays of hashes

    Tim O'Donovan, Oct 27, 2005, in forum: Perl Misc
    Replies:
    5
    Views:
    211
  5. PerlFAQ Server
    Replies:
    0
    Views:
    92
    PerlFAQ Server
    Mar 27, 2011
Loading...

Share This Page