Line data Source code
1 : /* -*- Mode: C; tab-width: 4; indent-tabs-mode: t; c-basic-offset: 4 -*- */
2 : /*
3 : * database.c
4 : * This file is part of "Sauvegarde" project.
5 : *
6 : * (C) Copyright 2014 - 2016 Olivier Delhomme
7 : * e-mail : olivier.delhomme@free.fr
8 : *
9 : * "Sauvegarde" is free software: you can redistribute it and/or modify
10 : * it under the terms of the GNU General Public License as published by
11 : * the Free Software Foundation, either version 3 of the License, or
12 : * (at your option) any later version.
13 : *
14 : * "Sauvegarde" is distributed in the hope that it will be useful,
15 : * but WITHOUT ANY WARRANTY; without even the implied warranty of
16 : * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
17 : * GNU General Public License for more details.
18 : *
19 : * You should have received a copy of the GNU General Public License
20 : * along with "Sauvegarde". If not, see <http://www.gnu.org/licenses/>
21 : */
22 :
23 : /**
24 : * @file database.c
25 : * This file contains the functions to wrap database calls in all the
26 : * programs of "Sauvegarde" project.
27 : */
28 :
29 : #include "libcdpfgl.h"
30 :
31 : static void print_db_error(sqlite3 *db, const char *format, ...);
32 : static void exec_sql_cmd(db_t *database, gchar *sql_cmd, gchar *format_message);
33 : static int table_callback(void *num, int nbCol, char **data, char **nomCol);
34 : static void verify_if_tables_exists(db_t *database);
35 : static file_row_t *new_file_row_t(void);
36 : static void free_file_row_t(file_row_t *row);
37 : static int get_file_callback(void *a_row, int nb_col, char **data, char **name_col);
38 : static file_row_t *get_file_id(db_t *database, meta_data_t *meta);
39 :
40 :
41 : /**
42 : * @returns a string containing the version of the database used.
43 : */
44 3 : gchar *db_version(void)
45 : {
46 3 : return (gchar *) sqlite3_libversion();
47 : }
48 :
49 :
50 : /**
51 : * Prints an error message to stderr and exit (db errors are considered as
52 : * fatal for now).
53 : * @param db : file connexion to the database.
54 : * @param format : the format of the message (as in printf)
55 : * @param ... : va_list of variable that are to be printed into format.
56 : */
57 0 : static void print_db_error(sqlite3 *db, const char *format, ...)
58 : {
59 : va_list ap;
60 :
61 0 : va_start(ap, format);
62 0 : vfprintf(stderr, format, ap);
63 0 : va_end(ap);
64 :
65 : /* sqlite3_close(db);
66 : exit(EXIT_FAILURE);
67 : */
68 0 : }
69 :
70 :
71 : /**
72 : * Executes the SQL command onto the database without any callback
73 : * @param database : the db_t * structure that contains the database connexion
74 : * @param sql_cmd : a gchar * SQL command to be executed onto the database
75 : * @param format_message : a gchar * format message to be used in case of an error
76 : */
77 149240 : static void exec_sql_cmd(db_t *database, gchar *sql_cmd, gchar *format_message)
78 : {
79 149240 : char *error_message = NULL;
80 149240 : const char *message = NULL;
81 149240 : int result = 0;
82 :
83 149240 : result = sqlite3_exec(database->db, sql_cmd, NULL, 0, &error_message);
84 :
85 149240 : if (result != SQLITE_OK)
86 : {
87 0 : result = sqlite3_extended_errcode(database->db);
88 : /** @note sqlite3_errstr needs at least sqlite 3.7.15 */
89 0 : message = sqlite3_errstr(result);
90 0 : print_db_error(database->db, format_message, result, message);
91 : }
92 149240 : }
93 :
94 :
95 : /**
96 : * Does a commit on the database
97 : * @param database : the db_t * structure that contains the database connexion
98 : */
99 49745 : static void sql_commit(db_t *database)
100 : {
101 49745 : exec_sql_cmd(database, "COMMIT;", _("(%d) Error commiting to the database: %s\n"));
102 49745 : }
103 :
104 :
105 : /**
106 : * Does a commit on the database
107 : * @param database : the db_t * structure that contains the database connexion
108 : */
109 49745 : static void sql_begin(db_t *database)
110 : {
111 49745 : exec_sql_cmd(database, "BEGIN;", _("(%d) Error openning the transaction: %s\n"));
112 49745 : }
113 :
114 :
115 : /**
116 : * Counts the number of row that we have by incrementing i.
117 : * @param num is an integer that will count the number of rows in the result.
118 : * @param nb_col gives the number of columns in this row.
119 : * @param data contains the data of each column.
120 : * @param name_col contains the name of each column.
121 : * @returns always 0.
122 : */
123 0 : static int table_callback(void *num, int nb_col, char **data, char **name_col)
124 : {
125 0 : int *i = (int *) num;
126 :
127 0 : *i = *i + 1;
128 :
129 0 : return 0;
130 : }
131 :
132 :
133 : /**
134 : * Verifies if the tables are created whithin the database and creates
135 : * them if there is no tables at all.
136 : * @param database : the structure to manage database's connexion.
137 : */
138 1 : static void verify_if_tables_exists(db_t *database)
139 : {
140 1 : char *error_message = NULL;
141 1 : int result = 0;
142 1 : int *i = NULL; /** int *i is used to count the number of row */
143 :
144 1 : i = (int *) g_malloc0(sizeof(int));
145 1 : *i = 0;
146 :
147 : /* Trying to get all the tables that are in the database */
148 1 : result = sqlite3_exec(database->db, "SELECT * FROM sqlite_master WHERE type='table';", table_callback, i, &error_message);
149 :
150 1 : if (result == SQLITE_OK && *i == 0) /* No row (0) means that there is no table */
151 : {
152 1 : print_debug(_("Creating tables into the database\n"));
153 :
154 : /* The database does not contain any tables. So we have to create them. */
155 : /* Creation of buffers table that contains checksums and their associated data */
156 1 : exec_sql_cmd(database, "CREATE TABLE buffers (buffer_id INTEGER PRIMARY KEY AUTOINCREMENT, url TEXT, data TEXT);", _("(%d) Error while creating database table 'buffers': %s\n"));
157 :
158 : /* Creation of transmited table that may contain id of transmited buffers if any + creation of its indexes */
159 1 : exec_sql_cmd(database, "CREATE TABLE transmited (buffer_id INTEGER PRIMARY KEY);", _("(%d) Error while creating database table 'transmited': %s\n"));
160 :
161 1 : exec_sql_cmd(database, "CREATE INDEX main.transmited_buffer_id ON transmited (buffer_id ASC)", _("(%d) Error while creating index 'transmited_buffer_id': %s\n"));
162 :
163 : /* Creation of files table that contains everything about a file */
164 1 : exec_sql_cmd(database, "CREATE TABLE files (file_id INTEGER PRIMARY KEY AUTOINCREMENT, cache_time INTEGER, type INTEGER, inode INTEGER, file_user TEXT, file_group TEXT, uid INTEGER, gid INTEGER, atime INTEGER, ctime INTEGER, mtime INTEGER, mode INTEGER, size INTEGER, name TEXT, transmitted BOOL, link TEXT);", _("(%d) Error while creating database table 'files': %s\n"));
165 :
166 1 : exec_sql_cmd(database, "CREATE INDEX main.files_inodes ON files (inode ASC)", _("(%d) Error while creating index 'files_inodes': %s\n"));
167 : }
168 :
169 1 : free_variable(i);
170 :
171 : /**
172 : * We are setting the asynchronous mode of SQLITE here. Tradeoff is that any
173 : * powerloss is leading to a database corruption and data loss !
174 : * @note This is NOT a good idea !
175 : */
176 : /* exec_sql_cmd(database, "PRAGMA synchronous = OFF;", _("Error while trying to set asynchronous mode.\n")); */
177 :
178 1 : }
179 :
180 :
181 : /**
182 : * Says whether a file is in already in the cache or not
183 : * @param database is the structure that contains everything that is
184 : * related to the database (it's connexion for instance).
185 : * @param meta is the file's metadata that we want to know if it's already
186 : * in the cache.
187 : * @returns a boolean that says TRUE if the file is already in the cache
188 : * and FALSE if not.
189 : */
190 49745 : gboolean is_file_in_cache(db_t *database, meta_data_t *meta)
191 : {
192 49745 : file_row_t *row = NULL;
193 :
194 49745 : if (meta != NULL && database != NULL)
195 : {
196 :
197 49745 : row = get_file_id(database, meta);
198 :
199 49745 : if (row != NULL)
200 : {
201 49745 : if (row->nb_row == 0) /* No row has been returned. It means that the file isn't in the cache */
202 : {
203 49745 : free_file_row_t(row);
204 49745 : return FALSE;
205 : }
206 : else
207 : { /* At least one row has been returned */
208 0 : free_file_row_t(row);
209 0 : return TRUE;
210 : }
211 : }
212 : else
213 : {
214 : return FALSE;
215 : }
216 : }
217 : else
218 : {
219 : return FALSE;
220 : }
221 : }
222 :
223 :
224 : /**
225 : * Gets file_ids from returned rows.
226 : * @param a_row is a file_row_t * structure
227 : * @param nb_col gives the number of columns in this row.
228 : * @param data contains the data of each column.
229 : * @param name_col contains the name of each column.
230 : * @returns always 0.
231 : */
232 0 : static int get_file_callback(void *a_row, int nb_col, char **data, char **name_col)
233 : {
234 0 : file_row_t *row = (file_row_t *) a_row;
235 :
236 0 : row->nb_row = row->nb_row + 1;
237 0 : row->id_list = g_slist_append(row->id_list, g_strdup(data[0]));
238 :
239 0 : return 0;
240 : }
241 :
242 :
243 : /**
244 : * Returns the file_id for the specified file.
245 : * @param database is the structure that contains everything that is
246 : * related to the database (it's connexion for instance).
247 : * @param meta is the file's metadata that we want to insert into the
248 : * cache.
249 : * @returns a file_row_t structure filed with values returned by the
250 : * database.
251 : */
252 49745 : static file_row_t *get_file_id(db_t *database, meta_data_t *meta)
253 : {
254 49745 : file_row_t *row = NULL;
255 49745 : char *error_message = NULL;
256 49745 : gchar *sql_command = NULL;
257 49745 : int db_result = 0;
258 :
259 49745 : row = new_file_row_t();
260 :
261 49745 : sql_command = g_strdup_printf("SELECT file_id from files WHERE inode=%" G_GUINT64_FORMAT " AND name='%s' AND type=%d AND uid=%d AND gid=%d AND ctime=%" G_GUINT64_FORMAT " AND mtime=%" G_GUINT64_FORMAT " AND mode=%d AND size=%" G_GUINT64_FORMAT ";", meta->inode, meta->name, meta->file_type, meta->uid, meta->gid, meta->ctime, meta->mtime, meta->mode, meta->size);
262 :
263 49745 : db_result = sqlite3_exec(database->db, sql_command, get_file_callback, row, &error_message);
264 :
265 49745 : free_variable(sql_command);
266 : /* exec_sql_cmd(database, "COMMIT;", _("(%d) Error commiting to the database: %s\n")); */
267 :
268 49745 : if (db_result == SQLITE_OK)
269 : {
270 : return row;
271 : }
272 : else
273 : {
274 0 : print_db_error(database->db, _("(%d) Error while searching into the table 'files': %s\n"), db_result, error_message);
275 0 : return NULL; /* to avoid a compilation warning as we exited with failure in print_db_error */
276 : }
277 : }
278 :
279 :
280 : /**
281 : * Creates and inits a new file_row_t * structure.
282 : * @returns an empty file_row_t * structure.
283 : */
284 : static file_row_t *new_file_row_t(void)
285 : {
286 49745 : file_row_t *row = NULL;
287 :
288 49745 : row = (file_row_t *) g_malloc(sizeof(file_row_t));
289 :
290 49745 : row->nb_row = 0;
291 49745 : row->id_list = NULL;
292 :
293 : return row;
294 : }
295 :
296 :
297 : /**
298 : * Frees everything whithin the file_row_t structure
299 : * @param row is the variable to be freed totaly
300 : */
301 49745 : static void free_file_row_t(file_row_t *row)
302 : {
303 49745 : if (row != NULL)
304 : {
305 49745 : g_slist_free_full(row->id_list, free_gchar_variable);
306 49745 : free_variable(row);
307 : }
308 49745 : }
309 :
310 :
311 : /**
312 : * Insert file into cache. One should have verified that the file
313 : * does not already exists in the database.
314 : * @note insert_file_into_cache is fast but does not garantee that the
315 : * data is on the disk !
316 : * @param database is the structure that contains everything that is
317 : * related to the database (it's connexion for instance).
318 : * @param meta is the file's metadata that we want to insert into the
319 : * cache.
320 : * @param only_meta : a gboolean that when set to TRUE only meta_data will
321 : * be saved and hashs data will not ! FALSE means that something
322 : * went wrong with server and that all data will be cached localy.
323 : */
324 49745 : void db_save_meta_data(db_t *database, meta_data_t *meta, gboolean only_meta)
325 : {
326 49745 : gchar *sql_command = NULL; /** gchar *sql_command is the command to be executed */
327 49745 : guint64 cache_time = 0;
328 :
329 49745 : if (meta != NULL && database != NULL)
330 : {
331 49745 : cache_time = g_get_real_time();
332 :
333 : /* beginning a transaction */
334 49745 : sql_begin(database);
335 :
336 : /* Inserting the file into the files table */
337 49745 : sql_command = g_strdup_printf("INSERT INTO files (cache_time, type, inode, file_user, file_group, uid, gid, atime, ctime, mtime, mode, size, name, transmitted, link) VALUES (%" G_GUINT64_FORMAT ", %d, %" G_GUINT64_FORMAT ", '%s', '%s', %d, %d, %" G_GUINT64_FORMAT ", %" G_GUINT64_FORMAT ", %" G_GUINT64_FORMAT ", %d, %" G_GUINT64_FORMAT ", '%s', %d, '%s');", cache_time, meta->file_type, meta->inode, meta->owner, meta->group, meta->uid, meta->gid, meta->atime, meta->ctime, meta->mtime, meta->mode, meta->size, meta->name, only_meta, meta->link);
338 :
339 49745 : exec_sql_cmd(database, sql_command, _("(%d) Error while inserting into the table 'files': %s\n"));
340 :
341 49745 : free_variable(sql_command);
342 :
343 : /* ending the transaction here */
344 49745 : sql_commit(database);
345 : }
346 49745 : }
347 :
348 :
349 : /**
350 : * Saves buffers that could not be sent to server
351 : * @param database is the structure that contains everything that is
352 : * related to the database (it's connexion for instance).
353 : * @param url is the url where buffer should have been POSTed
354 : * @param buffer is the buffer containing data that should have been
355 : * POSTed to server but couldn't.
356 : */
357 0 : void db_save_buffer(db_t *database, gchar *url, gchar *buffer)
358 : {
359 0 : gchar *sql_command = NULL; /** gchar *sql_command is the command to be executed */
360 :
361 0 : if (database != NULL && url != NULL && buffer != NULL)
362 : {
363 0 : sql_begin(database);
364 :
365 0 : sql_command = g_strdup_printf("INSERT INTO buffers (url, data) VALUES ('%s', '%s');", url, buffer);
366 0 : exec_sql_cmd(database, sql_command, _("(%d) Error while inserting into the table 'buffers': %s\n"));
367 0 : free_variable(sql_command);
368 :
369 0 : sql_commit(database);
370 : }
371 0 : }
372 :
373 :
374 : /**
375 : * This function says if the table 'buffers' is empty or not, that is
376 : * to say whether we have to transmit unsaved data or not.
377 : * @param database is the structure that contains everything that is
378 : * related to the database (it's connexion for instance).
379 : * @returns TRUE if table 'buffers' is not empty and FALSE otherwise
380 : */
381 40 : gboolean db_is_there_buffers_to_transmit(db_t *database)
382 : {
383 40 : char *error_message = NULL;
384 40 : int result = 0;
385 40 : int *i = NULL; /** int *i is used to count the number of row */
386 :
387 40 : i = (int *) g_malloc0(sizeof(int));
388 40 : *i = 0;
389 :
390 40 : result = sqlite3_exec(database->db, "SELECT * FROM buffers WHERE buffers.buffer_id NOT IN (SELECT transmited.buffer_id FROM transmited INNER JOIN buffers ON transmited.buffer_id = buffers.buffer_id);", table_callback, i, &error_message);
391 :
392 40 : if (result == SQLITE_OK && *i == 0)
393 : {
394 : /* Table exists but there is no buffers to transmit to server */
395 : return FALSE;
396 : }
397 0 : else if (result == SQLITE_OK && *i > 0)
398 : {
399 : /* Table exists and there is buffers to transmit to server */
400 : return TRUE;
401 : }
402 : else
403 : {
404 : /* result is not SQLITE_OK : something went wrong */
405 0 : return FALSE;
406 : }
407 : }
408 :
409 :
410 : /**
411 : * Transmits each row found in the database
412 : * @param userp is a pointer to a transmited_t * structure that must contain
413 : * a comm_t * pointer and a db_t * pointer.
414 : * @param nb_col gives the number of columns in this row.
415 : * @param data contains the data of each column.
416 : * @param name_col contains the name of each column.
417 : * @returns always 0.
418 : */
419 0 : static int transmit_callback(void *userp, int nb_col, char **data, char **name_col)
420 : {
421 0 : transmited_t *trans = (transmited_t *) userp;
422 0 : gchar *sql_command = NULL;
423 0 : gint success = 0;
424 :
425 0 : if (trans != NULL && data != NULL && trans->comm != NULL && trans->database != NULL)
426 : {
427 :
428 0 : trans->comm->readbuffer = data[2]; /** data[2] is the data column in buffers table of the database */
429 0 : success = post_url(trans->comm, data[1]); /** data[1] is the url column in buffers table of the database */
430 :
431 0 : if (success == CURLE_OK)
432 : {
433 0 : sql_begin(trans->database);
434 :
435 0 : sql_command = g_strdup_printf("INSERT INTO transmited (buffer_id) VALUES ('%s');", data[0]);
436 0 : exec_sql_cmd(trans->database, sql_command, _("(%d) Error while inserting into the table 'transmited': %s\n"));
437 0 : free_variable(sql_command);
438 :
439 0 : sql_commit(trans->database);
440 : }
441 : /** @todo use the result of post to be able to manage errors */
442 : }
443 :
444 0 : return 0;
445 : }
446 :
447 :
448 : /**
449 : * Allocates a new structure to be passed sqlite3_exec callback in
450 : * db_transmit_buffers function.
451 : * @param database is the pointer to the db_t * structure.
452 : * @param comm_t is the pointer to the comm_t * structure.
453 : * @returns a newly allocated transmited_t * structure that may be freed
454 : * when no longer needed.
455 : */
456 : static transmited_t *new_transmited_t(db_t *database, comm_t *comm)
457 : {
458 0 : transmited_t *trans = NULL;
459 :
460 0 : trans = (transmited_t *) g_malloc0(sizeof(transmited_t));
461 :
462 0 : trans->database = database;
463 0 : trans->comm = comm;
464 :
465 : return trans;
466 : }
467 :
468 :
469 : /**
470 : * Deletes all transmited buffers from the buffers table in database
471 : * based on transmited table.
472 : * @param userp is a pointer to a db_t * structure
473 : * @param nb_col gives the number of columns in this row.
474 : * @param data contains the data of each column.
475 : * @param name_col contains the name of each column.
476 : * @returns always 0.
477 : */
478 0 : static int delete_transmited_callback(void *userp, int nb_col, char **data, char **name_col)
479 : {
480 0 : db_t *database = (db_t *) userp;
481 0 : gchar *sql_command = NULL;
482 :
483 0 : if (database != NULL && data != NULL)
484 : {
485 0 : sql_begin(database);
486 :
487 0 : sql_command = g_strdup_printf("DELETE FROM buffers WHERE buffer_id='%s';", data[0]);
488 0 : exec_sql_cmd(database, sql_command, _("(%d) Error while deleting from table 'buffers': %s\n"));
489 0 : free_variable(sql_command);
490 :
491 0 : sql_commit(database);
492 : }
493 :
494 0 : return 0;
495 : }
496 :
497 :
498 : /**
499 : * Deletes transmited buffers from table 'buffers' of the database
500 : * @param database is the pointer to the db_t * structure.
501 : * @returns the result of the sqlite3_exec() function
502 : */
503 0 : static int delete_transmited_buffers(db_t *database)
504 : {
505 0 : char *error_message = NULL;
506 0 : int result = 0;
507 :
508 : /* This should select every buffer_id that where transmited and not deleted (that are still present in buffers table) */
509 0 : result = sqlite3_exec(database->db, "SELECT transmited.buffer_id FROM transmited INNER JOIN buffers ON transmited.buffer_id = buffers.buffer_id;", delete_transmited_callback, database, &error_message);
510 :
511 0 : return result;
512 : }
513 :
514 :
515 : /**
516 : * This function transferts the 'buffers' that are stored in the database
517 : * @param database is the structure that contains everything that is
518 : * related to the database (it's connexion for instance).
519 : * @param comm a comm_t * structure that must contain an initialized
520 : * curl_handle (must not be NULL).
521 : * @returns TRUE if table 'buffers' is not empty and FALSE otherwise
522 : */
523 0 : gboolean db_transmit_buffers(db_t *database, comm_t *comm)
524 : {
525 0 : char *error_message = NULL;
526 0 : int result = 0;
527 0 : transmited_t *trans = NULL;
528 :
529 0 : trans = new_transmited_t(database, comm);
530 :
531 : /* This should select only the rows in buffers that are not in transmited based on the primary key buffer_id */
532 0 : result = sqlite3_exec(database->db, "SELECT * FROM buffers WHERE buffers.buffer_id NOT IN (SELECT transmited.buffer_id FROM transmited INNER JOIN buffers ON transmited.buffer_id = buffers.buffer_id);", transmit_callback, trans, &error_message);
533 :
534 0 : g_free(trans);
535 :
536 0 : delete_transmited_buffers(database);
537 : /** @todo Catch the return value of this function and do something with it */
538 :
539 0 : if (result == SQLITE_OK)
540 : {
541 : /* Table exists and buffers has been transmited to the server */
542 : return TRUE;
543 : }
544 : else
545 : {
546 : /* result is not SQLITE_OK : something went wrong but buffers may have been transmited to the server */
547 0 : return FALSE;
548 : }
549 : }
550 :
551 :
552 : /**
553 : * Returns a database connexion or NULL.
554 : * @param database_name is the filename of the file that contains the
555 : * database
556 : * @result returns a db_t * filled with the database connexion or NULL
557 : * in case of an error.
558 : */
559 1 : db_t *open_database(gchar *database_name)
560 : {
561 1 : db_t *database = NULL;
562 1 : sqlite3 *db = NULL;
563 1 : int result = 0;
564 :
565 1 : database = (db_t *) g_malloc0(sizeof(db_t));
566 :
567 1 : result = sqlite3_open(database_name, &db);
568 :
569 1 : if (result != SQLITE_OK)
570 : {
571 0 : print_db_error(db, _("(%d) Error while trying to open %s database: %s\n"), result, database_name, sqlite3_errmsg(db));
572 0 : free_variable(database);
573 0 : sqlite3_close(db);
574 :
575 0 : return NULL;
576 : }
577 : else
578 : {
579 1 : database->db = db;
580 1 : sqlite3_extended_result_codes(db, 1);
581 1 : verify_if_tables_exists(database);
582 :
583 1 : return database;
584 : }
585 : }
586 :
587 :
588 :
589 :
|
