RoundhousE: Configuration

 

This week I introduced RoundhousE and it’s getting quite a stir. Let’s take a look at the configuration. We are going to focus on the MSBuild/NAnt task configuration for now because the console isn’t yet complete. The console will actually have at least this much if not more (we’ve talked about how to use it to create the migration scripts and not just run them).  Because we like to be able to infer things, we have determined that there are only two required items. Thus – the minimal configuration:

Minimal Configuration

<!-- basic functionality, these are the required items-->
<roundhouse
  databaseName="YOURDATABASENAME"
  sqlFilesDirectory="..\..\db"
  />

 This is the minimal configuration that you would need to run RoundhousE (RH for the remainder of this post). This will not version your table unless you have a _BuildInfo.xml file sitting next to your deployment.bat files. You can get that if you use UppercuT to do your automated builds.

  imageimage

When you use the minimal configuration, it is assumed that you are running migration on the default instance of the server/computer you are on at that time.

databaseName is the database you want to create/migrate. Yes, RH will create a database if there is not one already. There is no need to tell it you want it to create or just update. RH infers that you want to do both if there is no database.
sqlFilesDirectory is where your SQL Scripts are. We will get to this in a later post, but basically, in the folder that has your sql scripts, RH is going to look for these folders (in this order) and run scripts in them: up, runFirstAfterUp, functions, views, sprocs, and permissions. In the up folder you put only the files you want to run one time. These are DDL/DML scripts. Once you run them, don’t change them or RH will shut down execution with an error (configurable to a warning).  The other folders are items that should run every time. Right now these items should be idempotent scripts, in other words, written to be run again and again without issue. The sample that comes with with RH shows this behavior.

FUTURE ENHANCEMENT: RH will either drop and recreate these scripts (capturing and restoring permissions) or create the alter script mentality by reading through your script.

Full Configuration

<!-- FULL configuration, for changing conventions -->
<roundhouse
  serverName="(local)"
  databaseName="YOURDATABASENAME"
  sqlFilesDirectory="..\..\db"
  repositoryPath="http://tellmewherethisis.com/svn/"
  versionFile="_BuildInfo.xml"
  versionXPath="//buildInfo/version"
  upFolderName="up"
  downFolderName="down"
  runFirstAfterUpFolderName="runFirstAfterUp"
  functionsFolderName="functions"
  viewsFolderName="views"
  sprocsFolderName="sprocs"
  permissionsFolderName="permissions"
  schemaName="RoundhousE"
  versionTableName="Version"
  scriptsRunTableName="ScriptsRun"
  environmentName="LOCAL"
  restore="false"
  restoreFromPath="\\tell\me\where\YOURDATABASENAME.bak"
  outputPath="C:\RoundhousE_runs"
  warnOnOneTimeScriptChanges="false"
  nonInteractive="false"
  databaseType="roundhouse.databases.sqlserver2008.SqlServerDatabase, roundhouse.databases.sqlserver2008"
  />

This is quite a configuration. It allows for you to do just about everything you would want to do with RH. Let’s get cracking through shall we?

serverName is the server and the instance you would like to go to. (local) and (local)\MSSQL2000 are both valid values.
databaseName we talked about above.
sqlFilesDirectory we talked about above as well. Next.
repositoryPath is literally that. It can be any value because it is only recorded in the version table. Once set it is not recommended you change this value.
versionFile is either an Xml file or a DLL that RH can grab the version from.
versionXPath is what you supply if you supply an Xml File. RH has to know where to go to get the value.
imageupFolderName is the name of the folder for update scripts. Call it whatever you want, we don’t care. Just tell us here.
downFolderName is for downgrading scripts. This is a future enhancement to RH.

FUTURE ENHANCEMENT: RH will be able to downgrade a database to a particular version.

 

runFirstAfterUpFolderName is where you put functions, views, sprocs, or permissions that are order dependent. RH has a certain order it runs scripts in based on both folder names and names of scripts. If a view depends on another view and you are not able to get it named appropriately to run before the other view, put it here. If you have a function that depends on a view, you definitely need the view in this folder.
functionsFolderName is for functions. By the way, all of these folders are recursively walked, so if you want to further separate by folders, that’s great, too!
viewsFolderName is for views.
sprocsFolderName is for….ya, you’re figuring it out.
permissionsFolderName. I’m not sure I need to say anything here if you are still with me at this point. On to the cooler settings…
schemaName is for when you want to have the RH tables to be in a different schema. If you put in YOURCOMPANY, you will get a different schema. Once set, do not change this. This is definitely running with scissors and very sharp.

  Well, put in your company name, not YOURCOMPANY

  Is there really a company out there called YOURCOMPANY?
versionTableName allows you to name the version table something else if you don’t like the name of it or it conflicts with something else. Once set, do not change this.
scriptsRunTableName allows you to change the name of the scripts run table for the same reasons as versionTableName. Once set, do not change this. And when we say once set, it may mean the first time you go to prod and can no longer drop and restore databases on a whim.  These are very sharp to allow you to have flexibility, but because it is a knife you can still get cut if you use it wrong. I’m just saying. You’ve been warned.
environmentName allows you to have certain scripts that only run in certain environments. This is very useful for say the permissions scripts.

FUTURE ENHANCEMENT: Looking at Environments in the script names to determine whether to run or not.

restore tells RH whether to restore a database before migrating. This becomes handy when the DDL/DML scripts are in flux and/or once you’ve gone to production and are doing maintenance. There is an example of how you would do this in the sample.
restoreFromPath tells RH where to get the backed up database. Neat convention: If you use SQLServer and Litespeed, back up your database and put LS at the end of the file name (like TimmyLS.bak) and RH will convert over to do a Litespeed restore.
outputPath is where you want RH to set everything it ran in the migration. For more information see this enhancement: http://code.google.com/p/roundhouse/issues/detail?id=4

FUTURE ENHANCEMENT: If the enhancement request is still open, this is not done yet. Did I mention it will also zip the files up? http://code.google.com/p/roundhouse/issues/detail?id=5

FUTURE ENHANCEMENT: This may be where the backup is located as well: http://code.google.com/p/roundhouse/issues/detail?id=3

warnOnOneTimeScriptChanges is how you can turn off the error that occurs when RH sees a change to a one time script (DDL/DML). It is recommended that you never change the scripts that should only run once, but if you do, you are going to need to set this to true.
nonInteractive tells RH not to ask for user input when it runs. This is how you can set up scheduled jobs to run without you needing to be around.
databaseType is the type of database you are migrating. This is a bit of a misnomer. If you have 2008 installed, you can still deploy to a 2005 database. But it doesn’t work the other way around.  Here are the settings:

- roundhouse.databases.sqlserver2008.SqlServerDatabase, roundhouse.databases.sqlserver2008

- roundhouse.databases.sqlserver2005.SqlServerDatabase, roundhouse.databases.sqlserver2005

- roundhouse.databases.oracle.OracleDatabase, roundhouse.databases.oracle (FUTURE ENHANCEMENT)

- roundhouse.databases.mysql.MySqlDatabase, roundhouse.databases.mysql (FUTURE ENHANCEMENT)

NOTE: databaseType is a plug in model, so if you have a type that is not officially supported yet, you can write your own. Just reference roundhouse.dll and implement roundhouse.sql.Database (it’s an interface).  Then just make sure your DLL sits next to roundhouse.dll, edit databaseType to point to your assembly.

  Implementing Database

This is a lot to take in, so I’ll stop here. Oh wait - that was all of it. I’ll stop here because we’re done.  If this hasn’t intrigued you to at least download RH and take a look at it, perhaps the versioning aspect will when we talk about it. There is nothing better than knowing what revision in source to look at to find a problem with a stored procedure or a view.

Next up: A closer look at the versioning aspect and how you can version your database based on what you have in source control - RoundhousE : Version Your Database

kick it on DotNetKicks.com Shout it

Print | posted @ Sunday, November 15, 2009 4:06 AM

Comments on this entry:

Gravatar # re: RoundhousE: Configuration
by Elias Rangel at 11/18/2009 5:14 PM

Nice tool.
Two questions:
Shouldn't there be a folder for indexes?
How would you handle test data loading?
Gravatar # re: RoundhousE: Configuration
by Robz at 11/19/2009 1:35 AM

Great questions. Take a look at the enhancements. Index removal and addition would not delete data so they could definitely get their own folder.

Test data loading is an environment concern. We have plans to make RoundhousE environment aware so you could do this exact thing. Check out the roadmap - http://code.google.com/p/roundhouse/#roadmap
The line in version 2 that says environment aware for permissions should really be changed to environment aware.
Gravatar # re: RoundhousE: Configuration
by Hugo Fragoso at 12/31/2009 8:36 AM

Great Project structure, and arquitecture. Testing it out over migrator.net as it has a cleaner script visibility to production DBA's.

One question arises after 1 hour searching source code for error tracking, it seems like the connectionstring parameter is not working, as is not being passed to connection classes.
Gravatar # re: RoundhousE: Configuration
by Robz at 12/31/2009 10:07 AM

@Hugo: The connection string item was new at revision 88. It wasn't quite ready for release yet, but thank you for catching that.

I added it at roundhouse.infrastructure.ApplicationConfiguraton.cs:166
Gravatar # re: RoundhousE: Configuration
by Robz at 12/31/2009 10:11 AM

@Hugo: Also, the connection string is not needed for SqlServer - only if you are going to get into OleDb or ODBC (possible future enhancement).

Revision93 should be a good one for release. I will know more about it once I get it rockin...
Gravatar # re: RoundhousE: Configuration
by Hugo Fragoso at 1/2/2010 6:50 AM

I was testing out with connection string to bypass windows authentication that is not enabled on my environment (just passing sql user and password).

Just saw thar you did a new release, getting last version and trying it out.

Happy new year by the away :)
Gravatar # re: RoundhousE: Configuration
by Chris at 1/4/2010 8:48 AM

Been playing with RH, looks good for what we need, thank you!

Is it possible to specify the database file locations in the db restore script (ie the MOVE parameters in the RESTORE COMMAND)? I ask as if you are trying to restore from a backup where the files are stored in a location that you do not have on the target machine, it fails.

Thanks
Gravatar # re: RoundhousE: Configuration
by Robz at 1/4/2010 9:57 AM

@Chris: Yes, that can be added.
http://code.google.com/p/roundhouse/issues/detail?id=12
Check it out and comment.
Comments have been closed on this topic.