From dda9ef2ae7971bceaa792e328c8489cb0695b77e Mon Sep 17 00:00:00 2001
From: Trygve Laugstøl <trygvis@inamo.no>
Date: Mon, 16 Nov 2015 18:46:56 +0100
Subject: core: o Adding device_status table containing the latest device
 status. mqtt: o Listening on $SYS/broker/log, parse out messages about
 clients connecting and disconnecting and store in database.

---
 src/DillerDao.js | 86 ++++++++++++++++++++++++++++++++++++++++++++++++++++----
 1 file changed, 81 insertions(+), 5 deletions(-)

(limited to 'src/DillerDao.js')

diff --git a/src/DillerDao.js b/src/DillerDao.js
index 34f4205..cf4c75b 100644
--- a/src/DillerDao.js
+++ b/src/DillerDao.js
@@ -6,9 +6,70 @@ var _ = require('lodash');
  */
 function DillerDao(tx) {
 
-  var deviceColumns = 'id, created_timestamp, key, name, description';
+  var deviceColumns = ['id', 'created_timestamp', 'key', 'name', 'description'];
+  var deviceStatusColumns = ['device', 'online', 'timestamp', 'host'];
   var propertyColumns = 'id, created_timestamp, device, key, name, description, last_value, last_timestamp';
 
+  function cols(columns, select_prefix, as_prefix) {
+    var s;
+    if (select_prefix) {
+      as_prefix = as_prefix || select_prefix;
+      for (var i = 0; i < columns.length; i++) {
+        var c = columns[i];
+        if (!s) {
+          s = '';
+        } else {
+          s += ', ';
+        }
+
+        s += select_prefix + '.' + c + ' AS ' + as_prefix + '_' + c;
+      }
+      return s;
+    }
+
+    return columns.join(', ');
+  }
+
+  function unjoin(main) {
+    var children = Array.prototype.slice.call(arguments).slice(1);
+
+    function convertRow(row) {
+      if (!row) {
+        return row;
+      }
+      var r = {};
+      for (var field in row) {
+        var f = '' + field;
+        if (f.startsWith(main + '_')) {
+          r[f.substring(main.length + 1)] = row[f];
+        } else {
+          for (var i = 0; i < children.length; i++) {
+            var child = children[i];
+            var prefix = child + '_';
+
+            if (f.startsWith(prefix)) {
+              var o = r[child];
+              if (!o) {
+                o = r[child] = {};
+              }
+
+              o[f.substr(prefix.length)] = row[f];
+            }
+          }
+        }
+      }
+      return r;
+    }
+
+    return function (res) {
+      if (Array.isArray(res)) {
+        return res.map(convertRow);
+      } else {
+        return convertRow(res);
+      }
+    }
+  }
+
   // -------------------------------------------------------------------------------------------------------------------
   // Device
   // -------------------------------------------------------------------------------------------------------------------
@@ -17,19 +78,26 @@ function DillerDao(tx) {
    * @returns {Promise}
    */
   function devices() {
-    return tx.manyOrNone("SELECT " + deviceColumns + " FROM device");
+    var sql = 'SELECT ' + cols(deviceColumns, 'd') + ', ' + cols(deviceStatusColumns, 'ds', 'status') + " FROM device d LEFT JOIN device_status ds on (d.id = ds.device)";
+    return tx.manyOrNone(sql).then(unjoin('d', 'status'));
   }
 
   function deviceById(id) {
-    return tx.one("SELECT " + deviceColumns + " FROM device WHERE id=$1", id);
+    var sql = 'SELECT ' + cols(deviceColumns, 'd') + ', ' + cols(deviceStatusColumns, 'ds', 'status') + " FROM device d LEFT JOIN device_status ds on (d.id = ds.device) WHERE d.id=$1";
+    return tx.one(sql, [id]).then(unjoin('d', 'status'));
   }
 
   function deviceByKey(key) {
-    return tx.oneOrNone("SELECT " + deviceColumns + " FROM device WHERE key=$1", key);
+    var sql = 'SELECT ' + cols(deviceColumns, 'd') + ', ' + cols(deviceStatusColumns, 'ds', 'status') + " FROM device d LEFT JOIN device_status ds on (d.id = ds.device) WHERE d.key=$1";
+    return tx.oneOrNone(sql, [key]).then(unjoin('d', 'status'));
   }
 
   function insertDevice(key) {
-    return tx.one("INSERT INTO device(id, key, created_timestamp) VALUES(DEFAULT, $1, CURRENT_TIMESTAMP) RETURNING " + deviceColumns, key);
+    return tx.one("INSERT INTO device(id, key, created_timestamp) VALUES(DEFAULT, $1, CURRENT_TIMESTAMP) RETURNING " + cols(deviceColumns), key);
+  }
+
+  function insertDeviceStatus(device, online, timestamp, host) {
+    return tx.none("INSERT INTO device_status(device, online, timestamp, host) VALUES($1, $2, $3, $4)", [device, online, timestamp, host]);
   }
 
   function updateDevice(id, attributes) {
@@ -55,6 +123,11 @@ function DillerDao(tx) {
     return tx.none(sql, values);
   }
 
+  function updateDeviceStatus(device, timestamp, online, host) {
+    var sql = 'UPDATE device_status SET timestamp=$2, online=$3, host=$4 WHERE device=$1';
+    return tx.none(sql, [device, timestamp, online, host]);
+  }
+
   // -------------------------------------------------------------------------------------------------------------------
   // Device Property
   // -------------------------------------------------------------------------------------------------------------------
@@ -151,6 +224,9 @@ function DillerDao(tx) {
     insertDevice: insertDevice,
     updateDevice: updateDevice,
 
+    insertDeviceStatus: insertDeviceStatus,
+    updateDeviceStatus: updateDeviceStatus,
+
     devicePropertyById: devicePropertyById,
     devicePropertyByDeviceIdAndKey: devicePropertyByDeviceIdAndKey,
     devicePropertiesByDeviceId: devicePropertiesByDeviceId,
-- 
cgit v1.2.3