The most robust software project I ever worked on

by Michael S. Kaplan, published on 2005/03/03 09:40 -08:00, original URI: http://blogs.msdn.com/michkap/archive/2005/03/03/384392.aspx

I have been talking vaguely about this project a bunch of times, so I figured I would just post about it directly once and get it over with. There is even a cool international bug in there somewhere!

The project was the Partial Replica Wizard.

It was a originally a wizard for Access 97. Jet (version 3.5) has added partial replication as a feature and the Access folks (in their infinite wisdom) had decided to not support it. Jet replication in general was not a feature they ever did much for, as it proved to be a feature that sold a few boxes as a line item on a product marketing description but did not require much support otherwise. Why break the tradition for an extended feature that had taken them through the original? :-)

Now I had been doing consulting work using replication for customers since Access 95 during the beta, so I was of two minds:

• On the one hand, it was very profitable as a consultant to have a useful feature in a product that the product itself ignored (I was very quickly considered "the expert" on the technology mainly on the force of being one of the few non-Microsoft people who was answering so many questions in public on the area).
• On the other hand, as a person who realized that I could never get to everyone who needed help, I forsook my consulting heritage and really wanted Microsoft to do more to support the technology.

I had even co-written an article with Lynn Caldwell (nee Shanklin) on how to "fake" partial replication with Access 95/Jet 3.0. So having the feature added in a robust way was pretty cool.

But they didn't want to touch it. One program manager told me that not enough people used the feature to justify making it easier to use, without even a trace of the irony that such a statement deserved. Make a feature hard to use and then refuse to fix the issue on the basis that no one uses it? Perhaps not as much chutzpah as killing your parents and then begging the court for mercy on the grounds that you are an orphan, but it is on the same order of magnitude!

So one night I had an idea. I started writing a wizard that would allow a user to create one partial replica filter (like all the customers in Ohio or all of the salespeople that sell power equipment or whatever) and would then walk all of the tables in the database and extend that filter based on the relationships. It seemed better as a core product feature than a wizard, but I figured it would make a cool proof of concept. Maybe it would appeal to people enough that they would pick up and start putting some resources into the functionality (and yes, I was as naive as this makes it sound like I was).

Now as the idea was very strightforward, the algorithm used to walk the relationships was also very straightforward. It used a "family" metaphor to explain itself (in my defense, the idea came to me very late at night). I'll see if I can recapture it here:

• Consider the table with the filter to be the "center of the universe" (like the Salespeople table if each saleperson was going to have their own partial replica with which to enter sales on their laptop). We'll call it the "Me" table.
• All of the tables with a many-to-one relationship with the "Me" table (like the Territories table when compared to the Salespeople table) would be considered "Parents".
• All of the tables with many-to-one relationships with "Parents" (like the Subsidiaries table when compared to the Territories table) would be considered "Grandparents".
• All of the tables with a one-to-many relationship with "Me" (like the Orders table when compared with the Salespeople table) would be considered "Children".
• All of the tables with a one-to-many relationship with "Children" (like the OrderDetails table when compared to the Orders table) would be considered "Grandchildren".
• All of the tables with a one-to-many relationship with "Parents" or "Grandparents" (like the Stores table when compared to the Subsidiaries table) and any tables extending from that would be considered "Cousins" (actually aunts/uncles or whatever, but trying to keep the concept simple!).
• All of the tables with a many-to-one relationship with the "Children" (like the Customers table when compared to the Orders table) and any tables extending from that would be considered "In-Laws".
• Any tables with no relationship with any family member (like lookup tables such as the States table if no direct relationship was set up) would be considered "Total Strangers".

The rules for the partial replica are also very straightforward, and involved filtering the table so that related records or records would reasonably needed would be brought in. They could then choose in the case of "Total Strangers" whether to bring in all records or no records depending on their preference. A table could only be visited once and the properties would never be set on it again (we have nice, normal, G-rated families here!).

It then creates the replica, applies all of the rules, prompts for what to do with the "total strangers" (include all or include none), and then populates it with data. It even provides a report containing every property value it set so that you could do it all yourself programatically later on if you wanted to. It is completely re-entrant so that on demand you can change the center of the universe and will remove all the old rules and apply the new ones.

Of course there could have been more functionality (for an example, see what Phillip Garding did with SQL Server's filtered publication wizard -- 100% modelled after this wizard but allowing for example more than one main filter). But it was only intended to be a proof of concept -- I honestly expected them to produce something built into Microsoft Access 97 directly. I emphasized how easy it was to do, in order to help prove how easy the core feature would be.

The wizard was not something that Microsoft could claim to own (since I was just a contract resource and they had refused to do anything for the feature), but when they saw it the response was simple -- how much did I want for it so they could ship the wizard?

I have since been told several things by people both inside and outside of Microsoft:

1. Everything that happened was completely predictable and the fact that I was surprised at the result was just me being naive.
2. I should have built in the additional features I wanted to be done before I demonstrated it to anyone.
3. I should have held out for more money (I gave it to them for my hourly rate)

They are all 100% correct.

But ignoring that I am forced to admit that (to date) this wizard had fewer bugs reported against it (by testers internal to Microsoft or customers external to it) than anything else I have written, despite heavy usage against complex databases. Other than some minor UI spec issues that were handled as bugs (it was obviously written without a spec, but some after the fact UI decisions were made by program managers!), there has really only been one major bug reported against it (more on that in a moment).

This one simple fact has driven my desire to keep design plans on all future projects as simple and clean as possible -- or, in the words of James Doohan (Mr. Scott), the more one overthinks the plumbing, the easier it is to stop up the drain. This is not always easy to do (preaching conservative plans and simplicity to people who work on a project the size of Windows often falls on deaf ears, despite the Star Trek III quote that agrees with the point!) but I have remained an advocate to this idea even to this very day. The implementation may end up being very complex, but the basic principles of the idea should remain simple and straightforward, with clearly reasoned principles.

So what, you may be asking, was that one major international bug that was in the wizard?

Well, the filters that one creates on tables are either

• SQL expressions (like "[EmployeeID] = 4" or "[State] = 'WA'") or
• a simple string with "True" or "False" if one wants all records or none of them

It felt kind of horky to me to hardcode the strings "True" or "False" into the code since I had Boolean VBA variable with the value to put in, so I used the CStr() VBA function to convert that Boolean variable into a string (people familiar with COM's VT_BOOLEAN may now know what the bug is!).

The bug came in when someone in the French subsidiary reported that the wizard did not work on a localized French Windows (this was prior to Windows 2000 and prior to a lot of the knowledge of internationalization issues I picked up since then).

The reason for this was that the French system was translating False to Faux when one called the CStr function on a VT_BOOLEAN. Don't even get me started on what a bad idea that is, but of course the Jet Engine doesn't do that, so it was causing the wizard to create filters that did not work properly.

Yuck.

Oh well, at least the fix was easy!

While I had done some internationalization work before, I can honestly say that it was this one bug (and the problems inherent in the way people used internationalization inappropriately) that fascinated me enough to make it the focus of most of my future work. Over half of my new contracts after that ended up being based on internationalization, globalization, and localizability issues.

Talk about turning a career on a dime!

And I owe it all to that one little Access wizard, the one that also changed the philosophal nature of my development efforts, too. Makes me wish I kept the source code for it, just for the memories? :-)

This post brought to you by "Ω" (U+03a9, a.k.a. GREEK CAPITAL LETTER OMEGA)

# Sriram on Thursday, March 03, 2005 11:17 AM:

# Sriram on Thursday, March 03, 2005 11:25 AM:

# Michael Kaplan on Thursday, March 03, 2005 1:36 PM:

# Dean Harding on Thursday, March 03, 2005 2:43 PM:

# ChrisO on Thursday, March 03, 2005 7:52 PM:

# Michael Kaplan on Thursday, March 03, 2005 7:57 PM:

# ChrisO on Thursday, March 03, 2005 8:27 PM:

# Michael Kaplan on Thursday, March 03, 2005 8:36 PM:

# ChrisO on Thursday, March 03, 2005 8:40 PM:

# Michael Kaplan on Thursday, March 03, 2005 8:51 PM:

# Dean Harding on Thursday, March 03, 2005 9:12 PM:

# ChrisO on Thursday, March 03, 2005 10:25 PM:

referenced by

go to newer or older post, or back to index or month or day