Skip to content
Snippets Groups Projects

Resolve L2SS-542 "Add timescaledb views"

Merged Resolve L2SS-542 "Add timescaledb views"
L2SS-542-add-timescaledb-views into master
All threads resolved!
Merged Stefano Di Frischia requested to merge L2SS-542-add-timescaledb-views into master
All threads resolved!
Compare
and
12 files
+ 867
− 1853
Compare changes
  • Side-by-side
  • Inline
Files
12
docker-compose/timescaledb/resources/06_lofar_views.sql 0 → 100644
+ 377
− 0
\c hdb
-- NOTE: We concatenate domain/family/member here, which means we can't index
-- the resulting column. However, queries also supply the attribute name,
-- which we can index. The scan on the device name is then limited to
-- entries which have the same attribute name across devices.
-- DOUBLE --
CREATE OR REPLACE VIEW lofar_array_double AS
SELECT
att.data_time AS data_time,
CONCAT_WS('/', domain, family, member) AS device,
ac.name AS name,
array_element.idx - 1 AS x,
array_element.val as value
FROM att_array_devdouble att
-- add array values, and their index
JOIN LATERAL UNNEST(att.value_r) WITH ORDINALITY AS array_element(val,idx) ON TRUE
-- add the device information
JOIN att_conf ac ON att.att_conf_id = ac.att_conf_id
WHERE att.value_r IS NOT NULL;
CREATE OR REPLACE VIEW lofar_scalar_double AS
SELECT
att.data_time AS data_time,
CONCAT_WS('/', domain, family, member) AS device,
ac.name AS name,
value_r as value
FROM att_scalar_devdouble att
-- add the device information
JOIN att_conf ac ON att.att_conf_id = ac.att_conf_id
WHERE att.value_r IS NOT NULL;
-- BOOLEAN --
CREATE OR REPLACE VIEW lofar_array_boolean AS
SELECT
att.data_time AS data_time,
CONCAT_WS('/', domain, family, member) AS device,
ac.name AS name,
array_element.idx - 1 AS x,
array_element.val as value
FROM att_array_devboolean att
-- add array values, and their index
JOIN LATERAL UNNEST(att.value_r) WITH ORDINALITY AS array_element(val,idx) ON TRUE
-- add the device information
JOIN att_conf ac ON att.att_conf_id = ac.att_conf_id
WHERE att.value_r IS NOT NULL;
CREATE OR REPLACE VIEW lofar_scalar_boolean AS
SELECT
att.data_time AS data_time,
CONCAT_WS('/', domain, family, member) AS device,
ac.name AS name,
value_r as value
FROM att_scalar_devboolean att
-- add the device information
JOIN att_conf ac ON att.att_conf_id = ac.att_conf_id
WHERE att.value_r IS NOT NULL;
-- UCHAR --
CREATE OR REPLACE VIEW lofar_array_uchar AS
SELECT
att.data_time AS data_time,
CONCAT_WS('/', domain, family, member) AS device,
ac.name AS name,
array_element.idx - 1 AS x,
array_element.val as value
FROM att_array_devuchar att
-- add array values, and their index
JOIN LATERAL UNNEST(att.value_r) WITH ORDINALITY AS array_element(val,idx) ON TRUE
-- add the device information
JOIN att_conf ac ON att.att_conf_id = ac.att_conf_id
WHERE att.value_r IS NOT NULL;
CREATE OR REPLACE VIEW lofar_scalar_uchar AS
SELECT
att.data_time AS data_time,
CONCAT_WS('/', domain, family, member) AS device,
ac.name AS name,
value_r as value
FROM att_scalar_devuchar att
-- add the device information
JOIN att_conf ac ON att.att_conf_id = ac.att_conf_id
WHERE att.value_r IS NOT NULL;
-- SHORT --
CREATE OR REPLACE VIEW lofar_array_short AS
SELECT
att.data_time AS data_time,
CONCAT_WS('/', domain, family, member) AS device,
ac.name AS name,
array_element.idx - 1 AS x,
array_element.val as value
FROM att_array_devshort att
-- add array values, and their index
JOIN LATERAL UNNEST(att.value_r) WITH ORDINALITY AS array_element(val,idx) ON TRUE
-- add the device information
JOIN att_conf ac ON att.att_conf_id = ac.att_conf_id
WHERE att.value_r IS NOT NULL;
CREATE OR REPLACE VIEW lofar_scalar_short AS
SELECT
att.data_time AS data_time,
CONCAT_WS('/', domain, family, member) AS device,
ac.name AS name,
value_r as value
FROM att_scalar_devshort att
-- add the device information
JOIN att_conf ac ON att.att_conf_id = ac.att_conf_id
WHERE att.value_r IS NOT NULL;
-- USHORT --
CREATE OR REPLACE VIEW lofar_array_ushort AS
SELECT
att.data_time AS data_time,
CONCAT_WS('/', domain, family, member) AS device,
ac.name AS name,
array_element.idx - 1 AS x,
array_element.val as value
FROM att_array_devushort att
-- add array values, and their index
JOIN LATERAL UNNEST(att.value_r) WITH ORDINALITY AS array_element(val,idx) ON TRUE
-- add the device information
JOIN att_conf ac ON att.att_conf_id = ac.att_conf_id
WHERE att.value_r IS NOT NULL;
CREATE OR REPLACE VIEW lofar_scalar_ushort AS
SELECT
att.data_time AS data_time,
CONCAT_WS('/', domain, family, member) AS device,
ac.name AS name,
value_r as value
FROM att_scalar_devushort att
-- add the device information
JOIN att_conf ac ON att.att_conf_id = ac.att_conf_id
WHERE att.value_r IS NOT NULL;
-- LONG --
CREATE OR REPLACE VIEW lofar_array_long AS
SELECT
att.data_time AS data_time,
CONCAT_WS('/', domain, family, member) AS device,
ac.name AS name,
array_element.idx - 1 AS x,
array_element.val as value
FROM att_array_devlong att
-- add array values, and their index
JOIN LATERAL UNNEST(att.value_r) WITH ORDINALITY AS array_element(val,idx) ON TRUE
-- add the device information
JOIN att_conf ac ON att.att_conf_id = ac.att_conf_id
WHERE att.value_r IS NOT NULL;
CREATE OR REPLACE VIEW lofar_scalar_long AS
SELECT
att.data_time AS data_time,
CONCAT_WS('/', domain, family, member) AS device,
ac.name AS name,
value_r as value
FROM att_scalar_devlong att
-- add the device information
JOIN att_conf ac ON att.att_conf_id = ac.att_conf_id
WHERE att.value_r IS NOT NULL;
-- ULONG --
CREATE OR REPLACE VIEW lofar_array_ulong AS
SELECT
att.data_time AS data_time,
CONCAT_WS('/', domain, family, member) AS device,
ac.name AS name,
array_element.idx - 1 AS x,
array_element.val as value
FROM att_array_devulong att
-- add array values, and their index
JOIN LATERAL UNNEST(att.value_r) WITH ORDINALITY AS array_element(val,idx) ON TRUE
-- add the device information
JOIN att_conf ac ON att.att_conf_id = ac.att_conf_id
WHERE att.value_r IS NOT NULL;
CREATE OR REPLACE VIEW lofar_scalar_ulong AS
SELECT
att.data_time AS data_time,
CONCAT_WS('/', domain, family, member) AS device,
ac.name AS name,
value_r as value
FROM att_scalar_devulong att
-- add the device information
JOIN att_conf ac ON att.att_conf_id = ac.att_conf_id
WHERE att.value_r IS NOT NULL;
-- LONG64 --
CREATE OR REPLACE VIEW lofar_array_long64 AS
SELECT
att.data_time AS data_time,
CONCAT_WS('/', domain, family, member) AS device,
ac.name AS name,
array_element.idx - 1 AS x,
array_element.val as value
FROM att_array_devlong64 att
-- add array values, and their index
JOIN LATERAL UNNEST(att.value_r) WITH ORDINALITY AS array_element(val,idx) ON TRUE
-- add the device information
JOIN att_conf ac ON att.att_conf_id = ac.att_conf_id
WHERE att.value_r IS NOT NULL;
CREATE OR REPLACE VIEW lofar_scalar_long64 AS
SELECT
att.data_time AS data_time,
CONCAT_WS('/', domain, family, member) AS device,
ac.name AS name,
value_r as value
FROM att_scalar_devlong64 att
-- add the device information
JOIN att_conf ac ON att.att_conf_id = ac.att_conf_id
WHERE att.value_r IS NOT NULL;
-- ULONG64 --
CREATE OR REPLACE VIEW lofar_array_ulong64 AS
SELECT
att.data_time AS data_time,
CONCAT_WS('/', domain, family, member) AS device,
ac.name AS name,
array_element.idx - 1 AS x,
array_element.val as value
FROM att_array_devulong64 att
-- add array values, and their index
JOIN LATERAL UNNEST(att.value_r) WITH ORDINALITY AS array_element(val,idx) ON TRUE
-- add the device information
JOIN att_conf ac ON att.att_conf_id = ac.att_conf_id
WHERE att.value_r IS NOT NULL;
CREATE OR REPLACE VIEW lofar_scalar_ulong64 AS
SELECT
att.data_time AS data_time,
CONCAT_WS('/', domain, family, member) AS device,
ac.name AS name,
value_r as value
FROM att_scalar_devulong64 att
-- add the device information
JOIN att_conf ac ON att.att_conf_id = ac.att_conf_id
WHERE att.value_r IS NOT NULL;
-- FLOAT --
CREATE OR REPLACE VIEW lofar_array_float AS
SELECT
att.data_time AS data_time,
CONCAT_WS('/', domain, family, member) AS device,
ac.name AS name,
array_element.idx - 1 AS x,
array_element.val as value
FROM att_array_devfloat att
-- add array values, and their index
JOIN LATERAL UNNEST(att.value_r) WITH ORDINALITY AS array_element(val,idx) ON TRUE
-- add the device information
JOIN att_conf ac ON att.att_conf_id = ac.att_conf_id
WHERE att.value_r IS NOT NULL;
CREATE OR REPLACE VIEW lofar_scalar_float AS
SELECT
att.data_time AS data_time,
CONCAT_WS('/', domain, family, member) AS device,
ac.name AS name,
value_r as value
FROM att_scalar_devfloat att
-- add the device information
JOIN att_conf ac ON att.att_conf_id = ac.att_conf_id
WHERE att.value_r IS NOT NULL;
-- STRING --
CREATE OR REPLACE VIEW lofar_array_string AS
SELECT
att.data_time AS data_time,
CONCAT_WS('/', domain, family, member) AS device,
ac.name AS name,
array_element.idx - 1 AS x,
array_element.val as value
FROM att_array_devstring att
-- add array values, and their index
JOIN LATERAL UNNEST(att.value_r) WITH ORDINALITY AS array_element(val,idx) ON TRUE
-- add the device information
JOIN att_conf ac ON att.att_conf_id = ac.att_conf_id
WHERE att.value_r IS NOT NULL;
CREATE OR REPLACE VIEW lofar_scalar_string AS
SELECT
att.data_time AS data_time,
CONCAT_WS('/', domain, family, member) AS device,
ac.name AS name,
value_r as value
FROM att_scalar_devstring att
-- add the device information
JOIN att_conf ac ON att.att_conf_id = ac.att_conf_id
WHERE att.value_r IS NOT NULL;
-- STATE --
CREATE OR REPLACE VIEW lofar_array_state AS
SELECT
att.data_time AS data_time,
CONCAT_WS('/', domain, family, member) AS device,
ac.name AS name,
array_element.idx - 1 AS x,
array_element.val as value
FROM att_array_devstate att
-- add array values, and their index
JOIN LATERAL UNNEST(att.value_r) WITH ORDINALITY AS array_element(val,idx) ON TRUE
-- add the device information
JOIN att_conf ac ON att.att_conf_id = ac.att_conf_id
WHERE att.value_r IS NOT NULL;
CREATE OR REPLACE VIEW lofar_scalar_state AS
SELECT
att.data_time AS data_time,
CONCAT_WS('/', domain, family, member) AS device,
ac.name AS name,
value_r as value
FROM att_scalar_devstate att
-- add the device information
JOIN att_conf ac ON att.att_conf_id = ac.att_conf_id
WHERE att.value_r IS NOT NULL;
-- ENCODED --
CREATE OR REPLACE VIEW lofar_array_encoded AS
SELECT
att.data_time AS data_time,
CONCAT_WS('/', domain, family, member) AS device,
ac.name AS name,
array_element.idx - 1 AS x,
array_element.val as value
FROM att_array_devencoded att
-- add array values, and their index
JOIN LATERAL UNNEST(att.value_r) WITH ORDINALITY AS array_element(val,idx) ON TRUE
-- add the device information
JOIN att_conf ac ON att.att_conf_id = ac.att_conf_id
WHERE att.value_r IS NOT NULL;
CREATE OR REPLACE VIEW lofar_scalar_encoded AS
SELECT
att.data_time AS data_time,
CONCAT_WS('/', domain, family, member) AS device,
ac.name AS name,
value_r as value
FROM att_scalar_devencoded att
-- add the device information
JOIN att_conf ac ON att.att_conf_id = ac.att_conf_id
WHERE att.value_r IS NOT NULL;
-- ENUM --
CREATE OR REPLACE VIEW lofar_array_enum AS
SELECT
att.data_time AS data_time,
CONCAT_WS('/', domain, family, member) AS device,
ac.name AS name,
array_element.idx - 1 AS x,
array_element.val as value
FROM att_array_devenum att
-- add array values, and their index
JOIN LATERAL UNNEST(att.value_r) WITH ORDINALITY AS array_element(val,idx) ON TRUE
-- add the device information
JOIN att_conf ac ON att.att_conf_id = ac.att_conf_id
WHERE att.value_r IS NOT NULL;
CREATE OR REPLACE VIEW lofar_scalar_enum AS
SELECT
att.data_time AS data_time,
CONCAT_WS('/', domain, family, member) AS device,
ac.name AS name,
value_r as value
FROM att_scalar_devenum att
-- add the device information
JOIN att_conf ac ON att.att_conf_id = ac.att_conf_id
WHERE att.value_r IS NOT NULL;
\ No newline at end of file