This month I attended the Vancouver Writer’s Fest in anticipation of NaNoWriMo, and built some data management tooling for Approach.

Vancouver Writer’s Fest

This month was Vancouver’s annual Writer’s Fest, and I finally decided to attend some of the events. I always take a look at the festival’s events and am never really in too much of an active writing mood, but this year I decided to make sure I made it more of a priority and to go to at least one event.

In the end, I actually ended up going to 4!

The most useful was, I think, Night Class: A Workshop with UBC School of Creative Writing, where various UBC professors of their creative writing department came to give very condensed lectures and then a short writing prompt. My biggest takeaway from this event was one of the professors (I think it was Whitney French, though I might be misremembering) talked about for her to really get into writing, she had to figure out how her brain worked. For me, I definitely have trouble choosing and focusing on a single task. What works for me when I’m coding is that I let myself pick up and drop tasks very quickly – short, little bursts of intense focus, and then drop it and switch to something new, until I find something that really catches me and I can dedicate time and deep focus to it. Sometimes I have to pick up and drop the same thing multiple times before I can really take off with it. I’ve never applied this style of thinking and working to my writing. I always try to pick one project and see it through for a while before I drop it and never return to it, usually even dropping writing altogether for weeks or months at a time. I’m going to try some different approaches this November to see if I can coax some better writing out of myself this month.

NaNoWriMo

Speaking of November, NaNoWriMo is kicking off next month, an annual challenge to write 50,000 words and finish the first draft of a novel. It’s much less about writing anything of substance, and mostly just about volume – writing and building the habit of writing!

I’ll be doing little to no coding for the next month (if I can keep myself honest…) and I’ll be focused on writing instead.

Approach

Approach is the 5 Pin Bowling app I build for iOS and Android. This month I focused on iOS and data management.

Automatic Backups

Approach for iOS uses GRDB, a framework for working with SQLite from Swift that makes it really simple to query your SQLite DB with strong typing and robust error handling and concurrency. Perfect for Swift.

Unfortunately, one issue I’ve had with GRDB is figuring out how to safely and effectively sync the database to iCloud. Approach doesn’t charge any money or have a premium plan (yet) and I’m not quite willing to front the costs for a fully fledged backend sync, so it’s gone without any sort of cloud syncing up until now. There’s an option for users to export their data to keep it safe, but I’m not sure you can rely on non-technical users to do that (especially the aging demographic of bowlers).

My solution? I eventually realized that SQLite conveniently works out of a single file. All the data is stored there, and we can move or copy that file easily to create a backup! So all I had to do is use GRDB’s built in backup command to copy the database to another file, then I could sync that file to a folder in the user’s iCloud (as long as they’re signed into an Apple account).

Backup syncing will be enabled by default, but users will be able to disable it and delete the files from their iCloud if they can’t spare the room. It’ll backup with some regularity, and we’ll make sure the backups don’t get too stale or take up too much room. That’s all still a work in progress, but coming soon!

Imports from Android

I had a user reach out mid-October and ask why they couldn’t get their data to import correctly from the exported file from their Samsung phone, onto their new iPhone. The reason was that I had never actually built support for this! Let’s understand why this was the case, and how I built support for it.

iOS Data Format

When using GRDB’s automatic encoding and decoding for records, column names end up matching exactly how you define the corresponding variable (for example, a League belongs to a Bowler through the bowlerId foreign key, which is GRDB will write the column name). You can overwrite this default by explicitly defining CodingKeys on a Codable record, but I opted to use the default behaviour.

Another default, enums are encoded as their String representation, so the enum League.ExcludeFromStatistics has two cases, include and exclude and the SQLite values reflect that:

Android Data Format

Approach for Android uses Room, the de facto data storage solution for the platform. It has slightly different defaults.

When defining your models, you can easily override the column names directly inline, otherwise they will match your variable definition. This led me to mix the Kotlin standard camelCase with SQLite standard snake_case names, appropriate for each platform. A League’s bowlerId property in the Kotlin model would be reflected as bowler_id in the SQLite table column:

In Kotlin, enum class values are usually defined in UPPERCASE, and the value stored in SQLite reflects that:

Supporting Imports

With these platform differences in mind (among others), I couldn’t simply swap out the underlying DB file when importing, so I had to take a slightly custom approach.

First, I let the user pick the file from their filesystem. iOS will give you a URL as a result, and I start by copying that file to a temporary location (that I clean up at the end of the process) so the system can’t lock my access or delete it out from underneath me while I’m trying to process the file.

Next, I look at the file extensions and headers and try to determine what sort of file is being imported. When exporting, Approach for iOS uses GRDB’s backup function to cleanly write to a fresh SQLite DB File. Room doesn’t expose this as easily, so instead I zip up the sqlite and sqlite-wal files and give the user a single zip file. That means when importing I need to know if I’m dealing with a SQLite file or a ZIP file and act accordingly. Other files are unsupported and the app will report an error to the user.

Once I have the primary SQLite file, I open it with GRDB and try to perform a few reads to see if I can determine the format. GRDB uses migrations and a grdb_migrations table to track which migrations have been applied. I know that I can import any Approach for iOS file that has no newer migrations than the current app has – any older and GRDB will automatically handle the migration, any newer and the database is in a format we can’t understand. Room uses a hash value to define its DB versions, so I can inspect those.

For any supported iOS DB versions, I just copy the file to Approach for iOS’s DB file location and let GRDB handle the import/migration. For supported Android DB versions, I perform a series of queries and writes to ensure all the data gets copied over correctly. These queries will have to be kept up to date as the database format evolves over time, but that’s a trade-off I was okay with!

Once the DB file has been prepared, we attempt to open it and if it all succeeds, we let the user inspect the app and validate everything imported correctly – there is always an option to revert.