Warm tip: This article is reproduced from stackoverflow.com, please click
android database sqlite version

Overwrite existing shipped Sqlite DB with the new DB version on my next Android App Update

发布于 2020-03-27 10:26:44

I would like to overwrite the existing DB which was shipped with my old app version, with newly fully populated DB in my next app update. However, the onUpgrade() is never gets called, though i have tried to change the DB_version while passing it to the SQLiteOpenHelper class.

public class DataBaseHelper extends SQLiteOpenHelper {

    private static Context mContext;
    private static String DB_PATH = "/data/data/<app Package>/databases/";;
    private static final String DBNAME = "DB.db";
    private static DataBaseHelper sInstance;
    private static final int version = 2;


    public static synchronized DataBaseHelper getInstance(Context context) {
        // Use the application context, which will ensure that you
        // don't accidentally leak an Activity's context.
        if (sInstance == null) {
            sInstance = new DataBaseHelper(context.getApplicationContext());
        }
        return sInstance;
    }

    public DataBaseHelper(Context context, String s){
        super(context, DBNAME, null, version);

    }

    private DataBaseHelper(Context context) {
        super(context, DBNAME, null, version);
        mContext = context;

        if (Build.VERSION.SDK_INT >= Build.VERSION_CODES.JELLY_BEAN_MR1) {
            DB_PATH = context.getApplicationInfo().dataDir + "/databases/";
        } else {
            DB_PATH = "/data/data/" + context.getPackageName() + "/databases/";
        }

        new Handler().post(new Runnable() {

            @Override
            public void run() {
                // If u want to Copy Database from Assets.
                try {
                    CopyAndCreateDataBase();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        });
    }


    // If database not exists copy it from the assets
    public void CopyAndCreateDataBase() throws IOException {
        boolean mDataBaseExist = checkDataBase();
        if (!mDataBaseExist) {
            this.getReadableDatabase();
            this.getWritableDatabase();
            this.close();
            try {
                // Copy the database from assests
                copyDataBase();
                String mPath = DB_PATH + DBNAME;
            } catch (IOException mIOException) {
                throw new Error("ErrorCopyingDataBase");
            }
        }
         //FOR DB TESTING PURPOSES
        else{
            Log.d("DB STATUS:", "Database Already Exists!!!!!!!!!");
        }
    }


    // Check that the database exists here: /data/data/yourpackage/databases/DatabaseName
    private boolean checkDataBase() {
        File dbFile = new File(DB_PATH + DBNAME);
        // Log.v("dbFile", dbFile + "   "+ dbFile.exists());
        return dbFile.exists();
    }

    // Copy the database from assets
    private void copyDataBase() throws IOException {
        InputStream mInput = mContext.getAssets().open(DBNAME);
        String outFileName = DB_PATH + DBNAME;
        OutputStream mOutput = new FileOutputStream(outFileName);
        byte[] mBuffer = new byte[1024];
        int mLength;
        while ((mLength = mInput.read(mBuffer)) > 0) {
            mOutput.write(mBuffer, 0, mLength);
        }
        Log.d("DB STATUS:", "Daatabase Created ----------------"); //FOR DB TESTING PURPOSES
        mOutput.flush();
        mOutput.close();
        mInput.close();
    }

    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {

    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
        Log.d("Database Versions:", "old:"+i+"\nnew:"+i1);

    }
}

I have tried many ways to trigger the onUpgrade() method like calling the getReadableDatabase() and getWritableDatabase(, but unfortunately it did work out. i want to overwrite the exiting db with the newly shipped one.

Questioner
Jawad AlZaabi
Viewed
85
分享
MikeT 2019-07-04 08:49

I believe that your issue (initial issue, please read the entire answer) may be using the handler. Certainly testing the available code results in unanticipated results (database is always reported as existing so never copies the db from the assets, if the database exists never calls the onUpgrade (my guess is that the database is already open due to implicit opens and thus no attempt is made to do the open processing)).

Changing to use :-

    try {
        CopyAndCreateDataBase();
    } catch (IOException e) {
        e.printStackTrace();
    }

    /*
    new Handler().post(new Runnable() {

        @Override
        public void run() {
            // If u want to Copy Database from Assets.
            try {
                CopyAndCreateDataBase();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    });
    */

Produces the anticipated results e.g. :-

07-04 06:55:38.836 4365-4365/aso.so56873021recopydb D/DBEXISTS: The database /data/user/0/aso.so56873021recopydb/databases/DB.db was found.
07-04 06:55:38.836 4365-4365/aso.so56873021recopydb D/DB STATUS:: Database Already Exists!!!!!!!!!
07-04 06:55:38.838 4365-4365/aso.so56873021recopydb D/Database Versions:: old:1
    new:2
  • Note the DBEXISTS message was added when testing the code to ascertain the issue.

The testing utilised the following code in an activity :-

public class MainActivity extends AppCompatActivity {


    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        DataBaseHelper mDBHlpr = DataBaseHelper.getInstance(this);
        Cursor csr = mDBHlpr.getWritableDatabase().query("sqlite_master",null,null,null,null,null,null);
        DatabaseUtils.dumpCursor(csr);
    }
}

Part 2 - Upgrading

In short there are issues in that even though you could call onUpgrade when the version is increased after fixing the previous issue as when onUpgrade is invoked the database has already been opened and copying the new database will effectively be undone as the old database will overwrite the newly copied database and thus the changes get lost.

Instead I suggest checking, copying and overwriting the database be actioned prior to instantiating the database helper e.g. in the getInstance method.

As such I'd suggest the following Database Helper (see comments for changes made) :-

public class DataBaseHelper extends SQLiteOpenHelper {

    private static Context mContext;
    //private static String DB_PATH = "/data/data/<app Package>/databases/"; //NOT NEEDED Contexts getDatabasePath used instead
    private static final String DBNAME = "DB.db";
    private static DataBaseHelper sInstance;
    private static final int version = 2;


    public static synchronized DataBaseHelper getInstance(Context context) {
        // Use the application context, which will ensure that you
        // don't accidentally leak an Activity's context.
        checkDB(context.getApplicationContext(),DBNAME,version); //<<<<<<<<<< do the stuff before DatabaseHelper instantiation
        if (sInstance == null) {
            sInstance = new DataBaseHelper(context.getApplicationContext());
        }
        return sInstance;
    }

    private DataBaseHelper(Context context) {
        super(context, DBNAME, null, version);
        mContext = context;
    }

    // Copy the database from assets
    //<<<<<<<<<< CHANGED TO USE getDatabasepath requiring Context to be passed
    private static void copyDataBase(Context context) throws IOException {
        InputStream mInput = context.getAssets().open(DBNAME);
        String outFileName = context.getDatabasePath(DBNAME).toString();
        OutputStream mOutput = new FileOutputStream(outFileName);
        Log.d("DBCOPY","DB Copy inititaed"); //<<<<<<<<<< ADDED FOR TESTING
        byte[] mBuffer = new byte[1024];
        int mLength;
        int bytescopied = 0;
        while ((mLength = mInput.read(mBuffer)) > 0) {
            mOutput.write(mBuffer, 0, mLength);
            bytescopied = bytescopied + mLength;
        }
        Log.d("DBCOPY", "Database copied. " + String.valueOf(bytescopied) + " bytes copied." ); //<<<<<<<<<< ADDED FOR TESTING
        Log.d("DB STATUS:", "Daatabase Created ----------------"); //FOR DB TESTING PURPOSES
        mOutput.flush();
        mOutput.close();
        mInput.close();
    }

    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {

    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
        //<<<<<<<<<< ONLY USE ME FOR SCHEMA CHANGES !!!!NOT!!!! new DB copy
        Log.d("Database Versions:", "old:"+i+"\nnew:"+i1);
    }

    //<<<<<<<<<< NEW 
    private static void checkDB(Context context, String dbname, int version) {
        int dbversion;
        // Note uses the Contexts getDatabasePath
        File dbfile = new File(context.getDatabasePath(dbname).toString());
        if (!dbfile.exists()) {
            //<<<<<<<<<< create the databases directory (or whatever it is the future)
            if (!dbfile.getParentFile().exists()) {
                dbfile.getParentFile().mkdirs();
            }
            //<<<<<<<<<< Now do the copy of the initial DB 
            //<<<<<<<<<< Note new install will copy the latest DB
            try {
                copyDataBase(context);
            } catch (IOException e) {
                e.printStackTrace();
                throw new Error("Error copying initial Database.");
            }
        } 

        // If the database file does exist then retrieve the version
        else {


            SQLiteDatabase db = SQLiteDatabase.openDatabase(context.getDatabasePath(dbname).toString(),null,SQLiteDatabase.OPEN_READWRITE);
            dbversion = db.getVersion();
            Log.d("DBVERSION","The stored database version is " + String.valueOf(dbversion));
            db.close();

            //<<<<<<<<<< if the database's version is less than the coded version then copy the DB
            //<<<<<<<<<< NOTE!!!! always assumes new version = new copy
            //<<<<<<<<<< IF NOt WANTED THEN DO SPECIFIC VERSION CHECKING 
            if (dbversion <  version) {

                //<<<<<<<<<<<< EXAMPLE skip copy on version 10 >>>>>>>>>>
                //<TODO> Remove example check if not needed
                if (dbversion < version && version == 10) {
                    return;
                }
                //<<<<<<<<<< For Android 9+ delete the -wal and -shm files if copying database
                if (new File(context.getDatabasePath(dbname).toString() + "-wal").exists()) {
                    new File(context.getDatabasePath(dbname).toString() + "-wal").delete();
                }
                if (new File(context.getDatabasePath(dbname).toString() + "-shm").exists()) {
                    new File(context.getDatabasePath(dbname).toString() + "-shm").delete();
                }
                try {
                    copyDataBase(context);
                } catch (IOException e) {
                    e.printStackTrace();
                    throw new Error("Error copying upgraded database");
                }
            }
        }
    }
}

Testing

The above has been tested. The testing was based upon two databases (entirely different) using the following in an activity :-

public class MainActivity extends AppCompatActivity {


    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        DataBaseHelper mDBHlpr = DataBaseHelper.getInstance(this);
        Cursor csr = mDBHlpr.getWritableDatabase().query("sqlite_master",null,null,null,null,null,null);
        DatabaseUtils.dumpCursor(csr);
    }
}

The following is the assets folder :-

enter image description here

  • The original database being OLDDB.db copied to DB.db. NEWDB.db will be the subsequent new database.

Testing was undertaken on two devices API 23 and API 28 (Oreo so Pie+ and WAL).

Stage 1

First the App was run with the files as above and version set to 1. Resulting in the log containing :-

07-04 10:20:32.120 8154-8154/aso.so56873021recopydb D/DBCOPY: DB Copy inititaed
07-04 10:20:32.120 8154-8154/aso.so56873021recopydb D/DBCOPY: Database copied. 36864 bytes copied.
07-04 10:20:32.120 8154-8154/aso.so56873021recopydb D/DB STATUS:: Daatabase Created ----------------
07-04 10:20:32.140 8154-8154/aso.so56873021recopydb I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@6e2c433
07-04 10:20:32.140 8154-8154/aso.so56873021recopydb I/System.out: 0 {
07-04 10:20:32.140 8154-8154/aso.so56873021recopydb I/System.out:    type=table
07-04 10:20:32.140 8154-8154/aso.so56873021recopydb I/System.out:    name=android_metadata
07-04 10:20:32.140 8154-8154/aso.so56873021recopydb I/System.out:    tbl_name=android_metadata
07-04 10:20:32.140 8154-8154/aso.so56873021recopydb I/System.out:    rootpage=3
07-04 10:20:32.140 8154-8154/aso.so56873021recopydb I/System.out:    sql=CREATE TABLE android_metadata (locale TEXT)
07-04 10:20:32.140 8154-8154/aso.so56873021recopydb I/System.out: }
07-04 10:20:32.140 8154-8154/aso.so56873021recopydb I/System.out: 1 {
07-04 10:20:32.140 8154-8154/aso.so56873021recopydb I/System.out:    type=table
07-04 10:20:32.140 8154-8154/aso.so56873021recopydb I/System.out:    name=room_master_table
07-04 10:20:32.140 8154-8154/aso.so56873021recopydb I/System.out:    tbl_name=room_master_table
07-04 10:20:32.140 8154-8154/aso.so56873021recopydb I/System.out:    rootpage=4
07-04 10:20:32.140 8154-8154/aso.so56873021recopydb I/System.out:    sql=CREATE TABLE room_master_table (id INTEGER PRIMARY KEY,identity_hash TEXT)
07-04 10:20:32.140 8154-8154/aso.so56873021recopydb I/System.out: }
..........
  • 36K copied
  • Note table room_master_table

Similar result on API 28 device :-

2019-07-04 10:22:33.082 4532-4532/aso.so56873021recopydb D/DBCOPY: DB Copy inititaed
2019-07-04 10:22:33.083 4532-4532/aso.so56873021recopydb D/DBCOPY: Database copied. 36864 bytes copied.
2019-07-04 10:22:33.083 4532-4532/aso.so56873021recopydb D/DB STATUS:: Daatabase Created ----------------
2019-07-04 10:22:33.088 4532-4532/aso.so56873021recopydb I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@2f7f7bc
2019-07-04 10:22:33.089 4532-4532/aso.so56873021recopydb I/System.out: 0 {

Stage 2

App was run again no changes results (i.e. no DB copy) :-

07-04 10:24:13.642 8244-8244/? D/DBVERSION: The stored database version is 1
07-04 10:24:13.644 8244-8244/? I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@8c743f0

and on API 28 device :-

2019-07-04 10:26:24.531 4620-4620/? D/DBVERSION: The stored database version is 1
2019-07-04 10:26:24.536 4620-4620/? I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@877f345

Stage 3

Files in assets folder changed to be :-

enter image description here

App rerun WITHOUT changing version :-

No Copy and original data :-

07-04 10:30:01.838 8369-8369/? D/DBVERSION: The stored database version is 1
07-04 10:30:01.840 8369-8369/? I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@8c743f0
07-04 10:30:01.840 8369-8369/? I/System.out: 0 {
07-04 10:30:01.840 8369-8369/? I/System.out:    type=table
07-04 10:30:01.840 8369-8369/? I/System.out:    name=android_metadata
07-04 10:30:01.840 8369-8369/? I/System.out:    tbl_name=android_metadata
07-04 10:30:01.840 8369-8369/? I/System.out:    rootpage=3
07-04 10:30:01.840 8369-8369/? I/System.out:    sql=CREATE TABLE android_metadata (locale TEXT)
07-04 10:30:01.840 8369-8369/? I/System.out: }
07-04 10:30:01.840 8369-8369/? I/System.out: 1 {
07-04 10:30:01.840 8369-8369/? I/System.out:    type=table
07-04 10:30:01.840 8369-8369/? I/System.out:    name=room_master_table

and on API 28 :-

2019-07-04 10:31:11.591 4757-4757/aso.so56873021recopydb D/DBVERSION: The stored database version is 1
2019-07-04 10:31:11.596 4757-4757/aso.so56873021recopydb I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@877f345
2019-07-04 10:31:11.596 4757-4757/aso.so56873021recopydb I/System.out: 0 {
2019-07-04 10:31:11.596 4757-4757/aso.so56873021recopydb I/System.out:    type=table
2019-07-04 10:31:11.596 4757-4757/aso.so56873021recopydb I/System.out:    name=android_metadata
2019-07-04 10:31:11.596 4757-4757/aso.so56873021recopydb I/System.out:    tbl_name=android_metadata
2019-07-04 10:31:11.597 4757-4757/aso.so56873021recopydb I/System.out:    rootpage=3
2019-07-04 10:31:11.597 4757-4757/aso.so56873021recopydb I/System.out:    sql=CREATE TABLE android_metadata (locale TEXT)
2019-07-04 10:31:11.597 4757-4757/aso.so56873021recopydb I/System.out: }
2019-07-04 10:31:11.597 4757-4757/aso.so56873021recopydb I/System.out: 1 {
2019-07-04 10:31:11.597 4757-4757/aso.so56873021recopydb I/System.out:    type=table
2019-07-04 10:31:11.597 4757-4757/aso.so56873021recopydb I/System.out:    name=room_master_table

Stage 4

version increased from 1 to 2 :-

07-04 10:38:42.679 8857-8857/? D/DBVERSION: The stored database version is 1
07-04 10:38:42.679 8857-8857/? D/DBCOPY: DB Copy inititaed
07-04 10:38:42.680 8857-8857/? D/DBCOPY: Database copied. 77824 bytes copied.
07-04 10:38:42.680 8857-8857/? D/DB STATUS:: Daatabase Created ----------------
07-04 10:38:42.683 8857-8857/? D/Database Versions:: old:1
    new:2
07-04 10:38:42.688 8857-8857/? I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@f2b3d69
07-04 10:38:42.688 8857-8857/? I/System.out: 0 {
07-04 10:38:42.688 8857-8857/? I/System.out:    type=table
07-04 10:38:42.688 8857-8857/? I/System.out:    name=android_metadata
07-04 10:38:42.688 8857-8857/? I/System.out:    tbl_name=android_metadata
07-04 10:38:42.688 8857-8857/? I/System.out:    rootpage=3
07-04 10:38:42.689 8857-8857/? I/System.out:    sql=CREATE TABLE android_metadata (locale TEXT)
07-04 10:38:42.689 8857-8857/? I/System.out: }
07-04 10:38:42.689 8857-8857/? I/System.out: 1 {
07-04 10:38:42.689 8857-8857/? I/System.out:    type=table
07-04 10:38:42.689 8857-8857/? I/System.out:    name=shops
07-04 10:38:42.689 8857-8857/? I/System.out:    tbl_name=shops
07-04 10:38:42.689 8857-8857/? I/System.out:    rootpage=4
07-04 10:38:42.689 8857-8857/? I/System.out:    sql=CREATE TABLE shops (_id INTEGER  PRIMARY KEY , shoporder INTEGER  DEFAULT 1000 , shopname TEXT , shopstreet TEXT , shopcity TEXT , shopstate TEXT , shopnotes TEXT )
07-04 10:38:42.689 8857-8857/? I/System.out: }
........
  • Note 76K copied and table is now shops

And on API 28 :-

2019-07-04 10:40:31.799 5010-5010/aso.so56873021recopydb D/DBVERSION: The stored database version is 1
2019-07-04 10:40:31.800 5010-5010/aso.so56873021recopydb D/DBCOPY: DB Copy inititaed
2019-07-04 10:40:31.802 5010-5010/aso.so56873021recopydb D/DBCOPY: Database copied. 77824 bytes copied.
2019-07-04 10:40:31.802 5010-5010/aso.so56873021recopydb D/DB STATUS:: Daatabase Created ----------------
2019-07-04 10:40:31.826 5010-5010/aso.so56873021recopydb D/Database Versions:: old:1
    new:2
...........

Stage 5

App run again (i.e. post copy already done) :-

07-04 10:41:53.653 8947-8947/? D/DBVERSION: The stored database version is 2
07-04 10:41:53.655 8947-8947/? I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@8c743f0
07-04 10:41:53.655 8947-8947/? I/System.out: 0 {
07-04 10:41:53.655 8947-8947/? I/System.out:    type=table
07-04 10:41:53.655 8947-8947/? I/System.out:    name=android_metadata
07-04 10:41:53.655 8947-8947/? I/System.out:    tbl_name=android_metadata
07-04 10:41:53.655 8947-8947/? I/System.out:    rootpage=3
07-04 10:41:53.655 8947-8947/? I/System.out:    sql=CREATE TABLE android_metadata (locale TEXT)
07-04 10:41:53.655 8947-8947/? I/System.out: }
07-04 10:41:53.655 8947-8947/? I/System.out: 1 {
07-04 10:41:53.655 8947-8947/? I/System.out:    type=table
07-04 10:41:53.655 8947-8947/? I/System.out:    name=shops
07-04 10:41:53.655 8947-8947/? I/System.out:    tbl_name=shops

And on API 28 :-

2019-07-04 10:42:41.296 5098-5098/aso.so56873021recopydb D/DBVERSION: The stored database version is 2
2019-07-04 10:42:41.306 5098-5098/aso.so56873021recopydb I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@877f345
2019-07-04 10:42:41.307 5098-5098/aso.so56873021recopydb I/System.out: 0 {
2019-07-04 10:42:41.308 5098-5098/aso.so56873021recopydb I/System.out:    type=table
2019-07-04 10:42:41.308 5098-5098/aso.so56873021recopydb I/System.out:    name=android_metadata
2019-07-04 10:42:41.308 5098-5098/aso.so56873021recopydb I/System.out:    tbl_name=android_metadata
2019-07-04 10:42:41.308 5098-5098/aso.so56873021recopydb I/System.out:    rootpage=3
2019-07-04 10:42:41.308 5098-5098/aso.so56873021recopydb I/System.out:    sql=CREATE TABLE android_metadata (locale TEXT)
2019-07-04 10:42:41.308 5098-5098/aso.so56873021recopydb I/System.out: }
2019-07-04 10:42:41.308 5098-5098/aso.so56873021recopydb I/System.out: 1 {
2019-07-04 10:42:41.308 5098-5098/aso.so56873021recopydb I/System.out:    type=table
2019-07-04 10:42:41.308 5098-5098/aso.so56873021recopydb I/System.out:    name=shops
2019-07-04 10:42:41.309 5098-5098/aso.so56873021recopydb I/System.out:    tbl_name=shops
Jawad AlZaabi 2019-07-04 09:08:30

Many Thanks for your prompt and very detailed answer ... i'll check it and come back to you

Jawad AlZaabi 2019-07-04 09:42:21

Thanks God ... you deserve a star ... Many Thanks for your help :)

Related issues

1
Connect to SQLite3 in PyCharm
2
Sqlite foreign key mismatch?
3
SQLite query results limitation
4
Can't setup Electron + Reactjs + sqlite on windows 10
5
create a table if not exist in sqlite3 python using variable
6
How to insert single column of info into prepopulated sqlite database
7
SQL: Querying between multiple sets of values?
8
I get an error when changing data in SQlite; Python
9
Inserting rows into different tables using forein key in SQLite3
10
What's the difference of normal index and FTS in SQLite