better testing, columnFilters with schema, and updated DAC

[AartBluestoke]

• Updated SqlPackage.exe to latest version
• Updated to net6
• Added an index to the default DacPac
  • this breaks many unit tests :(
• added a schema comparison to the dacpac project so you can compare the current state of the test db to the expected state without browsing around in SSMS
• added code to handle schema for columnName
  • is a breaking change, if existing regex has a comma in it
• added 'basic' unit tests for a clean deploy to reset the test database to a known state (necessary after a unit test failure, otherwise one failure can provoke failures in others, especially when data and indexes are involved)
• added unit tests with data, as you get table rebuilds rather than alterations when there is data in it
• added a few log messages - both always present, and only present when #DEBUG is defined
• Added a Replacement process where a Decider can propose an alternate Step
  • use this decider to drop indexes that are about to be created, if we supressed the rebuild of the attached table.

[AartBluestoke]

NOTE: Unit tests fail here with the updated target DacPac with an index. The old DacPac passes all unit tests.

Removing the AdditionalDeploymentContributors allows the tests to pass, so it is something to do with the contributor.

[t-johnson]

Hi @AartBluestoke, thanks for spending time on this!

I have some concerns:

1. I think we need to keep .Net Framework target to ensure this library is usable with the Windows .Net Framework version of SqlPackage. For sure tests can target .NET 6.
2. Regarding the "breaking change" - I don't know how likely it is that any given regex would contain a comma. Is there a work-around if someone was using a comma in the regex?
3. I personally don't like including the sql package binaries with this repository. yes, tests run "out of the box", but guaranteed this will get out of date real quick. I'm not the original author, which is why I never removed the old SqlPackage binaries - i just worked around it. I wonder if it would be better to remove these files, and just replace with a readme with instructions on how to get SqlPackage to get the integration tests running?

[AartBluestoke]

1. The library once compiled is specific to versions of sqlpackage, I'll see what I can do, but I've received many "contributor initialisation errors" - eg, the version included in the main branch repo fails to run
2. If you want regex with a comma, begin with an extra comm - a null schema matches all schemas (should add a unit test)
3. the library only works with specific versions of sqlpackage - eg, the version in the main branch doesn't work in the integration tests. - this links to point 1 - we may need to work out how to do this.. I targeted net6 for this update as that is what sqlpackage is now based on

[AartBluestoke]

@t-johnson I've (perhaps temporarily) included the old sql package folder in the project - it however fails to load the framework build of the filter - I know we have an 'framework' targeted dll, but i don't know how to compile a filter version to target the 'old' version of sqlpackage (which is the one included in vs 2019, and is not listed on ) (the 'old' version that was there 15.0.4281.1 - not listed on https://learn.microsoft.com/en-us/sql/tools/sqlpackage/release-notes-sqlpackage - but appears to be a build number shortly before 18.1)

[t-johnson]

@AartBluestoke yeah no idea where that old version of SqlPackage came from. If we do want to have the integration tests run with the .NET 6 and the .NetFramework builds of SqlPackage, i would think it would be better to keep a version of SqlPackage that is mentioned on the MS page.

i checked out your changes - DacPac.refactorlog is mentioned but not included? we probably don't need this file at all?

[AartBluestoke]

@t-johnson

• included DacPac.refactorlog
• updated the unit tests with a demonstration of the schema handling "dbo,Employees" ",Employees" <-- demonstrates matching any schema - the old behavior - allows regex with commas to be used with a leading comma. "bob,Employees" <-- shows there isn't accidental matching

[AartBluestoke]

@t-johnson the specific problematic breaking change is at 19.0 - January 2022: you can't have the one DLL target both System.Data.SqlClient and Microsoft.Data.SqlClient

[AartBluestoke]

@t-johnson

• latest push successfully targets the 4.6.2 Framework version (as installed from the MSI into folder - C:\Program Files\Microsoft SQL Server\160\DAC\bin
• net6 version continues to work
• Core to the fix was ensuring the framework targeting (4.6.2) matched the version of the exe, but that means that we can't target even older sqlPackage versions (which require 4.6 targeting, which the newest DacFx library doesn't support)

[AartBluestoke]

fyi: Notes for sql package version ... Included in the repo above are two 162.0.52.1 versions.

SqlPackage Versions

VersionNumber | build number | Location | installed by | Targets | Notes -- | -- | -- | -- | -- | -- 162.0.52.1 | 162.0.52.1 unlisted | C:\Users\\.dotnet\tools\sqlpackage.exe | dotnet tool install -g microsoft.sqlpackage | Net6 / Microsoft.Data.SqlClient 5.0 | from https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-download 162.0.52.1 | 162.0.52.1 unlisted | C:\Program Files\Microsoft SQL Server\160\DAC\bin\sqlpackage.exe | MSI installer from main website | Net Framework 4.6.2 / Microsoft.Data.SqlClient 5.0 | Also from above link 19.3>19.2 | 16.0.9021.0 unlisted | C:\Program Files\Microsoft Visual Studio\2022\Professional\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\SqlPackage.exe | VisualStudio 2022 | Net Framework 4.6.2 / Microsoft.Data.SqlClient 3.0 | Current (2023-09-01) Visual Studio 2022 install 18.7>18.6 | 15.0.5015.3 unlisted | C:\Program Files (x86)\Microsoft Visual Studio\2019\BuildTools\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\150\SqlPackage.exe | Visual Studio 2019 | Net Framework 4.6 / System.Data.SqlClient | Visual Studio install in on-prem agent 18.1>18.0 | 15.0.4281.1 unlisted | C:\Program Files (x86)\Microsoft Visual Studio\2019\BuildTools\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\150\SqlPackage.exe | Visual Studio 2019 | Net Framework 4.6 / System.Data.SqlClient | Precise source unknown

[AartBluestoke]

SqlPackageFilter.dll version | .Net | Client | Microsoft.SqlServer.DacFx -- | -- | -- | -- Framework target | Net Framework 4.6.2 | Microsoft.Data.SqlClient 5.0.1 | 161.6374.0 netStandard target| Net Standard 2.1 | Microsoft.Data.SqlClient 5.1.0 | 162.0.52