forked from forks/microblog.pub
115 lines
3.9 KiB
Python
115 lines
3.9 KiB
Python
"""Outbox FTS
|
|
|
|
Revision ID: 368f511ad954
|
|
Revises: b28c0551c236
|
|
Create Date: 2022-11-02 19:14:37.865923+00:00
|
|
|
|
"""
|
|
from sqlalchemy import insert
|
|
from sqlalchemy import select
|
|
from sqlalchemy.orm.session import Session
|
|
|
|
from alembic import op
|
|
|
|
# revision identifiers, used by Alembic.
|
|
revision = '368f511ad954'
|
|
down_revision = 'b28c0551c236'
|
|
branch_labels = None
|
|
depends_on = None
|
|
|
|
|
|
def upgrade() -> None:
|
|
# ### commands auto generated by Alembic - please adjust! ###
|
|
# ### end Alembic commands ###
|
|
op.execute(
|
|
"CREATE VIRTUAL TABLE outbox_fts USING "
|
|
"fts5(summary, name, source, content='');"
|
|
)
|
|
op.execute(
|
|
"CREATE TRIGGER outbox_fts_ai AFTER "
|
|
"INSERT ON outbox WHEN new.ap_type in ('Article', 'Note', 'Question') BEGIN"
|
|
" INSERT INTO outbox_fts (rowid, source, name, summary)"
|
|
" VALUES ("
|
|
" new.id, "
|
|
" new.source, "
|
|
' json_extract(new.ap_object, "$.name"), '
|
|
' json_extract(new.ap_object, "$.summary")'
|
|
" ); "
|
|
"END;"
|
|
)
|
|
op.execute(
|
|
"CREATE TRIGGER outbox_fts_ad AFTER "
|
|
"DELETE ON outbox WHEN old.ap_type in ('Article', 'Note', 'Question') BEGIN"
|
|
" INSERT INTO outbox_fts (outbox_fts, rowid, source, name, summary)"
|
|
" VALUES ("
|
|
" 'delete', "
|
|
" old.id, "
|
|
" old.source, "
|
|
' json_extract(old.ap_object, "$.name"), '
|
|
' json_extract(old.ap_object, "$.summary")'
|
|
" ); "
|
|
"END;"
|
|
)
|
|
op.execute(
|
|
"CREATE TRIGGER outbox_fts_au_softdelete AFTER "
|
|
"UPDATE ON outbox WHEN new.is_deleted = 1 AND "
|
|
"new.ap_type in ('Article', 'Note', 'Question') BEGIN"
|
|
" INSERT INTO outbox_fts (outbox_fts, rowid, source, name, summary)"
|
|
" VALUES ("
|
|
" 'delete', "
|
|
" old.id, "
|
|
" old.source, "
|
|
' json_extract(old.ap_object, "$.name"), '
|
|
' json_extract(old.ap_object, "$.summary")'
|
|
" ); "
|
|
"END; "
|
|
)
|
|
op.execute(
|
|
"CREATE TRIGGER outbox_fts_au AFTER "
|
|
"UPDATE ON outbox "
|
|
"WHEN (new.source <> old.source OR new.ap_object <> old.ap_object) AND "
|
|
"new.ap_type in ('Note', 'Article', 'Quesion') BEGIN"
|
|
" INSERT INTO outbox_fts (outbox_fts, rowid, source, name, summary)"
|
|
" VALUES ("
|
|
" 'delete', "
|
|
" old.id, "
|
|
" old.source, "
|
|
' json_extract(old.ap_object, "$.name"), '
|
|
' json_extract(old.ap_object, "$.summary")'
|
|
" );"
|
|
" INSERT INTO outbox_fts (rowid, source, name, summary)"
|
|
" VALUES ("
|
|
" new.id, "
|
|
" new.source, "
|
|
' json_extract(new.ap_object, "$.name"), '
|
|
' json_extract(new.ap_object, "$.summary")'
|
|
" );"
|
|
"END;"
|
|
)
|
|
from app.models import OutboxObject
|
|
from app.models import outbox_fts
|
|
sess = Session(op.get_bind())
|
|
|
|
# Backfill the index
|
|
outbox_objects = sess.execute(select(OutboxObject).where(
|
|
OutboxObject.ap_type.in_(["Article", "Note", "Question"]))
|
|
).scalars()
|
|
for outbox_object in outbox_objects:
|
|
row = {"source": outbox_object.source, "rowid": outbox_object.id}
|
|
if name := outbox_object.ap_object.get("name"):
|
|
row["name"] = name
|
|
if summary := outbox_object.ap_object.get("summary"):
|
|
row["summary"] = summary
|
|
sess.execute(insert(outbox_fts).values(row))
|
|
|
|
sess.commit()
|
|
|
|
|
|
def downgrade() -> None:
|
|
# ### commands auto generated by Alembic - please adjust! ###
|
|
# ### end Alembic commands ###
|
|
op.drop_table('outbox_fts')
|
|
op.execute("DROP TRIGGER outbox_fts_ai;")
|
|
op.execute("DROP TRIGGER outbox_fts_ad;")
|
|
op.execute("DROP TRIGGER outbox_fts_au_softdelete;")
|
|
op.execute("DROP TRIGGER outbox_fts_au;")
|