Re: [SQLObject] unique index creates broken sql statement?

[Oleg B. <ph...@ph...>]

On Wed, Sep 15, 2010 at 07:34:06AM -0400, ed...@la... wrote:
> I'm following the example given in the SQLObject FAQ on creating custom
> mtm relationships. Creating the unique index for the UserPhonelists table
> seems to create an incorrect SQL statement.
> 
> My code:
> ---
> class User(SQLObject):
>     name = StringCol(length=255, default="")
>     [...]
>     phonelists =
> SQLRelatedJoin('Phonelist',intermediateTable='user_phonelists',createRelatedTable=False)
> 
> class Phonelist(SQLObject):
>     name = StringCol(length=255)
>     [...]
>     users =
> SQLRelatedJoin('User',intermediateTable='user_phonelists',createRelatedTable=False)
> 
> class UserPhonelists(SQLObject):
>     class sqlmeta:
>         table = "user_phonelists"
>     user = ForeignKey('User', notNull=True, cascade=True)
>     phonelist = ForeignKey('Phonelist', notNull=True, cascade=True)
>     [...]
>     unique = index.DatabaseIndex(user, phonelist, unique=True)
> ---
> 
> The queries it generates:
> ---
>  1/Query   :  ALTER TABLE user_phonelists ADD CONSTRAINT
> user_phonelists_user_id_exists FOREIGN KEY (user_id) REFERENCES user (id)
> ON DELETE CASCADE
>  1/Query   :  ALTER TABLE user_phonelists ADD CONSTRAINT
> user_phonelists_phonelist_id_exists FOREIGN KEY (phonelist_id) REFERENCES
> phonelist (id) ON DELETE CASCADE
>  1/Query   :  ALTER TABLE user_phonelists ADD UNIQUE unique (user_id,
> phonelist_id)
> ---
> 
> The error the last query triggers:
> ---
> sqlobject.dberrors.ProgrammingError: You have an error in your SQL syntax;
> check the manual that corresponds to your MySQL server version for the
> right syntax to use near 'unique (user_id, phonelist_id)' at line 1
> ---

   You code generates the following statement for me:

CREATE UNIQUE INDEX user_phonelists_unique ON user_phonelists (user_id, phonelist_id)

   SQLObject 0.13.0, SQLite.

   And the code

u = User(name='1')
pl = Phonelist(name='1')
pl.addUser(u)
pl.addUser(u)

   correctly produces the error:

sqlobject.dberrors.DuplicateEntryError: columns user_id, phonelist_id are not unique

   What version of SQLObject and on what backend do you use it?

Oleg.
-- 
     Oleg Broytman            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.