I'm making a update of my app. In this update, I have to add a field in a table in my Sqlite DB.
Here is the file to manage a single instance of my DB:
DatabaseManager.java
public class DatabaseManager {
private AtomicInteger mOpenCounter = new AtomicInteger();
private static DatabaseManager instance;
private static SQLiteOpenHelper mDatabaseHelper;
private SQLiteDatabase mDatabase;
public static synchronized void initializeInstance(SQLiteOpenHelper helper) {
if (instance == null) {
instance = new DatabaseManager();
mDatabaseHelper = helper;
}
}
public static synchronized DatabaseManager getInstance() {
if (instance == null) {
throw new IllegalStateException(
DatabaseManager.class.getSimpleName()
+ " is not initialized, call initializeInstance(..) method first.");
}
return instance;
}
public synchronized SQLiteDatabase openDatabase() {
if (mOpenCounter.incrementAndGet() == 1) {
// Opening new database
mDatabase = mDatabaseHelper.getWritableDatabase();
}
return mDatabase;
}
public synchronized void closeDatabase() {
if (mOpenCounter.decrementAndGet() == 0) {
// Closing database
mDatabase.close();
}
}
Then, the main Db File is :
public class PdvHandler extends SQLiteOpenHelper {
private static final String TAG = "com.spmkt.mobile";
// All Static variables
// Database Version
public static final int DATABASE_VERSION = 2;
// Database Name
public static final String DATABASE_NAME = "mydb.db";
....
public void dropAndCreateMetrics() {
SQLiteDatabase db = DatabaseManager.getInstance().openDatabase();
db.execSQL("DROP TABLE IF EXISTS " + MetricsHandler.TABLE_METRICS);
db.execSQL(MetricsHandler.CREATE_TABLE_METRICS);
DatabaseManager.getInstance().closeDatabase();
}
}
then, I want to add a column in the alerta table :
AlertaHandler.java
public class AlertaHandler extends SQLiteOpenHelper {
public AlertaHandler(Context context, String name, CursorFactory factory,
int version) {
super(context, name, factory, version);
DatabaseManager.initializeInstance(this);
}
public AlertaHandler(Context context) {
super(context, PdvHandler.DATABASE_NAME, null,
PdvHandler.DATABASE_VERSION);
DatabaseManager.initializeInstance(this);
}
SQLiteDatabase db;
public static final String TABLE_ALERTAS = "alertas";
private static final String ALERTA_ID = "id";
private static final String ALERTA_CAPTURA_ID = "captura_id";
private static final String ALERTA_MENSAJE = "mensaje";
private static final String ALERTA_URGENT = "urgent"; // This is the column to add
private static final String ALERTA_SYNC = "sincronizado";
// private static final String ADD_URGENT_COLUMN =
// "ALTER TABLE "+TABLE_ALERTAS+" ADD COLUMN "+ +" ";
public static String CREATE_ALERTA = "CREATE TABLE " + TABLE_ALERTAS + "("
+ ALERTA_ID + " INTEGER " + "PRIMARY KEY AUTOINCREMENT,"
+ ALERTA_CAPTURA_ID + " INTEGER," + ALERTA_URGENT
+ " INTEGER DEFAULT 0 NOT NULL, " + ALERTA_MENSAJE + " TEXT" + ", "
+ ALERTA_SYNC + " INTEGER )";
public static String DROP_ALERTA = "DROP TABLE IF EXISTS '" + TABLE_ALERTAS+"'";
public ArrayList<Mensaje> getUnSincronizedMessage() {
ArrayList<Mensaje> lMensajes = new ArrayList<Mensaje>();
// Select All Query
Mensaje message;
String messageQuery = "SELECT * FROM " + TABLE_ALERTAS + " WHERE "
+ ALERTA_SYNC + "= 0";
SQLiteDatabase db = DatabaseManager.getInstance().openDatabase();
Cursor cursor = db.rawQuery(messageQuery, null);
// looping through all rows and adding to list
if (cursor.moveToFirst()) {
do {
message = new Mensaje();
message.setId(cursor.getInt(cursor.getColumnIndex(ALERTA_ID)));
message.setCapturaId(cursor.getInt(cursor
.getColumnIndex(ALERTA_CAPTURA_ID)));
message.setMessage(cursor.getString(cursor
.getColumnIndex(ALERTA_MENSAJE)));
int urgent = cursor
.getInt(cursor.getColumnIndex(ALERTA_URGENT));
message.setUrgent(urgent == 0 ? false : true);
message.setSincronizado(cursor.getInt(cursor
.getColumnIndex(ALERTA_SYNC)));
lMensajes.add(message);
} while (cursor.moveToNext());
}
DatabaseManager.getInstance().closeDatabase();
return lMensajes;
}
public void addMessage(Mensaje m) {
SQLiteDatabase db = DatabaseManager.getInstance().openDatabase();
ContentValues values = new ContentValues();
values.put(ALERTA_CAPTURA_ID, m.getCapturaId());
values.put(ALERTA_MENSAJE, m.getMessage());
values.put(ALERTA_URGENT, m.isUrgent() ? 1 : 0);
values.put(ALERTA_SYNC, m.getSincronizado());
// Inserting Row
db.insert(TABLE_ALERTAS, null, values);
DatabaseManager.getInstance().closeDatabase();
}
@Override
public void onCreate(SQLiteDatabase db) {
}
public boolean existsMensaje(Mensaje m) {
SQLiteDatabase db = DatabaseManager.getInstance().openDatabase();
String sql = "SELECT COUNT(*) FROM " + TABLE_ALERTAS + " WHERE "
+ ALERTA_ID + "=" + m.getId();
Cursor mCount = db.rawQuery(sql, null);
mCount.moveToFirst();
int count = mCount.getInt(0);
mCount.close();
DatabaseManager.getInstance().closeDatabase();
if (count == 0)
return false;
else
return true;
}
public void updateMessage(Mensaje m) {
SQLiteDatabase db = DatabaseManager.getInstance().openDatabase();
ContentValues values = new ContentValues();
values.put(ALERTA_ID, m.getId());
values.put(ALERTA_MENSAJE, m.getMessage());
values.put(ALERTA_SYNC, m.getSincronizado());
values.put(ALERTA_URGENT, m.isUrgent());
values.put(ALERTA_CAPTURA_ID, m.getCapturaId());
// updating row
db.update(TABLE_ALERTAS, values, ALERTA_ID + " = ?",
new String[] { String.valueOf(m.getId()) });
DatabaseManager.getInstance().closeDatabase();
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
int upgradeTo = oldVersion + 1;
while (upgradeTo <= newVersion) {
switch (upgradeTo) {
case 2:
try{
db.execSQL(DROP_ALERTA);
db.execSQL(CREATE_ALERTA);
Log.e(TAG,DROP_ALERTA);
Log.e(TAG,CREATE_ALERTA);
}catch (Exception e) {
Crashlytics.logException(e);
}
break;
}
upgradeTo++;
}
}
I read that onUpgrade will only run when getWritableDatabase runs, but I don't understand well what's going on...
Should I alter my tables in PdvHandler, because it is the file which define DB version? Should I alter my tables in each handler file? ( I have 1 handler x table = 10 handlers)
I'm a little bit lost ! Any suggestion would be appreciated !
