/// <summary>
/// returns <see cref="ValuedColumn"/>, based on <paramref name="queryType"/>,
/// corresponding to the id of the triggered row in <paramref name="auditedTableSchema"/>.
/// </summary>
/// <remarks>
/// <see cref="ValuedColumn"/> value might reference the old / new row id,
/// before / after the triggering query took place (i.e (old/new).columnName),
/// or the last inserted row id in audited table.
/// </remarks>
/// <param name="auditedTableSchema"></param>
/// <param name="auditTableSchema"></param>
/// <param name="queryType"></param>
/// <returns>
/// <see cref="ValuedColumn"/>, based on <paramref name="queryType"/>,
/// corresponding to the id of the triggered row in <paramref name="auditedTableSchema"/>.
/// </returns>
private static ValuedColumn getAuditedTableTriggeredRowIdValuedColumn(
TableSchema auditedTableSchema,
TableSchema auditTableSchema,
Query.eQueryType queryType)
{
ValuedColumn auditedTableTriggeredRowIdValuedColumn = null;
// name of column (in audit table) containing audited table triggered row id
string columnName = auditTableSchema.ColumnSchemas
[AuditTableStructure.AUDITED_TABLE_ROW_ID_COLUMN_INDEX].Name;
if (queryType == Query.eQueryType.Insert) // get the ID the row got after being inserted
{
object columnValue = new FunctionTableColumn(
FunctionTableColumn.eFunctionType.LastInsertRowid);
auditedTableTriggeredRowIdValuedColumn = new ValuedColumn(columnName, columnValue);
}
else if (queryType == Query.eQueryType.Update || queryType == Query.eQueryType.Delete)
{
// get the id the row had before being updated / deleted
auditedTableTriggeredRowIdValuedColumn = new TriggerValuedColumn(
columnName,
auditedTableSchema.PrimaryKeyColumnSchema.Name,
TriggerValuedColumn.ValueExpression.eTime.Old);
}
return(auditedTableTriggeredRowIdValuedColumn);
}
private static string formatExceptionMessage(Query.eQueryType queryType)
{
string exceptionMessage = string.Format(
"Audit is not supported for queries of type '{0}'.",
Query.QueryTypeToString(queryType));
return(exceptionMessage);
}
/// <summary>
/// returns the <see cref="InsertQuery"/> performed on the instance of
/// <paramref name="auditTableSchema"/> corresponding to <paramref name="auditedTableSchema"/>,
/// when audit is triggered by query of <paramref name="queryType"/>, performed on
/// <paramref name="auditedTableSchema"/>.
/// </summary>
/// <param name="auditedTableSchema"></param>
/// <param name="auditTableSchema"></param>
/// <param name="queryType"></param>
/// <returns>
/// <see cref="InsertQuery"/> performed on the instance of
/// <paramref name="auditTableSchema"/> corresponding to <paramref name="auditedTableSchema"/>,
/// when audit is triggered by query of <paramref name="queryType"/>, performed on
/// <paramref name="auditedTableSchema"/>
/// </returns>
private static InsertQuery getOnTriggerAuditInsertQuery(
TableSchema auditedTableSchema,
TableSchema auditTableSchema,
Query.eQueryType queryType)
{
InsertQuery onTriggerAuditInsertQuery;
// init trigger InsertQuery ValuedColumn list with:
// 0. ID corresponding to a query of type queryType,
// which was performed on triggeredTableSchema and triggered the audit
// 1. ID of row which was triggered
List <ValuedColumn> triggerInsertQueryValuedColumns = new List <ValuedColumn>()
{
new ValuedColumn(
auditTableSchema.ColumnSchemas[1].Name,
getQueryTypeIdSelectQuery(queryType)),
getAuditedTableTriggeredRowIdValuedColumn(
auditedTableSchema,
auditTableSchema,
queryType)
};
// add values of rest of ValuedColumns of row which was triggered in
// auditedTable
// convert each (non-primary key) ValuedColumn (key, value) of triggered row in
// auditedTable,
// into a TriggerValuedColumn (key, [TriggerValuedColumn.eTime].key)
// in the auditTable InsertQuery
// (note that column names in auditTable starting from index 3 match column names in
// auditedTable starting from index 1)
TriggerValuedColumn.ValueExpression.eTime triggerValuedColumnTime =
queryType == Query.eQueryType.Insert
? TriggerValuedColumn.ValueExpression.eTime.New
: TriggerValuedColumn.ValueExpression.eTime.Old;
TriggerValuedColumn[] auditTableTriggerValueColumns =
auditedTableSchema.ColumnSchemas.Subarray(1).Select(
columnSchema => new TriggerValuedColumn(
columnSchema.Name,
columnSchema.Name,
triggerValuedColumnTime)
).ToArray();
// add ValuedColumns to ValuedColumn list
triggerInsertQueryValuedColumns.AddRange(auditTableTriggerValueColumns);
// init InsertQuery
onTriggerAuditInsertQuery = new InsertQuery(
auditTableSchema.Name,
triggerInsertQueryValuedColumns.ToArray()
);
return(onTriggerAuditInsertQuery);
}
public TriggerSchema(
string name,
eTriggeredQueryTime triggeredQueryTime,
Query.eQueryType triggeringQueryType,
string triggeredTableName,
Query triggeredQuery)
{
this.name = name;
this.triggeredQueryTime = triggeredQueryTime;
this.triggeringQueryType = triggeringQueryType;
this.triggeredTableName = triggeredTableName;
this.triggeredQuery = triggeredQuery;
}
/// <summary>
/// returns the <see cref="TriggerSchema"/> name corresponding to a query of
/// <paramref name="queryType"/> performed on <paramref name="auditedTableSchema"/>.
/// </summary>
/// <param name="auditedTableSchema"></param>
/// <param name="queryType"></param>
/// <returns>
/// <see cref="TriggerSchema"/> name corresponding to a query of
/// <paramref name="queryType"/> performed on <paramref name="auditedTableSchema"/>
/// </returns>
private static string getTriggerName(
TableSchema auditedTableSchema,
Query.eQueryType queryType)
{
string queryTypeString =
Query.QueryTypeToString(queryType).MakeOnlyCharactersAtIndicesUpper(0);
String triggerName = string.Format(
"on{0}{1}",
auditedTableSchema.Name,
queryTypeString);
return(triggerName);
}
/// <summary>
/// returns <see cref="TriggerSchema.eTriggeredQueryTime"/> corresponding to
/// specified <paramref name="queryType"/>.
/// </summary>
/// <param name="queryType"></param>
/// <returns>
/// <see cref="TriggerSchema.eTriggeredQueryTime"/> corresponding to
/// specified <paramref name="queryType"/>
/// </returns>
private static TriggerSchema.eTriggeredQueryTime getTriggerTime(Query.eQueryType queryType)
{
TriggerSchema.eTriggeredQueryTime triggerTime = 0; // init to temp value
if (queryType == Query.eQueryType.Insert)
{
triggerTime = TriggerSchema.eTriggeredQueryTime.After;
}
else if (queryType == Query.eQueryType.Update || queryType == Query.eQueryType.Delete)
{
triggerTime = TriggerSchema.eTriggeredQueryTime.Before;
}
return(triggerTime);
}
/// <summary>
/// returns <see cref="TriggerSchema"/> corresponding to a query of <paramref name="queryType"/>
/// performed on table corresponding to <paramref name="auditedTableSchema"/>.
/// </summary>
/// <param name="auditedTableSchema"></param>
/// <param name="auditTableSchema"></param>
/// <param name="queryType"></param>
/// <returns>
/// <see cref="TriggerSchema"/> corresponding to a query of <paramref name="queryType"/>
/// performed on table corresponding to <paramref name="auditedTableSchema"/>
/// </returns>
internal static TriggerSchema GetAuditTriggerSchema(
TableSchema auditedTableSchema,
TableSchema auditTableSchema,
Query.eQueryType queryType)
{
TriggerSchema auditTriggerSchema;
InsertQuery onTriggerAuditInsertQuery =
getOnTriggerAuditInsertQuery(
auditedTableSchema,
auditTableSchema,
queryType);
auditTriggerSchema = new TriggerSchema(
getTriggerName(auditedTableSchema, queryType),
getTriggerTime(queryType),
queryType,
auditedTableSchema.Name,
onTriggerAuditInsertQuery);
return(auditTriggerSchema);
}
/// <summary>
/// returns <see cref="SelectQuery"/> for selecting the id of the specified
/// <paramref name="queryType"/>.
/// </summary>
/// <param name="queryType"></param>
/// <returns>
/// <see cref="SelectQuery"/> for selecting the id of the specified
/// <paramref name="queryType"/>
/// </returns>
private static SelectQuery getQueryTypeIdSelectQuery(Query.eQueryType queryType)
{
// select queryTypeId corresponding to queryType
SelectQuery queryTypeIdSelectQuery = new SelectQuery(
QueryTypeTableStructure.TABLE_NAME,
new TableColumn[]
{
new TableColumn(
DatabaseStructure.ID_COLUMN_NAME,
QueryTypeTableStructure.TABLE_NAME)
},
null,
new BasicCondition(
new ValuedTableColumn(
QueryTypeTableStructure.NAME_COLUMN_NAME,
QueryTypeTableStructure.TABLE_NAME,
Query.QueryTypeToString(queryType)
),
BasicCondition.eOperatorType.Equal
)
);
return(queryTypeIdSelectQuery);
}
internal UnsupportedAuditQueryTypeException(Query.eQueryType queryType)
: base(formatExceptionMessage(queryType))
{
this.queryType = queryType;
}
/// <summary>
/// returns undo <see cref="Query"/> corresponding to query performed on
/// <paramref name="auditedTableName"/>, where <paramref name="auditTableRow"/> contains
/// the data required to undo a single row in <paramref name="auditedTableName"/>,
/// which was affected by the query.
/// </summary>
/// <param name="auditTableRow"></param>
/// <param name="auditedTableName"></param>
/// <param name="queryTypeIdToQueryType"></param>
/// <returns>
/// undo <see cref="Query"/> corresponding to query performed on
/// <paramref name="auditedTableName"/>, where <paramref name="auditTableRow"/> contains
/// the data required to undo a single row in <paramref name="auditedTableName"/>,
/// which was affected by the query
/// </returns>
/// <exception cref="UnsupportedAuditQueryTypeException">
/// thrown if <see cref="Query.eQueryType"/> specified in <paramref name="auditTableRow"/>
/// is not supported for audit
/// </exception>
private static WriteQuery getUndoQuery(
ResultSet.Row auditTableRow,
string auditedTableName,
Dictionary <long, Query.eQueryType> queryTypeIdToQueryType)
{
WriteQuery undoQuery;
// type of query that was originally performed on audited table
long queryTypeId = auditTableRow.GetColumnValue <long>(
AuditTableStructure.QUERY_ID_COLUMN_INDEX);
Query.eQueryType queryType = queryTypeIdToQueryType[queryTypeId];
// value of _id column of modified row in audit table
long auditedTableRowId = auditTableRow.GetColumnValue <long>(
AuditTableStructure.AUDITED_TABLE_ROW_ID_COLUMN_INDEX);
// get undo query based on query type
if (queryType == Query.eQueryType.Insert)
{
undoQuery = new DeleteQuery(
auditedTableName,
new BasicCondition(
new ValuedTableColumn(
DatabaseStructure.ID_COLUMN_NAME,
auditedTableName,
auditedTableRowId),
BasicCondition.eOperatorType.Equal
)
);
}
else if (queryType == Query.eQueryType.Update || queryType == Query.eQueryType.Delete)
{
// ValuedColumn of _id column of modified row in audited table
ArrayRange <ValuedColumn> auditedTableRowIdArrayRange =
new ArrayRange <ValuedColumn>(
new ValuedColumn(DatabaseStructure.ID_COLUMN_NAME, auditedTableRowId)
);
// ValueColumn of columns other than _id of modified row in audited table
ArrayRange <ValuedColumn> auditedTableRowValuesArrayRange =
new ArrayRange <ValuedColumn>(
ValuedColumn.Parse(auditTableRow.ColumnNames, auditTableRow.ColumnValues),
AuditTableStructure.AUDITED_TABLE_DATA_COLUMNS_START_INDEX);
// unified ValuedColumn list for undo query
List <ValuedColumn> queryValuedColumns
= ListUtils.ListFromArrayRanges(
auditedTableRowIdArrayRange,
auditedTableRowValuesArrayRange);
if (queryType == Query.eQueryType.Update)
{
undoQuery = new UpdateQuery(
auditedTableName,
queryValuedColumns,
new BasicCondition(
new ValuedTableColumn(
DatabaseStructure.ID_COLUMN_NAME,
auditedTableName,
auditedTableRowId),
BasicCondition.eOperatorType.Equal
)
);
}
else // queryType == Query.eQueryType.Delete
{
undoQuery = new InsertQuery(auditedTableName, queryValuedColumns);
}
}
else
{
throw new UnsupportedAuditQueryTypeException(queryType);
}
return(undoQuery);
}
