A few weeks ago I found a reference to DBVerse in The Morning Brew #58 which seemed like a great starting point for a problem I've had time and time again - keeping databases in sync from a structure perspective making sure that all the dependencies are in place and there is little or no risk when running these changes into a live server.

Bruce and I spent an hour or so today looking into this tool as part of our Madgex ILP time, and the following are our observations.

DBVerse is a C# project, which you install as a template on your machine. In my case, I copied the entire DbVerseDatabaseProjectTemplate.zip file, still as a zip, into my My Documents\Visual Studio 2005\Templates\ProjectTemplates folder.

To add a new DBVerse project to a solution, it is pretty simple, just a case of Adding a new project of type DbVerse Database Project. This will then create a new project, consisting of a DbVerse folder (which keeps all the required DLL and EXE files), a Scripts folder (in which any SQL scripts can be kept) and three cs files - Methods.cs, Methods.Initialize.cs and Methods.Updates.cs

The cs file we were most interested in was Methods.Updates.cs as that is our prime requirement - record and manage updates. In DBVerse updates can be scripted as SQL and put into the Scripts folder and then executed via
Db.ExecuteScript("UpdateJaneTestTable.sql");
or by using the DB objects that are exposed from the Lunaverse.DbVerse.Core such as   Db.AddTable("JaneTest");
  Db.Table("JaneTest").AddColumn("ID", DataType.Int);
  Db.Table("JaneTest").AddColumn("Name", DataType.NVarChar(255));. I'm a big believer in scripting out changes in SQL, as these can be tested against the database and written in a none destructive manner (i.e. checking for existance of an object before updating it etc).

Each update method is given an attribute similar to
[Update(2, "Jane Dallaway", "25/04/2008")]
where 2 is the update number, "Jane Dallaway" is the author and "25/04/2008" is the date that the update was written.

The DBVerse project produces a dll, which is run via either a GUI or a console application. To run the updates via the console application I ran
Lunaverse.DbVerse.Console.exe ApplyAllUpdates

A new table is created - Database Updates - which records all the updates scripts which have run, storing the ID, Method Name, Author, Authored data and Application Date.

On subsequent runs I expected this table to be checked before any updates were applied, and to test this I created the following Update
[Update(3, "Jane Dallaway", "25/04/2008")]
  public void InsertData()
  {
    Db.ExecuteScript(("InsertData.sql"));
  }
I commented out all previous Updates to make it easy to check and ran
Lunaverse.DbVerse.Console.exe ApplyAllUpdates

The output was
Starting...
------------------------------------------------------------
Server=localhost Database=JaneTest Method count=-1
------------------------------------------------------------
Method starting [ApplyAllUpdates]
------------------------------------------------------------
Script [InsertData.sql] was run
Inserted row into table [DatabaseUpdates] ('InsertData', 'Jane Dallaway', '25/04/2008 00:00:00', '25/04/2008')
------------------------------------------------------------
Method finished [ApplyAllUpdates]
------------------------------------------------------------
Done: all methods completed
------------------------------------------------------------
When I checked the database I got the following output:
ID    Name          Description
1     Test          Test via DBVerse

(1 row(s) affected)

so all as I expected.

I then ran the console application again, and got the following output
Starting...
------------------------------------------------------------
Server=localhost Database=NewDesignPartners Method count=-1
------------------------------------------------------------
Method starting [ApplyAllUpdates]
------------------------------------------------------------
Script [InsertData.sql] was run
------------------------------------------------------------
Method finished [ApplyAllUpdates]
------------------------------------------------------------
Done: all methods completed
------------------------------------------------------------
which has differing output to the previous time in the Script [InsertData.sql] line as last time we were notified what was inserted and this time we weren't.
On examining the database, the insert was run again resulting in the following rowsID    Name          Description
1     Test          Test via DBVerse
1     Test         Test via DBVerse

(2 row(s) affected)

This indicates that the check that the update hasn't already been run is causing an issue and cannot be relied upon. This invalidates the tool for our use. All of our other observations were minor - such as not being able to work out how to run all the updates in one go in the GUI, I could only seem to work out how to run one update at a time.

This project is a work in progress, and is probably worth keeping an eye on. Maybe I'll revisit it at a later stage, but I think it might be time to write something that does exactly what we need for the Madgex environment.