redmine backlogs sql tests

lib tasks test_sql_execute.rake

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