October 2007 - Posts

Unit testing a database
Database testing is often ignored by development teams, under the assumption that testing the application tests the database sufficiently. Richard Fennell gave an interesting presentation at SQLBits recommending formal unit testing of SQL Server using TSQLUnit, part of the xUnit family.

Although most developers (hopefully!) test database objects as they develop them, the testing is usually informal and undocumented, and rarely repeated. Richard suggested that using a framework such as TSQLUnit encouraged tests that would be consistent and repeatable, i.e. all tests could be performed on a regular basis to ensure that nothing that used to work has been broken by a later change.

TSQLUnit is extremely easy to install. The zip file download contains a SQL file called tsqlunit.sql which installs 5 tables and 10 stored procedures. That's it!

Writing tests is also simple, though I've yet to try testing any very complicated objects. Tests can be grouped together into TestSuites, and fixtures (data required for the test) can be created with a corresponding setup procedure for each TestSuite. Tests are run inside transactions, so any data you insert will be cleared when the transaction rolls back.

If you have the inclination then you can write tests to check any database object, security, static data, etc.

Richard also showed how to use Data Dude for unit testing a database, but it's hard to beat the simplicity of TSQLUnit.

Thanks to Richard for a great talk.
Database Change Management

One of the most interesting sessions at SQLBits was about managing change in databases. We still haven't reached the end-goal of a DBMS that prevents changes to objects until you check them out for editing, but it was the first time I'd heard of a solution which actually sounds solid enough to rely on.

When people talk about source-controlling their database they normally mean storing a set of CREATE scripts for every object in the database (there are some alternatives in this white paper). This approach gives you an audit trail so you can work out when an object changed, who changed it and why. Storing CREATE scripts also allows you to document your design using extended properties - it's dangerous to rely on SQL Server storing these, as they can easily be lost during upgrades.

The source-controlled scripts can be used to create an empty database but obviously aren't much use if you're regularly updating a production database - you can't just drop and re-create tables which have any data.

The main problem, though, is that this approach relies on discipline. If a developer or DBA makes a change to the development database and forgets to change the CREATE script then the source control loses its value.

Another problem is that upgrading the production system still requires a bunch of ALTER scripts for the changed tables. Either the developers have to write these at the same time as they make the changes or, when the system is upgraded, the DBA uses a comparison tool to ensure the production database ends up the same shape as the development one. The first of these involves three parts to keep in synch (database, CREATE script in source control and ALTER script for the upgrade) and the second leaves you in a position where the source controlled CREATE scripts are never checked against either development or production databases - although they may still be useful they could get horribly out of synch.

At SQLBits, András Belokosztolszki presented an enhancement to the process just described, which sounds like it might make the difference. András is the architect of Red-Gate's SQL Compare, so was obviously trying to sell the product, but it seems to be the last piece in the puzzle.

What the new version does differently is to compare databases against scripts. When I read about this feature it didn't strike me as particularly useful, but after talking to András I can see how powerful it is.

Firstly it removes the problem of developers/DBAs forgetting to update the CREATE scripts as they make changes. When the time comes for an upgrade to the production system, SQL Compare can be used to check that the source-controlled scripts reflect the development database. Anything that was omitted can be automatically synchronised, so you can be sure that your scripts are bang up to date.

Secondly there's no need to write the ALTER scripts. SQL Compare can compare the source-controlled CREATE scripts against the production database and can generate (and even apply) the scripts required to synchronise, maintaining any data. The scripts generated by SQL Compare 5 haven't always got dependencies in the right order so I'll be interested to see how version 6 does.

There's still a slight issue of losing incremental changes if an object is updated several times and only the final version is reflected in the script, so the fully source-controlled database remains the target. Until then, this seems like the best solution.

Thanks to András for a very interesting talk.

Thanks also to Simon and Tony for organising SQLBits.

Converting to HTA

An HTML Application (HTA) is a special HTML page which runs with the same security privileges as a normal window executable. It's displayed using the IE rendering engine but without any of the IE GUI or security model.

HTA systems are sometimes used to supply the UI on CDs and DVDs (e.g. the SQL Server 2005 install disk) and can also be used purely as a scripting host. One of the most interesting uses, however, is as the UI for an intranet application.

Intranet applications differ from general web application as they are trusted - the code on the intranet does not need to be subject to the same security as your average web page. Lifting these restrictions allows developers to write much more powerful applications, without having to work around the security model of the browser.

Another huge advantage of HTAs over normal IE is that you can control the processes: unless you start IE with the -New command line parameter, then IE is left to decide whether it needs a second process for a second browser. If it decides two browsers can share a process then they share sessions as well - cookies, session state, the works. Each HTA (and each instance of a particular HTA) runs in its own process, so nothing is shared.

Converting an existing web application to run as an HTA can be as simple as including the <application> tag in the start page and switching the extension of your page from ".html" to ".hta". Double-clicking the file will then launch an HTA window, as defined in your <application> tag. Once open, the HTA can be navigated to any page you want. The <application> tag has no effect on normal browsers - it is ignored.

In reality conversion is unlikely to be that simple. There are a number of things to bear in mind when converting a web application to be HTA-friendly:

  • The most important point is that, since the browser security model is not being used, you should not let the user navigate outside your application. If you have links to external sites then these should open in new IE windows or frames (see below) which enforce the usual security model.
  • Frames (including iFrames) are considered a danger in HTA systems as they can be used to show content which may not be as secure as the HTA system itself. Extra security restrictions are placed on the content of frames, for example the Javascript is not allowed to access it's parent frameset. If the content of a frame is safe (e.g. part of the same system) then frames can be declared as safe using the the application="yes" attribute. These frames then have full security privileges, as they are part of the trusted HTA. Viewing such HTML in a normal browser still works as the browser ignores the unknown attribute.
  • HTAs do not get a status bar. For usability reasons it's best to give some indication that any slow requests are being handled (rather than just appearing to hang) so an Ajax-style div with a continuous progress indicator (an animated gif) can be useful. This can be displayed opaquely on the page's onbeforeunload event. One issue you may encounter with the onbeforeunload event is that it's triggered by any hyperlink with an href, even if the href is Javascript opening a new window. The solution (which is better practise anyway) is to use the onclick attribute of the anchor element for your Javascript, and just set the href to "#" or a non-Javascript page.

  • The window.open command is not considered safe by HTA rules, so any new windows thrown up this way will have (at least in IE7) an address bar. If the content of the window is trusted then you can use window.showModelessDialog to open a window which is considered part of the trusted HTA.
  • Any hyperlinks in pages opened using window.open will by default load their response into the same window. This is not true of windows opened as modeless (or indeed modal) dialogs: by default hyperlinks will open a new window to display their response, so target="_self" must be specified explicitly in the HTML of the dialog.