Fluent Migrations - another way to track database changes

Every software developer is a potential creator having a goal to leave a remarkable thumbprint in project development cycle. Fuzzy type names, discussion-proofed coding conventions, the variety of third-party components used in projects - those are a few ways to express one's creativity.

Things get even more creative when it comes to database development as various techniques are used in the projects to track database changes. One of the most popular solutions is to maintain SQL change script file set and have some execution engine (batch file, Ant build script, etc.) to apply them to the target database incrementally. Another widely mentioned approach is to do generate SQL change scripts using schema compare tool (e.g. Redgate SQL Compare, tablediff).

Fluent migrations could be mentioned as a third option. In this article I am referring to fluent migrations as a part of FluentMigrator API. It is a .NET API that can be utilized to perform incremental database changes. Each database change is described as a Migration class descendant and basically consists of two methods:

• Up() - logic to perform incremental change
• Down() - logic to revert incremental change

Here is a sample of a complete migration class:

   [Migration(201208150020)]
   public class CreateUserTable : Migration
   {
      public override void Up()
      {
         Create.Table("Users");
      }

      public override void Down()
      {
         Delete.Table("Users");
      }
   }

I am not going to dive deep into the API details - some documentation is available here. My goal is to mention several features of Fluent Migrator which might bring it to the projects.

1. Unique API for all supported databases - with saying that I am 90% right as for example you cannot have unique syntax for adding identity column for MS SQL and Oracle database table as there is no identity column type for Oracle. But general routines - adding/dropping a table, adding/dropping/modifying a column, managing indexes - are supported. On the other hand methods to execute SQL statements and external SQL files are also provided. The list of supported databases is available here. 
2. Migration runners - a set of tools is provided to run migrations on the specified database. Those can be used as stand-alone tools or incorporated into continuous integration build process. No more batch scripts should be required.
3. Ability to rollback migration - it was already mentioned that each migration class consists of two methods: one for performing and another for rolling back a migration. With that in mind you could think of a situation where after performing several migrations you still have a possibility to bring your database to some previous state. Think of a software with pluggable components - migration sounds like a good way to install/remove them. 

Intersecting date intervals - the easy way

I guess this is a common situation were you have a task to filter a list of objects that fall into some date range. "Get all my tasks entered today", "Get all my payments made through the last week " - those could be quite realistic examples.

SELECT * FROM dbo.Tasks 
    WHERE tsk_DateCreated BETWEEN @today_start AND @today_end

  SELECT * FROM dbo.Payments
    WHERE pmt_DateMade BETWEEN @week_start_date AND @week_end_date

Those are quite easy scenarios, as you only have a single date to put into the interval: tsk_DateCreated for tasks and pmt_DateMade for payments. Things get little more complicated when you have two date fields come into play.

Examples: "Get all employee vacation list that intersect with a given period", "Get all user accounts that were valid during the given interval".

Solution (the hard way):

SELECT * FROM dbo.EmployeeVacations
    WHERE (empv_DateFrom BETWEEN @date_from AND @date_to) OR 
          (empv_DateTo BETWEEN @date_from AND @date_to) OR 
          (empv_DateFrom <= @date_from AND empv_DateTo >= @date_to) 

  SELECT * FROM dbo.Users
    WHERE (usr_DateFrom BETWEEN @date_from AND @date_to) OR 
          (usr_DateTo BETWEEN @date_from AND @date_to) OR 
          (usr_DateFrom <= @date_from AND usr_DateTo >= @date_to) 

Solution (the easy way):

SELECT * FROM dbo.EmployeeVacations
    WHERE (NOT((empv_DateTo < @date_from) OR (empv_DateFrom > @date_to)))

  SELECT * FROM dbo.Users
    WHERE (NOT((usr_DateTo < @date_from) OR (usr_DateFrom > @date_to)))

Give it a try, you will not regret it.

Update:  another way to check for date interval intersection:

SELECT * FROM dbo.EmployeeVacations
    WHERE (@date_from BETWEEN empv_DateFrom AND empv_DateTo OR empv_DateFrom BETWEEN @date_from AND @date_to)

  SELECT * FROM dbo.Users
    WHERE (@date_from BETWEEN usr_DateFrom AND usr_DateTo OR usr_DateFrom BETWEEN @date_from AND @date_to)

Nice reading about SQL indexing and SQL tuning for developers

My colleagues shared with me a link to an online SQL indexing and tuning tutorial (a guide to database performance by Markus Winand) which I think is worth reading. "Indexed ORDER BY", "Searching for ranges" - those are few sample topics described in the book. Different DBMS are covered - MS SQL, Oracle and others. You can also check your SQL tuning knowledge by taking a short online test.