Skip to content

Speakr is a personal, self-hosted web application designed for transcribing audio recordings

Python
8.7 KB
Raw Blame
"""Upgrade path for the mailr API migrations (spec section 2).

Each historical schema fixture is loaded, rows are seeded the way a real
installation holds them, the current migrations run, and the new columns are
checked: present, with their default, and no existing value changed. A second
startup must be a no-op. New migrations in this work add a case below.
"""

import os
import sqlite3
import sys

import pytest

sys.path.insert(0, os.path.dirname(os.path.dirname(os.path.abspath(__file__))))

from tests.test_upgrade_path import FIXTURE_DIR, FIXTURES, _fixture_app
from src.init_db import initialize_database


def _load(tmp_path, fixture):
    db_path = str(tmp_path / fixture.replace(".sql", ".db"))
    con = sqlite3.connect(db_path)
    con.executescript(open(os.path.join(FIXTURE_DIR, fixture)).read())
    con.commit()
    return db_path, con


def _columns(con, table):
    return {row[1] for row in con.execute(f'PRAGMA table_info("{table}")')}


def _upgrade(db_path):
    app = _fixture_app(db_path)
    with app.app_context():
        initialize_database(app)
    return app


@pytest.mark.parametrize("fixture", FIXTURES, ids=[f[:-4] for f in FIXTURES])
def test_api_token_scopes_column(tmp_path, fixture):
    db_path, con = _load(tmp_path, fixture)
    if "api_token" not in {r[0] for r in con.execute("SELECT name FROM sqlite_master WHERE type='table'")}:
        con.close()
        pytest.skip("api_token table not in this release")
    con.execute('INSERT INTO "user" (id, username, email, password) VALUES (1, ?, ?, ?)',
                ("u", "[email protected]", "hash"))
    con.execute("INSERT INTO api_token (id, user_id, token_hash, name, created_at, expires_at, revoked) "
                "VALUES (5, 1, 'abc123', 'n8n', '2026-01-01 00:00:00', '2027-01-01 00:00:00', 1)")
    con.commit()
    con.close()

    _upgrade(db_path)
    _upgrade(db_path)   # second start: no-op, no error

    con = sqlite3.connect(db_path)
    assert "scopes" in _columns(con, "api_token")
    row = con.execute("SELECT token_hash, name, expires_at, revoked, scopes FROM api_token WHERE id = 5").fetchone()
    con.close()
    assert row[:4] == ("abc123", "n8n", "2027-01-01 00:00:00", 1)
    assert row[4] is None, "existing tokens keep full access (scopes NULL)"


@pytest.mark.parametrize("fixture", FIXTURES, ids=[f[:-4] for f in FIXTURES])
def test_recording_updated_at_and_tombstones(tmp_path, fixture):
    """G2: updated_at is filled from completed_at or created_at, once."""
    db_path, con = _load(tmp_path, fixture)
    cols = _columns(con, "recording")
    con.execute('INSERT INTO "user" (id, username, email, password) VALUES (1, ?, ?, ?)',
                ("u", "[email protected]", "hash"))
    extra = {"keep_audio_only": 0} if "keep_audio_only" in cols else {}

    def _insert(**values):
        values.update(extra)
        names = ", ".join(values)
        marks = ", ".join("?" for _ in values)
        con.execute(f"INSERT INTO recording ({names}) VALUES ({marks})", tuple(values.values()))

    _insert(id=7, user_id=1, title="Old", status="COMPLETED", created_at="2024-03-01 10:00:00")
    if "completed_at" in cols:
        _insert(id=8, user_id=1, title="Done", status="COMPLETED", created_at="2024-03-02 10:00:00",
                completed_at="2024-03-02 11:30:00")
    con.commit()
    con.close()

    _upgrade(db_path)
    con = sqlite3.connect(db_path)
    con.execute("UPDATE recording SET updated_at = '2025-05-05 05:05:05' WHERE id = 7")
    con.commit()
    con.close()
    _upgrade(db_path)   # second start: the backfill does not run again

    con = sqlite3.connect(db_path)
    assert "updated_at" in _columns(con, "recording")
    rows = dict(con.execute("SELECT id, updated_at FROM recording").fetchall())
    titles = dict(con.execute("SELECT id, title FROM recording").fetchall())
    indexes = {r[1] for r in con.execute("PRAGMA index_list('recording')")}
    tables = {r[0] for r in con.execute("SELECT name FROM sqlite_master WHERE type='table'")}
    ledger = con.execute("SELECT count(*) FROM schema_migrations "
                         "WHERE migration_id = '0002_backfill_recording_updated_at'").fetchone()[0]
    since = con.execute("SELECT value FROM system_setting WHERE key = 'changes_feed_since'").fetchone()
    con.close()
    assert rows[7].startswith("2025-05-05 05:05:05"), "second start left the value alone"
    if 8 in rows:
        assert rows[8].startswith("2024-03-02 11:30:00"), "filled from completed_at"
    assert titles[7] == "Old"
    assert {"ix_recording_updated_at", "ix_recording_user_updated"} <= indexes
    assert "recording_tombstone" in tables
    assert ledger == 1 and since and since[0].endswith("Z")
    con = sqlite3.connect(db_path)
    assert con.execute("SELECT count(*) FROM schema_migrations "
                       "WHERE migration_id = '0003_link_transcript_speakers'").fetchone()[0] == 1
    con.close()


@pytest.mark.parametrize("fixture", FIXTURES, ids=[f[:-4] for f in FIXTURES])
def test_webhook_rotation_grace_columns(tmp_path, fixture):
    """W2: previous_secret columns arrive empty; the current secret is untouched."""
    db_path, con = _load(tmp_path, fixture)
    if "webhook" not in {r[0] for r in con.execute("SELECT name FROM sqlite_master WHERE type='table'")}:
        con.close()
        pytest.skip("webhook table not in this release")
    con.execute('INSERT INTO "user" (id, username, email, password) VALUES (1, ?, ?, ?)',
                ("u", "[email protected]", "hash"))
    cols = _columns(con, "webhook")
    values = {"id": 3, "user_id": 1, "name": "n8n", "url": "https://example.com/h", "secret": "s" * 40,
              "events": "[]", "enabled": 1, "auto_paused": 0, "consecutive_failures": 0,
              "created_at": "2026-01-01 00:00:00", "updated_at": "2026-01-01 00:00:00", "allow_http": 0}
    values = {k: v for k, v in values.items() if k in cols}
    con.execute(f"INSERT INTO webhook ({', '.join(values)}) VALUES ({', '.join('?' for _ in values)})",
                tuple(values.values()))
    con.commit()
    con.close()

    _upgrade(db_path)
    _upgrade(db_path)

    con = sqlite3.connect(db_path)
    assert {"previous_secret", "previous_secret_expires_at", "include_shared"} <= _columns(con, "webhook")
    assert con.execute("SELECT include_shared FROM webhook WHERE id = 3").fetchone()[0] in (0, False)
    row = con.execute("SELECT secret, previous_secret, previous_secret_expires_at FROM webhook WHERE id = 3").fetchone()
    con.close()
    assert row == ("s" * 40, None, None)


@pytest.mark.parametrize("fixture", FIXTURES, ids=[f[:-4] for f in FIXTURES])
def test_upload_idempotency_and_external_refs(tmp_path, fixture):
    """G8: the idempotency column and index, and the reference table."""
    db_path, con = _load(tmp_path, fixture)
    con.close()
    _upgrade(db_path)
    _upgrade(db_path)
    con = sqlite3.connect(db_path)
    assert "upload_idempotency_key" in _columns(con, "recording")
    indexes = {r[1]: r[2] for r in con.execute("PRAGMA index_list('recording')")}
    assert indexes.get("ux_recording_user_idem") == 1 and "ix_recording_user_idem" not in indexes
    assert {"recording_id", "user_id", "system", "kind", "ref", "url", "label"} <= _columns(con, "recording_external_ref")
    con.close()


@pytest.mark.parametrize("fixture", FIXTURES, ids=[f[:-4] for f in FIXTURES])
def test_speaker_contact_columns(tmp_path, fixture):
    """G6: email, aliases and updated_at arrive empty; names stay."""
    db_path, con = _load(tmp_path, fixture)
    if "speaker" not in {r[0] for r in con.execute("SELECT name FROM sqlite_master WHERE type='table'")}:
        con.close()
        pytest.skip("speaker table not in this release")
    con.execute('INSERT INTO "user" (id, username, email, password) VALUES (1, ?, ?, ?)', ("u", "[email protected]", "hash"))
    con.execute("INSERT INTO speaker (id, name, user_id) VALUES (4, 'Dana', 1)")
    con.commit()
    con.close()
    _upgrade(db_path)
    _upgrade(db_path)
    con = sqlite3.connect(db_path)
    assert {"email", "aliases", "updated_at"} <= _columns(con, "speaker")
    assert "ix_speaker_user_email" in {r[1] for r in con.execute("PRAGMA index_list('speaker')")}
    assert con.execute("SELECT name, email, aliases FROM speaker WHERE id = 4").fetchone() == ("Dana", None, None)
    con.close()


@pytest.mark.parametrize("fixture", FIXTURES, ids=[f[:-4] for f in FIXTURES])
def test_share_expiry_column(tmp_path, fixture):
    """G10: share.expires_at arrives empty, so existing links never expire."""
    db_path, con = _load(tmp_path, fixture)
    if "share" not in {r[0] for r in con.execute("SELECT name FROM sqlite_master WHERE type='table'")}:
        con.close()
        pytest.skip("share table not in this release")
    con.close()
    _upgrade(db_path)
    _upgrade(db_path)
    con = sqlite3.connect(db_path)
    assert "expires_at" in _columns(con, "share")
    con.close()