On Fri, 14 Jun 2002, Fran Fabrizio wrote: > >How well does this approach work with 90 tables? How does it handle > >arbitrary queries that may join 1-6 tables, with conditionals and sorting > >of arbitrary complexity? > > I'd have to agree, most of the real-world scenarios I have run across do > not consist of these neat 1-to-1 field to attribute type of mappings where > the majority of the queries are simple inserts or selects from a single > table or simple joins. Most of the apps I work on quickly evolve to the > point of many (in my current case, 120+) tables and where most of the > queries are gathering data for reports/views that bring data together from > several tables. Joins of 5+ tables, unions, excepts, subselects, > self-joins, complex sorting/grouping rules, etc.... are all commonplace and > this is where I think overzealous attempts to abstract the queries away > fall apart. I guess I'm more of the Perrin school of thought, where I > prefer crafting all the SQL directly.
I never said I like to actually write SQL directly. See below ... > I don't typically find apps that just mirror some subset of one of the db > tables to be all that interesting. After all, it's the relationships > between the data that make the data interesting, and by necessity this > means your queries will be complex. And looking over the stuff outlined at > poop.sourceforge.net, there's not too many models/frameworks out there that > can accurately abstract ALL of SQL, and if it only does a subset, I feel > like that's shooting myself in the foot. Well, I'm the author of both that doc _and_ Alzabo, which of the R-O tools described in that document supports the most of SQL except for DBIx::RecordSet, which is quite a bit lower-level, but basically can be used for _any_ SQL (I think). Alzabo supports enough for me and my 90-table database. So far I haven't needed unions, excepts, or difference, nor have I needed subselects (Alzabo actually has some support for subselects, though not the first three). I do need complex joins, self-joins, joins with aliases, complex sorting & grouping (though it doesn't support HAVING yet), outer joins, and all conditionals. Alzabo does support all of this, and frankly I find it easier to do this, particularly when it needs to be done on the fly, then I could by just generating SQL in the app itself. I'm all for _some_ abstraction. The eternal question is how much? -dave /*================== www.urth.org we await the New Sun ==================*/