I'll not argue that some really extended project got eventually messy (they all do): my point is that even with an ORM your code wouldn't be as neat as you might think. Without you providing an api that you may expect web2py can't figure out your business requirements, and surely you can't expect anyone to code something on top of your ideas without providing a consistent use-case that definitely benefits of an ORM on top of the DAL.
> Additionally, there are performance issues that a well-designed ORM can > solve - but that may have more to do with the implementation of > Units-Of-Work in SQLA more than an ORM, however I still believe that using > an ORM model, can help a lot in defining clusters of operations - that > would then be ordered and aggregated into a transaction. It may provide > orders-of-magnitude of better performance in some cases, and may be the > only way out of the N+1 problem. > > DAL does everything in one transaction (db transaction) always. If you're referring to some custom pattern as (pseudo-code) *record = Record(1) record.ordered_items = 1 .... record.ordered_items +=1 record.save()* ending up in a "update record set ordered_items = 2 where id=1" the same exact thing gets done by *record = db.record(1) record.ordered_items = 1 ..... record.ordered_items +=1 record.update_record()* by the DAL. If instead you're talking about DAL "optimizing" whatever you do to the minimum required number of queries...well, that goes very far along the way of AI, e.g. *db(db.record.id == 1).update(ordered_items=1) .... db(db.record.id == 1).update(ordered_items=2) .... db(db.record.id == 1).delete()* ending up in "delete from record where id = 1". DAL issues the queries you instruct in your code: it doesn't force you to use them. ORMs on the other hand are a little bit more "obscure" in terms of "what will happen when I choose to alter this object". ORMs can do that kind of "optimization" (up to a certain point) but please note that you're asking the code to be smarter than you. > For example, in one of the threads I read, Massimo gave an example of how > an ORM can be emulated using the DAL. This example included a db-query > inside a nested for-loop... > Now, we recently did a massive profiling of our application, and guess > where we found the bottlenecks... > This leads me to the subject of hierarchies: Sometimes, there is no way to > model a query that scans a tree of records, without resorting to this kind > of awful approach... This is a real problem. > > Anyways, I can elaborate more on my use-case if you think I should, but I > think you get the idea... > > Basically we are talking about a tree of "production-items", that relate > to a table of budget-items, but also that each node in the item-hierarchy > has it's own tree of "tasks", and each task has it's own tree of "comments" > for collaborative messaging. Additionally, each task may have a tree of > file-records representing a CMS of versions of files that are used in the > process of executing that task. then there's a status-matrix for > representing task-status's pipeline-process... > I really don't know how far I should get with this description, but you > may get the point - it's a huge tree-of-trees... The most horrible > data-model for a relational-database, and one that can benefit a lot from > an ORM. > I was thinking about moving that data into an ODB such as ZODB, but I > still want the data to be stored in the same Postgres DB, in the back-end - > so THERE I though I could use an ORM ontop of the DAL, as an adapter for a > ZODB back-end... > But that was a wild idea - I would much rather solve everything within > web2py, and there also, an ORM could really help... > The fact that your model doesn't fit a tabular storage doesn't really matter in "DAL vs ORM" ways of coding such a thing, performance-wise... you have to fetch the data either way, and that's the bottleneck. If your bottleneck is the code to fetch the data, then it will take surely less time with the DAL than with an ORM (given the head-start of having a layer less). As of choosing the right model to store hierarchies in a database, ink has been wasted on books and blood on posts around the web.... but that deserves another thread alltogether ^_^ <offtopic> Please note that I'm not saying that DAL fits the bills always: SQLA is by far the most complete "db integration on python" and, from a lot of perspective, may be one of the most mature out there also not "restricting" the perspective to python. Those are piece of code optimized over the years by loads of peoples. E.g., if I were to manage a database only with a python module, one of the major shortcomings of DAL vs SQLA are: - no indexes in models - flaky "schema" support - no full support for non integer pk - no full support for multiple-columns pk Now, if you're talking about those requirements, ok, maybe we need more features in DAL.... but those doesn't "count" for requiring an ORM on top of DAL. As far as framework goes, a little "dose" of assumptions are meant to be made to make the code less overbloated.... "requiring" every table to have an integer pk is somewhat recommended in every SQL book out there. Pun intended: I'm assuming nobody creating something has a problem with having column names without spaces, even if they are a possibility in every database engine. </offtopic> -- --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/groups/opt_out.
