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?
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
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.
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.
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
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.)