1 files changed, 84 insertions, 0 deletions
diff --git a/apps/sm-db-select.cpp b/apps/sm-db-select.cpp
new file mode 100644
index 0000000..2390733
--- /dev/null
+++ b/apps/sm-db-select.cpp
@@ -0,0 +1,84 @@
+#include <boost/optional.hpp>
+#include <boost/lexical_cast.hpp>
+#include <pqxx/connection.hxx>
+#include <pqxx/transaction.hxx>
+#include "json.hpp"
+#include "apps.h"
+
+namespace trygvis {
+namespace apps {
+
+template <class T>
+using o = boost::optional<T>;
+using namespace std;
+using json = nlohmann::json;
+
+class sm_db_select : public app {
+public:
+    sm_db_select() : app("sm-db-select") {}
+
+    ~sm_db_select() = default;
+
+    int main(app_execution &execution) override {
+        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;
+    }
+};
+}
+}
+
+int main(int argc, const char *argv[]) {
+    using namespace trygvis::apps;
+
+    return real_main(new sm_db_select(), argc, argv);
+}