Module: ActiveRecord::ConnectionAdapters::OracleEnhanced::ContextIndex

Included in:
ActiveRecord::ConnectionAdapters::OracleEnhancedAdapter
Defined in:
lib/active_record/connection_adapters/oracle_enhanced/context_index.rb

Defined Under Namespace

Modules: BaseClassMethods, ContextIndexClassMethods

Instance Method Summary collapse

Instance Method Details

#add_context_index(table_name, column_name, options = {}) ⇒ Object

Define full text index with Oracle specific CONTEXT index type

Oracle CONTEXT index by default supports full text indexing of one column. This method allows full text index creation also on several columns as well as indexing related table columns by generating stored procedure that concatenates all columns for indexing as well as generating trigger that will update main index column to trigger reindexing of record.

Use contains ActiveRecord model instance method to add CONTAINS where condition and order by score of matched results.

Options:

  • :name

  • :index_column

  • :index_column_trigger_on

  • :tablespace

  • :sync - 'MANUAL', 'EVERY “interval-string”' or 'ON COMMIT' (defaults to 'MANUAL').

  • :lexer - Lexer options (e.g. :type => 'BASIC_LEXER', :base_letter => true).

  • :wordlist - Wordlist options (e.g. :type => 'BASIC_WORDLIST', :prefix_index => true).

  • :transactional - When true, the CONTAINS operator will process inserted and updated rows.

Examples
Creating single column index
add_context_index :posts, :title

search with

Post.contains(:title, 'word')
Creating index on several columns
add_context_index :posts, [:title, :body]

search with (use first column as argument for contains method but it will search in all index columns)

Post.contains(:title, 'word')
Creating index on several columns with dummy index column and commit option
add_context_index :posts, [:title, :body], :index_column => :all_text, :sync => 'ON COMMIT'

search with

Post.contains(:all_text, 'word')
Creating index with trigger option (will reindex when specified columns are updated)
add_context_index :posts, [:title, :body], :index_column => :all_text, :sync => 'ON COMMIT',
                   :index_column_trigger_on => [:created_at, :updated_at]

search with

Post.contains(:all_text, 'word')
Creating index on multiple tables
add_context_index :posts,
 [:title, :body,
 # specify aliases always with AS keyword
 "SELECT comments.author AS comment_author, comments.body AS comment_body FROM comments WHERE comments.post_id = :id"
 ],
 :name => 'post_and_comments_index',
 :index_column => :all_text, :index_column_trigger_on => [:updated_at, :comments_count],
 :sync => 'ON COMMIT'

search in any table columns

Post.contains(:all_text, 'word')

search in specified column

Post.contains(:all_text, "aaa within title")
Post.contains(:all_text, "bbb within comment_author")
Creating index using lexer
add_context_index :posts, :title, :lexer => { :type => 'BASIC_LEXER', :base_letter => true, ... }
Creating index using wordlist
add_context_index :posts, :title, :wordlist => { :type => 'BASIC_WORDLIST', :prefix_index => true, ... }
Creating transactional index (will reindex changed rows when querying)
add_context_index :posts, :title, :transactional => true



76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
 
# File 'lib/active_record/connection_adapters/oracle_enhanced/context_index.rb', line 76

def add_context_index(table_name, column_name, options = {})
  column_names = Array(column_name)
  index_name = options[:name] || index_name(table_name, column: options[:index_column] || column_names,
    # CONEXT index name max length is 25
    identifier_max_length: 25)

  quoted_column_name = quote_column_name(options[:index_column] || column_names.first)
  if options[:index_column_trigger_on]
    raise ArgumentError, "Option :index_column should be specified together with :index_column_trigger_on option" \
      unless options[:index_column]
    create_index_column_trigger(table_name, index_name, options[:index_column], options[:index_column_trigger_on])
  end

  sql = +"CREATE INDEX #{quote_column_name(index_name)} ON #{quote_table_name(table_name)}"
  sql << " (#{quoted_column_name})"
  sql << " INDEXTYPE IS CTXSYS.CONTEXT"
  parameters = []
  if column_names.size > 1
    procedure_name = default_datastore_procedure(index_name)
    datastore_name = default_datastore_name(index_name)
    create_datastore_procedure(table_name, procedure_name, column_names, options)
    create_datastore_preference(datastore_name, procedure_name)
    parameters << "DATASTORE #{datastore_name} SECTION GROUP CTXSYS.AUTO_SECTION_GROUP"
  end
  if options[:tablespace]
    storage_name = default_storage_name(index_name)
    create_storage_preference(storage_name, options[:tablespace])
    parameters << "STORAGE #{storage_name}"
  end
  if options[:sync]
    parameters << "SYNC(#{options[:sync]})"
  end
  if options[:lexer] && (lexer_type = options[:lexer][:type])
    lexer_name = default_lexer_name(index_name)
    (lexer_options = options[:lexer].dup).delete(:type)
    create_lexer_preference(lexer_name, lexer_type, lexer_options)
    parameters << "LEXER #{lexer_name}"
  end
  if options[:wordlist] && (wordlist_type = options[:wordlist][:type])
    wordlist_name = default_wordlist_name(index_name)
    (wordlist_options = options[:wordlist].dup).delete(:type)
    create_wordlist_preference(wordlist_name, wordlist_type, wordlist_options)
    parameters << "WORDLIST #{wordlist_name}"
  end
  if options[:transactional]
    parameters << "TRANSACTIONAL"
  end
  unless parameters.empty?
    sql << " PARAMETERS ('#{parameters.join(' ')}')"
  end
  execute sql
end

#remove_context_index(table_name, options = {}) ⇒ Object

Drop full text index with Oracle specific CONTEXT index type



130
131
132
133
134
135
136
137
138
139
140
141
142
 
# File 'lib/active_record/connection_adapters/oracle_enhanced/context_index.rb', line 130

def remove_context_index(table_name, options = {})
  unless Hash === options # if column names passed as argument
    options = { column: Array(options) }
  end
  index_name = options[:name] || index_name(table_name,
    column: options[:index_column] || options[:column], identifier_max_length: 25)
  execute "DROP INDEX #{index_name}"
  drop_ctx_preference(default_datastore_name(index_name))
  drop_ctx_preference(default_storage_name(index_name))
  procedure_name = default_datastore_procedure(index_name)
  execute "DROP PROCEDURE #{quote_table_name(procedure_name)}" rescue nil
  drop_index_column_trigger(index_name)
end