public async Task <string> Value(IScriptVariables variables) { var mergeStatement = new List <string> { $"DELETE", $" mrg_table", $"FROM" }; var matchOn = variables.MergeIdentifierColumns.Select(c => c.Name); var deleteOn = variables.DeleteIdentifierColumns.Select(c => c.Name); var deleteOnFactId = matchOn.Where(m => !deleteOn.Any(d => d.Equals(m))).FirstOrDefault(); mergeStatement.AddRange(new List <string> { $" [{variables.SchemaName}].[{variables.Table.Name}] mrg_table", $" INNER JOIN @InsertedValues iv_inner ON mrg_table.{matchOn.Where(m => !deleteOn.Any(d => d.Equals(m))).FirstOrDefault()} = iv_inner.{matchOn.Where(m => !deleteOn.Any(d => d.Equals(m))).FirstOrDefault()}", $" LEFT JOIN @InsertedValues iv_outer ON mrg_table.{variables.Table.Name}Id = iv_outer.{variables.Table.Name}Id", $"WHERE", $" iv_outer.{variables.Table.Name}Id IS NULL" }); var scriptLines = await _scriptLineFactory.LinesFrom( 1, mergeStatement.ToArray() ); return(await _scriptLineFactory.StringFrom(scriptLines)); }
public async Task <string> Value(IScriptVariables variables) { var matchOn = variables.MergeIdentifierColumns.Select(c => c.Name); var insertValues = new List <string> { "DECLARE @InsertedValues TABLE (", $" [{variables.Table.Name}Id] [int]," }; var columnLines = variables.Table.Columns.Where(c => matchOn.Any(m => m.Equals(c.Name))); foreach (var columnLine in columnLines) { var line = $" [{columnLine.Name}] {DataType(columnLine)}"; if (columnLine != columnLines.Last()) { line += ","; } insertValues.Add(line); } insertValues.Add(")"); var scriptLines = await _scriptLineFactory.LinesFrom( 1, insertValues.ToArray() ); return(await _scriptLineFactory.StringFrom(scriptLines)); }
public async Task <string> Value(IScriptVariables variables) { var matchOn = variables.MergeIdentifierColumns.Select(c => c.Name); var usingType = (variables.TablesToJoin != null && variables.TablesToJoin.Any()) ? $"{variables.ProcedureName}Variable" : $"{variables.TableTypeVariableName()}"; var mergeStatement = new List <string> { $"MERGE [{variables.SchemaName}].[{variables.Table.Name}] AS t", $"USING @{usingType} AS s", $"ON (" }; foreach (var match in matchOn) { var line = $" t.[{match}]=s.[{match}]"; if (match != matchOn.Last()) { line += " AND"; } mergeStatement.Add(line); } mergeStatement.Add(")"); var scriptLines = await _scriptLineFactory.LinesFrom( 1, mergeStatement.ToArray() ); return(await _scriptLineFactory.StringFrom(scriptLines)); }
public async Task <string> Value(IScriptVariables variables) { var matchOn = variables.MergeIdentifierColumns.Select(c => c.Name); var updateColumns = variables.Table.Columns.Where(c => !matchOn.Any(m => m.Equals(c.Name, StringComparison.InvariantCultureIgnoreCase))).Where(c => !c.IdentityColumn); var mergeStatement = new List <string> { $"WHEN MATCHED", $" THEN UPDATE SET" }; foreach (var column in updateColumns) { var line = $" t.[{column.Name}]=s.[{column.Name}]"; if (column != updateColumns.Last()) { line += ","; } mergeStatement.Add(line); } var scriptLines = await _scriptLineFactory.LinesFrom( 1, mergeStatement.ToArray() ); return(await _scriptLineFactory.StringFrom(scriptLines)); }
public async Task <string> Value(IScriptVariables variables) { var scriptLines = await _scriptLineFactory.LinesFrom( 0, $"IF EXISTS (SELECT 1 FROM [sys].[objects] WHERE [object_id] = OBJECT_ID(N'[{variables.SchemaName}].[{variables.ProcedureName()}]') AND [type] IN (N'P'))", "BEGIN", $" DROP PROCEDURE [{variables.SchemaName}].[{variables.ProcedureName()}]", "END", "GO"); return(await _scriptLineFactory.StringFrom(scriptLines)); }
public async Task <string> Value(IScriptVariables variables) { var scriptLines = await _scriptLineFactory.LinesFrom( 0, $"IF EXISTS (SELECT 1 FROM [sys].[types] st JOIN [sys].[schemas] ss ON st.schema_id = ss.schema_id WHERE st.name = N'{variables.TableTypeName()}' AND ss.name = N'{variables.SchemaName}')", "BEGIN", $" DROP TYPE [{variables.SchemaName}].[{variables.TableTypeName()}]", "END", "GO"); return(await _scriptLineFactory.StringFrom(scriptLines)); }
public async Task <string> Value(IScriptVariables variables) { var scriptLines = await _scriptLineFactory.LinesFrom( 0, $"IF NOT EXISTS (SELECT 1 FROM [sys].[objects] WHERE [object_id] = OBJECT_ID(N'[{variables.SchemaName}].[{variables.ProcedureName()}]') AND [type] IN (N'P'))", "BEGIN", $" EXEC [dbo].[sp_executesql] @statement = N'CREATE PROCEDURE [{variables.SchemaName}].[{variables.ProcedureName()}] AS '", "END", "GO" ); return(await _scriptLineFactory.StringFrom(scriptLines)); }
public async Task <string> Value(IScriptVariables variables) { var scriptLines = await _scriptLineFactory.LinesFrom( 0, "SET NOCOUNT ON", "GO", "SET ANSI_NULLS ON", "GO", "SET QUOTED_IDENTIFIER ON", "GO"); return(await _scriptLineFactory.StringFrom(scriptLines)); }
public async Task <string> Value(IScriptVariables variables) { var scriptLines = await _scriptLineFactory.LinesFrom( 0, "END", "GO", "", $"ALTER AUTHORIZATION ON [{variables.SchemaName}].[{variables.ProcedureName()}] TO SCHEMA OWNER", "GO" ); return(await _scriptLineFactory.StringFrom(scriptLines)); }
public async Task <string> Value(IScriptVariables variables) { var mergeStatement = new List <string> { $"WHEN NOT MATCHED BY SOURCE AND t.{variables.RetentionPolicy.ComparisonColumn} < DATEADD(MONTH, -{variables.RetentionPolicy.RetentionMonths}, GETUTCDATE())", " THEN DELETE" }; var scriptLines = await _scriptLineFactory.LinesFrom( 1, mergeStatement.ToArray() ); return(await _scriptLineFactory.StringFrom(scriptLines)); }
public async Task <string> Value(IScriptVariables variables) { var scriptLines = await _scriptLineFactory.LinesFrom( 0, $"ALTER PROCEDURE [{variables.SchemaName}].[{variables.ProcedureName()}]", $" @{variables.TableTypeVariableName()} [{variables.SchemaName}].[{variables.TableTypeName()}] READONLY", "AS", "BEGIN", "", " SET NOCOUNT ON;", " SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;" ); return(await _scriptLineFactory.StringFrom(scriptLines)); }
public async Task <string> Value(IScriptVariables variables) { var mergeStatement = new List <string> { $"/** End of merge statement **/", $";" }; var scriptLines = await _scriptLineFactory.LinesFrom( 1, mergeStatement.ToArray() ); return(await _scriptLineFactory.StringFrom(scriptLines)); }
public async Task <string> Value(IScriptVariables variables) { var createTypeLines = new List <string> { $"IF NOT EXISTS (SELECT 1 FROM [sys].[types] st JOIN [sys].[schemas] ss ON st.schema_id = ss.schema_id WHERE st.name = N'{variables.TableTypeName()}' AND ss.name = N'{variables.SchemaName}')", "BEGIN", $" CREATE TYPE [{variables.SchemaName}].[{variables.TableTypeName()}] AS TABLE(" }; foreach (var column in variables.TableTypeColumns.OrderBy(c => c.Name)) { var columnDescription = $" [{column.Name}] {DataType(column)} {NullableColumn(column)}"; if (column != variables.TableTypeColumns.OrderBy(c => c.Name).Last()) { columnDescription += ","; } createTypeLines.Add(columnDescription); } createTypeLines.AddRange( new List <string> { " )", "END", "GO", "", $"ALTER AUTHORIZATION ON TYPE::[{variables.SchemaName}].[{variables.TableTypeName()}] TO SCHEMA OWNER", "GO" }); var scriptLines = await _scriptLineFactory.LinesFrom( 0, createTypeLines.ToArray() ); return(await _scriptLineFactory.StringFrom(scriptLines)); }
public async Task <string> Value(IScriptVariables variables) { var mergeStatement = new List <string> { $"OUTPUT", $" inserted.{variables.Table.Name}Id," }; var matchOn = variables.MergeIdentifierColumns.Select(c => c.Name); var deleteOn = variables.DeleteIdentifierColumns.Select(c => c.Name); var deleteOnFactId = matchOn.Where(m => !deleteOn.Any(d => d.Equals(m))).FirstOrDefault(); var insertedColumns = variables.Table.Columns.Where(c => matchOn.Any(m => m.Equals(c.Name))); foreach (var column in insertedColumns) { var line = $" inserted.{column.Name}"; if (column != insertedColumns.Last()) { line += ","; } mergeStatement.Add(line); } mergeStatement.Add("INTO @InsertedValues"); var scriptLines = await _scriptLineFactory.LinesFrom( 1, mergeStatement.ToArray() ); return(await _scriptLineFactory.StringFrom(scriptLines)); }
public async Task <string> Value(IScriptVariables variables) { var columns = variables.Table.Columns.Where(c => !c.IdentityColumn); var tableVariableLines = new List <string> { $"DECLARE @{variables.ProcedureName}Variable TABLE", "(" }; foreach (var column in columns) { var line = $" [{column.Name}] {DataType(column)} {NullableColumn(column)}"; if (column != columns.Last()) { line += ","; } tableVariableLines.Add(line); } tableVariableLines.AddRange(new List <string> { ")", $"INSERT INTO @{variables.ProcedureName}Variable", "SELECT" }); foreach (var column in columns) { var line = $" [{column.Name}]"; if (column != columns.Last()) { line += ","; } tableVariableLines.Add(line); } tableVariableLines.AddRange(new List <string> { "FROM", $" @{variables.TableTypeVariableName()} AS tt" }); foreach (var table in variables.TablesToJoin) { var joinColumns = string.Join(" AND ", table.selectColumns.Select(c => $"tt.[{c.Name}]=alias_{table.joinTable.Name.ToLower()}.[{c.Name}]")); var line = $" INNER JOIN [{variables.SchemaName}].[{table.joinTable.Name}] AS alias_{table.joinTable.Name.ToLower()} ON {joinColumns}"; tableVariableLines.Add(line); } var scriptLines = await _scriptLineFactory.LinesFrom( 1, tableVariableLines.ToArray() ); return(await _scriptLineFactory.StringFrom(scriptLines)); }
public async Task <string> Value(IMergeScriptVariableSet variableSet) { var matchOn = variableSet.MergeIdentifierColumns.Select(c => c.Name).ToList(); var deleteOn = variableSet.DeleteIdentifierColumns.Select(c => c.Name); var deleteOnFactId = matchOn.Where(m => !deleteOn.Any(d => d.Equals(m))).FirstOrDefault(); var inserts = true; var softDeleteColumn = variableSet.Table.SoftDeleteColumn(); if (softDeleteColumn != null) { matchOn.Add(softDeleteColumn.Name); } var updates = !matchOn.Count().Equals(variableSet.Table.Columns.Count) && !variableSet.Table.Columns.Where(c => !c.IdentityColumn).Select(c => c.Name).All(n => matchOn.Any(m => m.Equals(n, StringComparison.InvariantCultureIgnoreCase))); var deletes = softDeleteColumn != null; var retention = !string.IsNullOrWhiteSpace(variableSet.RetentionPolicy.ComparisonColumn); var retentionAmount = retention ? $"({variableSet.RetentionPolicy.RetentionMonths} months)" : ""; var tableTypeFields = new List <string>(); foreach (var column in variableSet.TableTypeColumns.OrderBy(c => c.Name)) { var columnDescription = $" ** [{column.Name}] {column.DataType()} {column.NullableColumn()}"; if (column != variableSet.TableTypeColumns.OrderBy(c => c.Name).Last()) { columnDescription += ","; } tableTypeFields.Add(columnDescription); } var dependantTables = new List <string>(); foreach (var dependantTable in variableSet.TablesToJoin) { var table = $" ** [{variableSet.Schema.Name}].[{dependantTable.joinTable.Name}]"; if (dependantTable != variableSet.TablesToJoin.Last()) { table += ","; } dependantTables.Add(table); } var aboutText = new List <string> { $" ** [{variableSet.Schema.Name}].[{variableSet.ScriptName}]", $" **", $" ** Expects table type parameter: @{variableSet.TableTypeVariableName} as", $" ** [{variableSet.Schema.Name}].[{variableSet.TableTypeName}] (" }; aboutText.AddRange(tableTypeFields); aboutText.AddRange(new List <string> { $" ** )", $" **", $" ** Merges data into table:", $" ** [{variableSet.Schema.Name}].[{variableSet.Table.Name}]" }); if (dependantTables.Any()) { aboutText.Add(" **"); aboutText.Add(" ** Merge requires data from:"); aboutText.AddRange(dependantTables); } var assemblyValue = Assembly .GetEntryAssembly()? .CustomAttributes .FirstOrDefault(a => a.AttributeType == typeof(AssemblyInformationalVersionAttribute))? .ConstructorArguments .FirstOrDefault() .Value; var softDeleteColumnValue = ""; if (softDeleteColumn != null) { softDeleteColumnValue = $" (column: [{softDeleteColumn.Name}])"; } aboutText.AddRange(new List <string> { $"#pad", $" ** Inserts: {inserts}", $" ** Updates: {updates}", $" ** Deletes: {deletes}{softDeleteColumnValue}", $" ** Retention policy: {retention} {retentionAmount}", $"#pad", $" ** Gaspra.Functions v{assemblyValue}" }); var affix = " **"; var longestLine = aboutText .Select(t => t.Length) .OrderByDescending(t => t) .First(); var start = "/" + new string('*', longestLine + 2); var end = " " + new string('*', longestLine + 2) + "/"; var aboutLines = new List <string> { start }; aboutLines .AddRange(aboutText.Select(t => { if (!t.Equals("#pad")) { return(t + new string(' ', longestLine - t.Length) + affix); } else { return(" " + new string('*', longestLine + 2 )); } })); aboutLines.Add(end); var scriptLines = await _scriptLineFactory.LinesFrom( 0, aboutLines.ToArray()); return(await _scriptLineFactory.StringFrom(scriptLines)); }