unique_together integration in CW (alf+syt)
* added in-DB representation of the constraint with a new entity type (CWUniqueTogetherConstraint)
* the schema is now migrated on a sync_schema_props_perms(EType) call in the migration script
* removed the migration commands provided in the first implementation
--- a/__pkginfo__.py Fri Sep 10 15:09:31 2010 +0200
+++ b/__pkginfo__.py Fri Sep 10 16:48:58 2010 +0200
@@ -22,7 +22,7 @@
modname = distname = "cubicweb"
-numversion = (3, 9, 5)
+numversion = (3, 9, 6)
version = '.'.join(str(num) for num in numversion)
description = "a repository of entities / relations for knowledge management"
--- a/hooks/syncschema.py Fri Sep 10 15:09:31 2010 +0200
+++ b/hooks/syncschema.py Fri Sep 10 16:48:58 2010 +0200
@@ -705,6 +705,45 @@
syssource.update_rdef_unique(session, rdef)
self.unique_changed = True
+class CWUniqueTogetherConstraintAddOp(MemSchemaOperation):
+ entity = None # make pylint happy
+ def precommit_event(self):
+ session = self.session
+ prefix = SQL_PREFIX
+ table = '%s%s' % (prefix, self.entity.constraint_of[0].name)
+ cols = ['%s%s' % (prefix, r.rtype.name)
+ for r in self.entity.relations]
+ dbhelper= session.pool.source('system').dbhelper
+ sql = dbhelper.sql_create_multicol_unique_index(table, cols)
+ session.system_sql(sql)
+
+ # XXX revertprecommit_event
+
+ def postcommit_event(self):
+ eschema = self.session.vreg.schema.schema_by_eid(self.entity.constraint_of[0].eid)
+ attrs = [r.rtype.name for r in self.entity.relations]
+ eschema._unique_together.append(attrs)
+
+class CWUniqueTogetherConstraintDelOp(MemSchemaOperation):
+ entity = oldcstr = None # for pylint
+ cols = [] # for pylint
+ def precommit_event(self):
+ session = self.session
+ prefix = SQL_PREFIX
+ table = '%s%s' % (prefix, self.entity.type)
+ dbhelper= session.pool.source('system').dbhelper
+ cols = ['%s%s' % (prefix, c) for c in self.cols]
+ sql = dbhelper.sql_drop_multicol_unique_index(table, cols)
+ session.system_sql(sql)
+
+ # XXX revertprecommit_event
+
+ def postcommit_event(self):
+ eschema = self.session.vreg.schema.schema_by_eid(self.entity.eid)
+ cols = set(self.cols)
+ unique_together = [ut for ut in eschema._unique_together
+ if set(ut) != cols]
+ eschema._unique_together = unique_together
# operations for in-memory schema synchronization #############################
@@ -1051,17 +1090,19 @@
class AfterAddConstrainedByHook(SyncSchemaHook):
- __regid__ = 'syncdelconstrainedby'
+ __regid__ = 'syncaddconstrainedby'
__select__ = SyncSchemaHook.__select__ & hook.match_rtype('constrained_by')
events = ('after_add_relation',)
def __call__(self):
if self._cw.added_in_transaction(self.eidfrom):
+ # used by get_constraints() which is called in CWAttributeAddOp
self._cw.transaction_data.setdefault(self.eidfrom, []).append(self.eidto)
-class BeforeDeleteConstrainedByHook(AfterAddConstrainedByHook):
+class BeforeDeleteConstrainedByHook(SyncSchemaHook):
__regid__ = 'syncdelconstrainedby'
+ __select__ = SyncSchemaHook.__select__ & hook.match_rtype('constrained_by')
events = ('before_delete_relation',)
def __call__(self):
@@ -1077,6 +1118,32 @@
else:
CWConstraintDelOp(self._cw, rdef=rdef, oldcstr=cstr)
+# unique_together constraints
+# XXX: use setoperations and before_add_relation here (on constraint_of and relations)
+class AfterAddCWUniqueTogetherConstraintHook(SyncSchemaHook):
+ __regid__ = 'syncadd_cwuniquetogether_constraint'
+ __select__ = SyncSchemaHook.__select__ & is_instance('CWUniqueTogetherConstraint')
+ events = ('after_add_entity', 'after_update_entity')
+
+ def __call__(self):
+ CWUniqueTogetherConstraintAddOp(self._cw, entity=self.entity)
+
+
+class BeforeDeleteConstraintOfHook(SyncSchemaHook):
+ __regid__ = 'syncdelconstraintof'
+ __select__ = SyncSchemaHook.__select__ & hook.match_rtype('constraint_of')
+ events = ('before_delete_relation',)
+
+ def __call__(self):
+ if self._cw.deleted_in_transaction(self.eidto):
+ return
+ schema = self._cw.vreg.schema
+ cstr = self._cw.entity_from_eid(self.eidfrom)
+ entity = schema.schema_by_eid(self.eidto)
+ cols = [r.rtype.name
+ for r in cstr.relations]
+ CWUniqueTogetherConstraintDelOp(self._cw, entity=entity, oldcstr=cstr, cols=cols)
+
# permissions synchronization hooks ############################################
--- a/misc/migration/bootstrapmigration_repository.py Fri Sep 10 15:09:31 2010 +0200
+++ b/misc/migration/bootstrapmigration_repository.py Fri Sep 10 16:48:58 2010 +0200
@@ -35,6 +35,9 @@
ss.execschemarql(rql, rdef, ss.rdef2rql(rdef, CSTRMAP, groupmap=None))
commit(ask_confirm=False)
+if applcubicwebversion < (3, 9, 6) and cubicwebversion >= (3, 9, 6):
+ add_entity_type('CWUniqueTogetherConstraint')
+
if applcubicwebversion == (3, 6, 0) and cubicwebversion >= (3, 6, 0):
CSTRMAP = dict(rql('Any T, X WHERE X is CWConstraintType, X name T',
ask_confirm=False))
--- a/schemas/bootstrap.py Fri Sep 10 15:09:31 2010 +0200
+++ b/schemas/bootstrap.py Fri Sep 10 16:48:58 2010 +0200
@@ -154,6 +154,17 @@
value = String(description=_('depends on the constraint type'))
+class CWUniqueTogetherConstraint(EntityType):
+ """defines a sql-level multicolumn unique index"""
+ __permissions__ = PUB_SYSTEM_ENTITY_PERMS
+ constraint_of = SubjectRelation('CWEType', cardinality='1*', composite='object',
+ inlined=True)
+ relations = SubjectRelation(('CWAttribute', 'CWRelation'), cardinality='+*',
+ constraints=[RQLConstraint(
+ 'O from_entity X, S constraint_of X, O relation_type T, '
+ 'T final TRUE OR (T final FALSE AND T inlined TRUE)')])
+
+
class CWConstraintType(EntityType):
"""define a schema constraint type"""
__permissions__ = PUB_SYSTEM_ENTITY_PERMS
--- a/server/migractions.py Fri Sep 10 15:09:31 2010 +0200
+++ b/server/migractions.py Fri Sep 10 16:48:58 2010 +0200
@@ -477,6 +477,7 @@
* description
* internationalizable, fulltextindexed, indexed, meta
* relations from/to this entity
+ * __unique_together__
* permissions if `syncperms`
"""
etype = str(etype)
@@ -524,6 +525,44 @@
continue
self._synchronize_rdef_schema(subj, rschema, obj,
syncprops=syncprops, syncperms=syncperms)
+ if syncprops: # need to process __unique_together__ after rdefs were processed
+ repo_unique_together = set([frozenset(ut)
+ for ut in repoeschema._unique_together])
+ unique_together = set([frozenset(ut)
+ for ut in eschema._unique_together])
+ for ut in repo_unique_together - unique_together:
+ restrictions = ', '.join(['C relations R%(i)d, '
+ 'R%(i)d relation_type T%(i)d, '
+ 'R%(i)d from_entity X, '
+ 'T%(i)d name %%(T%(i)d)s' % {'i': i,
+ 'col':col}
+ for (i, col) in enumerate(ut)])
+ substs = {'etype': etype}
+ for i, col in enumerate(ut):
+ substs['T%d'%i] = col
+ self.rqlexec('DELETE CWUniqueTogetherConstraint C '
+ 'WHERE C constraint_of E, '
+ ' E name %%(etype)s,'
+ ' %s' % restrictions,
+ substs)
+ for ut in unique_together - repo_unique_together:
+ relations = ', '.join(['C relations R%d' % i
+ for (i, col) in enumerate(ut)])
+ restrictions = ', '.join(['R%(i)d relation_type T%(i)d, '
+ 'R%(i)d from_entity E, '
+ 'T%(i)d name %%(T%(i)d)s' % {'i': i,
+ 'col':col}
+ for (i, col) in enumerate(ut)])
+ substs = {'etype': etype}
+ for i, col in enumerate(ut):
+ substs['T%d'%i] = col
+ self.rqlexec('INSERT CWUniqueTogetherConstraint C:'
+ ' C constraint_of E, '
+ ' %s '
+ 'WHERE '
+ ' E name %%(etype)s,'
+ ' %s' % (relations, restrictions),
+ substs)
def _synchronize_rdef_schema(self, subjtype, rtype, objtype,
syncperms=True, syncprops=True):
@@ -1166,34 +1205,6 @@
if commit:
self.commit()
- def cmd_add_unique_together_attrs(self, etype, attrlist, commit=True):
- """
- Add a (sql) UNIQUE index on all the underlying columns for the
- attributes listed in attrlist. That list can also contain
- inlined relations.
- """
- prefix = SQL_PREFIX
- dbhelper = self.repo.system_source.dbhelper
- cols = ['%s%s' % (prefix, col) for col in attrlist]
- table = '%s%s' % (prefix, etype)
- sql = dbhelper.sql_create_multicol_unique_index(table, cols)
- self.sqlexec(sql, ask_confirm=False)
- if commit:
- self.commit()
-
- def cmd_drop_unique_together_attrs(self, etype, attrlist, commit=True):
- """
- remove a UNIQUE index created with add_unique_together_attrs
- """
- prefix = SQL_PREFIX
- dbhelper = self.repo.system_source.dbhelper
- cols = ['%s%s' % (prefix, col) for col in attrlist]
- table = '%s%s' % (prefix, etype)
- sql = dbhelper.sql_drop_multicol_unique_index(table, cols)
- self.sqlexec(sql, ask_confirm=False)
- if commit:
- self.commit()
-
@deprecated('[3.2] use sync_schema_props_perms(ertype, syncprops=False)')
def cmd_synchronize_permissions(self, ertype, commit=True):
self.cmd_sync_schema_props_perms(ertype, syncprops=False, commit=commit)
--- a/server/schemaserial.py Fri Sep 10 15:09:31 2010 +0200
+++ b/server/schemaserial.py Fri Sep 10 16:48:58 2010 +0200
@@ -191,6 +191,7 @@
# e.g. if the relation type is marked as beeing symmetric)
rdefs = schema.add_relation_def(rdef)
if rdefs is not None:
+ ertidx[rdefeid] = rdefs
set_perms(rdefs, permsidx)
for values in session.execute(
@@ -219,6 +220,24 @@
continue
if rdefs is not None:
set_perms(rdefs, permsidx)
+ unique_togethers = {}
+ try:
+ rset = session.execute(
+ 'Any X,E,R WHERE '
+ 'X is CWUniqueTogetherConstraint, '
+ 'X constraint_of E, X relations R', build_descr=False)
+ except Exception:
+ import traceback
+ traceback.print_exc()
+ session.rollback() # first migration introducing CWUniqueTogetherConstraint
+ else:
+ for values in rset:
+ uniquecstreid, eeid, releid = values
+ eschema = schema.schema_by_eid(eeid)
+ relations = unique_togethers.setdefault(uniquecstreid, (eschema, []))
+ relations[1].append(ertidx[releid].rtype.type)
+ for eschema, unique_together in unique_togethers.itervalues():
+ eschema._unique_together.append(tuple(sorted(unique_together)))
schema.infer_specialization_rules()
session.commit()
schema.reading_from_database = False
@@ -332,6 +351,10 @@
rdef2rql(rdef, cstrtypemap, groupmap))
if pb is not None:
pb.update()
+ # serialize unique_together constraints
+ for eschema in eschemas:
+ for unique_together in eschema._unique_together:
+ execschemarql(execute, eschema, [uniquetogether2rql(eschema, unique_together)])
for rql, kwargs in specialize2rql(schema):
execute(rql, kwargs, build_descr=False)
if pb is not None:
@@ -389,6 +412,31 @@
values = {'x': eschema.eid, 'et': specialized_type.eid}
yield 'SET X specializes ET WHERE X eid %(x)s, ET eid %(et)s', values
+def uniquetogether2rql(eschema, unique_together):
+ relations = []
+ restrictions = []
+ substs = {}
+ for i, name in enumerate(unique_together):
+ rschema = eschema.rdef(name)
+ var = 'R%d' % i
+ rtype = 'T%d' % i
+ substs[rtype] = rschema.rtype.type
+ relations.append('C relations %s' % var)
+ restrictions.append('%(var)s from_entity X, '
+ '%(var)s relation_type %(rtype)s, '
+ '%(rtype)s name %%(%(rtype)s)s' \
+ % {'var': var,
+ 'rtype':rtype})
+ relations = ', '.join(relations)
+ restrictions = ', '.join(restrictions)
+ rql = ('INSERT CWUniqueTogetherConstraint C: '
+ ' C constraint_of X, %s '
+ 'WHERE '
+ ' X eid %%(x)s, %s' )
+
+ return rql % (relations, restrictions), substs
+
+
def _ervalues(erschema):
try:
type_ = unicode(erschema.type)
--- a/server/test/data/migratedapp/schema.py Fri Sep 10 15:09:31 2010 +0200
+++ b/server/test/data/migratedapp/schema.py Fri Sep 10 16:48:58 2010 +0200
@@ -101,6 +101,7 @@
class Personne(EntityType):
+ __unique_together__ = [('nom', 'prenom', 'datenaiss')]
nom = String(fulltextindexed=True, required=True, maxsize=64)
prenom = String(fulltextindexed=True, maxsize=64)
civility = String(maxsize=1, default='M', fulltextindexed=True)
@@ -126,7 +127,6 @@
'delete': ('managers', 'owners'),
'add': ('managers', 'users',)
}
-
nom = String(maxsize=64, fulltextindexed=True)
web = String(maxsize=128)
tel = Int()
--- a/server/test/data/schema.py Fri Sep 10 15:09:31 2010 +0200
+++ b/server/test/data/schema.py Fri Sep 10 16:48:58 2010 +0200
@@ -97,6 +97,7 @@
todo_by = SubjectRelation('CWUser')
class Personne(EntityType):
+ __unique_together__ = [('nom', 'prenom', 'inline2')]
nom = String(fulltextindexed=True, required=True, maxsize=64)
prenom = String(fulltextindexed=True, maxsize=64)
sexe = String(maxsize=1, default='M', fulltextindexed=True)
--- a/server/test/unittest_migractions.py Fri Sep 10 15:09:31 2010 +0200
+++ b/server/test/unittest_migractions.py Fri Sep 10 16:48:58 2010 +0200
@@ -309,6 +309,7 @@
migrschema['titre'].rdefs[('Personne', 'String')].description = 'title for this person'
delete_concerne_rqlexpr = self._rrqlexpr_rset('delete', 'concerne')
add_concerne_rqlexpr = self._rrqlexpr_rset('add', 'concerne')
+
self.mh.cmd_sync_schema_props_perms(commit=False)
self.assertEquals(cursor.execute('Any D WHERE X name "Personne", X description D')[0][0],
@@ -380,8 +381,15 @@
# finally
self.assertEquals(cursor.execute('Any COUNT(X) WHERE X is RQLExpression')[0][0],
nbrqlexpr_start + 1 + 2 + 2)
-
- self.mh.rollback()
+ self.mh.commit()
+ # unique_together test
+ self.assertEqual(len(self.schema.eschema('Personne')._unique_together), 1)
+ self.assertUnorderedIterableEquals(self.schema.eschema('Personne')._unique_together[0],
+ ('nom', 'prenom', 'datenaiss'))
+ rset = cursor.execute('Any C WHERE C is CWUniqueTogetherConstraint')
+ self.assertEquals(len(rset), 1)
+ relations = [r.rtype.name for r in rset.get_entity(0,0).relations]
+ self.assertUnorderedIterableEquals(relations, ('nom', 'prenom', 'datenaiss'))
def _erqlexpr_rset(self, action, ertype):
rql = 'RQLExpression X WHERE ET is CWEType, ET %s_permission X, ET name %%(name)s' % action
--- a/server/test/unittest_repository.py Fri Sep 10 15:09:31 2010 +0200
+++ b/server/test/unittest_repository.py Fri Sep 10 16:48:58 2010 +0200
@@ -74,9 +74,27 @@
(u'Int',),
(u'Interval',), (u'Password',),
(u'String',), (u'Time',)])
+ sql = ("SELECT etype.cw_eid, etype.cw_name, cstr.cw_eid, rel.eid_to "
+ "FROM cw_CWUniqueTogetherConstraint as cstr, "
+ " relations_relation as rel, "
+ " cw_CWEType as etype "
+ "WHERE cstr.cw_eid = rel.eid_from "
+ " AND cstr.cw_constraint_of = etype.cw_eid "
+ " AND etype.cw_name = 'Personne' "
+ ";")
+ cu = self.session.system_sql(sql)
+ rows = cu.fetchall()
+ self.assertEquals(len(rows), 3)
+ self.test_unique_together()
finally:
self.repo.set_schema(origshema)
+ def test_unique_together(self):
+ person = self.repo.schema.eschema('Personne')
+ self.assertEquals(len(person._unique_together), 1)
+ self.assertUnorderedIterableEquals(person._unique_together[0],
+ ('nom', 'prenom', 'inline2'))
+
def test_schema_has_owner(self):
repo = self.repo
cnxid = repo.connect(self.admlogin, password=self.admpassword)