Fervent Coder

Versioning your database is as important as versioning your code.  How much time do we waste currently before we are even productive when we have to fix or enhance something in a database?

RoundhousE versions your database how you want. The recommended way is to version based on source control, but that may not be your solution. But for a moment let’s pretend it is.

Why would versioning your database based on source control be a big help? What happens if you get a prod fix (or work order) to fix a view or a stored procedure (sproc)?  How do you know where in source control that is? Do you even know what repository it lives in? How can you even be sure right away that the item you have in source control directly maps to the item in production? Oh right. You can’t. At least not without doing a manual comparison (or with a diff tool).  And if you are not even familiar with the item, how do you get back to it in source control? You ask someone. And then someone else. And so on until you find someone that knows where you can find that view in source control. “Oh….Samsonite! I was WAYYYY off.”

How much time are we using up currently before we are even productive? Probably quite a bit more than we realize. Let’s put it in perspective. You are a new hire today. If I was to point you to a database and tell you to fix a view and update the view in source control, how soon do you think you could be productive? What questions are you going to ask me?

Where is the view in source control? When is this needed by? etc etc

If your company instead used RoundhousE (RH) you could empower the new hire or any developer that suffers from can’t remember stuff (CRS)! Just point them to the database. Let’s take a moment to see what happens as we run the migration.

RoundhousE Versions During Migration

Remember our look at the runner? I want to concentrate in just one area.

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

What exactly is happening here? We are looking into an xml file that contains a version.  RH also asks for the repository the versioning information comes from.  That all gets recorded in the database.

What does that build file look like?

<?xml version="1.0" ?>

<buildInfo>

  <projectName>RoundhousE</projectName>

  <companyName>FerventCoder Software</companyName>

  <versionMajor>0</versionMajor>

  <versionMinor>0</versionMinor>

  <buildNumber>0</buildNumber>

  <revision>25</revision>

  <version>1.0.22.25</version>

  <repositoryPath>http://someotherplace/svn/repositoryname/</repositoryPath>

  <microsoftNetFramework>net-3.5</microsoftNetFramework>

  <msbuildConfiguration>Release</msbuildConfiguration>

  <msbuildPlatform>Any CPU</msbuildPlatform>

  <builtWith>UppercuT v. 0.9.0.216</builtWith>

</buildInfo>

It is the output of an automated build product called UppercuT. Some of you have heard me talk about it before. It records the version in an xml output file to be used by deployment products etc.

Now I have a way to find the query at an exact point in source control history!

What if I don’t use UppercuT? That’s fine, create something in your automated build to create a simple xml file that contains a version. Then pass the file path and xpath for the version info to RH.

Or another way you can get to version is to point to a DLL you have versioned based on source control.

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

RH will automatically pull the file version from the DLL and use it to version the database.

Back To Your Empowerment

So you query the scripts ran table for the view. You notice version_id is 1.

Now you query the version table.

Boom. You have a path to a repository. You also have an exact point in source to find the item.  You can now look revision 67 versus the current revision in source control.

You now know exactly where to look. Two steps to productivity. You get that production fix done in no time and we are so impressed we give you a raise on the spot.

One Repository – Multiple Databases

That’s great, but I hit multiple databases when I deploy. Easy as pie. Do you remember last time how we said to always include the database name before the scripts (see the picture to the right)? For each database, it is just another call to RH to run telling it the specific information about the next database. Super simple.

Multiple Repositories – One Database

What if I have multiple repositories that address the same database? We got you covered. If you have more than one repository versioning a database, they will version the database independently.

Found version 1.0.22.25 from C:\code\roundhouse\code_drop\deployment\_BuildInfo.xml.
Migrating TestRoundhousE from version 0 to 1.0.22.25.
Versioning TestRoundhousE database with version 1.0.22.25 based on http://someotherplace/svn/repositoryname/.

Notice how RH said the prior version was 0. That’s because versioning is based on repository. That repository had never ran against this database before. Now looking in the version table, we see two different repositories with two different versions.

Conclusion

Versioning your database is as important as versioning your code. RoundhousE is a very powerful product for migrating your database (rivaling even some paid alternatives). And it’s free. RoundhousE is just going to keep getting better. So why not give it a try? DBAs approve. It makes auditors smile. I heard it once helped a lady walk again.

I’m a huge fan of NHibernate. It has excellent documentation and just makes the whole job of getting things into and out of the database much more enjoyable.  There is a whole series of posts on NHibernate from one of the committers, Ayende.  When Fluent NHibernate (FNH) came out, it was like butter on sliced bread.  FNH makes it even easier to use NHibernate.

Ayende had a post a while back on how to use Event Listeners.  That helped me to get to event listeners set up.

Event Listeners

namespace somethingimportant.we.hope.infrastructure.app.auditing

{

    using System;

    using System.Security.Principal;

    using System.Web;

    using domain;

    using NHibernate.Event;

    using NHibernate.Persister.Entity;

    public class AuditEventListener : IPreInsertEventListener, IPreUpdateEventListener

    {

        public string get_identity()

        {

            string identity_of_updater = WindowsIdentity.GetCurrent().Name;

        if (HttpContext.Current != null)

            {

                try

                {

                    identity_of_updater = HttpContext.Current.User.Identity.Name;

                }

                catch

                {

                    //move on

                }

            }

            return identity_of_updater;

        }

        //http://ayende.com/Blog/archive/2009/04/29/nhibernate-ipreupdateeventlistener-amp-ipreinserteventlistener.aspx

        public bool OnPreInsert(PreInsertEvent eventItem)

        {

            Auditable audit = eventItem as Auditable;

            if (audit == null)

            {

                return false;

            }

            DateTime? entered_date = DateTime.Now;

            DateTime? modified_date = DateTime.Now;

            string identity_of_updater = get_identity();

            store(eventItem.Persister, eventItem.State, "entered_date", entered_date);

            store(eventItem.Persister, eventItem.State, "modified_date", modified_date);

            store(eventItem.Persister, eventItem.State, "updating_user", identity_of_updater);

            audit.entered_date = entered_date;

            audit.modified_date = modified_date;

            audit.updating_user = identity_of_updater;

            return false;

        }

        public bool OnPreUpdate(PreUpdateEvent eventItem)

        {

            Auditable audit = eventItem as Auditable;

            if (audit == null)

            {

                return false;

            }

            DateTime? modified_date = DateTime.Now;

            string identity_of_updater = get_identity();

            store(eventItem.Persister, eventItem.State, "modified_date", modified_date);

            store(eventItem.Persister, eventItem.State, "updating_user", identity_of_updater);

            audit.modified_date = modified_date;

            audit.updating_user = identity_of_updater;

            //insert auditing object here

            return false;

        }

        public void store(IEntityPersister persister, object[] state, string property_name, object value)

        {

            int index = Array.IndexOf(persister.PropertyNames, property_name);

            if (index == -1)

            {

                return;

            }

            state[index] = value;

        }

    }

}

NHibernate.Cfg.Configuration cfg = new NHibernate.Cfg.Configuration();
cfg.EventListeners.PreUpdateEventListeners = 

    new IPreUpdateEventListener[] {new AuditEventListener()};

cfg.EventListeners.PreInsertEventListeners =

    new IPreInsertEventListener[] { new AuditEventListener() };

_sessionFactory = cfg.BuildSessionFactory();

private void build_factory()

{

    if (nhibernate_session_factory == null)

    {

        nhibernate_session_factory = Fluently.Configure()

            .Database(MsSqlConfiguration.MsSql2005

                .ConnectionString(c => 

                    c.FromConnectionStringWithKey("db")))

            .Mappings(m => 

                m.FluentMappings.AddFromAssemblyOf<VideoMapping>())

            .ExposeConfiguration(cfg => {

                        cfg.EventListeners.PreInsertEventListeners = 

                            new IPreInsertEventListener[] {new AuditEventListener()};

                        cfg.EventListeners.PreUpdateEventListeners = 

                            new IPreUpdateEventListener[] {new AuditEventListener()};

                        })

            .BuildSessionFactory();

    }

}

This same thing can be accomplished with VB.NET by making a call to a method (I’ve done that as well). With it’s discoverability, FNH makes it so easy to figure things out sometimes. :D

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.

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.
upFolderName 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.
 

 
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.

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

I just started the appetites with my last post on RoundhousE. RoundhousE itself has a migration sample to help you get familiar with how it works. You can get to that by using a subversion client to download the source. When you download the RoundhousE source from SVN and run build.bat, you get a code_drop folder. Go in there and look at the deployment folder for a sample run.

Prerequisites: Locally installed (and running) SQL Server 2008

Run That Bad Boy

When you get into the code_drop folder, take a look below at the screenshot. Run LOCAL.DBDeployment.bat. It will run the through a NAnt deploy of RoundhousE and create a database called TestRoundhousE.

When you run LOCAL.DBDeployment.bat, this is the output (provided you have SQL Server 2008 installed):

Running RoundhousE on (local) (TestRoundhousE). Looking in C:\code\roundhouse\code_drop\deployment\scripts\..\..\db\TestRoundhousE for sql scripts.
Executing RoundhousE against contents of C:\code\roundhouse\code_drop\deployment\scripts\..\..\db\TestRoundhousE.
Creating TestRoundhousE database on (local) server if it doesn't exist.
Creating RoundhousE schema if it doesn't exist.
Creating [RoundhousE].[Version] table if it doesn't exist.
Creating [RoundhousE].[ScriptsRun] table if it doesn't exist.
Attempting to resolve assembly file version from C:\code\roundhouse\code_drop\RoundhousE\NAnt\RoundhousE.dll.
Migrating TestRoundhousE from version 0 to 0.0.0.59.
Versioning TestRoundhousE database with version 0.0.0.59 based on http://roundhouse.googlecode.com/svn.
Running 0001_CreateTables.sql on (local) - TestRoundhousE.
Running 0002_ChangeTable.sql on (local) - TestRoundhousE.
Running vw_Dude.sql on (local) - TestRoundhousE.

Take a look here. It gives you feedback about where it is running and where it is looking for sql scripts.  Then it attempts to resolve the version based on a Dll’s version (can also do it through an xml file from the build containing a version).  It will create the database if it doesn’t exist (does not require action create, it’s smart about that).  Then RoundhousE will create it’s tables (one for tracking versions, and one for tracking what scripts have run).  Then it’s going to look through folders recursively and find sql files to run.

What happens when I run it a second time?

Running RoundhousE on (local) (TestRoundhousE). Looking in C:\code\roundhouse\code_drop\deployment\scripts\..\..\db\TestRoundhousE for sql scripts.
Executing RoundhousE against contents of C:\code\roundhouse\code_drop\deployment\scripts\..\..\db\TestRoundhousE.
Creating TestRoundhousE database on (local) server if it doesn't exist.
Creating RoundhousE schema if it doesn't exist.
Creating [RoundhousE].[Version] table if it doesn't exist.
Creating [RoundhousE].[ScriptsRun] table if it doesn't exist.
Attempting to resolve assembly file version from C:\code\roundhouse\code_drop\RoundhousE\NAnt\RoundhousE.dll.
Migrating TestRoundhousE from version 0.0.0.59 to 0.0.0.59.
Versioning TestRoundhousE database with version 0.0.0.59 based on http://roundhouse.googlecode.com/svn.
Skipped 0001_CreateTables.sql either due to being a one time script or finding no changes.
Skipped 0002_ChangeTable.sql either due to being a one time script or finding no changes.
Running vw_Dude.sql on (local) - TestRoundhousE.

The second time it runs it is only going to rerun items that are stateless, items that do not contain data, like functions, views and stored procedures.

FUTURE ENHANCEMENT: RoundhousE will only rerun stateless items if they have changed.  That will cut your migration time way down for databases that are heavy one the stored procedures side.

RoundhousE and SQL Server

What does that goodness give you? Let’s crack open SSMS (Sql Server Management Studio).

You get two tables (schemas and tables names are completely configurable): Version and ScriptsRun.

Looking a the version table, I have the idea of both a source repository and a version. What does that mean? It means RoundhousE knows that you may have multiple repositories that hit the database and that’s cool.

The ScriptsRun table here captures some really interesting information. There is script_name, the id of the version it is associated with, and then there is the text_of_script. Whoa…the text of the actual script I ran?! That’s awesome! But ….why?! We do this for two reasons. You now have an audit of what actually ran. This makes both DBAs and auditors happy. RoundhousE also has a goal in mind of versioning back down. This becomes extremely simple if we keep track of those things.

What happens if I start running bigger and bigger scripts? It’s a minimal impact. I tested this with a 15MB production level insert script for running an initial load of data and it ran like a champ. My database also only grew about 4MBish larger than the original database without recording the script. So the impact is minimal.

The next column we’ll focus on is one_time_script. This column is what tells you automatically whether this script is located in a one time run folder (the up folder in this case) or if it is a stateless item that will not cause data loss or error to run again and again.

Okay next column: text_hash – what is that? That is how RoundhousE can very quickly determine if there have been changes to the script. Which is a good transition into the next section.

RoundhousE is Smarter

What happens if someone changes a DDL/DML file that is meant to run only once? Add even one extra space in the file and this is what you get:

RoundhousE encountered an error:
System.Exception: 0001_CreateTables.sql has changed since the last time it was run. By default this is not allowed - scripts that run once should never change. To change this behavior to a warning, please set warnOnOneTimeScriptChanges to true and run again. Stopping execution.

It is configurable to set this to a warning, but the important thing to note is that RoundhousE is going to try to help your developers see that they made changes to something that won’t run. That way they are not making changes to files that should not change.

Go get the source and give it a whirl.

Next up – RoundhousE Configuration. Same bat time. Same bat channel.  And remember, “There is no charge for awesome.”

Right now my post on Sproc Executing Slow is the most popular topic on GWB!

One of the coolest projects I have been working on is how to solve issues with database versioning and migration. A project dubbed RoundhousE.

RoundhousE is an open source automated database deployment (change management) system that allows you to use your current idioms and gain much more. Currently it only supports Microsoft SQL Server, but there are future plans for other databases.

It seeks to solve both maintenance concerns and ease of deployment. We follow some of the same idioms as other database management systems (SQL scripts), but we are different in that we think about future maintenance concerns. We want to always apply certain scripts (anything stateless like functions, views, stored procedures, and permissions), so we don’t have to throw everything into our change scripts. This seeks to solves future source control concerns. How sweet is it when you can version the database according to your current source control version?

RoundhousE (RH) is a very lean implementation for database migration that uses SQL Scripts to apply changes to a database. It is extremely configurable in that you can override the conventions for everything, including the schema, version table, and scripts run table for RH. RoundhousE is implemented as an MSBuild/NAnt Task, a console application (the console is still in development), and in the future possibly a GUI as well. There is an extensive roadmap on the product. Over the next few weeks I will be talking about how to set up and use this product and future enhancements.

Until then, enjoy this image of Chuck showing off his intimate knowledge of open source!

Next in the spin: RoundhousE DB Migration: Action Shots

Iowa Code Camp was a great time! I really enjoyed the conference itself. It was probably one of the best conferences I have ever been to. There are a lot of talented people surrounding Iowa and I had the pleasure of hanging out with some very intelligent people that are doing some really awesome things.

The talk went well as well. Hopefully there were a few more people that got out there and tried UppercuT. Here are the slides from my talk.

So you have stored procedure and it’s having issues. When you run it, it returns really fast. When .NET runs it, its like a dog.

The first thing you learn about SQL Server is that it has something called ARITHABORT and it is turned on in SQL Management Studio and OFF in ADO.NET. Yeah you heard that right. OFF. So when you find this out, your first thought is WTF, mate?! Why do we have to make this hard on ourselves?

Reference post: http://sqladvice.com/blogs/gstark/archive/2008/02/12/Arithabort-Option-Effects-Stored-Procedure-Performance.aspx

So you turn it off in SQL Server Management Studio under {Tools}->{Options} and all is good right?

Well, you might be coming across another problem and you want to optimize the sproc so you pull the text out and start running query analyzer. First things first, compare the text of the sproc versus executing the actual sproc. You might be surprised to find that the actual execution is not as fast as the text.

WTF? Why is the sproc running slower than the actual text? This is due to a bad query plan being cached by SQL. If you use the parameters that are passed in by the sproc, SQL Server tries to guess what those are and it puts in fake values. Then it caches a bad query plan.

So if you’re still with me at this point, you’re probably saying, that’s all fine and dandy Rob, but I just want to fix the freaking thing and you are giving me too much context.

The fix is so easy, it’s crazy. All you have to do is set those parameters to local variables and use the local variables in the sproc.

Create PROC Tim 

    @StartDate DateTime

AS 

 BEGIN

    DECLARE @LocalStartDate DateTime

    SET @LocalStartDate = @StartDate

    /* your code here */

    SELECT @LocalStartDate AS StartDate

    /* instead of 

        SELECT @StartDate AS StartDate

     */

 END

It’s a hack. And only useful when the sproc is being a non-performant nancy boy.

And here’s another good reason not to use Sprocs. I’m just saying. YMMV.

If you are in Des Moines, IA, tomorrow for Iowa Code Camp, I’m speaking on “Automated Builds: How to UppercuT your Code” at 3:45.

If you are going to be there and we’ve only met on Twitter before, I’m looking forward to meeting you!

If you are using UppercuT, you will be pleased to know that it now supports Mono Migration Analyzer (MoMA for short).  All you have to do is upgrade. And with the design of UppercuT, we’ve made it super simple to upgrade.

How hard is it to upgrade UppercuT? Just drop in the files in your build folder, check for changes to the lib folder (especially in the NAnt directory) and new folders, and then check for any changes to the .bat files and UppercuT.config and you’re done.

Mono Migration Analyzer

Here is what the report looks like running against UppercuT.  It looks like UppercuT is okay to run on *nix, but a few of it’s dependencies may have some issues.

If you look closer, you can find whether the method is not implemented, on the TODO list, or the P/Invoke list. The P/Invoke’s will only work if your OS has implemented them.

Yet another reason to use UppercuT. The builds just keep getting better!

Here’s how you can sign a set of assemblies in a project with a key using UppercuT:

1. Open the UppercuT.config file.

2. Change the following to “true”:

3. Done.

Did I mention that creates a private key if it is not there as well?!

This was from a patch sent in by Dru Sellers. Thanks Dru!

With this knowledge you shall build.

The presentation for TopDNUG went pretty well. There were some good questions and back and forth. The room really seemed to come alive when in a matter of less than ten minutes I added UppercuT to two different Open Source projects (Reflexil and Quartz.NET) and had them off of the ground and building. Full builds, too. With versioning, compiling, unit testing, packaging, etc.

I also unveiled the new logo for UppercuT:

Here is the slide deck: UppercuT Presentation (may need to be renamed to .pptx).

Recently I reported that there were going to be some changes to UppercuT. And there have been. These are a summary of some of the most significant changes:

• Uppercut now reports it's version. This is helpful to know where you are versus the current version. It also reports the time when it finishes a build.
• Custom Replacement Tasks are now implemented. This is to add a task to the custom folder that completely replaces the normal workings of the build step it is replacing. A pre or post custom step will still run though.
• Pre, Post and Replacement tasks are implemented for every build step in UppercuT now.
• Support for Gallio Testing has been added.
• EnvironmentBuilder works better than ever now that it is custom code instead of NAnt property expansion which had buffer limitations.
• All .build files are now .step except for the actual builds (open, zip and default). default.build is what used to be __master.build.

http://code.google.com/p/uppercut/downloads/list

Most of this is due to prioritizing the time to get requested features into UppercuT prior to my upcoming presentation for TopDNUG.

There are some more changes on the way to v1. I will be talking about a road map soon.  If you aren’t yet a member of the uppercut users group, you might consider joining. :-)

With this knowledge you shall build.

So Gallio has been out for a little while and I admit that I am a little slow when it comes to looking at new frameworks. I mean there is so much to look at and only so much time in the day allocated to programming. Anyway, there really isn’t much documentation out there yet for using Gallio with NAnt. I am of the thought that is due to all of the people who are really smart with builds are using Rake and/or PSake now. So I set off to create another universal script. This basically follows the conventions from my post with MbUnit’s Universal script and how I added MbUnit2 category filters to it’s NAnt task in UppercuT.

So like I said, Gallio has been out for a little while. This is the first time I have heard that it has underwent some optimizations so now seems a good time to check it out. Until I figure out how to do it another way, this script requires Gallio to be installed on each machine that is going to use it. I am currently trying to figure out how I can get a reduced set of Gallio into source control. The install is 26.4MB right now and that’s bigger than I want my repositories to have to be just for adding a testing framework.

NAnt Script for Gallio

<?xml version="1.0" encoding="utf-8" ?>

<project name="GallioTestRunner" default="go">

  <!-- Project UppercuT - http://projectuppercut.org -->

  <!-- DO NOT EDIT THIS FILE - This follows a convention for testing with Integration tests being separated from Unit tests - find out more at http://uppercut.pbwiki.com -->

  <property name="build.config.settings" value="__NONE__" overwrite="false" />

  <include buildfile="${build.config.settings}" if="${file::exists(build.config.settings)}" />

  <property name="dirs.build" value="${directory::get-parent-directory(project::get-buildfile-path())}\..\..\build_output" />

  <property name="dirs.build_artifacts" value="${dirs.build}\build_artifacts" overwrite="false" />

  <property name="dirs.test_results" value="${dirs.build_artifacts}\gallio" overwrite="false" />

  <property name="file.test_results" value="gallio-results" overwrite="false" />

  <property name="time.limit.in.seconds" value="240" />  <!-- 4 minutes -->

  <target name="go" depends="cleanup, run_tests" description="Tests" />

  <target name="cleanup">

    <echo message="Removing and adding ${dirs.test_results}."/>

    <delete dir="${dirs.test_results}" failonerror="false" />

    <mkdir dir="${dirs.test_results}" />

  </target>

  <target name="load_tasks">

    <echo message="Loading Gallio Nant Tasks from Program Files." />

    <loadtasks assembly="C:\Program Files (x86)\Gallio\bin\Gallio.NAntTasks.dll" if="${file::exists('C:\Program Files (x86)\Gallio\bin\Gallio.NAntTasks.dll')}" />

    <loadtasks assembly="C:\Program Files\Gallio\bin\Gallio.NAntTasks.dll" if="${file::exists('C:\Program Files\Gallio\bin\Gallio.NAntTasks.dll')}" />

  </target>

  <target name="run_tests" depends="cleanup,load_tasks" description="Running Unit Tests">

    <echo message="Running tests using Gallio and putting results in ${dirs.test_results}."/>

    <gallio working-directory="${dirs.build}"

            report-types="Html;Xml;Text"

            report-directory="${dirs.test_results}"

            report-name-format="${file.test_results}"

            show-reports="false"

            failonerror="true"

            verbosity="Normal"

            echo-results="true"

            run-time-limit="${time.limit.in.seconds}"

            filter="exclude Category:Database or Category:Integration or Category:Slow or Category:NotWorking or Categroy:Ignore or Category:database or Category:integration or Category:slow or Category:notworking or Categroy:ignore"

            >

      <files>

        <exclude name="${dirs.build}\*Database*dll" />

        <exclude name="${dirs.build}\*.Integration*dll" />

        <exclude name="${dirs.build}\TestFu.dll" />

        <include name="${dirs.build}\*Test*dll" />

        <include name="${dirs.build}\*.Specs*dll" />

      </files>

    </gallio>

  </target>

  <target name="run_all_tests" depends="cleanup,load_tasks" description="Running All Unit Tests">

    <echo message="Running all tests (including integration tests) using Gallio and putting results in ${dirs.test_results}."/>

    <gallio working-directory="${dirs.build}"

            report-types="Html;Xml;Text"

            report-directory="${dirs.test_results}"

            report-name-format="${file.test_results}"

            show-reports="false"

            failonerror="true"

            verbosity="Normal"

            echo-results="true"

            run-time-limit="${time.limit.in.seconds}"

            >

      <files>

        <exclude name="${dirs.build}\TestFu.dll" />

        <include name="${dirs.build}\*Test*dll" />

        <include name="${dirs.build}\*.Specs*dll" />

      </files>

    </gallio>

  </target>

  <target name="open_results">

    <echo message="Opening results at ${path::get-full-path(dirs.test_results)}\${file.test_results}.html."/>

    <exec

      spawn="true"

      program="${environment::get-folder-path('ProgramFiles')}\Internet Explorer\iexplore.exe"

      commandline="${path::get-full-path(dirs.test_results)}\${file.test_results}.html"

      >

    </exec>

  </target>

</project>

UppercuT and Gallio

UppercuT now has support for Gallio baked in. What that means is that you select gallio as your test framework in the config file. Install Gallio. And you’re done. And when you run test from the command line, you get this in your browser:

What pretty reports you get from Gallio!

With this knowledge, you shall build.

I’m slimming it down and rethinking some of the idioms it is currently using. Stay tuned…

Due to scheduling conflicts, the meeting has been moved to Thursday September 24, 2009.

http://groups.google.com/group/topekadotnet/browse_thread/thread/f9563f846a50f3d7#

I updated the original post as well.

This being the second time in two months comes with a little explanation. The business where we hold our meetings graciously lets us use the meeting space for free. As such sometimes they ask that we reschedule to meet their needs.

Topeka DNUG is free for anyone to attend! Mark your calendars now!

SPEAKER: Rob Reynolds has been programming in .NET since the early days of 1.0. He is a .NET Developer at FHLBank Topeka, a bank where the doors are always locked and there’s no money inside. He holds a bachelor’s degree in MIS from Kansas State University (don’t hate!) and enjoys spending time with his wife and kid when his wife hasn’t locked him in the basement to work on any of the OSS projects he manages.

TOPIC: Automated Builds: How to UppercuT Your Code!

“Build – it’s not just for F5 anymore.”

How you build your code and verify quality is something that is usually not thought of at the beginning of a project, but is one of the most important things you can add to code! During this session Rob will go over the conventions in building and verifying code quality. You will see a project that is using automated builds and how all of the conventions are applied. We are going to see UppercuT and how well suited it is for automated builds. UppercuT is a build framework (based in NAnt) that allows rapid and powerful use of NAnt without having to understand the intricacies of NAnt. The last thing we will do is apply UppercuT to a project to show you how fast you can go from F5 to automated builds!

WHERE: Federal Home Loan Bank Topeka on the Security Benefit Campus – Directions?

WHEN: 11:30 AM - 1:00 PM on September 24, 2009

REGISTER: http://topekadotnet.wufoo.com/forms/topeka-dnug-meeting-attendance/

ADDITIONAL INFO: As always, please sign in and out of FHLBank to help them with their accountability. Please park in the visitors section at the front of the building when you arrive. If  there are no spots in visitors you may park in the overflow lot at the far east end of the facility.  Lunch will be provided and we will have some great door prizes!

If you’ve ever built a .NET application that runs out of memory constantly, it’s because you are hitting a 2GB limit. You may have known about marking an assembly “/largeaddressaware”. You may have not. The process of doing this is actually somewhat easy once you learn about it. You normally just start a Visual Studio Command Prompt (found in Start Menu under Microsoft Visual Studio version/Visual Studio Tools). Then you find the compiled application and run the following command:

editbin /largeaddressaware yourassembly.exe

That’s really all you need to do get more memory out of your application. There are some great resources on how and why in the two blog posts below.

• http://bilbroblog.com/wow64/hidden-secrets-of-w0w64-ndash-large-address-space/
• http://stevenharman.net/blog/archive/2008/04/29/hacking-visual-studio-to-use-more-than-2gigabytes-of-memory.aspx

I would instead like to concentrate on automation with UppercuT. 

Getting UppercuT to Automatically Mark an Assembly for More Than 2GB of Memory

We are going to make a custom AFTER task for the compile step of UppercuT. With that we are going to specify the executable to mark. When the application is built (on the build server), it is marked for address large amounts of freakin memory. *grin*

1. If you do not have a BuildScripts.Custom folder, create one.

2. In that folder we need to create a file named “_compile.post.build.”

3. Open the file in a text editor and insert the code below.

Contents of _compile.post.build (this simulates the VS2005 command prompt and calls editbin for you)

<?xml version="1.0" encoding="utf-8" ?>
<project name="Compiler" default="go">
  <property name="build.config.settings" value="__NONE__" overwrite="false" />
  <include buildfile="${build.config.settings}" if="${file::exists(build.config.settings)}" />
  <property name="dirs.current" value="${directory::get-parent-directory(project::get-buildfile-path())}" />
  <property name="dirs.build" value="${dirs.current}\..\build_output" />
  <property name="exe.name" value="__INSERT_NAME_HERE__" overwrite="false" />
  <property name="program.largeaware" value="${environment::get-folder-path('ProgramFiles')}\Microsoft Visual Studio 8\VC\bin\editbin.exe" />
  <property name="args.largeaware" value="/LARGEADDRESSAWARE ${dirs.build}\${exe.name}" />
  <property name="environment.properties.largeaware" value="${environment::get-folder-path('ProgramFiles')}\Microsoft Visual Studio 8\VC\vcvarsall.bat x86" />

  <target name="go" depends="set_large_aware" description="Compiling project." />

  <target name="set_large_aware" depends="" description="Building Library">
    <echo message="Setting the application ${dirs.build}\${app.name} to large aware."/>
    <exec program="${program.largeaware}">
      <environment>
        <variable name="VSINSTALLDIR" value="${environment::get-folder-path('ProgramFiles')}\Microsoft Visual Studio 8" />
        <variable name="VCINSTALLDIR" value="${environment::get-folder-path('ProgramFiles')}\Microsoft Visual Studio 8\VC" />
        <variable name="FrameworkDir" value="${environment::get-folder-path('System')}\..\Microsoft.NET\Framework" />
        <variable name="FrameworkVersion" value="v2.0.50727" />
        <variable name="FrameworkSDKDir" value="${environment::get-folder-path('ProgramFiles')}\Microsoft Visual Studio 8\SDK\v2.0" />
        <variable name="DevEnvDir" value="${environment::get-folder-path('ProgramFiles')}\Microsoft Visual Studio 8\Common7\IDE" />
        <variable name="PATH" path="${environment::get-folder-path('ProgramFiles')}\Microsoft Visual Studio 8\Common7\IDE;${environment::get-folder-path('ProgramFiles')}\Microsoft Visual Studio 8\VC\BIN;${environment::get-folder-path('ProgramFiles')}\Microsoft Visual Studio 8\Common7\Tools;${environment::get-folder-path('ProgramFiles')}\Microsoft Visual Studio 8\SDK\v2.0\bin;${environment::get-folder-path('System')}\..\Microsoft.NET\Framework\v2.0.50727;${environment::get-folder-path('ProgramFiles')}\Microsoft Visual Studio 8\VC\VCPackages;%PATH%" />
        <variable name="INCLUDE" path="${environment::get-folder-path('ProgramFiles')}\Microsoft Visual Studio 8\VC\INCLUDE;${environment::get-folder-path('ProgramFiles')}\Microsoft Visual Studio 8\SDK\v2.0\include;%INCLUDE%" />
        <variable name="LIB" path="${environment::get-folder-path('ProgramFiles')}\Microsoft Visual Studio 8\VC\LIB;${environment::get-folder-path('ProgramFiles')}\Microsoft Visual Studio 8\SDK\v2.0\lib;%LIB%" />
        <variable name="LIBPATH" path="${environment::get-folder-path('System')}\..\Microsoft.NET\Framework\v2.0.50727" />
      </environment>
      <arg line="${args.largeaware}" />
    </exec>
  </target>

<!--
All of the variables from where they are actually set - C:\Program Files\Microsoft Visual Studio 8\Common7\Tools\vsvars32.bat
-->

</project>

4. Change the property exe.name value to insert the name of the executable. UppercuT already knows where the file is going to get built.

5. Add that file to source control.

6. Run a local build to be sure everything is good and then check that puppy in!

7. Crack open a beer *ahem* soda and sip in the sweet taste of success.

That’s it! Once that stuff is in source control, your automated build will take care of the rest! You can now officially be lazy about this setting from now on.

Check out some of the other UppercuT posts! UppercuT category

With this knowledge you shall build.

I’m a huge fan of StExBar. I posted about it awhile back and have since found more reasons to think this is a must have tool! It’s got an ability to give you great shortcuts at your keyboard finger tips. How often have you been like “I need a command window here” and then went through a bunch of trouble to get it there? How about {Control} + {M}? BAM! Command Window opened and pointed to that directory.

I’ve been starting to use PowerShell more and I thought…hmmm – I could add PowerShell to this and type something like {Control}+{O} and have it pop up in the same way I get a command line.

Add PowerShell Hotkey to StExBar

1. Open StExBar Settings and then click {Add}.

2. Enter “Powershell” in the [Name:] box. In [Command line:] enter the path to PowerShell. Mine was “C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe”. In [Hotkey:] enter {Ctrl}+{O} or whatever combination you want to use. Click {OK}.

3. Now hit {Ctrl}+{O} or whatever hotkey you used. BAM! It opens up pointed to the current directory.

Check out what it looks like on the command bar.

That’s awesome!

<rant>

Recently a friend of mine wrote a post about having his comments deleted from a post. It has caused a bit of a controversy because both of them are MVPs.

http://flux88.com/blog/a-deleted-response-to-a-tfs-blog-post/

Go ahead and read it, I’ll wait here. Seriously.

Some people have said it was a bad thing that he wrote the post. I personally think he did a very good thing. At times it is very important to hold people accountable for something you believe in.  While it’s not always the popular stance, it’s great to see people do the ethical thing no matter what the expense (even though we are only talking losing followers, readers, web presence, etc. in this case – it’s still very commendable).

</rant>

I wanted to keep link love – but blog wouldn’t let me keep the link with the .NET in it when I edited it. The link is now here: http://ferventcoder.com/archive/2009/07/19/net-binding-redirects-ndash-updating-referenced-assemblies-without-recompiling-code.aspx

Due to scheduling conflicts, the meeting has been moved to Thursday August 20th.  http://groups.google.com/group/topekadotnet/browse_thread/thread/d8ed379f3b94858

Topeka DNUG is free for anyone to attend! Mark your calendars now!

Speaker

 Dru Sellers is the Solution Architect for Federal Home Loan Bank in Topeka, KS. He has been programming professionally for over 8 years and spends most of his time in C# and VB.Net, Castle, and junk punches people who 'touch' his database.

Topic

Object Oriented Databases and other non-relational options

Are you tired of writing SQL to maintain your databases? Are you using an object relational mapper and sick of the mapping? If you are then Dru would like to introduce you to the world of ‘NO MORE SQL’. It's an amazing world where objects are saved magically and still queryable.

In this talk Dru will be reviewing 'db4o' a free object database as well as a way to use the open source search engine 'Lucene.Net' as an object store as well. Dru will also discuss how some large corporations are switching away from traditional Relational databases to achieve unheard of performance.

WHERE: Federal Home Loan Bank Topeka on the Security Benefit Campus – Directions?

WHEN: 11:30 AM - 1:00 PM on August 20th, 2009

REGISTER: http://topekadotnet.wufoo.com/forms/topeka-dnug-meeting-attendance/

ADDITIONAL INFO: As always, please sign in and out of FHLBank to help them with their accountability. Please park in the visitors section at the front of the building when you arrive. If  there are no spots in visitors you may park in the overflow lot at the far east end of the facility.  Lunch will be provided and we will have some great door prizes!

UPDATE: Due to scheduling conflicts, this was changed from August 18th to August 20th - http://ferventcoder.com/archive/2009/08/01/topeka-.net-user-group-meeting-ndash-moved-to-august-20th.aspx

Have you ever seen this error?

System.IO.FileLoadException: Could not load file or assembly ‘nameOfAssembly’, Version=specificVersion, Culture=neutral, PublicKeyToken=publicKey’ or one of it's dependencies. The located assembly’s manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)

This means you’ve replaced the specific version of the third party assembly with either an earlier or an updated version. The assembly that uses it is compiled to point to a specific version of the assembly and now will not load. I’ve seen this the most with applications that use log4net and/or NHibernate and third party assemblies that also use log4net and/or NHibernate.

Sometimes you just can’t recompile code, but you want to update the version the assembly uses. Sometimes it’s troublesome (or even impossible) to go back to all of the third party assemblies and get their source and recompile everything to use the same version. This may be the biggest pain some people see in trying to use and upgrade OSS. But if you know what’s going on, it’s very easy to make everything work happily together without a lot of work.

I’ve fought for hours trying to figure out and correct this error. I’ve found that there is an easier alternative and I wanted to share so that others could see it is not that hard to deal with. It’s possible to make code look for an updated assembly (or an earlier version) by adding some elements to the config file.

This is done through a binding redirect.  In the configuration file for the application or DLL that uses the assembly, you include something like this:

<runtime>
  <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
   <dependentAssembly>
      <assemblyIdentity name="NHibernate"
                        publicKeyToken="aa95f207798dfdb4"
                        culture="neutral" />
        <bindingRedirect oldVersion="0.0.0.0-2.0.0.4000" newVersion="2.0.0.4000" />
    </dependentAssembly>
  </assemblyBinding>
</runtime>

You are instructing the application or DLL that during runtime, for a particular dependent assembly to use a particular version when an application and/or other assembly is looking for the older version.

You can see here that I pointed the application from all versions of NHibernate 0.0.0.0-2.0.0.4000 to use 2.0.0.4000, even though the application was only looking for 2.0.0.1001. This helps other assemblies in the same AppDomain (normally all of the assemblies within your application’s executing code) also update whatever versions they are tied to to also use the same version.

The error in the above picture occurred in Castle.Facilities.NHibernateIntegration.dll, but I didn’t add a Castle.Facilities.NhibernateIntegration.dll.config file. It wasn’t the entry point for my application. I have another asssembly, let’s call it Foo.exe, that references both Castle.Facilities.NHibernateIntegration.dll and NHibernate.dll. Foo.exe itself is actually using an updated reference to NHibernate version 2.0.0.4000. The NHibernateIntegration was compiled against NHibernate version 2.0.0.1001. I need to add a Foo.exe.confg file and add the code above to it. That way when Castle goes looking for any version of NHibernate between 0.0.0.0 and 2.0.0.4000, the AppDomain instructs it to use version 2.0.0.4000. If another assembly was looking for another version of NHibernate, it would also be instructed to use 2.0.0.4000.

Keep in mind this is not recommended for use when there are breaking changes between two versions of a dependent assembly (due to errors or inconsistent behaviors). Hopefully this will help you if you ever run into this issue.

From what I have seen and used, log4net is one of the best tools out there for implementing logging (the best?)!

That said, we noticed something recently that is very interesting. Let’s say you have a service. You keep it running all of the time. You have implemented logging for it. You keep those logs on a separate server for whatever reason. For purposes of discussion the server the service is on is the “app server” and the server the logs are on is the “log server.”

When you reboot that log server, you might expect that when it comes back up, the logs will continue. For whatever reason, this is not the case. You have to restart the service to get the logs running again.

For this reason you may want to consider ALWAYS putting your logs with your application. If you decide otherwise, at least understand that this could be an issue. Otherwise you may be perplexed at why your service is running and sending email, but isn’t logging anymore.