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)); }