summaryrefslogtreecommitdiff
path: root/src/home/database
diff options
context:
space:
mode:
Diffstat (limited to 'src/home/database')
-rw-r--r--src/home/database/__init__.py29
-rw-r--r--src/home/database/__init__.pyi11
-rw-r--r--src/home/database/bots.py104
-rw-r--r--src/home/database/clickhouse.py10
-rw-r--r--src/home/database/inverter.py102
-rw-r--r--src/home/database/mysql.py47
-rw-r--r--src/home/database/sensors.py66
-rw-r--r--src/home/database/simple_state.py46
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]
generated by cgit v1.2.3 (git 2.25.1) at 2024-11-24 21:12:46 +0000