Hacker News Reader

Comment by lxe | Hacker News Reader

> Site users then start downloading the site torrent, but, rather than downloading pieces of the torrent in "rarest first" order, they download pieces based on the search query they performed.

Interesting. How does the system know where the result of the query might appear in the file?

View on HN

Interesting question. I looked at the source code to understand that.

SQLite knows where to look for when you open a SQLite database and you run a query, right? It just asks the underlying filesystem to provide N bytes starting from an offset using a C function, then it repeats the same operation on different portions of the file, it does its computation and everybody is happy.

The software relies on sqltorrent, which is a custom VFS for SQLite. That means that SQLite function to read data from a file stored in the filesystem is replaced by a custom function. Such custom code computes which Torrent block(s) should have the highest priority, by dividing the offset and the number of bytes that SQLite wants to read by the size of the torrent blocks. It is just a division.

See: https://github.com/bittorrent/sqltorrent/blob/master/sqltorr...

Yep. I've used the same basic technique to write a S3 VFS wrapper for APSW. It's not quick, by any means, but it's dang useful in cases where I have very large SQLite database son S3 and only need to get a small bit of information out of them.

I was not aware of APSW. Thank you.

For future reference: "APSW has the following enhancements/differences over pysqlite 2 (wrapping SQLite 3): - APSW stays up to date with SQLite. As features are added and functionality changed in SQLite, APSW tracks them. - APSW gives all functionality of SQLite including virtual tables, Virtual File System (VFS), BLOB I/O, backups and file control. [...]" -- https://rogerbinns.github.io/apsw/pysqlite.html#pysqlitediff...

One thing I'd warn about with APSW:

https://rogerbinns.github.io/apsw/download.html#easy-install...

Basically, if you install it from pip (without following the directions to install from git directly), then you end up with some ancient version, which rather defeats the point of APSW.

SQLite uses a memory page mechanism. You can define the page size. With a static page size you can divide the chunks and with that you have an offset for each page chunk, or if you have the start and the end offset of the bytes, you can also solve the page offset with that info.

If you only implement the filesystem shims, you will receive everything already figure it out, and the btree engine(storage) will ask you for the given chunk of data where you can resolve to the right torrent chunk, download it and just care about delivering the bytes that were asked.

I deliver sqlite dbs over torrent and the torrent chunks are optimized for the sqlite page sizes which are 64K.

In my case i dont stream the database, but through filesystem VFS it can be done if i had a need for it.

The beauty here lies in the SQLite architecture and the abstraction over pages.

Reply on news.ycombinator.com