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.')