X-Git-Url: http://git.datanom.net/lensdb.git/blobdiff_plain/043ec3942020c1122ea256604c047442ac059cad..53efa25c70e37813b148f356cf2548a85da4e4de:/src/sqlite-storage.c diff --git a/src/sqlite-storage.c b/src/sqlite-storage.c index 9a0fddf..8957fd3 100644 --- a/src/sqlite-storage.c +++ b/src/sqlite-storage.c @@ -28,19 +28,24 @@ static sqlite3* db = NULL; static gchar* lensmaker = "create table lensmaker(" - "id integer primary key not null AUTOINCREMENT," + "id integer not null," "maker text not null UNIQUE," - "page int DEFAULT -1);"; + "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," + "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) @@ -62,7 +67,7 @@ gint init_storage(const gchar* path) { 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); + show_message(GTK_MESSAGE_ERROR, err); g_free(err); return 1; } @@ -72,7 +77,7 @@ gint init_storage(const gchar* path) { if (rc != SQLITE_OK) { gchar* err = g_strdup_printf("SQL error: %s\n", ErrMsg); fprintf(stderr, err); - show_err_msg(GTK_MESSAGE_ERROR, err); + show_message(GTK_MESSAGE_ERROR, err); g_free(err); sqlite3_free(ErrMsg); ErrMsg = NULL; @@ -81,7 +86,7 @@ gint init_storage(const gchar* path) { if (rc != SQLITE_OK) { gchar* err = g_strdup_printf("SQL error: %s\n", ErrMsg); fprintf(stderr, err); - show_err_msg(GTK_MESSAGE_ERROR, err); + show_message(GTK_MESSAGE_ERROR, err); g_free(err); sqlite3_free(ErrMsg); ErrMsg = NULL; @@ -90,7 +95,7 @@ gint init_storage(const gchar* path) { if (rc != SQLITE_OK) { gchar* err = g_strdup_printf("SQL error: %s\n", ErrMsg); fprintf(stderr, err); - show_err_msg(GTK_MESSAGE_ERROR, err); + show_message(GTK_MESSAGE_ERROR, err); g_free(err); sqlite3_free(ErrMsg); } @@ -99,16 +104,16 @@ 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, "insert into lens (sn, maker, description, notes) values (?1, ?2, ?3, ?4)", -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) { 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); + show_message(GTK_MESSAGE_ERROR, err); g_free(err); sqlite3_finalize(stmt); return status; @@ -118,7 +123,7 @@ gint add_lens(const Row* row) { 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); + show_message(GTK_MESSAGE_ERROR, err); g_free(err); sqlite3_finalize(stmt); return status; @@ -128,7 +133,7 @@ gint add_lens(const Row* row) { 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); + show_message(GTK_MESSAGE_ERROR, err); g_free(err); sqlite3_finalize(stmt); return status; @@ -138,7 +143,7 @@ gint add_lens(const Row* row) { 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); + show_message(GTK_MESSAGE_ERROR, err); g_free(err); sqlite3_finalize(stmt); return status; @@ -148,19 +153,20 @@ gint add_lens(const Row* row) { 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); + show_message(GTK_MESSAGE_ERROR, err); g_free(err); sqlite3_finalize(stmt); return status; } rc = sqlite3_step(stmt); - if (rc != SQLITE_DONE) { + 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); + show_message(GTK_MESSAGE_ERROR, err); g_free(err); } else { + row->id = sqlite3_column_int64(stmt, 0); status = 0; } @@ -178,7 +184,7 @@ gint update_lens(const Row* row) { 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); + show_message(GTK_MESSAGE_ERROR, err); g_free(err); sqlite3_finalize(stmt); return status; @@ -188,7 +194,7 @@ gint update_lens(const Row* row) { 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); + show_message(GTK_MESSAGE_ERROR, err); g_free(err); sqlite3_finalize(stmt); return status; @@ -198,7 +204,7 @@ gint update_lens(const Row* row) { 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); + show_message(GTK_MESSAGE_ERROR, err); g_free(err); sqlite3_finalize(stmt); return status; @@ -208,7 +214,7 @@ gint update_lens(const Row* row) { 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); + show_message(GTK_MESSAGE_ERROR, err); g_free(err); sqlite3_finalize(stmt); return status; @@ -218,7 +224,7 @@ gint update_lens(const Row* row) { 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); + show_message(GTK_MESSAGE_ERROR, err); g_free(err); sqlite3_finalize(stmt); return status; @@ -228,7 +234,7 @@ gint update_lens(const Row* row) { 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); + show_message(GTK_MESSAGE_ERROR, err); g_free(err); } else { status = 0; @@ -239,51 +245,90 @@ gint update_lens(const Row* row) { return status; } -gint add_maker(const gchar* maker, gchar** error) { +gint delete_lens(const Row* row) { gint rc; sqlite3_stmt* stmt; gint status = 1; - rc = sqlite3_prepare_v2(db, "insert into lensmaker (maker) values (?1)", -1, &stmt, 0); + 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_err_msg(GTK_MESSAGE_ERROR, err); + show_message(GTK_MESSAGE_ERROR, err); g_free(err); if (error) *error = g_strdup(sqlite3_errmsg(db)); sqlite3_finalize(stmt); - return status; + 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_err_msg(GTK_MESSAGE_ERROR, err); + show_message(GTK_MESSAGE_ERROR, err); g_free(err); if (error) *error = g_strdup(sqlite3_errmsg(db)); sqlite3_finalize(stmt); - return status; + return id; } rc = sqlite3_step(stmt); - if (rc != SQLITE_OK) { - gchar* err = g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db)); + 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); + show_message(GTK_MESSAGE_ERROR, err); g_free(err); if (error) *error = g_strdup(sqlite3_errmsg(db)); - sqlite3_finalize(stmt); - return status; - } - - status = 0; + } else { + id = sqlite3_column_int64(stmt, 0); + } + sqlite3_finalize(stmt); - return status; + return id; } Maker* get_maker(gint id) { @@ -295,7 +340,7 @@ Maker* get_maker(gint id) { 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); + show_message(GTK_MESSAGE_ERROR, err); g_free(err); sqlite3_finalize(stmt); return NULL; @@ -305,7 +350,7 @@ Maker* get_maker(gint 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); + show_message(GTK_MESSAGE_ERROR, err); g_free(err); sqlite3_finalize(stmt); return NULL; @@ -331,7 +376,7 @@ void update_maker(const Maker* maker) { 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); + show_message(GTK_MESSAGE_ERROR, err); g_free(err); sqlite3_finalize(stmt); return; @@ -341,7 +386,7 @@ void update_maker(const Maker* 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); + show_message(GTK_MESSAGE_ERROR, err); g_free(err); sqlite3_finalize(stmt); return; @@ -351,7 +396,7 @@ void update_maker(const Maker* 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); + show_message(GTK_MESSAGE_ERROR, err); g_free(err); sqlite3_finalize(stmt); return; @@ -362,7 +407,7 @@ void update_maker(const Maker* maker) { 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); + show_message(GTK_MESSAGE_ERROR, err); g_free(err); sqlite3_finalize(stmt); } @@ -371,13 +416,60 @@ void update_maker(const Maker* maker) { } 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_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; } @@ -390,9 +482,9 @@ GSList* get_makers() { 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); + show_message(GTK_MESSAGE_ERROR, err); g_free(err); - + sqlite3_finalize(stmt); return NULL; } @@ -409,13 +501,13 @@ 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) { 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); + show_message(GTK_MESSAGE_ERROR, err); g_free(err); sqlite3_finalize(stmt); return -1; @@ -425,7 +517,7 @@ gint get_maker_id(const gchar* 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); + show_message(GTK_MESSAGE_ERROR, err); g_free(err); sqlite3_finalize(stmt); return -1; @@ -440,12 +532,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; @@ -455,7 +541,7 @@ GSList* get_lens_by_maker(gint id) { 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); + show_message(GTK_MESSAGE_ERROR, err); g_free(err); sqlite3_finalize(stmt); return NULL; @@ -465,7 +551,7 @@ GSList* get_lens_by_maker(gint 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); + show_message(GTK_MESSAGE_ERROR, err); g_free(err); sqlite3_finalize(stmt); return NULL; @@ -515,7 +601,7 @@ GSList* get_lens_by_search(const Row* row) { 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); + show_message(GTK_MESSAGE_ERROR, err); g_free(err); sqlite3_finalize(stmt); return NULL; @@ -526,7 +612,7 @@ GSList* get_lens_by_search(const Row* row) { 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); + show_message(GTK_MESSAGE_ERROR, err); g_free(err); sqlite3_finalize(stmt); return NULL; @@ -538,7 +624,7 @@ GSList* get_lens_by_search(const Row* row) { 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); + show_message(GTK_MESSAGE_ERROR, err); g_free(err); sqlite3_finalize(stmt); return NULL; @@ -577,3 +663,62 @@ void row_free(gpointer data, gpointer user_data) { 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); +} + +