android SQLite best practices (how to SQLite, android sqlite guide tutorial)

Some info i feel obliged to persist.

Had lots of problems and knowledge gaps with android SQLite database, now all seems to fit in its places.

I will share the approach i came to in a few months time of debugging and error fixing.

1) To work with android SQLite you must create your own helper class extending SQLiteOpenHelper.

2) You should use a singleton pattern for this helper instance and get the instance where needed in Activity's onCreate method:

public static synchronized DBHelper getInstance(Context context) {
    // Use the application context, which will ensure that you

    // don't accidentally leak an Activity's context.       

    if (sInstance == null) {
        sInstance = new DBHelper(context.getApplicationContext());    

    }
    return sInstance;

}

3) Before each db interaction (insert, delete, etc) open the database: 
db = this.getWritableDatabase();

4) The helper class should have methods for fetching/persisting data. Methods using cursor must close the cursor after using it. Never close the database itself in this class.

5) Use transaction mode for looped interactions:

db = this.getWritableDatabase();

db.beginTransaction();

try {
    for (...) {
        db.insert(...);
    }

    db.yieldIfContendedSafely();    

    db.setTransactionSuccessful();

}catch(Exception exc){
    exc.printStackTrace();

}finally {
    db.endTransaction();

}

6) My approach to Cursors/queries:

db = this.getWritableDatabase();

Cursor c = db.rawQuery("Select * from TABLE where ID = ?",new String[] {id});
c.moveToPosition(-1);

while (c.moveToNext()) {
    String s = c.getString(c.getColumnIndex("col_name"));

}
c.close();

7) onUpgrade method - when you increment the database version, this method is  called. Mine looks like this:

public static final int DATABASE_VERSION = 238;

@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    dropAll(db); 

    makeDB(db);

}

private SQLiteDatabase dropAll(SQLiteDatabase db){
    db.execSQL("DROP TABLE IF EXISTS tasks");
    return db;
}
public static void makeDB(SQLiteDatabase db){
    db.execSQL(CREATE_TABLE_TASKS);
}
private static final String CREATE_TABLE_TASKS = "CREATE TABLE IF NOT EXISTS tasks(user VARCHAR, ID INTEGER PRIMARY KEY AUTOINCREMENT);";

8) onCreate method:

@Overridepublic void onCreate(SQLiteDatabase db) {
    makeDB(db);

}

9) Working with database is very fast, performance drops might be caused by something else usually, so put that code in AsyncTask.