public
Last active

Study of AR select + include

  • Download Gist
patch.rb
Ruby
1 2 3 4 5 6 7 8 9 10 11 12
# 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
select_with_include.rb
Ruby
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 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 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277
# 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

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.