def audit_trail_table_ddl(aCursor=None, schema='audit', table2audit=None): audit_trail_table = '%s%s' % (audit_trail_table_prefix, table2audit) # which columns to potentially audit cols2potentially_audit = gmPG2.get_col_defs(link_obj = aCursor, schema = schema, table = table2audit) # which to skip cols2skip = gmPG2.get_col_names(link_obj = aCursor, schema = audit_schema, table = audit_fields_table) # which ones to really audit cols2really_audit = [] for col in cols2potentially_audit[0]: if col in cols2skip: continue cols2really_audit.append("\t%s %s" % (col, cols2potentially_audit[1][col])) # does the audit trail target table exist ? exists = gmPG2.table_exists(aCursor, 'audit', audit_trail_table) if exists is None: _log.error('cannot check existence of table [audit.%s]' % audit_trail_table) return None if exists: _log.info('audit trail table [audit.%s] already exists' % audit_trail_table) # sanity check table structure currently_audited_cols = gmPG2.get_col_defs(link_obj = aCursor, schema = u'audit', table = audit_trail_table) currently_audited_cols = [ '\t%s %s' % (c, currently_audited_cols[1][c]) for c in currently_audited_cols[0] ] for col in cols2really_audit: try: currently_audited_cols.index(col) except ValueError: _log.error('table structure incompatible: column ".%s" not found in audit table' % col.strip()) _log.error('%s.%s:' % (schema, table2audit)) _log.error('%s' % ','.join(cols2really_audit)) _log.error('%s.%s:' % (audit_schema, audit_trail_table)) _log.error('%s' % ','.join(currently_audited_cols)) return None return [] # must create audit trail table _log.info('no audit trail table found for [%s.%s]' % (schema, table2audit)) _log.info('creating audit trail table [audit.%s]' % audit_trail_table) # create audit table DDL attributes = ',\n'.join(cols2really_audit) table_def = tmpl_create_audit_trail_table % ( audit_trail_table, attributes, audit_trail_parent_table, # FIXME: use audit_schema audit_trail_table, audit_trail_table, audit_trail_table, audit_trail_table ) return [table_def, '']
def create_audit_ddl(aCursor): # get list of all marked tables # we could also get the child tables for audit.audit_fields # but we would have to potentially parse down several levels # of interitance (such as with clin.clin_root_item) to find # the actual leaf table to audit cmd = u"select schema, table_name from audit.audited_tables" rows, idx = gmPG2.run_ro_queries(link_obj=aCursor, queries=[{'cmd': cmd}]) if len(rows) == 0: _log.info('no tables to audit') return None _log.debug('the following tables will be audited:') _log.debug(rows) ddl = [] ddl.append('\set check_function_bodies 1\n') ddl.append('set check_function_bodies to on;\n\n') # for each marked table for row in rows: if not gmPG2.table_exists(link_obj=aCursor, schema=row['schema'], table=row['table_name']): _log.error('table to audit (%s) does not exist', row) return None # create log table if necessary audit_trail_ddl = audit_trail_table_ddl(aCursor=aCursor, schema=row['schema'], table2audit=row['table_name']) if audit_trail_ddl is None: _log.error( 'cannot generate audit trail DDL for audited table [%s]' % row['table_name']) return None ddl.extend(audit_trail_ddl) if len(audit_trail_ddl) != 0: ddl.append('-- ----------------------------------------------') # create functions and triggers on log table ddl.extend( trigger_ddl(aCursor=aCursor, schema=row['schema'], audited_table=row['table_name'])) ddl.append('-- ----------------------------------------------') #ddl.append(SQL_TEMPLATE_DEM_STAFF_FK) return ddl
def create_audit_ddl(aCursor): # get list of all marked tables # we could also get the child tables for audit.audit_fields # but we would have to potentially parse down several levels # of interitance (such as with clin.clin_root_item) to find # the actual leaf tables to audit cmd = u"select schema, table_name from audit.audited_tables" rows, idx = gmPG2.run_ro_queries(link_obj=aCursor, queries = [{'cmd': cmd}]) if len(rows) == 0: _log.info('no tables to audit') return None _log.debug('the following tables will be audited:') _log.debug(rows) # for each marked table ddl = [] ddl.append('\set check_function_bodies 1\n') ddl.append('set check_function_bodies to on;\n\n') for row in rows: # sanity check: does table exist ? if not gmPG2.table_exists(link_obj = aCursor, schema = row['schema'], table = row['table_name']): _log.error('table to audit (%s) does not exist', row) return None audit_trail_ddl = audit_trail_table_ddl(aCursor=aCursor, schema=row['schema'], table2audit=row['table_name']) if audit_trail_ddl is None: _log.error('cannot generate audit trail DDL for audited table [%s]' % row['table_name']) return None ddl.extend(audit_trail_ddl) if len(audit_trail_ddl) != 0: ddl.append('-- ----------------------------------------------') ddl.extend(trigger_ddl(aCursor = aCursor, schema = row['schema'], audited_table = row['table_name'])) ddl.append('-- ----------------------------------------------') return ddl
def audit_trail_table_ddl(aCursor=None, schema=None, table2audit=None): audit_trail_table = '%s%s' % (LOG_TABLE_PREFIX, table2audit) # which columns to potentially audit cols2potentially_audit = gmPG2.get_col_defs(link_obj=aCursor, schema=schema, table=table2audit) # which to skip cols2skip = gmPG2.get_col_names(link_obj=aCursor, schema=AUDIT_SCHEMA, table=AUDIT_FIELDS_TABLE) # which ones to really audit cols2really_audit = [] for col in cols2potentially_audit[0]: if col in cols2skip: continue cols2really_audit.append("\t%s %s" % (col, cols2potentially_audit[1][col])) # does the audit trail target table exist ? exists = gmPG2.table_exists(aCursor, AUDIT_SCHEMA, audit_trail_table) if exists is None: _log.error('cannot check existence of table [audit.%s]' % audit_trail_table) return None if exists: _log.info('audit trail table [audit.%s] already exists' % audit_trail_table) # sanity check table structure currently_audited_cols = gmPG2.get_col_defs(link_obj=aCursor, schema=AUDIT_SCHEMA, table=audit_trail_table) currently_audited_cols = [ '\t%s %s' % (c, currently_audited_cols[1][c]) for c in currently_audited_cols[0] ] for col in cols2really_audit: try: currently_audited_cols.index(col) except ValueError: _log.error( 'table structure incompatible: column ".%s" not found in audit table' % col.strip()) _log.error('%s.%s:' % (schema, table2audit)) _log.error('%s' % ','.join(cols2really_audit)) _log.error('%s.%s:' % (AUDIT_SCHEMA, audit_trail_table)) _log.error('%s' % ','.join(currently_audited_cols)) return None return [] # must create audit trail table _log.info('no audit trail table found for [%s.%s]' % (schema, table2audit)) _log.info('creating audit trail table [audit.%s]' % audit_trail_table) args = { 'log_schema': AUDIT_SCHEMA, 'log_base_tbl': AUDIT_TRAIL_PARENT_TABLE, 'log_tbl': audit_trail_table, 'log_cols': u',\n '.join(cols2really_audit) } return [SQL_TEMPLATE_CREATE_AUDIT_TRAIL_TABLE % args, '']