public static string WrapInExistingTableHandling(CreateIfExistsModification createIfExistsModification,
DbTableName dbTableName,
string createTablePortion)
{
switch (createIfExistsModification)
{
case CreateIfExistsModification.CreateAnyway:
return(createTablePortion);
case CreateIfExistsModification.DropAndRecreate:
return($@"
DROP TABLE IF EXISTS {dbTableName};
{createTablePortion}");
case CreateIfExistsModification.CreateIfNotExists:
return($@"
IF (OBJECT_ID('{dbTableName}', 'U')) IS NULL
BEGIN
{createTablePortion}
END
");
default:
throw new ArgumentOutOfRangeException(nameof(createIfExistsModification),
createIfExistsModification, null);
}
}
/// <summary>
/// Returns the SQL create script to create the temporal table requested.
/// </summary>
/// <param name="sqlTable"></param>
/// <param name="schemaName"></param>
/// <param name="tableName"></param>
/// <returns></returns>
public static string GetScript(SqlTableForTemporal sqlTable,
string schemaName = "dbo",
string?tableName = null)
{
tableName ??= sqlTable.Name;
var dbTableName = new DbTableName(schemaName, tableName);
var regularColumns = CreateNormalColumns(sqlTable);
var temporalColumns = GetTemporalColumns(sqlTable);
var primaryKeyConstraint = CreatePrimaryKeyConstraint(sqlTable, tableName);
var historyTableLink = CreateHistoryTableLink(sqlTable, schemaName, tableName);
var indexCreation = CreateIndexText(sqlTable, dbTableName);
/*
* A SQL temporal table is made up of:
* - the regular columns of the table
* - the required temporal columns for validto/validfrom
* - temporal tables won't create without a primary key constraint
* - history table linkage and naming.
*/
var script = $@"
CREATE TABLE {dbTableName} (
{regularColumns}{temporalColumns}
{primaryKeyConstraint}
)
{historyTableLink};
{indexCreation}"
.RemoveEmptyLines();
return(script);
}
/// <summary>
/// Creates a history table link if there is a primary key on the main table.
/// </summary>
/// <param name="sqlTable"></param>
/// <param name="schemaName"></param>
/// <param name="tableName"></param>
/// <returns></returns>
private static string CreateHistoryTableLink(SqlTableForTemporal sqlTable, string schemaName, string tableName)
{
if (!sqlTable.HasPrimaryKey())
{
return(string.Empty);
}
var historyTableName = $"{tableName}_History".BracketizeSafe();
var dbTableName = new DbTableName(schemaName, historyTableName);
return($"WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = {dbTableName}))");
}
private static string CreateIndexText(SqlTableForTemporal sqlTableForTemporal, DbTableName dbTableName)
{
if (sqlTableForTemporal.IndexAnnotations.Count == 0)
{
return(String.Empty);
}
var newIndexStatements = sqlTableForTemporal
.IndexAnnotations
.Select(ia => IndexDmlScriptCreator.GetScript(ia))
.StringJoin(Environment.NewLine);
return($"{newIndexStatements}");
}
public static string CreateTableScript(string schemaName,
string tableName,
IList <SISColumn> columns,
CreateIfExistsModification ifExists = CreateIfExistsModification.CreateAnyway)
{
var dbTableName = new DbTableName(schemaName, tableName);
var sb = new StringBuilder();
sb.Append($"CREATE TABLE {dbTableName} (\r\n");
AddColumnDefinitions(sb, columns);
sb.AppendLine(");");
var createTablePortion = sb.ToString();
var totalCreateScript = WrapInExistingTableHandling(ifExists, dbTableName, createTablePortion);
return(totalCreateScript);
}
/// <summary>
/// <para>Implementation of <see cref="IEquatable{T}"/> for <see cref="SQLQuery"/>.</para>
/// <para>Returns true if <see cref="SQLQuery"/> references the same <see cref="object"/> or its members are equal.</para>
/// </summary>
/// <param name="other"></param>
/// <returns></returns>
public bool Equals(SQLQuery <T> other)
{
if (other is null)
{
return(false);
}
if (ReferenceEquals(this, other))
{
return(true);
}
return(SQL.Equals(other.SQL) &&
DbTableName.Equals(other.DbTableName) &&
TObjects.Equals(other.TObjects) &&
CommandType.Equals(other.CommandType) &&
IsBulk.Equals(other.IsBulk) &&
BatchSize.Equals(other.BatchSize));
}
public string NameFor(DbTableName table)
{
return(table.Name.CleanUp().ToSingular(Pluralizer));
}
