Mercurial > libervia-backend

annotate libervia/backend/memory/migration/versions/129ac51807e4_create_virtual_table_for_full_text_.py @ 4270:0d7bb4df2343

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Reformatted code base using black.
author Goffi <goffi@goffi.org>
date Wed, 19 Jun 2024 18:44:57 +0200
parents 4b842c1fb686
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 """
4270
0d7bb4df2343 Reformatted code base using black.
Goffi <goffi@goffi.org>
parents: 4071
diff changeset
8
3663
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
9 from alembic import op
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
10 import sqlalchemy as sa
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
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
13 # revision identifiers, used by Alembic.
4270
0d7bb4df2343 Reformatted code base using black.
Goffi <goffi@goffi.org>
parents: 4071
diff changeset
14 revision = "129ac51807e4"
0d7bb4df2343 Reformatted code base using black.
Goffi <goffi@goffi.org>
parents: 4071
diff changeset
15 down_revision = "8974efc51d22"
3663
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
16 branch_labels = None
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
17 depends_on = None
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
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
20 def upgrade():
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
21 queries = [
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
22 "CREATE VIRTUAL TABLE pubsub_items_fts "
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
23 "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
24 "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
25 " 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
26 "END",
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
27 "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
28 " 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
29 "VALUES('delete', old.id, old.data);"
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
30 "END",
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
31 "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
32 " 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
33 "('delete', old.id, old.data);"
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
34 " 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
35 "END",
4270
0d7bb4df2343 Reformatted code base using black.
Goffi <goffi@goffi.org>
parents: 4071
diff changeset
36 "INSERT INTO pubsub_items_fts(rowid, data) SELECT id, data from pubsub_items",
3663
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
37 ]
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
38 for q in queries:
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
39 op.execute(sa.DDL(q))
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
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
42 def downgrade():
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
43 queries = [
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_ins",
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_del",
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
46 "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
47 "DROP TABLE IF EXISTS pubsub_items_fts",
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
48 ]
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
49 for q in queries:
162866ca4be7 db (schema): create virtual table for FTS + migration
Goffi <goffi@goffi.org>
parents:
diff changeset
50 op.execute(sa.DDL(q))