Database Design (July 17)

We just fixed the problem of huge files in our new accounting software. It was caused by a couple stupid bugs in the code that decides where to save changed records inside the file. Zapping one got the TurtleSoft company file from 11 gigabytes down to 1.5 gigs. The second fix shrank it to 85 megabytes: bigger than the current 64 megs in Goldenseal, but that’s expected. We added a few things.

Our staff is back to using the new accounting software for our own business, in parallel with Goldenseal. As soon as there’s a month without serious bugs, it’ll be time for a first release.

Why did TurtleSoft write its own database code? Well, if we were just starting now, we’d use something free and open-source, for sure. Let someone else write the code to manage data on disk.

That was the original plan for Goldenseal in the late 80s, also. Our first estimating & accounting templates hit the limits of MS Excel, so we needed to switch to a real app. Open source software didn’t exist back then, so it needed to build atop something commercial.

First, we tried a dozen different databases and other development platforms: FileMaker, FoxBase, Omnis, etc. All had fatal flaws.

We finally decided to write the interface in C++, and license an object database called NeoAccess to manage files. Neo was popular at the time, especially on Macintosh. It worked pretty well, up until we started to test with real data. Uh oh, there were crashes, corrupted files, and weird error messages deep in NeoAccess code. Emails to their tech support went unanswered. Soon the company disappeared.

Searching the early Internet showed internal emails from AOL, Netscape and others also searching for solutions, and/or making plans to give up on Neo. We were in too deep, and had no better options. So our staff spent a year rewriting NeoAccess to be more sturdy.

Most of the time was spent just making the code readable, so we could figure what it did. Several definite bugs turned up. We probably fixed others accidentally. That made it good enough for the 1.0 release. A couple years later, there were more updates to Goldenseal’s database code. Those fixed the last of the leftover bugs.

Our new accounting software inherits the good parts from the original NeoAccess design. Plus the good parts of what we changed and added for Goldenseal. Plus a few improvements, based on 20+ years of experience afterwards.

One thing that’s different now is gap tracking. It’s needed to keep files compact, even though records move around inside them.

Neo used CNeoFreeList to track empty spaces. Actually, many of them, scattered within the file. We replaced them with a single DB_GapManager, doing the same things but easier to debug. We also added a DB_FileManager, a list of where each record was in the file. It made double-sure that records would not write on top of something else. Later, it also allowed recovery of data that was lost when a NeoAccess index became corrupted.

Tracking gaps is complicated. Removing a record may leave a gap of the same size, or it may widen a gap that it touches. Remove a record with gaps on either side, and three gaps must merge into one. The system worked, but it was too complicated.

For the new accounting software, DB_GapManager is gone. Computers are fast enough now that we just zip through lists of record locations and sizes, and compute gaps between them. Simpler and more reliable, once the stupid logic bugs are fixed.

DB_FileManager is also replaced. Goldenseal sometimes ran out of memory because the manager was so big, so we now divide the file into bite-size sectors. DB_SectorManagers each handle disk space for 16,000+ records. Our company file has 13 sector managers. There’s room for 16,000+ sectors, enough to manage 256 million records. There’s a way it can go beyond that, if ever needed.

BTW Google still shows a few hits for CNeoFreeList, 20+ years after its death. It’s mostly users back in the Aughties, trying to fix error messages and crashes in various apps.

NeoAccess caused pain for many Mac software developers, big and small. Other people’s code is not always the best answer.

Dennis Kolva
Programming Director
TurtleSoft.com

 

File Size (July 10)

Well, we finally fixed the mystery crashing error in our new accounting software. It took a week of testing and debugging. At least we fixed a few other potential problems along the way.

The good news is, the bug was simple. Our company file grew bigger than 4 gigabytes during the conversion from Goldenseal, but one old line of code still used 32-bit file marks. It truncated the 64-bit address, tried to save a record into the wrong part of the file, and chaos ensued. One small change made it OK.

The bad news is, our company file should not be that big. It already took up 3 gigabytes, then grew to 11 gigs in June. Goldenseal only needs 64 megs for TurtleSoft’s data. The new accounting app will have bigger files, but not by that much.

In theory, the new database has a good system to keep the file small, similar to what’s in Goldenseal. When saving a record, it looks for the first gap that’s a snug fit, and puts it there. If no tight fits, it then looks for large gaps with enough space left over to hold another record. If none of those, then it tries gaps that waste a little space (currently 24 bytes, but we may tweak it later). Any of those keep the file size unchanged. If no suitable gaps at all, the record tacks onto the end. That makes the file bigger.

If we’re lucky, there’s a stupid error in the gap-tracking code. Fix that, and files will shrink to a reasonable size.

If not lucky, we’ll need to fix a subtle design problem. One that can affect any database.

The conversion from Goldenseal to the new app does something potentially nasty. It loops through each bank transaction, and adds the ID to its bank account. That makes the account record 4 bytes bigger.

New files start out tightly packed, with zero gaps. When we save the account, it’s slightly too big for its old space, so it adds to the end. Next save it’s even bigger, so to the end again. Repeat for 25,000 bank records and the file gets huge, with 25,000 gaps that gradually increase in size.

If that’s the case, something clever needs to happen during the conversion to keep things compact. There are a few options, but we’ll worry about that only if it’s needed.

Luckily, the gap-finding system rarely fails in normal daily use. Records come in all sizes, so most of them will find a gap that’s snug. There always will be some wasted space within the file, but not much.

When our staff wrote the database code back in 2016-2018, we also added reports to help debug what’s inside the file. Right now, we’re tweaking those to be more useful and accessible. It will help track down whatever it is that makes files too big. Also any future problems.

Dennis Kolva
Programming Director
TurtleSoft.com

 

 

Data Conversions & Corruption (July 2)

Thanks to rain and hot weather, our staff has fixed many problems in breakdown tables and elsewhere. It’s time to test on our own business again. Except, we can’t. Converting TurtleSoft’s company file to the new format crashes part way through.

It’s something that has happened before. In the past we fixed things by tweaking how often changes are saved to disk. Sadly, that wasn’t enough, this time. All it did was change where the crash occurs.

Conversion is a complex process. First it reads from the Goldenseal file, using NeoAccess database code. Then it writes to the new file, using the new database setup. Some data gets tweaked along the way. For example, breakdowns change from two types to one. Bank accounts and transactions change from seven types to three. Other small fixes and improvements happen.

Once all data is moved over, there’s a second round of tweaks. Breakdowns and banking records may have new ID numbers, so anything that links to them needs to change. That process happens entirely within the new code, and the crash happens there. The Sample file and other test files always sail through, but not our data.

The most likely culprit was memory use. Our file has 20+ years of data, so it’s big. Many things need to stay in RAM during the conversion.  However, Activity Monitor only shows 65 megs max, which is tiny compared to some other apps. It stays about the same, so no big memory leaks.

We tried saving changes much more frequently. That made the conversion run very slowly, but it went to completion. Yippee! Sadly, when we opened the file, it gave warnings for bad data and crashed in an entirely different place.

That is very bad news. Corrupted data usually happens when data writes onto the wrong place in RAM or on disk, and zaps something else. Corruption is hard to diagnose, because errors show up long after the bad write that caused them.

We stepped through the code many times, and found a few suspects: things that might screw up saves in rare cases. Fixing those made the software generally more reliable, but it didn’t stop the crashes or bad data.

Early versions of Goldenseal had corruption bugs. To fix them, we first added tons of sanity-checking code, to give error messages for anything suspicious. We also added menu commands to work with raw data inside the file. Verify File is the most useful, but there are others in the View–Security menu. Those helped track down each bug, one by one. The last happened if text was too long in one of the Preferences fields, caught in 2005.

The new accounting software still has the same sanity-checking code, but not the diagnostic tools. Looks like we need to hook them up again, to see what’s going on inside. It may be something funky with our data, or something rare that we just happen to hit sometimes.

If any Goldenseal users want to be data guinea pigs, send us a compressed copy of your company file. We’ll check how well it survives the conversion process. Maybe having more examples will narrow down the cause of this problem. Eventually we’ll stumble upon it, but sooner is better.

Dennis Kolva
Programming Director
TurtleSoft.com

 

 

Exponentials & Climate (June 20)

When Covid-19 first appeared, I wrote about exponential growth. It’s why pandemics happen. Conditions change faster than anyone expects, so they get out of control.

All exponential growth tapers off, eventually. Sometimes it plateaus, sometimes it goes down to small or zero. Sometimes there are hills and valleys. Covid is doing that now.

TurtleSoft also began exponentially. Sales of our estimating software tripled annually over the first few years. Then it flattened for a few years, then shrank by 50% a couple times after Apple Computer almost died. Goldenseal created another spurt of exponential up, followed by a second slow ebb.

When this blog started, we expected to quickly update our accounting software to 64-bit, and see a third spurt of exponential growth. Maybe even bigger than the first two. After ten years of struggle, now we just want to finally finish the project. Then see whatever happens.

Meanwhile, the series of hottest-ever months continues. Last winter it was bad news for TurtleSoft users: great weather for outdoor work, so the usual spurt of stuck-indoors programming didn’t happen. Now it’s good news. AC + Computer > Sweat + Heat Stroke + Garden.

For me, the scariest part of climate change is the chance it also goes exponential. That happens when there’s positive feedback. For example, extra heat causes wildfires, vegetation loss and melting of permafrost, which increases CO2 and methane and heat. Repeat and multiply.

From ice cores and sediments, there’s evidence of large, rapid climate changes in the past: some in just a single decade. Life will be very interesting if we’re headed into one of those now.

Dennis Kolva
Programming Director
TurtleSoft.com

Edge Cases (June 10)

A software tester walks into a bar and orders a beer. They order 0 beers. They order 99999999 beers. They order asdfff beers. They order -1 beers for everyone in the house. The dogs playing poker each order a beer.

It’s called edge cases. Sometimes we try them deliberately. Often, our staff runs into them while testing other things in the new accounting software.

For example, we started with a brand new bank account to test a recent bug fix. Adding one transaction worked OK, but clicking the previous and next buttons gave weird errors. Those have no place to go, so it needed some code to disable them. Adding a second transaction worked OK, but when we came back later it gave an even weirder error, and eventually crashed. Fixing that solved a long-term bug in the way we load the most recently-used item. More code we forgot to add.

Many of the problems are in the screen interface, which is entirely new. The worst are in the links between the screen fields and accounting records. Those can cause all sorts of scary messages, plus corrupted data.

Sadly, I think it will be a few months until there’s a version we aren’t ashamed to release. Too many serious bugs are still turning up.

Dennis Kolva
Programming Director
TurtleSoft.com

 

 

Database & Cache (May 27)

Folks are still discussing exceptions on the Qt developer mailing lists. The new accounting software uses them to show useful error messages and prevent crashes. Ditto for the current Goldenseal.

Right now, exceptions don’t work properly on newer Macs with ARM chips. However, it’s looking better for a possible fix. The details will take a while to figure out.

One issue that came up in the talk is how to terminate cleanly: especially if Apple’s code causes the ARM problem. That probably would be impossible to work around or fix. It’d be like trying to nudge an ocean liner.

Nobody wants their data to be corrupted if their accounting program suddenly quits. Fortunately, we remove that risk for all but the rarest of conditions.

As you fill in fields for a record, it’s only stored in the GUI objects on the screen. Hit Save, and the data moves to a temporary record, stored in RAM. The app then posts changes to other records: anywhere from a few to 30+. Those also sit temporarily in RAM.

After posting is finished, all data writes to disk at the same time. It’s called a commit. The process only takes milliseconds on an SSD, or a fractional second with a hard drive. Modern hardware is great! In the early days of Goldenseal, commits took several seconds.

The only way to screw up the database is to exit in the middle of the commit process. The code there is very careful, so the only real risk is a grid failure or cat-power-plug-swat at the exact wrong time. Everything stays in sync if there’s an exit, anytime else. Unexpected program termination does lose unsaved changes, but what remains is fine.

To manage temporary records, we use a cache. It’s a list of all records in temporary storage, with a bit about their status. How long to keep them there is a design decision. If the cache gets too big, you may run out of RAM memory. Dump records too quickly, and things slow down. Disk access is much, much slower than RAM, so the cache tries to guess at what might be used again soon.

During testing, we’ve seen a few crashes from records that were deleted, but stayed in the cache. Such dangling pointers are impossible to sanity-check, and hard to fix. We need to track down where/when they were deleted, and why the cache wasn’t told about it. The crashes are rare and random, which makes them even harder to diagnose. Problems are so much easier when they happen every time you do something.

We just discovered that deleted bank transactions don’t clean up properly. It’s because we switched where the list of currently-found records is stored. Fixing that may also fix the crashes, but we’ll see.

Meanwhile, breakdowns work pretty well for Material Purchases and Sales. We’ve moved on to Estimates. Those and payroll are by far the most complicated parts of our software.

Dennis Kolva
Programming Director
TurtleSoft.com

 

Breakdown Tables (May 15)

Progress on the new accounting software accelerated this past week, thanks to a rainy spell. That will continue, since most of the garden is planted now. It has 6 different varieties of broccoli, to see which does best in this new location. Also a dozen types of tomatoes, scattered all over the property. If they all do well there will be way too much produce this summer. Maybe TurtleSoft should include a free heirloom tomato with every purchase!

Plenty of bugs are still turning up in the new estimating/accounting app, especially in breakdown tables. The basics work, but there are many small details that still need work. Breakdowns also took a few years to get right in Goldenseal. They are small lingering bugs, even after twenty years.

The new app still uses the original tables from Goldenseal. They no longer draw on the screen or interact with mouse and keyboard: that is handled with a Qt table and cell fields. But they contain all the code that does math or links cells (e.g. category and subcategory). Getting the two tables classes to talk with each other can sometimes be tricky, but it’s safer and easier than a total rewrite.

We will get breakdowns in Material Purchases and Sales to work perfectly, then move on to Estimates. Those have by far the most quirks. After that, I think the new app will be ready for its first early release.

It will be easy to test Estimates, since I plan to build a tool shed soon. The last time I did any new exterior framing was 30 years ago. Not much swinging of hammer these days, it’ll all be screws and lithium.

Some day we need to update the stock assemblies in the construction starter files. Construction has changed a lot since those were made.

Dennis Kolva
Programming Director
TurtleSoft.com

 

Qt Bugs (May 6)

TurtleSoft uses a framework called Qt to build the new accounting software. It’s pretty good, though it does have plenty of quirks. We’ve worked around most of them, but at least two are unsolved despite our efforts.

The first is just ugly: if you use the tab key to enter text into multiple fields, the flashing vertical edit bars won’t go away. There can be 6 or 8 of them blinking. So far we’ve tried a dozen different ways to remove them, but no luck. Last week we decided to file a Qt bug report on it, but one already was in the system. The ticket was from us, almost a year ago. A Qt person rated it as “somewhat important”. Otherwise there has been no action.

The other bug is more serious, related to error handling.

Goldenseal stays reliable and keeps improving because we “sanity check” anything that might crash. If the test fails, it shows a message with source code file and line number. Getting that info saves 90% of the work to fix bugs.

After the error message, Goldenseal throws a C++ exception. It’s a “get me out of here” that evades the code that will crash. Instead, the exception moves outward until it hits a catch. If no catch, the app terminates.

We catch almost all exceptions in the event loop. The app goes back to waiting for the next action from keyboard or mouse, just like normal. 99% of the time, it means you can keep working just fine. That one thing with the bug still won’t work, but everything else is OK.

TurtleSoft has built our estimating/accounting app atop five other frameworks. All of them had some way to catch exceptions in the event loop. Qt does it for Windows and for Intel Macs, but the catch does not work on newer ARM Macs with a M1/M2/M3 chip. Instead, the app exits abruptly. Any unsaved changes are lost. It’s nasty for users, and also for bug fixers.

We posted the problem on the Qt forum, but the main Qt spokesman there said if exceptions work on Intel it’s just an accident. They are not supposed to work at all. We put in a bug report, but it was closed as “invalid” the next day. Apparently Qt decided long ago to not handle exceptions in most of their code. Period.

Sadly, the new app will be less reliable than we would like. The response of the Qt team is also very discouraging. Bugs do happen, but it’s worse when the problem will never be fixed.

Alas.

Dennis Kolva
Programming Director
TurtleSoft.com

Deploy Progress #1 (Apr 29)

A while back, our staff built and deployed a finished app that runs on Intel Macs. It’s the first time we’ve actually used the new accounting software outside of the debugger.

It’s not huge news for users. Most of the Mac hardware/OS that benefits from this initial 64-bit version can still run the current 32-bit Goldenseal software. However, it’s a big step for TurtleSoft.

First of all, it makes testing much easier. Up until now, we had to use two different machines for in-house testing: Goldenseal on Intel vs new app on ARM. Now they both can run on the same machine. Copy/paste works, and there’s no longer need to swivel or hit the KVM switch. Mismatches are more obvious now, with windows side-by-side on the same screen.

We also created a 1/2/3 cheat sheet for the command-line deploy process. It builds a finished app with zero brain power: just follow the recipe, and paste a huge block of text into Terminal. A slightly different deploy process also works for newer ARM Macs, but we still need to figure out Apple’s code-signing system to make that work. Ditto for the Windows deploy.

With more frequent and deeper testing, bugs are still turning up. We probably should stress-test for a month or two before the first public release. That way the new accounting app will be less annoying for early adopters.

TurtleSoft moved to a new long-term location last October. It has a huge yard, and this is peak garden season. These days, plants and seeds consume much of our quality time.

We’re now growing Goldenseal, the plant. It’s the first time there has been habitat for it. These are in the greenhouse, and some are outdoors. The snapshot also shows Sugar Snap peas in the background for early harvest, and parsley just sprouted for greens next fall and winter.

Fortunately for users, there is rain. Plus sore muscles, back and knees. And soon, heat and biting flies. So the software bugs will be fixed almost as fast as they are found.

Dennis Kolva
Programming Director
TurtleSoft.com

 

Income Taxes & Complexities (Apr 18)

Income taxes finished with a few days to spare. It was more difficult this year because of capital gains on a house sale. I waited almost 30 years to sell, and that was a bad idea. It would have been better to move before price minus basis went over the $250K home sale exclusion.

Back when TurtleSoft made Excel-based estimating and accounting software, a company called Heizer Software sold Excel templates. They had a cheap construction estimator that was our main competitor. Also, worksheets to calculate and print IRS income tax forms. Those were a real time saver: just enter expenses and income, and everything filled in from there. Heizer had annual updates, but they faded away in the late 1990s.

Remarkably, I still use their 25-year-old Excel templates to calculate and print Schedules C and SE, plus the 4562 depreciation form. The spreadsheets just need minor tweaks each year. Heizer’s 1040 form also worked for years, up until ex-President Trump “simplified” it with an extra page and two new forms. Now it needs typing or copy/paste into pdf or e-file.

One extra complexity this year: capital gains were enough to make Social Security taxable. Calculating the amount is an 18-step process. That seemed way more complex than it deserved. At least the math is in a spreadsheet now, so next time will be easier.

Meanwhile, Apple just approved TurtleSoft LLC to get certificates for code signing. It lets us deploy an accounting app that runs on newer Mac OS versions and M-series chips. Our staff is wading into the instructions for that now. Also futzing with the deploy process for Windows.

The limiting factor for progress these days is just plain old complexity. There’s only so much information that one can absorb in a day. Arcane bullshit seeps in even slower.

Getting new processes to work is kinda like mastering a foreign language. It’s all just gibberish at first. The line 6a/6b IRS worksheet equals learning a phrase or two. App deployment is vocabulary to ask directions and order a meal. Writing an entire accounting app needs PhD level fluency a few times over. ¿Hablas español?

Dennis Kolva
Programming Director
TurtleSoft.com