From cc137038fa8c1db5e24a2ba103c7dbee79affa95 Mon Sep 17 00:00:00 2001
From: Dennis Eriksen <d@ennis.no>
Date: Sat, 4 Feb 2023 21:53:54 +0100
Subject: adding location and rooms

updating yr to add location

added coule tables to schema I forgot
---
 schema.sql | 133 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++-
 1 file changed, 132 insertions(+), 1 deletion(-)

(limited to 'schema.sql')

diff --git a/schema.sql b/schema.sql
index 9fbb3a2..66fbd08 100644
--- a/schema.sql
+++ b/schema.sql
@@ -65,6 +65,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: -
 --
@@ -152,6 +215,34 @@ CREATE SEQUENCE public.neohub_id_seq
 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,
@@ -199,6 +291,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: -
 --
@@ -237,6 +336,22 @@ ALTER TABLE ONLY public.entsoe
     ADD CONSTRAINT entsoe_pkey PRIMARY KEY (starttime, zone);
 
 
+--
+-- 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: -
 --
@@ -261,6 +376,14 @@ ALTER TABLE ONLY public.neohub
     ADD CONSTRAINT neohub_pkey PRIMARY KEY (id);
 
 
+--
+-- 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);
 
 
 --
-- 
cgit v1.2.3

