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.

3 files changed, 41 insertions, 0 deletions

diff --git a/migrations/20151115192327-device-online.js b/migrations/20151115192327-device-online.js
new file mode 100644
index 0000000..ee28cc3
--- /dev/null
+++ b/migrations/20151115192327-device-online.js
@@ -0,0 +1,30 @@
+var dbm = global.dbm || require('db-migrate');
+var type = dbm.dataType;
+var fs = require('fs');
+var path = require('path');
+
+exports.up = function(db, callback) {
+  var filePath = path.join(__dirname + '/sqls/20151115192327-device-online-up.sql');
+  fs.readFile(filePath, {encoding: 'utf-8'}, function(err,data){
+    if (err) return callback(err);
+      console.log('received data: ' + data);
+
+    db.runSql(data, function(err) {
+      if (err) return callback(err);
+      callback();
+    });
+  });
+};
+
+exports.down = function(db, callback) {
+  var filePath = path.join(__dirname + '/sqls/20151115192327-device-online-down.sql');
+  fs.readFile(filePath, {encoding: 'utf-8'}, function(err,data){
+    if (err) return callback(err);
+      console.log('received data: ' + data);
+
+    db.runSql(data, function(err) {
+      if (err) return callback(err);
+      callback();
+    });
+  });
+};
diff --git a/migrations/sqls/20151115192327-device-online-down.sql b/migrations/sqls/20151115192327-device-online-down.sql
new file mode 100644
index 0000000..44f074e
--- /dev/null
+++ b/migrations/sqls/20151115192327-device-online-down.sql
@@ -0,0 +1 @@
+/* Replace with your SQL commands */
\ No newline at end of file
diff --git a/migrations/sqls/20151115192327-device-online-up.sql b/migrations/sqls/20151115192327-device-online-up.sql
new file mode 100644
index 0000000..bd80a63
--- /dev/null
+++ b/migrations/sqls/20151115192327-device-online-up.sql
@@ -0,0 +1,10 @@
+DROP TABLE IF EXISTS device_status;
+CREATE UNLOGGED TABLE device_status(
+  device      BIGINT PRIMARY KEY REFERENCES device,
+  online      BOOLEAN NOT NULL,
+  timestamp   TIMESTAMPTZ NOT NULL,
+  host        VARCHAR(1000)
+);
+
+INSERT INTO device_status(device, online, timestamp)
+SELECT id, false, created_timestamp FROM device;