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/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 +++++++++ 4 files changed, 66 insertions(+) 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/sqls') 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