💥 sql.schema return detailed description of table.

kkharji · kkharji · commit a6bd3d1cae9d · 2021-08-26T20:40:19.000+03:00
Not sure if this is a wise decision or not. But it beat having the
schema returned given the key and the value the user written.
diff --git a/lua/sql.lua b/lua/sql.lua
@@ -266,46 +266,20 @@ end
 
 ---Get {name} table schema, if table does not exist then return an empty table.
 ---@param tbl_name string: the table name.
----@param info boolean: whether to return table info. default false.
 ---@return table list of keys or keys and their type.
-function DB:schema(tbl_name, info)
+function DB:schema(tbl_name)
   local sch = self:eval(string.format("pragma table_info(%s)", tbl_name))
-  if type(sch) == "boolean" then
-    return {}
-  end
-
-  local tbl_info = {}
-  local req = {}
-  local def = {}
-  local types = {}
-
-  for _, v in ipairs(sch) do
-    if v.notnull == 1 and v.pk == 0 then
-      req[v.name] = v
-    end
-    if v.dflt_value then
-      def[v.name] = v.dflt_value
-    end
-
-    tbl_info[v.name] = {
+  local schema = {}
+  for _, v in ipairs(type(sch) == "boolean" and {} or sch) do
+    schema[v.name] = {
+      cid = v.cid,
       required = v.notnull == 1,
       primary = v.pk == 1,
       type = v.type,
-      cid = v.cid,
       default = v.dflt_value,
     }
-
-    types[v.name] = v.type
   end
-
-  local obj = {
-    info = tbl_info,
-    req = req == {} and nil or req,
-    def = def == {} and nil or def,
-    types = types,
-  }
-
-  return info and obj or obj.types
+  return schema
 end
 
 ---Insert to lua table into sqlite database table.
@@ -426,26 +400,21 @@ end
 ---@return table[]
 function DB:select(tbl_name, spec)
   a.is_sqltbl(self, tbl_name, "select")
-  spec = spec or {}
-  self.modified = false
-
-  local ret = {}
-  local types = self:schema(tbl_name)
-
-  self.modified = false
-
-  clib.wrap_stmts(self.conn, function()
+  return clib.wrap_stmts(self.conn, function()
+    local ret = {}
+    spec = spec or {}
     spec.select = spec.keys and spec.keys or spec.select
 
     local s = stmt:parse(self.conn, P.select(tbl_name, spec))
     stmt.each(s, function()
       table.insert(ret, stmt.kv(s))
     end)
     stmt.reset(s)
-    stmt.finalize(s)
+    if stmt.finalize(s) then
+      self.modified = false
+    end
+    return P.post_select(ret, self:schema(tbl_name))
   end)
-
-  return P.post_select(ret, types)
 end
 
 ---Create new sql-table object.
diff --git a/lua/sql/defs.lua b/lua/sql/defs.lua
@@ -627,9 +627,9 @@ end
 ---@param fn func()
 M.wrap_stmts = function(conn_ptr, fn)
   M.exec_stmt(conn_ptr, "BEGIN")
-  fn()
+  local res = fn()
   M.exec_stmt(conn_ptr, "COMMIT")
-  return
+  return res
 end
 
 ---Get last error msg
diff --git a/lua/sql/parser.lua b/lua/sql/parser.lua
@@ -19,10 +19,10 @@ M.sqlvalue = function(v)
   return type(v) == "boolean" and (v == true and 1 or 0) or (v == nil and "null" or v)
 end
 
-M.luavalue = function(v, schema_type)
-  if schema_type == "luatable" or schema_type == "json" then
+M.luavalue = function(v, key_type)
+  if key_type == "luatable" or key_type == "json" then
     return json.decode(v)
-  elseif schema_type == "boolean" then
+  elseif key_type == "boolean" then
     return v == 0 and false or true
   end
 
@@ -393,16 +393,21 @@ M.create = function(tbl, defs)
   local items = {}
 
   tbl = defs.ensure and "if not exists " .. tbl or tbl
-  defs.ensure = nil
 
   for k, v in u.opairs(defs) do
-    local t = type(v)
-    if t == "boolean" then
-      tinsert(items, k .. " integer not null primary key")
-    elseif t ~= "table" then
-      tinsert(items, string.format("%s %s", k, v))
-    else
-      tinsert(items, ("%s %s"):format(k, opts_to_str(v)))
+    if k ~= "ensure" then
+      local t = type(v)
+      if t == "boolean" then
+        tinsert(items, k .. " integer not null primary key")
+      elseif t ~= "table" then
+        tinsert(items, string.format("%s %s", k, v))
+      else
+        if u.is_list(v) then
+          tinsert(items, ("%s %s"):format(k, tconcat(v, " ")))
+        else
+          tinsert(items, ("%s %s"):format(k, opts_to_str(v)))
+        end
+      end
     end
   end
   return ("create table %s(%s)"):format(tbl, tconcat(items, ", "))
@@ -425,11 +430,12 @@ M.pre_insert = function(rows, schema)
   local res = {}
   rows = u.is_nested(rows) and rows or { rows }
   for i, row in ipairs(rows) do
-    u.foreach(schema.req, function(k)
-      a.missing_req_key(row[k], k)
-    end)
+    -- u.foreach(schema.req, function(k)
+    --   a.missing_req_key(row[k], k)
+    -- end)
     res[i] = u.map(row, function(v, k)
-      local is_json = schema.types[k] == "luatable" or schema.types[k] == "json"
+      a.missing_req_key(v, schema[k].required)
+      local is_json = schema[k].type == "luatable" or schema[k].type == "json"
       return is_json and json.encode(v) or M.sqlvalue(v)
     end)
   end
@@ -442,13 +448,18 @@ end
 ---@param schema table tbl schema
 ---@return table pre processed rows
 ---@TODO support boolean values.
-M.post_select = function(rows, types)
+M.post_select = function(rows, schema)
   local is_nested = u.is_nested(rows)
   rows = is_nested and rows or { rows }
 
   for _, row in ipairs(rows) do
     for k, v in pairs(row) do
-      row[k] = M.luavalue(v, types[k])
+      local info = schema[k]
+      if info then
+        row[k] = M.luavalue(v, info.type)
+      else
+        row[k] = v
+      end
     end
   end
 
diff --git a/test/auto/sql_spec.lua b/test/auto/sql_spec.lua
@@ -680,11 +680,37 @@ describe("sql", function()
 
     it("gets a sql table schema", function()
       local sch = db:schema "test"
-      eq({ a = "text", b = "int", c = "int", d = "text" }, sch)
+      eq({
+        a = {
+          cid = 0,
+          primary = false,
+          required = false,
+          type = "text",
+        },
+        b = {
+          cid = 1,
+          primary = false,
+          required = false,
+          type = "int",
+        },
+        c = {
+          cid = 2,
+          primary = false,
+          required = true,
+          type = "int",
+        },
+        d = {
+          cid = 3,
+          default = "def",
+          primary = false,
+          required = false,
+          type = "text",
+        },
+      }, sch)
     end)
 
     it("gets a sql table schema info", function()
-      local sch = db:schema("test", true).info
+      local sch = db:schema "test"
       eq({
         a = {
           cid = 0,
@@ -732,10 +758,10 @@ describe("sql", function()
       eq(true, db:exists "test")
     end)
 
-    it("won't override the table schema if it exists", function()
+    it("skip overriding the table schema if it exists", function()
       db:create("test", { id = "not_a_type", ensure = true })
       local sch = db:schema "test"
-      eq("text", sch.title, "should not be nil")
+      eq("text", sch.title.type, "should exists and should be still text not be nil")
     end)
     it("auto enable foreign_keys on usage", function()
       db:create("test_keys", {
diff --git a/test/auto/table_spec.lua b/test/auto/table_spec.lua
@@ -53,38 +53,108 @@ describe("table", function()
         name = "text",
       },
     }
+    local detailed_schema = {
+      id = {
+        cid = 0,
+        primary = false,
+        required = false,
+        type = "int",
+      },
+      name = {
+        cid = 1,
+        primary = false,
+        required = false,
+        type = "text",
+      },
+    }
     local new = db:table("newtbl", opts)
 
     it("initalizes db with schema", function()
-      eq(opts.schema, new:schema(), "should be identical")
-      eq(opts.schema, new.tbl_schema, "should be identical")
+      eq(detailed_schema, new:schema(), "should be identical")
     end)
     it("should not rewrite schema.", function()
       local new2 = db:table "newtbl"
-      eq(opts.schema, new2:schema(), "should be identical")
-      eq(opts.schema, new2.tbl_schema, "should be identical")
+      eq(detailed_schema, new2:schema(), "should be identical")
+      eq(detailed_schema, new2.tbl_schema, "should be identical")
 
       local new3 = db:table("newtbl", { schema = { id = "string" } })
-      eq(opts.schema, new3:schema(), "should be identical")
-      eq(opts.schema, new3.tbl_schema, "should be identical")
+      eq(detailed_schema, new3:schema(), "should be identical")
+      eq(detailed_schema, new3.tbl_schema, "should be identical")
     end)
   end)
 
   describe(":schema", function()
     it("returns schema if self.tbl exists", function()
-      eq({ a = "integer", b = "text", c = "text" }, t1:schema())
+      eq({
+        a = {
+          cid = 0,
+          primary = false,
+          required = false,
+          type = "integer",
+        },
+        b = {
+          cid = 1,
+          primary = false,
+          required = false,
+          type = "text",
+        },
+        c = {
+          cid = 2,
+          primary = false,
+          required = false,
+          type = "text",
+        },
+      }, t1:schema())
     end)
     it("returns empty table if schema doesn't exists", function()
       eq({}, t2:schema())
     end)
     it("creates new table with schema", function()
-      local schema = { id = "int", a = "text", d = "text" }
+      local schema = {
+        a = {
+          cid = 0,
+          primary = false,
+          required = false,
+          type = "text",
+        },
+        d = {
+          cid = 1,
+          primary = false,
+          required = false,
+          type = "text",
+        },
+        id = {
+          cid = 2,
+          primary = false,
+          required = false,
+          type = "int",
+        },
+      }
       eq(true, t2:schema(schema), "Should return true")
       eq(schema, t2:schema(), "should return the schema.")
       eq(true, t2.tbl_exists, "should alter self.exists value")
     end)
     it("should drop and recreate the table if not schema.ensure", function()
-      local new = { id = "int", a = "text", f = "text" }
+      local new = {
+        a = {
+          cid = 0,
+          primary = false,
+          required = false,
+          type = "text",
+        },
+        f = {
+          cid = 1,
+          primary = false,
+          required = false,
+          type = "text",
+        },
+        id = {
+          cid = 2,
+          primary = false,
+          required = false,
+          type = "int",
+        },
+      }
       eq(true, t2:schema(new), "Should return true")
       eq(new, t2:schema(), "should return the schema.")
       eq(true, t2.tbl_exists, "should alter self.exists value")
