class ZXOracleDialect(JDBCDialect, default.DefaultDialect): """Details of the Oracle dialect. Not used directly in application code.""" max_identifier_length = 30 preexecute_pk_sequences = True supports_pk_autoincrement = False use_ansi = True _driver = 'oracle.jdbc.driver.OracleDriver' _jdbc_prefix= 'jdbc:oracle:thin' _colspecs = _oracle.colspecs _ischema_names = _oracle.ischema_names optimize_limits = False def _normalize_name(self, name): if name is None: return None elif name.upper() == name and not self.identifier_preparer._requires_quotes(name.lower().decode(self.encoding)): return name.lower().decode(self.encoding) else: return name.decode(self.encoding) def _denormalize_name(self, name): if name is None: return None elif name.lower() == name and not self.identifier_preparer._requires_quotes(name.lower()): return name.upper().encode(self.encoding) else: return name.encode(self.encoding) def table_names(self, connection, schema): # there doesn't seem to be a way to check tablespaces in DatabaseMetaData, # so we resort to nonportable code here if schema is None: s = "select table_name from all_tables where nvl(tablespace_name, 'no tablespace') NOT IN ('SYSTEM', 'SYSAUX')" cursor = connection.execute(s) else: s = "select table_name from all_tables where nvl(tablespace_name, 'no tablespace') NOT IN ('SYSTEM','SYSAUX') AND OWNER = ?" cursor = connection.execute(s, self._denormalize_name(schema)) return [self._normalize_name(row[0]) for row in cursor] def sequence_names(self, connection): cursor = connection.execute("""select sequence_name from all_sequences""") return [self._normalize_name(row[0]) for row in cursor.fetchall()] def has_sequence(self, connection, sequence_name): cursor = connection.execute("""select sequence_name from all_sequences where sequence_name=?""", self._denormalize_name(sequence_name)) return cursor.fetchone() is not None def get_default_schema_name(self, connection): return connection.execute('SELECT USER FROM DUAL').scalar() get_default_schema_name = base.connection_memoize( ('dialect', 'default_schema_name'))(get_default_schema_name) def oid_column_name(self, column): if not isinstance(column.table, (sql.TableClause, sql.Select)): return None else: return "rowid"
class OracleDialect(default.DefaultDialect): name = 'oracle' supports_alter = True supports_unicode_statements = False max_identifier_length = 30 supports_sane_rowcount = True supports_sane_multi_rowcount = False preexecute_pk_sequences = True supports_pk_autoincrement = False default_paramstyle = 'named' def __init__(self, use_ansi=True, auto_setinputsizes=True, auto_convert_lobs=True, threaded=True, allow_twophase=True, optimize_limits=False, arraysize=50, **kwargs): default.DefaultDialect.__init__(self, **kwargs) self.use_ansi = use_ansi self.threaded = threaded self.arraysize = arraysize self.allow_twophase = allow_twophase self.optimize_limits = optimize_limits self.supports_timestamp = self.dbapi is None or hasattr( self.dbapi, 'TIMESTAMP') self.auto_setinputsizes = auto_setinputsizes self.auto_convert_lobs = auto_convert_lobs if self.dbapi is None or not self.auto_convert_lobs or not 'CLOB' in self.dbapi.__dict__: self.dbapi_type_map = {} self.ORACLE_BINARY_TYPES = [] else: # only use this for LOB objects. using it for strings, dates # etc. leads to a little too much magic, reflection doesn't know if it should # expect encoded strings or unicodes, etc. self.dbapi_type_map = { self.dbapi.CLOB: OracleText(), self.dbapi.BLOB: OracleBinary(), self.dbapi.BINARY: OracleRaw(), } self.ORACLE_BINARY_TYPES = [ getattr(self.dbapi, k) for k in ["BFILE", "CLOB", "NCLOB", "BLOB"] if hasattr(self.dbapi, k) ] def dbapi(cls): import cx_Oracle return cx_Oracle dbapi = classmethod(dbapi) def create_connect_args(self, url): dialect_opts = dict(url.query) for opt in ('use_ansi', 'auto_setinputsizes', 'auto_convert_lobs', 'threaded', 'allow_twophase'): if opt in dialect_opts: util.coerce_kw_type(dialect_opts, opt, bool) setattr(self, opt, dialect_opts[opt]) if url.database: # if we have a database, then we have a remote host port = url.port if port: port = int(port) else: port = 1521 dsn = self.dbapi.makedsn(url.host, port, url.database) else: # we have a local tnsname dsn = url.host opts = dict( user=url.username, password=url.password, dsn=dsn, threaded=self.threaded, twophase=self.allow_twophase, ) if 'mode' in url.query: opts['mode'] = url.query['mode'] if isinstance(opts['mode'], basestring): mode = opts['mode'].upper() if mode == 'SYSDBA': opts['mode'] = self.dbapi.SYSDBA elif mode == 'SYSOPER': opts['mode'] = self.dbapi.SYSOPER else: util.coerce_kw_type(opts, 'mode', int) # Can't set 'handle' or 'pool' via URL query args, use connect_args return ([], opts) def is_disconnect(self, e): if isinstance(e, self.dbapi.InterfaceError): return "not connected" in str(e) else: return "ORA-03114" in str(e) or "ORA-03113" in str(e) def type_descriptor(self, typeobj): return sqltypes.adapt_type(typeobj, colspecs) def create_xid(self): """create a two-phase transaction ID. this id will be passed to do_begin_twophase(), do_rollback_twophase(), do_commit_twophase(). its format is unspecified.""" id = random.randint(0, 2**128) return (0x1234, "%032x" % id, "%032x" % 9) def do_release_savepoint(self, connection, name): # Oracle does not support RELEASE SAVEPOINT pass def do_begin_twophase(self, connection, xid): connection.connection.begin(*xid) def do_prepare_twophase(self, connection, xid): connection.connection.prepare() def do_rollback_twophase(self, connection, xid, is_prepared=True, recover=False): self.do_rollback(connection.connection) def do_commit_twophase(self, connection, xid, is_prepared=True, recover=False): self.do_commit(connection.connection) def do_recover_twophase(self, connection): pass def has_table(self, connection, table_name, schema=None): if not schema: schema = self.get_default_schema_name(connection) cursor = connection.execute( """select table_name from all_tables where table_name=:name and owner=:schema_name""", { 'name': self._denormalize_name(table_name), 'schema_name': self._denormalize_name(schema) }) return cursor.fetchone() is not None def has_sequence(self, connection, sequence_name, schema=None): if not schema: schema = self.get_default_schema_name(connection) cursor = connection.execute( """select sequence_name from all_sequences where sequence_name=:name and sequence_owner=:schema_name""", { 'name': self._denormalize_name(sequence_name), 'schema_name': self._denormalize_name(schema) }) return cursor.fetchone() is not None def _normalize_name(self, name): if name is None: return None elif name.upper( ) == name and not self.identifier_preparer._requires_quotes( name.lower().decode(self.encoding)): return name.lower().decode(self.encoding) else: return name.decode(self.encoding) def _denormalize_name(self, name): if name is None: return None elif name.lower( ) == name and not self.identifier_preparer._requires_quotes( name.lower()): return name.upper().encode(self.encoding) else: return name.encode(self.encoding) def get_default_schema_name(self, connection): return self._normalize_name( connection.execute('SELECT USER FROM DUAL').scalar()) get_default_schema_name = base.connection_memoize( ('dialect', 'default_schema_name'))(get_default_schema_name) def table_names(self, connection, schema): # note that table_names() isnt loading DBLINKed or synonym'ed tables if schema is None: s = "select table_name from all_tables where nvl(tablespace_name, 'no tablespace') NOT IN ('SYSTEM', 'SYSAUX')" cursor = connection.execute(s) else: s = "select table_name from all_tables where nvl(tablespace_name, 'no tablespace') NOT IN ('SYSTEM','SYSAUX') AND OWNER = :owner" cursor = connection.execute( s, {'owner': self._denormalize_name(schema)}) return [self._normalize_name(row[0]) for row in cursor] def _resolve_synonym(self, connection, desired_owner=None, desired_synonym=None, desired_table=None): """search for a local synonym matching the given desired owner/name. if desired_owner is None, attempts to locate a distinct owner. returns the actual name, owner, dblink name, and synonym name if found. """ sql = """select OWNER, TABLE_OWNER, TABLE_NAME, DB_LINK, SYNONYM_NAME from ALL_SYNONYMS WHERE """ clauses = [] params = {} if desired_synonym: clauses.append("SYNONYM_NAME=:synonym_name") params['synonym_name'] = desired_synonym if desired_owner: clauses.append("TABLE_OWNER=:desired_owner") params['desired_owner'] = desired_owner if desired_table: clauses.append("TABLE_NAME=:tname") params['tname'] = desired_table sql += " AND ".join(clauses) result = connection.execute(sql, **params) if desired_owner: row = result.fetchone() if row: return row['TABLE_NAME'], row['TABLE_OWNER'], row[ 'DB_LINK'], row['SYNONYM_NAME'] else: return None, None, None, None else: rows = result.fetchall() if len(rows) > 1: raise AssertionError( "There are multiple tables visible to the schema, you must specify owner" ) elif len(rows) == 1: row = rows[0] return row['TABLE_NAME'], row['TABLE_OWNER'], row[ 'DB_LINK'], row['SYNONYM_NAME'] else: return None, None, None, None def reflecttable(self, connection, table, include_columns): preparer = self.identifier_preparer resolve_synonyms = table.kwargs.get('oracle_resolve_synonyms', False) if resolve_synonyms: actual_name, owner, dblink, synonym = self._resolve_synonym( connection, desired_owner=self._denormalize_name(table.schema), desired_synonym=self._denormalize_name(table.name)) else: actual_name, owner, dblink, synonym = None, None, None, None if not actual_name: actual_name = self._denormalize_name(table.name) if not dblink: dblink = '' if not owner: owner = self._denormalize_name( table.schema or self.get_default_schema_name(connection)) c = connection.execute( "select COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE, DATA_DEFAULT from ALL_TAB_COLUMNS%(dblink)s where TABLE_NAME = :table_name and OWNER = :owner" % {'dblink': dblink}, { 'table_name': actual_name, 'owner': owner }) while True: row = c.fetchone() if row is None: break (colname, coltype, length, precision, scale, nullable, default) = (self._normalize_name(row[0]), row[1], row[2], row[3], row[4], row[5] == 'Y', row[6]) if include_columns and colname not in include_columns: continue # INTEGER if the scale is 0 and precision is null # NUMBER if the scale and precision are both null # NUMBER(9,2) if the precision is 9 and the scale is 2 # NUMBER(3) if the precision is 3 and scale is 0 #length is ignored except for CHAR and VARCHAR2 if coltype == 'NUMBER': if precision is None and scale is None: coltype = OracleNumeric elif precision is None and scale == 0: coltype = OracleInteger else: coltype = OracleNumeric(precision, scale) elif coltype == 'CHAR' or coltype == 'VARCHAR2': coltype = ischema_names.get(coltype, OracleString)(length) else: coltype = re.sub(r'\(\d+\)', '', coltype) try: coltype = ischema_names[coltype] except KeyError: util.warn("Did not recognize type '%s' of column '%s'" % (coltype, colname)) coltype = sqltypes.NULLTYPE colargs = [] if default is not None: colargs.append(schema.DefaultClause(sql.text(default))) table.append_column( schema.Column(colname, coltype, nullable=nullable, *colargs)) if not table.columns: raise AssertionError( "Couldn't find any column information for table %s" % actual_name) c = connection.execute( """SELECT ac.constraint_name, ac.constraint_type, loc.column_name AS local_column, rem.table_name AS remote_table, rem.column_name AS remote_column, rem.owner AS remote_owner FROM all_constraints%(dblink)s ac, all_cons_columns%(dblink)s loc, all_cons_columns%(dblink)s rem WHERE ac.table_name = :table_name AND ac.constraint_type IN ('R','P') AND ac.owner = :owner AND ac.owner = loc.owner AND ac.constraint_name = loc.constraint_name AND ac.r_owner = rem.owner(+) AND ac.r_constraint_name = rem.constraint_name(+) -- order multiple primary keys correctly ORDER BY ac.constraint_name, loc.position, rem.position""" % {'dblink': dblink}, { 'table_name': actual_name, 'owner': owner }) fks = {} while True: row = c.fetchone() if row is None: break #print "ROW:" , row (cons_name, cons_type, local_column, remote_table, remote_column, remote_owner) = row[0:2] + tuple( [self._normalize_name(x) for x in row[2:]]) if cons_type == 'P': table.primary_key.add(table.c[local_column]) elif cons_type == 'R': try: fk = fks[cons_name] except KeyError: fk = ([], []) fks[cons_name] = fk if remote_table is None: # ticket 363 util.warn( ("Got 'None' querying 'table_name' from " "all_cons_columns%(dblink)s - does the user have " "proper rights to the table?") % {'dblink': dblink}) continue if resolve_synonyms: ref_remote_name, ref_remote_owner, ref_dblink, ref_synonym = self._resolve_synonym( connection, desired_owner=self._denormalize_name(remote_owner), desired_table=self._denormalize_name(remote_table)) if ref_synonym: remote_table = self._normalize_name(ref_synonym) remote_owner = self._normalize_name(ref_remote_owner) if not table.schema and self._denormalize_name( remote_owner) == owner: refspec = ".".join([remote_table, remote_column]) t = schema.Table(remote_table, table.metadata, autoload=True, autoload_with=connection, oracle_resolve_synonyms=resolve_synonyms, useexisting=True) else: refspec = ".".join([ x for x in [remote_owner, remote_table, remote_column] if x ]) t = schema.Table(remote_table, table.metadata, autoload=True, autoload_with=connection, schema=remote_owner, oracle_resolve_synonyms=resolve_synonyms, useexisting=True) if local_column not in fk[0]: fk[0].append(local_column) if refspec not in fk[1]: fk[1].append(refspec) for name, value in fks.iteritems(): table.append_constraint( schema.ForeignKeyConstraint(value[0], value[1], name=name, link_to_name=True))
class PGDialect(default.DefaultDialect): name = 'postgres' supports_alter = True supports_unicode_statements = False max_identifier_length = 63 supports_sane_rowcount = True supports_sane_multi_rowcount = False preexecute_pk_sequences = True supports_pk_autoincrement = False default_paramstyle = 'pyformat' supports_default_values = True supports_empty_insert = False def __init__(self, server_side_cursors=False, **kwargs): default.DefaultDialect.__init__(self, **kwargs) self.server_side_cursors = server_side_cursors def dbapi(cls): import psycopg2 as psycopg return psycopg dbapi = classmethod(dbapi) def create_connect_args(self, url): opts = url.translate_connect_args(username='******') if 'port' in opts: opts['port'] = int(opts['port']) opts.update(url.query) return ([], opts) def type_descriptor(self, typeobj): return sqltypes.adapt_type(typeobj, colspecs) def do_begin_twophase(self, connection, xid): self.do_begin(connection.connection) def do_prepare_twophase(self, connection, xid): connection.execute( sql.text("PREPARE TRANSACTION :tid", bindparams=[sql.bindparam('tid', xid)])) def do_rollback_twophase(self, connection, xid, is_prepared=True, recover=False): if is_prepared: if recover: #FIXME: ugly hack to get out of transaction context when commiting recoverable transactions # Must find out a way how to make the dbapi not open a transaction. connection.execute(sql.text("ROLLBACK")) connection.execute( sql.text("ROLLBACK PREPARED :tid", bindparams=[sql.bindparam('tid', xid)])) connection.execute(sql.text("BEGIN")) self.do_rollback(connection.connection) else: self.do_rollback(connection.connection) def do_commit_twophase(self, connection, xid, is_prepared=True, recover=False): if is_prepared: if recover: connection.execute(sql.text("ROLLBACK")) connection.execute( sql.text("COMMIT PREPARED :tid", bindparams=[sql.bindparam('tid', xid)])) connection.execute(sql.text("BEGIN")) self.do_rollback(connection.connection) else: self.do_commit(connection.connection) def do_recover_twophase(self, connection): resultset = connection.execute( sql.text("SELECT gid FROM pg_prepared_xacts")) return [row[0] for row in resultset] def get_default_schema_name(self, connection): return connection.scalar("select current_schema()", None) get_default_schema_name = base.connection_memoize( ('dialect', 'default_schema_name'))(get_default_schema_name) def last_inserted_ids(self): if self.context.last_inserted_ids is None: raise exc.InvalidRequestError( "no INSERT executed, or can't use cursor.lastrowid without Postgres OIDs enabled" ) else: return self.context.last_inserted_ids def has_table(self, connection, table_name, schema=None): # seems like case gets folded in pg_class... if schema is None: cursor = connection.execute( """select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where n.nspname=current_schema() and lower(relname)=%(name)s""", {'name': table_name.lower().encode(self.encoding)}) else: cursor = connection.execute( """select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where n.nspname=%(schema)s and lower(relname)=%(name)s""", { 'name': table_name.lower().encode(self.encoding), 'schema': schema }) return bool(not not cursor.rowcount) def has_sequence(self, connection, sequence_name): cursor = connection.execute( '''SELECT relname FROM pg_class WHERE relkind = 'S' AND relnamespace IN ( SELECT oid FROM pg_namespace WHERE nspname NOT LIKE 'pg_%%' AND nspname != 'information_schema' AND relname = %(seqname)s);''', {'seqname': sequence_name.encode(self.encoding)}) return bool(not not cursor.rowcount) def is_disconnect(self, e): if isinstance(e, self.dbapi.OperationalError): return 'closed the connection' in str( e) or 'connection not open' in str(e) elif isinstance(e, self.dbapi.InterfaceError): return 'connection already closed' in str( e) or 'cursor already closed' in str(e) elif isinstance(e, self.dbapi.ProgrammingError): # yes, it really says "losed", not "closed" return "losed the connection unexpectedly" in str(e) else: return False def table_names(self, connection, schema): s = """ SELECT relname FROM pg_class c WHERE relkind = 'r' AND '%(schema)s' = (select nspname from pg_namespace n where n.oid = c.relnamespace) """ % locals() return [row[0].decode(self.encoding) for row in connection.execute(s)] def server_version_info(self, connection): v = connection.execute("select version()").scalar() m = re.match('PostgreSQL (\d+)\.(\d+)\.(\d+)', v) if not m: raise AssertionError( "Could not determine version from string '%s'" % v) return tuple([int(x) for x in m.group(1, 2, 3)]) def reflecttable(self, connection, table, include_columns): preparer = self.identifier_preparer if table.schema is not None: schema_where_clause = "n.nspname = :schema" schemaname = table.schema if isinstance(schemaname, str): schemaname = schemaname.decode(self.encoding) else: schema_where_clause = "pg_catalog.pg_table_is_visible(c.oid)" schemaname = None SQL_COLS = """ SELECT a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod), (SELECT substring(d.adsrc for 128) FROM pg_catalog.pg_attrdef d WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) AS DEFAULT, a.attnotnull, a.attnum, a.attrelid as table_oid FROM pg_catalog.pg_attribute a WHERE a.attrelid = ( 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') ) AND a.attnum > 0 AND NOT a.attisdropped ORDER BY a.attnum """ % schema_where_clause s = sql.text(SQL_COLS, bindparams=[ sql.bindparam('table_name', type_=sqltypes.Unicode), sql.bindparam('schema', type_=sqltypes.Unicode) ], typemap={ 'attname': sqltypes.Unicode, 'default': sqltypes.Unicode }) tablename = table.name if isinstance(tablename, str): tablename = tablename.decode(self.encoding) c = connection.execute(s, table_name=tablename, schema=schemaname) rows = c.fetchall() if not rows: raise exc.NoSuchTableError(table.name) domains = self._load_domains(connection) for name, format_type, default, notnull, attnum, table_oid in rows: if include_columns and name not in include_columns: continue ## strip (30) from character varying(30) attype = re.search('([^\([]+)', format_type).group(1) nullable = not notnull is_array = format_type.endswith('[]') try: charlen = re.search('\(([\d,]+)\)', format_type).group(1) except: charlen = False numericprec = False numericscale = False if attype == 'numeric': if charlen is False: numericprec, numericscale = (None, None) else: numericprec, numericscale = charlen.split(',') charlen = False if attype == 'double precision': numericprec, numericscale = (53, False) charlen = False if attype == 'integer': numericprec, numericscale = (32, 0) charlen = False args = [] for a in (charlen, numericprec, numericscale): if a is None: args.append(None) elif a is not False: args.append(int(a)) kwargs = {} if attype == 'timestamp with time zone': kwargs['timezone'] = True elif attype == 'timestamp without time zone': kwargs['timezone'] = False if attype in ischema_names: coltype = ischema_names[attype] else: if attype in domains: domain = domains[attype] if domain['attype'] in ischema_names: # A table can't override whether the domain is nullable. nullable = domain['nullable'] if domain['default'] and not default: # It can, however, override the default value, but can't set it to null. default = domain['default'] coltype = ischema_names[domain['attype']] else: coltype = None if coltype: coltype = coltype(*args, **kwargs) if is_array: coltype = PGArray(coltype) else: util.warn("Did not recognize type '%s' of column '%s'" % (attype, name)) coltype = sqltypes.NULLTYPE colargs = [] if default is not None: match = re.search(r"""(nextval\(')([^']+)('.*$)""", default) if match is not None: # the default is related to a Sequence sch = table.schema if '.' not in match.group(2) and sch is not None: # unconditionally quote the schema name. this could # later be enhanced to obey quoting rules / "quote schema" default = match.group(1) + ( '"%s"' % sch) + '.' + match.group(2) + match.group(3) colargs.append(schema.DefaultClause(sql.text(default))) table.append_column( schema.Column(name, coltype, nullable=nullable, *colargs)) # Primary keys PK_SQL = """ SELECT attname FROM pg_attribute WHERE attrelid = ( SELECT indexrelid FROM pg_index i WHERE i.indrelid = :table AND i.indisprimary = 't') ORDER BY attnum """ t = sql.text(PK_SQL, typemap={'attname': sqltypes.Unicode}) c = connection.execute(t, table=table_oid) for row in c.fetchall(): pk = row[0] if pk in table.c: col = table.c[pk] table.primary_key.add(col) if col.default is None: col.autoincrement = False # Foreign keys FK_SQL = """ SELECT conname, pg_catalog.pg_get_constraintdef(oid, true) as condef FROM pg_catalog.pg_constraint r WHERE r.conrelid = :table AND r.contype = 'f' ORDER BY 1 """ t = sql.text(FK_SQL, typemap={ 'conname': sqltypes.Unicode, 'condef': sqltypes.Unicode }) c = connection.execute(t, table=table_oid) for conname, condef in c.fetchall(): m = re.search( 'FOREIGN KEY \((.*?)\) REFERENCES (?:(.*?)\.)?(.*?)\((.*?)\)', condef).groups() (constrained_columns, referred_schema, referred_table, referred_columns) = m constrained_columns = [ preparer._unquote_identifier(x) for x in re.split(r'\s*,\s*', constrained_columns) ] if referred_schema: referred_schema = preparer._unquote_identifier(referred_schema) elif table.schema is not None and table.schema == self.get_default_schema_name( connection): # no schema (i.e. its the default schema), and the table we're # reflecting has the default schema explicit, then use that. # i.e. try to use the user's conventions referred_schema = table.schema referred_table = preparer._unquote_identifier(referred_table) referred_columns = [ preparer._unquote_identifier(x) for x in re.split(r'\s*,\s', referred_columns) ] refspec = [] if referred_schema is not None: schema.Table(referred_table, table.metadata, autoload=True, schema=referred_schema, autoload_with=connection) for column in referred_columns: refspec.append(".".join( [referred_schema, referred_table, column])) else: schema.Table(referred_table, table.metadata, autoload=True, autoload_with=connection) for column in referred_columns: refspec.append(".".join([referred_table, column])) table.append_constraint( schema.ForeignKeyConstraint(constrained_columns, refspec, conname, link_to_name=True)) # Indexes IDX_SQL = """ SELECT c.relname, i.indisunique, i.indexprs, i.indpred, a.attname FROM pg_index i, pg_class c, pg_attribute a WHERE i.indrelid = :table AND i.indexrelid = c.oid AND a.attrelid = i.indexrelid AND i.indisprimary = 'f' ORDER BY c.relname, a.attnum """ t = sql.text(IDX_SQL, typemap={'attname': sqltypes.Unicode}) c = connection.execute(t, table=table_oid) indexes = {} sv_idx_name = None for row in c.fetchall(): idx_name, unique, expr, prd, col = row if expr and not idx_name == sv_idx_name: util.warn( "Skipped unsupported reflection of expression-based index %s" % idx_name) sv_idx_name = idx_name continue if prd and not idx_name == sv_idx_name: util.warn( "Predicate of partial index %s ignored during reflection" % idx_name) sv_idx_name = idx_name if not indexes.has_key(idx_name): indexes[idx_name] = [unique, []] indexes[idx_name][1].append(col) for name, (unique, columns) in indexes.items(): schema.Index(name, *[table.columns[c] for c in columns], **dict(unique=unique)) def _load_domains(self, connection): ## Load data types for domains: SQL_DOMAINS = """ SELECT t.typname as "name", pg_catalog.format_type(t.typbasetype, t.typtypmod) as "attype", not t.typnotnull as "nullable", t.typdefault as "default", pg_catalog.pg_type_is_visible(t.oid) as "visible", n.nspname as "schema" FROM pg_catalog.pg_type t LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace LEFT JOIN pg_catalog.pg_constraint r ON t.oid = r.contypid WHERE t.typtype = 'd' """ s = sql.text(SQL_DOMAINS, typemap={'attname': sqltypes.Unicode}) c = connection.execute(s) domains = {} for domain in c.fetchall(): ## strip (30) from character varying(30) attype = re.search('([^\(]+)', domain['attype']).group(1) if domain['visible']: # 'visible' just means whether or not the domain is in a # schema that's on the search path -- or not overriden by # a schema with higher presedence. If it's not visible, # it will be prefixed with the schema-name when it's used. name = domain['name'] else: name = "%s.%s" % (domain['schema'], domain['name']) domains[name] = { 'attype': attype, 'nullable': domain['nullable'], 'default': domain['default'] } return domains