]> git.datanom.net - lensdb.git/blob - src/sqlite-storage.c
almost complete
[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 #define DBNAME "lensdb.db"
27
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,"
35 "sn text not null,"
36 "maker integer not null,"
37 "description text not null,"
38 "notes text,"
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";
43
44 void close_storage() {
45 if (db != NULL)
46 sqlite3_close(db);
47 }
48
49 gint init_storage(const gchar* path) {
50 gboolean init_db = FALSE;
51 gint rc;
52 gchar* ErrMsg = NULL;
53
54 gchar* file = g_strconcat(path, "/", DBNAME, NULL);
55 if (!g_file_test(file, G_FILE_TEST_EXISTS)) {
56 init_db = TRUE;
57 }
58 rc = sqlite3_open(file, &db);
59 g_free(file);
60
61 if (rc) {
62 fprintf(stderr, "Failed to open DB: %s\n", sqlite3_errmsg(db));
63 return 1;
64 }
65
66 if (init_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);
70 sqlite3_free(ErrMsg);
71 ErrMsg = NULL;
72 }
73 rc = sqlite3_exec(db, lensmaker, 0, 0, &ErrMsg);
74 if (rc != SQLITE_OK) {
75 fprintf(stderr, "SQL error: %s\n", ErrMsg);
76 sqlite3_free(ErrMsg);
77 ErrMsg = NULL;
78 }
79 rc = sqlite3_exec(db, lens, 0, 0, &ErrMsg);
80 if (rc != SQLITE_OK) {
81 fprintf(stderr, "SQL error: %s\n", ErrMsg);
82 sqlite3_free(ErrMsg);
83 }
84 }
85
86 return 0;
87 }
88
89 gint add_lens(const Row* row) {
90 gint rc;
91 sqlite3_stmt* stmt;
92 gint status = 1;
93
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);
98 return status;
99 }
100
101 rc = sqlite3_bind_text(stmt, 1, row->sn, -1, NULL);
102 if (rc) {
103 fprintf(stderr, "Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db));
104 sqlite3_finalize(stmt);
105 return status;
106 }
107
108 rc = sqlite3_bind_text(stmt, 2, row->data, -1, NULL);
109 if (rc) {
110 fprintf(stderr, "Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db));
111 sqlite3_finalize(stmt);
112 return status;
113 }
114
115 rc = sqlite3_bind_text(stmt, 3, row->notes, -1, NULL);
116 if (rc) {
117 fprintf(stderr, "Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db));
118 sqlite3_finalize(stmt);
119 return status;
120 }
121
122 if (sqlite3_step(stmt) != SQLITE_DONE) {
123 fprintf(stderr, "Step failure: %s\n", sqlite3_errmsg(db));
124 } else {
125 status = 0;
126 }
127
128 sqlite3_finalize(stmt);
129
130 return status;
131 }
132
133 gint update_lens(const Row* row) {
134 gint rc;
135 sqlite3_stmt* stmt;
136 gint status = 1;
137
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);
142 return status;
143 }
144
145 rc = sqlite3_bind_text(stmt, 1, row->sn, -1, NULL);
146 if (rc) {
147 fprintf(stderr, "Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db));
148 sqlite3_finalize(stmt);
149 return status;
150 }
151
152 rc = sqlite3_bind_text(stmt, 2, row->data, -1, NULL);
153 if (rc) {
154 fprintf(stderr, "Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db));
155 sqlite3_finalize(stmt);
156 return status;
157 }
158
159 rc = sqlite3_bind_text(stmt, 3, row->notes, -1, NULL);
160 if (rc) {
161 fprintf(stderr, "Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db));
162 sqlite3_finalize(stmt);
163 return status;
164 }
165
166 rc = sqlite3_bind_int64(stmt, 4, row->id);
167 if (rc) {
168 fprintf(stderr, "Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db));
169 sqlite3_finalize(stmt);
170 return status;
171 }
172
173 if (sqlite3_step(stmt) != SQLITE_DONE) {
174 fprintf(stderr, "Step failure: %s\n", sqlite3_errmsg(db));
175 } else {
176 status = 0;
177 }
178
179 sqlite3_finalize(stmt);
180
181 return status;
182 }
183
184 gint add_maker(const gchar* maker) {
185 gint status = 1;
186
187 return status;
188 }
189
190 Maker* get_maker(gint id) {
191 gint rc;
192 sqlite3_stmt* stmt;
193 Maker* maker = NULL;
194
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);
199 return NULL;
200 }
201
202 rc = sqlite3_bind_int64(stmt, 1, id);
203 if (rc) {
204 fprintf(stderr, "Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db));
205 sqlite3_finalize(stmt);
206 return NULL;
207 }
208
209 if (sqlite3_step(stmt) == SQLITE_ROW) {
210 maker = g_new0(Maker, 1);
211 maker->id = id;
212 maker->maker_name = g_strdup((const gchar*)sqlite3_column_text(stmt, 0));
213 maker->page = sqlite3_column_int(stmt, 1);
214 }
215
216 sqlite3_finalize(stmt);
217
218 return maker;
219 }
220
221 void update_maker(const Maker* maker) {
222 gint rc;
223 sqlite3_stmt* stmt;
224
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);
229 return;
230 }
231
232 rc = sqlite3_bind_int64(stmt, 1, maker->page);
233 if (rc) {
234 fprintf(stderr, "Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db));
235 sqlite3_finalize(stmt);
236 return;
237 }
238
239 rc = sqlite3_bind_int64(stmt, 1, maker->id);
240 if (rc) {
241 fprintf(stderr, "Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db));
242 sqlite3_finalize(stmt);
243 return;
244 }
245
246 if (sqlite3_step(stmt) != SQLITE_DONE) {
247 fprintf(stderr, "Step failure: %s\n", sqlite3_errmsg(db));
248 }
249
250 sqlite3_finalize(stmt);
251 }
252
253 void maker_free(Maker* maker) {
254 g_free(maker->maker_name);
255 g_free(maker);
256 }
257
258 gint delete_maker(const gchar* maker) {
259 gint status = 1;
260
261 return status;
262 }
263
264 GSList* get_makers() {
265 gint rc;
266 sqlite3_stmt* stmt;
267 GSList* makers = NULL;
268
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));
272
273 return NULL;
274 }
275
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);
279 }
280
281 sqlite3_finalize(stmt);
282
283 return makers;
284 }
285
286 gint get_maker_id(const gchar* maker) {
287 gint rc;
288 sqlite3_stmt* stmt;
289 gint id = 0;
290
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);
295 return -1;
296 }
297
298 rc = sqlite3_bind_text(stmt, 1, maker, -1, NULL);
299 if (rc) {
300 fprintf(stderr, "Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db));
301 sqlite3_finalize(stmt);
302 return -1;
303 }
304
305 if (sqlite3_step(stmt) == SQLITE_ROW) {
306 id = sqlite3_column_int64(stmt, 0);
307 }
308
309 sqlite3_finalize(stmt);
310
311 return id;
312 }
313
314 gint delete_lens(const Row* row) {
315 gint status = 1;
316
317 return status;
318 }
319
320 GSList* get_lens_by_maker(gint id) {
321 gint rc;
322 sqlite3_stmt* stmt;
323 GSList* lenses = NULL;
324
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);
329 return NULL;
330 }
331
332 rc = sqlite3_bind_int64(stmt, 1, id);
333 if (rc) {
334 fprintf(stderr, "Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db));
335 sqlite3_finalize(stmt);
336 return NULL;
337 }
338
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);
346 }
347
348 sqlite3_finalize(stmt);
349
350 return lenses;
351 }
352
353 GSList* get_lens_by_search(const Row* row) {
354 gint rc;
355 sqlite3_stmt* stmt;
356 GSList* lenses = NULL;
357 gchar* bind1 = NULL;
358 gchar* bind2 = NULL;
359 gchar* sql = NULL;
360
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) || '%'",
364 NULL);
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);
369 } else {
370 if (row->notes) {
371 sql = g_strconcat(search, " where upper(m.maker) like '%' || upper(?1) || '%'", NULL);
372 bind1 = g_strdup(row->notes);
373 } else {
374 sql = g_strconcat(search, " where upper(l.description) like '%' || upper(?1) || '%'", NULL);
375 bind1 = g_strdup(row->data);
376 }
377 }
378
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);
383 return NULL;
384 }
385
386 if (bind1) {
387 rc = sqlite3_bind_text(stmt, 1, bind1, -1, NULL);
388 if (rc) {
389 fprintf(stderr, "Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db));
390 sqlite3_finalize(stmt);
391 return NULL;
392 }
393 }
394
395 if (bind2) {
396 rc = sqlite3_bind_text(stmt, 2, bind2, -1, NULL);
397 if (rc) {
398 fprintf(stderr, "Couldn't bind to prepared sql stmt: %s\n", sqlite3_errmsg(db));
399 sqlite3_finalize(stmt);
400 return NULL;
401 }
402 }
403
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);
412 }
413
414 sqlite3_finalize(stmt);
415 g_free(bind1);
416 g_free(bind2);
417
418 return lenses;
419 }
420
421 Row* row_new() {
422 Row* row = g_new0(Row, 1);
423 row->id = 0;
424
425 return row;
426 }
427
428 void row_free(gpointer data, gpointer user_data) {
429 Row* row = (Row *) data;
430 g_free(row->sn);
431 g_free(row->data);
432 g_free(row->notes);
433 g_free(row);
434 }
435
This page took 0.712608 seconds and 6 git commands to generate.