One of the projects I’m on has over 2,200 stored procedures. Until I arrived, none of these were under source control — the ‘stable’ version was whatever was on the production database server, and the ‘trunk’ version was whatever was in the development database server (which incidentally wasn’t even backed up).

Anyway, I set about exporting all the stored procedures and checking them into source control. But as easy as it sounds, it actually took a few goes to get the it all right. You see, when generating a CREATE script with IF NOT EXISTS, SQL Management Studio has a habit of generating entire scripts as one giant EXEC sp_executesql string instead of just putting a GO in between. This isn’t very good for development — you don’t get intellisense, syntax highlighting, etc inside a string.

To get around this problem, I ended up generating two separate scripts — first a DROP (with IF NOT EXISTS), then a CREATE (without it) — and set SSMS to put each in the same file (Append to File). This gave me one script per stored procedure, each with an IF EXISTS DROP, GO then CREATE statement — perfect for source control.

Here are some other tips for configuring SQL Management Studio (via Options > SQL Server Object Explorer > Scripting) to make it automatically generate friendly scripts:

• Disable Include descriptive headers. They sound helpful, but all they do is add the script date and repeat the object’s name. This is really annoying because it clutters up my results for source-code text search in Visual Studio.
• Disable Script USE <database>. We often have several copies of the same database running with different names on the same SQL box (e.g. for different development branches). Hard-coding the name of the database in all your scripts means you have to manually change all the USE statements every time you run them. And trust me — the risk of having your changes leak over and get applied to the wrong database is not really something you want to be worrying about.
• Enable Include IF NOT EXISTS clause. Stored procedures, triggers, functions — it’s much easier to just nuke and re-create them every time instead of worrying about the semantics of CREATE vs ALTER. This also means you can just run each script over and over and over again.

If you get it right, SQL Management Studio 2008′s Script DROP and CREATE should produce something that looks like:

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Employee_Get]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_Employee_Get]
GO

CREATE PROCEDURE [dbo].[usp_Employee_Get]
AS
	SELECT
		EmployeeID,
		FirstName,
		LastName
	FROM
		Employee

GO

We also put permissions (e.g. GRANT EXEC) at the end of each script, so they are restored each time the stored procedure is created. February 6th, 2009 | 3 Comments

Just before Christmas we had a problem with an application database where rows were being mysteriously deleted. Unfortunately the table in question is written to by 20-30 stored procedures, so there was no single point in the application where I could put a trace.

Instead, I wrote a quick generic script that would automatically create the necessary triggers to log all INSERT, UPDATE and DELETE commands on a table of your choosing (or just generate the script).

For example, every time someone modified data in the HR.Employees table, a row would be written to HR._Employees_Audit with all the new values, time stamp, command, and name of the user who did it.

You can download it here: sql-server-auto-audit.sql

On my quest for DDD/TDD nirvana, I’m going to start documenting moments of insight on real projects when design problems are solved, principles suddenly make sense for the first time, or pitfalls are avoided. I want to do it partly for posterity, and partly to help others who are also learning. Here’s the first one.

One of my projects at the moment is developing a simple asset management system. It’s basically just a catalog of assets (computers, devices and office furniture) with various details (specs, serial numbers, notes on wear and tear etc). Each asset may be issued to a person.

I was writing some tests for my concrete repositories (mainly in order to check the database mappings are correct), when the question arose: what should happen to a person’s assets when the person is deleted?

My first thought was simply to edit the mapping XML and change a cascade option so Asset.AssignedToPersonID would be set back to NULL. However, I realised this is more than just a foreign key issue for NHibernate — returning a person’s assets when they leave the organisation is a significant domain concern.

If I take the shortcut and leave it implemented as a cascade option:

• It’ll work fine, but the business rule will be implicit, rather than explicitly expressed somewhere as something that happens when a person is terminated. Other developers probably won’t even notice it at all.
• The implementation won’t be testable without hitting a live SQL instance.
• The business rule won’t be observable in other test cases unless they hit a live SQL instance, because mock repositories will likely not factor for it.
• If we ever move away from NHibernate, or otherwise need to recreate the database mappings, this feature could be lost, and will probably only be rediscovered after FK constraint errors arise.

That’s no good. Let’s make this business rule an official part of the domain model instead, with a fully-fledged domain service:

public interface IPersonTerminatorService
{
    void TerminatePerson(Person person);
}

public class PersonTerminatorService : IPersonTerminatorService
{
    IPersonRepository personRepository;
    IAssetRepository assetRepository;

    public PersonTerminatorService(IPersonRepository personRepository,
        IAssetRepository assetRepository)
    {
        DesignByContract.Require(personRepository != null,
            "personRepository cannot be null.");
        DesignByContract.Require(assetRepository != null,
            "assetRepository cannot be null.");

        this.personRepository = personRepository;
        this.assetRepository = assetRepository;
    }

    public void TerminatePerson(Person person)
    {
        ReturnAllAssetsIssuedToPerson(person);
        this.personRepository.Remove(person);
    }

    public void ReturnAllAssetsIssuedToPerson(Person person)
    {
        IEnumerable<Asset> assets =
            this.assetRepository.GetAssetsIssuedToPerson(person);

        foreach (Asset asset in assets)
        {
            asset.Return();
            this.assetRepository.Save(asset);
        }
    }
}

The implementation is not tested yet (and will likely be refactored later because I think it does too much stuff) but here’s what it’s going to satisfy:

[Test]
public void Terminating_a_person_returns_all_assets_issued_to_them()
{
    Asset a = new Computer() { Name = "Apple Powerbook" };
    Asset b = new Computer() { Name = "Dell Dimension" };
    Asset c = new Computer() { Name = "IBM ThinkPad" };

    Person p = new Person("Richard");

    a.IssueTo(p);
    b.IssueTo(p);
    c.IssueTo(p);

    IPersonTerminatorService terminatorService =
        ServiceLocator.Current.GetInstance<IPersonTerminatorService>();

    terminatorService.TerminatePerson(p);

    Assert.IsNull(a.IssuedTo);
    Assert.IsNull(b.IssuedTo);
    Assert.IsNull(c.IssuedTo);
}

It’s a start!

Last week I wrote a SQL query to estimate how many columns are missing from foreign or primary keys. This works because of our naming convention for database keys:

• We use a Code suffix for natural keys e.g. CountryCode = NZ
• We use an ID suffix for surrogate keys e.g. EmployeeID = 32491

This script looks for any columns that match this naming pattern, but aren’t part of a primary or foreign key relationship.

-- Find columns on tables with names like FooID or FooCode which should
-- be part of primary or foreign keys, but aren't.
SELECT
	t.name AS [Table],
	c.name AS [Column]
FROM
	sys.tables t
	INNER JOIN sys.syscolumns c ON
		c.id = t.object_id

	-- Join on foreign key columns
	LEFT JOIN sys.foreign_key_columns fkc ON
		(fkc.parent_object_id = t.object_id
		AND c.colid = fkc.parent_column_id)
		OR (fkc.referenced_object_id = t.object_id
		AND c.colid = fkc.referenced_column_id)

	-- Join on primary key columns
	LEFT JOIN sys.indexes i ON
		i.object_id = t.object_id
		and i.is_primary_key = 1
	LEFT JOIN sys.index_columns ic ON
		ic.object_id = t.object_id
		AND ic.index_id = i.index_id
		AND ic.column_id = c.colid
WHERE
	t.is_ms_shipped = 0
	AND (c.name LIKE '%ID' OR c.name LIKE '%Code')
	AND
	(
		fkc.constraint_object_id IS NULL -- Not part of a foreign key
		AND ic.object_id IS NULL -- Not part of a primary key
	)
	AND
	(
		-- Ignore some tables
		t.name != 'sysdiagrams'
		AND t.name NOT LIKE '[_]%' -- temp tables
		AND t.name NOT LIKE '%temp%'
		AND t.name NOT LIKE '%Log%' -- log tables

		-- Ignore some columns
		AND c.name NOT IN ('GLCode', 'EID', 'AID') -- external keys
	)
ORDER BY
	t.name,
	c.name

Using this script, I found over 200 missing foreign keys in one production database!

So, it seems Service Pack 1 for Visual Studio 2008 adds some support for SQL Server 2008, in that you can now connect and browse SQL Server 2008 servers in the Server Explorer. This’ll let you do cool stuff like generate code with LINQ to SQL, but there’s one important feature missing:

Where’s the SQL Server 2008 database project template?

If you and try to create a SQL 2000 or 2005 project, Visual Studio will ask for a local SQL Server 2005 instance:

There’s no way around this — “design-time validation” cannot be disabled, and SQL Server 2008 isn’t supported yet. In other words, unless you have SQL Server 2005 installed, you cannot open or create Visual Studio database projects at all. I was pretty dismayed to discover this — all I wanted was a place to chuck some .sql database migrations inside a solution!

However, you can download a temporary fix. Grab the VSTS 2008 Database Edition GDR August CTP. I have no idea what GDR stands for, but it’ll solve all your problems by adding new project types that don’t require a local SQL Server instance at all:

The final release is due out this Spring.