How to check for Database version directly from .db file

Question

I have a dilemma regarding a free app that I allow users to import their data (an sqlite file on the SD card) to a paid app.

After doing these updates; I realized that if I need to make future changes to the database, the user will need to import the same DB version (from the free app) to the existing version (of the paid app).

Real life example:

Let's say version 1 of both apps has 1 table with 5 columns. I do an upgrade to v2, now the 1 table has 6 columns.

When a potential user does an import within the paid app to bring in data from the free app, there is a chance that the free app can be on DB version 1 (if user didn't update app recently) while the paid app would be looking for DB version 2. This I assume leads to a crash on startup.

How can I do a check like this in the paid app before doing import: (pseudo code)

File olddb = oldDb.db; // get the back up file here from SD
int piadAppDBVersion = 2; // check for the current version of the database in paid app

if (olddb.getDatabaseVersion() == getPaidAppDBVersion()) { // made up functions
 // allow import;
} else {
 // Toast "Your databases are incompatible"
}

So the two questions: 1. If the above code is an appropriate solution to ensure compatibility, how can this be done? 2. If the above is not a standard solution to my problem, then what is?

Side note: My import consists of a simple file copy: copying a backcup db on external storage and overwriting the db on internal storage.

One possible solution is to redirect the user to the Google Play store to update the free app before allowing them to start the paid app. — Code-Apprentice, Aug 13 '14 at 22:13
@user2864740 That is a very elegant solution and simple I think. I will give this a while to see if any other answers appear first. — TheLettuceMaster, Aug 13 '14 at 22:15

score 9 · Accepted Answer · edited Jun 20 '20 at 09:12

9

Consider a "version" or "feature" table in the SQLite Database. Do not be afraid to incorporate this as part of a stable schema; many of my database schemas contain journals of applied scripted changes.

However, SQLite does support PRAGMA user_version which can also be used for this purpose, albeit it can only store a single integer value:

The pragmas schema_version and user_version are used to set or get the value of the schema-version and user-version, respectively. The schema-version and the user-version are big-endian 32-bit signed integers stored in the database header at offsets 40 and 60, respectively.

The schema-version is usually only manipulated internally by SQLite ..

The user-version is not used internally by SQLite. It may be used by applications for any purpose.

The user_version PRAGMA is supported via the standard Android SQLite API which can be accessed with SQLiteDatabase.getVersion and setVersion; after the database has been opened.

See also Where does Android store SQLite's database version?

edited Jun 20 '20 at 09:12

Community

1
1

answered Aug 13 '14 at 22:14

user2864740

60,010
15
145
220

This seems like a good plan, but the issue is, I have to FIRST replace the current DB with the backup to check that table. Right? If so, the problem is, I need to check the DB version FIRST, and then IF they are the same, do the replace (import). Unless you know of a way where you can do queries on a file on the external storage (even though the app itself loads the DB class using the current DB (from the internal) - if that makes sense?) – TheLettuceMaster Aug 14 '14 at 18:57
@KickingLettuce You can open a different database file (I'm not sure if it's supported via external storage, but no reason there can't be a temporary "to import" database in the internal, baring space); there is no need to replace the original until the new database is validated and any data transformations have been applied. – user2864740 Aug 14 '14 at 18:57

score 1 · Answer 2 · answered Feb 27 '19 at 02:53

SQLiteDatabase sqlDb = SQLiteDatabase.openDatabase
                    (db.getPath(), null, SQLiteDatabase.OPEN_READONLY);
            if (sqlDb.getVersion()>DBConnect.version)
            {
                //you have to update app to the last version first
            }

How to check for Database version directly from .db file

2 Answers2