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