Class: SCAnalytics::Query

Inherits:
Object
  • Object
show all
Defined in:
lib/sc_analytics/query.rb

Constant Summary collapse

REQUIRED_PARAMS = 
[:query_name, :database, :sql]
DEFAULT_DATA_DIR = 
:data_files
DEFAULT_SQL_DIR = 
:sql_queries
@@total_queries = 
0

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(name = nil) {|_self| ... } ⇒ Query

Returns a new instance of Query.

Yields:

  • (_self)

Yield Parameters:



18
19
20
21
 
# 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

Instance Attribute Details

#auto_cast ⇒ Object

Returns the value of attribute auto_cast.



7
8
9
 
# File 'lib/sc_analytics/query.rb', line 7

def auto_cast
  @auto_cast
end

#bind_variables(var_hash = {}) ⇒ Object (readonly) Also known as: bind_variable

Returns the value of attribute bind_variables.



6
7
8
 
# File 'lib/sc_analytics/query.rb', line 6

def bind_variables
  @bind_variables
end

#columns_to_cast ⇒ Object (readonly)

Returns the value of attribute columns_to_cast.



6
7
8
 
# File 'lib/sc_analytics/query.rb', line 6

def columns_to_cast
  @columns_to_cast
end

#combined_csv_file ⇒ Object (readonly)

Returns the value of attribute combined_csv_file.



6
7
8
 
# File 'lib/sc_analytics/query.rb', line 6

def combined_csv_file
  @combined_csv_file
end

#concurrent ⇒ Object

Returns the value of attribute concurrent.



7
8
9
 
# File 'lib/sc_analytics/query.rb', line 7

def concurrent
  @concurrent
end

#connection ⇒ Object (readonly)

Returns the value of attribute connection.



6
7
8
 
# File 'lib/sc_analytics/query.rb', line 6

def connection
  @connection
end

#csv_combined_dir ⇒ Object (readonly)

Returns the value of attribute csv_combined_dir.



6
7
8
 
# File 'lib/sc_analytics/query.rb', line 6

def csv_combined_dir
  @csv_combined_dir
end

#csv_dir ⇒ Object (readonly)

Returns the value of attribute csv_dir.



6
7
8
 
# File 'lib/sc_analytics/query.rb', line 6

def csv_dir
  @csv_dir
end

#csv_file ⇒ Object (readonly) Also known as: csv_files

Returns the value of attribute csv_file.



6
7
8
 
# File 'lib/sc_analytics/query.rb', line 6

def csv_file
  @csv_file
end

#cursor ⇒ Object (readonly)

Returns the value of attribute cursor.



6
7
8
 
# File 'lib/sc_analytics/query.rb', line 6

def cursor
  @cursor
end

#database ⇒ Object

Returns the value of attribute database.



7
8
9
 
# File 'lib/sc_analytics/query.rb', line 7

def database
  @database
end

#end_time ⇒ Object (readonly)

Returns the value of attribute end_time.



6
7
8
 
# File 'lib/sc_analytics/query.rb', line 6

def end_time
  @end_time
end

#query_name ⇒ Object (readonly) Also known as: name

Returns the value of attribute query_name.



6
7
8
 
# File 'lib/sc_analytics/query.rb', line 6

def query_name
  @query_name
end

#results ⇒ Object (readonly) Also known as: result

Returns the value of attribute results.



6
7
8
 
# File 'lib/sc_analytics/query.rb', line 6

def results
  @results
end

#sql ⇒ Object

Returns the value of attribute sql.



7
8
9
 
# File 'lib/sc_analytics/query.rb', line 7

def sql
  @sql
end

#start_time ⇒ Object (readonly)

Returns the value of attribute start_time.



6
7
8
 
# File 'lib/sc_analytics/query.rb', line 6

def start_time
  @start_time
end

#threads ⇒ Object (readonly)

Returns the value of attribute threads.



6
7
8
 
# File 'lib/sc_analytics/query.rb', line 6

def threads
  @threads
end

Class Method Details

.total_queries ⇒ Object 



189
190
191
 
# File 'lib/sc_analytics/query.rb', line 189

def self.total_queries
  @@total_queries
end

Instance Method Details

#cast_columns(cols, type) ⇒ Object 



41
42
43
44
45
46
47
48
49
 
# 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 ⇒ Object

Raises:



31
32
33
34
35
36
37
38
39
 
# 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

#default_file_name(combined_file = false) ⇒ Object Also known as: default_file_names 



56
57
58
59
60
61
62
63
64
65
66
67
68
 
# 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

#export_csv ⇒ Object 



75
76
77
78
79
80
81
82
83
84
85
 
# 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 ⇒ Object 



87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
 
# 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? ⇒ Boolean

Returns:

  • (Boolean) 


71
72
73
 
# File 'lib/sc_analytics/query.rb', line 71

def forked?
  @should_fork
end

#name=(query_name) ⇒ Object 



103
104
105
 
# File 'lib/sc_analytics/query.rb', line 103

def name=(query_name)
  @query_name = query_name.to_s
end

#run ⇒ Object 



107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
 
# 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

#time_elapsed ⇒ Object 



161
162
163
164
165
166
167
 
# 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) ⇒ Object

Raises:



169
170
171
172
173
174
175
176
177
 
# 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) ⇒ Object

Raises:

  • (ArgumentError) 


179
180
181
182
183
184
185
186
187
 
# 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