I’m a big fan of Red Gate’s SQL Source Control, I really am. I raved about it earlier in the year and I still think it’s the best solution going for getting your databases under source control with Subversion.
However, I’ve hit a glitch which unless I’m wrong, appears to be a bit of a design flaw; versioning changes in case. Let’s say I have a table, creatively named “MyTable”, and an equally creatively named column called “MyColumID”. It looks just like this:
It’s all happily versioned under source control as indicated by the friendly green database:
Now let’s say I decide it’s more appropriate for the “ID” suffix to be “Id”:
Naturally, I want to version this change so I switch back over to the SQL Source Control window and go to commit changes:
Whoa! Where’s my change? And that, folks, is the problem.
Collation?
The collation for the database is Latin1_General_CI_AS, the default collation for all English languages (unless you’re American). This is a case-insensitive collation which as has been suggested, may be the root cause of Red Gate’s displeasure.
The thing is though, it’s not. And even it was, there’s still a problem which I’ll outline shortly. Let’s jump back over to the database and change the collation:
ALTER DATABASE SqlSourceControlCaseTest COLLATE Latin1_General_CS_AS
We can confirm the DB is now case sensitive with a quick equivalency check between the same character in different cases:
SELECT CASE WHEN 'A' = 'a' THEN 'Insensitive' ELSE 'Sensitive' END
So we’re now sensitive, let’s try committing changes again:
No joy. I’ll revert back to the original collation.
Some sensitivity
It’s not like Red Gate is entirely insensitive (sorry to keep humanising the tool, it’s just so easy!), lets’ add a column to test this from another angle:
And now try to commit again:
Ah! See that? Now there’s an acknowledgement that the case has changed. It’s almost as though the compare engine in the screen grab above is different to the one which identifies if there are any changes to be shown.
The use case (no pun intended!)
You might be wondering, why does it even matter? If the collation is case insensitive anyway, who cares if we change the case of a letter? Object persistence frameworks, that’s who cares.
Let’s say we’re using LINQ to SQL or any other ORM that automagically persists column names (let’s not debate the merits of this approach right now). Our column name was “MyColumnID” therefore our attribute name on the “MyTable” class inherits this. Because I’m diligent, I’ve got all this under source control – the table via SQL Source Control and the .NET project via Tortoise or Ankh or whatever.
My team members pull all this down and because they’re doing their app development the right way, they have their own instances of the development database. They sync these and they sync their .NET app. Now I decide to change the “ID” case in the DB to “Id” (it turns out FxCop is very disagreeable with “ID”), I regenerate the DBML, change any references to classes with the old casing to the new version and everything runs fine. On my machine.
Because the DB collation is case insensitive I can push the .NET app back to source control and it will work for other people against their own local DBs with the old casing. The app will simply pass “Id” via the DBML generated SQL statements and SQL Server will happily execute it against the “ID” column because the collation allows it. Now, let’s imagine another dev needs to regenerate his DBML. This is where the wheels fall off - his DBML is now back to “ID” but all the other references to this attribute in the code are “Id” which in the .NET world means the build is now broken.
The fix
We could just make other changes to the DB to force the tool to identify the change as we saw earlier on when I added a column, but who wants to do that just to change case? Of course we could also just check out the scripts under source control to a folder somewhere, change them in a text editor and resubmit them but that’s a pretty clunky way of going about things.
Back in the first post I wrote about SQL Source Control, I looked at the “C:\Users\[my username]\AppData\Local\Red Gate\SQL Source Control 1\LinkedDatabases.xml” file and showed how it contained a path to the Subversion working directory for each database. After identifying the working directory and drilling down into the “Tables” folder, we can see the offending database object:
We’re seeing TortoiseSVN indicate the file is under source control and is up to date. Let’s change that by opening up the file and changing any instances of “ID” to “Id” then using Tortoise to commit is back to SVN:
Problem solved! Well, half solved. We’ve got the case change under source control now, which is great, but what about the other team members? We have the same case insensitivity problem so when they go to update, if the change was only case it won’t show up for them to pull down.
The problem is, we can’t really reverse engineer the working copy mechanism in quite the same way. I thought about other changes I could possibly make to the original table which wouldn’t change the behaviour but would cause SQL Source Control to identify a change but to be honest, I’m reticent to start manipulating database objects in this fashion. It’s just not right.
So in the end I found the best way was for anyone else who wanted to pull the change to just delete the affected tables via SSMS, do a TortoiseSVN update on the “Tables” folder in the path mentioned above then drag the scripts back into SSMS and run them. This is not pretty, in fact it’s pretty damn ugly and there are obviously issues around data retention in the affected tables as well. But it’s effective and it gives you absolute certainty the tables are in sync.
Summary
I hate to have to hack around things like this but then again, it’s not every day you make a case-only change in a DB. Still, it’s something Red Gate probably should take a look at because for software which otherwise does most things right, this is kind of inconsistent behaviour. It’s funny also that tools like SQL Compare do a great job of identifying case changes which makes this behaviour all the more odd. Next version fix perhaps guys?