switch (back) to postgres fts engine for fast search & timeline filters

5 files changed, 13 insertions, 48 deletions

diff --git a/app/helpers/filter_helper.rb b/app/helpers/filter_helper.rb
index db2652557..b9dbc1f4f 100644
--- a/app/helpers/filter_helper.rb
+++ b/app/helpers/filter_helper.rb
@@ -7,7 +7,7 @@ module FilterHelper
 
     status = status.reblog if status.reblog?
 
-    if Status.where(id: status.id).regex_filtered_by_account(receiver_id).exists?
+    if Status.where(id: status.id).search_filtered_by_account(receiver_id).exists?
       redis.sadd("filtered_statuses:#{receiver_id}", status.id)
       return true
     end
diff --git a/app/helpers/search_helper.rb b/app/helpers/search_helper.rb
deleted file mode 100644
index 8bddbe187..000000000
--- a/app/helpers/search_helper.rb
+++ /dev/null
@@ -1,20 +0,0 @@
-require 'sixarm_ruby_unaccent'
-
-module SearchHelper
-	def expand_search_query(query)
-    return '' if query.blank?
-    query = query.downcase.unaccent.gsub(/[^\p{Word} [:punct:]]/, '').gsub(/  +/, ' ').strip
-    return '' if query.blank?
-
-    if query.include?(':')
-      query_parts = query.split(':', 2)
-      if %w(tag tags).include?(query_parts[0])
-        query = "^tag (#{query_parts[1].split.join('|')})"
-      elsif %w(subj text desc).include?(query_parts[0])
-        query = "^#{query_parts[0]} .*#{query_parts[1]}"
-      end
-    end
-
-    query.gsub(/"(.*)"/, '\\y\1\\y')
-  end
-end
diff --git a/app/lib/bangtags.rb b/app/lib/bangtags.rb
index a3874c128..9fec00d9d 100644
--- a/app/lib/bangtags.rb
+++ b/app/lib/bangtags.rb
@@ -3,7 +3,6 @@
 class Bangtags
   include ModerationHelper
   include ServiceAccountHelper
-  include SearchHelper
 
   attr_reader :status, :account
 
@@ -764,7 +763,7 @@ class Bangtags
           q = cmd[1..-1].join.strip
           next if q.blank?
           begin
-            data = @account.statuses.regex(expand_search_query(q))
+            data = @account.statuses.search(q.unaccent)
               .reorder(:created_at)
               .pluck(:created_at)
               .map { |d| d.strftime('%Y-%m') }
diff --git a/app/models/normalized_status.rb b/app/models/normalized_status.rb
deleted file mode 100644
index ad1ffaffc..000000000
--- a/app/models/normalized_status.rb
+++ /dev/null
@@ -1,13 +0,0 @@
-# == Schema Information
-#
-# Table name: normalized_statuses
-#
-#  id        :bigint(8)        not null, primary key
-#  status_id :bigint(8)
-#  text      :text
-#
-
-class NormalizedStatus < ApplicationRecord
-  belongs_to :status, inverse_of: :normalized_status
-  validates_uniqueness_of :status_id
-end
diff --git a/app/models/status.rb b/app/models/status.rb
index f4c90fea2..d051d8962 100644
--- a/app/models/status.rb
+++ b/app/models/status.rb
@@ -30,6 +30,7 @@
 #  edited                 :boolean
 #  boostable              :boolean
 #  reject_replies         :boolean
+#  tsv                    :tsvector
 #
 
 class Status < ApplicationRecord
@@ -82,7 +83,6 @@ class Status < ApplicationRecord
   has_one :status_stat, inverse_of: :status
   has_one :poll, inverse_of: :status, dependent: :destroy
   has_one :destructing_status, inverse_of: :status, dependent: :destroy
-  has_one :normalized_status, inverse_of: :status, dependent: :destroy
   has_one :imported_status, inverse_of: :status, dependent: :destroy
   has_one :sharekey, inverse_of: :status, dependent: :destroy
 
@@ -118,10 +118,10 @@ class Status < ApplicationRecord
   scope :mention_not_excluded_by_account, ->(account) { left_outer_joins(:mentions).where('mentions.account_id IS NULL OR mentions.account_id NOT IN (?)', account.excluded_from_timeline_account_ids) }
   scope :not_domain_blocked_by_account, ->(account) { account.excluded_from_timeline_domains.blank? ? left_outer_joins(:account) : left_outer_joins(:account).where('accounts.domain IS NULL OR accounts.domain NOT IN (?)', account.excluded_from_timeline_domains) }
 
-  scope :like, ->(needle) { joins(:normalized_status).select('statuses.*').where('normalized_statuses.text LIKE f_normalize(?)', needle) }
-  scope :regex, ->(needle) { joins(:normalized_status).select('statuses.*').where('normalized_statuses.text ~ f_normalize(?)', needle) }
-  scope :regex_filtered_by_account, ->(account_id) { joins(:normalized_status).select('statuses.*').where('normalized_statuses.text ~ ANY(ARRAY(SELECT f_normalize(phrase) FROM custom_filters WHERE account_id = ?))', account_id) }
-  scope :regex_not_filtered_by_account, ->(account_id) { joins(:normalized_status).select('statuses.*').where('normalized_statuses.text !~ ALL(ARRAY(SELECT f_normalize(phrase) FROM custom_filters WHERE account_id = ?))', account_id) }
+  scope :search, ->(needle) { where("tsv @@ websearch_to_tsquery('fedi', ?)", needle) }
+  scope :search_not, ->(needle) { where.not("tsv @@ websearch_to_tsquery('fedi', ?)", needle) }
+  scope :search_filtered_by_account, ->(account_id) { where('tsv @@ (SELECT tsquery_union(websearch_to_tsquery(phrase)) FROM custom_filters WHERE account_id = ?)', account_id) }
+  scope :search_not_filtered_by_account, ->(account_id) { where.not('tsv @@ (SELECT tsquery_union(websearch_to_tsquery(phrase)) FROM custom_filters WHERE account_id = ?)', account_id) }
 
   scope :not_missing_media_desc, -> { left_outer_joins(:media_attachments).select('statuses.*').where('media_attachments.id IS NULL OR media_attachments.description IS NOT NULL') }
 
@@ -362,8 +362,6 @@ class Status < ApplicationRecord
   after_save :process_bangtags, if: :local?
 
   class << self
-    include SearchHelper
-
     def search_for(term, account = nil, limit = 33, offset = 0)
       return none if account.nil?
       if term.start_with?('me:')
@@ -371,12 +369,13 @@ class Status < ApplicationRecord
         query = account.statuses
       else
         query = Status.where(account_id: account.id)
-          .or(Status.where(account_id: account.following, visibility: [:private, :local, :unlisted]))
+          .or(Status.where(visibility: [:local, :public]))
+          .or(Status.where(account_id: account.following, visibility: [:private, :unlisted]))
           .or(Status.where(id: account.mentions.select(:status_id)))
       end
-      return none if term.blank? || term.length < 3
+      return none if term.blank?
       query = query.without_reblogs
-        .regex(expand_search_query(term))
+        .search(term.unaccent)
         .offset(offset).limit(limit)
       apply_timeline_filters(query, account, true)
     rescue ActiveRecord::StatementInvalid
@@ -583,9 +582,9 @@ class Status < ApplicationRecord
       query = query.mention_not_excluded_by_account(account)
       unless account.custom_filters.nil?
         if account.user.invert_filters
-          query = query.regex_filtered_by_account(account.id)
+          query = query.search_filtered_by_account(account.id)
         else
-          query = query.regex_not_filtered_by_account(account.id)
+          query = query.search_not_filtered_by_account(account.id)
         end
       end
       query = query.not_missing_media_desc if account.filter_undescribed?