module ActiveRecord::ConnectionAdapters::OracleEnhanced::ContextIndex

Public Instance Methods

add_context_index(table_name, column_name, options = {}) click to toggle source

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
# 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 = {}) click to toggle source

Drop full text index with Oracle specific CONTEXT index type

# 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

Private Instance Methods

create_datastore_preference(datastore_name, procedure_name) click to toggle source
# File lib/active_record/connection_adapters/oracle_enhanced/context_index.rb, line 215
          def create_datastore_preference(datastore_name, procedure_name)
            drop_ctx_preference(datastore_name)
            execute <<~SQL
              BEGIN
                CTX_DDL.CREATE_PREFERENCE('#{datastore_name}', 'USER_DATASTORE');
                CTX_DDL.SET_ATTRIBUTE('#{datastore_name}', 'PROCEDURE', '#{procedure_name}');
              END;
            SQL
          end
create_datastore_procedure(table_name, procedure_name, column_names, options) click to toggle source
# File lib/active_record/connection_adapters/oracle_enhanced/context_index.rb, line 145
          def create_datastore_procedure(table_name, procedure_name, column_names, options)
            quoted_table_name = quote_table_name(table_name)
            select_queries, column_names = column_names.partition { |c| c.to_s =~ /^\s*SELECT\s+/i }
            select_queries = select_queries.map { |s| s.strip.gsub(/\s+/, " ") }
            keys, selected_columns = parse_select_queries(select_queries)
            quoted_column_names = (column_names + keys).map { |col| quote_column_name(col) }
            execute <<~SQL
              CREATE OR REPLACE PROCEDURE #{quote_table_name(procedure_name)}
                (p_rowid IN           ROWID,
                p_clob  IN OUT NOCOPY CLOB) IS
                -- add_context_index_parameters #{(column_names + select_queries).inspect}#{!options.empty? ? +', ' << options.inspect[1..-2] : ''}
              #{
                selected_columns.map do |cols|
                  cols.map do |col|
                    raise ArgumentError, "Alias #{col} too large, should be 28 or less characters long" unless col.length <= 28
                    "l_#{col} VARCHAR2(32767);\n"
                  end.join
                end.join
              } BEGIN
                FOR r1 IN (
                  SELECT #{quoted_column_names.join(', ')}
                  FROM   #{quoted_table_name}
                  WHERE  #{quoted_table_name}.ROWID = p_rowid
                ) LOOP
                  #{
                  (column_names.map do |col|
                    col = col.to_s
                    +"DBMS_LOB.WRITEAPPEND(p_clob, #{col.length + 2}, '<#{col}>');\n" <<
                    "IF LENGTH(r1.#{col}) > 0 THEN\n" <<
                    "DBMS_LOB.WRITEAPPEND(p_clob, LENGTH(r1.#{col}), r1.#{col});\n" <<
                    "END IF;\n" <<
                    "DBMS_LOB.WRITEAPPEND(p_clob, #{col.length + 3}, '</#{col}>');\n"
                  end.join) <<
                  (selected_columns.zip(select_queries).map do |cols, query|
                    (cols.map do |col|
                      "l_#{col} := '';\n"
                    end.join) <<
                    "FOR r2 IN (\n" <<
                    query.gsub(/:(\w+)/, "r1.\\1") << "\n) LOOP\n" <<
                    (cols.map do |col|
                      "l_#{col} := l_#{col} || r2.#{col} || CHR(10);\n"
                    end.join) <<
                    "END LOOP;\n" <<
                    (cols.map do |col|
                      col = col.to_s
                      +"DBMS_LOB.WRITEAPPEND(p_clob, #{col.length + 2}, '<#{col}>');\n" <<
                      "IF LENGTH(l_#{col}) > 0 THEN\n" <<
                      "DBMS_LOB.WRITEAPPEND(p_clob, LENGTH(l_#{col}), l_#{col});\n" <<
                      "END IF;\n" <<
                      "DBMS_LOB.WRITEAPPEND(p_clob, #{col.length + 3}, '</#{col}>');\n"
                    end.join)
                  end.join)
                }
                END LOOP;
              END;
            SQL
          end
create_index_column_trigger(table_name, index_name, index_column, index_column_source) click to toggle source
# File lib/active_record/connection_adapters/oracle_enhanced/context_index.rb, line 279
          def create_index_column_trigger(table_name, index_name, index_column, index_column_source)
            trigger_name = default_index_column_trigger_name(index_name)
            columns = Array(index_column_source)
            quoted_column_names = columns.map { |col| quote_column_name(col) }.join(", ")
            execute <<~SQL
              CREATE OR REPLACE TRIGGER #{quote_table_name(trigger_name)}
              BEFORE UPDATE OF #{quoted_column_names} ON #{quote_table_name(table_name)} FOR EACH ROW
              BEGIN
                :new.#{quote_column_name(index_column)} := '1';
              END;
            SQL
          end
create_lexer_preference(lexer_name, lexer_type, options) click to toggle source
# File lib/active_record/connection_adapters/oracle_enhanced/context_index.rb, line 241
def create_lexer_preference(lexer_name, lexer_type, options)
  drop_ctx_preference(lexer_name)
  sql = +"BEGIN\nCTX_DDL.CREATE_PREFERENCE('#{lexer_name}', '#{lexer_type}');\n"
  options.each do |key, value|
    plsql_value = case value
                  when String; "'#{value}'"
                  when true; "'YES'"
                  when false; "'NO'"
                  when nil; "NULL"
                  else value
    end
    sql << "CTX_DDL.SET_ATTRIBUTE('#{lexer_name}', '#{key}', #{plsql_value});\n"
  end
  sql << "END;\n"
  execute sql
end
create_storage_preference(storage_name, tablespace) click to toggle source
# File lib/active_record/connection_adapters/oracle_enhanced/context_index.rb, line 225
def create_storage_preference(storage_name, tablespace)
  drop_ctx_preference(storage_name)
  sql = +"BEGIN\nCTX_DDL.CREATE_PREFERENCE('#{storage_name}', 'BASIC_STORAGE');\n"
  ["I_TABLE_CLAUSE", "K_TABLE_CLAUSE", "R_TABLE_CLAUSE",
  "N_TABLE_CLAUSE", "I_INDEX_CLAUSE", "P_TABLE_CLAUSE"].each do |clause|
    default_clause = case clause
                     when "R_TABLE_CLAUSE"; "LOB(DATA) STORE AS (CACHE) "
                     when "I_INDEX_CLAUSE"; "COMPRESS 2 "
                     else ""
    end
    sql << "CTX_DDL.SET_ATTRIBUTE('#{storage_name}', '#{clause}', '#{default_clause}TABLESPACE #{tablespace}');\n"
  end
  sql << "END;\n"
  execute sql
end
create_wordlist_preference(wordlist_name, wordlist_type, options) click to toggle source
# File lib/active_record/connection_adapters/oracle_enhanced/context_index.rb, line 258
def create_wordlist_preference(wordlist_name, wordlist_type, options)
  drop_ctx_preference(wordlist_name)
  sql = +"BEGIN\nCTX_DDL.CREATE_PREFERENCE('#{wordlist_name}', '#{wordlist_type}');\n"
  options.each do |key, value|
    plsql_value = case value
                  when String; "'#{value}'"
                  when true; "'YES'"
                  when false; "'NO'"
                  when nil; "NULL"
                  else value
    end
    sql << "CTX_DDL.SET_ATTRIBUTE('#{wordlist_name}', '#{key}', #{plsql_value});\n"
  end
  sql << "END;\n"
  execute sql
end
default_datastore_name(index_name) click to toggle source
# File lib/active_record/connection_adapters/oracle_enhanced/context_index.rb, line 301
def default_datastore_name(index_name)
  "#{index_name}_dst"
end
default_datastore_procedure(index_name) click to toggle source
# File lib/active_record/connection_adapters/oracle_enhanced/context_index.rb, line 297
def default_datastore_procedure(index_name)
  "#{index_name}_prc"
end
default_index_column_trigger_name(index_name) click to toggle source
# File lib/active_record/connection_adapters/oracle_enhanced/context_index.rb, line 309
def default_index_column_trigger_name(index_name)
  "#{index_name}_trg"
end
default_lexer_name(index_name) click to toggle source
# File lib/active_record/connection_adapters/oracle_enhanced/context_index.rb, line 313
def default_lexer_name(index_name)
  "#{index_name}_lex"
end
default_storage_name(index_name) click to toggle source
# File lib/active_record/connection_adapters/oracle_enhanced/context_index.rb, line 305
def default_storage_name(index_name)
  "#{index_name}_sto"
end
default_wordlist_name(index_name) click to toggle source
# File lib/active_record/connection_adapters/oracle_enhanced/context_index.rb, line 317
def default_wordlist_name(index_name)
  "#{index_name}_wl"
end
drop_ctx_preference(preference_name) click to toggle source
# File lib/active_record/connection_adapters/oracle_enhanced/context_index.rb, line 275
def drop_ctx_preference(preference_name)
  execute "BEGIN CTX_DDL.DROP_PREFERENCE('#{preference_name}'); END;" rescue nil
end
drop_index_column_trigger(index_name) click to toggle source
# File lib/active_record/connection_adapters/oracle_enhanced/context_index.rb, line 292
def drop_index_column_trigger(index_name)
  trigger_name = default_index_column_trigger_name(index_name)
  execute "DROP TRIGGER #{quote_table_name(trigger_name)}" rescue nil
end
parse_select_queries(select_queries) click to toggle source
# File lib/active_record/connection_adapters/oracle_enhanced/context_index.rb, line 203
def parse_select_queries(select_queries)
  keys = []
  selected_columns = []
  select_queries.each do |query|
    # get primary or foreign keys like :id or :something_id
    keys << (query.scan(/:\w+/).map { |k| k[1..-1].downcase.to_sym })
    select_part = query.scan(/^select\s.*\sfrom/i).first
    selected_columns << select_part.scan(/\sas\s+(\w+)/i).map { |c| c.first }
  end
  [keys.flatten.uniq, selected_columns]
end