CoffeePi/CoffeeCode/database.cpp

/*
 * database.cpp
 *
 *  Created on: Dec 4, 2015
 *      Author: Philipp Hinz
 */

#include <cstdio>
#include <stdlib.h>
#include <inttypes.h>
#include <cstdarg>
#include <cstring>
#include <string>
#include <stdexcept>
#include <pthread.h>
#include <sys/time.h>
#include "sqlite/sqlite3.h"
#include "global.h"
#include "database.h"
#include "logger.h"

using namespace std;

sqlite3 *db; /**< The database connector */

/**
 * Helper function to allow the use of vsprintf() with a string
 * See: http://codereview.stackexchange.com/questions/52522/mimic-sprintf-with-stdstring-output
 */

std::string string_vsprintf(const char* format, std::va_list args) {
	va_list tmp_args; //unfortunately you cannot consume a va_list twice
	va_copy(tmp_args, args); //so we have to copy it
	const int required_len = vsnprintf(nullptr, 0, format, tmp_args) + 1;
	va_end(tmp_args);

	std::string buf(required_len, '\0');
	if (std::vsnprintf(&buf[0], buf.size(), format, args) < 0) {
		throw std::runtime_error { "string_vsprintf encoding error" };
	}
	return buf;
}

/**
 * Allows the format of printf but returns a string
 * Source: http://codereview.stackexchange.com/questions/52522/mimic-sprintf-with-stdstring-output
 * @return printf formatted string
 * @param format input string as char array
 */

std::string string_sprintf(const char* format, ...)
		__attribute__ ((format (printf, 1, 2)));

std::string string_sprintf(const char* format, ...) {
	std::va_list args;
	va_start(args, format);
	std::string str { string_vsprintf(format, args) };
	va_end(args);
	return str;
}

static int callback(void *NotUsed, int argc, char **argv, char **azColName) {
	int i;
	for (i = 0; i < argc; i++) {
		printf("%s = '%s'\n", azColName[i], argv[i] ? argv[i] : "NULL");
	}
	printf("\n");
	return 0;
}

/**
 * Opens and initializes a sqlite3 database connection
 * @return 0 on success
 */

int sqlOpen() {
	int rc = sqlite3_open(SQL_DATABASE, &db);
	if (rc) {
		logger_error("Can't open database: %s\n", sqlite3_errmsg(db));
		sqlite3_close(db);
		return (1);
	}
	logger(V_BASIC, "Successfully opened SQLite connection to %s\n",
	SQL_DATABASE);
	return 0;
}

/**
 * Closes the database connection
 */

void sqlClose() {
	sqlite3_close(db);
	logger(V_BASIC, "Closed SQLite connection.\n");
}

/**
 * Executes a SQL statement
 * @param *query SQL query string
 * @return 0 on success
 */

int sqlExecute(string query) {
	char *zErrMsg = 0;
	int rc;
	struct timeval tv1, tv2;

	pthread_mutex_lock(&mutex);

	logger(V_SQL, "Executing SQL Query: %s\n", query.c_str());

	gettimeofday(&tv1, NULL);
	rc = sqlite3_exec(db, query.c_str(), NULL, 0, &zErrMsg);
	gettimeofday(&tv2, NULL);

	if (verbose == V_SQL)
		printf("Time taken in execution = %f seconds\n",
				(double) (tv2.tv_usec - tv1.tv_usec) / 1000000
						+ (double) (tv2.tv_sec - tv1.tv_sec));

	pthread_mutex_unlock(&mutex);
	if (rc != SQLITE_OK) {
		logger_error("SQL error (%d): %s\n", rc, zErrMsg);
		sqlite3_free(zErrMsg);
		return rc;
	}
	return 0;
}

int sqlExecute2(char *query) {
	char *zErrMsg = 0;
	int rc;

	logger(V_SQL, "Executing SQL Query: %s\n", query);

	rc = sqlite3_exec(db, query, &callback, 0, &zErrMsg);
	if (rc != SQLITE_OK) {
		logger_error("SQL error (%d): %s\n", rc, zErrMsg);
		sqlite3_free(zErrMsg);
		return rc;
	}
	return 0;
}

/**
 * Sets up the database and creates the needed structure
 * @return 0 on success
 */

int sqlSetup() {
	sqlExecute("CREATE TABLE IF NOT EXISTS nodes "
			"(id INTEGER PRIMARY KEY ASC AUTOINCREMENT, "
			"guid INTEGER UNIQUE, "
			"term INTEGER, "
			"terminal NUMERIC, "
			"devclass INTEGER, "
			"devtype INTEGER, "
			"hw INTEGER, "
			"fwmajor INTEGER, "
			"fwminor INTEGER, "
			"lastseen INTEGER, "
			"tableversion INTEGER, "
			"clockspeed INTEGER, "
			"voltage NUMERIC);");
	sqlExecute("CREATE TABLE IF NOT EXISTS config "
			"(id INTEGER PRIMARY KEY, "
			"value NUMERIC);");
	sqlExecute("CREATE TABLE IF NOT EXISTS monitorlog "
			"(id INTEGER PRIMARY KEY ASC AUTOINCREMENT, "
			"time NUMERIC, "
			"node INTEGER, "
			"type INTEGER, "
			"value NUMERIC, "
			"weight INTEGER);");
	sqlExecute("CREATE TABLE IF NOT EXISTS counters "
			"(id INTEGER PRIMARY KEY ASC, "
			"por INTEGER, "
			"wdr INTEGER, "
			"bodr INTEGER, "
			"er INTEGER, "
			"asc INTEGER, "
			"uptime INTEGER, "
			"rxerr INTEGER, "
			"txerr INTEGER, "
			"rx INTEGER, "
			"tx INTEGER);");
	return 0;
}

/**
 * Reads a configuration key from the database
 * @param id Config ID key
 * @return value as integer (up to 64bit), 0 for not found
 */

uint64_t sqlGetConf(config_key_t id) {
	sqlite3_stmt *stmt;
	uint64_t out;
	string query = string_sprintf("SELECT id, value FROM config "
			"WHERE id = %d", id);
	int rc = sqlite3_prepare_v2(db, query.c_str(), -1, &stmt, NULL);
	if (rc != SQLITE_OK) {
		logger_error("SQL error (%d): %s\n", rc, sqlite3_errmsg(db));
		sqlite3_finalize(stmt);
		return 0;
	}

	rc = sqlite3_step(stmt);
	if (rc != SQLITE_ROW && rc != SQLITE_DONE) {
		logger_error("SQL error (%d): %s\n", rc, sqlite3_errmsg(db));
		sqlite3_finalize(stmt);
		return 0;
	}
	if (rc == SQLITE_DONE) { // no results
		sqlite3_finalize(stmt);
		return 0;
	}

	out = sqlite3_column_int64(stmt, 1);
	sqlite3_finalize(stmt);
	return out;
}

/**
 * Saves a configuration key to the database
 * @param id Config ID key
 * @param value Integer value
 * @return 0 on success
 */

int sqlSetConf(config_key_t id, uint64_t value) {
	int rc = sqlExecute(string_sprintf("REPLACE INTO config (id, value) "
			"VALUES (%d, %lld);", id, value));
	if (rc != SQLITE_OK)
		return EXIT_FAILURE;
	return EXIT_SUCCESS;
}

/**
 * Converts the GUID from int to char array
 * @param *guid Pointer to the uint8_t GUID
 * @return pointer to the converted string
 */

char *inttochar(uint8_t *guid) {
	char *out = new char[9];
	for (int i = 0; i < 8; i++) {
		out[i] = (char) guid[i];
	}
	out[8] = '\0';
	return out;
}

/**
 * Converts the GUID from char to int array
 * @param *guid Pointer to the char GUID
 * @return pointer to the converted int array
 */

uint8_t *chartoint(const unsigned char *guid) {
	uint8_t *out = new uint8_t[8];
	for (int i = 0; i < 8; i++) {
		out[i] = (uint8_t) guid[i];
	}
	return out;
}

/**
 * Converts the GUID from int64 to int8 array
 * @param guid 64bit GUID
 * @return pointer to the converted int array
 */

uint8_t *int64to8bit(sqlite_int64 guid) {
	uint8_t *out = new uint8_t[8];
	for (int i = 7; i >= 0; i--) {
		out[i] = guid & 0xff;
		guid = guid >> 8;
	}
	return out;
}

sqlite_int64 int8to64bit(uint8_t *guid) {
	sqlite_int64 out = 0;
	for (int i = 0; i < 8; i++) {
		out |= guid[i] << (8 * (7 - i));
	}
	return out;
}

void sqltest() {
	sqlSetup();
	//sqlExecute2("SELECT * FROM nodes;");
}