"Fossies" - the Fresh Open Source Software Archive

Member "vnstat-2.9/src/dbsql.c" (1 Jan 2022, 36785 Bytes) of package /linux/misc/vnstat-2.9.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 "dbsql.c" see the Fossies "Dox" file reference documentation and the latest Fossies "Diffs" side-by-side code changes report: 2.8_vs_2.9.

A hint: This file contains one or more very long lines, so maybe it is better readable using the pure text view mode that shows the contents as wrapped lines within the browser window.

    1 #include "common.h"
    2 #include "misc.h"
    3 #include "iflist.h"
    4 #include "dbsql.h"
    5 
    6 /* global db */
    7 sqlite3 *db;
    8 int db_errcode;
    9 int db_intransaction;
   10 
   11 int db_open_ro(void)
   12 {
   13     return db_open(0, 1);
   14 }
   15 
   16 int db_open_rw(const int createifnotfound)
   17 {
   18     return db_open(createifnotfound, 0);
   19 }
   20 
   21 int db_open(const int createifnotfound, const int readonly)
   22 {
   23     int rc, createdb = 0;
   24     char dbfilename[530];
   25 
   26 #ifdef CHECK_VNSTAT
   27     /* use ram based database when testing for shorter test execution times by reducing disk i/o */
   28     snprintf(dbfilename, 530, ":memory:");
   29     createdb = 1;
   30 #else
   31     struct stat filestat;
   32 
   33     if (db != NULL) {
   34         return 1;
   35     }
   36 
   37     snprintf(dbfilename, 530, "%s/%s", cfg.dbdir, DATABASEFILE);
   38 
   39     /* create database if file doesn't exist */
   40     if (stat(dbfilename, &filestat) != 0) {
   41         if (errno == ENOENT && createifnotfound && !readonly) {
   42             createdb = 1;
   43         } else {
   44             if (debug)
   45                 printf("Error (debug): Handling database \"%s\" failed: %s\n", dbfilename, strerror(errno));
   46             return 0;
   47         }
   48     } else {
   49         if (filestat.st_size == 0) {
   50             if (createifnotfound) {
   51                 createdb = 1;
   52             } else {
   53                 printf("Error: Database \"%s\" contains 0 bytes and isn't a valid database, exiting.\n", dbfilename);
   54                 exit(EXIT_FAILURE);
   55             }
   56         }
   57     }
   58 #endif
   59     db_errcode = 0;
   60     db_intransaction = 0;
   61     if (readonly) {
   62         rc = sqlite3_open_v2(dbfilename, &db, SQLITE_OPEN_READONLY, NULL);
   63     } else {
   64         rc = sqlite3_open_v2(dbfilename, &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL);
   65     }
   66 
   67     if (rc) {
   68         db_errcode = rc;
   69         if (debug)
   70             printf("Error (debug): Can't open database \"%s\": %s\n", dbfilename, sqlite3_errmsg(db));
   71         return 0;
   72     } else {
   73         if (debug)
   74             printf("Database \"%s\" open (ro: %d)\n", dbfilename, readonly);
   75     }
   76 
   77     if (createdb) {
   78 #ifndef CHECK_VNSTAT
   79         if (!spacecheck(cfg.dbdir)) {
   80             printf("Error: Not enough free diskspace available in \"%s\", exiting.\n", cfg.dbdir);
   81             db_close();
   82             exit(EXIT_FAILURE);
   83         }
   84 #endif
   85         if (!db_create()) {
   86             if (debug)
   87                 printf("Error (debug): Creating database \"%s\" structure failed\n", dbfilename);
   88             db_close();
   89             return 0;
   90         } else {
   91             if (debug)
   92                 printf("Database \"%s\" structure created\n", dbfilename);
   93             if (!db_setinfo("dbversion", SQLDBVERSION, 1)) {
   94                 if (debug)
   95                     printf("Error (debug): Writing version info to database \"%s\" failed\n", dbfilename);
   96                 db_close();
   97                 return 0;
   98             }
   99         }
  100     }
  101 
  102     /* set pragmas */
  103     if (!readonly) {
  104         sqlite3_busy_timeout(db, cfg.updateinterval * 1000);
  105         if (!db_setpragmas()) {
  106             db_close();
  107             return 0;
  108         }
  109     } else {
  110         /* set busy timeout when database is open in read-only mode */
  111         sqlite3_busy_timeout(db, DBREADTIMEOUTSECS * 1000);
  112     }
  113 
  114     if (!createdb) {
  115         if (!db_validate(readonly)) {
  116             db_close();
  117             return 0;
  118         }
  119     }
  120 
  121     if (createifnotfound && !readonly) {
  122         if (!db_setinfo("vnstatversion", getversion(), 1)) {
  123             db_close();
  124             return 0;
  125         }
  126     }
  127 
  128     return 1;
  129 }
  130 
  131 int db_validate(const int readonly)
  132 {
  133     int dbversion, currentversion;
  134 
  135     db_errcode = 0;
  136     dbversion = atoi(db_getinfo("dbversion"));
  137     if (db_errcode) {
  138         return 0;
  139     }
  140 
  141     currentversion = atoi(SQLDBVERSION);
  142 
  143     if (debug) {
  144         printf("Database version \"%d\", current version \"%d\"\n", dbversion, currentversion);
  145     }
  146 
  147     if (dbversion == currentversion) {
  148         return 1;
  149 
  150     } else if (dbversion == 0) {
  151         printf("Error: Database version \"%d\" suggests error situation in database, exiting.\n", dbversion);
  152         return 0;
  153 
  154     } else if (dbversion > currentversion) {
  155         printf("Error: Database version \"%d\" is not supported. Support is available up to version \"%d\", exiting.\n", dbversion, currentversion);
  156         return 0;
  157 
  158     } else if (dbversion < currentversion) {
  159         if (readonly) {
  160             /* database upgrade actions should be performed here once needed */
  161             printf("Error: Unable to upgrade read-only database from version \"%d\" to \"%d\", exiting.\n", dbversion, currentversion);
  162             return 0;
  163         }
  164         /* database upgrade actions should be performed here once needed, then return 1 */
  165         /* however, since this is the first database version, always return 0 */
  166     }
  167 
  168     return 0;
  169 }
  170 
  171 int db_setpragmas(void)
  172 {
  173     int rc;
  174     char sql[25];
  175     sqlite3_stmt *sqlstmt;
  176 
  177     /* enable use of foreign keys */
  178     if (!db_exec("PRAGMA foreign_keys = ON")) {
  179         return 0;
  180     }
  181 
  182     rc = sqlite3_prepare_v2(db, "PRAGMA foreign_keys", -1, &sqlstmt, NULL);
  183     if (rc != SQLITE_OK) {
  184         db_errcode = rc;
  185         snprintf(errorstring, 1024, "Exec prepare \"PRAGMA foreign_keys;\" failed (%d): %s", rc, sqlite3_errmsg(db));
  186         printe(PT_Error);
  187         return 0;
  188     }
  189 
  190     /* PRAGMA foreign_keys; is expected to return one row if the feature is supported */
  191     rc = sqlite3_step(sqlstmt);
  192     if (rc != SQLITE_ROW) {
  193         db_errcode = rc;
  194         snprintf(errorstring, 1024, "PRAGMA foreign_keys returned no row (%d): %s", rc, sqlite3_errmsg(db));
  195         printe(PT_Error);
  196         sqlite3_finalize(sqlstmt);
  197         return 0;
  198     }
  199 
  200     rc = sqlite3_finalize(sqlstmt);
  201     if (rc) {
  202         db_errcode = rc;
  203         snprintf(errorstring, 1024, "Exec finalize \"PRAGMA foreign_keys;\" failed (%d): %s", rc, sqlite3_errmsg(db));
  204         printe(PT_Error);
  205         return 0;
  206     }
  207 
  208 #if HAVE_DECL_SQLITE_CHECKPOINT_RESTART
  209     /* set journal_mode */
  210     if (cfg.waldb) {
  211         if (!db_exec("PRAGMA journal_mode = WAL")) {
  212             return 0;
  213         }
  214     } else {
  215         if (!db_exec("PRAGMA journal_mode = DELETE")) {
  216             return 0;
  217         }
  218     }
  219 #endif
  220 
  221     /* set synchronous */
  222     if (cfg.dbsynchronous == -1) {
  223 #if HAVE_DECL_SQLITE_CHECKPOINT_RESTART
  224         if (cfg.waldb) {
  225             if (!db_exec("PRAGMA synchronous = 1")) {
  226                 return 0;
  227             }
  228         } else {
  229             if (!db_exec("PRAGMA synchronous = 2")) {
  230                 return 0;
  231             }
  232         }
  233 #else
  234         if (!db_exec("PRAGMA synchronous = 2")) {
  235             return 0;
  236         }
  237 #endif
  238     } else {
  239         snprintf(sql, 25, "PRAGMA synchronous = %d", cfg.dbsynchronous);
  240         if (!db_exec(sql)) {
  241             return 0;
  242         }
  243     }
  244 
  245     return 1;
  246 }
  247 
  248 int db_close(void)
  249 {
  250     int rc;
  251 
  252     if (db == NULL) {
  253         return 1;
  254     }
  255 
  256     rc = sqlite3_close(db);
  257     if (rc == SQLITE_OK) {
  258         db = NULL;
  259         if (debug)
  260             printf("Database closed\n");
  261         return 1;
  262     } else {
  263         db_errcode = rc;
  264         if (debug)
  265             printf("Error (debug): Closing database failed (%d): %s\n", rc, sqlite3_errmsg(db));
  266         return 0;
  267     }
  268 }
  269 
  270 int db_exec(const char *sql)
  271 {
  272     int rc;
  273     sqlite3_stmt *sqlstmt;
  274 
  275     rc = sqlite3_prepare_v2(db, sql, -1, &sqlstmt, NULL);
  276     if (rc != SQLITE_OK) {
  277         db_errcode = rc;
  278         snprintf(errorstring, 1024, "Exec prepare failed (%d: %s): \"%s\"", rc, sqlite3_errmsg(db), sql);
  279         printe(PT_Error);
  280         return 0;
  281     }
  282 
  283     rc = sqlite3_step(sqlstmt);
  284     if (rc != SQLITE_DONE && rc != SQLITE_ROW) {
  285         db_errcode = rc;
  286         snprintf(errorstring, 1024, "Exec step failed (%d: %s): \"%s\"", rc, sqlite3_errmsg(db), sql);
  287         printe(PT_Error);
  288         sqlite3_finalize(sqlstmt);
  289         return 0;
  290     }
  291 
  292     rc = sqlite3_finalize(sqlstmt);
  293     if (rc) {
  294         db_errcode = rc;
  295         snprintf(errorstring, 1024, "Exec finalize failed (%d: %s): \"%s\"", rc, sqlite3_errmsg(db), sql);
  296         printe(PT_Error);
  297         return 0;
  298     }
  299 
  300     return 1;
  301 }
  302 
  303 int db_create(void)
  304 {
  305     int i;
  306     const char *constsql;
  307     char *sql;
  308     char buffer[32];
  309     const char *datatables[] = {"fiveminute", "hour", "day", "month", "year", "top"};
  310 
  311     if (!db_begintransaction()) {
  312         return 0;
  313     }
  314 
  315     constsql = "CREATE TABLE info(\n"
  316           "  id       INTEGER PRIMARY KEY,\n"
  317           "  name     TEXT UNIQUE NOT NULL,\n"
  318           "  value    TEXT NOT NULL)";
  319 
  320     if (!db_exec(constsql)) {
  321         db_rollbacktransaction();
  322         return 0;
  323     }
  324 
  325     constsql = "CREATE TABLE interface(\n"
  326           "  id           INTEGER PRIMARY KEY,\n"
  327           "  name         TEXT UNIQUE NOT NULL,\n"
  328           "  alias        TEXT,\n"
  329           "  active       INTEGER NOT NULL,\n"
  330           "  created      DATE NOT NULL,\n"
  331           "  updated      DATE NOT NULL,\n"
  332           "  rxcounter    INTEGER NOT NULL,\n"
  333           "  txcounter    INTEGER NOT NULL,\n"
  334           "  rxtotal      INTEGER NOT NULL,\n"
  335           "  txtotal      INTEGER NOT NULL)";
  336 
  337     if (!db_exec(constsql)) {
  338         db_rollbacktransaction();
  339         return 0;
  340     }
  341 
  342     sql = malloc(sizeof(char) * 512);
  343     for (i = 0; i < 6; i++) {
  344         sqlite3_snprintf(512, sql, "CREATE TABLE %s(\n"
  345                                    "  id           INTEGER PRIMARY KEY,\n"
  346                                    "  interface    INTEGER REFERENCES interface(id) ON DELETE CASCADE,\n"
  347                                    "  date         DATE NOT NULL,\n"
  348                                    "  rx           INTEGER NOT NULL,\n"
  349                                    "  tx           INTEGER NOT NULL,\n"
  350                                    "  CONSTRAINT u UNIQUE (interface, date))",
  351                          datatables[i]);
  352 
  353         if (!db_exec(sql)) {
  354             free(sql);
  355             db_rollbacktransaction();
  356             return 0;
  357         }
  358     }
  359     free(sql);
  360 
  361     snprintf(buffer, 32, "%" PRIu64 "", (uint64_t)MAX32);
  362     if (!db_setinfo("btime", buffer, 1)) {
  363         db_rollbacktransaction();
  364         return 0;
  365     }
  366 
  367     return db_committransaction();
  368 }
  369 
  370 int db_addinterface(const char *iface)
  371 {
  372     char sql[256];
  373 
  374     if (!strlen(iface)) {
  375         return 0;
  376     }
  377 
  378     sqlite3_snprintf(256, sql, "insert into interface (name, active, created, updated, rxcounter, txcounter, rxtotal, txtotal) values ('%q', 1, datetime('now'%s), datetime('now'%s), 0, 0, 0, 0)", iface, cfg.dbtzmodifier, cfg.dbtzmodifier);
  379     return db_exec(sql);
  380 }
  381 
  382 int db_removeinterface(const char *iface)
  383 {
  384     char sql[64];
  385     sqlite3_int64 ifaceid = 0;
  386 
  387     ifaceid = db_getinterfaceid(iface, 0);
  388     if (ifaceid == 0) {
  389         return 0;
  390     }
  391 
  392     sqlite3_snprintf(64, sql, "delete from interface where id=%" PRId64 "", (int64_t)ifaceid);
  393     return db_exec(sql);
  394 }
  395 
  396 int db_renameinterface(const char *iface, const char *newifname)
  397 {
  398     char sql[128];
  399     sqlite3_int64 ifaceid = 0;
  400 
  401     if (!strlen(newifname)) {
  402         return 0;
  403     }
  404 
  405     ifaceid = db_getinterfaceid(iface, 0);
  406     if (ifaceid == 0) {
  407         return 0;
  408     }
  409 
  410     sqlite3_snprintf(128, sql, "update interface set name='%q' where id=%" PRId64 "", newifname, (int64_t)ifaceid);
  411     return db_exec(sql);
  412 }
  413 
  414 uint64_t db_getinterfacecount(void)
  415 {
  416     return db_getinterfacecountbyname("");
  417 }
  418 
  419 uint64_t db_getinterfacecountbyname(const char *iface)
  420 {
  421     int rc;
  422     uint64_t result = 0;
  423     char sql[128], *inquery = NULL;
  424     sqlite3_stmt *sqlstmt;
  425 
  426     if (strchr(iface, '+') == NULL) {
  427         if (strlen(iface) > 0) {
  428             sqlite3_snprintf(128, sql, "select count(*) from interface where name='%q'", iface);
  429         } else {
  430             sqlite3_snprintf(128, sql, "select count(*) from interface");
  431         }
  432     } else {
  433         inquery = getifaceinquery(iface);
  434         if (inquery == NULL) {
  435             return 0;
  436         }
  437         sqlite3_snprintf(128, sql, "select count(*) from interface where name in (%q)", inquery);
  438         free(inquery);
  439     }
  440 
  441     rc = sqlite3_prepare_v2(db, sql, -1, &sqlstmt, NULL);
  442     if (rc != SQLITE_OK) {
  443         db_errcode = rc;
  444         snprintf(errorstring, 1024, "Failed to get interface count from database (%d): %s", rc, sqlite3_errmsg(db));
  445         printe(PT_Error);
  446         return 0;
  447     }
  448     if (sqlite3_column_count(sqlstmt) != 1) {
  449         return 0;
  450     }
  451     if (sqlite3_step(sqlstmt) == SQLITE_ROW) {
  452         result = (uint64_t)sqlite3_column_int64(sqlstmt, 0);
  453     }
  454     sqlite3_finalize(sqlstmt);
  455 
  456     /* consider merge query as invalid if not all requested interfaces are found or are not unique */
  457     if (strchr(iface, '+') != NULL) {
  458         if (result != getqueryinterfacecount(iface)) {
  459             result = 0;
  460         }
  461     }
  462 
  463     return result;
  464 }
  465 
  466 sqlite3_int64 db_getinterfaceid(const char *iface, const int createifnotfound)
  467 {
  468     int rc;
  469     char sql[128];
  470     sqlite3_int64 ifaceid = 0;
  471     sqlite3_stmt *sqlstmt;
  472 
  473     sqlite3_snprintf(128, sql, "select id from interface where name='%q'", iface);
  474     rc = sqlite3_prepare_v2(db, sql, -1, &sqlstmt, NULL);
  475     if (rc == SQLITE_OK) {
  476         if (sqlite3_step(sqlstmt) == SQLITE_ROW) {
  477             ifaceid = sqlite3_column_int64(sqlstmt, 0);
  478         }
  479         sqlite3_finalize(sqlstmt);
  480     } else {
  481         db_errcode = rc;
  482         snprintf(errorstring, 1024, "Failed to get interface id from database (%d): %s", rc, sqlite3_errmsg(db));
  483         printe(PT_Error);
  484     }
  485 
  486     if (ifaceid == 0 && createifnotfound) {
  487         if (!db_addinterface(iface)) {
  488             return 0;
  489         }
  490         ifaceid = sqlite3_last_insert_rowid(db);
  491     }
  492 
  493     return ifaceid;
  494 }
  495 
  496 char *db_getinterfaceidin(const char *iface)
  497 {
  498     int rc;
  499     char sql[256], *result, *inquery;
  500     sqlite3_stmt *sqlstmt;
  501 
  502     result = NULL;
  503     inquery = getifaceinquery(iface);
  504     if (inquery == NULL) {
  505         return NULL;
  506     }
  507 
  508     sqlite3_snprintf(256, sql, "select group_concat(id) from interface where name in (%q)", inquery);
  509     free(inquery);
  510     rc = sqlite3_prepare_v2(db, sql, -1, &sqlstmt, NULL);
  511     if (rc == SQLITE_OK) {
  512         if (sqlite3_step(sqlstmt) == SQLITE_ROW) {
  513             if (sqlite3_column_text(sqlstmt, 0) != NULL) {
  514                 result = strdup((const char *)sqlite3_column_text(sqlstmt, 0));
  515             }
  516         }
  517         sqlite3_finalize(sqlstmt);
  518     } else {
  519         db_errcode = rc;
  520         snprintf(errorstring, 1024, "Failed to get interface id from database (%d): %s", rc, sqlite3_errmsg(db));
  521         printe(PT_Error);
  522     }
  523 
  524     return result;
  525 }
  526 
  527 int db_setinterfacebyalias(char *iface, const char *alias, const int matchmethod)
  528 {
  529     int rc;
  530     char sql[256];
  531     sqlite3_stmt *sqlstmt;
  532 
  533     switch (matchmethod) {
  534         // case sensitive
  535         case 1:
  536             sqlite3_snprintf(256, sql, "select name from interface where alias='%q' order by rxtotal+txtotal desc", alias);
  537             break;
  538         // case insensitive
  539         case 2:
  540             sqlite3_snprintf(256, sql, "select name from interface where alias='%q' collate nocase order by rxtotal+txtotal desc", alias);
  541             break;
  542         // case insensitive prefix
  543         case 3:
  544             sqlite3_snprintf(256, sql, "select name from interface where alias like '%q%%' collate nocase order by rxtotal+txtotal desc", alias);
  545             break;
  546         default:
  547             return 0;
  548     }
  549 
  550     rc = sqlite3_prepare_v2(db, sql, -1, &sqlstmt, NULL);
  551     if (rc != SQLITE_OK) {
  552         db_errcode = rc;
  553         snprintf(errorstring, 1024, "Failed to get interface alias from database (%d): %s", rc, sqlite3_errmsg(db));
  554         printe(PT_Error);
  555         return 0;
  556     }
  557 
  558     if (sqlite3_column_count(sqlstmt) != 1) {
  559         return 0;
  560     }
  561 
  562     rc = 0;
  563     if (sqlite3_step(sqlstmt) == SQLITE_ROW) {
  564         if (sqlite3_column_text(sqlstmt, 0) != NULL) {
  565             strncpy_nt(iface, (const char *)sqlite3_column_text(sqlstmt, 0), 32);
  566             rc++;
  567         }
  568     }
  569     sqlite3_finalize(sqlstmt);
  570 
  571     return rc;
  572 }
  573 
  574 int db_setactive(const char *iface, const int active)
  575 {
  576     char sql[64];
  577     sqlite3_int64 ifaceid = 0;
  578 
  579     ifaceid = db_getinterfaceid(iface, 0);
  580     if (ifaceid == 0) {
  581         return 0;
  582     }
  583 
  584     sqlite3_snprintf(64, sql, "update interface set active=%d where id=%" PRId64 "", active, (int64_t)ifaceid);
  585     return db_exec(sql);
  586 }
  587 
  588 int db_setupdated(const char *iface, const time_t timestamp)
  589 {
  590     char sql[256];
  591     sqlite3_int64 ifaceid = 0;
  592 
  593     ifaceid = db_getinterfaceid(iface, 0);
  594     if (ifaceid == 0) {
  595         return 0;
  596     }
  597 
  598     sqlite3_snprintf(256, sql, "update interface set updated=datetime(%" PRIu64 ", 'unixepoch'%s) where id=%" PRId64 "", (uint64_t)timestamp, cfg.dbtzmodifier, (int64_t)ifaceid);
  599     return db_exec(sql);
  600 }
  601 
  602 int db_setcounters(const char *iface, const uint64_t rxcounter, const uint64_t txcounter)
  603 {
  604     char sql[256];
  605     sqlite3_int64 ifaceid = 0;
  606 
  607     ifaceid = db_getinterfaceid(iface, 0);
  608     if (ifaceid == 0) {
  609         return 0;
  610     }
  611 
  612     sqlite3_snprintf(256, sql, "update interface set rxcounter=%" PRIu64 ", txcounter=%" PRIu64 " where id=%" PRId64 "", rxcounter, txcounter, (int64_t)ifaceid);
  613     return db_exec(sql);
  614 }
  615 
  616 int db_getcounters(const char *iface, uint64_t *rxcounter, uint64_t *txcounter)
  617 {
  618     int rc;
  619     char sql[128];
  620     sqlite3_int64 ifaceid = 0;
  621     sqlite3_stmt *sqlstmt;
  622 
  623     *rxcounter = *txcounter = 0;
  624 
  625     ifaceid = db_getinterfaceid(iface, 0);
  626     if (ifaceid == 0) {
  627         return 0;
  628     }
  629 
  630     sqlite3_snprintf(128, sql, "select rxcounter, txcounter from interface where id=%" PRId64 "", (int64_t)ifaceid);
  631     rc = sqlite3_prepare_v2(db, sql, -1, &sqlstmt, NULL);
  632     if (rc != SQLITE_OK) {
  633         db_errcode = rc;
  634         snprintf(errorstring, 1024, "Failed to get interface counters from database (%d): %s", rc, sqlite3_errmsg(db));
  635         printe(PT_Error);
  636         return 0;
  637     }
  638     if (sqlite3_column_count(sqlstmt) != 2) {
  639         sqlite3_finalize(sqlstmt);
  640         return 0;
  641     }
  642     if (sqlite3_step(sqlstmt) == SQLITE_ROW) {
  643         *rxcounter = (uint64_t)sqlite3_column_int64(sqlstmt, 0);
  644         *txcounter = (uint64_t)sqlite3_column_int64(sqlstmt, 1);
  645     }
  646     sqlite3_finalize(sqlstmt);
  647 
  648     return 1;
  649 }
  650 
  651 int db_getinterfaceinfo(const char *iface, interfaceinfo *info)
  652 {
  653     int rc;
  654     char sql[512], *ifaceidin = NULL;
  655     sqlite3_int64 ifaceid;
  656     sqlite3_stmt *sqlstmt;
  657 
  658     if (strchr(iface, '+') == NULL) {
  659         ifaceid = db_getinterfaceid(iface, 0);
  660         if (ifaceid == 0) {
  661             return 0;
  662         }
  663         sqlite3_snprintf(512, sql, "select name, alias, active, strftime('%%s', created, 'utc'), strftime('%%s', updated, 'utc'), rxcounter, txcounter, rxtotal, txtotal from interface where id=%" PRId64 "", (int64_t)ifaceid);
  664     } else {
  665         ifaceidin = db_getinterfaceidin(iface);
  666         if (ifaceidin == NULL || strlen(ifaceidin) < 1) {
  667             free(ifaceidin);
  668             return 0;
  669         }
  670         sqlite3_snprintf(512, sql, "select \"%q\", NULL, max(active), max(strftime('%%s', created, 'utc')), min(strftime('%%s', updated, 'utc')), 0, 0, sum(rxtotal), sum(txtotal) from interface where id in (%q)", iface, ifaceidin);
  671         free(ifaceidin);
  672     }
  673 
  674     rc = sqlite3_prepare_v2(db, sql, -1, &sqlstmt, NULL);
  675     if (rc != SQLITE_OK) {
  676         db_errcode = rc;
  677         snprintf(errorstring, 1024, "Failed to get interface information from database (%d): %s", rc, sqlite3_errmsg(db));
  678         printe(PT_Error);
  679         return 0;
  680     }
  681     if (sqlite3_column_count(sqlstmt) != 9) {
  682         return 0;
  683     }
  684     if (sqlite3_step(sqlstmt) == SQLITE_ROW) {
  685         if (sqlite3_column_text(sqlstmt, 0) != NULL) {
  686             strncpy_nt(info->name, (const char *)sqlite3_column_text(sqlstmt, 0), 32);
  687         } else {
  688             info->name[0] = '\0';
  689         }
  690         if (sqlite3_column_text(sqlstmt, 1) != NULL) {
  691             strncpy_nt(info->alias, (const char *)sqlite3_column_text(sqlstmt, 1), 32);
  692         } else {
  693             info->alias[0] = '\0';
  694         }
  695         info->active = sqlite3_column_int(sqlstmt, 2);
  696         info->created = (time_t)sqlite3_column_int64(sqlstmt, 3);
  697         info->updated = (time_t)sqlite3_column_int64(sqlstmt, 4);
  698         info->rxcounter = (uint64_t)sqlite3_column_int64(sqlstmt, 5);
  699         info->txcounter = (uint64_t)sqlite3_column_int64(sqlstmt, 6);
  700         info->rxtotal = (uint64_t)sqlite3_column_int64(sqlstmt, 7);
  701         info->txtotal = (uint64_t)sqlite3_column_int64(sqlstmt, 8);
  702     }
  703     sqlite3_finalize(sqlstmt);
  704 
  705     return 1;
  706 }
  707 
  708 int db_setalias(const char *iface, const char *alias)
  709 {
  710     char sql[128];
  711     sqlite3_int64 ifaceid = 0;
  712 
  713     ifaceid = db_getinterfaceid(iface, 0);
  714     if (ifaceid == 0) {
  715         return 0;
  716     }
  717 
  718     sqlite3_snprintf(128, sql, "update interface set alias='%q' where id=%" PRId64 "", alias, (int64_t)ifaceid);
  719     return db_exec(sql);
  720 }
  721 
  722 int db_setinfo(const char *name, const char *value, const int createifnotfound)
  723 {
  724     int rc;
  725     char sql[128];
  726 
  727     sqlite3_snprintf(128, sql, "update info set value='%q' where name='%q'", value, name);
  728     rc = db_exec(sql);
  729     if (!rc || (!sqlite3_changes(db) && !createifnotfound)) {
  730         return 0;
  731     }
  732     if (!sqlite3_changes(db) && createifnotfound) {
  733         sqlite3_snprintf(512, sql, "insert into info (name, value) values ('%q', '%q')", name, value);
  734         rc = db_exec(sql);
  735     }
  736     return rc;
  737 }
  738 
  739 char *db_getinfo(const char *name)
  740 {
  741     int rc;
  742     char sql[128];
  743     static char buffer[64];
  744     sqlite3_stmt *sqlstmt;
  745 
  746     buffer[0] = '\0';
  747 
  748     sqlite3_snprintf(128, sql, "select value from info where name='%q'", name);
  749     rc = sqlite3_prepare_v2(db, sql, -1, &sqlstmt, NULL);
  750     if (rc != SQLITE_OK) {
  751         db_errcode = rc;
  752         snprintf(errorstring, 1024, "Failed to get info value for \"%s\" from database (%d): %s", name, rc, sqlite3_errmsg(db));
  753         printe(PT_Error);
  754         return buffer;
  755     }
  756     if (sqlite3_step(sqlstmt) == SQLITE_ROW) {
  757         if (sqlite3_column_text(sqlstmt, 0) != NULL) {
  758             strncpy_nt(buffer, (const char *)sqlite3_column_text(sqlstmt, 0), 64);
  759         }
  760     }
  761     sqlite3_finalize(sqlstmt);
  762 
  763     return buffer;
  764 }
  765 
  766 int db_getiflist(iflist **ifl)
  767 {
  768     return db_getiflist_sorted(ifl, 0);
  769 }
  770 
  771 int db_getiflist_sorted(iflist **ifl, const int orderbytraffic)
  772 {
  773     int rc;
  774     const char *constsql;
  775     sqlite3_stmt *sqlstmt;
  776 
  777     if (!orderbytraffic) {
  778         constsql = "select name from interface order by name asc";
  779     } else {
  780         constsql = "select name from interface order by rxtotal+txtotal desc";
  781     }
  782 
  783     rc = sqlite3_prepare_v2(db, constsql, -1, &sqlstmt, NULL);
  784     if (rc != SQLITE_OK) {
  785         db_errcode = rc;
  786         snprintf(errorstring, 1024, "Failed to get interface list from database (%d): %s", rc, sqlite3_errmsg(db));
  787         printe(PT_Error);
  788         return -1;
  789     }
  790 
  791     rc = 0;
  792     while (sqlite3_step(sqlstmt) == SQLITE_ROW) {
  793         if (sqlite3_column_text(sqlstmt, 0) == NULL) {
  794             continue;
  795         }
  796         if (!iflistadd(ifl, (const char *)sqlite3_column_text(sqlstmt, 0), 0)) {
  797             break;
  798         }
  799         rc++;
  800     }
  801 
  802     sqlite3_finalize(sqlstmt);
  803 
  804     return rc;
  805 }
  806 
  807 char *db_get_date_generator(const int range, const short direct, const char *nowdate)
  808 {
  809     static char dgen[512];
  810     dgen[0] = '\0';
  811 
  812     switch (range) {
  813         case 0: /* 5min */
  814             snprintf(dgen, 512, "datetime(%s, ('-' || (strftime('%%M', %s)) || ' minutes'), ('-' || (strftime('%%S', %s)) || ' seconds'), ('+' || (round(strftime('%%M', %s)/5,0)*5) || ' minutes')%s)", nowdate, nowdate, nowdate, nowdate, cfg.dbtzmodifier);
  815             break;
  816         case 1: /* hour */
  817             snprintf(dgen, 512, "strftime('%%Y-%%m-%%d %%H:00:00', %s%s)", nowdate, cfg.dbtzmodifier);
  818             break;
  819         case 2: /* day */
  820         case 5: /* top */
  821             snprintf(dgen, 512, "date(%s%s)", nowdate, cfg.dbtzmodifier);
  822             break;
  823         case 3: /* month */
  824             if (direct || cfg.monthrotate == 1) {
  825                 snprintf(dgen, 512, "strftime('%%Y-%%m-01', %s%s)", nowdate, cfg.dbtzmodifier);
  826             } else {
  827                 snprintf(dgen, 512, "strftime('%%Y-%%m-01', datetime(%s, '-%d days')%s)", nowdate, cfg.monthrotate - 1, cfg.dbtzmodifier);
  828             }
  829             break;
  830         case 4: /* year */
  831             if (direct || cfg.monthrotate == 1 || cfg.monthrotateyears == 0) {
  832                 snprintf(dgen, 512, "strftime('%%Y-01-01', %s%s)", nowdate, cfg.dbtzmodifier);
  833             } else {
  834                 snprintf(dgen, 512, "strftime('%%Y-01-01', datetime(%s, '-%d days')%s)", nowdate, cfg.monthrotate - 1, cfg.dbtzmodifier);
  835             }
  836             break;
  837         default:
  838             break;
  839     }
  840     return dgen;
  841 }
  842 
  843 int db_addtraffic(const char *iface, const uint64_t rx, const uint64_t tx)
  844 {
  845     return db_addtraffic_dated(iface, rx, tx, 0);
  846 }
  847 
  848 int db_addtraffic_dated(const char *iface, const uint64_t rx, const uint64_t tx, const uint64_t timestamp)
  849 {
  850     int i, intransaction = db_intransaction;
  851     char sql[1024], nowdate[64];
  852     sqlite3_int64 ifaceid = 0;
  853 
  854     const char *datatables[] = {"fiveminute", "hour", "day", "month", "year", "top"};
  855     int32_t *featurecfg[] = {&cfg.fiveminutehours, &cfg.hourlydays, &cfg.dailydays, &cfg.monthlymonths, &cfg.yearlyyears, &cfg.topdayentries};
  856 
  857     ifaceid = db_getinterfaceid(iface, 1);
  858     if (ifaceid == 0) {
  859         return 0;
  860     }
  861 
  862     if (timestamp > 0) {
  863         snprintf(nowdate, 64, "datetime(%" PRIu64 ", 'unixepoch')", timestamp);
  864     } else {
  865         snprintf(nowdate, 64, "'now'");
  866     }
  867 
  868     if (debug)
  869         printf("db add %s (%" PRId64 ") %" PRIu64 ": rx %" PRIu64 " - tx %" PRIu64 "\n", iface, (int64_t)ifaceid, timestamp, rx, tx);
  870 
  871     if (!intransaction) {
  872         if (!db_begintransaction()) {
  873             return 0;
  874         }
  875     }
  876 
  877     /* total */
  878     if (rx > 0 || tx > 0) {
  879         sqlite3_snprintf(1024, sql, "update interface set rxtotal=rxtotal+%" PRIu64 ", txtotal=txtotal+%" PRIu64 " where id=%" PRId64 "", rx, tx, (int64_t)ifaceid);
  880         if (!db_exec(sql)) {
  881             db_rollbacktransaction();
  882             return 0;
  883         }
  884     }
  885 
  886     /* time specific */
  887     for (i = 0; i < 6; i++) {
  888         if (featurecfg[i] == 0) {
  889             continue;
  890         }
  891         sqlite3_snprintf(1024, sql, "insert or ignore into %s (interface, date, rx, tx) values (%" PRId64 ", %s, 0, 0)", datatables[i], (int64_t)ifaceid, db_get_date_generator(i, 0, nowdate));
  892         if (!db_exec(sql)) {
  893             db_rollbacktransaction();
  894             return 0;
  895         }
  896         sqlite3_snprintf(1024, sql, "update %s set rx=rx+%" PRIu64 ", tx=tx+%" PRIu64 " where interface=%" PRId64 " and date=%s", datatables[i], rx, tx, (int64_t)ifaceid, db_get_date_generator(i, 0, nowdate));
  897         if (!db_exec(sql)) {
  898             db_rollbacktransaction();
  899             return 0;
  900         }
  901     }
  902 
  903     if (!intransaction) {
  904         return db_committransaction();
  905     }
  906     return 1;
  907 }
  908 
  909 int db_setcreation(const char *iface, const time_t timestamp)
  910 {
  911     char sql[256];
  912     sqlite3_int64 ifaceid = 0;
  913 
  914     ifaceid = db_getinterfaceid(iface, 0);
  915     if (ifaceid == 0) {
  916         return 0;
  917     }
  918 
  919     sqlite3_snprintf(256, sql, "update interface set created=datetime(%" PRIu64 ", 'unixepoch'%s) where id=%" PRId64 "", (uint64_t)timestamp, cfg.dbtzmodifier, (int64_t)ifaceid);
  920     return db_exec(sql);
  921 }
  922 
  923 int db_settotal(const char *iface, const uint64_t rx, const uint64_t tx)
  924 {
  925     char sql[256];
  926     sqlite3_int64 ifaceid = 0;
  927 
  928     ifaceid = db_getinterfaceid(iface, 0);
  929     if (ifaceid == 0) {
  930         return 0;
  931     }
  932 
  933     sqlite3_snprintf(256, sql, "update interface set rxtotal=%" PRIu64 ", txtotal=%" PRIu64 " where id=%" PRId64 "", rx, tx, (int64_t)ifaceid);
  934     return db_exec(sql);
  935 }
  936 
  937 /* used only for legacy data import */
  938 int db_insertdata(const char *table, const char *iface, const uint64_t rx, const uint64_t tx, const uint64_t timestamp)
  939 {
  940     int i, index = -1;
  941     char sql[1024], nowdate[64];
  942     sqlite3_int64 ifaceid = 0;
  943 
  944     const char *datatables[] = {"hour", "day", "month", "year", "top"};
  945 
  946     for (i = 0; i < 5; i++) {
  947         if (strcmp(table, datatables[i]) == 0) {
  948             index = i;
  949             break;
  950         }
  951     }
  952 
  953     if (index == -1) {
  954         return 0;
  955     }
  956 
  957     ifaceid = db_getinterfaceid(iface, 0);
  958     if (ifaceid == 0) {
  959         return 0;
  960     }
  961 
  962     /* legacy data is always local timezone and needs to be enforced here as a result */
  963     if (cfg.useutc) {
  964         snprintf(nowdate, 64, "datetime(%" PRIu64 ", 'unixepoch', 'localtime')", timestamp);
  965     } else {
  966         snprintf(nowdate, 64, "datetime(%" PRIu64 ", 'unixepoch')", timestamp);
  967     }
  968 
  969     sqlite3_snprintf(1024, sql, "insert or ignore into %s (interface, date, rx, tx) values (%" PRId64 ", %s, %" PRIu64 ", %" PRIu64 ")", table, (int64_t)ifaceid, db_get_date_generator(index + 1, 1, nowdate), rx, tx);
  970     return db_exec(sql);
  971 }
  972 
  973 int db_removeoldentries(void)
  974 {
  975     char sql[256];
  976 
  977     if (debug) {
  978         printf("db: removing old entries\n");
  979     }
  980 
  981     if (!db_begintransaction()) {
  982         return 0;
  983     }
  984 
  985     if (!db_removeoldentries_top()) {
  986         db_rollbacktransaction();
  987         return 0;
  988     }
  989 
  990     if (cfg.fiveminutehours > 0) {
  991         if (debug) {
  992             printf("db: fiveminute cleanup (%dh)\n", cfg.fiveminutehours);
  993         }
  994         sqlite3_snprintf(256, sql, "delete from fiveminute where date < datetime('now', '-%d hours'%s)", cfg.fiveminutehours, cfg.dbtzmodifier);
  995         if (!db_exec(sql)) {
  996             db_rollbacktransaction();
  997             return 0;
  998         }
  999     }
 1000 
 1001     if (cfg.hourlydays > 0) {
 1002         if (debug) {
 1003             printf("db: hour cleanup (%dd)\n", cfg.hourlydays);
 1004         }
 1005         sqlite3_snprintf(256, sql, "delete from hour where date < datetime('now', '-%d days'%s)", cfg.hourlydays, cfg.dbtzmodifier);
 1006         if (!db_exec(sql)) {
 1007             db_rollbacktransaction();
 1008             return 0;
 1009         }
 1010     }
 1011 
 1012     if (cfg.dailydays > 0) {
 1013         if (debug) {
 1014             printf("db: day cleanup (%dd)\n", cfg.dailydays);
 1015         }
 1016         sqlite3_snprintf(256, sql, "delete from day where date < date('now', '-%d days'%s)", cfg.dailydays, cfg.dbtzmodifier);
 1017         if (!db_exec(sql)) {
 1018             db_rollbacktransaction();
 1019             return 0;
 1020         }
 1021     }
 1022 
 1023     if (cfg.monthlymonths > 0) {
 1024         if (debug) {
 1025             printf("db: month cleanup (%dm)\n", cfg.monthlymonths);
 1026         }
 1027         sqlite3_snprintf(256, sql, "delete from month where date < date('now', '-%d months'%s)", cfg.monthlymonths, cfg.dbtzmodifier);
 1028         if (!db_exec(sql)) {
 1029             db_rollbacktransaction();
 1030             return 0;
 1031         }
 1032     }
 1033 
 1034     if (cfg.yearlyyears > 0) {
 1035         if (debug) {
 1036             printf("db: year cleanup (%dy)\n", cfg.yearlyyears);
 1037         }
 1038         sqlite3_snprintf(256, sql, "delete from year where date < date('now', '-%d years'%s)", cfg.yearlyyears, cfg.dbtzmodifier);
 1039         if (!db_exec(sql)) {
 1040             db_rollbacktransaction();
 1041             return 0;
 1042         }
 1043     }
 1044 
 1045     return db_committransaction();
 1046 }
 1047 
 1048 int db_removeoldentries_top(void)
 1049 {
 1050     int errorcount = 0;
 1051     char sql[512];
 1052     iflist *dbifl = NULL, *dbifl_iterator = NULL;
 1053     sqlite3_int64 ifaceid;
 1054 
 1055     if (cfg.topdayentries <= 0) {
 1056         return 1;
 1057     }
 1058 
 1059     if (db_getiflist(&dbifl) < 0) {
 1060         return 0;
 1061     }
 1062 
 1063     dbifl_iterator = dbifl;
 1064 
 1065     while (dbifl_iterator != NULL) {
 1066         if (debug) {
 1067             printf("db: top cleanup: %s (%d)\n", dbifl_iterator->interface, cfg.topdayentries);
 1068         }
 1069 
 1070         ifaceid = db_getinterfaceid(dbifl_iterator->interface, 0);
 1071         if (ifaceid == 0) {
 1072             errorcount++;
 1073             dbifl_iterator = dbifl_iterator->next;
 1074             continue;
 1075         }
 1076 
 1077         sqlite3_snprintf(512, sql, "delete from top where id in ( select id from top where interface=%" PRId64 " and date!=date('now'%s) order by rx+tx desc, date asc limit -1 offset %d )", (int64_t)ifaceid, cfg.dbtzmodifier, cfg.topdayentries);
 1078 
 1079         if (!db_exec(sql)) {
 1080             errorcount++;
 1081             dbifl_iterator = dbifl_iterator->next;
 1082             continue;
 1083         }
 1084 
 1085         dbifl_iterator = dbifl_iterator->next;
 1086     }
 1087 
 1088     iflistfree(&dbifl);
 1089 
 1090     if (errorcount) {
 1091         return 0;
 1092     }
 1093 
 1094     return 1;
 1095 }
 1096 
 1097 int db_vacuum(void)
 1098 {
 1099     if (debug) {
 1100         printf("db: vacuum\n");
 1101     }
 1102     return db_exec("VACUUM");
 1103 }
 1104 
 1105 int db_begintransaction(void)
 1106 {
 1107     int rc;
 1108 
 1109     if (debug) {
 1110         printf("db: begin transaction\n");
 1111     }
 1112 
 1113     rc = sqlite3_exec(db, "BEGIN IMMEDIATE", 0, 0, 0);
 1114     if (rc) {
 1115         db_errcode = rc;
 1116         snprintf(errorstring, 1024, "Begin transaction to database failed (%d): %s", rc, sqlite3_errmsg(db));
 1117         printe(PT_Error);
 1118         return 0;
 1119     }
 1120     db_intransaction = 1;
 1121     return 1;
 1122 }
 1123 
 1124 int db_committransaction(void)
 1125 {
 1126     int rc;
 1127 
 1128     if (debug) {
 1129         printf("db: commit transaction\n");
 1130     }
 1131 
 1132     db_intransaction = 0;
 1133 
 1134     rc = sqlite3_exec(db, "COMMIT", 0, 0, 0);
 1135     if (rc) {
 1136         snprintf(errorstring, 1024, "Commit transaction to database failed (%d): %s", rc, sqlite3_errmsg(db));
 1137         printe(PT_Error);
 1138         /* execute rollback if commit failure left the transaction open */
 1139         if (!sqlite3_get_autocommit(db)) {
 1140             db_rollbacktransaction();
 1141         }
 1142         db_errcode = rc;
 1143         return 0;
 1144     }
 1145     return 1;
 1146 }
 1147 
 1148 int db_rollbacktransaction(void)
 1149 {
 1150     int rc;
 1151 
 1152     if (debug) {
 1153         printf("db: rollback transaction\n");
 1154     }
 1155 
 1156     db_intransaction = 0;
 1157 
 1158     rc = sqlite3_exec(db, "ROLLBACK", 0, 0, 0);
 1159     if (rc) {
 1160         db_errcode = rc;
 1161         snprintf(errorstring, 1024, "Transaction rollback failed (%d): %s", rc, sqlite3_errmsg(db));
 1162         printe(PT_Error);
 1163         return 0;
 1164     }
 1165     return 1;
 1166 }
 1167 
 1168 int db_iserrcodefatal(int errcode)
 1169 {
 1170     switch (errcode) {
 1171         case SQLITE_OK:
 1172         case SQLITE_FULL:
 1173         case SQLITE_IOERR:
 1174         case SQLITE_LOCKED:
 1175         case SQLITE_BUSY:
 1176             return 0;
 1177         default:
 1178             return 1;
 1179     }
 1180 }
 1181 
 1182 int db_isdiskfull(int errcode)
 1183 {
 1184     if (errcode == SQLITE_FULL) {
 1185         return 1;
 1186     } else {
 1187         return 0;
 1188     }
 1189 }
 1190 
 1191 #if HAVE_DECL_SQLITE_CHECKPOINT_RESTART
 1192 void db_walcheckpoint(void)
 1193 {
 1194     double used_secs = 0.0;
 1195 
 1196     timeused(__func__, 1);
 1197 #if HAVE_DECL_SQLITE_CHECKPOINT_TRUNCATE
 1198     sqlite3_wal_checkpoint_v2(db, NULL, SQLITE_CHECKPOINT_TRUNCATE, NULL, NULL);
 1199 #else
 1200     sqlite3_wal_checkpoint_v2(db, NULL, SQLITE_CHECKPOINT_RESTART, NULL, NULL);
 1201 #endif
 1202     timeused(__func__, 0);
 1203 
 1204     used_secs = timeused(__func__, 0);
 1205     if (used_secs > SLOWDBWARNLIMIT) {
 1206         snprintf(errorstring, 1024, "Write-Ahead Logging checkpoint took %.1f seconds.", used_secs);
 1207         printe(PT_Warning);
 1208     }
 1209 }
 1210 #endif
 1211 
 1212 int db_getdata(dbdatalist **dbdata, dbdatalistinfo *listinfo, const char *iface, const char *table, const uint32_t resultlimit)
 1213 {
 1214     return db_getdata_range(dbdata, listinfo, iface, table, resultlimit, "", "");
 1215 }
 1216 
 1217 int db_getdata_range(dbdatalist **dbdata, dbdatalistinfo *listinfo, const char *iface, const char *table, const uint32_t resultlimit, const char *databegin, const char *dataend)
 1218 {
 1219     int ret, i, rc;
 1220     const char *datatables[] = {"fiveminute", "hour", "day", "month", "year", "top"};
 1221     char sql[512], limit[64], dbegin[37], dend[44], *ifaceidin = NULL;
 1222     sqlite3_stmt *sqlstmt;
 1223     time_t timestamp;
 1224     int64_t rowid;
 1225     uint64_t rx, tx;
 1226 
 1227     listinfo->count = 0;
 1228 
 1229     ret = 0;
 1230     for (i = 0; i < 6; i++) {
 1231         if (strcmp(table, datatables[i]) == 0) {
 1232             ret = 1;
 1233             break;
 1234         }
 1235     }
 1236     if (!ret) {
 1237         return 0;
 1238     }
 1239 
 1240     ifaceidin = db_getinterfaceidin(iface);
 1241     if (ifaceidin == NULL) {
 1242         return 0;
 1243     }
 1244 
 1245     dbegin[0] = '\0';
 1246     if (strlen(databegin)) {
 1247         if (strcmp(databegin, "today") == 0) {
 1248             snprintf(dbegin, 37, "and date >= date('now'%s)", cfg.dbtzmodifier);
 1249         } else {
 1250             snprintf(dbegin, 37, "and date >= '%s'", databegin);
 1251         }
 1252     }
 1253 
 1254     dend[0] = '\0';
 1255     if (strlen(dataend)) {
 1256         if (strchr(dataend, ':')) {
 1257             snprintf(dend, 44, "and date <= datetime('%s')", dataend);
 1258         } else {
 1259             snprintf(dend, 44, "and date <= datetime('%s 23:59:59')", dataend);
 1260         }
 1261     }
 1262 
 1263     limit[0] = '\0';
 1264     if (resultlimit > 0 && (!strlen(dbegin) || !strlen(dend))) {
 1265         snprintf(limit, 64, "limit %" PRIu32 "", resultlimit);
 1266     }
 1267 
 1268     /* note that using the linked list reverses the order */
 1269     /* most recent last in the linked list is considered the normal order */
 1270     if (strcmp(table, "top") == 0) {
 1271         /* 'top' entries, requires different query due to rx+tx ordering */
 1272         if (strlen(dbegin)) {
 1273             if (resultlimit > 0) {
 1274                 snprintf(limit, 64, "limit %" PRIu32 "", resultlimit);
 1275             }
 1276             sqlite3_snprintf(512, sql, "select * from (select id, strftime('%%s', date, 'utc') as unixdate, sum(rx) as rx, sum(tx) as tx from day where interface in (%q) %s %s group by date order by rx+tx desc, unixdate asc %s) order by rx+tx asc, unixdate desc", ifaceidin, dbegin, dend, limit);
 1277         } else {
 1278             sqlite3_snprintf(512, sql, "select * from (select id, strftime('%%s', date, 'utc') as unixdate, sum(rx) as rx, sum(tx) as tx from top where interface in (%q) group by date order by rx+tx desc, unixdate asc %s) order by rx+tx asc, unixdate desc", ifaceidin, limit);
 1279         }
 1280     } else {
 1281         if (strlen(dbegin) && strlen(limit)) {
 1282             sqlite3_snprintf(512, sql, "select * from (select id, strftime('%%s', date, 'utc') as unixdate, sum(rx), sum(tx) from %s where interface in (%q) %s %s group by date order by unixdate asc %s) order by unixdate desc", table, ifaceidin, dbegin, dend, limit);
 1283         } else {
 1284             sqlite3_snprintf(512, sql, "select id, strftime('%%s', date, 'utc') as unixdate, sum(rx), sum(tx) from %s where interface in (%q) %s %s group by date order by unixdate desc %s", table, ifaceidin, dbegin, dend, limit);
 1285         }
 1286     }
 1287     free(ifaceidin);
 1288 
 1289     rc = sqlite3_prepare_v2(db, sql, -1, &sqlstmt, NULL);
 1290     if (rc != SQLITE_OK) {
 1291         db_errcode = rc;
 1292         snprintf(errorstring, 1024, "Get data prepare failed (%d: %s): \"%s\"", rc, sqlite3_errmsg(db), sql);
 1293         printe(PT_Error);
 1294         return 0;
 1295     }
 1296 
 1297     rc = sqlite3_column_count(sqlstmt);
 1298     if (rc != 4) {
 1299         snprintf(errorstring, 1024, "Get data returned unexpected column count %d instead of 4: \"%s\"", rc, sql);
 1300         printe(PT_Error);
 1301         sqlite3_finalize(sqlstmt);
 1302         return 0;
 1303     }
 1304 
 1305     while (sqlite3_step(sqlstmt) == SQLITE_ROW) {
 1306         rowid = (int64_t)sqlite3_column_int64(sqlstmt, 0);
 1307         timestamp = (time_t)sqlite3_column_int64(sqlstmt, 1);
 1308         rx = (uint64_t)sqlite3_column_int64(sqlstmt, 2);
 1309         tx = (uint64_t)sqlite3_column_int64(sqlstmt, 3);
 1310         if (!dbdatalistadd(dbdata, rx, tx, timestamp, rowid)) {
 1311             snprintf(errorstring, 1024, "Storing data for processing failed: %s", strerror(errno));
 1312             printe(PT_Error);
 1313             ret = 0;
 1314             break;
 1315         }
 1316         updatelistinfo(listinfo, rx, tx, timestamp);
 1317     }
 1318     sqlite3_finalize(sqlstmt);
 1319 
 1320     /* clean list on failure */
 1321     if (!ret) {
 1322         dbdatalistfree(dbdata);
 1323         listinfo->count = 0;
 1324     }
 1325 
 1326     return ret;
 1327 }
 1328 
 1329 void updatelistinfo(dbdatalistinfo *listinfo, const uint64_t rx, const uint64_t tx, const time_t timestamp)
 1330 {
 1331     if (listinfo->count == 0) {
 1332         listinfo->maxtime = timestamp;
 1333         listinfo->mintime = timestamp;
 1334         listinfo->maxrx = rx;
 1335         listinfo->minrx = rx;
 1336         listinfo->maxtx = tx;
 1337         listinfo->mintx = tx;
 1338         listinfo->min = rx + tx;
 1339         listinfo->max = rx + tx;
 1340         listinfo->sumrx = rx;
 1341         listinfo->sumtx = tx;
 1342     } else {
 1343         if (timestamp > listinfo->maxtime) {
 1344             listinfo->maxtime = timestamp;
 1345         }
 1346         if (timestamp < listinfo->mintime) {
 1347             listinfo->mintime = timestamp;
 1348         }
 1349         if (rx < listinfo->minrx) {
 1350             listinfo->minrx = rx;
 1351         }
 1352         if (tx < listinfo->mintx) {
 1353             listinfo->mintx = tx;
 1354         }
 1355         if (rx > listinfo->maxrx) {
 1356             listinfo->maxrx = rx;
 1357         }
 1358         if (tx > listinfo->maxtx) {
 1359             listinfo->maxtx = tx;
 1360         }
 1361         if (rx + tx > listinfo->max) {
 1362             listinfo->max = rx + tx;
 1363         }
 1364         if (rx + tx < listinfo->min) {
 1365             listinfo->min = rx + tx;
 1366         }
 1367         listinfo->sumrx += rx;
 1368         listinfo->sumtx += tx;
 1369     }
 1370     listinfo->count++;
 1371 }
 1372 
 1373 int dbdatalistadd(dbdatalist **dbdata, const uint64_t rx, const uint64_t tx, const time_t timestamp, const int64_t rowid)
 1374 {
 1375     dbdatalist *newdata;
 1376 
 1377     newdata = malloc(sizeof(dbdatalist));
 1378     if (newdata == NULL) {
 1379         return 0;
 1380     }
 1381 
 1382     newdata->next = *dbdata;
 1383     *dbdata = newdata;
 1384 
 1385     newdata->rowid = rowid;
 1386     newdata->timestamp = timestamp;
 1387     newdata->rx = rx;
 1388     newdata->tx = tx;
 1389 
 1390     return 1;
 1391 }
 1392 
 1393 void dbdatalistfree(dbdatalist **dbdata)
 1394 {
 1395     dbdatalist *dbdata_prev;
 1396 
 1397     while (*dbdata != NULL) {
 1398         dbdata_prev = *dbdata;
 1399         *dbdata = (*dbdata)->next;
 1400         free(dbdata_prev);
 1401     }
 1402 }
 1403 
 1404 unsigned int getqueryinterfacecount(const char *input)
 1405 {
 1406     unsigned int i, ifacecount = 1;
 1407 
 1408     if (!strlen(input) || input[0] == '+' || input[strlen(input) - 1] == '+') {
 1409         return 0;
 1410     }
 1411 
 1412     for (i = 0; i < (unsigned int)strlen(input); i++) {
 1413         if (input[i] == '+') {
 1414             if (i > 0 && input[i - 1] == '+') {
 1415                 return 0;
 1416             } else {
 1417                 ifacecount++;
 1418             }
 1419         }
 1420     }
 1421 
 1422     return ifacecount;
 1423 }
 1424 
 1425 char *getifaceinquery(const char *input)
 1426 {
 1427     unsigned int i, j, ifacecount;
 1428     char *result;
 1429 
 1430     ifacecount = getqueryinterfacecount(input);
 1431 
 1432     if (ifacecount == 0) {
 1433         return NULL;
 1434     }
 1435 
 1436     /* each interface requires two quotes and comma or \0 so 3 extra chars */
 1437     j = (unsigned int)strlen(input) + ifacecount * 3;
 1438     result = malloc(sizeof(char) * j);
 1439     if (result == NULL) {
 1440         panicexit(__FILE__, __LINE__);
 1441     }
 1442 
 1443     memset(result, '\0', j);
 1444 
 1445     result[0] = '"';
 1446     j = 1;
 1447     for (i = 0; i < (unsigned int)strlen(input); i++) {
 1448         if (input[i] == '+') {
 1449             strcat(result, "\",\"");
 1450             j += 3;
 1451         } else {
 1452             result[j] = input[i];
 1453             j++;
 1454         }
 1455     }
 1456     result[j] = '"';
 1457 
 1458     return result;
 1459 }