From 1b09c7d0547fb430e957b863bdbb3bf54c85f52a Mon Sep 17 00:00:00 2001
From: Trygve Laugstøl <trygvis@inamo.no>
Date: Thu, 26 Feb 2015 16:06:46 +0100
Subject: o Adding a tool to query the database and create a influxdb json
 array.

---
 apps/CMakeLists.txt   |  1 +
 apps/sm-db-select.cpp | 82 +++++++++++++++++++++++++++++++++++++++++++++++++++
 2 files changed, 83 insertions(+)
 create mode 100644 apps/sm-db-select.cpp

diff --git a/apps/CMakeLists.txt b/apps/CMakeLists.txt
index 31f7939..792e339 100644
--- a/apps/CMakeLists.txt
+++ b/apps/CMakeLists.txt
@@ -1,6 +1,7 @@
 set(APPS
     ble-inspect-device
     sm-db-insert
+    sm-db-select
     sm-get-value)
 set(shared_sources SoilMoisture.cpp)
 
diff --git a/apps/sm-db-select.cpp b/apps/sm-db-select.cpp
new file mode 100644
index 0000000..8bcce32
--- /dev/null
+++ b/apps/sm-db-select.cpp
@@ -0,0 +1,82 @@
+#include <boost/optional.hpp>
+#include <boost/lexical_cast.hpp>
+#include <pqxx/connection.hxx>
+#include <pqxx/transaction.hxx>
+#include "json.hpp"
+
+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[]) {
+    string mac = "aa:bb:cc:dd:ee:ff";
+    int sensor = 1;
+
+    json j;
+
+    pqxx::connection c("host=localhost dbname=soil-moisture");
+
+    pqxx::work work(c);
+
+    auto rs = work.parameterized("select id from soil_moisture_device where mac=$1")(mac).exec();
+
+    if (!rs.size()) {
+        cout << "Unknown device: " << mac << endl;
+        return EXIT_FAILURE;
+    }
+
+    auto deviceId = rs.begin()["id"].as<int>();
+
+    rs = work.parameterized("select id from soil_moisture_sensor where device=$1 and sensor=$2")(deviceId)(sensor).exec();
+
+    if (!rs.size()) {
+        cout << "Unknown sensor: " << sensor << endl;
+        return EXIT_FAILURE;
+    }
+
+    auto sensorId = rs.begin()["id"].as<int>();
+
+    rs = work.parameterized("select timestamp, sensor, value from soil_moisture_sample where sensor=$1")(sensorId).exec();
+
+    json points = json::array();
+    for (auto sample: rs) {
+        json s;
+        s.push_back(sample["timestamp"].as<unsigned long>());
+        s.push_back(sample["sensor"].as<unsigned long>());
+        s.push_back(sample["value"].as<unsigned long>());
+        points.push_back({s});
+    }
+
+    json o;
+    o["columns"] = json::array({"time", "sensor", "value"});
+    o["points"] = json(points);
+    j.push_back(o);
+
+    cout << "JSON" << endl;
+    cout << setw(2) << j << endl;
+    cout << "JSON" << endl;
+
+    work.commit();
+
+    return EXIT_SUCCESS;
+}
-- 
cgit v1.2.3