Mercurial > libervia-backend

annotate libervia/backend/memory/migration/versions/129ac51807e4_create_virtual_table_for_full_text_.py @ 4071:4b842c1fb686

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
3663
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
1 """create virtual table for Full-Text Search
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
2
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
3 Revision ID: 129ac51807e4
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
4 Revises: 8974efc51d22
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
5 Create Date: 2021-08-13 19:13:54.112538
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
6
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
7 """
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
8 from alembic import op
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
9 import sqlalchemy as sa
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
10
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
11
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
12 # revision identifiers, used by Alembic.
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
13 revision = '129ac51807e4'
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
14 down_revision = '8974efc51d22'
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
15 branch_labels = None
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
16 depends_on = None
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
17
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
18
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
19 def upgrade():
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
20 queries = [
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
21 "CREATE VIRTUAL TABLE pubsub_items_fts "
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
22 "USING fts5(data, content=pubsub_items, content_rowid=id)",
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
23 "CREATE TRIGGER pubsub_items_fts_sync_ins AFTER INSERT ON pubsub_items BEGIN"
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
24 " INSERT INTO pubsub_items_fts(rowid, data) VALUES (new.id, new.data);"
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
25 "END",
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
26 "CREATE TRIGGER pubsub_items_fts_sync_del AFTER DELETE ON pubsub_items BEGIN"
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
27 " INSERT INTO pubsub_items_fts(pubsub_items_fts, rowid, data) "
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
28 "VALUES('delete', old.id, old.data);"
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
29 "END",
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
30 "CREATE TRIGGER pubsub_items_fts_sync_upd AFTER UPDATE ON pubsub_items BEGIN"
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
31 " INSERT INTO pubsub_items_fts(pubsub_items_fts, rowid, data) VALUES"
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
32 "('delete', old.id, old.data);"
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
33 " INSERT INTO pubsub_items_fts(rowid, data) VALUES(new.id, new.data);"
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
34 "END",
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
35 "INSERT INTO pubsub_items_fts(rowid, data) SELECT id, data from pubsub_items"
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
36 ]
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
37 for q in queries:
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
38 op.execute(sa.DDL(q))
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
39
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
40
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
41 def downgrade():
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
42 queries = [
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
43 "DROP TRIGGER IF EXISTS pubsub_items_fts_sync_ins",
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
44 "DROP TRIGGER IF EXISTS pubsub_items_fts_sync_del",
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
45 "DROP TRIGGER IF EXISTS pubsub_items_fts_sync_upd",
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
46 "DROP TABLE IF EXISTS pubsub_items_fts",
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
47 ]
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
48 for q in queries:
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
49 op.execute(sa.DDL(q))