android - how to upgrade database version with new rows with SQLiteAssetHelper

Question

i have developed a tutorial app in which i have saved more than 500 questions and answers. their is one more table called favourites. which is for user input. now, i want to update my app with new questions and answers. but i dont want to erase the data of favourites table (in case, user has marked some questions favourites, so those questions should not be erased from favourites)

so how can i do it? because, i have used SQLassethelper library for database connectivity.

---

my old db contains:

1. data table(static table)
2. favourites table(local table)

so, according to sqliteassethelper documentation i added my new db: that contains: updated data table. i didnt inserted favourites table here coz it will be created in script file. and stored that db in assets>>databases folder.

then i created a script fild db.db_upgrade_1-2.sql

alter table "favourites" rename to "favourites_tmp";
create table "favourites" (
"id" Integer not null primary key autoincrement unique,
"question" text,
"answer" text,
"category" text,
"catid" integer
);
insert into "favourites" ("id","question","answer","category","catid") select from "favourites_tmp" "id","question","answer","category","catid" from "favourites_tmp";
drop table "favourites_tmp";

so i think here favourites table will be created with old data. but when i run the project, it says: no such tabld favourites.

Answer 1

The documentation tells you to

create a text file containing all required SQL commands to upgrade the database from its previous version to it's current version.

You can use any SQL commands, not only ALTER TABLE, but also INSERT.

The database file in the assets folder is used only if there is no old data (if the app is installed for the first time). If there is old data, SQLiteAssetHelper executes the SQL upgrade script instead.

The SQLiteAssetHelper project contains an example that shows how such a script would look like.

To keep the data in the favourites table, you do not need to do anything. To add the new question/answers, use a bunch of INSERT statements. (For how to get those INSERT statements, see How to compare two SQLite databases.)