Skip to content

Instantly share code, notes, and snippets.

@rick rick/sql.rb forked from zerowidth/sql.md
Created Jul 3, 2014

Embed
What would you like to do?
# 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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.