diff options
Diffstat (limited to 'migrations/sqls')
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 +); |