aboutsummaryrefslogtreecommitdiff
path: root/migrations/sqls
diff options
context:
space:
mode:
Diffstat (limited to 'migrations/sqls')
-rw-r--r--migrations/sqls/20151019162208-base-down.sql1
-rw-r--r--migrations/sqls/20151019162208-base-up.sql45
-rw-r--r--migrations/sqls/20151019162254-by-hour-and-minute-down.sql1
-rw-r--r--migrations/sqls/20151019162254-by-hour-and-minute-up.sql19
4 files changed, 66 insertions, 0 deletions
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
+);