Re: [Openinteract-help] suggestions

[Chris W. <ch...@cw...>]

* Jon Dugan (jd...@nc...) [020625 15:54]:
> ...
>   booth they want the fiber to come to, etc.  At any time until we
>   close registration they can come in and modify their requests.
>   This data is all kept in a database (PostgreSQL) and used by those
>   of us building the network to determine how much gear we need to
>   borrow, etc.  So, the second set of users for this site are the
>   people on the networking committee.
>
>   I have developed a database schema that handles the data that we
>   need to collect and maintain pretty well.  My first question is
>   should I try to use SPOPS as my interface to this data?  SPOPS has
>   a lot of nice things, but the database is fairly normalized and
>   SPOPS seems most suited to single table applications.  I need to
>   do a lot of JOINs (thank goodness that Pg now supports OUTER JOINs
>   -- I had to emulate them with UNIONs last year).  This leads to my
>   second question -- how do I use SPOPS to access a normalized
>   database?

SPOPS does relationships just fine, but not using joins. (Not yet,
anyway.) The types of relationships you seem to imply are of the
dependent object sort -- the related object has no meaning outside the
context of the primary object. IME (which does not include a high
traffic environment) treating these differently than primary objects
is too much work.

You can declare relationships in the SPOPS configuration and have them
automatically created for you. For instance, without writing any code
you'd be able to do something like (in the context of OI):

 my $exhibitor = $R->exhibitor->fetch( $id );
 foreach my $booth ( @{ $exhibitor->booth } ) {
    foreach my $connection ( @{ $booth->net_connection } ) {
       print "Booth $booth->{booth_name}\n",
             "Connection $connection->{location}\n";
       foreach my $gear ( @{ $connection->gear } ) {
           print "  requires: $gear->{name}\n";
       }
    }
 }

This is circuitous, forcing you to walk down all the relationships
manually. (Well, sort of manually.) Fortunately, you can create
shortcuts fairly easily. For instance, say we want to get all the gear
required for a particular booth. We could create something like the
following code, telling SPOPS to include it into the generated class:

 package OpenInteract::Booth;

 sub get_gear_required {
     my ( $self ) = @_;
     my $R = OpenInteract::Request->instance;
     my @tables = ( $self->table_name,
                    $R->net_connection->table_name,
                    $R->connection_gear->table_name,
                    $R->gear->table_name );
     my @where = (
           "booth.booth_id = ?", 
           "connection.booth_id = booth.booth_id",
           "connection_gear.connection_id = connection.connection_id",
           "gear.gear_id = connection_gear.gear_id"
     );
     return $R->gear->fetch_group({
                             from  => \@tables,
                             where => join( ' AND ', @where ),
                             value => [ $self->id ] });
 }

(Again, this is in the context of OI. Without OI we'd just have to
lookup another way or hardcode the classnames.)

It would probably be fairly straightforward to declare and create
automatically OUTER JOIN relationships as well. When I finally get
around to implementing some of the improved relationship declarations
this may be part of the package.

>   That said, I like SQL, so I don't necessarily want it hidden from
>   me.  Is using SPOPS::SQLInterface a viable option?  The man page
>   seems to gently discourage it.

It's got its own way of doing things, as you can see from the example
above. The SQL paradigm is still prevalent -- you can still do joins,
just not with straight SQL. (Actually, you *can* pass SQL to the
various methods, but what's the point?)

Whether it's viable is really up to you. All the information necessary
for the tradeoffs is in your head: after testing, is this fast enough?
Are you able to create easy workarounds for the tasks you do all the
time? Does the object representation *feel* right? It's very difficult
for me to say without more concrete examples and knowing the problem a
little better.

I'd recommend you fool around with the data model and how you're going
to use it before tackling the application server. Otherwise it's too
much to do at once :-)

Finally, there's nothing wrong with using your own data access
framework within OI. You'll still need SPOPS for all the app server
maintenance stuff -- users, groups, themes, security, etc. -- but you
can use whatever persistence framework works for you.

>   Lastly, how is OI 2 coming along?  The CVS repository on
>   Sourceforge doesn't appear to have been touched in a few weeks.

It's moving, slowly. I expect that when I get back from vacation in
the first week of July I'll be able to spend some more time on it,
getting a working alpha/beta done by the end of August. (Sometime in
there I'm moving, too :-) I can't forecast right now when the release
will finally be out, but I hope that when things get to a working
state that other folks (you know who you are!) will be able to help
out.

Chris