Shrinking Migrations

So this is just a small post regarding a recent operation I had to do regarding SQL database migrations.

PadLock version 1.1.0 (currently on the Play Store) had an SQL database with a schema looking something like this:

packageName TEXT,
activityName TEXT,
displayName TEXT,
lockCode TEXT,
lockUntilTime INTEGER,
ignoreUntilTime INTEGER,
systemApplication INTEGER As BOOLEAN


This would mean that entries in PadLock looked like this:

com.pyamsoft.padlock|com.pyamsoft.padlock.app.main.MainActivity|PadLock|NULL|0|0|0
com.pyamsoft.padlock|com.pyamsoft.padlock.app.lockscreen.LockScreenActivity|PadLock|NULL|0|0|0

The displayName field was the exact same for every application in a package. For an application like Chrome or the Amazon Store, this could lead to hundreds of entries all storing the same value for the displayName field.

The displayName field existed only for one purpose, to populate the toolbar on the LockScreenActivity. Seems like a lot of work for something that can be loaded by the PackageManager as long as we have the package name.

So I wanted to go about removing the displayName field which would make the database cleaner and free up the space otherwise held by that TEXT entry. In order to do so, I needed to bump the database version and provide a migration.

SQLite on Android only provides a subset of the ALTER TABLE functionality. Effectively, it can only rename tables. There is no support for dropping individual columns, which is what I needed to do in this case.

The hacky solution then, was this.

ALTER the old table by naming it to a new name.
Copy the schema of the table and remove all of the columns that are being dropped.
Create a new table using the original table name
Copy over all of the needed information from the old table into the new one.
Delete the old table to free up the memory space.


While not too difficult in practice, this is admittedly a hacky work around to a limitation with SQLite, but appears to be the only nice way to get things done.

========================
Follow pyamsoft around the Web for updates and announcements about the newest applications!
Like what I do?

Send me an email at: pyam.soft@gmail.com
Or find me online at: https://pyamsoft.blogspot.com

Follow my FaceBook Page
Follow my Google+ Page
=========================