From 649f06759d933f4aacdfbb302e845e2bcb5e7641 Mon Sep 17 00:00:00 2001 From: Rodrigo Menezes Date: Thu, 14 Aug 2014 14:47:23 -0400 Subject: Added support for postgres_relkind. --- lib/sqlalchemy/dialects/postgresql/base.py | 44 ++++++++++++++++++++++-------- 1 file changed, 32 insertions(+), 12 deletions(-) (limited to 'lib/sqlalchemy/dialects/postgresql/base.py') diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index 5ff2f7c61..b3506f5d2 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -1669,11 +1669,12 @@ class PGDialect(default.DefaultDialect): "ops": {} }), (schema.Table, { - "ignore_search_path": False + "ignore_search_path": False, + "relkind": None }) ] - reflection_options = ('postgresql_ignore_search_path', ) + reflection_options = ('postgresql_ignore_search_path', 'postgresql_relkind') _backslash_escapes = True @@ -1898,7 +1899,7 @@ class PGDialect(default.DefaultDialect): return tuple([int(x) for x in m.group(1, 2, 3) if x is not None]) @reflection.cache - def get_table_oid(self, connection, table_name, schema=None, **kw): + def get_table_oid(self, connection, table_name, schema=None, postgresql_relkind=None, **kw): """Fetch the oid for schema.table_name. Several reflection methods require the table oid. The idea for using @@ -1911,13 +1912,28 @@ class PGDialect(default.DefaultDialect): schema_where_clause = "n.nspname = :schema" else: schema_where_clause = "pg_catalog.pg_table_is_visible(c.oid)" + + RELKIND_SYNONYMS = { + 'materialized': 'm', + 'foreign': 'f' + } + ACCEPTED_RELKINDS = ('r','v','m','f') + if postgresql_relkind is None: + postgresql_relkind = 'r' + else: + postgresql_relkind = postgresql_relkind.lower() + if postgresql_relkind in RELKIND_SYNONYMS: + postgresql_relkind = RELKIND_SYNONYMS[postgresql_relkind.lower()] + if postgresql_relkind not in ACCEPTED_RELKINDS: + raise exc.SQLAlchemyError('Invalid postgresql_relkind: %s' % postgresql_relkind) + query = """ SELECT c.oid FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE (%s) - AND c.relname = :table_name AND c.relkind in ('r','v') - """ % schema_where_clause + AND c.relname = :table_name AND c.relkind in ('%s', 'v') + """ % (schema_where_clause, postgresql_relkind) # Since we're binding to unicode, table_name and schema_name must be # unicode. table_name = util.text_type(table_name) @@ -2014,7 +2030,8 @@ class PGDialect(default.DefaultDialect): def get_columns(self, connection, table_name, schema=None, **kw): table_oid = self.get_table_oid(connection, table_name, schema, - info_cache=kw.get('info_cache')) + info_cache=kw.get('info_cache'), + postgresql_relkind=kw.get('postgresql_relkind')) SQL_COLS = """ SELECT a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod), @@ -2164,7 +2181,8 @@ class PGDialect(default.DefaultDialect): @reflection.cache def get_pk_constraint(self, connection, table_name, schema=None, **kw): table_oid = self.get_table_oid(connection, table_name, schema, - info_cache=kw.get('info_cache')) + info_cache=kw.get('info_cache'), + postgresql_relkind=kw.get('postgresql_relkind')) if self.server_version_info < (8, 4): PK_SQL = """ @@ -2214,7 +2232,8 @@ class PGDialect(default.DefaultDialect): postgresql_ignore_search_path=False, **kw): preparer = self.identifier_preparer table_oid = self.get_table_oid(connection, table_name, schema, - info_cache=kw.get('info_cache')) + info_cache=kw.get('info_cache'), + postgresql_relkind=kw.get('postgresql_relkind')) FK_SQL = """ SELECT r.conname, @@ -2318,11 +2337,11 @@ class PGDialect(default.DefaultDialect): @reflection.cache def get_indexes(self, connection, table_name, schema, **kw): table_oid = self.get_table_oid(connection, table_name, schema, - info_cache=kw.get('info_cache')) + info_cache=kw.get('info_cache'), + postgresql_relkind=kw.get('postgresql_relkind')) # cast indkey as varchar since it's an int2vector, # returned as a list by some drivers such as pypostgresql - IDX_SQL = """ SELECT i.relname as relname, @@ -2336,7 +2355,7 @@ class PGDialect(default.DefaultDialect): pg_attribute a on t.oid=a.attrelid and %s WHERE - t.relkind = 'r' + t.relkind IN ('r', 'v', 'f', 'm') and t.oid = :table_oid and ix.indisprimary = 'f' ORDER BY @@ -2391,7 +2410,8 @@ class PGDialect(default.DefaultDialect): def get_unique_constraints(self, connection, table_name, schema=None, **kw): table_oid = self.get_table_oid(connection, table_name, schema, - info_cache=kw.get('info_cache')) + info_cache=kw.get('info_cache'), + postgresql_relkind=kw.get('postgresql_relkind')) UNIQUE_SQL = """ SELECT -- cgit v1.2.1 From 4b26bf2e6f254d5cfc0998a9399a310c0f59944b Mon Sep 17 00:00:00 2001 From: Rodrigo Menezes Date: Thu, 14 Aug 2014 17:19:10 -0400 Subject: Add a view synonym too for consistency. --- lib/sqlalchemy/dialects/postgresql/base.py | 3 ++- 1 file changed, 2 insertions(+), 1 deletion(-) (limited to 'lib/sqlalchemy/dialects/postgresql/base.py') diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index 19d2c7ca4..893b4b3c1 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -1953,7 +1953,8 @@ class PGDialect(default.DefaultDialect): RELKIND_SYNONYMS = { 'materialized': 'm', - 'foreign': 'f' + 'foreign': 'f', + 'view': 'v' } ACCEPTED_RELKINDS = ('r','v','m','f') if postgresql_relkind is None: -- cgit v1.2.1 From 2f7dce1d6fa43e88f64c81b6e612fbc42235fddd Mon Sep 17 00:00:00 2001 From: Rodrigo Menezes Date: Tue, 26 Aug 2014 12:53:34 -0400 Subject: Removed all mentions to postgresql_relkind --- lib/sqlalchemy/dialects/postgresql/base.py | 38 +++++++----------------------- 1 file changed, 9 insertions(+), 29 deletions(-) (limited to 'lib/sqlalchemy/dialects/postgresql/base.py') diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index 893b4b3c1..155136c1d 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -1712,7 +1712,7 @@ class PGDialect(default.DefaultDialect): }) ] - reflection_options = ('postgresql_ignore_search_path', 'postgresql_relkind') + reflection_options = ('postgresql_ignore_search_path',) _backslash_escapes = True @@ -1937,7 +1937,7 @@ class PGDialect(default.DefaultDialect): return tuple([int(x) for x in m.group(1, 2, 3) if x is not None]) @reflection.cache - def get_table_oid(self, connection, table_name, schema=None, postgresql_relkind=None, **kw): + def get_table_oid(self, connection, table_name, schema=None, **kw): """Fetch the oid for schema.table_name. Several reflection methods require the table oid. The idea for using @@ -1951,28 +1951,13 @@ class PGDialect(default.DefaultDialect): else: schema_where_clause = "pg_catalog.pg_table_is_visible(c.oid)" - RELKIND_SYNONYMS = { - 'materialized': 'm', - 'foreign': 'f', - 'view': 'v' - } - ACCEPTED_RELKINDS = ('r','v','m','f') - if postgresql_relkind is None: - postgresql_relkind = 'r' - else: - postgresql_relkind = postgresql_relkind.lower() - if postgresql_relkind in RELKIND_SYNONYMS: - postgresql_relkind = RELKIND_SYNONYMS[postgresql_relkind.lower()] - if postgresql_relkind not in ACCEPTED_RELKINDS: - raise exc.SQLAlchemyError('Invalid postgresql_relkind: %s' % postgresql_relkind) - query = """ SELECT c.oid FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE (%s) - AND c.relname = :table_name AND c.relkind in ('%s', 'v') - """ % (schema_where_clause, postgresql_relkind) + AND c.relname = :table_name AND c.relkind in ('r', 'v', 'm', 'f') + """ % schema_where_clause # Since we're binding to unicode, table_name and schema_name must be # unicode. table_name = util.text_type(table_name) @@ -2069,8 +2054,7 @@ class PGDialect(default.DefaultDialect): def get_columns(self, connection, table_name, schema=None, **kw): table_oid = self.get_table_oid(connection, table_name, schema, - info_cache=kw.get('info_cache'), - postgresql_relkind=kw.get('postgresql_relkind')) + info_cache=kw.get('info_cache')) SQL_COLS = """ SELECT a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod), @@ -2224,8 +2208,7 @@ class PGDialect(default.DefaultDialect): @reflection.cache def get_pk_constraint(self, connection, table_name, schema=None, **kw): table_oid = self.get_table_oid(connection, table_name, schema, - info_cache=kw.get('info_cache'), - postgresql_relkind=kw.get('postgresql_relkind')) + info_cache=kw.get('info_cache')) if self.server_version_info < (8, 4): PK_SQL = """ @@ -2275,8 +2258,7 @@ class PGDialect(default.DefaultDialect): postgresql_ignore_search_path=False, **kw): preparer = self.identifier_preparer table_oid = self.get_table_oid(connection, table_name, schema, - info_cache=kw.get('info_cache'), - postgresql_relkind=kw.get('postgresql_relkind')) + info_cache=kw.get('info_cache')) FK_SQL = """ SELECT r.conname, @@ -2380,8 +2362,7 @@ class PGDialect(default.DefaultDialect): @reflection.cache def get_indexes(self, connection, table_name, schema, **kw): table_oid = self.get_table_oid(connection, table_name, schema, - info_cache=kw.get('info_cache'), - postgresql_relkind=kw.get('postgresql_relkind')) + info_cache=kw.get('info_cache')) # cast indkey as varchar since it's an int2vector, # returned as a list by some drivers such as pypostgresql @@ -2453,8 +2434,7 @@ class PGDialect(default.DefaultDialect): def get_unique_constraints(self, connection, table_name, schema=None, **kw): table_oid = self.get_table_oid(connection, table_name, schema, - info_cache=kw.get('info_cache'), - postgresql_relkind=kw.get('postgresql_relkind')) + info_cache=kw.get('info_cache')) UNIQUE_SQL = """ SELECT -- cgit v1.2.1 From bcf7a55da01633c4890502463a08cb96af9fe5e9 Mon Sep 17 00:00:00 2001 From: Rodrigo Menezes Date: Tue, 26 Aug 2014 12:56:54 -0400 Subject: Remove relkind from construct arguments. --- lib/sqlalchemy/dialects/postgresql/base.py | 3 +-- 1 file changed, 1 insertion(+), 2 deletions(-) (limited to 'lib/sqlalchemy/dialects/postgresql/base.py') diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index 155136c1d..75d0696ad 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -1707,8 +1707,7 @@ class PGDialect(default.DefaultDialect): "ops": {} }), (schema.Table, { - "ignore_search_path": False, - "relkind": None + "ignore_search_path": False }) ] -- cgit v1.2.1 From d39be884321d0afbae7ef3da556382b53fef8060 Mon Sep 17 00:00:00 2001 From: Rodrigo Menezes Date: Tue, 26 Aug 2014 13:02:19 -0400 Subject: Removed changes that are no longer necessary for postgresql_relkind. Also, removed newline changes. --- lib/sqlalchemy/dialects/postgresql/base.py | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'lib/sqlalchemy/dialects/postgresql/base.py') diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index 206a25d28..40b2f60ae 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -1781,7 +1781,7 @@ class PGDialect(default.DefaultDialect): }) ] - reflection_options = ('postgresql_ignore_search_path',) + reflection_options = ('postgresql_ignore_search_path', ) _backslash_escapes = True @@ -2019,7 +2019,6 @@ class PGDialect(default.DefaultDialect): schema_where_clause = "n.nspname = :schema" else: schema_where_clause = "pg_catalog.pg_table_is_visible(c.oid)" - query = """ SELECT c.oid FROM pg_catalog.pg_class c @@ -2435,6 +2434,7 @@ class PGDialect(default.DefaultDialect): # cast indkey as varchar since it's an int2vector, # returned as a list by some drivers such as pypostgresql + IDX_SQL = """ SELECT i.relname as relname, -- cgit v1.2.1 From fbd2d70a5cfd7b5c219c51cb5b7866c4ab89cece Mon Sep 17 00:00:00 2001 From: Rodrigo Menezes Date: Wed, 3 Sep 2014 16:38:43 -0400 Subject: Fixing some pep8s and adding get_foreign_tables. --- lib/sqlalchemy/dialects/postgresql/base.py | 22 ++++++++++++++++++++-- 1 file changed, 20 insertions(+), 2 deletions(-) (limited to 'lib/sqlalchemy/dialects/postgresql/base.py') diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index 40b2f60ae..69ae6cfed 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -2086,7 +2086,7 @@ class PGDialect(default.DefaultDialect): s = """ SELECT relname FROM pg_class c - WHERE relkind = 'v' + WHERE relkind IN ('m', v') AND '%(schema)s' = (select nspname from pg_namespace n where n.oid = c.relnamespace) """ % dict(schema=current_schema) @@ -2098,6 +2098,24 @@ class PGDialect(default.DefaultDialect): view_names = [row[0] for row in connection.execute(s)] return view_names + @reflection.cache + def get_foreign_table_names(self, connection, schema=None, **kw): + if schema is not None: + current_schema = schema + else: + current_schema = self.default_schema_name + + result = connection.execute( + sql.text("SELECT relname FROM pg_class c " + "WHERE relkind = 'f' " + "AND '%s' = (select nspname from pg_namespace n " + "where n.oid = c.relnamespace) " % + current_schema, + typemap={'relname': sqltypes.Unicode} + ) + ) + return [row[0] for row in result] + @reflection.cache def get_view_definition(self, connection, view_name, schema=None, **kw): if schema is not None: @@ -2434,7 +2452,7 @@ class PGDialect(default.DefaultDialect): # cast indkey as varchar since it's an int2vector, # returned as a list by some drivers such as pypostgresql - + IDX_SQL = """ SELECT i.relname as relname, -- cgit v1.2.1 From 619b0be0ce05c394613d8565c08c09cac10cdd88 Mon Sep 17 00:00:00 2001 From: Rodrigo Menezes Date: Fri, 5 Sep 2014 13:37:32 -0400 Subject: Added get_foreign_table_names to interface and put it in the test requirements. --- lib/sqlalchemy/dialects/postgresql/base.py | 1 + 1 file changed, 1 insertion(+) (limited to 'lib/sqlalchemy/dialects/postgresql/base.py') diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index 69ae6cfed..f65bc2473 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -1753,6 +1753,7 @@ class PGDialect(default.DefaultDialect): supports_default_values = True supports_empty_insert = False + supports_foreign_tables = True supports_multivalues_insert = True default_paramstyle = 'pyformat' ischema_names = ischema_names -- cgit v1.2.1 From fd2faa9bc2c6d2d1b0b8e1738f0bce21e2527bb0 Mon Sep 17 00:00:00 2001 From: Rodrigo Menezes Date: Fri, 5 Sep 2014 13:54:48 -0400 Subject: Added documentation. Changed my mind - added get_foreign_table_names() only to PGInspect and not in the Dialect. Added tests for PGInspect and removed a bunch of the old test scaffolding. --- lib/sqlalchemy/dialects/postgresql/base.py | 36 ++++++++++++++---------------- 1 file changed, 17 insertions(+), 19 deletions(-) (limited to 'lib/sqlalchemy/dialects/postgresql/base.py') diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index f65bc2473..63f6eb891 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -1679,6 +1679,23 @@ class PGInspector(reflection.Inspector): schema = schema or self.default_schema_name return self.dialect._load_enums(self.bind, schema) + def get_foreign_table_names(self, connection, schema=None, **kw): + if schema is not None: + current_schema = schema + else: + current_schema = self.default_schema_name + + result = connection.execute( + sql.text("SELECT relname FROM pg_class c " + "WHERE relkind = 'f' " + "AND '%s' = (select nspname from pg_namespace n " + "where n.oid = c.relnamespace) " % + current_schema, + typemap={'relname': sqltypes.Unicode} + ) + ) + return [row[0] for row in result] + class CreateEnumType(schema._CreateDropBase): __visit_name__ = "create_enum_type" @@ -1753,7 +1770,6 @@ class PGDialect(default.DefaultDialect): supports_default_values = True supports_empty_insert = False - supports_foreign_tables = True supports_multivalues_insert = True default_paramstyle = 'pyformat' ischema_names = ischema_names @@ -2099,24 +2115,6 @@ class PGDialect(default.DefaultDialect): view_names = [row[0] for row in connection.execute(s)] return view_names - @reflection.cache - def get_foreign_table_names(self, connection, schema=None, **kw): - if schema is not None: - current_schema = schema - else: - current_schema = self.default_schema_name - - result = connection.execute( - sql.text("SELECT relname FROM pg_class c " - "WHERE relkind = 'f' " - "AND '%s' = (select nspname from pg_namespace n " - "where n.oid = c.relnamespace) " % - current_schema, - typemap={'relname': sqltypes.Unicode} - ) - ) - return [row[0] for row in result] - @reflection.cache def get_view_definition(self, connection, view_name, schema=None, **kw): if schema is not None: -- cgit v1.2.1