public static MigrationBuilder EnableTemporalTable(
this MigrationBuilder migrationBuilder,
string table, string schema = null,
string historyTable = null, string historySchema = null,
string startColumn = null, string endColumn = null,
bool dataConsistencyCheck = false)
{
startColumn = startColumn ?? TemporalAnnotationNames.DefaultStartTime;
endColumn = endColumn ?? TemporalAnnotationNames.DefaultEndTime;
EnableTemporalTableOperation operation = new EnableTemporalTableOperation()
{
Name = table,
Schema = schema,
IsDestructiveChange = false,
HistorySchema = historySchema,
HistoryTable = historyTable,
SysStartDate = startColumn,
SysEndDate = endColumn,
DataConsistencyCheck = dataConsistencyCheck
};
migrationBuilder.Operations.Add(operation);
return(migrationBuilder);
}
protected override void Generate(CreateTableOperation operation, IndentedStringBuilder builder)
{
base.Generate(operation, builder);
if (operation.IsTemporalTable())
{
builder.AppendLine(";");
builder.AppendLine();
//"migrationBuilder" is hardcoded name in CSharpMigrationsGenerator class
builder.Append("migrationBuilder");
EnableTemporalTableOperation enableTemporalTableOperation = new EnableTemporalTableOperation()
{
Name = operation.Name,
Schema = operation.Schema,
HistoryTable = operation.GetHistoryTableName(),
HistorySchema = operation.GetHistoryTableSchema(),
SysStartDate = operation.GetSysStartColumnName(),
SysEndDate = operation.GetSysEndColumnName(),
DataConsistencyCheck = operation.DataConsistencyCheck()
};
this.Generate(enableTemporalTableOperation, builder);
}
}
protected override IEnumerable <MigrationOperation> Add(ITable target, DiffContext diffContext)
{
var _Entity = target.Model.Model.FindEntity(target.Name, target.Schema);
var _IsTemporal = _Entity.HasTemporalTable();
List <MigrationOperation> _Operations = new List <MigrationOperation>();
_Operations.AddRange(base.Add(target, diffContext).ToArray());
if (_IsTemporal == true)
{
for (int i = 0; i < _Operations.Count; i++)
{
if (_Operations[i] is CreateTableOperation createTableOperation)
{
string _HistorySchema = _Entity.GetHistoryTableSchema();
//Ensure schema for history table
if (_HistorySchema != null)
{
_Operations.AddRange(base.Add(_HistorySchema, diffContext));
}
//on top of "CREATE TABLE" operaion, we add additional operation to convert
//normal table into temporal table.
EnableTemporalTableOperation enableTemporalTableOperation = new EnableTemporalTableOperation()
{
Name = _Entity.GetTableName(),
Schema = _Entity.GetSchema(),
HistoryTable = _Entity.GetHistoryTableName(),
HistorySchema = _Entity.GetHistoryTableSchema(),
SysStartDate = _Entity.GetStartDateColumnName(),
SysEndDate = _Entity.GetEndDateColumnName(),
DataConsistencyCheck = _Entity.DataConsistencyCheck()
};
_Operations.Add(enableTemporalTableOperation);
}
}
}
return(_Operations);
}
protected override IEnumerable <MigrationOperation> Diff(ITable source, ITable target, DiffContext diffContext)
{
var _SourceEntity = source.Model.Model.FindEntity(source.Name, source.Schema);
var _TargetEntity = target.Model.Model.FindEntity(target.Name, target.Schema);
var _SourceIsTemporal = _SourceEntity.HasTemporalTable();
var _TargetIsTemporal = _TargetEntity.HasTemporalTable();
List <MigrationOperation> _Operations = new List <MigrationOperation>();
_Operations.AddRange(base.Diff(source, target, diffContext).ToArray());
if (_SourceIsTemporal == false && _TargetIsTemporal == true)
{
EnableTemporalTableOperation enableTemporal = new EnableTemporalTableOperation()
{
Name = _TargetEntity.GetTableName(),
Schema = _TargetEntity.GetSchema(),
HistoryTable = _TargetEntity.GetHistoryTableName(),
HistorySchema = _TargetEntity.GetHistoryTableSchema(),
SysStartDate = _TargetEntity.GetStartDateColumnName(),
SysEndDate = _TargetEntity.GetEndDateColumnName(),
DataConsistencyCheck = _TargetEntity.DataConsistencyCheck()
};
if (enableTemporal.HistorySchema != null)
{
//Ensure schema for history table.
_Operations.AddRange(base.Add(enableTemporal.HistorySchema, diffContext));
}
_Operations.Add(enableTemporal);
}
else if (_SourceIsTemporal == true && _TargetIsTemporal == false)
{
DisableTemporalTableOperation disableTemporal = new DisableTemporalTableOperation()
{
Name = _SourceEntity.GetTableName(),
Schema = _SourceEntity.GetSchema()
};
////Don't drop the Start/End date column when we detect history tracking is disabled.
//var _TemporalStartColumn = _SourceEntity.GetStartDateColumnName();
//var _TemporalEndColumn = _SourceEntity.GetEndDateColumnName();
//for (int i = 0; i < _Operations.Count; i++)
//{
// if (_Operations[i] is DropColumnOperation dropColumnOperation)
// {
// if (dropColumnOperation.Table == _SourceEntity.GetTableName() &&
// (dropColumnOperation.Name == _TemporalStartColumn || dropColumnOperation.Name == _TemporalEndColumn))
// {
// _Operations.RemoveAt(i);
// i--;
// }
// }
//}
_Operations.Add(disableTemporal);
}
return(_Operations);
}
protected virtual void Generate(EnableTemporalTableOperation operation, IndentedStringBuilder builder)
{
builder.AppendLine($".{nameof(MigrationBuilderExtensions.EnableTemporalTable)}(");
using (builder.Indent())
{
builder
.Append("table: ")
.Append(Code.Literal(operation.Name));
if (operation.Schema != null)
{
builder
.AppendLine(",")
.Append("schema: ")
.Append(Code.Literal(operation.Schema));
}
if (operation.HistorySchema != null)
{
builder
.AppendLine(",")
.Append("historySchema: ")
.Append(Code.Literal(operation.HistorySchema));
}
if (operation.HistoryTable != null)
{
builder
.AppendLine(",")
.Append("historyTable: ")
.Append(Code.Literal(operation.HistoryTable));
}
if (operation.SysStartDate != null)
{
builder
.AppendLine(",")
.Append("startColumn: ")
.Append(Code.Literal(operation.SysStartDate));
}
if (operation.SysEndDate != null)
{
builder
.AppendLine(",")
.Append("endColumn: ")
.Append(Code.Literal(operation.SysEndDate));
}
if (operation.DataConsistencyCheck)
{
builder
.AppendLine(",")
.Append("dataConsistencyCheck: ")
.Append(Code.Literal(operation.DataConsistencyCheck));
}
}
builder.Append(")");
}
protected virtual void Generate(EnableTemporalTableOperation operation, IModel model, MigrationCommandListBuilder builder)
{
string _SchemaQualifiedTableName =
Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Name, operation.Schema);
string _StartDate = operation.SysStartDate;
string _EndDate = operation.SysEndDate;
builder.AppendLine("IF NOT EXISTS(");
using (builder.Indent())
{
builder
.AppendLine("SELECT NULL FROM sys.[columns]")
.AppendLine($"WHERE OBJECT_ID('{_SchemaQualifiedTableName}') = [object_id] AND [name] = '{_StartDate}'");
}
builder.AppendLine(") AND NOT EXISTS(");
using (builder.Indent())
{
builder
.AppendLine("SELECT NULL FROM sys.[columns]")
.AppendLine($"WHERE OBJECT_ID('{_SchemaQualifiedTableName}') = [object_id] AND [name] = '{_EndDate}'");
}
builder
.AppendLine(")")
.AppendLine("BEGIN");
using (builder.Indent())
{
builder
.Append("ALTER TABLE ")
.AppendLine(_SchemaQualifiedTableName)
.AppendLine("ADD ");
using (builder.Indent())
{
builder
.Append($"{_StartDate} DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN")
.AppendLine();
using (builder.Indent())
{
builder
.Append("CONSTRAINT ")
.Append($"DF_{operation.Name}_{_StartDate}")
.Append(" DEFAULT (SYSUTCDATETIME())")
.AppendLine(",");
}
builder
.Append($"{_EndDate} DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN")
.AppendLine();
using (builder.Indent())
{
builder
.Append("CONSTRAINT ")
.Append($"DF_{operation.Name}_{_EndDate}")
.Append(" DEFAULT('9999-12-31 23:59:59.9999999')")
.AppendLine(",");
}
builder.Append($"PERIOD FOR SYSTEM_TIME([{_StartDate}], [{_EndDate}])");
}
}
builder
.AppendLine()
.AppendLine("END")
.EndCommand();
builder.AppendLine("IF NOT EXISTS(SELECT NULL FROM sys.[periods]");
using (builder.Indent())
{
builder.AppendLine($"WHERE [object_id] = OBJECT_ID('{_SchemaQualifiedTableName}'))");
}
builder.AppendLine("BEGIN");
using (builder.Indent())
{
builder.AppendLine($"EXEC('ALTER TABLE {_SchemaQualifiedTableName} ADD PERIOD FOR SYSTEM_TIME([{_StartDate}], [{_EndDate}])')");
}
builder
.AppendLine("END")
.EndCommand();
builder
.AppendLine($"ALTER TABLE {_SchemaQualifiedTableName} SET(");
using (builder.Indent())
{
builder.AppendLine("SYSTEM_VERSIONING = ON (");
using (builder.Indent())
{
builder
.Append($"HISTORY_TABLE = ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.HistoryTable, operation.HistorySchema ?? TemporalAnnotationNames.DefaultSchema));
if (operation.DataConsistencyCheck)
{
builder
.AppendLine(",")
.AppendLine("DATA_CONSISTENCY_CHECK = ON");
}
}
builder.AppendLine(")");
}
builder.AppendLine(")").EndCommand();
}
