update.c

#define _POSIX_C_SOURCE 200809L
#define _DEFAULT_SOURCE

#include <lektor/common.h>
#include <lektor/database.h>
#include <lektor/internal/dbmacro.h>
#include <dirent.h>

PRIVATE_FUNCTION bool
___add_kara_to_update_job(lkt_db *db, size_t id)
{
    char SQL[LKT_MAX_SQLITE_STATEMENT];

    if (id) {
        static const char *SQL_TEMP = "INSERT OR REPLACE INTO updates (job, kara_id) "
                                      "SELECT MAX(update_job), %ld FROM misc;"
                                      "UPDATE kara SET available = 1 WHERE id = %ld;";
        safe_snprintf(SQL, LKT_MAX_SQLITE_STATEMENT, SQL_TEMP, id, id);
    }

    else {
        static const char *SQL_TEMP = "INSERT OR REPLACE INTO updates (job, kara_id) "
                                      "SELECT MAX(update_job), last_insert_rowid() FROM misc;"
                                      "UPDATE kara SET available = 1 WHERE id = last_insert_rowid();";
        safe_strncpy(SQL, SQL_TEMP, LKT_MAX_SQLITE_STATEMENT);
    }

    SQLITE_EXEC(db, SQL, error);
    return true;

error:
    return false;
}

PRIVATE_FUNCTION bool
___is_id_in_database(lkt_db *db, size_t id)
{
    static const char *SQL = "SELECT id FROM kara WHERE id = ?;";
    sqlite3_stmt *stmt     = NULL;
    bool ret               = false;
    SQLITE_PREPARE(db, stmt, SQL, error);
    SQLITE_BIND_INT(db, stmt, 1, id, error);
    ret = (sqlite3_step(stmt) == SQLITE_ROW);

error:
    sqlite3_finalize(stmt);
    return ret;
}

PRIVATE_FUNCTION void
___flush_cache_from_disk(lkt_db *db, const char *filename)
{
    static const char *SQL = "INSERT OR REPLACE INTO kara_cache (kara_id, file_path)"
                             " SELECT id, file_path"
                             " FROM kara"
                             " WHERE file_path = ?";
    sqlite3_stmt *stmt = NULL;

    SQLITE_PREPARE(db, stmt, SQL, error);
    SQLITE_BIND_TEXT(db, stmt, 1, filename, error);
    SQLITE_STEP_OK(db, stmt, error);
    sqlite3_finalize(stmt);
    return;

error:
    sqlite3_finalize(stmt);
    return;
}

PRIVATE_FUNCTION bool
___database_add_kara(lkt_db *db, const char *filename)
{
    RETURN_UNLESS(db || filename, "Invalid argument", false);
    static const char *SQL_STMT = "INSERT INTO "
                                  "kara (song_name, source_name, category, song_type, language, "
                                  "file_path, is_new, author_name, song_number)"
                                  "SELECT ?, ?, ?, ?, ?, ?, ?, ?, ?";
    static const char *SQL_STMT_WITH_ID = "INSERT INTO "
                                          "kara (song_name, source_name, category, song_type, language, "
                                          "file_path, is_new, author_name, song_number, id)"
                                          "SELECT ?, ?, ?, ?, ?, ?, ?, ?, ?, ?";
    sqlite3_stmt *stmt = NULL;
    long status = false, kara_id = 0;
    struct kara_metadata data;
    char _path[PATH_MAX], *saveptr = NULL, *token, *id = NULL;
    char *path = _path;

    /* Metadata */
    if (kara_metadata_read(&data, filename) != 0) {
        LOG_ERROR("UPDATE", "Failed to get mdt for the kara '%s'", filename);
        return false;
    }

    SQLITE_EXEC(db, "BEGIN TRANSACTION;", error);

    /* Try to find an id in the filename, reuse of variables, it's ugly */
    path[PATH_MAX - 1] = '\0';
    memcpy(path, filename, (strlen(filename) + 1) * sizeof(char));
    while (NULL != (token = strtok_r(path, "/", &saveptr))) {
        id   = token;
        path = NULL;
    }
    size_t id_len = strspn(id, "0123456789");
    id[id_len]    = '\0'; /* Replace the . of .mkv */
    STRTOL(kara_id, id, token, id_len);
    if (!id_len && STR_MATCH(&token[1], "mkv")) {
        /* Check if found id is already in use. Only do that because we are
         * populating the database here. */
        if (___is_id_in_database(db, kara_id)) {
            LOG_WARN("DB",
                     "Detected id %lu for file %s is already taken, generating a new one. "
                     "Your database may be inconsistent",
                     kara_id, filename);
            goto generate_new_id;
        }

        /* Use the found id, most of the time, normally */
        LOG_INFO("DB", "Found id '%ld' in kara '%s'", kara_id, filename);
        SQLITE_PREPARE(db, stmt, SQL_STMT_WITH_ID, error);
        SQLITE_BIND_INT(db, stmt, 10, (int)kara_id, error);
    } else {
        /* Generate a new id */
    generate_new_id:
        SQLITE_PREPARE(db, stmt, SQL_STMT, error);
    }

    // clang-format off
    if ((sqlite3_bind_text(stmt, 1, data.song_name,   -1, 0) != SQLITE_OK) ||
        (sqlite3_bind_text(stmt, 2, data.source_name, -1, 0) != SQLITE_OK) ||
        (sqlite3_bind_text(stmt, 3, data.category,    -1, 0) != SQLITE_OK) ||
        (sqlite3_bind_text(stmt, 4, data.song_type,   -1, 0) != SQLITE_OK) ||
        (sqlite3_bind_text(stmt, 5, data.language,    -1, 0) != SQLITE_OK) ||
        (sqlite3_bind_text(stmt, 6, filename,         -1, 0) != SQLITE_OK) ||
        (sqlite3_bind_int (stmt, 7, 0)                       != SQLITE_OK) ||
        (sqlite3_bind_text(stmt, 8, data.author_name, -1, 0) != SQLITE_OK) ||
        (sqlite3_bind_int (stmt, 9, data.song_number)        != SQLITE_OK)) {
        LOG_ERROR("DB", "Failed to bind for kara %s: %s", filename, sqlite3_errmsg((sqlite3 *)db));
        goto error;
    }
    // clang-format on
    SQLITE_STEP_DONE(db, stmt, error);
    sqlite3_finalize(stmt);
    stmt = NULL;

    ___flush_cache_from_disk(db, filename);
    if (___add_kara_to_update_job(db, kara_id)) {
        SQLITE_EXEC(db, "COMMIT;", error);
        return true;
    }
error:
    if (stmt != NULL)
        sqlite3_finalize(stmt);
    SQLITE_DO_ROLLBACK(db);
    return status;
}

void
database_update_set_new(lkt_db *db, int id, bool is_new)
{
    sqlite3_stmt *stmt     = NULL;
    static const char *SQL = "UPDATE kara SET is_new = ? WHERE id = ?;";
    SQLITE_PREPARE(db, stmt, SQL, error);
    SQLITE_BIND_INT(db, stmt, 1, is_new, error);
    SQLITE_BIND_INT(db, stmt, 2, id, error);
    SQLITE_STEP_DONE(db, stmt, error);
error:
    sqlite3_finalize(stmt);
}

bool
database_update_add(lkt_db *db, const char *kara_path, struct kara_metadata *mdt, uint64_t id, bool avail)
{
    RETURN_UNLESS(db && kara_path && mdt && id, "Invalid argument", false);
    static const char *SQL_STMT = "INSERT OR REPLACE INTO kara (song_name, source_name, category, "
                                  "song_type, language, file_path, is_new, author_name, "
                                  "song_number, id, available)"
                                  "SELECT ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?";
    sqlite3_stmt *stmt = NULL;
    int ret            = false;

    /* From here we initialize the sqlite stmt. */

    SQLITE_PREPARE(db, stmt, SQL_STMT, error);

    // clang-format off
    if ((sqlite3_bind_text(stmt, 1, mdt->song_name,   -1, 0) != SQLITE_OK) ||
        (sqlite3_bind_text(stmt, 2, mdt->source_name, -1, 0) != SQLITE_OK) ||
        (sqlite3_bind_text(stmt, 3, mdt->category,    -1, 0) != SQLITE_OK) ||
        (sqlite3_bind_text(stmt, 4, mdt->song_type,   -1, 0) != SQLITE_OK) ||
        (sqlite3_bind_text(stmt, 5, mdt->language,    -1, 0) != SQLITE_OK) ||
        (sqlite3_bind_text(stmt, 6, kara_path,        -1, 0) != SQLITE_OK) ||
        (sqlite3_bind_int (stmt, 7, 0)                       != SQLITE_OK) ||
        (sqlite3_bind_text(stmt, 8, mdt->author_name, -1, 0) != SQLITE_OK) ||
        (sqlite3_bind_int (stmt, 9, mdt->song_number)        != SQLITE_OK) ||
        (sqlite3_bind_int (stmt, 10, id)                     != SQLITE_OK) ||
        (sqlite3_bind_int (stmt, 11, avail)                  != SQLITE_OK)) {
        LOG_ERROR("DB", "Failed to bind argument for kara %s: %s", kara_path, sqlite3_errmsg((sqlite3 *)db));
        goto error;
    }
    // clang-format on

    SQLITE_STEP_DONE(db, stmt, error);
    ___flush_cache_from_disk(db, kara_path);
    ret = ___add_kara_to_update_job(db, id);
error:
    sqlite3_finalize(stmt);
    return ret;
}

bool
database_update(lkt_db *db, const char *kara_dir, int check_timestamp)
{
    DIR *d;
    struct dirent *dir;
    char path[PATH_MAX];
    long db_timestamp = 0;
    memset(path, 0, PATH_MAX * sizeof(char));

    if (!(d = opendir(kara_dir))) {
        LOG_ERROR("DB", "Failed to open directory '%s': %s", kara_dir, strerror(errno));
        return false;
    }

    while (NULL != (dir = readdir(d))) {
        strncpy(path, kara_dir, PATH_MAX - 1);
        strncat(path, "/", PATH_MAX - 1);
        strncat(path, dir->d_name, PATH_MAX - 1);

        if (dir->d_type == DT_REG) {
            database_get_update(db, &db_timestamp, NULL, NULL);
            LOG_INFO("DB", "File is '%s'", path);
            uint64_t mtime = 0u;
            bool mtime_got = database_get_kara_mtime_path(db, path, &mtime);
            if (check_timestamp && mtime_got && mtime < (uint64_t)db_timestamp && /* Timestamp check */
                (db_timestamp == (long)(uint64_t)db_timestamp)) {                 /* Overflow check */
                LOG_INFO("DB", "Skip update of kara '%s' be cause of timestamps", path);
                continue;
            }
            if (!___database_add_kara(db, path))
                LOG_WARN("DB", "Failed to add kara with path '%s' to db", path);
            database_stamp(db);
        }

        else if (dir->d_type == DT_DIR && !STR_MATCH(dir->d_name, ".") && !STR_MATCH(dir->d_name, ".."))
            database_update(db, path, check_timestamp);
    }

    LOG_INFO("UPDATE", "Passed directory '%s'", kara_dir);
    database_updated(db);
    closedir(d);
    return true;
}

void
database_deleted_kara(lkt_db *db, int **kara_id, size_t *len)
{
    static const char *SQL = "SELECT kara.id, file_path FROM kara WHERE kara.id NOT IN"
                             "(SELECT kara_id FROM updates JOIN misc ON job = update_job);";
    sqlite3_stmt *stmt = NULL;
    *kara_id           = LKT_ALLOC_ARRAY(int, LKT_DEFAULT_LIST_SIZE);
    *len               = 0;
    RETURN_UNLESS(*kara_id, "Out of memory", NOTHING);
    SQLITE_PREPARE(db, stmt, SQL, out);
    size_t size = LKT_DEFAULT_LIST_SIZE;
    void *new;

    while (sqlite3_step(stmt) == SQLITE_ROW) {
        if (size == *len) {
            new = realloc(*kara_id, size * 2 * sizeof(int));
            GOTO_UNLESS(new, "Out of memory", out);
            size *= 2;
            *kara_id = new;
        }
        (*kara_id)[(*len)++] = sqlite3_column_int(stmt, 0);
    }

out:
    sqlite3_finalize(stmt);
    return;
}

void
database_get_update(lkt_db *db, long *timestamp, long *job, int *current)
{
    sqlite3_stmt *stmt     = NULL;
    static const char *SQL = "SELECT last_update, update_job, last_update > last_end_update "
                             "FROM misc WHERE id = 42;";
    SQLITE_PREPARE(db, stmt, SQL, error);
    SQLITE_STEP_ROW(db, stmt, error);
    if (timestamp)
        *timestamp = sqlite3_column_int(stmt, 0);
    if (job)
        *job = sqlite3_column_int(stmt, 1);
    if (current)
        *current = sqlite3_column_int(stmt, 2);
    sqlite3_finalize(stmt);
    return;
error:
    LOG_WARN("DB", "Failed to get informations about the last update: %s", sqlite3_errmsg((sqlite3 *)db));
    sqlite3_finalize(stmt);
}

void
database_update_touch(lkt_db *db, int id)
{
    ___add_kara_to_update_job(db, id);
}

void
database_update_del(lkt_db *db, int id)
{
    static const char *SQL = "DELETE FROM kara WHERE id = ?;";
    sqlite3_stmt *stmt     = NULL;
    SQLITE_PREPARE(db, stmt, SQL, error);
    SQLITE_BIND_INT(db, stmt, 1, id, error);
    SQLITE_STEP_DONE(db, stmt, error);
    LOG_WARN("DB", "Deleted kara with id '%d' from database", id);
error:
    sqlite3_finalize(stmt);
    return;
}

bool
database_stats(lkt_db *db, int *authors, int *sources, int *karas)
{
    static const char *SQL = "SELECT"
                             " (SELECT COUNT(DISTINCT author_name) FROM kara),"
                             " (SELECT COUNT(DISTINCT source_name) FROM kara),"
                             " (SELECT COUNT(id) FROM kara);";
    sqlite3_stmt *stmt = NULL;
    bool ret_code      = false;

    SQLITE_PREPARE(db, stmt, SQL, error);
    SQLITE_STEP_ROW(db, stmt, error);

    *authors = sqlite3_column_int(stmt, 0);
    *sources = sqlite3_column_int(stmt, 1);
    *karas   = sqlite3_column_int(stmt, 2);
    ret_code = true;

error:
    sqlite3_finalize(stmt);
    return ret_code;
}

LKT_DATABASE_VERSION
database_get_version(lkt_db *db)
{
    static const char *SQL   = "SELECT version FROM " LKT_PROTECTED_DATABASE ".misc;";
    sqlite3_stmt *stmt       = NULL;
    LKT_DATABASE_VERSION ret = LKT_DATABASE_VERSION_ALPHA;

    SQLITE_PREPARE(db, stmt, SQL, error);
    SQLITE_STEP_ROW(db, stmt, error);
    const char *database_version = sqlite3_column_chars(stmt, 0);

    LOG_DEBUG("DB", "Got version %s", database_version);

    if (NULL == database_version)
        ret = LKT_DATABASE_VERSION_ALPHA;
    else if (STR_MATCH("mk-7.1", database_version))
        ret = LKT_DATABASE_VERSION_MK_7_1;

error:
    sqlite3_finalize(stmt);
    return ret;
}

#define sqlite_just_exec(func, query)                                                                                  \
    void func(lkt_db *db)                                                                                              \
    {                                                                                                                  \
        SQLITE_EXEC(db, query, error);                                                                                 \
    error:                                                                                                             \
        return;                                                                                                        \
    }
// clang-format off
sqlite_just_exec(database_stamp,   "UPDATE misc SET last_update = strftime('%s','now');")
sqlite_just_exec(database_updated, "UPDATE misc SET last_end_update = strftime('%s','now'), update_job = update_job + 1;")
// clang-format on
#undef sqlite_just_exec