adding location and rooms

updating yr to add location

added coule tables to schema I forgot

2 files changed, 145 insertions, 4 deletions

diff --git a/schema.sql b/schema.sql
index 9fbb3a2..66fbd08 100644
--- a/schema.sql
+++ b/schema.sql
@@ -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);
 
 
 --
diff --git a/scripts/yr.py b/scripts/yr.py
index f6b654c..243f531 100644
--- a/scripts/yr.py
+++ b/scripts/yr.py
@@ -7,6 +7,7 @@ import requests
 
 import common
 
+location = int(os.environ['el_location'])
 lat = str(os.environ['el_yr_lat'])
 lon = str(os.environ['el_yr_lon'])
 
@@ -42,6 +43,7 @@ for item in data["properties"]["timeseries"]:
     details = item["data"]["instant"]["details"]
     values.append((
         item["time"],
+        location,
         details["air_temperature"],
         details["air_pressure_at_sea_level"],
         details["cloud_area_fraction"],
@@ -50,9 +52,17 @@ for item in data["properties"]["timeseries"]:
         details["wind_speed"]))
 
 # SQL
-sql = """ INSERT INTO yr
-          VALUES(%s, %s, %s, %s, %s, %s, %s)
-          ON CONFLICT (time) DO UPDATE SET
+sql = """ INSERT INTO yr (
+              time,
+              location,
+              air_temperature,
+              air_pressure_at_sea_level,
+              cloud_area_fraction,
+              relative_humidity,
+              wind_from_direction,
+              wind_speed)
+          VALUES(%s, %s, %s, %s, %s, %s, %s, %s)
+          ON CONFLICT (time, location) DO UPDATE SET
               air_temperature=EXCLUDED.air_temperature,
               air_pressure_at_sea_level=EXCLUDED.air_pressure_at_sea_level,
               cloud_area_fraction=EXCLUDED.cloud_area_fraction,