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, '']