sql/010_core_schema.sql - master - Alcatraz/Wagomon

sql/010_core_schema.sql
168 строк5 KB
saitgalineu
vkr
26 май 2026, 10:08
26 май 2026, 10:08f9b2919
BEGIN;

SET search_path TO public;

CREATE TABLE IF NOT EXISTS public.users (
	id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
	login text NOT NULL UNIQUE,
	password_hash text NOT NULL,
	role text NOT NULL DEFAULT 'user',
	permissions jsonb NOT NULL DEFAULT '{}'::jsonb,
	created_at timestamptz NOT NULL DEFAULT now(),
	telegram_id bigint,
	alert_status public.alert_status_enum NOT NULL DEFAULT 'INFO'
);

CREATE TABLE IF NOT EXISTS public.wago (
	id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
	name text NOT NULL,
	description text,
	analog boolean,
	"binary" boolean,
	ip text,
	alert_status public.alert_status_enum,
	location text
);

CREATE TABLE IF NOT EXISTS public.sensors_analog (
	id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
	name varchar(255) NOT NULL,
	min_value numeric(10,2),
	max_value numeric(10,2),
	bit_resolution integer,
	min_current numeric(10,2),
	max_current numeric(10,2),
	crit_min real,
	crit_max real,
	wago_id integer,
	connection_id integer,
	description text,
	message_low text,
	correction real,
	alert_status public.alert_status_enum,
	measure_type_id integer,
	formula_id integer,
	location text,
	message_high text
);

CREATE TABLE IF NOT EXISTS public.sensors_binary (
	id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
	name text NOT NULL,
	need_invert varchar(255) NOT NULL,
	trigger_value varchar(50),
	wago_id integer,
	connection_id integer,
	description text,
	message_ok text,
	message_bad text,
	alert_status public.alert_status_enum,
	dependency integer,
	type text,
	number text,
	location text
);

CREATE TABLE IF NOT EXISTS public.measure_types (
	id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
	name text NOT NULL,
	description text
);

CREATE TABLE IF NOT EXISTS public.analog_formulas (
	id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
	name text NOT NULL,
	description text,
	input_type text NOT NULL,
	expression text NOT NULL
);

CREATE TABLE IF NOT EXISTS public.binary_sensor_types (
	id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
	name text NOT NULL UNIQUE,
	description text
);

CREATE TABLE IF NOT EXISTS public.sensor_param_descriptions (
	id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
	table_name text NOT NULL,
	column_name text NOT NULL,
	name text,
	description text,
	data_type text
);

CREATE TABLE IF NOT EXISTS public.dashboards (
	id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
	name text NOT NULL UNIQUE,
	config jsonb NOT NULL,
	created_at timestamptz NOT NULL DEFAULT now(),
	updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS public.draws (
	id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
	name text NOT NULL UNIQUE,
	config jsonb NOT NULL,
	created_at timestamptz NOT NULL DEFAULT now(),
	updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS public.sensor_alert_states (
	sensor_id integer NOT NULL,
	sensor_type text NOT NULL,
	status text NOT NULL,
	updated_at timestamptz NOT NULL DEFAULT now(),
	CONSTRAINT sensor_alert_states_pkey PRIMARY KEY (sensor_id, sensor_type)
);

CREATE TABLE IF NOT EXISTS public.requests (
	login text NOT NULL,
	password text NOT NULL,
	telegram_id bigint NOT NULL
);

CREATE TABLE IF NOT EXISTS public.sensor_data_analog (
	"time" timestamptz NOT NULL,
	value double precision NOT NULL,
	sensor_id integer
);

CREATE TABLE IF NOT EXISTS public.sensor_data_binary (
	"time" timestamptz NOT NULL,
	value boolean NOT NULL,
	sensor_id integer
);

CREATE INDEX IF NOT EXISTS sensor_data_analog_time_idx
	ON public.sensor_data_analog ("time" DESC);

CREATE INDEX IF NOT EXISTS sensor_data_binary_time_idx
	ON public.sensor_data_binary ("time" DESC);

CREATE INDEX IF NOT EXISTS sensors_binary_dependency_idx
	ON public.sensors_binary (dependency);

ALTER TABLE IF EXISTS public.sensors_analog
	ADD CONSTRAINT sensors_analog_formula_id_fkey
		FOREIGN KEY (formula_id) REFERENCES public.analog_formulas(id)
		ON UPDATE NO ACTION ON DELETE NO ACTION;

ALTER TABLE IF EXISTS public.sensors_analog
	ADD CONSTRAINT sensors_analog_measure_type_id_fkey
		FOREIGN KEY (measure_type_id) REFERENCES public.measure_types(id)
		ON UPDATE NO ACTION ON DELETE NO ACTION;

ALTER TABLE IF EXISTS public.sensors_analog
	ADD CONSTRAINT sensors_analog_wago_id_fkey
		FOREIGN KEY (wago_id) REFERENCES public.wago(id) NOT VALID;

ALTER TABLE IF EXISTS public.sensor_data_analog
	ADD CONSTRAINT sensor_data_analog_sensor_id_fkey
		FOREIGN KEY (sensor_id) REFERENCES public.sensors_analog(id) NOT VALID;

ALTER TABLE IF EXISTS public.sensor_data_binary
	ADD CONSTRAINT sensor_data_binary_sensor_id_fkey
		FOREIGN KEY (sensor_id) REFERENCES public.sensors_binary(id) NOT VALID;

COMMIT;