How can I make structural DB changes, without losing data, for an Auto Call Recorder App?

Question

My App records every incoming and outgoing phone call using a service.

This service stores the file after the call is disconnected and then creates a row in an SQLite database.

I then use this data in my MainActivity to populate a ListView using a SimpleCursorAdapter.

The problem I have is that if I make structural changes to the SQlite database (e.g. to add a new column), I need to update the version, which then results in onUpgrade deleting the tables and thus all of the data. Therefore there are no ListView items.

If I populate my ListView by listing files in the directory I wouldn't be able to ascertain the call duration.

score 0 · Answer 1 · answered Aug 29 '17 at 21:42

SQlite databases are good when you need to maintain a lot of complex relational data and have to perform lookups on that data.

For your app you can use something much simpler, that is, a SharedPreferences file.

You can use a simple JSON object, something like :

{
    path : path_value,
    date : date_value,
    duration : duration_value,
    .....
}

And create a array of JSON objects, convert it into a string and save it into the SharedPreferences.

Since SharedPreferences are much simpler, you cant do much with it and therefore you wont have to modify it when updating the app.

NOTE : when you display each of the files, you might also want to use the path to check if it really exists there or not.

I think i can't use shared preferences because in future i need to create a blocklist using sqlite which will ignore call recording for certain numbers. BTW, Is there any sample code available here and there just to get idea which way they store and show files to user? — Ahsan Khan, Aug 29 '17 at 22:06
ofc you can search that on google using query `github android call recorder` — zeekhuge, Aug 29 '17 at 22:14

MikeT · Accepted Answer · 2017-08-30T04:02:57.847

The problem is, if i make changes in sqlite database, i need to update the version and then all listview items are gone because database has been updated but recorded files are stored in app data directory.

(after editing the question)

The problem I have is that if I make structural changes to the SQlite database (e.g. to add a new column), I need to update the version, which then results in onUpgrade deleting the tables and thus all of the data. Therefore there are no ListView items.

You don't actually have to update the version (you can utilise your own methodology if you wish). However, even if you do then you can utilise code to retain the old data. That is you are not necessarily limited to using the much seen, much copied, DROP TABLE call onCreate model e.g.:-

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE);
    onCreate(db);
}

You can use ALTER TABLE to add a column e.g

ALTER TABLE log ADD COLUMN extra TEXT DEFAULT extra ;

Using the the above resulted in:-

Where column extra was added, noting that the default value was applied and that existing data was retained (PS increasing version/onUpgrade wasn't used).

Here's an article that briefly discusses the DROP TABLE call onCreate model, which includes other pointers Android SQLite Database, WHY drop table and recreate on upgrade.

Thanx MikeT, I didn't know that. Thanx alot – Ahsan Khan Aug 29 '17 at 22:33 — Ahsan Khan, Aug 29 '17 at 22:33

How can I make structural DB changes, without losing data, for an Auto Call Recorder App?

2 Answers2