# HG changeset patch
# User Goffi <goffi@goffi.org>
# Date 1631116728 -7200
# Node ID 162866ca4be73af7584bbed642d996f301d1000c
# Parent  cbb988a6f507a15fa362c8bb5e9bed589bd614db
db (schema): create virtual table for FTS + migration

rel 361

diff -r cbb988a6f507 -r 162866ca4be7 sat/memory/migration/versions/129ac51807e4_create_virtual_table_for_full_text_.py
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/sat/memory/migration/versions/129ac51807e4_create_virtual_table_for_full_text_.py	Wed Sep 08 17:58:48 2021 +0200
@@ -0,0 +1,49 @@
+"""create virtual table for Full-Text Search
+
+Revision ID: 129ac51807e4
+Revises: 8974efc51d22
+Create Date: 2021-08-13 19:13:54.112538
+
+"""
+from alembic import op
+import sqlalchemy as sa
+
+
+# revision identifiers, used by Alembic.
+revision = '129ac51807e4'
+down_revision = '8974efc51d22'
+branch_labels = None
+depends_on = None
+
+
+def upgrade():
+    queries = [
+        "CREATE VIRTUAL TABLE pubsub_items_fts "
+        "USING fts5(data, content=pubsub_items, content_rowid=id)",
+        "CREATE TRIGGER pubsub_items_fts_sync_ins AFTER INSERT ON pubsub_items BEGIN"
+        "  INSERT INTO pubsub_items_fts(rowid, data) VALUES (new.id, new.data);"
+        "END",
+        "CREATE TRIGGER pubsub_items_fts_sync_del AFTER DELETE ON pubsub_items BEGIN"
+        "  INSERT INTO pubsub_items_fts(pubsub_items_fts, rowid, data) "
+        "VALUES('delete', old.id, old.data);"
+        "END",
+        "CREATE TRIGGER pubsub_items_fts_sync_upd AFTER UPDATE ON pubsub_items BEGIN"
+        "  INSERT INTO pubsub_items_fts(pubsub_items_fts, rowid, data) VALUES"
+        "('delete', old.id, old.data);"
+        "  INSERT INTO pubsub_items_fts(rowid, data) VALUES(new.id, new.data);"
+        "END",
+        "INSERT INTO pubsub_items_fts(rowid, data) SELECT id, data from pubsub_items"
+    ]
+    for q in queries:
+        op.execute(sa.DDL(q))
+
+
+def downgrade():
+    queries = [
+        "DROP TRIGGER IF EXISTS pubsub_items_fts_sync_ins",
+        "DROP TRIGGER IF EXISTS pubsub_items_fts_sync_del",
+        "DROP TRIGGER IF EXISTS pubsub_items_fts_sync_upd",
+        "DROP TABLE IF EXISTS pubsub_items_fts",
+    ]
+    for q in queries:
+        op.execute(sa.DDL(q))
diff -r cbb988a6f507 -r 162866ca4be7 sat/memory/sqla_mapping.py
--- a/sat/memory/sqla_mapping.py	Wed Sep 08 11:20:37 2021 +0200
+++ b/sat/memory/sqla_mapping.py	Wed Sep 08 17:58:48 2021 +0200
@@ -23,7 +23,7 @@
 import enum
 from sqlalchemy import (
     MetaData, Column, Integer, Text, Float, Boolean, DateTime, Enum, JSON, ForeignKey,
-    UniqueConstraint, Index
+    UniqueConstraint, Index, DDL, event
 )
 
 from sqlalchemy.orm import declarative_base, relationship
@@ -56,7 +56,7 @@
     COMPLETED = 2
     #: something wrong happened during synchronisation, won't sync
     ERROR = 3
-    #: synchronisation won't be done even if a syncing analyser match
+    #: synchronisation won't be done even if a syncing analyser matches
     NO_SYNC = 4
 
 
@@ -529,3 +529,48 @@
     parsed = Column(JSON)
 
     node = relationship("PubsubNode", back_populates="items")
+
+
+## Full-Text Search
+
+# create
+
+@event.listens_for(PubsubItem.__table__, "after_create")
+def fts_create(target, connection, **kw):
+    """Full-Text Search table creation"""
+    if connection.engine.name == "sqlite":
+        # Using SQLite FTS5
+        queries = [
+            "CREATE VIRTUAL TABLE pubsub_items_fts "
+            "USING fts5(data, content=pubsub_items, content_rowid=id)",
+            "CREATE TRIGGER pubsub_items_fts_sync_ins AFTER INSERT ON pubsub_items BEGIN"
+            "  INSERT INTO pubsub_items_fts(rowid, data) VALUES (new.id, new.data);"
+            "END",
+            "CREATE TRIGGER pubsub_items_fts_sync_del AFTER DELETE ON pubsub_items BEGIN"
+            "  INSERT INTO pubsub_items_fts(pubsub_items_fts, rowid, data) "
+            "VALUES('delete', old.id, old.data);"
+            "END",
+            "CREATE TRIGGER pubsub_items_fts_sync_upd AFTER UPDATE ON pubsub_items BEGIN"
+            "  INSERT INTO pubsub_items_fts(pubsub_items_fts, rowid, data) VALUES"
+            "('delete', old.id, old.data);"
+            "  INSERT INTO pubsub_items_fts(rowid, data) VALUES(new.id, new.data);"
+            "END"
+        ]
+        for q in queries:
+            connection.execute(DDL(q))
+
+# drop
+
+@event.listens_for(PubsubItem.__table__, "before_drop")
+def fts_drop(target, connection, **kw):
+    "Full-Text Search table drop" ""
+    if connection.engine.name == "sqlite":
+        # Using SQLite FTS5
+        queries = [
+            "DROP TRIGGER IF EXISTS pubsub_items_fts_sync_ins",
+            "DROP TRIGGER IF EXISTS pubsub_items_fts_sync_del",
+            "DROP TRIGGER IF EXISTS pubsub_items_fts_sync_upd",
+            "DROP TABLE IF EXISTS pubsub_items_fts",
+        ]
+        for q in queries:
+            connection.execute(DDL(q))