private void EmptyTable()
{
using (var writer = WriterDelegate.Invoke())
{
writer.WriteLine($"DELETE FROM {Table}");
StatementDelegate.Invoke(writer);
}
}
private void DropHistoryTable()
{
using (var writer = WriterDelegate.Invoke())
{
const string sql = "DROP TABLE {0};";
writer.WriteLine(sql, Settings.HistoryTable);
StatementDelegate.Invoke(writer);
}
}
private void DropPeriodConstraint()
{
using (var writer = WriterDelegate.Invoke())
{
const string sql = "ALTER TABLE {0} DROP PERIOD FOR SYSTEM_TIME;";
writer.WriteLine(sql, Settings.Table);
StatementDelegate.Invoke(writer);
}
}
private void DisableSystemVersioning()
{
using (var writer = WriterDelegate.Invoke())
{
const string sql = "ALTER TABLE {0} SET(SYSTEM_VERSIONING = OFF);";
writer.WriteLine(sql, Settings.Table);
StatementDelegate.Invoke(writer);
}
}
private void EnableSystemVersioning()
{
using (var writer = WriterDelegate.Invoke())
{
const string sql = "ALTER TABLE {0} SET(SYSTEM_VERSIONING = ON (HISTORY_TABLE = {1}, DATA_CONSISTENCY_CHECK = ON));";
writer.WriteLine(sql, Settings.Table, Settings.HistoryTable);
StatementDelegate.Invoke(writer);
}
}
private void CreatePeriodIndex()
{
using (var writer = WriterDelegate.Invoke())
{
const string sql = "ALTER TABLE {0} ADD PERIOD FOR SYSTEM_TIME ({1}, {2});";
writer.WriteLine(sql, Settings.Table, Settings.StartPeriodColumnName, Settings.EndPeriodColumnName);
StatementDelegate.Invoke(writer);
}
}
private void AddDefaultValues()
{
using (var writer = WriterDelegate.Invoke())
{
const string sql = "UPDATE {0} SET {1} = '{2}', {3} = '{4}';";
writer.WriteLine(sql, Settings.Table, Settings.StartPeriodColumnName, "19000101 00:00:00.0000000", Settings.EndPeriodColumnName, "99991231 23:59:59.9999999");
StatementDelegate.Invoke(writer);
}
}
private void DropPeriodColumns()
{
using (var writer = WriterDelegate.Invoke())
{
const string sql = "ALTER TABLE {0} DROP COLUMN {1};";
writer.WriteLine(sql, Settings.Table, Settings.StartPeriodColumnName);
writer.WriteLine(sql, Settings.Table, Settings.EndPeriodColumnName);
StatementDelegate.Invoke(writer);
}
}
private void AddPeriodColumnConstraints()
{
using (var writer = WriterDelegate.Invoke())
{
const string sql = "ALTER TABLE {0} ALTER COLUMN {1} [DATETIME2] NOT NULL;";
writer.WriteLine(sql, Settings.Table, Settings.StartPeriodColumnName);
writer.WriteLine(sql, Settings.Table, Settings.EndPeriodColumnName);
StatementDelegate.Invoke(writer);
}
}
private void PopulateDateDimension()
{
using (var writer = WriterDelegate.Invoke())
{
foreach (var dimension in GenerateDateDimensions())
{
string sql =
$@"INSERT INTO {Table}
(
[Date],
[Year],
[Month],
[Day],
[DayOfWeek],
[DayOfYear],
[DayOfWeekName],
[MonthName],
[Week],
[Suffix]
) VALUES (
'{dimension.Date.ToString("yyyy-MM-dd")}',
{dimension.Year},
{dimension.Month},
{dimension.Day},
{dimension.DayOfWeek},
{dimension.DayOfYear},
'{dimension.DayOfWeekName}',
'{dimension.MonthName}',
{dimension.Week},
'{dimension.Suffix}'
)";
writer.WriteLine(sql, Table, dimension.Date);
}
StatementDelegate.Invoke(writer);
}
}