]>
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"
26 #define DBNAME "lensdb.db"
28 static sqlite3
* db
= NULL
;
29 static gchar
* lensmaker
= "create table lensmaker("
30 "id integer primary key not null AUTOINCREMENT,"
31 "maker text not null,"
32 "page int DEFAULT -1);";
33 static gchar
* lens
= "create table lens("
34 "id integer primary key not null AUTOINCREMENT,"
36 "maker integer not null,"
37 "description text not null,"
39 "constraint fk_maker foreign key (maker) references lensmaker (id)"
40 " on delete cascade);";
41 static gchar
* search
= "SELECT l.id, l.sn, l.description, l.notes, l.maker"
42 " FROM lens l JOIN lensmaker m on l.maker = m.id";
44 void close_storage() {
49 gint
init_storage(const gchar
* path
) {
50 gboolean init_db
= FALSE
;
54 gchar
* file
= g_strconcat(path
, "/", DBNAME
, NULL
);
55 if (!g_file_test(file
, G_FILE_TEST_EXISTS
)) {
58 rc
= sqlite3_open(file
, &db
);
62 fprintf(stderr
, "Failed to open DB: %s\n", sqlite3_errmsg(db
));
67 rc
= sqlite3_exec(db
, "PRAGMA foreign_keys = ON", 0, 0, &ErrMsg
);
68 if (rc
!= SQLITE_OK
) {
69 fprintf(stderr
, "SQL error: %s\n", ErrMsg
);
73 rc
= sqlite3_exec(db
, lensmaker
, 0, 0, &ErrMsg
);
74 if (rc
!= SQLITE_OK
) {
75 fprintf(stderr
, "SQL error: %s\n", ErrMsg
);
79 rc
= sqlite3_exec(db
, lens
, 0, 0, &ErrMsg
);
80 if (rc
!= SQLITE_OK
) {
81 fprintf(stderr
, "SQL error: %s\n", ErrMsg
);
89 gint
add_lens(const Row
* row
) {
94 rc
= sqlite3_prepare_v2(db
, "inserte lens (sn, description, notes) values (?1, ?2, ?3)", -1, &stmt
, 0);
95 if (rc
!= SQLITE_OK
) {
96 fprintf(stderr
, "Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db
));
97 sqlite3_finalize(stmt
);
101 rc
= sqlite3_bind_text(stmt
, 1, row
->sn
, -1, NULL
);
103 fprintf(stderr
, "Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
104 sqlite3_finalize(stmt
);
108 rc
= sqlite3_bind_text(stmt
, 2, row
->data
, -1, NULL
);
110 fprintf(stderr
, "Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
111 sqlite3_finalize(stmt
);
115 rc
= sqlite3_bind_text(stmt
, 3, row
->notes
, -1, NULL
);
117 fprintf(stderr
, "Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
118 sqlite3_finalize(stmt
);
122 if (sqlite3_step(stmt
) != SQLITE_DONE
) {
123 fprintf(stderr
, "Step failure: %s\n", sqlite3_errmsg(db
));
128 sqlite3_finalize(stmt
);
133 gint
update_lens(const Row
* row
) {
138 rc
= sqlite3_prepare_v2(db
, "update lens set sn = ?1, description = ?2, notes = ?3 where id = ?4", -1, &stmt
, 0);
139 if (rc
!= SQLITE_OK
) {
140 fprintf(stderr
, "Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db
));
141 sqlite3_finalize(stmt
);
145 rc
= sqlite3_bind_text(stmt
, 1, row
->sn
, -1, NULL
);
147 fprintf(stderr
, "Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
148 sqlite3_finalize(stmt
);
152 rc
= sqlite3_bind_text(stmt
, 2, row
->data
, -1, NULL
);
154 fprintf(stderr
, "Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
155 sqlite3_finalize(stmt
);
159 rc
= sqlite3_bind_text(stmt
, 3, row
->notes
, -1, NULL
);
161 fprintf(stderr
, "Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
162 sqlite3_finalize(stmt
);
166 rc
= sqlite3_bind_int64(stmt
, 4, row
->id
);
168 fprintf(stderr
, "Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
169 sqlite3_finalize(stmt
);
173 if (sqlite3_step(stmt
) != SQLITE_DONE
) {
174 fprintf(stderr
, "Step failure: %s\n", sqlite3_errmsg(db
));
179 sqlite3_finalize(stmt
);
184 gint
add_maker(const gchar
* maker
) {
190 Maker
* get_maker(gint id
) {
195 rc
= sqlite3_prepare_v2(db
, "select maker, page from lensmaker where id = ?1", -1, &stmt
, 0);
196 if (rc
!= SQLITE_OK
) {
197 fprintf(stderr
, "Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db
));
198 sqlite3_finalize(stmt
);
202 rc
= sqlite3_bind_int64(stmt
, 1, id
);
204 fprintf(stderr
, "Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
205 sqlite3_finalize(stmt
);
209 if (sqlite3_step(stmt
) == SQLITE_ROW
) {
210 maker
= g_new0(Maker
, 1);
212 maker
->maker_name
= g_strdup((const gchar
*)sqlite3_column_text(stmt
, 0));
213 maker
->page
= sqlite3_column_int(stmt
, 1);
216 sqlite3_finalize(stmt
);
221 void update_maker(const Maker
* maker
) {
225 rc
= sqlite3_prepare_v2(db
, "update lensmaker set page = ?1 where id = ?2", -1, &stmt
, 0);
226 if (rc
!= SQLITE_OK
) {
227 fprintf(stderr
, "Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db
));
228 sqlite3_finalize(stmt
);
232 rc
= sqlite3_bind_int64(stmt
, 1, maker
->page
);
234 fprintf(stderr
, "Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
235 sqlite3_finalize(stmt
);
239 rc
= sqlite3_bind_int64(stmt
, 1, maker
->id
);
241 fprintf(stderr
, "Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
242 sqlite3_finalize(stmt
);
246 if (sqlite3_step(stmt
) != SQLITE_DONE
) {
247 fprintf(stderr
, "Step failure: %s\n", sqlite3_errmsg(db
));
250 sqlite3_finalize(stmt
);
253 void maker_free(Maker
* maker
) {
254 g_free(maker
->maker_name
);
258 gint
delete_maker(const gchar
* maker
) {
264 GSList
* get_makers() {
267 GSList
* makers
= NULL
;
269 rc
= sqlite3_prepare_v2(db
, "select maker from lensmaker", -1, &stmt
, 0);
270 if (rc
!= SQLITE_OK
) {
271 fprintf(stderr
, "Failed to fetch data: %s\n", sqlite3_errmsg(db
));
276 while (sqlite3_step(stmt
) == SQLITE_ROW
) {
277 gchar
* maker
= g_strdup((const gchar
*)sqlite3_column_text(stmt
, 0));
278 makers
= g_slist_append(makers
, maker
);
281 sqlite3_finalize(stmt
);
286 gint
get_maker_id(const gchar
* maker
) {
291 rc
= sqlite3_prepare_v2(db
, "select id from lensmaker where maker = ?1", -1, &stmt
, 0);
292 if (rc
!= SQLITE_OK
) {
293 fprintf(stderr
, "Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db
));
294 sqlite3_finalize(stmt
);
298 rc
= sqlite3_bind_text(stmt
, 1, maker
, -1, NULL
);
300 fprintf(stderr
, "Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
301 sqlite3_finalize(stmt
);
305 if (sqlite3_step(stmt
) == SQLITE_ROW
) {
306 id
= sqlite3_column_int64(stmt
, 0);
309 sqlite3_finalize(stmt
);
314 gint
delete_lens(const Row
* row
) {
320 GSList
* get_lens_by_maker(gint id
) {
323 GSList
* lenses
= NULL
;
325 rc
= sqlite3_prepare_v2(db
, "select sn, description, notes, id from lens where maker = ?1", -1, &stmt
, 0);
326 if (rc
!= SQLITE_OK
) {
327 fprintf(stderr
, "Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db
));
328 sqlite3_finalize(stmt
);
332 rc
= sqlite3_bind_int64(stmt
, 1, id
);
334 fprintf(stderr
, "Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
335 sqlite3_finalize(stmt
);
339 while (sqlite3_step(stmt
) == SQLITE_ROW
) {
340 Row
* row
= row_new();
341 row
->sn
= g_strdup((const gchar
*)sqlite3_column_text(stmt
, 0));
342 row
->data
= g_strdup((const gchar
*)sqlite3_column_text(stmt
, 1));
343 row
->notes
= g_strdup((const gchar
*)sqlite3_column_text(stmt
, 2));
344 row
->id
= sqlite3_column_int64(stmt
, 3);
345 lenses
= g_slist_append(lenses
, row
);
348 sqlite3_finalize(stmt
);
353 GSList
* get_lens_by_search(const Row
* row
) {
356 GSList
* lenses
= NULL
;
361 if (row
->notes
&& row
->data
) {
362 sql
= g_strconcat(search
, " where ",
363 "upper(m.maker) like '%' || upper(?1) || '%' and upper(l.description) like '%' || upper(?2) || '%'",
365 bind1
= g_strdup(row
->notes
);
366 bind2
= g_strdup(row
->data
);
367 } else if (!row
->notes
&& !row
->data
) {
368 sql
= g_strconcat(search
, NULL
);
371 sql
= g_strconcat(search
, " where upper(m.maker) like '%' || upper(?1) || '%'", NULL
);
372 bind1
= g_strdup(row
->notes
);
374 sql
= g_strconcat(search
, " where upper(l.description) like '%' || upper(?1) || '%'", NULL
);
375 bind1
= g_strdup(row
->data
);
379 rc
= sqlite3_prepare_v2(db
, sql
, -1, &stmt
, 0);
380 if (rc
!= SQLITE_OK
) {
381 fprintf(stderr
, "Couldn't prepare sql statement: %s\n", sqlite3_errmsg(db
));
382 sqlite3_finalize(stmt
);
387 rc
= sqlite3_bind_text(stmt
, 1, bind1
, -1, NULL
);
389 fprintf(stderr
, "Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
390 sqlite3_finalize(stmt
);
396 rc
= sqlite3_bind_text(stmt
, 2, bind2
, -1, NULL
);
398 fprintf(stderr
, "Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db
));
399 sqlite3_finalize(stmt
);
404 while (sqlite3_step(stmt
) == SQLITE_ROW
) {
405 Row
* row
= row_new();
406 row
->id
= sqlite3_column_int64(stmt
, 0);
407 row
->sn
= g_strdup((const gchar
*)sqlite3_column_text(stmt
, 1));
408 row
->data
= g_strdup((const gchar
*)sqlite3_column_text(stmt
, 2));
409 row
->notes
= g_strdup((const gchar
*)sqlite3_column_text(stmt
, 3));
410 row
->maker
= sqlite3_column_int64(stmt
, 4);
411 lenses
= g_slist_append(lenses
, row
);
414 sqlite3_finalize(stmt
);
422 Row
* row
= g_new0(Row
, 1);
428 void row_free(gpointer data
, gpointer user_data
) {
429 Row
* row
= (Row
*) data
;
This page took 0.712608 seconds and 6 git commands to generate.