How to shuffle database Table entries using cursors and swapping values

Question

This is a simple method to randomly shuffle certain database table entries based on a specific column value. Thanks to cbrulak's help, it now functions appropriately. It is not the most efficient by any means, but I thought I would just post it after the fix, as someone may find it somewhat useful.

/* The method will shuffle the specific entries in the table, based on column value */
public void shuffleDeck(int deck_id){

    int i, j, count;
    int loop1, loop2;
    int num;
    int id1, id2;
    Random rand = new Random();
    String front1, back1, front2, back2;


    /* Create two separate content values for swapping the values between the two rows */
    ContentValues updated_1 = new ContentValues();
    ContentValues updated_2 = new ContentValues();

    String[] columns = new String[]{CARD_ROWID, CARD_FRONT, CARD_BACK};

    /* Create two cursors pointing to the same query */
    Cursor cursor1 = Database.query(CARDS_TABLE, columns, DECK_ROWID + " = " + deck_id, 
            null, null, null, null);
    Cursor cursor2 = Database.query(CARDS_TABLE, columns, DECK_ROWID + " = " + deck_id, 
            null, null, null, null);

    cursor1.moveToFirst();
    cursor2.moveToFirst();

    int iRow = cursor1.getColumnIndex(CARD_ROWID);
    int iFront = cursor1.getColumnIndex(CARD_FRONT);
    int iBack = cursor1.getColumnIndex(CARD_BACK);

    /* Get total number of entries in the cursor */
    count = cursor1.getCount();

    /* Loop through  */
    for(i=1; i<=count; i++){
        num = rand.nextInt(count) + 1;

        /* Acquire the values from the current row that will be inserted into the randomly selected row */
        id1 = cursor1.getInt(iRow);
        front1 = cursor1.getString(iFront);
        back1 = cursor1.getString(iBack);

        /* Move to the next row in cursor2 for a random number of times */
        for (j=1; j<num; j++){

            cursor2.moveToNext();
            /* Fail safe to make sure it doesn't go out of bounds */
            if(cursor2.isLast())
                break;
        }

        /* Acquire the values from the random row */
        front2 = cursor2.getString(iFront);
        back2 = cursor2.getString(iBack);
        id2 = cursor2.getInt(iRow);

        /* Check to see if the second cursor is also pointing to the same position. */
        if(!(front1.equals(front2) && back1.equals(back2))){
            updated_1.put(CARD_FRONT, front2);
            updated_1.put(CARD_BACK, back2);
            updated_2.put(CARD_FRONT, front1);
            updated_2.put(CARD_BACK, back1);

            /* Call the database to UPDATE the values */
            Database.update(CARDS_TABLE, updated_1, CARD_ROWID + " = " + id1, null);
            Database.update(CARDS_TABLE, updated_2, CARD_ROWID + " = " + id2, null);

            /* Clear the content values */
            updated_1.remove(CARD_FRONT);
            updated_1.remove(CARD_BACK);
            updated_2.remove(CARD_FRONT);
            updated_2.remove(CARD_BACK);

            /* Requery the cursors */
            cursor1 = Database.query(CARDS_TABLE, columns, DECK_ROWID + " = " + deck_id, 
                    null, null, null, null);
            cursor2 = Database.query(CARDS_TABLE, columns, DECK_ROWID + " = " + deck_id, 
                    null, null, null, null);

            /* Move cursor1 to the same position it was, right before requerying it */
            for(loop1 = 1; loop1<=i; loop1++){
                cursor1.moveToNext();

                /* Fail save in case it tries to go out of bound */
                if(cursor1.isLast()) 
                    break;
            }
            /* Move cursor2 to the same position it was, right before requerying it */
            for(loop2 = 1; loop2<=j; loop2++){
                cursor2.moveToNext();

                /* Fail save in case it tries to go out of bound */
                if(cursor2.isLast()) 
                    break;
            }
        }
        /* Move the cursors to the new positions */
        cursor1.moveToNext();
        cursor2.moveToFirst();
    }
    cursor1.close();
    cursor2.close();
}

score 1 · Accepted Answer · edited May 23 '17 at 12:27

1

request a new cursor after the update(s).

See: Android - Can you update a Cursor for SQLite results?

And more important: http://developer.android.com/reference/android/database/Cursor.html#requery() (they say to request new ones)

Bonus points for you: use a content provider. Save you tons of headache.

edited May 23 '17 at 12:27

Community

1
1

answered Jan 15 '13 at 20:56

cbrulak

15,436
20
61
101

Well the thing is that I can see the cursors moving correctly through the different rows without a problem using the different calls. The problem is that the update queries do not update the values as they should. It's like they simply decline to update the values – Georgi Angelov Jan 15 '13 at 21:08
That is what I believe the problem is... I'll check that and get back to you – Georgi Angelov Jan 15 '13 at 21:18
1

cbrulak, thank you for your help! The requerying fixed everything. I really appreciate your help! – Georgi Angelov Jan 15 '13 at 21:40

How to shuffle database Table entries using cursors and swapping values

1 Answers1