[Cubicweb] creating multi column unique index in db

Alexandre Fayolle alexandre.fayolle at logilab.fr
Wed Sep 1 12:28:46 CEST 2010


Hello everyone,

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 
entity:

class Container(EntityType):
    name = String()

class Content(EntityType):
    name = String

class contained_in(RelationDefinition):
    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 :

class Content(EntityType):
   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 
containter'))])

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: 


class ContentUniqueName(UnicityConstraint):
     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 mailing list