SchemaComparison.Compare and SchemaComparisonResult.PublishChangesToProject ignores DropDmlTriggersNotInSource and SchemaDifference.Included property
Nobody has claimed this yet.
- 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
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- 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