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
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