Rake command for executing user defined sql file


#1

Dear altruists,

I have two different class where in one I am executing sql to create a
view (1st one) in another one user defined sql (2nd one). In sqlmanager
class I am calling the both methods statically. Now my question how to
execute this using rake command.

class AverageTimeForFirstReferrals
class << self
def create
self.connection.execute(%Q(create or replace view
average_time_for_first_referrals as
SELECT u.id, u.created_at,
u.referrer_id, r.created_at as ref_created_at,
hourdiff(r.created_at,u.created_at) as time_difference
from users u inner join users r
on u.referrer_id= r.id
WHERE
TIMEDIFF(r.created_at,u.created_at) > 0))
end
end
end

===========
class Hourdiff
class << self
def hourdiff
self.connection.execute(%Q(create function hourdiff (start_date
DateTime, end_date DateTime) RETURNS decimal(10,2)
BEGIN
RETURN Datediff(start_date, end_date)

  • 24.0 + (extract(hour from end_date) - extract(hour from start_date)) +
    (extract(minute from end_date) - extract(minute from start_date)) / 60
    END)
    end
    end
    end
    =============

class SQLmanager
def load_views
AverageTimeForFirstReferrals.create
Hourdiff.hourdiff
end
end

==========
##test class

require ‘test_helper’

class AverageTimeForFirstReferralTest < ActiveSupport::TestCase

def setup
user1 = FactoryGirl.create(:user)
user2 = FactoryGirl.create(:user)
user2.referrer_id = user1.id
user2.created_at = 1.month.from_now
user2.save
end

def test_creating_view_for_avg_time_for_first_ref
#AverageTimeForFirstReferral.update_view_command();
# binding.pry
assert_equal(1.month * 1.hour,
Reports::AverageTimeForFirstReferral.first.time_difference)
end
end