Re: [SQLObject] Enums, lookup tables, and python classes...

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

Bud P.Bruegger wrote:

> I've used the above pattern a lot and very successfully so.  But I
> believe also that it is a pattern that is limited strictly to the
> relational domain--so I've been pondering how to deal with this in a
> more object-oriented way.  And here is what I've come up with so far:
> 
> I believe the "lookup tables" or whatever you call them should behave
> as Python Classes to the Application programmer.  They may of course
> still be stored in the dbms (class attributes instead of instance
> attributes...).  If this sounds interesting, I'll keep you posted on
> my progress in this..

But this is how they are behaving right now, and it seems awkward and is 
inefficient.

Contents of the lookup table should be accessible as objects for 
updates, etc. But when it is being used to constrain the values of a 
column in another table, then that column should really be a first class 
attribute.

What I think is more natural is to have two kinds of foreign key 
constraints along the lines of:

class LookUpTable(SQLObject):
     _columns = [
         StringCol("stuff", default=None),
         StringCol("name", unique=1)]

class ConstrainedTable(SQLObject):
     _columns = [
         StringCol("moreStuff", default=None),
         StringCol("constrainedStuff",
             foreignKeyAttribute="LookUpTable.name")]

class NormalTable(SQLObject):
     _columns = [
         StringCol("otherStuff", default=None),
         StringCol("objectStuff", foreignKey="LookUpTable.name")

In use:

# Add entries to LookUpTable, treating it as an object
LookUpTable.new(name="Cat")
LookUpTable.new(name="Dog")

# Add entries to NormalTable, as per current SQLObject behavior
obj = NormalTable.new(objectStuff=<instance of LookUpTable>)
print obj.objectStuff
<instance of LookUpTable>

# Add entries to ConstrainedTable, where we have specified that
# the lookup table should not be "objectified" since it is really
# just a constraint table.
obj = ConstrainedTable.new(constrainedStuff="Cat")
print obj.constraintedStuff
"Cat"

...Edmund.