From e3b409f6830ba2f1a627aab87a6f701218ba08c9 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Sun, 4 Feb 2007 23:45:45 +0000 Subject: [PATCH] - added "schema" argument to all has_table() calls, only supported so far by PG - added basic unit test for PG reflection of tables in an alternate schema --- lib/sqlalchemy/ansisql.py | 4 ++-- lib/sqlalchemy/databases/firebird.py | 2 +- lib/sqlalchemy/databases/mssql.py | 4 ++-- lib/sqlalchemy/databases/mysql.py | 2 +- lib/sqlalchemy/databases/oracle.py | 2 +- lib/sqlalchemy/databases/sqlite.py | 2 +- lib/sqlalchemy/engine/base.py | 6 +++--- lib/sqlalchemy/schema.py | 2 +- test/engine/reflection.py | 32 ++++++++++++++++++++++++++-- 9 files changed, 42 insertions(+), 14 deletions(-) diff --git a/lib/sqlalchemy/ansisql.py b/lib/sqlalchemy/ansisql.py index 63ee5f7fdc..974319845d 100644 --- a/lib/sqlalchemy/ansisql.py +++ b/lib/sqlalchemy/ansisql.py @@ -677,7 +677,7 @@ class ANSISchemaGenerator(ANSISchemaBase): raise NotImplementedError() def visit_metadata(self, metadata): - collection = [t for t in metadata.table_iterator(reverse=False, tables=self.tables) if (not self.checkfirst or not self.dialect.has_table(self.connection, t.name))] + collection = [t for t in metadata.table_iterator(reverse=False, tables=self.tables) if (not self.checkfirst or not self.dialect.has_table(self.connection, t.name, schema=t.schema))] for table in collection: table.accept_schema_visitor(self, traverse=False) if self.supports_alter(): @@ -807,7 +807,7 @@ class ANSISchemaDropper(ANSISchemaBase): self.dialect = self.engine.dialect def visit_metadata(self, metadata): - collection = [t for t in metadata.table_iterator(reverse=True, tables=self.tables) if (not self.checkfirst or self.dialect.has_table(self.connection, t.name))] + collection = [t for t in metadata.table_iterator(reverse=True, tables=self.tables) if (not self.checkfirst or self.dialect.has_table(self.connection, t.name, schema=t.schema))] if self.supports_alter(): for alterable in self.find_alterables(collection): self.drop_foreignkey(alterable) diff --git a/lib/sqlalchemy/databases/firebird.py b/lib/sqlalchemy/databases/firebird.py index 6af6d6d87c..5a25b12db3 100644 --- a/lib/sqlalchemy/databases/firebird.py +++ b/lib/sqlalchemy/databases/firebird.py @@ -167,7 +167,7 @@ class FBDialect(ansisql.ANSIDialect): def preparer(self): return FBIdentifierPreparer(self) - def has_table(self, connection, table_name): + def has_table(self, connection, table_name, schema=None): tblqry = """ SELECT count(*) FROM RDB$RELATIONS R diff --git a/lib/sqlalchemy/databases/mssql.py b/lib/sqlalchemy/databases/mssql.py index 6d351d0fb4..fbb89de1ab 100644 --- a/lib/sqlalchemy/databases/mssql.py +++ b/lib/sqlalchemy/databases/mssql.py @@ -345,10 +345,10 @@ class MSSQLDialect(ansisql.ANSIDialect): c.name = c.name.upper() return t - def has_table(self, connection, tablename): + def has_table(self, connection, tablename, schema=None): import sqlalchemy.databases.information_schema as ischema - current_schema = self.get_default_schema_name() + current_schema = schema or self.get_default_schema_name() columns = self.uppercase_table(ischema.columns) s = sql.select([columns], current_schema diff --git a/lib/sqlalchemy/databases/mysql.py b/lib/sqlalchemy/databases/mysql.py index c09d02756c..c6bf2695fd 100644 --- a/lib/sqlalchemy/databases/mysql.py +++ b/lib/sqlalchemy/databases/mysql.py @@ -335,7 +335,7 @@ class MySQLDialect(ansisql.ANSIDialect): def dbapi(self): return self.module - def has_table(self, connection, table_name): + def has_table(self, connection, table_name, schema=None): cursor = connection.execute("show table status like '" + table_name + "'") return bool( not not cursor.rowcount ) diff --git a/lib/sqlalchemy/databases/oracle.py b/lib/sqlalchemy/databases/oracle.py index 7221d56620..2db9095924 100644 --- a/lib/sqlalchemy/databases/oracle.py +++ b/lib/sqlalchemy/databases/oracle.py @@ -211,7 +211,7 @@ class OracleDialect(ansisql.ANSIDialect): return OracleDefaultRunner(engine, proxy) - def has_table(self, connection, table_name): + def has_table(self, connection, table_name, schema=None): cursor = connection.execute("""select table_name from all_tables where table_name=:name""", {'name':table_name.upper()}) return bool( cursor.fetchone() is not None ) diff --git a/lib/sqlalchemy/databases/sqlite.py b/lib/sqlalchemy/databases/sqlite.py index dda488f9c0..2ab3c0d5ad 100644 --- a/lib/sqlalchemy/databases/sqlite.py +++ b/lib/sqlalchemy/databases/sqlite.py @@ -171,7 +171,7 @@ class SQLiteDialect(ansisql.ANSIDialect): def dbapi(self): return sqlite - def has_table(self, connection, table_name): + def has_table(self, connection, table_name, schema=None): cursor = connection.execute("PRAGMA table_info(" + table_name + ")", {}) row = cursor.fetchone() diff --git a/lib/sqlalchemy/engine/base.py b/lib/sqlalchemy/engine/base.py index 69d9ef9556..8871cac1da 100644 --- a/lib/sqlalchemy/engine/base.py +++ b/lib/sqlalchemy/engine/base.py @@ -91,7 +91,7 @@ class Dialect(sql.AbstractDialect): def reflecttable(self, connection, table): """given an Connection and a Table object, reflects its columns and properties from the database.""" raise NotImplementedError() - def has_table(self, connection, table_name): + def has_table(self, connection, table_name, schema=None): raise NotImplementedError() def has_sequence(self, connection, sequence_name): raise NotImplementedError() @@ -516,8 +516,8 @@ class Engine(sql.Executor, Connectable): finally: if connection is None: conn.close() - def has_table(self, table_name): - return self.run_callable(lambda c: self.dialect.has_table(c, table_name)) + def has_table(self, table_name, schema=None): + return self.run_callable(lambda c: self.dialect.has_table(c, table_name, schema=schema)) def raw_connection(self): """returns a DBAPI connection.""" diff --git a/lib/sqlalchemy/schema.py b/lib/sqlalchemy/schema.py index ae712252ba..a2d328bf35 100644 --- a/lib/sqlalchemy/schema.py +++ b/lib/sqlalchemy/schema.py @@ -272,7 +272,7 @@ class Table(SchemaItem, sql.TableClause): def do(conn): e = conn.engine - return e.dialect.has_table(conn, self.name) + return e.dialect.has_table(conn, self.name, schema=self.schema) return connectable.run_callable(do) def create(self, connectable=None, checkfirst=False): diff --git a/test/engine/reflection.py b/test/engine/reflection.py index 3467c6ae42..c77e15e284 100644 --- a/test/engine/reflection.py +++ b/test/engine/reflection.py @@ -491,9 +491,37 @@ class SchemaTest(PersistTest): print buf assert buf.index("CREATE TABLE someschema.table1") > -1 assert buf.index("CREATE TABLE someschema.table2") > -1 - - + + @testbase.supported('postgres') + def testpg(self): + """note: this test requires that the 'test_schema' schema be separate and accessible by the test user""" + meta1 = BoundMetaData(testbase.db) + users = Table('users', meta1, + Column('user_id', Integer, primary_key = True), + Column('user_name', String(30), nullable = False), + schema="test_schema" + ) + + addresses = Table('email_addresses', meta1, + Column('address_id', Integer, primary_key = True), + Column('remote_user_id', Integer, ForeignKey(users.c.user_id)), + Column('email_address', String(20)), + schema="test_schema" + ) + meta1.create_all() + try: + meta2 = BoundMetaData(testbase.db) + users = Table('users', meta2, autoload = True, schema="test_schema") + addresses = Table('email_addresses', meta2, autoload = True, schema="test_schema") + + print users + print addresses + j = join(users, addresses) + print str(j.onclause) + self.assert_((users.c.user_id==addresses.c.remote_user_id).compare(j.onclause)) + finally: + meta1.drop_all() if __name__ == "__main__": testbase.main() -- 2.47.2