private static void ValidateScriptAgainstRegex(string sql, RegexExpectedOutput expectedOutput)
{
if (expectedOutput == null)
{
// If expected output was null make sure we match the default values reges
Regex r = new Regex(@"INSERT INTO (.+) DEFAULT VALUES");
Match m = r.Match(sql);
Assert.True(m.Success);
// Table name matches
Assert.Equal(Common.TableName, m.Groups[1].Value);
}
else
{
// Do the whole validation
Regex r = new Regex(@"INSERT INTO (.+)\((.+)\) VALUES \((.+)\)");
Match m = r.Match(sql);
Assert.True(m.Success);
// Table name matches
Assert.Equal(Common.TableName, m.Groups[1].Value);
// In columns match
string cols = m.Groups[2].Value;
Assert.Equal(expectedOutput.ExpectedInColumns, cols.Split(',').Length);
// In values match
string vals = m.Groups[3].Value;
Assert.Equal(expectedOutput.ExpectedInValues, vals.Split(',').Length);
}
}
private static void ValidateCommandAgainstRegex(string sql, RegexExpectedOutput expectedOutput)
{
if (expectedOutput.ExpectedInColumns == 0 || expectedOutput.ExpectedInValues == 0)
{
// If expected output was null make sure we match the default values reges
Regex r = new Regex(@"INSERT INTO (.+) OUTPUT (.+) DEFAULT VALUES");
Match m = r.Match(sql);
Assert.True(m.Success);
// Table name matches
Assert.Equal(Common.TableName, m.Groups[1].Value);
// Output columns match
string[] outCols = m.Groups[2].Value.Split(", ");
Assert.Equal(expectedOutput.ExpectedOutColumns, outCols.Length);
Assert.All(outCols, col => Assert.StartsWith("inserted.", col));
}
else
{
// Do the whole validation
Regex r = new Regex(@"INSERT INTO (.+)\((.+)\) OUTPUT (.+) VALUES \((.+)\)");
Match m = r.Match(sql);
Assert.True(m.Success);
// Table name matches
Assert.Equal(Common.TableName, m.Groups[1].Value);
// Output columns match
string[] outCols = m.Groups[3].Value.Split(", ");
Assert.Equal(expectedOutput.ExpectedOutColumns, outCols.Length);
Assert.All(outCols, col => Assert.StartsWith("inserted.", col));
// In columns match
string[] inCols = m.Groups[2].Value.Split(", ");
Assert.Equal(expectedOutput.ExpectedInColumns, inCols.Length);
// In values match
string[] inVals = m.Groups[4].Value.Split(", ");
Assert.Equal(expectedOutput.ExpectedInValues, inVals.Length);
Assert.All(inVals, val => Assert.Matches(@"@.+\d+", val));
}
}
private static void ValidateCommandAgainstRegex(string sql, RegexExpectedOutput expectedOutput)
{
// Break the query into parts
string[] splitSql = sql.Split(Environment.NewLine);
Assert.Equal(3, splitSql.Length);
// Check the declare statement first
Regex declareRegex = new Regex(@"^DECLARE @(.+) TABLE \((.+)\)$");
Match declareMatch = declareRegex.Match(splitSql[0]);
Assert.True(declareMatch.Success);
// Declared table name matches
Assert.True(declareMatch.Groups[1].Value.StartsWith("Insert"));
Assert.True(declareMatch.Groups[1].Value.EndsWith("Output"));
// Correct number of columns in declared table
string[] declareCols = declareMatch.Groups[2].Value.Split(", ");
Assert.Equal(expectedOutput.ExpectedOutColumns, declareCols.Length);
// Check the insert statement in the middle
if (expectedOutput.ExpectedInColumns == 0 || expectedOutput.ExpectedInValues == 0)
{
// If expected output was null make sure we match the default values reges
Regex insertRegex = new Regex(@"^INSERT INTO (.+) OUTPUT (.+) INTO @(.+) DEFAULT VALUES$");
Match insertMatch = insertRegex.Match(splitSql[1]);
Assert.True(insertMatch.Success);
// Table name matches
Assert.Equal(Common.TableName, insertMatch.Groups[1].Value);
// Output columns match
string[] outCols = insertMatch.Groups[2].Value.Split(", ");
Assert.Equal(expectedOutput.ExpectedOutColumns, outCols.Length);
Assert.All(outCols, col => Assert.StartsWith("inserted.", col));
// Output table name matches
Assert.StartsWith("Insert", insertMatch.Groups[3].Value);
Assert.EndsWith("Output", insertMatch.Groups[3].Value);
}
else
{
// Do the whole validation
Regex insertRegex = new Regex(@"^INSERT INTO (.+)\((.+)\) OUTPUT (.+) INTO @(.+) VALUES \((.+)\)$");
Match insertMatch = insertRegex.Match(splitSql[1]);
Assert.True(insertMatch.Success);
// Table name matches
Assert.Equal(Common.TableName, insertMatch.Groups[1].Value);
// Output columns match
string[] outCols = insertMatch.Groups[3].Value.Split(", ");
Assert.Equal(expectedOutput.ExpectedOutColumns, outCols.Length);
Assert.All(outCols, col => Assert.StartsWith("inserted.", col));
// In columns match
string[] inCols = insertMatch.Groups[2].Value.Split(", ");
Assert.Equal(expectedOutput.ExpectedInColumns, inCols.Length);
// Output table name matches
Assert.StartsWith("Insert", insertMatch.Groups[4].Value);
Assert.EndsWith("Output", insertMatch.Groups[4].Value);
// In values match
string[] inVals = insertMatch.Groups[5].Value.Split(", ");
Assert.Equal(expectedOutput.ExpectedInValues, inVals.Length);
Assert.All(inVals, val => Assert.Matches(@"@.+\d+_\d+", val));
}
// Check the select statement last
Regex selectRegex = new Regex(@"^SELECT (.+) FROM @(.+)$");
Match selectMatch = selectRegex.Match(splitSql[2]);
Assert.True(selectMatch.Success);
// Correct number of columns in declared table
string[] selectCols = selectMatch.Groups[1].Value.Split(", ");
Assert.Equal(expectedOutput.ExpectedOutColumns, selectCols.Length);
// Declared table name matches
Assert.True(selectMatch.Groups[2].Value.StartsWith("Insert"));
Assert.True(selectMatch.Groups[2].Value.EndsWith("Output"));
}
public async Task GetCommand(bool includeIdentity, int defaultCols, int nullableCols, int valuesToSkip, RegexExpectedOutput expectedOutput)
{
// Setup:
// ... Generate the parameters for the row create
Common.TestDbColumnsWithTableMetadata data = new Common.TestDbColumnsWithTableMetadata(false, includeIdentity, defaultCols, nullableCols);
ResultSet rs = await Common.GetResultSet(data.DbColumns, includeIdentity);
// ... Mock db connection for building the command
var mockConn = new TestSqlConnection(null);
// ... Create a row create and set the appropriate number of cells
RowCreate rc = new RowCreate(100, rs, data.TableMetadata);
Common.AddCells(rc, valuesToSkip);
// If: I ask for the command for the row insert
DbCommand cmd = rc.GetCommand(mockConn);
// Then:
// ... The command should not be null
Assert.NotNull(cmd);
// ... There should be parameters in it
Assert.Equal(expectedOutput.ExpectedInValues, cmd.Parameters.Count);
// ... The script should match the expected regex output
ValidateCommandAgainstRegex(cmd.CommandText, expectedOutput);
}
public async Task GetScript(bool includeIdentity, int colsWithDefaultConstraints, int colsThatAllowNull, int valuesToSkipSetting, RegexExpectedOutput expectedOutput)
{
// Setup:
// ... Generate the parameters for the row create
Common.TestDbColumnsWithTableMetadata data = new Common.TestDbColumnsWithTableMetadata(false, includeIdentity, colsWithDefaultConstraints, colsThatAllowNull);
ResultSet rs = await Common.GetResultSet(data.DbColumns, includeIdentity);
// ... Create a row create and set the appropriate number of cells
RowCreate rc = new RowCreate(100, rs, data.TableMetadata);
Common.AddCells(rc, valuesToSkipSetting);
// If: I ask for the script for the row insert
string script = rc.GetScript();
// Then:
// ... The script should not be null
Assert.NotNull(script);
// ... The script should match the expected regex output
ValidateScriptAgainstRegex(script, expectedOutput);
}