Fuzz Fuzz - 4 months ago 24
Android Question

Android : Upgrading user database using SQLiteAssetHelper

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.

Answer

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";