X-Git-Url: http://git.datanom.net/lensdb.git/blobdiff_plain/1add773a4f57f945301ebfd9b77f3de70f377441..1689100fc9cd30b34628a2ab6fac95ee7f0977db:/src/sqlite-storage.c diff --git a/src/sqlite-storage.c b/src/sqlite-storage.c index 0154528..2fc1147 100644 --- a/src/sqlite-storage.c +++ b/src/sqlite-storage.c @@ -1,2 +1,662 @@ +/* + * 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," + "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"; + +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_err_msg(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_err_msg(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_err_msg(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_err_msg(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_err_msg(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_err_msg(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_err_msg(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_err_msg(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_err_msg(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_err_msg(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_err_msg(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_err_msg(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_err_msg(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_err_msg(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_err_msg(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_err_msg(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_err_msg(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_err_msg(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_err_msg(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 status = -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_err_msg(GTK_MESSAGE_ERROR, err); + g_free(err); + if (error) + *error = g_strdup(sqlite3_errmsg(db)); + sqlite3_finalize(stmt); + return status; + } + + 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_err_msg(GTK_MESSAGE_ERROR, err); + g_free(err); + if (error) + *error = g_strdup(sqlite3_errmsg(db)); + sqlite3_finalize(stmt); + return status; + } + + 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_err_msg(GTK_MESSAGE_ERROR, err); + g_free(err); + if (error) + *error = g_strdup(sqlite3_errmsg(db)); + } else { + status = sqlite3_column_int64(stmt, 0); + } + + sqlite3_finalize(stmt); + + return status; +} + +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_err_msg(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_err_msg(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_err_msg(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_err_msg(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_err_msg(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_err_msg(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_err_msg(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_err_msg(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_err_msg(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_err_msg(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_err_msg(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_err_msg(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_err_msg(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_err_msg(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_err_msg(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_err_msg(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_err_msg(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); +}