services/api/db/migrate/20190523180148_add_trigram_index_for_text_search.rb

   1 # Copyright (C) The Arvados Authors. All rights reserved.
   2 #
   3 # SPDX-License-Identifier: AGPL-3.0
   4
   5 class AddTrigramIndexForTextSearch < ActiveRecord::Migration[5.0]
   6   def trgm_indexes
   7     {
   8       "collections" => "collections_trgm_text_search_idx",
   9       "container_requests" => "container_requests_trgm_text_search_idx",
  10       "groups" => "groups_trgm_text_search_idx",
  11       "jobs" => "jobs_trgm_text_search_idx",
  12       "pipeline_instances" => "pipeline_instances_trgm_text_search_idx",
  13       "pipeline_templates" => "pipeline_templates_trgm_text_search_idx",
  14       "workflows" => "workflows_trgm_text_search_idx",
  15     }
  16   end
  17
  18   def up
  19     begin
  20       execute "CREATE EXTENSION IF NOT EXISTS pg_trgm"
  21     rescue ActiveRecord::StatementInvalid => e
  22       puts "Cannot create the pg_trgm extension."
  23       if e.cause.is_a?(PG::InsufficientPrivilege)
  24         puts "The user must have a SUPERUSER role."
  25       elsif e.cause.is_a?(PG::UndefinedFile)
  26         puts "The postgresql-contrib package is most likely not installed."
  27       else
  28         puts "Unknown Error."
  29       end
  30       puts "Please visit https://doc.arvados.org/admin/upgrading.html for instructions on how to run this migration."
  31       throw e
  32     end
  33
  34     trgm_indexes.each do |model, indx|
  35       execute "CREATE INDEX #{indx} ON #{model} USING gin((#{model.classify.constantize.full_text_trgm}) gin_trgm_ops)"
  36     end
  37   end
  38
  39   def down
  40     trgm_indexes.each do |_, indx|
  41       execute "DROP INDEX IF EXISTS #{indx}"
  42     end
  43   end
  44 end