Migracja schematu bazy danych z DbUp

AddCategories.sql

CREATE TABLE [dbo].[Categories] (
	[Id] INT NOT NULL IDENTITY(1,1), 
	[Name] NVARCHAR(255) NOT NULL, 
	CONSTRAINT [PK_Categories] PRIMARY KEY ([Id])
)

INSERT INTO dbo.Categories SELECT DISTINCT Category FROM dbo.Products;

ALTER TABLE [dbo].[Products] ADD [CategoryId] INT

GO

UPDATE p SET p.CategoryId = (SELECT c.Id FROM dbo.Categories c WHERE c.Name = p.Category) FROM dbo.Products p;

ALTER TABLE [dbo].[Products] ALTER COLUMN [CategoryId] INT NOT NULL;

ALTER TABLE [dbo].[Products] ADD CONSTRAINT [FK_Products_CategoryId_Categories_Id] FOREIGN KEY ([CategoryId]) REFERENCES [dbo].[Categories] ([Id]);

CREATE INDEX [IX_Products_CategoryId] ON [dbo].[Products] ([CategoryId] ASC);

DECLARE @default sysname, @sql nvarchar(max);

SELECT @default = name
FROM sys.default_constraints
WHERE parent_object_id = object_id('[dbo].[Products]')
AND type = 'D'
AND parent_column_id = (
SELECT column_id
FROM sys.columns
WHERE object_id = object_id('[dbo].[Products]')
AND name = 'Category'
);

SET @sql = N'ALTER TABLE [dbo].[Products] DROP CONSTRAINT ' + QUOTENAME(@default);
EXEC sp_executesql @sql;

ALTER TABLE [dbo].[Products] DROP COLUMN [Category];

AddProducts.sql

CREATE TABLE [dbo].[Products] (
	[Id] INT NOT NULL IDENTITY(1,1), 
	[Name] NVARCHAR(255) NOT NULL, 
	[Category] NVARCHAR(255) NOT NULL, 
	CONSTRAINT [PK_Products] PRIMARY KEY ([Id])
)

INSERT INTO [dbo].[Products] ([Name], [Category]) VALUES (N'Product 1.1', N'Category 1')
INSERT INTO [dbo].[Products] ([Name], [Category]) VALUES (N'Product 1.2', N'Category 1')
INSERT INTO [dbo].[Products] ([Name], [Category]) VALUES (N'Product 1.3', N'Category 1')
INSERT INTO [dbo].[Products] ([Name], [Category]) VALUES (N'Product 2.1', N'Category 2')
INSERT INTO [dbo].[Products] ([Name], [Category]) VALUES (N'Product 2.2', N'Category 2')

log.txt

2018-10-12 05:37:59.1027 INFO Beginning database upgrade
2018-10-12 05:37:59.2127 INFO Checking whether journal table exists..
2018-10-12 05:37:59.2618 INFO Journal table does not exist
2018-10-12 05:37:59.2888 INFO Executing Database Server script 'DbUpExample.Migrator.Migrations.201810100538_AddProducts.sql'
2018-10-12 05:37:59.3031 INFO Checking whether journal table exists..
2018-10-12 05:37:59.3140 INFO Creating the [SchemaVersions] table
2018-10-12 05:37:59.3327 INFO The [SchemaVersions] table has been created
2018-10-12 05:37:59.4007 INFO Executing Database Server script 'DbUpExample.Migrator.Migrations.201810100553_AddCategories.sql'
2018-10-12 05:37:59.5687 INFO Upgrade successful

Options.cs

public class Options
{
    [Option('c', "connectionString", Required = true, HelpText = "The connection string to database that needs to be updated.")]
    public string ConnectionString { get; set; }
}

Program.cs

class Program
{
    static void Main(string[] args)
    {
        var result = Parser.Default.ParseArguments<Options>(args);

        result
            .WithParsed(r => Migrate(r));
    }

    private static void Migrate(Options options)
    {
        var migrator =
            DeployChanges.To
                .SqlDatabase(options.ConnectionString)
                .WithScriptsEmbeddedInAssembly(Assembly.GetExecutingAssembly(), s => Filter(s))
                .LogToAutodetectedLog()
                .Build();

        var result = migrator.PerformUpgrade();

        if (result.Successful)
        {
            Console.WriteLine("Success!");
        }
        else
        {
            Console.WriteLine(result.Error);
        }
    }

    private static bool Filter(string script)
    {
        return script.StartsWith("DbUpExample.Migrator.Migrations");
    }
}