class Qx
Public Class Methods
# File lib/qx.rb, line 14 def self.config(h) @@type_map = h[:type_map] end
# File lib/qx.rb, line 158 def self.delete_from(table_name) self.new(DELETE_FROM: Qx.quote_ident(table_name)) end
Can pass in an expression string or another Qx
object Qx.execute
(“SELECT id FROM table_name”, {format: 'csv'}) Qx.execute
(Qx.select
(“id”).from(“table_name”))
# File lib/qx.rb, line 100 def self.execute(expr, data={}, options={}) return expr.execute(data) if expr.is_a?(Qx) interpolated = Qx.interpolate_expr(expr, data) return self.execute_raw(interpolated, options) end
# File lib/qx.rb, line 127 def self.execute_file(path, data={}, options={}) Qx.execute_raw(Qx.interpolate_expr(File.open(path, 'r').read, data), options) end
options
verbose: print the query format: 'csv' | 'hash' give data csv style with Arrays -- good for exports or for saving memory
# File lib/qx.rb, line 109 def self.execute_raw(expr, options={}) puts expr if options[:verbose] if options[:copy_csv] expr = "COPY (#{expr}) TO '#{options[:copy_csv]}' DELIMITER ',' CSV HEADER" end result = ActiveRecord::Base.connection.execute(expr) result.map_types!(@@type_map) if @@type_map if options[:format] == 'csv' data = result.map{|h| h.values} data.unshift((result.first || {}).keys) else data = result.map{|h| h} end result.clear data = data.map{|row| apply_nesting(row)} if options[:nesting] return data end
– Helpers!
# File lib/qx.rb, line 329 def self.fetch(table_name, data, options={}) expr = Qx.select('*').from(table_name) if data.is_a?(Hash) expr = data.reduce(expr){|acc, pair| acc.and_where("#{pair.first} IN ($vals)", vals: Array(pair.last))} else expr = expr.where("id IN ($ids)", ids: Array(data)) end result = expr.execute(options) return result end
# File lib/qx.rb, line 150 def self.insert_into(table_name, cols=[]) self.new(INSERT_INTO: Qx.quote_ident(table_name), INSERT_COLUMNS: cols.map{|c| Qx.quote_ident(c)}) end
Safely interpolate some data into a SQL expression
# File lib/qx.rb, line 362 def self.interpolate_expr(expr, data={}) expr.to_s.gsub(/\$\w+/) do |match| val = data[match.gsub(/[ \$]*/, '').to_sym] vals = val.is_a?(Array) ? val : [val] vals.map{|x| Qx.quote(x)}.join(", ") end end
Qx.new
, only used internally
# File lib/qx.rb, line 19 def initialize(tree) @tree = tree return self end
Parse a Qx
expression tree into a single query string that can be executed www.postgresql.org/docs/9.0/static/sql-commands.html
# File lib/qx.rb, line 55 def self.parse(expr) if expr.is_a?(String) return expr # already parsed elsif expr.is_a?(Array) return expr.join(",") elsif expr[:INSERT_INTO] str = "INSERT INTO #{expr[:INSERT_INTO]} (#{expr[:INSERT_COLUMNS].join(", ")})" throw ArgumentError.new("VALUES (or SELECT) clause is missing for INSERT INTO") unless expr[:VALUES] || expr[:SELECT] if expr[:SELECT] str += ' ' + parse_select(expr) else str += " VALUES #{expr[:VALUES].map{|vals| "(#{vals.join(", ")})"}.join(", ")}" end str += " RETURNING " + expr[:RETURNING].join(", ") if expr[:RETURNING] elsif expr[:SELECT] str = parse_select(expr) elsif expr[:DELETE_FROM] str = 'DELETE FROM ' + expr[:DELETE_FROM] throw ArgumentError.new("WHERE clause is missing for DELETE FROM") unless expr[:WHERE] str += ' WHERE ' + expr[:WHERE].map{|w| "(#{w})"}.join(" AND ") str += " RETURNING " + expr[:RETURNING].join(", ") if expr[:RETURNING] elsif expr[:UPDATE] str = 'UPDATE ' + expr[:UPDATE] throw ArgumentError.new("SET clause is missing for UPDATE") unless expr[:SET] throw ArgumentError.new("WHERE clause is missing for UPDATE") unless expr[:WHERE] str += ' SET ' + expr[:SET] str += ' FROM ' + expr[:FROM] if expr[:FROM] str += ' WHERE ' + expr[:WHERE].map{|w| "(#{w})"}.join(" AND ") str += " RETURNING " + expr[:RETURNING].join(", ") if expr[:RETURNING] end return str end
# File lib/qx.rb, line 30 def self.parse_select(expr) str = 'SELECT' if expr[:DISTINCT_ON] str += " DISTINCT ON (#{expr[:DISTINCT_ON].map(&:to_s).join(', ')})" elsif expr[:DISTINCT] str += " DISTINCT" end str += ' ' + expr[:SELECT].map{|expr| expr.is_a?(Qx) ? expr.parse : expr}.join(", ") throw ArgumentError.new("FROM clause is missing for SELECT") unless expr[:FROM] str += ' FROM ' + expr[:FROM] str += expr[:JOIN].map{|from, cond| " JOIN #{from} ON #{cond}"}.join if expr[:JOIN] str += expr[:LEFT_JOIN].map{|from, cond| " LEFT JOIN #{from} ON #{cond}"}.join if expr[:LEFT_JOIN] str += ' WHERE ' + expr[:WHERE].map{|w| "(#{w})"}.join(" AND ") if expr[:WHERE] str += ' GROUP BY ' + expr[:GROUP_BY].join(", ") if expr[:GROUP_BY] str += ' HAVING ' + expr[:HAVING].map{|h| "(#{h})"}.join(" AND ") if expr[:HAVING] str += ' ORDER BY ' + expr[:ORDER_BY].map{|col, order| col + (order ? ' ' + order : '')}.join(", ") if expr[:ORDER_BY] str += ' LIMIT ' + expr[:LIMIT] if expr[:LIMIT] str += ' OFFSET ' + expr[:OFFSET] if expr[:OFFSET] str = "(#{str}) AS #{expr[:AS]}" if expr[:AS] str = "EXPLAIN #{str}" if expr[:EXPLAIN] return str end
Quote a string for use in sql to prevent injection or weird errors Always use this for all values! Just uses double-dollar quoting universally. Should be generally safe and easy. Will return an unquoted value it it's a Fixnum
# File lib/qx.rb, line 374 def self.quote(val) if val.is_a?(Qx) val.parse elsif val.is_a?(Fixnum) val.to_s elsif val.is_a?(Time) "'" + val.to_s + "'" # single-quoted times for a little better readability elsif val == nil "NULL" elsif !!val == val # is a boolean val ? "'t'" : "'f'" else return "$Q$" + val.to_s + "$Q$" end end
Double-quote sql identifiers (or parse Qx
trees for subqueries)
# File lib/qx.rb, line 391 def self.quote_ident(expr) if expr.is_a?(Qx) Qx.parse(expr.tree) else expr.to_s.split('.').map{|s| s == '*' ? s : "\"#{s}\""}.join('.') end end
– Top-level clauses
# File lib/qx.rb, line 139 def self.select(*cols) self.new(SELECT: cols) end
# File lib/qx.rb, line 24 def self.transaction(&block) ActiveRecord::Base.transaction do yield block end end
# File lib/qx.rb, line 165 def self.update(table_name) self.new(UPDATE: Qx.quote_ident(table_name)) end
Public Instance Methods
# File lib/qx.rb, line 245 def add_join(*joins) js = Qx.get_join_param(joins) @tree[:JOIN] ||= [] @tree[:JOIN].concat(Qx.parse_joins(js)) self end
# File lib/qx.rb, line 256 def add_left_join(*joins) js = Qx.get_join_param(joins) @tree[:LEFT_JOIN] ||= [] @tree[:LEFT_JOIN].concat(Qx.parse_joins(js)) self end
# File lib/qx.rb, line 146 def add_select(*cols) @tree[:SELECT].push(cols) self end
# File lib/qx.rb, line 225 def and_having(expr, data={}) @tree[:HAVING].push(Qx.interpolate_expr(expr, data)) self end
# File lib/qx.rb, line 202 def and_where(*clauses) ws = Qx.get_where_params(clauses) @tree[:WHERE] ||= [] @tree[:WHERE].concat(Qx.parse_wheres(ws)) self end
# File lib/qx.rb, line 191 def as(table_name) @tree[:AS] = Qx.quote_ident(table_name) self end
A convenience function for setting the same values across all inserted rows
# File lib/qx.rb, line 287 def common_values(h) cols = h.keys.map{|col| Qx.quote_ident(col)} data = h.values.map{|val| Qx.quote(val)} @tree[:VALUES] = @tree[:VALUES].map{|row| row.concat(data)} @tree[:INSERT_COLUMNS] = @tree[:INSERT_COLUMNS].concat(cols) self end
# File lib/qx.rb, line 161 def delete_from(table_name) @tree[:DELETE_FROM] = Qx.quote_ident(table_name) self end
-
SELECT sub-clauses
# File lib/qx.rb, line 177 def distinct @tree[:DISTINCT] = true self end
# File lib/qx.rb, line 182 def distinct_on(*cols) @tree[:DISTINCT_ON] = cols self end
Qx.select
(“id”).from(“supporters”).execute
# File lib/qx.rb, line 91 def execute(options={}) expr = Qx.parse(@tree).to_s.encode('UTF-8', 'binary', invalid: :replace, undef: :replace, replace: '') return Qx.execute_raw(expr, options) end
# File lib/qx.rb, line 322 def explain @tree[:EXPLAIN] = true self end
# File lib/qx.rb, line 187 def from(expr) @tree[:FROM] = expr.is_a?(Qx) ? expr.parse : expr.to_s self end
# File lib/qx.rb, line 209 def group_by(*cols) @tree[:GROUP_BY] = cols.map{|c| c.to_s} self end
# File lib/qx.rb, line 221 def having(expr, data={}) @tree[:HAVING] = [Qx.interpolate_expr(expr, data)] self end
# File lib/qx.rb, line 153 def insert_into(table_name, cols=[]) @tree[:INSERT_INTO] = Qx.quote_ident(table_name) @tree[:INSERT_COLUMNS] = cols.map{|c| Qx.quote_ident(c)} self end
# File lib/qx.rb, line 240 def join(*joins) js = Qx.get_join_param(joins) @tree[:JOIN] = Qx.parse_joins(js) self end
# File lib/qx.rb, line 251 def left_join(*joins) js = Qx.get_join_param(joins) @tree[:LEFT_JOIN] = Qx.parse_joins(js) self end
# File lib/qx.rb, line 230 def limit(n) @tree[:LIMIT] = n.to_i.to_s self end
# File lib/qx.rb, line 235 def offset(n) @tree[:OFFSET] = n.to_i.to_s self end
# File lib/qx.rb, line 214 def order_by(*cols) orders = /(asc)|(desc)( nulls (first)|(last))?/i # Sanitize out invalid order keywords @tree[:ORDER_BY] = cols.map{|col, order| [col.to_s, order.to_s.downcase.strip.match(order.to_s.downcase) ? order.to_s.upcase : nil]} self end
Given a Qx
expression, add a LIMIT and OFFSET for pagination
# File lib/qx.rb, line 341 def paginate(current_page, page_length) current_page = 1 if current_page.nil? || current_page < 1 self.limit(page_length).offset((current_page - 1) * page_length) end
An instance method version of the above
# File lib/qx.rb, line 88 def parse; Qx.parse(@tree); end
# File lib/qx.rb, line 346 def pp str = self.parse # Colorize some tokens # TODO indent by paren levels str = str .gsub(/(FROM|WHERE|VALUES|SET|SELECT|UPDATE|INSERT INTO|DELETE FROM)/){"#{$1}".blue.bold} .gsub(/(\(|\))/){"#{$1}".cyan} .gsub("$Q$", "'") return str end
# File lib/qx.rb, line 308 def returning(*cols) @tree[:RETURNING] = cols.map{|c| Qx.quote_ident(c)} self end
# File lib/qx.rb, line 142 def select(*cols) @tree[:SELECT] = cols self end
Vals can be a raw SQL string or a hash of data
# File lib/qx.rb, line 314 def set(vals) if vals.is_a? Hash vals = vals.map{|key, val| "#{Qx.quote_ident(key)} = #{Qx.quote(val)}"}.join(", ") end @tree[:SET] = vals.to_s self end
helpers for JSON conversion
# File lib/qx.rb, line 132 def to_json(name) name = name.to_s Qx.select("array_to_json(array_agg(row_to_json(#{name})))").from(self.as(name)) end
– utils
# File lib/qx.rb, line 359 def tree; @tree; end
add timestamps to an insert or update
# File lib/qx.rb, line 296 def ts now = "'#{Time.now.utc}'" if @tree[:VALUES] @tree[:INSERT_COLUMNS].concat ['created_at', 'updated_at'] @tree[:VALUES] = @tree[:VALUES].map{|arr| arr.concat [now, now]} elsif @tree[:SET] @tree[:SET] += ", updated_at = #{now}" end self end
# File lib/qx.rb, line 168 def update(table_name) @tree[:UPDATE] = Qx.quote_ident(table_name) self end
Allows three formats:
insert.values([[col1, col2], [val1, val2], [val3, val3]], options) insert.values([{col1: val1, col2: val2}, {col1: val3, co2: val4}], options) insert.values({col1: val1, col2: val2}, options) <- only for single inserts
# File lib/qx.rb, line 269 def values(vals) if vals.is_a?(Array) && vals.first.is_a?(Array) cols = vals.first data = vals[1..-1] elsif vals.is_a?(Array) && vals.first.is_a?(Hash) hashes = vals.map{|h| h.sort.to_h} # Make sure hash keys line up with all row data cols = hashes.first.keys data = hashes.map{|h| h.values} elsif vals.is_a?(Hash) cols = vals.keys data = [vals.values] end @tree[:VALUES] = data.map{|vals| vals.map{|d| Qx.quote(d)}} @tree[:INSERT_COLUMNS] = cols.map{|c| Qx.quote_ident(c)} self end
Clauses are pairs of expression and data
# File lib/qx.rb, line 197 def where(*clauses) ws = Qx.get_where_params(clauses) @tree[:WHERE] = Qx.parse_wheres(ws) self end