How to update SQLite database when I didn't specify a version in my previous APK

Question

I created my original app using Kivy for Python. Since then, I decided to port it over to Java and have re-built it in Android Studio. The app has an SQLite database that needs to be updated (new table, more entries, etc).

I saw the the onUpgrade() function can be used to do this, but it looks like the way it works is that I have to specify the old database version and then specify the new one I am updating with.

However when creating the original version, I don't specify a database version.

How do I then update the SQLite database within the app? For this version, I'm more than happy to delete the old database and add in the new one as it's currently not using any data specific to the user.

i posted a code, also you can show this link https://stackoverflow.com/questions/12730390/copy-table-structure-to-new-table-in-sqlite3/12753695 — Alejandro Gonzalez, Jun 14 '20 at 18:04

score 0 · Answer 1 · answered Jun 14 '20 at 18:04

0

You only need to clone the table

CREATE TABLE copied AS SELECT * FROM mytable WHERE 0

Where mytable = 'Your table'

Also you can delete the table and recreate the scheme, but first you should do a backup

answered Jun 14 '20 at 18:04

Alejandro Gonzalez

1,221
4
15
30

Will all the new entries automatically be copied into the existing database on the users phone? – c_n_blue Jun 14 '20 at 19:26
If the database exist in the phone , the new entries could be copied into database – Alejandro Gonzalez Jun 14 '20 at 19:32

score 0 · Accepted Answer · answered Jun 14 '20 at 18:05

0

You have two possible solutions:

find the current version of the database, reading a table in the database where you will store the database version). If the table does not exist (as in your actual app version), you can assume to be in the version 0 and perform any required upgrade process.
delete the current database, as you are suggesting, and recreate it from scratch, adding the database version somewhere in the database.

To keep track of the database version, I suggest a Version table where you have just a few fields: VersionNumber, UpgradeDate.
Anytime you upgrade the database schema, you need to update the Version table with the version you have upgraded to.

answered Jun 14 '20 at 18:05

Yusef Maali

2,201
2
23
29

For the new version of my app I've added a database version so going forwards it should be ok. Given my previous app wasn't in Android Studio, how do I read that database version. Do I do it directly from my phone? – c_n_blue Jun 14 '20 at 18:17
You always want to read the database version at runtime and not through the IDE (ex. android studio). Do not hardcode the version in your code. Upgrading the schema requires you to exactly know what is the version of the database and you cannot assume it hardcoding versions in the code. – Yusef Maali Jun 14 '20 at 18:22
And then just select the database version at max(database_version)? – c_n_blue Jun 14 '20 at 18:55
Exactly. You can also order descending by versionNumber and take the first entry – Yusef Maali Jun 14 '20 at 20:55

How to update SQLite database when I didn't specify a version in my previous APK

2 Answers2