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
 
# File 'lib/sql/maker/select.rb', line 11

def initialize(args = {})
  @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 '
  @new_line = args[:new_line] || "\n"
  @strict = args[:strict] || false
  @auto_bind = args[:auto_bind] || false
  @where = args[:where]
  @having = args[:having]
  @limit = args[:limit]
  @offset = args[:offset]
  @for_update = args[:for_update]
  @quote_char = args[:quote_char]
  @name_sep = args[:name_sep]
  @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 



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

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 



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

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

#add_from(*args) ⇒ Object 



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

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 



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

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 



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

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

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

#add_index_hint(*args) ⇒ Object 



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

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 



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

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 



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

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

#add_select(*args) ⇒ Object 



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

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 



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

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

#add_where_raw(*args) ⇒ Object 



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

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

#as_sql ⇒ Object 



154
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
 
# File 'lib/sql/maker/select.rb', line 154

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 



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

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

#as_sql_group_by ⇒ Object 



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

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 



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

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

#as_sql_limit ⇒ Object 



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

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 



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

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 



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

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

#bind ⇒ Object 



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

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 



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

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

#limit(limit = nil) ⇒ Object 



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

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

#new_condition ⇒ Object 



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

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 



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

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

#prefix(prefix = nil) ⇒ Object 



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

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

#set_where(where) ⇒ Object 



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

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