From: Martin Hart Date: 2004-01-16T19:04:10+09:00 Subject: Re: Database applications and OOness 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