73 lines
2.0 KiB
SQL
73 lines
2.0 KiB
SQL
-- Base schema copied from cloud-beta.
|
|
-- Creates application tables, indexes and TimescaleDB hypertable for history.
|
|
|
|
CREATE EXTENSION IF NOT EXISTS timescaledb;
|
|
|
|
CREATE SCHEMA IF NOT EXISTS public;
|
|
|
|
CREATE TABLE IF NOT EXISTS public.edge (
|
|
id varchar(100) NOT NULL,
|
|
name text NOT NULL,
|
|
parent_id varchar(100),
|
|
CONSTRAINT edge_pkey PRIMARY KEY (id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS public.tag (
|
|
id varchar(100) NOT NULL,
|
|
name text NOT NULL,
|
|
min double precision,
|
|
max double precision,
|
|
comment text NOT NULL,
|
|
unit_of_measurement text NOT NULL,
|
|
precision integer,
|
|
tag_group varchar(255),
|
|
CONSTRAINT tag_pkey PRIMARY KEY (id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS public.current (
|
|
id integer GENERATED BY DEFAULT AS IDENTITY,
|
|
edge varchar(100) NOT NULL,
|
|
tag varchar(100) NOT NULL,
|
|
value double precision,
|
|
"createdAt" timestamp(3) without time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
|
|
"updatedAt" timestamp(3) without time zone NOT NULL,
|
|
CONSTRAINT current_pkey PRIMARY KEY (id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS public.history (
|
|
edge varchar(100) NOT NULL,
|
|
"timestamp" timestamp(3) without time zone NOT NULL,
|
|
tag varchar(100) NOT NULL,
|
|
value double precision NOT NULL,
|
|
"createdAt" timestamp(3) without time zone DEFAULT now() NOT NULL
|
|
);
|
|
|
|
SELECT create_hypertable(
|
|
'public.history',
|
|
'timestamp',
|
|
chunk_time_interval => INTERVAL '1 day',
|
|
if_not_exists => TRUE
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS public.camera (
|
|
edge varchar NOT NULL,
|
|
protocol varchar NOT NULL,
|
|
source varchar NOT NULL,
|
|
CONSTRAINT camera_pkey PRIMARY KEY (edge, protocol, source)
|
|
);
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS current_edge_tag_key
|
|
ON public.current USING btree (edge, tag);
|
|
|
|
CREATE INDEX IF NOT EXISTS current_edge_idx
|
|
ON public.current USING btree (edge);
|
|
|
|
CREATE INDEX IF NOT EXISTS current_tag_idx
|
|
ON public.current USING btree (tag);
|
|
|
|
CREATE INDEX IF NOT EXISTS history_edge_tag_timestamp_idx
|
|
ON public.history USING btree (edge, tag, "timestamp" DESC);
|
|
|
|
CREATE INDEX IF NOT EXISTS camera_edge_idx
|
|
ON public.camera USING btree (edge);
|