class Qx

Public Class Methods

config(h) click to toggle source
# File lib/qx.rb, line 14
def self.config(h)
  @@type_map = h[:type_map]
end
delete_from(table_name) click to toggle source
# File lib/qx.rb, line 158
def self.delete_from(table_name)
  self.new(DELETE_FROM: Qx.quote_ident(table_name))
end
execute(expr, data={}, options={}) click to toggle source

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
execute_file(path, data={}, options={}) click to toggle source
# 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
execute_raw(expr, options={}) click to toggle source

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
fetch(table_name, data, options={}) click to toggle source

– 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
insert_into(table_name, cols=[]) click to toggle source
# 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
interpolate_expr(expr, data={}) click to toggle source

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
new(tree) click to toggle source

Qx.new, only used internally

# File lib/qx.rb, line 19
def initialize(tree)
  @tree = tree
  return self
end
parse(expr) click to toggle source

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
parse_select(expr) click to toggle source
# 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(val) click to toggle source

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
quote_ident(expr) click to toggle source

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
select(*cols) click to toggle source

– Top-level clauses

# File lib/qx.rb, line 139
def self.select(*cols)
  self.new(SELECT: cols)
end
transaction() { |block| ... } click to toggle source
# File lib/qx.rb, line 24
def self.transaction(&block)
  ActiveRecord::Base.transaction do
    yield block
  end
end
update(table_name) click to toggle source
# File lib/qx.rb, line 165
def self.update(table_name)
  self.new(UPDATE: Qx.quote_ident(table_name))
end

Public Instance Methods

add_join(*joins) click to toggle source
# 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
add_left_join(*joins) click to toggle source
# 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
add_select(*cols) click to toggle source
# File lib/qx.rb, line 146
def add_select(*cols)
  @tree[:SELECT].push(cols)
  self
end
and_having(expr, data={}) click to toggle source
# File lib/qx.rb, line 225
def and_having(expr, data={})
  @tree[:HAVING].push(Qx.interpolate_expr(expr, data))
  self
end
and_where(*clauses) click to toggle source
# 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
as(table_name) click to toggle source
# File lib/qx.rb, line 191
def as(table_name)
  @tree[:AS] = Qx.quote_ident(table_name)
  self
end
common_values(h) click to toggle source

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
delete_from(table_name) click to toggle source
# File lib/qx.rb, line 161
def delete_from(table_name)
  @tree[:DELETE_FROM] = Qx.quote_ident(table_name)
  self
end
distinct() click to toggle source
  • SELECT sub-clauses

# File lib/qx.rb, line 177
def distinct
  @tree[:DISTINCT] = true
  self
end
distinct_on(*cols) click to toggle source
# File lib/qx.rb, line 182
def distinct_on(*cols)
  @tree[:DISTINCT_ON] = cols
  self
end
ex(options={})
Alias for: execute
execute(options={}) click to toggle source

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
Also aliased as: ex
explain() click to toggle source
# File lib/qx.rb, line 322
def explain
  @tree[:EXPLAIN] = true
  self
end
from(expr) click to toggle source
# File lib/qx.rb, line 187
def from(expr)
  @tree[:FROM] = expr.is_a?(Qx) ? expr.parse : expr.to_s
  self
end
group_by(*cols) click to toggle source
# File lib/qx.rb, line 209
def group_by(*cols)
  @tree[:GROUP_BY] = cols.map{|c| c.to_s}
  self
end
having(expr, data={}) click to toggle source
# File lib/qx.rb, line 221
def having(expr, data={})
  @tree[:HAVING] = [Qx.interpolate_expr(expr, data)]
  self
end
insert_into(table_name, cols=[]) click to toggle source
# 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
join(*joins) click to toggle source
# File lib/qx.rb, line 240
def join(*joins)
  js = Qx.get_join_param(joins)
  @tree[:JOIN] = Qx.parse_joins(js)
  self
end
left_join(*joins) click to toggle source
# 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
limit(n) click to toggle source
# File lib/qx.rb, line 230
def limit(n)
  @tree[:LIMIT] = n.to_i.to_s
  self
end
offset(n) click to toggle source
# File lib/qx.rb, line 235
def offset(n)
  @tree[:OFFSET] = n.to_i.to_s
  self
end
order_by(*cols) click to toggle source
# 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
paginate(current_page, page_length) click to toggle source

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
parse() click to toggle source

An instance method version of the above

# File lib/qx.rb, line 88
def parse; Qx.parse(@tree); end
pp() click to toggle source
# 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
returning(*cols) click to toggle source
# File lib/qx.rb, line 308
def returning(*cols)
  @tree[:RETURNING] = cols.map{|c| Qx.quote_ident(c)}
  self
end
select(*cols) click to toggle source
# File lib/qx.rb, line 142
def select(*cols)
  @tree[:SELECT] = cols
  self
end
set(vals) click to toggle source

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
timestamps()
Alias for: ts
to_json(name) click to toggle source

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
tree() click to toggle source

– utils

# File lib/qx.rb, line 359
def tree; @tree; end
ts() click to toggle source

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
Also aliased as: timestamps
update(table_name) click to toggle source
# File lib/qx.rb, line 168
def update(table_name)
  @tree[:UPDATE] = Qx.quote_ident(table_name)
  self
end
values(vals) click to toggle source

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
where(*clauses) click to toggle source

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