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:

data table(static table)
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.

There is a section in the SQLiteAssetHelper README concerned with upgrades. — PPartisan
– PPartisan, Commented Feb 21, 2017 at 7:00
but i dont want to alter the table. i just want to insert more data (rows) into a specific table — varsha valanju
– varsha valanju, Commented Feb 21, 2017 at 7:17
and i read readme file, it contains info about altering table. — varsha valanju
– varsha valanju, Commented Feb 21, 2017 at 7:17

Community · Accepted Answer · 2017-05-23 12:31:55Z

2

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.)

edited May 23, 2017 at 12:31

CommunityBot

11 silver badge

answered Feb 21, 2017 at 7:53

CL.

182k18 gold badges241 silver badges282 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

varsha valanju Over a year ago

means if i have more 100 new questions n answers then i have to write 100 insert queries i that file?

CL. Over a year ago

No, such tedious tasks are better left to the computer. If you want to know how, that would be a different question.

varsha valanju Over a year ago

i tried this method: >>> db.execSQL("attach database ? as old_db", new String[]{database_name_old});. db.execSQL("insert into "+database_name_new+".favourites " +" select * from old_db.favourites");

varsha valanju Over a year ago

but still its erasing local data...why?

CL. Over a year ago

That is not how you would do the upgrade. (It's not a text file containing SQL commands.) See How to compare two SQLite databases.

|

Community · Accepted Answer · 2017-05-23 11:53:59Z

finally i got answer for my questions. as i said i am using SQLiteAssetHelper library. and now i want to add new records and want to release the update version. so i was finding the feature by which i can store new updated db in assets folder. and this library will update the old db in user's phone. with keeping particular table in old db.

but currently this library doesnt offers such feature. the upgrade script can be use only to make changes is old db. we can add new updated db and copy the data from new one.

if we want to add new data and also dont want to erase local data table. then we need to write insert queries in upgrade script as described in documentation of that library.

but if we have to add 100 queries then their is no shortcut for it. we have to write 100 insert statements in upgrade script.

and in case if we gate a error saying cannot upgrade read only database from version x to y then here is the solution for it:

SQLITE cant upgrade read-only database from version 1 to 2

Collectives™ on Stack Overflow

how to upgrade database version with new rows with SQLiteAssetHelper

2 Answers 2

12 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

12 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related