Skip to content
Extraits de code Groupes Projets

Fixed: Move command

Fusionnées Fixed: Move command
move_command vers master
Fusionnées
Deurstannrequested to merge
move_command into master
Affichage du commit 2b9c5c93
Suivant
Afficher la dernière version
1 file
+ 123
− 18
Comparer les modifications
  • Côte à côte
  • En ligne
src/database/queue.c
+ 123
− 18
@@ -444,30 +444,135 @@ database_queue_move(volatile sqlite3 *db, int from, int to)
* 2. to
* 3. from
*/
static const char *SQL_STMT =
static const char *SQL_TEMPLATE_toINFfrom =
"BEGIN TRANSACTION;"
"DELETE FROM queue_tmp;"
"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;"
"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 =
"BEGIN TRANSACTION;"
"UPDATE queue SET position = position + 1 WHERE position >= ?;"
"UPDATE queue SET position = ? WHERE position = ?;"
"DELETE FROM queue_tmp;"
"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;"
"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 =
"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 =
"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;"
"DELETE FROM queue;"
"INSERT INTO queue(position, kara_id, priority)"
" SELECT position, kara_id, priority"
" FROM queue_tmp"
" ORDER BY priority, position;"
"DELETE FROM queue_tmp;"
"COMMIT;";
static const char *SQL_TEMPLATE_PART_2_toSUPfrom =
"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;"
"DELETE FROM queue;"
"INSERT INTO queue(position, kara_id, priority)"
" SELECT position, kara_id, priority"
" FROM queue_tmp"
" ORDER BY priority, position;"
"DELETE FROM queue_tmp;"
"COMMIT;";
sqlite3_stmt *stmt = 0;
bool ret = false;
int code;
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);
code = sqlite3_step(stmt);
if (code != SQLITE_ROW && code != SQLITE_DONE && code != SQLITE_OK) {
LOG_ERROR("DB", "Move failed");
goto error;
char SQL_Part1[LKT_MAX_SQLITE_STATEMENT];
char SQL_Part2[LKT_MAX_SQLITE_STATEMENT];
char SQL_Part3[LKT_MAX_SQLITE_STATEMENT];
if(to < from) {
safe_snprintf(SQL_Part1, LKT_MAX_SQLITE_STATEMENT, SQL_TEMPLATE_toINFfrom, to, from, to, from, to);
safe_snprintf(SQL_Part2, LKT_MAX_SQLITE_STATEMENT, SQL_TEMPLATE_PART_2_toINFfrom, to, from, from, to);
SQLITE_EXEC(db, SQL_Part1, error);
SQLITE_EXEC(db, SQL_Part2, error);
}
else {
safe_snprintf(SQL_Part1, LKT_MAX_SQLITE_STATEMENT, SQL_TEMPLATE_toSUPfrom_Part1, from, from, to);
safe_snprintf(SQL_Part2, LKT_MAX_SQLITE_STATEMENT, SQL_TEMPLATE_toSUPfrom_Part2, to, from, to,to,to);
safe_snprintf(SQL_Part3, LKT_MAX_SQLITE_STATEMENT, SQL_TEMPLATE_PART_2_toSUPfrom, to, from, from, to);
SQLITE_EXEC(db, SQL_Part1, error);
SQLITE_EXEC(db, SQL_Part2, error);
SQLITE_EXEC(db, SQL_Part3, error);
}
ret = true;
return true;
error:
sqlite3_finalize(stmt);
SQLITE_DO_ROLLBACK(db);
return ret;
}