[sql gen] Control size of index name using an md5 hash
When using concatenation of table and column, we may hit the backend size limit
on index names, which results on truncated name (64 for postgres). Then we may
have conflict or be unable to find expected index on later synchronization
attempt.
This is done by monkeypatching lgdb for now to avoid dependancy problems.
Unique and primary key indices of entity types handling is left to the backend,
as they are not explicitly named.
See https://www.logilab.org/ticket/4789189
Closes #13821949
--- a/cubicweb/dataimport/test/test_massive_store.py Mon Jun 20 17:59:43 2016 +0200
+++ b/cubicweb/dataimport/test/test_massive_store.py Wed Jun 22 07:57:13 2016 +0200
@@ -21,6 +21,7 @@
from cubicweb.devtools import testlib, PostgresApptestConfiguration
from cubicweb.devtools import startpgcluster, stoppgcluster
from cubicweb.dataimport import ucsvreader, stores
+from cubicweb.server.schema2sql import build_index_name
from cubicweb.dataimport.massive_store import MassiveObjectStore, PGHelper
import test_stores
@@ -61,7 +62,7 @@
for code, gmt, dst, raw_offset in ucsvreader(open(self.datapath('timeZones.txt'), 'rb'),
delimiter='\t'):
cnx.create_entity('TimeZone', code=code, gmt=float(gmt),
- dst=float(dst), raw_offset=float(raw_offset))
+ dst=float(dst), raw_offset=float(raw_offset))
timezone_code = dict(cnx.execute('Any C, X WHERE X is TimeZone, X code C'))
# Push data
for ind, infos in enumerate(ucsvreader(open(dumpname, 'rb'),
@@ -139,8 +140,10 @@
indexes = [r[0] for r in crs.fetchall()]
self.assertIn('entities_pkey', indexes)
self.assertIn('entities_extid_idx', indexes)
- self.assertIn('owned_by_relation_p_key', indexes)
- self.assertIn('owned_by_relation_to_idx', indexes)
+ self.assertIn(build_index_name('owned_by_relation', ['eid_from', 'eid_to'], 'key_'),
+ indexes)
+ self.assertIn(build_index_name('owned_by_relation', ['eid_from'], 'idx_'),
+ indexes)
def test_eids_seq_range(self):
with self.admin_access.repo_cnx() as cnx:
@@ -219,8 +222,10 @@
indexes = [r[0] for r in crs.fetchall()]
self.assertIn('entities_pkey', indexes)
self.assertIn('entities_extid_idx', indexes)
- self.assertIn('owned_by_relation_p_key', indexes)
- self.assertIn('owned_by_relation_to_idx', indexes)
+ self.assertIn(build_index_name('owned_by_relation', ['eid_from', 'eid_to'], 'key_'),
+ indexes)
+ self.assertIn(build_index_name('owned_by_relation', ['eid_from'], 'idx_'),
+ indexes)
def test_slave_mode_exception(self):
with self.admin_access.repo_cnx() as cnx:
@@ -252,8 +257,10 @@
indexes = [r[0] for r in crs.fetchall()]
self.assertNotIn('entities_pkey', indexes)
self.assertNotIn('entities_extid_idx', indexes)
- self.assertNotIn('owned_by_relation_p_key', indexes)
- self.assertNotIn('owned_by_relation_to_idx', indexes)
+ self.assertNotIn(build_index_name('owned_by_relation', ['eid_from', 'eid_to'], 'key_'),
+ indexes)
+ self.assertNotIn(build_index_name('owned_by_relation', ['eid_from'], 'idx_'),
+ indexes)
# Cleanup -> index
store.finish()
@@ -263,8 +270,10 @@
indexes = [r[0] for r in crs.fetchall()]
self.assertIn('entities_pkey', indexes)
self.assertIn('entities_extid_idx', indexes)
- self.assertIn('owned_by_relation_p_key', indexes)
- self.assertIn('owned_by_relation_to_idx', indexes)
+ self.assertIn(build_index_name('owned_by_relation', ['eid_from', 'eid_to'], 'key_'),
+ indexes)
+ self.assertIn(build_index_name('owned_by_relation', ['eid_from'], 'idx_'),
+ indexes)
def test_multiple_insert(self):
with self.admin_access.repo_cnx() as cnx:
--- a/cubicweb/misc/migration/3.23.0_Any.py Mon Jun 20 17:59:43 2016 +0200
+++ b/cubicweb/misc/migration/3.23.0_Any.py Wed Jun 22 07:57:13 2016 +0200
@@ -1,5 +1,42 @@
+
+from functools import partial
+
+from yams.constraints import UniqueConstraint
+
+from cubicweb.schema import PURE_VIRTUAL_RTYPES
+from cubicweb.server.schema2sql import build_index_name, check_constraint
+
+sql = partial(sql, ask_confirm=False)
+
+source = repo.system_source
+
+for rschema in schema.relations():
+ if rschema.rule or rschema in PURE_VIRTUAL_RTYPES:
+ continue
+ if rschema.final or rschema.inlined:
+ for rdef in rschema.rdefs.values():
+ table = 'cw_{0}'.format(rdef.subject)
+ column = 'cw_{0}'.format(rdef.rtype)
+ if any(isinstance(cstr, UniqueConstraint) for cstr in rdef.constraints):
+ old_name = '%s_%s_key' % (table.lower(), column.lower())
+ sql('ALTER TABLE %s DROP CONSTRAINT %s' % (table, old_name))
+ source.create_index(cnx, table, column, unique=True)
+ if rschema.inlined or rdef.indexed:
+ old_name = '%s_%s_idx' % (table.lower(), column.lower())
+ sql('DROP INDEX IF EXISTS %s' % old_name)
+ source.create_index(cnx, table, column)
+ else:
+ table = '{0}_relation'.format(rschema)
+ sql('ALTER TABLE %s DROP CONSTRAINT IF EXISTS %s_p_key' % (table, table))
+ sql('ALTER TABLE %s ADD CONSTRAINT %s PRIMARY KEY(eid_from, eid_to)'
+ % (table, build_index_name(table, ['eid_from', 'eid_to'], 'key_')))
+ for column in ('from', 'to'):
+ sql('DROP INDEX IF EXISTS %s_%s_idx' % (table, column))
+ sql('CREATE INDEX %s ON %s(eid_%s);'
+ % (build_index_name(table, ['eid_' + column], 'idx_'), table, column))
+
+
# we changed constraint serialization, which also changes their name
-from cubicweb.server.schema2sql import check_constraint
helper = repo.system_source.dbhelper
@@ -22,3 +59,4 @@
sql('ALTER TABLE cw_%(e)s ADD CONSTRAINT %(c)s CHECK(%(v)s)' % args)
commit()
+
--- a/cubicweb/server/schema2sql.py Mon Jun 20 17:59:43 2016 +0200
+++ b/cubicweb/server/schema2sql.py Wed Jun 22 07:57:13 2016 +0200
@@ -21,7 +21,7 @@
from hashlib import md5
-from six import string_types
+from six import string_types, text_type
from six.moves import range
from yams.constraints import (SizeConstraint, UniqueConstraint, Attribute,
@@ -44,6 +44,20 @@
return 'CREATE INDEX %s ON %s(%s);' % (idx, table, column)
+@monkeypatch(database._GenericAdvFuncHelper)
+def _index_name(self, table, column, unique=False):
+ if unique:
+ return build_index_name(table, [column], prefix='key_')
+ else:
+ return build_index_name(table, [column], prefix='idx_')
+
+
+def build_index_name(table, columns, prefix='idx_'):
+ return '%s%s' % (prefix, md5((table +
+ ',' +
+ ','.join(sorted(columns))).encode('ascii')).hexdigest())
+
+
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)
@@ -97,9 +111,8 @@
def unique_index_name(eschema, columns):
- return u'unique_%s' % md5((eschema.type +
- ',' +
- ','.join(sorted(columns))).encode('ascii')).hexdigest()
+ # unique_index_name is used as name of CWUniqueConstraint, hence it should be unicode
+ return text_type(build_index_name(eschema.type, columns, 'unique_'))
def iter_unique_index_names(eschema):
@@ -264,16 +277,20 @@
CREATE TABLE %(table)s (
eid_from INTEGER NOT NULL REFERENCES entities (eid),
eid_to INTEGER NOT NULL REFERENCES entities (eid),
- CONSTRAINT %(table)s_p_key PRIMARY KEY(eid_from, eid_to)
+ CONSTRAINT %(pkey_idx)s PRIMARY KEY(eid_from, eid_to)
);
-CREATE INDEX %(table)s_from_idx ON %(table)s(eid_from);
-CREATE INDEX %(table)s_to_idx ON %(table)s(eid_to);"""
+CREATE INDEX %(from_idx)s ON %(table)s(eid_from);
+CREATE INDEX %(to_idx)s ON %(table)s(eid_to);"""
def rschema2sql(rschema):
assert not rschema.rule
- return _SQL_SCHEMA % {'table': '%s_relation' % rschema.type}
+ table = '%s_relation' % rschema.type
+ return _SQL_SCHEMA % {'table': table,
+ 'pkey_idx': build_index_name(table, ['eid_from', 'eid_to'], 'key_'),
+ 'from_idx': build_index_name(table, ['eid_from'], 'idx_'),
+ 'to_idx': build_index_name(table, ['eid_to'], 'idx_')}
def droprschema2sql(rschema):
--- a/cubicweb/server/test/unittest_schema2sql.py Mon Jun 20 17:59:43 2016 +0200
+++ b/cubicweb/server/test/unittest_schema2sql.py Wed Jun 22 07:57:13 2016 +0200
@@ -39,7 +39,7 @@
ref varchar(12),
inline_rel integer REFERENCES entities (eid)
);
-CREATE INDEX affaire_inline_rel_idx ON Affaire(inline_rel);
+CREATE INDEX idx_444e29ba3bd1f6c7ea89008613345d7b ON Affaire(inline_rel);
CREATE TABLE Company(
name text
@@ -62,7 +62,7 @@
CREATE TABLE EPermission(
name varchar(100) NOT NULL
);
-CREATE INDEX epermission_name_idx ON EPermission(name);
+CREATE INDEX idx_86fb596553c6f1ebc159422169f76c32 ON EPermission(name);
CREATE TABLE Eetype(
name varchar(64) NOT NULL,
@@ -71,9 +71,9 @@
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 INDEX idx_f1f29b77c85f57921df19d2c29044d2d ON Eetype(name);
+ALTER TABLE Eetype ADD CONSTRAINT key_f1f29b77c85f57921df19d2c29044d2d UNIQUE(name);
+CREATE INDEX idx_27be7c0b18181bbdc76f3a54296dd81f ON Eetype(initial_state);
CREATE TABLE Employee(
);
@@ -133,14 +133,14 @@
ville varchar(32)
, CONSTRAINT cstra0a1deaa997dcd5f9b83a77654d7c287 CHECK(fax <= tel)
);
-ALTER TABLE Societe ADD CONSTRAINT societe_tel_key UNIQUE(tel);
+ALTER TABLE Societe ADD CONSTRAINT key_abace82c402eba4a37ac54a7872607af UNIQUE(tel);
CREATE TABLE State(
eid integer PRIMARY KEY REFERENCES entities (eid),
name varchar(256) NOT NULL,
description text
);
-CREATE INDEX state_name_idx ON State(name);
+CREATE INDEX idx_fba3802ef9056558bb9c06b5c6ba9aab ON State(name);
CREATE TABLE Subcompany(
name text
@@ -169,112 +169,113 @@
CREATE TABLE concerne_relation (
eid_from INTEGER NOT NULL REFERENCES entities (eid),
eid_to INTEGER NOT NULL REFERENCES entities (eid),
- CONSTRAINT concerne_relation_p_key PRIMARY KEY(eid_from, eid_to)
+ CONSTRAINT key_19e70eabae35becb48c247bc4a688170 PRIMARY KEY(eid_from, eid_to)
);
-CREATE INDEX concerne_relation_from_idx ON concerne_relation(eid_from);
-CREATE INDEX concerne_relation_to_idx ON concerne_relation(eid_to);
+CREATE INDEX idx_5ee7db9477832d6e0e847d9d9cd39f5f ON concerne_relation(eid_from);
+CREATE INDEX idx_07f609872b384bb1e598cc355686a53c ON concerne_relation(eid_to);
CREATE TABLE division_of_relation (
eid_from INTEGER NOT NULL REFERENCES entities (eid),
eid_to INTEGER NOT NULL REFERENCES entities (eid),
- CONSTRAINT division_of_relation_p_key PRIMARY KEY(eid_from, eid_to)
+ CONSTRAINT key_ca129a4cfa4c185c7783654e9e97da5a PRIMARY KEY(eid_from, eid_to)
);
-CREATE INDEX division_of_relation_from_idx ON division_of_relation(eid_from);
-CREATE INDEX division_of_relation_to_idx ON division_of_relation(eid_to);
+CREATE INDEX idx_78da9d594180fecb68ef1eba0c17a975 ON division_of_relation(eid_from);
+CREATE INDEX idx_0e6bd09d8d25129781928848e2f6d8d5 ON division_of_relation(eid_to);
CREATE TABLE evaluee_relation (
eid_from INTEGER NOT NULL REFERENCES entities (eid),
eid_to INTEGER NOT NULL REFERENCES entities (eid),
- CONSTRAINT evaluee_relation_p_key PRIMARY KEY(eid_from, eid_to)
+ CONSTRAINT key_61aa7ea90ed7e43818c9865a3a7eb046 PRIMARY KEY(eid_from, eid_to)
);
-CREATE INDEX evaluee_relation_from_idx ON evaluee_relation(eid_from);
-CREATE INDEX evaluee_relation_to_idx ON evaluee_relation(eid_to);
+CREATE INDEX idx_69358dbe47990b4f8cf22af55b064dc5 ON evaluee_relation(eid_from);
+CREATE INDEX idx_634663371244297334ff655a26d6cce3 ON evaluee_relation(eid_to);
CREATE TABLE next_state_relation (
eid_from INTEGER NOT NULL REFERENCES entities (eid),
eid_to INTEGER NOT NULL REFERENCES entities (eid),
- CONSTRAINT next_state_relation_p_key PRIMARY KEY(eid_from, eid_to)
+ CONSTRAINT key_24a1275472da1ccc1031f6c463cdaa95 PRIMARY KEY(eid_from, eid_to)
);
-CREATE INDEX next_state_relation_from_idx ON next_state_relation(eid_from);
-CREATE INDEX next_state_relation_to_idx ON next_state_relation(eid_to);
+CREATE INDEX idx_e5c1a2ddc41a057eaaf6bdf9f5c6b587 ON next_state_relation(eid_from);
+CREATE INDEX idx_a3cf3cb065213186cf825e13037df826 ON next_state_relation(eid_to);
CREATE TABLE obj_wildcard_relation (
eid_from INTEGER NOT NULL REFERENCES entities (eid),
eid_to INTEGER NOT NULL REFERENCES entities (eid),
- CONSTRAINT obj_wildcard_relation_p_key PRIMARY KEY(eid_from, eid_to)
+ CONSTRAINT key_d252c56177735139c85aee463cd65703 PRIMARY KEY(eid_from, eid_to)
);
-CREATE INDEX obj_wildcard_relation_from_idx ON obj_wildcard_relation(eid_from);
-CREATE INDEX obj_wildcard_relation_to_idx ON obj_wildcard_relation(eid_to);
+CREATE INDEX idx_efbd9bd98c44bdfe2add479ab6704017 ON obj_wildcard_relation(eid_from);
+CREATE INDEX idx_e8c168c66f9d6057ce14e644b8436808 ON obj_wildcard_relation(eid_to);
CREATE TABLE require_permission_relation (
eid_from INTEGER NOT NULL REFERENCES entities (eid),
eid_to INTEGER NOT NULL REFERENCES entities (eid),
- CONSTRAINT require_permission_relation_p_key PRIMARY KEY(eid_from, eid_to)
+ CONSTRAINT key_24f38c4edaf84fdcc0f0d093fec3d5c7 PRIMARY KEY(eid_from, eid_to)
);
-CREATE INDEX require_permission_relation_from_idx ON require_permission_relation(eid_from);
-CREATE INDEX require_permission_relation_to_idx ON require_permission_relation(eid_to);
+CREATE INDEX idx_193987ddfd7c66bf43ded029ea363605 ON require_permission_relation(eid_from);
+CREATE INDEX idx_f6dd784ff5161c4461a753591fe1de94 ON require_permission_relation(eid_to);
CREATE TABLE state_of_relation (
eid_from INTEGER NOT NULL REFERENCES entities (eid),
eid_to INTEGER NOT NULL REFERENCES entities (eid),
- CONSTRAINT state_of_relation_p_key PRIMARY KEY(eid_from, eid_to)
+ CONSTRAINT key_be6983bc3072230d2e22f7631a0c9e25 PRIMARY KEY(eid_from, eid_to)
);
-CREATE INDEX state_of_relation_from_idx ON state_of_relation(eid_from);
-CREATE INDEX state_of_relation_to_idx ON state_of_relation(eid_to);
+CREATE INDEX idx_5f17c14443de03bd1ef79750c89c2390 ON state_of_relation(eid_from);
+CREATE INDEX idx_0ee453927e090f6eec01c412278dea9b ON state_of_relation(eid_to);
CREATE TABLE subcompany_of_relation (
eid_from INTEGER NOT NULL REFERENCES entities (eid),
eid_to INTEGER NOT NULL REFERENCES entities (eid),
- CONSTRAINT subcompany_of_relation_p_key PRIMARY KEY(eid_from, eid_to)
+ CONSTRAINT key_25bee50df3b495a40a02aa39f832377f PRIMARY KEY(eid_from, eid_to)
);
-CREATE INDEX subcompany_of_relation_from_idx ON subcompany_of_relation(eid_from);
-CREATE INDEX subcompany_of_relation_to_idx ON subcompany_of_relation(eid_to);
+CREATE INDEX idx_1e6ee813030fec8d4439fc186ce752b0 ON subcompany_of_relation(eid_from);
+CREATE INDEX idx_259f9ba242f4cb80b9b2f2f9a754fca7 ON subcompany_of_relation(eid_to);
CREATE TABLE subdivision_of_relation (
eid_from INTEGER NOT NULL REFERENCES entities (eid),
eid_to INTEGER NOT NULL REFERENCES entities (eid),
- CONSTRAINT subdivision_of_relation_p_key PRIMARY KEY(eid_from, eid_to)
+ CONSTRAINT key_4d6f7368345676ebb66758ab71f60aef PRIMARY KEY(eid_from, eid_to)
);
-CREATE INDEX subdivision_of_relation_from_idx ON subdivision_of_relation(eid_from);
-CREATE INDEX subdivision_of_relation_to_idx ON subdivision_of_relation(eid_to);
+CREATE INDEX idx_a90a958166c767b50a7294e93858c1a8 ON subdivision_of_relation(eid_from);
+CREATE INDEX idx_0360028629649b26da96044a12735ad4 ON subdivision_of_relation(eid_to);
CREATE TABLE subj_wildcard_relation (
eid_from INTEGER NOT NULL REFERENCES entities (eid),
eid_to INTEGER NOT NULL REFERENCES entities (eid),
- CONSTRAINT subj_wildcard_relation_p_key PRIMARY KEY(eid_from, eid_to)
+ CONSTRAINT key_712ea3ec0bc1976bddc93ceba0acff06 PRIMARY KEY(eid_from, eid_to)
);
-CREATE INDEX subj_wildcard_relation_from_idx ON subj_wildcard_relation(eid_from);
-CREATE INDEX subj_wildcard_relation_to_idx ON subj_wildcard_relation(eid_to);
+CREATE INDEX idx_4dbfa4a0d44aaa0f0816560fa8b81c22 ON subj_wildcard_relation(eid_from);
+CREATE INDEX idx_09aa23f8a8b63189d05a63f8d49c7bc0 ON subj_wildcard_relation(eid_to);
CREATE TABLE sym_rel_relation (
eid_from INTEGER NOT NULL REFERENCES entities (eid),
eid_to INTEGER NOT NULL REFERENCES entities (eid),
- CONSTRAINT sym_rel_relation_p_key PRIMARY KEY(eid_from, eid_to)
+ CONSTRAINT key_c787b80522205c42402530580b0d307b PRIMARY KEY(eid_from, eid_to)
);
-CREATE INDEX sym_rel_relation_from_idx ON sym_rel_relation(eid_from);
-CREATE INDEX sym_rel_relation_to_idx ON sym_rel_relation(eid_to);
+CREATE INDEX idx_a46ed54f98cc4d91f0df5375d3ef73cb ON sym_rel_relation(eid_from);
+CREATE INDEX idx_0faa43abe25fc83e9400a3b96daed2b2 ON sym_rel_relation(eid_to);
CREATE TABLE travaille_relation (
eid_from INTEGER NOT NULL REFERENCES entities (eid),
eid_to INTEGER NOT NULL REFERENCES entities (eid),
- CONSTRAINT travaille_relation_p_key PRIMARY KEY(eid_from, eid_to)
+ CONSTRAINT key_d7b209a1f84d9cae74a98626ef0aba0b PRIMARY KEY(eid_from, eid_to)
);
-CREATE INDEX travaille_relation_from_idx ON travaille_relation(eid_from);
-CREATE INDEX travaille_relation_to_idx ON travaille_relation(eid_to);
+CREATE INDEX idx_b00e86c772e6577ad7a7901dd0b257b2 ON travaille_relation(eid_from);
+CREATE INDEX idx_970c052363294a9871a4824c9588e220 ON travaille_relation(eid_to);
"""
+
class SQLSchemaTC(TestCase):
def test_known_values(self):