Re: [SQLObject] How to store attributes of relationships?

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

I did an experiment this morning to see if the mapping table could be 
treated as a first class table. It can, so I don't think SQLObject needs 
to be modified. Here's what I did:

class Person(SQLObject):
     _columns = [
         StringCol("firstName"),
         StringCol("middleInitial", default=None),
         StringCol("lastName")]
     _joins = [RelatedJoin("Role")]

class PersonRole(SQLObject):
     _columns = [
         IntCol("personId"),
         IntCol("roleId"),
         StringCol("color"),
         StringCol("weight")]

class Role(SQLObject):
     _columns = [
         StringCol("name")]
     _joins = [RelatedJoin("Person")]


Rather than rely on auto schema generation, I just created the mapping 
table myself, and included an id column as well as test attribute 
columns. With this, the many-to-many mapping via .addRole, .addPerson 
etc. works. And, you can create a PersonRole object directly and 
manipulate its attributes as:

pr = PersonRole.new(...)
pr.color = "Blah"


There is one thing though--without specifying a unique (person_id, 
role_id) constraint, it is possible to insert duplicate mappings into 
the table. Adding the constraint stops the redundent entry, but causes 
SQLObj to raise an exception (because psycopg raises an exception). I 
think the exception should be caught and silently ignored.


...Edmund.