add optional support for SQLite Write-Ahead Logging mode

diff --git a/CHANGES b/CHANGES
index 83e39d7147181d26150fccf21ba47626c0e7896f..4e4d54475243405c2915f1c46680608cdc82d00d 100644 (file)
--- a/CHANGES
+++ b/CHANGES
@@ -6,6 +6,11 @@
    - Systemd example service file could result in database file write issues
      if the used systemd version supported ProtectSystem=strict but didn't
      support StateDirectory (issue seen at least with systemd 232 in Debian 9)
+ - New
+   - Add configuration option WriteAheadLoggingDatabase to enable SQLite
+     Write-Ahead Logging mode which may provide some disk i/o benefits,
+     see https://www.sqlite.org/wal.html for more details and note that
+     SQLite 3.22.0 or later is required to support read-only operations
 
 
 2.2 / 28-Apr-2018

diff --git a/UPGRADE.md b/UPGRADE.md
index 9794c6057d232a4c8f618f6ca499b8241d960af8..9147a7e846fdefde46b1ab29eafe7ada9c1bc4b1 100644 (file)
--- a/UPGRADE.md
+++ b/UPGRADE.md
@@ -1,6 +1,8 @@
 
 # New configuration settings
 
+ * 2.3: WriteAheadLoggingDatabase
+
  * 2.2: 64bitInterfaceCounters
 
  * 2.1: (none)

diff --git a/cfg/vnstat.conf b/cfg/vnstat.conf
index 4b6b1334e7737b9b4a6d6654e0b24e732413526a..ad178e35961ff442b185b0a08f08bb046846c4b7 100644 (file)
--- a/cfg/vnstat.conf
+++ b/cfg/vnstat.conf
@@ -148,6 +148,9 @@ PidFile "/var/run/vnstat/vnstat.pid"
 # 1 = 64-bit, 0 = 32-bit, -1 = old style logic, -2 = automatic detection
 64bitInterfaceCounters -2
 
+# use SQLite Write-Ahead Logging mode (1 = enabled, 0 = disabled)
+WriteAheadLoggingDatabase 0
+
 
 # vnstati
 ##

diff --git a/man/vnstat.conf.5 b/man/vnstat.conf.5
index 4e5da158a2f026eea35439031cf044b74da52607..01584f703864e7541174a7526be74e6e87e265a8 100644 (file)
--- a/man/vnstat.conf.5
+++ b/man/vnstat.conf.5
@@ -336,6 +336,12 @@ defines for how many past years entries will be stored. Set to -1 for
 unlimited entries or to 0 to disable the data collection of this
 resolution.
 
+.TP
+.B WriteAheadLoggingDatabase
+Enable or disable SQLite Write-Ahead Logging mode for the database. See SQLite
+documentation for more details and note that support for read-only operations
+isn't available in older versions. 1 = enabled, 0 = disabled.
+
 .SH IMAGE OUTPUT RELATED KEYWORDS
 
 .TP

diff --git a/src/cfg.c b/src/cfg.c
index 0f5a46b2ea1e8b4aa82fcc601c32402bb04c27cb..69177375336d3f1f87e50ab38041392155f033c1 100644 (file)
--- a/src/cfg.c
+++ b/src/cfg.c
@@ -64,6 +64,7 @@ int loadcfg(const char *cfgfile)
                 {"LogFile", cfg.logfile, 0, 512, 0},
                 {"PidFile", cfg.pidfile, 0, 512, 0},
                 {"64bitInterfaceCounters", 0, &cfg.is64bit, 0, 0},
+                {"WriteAheadLoggingDatabase", 0, &cfg.waldb, 0, 0},
                 {"HeaderFormat", cfg.hformat, 0, 64, 0},
                 {"HourlyRate", 0, &cfg.hourlyrate, 0, 0},
                 {"SummaryRate", 0, &cfg.summaryrate, 0, 0},
@@ -190,6 +191,7 @@ void validatecfg(void)
        validateint("CreateDirs", &cfg.createdirs, CREATEDIRS, 0, 2);
        validateint("UpdateFileOwner", &cfg.updatefileowner, UPDATEFILEOWNER, 0, 2);
        validateint("64bitInterfaceCounters", &cfg.is64bit, IS64BIT, -2, 1);
+       validatebool("WriteAheadLoggingDatabase", &cfg.waldb, WALDB);
        validatebool("TransparentBg", &cfg.transbg, TRANSBG);
        validatebool("HourlyRate", &cfg.hourlyrate, HOURLYRATE);
        validatebool("SummaryRate", &cfg.summaryrate, SUMMARYRATE);
@@ -339,6 +341,7 @@ void defaultcfg(void)
        strncpy_nt(cfg.logfile, LOGFILE, 512);
        strncpy_nt(cfg.pidfile, PIDFILE, 512);
        cfg.is64bit = IS64BIT;
+       cfg.waldb = WALDB;
 
        cfg.transbg = TRANSBG;
        strncpy_nt(cfg.cbg, CBACKGROUND, 8);

diff --git a/src/cfgoutput.c b/src/cfgoutput.c
index 74946855a98e987ed432d5954e832849c387f43e..c24a52d6314010728293fe7788a60523c056ad11 100644 (file)
--- a/src/cfgoutput.c
+++ b/src/cfgoutput.c
@@ -70,9 +70,9 @@ void printcfgfile(void)
        printf("ListDays       %2d\n", cfg.listdays);
        printf("ListMonths     %2d\n", cfg.listmonths);
        printf("ListYears      %2d\n", cfg.listyears);
-       printf("ListTop        %2d\n", cfg.listtop);
+       printf("ListTop        %2d\n\n", cfg.listtop);
 
-       printf("\n\n");
+       printf("\n");
 
        /* vnstatd section */
        printf("# vnstatd\n##\n\n");
@@ -161,9 +161,12 @@ void printcfgfile(void)
        printf("PidFile \"%s\"\n\n", cfg.pidfile);
 
        printf("# 1 = 64-bit, 0 = 32-bit, -1 = old style logic, -2 = automatic detection\n");
-       printf("64bitInterfaceCounters %d\n", cfg.is64bit);
+       printf("64bitInterfaceCounters %d\n\n", cfg.is64bit);
+
+       printf("# use SQLite Write-Ahead Logging mode (1 = enabled, 0 = disabled)\n");
+       printf("WriteAheadLoggingDatabase %d\n\n", cfg.waldb);
 
-       printf("\n\n");
+       printf("\n");
 
        /* vnstati section */
        printf("# vnstati\n##\n\n");

diff --git a/src/common.h b/src/common.h
index 3ab0215daafeee770d322ee7758c9c6c13303018..c61dcc3971678d7594b46e6c4265dcd2f3367470 100644 (file)
--- a/src/common.h
+++ b/src/common.h
@@ -231,6 +231,7 @@ and most can be changed later from the config file.
 #define LOGFILE "/var/log/vnstat/vnstat.log"
 #define PIDFILE "/var/run/vnstat/vnstat.pid"
 #define IS64BIT -2
+#define WALDB 0
 
 /* no transparency by default */
 #define TRANSBG 0
@@ -263,7 +264,7 @@ typedef struct {
        char cline[8], clinel[8], cvnstat[8], crx[8], crxd[8], ctx[8], ctxd[8];
        int32_t unitmode, rateunitmode, rateunit, bvar, qmode, sampletime, hourlyrate, summaryrate;
        int32_t monthrotate, monthrotateyears, maxbw, spacecheck, trafficlessentries, transbg, ostyle;
-       int32_t defaultdecimals, hourlydecimals, hourlystyle, is64bit;
+       int32_t defaultdecimals, hourlydecimals, hourlystyle, is64bit, waldb;
        char cfgfile[512], logfile[512], pidfile[512];
        char daemonuser[33], daemongroup[33];
        int32_t timesyncwait, updateinterval, pollinterval, saveinterval, offsaveinterval, savestatus;

diff --git a/src/dbsql.c b/src/dbsql.c
index e25c91fc49c36795c723eec8eb1e9894ccf0c47b..7049eb67c99ee3a75c3ee7b280f80175064403c3 100644 (file)
--- a/src/dbsql.c
+++ b/src/dbsql.c
@@ -95,9 +95,11 @@ int db_open(const int createifnotfound, const int readonly)
        }
 
        /* set pragmas */
-       if (!db_setpragmas()) {
-               db_close();
-               return 0;
+       if (!readonly) {
+               if (!db_setpragmas()) {
+                       db_close();
+                       return 0;
+               }
        }
 
        if (!createdb) {
@@ -193,6 +195,23 @@ int db_setpragmas(void)
                return 0;
        }
 
+       /* set journal_mode */
+       if (cfg.waldb) {
+               if (!db_exec("PRAGMA journal_mode = WAL")) {
+                       return 0;
+               }
+               if (!db_exec("PRAGMA synchronous = 1")) {
+                       return 0;
+               }
+       } else {
+               if (!db_exec("PRAGMA journal_mode = DELETE")) {
+                       return 0;
+               }
+               if (!db_exec("PRAGMA synchronous = 2")) {
+                       return 0;
+               }
+       }
+
        return 1;
 }
 
@@ -224,7 +243,7 @@ int db_exec(const char *sql)
        }
 
        rc = sqlite3_step(sqlstmt);
-       if (rc != SQLITE_DONE) {
+       if (rc != SQLITE_DONE && rc != SQLITE_ROW) {
                db_errcode = rc;
                snprintf(errorstring, 1024, "Exec step failed (%d: %s): \"%s\"", rc, sqlite3_errmsg(db), sql);
                printe(PT_Error);