]>
git.datanom.net - lensdb.git/blob - src/sqlite-storage.c
2fc114792b9473699c1a9714bfa638ab4c04fbdd
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 "PRIMARY KEY(id AUTOINCREMENT),"
42 "constraint fk_maker foreign key (maker) references lensmaker (id)"
43 " on delete cascade);";
44 static gchar
* search
= "SELECT l.id, l.sn, l.description, l.notes, l.maker"
45 " FROM lens l JOIN lensmaker m on l.maker = m.id";
47 void close_storage() {
52 gint
init_storage(const gchar
* path
) {
53 gboolean init_db
= FALSE
;
57 gchar
* file
= g_strconcat(path
, "/", DBNAME
, NULL
);
58 if (!g_file_test(file
, G_FILE_TEST_EXISTS
)) {
61 rc
= sqlite3_open(file
, &db
);
65 gchar
* err
= g_strdup_printf("Failed to open DB: %s\n", sqlite3_errmsg(db
));
67 show_err_msg(GTK_MESSAGE_ERROR
, err
);
73 rc
= sqlite3_exec(db
, "PRAGMA foreign_keys = ON", 0, 0, &ErrMsg
);
74 if (rc
!= SQLITE_OK
) {
75 gchar
* err
= g_strdup_printf("SQL error: %s\n", ErrMsg
);
77 show_err_msg(GTK_MESSAGE_ERROR
, err
);
82 rc
= sqlite3_exec(db
, lensmaker
, 0, 0, &ErrMsg
);
83 if (rc
!= SQLITE_OK
) {
84 gchar
* err
= g_strdup_printf("SQL error: %s\n", ErrMsg
);
86 show_err_msg(GTK_MESSAGE_ERROR
, err
);
91 rc
= sqlite3_exec(db
, lens
, 0, 0, &ErrMsg
);
92 if (rc
!= SQLITE_OK
) {
93 gchar
* err
= g_strdup_printf("SQL error: %s\n", ErrMsg
);
95 show_err_msg(GTK_MESSAGE_ERROR
, err
);
104 gint
add_lens(Row
* row
) {
109 rc
= sqlite3_prepare_v2(db
, "insert into lens (sn, maker, description, notes) values (?1, ?2, ?3, ?4) returning id", -1, &stmt
, 0);
110 if (rc
!= SQLITE_OK
) {
111 gchar
* err
= g_strdup_printf("Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db
));
112 fprintf(stderr
, err
);
113 show_err_msg(GTK_MESSAGE_ERROR
, err
);
115 sqlite3_finalize(stmt
);
119 rc
= sqlite3_bind_text(stmt
, 1, row
->sn
, -1, NULL
);
121 gchar
* err
= g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
122 fprintf(stderr
, err
);
123 show_err_msg(GTK_MESSAGE_ERROR
, err
);
125 sqlite3_finalize(stmt
);
129 rc
= sqlite3_bind_int(stmt
, 2, row
->maker
);
131 gchar
* err
= g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
132 fprintf(stderr
, err
);
133 show_err_msg(GTK_MESSAGE_ERROR
, err
);
135 sqlite3_finalize(stmt
);
139 rc
= sqlite3_bind_text(stmt
, 3, row
->data
, -1, NULL
);
141 gchar
* err
= g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
142 fprintf(stderr
, err
);
143 show_err_msg(GTK_MESSAGE_ERROR
, err
);
145 sqlite3_finalize(stmt
);
149 rc
= sqlite3_bind_text(stmt
, 4, row
->notes
, -1, NULL
);
151 gchar
* err
= g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
152 fprintf(stderr
, err
);
153 show_err_msg(GTK_MESSAGE_ERROR
, err
);
155 sqlite3_finalize(stmt
);
159 rc
= sqlite3_step(stmt
);
160 if (rc
!= SQLITE_ROW
) {
161 gchar
* err
= g_strdup_printf("Step failure: %s\n", sqlite3_errmsg(db
));
162 fprintf(stderr
, err
);
163 show_err_msg(GTK_MESSAGE_ERROR
, err
);
166 row
->id
= sqlite3_column_int64(stmt
, 0);
170 sqlite3_finalize(stmt
);
175 gint
update_lens(const Row
* row
) {
180 rc
= sqlite3_prepare_v2(db
, "update lens set sn = ?1, description = ?2, notes = ?3 where id = ?4", -1, &stmt
, 0);
181 if (rc
!= SQLITE_OK
) {
182 gchar
* err
= g_strdup_printf("Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db
));
183 fprintf(stderr
, err
);
184 show_err_msg(GTK_MESSAGE_ERROR
, err
);
186 sqlite3_finalize(stmt
);
190 rc
= sqlite3_bind_text(stmt
, 1, row
->sn
, -1, NULL
);
192 gchar
* err
= g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
193 fprintf(stderr
, err
);
194 show_err_msg(GTK_MESSAGE_ERROR
, err
);
196 sqlite3_finalize(stmt
);
200 rc
= sqlite3_bind_text(stmt
, 2, row
->data
, -1, NULL
);
202 gchar
* err
= g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
203 fprintf(stderr
, err
);
204 show_err_msg(GTK_MESSAGE_ERROR
, err
);
206 sqlite3_finalize(stmt
);
210 rc
= sqlite3_bind_text(stmt
, 3, row
->notes
, -1, NULL
);
212 gchar
* err
= g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
213 fprintf(stderr
, err
);
214 show_err_msg(GTK_MESSAGE_ERROR
, err
);
216 sqlite3_finalize(stmt
);
220 rc
= sqlite3_bind_int64(stmt
, 4, row
->id
);
222 gchar
* err
= g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
223 fprintf(stderr
, err
);
224 show_err_msg(GTK_MESSAGE_ERROR
, err
);
226 sqlite3_finalize(stmt
);
230 rc
= sqlite3_step(stmt
);
231 if (rc
!= SQLITE_DONE
) {
232 gchar
* err
= g_strdup_printf("Step failure: %s\n", sqlite3_errmsg(db
));
233 fprintf(stderr
, err
);
234 show_err_msg(GTK_MESSAGE_ERROR
, err
);
240 sqlite3_finalize(stmt
);
245 gint
delete_lens(const Row
* row
) {
250 rc
= sqlite3_prepare_v2(db
, "delete from lens where id = ?1", -1, &stmt
, 0);
251 if (rc
!= SQLITE_OK
) {
252 gchar
* err
= g_strdup_printf("Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db
));
253 fprintf(stderr
, err
);
254 show_err_msg(GTK_MESSAGE_ERROR
, err
);
256 sqlite3_finalize(stmt
);
260 rc
= sqlite3_bind_int64(stmt
, 1, row
->id
);
262 gchar
* err
= g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
263 fprintf(stderr
, err
);
264 show_err_msg(GTK_MESSAGE_ERROR
, err
);
266 sqlite3_finalize(stmt
);
270 rc
= sqlite3_step(stmt
);
271 if (rc
!= SQLITE_DONE
) {
272 gchar
* err
= g_strdup_printf("Step failure: %s\n", sqlite3_errmsg(db
));
273 fprintf(stderr
, err
);
274 show_err_msg(GTK_MESSAGE_ERROR
, err
);
280 sqlite3_finalize(stmt
);
285 gint
add_maker(const gchar
* maker
, gchar
** error
) {
290 rc
= sqlite3_prepare_v2(db
, "insert into lensmaker (maker) values (?1) returning id", -1, &stmt
, 0);
291 if (rc
!= SQLITE_OK
) {
292 gchar
* err
= g_strdup_printf("Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db
));
293 fprintf(stderr
, err
);
294 show_err_msg(GTK_MESSAGE_ERROR
, err
);
297 *error
= g_strdup(sqlite3_errmsg(db
));
298 sqlite3_finalize(stmt
);
302 rc
= sqlite3_bind_text(stmt
, 1, maker
, -1, NULL
);
304 gchar
* err
= g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
305 fprintf(stderr
, err
);
306 show_err_msg(GTK_MESSAGE_ERROR
, err
);
309 *error
= g_strdup(sqlite3_errmsg(db
));
310 sqlite3_finalize(stmt
);
314 rc
= sqlite3_step(stmt
);
315 if (rc
!= SQLITE_ROW
) {
316 gchar
* err
= g_strdup_printf("Couldn't insert maker: %s\n", sqlite3_errmsg(db
));
317 fprintf(stderr
, err
);
318 show_err_msg(GTK_MESSAGE_ERROR
, err
);
321 *error
= g_strdup(sqlite3_errmsg(db
));
323 status
= sqlite3_column_int64(stmt
, 0);
326 sqlite3_finalize(stmt
);
331 Maker
* get_maker(gint id
) {
336 rc
= sqlite3_prepare_v2(db
, "select maker, page from lensmaker where id = ?1", -1, &stmt
, 0);
337 if (rc
!= SQLITE_OK
) {
338 gchar
* err
= g_strdup_printf("Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db
));
339 fprintf(stderr
, err
);
340 show_err_msg(GTK_MESSAGE_ERROR
, err
);
342 sqlite3_finalize(stmt
);
346 rc
= sqlite3_bind_int64(stmt
, 1, id
);
348 gchar
* err
= g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
349 fprintf(stderr
, err
);
350 show_err_msg(GTK_MESSAGE_ERROR
, err
);
352 sqlite3_finalize(stmt
);
356 if (sqlite3_step(stmt
) == SQLITE_ROW
) {
357 maker
= g_new0(Maker
, 1);
359 maker
->maker_name
= g_strdup((const gchar
*)sqlite3_column_text(stmt
, 0));
360 maker
->page
= sqlite3_column_int(stmt
, 1);
363 sqlite3_finalize(stmt
);
368 void update_maker(const Maker
* maker
) {
372 rc
= sqlite3_prepare_v2(db
, "update lensmaker set page = ?1 where id = ?2", -1, &stmt
, 0);
373 if (rc
!= SQLITE_OK
) {
374 gchar
* err
= g_strdup_printf("Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db
));
375 fprintf(stderr
, err
);
376 show_err_msg(GTK_MESSAGE_ERROR
, err
);
378 sqlite3_finalize(stmt
);
382 rc
= sqlite3_bind_int(stmt
, 1, maker
->page
);
384 gchar
* err
= g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
385 fprintf(stderr
, err
);
386 show_err_msg(GTK_MESSAGE_ERROR
, err
);
388 sqlite3_finalize(stmt
);
392 rc
= sqlite3_bind_int64(stmt
, 2, maker
->id
);
394 gchar
* err
= g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
395 fprintf(stderr
, err
);
396 show_err_msg(GTK_MESSAGE_ERROR
, err
);
398 sqlite3_finalize(stmt
);
402 //fprintf(stderr, "%s\n", sqlite3_expanded_sql(stmt));
403 rc
= sqlite3_step(stmt
);
404 if (rc
!= SQLITE_DONE
) {
405 gchar
* err
= g_strdup_printf("Step failure: %s\n", sqlite3_errmsg(db
));
406 fprintf(stderr
, err
);
407 show_err_msg(GTK_MESSAGE_ERROR
, err
);
409 sqlite3_finalize(stmt
);
412 sqlite3_finalize(stmt
);
415 void maker_free(Maker
* maker
) {
417 g_free(maker
->maker_name
);
422 gint
delete_maker(const gchar
* maker
, gchar
** error
) {
427 gint id
= get_maker_id(maker
);
428 rc
= sqlite3_prepare_v2(db
, "delete from lensmaker where id = ?1", -1, &stmt
, 0);
429 if (rc
!= SQLITE_OK
) {
430 gchar
* err
= g_strdup_printf("Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db
));
431 fprintf(stderr
, err
);
432 show_err_msg(GTK_MESSAGE_ERROR
, err
);
435 *error
= g_strdup(sqlite3_errmsg(db
));
436 sqlite3_finalize(stmt
);
441 rc
= sqlite3_bind_int(stmt
, 1, id
);
443 gchar
* err
= g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
444 fprintf(stderr
, err
);
445 show_err_msg(GTK_MESSAGE_ERROR
, err
);
448 *error
= g_strdup(sqlite3_errmsg(db
));
449 sqlite3_finalize(stmt
);
454 //fprintf(stderr, "%s\n", sqlite3_expanded_sql(stmt));
455 rc
= sqlite3_step(stmt
);
456 if (rc
!= SQLITE_DONE
) {
457 gchar
* err
= g_strdup_printf("Step failure: %s\n", sqlite3_errmsg(db
));
458 fprintf(stderr
, err
);
459 show_err_msg(GTK_MESSAGE_ERROR
, err
);
462 *error
= g_strdup(sqlite3_errmsg(db
));
463 sqlite3_finalize(stmt
);
468 sqlite3_finalize(stmt
);
473 GSList
* get_makers() {
476 GSList
* makers
= NULL
;
478 rc
= sqlite3_prepare_v2(db
, "select maker from lensmaker order by id", -1, &stmt
, 0);
479 if (rc
!= SQLITE_OK
) {
480 gchar
* err
= g_strdup_printf("Failed to fetch data: %s\n", sqlite3_errmsg(db
));
481 fprintf(stderr
, err
);
482 show_err_msg(GTK_MESSAGE_ERROR
, err
);
484 sqlite3_finalize(stmt
);
488 while (sqlite3_step(stmt
) == SQLITE_ROW
) {
489 gchar
* maker
= g_strdup((const gchar
*)sqlite3_column_text(stmt
, 0));
490 makers
= g_slist_append(makers
, maker
);
493 sqlite3_finalize(stmt
);
498 gint
get_maker_id(const gchar
* maker
) {
503 rc
= sqlite3_prepare_v2(db
, "select id from lensmaker where maker = ?1", -1, &stmt
, 0);
504 if (rc
!= SQLITE_OK
) {
505 gchar
* err
= g_strdup_printf("Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db
));
506 fprintf(stderr
, err
);
507 show_err_msg(GTK_MESSAGE_ERROR
, err
);
509 sqlite3_finalize(stmt
);
513 rc
= sqlite3_bind_text(stmt
, 1, maker
, -1, NULL
);
515 gchar
* err
= g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
516 fprintf(stderr
, err
);
517 show_err_msg(GTK_MESSAGE_ERROR
, err
);
519 sqlite3_finalize(stmt
);
523 if (sqlite3_step(stmt
) == SQLITE_ROW
) {
524 id
= sqlite3_column_int64(stmt
, 0);
527 sqlite3_finalize(stmt
);
532 GSList
* get_lens_by_maker(gint id
) {
535 GSList
* lenses
= NULL
;
537 rc
= sqlite3_prepare_v2(db
, "select sn, description, notes, id from lens where maker = ?1", -1, &stmt
, 0);
538 if (rc
!= SQLITE_OK
) {
539 gchar
* err
= g_strdup_printf("Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db
));
540 fprintf(stderr
, err
);
541 show_err_msg(GTK_MESSAGE_ERROR
, err
);
543 sqlite3_finalize(stmt
);
547 rc
= sqlite3_bind_int64(stmt
, 1, id
);
549 gchar
* err
= g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
550 fprintf(stderr
, err
);
551 show_err_msg(GTK_MESSAGE_ERROR
, err
);
553 sqlite3_finalize(stmt
);
557 while (sqlite3_step(stmt
) == SQLITE_ROW
) {
558 Row
* row
= row_new();
559 row
->sn
= g_strdup((const gchar
*)sqlite3_column_text(stmt
, 0));
560 row
->data
= g_strdup((const gchar
*)sqlite3_column_text(stmt
, 1));
561 row
->notes
= g_strdup((const gchar
*)sqlite3_column_text(stmt
, 2));
562 row
->id
= sqlite3_column_int64(stmt
, 3);
563 lenses
= g_slist_append(lenses
, row
);
566 sqlite3_finalize(stmt
);
571 GSList
* get_lens_by_search(const Row
* row
) {
574 GSList
* lenses
= NULL
;
579 if (row
->notes
&& row
->data
) {
580 sql
= g_strconcat(search
, " where ",
581 "upper(m.maker) like '%' || upper(?1) || '%' and upper(l.description) like '%' || upper(?2) || '%'",
583 bind1
= g_strdup(row
->notes
);
584 bind2
= g_strdup(row
->data
);
585 } else if (!row
->notes
&& !row
->data
) {
586 sql
= g_strconcat(search
, NULL
);
589 sql
= g_strconcat(search
, " where upper(m.maker) like '%' || upper(?1) || '%'", NULL
);
590 bind1
= g_strdup(row
->notes
);
592 sql
= g_strconcat(search
, " where upper(l.description) like '%' || upper(?1) || '%'", NULL
);
593 bind1
= g_strdup(row
->data
);
597 rc
= sqlite3_prepare_v2(db
, sql
, -1, &stmt
, 0);
598 if (rc
!= SQLITE_OK
) {
599 gchar
* err
= g_strdup_printf("Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db
));
600 fprintf(stderr
, err
);
601 show_err_msg(GTK_MESSAGE_ERROR
, err
);
603 sqlite3_finalize(stmt
);
608 rc
= sqlite3_bind_text(stmt
, 1, bind1
, -1, NULL
);
610 gchar
* err
= g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
611 fprintf(stderr
, err
);
612 show_err_msg(GTK_MESSAGE_ERROR
, err
);
614 sqlite3_finalize(stmt
);
620 rc
= sqlite3_bind_text(stmt
, 2, bind2
, -1, NULL
);
622 gchar
* err
= g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
623 fprintf(stderr
, err
);
624 show_err_msg(GTK_MESSAGE_ERROR
, err
);
626 sqlite3_finalize(stmt
);
631 while (sqlite3_step(stmt
) == SQLITE_ROW
) {
632 Row
* row
= row_new();
633 row
->id
= sqlite3_column_int64(stmt
, 0);
634 row
->sn
= g_strdup((const gchar
*)sqlite3_column_text(stmt
, 1));
635 row
->data
= g_strdup((const gchar
*)sqlite3_column_text(stmt
, 2));
636 row
->notes
= g_strdup((const gchar
*)sqlite3_column_text(stmt
, 3));
637 row
->maker
= sqlite3_column_int64(stmt
, 4);
638 lenses
= g_slist_append(lenses
, row
);
641 sqlite3_finalize(stmt
);
649 Row
* row
= g_new0(Row
, 1);
655 void row_free(gpointer data
, gpointer user_data
) {
656 Row
* row
= (Row
*) data
;
This page took 0.665499 seconds and 4 git commands to generate.