Distributing One-Time Data "Hot Fixes"

Fixing production client data for a subset of a large user base is a tricky problem.

For example, we support about 75 tax collectors spread across four counties in Pennsylvania. We prepare their annual tax data files which include billing information for all the taxpayers in their district. Occasionally, we realize after sending out the data files that we made a mistake in the files for some of the collectors.

So how do you distribute a one-time data fix to multiple users without impacting unaffected users?

The `ApplyHotFixes()` Routine

At startup, our tax collection software calls the following routine:

Sub ApplyHotFixes()
    On Error GoTo Err_ApplyHotFixes

    If NeedsHotFix8335 Then ApplyHotFix8335
    If NeedsHotFix8044 Then ApplyHotFix8044
    If NeedsHotFix7668 Then ApplyHotFix7668
    If NeedsHotFix6484 Then ApplyHotFix6484

Exit_ApplyHotFixes:
    Exit Sub
Err_ApplyHotFixes:
    Select Case Err.Number
    Case Else
        LogErr Err, Errors, "tcHotFixes", "ApplyHotFixes", elHide
    End Select
    Resume Exit_ApplyHotFixes
End Sub

The routine goes through a series of checks to see whether the current user's data file requires a particular hot fix. If it does, the hotfix gets applied.

If there is an error within any of the calls, the error gets logged via the LogErr call (either to a local text file or to FogBugz via the BugzScout feature). The elHide parameter is an error logging enumerated value that tells the LogErr function not to display the error to the user. I include this to avoid an unexpected error message annoying the user on every startup.

This entire routine runs in under a tenth of a second almost every time it's called.

Key Objectives: Speed and Safety

In test driven development, the first step is to create a failing test.

We treat the NeedsHotFix#### function the same way here. It should return True before running the associated ApplyHotFix#### routine and return False after that routine runs.

Once we confirm it works the way we expect, we can put it in place to act as a gatekeeper for the ApplyHotFix#### routine.

Achieving Speed

Rather than running an expensive query to check whether a problem exists in the data, I start with a series of very fast guard clauses.

The goal is that these checks will disqualify the data environment without having to run the query in the first place. Here's an example of what that looks like in practice:

Private Function NeedsHotFix8335() As Boolean
    If Duplicate.TaxDistrict <> td_BermudianSpringsSD Then Exit Function
    If App.G.Year <> 2020 Then Exit Function
    If App.G.CountyID <> C.CountyID.Adams Then Exit Function
    If Duplicate.EntityType <> et_School Then Exit Function
    If ECount("*", "Receipts", "(BillID=146128) AND (ReceiptID=1) AND (TCReceiptID=3117)") = 0 Then Exit Function
    
    NeedsHotFix8335 = True
End Function

I start out with four extremely fast-executing checks that will fail for most circumstances. I run them in order from fastest to slowest to execute. The first four have similarly fast execution speeds. As a tie-breaker, I start with the most restrictive check (i.e., the one most likely to return True in the greatest number of situations).

Only after all four instant checks pass do I finally use Allen Browne's ECount() function to look inside the data to see if the hot fix needs to be applied.

Achieving Safety

Within my ApplyHotFix#### routines, I try to rely as little as possible on the NeedsHotFix#### routine being run first.

For example, I use the most restrictive WHERE clause I possibly can so that if somehow the ApplyHotFix#### routine gets run accidentally it doesn't ruin someone's day. Here's an example of that:

Private Sub ApplyHotFix8335()
    Dim SQL As New clsSQL
    SQL.From = "Receipts"
    'This hot fix should only be applied to a single record 
    '    in the Bermudian Springs school district file
    SQL.Where = "District='10'"
    SQL.AndWhere "SchoolDist='B'"
    SQL.AndWhere "BillID=146128"
    SQL.AndWhere "ReceiptID=1"
    SQL.AndWhere "TCReceiptID=3117"
    SQL.SetValue "TCReceiptID", 1003117
    SQL.Update
    Debug.Print SQL.RecordsAffected
End Sub

In my development environment, I would run the above routine a few times to confirm that (A) it only changed the number of records it was supposed to on the first run and (B) it did not change any records on subsequent runs.

Sample Execution

Note: In the screenshot below, I did not switch the routine signatures to make them Public. By fully qualifying the routine name of a Private Sub or Function you can call it from the Immediate Window! Try it, it really works (you will have to type the name out...IntelliSense won't autocomplete a private routine).

High Signal to Noise Ratio

I use the NeedsHotFix#### and ApplyHotFix#### naming conventions because they convey a lot of information in a very compact format.

The number that gets appended to each routine name corresponds with a case number in our FogBugz database. I can highlight the number then press [Ctrl] + [Win] + [F] and my browser will immediately open to the case information associated with that number.

The FogBugz case provides way more detail and context than I would ever consider putting into a code comment.

(If you're curious how the [Ctrl] + [Win] + [F] trick works, it's very similar in concept to the [Ctrl] + [Win] + [G] AutoHotkey shortcut that I describe in this article.)