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