Using LuaLaTeX and SQLite3

Question

I am trying to access SQLite databases from LuaLaTeX but am not able to load an installed lsqlite3 package. Here's what I tried so far:

1. Took a brandnew Xubuntu 14.04 installation
2. Installed a complete TeX Live 2014
3. Installed Luarocks (which installed Lua5.1 on the way)

4. Used Luarocks to install lsqlite3

   uwe@luabuntu:/media/uwe$ luarocks list

   Installed rocks:

   lsqlite3 0.9.1-2 (installed) - /usr/local/lib/luarocks/rocks

5. Appended the package.path in the following TeX file:

   \documentclass{article}    
   \usepackage{luacode}
   
   \begin{document}
   
   \begin{luacode*}
     package.path="/usr/local/lib/luarocks/rocks;/usr/local/lib/luarocks/rocks/lsqlite3; 
     /usr/local/lib/lua/5.1/lsqlite3.so" .. package.path
   
     require("lsqlite3")
   \end{luacode*}
   
   Hello Lua!
   
   \end{document}
   

The error I receive is the following:

! LuaTeX error [\directlua]:3: module 'lsqlite3' not found:
  no field package.preload['lsqlite3']
  [luatexbase.loader] Search failed
  [kpse lua searcher] file not found: 'lsqlite3'
  [kpse C searcher] file not found: 'lsqlite3'
  [oberdiek.luatex.kpse_module_loader]-eroux Search failed
stack traceback:
  [C]: in function 'require'
  [\directlua]:3: in main chunk.
\luacode@dbg@exec ...code@maybe@printdbg {#1} #1 }
l.9 \end{luacode*}

What must be set to have LuaLaTeX find the lsqlite3 package?

EDIT

I have tried to adjust everything following the comments below:

1. Adjusted /usr/local/texlive/2014/texmf.cnf
2. Put everything into the example

3. Still get the error about the missing lsqlite3 module with the following example

   \documentclass{article}
   \directlua{
   require "lualoader"
   }
   
   \usepackage{fontspec}
   \usepackage[english]{babel}
   \begin{document}
   
   \directlua{package.path='/usr/local/share/lua/5.1/?.lua;/usr/local/share/lua/5.1/?/init.lua;/home/uwe/.luarocks/share/lua/5.1/?.lua;/home/uwe/.luarocks/share/lua/5.1/?/init.lua;/usr/share/lua/5.1//?.lua;/usr/share/lua/5.1//?/init.lua;./?.lua;/usr/local/share/lua/5.1/?.lua;/usr/local/share/lua/5.1/?/init.lua;/usr/local/lib/lua/5.1/?.lua;/usr/local/lib/lua/5.1/?/init.lua;/usr/share/lua/5.1/?.lua;/usr/share/lua/5.1/?/init.lua'
   package.cpath='/usr/local/lib/lua/5.1/?.so;/home/uwe/.luarocks/lib/lua/5.1/?.so;./?.so;/usr/local/lib/lua/5.1/?.so;/usr/lib/x86_64-linux-gnu/lua/5.1/?.so;/usr/lib/lua/5.1/?.so;/usr/local/lib/lua/5.1/loadall.so'
   require "lualoader"
   }
   
   \directlua{%
   local sqlite3 = require("lsqlite3")
   local db = sqlite3.open_memory()
   
   db:exec[[
     CREATE TABLE test (id INTEGER PRIMARY KEY, content);
   
     INSERT INTO test VALUES (NULL, 'Hello World');
     INSERT INTO test VALUES (NULL, 'Hello Lua');
     INSERT INTO test VALUES (NULL, 'Hello Sqlite3')
   ]]
   
   for row in db:nrows("SELECT * FROM test") do
     tex.print(row.id .. " : ".. row.content )
   end
   
   }
   \end{document}
   

Answer 1

It is possible to modify the package loading mechanism in Lua. In the case of LuaTeX, the kpse library is used to load modules instead of the default mechanism that uses package.path and package.cpath. So setting these variables will not have any effect. But nothing prevents us from using both methods using a Lua module, lualoader.lua. Save the snippet below as a file in the directory containing your document:

-- lualoader.lua
-- this is copied from luatexbase.loader
local make_loader = function(path, pos, loadfunc)
  local default_loader = package.searchers[pos]
  local loader = function(name)
    local file, _ = package.searchpath(name,path)
    if not file then
      local msg = "\n\t[lualoader] Search failed"
      local ret = default_loader(name)
      if type(ret) == "string" then
        return msg ..ret
      elseif type(ret) == "nil" then
        return msg
      else
        return ret
      end
    end
    local loader,err = loadfunc(file, name)
    if not loader then
      return "\n\t[lualoader] Loading error:\n\t"..err
    end
    return loader
  end
  package.searchers[pos] = loader
end
local binary_loader = function(file, name)
  local symbol = name:gsub("%.","")
  return package.loadlib(file, "luaopen"..symbol)
end
make_loader(package.path,2,loadfile)
make_loader(package.cpath,3, binary_loader)

We use the function make_loader to insert a function searching package.path or package.cpath at a specified index in the package.searchers table. 2 for lua files, 3 for binary modules. We also need to use different functions to load a module depending on its type. We use loadfile for Lua files and slightly more complicated function for binary modules that uses package.loadlib.

As an example, we can try loading lsqlite3 from LuaTeX:

\documentclass{article}
\directlua{
require "lualoader"
}
\usepackage{fontspec}
\usepackage[english]{babel}
\begin{document}
\directlua{%
local sqlite3 = require("lsqlite3")
local db = sqlite3.open_memory()
db:exec[[
  CREATE TABLE test (id INTEGER PRIMARY KEY, content);
INSERT INTO test VALUES (NULL, 'Hello World');
  INSERT INTO test VALUES (NULL, 'Hello Lua');
  INSERT INTO test VALUES (NULL, 'Hello Sqlite3')
]]
for row in db:nrows("SELECT * FROM test") do
  tex.print(row.id .. " : ".. row.content )
end
}
\end{document}

It is important to run LuaLaTeX with the --shell-escape flag, because binary libraries are blocked by default. With --shell-escape you can execute external programs and call binary libraries.

The example yields:

Edit:

As Uwe pointed out, luarocks as installed by some Linux package managers may not work correctly. I experienced this on Fedora, so I installed luarocks from source. Another solution may be to set package.path and package.cpath to the directories listed by the command

luarocks path

On my own system, this yields:

$ luarocks path
export LUA_PATH='/home/michal/.luarocks/share/lua/5.2/?.lua;/home/michal/.luarocks/share/lua/5.2/?/init.lua;/usr/local/share/lua/5.2/?.lua;/usr/local/share/lua/5.2/?/init.lua;/usr/share/lua/5.2/?.lua;/usr/share/lua/5.2/?/init.lua;/usr/lib/lua/5.2/?.lua;/usr/lib/lua/5.2/?/init.lua;./?.lua'
export LUA_CPATH='/home/michal/.luarocks/lib/lua/5.2/?.so;/usr/local/lib/lua/5.2/?.so;/usr/lib/lua/5.2/?.so;/usr/lib/lua/5.2/loadall.so;./?.so'

So a modified preamble would be:

\directlua{    
package.cpath = '/home/michal/.luarocks/lib/lua/5.2/?.so;/usr/local/lib/lua/5.2/?.so;/usr/lib/lua/5.2/?.so;/usr/lib/lua/5.2/loadall.so;./?.so'
package.path = '/home/michal/.luarocks/share/lua/5.2/?.lua;/home/michal/.luarocks/share/lua/5.2/?/init.lua;/usr/local/share/lua/5.2/?.lua;/usr/local/share/lua/5.2/?/init.lua;/usr/share/lua/5.2/?.lua;/usr/share/lua/5.2/?/init.lua;/usr/lib/lua/5.2/?.lua;/usr/lib/lua/5.2/?/init.lua;./?.lua'
require "lualoader"
}

Answer 2

As of May 2017, there is a package on CTAN called luapackageloader, that allows you to modify the default package searching behavior in LuaTeX.

Once you load luapackageloader, doing a require will first try the default kpse based searcher. If the module isn’t found, it will also try to load the package from Lua’s package.path and package.cpath values.

Usage example

\documentclass{article}    
\usepackage{luacode}
\usepackage{luapackageloader}

\begin{document}

\begin{luacode*}
  package.path="/usr/local/lib/luarocks/rocks;/usr/local/lib/luarocks/rocks/lsqlite3; 
  /usr/local/lib/lua/5.1/lsqlite3.so" .. package.path

  require("lsqlite3")
\end{luacode*}

Hello Lua!

\end{document}

Additional note: you can do eval $(luarocks path) on your shell to set the package path using environment variables which the Lua interpreter will pick up automatically. That way, you do not need to fiddle with the package.path yourself.

Answer 3

Based on comments on this question, I have the following line in /usr/local/texlive/2014/texmf.cnf:

CLUAINPUTS  = .;$SELFAUTOLOC/lib/{$progname,$engine,}/lua//;/usr/lib/{$progname,$engine,}/lua//

But my luarocks list gives:

Installed rocks:

lsqlite3
   0.9.1-2 (installed) - /usr/lib/luarocks/rocks-5.2

so you may need to adjust the second path according to your installation.

Answer 4

Summary

Searching for the package

• In normal Lua, a package is searched in package.path and package.cpath.

• In LuaTeX, a package is by-default searched with kpse.

• There are 2 ways to modify where LuaTeX searches for packages

1. modify behavior of kpse

   Modify texmf.cnf file, variables LUAINPUTS and CLUAINPUTS.

2. make Lua load from package.(c)path, then modify those

   • With \usepackage{luapackageloader}, packages are searched in both kpse and package.(c)path.

   • The value of package.(c)path can be set manually by the user within the Lua code itself, but it's better to set environment variables e.g. LUA_PATH, LUA_CPATH.

     If you run eval $(luarocks path) in bash, all processes spawned from that bash shell will inherit the environment variables.

Binary package

• If the package requires binary files, --shell-escape must be passed.

Versioning

• The package version must be compatible with Lua version. Do print(_VERSION) within Lua to check.

  To specify particular version to luarocks, do e.g. luarocks --lua-version 5.3 path.