mdipierro wrote: > On Apr 25, 10:48 am, Dan F <[email protected]> wrote: > [...] > >> Does the web2py DAL allow batch updates etc. to occur without recourse >> to actual SQL statements? i.e., updating all columns in the database >> without making a call per record? If not, functionality like this gives >> SQLAlchemy perhaps another benefit. >> > > If I understand the statement: yes we can. for example say I have a > table "mytable" with a field1 and a field2 and I want to update every > record that has field1==0 and set its value to the value of field1+2 > and the value of field3 to 0. > > db(db.mytable.field1==0).update(field1=db.mytable.field2+2, field3=0) > > You can have complex conditions and update more columns in the same > statement. > Very cool. This syntax looks nice. > <snip> > > Personally I believe that designing a database schema that does not > have a autoincrement ID is a mistake and this is why web2py enforces > it. I do not call it a mistake by any means, and I'm not even talking about the debate between natural vs. surrogate keys here. Autoincrementing IDs are fine and great. Usually preferable. There are however times where it makes very good sense from a db designer's point of view to combine more than one of these autoincrement ids. The common example is a linking table in a many-to-many. I consider the most sensible PK for a linking table to be the combination of the PK's of the tables that are linked. Otherwise you're left to define yet another column along with the index that needs to be put on what would otherwise be indexed as PK.
Another less common but probably more illustrative scenario is the "Diamond" relationship, where there's a parent, multiple child tables, and then a table which references specific combinations of the children: Parent - parent_id PK LeftChild - leftchild_id PK - parent_id PK,FK RightChild - rightchild_id PK - parent_id PK,FK ChildCombo - childcombo_id PK - rightchild_id FK - leftchild_id FK - parent_id FK In this scenario, the ChildCombo must reference a left and right child which share the same parent. My compound FK can do this since the two child tables have taken on the parent's PK as part of their own. Now I'll grant that possibly a majority developers aren't going to care enough to design it out this way. They're happy to allow the "same parent" constraint to fall by the wayside in order to simplify the design. But I think it's best to let the database care for this if you can, and I certainly wouldn't call doing so a mistake. But that for me is the bigger difference between SQL Alchemy and the simpler ORMs...not so much the freedom, but the philosophy of SQL Alchemy that wishes to let the database play its strengths, even if it means less of an abstraction. The thing about abstractions of course, is that they end up playing to the "lowest common denominator" of all implementations. > This buys us a lot of other goodies that other ORMs do not have. > For example automatic forms and the ability to assign uuids to records > for import/export/merge of databases (web2py can export an entire > database in Oracle and reimport it on MSSQL or Google App Engine > without breaking references even if the autoincrement IDs of the > source and destination may be different). > Here's the great part about the web2py abstraction in my opinion. It would be very cool to take an app that runs off a database and just drop it into AppEngine. No way that's going to work with SQL Alchemy. The philosophy of SA is such that it just wouldn't be done. > Anyway, I am not comfortable with talking about web2py on this list. > As I said I very much like TG and its developers. But if you ask me > questions I feel compelled to answer. > I actually appreciate your talking about web2py on this list. I wouldn't have gotten to know so much about it otherwise, and I am the better for it. Plus it helps to show what TurboGears is about when this comparison is made. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "TurboGears" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/turbogears?hl=en -~----------~----~----~----~------~----~------~--~---
