"Fossies" - the Fresh Open Source Software Archive

Member "ncdc-1.22.1/src/db.c" (26 Mar 2019, 48855 Bytes) of package /linux/privat/ncdc-1.22.1.tar.gz:


As a special service "Fossies" has tried to format the requested source page into HTML format using (guessed) C and C++ source code syntax highlighting (style: standard) with prefixed line numbers and code folding option. Alternatively you can here view or download the uninterpreted source code file. For more information about "db.c" see the Fossies "Dox" file reference documentation and the last Fossies "Diffs" side-by-side code changes report: 1.20_vs_1.21.

    1 /* ncdc - NCurses Direct Connect client
    2 
    3   Copyright (c) 2011-2019 Yoran Heling
    4 
    5   Permission is hereby granted, free of charge, to any person obtaining
    6   a copy of this software and associated documentation files (the
    7   "Software"), to deal in the Software without restriction, including
    8   without limitation the rights to use, copy, modify, merge, publish,
    9   distribute, sublicense, and/or sell copies of the Software, and to
   10   permit persons to whom the Software is furnished to do so, subject to
   11   the following conditions:
   12 
   13   The above copyright notice and this permission notice shall be included
   14   in all copies or substantial portions of the Software.
   15 
   16   THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
   17   EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
   18   MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT.
   19   IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY
   20   CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT,
   21   TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE
   22   SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
   23 
   24 */
   25 
   26 
   27 #include "ncdc.h"
   28 #include "db.h"
   29 
   30 // Most of the db_* functions can be used from multiple threads. The database
   31 // is only accessed from within the database thread (db_thread_func()). All
   32 // access to the database from other threads is performed via message passing.
   33 //
   34 // Some properties of this implementation:
   35 // - Multiple UPDATE/DELETE/INSERT statements in a short interval are grouped
   36 //   together in a single transaction.
   37 // - All queries are executed in the same order as they are queued.
   38 
   39 
   40 // TODO: Improve error handling. In the current implementation, if an error
   41 // occurs, the transaction is aborted and none of the queries scheduled for the
   42 // transaction is executed. The only way the user can know that his has
   43 // happened is by looking at stderr.log, it'd be better to provide a notify to
   44 // the UI.
   45 
   46 
   47 static GAsyncQueue *db_queue = NULL;
   48 static GThread *db_thread = NULL;
   49 static GHashTable *db_stmt_cache = NULL;
   50 
   51 
   52 // A "queue item" is a darray (see util.c) to represent a queued SQL query,
   53 // with the following structure:
   54 //   int32 = flags
   55 //   ptr   = (char *)sql_query. This must be a static string in global memory.
   56 // arguments:
   57 //   int32 = type
   58 //   rest depending on type:
   59 //     NULL:  no further arguments
   60 //     INT:   int32
   61 //     INT64: int64
   62 //     TEXT:  string
   63 //     BLOB:  dat
   64 //     RES:   ptr to a GAsyncQueue followed by an array of int32 DBQ_* items
   65 //            until DBQ_END. (Only INT, INT64, TEXT and BLOB can be used)
   66 //   if(type != END)
   67 //     goto arguments
   68 
   69 // A "result item" is a darray to represent a result row, with the following
   70 // structure:
   71 //   int32 = result code (SQLITE_ROW, SQLITE_DONE or anything else for error)
   72 // For SQLITE_DONE:
   73 //   if DBQ_LASTID is requested: int64. Otherwise no other arguments.
   74 // For SQLITE_ROW:
   75 //   for each array in the above RES thing, the data of the column.
   76 
   77 
   78 // Query flags
   79 #define DBF_NEXT    1 // Current query must be in the same transaction as next query in the queue.
   80 #define DBF_LAST    2 // Current query must be the last in a transaction (forces a flush)
   81 #define DBF_SINGLE  4 // Query must not be executed in a transaction (e.g. VACUUM)
   82 #define DBF_NOCACHE 8 // Don't cache this query in the prepared statement cache
   83 #define DBF_END   128 // Signal the database thread to close
   84 
   85 // Column types
   86 #define DBQ_END    0
   87 #define DBQ_NULL   1 // No arguments
   88 #define DBQ_INT    2 // int
   89 #define DBQ_INT64  3 // gint64
   90 #define DBQ_TEXT   4 // char * (NULL allowed)
   91 #define DBQ_BLOB   5 // int length, char *data (NULL allowed)
   92 #define DBQ_RES    6
   93 #define DBQ_LASTID 7 // To indicate that the query wants the last inserted row id as result
   94 
   95 
   96 // How long to keep a transaction active before flushing. In microseconds.
   97 #define DB_FLUSH_TIMEOUT (5000000)
   98 
   99 
  100 // Give back a final response and unref the queue.
  101 static void db_queue_item_final(GAsyncQueue *res, int code, gint64 lastid) {
  102   if(!res)
  103     return;
  104   GByteArray *r = g_byte_array_new();
  105   darray_init(r);
  106   darray_add_int32(r, code);
  107   if(code == SQLITE_DONE)
  108     darray_add_int64(r, lastid);
  109   g_async_queue_push(res, g_byte_array_free(r, FALSE));
  110   g_async_queue_unref(res);
  111 }
  112 
  113 
  114 // Give back an error result and decrement the reference counter of the
  115 // response queue. Assumes the `flags' has already been read.
  116 static void db_queue_item_error(char *q) {
  117   char *b = darray_get_ptr(q); // query
  118   b++; // otherwise gcc will complain
  119   int t;
  120   while((t = darray_get_int32(q)) != DBQ_END && t != DBQ_RES)
  121     ;
  122   if(t == DBQ_RES)
  123     db_queue_item_final(darray_get_ptr(q), SQLITE_ERROR, 0);
  124 }
  125 
  126 
  127 // Similar to sqlite3_prepare_v2(), except this returns a cached statement
  128 // handler if the query had already been prepared before. Note that the lookup
  129 // in the db_stmt_cache is *NOT* done by the actual query string, but by its
  130 // pointer value. This is a lot more efficient, but assumes that SQL statements
  131 // are never dynamically generated: they must be somewhere in static memory.
  132 // Note: db_stmt_cache is assumed to be used only for the given *db pointer.
  133 // Important: DON'T run sqlite3_finalize() on queries returned by this
  134 // function! Use sqlite3_reset() instead.
  135 static int db_queue_process_prepare(sqlite3 *db, const char *query, sqlite3_stmt **s) {
  136   *s = g_hash_table_lookup(db_stmt_cache, query);
  137   if(*s)
  138     return SQLITE_OK;
  139   int r = sqlite3_prepare_v2(db, query, -1, s, NULL);
  140   if(r == SQLITE_OK)
  141     g_hash_table_insert(db_stmt_cache, (gpointer)query, *s);
  142   return r;
  143 }
  144 
  145 
  146 // Executes a single query.
  147 // If transaction = TRUE, the query is assumed to be executed in a transaction
  148 //   (which has already been initiated)
  149 // The return path (if any) and lastid (0 if not requested) are stored in *res
  150 // and *lastid. The caller of this function is responsible for sending back the
  151 // final response. If this function returns anything other than SQLITE_DONE,
  152 // the query has failed.
  153 // It is assumed that the first `flags' part of the queue item has already been
  154 // fetched.
  155 static int db_queue_process_one(sqlite3 *db, char *q, gboolean nocache, gboolean transaction, GAsyncQueue **res, gint64 *lastid) {
  156   char *query = darray_get_ptr(q);
  157   *res = NULL;
  158   *lastid = 0;
  159 
  160   // Would be nice to have the parameters logged
  161   g_debug("db: Executing \"%s\"", query);
  162 
  163   // Get statement handler
  164   int r = SQLITE_ROW;
  165   sqlite3_stmt *s;
  166   if(nocache ? sqlite3_prepare_v2(db, query, -1, &s, NULL) : db_queue_process_prepare(db, query, &s)) {
  167     g_critical("SQLite3 error preparing `%s': %s", query, sqlite3_errmsg(db));
  168     r = SQLITE_ERROR;
  169   }
  170 
  171   // Bind parameters
  172   int t, n;
  173   int i = 1;
  174   char *a;
  175   while((t = darray_get_int32(q)) != DBQ_END && t != DBQ_RES) {
  176     if(r == SQLITE_ERROR)
  177       continue;
  178     switch(t) {
  179     case DBQ_NULL:
  180       sqlite3_bind_null(s, i);
  181       break;
  182     case DBQ_INT:
  183       sqlite3_bind_int(s, i, darray_get_int32(q));
  184       break;
  185     case DBQ_INT64:
  186       sqlite3_bind_int64(s, i, darray_get_int64(q));
  187       break;
  188     case DBQ_TEXT:
  189       sqlite3_bind_text(s, i, darray_get_string(q), -1, SQLITE_STATIC);
  190       break;
  191     case DBQ_BLOB:
  192       a = darray_get_dat(q, &n);
  193       sqlite3_bind_blob(s, i, a, n, SQLITE_STATIC);
  194       break;
  195     }
  196     i++;
  197   }
  198 
  199   // Fetch information about what results we need to send back
  200   gboolean wantlastid = FALSE;
  201   char columns[20]; // 20 should be enough for everyone
  202   n = 0;
  203   if(t == DBQ_RES) {
  204     *res = darray_get_ptr(q);
  205     while((t = darray_get_int32(q)) != DBQ_END) {
  206       if(t == DBQ_LASTID)
  207         wantlastid = TRUE;
  208       else
  209         columns[n++] = t;
  210     }
  211   }
  212 
  213   // Execute query
  214   while(r == SQLITE_ROW) {
  215     // do the step()
  216     if(transaction)
  217       r = sqlite3_step(s);
  218     else
  219       while((r = sqlite3_step(s)) == SQLITE_BUSY)
  220         ;
  221     if(r != SQLITE_DONE && r != SQLITE_ROW)
  222       g_critical("SQLite3 error on step() of `%s': %s", query, sqlite3_errmsg(db));
  223     // continue with the next step() if we're not going to do anything with the results
  224     if(r != SQLITE_ROW || !*res || !n)
  225       continue;
  226     // send back a response
  227     GByteArray *rc = g_byte_array_new();
  228     darray_init(rc);
  229     darray_add_int32(rc, r);
  230     for(i=0; i<n; i++) {
  231       switch(columns[i]) {
  232       case DBQ_INT:   darray_add_int32( rc, sqlite3_column_int(  s, i)); break;
  233       case DBQ_INT64: darray_add_int64( rc, sqlite3_column_int64(s, i)); break;
  234       case DBQ_TEXT:  darray_add_string(rc, (char *)sqlite3_column_text( s, i)); break;
  235       case DBQ_BLOB:  darray_add_dat(   rc, sqlite3_column_blob( s, i), sqlite3_column_bytes(s, i)); break;
  236       default: g_warn_if_reached();
  237       }
  238     }
  239     g_async_queue_push(*res, g_byte_array_free(rc, FALSE));
  240   }
  241 
  242   // Fetch last id, if requested
  243   if(r == SQLITE_DONE && wantlastid)
  244     *lastid = sqlite3_last_insert_rowid(db);
  245   sqlite3_reset(s);
  246   if(nocache)
  247     sqlite3_finalize(s);
  248 
  249   return r;
  250 }
  251 
  252 
  253 static int db_queue_process_commit(sqlite3 *db) {
  254   g_debug("db: COMMIT");
  255   int r;
  256   sqlite3_stmt *s;
  257   if(db_queue_process_prepare(db, "COMMIT", &s))
  258     r = SQLITE_ERROR;
  259   else
  260     while((r = sqlite3_step(s)) == SQLITE_BUSY)
  261       ;
  262   if(r != SQLITE_DONE)
  263     g_critical("SQLite3 error committing transaction: %s", sqlite3_errmsg(db));
  264   sqlite3_reset(s);
  265   return r;
  266 }
  267 
  268 
  269 static int db_queue_process_begin(sqlite3 *db) {
  270   g_debug("db: BEGIN");
  271   int r;
  272   sqlite3_stmt *s;
  273   if(db_queue_process_prepare(db, "BEGIN", &s))
  274     r = SQLITE_ERROR;
  275   else
  276     r = sqlite3_step(s);
  277   if(r != SQLITE_DONE)
  278     g_critical("SQLite3 error starting transaction: %s", sqlite3_errmsg(db));
  279   sqlite3_reset(s);
  280   return r;
  281 }
  282 
  283 
  284 #define db_queue_process_rollback(db) do {\
  285     char *rollback_err = NULL;\
  286     g_debug("db: ROLLBACK");\
  287     if(sqlite3_exec(db, "ROLLBACK", NULL, NULL, &rollback_err) && rollback_err) {\
  288       g_debug("SQLite3 error rolling back transaction: %s", rollback_err);\
  289       sqlite3_free(rollback_err);\
  290     }\
  291   } while(0)
  292 
  293 
  294 static void db_queue_process(sqlite3 *db) {
  295   GTimeVal trans_end = {}; // tv_sec = 0 if no transaction is active
  296   gboolean donext = FALSE;
  297   gboolean errtrans = FALSE;
  298 
  299   GAsyncQueue *res;
  300   gint64 lastid;
  301   int r;
  302 
  303   while(1) {
  304     char *q =   donext ? g_async_queue_try_pop(db_queue) :
  305       trans_end.tv_sec ? g_async_queue_timed_pop(db_queue, &trans_end) :
  306                          g_async_queue_pop(db_queue);
  307 
  308     int flags = q ? darray_get_int32(q) : 0;
  309     gboolean nocache = flags & DBF_NOCACHE ? TRUE : FALSE;
  310 
  311     // Commit state if we need to
  312     if(!q || flags & DBF_SINGLE || flags & DBF_END) {
  313       g_warn_if_fail(!donext);
  314       if(trans_end.tv_sec)
  315         db_queue_process_commit(db);
  316       trans_end.tv_sec = 0;
  317       donext = errtrans = FALSE;
  318     }
  319 
  320     // If this was a timeout, wait for next query
  321     if(!q)
  322       continue;
  323 
  324     // if this is an END, quit.
  325     if(flags & DBF_END) {
  326       g_debug("db: Shutting down.");
  327       g_free(q);
  328       break;
  329     }
  330 
  331     // handle SINGLE
  332     if(flags & DBF_SINGLE) {
  333       r = db_queue_process_one(db, q, nocache, FALSE, &res, &lastid);
  334       db_queue_item_final(res, r, lastid);
  335       g_free(q);
  336       continue;
  337     }
  338 
  339     // report error to NEXT-chained queries if the transaction has been aborted.
  340     if(errtrans) {
  341       g_warn_if_fail(donext);
  342       db_queue_item_error(q);
  343       donext = flags & DBF_NEXT ? TRUE : FALSE;
  344       if(!donext) {
  345         errtrans = FALSE;
  346         trans_end.tv_sec = 0;
  347       }
  348       g_free(q);
  349       continue;
  350     }
  351 
  352     // handle LAST queries
  353     if(flags & DBF_LAST) {
  354       r = db_queue_process_one(db, q, nocache, trans_end.tv_sec?TRUE:FALSE, &res, &lastid);
  355       // Commit first, then send back the final result
  356       if(trans_end.tv_sec) {
  357         if(r == SQLITE_DONE)
  358           r = db_queue_process_commit(db);
  359         if(r != SQLITE_DONE)
  360           db_queue_process_rollback(db);
  361       }
  362       trans_end.tv_sec = 0;
  363       donext = FALSE;
  364       db_queue_item_final(res, r, lastid);
  365       g_free(q);
  366       continue;
  367     }
  368 
  369     // start a new transaction for normal/NEXT queries
  370     if(!trans_end.tv_sec) {
  371       g_get_current_time(&trans_end);
  372       g_time_val_add(&trans_end, DB_FLUSH_TIMEOUT);
  373       r = db_queue_process_begin(db);
  374       if(r != SQLITE_DONE) {
  375         if(flags & DBF_NEXT)
  376           donext = errtrans = TRUE;
  377         else
  378           trans_end.tv_sec = 0;
  379         db_queue_item_error(q);
  380         g_free(q);
  381         continue;
  382       }
  383     }
  384 
  385     // handle normal/NEXT queries
  386     r = db_queue_process_one(db, q, nocache, TRUE, &res, &lastid);
  387     db_queue_item_final(res, r, lastid);
  388     g_free(q);
  389 
  390     // Rollback and update state on error
  391     if(r != SQLITE_DONE) {
  392       db_queue_process_rollback(db);
  393       if(flags & DBF_NEXT)
  394         errtrans = TRUE;
  395       else
  396         trans_end.tv_sec = 0;
  397     }
  398   }
  399 }
  400 
  401 
  402 static void db_stmt_free(gpointer dat) { sqlite3_finalize(dat); }
  403 
  404 static gpointer db_thread_func(gpointer dat) {
  405   // Open database
  406   char *dbfn = dat;
  407   sqlite3 *db;
  408   if(sqlite3_open(dbfn, &db))
  409     g_error("Couldn't open `%s': %s", dbfn, sqlite3_errmsg(db));
  410   g_free(dbfn);
  411 
  412   sqlite3_busy_timeout(db, 10);
  413   sqlite3_exec(db, "PRAGMA foreign_keys = FALSE", NULL, NULL, NULL);
  414 
  415   // Create prepared statement cache and start handling queries
  416   db_stmt_cache = g_hash_table_new_full(g_direct_hash, g_direct_equal, NULL, db_stmt_free);
  417   db_queue_process(db);
  418   g_hash_table_unref(db_stmt_cache);
  419 
  420   // Close
  421   sqlite3_close(db);
  422   return NULL;
  423 }
  424 
  425 
  426 // Flushes the queue, blocks until all queries are processed and then performs
  427 // a little cleanup.
  428 void db_close() {
  429   // Send a END message to the database thread
  430   GByteArray *a = g_byte_array_new();
  431   darray_init(a);
  432   darray_add_int32(a, DBF_END);
  433   g_async_queue_push(db_queue, g_byte_array_free(a, FALSE));
  434   // And wait for it to quit
  435   g_thread_join(db_thread);
  436   g_async_queue_unref(db_queue);
  437   db_queue = NULL;
  438 }
  439 
  440 
  441 // The query is assumed to be a static string that is not freed or modified.
  442 static void *db_queue_item_create(int flags, const char *q, ...) {
  443   GByteArray *a = g_byte_array_new();
  444   darray_init(a);
  445   darray_add_int32(a, flags);
  446   darray_add_ptr(a, q);
  447 
  448   int t;
  449   char *p;
  450   va_list va;
  451   va_start(va, q);
  452   while((t = va_arg(va, int)) != DBQ_END && t != DBQ_RES) {
  453     switch(t) {
  454     case DBQ_NULL:
  455       darray_add_int32(a, DBQ_NULL);
  456       break;
  457     case DBQ_INT:
  458       darray_add_int32(a, DBQ_INT);
  459       darray_add_int32(a, va_arg(va, int));
  460       break;
  461     case DBQ_INT64:
  462       darray_add_int32(a, DBQ_INT64);
  463       darray_add_int64(a, va_arg(va, gint64));
  464       break;
  465     case DBQ_TEXT:
  466       p = va_arg(va, char *);
  467       if(p) {
  468         darray_add_int32(a, DBQ_TEXT);
  469         darray_add_string(a, p);
  470       } else
  471         darray_add_int32(a, DBQ_NULL);
  472       break;
  473     case DBQ_BLOB:
  474       t = va_arg(va, int);
  475       p = va_arg(va, char *);
  476       if(p) {
  477         darray_add_int32(a, DBQ_BLOB);
  478         darray_add_dat(a, p, t);
  479       } else
  480         darray_add_int32(a, DBQ_NULL);
  481       break;
  482     default:
  483       g_return_val_if_reached(NULL);
  484     }
  485   }
  486 
  487   if(t == DBQ_RES) {
  488     darray_add_int32(a, DBQ_RES);
  489     GAsyncQueue *queue = va_arg(va, GAsyncQueue *);
  490     g_async_queue_ref(queue);
  491     darray_add_ptr(a, queue);
  492     while((t = va_arg(va, int)) != DBQ_END)
  493       darray_add_int32(a, t);
  494   }
  495 
  496   va_end(va);
  497   darray_add_int32(a, DBQ_END);
  498 
  499   return g_byte_array_free(a, FALSE);
  500 }
  501 
  502 
  503 #define db_queue_lock() g_async_queue_lock(db_queue)
  504 #define db_queue_unlock() g_async_queue_unlock(db_queue)
  505 #define db_queue_push(...) g_async_queue_push(db_queue, db_queue_item_create(__VA_ARGS__))
  506 #define db_queue_push_unlocked(...) g_async_queue_push_unlocked(db_queue, db_queue_item_create(__VA_ARGS__))
  507 
  508 
  509 
  510 
  511 
  512 
  513 // hashdata and hashfiles
  514 
  515 // Adds a file to hashfiles and, if not present yet, hashdata. Returns the new hashfiles.id.
  516 gint64 db_fl_addhash(const char *path, guint64 size, time_t lastmod, const char *root, const char *tthl, int tthl_len) {
  517   char hash[40] = {};
  518   base32_encode(root, hash);
  519 
  520   db_queue_lock();
  521   db_queue_push_unlocked(DBF_NEXT,
  522     "INSERT OR IGNORE INTO hashdata (root, size, tthl) VALUES(?, ?, ?)",
  523     DBQ_TEXT, hash,
  524     DBQ_INT64, (gint64)size,
  525     DBQ_BLOB, tthl_len, tthl,
  526     DBQ_END
  527   );
  528 
  529   // hashfiles.
  530   // Note that it in certain situations it may happen that a row with the same
  531   // filename is already present. This happens when two files in the share have
  532   // the same realpath() (e.g. one is a symlink). In such a case it is safe to
  533   // just do a REPLACE.
  534   GAsyncQueue *a = g_async_queue_new_full(g_free);
  535   db_queue_push_unlocked(0,
  536     "INSERT OR REPLACE INTO hashfiles (tth, lastmod, filename) VALUES(?, ?, ?)",
  537     DBQ_TEXT, hash,
  538     DBQ_INT64, (gint64)lastmod,
  539     DBQ_TEXT, path,
  540     DBQ_RES, a, DBQ_LASTID,
  541     DBQ_END
  542   );
  543   db_queue_unlock();
  544 
  545   char *r = g_async_queue_pop(a);
  546   guint64 id = darray_get_int32(r) == SQLITE_DONE ? darray_get_int64(r) : 0;
  547   g_free(r);
  548   g_async_queue_unref(a);
  549   return id;
  550 }
  551 
  552 
  553 // Fetch the tthl data associated with a TTH root. Return value must be
  554 // g_free()'d. Returns NULL on error or when it's not in the DB.
  555 char *db_fl_gettthl(const char *root, int *len) {
  556   char hash[40] = {};
  557   base32_encode(root, hash);
  558 
  559   GAsyncQueue *a = g_async_queue_new_full(g_free);
  560   db_queue_push(0, "SELECT COALESCE(tthl, '') FROM hashdata WHERE root = ?",
  561     DBQ_TEXT, hash,
  562     DBQ_RES, a, DBQ_BLOB,
  563     DBQ_END
  564   );
  565 
  566   char *r = g_async_queue_pop(a);
  567   int n = 0;
  568   char *res = darray_get_int32(r) == SQLITE_ROW ? darray_get_dat(r, &n) : NULL;
  569   res = n ? g_memdup(res, n) : NULL;
  570   if(len)
  571     *len = n;
  572 
  573   g_free(r);
  574   g_async_queue_unref(a);
  575   return res;
  576 }
  577 
  578 
  579 // Get information for a file. Returns 0 if not found or error.
  580 gint64 db_fl_getfile(const char *path, time_t *lastmod, guint64 *size, char *tth) {
  581   GAsyncQueue *a = g_async_queue_new_full(g_free);
  582   db_queue_push(0,
  583     "SELECT f.id, f.lastmod, f.tth, d.size FROM hashfiles f JOIN hashdata d ON d.root = f.tth WHERE f.filename = ?",
  584     DBQ_TEXT, path,
  585     DBQ_RES, a, DBQ_INT64, DBQ_INT64, DBQ_TEXT, DBQ_INT64,
  586     DBQ_END
  587   );
  588 
  589   char *r = g_async_queue_pop(a);
  590   gint64 id = 0;
  591   if(darray_get_int32(r) == SQLITE_ROW) {
  592     id = darray_get_int64(r);
  593     *lastmod = darray_get_int64(r);
  594     base32_decode(darray_get_string(r), tth);
  595     *size = darray_get_int64(r);
  596   }
  597   g_free(r);
  598   g_async_queue_unref(a);
  599 
  600   return id;
  601 }
  602 
  603 
  604 // Batch-remove rows from hashfiles.
  605 // TODO: how/when to remove rows from hashdata for which no entry in hashfiles
  606 // exist? A /gc will do this by calling db_fl_purgedata(), but ideally this
  607 // would be done as soon as the hashdata row has become obsolete.
  608 void db_fl_rmfiles(gint64 *ids, int num) {
  609   int i;
  610   for(i=0; i<num; i++)
  611     db_queue_push(0, "DELETE FROM hashfiles WHERE id = ?", DBQ_INT64, ids[i], DBQ_END);
  612 }
  613 
  614 
  615 // Gets the full list of all ids in the hashfiles table, in ascending order.
  616 // *callback is called for every row.
  617 void db_fl_getids(void (*callback)(gint64)) {
  618   // This query is fast: `id' is the SQLite rowid, and has an index that is
  619   // already ordered.
  620   GAsyncQueue *a = g_async_queue_new_full(g_free);
  621   db_queue_push(0, "SELECT id FROM hashfiles ORDER BY id ASC",
  622     DBQ_RES, a, DBQ_INT64,
  623     DBQ_END
  624   );
  625 
  626   char *r;
  627   while((r = g_async_queue_pop(a)) && darray_get_int32(r) == SQLITE_ROW) {
  628     callback(darray_get_int64(r));
  629     g_free(r);
  630   }
  631   g_free(r);
  632   g_async_queue_unref(a);
  633 }
  634 
  635 
  636 // Remove rows from the hashdata table that are not referenced from the
  637 // hashfiles table.
  638 void db_fl_purgedata() {
  639   // For small databases, sqlite is clever enough to create a temporary
  640   // in-memory index on hashfiles(tth). But sometimes it doesn't, and then this
  641   // query takes an hour or longer to run. To be on the safe side, explicitely
  642   // create an index. This requires some extra disk space and makes a /gc run
  643   // longer on average, but it should guarantee that a /gc actually finishes
  644   // within a matter of minutes rather than hours.
  645   db_queue_lock();
  646   db_queue_push_unlocked(DBF_NEXT, "CREATE INDEX hashfiles_tth_gc ON hashfiles (tth)", DBQ_END);
  647   db_queue_push_unlocked(DBF_NEXT, "DELETE FROM hashdata WHERE NOT EXISTS(SELECT 1 FROM hashfiles WHERE tth = root)", DBQ_END);
  648   db_queue_push_unlocked(0, "DROP INDEX hashfiles_tth_gc", DBQ_END);
  649   db_queue_unlock();
  650 }
  651 
  652 
  653 
  654 
  655 
  656 // dl and dl_users
  657 
  658 
  659 // Fetches everything (except the raw TTHL data) from the dl table in no
  660 // particular order, calls the callback for each row.
  661 void db_dl_getdls(
  662   void (*callback)(const char *tth, guint64 size, const char *dest, signed char prio, char error, const char *error_msg, int tthllen)
  663 ) {
  664   GAsyncQueue *a = g_async_queue_new_full(g_free);
  665   db_queue_push(DBF_NOCACHE, "SELECT tth, size, dest, priority, error, COALESCE(error_msg, ''), length(tthl) FROM dl",
  666     DBQ_RES, a, DBQ_TEXT, DBQ_INT64, DBQ_TEXT, DBQ_INT, DBQ_INT, DBQ_TEXT, DBQ_INT,
  667     DBQ_END
  668   );
  669 
  670   char *r;
  671   while((r = g_async_queue_pop(a)) && darray_get_int32(r) == SQLITE_ROW) {
  672     char hash[24];
  673     base32_decode(darray_get_string(r), hash);
  674     guint64 size = darray_get_int64(r);
  675     char *dest = darray_get_string(r);
  676     signed char prio = darray_get_int32(r);
  677     char err = darray_get_int32(r);
  678     char *errmsg = darray_get_string(r);
  679     int tthllen = darray_get_int32(r);
  680     callback(hash, size, dest, prio, err, errmsg[0]?errmsg:NULL, tthllen);
  681     g_free(r);
  682   }
  683   g_free(r);
  684   g_async_queue_unref(a);
  685 }
  686 
  687 
  688 // Fetches everything from the dl_users table in no particular order, calls the
  689 // callback for each row.
  690 void db_dl_getdlus(void (*callback)(const char *tth, guint64 uid, char error, const char *error_msg)) {
  691   GAsyncQueue *a = g_async_queue_new_full(g_free);
  692   db_queue_push(DBF_NOCACHE, "SELECT tth, uid, error, COALESCE(error_msg, '') FROM dl_users",
  693     DBQ_RES, a, DBQ_TEXT, DBQ_INT64, DBQ_INT, DBQ_TEXT,
  694     DBQ_END
  695   );
  696 
  697   char *r;
  698   while((r = g_async_queue_pop(a)) && darray_get_int32(r) == SQLITE_ROW) {
  699     char hash[24];
  700     base32_decode(darray_get_string(r), hash);
  701     guint64 uid  = darray_get_int64(r);
  702     char err     = darray_get_int32(r);
  703     char *errmsg = darray_get_string(r);
  704     callback(hash, uid, err, errmsg[0] ? errmsg : NULL);
  705     g_free(r);
  706   }
  707   g_free(r);
  708   g_async_queue_unref(a);
  709 }
  710 
  711 
  712 // Delete a row from dl and any rows from dl_users that reference the row.
  713 void db_dl_rm(const char *tth) {
  714   char hash[40] = {};
  715   base32_encode(tth, hash);
  716 
  717   db_queue_lock();
  718   db_queue_push_unlocked(DBF_NEXT, "DELETE FROM dl_users WHERE tth = ?", DBQ_TEXT, hash, DBQ_END);
  719   db_queue_push_unlocked(0, "DELETE FROM dl WHERE tth = ?", DBQ_TEXT, hash, DBQ_END);
  720   db_queue_unlock();
  721 }
  722 
  723 
  724 // Set the priority, error and error_msg columns of a dl row
  725 void db_dl_setstatus(const char *tth, signed char priority, char error, const char *error_msg) {
  726   char hash[40] = {};
  727   base32_encode(tth, hash);
  728   db_queue_push(0, "UPDATE dl SET priority = ?, error = ?, error_msg = ? WHERE tth = ?",
  729     DBQ_INT, (int)priority, DBQ_INT, (int)error,
  730     DBQ_TEXT, error_msg,
  731     DBQ_TEXT, hash,
  732     DBQ_END
  733   );
  734 }
  735 
  736 
  737 // Set the error information for a dl_user row (if tth != NULL), or
  738 // all rows for a single user if tth = NULL.
  739 // TODO: tth = NULL is currently not very fast - no index on dl_user(uid).
  740 void db_dl_setuerr(guint64 uid, const char *tth, char error, const char *error_msg) {
  741   // for a single dl item
  742   if(tth) {
  743     char hash[40] = {};
  744     base32_encode(tth, hash);
  745     db_queue_push(0, "UPDATE dl_users SET error = ?, error_msg = ? WHERE uid = ? AND tth = ?",
  746       DBQ_INT, (int)error,
  747       DBQ_TEXT, error_msg,
  748       DBQ_INT64, (gint64)uid,
  749       DBQ_TEXT, hash,
  750       DBQ_END
  751     );
  752   // for all dl items
  753   } else {
  754     db_queue_push(0, "UPDATE dl_users SET error = ?, error_msg = ? WHERE uid = ?",
  755       DBQ_INT, (int)error,
  756       DBQ_TEXT, error_msg,
  757       DBQ_INT64, (gint64)uid,
  758       DBQ_END
  759     );
  760   }
  761 }
  762 
  763 
  764 // Remove a dl_user row from the database (if tth != NULL), or all
  765 // rows from a single user if tth = NULL. (Same note as for db_dl_setuerr()
  766 // applies here).
  767 void db_dl_rmuser(guint64 uid, const char *tth) {
  768   // for a single dl item
  769   if(tth) {
  770     char hash[40] = {};
  771     base32_encode(tth, hash);
  772     db_queue_push(0, "DELETE FROM dl_users WHERE uid = ? AND tth = ?",
  773       DBQ_INT64, (gint64)uid,
  774       DBQ_TEXT, hash,
  775       DBQ_END
  776     );
  777   // for all dl items
  778   } else {
  779     db_queue_push(0, "DELETE FROM dl_users WHERE uid = ?",
  780       DBQ_INT64, (gint64)uid,
  781       DBQ_END
  782     );
  783   }
  784 }
  785 
  786 
  787 // Sets the tthl column for a dl row.
  788 void db_dl_settthl(const char *tth, const char *tthl, int len) {
  789   char hash[40] = {};
  790   base32_encode(tth, hash);
  791   db_queue_push(0, "UPDATE dl SET tthl = ? WHERE tth = ?",
  792     DBQ_BLOB, len, tthl,
  793     DBQ_TEXT, hash,
  794     DBQ_END
  795   );
  796 }
  797 
  798 
  799 // Adds a new row to the dl table.
  800 void db_dl_insert(const char *tth, guint64 size, const char *dest, signed char priority, char error, const char *error_msg) {
  801   char hash[40] = {};
  802   base32_encode(tth, hash);
  803   db_queue_push(0, "INSERT OR REPLACE INTO dl (tth, size, dest, priority, error, error_msg) VALUES (?, ?, ?, ?, ?, ?)",
  804     DBQ_TEXT, hash,
  805     DBQ_INT64, (gint64)size,
  806     DBQ_TEXT, dest,
  807     DBQ_INT, (int)priority,
  808     DBQ_INT, (int)error,
  809     DBQ_TEXT, error_msg,
  810     DBQ_END
  811   );
  812 }
  813 
  814 
  815 // Adds a new row to the dl_users table.
  816 void db_dl_adduser(const char *tth, guint64 uid, char error, const char *error_msg) {
  817   char hash[40] = {};
  818   base32_encode(tth, hash);
  819   db_queue_push(0, "INSERT OR REPLACE INTO dl_users (tth, uid, error, error_msg) VALUES (?, ?, ?, ?)",
  820     DBQ_TEXT, hash,
  821     DBQ_INT64, (gint64)uid,
  822     DBQ_INT, (int)error,
  823     DBQ_TEXT, error_msg,
  824     DBQ_END
  825   );
  826 }
  827 
  828 
  829 gboolean db_dl_checkhash(const char *root, int num, const char *hash) {
  830   char rhash[40] = {};
  831   base32_encode(root, rhash);
  832   GAsyncQueue *a = g_async_queue_new_full(g_free);
  833   db_queue_push(0, "SELECT 1 FROM dl WHERE tth = ? AND substr(tthl, 1+(24*?), 24) = ?",
  834     DBQ_TEXT, rhash,
  835     DBQ_INT, num,
  836     DBQ_BLOB, 24, hash,
  837     DBQ_RES, a, DBQ_INT,
  838     DBQ_END
  839   );
  840 
  841   char *r = g_async_queue_pop(a);
  842   gboolean res = darray_get_int32(r) == SQLITE_ROW ? TRUE : FALSE;
  843   g_free(r);
  844   g_async_queue_unref(a);
  845   return res;
  846 }
  847 
  848 
  849 
  850 
  851 
  852 
  853 // The share table
  854 
  855 // The db_share* functions are NOT thread-safe, and must be accessed only from
  856 // the main thread. (This is because they do caching)
  857 
  858 #if INTERFACE
  859 struct db_share_item_t { char *name; char *path; };
  860 #endif
  861 
  862 static GArray *db_share_cache = NULL;
  863 
  864 
  865 // Returns a zero-terminated array of the shared directories. The array is
  866 // ordered by name. The array should not be freed, and may be modified by any
  867 // later call to a db_share_ function.
  868 db_share_item_t *db_share_list() {
  869   // Return cache
  870   if(db_share_cache)
  871     return (db_share_item_t *)db_share_cache->data;
  872 
  873   // Otherwise, create the cache
  874   db_share_cache = g_array_new(TRUE, FALSE, sizeof(db_share_item_t));
  875   GAsyncQueue *a = g_async_queue_new_full(g_free);
  876   db_queue_push(DBF_NOCACHE, "SELECT name, path FROM share ORDER BY name",
  877     DBQ_RES, a, DBQ_TEXT, DBQ_TEXT,
  878     DBQ_END
  879   );
  880 
  881   char *r;
  882   db_share_item_t i;
  883   while((r = g_async_queue_pop(a)) && darray_get_int32(r) == SQLITE_ROW) {
  884     i.name = g_strdup(darray_get_string(r));
  885     i.path = g_strdup(darray_get_string(r));
  886     g_array_append_val(db_share_cache, i);
  887     g_free(r);
  888   }
  889   g_free(r);
  890   g_async_queue_unref(a);
  891 
  892   return (db_share_item_t *)db_share_cache->data;
  893 }
  894 
  895 
  896 // Returns the path associated with a shared directory. The returned string
  897 // should not be freed, and may be modified by any later call to a db_share
  898 // function.
  899 const char *db_share_path(const char *name) {
  900   // The list is always ordered, so a binary search is possible and will be
  901   // more efficient than this linear search. I don't think anyone has enough
  902   // shared directories for that to matter, though.
  903   db_share_item_t *l = db_share_list();
  904   for(; l->name; l++)
  905     if(strcmp(name, l->name) == 0)
  906       return l->path;
  907   return NULL;
  908 }
  909 
  910 
  911 // Remove an item from the share. Use name = NULL to remove everything.
  912 void db_share_rm(const char *name) {
  913   // Remove all
  914   if(!name) {
  915     // Purge cache
  916     db_share_item_t *l = db_share_list();
  917     for(; l->name; l++) {
  918       g_free(l->name);
  919       g_free(l->path);
  920     }
  921     g_array_set_size(db_share_cache, 0);
  922 
  923     // Remove from the db
  924     db_queue_push(0, "DELETE FROM share", DBQ_END);
  925 
  926   // Remove one
  927   } else {
  928     // Remove from the cache
  929     db_share_item_t *l = db_share_list();
  930     int i;
  931     for(i=0; l->name; l++,i++) {
  932       if(strcmp(name, l->name) == 0) {
  933         g_free(l->name);
  934         g_free(l->path);
  935         g_array_remove_index(db_share_cache, i);
  936         break;
  937       }
  938     }
  939 
  940     // Remove from the db
  941     db_queue_push(0, "DELETE FROM share WHERE name = ?", DBQ_TEXT, name, DBQ_END);
  942   }
  943 }
  944 
  945 
  946 // Add an item to the share.
  947 void db_share_add(const char *name, const char *path) {
  948   // Add to the cache
  949   db_share_item_t new;
  950   new.name = g_strdup(name);
  951   new.path = g_strdup(path);
  952 
  953   db_share_item_t *l = db_share_list();
  954   int i;
  955   for(i=0; l->name; l++,i++)
  956     if(strcmp(l->name, name) > 0)
  957       break;
  958   g_array_insert_val(db_share_cache, i, new);
  959 
  960   // Add to the db
  961   db_queue_push(0, "INSERT INTO share (name, path) VALUES (?, ?)", DBQ_TEXT, name, DBQ_TEXT, path, DBQ_END);
  962 }
  963 
  964 
  965 
  966 
  967 
  968 // Vars table
  969 
  970 // As with db_share*, the db_vars* functions are NOT thread-safe, and must be
  971 // accessed only from the main thread.
  972 
  973 // Try to avoid using the db_vars_(get|set) functions directly. Use the
  974 // higher-level vars.c abstraction instead.
  975 
  976 typedef struct db_var_item_t { char *name; char *val; guint64 hub; } db_var_item_t;
  977 static GHashTable *db_vars_cache = NULL;
  978 
  979 
  980 // Hash, equal and free functions for the hash table
  981 static guint db_vars_cachehash(gconstpointer a) {
  982   const db_var_item_t *i = a;
  983   return g_str_hash(i->name) + g_int64_hash(&i->hub);
  984 }
  985 
  986 static gboolean db_vars_cacheeq(gconstpointer a, gconstpointer b) {
  987   const db_var_item_t *x = a;
  988   const db_var_item_t *y = b;
  989   return strcmp(x->name, y->name) == 0 && x->hub == y->hub ? TRUE : FALSE;
  990 }
  991 
  992 static void db_vars_cachefree(gpointer a) {
  993   db_var_item_t *i = a;
  994   g_free(i->name);
  995   g_free(i->val);
  996   g_slice_free(db_var_item_t, i);
  997 }
  998 
  999 
 1000 // Ensures db_vars_cache is initialized
 1001 static void db_vars_cacheget() {
 1002   if(db_vars_cache)
 1003     return;
 1004 
 1005   db_vars_cache = g_hash_table_new_full(db_vars_cachehash, db_vars_cacheeq, NULL, db_vars_cachefree);
 1006   GAsyncQueue *a = g_async_queue_new_full(g_free);
 1007   db_queue_push(DBF_NOCACHE, "SELECT name, hub, value FROM vars",
 1008     DBQ_RES, a, DBQ_TEXT, DBQ_INT64, DBQ_TEXT,
 1009     DBQ_END
 1010   );
 1011 
 1012   char *r;
 1013   while((r = g_async_queue_pop(a)) && darray_get_int32(r) == SQLITE_ROW) {
 1014     db_var_item_t *i = g_slice_new(db_var_item_t);
 1015     i->name = g_strdup(darray_get_string(r));
 1016     i->hub = darray_get_int64(r);
 1017     i->val = g_strdup(darray_get_string(r));
 1018     g_hash_table_insert(db_vars_cache, i, i);
 1019     g_free(r);
 1020   }
 1021   g_free(r);
 1022   g_async_queue_unref(a);
 1023 }
 1024 
 1025 
 1026 // Get a value from the vars table. The return value should not be modified or freed.
 1027 char *db_vars_get(guint64 hub, const char *name) {
 1028   db_vars_cacheget();
 1029   db_var_item_t i, *r;
 1030   i.name = (char *)name;
 1031   i.hub = hub;
 1032   r = g_hash_table_lookup(db_vars_cache, &i);
 1033   return r ? r->val : NULL;
 1034 }
 1035 
 1036 
 1037 // Unset a value (remove it)
 1038 void db_vars_rm(guint64 hub, const char *name) {
 1039   if(!db_vars_get(hub, name))
 1040     return;
 1041 
 1042   // Update cache
 1043   db_var_item_t i;
 1044   i.name = (char *)name;
 1045   i.hub = hub;
 1046   g_hash_table_remove(db_vars_cache, &i);
 1047 
 1048   // Update database
 1049   db_queue_push(0, "DELETE FROM vars WHERE name = ? AND hub = ?",
 1050     DBQ_TEXT, name, DBQ_INT64, hub, DBQ_END);
 1051 }
 1052 
 1053 
 1054 // Unset all values for a certain hubid.
 1055 void db_vars_rmhub(guint64 hub) {
 1056   g_return_if_fail(hub); // Not strictly an error, but not what this function was designed to do.
 1057   db_vars_cacheget();
 1058 
 1059   // Update cache
 1060   GHashTableIter i;
 1061   db_var_item_t *n;
 1062   g_hash_table_iter_init(&i, db_vars_cache);
 1063   while(g_hash_table_iter_next(&i, NULL, (gpointer *)&n))
 1064     if(n->hub == hub)
 1065       g_hash_table_iter_remove(&i);
 1066 
 1067   // Update database
 1068   db_queue_push(0, "DELETE FROM vars WHERE hub = ?", DBQ_INT64, hub, DBQ_END);
 1069 }
 1070 
 1071 
 1072 // Set a value. If val = NULL, then _rm() is called instead.
 1073 void db_vars_set(guint64 hub, const char *name, const char *val) {
 1074   if(!val) {
 1075     db_vars_rm(hub, name);
 1076     return;
 1077   }
 1078 
 1079   char *old = db_vars_get(hub, name);
 1080   if(old && strcmp(val, old) == 0)
 1081     return;
 1082 
 1083   // Update cache
 1084   db_var_item_t *i = g_slice_new(db_var_item_t);;
 1085   i->hub = hub;
 1086   i->name = g_strdup(name);
 1087   i->val = g_strdup(val);
 1088   g_hash_table_replace(db_vars_cache, i, i);
 1089 
 1090   // Update database
 1091   db_queue_push(0, "INSERT OR REPLACE INTO vars (name, hub, value) VALUES (?, ?, ?)",
 1092     DBQ_TEXT, name, DBQ_INT64, hub, DBQ_TEXT, val, DBQ_END);
 1093 }
 1094 
 1095 
 1096 // Get the hub id given the `hubname' variable. (linear search)
 1097 guint64 db_vars_hubid(const char *name) {
 1098   db_vars_cacheget();
 1099 
 1100   if(*name == '#')
 1101     name++;
 1102 
 1103   GHashTableIter i;
 1104   db_var_item_t *n;
 1105   g_hash_table_iter_init(&i, db_vars_cache);
 1106   while(g_hash_table_iter_next(&i, NULL, (gpointer *)&n))
 1107     if(strcmp(n->name, "hubname") == 0 && *n->val && strcmp(n->val+1, name) == 0)
 1108       return n->hub;
 1109   return 0;
 1110 }
 1111 
 1112 
 1113 // Get a sorted list of hub names. Should be freed with g_strfreev()
 1114 char **db_vars_hubs() {
 1115   db_vars_cacheget();
 1116 
 1117   GPtrArray *p = g_ptr_array_new();
 1118   GHashTableIter i;
 1119   db_var_item_t *n;
 1120   g_hash_table_iter_init(&i, db_vars_cache);
 1121   while(g_hash_table_iter_next(&i, NULL, (gpointer *)&n))
 1122     if(strcmp(n->name, "hubname") == 0)
 1123       g_ptr_array_add(p, g_strdup(n->val));
 1124   g_ptr_array_sort(p, cmpstringp);
 1125   g_ptr_array_add(p, NULL);
 1126   return (char **)g_ptr_array_free(p, FALSE);
 1127 }
 1128 
 1129 
 1130 
 1131 
 1132 
 1133 // Users table
 1134 // (not thread-safe)
 1135 
 1136 #if INTERFACE
 1137 
 1138 #define DB_USERFLAG_GRANT  1 // User has been granted a slot
 1139 
 1140 struct db_user_t {
 1141   guint64 hub;
 1142   guint64 uid; // Set, but not actually used. Matching is on the nick.
 1143   int flags;
 1144   char nick[1];
 1145 };
 1146 
 1147 #endif
 1148 
 1149 static GHashTable *db_users_cache = NULL;
 1150 
 1151 static guint db_user_hash(gconstpointer key) {
 1152   const db_user_t *u = key;
 1153   return g_str_hash(u->nick) + g_int64_hash(&u->hub);
 1154 }
 1155 
 1156 static gboolean db_user_equal(gconstpointer pa, gconstpointer pb) {
 1157   const db_user_t *a = pa;
 1158   const db_user_t *b = pb;
 1159   return a->hub == b->hub && strcmp(a->nick, b->nick) == 0 ? TRUE : FALSE;
 1160 }
 1161 
 1162 // For use with qsort()
 1163 static int db_users_cmp(const void *pa, const void *pb) {
 1164   db_user_t *a = *((db_user_t **)pa);
 1165   db_user_t *b = *((db_user_t **)pb);
 1166   int r = g_utf8_collate(a->nick, b->nick);
 1167   if(r == 0)
 1168     r = a->hub > b->hub ? 1 : a->hub < b->hub ? -1 : 0;
 1169   return r;
 1170 }
 1171 
 1172 static db_user_t *db_users_alloc(guint64 hub, guint64 uid, int flags, const char *nick) {
 1173   db_user_t *u = g_malloc(offsetof(db_user_t, nick) + strlen(nick) + 1);
 1174   u->hub = hub;
 1175   u->uid = uid;
 1176   u->flags = flags;
 1177   strcpy(u->nick, nick);
 1178   return u;
 1179 }
 1180 
 1181 static void db_users_cacheget() {
 1182   if(db_users_cache)
 1183     return;
 1184 
 1185   db_users_cache = g_hash_table_new_full(db_user_hash, db_user_equal, NULL, g_free);
 1186   GAsyncQueue *a = g_async_queue_new_full(g_free);
 1187   db_queue_push(DBF_NOCACHE, "SELECT hub, uid, flags, nick FROM users",
 1188     DBQ_RES, a, DBQ_INT64, DBQ_INT64, DBQ_INT, DBQ_TEXT,
 1189     DBQ_END
 1190   );
 1191 
 1192   char *r;
 1193   while((r = g_async_queue_pop(a)) && darray_get_int32(r) == SQLITE_ROW) {
 1194     guint64 hub = darray_get_int64(r);
 1195     guint64 uid = darray_get_int64(r);
 1196     int flags = darray_get_int32(r);
 1197     db_user_t *u = db_users_alloc(hub, uid, flags, darray_get_string(r));
 1198     g_hash_table_insert(db_users_cache, u, u);
 1199     g_free(r);
 1200   }
 1201   g_free(r);
 1202   g_async_queue_unref(a);
 1203 }
 1204 
 1205 // Returns the flags for a particular user, 0 if not in the DB
 1206 int db_users_get(guint64 hub, const char *nick) {
 1207   db_users_cacheget();
 1208   db_user_t *u = db_users_alloc(hub, 0, 0, nick);
 1209   db_user_t *r = g_hash_table_lookup(db_users_cache, u);
 1210   g_free(u);
 1211   return r ? r->flags : 0;
 1212 }
 1213 
 1214 
 1215 void db_users_rm(guint64 hub, const char *nick) {
 1216   if(!db_users_get(hub, nick))
 1217     return;
 1218 
 1219   db_user_t *u = db_users_alloc(hub, 0, 0, nick);
 1220   g_hash_table_remove(db_users_cache, u);
 1221   g_free(u);
 1222 
 1223   db_queue_push(0, "DELETE FROM users WHERE nick = ? AND hub = ?",
 1224     DBQ_TEXT, nick, DBQ_INT64, hub, DBQ_END);
 1225 }
 1226 
 1227 
 1228 // Set a value. If val = NULL, then _rm() is called instead.
 1229 void db_users_set(guint64 hub, guint64 uid, const char *nick, int flags) {
 1230   if(!flags) {
 1231     db_users_rm(hub, nick);
 1232     return;
 1233   }
 1234 
 1235   db_user_t *u = db_users_alloc(hub, uid, flags, nick);
 1236   g_hash_table_replace(db_users_cache, u, u);
 1237 
 1238   db_queue_push(0, "INSERT OR REPLACE INTO users (hub, uid, nick, flags) VALUES (?, ?, ?, ?)",
 1239     DBQ_INT64, hub, DBQ_INT64, uid, DBQ_TEXT, nick, DBQ_INT, flags, DBQ_END);
 1240 }
 1241 
 1242 
 1243 // Remove all user info of a particular
 1244 void db_users_rmhub(guint64 hub) {
 1245   db_users_cacheget();
 1246 
 1247   GHashTableIter i;
 1248   db_user_t *u;
 1249   g_hash_table_iter_init(&i, db_users_cache);
 1250   while(g_hash_table_iter_next(&i, NULL, (gpointer *)&u))
 1251     if(u->hub == hub)
 1252       g_hash_table_iter_remove(&i);
 1253 
 1254   db_queue_push(0, "DELETE FROM users WHERE hub = ?", DBQ_INT64, hub, DBQ_END);
 1255 }
 1256 
 1257 
 1258 // Get an ordered list (username, hubid) of users. The array must be g_free()'d
 1259 // after use, but the elements shouldn't.
 1260 db_user_t **db_users_list() {
 1261   db_users_cacheget();
 1262 
 1263   db_user_t **list = g_new(db_user_t *, g_hash_table_size(db_users_cache)+1);
 1264   db_user_t *u;
 1265   GHashTableIter i;
 1266   g_hash_table_iter_init(&i, db_users_cache);
 1267   int n = 0;
 1268   while(g_hash_table_iter_next(&i, NULL, (gpointer *)&u))
 1269     list[n++] = u;
 1270   list[n] = NULL;
 1271   qsort(list, n, sizeof(db_user_t *), db_users_cmp);
 1272   return list;
 1273 }
 1274 
 1275 
 1276 
 1277 
 1278 
 1279 
 1280 // Initialize the database directory and other stuff
 1281 
 1282 const char *db_dir = NULL;
 1283 
 1284 gnutls_certificate_credentials_t db_certificate;
 1285 
 1286 // Base32-encoded keyprint of our own certificate
 1287 char *db_certificate_kp = NULL;
 1288 
 1289 
 1290 static const char *cert_gen(const char *cert_file, const char *key_file, gnutls_x509_crt_t cert, gnutls_x509_privkey_t key) {
 1291   unsigned char dat[32*1024];
 1292   size_t len;
 1293   FILE *f;
 1294 
 1295   printf("Generating certificates...");
 1296   fflush(stdout);
 1297 
 1298   // Make sure either both exists or none exists
 1299   unlink(cert_file);
 1300   unlink(key_file);
 1301 
 1302   // Private key
 1303   int bits = 2432;
 1304 #if GNUTLS_VERSION_MAJOR > 2 || (GNUTLS_VERSION_MAJOR == 2 && GNUTLS_VERSION_MINOR >= 12)
 1305   bits = gnutls_sec_param_to_pk_bits(GNUTLS_PK_RSA, GNUTLS_SEC_PARAM_NORMAL);
 1306 #endif
 1307   gnutls_x509_privkey_generate(key, GNUTLS_PK_RSA, bits, 0);
 1308   len = sizeof(dat);
 1309   g_assert(gnutls_x509_privkey_export(key, GNUTLS_X509_FMT_PEM, dat, &len) == 0);
 1310   if(!(f = fopen(key_file, "w"))
 1311       || fwrite(dat, 1, len, f) != len
 1312       || fclose(f))
 1313     return g_strerror(errno);
 1314 
 1315   // Certificate (self-signed)
 1316   time_t t = time(NULL);
 1317   gnutls_x509_crt_set_dn_by_oid(cert, GNUTLS_OID_X520_ORGANIZATION_NAME,        0, "Unknown", strlen("Unknown"));
 1318   gnutls_x509_crt_set_dn_by_oid(cert, GNUTLS_OID_X520_ORGANIZATIONAL_UNIT_NAME, 0, "Unknown", strlen("Unknown"));
 1319   gnutls_x509_crt_set_dn_by_oid(cert, GNUTLS_OID_X520_COMMON_NAME,              0, "Unknown", strlen("Unknown"));
 1320   gnutls_x509_crt_set_dn_by_oid(cert, GNUTLS_OID_X520_LOCALITY_NAME,            0, "Unknown", strlen("Unknown"));
 1321   gnutls_x509_crt_set_dn_by_oid(cert, GNUTLS_OID_X520_STATE_OR_PROVINCE_NAME,   0, "Unknown", strlen("Unknown"));
 1322   gnutls_x509_crt_set_dn_by_oid(cert, GNUTLS_OID_X520_COUNTRY_NAME,             0, "UN", strlen("UN"));
 1323   gnutls_x509_crt_set_key(cert, key);
 1324   gnutls_x509_crt_set_serial(cert, &t, sizeof(t));
 1325   gnutls_x509_crt_set_activation_time(cert, t-(24*3600));
 1326   gnutls_x509_crt_set_expiration_time(cert, t+(3560*24*3600));
 1327   gnutls_x509_crt_sign(cert, cert, key);
 1328   len = sizeof(dat);
 1329   g_assert(gnutls_x509_crt_export(cert, GNUTLS_X509_FMT_PEM, dat, &len) == 0);
 1330   if(!(f = fopen(cert_file, "w"))
 1331       || fwrite(dat, 1, len, f) != len
 1332       || fclose(f))
 1333     return g_strerror(errno);
 1334 
 1335   return NULL;
 1336 }
 1337 
 1338 
 1339 // Convenience function based on
 1340 // http://www.gnu.org/software/gnutls/manual/html_node/Using-a-callback-to-select-the-certificate-to-use.html
 1341 static gnutls_datum_t load_file(const char *file, const char **err) {
 1342   FILE *f;
 1343   gnutls_datum_t d = { NULL, 0 };
 1344   long len;
 1345   void *ptr = NULL;
 1346 
 1347   if (!(f = fopen(file, "r"))
 1348       || fseek(f, 0, SEEK_END) != 0
 1349       || (len = ftell(f)) < 0
 1350       || fseek(f, 0, SEEK_SET) != 0
 1351       || !(ptr = g_malloc((size_t)len))
 1352       || fread(ptr, 1, (size_t)len, f) < (size_t)len)
 1353     *err = g_strerror(errno);
 1354   if(f)
 1355     fclose(f);
 1356   if(*err && ptr)
 1357     g_free(ptr);
 1358   if(!*err) {
 1359     d.data = ptr;
 1360     d.size = (unsigned int)len;
 1361   }
 1362   return d;
 1363 }
 1364 
 1365 
 1366 static const char *cert_load(const char *cert_file, const char *key_file, gnutls_x509_crt_t cert, gnutls_x509_privkey_t key) {
 1367   const char *err = NULL;
 1368   // Load cert
 1369   int n;
 1370   gnutls_datum_t crtdat = load_file(cert_file, &err);
 1371   if(err)
 1372     return err;
 1373 
 1374   if((n = gnutls_x509_crt_import(cert, &crtdat, GNUTLS_X509_FMT_PEM)) < 0)
 1375     return gnutls_strerror(n);
 1376   g_free(crtdat.data);
 1377 
 1378   // Load key
 1379   gnutls_datum_t keydat = load_file(key_file, &err);
 1380   if(err)
 1381     return err;
 1382 
 1383   if((n = gnutls_x509_privkey_import(key, &keydat, GNUTLS_X509_FMT_PEM)) < 0)
 1384     return gnutls_strerror(n);
 1385   g_free(keydat.data);
 1386 
 1387   return NULL;
 1388 }
 1389 
 1390 
 1391 static void cert_init() {
 1392   char *cert_file = g_build_filename(db_dir, "cert", "client.crt", NULL);
 1393   char *key_file = g_build_filename(db_dir, "cert", "client.key", NULL);
 1394 
 1395   gnutls_x509_crt_t cert;
 1396   gnutls_x509_privkey_t key;
 1397   gnutls_x509_crt_init(&cert);
 1398   gnutls_x509_privkey_init(&key);
 1399 
 1400   const char *err = NULL;
 1401   if(g_file_test(cert_file, G_FILE_TEST_EXISTS) && g_file_test(key_file, G_FILE_TEST_EXISTS))
 1402     err = cert_load(cert_file, key_file, cert, key);
 1403   else
 1404     err = cert_gen(cert_file, key_file, cert, key);
 1405 
 1406   if(err) {
 1407     printf(
 1408       "ERROR: Could not load the client certificate files.\n"
 1409       "  %s\n\n"
 1410       "Please check that a valid client certificate is stored in the following two files:\n"
 1411       "  %s\n  %s\n"
 1412       "Or remove the files to automatically generate a new certificate.\n",
 1413       err, cert_file, key_file);
 1414     exit(1);
 1415   }
 1416 
 1417   // Set credentials
 1418   gnutls_certificate_allocate_credentials(&db_certificate);
 1419   gnutls_certificate_set_x509_key(db_certificate, &cert, 1, key);
 1420 
 1421   // Generate keyprint
 1422   size_t len = 8*1024; // should be enough
 1423   unsigned char crtder[len];
 1424   char raw[32];
 1425   g_assert(gnutls_x509_crt_export(cert, GNUTLS_X509_FMT_DER, (void *)crtder, &len) == 0);
 1426   gnutls_datum_t dat;
 1427   dat.data = crtder;
 1428   dat.size = len;
 1429   db_certificate_kp = g_malloc0(53);
 1430   certificate_sha256(dat, raw);
 1431   base32_encode_dat(raw, db_certificate_kp, 32);
 1432 
 1433   gnutls_x509_crt_deinit(cert);
 1434   gnutls_x509_privkey_deinit(key);
 1435   g_free(cert_file);
 1436   g_free(key_file);
 1437 }
 1438 
 1439 
 1440 // Checks or creates the initial session directory, including subdirectories
 1441 // and the version/lock file. Returns the database version. (major<<8 + minor)
 1442 static int db_dir_init() {
 1443   // Get location of the session directory. It may already have been set in main.c
 1444   if(!db_dir && (db_dir = g_getenv("NCDC_DIR")))
 1445     db_dir = g_strdup(db_dir);
 1446   if(!db_dir)
 1447     db_dir = g_build_filename(g_get_home_dir(), ".ncdc", NULL);
 1448 
 1449   // try to create it (ignoring errors if it already exists)
 1450   g_mkdir(db_dir, 0700);
 1451   if(g_access(db_dir, F_OK | R_OK | X_OK | W_OK) < 0)
 1452     g_error("Directory '%s' does not exist or is not writable.", db_dir);
 1453 
 1454   // Make sure it's an absolute path (yes, after mkdir'ing it, path_expand()
 1455   // may return an error if it doesn't exist). Just stick with the relative
 1456   // path if this fails, it's not critical anyway.
 1457   char *real = path_expand(db_dir);
 1458   if(real) {
 1459     g_free((char *)db_dir);
 1460     db_dir = real;
 1461   }
 1462 
 1463   // make sure some subdirectories exist and are writable
 1464 #define cdir(d) do {\
 1465     char *tmp = g_build_filename(db_dir, d, NULL);\
 1466     g_mkdir(tmp, 0777);\
 1467     if(g_access(db_dir, F_OK | R_OK | X_OK | W_OK) < 0)\
 1468       g_error("Directory '%s' does not exist or is not writable.", tmp);\
 1469     g_free(tmp);\
 1470   } while(0)
 1471   cdir("logs");
 1472   cdir("inc");
 1473   cdir("fl");
 1474   cdir("dl");
 1475   cdir("cert");
 1476 #undef cdir
 1477 
 1478   // make sure that there is no other ncdc instance working with the same config directory
 1479   char *ver_file = g_build_filename(db_dir, "version", NULL);
 1480   int ver_fd = g_open(ver_file, O_RDWR|O_CREAT, 0600);
 1481   struct flock lck;
 1482   lck.l_type = F_WRLCK;
 1483   lck.l_whence = SEEK_SET;
 1484   lck.l_start = 0;
 1485   lck.l_len = 0;
 1486   if(ver_fd < 0 || fcntl(ver_fd, F_SETLK, &lck) == -1)
 1487     g_error("Unable to open lock file. Is another instance of ncdc running with the same configuration directory?");
 1488 
 1489   // check data directory version
 1490   // version = major, minor
 1491   //   minor = forward & backward compatible, major only backward.
 1492   char dir_ver[2] = {2, 0};
 1493   if(read(ver_fd, dir_ver, 2) < 2)
 1494     if(write(ver_fd, dir_ver, 2) < 2)
 1495       g_error("Could not write to '%s': %s", ver_file, g_strerror(errno));
 1496   g_free(ver_file);
 1497   // Don't close the above file. Keep it open and let the OS close it (and free
 1498   // the lock) when ncdc is closed, was killed or has crashed.
 1499 
 1500   return (((int)dir_ver[0])<<8) + (int)dir_ver[1];
 1501 }
 1502 
 1503 
 1504 #define DB_USERS_TABLE \
 1505   "CREATE TABLE users ("\
 1506   "  hub INTEGER NOT NULL,"\
 1507   "  uid INTEGER NOT NULL,"\
 1508   "  nick TEXT NOT NULL,"\
 1509   "  flags INTEGER NOT NULL"\
 1510   ")"
 1511 
 1512 
 1513 static void db_init_schema() {
 1514   // Get user_version
 1515   GAsyncQueue *a = g_async_queue_new_full(g_free);
 1516   db_queue_push(DBF_SINGLE|DBF_NOCACHE, "PRAGMA user_version", DBQ_RES, a, DBQ_INT, DBQ_END);
 1517 
 1518   char *r = g_async_queue_pop(a);
 1519   int ver;
 1520   if(darray_get_int32(r) == SQLITE_ROW)
 1521     ver = darray_get_int32(r);
 1522   else
 1523     g_error("Unable to get database version.");
 1524   g_free(r);
 1525   g_async_queue_unref(a);
 1526 
 1527   // New database? Initialize schema.
 1528   if(ver == 0) {
 1529     db_queue_lock();
 1530     db_queue_push_unlocked(DBF_NEXT|DBF_NOCACHE, "PRAGMA user_version = 2", DBQ_END);
 1531     db_queue_push_unlocked(DBF_NEXT|DBF_NOCACHE,
 1532       "CREATE TABLE hashdata ("
 1533       "  root TEXT NOT NULL PRIMARY KEY,"
 1534       "  size INTEGER NOT NULL,"
 1535       "  tthl BLOB NOT NULL"
 1536       ")", DBQ_END);
 1537     db_queue_push_unlocked(DBF_NEXT|DBF_NOCACHE,
 1538       "CREATE TABLE hashfiles ("
 1539       "  id INTEGER PRIMARY KEY,"
 1540       "  filename TEXT NOT NULL UNIQUE,"
 1541       "  tth TEXT NOT NULL,"
 1542       "  lastmod INTEGER NOT NULL"
 1543       ")", DBQ_END);
 1544     db_queue_push_unlocked(DBF_NEXT|DBF_NOCACHE,
 1545       "CREATE TABLE dl ("
 1546       "  tth TEXT NOT NULL PRIMARY KEY,"
 1547       "  size INTEGER NOT NULL,"
 1548       "  dest TEXT NOT NULL,"
 1549       "  priority INTEGER NOT NULL DEFAULT 0,"
 1550       "  error INTEGER NOT NULL DEFAULT 0,"
 1551       "  error_msg TEXT,"
 1552       "  tthl BLOB"
 1553       ")", DBQ_END);
 1554     db_queue_push_unlocked(DBF_NEXT|DBF_NOCACHE,
 1555       "CREATE TABLE dl_users ("
 1556       "  tth TEXT NOT NULL,"
 1557       "  uid INTEGER NOT NULL,"
 1558       "  error INTEGER NOT NULL DEFAULT 0,"
 1559       "  error_msg TEXT,"
 1560       "  PRIMARY KEY(tth, uid)"
 1561       ")", DBQ_END);
 1562     db_queue_push_unlocked(DBF_NEXT|DBF_NOCACHE,
 1563       "CREATE TABLE share ("
 1564       "  name TEXT NOT NULL PRIMARY KEY,"
 1565       "  path TEXT NOT NULL"
 1566       ")", DBQ_END);
 1567     db_queue_push_unlocked(DBF_NEXT|DBF_NOCACHE, DB_USERS_TABLE, DBQ_END);
 1568     // Get a result from the last one, to make sure the above queries were successful.
 1569     GAsyncQueue *a = g_async_queue_new_full(g_free);
 1570     db_queue_push_unlocked(DBF_LAST|DBF_NOCACHE,
 1571       "CREATE TABLE vars ("
 1572       "  name TEXT NOT NULL,"
 1573       "  hub INTEGER NOT NULL DEFAULT 0,"
 1574       "  value TEXT NOT NULL,"
 1575       "  PRIMARY KEY(name, hub)"
 1576       ")", DBQ_RES, a, DBQ_END);
 1577     db_queue_unlock();
 1578     char *r = g_async_queue_pop(a);
 1579     if(darray_get_int32(r) != SQLITE_DONE)
 1580       g_error("Error creating database schema.");
 1581     g_free(r);
 1582     g_async_queue_unref(a);
 1583   }
 1584 
 1585   // Version 1 didn't have the users table
 1586   if(ver == 1) {
 1587     db_queue_lock();
 1588     GAsyncQueue *a = g_async_queue_new_full(g_free);
 1589     db_queue_push_unlocked(DBF_NEXT|DBF_NOCACHE, "PRAGMA user_version = 2", DBQ_END);
 1590     db_queue_push_unlocked(DBF_LAST|DBF_NOCACHE, DB_USERS_TABLE, DBQ_RES, a, DBQ_END);
 1591     db_queue_unlock();
 1592     char *r = g_async_queue_pop(a);
 1593     if(darray_get_int32(r) != SQLITE_DONE)
 1594       g_error("Error updating database schema.");
 1595     g_free(r);
 1596     g_async_queue_unref(a);
 1597   }
 1598 }
 1599 
 1600 
 1601 void db_init() {
 1602   int ver = db_dir_init();
 1603 
 1604   if(ver>>8 < 2)
 1605     g_error("Database version too old. Please delete the directory to start from scratch, or run the ncdc-db-upgrade utility available with ncdc 1.13 and earlier.");
 1606   if(ver>>8 > 2)
 1607     g_error("Incompatible database version. You may want to upgrade ncdc.");
 1608 
 1609   // load client certificate
 1610   cert_init();
 1611 
 1612   // start database thread
 1613   db_queue = g_async_queue_new();
 1614   db_thread = g_thread_create(db_thread_func, g_build_filename(db_dir, "db.sqlite3", NULL), TRUE, NULL);
 1615 
 1616   db_init_schema();
 1617 }
 1618 
 1619 
 1620 
 1621 
 1622 // Executes a VACUUM
 1623 void db_vacuum() {
 1624   db_queue_push(DBF_SINGLE|DBF_NOCACHE, "VACUUM", DBQ_END);
 1625 }