That would have been *perfect*

Ever think of a witty retort an hour too late? Me too.

Ever think of the best way to handle a sticky interpersonal issue after you already handled it… suboptimally? Me too.

Ever spend three days writing a seed data SQL script and realizing it would have been perfect for TDD? Me too.

Let me back up.

In an engagement, the system and constraints were such that the configuration necessary for the software to run was stored in the database, and due to the “going fast” nature of the project, the necessary putting together of the script so that other environments (staging, production) could operate was held off until very late in the project (a scant few weeks before delivery).

This is, as they say, what it is.

However, the timing of that meant that by this point there were dozens if not hundreds of rows across tables and databases that needed to be recorded and put into production so that the application would work out of the box. It was also possible the target database already had values in it, and it’s also possible we’d be adding new ‘seed’ data as the project went on (guaranteed, in fact). So we needed a way that would handle this.

There are a few ways to handle this:

  1. Write a script that contains these values by looking through the databases and manually constructing the script.
  2. Generate a script that can run idempotently (in case some of those values are already in the target environment) by writing a program that targets the tables with these values and emits idempotent SQL. (Idempotent is a fancy word for re-entrant which is a fancy word for being able to run the same thing over and over again and getting the same result each time, without cascading side effects
    (As a simple example, i=i+1 will always increment i, no matter how many times you run it. i = 10 will always assign 10 to i, no matter how many times you run it. The latter is idempoten, the former is not).
  3. Use a program like SQLCompare to ‘diff’ the databases and have it generate the script.

There isn’t much difference between #2 and #3 except that if we write our own program, we can target tables, or target uniqueness (inside baseball: The unique values were often strings, since this was configuration data, and business domain uniqueness was not the same as database uniqueness values (Primary Keys).

With Writing our own script generation program, we can also omit auditing data, as that wouldn’t need to be captured or maintained on the seed data. A common complaint with SQL Compare is that it retained the auditing columns as well.

So I chose #2, and started off. The requirements: Write a program that generates SQL that can target specific databases and specific tables and get out the unique occurrences of configuration data and put that data into a idempotent set of inserts that won’t overwrite the data if it exists on the target table, but will fill in the gaps where it doesn’t exist.

Did I mention this problem is ripe for TDD? Did I also mention that I failed to use TDD to solve this problem?

Why? I felt like it was ‘too small’ for TDD, and that I’d be able to make quick work of it without TDD, and that it was a one-off program.

And then I got into the problem a bit deeper, and realized there were rules I had forgotten about. Like in SQL certain types of values need to be escaped, and others need to have quotes around them, and what about quotes inside of values that contain quotes? All of this made for a more difficult implementation than I was expecting.

So I did it. In the end, I pivoted languages for business reasons, but I got it done, without TDD.

And then I realized another problem I had created by not using TDD: The only documentation was whatever my variable names were and whatever I wrote as documentation.

Well named tests and examples of executing the code with expected inputs would have given a maintenance programmer a leg-up on understanding what the program did and why it did it, and unlike documentation after the fact, would have helped me go faster and document the code at the same time.

Even after using TDD for all this time, there still times I don’t use it when I should. This was one of those times. IF you want to learn how TDD can help you do all those things I listed above (and more), sign up to get details about my course: TDD for .NET Software Project Teams, which is a TDD course that helps you learn and implement TDD in real-world software projects, using the same real world constraints we have day to day. One of the perks of signing up is that you’ll receive a subscriber’s only discount that won’t be available anywhere else.

Leave a Reply