Thread: [SQLObject] Joins/FKs with bad IDs
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: Luke O. <lu...@me...> - 2003-05-17 22:38:57
|
Hi - Interface question for everyone to ponder: If you have a foreignKey column, or a join, and have an object with an invalid ID in one of these slots, it will raise an SQLObjectNotFound exception when you try to access any of these attributes. Which is cool and all, except that this means that many attribute accesses need to be try/excepted, instead of just the explicit creation of objects. For instance, the contrived simple access: # should be try/excepted anyways, and we do ob = Person(12) # these don't seem reasonable to do so v = ob.roles w = ob.phoneNumber should be: try: ob = Person(12) except SQLObjectNotFound: fail() try: v = ob.roles except SQLObjectNotFound: v = [] try: w = ob.phoneNumber except SQLObjectNotFound: w = None Now, I'm not entirely convinced on the solution. Sometimes you want to know that you've got bad data, other times you just want to be able to go on, blindly ignoring the bad (so having SQLObject do the exception checking in those cases would be cool). (Alternatively, as I'm sure Edmund will tell me, my database should ensure integrity at all times. And he's probably right, but this still concerns me. :) So. Should SQLObject provide an option to transparently pass over bad relationships? Perhaps at the class level, perhaps at the instance level (creation argument or anytime flag?), or should this be rethought into a stronger enforcement of relationships throughout? (We've occasionally been having full interpreter hangs on these accesses, but I haven't tracked it down, may be an odd Webware or Postgres interaction. It is completely tangential to this idea, but if anyone else is experiencing hangs instead of exceptions when accessing invalid join or FK attrs, I'd love to hear about it to help track this down.) Thanks for your thoughts, - Luke |
From: Luke O. <lu...@me...> - 2003-05-19 05:34:23
|
As a short partial answer to myself: We really only encounter this when we have testing/temporary databases, as I agree strongly with Edmund that it is the constraints that give a database meaning. 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. I also think (don't know yet) that having python-side knowledge of the constraints of the system will make dealing with these errors in an intelligent manner 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. 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... (I think my mind is slowly trying to figure out how to make SQLObject into an implementation of Date's 'real' relational database / 'D' from Third Manifesto, although i'm not at all sure yet. Reading 'What not How' again is tying into a discussion I've been having on #python about static typing as a way of declaring invariants about a system... that's what constraints are too... must slow down the thoughts. :) - Luke |
From: Edmund L. <el...@in...> - 2003-05-19 06:21:56
|
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. |
From: Luke O. <lu...@me...> - 2003-05-19 06:39:50
|
> [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 am also still weighing this. The clumsiness needed for the AND() and OR() problems, and that there really aren't a lot of 'hard' clauses I've had that are easier to manage in python instead of SQL make me avoid it. I think it may be more helpful/relevant/(necessary?) when SQLObject supports more abstracted toSQL/fromSQL actions for columns, but for now sqlEncode or whatever it's called is just as easily used to make an SQL string manually that any developer can read. > > 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? yep, I try to read dbdebunk.com every once in a while, although it's become a lot of repititious Pascal whining about normalization than the stuff that really interests me (although I do take it as a matter of fact that you have to have a really freaking big proven reason to denormalize in any database I have say over. This gets to a point Date makes occasionally about most SQL products' base tables being equal to implementation, when they ought to be as abstract from implementation (conceptually at least) as views are from base tables. Anyways... Yes, I think the book is worth a read, although it's expensive and short, and if you already know Date's views then half the book is a refresher. Also, definitely could have used some more structural editing (a lot of "Oh and this is relevant, but I'll talk about it in these other two chapters so forget I said it.") I found Third Manifesto to be much more worthwhile, and it covers most of the constraint arguments here. The one value I've really gotten out of What Not How is that it's so short, and introduces concepts like relvar vs relation as sidenotes, that it's a very handy book to pass off to co-workers etc to make a point. Most people won't read Third Manifesto, or care, but this is very conversational and short. :) - Luke |
From: Bud P. B. <bu...@si...> - 2003-05-19 12:29:13
|
On Mon, 19 May 2003 02:21:42 -0400 Edmund Lian <el...@in...> wrote: > 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! :-) Maybe one way of doing this would be to allow a (what I call) ClassMap to use custom SQL instead of one generated automatically. That way, almost anything could be encapsulated as an object. Don't know how to deal with relationships this way, though.. --b /----------------------------------------------------------------- | Bud P. Bruegger, Ph.D. | Sistema (www.sistema.it) | Via U. Bassi, 54 | 58100 Grosseto, Italy | +39-0564-411682 (voice and fax) \----------------------------------------------------------------- |