From 37cd413199a9731c5e198e4da246db942f44dbcb Mon Sep 17 00:00:00 2001 From: Teemu Toivola Date: Sun, 22 Jan 2017 01:50:45 +0200 Subject: [PATCH] implement functions and tests for data retrieval --- src/dbsql.c | 139 +++++++++++++++++++++++++++++++++++++++++--- src/dbsql.h | 18 ++++++ tests/dbsql_tests.c | 51 ++++++++++++++++ 3 files changed, 200 insertions(+), 8 deletions(-) diff --git a/src/dbsql.c b/src/dbsql.c index 1471b0b..2f72ada 100644 --- a/src/dbsql.c +++ b/src/dbsql.c @@ -100,7 +100,7 @@ int db_exec(const char *sql) sqlite3_stmt *sqlstmt; rc = sqlite3_prepare_v2(db, sql, -1, &sqlstmt, NULL); - if (rc) { + if (rc != SQLITE_OK) { if (debug) printf("Error: Exec prepare \"%s\" failed (%d): %s\n", sql, rc, sqlite3_errmsg(db)); return 0; @@ -233,7 +233,7 @@ uint64_t db_getinterfacecountbyname(const char *iface) sqlite3_snprintf(512, sql, "select count(*) from interface"); } rc = sqlite3_prepare_v2(db, sql, -1, &sqlstmt, NULL); - if (rc) { + if (rc != SQLITE_OK) { return 0; } if (sqlite3_column_count(sqlstmt) != 1) { @@ -256,7 +256,7 @@ sqlite3_int64 db_getinterfaceid(const char *iface, const int createifnotfound) sqlite3_snprintf(512, sql, "select id from interface where name='%q'", iface); rc = sqlite3_prepare_v2(db, sql, -1, &sqlstmt, NULL); - if (!rc) { + if (rc == SQLITE_OK) { if (sqlite3_step(sqlstmt) == SQLITE_ROW) { ifaceid = sqlite3_column_int64(sqlstmt, 0); } @@ -317,10 +317,11 @@ int db_getcounters(const char *iface, uint64_t *rxcounter, uint64_t *txcounter) sqlite3_snprintf(512, sql, "select rxcounter, txcounter from interface where id=%"PRId64";", (int64_t)ifaceid); rc = sqlite3_prepare_v2(db, sql, -1, &sqlstmt, NULL); - if (rc) { + if (rc != SQLITE_OK) { return 0; } if (sqlite3_column_count(sqlstmt) != 2) { + sqlite3_finalize(sqlstmt); return 0; } if (sqlite3_step(sqlstmt) == SQLITE_ROW) { @@ -346,7 +347,7 @@ int db_getinterfaceinfo(const char *iface, interfaceinfo *info) 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); rc = sqlite3_prepare_v2(db, sql, -1, &sqlstmt, NULL); - if (rc) { + if (rc != SQLITE_OK) { return 0; } if (sqlite3_column_count(sqlstmt) != 9) { @@ -418,7 +419,7 @@ char *db_getinfo(const char *name) sqlite3_snprintf(512, sql, "select value from info where name='%q';", name); rc = sqlite3_prepare_v2(db, sql, -1, &sqlstmt, NULL); - if (rc) { + if (rc != SQLITE_OK) { return buffer; } if (sqlite3_step(sqlstmt) == SQLITE_ROW) { @@ -435,12 +436,12 @@ int db_getiflist(dbiflist **dbifl) { int rc; char sql[512]; - static sqlite3_stmt *sqlstmt; + sqlite3_stmt *sqlstmt; sqlite3_snprintf(512, sql, "select name from interface order by name desc;"); rc = sqlite3_prepare_v2(db, sql, -1, &sqlstmt, NULL); - if (rc) { + if (rc != SQLITE_OK ) { return -1; } @@ -798,3 +799,125 @@ void dbiflistfree(dbiflist **dbifl) free(dbifl_prev); } } + +int db_getdata(dbdatalist **dbdata, dbdatalistinfo *listinfo, const char *iface, const char *table, const uint32_t resultlimit, const int reverse) +{ + int ret = 1; + char sql[512], limit[64]; + sqlite3_int64 ifaceid = 0; + sqlite3_stmt *sqlstmt; + time_t timestamp; + uint64_t rx, tx; + + listinfo->count = 0; + + ifaceid = db_getinterfaceid(iface, 0); + if (ifaceid == 0) { + return 0; + } + + /* TODO: add table validation here */ + + if (resultlimit > 0) { + snprintf(limit, 64, "limit %"PRIu32"", resultlimit); + } else { + limit[0] = '\0'; + } + + /* note that using the linked list reverses the order */ + /* most recent last in the linked list is considered the normal order */ + if (reverse == 0) { + sqlite3_snprintf(512, sql, "select strftime('%%s', date, 'utc'), rx, tx from %s where interface=%"PRId64" order by date desc %s;", table, (int64_t)ifaceid, limit); + } else { + sqlite3_snprintf(512, sql, "select * from (select strftime('%%s', date, 'utc'), rx, tx from %s where interface=%"PRId64" order by date desc %s) order by date asc;", table, (int64_t)ifaceid, limit); + } + + if (sqlite3_prepare_v2(db, sql, -1, &sqlstmt, NULL) != SQLITE_OK) { + return 0; + } + + if (sqlite3_column_count(sqlstmt) != 3) { + sqlite3_finalize(sqlstmt); + return 0; + } + + while (sqlite3_step(sqlstmt) == SQLITE_ROW) { + timestamp = (time_t)sqlite3_column_int64(sqlstmt, 0); + rx = (uint64_t)sqlite3_column_int64(sqlstmt, 1); + tx = (uint64_t)sqlite3_column_int64(sqlstmt, 2); + if (!dbdatalistadd(dbdata, rx, tx, timestamp)) { + /* TODO: some info print may be needed here */ + ret = 0; + break; + } + updatelistinfo(listinfo, rx, tx, timestamp); + } + sqlite3_finalize(sqlstmt); + + /* TODO: should list be cleaned if it doesn't contain everything? */ + + return ret; +} + +void updatelistinfo(dbdatalistinfo *listinfo, const uint64_t rx, const uint64_t tx, const time_t timestamp) +{ + if (listinfo->count == 0) { + listinfo->maxtime = timestamp; + listinfo->mintime = timestamp; + listinfo->maxrx = rx; + listinfo->minrx = rx; + listinfo->maxtx = tx; + listinfo->mintx = tx; + } else { + if (timestamp > listinfo->maxtime) { + listinfo->maxtime = timestamp; + } + if (timestamp < listinfo->mintime) { + listinfo->mintime = timestamp; + } + if (rx < listinfo->minrx) { + listinfo->minrx = rx; + } + if (tx < listinfo->mintx) { + listinfo->mintx = tx; + } + if (rx > listinfo->maxrx) { + listinfo->maxrx = rx; + } + if (tx > listinfo->maxtx) { + listinfo->maxtx = tx; + } + } + listinfo->count++; +} + +int dbdatalistadd(dbdatalist **dbdata, const uint64_t rx, const uint64_t tx, const time_t timestamp) +{ + dbdatalist *newdata; + + newdata = malloc(sizeof(dbdatalist)); + if (newdata == NULL) { + return 0; + } + + newdata->next = *dbdata; + *dbdata = newdata; + + newdata->timestamp = timestamp; + newdata->rx = rx; + newdata->tx = tx; + + return 1; +} + +void dbdatalistfree(dbdatalist **dbdata) +{ + dbdatalist *dbdata_prev; + + while (*dbdata != NULL) { + dbdata_prev = *dbdata; + *dbdata = (*dbdata)->next; + free(dbdata_prev); + } + +} diff --git a/src/dbsql.h b/src/dbsql.h index 27b95bc..0203242 100644 --- a/src/dbsql.h +++ b/src/dbsql.h @@ -8,6 +8,19 @@ typedef struct dbiflist { struct dbiflist *next; } dbiflist; +typedef struct dbdatalist { + time_t timestamp; + uint64_t rx, tx; + struct dbdatalist *next; +} dbdatalist; + +typedef struct dbdatalistinfo { + uint32_t count; + time_t maxtime, mintime; + uint64_t minrx, mintx; + uint64_t maxrx, maxtx; +} dbdatalistinfo; + typedef struct interfaceinfo { char name[32], alias[32]; int active; @@ -48,6 +61,11 @@ int db_rollbacktransaction(void); int dbiflistadd(dbiflist **dbifl, const char *iface); void dbiflistfree(dbiflist **dbifl); +int db_getdata(dbdatalist **dbdata, dbdatalistinfo *listinfo, const char *iface, const char *table, const uint32_t limit, const int reverse); +void updatelistinfo(dbdatalistinfo *listinfo, const uint64_t rx, const uint64_t tx, const time_t timestamp); +int dbdatalistadd(dbdatalist **dbdata, const uint64_t rx, const uint64_t tx, const time_t timestamp); +void dbdatalistfree(dbdatalist **dbdata); + /* global db */ sqlite3 *db; diff --git a/tests/dbsql_tests.c b/tests/dbsql_tests.c index a9cf008..09626b8 100644 --- a/tests/dbsql_tests.c +++ b/tests/dbsql_tests.c @@ -719,6 +719,56 @@ START_TEST(db_data_can_be_inserted) } END_TEST +START_TEST(db_data_can_be_retrieved) +{ + int ret;; + dbdatalist *datalist = NULL, *datalist_iterator = NULL; + dbdatalistinfo datainfo; + + defaultcfg(); + + ret = db_open(1); + ck_assert_int_eq(ret, 1); + ret = db_addinterface("eth0"); + ck_assert_int_eq(ret, 1); + + ret = db_insertdata("hour", "eth0", 1, 2, 3); + ck_assert_int_eq(ret, 1); + + ret = db_insertdata("hour", "eth0", 10, 20, 10000); + ck_assert_int_eq(ret, 1); + + ret = db_getdata(&datalist, &datainfo, "eth0", "hour", 2, 0); + ck_assert_int_eq(ret, 1); + + ck_assert_int_eq(datainfo.count, 2); + ck_assert_int_eq(datainfo.minrx, 1); + ck_assert_int_eq(datainfo.maxrx, 10); + ck_assert_int_eq(datainfo.mintx, 2); + ck_assert_int_eq(datainfo.maxtx, 20); + /* db_insertdata rounds the timestamps to full hours */ + ck_assert_int_eq((int)datainfo.maxtime, 7200); + ck_assert_int_eq((int)datainfo.mintime, 0); + + datalist_iterator = datalist; + + ck_assert_int_eq(datalist_iterator->rx, 1); + ck_assert_int_eq(datalist_iterator->tx, 2); + ck_assert_int_eq(datalist_iterator->timestamp, 0); + + datalist_iterator = datalist_iterator->next; + + ck_assert_int_eq(datalist_iterator->rx, 10); + ck_assert_int_eq(datalist_iterator->tx, 20); + ck_assert_int_eq(datalist_iterator->timestamp, 7200); + + dbdatalistfree(&datalist); + + ret = db_close(); + ck_assert_int_eq(ret, 1); +} +END_TEST + void add_dbsql_tests(Suite *s) { TCase *tc_dbsql = tcase_create("DB SQL"); @@ -753,5 +803,6 @@ void add_dbsql_tests(Suite *s) tcase_add_test(tc_dbsql, db_getiflist_lists_interfaces); tcase_add_test(tc_dbsql, db_maintenance_does_not_fault); tcase_add_test(tc_dbsql, db_data_can_be_inserted); + tcase_add_test(tc_dbsql, db_data_can_be_retrieved); suite_add_tcase(s, tc_dbsql); } -- 2.40.0