Android : Upgrading user database using SQLiteAssetHelper

Question

I cant seem to understand how to upgrade the database after reading the docs at https://github.com/jgilfelt/android-sqlite-asset-helper

I want to keep the users data in one table only. I don't need to alter any tables. The upgrade is pretty much adding new rows in another table.

So the flow (I'm guessing) would be :

onUpgrade :
1) get ArrayList of user defined data from table 1
2) update database
3) put the user data back into table 1

Am I thinking about this all wrong?
Any tips would be greatly appreciated.

Richard Le Mesurier · Accepted Answer · 2014-07-03 12:07:38Z

I misunderstood the point of the update script.
When your app is updated it does not install the new database. It keeps your old one and only runs the update script to bring your old one up to speed.

So if you dont need to change your user data it will not be lost. It will only run the sql in the script.

If you do need to alter your user data table you can use a temporary table to do the change.

eg :

-- add a FullNames column to Employees
ALTER TABLE "Employees" RENAME TO 'Employees_ME_TMP';

CREATE TABLE "Employees" 
(
    "EmployeeID" int NOT NULL,
    "LastName" varchar(20) NOT NULL,
    "FirstName" varchar(10) NOT NULL,
    "FullName" varchar(150),
    PRIMARY KEY ("EmployeeID")
);

INSERT INTO "Employees"  
(
    "EmployeeID", 
    "LastName", 
    "FirstName", 
    "FullName"
) 
SELECT 
    "EmployeeID", 
    "LastName", 
    "FirstName", 
    "FirstName" || ' ' || "LastName" 
FROM 
    "Employees_ME_TMP";

DROP TABLE "Employees_ME_TMP";

Martin Pfeffer · Accepted Answer · 2017-06-28 23:40:33Z

1

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}

answered Jun 28, 2017 at 23:40

Martin Pfeffer

12.7k10 gold badges61 silver badges72 bronze badges

Comments

Farhan · Accepted Answer · 2014-03-06 09:34:18Z

0

I want to keep the users data in one table only. I don't need to alter any tables. The upgrade is pretty much adding new rows in another table.

If you just want to add some rows, then just add them in onUpdate.

answered Mar 6, 2014 at 9:34

Farhan

13.4k2 gold badges35 silver badges60 bronze badges

2 Comments

Fuzz Over a year ago

I did consider that, but it is a couple thousand rows and what if the user misses this upgrade and upgrades to version 3. I would like to know how to retain user data in tables for other instances.

Fuzz Over a year ago

Also.. onUpdate is used by the SQLiteAssetHelper, if you overwrite it, it does not do its magic.

Gürkan Çatak · Accepted Answer · 2016-07-25 13:04:15Z

0

There is an easier way that I found. After updating database in the main/assets/databases folder by using Firefox SQLite Manager, just remove the previous database firstly before creating the database in the code, then ship database to application again. To ship: click,
To remove:

this.deleteDatabase("<yourdatabasename>");

For example;

this.deleteDatabase("db.db");

answered Jul 25, 2016 at 13:04

Gürkan Çatak

9631 gold badge10 silver badges18 bronze badges

Collectives™ on Stack Overflow

Android : Upgrading user database using SQLiteAssetHelper

4 Answers 4

Comments

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related