path: root/src/database_interaction.c

/* SPDX-License-Identifier: Apache-2.0 */

/**
 * Copyright 2026 Thorsten Töpper
 *
 * The database contains those tables:
 * - filenames
 *   -> id INTEGER PRIMARY KEY
 *   -> name TEXT
 * - paths
 *   -> id INTEGER PRIMARY KEY
 *   -> pathname TEXT
 * - fileinfo
 *   -> id INTEGER PRIMARY KEY
 *   -> p_id INTEGER
 *   -> fn_id INTEGER
 *   -> h_id INTEGER
 *   -> size INTEGER
 *   -> last_seen INTEGER
 *   -> stat_bin BLOB
 * - hashes
 *   -> id INTEGER PRIMARY KEY
 *   -> blake2 TEXT
 *   -> sha256 TEXT
 *   -> sha512 TEXT
 *
 * @file database_interaction.c
 *
 * vim:ts=4:sw=4:expandtab
 */
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <unistd.h>
#include <stdbool.h>
#include <stdint.h>
#include <errno.h>
#include <time.h>

#include <sqlite3.h>

#include "options.h"
#include "database_interaction.h"
#include "trace_macros.h"
#include "file_processor.h"


/*=========== DEFINES, CONSTANTS AND TYPES ===========*/

/*=========== GLOBAL VARIABLES ===========*/
sqlite3 *dbconn = NULL;

/* The statements will be wrapped via a function dbi_STATEMENTNAME() for the outside */
sqlite3_stmt *select_filename_by_id,
             *select_filename_by_name,
             *select_filename_all_ids,
             *select_filename_complete_table,
             *select_path_by_id,
             *select_path_by_pathname,
             *select_hashes_all_ids,
             *select_hashes_by_id,
             *select_hashes_by_strings,
             *select_hashes_complete_table,
             *select_fileinfo_by_id,
             *select_fileinfo_by_id_resolved,
             *select_fileinfo_by_path_id,
             *select_fileinfo_by_filename_id,
             *select_fileinfo_by_filename_id_resolved,
             *select_fileinfo_by_path_filename_ids,
             *select_fileinfo_by_hash_path_filename_ids,
             *select_fileinfo_by_hash_id,
             *select_fileinfo_by_hash_id_key_only,
             *select_fileinfo_by_hash_id_resolved,
             *select_fileinfo_complete_table,
             *select_fileinfo_complete_table_resolved;

sqlite3_stmt *select_full_path;

sqlite3_stmt *insert_filename,
             *insert_pathname,
             *insert_hashes,
             *insert_fileinfo;

sqlite3_stmt *update_fileinfo_last_seen,
             *update_fileinfo_complete;

sqlite3_stmt *delete_fileinfo_by_id;

sqlite3_stmt *count_fileinfo_by_hash_id,
             *count_fileinfo_by_filename,
             *count_filenames,
             *count_hashes,
             *count_fileinfo;

/*=========== FUNCTIONS ===========*/
void create_tables();
int prepare_statements();
char *select_string_by_int(sqlite3_stmt *st, int64_t id);
int64_t call_count_query(sqlite3_stmt *st);
int64_t *call_select_all_ids(sqlite3_stmt *all_ids, sqlite3_stmt *count_query);

/* Writing this block way too often */
#define DBCONN_CHECK(x) \
    if (dbconn==NULL){ LOGERR("ERROR: No database connection.\n");\
        return x; }



bool dbi_open(char *filename) {
    if (filename == NULL || filename[0] == '\0') {
        LOGERR("ERROR: No valid filename given.\n");
        return false;
    }

    if (dbconn != NULL) {
        LOGERR("ERROR: There's already an open database\n");
        return false;
    }

    if (sqlite3_open(filename, &dbconn) != SQLITE_OK) {
        LOGERR("ERROR: Failed to open database: %s\n",
                sqlite3_errmsg(dbconn));
        sqlite3_close(dbconn);
        dbconn = NULL;
        return false;
    }

    create_tables();
    if (prepare_statements() != 0) {
        return false;
    }

    return true;
}

void dbi_close() {
    /* TODO: sqlite3_finalize for all prepared statements */
#define LOCAL_FINALIZE(x) { sqlite3_finalize(x); x=NULL; }

    DBCONN_CHECK();

    LOCAL_FINALIZE(select_filename_all_ids);
    LOCAL_FINALIZE(select_filename_by_id);
    LOCAL_FINALIZE(select_filename_by_name);
    LOCAL_FINALIZE(select_filename_complete_table);
    LOCAL_FINALIZE(select_path_by_id);
    LOCAL_FINALIZE(select_path_by_pathname);
    LOCAL_FINALIZE(select_hashes_all_ids);
    LOCAL_FINALIZE(select_hashes_by_id);
    LOCAL_FINALIZE(select_hashes_by_strings);
    LOCAL_FINALIZE(select_hashes_complete_table);
    LOCAL_FINALIZE(select_fileinfo_by_id);
    LOCAL_FINALIZE(select_fileinfo_by_id_resolved);
    LOCAL_FINALIZE(select_fileinfo_by_path_id);
    LOCAL_FINALIZE(select_fileinfo_by_filename_id);
    LOCAL_FINALIZE(select_fileinfo_by_filename_id_resolved);
    LOCAL_FINALIZE(select_fileinfo_by_path_filename_ids);
    LOCAL_FINALIZE(select_fileinfo_by_hash_id);
    LOCAL_FINALIZE(select_fileinfo_by_hash_id_resolved);
    LOCAL_FINALIZE(select_fileinfo_by_hash_id_key_only);
    LOCAL_FINALIZE(select_fileinfo_complete_table);
    LOCAL_FINALIZE(select_fileinfo_complete_table_resolved);

    LOCAL_FINALIZE(select_full_path);
    LOCAL_FINALIZE(insert_filename);
    LOCAL_FINALIZE(insert_pathname);
    LOCAL_FINALIZE(insert_hashes);
    LOCAL_FINALIZE(insert_fileinfo);

    LOCAL_FINALIZE(update_fileinfo_last_seen);
    LOCAL_FINALIZE(update_fileinfo_complete);

    LOCAL_FINALIZE(delete_fileinfo_by_id);

    LOCAL_FINALIZE(count_fileinfo);
    LOCAL_FINALIZE(count_fileinfo_by_hash_id);
    LOCAL_FINALIZE(count_fileinfo_by_filename);
    LOCAL_FINALIZE(count_filenames);
    LOCAL_FINALIZE(count_hashes);
#undef LOCAL_FINALIZE

    sqlite3_close(dbconn);
    dbconn = NULL;
}


/**
 * Create the later used tables if they don't exist yet
 */
inline void create_tables() {
    char *err = NULL;

    sqlite3_exec(dbconn, "CREATE TABLE IF NOT EXISTS filenames ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT UNIQUE);", NULL, NULL, &err);
    if (err != NULL) {
        LOGERR("ERROR: Creation of table filenames failed: %s\n", err);
        sqlite3_free(err);
        err = NULL;
    }

    sqlite3_exec(dbconn, "CREATE TABLE IF NOT EXISTS paths ( id INTEGER PRIMARY KEY AUTOINCREMENT, pathname TEXT UNIQUE);", NULL, NULL, &err);
    if (err != NULL) {
        LOGERR("ERROR: Creation of table pathss failed: %s\n", err);
        sqlite3_free(err);
        err = NULL;
    }

    /* no UNIQUE here, as even for the rare case of a hash collission in a single algorithm, all three won't collide at the same time. */
    sqlite3_exec(dbconn, "CREATE TABLE IF NOT EXISTS hashes ( id INTEGER PRIMARY KEY AUTOINCREMENT, blake2 TEXT, sha256 TEXT, sha512 TEXT );", NULL, NULL, &err);
    if (err != NULL) {
        LOGERR("ERROR: Creation of table hashes failed: %s\n", err);
        sqlite3_free(err);
        err = NULL;
    }
    sqlite3_exec(dbconn, "CREATE TABLE IF NOT EXISTS fileinfo ( id INTEGER PRIMARY KEY, p_id INTEGER, "
            "fn_id INTEGER, h_id INTEGER, size INTEGER, last_seen INTEGER, stat_struct BLOB, "
            "FOREIGN KEY(p_id) REFERENCES paths(id), FOREIGN KEY(fn_id) REFERENCES filenames(id), "
            "FOREIGN KEY(h_id) REFERENCES hashes(id));", NULL, NULL, &err);
    if (err != NULL) {
        LOGERR("ERROR: Creation of table fileinfo failed: %s\n", err);
        sqlite3_free(err);
        err = NULL;
    }
}

int prepare_statements() {
    int counter = 0;
    /* Error handling in KISS. */
#define LOCAL_PREP_STMT(q, s) { counter++; \
    if ((sqlite3_prepare_v2(dbconn, q, -1, s, NULL)) != SQLITE_OK) { \
        LOGERR("ERROR: Failed to prepare statement %d '%s': %s\n", \
        counter, q, sqlite3_errmsg(dbconn)); return -1; } \
    }

    /* SELECT */
    LOCAL_PREP_STMT("SELECT name FROM filenames WHERE id = ? ;", &select_filename_by_id);
    LOCAL_PREP_STMT("SELECT id FROM filenames WHERE name = ? ;", &select_filename_by_name);
    LOCAL_PREP_STMT("SELECT id FROM filenames ;", &select_filename_all_ids);
    LOCAL_PREP_STMT("SELECT * FROM filenames;", &select_filename_complete_table);

    LOCAL_PREP_STMT("SELECT pathname FROM paths WHERE id = ? ;", &select_path_by_id);
    LOCAL_PREP_STMT("SELECT id FROM paths WHERE pathname = ? ;", &select_path_by_pathname);

    LOCAL_PREP_STMT("SELECT hashes.id FROM hashes ;", &select_hashes_all_ids);
    LOCAL_PREP_STMT("SELECT blake2, sha256, sha512 FROM hashes WHERE id = ? ;", &select_hashes_by_id);
    LOCAL_PREP_STMT("SELECT id FROM hashes WHERE blake2 = ? AND sha256 = ? AND sha512 = ? ;", &select_hashes_by_strings);
    LOCAL_PREP_STMT("SELECT * FROM hashes;", &select_hashes_complete_table);


    LOCAL_PREP_STMT("SELECT * FROM fileinfo WHERE id = ? ;", &select_fileinfo_by_id);
    LOCAL_PREP_STMT("SELECT * FROM fileinfo WHERE p_id = ? ;", &select_fileinfo_by_path_id);
    LOCAL_PREP_STMT("SELECT * FROM fileinfo WHERE fn_id = ? ;", &select_fileinfo_by_filename_id);
    LOCAL_PREP_STMT("SELECT * FROM fileinfo WHERE p_id = ? AND fn_id = ? ;", &select_fileinfo_by_path_filename_ids);
    LOCAL_PREP_STMT("SELECT * FROM fileinfo WHERE h_id = ? AND p_id = ? AND fn_id = ? ;", &select_fileinfo_by_hash_path_filename_ids);
    LOCAL_PREP_STMT("SELECT * FROM fileinfo WHERE h_id = ? ;", &select_fileinfo_by_hash_id);
    LOCAL_PREP_STMT("SELECT id FROM fileinfo WHERE h_id = ? ;", &select_fileinfo_by_hash_id_key_only);

    /* TODO: so far the only query with JOINs or masking it in another way?
     * Many years since
     */
    LOCAL_PREP_STMT("SELECT paths.pathname, filenames.name, hashes.blake2, hashes.sha256, hashes.sha512, fileinfo.size, fileinfo.last_seen, fileinfo.stat_struct FROM fileinfo INNER JOIN paths ON fileinfo.p_id = paths.id INNER JOIN filenames ON fileinfo.fn_id = filenames.id INNER JOIN hashes ON fileinfo.h_id = hashes.id WHERE fileinfo.id = ? ;", &select_fileinfo_by_id_resolved);
    LOCAL_PREP_STMT("SELECT paths.pathname, filenames.name, hashes.blake2, hashes.sha256, hashes.sha512, fileinfo.size, fileinfo.last_seen, fileinfo.stat_struct FROM fileinfo INNER JOIN paths ON fileinfo.p_id = paths.id INNER JOIN filenames ON fileinfo.fn_id = filenames.id INNER JOIN hashes ON fileinfo.h_id = hashes.id ;", &select_fileinfo_complete_table_resolved);
    LOCAL_PREP_STMT("SELECT paths.pathname, filenames.name, hashes.blake2, hashes.sha256, hashes.sha512, fileinfo.size, fileinfo.last_seen, fileinfo.stat_struct FROM fileinfo INNER JOIN paths ON fileinfo.p_id = paths.id INNER JOIN filenames ON fileinfo.fn_id = filenames.id INNER JOIN hashes ON fileinfo.h_id = hashes.id WHERE fileinfo.h_id = ?;", &select_fileinfo_by_hash_id_resolved);
    LOCAL_PREP_STMT("SELECT paths.pathname, filenames.name, hashes.blake2, hashes.sha256, hashes.sha512, fileinfo.size, fileinfo.last_seen, fileinfo.stat_struct FROM fileinfo INNER JOIN paths ON fileinfo.p_id = paths.id INNER JOIN filenames ON fileinfo.fn_id = filenames.id INNER JOIN hashes ON fileinfo.h_id = hashes.id WHERE fileinfo.fn_id = ?;", &select_fileinfo_by_filename_id_resolved);
    LOCAL_PREP_STMT("SELECT p_id, fn_id, h_id, size, last_seen, stat_struct FROM fileinfo ;", &select_fileinfo_complete_table);

    LOCAL_PREP_STMT("SELECT paths.pathname, filenames.name FROM fileinfo INNER JOIN paths ON fileinfo.p_id = paths.id INNER JOIN filenames ON fileinfo.fn_id = filenames.id ;", &select_full_path);

    /* INSERT */
    LOCAL_PREP_STMT("INSERT INTO filenames (name) VALUES (?);", &insert_filename);
    LOCAL_PREP_STMT("INSERT INTO paths (pathname) VALUES (?);", &insert_pathname);
    LOCAL_PREP_STMT("INSERT INTO hashes (blake2, sha256, sha512) VALUES (?, ?, ?);", &insert_hashes);
    LOCAL_PREP_STMT("INSERT INTO fileinfo (p_id, fn_id, h_id, size, last_seen, stat_struct) "
            "VALUES (?, ?, ?, ?, ?, ?);", &insert_fileinfo);

    /* UPDATE */
    LOCAL_PREP_STMT("UPDATE fileinfo SET last_seen = @time WHERE id = @id ;", &update_fileinfo_last_seen);
    LOCAL_PREP_STMT("UPDATE fileinfo SET p_id = @pid , fn_id = @fnid , h_id = @hid , "
            "size = @sz , last_seen = @ls, stat_struct = @stat WHERE id = @id ;", &update_fileinfo_complete);

    /* DELETE */
    LOCAL_PREP_STMT("DELETE FROM fileinfo WHERE id = ? ;", &delete_fileinfo_by_id);

    /* COUNT */
    LOCAL_PREP_STMT("SELECT COUNT(fileinfo.id) FROM fileinfo;", &count_fileinfo);
    LOCAL_PREP_STMT("SELECT COUNT(fileinfo.h_id) FROM fileinfo WHERE fileinfo.h_id = ?;", &count_fileinfo_by_hash_id);
    LOCAL_PREP_STMT("SELECT COUNT(fileinfo.fn_id) FROM fileinfo WHERE fileinfo.fn_id = ?;", &count_fileinfo_by_filename);
    LOCAL_PREP_STMT("SELECT COUNT(hashes.id) FROM hashes ;", &count_hashes);
    LOCAL_PREP_STMT("SELECT COUNT(filenames.id) FROM filenames ;", &count_filenames);

#undef LOCAL_PREP_STMT
    return 0;
}

/**
 * To be wrapped for simple SELECT text ... WHERE PK = id; statements.
 * @param st A prepared statement
 * @param id A 64 bit integer used as primary key in the query.
 * @return NULL in case of error
 *      copy of the database content
 */
char *select_string_by_int(sqlite3_stmt *st, int64_t id) {
    char *result = NULL;
    int strc = 0;
    const char *text;

    DBCONN_CHECK(NULL);

    if (st == NULL) {
        LOGERR("ERROR: No prepared statement.\n");
        return NULL;
    }

    if (id < 1) {
        //LOGERR("ERROR: Invalid id %" PRId64 "\n", id);
        LOGERR("ERROR: Invalid id %ld\n", id);
        return NULL;
    }

    if (sqlite3_bind_int64(st, 1, id) != SQLITE_OK) {
        // LOGERR("ERROR: Failed to bind id %" PRId64 " to prepared statement: %s\n", sqlite3_errmsg(dbconn));
        LOGERR("ERROR: Failed to bind id %ld to prepared statement: %s\n", id, sqlite3_errmsg(dbconn));
        return NULL;
    }

    strc = sqlite3_step(st);
    /* Dont' forget: the sqlite3_reset() call must be executed! */
    if (strc == SQLITE_ROW) {
        text = (const char *)sqlite3_column_text(st, 0);
        if ((result = calloc((strlen(text)+1), sizeof(char))) == NULL) {
            LOGERR("ERROR: Failed to allocate memory for copy of query result.\n");
            sqlite3_reset(st);
            return NULL;
        }
        memcpy(result, text, strlen(text));
    } else if (strc != SQLITE_DONE) {
        LOGERR("ERROR: Failed step: %s\n", sqlite3_errmsg(dbconn));
    }

    sqlite3_clear_bindings(st);
    sqlite3_reset(st);

    return result;
}

/**
 *
 * @return 0 if ok, <0 in case of error
 */
int insert_text(sqlite3_stmt *st, int64_t (*check_function)(const char*), const char *text) {
    int strc = 0;

    DBCONN_CHECK(-1);

    if (st == NULL) {
        LOGERR("ERROR: No prepared statement.\n");
        return -1;
    }

    if (text == NULL) {
        LOGERR("ERROR: No content to insert.\n");
        return -1;
    }

    /* CHECK WHETHER THE ENTRY ALREADY EXISTS! */
    if ((check_function != NULL) && (*check_function)(text) > 0) {
        return 0;
    }

    if (sqlite3_bind_text(st, 1, text, -1, SQLITE_TRANSIENT) != SQLITE_OK) {
        LOGERR("ERROR: Failed to bind text '%s' to statement: %s\n", text, sqlite3_errmsg(dbconn));
        return -1;
    }

    strc = sqlite3_step(st);
    if (strc != SQLITE_DONE) {
        LOGERR("ERROR: Failed to insert text '%s' into db: %s\n", text, sqlite3_errmsg(dbconn));
    }

    sqlite3_clear_bindings(st);
    sqlite3_reset(st);

    return (strc == SQLITE_DONE) ? 0 : -1;
}

/**
 * To be wrapped for simple SELECT text ... WHERE COL = string; statements COL being UNIQUE.
 * @param st A prepared statement
 * @param id a string bound to the WHERE in the statement
 * @return < -1 in case of error
 *         0 if not found
 *        >0 the id in the database
 */
int64_t select_int_by_string(sqlite3_stmt *st, const char *s) {
    int64_t result = -1;
    int strc = 0;

    DBCONN_CHECK(-2);

    if (st == NULL) {
        LOGERR("ERROR: No prepared statement.\n");
        return -2;
    }

    if (s == NULL || strlen(s)==0) {
        LOGERR("ERROR: Invalid string %s\n", s);
        return -2;
    }


    sqlite3_clear_bindings(st);
    sqlite3_reset(st);

    if (sqlite3_bind_text(st, 1, s, -1, SQLITE_TRANSIENT) != SQLITE_OK) {
        LOGERR("ERROR: Failed to bind string %s to prepared statement: %s\n", s, sqlite3_errmsg(dbconn));
        return -2;
    }

    strc = sqlite3_step(st);
    /* Dont' forget: the sqlite3_reset() call must be executed! */
    if (strc == SQLITE_ROW) {
        result = (int64_t) sqlite3_column_int64(st, 0);
    } else if (strc != SQLITE_DONE) {
        LOGERR("ERROR: Failed step: %s\n", sqlite3_errmsg(dbconn));
        result = -2;
    } else { /* SQLITE_DONE => EMPTY */
        DBGTRC("DEBUG: Combination not found in db\n");
        result = 0;
    }

    sqlite3_clear_bindings(st);
    sqlite3_reset(st);

    return result;
}



char *dbi_select_filename_by_id(int64_t id) {
    return select_string_by_int(select_filename_by_id, id);
}

char *dbi_select_path_by_id(int64_t id) {
    return select_string_by_int(select_path_by_id, id);
}

int64_t dbi_select_filename_by_name(const char *name) {
    return select_int_by_string(select_filename_by_name, name);
}

int64_t dbi_select_path_by_pathname(const char *pathname) {
    return select_int_by_string(select_path_by_pathname, pathname);
}


int dbi_insert_filename(const char *filename) {
    return insert_text(insert_filename, dbi_select_filename_by_name, filename);
}

int dbi_insert_pathname(const char *path) {
    return insert_text(insert_pathname, dbi_select_path_by_pathname, path);
}

int64_t dbi_select_hashes_by_strings(const char *blake2, const char *sha256, const char *sha512) {
    int64_t result = 0;
    int strc = 0;
    sqlite3_stmt *st = select_hashes_by_strings;

    DBCONN_CHECK(-2);

    if (blake2 == NULL || sha256 == NULL || sha512 == NULL ||
        strlen(blake2)==0 || strlen(sha256)==0 || strlen(sha512)==0) {
        LOGERR("ERROR: Invalid argument: blake2=%s sha256=%s sha512=%s\n",
                blake2, sha256, sha512);
        return -2;
    }

    /* Avoid conflict with earlier calls */
    sqlite3_clear_bindings(st);
    sqlite3_reset(st);

    if (sqlite3_bind_text(st, 1, blake2, -1, SQLITE_TRANSIENT) != SQLITE_OK) {
        LOGERR("ERROR: Failed to bind field blake2 to prepared statement: %s\n", sqlite3_errmsg(dbconn));
        return -2;
    }

    if (sqlite3_bind_text(st, 2, sha256, -1, SQLITE_TRANSIENT) != SQLITE_OK) {
        LOGERR("ERROR: Failed to bind field sha256 to prepared statement: %s\n", sqlite3_errmsg(dbconn));
        return -2;
    }

    if (sqlite3_bind_text(st, 3, sha512, -1, SQLITE_TRANSIENT) != SQLITE_OK) {
        LOGERR("ERROR: Failed to bind field sha512 to prepared statement: %s\n", sqlite3_errmsg(dbconn));
        return -2;
    }

    strc = sqlite3_step(st);
    /* Dont' forget: the sqlite3_reset() call must be executed! */
    if (strc == SQLITE_ROW) {
        result = (int64_t) sqlite3_column_int64(st, 0);
    } else if (strc != SQLITE_DONE) {
        LOGERR("ERROR: Failed step: %s\n", sqlite3_errmsg(dbconn));
        result = -2;
    } else { /* SQLITE_DONE => EMPTY */
        DBGTRC("DEBUG: Combination not found in db\n");
        result = 0;
    }

    sqlite3_clear_bindings(st);
    sqlite3_reset(st);

    return result;
}

int dbi_insert_hashes(const char *blake2, const char *sha256, const char *sha512) {
    int rc = 0;
    int64_t strc = 0;
    sqlite3_stmt *st = insert_hashes;

    DBCONN_CHECK(-2);

    if (blake2 == NULL || sha256 == NULL || sha512 == NULL ||
        strlen(blake2)==0 || strlen(sha256)==0 || strlen(sha512)==0) {
        LOGERR("ERROR: Invalid argument: blake2=%s sha256=%s sha512=%s\n",
                blake2, sha256, sha512);
        return -2;
    }

    if (dbi_select_hashes_by_strings(blake2, sha256, sha512) > 0) {
        return 0;
    }

    /* Avoid conflict with earlier calls */
    sqlite3_clear_bindings(st);
    sqlite3_reset(st);

    if (sqlite3_bind_text(st, 1, blake2, -1, SQLITE_TRANSIENT) != SQLITE_OK) {
        LOGERR("ERROR: Failed to bind field blake2 to prepared statement: %s\n", sqlite3_errmsg(dbconn));
        return -2;
    }

    if (sqlite3_bind_text(st, 2, sha256, -1, SQLITE_TRANSIENT) != SQLITE_OK) {
        LOGERR("ERROR: Failed to bind field sha256 to prepared statement: %s\n", sqlite3_errmsg(dbconn));
        return -2;
    }

    if (sqlite3_bind_text(st, 3, sha512, -1, SQLITE_TRANSIENT) != SQLITE_OK) {
        LOGERR("ERROR: Failed to bind field sha512 to prepared statement: %s\n", sqlite3_errmsg(dbconn));
        return -2;
    }

    strc = sqlite3_step(st);
    if (strc != SQLITE_DONE) {
        LOGERR("ERROR: Failed to insert hashes (blake2=%s, sha256=%s, sha512=%s) into db: %s\n",
                blake2, sha256, sha512, sqlite3_errmsg(dbconn));
        rc = -2;
    } else {
        rc = 0;
    }

    sqlite3_clear_bindings(st);
    sqlite3_reset(st);

    return rc;
}

struct df_hashstrings *dbi_select_hashes_by_id(int64_t id) {
    struct df_hashstrings *result = NULL;
    int strc = 0;
    sqlite3_stmt *st = select_hashes_by_id;
    const char *text;

    DBCONN_CHECK(NULL);

    if (id < 1) {
        LOGERR("ERROR: invalid id %lld\n", (long long int)id); /* TODO: Macro resolve not ok */
        return NULL;
    }

    if (sqlite3_bind_int64(st, 1, id) != SQLITE_OK) {
        // LOGERR("ERROR: Failed to bind id %" PRId64 " to prepared statement: %s\n", sqlite3_errmsg(dbconn));
        LOGERR("ERROR: Failed to bind id %ld to prepared statement: %s\n", id, sqlite3_errmsg(dbconn));
        return NULL;
    }

    strc = sqlite3_step(st);
    /* Dont' forget: the sqlite3_reset() call must be executed! */
    if (strc == SQLITE_ROW) {
        if ((result = calloc(1, sizeof(struct df_hashstrings))) == NULL) {
            LOGERR("ERROR: Failed to allocate memory for copy of query result.\n");
            sqlite3_reset(st);
            return NULL;
        }
        text = (const char *)sqlite3_column_text(st, 0);
        memcpy(result->blake2, text, strlen(text));
        text = (const char *)sqlite3_column_text(st, 1);
        memcpy(result->sha256, text, strlen(text));
        text = (const char *)sqlite3_column_text(st, 2);
        memcpy(result->sha512, text, strlen(text));
    } else if (strc != SQLITE_DONE) {
        LOGERR("ERROR: Failed step: %s\n", sqlite3_errmsg(dbconn));
    }

    sqlite3_clear_bindings(st);
    sqlite3_reset(st);
    return result;
}


int64_t dbi_select_fileinfo_by_hash_path_filename_ids(int64_t hash_id, int64_t path_id, int64_t filename_id) {
    int64_t result = 0;
    int strc = 0;
    sqlite3_stmt *st = select_fileinfo_by_hash_path_filename_ids;

    DBCONN_CHECK(-2);

    sqlite3_clear_bindings(st);
    sqlite3_reset(st);

    if ( hash_id < 1 || path_id < 1 || filename_id < 1 ) {
        LOGERR("ERROR: At least one invalid id: hashes %ld | path %ld | filename %ld\n",
                hash_id, path_id, filename_id);
        return -2;
    }

    if (sqlite3_bind_int64(st, 1, hash_id) != SQLITE_OK) {
        LOGERR("ERROR: Failed to bind hash_id %ld to prepared statement: %s\n", hash_id, sqlite3_errmsg(dbconn));
        return -2;
    }

    if (sqlite3_bind_int64(st, 2, path_id) != SQLITE_OK) {
        LOGERR("ERROR: Failed to bind path_id %ld to prepared statement: %s\n", path_id, sqlite3_errmsg(dbconn));
        return -2;
    }

    if (sqlite3_bind_int64(st, 3, filename_id) != SQLITE_OK) {
        LOGERR("ERROR: Failed to bind filename_id %ld to prepared statement: %s\n", filename_id, sqlite3_errmsg(dbconn));
        return -2;
    }

    strc = sqlite3_step(st);
    /* Dont' forget: the sqlite3_reset() call must be executed! */
    if (strc == SQLITE_ROW) {
        result = (int64_t) sqlite3_column_int64(st, 0);
    } else if (strc != SQLITE_DONE) {
        LOGERR("ERROR: Failed step: %s\n", sqlite3_errmsg(dbconn));
        result = -2;
    } else { /* SQLITE_DONE => EMPTY */
        DBGTRC("DEBUG: Combination not found in db\n");
        result = 0;
    }

    sqlite3_clear_bindings(st);
    sqlite3_reset(st);


    return result;
}

int64_t dbi_select_fileinfo_by_path_filename_ids(int64_t pname_id, int64_t fname_id) {
    int64_t result = 0;
    int strc = 0;
    sqlite3_stmt *st = select_fileinfo_by_path_filename_ids;

    DBCONN_CHECK(-2);

    sqlite3_clear_bindings(st);
    sqlite3_reset(st);

    if ( pname_id < 1 || fname_id < 1 ) {
        LOGERR("ERROR: At least one invalid id: path %ld | filename %ld\n",
                pname_id, fname_id);
        return -2;
    }

    if (sqlite3_bind_int64(st, 1, pname_id) != SQLITE_OK) {
        LOGERR("ERROR: Failed to bind pname_id %ld to prepared statement: %s\n", pname_id, sqlite3_errmsg(dbconn));
        return -2;
    }

    if (sqlite3_bind_int64(st, 2, fname_id) != SQLITE_OK) {
        LOGERR("ERROR: Failed to bind fname_id %ld to prepared statement: %s\n", fname_id, sqlite3_errmsg(dbconn));
        return -2;
    }

    strc = sqlite3_step(st);
    /* Dont' forget: the sqlite3_reset() call must be executed! */
    if (strc == SQLITE_ROW) {
        result = (int64_t) sqlite3_column_int64(st, 0);
    } else if (strc != SQLITE_DONE) {
        LOGERR("ERROR: Failed step: %s\n", sqlite3_errmsg(dbconn));
        result = -2;
    } else { /* SQLITE_DONE => EMPTY */
        DBGTRC("DEBUG: Combination not found in db\n");
        result = 0;
    }

    sqlite3_clear_bindings(st);
    sqlite3_reset(st);

    return result;
}

int dbi_update_fileinfo_last_seen(int64_t id) {
    int rc = -1, strc = 0;
    time_t ts = time(NULL);
    sqlite3_stmt *st = update_fileinfo_last_seen;

    DBCONN_CHECK( -2 );

    if (id < 1) {
        LOGERR("ERROR: Invalid id.\n");
        return -1;
    }

    sqlite3_clear_bindings(st);
    sqlite3_reset(st);

    if (sqlite3_bind_int64(st, 1, ts) != SQLITE_OK) {
        LOGERR("ERROR: Failed to bind last_seen timestamp to prepared statement: %s\n", sqlite3_errmsg(dbconn));
        return -2;
    }
    if (sqlite3_bind_int64(st, 2, id) != SQLITE_OK) {
        LOGERR("ERROR: Failed to bind last_seen timestamp to prepared statement: %s\n", sqlite3_errmsg(dbconn));
        return -2;
    }

    strc = sqlite3_step(st);
    if (strc != SQLITE_DONE) {
        LOGERR("ERROR: Failed to update last_seen timestamp for entry %ld: %s\n", id, sqlite3_errmsg(dbconn));
        rc = -2;
    } else {
        rc = 0;
    }

    sqlite3_clear_bindings(st);
    sqlite3_reset(st);

    return rc;
}

int update_fileinfo_function(struct df_fileinfo *fi, int64_t existing_id,
        int64_t pname_id, int64_t fname_id, int64_t hashes_id) {
    int rc = -1, strc = 0;
    time_t ts = time(NULL);
    sqlite3_stmt *st = update_fileinfo_complete;

    DBCONN_CHECK( -2 );

    if (fi == NULL) {
        LOGERR("ERROR: Invalid argument.\n");
        return -2;
    }

    if (existing_id < 1) {
        /* TODO: ALL possible situations need to be checked */
        if (fname_id < 1) {
            fname_id = dbi_select_filename_by_name(fi->name);
        }

        if (pname_id < 1) {
            pname_id = dbi_select_path_by_pathname(fi->path);
        }
    } else {
        LOGERR("ERROR: No entry given.\n");
        return -2;
    }

    /* Possibly new hashes so always INSERT and use the return which was given */
    if (hashes_id < 1) {
        if (dbi_insert_hashes(fi->hashes.blake2, fi->hashes.sha256, fi->hashes.sha512) < 0) {
            LOGERR("ERROR: abort due to previous error.\n");
        return -2;
        }
        hashes_id = dbi_select_hashes_by_strings(fi->hashes.blake2, fi->hashes.sha256, fi->hashes.sha512);
    }

    sqlite3_clear_bindings(st);
    sqlite3_reset(st);

    if (sqlite3_bind_int64(st, 1, pname_id) != SQLITE_OK) {
        LOGERR("ERROR: Failed to bind path_id to prepared statement: %s\n", sqlite3_errmsg(dbconn));
        return -2;
    }
    if (sqlite3_bind_int64(st, 2, fname_id) != SQLITE_OK) {
        LOGERR("ERROR: Failed to bind filename_id to prepared statement: %s\n", sqlite3_errmsg(dbconn));
        return -2;
    }
    if (sqlite3_bind_int64(st, 3, hashes_id) != SQLITE_OK) {
        LOGERR("ERROR: Failed to bind filename_id to prepared statement: %s\n", sqlite3_errmsg(dbconn));
        return -2;
    }
    if (sqlite3_bind_int64(st, 4, fi->statbuf.st_size) != SQLITE_OK) {
        LOGERR("ERROR: Failed to bind size to prepared statement: %s\n", sqlite3_errmsg(dbconn));
        return -2;
    }
    if (sqlite3_bind_int64(st, 5, ts) != SQLITE_OK) {
        LOGERR("ERROR: Failed to bind last_seen timestamp to prepared statement: %s\n", sqlite3_errmsg(dbconn));
        return -2;
    }
    if (sqlite3_bind_blob(st, 6, &(fi->statbuf), sizeof(struct stat), SQLITE_TRANSIENT) != SQLITE_OK) {
        LOGERR("ERROR: Failed to bind last_seen timestamp to prepared statement: %s\n", sqlite3_errmsg(dbconn));
        return -2;
    }

    strc = sqlite3_step(st);
    if (strc != SQLITE_DONE) {
        LOGERR("ERROR: Failed to completely update entry %ld: %s\n", existing_id, sqlite3_errmsg(dbconn));
        rc = -2;
    } else {
        rc = 0;
    }

    sqlite3_clear_bindings(st);
    sqlite3_reset(st);

    return rc;
}

int dbi_update_fileinfo_complete(struct df_fileinfo *fi, int64_t existing_id) {
    return update_fileinfo_function(fi, existing_id, -1, -1, -1);
}

/**
 * Wrapper function around several other inserts
 */
int dbi_insert_fileinfo(struct df_fileinfo *fi) {
    int rc = 0, strc = 0;
    int64_t fname_id, pname_id, hashes_id, existing_entry = 0;
    time_t ts = 0;
    sqlite3_stmt *st = insert_fileinfo;


    DBCONN_CHECK(-2);

    if (fi == NULL) {
        LOGERR("ERROR: No fileinfo given.\n");
        return -2;
    }

    if (dbi_insert_filename(fi->name) < 0) {
        LOGERR("ERROR: abort due to previous error.\n");
        return -2;
    }
    fname_id = dbi_select_filename_by_name(fi->name);

    if (dbi_insert_pathname(fi->path) < 0) {
        LOGERR("ERROR: abort due to previous error.\n");
        return -2;
    }
    pname_id = dbi_select_path_by_pathname(fi->path);


    /* TODO: Take some time and decide whether it shall stay like this or hand over the struct */
    if (dbi_insert_hashes(fi->hashes.blake2, fi->hashes.sha256, fi->hashes.sha512) < 0) {
        LOGERR("ERROR: abort due to previous error.\n");
        return -2;
    }
    hashes_id = dbi_select_hashes_by_strings(fi->hashes.blake2, fi->hashes.sha256, fi->hashes.sha512);

    /* Any problems with the selects? */
    if (fname_id <0 || pname_id <0 || hashes_id <0) {
        LOGERR("ERROR: abort due to previous error.\n");
        return -2;
    }

    ts = time(NULL);

    /* TODO: There also belongs a query whether the fullpath already has an entry, if so and hash_id
     * is different, an UPDATE and not an insert is required.
     */
    existing_entry = dbi_select_fileinfo_by_hash_path_filename_ids(hashes_id, pname_id, fname_id);
    if (existing_entry > 0) {
        return dbi_update_fileinfo_last_seen(existing_entry);
    }

    /* fullpath entry exists, but the hashes mismatch. */
    existing_entry = dbi_select_fileinfo_by_path_filename_ids(pname_id, fname_id);
    if (existing_entry > 0) {
        return update_fileinfo_function(fi, existing_entry, pname_id, fname_id, hashes_id);
    }

    sqlite3_clear_bindings(st);
    sqlite3_reset(st);

    if (sqlite3_bind_int64(st, 1, pname_id) != SQLITE_OK) {
        LOGERR("ERROR: Failed to bind path_id to prepared statement: %s\n", sqlite3_errmsg(dbconn));
        return -2;
    }
    if (sqlite3_bind_int64(st, 2, fname_id) != SQLITE_OK) {
        LOGERR("ERROR: Failed to bind filename_id to prepared statement: %s\n", sqlite3_errmsg(dbconn));
        return -2;
    }
    if (sqlite3_bind_int64(st, 3, hashes_id) != SQLITE_OK) {
        LOGERR("ERROR: Failed to bind filename_id to prepared statement: %s\n", sqlite3_errmsg(dbconn));
        return -2;
    }
    if (sqlite3_bind_int64(st, 4, fi->statbuf.st_size) != SQLITE_OK) {
        LOGERR("ERROR: Failed to bind size to prepared statement: %s\n", sqlite3_errmsg(dbconn));
        return -2;
    }
    if (sqlite3_bind_int64(st, 5, ts) != SQLITE_OK) {
        LOGERR("ERROR: Failed to bind last_seen timestamp to prepared statement: %s\n", sqlite3_errmsg(dbconn));
        return -2;
    }
    if (sqlite3_bind_blob(st, 6, &(fi->statbuf), sizeof(struct stat), SQLITE_TRANSIENT) != SQLITE_OK) {
        LOGERR("ERROR: Failed to bind last_seen timestamp to prepared statement: %s\n", sqlite3_errmsg(dbconn));
        return -2;
    }


    strc = sqlite3_step(st);
    if (strc != SQLITE_DONE) {
        LOGERR("ERROR: Failed to insert fileinfo for %s/%s into db: %s\n", fi->path, fi->name, sqlite3_errmsg(dbconn));
        rc = -2;
    } else {
        rc = 0;
    }


    sqlite3_clear_bindings(st);
    sqlite3_reset(st);

    return rc;
};

/**
 * Fill the given struct (path and filename required) with database content if available.
 *
 * This can be used to speed up a rescan if the stat matches and last mtime was before last_seen
 */
int dbi_fill_fileinfo(struct df_fileinfo *fi) {
    int rc = 0;
    int64_t fname_id, pname_id, existing_entry = 0;


    DBCONN_CHECK(-1);

    if (fi == NULL) {
        LOGERR("ERROR: No fileinfo given.\n");
        return -1;
    }

    fname_id = dbi_select_filename_by_name(fi->name);
    pname_id = dbi_select_path_by_pathname(fi->path);

    /* Any problems with the selects? */
    if (fname_id <0 || pname_id <0) {
        DBGTRC("ERROR: abort due to previous error.\n");
        return -1;
    }

    /* Abort if no matching entry in the table */
    existing_entry = dbi_select_fileinfo_by_path_filename_ids(pname_id, fname_id);
    if (existing_entry < 0) {
        fi->id = 0;
        return 0;
    }

    /* TODO: The following has be moved to a dbi_select_fileinfo_by_id_resolved */
    rc = dbi_select_fileinfo_by_id_resolved(existing_entry, fi);

    return rc;
}



int dbi_select_fileinfo_by_id_resolved(int64_t id, struct df_fileinfo *fi) {
    int strc = 0;
    size_t len;
    char *tmp = NULL;
    const unsigned char *txt = NULL;
    sqlite3_stmt *st = select_fileinfo_by_id_resolved;


    DBCONN_CHECK(-1);

    if (id <=0) {
        LOGERR("ERROR: No id given.\n");
        return -1;
    }

    if (fi == NULL) {
        LOGERR("ERROR: No fileinfo struct given.\n");
        return -1;
    }


    sqlite3_clear_bindings(st);
    sqlite3_reset(st);

    if (sqlite3_bind_int64(st, 1,id) != SQLITE_OK) {
        LOGERR("ERROR: Failed to bind id to prepared statement: %s\n", sqlite3_errmsg(dbconn));
        return -1;
    }

    strc = sqlite3_step(st);

    if (strc == SQLITE_DONE) {
        return -1;
    }

    if (strc != SQLITE_ROW) {
        LOGERR("ERROR: Failed step: %s\n", sqlite3_errmsg(dbconn));
        return -1;
    }

    if (fi->path == NULL) {
        txt = sqlite3_column_text(st, 0); /* paths.pathname */
        len = (size_t)sqlite3_column_bytes(st, 0);
        if ((tmp = calloc(len+1, sizeof(char))) == NULL) {
            LOGERR("ERROR: Failed to allocate a few bytes memory\n");
            return -1;
        }
        memcpy(tmp, txt, len);
        fi->path = tmp;
    }

    if (fi->name == NULL) {
        txt = sqlite3_column_text(st, 1); /* filenames.name */
        len = (size_t)sqlite3_column_bytes(st, 1);
        if ((tmp = calloc(len+1, sizeof(char))) == NULL) {
            LOGERR("ERROR: Failed to allocate a few bytes memory\n");
            return -1;
        }
        memcpy(tmp, txt, len);
        fi->path = tmp;
    }

    txt = sqlite3_column_text(st, 2); /* hashes.blake2 */
    memcpy(fi->hashes.blake2, txt, DF_STR_SIZE_512);
    fi->hashes.blake2[DF_STR_SIZE_512] = '\0'; /* array has the additional nul byte space */

    txt = sqlite3_column_text(st, 3); /* hashes.sha256 */
    memcpy(fi->hashes.sha256, txt, DF_STR_SIZE_256);
    fi->hashes.sha256[DF_STR_SIZE_256] = '\0'; /* array has the additional nul byte space */

    txt = sqlite3_column_text(st, 4); /* hashes.sha512 */
    memcpy(fi->hashes.sha512, txt, DF_STR_SIZE_512);
    fi->hashes.sha512[DF_STR_SIZE_512] = '\0'; /* array has the additional nul byte space */

    /* Ignore the item 5 from the query, it's the filesize we have in the stat struct */
    
    fi->last_seen = (time_t) sqlite3_column_int64(st, 6); /* last_seen */

    /* struct stat statbuf */
    memcpy(&(fi->statbuf), sqlite3_column_blob(st, 7), (size_t)sqlite3_column_bytes(st, 7));

    fi->id = id;

    sqlite3_clear_bindings(st);
    sqlite3_reset(st);


    return 0;
}

/**
 * Print the database content to the given filedescriptor in a CSV form which can be processed with grep awk etc.
 *
 * @param out output descriptor, if NULL stdout is used.
 * @return 0 on success,
 *        -1 on failure
 */
int dbi_print_fileinfo_resolved(FILE *out) {
    int rc = 0;
    int64_t i;
    int strc = 0;
    FILE *fd = out;
    const unsigned char *txt = NULL;
    sqlite3_stmt *st = select_fileinfo_complete_table_resolved;

    DBCONN_CHECK(-1);

    sqlite3_reset(st);


    if (fd == NULL) { fd = stdout; }

    /* We ignore the struct_stat and last_seen columns */

    do {
        strc = sqlite3_step(st);

        if (strc == SQLITE_DONE) {
            break;
        }

        if (strc != SQLITE_ROW) {
            LOGERR("ERROR: Failed step: %s\n", sqlite3_errmsg(dbconn));
            rc = -1;
            break;
        }

        txt = sqlite3_column_text(st, 0); /* paths.pathname */
        fprintf(fd, "%s/", txt);
        txt = sqlite3_column_text(st, 1); /* filenames.name */
        fprintf(fd, "%s;", txt);
        txt = sqlite3_column_text(st, 2); /* hashes.blake2 */
        fprintf(fd, "%s;", txt);
        txt = sqlite3_column_text(st, 3); /* hashes.sha256 */
        fprintf(fd, "%s;", txt);
        txt = sqlite3_column_text(st, 4); /* hashes.sha512 */
        fprintf(fd, "%s;", txt);
        i = (int64_t) sqlite3_column_int64(st, 5); /* fileinfo.size */
        fprintf(fd, "%ld\n", i);

    } while (strc == SQLITE_ROW);

    sqlite3_reset(st);

    return rc;
}


inline int64_t call_count_query(sqlite3_stmt *st) {
    int64_t count;
    int strc = 0;

    DBCONN_CHECK(-1);

    sqlite3_reset(st);
    strc = sqlite3_step(st);

    if (strc != SQLITE_ROW && strc != SQLITE_DONE) {
        LOGERR("ERROR: Failed step: %s\n", sqlite3_errmsg(dbconn));
        return -1;
    }

    count = (int64_t) sqlite3_column_int64(st, 0);

    sqlite3_reset(st);

    return count;
}

/**
 * Get an array containing all ids from table with the first field
 * containing the complete length of the array including this field.
 *
 * @return NULL on failure
 *         an array on the heap which must be freed by the caller.
 */
inline int64_t *call_select_all_ids(sqlite3_stmt *all_ids, sqlite3_stmt *count_query) {
    int64_t *result = NULL;
    int64_t rows = 0, id = 1, pos = 1;
    int strc = 0;
    sqlite3_stmt *st = all_ids;

    DBCONN_CHECK(NULL);

    rows = call_count_query(count_query);

    if (rows < 0) { return NULL; }

    rows++;
#pragma GCC diagnostic push
#pragma GCC diagnostic ignored "-Wsign-conversion"
    if ((result = calloc(rows, sizeof(int64_t))) == NULL) {
#pragma GCC diagnostic pop
        LOGERR("ERROR: Failed to allocate heap memory\n");
        return NULL;
    }

    result[0] = rows;

    sqlite3_reset(st);

    do {
        strc = sqlite3_step(st);

        if (strc == SQLITE_DONE) {
            break;
        }

        if (strc != SQLITE_ROW) {
            LOGERR("ERROR: Failed step: %s\n", sqlite3_errmsg(dbconn));
            free(result);
            result = NULL;
            break;
        }

        id = (int64_t) sqlite3_column_int64(st, 0); /* hashes.id */
        result[pos++] = id;
        DBGTRC("DEBUG: Found id %ld\n", id);

    } while (strc == SQLITE_ROW);

    sqlite3_reset(st);

    DBGTRC("DEBUG: rows %ld | pos %ld\n", rows, pos);

    return result;
}

int64_t *dbi_select_filenames_all_ids() {
    return call_select_all_ids(select_filename_all_ids, count_filenames);
}


int64_t *dbi_select_hashes_all_ids() {
    return call_select_all_ids(select_hashes_all_ids, count_hashes);
}

/**
 * Iterate over the stored hashes, for those associated with more than
 * one row in fileinfo the information from the latter will be printed.
 *
 * @param out filestream for output, if NULL stdout is used
 *
 * @return 0 on success
 *        <0 on failure
 */
int dbi_print_identical_hashes(FILE *out) {
    int rc = 0;
    int64_t hid, count, id, i;
    int64_t *hid_array;
    int strc_fi = 0, strc_count = 0;
    FILE *fd = out;
    const unsigned char *txt = NULL;
    sqlite3_stmt *stfi = select_fileinfo_by_hash_id_resolved,
                 *stcount = count_fileinfo_by_hash_id;

    DBCONN_CHECK(-1);

    if (fd == NULL) { fd = stdout; }

    /* SQLite only supports one query at a time per connection, therefore the
     * segmented approach. Query for all hashes. Iterating over the array and
     * query to count the usage of each id, query in case there's more than a
     * single association. A second connection is possible, but would require
     * another set of query preparation and other surround stuff.
     */
    hid_array = dbi_select_hashes_all_ids();
    if (hid_array == NULL) {
        return -1;
    }

    for (i=1; i<hid_array[0]; i++) {
        sqlite3_clear_bindings(stcount);
        sqlite3_reset(stcount);

        /* prevent human errors */
        hid = hid_array[i];

        if (sqlite3_bind_int64(stcount, 1, hid) != SQLITE_OK) {
            LOGERR("ERROR: Failed to bind hashes.id to prepared statement for count: %s\n", sqlite3_errmsg(dbconn));
            rc = -1;
            break;
        }

        strc_count = sqlite3_step(stcount);
        if (strc_count == SQLITE_DONE) { /* Not found */
            continue;
        } else if (strc_count != SQLITE_ROW) {
            LOGERR("ERROR: Failed step: %s\n", sqlite3_errmsg(dbconn));
            rc = -1;
            break;
        }

        count = (int64_t) sqlite3_column_int64(stcount, 0);
        DBGTRC("DEBUG: count results for hash id %ld: %ld\n", hid, count);

        if (count>1) {
            sqlite3_reset(stfi);
            sqlite3_clear_bindings(stfi);

            if (sqlite3_bind_int64(stfi, 1, hid) != SQLITE_OK) {
                LOGERR("ERROR: Failed to bind hashes.id to prepared statement for count: %s\n", sqlite3_errmsg(dbconn));
                free(hid_array);
                return -1;
            }

            do {
                strc_fi = sqlite3_step(stfi);

                if (strc_fi == SQLITE_DONE) {
                    DBGTRC("DEBUG: finished for hid %ld\n", hid);
                    break;
                }

                if (strc_fi != SQLITE_ROW) {
                    LOGERR("ERROR: Failed step to get fileinfo content: %s\n", sqlite3_errmsg(dbconn));
                    free(hid_array);
                    return -1; /* drop-it */
                }

                txt = sqlite3_column_text(stfi, 0); /* paths.pathname */
                fprintf(fd, "%s/", txt);
                txt = sqlite3_column_text(stfi, 1); /* filenames.name */
                fprintf(fd, "%s;", txt);
                txt = sqlite3_column_text(stfi, 2); /* hashes.blake2 */
                fprintf(fd, "%s;", txt);
                txt = sqlite3_column_text(stfi, 3); /* hashes.sha256 */
                fprintf(fd, "%s;", txt);
                txt = sqlite3_column_text(stfi, 4); /* hashes.sha512 */
                fprintf(fd, "%s;", txt);
                id = (int64_t) sqlite3_column_int64(stfi, 5); /* fileinfo.size */
                fprintf(fd, "%ld\n", id);
            } while (strc_fi == SQLITE_ROW);
        }
        sqlite3_reset(stfi);
        sqlite3_clear_bindings(stfi);
    }

    free(hid_array);

    sqlite3_clear_bindings(stfi);
    sqlite3_clear_bindings(stcount);
    sqlite3_reset(stfi);
    sqlite3_reset(stcount);

    return rc;
}


int dbi_print_identical_filenames(FILE *out) {
    int rc = 0;
    int64_t fnid, count, id, i;
    int64_t *fnid_array;
    int strc_fi = 0, strc_count = 0;
    FILE *fd = out;
    const unsigned char *txt = NULL;
    sqlite3_stmt *stfi = select_fileinfo_by_filename_id_resolved,
                 *stcount = count_fileinfo_by_filename;

    DBCONN_CHECK(-1);

    if (fd == NULL) { fd = stdout; }

    /* SQLite only supports one query at a time per connection, therefore the
     * segmented approach. Query for all hashes. Iterating over the array and
     * query to count the usage of each id, query in case there's more than a
     * single association. A second connection is possible, but would require
     * another set of query preparation and other surround stuff.
     */
    fnid_array = dbi_select_filenames_all_ids();
    if (fnid_array == NULL) {
        return -1;
    }

    for (i=1; i<fnid_array[0]; i++) {
        sqlite3_clear_bindings(stcount);
        sqlite3_reset(stcount);

        /* prevent human errors */
        fnid = fnid_array[i];

        if (sqlite3_bind_int64(stcount, 1, fnid) != SQLITE_OK) {
            LOGERR("ERROR: Failed to bind hashes.id to prepared statement for count: %s\n", sqlite3_errmsg(dbconn));
            rc = -1;
            break;
        }

        strc_count = sqlite3_step(stcount);
        if (strc_count == SQLITE_DONE) { /* Not found */
            continue;
        } else if (strc_count != SQLITE_ROW) {
            LOGERR("ERROR: Failed step: %s\n", sqlite3_errmsg(dbconn));
            rc = -1;
            break;
        }

        count = (int64_t) sqlite3_column_int64(stcount, 0);
        DBGTRC("DEBUG: count results for hash id %ld: %ld\n", fnid, count);

        if (count>1) {
            sqlite3_reset(stfi);
            sqlite3_clear_bindings(stfi);

            if (sqlite3_bind_int64(stfi, 1, fnid) != SQLITE_OK) {
                LOGERR("ERROR: Failed to bind hashes.id to prepared statement for count: %s\n", sqlite3_errmsg(dbconn));
                free(fnid_array);
                return -1;
            }

            do {
                strc_fi = sqlite3_step(stfi);

                if (strc_fi == SQLITE_DONE) {
                    DBGTRC("DEBUG: finished for hid %ld\n", fnid);
                    break;
                }

                if (strc_fi != SQLITE_ROW) {
                    LOGERR("ERROR: Failed step to get fileinfo content: %s\n", sqlite3_errmsg(dbconn));
                    free(fnid_array);
                    return -1; /* drop-it */
                }

                txt = sqlite3_column_text(stfi, 0); /* paths.pathname */
                fprintf(fd, "%s/", txt);
                txt = sqlite3_column_text(stfi, 1); /* filenames.name */
                fprintf(fd, "%s;", txt);
                txt = sqlite3_column_text(stfi, 2); /* hashes.blake2 */
                fprintf(fd, "%s;", txt);
                txt = sqlite3_column_text(stfi, 3); /* hashes.sha256 */
                fprintf(fd, "%s;", txt);
                txt = sqlite3_column_text(stfi, 4); /* hashes.sha512 */
                fprintf(fd, "%s;", txt);
                id = (int64_t) sqlite3_column_int64(stfi, 5); /* fileinfo.size */
                fprintf(fd, "%ld\n", id);
            } while (strc_fi == SQLITE_ROW);
        }
        sqlite3_reset(stfi);
        sqlite3_clear_bindings(stfi);
    }

    free(fnid_array);

    sqlite3_clear_bindings(stfi);
    sqlite3_clear_bindings(stcount);
    sqlite3_reset(stfi);
    sqlite3_reset(stcount);

    return rc;
}

int dbi_print_fullpaths(FILE *out) {
    int rc = 0;
    int strc = 0;
    FILE *fd = out;
    const unsigned char *txt = NULL;
    sqlite3_stmt *st = select_full_path;

    DBCONN_CHECK(-1);

    if (fd == NULL) { fd = stdout; }

    sqlite3_clear_bindings(st);
    sqlite3_reset(st);
    do {
        strc = sqlite3_step(st);

        if (strc == SQLITE_DONE) {
            break;
        }

        if (strc != SQLITE_ROW) {
            LOGERR("ERROR: Failed step to get fileinfo content: %s\n", sqlite3_errmsg(dbconn));
            return -1; /* drop-it */
        }

        txt = sqlite3_column_text(st, 0); /* paths.pathname */
        fprintf(fd, "%s/", txt);
        txt = sqlite3_column_text(st, 1); /* filenames.name */
        fprintf(fd, "%s\n", txt);
    } while (strc == SQLITE_ROW);


    sqlite3_clear_bindings(st);
    sqlite3_reset(st);

    return rc;
}


int dbi_print_filenames(FILE *out) {
    int rc = 0;
    int strc = 0;
    FILE *fd = out;
    const unsigned char *txt = NULL;
    sqlite3_stmt *st = select_filename_complete_table;

    DBCONN_CHECK(-1);

    if (fd == NULL) { fd = stdout; }

    sqlite3_clear_bindings(st);
    sqlite3_reset(st);
    do {
        strc = sqlite3_step(st);

        if (strc == SQLITE_DONE) {
            break;
        }

        if (strc != SQLITE_ROW) {
            LOGERR("ERROR: Failed step to get filenames.name: %s\n", sqlite3_errmsg(dbconn));
            return -1; /* drop-it */
        }

        txt = sqlite3_column_text(st, 1); /* filenames.name */
        fprintf(fd, "%s\n", txt);
    } while (strc == SQLITE_ROW);


    sqlite3_clear_bindings(st);
    sqlite3_reset(st);

    return rc;
}


#if 0
             *select_fileinfo_by_id,
             *select_fileinfo_by_path_id,
             *select_fileinfo_by_filename_id,
             *select_fileinfo_by_path_filename_ids,
             *select_fileinfo_by_hash_id;

             *select_fileinfo_complete_table,
             *select_fileinfo_complete_table_resolved;

sqlite3_stmt *delete_fileinfo_by_id;
#endif