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:
-
`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).
Constants
- NOW
- NULL
Public: prepackaged literal values.
Attributes
Internal: A Symbol-Keyed Hash of bind values.
Public: The SQL
String to be executed. Modified in place.
Public Class Methods
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Public: Get first row of results.
# File lib/github/sql.rb, line 279 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.
# File lib/github/sql.rb, line 265 def run(sql = nil, extras = nil) add sql, extras if !sql.nil? results self end
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
Public: Run inside a transaction for the connection.
# File lib/github/sql.rb, line 301 def transaction(options = {}, &block) connection.transaction(**options, &block) end
Public: Get the first column of the first row of results.
# File lib/github/sql.rb, line 284 def value row && row.first end
Public: Is there a value?
# File lib/github/sql.rb, line 289 def value? !value.nil? end
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
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