From 390cbf31635951e46a49ed8657cd2a1635757f19 Mon Sep 17 00:00:00 2001 From: yuzu-eva Date: Sun, 8 Jun 2025 14:33:02 +0200 Subject: changed project structure, adjusted Makefile --- src/dbhandling.c | 247 +++++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 247 insertions(+) create mode 100644 src/dbhandling.c (limited to 'src/dbhandling.c') diff --git a/src/dbhandling.c b/src/dbhandling.c new file mode 100644 index 0000000..0a588ca --- /dev/null +++ b/src/dbhandling.c @@ -0,0 +1,247 @@ +#include "dbhandling.h" +#include "enum.h" + +void exit_with_error(sqlite3 *db, const char *msg) +{ + fprintf(stderr, "%s: %s\n", msg, sqlite3_errmsg(db)); + sqlite3_close(db); + exit(69); +} + +/* + TODO: write function for preparing the statement + Should take entry_t *entry, args_e target (,args_e mode?) + I already differentiate the mode in main, so passing it here + would be redundant. Find another solution. +*/ + +/*POSSIBLE OTHER SOLUTION: + TODO: try to get rid of args_e target and args_e mode and instead + use a flag. 3 possible tables with 3 different modes = 9 bits. Maybe + do 16bits in preparation for additional modes and tables. Not sure how + to go about setting them, though... that will be one hell of an + if-else block, lmao. +*/ + +void select_from_table(sqlite3 *db, args_e target, entry_t *entry) +{ + char *sql; + int rc; + int found = 0; + + const unsigned char *name, *author, *value, *status; + const char *type; + + printf("Name: "); + fgets(entry->name, MAX_NAME_LEN, stdin); + + // remove newline char, otherwise it messes with the query + entry->name[strcspn(entry->name, "\n")] = 0; + + char *query_param = malloc(strlen(entry->name)); + switch (target) { + case ANIME: + type = "Episode"; + sql = "SELECT * FROM anime WHERE name LIKE ?1 ORDER BY id;"; + break; + case MANGA: + type = "Chapter"; + sql = "SELECT * FROM manga WHERE name LIKE ?1 ORDER BY id;"; + break; + case BOOK: + type = "Chapter"; + sql = "SELECT * FROM book WHERE name LIKE ?1 OR author LIKE ?1 ORDER BY id;"; + break; + default: + fprintf(stderr, "table does not exist...\n"); + sqlite3_close(db); + exit(69); + } + + sqlite3_stmt *stmt; + snprintf(query_param, strlen(entry->name) + 2, "%s%%", entry->name); + + rc = sqlite3_prepare_v2(db, sql, -1, &stmt, 0); + if (rc != SQLITE_OK) { + exit_with_error(db, "failure fetching data: "); + } + + sqlite3_bind_text(stmt, 1, query_param, -1, SQLITE_TRANSIENT); + + if (target == BOOK) { + while(sqlite3_step(stmt) == SQLITE_ROW) { + name = sqlite3_column_text(stmt, 1); + author = sqlite3_column_text(stmt, 2); + value = sqlite3_column_text(stmt, 3); + status = sqlite3_column_text(stmt, 4); + printf("%s - %s, %s %s, %s\n", author, name, type, value, status); + found = 1; + } + } else { + while(sqlite3_step(stmt) == SQLITE_ROW) { + name = sqlite3_column_text(stmt, 1); + value = sqlite3_column_text(stmt, 2); + status = sqlite3_column_text(stmt, 3); + printf("%s, %s %s, %s\n", name, type, value, status); + found = 1; + } + } + + if (!found) { + printf("no entry found...\n"); + } + + sqlite3_finalize(stmt); + free(query_param); +} + +void update_entry(sqlite3 *db, args_e target, entry_t *entry) +{ + char *sql; + int rc; + int res; + + sqlite3_stmt *stmt; + + printf("Name: "); + fgets(entry->name, MAX_NAME_LEN, stdin); + entry->name[strcspn(entry->name, "\n")] = 0; + + // status is currently never NULL, so the if-else makes no sense + // would be addressed by the TODO: switching to flags + // i'll just keep it as is for now... + switch (target) { + case ANIME: + printf("Episode: "); + fgets(entry->value, MAX_VALUE_LEN, stdin); + entry->value[strcspn(entry->value, "\n")] = 0; + if (entry->status == NULL) { + sql = "UPDATE anime SET episode=?1 WHERE name=?2;"; + } else { + printf("Status: "); + fgets(entry->status, MAX_STATUS_LEN, stdin); + entry->status[strcspn(entry->status, "\n")] = 0; + sql = "UPDATE anime SET episode=?1, status=?2 WHERE name=?3;"; + } + break; + case MANGA: + printf("Chapter: "); + fgets(entry->value, MAX_VALUE_LEN, stdin); + entry->value[strcspn(entry->value, "\n")] = 0; + if (entry->status == NULL) { + sql = "UPDATE manga SET chapter=?1 WHERE name=?2;"; + } else { + printf("Status: "); + fgets(entry->status, MAX_STATUS_LEN, stdin); + entry->status[strcspn(entry->status, "\n")] = 0; + sql = "UPDATE manga SET chapter=?1, status=?2 WHERE name=?3;"; + } + break; + case BOOK: + printf("Chapter: "); + fgets(entry->value, MAX_VALUE_LEN, stdin); + entry->value[strcspn(entry->value, "\n")] = 0; + if (entry->status == NULL) { + sql = "UPDATE book SET chapter=?1 WHERE name=?2;"; + } else { + printf("Status: "); + fgets(entry->status, MAX_STATUS_LEN, stdin); + entry->status[strcspn(entry->status, "\n")] = 0; + sql = "UPDATE book SET chapter=?1, status=?2 WHERE name=?3;"; + } + break; + default: + fprintf(stderr, "table does not exist...\n"); + sqlite3_close(db); + exit(69); + } + + rc = sqlite3_prepare_v2(db, sql, -1, &stmt, 0); + if (rc != SQLITE_OK) { + exit_with_error(db, "failure fetching data: "); + } + + if (entry->status == NULL) { + sqlite3_bind_text(stmt, 1, entry->value, -1, SQLITE_STATIC); + sqlite3_bind_text(stmt, 2, entry->name, -1, SQLITE_STATIC); + } else { + sqlite3_bind_text(stmt, 1, entry->value, -1, SQLITE_STATIC); + sqlite3_bind_text(stmt, 2, entry->status, -1, SQLITE_STATIC); + sqlite3_bind_text(stmt, 3, entry->name, -1, SQLITE_STATIC); + } + + rc = sqlite3_step(stmt); + res = sqlite3_changes(db); + if (!res) { + printf("no entry found...\n"); + } + sqlite3_finalize(stmt); +} + +void add_entry(sqlite3 *db, args_e target, entry_t *entry) +{ + char *sql; + int rc; + + sqlite3_stmt *stmt; + + printf("Name: "); + fgets(entry->name, MAX_NAME_LEN, stdin); + entry->name[strcspn(entry->name, "\n")] = 0; + switch (target) { + case ANIME: + printf("Episode: "); + fgets(entry->value, MAX_VALUE_LEN, stdin); + entry->value[strcspn(entry->value, "\n")] = 0; + printf("Status: "); + fgets(entry->status, MAX_STATUS_LEN, stdin); + entry->status[strcspn(entry->status, "\n")] = 0; + sql = "INSERT INTO anime (name, episode, status) VALUES (?1, ?2, ?3);"; + break; + case MANGA: + printf("Chapter: "); + fgets(entry->value, MAX_VALUE_LEN, stdin); + entry->value[strcspn(entry->value, "\n")] = 0; + printf("Status: "); + fgets(entry->status, MAX_STATUS_LEN, stdin); + entry->status[strcspn(entry->status, "\n")] = 0; + sql = "INSERT INTO manga (name, chapter, status) VALUES (?1, ?2, ?3);"; + break; + case BOOK: + printf("Author: "); + fgets(entry->author, MAX_AUTHOR_LEN, stdin); + entry->author[strcspn(entry->author, "\n")] = 0; + printf("Chapter: "); + fgets(entry->value, MAX_VALUE_LEN, stdin); + entry->value[strcspn(entry->value, "\n")] = 0; + printf("Status: "); + fgets(entry->status, MAX_STATUS_LEN, stdin); + entry->status[strcspn(entry->status, "\n")] = 0; + sql = "INSERT INTO book (name, author, chapter, status) VALUES (?1, ?2, ?3, ?4);"; + break; + default: + fprintf(stderr, "table does not exist...\n"); + sqlite3_close(db); + exit(69); + } + + rc = sqlite3_prepare_v2(db, sql, -1, &stmt, 0); + if (rc != SQLITE_OK) { + exit_with_error(db, "failure fetching data: "); + } + + if (target == BOOK) { + sqlite3_bind_text(stmt, 1, entry->name, -1, SQLITE_STATIC); + sqlite3_bind_text(stmt, 2, entry->author, -1, SQLITE_STATIC); + sqlite3_bind_text(stmt, 3, entry->value, -1, SQLITE_STATIC); + sqlite3_bind_text(stmt, 4, entry->status, -1, SQLITE_STATIC); + } else { + sqlite3_bind_text(stmt, 1, entry->name, -1, SQLITE_STATIC); + sqlite3_bind_text(stmt, 2, entry->value, -1, SQLITE_STATIC); + sqlite3_bind_text(stmt, 3, entry->status, -1, SQLITE_STATIC); + } + + rc = sqlite3_step(stmt); + printf("Entry %s added!\n", entry->name); + sqlite3_finalize(stmt); +} -- cgit v1.2.3