Database Schema and Source Control - the tools

A couple of weeks ago I said I would release some example source code for managing database change and getting schemas into source control painlessly. Attached is the full source to the tool used in this approach. It's by no means production quality, I literally knocked this up in a couple of hours this evening, but it works and should provide a basis for improvement.

The tool allows you to follow the approach discussed in my earlier post and have your scripts applied to your database as needed. The tool will also generate database creation and update scripts instead of applying them to your database directly - which is handy for packaging releases.

You'll find 3 projects in the solution - an example database project to run the tools against, a command line implementation of the schema tool and the schema engine itself.

To use the command line tool, use the following arguments;

Deepcode.SchemaTool.Cmd.exe - followed by; (items in red are mandatory);

-h	Display usage information
-l [location]	The location of your database project
-m [dbscript]	Specify db to run in database update mode - scripts will be applied to the database specified. Specify script to generate scripts to create the entire database and to patch it.

When running in database update mode (-m db), use these additional arguments;

-s [server]	The SQL Server to target (default to (local))
-d [database]	The database to target
-id [databaseid]	The id of the database in the DatabaseVersions tracking table.
-c	If present, instructs the engine to create the database if it doesn't exist.
-cd	If present, instructs the engine to create the database. If it exists already, it will first be dropped.
-u [username]	The SQL username to login to the server as - if not present the system will use windows authentication as the current windows identity.
-p [password]	The password to connect to SQL server, when -u is being specified

When running in script generation mode (-m script), use these arguments;

-o [location]

The location to store the generated SQL scripts.

To use this as part of your development process, place a copy of the exe into your trunk/library directory (or whatever strategy you use for SCC of dependencies), and create two batch files - one to update the database and one to generate the scripts. Then, whenever you make a change, just run the update script. When you release a version, run the generation script.

I didn't have time to do it tonight, but it should be trivial to wrap the same functionality as the command line host into a custom MsBuild task and automate everything. If anyone does this, please post a link to your wrapper source.

And finally, again, remember this isn't production quality, I offer no warranty etc...use at your own risk.

Click here for the source code

Database schema in source control

Getting your database schema under source control is an important part of any development process as your solution isn't just the finely crafted code you've built, it's also the database schemas it uses.

There are a variety of ways of bringing your schema into SCC, such as using Visual Studio Database Professional Edition, or one of the fine offerings from red gate, at an absolute minimum you should keep a script that is capable of recreating your schema, but with a little planning and a simple custom tool, it's reasonably straight forward to manage your schema very well without spending a fortune on tools. I present below the mechanism I'm using for the next release of flux. If you've not yet got your database schemas under source control, I'd recommend doing it now!

The way I approach database schemas in source control is to setup a project with the following directories (if using visual studio, you can setup a simple database project and then create the structure shown below).

The concept is, we define an immutable baseline that represents the initial structure of our database tables that, once committed to source control, will not be changed. We then apply any version updates to the structure to update it to the latest version before finally dropping and re-creating any dynamic elements such as stored procedures, views and functions.

The baseline directory contains a single script to establish the initial structure of the database. At an absolute minimum this establishes a DatabaseVersions table as below and inserts a record into it that states the current version is 1.0.0.

Note that the baseline scripts should not include the creation of any stored procedures, views, functions or other dynamic elements - these are re-created each time we run the update process against our database.

With the baseline in place, the updates directory is then used to apply change scripts to the database as each change is made. Each change script makes all of the adjustments necessary in one atomic unit. Again updates are only to the tables, keys and constraints and not the dynamic elements which are covered shortly. As each update is applied, it sets the version number in the DatabaseVersions table to the new version.

Each update is made up of one single script, and the file is named according to the version of the database that it updates. eg: If your script will update a version 2.0.4 database to 3.0.0, the script will be named update020004.sql.

*** EDIT: BE AWARE, YOUR UPDATE SCRIPT SHOULD RUN WITHIN ONE BIG SQL TRANSACTION AND ROLLBACK ALL IF ANYTHING FAILS – THIS WILL ENSURE YOUR UPDATES AREN’T PART COMMITTED ***

The dynamic directory contains all of the various scripts that will drop and re-create any views, stored procedures, functions etc. Each object is represented by it's own script and each script should start by querying if the object already exists before dropping it and re-creating.

The dynamic elements allow the database schema to be validated - for example, if a view uses a field that has been dropped in a recent update, the update process will fail as the view cannot be re-created with this erroneous field.

As a manual process this would all be reasonably tiresome, so I suggest the creation of a custom build task, or command line application that you can run against a database, it's objectives being;

1. Connect to the database (optionally create it)
2. Does the DatabaseVersions table exist? If not, run the initial baseline script against the database.
3. Read the current version number from DatabaseVersions.
4. If a script exists named updateXXYYZZ.sql in the Updates directory, run it and then repeat from step 3.
5. Execute every script contained in the dynamic directory.

All of the above would be completed within one transaction, so if any part of an update failed, the entire update phase would fail.

Having this process in place allows you to keep your schema as safe as you keep your code, but also keeps the overhead of maintaining it reasonably low.

You can even use the above process during the initial development of your baseline. Your baseline starts out as being nothing more than the creation of the database version table, with each update then adding the tables as you develop them. Once your happy with your database and want to make it the new baseline, simply re-script it's structure, overwrite the current baseline and remove the update scripts.

Your tool to process the scripts should also be capable of generating standalone update scripts that can be shipped with your application. Your app will need a script to create the initial release 1 database, but will also then need a single "update v1 to v2" script, followed by an "update v2 to v3" script and so on. This should be achieved by running the tool in script generation mode. It would then;

1. Spit out a script to create the entire database in it's present version. Incorporating baseline, all updates and all dynamic items.

2. Spit out a script to move from baseline to v2, v2 to v3 and so on - but without the dynamic components. Along with a separate script to update the dynamic components.

This would mean that the user of your app only needs to run the main script if they are installing a new instance, or run the individual update scripts followed by the single dynamic script to update an existing instance.

[Edit: The source code to a tool that implements all of the above is now available from this post or from codeplex here]

Beware DTC configuration in a cluster

Suffered from a severe amount of head scratching today due to using Mutual Authentication in Microsoft DTC.

On a particular project of a clients, we have a bunch of applications and a bunch of services. These services use System.Transactions and nest a bunch of transactions together in a DAO layer - which all use the transaction scope option of required, so if a transaction's already started, it simply enlists in it.

Everything appeared to work fine - the clients were working, the services on the test server were working fine, connecting to our database cluster and everything seemed happy and ready for a dry run test into the live environment. When we moved the services into a cluster however, everything failed - with the ubiquitous "communication with the underlying transaction manager failed" error, so common when DTC isn't configured correctly.

After two days, the operations guys discovered (with a little help from Microsoft support) that the issue was actually because we were using mutual authentication configuration in MSDTC.

Mutual authentication works fine server to server and in all non-clustered environments, but where you have a cluster that needs to talk out to another server, or two clusters talking to each other, mutual authentication is a very bad thing, and armed with the knowledge of why, it's easy to understand in hindsight.

It boils down to how mutual authentication works. Normally, the mutual authentication conversation between two machines might go like this;

Machine A: "Hi, I'm Machine A, with IP address 192.168.01"
Machine B: "Are you really Machine A, with IP address 192.168.0.1"
Machine A: "Yeah!"
Machine B: "Cool"

And everything is happy, however, assuming we have two clusters as follows;

• Cluster A (Database cluster - virtual IP 192.168.0.1)
  
  • Server A.1 (IP 192.168.0.100)
  • Server A.2 (IP 192.168.0.101)
    
• Cluster B (Services cluster - virtual IP 192.168.0.2)
  
  • Server B.1 (IP 192.168.0.102)
  • Server B.2 (IP 192.168.0.103)

The conversation goes like this:

Cluster A: "Hi, I'm Cluster A, with IP address 192.168.0.1"
Cluster B: "Are you really Cluster A, with IP address 192.168.0.1"
Server A.1: "No, I'm actually Server A.1 with IP address 192.168.0.100"
Cluster B: "Then I suggest you leave before I call the boys in!"

Clustered servers don't confirm their identity as the cluster virtual name/ip, and instead validate as the current active node in the cluster. This makes mutual authentication fail.

So the moral of the story - if you're using clusters, you simply cannot use mutual authentication. It does actually say this in the recommended cluster configuration, but it's not very clear - is says "You cannot select mutual authentication" - with no explaination, which makes it sound like it's disabled, so when you realise it's not and it's available, you can easily and naturally turn it on thinking that would be the most secure configuration.

As a final note - we were told that in internal networks, the use of "No authentication" is actually recommended practice and that you don't need to use the higher authentication levels.

Playing with WPF

I wanted to explore some of the features of WPF today, so decided to knock up a simple example that would provide a contemporary (AKA: crap looking as it's designed by a developer!) user interface to a fictitious application

I wanted the layout to be skinnable and be able to change various aspects and colours of the application by adding extra XAML. Here's a screenshot of how the basic UI functions;

The left image shows how the UI should look by default (albeit with a distinct lack of controls), whilst the image on the right shows the "reports" panel overlaid on top of the default UI. The overlays fade in and out as they are brought up and closed.

Pretty basic right? Well yeah, but it was a good exercise in learning some of the cool features of WPF. Lets start with the basic layout for the application. The following XAML is for the main window and it's panels.

   1: <DockPanel>

   2:     <!-- Header Area - branding -->

   3:     <Border Height="50" DockPanel.Dock="Top" 

   4:             BorderBrush="{DynamicResource BrandingLow}" 

   5:             BorderThickness="0,0,0,1" Padding="10,0,10,0" >

   6:             <TextBlock Opacity="1" FontFamily="Segoe" FontSize="24" 

   7:                        FontStretch="Normal" FontWeight="Light" TextWrapping="Wrap" 

   8:                        Foreground="{DynamicResource BrandingHi}" 

   9:                        VerticalAlignment="Bottom" Margin="0,0,0,5">

  10:                 <Run Foreground="{DynamicResource BrandingLow}">DC</Run><Run FontWeight="Normal">.Finances</Run>

  11:             </TextBlock>

  12:     </Border>

  13:     

  14:     <!-- Menu Area -->

  15:     <Menu DockPanel.Dock="Top" Margin="5,0,0,0" Style="{DynamicResource MenuStyle}">

  16:         <MenuItem Header="_Reports" x:Name="ReportsMenu" Style="{DynamicResource MenuItemStyle}" Click="ReportsMenu_Click" />

  17:         <MenuItem Header="_Admin" x:Name="AdminMenu" Style="{DynamicResource MenuItemStyle}" Click="AdminMenu_Click"/>

  18:     </Menu>

  19:  

  20:     <!-- Content Area -->

  21:     <Grid>

  22:         <!-- Main content - the account register -->

  23:         <local:RegisterPanel x:Name="pnlContent"/>

  24:  

  25:         <!-- Reports Pane - hides and shows as necessary -->

  26:         <local:ReportsPanel x:Name="pnlReports" Visibility="Hidden" PanelClosed="PanelClosed"/>

  27:  

  28:         <!-- Administration Pane - hides and shows as necessary -->

  29:         <local:AdminPanel x:Name="pnlAdministration" Visibility="Hidden" PanelClosed="PanelClosed"/>

  30:     </Grid>

  31: </DockPanel>

As you can see, we basically split the form into 3 sections. The branding area at the top, the menu area and finally the content area which hosts 3 separate user controls, 2 of which are set to be hidden (the admin and reports panels).

Skinning using resources and styles

Notice that in order to allow the application to be "skinned" and have it's colours changed etc, we have made use of {DynamicResource} in a number of places for brushes and for styles. These are then encapsulated into a separate XAML file. For example, the XAML for the BrandingLow resource is as follows;

<SolidColorBrush x:Key="BrandingLow" Color="#FFCFD3DA"/>

This defines the branding low light brush, and can then be referenced as {DynamicResource BrandlingLow} anywhere in XAML that requires a brush. But to get the resource linked to the application, we must add it to the applications resource dictionary. This is done in app.xaml as follows;

   1: <Application x:Class="DC.Finances.App"

   2:     xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"

   3:     xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"

   4:     StartupUri="MainWindow.xaml">

   5:  

   6:     <Application.Resources>

   7:         

   8:         <!-- Pull in the merged resources - use default skin -->

   9:         <ResourceDictionary>

  10:             <ResourceDictionary.MergedDictionaries>

  11:                 <ResourceDictionary Source="Skins\Default\Skin.xaml"/>

  12:             </ResourceDictionary.MergedDictionaries>

  13:         </ResourceDictionary>

  14:  

  15:     </Application.Resources>

  16: </Application>

This embeds the skin.xaml file as a resource. Within this XAML we can either go ahead and define the elements we need or we can split it down into separate files. In this case I chose to split it into additional files to separate the brushes and styles etc.

Styles are interesting in WPF. They allow you to define a common look and feel for different element types and also add behaviour. Take for example, the style used for a menu item as seen in the example above;

   1: <MenuItem Header="_Reports" x:Name="ReportsMenu" 

   2:     Style="{DynamicResource MenuItemStyle}" Click="ReportsMenu_Click" />

   3:  

   4: <MenuItem Header="_Admin" x:Name="AdminMenu" 

   5:     Style="{DynamicResource MenuItemStyle}" Click="AdminMenu_Click"/>

The MenuItemStyle is then defined as;

   1: <Style x:Key="MenuItemStyle" TargetType="{x:Type MenuItem}">

   2:     <Setter Property="Background" Value="Transparent"/>

   3:     <Setter Property="Foreground" Value="{DynamicResource FontColor}"/>

   4:     <Setter Property="FontSize" Value="10"/>

   5:     <Setter Property="Padding" Value="8,5,20,5"/>

   6:     <Style.Triggers>

   7:         <Trigger Property="IsHighlighted" Value="true">

   8:             <Setter Property="Background" 

   9:                 Value="{DynamicResource MenuActiveBackgroundBrush}"/>

  10:             <Setter Property="Foreground" 

  11:                 Value="{DynamicResource MenuActiveFontColor}"/>

  12:             <Setter Property="BorderBrush" Value="#FF000000"/>

  13:         </Trigger>

  14:     </Style.Triggers>

  15: </Style>

This defines a style named MenuItemStyle that applies to elements of type MenuItem. It sets the background colour to transparent, the foreground colour to the standard font colour from the resources, the font to use 10 pixels and to use a specific padding. In addition it then defines some rudimentary behaviour in the form of triggers.

When the IsHighlighted property of the host item (a MenuItem element) is set to true, the three setters that are specified within the trigger are invoked. In this case it changes the background colour, foreground colour and outline brush to be of specific colours. This provides us with a nice rollover effect on the menus that use this style;

Applying animations

As mentioned above, when we click reports or admin on the menu, we want to bring up the appropriate panel, and play a little animation to fade it in, then fade it out when we close it. This is incredibly easy in WPF.

Here's the Window.Resources section from the main form, which defines two storyboard based animations for fading up and down elements;

   1: <Window.Resources>

   2:         <!-- Animation for showing panels -->

   3:         <Storyboard x:Key="ShowPanel">

   4:             <DoubleAnimationUsingKeyFrames BeginTime="00:00:00" Storyboard.TargetProperty="(UIElement.Opacity)">

   5:                 <SplineDoubleKeyFrame KeyTime="00:00:00" Value="0"/>

   6:                 <SplineDoubleKeyFrame KeyTime="00:00:00.3000000" Value="1"/>

   7:             </DoubleAnimationUsingKeyFrames>

   8:             <ObjectAnimationUsingKeyFrames BeginTime="00:00:00" Storyboard.TargetProperty="(UIElement.Visibility)">

   9:                 <DiscreteObjectKeyFrame KeyTime="00:00:00" Value="{x:Static Visibility.Visible}"/>

  10:                 <DiscreteObjectKeyFrame KeyTime="00:00:00.3000000" Value="{x:Static Visibility.Visible}"/>

  11:             </ObjectAnimationUsingKeyFrames>

  12:         </Storyboard>

  13:         

  14:         <!-- Animation for hiding panels -->

  15:         <Storyboard x:Key="HidePanel">

  16:             <DoubleAnimationUsingKeyFrames BeginTime="00:00:00" Storyboard.TargetProperty="(UIElement.Opacity)">

  17:                 <SplineDoubleKeyFrame KeyTime="00:00:00" Value="1"/>

  18:                 <SplineDoubleKeyFrame KeyTime="00:00:00.3000000" Value="0"/>

  19:             </DoubleAnimationUsingKeyFrames>

  20:             <ObjectAnimationUsingKeyFrames BeginTime="00:00:00" Storyboard.TargetProperty="(UIElement.Visibility)">

  21:                 <DiscreteObjectKeyFrame KeyTime="00:00:00" Value="{x:Static Visibility.Visible}"/>

  22:                 <DiscreteObjectKeyFrame KeyTime="00:00:00.3000000" Value="{x:Static Visibility.Hidden}"/>

  23:             </ObjectAnimationUsingKeyFrames>

  24:         </Storyboard>

  25:     </Window.Resources>

Lines 3 - 12 show the animation declaration for fading up an object. The storyboard follows two animation paths, one on the opacity of the target object and one on the visibility property of the target object. For fading up, we see a spline keyframe animation moving the value of opacity from 0 to 1 over the course of 1/3 of a second. This then fades smoothly between the values over the time period specified. Also, we use a discrete keyframe animation to set the visibility property to true when the animation starts.

Lines 15-23 do the exact same thing but in reverse, and the discrete keyframe animation on the visibility property sets the object to hidden at the end.

Simple huh? So the only thing left to do is to trigger the animation when the menu item is clicked and bring the appropriate panel up.

The possible options here are;

a) User clicks the reports or admin menu

a1) No current panel - just fade up the selected panel

a2) Current panel visible - fade it down, then fade up the selected panel

b) User clicks the close button on a panel

b1) Fade down the active panel

To implement this logic, I chose to use the code behind to track an active FrameworkElement, then when I click on a menu option, I invoke the fade animation on the active element, fade up the new element and make it the active one.

Triggering animations is as straight forward as;

((Storyboard)this.Resources["HidePanel"]).Begin(_activeChildComponent);

As such, the entire logic for showing/hiding panels etc is shown below;

   1: public partial class Window1 : Window

   2: {

   3:     // This will be used to track any active open panels

   4:     FrameworkElement _activeChildComponent = null;

   5:     

   6:      /// <summary>

   7:     /// User clicked reports menu, hide the current panel if there is one and show the reports pane

   8:     /// </summary>

   9:     /// <param name="sender"></param>

  10:     /// <param name="e"></param>

  11:     private void ReportsMenu_Click(object sender, RoutedEventArgs e)

  12:     {

  13:         HideActivePanel();

  14:         ShowPanel(pnlReports);

  15:     }

  16:  

  17:     /// <summary>

  18:     /// User clicked admin menu, hide the current panel if there is one and show the reports pane

  19:     /// </summary>

  20:     /// <param name="sender"></param>

  21:     /// <param name="e"></param>

  22:     private void AdminMenu_Click(object sender, RoutedEventArgs e)

  23:     {

  24:         HideActivePanel();

  25:         ShowPanel(pnlAdministration);

  26:     }

  27:     

  28:     /// <summary>

  29:     /// User clicked to close the panel

  30:     /// </summary>

  31:     /// <param name="sender"></param>

  32:     /// <param name="e"></param>

  33:     private void PanelClosed(object sender, EventArgs e)

  34:     {

  35:         HideActivePanel();

  36:     }

  37:  

  38:     /// <summary>

  39:     /// If an active panel is open, this will fade it down

  40:     /// </summary>

  41:     private void HideActivePanel()

  42:     {

  43:         if (_activeChildComponent == null) return;

  44:         ((Storyboard)this.Resources["HidePanel"]).Begin(_activeChildComponent);

  45:         // TODO: Need to find a mechanism to wait until the fade has completed

  46:         _activeChildComponent = null;

  47:     }

  48:  

  49:     /// <summary>

  50:     /// Sets the panel specified to be the active panel

  51:     /// </summary>

  52:     /// <param name="target"></param>

  53:     private void ShowPanel(FrameworkElement target)

  54:     {

  55:         ((Storyboard)this.Resources["ShowPanel"]).Begin(target);

  56:         _activeChildComponent = target;

  57:     }

  58: }

Have you spotted the deliberate mistake? I've not yet worked out how to wait for the animation to complete, so when you go from panel to panel, the current panel doesn't get chance to fade down properly before the new panel fades up... I'll save that for another day though.