Created
July 22, 2010 22:56
-
-
Save thinkerbot/486750 to your computer and use it in GitHub Desktop.
Study of AR select + include
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# 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 file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# 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