bungalow64 / dbconfirm Goto Github PK

When validating the DBConfirm NuGet packages, it is highlighted that the symbols are missing for the source link.

These need to be published.

See: https://docs.microsoft.com/en-us/nuget/create-packages/symbol-packages-snupkg

Need to update the package details, and update the pack/publish builds.

See SlowFox: https://github.com/Bungalow64/SlowFox/blob/main/azure-pipelines.yml

Pack and push the NuGet packages to Azure DevOps feed, reference these packages in the SampleSolutions\NuGetTests projects, and let the tests run using these packages directly.

Most of the tests focusing on the verification methods (which are calling the DB) are only checking that the verifications pass as expected.

There can be more to check they fail as expected, too.

For MSTest we can just wrap the verification in a try...catch and assert the caught exception. For NUnit we need to run the test in an isolated process, and catch the exception.

Check everything works with SQL Server 2022.

• update readme
• update docs
• add DB setup script for SQL Server 2022
• add test projects for SQL Server 2022
• update pipelines to run new tests

If I have a stored procedure that should throw an exception, I'll want to be able to test for that.

I can wrap the TestRunner.ExecuteStoredProcedureNonQueryAsync call in a function, and test the result for an exception using something like FluentAssertions:

Func<Task> act = async () => await TestRunner.ExecuteStoredProcedureNonQueryAsync("dbo.DoSomethingProcedure",
    new SqlQueryParameter("Value1", "abc"),
    new SqlQueryParameter("Value2", "def"));

act.Should().Throw<SqlException>();

But it would be nice if this was handled within the DBConfirm framework itself. We'd probably need to have a new, dedicated method to check for this (since some of these existing Execute procedures don't return anything, and we wouldn't want exceptions to be hidden), but probably just one would work for all execution types, since we wouldn't need to test any data that's returned (since it should have errored).

Say I have a table in the database (SQL Server) called:

dbo.User's

When the template is generated for this using the GenerateTemplatesSQLServer tool, the apostrophe is maintained in the name, resulting in a class that doesn't compile.

Ideally this special character (and others) need to be replaced, but we need to make sure that we don't end up with duplicate names (e.g., if there is another table called dbo.User_s, then we can't just replace the apostrophe with an underscore).

The test projects are currently using .NET6, and it would be nice to be able to use some of the language features in .NET 7/8.

Upgrade all non-package projects (keep those as netstandard 2.0) to .NET 8.

Apply project-wide refactoring of the following:

• file-scoped namespaces (https://learn.microsoft.com/en-us/dotnet/csharp/language-reference/proposals/csharp-10.0/file-scoped-namespaces)
• target-typed new expressions (https://learn.microsoft.com/en-us/dotnet/csharp/language-reference/proposals/csharp-9.0/target-typed-new)

When running docker-compose on Windows 11, the servers are set up correctly, but the databases aren't added.

I haven't figured out what's changed, or where there's an error, but this can be fixed by running these commands once the containers have been set up:

docker exec -it dbconfirm-sqlserver2019-1 /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -d master -i northwind_setup.sql
docker exec -it dbconfirm-sqlserver2019-1 /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -d master -i sampledb_setup.sql
docker exec -it dbconfirm-sqlserver2017-1 /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -d master -i northwind_setup.sql
docker exec -it dbconfirm-sqlserver2017-1 /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -d master -i sampledb_setup.sql

The password can be found in the docker\sqlserver.env file.

Generate templates via the GenerateTemplatesSQLServer tool (v0.1.3), then open the generated files in VS2019. I get a warning:

It doesn't appear for all generated files - I get it when generating dbo.Orders in Northwind though.

We shouldn't get this error.

We would need new packages for:

• Databases.MySQL
• Packages.MySQL.MSTest
• Packages.MySQL.NUnit
• Templates.MySQL.MSTest
• Templates.MySQL.NUnit

Plus the scripts for the test database and the new test projects.

Using v0.1.3, SQL Server and MSTest.

Say there is a stored procedure that returns a column that is set as smallint (e.g., Quantity in Northwind's dbo.CustOrderDetail). I write a test that verifies that this number is 2.

If I write the assertion like this:

result
  .AssertRowCount(1)
  .ValidateRow(0)
  .AssertValues(new DataSetRow
  {
      { "Quantity", 2 }
  });

Then the test fails, with this error:

Assert.AreEqual failed. Expected:<2 (System.Int32)>. Actual:<2 (System.Int16)>. Column Quantity in row 0 has an unexpected value

I would have to cast the expected value to the correct type for the test to pass:

result
  .AssertRowCount(1)
  .ValidateRow(0)
  .AssertValues(new DataSetRow
  {
      { "Quantity", (short)2 }
  });

This applies to all number types - for example decimals, which requires either casting or postfixing with M).

It would be helpful for some implicit casting, where possible. If I write the number 2, then that should be able to be used in an assertion with any number type that can represent this number fully, without failing on a type check. If a number is used that cannot be cast fully (e.g., a 1.5 used to assert a shortint column), then the original type needs to be used, since it wouldn't make sense to use either 1 or 2 for this assertion.

The only downside for relaxing the rules on type checking is that it's no longer possible (in 0.1.3) to write a test that locks down the type of data returned for a given column. This is currently done by default when the value is asserted. So it might be a good idea to add extra validation methods to explicitly check for the column type (e.g., AssertColumnType(string expectedColumnName, Type expectedType)), so that tests can still check this if required.

TL;DR;

1. When asserting numeric values, attempt to avoid unnecessary type errors (possibly by exact casting)
2. Add extra validation methods to test for column types (already possible)

When validating the NuGet packages, it has been highlighted that they are non-deterministic and compiler flags are missing. This needs to be fixed.

See https://mitchelsellers.com/blog/article/net-5-deterministic-builds-source-linking

Say we execute a stored procedure that returns a QueryResult object, and we want to check that a specific row does not exist. We can call this:

data
.AssertRowDoesNotExist(new DataSetRow
{
{ "FirstName", "Jeff" },
{ "LastName", "Burns" }
});

When using MSTest this works as expected, but in NUnit we get a test failure:

Multiple failures or warnings in test:

1. Column FirstName in row 0 has an unexpected value
   Expected string length 4 but was 5. Strings differ at index 1.
   Expected: "Jeff"
   But was: "Jamie"
   ------------^

2. Column FirstName in row 1 has an unexpected value
   Expected string length 4 but was 3. Strings differ at index 0.
   Expected: "Jeff"
   But was: "AAA"
   -----------^

For example, the AddUser_ValidData_UserAdded test in MSTest works, but fails when run in NUnit: https://github.com/Bungalow64/DBConfirm/blob/main/tests/Sample.Core.MSTest.Tests/StoredProcedures/AddUserTests.cs

This assertion should only fail if a row is found that matches.

Root cause

The problem is that we're iterating through each row and using the same logic to see if the row matches the data, and catching/swallowing the assert exception for each row that doesn't match. This works fine for MSTest, but NUnit still fails the test even though the exception is caught.

Really, we don't want to be using exceptions for this, so we need to rewrite this logic to do the same checks without using assert logic.

In the documentation, there are different sections within each page, and links to these sections/headers work correctly within the page itself (e.g., on https://www.dbconfirm.com/writingtests, clicking the link to 'Act' scrolls down to that header).

However, using that URL directly, doesn't scroll. For example, this link does not scroll: https://www.dbconfirm.com/writingtests#act

This scrolling should work.

See https://dev.to/dgenezini/how-to-run-disposable-databases-for-your-tests-improve-your-integration-tests-accuracy-with-testcontainers-58nn

When running the GenerateTemplatesSQLServer tool, if a destination is selected that doesn't exist (e.g., create a new project, then try to generate templates into a Templates directory that hasn't been created yet) then we get an error saying that the path can't be found:

PS C:\Git\TestProject\DBTests> GenerateTemplatesSQLServer -d "TestDB" -t "*" -n "DBTests.Templates" --destination Templates
Using connection: SERVER=(local);DATABASE=TestDB;Integrated Security=true;Connection Timeout=30;
Found table (dbo.User) and 9 columns
Class definition generated.  Saving...
There has been an error
Could not find a part of the path 'C:\Git\TestProject\DBTests\Templates\UserTemplate.cs'.
   at System.IO.FileStream.ValidateFileHandle(SafeFileHandle fileHandle)
   at System.IO.FileStream.CreateFileOpenHandle(FileMode mode, FileShare share, FileOptions options)
   at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, FileOptions options)
   at System.IO.StreamWriter.ValidateArgsAndOpenPath(String path, Boolean append, Encoding encoding, Int32 bufferSize)
   at System.IO.StreamWriter..ctor(String path)
   at System.IO.File.WriteAllText(String path, String contents)
   at DBConfirm.TemplateGeneration.Logic.FileHelper.WriteAllText(String path, String contents) in /home/vsts/work/1/s/tools/TemplateGeneration.SQLServer/Logic/FileHelper.cs:line 10
   at DBConfirm.TemplateGeneration.Logic.Generator.GenerateFile(IEnumerable`1 processedColumns, String schemaName, String tableName) in /home/vsts/work/1/s/tools/TemplateGeneration.SQLServer/Logic/Generator.cs:line 112
   at DBConfirm.TemplateGeneration.Logic.Generator.GenerateFileAsync() in /home/vsts/work/1/s/tools/TemplateGeneration.SQLServer/Logic/Generator.cs:line 61
   at DBConfirm.TemplateGeneration.Program.<>c.<<Main>b__0_0>d.MoveNext() in /home/vsts/work/1/s/tools/TemplateGeneration.SQLServer/Program.cs:line 18

The tool should try and create the directory if it doesn't already exist.

We're using version 2.1.2 for Microsoft.Data.SqlClient and 4.8.5 for System.Data.SqlClient, both of which have vulnerabilities. We need to upgrade to at least 2.1.7 and 4.8.6 respectively.

There's a SQL Server 2017 setup script in the root of the solution. I assume it's not being used by anything. Check, and remove.

I have a table containing an Identity and nvarchar(max) column:

CREATE TABLE [dbo].[Titles](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Title] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_Titles] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Running DBConfirm.TemplateGeneration on this table results in an error:

PS C:\Git\Database1Tests> GenerateTemplatesSQLServer -d "Database1" -t "Titles" --dry-run                         
Using connection: SERVER=(local);DATABASE=Database1;Integrated Security=true;Connection Timeout=30;
Found table (dbo.Titles) and 2 columns
There has been an error
Length cannot be less than zero. (Parameter 'length')
   at System.String.Substring(Int32 startIndex, Int32 length)
   at DBConfirm.TemplateGeneration.Models.ColumnDefinition.TruncateLongString(String value) in /home/vsts/work/1/s/tools/TemplateGeneration.SQLServer/Models/ColumnDefinition.cs:line 138
   at DBConfirm.TemplateGeneration.Models.ColumnDefinition.GetDefaultText() in /home/vsts/work/1/s/tools/TemplateGeneration.SQLServer/Models/ColumnDefinition.cs:line 119
   at DBConfirm.TemplateGeneration.Models.ColumnDefinition.ToDefaultData() in /home/vsts/work/1/s/tools/TemplateGeneration.SQLServer/Models/ColumnDefinition.cs:line 163
   at DBConfirm.TemplateGeneration.Logic.Generator.<>c.<GenerateClass>b__11_5(ColumnDefinition p) in /home/vsts/work/1/s/tools/TemplateGeneration.SQLServer/Logic/Generator.cs:line 183
   at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()
   at System.String.Join(String separator, IEnumerable`1 values)
   at DBConfirm.TemplateGeneration.Logic.Generator.GenerateClass(IEnumerable`1 processedColumns, String schemaName, String tableName) in /home/vsts/work/1/s/tools/TemplateGeneration.SQLServer/Logic/Generator.cs:line 173
   at DBConfirm.TemplateGeneration.Logic.Generator.GenerateFile(IEnumerable`1 processedColumns, String schemaName, String tableName) in /home/vsts/work/1/s/tools/TemplateGeneration.SQLServer/Logic/Generator.cs:line 93
   at DBConfirm.TemplateGeneration.Logic.Generator.GenerateFileAsync() in /home/vsts/work/1/s/tools/TemplateGeneration.SQLServer/Logic/Generator.cs:line 59
   at DBConfirm.TemplateGeneration.Program.<>c.<<Main>b__0_0>d.MoveNext() in /home/vsts/work/1/s/tools/TemplateGeneration.SQLServer/Program.cs:line 18

The tool should be able to generate a template with both columns included.

Currently we're using .NET Core 3.1, which comes to end of life December 2022.

We should upgrade to .NET 6.

Hi,

thanks for this very nice project.

I have just played a bit and was expecting a function to confirm uniqueness. i.e.

  [TestMethod]
       public async Task unique()
        {
            QueryResult data1 = await TestRunner.ExecuteCommandAsync("SELECT 1 a, 2 b UNION SELECT 2, 2 ORDER BY a");
            data1
              .AssertColumnCount(2)
              .AssertColumnExists("a")
              .AssertColumnsNotExist("c", "d")
              .AssertValue(0, "a", 1) 
// missing?
              .AssertColumnUniqe("a")
              .AssertColumnsUniqe("a", "b")
            ;
        }

Did I miss something? Could those be added?

regards
Felix

• Clone DBConfirm (on a Windows machine)
• Run 'docker compose build'
• Run 'docker compose up'

This error will appear in the logs somewhere, and the sample DBs won't be created:

syntax error near unexpected token `$'\r''

The problem is that the import-data.sh file needs to have Unix (LF) line endings, and git is cloning this file with Windows (CLRF). This can be fixed by manually changing the file to LF (e.g., opening it in Visual Studio Code and changing it at the bottom-right), and that fixes it, but ideally git needs to leave the line endings alone.

Try generating the templates for all tables for a schema that doesn't have any tables (or for a schema that doesn't exist itself), and the error returned is:

Cannot find table: dbo2.*

For example, say I have a database with some tables in dbo, but where the dbo2 schema doesn't exist. Running:

GenerateTemplatesSQLServer -d "DatabaseName" -t "*" -s "dbo2" 

This shows the error.

Really, this should say:

Cannot find any tables in the dbo2 schema