|
|
|
#include <stdio.h>
|
|
|
|
#include <string.h>
|
|
|
|
#include <unistd.h>
|
|
|
|
#include <fcntl.h>
|
|
|
|
#include <termios.h>
|
|
|
|
#include <stdlib.h>
|
|
|
|
#include <signal.h>
|
|
|
|
#include <time.h>
|
|
|
|
|
|
|
|
#include <sqlite3.h>
|
|
|
|
#include <stdbool.h>
|
|
|
|
|
|
|
|
#define WTHR_STATION_ID 0
|
|
|
|
#define TIMESTAMP_FIELD 1
|
|
|
|
#define DATA_VAL_FIELD 2
|
|
|
|
#define STATION_ID_FIELD 3
|
|
|
|
#define INSERT_HUM_TEMPL "INSERT INTO humidity(timestamp, value, station) VALUES(?, ?, ?)"
|
|
|
|
#define INSERT_TEMP_TEMPL "INSERT INTO temperature(timestamp, value, station) VALUES(?, ?, ?)"
|
|
|
|
|
|
|
|
sqlite3* get_db(void) {
|
|
|
|
sqlite3 *db;
|
|
|
|
char *zErrMsg = 0;
|
|
|
|
int rc;
|
|
|
|
|
|
|
|
rc = sqlite3_open("test.db", &db);
|
|
|
|
|
|
|
|
if( rc ) {
|
|
|
|
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
|
|
|
|
} else {
|
|
|
|
fprintf(stderr, "Opened database successfully\n");
|
|
|
|
}
|
|
|
|
|
|
|
|
return db;
|
|
|
|
}
|
|
|
|
|
|
|
|
int open_port(void) {
|
|
|
|
int fd; /* File descriptor for the port */
|
|
|
|
|
|
|
|
fd = open("/dev/ttyUSB0", O_RDWR | O_NOCTTY | O_NDELAY);
|
|
|
|
if (fd == -1) {
|
|
|
|
perror("open_port: Unable to open /dev/USB0 - ");
|
|
|
|
} else
|
|
|
|
fcntl(fd, F_SETFL, 0);
|
|
|
|
return (fd);
|
|
|
|
}
|
|
|
|
|
|
|
|
enum record_type {
|
|
|
|
R_TEMP,
|
|
|
|
R_REL_HUMIDITY
|
|
|
|
};
|
|
|
|
|
|
|
|
void ccleanup(sqlite3 *db) {
|
|
|
|
static bool setup = false;
|
|
|
|
static sqlite3 *saved;
|
|
|
|
|
|
|
|
if (!setup) {
|
|
|
|
saved = db;
|
|
|
|
setup = true;
|
|
|
|
} else {
|
|
|
|
sqlite3_close(db);
|
|
|
|
printf("Closed DB.\n");
|
|
|
|
}
|
|
|
|
|
|
|
|
}
|
|
|
|
|
|
|
|
static void call_cleanup(int signo) {
|
|
|
|
ccleanup(NULL);
|
|
|
|
exit(0);
|
|
|
|
}
|
|
|
|
|
|
|
|
struct query_result {
|
|
|
|
int argc;
|
|
|
|
char **argv;
|
|
|
|
char **azColName;
|
|
|
|
};
|
|
|
|
|
|
|
|
static int save_qry(void *retval, int argc, char **argv, char **azColName) {
|
|
|
|
struct query_result r;
|
|
|
|
r.argc = argc;
|
|
|
|
r.argv = argv;
|
|
|
|
r.azColName = azColName;
|
|
|
|
|
|
|
|
struct query_result* ret = retval;
|
|
|
|
*ret = r;
|
|
|
|
|
|
|
|
return 0;
|
|
|
|
}
|
|
|
|
|
|
|
|
static int callback(void *retval, 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;
|
|
|
|
}
|
|
|
|
|
|
|
|
void initialise_db(sqlite3* db) {
|
|
|
|
char *query = "SELECT name from sqlite_master WHERE name = 'temperature'";
|
|
|
|
char *zErrMsg = 0;
|
|
|
|
int rc;
|
|
|
|
struct query_result r;
|
|
|
|
|
|
|
|
rc = sqlite3_exec(db, query, save_qry, &r, &zErrMsg);
|
|
|
|
|
|
|
|
if (rc != SQLITE_OK) {
|
|
|
|
fprintf(stderr, "SQL: %s\n", zErrMsg);
|
|
|
|
sqlite3_free(zErrMsg);
|
|
|
|
}
|
|
|
|
|
|
|
|
if (r.argc == 0) {
|
|
|
|
char *query = "CREATE TABLE temperature (" \
|
|
|
|
"timestamp INT PRIMARY KEY NOT NULL," \
|
|
|
|
"value INT NOT NULL," \
|
|
|
|
"station INT NOT NULL," \
|
|
|
|
"FOREIGN KEY(station) REFERENCES station(id)" \
|
|
|
|
" )";
|
|
|
|
rc = sqlite3_exec(db, query, save_qry, &r, &zErrMsg);
|
|
|
|
if (rc != SQLITE_OK) {
|
|
|
|
fprintf(stderr, "SQL: %s\n", zErrMsg);
|
|
|
|
sqlite3_free(zErrMsg);
|
|
|
|
call_cleanup(1);
|
|
|
|
}
|
|
|
|
}
|
|
|
|
query = "SELECT name from sqlite_master WHERE name = 'humidity'";
|
|
|
|
|
|
|
|
struct query_result re;
|
|
|
|
rc = sqlite3_exec(db, query, save_qry, &re, &zErrMsg);
|
|
|
|
|
|
|
|
if (rc != SQLITE_OK) {
|
|
|
|
fprintf(stderr, "SQL: %s\n", zErrMsg);
|
|
|
|
sqlite3_free(zErrMsg);
|
|
|
|
call_cleanup(1);
|
|
|
|
}
|
|
|
|
|
|
|
|
if (re.argc == 0) {
|
|
|
|
query = "CREATE TABLE humidity(" \
|
|
|
|
"timestamp INT PRIMARY KEY NOT NULL," \
|
|
|
|
"value INT NOT NULL," \
|
|
|
|
"station INT NOT NULL," \
|
|
|
|
"FOREIGN KEY(station) REFERENCES station(id)" \
|
|
|
|
" )";
|
|
|
|
rc = sqlite3_exec(db, query, save_qry, &r, &zErrMsg);
|
|
|
|
if (rc != SQLITE_OK) {
|
|
|
|
fprintf(stderr, "SQL: %s\n", zErrMsg);
|
|
|
|
sqlite3_free(zErrMsg);
|
|
|
|
call_cleanup(1);
|
|
|
|
}
|
|
|
|
}
|
|
|
|
}
|
|
|
|
|
|
|
|
int check_error(int rc) {
|
|
|
|
if (rc != SQLITE_OK) {
|
|
|
|
fprintf(stderr, "SQL Error: %d\n", rc);
|
|
|
|
call_cleanup(1);
|
|
|
|
}
|
|
|
|
return 0;
|
|
|
|
}
|
|
|
|
|
|
|
|
int insert_rec(sqlite3 *db, char* sqlt, int value, int station, int timestamp) {
|
|
|
|
sqlite3_stmt * statement;
|
|
|
|
const char *tail = 0;
|
|
|
|
int rc = sqlite3_prepare_v2(db, sqlt, -1, &statement, &tail);
|
|
|
|
check_error(rc);
|
|
|
|
rc = sqlite3_bind_int(statement, DATA_VAL_FIELD, value);
|
|
|
|
check_error(rc);
|
|
|
|
rc = sqlite3_bind_int(statement, STATION_ID_FIELD, station);
|
|
|
|
check_error(rc);
|
|
|
|
rc = sqlite3_bind_int(statement, TIMESTAMP_FIELD, timestamp);
|
|
|
|
check_error(rc);
|
|
|
|
rc = sqlite3_step(statement);
|
|
|
|
if (rc != SQLITE_DONE) {
|
|
|
|
check_error(rc);
|
|
|
|
}
|
|
|
|
rc = sqlite3_finalize(statement);
|
|
|
|
check_error(rc);
|
|
|
|
return 0;
|
|
|
|
}
|
|
|
|
|
|
|
|
int main (int argc, char **arv) {
|
|
|
|
sqlite3 *db = get_db();
|
|
|
|
ccleanup(db);
|
|
|
|
initialise_db(db);
|
|
|
|
|
|
|
|
signal(SIGINT, call_cleanup);
|
|
|
|
|
|
|
|
FILE *serial = fdopen(open_port(), "r");
|
|
|
|
|
|
|
|
char string[200];
|
|
|
|
int val;
|
|
|
|
|
|
|
|
int time_now = 0;
|
|
|
|
int last_time = 0;
|
|
|
|
while(1) {
|
|
|
|
last_time = time_now;
|
|
|
|
time_now = time(NULL);
|
|
|
|
memset(string, 0, sizeof(char) * 200);
|
|
|
|
fscanf(serial, "%s%d", string, &val);
|
|
|
|
if (!strcmp(string, "temp:")) {
|
|
|
|
printf("Temperature: %d\n", val);
|
|
|
|
if (last_time != time_now) {
|
|
|
|
insert_rec(db, INSERT_TEMP_TEMPL, val, WTHR_STATION_ID, time_now);
|
|
|
|
}
|
|
|
|
}
|
|
|
|
if (!strcmp(string, "humi:")) {
|
|
|
|
printf("Relative Humidity: %d\n", val);
|
|
|
|
if (last_time != time_now) {
|
|
|
|
insert_rec(db, INSERT_HUM_TEMPL, val, WTHR_STATION_ID, time_now);
|
|
|
|
}
|
|
|
|
}
|
|
|
|
}
|
|
|
|
|
|
|
|
fclose(serial);
|
|
|
|
}
|