class GitHub::SQL

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:

Constants

NOW
NULL

Public: prepackaged literal values.

Attributes

binds[R]

Internal: A Symbol-Keyed Hash of bind values.

query[R]

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

Public Class Methods

BINARY(string) click to toggle source

Public: Escape a binary SQL value

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

# File lib/github/sql.rb, line 61
def self.BINARY(string)
  GitHub::SQL.LITERAL(GitHub::SQL.BINARY_LITERAL(string))
end
BINARY_LITERAL(string) click to toggle source

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

# File lib/github/sql.rb, line 69
def self.BINARY_LITERAL(string)
  "x'#{string.unpack("H*")[0]}'"
end
LITERAL(string) click to toggle source

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.

# File lib/github/sql.rb, line 53
def self.LITERAL(string)
  Literal.new(string)
end
ROWS(rows) click to toggle source

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

# File lib/github/sql.rb, line 74
def self.ROWS(rows)
  Rows.new(rows)
end
hash_results(sql, bindings = {}) click to toggle source

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.

# File lib/github/sql.rb, line 94
def self.hash_results(sql, bindings = {})
  new(sql, bindings).hash_results
end
new(query = nil, binds = nil) click to toggle source

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.
# 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
results(sql, bindings = {}) click to toggle source

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.

# File lib/github/sql.rb, line 104
def self.results(sql, bindings = {})
  new(sql, bindings).results
end
run(sql, bindings = {}) click to toggle source

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.

# File lib/github/sql.rb, line 84
def self.run(sql, bindings = {})
  new(sql, bindings).run
end
transaction(options = {}, &block) click to toggle source

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.

# File lib/github/sql.rb, line 45
def self.transaction(options = {}, &block)
  ActiveRecord::Base.connection.transaction(**options, &block)
end
value(sql, bindings = {}) click to toggle source

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.

# File lib/github/sql.rb, line 115
def self.value(sql, bindings = {})
  new(sql, bindings).value
end
values(sql, bindings = {}) click to toggle source

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.

# File lib/github/sql.rb, line 125
def self.values(sql, bindings = {})
  new(sql, bindings).values
end

Public Instance Methods

add(sql, extras = nil) click to toggle source

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.

# 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) click to toggle source

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.

# 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() click to toggle source

Public: The number of affected rows for this connection.

# File lib/github/sql.rb, line 313
def affected_rows
  @affected_rows || connection.raw_connection.affected_rows
end
bind(binds) click to toggle source

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.

# File lib/github/sql.rb, line 203
def bind(binds)
  self.binds.merge! binds
  self
end
connection() click to toggle source

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.

# File lib/github/sql.rb, line 308
def connection
  @connection || ActiveRecord::Base.connection
end
found_rows() click to toggle source

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.

# 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() click to toggle source

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

# File lib/github/sql.rb, line 273
def hash_results
  results
  @hash_results || @results
end
interpolate(sql, extras = nil) click to toggle source

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

# 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() click to toggle source

Public: The last inserted ID for this connection.

# File lib/github/sql.rb, line 336
def last_insert_id
  @last_insert_id || connection.raw_connection.last_insert_id
end
models(klass) click to toggle source

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

# 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}").
    map { |record| klass.send :instantiate, record }

  retrieve_found_row_count
  freeze

  @models
end
results() click to toggle source

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

# 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() click to toggle source

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.

# 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() click to toggle source

Public: Get first row of results.

# File lib/github/sql.rb, line 279
def row
  results.first
end
run(sql = nil, extras = nil) click to toggle source

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.

# File lib/github/sql.rb, line 265
def run(sql = nil, extras = nil)
  add sql, extras if !sql.nil?
  results

  self
end
sanitize(value) click to toggle source

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

# 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) click to toggle source

Public: Run inside a transaction for the connection.

# File lib/github/sql.rb, line 301
def transaction(options = {}, &block)
  connection.transaction(**options, &block)
end
value() click to toggle source

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

# File lib/github/sql.rb, line 284
def value
  row && row.first
end
value?() click to toggle source

Public: Is there a value?

# File lib/github/sql.rb, line 289
def value?
  !value.nil?
end
values() click to toggle source

Public: Get first column of every row of results.

Returns an Array or nil.

# File lib/github/sql.rb, line 296
def values
  results.map(&:first)
end

Private Instance Methods

enforce_timezone() { || ... } click to toggle source

Private: Forces ActiveRecord's default timezone for duration of block.

# File lib/github/sql.rb, line 404
def enforce_timezone(&block)
  begin
    if @force_timezone
      zone = ActiveRecord::Base.default_timezone
      ActiveRecord::Base.default_timezone = @force_timezone
    end

    yield if block_given?
  ensure
    ActiveRecord::Base.default_timezone = zone if @force_timezone
  end
end