Erase fails when functions depend on each other

[johnwright]

Sample output:

Evolve initialized.
Executing Erase...
Erase failed. Impossible to erase schema dbo. Cannot DROP FUNCTION 'dbo.FpR' because it is being referenced by object 'FpObscure16'. Sql query: DROP FUNCTION [dbo].[FpR] Cannot DROP FUNCTION 'dbo.FpR' because it is being referenced by object 'FpObscure16'.
Erase failed. Impossible to erase schema dbo. Cannot DROP FUNCTION 'dbo.FpR' because it is being referenced by object 'FpObscure16'. Sql query: DROP FUNCTION [dbo].[FpR] Cannot DROP FUNCTION 'dbo.FpR' because it is being referenced by object 'FpObscure16'.

Evolve Tool version: 2.4.0
SQL Server version: Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) Sep 24 2019 13:48:23 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Home 10.0 (Build 15063: )

N.B. both functions are defined WITH SCHEMABINDING.

[johnwright]

I forgot to mention - this doesn't seem to happen consistently. Maybe the ordering of dropping functions isn't deterministic?

[lecaillon]

Thanks for the feedback, I'm going to check what can be done

[lecaillon]

Just release Evolve 3.0.0-alpha2 that should fix your issue. Waiting for your feedback.

[johnwright]

Thanks for this! I'll try with that version and get back to you. It might take a while as the issue is intermittent.

[johnwright]

Since I'm using Evolve.Tool, is there a way for me to try this version on the command line? Or would you need to release a new version of the tool also?

[lecaillon]

So I'm gonna release a version of Evolve.Tool too. It should be available in the hour. Note that in the version 3.0.0, Evolve.Tool needs either netcoreapp3.1 or net5.0 on your machine. (netcoreapp2.2 support is over)

[johnwright]

Unfortunately I'm getting a different error now. I think it's happening because a table with a computed column depends on that function, and functions are now being dropped before tables.

PS C:\Users\John\P1\P1.A> dotnet tool update Evolve.Tool --global --version 3.0.0-alpha2
Tool 'evolve.tool' was successfully updated from version '2.4.0' to version '3.0.0-alpha2'.
PS C:\Users\John\P1\P1.A> evolve erase @evolve-config.txt
Evolve initialized.
Executing Erase...
Erase failed. Impossible to erase schema dbo. Cannot DROP FUNCTION 'dbo.OfferPresentationId' because it is being referenced by object 'Offer'. Sql query: DROP FUNCTION [dbo].[OfferPresentationId] Cannot DROP FUNCTION 'dbo.OfferPresentationId' because it is being referenced by object 'Offer'.
Erase failed. Impossible to erase schema dbo. Cannot DROP FUNCTION 'dbo.OfferPresentationId' because it is being referenced by object 'Offer'. Sql query: DROP FUNCTION [dbo].[OfferPresentationId] Cannot DROP FUNCTION 'dbo.OfferPresentationId' because it is being referenced by object 'Offer'.

[lecaillon]

Ok then. Again, thank you for your feedback. Your description of the issue is really clear.
I've been a bit lazy concerning the computed columns indeed ;) I must apply the same "pattern" than the one I used for the functions. My bad. I will release an alpha3 as soon as I can (tomorow at worst). I will post a new comment to notify you.

[lecaillon]

@johnwright Evolve-Tool 3.0.0-alpha3 released

Happy testing ;)

[johnwright]

Thanks for the update!

I'm still seeing an error, but it doesn't look like it's related to function dependencies this time. It's just a regular foreign key dependency between tables. Several tables have a foreign key referencing EnquiryItemTransition.

PS C:\Users\John\P1> dotnet tool update  Evolve.Tool --global --version 3.0.0-alpha3
Tool 'evolve.tool' was successfully updated from version '3.0.0-alpha2' to version '3.0.0-alpha3'.
PS C:\Users\John\P1> cd P1.A
PS C:\Users\John\P1\P1.A> evolve erase @evolve-config.txt
Evolve initialized.
Executing Erase...
Erase failed. Impossible to erase schema dbo. Could not drop object 'dbo.EnquiryItemTransition' because it is referenced by a FOREIGN KEY constraint. Sql query: DROP TABLE [dbo].[EnquiryItemTransition] Could not drop object 'dbo.EnquiryItemTransition' because it is referenced by a FOREIGN KEY constraint.
Erase failed. Impossible to erase schema dbo. Could not drop object 'dbo.EnquiryItemTransition' because it is referenced by a FOREIGN KEY constraint. Sql query: DROP TABLE [dbo].[EnquiryItemTransition] Could not drop object 'dbo.EnquiryItemTransition' because it is referenced by a FOREIGN KEY constraint.

Is it possible that the GetTables() call is returning tables in an incorrect order, or that checking for table_type='BASE_TABLE' isn't quite right? I'm not very knowledgeable about SQL Server unfortunately.

[lecaillon]

Ok your database schema is challenging. Let me check the logic one more time. If you could send me your database scripts on my personnal mail it would be perfect. But I understand if there is sensitive data you cannot share of course.

[lecaillon]

What's strange, is that I drop FK constraints first : DropForeignKeys(); in the erase process ...
If you execute the query below after Evolve fails, does it return anything ?

SELECT TABLE_NAME, CONSTRAINT_NAME 
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
WHERE CONSTRAINT_TYPE IN ('FOREIGN KEY','CHECK') AND TABLE_SCHEMA = 'dbo';

[johnwright]

Yes, it returns a lot of rows - it looks like the drop didn't work properly.

I've emailed the schema to evolve-db@hotmail.com, I hope that makes it clearer what's going on here.

[lecaillon]

Thank you @johnwright The good news is I can reproduce the issue now. The bad news is that is not trivial. Your database objects rely a lot on each other. Like I said, it's challenging. I need time for that kind of issue. I will work on it this week-end. I hope I will release an alpha4 by monday. I'll keep you in touch

[johnwright]

Thanks for your help, I really appreciate it.

I had a look at the equivalent code in Flyway, and they are using a similar approach - I'll try testing with Flyway to see if their code works for this schema.

[johnwright]

I've tried with Flyway, and it also isn't able to clean the schema. It fails because a check constraint is dependent on a computed column!

[lecaillon]

Ok, thanks. It is nice to know. Nevertheless, I'll try to find a solution as I told you.

[lecaillon]

@johnwright Evolve-Tool 3.0.0-alpha4 released

The issue is now fixed on my computer ;)

Happy testing again

[johnwright]

This is now working - thanks very much! I owe you a coffee if you're ever in London.

[lecaillon]

I've got family there, who knows :) ?
In the meantime, I would appreciate that you try to convince your company to sponsor me, even by the smallest tier : a coffee. It would help me spend more time developping new features and helping more people using it.

[johnwright]

Absolutely, especially since we will be using Evolve for the duration of this client project and most likely for others too!

[lecaillon]

Thank you, appreciated it. I would be happy to put the logo of your company on the Evolve's homepage and/or documentation if you want.

And tell me if the git history is alright for you now, I'm not a git wizard

[johnwright]

Git history looks good, thanks very much. Sponsorship should be set up now!

[lecaillon]

Yes it is ! Thank you very much. Highly appreciated. Feel free to send me feedback or contact me for anything related to Evolve