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);
}
}
}
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);
}
}
}
private static List <SchemaDataSet.ColumnsRow> GetTrackingColumnsForTable(TrackingTable trackingTable, AuditUpdateParameters updateParameters)
{
IEnumerable <SchemaDataSet.ColumnsRow> columnsRows = GetColumnsForTable(updateParameters.AuditingSchema, trackingTable.ToString(), updateParameters);
IEnumerable <SchemaDataSet.ColumnsRow> query = from columnRow in columnsRows
where !columnRow.COLUMN_NAME.Equals("TrackºId", StringComparison.Ordinal)
select columnRow;
return(query.ToList());
}
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);
}
public static TrackingTable Parse(string trackingTableName)
{
string[] strings = trackingTableName.Split(new[] {'º'}, StringSplitOptions.RemoveEmptyEntries);
if (strings.Length < 3)
{
throw new InvalidArgumentException("Must be delimited with º character in two places");
}
TrackingTable trackingTable = new TrackingTable(strings[0], strings[1], Convert.ToInt32(strings[2]));
return trackingTable;
}
public static TrackingTable Parse(string trackingTableName)
{
string[] strings = trackingTableName.Split(new[] { 'º' }, StringSplitOptions.RemoveEmptyEntries);
if (strings.Length < 3)
{
throw new InvalidArgumentException("Must be delimited with º character in two places");
}
TrackingTable trackingTable = new TrackingTable(strings[0], strings[1], Convert.ToInt32(strings[2]));
return(trackingTable);
}
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);
}
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 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;
}
/// <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 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);
}
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);
}
internal static void CreateDeltaProcedures(string schema, string tableName, TrackingTable trackingTable, List<SchemaDataSet.ColumnsRow> targetTableColumns, StringBuilder sqlBuilder, AuditUpdateParameters updateParameters, Action<string, float?> reportProgress)
{
GenerateBuildDeltaProcedures(schema, tableName, trackingTable, targetTableColumns, sqlBuilder, updateParameters);
reportProgress(string.Format("Generated BuildDelta procedure for {0}º{1}", schema, tableName), null);
}
private static List<SchemaDataSet.ColumnsRow> GetTrackingColumnsForTable(TrackingTable trackingTable, AuditUpdateParameters updateParameters)
{
IEnumerable<SchemaDataSet.ColumnsRow> columnsRows = GetColumnsForTable(updateParameters.AuditingSchema, trackingTable.ToString(), updateParameters);
IEnumerable<SchemaDataSet.ColumnsRow> query = from columnRow in columnsRows
where !columnRow.COLUMN_NAME.Equals("TrackºId", StringComparison.Ordinal)
select columnRow;
return query.ToList();
}
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);
}
internal static void CreateDeltaProcedures(string schema, string tableName, TrackingTable trackingTable, List <SchemaDataSet.ColumnsRow> targetTableColumns, StringBuilder sqlBuilder, AuditUpdateParameters updateParameters, Action <string, float?> reportProgress)
{
GenerateBuildDeltaProcedures(schema, tableName, trackingTable, targetTableColumns, sqlBuilder, updateParameters);
reportProgress(string.Format("Generated BuildDelta procedure for {0}º{1}", schema, tableName), null);
}