Ssdt dacpac

[JordanMarr]

Proposed Changes

This PR completely overhauls the SSDT provider to work with .dacpac files. This is way better that the previous release which was actually parsing the .sql files. Advantages:

• The .dacpac file can be parsed without a library, so the 3rd party parser dependency is eliminated! That means no more need to specify a ResolutionPath.
• The .dacpac contains more information than the parser did, so it is now able to recursively trace view dependencies to determine columns and their types even for complex views (CTEs, etc).
• I think that .dacpac support is what most people wanted in the first place.

Types of changes

What types of changes does your code introduce to SQLProvider? Put an x in the boxes that apply

• [ ] Bugfix (non-breaking change which fixes an issue)
• [ ] New feature (non-breaking change which adds functionality)
• [x] Breaking change (fix or feature that would cause existing functionality to not work as expected)

This is a breaking change because the previous release expected the SsdtPath to be set to a .sqlproj file, whereas this release expects a .dacpac.

Checklist

Put an x in the boxes that apply. You can also fill these out after creating the PR. If you're unsure about any of them, don't hesitate to ask. We're here to help! This is simply a reminder of what we are going to look for before merging your code.

• [ ] Build and tests pass locally
• [ ] I have added tests that prove my fix is effective or that my feature works (if appropriate)
• [x] I have added necessary documentation (if appropriate)

[Thorium]

Would it be worth of having both SSDT and Dacpac, or not?

(i.e. can you easily create dacpac from your ssdt, so that it wouldn't be needed?)

[JordanMarr]

Would it be worth of having both SSDT and Dacpac, or not?

(i.e. can you easily create dacpac from your ssdt, so that it wouldn't be needed?)

Benefits of the .dacpac:

• The .dacpac file is generated automatically as the output each build (in the bin folder of the .sqlproj). So if you have an SSDT project, you automatically have .dacpac.
• The very existence of the .dacpac file is proof that the SQL build was successful, whereas parsing the .sql files provides no guarantee that the SQL is valid, which could cause confusing exceptions at parse time.
• I previously was not aware that the generated .dacpac files are actually just a .zip file containing a model.xml file (I thought it was a custom binary format). So this eliminates the need for the 3rd party parser.
• The model.xml file in the .dacpac already contain an AST of the parsed SQL file that has more information than the AST I was generating using the Microsoft SqlParser package. With the previous SqlParser method, it was not going to be possible to obtain view columns beyond anything but the most basic views because the generated AST was nowhere near as comprehensive.
• The .dacpac is a single file that can easily be "Copied to Output" with the build so that it can be automatically deployed along with the dlls.
• .dacpac should also allow me to pretty easily support columns with UDT, which will be a nice upgrade.

Given that .dacpac does a much better job of providing types for views, and the fact that it is automatically generated on each build, I don't see any reason to support direct parsing of SQL.

In fact, I think there is reason to not support it:

• Supporting two different parsing types would double the amount of code to maintain, which would increase complexity.
• People would inevitably post issues about the SqlParser missing columns or not being able to determine the data type for columns, and the best answer would be "use .dacpac".

With all that said, it would certainly be possible to have both, but probably not worth it.

[Thorium]

Thanks! Released in 1.1.98.

[JordanMarr]

Thanks! Released in 1.1.98.

Just tested it and everything works great! Very exciting!