Class: SQL::Maker::Select

Inherits:
Object
  • Object
show all
Includes:
Util
Defined in:
lib/sql/maker/select.rb

Direct Known Subclasses

Oracle

Defined Under Namespace

Classes: Oracle

Instance Attribute Summary collapse

Instance Method Summary collapse

Methods included from Util

#array_wrap, #bind_param, bind_param, #croak, included, #parse_args, #quote_identifier, quote_identifier

Constructor Details

#initialize(args = {}) ⇒ Select

Returns a new instance of Select.



11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
 
# File 'lib/sql/maker/select.rb', line 11

def initialize(args = {})
  @quote_char = args[:quote_char] || ''
  @name_sep = args[:name_sep] || '.'
  @new_line = args[:new_line] || "\n"
  @strict = args[:strict] || false
  @auto_bind = args[:auto_bind] || false

  @select = args[:select] || []
  @distinct = args[:distinct] || false
  @select_map = args[:select_map] || {}
  @select_map_reverse = args[:select_map_reverse] || {}
  @from = args[:from] || []
  @joins = args[:joins] || []
  @index_hint = args[:index_hint] || {}
  @group_by = args[:group_by] || []
  @order_by = args[:order_by] || []
  @prefix = args[:prefix] || 'SELECT '
  @where = args[:where]
  @having = args[:having]
  @limit = args[:limit]
  @offset = args[:offset]
  @for_update = args[:for_update]
  @subqueries = []
end

Instance Attribute Details

#auto_bind ⇒ Object (readonly)

Returns the value of attribute auto_bind.



7
8
9
 
# File 'lib/sql/maker/select.rb', line 7

def auto_bind
  @auto_bind
end

#for_update ⇒ Object

Returns the value of attribute for_update.



8
9
10
 
# File 'lib/sql/maker/select.rb', line 8

def for_update
  @for_update
end

#from ⇒ Object

Returns the value of attribute from.



8
9
10
 
# File 'lib/sql/maker/select.rb', line 8

def from
  @from
end

#group_by ⇒ Object

Returns the value of attribute group_by.



8
9
10
 
# File 'lib/sql/maker/select.rb', line 8

def group_by
  @group_by
end

#having ⇒ Object

Returns the value of attribute having.



8
9
10
 
# File 'lib/sql/maker/select.rb', line 8

def having
  @having
end

#index_hint ⇒ Object

Returns the value of attribute index_hint.



8
9
10
 
# File 'lib/sql/maker/select.rb', line 8

def index_hint
  @index_hint
end

#joins ⇒ Object

Returns the value of attribute joins.



8
9
10
 
# File 'lib/sql/maker/select.rb', line 8

def joins
  @joins
end

#name_sep ⇒ Object (readonly)

Returns the value of attribute name_sep.



7
8
9
 
# File 'lib/sql/maker/select.rb', line 7

def name_sep
  @name_sep
end

#new_line ⇒ Object (readonly)

Returns the value of attribute new_line.



7
8
9
 
# File 'lib/sql/maker/select.rb', line 7

def new_line
  @new_line
end

#order_by ⇒ Object

Returns the value of attribute order_by.



8
9
10
 
# File 'lib/sql/maker/select.rb', line 8

def order_by
  @order_by
end

#quote_char ⇒ Object (readonly)

Returns the value of attribute quote_char.



7
8
9
 
# File 'lib/sql/maker/select.rb', line 7

def quote_char
  @quote_char
end

#select ⇒ Object

Returns the value of attribute select.



8
9
10
 
# File 'lib/sql/maker/select.rb', line 8

def select
  @select
end

#select_map ⇒ Object

Returns the value of attribute select_map.



8
9
10
 
# File 'lib/sql/maker/select.rb', line 8

def select_map
  @select_map
end

#select_map_reverse ⇒ Object

Returns the value of attribute select_map_reverse.



8
9
10
 
# File 'lib/sql/maker/select.rb', line 8

def select_map_reverse
  @select_map_reverse
end

#strict ⇒ Object (readonly)

Returns the value of attribute strict.



7
8
9
 
# File 'lib/sql/maker/select.rb', line 7

def strict
  @strict
end

#subqueries ⇒ Object

Returns the value of attribute subqueries.



8
9
10
 
# File 'lib/sql/maker/select.rb', line 8

def subqueries
  @subqueries
end

#where ⇒ Object

Returns the value of attribute where.



8
9
10
 
# File 'lib/sql/maker/select.rb', line 8

def where
  @where
end

Instance Method Details

#_add_index_hint(*args) ⇒ Object 



317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
 
# File 'lib/sql/maker/select.rb', line 317

def _add_index_hint(*args)
  table, as = parse_args(*args)
  tbl_name =
    if table.respond_to?(:as_sql)
      '(' + table.as_sql + ')'
    else
      self._quote(table)
    end
  quoted = as ? tbl_name + ' ' + self._quote(as) : tbl_name
  hint = self.index_hint[table]
  return quoted unless hint && hint.is_a?(Hash)
  if hint[:list]&& !hint[:list].empty?
    return quoted + ' ' + (hint[:type].upcase || 'USE') + ' INDEX (' + 
      hint[:list].map {|e| self._quote(e) }.join(',') + ')'
  end
  return quoted
end

#_quote(label) ⇒ Object 



151
152
153
 
# File 'lib/sql/maker/select.rb', line 151

def _quote(label)
  SQL::Maker::Util.quote_identifier(label, self.quote_char, self.name_sep)
end

#add_from(*args) ⇒ Object 



98
99
100
101
102
103
104
105
106
107
108
 
# File 'lib/sql/maker/select.rb', line 98

def add_from(*args)
  table, as = parse_args(*args)
  if table.respond_to?(:as_sql)
    self.subqueries += table.bind
    self.from += [[table, as]]
  else
    table = table.to_s
    self.from += [[table, as]]
  end
  self
end

#add_group_by(*args) ⇒ Object 



253
254
255
256
257
258
259
260
261
262
 
# File 'lib/sql/maker/select.rb', line 253

def add_group_by(*args)
  group, order = parse_args(*args)
  self.group_by +=
    if group.respond_to?(:as_sql)
      [group.as_sql]
    else
      order ? [self._quote(group) + " #{order}"] : [self._quote(group)]
    end
  return self
end

#add_having(*args) ⇒ Object 



301
302
303
304
305
306
307
308
309
310
311
 
# File 'lib/sql/maker/select.rb', line 301

def add_having(*args)
  col, val = parse_args(*args)
  col = col.to_s
  if orig = self.select_map_reverse[col]
    col = orig.respond_to?(:as_sql) ? orig.as_sql : orig
  end

  self.having ||= self.new_condition()
  self.having.add(col, val)
  return self
end

#add_index_hint(*args) ⇒ Object 



130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
 
# File 'lib/sql/maker/select.rb', line 130

def add_index_hint(*args)
  table, hint = parse_args(*args)
  table = table.to_s
  if hint.is_a?(Hash)
    # { :type => '...', :list => ['foo'] }
    type = hint[:type] || 'USE'
    list = array_wrap(hint[:list])
  else
    # ['foo, 'bar'] or just 'foo'
    type = 'USE'
    list = array_wrap(hint)
  end

  self.index_hint[table] = {
    :type => type,
    :list => list,
  }

  return self
end

#add_join(*args) ⇒ Object 



110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
 
# File 'lib/sql/maker/select.rb', line 110

def add_join(*args)
  # :user => { :type => 'inner', :table => 'config', :condition => {'user.user_id' => 'config.user_id'} }
  # [ subquery, 'bar' ] => { :type => 'inner', :table => 'config', :condition => {'user.user_id' => 'config.user_id'} }
  table, joins = parse_args(*args)
  table, as = parse_args(*table)

  if table.respond_to?(:as_sql)
    self.subqueries += table.bind
    table = '('  + table.as_sql + ')'
  else
    table = table.to_s
  end

  self.joins += [{
    :table => [ table, as ],
    :joins => joins
  }]
  self
end

#add_order_by(*args) ⇒ Object 



233
234
235
236
237
 
# File 'lib/sql/maker/select.rb', line 233

def add_order_by(*args)
  col, type = parse_args(*args)
  self.order_by += [[col, type]]
  return self
end

#add_select(*args) ⇒ Object 



88
89
90
91
92
93
94
95
96
 
# File 'lib/sql/maker/select.rb', line 88

def add_select(*args)
  term, col = parse_args(*args)
  term = term.to_s if term.is_a?(Symbol)
  col ||= term
  self.select += array_wrap(term)
  self.select_map[term] = col
  self.select_map_reverse[col] = term
  self # method chain
end

#add_where(*args) ⇒ Object 



276
277
278
279
280
 
# File 'lib/sql/maker/select.rb', line 276

def add_where(*args)
  self.where ||= self.new_condition()
  self.where.add(*args)
  return self
end

#add_where_raw(*args) ⇒ Object 



282
283
284
285
286
 
# File 'lib/sql/maker/select.rb', line 282

def add_where_raw(*args)
  self.where ||= self.new_condition()
  self.where.add_raw(*args)
  return self
end

#as_sql ⇒ Object 



155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
 
# File 'lib/sql/maker/select.rb', line 155

def as_sql
  sql = ''
  new_line = self.new_line
 
  unless self.select.empty?
    sql += self.prefix
    sql += 'DISTINCT ' if self.distinct
    sql += self.select.map {|col|
      as = self.select_map[col]
      col = col.respond_to?(:as_sql) ? col.as_sql : self._quote(col)
      next col if as.nil?
      as = as.respond_to?(:as_sql) ? as.as_sql : self._quote(as)
      if as && col =~ /(?:^|\.)#{Regexp.escape(as)}$/
        col
      else
        col + ' AS ' +  as
      end
    }.join(', ') + new_line
  end

  sql += 'FROM '

  ## Add any explicit JOIN statements before the non-joined tables.
  unless self.joins.empty?
    initial_table_written = 0
    self.joins.each do |j|
      table = j[:table]
      join  = j[:joins]
      table = self._add_index_hint(table); ## index hint handling
      sql += table if initial_table_written == 0
      initial_table_written += 1
      sql += ' ' + join[:type].upcase if join[:type]
      sql += ' JOIN ' + self._quote(join[:table])
      sql += ' ' + self._quote(join[:alias]) if join[:alias]

      if condition = join[:condition]
        if condition.is_a?(Array)
          sql += ' USING (' + condition.map {|e| self._quote(e) }.join(', ') + ')'
        elsif condition.is_a?(Hash)
          conds = []
          condition.keys.each do |key|
            conds += [self._quote(key) + ' = ' + self._quote(condition[key])]
          end
          sql += ' ON ' + conds.join(' AND ')
        else
          sql += ' ON ' + condition
        end
      end
    end
    sql += ', ' unless self.from.empty?
  end

  unless self.from.empty?
    sql += self.from.map {|e| self._add_index_hint(e[0], e[1]) }.join(', ')
  end

  sql += new_line
  sql += self.as_sql_where     if self.where

  sql += self.as_sql_group_by  if self.group_by
  sql += self.as_sql_having    if self.having
  sql += self.as_sql_order_by  if self.order_by

  sql += self.as_sql_limit     if self.limit

  sql += self.as_sql_for_update
  sql.gsub!(/#{new_line}+$/, '')

  @auto_bind ? bind_param(sql, self.bind) : sql
end

#as_sql_for_update ⇒ Object 



313
314
315
 
# File 'lib/sql/maker/select.rb', line 313

def as_sql_for_update
  self.for_update ? ' FOR UPDATE' : ''
end

#as_sql_group_by ⇒ Object 



264
265
266
267
268
269
 
# File 'lib/sql/maker/select.rb', line 264

def as_sql_group_by
  elems = self.group_by
  return '' if elems.empty?

  return 'GROUP BY ' + elems.join(', ') + self.new_line
end

#as_sql_having ⇒ Object 



293
294
295
296
297
298
299
 
# File 'lib/sql/maker/select.rb', line 293

def as_sql_having
  if self.having
    'HAVING ' + self.having.as_sql + self.new_line
  else
    ''
  end
end

#as_sql_limit ⇒ Object 



226
227
228
229
230
231
 
# File 'lib/sql/maker/select.rb', line 226

def as_sql_limit
  return '' unless n = self.limit
  croak("Non-numerics in limit clause (n)") if n =~ /\D/
  return sprintf "LIMIT %d%s" + self.new_line, n,
    (self.offset ? " OFFSET " + self.offset.to_i.to_s : "")
end

#as_sql_order_by ⇒ Object 



239
240
241
242
243
244
245
246
247
248
249
250
251
 
# File 'lib/sql/maker/select.rb', line 239

def as_sql_order_by
  attrs = self.order_by
  return '' if attrs.empty?

  return 'ORDER BY ' + attrs.map {|e|
    col, type = e
    if col.respond_to?(:as_sql)
      col.as_sql
    else
      type ? self._quote(col) + " #{type}" : self._quote(col)
    end
  }.join(', ') + self.new_line
end

#as_sql_where ⇒ Object 



288
289
290
291
 
# File 'lib/sql/maker/select.rb', line 288

def as_sql_where
  where = self.where.as_sql()
  where and !where.empty? ? "WHERE #{where}" + self.new_line : ''
end

#bind ⇒ Object 



80
81
82
83
84
85
86
 
# File 'lib/sql/maker/select.rb', line 80

def bind
  bind = []
  bind += self.subqueries if self.subqueries
  bind += self.where.bind if self.where
  bind += self.having.bind if self.having
  bind
end

#distinct(distinct = nil) ⇒ Object 



36
37
38
39
40
41
42
43
 
# File 'lib/sql/maker/select.rb', line 36

def distinct(distinct = nil)
  if distinct
    @distinct = distinct
    self # method chain
  else
    @distinct
  end
end

#limit(limit = nil) ⇒ Object 



63
64
65
66
67
68
69
70
 
# File 'lib/sql/maker/select.rb', line 63

def limit(limit = nil)
  if limit
    @limit = limit
    self # method chain
  else
    @limit
  end
end

#new_condition ⇒ Object 



72
73
74
75
76
77
78
 
# File 'lib/sql/maker/select.rb', line 72

def new_condition
  SQL::Maker::Condition.new(
    :quote_char => self.quote_char,
    :name_sep   => self.name_sep,
    :strict     => self.strict,
  )
end

#offset(offset = nil) ⇒ Object 



54
55
56
57
58
59
60
61
 
# File 'lib/sql/maker/select.rb', line 54

def offset(offset = nil)
  if offset
    @offset = offset
    self # method chain
  else
    @offset
  end
end

#prefix(prefix = nil) ⇒ Object 



45
46
47
48
49
50
51
52
 
# File 'lib/sql/maker/select.rb', line 45

def prefix(prefix = nil)
  if prefix
    @prefix = prefix
    self # method chain
  else
    @prefix
  end
end

#set_where(where) ⇒ Object 



271
272
273
274
 
# File 'lib/sql/maker/select.rb', line 271

def set_where(where)
  self.where = where
  return self
end