Mega Update - Part 1

I recently gave a client a major update to their custom application. Actually, "major" doesn't even do it justice. It was more like "mega major" and I don't think I'll take the approach I did ever again. But I wanted to move their code to the latest compilers and to support the latest operating systems. I also needed to change some low-level database constructs. Why all this? Because I'm a best-practices sort of guy.

Here's what I had to start:

• Delphi 2006 development environment
• ElevateDB database (ANSI)
• Rave Reports
• Other third-party libraries and custom components

The database used auto-increment integers for primary keys identifiers (IDs). This has worked successfully in all other database applications I've written. And in the beginning stages, it worked for this client as well. But when I implemented remote synchronization, I ran into problems.

Most of the staff worked remotely and internet access to the main office database was not guaranteed. Additionally, when they were traveling, they wanted local database access with the ability to synchronize changes once back in their office.

To accommodate this, I setup number ranges for each person so that new records created locally would have different IDs. The idea was that when remote users synchronized with the main database, new records from multiple people would not have primary key conflicts. This worked in a limited way for a short time, but became too difficult to manage and any error reeked havoc, resulting in duplicate keys (despite my best efforts) or orphaned records. It didn't seem that difficult in concept and I still believe it should've worked, but there were so many problems, I gave up synchronization until I could come up with a better way. (I should note that the problem was purely programmatic, not the fault of the underlying database. I learned a lot about synchronization in a short time and realized it's not as easy as just replicating a database.)

Researching synchronization topics led me to some articles declaring GUIDs as the sure way to avoid integer IDs altogether because a GUID (Globally Unique IDentifier) is "guaranteed" to be unique every singe time one is generated ("guaranteed" should be read as statistically impossible to generate a duplicate because of all the factors that feed into the generation of the number, including network device ID, date and time, and many other factors).

Integers are 32-bit binary values while GUIDs are 32 character hexadecimal strings. So they're an order of magnitude larger. However, with the speed of computers and the internet and with low-cost massive hard disks, the disadvantages are minimal, while the advantage would be that I'd never get duplicates again.

So the first update goal was to switch from integer IDs to GUIDs.

I should've stopped right there. But remember, I'm a best practices kind of guy, and some other goals were lurking close to the surface. One of these was that the Vista operating system was finally stable enough to use and Windows 7 was coming in the near future. I wanted to keep up with the technology and make sure the program was forward compatible on any Windows platform. I was using Vista on a computer in another office and had downloaded Windows 7 to a virtual machine. Not only did I find that Delphi 2006 had problems with anything after XP, but there were changes that were needed in my application as well. The application was easy enough to change, but I wanted to use the newer operating system as my main desktop. Sure there were ways to get Delphi 2006 installed, but admittedly, there were new features I wanted to take advantage of as well.

So the question of what version of Delphi should I move to became a big decision. There weren't enough features in Delphi 2007 to really make me want to jump. But there were in Delphi 2009. On the other hand, a big consideration of Delphi 2009 was Unicode. There were mixed reviews on people upgrading applications to Delphi 2009 and going through the Unicode hurdle. One was old third-party components. I checked and all the libraries I had were already on board with Delphi 2009. Also, the whole world seemed to be heading toward Unicode and since I like keeping up and am a best practices kind of guy, this was added to the upgrade list.

The second goal was to upgrade from Delphi 2006 (ANSI) to Delphi 2009 (Unicode).

Since the development environment was going to be Unicode, I might as well upgrade the database at the same time. A big consideration was the fact that there's no easy and direct way to upgrade the ElevateDB database from ANSI to Unicode. You have to recreate the structure in the Unicode database, export the data to a generic format (like CSV files) from the ANSI database, then import the data into the new Unicode database. ElevateDB has a pretty good scripting language and I was able to almost completely automate this (more on this in Part 2).

The third goal was to upgrade from ElevateDB ANSI to ElevateDB Unicode.

Of course, all the third-party libraries had to be updated. This didn't at first seem to be a problem. I use Raize Components and they had a Delphi 2009 version early on. EurekaLog, a great error reporting tool, thought Windows 7 was Vista, so that needed to be updated. A new version of AddictSpell, a spell check component, was available. And of course, ElevateDB was already in place. My own components simply needed recompiled.

Initial tests showed that the embedded Rave Reports engine that shipped with Delphi 2009 was going to work just fine. So, it looked like everything was in place. I'll go into the details of the conversion process in the next installment of this story, but I want to cover the problems with the reporting tool first.

As I neared the end of the conversion and was testing every little detail, I noticed some reports had strange characters. I also started getting a "stream" error. These and other problematic issues with the Rave Reports engine led me to believe (somewhat erroneously, as I learned later) that the reporting engine was broken and had to be completely replaced.

I should stop and explain why I jumped to that conclusion so quickly. Several years ago, at an ODUG meeting, I won a copy of Rave Reports 4, a reporting engine that had begun shipping with Delphi. Since it was free, looked fairly solid, and was a lot better than the current default reporting tool, QuickReport, I started using it. I upgraded over the years and learned how to make it do anything I needed.

However, Nevrona Designs, publisher of Rave Reports, started getting behind in putting out updates and tech support seemed to be slow. Errors were fixed with work-arounds instead of product upgrades. Along the way, their web site languished in spelling errors, broken links, and outdated information. I had pre-paid for a promised major update which didn't show for several months. I finally asked for my money back and later made the decision to switch to ReportBuilder, used by so many Delphi developers, it's practically the standard.

(I later learned the strange characters mentioned a couple of paragraphs back were actually a result of using a non-Unicode aware database memo component and saving characters to the database incorrectly. I never did find the stream error.)

The fourth goal was to rewrite all the reports in ReportBuilder.

Any one of these goals would've been reason enough to call the upgrade "major" but the combination was just crazy. I spent many sleepless nights, stressful days, and long weekends of programming like mad to reach the end result I had set myself up to attain.

Eventually, I did. And even lived to tell about it!