def from_db (class_, table, column_names=None):
order_by = ''
if column_names:
order_by = "ORDER BY {}".format(", ".join(column_names))
if not table.data:
with db.exact_numbers():
rs = db.query_all(""" SELECT *
FROM {}
{}
""".format(table.name, order_by))
if rs:
columns = [table.database.find(
OracleFQN(table.name.schema, table.name.obj, column_name), Column)
for column_name in rs[0]]
for row in rs:
table.add_data(columns, [Data.escape(col, columns[i])
for i, col in enumerate(row.values())])
def from_db (class_, table, column_names=None):
order_by = ''
if column_names:
order_by = "ORDER BY {}".format(", ".join(column_names))
if not table.data:
with db.exact_numbers():
table.log.debug("Querying {} data for columns {}...".format(
table.name, "(all)" if column_names is None else ", ".join(column_names)))
rs = db.query_all(""" SELECT *
FROM {}
{}
""".format(table.name, order_by))
table.log.debug("Query returned {} rows.".format(len(rs)))
if rs:
columns = [table.database.find(
OracleFQN(table.name.schema, table.name.obj, column_name), Column)
for column_name in rs[0]]
for row in rs:
table.add_data(columns, [Data.escape(col, columns[i])
for i, col in enumerate(row.values())])
def errors (self, throw=True):
rs = db.query_all(""" SELECT line
, position
, text
, attribute
FROM dba_errors
WHERE owner = :o
AND name = :n
AND type = :t
ORDER BY sequence
""", o=self.name.schema, n=self.name.obj, t=self.type)
warnings = [row for row in rs if row['attribute'] == 'WARNING']
errors = [row for row in rs if row['attribute'] == 'ERROR']
if warnings:
self.log.warn(PlsqlSyntaxError(self, warnings))
if errors:
e = PlsqlSyntaxError(self, errors)
if throw:
raise e
self.log.error(e)
def from_db (self):
owner = self.name.schema
self.log.info("Fetching schema {}...".format(owner))
schema = {
'objects': db.query_all(
""" SELECT object_name
, object_type
, last_ddl_time
FROM dba_objects
WHERE owner = :o
AND subobject_name IS NULL
AND object_type IN ( 'FUNCTION'
, 'INDEX'
, 'PACKAGE'
, 'PACKAGE BODY'
, 'PROCEDURE'
, 'SEQUENCE'
, 'SYNONYM'
, 'TABLE'
, 'TRIGGER'
, 'TYPE'
, 'TYPE BODY'
-- , 'VIEW'
)
UNION ALL
SELECT constraint_name
, 'CONSTRAINT'
, last_change
FROM dba_constraints
WHERE owner = :o
-- Ignore constraints on tables in the recyclebin
AND NOT (LENGTH(table_name) = 30
AND table_name LIKE 'BIN$%')
""", o=owner, oracle_names=['object_name']),
'columns': db.query_all(
""" SELECT table_name
, COUNT(*) AS num_columns
FROM dba_tab_cols
WHERE owner = :o
-- Ignore columns on tables in the recyclebin
AND NOT (LENGTH(table_name) = 30
AND table_name LIKE 'BIN$%')
GROUP BY table_name
""", o=owner, oracle_names=['table_name']),
'grants': 0,
# db.query_one(
# """ SELECT COUNT(*)
# FROM (SELECT DISTINCT owner, table_name
# FROM dba_tab_privs
# WHERE grantee = :o)
# """, o=owner),
}
self.log.debug("Query complete.")
total_objects = (len(schema['objects']) + sum(table['num_columns'] for table in schema['columns']) +
schema['grants'])
modified_times = {}
for object in schema['objects']:
object_name = OracleFQN(owner, object['object_name'])
object_type = _to_type(object['object_type'], object_name)
if issubclass(object_type, PlsqlCode):
object_name = _mangle_plsql_name(object_type, object_name)
object_type = PlsqlCode
if object_type not in modified_times:
modified_times[object_type] = {}
modified_times[object_type][object_name] = object['last_ddl_time']
self.log.info("Schema {} has {}.".format(owner, pluralize(total_objects,
'object')))
to_refresh = self.read_cache(modified_times)
if schema['grants']:
# Refresh all grants, but only if there are actually any grants out there
to_refresh[Grant] = None
change_count = 0
for obj_type, names in to_refresh.items():
if obj_type is Column:
for table in schema['columns']:
if names is None or table['table_name'] in names:
change_count += table['num_columns']
elif names is None:
if obj_type in modified_times:
change_count += len(modified_times[obj_type])
elif obj_type is Grant:
change_count += schema['grants']
else:
change_count += len(names)
if to_refresh:
def progress_message (o):
return "Fetched {{}} of schema {}.{}".format(owner,
" Currently fetching {}...".format(_plural_type(o))
if o else '')
actual = 0
for obj in progress_log((obj for obj_type, names in to_refresh.items()
for obj in obj_type.from_db(
self.name.schema, self.database, names)),
self.log, progress_message, count=change_count):
actual += 1
self.add(obj)
self.log.info("Fetching schema {} complete.".format(owner))
self.cache(modified_times)
else:
self.log.info('Using cached schema.')
