search

rsim / oracle-enhanced

Oracle enhaced adapter for ActiveRecord
MIT License
543 stars 307 forks source link

OCIError: ORA-00904 - problem with full text indexing columns #664

Closed Dharshi closed 8 years ago

Dharshi commented 9 years ago

I have done following updates

rails from 4.1.0 to 4.2.3 activerecord-oracle_enhanced-adapter from 1.5.5 to 1.6.0 ruby-oci8 from 2.1.7 to 2.1.8

And now having following error

OCIError: ORA-00904: "departments.name": invalid identifier when I tried to do

Model.contains('departments.name', '%xyz%')

To reproduce 

add_context_index "departments", ["name"], name: "i_dep_context_name", sync: "ON COMMIT"

class Department < ActiveRecord::Base
   has_context_index
end

Department.contains("Department.name", '%xyz%')
yahonda commented 9 years ago

Would you update also Department migration definitions?

Dharshi commented 9 years ago

Please find the Department definition in schema file.

create_table "departments", force: true do |t|
    t.string   "name",  null: false
    t.integer  "year_id",  precision: 38, scale: 0,  null: false
    t.datetime "created_at"
    t.datetime "updated_at"
    t.integer  "org_unit_id",  precision: 38, scale: 0
  end

  add_context_index "departments", ["name"], name: "i_dep_context_name", sync: "ON COMMIT"
  add_index "departments", ["name", "year_id"], name: "i_dep_nam_yea_id", unique: true
  add_index "departments", ["name"], name: "i_departments_name"
  add_index "departments", ["org_unit_id"], name: "i_departments_org_unit_id"
yahonda commented 9 years ago

Thanks for providing info. I tested using Rails 4.1.0 with Oracle enhanced adapter 1.5.5 and Rails 4.2.3 with Oracle enhanced adapter 1.6.2. In both versions, it gets OCIError: ORA-00904: "Department.name": invalid identifier: SELECT "DEPARTMENTS".* FROM "DEPARTMENTS" WHERE (CONTAINS("Department.name", '%xyz%', 0) > 0) ORDER BY SCORE(0) DESC 


begin
  require 'bundler/inline'
rescue LoadError => e
  $stderr.puts 'Bundler version 1.10 or later is required. Please update your Bundler'
  raise e
end

gemfile(true) do
  source 'https://rubygems.org'
  gem 'activerecord', '4.2.3'
  gem 'activerecord-oracle_enhanced-adapter','1.6.2'
  gem 'ruby-oci8'
  gem 'minitest'
end

require 'active_record'
require 'minitest/autorun'
require 'logger'

Minitest::Test = MiniTest::Unit::TestCase unless defined?(Minitest::Test)

DATABASE_NAME         = ENV['DATABASE_NAME']         || 'orcl'
DATABASE_HOST         = ENV['DATABASE_HOST']         || "127.0.0.1"
DATABASE_PORT         = ENV['DATABASE_PORT']         || 1521
DATABASE_USER         = ENV['DATABASE_USER']         || 'oracle_enhanced'
DATABASE_PASSWORD     = ENV['DATABASE_PASSWORD']     || 'oracle_enhanced'
DATABASE_SYS_PASSWORD = ENV['DATABASE_SYS_PASSWORD'] || 'admin'

CONNECTION_PARAMS = {
  :adapter => "oracle_enhanced",
  :database => DATABASE_NAME,
  :host => DATABASE_HOST,
  :port => DATABASE_PORT,
  :username => DATABASE_USER,
  :password => DATABASE_PASSWORD
}

ActiveRecord::Base.establish_connection(CONNECTION_PARAMS)
ActiveRecord::Base.logger = Logger.new(STDOUT)

ActiveRecord::Schema.define do
  create_table "departments", force: true do |t|
    t.string   "name",  null: false
    t.integer  "year_id",  precision: 38, scale: 0,  null: false
    t.datetime "created_at"
    t.datetime "updated_at"
    t.integer  "org_unit_id",  precision: 38, scale: 0
  end

  add_context_index "departments", ["name"], name: "i_dep_context_name", sync: "ON COMMIT"
  add_index "departments", ["name", "year_id"], name: "i_dep_nam_yea_id", unique: true
#  add_index "annual_departments", ["name"], name: "i_departments_name"
#  add_index "annual_departments", ["org_unit_id"], name: "i_departments_org_unit_id"
end

class Department < ActiveRecord::Base
  has_context_index
end

xyz = Department.contains("Department.name", '%xyz%')
#xyz = Department.contains("name", '%xyz%')
puts xyz

D, [2015-07-28T12:19:46.281002 #23943] DEBUG -- :   Department Load (2.9ms)  SELECT "DEPARTMENTS".* FROM "DEPARTMENTS" WHERE (CONTAINS("Department.name", '%xyz%', 0) > 0)  ORDER BY SCORE(0) DESC
E, [2015-07-28T12:19:46.281122 #23943] ERROR -- : OCIError: ORA-00904: "Department.name": invalid identifier: SELECT "DEPARTMENTS".* FROM "DEPARTMENTS" WHERE (CONTAINS("Department.name", '%xyz%', 0) > 0)  ORDER BY SCORE(0) DESC
stmt.c:250:in oci8lib_220.so: OCIError: ORA-00904: "Department.name": invalid identifier: SELECT "DEPARTMENTS".* FROM "DEPARTMENTS" WHERE (CONTAINS("Department.name", '%xyz%', 0) > 0)  ORDER BY SCORE(0) DESC (ActiveRecord::StatementInvalid)
        from /home/yahonda/.rvm/gems/ruby-2.2.2@rails42/gems/ruby-oci8-2.1.8/lib/oci8/cursor.rb:126:in `exec'
        from /home/yahonda/.rvm/gems/ruby-2.2.2@rails42/gems/activerecord-oracle_enhanced-adapter-1.6.2/lib/active_record/connection_adapters/oracle_enhanced/oci_connection.rb:151:in `exec'
        from /home/yahonda/.rvm/gems/ruby-2.2.2@rails42/gems/activerecord-oracle_enhanced-adapter-1.6.2/lib/active_record/connection_adapters/oracle_enhanced/database_statements.rb:43:in `block in exec_query'
        from /home/yahonda/.rvm/gems/ruby-2.2.2@rails42/gems/activerecord-4.2.3/lib/active_record/connection_adapters/abstract_adapter.rb:473:in `block in log'
        from /home/yahonda/.rvm/gems/ruby-2.2.2@rails42/gems/activesupport-4.2.3/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
        from /home/yahonda/.rvm/gems/ruby-2.2.2@rails42/gems/activerecord-4.2.3/lib/active_record/connection_adapters/abstract_adapter.rb:467:in `log'
        from /home/yahonda/.rvm/gems/ruby-2.2.2@rails42/gems/activerecord-oracle_enhanced-adapter-1.6.2/lib/active_record/connection_adapters/oracle_enhanced_adapter.rb:1348:in `log'
        from /home/yahonda/.rvm/gems/ruby-2.2.2@rails42/gems/activerecord-oracle_enhanced-adapter-1.6.2/lib/active_record/connection_adapters/oracle_enhanced/database_statements.rb:23:in `exec_query'
        from /home/yahonda/.rvm/gems/ruby-2.2.2@rails42/gems/activerecord-oracle_enhanced-adapter-1.6.2/lib/active_record/connection_adapters/oracle_enhanced_adapter.rb:1308:in `select'
        from /home/yahonda/.rvm/gems/ruby-2.2.2@rails42/gems/activerecord-4.2.3/lib/active_record/connection_adapters/abstract/database_statements.rb:32:in `select_all'
        from /home/yahonda/.rvm/gems/ruby-2.2.2@rails42/gems/activerecord-4.2.3/lib/active_record/connection_adapters/abstract/query_cache.rb:70:in `select_all'
        from /home/yahonda/.rvm/gems/ruby-2.2.2@rails42/gems/activerecord-4.2.3/lib/active_record/querying.rb:39:in `find_by_sql'
        from /home/yahonda/.rvm/gems/ruby-2.2.2@rails42/gems/activerecord-4.2.3/lib/active_record/relation.rb:638:in `exec_queries'
        from /home/yahonda/.rvm/gems/ruby-2.2.2@rails42/gems/activerecord-4.2.3/lib/active_record/relation.rb:514:in `load'
        from /home/yahonda/.rvm/gems/ruby-2.2.2@rails42/gems/activerecord-4.2.3/lib/active_record/relation.rb:243:in `to_a'
        from /home/yahonda/.rvm/gems/ruby-2.2.2@rails42/gems/activerecord-4.2.3/lib/active_record/relation/delegation.rb:46:in `to_ary'
        from test.rb:63:in `puts'
        from test.rb:63:in `puts'
        from test.rb:63:in `<main>'
yahonda commented 9 years ago

By replacing

Department.contains("Department.name", '%xyz%')

with like this, it should work.

Department.contains("name", '%xyz%')

contains first argument should not contain model name, just use attribute/column name here. You can refer test cases from this url. https://github.com/rsim/oracle-enhanced/blob/master/spec/active_record/connection_adapters/oracle_enhanced_context_index_spec.rb

Dharshi commented 9 years ago

Hi yahonda, Appreciate getting back on this one. Yes it works for a single table query but the problem I am having is there are other join queries where I needed to specify it's model name with attribute because otherwise it will be ambiguous column name error. It was working on previous version of (1.5.5).

yahonda commented 9 years ago

Please update the entire test case so that I (or someone else) can reproduce your errors. Unfortunately I was not able to reproduce your cases since it did not have information about "other join queries".

Dharshi commented 9 years ago

Updated test case

class Department < ActiveRecord::Base
   extend RailsApp::Searchable

   has_context_index
   belongs_to :org_unit

end

class OrgUnit < ActiveRecord::Base
   has_many :departments
end

Department Schema definition

create_table "departments", force: true do |t|
    t.string   "name",  null: false
    t.integer  "year_id",  precision: 38, scale: 0,  null: false
    t.datetime "created_at"
    t.datetime "updated_at"
    t.integer  "org_unit_id",  precision: 38, scale: 0
  end

  add_context_index "departments", ["name"], name: "i_dep_context_name", sync: "ON COMMIT"
  add_index "departments", ["name", "year_id"], name: "i_dep_nam_yea_id", unique: true
  add_index "departments", ["name"], name: "i_departments_name"
  add_index "departments", ["org_unit_id"], name: "i_departments_org_unit_id"

OrgUnit Schema definition

  create_table "org_units", force: true do |t|
    t.string   "name"
    t.datetime "created_at",                                               null: false
    t.datetime "updated_at",                                               null: false
    t.string   "ancestry"
    t.integer  "ancestry_depth",      precision: 38, scale: 0, default: 0
  end

  add_index "org_units", ["ancestry"], name: "index_org_units_on_ancestry"
  add_index "org_units", ["ancestry_depth"], name: "i_org_units_ancestry_depth"

***searchable.rb

module RailsApp  
  module Searchable
    def self.extended(model)
      model.has_context_index
    end

    def text_search(column, query, options = {})
      text_search_query column, query, [TEXT_OR_STATEMENT, OR_STATEMENT], options
    end

    private

    TEXT_OR_STATEMENT = ' | '
    TEXT_AND_STATEMENT = ' & '
    OR_STATEMENT = ' OR '
    AND_STATEMENT = ' AND '

    def text_search_query(column, query, statement, options = {})
      columns = Array(column)
      queries = Array(query)

      if queries.length > 1
        text_search_multiple_queries columns, queries, statement, options
      else
        text_search_single_query columns, queries.first, statement.last, options
      end
    end

    def text_search_single_query(columns, query, statement, options = {})
      if RailsApp::Configuration.instance.oracle_text?
        contains(columns.first, query, options)
      else
        where(columns.map { |c| "LOWER(#{c}) LIKE LOWER(:query)" }.join(statement), query: query)
      end
    end

    def text_search_multiple_queries(columns, queries, statement, options = {})
      if RailsApp::Configuration.instance.oracle_text?
        contains(columns.first, queries.join(statement.first), options)
      else
        default_text_search_multiple_queries(columns, queries, statement)
      end
    end

    def default_text_search_multiple_queries(columns, queries, statement)
      where(columns.map { |c|
              queries.map { |q|
                "LOWER(#{c}) LIKE LOWER(#{connection.quote(q)})"
              }.join(statement.last)
            }.join(statement.last))
    end
  end
end

*\ configuration.rb

require 'singleton'

module RailsApp
  class Configuration
    include Singleton

    def initialize
      @config = YAML.load(ERB.new(File.read("#{Rails.root}/config/app_config.yml")).result)[Rails.env]
    end

    def oracle_text?
      config['has_oracle_text']
    end

    def oracle_enterprise?
      config['oracle_enterprise']
    end

    private

    attr_reader :config

    def url(url)
      if port
        "#{url}:#{port}"
      else
        url
      end
    end

    def port
      Capybara.server_port if defined?(Capybara) && Capybara.server_port.present?
    end
  end
end

***app_config.yml

production:
  has_oracle_text: true
  oracle_enterprise: true

To reproduce the problem 

Department.joins(:org_unit).text_search('departments.name', 'xyz').order('departments.name').select('departments.name').map(&:name).uniq

it is giving me following error 

OCIError: ORA-00904: "departments.name": invalid identifier
yahonda commented 9 years ago

Thanks for the update.

yahonda commented 9 years ago

Would you update the definition of text_search method since it gets this message.

: undefined method `text_search' for #<ActiveRecord::Relation []> (NoMethodError)
        from /home/yahonda/.rvm/gems/ruby-2.2.2@rails42/gems/activerecord-4.2.3/lib/active_record/relation/delegation.rb:99:in `method_missing'
        from test.rb:77:in `<main>'
begin
  require 'bundler/inline'
rescue LoadError => e
  $stderr.puts 'Bundler version 1.10 or later is required. Please update your Bundler'
  raise e
end

gemfile(true) do
  source 'https://rubygems.org'
  gem 'activerecord', '4.2.3'
  gem 'activerecord-oracle_enhanced-adapter','1.6.2'
  gem 'ruby-oci8'
  gem 'minitest'
end

require 'active_record'
require 'minitest/autorun'
require 'logger'

Minitest::Test = MiniTest::Unit::TestCase unless defined?(Minitest::Test)

DATABASE_NAME         = ENV['DATABASE_NAME']         || 'orcl'
DATABASE_HOST         = ENV['DATABASE_HOST']         || "127.0.0.1"
DATABASE_PORT         = ENV['DATABASE_PORT']         || 1521
DATABASE_USER         = ENV['DATABASE_USER']         || 'oracle_enhanced'
DATABASE_PASSWORD     = ENV['DATABASE_PASSWORD']     || 'oracle_enhanced'
DATABASE_SYS_PASSWORD = ENV['DATABASE_SYS_PASSWORD'] || 'admin'

CONNECTION_PARAMS = {
  :adapter => "oracle_enhanced",
  :database => DATABASE_NAME,
  :host => DATABASE_HOST,
  :port => DATABASE_PORT,
  :username => DATABASE_USER,
  :password => DATABASE_PASSWORD
}

ActiveRecord::Base.establish_connection(CONNECTION_PARAMS)
ActiveRecord::Base.logger = Logger.new(STDOUT)

ActiveRecord::Schema.define do
  create_table "departments", force: true do |t|
    t.string   "name",  null: false
    t.integer  "year_id",  precision: 38, scale: 0,  null: false
    t.datetime "created_at"
    t.datetime "updated_at"
    t.integer  "org_unit_id",  precision: 38, scale: 0
  end

  add_context_index "departments", ["name"], name: "i_dep_context_name", sync: "ON COMMIT"
  add_index "departments", ["name", "year_id"], name: "i_dep_nam_yea_id", unique: true
  add_index "departments", ["name"], name: "i_departments_name"
  add_index "departments", ["org_unit_id"], name: "i_departments_org_unit_id"

  create_table "org_units", force: true do |t|
    t.string   "name"
    t.datetime "created_at",  null: false
    t.datetime "updated_at",  null: false
    t.string   "ancestry"
    t.integer  "ancestry_depth",      precision: 38, scale: 0, default: 0
  end

  add_index "org_units", ["ancestry"], name: "index_org_units_on_ancestry"
  add_index "org_units", ["ancestry_depth"], name: "i_org_units_ancestry_depth"
end

class Department < ActiveRecord::Base
  has_context_index
  belongs_to :org_unit
end

class OrgUnit < ActiveRecord::Base
  has_many :departments
end

Department.joins(:org_unit).text_search('departments.name', 'xyz').order('departments.name').select('departments.name').map(&:name).uniq
$ ruby test.rb
Fetching gem metadata from https://rubygems.org/.......
Fetching version metadata from https://rubygems.org/..
Resolving dependencies...
Using i18n 0.7.0
Using json 1.8.3
Using minitest 5.7.0
Using thread_safe 0.3.5
Using tzinfo 1.2.2
Using activesupport 4.2.3
Using builder 3.2.2
Using activemodel 4.2.3
Using arel 6.0.2
Using activerecord 4.2.3
Using activerecord-oracle_enhanced-adapter 1.6.2
Using ruby-oci8 2.1.8
Using bundler 1.10.5
-- create_table("departments", {:force=>true})
D, [2015-07-30T11:58:47.154197 #8901] DEBUG -- :    (183.5ms)  DROP TABLE "DEPARTMENTS"
D, [2015-07-30T11:58:47.158412 #8901] DEBUG -- :    (3.7ms)  DROP SEQUENCE "DEPARTMENTS_SEQ"
D, [2015-07-30T11:58:47.174150 #8901] DEBUG -- :    (15.2ms)  CREATE TABLE "DEPARTMENTS" ("ID" NUMBER(38) NOT NULL PRIMARY KEY, "NAME" VARCHAR2(255) NOT NULL, "YEAR_ID" NUMBER(38) NOT NULL, "CREATED_AT" DATE, "UPDATED_AT" DATE, "ORG_UNIT_ID" NUMBER(38))
D, [2015-07-30T11:58:47.176776 #8901] DEBUG -- :    (2.4ms)  CREATE SEQUENCE "DEPARTMENTS_SEQ" START WITH 10000
   -> 0.2593s
-- add_context_index("departments", ["name"], {:name=>"i_dep_context_name", :sync=>"ON COMMIT"})
D, [2015-07-30T11:58:47.251666 #8901] DEBUG -- :    (74.5ms)  CREATE INDEX "I_DEP_CONTEXT_NAME" ON "DEPARTMENTS" ("NAME") INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ('SYNC(ON COMMIT)')
   -> 0.0748s
   -> 0 rows
-- add_index("departments", ["name", "year_id"], {:name=>"i_dep_nam_yea_id", :unique=>true})
W, [2015-07-30T11:58:47.251960 #8901]  WARN -- : OracleEnhanced shortened default index name index_departments_on_name_and_year_id to i_departments_name_year_id
D, [2015-07-30T11:58:47.266565 #8901] DEBUG -- :    (1.9ms)              SELECT 1 FROM all_indexes i
            WHERE i.owner = 'ORACLE_ENHANCED'
               AND i.table_owner = 'ORACLE_ENHANCED'
               AND i.table_name = 'DEPARTMENTS'
               AND i.index_name = 'I_DEP_NAM_YEA_ID'

D, [2015-07-30T11:58:47.271972 #8901] DEBUG -- :    (5.0ms)  CREATE UNIQUE INDEX "I_DEP_NAM_YEA_ID" ON "DEPARTMENTS" ("NAME", "YEAR_ID")
D, [2015-07-30T11:58:47.278362 #8901] DEBUG -- :    (6.1ms)  ALTER TABLE "DEPARTMENTS" ADD CONSTRAINT "I_DEP_NAM_YEA_ID" UNIQUE ("NAME", "YEAR_ID")
   -> 0.0266s
   -> 0 rows
-- add_index("departments", ["name"], {:name=>"i_departments_name"})
D, [2015-07-30T11:58:47.281375 #8901] DEBUG -- :    (1.0ms)              SELECT 1 FROM all_indexes i
            WHERE i.owner = 'ORACLE_ENHANCED'
               AND i.table_owner = 'ORACLE_ENHANCED'
               AND i.table_name = 'DEPARTMENTS'
               AND i.index_name = 'I_DEPARTMENTS_NAME'

D, [2015-07-30T11:58:47.286055 #8901] DEBUG -- :    (4.5ms)  CREATE  INDEX "I_DEPARTMENTS_NAME" ON "DEPARTMENTS" ("NAME")
   -> 0.0076s
-- add_index("departments", ["org_unit_id"], {:name=>"i_departments_org_unit_id"})
W, [2015-07-30T11:58:47.286247 #8901]  WARN -- : OracleEnhanced shortened default index name index_departments_on_org_unit_id to i_departments_org_unit_id
D, [2015-07-30T11:58:47.287545 #8901] DEBUG -- :    (0.7ms)              SELECT 1 FROM all_indexes i
            WHERE i.owner = 'ORACLE_ENHANCED'
               AND i.table_owner = 'ORACLE_ENHANCED'
               AND i.table_name = 'DEPARTMENTS'
               AND i.index_name = 'I_DEPARTMENTS_ORG_UNIT_ID'

D, [2015-07-30T11:58:47.292284 #8901] DEBUG -- :    (4.5ms)  CREATE  INDEX "I_DEPARTMENTS_ORG_UNIT_ID" ON "DEPARTMENTS" ("ORG_UNIT_ID")
   -> 0.0062s
-- create_table("org_units", {:force=>true})
D, [2015-07-30T11:58:47.311777 #8901] DEBUG -- :    (18.3ms)  DROP TABLE "ORG_UNITS"
D, [2015-07-30T11:58:47.315889 #8901] DEBUG -- :    (3.7ms)  DROP SEQUENCE "ORG_UNITS_SEQ"
D, [2015-07-30T11:58:47.332385 #8901] DEBUG -- :    (13.8ms)  CREATE TABLE "ORG_UNITS" ("ID" NUMBER(38) NOT NULL PRIMARY KEY, "NAME" VARCHAR2(255), "CREATED_AT" DATE NOT NULL, "UPDATED_AT" DATE NOT NULL, "ANCESTRY" VARCHAR2(255), "ANCESTRY_DEPTH" NUMBER(38) DEFAULT 0)
D, [2015-07-30T11:58:47.335192 #8901] DEBUG -- :    (2.5ms)  CREATE SEQUENCE "ORG_UNITS_SEQ" START WITH 10000
   -> 0.0426s
-- add_index("org_units", ["ancestry"], {:name=>"index_org_units_on_ancestry"})
D, [2015-07-30T11:58:47.336478 #8901] DEBUG -- :    (0.6ms)              SELECT 1 FROM all_indexes i
            WHERE i.owner = 'ORACLE_ENHANCED'
               AND i.table_owner = 'ORACLE_ENHANCED'
               AND i.table_name = 'ORG_UNITS'
               AND i.index_name = 'INDEX_ORG_UNITS_ON_ANCESTRY'

D, [2015-07-30T11:58:47.341264 #8901] DEBUG -- :    (4.6ms)  CREATE  INDEX "INDEX_ORG_UNITS_ON_ANCESTRY" ON "ORG_UNITS" ("ANCESTRY")
   -> 0.0060s
-- add_index("org_units", ["ancestry_depth"], {:name=>"i_org_units_ancestry_depth"})
W, [2015-07-30T11:58:47.341451 #8901]  WARN -- : OracleEnhanced shortened default index name index_org_units_on_ancestry_depth to i_org_units_ancestry_depth
D, [2015-07-30T11:58:47.342510 #8901] DEBUG -- :    (0.6ms)              SELECT 1 FROM all_indexes i
            WHERE i.owner = 'ORACLE_ENHANCED'
               AND i.table_owner = 'ORACLE_ENHANCED'
               AND i.table_name = 'ORG_UNITS'
               AND i.index_name = 'I_ORG_UNITS_ANCESTRY_DEPTH'

D, [2015-07-30T11:58:47.347031 #8901] DEBUG -- :    (4.3ms)  CREATE  INDEX "I_ORG_UNITS_ANCESTRY_DEPTH" ON "ORG_UNITS" ("ANCESTRY_DEPTH")
   -> 0.0057s
D, [2015-07-30T11:58:47.403339 #8901] DEBUG -- :    (41.5ms)  SELECT DECODE(table_name, UPPER(table_name), LOWER(table_name), table_name) FROM all_tables WHERE owner = SYS_CONTEXT('userenv', 'session_user') AND secondary = 'N'
D, [2015-07-30T11:58:47.404548 #8901] DEBUG -- :   Sequence (0.5ms)  select us.sequence_name from all_sequences us where us.sequence_owner = 'ORACLE_ENHANCED' and us.sequence_name = 'ORG_UNITS_SEQ'
D, [2015-07-30T11:58:47.406361 #8901] DEBUG -- :   Primary Key (1.7ms)  SELECT cc.column_name FROM all_constraints c, all_cons_columns cc WHERE c.owner = 'ORACLE_ENHANCED' AND c.table_name = 'ORG_UNITS' AND c.constraint_type = 'P' AND cc.owner = c.owner AND cc.constraint_name = c.constraint_name
/home/yahonda/.rvm/gems/ruby-2.2.2@rails42/gems/activerecord-4.2.3/lib/active_record/relation/delegation.rb:136:in `method_missing'D, [2015-07-30T11:58:47.416293 #8901] DEBUG -- :   Department Load (7.9ms)  SELECT "DEPARTMENTS".* FROM "DEPARTMENTS" INNER JOIN "ORG_UNITS" ON "ORG_UNITS"."ID" = "DEPARTMENTS"."ORG_UNIT_ID"
D, [2015-07-30T11:58:47.420827 #8901] DEBUG -- :    (3.4ms)  SELECT column_name AS name, data_type AS sql_type, data_default, nullable, virtual_column, hidden_column, data_type_owner AS sql_type_owner, DECODE(data_type, 'NUMBER', data_precision, 'FLOAT', data_precision, 'VARCHAR2', DECODE(char_used, 'C', char_length, data_length), 'RAW', DECODE(char_used, 'C', char_length, data_length), 'CHAR', DECODE(char_used, 'C', char_length, data_length), NULL) AS limit, DECODE(data_type, 'NUMBER', data_scale, NULL) AS scale FROM all_tab_cols WHERE owner = 'ORACLE_ENHANCED' AND table_name = 'DEPARTMENTS' AND hidden_column = 'NO' ORDER BY column_id
: undefined method `text_search' for #<ActiveRecord::Relation []> (NoMethodError)
        from /home/yahonda/.rvm/gems/ruby-2.2.2@rails42/gems/activerecord-4.2.3/lib/active_record/relation/delegation.rb:99:in `method_missing'
        from test.rb:77:in `<main>'
yahonda commented 8 years ago

Closing this since no feedback about text_search definition.

Dharshi commented 8 years ago

Hi Yahonda,

I updated my answer on July 30 with the text_search. Please find it below as well.

***searchable.rb

module RailsApp  
  module Searchable
    def self.extended(model)
      model.has_context_index
    end

    def text_search(column, query, options = {})
      text_search_query column, query, [TEXT_OR_STATEMENT, OR_STATEMENT], options
    end

    private

    TEXT_OR_STATEMENT = ' | '
    TEXT_AND_STATEMENT = ' & '
    OR_STATEMENT = ' OR '
    AND_STATEMENT = ' AND '

    def text_search_query(column, query, statement, options = {})
      columns = Array(column)
      queries = Array(query)

      if queries.length > 1
        text_search_multiple_queries columns, queries, statement, options
      else
        text_search_single_query columns, queries.first, statement.last, options
      end
    end

    def text_search_single_query(columns, query, statement, options = {})
      if RailsApp::Configuration.instance.oracle_text?
        contains(columns.first, query, options)
      else
        where(columns.map { |c| "LOWER(#{c}) LIKE LOWER(:query)" }.join(statement), query: query)
      end
    end

    def text_search_multiple_queries(columns, queries, statement, options = {})
      if RailsApp::Configuration.instance.oracle_text?
        contains(columns.first, queries.join(statement.first), options)
      else
        default_text_search_multiple_queries(columns, queries, statement)
      end
    end

    def default_text_search_multiple_queries(columns, queries, statement)
      where(columns.map { |c|
              queries.map { |q|
                "LOWER(#{c}) LIKE LOWER(#{connection.quote(q)})"
              }.join(statement.last)
            }.join(statement.last))
    end
  end
end
yahonda commented 8 years ago

Thanks for the update. I was not aware of your previous update.

Now I am able to reproduce with Rails 4.2 but also it reproduces with these versions that you said it worked.

ActiveRecord 4.1.0
Oracle enhanced adapter 1.5.5
ruby-oci 8 2.1.7

Please make sure that your code have been working at previous version of Rails and/or Oracle enhanced adapter. 


2.2.3@rails41 [~/work/rep664_rails41]$ ruby test.rb
Fetching gem metadata from https://rubygems.org/.......
Fetching version metadata from https://rubygems.org/..
Resolving dependencies...
Using i18n 0.7.0
Using json 1.8.3
Using minitest 5.8.1
Using thread_safe 0.3.5
Using tzinfo 1.2.2
Using activesupport 4.1.0
Using builder 3.2.2
Using activemodel 4.1.0
Using arel 5.0.1.20140414130214
Using activerecord 4.1.0
Using activerecord-oracle_enhanced-adapter 1.5.5
Using ruby-oci8 2.1.7
Using bundler 1.10.6
/home/yahonda/.rvm/gems/ruby-2.2.3@rails41/gems/activesupport-4.1.0/lib/active_support/values/time_zone.rb:285: warning: circular argument reference - now
-- create_table("departments", {:force=>true})
D, [2015-10-13T05:44:23.503890 #23652] DEBUG -- :    (657.1ms)  DROP TABLE "DEPARTMENTS"
D, [2015-10-13T05:44:23.550385 #23652] DEBUG -- :    (45.9ms)  DROP SEQUENCE "DEPARTMENTS_SEQ"
D, [2015-10-13T05:44:23.652934 #23652] DEBUG -- :    (101.2ms)  CREATE TABLE "DEPARTMENTS" ("ID" NUMBER(38) NOT NULL PRIMARY KEY, "NAME" VARCHAR2(255) NOT NULL, "YEAR_ID" NUMBER(38) NOT NULL, "CREATED_AT" DATE, "UPDATED_AT" DATE, "ORG_UNIT_ID" NUMBER(38))
D, [2015-10-13T05:44:23.689193 #23652] DEBUG -- :    (35.7ms)  CREATE SEQUENCE "DEPARTMENTS_SEQ" START WITH 10000
   -> 0.9059s
-- add_context_index("departments", ["name"], {:name=>"i_dep_context_name", :sync=>"ON COMMIT"})
D, [2015-10-13T05:44:23.934875 #23652] DEBUG -- :    (244.8ms)  CREATE INDEX "I_DEP_CONTEXT_NAME" ON "DEPARTMENTS" ("NAME") INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ('SYNC(ON COMMIT)')
   -> 0.2453s
   -> 0 rows
-- add_index("departments", ["name", "year_id"], {:name=>"i_dep_nam_yea_id", :unique=>true})
W, [2015-10-13T05:44:23.935276 #23652]  WARN -- : OracleEnhanced shortened default index name index_departments_on_name_and_year_id to i_departments_name_year_id
D, [2015-10-13T05:44:23.964908 #23652] DEBUG -- :    (3.1ms)            SELECT 1 FROM all_indexes i
          WHERE i.owner = 'ORACLE_ENHANCED'
             AND i.table_owner = 'ORACLE_ENHANCED'
             AND i.table_name = 'DEPARTMENTS'
             AND i.index_name = 'I_DEP_NAM_YEA_ID'

D, [2015-10-13T05:44:24.014387 #23652] DEBUG -- :    (49.0ms)  CREATE UNIQUE INDEX "I_DEP_NAM_YEA_ID" ON "DEPARTMENTS" ("NAME", "YEAR_ID")
   -> 0.0795s
   -> 0 rows
-- add_index("departments", ["name"], {:name=>"i_departments_name"})
D, [2015-10-13T05:44:24.017985 #23652] DEBUG -- :    (1.6ms)            SELECT 1 FROM all_indexes i
          WHERE i.owner = 'ORACLE_ENHANCED'
             AND i.table_owner = 'ORACLE_ENHANCED'
             AND i.table_name = 'DEPARTMENTS'
             AND i.index_name = 'I_DEPARTMENTS_NAME'

D, [2015-10-13T05:44:24.044096 #23652] DEBUG -- :    (25.7ms)  CREATE  INDEX "I_DEPARTMENTS_NAME" ON "DEPARTMENTS" ("NAME")
   -> 0.0295s
   -> 0 rows
-- add_index("departments", ["org_unit_id"], {:name=>"i_departments_org_unit_id"})
W, [2015-10-13T05:44:24.044668 #23652]  WARN -- : OracleEnhanced shortened default index name index_departments_on_org_unit_id to i_departments_org_unit_id
D, [2015-10-13T05:44:24.047128 #23652] DEBUG -- :    (1.4ms)            SELECT 1 FROM all_indexes i
          WHERE i.owner = 'ORACLE_ENHANCED'
             AND i.table_owner = 'ORACLE_ENHANCED'
             AND i.table_name = 'DEPARTMENTS'
             AND i.index_name = 'I_DEPARTMENTS_ORG_UNIT_ID'

D, [2015-10-13T05:44:24.069328 #23652] DEBUG -- :    (21.8ms)  CREATE  INDEX "I_DEPARTMENTS_ORG_UNIT_ID" ON "DEPARTMENTS" ("ORG_UNIT_ID")
   -> 0.0250s
   -> 0 rows
-- create_table("org_units", {:force=>true})
D, [2015-10-13T05:44:24.119556 #23652] DEBUG -- :    (48.4ms)  DROP TABLE "ORG_UNITS"
D, [2015-10-13T05:44:24.167495 #23652] DEBUG -- :    (47.3ms)  DROP SEQUENCE "ORG_UNITS_SEQ"
D, [2015-10-13T05:44:24.212874 #23652] DEBUG -- :    (40.7ms)  CREATE TABLE "ORG_UNITS" ("ID" NUMBER(38) NOT NULL PRIMARY KEY, "NAME" VARCHAR2(255), "CREATED_AT" DATE NOT NULL, "UPDATED_AT" DATE NOT NULL, "ANCESTRY" VARCHAR2(255), "ANCESTRY_DEPTH" NUMBER(38) DEFAULT 0)
D, [2015-10-13T05:44:24.267350 #23652] DEBUG -- :    (53.6ms)  CREATE SEQUENCE "ORG_UNITS_SEQ" START WITH 10000
   -> 0.1979s
-- add_index("org_units", ["ancestry"], {:name=>"index_org_units_on_ancestry"})
D, [2015-10-13T05:44:24.271166 #23652] DEBUG -- :    (1.4ms)            SELECT 1 FROM all_indexes i
          WHERE i.owner = 'ORACLE_ENHANCED'
             AND i.table_owner = 'ORACLE_ENHANCED'
             AND i.table_name = 'ORG_UNITS'
             AND i.index_name = 'INDEX_ORG_UNITS_ON_ANCESTRY'

D, [2015-10-13T05:44:24.312533 #23652] DEBUG -- :    (40.9ms)  CREATE  INDEX "INDEX_ORG_UNITS_ON_ANCESTRY" ON "ORG_UNITS" ("ANCESTRY")
   -> 0.0450s
   -> 0 rows
-- add_index("org_units", ["ancestry_depth"], {:name=>"i_org_units_ancestry_depth"})
W, [2015-10-13T05:44:24.313023 #23652]  WARN -- : OracleEnhanced shortened default index name index_org_units_on_ancestry_depth to i_org_units_ancestry_depth
D, [2015-10-13T05:44:24.315923 #23652] DEBUG -- :    (1.7ms)            SELECT 1 FROM all_indexes i
          WHERE i.owner = 'ORACLE_ENHANCED'
             AND i.table_owner = 'ORACLE_ENHANCED'
             AND i.table_name = 'ORG_UNITS'
             AND i.index_name = 'I_ORG_UNITS_ANCESTRY_DEPTH'

D, [2015-10-13T05:44:24.381851 #23652] DEBUG -- :    (65.5ms)  CREATE  INDEX "I_ORG_UNITS_ANCESTRY_DEPTH" ON "ORG_UNITS" ("ANCESTRY_DEPTH")
   -> 0.0691s
   -> 0 rows
D, [2015-10-13T05:44:24.412567 #23652] DEBUG -- :   Sequence (1.5ms)  select us.sequence_name from all_sequences us where us.sequence_owner = 'ORACLE_ENHANCED' and us.sequence_name = 'ORG_UNITS_SEQ'
D, [2015-10-13T05:44:24.415495 #23652] DEBUG -- :   Primary Key (2.5ms)  SELECT cc.column_name FROM all_constraints c, all_cons_columns cc WHERE c.owner = 'ORACLE_ENHANCED' AND c.table_name = 'ORG_UNITS' AND c.constraint_type = 'P' AND cc.owner = c.owner AND cc.constraint_name = c.constraint_name
D, [2015-10-13T05:44:24.803330 #23652] DEBUG -- :    (386.1ms)  SELECT column_name AS name, data_type AS sql_type, data_default, nullable, virtual_column, hidden_column, data_type_owner AS sql_type_owner, DECODE(data_type, 'NUMBER', data_precision, 'FLOAT', data_precision, 'VARCHAR2', DECODE(char_used, 'C', char_length, data_length), 'RAW', DECODE(char_used, 'C', char_length, data_length), 'CHAR', DECODE(char_used, 'C', char_length, data_length), NULL) AS limit, DECODE(data_type, 'NUMBER', data_scale, NULL) AS scale FROM all_tab_cols WHERE owner = 'ORACLE_ENHANCED' AND table_name = 'DEPARTMENTS' AND hidden_column = 'NO' ORDER BY column_id
D, [2015-10-13T05:44:24.807236 #23652] DEBUG -- :   Sequence (1.3ms)  select us.sequence_name from all_sequences us where us.sequence_owner = 'ORACLE_ENHANCED' and us.sequence_name = 'DEPARTMENTS_SEQ'
D, [2015-10-13T05:44:24.809405 #23652] DEBUG -- :   Primary Key (1.9ms)  SELECT cc.column_name FROM all_constraints c, all_cons_columns cc WHERE c.owner = 'ORACLE_ENHANCED' AND c.table_name = 'DEPARTMENTS' AND c.constraint_type = 'P' AND cc.owner = c.owner AND cc.constraint_name = c.constraint_name
D, [2015-10-13T05:44:24.816097 #23652] DEBUG -- :   Department Load (4.8ms)  SELECT departments.name FROM "DEPARTMENTS" INNER JOIN "ORG_UNITS" ON "ORG_UNITS"."ID" = "DEPARTMENTS"."ORG_UNIT_ID" WHERE (CONTAINS("departments.name", 'xyz', 0) > 0)  ORDER BY SCORE(0) DESC, departments.name
E, [2015-10-13T05:44:24.816270 #23652] ERROR -- : OCIError: ORA-00904: "departments.name": invalid identifier: SELECT departments.name FROM "DEPARTMENTS" INNER JOIN "ORG_UNITS" ON "ORG_UNITS"."ID" = "DEPARTMENTS"."ORG_UNIT_ID" WHERE (CONTAINS("departments.name", 'xyz', 0) > 0)  ORDER BY SCORE(0) DESC, departments.name
stmt.c:230:in oci8lib_220.so: OCIError: ORA-00904: "departments.name": invalid identifier: SELECT departments.name FROM "DEPARTMENTS" INNER JOIN "ORG_UNITS" ON "ORG_UNITS"."ID" = "DEPARTMENTS"."ORG_UNIT_ID" WHERE (CONTAINS("departments.name", 'xyz', 0) > 0)  ORDER BY SCORE(0) DESC, departments.name (ActiveRecord::StatementInvalid)
        from /home/yahonda/.rvm/gems/ruby-2.2.3@rails41/gems/ruby-oci8-2.1.7/lib/oci8/cursor.rb:126:in `exec'
        from /home/yahonda/.rvm/gems/ruby-2.2.3@rails41/gems/activerecord-oracle_enhanced-adapter-1.5.5/lib/active_record/connection_adapters/oracle_enhanced_oci_connection.rb:149:in `exec'
        from /home/yahonda/.rvm/gems/ruby-2.2.3@rails41/gems/activerecord-oracle_enhanced-adapter-1.5.5/lib/active_record/connection_adapters/oracle_enhanced_adapter.rb:772:in `block in exec_query'
        from /home/yahonda/.rvm/gems/ruby-2.2.3@rails41/gems/activerecord-4.1.0/lib/active_record/connection_adapters/abstract_adapter.rb:373:in `block in log'
        from /home/yahonda/.rvm/gems/ruby-2.2.3@rails41/gems/activesupport-4.1.0/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
        from /home/yahonda/.rvm/gems/ruby-2.2.3@rails41/gems/activerecord-4.1.0/lib/active_record/connection_adapters/abstract_adapter.rb:367:in `log'
        from /home/yahonda/.rvm/gems/ruby-2.2.3@rails41/gems/activerecord-oracle_enhanced-adapter-1.5.5/lib/active_record/connection_adapters/oracle_enhanced_adapter.rb:1510:in `log'
        from /home/yahonda/.rvm/gems/ruby-2.2.3@rails41/gems/activerecord-oracle_enhanced-adapter-1.5.5/lib/active_record/connection_adapters/oracle_enhanced_adapter.rb:752:in `exec_query'
        from /home/yahonda/.rvm/gems/ruby-2.2.3@rails41/gems/activerecord-oracle_enhanced-adapter-1.5.5/lib/active_record/connection_adapters/oracle_enhanced_adapter.rb:1464:in `select'
        from /home/yahonda/.rvm/gems/ruby-2.2.3@rails41/gems/activerecord-4.1.0/lib/active_record/connection_adapters/abstract/database_statements.rb:31:in `select_all'
        from /home/yahonda/.rvm/gems/ruby-2.2.3@rails41/gems/activerecord-4.1.0/lib/active_record/connection_adapters/abstract/query_cache.rb:69:in `select_all'
        from /home/yahonda/.rvm/gems/ruby-2.2.3@rails41/gems/activerecord-4.1.0/lib/active_record/querying.rb:39:in `find_by_sql'
        from /home/yahonda/.rvm/gems/ruby-2.2.3@rails41/gems/activerecord-4.1.0/lib/active_record/relation.rb:603:in `exec_queries'
        from /home/yahonda/.rvm/gems/ruby-2.2.3@rails41/gems/activerecord-4.1.0/lib/active_record/relation.rb:487:in `load'
        from /home/yahonda/.rvm/gems/ruby-2.2.3@rails41/gems/activerecord-4.1.0/lib/active_record/relation.rb:231:in `to_a'
        from /home/yahonda/.rvm/gems/ruby-2.2.3@rails41/gems/activerecord-4.1.0/lib/active_record/relation/delegation.rb:46:in `map'
        from test.rb:151:in `<main>'
2.2.3@rails41 [~/work/rep664_rails41]$
2.2.3@rails41 [~/work/rep664_rails41]$ more test.rb

begin
  require 'bundler/inline'
rescue LoadError => e
  $stderr.puts 'Bundler version 1.10 or later is required. Please update your Bundler'
  raise e
end

gemfile(true) do
  source 'https://rubygems.org'
  gem 'activerecord', '4.1.0'
  gem 'activerecord-oracle_enhanced-adapter','1.5.5'
  gem 'ruby-oci8', '2.1.7'
  gem 'minitest'
end

require 'active_record'
require 'minitest/autorun'
require 'logger'

Minitest::Test = MiniTest::Unit::TestCase unless defined?(Minitest::Test)

DATABASE_NAME         = ENV['DATABASE_NAME']         || 'orcl'
DATABASE_HOST         = ENV['DATABASE_HOST']         || "127.0.0.1"
DATABASE_PORT         = ENV['DATABASE_PORT']         || 1521
DATABASE_USER         = ENV['DATABASE_USER']         || 'oracle_enhanced'
DATABASE_PASSWORD     = ENV['DATABASE_PASSWORD']     || 'oracle_enhanced'
DATABASE_SYS_PASSWORD = ENV['DATABASE_SYS_PASSWORD'] || 'admin'

CONNECTION_PARAMS = {
  :adapter => "oracle_enhanced",
  :database => DATABASE_NAME,
  :host => DATABASE_HOST,
  :port => DATABASE_PORT,
  :username => DATABASE_USER,
  :password => DATABASE_PASSWORD
}

ActiveRecord::Base.establish_connection(CONNECTION_PARAMS)
ActiveRecord::Base.logger = Logger.new(STDOUT)

ActiveRecord::Schema.define do
  create_table "departments", force: true do |t|
    t.string   "name",  null: false
    t.integer  "year_id",  precision: 38, scale: 0,  null: false
    t.datetime "created_at"
    t.datetime "updated_at"
    t.integer  "org_unit_id",  precision: 38, scale: 0
  end

  add_context_index "departments", ["name"], name: "i_dep_context_name", sync: "ON COMMIT"
  add_index "departments", ["name", "year_id"], name: "i_dep_nam_yea_id", unique: true
  add_index "departments", ["name"], name: "i_departments_name"
  add_index "departments", ["org_unit_id"], name: "i_departments_org_unit_id"

  create_table "org_units", force: true do |t|
    t.string   "name"
    t.datetime "created_at",  null: false
    t.datetime "updated_at",  null: false
    t.string   "ancestry"
    t.integer  "ancestry_depth",      precision: 38, scale: 0, default: 0
  end

  add_index "org_units", ["ancestry"], name: "index_org_units_on_ancestry"
  add_index "org_units", ["ancestry_depth"], name: "i_org_units_ancestry_depth"
end

require 'singleton'

module RailsApp
  class Configuration
    include Singleton

    def initialize
      @config = YAML.load(ERB.new(File.read("#{Rails.root}/config/app_config.yml")).result)[Rails.env]
    end

    def oracle_text?
      config['has_oracle_text']
    end

    def oracle_enterprise?
      config['oracle_enterprise']
    end

    private

    attr_reader :config

    def url(url)
      if port
        "#{url}:#{port}"
      else
        url
      end
    end

    def port
      Capybara.server_port if defined?(Capybara) && Capybara.server_port.present?
    end
  end
end

module RailsApp
  module Searchable
    def self.extended(model)
      model.has_context_index
    end

    def text_search(column, query, options = {})
      text_search_query column, query, [TEXT_OR_STATEMENT, OR_STATEMENT], options
    end

    TEXT_OR_STATEMENT = ' | '
    TEXT_AND_STATEMENT = ' & '
    OR_STATEMENT = ' OR '
    AND_STATEMENT = ' AND '

    def text_search_query(column, query, statement, options = {})
      columns = Array(column)
      queries = Array(query)
      if queries.length > 1
        text_search_multiple_queries columns, queries, statement, options
      else
        text_search_single_query columns, queries.first, statement.last, options
      end
    end

    def text_search_single_query(columns, query, statement, options = {})
      contains(columns.first, query, options)
    end

    def text_search_multiple_queries(columns, queries, statement, options = {})
      contains(columns.first, queries.join(statement.first), options)
    end
  end
end
class Department < ActiveRecord::Base
  extend RailsApp::Searchable

  has_context_index
  belongs_to :org_unit
end

class OrgUnit < ActiveRecord::Base
  has_many :departments
end

Department.joins(:org_unit).text_search('departments.name', 'xyz').order('departments.name').select('departments.name').map(&:name).uniq

$
yahonda commented 8 years ago

Looks like it may be duplicate with #463. Then just create a test branch.

Would you give it a try by adding this entry to your application Gemfile.

gem 'activerecord-oracle_enhanced-adapter', github: 'yahonda/oracle-enhanced', branch: 'rep664'
carlosbaraza commented 8 years ago

:+1:

I tried gem 'activerecord-oracle_enhanced-adapter', github: 'yahonda/oracle-enhanced', branch: 'rep664' and still is not working.

Thanks.

yahonda commented 8 years ago

@carlosbaraza Thanks for the update. Would you update your own test case since this test finishes successfully in my environment. There may be some other test cases that is not included in 'rep664' branch.

begin
  require 'bundler/inline'
rescue LoadError => e
  $stderr.puts 'Bundler version 1.10 or later is required. Please update your Bundler'
  raise e
end

gemfile(true) do
  source 'https://rubygems.org'
  gem 'activerecord', '4.2.4'
  gem 'activerecord-oracle_enhanced-adapter', github: 'yahonda/oracle-enhanced', branch: 'rep664'
  gem 'ruby-oci8'
  gem 'minitest'
end

require 'active_record'
require 'minitest/autorun'
require 'logger'

Minitest::Test = MiniTest::Unit::TestCase unless defined?(Minitest::Test)

DATABASE_NAME         = ENV['DATABASE_NAME']         || 'orcl'
DATABASE_HOST         = ENV['DATABASE_HOST']         || "127.0.0.1"
DATABASE_PORT         = ENV['DATABASE_PORT']         || 1521
DATABASE_USER         = ENV['DATABASE_USER']         || 'oracle_enhanced'
DATABASE_PASSWORD     = ENV['DATABASE_PASSWORD']     || 'oracle_enhanced'
DATABASE_SYS_PASSWORD = ENV['DATABASE_SYS_PASSWORD'] || 'admin'

CONNECTION_PARAMS = {
  :adapter => "oracle_enhanced",
  :database => DATABASE_NAME,
  :host => DATABASE_HOST,
  :port => DATABASE_PORT,
  :username => DATABASE_USER,
  :password => DATABASE_PASSWORD
}

ActiveRecord::Base.establish_connection(CONNECTION_PARAMS)
ActiveRecord::Base.logger = Logger.new(STDOUT)

ActiveRecord::Schema.define do
  create_table "departments", force: true do |t|
    t.string   "name",  null: false
    t.integer  "year_id",  precision: 38, scale: 0,  null: false
    t.datetime "created_at"
    t.datetime "updated_at"
    t.integer  "org_unit_id",  precision: 38, scale: 0
  end

  add_context_index "departments", ["name"], name: "i_dep_context_name", sync: "ON COMMIT"
  add_index "departments", ["name", "year_id"], name: "i_dep_nam_yea_id", unique: true
  add_index "departments", ["name"], name: "i_departments_name"
  add_index "departments", ["org_unit_id"], name: "i_departments_org_unit_id"

  create_table "org_units", force: true do |t|
    t.string   "name"
    t.datetime "created_at",  null: false
    t.datetime "updated_at",  null: false
    t.string   "ancestry"
    t.integer  "ancestry_depth",      precision: 38, scale: 0, default: 0
  end

  add_index "org_units", ["ancestry"], name: "index_org_units_on_ancestry"
  add_index "org_units", ["ancestry_depth"], name: "i_org_units_ancestry_depth"
end

require 'singleton'

module RailsApp
  class Configuration
    include Singleton

    def initialize
      @config = YAML.load(ERB.new(File.read("#{Rails.root}/config/app_config.yml")).result)[Rails.env]
    end

    def oracle_text?
      config['has_oracle_text']
    end

    def oracle_enterprise?
      config['oracle_enterprise']
    end

    private

    attr_reader :config

    def url(url)
      if port
        "#{url}:#{port}"
      else
        url
      end
    end

    def port
      Capybara.server_port if defined?(Capybara) && Capybara.server_port.present?
    end
  end
end

module RailsApp  
  module Searchable
    def self.extended(model)
      model.has_context_index
    end

    def text_search(column, query, options = {})
      text_search_query column, query, [TEXT_OR_STATEMENT, OR_STATEMENT], options
    end

    TEXT_OR_STATEMENT = ' | '
    TEXT_AND_STATEMENT = ' & '
    OR_STATEMENT = ' OR '
    AND_STATEMENT = ' AND '

    def text_search_query(column, query, statement, options = {})
      columns = Array(column)
      queries = Array(query)
      if queries.length > 1
        text_search_multiple_queries columns, queries, statement, options
      else
        text_search_single_query columns, queries.first, statement.last, options
      end
    end

    def text_search_single_query(columns, query, statement, options = {})
      contains(columns.first, query, options)
    end

    def text_search_multiple_queries(columns, queries, statement, options = {})
      contains(columns.first, queries.join(statement.first), options)
    end
  end
end
class Department < ActiveRecord::Base
  extend RailsApp::Searchable

  has_context_index
  belongs_to :org_unit
end

class OrgUnit < ActiveRecord::Base
  has_many :departments
end

Department.joins(:org_unit).text_search('departments.name', 'xyz').order('departments.name').select('departments.name').map(&:name).uniq
carlosbaraza commented 8 years ago

Hi @yahonda,

I have researched a bit more and it was my mistake because I was trying to create an index on a column that was not existing. I had mapped the keys of a serialised (JSON) column with accessors and I thought they were actual columns in the DB. My fault.

Thanks though for your response!

yahonda commented 8 years ago

@carlosbaraza Thanks for the update.

yahonda commented 8 years ago

I've found a better way to fix. Since quote_table_name supports "schema_name.table_name" quoting, which can be used for this query "table_name.column_name", which is kind of confusing but make sense to address this issue.

https://github.com/yahonda/oracle-enhanced/commit/0448a066923e4bab1facfd1b4286ad17c77ef855

yahonda commented 8 years ago

Closed as #758 has been merged to master. Thanks for reporting with useful test case.