unique indexes: take an index name

This avoids creating index names from the column list. In practice this can yield long names which can be silently truncated at index creation, leading to an annoying information loss.

Closes #151507.

authorAurelien Campeas <aurelien.campeas@logilab.fr>
changesete1ed19a8d6dd
branchdefault
phasepublic
hiddenno
parent revision#67e79ef2af65 [postgres] Remove dead tokenization code and import
child revision#eef5e445ece1 [postgres] Add an adapter for the psycopg2cffi driver, which works great with pypy, #337613894bac 1.11
files modified by this revision
__init__.py
sqlserver2005.py
# HG changeset patch
# User Aurelien Campeas <aurelien.campeas@logilab.fr>
# Date 1381328655 -7200
# Wed Oct 09 16:24:15 2013 +0200
# Node ID e1ed19a8d6ddb547669075eb2676bea19315a3f4
# Parent 67e79ef2af65704801fa732569e9802277825258
unique indexes: take an index name

This avoids creating index names from the column list.
In practice this can yield long names which can be silently truncated at index creation,
leading to an annoying information loss.

Closes #151507.

diff --git a/__init__.py b/__init__.py
@@ -887,22 +887,29 @@ 
1          if unique:
2              return 'ALTER TABLE %s DROP CONSTRAINT %s' % (table, idx)
3          else:
4              return 'DROP INDEX %s' % idx
5 
6 -    def sqls_create_multicol_unique_index(self, table, columns):
7 +    def sqls_create_multicol_unique_index(self, table, columns, indexname=None):
8          columns = sorted(columns)
9 -        idx = 'unique_%s_%s_idx' % (table, '_'.join(columns))
10 -        sql = 'CREATE UNIQUE INDEX %s ON %s(%s);' % (idx.lower(),
11 -                                                     table,
12 -                                                     ','.join(columns))
13 +        if indexname is None:
14 +            warn('You should provide an explicit index name else you risk '
15 +                 'a silent truncation of the computed index name.',
16 +                 DeprecationWarning)
17 +            indexname = 'unique_%s_%s_idx' % (table, '_'.join(columns))
18 +        sql = 'CREATE UNIQUE INDEX %s ON %s(%s);' % (indexname.lower(),
19 +                                                     table, ','.join(columns))
20          return [sql]
21 
22 -    def sqls_drop_multicol_unique_index(self, table, columns):
23 +    def sqls_drop_multicol_unique_index(self, table, columns, indexname=None):
24          columns = sorted(columns)
25 -        idx = 'unique_%s_%s_idx' % (table, '_'.join(columns))
26 -        sql = 'DROP INDEX %s;' % (idx.lower())
27 +        if indexname is None:
28 +            warn('You should provide an explicit index name else you risk '
29 +                 'a silent truncation of the computed index name.',
30 +                 DeprecationWarning)
31 +            indexname = 'unique_%s_%s_idx' % (table, '_'.join(columns))
32 +        sql = 'DROP INDEX %s;' % (indexname.lower())
33          return [sql]
34 
35      def sql_create_sequence(self, seq_name):
36          return '''CREATE TABLE %s (last INTEGER);
37  INSERT INTO %s VALUES (0);''' % (seq_name, seq_name)
diff --git a/sqlserver2005.py b/sqlserver2005.py
@@ -226,28 +226,36 @@ 
38          else:
39              sql += '\nORDER BY %s' % ','.join(sortterms)
40 
41          return sql
42 
43 -    def sqls_create_multicol_unique_index(self, table, columns):
44 +    def sqls_create_multicol_unique_index(self, table, columns, indexname=None):
45          columns = sorted(columns)
46 -        view = 'utv_%s_%s' % (table, '_'.join(columns))
47 +        view = 'utv_%s_%s' % (table, indexname or '_'.join(columns))
48          where = ' AND '.join(['%s IS NOT NULL' % c for c in columns])
49 -        idx = 'unique_%s_%s_idx' % (table, '_'.join(columns))
50 +        if indexname is None:
51 +            warn('You should provide an explicit index name else you risk '
52 +                 'a silent truncation of the computed index name.',
53 +                 DeprecationWarning)
54 +            indexname = 'unique_%s_%s_idx' % (table, '_'.join(columns))
55          sql = ['CREATE VIEW %s WITH SCHEMABINDING AS SELECT %s FROM dbo.%s WHERE %s ;'%(view.lower(), 
56                                                        ', '.join(columns),
57                                                        table,
58                                                        where),
59 -               'CREATE UNIQUE CLUSTERED INDEX %s ON %s(%s);' % (idx.lower(),
60 +               'CREATE UNIQUE CLUSTERED INDEX %s ON %s(%s);' % (indexname.lower(),
61                                                                  view.lower(),
62                                                                  ','.join(columns))
63              ]
64          return sql
65 
66 -    def sqls_drop_multicol_unique_index(self, table, columns):
67 +    def sqls_drop_multicol_unique_index(self, table, columns, indexname=None):
68 +        if indexname is None:
69 +            warn('You should provide an explicit index name else you risk '
70 +                 'a silent truncation of the computed index name.',
71 +                 DeprecationWarning)
72          columns = sorted(columns)
73 -        view = 'utv_%s_%s' % (table, '_'.join(columns))
74 +        view = 'utv_%s_%s' % (table, indexname or '_'.join(columns))
75          sql = 'DROP VIEW %s' % (view.lower()) # also drops the index
76          return [sql]
77 
78      def sql_drop_index(self, table, column, unique=False):
79          if unique: