I'm working on an Android app with an SQLite database.
I have a table called REMINDER (with 2 columns: REMINDER_ID and REMINDER_VALUE) and I am allowing the user to delete any record from that table. For reasons that would take a bit to explain, I need the REMINDER_ID column to always have sequential numbers. So, if the user deletes row 2, I need to eliminate that gap, so row 3 becomes row 2, row 4 becomes row 3, etc..
I wrote a method in my DBHelper class to do the update of the db whenever a row is deleted, but I'm not sure whether this is the best way to do it.
Below is the method to delete a row from the REMINDER table. As you can see, before I delete the row, I check whether it's the last row or not, because if it is I don't have to bother updating the ids.
public void deleteReminder(int reminder_id){
SQLiteDatabase db = this.getWritableDatabase();
String selectQuery = "SELECT MAX(" + COLUMN_REMINDER_ID + ") FROM " + REMINDERS_TABLE;
Cursor cursor = db.rawQuery(selectQuery, null);
int maxId = cursor.getInt(cursor.getColumnIndex(COLUMN_REMINDER_ID));
db.delete(REMINDERS_TABLE, COLUMN_REMINDER_ID + "= ?", new String[] {String.valueOf(reminder_id)});
if (reminder_id != maxId){
updateRemindersIds();
}
cursor.close();
db.close();
}
This is the method used for updating the id's:
public void updateRemindersIds(){
SQLiteDatabase db = this.getWritableDatabase();
String selectQuery = "SELECT * FROM " + REMINDERS_TABLE;
Cursor cursor = db.rawQuery(selectQuery, null);
ContentValues values = new ContentValues();
int new_id = 1;
if(cursor.moveToFirst()){
do{
values.put(COLUMN_REMINDER_ID, new_id);
int old_id = cursor.getInt(cursor.getColumnIndex(COLUMN_REMINDER_ID));
db.update(REMINDERS_TABLE, values, COLUMN_REMINDER_ID + "= ?", new String[] {String.valueOf(old_id)});
new_id++;
} while (cursor.moveToNext());
}
}
}
I haven't gotten to the point of testing it yet, but even if it works, I'm wondering whether there is a better way of doing this? My reason for asking is because I don't have much experience.
Thank you
EDIT: creating a method that implements Joachim Isaksson's solution:
public void updateRemindersIds(){
SQLiteDatabase db = this.getWritableDatabase();
String updateQuery = "UPDATE " + REMINDERS_TABLE + " SET " + COLUMN_REMINDER_ID + " = (" +
"SELECT COUNT(*) + 1 FROM " + REMINDERS_TABLE + " r " +
"WHERE " + REMINDERS_TABLE + "." + COLUMN_REMINDER_ID + "> r." + COLUMN_REMINDER_ID + ")";
db.execSQL(updateQuery);
db.close();
}
I suppose this is correct for Android / Java / SQLite?
As long as they have unique reminder id's, you could renumber the items using a single update;
UPDATE reminders SET reminder_id = (
SELECT COUNT(*)+1
FROM reminders r
WHERE reminders.reminder_id>r.reminder_id
);