class SCAnalytics::Query

Constants

DEFAULT_DATA_DIR
DEFAULT_SQL_DIR
REQUIRED_PARAMS

Attributes

auto_cast[RW]
bind_variable[R]
bind_variables[R]
columns_to_cast[R]
combined_csv_file[R]
concurrent[RW]
connection[R]
csv_combined_dir[R]
csv_dir[R]
csv_file[R]
csv_files[R]
cursor[R]
database[RW]
end_time[R]
name[R]
query_name[R]
result[R]
results[R]
sql[RW]
start_time[R]
threads[R]

Public Class Methods

new(name = nil) { |self| ... } click to toggle source
# File lib/sc_analytics/query.rb, line 18
def initialize(name = nil, &block)
  @query_name = name.to_s if name
  yield self if block_given?
end
total_queries() click to toggle source
# File lib/sc_analytics/query.rb, line 189
def self.total_queries
  @@total_queries
end

Public Instance Methods

cast_columns(cols, type) click to toggle source
# File lib/sc_analytics/query.rb, line 41
def cast_columns(cols, type)
  @columns_to_cast||= {}
  @columns_to_cast[type]||= []

  @columns_to_cast[type] << cols
  @columns_to_cast[type].flatten!

  return nil
end
check_params() click to toggle source
# File lib/sc_analytics/query.rb, line 31
def check_params
  missing_params = []

  REQUIRED_PARAMS.each do |param|
    missing_params << param unless instance_variable_get("@#{param}")
  end

  raise SCAnalytics::QueryError, "must set query parameter#{"s" if missing_params.length > 1 }: #{missing_params.join(", ")}" unless missing_params.empty?
end
database=(*db) click to toggle source
# File lib/sc_analytics/query.rb, line 51
def database=(*db)
  @database = db.flatten
  @should_fork = (@database.size > 1)
end
default_file_name(combined_file = false) click to toggle source
# File lib/sc_analytics/query.rb, line 56
def default_file_name(combined_file = false)
  file_names = []

  if combined_file == :combined
    file_names = "#{@database.join("_")}_#{self.name.gsub(" ","_")}_combined"
  else
    @database.each do |db|
      file_names << "#{db}_#{self.name.gsub(" ","_")}"
    end
  end

  file_names
end
Also aliased as: default_file_names
default_file_names(combined_file = false)
Alias for: default_file_name
export_csv() click to toggle source
# File lib/sc_analytics/query.rb, line 75
def export_csv
  CSV.open(@csv_file.first,"w") do |csv|
    csv << @results.headers
  
    @results.rows.each do |row|
      # export date and time strings in this format for easier import using MS Query
      export_row = row.map{|el| el.is_a?(Time) ? el.strftime("%m-%d-%Y %H:%M:%S") : el}
      csv << export_row
    end
  end
end
export_csv_combined() click to toggle source
# File lib/sc_analytics/query.rb, line 87
def export_csv_combined
  CSV.open(@combined_csv_file,"w") do |csv|
    csv << @results.values.first.headers
  end

  @results.each_value do |qry|
    CSV.open(@combined_csv_file,"a") do |csv|
      qry.rows.each do |row|
        # export date and time strings in this format for easier import using MS Query
        export_row = row.map{|el| el.is_a?(Time) ? el.strftime("%m-%d-%Y %H:%M:%S") : el}
        csv << export_row
      end
    end
  end
end
forked?() click to toggle source
# File lib/sc_analytics/query.rb, line 71
def forked?
  @should_fork
end
name=(query_name) click to toggle source
# File lib/sc_analytics/query.rb, line 103
def name=(query_name)
  @query_name = query_name.to_s
end
run() click to toggle source
# File lib/sc_analytics/query.rb, line 107
def run
  check_params

  unless @should_fork
    @connection = Connections.use @database.first
    @cursor = @connection.get_cursor_for self
    bind_vars_to_cursor if @bind_variables

    @start_time = Time.now
    alert("Running query #{self.name}")

    @results = @connection.run(self, @cursor)

    @end_time = Time.now
    alert("Completed query #{self.name} in #{time_elapsed/60} minutes")

    @@total_queries += 1

    export_csv if @to_csv
  else
    fork_query
    @results = run_forked_queries
    export_csv_combined if @to_csv_combined
  end
end
sql=(arg) click to toggle source
# File lib/sc_analytics/query.rb, line 137
def sql=(arg)
  raise SCAnalytics::QueryError, "a SQL statement is already bound to query #{self.name}" if @sql

  file_name = apply_format arg, :suggest => {:ext => :sql}
  file_name_in_subfolder = apply_format file_name, :suggest => {:dir => DEFAULT_SQL_DIR}

  sql = case 
  when File.exists?(file_name)
    File.read(file_name)
  when File.exists?(file_name_in_subfolder)
    File.read(file_name_in_subfolder)
  else
    # assume that the argument is a sql statement if no file is found
    arg
  end

  # remove semi-colon from end of sql statement (if present) because ruby-oci8 does not accept these.
  # also remove all trailing new lines and white space to make sure we find any trailing semi-colons.
  while sql.chomp! || sql.chomp!(' ') || sql.chomp!(';'); end
  @sql = ERB.new(sql)
ensure
  @results = nil
end
time_elapsed() click to toggle source
# File lib/sc_analytics/query.rb, line 161
def time_elapsed
  if @start_time && @end_time
    @end_time - @start_time
  else
    nil
  end
end
to_csv(*file_names) click to toggle source
# File lib/sc_analytics/query.rb, line 169
def to_csv(*file_names)
  @to_csv = true
  @csv_dir = File.expand_path((file_names.last.is_a?(Hash) ? file_names.pop[:csv_dir] : DEFAULT_DATA_DIR).to_s)
  file_names = default_file_names if file_names.empty?
  
  @csv_file = file_names.flatten.map{|file_name| apply_format file_name, :enforce => {:ext => :csv}, :suggest => {:dir => @csv_dir}}

  raise SCAnalytics::QueryError, "must supply one csv file name for each database bound to query #{self.name}" unless @csv_file.length == @database.length
end
to_csv_combined(*file_name) click to toggle source
# File lib/sc_analytics/query.rb, line 179
def to_csv_combined(*file_name)
  raise ArgumentError, "too many arguments - #{file_name.length} for 2" if file_name.length > 2
  raise ArgumentError, "optional second argument must be of type Hash" if file_name.length == 2 && ! file_name.last.is_a?(Hash)
  @to_csv_combined = true
  @csv_combined_dir = File.expand_path((file_name.last.is_a?(Hash) ? file_name.pop[:csv_dir] : DEFAULT_DATA_DIR).to_s)
  file_name = file_name.empty? ? default_file_name(:combined) : file_name.first

  @combined_csv_file = apply_format file_name, :enforce => {:ext => :csv}, :suggest => {:dir => @csv_combined_dir}
end

Private Instance Methods

apply_format(file_name, options = {}) click to toggle source
# File lib/sc_analytics/query.rb, line 201
def apply_format(file_name, options = {})
  unknown_options = []
  options.keys.each do |option|
    unknown_options << option unless [:enforce, :suggest].include?(option.to_sym)
  end
  raise "unknown format option#{"s" if unknown_options.length > 1}: #{unknown_options.join(", ")}" unless unknown_options.empty?

  options.each_pair do |option, settings|
    settings.each_pair do |setting, format|
      file_name = send("#{option.to_s}_#{setting.to_s}".to_sym, file_name, format)
    end
  end

  file_name
end
bind_vars_to_cursor() click to toggle source
# File lib/sc_analytics/query.rb, line 195
def bind_vars_to_cursor
  @bind_variables.each_pair do |bind_var, value|
    @cursor.bind_param(":#{bind_var.to_s}", value)
  end
end
enforce_dir(file_name, dir) click to toggle source
# File lib/sc_analytics/query.rb, line 226
def enforce_dir(file_name, dir)
  return file_name if File.dirname(file_name) == dir.to_s
  
  FileUtils.mkdir_p(dir.to_s)
  file_name = file_name.sub(File.dirname(file_name), "") unless File.dirname(file_name) == "."
  "#{dir}/" + file_name
end
enforce_ext(file_name, ext) click to toggle source
# File lib/sc_analytics/query.rb, line 217
def enforce_ext(file_name, ext)
  return file_name if file_name.match(/\.#{ext}$/)

  # remove any file extension provided and make sure
  # that the file_name ends with required extension
  file_name = file_name.sub(/\.\w+$(?!\.)/i, "")
  file_name + ".#{ext}"
end
fork_query() click to toggle source
# File lib/sc_analytics/query.rb, line 247
def fork_query
  @tines = {}

  @database.each_with_index do |db, i|
    forked_query = self.dup
    forked_query.database = db
    forked_query.to_csv(@csv_file[i], :csv_dir => @csv_dir) if @to_csv
    forked_query.name = "#{db.to_s}_#{self.name}"
    @tines[db] = forked_query
  end
end
run_forked_queries() click to toggle source
# File lib/sc_analytics/query.rb, line 259
def run_forked_queries
  @start_time = Time.now

  results = {}
  @threads = {}
  @tines.each_pair do |db, query|
    @threads[db] = Thread.new(query) do |qry|
      qry.run
      results[qry.database.first] = qry.results
    end
  end

  @threads.each_value{|thread| thread.join }

  @end_time = Time.now
  return results
end
suggest_dir(file_name, dir) click to toggle source
# File lib/sc_analytics/query.rb, line 240
def suggest_dir(file_name, dir)
  return file_name unless File.dirname(file_name) == "." && dir != "."

  FileUtils.mkdir_p(dir.to_s)
  "#{dir}/" + file_name
end
suggest_ext(file_name, ext) click to toggle source
# File lib/sc_analytics/query.rb, line 234
def suggest_ext(file_name, ext)
  return file_name if file_name.match(/\.\w+$(?!\.)/i)

  file_name + ".#{ext}"
end