Beefy Boxes and Bandwidth Generously Provided by pair Networks
Think about Loose Coupling

Set-Theoretical Database Interface

by awwaiid (Friar)
on Feb 19, 2004 at 09:21 UTC ( #330181=CUFP: print w/replies, xml ) Need Help??

So here is something I've been toying with a bit. I've never quite been satisfied as a programmer of databases... when wearing my database designer hat I get to think in terms of relational set theory. When I put on my programmer hat I get to think in terms of rows. No fun at all. I decided that it would be Cool to let my programmer-hat-person get a better view of the database. This is a little snippet from my work-in-progress toward this end.

Other DB interfaces, such as Class::DBI, Class::Tables, or DBIx::Recordset, do not quite feel right to me, in case you were wondering.

The schema here has three tables: person, has_book, and book. The person table holds people and the book table holds books and the has_book table holds pointers to a person and a book. You'll note that the code does not mention the has_book table at all -- that is because the path from person to book through has_book is inferred from the schema.

use strict; use DBI; use SetDB; # Must turn off no strict subs so I can use magical barewords # This is certainly not required for using this module no strict 'subs'; # Okay, first we set up our DBI connection my $dbi = DBI->connect('DBI:mysql:database=setdb_test;host=localhost', 'setdb', 'setdb'); # Now we set up our schema (see website for the file) use vars qw( $schema ); do ''; # This creates our SetDB database my $db = new SetDB($dbi, $schema); # Lets get a set of people and their books my $people = $db->newSet(person, [book]); # Now loop through and print out each person while(my $person = $people->fetchNext()) { print "Name: $person->{name}\n"; # This is the set of books which that person has my $books = $person->{book}; # Lets go ahead and print all of their books while(my $book = $books->fetchNext()) { print " Book: $book->{title}\n"; # Demonstrate updates by adding ' sucks!' to the book's title $book->{title} .= ' sucks!'; } }

This looks like the following when run (two executions to demonstrate the database update -- oh, and Joe has no books, BTW):

awwaiid@feline:~/projects/perl/setdb$ ./ Name: Bob Book: How to Take Over the World! Book: When I Went Shopping Name: Joe Name: Frank Book: How to Take Over the World! sucks! Book: How to build a turtle Book: Turtle Soup awwaiid@feline:~/projects/perl/setdb$ ./ Name: Bob Book: How to Take Over the World! sucks! sucks! Book: When I Went Shopping sucks! Name: Joe Name: Frank Book: How to Take Over the World! sucks! sucks! sucks! Book: How to build a turtle sucks! Book: Turtle Soup sucks!

The code to make this happen includes such strange things as an object who's blessed thingie is a tied hash back to itself so that it can detect changes to its own hash elements... something which would not work out so well (or not be quite so possible) in just about any other language I can think of, thats for sure!

So what do you think? Cool Use for Perl? There is a lot to be done here and a lot more ideas for this project which I will eventually implement. Please tell me your ideas!

See for full code and eventual updates.

UPDATE: Fixed some code a bit.

Replies are listed 'Best First'.
Re: Set-Theoretical Database Interface
by jZed (Prior) on Feb 19, 2004 at 17:51 UTC
    You might want to look at Tie::DBI (by Lincoln Stein) or AnyData (by me). AnyData is a tied-hash interface to DBI (it underlies DBD::AnyData but has a separate user interface). Here's some sample AnyData code:
    # create a table # $table = adTie( 'CSV','my_db.csv','o', {col_names=>'name,country,sex'} ); # insert a row # $table->{Sue} = {country=>'de',sex=>'f'}; # delete a single row # delete $table->{Tom}; # select a single value # $str = $table->{Sue}->{country}; # loop through table # while ( my $row = each %$table ) { print $row->{name} if $row->{sex} eq 'f'; } # select multiple rows # $rows = $table->{{age=>'> 25'}} # update multiple rows # $table->{{country=>'Nz'}}={country=>'nz'};
    I really recommend that you write to to get ideas from Tim and other DBI/DBD authors.

      Thanks for the example, AnyData is super-cool. Looks like you have similar ideas and your module can do similar things as mine. I think I'll take your advice and get on to dbi-dev to see if I can get even more ideas and pre-existing solutions.

      Though I will look at AnyData in detail later, does AnyData have an idea of joins? As TheOrb pointed out, right now my code is all joins and very little filtering -- but my next round of improvements will be addressing that issue. Looks like you have filtering worked out already.

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: CUFP [id://330181]
Approved by Corion
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others having an uproarious good time at the Monastery: (5)
As of 2021-09-17 07:03 GMT
Find Nodes?
    Voting Booth?

    No recent polls found