#define _POSIX_C_SOURCE 200809L
#include <lektor/common.h>
#include <lektor/database.h>
#include <lektor/internal/dbmacro.h>
#define sqlite_just_exec(func, query) \
bool func(lkt_db *db) \
{ \
SQLITE_EXEC(db, "BEGIN TRANSACTION;" query "COMMIT;", error); \
return true; \
error: \
SQLITE_DO_ROLLBACK(db); \
return false; \
}
// clang-format off
sqlite_just_exec(database_queue_toggle_pause, "UPDATE queue_state SET paused = 1 - paused;");
sqlite_just_exec(database_queue_crop, "DELETE FROM queue WHERE queue.kara_id <> (SELECT current FROM queue_state LIMIT 1);");
sqlite_just_exec(database_queue_stop, "UPDATE queue_state SET current = NULL;");
sqlite_just_exec(database_queue_clear, "DELETE FROM queue;"
"DELETE FROM queue_tmp;"
"DELETE FROM sqlite_sequence WHERE name = 'queue_tmp';"
"DELETE FROM " LKT_PROTECTED_DATABASE ".sqlite_sequence WHERE name = 'queue';"
"UPDATE queue_state SET current = NULL;");
sqlite_just_exec(database_config_queue_default, "UPDATE queue_state SET volume = 100, paused = 1,"
" random = 0, repeat = 0, single = 0, consume = 0,"
" current = NULL, duration = 0;");
sqlite_just_exec(database_queue_flat, "UPDATE queue SET priority = 1;");
// clang-format on
#undef sqlite_just_exec
PRIVATE_FUNCTION void
__queue_resequence(lkt_db *db)
{
static const char *SQL = "UPDATE " LKT_PROTECTED_DATABASE
".sqlite_sequence SET seq = (SELECT COUNT(*) FROM queue) WHERE name = 'queue';";
SQLITE_EXEC(db, SQL, error);
LOG_INFO("DB", "Re-sequenced the queue");
return;
error:
LOG_ERROR("DB", "Failed to update the " LKT_PROTECTED_DATABASE ".sqlite_sequence table...");
return;
}
bool
database_queue_set_paused(lkt_db *db, bool paused)
{
const char *SQL = paused ? "UPDATE queue_state SET paused = 1;" : "UPDATE queue_state SET paused = 0;";
SQLITE_EXEC(db, SQL, error);
return true;
error:
return false;
}
bool
database_queue_state(lkt_db *db, struct lkt_queue_state *res)
{
static const char *SQL_STMT = "SELECT"
" volume, paused, random, repeat, single, current, duration, consume,"
" (SELECT COUNT(*) FROM queue) AS length "
"FROM queue_state;\n";
sqlite3_stmt *stmt = 0;
bool ret = false;
SQLITE_PREPARE(db, stmt, SQL_STMT, error);
SQLITE_STEP_ROW(db, stmt, error);
res->volume = sqlite3_column_int(stmt, 0);
res->paused = sqlite3_column_int(stmt, 1);
res->random = sqlite3_column_int(stmt, 2);
res->repeat = sqlite3_column_int(stmt, 3);
res->single = sqlite3_column_int(stmt, 4);
if (sqlite3_column_type(stmt, 5) == SQLITE_NULL)
res->current = -1;
else
res->current = sqlite3_column_int(stmt, 5);
if (sqlite3_column_type(stmt, 6) == SQLITE_NULL)
res->duration = 0;
else
res->duration = sqlite3_column_int(stmt, 6);
res->consume = sqlite3_column_int(stmt, 7);
res->length = sqlite3_column_int(stmt, 8);
ret = true;
error:
sqlite3_finalize(stmt);
return ret;
}
void
database_queue_playtime(lkt_db *db, uint64_t *seconds)
{
static const char *SQL = "SELECT SUM(cached_duration) "
"FROM kara "
"JOIN queue ON queue.kara_id = kara.id;";
sqlite3_stmt *stmt = NULL;
*seconds = 0;
SQLITE_PREPARE(db, stmt, SQL, error);
SQLITE_STEP_ROW(db, stmt, error);
*seconds = sqlite3_column_int(stmt, 0);
error:
sqlite3_finalize(stmt);
return;
}
void
database_total_playtime(lkt_db *db, uint64_t *seconds)
{
static const char *SQL = "SELECT SUM(cached_duration) FROM kara;";
sqlite3_stmt *stmt = NULL;
*seconds = 0;
SQLITE_PREPARE(db, stmt, SQL, error);
SQLITE_STEP_ROW(db, stmt, error);
*seconds = sqlite3_column_int(stmt, 0);
error:
sqlite3_finalize(stmt);
return;
}
bool
database_queue_current_kara(lkt_db *db, struct kara_metadata *res, int *id)
{
static const char *SQL_STMT = "SELECT song_name, source_name, category, language, author_name, "
"song_type, song_number, kara_id"
" FROM kara"
" JOIN queue ON kara_id = kara.id"
" JOIN queue_state ON current = position";
sqlite3_stmt *stmt = NULL;
int ret = false;
SQLITE_PREPARE(db, stmt, SQL_STMT, error);
SQLITE_STEP_ROW(db, stmt, error);
/* Here use gotos because of optimisations done by compilators.
Most of the time it won't be NULL. */
if (!res)
goto no_metadata;
// clang-format off
strncpy(res->song_name, sqlite3_column_chars(stmt, 0), LEKTOR_TAG_MAX - 1);
strncpy(res->source_name, sqlite3_column_chars(stmt, 1), LEKTOR_TAG_MAX - 1);
strncpy(res->category, sqlite3_column_chars(stmt, 2), LEKTOR_TAG_MAX - 1);
strncpy(res->language, sqlite3_column_chars(stmt, 3), LEKTOR_TAG_MAX - 1);
strncpy(res->author_name, sqlite3_column_chars(stmt, 4), LEKTOR_TAG_MAX - 1);
strncpy(res->song_type, sqlite3_column_chars(stmt, 5), LEKTOR_TAG_MAX - 1);
// clang-format on
res->song_number = sqlite3_column_int(stmt, 6);
no_metadata:
/* Most of the time this will be NULL. */
if (id && sqlite3_column_type(stmt, 7) != SQLITE_NULL)
*id = sqlite3_column_int(stmt, 7);
ret = true;
error:
sqlite3_finalize(stmt);
return ret;
}
#define reorder(db, prio, error) \
if (prio > 1) { \
if (__queue_reorder(db)) { \
LOG_INFO("DB", "Queue has been reordered"); \
} else { \
LOG_INFO("DB", "Failed to reorder"); \
goto error; \
} \
}
static bool
__queue_reorder(lkt_db *db)
{
#define CURRENT_POS_OR_0 /* To move karas after the current */ \
"(SELECT CASE WHEN (SELECT current FROM queue_state) IS NULL THEN 0" \
" ELSE (SELECT current FROM queue_state) END AS val LIMIT 1)"
static const char *SQL_REORDER =
/* Clear the TMP */
"DELETE FROM queue_tmp;"
"DELETE FROM sqlite_sequence WHERE name = 'queue_tmp';"
/* Separate karas that are after the current one */
"INSERT INTO queue_tmp (kara_id, priority)"
" SELECT kara_id, priority FROM queue WHERE position > " CURRENT_POS_OR_0
" ORDER BY priority DESC, position ASC;"
"DELETE FROM queue WHERE position > " CURRENT_POS_OR_0 ";"
/* Update the sqlite_sequence table */
"UPDATE " LKT_PROTECTED_DATABASE ".sqlite_sequence SET seq = " CURRENT_POS_OR_0 " WHERE name = 'queue';"
/* Insert back */
"INSERT INTO queue (position, kara_id, priority)"
" SELECT position + " CURRENT_POS_OR_0 ", kara_id, priority"
" FROM queue_tmp;"
/* Clear the TMP */
"DELETE FROM queue_tmp;"
"DELETE FROM sqlite_sequence WHERE name = 'queue_tmp';";
#undef CURRENT_POS_OR_0
SQLITE_EXEC(db, SQL_REORDER, error);
return true;
error:
return false;
}
static bool
queue_add_with_col_like_str(lkt_db *db, const char *col, const char *val, int priority)
{
static const char *SQL_STMT =
/* Add correspondig karas into the queue */
"INSERT INTO queue (kara_id, priority)"
" SELECT id, ?"
" FROM kara"
" WHERE %s LIKE ? AND available = 1"
" ORDER BY RANDOM();";
RETURN_IF(___is_sql_str_invalid(col), "Column name is invalid", false);
char SQL[LKT_MAX_SQLITE_STATEMENT];
sqlite3_stmt *stmt = NULL;
/* Begin, transactions are atomic so we won't be anoyed
by multi-threading */
SQLITE_EXEC(db, "BEGIN TRANSACTION;", error);
/* Insert at the end of the queue */
safe_snprintf(SQL, LKT_MAX_SQLITE_STATEMENT, SQL_STMT, col);
SQLITE_PREPARE(db, stmt, SQL, error);
SQLITE_BIND_INT(db, stmt, 1, priority, error);
SQLITE_BIND_TEXT(db, stmt, 2, val, error);
SQLITE_STEP_DONE(db, stmt, error);
sqlite3_finalize(stmt);
/* Reorder kara that are after the current one, do this shit only
if priority > 1... */
reorder(db, priority, error);
/* End */
SQLITE_EXEC(db, "COMMIT;", error);
LOG_INFO("DB-DEBUG", "Added with col %s like %s with priority %d", col, val, priority);
return true;
error:
sqlite3_finalize(stmt);
SQLITE_DO_ROLLBACK(db);
__queue_resequence(db);
return false;
}
bool
database_queue_add_plt(lkt_db *db, const char *plt_name, int priority)
{
static const char *SQL_STMT =
/* Insert the playlist */
"INSERT INTO queue (kara_id, priority) "
"SELECT kara.id, ?"
" FROM kara"
" JOIN kara_playlist ON kara_id = kara.id AND kara.available = 1"
" JOIN playlist ON playlist_id = playlist.id AND playlist.name = ?"
" ORDER BY RANDOM();";
sqlite3_stmt *stmt = NULL;
/* Begin, transactions are atomic so we won't be anoyed
by multi-threading */
SQLITE_EXEC(db, "BEGIN TRANSACTION;", error);
SQLITE_PREPARE(db, stmt, SQL_STMT, error);
SQLITE_BIND_INT(db, stmt, 1, priority, error);
SQLITE_BIND_TEXT(db, stmt, 2, plt_name, error);
SQLITE_STEP_DONE(db, stmt, error);
sqlite3_finalize(stmt);
/* Do the move shit only if the priority > 1 */
reorder(db, priority, error);
SQLITE_EXEC(db, "COMMIT;", error);
LOG_INFO("DB-DEBUG", "Added play list %s with priority %d", plt_name, priority);
return true;
error:
sqlite3_finalize(stmt);
SQLITE_DO_ROLLBACK(db);
__queue_resequence(db);
return false;
}
bool
database_queue_add_id(lkt_db *db, int id, int priority)
{
static const char *SQL_STMT = "INSERT INTO queue (kara_id, priority) VALUES (?,?);";
sqlite3_stmt *stmt = NULL;
SQLITE_EXEC(db, "BEGIN TRANSACTION;", error);
SQLITE_PREPARE(db, stmt, SQL_STMT, error);
SQLITE_BIND_INT(db, stmt, 1, id, error);
SQLITE_BIND_INT(db, stmt, 2, priority, error);
SQLITE_STEP_DONE(db, stmt, error);
sqlite3_finalize(stmt);
/* Do the move shit only if the priority > 1 */
reorder(db, priority, error_no_stmt);
SQLITE_EXEC(db, "COMMIT;", error);
return true;
error:
LOG_ERROR("DB",
"Error while adding the kara id %d into the queue with the "
"priority %d",
id, priority);
sqlite3_finalize(stmt);
error_no_stmt:
LOG_ERROR("DB",
"Need to rollback the queue because of failed insertion of id "
"%d with priority %d in the queue",
id, priority);
SQLITE_DO_ROLLBACK(db);
__queue_resequence(db);
return false;
}
bool
database_queue_add_uri(lkt_db *db, struct lkt_uri *uri, int prio)
{
switch (uri->type) {
case URI_QUERY:
return queue_add_with_col_like_str(db, LKT_DB_ALL, uri->value, prio);
case URI_ID:
return database_queue_add_id(db, uri->id, prio);
case URI_TYPE:
return queue_add_with_col_like_str(db, LKT_DB_TYPE, uri->value, prio);
case URI_CATEGORY:
return queue_add_with_col_like_str(db, LKT_DB_CAT, uri->value, prio);
case URI_LANGUAGE:
return queue_add_with_col_like_str(db, LKT_DB_LANG, uri->value, prio);
case URI_AUTHOR:
return queue_add_with_col_like_str(db, LKT_DB_AUTHOR, uri->value, prio);
case URI_PLAYLIST:
return database_queue_add_plt(db, uri->value, prio);
default:
LOG_WARN("DB", "Add to queue for uri of type %d is not implemented", uri->type);
return false;
}
}
bool
database_queue_del_id(lkt_db *db, int id)
{
static const char *SQL_TEMPLATE =
"BEGIN TRANSACTION;"
/* Clear the TMP */
"DELETE FROM queue_tmp;"
"DELETE FROM sqlite_sequence WHERE name = 'queue_tmp';"
/* Move the current 'pointer' */
"UPDATE queue_state SET current = (SELECT NULLIF(COUNT(position), 0) FROM queue JOIN queue_state ON position <= current AND kara_id != %d);"
"DELETE FROM queue WHERE kara_id = %d;" /* Delete any kara with the specified id */
"INSERT INTO queue_tmp(priority, position, kara_id) SELECT priority, position, kara_id FROM queue ORDER BY position ASC;"
"DELETE FROM queue;"
"INSERT INTO queue(priority, position, kara_id) SELECT priority, ROW_NUMBER() OVER(ORDER BY position ASC), kara_id FROM queue_tmp;"
"UPDATE " LKT_PROTECTED_DATABASE
".sqlite_sequence SET seq = (SELECT COUNT(*) FROM queue) WHERE name = 'queue';" /* Update the sqlite_sequence */
/* Clear the TMP */
"DELETE FROM queue_tmp;"
"DELETE FROM sqlite_sequence WHERE name = 'queue_tmp';"
"COMMIT;";
char SQL[LKT_MAX_SQLITE_STATEMENT];
safe_snprintf(SQL, LKT_MAX_SQLITE_STATEMENT, SQL_TEMPLATE, id, id);
SQLITE_EXEC(db, SQL, error);
__queue_resequence(db);
return true;
error:
SQLITE_DO_ROLLBACK(db);
return false;
}
bool
database_queue_del_pos(lkt_db *db, int pos)
{
bool sta = false;
sqlite3_stmt *stmt = NULL;
struct lkt_queue_state queue = { .current = -1 };
RETURN_UNLESS(database_queue_state(db, &queue), "Failed to get the queue state", false);
/* Delete the current kara, can't do that because we need to skip it, and
* send signals to the window module to do so, so we just fail here... */
if (queue.current == pos) {
LOG_ERROR("DB", "Can't delete the currently playing kara at position %d in queue", pos);
return false;
}
static const char *SQL_TEMPLATE =
/* Create temporary queue */
"DELETE FROM queue_tmp;"
"DELETE FROM sqlite_sequence WHERE name = 'queue_tmp';"
/* -1 to all positions after the deleted kara */
"DELETE FROM queue WHERE position = %d;"
"INSERT INTO queue_tmp (kara_id, priority)"
" SELECT kara_id, priority"
" FROM queue;"
/* Re-init positions in the queue */
"DELETE FROM queue;"
"DELETE FROM " LKT_PROTECTED_DATABASE ".sqlite_sequence WHERE name = 'queue';"
/* Insert back all karas */
"INSERT INTO queue (kara_id, priority, position)"
" SELECT kara_id, priority, position"
" FROM queue_tmp;"
/* Slap correct values here */
"UPDATE " LKT_PROTECTED_DATABASE ".sqlite_sequence"
" SET seq = (SELECT COUNT(position) FROM queue)"
" WHERE name = 'queue';"
"DELETE FROM queue_tmp;"
"DELETE FROM sqlite_sequence WHERE name = 'queue_tmp';";
char SQL[LKT_MAX_SQLITE_STATEMENT];
safe_snprintf(SQL, LKT_MAX_SQLITE_STATEMENT, SQL_TEMPLATE, pos);
SQLITE_EXEC(db, "BEGIN TRANSACTION;", error_no_rollback);
SQLITE_EXEC(db, SQL, error_rollback);
if (pos < queue.current) {
LOG_DEBUG("DB", "Needs to decrement current index: pos %d < current %d", pos, queue.current);
SQLITE_EXEC(db, "UPDATE queue_state SET current = current - 1;", error_in_delete_or_update);
}
sta = true;
error_in_delete_or_update:
if (sta) {
/* Commit transaction, if failed use a magnificient goto... */
__queue_resequence(db);
SQLITE_EXEC(db, "END TRANSACTION;", error_rollback);
}
else {
error_rollback:
sta = false;
LOG_DEBUG("DB", "An error occured, needs to rollback transaction");
SQLITE_DO_ROLLBACK(db);
}
error_no_rollback:
if (stmt != NULL)
sqlite3_finalize(stmt);
return sta;
}
bool
database_queue_next(lkt_db *db, char filepath[PATH_MAX])
{
static const char *SQL_STMT =
"SELECT file_path, position, RANDOM()"
" FROM kara"
" JOIN queue ON kara.id = queue.kara_id"
" JOIN queue_state"
" ON current IS NULL"
" OR (CASE WHEN (SELECT single FROM queue_state LIMIT 1) = 1 THEN position = current"
" WHEN (SELECT random FROM queue_state LIMIT 1) = 1 THEN position <> current"
" ELSE position > current END)"
" ORDER BY CASE WHEN (SELECT random FROM queue_state LIMIT 1) = 1 THEN RANDOM() ELSE 2 END"
" LIMIT 1";
char SQL_UPDATE[LKT_MAX_SQLITE_STATEMENT];
bool status = false;
int code = SQLITE_OK, id;
sqlite3_stmt *stmt = NULL;
SQLITE_PREPARE(db, stmt, SQL_STMT, error);
code = sqlite3_step(stmt);
if (code == SQLITE_ROW) {
id = MAX(1, sqlite3_column_int(stmt, 1));
safe_snprintf(SQL_UPDATE, LKT_MAX_SQLITE_STATEMENT, "UPDATE queue_state SET current = %d;", id);
if (filepath != NULL)
strncpy(filepath, sqlite3_column_chars(stmt, 0), PATH_MAX - 1);
else {
LOG_ERROR("DB", "Failed to get file, id was %d", id);
goto error;
}
}
else if (code == SQLITE_DONE) {
LOG_ERROR("DB", "Failed to get next");
goto error;
}
else {
LOG_ERROR("DB", "Failed to fetch next kara: %s", sqlite3_errmsg((sqlite3 *)db));
goto error;
}
SQLITE_EXEC(db, SQL_UPDATE, error);
status = true;
error:
sqlite3_finalize(stmt);
return status;
}
bool
database_queue_skip_current(lkt_db *db, char filepath[PATH_MAX])
{
if (database_queue_next(db, filepath))
return true;
else if (database_queue_prev(db, filepath))
return true;
else {
database_queue_stop(db);
return false;
}
}
bool
database_queue_prev(lkt_db *db, char filepath[PATH_MAX])
{
static const char *SQL_STMT = "SELECT file_path, position "
" FROM kara"
" JOIN queue ON kara.id = queue.kara_id AND available = 1"
" JOIN queue_state ON CASE "
" WHEN (SELECT single FROM queue_state LIMIT 1) = 1 THEN position = current"
" ELSE queue.position < queue_state.current"
" END"
" ORDER BY position DESC LIMIT 1;";
char SQL_UPDATE[LKT_MAX_SQLITE_STATEMENT];
bool status = false;
int code = SQLITE_OK, id;
sqlite3_stmt *stmt = NULL;
SQLITE_PREPARE(db, stmt, SQL_STMT, error);
code = sqlite3_step(stmt);
if (code == SQLITE_ROW) {
id = MAX(1, sqlite3_column_int(stmt, 1));
safe_snprintf(SQL_UPDATE, LKT_MAX_SQLITE_STATEMENT, "UPDATE queue_state SET current = %d;", id);
if (filepath != NULL)
strncpy(filepath, sqlite3_column_chars(stmt, 0), PATH_MAX - 1);
else {
LOG_ERROR("DB", "Failed to get file, position was %d", id);
goto error;
}
}
else if (code == SQLITE_DONE) {
LOG_ERROR("DB", "Failed to get previous");
goto error;
}
else {
LOG_ERROR("DB", "Failed to fetch prev kara: %s", sqlite3_errmsg((sqlite3 *)db));
goto error;
}
SQLITE_EXEC(db, SQL_UPDATE, error);
status = true;
error:
sqlite3_finalize(stmt);
return status;
}
bool
database_queue_swap(lkt_db *db, int from, int to)
{
static const char *SQL_POP_ROW_1 = "SELECT position, priority, kara_id FROM queue WHERE position = ?;";
static const char *SQL_POP_ROW_2 = "DELETE FROM queue WHERE position = ?;";
static const char *SQL_SLAP_MAX = "UPDATE " LKT_PROTECTED_DATABASE ".sqlite_sequence"
" SET seq = (SELECT COUNT(position) FROM queue)"
" WHERE name = 'queue';";
static const char *SQL_PUSH_ROW = "INSERT INTO queue (position, priority, kara_id) VALUES (?, ?, ?);";
struct {
int position;
int priority;
int kara_id;
} kara_from, kara_to;
sqlite3_stmt *stmt = NULL;
bool sta = false;
int tmp_kara_id;
/* Get one kara from the 'queue' and delete it */
#define POP_QUEUE(name) \
SQLITE_PREPARE(db, stmt, SQL_POP_ROW_1, error); \
SQLITE_BIND_INT(db, stmt, 1, name, error); \
SQLITE_STEP_ROW(db, stmt, error); \
kara_##name.position = sqlite3_column_int(stmt, 0); \
kara_##name.priority = sqlite3_column_int(stmt, 1); \
kara_##name.kara_id = sqlite3_column_int(stmt, 2); \
sqlite3_finalize(stmt); \
SQLITE_PREPARE(db, stmt, SQL_POP_ROW_2, error); \
SQLITE_BIND_INT(db, stmt, 1, name, error); \
SQLITE_STEP_DONE(db, stmt, error); \
sqlite3_finalize(stmt);
/* Push one kara on the 'queue' */
#define PUSH_QUEUE(name) \
SQLITE_PREPARE(db, stmt, SQL_PUSH_ROW, error); \
SQLITE_BIND_INT(db, stmt, 1, kara_##name.position, error); \
SQLITE_BIND_INT(db, stmt, 2, kara_##name.priority, error); \
SQLITE_BIND_INT(db, stmt, 3, kara_##name.kara_id, error); \
SQLITE_STEP_DONE(db, stmt, error); \
sqlite3_finalize(stmt);
SQLITE_EXEC(db, "BEGIN TRANSACTION;", error_no_rollback);
POP_QUEUE(from);
POP_QUEUE(to);
tmp_kara_id = kara_from.kara_id;
kara_from.kara_id = kara_to.kara_id;
kara_to.kara_id = tmp_kara_id;
PUSH_QUEUE(from);
PUSH_QUEUE(to);
SQLITE_EXEC(db, SQL_SLAP_MAX, error_no_stmt);
SQLITE_EXEC(db, "END TRANSACTION;", error_no_stmt);
#undef PUSH_QUEUE
#undef POP_QUEUE
sta = true;
goto error_no_stmt;
error:
sqlite3_finalize(stmt);
error_no_stmt:
if (!sta)
SQLITE_DO_ROLLBACK(db);
error_no_rollback:
return sta;
}
bool
database_queue_move(lkt_db *db, int from, int to)
{
static const char *SQL_TEMPLATE_PART_1 =
/* Clean queue_tmp */
"DELETE FROM queue_tmp;"
"DELETE FROM sqlite_sequence WHERE name = 'queue_tmp';"
/* Construct queue */
"INSERT INTO queue_tmp(kara_id,priority)"
"WITH TEMP_TABLE AS (SELECT"
" position, kara_id, priority"
" FROM queue"
" ORDER BY position ASC, priority DESC)"
"SELECT kara_id, priority"
" FROM TEMP_TABLE WHERE POSITION < %d AND POSITION != %d ORDER BY POSITION;"
/* Construct queue */
"INSERT INTO queue_tmp(kara_id,priority)"
" VALUES(("
" WITH TEMP_TABLE AS ("
" SELECT ROW_NUMBER() OVER(ORDER BY position ASC, priority DESC) 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 position ASC, priority DESC) 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 =
/* Construct queue */
"INSERT INTO queue_tmp(kara_id,priority)"
"WITH TEMP_TABLE AS ("
" SELECT ROW_NUMBER() OVER(ORDER BY position ASC, priority DESC) AS POSITION,"
" kara_id, priority FROM queue)"
"SELECT kara_id, priority"
" FROM TEMP_TABLE WHERE POSITION >= %d AND POSITION != %d ORDER BY POSITION;"
/* Clean queue and insert back */
"UPDATE queue_state"
" SET current = CASE"
" WHEN current NOT NULL "
" THEN (SELECT position FROM queue_tmp "
" WHERE kara_id=(SELECT kara_id FROM queue WHERE position = current))"
" ELSE NULL END;"
"DELETE FROM queue;"
"DELETE FROM " LKT_PROTECTED_DATABASE ".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;";
char SQL[LKT_MAX_SQLITE_STATEMENT];
SQLITE_EXEC(db, "BEGIN TRANSACTION;", error_no_rollback);
safe_snprintf(SQL, LKT_MAX_SQLITE_STATEMENT, SQL_TEMPLATE_PART_1, to, from, from, to, to, to);
SQLITE_EXEC(db, SQL, error);
safe_snprintf(SQL, LKT_MAX_SQLITE_STATEMENT, SQL_TEMPLATE_PART_2, to, from);
SQLITE_EXEC(db, SQL, error);
SQLITE_EXEC(db, "COMMIT;", error);
return true;
error:
SQLITE_DO_ROLLBACK(db);
error_no_rollback:
return false;
}
bool
database_queue_play(lkt_db *db, int pos)
{
static const char *SQL_STMT = "UPDATE queue_state SET current = ?, paused = 0"
" WHERE current <= 0 OR current IS NULL";
int code = SQLITE_OK;
bool ret = false;
sqlite3_stmt *stmt = NULL;
SQLITE_PREPARE(db, stmt, SQL_STMT, error);
SQLITE_BIND_INT(db, stmt, 1, pos, error);
code = sqlite3_step(stmt);
if (code != SQLITE_OK && code != SQLITE_DONE) {
LOG_ERROR("DB", "Failed to update queue_state: %s", sqlite3_errmsg((sqlite3 *)db));
goto error;
}
ret = true;
error:
sqlite3_finalize(stmt);
return ret;
}
bool
database_queue_set_current_index(lkt_db *db, int idx)
{
static const char *SQL_GET_TEMPLATE = "UPDATE queue_state SET current = %d;";
char SQL_GET[LKT_MAX_SQLITE_STATEMENT];
if (idx <= 0) {
LOG_ERROR("DB", "An idx of %d is invalid, must be >= 0", idx);
return false;
}
if (safe_snprintf(SQL_GET, LKT_MAX_SQLITE_STATEMENT, SQL_GET_TEMPLATE, idx) < 0)
return false;
SQLITE_EXEC(db, SQL_GET, error);
return true;
error:
return false;
}
bool
database_queue_get_current_file(lkt_db *db, char filepath[PATH_MAX])
{
static const char *SQL_STMT = "SELECT file_path"
" FROM kara"
" JOIN queue ON kara.id = queue.kara_id"
" JOIN queue_state ON queue.position = queue_state.current";
bool status = false;
int code = SQLITE_OK;
sqlite3_stmt *stmt = NULL;
if (filepath == NULL)
goto error;
SQLITE_PREPARE(db, stmt, SQL_STMT, error);
code = sqlite3_step(stmt);
if (code == SQLITE_ROW) {
strncpy(filepath, sqlite3_column_chars(stmt, 0), PATH_MAX);
filepath[PATH_MAX - 1] = '\0';
}
else {
LOG_ERROR("DB", "Failed to fetch prev kara: %s", sqlite3_errmsg((sqlite3 *)db));
goto error;
}
status = true;
error:
sqlite3_finalize(stmt);
return status;
}
bool
database_get_kara_path(lkt_db *db, int id, char filepath[PATH_MAX])
{
bool ret_code = false;
sqlite3_stmt *stmt = NULL;
static const char *SQL = "SELECT file_path FROM kara_cache WHERE kara_id = ?;";
RETURN_UNLESS(id && db, "Invalid argument", false);
SQLITE_PREPARE(db, stmt, SQL, error);
SQLITE_BIND_INT(db, stmt, 1, id, error);
SQLITE_STEP_ROW(db, stmt, error);
if (filepath)
safe_strncpy(filepath, sqlite3_column_chars(stmt, 0), PATH_MAX);
ret_code = true;
error:
sqlite3_finalize(stmt);
return ret_code;
}
bool
database_get_kara_id(lkt_db *db, char filepath[PATH_MAX], int *id)
{
bool ret_code = false;
sqlite3_stmt *stmt = NULL;
static const char *SQL = "SELECT id FROM kara WHERE file_path = ? AND available = 1;";
RETURN_UNLESS(id && db, "Invalid argument", false);
SQLITE_PREPARE(db, stmt, SQL, error);
SQLITE_BIND_TEXT(db, stmt, 1, filepath, error);
SQLITE_STEP_ROW(db, stmt, error);
if (id)
*id = sqlite3_column_int(stmt, 0);
ret_code = true;
error:
sqlite3_finalize(stmt);
return ret_code;
}
bool
database_get_kara_position(lkt_db *db, int id, int *pos)
{
bool ret_code = false;
sqlite3_stmt *stmt = NULL;
static const char *SQL = "WITH content AS ("
" SELECT kara.id AS id, position"
" FROM queue"
" JOIN kara ON kara_id = kara.id"
" GROUP BY position ORDER BY position ASC, priority DESC)"
"SELECT id, position FROM content WHERE id = ?;";
RETURN_UNLESS(db && pos, "Invalid argument", false);
SQLITE_PREPARE(db, stmt, SQL, error);
SQLITE_BIND_INT(db, stmt, 1, id, error);
SQLITE_STEP_ROW(db, stmt, error);
*pos = sqlite3_column_int(stmt, 0);
ret_code = true;
error:
sqlite3_finalize(stmt);
return ret_code;
}
int
database_queue_probe_id(lkt_db *db, int id)
{
int position = 0; /* The queue begins with the position 1 */
sqlite3_stmt *stmt = NULL;
static const char *SQL = "SELECT position FROM queue WHERE kara_id = ?;";
SQLITE_PREPARE(db, stmt, SQL, error);
SQLITE_BIND_INT(db, stmt, 1, id, error);
SQLITE_STEP_ROW(db, stmt, error);
position = sqlite3_column_int(stmt, 0);
error:
sqlite3_finalize(stmt);
return position;
}
bool
database_queue_shuffle(lkt_db *db)
{
const char *SQL = "BEGIN TRANSACTION;"
/* Create temporary queue */
"DELETE FROM queue_tmp;"
"DELETE FROM sqlite_sequence WHERE name = 'queue_tmp';"
/* When current is NULL, that thing is also NULL, so no insertion is done */
"INSERT INTO queue_tmp (kara_id, priority)"
" SELECT kara_id, 5"
" FROM queue"
" JOIN queue_state ON queue.position = queue_state.current;"
/* Insert the others karas */
"INSERT INTO queue_tmp (kara_id, priority)"
" SELECT kara_id, priority"
" FROM queue"
" JOIN queue_state ON"
" queue.position <> CASE"
" WHEN queue_state.current IS NOT NULL THEN queue_state.current"
" ELSE 0 END"
" ORDER BY RANDOM();"
/* Do the insertion in the other way */
"DELETE FROM queue;"
"DELETE FROM " LKT_PROTECTED_DATABASE ".sqlite_sequence WHERE name = 'queue';"
"INSERT INTO queue (position, kara_id, priority)"
" SELECT position, kara_id, priority"
" FROM queue_tmp"
" ORDER BY priority, position;"
/* Set the current to the right kara when needed */
"UPDATE queue_state"
" SET current = CASE"
" WHEN current NOT NULL THEN 1"
" ELSE NULL END;"
/* Clear the TMP */
"DELETE FROM queue_tmp;"
"DELETE FROM sqlite_sequence WHERE name = 'queue_tmp';"
"COMMIT;";
database_queue_flat(db);
SQLITE_EXEC(db, SQL, error);
return true;
error:
SQLITE_DO_ROLLBACK(db);
return false;
}
bool
database_queue_list(lkt_db *db, size_t from, size_t to, struct lkt_callback *callback)
{
const char *SQL_STMT =
"WITH content AS ("
" SELECT kara.id AS id, string, LENGTH(CAST(kara.id AS TEXT)) AS len, position, LENGTH(CAST(position AS TEXT)) AS pos_len"
" FROM queue"
" JOIN kara ON kara_id = kara.id"
" WHERE position >= ? AND position <= ?"
" GROUP BY position ORDER BY position ASC, priority DESC)"
"SELECT id, string, (SELECT MAX(len) FROM content), position, (SELECT MAX(pos_len) FROM content) FROM content;";
int code, id, id_len, pos, pos_len;
const char *row;
bool ret = false;
sqlite3_stmt *stmt;
SQLITE_PREPARE(db, stmt, SQL_STMT, error);
SQLITE_BIND_INT(db, stmt, 1, (int)from, error);
SQLITE_BIND_INT(db, stmt, 2, (int)to, error);
for (;;) {
code = sqlite3_step(stmt);
if (code == SQLITE_ROW) {
id = sqlite3_column_int(stmt, 0);
row = sqlite3_column_chars(stmt, 1);
id_len = sqlite3_column_int(stmt, 2);
pos = sqlite3_column_int(stmt, 3);
pos_len = sqlite3_column_int(stmt, 4);
++(callback->iterations);
if (callback->call(callback, pos_len, pos, id, id_len, row))
continue;
else
break;
}
else if (code == SQLITE_OK || code == SQLITE_DONE)
goto done;
else {
LOG_ERROR("DB", "Failed: %s", sqlite3_errmsg((sqlite3 *)db));
break;
}
}
done:
ret = true;
error:
sqlite3_finalize(stmt);
return ret;
}
bool
database_queue_dump(lkt_db *db, const char *plt_name)
{
static const char *SQL = "WITH plt_id AS (SELECT playlist.id AS id FROM playlist"
" WHERE name COLLATE nocase = ?) "
"INSERT OR IGNORE INTO kara_playlist (kara_id, playlist_id)"
" SELECT DISTINCT kara_id, plt_id.id"
" FROM queue, plt_id"
" ORDER BY RANDOM();";
bool ret_code = false;
sqlite3_stmt *stmt = NULL;
SQLITE_PREPARE(db, stmt, SQL, error);
SQLITE_BIND_TEXT(db, stmt, 1, plt_name, error);
SQLITE_STEP_DONE(db, stmt, error);
ret_code = true;
error:
sqlite3_finalize(stmt);
return ret_code;
}
bool
database_queue_seekid(lkt_db *db, int id, int *out_pos)
{
static const char *SQL_STMT = "SELECT position FROM queue WHERE kara_id = ? LIMIT 1";
int ret = 0;
sqlite3_stmt *stmt;
SQLITE_PREPARE(db, stmt, SQL_STMT, error);
SQLITE_BIND_INT(db, stmt, 1, id, error);
SQLITE_STEP_ROW(db, stmt, error);
*out_pos = sqlite3_column_int(stmt, 0);
ret = true;
error:
sqlite3_finalize(stmt);
return ret;
}