CODEWITHSUNDEEP
androidsqlsqlite
Eric
Eric

Reputation: 137

Update Sqlite column to remove missing numbers

I have an Sqlite table that has a column for play_order.
If I delete an item is there a command to reorder the data to remove the now missing number from the column?

For example, say the table looks like this:

_id | play_order
----------------
1   | 0
2   | 1
3   | 2
4   | 3
5   | 4

I delete _id 3, I'd like to reorder the play_order so _id 4 now has a play_order of 2 and _id 5 has 3 etc...

I can do this in Java (Android), but would rather do it in SQL if possible.

I've tried using a fake row id, but the data is all over the table and can be full of other random data at any point.

I've also thought of doing a subquery and getting all the data, but don't know if I can loop over it or something to set the the order.

As far as Android goes, I've looked into doing a "applybatch" on the contentresolver, but can't get that to work either.

Upvotes: 0

Views: 195

Answers (3)

Karakuri
Karakuri

Reputation: 38595

Playlists.Members has a static method moveItem() which should take care of updating the play orders in the playlist. If you know the size of the playlist, try first moving the item to the end of the playlist, then delete the item.

private boolean deleteFromPlayList(long playlistId, int position, int playlistSize) {
    ContentResolver cr = getContentResolver(); // or pass it in method args
    // I'm assuming play order starts at 1, but if it starts at 0, 
    // then change this check accordingly
    if (position != playlistSize) {
        // this will return true on success, in case you want to check that it moved
        Playlists.Members.moveItem(cr, playlistId, position, playlistSize);
    }
    return cr.delete(...) > 0;
}

One thing to note is that moveItem() will result in a Uri notification when it moves the item, so if you have a Loader or something listening for that, it will get triggered. This may or may not be an issue. You might be able to avoid this by using applyBatch(), in which case you'll have to build the move operation yourself. Based on the source code around line 1653, it should be an update operation.

ArrayList<ContentProviderOperation> ops = new ArrayList<ContentProviderOperation>(2);
Uri moveUri = Playlists.Members.getContentUri("external", playlistId)
        .buildUpon()
        .appendPath(Integer.toString(position))
        .appendQueryParameter("move", "true")
        .build();
ops.add(ContentProviderOperation.newUpdate(moveUri)
        .withValue(Playlists.Members.PLAY_ORDER, playlistSize)
        .build());
Uri deleteUri = Playlists.Members.getContentUri("external", playlistId)
        .buildUpon()
        .appendPath(Long.toString(playlistMemberId))
        .build();
ops.add(ContentProviderOperation.newDelete(deleteUri).build());
contentResolver.applyBatch(MediaStore.AUTHORITY, ops);

Upvotes: 1

Karakuri
Karakuri

Reputation: 38595

Degen Sharew's answer works fine, but here's an alternative:

In your SQLiteOpenHelper, when you create your database, run this statement:

db.execSQL("CREATE TRIGGER trigger_compress_play_order" +
        " AFTER DELETE ON " + TABLE_NAME + " BEGIN " + 
        " UPDATE " + TABLE_NAME + 
        " SET " + PLAY_ORDER_COLUMN + " = " + PLAY_ORDER_COLUMN + " - 1" + 
        " WHERE " + PLAY_ORDER_COLUMN + " > OLD." + PLAY_ORDER_COLUMN + "; END;");

This will compress the values as you desire no matter how many rows you delete at any time. You don't even have to run a statement after the delete, this does it automatically.

Upvotes: 4

dsharew
dsharew

Reputation: 10665

Execute this update statement after you deleted the record ( but you need to store play_order of the deleted record before deleting it). 

update yourTableName set play_order = play_order - 1 where play_order > play_order_deleted;

yourTableName => Name of the table
play_order_deleted => The play_order of the record you deleted.

Upvotes: 1

Related Questions