From 73d272ffe8954b3169901eda74428bad3d2740fe Mon Sep 17 00:00:00 2001 From: Trygve Laugstøl Date: Mon, 19 Oct 2015 21:53:49 +0200 Subject: o Adding aggregation tables. o Adding migration scripts for the schema. --- migrations/20151019162208-base.js | 30 +++++++++++++++ migrations/20151019162254-by-hour-and-minute.js | 30 +++++++++++++++ migrations/sqls/20151019162208-base-down.sql | 1 + migrations/sqls/20151019162208-base-up.sql | 45 ++++++++++++++++++++++ .../20151019162254-by-hour-and-minute-down.sql | 1 + .../sqls/20151019162254-by-hour-and-minute-up.sql | 19 +++++++++ 6 files changed, 126 insertions(+) create mode 100644 migrations/20151019162208-base.js create mode 100644 migrations/20151019162254-by-hour-and-minute.js create mode 100644 migrations/sqls/20151019162208-base-down.sql create mode 100644 migrations/sqls/20151019162208-base-up.sql create mode 100644 migrations/sqls/20151019162254-by-hour-and-minute-down.sql create mode 100644 migrations/sqls/20151019162254-by-hour-and-minute-up.sql (limited to 'migrations') 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 +); -- cgit v1.2.3