olaf
f55c1fe6f1
- Added voltage monitoring table and storage pipeline - Extended pool payload to 17 bytes with VCC field (protocol v2) - Improved database connection pool resilience (reduced pool size, aggressive recycling, pool disposal on failure) - Added environment variable support for database configuration - Fixed receiver MQTT deprecation warning (CallbackAPIVersion.VERSION2) - Silenced excessive RSSI status logging in receiver - Added reset flag tracking and reporting - Updated Docker compose with DB config and log rotation limits
97 lines
4.0 KiB
Python
97 lines
4.0 KiB
Python
from sqlalchemy import create_engine, Column, Integer, String, DateTime, Float, ForeignKey
|
|
# from sqlalchemy.ext.declarative import declarative_base
|
|
from sqlalchemy.orm import sessionmaker, relationship, declarative_base
|
|
from datetime import datetime, timezone
|
|
|
|
# Create a base class for declarative class definitions
|
|
Base = declarative_base()
|
|
|
|
# Define the Sensor table
|
|
class Sensor(Base):
|
|
__tablename__ = 'sensors'
|
|
id = Column(Integer, primary_key=True)
|
|
mqtt_name = Column(String(50)) # Removed unique=True to allow multiple pool sensors with same name
|
|
mqtt_id = Column(String(50))
|
|
position = Column(String(50))
|
|
room = Column(String(50))
|
|
battery = Column(Float)
|
|
rain_offset = Column(Float, default=0.0) # Cumulative offset for rain sensor resets
|
|
last_rain_value = Column(Float, default=0.0) # Last reported rain value (for reset detection)
|
|
node_id = Column(Integer, nullable=True) # For pool sensors: the nodeId (1, 2, etc.) that generates mqtt_id
|
|
sensor_type = Column(String(50), nullable=True) # Sensor type: 'BME280', 'DS18B20', 'Bresser-6in1', etc.
|
|
last_contact = Column(DateTime, default=lambda: datetime.now(timezone.utc), nullable=True) # Last transmission received from sensor
|
|
|
|
# Define the TemperatureInside table
|
|
class TemperatureInside(Base):
|
|
__tablename__ = 'temperature_inside'
|
|
id = Column(Integer, primary_key=True)
|
|
sensor_id = Column(Integer, ForeignKey('sensors.id'))
|
|
sensor = relationship('Sensor', backref='temperature_inside')
|
|
timestamp = Column(DateTime, default=datetime.utcnow)
|
|
temperature_c = Column(Float)
|
|
|
|
# Define the TemperatureOutside table
|
|
class TemperatureOutside(Base):
|
|
__tablename__ = 'temperature_outside'
|
|
id = Column(Integer, primary_key=True)
|
|
sensor_id = Column(Integer, ForeignKey('sensors.id'))
|
|
sensor = relationship('Sensor', backref='temperature_outside')
|
|
timestamp = Column(DateTime, default=datetime.utcnow)
|
|
temperature_c = Column(Float)
|
|
|
|
|
|
# Define the HumidityInside table
|
|
class HumidityInside(Base):
|
|
__tablename__ = 'humidity_inside'
|
|
id = Column(Integer, primary_key=True)
|
|
sensor_id = Column(Integer, ForeignKey('sensors.id'))
|
|
sensor = relationship('Sensor', backref='humidity_inside')
|
|
timestamp = Column(DateTime, default=datetime.utcnow)
|
|
humidity = Column(Float)
|
|
|
|
# Define the HumidityInside table
|
|
class HumidityOutside(Base):
|
|
__tablename__ = 'humidity_outside'
|
|
id = Column(Integer, primary_key=True)
|
|
sensor_id = Column(Integer, ForeignKey('sensors.id'))
|
|
sensor = relationship('Sensor', backref='humidity_outside')
|
|
timestamp = Column(DateTime, default=datetime.utcnow)
|
|
humidity = Column(Float)
|
|
|
|
# Define the AirPressure table
|
|
class AirPressure(Base):
|
|
__tablename__ = 'air_pressure'
|
|
id = Column(Integer, primary_key=True)
|
|
sensor_id = Column(Integer, ForeignKey('sensors.id'))
|
|
sensor = relationship('Sensor', backref='air_pressure')
|
|
timestamp = Column(DateTime, default=datetime.utcnow)
|
|
pressure_rel = Column(Float)
|
|
|
|
# Define the wind table
|
|
class Wind(Base):
|
|
__tablename__ = 'wind'
|
|
id = Column(Integer, primary_key=True)
|
|
sensor_id = Column(Integer, ForeignKey('sensors.id'))
|
|
sensor = relationship('Sensor', backref='wind')
|
|
timestamp = Column(DateTime, default=datetime.utcnow)
|
|
average_speed = Column(Float)
|
|
direction = Column(Float)
|
|
gust = Column(Float)
|
|
|
|
# Define the precipitation table
|
|
class Precipitation(Base):
|
|
__tablename__ = 'precipitation'
|
|
id = Column(Integer, primary_key=True)
|
|
sensor_id = Column(Integer, ForeignKey('sensors.id'))
|
|
sensor = relationship('Sensor', backref='precipitation')
|
|
timestamp = Column(DateTime, default=datetime.utcnow)
|
|
precipitation = Column(Float)
|
|
|
|
# Define the Voltage table
|
|
class Voltage(Base):
|
|
__tablename__ = 'voltage'
|
|
id = Column(Integer, primary_key=True)
|
|
sensor_id = Column(Integer, ForeignKey('sensors.id'))
|
|
sensor = relationship('Sensor', backref='voltage')
|
|
timestamp = Column(DateTime, default=datetime.utcnow)
|
|
vcc_mv = Column(Integer) |