• Engage the customer and build excitement in the product
• Help drive the estimation process and determine the system's complexity
• Map fields from the GUI to the repository/database
• Enforce standards across the application including layout and validation errors
• Ensure that all text including error messages are created and spell/grammar checked by the business analysis team - It should be possible to copy text off the diagram

• Be customised for different audiences (same deck of cards but with sheets removed for different scenarios)
  • Senior Management - Given cards that show the end system's look and feel
  • Application Champion - Given cards that show dynamic behaviour - Before/After effects of a button press or validation error on a field entry
    
  • Data Analysts - Given cards/populate the mappings between user interface fields and the underlying data model
  • Programmers - Given all cards
• Include version numbers on each sheet
• Provide traceability between the screen and the original requirement/user story

Unfortunately I no longer have access to the fantastic IA template that provided all of the points listed above.  So instead in the space of a couple of hours I created a Visio template for doing simple Site Maps and Wireframes in Visio. 

The following shows a site map example:   

And an example of a page is as follows:

As you can see it's very basic but I think it's a good starting point.  The stencil includes

• Standard background for all diagrams
  • Sheet Id
  • Requirements Traceability
    
  • Version number
    
  • Boxes for descriptive text about the Site Map/Wireframe
• A couple of useful shapes
  • Distance marker
  • Page fold

Visio 2007 - AI Template v 0.01.zip (131.63 kb)

The most fundamental tool for any team doing agile development is Continuous Integration. Unfortunately while Unit testing frameworks such as Nunit and friends and CI servers such as Cruise Control and Teamcity seem to have universal adoption, the tools for managing the build process of the database is nowhere near advanced.

This walk through (I have made the mistake of implementing each of the Antipatterns) describes how I have successfully implemented a build/versioning process for the database(s) on a number of projects over the last 3 years. At the core is a simple version table used to store changes that have been applied to the database. A build task iterates over a directory of sequentially numbered scripts and applies all the scripts that have not already been applied to the database and updates the version number.

The code is open source (BSD license) and is implemented as Nant/MSBuild tasks so that it can be plugged into your current build process. There is also an Installer class so that you can embed the versioning process into an MSI. Currently the code works with SQL Server and Postgress. Feel free to send me an email for future enhancements or patches to enable integration with any DBMS such as MySQL and Oracle. The code will be made available on a future posting...

Database Deployment Patterns

The following is a list of development processes that I have built up over the last couple of years.

Anti-Pattern: Shared development database

Developers should NEVER connect to a shared database. Full stop end of story. Each developer and build machine should have its own database.

Sharing a database is fraught with problems:

• Any schema or data change effects EVERYONE

• It is impossible to write integration tests that need to commit transactions. By committing the data it becomes visible to the other database users. For example any tool or library that manages transactions such as SQL Server Integration Services will commit during its processing.

• Eventually the database gets cloned anyway for Performance testing, big schema changes etc. So you may as well have individual databases from day one.

Anti-Pattern: “Big Bang” database upgrade script

Database modifications are by their very nature unidirectional. Once a script has deleted all the rows out of a database the only way back is a database restore.

Hence as soon as a database enters production it is imperative that all database changes are managed through a rigorously tested deployment process. The ONLY reliable method is to regularly (weekly/fortnightly?) restore a copy of production in a test environment and simulate the deployment process that is going to take place in production. Tools such as Redgate can be used to do a database compare between the production and development databases. The differences between environments are then bundled up into an update script that will be run during “Go Live”.

Once the database has been upgraded the testing begins. Unit tests are run to ensure that the business rules they validate are still correct. Testers hammer the front end racing through their regression test scripts to verifying and validating up until the green light is given.

The problems arise when environment explosion takes place. As well as development and production there may be training, integration test, system test, user acceptance test and production hot fix environments. Lets assume that data in each database is different (at the very least Authorisation and Authentication data is different – Testers are “Gods” in test but not prod). The release cycle will also be different with many releases into the lower test environments and less regular releases into production. This state of affairs quickly leads to the one large upgrade script being broken into smaller logical chunks.  Where the scripts granularity maps to the needs of the environment that gets deployed into the most regularly.

Anti-Pattern: Scripts updates many databases

The “use <database>” statement should NEVER exist in an update script. Scripts should be database agnostic.  They should only change and modify the data for a single database. This has many benefits:

• Easy to have single database server running different test environments.

• Easy to manage in source control place each databases scripts in a different folder

Pattern: Database has a version number/Scripts are versioned

If we have a folder of 20 database scripts, how can we determine which have and which have not been executed against the database? For schema changes we can see if a column exists or doesn't but it is impossible to tell if an update or delete statement has been run. For this reason tagging the database with a version number is a far simpler solution.

Table: DatabaseScriptRunner

• Version: int

• AppliedOn: Date Time

Pattern: Version key includes Release Id

When I first started using versioning tables I was repeatedly burnt when production systems needed to be hot fixed. For example assume that the production database was at version 50 and after a couple of weeks of development the trunk had reached version 60. In order to patch production we needed to create a new script 51 for the production branch.  At this point there were now 2 version 51 scripts, in different branches each with different content.  To get the releases aligned the release 51 script would need to be prepended to the  trunk script.  It started getting very messy once production needed a second and third emergency patch.  The trunk 51 script would get longer and longer.

A simpler approach is to store each release's scripts in a different folder and have the first script version number set to 0.

Table: DatabaseScriptRunner

• Release: int

• Version: int

• AppliedOn: Date Time

Pattern: Version table includes Assembly version numbers

Depending upon your build environment it can be extremely useful to record the version of the components that have been built against the database. This can be used during debugging to ensure that the same version of the code is being used as the one that the database is expecting.

Table: DatabaseScriptRunner

• Release: int

• Version: int

• RepositoryAssemblyVersion: string

• AppliedOn: Date Time

Team Specific: Don't version stored procedures

If you work in an organisation with a low number of stored procedures (for example you may use an ORM for instance) then the script changes should be migrated through the database continuous process. On the other hand, if the team is using stored procedures as the solution's data access layer then it can be very important for each script to be easily tracked in the source control repository.  For this reason it is possible to use the build process for all schema and data changes and have all the stored procedures placed into a seperate unversioned folder where all the scripts are rerun each time the build is executed.

The Temporary SQL Express database is used to create a database that exists only within the confines of a unit test or test fixture.  I have used the class to test deployment issues with database scripts or when testing complex integration scenarios that can't be placed into a database transaction such as those within SQL Server Integration Services.  

It can be used as follows.  

   1: [Test]

   2: public void TemporaryDatabaseExample()

   3: {

   4:using (var tempDb = new TemporarySqlExpressDatabase())

   5:     {

   6:using (var con = new SqlConnection(tempDb.ConnectionString))

   7:         {

   8:             con.Open();

   9: using (var cmd = new SqlCommand("CREATE TABLE foo (id int)", con))

  10:             {

  11:                 cmd.CommandType = CommandType.Text;

  12:                 cmd.ExecuteNonQuery();

  13:             }

  14:  

  15: using (var cmd = new SqlCommand("INSERT INTO foo (id) VALUES (1)", con))

  16:             {

  17:                 cmd.CommandType = CommandType.Text;

  18:                 cmd.ExecuteNonQuery();

  19:             }

  20:  

  21: using (var cmd = new SqlCommand("SELECT id FROM foo", con))

  22:             {

  23:                 cmd.CommandType = CommandType.Text;

  24:                 var result = (int)cmd.ExecuteScalar();

  25:                 Assert.AreEqual(1, result);

  26:             }

  27:         }

  28:     }

  29: }

Under the covers the class uses a TemporaryDirectory which ensures that a data files are deleted after the object is destroyed. 

   1: using System;

   2: using System.Data.SqlClient;

   3: using System.IO;

   4:  

   5: namespace PebbleSteps.TestingTools

   6: {

   7:public class TemporarySqlExpressDatabase : IDisposable

   8:     {

   9:private const string SqlExpressConnectionString

  10:             = "Data Source=.\\sqlexpress;Initial Catalog=tempdb;Integrated Security=true;User Instance=True;";

  11:  

  12:private readonly string _temporaryDatabaseFileName;

  13:private readonly TemporaryDirectory _temporaryDirectory;

  14:private bool _disposed;

  15:  

  16:public TemporarySqlExpressDatabase()

  17:         {

  18:             _temporaryDirectory = new TemporaryDirectory();

  19:             DatabaseName = "temp" + Guid.NewGuid().ToString("N");

  20:             _temporaryDatabaseFileName = Path.Combine(_temporaryDirectory.FullName, DatabaseName + ".mdf");

  21:  

  22: using (var connection = new SqlConnection(SqlExpressConnectionString))

  23:             {

  24:                 connection.Open();

  25:  

  26: using (SqlCommand command = connection.CreateCommand())

  27:                 {

  28:                     command.CommandText =

  29: string.Format("CREATE DATABASE {0} ON PRIMARY (NAME={0}, FILENAME='{1}', SIZE = 3000KB) LOG ON ( NAME = N'{0}_Log', FILENAME = N'{1}_Log.ldf', SIZE = 512KB)", DatabaseName, _temporaryDatabaseFileName);

  30:                     command.ExecuteNonQuery();

  31:  

  32:                     command.CommandText = "EXEC sp_detach_db '" + DatabaseName + "', 'true'";

  33:                     command.ExecuteNonQuery();

  34:                 }

  35:             }

  36:         }

  37:  

  38:public string ConnectionString

  39:         {

  40:             get

  41:             {

  42: return

  43: string.Format(@"Server=.\SQLExpress;AttachDbFilename={0};Database={1}; Trusted_Connection=Yes;",

  44:                                   _temporaryDatabaseFileName, DatabaseName);

  45:             }

  46:         }

  47:  

  48:public string DatabaseName { get; private set; }

  49:  

  50:#region IDisposable Members

  51:  

  52:public void Dispose()

  53:         {

  54:             Dispose(true);

  55:             GC.SuppressFinalize(this);

  56:         }

  57:  

  58:#endregion

  59:  

  60:private static void ClearConnectionsToTemporaryDatabase()

  61:         {

  62:             GC.Collect();

  63:             SqlConnection.ClearAllPools();

  64:         }

  65:  

  66:private void Dispose(bool disposing)

  67:         {

  68: if (!_disposed)

  69:             {

  70: // Cleanup Managed resources

  71:                 ClearConnectionsToTemporaryDatabase();

  72:                 _temporaryDirectory.Dispose();

  73:  

  74: if(disposing)

  75:                 {

  76: //Clean unmanaged resources

  77:                 }

  78:  

  79:                 _disposed = true;

  80:             }

  81:         }

  82:  

  83:         ~TemporarySqlExpressDatabase()

  84:         {

  85:             Dispose(false);

  86:         }

  87:     }

  88: }

The TemporaryDirectory class has been updated so that it sleeps for up to 3 seconds while it waits until any running processes to close open file handles on the folder.

   1: using System;

   2: using System.IO;

   3: using System.Security.AccessControl;

   4: using System.Threading;

   5:  

   6: namespace PebbleSteps.TestingTools

   7: {

   8:/// <summary>

   9:/// Creates a Temporary Directory that is deleted when the Dispose method is called.  The class can be used

  10:/// in Unit tests.

  11:/// </summary>

  12:public class TemporaryDirectory : IDisposable

  13:     {

  14:private readonly DirectoryInfo _directory;

  15:private bool _disposed;

  16:  

  17:// The class constructor.

  18:public TemporaryDirectory()

  19:         {

  20: // Create the temporary directory

  21: string directoryPath = Path.Combine(Path.GetTempPath(), Guid.NewGuid().ToString("N"));

  22:             _directory = Directory.CreateDirectory(directoryPath);

  23:  

  24: // Change security to full control so that directory can be used by

  25: // services under test

  26:             DirectorySecurity dirSec = _directory.GetAccessControl();

  27:             dirSec.AddAccessRule(

  28: new FileSystemAccessRule("Everyone", FileSystemRights.FullControl, AccessControlType.Allow));

  29:             _directory.SetAccessControl(dirSec);

  30:         }

  31:  

  32:/// <summary>

  33:/// FullName of the folder.  Files created here will be automatically deleted

  34:/// </summary>

  35:public string FullName

  36:         {

  37:             get { return _directory.FullName; }

  38:         }

  39:  

  40:/// <summary>

  41:/// Persists a stream to a file that is created in the temporary directory

  42:/// </summary>

  43:/// <param name="s"></param>

  44:/// <param name="fileName"></param>

  45:public void StoreToDirectory(Stream s, string fileName)

  46:         {

  47: using (var r = new StreamReader(s))

  48:             {

  49: using (var w = new StreamWriter(Path.Combine(_directory.Name, Path.Combine(_directory.FullName, fileName))))

  50:                 {

  51:                     w.Write(r.ReadToEnd());

  52:                 }

  53:             }

  54:         }

  55:  

  56:#region IDisposable

  57:  

  58:public void Dispose()

  59:         {

  60:             Dispose(true);

  61:             GC.SuppressFinalize(this);

  62:         }

  63:  

  64:private void Dispose(bool disposing)

  65:         {

  66: if (!_disposed)

  67:             {

  68: // Cleanup Unmanaged resources, wait up to 3 seconds for the test to complete...

  69: int retryCount = 3;

  70: while (retryCount > 0)

  71:                 {

  72: try

  73:                     {

  74:                         Directory.Delete(_directory.FullName, true);

  75:                         retryCount = 0;

  76:                     }

  77: catch (IOException)

  78:                     {

  79:                         retryCount--;

  80:                         Thread.Sleep(1000);

  81:                     }

  82:                 }

  83:                 _disposed = true;

  84:             }

  85:         }

  86:  

  87:         ~TemporaryDirectory()

  88:         {

  89:             Dispose(false);

  90:         }

  91:  

  92:#endregion

  93:     }

  94: }

After moving to England I quickly found that I was pining for a library of test utility classes that a colleague of mine had written while we were both consulting on a large ETL project.  I will be slowly rewriting the classes and placing the code on PebbleSteps.  The first class I will be discussing is the TemporaryDirectory which allows the developer to dump files into a folder which gets automatically deleted when the test completes.

For a quick recap the best unit tests:

• Are fast
  
• Are rerunnable
• Don't leave a mess
  

Slow unit tests are inevitably moved into the nightly build process.  This implies that the last two unit test attributes are the most important.  Problems arise when the unit tests need to interact with resources.  Each time the unit test leaves the happy world of burning CPU cycles and moves into interacting with other systems traces of the interaction are left.  Log files are created, alerts are generated, database tables are changed.  Luckily many resources implement the resource manager APIs and all the modifications disappear when the transaction rolls back.  Unfortunately there are a number of scenarios where it is impossible to place the entire test inside a transaction.  

These include:

• Interacting with files
• Creating databases
• Interacting with systems that manage their own transactions such as SQL Server Integration Server 

When testing a component that interacts with files it is important that the unit test cleans up the files after the test completes.  The TemporaryDirectory class automatically creates a directory in its constructor and removes the directory when the Dispose method is called.

Here is the code...  Enjoy!

Scaffolding definition within the building industry

Scaffolding is a temporary framework used to support people and material in the construction or repair of buildings and other large structures. (Wikipedia, 2008)

Scaffolding definition in software project management

Scaffolding are the semi-rigid processes and tools that surround a project that are used to support people in the construction of complex systems such as software.

Each project management methodology is optimised against a particular pain point. When the software industry was young the hardest part of software development was coding. Assembly coding errors was common so reams of upfront documentation was necessary to ensure that coding errors were minimised. Today the pain points are very different. Internet time has shortened product schedules to months instead of years. Customers are far more savvy and their expectations are far greater.

Without delving deeply into the definition of Agile I think that most people would agree that Agile methodologies are optimised to provide constant project monitoring. Unit tests quickly inform developers that code changes have broken working code. Daily stand ups, sprint retrospectives and project post-mortems all ensure that throughout the project, the team is tracking progress and that the progress is visible to external parties such as the client and management teams.

However project monitoring is only part of the Agile way and often it's more important partner feedback is forgotten. In control theory feedback is defined by a system that monitors its output and uses that information to maintain a desired state. For many years I worked for a company that built Autopilots for fishing boats. The ship's captain would dial in a set of way points into the Autopilot and the device would use GPS and other sensors to steer the boat in the correct direction. If the boat was going left or right of the desired target the Autopilot would move the rudder to minimise the error. To a far away observer the boat would appear to be moving in a straight line. However this was not the case.  The rudder would often be moving violently back and forth countering the effects of the wind and current.

Agile projects are at their best when:

• Process is borderline regimental

  • Daily stand ups (happen even when the team lead is away)

  • Defined sprint durations and outcomes

  • Change tightly managed through the sprint/product backlog

• Waste is minimised

  • Code that is developed gets into production

  • Development time is spent solving business problems and not creating YABF (Yet Another Bloody Framework)

  • Meetings make decisions

• Feedback

  • Bugs, Defects, Misunderstandings are caught fast

  • Are fixed faster

  • No surprises for management and the client

  • Project can turn on a dime – features added/removed at will

Unfortunately, over the last couple of years I have seen Agile projects that have failed over and over again in the wild. The projects were humming along nicely and BANG suddenly out of the blue they were 3 months behind schedule and the reporting module which was the project's main deliverable as far as upper management were concerned had yet to be started. I've witnessed highly skilled consultants from companies that are synonymous with Agile being walked out of client sites after project after project had failed. I used to strongly argue that these teams weren't doing real Agile. Like any zealot I nodded my head and sagely predicted that if only they had done some random XP task better they wouldn't have failed or that it was due to poor staff on the project. So it wasn't really Agile's fault. If only they had done more peer programming...

In hindsight, the common problem between all of the failed projects was pretty simple. The project's had lots of alerting and monitoring systems in place such as unit tests, continuous build servers and stand ups but there was no real feedback loop. The projects were in trouble but at no time was the rudder pointed in a new direction.

So, are you on an Agile project; or is the project very good at writing unit tests?