Updating SQLite database when releasing new version of the android app on the store

Question

I'm using sqlite in my android app, my database file is called data.db. Let's suppose that I have two table in it one is having static values(read only) and second is saves dynamic values and pushed the app to the playStore.

In the next version I updated the data.db and I updated the values in first table, added new columns in second table and added third new table and push the app to the PlayStore. So how I can check if user is updating the app and what is best possible way to save existing data and how I can update data.db programmatically when it is a update not a fresh install?

score 4 · Answer 1 · answered Mar 24 '15 at 07:20

You can use SQLiteOpenHelper's onUpgrade method. In the onUpgrade method, you get the oldVersion as one of the parameters.

In the onUpgrade use Switch case and in each of the cases use the version number to keep track of the current version of Database that was sent for each new Version of Database.

Its best that you loop over from oldVersion to newVersion, incrementing version by 1 at a time and then upgrade the database stepbystep. This is very helpful when someone with Database version 1 upgrades the app after a long time, to a version using database version 7 and the app starts crashing because of certain incompatible changes.

Then the updates in database will be done stepwise, covering all possible cases i.e incorporating the changes in the database done for each new version and thereby preventing your application from Crashing.

score 1 · Answer 2 · answered Mar 24 '15 at 05:12

There are two ways

1] Changed database version so that when user updates an app SQLiteOpenHelper's onUpgrade() method gets executed in this method you can write a code to drop tables n create new tables according to new schema and send network calls to fetch data.

2] Push app with pre-installed db. Create database with db version and save it in assets folder. Whenever app runs compare versions of assets's db & app's db. if asset's db version is higher than app's db then this means you need to update database. Directly copy Assete's db into folder structor and sends delta call to fetch data.

score 1 · Answer 3 · edited Jan 26 '16 at 21:53

1

In this example you have add a new column so the changes in this case should be like this (here you gonna not lose your data).

@Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    String sql = "ALTER TABLE " + TABLE_SECRET + " ADD COLUMN " +
     "name_of_column_to_be_added" + " INTEGER";
    db.execSQL(sql);        
}

edited Jan 26 '16 at 21:53

Amir

16,067
10
80
119

answered Jan 26 '16 at 20:46

Salim Lachdhaf

1,073
1
9
21

score -1 · Answer 4 · answered Mar 24 '15 at 07:27

I advice you to use http://www.activeandroid.com/ library. It supports migrations. You just need to write [n].sql files in assets/migrations folder for n version of you database model. Active android carries about existing version and applies required migration.

score -1 · Answer 5 · edited May 16 '16 at 13:40

private static String DB_PATH = Environment.getDataDirectory()+"/data/package-name/databases/";
private static String DB_NAME = "name_of_the_database"; // student.db

private void copyDataBase() throws IOException
{


    // Open your local db as the input stream
    InputStream myInput = myContext.getAssets().open(DB_NAME);

    // Path to the just created empty d inb
    String outFileName = DB_PATH + DB_NAME;

    // Open the empty db as the output stream
    OutputStream myOutput = new FileOutputStream(outFileName);

    // transfer bytes from the inputfile to the outputfile
    byte[] buffer = new byte[1024];
    int length;
    while ((length = myInput.read(buffer)) > 0) 
    {
        myOutput.write(buffer, 0, length);
    }
    // Close the streams
    myOutput.flush();
    myOutput.close();
    myInput.close();

    //Copy successful
    outFileName = DB_PATH + DB_SUCCESS;
    myOutput = new FileOutputStream(outFileName);
    myOutput.write(1);
    myOutput.flush();
    myOutput.close();
}

score -2 · Answer 6 · answered Mar 24 '15 at 05:08

package com.example.sqllite_db;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabseAdapter extends SQLiteOpenHelper

{
    public static String DatabaseName="NewDatabase";
    public static int DatabaseVersion=1;

    public DatabseAdapter(Context context) 
    {
        super(context,DatabaseName,null,DatabaseVersion);
        // TODO Auto-generated constructor stub
    }

    /*public static String TableUser="tb1";
    public static String keyId="id";
    public static String keyName="name";
    public static String keyPass="pass";*/


    @Override
    public void onCreate(SQLiteDatabase db) 
    {
        // TODO Auto-generated method stub
        String createQuery="create table tb1(name text,pass text);";
        db.execSQL(createQuery);
        //String createQuery1="create table tb2(pass text);";//("+keyName+"text,"+keyPass+"text);"; 
        //db.execSQL(createQuery1);


    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) 
    {
        // TODO Auto-generated method stub
        db.execSQL("drop table tb1");
        onCreate(db);
        //db.execSQL("drop table tb2");
        //onCreate(db);

    }

}

drop the database in on Upgrade method then again call on Create method in it and your database will update when you upload new database.

Doing this will delete all the data that was stored by application earlier. Its a very Bad design. — Roadblock, Mar 24 '15 at 07:05

Updating SQLite database when releasing new version of the android app on the store

6 Answers6