initial public

8 files changed, 415 insertions, 0 deletions

diff --git a/src/home/database/__init__.py b/src/home/database/__init__.py
new file mode 100644
index 0000000..b50cbce
--- /dev/null
+++ b/src/home/database/__init__.py
@@ -0,0 +1,29 @@
+import importlib
+
+__all__ = [
+    'get_mysql',
+    'mysql_now',
+    'get_clickhouse',
+    'SimpleState',
+
+    'SensorsDatabase',
+    'InverterDatabase',
+    'BotsDatabase'
+]
+
+
+def __getattr__(name: str):
+    if name in __all__:
+        if name.endswith('Database'):
+            file = name[:-8].lower()
+        elif 'mysql' in name:
+            file = 'mysql'
+        elif 'clickhouse' in name:
+            file = 'clickhouse'
+        else:
+            file = 'simple_state'
+
+        module = importlib.import_module(f'.{file}', __name__)
+        return getattr(module, name)
+
+    raise AttributeError(f"module {__name__!r} has no attribute {name!r}")
diff --git a/src/home/database/__init__.pyi b/src/home/database/__init__.pyi
new file mode 100644
index 0000000..31aae5d
--- /dev/null
+++ b/src/home/database/__init__.pyi
@@ -0,0 +1,11 @@
+from .mysql import (
+    get_mysql as get_mysql,
+    mysql_now as mysql_now
+)
+from .clickhouse import get_clickhouse as get_clickhouse
+
+from simple_state import SimpleState as SimpleState
+
+from .sensors import SensorsDatabase as SensorsDatabase
+from .inverter import InverterDatabase as InverterDatabase
+from .bots import BotsDatabase as BotsDatabase
diff --git a/src/home/database/bots.py b/src/home/database/bots.py
new file mode 100644
index 0000000..bc490e1
--- /dev/null
+++ b/src/home/database/bots.py
@@ -0,0 +1,104 @@
+import pytz
+
+from .mysql import mysql_now, MySQLDatabase, datetime_fmt
+from ..api.types import (
+    BotType,
+    SoundSensorLocation
+)
+from typing import Optional
+from datetime import datetime
+from html import escape
+
+
+class OpenwrtLogRecord:
+    id: int
+    log_time: datetime
+    received_time: datetime
+    text: str
+
+    def __init__(self, id, text, log_time, received_time):
+        self.id = id
+        self.text = text
+        self.log_time = log_time
+        self.received_time = received_time
+
+    def __repr__(self):
+        return f"<b>{self.log_time.strftime('%H:%M:%S')}</b> {escape(self.text)}"
+
+
+class BotsDatabase(MySQLDatabase):
+    def add_request(self,
+                    bot: BotType,
+                    user_id: int,
+                    message: str):
+        with self.cursor() as cursor:
+            cursor.execute("INSERT INTO requests_log (user_id, message, bot, time) VALUES (%s, %s, %s, %s)",
+                           (user_id, message, bot.name.lower(), mysql_now()))
+        self.commit()
+
+    def add_openwrt_logs(self,
+                         lines: list[tuple[datetime, str]]):
+        now = datetime.now()
+        with self.cursor() as cursor:
+            for line in lines:
+                time, text = line
+                cursor.execute("INSERT INTO openwrt (log_time, received_time, text) VALUES (%s, %s, %s)",
+                               (time.strftime(datetime_fmt), now.strftime(datetime_fmt), text))
+        self.commit()
+
+    def add_sound_hits(self,
+                       hits: list[tuple[SoundSensorLocation, int]],
+                       time: datetime):
+        with self.cursor() as cursor:
+            for loc, count in hits:
+                cursor.execute("INSERT INTO sound_hits (location, `time`, hits) VALUES (%s, %s, %s)",
+                               (loc.name.lower(), time.strftime(datetime_fmt), count))
+            self.commit()
+
+    def get_sound_hits(self,
+                       location: SoundSensorLocation,
+                       after: Optional[datetime] = None,
+                       last: Optional[int] = None) -> list[dict]:
+        with self.cursor(dictionary=True) as cursor:
+            sql = "SELECT `time`, hits FROM sound_hits WHERE location=%s"
+            args = [location.name.lower()]
+
+            if after:
+                sql += ' AND `time` >= %s ORDER BY time DESC'
+                args.append(after)
+            elif last:
+                sql += ' ORDER BY time DESC LIMIT 0, %s'
+                args.append(last)
+            else:
+                raise ValueError('no `after`, no `last`, what do you expect?')
+
+            cursor.execute(sql, tuple(args))
+            data = []
+            for row in cursor.fetchall():
+                data.append({
+                    'time': row['time'],
+                    'hits': row['hits']
+                })
+            return data
+
+    def get_openwrt_logs(self,
+                         filter_text: str,
+                         min_id: int,
+                         limit: int = None) -> list[OpenwrtLogRecord]:
+        tz = pytz.timezone('Europe/Moscow')
+        with self.cursor(dictionary=True) as cursor:
+            sql = "SELECT * FROM openwrt WHERE text LIKE %s AND id > %s"
+            if limit is not None:
+                sql += f" LIMIT {limit}"
+
+            cursor.execute(sql, (f'%{filter_text}%', min_id))
+            data = []
+            for row in cursor.fetchall():
+                data.append(OpenwrtLogRecord(
+                    id=int(row['id']),
+                    text=row['text'],
+                    log_time=row['log_time'].astimezone(tz),
+                    received_time=row['received_time'].astimezone(tz)
+                ))
+
+            return data
diff --git a/src/home/database/clickhouse.py b/src/home/database/clickhouse.py
new file mode 100644
index 0000000..4a2a247
--- /dev/null
+++ b/src/home/database/clickhouse.py
@@ -0,0 +1,10 @@
+from clickhouse_driver import Client as ClickhouseClient
+
+_links = {}
+
+
+def get_clickhouse(db: str) -> ClickhouseClient:
+    if db not in _links:
+        _links[db] = ClickhouseClient.from_url(f'clickhouse://localhost/{db}')
+
+    return _links[db]
diff --git a/src/home/database/inverter.py b/src/home/database/inverter.py
new file mode 100644
index 0000000..8902f04
--- /dev/null
+++ b/src/home/database/inverter.py
@@ -0,0 +1,102 @@
+from .clickhouse import get_clickhouse
+from time import time
+
+
+class InverterDatabase:
+    def __init__(self):
+        self.db = get_clickhouse('solarmon')
+
+    def add_generation(self, home_id: int, client_time: int, watts: int) -> None:
+        self.db.execute(
+            'INSERT INTO generation (ClientTime, ReceivedTime, HomeID, Watts) VALUES',
+            [[client_time, round(time()), home_id, watts]]
+        )
+
+    def add_status(self, home_id: int,
+                   client_time: int,
+                   grid_voltage: int,
+                   grid_freq: int,
+                   ac_output_voltage: int,
+                   ac_output_freq: int,
+                   ac_output_apparent_power: int,
+                   ac_output_active_power: int,
+                   output_load_percent: int,
+                   battery_voltage: int,
+                   battery_voltage_scc: int,
+                   battery_voltage_scc2: int,
+                   battery_discharging_current: int,
+                   battery_charging_current: int,
+                   battery_capacity: int,
+                   inverter_heat_sink_temp: int,
+                   mppt1_charger_temp: int,
+                   mppt2_charger_temp: int,
+                   pv1_input_power: int,
+                   pv2_input_power: int,
+                   pv1_input_voltage: int,
+                   pv2_input_voltage: int,
+                   mppt1_charger_status: int,
+                   mppt2_charger_status: int,
+                   battery_power_direction: int,
+                   dc_ac_power_direction: int,
+                   line_power_direction: int,
+                   load_connected: int) -> None:
+        self.db.execute("""INSERT INTO status (
+            ClientTime,
+            ReceivedTime,
+            HomeID,
+            GridVoltage,
+            GridFrequency,
+            ACOutputVoltage,
+            ACOutputFrequency,
+            ACOutputApparentPower,
+            ACOutputActivePower,
+            OutputLoadPercent,
+            BatteryVoltage,
+            BatteryVoltageSCC,
+            BatteryVoltageSCC2,
+            BatteryDischargingCurrent,
+            BatteryChargingCurrent,
+            BatteryCapacity,
+            HeatSinkTemp,
+            MPPT1ChargerTemp,
+            MPPT2ChargerTemp,
+            PV1InputPower,
+            PV2InputPower,
+            PV1InputVoltage,
+            PV2InputVoltage,
+            MPPT1ChargerStatus,
+            MPPT2ChargerStatus,
+            BatteryPowerDirection,
+            DCACPowerDirection,
+            LinePowerDirection,
+            LoadConnected) VALUES""", [[
+            client_time,
+            round(time()),
+            home_id,
+            grid_voltage,
+            grid_freq,
+            ac_output_voltage,
+            ac_output_freq,
+            ac_output_apparent_power,
+            ac_output_active_power,
+            output_load_percent,
+            battery_voltage,
+            battery_voltage_scc,
+            battery_voltage_scc2,
+            battery_discharging_current,
+            battery_charging_current,
+            battery_capacity,
+            inverter_heat_sink_temp,
+            mppt1_charger_temp,
+            mppt2_charger_temp,
+            pv1_input_power,
+            pv2_input_power,
+            pv1_input_voltage,
+            pv2_input_voltage,
+            mppt1_charger_status,
+            mppt2_charger_status,
+            battery_power_direction,
+            dc_ac_power_direction,
+            line_power_direction,
+            load_connected
+        ]])
diff --git a/src/home/database/mysql.py b/src/home/database/mysql.py
new file mode 100644
index 0000000..fe97cd4
--- /dev/null
+++ b/src/home/database/mysql.py
@@ -0,0 +1,47 @@
+import time
+import logging
+
+from mysql.connector import connect, MySQLConnection, Error
+from typing import Optional
+from ..config import config
+
+link: Optional[MySQLConnection] = None
+logger = logging.getLogger(__name__)
+
+datetime_fmt = '%Y-%m-%d %H:%M:%S'
+
+
+def get_mysql() -> MySQLConnection:
+    global link
+
+    if link is not None:
+        return link
+
+    link = connect(
+        host=config['mysql']['host'],
+        user=config['mysql']['user'],
+        password=config['mysql']['password'],
+        database=config['mysql']['database'],
+    )
+    link.time_zone = '+01:00'
+    return link
+
+
+def mysql_now() -> str:
+    return time.strftime('%Y-%m-%d %H:%M:%S')
+
+
+class MySQLDatabase:
+    def __init__(self):
+        self.db = get_mysql()
+
+    def cursor(self, **kwargs):
+        try:
+            self.db.ping(reconnect=True, attempts=2)
+        except Error as e:
+            logger.exception(e)
+            self.db = get_mysql()
+        return self.db.cursor(**kwargs)
+
+    def commit(self):
+        self.db.commit()
diff --git a/src/home/database/sensors.py b/src/home/database/sensors.py
new file mode 100644
index 0000000..ca53dd0
--- /dev/null
+++ b/src/home/database/sensors.py
@@ -0,0 +1,66 @@
+from time import time
+from datetime import datetime
+from typing import Tuple, List
+from .clickhouse import get_clickhouse
+from ..api.types import TemperatureSensorLocation
+
+
+def get_temperature_table(sensor: TemperatureSensorLocation) -> str:
+    if sensor == TemperatureSensorLocation.DIANA:
+        return 'temp_diana'
+
+    elif sensor == TemperatureSensorLocation.STREET:
+        return 'temp_street'
+
+    elif sensor == TemperatureSensorLocation.BIG_HOUSE_1:
+        return 'temp'
+
+    elif sensor == TemperatureSensorLocation.BIG_HOUSE_2:
+        return 'temp_roof'
+
+    elif sensor == TemperatureSensorLocation.SPB1:
+        return 'temp_spb1'
+
+
+class SensorsDatabase:
+    def __init__(self):
+        self.db = get_clickhouse('home')
+
+    def add_temperature(self,
+                        home_id: int,
+                        client_time: int,
+                        sensor: TemperatureSensorLocation,
+                        temp: int,
+                        rh: int):
+        table = get_temperature_table(sensor)
+        sql = """INSERT INTO """ + table + """ (
+                        ClientTime,
+                        ReceivedTime,
+                        HomeID,
+                        Temperature,
+                        RelativeHumidity
+                        ) VALUES"""
+        self.db.execute(sql, [[
+            client_time,
+            int(time()),
+            home_id,
+            temp,
+            rh
+        ]])
+
+    def get_temperature_recordings(self,
+                                   sensor: TemperatureSensorLocation,
+                                   time_range: Tuple[datetime, datetime],
+                                   home_id=1) -> List[tuple]:
+        table = get_temperature_table(sensor)
+        sql = f"""SELECT ClientTime, Temperature, RelativeHumidity 
+            FROM {table}
+            WHERE ClientTime >= %(from)s AND ClientTime <= %(to)s
+            ORDER BY ClientTime"""
+        dt_from, dt_to = time_range
+
+        data = self.db.execute(sql, {
+            'from': dt_from,
+            'to': dt_to
+        })
+        return [(date, temp/100, humidity/100) for date, temp, humidity in data]
diff --git a/src/home/database/simple_state.py b/src/home/database/simple_state.py
new file mode 100644
index 0000000..cada9c8
--- /dev/null
+++ b/src/home/database/simple_state.py
@@ -0,0 +1,46 @@
+import os
+import json
+import atexit
+
+
+class SimpleState:
+    def __init__(self,
+                 file: str,
+                 default: dict = None,
+                 **kwargs):
+        if default is None:
+            default = {}
+        elif type(default) is not dict:
+            raise TypeError('default must be dictionary')
+
+        if not os.path.exists(file):
+            self._data = default
+        else:
+            with open(file, 'r') as f:
+                self._data = json.loads(f.read())
+
+        self._file = file
+        atexit.register(self.__cleanup)
+
+    def __cleanup(self):
+        if hasattr(self, '_file'):
+            with open(self._file, 'w') as f:
+                f.write(json.dumps(self._data))
+        atexit.unregister(self.__cleanup)
+
+    def __del__(self):
+        if 'open' in __builtins__:
+            self.__cleanup()
+
+    def __getitem__(self, key):
+        return self._data[key]
+
+    def __setitem__(self, key, value):
+        self._data[key] = value
+
+    def __contains__(self, key):
+        return key in self._data
+
+    def __delitem__(self, key):
+        if key in self._data:
+            del self._data[key]