Mercurial > libervia-backend

annotate libervia/backend/memory/migration/versions/129ac51807e4_create_virtual_table_for_full_text_.py @ 4242:8acf46ed7f36

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
frontends: remote control implementation: This is the frontends common part of remote control implementation. It handle the creation of WebRTC session, and management of inputs. For now the reception use freedesktop.org Desktop portal, and works mostly with Wayland based Desktop Environments. rel 436
author Goffi <goffi@goffi.org>
date Sat, 11 May 2024 13:52:43 +0200
parents 4b842c1fb686
children 0d7bb4df2343
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))