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> ScriptFrom(IScriptVariables variables)
{
var deepestOrder = _scriptSections
.Select(s => s.Order.Values.Length)
.OrderByDescending(s => s)
.First();
var flattenedOrders = _scriptSections.Select(s =>
{
var orderLengthAffix = new string('0', deepestOrder - s.Order.Values.Length);
var flatOrder = string.Join("", s.Order.Values) + orderLengthAffix;
var flattenedOrder = int.Parse(flatOrder);
return(flattenedOrder, s);
})
.ToList();
var sections = flattenedOrders
.OrderBy(f => f.flattenedOrder)
.Select(f => f.s);
var script = "";
foreach (var section in sections)
{
if (await section.Valid(variables))
{
script += $"{await section.Value(variables)}{Environment.NewLine}{Environment.NewLine}";
}
}
return(script);
}
public Task <bool> Valid(IScriptVariables variables)
{
return(Task.FromResult(
!string.IsNullOrWhiteSpace(variables.SchemaName) &&
!string.IsNullOrWhiteSpace(variables.ProcedureName()) &&
!string.IsNullOrWhiteSpace(variables.TableTypeName())));
}
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 Task <bool> Valid(IScriptVariables variables)
{
var matchOn = variables.MergeIdentifierColumns.Select(c => c.Name);
return(Task.FromResult(
!matchOn.Count().Equals(variables.Table.Columns.Count) &&
!variables.Table.Columns.Where(c => !c.IdentityColumn).Select(c => c.Name).All(n => matchOn.Any(m => m.Equals(n, StringComparison.InvariantCultureIgnoreCase)))));
}
public Task <bool> Valid(IScriptVariables variables)
{
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();
return(Task.FromResult(!string.IsNullOrWhiteSpace(deleteOnFactId) && deleteOn.Any()));
}
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 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,
"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 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,
$"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 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 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>
{
$"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 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 Task <bool> Valid(IScriptVariables variables)
{
return(Task.FromResult(true));
}
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 Task <bool> Valid(IScriptVariables variables)
{
return(Task.FromResult(
variables.TablesToJoin != null &&
variables.TablesToJoin.Any()));
}
public Task <bool> Valid(IScriptVariables variables)
{
return(Task.FromResult(variables.RetentionPolicy != null));
}