Fixed: Move command

Fixed move command

src/database/queue.c

@@ -439,36 +439,133 @@ error:
 bool
 database_queue_move(volatile sqlite3 *db, int from, int to)
 {
-    /* stmt arguments in order:
-     * 1. to
-     * 2. to
-     * 3. from
-     */
-    static const char *SQL_STMT =
-        "BEGIN TRANSACTION;"
-        "UPDATE queue SET position = position + 1 WHERE position >= ?;"
-        "UPDATE queue SET position = ? WHERE position = ?;"
-        "COMMIT;";
-    sqlite3_stmt *stmt = 0;
-    bool ret = false;
-    int code;
+    static const char *SQL_TEMPLATE_toINFfrom =
+        /* Clean queue_tmp */
+        "DELETE FROM queue_tmp;"
+        "DELETE FROM sqlite_sequence WHERE name = 'queue_tmp';"
+        /* Construct queue */
+        "INSERT INTO queue_tmp(position, kara_id,priority)"
+        "WITH TEMP_TABLE AS (SELECT"
+        "  ROW_NUMBER() OVER(ORDER BY priority DESC, position ASC) AS POSITION, kara_id, priority"
+        "  FROM queue)"
+        "SELECT POSITION, kara_id, priority"
+        "  FROM TEMP_TABLE WHERE POSITION < %d AND POSITION != %d ORDER BY POSITION;"
+        /* Construct queue */
+        "INSERT INTO queue_tmp(position, kara_id,priority)"
+        "  VALUES(%d,("
+        "    WITH TEMP_TABLE AS ("
+        "      SELECT ROW_NUMBER() OVER(ORDER BY priority DESC, position ASC) AS POSITION,"
+        "        kara_id, priority FROM queue)"
+        "      SELECT kara_id FROM TEMP_TABLE WHERE POSITION = %d"
+        "    ),("
+        "      WITH TEMP_TABLE AS ("
+        "      SELECT ROW_NUMBER() OVER(ORDER BY priority DESC, position ASC) AS POSITION,"
+        "        kara_id, priority FROM queue)"
+        "      SELECT priority FROM TEMP_TABLE WHERE POSITION = %d"
+        "  ));";
+
+    static const char *SQL_TEMPLATE_toSUPfrom_Part1 =
+        /* Clean queue_tmp */
+        "DELETE FROM queue_tmp;"
+        "DELETE FROM sqlite_sequence WHERE name = 'queue_tmp';"
+        /* Construct queue */
+        "INSERT INTO queue_tmp(position, kara_id,priority)"
+        "WITH TEMP_TABLE AS (SELECT"
+        "  ROW_NUMBER() OVER(ORDER BY priority DESC, position ASC) AS POSITION, kara_id, priority"
+        "  FROM queue)"
+        "SELECT POSITION, kara_id, priority"
+        "  FROM TEMP_TABLE WHERE POSITION < %d ORDER BY POSITION;"
+        /* Construct queue */
+        "INSERT INTO queue_tmp(position, kara_id,priority)"
+        "WITH TEMP_TABLE AS (SELECT"
+        "  ROW_NUMBER() OVER(ORDER BY priority DESC, position ASC) AS POSITION, kara_id, priority"
+        "  FROM queue)"
+        "SELECT POSITION - 1, kara_id, priority"
+        "  FROM TEMP_TABLE WHERE POSITION > %d AND POSITION < %d ORDER BY POSITION;";
+    static const char *SQL_TEMPLATE_toSUPfrom_Part2 =
+        /* Construct queue */
+        "INSERT INTO queue_tmp(position, kara_id,priority)"
+        "  VALUES(%d-1,("
+        "    WITH TEMP_TABLE AS (SELECT"
+        "      ROW_NUMBER() OVER(ORDER BY priority DESC, position ASC) AS POSITION,"
+        "      kara_id, priority FROM queue)"
+        "    SELECT kara_id FROM TEMP_TABLE WHERE POSITION = %d"
+        "  ),("
+        "    WITH TEMP_TABLE AS (SELECT"
+        "      ROW_NUMBER() OVER(ORDER BY priority DESC, position ASC) AS POSITION,"
+        "      kara_id, priority FROM queue)"
+        "    SELECT priority FROM TEMP_TABLE WHERE POSITION ="
+        "    CASE WHEN (SELECT COUNT(*) FROM TEMP_TABLE)>=%d THEN %d ELSE %d-1 END"
+        "));";
+
+    static const char *SQL_TEMPLATE_PART_2_toINFfrom =
+        /* Construct queue */
+        "INSERT INTO queue_tmp(position, kara_id,priority)"
+        "WITH TEMP_TABLE AS (SELECT"
+        "  ROW_NUMBER() OVER(ORDER BY priority DESC, position ASC) AS POSITION,"
+        "  kara_id, priority FROM queue)"
+        "SELECT (POSITION+1) , kara_id, priority"
+        "  FROM TEMP_TABLE WHERE POSITION >= %d AND POSITION < %d ORDER BY POSITION;"
+        "INSERT INTO queue_tmp(position, kara_id,priority)"
+        "  WITH TEMP_TABLE AS (SELECT"
+        "    ROW_NUMBER() OVER(ORDER BY priority DESC, position ASC) AS POSITION,"
+        "    kara_id, priority"
+        "    FROM queue)"
+        "  SELECT POSITION , kara_id, priority"
+        "  FROM TEMP_TABLE WHERE POSITION > %d AND POSITION >=%d ORDER BY POSITION;"
+        /* Clean queue and insert back */
+        "DELETE FROM queue;"
+        "DELETE FROM sqlite_sequence WHERE name = 'queue';"
+        "INSERT INTO queue(position, kara_id, priority)"
+        "  SELECT position, kara_id, priority FROM queue_tmp ORDER BY priority, position;"
+        /* Clean queue_tmp */
+        "DELETE FROM sqlite_sequence WHERE name = 'queue_tmp';"
+        "DELETE FROM queue_tmp;";
+    static const char *SQL_TEMPLATE_PART_2_toSUPfrom =
+        /* Construct queue */
+        "INSERT INTO queue_tmp(position, kara_id,priority)"
+        "WITH TEMP_TABLE AS ("
+        "  SELECT ROW_NUMBER() OVER(ORDER BY priority DESC, position ASC) AS POSITION,"
+        "    kara_id, priority FROM queue)"
+        "SELECT POSITION , kara_id, priority"
+        "  FROM TEMP_TABLE WHERE POSITION >= %d AND POSITION != %d ORDER BY POSITION;"
+        /* Clean queue and insert back */
+        "DELETE FROM queue;"
+        "DELETE FROM sqlite_sequence WHERE name = 'queue';"
+        "INSERT INTO queue(position, kara_id, priority)"
+        "  SELECT position, kara_id, priority FROM queue_tmp ORDER BY priority, position;"
+        /* Clean queue_tmp */
+        "DELETE FROM sqlite_sequence WHERE name = 'queue_tmp';"
+        "DELETE FROM queue_tmp;";
 
-    SQLITE_PREPARE(db, stmt, SQL_STMT, error);
-    SQLITE_BIND_INT(db, stmt, 1, to, error);
-    SQLITE_BIND_INT(db, stmt, 2, to, error);
-    SQLITE_BIND_INT(db, stmt, 3, from, error);
+    char SQL[LKT_MAX_SQLITE_STATEMENT];
 
-    code = sqlite3_step(stmt);
+    SQLITE_EXEC(db, "BEGIN TRANSACTION;", error_no_rollback);
+    if (to < from) {
+        safe_snprintf(SQL, LKT_MAX_SQLITE_STATEMENT, SQL_TEMPLATE_toINFfrom, to, from, to, from, to);
+        SQLITE_EXEC(db, SQL, error);
 
-    if (code != SQLITE_ROW && code != SQLITE_DONE && code != SQLITE_OK) {
-        LOG_ERROR("DB", "Move failed");
-        goto error;
+        safe_snprintf(SQL, LKT_MAX_SQLITE_STATEMENT, SQL_TEMPLATE_PART_2_toINFfrom, to, from, from, to);
+        SQLITE_EXEC(db, SQL, error);
     }
 
-    ret = true;
+    else {
+        safe_snprintf(SQL, LKT_MAX_SQLITE_STATEMENT, SQL_TEMPLATE_toSUPfrom_Part1, from, from, to);
+        SQLITE_EXEC(db, SQL, error);
+
+        safe_snprintf(SQL, LKT_MAX_SQLITE_STATEMENT, SQL_TEMPLATE_toSUPfrom_Part2, to, from, to, to, to);
+        SQLITE_EXEC(db, SQL, error);
+
+        safe_snprintf(SQL, LKT_MAX_SQLITE_STATEMENT, SQL_TEMPLATE_PART_2_toSUPfrom, to, from, from, to);
+        SQLITE_EXEC(db, SQL, error);
+    }
+
+    SQLITE_EXEC(db, "COMMIT;", error);
+    return true;
 error:
-    sqlite3_finalize(stmt);
-    return ret;
+    SQLITE_DO_ROLLBACK(db);
+error_no_rollback:
+    return false;
 }
 
 bool