def trigger_ddl(aCursor='default', schema=AUDIT_SCHEMA, audited_table=None):
target_columns = gmPG2.get_col_names(link_obj=aCursor,
schema=schema,
table=audited_table)
columns2skip = gmPG2.get_col_names(link_obj=aCursor,
schema=AUDIT_SCHEMA,
table=AUDIT_FIELDS_TABLE)
columns = []
values = []
for column in target_columns:
if column not in columns2skip:
columns.append(column)
values.append(u'OLD.%s' % column)
args = {
'src_tbl': audited_table,
'src_schema': schema,
'log_tbl': u'%s%s' % (LOG_TABLE_PREFIX, audited_table),
'cols_clause': u', '.join(columns),
'vals_clause': u', '.join(values)
}
modified_by_func_exists = gmPG2.function_exists(
link_obj=aCursor,
schema=u'gm',
function=u'account_is_dbowner_or_staff')
ddl = []
if modified_by_func_exists:
ddl.append(SQL_TEMPLATE_INSERT % args)
ddl.append(u'')
ddl.append(SQL_TEMPLATE_UPDATE % args)
ddl.append(u'')
ddl.append(SQL_TEMPLATE_DELETE % args)
#ddl.append(u'')
#ddl.append(SQL_TEMPLATE_FK_MODIFIED_BY % args)
else:
# the *_NO_*_CHECK variants are needed for pre-v21 databases
# where gm.account_is_dbowner_or_staff() doesn't exist yet
ddl.append(SQL_TEMPLATE_INSERT_NO_INSERTER_CHECK % args)
ddl.append(u'')
ddl.append(SQL_TEMPLATE_UPDATE_NO_UPDATER_CHECK % args)
ddl.append(u'')
ddl.append(SQL_TEMPLATE_DELETE_NO_DELETER_CHECK % args)
ddl.append(u'')
return ddl
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 trigger_ddl(aCursor='default', schema='audit', audited_table=None):
audit_trail_table = '%s%s' % (audit_trail_table_prefix, audited_table)
target_columns = gmPG2.get_col_names(link_obj = aCursor, schema = schema, table = audited_table)
columns2skip = gmPG2.get_col_names(link_obj = aCursor, schema = audit_schema, table = audit_fields_table)
columns = []
values = []
for column in target_columns:
if column not in columns2skip:
columns.append(column)
values.append('OLD.%s' % column)
columns_clause = string.join(columns, ', ')
values_clause = string.join(values, ', ')
ddl = []
# insert
ddl.append(tmpl_insert_function % (audited_table, audited_table))
ddl.append('')
ddl.append(tmpl_insert_trigger % (audited_table, schema, audited_table, audited_table))
ddl.append('')
# update
ddl.append(tmpl_update_function % (audited_table, audited_table, audit_trail_table, columns_clause, values_clause))
ddl.append('')
ddl.append(tmpl_update_trigger % (audited_table, schema, audited_table, audited_table))
ddl.append('')
# delete
ddl.append(tmpl_delete_function % (audited_table, audited_table, audit_trail_table, columns_clause, values_clause))
ddl.append('')
ddl.append(tmpl_delete_trigger % (audited_table, schema, audited_table, audited_table))
ddl.append('')
# disallow delete/update on auditing table
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, '']
