At my day job, we finally decided to cull out a couple of columns that were no longer needed in our database schema.  We could have left them in, but since they were in a table that typically ends up with several hundred million rows, we thought it *might* actually save us some space in the long run.  Besides, taking out a couple of columns should be easy, right?


Let’s see what all this change actually entailed.  Obviously we had some code that was still wrapping these properties in our object model, so I had to remove the properties.  Then I had to update their tests (no, this does not mean that Evil Rob was right).  Then I had to update the data access code, and that’s when things took a turn for the hilariously painful. 

Our data access code is very, very old-school.  Our Data Access Layer (DAL) is a single, massive repository that provides access to *everything* in the database.  The interface for the DAL is 1,342 lines long!  THE INTERFACE!  When the decision was made five or six years ago to consolidate all the data access code, we failed to realize that the simple schema we started with was likely to balloon, and that every single module in the system was going to want slightly different views of things… fast forward to today, and yeah, our data access interface is 1,342 lines of code and comments.

So, I had to update the interface.  Do you know how well Visual Studio works on files that are 1,342 lines long?  NOT VERY WELL.  Unfortunately, it gets worse: we have *two* classes that implement the DAL interface.  One is a stub that is used in testing.  The stub is 4,153 lines long.  Yeah, it’s a complicated stub.  It tries to mimic a lot of the things that the database does, that way developers working on modules in the system can’t do things that the real DAL won’t allow.  So, I had to update the stub.  Again, how well do you think Visual Studio works with a file of that size?  I’ll give you a hint, there is about a 10 second delay between my fingers hitting the keyboard and a burst of text appearing on my screen. Sidenote: some of this may be due to Resharper, but since using Visual Studio without Resharper is like rubbing a cheese grater on your brain, I refuse to disable it.

Alright, so I update the stub DAL.  Then, I update it’s test fixture, which is 4,107 lines.  /scream

That’s out of the way, so now I’m ready to hit the real DAL, a 11,882 line monster.  Again, we did things old-school: nothing goes directly to the tables, everything goes through stored procedures.  Recall that I just removed two columns/properties.  Well, I have to change things in four places in the DAL!  I have to change the code (removing references to the deleted columns/properties, removing all the 100% obsolete methods, etc).  Then I have to change all the stored procedures, deleting those that are completely obsolete and altering those that just referenced the deleted columns.  Then I can actually delete the columns from the table.  THEN I have to update the unit tests (which was actually the easy part, thank God we write decent unit tests). 

Finally, it is done, and I’m ready to commit.

Oh, wait, I’m NOT finished, there’s a third class that implements the DAL interface: a web service wrapper class!  See, web services were all the rage five or six years ago, and we had to provide a mechanism for people to hook in to our application and access its data, so we stuck a web service on top of it.  This seemed like a great idea when we had about 5 tables.  It doesn’t seem like such a good idea now that we have about 30…

Fortunately, updating the web service was the easy part.  The end result?  My SVN commit is going to be a 15,563 line diff that changes 40 files (some classes were rendered obsolete by the removal of these properties; it’s kinda complicated).  All of this so that I could take two columns out of the database. 

The moral of this story is simple: DO NOT HAND-CODE YOUR DATA ACCESS LAYER.  It is error-prone, it is hard to maintain, and it will come back to bite you.  Even if you think "meh, I only have four tables, I don’t need an ORM or code generation or anything", DO NOT DO IT.  Use some sort of tool that will make your life easy when it comes time to make changes down the road.

I think I’ll try repeating the same process on another project here that uses ActiveRecord, and post my experiences with that.  I suspect that it will be a very different story.