On Thursday 08 January 2004 06:00, Tim Bates wrote:
> >
> > What would you like it to return? Should it construct a class on the fly,
> > with fields 'name' and 'customers'? And should it use its own query
> > language to do so, or parse the sql query?
>
> I don't know. Probably an array of arrays or an array of hashes - which,
> I know, is exactly what DBI would return for such a query. The point I'm
> trying to make is I'd like the system to be able to handle this sort of
> query _as_well_as_ the "SELECT * FROM table WHERE property = ?" type
> return-a-list-of-objects query. I don't know of any system that can do
> that, or even if it could be done neatly. Possibly such a system would
> have to accept two sorts of query and handle both separately, but that
> introduces its own ugliness.
>

forgive me for jumping in late in this thread...
I have recently been working on a persistence layer for our product.  After 
spending some time looking through the projects available on RAA I was not 
entirely satisfied with any of them - so we rolled our own that meets our 
current needs

I can do things like:

allPeopleAsAnArrayOfPersonObjects = store.get(Person)  # select * from persons
peopleOlderThan30 = store.get(Person, :age, :>, 30) # select * from persons 
where age > 30
peopleBetween30And60 = store.get(Person, :age, :>, 30, :age, :<, 60) # select 
* from persons where age > 30 and age < 30
or - store.get(Person, :age, :between?, [30, 60])
peopleWithNameStartingWithFre = store.get(Person, :name, :=~, "Fre") # using 
the =~ operator is misleading here - it currently does "LIKE '#{arg}%'" :-)
...yada yada...

The store object takes care of creating an SQL query.  While the interface is 
still pretty ugly, it sure beats embedded sql code everywhere, and is less 
verbose.

The code only does the bare minimum to satisfy my needs at the moment and is 
being actively developed, but there is capability for:
* relationships between tables (although i do make assumptions about how 
tables are related).  For example, if a Widget contains a single Part, then 
we assume a table of widgets and a table of parts - where the widget table 
has a "part-id" field When saving, we save the part, then the widget.  If a 
widget contains an array of parts then the relationship is reversed - the 
part table has a "widget-id" field, etc etc.
* returning data as arrays of hashes and/or as objects.
* arbitrary query execution using get(field, operator, value(s)) - although 
this is fairly limited at the moment.

I have avoided the issue of joining tables when running queries - we simply 
create a view which joins the tables as required and then run a query on 
that.

While what we have done is certainly not robust enough - nor generic enough 
for general purpose use, I'd be happy to post some (barely) working code if 
anybody is interested.

Cheers,
Martin

-- 
Martin Hart
Arnclan Limited
53 Union Street
Dunstable, Beds
LU6 1EX
http://www.arnclanit.com