Whew, that was a challenge. The Widgets app and Apparatuses were updated to version 3.0. This update was nicknamed “The Heavy Lift” because of the difficulty in migrating the existing reference data to a proper database. Instead of using giant JavaScript arrays, which was bad for memory management, SQLite is used instead. This switch was necessary to launch the latest widget — the “Thesaurus” widget.
Back in June of 2019, when this project was started, there was a rush to get something launched by the Fall of that year. Apple was pulling the plug on Dashboard and lots of people (myself included) were looking for an alternative. The early results were not too bad. The Widgets app reached as high as the #3 paid app in the “Reference” category. Nice!
The general idea is to make the app better with each update. But for the 3.0 update, something extra special was needed. Looking at the feedback, a “Thesaurus” widget was a common request. This was surprising, as the “Dictionary” widget already has a “Related” feature. Apparently, people wanted something more powerful. That’s where SQLite enters the picture.
Previously, the memory usage could hit around a quarter of a gigabyte. If you looked up a word, a bible verse, and maybe a zip code, the app would drastically expand its memory footprint and not let go. Looking at the 140,000+ lines of thesaurus data, this problem had to be solved before the new widget could be added.
The first step was to actually create a database. Photics.com uses MySQL, which is a very common web database. However, Widgets is meant to work offline. At least, it’s meant to work offline as much as possible. (The Weather widget simply requires Internet access to fetch the latest data.) While it is possible to run MySQL locally, SQLite emerged as the alternative. The DB Browser for SQLite app helped to solidify that decision.
The app makes it trivial to convert comma-separated values (CSV) to SQLite tables. Even better, there is an option to execute SQL. This was perfect for testing search parameters before moving SQL into Xcode. Getting the app code right was tricky. That was the difficult part. But now that the problem is solved, that information is applicable to future development.
First, the zip codes data was migrated. Once the “Weather” widget was working again, the same process was applied to the “Bible” and “Dictionary” widgets. There was a bit of a snag with optimizing the SQL. The idea was to improve memory usage without sacrificing CPU performance. Using the “lower” and “upper” SQL functions, in order to modify the case of the letters, was causing the CPU to work harder than before. Fortunately, the problem was resolved. It wasn’t necessary to use those functions to get the job done.
When the “Thesaurus” widget was finally working — wow — it felt powerful. Searching a word is as simple as typing it. From there, synonyms can be clicked. It almost feels like a game, to see how far away you can get from the starting word. The “Thesaurus” widget also feels useful. Hopefully people will like it. A lot of work went into this one.