Geekly Articles each Day
⬆️ ⬇️

FluentMigrator - Version Migration System



Hello. What are migrations and why they are needed are well described in the article Version migration of database structure: main approaches .

I want to tell you about the versioned migration system: FluentMigrator. Why do I like this particular project? Because of the nice migration syntax and support for various DBMS. Interested? Welcome under cat.



The content of the article



about the project

Briefly about the possibilities

Components of the system

How it works

Subtleties

Conclusion



about the project



The project itself is based on GitHub: github.com/schambers/fluentmigrator

My fork: github.com/tabushi/fluentmigrator

The history of repository changes begins from 17.12.2008

Distributed under the license: Apache License 2.0

Written in C # under .Net Framework 3.5



Briefly about the possibilities



Supported DBMS
Supported operations
Components of the system

How it works



And now let's take a closer look at the migration file. So, the migration file can contain the following:
Learn more about these entities.


Consider the migration using the following example:

using System;

using FluentMigrator;



namespace ExampleDatabaseMigrations

{

[Migration(2011091900)]

public class ExampleMigration : Migration

{

public override void Up()

{

if (!Schema.Table( "EXAMPLE_TABLE" ).Exists())

{

Create.Table( "EXAMPLE_TABLE" )

.WithColumn( "ID" ).AsInt16().NotNullable().PrimaryKey( "PK_EXAMTABL_ID" )

.WithColumn( "NAME" ).AsAnsiString(100).NotNullable()

.WithColumn( "SHORT_NAME" ).AsAnsiString(50).Nullable()

.WithColumn( "START_DATE" ).AsDate().NotNullable()

.WithColumn( "END_DATE" ).AsDate().Nullable();

Insert.IntoTable( "IDX_EXAMTABL_NAME" )

.Row( new {Id = 1, Name = "TEST" , Start_Date = new DateTime (2011, 9, 19)});

}

if (!Schema.Table( "EXAMPLE_TABLE" ).Index( "IDX_EXAMTABL_NAME" ).Exists())

{

Create.Index( "IDX_EXAMTABL_NAME" )

.OnTable( "EXAMPLE_TABLE" )

.OnColumn( "NAME" ).Ascending();

}

if (!Schema.Table( "EXAMPLE_TABLE" ).Index( "IDX_EXAMTABL_STARDATE_ENDDATE" ).Exists())

{

Create.Index( "IDX_EXAMTABL_STARDATE_ENDDATE" )

.OnTable( "EXAMPLE_TABLE" )

.OnColumn( "START_DATE" ).Ascending()

.OnColumn( "END_DATE" ).Ascending();

}

}



public override void Down()

{

if (Schema.Table( "EXAMPLE_TABLE" ).Exists())

Delete.Table( "EXAMPLE_TABLE" );

}

}

}



* This source code was highlighted with Source Code Highlighter .


In the example, I did not indicate anywhere the scheme for creating a table \ index, but of course there is such a possibility.

The numbering of migrations should go in ascending order, which is logical. But simply to order it seemed to us not convenient, so the numbering was taken as follows: yyyyMMddxx, where: yyyy is the year, MM is the month, dd is the day, xx is the number in the order from 00 to 99. Thus, the order is observed and the numbering says about the creation of the migration.

This example gives an approximate idea of ​​how the migration looks, and we’ll stop there.

')

Profile template


using FluentMigrator;



namespace ExampleDatabaseMigrations

{

[Profile( "Example" )]

public class ExampleProfile : Migration

{

public override void Up()

{

//do something

}



public override void Down()

{

//empty, not used

}

}

}



* This source code was highlighted with Source Code Highlighter .


As you can see, the profile is also inherited from the Migration class, but is marked with the Profile attribute. In the profile, only the Up method is executed (although in the example in the github documentation, the Down method is also filled, maybe it was also executed once). A migration file can contain an unlimited number of profiles with different names.



What is the difference between a profile and a migration other than attributes? That:
  1. a profile is executed only if it is explicitly set, indicating its name when performing migrations;
  2. the profile is always executed after successful migration, if there was nothing to install from the migrations, the profile is still executed.
Thus, the profile can be used for any service functions. Collect statistics or something. We use it to start the procedures for recompiling invalid database objects that have become such due to changes in the structure.



Most recently, on a githab was a request for adding a hybrid profile and migrations — profiles that have a migration number. The fact is that a person used a profile to fill in test data in the developers database when necessary, and since the database structure changed when adding migrations, he had to change the profile every time. This is an example of unsuccessful use of the profile. He was advised to use for this migration, within which through if to check some condition, such as an environment variable, since command line options in the migration, of course, are not available.



Version table


using FluentMigrator.VersionTableInfo;



namespace ExampleDatabaseMigrations

{

[VersionTableMetaData]

public class ExampleVersionInfo : IVersionTableMetaData

{

public string SchemaName { get { return "EXAMPLE_SCHEMA" ; } }

public string TableName { get { return "EXAMPLE_VERSION_TABLE" ; } }

public string ColumnName { get { return "EXAMPLE_VERSION_COLUMN" ; } }

}

}



* This source code was highlighted with Source Code Highlighter .


Thus, even in one scheme, you can have several different tables with versions for different projects, or keep the table with versions separately from the scheme with a project.

that will be if to set an empty line instead of a name of the scheme it is written above.



Embedded Scripts


Embedded scripts are ordinary sql scripts whose files are connected to the project and having the property “Build Action” = “Embedded Resource”. I don’t need to talk more about them.



How it works



Next, suppose that we are migrating up, i.e. Install the latest version of the database structure.



Work algorithm
  1. The program (Runner) is passed parameters containing, if briefly, data about the database, the migration file and the task itself (in fact, there are many parameters, and what I have indicated above is given by more than three parameters).
  2. Runner connects to the database and selects the maximum number in the version table, scans the migration file and performs the migration.
  3. In case of an error, the runner rolls back the migration on which it stumbled (with some exceptions, about which later in the subtleties section).
Perform one migration


Migration is divided into 2 stages.
  1. Execution of the Up () method (or Down (), in case of setting downward migrations), which adds expressions to the list of expressions
  2. Converting expressions to sql and sending to execution in the database

Subtleties



Changes are not always rolled back, since not all DBMSs support rolling back DDL operations. For such DBMS, migration is desirable to do as small as possible, from a single operation. If this one operation is not established, then it will not need to be rolled back.



It seems that the project was originally conceived for writing migrations once for all supported DBMS (and for basic simple operations it works). But nevertheless, it is worth admitting that the DBMS features and syntax are very different (for example, some DBMSs use auto-increment fields, and others have sequences), so recently a function with a speaker named IfDatabase has become available within migrations.



Conclusion



The project is open source, so you should not expect miracles from it. Check what happened on the required DBMS, as it may happen that something is not implemented for this DBMS. Describe the bugs - fix it, or help them fix it yourself.



PS: I want to ask you to speak about what else you would be interested to know. I will write about it.

Source: https://habr.com/ru/post/129242/

More articles:



All Articles