"Fossies" - the Fresh Open Source Software Archive  

Source code changes of the file "src/dbsql.c" between
vnstat-2.7.tar.gz and vnstat-2.8.tar.gz

About: vnStat is a console-based network traffic monitor (using the /proc filesystem).

dbsql.c  (vnstat-2.7):dbsql.c  (vnstat-2.8)
skipping to change at line 378 skipping to change at line 378
} }
int db_addinterface(const char *iface) int db_addinterface(const char *iface)
{ {
char sql[256]; char sql[256];
if (!strlen(iface)) { if (!strlen(iface)) {
return 0; return 0;
} }
sqlite3_snprintf(256, sql, "insert into interface (name, active, created, updated, rxcounter, txcounter, rxtotal, txtotal) values ('%q', 1, datetime('now ', 'localtime'), datetime('now', 'localtime'), 0, 0, 0, 0)", iface); 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 );
return db_exec(sql); return db_exec(sql);
} }
int db_removeinterface(const char *iface) int db_removeinterface(const char *iface)
{ {
char sql[64]; char sql[64];
sqlite3_int64 ifaceid = 0; sqlite3_int64 ifaceid = 0;
ifaceid = db_getinterfaceid(iface, 0); ifaceid = db_getinterfaceid(iface, 0);
if (ifaceid == 0) { if (ifaceid == 0) {
skipping to change at line 551 skipping to change at line 551
int db_setupdated(const char *iface, const time_t timestamp) int db_setupdated(const char *iface, const time_t timestamp)
{ {
char sql[256]; char sql[256];
sqlite3_int64 ifaceid = 0; sqlite3_int64 ifaceid = 0;
ifaceid = db_getinterfaceid(iface, 0); ifaceid = db_getinterfaceid(iface, 0);
if (ifaceid == 0) { if (ifaceid == 0) {
return 0; return 0;
} }
sqlite3_snprintf(256, sql, "update interface set updated=datetime(%" PRIu 64 ", 'unixepoch', 'localtime') where id=%" PRId64 "", (uint64_t)timestamp, (int 64_t)ifaceid); sqlite3_snprintf(256, sql, "update interface set updated=datetime(%" PRIu 64 ", 'unixepoch'%s) where id=%" PRId64 "", (uint64_t)timestamp, cfg.dbtzmodifie r, (int64_t)ifaceid);
return db_exec(sql); return db_exec(sql);
} }
int db_setcounters(const char *iface, const uint64_t rxcounter, const uint64_t t xcounter) int db_setcounters(const char *iface, const uint64_t rxcounter, const uint64_t t xcounter)
{ {
char sql[256]; char sql[256];
sqlite3_int64 ifaceid = 0; sqlite3_int64 ifaceid = 0;
ifaceid = db_getinterfaceid(iface, 0); ifaceid = db_getinterfaceid(iface, 0);
if (ifaceid == 0) { if (ifaceid == 0) {
skipping to change at line 767 skipping to change at line 767
return rc; return rc;
} }
char *db_get_date_generator(const int range, const short direct, const char *now date) char *db_get_date_generator(const int range, const short direct, const char *now date)
{ {
static char dgen[512]; static char dgen[512];
dgen[0] = '\0'; dgen[0] = '\0';
switch (range) { switch (range) {
case 0: /* 5min */ case 0: /* 5min */
snprintf(dgen, 512, "datetime(%s, ('-' || (strftime('%%M' , %s)) || ' minutes'), ('-' || (strftime('%%S', %s)) || ' seconds'), ('+' || (ro und(strftime('%%M', %s)/5,0)*5) || ' minutes'), 'localtime')", nowdate, nowdate, nowdate, nowdate); snprintf(dgen, 512, "datetime(%s, ('-' || (strftime('%%M' , %s)) || ' minutes'), ('-' || (strftime('%%S', %s)) || ' seconds'), ('+' || (ro und(strftime('%%M', %s)/5,0)*5) || ' minutes')%s)", nowdate, nowdate, nowdate, n owdate, cfg.dbtzmodifier);
break; break;
case 1: /* hour */ case 1: /* hour */
snprintf(dgen, 512, "strftime('%%Y-%%m-%%d %%H:00:00', %s , 'localtime')", nowdate); snprintf(dgen, 512, "strftime('%%Y-%%m-%%d %%H:00:00', %s %s)", nowdate, cfg.dbtzmodifier);
break; break;
case 2: /* day */ case 2: /* day */
case 5: /* top */ case 5: /* top */
snprintf(dgen, 512, "date(%s, 'localtime')", nowdate); snprintf(dgen, 512, "date(%s%s)", nowdate, cfg.dbtzmodifi er);
break; break;
case 3: /* month */ case 3: /* month */
if (direct || cfg.monthrotate == 1) { if (direct || cfg.monthrotate == 1) {
snprintf(dgen, 512, "strftime('%%Y-%%m-01', %s, ' localtime')", nowdate); snprintf(dgen, 512, "strftime('%%Y-%%m-01', %s%s) ", nowdate, cfg.dbtzmodifier);
} else { } else {
snprintf(dgen, 512, "strftime('%%Y-%%m-01', datet ime(%s, '-%d days'), 'localtime')", nowdate, cfg.monthrotate - 1); snprintf(dgen, 512, "strftime('%%Y-%%m-01', datet ime(%s, '-%d days')%s)", nowdate, cfg.monthrotate - 1, cfg.dbtzmodifier);
} }
break; break;
case 4: /* year */ case 4: /* year */
if (direct || cfg.monthrotate == 1 || cfg.monthrotateyear s == 0) { if (direct || cfg.monthrotate == 1 || cfg.monthrotateyear s == 0) {
snprintf(dgen, 512, "strftime('%%Y-01-01', %s, 'l ocaltime')", nowdate); snprintf(dgen, 512, "strftime('%%Y-01-01', %s%s)" , nowdate, cfg.dbtzmodifier);
} else { } else {
snprintf(dgen, 512, "strftime('%%Y-01-01', dateti me(%s, '-%d days'), 'localtime')", nowdate, cfg.monthrotate - 1); snprintf(dgen, 512, "strftime('%%Y-01-01', dateti me(%s, '-%d days')%s)", nowdate, cfg.monthrotate - 1, cfg.dbtzmodifier);
} }
break; break;
default: default:
break; break;
} }
return dgen; return dgen;
} }
int db_addtraffic(const char *iface, const uint64_t rx, const uint64_t tx) int db_addtraffic(const char *iface, const uint64_t rx, const uint64_t tx)
{ {
skipping to change at line 872 skipping to change at line 872
int db_setcreation(const char *iface, const time_t timestamp) int db_setcreation(const char *iface, const time_t timestamp)
{ {
char sql[256]; char sql[256];
sqlite3_int64 ifaceid = 0; sqlite3_int64 ifaceid = 0;
ifaceid = db_getinterfaceid(iface, 0); ifaceid = db_getinterfaceid(iface, 0);
if (ifaceid == 0) { if (ifaceid == 0) {
return 0; return 0;
} }
sqlite3_snprintf(256, sql, "update interface set created=datetime(%" PRIu 64 ", 'unixepoch', 'localtime') where id=%" PRId64 "", (uint64_t)timestamp, (int 64_t)ifaceid); sqlite3_snprintf(256, sql, "update interface set created=datetime(%" PRIu 64 ", 'unixepoch'%s) where id=%" PRId64 "", (uint64_t)timestamp, cfg.dbtzmodifie r, (int64_t)ifaceid);
return db_exec(sql); return db_exec(sql);
} }
int db_settotal(const char *iface, const uint64_t rx, const uint64_t tx) int db_settotal(const char *iface, const uint64_t rx, const uint64_t tx)
{ {
char sql[256]; char sql[256];
sqlite3_int64 ifaceid = 0; sqlite3_int64 ifaceid = 0;
ifaceid = db_getinterfaceid(iface, 0); ifaceid = db_getinterfaceid(iface, 0);
if (ifaceid == 0) { if (ifaceid == 0) {
return 0; return 0;
} }
sqlite3_snprintf(256, sql, "update interface set rxtotal=%" PRIu64 ", txt otal=%" PRIu64 " where id=%" PRId64 "", rx, tx, (int64_t)ifaceid); sqlite3_snprintf(256, sql, "update interface set rxtotal=%" PRIu64 ", txt otal=%" PRIu64 " where id=%" PRId64 "", rx, tx, (int64_t)ifaceid);
return db_exec(sql); return db_exec(sql);
} }
/* used only for legacy data import */
int db_insertdata(const char *table, const char *iface, const uint64_t rx, const uint64_t tx, const uint64_t timestamp) int db_insertdata(const char *table, const char *iface, const uint64_t rx, const uint64_t tx, const uint64_t timestamp)
{ {
int i, index = -1; int i, index = -1;
char sql[1024], nowdate[64]; char sql[1024], nowdate[64];
sqlite3_int64 ifaceid = 0; sqlite3_int64 ifaceid = 0;
const char *datatables[] = {"hour", "day", "month", "year", "top"}; const char *datatables[] = {"hour", "day", "month", "year", "top"};
for (i = 0; i < 5; i++) { for (i = 0; i < 5; i++) {
if (strcmp(table, datatables[i]) == 0) { if (strcmp(table, datatables[i]) == 0) {
skipping to change at line 914 skipping to change at line 915
if (index == -1) { if (index == -1) {
return 0; return 0;
} }
ifaceid = db_getinterfaceid(iface, 0); ifaceid = db_getinterfaceid(iface, 0);
if (ifaceid == 0) { if (ifaceid == 0) {
return 0; return 0;
} }
snprintf(nowdate, 64, "datetime(%" PRIu64 ", 'unixepoch')", timestamp); /* legacy data is always local timezone and needs to be enforced here as
a result */
if (cfg.useutc) {
snprintf(nowdate, 64, "datetime(%" PRIu64 ", 'unixepoch', 'localt
ime')", timestamp);
} else {
snprintf(nowdate, 64, "datetime(%" PRIu64 ", 'unixepoch')", times
tamp);
}
sqlite3_snprintf(1024, sql, "insert or ignore into %s (interface, date, r x, tx) values (%" PRId64 ", %s, %" PRIu64 ", %" PRIu64 ")", table, (int64_t)ifac eid, db_get_date_generator(index + 1, 1, nowdate), rx, tx); sqlite3_snprintf(1024, sql, "insert or ignore into %s (interface, date, r x, tx) values (%" PRId64 ", %s, %" PRIu64 ", %" PRIu64 ")", table, (int64_t)ifac eid, db_get_date_generator(index + 1, 1, nowdate), rx, tx);
return db_exec(sql); return db_exec(sql);
} }
int db_removeoldentries(void) int db_removeoldentries(void)
{ {
char sql[256]; char sql[256];
if (debug) { if (debug) {
skipping to change at line 941 skipping to change at line 947
if (!db_removeoldentries_top()) { if (!db_removeoldentries_top()) {
db_rollbacktransaction(); db_rollbacktransaction();
return 0; return 0;
} }
if (cfg.fiveminutehours > 0) { if (cfg.fiveminutehours > 0) {
if (debug) { if (debug) {
printf("db: fiveminute cleanup (%dh)\n", cfg.fiveminuteho urs); printf("db: fiveminute cleanup (%dh)\n", cfg.fiveminuteho urs);
} }
sqlite3_snprintf(256, sql, "delete from fiveminute where date < d atetime('now', '-%d hours', 'localtime')", cfg.fiveminutehours); sqlite3_snprintf(256, sql, "delete from fiveminute where date < d atetime('now', '-%d hours'%s)", cfg.fiveminutehours, cfg.dbtzmodifier);
if (!db_exec(sql)) { if (!db_exec(sql)) {
db_rollbacktransaction(); db_rollbacktransaction();
return 0; return 0;
} }
} }
if (cfg.hourlydays > 0) { if (cfg.hourlydays > 0) {
if (debug) { if (debug) {
printf("db: hour cleanup (%dd)\n", cfg.hourlydays); printf("db: hour cleanup (%dd)\n", cfg.hourlydays);
} }
sqlite3_snprintf(256, sql, "delete from hour where date < datetim e('now', '-%d days', 'localtime')", cfg.hourlydays); sqlite3_snprintf(256, sql, "delete from hour where date < datetim e('now', '-%d days'%s)", cfg.hourlydays, cfg.dbtzmodifier);
if (!db_exec(sql)) { if (!db_exec(sql)) {
db_rollbacktransaction(); db_rollbacktransaction();
return 0; return 0;
} }
} }
if (cfg.dailydays > 0) { if (cfg.dailydays > 0) {
if (debug) { if (debug) {
printf("db: day cleanup (%dd)\n", cfg.dailydays); printf("db: day cleanup (%dd)\n", cfg.dailydays);
} }
sqlite3_snprintf(256, sql, "delete from day where date < date('no w', '-%d days', 'localtime')", cfg.dailydays); sqlite3_snprintf(256, sql, "delete from day where date < date('no w', '-%d days'%s)", cfg.dailydays, cfg.dbtzmodifier);
if (!db_exec(sql)) { if (!db_exec(sql)) {
db_rollbacktransaction(); db_rollbacktransaction();
return 0; return 0;
} }
} }
if (cfg.monthlymonths > 0) { if (cfg.monthlymonths > 0) {
if (debug) { if (debug) {
printf("db: month cleanup (%dm)\n", cfg.monthlymonths); printf("db: month cleanup (%dm)\n", cfg.monthlymonths);
} }
sqlite3_snprintf(256, sql, "delete from month where date < date(' now', '-%d months', 'localtime')", cfg.monthlymonths); sqlite3_snprintf(256, sql, "delete from month where date < date(' now', '-%d months'%s)", cfg.monthlymonths, cfg.dbtzmodifier);
if (!db_exec(sql)) { if (!db_exec(sql)) {
db_rollbacktransaction(); db_rollbacktransaction();
return 0; return 0;
} }
} }
if (cfg.yearlyyears > 0) { if (cfg.yearlyyears > 0) {
if (debug) { if (debug) {
printf("db: year cleanup (%dy)\n", cfg.yearlyyears); printf("db: year cleanup (%dy)\n", cfg.yearlyyears);
} }
sqlite3_snprintf(256, sql, "delete from year where date < date('n ow', '-%d years', 'localtime')", cfg.yearlyyears); sqlite3_snprintf(256, sql, "delete from year where date < date('n ow', '-%d years'%s)", cfg.yearlyyears, cfg.dbtzmodifier);
if (!db_exec(sql)) { if (!db_exec(sql)) {
db_rollbacktransaction(); db_rollbacktransaction();
return 0; return 0;
} }
} }
return db_committransaction(); return db_committransaction();
} }
int db_removeoldentries_top(void) int db_removeoldentries_top(void)
skipping to change at line 1024 skipping to change at line 1030
printf("db: top cleanup: %s (%d)\n", dbifl_iterator->inte rface, cfg.topdayentries); printf("db: top cleanup: %s (%d)\n", dbifl_iterator->inte rface, cfg.topdayentries);
} }
ifaceid = db_getinterfaceid(dbifl_iterator->interface, 0); ifaceid = db_getinterfaceid(dbifl_iterator->interface, 0);
if (ifaceid == 0) { if (ifaceid == 0) {
errorcount++; errorcount++;
dbifl_iterator = dbifl_iterator->next; dbifl_iterator = dbifl_iterator->next;
continue; continue;
} }
sqlite3_snprintf(512, sql, "delete from top where id in ( select id from top where interface=%" PRId64 " and date!=date('now', 'localtime') order by rx+tx desc limit -1 offset %d )", (int64_t)ifaceid, cfg.topdayentries); 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 d esc, date asc limit -1 offset %d )", (int64_t)ifaceid, cfg.dbtzmodifier, cfg.top dayentries);
if (!db_exec(sql)) { if (!db_exec(sql)) {
errorcount++; errorcount++;
dbifl_iterator = dbifl_iterator->next; dbifl_iterator = dbifl_iterator->next;
continue; continue;
} }
dbifl_iterator = dbifl_iterator->next; dbifl_iterator = dbifl_iterator->next;
} }
skipping to change at line 1166 skipping to change at line 1172
} }
#endif #endif
int db_getdata(dbdatalist **dbdata, dbdatalistinfo *listinfo, const char *iface, const char *table, const uint32_t resultlimit) int db_getdata(dbdatalist **dbdata, dbdatalistinfo *listinfo, const char *iface, const char *table, const uint32_t resultlimit)
{ {
return db_getdata_range(dbdata, listinfo, iface, table, resultlimit, "", ""); return db_getdata_range(dbdata, listinfo, iface, table, resultlimit, "", "");
} }
int db_getdata_range(dbdatalist **dbdata, dbdatalistinfo *listinfo, const char * iface, const char *table, const uint32_t resultlimit, const char *databegin, con st char *dataend) int db_getdata_range(dbdatalist **dbdata, dbdatalistinfo *listinfo, const char * iface, const char *table, const uint32_t resultlimit, const char *databegin, con st char *dataend)
{ {
int ret = 1, i, rc; int ret, i, rc;
const char *datatables[] = {"fiveminute", "hour", "day", "month", "year", "top"}; const char *datatables[] = {"fiveminute", "hour", "day", "month", "year", "top"};
char sql[512], limit[64], dbegin[32], dend[44], *ifaceidin = NULL; char sql[512], limit[64], dbegin[32], dend[44], *ifaceidin = NULL;
sqlite3_stmt *sqlstmt; sqlite3_stmt *sqlstmt;
time_t timestamp; time_t timestamp;
int64_t rowid; int64_t rowid;
uint64_t rx, tx; uint64_t rx, tx;
listinfo->count = 0; listinfo->count = 0;
ret = 0; ret = 0;
skipping to change at line 1219 skipping to change at line 1225
} }
/* note that using the linked list reverses the order */ /* note that using the linked list reverses the order */
/* most recent last in the linked list is considered the normal order */ /* most recent last in the linked list is considered the normal order */
if (strcmp(table, "top") == 0) { if (strcmp(table, "top") == 0) {
/* 'top' entries, requires different query due to rx+tx ordering */ /* 'top' entries, requires different query due to rx+tx ordering */
if (strlen(dbegin)) { if (strlen(dbegin)) {
if (resultlimit > 0) { if (resultlimit > 0) {
snprintf(limit, 64, "limit %" PRIu32 "", resultli mit); snprintf(limit, 64, "limit %" PRIu32 "", resultli mit);
} }
sqlite3_snprintf(512, sql, "select * from (select id, str ftime('%%s', date, 'utc'), sum(rx) as rx, sum(tx) as tx from day where interface in (%q) %s %s group by date order by rx+tx desc %s) order by rx+tx asc", ifacei din, dbegin, dend, limit); sqlite3_snprintf(512, sql, "select * from (select id, str ftime('%%s', date, 'utc') as unixdate, sum(rx) as rx, sum(tx) as tx from day whe re interface in (%q) %s %s group by date order by rx+tx desc, unixdate asc %s) o rder by rx+tx asc, unixdate desc", ifaceidin, dbegin, dend, limit);
} else { } else {
sqlite3_snprintf(512, sql, "select * from (select id, str ftime('%%s', date, 'utc'), sum(rx) as rx, sum(tx) as tx from top where interface in (%q) group by date order by rx+tx desc %s) order by rx+tx asc", ifaceidin, l imit); sqlite3_snprintf(512, sql, "select * from (select id, str ftime('%%s', date, 'utc') as unixdate, sum(rx) as rx, sum(tx) as tx from top whe re interface in (%q) group by date order by rx+tx desc, unixdate asc %s) order b y rx+tx asc, unixdate desc", ifaceidin, limit);
} }
} else { } else {
if (strlen(dbegin) && strlen(limit)) { if (strlen(dbegin) && strlen(limit)) {
sqlite3_snprintf(512, sql, "select * from (select id, str ftime('%%s', date, 'utc') as unixdate, sum(rx), sum(tx) from %s where interface in (%q) %s %s group by date order by date asc %s) order by unixdate desc", table , ifaceidin, dbegin, dend, limit); sqlite3_snprintf(512, sql, "select * from (select id, str ftime('%%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", t able, ifaceidin, dbegin, dend, limit);
} else { } else {
sqlite3_snprintf(512, sql, "select id, strftime('%%s', da te, 'utc'), sum(rx), sum(tx) from %s where interface in (%q) %s %s group by date order by date desc %s", table, ifaceidin, dbegin, dend, limit); sqlite3_snprintf(512, sql, "select id, strftime('%%s', da te, 'utc') as unixdate, sum(rx), sum(tx) from %s where interface in (%q) %s %s g roup by date order by unixdate desc %s", table, ifaceidin, dbegin, dend, limit);
} }
} }
free(ifaceidin); free(ifaceidin);
rc = sqlite3_prepare_v2(db, sql, -1, &sqlstmt, NULL); rc = sqlite3_prepare_v2(db, sql, -1, &sqlstmt, NULL);
if (rc != SQLITE_OK) { if (rc != SQLITE_OK) {
db_errcode = rc; db_errcode = rc;
snprintf(errorstring, 1024, "Get data prepare failed (%d: %s): \" %s\"", rc, sqlite3_errmsg(db), sql); snprintf(errorstring, 1024, "Get data prepare failed (%d: %s): \" %s\"", rc, sqlite3_errmsg(db), sql);
printe(PT_Error); printe(PT_Error);
return 0; return 0;
skipping to change at line 1370 skipping to change at line 1376
ifacecount++; ifacecount++;
} }
} }
} }
return ifacecount; return ifacecount;
} }
char *getifaceinquery(const char *input) char *getifaceinquery(const char *input)
{ {
unsigned int i, j, ifacecount = 1; unsigned int i, j, ifacecount;
char *result; char *result;
ifacecount = getqueryinterfacecount(input); ifacecount = getqueryinterfacecount(input);
if (ifacecount == 0) { if (ifacecount == 0) {
return NULL; return NULL;
} }
/* each interface requires two quotes and comma or \0 so 3 extra chars */ /* each interface requires two quotes and comma or \0 so 3 extra chars */
j = (unsigned int)strlen(input) + ifacecount * 3; j = (unsigned int)strlen(input) + ifacecount * 3;
 End of changes. 24 change blocks. 
23 lines changed or deleted 32 lines changed or added

Home  |  About  |  Features  |  All  |  Newest  |  Dox  |  Diffs  |  RSS Feeds  |  Screenshots  |  Comments  |  Imprint  |  Privacy  |  HTTP(S)