matei-tm / ssdt-continuous-deployment-project-template-helpers

Visual Studio Extension: Helpers for SSDT Continuous Deployment Project Template

SSDT-Continuous-Deployment-Project-Template-Helpers

• SSDT-Continuous-Deployment-Project-Template-Helpers
• The quick wrapper command for datapatches
  • Constraints to qualify for wrapping
• The change promoter of common datapatches to sibling folders that aggregates the fixes for different deployments
  • How it works
  • Promoting "*.all.sql" files.
    • Demo
    • Constraints for a valid ALL promotion
  • Promoting "*.sub.sql" files.
    • Demo
    • Constraints for a valid FILTERED SUBset promotion
    • Advantages
• Requirements
• Installation
• Demo solution
  • Changelog
  • Disclaimer
    • The datapatch wrapper module updated files
    • The change promoter updated files

This is a Visual Studio extension that add some helpers to the SSDT-Continuous-Deployment-Project-Template

• A quick wrapper command for datapatches
• A change promoter of common datapatches in two flavors
  • adding automatic references to all files
  • providing a configuration UI where the user can choose the promotion destinations

The quick wrapper command for datapatches

In order to capture changes into the migration table a datapatch should be applied through sp_execute_cd_script. Using the quick wrapper the user can create a datapatch as a plain script with DML statements (INSERT/UPDATE/DELETE) and transform it as parameter to sp_execute_cd_script

Constraints to qualify for wrapping

• the file must be opened in the current document tab
• the file must be part of a SSDT Database project
• the file should have a sql extension
• the file must have DML statements (INSERT/UPDATE/DELETE)
• if it was wrapped already it will not be applied twice

The change promoter of common datapatches to sibling folders that aggregates the fixes for different deployments

If the base schema is distributed to several independent databases, this tool will help promoting the datapatch to several sibling folders. It will help to add a SQLCMD reference of the selected script to the needed matching destinations.

How it works

It works with two naming conventions:

1. A file that is matching "*.all.sql" will be promoted to all sibling files matching "*.main.datapatch.sql"
2. A file that is matching "*.sub.sql" will provide a configuration UI where the user can choose the destination files matching "*.main.datapatch.sql"

Promoting "*.all.sql" files.

Demo

Constraints for a valid ALL promotion

• the subject to promotion must match "*.all.sql"
• the subject to promotion must be a sql script
• the subject to promotion must have the "Build Action" as "None"
• the destination file must match "*.main.datapatch.sql"
• the destination file must be in a folder of the same level as the subject

Promoting "*.sub.sql" files.

Demo

Constraints for a valid FILTERED SUBset promotion

• the subject to promotion must match "*.sub.sql"
• the subject to promotion must be a sql script
• the subject to promotion must have the "Build Action" as "None"
• the destination file must match "*.main.datapatch.sql"
• the destination file must be in a folder of the same level as the subject
• the destination file must be checked in the "Destination Picker" dialog box

Advantages

• the destination files collection can be saved and reused
• alows filtered subset of destination files
• all the files can be checked in a single action
• the "all" collection is protected from changes
• the configuration setup is saved as json file
• the configuration collection are persisted in the current session of VS

Requirements

1. SSDT Tools
2. A database project created from the template provided by SSDT-Continuous-Deployment-Project-Template

Installation

Install from the Visual Studio Marketplace

Demo solution

A demo database project structure can be downloaded from https://github.com/matei-tm/SSDT-Continuous-Deployment-Project-Template-Helpers/tree/develop/examples/SSDT-CICD-Demo

Changelog

Please see project's changelog

Disclaimer

Important: This extension, being a code generator, will change files in the targeted database project. Use a suitable source control system (eg, git) to protect your code from transient changes.

The datapatch wrapper module updated files

The helper updates, in the current SSDT project, the current sql file opened in the editor tab of Visual Studio

The change promoter updated files

The helper updates, in the current SSDT project, all the files matching the pattern "*.main.datapatch.sql" contained in all the folders that are at the same level (siblings) as the folder that contains the "*.all.sql" file (the file used for the promotion).

Although I should not overwrite any file, other than those mentioned, during this process, I do not provide any warranty or take any responsibility for the loss of data.

ssdt-continuous-deployment-project-template-helpers's Issues

Context menu for Promote Change issue

• defer visibility control to the VisibilityConstraints section in the .vsct file

Implementation for change promoter

Basic implementation for change promoter

Refactor ssdtProjectHelper namespaces and assemblies

Refactor namespaces and assemblies to be clearly identified in the current context

The promotion pattern must match "*.main.datapatch.sql"

The promotion pattern must match "*.main.datapatch.sql"

Fix typo in bug report

Bug report is not presented due to a typo in the header

Add installation info to Readme

Add installation info with Marketplace link

The promotion should have a filtering UI

For promoting changes the user must be able to filter out sibling folders.

Publish version 1.0.1

Publish checklist

• Update the src/ssdt-m8-helpers/source.extension.vsixmanifest with the required version
• Update the changelog
• Update documentation
  • README.md
• Format the code
  • On every changed file Ctrl + K + D
• Run manual/visual tests
  • In Visual Studio, start debugging to open an experimental instance of Visual Studio.
  • In the experimental instance, go to the Tools menu and click Extensions and Updates. The TestPublish extension should appear in the center pane and be enabled.
  • On the Tools menu, make sure you see the test command.
• Run integration tests
  • In Visual Studio, from the Test Explorer run the tests
• Commit with message #xxxx. Publish review completed
• Merge develop changes into main
  • Check CI results
• Publish to the Visual Studio Marketplace
• Commit empty git commit --allow-empty -m "Completed publish closes #xxxx."

Atomic undo action for changes made by datapatch wrapper

Is your feature request related to a problem? Please describe.
Undoing actions of datapatch wrapper needs several undo actions

Describe the solution you'd like
The datapatch wrapper should undo all operations in a single action

Publish version 1.0.0

Publish checklist

• Update the src/ssdt-m8-helpers/source.extension.vsixmanifest with the required version
• Update the changelog
• Update documentation
  • README.md
• Format the code
  • On every changed file Ctrl + K + D
• Run manual/visual tests
  • In Visual Studio, start debugging to open an experimental instance of Visual Studio.
  • In the experimental instance, go to the Tools menu and click Extensions and Updates. The TestPublish extension should appear in the center pane and be enabled.
  • On the Tools menu, make sure you see the test command.
• Run integration tests
  • In Visual Studio, from the Test Explorer run the tests
• Commit with message #xxxx. Publish review completed
• Merge develop changes into main
  • Check CI results
• Publish to the Visual Studio Marketplace
• Commit empty git commit --allow-empty -m "Completed publish closes #xxxx."

Publish version 1.2.0

Publish checklist

• Update the src/ssdt-m8-helpers/source.extension.vsixmanifest with the required version
• Update the changelog
• Update documentation
  • README.md
• Format the code
  • On every changed file Ctrl + K + D
• Run manual/visual tests
  • In Visual Studio, start debugging to open an experimental instance of Visual Studio.
  • In the experimental instance, go to the Tools menu and click Extensions and Updates. The TestPublish extension should appear in the center pane and be enabled.
  • On the Tools menu, make sure you see the test command.
• Run integration tests
  • In Visual Studio, from the Test Explorer run the tests
• Commit with message #xxxx. Publish review completed
• Merge develop changes into main
  • Check CI results
• Publish to the Visual Studio Marketplace
• Commit empty git commit --allow-empty -m "Completed publish closes #xxxx."

The change promoter should be case insensitive on checking

The change promoter should be case insensitive on checking for existing reference inside the target file.

Publish version 1.1.0

Publish checklist

• Update the src/ssdt-m8-helpers/source.extension.vsixmanifest with the required version
• Update the changelog
• Update documentation
  • README.md
• Format the code
  • On every changed file Ctrl + K + D
• Run manual/visual tests
  • In Visual Studio, start debugging to open an experimental instance of Visual Studio.
  • In the experimental instance, go to the Tools menu and click Extensions and Updates. The TestPublish extension should appear in the center pane and be enabled.
  • On the Tools menu, make sure you see the test command.
• Run integration tests
  • In Visual Studio, from the Test Explorer run the tests
• Commit with message #xxxx. Publish review completed
• Merge develop changes into main
  • Check CI results
• Publish to the Visual Studio Marketplace
• Commit empty git commit --allow-empty -m "Completed publish closes #xxxx."

Fix typo on Publish request

On publish request the master branch should be renamed as main

Add a starter solution based on a sample from VSSDK Extensions

Add starter solution based on https://github.com/microsoft/VSSDK-Extensibility-Samples/tree/master/Backwards_Compatible_Editor_Command

The license badge is not properly rendered

The license file should be fixed

The change promotion by SET must use the name convention *.SUB.sql

Support for Visual Studio 2022 Extension

Hey we are using your Tool for wrapping DML scripts in Visual Studio 2019, now we updated to Visual Studio 2022 and have figured out that your Extension is not available for this Version. Can you please release this tool also as a VS 2022 Extension?

Thanks in advance

Add github templates and helpers

Add github templates for:

• bug report
• feature request
• publish request
• pull request

Add helpers for:

• git ignore
• editor config

The change promoter must add a mandatory new line when adding references

Issue with files that do not have a new line at the end.
The change promoter must add a mandatory new line when adding references

Add usage info for "Change promoter"

• Update readme
• Add demo solution

Add the demo database solution to build process

Add the demo database solution to build process

Add build pipeline using Appveyor

Add build pipeline to the project

Add basic introductory info to readme

Fill the readme with basic info

Add to readme.md a basic howto as an animated gif

Add an animated gif presenting datapatchwrapper operations

Scripts with ~empty content are raising exception

The empty scripts or with invalid content must be validated