How to setup database versioning tool RoundhousE

Introduction

RoundhousE (RH) is an automated database deployment (change management) system. RH manage the versions by the same idiom used by other database management systems i.e. SQL scripts. RoundhousE currently works with Oracle, SQL Server (2000/2005/2008/Express), Access, MySQL, SQLite and PostgreSQL. It comes in the form of a tool, MSBuild, and an embeddable DLL. While someone is working on a GUI, there is no visual tool at the current time.

Prerequisites

Database installed

Whatever database you are going to use, be it SQL Server or be it MySQL, needs to be installed so you can do database change management.

.NET Framework 3.5

You will also need to have the .NET framework installed. Currently RH uses 3.5.

Steps to configure RoundhousE

As earlier I have told you that RH comes in the form of tool,MSBuild and embeddable DLL; here I will show you how to configure RH by using MSBuild file with sql server database RoundhouseDb. By using MSBuild file we can build RH project separately so that we can build database project when we add some scripts to it.

Step 1

• First create your database in sql server or any other database engine. Download roundhouse from link https://code.google.com/p/roundhouse/downloads/list

• Create one project as class library in your present solution in visual studio. For example RoundhouseTest .

• Right click on references in project. Choose 'Manage NuGet Packages'.

• It will open one window. In that search for roundhouse. You will get list of roundhouse packages to install. Select Roundhouse MSBuild package from that list and click on install. You will get some files. Open DBDeploy_MSBuild.proj file from them.

Step 2

• Now you have to configure your database in DBDeploy_MSBuild.proj file using selected tags.

• Find your project solution here RoundhouseTest  on hard disk and create one folder named Lib in root folder RoundhouseTest .

• Now you have already downloaded roundhouse.
  
  Copy these files

1. log4net.dll
2. roundhouse.dll
3. roundhouse.tasks.dll

and paste them in your Lib folder that you have created now.

• Open DBDeploy_MSBuild.proj file. In this file first you have <UsingTask> tag. Set AssemblyFile attribute to"$(ProjectDir)Lib\roundhouse.tasks.dll" keep TaskName as it is.

• Now in <Roundhouse> tag write your database server name in ServerName Attribute. If you are using sql server locally then write (local)\SQLEXPRESS

• Write your database name in DatabaseName attribute i.e. RoundhoseDb here.

• Write your connection string in ConnectionString attribute.

• In SqlFileDirectory give the folder name in which you are going to place your scripts. Prior to this
  make a folder structure in your project as shown in image. Here my sql file directory is Databse as shown in figure.

• Set Silent = "true".

Note:

• In alterDatabase folder you will have only one script to initialise the database

• In functions you will have functions.

• In sprocs there will be stored procedures.

• And in up folder you will have DDL( insert/update/delete) and DML(schema changes - database structure) scripts. These folder name should not be changed because they are already known to roundhouse.

Step 3

• Now right click on RoundhouseTest project and click on Unload Project.

• Again right click on RoundhouseTest project and select edit RoundhouseTest.csproj file.

• See at the last end of file one commented block is there. First uncomment it .

• in tag <Target Name="AfterBuild"> write

<MSBuild Projects="DBDeploy_MSBuild.proj"
Properties="VersionAssembly=$(TargetDir)RoundhouseTest.dll"/>

• After this right click on RoundhouseTest project and reload it.

Step 4

• Now write one script to insert data into database table. Add that script to up folder. Now build your
  roundhouse project library.

• Check your database in sql server it will have three more tables for your database those tables are for roundhouse version management.

Note:

• In your up folder at first position you have to place Baseline.sql file (Not required same name) which contains database creation script. Just you have to convert your database into script from sql server management studio. Means if your database gone accidently then you can recover its schema back by this script. Just you have to create empty database in sql server and build your RoundhouseTest project once again.

• Remember one thing always that , you have to give names to script as acsending order of names i.e. One should prepend your order specific scripts with either a number moving upwards padded with three zeros(i.e. 0001_somescript.sql) followed by 0002_nextscript) or a nice long date time in YYYYMMddHHmmss format(i.e. 201401231400_somescript.sql) followed by 201401231401_nextscript.sql . In up folder latest script should be at the end.

• If you want to update the existing script which was already executed then make changes in file and rename it by current date and time as above to bring that script at the end

• For documentation of roundhouse you can read on this link

https://github.com/chucknorris/roundhouse/wiki

Points of Interest

While getting work RH I had gone some interesting things. There were no articles on RH on internet which can specify the steps that How exactly one can setup RH in simple way. I had done all things that were specified in other articles on internet but they were all about the RH as a tool. So at the last got one way where to change settings to build RH project separately. After those changes I have got success.