1

I have template database in /assets directory. This database has to be copied to the main app's database which resides in the private storage area.

Now, all samples I've seen on the Internet seem very odd to me. Namely, they all extend SQLiteOpenHelper and then create initialization and copy methods, but leave onCreate and onUpgrade methods empty.

This does not look like a proper way, and yet I've seen it as an answer in multiple threads on StackOverflow as well on Internet blogs.

Is this really a proper way? Since I thought of doing coping from within onCreate (within a class that extended SQLiteOpenHelper) in a way that I first create a database that matches the template database and then I populate it with data from the template database.

sandalone
  • 41,141
  • 63
  • 222
  • 338
  • 1
    "Is this really a proper way?" -- if it is the code that I am used to seeing, it is garbage. `SQLiteAssetHelper` is a far better solution. – CommonsWare May 10 '14 at 10:39
  • @CommonsWare Yes, I was not moving away from a class extending `SQLiteOpenHelper` just forgot to clarify that in my idea (edited it now). Also all samples I saw were using the class extending `SQLiteOpenHelper` but all such samples left `onCreate` and `onUpgrade` overridden methods empty. Do you have any sample on this issue? – sandalone May 10 '14 at 10:47
  • 1
    For an example, [check out the source I referenced](https://github.com/jgilfelt/android-sqlite-asset-helper/blob/master/library/src/main/java/com/readystatesoftware/sqliteasset/SQLiteAssetHelper.java). Method `createOrOpenDatabase()` starting line 379. That is where the "heavy lifting" is done. – Richard Le Mesurier May 10 '14 at 10:50

3 Answers3

4

I suggest the SQLiteAssetHelper library from:

It has become the standard for this technique, and does all the hard work for you.

And integration into your project is as simple as including 1 jar file.

For example, here is my database helper class - as you can see it inherits from SQLiteAssetHelper, but there is no code I had to write in order to get the database copied from the /assets directory. That is all handled for me.

public class DbHelper extends SQLiteAssetHelper
{
    private static final String DATABASE_NAME = "NameOfDbFile.sqlite";
    private static final int DATABASE_VERSION = 2;

    public DbHelper(Context context)
    {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);

        // delete old database when upgrading
        setForcedUpgrade(DATABASE_VERSION);
    }


// ... and below a basic query as if it was any old SQLiteOpenHelper subclass

    public Cursor getUsers()
    {
        SQLiteDatabase db = getReadableDatabase();
        SQLiteQueryBuilder qb = new SQLiteQueryBuilder();

        qb.setTables(User.TABLE);
        Cursor c = qb.query(db, User.COLUMNS, null, null, null, null, null);

        c.moveToFirst();
        return c;
    }

    // etc...
}

I was hesitant at first to use a library for this, but it is really simple to use.

However, if you want to redo the work yourself, then you can take a look at how this library does it, since it is open-source on Github.

Richard Le Mesurier
  • 29,432
  • 22
  • 140
  • 255
  • 1
    +1, though you should not need your `onUpgrade()`, since it is not doing anything. – CommonsWare May 10 '14 at 10:40
  • @CommonsWare I know... :) from a "work in progress" project. Good point tho - I've removed it from the answer. – Richard Le Mesurier May 10 '14 at 10:43
  • 1
    @RichardLeMesurier WOW! You really rocked `SQLiteOpenHelper` :). I have never seen so extensive database child class. You're using a singleton, if I am not wrong? – sandalone May 10 '14 at 10:58
  • @sandalone Yes, it is a drop-in replacement for `SQLiteOpenHelper`. That is why it has become the standard. Singleton or not, depending on the use. Enjoy it. – Richard Le Mesurier May 10 '14 at 11:08
0

Personally rather than using a pre-populated db in the assets folder I would create an xml file with the initial data and write an intentService (http://code.tutsplus.com/tutorials/android-fundamentals-intentservice-basics--mobile-6183) to read that xml and populate your main db on first start.

Now, all samples I've seen on the Internet seem very odd to me. Namely, they all extend SQLiteOpenHelper and then create initialization and copy methods, but leave onCreate and onUpgrade methods empty.

Nothing odd about that at all. All you will be doing with this asset db is copying data from the assets db to the applications db so why would you need any other methods?

jamesc
  • 12,423
  • 15
  • 74
  • 113
  • 1
    Unfortunately there is no way I use XML since I get template database from the client. Converting it to XML would be bad thing to do. – sandalone May 10 '14 at 10:50
  • @sandalone Then the SqliteAssetHelper is the way to go in an IntentService – jamesc May 10 '14 at 10:53
  • 1
    At this point, the SQLite database file format seems to be stable -- it has not changed in years. Running `INSERT` statements in transactions will be substantially slower than simply copying a database. "Nothing odd about that at all" -- having a concrete subclass of `SQLiteOpenHelper` that is used directly and does not use `onCreate()` and `onUpgrade()` is a massive code smell. The *complete and entire point* behind `SQLiteOpenHelper` is to leverage `onCreate()` and `onUpgrade()`. If you will not use those methods, just extend `Object`, not `SQLiteOpenHelper`. – CommonsWare May 10 '14 at 11:04
  • @CommonsWare What's the point of providing functionality that won't be used? this is a one off call to transfer data from one database to another that will never be used again. Copying the physical database file would be something to avoided and providing an xml file exported from the original database provides no end of upgrade options if needed. – jamesc May 10 '14 at 13:57
  • 1
    @jamesw: "What's the point of providing functionality that won't be used?" -- I completely agree. This is why talented developers go with a solution (`SQLiteAssetHelper`) that offers the best end-user performance for the least code to maintain. The only reason not to do that is because you want to offer some functionality that `SQLiteAssetHelper` does not support. For example, if your proposed XML also can come from a Web service, to replace data outside of app updates, then it is worth investing in a parse-the-XML-and-manually-modify-the-database solution, because you will be reusing it. – CommonsWare May 10 '14 at 14:11
  • @CommonsWare I'm getting lost now. Using SQLiteAssetHelper is exactly what I suggested in my comment so what's your point? – jamesc May 10 '14 at 14:26
  • @jamesw: My original comment was with regards to your answer, where you suggested the XML solution. My point is that while there are cases where the XML solution may be worthwhile, it should not be the starting point, as suggested in your answer and your first two comments to that answer. – CommonsWare May 10 '14 at 14:34
  • @CommonsWare Ahh. I was just offering a potentially viable alternative. – jamesc May 10 '14 at 14:37
  • @jamesw: OK. I agree that it's potentially viable. – CommonsWare May 10 '14 at 14:38
0

well, i am not soo sure i understand what do u need, and regarding Richard Le Mesurier answer, if this code copys the db from assets.. its nice

but if i were you i will include a text file in my RAW, which contains dump data for the db

INSERT INTO ... (...
INSERT INTO ... (...
INSERT INTO ... (...

and so...

then at onCreate() of the dbHelper, i would create tables as usual, then execute the sql statements in the RAW file.

onUpgrade() is not needed now, for next versions, you will use it, if you included more data in another RAW file even you can inclide DELETE statements... or just modify the db by queries hardcoded at onUpgrade()

Yazan
  • 6,074
  • 1
  • 19
  • 33