Skip to content

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

Python
23.5 KB
Raw Blame
"""
Database schema migration utilities.

IMPORTANT: All migrations must be compatible with both SQLite and PostgreSQL.
- Boolean defaults: SQLite uses 0/1, PostgreSQL requires FALSE/TRUE
- Type differences: SQLite DATETIME -> PostgreSQL TIMESTAMP, BLOB -> BYTEA
- Reserved keywords: "user", "order" etc. must be quoted
- The add_column_if_not_exists() function handles these automatically
- Use create_index_if_not_exists() for index creation with proper quoting
"""

import fcntl
import hashlib
import os
import re
import tempfile
import time
from contextlib import contextmanager

from sqlalchemy import inspect, text

# Arbitrary but fixed key for PostgreSQL advisory locking, derived from a name so
# it cannot collide with an application lock chosen the same way.
_PG_MIGRATION_LOCK_KEY = int.from_bytes(
    hashlib.sha256(b'speakr.startup_migrations').digest()[:4], 'big'
)


@contextmanager
def migration_lock(engine, logger=None, timeout=120):
    """Serialise startup migrations across every process touching this database.

    `initialize_database()` runs about five times per container start: from the
    entrypoint, from the admin-user script, and once per gunicorn worker, several
    of them simultaneously. Without a lock they interleave, and a migration can
    see a half-applied schema produced by a sibling process.

    Yields True when the lock was taken and False when it timed out. Timing out
    is not fatal and the caller should proceed: migrations are individually
    idempotent, so the lock is protection against wasted work and interleaving
    rather than a correctness requirement. Failing open keeps a stuck lock from
    holding the container down.
    """
    acquired = False
    handle = None
    connection = None

    try:
        if engine.name == 'postgresql':
            connection = engine.connect()
            deadline = time.monotonic() + timeout
            while True:
                acquired = bool(connection.execute(
                    text('SELECT pg_try_advisory_lock(:key)'),
                    {'key': _PG_MIGRATION_LOCK_KEY},
                ).scalar())
                if acquired or time.monotonic() >= deadline:
                    break
                time.sleep(0.5)
            # Close the implicit transaction the SELECT opened. The advisory
            # lock is session-level, so it survives the commit; without this
            # the connection sits idle-in-transaction for the whole migration
            # run and a hardened server's timeout could kill it mid-hold.
            connection.commit()
        else:
            # One lock file per database URL so unrelated databases on the same
            # host do not serialise against each other.
            digest = hashlib.sha256(str(engine.url).encode()).hexdigest()[:16]
            lock_path = os.path.join(tempfile.gettempdir(), f'speakr_migration_{digest}.lock')
            # O_NOFOLLOW: the path is predictable and under a shared temp dir, so a
            # symlink planted there must not be followed and truncated. 0o600 keeps
            # other local users from pre-creating or reading it.
            handle = os.fdopen(os.open(lock_path, os.O_CREAT | os.O_RDWR | os.O_NOFOLLOW, 0o600), 'r+')
            deadline = time.monotonic() + timeout
            while True:
                try:
                    fcntl.flock(handle.fileno(), fcntl.LOCK_EX | fcntl.LOCK_NB)
                    acquired = True
                except BlockingIOError:
                    acquired = False
                if acquired or time.monotonic() >= deadline:
                    break
                time.sleep(0.5)

        if not acquired and logger:
            logger.warning(
                "Could not acquire the migration lock within %ss; proceeding anyway. "
                "Migrations are idempotent, so this is safe but may duplicate work.",
                timeout,
            )

        yield acquired

    finally:
        try:
            if connection is not None:
                if acquired:
                    connection.execute(
                        text('SELECT pg_advisory_unlock(:key)'),
                        {'key': _PG_MIGRATION_LOCK_KEY},
                    )
                connection.close()
            if handle is not None:
                if acquired:
                    fcntl.flock(handle.fileno(), fcntl.LOCK_UN)
                handle.close()
        except Exception:
            # Both lock types are released by the OS or the connection closing,
            # so a failure here must not mask whatever the caller was doing.
            pass


# Singleton locks are held for the process lifetime; keeping the file handle
# referenced here is what keeps them held.
_held_singleton_locks = {}


def acquire_singleton_lock(engine, name, logger=None):
    """Elect exactly one process on this host to own a named responsibility, for its lifetime.

    The counterpart of `migration_lock`: that serialises work every process must
    do, this picks the one process that should do it at all, such as running the
    job-queue workers. It does not wait (a loser returns False at once) and it
    never releases: the holder keeps the lock until the process exits, so
    leadership ends on process death, crash or SIGKILL included, and the
    replacement process wins the next election.

    A file lock is used for every backend: a session-level advisory lock lives
    on a database connection nobody watches, and when that connection dies the
    lock is gone while the owner still believes it holds it. Returns True when
    this process holds `name`; asking again returns the original answer. If the
    lock cannot be taken at all this process takes ownership anyway, failing
    open like `migration_lock`, so the worst case is the pre-election behaviour
    rather than an app that transcribes nothing.
    """
    if name in _held_singleton_locks:
        return _held_singleton_locks[name][0]

    # One lock file per (database, name) so unrelated databases elect independently
    digest = hashlib.sha256(f'{engine.url}|{name}'.encode()).hexdigest()[:16]
    lock_path = os.path.join(tempfile.gettempdir(), f'speakr_singleton_{digest}.lock')
    handle = None
    try:
        # O_NOFOLLOW: the path is predictable and under a shared temp dir, so a
        # symlink planted there must not be followed and truncated. 0o600 keeps
        # other local users from pre-creating or reading it.
        handle = os.fdopen(os.open(lock_path, os.O_CREAT | os.O_RDWR | os.O_NOFOLLOW, 0o600), 'r+')
        fcntl.flock(handle.fileno(), fcntl.LOCK_EX | fcntl.LOCK_NB)
        acquired = True
    except BlockingIOError:
        acquired = False
    except Exception as e:
        # Fail open: every process working (today's behaviour) beats none working
        if logger:
            logger.error("Could not run the '%s' election, taking ownership anyway: %s", name, e)
        acquired = True
    if not acquired and handle is not None:
        handle.close()
        handle = None

    # Keep the file handle referenced so GC cannot release the lock
    _held_singleton_locks[name] = (acquired, handle)

    if logger:
        logger.info("Singleton '%s': this process %s", name, "holds it" if acquired else "does not hold it")
    return acquired


def ensure_migration_ledger(engine):
    """Create the table recording which one-shot migrations have already run."""
    with engine.connect() as conn:
        conn.execute(text(
            'CREATE TABLE IF NOT EXISTS schema_migrations ('
            '  migration_id VARCHAR(255) NOT NULL PRIMARY KEY,'
            '  applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP'
            ')'
        ))
        conn.commit()


def run_once(engine, migration_id, migration, logger=None):
    """Run a one-shot migration the first time only, then never again.

    Most migrations here detect their own state, which works but means every
    startup pays to re-check, and the detection is itself a source of bugs when
    the schema drifts underneath it. A migration that cannot cheaply tell
    whether it has run, or that would otherwise scan a whole table on every
    boot, should be registered here instead.

    The callable is passed the engine and runs before the id is recorded, so a
    failure leaves the migration unrecorded and it is retried next startup.

    Returns True if the migration ran on this call, False if it had already run.
    """
    ensure_migration_ledger(engine)

    with engine.connect() as conn:
        already_applied = conn.execute(
            text('SELECT 1 FROM schema_migrations WHERE migration_id = :id'),
            {'id': migration_id},
        ).scalar()

    if already_applied:
        return False

    migration(engine)

    with engine.connect() as conn:
        conn.execute(
            text('INSERT INTO schema_migrations (migration_id) VALUES (:id)'),
            {'id': migration_id},
        )
        conn.commit()

    if logger:
        logger.info("Applied one-shot migration '%s'", migration_id)
    return True


def add_column_if_not_exists(engine, table_name, column_name, column_type):
    """
    Add a column to a table if it doesn't already exist.

    Args:
        engine: SQLAlchemy engine
        table_name: Name of the table
        column_name: Name of the column to add
        column_type: SQL type definition for the column

    Returns:
        bool: True if column was added, False if it already existed
    """
    inspector = inspect(engine)
    columns = [col['name'] for col in inspector.get_columns(table_name)]

    if column_name not in columns:
        if engine.name == 'postgresql':
            # PostgreSQL requires TRUE/FALSE for boolean defaults, not 0/1
            if 'BOOLEAN' in column_type.upper():
                column_type = column_type.replace('DEFAULT 0', 'DEFAULT FALSE')
                column_type = column_type.replace('DEFAULT 1', 'DEFAULT TRUE')

            # PostgreSQL uses TIMESTAMP, not DATETIME
            column_type = re.sub(r'\bDATETIME\b', 'TIMESTAMP', column_type, flags=re.IGNORECASE)

            # PostgreSQL uses BYTEA, not BLOB
            column_type = re.sub(r'\bBLOB\b', 'BYTEA', column_type, flags=re.IGNORECASE)

            # PostgreSQL interprets double-quoted strings as identifiers, not literals
            # Convert DEFAULT "value" to DEFAULT 'value'
            column_type = re.sub(r'''DEFAULT\s+"([^"]*)"''', r"DEFAULT '\1'", column_type, flags=re.IGNORECASE)

        with engine.connect() as conn:
            # Quote identifiers to handle reserved keywords (e.g., "user" in PostgreSQL)
            # MySQL uses backticks, PostgreSQL/SQLite use double quotes
            # Handle special case where column_type includes the column name
            if column_name in column_type:
                if engine.name == 'mysql':
                    conn.execute(text(f'ALTER TABLE `{table_name}` ADD COLUMN {column_type}'))
                else:
                    conn.execute(text(f'ALTER TABLE "{table_name}" ADD COLUMN {column_type}'))
            else:
                if engine.name == 'mysql':
                    conn.execute(text(f'ALTER TABLE `{table_name}` ADD COLUMN `{column_name}` {column_type}'))
                else:
                    conn.execute(text(f'ALTER TABLE "{table_name}" ADD COLUMN "{column_name}" {column_type}'))
            conn.commit()
        return True
    return False


def create_index_if_not_exists(engine, index_name, table_name, columns, unique=False):
    """
    Create an index on a table if it doesn't already exist.

    Handles cross-database compatibility by properly quoting table names,
    especially important for reserved keywords like 'user', 'order', etc.

    Args:
        engine: SQLAlchemy engine
        index_name: Name of the index to create
        table_name: Name of the table
        columns: Column(s) to index (string, can be comma-separated for composite)
        unique: Whether to create a unique index (default False)

    Returns:
        bool: True if index was created, False if it already existed or table doesn't exist
    """
    inspector = inspect(engine)

    # Check if table exists
    if table_name not in inspector.get_table_names():
        return False

    # Check if index already exists
    existing_indexes = [idx['name'] for idx in inspector.get_indexes(table_name)]
    if index_name in existing_indexes:
        return False

    unique_clause = 'UNIQUE ' if unique else ''

    with engine.connect() as conn:
        # Quote table name to handle reserved keywords (e.g., "user" in PostgreSQL)
        # MySQL uses backticks, PostgreSQL/SQLite use double quotes
        if engine.name == 'mysql':
            quoted_table = f'`{table_name}`'
        else:
            quoted_table = f'"{table_name}"'

        # Note: IF NOT EXISTS may not be supported on all databases, but we already
        # checked for existence above, so it's just a safety net
        try:
            conn.execute(text(
                f'CREATE {unique_clause}INDEX IF NOT EXISTS {index_name} ON {quoted_table} ({columns})'
            ))
        except Exception:
            # Some databases don't support IF NOT EXISTS, try without
            conn.execute(text(
                f'CREATE {unique_clause}INDEX {index_name} ON {quoted_table} ({columns})'
            ))
        conn.commit()
    return True


def drop_index_if_exists(engine, index_name, table_name):
    """Drop an index if it exists. Returns True if it was dropped."""
    inspector = inspect(engine)
    if table_name not in inspector.get_table_names():
        return False
    if index_name not in [idx['name'] for idx in inspector.get_indexes(table_name)]:
        return False
    with engine.connect() as conn:
        if engine.name == 'mysql':
            conn.execute(text(f'DROP INDEX {index_name} ON `{table_name}`'))
        else:
            conn.execute(text(f'DROP INDEX {index_name}'))
        conn.commit()
    return True


def drop_not_null(engine, table_name, column_name):
    """
    Make an existing NOT NULL column nullable.

    PostgreSQL and MySQL alter the constraint in place. SQLite cannot alter a
    column constraint at all, so it swaps in a fresh column: ADD COLUMN always
    produces a nullable column, so copying the values across and renaming
    reaches the same end state.

    The SQLite path deliberately does NOT rebuild the table. A rebuild has to
    restate the whole schema in hand-written DDL, which silently drops every
    column, index and constraint the DDL has fallen behind on; that is exactly
    how the previous version of this migration came to destroy 27 columns of
    user settings on any database it managed to run to completion (#379).

    The SQLite statements run inside one explicit transaction because pysqlite
    does not open one for DDL by itself. Without it a failure partway through
    leaves the half-built column committed and the migration permanently
    wedged, which is the other half of #379. BEGIN IMMEDIATE takes the write
    lock up front so the several worker processes that run migrations at
    startup cannot race, and the constraint is re-checked under that lock.

    Note: SQLite refuses to drop a column that is indexed, unique or part of
    the primary key, so this helper cannot be used on such a column.

    Args:
        engine: SQLAlchemy engine
        table_name: Name of the table
        column_name: Name of the column to make nullable

    Returns:
        bool: True if the column was made nullable, False if there was nothing
              to do (table or column missing, or already nullable).
    """
    inspector = inspect(engine)

    if table_name not in inspector.get_table_names():
        return False

    columns = {col['name']: col for col in inspector.get_columns(table_name)}
    temp_col = f"{column_name}__nullable_tmp"

    # Resume an attempt that died between DROP COLUMN and RENAME COLUMN: the
    # data is all in the temporary column, so completing the rename is safe.
    if engine.name == 'sqlite' and column_name not in columns and temp_col in columns:
        with engine.connect() as conn:
            conn.execute(text(
                f'ALTER TABLE "{table_name}" RENAME COLUMN "{temp_col}" TO "{column_name}"'
            ))
            conn.commit()
        return True

    column = columns.get(column_name)
    if column is None or column.get('nullable', True):
        return False

    column_type = column['type'].compile(engine.dialect)

    if engine.name == 'postgresql':
        with engine.connect() as conn:
            conn.execute(text(
                f'ALTER TABLE "{table_name}" ALTER COLUMN "{column_name}" DROP NOT NULL'
            ))
            conn.commit()
        return True

    if engine.name == 'mysql':
        with engine.connect() as conn:
            conn.execute(text(
                f'ALTER TABLE `{table_name}` MODIFY COLUMN `{column_name}` {column_type} NULL'
            ))
            conn.commit()
        return True

    if engine.name != 'sqlite':
        raise NotImplementedError(
            f"drop_not_null() does not support the '{engine.name}' dialect"
        )

    raw_connection = engine.raw_connection()
    try:
        dbapi_connection = raw_connection.driver_connection
        previous_isolation = dbapi_connection.isolation_level
        # Hand transaction control to us so that BEGIN covers the DDL too.
        dbapi_connection.isolation_level = None
        cursor = dbapi_connection.cursor()
        try:
            cursor.execute('BEGIN IMMEDIATE')

            # Re-check under the write lock; a concurrent worker may have
            # completed the migration between our inspection and this point.
            still_not_null = any(
                row[1] == column_name and row[3] == 1
                for row in cursor.execute(f'PRAGMA table_info("{table_name}")').fetchall()
            )
            if not still_not_null:
                cursor.execute('ROLLBACK')
                return False

            if temp_col in columns:
                cursor.execute(f'ALTER TABLE "{table_name}" DROP COLUMN "{temp_col}"')

            cursor.execute(f'ALTER TABLE "{table_name}" ADD COLUMN "{temp_col}" {column_type}')
            cursor.execute(f'UPDATE "{table_name}" SET "{temp_col}" = "{column_name}"')
            cursor.execute(f'ALTER TABLE "{table_name}" DROP COLUMN "{column_name}"')
            cursor.execute(
                f'ALTER TABLE "{table_name}" RENAME COLUMN "{temp_col}" TO "{column_name}"'
            )
            cursor.execute('COMMIT')
        except Exception:
            # If BEGIN itself failed there is no transaction to roll back, and
            # that secondary error must not mask the one we are re-raising.
            try:
                cursor.execute('ROLLBACK')
            except Exception:
                pass
            raise
        finally:
            cursor.close()
            dbapi_connection.isolation_level = previous_isolation
    finally:
        raw_connection.close()

    return True


def migrate_column_type(engine, table_name, column_name, new_type, transform_sql=None):
    """
    Migrate a column to a new type if it exists.

    For SQLite, this uses a temporary column approach since SQLite doesn't support ALTER COLUMN.

    Args:
        engine: SQLAlchemy engine
        table_name: Name of the table
        column_name: Name of the column to modify
        new_type: New SQL type for the column
        transform_sql: Optional SQL expression to transform existing data (e.g., "datetime(meeting_date || ' 12:00:00')")
                       If None, data is copied as-is

    Returns:
        bool: True if column was migrated, False if it didn't exist or migration wasn't needed
    """
    inspector = inspect(engine)

    # Check if table exists
    if table_name not in inspector.get_table_names():
        return False

    columns = {col['name']: col for col in inspector.get_columns(table_name)}

    if column_name not in columns:
        return False

    engine_name = engine.name

    with engine.connect() as conn:
        if engine_name == 'sqlite':
            # SQLite approach: use temporary column
            temp_col = f"{column_name}_new"

            # Check if temp column already exists (migration interrupted?)
            if temp_col in columns:
                try:
                    # Try to drop it and start over
                    conn.execute(text(f'ALTER TABLE "{table_name}" DROP COLUMN "{temp_col}"'))
                    conn.commit()
                except Exception:
                    # If we can't drop it, the migration may have partially completed
                    # Check if old column still exists
                    if column_name not in columns:
                        # Old column is gone, temp exists - just rename temp to complete migration
                        try:
                            conn.execute(text(f'ALTER TABLE "{table_name}" RENAME COLUMN "{temp_col}" TO "{column_name}"'))
                            conn.commit()
                            return True
                        except Exception as e:
                            # Can't complete, leave as-is
                            return False
                    # Both columns exist - abort to avoid data issues
                    return False

            # Add temporary column with new type
            conn.execute(text(f'ALTER TABLE "{table_name}" ADD COLUMN "{temp_col}" {new_type}'))

            # Copy data with optional transformation
            if transform_sql:
                conn.execute(text(f'UPDATE "{table_name}" SET "{temp_col}" = {transform_sql} WHERE "{column_name}" IS NOT NULL'))
            else:
                conn.execute(text(f'UPDATE "{table_name}" SET "{temp_col}" = "{column_name}"'))

            # Drop old column (SQLite 3.35.0+ only)
            try:
                conn.execute(text(f'ALTER TABLE "{table_name}" DROP COLUMN "{column_name}"'))
                # Drop succeeded, now rename temp to original name
                conn.execute(text(f'ALTER TABLE "{table_name}" RENAME COLUMN "{temp_col}" TO "{column_name}"'))
                conn.commit()
            except Exception:
                # Older SQLite - can't drop columns
                # Rename temp column to original name (this will fail if original still exists)
                try:
                    conn.execute(text(f'ALTER TABLE "{table_name}" RENAME COLUMN "{temp_col}" TO "{column_name}"'))
                    conn.commit()
                except Exception:
                    # Can't rename because old column exists - this is OK for SQLite
                    # Just keep the new column and let the app use the old one
                    # The data in the old column is still valid
                    conn.rollback()
                    # Actually, let's just commit the temp column addition
                    # The model will use column_name which still exists with old data
                    # This is safe - new records will use the new model definition
                    return False

        elif engine_name == 'postgresql':
            # PostgreSQL can alter column type directly
            if transform_sql:
                conn.execute(text(f'ALTER TABLE "{table_name}" ALTER COLUMN "{column_name}" TYPE {new_type} USING {transform_sql}'))
            else:
                conn.execute(text(f'ALTER TABLE "{table_name}" ALTER COLUMN "{column_name}" TYPE {new_type}'))
            conn.commit()

        elif engine_name == 'mysql':
            # MySQL can modify column type
            conn.execute(text(f'ALTER TABLE `{table_name}` MODIFY COLUMN `{column_name}` {new_type}'))

            # Apply transformation if provided
            if transform_sql:
                conn.execute(text(f'UPDATE `{table_name}` SET `{column_name}` = {transform_sql} WHERE `{column_name}` IS NOT NULL'))
            conn.commit()

        return True

    return False