> > > Shop demostrates how you can build a simple application with only a > > couple of custom SQL queries. The rest are simple joins and CRUD. If > > you need more complex queries, there are escapes. You still probably > > end up with a list of tuples for your reports. The key we have found > > is avoiding indirection by naming fields and models the same in SQL > > and Perl objects. This allows you to seamlessly switch between the > > two.
>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 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. -Fran