5 ms·

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

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?

yellowapple 8y ago
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.
- infogulch 8y ago
  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.
- SQLite 8y ago
  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 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.
  - yellowapple 8y ago
    "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.
- torstenvl 8y ago
  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.
  - yellowapple 8y ago
    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.
koolba 8y ago
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!
noselasd 8y ago
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
jm4rc05 8y ago
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...
pjc50 8y ago
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.
- JackCh 8y ago
  I expect that even if it works on 'naked' Windows, many antivirus systems will take a dim view of it.
  - phaedrus 8y ago
    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?
    - JackCh 8y ago
      I'd expect reading to be fine, but probably not writing. I could be wrong though, I'm just speculating.
  - dfox 8y ago
    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).

yellowapple 8y ago

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.

infogulch 8y ago
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.
SQLite 8y ago
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 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.
- yellowapple 8y ago
  "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.
torstenvl 8y ago
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.
- yellowapple 8y ago
  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.

koolba 8y ago

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!

noselasd 8y ago

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

jm4rc05 8y ago

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...

pjc50 8y ago

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.

JackCh 8y ago
I expect that even if it works on 'naked' Windows, many antivirus systems will take a dim view of it.
- phaedrus 8y ago
  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?
  - JackCh 8y ago
    I'd expect reading to be fine, but probably not writing. I could be wrong though, I'm just speculating.
- dfox 8y ago
  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).