BigW Consortium Gitlab

20160226114608_add_trigram_indexes_for_searching.rb 1.98 KB
Newer Older
1
# rubocop:disable all
2 3 4 5 6 7
class AddTrigramIndexesForSearching < ActiveRecord::Migration
  disable_ddl_transaction!

  def up
    return unless Gitlab::Database.postgresql?

8 9
    create_trigrams_extension

10
    unless trigrams_enabled?
11 12 13 14
      raise 'You must enable the pg_trgm extension. You can do so by running ' \
        '"CREATE EXTENSION pg_trgm;" as a PostgreSQL super user, this must be ' \
        'done for every GitLab database. For more information see ' \
        'http://www.postgresql.org/docs/current/static/sql-createextension.html'
15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42
    end

    # trigram indexes are case-insensitive so we can just index the column
    # instead of indexing lower(column)
    to_index.each do |table, columns|
      columns.each do |column|
        execute "CREATE INDEX CONCURRENTLY index_#{table}_on_#{column}_trigram ON #{table} USING gin(#{column} gin_trgm_ops);"
      end
    end
  end

  def down
    return unless Gitlab::Database.postgresql?

    to_index.each do |table, columns|
      columns.each do |column|
        remove_index table, name: "index_#{table}_on_#{column}_trigram"
      end
    end
  end

  def trigrams_enabled?
    res = execute("SELECT true AS enabled FROM pg_available_extensions WHERE name = 'pg_trgm' AND installed_version IS NOT NULL;")
    row = res.first

    row && row['enabled'] == 't' ? true : false
  end

43 44 45 46 47 48 49 50 51
  def create_trigrams_extension
    # This may not work if the user doesn't have permission. We attempt in
    # case we do have permission, particularly for test/dev environments.
    begin
      enable_extension 'pg_trgm'
    rescue
    end
  end

52 53 54 55 56 57 58 59 60 61 62 63 64 65
  def to_index
    {
      ci_runners:     [:token, :description],
      issues:         [:title, :description],
      merge_requests: [:title, :description],
      milestones:     [:title, :description],
      namespaces:     [:name, :path],
      notes:          [:note],
      projects:       [:name, :path, :description],
      snippets:       [:title, :file_name],
      users:          [:username, :name, :email]
    }
  end
end