/* * sqlite-storage.c * * Copyright 2024 Michael Rasmussen * * This program is free software; you can redistribute it and/or modify * it under the terms of the GNU General Public License as published by * the Free Software Foundation; either version 3 of the License, or * (at your option) any later version. * * This program is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU General Public License for more details. * * You should have received a copy of the GNU General Public License * along with this program; if not, write to the Free Software * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, * MA 02110-1301, USA. * * */ #include #include "sqlite-storage.h" #include "gui.h" #define DBNAME "lensdb.db" static sqlite3* db = NULL; static gchar* lensmaker = "create table lensmaker(" "id integer not null," "maker text not null UNIQUE," "page int DEFAULT -1," "PRIMARY KEY(id AUTOINCREMENT));"; static gchar* lens = "create table lens(" "id integer not null," "sn text not null," "maker integer not null," "description text not null," "notes text," "date_added datetime DEFAULT current_timestamp," "PRIMARY KEY(id AUTOINCREMENT)," "constraint fk_maker foreign key (maker) references lensmaker (id)" " on delete cascade);"; static gchar* search = "SELECT l.id, l.sn, l.description, l.notes, l.maker" " FROM lens l JOIN lensmaker m on l.maker = m.id"; static gchar* all = "SELECT m.maker,l.sn,l.description,l.notes,l.date_added" " FROM lensmaker m JOIN lens l on m.id = l.maker"; void close_storage() { if (db != NULL) sqlite3_close(db); } gint init_storage(const gchar* path) { gboolean init_db = FALSE; gint rc; gchar* ErrMsg = NULL; gchar* file = g_strconcat(path, "/", DBNAME, NULL); if (!g_file_test(file, G_FILE_TEST_EXISTS)) { init_db = TRUE; } rc = sqlite3_open(file, &db); g_free(file); if (rc) { gchar* err = g_strdup_printf("Failed to open DB: %s\n", sqlite3_errmsg(db)); fprintf(stderr, err); show_message(GTK_MESSAGE_ERROR, err); g_free(err); return 1; } if (init_db) { rc = sqlite3_exec(db, "PRAGMA foreign_keys = ON", 0, 0, &ErrMsg); if (rc != SQLITE_OK) { gchar* err = g_strdup_printf("SQL error: %s\n", ErrMsg); fprintf(stderr, err); show_message(GTK_MESSAGE_ERROR, err); g_free(err); sqlite3_free(ErrMsg); ErrMsg = NULL; } rc = sqlite3_exec(db, lensmaker, 0, 0, &ErrMsg); if (rc != SQLITE_OK) { gchar* err = g_strdup_printf("SQL error: %s\n", ErrMsg); fprintf(stderr, err); show_message(GTK_MESSAGE_ERROR, err); g_free(err); sqlite3_free(ErrMsg); ErrMsg = NULL; } rc = sqlite3_exec(db, lens, 0, 0, &ErrMsg); if (rc != SQLITE_OK) { gchar* err = g_strdup_printf("SQL error: %s\n", ErrMsg); fprintf(stderr, err); show_message(GTK_MESSAGE_ERROR, err); g_free(err); sqlite3_free(ErrMsg); } } return 0; } gint add_lens(Row* row) { gint rc; sqlite3_stmt* stmt; gint status = 1; rc = sqlite3_prepare_v2(db, "insert into lens (sn, maker, description, notes) values (?1, ?2, ?3, ?4) returning id", -1, &stmt, 0); if (rc != SQLITE_OK) { gchar* err = g_strdup_printf("Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db)); fprintf(stderr, err); show_message(GTK_MESSAGE_ERROR, err); g_free(err); sqlite3_finalize(stmt); return status; } rc = sqlite3_bind_text(stmt, 1, row->sn, -1, NULL); if (rc) { gchar* err = g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db)); fprintf(stderr, err); show_message(GTK_MESSAGE_ERROR, err); g_free(err); sqlite3_finalize(stmt); return status; } rc = sqlite3_bind_int(stmt, 2, row->maker); if (rc) { gchar* err = g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db)); fprintf(stderr, err); show_message(GTK_MESSAGE_ERROR, err); g_free(err); sqlite3_finalize(stmt); return status; } rc = sqlite3_bind_text(stmt, 3, row->data, -1, NULL); if (rc) { gchar* err = g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db)); fprintf(stderr, err); show_message(GTK_MESSAGE_ERROR, err); g_free(err); sqlite3_finalize(stmt); return status; } rc = sqlite3_bind_text(stmt, 4, row->notes, -1, NULL); if (rc) { gchar* err = g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db)); fprintf(stderr, err); show_message(GTK_MESSAGE_ERROR, err); g_free(err); sqlite3_finalize(stmt); return status; } rc = sqlite3_step(stmt); if (rc != SQLITE_ROW) { gchar* err = g_strdup_printf("Step failure: %s\n", sqlite3_errmsg(db)); fprintf(stderr, err); show_message(GTK_MESSAGE_ERROR, err); g_free(err); } else { row->id = sqlite3_column_int64(stmt, 0); status = 0; } sqlite3_finalize(stmt); return status; } gint update_lens(const Row* row) { gint rc; sqlite3_stmt* stmt; gint status = 1; rc = sqlite3_prepare_v2(db, "update lens set sn = ?1, description = ?2, notes = ?3 where id = ?4", -1, &stmt, 0); if (rc != SQLITE_OK) { gchar* err = g_strdup_printf("Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db)); fprintf(stderr, err); show_message(GTK_MESSAGE_ERROR, err); g_free(err); sqlite3_finalize(stmt); return status; } rc = sqlite3_bind_text(stmt, 1, row->sn, -1, NULL); if (rc) { gchar* err = g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db)); fprintf(stderr, err); show_message(GTK_MESSAGE_ERROR, err); g_free(err); sqlite3_finalize(stmt); return status; } rc = sqlite3_bind_text(stmt, 2, row->data, -1, NULL); if (rc) { gchar* err = g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db)); fprintf(stderr, err); show_message(GTK_MESSAGE_ERROR, err); g_free(err); sqlite3_finalize(stmt); return status; } rc = sqlite3_bind_text(stmt, 3, row->notes, -1, NULL); if (rc) { gchar* err = g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db)); fprintf(stderr, err); show_message(GTK_MESSAGE_ERROR, err); g_free(err); sqlite3_finalize(stmt); return status; } rc = sqlite3_bind_int64(stmt, 4, row->id); if (rc) { gchar* err = g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db)); fprintf(stderr, err); show_message(GTK_MESSAGE_ERROR, err); g_free(err); sqlite3_finalize(stmt); return status; } rc = sqlite3_step(stmt); if (rc != SQLITE_DONE) { gchar* err = g_strdup_printf("Step failure: %s\n", sqlite3_errmsg(db)); fprintf(stderr, err); show_message(GTK_MESSAGE_ERROR, err); g_free(err); } else { status = 0; } sqlite3_finalize(stmt); return status; } gint update_lens_maker(const Row* row) { gint rc; sqlite3_stmt* stmt; gint status = 1; rc = sqlite3_prepare_v2(db, "update lens set maker = ?1 where id = ?2", -1, &stmt, 0); if (rc != SQLITE_OK) { gchar* err = g_strdup_printf("Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db)); fprintf(stderr, err); show_message(GTK_MESSAGE_ERROR, err); g_free(err); sqlite3_finalize(stmt); return status; } rc = sqlite3_bind_int64(stmt, 1, row->maker); if (rc) { gchar* err = g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db)); fprintf(stderr, err); show_message(GTK_MESSAGE_ERROR, err); g_free(err); sqlite3_finalize(stmt); return status; } rc = sqlite3_bind_int64(stmt, 2, row->id); if (rc) { gchar* err = g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db)); fprintf(stderr, err); show_message(GTK_MESSAGE_ERROR, err); g_free(err); sqlite3_finalize(stmt); return status; } rc = sqlite3_step(stmt); if (rc != SQLITE_DONE) { gchar* err = g_strdup_printf("Step failure: %s\n", sqlite3_errmsg(db)); fprintf(stderr, err); show_message(GTK_MESSAGE_ERROR, err); g_free(err); } else { status = 0; } sqlite3_finalize(stmt); return status; } gint delete_lens(const Row* row) { gint rc; sqlite3_stmt* stmt; gint status = 1; rc = sqlite3_prepare_v2(db, "delete from lens where id = ?1", -1, &stmt, 0); if (rc != SQLITE_OK) { gchar* err = g_strdup_printf("Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db)); fprintf(stderr, err); show_message(GTK_MESSAGE_ERROR, err); g_free(err); sqlite3_finalize(stmt); return status; } rc = sqlite3_bind_int64(stmt, 1, row->id); if (rc) { gchar* err = g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db)); fprintf(stderr, err); show_message(GTK_MESSAGE_ERROR, err); g_free(err); sqlite3_finalize(stmt); return status; } rc = sqlite3_step(stmt); if (rc != SQLITE_DONE) { gchar* err = g_strdup_printf("Step failure: %s\n", sqlite3_errmsg(db)); fprintf(stderr, err); show_message(GTK_MESSAGE_ERROR, err); g_free(err); } else { status = 0; } sqlite3_finalize(stmt); return status; } gint add_maker(const gchar* maker, gchar** error) { gint rc; sqlite3_stmt* stmt; gint id = -1; rc = sqlite3_prepare_v2(db, "insert into lensmaker (maker) values (?1) returning id", -1, &stmt, 0); if (rc != SQLITE_OK) { gchar* err = g_strdup_printf("Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db)); fprintf(stderr, err); show_message(GTK_MESSAGE_ERROR, err); g_free(err); if (error) *error = g_strdup(sqlite3_errmsg(db)); sqlite3_finalize(stmt); return id; } rc = sqlite3_bind_text(stmt, 1, maker, -1, NULL); if (rc) { gchar* err = g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db)); fprintf(stderr, err); show_message(GTK_MESSAGE_ERROR, err); g_free(err); if (error) *error = g_strdup(sqlite3_errmsg(db)); sqlite3_finalize(stmt); return id; } rc = sqlite3_step(stmt); if (rc != SQLITE_ROW) { gchar* err = g_strdup_printf("Couldn't insert maker: %s\n", sqlite3_errmsg(db)); fprintf(stderr, err); show_message(GTK_MESSAGE_ERROR, err); g_free(err); if (error) *error = g_strdup(sqlite3_errmsg(db)); } else { id = sqlite3_column_int64(stmt, 0); } sqlite3_finalize(stmt); return id; } Maker* get_maker(gint id) { gint rc; sqlite3_stmt* stmt; Maker* maker = NULL; rc = sqlite3_prepare_v2(db, "select maker, page from lensmaker where id = ?1", -1, &stmt, 0); if (rc != SQLITE_OK) { gchar* err = g_strdup_printf("Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db)); fprintf(stderr, err); show_message(GTK_MESSAGE_ERROR, err); g_free(err); sqlite3_finalize(stmt); return NULL; } rc = sqlite3_bind_int64(stmt, 1, id); if (rc) { gchar* err = g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db)); fprintf(stderr, err); show_message(GTK_MESSAGE_ERROR, err); g_free(err); sqlite3_finalize(stmt); return NULL; } if (sqlite3_step(stmt) == SQLITE_ROW) { maker = g_new0(Maker, 1); maker->id = id; maker->maker_name = g_strdup((const gchar*)sqlite3_column_text(stmt, 0)); maker->page = sqlite3_column_int(stmt, 1); } sqlite3_finalize(stmt); return maker; } void update_maker(const Maker* maker) { gint rc; sqlite3_stmt* stmt; rc = sqlite3_prepare_v2(db, "update lensmaker set page = ?1 where id = ?2", -1, &stmt, 0); if (rc != SQLITE_OK) { gchar* err = g_strdup_printf("Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db)); fprintf(stderr, err); show_message(GTK_MESSAGE_ERROR, err); g_free(err); sqlite3_finalize(stmt); return; } rc = sqlite3_bind_int(stmt, 1, maker->page); if (rc) { gchar* err = g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db)); fprintf(stderr, err); show_message(GTK_MESSAGE_ERROR, err); g_free(err); sqlite3_finalize(stmt); return; } rc = sqlite3_bind_int64(stmt, 2, maker->id); if (rc) { gchar* err = g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db)); fprintf(stderr, err); show_message(GTK_MESSAGE_ERROR, err); g_free(err); sqlite3_finalize(stmt); return; } //fprintf(stderr, "%s\n", sqlite3_expanded_sql(stmt)); rc = sqlite3_step(stmt); if (rc != SQLITE_DONE) { gchar* err = g_strdup_printf("Step failure: %s\n", sqlite3_errmsg(db)); fprintf(stderr, err); show_message(GTK_MESSAGE_ERROR, err); g_free(err); sqlite3_finalize(stmt); } sqlite3_finalize(stmt); } void maker_free(Maker* maker) { if (maker) { g_free(maker->maker_name); g_free(maker); } } gint delete_maker(const gchar* maker, gchar** error) { gint rc; sqlite3_stmt* stmt; gint status = 0; gint id = get_maker_id(maker); rc = sqlite3_prepare_v2(db, "delete from lensmaker where id = ?1", -1, &stmt, 0); if (rc != SQLITE_OK) { gchar* err = g_strdup_printf("Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db)); fprintf(stderr, err); show_message(GTK_MESSAGE_ERROR, err); g_free(err); if (error) *error = g_strdup(sqlite3_errmsg(db)); sqlite3_finalize(stmt); return 1; } rc = sqlite3_bind_int(stmt, 1, id); if (rc) { gchar* err = g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db)); fprintf(stderr, err); show_message(GTK_MESSAGE_ERROR, err); g_free(err); if (error) *error = g_strdup(sqlite3_errmsg(db)); sqlite3_finalize(stmt); return 1; } //fprintf(stderr, "%s\n", sqlite3_expanded_sql(stmt)); rc = sqlite3_step(stmt); if (rc != SQLITE_DONE) { gchar* err = g_strdup_printf("Step failure: %s\n", sqlite3_errmsg(db)); fprintf(stderr, err); show_message(GTK_MESSAGE_ERROR, err); g_free(err); if (error) *error = g_strdup(sqlite3_errmsg(db)); sqlite3_finalize(stmt); status = 1; } sqlite3_finalize(stmt); return status; } GSList* get_makers() { gint rc; sqlite3_stmt* stmt; GSList* makers = NULL; rc = sqlite3_prepare_v2(db, "select maker from lensmaker order by id", -1, &stmt, 0); if (rc != SQLITE_OK) { gchar* err = g_strdup_printf("Failed to fetch data: %s\n", sqlite3_errmsg(db)); fprintf(stderr, err); show_message(GTK_MESSAGE_ERROR, err); g_free(err); sqlite3_finalize(stmt); return NULL; } while (sqlite3_step(stmt) == SQLITE_ROW) { gchar* maker = g_strdup((const gchar*)sqlite3_column_text(stmt, 0)); makers = g_slist_append(makers, maker); } sqlite3_finalize(stmt); return makers; } gint get_maker_id(const gchar* maker) { gint rc; sqlite3_stmt* stmt; gint id = -1; rc = sqlite3_prepare_v2(db, "select id from lensmaker where maker = ?1", -1, &stmt, 0); if (rc != SQLITE_OK) { gchar* err = g_strdup_printf("Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db)); fprintf(stderr, err); show_message(GTK_MESSAGE_ERROR, err); g_free(err); sqlite3_finalize(stmt); return -1; } rc = sqlite3_bind_text(stmt, 1, maker, -1, NULL); if (rc) { gchar* err = g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db)); fprintf(stderr, err); show_message(GTK_MESSAGE_ERROR, err); g_free(err); sqlite3_finalize(stmt); return -1; } if (sqlite3_step(stmt) == SQLITE_ROW) { id = sqlite3_column_int64(stmt, 0); } sqlite3_finalize(stmt); return id; } GSList* get_lens_by_maker(gint id) { gint rc; sqlite3_stmt* stmt; GSList* lenses = NULL; rc = sqlite3_prepare_v2(db, "select sn, description, notes, id from lens where maker = ?1", -1, &stmt, 0); if (rc != SQLITE_OK) { gchar* err = g_strdup_printf("Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db)); fprintf(stderr, err); show_message(GTK_MESSAGE_ERROR, err); g_free(err); sqlite3_finalize(stmt); return NULL; } rc = sqlite3_bind_int64(stmt, 1, id); if (rc) { gchar* err = g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db)); fprintf(stderr, err); show_message(GTK_MESSAGE_ERROR, err); g_free(err); sqlite3_finalize(stmt); return NULL; } while (sqlite3_step(stmt) == SQLITE_ROW) { Row* row = row_new(); row->sn = g_strdup((const gchar*)sqlite3_column_text(stmt, 0)); row->data = g_strdup((const gchar*)sqlite3_column_text(stmt, 1)); row->notes = g_strdup((const gchar*)sqlite3_column_text(stmt, 2)); row->id = sqlite3_column_int64(stmt, 3); lenses = g_slist_append(lenses, row); } sqlite3_finalize(stmt); return lenses; } GSList* get_lens_by_search(const Row* row) { gint rc; sqlite3_stmt* stmt; GSList* lenses = NULL; gchar* bind1 = NULL; gchar* bind2 = NULL; gchar* sql = NULL; if (row->notes && row->data) { sql = g_strconcat(search, " where ", "upper(m.maker) like '%' || upper(?1) || '%' and upper(l.description) like '%' || upper(?2) || '%'", NULL); bind1 = g_strdup(row->notes); bind2 = g_strdup(row->data); } else if (!row->notes && !row->data) { sql = g_strconcat(search, NULL); } else { if (row->notes) { sql = g_strconcat(search, " where upper(m.maker) like '%' || upper(?1) || '%'", NULL); bind1 = g_strdup(row->notes); } else { sql = g_strconcat(search, " where upper(l.description) like '%' || upper(?1) || '%'", NULL); bind1 = g_strdup(row->data); } } rc = sqlite3_prepare_v2(db, sql, -1, &stmt, 0); if (rc != SQLITE_OK) { gchar* err = g_strdup_printf("Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db)); fprintf(stderr, err); show_message(GTK_MESSAGE_ERROR, err); g_free(err); sqlite3_finalize(stmt); return NULL; } if (bind1) { rc = sqlite3_bind_text(stmt, 1, bind1, -1, NULL); if (rc) { gchar* err = g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db)); fprintf(stderr, err); show_message(GTK_MESSAGE_ERROR, err); g_free(err); sqlite3_finalize(stmt); return NULL; } } if (bind2) { rc = sqlite3_bind_text(stmt, 2, bind2, -1, NULL); if (rc) { gchar* err = g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db)); fprintf(stderr, err); show_message(GTK_MESSAGE_ERROR, err); g_free(err); sqlite3_finalize(stmt); return NULL; } } while (sqlite3_step(stmt) == SQLITE_ROW) { Row* row = row_new(); row->id = sqlite3_column_int64(stmt, 0); row->sn = g_strdup((const gchar*)sqlite3_column_text(stmt, 1)); row->data = g_strdup((const gchar*)sqlite3_column_text(stmt, 2)); row->notes = g_strdup((const gchar*)sqlite3_column_text(stmt, 3)); row->maker = sqlite3_column_int64(stmt, 4); lenses = g_slist_append(lenses, row); } sqlite3_finalize(stmt); g_free(bind1); g_free(bind2); return lenses; } Row* row_new() { Row* row = g_new0(Row, 1); row->id = 0; return row; } void row_free(gpointer data, gpointer user_data) { Row* row = (Row *) data; g_free(row->sn); g_free(row->data); g_free(row->notes); g_free(row); } void dump_storage(CsvParser* parser) { gint rc; sqlite3_stmt* stmt; CvsError* err = NULL; if (parser == NULL) return; rc = sqlite3_prepare_v2(db, all, -1, &stmt, 0); if (rc != SQLITE_OK) { gchar* err = g_strdup_printf("Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db)); fprintf(stderr, err); show_message(GTK_MESSAGE_ERROR, err); g_free(err); sqlite3_finalize(stmt); return; } Record* row = g_new0(Record, 1); row->maker = g_strdup("maker"); row->sn = g_strdup("sn"); row->data = g_strdup("lens"); row->notes = g_strdup("notes"); row->date = g_strdup("date"); csv_parser_dump_row(parser, row, &err); csv_record_free(row); if (err) { gchar* msg = g_strdup_printf("%zu: %s\n", err->line, err->message); fprintf(stderr, msg); show_message(GTK_MESSAGE_ERROR, msg); g_free(msg); csv_error_clear(&err); sqlite3_finalize(stmt); return; } while (sqlite3_step(stmt) == SQLITE_ROW) { row = g_new0(Record, 1); row->maker = g_strdup((const gchar*)sqlite3_column_text(stmt, 0)); row->sn = g_strdup((const gchar*)sqlite3_column_text(stmt, 1)); row->data = g_strdup((const gchar*)sqlite3_column_text(stmt, 2)); row->notes = g_strdup((const gchar*)sqlite3_column_text(stmt, 3)); row->date = g_strdup((const gchar*)sqlite3_column_text(stmt, 4)); csv_parser_dump_row(parser, row, &err); csv_record_free(row); if (err) { gchar* msg = g_strdup_printf("%zu: %s\n", err->line, err->message); fprintf(stderr, msg); show_message(GTK_MESSAGE_ERROR, msg); g_free(msg); csv_error_clear(&err); sqlite3_finalize(stmt); return; } } sqlite3_finalize(stmt); }