Class: GitHub::SQL

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

Overview

Public: Build and execute a SQL query, returning results as Arrays. This class uses ActiveRecord’s connection classes, but provides a better API for bind values and raw data access.

Example:

sql = GitHub::SQL.new("  SELECT * FROM repositories\n  WHERE source_id = :network_id AND parent_id IN :parent_ids\n", :parent_ids => parent_ids, :network_id => network_id)
sql.results
=> returns an Array of Arrays, one for each row
sql.hash_results
=> returns an Array of Hashes instead

Things to be aware of:

  • ‘nil` is always considered an error and not a usable value. If you need a

    SQL NULL, use the NULL constant instead.

  • Identical column names in SELECTs will be overridden: ‘SELECT t1.id, t2.id FROM…` will only return one value for `id`. To get more than one column of the same name, use aliases: `SELECT t1.id t1_id, t2.id t2_id FROM …`

  • Arrays are escaped as ‘(item, item, item)`. If you need to insert multiple rows (Arrays of Arrays), you must specify the bind value using GitHub::SQL::ROWS(array_of_arrays).

Defined Under Namespace

Classes: BadBind, BadValue, Error, Literal, Rows

Constant Summary collapse

NULL =

Public: prepackaged literal values.

Literal.new "NULL"
NOW = 
Literal.new "NOW()"

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(query = nil, binds = nil) ⇒ SQL

Public: Initialize a new instance.

query - An initial SQL string (default: “”). binds - A Hash of bind values keyed by Symbol (default: {}). There are

a couple exceptions.  If they clash with a bind value, add them
in a later #bind or #add call.

:connection     - An ActiveRecord Connection adapter.
:force_timezone - A Symbol describing the ActiveRecord default
                  timezone.  Either :utc or :local.



146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
 
# File 'lib/github/sql.rb', line 146

def initialize(query = nil, binds = nil)
  if query.is_a? Hash
    binds = query
    query = nil
  end

  @last_insert_id = nil
  @affected_rows  = nil
  @binds      = binds ? binds.dup : {}
  @query      = ""
  @connection = @binds.delete :connection
  @force_tz   = @binds.delete :force_timezone

  add query
end

Instance Attribute Details

#binds ⇒ Object (readonly)

Internal: A Symbol-Keyed Hash of bind values.



130
131
132
 
# File 'lib/github/sql.rb', line 130

def binds
  @binds
end

#query ⇒ Object (readonly)

Public: The SQL String to be executed. Modified in place.



133
134
135
 
# File 'lib/github/sql.rb', line 133

def query
  @query
end

Class Method Details

.BINARY(string) ⇒ Object

Public: Escape a binary SQL value

Used when a column contains binary data which needs to be escaped to prevent warnings from MySQL



89
90
91
 
# File 'lib/github/sql.rb', line 89

def self.BINARY(string)
  GitHub::SQL.LITERAL(GitHub::SQL.BINARY_LITERAL(string))
end

.BINARY_LITERAL(string) ⇒ Object

Public: Escape a binary SQL value, yielding a string which can be used as a literal in SQL

Performs the core escaping logic for binary strings in MySQL



97
98
99
 
# File 'lib/github/sql.rb', line 97

def self.BINARY_LITERAL(string)
  "x'#{string.unpack("H*")[0]}'"
end

.hash_results(sql, bindings = {}) ⇒ Object

Public: Create and execute a new SQL query, returning its hash_result rows.

sql - A SQL string. See GitHub::SQL#add for details. bindings - Optional bind values. See GitHub::SQL#add for details.

Returns an Array of result hashes.



368
369
370
 
# File 'lib/github/sql.rb', line 368

def self.hash_results(sql, bindings = {})
  new(sql, bindings).hash_results
end

.LITERAL(string) ⇒ Object

Public: Instantiate a literal SQL value.

WARNING: The given value is LITERALLY inserted into your SQL without being escaped, so use this with extreme caution.



81
82
83
 
# File 'lib/github/sql.rb', line 81

def self.LITERAL(string)
  Literal.new(string)
end

.results(sql, bindings = {}) ⇒ Object

Public: Create and execute a new SQL query, returning its result rows.

sql - A SQL string. See GitHub::SQL#add for details. bindings - Optional bind values. See GitHub::SQL#add for details.

Returns an Array of result arrays.



378
379
380
 
# File 'lib/github/sql.rb', line 378

def self.results(sql, bindings = {})
  new(sql, bindings).results
end

.ROWS(rows) ⇒ Object

Public: Instantiate a list of Arrays of SQL values for insertion.



102
103
104
 
# File 'lib/github/sql.rb', line 102

def self.ROWS(rows)
  Rows.new(rows)
end

.run(sql, bindings = {}) ⇒ Object

Public: Create and execute a new SQL query, ignoring results.

sql - A SQL string. See GitHub::SQL#add for details. bindings - Optional bind values. See GitHub::SQL#add for details.

Returns self.



358
359
360
 
# File 'lib/github/sql.rb', line 358

def self.run(sql, bindings = {})
  new(sql, bindings).run
end

.transaction ⇒ Object

Public: Run inside a transaction



71
72
73
74
75
 
# File 'lib/github/sql.rb', line 71

def self.transaction
  ActiveRecord::Base.connection.transaction do
    yield
  end
end

.value(sql, bindings = {}) ⇒ Object

Public: Create and execute a new SQL query, returning the value of the first column of the first result row.

sql - A SQL string. See GitHub::SQL#add for details. bindings - Optional bind values. See GitHub::SQL#add for details.

Returns a value or nil.



389
390
391
 
# File 'lib/github/sql.rb', line 389

def self.value(sql, bindings = {})
  new(sql, bindings).value
end

.values(sql, bindings = {}) ⇒ Object

Public: Create and execute a new SQL query, returning its values.

sql - A SQL string. See GitHub::SQL#add for details. bindings - Optional bind values. See GitHub::SQL#add for details.

Returns an Array of values.



399
400
401
 
# File 'lib/github/sql.rb', line 399

def self.values(sql, bindings = {})
  new(sql, bindings).values
end

Instance Method Details

#add(sql, extras = nil) ⇒ Object

Public: Add a chunk of SQL to the query. Any “:keyword” tokens in the SQL will be replaced with database-safe values from the current binds.

sql - A String containing a fragment of SQL. extras - A Hash of bind values keyed by Symbol (default: {}). These bind

values are only be used to interpolate this SQL fragment,and
aren't available to subsequent adds.

Returns self. Raises GitHub::SQL::BadBind for unknown keyword tokens.



172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
 
# File 'lib/github/sql.rb', line 172

def add(sql, extras = nil)
  return self if sql.nil? || sql.empty?

  query << " " unless query.empty?

  begin
    if @force_tz
      zone = ActiveRecord::Base.default_timezone
      ActiveRecord::Base.default_timezone = @force_tz
    end

    query << interpolate(sql.strip, extras)

    self
  ensure
    ActiveRecord::Base.default_timezone = zone if @force_tz
  end
end

#add_unless_empty(sql, extras = nil) ⇒ Object

Public: Add a chunk of SQL to the query, unless query generated so far is empty.

Example: use this for conditionally adding UNION when generating sets of SELECTs.

sql - A String containing a fragment of SQL. extras - A Hash of bind values keyed by Symbol (default: {}). These bind

values are only be used to interpolate this SQL fragment,and
aren't available to subsequent adds.

Returns self. Raises GitHub::SQL::BadBind for unknown keyword tokens.



202
203
204
205
 
# File 'lib/github/sql.rb', line 202

def add_unless_empty(sql, extras = nil)
  return self if query.empty?
  add sql, extras
end

#affected_rows ⇒ Object

Public: The number of affected rows for this connection.



208
209
210
 
# File 'lib/github/sql.rb', line 208

def affected_rows
  @affected_rows || connection.raw_connection.affected_rows
end

#bind(binds) ⇒ Object

Public: Add additional bind values to be interpolated each time SQL is added to the query.

hash - A Symbol-keyed Hash of new values.

Returns self.



218
219
220
221
 
# File 'lib/github/sql.rb', line 218

def bind(binds)
  self.binds.merge! binds
  self
end

#connection ⇒ Object

Internal: The object we use to execute SQL and retrieve results. Defaults to AR::B.connection, but can be overridden with a “:connection” key when initializing a new instance.



226
227
228
 
# File 'lib/github/sql.rb', line 226

def connection
  @connection || ActiveRecord::Base.connection
end

#found_rows ⇒ Object

Public: the number of rows found by the query.

Returns FOUND_ROWS() if a SELECT query included SQL_CALC_FOUND_ROWS. Raises if SQL_CALC_FOUND_ROWS was not present in the query.



234
235
236
237
 
# File 'lib/github/sql.rb', line 234

def found_rows
  raise "no SQL_CALC_FOUND_ROWS clause present" unless defined? @found_rows
  @found_rows
end

#hash_results ⇒ Object

Public: If the query is a SELECT, return an array of hashes instead of an array of arrays.



319
320
321
322
 
# File 'lib/github/sql.rb', line 319

def hash_results
  results
  @hash_results || @results
end

#interpolate(sql, extras = nil) ⇒ Object

Internal: Replace “:keywords” with sanitized values from binds or extras.



240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
 
# File 'lib/github/sql.rb', line 240

def interpolate(sql, extras = nil)
  sql.gsub(/:[a-z][a-z0-9_]*/) do |raw|
    sym = raw[1..-1].intern # O.o gensym

    if extras && extras.include?(sym)
      val = extras[sym]
    elsif binds.include?(sym)
      val = binds[sym]
    end

    raise BadBind.new raw if val.nil?

    sanitize val
  end
end

#last_insert_id ⇒ Object

Public: The last inserted ID for this connection.



257
258
259
 
# File 'lib/github/sql.rb', line 257

def last_insert_id
  @last_insert_id || connection.raw_connection.last_insert_id
end

#models(klass) ⇒ Object

Public: Map each row to an instance of an ActiveRecord::Base subclass.



262
263
264
265
266
267
268
269
270
271
272
273
274
275
 
# File 'lib/github/sql.rb', line 262

def models(klass)
  return @models if defined? @models
  return [] if frozen?

  # Use select_all to retrieve hashes for each row instead of arrays of values.
  @models = connection.
    select_all(query, "#{klass.name} Load via #{self.class.name}").
      collect! { |record| klass.send :instantiate, record }

  retrieve_found_row_count
  freeze

  @models
end

#results ⇒ Object

Public: Execute, memoize, and return the results of this query.



278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
 
# File 'lib/github/sql.rb', line 278

def results
  return @results if defined? @results
  return [] if frozen?

  begin
    if @force_tz
      zone = ActiveRecord::Base.default_timezone
      ActiveRecord::Base.default_timezone = @force_tz
    end

    case query
    when /\ADELETE/i
      @affected_rows = connection.delete(query, "#{self.class.name} Delete")

    when /\AINSERT/i
      @last_insert_id = connection.insert(query, "#{self.class.name} Insert")

    when /\AUPDATE/i
      @affected_rows = connection.update(query, "#{self.class.name} Update")

    when /\ASELECT/i
      # Why not execute or select_rows? Because select_all hits the query cache.
      @hash_results = connection.select_all(query, "#{self.class.name} Select").to_ary
      @results = @hash_results.map(&:values)

    else
      @results = connection.execute(query, "#{self.class.name} Execute").to_a
    end

    @results ||= []

    retrieve_found_row_count
    freeze

    @results
  ensure
    ActiveRecord::Base.default_timezone = zone if @force_tz
  end
end

#retrieve_found_row_count ⇒ Object

Internal: when a SQL_CALC_FOUND_ROWS clause is present in a SELECT query, retrieve the FOUND_ROWS() value to get a count of the rows sans any LIMIT/OFFSET clause.



346
347
348
349
350
 
# File 'lib/github/sql.rb', line 346

def retrieve_found_row_count
  if query =~ /\A\s*SELECT\s+SQL_CALC_FOUND_ROWS\s+/i
    @found_rows = connection.select_value "SELECT FOUND_ROWS()", self.class.name
  end
end

#row ⇒ Object

Public: Get first row of results.



325
326
327
 
# File 'lib/github/sql.rb', line 325

def row
  results.first
end

#run(sql = nil, extras = nil) ⇒ Object

Public: Execute, ignoring results. This is useful when the results of a query aren’t important, often INSERTs, UPDATEs, or DELETEs.

sql - An optional SQL string. See GitHub::SQL#add for details. extras - Optional bind values. See GitHub::SQL#add for details.

Returns self.



336
337
338
339
340
341
 
# File 'lib/github/sql.rb', line 336

def run(sql = nil, extras = nil)
  add sql, extras if !sql.nil?
  results

  self
end

#sanitize(value) ⇒ Object

Internal: Make ‘value` database-safe. Ish.



404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
 
# File 'lib/github/sql.rb', line 404

def sanitize(value)
  case value

  when Integer
    value.to_s

  when Numeric, String
    connection.quote value

  when Array
    raise BadValue.new(value, "an empty array") if value.empty?
    raise BadValue.new(value, "a nested array") if value.any? { |v| v.is_a? Array }

    "(" + value.map { |v| sanitize v }.join(", ") + ")"

  when Literal
    value.value

  when Rows # rows for insertion
    value.values.map { |v| sanitize v }.join(", ")

  when Class
    connection.quote value.name

  when DateTime, Time, Date
    connection.quote value.to_s(:db)

  when true
    connection.quoted_true

  when false
    connection.quoted_false

  when Symbol
    connection.quote value.to_s

  else
    raise BadValue, value
  end
end

#value ⇒ Object

Public: Get the first column of the first row of results.



446
447
448
 
# File 'lib/github/sql.rb', line 446

def value
  row && row.first
end

#value? ⇒ Boolean

Public: Is there a value?

Returns:

  • (Boolean) 


451
452
453
 
# File 'lib/github/sql.rb', line 451

def value?
  !value.nil?
end

#values ⇒ Object

Public: Get first column of every row of results.

Returns an Array or nil.



458
459
460
 
# File 'lib/github/sql.rb', line 458

def values
  results.map(&:first)
end