diff options
| -rw-r--r-- | schema.sql | 80 |
1 files changed, 66 insertions, 14 deletions
@@ -2,8 +2,8 @@ -- PostgreSQL database dump -- --- Dumped from database version 14.6 (Ubuntu 14.6-0ubuntu0.22.04.1) --- Dumped by pg_dump version 14.6 (Ubuntu 14.6-0ubuntu0.22.04.1) +-- Dumped from database version 14.7 (Ubuntu 14.7-0ubuntu0.22.04.1) +-- Dumped by pg_dump version 14.7 (Ubuntu 14.7-0ubuntu0.22.04.1) SET statement_timeout = 0; SET lock_timeout = 0; @@ -134,7 +134,7 @@ ALTER SEQUENCE public.mqtt_han_id_seq OWNED BY public.mqtt_han.id; CREATE TABLE public.mqtt_temps ( id integer NOT NULL, - name text NOT NULL, + sensor_id smallint NOT NULL, temperature real, humidity real, battery smallint, @@ -216,12 +216,12 @@ ALTER SEQUENCE public.neohub_id_seq OWNED BY public.neohub.id; -- --- Name: room; Type: TABLE; Schema: public; Owner: - +-- Name: rooms; Type: TABLE; Schema: public; Owner: - -- -CREATE TABLE public.room ( +CREATE TABLE public.rooms ( id smallint NOT NULL, - location_id smallint NOT NULL, + location smallint NOT NULL, name character varying(255) NOT NULL, description text, created timestamp with time zone DEFAULT now() NOT NULL, @@ -230,11 +230,39 @@ CREATE TABLE public.room ( -- --- Name: room_id_seq; Type: SEQUENCE; Schema: public; Owner: - +-- Name: rooms_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 +ALTER TABLE public.rooms ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY ( + SEQUENCE NAME public.rooms_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1 +); + + +-- +-- Name: sensors; Type: TABLE; Schema: public; Owner: - +-- + +CREATE TABLE public.sensors ( + id smallint NOT NULL, + name character varying(255) NOT NULL, + description text, + room smallint, + created timestamp with time zone DEFAULT now() NOT NULL, + updated timestamp with time zone +); + + +-- +-- Name: sensors_id_seq; Type: SEQUENCE; Schema: public; Owner: - +-- + +ALTER TABLE public.sensors ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY ( + SEQUENCE NAME public.sensors_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE @@ -377,14 +405,22 @@ ALTER TABLE ONLY public.neohub -- --- Name: room room_pkey; Type: CONSTRAINT; Schema: public; Owner: - +-- Name: rooms room_pkey; Type: CONSTRAINT; Schema: public; Owner: - -- -ALTER TABLE ONLY public.room +ALTER TABLE ONLY public.rooms ADD CONSTRAINT room_pkey PRIMARY KEY (id); -- +-- Name: sensors sensors_pkey; Type: CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY public.sensors + ADD CONSTRAINT sensors_pkey PRIMARY KEY (id); + + +-- -- Name: tibber_consumption tibber_consumption_pkey; Type: CONSTRAINT; Schema: public; Owner: - -- @@ -409,11 +445,27 @@ ALTER TABLE ONLY public.yr -- --- Name: room fk_location; Type: FK CONSTRAINT; Schema: public; Owner: - +-- Name: rooms fk_location; Type: FK CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY public.rooms + ADD CONSTRAINT fk_location FOREIGN KEY (location) REFERENCES public.location(id); + + +-- +-- Name: mqtt_temps fk_sensor_id; Type: FK CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY public.mqtt_temps + ADD CONSTRAINT fk_sensor_id FOREIGN KEY (sensor_id) REFERENCES public.sensors(id); + + +-- +-- Name: sensors fk_sensors; Type: FK CONSTRAINT; Schema: public; Owner: - -- -ALTER TABLE ONLY public.room - ADD CONSTRAINT fk_location FOREIGN KEY (location_id) REFERENCES public.location(id); +ALTER TABLE ONLY public.sensors + ADD CONSTRAINT fk_sensors FOREIGN KEY (room) REFERENCES public.rooms(id); -- |