microsoft / microsoft/DacFx

SchemaComparison.Compare and SchemaComparisonResult.PublishChangesToProject ignores DropDmlTriggersNotInSource and SchemaDifference.Included property

Open
#595 0 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

bug
Dominant language
C#
Stars
460
Forks
29
Avg merge
4d 9h
Merged PRs (30d)
7

Description

Tested on:
- DacFx Version: 162.1.172, 162.2.111, 162.3.566, 162.4.92, 162.5.57 as well as 170.0.76-preview
- .NET Framework 4.8
- Environment (local platform and source/target platforms): WIndows 11

**Steps to Reproduce:**
Lets assume we have the following
1. We have database schema in our initial dacpac as follows
```sql
CREATE TABLE [dbo].[Table1] (
Id INT NOT NULL,
Col1 nvarchar(50)
)
GO
CREATE TRIGGER [dbo].[Trigger1]
ON [dbo].[Table1]
AFTER INSERT
AS PRINT 'Inserted';
GO
```
we are updating the project with it using
```csharp
var comparisonResult = new SchemaComparison(source, target).Compare();
var publishResult = comparisonResult.PublishChangesToProject(projectPath, DacExtractTarget.SchemaObjectType);
```
2. We have database schema in our modified dacpac as follows:
```sql
CREATE TABLE [dbo].[Table1] (
Id INT NOT NULL,
Col1 nvarchar(50),
Col2 nvarchar(50) -- added
)
GO

-- Trigger1 removed

CREATE TRIGGER [dbo].[Trigger2] -- added
ON [dbo].[Table1]
AFTER UPDATE
AS PRINT 'updated';
GO

CREATE TABLE [dbo].[Table2] ( -- added
Id INT NOT NULL,
Col1 nvarchar(50)
)
GO

```
3. we are updating the project with it using
```csharp
var comparisonResult = new SchemaComparison(source, target){
Options = {
DropDmlTriggersNotInSource = false,
}
}.Compare();
var publishResult = comparisonResult.PublishChangesToProject(projectPath, DacExtractTarget.SchemaObjectType);
```
this results in Trigger1 being removed from the project.
3. we update the the project using following
```csharp
var comparisonResult = new SchemaComparison(source, target){}.Compare();
comparisonResult.Exclude(GerTrigger1Difference(comparisonResult));
var publishResult = comparisonResult.PublishChangesToProject(projectPath, DacExtractTarget.SchemaObjectType);
```
this results in `Trigger1` being removed from the project.

From what I can see calling Include and Exclude method works only for top level objects, not second level like triggers.
I've checked this in `DeploymentPlanModifier` I added to this `SchemaComparison.Oprions` and drop trigger is present in `DeploymentPlanContributorContext.ComparisonResult.ElementsToDrop` but there is no operation in `DeploymentPlanContributorContext.PlanHandle`, I though I could fix it by removing `DeploymentStep` with this drop, from the plan.
I've also tried to add this trigger to `SchemaComparison.ExcludedTargetObjects` instance property it did not make any difference.

`SchemaComparison` in this example uses dacpac as source, because I have tests that build a package from those scripts above before Compare.

Clicking Exclude on trigger works in VS 2022 17.13.5 UI, trigger will not be deleted from the project. I'm 95% sure VS does not use `DacFx` internally for schema comparison and updating the project (I think this is the class `Microsoft.Data.Tools.Schema.SchemaModel.ModelComparisonResult` that corresponds to `Microsoft.SqlServer.Dac.SchemaComparisonResult` I'm using), its difficult to be sure when I was analyzing VS code in ilspy sadly I'm not the MS employee :)

I want to automate synchronization of a database in my project by ignoring some objects that I know I have to deal with later. but I dont want to do it manually every time I sync it, because database development is ongoing and we have too many objects to click through in UI.

**Did this occur in prior versions? I dont think so 162.0.52 is not compatible with my project I would have to change few things**

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start by reproducing the trigger scenario through SchemaComparison.Compare and SchemaComparisonResult.PublishChangesToProject, using the supplied dacpac schemas and options. Inspect Include/Exclude, DeploymentPlanModifier, DeploymentPlanContributorContext.ComparisonResult.ElementsToDrop, PlanHandle, DeploymentStep, and ExcludedTargetObjects; done means both DropDmlTriggersNotInSource=false and excluding a trigger prevent Trigger1 from being removed from the project.

Written by the indexing model from the issue text.

Assessment

Tech stack
csharp, sql
Domain
databases, tooling
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.