Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Migracja schematu bazy danych z DbUp
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];
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')
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
public class Options
{
[Option('c', "connectionString", Required = true, HelpText = "The connection string to database that needs to be updated.")]
public string ConnectionString { get; set; }
}
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");
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment