X-Git-Url: http://git.datanom.net/lensdb.git/blobdiff_plain/af57ef1585e9f2f811626ca1bac5ed2a56198521..refs/heads/release/0.9:/src/sqlite-storage.c diff --git a/src/sqlite-storage.c b/src/sqlite-storage.c index 8206387..2fc1147 100644 --- a/src/sqlite-storage.c +++ b/src/sqlite-storage.c @@ -23,19 +23,22 @@ #include #include "sqlite-storage.h" +#include "gui.h" #define DBNAME "lensdb.db" static sqlite3* db = NULL; static gchar* lensmaker = "create table lensmaker(" - "id integer primary key not null AUTOINCREMENT," - "maker text not null," - "page int DEFAULT -1);"; + "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 primary key not null AUTOINCREMENT," + "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" @@ -59,26 +62,38 @@ gint init_storage(const gchar* path) { g_free(file); if (rc) { - fprintf(stderr, "Failed to open DB: %s\n", sqlite3_errmsg(db)); + 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) { - fprintf(stderr, "SQL error: %s\n", ErrMsg); + 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) { - fprintf(stderr, "SQL error: %s\n", ErrMsg); + 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) { - fprintf(stderr, "SQL error: %s\n", ErrMsg); + 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); } } @@ -86,42 +101,69 @@ gint init_storage(const gchar* path) { return 0; } -gint add_lens(const Row* row) { +gint add_lens(Row* row) { gint rc; sqlite3_stmt* stmt; gint status = 1; - rc = sqlite3_prepare_v2(db, "inserte lens (sn, description, notes) values (?1, ?2, ?3)", -1, &stmt, 0); + 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) { - fprintf(stderr, "Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db)); + 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) { - fprintf(stderr, "Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db)); + 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); + rc = sqlite3_bind_int(stmt, 2, row->maker); if (rc) { - fprintf(stderr, "Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db)); + 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); + rc = sqlite3_bind_text(stmt, 3, row->data, -1, NULL); if (rc) { - fprintf(stderr, "Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db)); + 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; } - if (sqlite3_step(stmt) != SQLITE_DONE) { - fprintf(stderr, "Step failure: %s\n", sqlite3_errmsg(db)); + 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; } @@ -137,41 +179,60 @@ gint update_lens(const Row* row) { rc = sqlite3_prepare_v2(db, "update lens set sn = ?1, description = ?2, notes = ?3 where id = ?4", -1, &stmt, 0); if (rc != SQLITE_OK) { - fprintf(stderr, "Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db)); - sqlite3_finalize(stmt); + 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) { - fprintf(stderr, "Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db)); + 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) { - fprintf(stderr, "Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db)); + 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) { - fprintf(stderr, "Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db)); + 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) { - fprintf(stderr, "Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db)); + 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; } - if (sqlite3_step(stmt) != SQLITE_DONE) { - fprintf(stderr, "Step failure: %s\n", sqlite3_errmsg(db)); + 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; } @@ -181,9 +242,89 @@ gint update_lens(const Row* row) { return status; } -gint add_maker(const gchar* maker) { +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; } @@ -194,14 +335,20 @@ Maker* get_maker(gint id) { rc = sqlite3_prepare_v2(db, "select maker, page from lensmaker where id = ?1", -1, &stmt, 0); if (rc != SQLITE_OK) { - fprintf(stderr, "Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db)); + 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) { - fprintf(stderr, "Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db)); + 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; } @@ -224,40 +371,102 @@ void update_maker(const Maker* maker) { rc = sqlite3_prepare_v2(db, "update lensmaker set page = ?1 where id = ?2", -1, &stmt, 0); if (rc != SQLITE_OK) { - fprintf(stderr, "Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db)); + 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_int64(stmt, 1, maker->page); + rc = sqlite3_bind_int(stmt, 1, maker->page); if (rc) { - fprintf(stderr, "Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db)); + 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, 1, maker->id); + rc = sqlite3_bind_int64(stmt, 2, maker->id); if (rc) { - fprintf(stderr, "Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db)); + 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; } - if (sqlite3_step(stmt) != SQLITE_DONE) { - fprintf(stderr, "Step failure: %s\n", sqlite3_errmsg(db)); + //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); + + sqlite3_finalize(stmt); } void maker_free(Maker* maker) { - g_free(maker->maker_name); - g_free(maker); + if (maker) { + g_free(maker->maker_name); + g_free(maker); + } } -gint delete_maker(const gchar* maker) { - gint status = 1; +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; } @@ -266,10 +475,13 @@ GSList* get_makers() { sqlite3_stmt* stmt; GSList* makers = NULL; - rc = sqlite3_prepare_v2(db, "select maker from lensmaker", -1, &stmt, 0); + rc = sqlite3_prepare_v2(db, "select maker from lensmaker order by id", -1, &stmt, 0); if (rc != SQLITE_OK) { - fprintf(stderr, "Failed to fetch data: %s\n", sqlite3_errmsg(db)); - + 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; } @@ -286,18 +498,24 @@ GSList* get_makers() { gint get_maker_id(const gchar* maker) { gint rc; sqlite3_stmt* stmt; - gint id = 0; + gint id = -1; rc = sqlite3_prepare_v2(db, "select id from lensmaker where maker = ?1", -1, &stmt, 0); if (rc != SQLITE_OK) { - fprintf(stderr, "Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db)); + 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) { - fprintf(stderr, "Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db)); + 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; } @@ -311,12 +529,6 @@ gint get_maker_id(const gchar* maker) { return id; } -gint delete_lens(const Row* row) { - gint status = 1; - - return status; -} - GSList* get_lens_by_maker(gint id) { gint rc; sqlite3_stmt* stmt; @@ -324,14 +536,20 @@ GSList* get_lens_by_maker(gint id) { rc = sqlite3_prepare_v2(db, "select sn, description, notes, id from lens where maker = ?1", -1, &stmt, 0); if (rc != SQLITE_OK) { - fprintf(stderr, "Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db)); + 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) { - fprintf(stderr, "Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db)); + 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; } @@ -378,7 +596,10 @@ GSList* get_lens_by_search(const Row* row) { rc = sqlite3_prepare_v2(db, sql, -1, &stmt, 0); if (rc != SQLITE_OK) { - fprintf(stderr, "Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db)); + 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; } @@ -386,7 +607,10 @@ GSList* get_lens_by_search(const Row* row) { if (bind1) { rc = sqlite3_bind_text(stmt, 1, bind1, -1, NULL); if (rc) { - fprintf(stderr, "Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db)); + 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; } @@ -395,7 +619,10 @@ GSList* get_lens_by_search(const Row* row) { if (bind2) { rc = sqlite3_bind_text(stmt, 2, bind2, -1, NULL); if (rc) { - fprintf(stderr, "Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db)); + 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; }