From 7fa21b22989f6d53ff70a8df71fc6d210c556e07 Mon Sep 17 00:00:00 2001 From: Johannes Erdfelt Date: Wed, 10 Sep 2014 07:37:59 -0700 Subject: [PATCH] Reflect unique constraints when reflecting a Table object Calls to reflect a table did not create any UniqueConstraint objects. The reflection core made no calls to get_unique_constraints and as a result, the sqlite dialect would never reflect any unique constraints. MySQL transparently converts unique constraints into unique indexes, but SQLAlchemy would reflect those as an Index object and as a UniqueConstraint. The reflection core will now deduplicate the unique constraints. PostgreSQL would reflect unique constraints as an Index object and as a UniqueConstraint object. The reflection core will now deduplicate the unique indexes. --- lib/sqlalchemy/dialects/mysql/base.py | 3 +- lib/sqlalchemy/dialects/postgresql/base.py | 18 ++++++--- lib/sqlalchemy/engine/reflection.py | 34 +++++++++++++++++ .../testing/suite/test_reflection.py | 13 +++++-- test/dialect/mysql/test_reflection.py | 31 ++++++++++++++++ test/dialect/postgresql/test_reflection.py | 37 ++++++++++++++++++- 6 files changed, 124 insertions(+), 12 deletions(-) diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py index 7ccd59abb3..2f85a36263 100644 --- a/lib/sqlalchemy/dialects/mysql/base.py +++ b/lib/sqlalchemy/dialects/mysql/base.py @@ -2590,7 +2590,8 @@ class MySQLDialect(default.DefaultDialect): return [ { 'name': key['name'], - 'column_names': [col[0] for col in key['columns']] + 'column_names': [col[0] for col in key['columns']], + 'duplicates_index': key['name'], } for key in parsed_state.keys if key['type'] == 'UNIQUE' diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index b9a0d461bc..556493b3c3 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -2471,14 +2471,19 @@ class PGDialect(default.DefaultDialect): SELECT i.relname as relname, ix.indisunique, ix.indexprs, ix.indpred, - a.attname, a.attnum, ix.indkey%s + a.attname, a.attnum, c.conrelid, ix.indkey%s FROM pg_class t join pg_index ix on t.oid = ix.indrelid - join pg_class i on i.oid=ix.indexrelid + join pg_class i on i.oid = ix.indexrelid left outer join pg_attribute a - on t.oid=a.attrelid and %s + on t.oid = a.attrelid and %s + left outer join + pg_constraint c + on (ix.indrelid = c.conrelid and + ix.indexrelid = c.conindid and + c.contype in ('p', 'u', 'x')) WHERE t.relkind IN ('r', 'v', 'f', 'm') and t.oid = :table_oid @@ -2501,7 +2506,7 @@ class PGDialect(default.DefaultDialect): sv_idx_name = None for row in c.fetchall(): - idx_name, unique, expr, prd, col, col_num, idx_key = row + idx_name, unique, expr, prd, col, col_num, conrelid, idx_key = row if expr: if idx_name != sv_idx_name: @@ -2523,11 +2528,14 @@ class PGDialect(default.DefaultDialect): index['cols'][col_num] = col index['key'] = [int(k.strip()) for k in idx_key.split()] index['unique'] = unique + index['duplicates_constraint'] = (None if conrelid is None + else idx_name) return [ {'name': name, 'unique': idx['unique'], - 'column_names': [idx['cols'][i] for i in idx['key']]} + 'column_names': [idx['cols'][i] for i in idx['key']], + 'duplicates_constraint': idx['duplicates_constraint']} for name, idx in indexes.items() ] diff --git a/lib/sqlalchemy/engine/reflection.py b/lib/sqlalchemy/engine/reflection.py index c0a3240a53..330fc2b196 100644 --- a/lib/sqlalchemy/engine/reflection.py +++ b/lib/sqlalchemy/engine/reflection.py @@ -603,12 +603,15 @@ class Inspector(object): columns = index_d['column_names'] unique = index_d['unique'] flavor = index_d.get('type', 'index') + duplicates = index_d.get('duplicates_constraint') if include_columns and \ not set(columns).issubset(include_columns): util.warn( "Omitting %s key for (%s), key covers omitted columns." % (flavor, ', '.join(columns))) continue + if duplicates: + continue # look for columns by orig name in cols_by_orig_name, # but support columns that are in-Python only as fallback idx_cols = [] @@ -626,3 +629,34 @@ class Inspector(object): idx_cols.append(idx_col) sa_schema.Index(name, *idx_cols, **dict(unique=unique)) + + # Unique Constraints + constraints = self.get_unique_constraints(table_name, schema) + for const_d in constraints: + conname = const_d['name'] + columns = const_d['column_names'] + duplicates = const_d.get('duplicates_index') + if include_columns and \ + not set(columns).issubset(include_columns): + util.warn( + "Omitting unique constraint key for (%s), " + "key covers omitted columns." % + ', '.join(columns)) + continue + if duplicates: + continue + # look for columns by orig name in cols_by_orig_name, + # but support columns that are in-Python only as fallback + constrained_cols = [] + for c in columns: + try: + constrained_col = cols_by_orig_name[c] \ + if c in cols_by_orig_name else table.c[c] + except KeyError: + util.warn( + "unique constraint key '%s' was not located in " + "columns for table '%s'" % (c, table_name)) + else: + constrained_cols.append(constrained_col) + table.append_constraint( + sa_schema.UniqueConstraint(*constrained_cols, name=conname)) diff --git a/lib/sqlalchemy/testing/suite/test_reflection.py b/lib/sqlalchemy/testing/suite/test_reflection.py index 690a880bb7..bd0be57380 100644 --- a/lib/sqlalchemy/testing/suite/test_reflection.py +++ b/lib/sqlalchemy/testing/suite/test_reflection.py @@ -487,10 +487,12 @@ class ComponentReflectionTest(fixtures.TablesTest): @testing.requires.temp_table_reflection def test_get_temp_table_unique_constraints(self): insp = inspect(self.metadata.bind) - eq_( - insp.get_unique_constraints('user_tmp'), - [{'column_names': ['name'], 'name': 'user_tmp_uq'}] - ) + reflected = insp.get_unique_constraints('user_tmp') + for refl in reflected: + # Different dialects handle duplicate index and constraints + # differently, so ignore this flag + refl.pop('duplicates_index', None) + eq_(reflected, [{'column_names': ['name'], 'name': 'user_tmp_uq'}]) @testing.requires.temp_table_reflection def test_get_temp_table_indexes(self): @@ -544,6 +546,9 @@ class ComponentReflectionTest(fixtures.TablesTest): ) for orig, refl in zip(uniques, reflected): + # Different dialects handle duplicate index and constraints + # differently, so ignore this flag + refl.pop('duplicates_index', None) eq_(orig, refl) @testing.provide_metadata diff --git a/test/dialect/mysql/test_reflection.py b/test/dialect/mysql/test_reflection.py index bf35a2c6bf..b8f72b9426 100644 --- a/test/dialect/mysql/test_reflection.py +++ b/test/dialect/mysql/test_reflection.py @@ -283,6 +283,37 @@ class ReflectionTest(fixtures.TestBase, AssertsExecutionResults): view_names = dialect.get_view_names(connection, "information_schema") self.assert_('TABLES' in view_names) + def test_reflection_with_unique_constraint(self): + insp = inspect(testing.db) + + uc_table = Table('mysql_uc', MetaData(testing.db), + Column('a', String(10)), + UniqueConstraint('a', name='uc_a')) + + try: + uc_table.create() + + # MySQL converts unique constraints into unique indexes and + # the 0.9 API returns it as both an index and a constraint + indexes = set(i['name'] for i in insp.get_indexes('mysql_uc')) + constraints = set(i['name'] + for i in insp.get_unique_constraints('mysql_uc')) + + self.assert_('uc_a' in indexes) + self.assert_('uc_a' in constraints) + + # However, upon creating a Table object via reflection, it should + # only appear as a unique index and not a constraint + reflected = Table('mysql_uc', MetaData(testing.db), autoload=True) + + indexes = set(i.name for i in reflected.indexes) + constraints = set(uc.name for uc in reflected.constraints) + + self.assert_('uc_a' in indexes) + self.assert_('uc_a' not in constraints) + finally: + uc_table.drop() + class RawReflectionTest(fixtures.TestBase): def setup(self): diff --git a/test/dialect/postgresql/test_reflection.py b/test/dialect/postgresql/test_reflection.py index b8b9be3dec..fc013c72aa 100644 --- a/test/dialect/postgresql/test_reflection.py +++ b/test/dialect/postgresql/test_reflection.py @@ -7,7 +7,7 @@ from sqlalchemy.testing import fixtures from sqlalchemy import testing from sqlalchemy import inspect from sqlalchemy import Table, Column, MetaData, Integer, String, \ - PrimaryKeyConstraint, ForeignKey, join, Sequence + PrimaryKeyConstraint, ForeignKey, join, Sequence, UniqueConstraint from sqlalchemy import exc import sqlalchemy as sa from sqlalchemy.dialects.postgresql import base as postgresql @@ -656,7 +656,8 @@ class ReflectionTest(fixtures.TestBase): conn.execute("ALTER TABLE t RENAME COLUMN x to y") ind = testing.db.dialect.get_indexes(conn, "t", None) - eq_(ind, [{'unique': False, 'column_names': ['y'], 'name': 'idx1'}]) + eq_(ind, [{'unique': False, 'duplicates_constraint': None, + 'column_names': ['y'], 'name': 'idx1'}]) conn.close() @testing.provide_metadata @@ -803,6 +804,38 @@ class ReflectionTest(fixtures.TestBase): 'labels': ['sad', 'ok', 'happy'] }]) + def test_reflection_with_unique_constraint(self): + insp = inspect(testing.db) + + uc_table = Table('pgsql_uc', MetaData(testing.db), + Column('a', String(10)), + UniqueConstraint('a', name='uc_a')) + + try: + uc_table.create() + + # PostgreSQL will create an implicit index for a unique + # constraint. As a result, the 0.9 API returns it as both + # an index and a constraint + indexes = set(i['name'] for i in insp.get_indexes('pgsql_uc')) + constraints = set(i['name'] + for i in insp.get_unique_constraints('pgsql_uc')) + + self.assert_('uc_a' in indexes) + self.assert_('uc_a' in constraints) + + # However, upon creating a Table object via reflection, it should + # only appear as a unique constraint and not an index + reflected = Table('pgsql_uc', MetaData(testing.db), autoload=True) + + indexes = set(i.name for i in reflected.indexes) + constraints = set(uc.name for uc in reflected.constraints) + + self.assert_('uc_a' not in indexes) + self.assert_('uc_a' in constraints) + finally: + uc_table.drop() + class CustomTypeReflectionTest(fixtures.TestBase): -- 2.47.2