sql.rb

# https://gist.github.com/zerowidth/9696463
#
# GitHub::SQL - a helping hand for SQL in a rails app.
#
# Built for MySQL, adaptations and updates welcome.
#
# Authors/maintainers: @jbarnette, @zerowidth, et. al.
#
# The MIT License (MIT)
#
# Copyright (c) 2014 GitHub, Inc.
#
# Permission is hereby granted, free of charge, to any person obtaining a copy
# of this software and associated documentation files (the "Software"), to deal
# in the Software without restriction, including without limitation the rights
# to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
# copies of the Software, and to permit persons to whom the Software is
# furnished to do so, subject to the following conditions:
#
# The above copyright notice and this permission notice shall be included in
# all copies or substantial portions of the Software.
#
# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
# IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
# FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
# AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
# LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
# OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
# THE SOFTWARE.
#
module GitHub

  # 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.
  #
  # `nil` is always considered an error and not a usable value. If you
  # need a SQL NULL, use the NULL constant instead.
  class SQL

    # Internal: a SQL literal value.
    # If you must use this directly, do so with great care.
    class Literal < String
      def initialize(value)
        super
        freeze
      end

      def inspect
        "<#{self.class.name} #{to_s}>"
      end
    end

    # Public: prepackaged literal values.
    NULL    = Literal.new "NULL"
    NOW     = Literal.new "NOW()"
    DEFAULT = Literal.new "DEFAULT"

    # Public: A superclass for errors.
    class Error < RuntimeError
    end

    # Public: Raised when a bound ":keyword" value isn't available.
    class BadBind < Error
      def initialize(keyword)
        super "There's no bind value for #{keyword.inspect}"
      end
    end

    # Public: Raised when a bound value can't be sanitized.
    class BadValue < Error
      def initialize(value)
        super "Can't sanitize a #{value.class.name}: #{value.inspect}"
      end
    end

    # Internal: A Symbol-Keyed Hash of bind values.
    attr_reader :binds

    # Internal: The SQL String to be executed. Modified in place.
    attr_reader :query

    # Public: Initialize a new instance.
    #
    # query - An initial SQL string (default: "").
    # binds - A Hash of bind values keyed by Symbol (default: {}). One
    #         exception: A :connection value will be used as the database
    #         connection for this instance. If you need a bind value called
    #         ":connection" you can add it in a later bind call.
    def initialize(query = nil, binds = nil)
      if query.is_a? Hash
        binds = query
        query = nil
      end

      @binds      = binds ? binds.dup : {}
      @query      = ""
      @connection = @binds.delete :connection

      add query if !query.nil?
    end

    # 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.
    def add(sql, extras = nil)
      return self if sql.blank?

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

      self
    end

    # Public: The number of affected rows for this connection.
    def affected_rows
      @affected_rows || connection.raw_connection.affected_rows
    end

    # 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.
    def bind(binds)
      self.binds.merge! binds
      self
    end

    # 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.
    def connection
      @connection || ActiveRecord::Base.connection
    end

    # 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.
    def found_rows
      raise "no SQL_CALC_FOUND_ROWS clause present" unless defined? @found_rows
      @found_rows
    end

    # Internal: Replace ":keywords" with sanitized values from binds or extras.
    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

    # Public: The last inserted ID for this connection.
    def last_insert_id
      connection.raw_connection.last_id
    end

    # Public: Map each row to an instance of an AR::B subclass.
    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 GitHub::SQL").
          collect! { |record| klass.send :instantiate, record }

      freeze

      @models
    end

    # Public: Execute, memoize, and return the results of this query.
    def results
      return @results if defined? @results
      return [] if frozen?

      case query
      when /\ADELETE/i
        @affected_rows = connection.delete(query, "GitHub::SQL Delete")

      when /\AINSERT/i
        @last_insert_id = connection.insert(query, "GitHub::SQL Insert")

      when /\AUPDATE/i
        @affected_rows = connection.update(query, "GitHub::SQL Update")

      else
        @results = connection.execute(query, "GitHub::SQL").to_a
      end

      @results ||= []

      freeze

      @results
    end

    # Public: Get first row of results.
    def row
      results.first
    end

    # 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.
    def run(sql = nil, extras = nil)
      add sql, extras if !sql.nil?
      results

      self
    end

    # Internal: Make `value` database-safe. Ish.
    def sanitize(value)
      case value
      when Literal
        value.to_s

      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 Numeric, String
        connection.quote value

      when Symbol
        connection.quote value.to_s

      when Array
        mapped = value.map { |v| sanitize v }.join ", "

        # It's probably an Array of Arrays for a bulk INSERT or UPDATE.
        return mapped if value.first.is_a? Array

        # Otherwise it's probably OK to just wrap it in parens.
        "(" + mapped + ")"

      else
        raise BadValue, value
      end
    end

    # Public: Get the first column of the first row of results.
    def value
      row && row.first
    end

    # Public: Is there a value?
    def value?
      !value.nil?
    end

    # Public: Get first column of every row of results.
    #
    # Returns an Array or nil.
    def values
      results.map &:first
    end
  end
end