]> git.datanom.net - lensdb.git/blob - src/sqlite-storage.c
9a0fddf1f5b6409460a67756a4a90be6f3f00a27
[lensdb.git] / src / sqlite-storage.c
1 /*
2 * sqlite-storage.c
3 *
4 * Copyright 2024 Michael Rasmussen <mir@datanom.net>
5 *
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.
10 *
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.
15 *
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,
19 * MA 02110-1301, USA.
20 *
21 *
22 */
23
24 #include <stdio.h>
25 #include "sqlite-storage.h"
26 #include "gui.h"
27 #define DBNAME "lensdb.db"
28
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,"
36 "sn text not null,"
37 "maker integer not null,"
38 "description text not null,"
39 "notes text,"
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";
44
45 void close_storage() {
46 if (db != NULL)
47 sqlite3_close(db);
48 }
49
50 gint init_storage(const gchar* path) {
51 gboolean init_db = FALSE;
52 gint rc;
53 gchar* ErrMsg = NULL;
54
55 gchar* file = g_strconcat(path, "/", DBNAME, NULL);
56 if (!g_file_test(file, G_FILE_TEST_EXISTS)) {
57 init_db = TRUE;
58 }
59 rc = sqlite3_open(file, &db);
60 g_free(file);
61
62 if (rc) {
63 gchar* err = g_strdup_printf("Failed to open DB: %s\n", sqlite3_errmsg(db));
64 fprintf(stderr, err);
65 show_err_msg(GTK_MESSAGE_ERROR, err);
66 g_free(err);
67 return 1;
68 }
69
70 if (init_db) {
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);
74 fprintf(stderr, err);
75 show_err_msg(GTK_MESSAGE_ERROR, err);
76 g_free(err);
77 sqlite3_free(ErrMsg);
78 ErrMsg = NULL;
79 }
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);
83 fprintf(stderr, err);
84 show_err_msg(GTK_MESSAGE_ERROR, err);
85 g_free(err);
86 sqlite3_free(ErrMsg);
87 ErrMsg = NULL;
88 }
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);
92 fprintf(stderr, err);
93 show_err_msg(GTK_MESSAGE_ERROR, err);
94 g_free(err);
95 sqlite3_free(ErrMsg);
96 }
97 }
98
99 return 0;
100 }
101
102 gint add_lens(const Row* row) {
103 gint rc;
104 sqlite3_stmt* stmt;
105 gint status = 1;
106
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);
112 g_free(err);
113 sqlite3_finalize(stmt);
114 return status;
115 }
116
117 rc = sqlite3_bind_text(stmt, 1, row->sn, -1, NULL);
118 if (rc) {
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);
122 g_free(err);
123 sqlite3_finalize(stmt);
124 return status;
125 }
126
127 rc = sqlite3_bind_int(stmt, 2, row->maker);
128 if (rc) {
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);
132 g_free(err);
133 sqlite3_finalize(stmt);
134 return status;
135 }
136
137 rc = sqlite3_bind_text(stmt, 3, row->data, -1, NULL);
138 if (rc) {
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);
142 g_free(err);
143 sqlite3_finalize(stmt);
144 return status;
145 }
146
147 rc = sqlite3_bind_text(stmt, 4, row->notes, -1, NULL);
148 if (rc) {
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);
152 g_free(err);
153 sqlite3_finalize(stmt);
154 return status;
155 }
156
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);
162 g_free(err);
163 } else {
164 status = 0;
165 }
166
167 sqlite3_finalize(stmt);
168
169 return status;
170 }
171
172 gint update_lens(const Row* row) {
173 gint rc;
174 sqlite3_stmt* stmt;
175 gint status = 1;
176
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);
182 g_free(err);
183 sqlite3_finalize(stmt);
184 return status;
185 }
186
187 rc = sqlite3_bind_text(stmt, 1, row->sn, -1, NULL);
188 if (rc) {
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);
192 g_free(err);
193 sqlite3_finalize(stmt);
194 return status;
195 }
196
197 rc = sqlite3_bind_text(stmt, 2, row->data, -1, NULL);
198 if (rc) {
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);
202 g_free(err);
203 sqlite3_finalize(stmt);
204 return status;
205 }
206
207 rc = sqlite3_bind_text(stmt, 3, row->notes, -1, NULL);
208 if (rc) {
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);
212 g_free(err);
213 sqlite3_finalize(stmt);
214 return status;
215 }
216
217 rc = sqlite3_bind_int64(stmt, 4, row->id);
218 if (rc) {
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);
222 g_free(err);
223 sqlite3_finalize(stmt);
224 return status;
225 }
226
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);
232 g_free(err);
233 } else {
234 status = 0;
235 }
236
237 sqlite3_finalize(stmt);
238
239 return status;
240 }
241
242 gint add_maker(const gchar* maker, gchar** error) {
243 gint rc;
244 sqlite3_stmt* stmt;
245 gint status = 1;
246
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);
252 g_free(err);
253 if (error)
254 *error = g_strdup(sqlite3_errmsg(db));
255 sqlite3_finalize(stmt);
256 return status;
257 }
258
259 rc = sqlite3_bind_text(stmt, 1, maker, -1, NULL);
260 if (rc) {
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);
264 g_free(err);
265 if (error)
266 *error = g_strdup(sqlite3_errmsg(db));
267 sqlite3_finalize(stmt);
268 return status;
269 }
270
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);
276 g_free(err);
277 if (error)
278 *error = g_strdup(sqlite3_errmsg(db));
279 sqlite3_finalize(stmt);
280 return status;
281 }
282
283 status = 0;
284 sqlite3_finalize(stmt);
285
286 return status;
287 }
288
289 Maker* get_maker(gint id) {
290 gint rc;
291 sqlite3_stmt* stmt;
292 Maker* maker = NULL;
293
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);
299 g_free(err);
300 sqlite3_finalize(stmt);
301 return NULL;
302 }
303
304 rc = sqlite3_bind_int64(stmt, 1, id);
305 if (rc) {
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);
309 g_free(err);
310 sqlite3_finalize(stmt);
311 return NULL;
312 }
313
314 if (sqlite3_step(stmt) == SQLITE_ROW) {
315 maker = g_new0(Maker, 1);
316 maker->id = id;
317 maker->maker_name = g_strdup((const gchar*)sqlite3_column_text(stmt, 0));
318 maker->page = sqlite3_column_int(stmt, 1);
319 }
320
321 sqlite3_finalize(stmt);
322
323 return maker;
324 }
325
326 void update_maker(const Maker* maker) {
327 gint rc;
328 sqlite3_stmt* stmt;
329
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);
335 g_free(err);
336 sqlite3_finalize(stmt);
337 return;
338 }
339
340 rc = sqlite3_bind_int(stmt, 1, maker->page);
341 if (rc) {
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);
345 g_free(err);
346 sqlite3_finalize(stmt);
347 return;
348 }
349
350 rc = sqlite3_bind_int64(stmt, 2, maker->id);
351 if (rc) {
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);
355 g_free(err);
356 sqlite3_finalize(stmt);
357 return;
358 }
359
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);
366 g_free(err);
367 sqlite3_finalize(stmt);
368 }
369
370 sqlite3_finalize(stmt);
371 }
372
373 void maker_free(Maker* maker) {
374 g_free(maker->maker_name);
375 g_free(maker);
376 }
377
378 gint delete_maker(const gchar* maker) {
379 gint status = 1;
380
381 return status;
382 }
383
384 GSList* get_makers() {
385 gint rc;
386 sqlite3_stmt* stmt;
387 GSList* makers = NULL;
388
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);
394 g_free(err);
395
396 return NULL;
397 }
398
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);
402 }
403
404 sqlite3_finalize(stmt);
405
406 return makers;
407 }
408
409 gint get_maker_id(const gchar* maker) {
410 gint rc;
411 sqlite3_stmt* stmt;
412 gint id = 0;
413
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);
419 g_free(err);
420 sqlite3_finalize(stmt);
421 return -1;
422 }
423
424 rc = sqlite3_bind_text(stmt, 1, maker, -1, NULL);
425 if (rc) {
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);
429 g_free(err);
430 sqlite3_finalize(stmt);
431 return -1;
432 }
433
434 if (sqlite3_step(stmt) == SQLITE_ROW) {
435 id = sqlite3_column_int64(stmt, 0);
436 }
437
438 sqlite3_finalize(stmt);
439
440 return id;
441 }
442
443 gint delete_lens(const Row* row) {
444 gint status = 1;
445
446 return status;
447 }
448
449 GSList* get_lens_by_maker(gint id) {
450 gint rc;
451 sqlite3_stmt* stmt;
452 GSList* lenses = NULL;
453
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);
459 g_free(err);
460 sqlite3_finalize(stmt);
461 return NULL;
462 }
463
464 rc = sqlite3_bind_int64(stmt, 1, id);
465 if (rc) {
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);
469 g_free(err);
470 sqlite3_finalize(stmt);
471 return NULL;
472 }
473
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);
481 }
482
483 sqlite3_finalize(stmt);
484
485 return lenses;
486 }
487
488 GSList* get_lens_by_search(const Row* row) {
489 gint rc;
490 sqlite3_stmt* stmt;
491 GSList* lenses = NULL;
492 gchar* bind1 = NULL;
493 gchar* bind2 = NULL;
494 gchar* sql = NULL;
495
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) || '%'",
499 NULL);
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);
504 } else {
505 if (row->notes) {
506 sql = g_strconcat(search, " where upper(m.maker) like '%' || upper(?1) || '%'", NULL);
507 bind1 = g_strdup(row->notes);
508 } else {
509 sql = g_strconcat(search, " where upper(l.description) like '%' || upper(?1) || '%'", NULL);
510 bind1 = g_strdup(row->data);
511 }
512 }
513
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);
519 g_free(err);
520 sqlite3_finalize(stmt);
521 return NULL;
522 }
523
524 if (bind1) {
525 rc = sqlite3_bind_text(stmt, 1, bind1, -1, NULL);
526 if (rc) {
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);
530 g_free(err);
531 sqlite3_finalize(stmt);
532 return NULL;
533 }
534 }
535
536 if (bind2) {
537 rc = sqlite3_bind_text(stmt, 2, bind2, -1, NULL);
538 if (rc) {
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);
542 g_free(err);
543 sqlite3_finalize(stmt);
544 return NULL;
545 }
546 }
547
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);
556 }
557
558 sqlite3_finalize(stmt);
559 g_free(bind1);
560 g_free(bind2);
561
562 return lenses;
563 }
564
565 Row* row_new() {
566 Row* row = g_new0(Row, 1);
567 row->id = 0;
568
569 return row;
570 }
571
572 void row_free(gpointer data, gpointer user_data) {
573 Row* row = (Row *) data;
574 g_free(row->sn);
575 g_free(row->data);
576 g_free(row->notes);
577 g_free(row);
578 }
579
This page took 0.605358 seconds and 4 git commands to generate.