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