Class: GitHub::SQL

Inherits:
Object
  • Object
show all
Defined in:
lib/github/sql.rb,
lib/github/sql/rows.rb,
lib/github/sql/errors.rb,
lib/github/sql/literal.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 for hash_results: ‘SELECT t1.id, t2.id FROM…` will only return one value for `id`. The second ID colum won’t be included in the hash:

    { “id” => “1” }

    To get more than one column of the same name, use aliases: ‘SELECT t1.id t1_id, t2.id t2_id FROM …`

    Calling ‘results` however will return an array with all the values:

    [1, 1]

  • 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_timezone = @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



61
62
63
 
# File 'lib/github/sql.rb', line 61

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



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

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.



94
95
96
 
# File 'lib/github/sql.rb', line 94

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.



53
54
55
 
# File 'lib/github/sql.rb', line 53

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.



104
105
106
 
# File 'lib/github/sql.rb', line 104

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

.ROWS(rows) ⇒ Object

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



74
75
76
 
# File 'lib/github/sql.rb', line 74

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.



84
85
86
 
# File 'lib/github/sql.rb', line 84

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

.transaction(options = {}, &block) ⇒ Object

Public: Run inside a transaction. Class version of this method only works if only one connection is in use. If passing connections to GitHub::SQL#initialize or overriding connection then you’ll need to use the instance version.



45
46
47
 
# File 'lib/github/sql.rb', line 45

def self.transaction(options = {}, &block)
  ActiveRecord::Base.connection.transaction(options, &block)
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.



115
116
117
 
# File 'lib/github/sql.rb', line 115

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.



125
126
127
 
# File 'lib/github/sql.rb', line 125

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
 
# File 'lib/github/sql.rb', line 172

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

  query << " " unless query.empty?
  query << interpolate(sql.strip, extras)

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



192
193
194
195
 
# File 'lib/github/sql.rb', line 192

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.



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

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.



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

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.



308
309
310
 
# File 'lib/github/sql.rb', line 308

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.



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

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.



273
274
275
276
 
# File 'lib/github/sql.rb', line 273

def hash_results
  results
  @hash_results || @results
end

#interpolate(sql, extras = nil) ⇒ Object

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



341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
 
# File 'lib/github/sql.rb', line 341

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.



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

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.



209
210
211
212
213
214
215
216
217
218
219
220
221
222
 
# File 'lib/github/sql.rb', line 209

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.



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
 
# File 'lib/github/sql.rb', line 225

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

  enforce_timezone do
    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.
      ar_results = connection.select_all(query, "#{self.class.name} Select")
      @hash_results = ar_results.to_ary
      @results = ar_results.rows
    else
      @results = connection.execute(query, "#{self.class.name} Execute").to_a
    end

    @results ||= []

    retrieve_found_row_count
    freeze

    @results
  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.



329
330
331
332
333
 
# File 'lib/github/sql.rb', line 329

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.



279
280
281
 
# File 'lib/github/sql.rb', line 279

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.



265
266
267
268
269
270
 
# File 'lib/github/sql.rb', line 265

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

  self
end

#sanitize(value) ⇒ Object

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



358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
 
# File 'lib/github/sql.rb', line 358

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
    enforce_timezone do
      connection.quote value.to_s(:db)
    end

  when true
    connection.quoted_true

  when false
    connection.quoted_false

  when Symbol
    connection.quote value.to_s

  else
    raise BadValue, value
  end
end

#transaction(options = {}, &block) ⇒ Object

Public: Run inside a transaction for the connection.



301
302
303
 
# File 'lib/github/sql.rb', line 301

def transaction(options = {}, &block)
  connection.transaction(options, &block)
end

#value ⇒ Object

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



284
285
286
 
# File 'lib/github/sql.rb', line 284

def value
  row && row.first
end

#value? ⇒ Boolean

Public: Is there a value?

Returns:

  • (Boolean) 


289
290
291
 
# File 'lib/github/sql.rb', line 289

def value?
  !value.nil?
end

#values ⇒ Object

Public: Get first column of every row of results.

Returns an Array or nil.



296
297
298
 
# File 'lib/github/sql.rb', line 296

def values
  results.map(&:first)
end