]>
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) 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
) {
416 g_free(maker
->maker_name
);
420 gint
delete_maker(const gchar
* maker
, gchar
** error
) {
425 gint id
= get_maker_id(maker
);
426 rc
= sqlite3_prepare_v2(db
, "delete from lensmaker where id = ?1", -1, &stmt
, 0);
427 if (rc
!= SQLITE_OK
) {
428 gchar
* err
= g_strdup_printf("Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db
));
429 fprintf(stderr
, err
);
430 show_err_msg(GTK_MESSAGE_ERROR
, err
);
433 *error
= g_strdup(sqlite3_errmsg(db
));
434 sqlite3_finalize(stmt
);
439 rc
= sqlite3_bind_int(stmt
, 1, id
);
441 gchar
* err
= g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
442 fprintf(stderr
, err
);
443 show_err_msg(GTK_MESSAGE_ERROR
, err
);
446 *error
= g_strdup(sqlite3_errmsg(db
));
447 sqlite3_finalize(stmt
);
452 //fprintf(stderr, "%s\n", sqlite3_expanded_sql(stmt));
453 rc
= sqlite3_step(stmt
);
454 if (rc
!= SQLITE_DONE
) {
455 gchar
* err
= g_strdup_printf("Step failure: %s\n", sqlite3_errmsg(db
));
456 fprintf(stderr
, err
);
457 show_err_msg(GTK_MESSAGE_ERROR
, err
);
460 *error
= g_strdup(sqlite3_errmsg(db
));
461 sqlite3_finalize(stmt
);
466 sqlite3_finalize(stmt
);
471 GSList
* get_makers() {
474 GSList
* makers
= NULL
;
476 rc
= sqlite3_prepare_v2(db
, "select maker from lensmaker order by id", -1, &stmt
, 0);
477 if (rc
!= SQLITE_OK
) {
478 gchar
* err
= g_strdup_printf("Failed to fetch data: %s\n", sqlite3_errmsg(db
));
479 fprintf(stderr
, err
);
480 show_err_msg(GTK_MESSAGE_ERROR
, err
);
482 sqlite3_finalize(stmt
);
486 while (sqlite3_step(stmt
) == SQLITE_ROW
) {
487 gchar
* maker
= g_strdup((const gchar
*)sqlite3_column_text(stmt
, 0));
488 makers
= g_slist_append(makers
, maker
);
491 sqlite3_finalize(stmt
);
496 gint
get_maker_id(const gchar
* maker
) {
501 rc
= sqlite3_prepare_v2(db
, "select id from lensmaker where maker = ?1", -1, &stmt
, 0);
502 if (rc
!= SQLITE_OK
) {
503 gchar
* err
= g_strdup_printf("Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db
));
504 fprintf(stderr
, err
);
505 show_err_msg(GTK_MESSAGE_ERROR
, err
);
507 sqlite3_finalize(stmt
);
511 rc
= sqlite3_bind_text(stmt
, 1, maker
, -1, NULL
);
513 gchar
* err
= g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
514 fprintf(stderr
, err
);
515 show_err_msg(GTK_MESSAGE_ERROR
, err
);
517 sqlite3_finalize(stmt
);
521 if (sqlite3_step(stmt
) == SQLITE_ROW
) {
522 id
= sqlite3_column_int64(stmt
, 0);
525 sqlite3_finalize(stmt
);
530 GSList
* get_lens_by_maker(gint id
) {
533 GSList
* lenses
= NULL
;
535 rc
= sqlite3_prepare_v2(db
, "select sn, description, notes, id from lens where maker = ?1", -1, &stmt
, 0);
536 if (rc
!= SQLITE_OK
) {
537 gchar
* err
= g_strdup_printf("Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db
));
538 fprintf(stderr
, err
);
539 show_err_msg(GTK_MESSAGE_ERROR
, err
);
541 sqlite3_finalize(stmt
);
545 rc
= sqlite3_bind_int64(stmt
, 1, id
);
547 gchar
* err
= g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
548 fprintf(stderr
, err
);
549 show_err_msg(GTK_MESSAGE_ERROR
, err
);
551 sqlite3_finalize(stmt
);
555 while (sqlite3_step(stmt
) == SQLITE_ROW
) {
556 Row
* row
= row_new();
557 row
->sn
= g_strdup((const gchar
*)sqlite3_column_text(stmt
, 0));
558 row
->data
= g_strdup((const gchar
*)sqlite3_column_text(stmt
, 1));
559 row
->notes
= g_strdup((const gchar
*)sqlite3_column_text(stmt
, 2));
560 row
->id
= sqlite3_column_int64(stmt
, 3);
561 lenses
= g_slist_append(lenses
, row
);
564 sqlite3_finalize(stmt
);
569 GSList
* get_lens_by_search(const Row
* row
) {
572 GSList
* lenses
= NULL
;
577 if (row
->notes
&& row
->data
) {
578 sql
= g_strconcat(search
, " where ",
579 "upper(m.maker) like '%' || upper(?1) || '%' and upper(l.description) like '%' || upper(?2) || '%'",
581 bind1
= g_strdup(row
->notes
);
582 bind2
= g_strdup(row
->data
);
583 } else if (!row
->notes
&& !row
->data
) {
584 sql
= g_strconcat(search
, NULL
);
587 sql
= g_strconcat(search
, " where upper(m.maker) like '%' || upper(?1) || '%'", NULL
);
588 bind1
= g_strdup(row
->notes
);
590 sql
= g_strconcat(search
, " where upper(l.description) like '%' || upper(?1) || '%'", NULL
);
591 bind1
= g_strdup(row
->data
);
595 rc
= sqlite3_prepare_v2(db
, sql
, -1, &stmt
, 0);
596 if (rc
!= SQLITE_OK
) {
597 gchar
* err
= g_strdup_printf("Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db
));
598 fprintf(stderr
, err
);
599 show_err_msg(GTK_MESSAGE_ERROR
, err
);
601 sqlite3_finalize(stmt
);
606 rc
= sqlite3_bind_text(stmt
, 1, bind1
, -1, NULL
);
608 gchar
* err
= g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
609 fprintf(stderr
, err
);
610 show_err_msg(GTK_MESSAGE_ERROR
, err
);
612 sqlite3_finalize(stmt
);
618 rc
= sqlite3_bind_text(stmt
, 2, bind2
, -1, NULL
);
620 gchar
* err
= g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
621 fprintf(stderr
, err
);
622 show_err_msg(GTK_MESSAGE_ERROR
, err
);
624 sqlite3_finalize(stmt
);
629 while (sqlite3_step(stmt
) == SQLITE_ROW
) {
630 Row
* row
= row_new();
631 row
->id
= sqlite3_column_int64(stmt
, 0);
632 row
->sn
= g_strdup((const gchar
*)sqlite3_column_text(stmt
, 1));
633 row
->data
= g_strdup((const gchar
*)sqlite3_column_text(stmt
, 2));
634 row
->notes
= g_strdup((const gchar
*)sqlite3_column_text(stmt
, 3));
635 row
->maker
= sqlite3_column_int64(stmt
, 4);
636 lenses
= g_slist_append(lenses
, row
);
639 sqlite3_finalize(stmt
);
647 Row
* row
= g_new0(Row
, 1);
653 void row_free(gpointer data
, gpointer user_data
) {
654 Row
* row
= (Row
*) data
;
This page took 0.843779 seconds and 6 git commands to generate.