From c2b3f184df4a60a872eea77971cd83ea6d8297b0 Mon Sep 17 00:00:00 2001
From: erdgeist <erdgeist@erdgeist.org>
Date: Fri, 26 Jun 2026 22:11:52 +0200
Subject: Enable full-text search via PostgreSQL tsvector on page_translations

- Restore search vector migration (was parked in doc/ pending PostgreSQL upgrade)
- Restore Node.search using plainto_tsquery with simple dictionary
- Cross-locale keyword search, no stemming, works for both de and en content
---
 ...25705_add_search_vector_to_page_translations.rb | 47 ++++++++++++++++++++++
 1 file changed, 47 insertions(+)
 create mode 100644 db/migrate/20260626025705_add_search_vector_to_page_translations.rb

(limited to 'db')

diff --git a/db/migrate/20260626025705_add_search_vector_to_page_translations.rb b/db/migrate/20260626025705_add_search_vector_to_page_translations.rb
new file mode 100644
index 0000000..0747637
--- /dev/null
+++ b/db/migrate/20260626025705_add_search_vector_to_page_translations.rb
@@ -0,0 +1,47 @@
+class AddSearchVectorToPageTranslations < ActiveRecord::Migration[7.2]
+  def up
+    add_column :page_translations, :search_vector, :tsvector
+
+    execute <<~SQL
+      UPDATE page_translations
+      SET search_vector = to_tsvector(
+        'simple',
+        coalesce(title, '') || ' ' ||
+        coalesce(abstract, '') || ' ' ||
+        coalesce(body, '')
+      )
+    SQL
+
+    add_index :page_translations, :search_vector,
+              using: :gin,
+              name: 'index_page_translations_on_search_vector'
+
+    execute <<~SQL
+      CREATE OR REPLACE FUNCTION page_translations_search_vector_update()
+      RETURNS trigger AS $$
+      BEGIN
+        NEW.search_vector := to_tsvector(
+          'simple',
+          coalesce(NEW.title, '') || ' ' ||
+          coalesce(NEW.abstract, '') || ' ' ||
+          coalesce(NEW.body, '')
+        );
+        RETURN NEW;
+      END;
+      $$ LANGUAGE plpgsql;
+
+      CREATE TRIGGER page_translations_search_vector_trigger
+      BEFORE INSERT OR UPDATE ON page_translations
+      FOR EACH ROW EXECUTE PROCEDURE page_translations_search_vector_update();
+    SQL
+  end
+
+  def down
+    execute <<~SQL
+      DROP TRIGGER IF EXISTS page_translations_search_vector_trigger ON page_translations;
+      DROP FUNCTION IF EXISTS page_translations_search_vector_update();
+    SQL
+    remove_index :page_translations, name: 'index_page_translations_on_search_vector'
+    remove_column :page_translations, :search_vector
+  end
+end
-- 
cgit v1.3