[Cubicweb] creating multi column unique index in db
alexandre.fayolle at logilab.fr
Wed Sep 1 12:28:46 CEST 2010
I need to create multicolumn unique index in the db backend for an instance.
See https://www.logilab.net/cwo/ticket/959523 for the ticket itself.
The typical need is to ensure uniqueness of an attribute within a container
name = String()
name = String
subject = 'Content'
object = 'Container'
inlined = True
composite = 'object'
cardinality = '1*'
I need e.g. that the name of the content is unique within a container. So far
I've been using :
name = String(constraints=[RQLUniqueConstraint('S name N, S contained_in C,
Y name N, Y contained_in C','Y', msg=_('This name is already used in the
But this is not very efficient and will not work for long as I'm soon going to
deploy several instances sharing a single database, and the implementation for
constraints does not support that setting.
Possible solutions include :
* hacking a bit cubicweb.server to add an IPC lock on the constraints
checking, but this is not something I would like to see as it could easily
transform in deadlocking and will not improve the perfs
* add a SQL-level unique index on cw_Content(cw_name, cw_contained_in).
The second solution is both clean and efficient, and will work since the
relation is inlined. My main concern is how can I get CW/yams to do this for
me. It's easy to add sql() statements in a migration script, but ideally, such
things would be specified in the schema, and handled by migrations. I'm not
sure how I would like to see this specified in the schema.
A possible specification could be:
entity = 'Content'
attributes = ['name', 'contained_in']
What do you think about this, folks?
Alexandre Fayolle LOGILAB, Paris (France)
Formations Python, CubicWeb, Debian : http://www.logilab.fr/formations
Développement logiciel sur mesure : http://www.logilab.fr/services
Informatique scientifique: http://www.logilab.fr/science
More information about the Cubicweb