# HG changeset patch # User Sylvain Thénault # Date 1466438383 -7200 # Node ID 2da2dd60331c54edd9aa8f4956cae454cd3ac012 # Parent 179b5ff3f42855c221f9e41f7bf4eeac532b18e3 [sql gen] Explicitly name unique index This patch hotfix logilab database for https://www.logilab.org/6662663 and fix database creation to stop using UNIQUE in the table schema but rather create the index later on, so its name is under control. Also consider this change in schema synchronization hooks. This is preliminary work related to #13822045 diff -r 179b5ff3f428 -r 2da2dd60331c cubicweb/hooks/syncschema.py --- a/cubicweb/hooks/syncschema.py Wed Jun 22 17:13:10 2016 +0200 +++ b/cubicweb/hooks/syncschema.py Mon Jun 20 17:59:43 2016 +0200 @@ -475,14 +475,6 @@ # then make necessary changes to the system source database syssource = cnx.repo.system_source attrtype = y2sql.type_from_rdef(syssource.dbhelper, rdef) - # XXX should be moved somehow into lgdb: sqlite doesn't support to - # add a new column with UNIQUE, it should be added after the ALTER TABLE - # using ADD INDEX - if syssource.dbdriver == 'sqlite' and 'UNIQUE' in attrtype: - extra_unique_index = True - attrtype = attrtype.replace(' UNIQUE', '') - else: - extra_unique_index = False # added some str() wrapping query since some backend (eg psycopg) don't # allow unicode queries table = SQL_PREFIX + rdefdef.subject @@ -497,10 +489,9 @@ # the entity's type has just been added or if the column # has not been previously dropped self.error('error while altering table %s: %s', table, ex) - if extra_unique_index or entity.indexed: + if entity.indexed: try: - syssource.create_index(cnx, table, column, - unique=extra_unique_index) + syssource.create_index(cnx, table, column, unique=False) except Exception as ex: self.error('error while creating index for %s.%s: %s', table, column, ex) @@ -654,7 +645,6 @@ self.cnx.vreg.schema.add_relation_def(rdef) - class RDefUpdateOp(MemSchemaOperation): """actually update some properties of a relation definition""" rschema = rdefkey = values = None # make pylint happy @@ -766,11 +756,6 @@ def precommit_event(self): cnx = self.cnx rdefentity = self.entity.reverse_constrained_by[0] - # when the relation is added in the same transaction, the constraint - # object is created by the operation adding the attribute or relation, - # so there is nothing to do here - if cnx.added_in_transaction(rdefentity.eid): - return rdef = self.rdef = cnx.vreg.schema.schema_by_eid(rdefentity.eid) cstrtype = self.entity.type if cstrtype in UNIQUE_CONSTRAINTS: diff -r 179b5ff3f428 -r 2da2dd60331c cubicweb/server/schema2sql.py --- a/cubicweb/server/schema2sql.py Wed Jun 22 17:13:10 2016 +0200 +++ b/cubicweb/server/schema2sql.py Mon Jun 20 17:59:43 2016 +0200 @@ -26,12 +26,24 @@ from yams.constraints import (SizeConstraint, UniqueConstraint, Attribute, NOW, TODAY) +from logilab import database +from logilab.common.decorators import monkeypatch # default are usually not handled at the sql level. If you want them, set # SET_DEFAULT to True SET_DEFAULT = False +# backport fix for lgdb #6662663 +@monkeypatch(database._GenericAdvFuncHelper) +def sql_create_index(self, table, column, unique=False): + idx = self._index_name(table, column, unique) + if unique: + return 'ALTER TABLE %s ADD CONSTRAINT %s UNIQUE(%s);' % (table, idx, column) + else: + return 'CREATE INDEX %s ON %s(%s);' % (idx, table, column) + + def rschema_has_table(rschema, skip_relations): """Return True if the given schema should have a table in the database""" return not (rschema.final or rschema.inlined or rschema.rule or rschema.type in skip_relations) @@ -146,6 +158,9 @@ rschema, attrschema = attrs[i] if attrschema is None or eschema.rdef(rschema).indexed: w(dbhelper.sql_create_index(table, prefix + rschema.type)) + if attrschema and any(isinstance(cstr, UniqueConstraint) + for cstr in eschema.rdef(rschema).constraints): + w(dbhelper.sql_create_index(table, prefix + rschema.type, unique=True)) for columns, index_name in iter_unique_index_names(eschema): cols = ['%s%s' % (prefix, col) for col in columns] sqls = dbhelper.sqls_create_multicol_unique_index(table, cols, index_name) @@ -198,7 +213,7 @@ """write an attribute schema as SQL statements to stdout""" attr = rschema.type rdef = rschema.rdef(eschema.type, aschema.type) - sqltype = type_from_rdef(dbhelper, rdef, creating) + sqltype = type_from_rdef(dbhelper, rdef) if SET_DEFAULT: default = eschema.default(attr) if default is not None: @@ -222,23 +237,19 @@ return sqltype -def type_from_rdef(dbhelper, rdef, creating=True): +def type_from_rdef(dbhelper, rdef): """return a sql type string corresponding to the relation definition""" constraints = list(rdef.constraints) - unique, sqltype = False, None - for constraint in constraints: - if isinstance(constraint, UniqueConstraint): - unique = True - elif (isinstance(constraint, SizeConstraint) - and rdef.object.type == 'String' - and constraint.max is not None): - size_constrained_string = dbhelper.TYPE_MAPPING.get( - 'SizeConstrainedString', 'varchar(%s)') - sqltype = size_constrained_string % constraint.max + sqltype = None + if rdef.object.type == 'String': + for constraint in constraints: + if isinstance(constraint, SizeConstraint) and constraint.max is not None: + size_constrained_string = dbhelper.TYPE_MAPPING.get( + 'SizeConstrainedString', 'varchar(%s)') + sqltype = size_constrained_string % constraint.max + break if sqltype is None: sqltype = sql_type(dbhelper, rdef) - if creating and unique: - sqltype += ' UNIQUE' return sqltype diff -r 179b5ff3f428 -r 2da2dd60331c cubicweb/server/sources/native.py --- a/cubicweb/server/sources/native.py Wed Jun 22 17:13:10 2016 +0200 +++ b/cubicweb/server/sources/native.py Mon Jun 20 17:59:43 2016 +0200 @@ -121,7 +121,7 @@ """ if not rdef.object.final: return dbhelper.TYPE_MAPPING['Int'] - coltype = y2sql.type_from_rdef(dbhelper, rdef, creating=False) + coltype = y2sql.type_from_rdef(dbhelper, rdef) allownull = rdef.cardinality[0] != '1' return coltype, allownull diff -r 179b5ff3f428 -r 2da2dd60331c cubicweb/server/test/unittest_migractions.py --- a/cubicweb/server/test/unittest_migractions.py Wed Jun 22 17:13:10 2016 +0200 +++ b/cubicweb/server/test/unittest_migractions.py Mon Jun 20 17:59:43 2016 +0200 @@ -186,7 +186,15 @@ # test default value set on existing entities self.assertEqual(cnx.execute('Note X').get_entity(0, 0).shortpara, 'hop') # test default value set for next entities - self.assertEqual(cnx.create_entity('Note').shortpara, 'hop') + self.assertEqual(cnx.create_entity('Note', shortpara=u'hophop').shortpara, u'hophop') + + def test_add_attribute_unique(self): + with self.mh() as (cnx, mh): + self.assertNotIn('unique_id', self.schema) + mh.cmd_add_attribute('Note', 'unique_id') + # test unique index creation + dbh = self.repo.system_source.dbhelper + self.assertTrue(dbh.index_exists(cnx.cnxset.cu, 'cw_Note', 'cw_unique_id', unique=True)) def test_add_datetime_with_default_value_attribute(self): with self.mh() as (cnx, mh): diff -r 179b5ff3f428 -r 2da2dd60331c cubicweb/server/test/unittest_schema2sql.py --- a/cubicweb/server/test/unittest_schema2sql.py Wed Jun 22 17:13:10 2016 +0200 +++ b/cubicweb/server/test/unittest_schema2sql.py Mon Jun 20 17:59:43 2016 +0200 @@ -65,13 +65,14 @@ CREATE INDEX epermission_name_idx ON EPermission(name); CREATE TABLE Eetype( - name varchar(64) UNIQUE NOT NULL, + name varchar(64) NOT NULL, description text, meta boolean, final boolean, initial_state integer REFERENCES entities (eid) ); CREATE INDEX eetype_name_idx ON Eetype(name); +ALTER TABLE Eetype ADD CONSTRAINT eetype_name_key UNIQUE(name); CREATE INDEX eetype_initial_state_idx ON Eetype(initial_state); CREATE TABLE Employee( @@ -122,7 +123,7 @@ CREATE TABLE Societe( nom varchar(64), web varchar(128), - tel integer UNIQUE, + tel integer, fax integer, rncs varchar(32), ad1 varchar(128), @@ -132,6 +133,7 @@ ville varchar(32) , CONSTRAINT cstra0a1deaa997dcd5f9b83a77654d7c287 CHECK(fax <= tel) ); +ALTER TABLE Societe ADD CONSTRAINT societe_tel_key UNIQUE(tel); CREATE TABLE State( eid integer PRIMARY KEY REFERENCES entities (eid),