Hacker News Reader

Comment by torstenvl | Hacker News Reader

It seems like the intended use-case would be for the database to be appended after the executable that is using it. But writing to a file that's being executed seems (to my naive view - I'm not terribly experienced) like it could cause issues.

What am I missing?

View on HN

I'm going to have to try this, but I suspect such writing won't work on Windows, which aggressively locks executables. Maybe it just locks the in-use range and allows appending.

On Linux this probably works fine because writing past the executable part of the file does not change any offsets beforehand.

(On NFS systems you can definitely overwrite in-use executables at any point, which works until the relevant bit gets paged out then back in)

Reading, on the other hand, ought to work fine everywhere. This continues "SQLite is a replacement for fopen()" - you can use it in similar cases to self-extracting ZIP files or self-executing JAR files.

I expect that even if it works on 'naked' Windows, many antivirus systems will take a dim view of it.

Many Windows programs embed resources (such as icons) in the executable; perhaps this access pattern would be indistinguishable from what common programs already do?

I'm honestly not sure - for instance maybe the embedded resources get loaded into RAM not read from the file. But if that is the case couldn't the embedded SQLite file be marked as a resource and loaded by the same mechanism?

I'd expect reading to be fine, but probably not writing. I could be wrong though, I'm just speculating.

In DOS days AVG's heuristic detection engine had flag for programs that did write into their own binary (it was reported as +i AFAIK), but it didn't have much weight as essentially anything compiled with Borland Pascal was flagged in this way (IIRC including AVG itself).

I suspect the intent would be an extension of the idea of using SQLite as the base for a custom binary file format. One use case might be to embed non-code resources (graphics, audio, localization strings, etc.) such that they can be easily queried and loaded via SQLite instead of some other method.

I confirm your suspicions.

People have been appending ZIP archives to executables, in order to hold non-code resources, for time out of mind. The appendvfs extension makes the same thing possible for SQLite databases. An SQLite database has advantages over a ZIP archive in that SQLite supports a far richer data model, is far faster for random access, and has a query language. Both ZIP and SQLite are well-defined and very widely deployed formats. Many developers are more familiar with ZIP, but there are more than 1 trillion SQLite database files in circulation, and SQLite is a recommended storage format according to the US Library of Congress (https://www.sqlite.org/locrsf.html) so SQLite should not be dismissed as being too unfamiliar.

Example use cases:

(1) We have experimented with (but not published) putting all the SQLite documentation into an SQLite database and appending it to a special webserver app. Download the "EXE" and double-click on it and the SQLite docs automatically pop up in your web browser. This is better than a pile-of-HTML-files in that it can use server-side computing for things like the "Search".

(2) Not yet published or documented, but you can do "make sqltclsh" from the SQLite source tarball and generate a TCL interpreter with SQLite built in. If you also append an SQLite database to this interpreter, it reads its scripts from the database. Use this to build stand-alone Tcl/Tk/SQLite applications.

(3) By 3rd-party user request: the Fossil version control system allows a Fossil repository (which is just an SQLite database) to the end of the "fossil.exe" binary. This is being used (I am told) to provide a rich package of read-only but versioned content to non-technical users. The non-techies just put the "document.exe" file on there windows desktop and double-click, and a webserver pops up showing the reports they need, with complete historical versioning provided by Fossil.

"Not yet published or documented, but you can do "make sqltclsh" from the SQLite source tarball and generate a TCL interpreter with SQLite built in. If you also append an SQLite database to this interpreter, it reads its scripts from the database. Use this to build stand-alone Tcl/Tk/SQLite applications."

Holy shit. This will be a godsend. I've been searching for an easy and robust way to package Tcl scripts into executables independent of a full-blown Tcl installation (I've tried a couple different approaches, but they all tend to have issues on some platform or other). This answers my prayers rather thoroughly, provided it can be documented and made relatively easy to do.

That's a neat application for this. Don't use it as a live data store, but as a (much) better way to embed and access static assets and default data, maybe even embed the default schema for a new installation where it copies the data to a normal writable file on first run.

Many binaries are signed, and just having data appended to them arbitrarily would break signatures. But if it's only used for static data anyways that wouldn't be a problem.

Localization strings might make sense. But graphics and audio? Seems a little cumbersome to have to re-blob the graphics resources into the database each time you make an update.

This would be more for read-only assets embedded into the executable itself (e.g. the little icons on buttons and stuff in a GUI). You probably shouldn't be updating these at runtime, and at compile-time I'd assume there would be tools doing it for you.

The executable file have defined areas, as long as you append/write to that executable, you're working on an area of the executable file that doesn't interfere with anything else

And about virus protection? Am I missing something too, but this seems impractical in desktop apps or even production environment - you always can whitelist, but this a concern and trust doesn't come cheap...

Writing would be scary but reading should be fine. That’d allow importing an entire database worth of data into an app packaged in a single executable. Rather nifty!

Reply on news.ycombinator.com