fix fts for notes

in case of renotes without comments, the top-level text is null, so
nothing got indexed

1 files changed, 21 insertions, 0 deletions

diff --git a/lib/Dakkar/TweetArchive/Store.pm b/lib/Dakkar/TweetArchive/Store.pm
index 1b926d5..7cf43c7 100644
--- a/lib/Dakkar/TweetArchive/Store.pm
+++ b/lib/Dakkar/TweetArchive/Store.pm
@@ -548,4 +548,25 @@ CREATE TABLE misskey_following (
 SQL
 }
 
+sub _schema_deploy_7($self) {
+    my $dbh = $self->dbh;
+
+    $dbh->do(<<'SQL');
+CREATE OR REPLACE FUNCTION note_text_recursive(IN t JSONB) RETURNS text
+LANGUAGE sql
+IMMUTABLE
+RETURNS NULL ON NULL INPUT
+PARALLEL SAFE
+AS $$ SELECT
+COALESCE( note_text(t), '') || ' ' ||
+COALESCE( note_text_recursive(t->'renote'), '')
+$$;
+SQL
+
+    $dbh->do(<<'SQL');
+UPDATE notes
+SET fts = to_tsvector(note_language(data),note_text_recursive(data));
+SQL
+}
+
 1;