#!/usr/bin/env ruby
#
# JOIN vs HABTM vs INSET benchmark
#

require File.expand_path(File.dirname(__FILE__) + '/../../config/environment')
require 'benchmark'

module ToyApp

  PERFORMANCE_PGSQL = {
    :database => 'benchmark',
    :adapter => 'postgresql',
    :host => 'localhost',
    :username => 'user',
    :password => '',
    :encoding => 'utf8'
  }
  PERFORMANCE_MYSQL = {
    :database => 'benchmark',
    :adapter => 'mysql',
    :host => 'localhost',
    :username => 'user',
    :password => '',
    :encoding => 'utf8'
  }

  ActiveRecord::Base.establish_connection PERFORMANCE_MYSQL # Change it on tests

  class CreatePerformanceDB < ActiveRecord::Migration
    def self.up
      create_table :posts, :force => true do |t|
        t.text :data
      end

      100.times do
        Post.create(:data => Digest::SHA1.hexdigest(rand.to_s))
      end

      # JOIN
      create_table :posts_balls, :force => true do |t|
        t.integer :post_id, :ball_id
      end
      create_table :balls, :force => true do |t|
        t.text :data
      end
      add_index :posts_balls, :post_id
      add_index :posts_balls, :ball_id

      100.times do |i|
        Ball.create(:data => Digest::SHA1.hexdigest(rand.to_s))
      end

      # HBTM
      create_table :cubes_posts, :id => false, :force => true do |t|
        t.integer :post_id, :cube_id
      end
      create_table :cubes, :force => true do |t|
        t.text :data
      end
      add_index :cubes_posts, :post_id
      add_index :cubes_posts, :cube_id

      100.times do
        Cube.create(:data => Digest::SHA1.hexdigest(rand.to_s))
      end
    end

    def self.down
      drop_table :posts
      drop_table :posts_balls
      drop_table :cubes_posts
      drop_table :balls
      drop_table :cubes
    end
  end
  class Post < ActiveRecord::Base
    # JOIN
    has_many :post_balls
    has_many :balls, :through => :post_balls
    # HBTM
    has_and_belongs_to_many :cubes

    # InSet method
    def groups
      ids = post_balls.map(&:ball_id)
      Ball.find(:all, :conditions => ["id in (?)", ids])
    end

  end

  class PostBall < ActiveRecord::Base
    set_table_name 'posts_balls'
    belongs_to :post
    belongs_to :ball
  end

  class Cube < ActiveRecord::Base
    has_and_belongs_to_many :posts
  end

  class Ball < ActiveRecord::Base
    has_many :post_balls
    has_many :posts, :through => :post_balls

    # InSet method
    def groups
      ids = post_balls.map(&:post_id)
      Post.find(:all, :conditions => ["id in (?)", ids])
    end
  end

  def self.adding
    puts "========= ADDING ========="
    posts = Post.find(:all)
    balls_array = Ball.find(:all)
    cubes_array = Cube.find(:all)
    Benchmark.bm do |rep|
      rep.report("HBTM") do
        for post in posts
          cubes_array.each{|cube| post.cubes << cube}
        end
      end
      rep.report("JOIN") do
        for post in posts
          balls_array.each{|ball| post.balls << ball}
        end
      end
    end
  end

  def self.adding_by_groups
    puts "========= ADDING BY GROUPS========="
    posts = Post.find(:all)
    balls_array = Ball.find(:all)
    cubes_array = Cube.find(:all)
    Benchmark.bm do |rep|
      rep.report("HBTM") do
        for post in posts
          cubes_array.in_groups_of(10){|c| post.cubes << c}
        end
      end
      rep.report("JOIN") do
        for post in posts
          balls_array.in_groups_of(10){|b| post.balls << b}
        end
      end
    end
  end

  def self.finding
    puts "========= FINDING ========="
    posts = Post.find(:all)
    Benchmark.bm do |rep|
      rep.report("HBTM") do
        10.times do
          posts.each{|post| post.cubes :all, :group_by => :cube_id}
        end
      end
      rep.report("JOIN") do
        10.times do
          posts.each{|post| post.balls :all, :group_by => :ball_id}
        end
      end
      rep.report("INSET") do
        10.times do
          posts.each{|c| c.groups }
        end
      end
    end
  end

  def self.finding_back
    puts "========= FINDING BACK ========="
    balls_array = Ball.find(:all)
    cubes_array = Cube.find(:all)
    Benchmark.bm do |rep|
      rep.report("HBTM") do
        10.times do
          cubes_array.each{|c| c.posts :all, :group_by => :post_id}
        end
      end
      rep.report("JOIN") do
        10.times do
          balls_array.each{|b| b.posts :all, :group_by => :post_id}
        end
      end
      rep.report("INSET") do
        10.times do
          balls_array.each{|c| c.groups }
        end
      end
    end
  end

  CreatePerformanceDB.up
  adding
  adding_by_groups
  finding
  finding_back

end

# edge rails trunk/r8381
# RESULTS:

#  ++++++++ MYSQL +++++++++
#  ==  ToyApp::CreatePerformanceDB: migrating ====================================
#  [...skip...]
#  ==  ToyApp::CreatePerformanceDB: migrated (0.8456s) ===========================
#
#  ========= ADDING =========
#        user     system      total        real
#  HBTM 24.350000   1.270000  25.620000 ( 47.239847)
#  JOIN 12.410000   1.550000  13.960000 ( 30.863089)
#  ========= ADDING BY GROUPS=========
#        user     system      total        real
#  HBTM 29.110000   0.580000  29.690000 ( 34.925083)
#  JOIN  8.160000   0.730000   8.890000 ( 10.680383)
#  ========= FINDING =========
#        user     system      total        real
#  HBTM  8.830000   0.230000   9.060000 ( 13.733686)
#  JOIN  3.780000   0.230000   4.010000 (  7.609844)
#  INSET  7.070000   0.530000   7.600000 (  7.816038)
#  ========= FINDING BACK =========
#        user     system      total        real
#  HBTM 12.250000   0.240000  12.490000 ( 17.041704)
#  JOIN  3.850000   0.180000   4.030000 (  7.586062)
#  INSET  7.280000   0.530000   7.810000 (  8.170005)
#
#  ++++++++ PGSQL +++++++++
#  ==  ToyApp::CreatePerformanceDB: migrating ====================================
#  [...skip...]
#  ==  ToyApp::CreatePerformanceDB: migrated (1.1611s) ===========================
#
#  ========= ADDING =========
#        user     system      total        real
#  HBTM 10.470000   1.500000  11.970000 ( 35.073944)
#  JOIN 17.470000   1.910000  19.380000 ( 39.107405)
#  ========= ADDING BY GROUPS=========
#        user     system      total        real
#  HBTM  5.600000   0.810000   6.410000 ( 15.075026)
#  JOIN 10.370000   0.950000  11.320000 ( 14.386206)
#  ========= FINDING =========
#        user     system      total        real
#  HBTM  7.880000   0.690000   8.570000 ( 10.469631)
#  JOIN  5.830000   0.640000   6.470000 (  7.488131)
#  INSET  7.040000   0.760000   7.800000 (  8.859070)
#  ========= FINDING BACK =========
#        user     system      total        real
#  HBTM  7.590000   0.760000   8.350000 ( 10.211620)
#  JOIN  5.470000   0.550000   6.020000 (  6.913570)
#  INSET  7.800000   0.650000   8.450000 (  9.524528)