Interesting other VFSes

[daenney]

I came across https://jtarchie.com/posts/2024-07-02-optimizing-large-scale-openstreetmap-data-with-sqlite today which had this interesting bit of using Zstd's seekable format to implement a (read-only) VFS with compression.

Their code is here https://github.com/jtarchie/sqlitezstd which uses https://github.com/psanford/sqlite3vfs (together with the mattn Cgo driver).

That repository also has an interesting other VFS linked, one that allows (partially) retrieving SQLite databases over HTTP (for example when a DB is stored in S3). That also reminded me a bit of Steampipe but that seems to be a touch more involved.

[ncruces]

For zstandard, see: #105

I think (hope?) that I don't need to do anything here. You should be able just use the reader VFS with zstd-seekable-format-go. If you can try and report back, it'd be very welcome. I could add an example to the docs.

[ncruces]

Steampipe is actually a (collection of) virtual table(s), not a VFS.

I really wanted to get them working, but they don't seem interested.

Sadly, it doesn't really help their business model so I doubt they ever will.

[ncruces]

For HTTP I already have a working (testable!) example:

go-sqlite3/vfs/readervfs/example_test.go

Lines 21 to 66 in fff8b1c

	func Example_http() {
	readervfs.Create("demo.db", httpreadat.New("https://sanford.io/demo.db"))
	defer readervfs.Delete("demo.db")
	db, err := sql.Open("sqlite3", "file:demo.db?vfs=reader")
	if err != nil {
	log.Fatal(err)
	}
	defer db.Close()
	magname := map[int]string{
	3: "thousand",
	6: "million",
	9: "billion",
	}
	rows, err := db.Query(`
	SELECT period, data_value, magntude, units FROM csv
	WHERE period > '2010'
	LIMIT 10`)
	if err != nil {
	log.Fatal(err)
	}
	defer rows.Close()
	for rows.Next() {
	var period, units string
	var value int64
	var mag int
	err = rows.Scan(&period, &value, &mag, &units)
	if err != nil {
	log.Fatal(err)
	}
	fmt.Printf("%s: %d %s %s\n", period, value, magname[mag], units)
	}
	// Output:
	// 2010.03: 17463 million Dollars
	// 2010.06: 17260 million Dollars
	// 2010.09: 15419 million Dollars
	// 2010.12: 17088 million Dollars
	// 2011.03: 18516 million Dollars
	// 2011.06: 18835 million Dollars
	// 2011.09: 16390 million Dollars
	// 2011.12: 18748 million Dollars
	// 2012.03: 18477 million Dollars
	// 2012.06: 18270 million Dollars
	}