On 02/04/10 13:43 +0200, Chenal Bertrand wrote:
> Le 02/04/10 13:27, Cédric Krier a écrit :
> > On 02/04/10 13:20 +0200, Chenal Bertrand wrote:
> >
> >> Le 02/04/10 11:10, Cédric Krier a écrit :
> >>
> >>> Hi,
> >>>
> >>> I would like to bring some attention on the issue1422[1]. I think we
> >>> should
> >>> try to fix this before release 1.6.0.
> >>> There is a possible solution that will work only for PostgreSQL:
> >>>
> >>> CREATE UNIQUE INDEX "index" ON "table" ("name", COALESCE("parent",
> >>> 0));
> >>>
> >>> Is there anybody who has better solution?
> >>>
> >>>
> >>> [1] http://bugs.tryton.org/roundup/issue1422
> >>>
> >>>
> >> For me it's a good solution, but what about Mysql ?
> >> (for Sqlite it's already handled in the python code).
> >>
> > SQLite doesn't work because NULL != NULL (for PostgreSQL, SQLite and MySQL).
> > So it will not work for SQLite nor MySQL because they don't allow to put
> > expressions in the definition of an index (only column names).
> >
> >
> OK, but aren't we talking about a this:
>
> self._constraints += [('unique_parent_name', 'UNIQUE(parent, name)',
> 'msg' )]"
>
> in the python code ?
>
> And it generates
>
> ALTER TABLE "table" ADD CONTRAINT "unique_parent_name" unique(parent,
> name)
>
> that implicitly creates the index.
>
> This case is correctly handled with sqlite because constraint are
> enforced by an extra query in _validate method in modelsql.py and this
> extra query support the coalesce trick.
> We can not change the _constraint because expressions are not allowed. So the trick for PostgreSQL is to change the index created to enforce the UNIQUE constraint. And the queries in _validate doesn't work for multi-transactions. -- Cédric Krier B2CK SPRL Rue de Rotterdam, 4 4000 Liège Belgium Tel: +32 472 54 46 59 Email/Jabber: [email protected] Website: http://www.b2ck.com/
pgprmblRkfug8.pgp
Description: PGP signature
