2 This file is part of GNUnet
3 Copyright (C) 2006, 2009, 2015 GNUnet e.V.
5 GNUnet is free software; you can redistribute it and/or modify
6 it under the terms of the GNU General Public License as published
7 by the Free Software Foundation; either version 3, or (at your
8 option) any later version.
10 GNUnet is distributed in the hope that it will be useful, but
11 WITHOUT ANY WARRANTY; without even the implied warranty of
12 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
13 General Public License for more details.
15 You should have received a copy of the GNU General Public License
16 along with GNUnet; see the file COPYING. If not, write to the
17 Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor,
18 Boston, MA 02110-1301, USA.
22 * @file datacache/plugin_datacache_sqlite.c
23 * @brief sqlite for an implementation of a database backend for the datacache
24 * @author Christian Grothoff
27 #include "gnunet_util_lib.h"
28 #include "gnunet_datacache_plugin.h"
29 #include "gnunet_sq_lib.h"
32 #define LOG(kind,...) GNUNET_log_from (kind, "datacache-sqlite", __VA_ARGS__)
34 #define LOG_STRERROR_FILE(kind,op,fn) GNUNET_log_from_strerror_file (kind, "datacache-sqlite", op, fn)
38 * How much overhead do we assume per entry in the
41 #define OVERHEAD (sizeof(struct GNUNET_HashCode) + 32)
44 * Context for all functions in this plugin.
49 * Our execution environment.
51 struct GNUNET_DATACACHE_PluginEnvironment *env;
54 * Handle to the sqlite database.
59 * Filename used for the DB.
64 * Prepared statement for #sqlite_plugin_put.
66 sqlite3_stmt *insert_stmt;
69 * Prepared statement for #sqlite_plugin_get.
71 sqlite3_stmt *get_count_stmt;
74 * Prepared statement for #sqlite_plugin_get.
76 sqlite3_stmt *get_stmt;
79 * Prepared statement for #sqlite_plugin_del.
81 sqlite3_stmt *del_select_stmt;
84 * Prepared statement for #sqlite_plugin_del.
86 sqlite3_stmt *del_stmt;
89 * Prepared statement for #sqlite_plugin_get_random.
91 sqlite3_stmt *get_random_stmt;
94 * Prepared statement for #sqlite_plugin_get_closest.
96 sqlite3_stmt *get_closest_stmt;
99 * Number of key-value pairs in the database.
101 unsigned int num_items;
106 * Log an error message at log-level @a level that indicates
107 * a failure of the command @a cmd with the error from the database @a db
109 * @param db database handle
110 * @param level log level
111 * @param cmd failed command
113 #define LOG_SQLITE(db, level, cmd) do { LOG (level, _("`%s' failed at %s:%d with error: %s\n"), cmd, __FILE__, __LINE__, sqlite3_errmsg(db)); } while(0)
117 * Execute SQL statement.
119 * @param db database handle
120 * @param cmd SQL command to execute
122 #define SQLITE3_EXEC(db, cmd) do { emsg = NULL; if (SQLITE_OK != sqlite3_exec(db, cmd, NULL, NULL, &emsg)) { LOG (GNUNET_ERROR_TYPE_ERROR | GNUNET_ERROR_TYPE_BULK, _("`%s' failed at %s:%d with error: %s\n"), "sqlite3_exec", __FILE__, __LINE__, emsg); sqlite3_free(emsg); } } while(0)
126 * @brief Prepare a SQL statement
128 * @param dbh database handle
129 * @param zsql SQL statement text
130 * @param[out] ppStmt set to the prepared statement
131 * @return 0 on success
134 sq_prepare (sqlite3 *dbh,
135 const char *zSql, /* SQL statement, UTF-8 encoded */
136 sqlite3_stmt **ppStmt)
137 { /* OUT: Statement handle */
140 return sqlite3_prepare (dbh,
144 (const char **) &dummy);
149 * Store an item in the datastore.
151 * @param cls closure (our `struct Plugin`)
152 * @param key key to store @a data under
153 * @param size number of bytes in @a data
154 * @param data data to store
155 * @param type type of the value
156 * @param discard_time when to discard the value in any case
157 * @param path_info_len number of entries in @a path_info
158 * @param path_info array of peers that have processed the request
159 * @return 0 if duplicate, -1 on error, number of bytes used otherwise
162 sqlite_plugin_put (void *cls,
163 const struct GNUNET_HashCode *key,
166 enum GNUNET_BLOCK_Type type,
167 struct GNUNET_TIME_Absolute discard_time,
168 unsigned int path_info_len,
169 const struct GNUNET_PeerIdentity *path_info)
171 struct Plugin *plugin = cls;
172 uint32_t type32 = type;
173 struct GNUNET_SQ_QueryParam params[] = {
174 GNUNET_SQ_query_param_uint32 (&type32),
175 GNUNET_SQ_query_param_absolute_time (&discard_time),
176 GNUNET_SQ_query_param_auto_from_type (key),
177 GNUNET_SQ_query_param_fixed_size (data, size),
178 GNUNET_SQ_query_param_fixed_size (path_info,
179 path_info_len * sizeof (struct GNUNET_PeerIdentity)),
180 GNUNET_SQ_query_param_end
183 LOG (GNUNET_ERROR_TYPE_DEBUG,
184 "Processing PUT of %u bytes with key `%s' and expiration %s\n",
187 GNUNET_STRINGS_relative_time_to_string (GNUNET_TIME_absolute_get_remaining (discard_time),
190 GNUNET_SQ_bind (plugin->insert_stmt,
193 LOG_SQLITE (plugin->dbh,
194 GNUNET_ERROR_TYPE_ERROR | GNUNET_ERROR_TYPE_BULK,
196 GNUNET_SQ_reset (plugin->dbh,
197 plugin->insert_stmt);
201 sqlite3_step (plugin->insert_stmt))
203 LOG_SQLITE (plugin->dbh,
204 GNUNET_ERROR_TYPE_ERROR | GNUNET_ERROR_TYPE_BULK,
206 GNUNET_SQ_reset (plugin->dbh,
207 plugin->insert_stmt);
211 GNUNET_SQ_reset (plugin->dbh,
212 plugin->insert_stmt);
213 return size + OVERHEAD;
218 * Iterate over the results for a particular key
221 * @param cls closure (our `struct Plugin`)
223 * @param type entries of which type are relevant?
224 * @param iter maybe NULL (to just count)
225 * @param iter_cls closure for @a iter
226 * @return the number of results found
229 sqlite_plugin_get (void *cls,
230 const struct GNUNET_HashCode *key,
231 enum GNUNET_BLOCK_Type type,
232 GNUNET_DATACACHE_Iterator iter,
235 struct Plugin *plugin = cls;
236 uint32_t type32 = type;
237 struct GNUNET_TIME_Absolute now;
238 struct GNUNET_TIME_Absolute exp;
245 struct GNUNET_PeerIdentity *path;
246 struct GNUNET_SQ_QueryParam params_count[] = {
247 GNUNET_SQ_query_param_auto_from_type (key),
248 GNUNET_SQ_query_param_uint32 (&type32),
249 GNUNET_SQ_query_param_absolute_time (&now),
250 GNUNET_SQ_query_param_end
252 struct GNUNET_SQ_QueryParam params_select[] = {
253 GNUNET_SQ_query_param_auto_from_type (key),
254 GNUNET_SQ_query_param_uint32 (&type32),
255 GNUNET_SQ_query_param_absolute_time (&now),
256 GNUNET_SQ_query_param_uint32 (&off),
257 GNUNET_SQ_query_param_end
259 struct GNUNET_SQ_ResultSpec rs[] = {
260 GNUNET_SQ_result_spec_variable_size (&dat,
262 GNUNET_SQ_result_spec_absolute_time (&exp),
263 GNUNET_SQ_result_spec_variable_size ((void **) &path,
265 GNUNET_SQ_result_spec_end
268 now = GNUNET_TIME_absolute_get ();
269 LOG (GNUNET_ERROR_TYPE_DEBUG,
270 "Processing GET for key `%s'\n",
274 GNUNET_SQ_bind (plugin->get_count_stmt,
277 LOG_SQLITE (plugin->dbh,
278 GNUNET_ERROR_TYPE_ERROR | GNUNET_ERROR_TYPE_BULK,
280 GNUNET_SQ_reset (plugin->dbh,
281 plugin->get_count_stmt);
285 sqlite3_step (plugin->get_count_stmt))
287 LOG_SQLITE (plugin->dbh, GNUNET_ERROR_TYPE_ERROR | GNUNET_ERROR_TYPE_BULK,
289 GNUNET_SQ_reset (plugin->dbh,
290 plugin->get_count_stmt);
291 LOG (GNUNET_ERROR_TYPE_DEBUG,
292 "No content found when processing GET for key `%s'\n",
296 total = sqlite3_column_int (plugin->get_count_stmt,
298 GNUNET_SQ_reset (plugin->dbh,
299 plugin->get_count_stmt);
304 LOG (GNUNET_ERROR_TYPE_DEBUG,
305 "No content found when processing GET for key `%s'\n",
311 off = GNUNET_CRYPTO_random_u32 (GNUNET_CRYPTO_QUALITY_WEAK,
315 off = (off + 1) % total;
317 GNUNET_SQ_bind (plugin->get_stmt,
320 LOG_SQLITE (plugin->dbh,
321 GNUNET_ERROR_TYPE_ERROR | GNUNET_ERROR_TYPE_BULK,
323 GNUNET_SQ_reset (plugin->dbh,
328 sqlite3_step (plugin->get_stmt))
331 GNUNET_SQ_extract_result (plugin->get_stmt,
335 GNUNET_SQ_reset (plugin->dbh,
339 if (0 != psize % sizeof (struct GNUNET_PeerIdentity))
345 psize /= sizeof (struct GNUNET_PeerIdentity);
347 LOG (GNUNET_ERROR_TYPE_DEBUG,
348 "Found %u-byte result when processing GET for key `%s'\n",
351 if (GNUNET_OK != iter (iter_cls,
360 GNUNET_SQ_cleanup_result (rs);
361 GNUNET_SQ_reset (plugin->dbh,
365 GNUNET_SQ_cleanup_result (rs);
366 GNUNET_SQ_reset (plugin->dbh,
369 GNUNET_SQ_reset (plugin->dbh,
376 * Delete the entry with the lowest expiration value
377 * from the datacache right now.
379 * @param cls closure (our `struct Plugin`)
380 * @return #GNUNET_OK on success, #GNUNET_SYSERR on error
383 sqlite_plugin_del (void *cls)
385 struct Plugin *plugin = cls;
389 struct GNUNET_HashCode hc;
390 struct GNUNET_SQ_ResultSpec rs[] = {
391 GNUNET_SQ_result_spec_uint64 (&rowid),
392 GNUNET_SQ_result_spec_auto_from_type (&hc),
393 GNUNET_SQ_result_spec_variable_size ((void **) &data,
395 GNUNET_SQ_result_spec_end
397 struct GNUNET_SQ_QueryParam params[] = {
398 GNUNET_SQ_query_param_uint64 (&rowid),
399 GNUNET_SQ_query_param_end
402 LOG (GNUNET_ERROR_TYPE_DEBUG,
405 sqlite3_step (plugin->del_select_stmt))
407 LOG_SQLITE (plugin->dbh,
408 GNUNET_ERROR_TYPE_ERROR | GNUNET_ERROR_TYPE_BULK,
410 GNUNET_SQ_reset (plugin->dbh,
411 plugin->del_select_stmt);
412 return GNUNET_SYSERR;
415 GNUNET_SQ_extract_result (plugin->del_select_stmt,
419 GNUNET_SQ_reset (plugin->dbh,
420 plugin->del_select_stmt);
421 return GNUNET_SYSERR;
423 GNUNET_SQ_cleanup_result (rs);
424 GNUNET_SQ_reset (plugin->dbh,
425 plugin->del_select_stmt);
427 GNUNET_SQ_bind (plugin->del_stmt,
430 LOG_SQLITE (plugin->dbh,
431 GNUNET_ERROR_TYPE_ERROR | GNUNET_ERROR_TYPE_BULK,
433 GNUNET_SQ_reset (plugin->dbh,
435 return GNUNET_SYSERR;
438 sqlite3_step (plugin->del_stmt))
440 LOG_SQLITE (plugin->dbh,
441 GNUNET_ERROR_TYPE_ERROR | GNUNET_ERROR_TYPE_BULK,
443 GNUNET_SQ_reset (plugin->dbh,
445 return GNUNET_SYSERR;
448 plugin->env->delete_notify (plugin->env->cls,
451 GNUNET_SQ_reset (plugin->dbh,
458 * Obtain a random key-value pair from the datacache.
460 * @param cls closure (our `struct Plugin`)
461 * @param iter maybe NULL (to just count)
462 * @param iter_cls closure for @a iter
463 * @return the number of results found, zero (datacache empty) or one
466 sqlite_plugin_get_random (void *cls,
467 GNUNET_DATACACHE_Iterator iter,
470 struct Plugin *plugin = cls;
471 struct GNUNET_TIME_Absolute exp;
477 struct GNUNET_PeerIdentity *path;
478 struct GNUNET_HashCode key;
479 struct GNUNET_SQ_QueryParam params[] = {
480 GNUNET_SQ_query_param_uint32 (&off),
481 GNUNET_SQ_query_param_end
483 struct GNUNET_SQ_ResultSpec rs[] = {
484 GNUNET_SQ_result_spec_variable_size (&dat,
486 GNUNET_SQ_result_spec_absolute_time (&exp),
487 GNUNET_SQ_result_spec_variable_size ((void **) &path,
489 GNUNET_SQ_result_spec_auto_from_type (&key),
490 GNUNET_SQ_result_spec_uint32 (&type),
491 GNUNET_SQ_result_spec_end
494 if (0 == plugin->num_items)
498 off = GNUNET_CRYPTO_random_u32 (GNUNET_CRYPTO_QUALITY_NONCE,
501 GNUNET_SQ_bind (plugin->get_random_stmt,
507 sqlite3_step (plugin->get_random_stmt))
510 GNUNET_SQ_reset (plugin->dbh,
511 plugin->get_random_stmt);
515 GNUNET_SQ_extract_result (plugin->get_random_stmt,
519 GNUNET_SQ_reset (plugin->dbh,
520 plugin->get_random_stmt);
523 if (0 != psize % sizeof (struct GNUNET_PeerIdentity))
529 psize /= sizeof (struct GNUNET_PeerIdentity);
530 LOG (GNUNET_ERROR_TYPE_DEBUG,
531 "Found %u-byte result with key %s when processing GET-RANDOM\n",
534 (void) iter (iter_cls,
538 (enum GNUNET_BLOCK_Type) type,
542 GNUNET_SQ_cleanup_result (rs);
543 GNUNET_SQ_reset (plugin->dbh,
544 plugin->get_random_stmt);
550 * Iterate over the results that are "close" to a particular key in
551 * the datacache. "close" is defined as numerically larger than @a
552 * key (when interpreted as a circular address space), with small
555 * @param cls closure (internal context for the plugin)
556 * @param key area of the keyspace to look into
557 * @param num_results number of results that should be returned to @a iter
558 * @param iter maybe NULL (to just count)
559 * @param iter_cls closure for @a iter
560 * @return the number of results found
563 sqlite_plugin_get_closest (void *cls,
564 const struct GNUNET_HashCode *key,
565 unsigned int num_results,
566 GNUNET_DATACACHE_Iterator iter,
569 struct Plugin *plugin = cls;
570 uint32_t num_results32 = num_results;
571 struct GNUNET_TIME_Absolute now;
572 struct GNUNET_TIME_Absolute exp;
578 struct GNUNET_HashCode hc;
579 struct GNUNET_PeerIdentity *path;
580 struct GNUNET_SQ_QueryParam params[] = {
581 GNUNET_SQ_query_param_auto_from_type (key),
582 GNUNET_SQ_query_param_absolute_time (&now),
583 GNUNET_SQ_query_param_uint32 (&num_results32),
584 GNUNET_SQ_query_param_end
586 struct GNUNET_SQ_ResultSpec rs[] = {
587 GNUNET_SQ_result_spec_variable_size (&dat,
589 GNUNET_SQ_result_spec_absolute_time (&exp),
590 GNUNET_SQ_result_spec_variable_size ((void **) &path,
592 GNUNET_SQ_result_spec_uint32 (&type),
593 GNUNET_SQ_result_spec_auto_from_type (&hc),
594 GNUNET_SQ_result_spec_end
597 now = GNUNET_TIME_absolute_get ();
598 LOG (GNUNET_ERROR_TYPE_DEBUG,
599 "Processing GET_CLOSEST for key `%s'\n",
602 GNUNET_SQ_bind (plugin->get_closest_stmt,
605 LOG_SQLITE (plugin->dbh,
606 GNUNET_ERROR_TYPE_ERROR | GNUNET_ERROR_TYPE_BULK,
608 GNUNET_SQ_reset (plugin->dbh,
609 plugin->get_closest_stmt);
614 sqlite3_step (plugin->get_closest_stmt))
617 GNUNET_SQ_extract_result (plugin->get_closest_stmt,
623 if (0 != psize % sizeof (struct GNUNET_PeerIdentity))
629 psize /= sizeof (struct GNUNET_PeerIdentity);
631 LOG (GNUNET_ERROR_TYPE_DEBUG,
632 "Found %u-byte result at %s when processing GET_CLOSE\n",
635 if (GNUNET_OK != iter (iter_cls,
644 GNUNET_SQ_cleanup_result (rs);
647 GNUNET_SQ_cleanup_result (rs);
649 GNUNET_SQ_reset (plugin->dbh,
650 plugin->get_closest_stmt);
656 * Entry point for the plugin.
658 * @param cls closure (the `struct GNUNET_DATACACHE_PluginEnvironment`)
659 * @return the plugin's closure (our `struct Plugin`)
662 libgnunet_plugin_datacache_sqlite_init (void *cls)
664 struct GNUNET_DATACACHE_PluginEnvironment *env = cls;
665 struct GNUNET_DATACACHE_PluginFunctions *api;
666 struct Plugin *plugin;
673 GNUNET_CONFIGURATION_get_value_yesno (env->cfg,
677 if (SQLITE_OK != sqlite3_open (":memory:", &dbh))
683 fn = GNUNET_DISK_mktemp ("gnunet-datacache");
689 /* fn should be UTF-8-encoded. If it isn't, it's a bug. */
690 fn_utf8 = GNUNET_strdup (fn);
691 if (SQLITE_OK != sqlite3_open (fn_utf8, &dbh))
694 GNUNET_free (fn_utf8);
700 SQLITE3_EXEC (dbh, "PRAGMA temp_store=MEMORY");
701 SQLITE3_EXEC (dbh, "PRAGMA locking_mode=EXCLUSIVE");
702 SQLITE3_EXEC (dbh, "PRAGMA journal_mode=OFF");
703 SQLITE3_EXEC (dbh, "PRAGMA synchronous=OFF");
704 SQLITE3_EXEC (dbh, "PRAGMA page_size=4092");
706 GNUNET_CONFIGURATION_get_value_yesno (env->cfg,
709 SQLITE3_EXEC (dbh, "PRAGMA sqlite_temp_store=3");
712 "CREATE TABLE ds090 (" " type INTEGER NOT NULL DEFAULT 0,"
713 " expire INTEGER NOT NULL DEFAULT 0,"
714 " key BLOB NOT NULL DEFAULT '',"
715 " value BLOB NOT NULL DEFAULT '',"
716 " path BLOB DEFAULT '')");
717 SQLITE3_EXEC (dbh, "CREATE INDEX idx_hashidx ON ds090 (key,type,expire)");
718 SQLITE3_EXEC (dbh, "CREATE INDEX idx_expire ON ds090 (expire)");
719 plugin = GNUNET_new (struct Plugin);
722 plugin->fn = fn_utf8;
725 sq_prepare (plugin->dbh,
726 "INSERT INTO ds090 (type, expire, key, value, path) "
727 "VALUES (?, ?, ?, ?, ?)",
728 &plugin->insert_stmt)) ||
730 sq_prepare (plugin->dbh,
731 "SELECT count(*) FROM ds090 "
732 "WHERE key=? AND type=? AND expire >= ?",
733 &plugin->get_count_stmt)) ||
735 sq_prepare (plugin->dbh,
736 "SELECT value,expire,path FROM ds090 "
737 "WHERE key=? AND type=? AND expire >= ? LIMIT 1 OFFSET ?",
738 &plugin->get_stmt)) ||
740 sq_prepare (plugin->dbh,
741 "SELECT _ROWID_,key,value FROM ds090 ORDER BY expire ASC LIMIT 1",
742 &plugin->del_select_stmt)) ||
744 sq_prepare (plugin->dbh,
745 "DELETE FROM ds090 WHERE _ROWID_=?",
746 &plugin->del_stmt)) ||
748 sq_prepare (plugin->dbh,
749 "SELECT value,expire,path,key,type FROM ds090 "
750 "ORDER BY key LIMIT 1 OFFSET ?",
751 &plugin->get_random_stmt)) ||
753 sq_prepare (plugin->dbh,
754 "SELECT value,expire,path,type,key FROM ds090 "
755 "WHERE key>=? AND expire >= ? ORDER BY KEY ASC LIMIT ?",
756 &plugin->get_closest_stmt))
759 LOG_SQLITE (plugin->dbh,
760 GNUNET_ERROR_TYPE_ERROR | GNUNET_ERROR_TYPE_BULK,
762 GNUNET_break (SQLITE_OK ==
763 sqlite3_close (plugin->dbh));
764 GNUNET_free (plugin);
768 api = GNUNET_new (struct GNUNET_DATACACHE_PluginFunctions);
770 api->get = &sqlite_plugin_get;
771 api->put = &sqlite_plugin_put;
772 api->del = &sqlite_plugin_del;
773 api->get_random = &sqlite_plugin_get_random;
774 api->get_closest = &sqlite_plugin_get_closest;
775 LOG (GNUNET_ERROR_TYPE_INFO,
776 "Sqlite datacache running\n");
782 * Exit point from the plugin.
784 * @param cls closure (our `struct Plugin`)
788 libgnunet_plugin_datacache_sqlite_done (void *cls)
790 struct GNUNET_DATACACHE_PluginFunctions *api = cls;
791 struct Plugin *plugin = api->cls;
794 #if SQLITE_VERSION_NUMBER >= 3007000
798 #if !WINDOWS || defined(__CYGWIN__)
799 if ( (NULL != plugin->fn) &&
800 (0 != UNLINK (plugin->fn)) )
801 LOG_STRERROR_FILE (GNUNET_ERROR_TYPE_WARNING,
804 GNUNET_free_non_null (plugin->fn);
806 sqlite3_finalize (plugin->insert_stmt);
807 sqlite3_finalize (plugin->get_count_stmt);
808 sqlite3_finalize (plugin->get_stmt);
809 sqlite3_finalize (plugin->del_select_stmt);
810 sqlite3_finalize (plugin->del_stmt);
811 sqlite3_finalize (plugin->get_random_stmt);
812 sqlite3_finalize (plugin->get_closest_stmt);
813 result = sqlite3_close (plugin->dbh);
814 #if SQLITE_VERSION_NUMBER >= 3007000
815 if (SQLITE_BUSY == result)
817 LOG (GNUNET_ERROR_TYPE_WARNING,
818 _("Tried to close sqlite without finalizing all prepared statements.\n"));
819 stmt = sqlite3_next_stmt (plugin->dbh, NULL);
822 result = sqlite3_finalize (stmt);
823 if (result != SQLITE_OK)
824 LOG (GNUNET_ERROR_TYPE_WARNING,
825 "Failed to close statement %p: %d\n",
828 stmt = sqlite3_next_stmt (plugin->dbh, NULL);
830 result = sqlite3_close (plugin->dbh);
833 if (SQLITE_OK != result)
834 LOG_SQLITE (plugin->dbh,
835 GNUNET_ERROR_TYPE_ERROR,
838 #if WINDOWS && !defined(__CYGWIN__)
839 if ( (NULL != plugin->fn) &&
840 (0 != UNLINK (plugin->fn)) )
841 LOG_STRERROR_FILE (GNUNET_ERROR_TYPE_WARNING,
844 GNUNET_free_non_null (plugin->fn);
846 GNUNET_free (plugin);
853 /* end of plugin_datacache_sqlite.c */