diff options
Diffstat (limited to 'src/database_interaction.c')
| -rw-r--r-- | src/database_interaction.c | 885 |
1 files changed, 885 insertions, 0 deletions
diff --git a/src/database_interaction.c b/src/database_interaction.c new file mode 100644 index 0000000..e31d197 --- /dev/null +++ b/src/database_interaction.c @@ -0,0 +1,885 @@ +/* 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_path_by_id, + *select_path_by_pathname, + *select_hashes_by_id, + *select_hashes_by_strings, + *select_fileinfo_by_id, + *select_fileinfo_by_id_resolved, + *select_fileinfo_by_path_id, + *select_fileinfo_by_filename_id, + *select_fileinfo_by_path_filename_ids, + *select_fileinfo_by_hash_path_filename_ids, + *select_fileinfo_by_hash_id; + +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; + +/*=========== FUNCTIONS ===========*/ +void create_tables(); +int prepare_statements(); +char *select_string_by_int(sqlite3_stmt *st, int64_t id); + +/* 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_by_id); + LOCAL_FINALIZE(select_filename_by_name); + LOCAL_FINALIZE(select_path_by_id); + LOCAL_FINALIZE(select_path_by_pathname); + LOCAL_FINALIZE(select_hashes_by_id); + LOCAL_FINALIZE(select_hashes_by_strings); + 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_path_filename_ids); + LOCAL_FINALIZE(select_fileinfo_by_hash_id); + + 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); + +#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 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 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 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); + + /* TODO: so far the only query with JOINs or masking it in another way? Ignore this frontend query for now.*/ + select_fileinfo_by_id_resolved = NULL; + + /* 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 (@blake2, @sha256, @sha512);", &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); + +#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, sqlite3_bind_parameter_index(st, "blake2"), 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, sqlite3_bind_parameter_index(st, "sha256"), 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, sqlite3_bind_parameter_index(st, "sha512"), 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, sqlite3_bind_parameter_index(st, "blake2"), 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, sqlite3_bind_parameter_index(st, "sha256"), 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, sqlite3_bind_parameter_index(st, "sha512"), 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; +}; + +#if 0 + *select_fileinfo_by_id, + *select_fileinfo_by_id_resolved, + *select_fileinfo_by_path_id, + *select_fileinfo_by_filename_id, + *select_fileinfo_by_path_filename_ids, + *select_fileinfo_by_hash_id; + + +sqlite3_stmt *, + +sqlite3_stmt *delete_fileinfo_by_id; +#endif + |