akojo / clutch Goto Github PK

Code like

db:update("INSERT INTO some_table VALUES(?)", {42})

inserts the string "42" instead of the number 42 (at least with Lua 5.3). The reason is the following snippet of bind_one_param:

static int bind_one_param(lua_State *L, sqlite3_stmt *stmt, int index)
{ 
  int status = SQLITE_OK;

  if (lua_isstring(L, -1)) /* <------ is true for all numbers */
  { 
    size_t len;
    const char *text = lua_tolstring(L, -1, &len);
    status = sqlite3_bind_text(stmt, index, text, len, SQLITE_TRANSIENT);
#if LUA_VERSION_NUM >= 503
  }
  else if (lua_isinteger(L, -1)) /* <------- not reachable  */
  { 
    status = sqlite3_bind_int64(stmt, index, lua_tointeger(L, -1));
#endif
  }
  else if (lua_isnumber(L, -1))
  { 
    status = sqlite3_bind_double(stmt, index, lua_tonumber(L, -1));
  }
  else if (lua_isnil(L, -1))

The reference manual for Lua 5.3 states:

int lua_isstring (lua_State *L, int index);

Returns 1 if the value at the given index is a string or a number (which is always convertible to a string), and 0 otherwise.

The fix: Ask for lua_isinteger and lua_isnumber first. In my timezone, it's evening and I feel admittedly too lazy to go the official way of forking, branching and submitting an official pull request for such a small thing.

Also: I love your bindings (I have looked previously and was reluctant to use sqlite because the others are ugly).

The current version published on LuaRocks seems to coerce number-like string parameters to numbers. The good news is that the latest code in this repo resolves the issue, at least for me. :) Type affinity issues with DBs only opened by clutch were noted in #2, and this is the situation in which I triggered this issue as well.

Minimal script to reproduce the issue:

local clutch = require('clutch')
local db = clutch.open(':memory:')

local s = '123456'
db:update('CREATE TABLE tab (fld TEXT)')
db:update("INSERT INTO tab VALUES (?)", { s }) -- Inserts '123456.0'
-- db:update("INSERT INTO tab VALUES ('123456')") -- Works as expected.
print(db:queryone('SELECT * FROM tab').fld)

Expected output:

123456

Actual output:

123456.0

(Thanks for this library, by the way. It is very clean and usable, whereas every other way I found of SQLiteing in Lua was pretty miserable.)

Hi, thanks for sharing this great tool. Its quite awesome.

I wrote a shell script that displays the output of sqlite queries in Rofi, a dmenu like program. The shell script got a little out of hand, so I am attempting to rewrite it in lua as a noob exercise, and for the simple fact the rest of the project is lua.

Clutch makes working with sqlite in lua a dream, but I cant figure out how to use queryall. I know its supposed to return an array-like table, but I cant figure out how to make use of those results. I can get queryall to return a list of tables...

table: 0x55ae87169a10
table: 0x55ae87169a80
table: 0x55ae868aac00
table: 0x55ae868aac40
table: 0x55ae868aacb0

But am stuck at that point. Regarding the script, I am thinking I'll have to use queryall rather than query. When I try to pipe the results of query to Rofi it will only display the first line, I assume because query returns one row at a time rofi grabs the first line from stdin and thats it. Any pointers you may have would be greatly appreciated.

Thanks for your time.

EDIT:

Okay, so I was able to figure out queryall, but rofi is still just grabbing the first result. So the issue is most likely just the result of my s**t programming. Ill have to ponder on this one.

local results = db:queryall("select distinct albumartist from beets")
    for i,v in ipairs(results) do
    	local val = (v.albumartist)
	local cmd = ('echo ' ..val.. ' | ' .._rofi.. '  ')
	local buf = os.capture(cmd)
      return
	end
end 

Messy script I through together real quick to test:

#!/usr/bin/lua

clutch = require 'clutch'
db = clutch.open('beet.db')


_rofi = [[ rofi -p '>>' -dmenu -i \
  -config /home/clu/.config/rofi/beets.rasi \
  -kb-custom-1 ctrl+0 -kb-custom-2 ctrl+9 \
  -async-pre-read 15
]]

-- capture system cmd
function os.capture(cmd)
  local f = assert(io.popen(cmd, 'r'))
  local s = assert(f:read('*a'))
  f:close()
  return s
end


function queryARTIST()
  for r in db:query("select distinct albumartist from beets") do
    local val = (r.albumartist)
    local cmd = ('echo ' ..val.. ' | ' .._rofi.. '  ')
    local buf = os.capture(cmd)
    return
  end
end


function mainMENU()
  local cmd = ('echo -e "Artists\nGenres\nScores" | '.._rofi..'  ')
  local buf = os.capture(cmd)
  if string.find(buf, "Artists") then
    queryARTIST()
  elseif string.find(buf, "Genres") then
    print(buf)
  elseif string.find(buf, "Scores") then
    print(buf)
  end
end
mainMENU()