# HG changeset patch # User Alexandre Fayolle # Date 1284130138 -7200 # Node ID 07b176640a8c33a5236b0a1e049a5f880cb5d41c # Parent c3f11fadf9edbdd22622610c4afb2587d1531b2f 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 diff -r c3f11fadf9ed -r 07b176640a8c __pkginfo__.py --- 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" diff -r c3f11fadf9ed -r 07b176640a8c hooks/syncschema.py --- 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 ############################################ diff -r c3f11fadf9ed -r 07b176640a8c misc/migration/bootstrapmigration_repository.py --- 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)) diff -r c3f11fadf9ed -r 07b176640a8c schemas/bootstrap.py --- 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 diff -r c3f11fadf9ed -r 07b176640a8c server/migractions.py --- 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) diff -r c3f11fadf9ed -r 07b176640a8c server/schemaserial.py --- 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) diff -r c3f11fadf9ed -r 07b176640a8c server/test/data/migratedapp/schema.py --- 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() diff -r c3f11fadf9ed -r 07b176640a8c server/test/data/schema.py --- 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) diff -r c3f11fadf9ed -r 07b176640a8c server/test/unittest_migractions.py --- 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 diff -r c3f11fadf9ed -r 07b176640a8c server/test/unittest_repository.py --- 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)