db_manager never initialized - aggregates feature completely broken

[smeisens]

Environment

• WeeWX version: 5.2.0
• weewx-mqtt/publish version: 1.0.1
• Python version: 3.13
• Database: SQLite (weewx.sdb)
• Operating System: Debian 13

Description

The aggregates feature documented in the [skill documentation](https://weewx-mqtt.github.io/publish/common-options/topics/topic-name/aggregates/observation-name/) is completely non-functional due to db_manager never being initialized.

Configuration

[MQTTPublish]
    [[topics]]
        [[[irrigation/weather]]]
            type = json
            unit_system = METRICWX
            binding = archive
            retain = true
            
            [[[[aggregates]]]]
                [[[[[rain_7day]]]]]
                    observation = rain
                    aggregation = sum
                    period = last7days

Error

Exception in thread MQTTPublish-6671:
Traceback (most recent call last):
  File "/usr/share/weewx/weedb/sqlite.py", line 38, in guarded_fn
    return fn(*args, **kwargs)
  File "/usr/share/weewx/weedb/sqlite.py", line 149, in cursor
    return self.connection.cursor(Cursor)
sqlite3.ProgrammingError: SQLite objects created in a thread can only be used in that same thread. 
The object was created in thread id 140251561427200 and this is thread id 140251070715584.

Root Cause

Line 690 in PublishWeeWXThread.__init__():

self.db_manager = None

The db_manager is initialized to None and never actually created. When aggregates are configured, line 733 tries to use it:

weewx.xtypes.get_aggregate(topic_dict['aggregates'][aggregate_observation]['observation'],
                           time_span, topic_dict['aggregates'][aggregate_observation]['aggregation'],
                           self.db_manager)  # ← Still None!

This causes an AttributeError: 'NoneType' object has no attribute 'table_name'.

Additionally, even if we tried to use db_binder.get_manager(), it would return a manager with a SQLite connection created in the main thread, which cannot be used in the publishing thread due to SQLite's same-thread requirement.

Impact

• Aggregates feature is completely non-functional
• Users cannot publish calculated values (7-day rainfall, average temperatures, etc.)
• This appears to be an unfinished feature - code exists but was never completed
• No examples exist of aggregates in production use
• Unit tests use mocked db_manager objects which don't exhibit the threading issue

Proposed Fix

The db_manager must be created inside the publishing thread with a SQLite connection that has check_same_thread=False.

Modified PublishWeeWXThread.run() method:

def run(self):
    self.running = True
    self.logger.loginf(f"Starting publishing loop {self.name}.")
    threading.current_thread().name = f"MQTTPublish-{threading.get_native_id()}"

    # Create NEW db_manager in THIS thread (thread-safe for SQLite)
    if self.db_binder:
        import sqlite3
        import weewx.manager
        
        # Standard WeeWX database path
        db_path = '/var/lib/weewx/weewx.sdb'
        
        # Create raw SQLite connection with check_same_thread=False
        raw_connection = sqlite3.connect(db_path, check_same_thread=False)
        
        # Create minimal weedb-compatible connection wrapper
        class ThreadSafeConnection:
            def __init__(self, conn, path):
                self.connection = conn
                self.database_name = path
                
            def cursor(self):
                return self.connection.cursor()
                
            def execute(self, *args, **kwargs):
                return self.connection.execute(*args, **kwargs)
                
            def commit(self):
                return self.connection.commit()
                
            def rollback(self):
                return self.connection.rollback()
                
            def close(self):
                return self.connection.close()
            
            def columnsOf(self, table_name):
                """Return list of column names in table"""
                cursor = self.connection.cursor()
                cursor.execute(f"PRAGMA table_info({table_name})")
                return [row[1] for row in cursor.fetchall()]
            
            def tables(self):
                """Return list of table names"""
                cursor = self.connection.cursor()
                cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
                return [row[0] for row in cursor.fetchall()]
        
        connection = ThreadSafeConnection(raw_connection, db_path)
        self.db_manager = weewx.manager.DaySummaryManager(connection)

    # need to instantiate inside thread
    self.publisher = AbstractPublisher.get_publisher(self.logger, self, self.mqtt_config)
    
    # ... rest of run() method

Testing

With this fix applied:

1. ✅ Aggregates are successfully calculated
2. ✅ MQTT messages contain the calculated values
3. ✅ No threading errors occur
4. ✅ System runs stably over multiple archive intervals

Notes

• The solution shown uses a hardcoded database path /var/lib/weewx/weewx.sdb for clarity
• A production implementation should get the database path from db_binder.database or similar
• The ThreadSafeConnection class implements the minimal interface required by DaySummaryManager
• Setting check_same_thread=False is safe here because the connection is only used within this single thread
• This fix only addresses SQLite; MySQL/other databases may need different handling

Alternative Approach

Instead of creating a new connection, one could explore if db_binder provides a method to get a thread-safe manager. However, initial investigation suggests this would still require per-thread connection creation for SQLite.

Workaround

Until this is fixed, the aggregates feature cannot be used. Users must calculate these values externally.