Search code examples
javaandroidsqliteandroid-sqlite

Moving a row in sqlite (Position column)

So, I am currently using the DragSortListView with a SQLite database. I want to resort my database whenever im dragging an item of the ListView to a new position. Therefore I wrote a piece of code that should manage it. But if I'm moving something from a position below to a higher one, it is strangely sorted. I already looked on this code for hours and asked some friends, but I never found the solution.

Code

Method in the DatabaseHelper

public void moveValues(int from, int to, String sammlung){
    SQLiteDatabase sqLiteDatabase = this.getWritableDatabase();
    System.out.println(from + " - " + to);
    if(from < to){
        //This part is working properly
        Cursor cursor = sqLiteDatabase.rawQuery("SELECT ID FROM " + TABLE_NAME + " WHERE POSITION IS " + "\"" + from + "\" AND SAMMLUNG IS " + "\"" + sammlung + "\"", null);
        for (int i = from+1; i < to+1; i++){
            switchPositionValue(i, i-1);
        }
        ContentValues contentValues = new ContentValues();
        contentValues.put(COLUMN_5, to);
        cursor.moveToFirst();
        sqLiteDatabase.update(TABLE_NAME, contentValues, "ID=?", new String[]{cursor.getInt(0)+""});
        cursor.close();
    }else{
        //This is the not working part
        Cursor cursor = sqLiteDatabase.rawQuery("SELECT ID FROM " + TABLE_NAME + " WHERE POSITION IS " + "\"" + from + "\" AND SAMMLUNG IS " + "\"" + sammlung + "\"", null);
        for (int i = from-1; i > to-1; i--){
            switchPositionValue(i, i+1);
        }
        ContentValues contentValues = new ContentValues();
        contentValues.put(COLUMN_5, to);
        cursor.moveToFirst();
        System.out.println(cursor.getInt(0));
        //The next line destroys it. But I dont know how to fix it or what exactly is not working
        sqLiteDatabase.update(TABLE_NAME, contentValues, "ID=?", new String[]{cursor.getInt(0)+""});
        cursor.close();
    }
}

The parameters are: from: The position it's taken away from to: The position it's taken to sammlung: I saving all the items of different collections in one database. So it's only there to sort out only specific items.

Method to change the value of a row with a specific position

public void switchPositionValue(int before, int after){
    SQLiteDatabase sqLiteDatabase = this.getWritableDatabase();
    ContentValues contentValues = new ContentValues();
    contentValues.put(COLUMN_5, after);
    sqLiteDatabase.update(TABLE_NAME, contentValues, "POSITION=?", new String[]{before+""});
}

Demonstration

Demonstration

So if you've got any idea why its behaving this strange or do know a better approach to moving a row in a database, I would really appreciate your help.

Solution

  • Android other way SQL one (COLUMN_5 is POSITION column)

    public void moveValues(int from, int to) {
    SQLiteDatabase sqLiteDatabase = this.getWritableDatabase();
    String query =
            "WITH p(from_position,to_position,max_position,min_position) AS (" +
                    "SELECT " +
                    "?," +
                    "?," +
                    "(SELECT max(" + COLUMN_5 + ") FROM " + TABLE_NAME + ")," +
                    "(SELECT min(" + COLUMN_5 + ") FROM " + TABLE_NAME + ")), " +

                    "updates AS(" +
                    "SELECT " + TABLE_NAME + ".id," + COLUMN_5 + " AS current, " + COLUMN_5 + " + 1 AS proposed " +
                    "FROM " + TABLE_NAME + " " +
                    "WHERE " + COLUMN_5 + " >= (SELECT to_position FROM p) " +
                    "AND " + COLUMN_5 + " < (SELECT from_position FROM p) " +

                    "UNION SELECT " + TABLE_NAME + ".id," + COLUMN_5 + " AS current, " + COLUMN_5 + " -1 AS proposed " +
                    "FROM " + TABLE_NAME + " " +
                    "WHERE " + COLUMN_5 + " <= (SELECT to_position FROM p) " +
                    "AND " + COLUMN_5 + " > (SELECT from_position FROM p) " +

                    "UNION SELECT " + TABLE_NAME + ".id," + COLUMN_5 + ",(SELECT to_position FROM p) " +
                    "FROM " + TABLE_NAME + " " +
                    "WHERE " + COLUMN_5 + " = (SELECT from_position FROM p) " +
                    "AND (SELECT from_position FROM p) <> (SELECT to_position FROM p)" +
                    "), " +

                    "finish_updates AS (" +
                    "SELECT * FROM updates " +
                    "WHERE max((SELECT from_position FROM p),(SELECT to_position FROM p)) <= (SELECT max_position FROM p) " +
                    "AND min((SELECT from_position FROM p),(SELECT to_position FROM p)) >= (SELECT min_position FROM p)" +
                    ")" +

                    "UPDATE " + TABLE_NAME + " SET " + COLUMN_5 + " = " +
                    "(" +
                    " SELECT proposed " +
                    " FROM finish_updates " +
                    " WHERE finish_updates.id = " + TABLE_NAME + ".id" +
                    ") " +
                    "WHERE " + TABLE_NAME + ".id IN " +
                    "(" +
                    " SELECT id FROM finish_updates" +
                    ");";
    SQLiteStatement stmnt = sqLiteDatabase.compileStatement(query);
    stmnt.bindLong(1,from);
    stmnt.bindLong(2,to);
    stmnt.executeUpdateDelete();
}

    fiddle test SQL at