Created
May 1, 2012 10:08
-
-
Save patrickatamaniuk/2566995 to your computer and use it in GitHub Desktop.
redmine backlogs sql tests
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
namespace :redmine do | |
namespace :backlogs do | |
desc "test sql execute" | |
task :test_sql_execute => :environment do | |
# using indices has different behavior between mysql and postgres | |
#query = "select coalesce(max(position), -1) + 1 from issues where not position is null" | |
#puts query | |
#res = RbStory.connection.execute(query) | |
#puts res | |
#res.each{|row| | |
# puts "row #{row}" | |
# puts "row[0] #{row[0]}" #bad on pg, ok on mysql | |
# puts "row.values[0] #{row.values[0]}" #ok on pg but not defined for mysql | |
#} | |
#using ActiveRecord and no 'as' clause will always return nil. | |
#puts "ActiveRecord not using as" | |
#RbStory.find_by_sql('select coalesce(max(position), -1) + 1 from issues where not position is null').each{|row| | |
# #puts "row #{row}" | |
# puts "row[0] #{row[0]}" | |
# newpos = row[0] | |
# puts "newpos=row[0] #{newpos}" | |
# newpos = row[0].to_i | |
# puts "newpos=row[0].to_i #{newpos}" | |
#} | |
puts "ActiveRecord using as" | |
query = 'select coalesce(max(position), -1) + 1 as newpos from issues where not position is null' | |
puts query | |
RbStory.find_by_sql(query).each{|row| | |
newpos = row[0].to_i #bad on pg and mysql | |
puts "row #{newpos}" | |
puts "row #{row.newpos}" #ok on pg and mysql | |
newpos = row.newpos.to_i #ok on pg and mysql | |
puts "row #{newpos}" | |
} | |
puts "ActiveRecord select_all and as" | |
RbStory.connection.select_all('select coalesce(max(position), -1) + 1 as newpos from issues where not position is null').each{|row| | |
puts "row #{row}" #ok on pg and mysql | |
puts "row[0] #{row[0]}" #bad on pg and mysql | |
puts "row['newpos'] #{row['newpos']}" #ok on pg and mysql | |
} | |
#puts "ActiveRecord select_all without as" | |
#RbStory.connection.select_all('select coalesce(max(position), -1) + 1 from issues where not position is null').each{|row| | |
# puts "row[0] #{row[0]}" #bad on pg and mysql | |
#} | |
# using 'as' and each works fine for postgres. not for mysql. | |
# using 'as and each_hash works on mysql but not on postgres | |
#puts "connection execute using as and each" | |
#query = 'select coalesce(max(position), -1) + 1 as newpos from issues where not position is null' | |
#puts query | |
#res = RbStory.connection.execute(query) | |
#puts res | |
#res.each{|row| | |
# puts "row #{row}" | |
# #puts "row['newpos'] #{row['newpos']}" #ok on pg, bad on mysql (cant convert string to integer) | |
# puts "row[0] #{row[0]}" #fine on mysql, bad on pg | |
#} | |
#using select_value might be confusing but works for this case | |
puts "using select_values" | |
res = RbStory.connection.select_values('select coalesce(max(position), -1) + 1 from issues where not position is null') | |
puts "res is #{res}" # ok on pg and mysql | |
puts "res[0] is #{res[0]}" #ok on pg and mysql | |
res.each{|row| | |
puts "row #{row}" # ok on pg and mysql | |
} | |
end | |
end | |
end |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment