I can't seem to load a VFS

[lawik]

.. and it is making me sad.

I am not sure if the URI way of loading vfs, eg: database: "file://./bla.db?vfs=httpvfs" is enabled so it could work.

I tried just hacking in the vfs I wanted in the C code where sqlite3_open_v2 is called but that hasn't worked. It might be this VFS doing some crime, or not loading, or something.

But something is up. Trying to do this one: https://github.com/psanford/sqlite3vfshttp

For now you can try it with this URL:

export SQLITE3VFSHTTP_URL="https://fly.storage.tigris.dev/underjord-streaming-public/podcast-index.db"

My attempt

config :exqlite,
  force_build: true,
  load_extensions: [ "../sqlite3vfshttp/sqlite3http-ext/httpvfs"]

defmodule Podstream.DB do
    def go do
        {:ok, conn} = Exqlite.Sqlite3.open("./foo.db", mode: :readonly)
        {:ok, statement} = Exqlite.Sqlite3.prepare(conn, "select * from podcasts limit 10")
        :ok = Exqlite.Sqlite3.bind(conn, statement, [])
        result = Exqlite.Sqlite3.step(conn, statement)
        IO.inspect(result, label: "result")
    end
end

// ..

    rc = sqlite3_open_v2(filename, &db, flags, "httpvfs");
    if (rc != SQLITE_OK) {
        return make_error_tuple(env, "database_open_failed");
    }
// ..

Forced mix deps.compile exqlite --force.

From SQLite docs:

The default VFS can be changed by registering or re-registering the VFS using the sqlite3_vfs_register() interface with a second parameter of 1. Hence, if a (unix) process wants to always use the "unix-nolock" VFS in place of "unix", the following code would work:

sqlite3_vfs_register(sqlite3_vfs_find("unix-nolock"), 1); An alternate VFS can also be specified as the 4th parameter to the sqlite3_open_v2() function. For example:

int rc = sqlite3_open_v2("demo.db", &db, SQLITE_OPEN_READWRITE, "unix-nolock"); Finally, if URI filenames have been enabled, then the alternative VFS can be specified using the "vfs=" parameter on the URI. This technique works with sqlite3_open(), sqlite3_open16(), sqlite3_open_v2(), and when a new database is ATTACH-ed to an existing database connection. For example:

ATTACH 'file:demo2.db?vfs=unix-none' AS demo2; The VFS specified by a URI has the highest priority. After that comes a VFS specified as the fourth argument to sqlite3_open_v2(). The default VFS is used if no VFS is specified otherwise.

[warmwaffles]

I haven't needed to load a VFS before. I'll take a look at this. We'll probably need to pass a new option or configuration to exqlite when opening to signal that it is VFS and special handling needs to take place.

[lawik]

So I imagine exqlite just passes the query params through and if that is the case, as long as the URI flag thing is switched on it SHOULD be able to do VFS without more modification. But I did not get this working.

Let me know if anything needs clarification.

[Hermanverschooten]

We had a nice conversation during @lawik s live stream this afternoon. After I decided to try some more and found this solution to query the remote db.

I followed Lars's directions to compile httpvfs.so, set the export SQLITE3VFSHTTP_URL=https://fly.storage.tigris.dev/underjord-streaming-public/podcast-index.db, I copied it to my test phoenix project to make it easier, still needed to supply the full path though.

defmodule Sqlvfs.Db do
  def go do 
    {:ok, conn} = Exqlite.Sqlite3.open("memory:")
    :ok = Exqlite.Sqlite3.enable_load_extension(conn, true)

    :ok =
      Exqlite.Sqlite3.execute(
        conn,
        "select load_extension('/home/herman/Projects/sqlite-objects/sqlvfs/httpvfs.so')"
      )

    {:ok, conn} = Exqlite.Sqlite3.open("file:///foo.db?vfs=httpvfs")
    {:ok, stmt} = Exqlite.Sqlite3.prepare(conn, "select * from podcasts limit 1;")
    {:ok, row} = Exqlite.Sqlite3.fetch_all(conn, stmt)
    :ok = Exqlite.Sqlite3.release(conn, stmt)
    :ok = Exqlite.Sqlite3.close(conn)
    IO.inspect(row)
  end
end

I followed this sequence as it mimics the flow we did with the sqlite3 cli.

[warmwaffles]

I can confirm that I am also able to get this to work.

[warmwaffles]

Something is up with the DBConnection implementation and how Ecto is initiating the connection. I can reach the VFS directly with the code above and work with raw data. What I am struggling with is Ecto establishing a pool of connections (1 in this case) and is not able to open the sqlite database at all which doesn't let it load the necessary extensions.

I will need to dig more into the initialization code to understand more.