diff options
Diffstat (limited to 'schema.sql')
-rw-r--r-- | schema.sql | 133 |
1 files changed, 132 insertions, 1 deletions
@@ -66,6 +66,69 @@ CREATE TABLE public.entsoe ( -- +-- Name: location; Type: TABLE; Schema: public; Owner: - +-- + +CREATE TABLE public.location ( + id smallint NOT NULL, + name character varying(255) NOT NULL, + description text, + lon real, + lat real, + created timestamp with time zone DEFAULT now() NOT NULL, + updated timestamp with time zone +); + + +-- +-- Name: location_id_seq; Type: SEQUENCE; Schema: public; Owner: - +-- + +ALTER TABLE public.location ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY ( + SEQUENCE NAME public.location_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1 +); + + +-- +-- Name: mqtt_han; Type: TABLE; Schema: public; Owner: - +-- + +CREATE TABLE public.mqtt_han ( + id bigint NOT NULL, + name text, + current real, + power real, + voltage real, + linkquality smallint, + "time" timestamp with time zone DEFAULT now() NOT NULL +); + + +-- +-- Name: mqtt_han_id_seq; Type: SEQUENCE; Schema: public; Owner: - +-- + +CREATE SEQUENCE public.mqtt_han_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +-- +-- Name: mqtt_han_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- + +ALTER SEQUENCE public.mqtt_han_id_seq OWNED BY public.mqtt_han.id; + + +-- -- Name: mqtt_temps; Type: TABLE; Schema: public; Owner: - -- @@ -153,6 +216,34 @@ ALTER SEQUENCE public.neohub_id_seq OWNED BY public.neohub.id; -- +-- Name: room; Type: TABLE; Schema: public; Owner: - +-- + +CREATE TABLE public.room ( + id smallint NOT NULL, + location_id smallint NOT NULL, + name character varying(255) NOT NULL, + description text, + created timestamp with time zone DEFAULT now() NOT NULL, + updated timestamp with time zone +); + + +-- +-- Name: room_id_seq; Type: SEQUENCE; Schema: public; Owner: - +-- + +ALTER TABLE public.room ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY ( + SEQUENCE NAME public.room_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1 +); + + +-- -- Name: tibber_consumption; Type: TABLE; Schema: public; Owner: - -- @@ -188,6 +279,7 @@ CREATE TABLE public.tibber_prices ( CREATE TABLE public.yr ( "time" timestamp with time zone NOT NULL, + location smallint NOT NULL, air_temperature real, air_pressure_at_sea_level real, cloud_area_fraction real, @@ -200,6 +292,13 @@ CREATE TABLE public.yr ( -- +-- Name: mqtt_han id; Type: DEFAULT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY public.mqtt_han ALTER COLUMN id SET DEFAULT nextval('public.mqtt_han_id_seq'::regclass); + + +-- -- Name: mqtt_temps id; Type: DEFAULT; Schema: public; Owner: - -- @@ -238,6 +337,22 @@ ALTER TABLE ONLY public.entsoe -- +-- Name: location location_pkey; Type: CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY public.location + ADD CONSTRAINT location_pkey PRIMARY KEY (id); + + +-- +-- Name: mqtt_han mqtt_han_pkey; Type: CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY public.mqtt_han + ADD CONSTRAINT mqtt_han_pkey PRIMARY KEY (id); + + +-- -- Name: mqtt_temps mqtt_temps_pkey; Type: CONSTRAINT; Schema: public; Owner: - -- @@ -262,6 +377,14 @@ ALTER TABLE ONLY public.neohub -- +-- Name: room room_pkey; Type: CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY public.room + ADD CONSTRAINT room_pkey PRIMARY KEY (id); + + +-- -- Name: tibber_consumption tibber_consumption_pkey; Type: CONSTRAINT; Schema: public; Owner: - -- @@ -282,7 +405,15 @@ ALTER TABLE ONLY public.tibber_prices -- ALTER TABLE ONLY public.yr - ADD CONSTRAINT yr_pkey PRIMARY KEY ("time"); + ADD CONSTRAINT yr_pkey PRIMARY KEY ("time", location); + + +-- +-- Name: room fk_location; Type: FK CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY public.room + ADD CONSTRAINT fk_location FOREIGN KEY (location_id) REFERENCES public.location(id); -- |