]> git.datanom.net - lensdb.git/blob - src/sqlite-storage.c
Add missing tooltip
[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 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,"
37 "sn text not null,"
38 "maker integer not null,"
39 "description text not null,"
40 "notes text,"
41 "date_added datetime DEFAULT current_timestamp,"
42 "PRIMARY KEY(id AUTOINCREMENT),"
43 "constraint fk_maker foreign key (maker) references lensmaker (id)"
44 " on delete cascade);";
45 static gchar* search = "SELECT l.id, l.sn, l.description, l.notes, l.maker"
46 " FROM lens l JOIN lensmaker m on l.maker = m.id";
47 static gchar* all = "SELECT m.maker,l.sn,l.description,l.notes,l.date_added"
48 " FROM lensmaker m JOIN lens l on m.id = l.maker";
49
50 void close_storage() {
51 if (db != NULL)
52 sqlite3_close(db);
53 }
54
55 gint init_storage(const gchar* path) {
56 gboolean init_db = FALSE;
57 gint rc;
58 gchar* ErrMsg = NULL;
59
60 gchar* file = g_strconcat(path, "/", DBNAME, NULL);
61 if (!g_file_test(file, G_FILE_TEST_EXISTS)) {
62 init_db = TRUE;
63 }
64 rc = sqlite3_open(file, &db);
65 g_free(file);
66
67 if (rc) {
68 gchar* err = g_strdup_printf("Failed to open DB: %s\n", sqlite3_errmsg(db));
69 fprintf(stderr, err);
70 show_message(GTK_MESSAGE_ERROR, err);
71 g_free(err);
72 return 1;
73 }
74
75 if (init_db) {
76 rc = sqlite3_exec(db, "PRAGMA foreign_keys = ON", 0, 0, &ErrMsg);
77 if (rc != SQLITE_OK) {
78 gchar* err = g_strdup_printf("SQL error: %s\n", ErrMsg);
79 fprintf(stderr, err);
80 show_message(GTK_MESSAGE_ERROR, err);
81 g_free(err);
82 sqlite3_free(ErrMsg);
83 ErrMsg = NULL;
84 }
85 rc = sqlite3_exec(db, lensmaker, 0, 0, &ErrMsg);
86 if (rc != SQLITE_OK) {
87 gchar* err = g_strdup_printf("SQL error: %s\n", ErrMsg);
88 fprintf(stderr, err);
89 show_message(GTK_MESSAGE_ERROR, err);
90 g_free(err);
91 sqlite3_free(ErrMsg);
92 ErrMsg = NULL;
93 }
94 rc = sqlite3_exec(db, lens, 0, 0, &ErrMsg);
95 if (rc != SQLITE_OK) {
96 gchar* err = g_strdup_printf("SQL error: %s\n", ErrMsg);
97 fprintf(stderr, err);
98 show_message(GTK_MESSAGE_ERROR, err);
99 g_free(err);
100 sqlite3_free(ErrMsg);
101 }
102 }
103
104 return 0;
105 }
106
107 gint add_lens(Row* row) {
108 gint rc;
109 sqlite3_stmt* stmt;
110 gint status = 1;
111
112 rc = sqlite3_prepare_v2(db, "insert into lens (sn, maker, description, notes) values (?1, ?2, ?3, ?4) returning id", -1, &stmt, 0);
113 if (rc != SQLITE_OK) {
114 gchar* err = g_strdup_printf("Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db));
115 fprintf(stderr, err);
116 show_message(GTK_MESSAGE_ERROR, err);
117 g_free(err);
118 sqlite3_finalize(stmt);
119 return status;
120 }
121
122 rc = sqlite3_bind_text(stmt, 1, row->sn, -1, NULL);
123 if (rc) {
124 gchar* err = g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db));
125 fprintf(stderr, err);
126 show_message(GTK_MESSAGE_ERROR, err);
127 g_free(err);
128 sqlite3_finalize(stmt);
129 return status;
130 }
131
132 rc = sqlite3_bind_int(stmt, 2, row->maker);
133 if (rc) {
134 gchar* err = g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db));
135 fprintf(stderr, err);
136 show_message(GTK_MESSAGE_ERROR, err);
137 g_free(err);
138 sqlite3_finalize(stmt);
139 return status;
140 }
141
142 rc = sqlite3_bind_text(stmt, 3, row->data, -1, NULL);
143 if (rc) {
144 gchar* err = g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db));
145 fprintf(stderr, err);
146 show_message(GTK_MESSAGE_ERROR, err);
147 g_free(err);
148 sqlite3_finalize(stmt);
149 return status;
150 }
151
152 rc = sqlite3_bind_text(stmt, 4, row->notes, -1, NULL);
153 if (rc) {
154 gchar* err = g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db));
155 fprintf(stderr, err);
156 show_message(GTK_MESSAGE_ERROR, err);
157 g_free(err);
158 sqlite3_finalize(stmt);
159 return status;
160 }
161
162 rc = sqlite3_step(stmt);
163 if (rc != SQLITE_ROW) {
164 gchar* err = g_strdup_printf("Step failure: %s\n", sqlite3_errmsg(db));
165 fprintf(stderr, err);
166 show_message(GTK_MESSAGE_ERROR, err);
167 g_free(err);
168 } else {
169 row->id = sqlite3_column_int64(stmt, 0);
170 status = 0;
171 }
172
173 sqlite3_finalize(stmt);
174
175 return status;
176 }
177
178 gint update_lens(const Row* row) {
179 gint rc;
180 sqlite3_stmt* stmt;
181 gint status = 1;
182
183 rc = sqlite3_prepare_v2(db, "update lens set sn = ?1, description = ?2, notes = ?3 where id = ?4", -1, &stmt, 0);
184 if (rc != SQLITE_OK) {
185 gchar* err = g_strdup_printf("Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db));
186 fprintf(stderr, err);
187 show_message(GTK_MESSAGE_ERROR, err);
188 g_free(err);
189 sqlite3_finalize(stmt);
190 return status;
191 }
192
193 rc = sqlite3_bind_text(stmt, 1, row->sn, -1, NULL);
194 if (rc) {
195 gchar* err = g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db));
196 fprintf(stderr, err);
197 show_message(GTK_MESSAGE_ERROR, err);
198 g_free(err);
199 sqlite3_finalize(stmt);
200 return status;
201 }
202
203 rc = sqlite3_bind_text(stmt, 2, row->data, -1, NULL);
204 if (rc) {
205 gchar* err = g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db));
206 fprintf(stderr, err);
207 show_message(GTK_MESSAGE_ERROR, err);
208 g_free(err);
209 sqlite3_finalize(stmt);
210 return status;
211 }
212
213 rc = sqlite3_bind_text(stmt, 3, row->notes, -1, NULL);
214 if (rc) {
215 gchar* err = g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db));
216 fprintf(stderr, err);
217 show_message(GTK_MESSAGE_ERROR, err);
218 g_free(err);
219 sqlite3_finalize(stmt);
220 return status;
221 }
222
223 rc = sqlite3_bind_int64(stmt, 4, row->id);
224 if (rc) {
225 gchar* err = g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db));
226 fprintf(stderr, err);
227 show_message(GTK_MESSAGE_ERROR, err);
228 g_free(err);
229 sqlite3_finalize(stmt);
230 return status;
231 }
232
233 rc = sqlite3_step(stmt);
234 if (rc != SQLITE_DONE) {
235 gchar* err = g_strdup_printf("Step failure: %s\n", sqlite3_errmsg(db));
236 fprintf(stderr, err);
237 show_message(GTK_MESSAGE_ERROR, err);
238 g_free(err);
239 } else {
240 status = 0;
241 }
242
243 sqlite3_finalize(stmt);
244
245 return status;
246 }
247
248 gint update_lens_maker(const Row* row) {
249 gint rc;
250 sqlite3_stmt* stmt;
251 gint status = 1;
252
253 rc = sqlite3_prepare_v2(db, "update lens set maker = ?1 where id = ?2", -1, &stmt, 0);
254 if (rc != SQLITE_OK) {
255 gchar* err = g_strdup_printf("Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db));
256 fprintf(stderr, err);
257 show_message(GTK_MESSAGE_ERROR, err);
258 g_free(err);
259 sqlite3_finalize(stmt);
260 return status;
261 }
262
263 rc = sqlite3_bind_int64(stmt, 1, row->maker);
264 if (rc) {
265 gchar* err = g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db));
266 fprintf(stderr, err);
267 show_message(GTK_MESSAGE_ERROR, err);
268 g_free(err);
269 sqlite3_finalize(stmt);
270 return status;
271 }
272
273 rc = sqlite3_bind_int64(stmt, 2, row->id);
274 if (rc) {
275 gchar* err = g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db));
276 fprintf(stderr, err);
277 show_message(GTK_MESSAGE_ERROR, err);
278 g_free(err);
279 sqlite3_finalize(stmt);
280 return status;
281 }
282
283 rc = sqlite3_step(stmt);
284 if (rc != SQLITE_DONE) {
285 gchar* err = g_strdup_printf("Step failure: %s\n", sqlite3_errmsg(db));
286 fprintf(stderr, err);
287 show_message(GTK_MESSAGE_ERROR, err);
288 g_free(err);
289 } else {
290 status = 0;
291 }
292
293 sqlite3_finalize(stmt);
294
295 return status;
296 }
297
298 gint delete_lens(const Row* row) {
299 gint rc;
300 sqlite3_stmt* stmt;
301 gint status = 1;
302
303 rc = sqlite3_prepare_v2(db, "delete from lens where id = ?1", -1, &stmt, 0);
304 if (rc != SQLITE_OK) {
305 gchar* err = g_strdup_printf("Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db));
306 fprintf(stderr, err);
307 show_message(GTK_MESSAGE_ERROR, err);
308 g_free(err);
309 sqlite3_finalize(stmt);
310 return status;
311 }
312
313 rc = sqlite3_bind_int64(stmt, 1, row->id);
314 if (rc) {
315 gchar* err = g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db));
316 fprintf(stderr, err);
317 show_message(GTK_MESSAGE_ERROR, err);
318 g_free(err);
319 sqlite3_finalize(stmt);
320 return status;
321 }
322
323 rc = sqlite3_step(stmt);
324 if (rc != SQLITE_DONE) {
325 gchar* err = g_strdup_printf("Step failure: %s\n", sqlite3_errmsg(db));
326 fprintf(stderr, err);
327 show_message(GTK_MESSAGE_ERROR, err);
328 g_free(err);
329 } else {
330 status = 0;
331 }
332
333 sqlite3_finalize(stmt);
334
335 return status;
336 }
337
338 gint add_maker(const gchar* maker, gchar** error) {
339 gint rc;
340 sqlite3_stmt* stmt;
341 gint id = -1;
342
343 rc = sqlite3_prepare_v2(db, "insert into lensmaker (maker) values (?1) returning id", -1, &stmt, 0);
344 if (rc != SQLITE_OK) {
345 gchar* err = g_strdup_printf("Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db));
346 fprintf(stderr, err);
347 show_message(GTK_MESSAGE_ERROR, err);
348 g_free(err);
349 if (error)
350 *error = g_strdup(sqlite3_errmsg(db));
351 sqlite3_finalize(stmt);
352 return id;
353 }
354
355 rc = sqlite3_bind_text(stmt, 1, maker, -1, NULL);
356 if (rc) {
357 gchar* err = g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db));
358 fprintf(stderr, err);
359 show_message(GTK_MESSAGE_ERROR, err);
360 g_free(err);
361 if (error)
362 *error = g_strdup(sqlite3_errmsg(db));
363 sqlite3_finalize(stmt);
364 return id;
365 }
366
367 rc = sqlite3_step(stmt);
368 if (rc != SQLITE_ROW) {
369 gchar* err = g_strdup_printf("Couldn't insert maker: %s\n", sqlite3_errmsg(db));
370 fprintf(stderr, err);
371 show_message(GTK_MESSAGE_ERROR, err);
372 g_free(err);
373 if (error)
374 *error = g_strdup(sqlite3_errmsg(db));
375 } else {
376 id = sqlite3_column_int64(stmt, 0);
377 }
378
379 sqlite3_finalize(stmt);
380
381 return id;
382 }
383
384 Maker* get_maker(gint id) {
385 gint rc;
386 sqlite3_stmt* stmt;
387 Maker* maker = NULL;
388
389 rc = sqlite3_prepare_v2(db, "select maker, page from lensmaker where id = ?1", -1, &stmt, 0);
390 if (rc != SQLITE_OK) {
391 gchar* err = g_strdup_printf("Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db));
392 fprintf(stderr, err);
393 show_message(GTK_MESSAGE_ERROR, err);
394 g_free(err);
395 sqlite3_finalize(stmt);
396 return NULL;
397 }
398
399 rc = sqlite3_bind_int64(stmt, 1, id);
400 if (rc) {
401 gchar* err = g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db));
402 fprintf(stderr, err);
403 show_message(GTK_MESSAGE_ERROR, err);
404 g_free(err);
405 sqlite3_finalize(stmt);
406 return NULL;
407 }
408
409 if (sqlite3_step(stmt) == SQLITE_ROW) {
410 maker = g_new0(Maker, 1);
411 maker->id = id;
412 maker->maker_name = g_strdup((const gchar*)sqlite3_column_text(stmt, 0));
413 maker->page = sqlite3_column_int(stmt, 1);
414 }
415
416 sqlite3_finalize(stmt);
417
418 return maker;
419 }
420
421 void update_maker(const Maker* maker) {
422 gint rc;
423 sqlite3_stmt* stmt;
424
425 rc = sqlite3_prepare_v2(db, "update lensmaker set page = ?1 where id = ?2", -1, &stmt, 0);
426 if (rc != SQLITE_OK) {
427 gchar* err = g_strdup_printf("Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db));
428 fprintf(stderr, err);
429 show_message(GTK_MESSAGE_ERROR, err);
430 g_free(err);
431 sqlite3_finalize(stmt);
432 return;
433 }
434
435 rc = sqlite3_bind_int(stmt, 1, maker->page);
436 if (rc) {
437 gchar* err = g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db));
438 fprintf(stderr, err);
439 show_message(GTK_MESSAGE_ERROR, err);
440 g_free(err);
441 sqlite3_finalize(stmt);
442 return;
443 }
444
445 rc = sqlite3_bind_int64(stmt, 2, maker->id);
446 if (rc) {
447 gchar* err = g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db));
448 fprintf(stderr, err);
449 show_message(GTK_MESSAGE_ERROR, err);
450 g_free(err);
451 sqlite3_finalize(stmt);
452 return;
453 }
454
455 //fprintf(stderr, "%s\n", sqlite3_expanded_sql(stmt));
456 rc = sqlite3_step(stmt);
457 if (rc != SQLITE_DONE) {
458 gchar* err = g_strdup_printf("Step failure: %s\n", sqlite3_errmsg(db));
459 fprintf(stderr, err);
460 show_message(GTK_MESSAGE_ERROR, err);
461 g_free(err);
462 sqlite3_finalize(stmt);
463 }
464
465 sqlite3_finalize(stmt);
466 }
467
468 void maker_free(Maker* maker) {
469 if (maker) {
470 g_free(maker->maker_name);
471 g_free(maker);
472 }
473 }
474
475 gint delete_maker(const gchar* maker, gchar** error) {
476 gint rc;
477 sqlite3_stmt* stmt;
478 gint status = 0;
479
480 gint id = get_maker_id(maker);
481 rc = sqlite3_prepare_v2(db, "delete from lensmaker where id = ?1", -1, &stmt, 0);
482 if (rc != SQLITE_OK) {
483 gchar* err = g_strdup_printf("Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db));
484 fprintf(stderr, err);
485 show_message(GTK_MESSAGE_ERROR, err);
486 g_free(err);
487 if (error)
488 *error = g_strdup(sqlite3_errmsg(db));
489 sqlite3_finalize(stmt);
490
491 return 1;
492 }
493
494 rc = sqlite3_bind_int(stmt, 1, id);
495 if (rc) {
496 gchar* err = g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db));
497 fprintf(stderr, err);
498 show_message(GTK_MESSAGE_ERROR, err);
499 g_free(err);
500 if (error)
501 *error = g_strdup(sqlite3_errmsg(db));
502 sqlite3_finalize(stmt);
503
504 return 1;
505 }
506
507 //fprintf(stderr, "%s\n", sqlite3_expanded_sql(stmt));
508 rc = sqlite3_step(stmt);
509 if (rc != SQLITE_DONE) {
510 gchar* err = g_strdup_printf("Step failure: %s\n", sqlite3_errmsg(db));
511 fprintf(stderr, err);
512 show_message(GTK_MESSAGE_ERROR, err);
513 g_free(err);
514 if (error)
515 *error = g_strdup(sqlite3_errmsg(db));
516 sqlite3_finalize(stmt);
517
518 status = 1;
519 }
520
521 sqlite3_finalize(stmt);
522
523 return status;
524 }
525
526 GSList* get_makers() {
527 gint rc;
528 sqlite3_stmt* stmt;
529 GSList* makers = NULL;
530
531 rc = sqlite3_prepare_v2(db, "select maker from lensmaker order by id", -1, &stmt, 0);
532 if (rc != SQLITE_OK) {
533 gchar* err = g_strdup_printf("Failed to fetch data: %s\n", sqlite3_errmsg(db));
534 fprintf(stderr, err);
535 show_message(GTK_MESSAGE_ERROR, err);
536 g_free(err);
537 sqlite3_finalize(stmt);
538 return NULL;
539 }
540
541 while (sqlite3_step(stmt) == SQLITE_ROW) {
542 gchar* maker = g_strdup((const gchar*)sqlite3_column_text(stmt, 0));
543 makers = g_slist_append(makers, maker);
544 }
545
546 sqlite3_finalize(stmt);
547
548 return makers;
549 }
550
551 gint get_maker_id(const gchar* maker) {
552 gint rc;
553 sqlite3_stmt* stmt;
554 gint id = -1;
555
556 rc = sqlite3_prepare_v2(db, "select id from lensmaker where maker = ?1", -1, &stmt, 0);
557 if (rc != SQLITE_OK) {
558 gchar* err = g_strdup_printf("Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db));
559 fprintf(stderr, err);
560 show_message(GTK_MESSAGE_ERROR, err);
561 g_free(err);
562 sqlite3_finalize(stmt);
563 return -1;
564 }
565
566 rc = sqlite3_bind_text(stmt, 1, maker, -1, NULL);
567 if (rc) {
568 gchar* err = g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db));
569 fprintf(stderr, err);
570 show_message(GTK_MESSAGE_ERROR, err);
571 g_free(err);
572 sqlite3_finalize(stmt);
573 return -1;
574 }
575
576 if (sqlite3_step(stmt) == SQLITE_ROW) {
577 id = sqlite3_column_int64(stmt, 0);
578 }
579
580 sqlite3_finalize(stmt);
581
582 return id;
583 }
584
585 GSList* get_lens_by_maker(gint id) {
586 gint rc;
587 sqlite3_stmt* stmt;
588 GSList* lenses = NULL;
589
590 rc = sqlite3_prepare_v2(db, "select sn, description, notes, id from lens where maker = ?1", -1, &stmt, 0);
591 if (rc != SQLITE_OK) {
592 gchar* err = g_strdup_printf("Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db));
593 fprintf(stderr, err);
594 show_message(GTK_MESSAGE_ERROR, err);
595 g_free(err);
596 sqlite3_finalize(stmt);
597 return NULL;
598 }
599
600 rc = sqlite3_bind_int64(stmt, 1, id);
601 if (rc) {
602 gchar* err = g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db));
603 fprintf(stderr, err);
604 show_message(GTK_MESSAGE_ERROR, err);
605 g_free(err);
606 sqlite3_finalize(stmt);
607 return NULL;
608 }
609
610 while (sqlite3_step(stmt) == SQLITE_ROW) {
611 Row* row = row_new();
612 row->sn = g_strdup((const gchar*)sqlite3_column_text(stmt, 0));
613 row->data = g_strdup((const gchar*)sqlite3_column_text(stmt, 1));
614 row->notes = g_strdup((const gchar*)sqlite3_column_text(stmt, 2));
615 row->id = sqlite3_column_int64(stmt, 3);
616 lenses = g_slist_append(lenses, row);
617 }
618
619 sqlite3_finalize(stmt);
620
621 return lenses;
622 }
623
624 GSList* get_lens_by_search(const Row* row) {
625 gint rc;
626 sqlite3_stmt* stmt;
627 GSList* lenses = NULL;
628 gchar* bind1 = NULL;
629 gchar* bind2 = NULL;
630 gchar* sql = NULL;
631
632 if (row->notes && row->data) {
633 sql = g_strconcat(search, " where ",
634 "upper(m.maker) like '%' || upper(?1) || '%' and upper(l.description) like '%' || upper(?2) || '%'",
635 NULL);
636 bind1 = g_strdup(row->notes);
637 bind2 = g_strdup(row->data);
638 } else if (!row->notes && !row->data) {
639 sql = g_strconcat(search, NULL);
640 } else {
641 if (row->notes) {
642 sql = g_strconcat(search, " where upper(m.maker) like '%' || upper(?1) || '%'", NULL);
643 bind1 = g_strdup(row->notes);
644 } else {
645 sql = g_strconcat(search, " where upper(l.description) like '%' || upper(?1) || '%'", NULL);
646 bind1 = g_strdup(row->data);
647 }
648 }
649
650 rc = sqlite3_prepare_v2(db, sql, -1, &stmt, 0);
651 if (rc != SQLITE_OK) {
652 gchar* err = g_strdup_printf("Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db));
653 fprintf(stderr, err);
654 show_message(GTK_MESSAGE_ERROR, err);
655 g_free(err);
656 sqlite3_finalize(stmt);
657 return NULL;
658 }
659
660 if (bind1) {
661 rc = sqlite3_bind_text(stmt, 1, bind1, -1, NULL);
662 if (rc) {
663 gchar* err = g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db));
664 fprintf(stderr, err);
665 show_message(GTK_MESSAGE_ERROR, err);
666 g_free(err);
667 sqlite3_finalize(stmt);
668 return NULL;
669 }
670 }
671
672 if (bind2) {
673 rc = sqlite3_bind_text(stmt, 2, bind2, -1, NULL);
674 if (rc) {
675 gchar* err = g_strdup_printf("Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db));
676 fprintf(stderr, err);
677 show_message(GTK_MESSAGE_ERROR, err);
678 g_free(err);
679 sqlite3_finalize(stmt);
680 return NULL;
681 }
682 }
683
684 while (sqlite3_step(stmt) == SQLITE_ROW) {
685 Row* row = row_new();
686 row->id = sqlite3_column_int64(stmt, 0);
687 row->sn = g_strdup((const gchar*)sqlite3_column_text(stmt, 1));
688 row->data = g_strdup((const gchar*)sqlite3_column_text(stmt, 2));
689 row->notes = g_strdup((const gchar*)sqlite3_column_text(stmt, 3));
690 row->maker = sqlite3_column_int64(stmt, 4);
691 lenses = g_slist_append(lenses, row);
692 }
693
694 sqlite3_finalize(stmt);
695 g_free(bind1);
696 g_free(bind2);
697
698 return lenses;
699 }
700
701 Row* row_new() {
702 Row* row = g_new0(Row, 1);
703 row->id = 0;
704
705 return row;
706 }
707
708 void row_free(gpointer data, gpointer user_data) {
709 Row* row = (Row *) data;
710 g_free(row->sn);
711 g_free(row->data);
712 g_free(row->notes);
713 g_free(row);
714 }
715
716 void dump_storage(CsvParser* parser) {
717 gint rc;
718 sqlite3_stmt* stmt;
719 CvsError* err = NULL;
720
721 if (parser == NULL) return;
722
723 rc = sqlite3_prepare_v2(db, all, -1, &stmt, 0);
724 if (rc != SQLITE_OK) {
725 gchar* err = g_strdup_printf("Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db));
726 fprintf(stderr, err);
727 show_message(GTK_MESSAGE_ERROR, err);
728 g_free(err);
729 sqlite3_finalize(stmt);
730 return;
731 }
732
733 Record* row = g_new0(Record, 1);
734 row->maker = g_strdup("maker");
735 row->sn = g_strdup("sn");
736 row->data = g_strdup("lens");
737 row->notes = g_strdup("notes");
738 row->date = g_strdup("date");
739 csv_parser_dump_row(parser, row, &err);
740 csv_record_free(row);
741 if (err) {
742 gchar* msg = g_strdup_printf("%zu: %s\n", err->line, err->message);
743 fprintf(stderr, msg);
744 show_message(GTK_MESSAGE_ERROR, msg);
745 g_free(msg);
746 csv_error_clear(&err);
747 sqlite3_finalize(stmt);
748 return;
749 }
750
751 while (sqlite3_step(stmt) == SQLITE_ROW) {
752 row = g_new0(Record, 1);
753 row->maker = g_strdup((const gchar*)sqlite3_column_text(stmt, 0));
754 row->sn = g_strdup((const gchar*)sqlite3_column_text(stmt, 1));
755 row->data = g_strdup((const gchar*)sqlite3_column_text(stmt, 2));
756 row->notes = g_strdup((const gchar*)sqlite3_column_text(stmt, 3));
757 row->date = g_strdup((const gchar*)sqlite3_column_text(stmt, 4));
758 csv_parser_dump_row(parser, row, &err);
759 csv_record_free(row);
760 if (err) {
761 gchar* msg = g_strdup_printf("%zu: %s\n", err->line, err->message);
762 fprintf(stderr, msg);
763 show_message(GTK_MESSAGE_ERROR, msg);
764 g_free(msg);
765 csv_error_clear(&err);
766 sqlite3_finalize(stmt);
767 return;
768 }
769 }
770
771 sqlite3_finalize(stmt);
772 }
773
774
This page took 1.481996 seconds and 6 git commands to generate.