On Wednesday 27 June 2007, Mike Orr wrote:
> 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.
If it could, yes. Right now it looks to me like SA automatically rolls every
select back without a way to tell it not to (and no, explicit transaction
didn't do the trick, but then I'm not an expert on SA, so there may be a
better way)
> > > 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.
Interoperability has gotten better? A bit probably, but not all that much :-(
On the other hand, if you're as likely to change your DB model as you are to
modify your application, your app and db model are either very simple, or the
db model wasn't designed all that well. My current application has 513 tables
and been in production for about 5 years. The application running on top of
it was changed numerous times, the DB model had only 3 changes (all minor,
like adding a field to a table) over the years and the stored
procedures/triggers/views actually never changed. In my case there are third
party applications accessing the database (i.e. someone made fancy reports
with Crystal Reports), so changing the DB structure is a major pain with a
lot of paperwork where changing the application is not.
I'd also like to argue that everyone in the open-source field would certainly
love to see companies adopt open source over proprietary software. One of the
factors for that decision is whether the requirements for large scale
deployments are fulfilled. I'd love to see Bank of America run their
homebanking on something open-source, but I doubt they do - one of the
reasons being that their requirements aren't met by a lot of frameworks out
there.
Actually I'd also argue the "convenient" point. How is a ORM convenient?
Having a query with say 20 joins leads to a "pythonic" but pretty much
unreadable, much less comprehensible query definition. Everyone dealing with
SQL databases should be able to read SQL, but a query object definition that
doesn't even fit on 2 pages anymore is far from readable. So yes, there are
reasons why one would use ORM's and there are some against ORM's - a split SA
has actually mastered fairly well since you don't have to use the mapper
part.
> 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.
You'd still have to consider all the incompatibilities between databases.
PostgreSQL has far more data types, some of which are not handled by SA (i.e.
arrays, which again are not SQL standard but very useful). Also be aware that
SA doesn't handle postgresql user types correctly (particularly with
autodetect), so be prepared to patch SA when you use the geographical
functions. In the end, the moment you NEED to switch databases because of
features missing in the current one, you're already on the way to lock
yourself into one database (i.e. what if you want to move your application to
DB2? Does DB2 have geographical functions?).
> 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.
In the end, whether you use features like views, procedures, triggers etc. or
not, is just a question of whether it makes sense for the application. Since
Pylons and TG are frameworks for pretty much any application, they just
should support such features without having to patch core components.
Uwe
--
Open Source Solutions 4U, LLC 1618 Kelly St
Phone: +1 707 568 3056 Santa Rosa, CA 95401
Cell: +1 650 302 2405 United States
Fax: +1 707 568 6416
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"pylons-discuss" group.
To post to this group, send email to pylons-discuss@googlegroups.com
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
-~----------~----~----~----~------~----~------~--~---
