There are many ways for a database to go bad. Losing records is a big problem, of course. Even worse is when one record writes over the top of another one. That corrupts at least one record. Sometimes the damage expands further.
A more subtle problem happens when records change: something that happens often in accounting and estimating software. Usually records get bigger, which means they no longer fit into their old place. It’s easiest to move them to the end of the file, but then there’s a gap at their former location. Do that enough times, and you end up with a huge file that is mostly empty space.
The NeoAccess database kept a ‘free list’ of empty spaces that was meant to prevent both those problems. New and changed records filled in the gaps, and kept the file compact. However, there were occasional mystery bugs, and we suspected the free list was at fault. If it ever forgot to remove a gap, then a record would be trashed when a second was written to the same place. We tried to debug the free list code but it was too confusing.
In 2002, we added the File Manager to help manage file contents. It kept its own list of gaps that duplicated the free list, and also stored the location of every record in the file. That way there were two ways to make sure records were added safely. Goldenseal could find a gap, then double-check the neighborhood to make sure the space was really free.
At first the File Manager was just a diagnostic tool, but it soon replaced the old free list. Mystery over-write bugs disappeared. The Manager added an extra 10 bytes per record, but increased reliability was well worth the cost.
Unfortunately, the File Manager had a subtle flaw. It was by far the biggest record in the database (in the TurtleSoft file, it’s 2.5 megabytes). If users were low on RAM, it was the first thing that wouldn’t fit. Nearly always, that led to a crash or a freeze, but the database would still be fine. However, if there was exactly enough RAM to load the Manager, but not enough to save it, it would die midway through the file save, and corrupt the database. It didn’t happen often but it was awful when it did.
Goldenseal users haven’t reported this problem in the past few years, probably because computers now have so many gigabytes of RAM. However, it’s still a design flaw.
In Goldenseal Pro we fix it by using multiple, smaller File Managers. There’s one for each sector in the file, enough to hold 32,000 records. Managers are accessed via a very broad, 2-level tree that is similar to the ones used to index records. It’s safer and a teeny bit faster than the old system, with less data to read from the drive.
The old File Manager kept growing larger as more records were added. Periodically, it had to be relocated in the file. That was when it was most likely to die, and kill the file with it. In Goldenseal Pro, each File Manager is a fixed size. When it fills, we just add another one. That also makes it safer.
Essentially, Goldenseal Pro stores the location of every record in two different places: one sorted by record ID, and one sorted by location in the file. There’s also a separate list of file gaps that helps keep the file compact. The system is more complicated than the previous File Manager, but not by much. The code is much more understandable, so we’ll be able to repair damage to either half of the record storage.
Right now, the system can handle a billion records. If any users ever get close to that amount, we’ll add code to allow a 3-level tree. That will jump it into the trillions.