Class: SqlBuilder

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

Overview

provides a builder interface for creating SQL queries

Constant Summary collapse

DEFAULT_LIMIT = 
10000
ARRAY_ATTRS =

attributes that are arrays and should be included in serialization

%w[selects clauses distincts froms joins order_bys group_bys havings withs]
SCALAR_ATTRS =

attributes that are scalars and should be included in serialization

%w[the_limit make_objects row_offset fetch_next the_dialect]
DIALECTS =

Dialects

i(psql mssql)
@@default_make_objects = 
true

Class Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize ⇒ SqlBuilder

Returns a new instance of SqlBuilder.



41
42
43
44
45
46
47
48
49
50
51
52
 
# File 'lib/sql_builder.rb', line 41

def initialize
  ARRAY_ATTRS.each do |attr|
    self.send "#{attr}=", []
  end

  @make_objects = @@default_make_objects
  @the_limit = DEFAULT_LIMIT
  @limit_warning = true
  @row_offset = nil
  @fetch_next = nil
  @the_dialect = :psql
end

Class Attribute Details

.default_timezone ⇒ Object

This should mimic the behavior of ActiveRecord::Base.default_timezone i.e. it’s either :utc (default) or :local



36
37
38
 
# File 'lib/sql_builder.rb', line 36

def default_timezone
  @default_timezone
end

Class Method Details

.default_make_objects=(val) ⇒ Object 



29
30
31
 
# File 'lib/sql_builder.rb', line 29

def self.default_make_objects=(val)
  @@default_make_objects = val
end

.from_raw(raw) ⇒ Object 



320
321
322
323
324
325
326
327
328
 
# File 'lib/sql_builder.rb', line 320

def self.from_raw(raw)
  builder = SqlBuilder.new
  (ARRAY_ATTRS + SCALAR_ATTRS).each do |attr|
    if raw[attr]
      builder.send "#{attr}=", raw[attr]
    end
  end
  builder
end

Instance Method Details

#as_objects ⇒ Object 



315
316
317
318
 
# File 'lib/sql_builder.rb', line 315

def as_objects
  @make_objects = true
  self
end

#as_raw ⇒ Object 



310
311
312
313
 
# File 'lib/sql_builder.rb', line 310

def as_raw
  @make_objects = false
  self
end

#check_result_limit!(query) ⇒ Object 



284
285
286
287
288
289
 
# File 'lib/sql_builder.rb', line 284

def check_result_limit!(query)
  if query.count == @the_limit and @limit_warning
    raise "Query result has exactly #{@the_limit} results, which is the same as the limit"
  end
  query
end

#dialect(new_dialect = nil) ⇒ Object 



59
60
61
62
63
64
65
66
67
68
69
 
# File 'lib/sql_builder.rb', line 59

def dialect(new_dialect=nil)
  if new_dialect.nil?
    return @the_dialect # make this method act like a getter as well
  end
  new_dialect = new_dialect.to_sym
  unless DIALECTS.index new_dialect
    raise "Invalid dialect #{new_dialect}, must be one of: #{DIALECTS.join(', ')}"
  end
  @the_dialect = new_dialect
  self
end

#distinct(distinct, table = nil) ⇒ Object 



205
206
207
208
209
210
211
212
213
 
# File 'lib/sql_builder.rb', line 205

def distinct(distinct, table=nil)
  distinct = [distinct] unless distinct.is_a? Array
  table_part = table ? "#{table}." : ''
  distinct.each do |d|
    statement = "#{table_part}#{d}"
    @distincts << statement
  end
  self
end

#dup ⇒ Object 



300
301
302
303
304
305
306
307
308
 
# File 'lib/sql_builder.rb', line 300

def dup
  other = SqlBuilder.new
  (ARRAY_ATTRS + SCALAR_ATTRS).each do |attr|
    other.send "#{attr}=", self.send(attr).dup
  end
  other.make_objects = @make_objects
  other.the_limit = @the_limit
  other
end

#exec ⇒ Object 



291
292
293
294
295
296
297
298
 
# File 'lib/sql_builder.rb', line 291

def exec
  results = ActiveRecord::Base.connection.execute(self.to_sql).to_a
  if @make_objects
    check_result_limit!(QueryResult.new results)
  else
    check_result_limit!(results)
  end
end

#fetch(fetch) ⇒ Object 



200
201
202
203
 
# File 'lib/sql_builder.rb', line 200

def fetch(fetch)
  @fetch_next = fetch
  self
end

#from(table, as = nil) ⇒ Object 



71
72
73
74
75
76
77
78
 
# File 'lib/sql_builder.rb', line 71

def from(table, as=nil)
  if as
    @froms << "#{table} AS #{as}"
  else
    @froms << table
  end
  self
end

#get_join_mode_vars(arg1, arg2, arg3) ⇒ Object 



98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
 
# File 'lib/sql_builder.rb', line 98

def get_join_mode_vars(arg1, arg2, arg3)
  # 1 and 2 arg options can only join parent tables
  if arg2.nil? && arg3.nil?
    # 'work_orders AS wo'
    table = arg1.split(' ').first
    as = arg1.split(' ').last

    if self.froms.blank?
      raise 'must declare a from statement to use 1 argument join'
    end
    child_table = self.froms.first.split(' ').last
    foreign_key = "#{table.singularize}_id"
    clause = "#{child_table}.#{foreign_key} = #{as}.id"
  elsif arg3.nil?
    # 'work_orders AS wo', 'inspection_items.work_order_id'
    table = arg1.split(' ').first
    as = arg1.split(' ').last
    clause = "#{as}.id = #{arg2}"
  else
    table = arg1
    as = arg2
    clause = arg3
  end
  [table, as, clause]
end

#group_by(expression) ⇒ Object 



179
180
181
182
 
# File 'lib/sql_builder.rb', line 179

def group_by(expression)
  @group_bys << expression
  self
end

#having(clause) ⇒ Object 



174
175
176
177
 
# File 'lib/sql_builder.rb', line 174

def having(clause)
  @havings << clause
  self
end

#inner_join(arg1, arg2 = nil, arg3 = nil) ⇒ Object 



131
132
133
134
135
 
# File 'lib/sql_builder.rb', line 131

def inner_join(arg1, arg2=nil, arg3=nil)
  table, as, clause = get_join_mode_vars arg1, arg2, arg3
  @joins << "INNER JOIN #{table} AS #{as} ON #{clause}"
  self
end

#left_join(arg1, arg2 = nil, arg3 = nil) ⇒ Object 



125
126
127
128
129
 
# File 'lib/sql_builder.rb', line 125

def left_join(arg1, arg2=nil, arg3=nil)
  table, as, clause = get_join_mode_vars arg1, arg2, arg3
  @joins << "LEFT JOIN #{table} AS #{as} ON #{clause}"
  self
end

#limit(limit, limit_warning = false) ⇒ Object 



189
190
191
192
193
 
# File 'lib/sql_builder.rb', line 189

def limit(limit, limit_warning=false)
  @limit_warning = limit_warning
  @the_limit = limit
  self
end

#offset(offset) ⇒ Object 



195
196
197
198
 
# File 'lib/sql_builder.rb', line 195

def offset(offset)
  @row_offset = offset
  self
end

#order_by(expression) ⇒ Object 



184
185
186
187
 
# File 'lib/sql_builder.rb', line 184

def order_by(expression)
  @order_bys << expression
  self
end

#outer_join(arg1, arg2 = nil, arg3 = nil) ⇒ Object 



137
138
139
140
141
 
# File 'lib/sql_builder.rb', line 137

def outer_join(arg1, arg2=nil, arg3=nil)
  table, as, clause = get_join_mode_vars arg1, arg2, arg3
  @joins << "LEFT OUTER JOIN #{table} AS #{as} ON #{clause}"
  self
end

#parse_where(clause) ⇒ Object 



149
150
151
152
153
154
155
156
157
158
159
160
 
# File 'lib/sql_builder.rb', line 149

def parse_where(clause)
  clause.each_with_index do |entry, i|
    if entry.is_a?(Array)
      template = clause[0].split('?')
      template = template.map.with_index {|phrase, index| index==i-1 ? phrase : phrase+'?' }
      template.insert(i, '('+('?'*entry.length).split('').join(',')+')')
      clause[0] = template.join('')
      clause.delete_at(i)
      clause.insert(i, *entry)
    end
  end
end

#right_join(arg1, arg2 = nil, arg3 = nil) ⇒ Object 



143
144
145
146
147
 
# File 'lib/sql_builder.rb', line 143

def right_join(arg1, arg2=nil, arg3=nil)
  table, as, clause = get_join_mode_vars arg1, arg2, arg3
  @joins << "RIGHT JOIN #{table} AS #{as} ON #{clause}"
  self
end

#sanitize(query) ⇒ Object 



271
272
273
274
275
276
277
278
279
280
281
282
 
# File 'lib/sql_builder.rb', line 271

def sanitize(query)
  query.each_with_index do |entry, i| #need to escape %
    if entry.is_a?(String)
      query[i] = entry.gsub('%', '%%')
    end
  end
  if ActiveRecord::Base.respond_to? :sanitize_sql
    ActiveRecord::Base.sanitize_sql query
  else
    ActiveRecord::Base.sanitize_sql_array query
  end
end

#select(columns, table = nil, prefix = nil) ⇒ Object 



80
81
82
83
84
85
86
87
88
89
90
91
 
# File 'lib/sql_builder.rb', line 80

def select(columns, table=nil, prefix=nil)
  columns = [columns] unless columns.is_a? Array
  table_part = table ? "#{table}." : ''
  columns.each do |c|
    statement = "#{table_part}#{c}"
    if prefix
      statement += " #{prefix}#{c}"
    end
    @selects << statement
  end
  self
end

#to_sql ⇒ Object 



215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
 
# File 'lib/sql_builder.rb', line 215

def to_sql
  _distinct = ''
  if @distincts and @distincts.count > 0
    if @the_dialect == :mssql
      _distinct += 'DISTINCT ('
      _distinct += @distincts.join(', ')
      _distinct += ')'
    else
      _distinct += 'DISTINCT ON ('
      _distinct += @distincts.join(', ')
      _distinct += ')'
    end
  end

  withs_s = @withs.map do |w|
    "WITH #{w}"
  end.join(' ')

  top_s = if @the_limit && @the_dialect == :mssql
    "TOP #{@the_limit}"
  else
    ''
  end

  froms_s = @froms.empty? ? '' : "FROM #{@froms.join(', ')}"

  s = "#{withs_s} SELECT #{top_s} #{_distinct} #{@selects.join(', ')} #{froms_s} #{@joins.join(' ')}"
  if @clauses.length > 0
    clauses_s = @clauses.map{|c| "(#{c})"}.join(' AND ')
    s += "  WHERE #{clauses_s}"
  end
  if @group_bys.length > 0
    s += " GROUP BY #{@group_bys.join(', ')}"
  end
  if @havings.length > 0
    s += " HAVING #{@havings.join(' AND ')}"
  end
  if @order_bys.length > 0
    s += " ORDER BY #{@order_bys.join(', ')}"
  end
  if @the_limit && @the_dialect != :mssql
    s += " LIMIT #{@the_limit}"
  end
  if @row_offset && @the_dialect == :mssql
    s += " OFFSET #{@row_offset} ROWS"
  elsif @row_offset
    s += " OFFSET #{@row_offset}"
  end
  if @fetch_next && @the_dialect == :mssql
    s += " FETCH NEXT #{@fetch_next} ROWS ONLY"
  elsif @fetch_next
    s += " FETCH FIRST #{@fetch_next} ROWS ONLY"
  end
  s
end

#where(*clause) ⇒ Object 



169
170
171
172
 
# File 'lib/sql_builder.rb', line 169

def where(*clause)
  @clauses << sanitize(parse_where(clause))
  self
end

#where_raw(clause) ⇒ Object

Adds a where clause without any sanitization or substitution This is essentially for clauses containing a ‘?`



164
165
166
167
 
# File 'lib/sql_builder.rb', line 164

def where_raw(clause)
  @clauses << clause
  self
end

#with(w) ⇒ Object 



93
94
95
96
 
# File 'lib/sql_builder.rb', line 93

def with(w)
  @withs << w
  self
end