Repository: tami5/sql.nvim (lua)

Issue:
better support for boolean and lua tables
Currently sql.nvim uses the following for interoping lua boolean to 0/1.

 ```lua
 -- lua/sql.lua
local booleansql = function(value)
  if type(value) == "boolean" then
    value = (value == true) and 1 or 0
  end
  return value
end
```

However there are no support for interop 0/1 back to lua boolean. This aspect
should be handled in `stmt` module with the above code removed from `sql:eval`.

Pull request description:
closes #11

Patch:
diff --git a/README.md b/README.md
index 256da76d..344b9da8 100644
--- a/README.md
+++ b/README.md
@@ -26,14 +26,13 @@ welcomed. Issues, feature and suggestions are encouraged.
 - Evaluate any sqlite statement and return result if any `sql:eval`
 - Helper function over `sql:eval` to do all sort of operation. 
 - High level API with `sql:table` for better experience
+- lua tables deserialization/serialization (in helper functions and high level api)
 - 90% test coverage.
 
 #### WIP: 
 
 - Docs
-- Better boolean interop.
 - Better join support.
-- Serialization of lua tables to json so that lua tables can be inserted. (or byte code)
 
 Installation
 -----------------
@@ -167,6 +166,7 @@ db:create("tbl_name", {
   id = {"integer", "primary", "key"},
   title = "text",
   desc = "text",
+  data = "json" or "luatable", -- will be auto serialized and deserialized
   created = "int",
   -- ensure = true --:> if you like to create the table if it doesn't exist
   done = {"int", "not", "null", "default", 0},
@@ -284,7 +284,7 @@ db:delete("todos", {where =  {id = 88}})
 local db = sql:new(dbpath or nil) -- db:open/db:close is optional and not required
 
 -- Initialize the sql table object. it can be non existing or new table.
-local t = db:table("todos")
+local t = db:table("todos", {schema = {}, nocache = true})
 ```
 
 #### Table schema
@@ -299,6 +299,7 @@ table already has data, then the it should error out.
 t:schema{
   id = {"integer", "not", "null"},
   name = "text",
+  data = "json" or "luatable", -- will be auto serialized and deserialized
   age = "integer"
 }
 
diff --git a/lua/sql.lua b/lua/sql.lua
index 3d60ae7a..1a8a70b5 100644
--- a/lua/sql.lua
+++ b/lua/sql.lua
@@ -3,6 +3,7 @@ local stmt = require'sql.stmt'
 local u = require'sql.utils'
 local t = require'sql.table'
 local P = require'sql.parser'
+local json = require'sql.json'
 local flags = clib.flags
 local sql = {}
 sql.__index = sql
@@ -300,14 +301,14 @@ function sql:pre_insert(rows, info)
       end
     end
 
-    -- TODO: do value interop here.
-    -- for k, v in pairs(info.def) do
-    --   if not row[k] then
-    --     row[k] = v
-    --   elseif row[k] == "null" then
-    --     row[k] = nil
-    --   end
-    -- end
+    for k, v in pairs(row) do
+      if info.types[k] == "luatable" or info.types[k] == "json"  then
+        row[k] = json.encode(v)
+      end
+      if type(v) == "boolean" then
+        row[k] = v == true and 1 or 0
+      end
+    end
   end
   return rows
 end
@@ -405,19 +406,28 @@ end
 -- @return lua list of matching rows
 function sql:select(tbl, spec)
   self:__assert_tbl(tbl, "select")
+  spec = spec or {}
   local ret = {}
-  if not spec then
-    return self:eval(P.select(tbl))
-  end
+  local types = self:schema(tbl)
 
   self:__wrap_stmts(function()
     if spec.keys then spec.select = spec.keys end
-    local s = self:__parse(P.select(tbl, {
-      select = spec and spec.select or nil,
-      where = spec and spec.where or nil,
-      join = spec and spec.join or nil,
-    }))
-    ret = s:kvrows()
+    local s = spec and self:__parse(P.select(tbl, {
+      select = spec.select,
+      where = spec.where,
+      join = spec.join,
+    })) or self:__parse(P.select(tbl))
+
+    s:each(function()
+      local row = s:kv()
+      for k, v in pairs(row) do
+        if types[k] == "luatable" or types[k] == "json" then
+          row[k] = json.decode(v)
+        end
+      end
+      table.insert(ret, row)
+    end)
+
     s:reset()
     s:finalize()
   end)
diff --git a/lua/sql/json.lua b/lua/sql/json.lua
new file mode 100644
index 00000000..ca2f8619
--- /dev/null
+++ b/lua/sql/json.lua
@@ -0,0 +1,387 @@
+--
+-- json.lua
+--
+-- Copyright (c) 2020 rxi
+--
+-- 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.
+--
+
+local json = { _version = "0.1.2" }
+
+-------------------------------------------------------------------------------
+-- Encode
+-------------------------------------------------------------------------------
+
+local encode
+
+local escape_char_map = {
+  [ "\\" ] = "\\",
+  [ "\"" ] = "\"",
+  [ "\b" ] = "b",
+  [ "\f" ] = "f",
+  [ "\n" ] = "n",
+  [ "\r" ] = "r",
+  [ "\t" ] = "t",
+}
+
+local escape_char_map_inv = { [ "/" ] = "/" }
+for k, v in pairs(escape_char_map) do
+  escape_char_map_inv[v] = k
+end
+
+
+local function escape_char(c)
+  return "\\" .. (escape_char_map[c] or string.format("u%04x", c:byte()))
+end
+
+
+local function encode_nil(val)
+  return val == nil and "null"
+end
+
+
+local function encode_table(val, stack)
+  local res = {}
+  stack = stack or {}
+
+  -- Circular reference?
+  if stack[val] then error("circular reference") end
+
+  stack[val] = true
+
+  if rawget(val, 1) ~= nil or next(val) == nil then
+    -- Treat as array -- check keys are valid and it is not sparse
+    local n = 0
+    for k in pairs(val) do
+      if type(k) ~= "number" then
+        error("invalid table: mixed or invalid key types")
+      end
+      n = n + 1
+    end
+    if n ~= #val then
+      error("invalid table: sparse array")
+    end
+    -- Encode
+    for _, v in ipairs(val) do
+      table.insert(res, encode(v, stack))
+    end
+    stack[val] = nil
+    return "[" .. table.concat(res, ",") .. "]"
+
+  else
+    -- Treat as an object
+    for k, v in pairs(val) do
+      if type(k) ~= "string" then
+        error("invalid table: mixed or invalid key types")
+      end
+      table.insert(res, encode(k, stack) .. ":" .. encode(v, stack))
+    end
+    stack[val] = nil
+    return "{" .. table.concat(res, ",") .. "}"
+  end
+end
+
+
+local function encode_string(val)
+  return '"' .. val:gsub('[%z\1-\31\\"]', escape_char) .. '"'
+end
+
+
+local function encode_number(val)
+  -- Check for NaN, -inf and inf
+  if val ~= val or val <= -math.huge or val >= math.huge then
+    error("unexpected number value '" .. tostring(val) .. "'")
+  end
+  return string.format("%.14g", val)
+end
+
+
+local type_func_map = {
+  [ "nil"     ] = encode_nil,
+  [ "table"   ] = encode_table,
+  [ "string"  ] = encode_string,
+  [ "number"  ] = encode_number,
+  [ "boolean" ] = tostring,
+}
+
+
+encode = function(val, stack)
+  local t = type(val)
+  local f = type_func_map[t]
+  if f then
+    return f(val, stack)
+  end
+  error("unexpected type '" .. t .. "'")
+end
+
+
+function json.encode(val)
+  return ( encode(val) )
+end
+
+
+-------------------------------------------------------------------------------
+-- Decode
+-------------------------------------------------------------------------------
+
+local parse
+
+local function create_set(...)
+  local res = {}
+  for i = 1, select("#", ...) do
+    res[ select(i, ...) ] = true
+  end
+  return res
+end
+
+local space_chars   = create_set(" ", "\t", "\r", "\n")
+local delim_chars   = create_set(" ", "\t", "\r", "\n", "]", "}", ",")
+local escape_chars  = create_set("\\", "/", '"', "b", "f", "n", "r", "t", "u")
+local literals      = create_set("true", "false", "null")
+
+local literal_map = {
+  [ "true"  ] = true,
+  [ "false" ] = false,
+  [ "null"  ] = nil,
+}
+
+
+local function next_char(str, idx, set, negate)
+  for i = idx, #str do
+    if set[str:sub(i, i)] ~= negate then
+      return i
+    end
+  end
+  return #str + 1
+end
+
+
+local function decode_error(str, idx, msg)
+  local line_count = 1
+  local col_count = 1
+  for i = 1, idx - 1 do
+    col_count = col_count + 1
+    if str:sub(i, i) == "\n" then
+      line_count = line_count + 1
+      col_count = 1
+    end
+  end
+  error( string.format("%s at line %d col %d", msg, line_count, col_count) )
+end
+
+
+local function codepoint_to_utf8(n)
+  -- http://scripts.sil.org/cms/scripts/page.php?site_id=nrsi&id=iws-appendixa
+  local f = math.floor
+  if n <= 0x7f then
+    return string.char(n)
+  elseif n <= 0x7ff then
+    return string.char(f(n / 64) + 192, n % 64 + 128)
+  elseif n <= 0xffff then
+    return string.char(f(n / 4096) + 224, f(n % 4096 / 64) + 128, n % 64 + 128)
+  elseif n <= 0x10ffff then
+    return string.char(f(n / 262144) + 240, f(n % 262144 / 4096) + 128,
+                       f(n % 4096 / 64) + 128, n % 64 + 128)
+  end
+  error( string.format("invalid unicode codepoint '%x'", n) )
+end
+
+
+local function parse_unicode_escape(s)
+  local n1 = tonumber( s:sub(1, 4),  16 )
+  local n2 = tonumber( s:sub(7, 10), 16 )
+   -- Surrogate pair?
+  if n2 then
+    return codepoint_to_utf8((n1 - 0xd800) * 0x400 + (n2 - 0xdc00) + 0x10000)
+  else
+    return codepoint_to_utf8(n1)
+  end
+end
+
+
+local function parse_string(str, i)
+  local res = ""
+  local j = i + 1
+  local k = j
+
+  while j <= #str do
+    local x = str:byte(j)
+
+    if x < 32 then
+      decode_error(str, j, "control character in string")
+
+    elseif x == 92 then -- `\`: Escape
+      res = res .. str:sub(k, j - 1)
+      j = j + 1
+      local c = str:sub(j, j)
+      if c == "u" then
+        local hex = str:match("^[dD][89aAbB]%x%x\\u%x%x%x%x", j + 1)
+                 or str:match("^%x%x%x%x", j + 1)
+                 or decode_error(str, j - 1, "invalid unicode escape in string")
+        res = res .. parse_unicode_escape(hex)
+        j = j + #hex
+      else
+        if not escape_chars[c] then
+          decode_error(str, j - 1, "invalid escape char '" .. c .. "' in string")
+        end
+        res = res .. escape_char_map_inv[c]
+      end
+      k = j + 1
+
+    elseif x == 34 then -- `"`: End of string
+      res = res .. str:sub(k, j - 1)
+      return res, j + 1
+    end
+
+    j = j + 1
+  end
+
+  decode_error(str, i, "expected closing quote for string")
+end
+
+
+local function parse_number(str, i)
+  local x = next_char(str, i, delim_chars)
+  local s = str:sub(i, x - 1)
+  local n = tonumber(s)
+  if not n then
+    decode_error(str, i, "invalid number '" .. s .. "'")
+  end
+  return n, x
+end
+
+
+local function parse_literal(str, i)
+  local x = next_char(str, i, delim_chars)
+  local word = str:sub(i, x - 1)
+  if not literals[word] then
+    decode_error(str, i, "invalid literal '" .. word .. "'")
+  end
+  return literal_map[word], x
+end
+
+
+local function parse_array(str, i)
+  local res = {}
+  local n = 1
+  i = i + 1
+  while 1 do
+    local x
+    i = next_char(str, i, space_chars, true)
+    -- Empty / end of array?
+    if str:sub(i, i) == "]" then
+      i = i + 1
+      break
+    end
+    -- Read token
+    x, i = parse(str, i)
+    res[n] = x
+    n = n + 1
+    -- Next token
+    i = next_char(str, i, space_chars, true)
+    local chr = str:sub(i, i)
+    i = i + 1
+    if chr == "]" then break end
+    if chr ~= "," then decode_error(str, i, "expected ']' or ','") end
+  end
+  return res, i
+end
+
+
+local function parse_object(str, i)
+  local res = {}
+  i = i + 1
+  while 1 do
+    local key, val
+    i = next_char(str, i, space_chars, true)
+    -- Empty / end of object?
+    if str:sub(i, i) == "}" then
+      i = i + 1
+      break
+    end
+    -- Read key
+    if str:sub(i, i) ~= '"' then
+      decode_error(str, i, "expected string for key")
+    end
+    key, i = parse(str, i)
+    -- Read ':' delimiter
+    i = next_char(str, i, space_chars, true)
+    if str:sub(i, i) ~= ":" then
+      decode_error(str, i, "expected ':' after key")
+    end
+    i = next_char(str, i + 1, space_chars, true)
+    -- Read value
+    val, i = parse(str, i)
+    -- Set
+    res[key] = val
+    -- Next token
+    i = next_char(str, i, space_chars, true)
+    local chr = str:sub(i, i)
+    i = i + 1
+    if chr == "}" then break end
+    if chr ~= "," then decode_error(str, i, "expected '}' or ','") end
+  end
+  return res, i
+end
+
+
+local char_func_map = {
+  [ '"' ] = parse_string,
+  [ "0" ] = parse_number,
+  [ "1" ] = parse_number,
+  [ "2" ] = parse_number,
+  [ "3" ] = parse_number,
+  [ "4" ] = parse_number,
+  [ "5" ] = parse_number,
+  [ "6" ] = parse_number,
+  [ "7" ] = parse_number,
+  [ "8" ] = parse_number,
+  [ "9" ] = parse_number,
+  [ "-" ] = parse_number,
+  [ "t" ] = parse_literal,
+  [ "f" ] = parse_literal,
+  [ "n" ] = parse_literal,
+  [ "[" ] = parse_array,
+  [ "{" ] = parse_object,
+}
+
+
+parse = function(str, idx)
+  local chr = str:sub(idx, idx)
+  local f = char_func_map[chr]
+  if f then
+    return f(str, idx)
+  end
+  decode_error(str, idx, "unexpected character '" .. chr .. "'")
+end
+
+
+function json.decode(str)
+  if type(str) ~= "string" then
+    error("expected argument of type string, got " .. type(str))
+  end
+  local res, idx = parse(str, next_char(str, 1, space_chars, true))
+  idx = next_char(str, idx, space_chars, true)
+  if idx <= #str then
+    decode_error(str, idx, "trailing garbage")
+  end
+  return res
+end
+
+return json


Test patch:
diff --git a/test/auto/sql_spec.lua b/test/auto/sql_spec.lua
index 0bbad22b..35cb1b92 100644
--- a/test/auto/sql_spec.lua
+++ b/test/auto/sql_spec.lua
@@ -317,12 +317,15 @@ describe("sql", function()
       eq(false, ok, "should fail")
     end)
 
-    -- it("inserts lua table in sql column", function()
-    --   db:insert("todos", {
-    --     {title = "TODO 1", desc = {"list", "of", "lines"}},
-    --     {title = "TODO 2", desc = { key = "value", pair = true}}
-    --   })
-    -- end)
+    it("serialize lua table in sql column", function()
+      db:eval("drop table test")
+      db:eval("create table test(id integer, data luatable)")
+      db:insert("test", {id = 1, data = {"list", "of", "lines"}})
+
+      local res = db:eval[[select * from test]]
+      eq('["list","of","lines"]',res[1].data)
+      db:eval("drop table test")
+    end)
     db:close()
   end)
 
@@ -554,6 +557,13 @@ describe("sql", function()
 
       eq(expected, res[1])
     end)
+    it("it serialize json if the schema key datatype is json", function()
+      db:eval("create table test(id integer, data luatable)")
+      db:eval("insert into test(id,data) values(1, '[\"list\",\"of\",\"lines\"]')")
+      eq({
+        { id = 1, data = {"list", "of", "lines"} }
+      }, db:select("test"), "they should be identical")
+    end)
 
     db:close()
   end)