From: Ryan Pavlik <rpav@...>
Date: 2004-01-08T05:55:23+09:00
Subject: Re: Database applications and OOness

On Wed, 7 Jan 2004 21:12:06 +0900
Tim Bates <tim@bates.id.au> wrote:

> On Wed, Jan 07, 2004 at 08:16:40PM +0900, gabriele renzi wrote:
> > have you ever looked at the Criteria library ?
> 
> Yes, in fact I wrote a DBI module for it. It's great; if I were
> going to write an OO DBMS interface I'd use it, or at least some of
> the code and ideas from it. But it doesn't solve the whole problem -
> only the querying bit, and even then it can't quite manage queries
> as complex as the example I gave with in the OP. It still only
> returns (at best) arrays of data, and does nothing about turning
> them into first-class objects.

I just noticed a thread discussing Criteria in passing. ;-)  Looking
at the original post, I don't think the query is that hard to
generate:

    require 'criteria/sql'
    include Criteria

    s = SQLTable.new("salesperson")
    t = SQLTable.new("transactions")

    q          = s.*.left_outer_join(s.id == t.salesperson, s)
    q.order_by = :customers
    q.order    = :desc

    q.select(s.name, "COUNT(DISTINCT #{t.customer_id})")

    # => SELECT salesperson.name, COUNT(DISTINCT
    # transactions.customer_id) FROM salesperson LEFT OUTER JOIN
    # salesperson ON (salesperson.id = transactions.salesperson) ORDER
    # BY customers desc

Granted, it doesn't generater column headings, and you currently have
to use a literal string hack to get the SQL function.  This should
hopefully be fixed before long.

> I don't know if there is a solution to my problem, whether what I
> want can be done cleanly. To tell the truth I'm not 100% certain of
> what I want, but I visualise something like a cross between Vapor
> and Criteria.  If no such thing exists, and I can't find an
> alternate solution I guess I'm back to writing it myself, or
> settling for some other method. It just seems like there should be a
> good way to marry the power of SQL (and the optimised
> searching/indexing and concurrency routines a good RDBMS provides)
> with the pristine Object-Orientedness of Ruby.

I'm not sure what Vapor is, but---and I know you've mentioned it so
you're aware of it---you may take a look at Mephle, since I think it
does what you want:

    class Foo
        # When you call these, they automatically update the proper
        # row in the table.
        attr_accessor_indexed String, :bar, :baz
        attr_accessor_indexed Integer, :quux

        :
    end

Things are a little less automatic than I'd like at the moment.
Writing this has given me an idea of how to fix it, though, with an
'auto_index' function, so you'd have something like:

    class Foo
        IDX = DBITable.new(...)

        def initialize(...)
           :

           auto_index(IDX)
        end
    end

This could look at the column titles (or a list of what
attr_accessor_indexed declares) and insert a row with the proper
data.  (Right now you have to do a manual insertion, which isn't too
tough either.)

There are a few reason I still serialize and index instead of using
tables exclusively:

    1.  Some objects are large, and you don't want to index everything
    2.  Looking up attributes every time is slow
    3.  Not all ruby things can be represented as SQL things
   (4.  SQL isn't guaranteed to be the storage mechanism, either)

I'm open to suggestion though.  It's conceivable that there's a
"CachedTableObject" class or something where you give up the ability
to write into a live system, and you avoid point 2, for objects that
work.  Of course, you still need to be able to load that object later,
and using a unique OID for every object in the system is nice, so
you'd still have something in the object table.  I'm not sure what you
gain by this one other than a few extra bytes, but it could be done.

Making Mephle indexing less manual is probably a lot more useful---you
get what you want without any work (and there's not really much as it
is).

-- 
Ryan Pavlik <rpav@mephle.com>

"Spinal hazards *are* hazardous..." - 8BT