Changing SQLite database encoding in AIR app to UTF-8

Question

In an AIR app you can use SQLite via the flash.data classes. It appears that by default the encoding of the database created is set to UTF-16le, which means that textual data is stored with two bytes per character, resulting in a nearly 100% overhead for ASCII-heavy database content.

The default for a SQLite database is UTF-8, assuming the shell program (sqlite3) is indicative. Presumably Adobe has decided to override this for some reason, but I'd prefer not to suffer the wasted storage space if possible.

A PRAGMA encoding = "UTF-8"; statement prior to writing anything to the database would normally resolve the issue, but it appears that's not allowed in AIR either.

My workaround for now is to use a "template.db" that I create ahead of time and bundled into the application. In this template.db I've set the encoding to UTF-8 manually. If the database file does not exist already when my program starts, I create it by copying that template to my database file, then proceed to open and use it normally. I've confirmed that TEXT data is then stored as UTF-8, as desired.

I haven't seen any ill effects yet, but this is hackish. Is there a better way to set the encoding to UTF-8? Or is it a Bad Idea for some reason?

Answer 1

With no other workarounds or answers found, I'm posting my workaround as the Answer. It worked fine in a PlayBook app for the last two years, so presumably has no unforeseen side-effects, at least in that environment:

My workaround for now is to use a "template.db" that I create ahead of time and bundled into the application. In this template.db I've set the encoding to UTF-8 manually. If the database file does not exist already when my program starts, I create it by copying that template to my database file, then proceed to open and use it normally. I've confirmed that TEXT data then now stored as UTF-8 as desired.