CODEWITHSUNDEEP
androidandroid-sqliteandroid-assets
Paul Holden
Paul Holden

Reputation: 868

Cannot Copy DB from assets/ in SQLiteOpenHelper

I have a pre-populated DB that I've created offline and put in the assets/ directory. I'm trying to copy it when SQLiteOpenHelper.onCreate() is called, but I'm running into serious trouble.

public void onCreate(SQLiteDatabase db) {
    importAssets();
}

When I write my code like this, I consistently get a no such table error when subsequently trying to get data out of the database. I did some mucking around and started inserting SQL statements into onCreate() as well as the call to importAssets(), for example:

public void onCreate(SQLiteDatabase db) {
    importAssets();
    db.execSQL("CREATE TABLE main_table (some_col);");
}

Now when I run the app, I get problems with a column not found error (the above schema doesn't match what the DB access code expects, it's missing a number of columns. On the second run of the app, android_metatdata get corrupted and then it all goes to hell. I've also tried with the SQL code before the importAssets() call, with similarly disasterous results.

So I'm starting to think that the file copy I'm doing in importAssets() is essentially getting truncated by onCreate(). Am I right? Is the only way to create a DB in onCreate() by directly using SQL? Can I not use this function to copy a ready-made DB from assets/?

I have confirmed that importAssets() is running correctly by using Log() calls. Here is the complete function:

private void importAssets() throws IOException
{  
    AssetManager am = mCtx.getAssets();
    InputStream in = am.open(DB_NAME);
    FileOutputStream out;
    byte[] buffer = new byte[1024];
    int len;
    File db;

    db = new File(DB_DIR + "/" + DB_NAME);

    // copy the DB from assets to standard DB dir created above
    out = new FileOutputStream(db);
    while ((len = in.read(buffer)) > 0)
    {
        out.write(buffer, 0, len);
    }

    out.flush();
    out.close();
    in.close();
    if (!db.exists())
    {
        Log.e(TAG, DB_DIR + "/" + DB_NAME + " does not exist");
    }
}

I've seen elsewhere the suggestion that I do the file copy inside the SQLiteOpenHelper constructor, but that means I'd have to manually check the presence of the file in importAssets() since the constructor gets called every time I use the DB. Also, onCreate() would be effectively useless for what I want to do, though I don't see any way to avoid that.

Upvotes: 2

Views: 1109

Answers (1)

Paul Holden
Paul Holden

Reputation: 868

I'm now convinced that my suspicion was right. The onCreate() function of SQLiteOpenHelper will create the DB file, specified in the name parameter of the call to the super class constructor. It then expects you to modify this DB file (add tables, data, alter tables, etc) from within the onCreate() function using the various DB libraries. Copying a file from somewhere else will just get truncated.

I don't know how or why it's doing this, but this makes onCreate() useless for 'creating' a DB by copying it intact from assets/ which is what I need. I've moved importAssets() to the constructor and added the appropriate check to not overwrite it if it's already there. With nothing in onCreate() at all it doesn't seem to disrupt what's going on elsewhere.

I think that SQLiteOpenHelper really needs the capability to run an SQLite script. This way I don't have to hard-code long chunks of SQL into my Java code, when all I really want to do is get a DB setup.

Upvotes: 2

Related Questions