Migracja schematu bazy danych z Fluent Migrator - https://plawgo.pl/2018/10/09/migracja-schematu-bazy-danych-z-fluent-migrator/
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
[Migration(201810030717)] | |
public class AddCategory : Migration | |
{ | |
public override void Up() | |
{ | |
Create.Table("Categories") | |
.WithColumn("Id").AsInt32().NotNullable().PrimaryKey().Identity() | |
.WithColumn("Name").AsString(); | |
Execute.Sql("INSERT INTO dbo.Categories SELECT DISTINCT Category FROM dbo.Products;"); | |
Alter.Table("Products") | |
.AddColumn("CategoryId") | |
.AsInt32() | |
.Nullable(); | |
Execute.Sql("UPDATE p SET p.CategoryId = (SELECT c.Id FROM dbo.Categories c WHERE c.Name = p.Category) FROM dbo.Products p;"); | |
Alter.Column("CategoryId") | |
.OnTable("Products") | |
.AsInt32() | |
.NotNullable() | |
.ForeignKey("Categories", "Id") | |
.Indexed(); | |
Delete.Column("Category") | |
.FromTable("Products"); | |
} | |
public override void Down() | |
{ | |
Alter.Table("Products") | |
.AddColumn("Category") | |
.AsString() | |
.Nullable(); | |
Execute.Sql("UPDATE p SET p.Category = (SELECT c.Name FROM dbo.Categories c WHERE c.Id = p.CategoryId) FROM dbo.Products p;"); | |
Alter.Column("Category") | |
.OnTable("Products") | |
.AsString() | |
.NotNullable(); | |
Delete.ForeignKey() | |
.FromTable("Products") | |
.ForeignColumn("CategoryId") | |
.ToTable("Categories") | |
.PrimaryColumn("Id"); | |
Delete.Index() | |
.OnTable("Products") | |
.OnColumn("CategoryId"); | |
Delete.Column("CategoryId") | |
.FromTable("Products"); | |
Delete.Table("Categories"); | |
} | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
[Migration(201810030605)] | |
public class AddProduct : Migration | |
{ | |
public override void Up() | |
{ | |
Create.Table("Products") | |
.WithColumn("Id").AsInt32().NotNullable().PrimaryKey().Identity() | |
.WithColumn("Name").AsString() | |
.WithColumn("Category").AsString(); | |
Insert.IntoTable("Products").Row(new {Name = "Product 1.1", Category = "Category 1" }); | |
Insert.IntoTable("Products").Row(new {Name = "Product 1.2", Category = "Category 1" }); | |
Insert.IntoTable("Products").Row(new {Name = "Product 1.3", Category = "Category 1" }); | |
Insert.IntoTable("Products").Row(new {Name = "Product 2.1", Category = "Category 2" }); | |
Insert.IntoTable("Products").Row(new {Name = "Product 2.2", Category = "Category 2" }); | |
} | |
public override void Down() | |
{ | |
Delete.Table("Products"); | |
} | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
//aktualizacja bazy do najnowszej struktury | |
FluentMigratorExample.Migrator.exe -c Server=.\sqlexpress;Database=FluentMigratorExample;Trusted_Connection=True; | |
//cofnięcie bazy danych do wersji 201810030605 - same tabela Products | |
FluentMigratorExample.Migrator.exe -c Server=.\sqlexpress;Database=FluentMigratorExample;Trusted_Connection=True; -v 201810030605 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
2018-10-08 05:28:59.7465 INFO Using connection string Server=.\sqlexpress;Database=FluentMigratorExample;Trusted_Connection=True; | |
2018-10-08 05:29:00.0251 INFO VersionMigration migrating | |
2018-10-08 05:29:00.0680 INFO Beginning Transaction | |
2018-10-08 05:29:00.0861 INFO BEGIN TRANSACTION | |
2018-10-08 05:29:00.1290 INFO CreateTable VersionInfo | |
2018-10-08 05:29:00.1540 INFO CREATE TABLE [dbo].[VersionInfo] ([Version] BIGINT NOT NULL) | |
2018-10-08 05:29:00.2182 INFO => 0,0758923s | |
2018-10-08 05:29:00.2182 INFO Committing Transaction | |
2018-10-08 05:29:00.2502 INFO COMMIT TRANSACTION | |
2018-10-08 05:29:00.2502 INFO VersionMigration migrated | |
2018-10-08 05:29:00.2690 INFO => 0,1227984s | |
2018-10-08 05:29:00.3641 INFO VersionUniqueMigration migrating | |
2018-10-08 05:29:00.3740 INFO Beginning Transaction | |
2018-10-08 05:29:00.3740 INFO BEGIN TRANSACTION | |
2018-10-08 05:29:00.4100 INFO CreateIndex VersionInfo (Version) | |
2018-10-08 05:29:00.4771 INFO CREATE UNIQUE CLUSTERED INDEX [UC_Version] ON [dbo].[VersionInfo] ([Version] ASC) | |
2018-10-08 05:29:00.4941 INFO => 0,0741045s | |
2018-10-08 05:29:00.5030 INFO AlterTable VersionInfo | |
2018-10-08 05:29:00.5161 INFO | |
2018-10-08 05:29:00.5161 INFO => 0,0110072s | |
2018-10-08 05:29:00.5371 INFO CreateColumn VersionInfo AppliedOn DateTime | |
2018-10-08 05:29:00.5461 INFO ALTER TABLE [dbo].[VersionInfo] ADD [AppliedOn] DATETIME | |
2018-10-08 05:29:00.5461 INFO => 0,0149699s | |
2018-10-08 05:29:00.5690 INFO Committing Transaction | |
2018-10-08 05:29:00.5830 INFO COMMIT TRANSACTION | |
2018-10-08 05:29:00.5930 INFO VersionUniqueMigration migrated | |
2018-10-08 05:29:00.5930 INFO => 0,0479151s | |
2018-10-08 05:29:00.6380 INFO VersionDescriptionMigration migrating | |
2018-10-08 05:29:00.6490 INFO Beginning Transaction | |
2018-10-08 05:29:00.6600 INFO BEGIN TRANSACTION | |
2018-10-08 05:29:00.6700 INFO AlterTable VersionInfo | |
2018-10-08 05:29:00.6910 INFO | |
2018-10-08 05:29:00.7081 INFO => 0,0170768s | |
2018-10-08 05:29:00.7260 INFO CreateColumn VersionInfo Description String | |
2018-10-08 05:29:00.7410 INFO ALTER TABLE [dbo].[VersionInfo] ADD [Description] NVARCHAR(1024) | |
2018-10-08 05:29:00.7561 INFO => 0,0194427s | |
2018-10-08 05:29:00.7651 INFO Committing Transaction | |
2018-10-08 05:29:00.7651 INFO COMMIT TRANSACTION | |
2018-10-08 05:29:00.7830 INFO VersionDescriptionMigration migrated | |
2018-10-08 05:29:00.7973 INFO => 0,0453261s | |
2018-10-08 05:29:00.7973 INFO 201810030605: AddProduct migrating | |
2018-10-08 05:29:00.8230 INFO Beginning Transaction | |
2018-10-08 05:29:00.8350 INFO BEGIN TRANSACTION | |
2018-10-08 05:29:00.8550 INFO CreateTable Products | |
2018-10-08 05:29:00.8699 INFO 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])) | |
2018-10-08 05:29:00.8888 INFO => 0,0218413s | |
2018-10-08 05:29:00.8888 INFO INSERT INTO [dbo].[Products] ([Name], [Category]) VALUES (N'Product 1.1', N'Category 1') | |
2018-10-08 05:29:00.9161 INFO INSERT INTO [dbo].[Products] ([Name], [Category]) VALUES (N'Product 1.2', N'Category 1') | |
2018-10-08 05:29:00.9270 INFO INSERT INTO [dbo].[Products] ([Name], [Category]) VALUES (N'Product 1.3', N'Category 1') | |
2018-10-08 05:29:00.9270 INFO INSERT INTO [dbo].[Products] ([Name], [Category]) VALUES (N'Product 2.1', N'Category 2') | |
2018-10-08 05:29:00.9450 INFO INSERT INTO [dbo].[Products] ([Name], [Category]) VALUES (N'Product 2.2', N'Category 2') | |
2018-10-08 05:29:00.9580 INFO -> 5 Insert operations completed in 00:00:00.0580001 taking an average of 00:00:00.0116000 | |
2018-10-08 05:29:00.9678 INFO INSERT INTO [dbo].[VersionInfo] ([Version], [AppliedOn], [Description]) VALUES (201810030605, '2018-10-08T03:29:00', N'AddProduct') | |
2018-10-08 05:29:00.9874 INFO Committing Transaction | |
2018-10-08 05:29:01.0000 INFO COMMIT TRANSACTION | |
2018-10-08 05:29:01.0000 INFO 201810030605: AddProduct migrated | |
2018-10-08 05:29:01.0230 INFO => 0,063068s | |
2018-10-08 05:29:01.0490 INFO 201810030717: AddCategory migrating | |
2018-10-08 05:29:01.0682 INFO Beginning Transaction | |
2018-10-08 05:29:01.0797 INFO BEGIN TRANSACTION | |
2018-10-08 05:29:01.1142 INFO CreateTable Categories | |
2018-10-08 05:29:01.1260 INFO CREATE TABLE [dbo].[Categories] ([Id] INT NOT NULL IDENTITY(1,1), [Name] NVARCHAR(255) NOT NULL, CONSTRAINT [PK_Categories] PRIMARY KEY ([Id])) | |
2018-10-08 05:29:01.1467 INFO => 0,0206976s | |
2018-10-08 05:29:01.1611 INFO ExecuteSqlStatement INSERT INTO dbo.Categories SELECT DISTINCT Category FROM dbo.Products; | |
2018-10-08 05:29:01.1717 INFO INSERT INTO dbo.Categories SELECT DISTINCT Category FROM dbo.Products; | |
2018-10-08 05:29:01.1900 INFO => 0,0199321s | |
2018-10-08 05:29:01.2050 INFO AlterTable Products | |
2018-10-08 05:29:01.2050 INFO | |
2018-10-08 05:29:01.2260 INFO => 0,0109935s | |
2018-10-08 05:29:01.2400 INFO CreateColumn Products CategoryId Int32 | |
2018-10-08 05:29:01.2500 INFO ALTER TABLE [dbo].[Products] ADD [CategoryId] INT | |
2018-10-08 05:29:01.2640 INFO => 0,014039s | |
2018-10-08 05:29:01.2640 INFO ExecuteSqlStatement UPDATE p SET p.CategoryId = (SELECT c.Id FROM dbo.Categories c WHERE c.Name = p.Category) FROM dbo.Products p; | |
2018-10-08 05:29:01.2910 INFO UPDATE p SET p.CategoryId = (SELECT c.Id FROM dbo.Categories c WHERE c.Name = p.Category) FROM dbo.Products p; | |
2018-10-08 05:29:01.3050 INFO => 0,0140096s | |
2018-10-08 05:29:01.3180 INFO AlterColumn Products CategoryId Int32 | |
2018-10-08 05:29:01.3330 INFO ALTER TABLE [dbo].[Products] ALTER COLUMN [CategoryId] INT NOT NULL | |
2018-10-08 05:29:01.3456 INFO => 0,0151768s | |
2018-10-08 05:29:01.3583 INFO CreateForeignKey FK_Products_CategoryId_Categories_Id Products(CategoryId) Categories(Id) | |
2018-10-08 05:29:01.3730 INFO ALTER TABLE [dbo].[Products] ADD CONSTRAINT [FK_Products_CategoryId_Categories_Id] FOREIGN KEY ([CategoryId]) REFERENCES [dbo].[Categories] ([Id]) | |
2018-10-08 05:29:01.3966 INFO => 0,0264217s | |
2018-10-08 05:29:01.4112 INFO CreateIndex Products (CategoryId) | |
2018-10-08 05:29:01.4215 INFO CREATE INDEX [IX_Products_CategoryId] ON [dbo].[Products] ([CategoryId] ASC) | |
2018-10-08 05:29:01.4410 INFO => 0,0194277s | |
2018-10-08 05:29:01.4540 INFO DeleteColumn Products Category | |
2018-10-08 05:29:01.4690 INFO DECLARE @default sysname, @sql nvarchar(max); | |
-- get name of default constraint | |
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' | |
); | |
-- create alter table command to drop constraint as string and run it | |
SET @sql = N'ALTER TABLE [dbo].[Products] DROP CONSTRAINT ' + QUOTENAME(@default); | |
EXEC sp_executesql @sql; | |
-- now we can finally drop column | |
ALTER TABLE [dbo].[Products] DROP COLUMN [Category]; | |
2018-10-08 05:29:01.5502 INFO => 0,0842406s | |
2018-10-08 05:29:01.5620 INFO INSERT INTO [dbo].[VersionInfo] ([Version], [AppliedOn], [Description]) VALUES (201810030717, '2018-10-08T03:29:01', N'AddCategory') | |
2018-10-08 05:29:01.5620 INFO Committing Transaction | |
2018-10-08 05:29:01.5851 INFO COMMIT TRANSACTION | |
2018-10-08 05:29:01.5992 INFO 201810030717: AddCategory migrated | |
2018-10-08 05:29:01.6113 INFO => 0,1299746s |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
public class Options | |
{ | |
[Option('c', "connectionString", Required = true, HelpText = "The connection string to database that needs to be updated.")] | |
public string ConnectionString { get; set; } | |
[Option('v', "version", Required = false, HelpText = "The database version.")] | |
public long? Version { get; set; } | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 serviceProvider = CreateServices(options.ConnectionString); | |
using (var scope = serviceProvider.CreateScope()) | |
{ | |
UpdateDatabase(scope.ServiceProvider, options); | |
} | |
} | |
private static IServiceProvider CreateServices(string connectionString) | |
{ | |
return new ServiceCollection() | |
.AddFluentMigratorCore() | |
.ConfigureRunner(rb => rb | |
.AddSqlServer2016() | |
.WithGlobalConnectionString(connectionString) | |
.ScanIn(typeof(Program).Assembly).For.Migrations()) | |
.AddLogging(lb => lb.AddFluentMigratorConsole().AddNLog()) | |
.BuildServiceProvider(true); | |
} | |
private static void UpdateDatabase(IServiceProvider serviceProvider, Options options) | |
{ | |
var runner = serviceProvider.GetRequiredService<IMigrationRunner>(); | |
if (options.Version.HasValue) | |
{ | |
runner.MigrateDown(options.Version.Value); | |
} | |
else | |
{ | |
runner.MigrateUp(); | |
} | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment