From 5a8fa2161ba5b53ab05e43649e8ae5532cf0a8df Mon Sep 17 00:00:00 2001
From: Trygve Laugstøl <trygvis@inamo.no>
Date: Thu, 26 Feb 2015 08:43:27 +0100
Subject: o Adding utility to insert samples from json files.

---
 apps/CMakeLists.txt    |  5 +++-
 apps/sm-db-insert.cpp  | 78 ++++++++++++++++++++++++++++++++++++++++++++++++++
 apps/sm-get-value.cpp  | 21 ++++++++++----
 apps/soil-moisture.sql | 24 ++++++++++++++++
 4 files changed, 122 insertions(+), 6 deletions(-)
 create mode 100644 apps/sm-db-insert.cpp
 create mode 100644 apps/soil-moisture.sql

diff --git a/apps/CMakeLists.txt b/apps/CMakeLists.txt
index 9d68f79..31f7939 100644
--- a/apps/CMakeLists.txt
+++ b/apps/CMakeLists.txt
@@ -1,4 +1,7 @@
-set(APPS sm-get-value ble-inspect-device)
+set(APPS
+    ble-inspect-device
+    sm-db-insert
+    sm-get-value)
 set(shared_sources SoilMoisture.cpp)
 
 # Boost
diff --git a/apps/sm-db-insert.cpp b/apps/sm-db-insert.cpp
new file mode 100644
index 0000000..a3ebb76
--- /dev/null
+++ b/apps/sm-db-insert.cpp
@@ -0,0 +1,78 @@
+#include <boost/optional.hpp>
+#include <boost/lexical_cast.hpp>
+#include <pqxx/connection.hxx>
+#include <pqxx/transaction.hxx>
+#include "json.hpp"
+#include <fstream>
+
+template<class T>
+using o = boost::optional<T>;
+using namespace std;
+using json = nlohmann::json;
+
+class missing_key : public runtime_error {
+public:
+    missing_key(const json::string_t key) : runtime_error("Missing key: " + key), key(key) {
+    }
+
+    const json::string_t key;
+};
+
+template<typename T>
+T get(json j, string key) {
+    auto ref = j[key];
+
+    if (ref.is_null()) {
+        throw missing_key(key);
+    }
+
+    return ref;
+}
+
+int main(int argc, char *argv[]) {
+    cout << "reading from " << argv[1] << endl;
+
+    fstream f(argv[1]);
+
+    json j;
+
+    j << f;
+
+    pqxx::connection c("host=localhost dbname=soil-moisture");
+
+    pqxx::work work(c);
+
+    string mac = j["mac"]; // "aa:bb:cc:dd:ee:ff";
+
+    auto rs = work.parameterized("select id from soil_moisture_device where mac=$1")(mac).exec();
+
+    if (!rs.size()) {
+        cout << "New device: " << mac << endl;
+
+        rs = work.parameterized("insert into soil_moisture_device(mac) values($1) returning id")(mac).exec();
+    }
+
+    auto deviceId = rs.begin()["id"].as<int>();
+
+    int sensor = j["sensor"];
+
+    rs = work.parameterized("select id from soil_moisture_sensor where device=$1 and sensor=$2")(deviceId)(sensor).exec();
+
+    if (!rs.size()) {
+        cout << "New sensor: " << sensor << endl;
+
+        rs = work.parameterized("insert into soil_moisture_sensor(device, sensor) values($1, $2) returning id")(deviceId)(sensor).exec();
+    }
+    auto sensorId = rs.begin()["id"].as<int>();
+
+    unsigned long timestamp = get<unsigned long>(j, "timestamp");
+    unsigned int value = get<unsigned int>(j, "value");
+
+    work.parameterized("insert into soil_moisture_sample(sensor, timestamp, value) values($1, $2, $3)")(sensorId)(timestamp)(value).exec();
+
+    cout << "Sample inserted" << endl;
+
+    work.commit();
+
+    return EXIT_SUCCESS;
+}
diff --git a/apps/sm-get-value.cpp b/apps/sm-get-value.cpp
index 97858f4..14a90de 100644
--- a/apps/sm-get-value.cpp
+++ b/apps/sm-get-value.cpp
@@ -18,7 +18,8 @@ using json = nlohmann::json;
 
 enum class Format {
     PLAIN,
-    JSON
+    JSON,
+    SQL
 };
 
 void validate(boost::any &v, const std::vector<std::string> &values, Format *, int) {
@@ -26,10 +27,12 @@ void validate(boost::any &v, const std::vector<std::string> &values, Format *, i
 
     const std::string &s = validators::get_single_string(values);
 
-    if (s == "json") {
-        v = boost::any(Format::JSON);
-    } else if (s == "plain") {
+    if (s == "plain") {
         v = boost::any(Format::PLAIN);
+    } else if (s == "json") {
+        v = boost::any(Format::JSON);
+    } else if (s == "sql") {
+        v = boost::any(Format::SQL);
     } else {
         throw validation_error(validation_error::invalid_option_value);
     }
@@ -43,8 +46,10 @@ string lexical_cast(const Format &arg) {
         return "plain";
     else if (arg == Format::JSON)
         return "json";
+    else if (arg == Format::SQL)
+        return "sql";
     else
-        throw std::runtime_error("Unknown Format value: " + to_string(static_cast<std::underlying_type<Format>::type>(arg)));
+        throw std::runtime_error("Unknown format value: " + lexical_cast<string>(arg));
 }
 
 }
@@ -98,6 +103,12 @@ void withConnection(BluetoothGatt &gatt) {
                 j["timestamp"] = timestamp;
                 j["value"] = value;
                 cout << j << endl;
+            } else if (format == Format::SQL) {
+                cout << "INSERT INTO soil_moisture_sample(device, sensor, timestamp, value) VALUES("
+                        << "'" << device.str() << "', "
+                        << sensor << ", "
+                        << timestamp << ", "
+                        << value << ";" << endl;
             }
         }
 
diff --git a/apps/soil-moisture.sql b/apps/soil-moisture.sql
new file mode 100644
index 0000000..7f16200
--- /dev/null
+++ b/apps/soil-moisture.sql
@@ -0,0 +1,24 @@
+DROP TABLE IF EXISTS soil_moisture_sample;
+DROP TABLE IF EXISTS soil_moisture_sensor;
+DROP TABLE IF EXISTS soil_moisture_device;
+
+DROP SEQUENCE IF EXISTS sm_id;
+CREATE SEQUENCE sm_id;
+
+CREATE TABLE soil_moisture_device (
+  id  BIGINT PRIMARY KEY NOT NULL DEFAULT nextval('sm_id'),
+  mac VARCHAR(100)
+);
+
+CREATE TABLE soil_moisture_sensor (
+  id     BIGINT PRIMARY KEY NOT NULL DEFAULT nextval('sm_id'),
+  device BIGINT             NOT NULL REFERENCES soil_moisture_device,
+  sensor BIGINT             NOT NULL
+);
+
+CREATE TABLE soil_moisture_sample (
+  sensor    BIGINT NOT NULL REFERENCES soil_moisture_sensor,
+  timestamp BIGINT NOT NULL,
+  value     BIGINT NOT NULL,
+  PRIMARY KEY (sensor, timestamp)
+) WITHOUT OIDS;
-- 
cgit v1.2.3