Skip to content
Extraits de code Groupes Projets

Fixed: Move command

Fusionnées Deurstann a demandé de fusionner move_command vers master

Fichiers

+ 123
24
@@ -439,36 +439,135 @@ error:
@@ -439,36 +439,135 @@ error:
bool
bool
database_queue_move(volatile sqlite3 *db, int from, int to)
database_queue_move(volatile sqlite3 *db, int from, int to)
{
{
/* stmt arguments in order:
/* TODO: See if there is a solution with less lines of code */
* 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;
SQLITE_PREPARE(db, stmt, SQL_STMT, error);
static const char *SQL_TEMPLATE_toINFfrom =
SQLITE_BIND_INT(db, stmt, 1, to, error);
/* Clean queue_tmp */
SQLITE_BIND_INT(db, stmt, 2, to, error);
"DELETE FROM queue_tmp;"
SQLITE_BIND_INT(db, stmt, 3, from, error);
"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;";
code = sqlite3_step(stmt);
char SQL[LKT_MAX_SQLITE_STATEMENT];
if (code != SQLITE_ROW && code != SQLITE_DONE && code != SQLITE_OK) {
SQLITE_EXEC(db, "BEGIN TRANSACTION;", error_no_rollback);
LOG_ERROR("DB", "Move failed");
if (to < from) {
goto error;
safe_snprintf(SQL, LKT_MAX_SQLITE_STATEMENT, SQL_TEMPLATE_toINFfrom, to, from, to, from, to);
 
SQLITE_EXEC(db, SQL, 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:
error:
sqlite3_finalize(stmt);
SQLITE_DO_ROLLBACK(db);
return ret;
error_no_rollback:
 
return false;
}
}
bool
bool
Chargement en cours