Android not paying attention to file changes?

Question

I'm running into a very frustrating bug.

I have a java class that reads in data from a file, and enters it into the database.

 package edu.uci.ics.android;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DbAdapter extends SQLiteOpenHelper{

    private static final String DATABASE_NAME = "mydb";
    private static final int DATABASE_VERSION = 1;
    private static final String TABLE_NAME = "fruits";
    private static final String FRUIT_ID = "_id";
    private static final String FRUIT_NAME = "name";
    private static final String FILE_NAME = "fruits.txt";
    private static final String CREATE_TABLE = "CREATE TABLE "+ TABLE_NAME + "("+FRUIT_ID+" integer primary key autoincrement, "+FRUIT_NAME+" text not null);";
    private SQLiteDatabase mDb;
    private Context mContext;

    public DbAdapter(Context ctx){
        super(ctx, DATABASE_NAME, null, DATABASE_VERSION);
        mContext = ctx;
        this.mDb = getWritableDatabase();
    }
    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_TABLE);
        // populate database
        try {
            BufferedReader in = new BufferedReader(new InputStreamReader(mContext.getAssets().open(FILE_NAME)));
            String line;

            while((line=in.readLine())!=null) {
                ContentValues values = new ContentValues();
                values.put(FRUIT_NAME, line);
                db.insert(TABLE_NAME, null, values);
            }
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS "+TABLE_NAME);
        onCreate(db);   
    }

    public Cursor fetchAll() {
        return mDb.query(TABLE_NAME, new String[] {FRUIT_NAME}, null, null, null, null, null);
    }

}

EDIT:

To be more clear, this is what fails:

When I change the database name variable, the table name variable, the program force closes, indicating that something went wrong. Why can't I change the name of the table I create?

When I make changes in the fruits.txt file, I don't see anything reflecting those changes at run-time. Why does this happen?

Answer 1

SQLiteOpenHelper.onCreate() will only get called automatically if the database does not exist, which will only happen once. After that, the database file exists on the device's internal storage so it is simply going to load up the version it has.

If you want to create a new database when the external file is changed, you need to either delete the current database file (manual process) or also change the current database version the helper is created with. When Android sees that the version SQLiteOpenHelper is created with varies from the current file in internal storage, it will call onUpgrade() to allow the existing database to be modified to match the new "version".

EDIT:

To clarify, when you create a database, a db file is created (and persisted) on the device's internal storage, separate from your application's assets. When you re-run your application, persisted data storage does not clear out (or else it wouldn't be "persisted" anymore) so that database file from the last run of your application still exists...with all the settings from when it was created.

When you make changes to the variables in this class, it doesn't somehow magically modify the database file that already exists on the device, so now you are looking for tables and databases that don't exist (probably where your crashes are coming from).

If you simply need to clear out the database so it will reflect changes you've made during development, just clear the database manually on the device by going into Settings -> Manage Applications -> {Application Name} -> Clear Data. This deletes persisted files so they can be re-created by your application the next time you launch it.

If, however, you need this to somehow be a feature where your application automatically recognizes changes you've made to a file in /assets and modifies or re-creates the database as a result, then look at my previous suggestions about using the upgrade mechanism built into SQLiteOpenHelper

HTH

Answer 2

When you change the database name or table name in your code, they no longer reflect the names in the database on the device, so you get a force close. During development, the easy thing is to just uninstall your application and then reinstall whenever you make incompatible changes like that. When changing your database schema from one released version to another, you need to increase the database version number and do the right thing in onUpgrade().

For example, right now, you have

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL("DROP TABLE IF EXISTS "+TABLE_NAME);
    onCreate(db);   
}

So when you change the table name in code from, say "fruits" to "veggies", onUpgrade() gets run, but table veggies doesn't exist, so it isn't dropped, and then you call onCreate(db) whith a conflicting shchema on top of the existing database. So you need to check oldVeresion and newVersion and do something more like

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    if (newVersion == 2 && oldVersion == 1) {
        db.execSQL("DROP TABLE IF EXISTS" + TABLE_NAME_V1);
        db.execSQL("CREATE TABLE "+ TABLE_NAME ...);
    }
}

If you're trying to change fruits.txt on the device, it won't work. That's how Android is designed: your assets never change, they're a read-only part of your APK. You need to write the fruit.txt file to the SD card if you want it to be able to change it.