def from_db (class_, schema, into_database, index_names=None):
index_filter = db.filter_clause('index_name', index_names)
into_database.log.debug("Querying for indexes {} from DB...".format(
"(all)" if index_names is None else ", ".join(index_names)))
rs = db.query(""" SELECT index_name
, index_type
, uniqueness
, tablespace_name
, status
, partitioned
, table_owner
, table_name
, ( SELECT CAST(COLLECT(column_name ORDER BY dic.column_position) AS gt_string_table)
FROM dba_ind_columns dic
WHERE dic.index_owner = di.owner
AND dic.index_name = di.index_name
) AS columns
FROM dba_indexes di
WHERE owner = :o
{}
""".format(index_filter), o=schema,
oracle_names=['tablespace_name', 'table_owner', 'table_name',
'index_name', 'columns'])
into_database.log.debug("Cursor obtained")
for row in rs:
index_name = OracleFQN(schema,
OracleIdentifier(row['index_name'], trust_me=True,
generated=(row['generated'] == 'Y')))
into_database.log.debug("Processing index {}".format(index_name))
index_type = row['index_type']
if index_type == 'IOT - TOP':
into_database.log.debug(
"Index {} is for an index-organized table. Skipping..."
.format(index_name))
continue
if index_type.find('NORMAL') == -1:
into_database.log.debug(
"Index {} is an unsupported type {}. Skipping...".format(index_name,
index_type))
continue
#raise UnimplementedFeatureError(
#"Index {} has unsupported type {}".format(index_name, index_type))
from precog.objects.column import Column
columns = [into_database.find(OracleFQN(row['table_owner'], row['table_name'], column_name), Column)
for column_name in row['columns']]
# An index without columns is hardly an index at all!
if not columns:
into_database.log.debug(
"Index {} has no columns. Index skipped.".format(index_name))
continue
yield class_(index_name, columns=columns, index_type=index_type,
uniqueness=row['uniqueness'], status=row['status'],
tablespace_name=row['tablespace_name'],
partitioned=row['partitioned'],
database=into_database, create_location=(db.location,))
rs.close()
def from_db (class_, schema, into_database, synonym_names=None):
synonym_filter = db.filter_clause('synonym_name', synonym_names)
rs = db.query(""" SELECT synonym_name
, table_owner
, table_name
FROM dba_synonyms
WHERE owner = :o
{}
""".format(synonym_filter), o=schema,
oracle_names=['synonym_name', 'table_owner', 'table_name'])
for row in rs:
yield class_(OracleFQN(schema, row['synonym_name']),
for_name=OracleFQN(row['table_owner'], row['table_name']),
database=into_database, create_location=(db.location,))
rs.close()
def from_db (class_, schema, into_database, synonym_names=None):
synonym_filter = db.filter_clause('synonym_name', synonym_names)
into_database.log.debug("Querying for synonyms {} from DB...".format(
"(all)" if synonym_names is None else ", ".join(synonym_names)))
rs = db.query(""" SELECT synonym_name
, table_owner
, table_name
FROM dba_synonyms
WHERE owner = :o
{}
""".format(synonym_filter), o=schema,
oracle_names=['synonym_name', 'table_owner', 'table_name'])
into_database.log.debug("Cursor obtained")
for row in rs:
into_database.log.debug("Processing synonym {}".format(row['synonym']))
yield class_(OracleFQN(schema, row['synonym_name']),
for_name=OracleFQN(row['table_owner'], row['table_name']),
database=into_database, create_location=(db.location,))
rs.close()
def from_db (class_, schema, into_database, sequence_names=None):
sequence_filter = db.filter_clause('sequence_name', sequence_names)
rs = db.query(""" SELECT sequence_name
, min_value
, max_value
, increment_by
, cycle_flag
, order_flag
, cache_size
FROM dba_sequences
WHERE sequence_owner = :o
{}
""".format(sequence_filter), o=schema,
oracle_names=['sequence_name', 'table_owner', 'table_name'])
for row in rs:
yield class_(OracleFQN(schema, row.pop('sequence_name')),
database=into_database, create_location=(db.location,),
**row)
rs.close()
def from_db (class_, schema, into_database, sequence_names=None):
sequence_filter = db.filter_clause('sequence_name', sequence_names)
into_database.log.debug("Querying for sequences {} from DB...".format(
"(all)" if sequence_names is None else ", ".join(sequence_names)))
rs = db.query(""" SELECT sequence_name
, min_value
, max_value
, increment_by
, cycle_flag
, order_flag
, cache_size
FROM dba_sequences
WHERE sequence_owner = :o
{}
""".format(sequence_filter), o=schema,
oracle_names=['sequence_name', 'table_owner', 'table_name'])
into_database.log.debug("Cursor obtained")
for row in rs:
into_database.log.debug("Processing sequence {}".format(row['sequence_name']))
yield class_(OracleFQN(schema, row.pop('sequence_name')),
database=into_database, create_location=(db.location,),
**row)
rs.close()
def from_db (class_, schema, into_database, plsql_names=None):
plsql_filter = db.filter_clause("object_type || '.' || object_name",
plsql_names)
rs = db.query(""" SELECT do.object_name
, do.object_type
, do.status
, CURSOR(SELECT ds.text
FROM dba_source ds
WHERE ds.owner = do.owner
AND ds.name = do.object_name
AND ds.type = do.object_type
ORDER BY ds.line
) AS text
FROM dba_objects do
WHERE do.owner = :o
AND do.object_type IN ( 'FUNCTION'
, 'PACKAGE'
, 'PACKAGE BODY'
, 'PROCEDURE'
, 'TRIGGER'
, 'TYPE'
, 'TYPE BODY'
)
-- Ignore secretly generated types for PL/SQL types
AND do.object_name NOT LIKE 'SYS_PLSQL%'
{}
""".format(plsql_filter), o=schema,
oracle_names=['object_name'])
for row in rs:
plsql_name = OracleFQN(schema, row['object_name'])
yield _type_to_class(row['object_type'], plsql_name)(
plsql_name, source=''.join(line['text'] for line in row['text']),
database=into_database, create_location=(db.location,),
status=row['status'])
rs.close()
def from_db (class_, schema, into_database, table_names=None):
table_filter = db.filter_clause('table_name', table_names)
into_database.log.debug("Querying for columns {} from DB...".format(
"(all)" if table_names is None else "in tables {}".format(", ".join(table_names))))
rs = db.query(""" SELECT table_name
, column_name
, qualified_col_name
, data_type
, CASE WHEN data_type_owner = 'PUBLIC'
OR data_type_owner LIKE '%SYS' THEN NULL
ELSE data_type_owner
END AS data_type_owner
, data_length
, data_precision
, data_scale
, data_default
, char_length
, char_used
, nullable
, virtual_column
, hidden_column
, internal_column_id
, ( SELECT CAST(COLLECT(constraint_name) AS gt_string_table)
FROM (
SELECT constraint_name
FROM dba_cons_columns dcc
WHERE dcc.owner = dtc.owner
AND dcc.table_name = dtc.table_name
GROUP BY constraint_name
HAVING COUNT(*) = 1
AND MAX(column_name) = dtc.column_name
)
) AS constraints
FROM dba_tab_cols dtc
, dba_objects do
WHERE dtc.owner = :o
-- Ignore columns on tables in the recyclebin
AND NOT (LENGTH(table_name) = 30
AND table_name LIKE 'BIN$%')
AND do.owner = dtc.owner
AND do.object_name = dtc.table_name
AND do.object_type = 'TABLE'
{}
""".format(table_filter), o=schema,
oracle_names=['table_name', 'column_name',
'qualified_col_name', 'constraints',
'data_type_owner', 'data_type'])
into_database.log.debug("Cursor obtained")
for row in rs:
into_database.log.debug("Grabbing a column row...")
(_, table_name), (_, col_name), *props, (_, constraints) = row.items()
props = dict(props)
generated = props['hidden_column'] == 'YES'
if col_name == props['qualified_col_name']:
props['qualified_col_name']._generated = generated
col_name._generated = generated
column_name = OracleFQN(schema, table_name, col_name)
into_database.log.debug("Processing column {}".format(column_name))
if props['data_type_owner']:
props['user_type'] = into_database.find(
OracleFQN(props['data_type_owner'], props['data_type']), Type)
del props['data_type_owner']
elif props['data_type']:
# Remove quotes that may be on built-in types
props['data_type'] = props['data_type'].strip('"')
constraints = {
into_database.find(OracleFQN(schema, constraint_name), Constraint)
for constraint_name in constraints}
yield class_(column_name, constraints=constraints,
database=into_database, create_location=(db.location,),
**props)
rs.close()
def from_db (class_, schema, into_database, plsql_names=None):
plsql_filter = db.filter_clause("object_type || '.' || object_name",
plsql_names)
into_database.log.debug("Querying for plsql {} from DB...".format(
"(all)" if plsql_names is None else ", ".join(plsql_names)))
rs = db.query(""" SELECT do.object_name
, do.object_type
, do.status
, CURSOR(SELECT ds.text
FROM dba_source ds
WHERE ds.owner = do.owner
AND ds.name = do.object_name
AND ds.type = do.object_type
ORDER BY ds.line
) AS text
FROM dba_objects do
WHERE do.owner = :o
AND do.object_type IN ( 'FUNCTION'
, 'PACKAGE'
, 'PACKAGE BODY'
, 'PROCEDURE'
, 'TRIGGER'
, 'TYPE'
, 'TYPE BODY'
)
-- Ignore secretly generated types for PL/SQL types
AND do.object_name NOT LIKE 'SYS_PLSQL%' -- PLSQL function generated types
AND do.object_name NOT LIKE 'SYSTPS%==' -- Collection types generated by COLLECT()
{}
""".format(plsql_filter), o=schema,
oracle_names=['object_name'])
into_database.log.debug("Cursor obtained")
for row in rs:
plsql_name = OracleFQN(schema, row['object_name'])
into_database.log.debug("Processing plsql {}".format(plsql_name))
yield _type_to_class(row['object_type'], plsql_name)(
plsql_name, source=''.join(line['text'] for line in row['text']),
database=into_database, create_location=(db.location,),
status=row['status'])
filter_clause = ''
if plsql_names:
dep_filter = db.filter_clause("type || '.' || name", plsql_names, logical_connective="AND (")
ref_filter = db.filter_clause("referenced_type || '.' || referenced_name", plsql_names, logical_connective="OR")
filter_clause = '{} {})'.format(dep_filter, ref_filter)
into_database.log.debug("Querying for plsql {} dependencies from DB...".format(
"(all)" if plsql_names is None else ", ".join(plsql_names)))
rs = db.query(""" SELECT name
, type
, dependency_type
, referenced_owner
, referenced_name
, referenced_type
FROM dba_dependencies
WHERE owner = :o
AND type IN ( 'TYPE', 'TABLE' /*'FUNCTION'
, 'PACKAGE'
, 'PACKAGE BODY'
, 'PROCEDURE'
, 'TRIGGER'
, 'TYPE'
, 'TYPE BODY' */
)
AND referenced_type = 'TYPE'
AND referenced_owner NOT IN ('SYS', 'PUBLIC')
{}
ORDER BY type, name
""".format(filter_clause), o=schema,
oracle_names=['name', 'referenced_owner', 'referenced_name'])
into_database.log.debug("Cursor obtained")
for row in rs:
if row['dependency_type'] != 'HARD':
raise UnimplementedFeatureError("Unsupported dependency_type = {}".format(row['dependency_type']))
name = OracleFQN(schema, row['name'])
try:
obj = into_database.find(name, row['type'], deferred=False)
referenced_name = OracleFQN(row['referenced_owner'], row['referenced_name'])
referenced_obj = into_database.find(referenced_name, row['referenced_type'])
into_database.log.debug("{} depends on {}".format(obj.pretty_name, referenced_obj.pretty_name))
obj._set_dependency(referenced_obj)
except NonexistentSchemaObjectError:
# If we don't know about the object, we don't want to track its dependencies
pass
def from_db (class_, schema, into_database, table_names=None):
table_filter = db.filter_clause('table_name', table_names)
into_database.log.debug("Querying for tables {} from DB...".format(
"(all)" if table_names is None else ", ".join(table_names)))
rs = db.query(""" SELECT table_name
, table_type
, CASE WHEN table_type_owner = 'PUBLIC'
OR table_type_owner LIKE '%SYS' THEN NULL
ELSE table_type_owner
END AS table_type_owner
, tablespace_name
, iot_type
, nested
, ( SELECT CAST(COLLECT(column_name ORDER BY dtc.internal_column_id) AS gt_string_table)
FROM dba_tab_cols dtc
WHERE dtc.owner = dat.owner
AND dtc.table_name = dat.table_name
) AS columns
, ( SELECT CAST(COLLECT(constraint_name) AS gt_string_table)
FROM ( SELECT constraint_name
FROM dba_cons_columns dcc
WHERE dcc.owner = dat.owner
AND dcc.table_name = dat.table_name
GROUP BY constraint_name
HAVING COUNT(*) > 1)
) AS constraints
FROM dba_all_tables dat
WHERE owner = :o
{}
""".format(table_filter), o=schema,
oracle_names=['table_name', 'columns', 'constraints', 'tablespace_name'])
into_database.log.debug("Cursor obtained")
for row in rs:
props = {'tablespace_name': row['tablespace_name'],
'table_type': row['table_type']}
table_name = OracleFQN(schema, row['table_name'])
into_database.log.debug("Processing table {}".format(table_name))
if row['iot_type'] is not None:
# TODO: currently ignoring index-organized tables
into_database.log.debug(
"Table {} is an index-organized table. Skipping...".format(
table_name))
continue
if row['nested'] == 'YES':
# TODO: assuming that nested tables are managed by their parent tables
into_database.log.debug(
"Table {} is a nested table. Skipping...".format(table_name))
continue
if row['table_type_owner']:
props['user_type'] = into_database.find(
OracleFQN(row['table_type_owner'], row['table_type']), Type)
props['columns'] = [into_database.find(OracleFQN(table_name.schema,
table_name.obj,
column_name),
Column)
for column_name in row['columns']]
props['constraints'] = {
into_database.find(OracleFQN(table_name.schema, constraint_name), Constraint)
for constraint_name in row['constraints']}
yield class_(table_name, database=into_database,
create_location=(db.location,), **props)
rs.close()
def from_db (class_, schema, into_database, table_names=None):
table_filter = db.filter_clause('table_name', table_names)
rs = db.query(""" SELECT table_name
, column_name
, qualified_col_name
, data_type
, CASE WHEN data_type_owner = 'PUBLIC'
OR data_type_owner LIKE '%SYS' THEN NULL
ELSE data_type_owner
END AS data_type_owner
, data_length
, data_precision
, data_scale
, data_default
, char_length
, char_used
, nullable
, virtual_column
, hidden_column
, internal_column_id
, CURSOR(
SELECT constraint_name
FROM dba_cons_columns dcc
WHERE dcc.owner = dtc.owner
AND dcc.table_name = dtc.table_name
GROUP BY constraint_name
HAVING COUNT(*) = 1
AND MAX(column_name) = dtc.column_name
) AS constraints
FROM dba_tab_cols dtc
WHERE owner = :o
-- Ignore columns on tables in the recyclebin
AND NOT (LENGTH(table_name) = 30
AND table_name LIKE 'BIN$%')
{}
""".format(table_filter), o=schema,
oracle_names=['table_name', 'column_name',
'qualified_col_name', 'constraint_name',
'data_type_owner', 'data_type'])
for row in rs:
(_, table_name), (_, col_name), *props, (_, constraints) = row.items()
props = dict(props)
generated = props['hidden_column'] == 'YES'
if col_name == props['qualified_col_name']:
props['qualified_col_name']._generated = generated
col_name._generated = generated
column_name = OracleFQN(schema, table_name, col_name)
if props['data_type_owner']:
props['user_type'] = into_database.find(
OracleFQN(props['data_type_owner'], props['data_type']), Type)
del props['data_type_owner']
elif props['data_type']:
# Remove quotes that may be on built-in types
props['data_type'] = props['data_type'].strip('"')
constraints = {
into_database.find(OracleFQN(schema, cons['constraint_name']),
Constraint)
for cons in constraints}
yield class_(column_name, constraints=constraints,
database=into_database, create_location=(db.location,),
**props)
rs.close()