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