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 GetCommand(bool includeIdentity, bool isMemoryOptimized)
{
// Setup:
// ... Create a row update with cell updates
var columns = Common.GetColumns(includeIdentity);
var rs = await Common.GetResultSet(columns, includeIdentity);
var etm = Common.GetStandardMetadata(columns, isMemoryOptimized);
RowUpdate ru = new RowUpdate(0, rs, etm);
Common.AddCells(ru, includeIdentity);
// ... Mock db connection for building the command
var mockConn = new TestSqlConnection(null);
// If: I ask for a command to be generated for update
DbCommand cmd = ru.GetCommand(mockConn);
// Then:
// ... The command should not be null
Assert.NotNull(cmd);
// ... There should be an appropriate number of parameters in it
// (1 or 3 keys, 3 value parameters)
int expectedKeys = includeIdentity ? 1 : 3;
Assert.Equal(expectedKeys + 3, cmd.Parameters.Count);
// ... It should be formatted into an update script with output
string regexFormat = isMemoryOptimized
? @"UPDATE (.+) WITH \(SNAPSHOT\) SET (.+) OUTPUT (.+) WHERE (.+)"
: @"UPDATE (.+) SET (.+) OUTPUT(.+) WHERE (.+)";
Regex r = new Regex(regexFormat);
var m = r.Match(cmd.CommandText);
Assert.True(m.Success);
// ... There should be a table
string tbl = m.Groups[1].Value;
Assert.Equal(etm.EscapedMultipartName, tbl);
// ... There should be 3 parameters for input
string[] inCols = m.Groups[2].Value.Split(',');
Assert.Equal(3, inCols.Length);
Assert.All(inCols, s => Assert.Matches(@"\[.+\] = @Value\d+", s));
// ... There should be 3 OR 4 columns for output
string[] outCols = m.Groups[3].Value.Split(',');
Assert.Equal(includeIdentity ? 4 : 3, outCols.Length);
Assert.All(outCols, s => Assert.StartsWith("inserted.", s.Trim()));
// ... There should be 1 OR 3 columns for where components
string[] whereComponents = m.Groups[4].Value.Split(new[] { "AND" }, StringSplitOptions.None);
Assert.Equal(expectedKeys, whereComponents.Length);
Assert.All(whereComponents, s => Assert.Matches(@"\(.+ = @Param\d+\)", s));
}
public async Task GetCommandMissingCell()
{
// Setup: Generate the parameters for the row create
RowCreate rc = await GetStandardRowCreate();
var mockConn = new TestSqlConnection(null);
// If: I ask for a script to be generated without setting any values
// Then: An exception should be thrown for missing cells
Assert.Throws <InvalidOperationException>(() => rc.GetCommand(mockConn));
}
public async Task GetVerifyQuery()
{
// Setup: Create a row update and set the first row cell to have values
// ... other than "1" for testing purposes (simulated select query result).
Common.TestDbColumnsWithTableMetadata data = new Common.TestDbColumnsWithTableMetadata(false, false, 0, 0);
var rs = await Common.GetResultSet(data.DbColumns, false);
RowUpdate ru = new RowUpdate(0, rs, data.TableMetadata);
object[][] rows =
{
new object[] { "2", "0", "0" },
};
var testResultSet = new TestResultSet(data.DbColumns, rows);
var newRowReader = new TestDbDataReader(new[] { testResultSet }, false);
await ru.ApplyChanges(newRowReader);
// ... Create a row delete.
RowDelete rd = new RowDelete(0, rs, data.TableMetadata);
int expectedKeys = 3;
// If: I generate a verify command
String verifyCommand = rd.GetVerifyScript();
// Then:
// ... The command should not be null
Assert.NotNull(verifyCommand);
// ... It should be formatted into an where script
string regexTest = @"SELECT COUNT \(\*\) FROM (.+) WHERE (.+)";
Regex r = new Regex(regexTest);
var m = r.Match(verifyCommand);
Assert.True(m.Success);
// ... There should be a table
string tbl = m.Groups[1].Value;
Assert.Equal(data.TableMetadata.EscapedMultipartName, tbl);
// ... There should be as many where components as there are keys
string[] whereComponents = m.Groups[2].Value.Split(new[] { "AND" }, StringSplitOptions.None);
Assert.Equal(expectedKeys, whereComponents.Length);
// ... Mock db connection for building the command
var mockConn = new TestSqlConnection(new[] { testResultSet });
// If: I attempt to get a command for a simulated delete of a row with duplicates.
// Then: The Command will throw an exception as it detects there are
// ... 2 or more rows with the same value in the simulated query results data.
Assert.Throws <EditDataDeleteException>(() => rd.GetCommand(mockConn));
}
public async Task GetCommand(bool includeIdentity)
{
// Setup:
// ... Create a row create with cell updates
const long rowId = 100;
var columns = Common.GetColumns(includeIdentity);
var rs = await Common.GetResultSet(columns, includeIdentity);
var etm = Common.GetStandardMetadata(columns);
RowCreate rc = new RowCreate(rowId, rs, etm);
Common.AddCells(rc, includeIdentity);
// ... Mock db connection for building the command
var mockConn = new TestSqlConnection(null);
// If: I attempt to get a command for the edit
DbCommand cmd = rc.GetCommand(mockConn);
// Then:
// ... The command should not be null
Assert.NotNull(cmd);
// ... There should be parameters in it
Assert.Equal(3, cmd.Parameters.Count);
// ... It should be formatted into an insert script with output
Regex r = new Regex(@"INSERT INTO (.+)\((.+)\) OUTPUT (.+) VALUES \((.+)\)");
var m = r.Match(cmd.CommandText);
Assert.True(m.Success);
// ... There should be a table
string tbl = m.Groups[1].Value;
Assert.Equal(etm.EscapedMultipartName, tbl);
// ... There should be 3 columns for input
string inCols = m.Groups[2].Value;
Assert.Equal(3, inCols.Split(',').Length);
// ... There should be 3 OR 4 columns for output that are inserted.
string[] outCols = m.Groups[3].Value.Split(',');
Assert.Equal(includeIdentity ? 4 : 3, outCols.Length);
Assert.All(outCols, s => Assert.StartsWith("inserted.", s.Trim()));
// ... There should be 3 parameters
string[] param = m.Groups[4].Value.Split(',');
Assert.Equal(3, param.Length);
Assert.All(param, s => Assert.StartsWith("@Value", s.Trim()));
}
public async Task CommitNullFailureHandler()
{
// Setup:
// ... Create a basic session
EditSession s = await GetBasicSession();
// ... Mock db connection
DbConnection conn = new TestSqlConnection(null);
// If: I attempt to commit with a null success handler
// Then: I should get an exception
Assert.Throws <ArgumentNullException>(() => s.CommitEdits(conn, () => Task.CompletedTask, null));
}
public async Task GetCommand(bool includeIdentity, bool isMemoryOptimized)
{
// Setup:
// ... Create a row delete
var columns = Common.GetColumns(includeIdentity);
var rs = await Common.GetResultSet(columns, includeIdentity);
var etm = Common.GetStandardMetadata(columns, isMemoryOptimized);
RowDelete rd = new RowDelete(0, rs, etm);
// ... Mock db connection for building the command
var mockConn = new TestSqlConnection(null);
// If: I attempt to get a command for the edit
DbCommand cmd = rd.GetCommand(mockConn);
// Then:
// ... The command should not be null
Assert.NotNull(cmd);
// ... Only the keys should be used for parameters
int expectedKeys = includeIdentity ? 1 : 3;
Assert.Equal(expectedKeys, cmd.Parameters.Count);
// ... It should be formatted into an delete script
string regexTest = isMemoryOptimized
? @"DELETE FROM (.+) WITH\(SNAPSHOT\) WHERE (.+)"
: @"DELETE FROM (.+) WHERE (.+)";
Regex r = new Regex(regexTest);
var m = r.Match(cmd.CommandText);
Assert.True(m.Success);
// ... There should be a table
string tbl = m.Groups[1].Value;
Assert.Equal(etm.EscapedMultipartName, tbl);
// ... There should be as many where components as there are keys
string[] whereComponents = m.Groups[2].Value.Split(new[] { "AND" }, StringSplitOptions.None);
Assert.Equal(expectedKeys, whereComponents.Length);
// ... Each component should have be equal to a parameter
Assert.All(whereComponents, c => Assert.True(Regex.IsMatch(c.Trim(), @"\(.+ = @.+\)")));
}
public async Task GetCommandMissingCellNoDefault(bool includeIdentity, int defaultCols, int nullableCols,
int valuesToSkip)
{
// Setup:
// ... Generate the row create object
Common.TestDbColumnsWithTableMetadata data = new Common.TestDbColumnsWithTableMetadata(false, includeIdentity, defaultCols, nullableCols);
ResultSet rs = await Common.GetResultSet(data.DbColumns, includeIdentity);
RowCreate rc = new RowCreate(100, rs, data.TableMetadata);
// ... Create a mock db connection for building the command
var mockConn = new TestSqlConnection();
// If: I ask for a script to be generated without setting all the required values
// Then: An exception should be thrown for the missing cells
Assert.Throws <InvalidOperationException>(() => rc.GetCommand(mockConn));
}
public async Task CommitSuccess()
{
// Setup:
// ... Basic session and db connection
EditSession s = await GetBasicSession();
DbConnection conn = new TestSqlConnection(null);
// ... Add a mock commands for fun
Mock <RowEditBase> edit = new Mock <RowEditBase>();
edit.Setup(e => e.GetCommand(It.IsAny <DbConnection>())).Returns <DbConnection>(dbc => dbc.CreateCommand());
edit.Setup(e => e.ApplyChanges(It.IsAny <DbDataReader>())).Returns(Task.FromResult(0));
s.EditCache[0] = edit.Object;
// If: I commit these changes (and await completion)
bool successCalled = false;
bool failureCalled = false;
s.CommitEdits(conn,
() => {
successCalled = true;
return(Task.FromResult(0));
},
e => {
failureCalled = true;
return(Task.FromResult(0));
});
await s.CommitTask;
// Then:
// ... The task should still exist
Assert.NotNull(s.CommitTask);
// ... The success handler should have been called (not failure)
Assert.True(successCalled);
Assert.False(failureCalled);
// ... The mock edit should have generated a command and applied changes
edit.Verify(e => e.GetCommand(conn), Times.Once);
edit.Verify(e => e.ApplyChanges(It.IsAny <DbDataReader>()), Times.Once);
// ... The edit cache should be empty
Assert.Empty(s.EditCache);
}
public async Task CommitInProgress()
{
// Setup:
// ... Basic session and db connection
EditSession s = await GetBasicSession();
DbConnection conn = new TestSqlConnection(null);
// ... Mock a task that has not completed
Task notCompleted = new Task(() => {});
s.CommitTask = notCompleted;
// If: I attempt to commit while a task is in progress
// Then: I should get an exception
Assert.Throws <InvalidOperationException>(
() => s.CommitEdits(conn, () => Task.CompletedTask, e => Task.CompletedTask));
}
public async Task CommitFailure()
{
// Setup:
// ... Basic session and db connection
EditSession s = await GetBasicSession();
DbConnection conn = new TestSqlConnection(null);
// ... Add a mock edit that will explode on generating a command
Mock <RowEditBase> edit = new Mock <RowEditBase>();
edit.Setup(e => e.GetCommand(It.IsAny <DbConnection>())).Throws <Exception>();
s.EditCache[0] = edit.Object;
// If: I commit these changes (and await completion)
bool successCalled = false;
bool failureCalled = false;
s.CommitEdits(conn,
() => {
successCalled = true;
return(Task.FromResult(0));
},
e => {
failureCalled = true;
return(Task.FromResult(0));
});
await s.CommitTask;
// Then:
// ... The task should still exist
Assert.NotNull(s.CommitTask);
// ... The error handler should have been called (not success)
Assert.False(successCalled);
Assert.True(failureCalled);
// ... The mock edit should have been asked to generate a command
edit.Verify(e => e.GetCommand(conn), Times.Once);
// ... The edit cache should not be empty
Assert.NotEmpty(s.EditCache);
}
public async Task GetCommand(bool includeIdentity, bool isMemoryOptimized)
{
// Setup:
// ... Create a row update with cell updates
var data = new Common.TestDbColumnsWithTableMetadata(isMemoryOptimized, includeIdentity, 0, 0);
var rs = await Common.GetResultSet(data.DbColumns, includeIdentity);
RowUpdate ru = new RowUpdate(0, rs, data.TableMetadata);
Common.AddCells(ru, includeIdentity ? 1 : 0);
// ... Mock db connection for building the command
var mockConn = new TestSqlConnection(null);
// If: I ask for a command to be generated for update
DbCommand cmd = ru.GetCommand(mockConn);
// Then:
// ... The command should not be null
Assert.NotNull(cmd);
// ... Validate the command's makeup
// Break the query into parts
string[] splitSql = cmd.CommandText.Split(Environment.NewLine);
Assert.True(splitSql.Length >= 3);
// 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("Update"));
Assert.True(declareMatch.Groups[1].Value.EndsWith("Output"));
// Correct number of columns in declared table
string[] declareCols = declareMatch.Groups[2].Value.Split(", ");
Assert.Equal(rs.Columns.Length, declareCols.Length);
// Check the update statement in the middle
string regex = isMemoryOptimized
? @"^UPDATE (.+) WITH \(SNAPSHOT\) SET (.+) OUTPUT (.+) INTO @(.+) WHERE .+$"
: @"^UPDATE (.+) SET (.+) OUTPUT (.+) INTO @(.+) WHERE .+$";
Regex updateRegex = new Regex(regex);
Match updateMatch = updateRegex.Match(splitSql[10]);
Assert.True(updateMatch.Success);
// Table name matches
Assert.Equal(Common.TableName, updateMatch.Groups[1].Value);
// Output columns match
string[] outCols = updateMatch.Groups[3].Value.Split(", ");
Assert.Equal(rs.Columns.Length, outCols.Length);
Assert.All(outCols, col => Assert.StartsWith("inserted.", col));
// Set columns match
string[] setCols = updateMatch.Groups[2].Value.Split(", ");
Assert.Equal(3, setCols.Length);
Assert.All(setCols, s => Assert.Matches(@".+ = @Value\d+_\d+", s));
// Output table name matches
Assert.StartsWith("Update", updateMatch.Groups[4].Value);
Assert.EndsWith("Output", updateMatch.Groups[4].Value);
// Check the select statement last
Regex selectRegex = new Regex(@"^SELECT (.+) FROM @(.+)$");
Match selectMatch = selectRegex.Match(splitSql[11]);
Assert.True(selectMatch.Success);
// Correct number of columns in select statement
string[] selectCols = selectMatch.Groups[1].Value.Split(", ");
Assert.Equal(rs.Columns.Length, selectCols.Length);
// Select table name matches
Assert.StartsWith("Update", selectMatch.Groups[2].Value);
Assert.EndsWith("Output", selectMatch.Groups[2].Value);
// ... There should be an appropriate number of parameters in it
// (1 or 3 keys, 3 value parameters)
int expectedKeys = includeIdentity ? 1 : 3;
Assert.Equal(expectedKeys + 3, cmd.Parameters.Count);
}
