]>
git.datanom.net - lensdb.git/blob - src/sqlite-storage.c
9a0fddf1f5b6409460a67756a4a90be6f3f00a27
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 primary key not null AUTOINCREMENT,"
32 "maker text not null UNIQUE,"
33 "page int DEFAULT -1);";
34 static gchar
* lens
= "create table lens("
35 "id integer primary key not null AUTOINCREMENT,"
37 "maker integer not null,"
38 "description text not null,"
40 "constraint fk_maker foreign key (maker) references lensmaker (id)"
41 " on delete cascade);";
42 static gchar
* search
= "SELECT l.id, l.sn, l.description, l.notes, l.maker"
43 " FROM lens l JOIN lensmaker m on l.maker = m.id";
45 void close_storage() {
50 gint
init_storage(const gchar
* path
) {
51 gboolean init_db
= FALSE
;
55 gchar
* file
= g_strconcat(path
, "/", DBNAME
, NULL
);
56 if (!g_file_test(file
, G_FILE_TEST_EXISTS
)) {
59 rc
= sqlite3_open(file
, &db
);
63 gchar
* err
= g_strdup_printf("Failed to open DB: %s\n", sqlite3_errmsg(db
));
65 show_err_msg(GTK_MESSAGE_ERROR
, err
);
71 rc
= sqlite3_exec(db
, "PRAGMA foreign_keys = ON", 0, 0, &ErrMsg
);
72 if (rc
!= SQLITE_OK
) {
73 gchar
* err
= g_strdup_printf("SQL error: %s\n", ErrMsg
);
75 show_err_msg(GTK_MESSAGE_ERROR
, err
);
80 rc
= sqlite3_exec(db
, lensmaker
, 0, 0, &ErrMsg
);
81 if (rc
!= SQLITE_OK
) {
82 gchar
* err
= g_strdup_printf("SQL error: %s\n", ErrMsg
);
84 show_err_msg(GTK_MESSAGE_ERROR
, err
);
89 rc
= sqlite3_exec(db
, lens
, 0, 0, &ErrMsg
);
90 if (rc
!= SQLITE_OK
) {
91 gchar
* err
= g_strdup_printf("SQL error: %s\n", ErrMsg
);
93 show_err_msg(GTK_MESSAGE_ERROR
, err
);
102 gint
add_lens(const Row
* row
) {
107 rc
= sqlite3_prepare_v2(db
, "insert into lens (sn, maker, description, notes) values (?1, ?2, ?3, ?4)", -1, &stmt
, 0);
108 if (rc
!= SQLITE_OK
) {
109 gchar
* err
= g_strdup_printf("Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db
));
110 fprintf(stderr
, err
);
111 show_err_msg(GTK_MESSAGE_ERROR
, err
);
113 sqlite3_finalize(stmt
);
117 rc
= sqlite3_bind_text(stmt
, 1, row
->sn
, -1, NULL
);
119 gchar
* err
= g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
120 fprintf(stderr
, err
);
121 show_err_msg(GTK_MESSAGE_ERROR
, err
);
123 sqlite3_finalize(stmt
);
127 rc
= sqlite3_bind_int(stmt
, 2, row
->maker
);
129 gchar
* err
= g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
130 fprintf(stderr
, err
);
131 show_err_msg(GTK_MESSAGE_ERROR
, err
);
133 sqlite3_finalize(stmt
);
137 rc
= sqlite3_bind_text(stmt
, 3, row
->data
, -1, NULL
);
139 gchar
* err
= g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
140 fprintf(stderr
, err
);
141 show_err_msg(GTK_MESSAGE_ERROR
, err
);
143 sqlite3_finalize(stmt
);
147 rc
= sqlite3_bind_text(stmt
, 4, row
->notes
, -1, NULL
);
149 gchar
* err
= g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
150 fprintf(stderr
, err
);
151 show_err_msg(GTK_MESSAGE_ERROR
, err
);
153 sqlite3_finalize(stmt
);
157 rc
= sqlite3_step(stmt
);
158 if (rc
!= SQLITE_DONE
) {
159 gchar
* err
= g_strdup_printf("Step failure: %s\n", sqlite3_errmsg(db
));
160 fprintf(stderr
, err
);
161 show_err_msg(GTK_MESSAGE_ERROR
, err
);
167 sqlite3_finalize(stmt
);
172 gint
update_lens(const Row
* row
) {
177 rc
= sqlite3_prepare_v2(db
, "update lens set sn = ?1, description = ?2, notes = ?3 where id = ?4", -1, &stmt
, 0);
178 if (rc
!= SQLITE_OK
) {
179 gchar
* err
= g_strdup_printf("Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db
));
180 fprintf(stderr
, err
);
181 show_err_msg(GTK_MESSAGE_ERROR
, err
);
183 sqlite3_finalize(stmt
);
187 rc
= sqlite3_bind_text(stmt
, 1, row
->sn
, -1, NULL
);
189 gchar
* err
= g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
190 fprintf(stderr
, err
);
191 show_err_msg(GTK_MESSAGE_ERROR
, err
);
193 sqlite3_finalize(stmt
);
197 rc
= sqlite3_bind_text(stmt
, 2, row
->data
, -1, NULL
);
199 gchar
* err
= g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
200 fprintf(stderr
, err
);
201 show_err_msg(GTK_MESSAGE_ERROR
, err
);
203 sqlite3_finalize(stmt
);
207 rc
= sqlite3_bind_text(stmt
, 3, row
->notes
, -1, NULL
);
209 gchar
* err
= g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
210 fprintf(stderr
, err
);
211 show_err_msg(GTK_MESSAGE_ERROR
, err
);
213 sqlite3_finalize(stmt
);
217 rc
= sqlite3_bind_int64(stmt
, 4, row
->id
);
219 gchar
* err
= g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
220 fprintf(stderr
, err
);
221 show_err_msg(GTK_MESSAGE_ERROR
, err
);
223 sqlite3_finalize(stmt
);
227 rc
= sqlite3_step(stmt
);
228 if (rc
!= SQLITE_DONE
) {
229 gchar
* err
= g_strdup_printf("Step failure: %s\n", sqlite3_errmsg(db
));
230 fprintf(stderr
, err
);
231 show_err_msg(GTK_MESSAGE_ERROR
, err
);
237 sqlite3_finalize(stmt
);
242 gint
add_maker(const gchar
* maker
, gchar
** error
) {
247 rc
= sqlite3_prepare_v2(db
, "insert into lensmaker (maker) values (?1)", -1, &stmt
, 0);
248 if (rc
!= SQLITE_OK
) {
249 gchar
* err
= g_strdup_printf("Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db
));
250 fprintf(stderr
, err
);
251 show_err_msg(GTK_MESSAGE_ERROR
, err
);
254 *error
= g_strdup(sqlite3_errmsg(db
));
255 sqlite3_finalize(stmt
);
259 rc
= sqlite3_bind_text(stmt
, 1, maker
, -1, NULL
);
261 gchar
* err
= g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
262 fprintf(stderr
, err
);
263 show_err_msg(GTK_MESSAGE_ERROR
, err
);
266 *error
= g_strdup(sqlite3_errmsg(db
));
267 sqlite3_finalize(stmt
);
271 rc
= sqlite3_step(stmt
);
272 if (rc
!= SQLITE_OK
) {
273 gchar
* err
= g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
274 fprintf(stderr
, err
);
275 show_err_msg(GTK_MESSAGE_ERROR
, err
);
278 *error
= g_strdup(sqlite3_errmsg(db
));
279 sqlite3_finalize(stmt
);
284 sqlite3_finalize(stmt
);
289 Maker
* get_maker(gint id
) {
294 rc
= sqlite3_prepare_v2(db
, "select maker, page from lensmaker where id = ?1", -1, &stmt
, 0);
295 if (rc
!= SQLITE_OK
) {
296 gchar
* err
= g_strdup_printf("Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db
));
297 fprintf(stderr
, err
);
298 show_err_msg(GTK_MESSAGE_ERROR
, err
);
300 sqlite3_finalize(stmt
);
304 rc
= sqlite3_bind_int64(stmt
, 1, id
);
306 gchar
* err
= g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
307 fprintf(stderr
, err
);
308 show_err_msg(GTK_MESSAGE_ERROR
, err
);
310 sqlite3_finalize(stmt
);
314 if (sqlite3_step(stmt
) == SQLITE_ROW
) {
315 maker
= g_new0(Maker
, 1);
317 maker
->maker_name
= g_strdup((const gchar
*)sqlite3_column_text(stmt
, 0));
318 maker
->page
= sqlite3_column_int(stmt
, 1);
321 sqlite3_finalize(stmt
);
326 void update_maker(const Maker
* maker
) {
330 rc
= sqlite3_prepare_v2(db
, "update lensmaker set page = ?1 where id = ?2", -1, &stmt
, 0);
331 if (rc
!= SQLITE_OK
) {
332 gchar
* err
= g_strdup_printf("Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db
));
333 fprintf(stderr
, err
);
334 show_err_msg(GTK_MESSAGE_ERROR
, err
);
336 sqlite3_finalize(stmt
);
340 rc
= sqlite3_bind_int(stmt
, 1, maker
->page
);
342 gchar
* err
= g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
343 fprintf(stderr
, err
);
344 show_err_msg(GTK_MESSAGE_ERROR
, err
);
346 sqlite3_finalize(stmt
);
350 rc
= sqlite3_bind_int64(stmt
, 2, maker
->id
);
352 gchar
* err
= g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
353 fprintf(stderr
, err
);
354 show_err_msg(GTK_MESSAGE_ERROR
, err
);
356 sqlite3_finalize(stmt
);
360 //fprintf(stderr, "%s\n", sqlite3_expanded_sql(stmt));
361 rc
= sqlite3_step(stmt
);
362 if (rc
!= SQLITE_DONE
) {
363 gchar
* err
= g_strdup_printf("Step failure: %s\n", sqlite3_errmsg(db
));
364 fprintf(stderr
, err
);
365 show_err_msg(GTK_MESSAGE_ERROR
, err
);
367 sqlite3_finalize(stmt
);
370 sqlite3_finalize(stmt
);
373 void maker_free(Maker
* maker
) {
374 g_free(maker
->maker_name
);
378 gint
delete_maker(const gchar
* maker
) {
384 GSList
* get_makers() {
387 GSList
* makers
= NULL
;
389 rc
= sqlite3_prepare_v2(db
, "select maker from lensmaker order by id", -1, &stmt
, 0);
390 if (rc
!= SQLITE_OK
) {
391 gchar
* err
= g_strdup_printf("Failed to fetch data: %s\n", sqlite3_errmsg(db
));
392 fprintf(stderr
, err
);
393 show_err_msg(GTK_MESSAGE_ERROR
, err
);
399 while (sqlite3_step(stmt
) == SQLITE_ROW
) {
400 gchar
* maker
= g_strdup((const gchar
*)sqlite3_column_text(stmt
, 0));
401 makers
= g_slist_append(makers
, maker
);
404 sqlite3_finalize(stmt
);
409 gint
get_maker_id(const gchar
* maker
) {
414 rc
= sqlite3_prepare_v2(db
, "select id from lensmaker where maker = ?1", -1, &stmt
, 0);
415 if (rc
!= SQLITE_OK
) {
416 gchar
* err
= g_strdup_printf("Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db
));
417 fprintf(stderr
, err
);
418 show_err_msg(GTK_MESSAGE_ERROR
, err
);
420 sqlite3_finalize(stmt
);
424 rc
= sqlite3_bind_text(stmt
, 1, maker
, -1, NULL
);
426 gchar
* err
= g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
427 fprintf(stderr
, err
);
428 show_err_msg(GTK_MESSAGE_ERROR
, err
);
430 sqlite3_finalize(stmt
);
434 if (sqlite3_step(stmt
) == SQLITE_ROW
) {
435 id
= sqlite3_column_int64(stmt
, 0);
438 sqlite3_finalize(stmt
);
443 gint
delete_lens(const Row
* row
) {
449 GSList
* get_lens_by_maker(gint id
) {
452 GSList
* lenses
= NULL
;
454 rc
= sqlite3_prepare_v2(db
, "select sn, description, notes, id from lens where maker = ?1", -1, &stmt
, 0);
455 if (rc
!= SQLITE_OK
) {
456 gchar
* err
= g_strdup_printf("Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db
));
457 fprintf(stderr
, err
);
458 show_err_msg(GTK_MESSAGE_ERROR
, err
);
460 sqlite3_finalize(stmt
);
464 rc
= sqlite3_bind_int64(stmt
, 1, id
);
466 gchar
* err
= g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
467 fprintf(stderr
, err
);
468 show_err_msg(GTK_MESSAGE_ERROR
, err
);
470 sqlite3_finalize(stmt
);
474 while (sqlite3_step(stmt
) == SQLITE_ROW
) {
475 Row
* row
= row_new();
476 row
->sn
= g_strdup((const gchar
*)sqlite3_column_text(stmt
, 0));
477 row
->data
= g_strdup((const gchar
*)sqlite3_column_text(stmt
, 1));
478 row
->notes
= g_strdup((const gchar
*)sqlite3_column_text(stmt
, 2));
479 row
->id
= sqlite3_column_int64(stmt
, 3);
480 lenses
= g_slist_append(lenses
, row
);
483 sqlite3_finalize(stmt
);
488 GSList
* get_lens_by_search(const Row
* row
) {
491 GSList
* lenses
= NULL
;
496 if (row
->notes
&& row
->data
) {
497 sql
= g_strconcat(search
, " where ",
498 "upper(m.maker) like '%' || upper(?1) || '%' and upper(l.description) like '%' || upper(?2) || '%'",
500 bind1
= g_strdup(row
->notes
);
501 bind2
= g_strdup(row
->data
);
502 } else if (!row
->notes
&& !row
->data
) {
503 sql
= g_strconcat(search
, NULL
);
506 sql
= g_strconcat(search
, " where upper(m.maker) like '%' || upper(?1) || '%'", NULL
);
507 bind1
= g_strdup(row
->notes
);
509 sql
= g_strconcat(search
, " where upper(l.description) like '%' || upper(?1) || '%'", NULL
);
510 bind1
= g_strdup(row
->data
);
514 rc
= sqlite3_prepare_v2(db
, sql
, -1, &stmt
, 0);
515 if (rc
!= SQLITE_OK
) {
516 gchar
* err
= g_strdup_printf("Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db
));
517 fprintf(stderr
, err
);
518 show_err_msg(GTK_MESSAGE_ERROR
, err
);
520 sqlite3_finalize(stmt
);
525 rc
= sqlite3_bind_text(stmt
, 1, bind1
, -1, NULL
);
527 gchar
* err
= g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
528 fprintf(stderr
, err
);
529 show_err_msg(GTK_MESSAGE_ERROR
, err
);
531 sqlite3_finalize(stmt
);
537 rc
= sqlite3_bind_text(stmt
, 2, bind2
, -1, NULL
);
539 gchar
* err
= g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
540 fprintf(stderr
, err
);
541 show_err_msg(GTK_MESSAGE_ERROR
, err
);
543 sqlite3_finalize(stmt
);
548 while (sqlite3_step(stmt
) == SQLITE_ROW
) {
549 Row
* row
= row_new();
550 row
->id
= sqlite3_column_int64(stmt
, 0);
551 row
->sn
= g_strdup((const gchar
*)sqlite3_column_text(stmt
, 1));
552 row
->data
= g_strdup((const gchar
*)sqlite3_column_text(stmt
, 2));
553 row
->notes
= g_strdup((const gchar
*)sqlite3_column_text(stmt
, 3));
554 row
->maker
= sqlite3_column_int64(stmt
, 4);
555 lenses
= g_slist_append(lenses
, row
);
558 sqlite3_finalize(stmt
);
566 Row
* row
= g_new0(Row
, 1);
572 void row_free(gpointer data
, gpointer user_data
) {
573 Row
* row
= (Row
*) data
;
This page took 0.605358 seconds and 4 git commands to generate.