On 6/27/07, Uwe C. Schroeder <[EMAIL PROTECTED]> wrote: > > if you > > really want COMMIT for every SELECT, i'd favor it being enabled via an > > option passed to create_engine(). > > Not every select, every transaction that didn't roll back. > I just think the default of rollback on every transaction is wrong - a > rollback should occur when there is a problem, not when the transaction was > fine. But that may just be me.
But the framework doesn't know whether there's a problem, or whether some uncommitted changes exist (maybe you called a function that changed the database but didn't commit). It certainly doesn't know whether it "should" commit or roll back these changes if they do exist. But it DOES know that stray uncommitted changes should not leak into the next web request or all hell will break loose. So it has to commit or roll back. Rolling back is the prudent thing because if the function really wanted to save those changes permanently, well, it should've committed them. > > Beyond that I think the "model > > implemented as stored procedure" style of development is much in the > > minority these days, particularly within the "lightweight/open-source" > > development community. just that the stored procedure changes the > > semantics of SELECT to be a "write" operation reminds me of the > > RESTful sin of using GET to post data. > > Probably because a lot of people can't figure out how to use stored procedures > and triggers, since the "lightweight/open-source" programming is often done > on a database that has very limited support for both :-) > > Personally I'm not a big fan of handling database integrity outside the > database. Take a simple portal as example, where every new user who signs up > gets a couple of mailboxes by default (inbox, outbox,drafts,trash). I could > handle that in the db structure itself, but that would make the db model > overly complicated for such a simple thing. The proper way is to handle that > in a trigger, or I can handle it in a stored procedure that just makes sure > the mailboxes are created. In my case I chose a stored procedure because it's > actually a select on the user's profile, but the stored procedure also does > certain sanity checks and inserts/updates stuff according to its findings. > > Handling the same in the application code is IMHO the least desirable > solution, simply because I can test the trigger/stored procedure once and I > know everything works until I change the database structure. Application code > is meddled around with much more, so the chance of introducing a bug is > higher on that end. > You could argue that using SA with stored procedures defeats the "portability" > approach. I agree that you can't easily move a Oracle or PostgreSQL or DB2 > based application with stored procedures to a different database, but how > many people do that? If you build a generic application the argument is > valid, but if you build something for a specific purpose, the database will > never be changed for the life of the application (because generally "special > purpose" applications are funded by someone and those people usually don't > invest another couple grand just to make it work with some other database). I'd argue the opposite, that people don't use stored procedures and triggers because that ties them to a certain database, and that's the opposite of why they chose SQLAlchemy in the first place. Not only that, you have to learn a second language that's limited to that database, with its own quirks and limitations, and the language is much less convenient/featureful than Python (it looks like a fossil from 20 years ago), which is why we're using Python in the first place. I would be more likely to change my database than change my application, or at least just as likely. It's not that big a deal to copy everything into a new database and adjust the program for it -- provided you don't have huge databases or stored procedures. The "huge database" part is outside your control, but the stored procedure part is. Tying myself to one database product gives me bad memories of the "vendor lock-in" problems that used to be more prevelant in the past, but are thankfully becoming rarer now that interoperability has gotten better. I actually have a practical example of this. I may have to switch one application from MySQL to PostgreSQL in order to access its PostGIS geographical functions, which I hear are more advanced than MySQL's. So if we decide we need this functionality we'll have to switch. Without stored procedures I can just change my DBURI string and voila, just write the SQL expressions. With stored procedures I'd have to port all those to the new database. Having said all this, I have had to break down and start using views because it made performance acceptable in one case (a mapped object on a SELECT was just too slow). Someday I may have to write stored procedures for that reason. But so far I haven't needed to. -- Mike Orr <[EMAIL PROTECTED]> --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "pylons-discuss" 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/pylons-discuss?hl=en -~----------~----~----~----~------~----~------~--~---
