Mercurial > libervia-backend
annotate libervia/backend/memory/migration/versions/129ac51807e4_create_virtual_table_for_full_text_.py @ 4130:02f0adc745c6
core: notifications implementation, first draft:
add a new table for notifications, and methods/bridge methods to manipulate them.
author | Goffi <goffi@goffi.org> |
---|---|
date | Mon, 16 Oct 2023 17:29:31 +0200 |
parents | 4b842c1fb686 |
children | 0d7bb4df2343 |
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)) |