Skip to content
This repository

Versioning Your Database with RoundhousE

Version The Way You Want

Versioning is very important and done exactly how you want to do versioning. RH has a convention that you provide it the version number through a TXT file (uses the file contents), an XML file (and an xpath to the version) or a DLL file (uses the file version). The other thing it wants to know is the repository path (if provided). It is preferred that you version based on the same way you version your code. In this sample, versioning is done with source control revisions as the last number in the version. Notice that RH is looking at an XML file and XPath for versioning. As mentioned it can also use a DLL or a TXT file. It is intelligent enough to know the difference based on the file name from the configuration.

Global Versioning Recommended

It is recommended you use a versioning technique that applies globally to all instances of your databases. Globally means that when a database is on a particular version in an environment (like STAGE), you can guarantee that if another database is on that same version, it has all of the same scripts applied through RH (technically it could be different based on outside influences, but through normal use it is the same as far as RH scripts applied to the database).

Defaults

When you supply nothing:

  • VersionFile = "_BuildInfo.xml"
  • VersionXPath = "//buildInfo/version"

Sequence Number Based - Non-Global

SET DIR=%~d0%~p0%
"%DIR%rh.exe" /d=bob

If you give RoundhousE nothing for versioning (and it doesn't find the default file), it will version by a number. It holds no representation of a true version and is not global, but it's a start.
The method it uses here is the method that you see in other migration tools where a number increases by one every time you migrate. The method RH uses is the same method it does for the others, you just look at id instead of version.

Sequence Only

In the image above you notice how version is always zero. The id increments up though. When using sequence-based versioning, you look only at the ID.

Why is sequence number versioning non-global?

Since the number goes up every time you run the migrator, you could have one instance on DEV on version 10 and an instance on TEST on version 2 and they would have the same scripts applied to them. How does this happen?

Say I create a script and run it against DEV. That takes me to version 1. Then I create 5 other scripts and run them one at a time against DEV. If you are keeping track that puts us on version 6. Now we need to apply the scripts to TEST. My first run creates the database and applies the scripts. The same 6 scripts are applied but I'm only a version 1 on TEST. That doesn't seem right. Except that it is. That's how sequence number versioning works and why it is non-global. That's also why it is not recommended.

Script Number Based - Non-Global - v0.8.5+ - Fixed in v0.8.6

SET DIR=%~d0%~p0%
"%DIR%rh.exe" /d=bob /vf="sql"

This uses the highest number found from the scripts in the up folder. This doesn't work perfectly as it is a newer versioning

Why is script number versioning non-global?

The best example I can think of is two scripts with the same number 0002_UpdatesFromWorkOrder1.sql and 0002_UpdatesFromWorkOrder2MergedAroundTheSameTimeAs1.sql. How does this happen? Two different people working on feature branches or workorder branches and merge them in and deploy. Both only saw 001_CreateTables.sql in the up folder so they both naturally created 0002_something to signify the next version. One has deployed their version 0002 to one instance on TEST and the other 0002 on their instance on TEST. Both are 0002 but not globally the same.

To take this out to its logical conclusion, when the last person merges back in to go to production, they rename their script to 0003_something.sql. So now production is on version 0003 while the TEST instance is on 0002. They might be the same, they might be different.

XML Based - Global

SET DIR=%~d0%~p0%
"%DIR%rh.exe" /d=bob /vf="_BuildInfo.xml" /vx="//buildInfo/version" /r="http://roundhouse.googlecode.com/svn"

Global Versions

==================================================
Versioning
==================================================
 Attempting to resolve version from C:\code\roundhouse\code_drop\sample\deployment\_BuildInfo.xml using //buildInfo/version.
 Found version 0.8.0.305 from C:\code\roundhouse\code_drop\sample\deployment\_BuildInfo.xml.
 Migrating TestRoundhousE from version 0 to 0.8.0.305.
 Versioning TestRoundhousE database with version 0.8.0.305 based on http://roundhouse.googlecode.com/svn.

DLL Based - Global

SET DIR=%~d0%~p0%
"%DIR%rh.exe" /d=bob /vf="%DIR%..\..\code_drop\RoundhousE\lib\roundhouse.dll" /r="http://roundhouse.googlecode.com/svn"

Global Versions

==================================================
Versioning
==================================================
Attempting to resolve assembly file version from C:\code\roundhouse\code_drop\RoundhousE\lib\roundhouse.dll. 
Found version 0.8.0.305 from C:\code\roundhouse\code_drop\RoundhousE\lib\roundhouse.dll.
 Migrating TestRoundhousE from version 0 to 0.8.0.305.
 Versioning TestRoundhousE database with version 0.8.0.305 based on http://roundhouse.googlecode.com/svn.

TXT Based - Global - v0.8.6+

SET DIR=%~d0%~p0%
"%DIR%rh.exe" /d=bob /vf="%DIR%..\..\code_drop\version.txt" /r="http://roundhouse.googlecode.com/svn"

Global Versions

==================================================
Versioning
==================================================
 Attempting to resolve version from text file C:\code\roundhouse\code_drop\version.txt.
 Found version 2.30.2012.02.15.1 from C:\code\roundhouse\code_drop\version.txt.
 Migrating TestApp from version 2.29.2012.01.26.2 to 2.30.2012.02.15.1.
 Versioning TestApp database with version 2.30.2012.02.15.1 based on http://roundhouse.googlecode.com/svn.

Version Table

Below is an example of the version table.

Versioning

Something went wrong with that request. Please try again.