Ian Bicking: the old part of his blog

Another plan sqlobject 07 comment 000

Well, like other people said, SQLObject can be used at a lower level than any single deployment -- SQLObject can be part of a project which may get installed anywhere. This just came up for a user: http://www.crummy.com/2005/02/18/0 -- and it will come up all the time for open source developers that distribute projects based on SQLObject.

But even then, database triggers are limited; they can operate on the database, but not otherwise. So, if your dependent object isn't a SQLObject instance then a database trigger won't accomplish anything. E.g., if you want a FileCol which only stores a filename in the database, and that file needs to be deleted along with the row. Or if you have two separate databases that have dependencies across the databases.

And even when you are only dealing with the database, database triggers still aren't sufficient, because they don't give SQLObject any feedback, so it doesn't know how to update its cache. SQLObject, for reasons tied to its very ORMness, has problems when the database gets updated behind its back. I'd like to fix some of those problems (and some of these features could help), but in this case SQLObject could still cache aggressively in the presence of in-process cascades.

Comment on Re: Another plan: SQLObject 0.7
by Ian Bicking

Comments:

Good points, Ian. I think I detect a fairly fundamental difference in worldview/emphasis/(don't say the p*gm word!) between:

  1. the application as the primary thing and the db as a dumb persistence store

versus ...

  1. the data as the primary thing and the dbms as its primary guardian; triggers (etc) as the immune system that protect it from things malicious/miguided applications might try to get up to.

It's a design decision about at what layer in the stack you want to have your basic data integrity defence mechanisms, with different valid answers in different circumstances. Although what you seem to be saying/implying is that SQLObject won't be able to cope if you put them anywhere other than in the app's persistence layer. I would expect that (with all due respect) to be very significantly slower and less reliable than native triggers in any mature production rdbms - especially if the app isn't running on the same machine. And even if you can live with that, what if you aren't the only application - or the only instance of the same application - that's talking to the same database?

So you would appear to be limited to one instance of one application that "owns" the database entirely. Which of course is perfectly adequate in many circumstances, but severely limits what you can do if you want to load balance your app / run with existing production databases that already have integrity protections in place / etc.

# Alan Little

Martin Fowler called one style an Application Database (what SQLObject prefers) vs. an Integration Database (where the database is authoritative and the communication medium for multiple applications).

I should really write a more thorough summary of why it's hard to be reasonably efficient with an ORM while also being compatible with an Integration Database style. Not impossible -- it's just a matter of what your expectations are. Hmm... I've never properly written up the performance issues of an ORM; I should do that so I can refer to it at times like these...

# Ian Bicking