Re: [SQLObject] Direct support for UNIQUE table constraints

[Ian B. <ia...@co...>]

David M. Cook wrote:
> http://sourceforge.net/tracker/index.php?func=detail&aid=1181657&group_id=74338&atid=540674
> 
> This is a patch to provide direct support for UNIQUE table constraints by
> marking each column in the constraint with uniqueKey=True. I stole the idea
> from hibernate (actually in hibernate you specify a constraint name, so you
> can have multiple unique constraints per table; this is just a first pass at
> the idea and a RFC). 
> 
> class Foo(SQLObject):
>     baz = StringCol(uniqueKey=True)
>     quux = StringCol(uniqueKey=True)
> 
> would produce
> 
> CREATE TABLE foo (
>     id INTEGER PRIMARY KEY,
>     quux TEXT,
>     baz TEXT,
>     UNIQUE(quux, baz)
> );

Hrm... the translation doesn't feel quite right to me.  The UNIQUE(quux, 
baz) is a general constraint that could occur multiple times with 
different combinations of columns in a table, but uniqueKey=True is 
grouping all the given columns in a single constraint.

If it's just a matter of compound keys, this goes only half the way. 
Ideally compound keys would look like:

class Foo(SQLObject):
     id = ('baz', 'quux')
     baz = StringCol()
     quux = StringCol()

Though of course that requires diving into SQLObject a fair amount to 
make it use compound IDs.  Good TDD candidate.

Simply for adding constraints, I'd think that would be best done like:

class Foo(SQLObject):
     class sqlmeta:
         contraints = [UniqueConstraint('quux', 'baz')]
     ...

-- 
Ian Bicking  /  ia...@co...  /  http://blog.ianbicking.org