Mercurial > libervia-backend
diff libervia/backend/memory/migration/versions/129ac51807e4_create_virtual_table_for_full_text_.py @ 4071:4b842c1fb686
refactoring: renamed `sat` package to `libervia.backend`
author | Goffi <goffi@goffi.org> |
---|---|
date | Fri, 02 Jun 2023 11:49:51 +0200 |
parents | sat/memory/migration/versions/129ac51807e4_create_virtual_table_for_full_text_.py@162866ca4be7 |
children | 0d7bb4df2343 |
line wrap: on
line diff
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/libervia/backend/memory/migration/versions/129ac51807e4_create_virtual_table_for_full_text_.py Fri Jun 02 11:49:51 2023 +0200 @@ -0,0 +1,49 @@ +"""create virtual table for Full-Text Search + +Revision ID: 129ac51807e4 +Revises: 8974efc51d22 +Create Date: 2021-08-13 19:13:54.112538 + +""" +from alembic import op +import sqlalchemy as sa + + +# revision identifiers, used by Alembic. +revision = '129ac51807e4' +down_revision = '8974efc51d22' +branch_labels = None +depends_on = None + + +def upgrade(): + queries = [ + "CREATE VIRTUAL TABLE pubsub_items_fts " + "USING fts5(data, content=pubsub_items, content_rowid=id)", + "CREATE TRIGGER pubsub_items_fts_sync_ins AFTER INSERT ON pubsub_items BEGIN" + " INSERT INTO pubsub_items_fts(rowid, data) VALUES (new.id, new.data);" + "END", + "CREATE TRIGGER pubsub_items_fts_sync_del AFTER DELETE ON pubsub_items BEGIN" + " INSERT INTO pubsub_items_fts(pubsub_items_fts, rowid, data) " + "VALUES('delete', old.id, old.data);" + "END", + "CREATE TRIGGER pubsub_items_fts_sync_upd AFTER UPDATE ON pubsub_items BEGIN" + " INSERT INTO pubsub_items_fts(pubsub_items_fts, rowid, data) VALUES" + "('delete', old.id, old.data);" + " INSERT INTO pubsub_items_fts(rowid, data) VALUES(new.id, new.data);" + "END", + "INSERT INTO pubsub_items_fts(rowid, data) SELECT id, data from pubsub_items" + ] + for q in queries: + op.execute(sa.DDL(q)) + + +def downgrade(): + queries = [ + "DROP TRIGGER IF EXISTS pubsub_items_fts_sync_ins", + "DROP TRIGGER IF EXISTS pubsub_items_fts_sync_del", + "DROP TRIGGER IF EXISTS pubsub_items_fts_sync_upd", + "DROP TABLE IF EXISTS pubsub_items_fts", + ] + for q in queries: + op.execute(sa.DDL(q))