vnstat  2.9
About: vnStat is a console-based network traffic monitor (using the /proc filesystem).
  Fossies Dox: vnstat-2.9.tar.gz  ("unofficial" and yet experimental doxygen-generated source code documentation)  

dbsql.c
Go to the documentation of this file.
1#include "common.h"
2#include "misc.h"
3#include "iflist.h"
4#include "dbsql.h"
5
6/* global db */
7sqlite3 *db;
10
11int db_open_ro(void)
12{
13 return db_open(0, 1);
14}
15
16int db_open_rw(const int createifnotfound)
17{
18 return db_open(createifnotfound, 0);
19}
20
21int 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;
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
131int 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
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));
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));
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));
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
248int 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
270int 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);
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);
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);
297 return 0;
298 }
299
300 return 1;
301}
302
303int 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)) {
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)) {
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);
356 return 0;
357 }
358 }
359 free(sql);
360
361 snprintf(buffer, 32, "%" PRIu64 "", (uint64_t)MAX32);
362 if (!db_setinfo("btime", buffer, 1)) {
364 return 0;
365 }
366
367 return db_committransaction();
368}
369
370int 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
382int 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
396int 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
415{
417}
418
419uint64_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));
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
466sqlite3_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));
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
496char *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));
522 }
523
524 return result;
525}
526
527int 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));
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
574int 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
588int 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
602int 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
616int 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));
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
651int 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));
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
708int 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
722int 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
739char *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));
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
767{
768 return db_getiflist_sorted(ifl, 0);
769}
770
771int 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));
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
807char *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
843int 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
848int 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"};
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)) {
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)) {
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)) {
899 return 0;
900 }
901 }
902
903 if (!intransaction) {
904 return db_committransaction();
905 }
906 return 1;
907}
908
909int 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
923int 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 */
938int 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
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
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)) {
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)) {
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)) {
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)) {
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)) {
1041 return 0;
1042 }
1043 }
1044
1045 return db_committransaction();
1046}
1047
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
1097int db_vacuum(void)
1098{
1099 if (debug) {
1100 printf("db: vacuum\n");
1101 }
1102 return db_exec("VACUUM");
1103}
1104
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));
1118 return 0;
1119 }
1120 db_intransaction = 1;
1121 return 1;
1122}
1123
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));
1138 /* execute rollback if commit failure left the transaction open */
1139 if (!sqlite3_get_autocommit(db)) {
1141 }
1142 db_errcode = rc;
1143 return 0;
1144 }
1145 return 1;
1146}
1147
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));
1163 return 0;
1164 }
1165 return 1;
1166}
1167
1168int 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
1182int 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
1192void 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);
1208 }
1209}
1210#endif
1211
1212int 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
1217int 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);
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);
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));
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
1329void 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
1373int 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
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
1404unsigned 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
1425char *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}
double timeused(const char *func, const int reset)
Definition: common.c:298
void panicexit(const char *sourcefile, const int sourceline)
Definition: common.c:277
char errorstring[1024]
Definition: common.c:6
int printe(const PrintType type)
Definition: common.c:14
int debug
Definition: common.c:8
char * getversion(void)
Definition: common.c:285
CFG cfg
Definition: common.c:4
char * strncpy_nt(char *dest, const char *src, size_t n)
Definition: common.c:253
#define SLOWDBWARNLIMIT
Definition: common.h:248
#define MAX32
Definition: common.h:214
#define SQLDBVERSION
Definition: common.h:192
#define __func__
Definition: common.h:51
#define DATABASEFILE
Definition: common.h:72
@ PT_Error
Definition: common.h:353
@ PT_Warning
Definition: common.h:352
#define DBREADTIMEOUTSECS
Definition: common.h:253
int db_close(void)
Definition: dbsql.c:248
int db_getcounters(const char *iface, uint64_t *rxcounter, uint64_t *txcounter)
Definition: dbsql.c:616
int db_open_ro(void)
Definition: dbsql.c:11
int db_removeoldentries_top(void)
Definition: dbsql.c:1048
int db_getiflist(iflist **ifl)
Definition: dbsql.c:766
int db_addtraffic_dated(const char *iface, const uint64_t rx, const uint64_t tx, const uint64_t timestamp)
Definition: dbsql.c:848
int db_setcreation(const char *iface, const time_t timestamp)
Definition: dbsql.c:909
char * db_get_date_generator(const int range, const short direct, const char *nowdate)
Definition: dbsql.c:807
unsigned int getqueryinterfacecount(const char *input)
Definition: dbsql.c:1404
int db_open_rw(const int createifnotfound)
Definition: dbsql.c:16
sqlite3_int64 db_getinterfaceid(const char *iface, const int createifnotfound)
Definition: dbsql.c:466
int db_removeinterface(const char *iface)
Definition: dbsql.c:382
int db_validate(const int readonly)
Definition: dbsql.c:131
int db_renameinterface(const char *iface, const char *newifname)
Definition: dbsql.c:396
int dbdatalistadd(dbdatalist **dbdata, const uint64_t rx, const uint64_t tx, const time_t timestamp, const int64_t rowid)
Definition: dbsql.c:1373
char * db_getinterfaceidin(const char *iface)
Definition: dbsql.c:496
int db_errcode
Definition: dbsql.c:8
int db_settotal(const char *iface, const uint64_t rx, const uint64_t tx)
Definition: dbsql.c:923
int db_setalias(const char *iface, const char *alias)
Definition: dbsql.c:708
int db_exec(const char *sql)
Definition: dbsql.c:270
int db_insertdata(const char *table, const char *iface, const uint64_t rx, const uint64_t tx, const uint64_t timestamp)
Definition: dbsql.c:938
int db_addinterface(const char *iface)
Definition: dbsql.c:370
uint64_t db_getinterfacecount(void)
Definition: dbsql.c:414
int db_setactive(const char *iface, const int active)
Definition: dbsql.c:574
uint64_t db_getinterfacecountbyname(const char *iface)
Definition: dbsql.c:419
char * db_getinfo(const char *name)
Definition: dbsql.c:739
int db_create(void)
Definition: dbsql.c:303
int db_removeoldentries(void)
Definition: dbsql.c:973
int db_isdiskfull(int errcode)
Definition: dbsql.c:1182
int db_begintransaction(void)
Definition: dbsql.c:1105
int db_setcounters(const char *iface, const uint64_t rxcounter, const uint64_t txcounter)
Definition: dbsql.c:602
int db_setpragmas(void)
Definition: dbsql.c:171
int db_iserrcodefatal(int errcode)
Definition: dbsql.c:1168
int db_getiflist_sorted(iflist **ifl, const int orderbytraffic)
Definition: dbsql.c:771
int db_setupdated(const char *iface, const time_t timestamp)
Definition: dbsql.c:588
int db_vacuum(void)
Definition: dbsql.c:1097
int db_setinfo(const char *name, const char *value, const int createifnotfound)
Definition: dbsql.c:722
int db_rollbacktransaction(void)
Definition: dbsql.c:1148
int db_setinterfacebyalias(char *iface, const char *alias, const int matchmethod)
Definition: dbsql.c:527
int db_addtraffic(const char *iface, const uint64_t rx, const uint64_t tx)
Definition: dbsql.c:843
int db_getdata(dbdatalist **dbdata, dbdatalistinfo *listinfo, const char *iface, const char *table, const uint32_t resultlimit)
Definition: dbsql.c:1212
char * getifaceinquery(const char *input)
Definition: dbsql.c:1425
void updatelistinfo(dbdatalistinfo *listinfo, const uint64_t rx, const uint64_t tx, const time_t timestamp)
Definition: dbsql.c:1329
sqlite3 * db
Definition: dbsql.c:7
int db_intransaction
Definition: dbsql.c:9
int db_getdata_range(dbdatalist **dbdata, dbdatalistinfo *listinfo, const char *iface, const char *table, const uint32_t resultlimit, const char *databegin, const char *dataend)
Definition: dbsql.c:1217
int db_open(const int createifnotfound, const int readonly)
Definition: dbsql.c:21
void dbdatalistfree(dbdatalist **dbdata)
Definition: dbsql.c:1393
int db_getinterfaceinfo(const char *iface, interfaceinfo *info)
Definition: dbsql.c:651
int db_committransaction(void)
Definition: dbsql.c:1124
void iflistfree(iflist **ifl)
Definition: iflist.c:43
int iflistadd(iflist **ifl, const char *iface, const uint32_t bandwidth)
Definition: iflist.c:4
int spacecheck(const char *path)
Definition: misc.c:20
int32_t monthrotateyears
Definition: common.h:315
int32_t dailydays
Definition: common.h:323
int32_t monthrotate
Definition: common.h:315
char dbdir[512]
Definition: common.h:310
int32_t yearlyyears
Definition: common.h:323
int32_t updateinterval
Definition: common.h:321
int32_t hourlydays
Definition: common.h:323
int32_t dbsynchronous
Definition: common.h:316
int32_t useutc
Definition: common.h:316
int32_t fiveminutehours
Definition: common.h:323
int32_t waldb
Definition: common.h:316
char dbtzmodifier[14]
Definition: common.h:310
int32_t monthlymonths
Definition: common.h:323
int32_t topdayentries
Definition: common.h:323
time_t timestamp
Definition: dbsql.h:8
struct dbdatalist * next
Definition: dbsql.h:11
int64_t rowid
Definition: dbsql.h:9
uint64_t rx
Definition: dbsql.h:10
uint64_t tx
Definition: dbsql.h:10
time_t mintime
Definition: dbsql.h:16
time_t maxtime
Definition: dbsql.h:16
uint64_t mintx
Definition: dbsql.h:17
uint64_t min
Definition: dbsql.h:19
uint64_t maxrx
Definition: dbsql.h:18
uint64_t minrx
Definition: dbsql.h:17
uint64_t sumrx
Definition: dbsql.h:20
uint64_t sumtx
Definition: dbsql.h:20
uint32_t count
Definition: dbsql.h:15
uint64_t max
Definition: dbsql.h:19
uint64_t maxtx
Definition: dbsql.h:18
Definition: iflist.h:4
int active
Definition: dbsql.h:25
char alias[32]
Definition: dbsql.h:24
uint64_t rxcounter
Definition: dbsql.h:27
time_t created
Definition: dbsql.h:26
time_t updated
Definition: dbsql.h:26
uint64_t txtotal
Definition: dbsql.h:28
char name[32]
Definition: dbsql.h:24
uint64_t rxtotal
Definition: dbsql.h:28
uint64_t txcounter
Definition: dbsql.h:27