Re: [SQLObject] Joins/FKs with bad IDs

[Edmund L. <el...@in...>]

Luke Opperman wrote:

> However, I would like to see a decent constraints system built into
> SQLObject/Python, rather than relying on the database. Just was I have
> obviously been pushing towards a python-side transaction system, etc. My needs
> are typically the inverse of Edmund's: flexibility to use Python exclusively
> but need to run across a variety of backends occasionally, and the fact that
> I'm not a fan of most databases' 'trigger'-based procedural constraints
> systems.

Me neither. I usually stick to extensive referential integrity 
constraints. Triggers and stored procedures are really hard to debug at 
times, and they are outside the purview of a source code control system.

I've found situations where triggers were genuinely needed though, so 
I've got a smattering of them.

> Now, it would be nice to simply augment the DB's constraint system if possible,
> since I appreciate the fact that other tools touch the DB (heck, that's where
> our bad IDs that started me talking about this came from..) Not sure how
> feasible that will be beyond adding them to the creation SQL where there's a
> conceptual match.

What we need is something that allows us to use a mixture of techniques. 
E.g., use the tool to do quick/dirty/simple stuff, but also use the tool 
to implement hard stuff that requires lots of enterprise level SQL (for 
want of a better term): multiway joins, subqueries, transactions, etc. 
OK, so I'm dreaming!  :-)

SQLObject might get there... it certainly feels different--even so early 
in its life--from the other ORMs, which fall down on support for 
complex, preexisting data-models and SQL queries.

[As an aside, I'm not quite convinced that using Python to express where 
clauses, etc. is better, since you have to work much harder to deduce 
the underlying SQL constructs when debugging. But, I'm still mulling 
this over.]


> I'm in the midst of re-reading CJ Date's "What not How",
> which applies so directly to this issue... tangentially, I'm also leaning
> towards the suggestion of supporting nested types, ala the Point example,
> although I need to think it through for myself...

Don't forget to check out http://www.pgro.uk7.net/index.htm  It has lots 
of stuff by Date, Pascal, etc. They do take an extremist, theoretic view 
of the world, but maybe more rigor is needed given how many people seem 
to equate MySQL and Access with PostgreSQL, Oracle, DB2, etc.

Is the book worth a read?

...Edmund.