From 60f51b9c08a5bcc04659b7c8c20ac22df37bb783 Mon Sep 17 00:00:00 2001
From: Dennis Eriksen <d@ennis.no>
Date: Fri, 17 Mar 2023 10:45:41 +0100
Subject: updating schema

---
 schema.sql | 80 +++++++++++++++++++++++++++++++++++++++++++++++++++-----------
 1 file changed, 66 insertions(+), 14 deletions(-)

(limited to 'schema.sql')

diff --git a/schema.sql b/schema.sql
index 66fbd08..16643b3 100644
--- a/schema.sql
+++ b/schema.sql
@@ -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,13 +405,21 @@ 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);
 
 
 --
-- 
cgit v1.2.3

