private static void GenerateBuildDeltaProcedures(string schema, string tableName, TrackingTable trackingTable, List<SchemaDataSet.ColumnsRow> targetTableColumns, StringBuilder sqlBuilder, AuditUpdateParameters updateParameters)
{
CreateUpdateDeltaTableProcedure createUpdateDeltaTableProcedureTemplate = new CreateUpdateDeltaTableProcedure(schema, tableName, trackingTable, targetTableColumns, updateParameters);
string sql = createUpdateDeltaTableProcedureTemplate.TransformText();
sqlBuilder.AppendLine(sql);
SchemaDataSet schemaDataSet = updateParameters.SchemaDataSet;
if (schemaDataSet.SchemaTables.TableExists(updateParameters.AuditingSchema, "Catalog"))
{
EntityConnectionStringBuilder connectionStringBuilder = Generator.GetEntityConnectionStringBuilder(updateParameters);
AuditFrameworkEntities context = new AuditFrameworkEntities(connectionStringBuilder.ToString());
IOrderedQueryable<Catalog> priorVersionTrackingTableQuery = from catalog in context.Catalogs
where catalog.AuditedSchema.Equals(schema, StringComparison.OrdinalIgnoreCase)
where catalog.AuditedTableName.Equals(tableName, StringComparison.OrdinalIgnoreCase)
where catalog.Version < trackingTable.Version
where !catalog.Archived
orderby catalog.Version descending
select catalog;
foreach (Catalog catalog in priorVersionTrackingTableQuery)
{
TrackingTable priorTrackingTable = TrackingTable.Parse(catalog.AuditingTableName);
CreateUpdateDeltaPriorTableProcedure createUpdateDeltaPriorTableProcedureTemplate =new CreateUpdateDeltaPriorTableProcedure(schema, tableName, trackingTable, priorTrackingTable, targetTableColumns, updateParameters);
sql = createUpdateDeltaPriorTableProcedureTemplate.TransformText();
sqlBuilder.AppendLine(sql);
}
}
}
internal static void GenerateAuditForAllSchemas(StringBuilder sqlBuilder, AuditUpdateParameters updateParameters, Action<string, float?> reportProgress)
{
foreach (string schema in updateParameters.SchemasToAudit)
{
GenerateAuditForSchema(schema, sqlBuilder, updateParameters, reportProgress);
}
}
private static void GenerateAuditForSchema(string schema, StringBuilder sqlBuilder, AuditUpdateParameters updateParameters, Action<string, float?> reportProgress)
{
reportProgress(string.Format("Generating audit for schema {0}", schema), null);
SchemaDataSet schemaDataSet = updateParameters.SchemaDataSet;
SchemaDataSet.SchemaPrimaryKeysDataTable primaryKeysDataTable = schemaDataSet.SchemaPrimaryKeys;
primaryKeysDataTable.FillPrimaryKeysBySchema(schema, updateParameters.AuditingDatabaseConnection);
SchemaDataSet.SchemaTablesDataTable schemaTables = schemaDataSet.SchemaTables;
var schemaTablesRows = schemaTables.AsEnumerable();
var schemaTablesQuery = from schemaTableRow in schemaTablesRows
where schemaTableRow.TABLE_SCHEMA.Equals(schema)
where schemaTableRow.TABLE_TYPE.Equals("BASE TABLE")
orderby schemaTableRow.TABLE_NAME
select schemaTableRow;
foreach (SchemaDataSet.SchemaTablesRow schemaTablesRow in schemaTablesQuery)
{
string tableName = schemaTablesRow.TABLE_NAME;
if (!updateParameters.TablesNotAudited.Cast<string>().Any(name => name.Equals(tableName, StringComparison.OrdinalIgnoreCase)))
{
GenerateTableAudit.GenerateAuditForTable(schema, tableName, primaryKeysDataTable, sqlBuilder, updateParameters, reportProgress);
}
}
reportProgress(string.Format("Generated audit for schema {0}", schema), null);
}
internal static void CreateBaseAuditingProcedures(StringBuilder sqlBuilder, AuditUpdateParameters updateParameters, Action<string, float?> reportProgress)
{
GenerateRequestTransactionSequenceProcedure(sqlBuilder, updateParameters);
GenerateCurrentTransactionSequenceProcedure(sqlBuilder, updateParameters);
GenerateReleaseTransactionSequenceProcedure(sqlBuilder, updateParameters);
reportProgress("Generated TransactionSequence procedures", null);
}
internal SetBaseline(string schema, string tableName, TrackingTable trackingTable, List<SchemaDataSet.ColumnsRow> targetTableColumns, AuditUpdateParameters updateParameters)
{
_schema = schema;
_tableName = tableName;
_trackingTable = trackingTable;
_targetTableColumns = targetTableColumns;
_params = updateParameters;
}
internal CreateUpdateDeltaTableProcedure(string schema, string tableName, TrackingTable trackingTable, List<SchemaDataSet.ColumnsRow> targetTableColumns, AuditUpdateParameters updateParameters)
{
_schema = schema;
_tableName = tableName;
_trackingTable = trackingTable;
_targetTableColumns = targetTableColumns;
_params = updateParameters;
}
public CreateTrackingTable(string schema, string tableName, string trackingTableName, List<SchemaDataSet.ColumnsRow> targetTableColumns, AuditUpdateParameters updateParameters)
{
_schema = schema;
_tableName = tableName;
_trackingTableName = trackingTableName;
_targetTableColumns = targetTableColumns;
_params = updateParameters;
}
private static List<SchemaDataSet.ColumnsRow> GetAuditedColumnsForTable(string schema, string tableName, AuditUpdateParameters updateParameters)
{
StringCollection columnsNotAudited = updateParameters.ColumnsNotAudited;
IEnumerable<SchemaDataSet.ColumnsRow> columnsRows = GetColumnsForTable(schema, tableName, updateParameters);
IEnumerable<SchemaDataSet.ColumnsRow> query = from columnRow in columnsRows
where !(columnsNotAudited.Cast<string>().Any(columnName => columnName.Equals(columnRow.COLUMN_NAME, StringComparison.OrdinalIgnoreCase)))
select columnRow;
return query.ToList();
}
public CreateTrackingInsertProcedure(string schema, string auditedTable, string trackingTableName, string deltaTableName, List<SchemaDataSet.ColumnsRow> targetTableColumns, AuditUpdateParameters updateParameters)
{
_schema = schema;
_auditedTable = auditedTable;
_trackingTableName = trackingTableName;
_deltaTableName = deltaTableName;
_targetTableColumns = targetTableColumns;
_params = updateParameters;
}
private static void GenerateAuditMasterTable(StringBuilder sqlBuilder, AuditUpdateParameters updateParameters)
{
SchemaDataSet schemaDataSet = updateParameters.SchemaDataSet;
if (!schemaDataSet.SchemaTables.TableExists(updateParameters.AuditingSchema, "AuditMaster"))
{
CreateAuditMasterTable createAuditMasterTableTemplate = new CreateAuditMasterTable(updateParameters);
string sql = createAuditMasterTableTemplate.TransformText();
sqlBuilder.AppendLine(sql);
}
}
private static void GenerateTransactionSequenceTable(StringBuilder sqlBuilder, AuditUpdateParameters updateParameters)
{
SchemaDataSet schemaDataSet = updateParameters.SchemaDataSet;
if (!schemaDataSet.SchemaTables.TableExists(updateParameters.AuditingSchema, "TransactionSequence"))
{
CreateTransactionSequenceTable createTransactionSequenceTable = new CreateTransactionSequenceTable(updateParameters);
string sql = createTransactionSequenceTable.TransformText();
sqlBuilder.AppendLine(sql);
}
}
internal static IEnumerable<SchemaDataSet.ColumnsRow> GetColumnsForTable(string schema, string tableName, AuditUpdateParameters updateParameters)
{
SchemaDataSet schemaDataSet = updateParameters.SchemaDataSet;
SchemaDataSet.ColumnsDataTable columnsDataTable = schemaDataSet.Columns;
IEnumerable<SchemaDataSet.ColumnsRow> columnsRows = columnsDataTable.Rows.Cast<SchemaDataSet.ColumnsRow>();
IEnumerable<SchemaDataSet.ColumnsRow> query = from columnRow in columnsRows
where columnRow.TABLE_SCHEMA.Equals(schema, StringComparison.OrdinalIgnoreCase)
&& columnRow.TABLE_NAME.Equals(tableName, StringComparison.OrdinalIgnoreCase)
orderby columnRow.ORDINAL_POSITION
select columnRow;
return query;
}
internal static void CreateBaseAuditingTables(StringBuilder sqlBuilder, AuditUpdateParameters updateParameters, Action<string, float?> reportProgress)
{
GenerateCatalogTable(sqlBuilder, updateParameters);
reportProgress("Generated Catalog table", null);
GenerateActionCodesTable(sqlBuilder, updateParameters);
reportProgress("Generated ActionCodes table", null);
GenerateTransactionSequenceTable(sqlBuilder, updateParameters);
reportProgress("Generated TransactionSequence table", null);
GenerateSessionTransactionTable(sqlBuilder, updateParameters);
reportProgress("Generated SessionTransaction table", null);
GenerateAuditMasterTable(sqlBuilder, updateParameters);
reportProgress("Generated AuditMaster table", null);
}
internal static void GenerateAuditSupportForTable(string schema, string tableName, TrackingTable trackingTable, StringBuilder sqlBuilder, List<SchemaDataSet.ColumnsRow> targetTableColumns, AuditUpdateParameters updateParameters, Action<string, float?> reportProgress)
{
string trackingTableName = trackingTable.ToString();
string deltaTableName = trackingTableName + "Δ";
CreateDeltaTable createDeltaTableTemplate = new CreateDeltaTable(trackingTableName + "Δ", targetTableColumns, updateParameters);
string sql = createDeltaTableTemplate.TransformText();
sqlBuilder.AppendLine(sql);
GenerateAuditTableInsertProcedures(schema, tableName, trackingTableName, deltaTableName, targetTableColumns, sqlBuilder, updateParameters, reportProgress);
if (updateParameters.GenerateBaseline)
{
Baseline(schema, tableName, trackingTable, targetTableColumns, sqlBuilder, updateParameters, reportProgress);
}
GenerateAuditTableTriggers(schema, tableName, sqlBuilder, updateParameters, reportProgress);
}
public string GenerateAuditingFrameworkUpdateSql(AuditUpdateParameters updateParameters)
{
StringBuilder sqlBuilder = new StringBuilder();
Action<string, float?> reportProgress = (message, percentComplete) =>
{
sqlBuilder.AppendLine();
sqlBuilder.AppendFormat("PRINT(N'{0}');", message);
sqlBuilder.AppendLine();
sqlBuilder.AppendLine("GO");
OnReportProgress(message, percentComplete);
};
SqlConnection connection = updateParameters.AuditingDatabaseConnection;
try
{
OnReportProgress("Started generating script", null);
connection.Open();
if (updateParameters.RemoveAll)
{
GenerateRemoveAll(sqlBuilder, updateParameters);
}
else
{
LoadSchemaDataSet(updateParameters);
GenerateDatabaseFiles.GenerateDatabaseSetup(sqlBuilder, updateParameters, reportProgress);
GenerateAuditingBase.CreateBaseAuditingTables(sqlBuilder, updateParameters, reportProgress);
GenerateAuditingBase.CreateBaseAuditingProcedures(sqlBuilder, updateParameters, reportProgress);
GenerateAuditingBase.CreateProcedureGrant(sqlBuilder, updateParameters, reportProgress);
GenerateSchemaAudit.GenerateAuditForAllSchemas(sqlBuilder, updateParameters, reportProgress);
GenerateDelta.GenerateBuildAllDeltaProcedures(sqlBuilder, updateParameters, reportProgress);
}
}
finally
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
return sqlBuilder.ToString();
}
internal static void GenerateDatabaseSetup(StringBuilder sqlBuilder, AuditUpdateParameters updateParameters, Action<string, float?> reportProgress)
{
Server server = new Server(updateParameters.AuditingDatabaseConnection.DataSource);
Database database = server.Databases[updateParameters.AuditingDatabaseConnection.Database];
if (!database.FileGroups.Contains(updateParameters.AuditingFileGroup))
{
DataFile dataFile = database.FileGroups[0].Files[0];
string fileName = dataFile.FileName;
string path = Path.GetDirectoryName(fileName);
if (path == null)
{
throw new InvalidOperationException("Can't determine path of database file");
}
updateParameters.SqlFilePath = new DirectoryInfo(path);
SetupDatabase setupDatabaseTemplate = new SetupDatabase(updateParameters);
string text = setupDatabaseTemplate.TransformText();
sqlBuilder.AppendLine(text);
}
reportProgress("Generated database setup", null);
}
/// <summary>
/// Determines whether a new tracking table should be built and catalogued
/// </summary>
/// <param name="schema">The name of the audited schema</param>
/// <param name="tableName">The name of the audited table to check</param>
/// <param name="targetTableColumns">A list of the columns in the audited table</param>
/// <param name="updateParameters">Generator parameters</param>
/// <returns>True of the audited table has different columns (name or type) than the tracking table</returns>
/// <remarks>
/// This routine only checks the column names and data types for a match. A change to indexes, relationships, and keys will not affect
/// the comparison.
/// </remarks>
private static bool NewVersionOfTrackingTableNeeded(string schema, string tableName, ICollection<SchemaDataSet.ColumnsRow> targetTableColumns, AuditUpdateParameters updateParameters)
{
// check whether we even have a tracking table
TrackingTable trackingTable = GenerateTableTracking.GetCurrentTrackingTable(schema, tableName, updateParameters);
if (null == trackingTable)
{
return true;
}
// get the columns from the tracking table
List<SchemaDataSet.ColumnsRow> trackingTableColumns = GetTrackingColumnsForTable(trackingTable, updateParameters);
// check whether the number of columns match
if (targetTableColumns.Count != trackingTableColumns.Count)
{
return true;
}
// compare each column
return AllColumnsMatch(targetTableColumns, trackingTableColumns);
}
internal static TrackingTable GenerateAuditForTable(string schema, string tableName, SchemaDataSet.SchemaPrimaryKeysDataTable primaryKeysDataTable, StringBuilder sqlBuilder, AuditUpdateParameters updateParameters, Action<string, float?> reportProgress)
{
TrackingTable trackingTable;
reportProgress(String.Format("Generating audit for table {0}", tableName), null);
List<SchemaDataSet.ColumnsRow> targetTableColumns = GetAuditedColumnsForTable(schema, tableName, updateParameters);
bool createNewTableVersion = NewVersionOfTrackingTableNeeded(schema, tableName, targetTableColumns, updateParameters);
if (createNewTableVersion)
{
trackingTable = GenerateTableTracking.GenerateNewTrackingTable(schema, tableName, targetTableColumns, updateParameters, sqlBuilder, reportProgress);
}
else
{
trackingTable = GenerateTableTracking.GetCurrentTrackingTable(schema, tableName, updateParameters);
}
GenerateTableTracking.GenerateAuditSupportForTable(schema, tableName, trackingTable, sqlBuilder, targetTableColumns, updateParameters, reportProgress);
GenerateTableTracking.GenerateInstantSupportForTable(schema, tableName, sqlBuilder, updateParameters);
GenerateDelta.CreateDeltaProcedures(schema, tableName, trackingTable, targetTableColumns, sqlBuilder, updateParameters, reportProgress);
reportProgress(String.Format("Generated audit for table {0}", tableName), null);
return trackingTable;
}
internal static TrackingTable GenerateNewTrackingTable(string schema, string tableName, List<SchemaDataSet.ColumnsRow> targetTableColumns, AuditUpdateParameters updateParameters, StringBuilder sqlBuilder, Action<string, float?> reportProgress)
{
int version;
SchemaDataSet schemaDataSet = updateParameters.SchemaDataSet;
if (schemaDataSet.SchemaTables.TableExists(updateParameters.AuditingSchema, "Catalog"))
{
EntityConnectionStringBuilder connectionStringBuilder = Generator.GetEntityConnectionStringBuilder(updateParameters);
AuditFrameworkEntities context = new AuditFrameworkEntities(connectionStringBuilder.ToString());
var catalogQuery = from catalog in context.Catalogs
where catalog.AuditedSchema.Equals(schema, StringComparison.OrdinalIgnoreCase)
&& catalog.AuditedTableName.Equals(tableName, StringComparison.OrdinalIgnoreCase)
orderby catalog.Version descending
select catalog;
var latestCatalog = catalogQuery.FirstOrDefault();
if (null == latestCatalog)
{
version = 0;
}
else
{
version = latestCatalog.Version + 1;
}
}
else
{
version = 0;
}
TrackingTable trackingTable = new TrackingTable(schema, tableName, version);
string trackingTableName = trackingTable.ToString();
InsertCatalog insertCatalogTemplate = new InsertCatalog(schema, tableName, version, updateParameters.AuditingSchema, trackingTableName);
string sql = insertCatalogTemplate.TransformText();
sqlBuilder.AppendLine(sql);
CreateTrackingTable createTrackingTableTemplate = new CreateTrackingTable(schema, tableName, trackingTableName, targetTableColumns, updateParameters);
sql = createTrackingTableTemplate.TransformText();
sqlBuilder.AppendLine(sql);
return trackingTable;
}
internal static TrackingTable GetCurrentTrackingTable(string schema, string tableName, AuditUpdateParameters updateParameters)
{
SchemaDataSet schemaDataSet = updateParameters.SchemaDataSet;
if (schemaDataSet.SchemaTables.TableExists(updateParameters.AuditingSchema, "Catalog"))
{
EntityConnectionStringBuilder connectionStringBuilder = Generator.GetEntityConnectionStringBuilder(updateParameters);
AuditFrameworkEntities context = new AuditFrameworkEntities(connectionStringBuilder.ToString());
var catalogQuery = from catalog in context.Catalogs
where catalog.AuditedSchema.Equals(schema, StringComparison.OrdinalIgnoreCase)
&& catalog.AuditedTableName.Equals(tableName, StringComparison.OrdinalIgnoreCase)
orderby catalog.Version descending
select catalog;
Catalog catalogRow = catalogQuery.FirstOrDefault();
if (null == catalogRow)
{
return null;
}
TrackingTable trackingTable = TrackingTable.Parse(catalogRow.AuditingTableName);
return trackingTable;
}
return null;
}
internal static void GenerateBuildAllDeltaProcedures(StringBuilder sqlBuilder, AuditUpdateParameters updateParameters, Action<string, float?> reportProgress)
{
CreateBuildDeltaProcedure createBuildDeltaProcedureTemplate = new CreateBuildDeltaProcedure(updateParameters);
string sql = createBuildDeltaProcedureTemplate.TransformText();
sqlBuilder.AppendLine(sql);
}
public CreateAuditMasterTable(AuditUpdateParameters updateParameters)
{
_params = updateParameters;
}
public CreateActionCodesTable(AuditUpdateParameters updateParameters)
{
_params = updateParameters;
}
public SetupDatabase(AuditUpdateParameters updateParameters)
{
_params = updateParameters;
}
public CreateDeltaTable(string deltaTableName, List<SchemaDataSet.ColumnsRow> targetTableColumns, AuditUpdateParameters updateParameters)
{
_deltaTableName = deltaTableName;
_targetTableColumns = targetTableColumns;
_params = updateParameters;
}
public CreateReleaseTransactionSequenceProcedure(AuditUpdateParameters updateParameters)
{
_params = updateParameters;
}
private static void Baseline(string schema, string tableName, TrackingTable trackingTable, List<SchemaDataSet.ColumnsRow> targetTableColumns, StringBuilder sqlBuilder, AuditUpdateParameters updateParameters, Action<string, float?> reportProgress)
{
reportProgress(string.Format("Starting to set baseline records for {0}.{1}", schema, tableName), null);
SetBaseline performBaseLineTemplate = new SetBaseline(schema, tableName, trackingTable, targetTableColumns, updateParameters);
string sql = performBaseLineTemplate.TransformText();
sqlBuilder.AppendLine(sql);
reportProgress(string.Format("Finished setting baseline records for {0}.{1}", schema, tableName), null);
}
private static void GenerateAuditTableInsertProcedures(string schema, string tableName, string trackingTableName, string deltaTableName, List<SchemaDataSet.ColumnsRow> targetTableColumns, StringBuilder sqlBuilder, AuditUpdateParameters updateParameters, Action<string, float?> reportProgress)
{
reportProgress(string.Format("Generating TrackingInsert Procedure for {0}.{1}", schema, tableName), null);
CreateTrackingInsertProcedure createTrackingInsertProcedureTemplate = new CreateTrackingInsertProcedure(schema, tableName, trackingTableName, deltaTableName, targetTableColumns, updateParameters);
string sql = createTrackingInsertProcedureTemplate.TransformText();
sqlBuilder.AppendLine(sql);
reportProgress(string.Format("Generated TrackingInsert Procedure for {0}.{1}", schema, tableName), null);
}
public static void GenerateInstantSupportForTable(string schema, string tableName, StringBuilder sqlBuilder, AuditUpdateParameters updateParameters)
{
CreateInstantTrackIdProcedure createInstantTrackIdProcedureTemplate = new CreateInstantTrackIdProcedure(schema, tableName, updateParameters);
string sql = createInstantTrackIdProcedureTemplate.TransformText();
sqlBuilder.AppendLine(sql);
}
private static void GenerateAuditTableTriggers(string schema, string tableName, StringBuilder sqlBuilder, AuditUpdateParameters updateParameters, Action<string, float?> reportProgress)
{
reportProgress(string.Format("Generating Triggers for {0}.{1}", schema, tableName), null);
CreateTrigger triggerTemplate = new CreateTrigger(schema, tableName, updateParameters);
string sql = triggerTemplate.TransformText();
sqlBuilder.AppendLine(sql);
reportProgress(string.Format("Generated Triggers for {0}.{1}", schema, tableName), null);
}
