Mercurial > libervia-backend
comparison sat/memory/migration/versions/129ac51807e4_create_virtual_table_for_full_text_.py @ 3663:162866ca4be7
db (schema): create virtual table for FTS + migration
rel 361
| author | Goffi <goffi@goffi.org> |
|---|---|
| date | Wed, 08 Sep 2021 17:58:48 +0200 |
| parents | |
| children |
comparison
equal
deleted
inserted
replaced
| 3661:cbb988a6f507 | 3663:162866ca4be7 |
|---|---|
| 1 """create virtual table for Full-Text Search | |
| 2 | |
| 3 Revision ID: 129ac51807e4 | |
| 4 Revises: 8974efc51d22 | |
| 5 Create Date: 2021-08-13 19:13:54.112538 | |
| 6 | |
| 7 """ | |
| 8 from alembic import op | |
| 9 import sqlalchemy as sa | |
| 10 | |
| 11 | |
| 12 # revision identifiers, used by Alembic. | |
| 13 revision = '129ac51807e4' | |
| 14 down_revision = '8974efc51d22' | |
| 15 branch_labels = None | |
| 16 depends_on = None | |
| 17 | |
| 18 | |
| 19 def upgrade(): | |
| 20 queries = [ | |
| 21 "CREATE VIRTUAL TABLE pubsub_items_fts " | |
| 22 "USING fts5(data, content=pubsub_items, content_rowid=id)", | |
| 23 "CREATE TRIGGER pubsub_items_fts_sync_ins AFTER INSERT ON pubsub_items BEGIN" | |
| 24 " INSERT INTO pubsub_items_fts(rowid, data) VALUES (new.id, new.data);" | |
| 25 "END", | |
| 26 "CREATE TRIGGER pubsub_items_fts_sync_del AFTER DELETE ON pubsub_items BEGIN" | |
| 27 " INSERT INTO pubsub_items_fts(pubsub_items_fts, rowid, data) " | |
| 28 "VALUES('delete', old.id, old.data);" | |
| 29 "END", | |
| 30 "CREATE TRIGGER pubsub_items_fts_sync_upd AFTER UPDATE ON pubsub_items BEGIN" | |
| 31 " INSERT INTO pubsub_items_fts(pubsub_items_fts, rowid, data) VALUES" | |
| 32 "('delete', old.id, old.data);" | |
| 33 " INSERT INTO pubsub_items_fts(rowid, data) VALUES(new.id, new.data);" | |
| 34 "END", | |
| 35 "INSERT INTO pubsub_items_fts(rowid, data) SELECT id, data from pubsub_items" | |
| 36 ] | |
| 37 for q in queries: | |
| 38 op.execute(sa.DDL(q)) | |
| 39 | |
| 40 | |
| 41 def downgrade(): | |
| 42 queries = [ | |
| 43 "DROP TRIGGER IF EXISTS pubsub_items_fts_sync_ins", | |
| 44 "DROP TRIGGER IF EXISTS pubsub_items_fts_sync_del", | |
| 45 "DROP TRIGGER IF EXISTS pubsub_items_fts_sync_upd", | |
| 46 "DROP TABLE IF EXISTS pubsub_items_fts", | |
| 47 ] | |
| 48 for q in queries: | |
| 49 op.execute(sa.DDL(q)) |