Thread: [SQLObject] unique index creates broken sql statement?
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: <ed...@la...> - 2010-09-15 11:34:15
|
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 --- Shouldn't the generated statement be: --- ALTER TABLE user_phonelists ADD UNIQUE (user_id, phonelist_id); --- Regards, Ed |
From: Oleg B. <ph...@ph...> - 2010-09-15 11:49:47
|
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. |
From: Oleg B. <ph...@ph...> - 2010-09-15 12:02:24
|
On Wed, Sep 15, 2010 at 03:49:37PM +0400, Oleg Broytman wrote: > On Wed, Sep 15, 2010 at 07:34:06AM -0400, ed...@la... wrote: > > 1/Query : ALTER TABLE user_phonelists ADD UNIQUE unique (user_id, > > phonelist_id) > > What version of SQLObject and on what backend do you use it? Aha, I see - it's MySQL. Well, the syntax is ALTER TABLE tname ADD UNIQUE [index_name] (index_col_name,...) The problem is the name of the index - 'unique'. Try to rename it: user_phonelist_unique = index.DatabaseIndex(user, phonelist, unique=True) Oleg. -- Oleg Broytman http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |