Repository: tami5/sql.nvim (lua)

Issue:
sql:insert and table:insert should not require the primary key, instead should return it.
Hello just testing the project, looks great just have some comments on the insert behavior.
[stmt:step](https://www.sqlite.org/c3ref/step.html) returns `SQLITE_ROW` after an insert from where the id of the inserted row is retrieved, there's also [last_insert_row_id](https://www.sqlite.org/c3ref/last_insert_rowid.html). Therefore the primary key of a table shouldn't be required when inserting.

> Each entry in most SQLite tables (except for WITHOUT ROWID tables) has a unique 64-bit signed integer key called the "rowid". The rowid is always available as an undeclared column named ROWID, OID, or _ROWID_ as long as those names are not also used by explicitly declared columns. **If the table has a column of type INTEGER PRIMARY KEY then that column is another alias for the rowid.**

Pull request:
This change allows for `db:insert()` and `t:insert()` to return the last inserted row id as a second return value, provided by sql. The process is done after the rows statements have been completed but finishing the transaction to guarantee the value integrity. This closes #46 

The interface to users doesn't change thanks to lua useful adjustment. https://www.lua.org/pil/5.1.html I'm sure you know @tami5  I just like to document my PR :)

I've also added tests.

If you agree with the changes and the tests I can update the readme to reflect the changes.

Patch:
diff --git a/lua/sql.lua b/lua/sql.lua
index 8cfd9f05..4aa9c1fa 100644
--- a/lua/sql.lua
+++ b/lua/sql.lua
@@ -323,7 +323,7 @@ end
 --- Insert to lua table into sqlite database table.
 ---@params tbl string: the table name
 ---@params rows table: rows to insert to the table.
----@return boolean: true incase the table was inserted successfully.
+---@return boolean|integer: true incase the table was inserted successfully, and the last inserted row id.
 ---@usage db:insert("todos", { title = "new todo" })
 ---@todo support unnamed or anonymous args
 ---@todo handle inconflict case
@@ -332,6 +332,7 @@ function sql:insert(tbl, rows)
   local ret_vals = {}
   local info = self:schema(tbl, true)
   local items = self:pre_insert(rows, info)
+  local last_rowid
   self:__wrap_stmts(function()
 
     for _, v in ipairs(items) do
@@ -341,11 +342,12 @@ function sql:insert(tbl, rows)
       s:bind_clear()
       table.insert(ret_vals, s:finalize())
     end
+      last_rowid = tonumber(clib.last_insert_rowid(self.conn))
   end)
 
   local succ = u.all(ret_vals, function(_, v) return v end)
   if succ then self.modified = true end
-  return succ
+  return succ, last_rowid
 end
 
 --- Update table row with where closure and list of values
diff --git a/lua/sql/table.lua b/lua/sql/table.lua
index d9252024..0e9af3f5 100644
--- a/lua/sql/table.lua
+++ b/lua/sql/table.lua
@@ -254,15 +254,15 @@ end
 ---@param rows table: a row or a group of rows
 ---@see t:__run()
 ---@see sql:insert()
----@return boolean
+---@return boolean|integer
 function t:insert(rows)
   return self:__run(function()
-    local succ = self.db:insert(self.name, rows)
+    local succ, last_rowid = self.db:insert(self.name, rows)
     self:__clear_cache(succ, rows)
     if succ then
       self.has_content = self:count() ~= 0 or false
     end
-    return succ
+    return succ, last_rowid
   end)
 end
 


Test patch:
diff --git a/test/auto/sql_spec.lua b/test/auto/sql_spec.lua
index 39663006..f7a84931 100644
--- a/test/auto/sql_spec.lua
+++ b/test/auto/sql_spec.lua
@@ -244,21 +244,25 @@ describe("sql", function()
   describe(':insert', function()
     local db = sql:open()
     assert(db:eval("create table todos(title text, desc text)"))
+    local ex_last_rowid = 0
 
     it('inserts a single row', function()
-      db:insert("todos", {
+      local rows = {
         title = "TODO 1",
         desc = "................",
-      })
-      local results = db:eval("select * from todos")
+      }
+      local _, last_row_id = db:insert("todos", rows)
+      ex_last_rowid = ex_last_rowid + 1
+      local results = db:eval("select rowid, * from todos")
       eq(true, type(results) == "table", "It should be inserted.")
+      eq(last_row_id, results[1].rowid, "It should be equals to the returned last inserted rowid " .. last_row_id)
       eq("TODO 1", results[1].title)
       eq("................", results[1].desc)
       db:eval("delete from todos")
     end)
 
     it('inserts multiple rows', function()
-      db:insert("todos", {
+      local rows = {
         {
           title = "todo 3",
           desc = "...",
@@ -271,8 +275,11 @@ describe("sql", function()
           title = "todo 1",
           desc = "...",
         },
-      })
-      local store = db:eval("select * from todos")
+      }
+      local _, rowid = db:insert("todos", rows)
+      ex_last_rowid = ex_last_rowid + #rows
+      local store = db:eval("select rowid, * from todos")
+      eq(rowid, store[#store].rowid, "It should be equals to the returned last inserted rowid " .. rowid)
       eq(3, vim.tbl_count(store))
       for _, v in ipairs(store) do
         eq(true, v.desc == "...")
diff --git a/test/auto/table_spec.lua b/test/auto/table_spec.lua
index ed0cbb37..fa3e265d 100644
--- a/test/auto/table_spec.lua
+++ b/test/auto/table_spec.lua
@@ -346,10 +346,14 @@ describe('table', function()
   seed()
 
   describe(":insert", function()
+    local ex_last_rowid = #demo
     it("inserts a row", function()
-      local new_rows = { a = 109,  b = "0", c = "0" }
-      eq(true, t1:insert(new_rows), "it should return true")
-      eq({new_rows}, db:eval("select * from T where a = 109"), "it should have the new rows")
+      local new_row = { a = 109,  b = "0", c = "0" }
+      ex_last_rowid = ex_last_rowid + 1
+      local succ, last_rowid = t1:insert(new_row)
+      eq(true, succ, "it should return true")
+      eq(ex_last_rowid, last_rowid, "it should return row_id " .. ex_last_rowid)
+      eq({new_row}, db:eval("select * from T where a = 109"), "it should have the new rows")
     end)
 
     it("inserts a list of rows", function()
@@ -358,7 +362,10 @@ describe('table', function()
         { a = 22, b = "sdj", c = "in" },
         { a = 33, b = "sbs", c = "en" }
       }
-      eq(true, t1:insert(new_rows), "it should return true")
+      ex_last_rowid = ex_last_rowid + #new_rows
+      local succ, last_rowid = t1:insert(new_rows)
+      eq(true, succ, "it should return true")
+      eq(ex_last_rowid, last_rowid, "it should return row_id " .. ex_last_rowid)
       eq(new_rows, db:eval("select * from T where a = 11 or a = 22 or a = 33"), "it should have the new rows")
     end)
   end)