Issues when using sqlite db copied from assets folder on Android

Although we are generally getting favorable reviews, the following errors are piling up and giving us grief:

java.lang.RuntimeException: Unable to start activity ComponentInfo{com.svs.missions.MissionsGalleryView}: android.database.sqlite.SQLiteException: no such table: missions: , while compiling: SELECT * FROM missions WHERE banner is not null order by title COLLATE NOCASE
at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2787)
at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2803)
at android.app.ActivityThread.access$2300(ActivityThread.java:135)
at android.app.ActivityThread$H.handleMessage(ActivityThread.java:2136)
at android.os.Handler.dispatchMessage(Handler.java:99)
at android.os.Looper.loop(Looper.java:144)
at android.app.ActivityThread.main(ActivityThread.java:4937)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:521)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:868)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:626)
at dalvik.system.NativeStart.main(Native Method)
Caused by: android.database.sqlite.SQLiteException: no such table: missions: , while compiling: SELECT * FROM missions WHERE banner is not null order by title COLLATE NOCASE
at android.database.sqlite.SQLiteCompiledSql.native_compile(Native Method)
at android.database.sqlite.SQLiteCompiledSql.compile(SQLiteCompiledSql.java:91)
at android.database.sqlite.SQLiteCompiledSql.<init>(SQLiteCompiledSql.java:64)
at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:80)
at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:46)
at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:53)
at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1409)
at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1293)
at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1248)
at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1328)
...

It seems that not all the tables get created when copying the db from the assets folder. Here is one feedback message from a user in the market panel:

“Whenever I click on "missions" the app force closes. all other links seem ok.”

So it is really hit and miss. For some users, it appears none of the tables get copied.

I’m tempted to just rip out the database copy altogether and create the db on launch, but then I still cannot guarantee the db will be created (and won't get any of the metadata that ships with the app, unless we download it on launch, lots of data btw). Seems a little silly to me though. So many different platforms to support. So disjointed.

Funny that I call table creation code on every launch just to be sure the db tables exist, and it doesn’t fix it (snippet here):

   this.openDB();
    db.execSQL("CREATE TABLE  if not exists missions (_id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT, description TEXT, created TEXT, orgHTML TEXT, UNIQUE(id) ON CONFLICT IGNORE);");
...
this.closeDB();

I found this:

http://www.anddev.org/networking-database-problems-f29/missing-table-in-sqlite-with-specific-version-of-desire-hd-t50364.html

and am already applying the workaround, but still getting error reports.

I don’t know for sure if it is only on HTC devices, just following the lead of that forum post above.

Background:

The db is in the assets folder. It is 2.8mb large, and I assign a .mp3 extension to avoid compression so that it will copy (fix an old issue that is well known).

One thing I haven't added yet is a check for free space, but that will be next. I may just create the whole db empty if there is not enough free space I suppose. But if it runs out of free space when copying the assets folder db, the db should be corrupted I would think.

BTW: Oncreate and Onupgrade are currently empty:

@Override
public void onCreate(SQLiteDatabase sqLiteDatabase) {
  }

@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
}

This works on all of our test devices:

HTC Aria, 2.1, small footprint, 16mb heap

Original Droid, 2.2.1

Samsung Galaxy, 2.2

Asus tablet (don’t have model), 3.0

Xoom, 3.1

The DB class extends sqliteopenhelper:

public class DBManager extends SQLiteOpenHelper {

Here is the code used (Since none of our users have contacted us yet, we don't know what the Log.e writes are, unfortunately):

  ... 

    private static final String DB_NAME = "db.mp3";
    private static final String DB_PATH = "/data/data/com.svs/databases/";

     ... 

    public boolean createNewDatabaseFromAsset() {
        File file = new File(DB_PATH , DB_NAME);
        if (file.exists()) {
            return true;
        }
        SQLiteDatabase db_Read = null;
        db_Read=this.getReadableDatabase();
         db_Read.close(); 

         try {
            InputStream assetsDB = this.context.getAssets().open(DB_NAME);
            OutputStream dbOut = new FileOutputStream(DB_PATH + DB_NAME);

            byte[] buffer = new byte[1024];
            int length;
            while ((length = assetsDB.read(buffer)) > 0) {
                dbOut.write(buffer, 0, length);
            }

            dbOut.flush();
            dbOut.close();
            assetsDB.close();
            this.buildTables();


            Log.i(TAG, "New database created...");
            return true;
        } catch (IOException e) {
            Log.e(TAG, "IO Error. Could not create new database...");
            e.printStackTrace();
               this.buildTables();
            return false;
        } catch (SQLiteException e) {
            Log.e(TAG, "Database Locked. Could not create new database...");
         开发者_开发问答   e.printStackTrace();
               this.buildTables();
            return false;
        }

   }

    public DBManager openDB() {
           if (!createNewDatabaseFromAsset())
            return null;
           if (db==null){
               dbManager = new DBManager(context);
            db = dbManager.getWritableDatabase();
           }
        return this;
    }

---

I thought I would update this answer since it's a bit old. On tablets at least, as of android API 19, you can have multiple users. Those accounts on a Nexus 7 at least go into the directory, /data/users/10/

You have to do something like the following now:

InputStream input = this.context.getAssets().open(DATABASE_NAME);
File dbFile = context.getDatabasePath(DATABASE_NAME);
dbFile.getParentFile().mkdir();
dbFile.createNewFile();
OutputStream output = new FileOutputStream(dbFile.getAbsolutePath());

So you can't assume the path for your app is located statically at /data/data//databases.

---

Sorry, left this one hanging.

So I finally just decided to check and delete/create any table that didn't exist in the copied db on first launch (copying remotely any data that usually ships in the package).

It's a hack I know, but at least it gets past the issue we could not resolve otherwise.

---

Take a look at this article here, it has a good create/copy db method which has always worked for me. Also try chopping up the database into <1mb chunks then put back together - thats what i do when dealing with large databases. Example:

static private void CopyDatabase(Context Ctxt, File DBFile) throws IOException
{
AssetManager am = Ctxt.getAssets();
OutputStream os = new FileOutputStream(DBFile);
DBFile.createNewFile();
byte []b = new byte[1024];
int i, r;
String []Files = am.list("");
Arrays.sort(Files);
for(i=1;i<10;i++)
{
       String fn = String.format("%d.db", i);
    if(Arrays.binarySearch(Files, fn) < 0)
           break;
    InputStream is = am.open(fn);
    while((r = is.read(b)) != -1)
        os.write(b, 0, r);
    is.close();
}
os.close();
}

Hope this helps!