rt/wrolpi
1
0
Fork 0
mirror of https://github.com/lrnselfreliance/wrolpi.git synced 2026-10-26 19:01:39 -04:00
Code Issues Projects Releases Packages Wiki Activity Actions
wrolpi/alembic/versions/2026_07_10_1350_sqlite_baseline.py
Roland d9303a0ea8
Postgres -> SQLite hard cutover: the database moves into the media directory. 
The database now lives at <media>/config/wrolpi.db so a library (files,
configs, AND database) travels with the drive between WROLPi systems.

Core:
- wrolpi/db.py: lazy SQLite engine factory (WAL, busy_timeout=30s,
  foreign_keys=ON); get_db_curs yields a plain sqlite3.Cursor; native
  sqlite3 paramstyle everywhere (no compatibility shims); global sqlite3
  adapters keep raw-SQL datetime formats identical to the ORM's.
- wrolpi/fts.py: FTS5 external-content search replacing tsvector/GIN.
  Injection-proof websearch->FTS5 translator, bm25(10,4,2,1) reproducing
  A/B/C/D weighting, fast(abc)/deep column filtering, snippet headlines.
  Stemming contract pinned by dedicated tests (watch/watches/watched/
  watching are interchangeable).
- wrolpi/schema_ddl.py: SQLite triggers shared verbatim by the alembic
  baseline and test DBs; fixes the channel video_count DELETE/UPDATE
  bugs and a latent total_size bug (it used the video id as channel id).
- wrolpi/db_bootstrap.py: startup auto-create/migrate; refuses databases
  written by a newer WROLPi (db_version_mismatch); SQLite 3.40 floor;
  network-filesystem refusal; never creates/migrates/trusts a database
  beneath an unmounted production media directory (shadow-DB guard).
- Alembic: 85 Postgres-era migrations squashed into one baseline;
  revision ids are dates (2026_07_10_1350) so filenames self-sort and
  alembic_version says when a schema was created; render_as_batch=True.
- All raw Postgres SQL rewritten (ANY->json_each IN, array_agg @> ->
  HAVING COUNT(DISTINCT), unnest->executemany/UPDATE..FROM,
  generate_series histograms->strftime GROUP BY, DISTINCT ON->
  row_number(), writable CTEs->two statements, jsonb_*->json_*, explicit
  NULLS FIRST/LAST, LIMIT-before-OFFSET).  Simple statements converted
  to SQLAlchemy instead (flag upsert, download retry/summary/tag checks,
  channel-by-tag estimate, Directory delete).
- Config imports count skipped entries; background dumps are blocked
  after a lossy import so partial imports can never overwrite YAML
  configs (they are the sole source of truth for rebuilds now).
- Search tolerates explicit JSON nulls for limit/offset (Postgres
  treated OFFSET NULL as 0; SQLite errors).
- file_group.id/doc_section.id render as INTEGER PRIMARY KEY on SQLite
  (BIGINT is not a rowid alias; FTS5 content_rowid requires it).

Ops teardown: docker-compose db service, CI postgres sidecars,
initialize_api_db.sh, controller postgresql service, psycopg2-binary all
removed.  upgrade.sh stops/disables postgresql (data is NOT deleted;
`systemctl enable --now postgresql` restores it); systemd units no
longer Want postgresql.service.

Existing-user upgrade: first boot creates+migrates the DB, configs
import (tags recreate their FileGroups from disk), a refresh rebuilds
the library index.  QA'd on the 10.0.0.8 RPi: rebuild matched the
Postgres baseline exactly; survived an uncontrolled hard reboot with
clean integrity checks.

Accepted behavior changes: FTS5 has no stopwords; bm25 length
normalization shifts some rank orders; fast-path d_headline is
un-highlighted; all-negative queries match nothing.

Deferred: Debian/trixie package lists (incl. sqlite3 CLI), Cypress
re-baseline, VACUUM INTO snapshots, exFAT/NTFS warning, auto-kicked
rebuild refresh with progress UX.

Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
2026-07-12 14:09:37 -06:00

378 lines
20 KiB
Python
Raw Permalink Blame History

"""The SQLite baseline.
The entire Postgres-era migration history (85 revisions) was squashed into this baseline when
WROLPi moved its database into the media directory (SQLite). See the git history of
alembic/versions/ for the old chain.
Revision ID: 2026_07_10_1350
Revises:
Create Date: 2026-07-10 13:50:59.070646
"""
from alembic import op
import sqlalchemy as sa
import wrolpi.dates
import wrolpi.files.models
import wrolpi.media_path
# revision identifiers, used by Alembic.
revision = '2026_07_10_1350'
down_revision = None
branch_labels = None
depends_on = None
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table('directory',
sa.Column('path', wrolpi.media_path.MediaPathType(), nullable=False),
sa.Column('name', sa.String(), nullable=False),
sa.Column('idempotency', wrolpi.dates.TZDateTime(), nullable=True),
sa.PrimaryKeyConstraint('path')
)
with op.batch_alter_table('directory', schema=None) as batch_op:
batch_op.create_index('directory_name_idx', ['name'], unique=False)
op.create_table('file_group',
sa.Column('id', sa.BigInteger().with_variant(sa.Integer(), 'sqlite'), nullable=False),
sa.Column('directory', wrolpi.media_path.MediaPathType(), nullable=False),
sa.Column('author', sa.String(), nullable=True),
sa.Column('censored', sa.Boolean(), nullable=True),
sa.Column('data', wrolpi.files.models.FancyJSON(), nullable=True),
sa.Column('download_datetime', wrolpi.dates.TZDateTime(), nullable=True),
sa.Column('files', wrolpi.files.models.FancyJSON(), nullable=True),
sa.Column('idempotency', wrolpi.dates.TZDateTime(), nullable=True),
sa.Column('indexed', sa.Boolean(), nullable=True),
sa.Column('length', sa.BigInteger(), nullable=True),
sa.Column('mimetype', sa.String(), nullable=True),
sa.Column('model', sa.String(), nullable=True),
sa.Column('modification_datetime', wrolpi.dates.TZDateTime(), nullable=True),
sa.Column('primary_path', wrolpi.media_path.MediaPathType(), nullable=False),
sa.Column('published_datetime', wrolpi.dates.TZDateTime(), nullable=True),
sa.Column('published_modified_datetime', wrolpi.dates.TZDateTime(), nullable=True),
sa.Column('size', sa.BigInteger(), nullable=True),
sa.Column('suffix', sa.String(), nullable=True),
sa.Column('title', sa.String(), nullable=True),
sa.Column('url', sa.String(), nullable=True),
sa.Column('viewed', wrolpi.dates.TZDateTime(), nullable=True),
sa.Column('effective_datetime', wrolpi.dates.TZDateTime(), nullable=True),
sa.Column('a_text', sa.String(), nullable=True),
sa.Column('b_text', sa.String(), nullable=True),
sa.Column('c_text', sa.String(), nullable=True),
sa.Column('d_text', sa.String(), nullable=True),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('primary_path')
)
with op.batch_alter_table('file_group', schema=None) as batch_op:
batch_op.create_index('file_group_author_idx', ['author'], unique=False)
batch_op.create_index('file_group_directory_idx', ['directory'], unique=False)
batch_op.create_index('file_group_download_datetime_idx', ['download_datetime'], unique=False)
batch_op.create_index('file_group_effective_datetime_idx', ['effective_datetime'], unique=False)
batch_op.create_index('file_group_length_idx', ['length'], unique=False)
batch_op.create_index('file_group_mimetype_idx', ['mimetype'], unique=False)
batch_op.create_index('file_group_model_idx', ['model'], unique=False)
batch_op.create_index('file_group_modification_datetime_idx', ['modification_datetime'], unique=False)
batch_op.create_index('file_group_published_datetime_idx', ['published_datetime'], unique=False)
batch_op.create_index('file_group_published_modified_datetime_idx', ['published_modified_datetime'], unique=False)
batch_op.create_index('file_group_size_ix', ['size'], unique=False)
batch_op.create_index('file_group_url_idx', ['url'], unique=False)
batch_op.create_index('file_group_viewed_idx', ['viewed'], unique=False)
batch_op.create_index(batch_op.f('ix_file_group_suffix'), ['suffix'], unique=False)
op.create_table('tag',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(), nullable=False),
sa.Column('color', sa.String(), nullable=True),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('name')
)
op.create_table('wrolpi_flag',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('refresh_complete', sa.Boolean(), nullable=True),
sa.Column('outdated_zims', sa.Boolean(), nullable=True),
sa.PrimaryKeyConstraint('id')
)
op.create_table('collection',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(), nullable=False),
sa.Column('description', sa.Text(), nullable=True),
sa.Column('kind', sa.String(), server_default='channel', nullable=False),
sa.Column('directory', wrolpi.media_path.MediaPathType(), nullable=True),
sa.Column('tag_id', sa.Integer(), nullable=True),
sa.Column('file_format', sa.String(), nullable=True),
sa.Column('created_date', sa.DateTime(), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False),
sa.Column('item_count', sa.Integer(), nullable=False),
sa.Column('total_size', sa.BigInteger(), nullable=False),
sa.ForeignKeyConstraint(['tag_id'], ['tag.id'], ),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('directory', name='uq_collection_directory'),
sa.UniqueConstraint('name', 'kind', name='uq_collection_name_kind')
)
with op.batch_alter_table('collection', schema=None) as batch_op:
batch_op.create_index('idx_collection_item_count', ['item_count'], unique=False)
batch_op.create_index('idx_collection_kind', ['kind'], unique=False)
batch_op.create_index('idx_collection_total_size', ['total_size'], unique=False)
op.create_table('doc',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('size', sa.BigInteger(), nullable=True),
sa.Column('publisher', sa.String(), nullable=True),
sa.Column('language', sa.String(), nullable=True),
sa.Column('page_count', sa.Integer(), nullable=True),
sa.Column('subject', sa.String(), nullable=True),
sa.Column('description', sa.Text(), nullable=True),
sa.Column('file_group_id', sa.BigInteger(), nullable=False),
sa.ForeignKeyConstraint(['file_group_id'], ['file_group.id'], ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('file_group_id')
)
with op.batch_alter_table('doc', schema=None) as batch_op:
batch_op.create_index('doc_size_idx', ['size'], unique=False)
op.create_table('tag_file',
sa.Column('created_at', wrolpi.dates.TZDateTime(), nullable=True),
sa.Column('tag_id', sa.Integer(), nullable=False),
sa.Column('file_group_id', sa.BigInteger(), nullable=False),
sa.ForeignKeyConstraint(['file_group_id'], ['file_group.id'], ondelete='CASCADE'),
sa.ForeignKeyConstraint(['tag_id'], ['tag.id'], ),
sa.PrimaryKeyConstraint('tag_id', 'file_group_id'),
sa.UniqueConstraint('tag_id', 'file_group_id', name='tag_file_tag_id_file_group_id_key')
)
op.create_table('zim',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('path', wrolpi.media_path.MediaPathType(), nullable=False),
sa.Column('file_group_id', sa.BigInteger(), nullable=False),
sa.Column('auto_search', sa.Boolean(), nullable=True),
sa.ForeignKeyConstraint(['file_group_id'], ['file_group.id'], ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('path', name='zim_path_key')
)
op.create_table('archive',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('collection_id', sa.Integer(), nullable=True),
sa.Column('file_group_id', sa.BigInteger(), nullable=False),
sa.ForeignKeyConstraint(['collection_id'], ['collection.id'], ondelete='CASCADE'),
sa.ForeignKeyConstraint(['file_group_id'], ['file_group.id'], ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('file_group_id')
)
with op.batch_alter_table('archive', schema=None) as batch_op:
batch_op.create_index('idx_archive_collection_id', ['collection_id'], unique=False)
op.create_table('channel',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('url', sa.String(), nullable=True),
sa.Column('generate_posters', sa.Boolean(), nullable=True),
sa.Column('calculate_duration', sa.Boolean(), nullable=True),
sa.Column('download_missing_data', sa.Boolean(), nullable=True),
sa.Column('source_id', sa.String(), nullable=True),
sa.Column('refreshed', sa.Boolean(), nullable=True),
sa.Column('video_count', sa.Integer(), nullable=False),
sa.Column('total_size', sa.BigInteger(), nullable=False),
sa.Column('minimum_frequency', sa.Integer(), nullable=True),
sa.Column('info_json', sa.JSON(), nullable=True),
sa.Column('info_date', sa.Date(), nullable=True),
sa.Column('collection_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(['collection_id'], ['collection.id'], ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id')
)
with op.batch_alter_table('channel', schema=None) as batch_op:
batch_op.create_index('channel_minimum_frequency_idx', ['minimum_frequency'], unique=False)
batch_op.create_index('channel_source_id', ['source_id'], unique=False)
batch_op.create_index('channel_total_size_idx', ['total_size'], unique=False)
batch_op.create_index('channel_url_key', ['url'], unique=True, postgresql_where=sa.text('url IS NOT NULL'), sqlite_where=sa.text('url IS NOT NULL'))
batch_op.create_index('channel_video_count_idx', ['video_count'], unique=False)
op.create_table('collection_item',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('collection_id', sa.Integer(), nullable=False),
sa.Column('item_kind', sa.String(), server_default='file', nullable=False),
sa.Column('file_group_id', sa.Integer(), nullable=True),
sa.Column('zim_id', sa.Integer(), nullable=True),
sa.Column('zim_entry', sa.Text(), nullable=True),
sa.Column('url', sa.Text(), nullable=True),
sa.Column('title', sa.Text(), nullable=True),
sa.Column('position', sa.Integer(), nullable=False),
sa.Column('added_date', sa.DateTime(), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False),
sa.CheckConstraint("(item_kind = 'file' AND file_group_id IS NOT NULL AND zim_id IS NULL AND url IS NULL) OR (item_kind = 'zim' AND zim_id IS NOT NULL AND zim_entry IS NOT NULL AND file_group_id IS NULL AND url IS NULL) OR (item_kind = 'url' AND url IS NOT NULL AND file_group_id IS NULL AND zim_id IS NULL)", name='ck_collection_item_kind'),
sa.ForeignKeyConstraint(['collection_id'], ['collection.id'], ondelete='CASCADE'),
sa.ForeignKeyConstraint(['file_group_id'], ['file_group.id'], ondelete='CASCADE'),
sa.ForeignKeyConstraint(['zim_id'], ['zim.id'], ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('collection_id', 'file_group_id', name='uq_collection_file_group'),
sa.UniqueConstraint('collection_id', 'url', name='uq_collection_url'),
sa.UniqueConstraint('collection_id', 'zim_id', 'zim_entry', name='uq_collection_zim_entry')
)
with op.batch_alter_table('collection_item', schema=None) as batch_op:
batch_op.create_index('idx_collection_item_collection_id', ['collection_id'], unique=False)
batch_op.create_index('idx_collection_item_collection_position', ['collection_id', 'position'], unique=False)
batch_op.create_index('idx_collection_item_file_group_id', ['file_group_id'], unique=False)
batch_op.create_index('idx_collection_item_position', ['position'], unique=False)
op.create_table('doc_section',
sa.Column('id', sa.BigInteger().with_variant(sa.Integer(), 'sqlite'), nullable=False),
sa.Column('doc_id', sa.Integer(), nullable=False),
sa.Column('kind', sa.String(), nullable=False),
sa.Column('ordinal', sa.Integer(), nullable=False),
sa.Column('label', sa.String(), nullable=True),
sa.Column('content', sa.Text(), nullable=True),
sa.ForeignKeyConstraint(['doc_id'], ['doc.id'], ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id')
)
with op.batch_alter_table('doc_section', schema=None) as batch_op:
batch_op.create_index('doc_section_doc_id_idx', ['doc_id', 'ordinal'], unique=False)
op.create_table('download',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('url', sa.String(), nullable=False),
sa.Column('attempts', sa.Integer(), nullable=True),
sa.Column('destination', wrolpi.media_path.MediaPathType(), nullable=True),
sa.Column('downloader', sa.Text(), nullable=True),
sa.Column('sub_downloader', sa.Text(), nullable=True),
sa.Column('error', sa.Text(), nullable=True),
sa.Column('frequency', sa.Integer(), nullable=True),
sa.Column('info_json', sa.JSON(), nullable=True),
sa.Column('last_download_attempt', wrolpi.dates.TZDateTime(), nullable=True),
sa.Column('last_successful_download', wrolpi.dates.TZDateTime(), nullable=True),
sa.Column('location', sa.Text(), nullable=True),
sa.Column('next_download', wrolpi.dates.TZDateTime(), nullable=True),
sa.Column('settings', sa.JSON(), nullable=True),
sa.Column('status', sa.String(), nullable=True),
sa.Column('tag_names', sa.JSON(), nullable=True),
sa.Column('collection_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(['collection_id'], ['collection.id'], ondelete='SET NULL'),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('url')
)
with op.batch_alter_table('download', schema=None) as batch_op:
batch_op.create_index('idx_download_collection_id', ['collection_id'], unique=False)
batch_op.create_index('idx_download_last_download_attempt', ['last_download_attempt'], unique=False)
op.create_table('tag_zim',
sa.Column('tag_id', sa.Integer(), nullable=False),
sa.Column('zim_id', sa.Integer(), nullable=False),
sa.Column('zim_entry', sa.Text(), nullable=False),
sa.Column('created_at', wrolpi.dates.TZDateTime(), nullable=True),
sa.ForeignKeyConstraint(['tag_id'], ['tag.id'], ),
sa.ForeignKeyConstraint(['zim_id'], ['zim.id'], ondelete='CASCADE'),
sa.PrimaryKeyConstraint('tag_id', 'zim_id', 'zim_entry'),
sa.UniqueConstraint('tag_id', 'zim_id', 'zim_entry', name='tag_zim_tag_id_zim_id_zim_entry_key')
)
op.create_table('video',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('source_id', sa.String(), nullable=True),
sa.Column('view_count', sa.BigInteger(), nullable=True),
sa.Column('ffprobe_json', sa.JSON(), nullable=True),
sa.Column('have_comments', sa.Boolean(), nullable=True),
sa.Column('comments_failed', sa.Boolean(), nullable=True),
sa.Column('channel_id', sa.Integer(), nullable=True),
sa.Column('file_group_id', sa.BigInteger(), nullable=False),
sa.ForeignKeyConstraint(['channel_id'], ['channel.id'], ),
sa.ForeignKeyConstraint(['file_group_id'], ['file_group.id'], ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('file_group_id'),
sqlite_autoincrement=True
)
with op.batch_alter_table('video', schema=None) as batch_op:
batch_op.create_index('video_channel_id_idx', ['channel_id'], unique=False)
batch_op.create_index('video_source_id_idx', ['source_id'], unique=False)
batch_op.create_index('video_view_count_idx', ['view_count'], unique=False)
op.create_table('zim_subscription',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.Text(), nullable=False),
sa.Column('language', sa.Text(), nullable=False),
sa.Column('download_id', sa.Integer(), nullable=False),
sa.ForeignKeyConstraint(['download_id'], ['download.id'], ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('name')
)
# ### end Alembic commands ###
# Triggers (channel summaries, effective_datetime) and the FTS5 full-text-search tables.
# Shared with the test databases (wrolpi/conftest.py) so tests exercise the same schema.
from wrolpi.schema_ddl import install_raw_ddl
from wrolpi.fts import rebuild_fts
connection = op.get_bind()
install_raw_ddl(connection)
# Index any existing content (no-op on a fresh database).
rebuild_fts(connection)
def downgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.drop_table('zim_subscription')
with op.batch_alter_table('video', schema=None) as batch_op:
batch_op.drop_index('video_view_count_idx')
batch_op.drop_index('video_source_id_idx')
batch_op.drop_index('video_channel_id_idx')
op.drop_table('video')
op.drop_table('tag_zim')
with op.batch_alter_table('download', schema=None) as batch_op:
batch_op.drop_index('idx_download_last_download_attempt')
batch_op.drop_index('idx_download_collection_id')
op.drop_table('download')
with op.batch_alter_table('doc_section', schema=None) as batch_op:
batch_op.drop_index('doc_section_doc_id_idx')
op.drop_table('doc_section')
with op.batch_alter_table('collection_item', schema=None) as batch_op:
batch_op.drop_index('idx_collection_item_position')
batch_op.drop_index('idx_collection_item_file_group_id')
batch_op.drop_index('idx_collection_item_collection_position')
batch_op.drop_index('idx_collection_item_collection_id')
op.drop_table('collection_item')
with op.batch_alter_table('channel', schema=None) as batch_op:
batch_op.drop_index('channel_video_count_idx')
batch_op.drop_index('channel_url_key', postgresql_where=sa.text('url IS NOT NULL'), sqlite_where=sa.text('url IS NOT NULL'))
batch_op.drop_index('channel_total_size_idx')
batch_op.drop_index('channel_source_id')
batch_op.drop_index('channel_minimum_frequency_idx')
op.drop_table('channel')
with op.batch_alter_table('archive', schema=None) as batch_op:
batch_op.drop_index('idx_archive_collection_id')
op.drop_table('archive')
op.drop_table('zim')
op.drop_table('tag_file')
with op.batch_alter_table('doc', schema=None) as batch_op:
batch_op.drop_index('doc_size_idx')
op.drop_table('doc')
with op.batch_alter_table('collection', schema=None) as batch_op:
batch_op.drop_index('idx_collection_total_size')
batch_op.drop_index('idx_collection_kind')
batch_op.drop_index('idx_collection_item_count')
op.drop_table('collection')
op.drop_table('wrolpi_flag')
op.drop_table('tag')
with op.batch_alter_table('file_group', schema=None) as batch_op:
batch_op.drop_index(batch_op.f('ix_file_group_suffix'))
batch_op.drop_index('file_group_viewed_idx')
batch_op.drop_index('file_group_url_idx')
batch_op.drop_index('file_group_size_ix')
batch_op.drop_index('file_group_published_modified_datetime_idx')
batch_op.drop_index('file_group_published_datetime_idx')
batch_op.drop_index('file_group_modification_datetime_idx')
batch_op.drop_index('file_group_model_idx')
batch_op.drop_index('file_group_mimetype_idx')
batch_op.drop_index('file_group_length_idx')
batch_op.drop_index('file_group_effective_datetime_idx')
batch_op.drop_index('file_group_download_datetime_idx')
batch_op.drop_index('file_group_directory_idx')
batch_op.drop_index('file_group_author_idx')
op.drop_table('file_group')
with op.batch_alter_table('directory', schema=None) as batch_op:
batch_op.drop_index('directory_name_idx')
op.drop_table('directory')
# ### end Alembic commands ###
Reference in a new issue View git blame Copy permalink