room_cal/room_cal/store/_sqlite.py

import logging
import sqlite3
from contextlib import asynccontextmanager
from datetime import datetime
from pathlib import Path

from room_cal.store._backend import EventRecord, register_backend

_log = logging.getLogger(__name__)

_CREATE_TABLE = '''
CREATE TABLE IF NOT EXISTS events (
  uid         TEXT NOT NULL PRIMARY KEY,
  start       TEXT NOT NULL,
  end         TEXT NOT NULL,
  all_day     INTEGER NOT NULL,
  title       TEXT NOT NULL,
  description TEXT NOT NULL DEFAULT '',
  x_data      TEXT
)
'''

_CREATE_INDEX = 'CREATE INDEX IF NOT EXISTS idx_events_start_end ON events(start, end)'

# This is not a carefully optimized SQL implementation. It shoves JSON blobs into a column
# But it is very easy to map to CalDAV, which is where the room_cal module was born, and
# it's so much faster than CalDAV that it's hard to care about the simplistic design
_COLS = 'uid, start, end, all_day, title, description, x_data'

_MIGRATE_DROP_CALENDAR_NAME = '''
CREATE TABLE events_new (
  uid         TEXT NOT NULL PRIMARY KEY,
  start       TEXT NOT NULL,
  end         TEXT NOT NULL,
  all_day     INTEGER NOT NULL,
  title       TEXT NOT NULL,
  description TEXT NOT NULL DEFAULT '',
  x_data      TEXT
);
INSERT INTO events_new SELECT uid, start, end, all_day, title, description, x_data FROM events;
DROP TABLE events;
ALTER TABLE events_new RENAME TO events;
CREATE INDEX IF NOT EXISTS idx_events_start_end ON events(start, end);
'''


@register_backend('sqlite')
class SQLiteBackend:
  def __init__(self, db_path: Path):
    self._db_path = db_path

  @classmethod
  def from_store_config(cls, store_cfg) -> 'SQLiteBackend':
    import sqlite3
    from room_cal._utils import data_home
    db_path = Path(store_cfg.sqlite.db_path)
    if not db_path.is_absolute():
      db_path = data_home() / db_path
    with sqlite3.connect(db_path) as conn:
      cols = {row[1] for row in conn.execute('PRAGMA table_info(events)')}
      if 'calendar_name' in cols:
        conn.executescript(_MIGRATE_DROP_CALENDAR_NAME)
      else:
        conn.execute(_CREATE_TABLE)
        conn.execute(_CREATE_INDEX)
    return cls(db_path)

  async def health_check(self) -> tuple[bool, str | None]:
    try:
      async with self._connect() as db:
        await db.execute('SELECT 1')
    except Exception as e:
      return False, str(e)
    return True, None

  @asynccontextmanager
  async def _connect(self):
    import aiosqlite
    try:
      async with aiosqlite.connect(self._db_path) as db:
        yield db
    except sqlite3.OperationalError as e:
      raise sqlite3.OperationalError(f"SQLite error for {self._db_path}: {e}") from None

  def _to_record(self, row) -> EventRecord:
    uid, start, end, all_day, title, description, x_data = row
    return EventRecord(
      uid=uid,
      start=datetime.fromisoformat(start),
      end=datetime.fromisoformat(end),
      all_day=bool(all_day),
      title=title,
      description=description,
      x_data=x_data,
    )

  async def fetch_range(self, start: datetime, end: datetime) -> list[EventRecord]:
    async with self._connect() as db:
      await db.execute(_CREATE_TABLE)
      cursor = await db.execute(
        f'SELECT {_COLS} FROM events WHERE start < ? AND end > ? ORDER BY start',
        (end.isoformat(), start.isoformat()),
      )
      rows = await cursor.fetchall()
    return [self._to_record(r) for r in rows]

  async def create(self, record: EventRecord) -> EventRecord:
    async with self._connect() as db:
      await db.execute(_CREATE_TABLE)
      await db.execute(
        f'INSERT INTO events ({_COLS}) VALUES (?, ?, ?, ?, ?, ?, ?)',
        (record.uid, record.start.isoformat(), record.end.isoformat(),
         int(record.all_day), record.title, record.description, record.x_data),
      )
      await db.commit()
    _log.info('create date=%s title=%r', record.start.date(), record.title)
    return record

  async def update(self, uid: str, *, title: str | None = None,
                   description: str | None = None, x_data: str | None = None) -> EventRecord:
    async with self._connect() as db:
      await db.execute(_CREATE_TABLE)
      if title is not None:
        await db.execute('UPDATE events SET title = ? WHERE uid = ?', (title, uid))
      if description is not None:
        await db.execute('UPDATE events SET description = ? WHERE uid = ?', (description, uid))
      if x_data is not None:
        await db.execute('UPDATE events SET x_data = ? WHERE uid = ?', (x_data, uid))
      await db.commit()
      cursor = await db.execute(f'SELECT {_COLS} FROM events WHERE uid = ?', (uid,))
      row = await cursor.fetchone()
    _log.info('update uid=%s', uid)
    return self._to_record(row)

  async def delete(self, uid: str) -> None:
    async with self._connect() as db:
      await db.execute(_CREATE_TABLE)
      await db.execute('DELETE FROM events WHERE uid = ?', (uid,))
      await db.commit()
    _log.info('delete uid=%s', uid)