From: Edmund L. <el...@in...> - 2003-06-04 03:09:19
|
I've found a couple of bugs in RelatedJoin... 1. If you're going to explicitly define a class for the mapping table, you cannot use .createTable() to create tables on either side of the join. This is because the RelatedJoin declaration forces SQLObject to try to create the mapping (intermediate) table again, and this causes an exception. 2. When the mapping table table is used to map two or more many:many relationships, the presence of null values in the mapping table causes an exception. Example test case: Schema ------ create table cat ( id serial, name text ); create table dog( id serial, name text ); create table person( id serial, name text ); create table owner_map( id serial, person_id integer, cat_id integer, dog_id integer ); Classes ------- class Dog(SQLObject): _columns = [ StringCol("name")] _joins = [ RelatedJoin("Person", intermediateTable = "owner_map", joinColumn = "dog_id", otherColumn = "person_id")] class Cat(SQLObject): _columns = [ StringCol("name")] _joins = [ RelatedJoin("Person", intermediateTable = "owner_map", joinColumn = "cat_id", otherColumn = "person_id")] class Person(SQLObject): _columns = [ StringCol("name")] _joins = [ RelatedJoin("Dog", intermediateTable = "owner_map", joinColumn = "person_id", otherColumn = "dog_id"), RelatedJoin("Cat", intermediateTable = "owner_map", joinColumn = "person_id", otherColumn = "cat_id")] class OwnerMap(SQLObject): _columns = [ IntCol("personId"), IntCol("dogId", foreignKey="Dog"), IntCol("catId", foreignKey="Cat")] How to raise the exception: >>> d = Dog.new(name="Snoopy") >>> c = Cat.new(name="Garfield") >>> p = Person.new(name="John") >>> p.addDog(d) >>> p.dogs [<Dog 2 name='Snoopy'>] >>> p.cats [Traceback (most recent call last): File "<stdin>", line 1, in ? File "/usr/lib/python2.2/site-packages/SQLObject/SQLObject.py", line 925, in __repr__ return '<%s %i %s>' \ AttributeError: 'Cat' object has no attribute 'id' >>> What's happening (I think) is that the query that SQLObject uses to find the right row in the mapping table is assuming that either no row is found, or that if a row is found, then a not null value for cat_id will exist. So the null/None value for cat_id in owner_map causes an exception. I'd fix it, but I don't understand SQLObject well enough to do so. Sorry... ...Edmund. |
From: Luke O. <lu...@me...> - 2003-06-04 05:01:21
|
Ran into a bug with (at least) ForeignKeys. Actually, the bug is when we access an attribute of an FK object that is a null entry. (Clearly, we shouldn't be doing this, but the bug is that it's not raising NotFound, but getting through to the attribute). ... groupName = category.artworkGroup.name File "<string>", line 1, in <lambda> File "/usr/lib/python2.2/site-packages/SQLObject/SQLObject.py", line 692, in _SO_getValue results = self._connection._SO_selectOne(self, [self._SO_columnDict[name].dbName]) File "/usr/lib/python2.2/site-packages/SQLObject/DBConnection.py", line 254, in _SO_selectOne return self.queryOne("SELECT %s FROM %s WHERE %s = %s" % AttributeError: 'ArtworkCategory' object has no attribute 'id' this is when category.artworkGroupID is NULL. I'll look into this further tomorrow. In my glancing today I couldn't determine why instantiating the artworkGroup object doesn't blow up, which is the expected behavior, but instead an object WITHOUT an id field is being created. Edmund's report may be related to this however, which is why i bring it up... For now, just starting a thread to talk to myself in... - Luke |
From: Ian B. <ia...@co...> - 2003-06-04 05:08:49
|
On Tue, 2003-06-03 at 23:46, Luke Opperman wrote: > Ran into a bug with (at least) ForeignKeys. Actually, the bug is when we access > an attribute of an FK object that is a null entry. (Clearly, we shouldn't be > doing this, but the bug is that it's not raising NotFound, but getting through > to the attribute). Yes, this *did* happen, for reasons to boring to go into, but it shouldn't anymore (as of a couple days ago). Is this happening with a fresh CVS checkout? Ian |
From: Edmund L. <el...@in...> - 2003-06-04 05:27:19
|
Luke Opperman wrote: > Ran into a bug with (at least) ForeignKeys. Actually, the bug is when we access > an attribute of an FK object that is a null entry. (Clearly, we shouldn't be > doing this, but the bug is that it's not raising NotFound, but getting through > to the attribute). Looks like the same, or closely related bug as mine. ...Edmund. |
From: Ian B. <ia...@co...> - 2003-06-04 05:19:38
|
On Tue, 2003-06-03 at 22:07, Edmund Lian wrote: > I've found a couple of bugs in RelatedJoin... > > 1. If you're going to explicitly define a class for the mapping table, > you cannot use .createTable() to create tables on either side of the > join. This is because the RelatedJoin declaration forces SQLObject to > try to create the mapping (intermediate) table again, and this causes an > exception. You can put do createTable(createJoinTables=False). Otherwise it should create the join table when you create the table that's alphabetically first (so both createTables don't create the shared table). Is that not working for you? > > 2. When the mapping table table is used to map two or more many:many > relationships, the presence of null values in the mapping table causes > an exception. Example test case: Why would there by a null value? Each row is a relationship, so a row with a null should just be deleted. Maybe it's because you are using it all funny like, with a triple join... hmm... well, I made a change that I think should work. I also got rid of the funny behavior you got when you accidentally instantiated an object with an id of None. Ian |
From: Edmund L. <el...@in...> - 2003-06-04 05:33:04
|
Ian Bicking wrote: > You can put do createTable(createJoinTables=False). Otherwise it should > create the join table when you create the table that's alphabetically > first (so both createTables don't create the shared table). Is that not > working for you? No, I just didn't think of doing this. I will now! > Why would there by a null value? Each row is a relationship, so a row > with a null should just be deleted. Maybe it's because you are using it > all funny like, with a triple join... hmm... well, I made a change that > I think should work. I also got rid of the funny behavior you got when > you accidentally instantiated an object with an id of None. Yes, it seems wierd the way I do it, but not really. What I'm trying to do (using my Cat/Dog/Person example) is capture the relationship between a person and all his/her animals within the same mapping table. There are a variety of legitimate reasons for doing so. When you do this, a null value in one row need not necessarily indicate invalid data. I do have a check constraint to ensure that each row has at least pair of non-null values. ...Edmund. |
From: Luke O. <lu...@me...> - 2003-06-04 05:49:31
|
> Yes, it seems wierd the way I do it, but not really. What I'm trying to > do (using my Cat/Dog/Person example) is capture the relationship between > a person and all his/her animals within the same mapping table. There > are a variety of legitimate reasons for doing so. > > When you do this, a null value in one row need not necessarily indicate > invalid data. I do have a check constraint to ensure that each row has > at least pair of non-null values. Hmm, I was just going to reply to your other post that this merged intersection table seems odd. I have a few three-way intersections in my latest project, but not where either-this-or-that-or-both is the meaning (no NULLs here). Hmm. I've proposed that joins silently pass over NULL values before, but always changed my mind because it always seemed to point to bad data for us, and we didn't want to hide that. I suppose it could become a constructor argument for the join, allowNulls(?). But I'm only convinced by the legacy possibility of your table. :) Care to elaborate on the design reasons for sticking these both in one table instead of two? - Luke |
From: Edmund L. <el...@in...> - 2003-06-04 16:57:23
|
Luke Opperman wrote: > Care to elaborate on the design reasons for sticking these both > in one table instead of two? Sure... just give me a day or so to get back to you... ...Edmund. |
From: Edmund L. <el...@in...> - 2003-06-06 16:58:00
|
Very strange. When I run the pydoc HTTP server via pydoc -p <port number>, everything looks fine. But, when I navigate to SQLObject within ~/site-packages, I can get into the SQLObject package, but trying to get into DBConnection, SQLBuilder or SQLObject raises an exception. Cache, Col, Constraints, Join, Style, and util are fine. Is anybody else noticing this? ...Edmund. |
From: Edmund L. <el...@in...> - 2003-06-09 06:22:05
|
Luke Opperman wrote: > Care to elaborate on the design reasons for sticking these both > in one table instead of two? Sorry to take so long to get back to you... I had been rummaging around looking for an example from a rather wierd organization relationship situation I came across a few months back at a government organization. After looking at it, I don't think it applies here. However, I suppose the biggest reason for wanting to keep more than one related join in a table is to allow for easy querying. By keeping all the related join mappings in a single table, it is easier to make queries on the relationships themselves. E.g., find all relationships where a Person has a simultaneous relationship with (Cat and Dog and Bird). This is mostly useful when the relationship or characteristics of the relationship are important. If each pairwise relationship was scattered across separate mapping tables, it becomes a bit more cumbersome to make queries on relationships. What's more if some attributes only apply when certain combinations of relationships are true, then maybe it make sense to store them with the mapping table. Or, maybe the data model really needs to be rewritten... But anyway, it is nice to have the option of using perverted data model. :-) ...Edmund. |
From: Nick <ni...@dd...> - 2003-06-09 13:53:29
|
Edmund Lian wrote: > However, I suppose the biggest reason for wanting to keep more than one > related join in a table is to allow for easy querying. It's not uncommon to have 3 (but far less common to have more than that) related columns in a single table. It'll happen in 4th normal form, and there *is* somewhat of a convenience factor, depending on what it is you're relating. Nick |
From: Edmund L. <el...@in...> - 2003-06-04 05:39:43
|
Ian Bicking wrote: > hmm... well, I made a change that I think should work. I also got rid of the > funny behavior you got when you accidentally instantiated an object with an id of None. Yup, you got it, thank you! ...Edmund. |