Modelo-de-datos

Ver en Git

---

Script Modelo de datos

CREATE TABLE public.variable_groups (
	group_id serial4 NOT NULL,
	group_uuid uuid NOT NULL DEFAULT uuid_generate_v4(),
	created_at timestamp NOT NULL DEFAULT timezone('UTC'::text, now()),
	deleted_at timestamp NULL,
	group_name varchar(100) NULL,
	provider_uuid uuid NULL,
	"position" text NULL,
	device text NULL,
	timezone_offset int2 NULL DEFAULT 0,
	metadata jsonb NULL,
	user_group_name varchar(100) NULL,
	user_position text NULL,
	CONSTRAINT variable_groups_pk PRIMARY KEY (group_id)
);

create table roles (
	role_id int2 primary key,
	role_name varchar(20),
	permissions int2[] not null	
);
insert into roles values (1,'Owner', array[601,602,603,604,605,606,607]);

CREATE TABLE public.group_access (
	id serial4 NOT null primary KEY,
	user_id int4 NOT NULL,
	group_id int4 NOT NULL,
	role_id int2 NOT NULL,
	created_at timestamp NOT NULL DEFAULT timezone('UTC'::text, now()),
	deleted_at timestamp NULL,
	CONSTRAINT group_access_role_id_fk FOREIGN KEY (role_id) REFERENCES public.roles(role_id),
	CONSTRAINT group_access_group_id_fk FOREIGN KEY (group_id) REFERENCES public.variable_groups(group_id)
);

create table magnitudes (
	magnitude_id serial not null,
	magnitude text not null,
	default_unit jsonb not null,
	units jsonb not null,
	CONSTRAINT magnitude_pk PRIMARY key (magnitude_id)
);

create table variable_types (
	type_id serial not null,
	magnitude_id int2 not null,
	type_name text not null,
	description text,
	CONSTRAINT type_pk PRIMARY key (type_id),
	CONSTRAINT magnitude_fk 
		FOREIGN KEY (magnitude_id) 
		REFERENCES magnitudes (magnitude_id)
);

CREATE TABLE public.variables (
	variable_id serial4 NOT NULL,
	variable_uuid uuid NOT NULL DEFAULT uuid_generate_v4(),
	variable_name varchar(100) NULL,
	group_id int4 NOT NULL,
	created_at timestamp NOT NULL DEFAULT timezone('UTC'::text, now()),
	deleted_at timestamp NULL,
	device text NULL,
	metadata jsonb NULL,
	type_id int4 NULL,
	user_variable_name varchar(100) NULL,
	CONSTRAINT variables_pk PRIMARY KEY (variable_id),
	constraint variable_group_id FOREIGN KEY (group_id) REFERENCES public.variable_groups(group_id),
	CONSTRAINT type_id_fk FOREIGN KEY (type_id) REFERENCES public.variable_types(type_id)
);


create table variable_values (
	variable_id int4 not null,
	time timestamp not null,
	value float4,
	data jsonb
);
select create_hypertable('variable_values', 'time') ;

CREATE UNIQUE INDEX variable_values_variable_id_idx ON public.variable_values USING btree (variable_id, "time" DESC);

------------- PENDIENTE PROVIDERS -----------
create table providers (
	provider_uuid uuid default uuid_generate_v4() not null,
	status int2 not null default 1,
	provider_name text not null,
	CONSTRAINT providers_un UNIQUE (provider_uuid)
);
ALTER TABLE variable_groups 
ADD CONSTRAINT provider_fk 
FOREIGN KEY (provider_uuid) 
REFERENCES providers (provider_uuid);
-------------------------------------------------


CREATE TABLE public.variable_alarms (
	alarm_id serial4 NOT NULL,
	user_id int4 NOT NULL,
	variable_id int4 NOT NULL,
	trigger_type text NOT NULL,
	created_at timestamp NOT NULL DEFAULT timezone('utc'::text, now()),
	deleted_at timestamp NULL,
	value numeric NOT NULL,
	alarm_uuid uuid NOT NULL DEFAULT uuid_generate_v4(),
	CONSTRAINT alarm_pk PRIMARY KEY (alarm_id),
	CONSTRAINT variable_fk FOREIGN KEY (variable_id) REFERENCES public.variables(variable_id)
);

create table variable_alarm_check_history (
	variable_id int4 not null,
	last_checked timestamp not null,
	CONSTRAINT variable_id_fk FOREIGN KEY (variable_id) REFERENCES public.variables(variable_id)
);



--- METOS

CREATE TABLE public.metos_sensor_codes (
	sensor_code varchar NOT NULL,
	"name" varchar NOT NULL,
	default_unit varchar NOT NULL DEFAULT ''::character varying,
	default_aggr varchar NULL,
	variable_type_id int4 NULL,
	CONSTRAINT metos_sensor_codes_pk PRIMARY KEY (sensor_code),
	CONSTRAINT type_fk FOREIGN KEY (variable_type_id) REFERENCES public.variable_types(type_id)
);

CREATE TABLE public.metos_disease_models (
	crop varchar NOT NULL,
	model_key varchar NOT NULL,
	model_result varchar NOT NULL,
	metadata jsonb NOT NULL,
	variable_name varchar NOT NULL,
	type_id int4 NULL,
	CONSTRAINT metos_disease_models_variable_type_id_fkey FOREIGN KEY (type_id) REFERENCES public.variable_types(type_id)
);



CREATE table metos_synced_stations(
	id serial,
	user_id int4 not null, 
	device varchar(20) not null, 
	variable_group_uuid uuid not null,
	created_at timestamp default timezone('utc', now()) not null,
	deleted_at timestamp null,
	CONSTRAINT metos_synced_stations_pk PRIMARY key (id)
);