How to open database with immutable flag?

[JanValiska]

Hi,

this is more question than bug report.
I need to read firefox's bookmark database. But when FF is started the database is locked.

➜  hbo9xv4w.default sqlite3 places.sqlite
SQLite version 3.37.0 2021-11-27 14:13:22
Enter ".help" for usage hints.
sqlite> .tables
Error: database is locked
sqlite> 

When I open database with immutable flag, I can read items from database:

➜  hbo9xv4w.default sqlite3 'file:places.sqlite?immutable=1'
SQLite version 3.37.0 2021-11-27 14:13:22
Enter ".help" for usage hints.
sqlite> .tables
moz_anno_attributes                    
moz_annos                              
moz_bookmarks 
...

Is it possible to open database using sqlite.lua library in immutable manner?

Thank you.

[kkharji]

hmmm I believe is either append ?immutable=1 or a support for sqlite3_open_v2 is needed for this to work. could you try the first solution and how did you install sqlite.lua? using luarocks?

[JanValiska]

I use sqlite.lua in neovim text editor using packer plugin manager. Plugin is basically cloned into plugins directory.

I tried append ?immutable=1 to database file path provided to sqlite.new(path):open() function without luck.
Perhaps the sqlite3_open_v2 support is needed as you wrote.
Can I somehow help here?

[kkharji]

I just did a quick research and it seems to me that it's mostly likely a URI thing, see Recognized Query Parameters and URI filename examples section.

I'm starting to believe that maybe open_v2 won't be needed and perhaps some internal url processing strips the query params.

Can I somehow help here?

Can you navigate to sqlite/defs, and run the following throw evaluating the file

  local uri = 'file:places.sqlite?immutable=1'
  local opts = {}
  local conn = M.get_new_db_ptr()
  local code = clib.sqlite3_open(uri, conn)

  if code ~= M.flags.ok then
    error(("sqlite.lua: couldn't connect to sql database, ERR:"):format(code))
  end

  for k, v in pairs(opts) do
    if not M.valid_pargma[k] then
      error("sqlite.lua: " .. k .. " is not a valid pragma")
    end
    if type(k) == "boolean" then
      k = "ON"
    end
    M.exec_stmt(conn[0], ("pragma %s = %s"):format(k, v))
  end

  vim.inspect(conn[0])

btw defs.lua is where ffi interface are. so if changes needs to made it will be made here.

[JanValiska]

I tried to evaluate code you posted above. But I changed the uri to contain absolute path to sqlite db like:

local uri = 'file:/home/janko/.mozilla/firefox/hbo9xv4w.default/places.sqlite?immutable=1'

Evaluation ended up on

defs.lua:690: sqlite.lua: couldn't connect to sql database, ERR:
stack traceback:                                                                                         
       [C]: in function 'error'                                                                         
       ...nvim/site/pack/packer/opt/sqlite.lua/lua/sqlite/defs.lua:690: in function 'eval'              
       ...rks.nvim/lua/telescope/_extensions/bookmarks/firefox.lua:66: in function 'collect_bookmarks'  
       ...e-bookmarks.nvim/lua/telescope/_extensions/bookmarks.lua:54: in function <...e-bookmarks.nvim/
lua/telescope/_extensions/bookmarks.lua:40>                                                              
       ...ck/packer/start/telescope.nvim/lua/telescope/command.lua:184: in function 'run_command'       
       ...ck/packer/start/telescope.nvim/lua/telescope/command.lua:241: in function 'load_command'      
       [string ":lua"]:1: in main chunk

To be precise I only defined function eval in defs module and called it from another place in neovim configuration:

M.eval = function()
  local uri = 'file:/home/janko/.mozilla/firefox/hbo9xv4w.default/places.sqlite?immutable=1'
  local opts = {}
  local conn = M.get_new_db_ptr()
  local code = clib.sqlite3_open(uri, conn)

  if code ~= M.flags.ok then
    error(("sqlite.lua: couldn't connect to sql database, ERR:"):format(code))
  end

  for k, v in pairs(opts) do
    if not M.valid_pargma[k] then
      error("sqlite.lua: " .. k .. " is not a valid pragma")
    end
    if type(k) == "boolean" then
      k = "ON"
    end
    M.exec_stmt(conn[0], ("pragma %s = %s"):format(k, v))
  end

  vim.inspect(conn[0])
end

[kkharji]

Okay @Conni2461, informed me that perhaps open_uri need to be passed for this to work. Can you change the value of code to clib.sqlite3_open_v2(uri, conn, M.flags.open_uri, nil).

Also, to see errors change the body of code ~= M.flags.ok to

M.get_err_msg = function(code)
  for msg, ecode in pairs(M.flags) do
    if code == ecode then
      return msg
    end
  end
end

  if code ~= M.flags.ok then
    error(("sqlite.lua: couldn't connect to sql database, ERR: %s"):format(M.get_err_msg(code)))
  end

I've tried it on my machine and for some reason I got missue. maybe I got my firefox uri wrong.

edit: nvm, a mode need to passed instead of nil

[kkharji]

@JanValiska can u checkout #132 and pass as second argument {open_mode = "ro"}? https://github.com/dhruvmanila/telescope-bookmarks.nvim/blob/main/lua/telescope/_extensions/bookmarks/firefox.lua#L72. I believe it should work

[JanValiska]

Chcecked out #132 and added second agrument to new function on L32 of firefox.lua. I got this error:

E5108: Error executing lua ...nvim/site/pack/packer/opt/sqlite.lua/lua/sqlite/defs.lua:699: missing declaration for symbol 'sqlite3_eval'                                                                          
stack traceback:                                                                                                                                                                                                   
        [C]: in function '__index'                                                                                                                                                                                 
        ...nvim/site/pack/packer/opt/sqlite.lua/lua/sqlite/defs.lua:699: in function '__index'   

[kkharji]

That's odd. why the heck it's calling eval. are you still calling eval somewhere?

[kkharji]

oh wait the uri needs to be edited and have ?immutable=1 as well. I'm testing telescope-bookmarks right now

[JanValiska]

I've changed db connection to:

  local db = sqlite.new(utils.join_path("file:"..profile_dir, "places.sqlite?immutable=1"),{open_mode = "ro"}):open()

and it works.

Also as you found I forgot to remove old eval functon from collect_bookmarks function :/

[kkharji]

try

  local uri = "file:" .. utils.join_path(profile_dir, "places.sqlite") .. "?immutable=1"
  local db = sqlite.new(uri, { open_mode = "ro" }):open()

[kkharji]

  local db = sqlite.new(utils.join_path("file:"..profile_dir, "places.sqlite?immutable=1"),{open_mode = "ro"}):open()

Nice, as soon as I merge open_v2 pr, another pr could be open in telescope-bookmarks to somehow have the above changes. For now, just have fun with telescope-bookmarks