Skip to content

Embed URL

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP
Study of AR select + include
# Patch fixes issue on oracle.
class ActiveRecord::Base
NONQUOTED_OBJECT_NAME = /[A-Za-z][A-z0-9$#]{0,29}/
NONQUOTED_DATABASE_LINK = /[A-Za-z][A-z0-9$#\.@]{0,127}/
TABLES_IN_STRING = /((?:#{NONQUOTED_OBJECT_NAME}\.)?#{NONQUOTED_OBJECT_NAME}(?:@#{NONQUOTED_DATABASE_LINK})?)\..?/
def self.tables_in_string(string)
return [] if string.blank?
# string.scan(/([\.a-zA-Z_]+).?\./).flatten
string.scan(TABLES_IN_STRING).flatten.map {|str| str.downcase }.uniq - ['raw_sql_']
end
end
# This is a study of how ActiveRecord deals with select + include (ie eager
# loading with limits on what data gets returned) when running on Oracle. The
# tests must be run in a folder with a standard config/database.yml file.
#
# % RAILS_ENV=test ruby select_with_include_test.rb
#
# These tests reveal several setups where AR behaves inconsistently, or
# changes its behavior entirely when you fully qualify table names with a
# schema. The two especially troubling findings are:
#
# * schemas that contain non-word (but legal) characters can break AR even
# though the adapter and oracle allow them
# * fully qualified table names break eager loading of associations that have
# a select, if in conjuction with find by primary key (but not :all or
# :first)
#
# Both of these are ultimately caused by eager loading reverting to the 'left
# outer join' strategy rather than using a one-query-per-association strategy
# to load data.
#
# == error 1
#
# The specific culprit in the first error is this method which extracts table
# names from all the select, condition, join statements.
#
# [active_record/associations.rb]
# def tables_in_string(string)
# return [] if string.blank?
# string.scan(/([\.a-zA-Z_]+).?\./).flatten
# end
#
# As you can see non-word characters will not be extracted as a part of the
# table name, and hence schemas like ops$user will be left out. The extracted
# table names are used to determine if the left outer join strategy is needed
# (see the references_eager_loaded_tables? method). If the schema is not
# extracted then the reversion can be mistakenly triggered.
#
# == error 2
#
# Has to do with the same method as above, plus a case issue.
#
##############################################################################
# Setup Notes
#
# * SCHEMA MUST be downcase or everything breaks (I don't know why)
# * The assertions check that a 'nombre' method is available on a record. The
# nombre method will be set if the select statement is actually used in the
# query -- the queries rename one existing column as nombre.
# * Wrap some statements in with_logger to print the SQL to STDERR
#
###############################################################################
require 'rubygems'
gem 'activerecord', '= 2.3.5'
require 'test/unit'
require 'active_record'
env = ENV['RAILS_ENV'] || 'test'
config_file = File.expand_path('../config/database.yml', __FILE__)
configs = YAML.load_file(config_file)[env]
ActiveRecord::Base.logger = Logger.new(STDERR)
ActiveRecord::Base.logger.level = Logger::FATAL
ActiveRecord::Base.establish_connection(configs)
SCHEMA = configs['username'].downcase
class Test::Unit::TestCase
def with_logger
ActiveRecord::Base.logger.level = Logger::DEBUG
yield
ensure
ActiveRecord::Base.logger.level = Logger::FATAL
end
def assert_select_used(obj)
assert_equal true, obj.respond_to?(:nombre)
end
def assert_select_not_used(obj)
assert_equal false, obj.respond_to?(:nombre)
end
end
##############################################################################
# Tests
###############################################################################
class SelectOnFindWithIncludeTest < Test::Unit::TestCase
#
# test select on find
#
class Dept < ActiveRecord::Base
set_table_name 'dept'
end
class Emp < ActiveRecord::Base
set_table_name 'emp'
belongs_to :dept
end
class SchemaEmp < ActiveRecord::Base
set_table_name "#{SCHEMA}.emp"
belongs_to :dept
end
def test_eager_loading_with_select_works_if_columns_are_unqualified
assert_select_used Emp.find(:first, :select => 'first_name nombre, dept_id', :include => :dept)
assert_select_used SchemaEmp.find(:first, :select => 'first_name nombre, dept_id', :include => :dept)
end
# This is a trouble case, but easy to avoid (get your code right)
def test_eager_loading_with_select_does_not_work_if_columns_are_partially_qualified
assert_select_not_used SchemaEmp.find(:first, :select => "emp.first_name nombre, emp.dept_id", :include => :dept)
end
# This is the BIG trouble case (fails for ops$)
def test_eager_loading_with_select_works_if_columns_are_fully_qualified
assert_select_used Emp.find(:first, :select => 'emp.first_name nombre, emp.dept_id', :include => :dept)
assert_select_used SchemaEmp.find(:first, :select => "#{SCHEMA}.emp.first_name nombre, #{SCHEMA}.emp.dept_id", :include => :dept)
end
# Note the inconsistency -- join is ok with partial qualify
def test_eager_loading_with_select_works_with_partially_qualified_join
assert_select_used Emp.find(:first,
:select => "job.name nombre, emp.dept_id",
:joins => "join job on job.id = emp.job_id",
:include => :dept)
assert_select_used SchemaEmp.find(:first,
:select => "job.name nombre, emp.dept_id",
:joins => "join job on job.id = emp.job_id",
:include => :dept)
end
def test_eager_loading_with_select_works_with_fully_qualified_join
assert_select_used SchemaEmp.find(:first,
:select => "#{SCHEMA}.job.name nombre, #{SCHEMA}.emp.dept_id",
:joins => "join #{SCHEMA}.job on #{SCHEMA}.job.id = #{SCHEMA}.emp.job_id",
:include => :dept)
end
end
class SelectOnAssocWithIncludeTest < Test::Unit::TestCase
#
# test belongs_to with select
#
class Dept < ActiveRecord::Base
set_table_name 'dept'
end
class Emp < ActiveRecord::Base
set_table_name 'emp'
belongs_to :dept, :select => 'id, name nombre'
end
class SchemaDept < ActiveRecord::Base
set_table_name "#{SCHEMA}.dept"
end
class SchemaEmp < ActiveRecord::Base
set_table_name "#{SCHEMA}.emp"
belongs_to :dept, :class_name => 'SelectOnAssocWithIncludeTest::SchemaDept', :foreign_key => 'dept_id', :select => "#{SCHEMA}.dept.id, #{SCHEMA}.dept.name nombre"
end
class PartialSchemaEmp < ActiveRecord::Base
set_table_name "#{SCHEMA}.emp"
belongs_to :dept, :class_name => 'SelectOnAssocWithIncludeTest::SchemaDept', :foreign_key => 'dept_id', :select => "dept.id, dept.name nombre"
end
class UnqualifiedSchemaEmp < ActiveRecord::Base
set_table_name "#{SCHEMA}.emp"
belongs_to :dept, :class_name => 'SelectOnAssocWithIncludeTest::SchemaDept', :foreign_key => 'dept_id', :select => "id, name nombre"
end
def test_lazy_loading_select_on_bt_assoc_works_if_columns_are_fully_qualified
assert_select_used Emp.find(:all).first.dept
assert_select_used SchemaEmp.find(:all).first.dept
assert_select_used PartialSchemaEmp.find(:all).first.dept
assert_select_used UnqualifiedSchemaEmp.find(:all).first.dept
assert_select_used Emp.find(:first).dept
assert_select_used SchemaEmp.find(:first).dept
assert_select_used PartialSchemaEmp.find(:first).dept
assert_select_used UnqualifiedSchemaEmp.find(:first).dept
assert_select_used Emp.find(1).dept
assert_select_used SchemaEmp.find(1).dept
assert_select_used PartialSchemaEmp.find(1).dept
assert_select_used UnqualifiedSchemaEmp.find(1).dept
end
def test_eager_loading_select_on_bt_assoc_works_if_columns_are_fully_qualified
assert_select_used Emp.find(:all, :include => :dept).first.dept
assert_select_used SchemaEmp.find(:all, :include => :dept).first.dept
assert_select_used PartialSchemaEmp.find(:all, :include => :dept).first.dept
assert_select_used UnqualifiedSchemaEmp.find(:all, :include => :dept).first.dept
assert_select_used Emp.find(:first, :include => :dept).dept
assert_select_used SchemaEmp.find(:first, :include => :dept).dept
assert_select_used PartialSchemaEmp.find(:first, :include => :dept).dept
assert_select_used UnqualifiedSchemaEmp.find(:first, :include => :dept).dept
# BIG trouble -- find by id bombs for the last three
assert_select_used Emp.find(1, :include => :dept).dept
assert_select_used SchemaEmp.find(1, :include => :dept).dept
assert_select_used PartialSchemaEmp.find(1, :include => :dept).dept
assert_select_used UnqualifiedSchemaEmp.find(1, :include => :dept).dept
end
class Job < ActiveRecord::Base
set_table_name 'job'
has_many :emps
has_many :depts, :through => :emps, :select => 'dept.id, dept.name nombre'
end
#
# test has_many through with select
#
class SchemaJob < ActiveRecord::Base
set_table_name "#{SCHEMA}.job"
has_many :emps, :class_name => 'SelectOnAssocWithIncludeTest::SchemaEmp', :foreign_key => 'job_id'
has_many :depts, :through => :emps, :select => "#{SCHEMA}.dept.id, #{SCHEMA}.dept.name nombre"
end
class PartialSchemaJob < ActiveRecord::Base
set_table_name "#{SCHEMA}.job"
has_many :emps, :class_name => 'SelectOnAssocWithIncludeTest::SchemaEmp', :foreign_key => 'job_id'
has_many :depts, :through => :emps, :select => "dept.id, dept.name nombre"
end
class UnqualifiedSchemaJob < ActiveRecord::Base
set_table_name "#{SCHEMA}.job"
has_many :emps, :class_name => 'SelectOnAssocWithIncludeTest::SchemaEmp', :foreign_key => 'job_id'
has_many :depts, :through => :emps, :select => "id, name nombre"
end
def test_lazy_loading_select_on_hmt_assoc_works_if_columns_are_fully_qualified
assert_select_used Job.find(:all).first.depts.first
assert_select_used SchemaJob.find(:all).first.depts.first
assert_select_used PartialSchemaJob.find(:all).first.depts.first
assert_raises(ActiveRecord::StatementInvalid) { UnqualifiedSchemaJob.find(:all).first.depts.first }
assert_select_used Job.find(:first).depts.first
assert_select_used SchemaJob.find(:first).depts.first
assert_select_used PartialSchemaJob.find(:first).depts.first
assert_raises(ActiveRecord::StatementInvalid) { UnqualifiedSchemaJob.find(:first).depts.first }
assert_select_used Job.find(1).depts.first
assert_select_used SchemaJob.find(1).depts.first
assert_select_used PartialSchemaJob.find(1).depts.first
assert_raises(ActiveRecord::StatementInvalid) { UnqualifiedSchemaJob.find(1).depts.first }
end
def test_eager_loading_select_on_hmt_assoc_works_if_columns_are_fully_qualified
assert_select_used Job.find(:all, :include => :depts).first.depts.first
assert_select_used SchemaJob.find(:all, :include => :depts).first.depts.first
assert_select_used PartialSchemaJob.find(:all, :include => :depts).first.depts.first
assert_select_used UnqualifiedSchemaJob.find(:all, :include => :depts).first.depts.first
assert_select_used Job.find(:first, :include => :depts).depts.first
assert_select_used SchemaJob.find(:first, :include => :depts).depts.first
assert_select_used PartialSchemaJob.find(:first, :include => :depts).depts.first
assert_select_used UnqualifiedSchemaJob.find(:first, :include => :depts).depts.first
# BIG trouble -- find by id bombs for the last three
assert_select_used Job.find(1, :include => :depts).depts.first
assert_select_used SchemaJob.find(1, :include => :depts).depts.first
assert_select_used PartialSchemaJob.find(1, :include => :depts).depts.first
assert_select_used UnqualifiedSchemaJob.find(1, :include => :depts).depts.first
end
end
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.