public async Task SetCellImplicitRowRevertTests() { // Setup: Create a fake column to update DbColumn[] columns = Common.GetColumns(true); ResultSet rs = await Common.GetResultSet(columns, true); EditTableMetadata etm = Common.GetStandardMetadata(columns); // If: // ... I add updates to one cell in the row RowUpdate ru = new RowUpdate(0, rs, etm); ru.SetCell(1, "qqq"); // ... Then I update the cell to its original value var eucr = ru.SetCell(1, (string)rs.GetRow(0)[1].RawObject); // Then: // ... An edit cell should have been returned Assert.NotNull(eucr); Assert.NotNull(eucr.Cell); // ... The old value should be returned Assert.Equal(rs.GetRow(0)[1].DisplayValue, eucr.Cell.DisplayValue); Assert.False(eucr.Cell.IsNull); // ... The cell should be clean Assert.False(eucr.Cell.IsDirty); // ... The row should be clean Assert.False(eucr.IsRowDirty); // TODO: Make sure that the script and command things will return null }
public async Task SetCell() { // Setup: Create a row update RowUpdate ru = await GetStandardRowUpdate(); // If: I set a cell that can be updated EditUpdateCellResult eucr = ru.SetCell(0, "col1"); // Then: // ... A edit cell was returned Assert.NotNull(eucr); Assert.NotNull(eucr.Cell); // ... The new value we provided should be returned Assert.Equal("col1", eucr.Cell.DisplayValue); Assert.False(eucr.Cell.IsNull); // ... The row is still dirty Assert.True(eucr.IsRowDirty); // ... The cell should be dirty Assert.True(eucr.Cell.IsDirty); // ... There should be a cell update in the cell list Assert.Contains(0, ru.cellUpdates.Keys); Assert.NotNull(ru.cellUpdates[0]); }
public async Task RevertCellThatWasSet() { // Setup: // ... Create a row update var data = new Common.TestDbColumnsWithTableMetadata(false, false, 0, 0); var rs = await Common.GetResultSet(data.DbColumns, false); RowUpdate ru = new RowUpdate(0, rs, data.TableMetadata); ru.SetCell(0, "qqq"); ru.SetCell(1, "qqq"); // If: I attempt to revert a cell that was set EditRevertCellResult result = ru.RevertCell(0); // Then: // ... We should get a result back Assert.NotNull(result); // ... We should get the original value back // @TODO: Check for a default value when we support it Assert.NotNull(result.Cell); Assert.Equal(rs.GetRow(0)[0].DisplayValue, result.Cell.DisplayValue); // ... The row should be dirty still Assert.True(result.IsRowDirty); // ... The cell should no longer be set Assert.DoesNotContain(0, ru.cellUpdates.Keys); }
public async Task RevertCellRevertsRow() { // Setup: // ... Create a row update var columns = Common.GetColumns(false); var rs = await Common.GetResultSet(columns, false); var etm = Common.GetStandardMetadata(columns); RowUpdate ru = new RowUpdate(0, rs, etm); ru.SetCell(0, "qqq"); // If: I attempt to revert a cell that was set EditRevertCellResult result = ru.RevertCell(0); // Then: // ... We should get a result back Assert.NotNull(result); // ... We should get the original value back // @TODO: Check for a default value when we support it Assert.NotNull(result.Cell); Assert.Equal(rs.GetRow(0)[0].DisplayValue, result.Cell.DisplayValue); // ... The row should now be reverted Assert.False(result.IsRowDirty); // ... The cell should no longer be set Assert.DoesNotContain(0, ru.cellUpdates.Keys); }
public async Task GetScriptTest(bool isMemoryOptimized) { // Setup: Create a fake table to update var data = new Common.TestDbColumnsWithTableMetadata(isMemoryOptimized, true, 0, 0); ResultSet rs = await Common.GetResultSet(data.DbColumns, true); // If: I ask for a script to be generated for update RowUpdate ru = new RowUpdate(0, rs, data.TableMetadata); Common.AddCells(ru, 1); string script = ru.GetScript(); // Then: // ... The script should not be null Assert.NotNull(script); // ... It should be formatted as an update script string regexString = isMemoryOptimized ? @"UPDATE (.+) WITH \(SNAPSHOT\) SET (.*) WHERE .+" : @"UPDATE (.+) SET (.*) WHERE .+"; Regex r = new Regex(regexString); var m = r.Match(script); Assert.True(m.Success); // ... It should have 3 updates string tbl = m.Groups[1].Value; string updates = m.Groups[2].Value; string[] updateSplit = updates.Split(','); Assert.Equal(data.TableMetadata.EscapedMultipartName, tbl); Assert.Equal(3, updateSplit.Length); Assert.All(updateSplit, s => Assert.Equal(2, s.Split('=').Length)); }
[InlineData(100)] // Way too large value public async Task SetCellOutOfRange(int columnId) { // Setup: Generate a row create RowUpdate ru = await GetStandardRowUpdate(); // If: I attempt to set a cell on a column that is out of range, I should get an exception Assert.Throws <ArgumentOutOfRangeException>(() => ru.SetCell(columnId, string.Empty)); }
/** [シングルトン]constructor */ private Function() { this.rowupdate = new RowUpdate(); //PlayerLoopType this.playerloop_flag = true; Fee.PlayerLoopSystem.PlayerLoopSystem.GetInstance().Add(Config.PLAYERLOOP_ROWUPDATE_ADDTYPE, Config.PLAYERLOOP_ROWUPDATE_TARGETTYPE, typeof(PlayerLoopType.Fee_Function_RowUpdate), this.RowUpdate); }
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 GetCommandNullConnection() { // Setup: Create a row update RowUpdate ru = await GetStandardRowUpdate(); // If: I attempt to create a command with a null connection // Then: It should throw an exception Assert.Throws <ArgumentNullException>(() => ru.GetCommand(null)); }
public async Task GetEditNullRow() { // Setup: Create a row update RowUpdate ru = await GetStandardRowUpdate(); // If: I attempt to get an edit row with a null cached row // Then: I should get an exception Assert.Throws <ArgumentNullException>(() => ru.GetEditRow(null)); }
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)); }
[InlineData(100)] // Way too large value public async Task RevertCellOutOfRange(int columnId) { // Setup: // ... Create a row update (no cell updates needed) var data = new Common.TestDbColumnsWithTableMetadata(false, false, 0, 0); var rs = await Common.GetResultSet(data.DbColumns, false); RowUpdate ru = new RowUpdate(0, rs, data.TableMetadata); // If: I attempt to revert a cell that is out of range // Then: I should get an exception Assert.Throws <ArgumentOutOfRangeException>(() => ru.RevertCell(columnId)); }
public async Task ApplyChangesNullReader() { // Setup: // ... Create a row update (no cell updates needed) var data = new Common.TestDbColumnsWithTableMetadata(false, true, 0, 0); var rs = await Common.GetResultSet(data.DbColumns, true); RowUpdate ru = new RowUpdate(0, rs, data.TableMetadata); // If: I ask for the changes to be applied with a null db reader // Then: I should get an exception await Assert.ThrowsAsync <ArgumentNullException>(() => ru.ApplyChanges(null)); }
[InlineData(100)] // Way too large value public async Task RevertCellOutOfRange(int columnId) { // Setup: // ... Create a row update (no cell updates needed) var columns = Common.GetColumns(false); var rs = await Common.GetResultSet(columns, false); var etm = Common.GetStandardMetadata(columns); RowUpdate ru = new RowUpdate(0, rs, etm); // If: I attempt to revert a cell that is out of range // Then: I should get an exception Assert.Throws <ArgumentOutOfRangeException>(() => ru.RevertCell(columnId)); }
public async Task ApplyChangesNullReader() { // Setup: // ... Create a row update (no cell updates needed) var columns = Common.GetColumns(true); var rs = await Common.GetResultSet(columns, true); var etm = Common.GetStandardMetadata(columns); RowUpdate ru = new RowUpdate(0, rs, etm); // If: I ask for the changes to be applied with a null db reader // Then: I should get an exception await Assert.ThrowsAsync <ArgumentNullException>(() => ru.ApplyChanges(null)); }
public async Task RowUpdateConstruction() { // Setup: Create the values to store const long rowId = 0; var data = new Common.TestDbColumnsWithTableMetadata(false, false, 0, 0); var rs = await Common.GetResultSet(data.DbColumns, false); // If: I create a RowUpdate instance RowUpdate rc = new RowUpdate(rowId, rs, data.TableMetadata); // Then: The values I provided should be available Assert.Equal(rowId, rc.RowId); Assert.Equal(rs, rc.AssociatedResultSet); Assert.Equal(data.TableMetadata, rc.AssociatedObjectMetadata); }
public void RowUpdateConstruction() { // Setup: Create the values to store const long rowId = 0; ResultSet rs = QueryExecution.Common.GetBasicExecutedBatch().ResultSets[0]; EditTableMetadata etm = Common.GetStandardMetadata(rs.Columns); // If: I create a RowUpdate instance RowUpdate rc = new RowUpdate(rowId, rs, etm); // Then: The values I provided should be available Assert.Equal(rowId, rc.RowId); Assert.Equal(rs, rc.AssociatedResultSet); Assert.Equal(etm, rc.AssociatedObjectMetadata); }
public void SetCellHasCorrections() { // Setup: // ... Generate a result set with a single binary column DbColumn[] cols = { new TestDbColumn { DataType = typeof(byte[]), DataTypeName = "binary" } }; object[][] rows = { new object[] { new byte[] { 0x00 } } }; var testResultSet = new TestResultSet(cols, rows); var testReader = new TestDbDataReader(new[] { testResultSet }); var rs = new ResultSet(0, 0, MemoryFileSystem.GetFileStreamFactory()); rs.ReadResultToEnd(testReader, CancellationToken.None).Wait(); // ... Generate the metadata var etm = Common.GetStandardMetadata(cols); // ... Create the row update RowUpdate ru = new RowUpdate(0, rs, etm); // If: I set a cell in the newly created row to something that will be corrected EditUpdateCellResult eucr = ru.SetCell(0, "1000"); // Then: // ... A edit cell was returned Assert.NotNull(eucr); Assert.NotNull(eucr.Cell); // ... The value we used won't be returned Assert.NotEmpty(eucr.Cell.DisplayValue); Assert.NotEqual("1000", eucr.Cell.DisplayValue); Assert.False(eucr.Cell.IsNull); // ... The cell should be dirty Assert.True(eucr.Cell.IsDirty); // ... The row is still dirty Assert.True(eucr.IsRowDirty); // ... There should be a cell update in the cell list Assert.Contains(0, ru.cellUpdates.Keys); Assert.NotNull(ru.cellUpdates[0]); }
public async Task SetCellImplicitRevertTest() { // Setup: Create a fake table to update DbColumn[] columns = Common.GetColumns(true); ResultSet rs = await Common.GetResultSet(columns, true); EditTableMetadata etm = Common.GetStandardMetadata(columns); // If: // ... I add updates to all the cells in the row RowUpdate ru = new RowUpdate(0, rs, etm); Common.AddCells(ru, true); // ... Then I update a cell back to it's old value var eucr = ru.SetCell(1, (string)rs.GetRow(0)[1].RawObject); // Then: // ... A edit cell was returned Assert.NotNull(eucr); Assert.NotNull(eucr.Cell); // ... The new value we provided should be returned Assert.Equal(rs.GetRow(0)[1].DisplayValue, eucr.Cell.DisplayValue); Assert.False(eucr.Cell.IsNull); // ... The cell should be clean Assert.False(eucr.Cell.IsDirty); // ... The row is still dirty Assert.True(eucr.IsRowDirty); // ... It should be formatted as an update script Regex r = new Regex(@"UPDATE .+ SET (.*) WHERE"); var m = r.Match(ru.GetScript()); // ... It should have 2 updates string updates = m.Groups[1].Value; string[] updateSplit = updates.Split(','); Assert.Equal(2, updateSplit.Length); Assert.All(updateSplit, s => Assert.Equal(2, s.Split('=').Length)); }
public static void InsertDivision(IDivision division) { CheckWorkBook(); var attributes = DivisionToAttributes(division); var id = new KeyValuePair <string, object>("Division_ID", attributes["Division_ID"]); if (Excel.IO.ExcelIOAPIs.IsIDOfWorksheet(FilePath, worksheets[2], id)) { var idColumn = new Dictionary <string, object>() { { id.Key, id.Value } }; RowUpdate.Update(FilePath, worksheets[2], idColumn, attributes); return; } RowInsert.Insert(FilePath, worksheets[2], attributes); }
public async Task GetEditRow() { // Setup: Create a row update with a cell set var columns = Common.GetColumns(false); var rs = await Common.GetResultSet(columns, false); var etm = Common.GetStandardMetadata(columns); RowUpdate ru = new RowUpdate(0, rs, etm); ru.SetCell(0, "foo"); // If: I attempt to get an edit row DbCellValue[] cells = rs.GetRow(0).ToArray(); EditRow er = ru.GetEditRow(cells); // Then: // ... The state should be dirty Assert.True(er.IsDirty); Assert.Equal(EditRow.EditRowState.DirtyUpdate, er.State); // ... The ID should be the same as the one provided Assert.Equal(0, er.Id); // ... The row should match the cells that were given, except for the updated cell Assert.Equal(cells.Length, er.Cells.Length); for (int i = 1; i < cells.Length; i++) { DbCellValue originalCell = cells[i]; DbCellValue outputCell = er.Cells[i]; Assert.Equal(originalCell.DisplayValue, outputCell.DisplayValue); Assert.Equal(originalCell.IsNull, outputCell.IsNull); // Note: No real need to check the RawObject property } // ... The updated cell should match what it was set to and be dirty EditCell newCell = er.Cells[0]; Assert.Equal("foo", newCell.DisplayValue); Assert.False(newCell.IsNull); Assert.True(newCell.IsDirty); }
public static void InsertPublicationType(IPublicationType publicationType) { CheckWorkBook(); var attributes = PublicationTypeToAttributes(publicationType); var id = new KeyValuePair <string, object>("PublicationType_ID", attributes["PublicationType_ID"]); if (Excel.IO.ExcelIOAPIs.IsIDOfWorksheet(FilePath, worksheets[3], id)) { var idColumn = new Dictionary <string, object>() { { id.Key, id.Value } }; RowUpdate.Update(FilePath, worksheets[3], idColumn, attributes); return; } RowInsert.Insert(FilePath, worksheets[3], attributes); }
public static void InsertTag(ITag tag) { CheckWorkBook(); var attributes = TagToAttributes(tag); var id = new KeyValuePair <string, object>("Tag_ID", attributes["Tag_ID"]); if (Excel.IO.ExcelIOAPIs.IsIDOfWorksheet(FilePath, worksheets[5], id)) { var idColumn = new Dictionary <string, object>() { { id.Key, id.Value } }; RowUpdate.Update(FilePath, worksheets[5], idColumn, attributes); return; } RowInsert.Insert(FilePath, worksheets[5], attributes); }
public static void InsertAuthor(IAuthor author) { CheckWorkBook(); var attributes = AuthorToAttributes(author); var id = new KeyValuePair <string, object>("Author_ID", attributes["Author_ID"]); if (Excel.IO.ExcelIOAPIs.IsIDOfWorksheet(FilePath, worksheets[1], id)) { var idColumn = new Dictionary <string, object>() { { id.Key, id.Value } }; RowUpdate.Update(FilePath, worksheets[1], idColumn, attributes); return; } RowInsert.Insert(FilePath, worksheets[1], attributes); }
public async Task ApplyChanges(bool includeIdentity) { // Setup: // ... Create a row update (no cell updates needed) var data = new Common.TestDbColumnsWithTableMetadata(false, includeIdentity, 0, 0); var rs = await Common.GetResultSet(data.DbColumns, includeIdentity); RowUpdate ru = new RowUpdate(0, rs, data.TableMetadata); long oldBytesWritten = rs.totalBytesWritten; // ... Setup a db reader for the result of an update var newRowReader = Common.GetNewRowDataReader(data.DbColumns, includeIdentity); // If: I ask for the change to be applied await ru.ApplyChanges(newRowReader); // Then: // ... The result set should have the same number of rows as before Assert.Equal(1, rs.RowCount); Assert.True(oldBytesWritten < rs.totalBytesWritten); }
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); }