o Adding aggregation tables.

o Adding migration scripts for the schema.

6 files changed, 126 insertions, 0 deletions

diff --git a/migrations/20151019162208-base.js b/migrations/20151019162208-base.js
new file mode 100644
index 0000000..3a672b3
--- /dev/null
+++ b/migrations/20151019162208-base.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/20151019162208-base-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/20151019162208-base-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/20151019162254-by-hour-and-minute.js b/migrations/20151019162254-by-hour-and-minute.js
new file mode 100644
index 0000000..a9272a6
--- /dev/null
+++ b/migrations/20151019162254-by-hour-and-minute.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/20151019162254-by-hour-and-minute-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/20151019162254-by-hour-and-minute-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/20151019162208-base-down.sql b/migrations/sqls/20151019162208-base-down.sql
new file mode 100644
index 0000000..44f074e
--- /dev/null
+++ b/migrations/sqls/20151019162208-base-down.sql
@@ -0,0 +1 @@
+/* Replace with your SQL commands */
\ No newline at end of file
diff --git a/migrations/sqls/20151019162208-base-up.sql b/migrations/sqls/20151019162208-base-up.sql
new file mode 100644
index 0000000..97b17a6
--- /dev/null
+++ b/migrations/sqls/20151019162208-base-up.sql
@@ -0,0 +1,45 @@
+DROP TABLE IF EXISTS value;
+DROP TABLE IF EXISTS device_property;
+DROP TABLE IF EXISTS device;
+DROP TABLE IF EXISTS message;
+
+DROP SEQUENCE IF EXISTS id_seq;
+
+CREATE SEQUENCE id_seq;
+
+CREATE TABLE message (
+  timestamp TIMESTAMPTZ   NOT NULL,
+  topic     VARCHAR(1000) NOT NULL,
+  message   BYTEA         NOT NULL
+);
+
+CREATE TABLE device (
+  id                BIGINT        NOT NULL DEFAULT nextval('id_seq'),
+  key               VARCHAR(1000) NOT NULL,
+  created_timestamp TIMESTAMPTZ   NOT NULL,
+  name              VARCHAR(1000),
+  description       VARCHAR(1000),
+
+  PRIMARY KEY (id),
+  CONSTRAINT uq_device__key UNIQUE (key)
+);
+
+CREATE TABLE device_property (
+  id                BIGINT        NOT NULL DEFAULT nextval('id_seq'),
+  device            BIGINT        NOT NULL REFERENCES device,
+  key               VARCHAR(1000) NOT NULL,
+  created_timestamp TIMESTAMPTZ   NOT NULL,
+  name              VARCHAR(1000),
+  description       VARCHAR(1000),
+  last_value        VARCHAR(1000),
+
+  PRIMARY KEY (id),
+  CONSTRAINT uq_device_property__key_name UNIQUE (id, name)
+);
+
+-- no constraints!
+CREATE TABLE value (
+  property  BIGINT      NOT NULL,
+  timestamp TIMESTAMPTZ NOT NULL,
+  value     VARCHAR(1000)
+);
diff --git a/migrations/sqls/20151019162254-by-hour-and-minute-down.sql b/migrations/sqls/20151019162254-by-hour-and-minute-down.sql
new file mode 100644
index 0000000..44f074e
--- /dev/null
+++ b/migrations/sqls/20151019162254-by-hour-and-minute-down.sql
@@ -0,0 +1 @@
+/* Replace with your SQL commands */
\ No newline at end of file
diff --git a/migrations/sqls/20151019162254-by-hour-and-minute-up.sql b/migrations/sqls/20151019162254-by-hour-and-minute-up.sql
new file mode 100644
index 0000000..8e212f4
--- /dev/null
+++ b/migrations/sqls/20151019162254-by-hour-and-minute-up.sql
@@ -0,0 +1,19 @@
+DROP TABLE IF EXISTS value_by_hour;
+CREATE TABLE value_by_hour (
+  property  BIGINT    NOT NULL,
+  timestamp TIMESTAMP NOT NULL,
+  count     NUMERIC   NOT NULL,
+  max       NUMERIC   NOT NULL,
+  min       NUMERIC   NOT NULL,
+  avg       NUMERIC   NOT NULL
+);
+
+DROP TABLE IF EXISTS value_by_minute;
+CREATE TABLE value_by_minute (
+  property  BIGINT    NOT NULL,
+  timestamp TIMESTAMP NOT NULL,
+  count     NUMERIC   NOT NULL,
+  max       NUMERIC   NOT NULL,
+  min       NUMERIC   NOT NULL,
+  avg       NUMERIC   NOT NULL
+);