Module: ActiveRecord::ConnectionAdapters::OracleEnhancedContextIndex

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).

  • :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 transactional index (will reindex changed rows when querying)
add_context_index :posts, :title, :transactional => true



70
71
72
73
74
75
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
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_context_index.rb', line 70

def add_context_index(table_name, column_name, options = {})
  self.all_schema_indexes = nil
  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[: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



119
120
121
122
123
124
125
126
127
128
129
130
131
132
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_context_index.rb', line 119

def remove_context_index(table_name, options = {})
  self.all_schema_indexes = nil
  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