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