sqlrocks.lua

--
-- Sqlrocks, is a transparent, schemaless library for building and composing SQL statements.
--
-- @author    leite (xico@simbio.se)
-- @license   MIT
-- @copyright Simbiose 2015

local string, math, table = require 'string', require 'math', require 'table'

local find, match, gmatch, gsub, sub, lower, upper, format, max, floor, concat, insert,
  type, pairs, ipairs, tostring, select, next, error, rawset, setmetatable, getmetatable =
  string.find, string.match, string.gmatch, string.gsub, string.sub, string.lower,
  string.upper, string.format, math.max, math.floor, table.concat, table.insert, type,
  pairs, ipairs, tostring, select, next, error, rawset, setmetatable, getmetatable

string, math, table = nil, nil, nil

local reserved = {
  abort=true, action=true, add=true, after=true, all=true, alter=true, analyse=true,
  analyze=true, ['and']=true, any=true, array=true, as=true, asc=true,
  asymmetric=true, attach=true, authorization=true, autoincrement=true, before=true,
  begin=true, between=true, both=true, by=true, cascade=true, case=true, cast=true,
  check=true, collate=true, collation=true, column=true, commit=true, conflict=true,
  constraint=true, create=true, cross=true, current_catalog=true, current_date=true,
  current_role=true, current_time=true, current_timestamp=true, current_user=true,
  database=true, default=true, deferrable=true, deferred=true, delete=true,
  desc=true, detach=true, distinct=true, ['do']=true, drop=true, each=true,
  ['else']=true, ['end']=true, escape=true, except=true, exclusive=true, exists=true,
  explain=true, fail=true, ['false']=true, fetch=true, ['for']=true, foreign=true,
  freeze=true, from=true, full=true, glob=true, grant=true, group=true, having=true,
  ['if']=true, ignore=true, ilike=true, immediate=true, ['in']=true, index=true,
  indexed=true, initially=true, inner=true, insert=true, instead=true,
  intersect=true, into=true, is=true, isnull=true, join=true, key=true, lateral=true,
  leading=true, left=true, like=true, limit=true, localtime=true,
  localtimestamp=true, match=true, natural=true, no=true, ['not']=true, notnull=true,
  null=true, of=true, offset=true, on=true, only=true, ['or']=true, order=true,
  outer=true, over=true, overlaps=true, placing=true, plan=true, pragma=true,
  primary=true, query=true, raise=true, references=true, regexp=true, reindex=true,
  release=true, rename=true, replace=true, restrict=true, returning=true, right=true,
  rollback=true, row=true, savepoint=true, ['select']=true, session_user=true,
  set=true, similar=true, some=true, symmetric=true, table=true, temp=true,
  temporary=true, ['then']=true, trailing=true, transaction=true, trigger=true,
  ['true']=true, union=true, unique=true, update=true, to=true, user=true, using=true,
  vacuum=true, values=true, variadic=true, verbose=true, view=true, virtual=true,
  when=true, where=true, window=true, with=true}

local compounds = {
  union='UNION', unionAll='UNION ALL', intersect='INTERSECT',
  intersectAll='INTERSECT ALL', minus='MINUS', minusAll='MINUS ALL',
  except='EXCEPT', exceptAll='EXCEPT ALL'}

local EMPTY, SPACE, DOT, COMA, COMA_SPC, Q_MARK, STAR, PAR_SPC, LT, LE, NOT_EQ,
  EQ, GT, GE, DOLLAR, QUOTE, QQ, ESP_AND, ESP_AS, _INDEX, _HAVING, I, _RETURNING,
  USING, WHERE, ALLSPACE, AND, ANYSPACE, COLS, CROSS, DELSPACEFROMSPACE, DEL,
  FOR_UPDATE, FOR_UPDATE_TBLS, FULL, FUNCTION, GROUPBY, GROUP_BY, HASH, INDEX,
  INNER, INSERT, IS_NOT_NULL, IS_NULL, LEFT, NO_WAIT, NUMBER, OR_ABORT, OR_FAIL,
  OR_IGNORE, OR_REPLACE, OR_ROLLBACK, OR, ORDERBY, ORDER_BY, RETURNING, RIGHT,
  SELECT, _SELECT, STRING, TABLE, TBLS, TEMP, UPDATE, _UPDATE, VALS_P =
  '', ' ', '.', ',', ', ', '?', '*', ') ', '<', '<=', '<>', '=', '>', '>=', '$',
  "'", "''", ' AND ', ' AS ', '__index', '_having', '_i', '_returning', '_using',
  '_where', 'ALL ', 'AND', 'ANY ', 'cols', 'CROSS', 'DELETE FROM ', 'delete',
  'FOR UPDATE ', 'for_update_tbls', 'FULL', 'function', 'GROUP BY ', 'group_by',
  'Hash', 'index', 'INNER', 'INSERT ', 'IS NOT NULL', 'IS NULL', 'LEFT', 'NO WAIT ',
  'number',  'OR ABORT ', 'OR FAIL ', 'OR IGNORE ', 'OR REPLACE ', 'OR ROLLBACK ',
  'OR', 'ORDER BY ', 'order_by', 'RETURNING ', 'RIGHT', 'SELECT ', 'select', 'string',
  'table', 'tbls', 'TEMP ', 'UPDATE ', 'update', 'VALUES ('

local patterns = {
  DQS = '"%s"', CAP1 = '([^%.]+)', DPS = '(%s)', SSPACE = '%s ', SSPACESSSPACE = '%s %s ',
  SSPACESSSPACESS = '%s %s %s%s', SSPACESSUNDERS = '%s %s_%s', SSPACES = '%s %s',
  SEQS = '%s = %s', SEQSS = '%s = %s%s', S_BTW_S_AND_S = '%s BETWEEN %s AND %s',
  S_IN_S = '%s IN (%s)', S_JOIN_S_ON_S = '%s JOIN %s ON %s', S_LIKE_SS = '%s LIKE %s%s',
  S_SET = '%s SET ', S_S = '%s_%s', CAP2 = '%s*([^%s,$]+)%s*,?$?', SS = '%s%s',
  S7 = '%s%s%s%s%s%s%s', EXISTS_S = 'EXISTS (%s)', FROM_S = 'FROM %s ', HVG_S = 'HAVING %s',
  CAP3 = '^([%w%_%-%d]*)%.?([%w%_%-%d]*)%.?([%w%_%-%d]*)%s?[aA]?[sS]?%s?([[%w%_%-%d]*]?)$',
  EXPECT_VAL_GOT = 'expecting %d values, got %d', INTO_S_P = 'INTO %s (', NOT_S = 'NOT %s',
  INTO_SS = 'INTO %s%s ', LIMIT_S = 'LIMIT %s ', OFFSET_S = 'OFFSET %s ',
  USING_S = 'USING %s ', WHERE_S = 'WHERE %s', ESCAPE_Q_S = " ESCAPE '%s'", Q_S_Q = "'%s'",
  EXPECT_HASH = "expecting hash, got a '%s'", UNSP_N_D =
  'Unsupported number of tables in pseudo-view: %d'
}

--
--
--
--
--

local class

do

  local classes = setmetatable({}, {__mode='k'})

  local function deep_copy(t, dest)
    local t, r = t or {}, dest or {}
    for k,v in pairs(t) do
      if type(v) == TABLE and k ~= _INDEX then
        r[k] = deep_copy(v)
      else
        r[k] = v
      end
    end
    return r
  end

  local function is(self, klass)
    local m = getmetatable(self)
    while m do
      if m == klass then return true end
      m = m.super
    end
    return false
  end

  local function include(self, ...)
    if not ... then return end
    for k,v in pairs(...) do
      if self[k] then self[k] = nil end
      self[k] = v
    end
  end

  local function extends(self, ...)
    local meta = {}
    if ... then deep_copy(..., deep_copy(self, meta)) else deep_copy(self, meta) end
    setmetatable(meta, getmetatable(self))
    return meta
  end

  local function new_index(self, key, value)
    rawset(self, key, value)
    for i=1, #classes[self] do
      classes[self][i][key] = value
    end
  end

  class = function (base)
    local c, mt = {}, {}
    if type(base) == TABLE then
      for i,v in pairs(base) do c[i] = v end
      c.super = base
    end

    c.__index     = c
    c.is          = is
    c.extends     = extends
    c.include     = include
    mt.__newindex = new_index
    classes[c]    = {}

    mt.__call = function(self, ...)
      local obj = {}
      setmetatable(obj, c)
      if c.__init then c.__init(obj, ...) end
      return obj
    end

    if c.super then
      insert(classes[c.super], c)
    end

    setmetatable(c, mt)
    return c
  end
end

--
--
--
--
--

local Hash = class()
Hash.__name = 'Hash'

function Hash:__init(bootstrap)
  rawset(self, I, 0)
  rawset(self, 'index', {})

  if TABLE ~= type(bootstrap) then return end
  for k,v in pairs(bootstrap) do self.index[k] = v end
end

function Hash:__newindex(key, val)
  if not self.index[key] then
    insert(self.index, key)
  end

  if nil == val then
    for i=1, #self.index do
      if self.index[i] == key then
        remove(self.index, i)
        self._i = self._i > 0 and (self._i - 1) or self._i
      end
    end
  end

  self.index[key] = val
end

function Hash:__index(key)
  return rawget(getmetatable(self), key) or self.index[key]
end

function Hash:__pairs()
  self._i = 0
  local function iter(self)
    self._i = self._i + 1
    local k = self.index[self._i]
    if k then return k, self.index[k] end
  end
  return iter, self
end

function Hash:add(index, val)
  if NUMBER == type(index) then
    insert(self.index[self.index[index]], val)
  else
    insert(self.index[index], val)
  end
end

function Hash:set(index, val)
  if self.index[index] then
    self.index[self.index[index]] = val return true
  end
  return false
end

function Hash:get(index)
  if self.index[index] then return self.index[self.index[index]] end
  return nil
end

function Hash:__len()
  return #self.index
end

function Hash:__sub(hash)
  if not(TABLE == type(hash) and hash.__name == 'Hash') then
    error(format(patterns.EXPECT_HASH, type(hash)))
  end
  local new_index = {}
  for i=1, #self.index do
    if hash.index[self.index[i]] then
      insert(new_index, self.index[i])
      new_index[self.index[i]] = self.index[self.index[i]]
    end
  end
  return getmetatable(self)(new_index)
end

function Hash:__add(hash)
  if not(TABLE == type(hash) and hash.__name == 'Hash') then
    error(format(patterns.EXPECT_HASH, type(hash)))
  end
  local new_hash = getmetatable(self)(self.index)
  for i=1, #hash.index do
    if not new_hash.index[hash.index[i]] then
      new_hash[hash.index[i]] = hash.index[hash.index[i]]
    end
  end
  return new_hash
end

--
--
--
--
--

local function clone(orig)
  local orig_type, copy = type(orig), {}
  --local copy
  if TABLE == orig_type and orig.is then
    copy = orig:extends()
  elseif TABLE == orig_type then
    --copy = {}
    for orig_key, orig_value in next, orig, nil do
      copy[clone(orig_key)] = clone(orig_value)
    end
  else
    copy = orig
  end
  return copy
end

local function is_array(t)
  if not t or type(t) ~= TABLE then return false end
  local i = 0
  for _ in pairs(t) do
    i = i + 1
    if t[i] == nil then return false end
  end
  return true
end

local function args2array(args)
  if not args then return {} end
  if #args==1 and is_array(args[1]) then
    return args[1]
  elseif STRING == type(args[1]) and find(args[1], COMA, 1, true) ~= nil then
    local results = {}
    gsub(args[1], patterns.CAP2, function(st) insert(results, st) end)
    return results
  elseif TABLE == type(args[1]) and #args==1 then
    return args[1]
  else
    return args
  end
end

local function args2object(args)
  if TABLE == type(args[1]) then return args[1] end
  local obj = {}
  if nil ~= args[1] then obj[args[1]] = args[2] end
  return obj
end

local function check_same_len(length, expected)
  assert(length == expected, format(patterns.EXPECT_VAL_GOT, length, expected))
end

local function convert(col, new_aliases)
  local col_parts = {}
  for key in gmatch(col, "([^%.]+)") do insert(col_parts, key) end
  if #col_parts == 1 then return col end

  local tbl_ix    = #col_parts - 1
  local tbl_alias = col_parts[tbl_ix]
  if new_aliases[tbl_alias] then
    col_parts[tbl_ix] = new_aliases[tbl_alias]
    return concat(col_parts, DOT)
  end
  return col
end

local function convert_expr(expr, new_aliases)
  if expr.col then expr.col = convert(expr.col, new_aliases) end
  if expr.expressions then
    for i=1, #expr.expressions do
      convert_expr(expr.expressions[i], new_aliases)
    end
  end
end

local function namespaced_on(on, new_aliases)
  local namespace = {}
  for key, value in pairs(on) do
    namespace[convert(key, new_aliases)] = convert(value, new_aliases)
  end
  return namespace
end

-- define basic class-like metatables
local Select, Insert, Update, Delete
local Sql, Val, Statement, Join, Group, Not, Binary, Like, Between, Unary,
  In, Exists = class(), class(), class(), class(), class(), class(), class(),
  class(), class(), class(), class(), class()

Sql.__name, Val.__name, Statement.__name, Join.__name, Group.__name, Not.__name,
  Binary.__name, Like.__name, Between.__name, Unary.__name,
  In.__name, Exists.__name = 'Sql', 'Val', 'Statement', 'Join', 'Group', 'Not',
  'Binary', 'Like', 'Between', 'Unary', 'In', 'Exists'

function get_alias(tbl)
  local sep    = ESP_AS
  local sep_ix = find(tbl, sep, 1, true)
  if not sep_ix then
    sep    = SPACE
    sep_ix = find(tbl, sep, 1, true)
  end
  if sep_ix then return sub(tbl, sep_ix + #sep, -1) end
  return tbl
end

function get_table(tbl)
  local sep_ix = find(tbl, SPACE, 1, true)
  if sep_ix then return sub(tbl, 1, sep_ix - 1) end
  return tbl
end

local function is_expr(e)
  return e.is and (e:is(Group) or e:is(Not) or e:is(Binary) or e:is(Unary) or
    e:is(In) or e:is(Like) or e:is(Between) or e:is(Exists))
end

local function handle_value(val, opts)
  local _type = type(val)
  if TABLE == _type and val.is then
    if val:is(Statement) then return format(patterns.DPS, val:_toString(opts)) end
    if val:is(Sql) then return tostring(val) end
  end

  if opts and opts.parameterized then
    insert(opts.values, val)
    local prefix = EMPTY
    if opts.placeholder and opts.placeholder == '%' then
      prefix = '%s'
      opts.value_ix = opts.value_ix + 1
      return prefix
    else
      prefix = format(patterns.SS, (opts.placeholder or DOLLAR), tostring(opts.value_ix))
      opts.value_ix = opts.value_ix + 1
      return prefix
    end
  end

  if STRING == _type or TABLE == _type then
    return format(
        patterns.Q_S_Q,
        gsub(tostring(TABLE == _type and concat(val, COMA) or val), QUOTE, QQ)
      )
  end

  return val
end

local function handle_column(expr, opts)
  if expr.is then
    if expr:is(Statement) then return format(patterns.DPS, expr:_toString(opts)) end
    if expr:is(Val) then return handle_value(expr.val, opts) end
  end

  local db, tb, fd, al = match(expr, patterns.CAP3)
  if db then
    local tb_dot, db_dot = DOT, DOT
    if fd == EMPTY and tb == EMPTY then
      fd = (reserved[lower(db)] and format(patterns.DQS, db) or db)
      db = EMPTY db_dot = EMPTY tb_dot = EMPTY
    elseif fd == EMPTY and tb ~= EMPTY then
      fd, tb = (reserved[lower(tb)] and format(patterns.DQS, tb) or tb),
        (reserved[lower(db)] and format(patterns.DQS, db) or db) db = EMPTY db_dot = EMPTY
    else
      fd, tb, db = (reserved[lower(fd)] and format(patterns.DQS, fd) or fd),
        (reserved[lower(tb)] and format(patterns.DQS, tb) or tb),
        (reserved[lower(db)] and format(patterns.DQS, db) or db)
    end
    return format(
      patterns.S7, db, db_dot, tb, tb_dot, fd, (al == EMPTY and EMPTY or ESP_AS), al)
  end
  return expr
end

--
--
--
--
--

local function handles(res, data, opts, ...)
  local both, value, appends, addendum = ...
  both, value, appends, addendum =
    (both or false), (value or false), (appends or COMA_SPC), (addendum or SPACE)

  if both then
    for k,v in pairs(data) do
      insert(res, format(
        patterns.SEQSS, handle_column(k, opts), handle_value(v, opts), appends))
    end
    res[#res] = format(patterns.SS, sub(res[#res], 1, ((#appends + 1) * -1)), addendum)
    return res
  end

  if value then
    if data.is and data.is(data, Hash) and TABLE == type(data.get(data, 1)) then
      for i=1, #data.get(data, 1) do
        for k,v in pairs(data) do
          insert(res, format(patterns.SS, handle_value(v[i], opts), appends))
        end
        res[#res] = format('%s), (', sub(res[#res], 1, -3))
      end
      res[#res] = sub(res[#res], 1, -5)
      return res
    end
    for k, v in pairs(data) do
      insert(res, format(patterns.SS, handle_value(v, opts), appends))
    end
  else
    for key in pairs(data) do
      if NUMBER == type(key) then
        for k,v in pairs(data) do
          insert(res, format(patterns.SS, handle_column(v, opts), appends))
        end
      else
        for k in pairs(data) do
          insert(res, format(patterns.SS, handle_column(k, opts), appends))
        end
      end
      break
    end
  end
  res[#res] = format(patterns.SS, sub(res[#res], 1, ((#appends + 1) * -1)), addendum)
  return res
end

--
--
--
--
--

local function _and(...)            return Group('AND', args2array({...}))  end
local function _or(...)             return Group(OR, args2array({...}))   end
local function isNull(col)          return Unary('IS NULL', col)            end
local function isNotNull(col)       return Unary('IS NOT NULL', col)        end
local function innerJoin(self, ...) return self:_addJoins({...}, 'INNER')   end
local function leftJoin(self, ...)  return self:_addJoins({...}, 'LEFT')    end
local function rightJoin(self, ...) return self:_addJoins({...}, RIGHT)   end
local function fullJoin(self, ...)  return self:_addJoins({...}, FULL)    end
local function where(self, ...) return self:_addExpression({...}, '_where') end
local function group(self, ...) return self:_addListArgs({...}, GROUP_BY) end
local function order(self, ...) return self:_addListArgs({...}, ORDER_BY) end
local function orReplace(self)    self._or = OR_REPLACE  return self end
local function orRollback(self)   self._or = OR_ROLLBACK return self end
local function orAbort(self)      self._or = OR_ABORT    return self end
local function orFail(self)       self._or = OR_FAIL     return self end
local function orIgnore(self)     self._or = OR_IGNORE   return self end
local function eq(col, val)       return Binary(EQ, col, val)          end
local function eqAll(col, val)    return Binary(EQ, col, val, 'ALL ')  end
local function eqAny(col, val)    return Binary(EQ, col, val, 'ANY ')  end
local function notEq(col, val)    return Binary(NOT_EQ, col, val)         end
local function notEqAll(col, val) return Binary(NOT_EQ, col, val, 'ALL ') end
local function notEqAny(col, val) return Binary(NOT_EQ, col, val, 'ANY ') end
local function lt(col, val)       return Binary(LT, col, val)          end
local function ltAll(col, val)    return Binary(LT, col, val, 'ALL ')  end
local function ltAny(col, val)    return Binary(LT, col, val, 'ANY ')  end
local function le(col, val)       return Binary(LE, col, val)         end
local function leAll(col, val)    return Binary(LE, col, val, 'ALL ') end
local function leAny(col, val)    return Binary(LE, col, val, 'ANY ') end
local function gt(col, val)       return Binary(GT, col, val)          end
local function gtAll(col, val)    return Binary(GT, col, val, 'ALL ')  end
local function gtAny(col, val)    return Binary(GT, col, val, 'ANY ')  end
local function ge(col, val)       return Binary(GE, col, val)         end
local function geAll(col, val)    return Binary(GE, col, val, 'ALL ') end
local function geAny(col, val)    return Binary(GE, col, val, 'ANY ') end
local function into(self, tbl)     self._into = tbl return self end
local function intoTemp(self, tbl) self._into_tmp = true return self:into(tbl) end
local function forUpdate(self, ...)
  self.for_update = true
  return self:_addListArgs({...}, FOR_UPDATE_TBLS)
end

local function obj2equals(obj, expressions)
  for key in pairs(obj) do insert(expressions, eq(key, obj[key])) end
  return expressions
end

--
--
--
--
--

function Statement:__init(type)
  self.type = type
end

function Statement:clone()
  local ctor
  if     self:is(Select) then ctor = Select
  elseif self:is(Insert) then ctor = Insert
  elseif self:is(Update) then ctor = Update
  elseif self:is(Delete) then ctor = Delete end

  local stmt = ctor:extends(self)

  if stmt._where  then stmt._where  = clone(stmt._where)       end
  if stmt.joins   then stmt.joins   = clone(stmt.joins)        end
  if stmt._values then stmt._values = Hash(stmt._values.index) end
  return stmt
end

function Statement:toParams(opts)
  if self.prev_stmt then
    return self.prev_stmt:toParams(opts)
  end

  opts = opts or {}
  opts.parameterized, opts.values, opts.value_ix =
    (opts.parameterized or true), (opts.values or {}), (opts.value_ix or 1)

  local sql, result = (self .. opts), {}
  local vv, vk      = next(opts.values)

  if TABLE == type(vk) then
    for _, key in pairs(opts.values) do
      insert(result, (key.is and tostring(key) or concat(key, COMA)))
    end
  else
    for i=1, #opts.values do
      insert(result, opts.values[i])
    end
  end

  return {text=sql, values=result}
end

function Statement:__tostring()
  return self:__concat({})
end

function Statement:__concat(opts)
  if self.prev_stmt then return tostring(self.prev_stmt)
  else return self:_toString(opts) end
end

function Statement:_exprToString(opts, expr)
  expr = (expr or self._where)
  expr.parens = false
  if expr.expressions and #expr.expressions == 1 then
    expr.expressions[1].parens = false
  end
  return format(patterns.SSPACE, (expr .. opts))
end

function Statement:_add(arr, name)
  if not self[name] then self[name] = {} end

  if TABLE == type(arr) and not arr.is then
    for i=1, #arr do insert(self[name], arr[i]) end
  else
    insert(self[name], arr)
  end

  return self
end

function Statement:_addListArgs(args, name)
  return self:_add(args2array(args), name)
end

function Statement:_addExpression(args, name)
  if not self[name] then self[name] = _and() end
  if #args==2 and TABLE ~= type(args[1]) and TABLE ~= type(args[2]) or
   (#args==2 and args[1].is and (args[1]:is(Sql) or args[1]:is(Val)) or
   args[2] and args[2].is and (args[2]:is(Sql) or args[2]:is(Val))) then
    insert(self[name].expressions, eq(args[1], args[2]))
  else
    for k in pairs(args) do
      if is_expr(args[k]) then
        insert(self[name].expressions, args[k])
      else
        obj2equals(args[k], self[name].expressions)
      end
    end
  end
  return self
end

function Statement:_addJoins(args, _type)
  if not self.joins then self.joins = {} end
  local tbls, on

  if TABLE == type(args[2]) then
    tbls, on = {args[1]}, args[2]
  else
    tbls = args2array(args)
  end

  for _, tbl in pairs(tbls) do
    tbl = self:expandAlias(tbl)
    local left_tbl = self.last_join or (self.tbls and self.tbls[#self.tbls])
    insert(self.joins, Join(tbl, left_tbl, on, _type))
    self.joins[#self.joins]._joinCriteria = self._joinCriteria
  end

  self.last_join = tbls[#tbls]
  return self
end

function Statement:expandAlias(tbl)
  return self._aliases[tbl] and format(patterns.SSPACES, self._aliases[tbl], tbl) or tbl
end

--
--
--
--
--

Select = class(Statement)
Select.__name = 'Select'

function Select:__init(...)
  self.super.__init(self, _SELECT)
  return self:select(...)
end

function Select:select(...)
  return self:_addListArgs({...}, 'cols')
end

function Select:distinct(...)
  self._distinct = true
  return self:_addListArgs({...}, 'cols')
end

function Select:crossJoin(...)
  return self:_addJoins({...}, 'CROSS')
end

function Select:on(...)
  local on = ...
  local last_join, arr = self.joins[#self.joins]
  if is_expr(on) then
    last_join.on = on
  else
    last_join.on = last_join.on or {}
    local args = args2object({...})
    for k,v in pairs(args) do
      last_join.on[k] = v
    end
  end
  return self
end

function Select:having(...)
  return self:_addExpression({...}, _HAVING)
end

function Select:limit(count)
  self._limit = count
  return self
end

function Select:offset(count)
  self._offset = count
  return self
end

function Select:noWait()
  self.no_wait = true
  return self
end

function Select:from(...)
  local arr = args2array({...})
  for i=1, #arr do
    arr[i] = self:expandAlias(arr[i])
  end
  return self:_add(arr, TBLS)
end

Select.into           = into
Select.intoTable      = into
Select.intoTemp       = intoTemp
Select.intoTempTable  = intoTemp
Select.forUpdate      = forUpdate
Select.forUpdateOf    = forUpdate
Select.join           = innerJoin
Select.innerJoin      = innerJoin
Select.leftJoin       = leftJoin
Select.leftOuterJoin  = leftJoin
Select.rightJoin      = rightJoin
Select.rightOuterJoin = rightJoin
Select.fullJoin       = fullJoin
Select.fullOuterJoin  = fullJoin
Select.where          = where
Select._and           = where
Select.group          = group
Select.groupBy        = group
Select.order          = order
Select.orderBy        = order

for key, value in pairs(compounds) do
  Select[key] = function(self, ...)
    local stmts, stmt = args2array({...})
    if TABLE == type(stmts) and #stmts == 0 and not stmts.is then
      stmt = Select()
      stmt.prev_stmt, stmt._aliases, stmt._views, stmt._joinCriteria =
        self, self._aliases, self._views, self._joinCriteria
      stmts = {stmt}
    end

    self:_add(stmts, '_' .. key)

    if stmt then
      return stmt
    end
    return self
  end
end

function Select:joinView(view, on, _type)
  local alias, view_name = get_alias(view), get_table(view)
  local view             = self._views[view_name]
  local tbl              = format(patterns.SSPACES, get_table(view.tbls[1]), alias)
  local new_aliases      = {[get_alias(view.tbls[1])] = alias}

  self:_addJoins({tbl, (on or {})}, (_type and upper(_type) or 'INNER'))

  if view.joins then
    local j_alias, j_tbl, _tbl  = EMPTY, EMPTY, EMPTY
    for i=1, #view.joins do
      j_alias, j_tbl = get_alias(view.joins[i].tbl), get_table(view.joins[i].tbl)
      new_aliases[j_alias] = format(patterns.S_S, alias, j_alias)
      _tbl = format(patterns.SSPACESSUNDERS, j_tbl, alias, j_alias)
      local join =
        Join(_tbl, view.joins[i].left_tbl, view.joins[i].on, view.joins[i].type)
      join._joinCriteria = self._joinCriteria
      if not join.on then join.on = join:autoGenerateOn(_tbl, join.left_tbl) end
      insert(self.joins, join)
      join.on = namespaced_on(join.on, new_aliases)
    end
  end

  if view._where then
    for i=1, #view._where.expressions do
      local expr = view._where.expressions[i]:extends()
      convert_expr(expr, new_aliases)
      self:where(expr)
    end
  end

  return self
end

function Select:_toString(opts)
  local cols, res = (#self.cols > 0 and self.cols or {STAR}), {SELECT}
  if self._distinct then insert(res, 'DISTINCT ') end
  handles(res, cols, opts)
  if self._into then
    insert(res, format(patterns.INTO_SS, (self._into_tmp and TEMP or EMPTY), self._into))
  end
  if self.tbls then
    insert(res, format(patterns.FROM_S, concat(self.tbls, COMA_SPC)))
  end
  if self.joins then
    for i=1, #self.joins do
      insert(res, format(patterns.SSPACE, (self.joins[i] .. opts)))
    end
  end
  if self._where then
    insert(res, format(patterns.WHERE_S, self:_exprToString(opts)))
  end
  if self.group_by then
    insert(res, GROUPBY)
    handles(res, self.group_by, opts)
  end
  if self._having then
    insert(res, format(patterns.HVG_S, self:_exprToString(opts, self._having)))
  end
  if self.order_by then
    insert(res, ORDERBY)
    handles(res, self.order_by, opts)
  end
  if self._limit then
    insert(res, format(patterns.LIMIT_S, tostring(self._limit)))
  end
  if self._offset then
    insert(res, format(patterns.OFFSET_S, tostring(self._offset)))
  end

  for key, value in pairs(compounds) do
    local stmt = self['_' .. key]
    if stmt and #stmt > 0 then
      insert(res, value)
      insert(res, SPACE)
      for i=1, (#stmt - 1) do
        insert(res, format(patterns.SSPACESSSPACE, stmt[i]:_toString(opts), value))
      end
      insert(res, stmt[#stmt]:_toString(opts))
      insert(res, SPACE)
    end
  end

  if self.for_update then
    insert(res, FOR_UPDATE)
    if self.for_update_tbls then
      insert(res, concat(self.for_update_tbls, COMA_SPC))
      insert(res, SPACE)
    end
    if self.no_wait then insert(res, 'NO WAIT ') end
  end

  return sub(concat(res), 1, -2)
end

--
--
--
--
--

Insert = class(Statement)
Insert.__name = 'Insert'

function Insert:__init(expansions, tbl, ...)
  self._aliases = expansions
  self.super.__init(self, 'insert')
  return self:into(tbl, ...)
end

Insert.orReplace  = orReplace
Insert.orRollback = orRollback
Insert.orAbort    = orAbort
Insert.orFail     = orFail
Insert.orIgnore   = orIgnore

function Insert:into(tbl, ...)
  if tbl then self.tbls = {self:expandAlias(tbl)} end
  local values = {...}
  if #values > 0 then
    if STRING == type(values[1]) or
      (is_array(values[1]) and STRING == type(values[1][1]))
    then
      self._split_keys_vals_mode, self._values = true, Hash(args2array({...}))
    else
      self:values(...)
    end
  end
  return self
end

function Insert:values(...)
  local values, check = args2array({...}), 0
  if self._split_keys_vals_mode and #values > 0 then
    if TABLE == type(values[1]) then
      check_same_len(#self._values, #values[1])
      for i=1, #values[1] do self._values.set(self._values, i, {values[1][i]}) end
      for i=2, #values do
        check_same_len(#self._values, #values[i])
        for k=1, #values[i] do self._values.add(self._values, k, values[i][k]) end
      end
    else
      check_same_len(#self._values, #values)
      for k=1, #values do self._values.set(self._values, k, values[k]) end
    end
    goto returning
  end

  self._values = self._values or Hash()
  if TABLE == type(values[1]) and TABLE == type(values[1][1]) then
    for k,v in pairs(values[1][1]) do self._values[k] = {v} end
    for i=2, #values[1] do
      check_same_len(#self._values, #values[1][i])
      for k,v in pairs(values[1][i]) do self._values.add(self._values, k, v) end
    end
  else
    for k,v in pairs((values[1] or values)) do self._values[k] = v end
  end

  ::returning::
  return self
end

function Insert:select(...)
  self._select = Select(...)
  self._select._aliases, self._select._views, self._select._joinCriteria =
    self._aliases, self._views, self._joinCriteria
  self._select.prev_stmt = self
  return self._select
end

function Insert:returning(...)
  return self._addListArgs({...}, _RETURNING)
end

function Insert:_toString(opts)
  local res = {'INSERT '}
  if self._or then insert(res, self._or) end
  insert(res, format(patterns.INTO_S_P, concat(self.tbls, COMA_SPC)))
  handles(res, self._values, opts, false, false, nil, EMPTY)
  insert(res, PAR_SPC)
  if self._select then
    insert(res, self._select:_toString(opts))
    insert(res, SPACE)
  else
    insert(res, VALS_P)
    handles(res, self._values, opts, false, true, nil, EMPTY)
    insert(res, PAR_SPC)
  end
  if self._returning then
    insert(res, RETURNING)
    handles(res, self._returning, opts)
  end

  return sub(concat(res), 1, -2)
end

--
--
--
--
--

Update = class(Statement)
Update.__name = 'Update'

function Update:__init(expansions, tbl, ...)
  self._aliases = expansions
  self.super.__init(self, _UPDATE)
  self.tbls = {self:expandAlias(tbl)}
  if ... then self:set(...) end
  return self
end

Update.orReplace  = orReplace
Update.orRollback = orRollback
Update.orAbort    = orAbort
Update.orFail     = orFail
Update.orIgnore   = orIgnore
Update.where      = where
Update._and       = where

function Update:set(...)
  local args = args2object({...})
  self._values = self._values or Hash()
  for k,v in pairs(args) do self._values[k] = v end
  return self
end

function Update:values(...)
  return self:set(...)
end

function Update:_toString(opts)
  local res = {UPDATE}
  if self._or then insert(res, self._or) end
  insert(res, format(patterns.S_SET, self.tbls[1]))
  handles(res, self._values, opts, true)
  if self._where then insert(res, format(patterns.WHERE_S, self:_exprToString(opts))) end

  return sub(concat(res), 1, -2)
end

--
--
--
--
--

Delete = class(Statement)
Delete.__name = 'Delete'

function Delete:__init(expansions, ...)
  self._aliases = expansions
  self.super.__init(self, 'delete')
  if ... then self.tbls = {self:expandAlias(...)} end
  return self
end

function Delete:from(...)
  local arr = args2array({...})
  for i=1, #arr do arr[i] = self:expandAlias(arr[i]) end
  return self:_add(arr, TBLS)
end

Delete.where = where
Delete._and  = where

function Delete:using(...)
  local args = args2array({...})
  for i=1, #args do args[i] = self:expandAlias(args[i]) end
  return self:_add(args, USING)
end

function Delete:_toString(opts)
  local res = {'DELETE FROM ', self.tbls[1], SPACE}
  if self._using then insert(res, format(patterns.USING_S, concat(self._using, COMA_SPC))) end
  if self._where then insert(res, format(patterns.WHERE_S, self:_exprToString(opts))) end
  return sub(concat(res), 1, -2)
end

--
--
--
--
--

function Join:__init(tbl, left_tbl, on, type)
  self.tbl, self.left_tbl, self.on, self.type = tbl, left_tbl, on, type
end

function Join:autoGenerateOn(tbl, left_tbl)
  return self._joinCriteria(
      get_table(left_tbl), get_alias(left_tbl), get_table(tbl), get_alias(tbl)
    )
end

function Join:__concat(opts)
  local on, result = self.on, {}
  if not on or next(on) == nil then
    if self._joinCriteria then
      on = self:autoGenerateOn(self.tbl, self.left_tbl)
    else
      error(format('No join criteria supplied for "%s" join', get_alias(self.tbl)))
    end
  end
  if is_expr(on) then
    on = on .. opts
  else
    for key, val in pairs(on) do
      insert(result, format(patterns.SEQS, handle_column(key, opts), handle_column(val, opts)))
      insert(result, ESP_AND)
    end
    result[#result] = nil
    on = concat(result)
  end
  return format(patterns.S_JOIN_S_ON_S, self.type, self.tbl, on)
end

function Join:__tostring() return self:__concat({}) end

--
--
--
--
--

function Group:__init(op, expressions)
  self.op, self.expressions = op, {}
  for k in pairs(expressions) do
    if is_expr(expressions[k]) then
      insert(self.expressions, expressions[k])
    else
      obj2equals(expressions[k], self.expressions)
    end
  end
end

function Group:clone()
  return Group(self.op, self.expressions:extends())
end

function Group:__concat(opts)
  local res = {}
  for k in pairs(self.expressions) do
    insert(res, format("%s %s ", (self.expressions[k] .. opts), self.op))
  end
  res[#res] = sub(res[#res], 1, (#self.op + 3) * -1)
  if #self.expressions > 1 and (self.parens or self.parens == nil) then
    return format("(%s)", concat(res))
  end
  return concat(res)
end

function Group:__tostring() return self:__concat({}) end

--
--
--
--
--

function Not:__init(expr)
  self.expressions = is_expr(expr) and {expr} or {_and({expr})}
end

function Not:clone()
  return Not(self.expressions[1]:extends())
end

function Not:__concat(opts)
  return format(patterns.NOT_S, self.expressions[1] .. opts)
end

function Not:__tostring() return self:__concat({}) end

--
--
--
--
--

function Binary:__init(op, col, val, ...)
  self.op, self.col, self.val, self.quantifier =
    op, col, val, (... or EMPTY)
end

function Binary:clone()
  return Binary(self.op, self.col, self.val)
end

function Binary:__concat(opts)
  return format(patterns.SSPACESSSPACESS, handle_column(self.col, opts), self.op,
    self.quantifier, handle_value(self.val, opts))
end

function Binary:__tostring() return self:__concat({}) end

--
--
--
--
--

function Like:__init(col, val, ...)
  self.col, self.val, self.escape_char = col, val, ...
end

function Like:clone()
  return Like(self.col, self.val, self.escape_char)
end

function Like:__concat(opts)
  return format(
      patterns.S_LIKE_SS, handle_column(self.col, opts), handle_value(self.val, opts),
      (self.escape_char and format(patterns.ESCAPE_Q_S, self.escape_char) or EMPTY)
    )
end

function Like:__tostring() return self:__concat({}) end

--
--
--
--
--

function Between:__init(col, val1, val2)
  self.col, self.val1, self.val2 = col, val1, val2
end

function Between:clone()
  return Between(self.col, self.val1, self.val2)
end

function Between:__concat(opts)
  return format(patterns.S_BTW_S_AND_S, handle_column(self.col, opts),
    handle_value(self.val1, opts), handle_value(self.val2, opts))
end

function Between:__tostring() return self:__concat({}) end

--
--
--
--
--

function Unary:__init(op, col)
  self.op, self.col = op, col
end

function Unary:clone()
  return Unary(self.op, self.col)
end

function Unary:__concat(opts)
  return format(patterns.SSPACES, handle_column(self.col, opts), self.op)
end

function Unary:__tostring() return self:__concat({}) end

--
--
--
--
--

function In:__init(col, ...)
  self.col, self.list = col, ...
  self.list = TABLE ~= type(self.list) and {...} or self.list
end

function In:clone()
  return In(self.col, clone(self.list))
end

function In:__concat(opts)
  local res = EMPTY
  if is_array(self.list) then
    res = sub(concat(handles({}, self.list, opts, false, true)), 1, -2)
  elseif self.list.is and self.list:is(Statement) then
    res = self.list:_toString(opts)
  end

  return format(patterns.S_IN_S, handle_column(self.col, opts), res)
end

function In:__tostring() return self:__concat({}) end

--
--
--
--
--

function Exists:__init(subquery)
  self.subquery = subquery
end

function Exists:clone()
  return Exists(clone(self.subquery))
end

function Exists:__concat(opts)
  return format(patterns.EXISTS_S, self.subquery:_toString(opts))
end

function Exists:__tostring() return self:__concat({}) end

--
--
--
--
--

function Val:__init(_val) self.val = _val end

function Val:__tostring() return self.val end

--
--
--
--
--

function Sql:__init(str)   self.str = str end

function Sql:__tostring()  return self.str end

function Sql:__concat(arg) return self.str .. arg end

--
--
--
--
--

local SQLRocks = {}
SQLRocks.__index = SQLRocks

function SQLRocks:__call(_expansions, _criteria, _views)
  local expansions, criteria, views, this =
    (TABLE    == type(_expansions) and _expansions or {}),
    (FUNCTION == type(_criteria)   and _criteria   or nil),
    (TABLE    == type(_views)      and _views      or {}),
    {
      val       = Val,       sql    = Sql,    _and     = _and,     _or    = _or,
      _not      = Not,       like   = Like,   between  = Between,  isNull = isNull,
      isNotNull = isNotNull, exists = Exists, eq       = eq,       eqAll  = eqAll,
      eqAny     = eqAny,     notEq  = notEq,  notEqAll = notEqAll, ltAny  = ltAny,
      notEqAny  = notEqAny,  lt     = lt,     ltAll    = ltAll,    le     = le,
      leAll     = leAll,     leAny  = leAny,  gt       = gt,       gtAll  = gtAll,
      gtAny     = gtAny,     ge     = ge,     geAll    = geAll,    geAny  = geAny,
      _in       = In
    }

  this.addView = function(name, sel)
    if #sel.tbls ~= 1 then
      error(format(patterns.UNSP_N_D, #sel.tbls))
    end
    views[name] = sel
  end
  this.getView         = function(name) return views[name] end
  this.aliasExpansions = function(_expansions) expansions = _expansions end
  this.joinCriteria    = function(_criteria) criteria = _criteria end

  this.select, this.insert, this.update, this.delete, this.Join =
    function(...)
      local sel = Select(...)
      sel._aliases, sel._views, sel._joinCriteria = expansions, views, criteria
      return sel
    end,
    function(...)
      local ins = Insert(expansions, ...)
      ins._views, ins._joinCriteria = views, criteria
      return ins
    end,
    function(...) return Update(expansions, ...) end,
    function(...) return Delete(expansions, ...) end,
    function(...) local joi = Join(...) joi._joinCriteria = criteria return joi end

  this.insertInto, this.deleteFrom = this.insert, this.delete

  return this
end

return setmetatable({
    Statement = Statement, Join   = Join,   Select = Select,
    Insert    = Insert,    Update = Update, Delete = Delete,
    Sql       = Sql,       Val    = Val,    Group  = Group,
    Not       = Not,       Binary = Binary, Like   = Like,
    Between   = Between,   Unary  = Unary,  In     = In,     Exists = Exists
  }, SQLRocks)