sqlite function in insert break other values

[kkharji]

Apparently the following parsed statement is not good enough to pass:

insert into timestamps (fid, date) values(:fid, julianday('now'))"

it does insert the date, but sadly ignore fid.

🤔 not sure how this would be solved 😭

function DB:insert(tbl_name, rows)
  --- ...
  clib.wrap_stmts(self.conn, function()
    for _, v in ipairs(items) do
      local s = stmt:parse(self.conn, P.insert(tbl_name, { values = v }))
      --> P.insert(....) => "insert into timestamps (fid, date) values(:fid, julianday('now'))"
      --> stmt:parse(..) => "insert into timestamps (fid, date) values(NULL, julianday('now'))"
      s:bind(v)
      s:step()
      s:bind_clear()
      table.insert(ret_vals, s:finalize())
    end
  end)
end

On the parser side, instead of binding to :date it uses the value in place.

local pvalues = function(defs, kv)
  --- ....
  local keys = {}
  for k, v in u.opairs(defs) do
    if type(v) == "string" and v:match "%a+%(.+%)" then
      tinsert(keys, v)
    else
      tinsert(keys, ":" .. k)
    end
  end

  return ("values(%s)"):format(tconcat(keys, ", "))
end

[kkharji]

Options:

• At parser level by having all the key values set in place, when one of the keys is a sql function.
• At db.insert level remove keys that match type(v) == "string" and v:match "%a+%(.+%)"
• Research more ....

edit: s:expand() outputs:

insert into timestamps (fid, date) values(NULL, julianday('now'))

That's interesting, I wonder if I should be fixing the issue on sql statement level 🤔

[kkharji]

😆 😆 😆 f####
https:/tami5/sql.nvim/blob/2feef57bef147cf502b4491e3fc6c58a05f3096e/lua/sql/stmt.lua#L307-L315 . We should've errored out, here's 3 hours I will never get back

pushing fix soon ..