How can I clear an SQLite database each time I start my application?

Question

I want my SQLite database instance to be wiped away when the program starts.

What I tried was make a method on my class MyDBAdapter.java like this:

public class MyDbAdapter {
    private static final String TAG = "NotesDbAdapter";
    private DatabaseHelper mDbHelper;
    private SQLiteDatabase mDb;
    private static final String DATABASE_NAME = "gpslocdb";
    private static final String PERMISSION_TABLE_CREATE = "CREATE TABLE permission ( fk_email1 varchar, fk_email2 varchar, validated tinyint, hour1 time default '08:00:00', hour2 time default '20:00:00', date1 date, date2 date, weekend tinyint default '0', fk_type varchar, PRIMARY KEY  (fk_email1,fk_email2))";
    private static final String USER_TABLE_CREATE = "CREATE TABLE user ( email varchar, password varchar, fullName varchar, mobilePhone varchar, mobileOperatingSystem varchar, PRIMARY KEY  (email))";

    private static final int DATABASE_VERSION = 2;
    private final Context mCtx;
    private static class DatabaseHelper extends SQLiteOpenHelper {

        DatabaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            db.execSQL(PERMISSION_TABLE_CREATE);
            db.execSQL(USER_TABLE_CREATE);
        }

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

    /**
     * Constructor - takes the context to allow the database to be
     * opened/created
     * 
     * @param ctx the Context within which to work
     */
    public MyDbAdapter(Context ctx) {
        this.mCtx = ctx;
    }

    /**
     * Open the database. If it cannot be opened, try to create a new
     * instance of the database. If it cannot be created, throw an exception to
     * signal the failure
     * 
     * @return this (self reference, allowing this to be chained in an
     *         initialization call)
     * @throws SQLException if the database could be neither opened or created
     */
    public MyDbAdapter open() throws SQLException {
        mDbHelper = new DatabaseHelper(mCtx);
        mDb = mDbHelper.getWritableDatabase();
        return this;
    }

    public void close() {
        mDbHelper.close();
    }

    public long createUser(String email, String password, String fullName, String mobilePhone, String mobileOperatingSystem) 
    {
        ContentValues initialValues = new ContentValues();
        initialValues.put("email",email);
        initialValues.put("password",password);
        initialValues.put("fullName",fullName);
        initialValues.put("mobilePhone",mobilePhone);
        initialValues.put("mobileOperatingSystem",mobileOperatingSystem);
        return mDb.insert("user", null, initialValues);
    }


    public Cursor fetchAllUsers() {

        return mDb.query("user", new String[] {"email", "password", "fullName", "mobilePhone", "mobileOperatingSystem"}, null, null, null, null, null);
    }

    public Cursor fetchUser(String email) throws SQLException {

        Cursor mCursor = mDb.query(true, "user", new String[] {"email", "password", "fullName", "mobilePhone", "mobileOperatingSystem"}
            , "email" + "=" + email, null, null, null, null, null);
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;
    }
    public List<Friend> retrieveAllUsers() 
    {
        List <Friend> friends=new ArrayList<Friend>();

        Cursor result=fetchAllUsers();

        if( result.moveToFirst() ){
            do{
                //note.getString(note.getColumnIndexOrThrow(NotesDbAdapter.KEY_TITLE)));
                friends.add(new Friend(result.getString(result.getColumnIndexOrThrow("email")), "","","",""));
            }while( result.moveToNext() );
        }


        return friends;
    }

}

What is the best way to do this?

Answer 1

Beside onCreate() and onUpgrade() you can override onOpen(). Drop all tables there and call onCreate().

public class MyApplication extends Application {
    protected static final String           LOG_TAG = "MyApplication";

    private static DatabaseAdapter          mDb;

    private static MyApplication    mInstance;

    /**
     * @return The instance of the database adapter.
     */
    public static DatabaseAdapter getDatabaseAdapter() {
        return mDb;
    }

    /**
     * @return The instance of the application.
     */
    public static Context getInstance() {
        return mInstance;
    }

    @Override
    public void onCreate() {
        super.onCreate();
        Log.w(LOG_TAG, "Application::onCreate");
        mInstance = this;
        mDb = new DatabaseAdapter();
    }

    @Override
    public void onTerminate() {
        // Close the internal db
        getDatabaseAdapter().close(DatabaseAdapter.INTERNAL);

        Log.e(LOG_TAG, "::onTerminate::");
        super.onTerminate();
    }
}

The advantage of subclassing Application is that this will be called always when your application is started or terminated. Independent of the activity that is started. Global operations like open/close a database should be placed here.

Documentation:

Base class for those who need to maintain global application state. You can provide your own implementation by specifying its name in your AndroidManifest.xml's tag, which will cause that class to be instantiated for you when the process for your application/package is created.