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(<<-SQL, :parent_ids => parent_ids, :network_id => network_id)
  SELECT * FROM repositories
  WHERE source_id = :network_id AND parent_id IN :parent_ids
SQL
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.



135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
 
# File 'lib/github/sql.rb', line 135

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 if !query.nil?
end

Instance Attribute Details

#binds ⇒ Object (readonly)

Internal: A Symbol-Keyed Hash of bind values.



119
120
121
 
# File 'lib/github/sql.rb', line 119

def binds
  @binds
end

#query ⇒ Object (readonly)

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



122
123
124
 
# File 'lib/github/sql.rb', line 122

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



78
79
80
 
# File 'lib/github/sql.rb', line 78

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



86
87
88
 
# File 'lib/github/sql.rb', line 86

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.



353
354
355
 
# File 'lib/github/sql.rb', line 353

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.



70
71
72
 
# File 'lib/github/sql.rb', line 70

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.



363
364
365
 
# File 'lib/github/sql.rb', line 363

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

.ROWS(rows) ⇒ Object

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



91
92
93
 
# File 'lib/github/sql.rb', line 91

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.



343
344
345
 
# File 'lib/github/sql.rb', line 343

def self.run(sql, bindings = {})
  new(sql, bindings).run
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.



374
375
376
 
# File 'lib/github/sql.rb', line 374

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.



384
385
386
 
# File 'lib/github/sql.rb', line 384

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.



161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
 
# File 'lib/github/sql.rb', line 161

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

  query << " " unless query.empty?

  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

#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.



189
190
191
192
 
# File 'lib/github/sql.rb', line 189

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.



195
196
197
 
# File 'lib/github/sql.rb', line 195

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.



205
206
207
208
 
# File 'lib/github/sql.rb', line 205

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.



213
214
215
 
# File 'lib/github/sql.rb', line 213

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.



221
222
223
224
 
# File 'lib/github/sql.rb', line 221

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.



304
305
306
307
 
# File 'lib/github/sql.rb', line 304

def hash_results
  results
  @hash_results || @results
end

#interpolate(sql, extras = nil) ⇒ Object

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



227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
 
# File 'lib/github/sql.rb', line 227

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.



244
245
246
 
# File 'lib/github/sql.rb', line 244

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.



249
250
251
252
253
254
255
256
257
258
259
260
261
262
 
# File 'lib/github/sql.rb', line 249

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.



265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
 
# File 'lib/github/sql.rb', line 265

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

  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")
    @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

#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.



331
332
333
334
335
 
# File 'lib/github/sql.rb', line 331

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.



310
311
312
 
# File 'lib/github/sql.rb', line 310

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.



321
322
323
324
325
326
 
# File 'lib/github/sql.rb', line 321

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

  self
end

#sanitize(value) ⇒ Object

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



389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
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
 
# File 'lib/github/sql.rb', line 389

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.



431
432
433
 
# File 'lib/github/sql.rb', line 431

def value
  row && row.first
end

#value? ⇒ Boolean

Public: Is there a value?

Returns:

  • (Boolean) 


436
437
438
 
# File 'lib/github/sql.rb', line 436

def value?
  !value.nil?
end

#values ⇒ Object

Public: Get first column of every row of results.

Returns an Array or nil.



443
444
445
 
# File 'lib/github/sql.rb', line 443

def values
  results.map(&:first)
end