]>
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 "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)", -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_DONE
) {
316 gchar
* err
= g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
317 fprintf(stderr
, err
);
318 show_err_msg(GTK_MESSAGE_ERROR
, err
);
321 *error
= g_strdup(sqlite3_errmsg(db
));
322 sqlite3_finalize(stmt
);
327 sqlite3_finalize(stmt
);
332 Maker
* get_maker(gint id
) {
337 rc
= sqlite3_prepare_v2(db
, "select maker, page from lensmaker where id = ?1", -1, &stmt
, 0);
338 if (rc
!= SQLITE_OK
) {
339 gchar
* err
= g_strdup_printf("Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db
));
340 fprintf(stderr
, err
);
341 show_err_msg(GTK_MESSAGE_ERROR
, err
);
343 sqlite3_finalize(stmt
);
347 rc
= sqlite3_bind_int64(stmt
, 1, id
);
349 gchar
* err
= g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
350 fprintf(stderr
, err
);
351 show_err_msg(GTK_MESSAGE_ERROR
, err
);
353 sqlite3_finalize(stmt
);
357 if (sqlite3_step(stmt
) == SQLITE_ROW
) {
358 maker
= g_new0(Maker
, 1);
360 maker
->maker_name
= g_strdup((const gchar
*)sqlite3_column_text(stmt
, 0));
361 maker
->page
= sqlite3_column_int(stmt
, 1);
364 sqlite3_finalize(stmt
);
369 void update_maker(const Maker
* maker
) {
373 rc
= sqlite3_prepare_v2(db
, "update lensmaker set page = ?1 where id = ?2", -1, &stmt
, 0);
374 if (rc
!= SQLITE_OK
) {
375 gchar
* err
= g_strdup_printf("Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db
));
376 fprintf(stderr
, err
);
377 show_err_msg(GTK_MESSAGE_ERROR
, err
);
379 sqlite3_finalize(stmt
);
383 rc
= sqlite3_bind_int(stmt
, 1, maker
->page
);
385 gchar
* err
= g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
386 fprintf(stderr
, err
);
387 show_err_msg(GTK_MESSAGE_ERROR
, err
);
389 sqlite3_finalize(stmt
);
393 rc
= sqlite3_bind_int64(stmt
, 2, maker
->id
);
395 gchar
* err
= g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
396 fprintf(stderr
, err
);
397 show_err_msg(GTK_MESSAGE_ERROR
, err
);
399 sqlite3_finalize(stmt
);
403 //fprintf(stderr, "%s\n", sqlite3_expanded_sql(stmt));
404 rc
= sqlite3_step(stmt
);
405 if (rc
!= SQLITE_DONE
) {
406 gchar
* err
= g_strdup_printf("Step failure: %s\n", sqlite3_errmsg(db
));
407 fprintf(stderr
, err
);
408 show_err_msg(GTK_MESSAGE_ERROR
, err
);
410 sqlite3_finalize(stmt
);
413 sqlite3_finalize(stmt
);
416 void maker_free(Maker
* maker
) {
417 g_free(maker
->maker_name
);
421 gint
delete_maker(const gchar
* maker
, gchar
** error
) {
426 gint id
= get_maker_id(maker
);
427 rc
= sqlite3_prepare_v2(db
, "delete from lensmaker where id = ?1", -1, &stmt
, 0);
428 if (rc
!= SQLITE_OK
) {
429 gchar
* err
= g_strdup_printf("Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db
));
430 fprintf(stderr
, err
);
431 show_err_msg(GTK_MESSAGE_ERROR
, err
);
434 *error
= g_strdup(sqlite3_errmsg(db
));
435 sqlite3_finalize(stmt
);
440 rc
= sqlite3_bind_int(stmt
, 1, id
);
442 gchar
* err
= g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
443 fprintf(stderr
, err
);
444 show_err_msg(GTK_MESSAGE_ERROR
, err
);
447 *error
= g_strdup(sqlite3_errmsg(db
));
448 sqlite3_finalize(stmt
);
453 //fprintf(stderr, "%s\n", sqlite3_expanded_sql(stmt));
454 rc
= sqlite3_step(stmt
);
455 if (rc
!= SQLITE_DONE
) {
456 gchar
* err
= g_strdup_printf("Step failure: %s\n", sqlite3_errmsg(db
));
457 fprintf(stderr
, err
);
458 show_err_msg(GTK_MESSAGE_ERROR
, err
);
461 *error
= g_strdup(sqlite3_errmsg(db
));
462 sqlite3_finalize(stmt
);
467 sqlite3_finalize(stmt
);
472 GSList
* get_makers() {
475 GSList
* makers
= NULL
;
477 rc
= sqlite3_prepare_v2(db
, "select maker from lensmaker order by id", -1, &stmt
, 0);
478 if (rc
!= SQLITE_OK
) {
479 gchar
* err
= g_strdup_printf("Failed to fetch data: %s\n", sqlite3_errmsg(db
));
480 fprintf(stderr
, err
);
481 show_err_msg(GTK_MESSAGE_ERROR
, err
);
483 sqlite3_finalize(stmt
);
487 while (sqlite3_step(stmt
) == SQLITE_ROW
) {
488 gchar
* maker
= g_strdup((const gchar
*)sqlite3_column_text(stmt
, 0));
489 makers
= g_slist_append(makers
, maker
);
492 sqlite3_finalize(stmt
);
497 gint
get_maker_id(const gchar
* maker
) {
502 rc
= sqlite3_prepare_v2(db
, "select id from lensmaker where maker = ?1", -1, &stmt
, 0);
503 if (rc
!= SQLITE_OK
) {
504 gchar
* err
= g_strdup_printf("Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db
));
505 fprintf(stderr
, err
);
506 show_err_msg(GTK_MESSAGE_ERROR
, err
);
508 sqlite3_finalize(stmt
);
512 rc
= sqlite3_bind_text(stmt
, 1, maker
, -1, NULL
);
514 gchar
* err
= g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
515 fprintf(stderr
, err
);
516 show_err_msg(GTK_MESSAGE_ERROR
, err
);
518 sqlite3_finalize(stmt
);
522 if (sqlite3_step(stmt
) == SQLITE_ROW
) {
523 id
= sqlite3_column_int64(stmt
, 0);
526 sqlite3_finalize(stmt
);
531 GSList
* get_lens_by_maker(gint id
) {
534 GSList
* lenses
= NULL
;
536 rc
= sqlite3_prepare_v2(db
, "select sn, description, notes, id from lens where maker = ?1", -1, &stmt
, 0);
537 if (rc
!= SQLITE_OK
) {
538 gchar
* err
= g_strdup_printf("Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db
));
539 fprintf(stderr
, err
);
540 show_err_msg(GTK_MESSAGE_ERROR
, err
);
542 sqlite3_finalize(stmt
);
546 rc
= sqlite3_bind_int64(stmt
, 1, id
);
548 gchar
* err
= g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
549 fprintf(stderr
, err
);
550 show_err_msg(GTK_MESSAGE_ERROR
, err
);
552 sqlite3_finalize(stmt
);
556 while (sqlite3_step(stmt
) == SQLITE_ROW
) {
557 Row
* row
= row_new();
558 row
->sn
= g_strdup((const gchar
*)sqlite3_column_text(stmt
, 0));
559 row
->data
= g_strdup((const gchar
*)sqlite3_column_text(stmt
, 1));
560 row
->notes
= g_strdup((const gchar
*)sqlite3_column_text(stmt
, 2));
561 row
->id
= sqlite3_column_int64(stmt
, 3);
562 lenses
= g_slist_append(lenses
, row
);
565 sqlite3_finalize(stmt
);
570 GSList
* get_lens_by_search(const Row
* row
) {
573 GSList
* lenses
= NULL
;
578 if (row
->notes
&& row
->data
) {
579 sql
= g_strconcat(search
, " where ",
580 "upper(m.maker) like '%' || upper(?1) || '%' and upper(l.description) like '%' || upper(?2) || '%'",
582 bind1
= g_strdup(row
->notes
);
583 bind2
= g_strdup(row
->data
);
584 } else if (!row
->notes
&& !row
->data
) {
585 sql
= g_strconcat(search
, NULL
);
588 sql
= g_strconcat(search
, " where upper(m.maker) like '%' || upper(?1) || '%'", NULL
);
589 bind1
= g_strdup(row
->notes
);
591 sql
= g_strconcat(search
, " where upper(l.description) like '%' || upper(?1) || '%'", NULL
);
592 bind1
= g_strdup(row
->data
);
596 rc
= sqlite3_prepare_v2(db
, sql
, -1, &stmt
, 0);
597 if (rc
!= SQLITE_OK
) {
598 gchar
* err
= g_strdup_printf("Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db
));
599 fprintf(stderr
, err
);
600 show_err_msg(GTK_MESSAGE_ERROR
, err
);
602 sqlite3_finalize(stmt
);
607 rc
= sqlite3_bind_text(stmt
, 1, bind1
, -1, NULL
);
609 gchar
* err
= g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
610 fprintf(stderr
, err
);
611 show_err_msg(GTK_MESSAGE_ERROR
, err
);
613 sqlite3_finalize(stmt
);
619 rc
= sqlite3_bind_text(stmt
, 2, bind2
, -1, NULL
);
621 gchar
* err
= g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
622 fprintf(stderr
, err
);
623 show_err_msg(GTK_MESSAGE_ERROR
, err
);
625 sqlite3_finalize(stmt
);
630 while (sqlite3_step(stmt
) == SQLITE_ROW
) {
631 Row
* row
= row_new();
632 row
->id
= sqlite3_column_int64(stmt
, 0);
633 row
->sn
= g_strdup((const gchar
*)sqlite3_column_text(stmt
, 1));
634 row
->data
= g_strdup((const gchar
*)sqlite3_column_text(stmt
, 2));
635 row
->notes
= g_strdup((const gchar
*)sqlite3_column_text(stmt
, 3));
636 row
->maker
= sqlite3_column_int64(stmt
, 4);
637 lenses
= g_slist_append(lenses
, row
);
640 sqlite3_finalize(stmt
);
648 Row
* row
= g_new0(Row
, 1);
654 void row_free(gpointer data
, gpointer user_data
) {
655 Row
* row
= (Row
*) data
;
This page took 1.488296 seconds and 6 git commands to generate.