]>
git.datanom.net - lensdb.git/blob - src/sqlite-storage.c
4 * Copyright 2024 Michael Rasmussen <mir@datanom.net>
6 * This program is free software; you can redistribute it and/or modify
7 * it under the terms of the GNU General Public License as published by
8 * the Free Software Foundation; either version 3 of the License, or
9 * (at your option) any later version.
11 * This program is distributed in the hope that it will be useful,
12 * but WITHOUT ANY WARRANTY; without even the implied warranty of
13 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
14 * GNU General Public License for more details.
16 * You should have received a copy of the GNU General Public License
17 * along with this program; if not, write to the Free Software
18 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston,
25 #include "sqlite-storage.h"
27 #define DBNAME "lensdb.db"
29 static sqlite3
* db
= NULL
;
30 static gchar
* lensmaker
= "create table lensmaker("
31 "id integer not null,"
32 "maker text not null UNIQUE,"
33 "page int DEFAULT -1,"
34 "PRIMARY KEY(id AUTOINCREMENT));";
35 static gchar
* lens
= "create table lens("
36 "id integer not null,"
38 "maker integer not null,"
39 "description text not null,"
41 "date_added datetime DEFAULT current_timestamp,"
42 "PRIMARY KEY(id AUTOINCREMENT),"
43 "constraint fk_maker foreign key (maker) references lensmaker (id)"
44 " on delete cascade);";
45 static gchar
* search
= "SELECT l.id, l.sn, l.description, l.notes, l.maker"
46 " FROM lens l JOIN lensmaker m on l.maker = m.id";
47 static gchar
* all
= "SELECT m.maker,l.sn,l.description,l.notes,l.date_added"
48 " FROM lensmaker m JOIN lens l on m.id = l.maker";
50 void close_storage() {
55 gint
init_storage(const gchar
* path
) {
56 gboolean init_db
= FALSE
;
60 gchar
* file
= g_strconcat(path
, "/", DBNAME
, NULL
);
61 if (!g_file_test(file
, G_FILE_TEST_EXISTS
)) {
64 rc
= sqlite3_open(file
, &db
);
68 gchar
* err
= g_strdup_printf("Failed to open DB: %s\n", sqlite3_errmsg(db
));
70 show_err_msg(GTK_MESSAGE_ERROR
, err
);
76 rc
= sqlite3_exec(db
, "PRAGMA foreign_keys = ON", 0, 0, &ErrMsg
);
77 if (rc
!= SQLITE_OK
) {
78 gchar
* err
= g_strdup_printf("SQL error: %s\n", ErrMsg
);
80 show_err_msg(GTK_MESSAGE_ERROR
, err
);
85 rc
= sqlite3_exec(db
, lensmaker
, 0, 0, &ErrMsg
);
86 if (rc
!= SQLITE_OK
) {
87 gchar
* err
= g_strdup_printf("SQL error: %s\n", ErrMsg
);
89 show_err_msg(GTK_MESSAGE_ERROR
, err
);
94 rc
= sqlite3_exec(db
, lens
, 0, 0, &ErrMsg
);
95 if (rc
!= SQLITE_OK
) {
96 gchar
* err
= g_strdup_printf("SQL error: %s\n", ErrMsg
);
98 show_err_msg(GTK_MESSAGE_ERROR
, err
);
100 sqlite3_free(ErrMsg
);
107 gint
add_lens(Row
* row
) {
112 rc
= sqlite3_prepare_v2(db
, "insert into lens (sn, maker, description, notes) values (?1, ?2, ?3, ?4) returning id", -1, &stmt
, 0);
113 if (rc
!= SQLITE_OK
) {
114 gchar
* err
= g_strdup_printf("Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db
));
115 fprintf(stderr
, err
);
116 show_err_msg(GTK_MESSAGE_ERROR
, err
);
118 sqlite3_finalize(stmt
);
122 rc
= sqlite3_bind_text(stmt
, 1, row
->sn
, -1, NULL
);
124 gchar
* err
= g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
125 fprintf(stderr
, err
);
126 show_err_msg(GTK_MESSAGE_ERROR
, err
);
128 sqlite3_finalize(stmt
);
132 rc
= sqlite3_bind_int(stmt
, 2, row
->maker
);
134 gchar
* err
= g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
135 fprintf(stderr
, err
);
136 show_err_msg(GTK_MESSAGE_ERROR
, err
);
138 sqlite3_finalize(stmt
);
142 rc
= sqlite3_bind_text(stmt
, 3, row
->data
, -1, NULL
);
144 gchar
* err
= g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
145 fprintf(stderr
, err
);
146 show_err_msg(GTK_MESSAGE_ERROR
, err
);
148 sqlite3_finalize(stmt
);
152 rc
= sqlite3_bind_text(stmt
, 4, row
->notes
, -1, NULL
);
154 gchar
* err
= g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
155 fprintf(stderr
, err
);
156 show_err_msg(GTK_MESSAGE_ERROR
, err
);
158 sqlite3_finalize(stmt
);
162 rc
= sqlite3_step(stmt
);
163 if (rc
!= SQLITE_ROW
) {
164 gchar
* err
= g_strdup_printf("Step failure: %s\n", sqlite3_errmsg(db
));
165 fprintf(stderr
, err
);
166 show_err_msg(GTK_MESSAGE_ERROR
, err
);
169 row
->id
= sqlite3_column_int64(stmt
, 0);
173 sqlite3_finalize(stmt
);
178 gint
update_lens(const Row
* row
) {
183 rc
= sqlite3_prepare_v2(db
, "update lens set sn = ?1, description = ?2, notes = ?3 where id = ?4", -1, &stmt
, 0);
184 if (rc
!= SQLITE_OK
) {
185 gchar
* err
= g_strdup_printf("Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db
));
186 fprintf(stderr
, err
);
187 show_err_msg(GTK_MESSAGE_ERROR
, err
);
189 sqlite3_finalize(stmt
);
193 rc
= sqlite3_bind_text(stmt
, 1, row
->sn
, -1, NULL
);
195 gchar
* err
= g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
196 fprintf(stderr
, err
);
197 show_err_msg(GTK_MESSAGE_ERROR
, err
);
199 sqlite3_finalize(stmt
);
203 rc
= sqlite3_bind_text(stmt
, 2, row
->data
, -1, NULL
);
205 gchar
* err
= g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
206 fprintf(stderr
, err
);
207 show_err_msg(GTK_MESSAGE_ERROR
, err
);
209 sqlite3_finalize(stmt
);
213 rc
= sqlite3_bind_text(stmt
, 3, row
->notes
, -1, NULL
);
215 gchar
* err
= g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
216 fprintf(stderr
, err
);
217 show_err_msg(GTK_MESSAGE_ERROR
, err
);
219 sqlite3_finalize(stmt
);
223 rc
= sqlite3_bind_int64(stmt
, 4, row
->id
);
225 gchar
* err
= g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
226 fprintf(stderr
, err
);
227 show_err_msg(GTK_MESSAGE_ERROR
, err
);
229 sqlite3_finalize(stmt
);
233 rc
= sqlite3_step(stmt
);
234 if (rc
!= SQLITE_DONE
) {
235 gchar
* err
= g_strdup_printf("Step failure: %s\n", sqlite3_errmsg(db
));
236 fprintf(stderr
, err
);
237 show_err_msg(GTK_MESSAGE_ERROR
, err
);
243 sqlite3_finalize(stmt
);
248 gint
delete_lens(const Row
* row
) {
253 rc
= sqlite3_prepare_v2(db
, "delete from lens where id = ?1", -1, &stmt
, 0);
254 if (rc
!= SQLITE_OK
) {
255 gchar
* err
= g_strdup_printf("Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db
));
256 fprintf(stderr
, err
);
257 show_err_msg(GTK_MESSAGE_ERROR
, err
);
259 sqlite3_finalize(stmt
);
263 rc
= sqlite3_bind_int64(stmt
, 1, row
->id
);
265 gchar
* err
= g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
266 fprintf(stderr
, err
);
267 show_err_msg(GTK_MESSAGE_ERROR
, err
);
269 sqlite3_finalize(stmt
);
273 rc
= sqlite3_step(stmt
);
274 if (rc
!= SQLITE_DONE
) {
275 gchar
* err
= g_strdup_printf("Step failure: %s\n", sqlite3_errmsg(db
));
276 fprintf(stderr
, err
);
277 show_err_msg(GTK_MESSAGE_ERROR
, err
);
283 sqlite3_finalize(stmt
);
288 gint
add_maker(const gchar
* maker
, gchar
** error
) {
293 rc
= sqlite3_prepare_v2(db
, "insert into lensmaker (maker) values (?1) returning id", -1, &stmt
, 0);
294 if (rc
!= SQLITE_OK
) {
295 gchar
* err
= g_strdup_printf("Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db
));
296 fprintf(stderr
, err
);
297 show_err_msg(GTK_MESSAGE_ERROR
, err
);
300 *error
= g_strdup(sqlite3_errmsg(db
));
301 sqlite3_finalize(stmt
);
305 rc
= sqlite3_bind_text(stmt
, 1, maker
, -1, NULL
);
307 gchar
* err
= g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
308 fprintf(stderr
, err
);
309 show_err_msg(GTK_MESSAGE_ERROR
, err
);
312 *error
= g_strdup(sqlite3_errmsg(db
));
313 sqlite3_finalize(stmt
);
317 rc
= sqlite3_step(stmt
);
318 if (rc
!= SQLITE_ROW
) {
319 gchar
* err
= g_strdup_printf("Couldn't insert maker: %s\n", sqlite3_errmsg(db
));
320 fprintf(stderr
, err
);
321 show_err_msg(GTK_MESSAGE_ERROR
, err
);
324 *error
= g_strdup(sqlite3_errmsg(db
));
326 status
= sqlite3_column_int64(stmt
, 0);
329 sqlite3_finalize(stmt
);
334 Maker
* get_maker(gint id
) {
339 rc
= sqlite3_prepare_v2(db
, "select maker, page from lensmaker where id = ?1", -1, &stmt
, 0);
340 if (rc
!= SQLITE_OK
) {
341 gchar
* err
= g_strdup_printf("Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db
));
342 fprintf(stderr
, err
);
343 show_err_msg(GTK_MESSAGE_ERROR
, err
);
345 sqlite3_finalize(stmt
);
349 rc
= sqlite3_bind_int64(stmt
, 1, id
);
351 gchar
* err
= g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
352 fprintf(stderr
, err
);
353 show_err_msg(GTK_MESSAGE_ERROR
, err
);
355 sqlite3_finalize(stmt
);
359 if (sqlite3_step(stmt
) == SQLITE_ROW
) {
360 maker
= g_new0(Maker
, 1);
362 maker
->maker_name
= g_strdup((const gchar
*)sqlite3_column_text(stmt
, 0));
363 maker
->page
= sqlite3_column_int(stmt
, 1);
366 sqlite3_finalize(stmt
);
371 void update_maker(const Maker
* maker
) {
375 rc
= sqlite3_prepare_v2(db
, "update lensmaker set page = ?1 where id = ?2", -1, &stmt
, 0);
376 if (rc
!= SQLITE_OK
) {
377 gchar
* err
= g_strdup_printf("Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db
));
378 fprintf(stderr
, err
);
379 show_err_msg(GTK_MESSAGE_ERROR
, err
);
381 sqlite3_finalize(stmt
);
385 rc
= sqlite3_bind_int(stmt
, 1, maker
->page
);
387 gchar
* err
= g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
388 fprintf(stderr
, err
);
389 show_err_msg(GTK_MESSAGE_ERROR
, err
);
391 sqlite3_finalize(stmt
);
395 rc
= sqlite3_bind_int64(stmt
, 2, maker
->id
);
397 gchar
* err
= g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
398 fprintf(stderr
, err
);
399 show_err_msg(GTK_MESSAGE_ERROR
, err
);
401 sqlite3_finalize(stmt
);
405 //fprintf(stderr, "%s\n", sqlite3_expanded_sql(stmt));
406 rc
= sqlite3_step(stmt
);
407 if (rc
!= SQLITE_DONE
) {
408 gchar
* err
= g_strdup_printf("Step failure: %s\n", sqlite3_errmsg(db
));
409 fprintf(stderr
, err
);
410 show_err_msg(GTK_MESSAGE_ERROR
, err
);
412 sqlite3_finalize(stmt
);
415 sqlite3_finalize(stmt
);
418 void maker_free(Maker
* maker
) {
420 g_free(maker
->maker_name
);
425 gint
delete_maker(const gchar
* maker
, gchar
** error
) {
430 gint id
= get_maker_id(maker
);
431 rc
= sqlite3_prepare_v2(db
, "delete from lensmaker where id = ?1", -1, &stmt
, 0);
432 if (rc
!= SQLITE_OK
) {
433 gchar
* err
= g_strdup_printf("Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db
));
434 fprintf(stderr
, err
);
435 show_err_msg(GTK_MESSAGE_ERROR
, err
);
438 *error
= g_strdup(sqlite3_errmsg(db
));
439 sqlite3_finalize(stmt
);
444 rc
= sqlite3_bind_int(stmt
, 1, id
);
446 gchar
* err
= g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
447 fprintf(stderr
, err
);
448 show_err_msg(GTK_MESSAGE_ERROR
, err
);
451 *error
= g_strdup(sqlite3_errmsg(db
));
452 sqlite3_finalize(stmt
);
457 //fprintf(stderr, "%s\n", sqlite3_expanded_sql(stmt));
458 rc
= sqlite3_step(stmt
);
459 if (rc
!= SQLITE_DONE
) {
460 gchar
* err
= g_strdup_printf("Step failure: %s\n", sqlite3_errmsg(db
));
461 fprintf(stderr
, err
);
462 show_err_msg(GTK_MESSAGE_ERROR
, err
);
465 *error
= g_strdup(sqlite3_errmsg(db
));
466 sqlite3_finalize(stmt
);
471 sqlite3_finalize(stmt
);
476 GSList
* get_makers() {
479 GSList
* makers
= NULL
;
481 rc
= sqlite3_prepare_v2(db
, "select maker from lensmaker order by id", -1, &stmt
, 0);
482 if (rc
!= SQLITE_OK
) {
483 gchar
* err
= g_strdup_printf("Failed to fetch data: %s\n", sqlite3_errmsg(db
));
484 fprintf(stderr
, err
);
485 show_err_msg(GTK_MESSAGE_ERROR
, err
);
487 sqlite3_finalize(stmt
);
491 while (sqlite3_step(stmt
) == SQLITE_ROW
) {
492 gchar
* maker
= g_strdup((const gchar
*)sqlite3_column_text(stmt
, 0));
493 makers
= g_slist_append(makers
, maker
);
496 sqlite3_finalize(stmt
);
501 gint
get_maker_id(const gchar
* maker
) {
506 rc
= sqlite3_prepare_v2(db
, "select id from lensmaker where maker = ?1", -1, &stmt
, 0);
507 if (rc
!= SQLITE_OK
) {
508 gchar
* err
= g_strdup_printf("Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db
));
509 fprintf(stderr
, err
);
510 show_err_msg(GTK_MESSAGE_ERROR
, err
);
512 sqlite3_finalize(stmt
);
516 rc
= sqlite3_bind_text(stmt
, 1, maker
, -1, NULL
);
518 gchar
* err
= g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
519 fprintf(stderr
, err
);
520 show_err_msg(GTK_MESSAGE_ERROR
, err
);
522 sqlite3_finalize(stmt
);
526 if (sqlite3_step(stmt
) == SQLITE_ROW
) {
527 id
= sqlite3_column_int64(stmt
, 0);
530 sqlite3_finalize(stmt
);
535 GSList
* get_lens_by_maker(gint id
) {
538 GSList
* lenses
= NULL
;
540 rc
= sqlite3_prepare_v2(db
, "select sn, description, notes, id from lens where maker = ?1", -1, &stmt
, 0);
541 if (rc
!= SQLITE_OK
) {
542 gchar
* err
= g_strdup_printf("Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db
));
543 fprintf(stderr
, err
);
544 show_err_msg(GTK_MESSAGE_ERROR
, err
);
546 sqlite3_finalize(stmt
);
550 rc
= sqlite3_bind_int64(stmt
, 1, id
);
552 gchar
* err
= g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
553 fprintf(stderr
, err
);
554 show_err_msg(GTK_MESSAGE_ERROR
, err
);
556 sqlite3_finalize(stmt
);
560 while (sqlite3_step(stmt
) == SQLITE_ROW
) {
561 Row
* row
= row_new();
562 row
->sn
= g_strdup((const gchar
*)sqlite3_column_text(stmt
, 0));
563 row
->data
= g_strdup((const gchar
*)sqlite3_column_text(stmt
, 1));
564 row
->notes
= g_strdup((const gchar
*)sqlite3_column_text(stmt
, 2));
565 row
->id
= sqlite3_column_int64(stmt
, 3);
566 lenses
= g_slist_append(lenses
, row
);
569 sqlite3_finalize(stmt
);
574 GSList
* get_lens_by_search(const Row
* row
) {
577 GSList
* lenses
= NULL
;
582 if (row
->notes
&& row
->data
) {
583 sql
= g_strconcat(search
, " where ",
584 "upper(m.maker) like '%' || upper(?1) || '%' and upper(l.description) like '%' || upper(?2) || '%'",
586 bind1
= g_strdup(row
->notes
);
587 bind2
= g_strdup(row
->data
);
588 } else if (!row
->notes
&& !row
->data
) {
589 sql
= g_strconcat(search
, NULL
);
592 sql
= g_strconcat(search
, " where upper(m.maker) like '%' || upper(?1) || '%'", NULL
);
593 bind1
= g_strdup(row
->notes
);
595 sql
= g_strconcat(search
, " where upper(l.description) like '%' || upper(?1) || '%'", NULL
);
596 bind1
= g_strdup(row
->data
);
600 rc
= sqlite3_prepare_v2(db
, sql
, -1, &stmt
, 0);
601 if (rc
!= SQLITE_OK
) {
602 gchar
* err
= g_strdup_printf("Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db
));
603 fprintf(stderr
, err
);
604 show_err_msg(GTK_MESSAGE_ERROR
, err
);
606 sqlite3_finalize(stmt
);
611 rc
= sqlite3_bind_text(stmt
, 1, bind1
, -1, NULL
);
613 gchar
* err
= g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
614 fprintf(stderr
, err
);
615 show_err_msg(GTK_MESSAGE_ERROR
, err
);
617 sqlite3_finalize(stmt
);
623 rc
= sqlite3_bind_text(stmt
, 2, bind2
, -1, NULL
);
625 gchar
* err
= g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
626 fprintf(stderr
, err
);
627 show_err_msg(GTK_MESSAGE_ERROR
, err
);
629 sqlite3_finalize(stmt
);
634 while (sqlite3_step(stmt
) == SQLITE_ROW
) {
635 Row
* row
= row_new();
636 row
->id
= sqlite3_column_int64(stmt
, 0);
637 row
->sn
= g_strdup((const gchar
*)sqlite3_column_text(stmt
, 1));
638 row
->data
= g_strdup((const gchar
*)sqlite3_column_text(stmt
, 2));
639 row
->notes
= g_strdup((const gchar
*)sqlite3_column_text(stmt
, 3));
640 row
->maker
= sqlite3_column_int64(stmt
, 4);
641 lenses
= g_slist_append(lenses
, row
);
644 sqlite3_finalize(stmt
);
652 Row
* row
= g_new0(Row
, 1);
658 void row_free(gpointer data
, gpointer user_data
) {
659 Row
* row
= (Row
*) data
;
666 void dump_storage(CsvParser
* parser
) {
669 CvsError
* err
= NULL
;
671 if (parser
== NULL
) return;
673 rc
= sqlite3_prepare_v2(db
, all
, -1, &stmt
, 0);
674 if (rc
!= SQLITE_OK
) {
675 gchar
* err
= g_strdup_printf("Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db
));
676 fprintf(stderr
, err
);
677 show_err_msg(GTK_MESSAGE_ERROR
, err
);
679 sqlite3_finalize(stmt
);
683 Record
* row
= g_new0(Record
, 1);
684 row
->maker
= g_strdup("maker");
685 row
->sn
= g_strdup("sn");
686 row
->data
= g_strdup("lens");
687 row
->notes
= g_strdup("notes");
688 row
->date
= g_strdup("date");
689 csv_parser_dump_row(parser
, row
, &err
);
690 csv_record_free(row
);
692 gchar
* msg
= g_strdup_printf("%zu: %s\n", err
->line
, err
->message
);
693 fprintf(stderr
, msg
);
694 show_err_msg(GTK_MESSAGE_ERROR
, msg
);
696 csv_error_clear(&err
);
697 sqlite3_finalize(stmt
);
701 while (sqlite3_step(stmt
) == SQLITE_ROW
) {
702 row
= g_new0(Record
, 1);
703 row
->maker
= g_strdup((const gchar
*)sqlite3_column_text(stmt
, 0));
704 row
->sn
= g_strdup((const gchar
*)sqlite3_column_text(stmt
, 1));
705 row
->data
= g_strdup((const gchar
*)sqlite3_column_text(stmt
, 2));
706 row
->notes
= g_strdup((const gchar
*)sqlite3_column_text(stmt
, 3));
707 row
->date
= g_strdup((const gchar
*)sqlite3_column_text(stmt
, 4));
708 csv_parser_dump_row(parser
, row
, &err
);
709 csv_record_free(row
);
711 gchar
* msg
= g_strdup_printf("%zu: %s\n", err
->line
, err
->message
);
712 fprintf(stderr
, msg
);
713 show_err_msg(GTK_MESSAGE_ERROR
, msg
);
715 csv_error_clear(&err
);
716 sqlite3_finalize(stmt
);
721 sqlite3_finalize(stmt
);
This page took 0.968349 seconds and 6 git commands to generate.