Skip to content

Instantly share code, notes, and snippets.

@zerowidth

zerowidth/sql.md

Last active April 11, 2017 18:31
Show Gist options
  • Save zerowidth/9696463 to your computer and use it in GitHub Desktop.
Save zerowidth/9696463 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. zerowidth revised this gist Apr 11, 2017. 2 changed files with 3 additions and 446 deletions.
    3 changes: 3 additions & 0 deletions sql.md
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,3 @@
    # DEPRECATED

    `GitHub::SQL` has been released as an officially-maintained project and ruby gem: [github/github-ds](https://github.com/github/github-ds).
    446 changes: 0 additions & 446 deletions sql.rb
    View file
    Original file line number Diff line number Diff line change
    @@ -1,446 +0,0 @@
    # 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.
    #
    # 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
    #
    # 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).
    #
    class SQL

    # Internal: a SQL literal value.
    class Literal
    # Public: the string value of this literal
    attr_reader :value

    def initialize(value)
    @value = value.to_s.dup.freeze
    end

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

    # Internal: a list of arrays of values for insertion into SQL.
    class Rows
    # Public: the Array of row values
    attr_reader :values

    def initialize(values)
    unless values.all? { |v| v.is_a? Array }
    raise ArgumentError, "cannot instantiate SQL rows with anything but arrays"
    end
    @values = values.dup.freeze
    end

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

    # 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.
    def self.LITERAL(string)
    Literal.new(string)
    end

    # Public: Escape a binary SQL value
    #
    # Used when a column contains binary data which needs to be escaped
    # to prevent warnings from MySQL
    def self.BINARY(string)
    GitHub::SQL.LITERAL "x'#{string.unpack("H*")[0]}'"
    end

    # Public: Instantiate a list of Arrays of SQL values for insertion.
    def self.ROWS(rows)
    Rows.new(rows)
    end

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

    # 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, description=nil)
    description ||= "a #{value.class.name}"
    super "Can't sanitize #{description}: #{value.inspect}"
    end
    end

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

    # Public: 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: {}). 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.
    #
    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
    @force_tz = @binds.delete :force_timezone

    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?

    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 zone
    end

    # 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.
    def add_unless_empty(sql, extras = nil)
    return self if query.empty?
    add sql, extras
    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_insert_id
    end

    # Public: Map each row to an instance of an ActiveRecord::Base 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 #{self.class.name}").
    collect! { |record| klass.send :instantiate, record }

    retrieve_found_row_count
    freeze

    @models
    end

    # Public: Execute, memoize, and return the results of this query.
    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.
    @results = connection.select_all(query, "#{self.class.name} Select").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 zone
    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: 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.
    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

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

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

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

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

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

    when Fixnum
    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

    # 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
  2. zerowidth revised this gist Aug 22, 2016. 1 changed file with 25 additions and 13 deletions.
    38 changes: 25 additions & 13 deletions sql.rb
    View file
    Original file line number Diff line number Diff line change
    @@ -96,6 +96,14 @@ def self.LITERAL(string)
    Literal.new(string)
    end

    # Public: Escape a binary SQL value
    #
    # Used when a column contains binary data which needs to be escaped
    # to prevent warnings from MySQL
    def self.BINARY(string)
    GitHub::SQL.LITERAL "x'#{string.unpack("H*")[0]}'"
    end

    # Public: Instantiate a list of Arrays of SQL values for insertion.
    def self.ROWS(rows)
    Rows.new(rows)
    @@ -118,8 +126,9 @@ def initialize(keyword)

    # 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}"
    def initialize(value, description=nil)
    description ||= "a #{value.class.name}"
    super "Can't sanitize #{description}: #{value.inspect}"
    end
    end

    @@ -231,7 +240,7 @@ def found_rows

    # Internal: Replace ":keywords" with sanitized values from binds or extras.
    def interpolate(sql, extras = nil)
    sql.gsub /:[a-z][a-z0-9_]+/ do |raw|
    sql.gsub /:[a-z][a-z0-9_]*/ do |raw|
    sym = raw[1..-1].intern # O.o gensym

    if extras && extras.include?(sym)
    @@ -248,7 +257,7 @@ def interpolate(sql, extras = nil)

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

    # Public: Map each row to an instance of an ActiveRecord::Base subclass.
    @@ -378,6 +387,18 @@ def self.values(sql, bindings={})
    def sanitize(value)
    case value

    when Fixnum
    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

    @@ -396,18 +417,9 @@ def sanitize(value)
    when false
    connection.quoted_false

    when Numeric, String
    connection.quote value

    when Symbol
    connection.quote value.to_s

    when Array
    # disallow nested arrays:
    raise BadValue, value if value.any? { |v| v.is_a? Array }

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

    else
    raise BadValue, value
    end
  3. zerowidth revised this gist Mar 4, 2015. 1 changed file with 162 additions and 27 deletions.
    189 changes: 162 additions & 27 deletions sql.rb
    View file
    Original file line number Diff line number Diff line change
    @@ -32,23 +32,75 @@ module GitHub
    # 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.
    # 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
    #
    # 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).
    #
    class SQL

    # Internal: a SQL literal value.
    # If you must use this directly, do so with great care.
    class Literal < String
    class Literal
    # Public: the string value of this literal
    attr_reader :value

    def initialize(value)
    super
    freeze
    @value = value.to_s.dup.freeze
    end

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

    # Internal: a list of arrays of values for insertion into SQL.
    class Rows
    # Public: the Array of row values
    attr_reader :values

    def initialize(values)
    unless values.all? { |v| v.is_a? Array }
    raise ArgumentError, "cannot instantiate SQL rows with anything but arrays"
    end
    @values = values.dup.freeze
    end

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

    # 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.
    def self.LITERAL(string)
    Literal.new(string)
    end

    # Public: Instantiate a list of Arrays of SQL values for insertion.
    def self.ROWS(rows)
    Rows.new(rows)
    end

    # Public: prepackaged literal values.
    NULL = Literal.new "NULL"
    NOW = Literal.new "NOW()"
    @@ -74,16 +126,20 @@ def initialize(value)
    # Internal: A Symbol-Keyed Hash of bind values.
    attr_reader :binds

    # Internal: The SQL String to be executed. Modified in place.
    # Public: 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.
    # 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.
    #
    def initialize(query = nil, binds = nil)
    if query.is_a? Hash
    binds = query
    @@ -93,6 +149,7 @@ def initialize(query = nil, binds = nil)
    @binds = binds ? binds.dup : {}
    @query = ""
    @connection = @binds.delete :connection
    @force_tz = @binds.delete :force_timezone

    add query if !query.nil?
    end
    @@ -111,9 +168,33 @@ 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 zone
    end

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

    # Public: The number of affected rows for this connection.
    @@ -170,14 +251,14 @@ def last_insert_id
    connection.raw_connection.last_id
    end

    # Public: Map each row to an instance of an AR::B subclass.
    # Public: Map each row to an instance of an ActiveRecord::Base 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").
    select_all(query, "#{klass.name} Load via #{self.class.name}").
    collect! { |record| klass.send :instantiate, record }

    retrieve_found_row_count
    @@ -191,18 +272,27 @@ 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, "GitHub::SQL Delete")
    @affected_rows = connection.delete(query, "#{self.class.name} Delete")

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

    when /\AUPDATE/i
    @affected_rows = connection.update(query, "GitHub::SQL Update")
    @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.
    @results = connection.select_all(query, "#{self.class.name} Select").map(&:values)

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

    @results ||= []
    @@ -211,6 +301,8 @@ def results
    freeze

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

    # Public: Get first row of results.
    @@ -237,21 +329,66 @@ def run(sql = nil, extras = nil)
    # LIMIT/OFFSET clause.
    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()", "GitHub::SQL"
    @found_rows = connection.select_value "SELECT FOUND_ROWS()", self.class.name
    end
    end

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

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

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

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

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

    when Literal
    value.to_s
    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
    connection.quote value.to_s(:db)

    when true
    connection.quoted_true
    @@ -266,13 +403,10 @@ def sanitize(value)
    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
    # disallow nested arrays:
    raise BadValue, value if value.any? { |v| v.is_a? Array }

    # Otherwise it's probably OK to just wrap it in parens.
    "(" + mapped + ")"
    "(" + value.map { |v| sanitize v }.join(", ") + ")"

    else
    raise BadValue, value
    @@ -295,5 +429,6 @@ def value?
    def values
    results.map &:first
    end

    end
    end
  4. zerowidth created this gist Mar 21, 2014.
    299 changes: 299 additions & 0 deletions sql.rb
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,299 @@
    # 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()"

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

    retrieve_found_row_count
    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 ||= []

    retrieve_found_row_count
    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: 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.
    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()", "GitHub::SQL"
    end
    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