8 ms·

VFS shim that allows a SQLite database to be appended to another file

176 points by blacksqr 8y ago

torstenvl 8y ago
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).
usermac 8y ago
Anything SQLite I upvote. ^_^
_pmf_ 8y ago
That's the kind of amazing emergent use cases that you get by sticking to known, old technologies and not doing anything supposedly clever.
- deleted 8y ago
  [deleted]
mhd 8y ago
So, resource fork 2.0?
phaedrus 8y ago
I could use this! While converting someone's Win32 C program to C++, it struck me that most of the magic numbers, window class names, arrays of GUI control data etc. which are defined in code (in both windows.h and the user source code) could be defined in a read-only sqlite database instead. And with this link here, it wouldn't even have to be a separate file.
- roel_v 8y ago
  On the user side, most of that can be stored in the resource section (for PE format executables, so Windows only for all practical intents and purposes). It has annoyed me many times that no such thing exist in Unix land (ELF, basically), and instead I have to rely on 'bin2hex' style tools and store that data in the data section.
  - dmitrygr 8y ago
    Elf has the concept of sections, and you can make as many of them as you want. The standard gnu Linker also automatically creates labels that you can use to get ahold of those sections. For example for a section titled XYZ, it'll automatically create to extern variables named __xyz_start and @__xyz_end sorry some such thing. You can place things into sections from source code, or add them later using objcopy
    - roel_v 8y ago
      Well yes, let me rephrase then: it's a shame there is no standard API to categorize and access data in other sections.
rkeene2 8y ago
For a bit of context, in the Tcl ecosystem there's the concept of "Starkits" which are executable scripts which contain a stub loader which mounts a virtual filesystem archive (which contains all the files the actual script needs to run). Typically this has been either a Metakit database or a Zip file, but they both have disadvantages when compared to SQLite and we've been wanting to move to SQLite for a while but it didn't support being appended to things, so we couldn't. At the most recent Tcl Conference DRH agreed to fix this and with this change it looks like he has done it. Expect an SQLite-backed Starkit soon ! Also, and unrelated, Starkits can be converted to "Starpacks" which replace the script-based stub loader with a native executable, so you can distribute Tcl-based scripts as a single file.
- oblio 8y ago
  Tcl has so many amazing ideas. I just wish it had higher adoption and everything about it wouldn't feel so... old. Look at the wiki for example. It should really be an actual wiki à la Wikipedia not some sort of strange Wikipedia discussion page masquerading as documentation.
  - btreecat 8y ago
    But where else would you find gems like this? https://wiki.tcl.tk/9832 https://wiki.tcl.tk/9832
    - pasbesoin 8y ago
      FINALLY, someone actually defined -- in practice -- what "(E)nterprise ready" means. Best definition yet, that I've seen.
  - srean 8y ago
    I like the quirky feel of Tcl'ers wiki. It gives it an unique playful character. I would like it to stay that way.
  - teraflop 8y ago
    > Look at the wiki for example. It should really be an actual wiki à la Wikipedia not some sort of strange Wikipedia discussion page masquerading as documentation. You can complain about it looking "old" if you want, but it's hardly fair to complain that it's not an "actual wiki". It follows the style of the original WikiWikiWeb which predates Wikipedia by a number of years.
hexmiles 8y ago
that's so cool, I don't understand if this is available in the standard distribution. it seem so, but when i tried to use the command-line tool i get "no such VFS", maybe i am missing something. Is there a tutorial/introduction on how to use it? is exactly what i needed for a couple of project edit: found! i need to use the '--append' option
- dangerbird2 8y ago
  VFSes just a c object created as part of the sqlite runtime and are registered and activated ^1 with the c api. You can recompile the sqlite command line to open with a given vfs object, or enable the URI filenames feature ^2 which allows opening a file with a given vfs. For the appendvfs in th original post, you probably need to use a new version of sqlite with it in the sourcecode ^1 https://www.sqlite.org/c3ref/vfs_find.html https://www.sqlite.org/c3ref/vfs_find.html ^2 https://www.sqlite.org/uri.html https://www.sqlite.org/uri.html
regularfry 8y ago
Heh, this reminds me of a trick I was working on right about the ruby 1.8->1.9 transition: import the tree of .rb files (including gems) into a sqlite database, mess with the `require` mechanism to read from it instead of the actual filesystem, and make a static build of ruby with sqlite itself, the ruby code database packaged as a .text area, and any binary extensions all linked up into a single file. Ta-da, single file static ruby apps and trivial deployments: a truly useful thing back when Capistrano was the latest and greatest. Unfortunately, 1.9 changed the ruby build system enough that I couldn't quite get it working, and I've not got round to looking at it since.
- gandreani 8y ago
  That is very neat! I always thought about expanding that idea to most programming languages. Most of the startup time is probably spend stating and reading files. Just from a quick experiment time python -c ''; time python -S -c '' real 0m0.079s user 0m0.021s sys 0m0.030s real 0m0.017s user 0m0.007s sys 0m0.007s From the manpage: -S Disable the import of the module site and the site-dependent manipulations of sys.path that it entails. I also considered doing the same thing for a linux distro. Just out of pure curiousity to what would happen to the boot up times. Wish I could have time to try it one day :(
  - namibj 8y ago
    What about going all the way and using an unikernel (or something approaching one)? Statically link suitable readonly data/code into a single global address space (to make use of optimizations possible by combining binaries, as apparent in e.g. busybox, and remove the overhead of code duplication and dynamic linking, if possible even skipping some overhead with syscalls, by allowing the compiler to reduce spilling registers to stack in an effort to prevent clobbering), and just make sensitive and modifiable data unavailable to or at least not writable by other/unprivileged processes. This would probably require some modification to GCC or LLVM, if the latter even supports building a normal Linux.
    - gandreani 8y ago
pdw 8y ago
I encourage everybody to check out sqlite's ext/misc directory. There are lots of interesting extensions there, and most people don't know about it.

176 points by blacksqr 8y ago

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

usermac 8y ago

Anything SQLite I upvote. ^_^

_pmf_ 8y ago

That's the kind of amazing emergent use cases that you get by sticking to known, old technologies and not doing anything supposedly clever.

deleted 8y ago
[deleted]

mhd 8y ago

So, resource fork 2.0?

phaedrus 8y ago

I could use this! While converting someone's Win32 C program to C++, it struck me that most of the magic numbers, window class names, arrays of GUI control data etc. which are defined in code (in both windows.h and the user source code) could be defined in a read-only sqlite database instead. And with this link here, it wouldn't even have to be a separate file.

roel_v 8y ago
On the user side, most of that can be stored in the resource section (for PE format executables, so Windows only for all practical intents and purposes). It has annoyed me many times that no such thing exist in Unix land (ELF, basically), and instead I have to rely on 'bin2hex' style tools and store that data in the data section.
- dmitrygr 8y ago
  Elf has the concept of sections, and you can make as many of them as you want. The standard gnu Linker also automatically creates labels that you can use to get ahold of those sections. For example for a section titled XYZ, it'll automatically create to extern variables named __xyz_start and @__xyz_end sorry some such thing. You can place things into sections from source code, or add them later using objcopy
  - roel_v 8y ago
    Well yes, let me rephrase then: it's a shame there is no standard API to categorize and access data in other sections.

rkeene2 8y ago

For a bit of context, in the Tcl ecosystem there's the concept of "Starkits" which are executable scripts which contain a stub loader which mounts a virtual filesystem archive (which contains all the files the actual script needs to run). Typically this has been either a Metakit database or a Zip file, but they both have disadvantages when compared to SQLite and we've been wanting to move to SQLite for a while but it didn't support being appended to things, so we couldn't. At the most recent Tcl Conference DRH agreed to fix this and with this change it looks like he has done it. Expect an SQLite-backed Starkit soon ! Also, and unrelated, Starkits can be converted to "Starpacks" which replace the script-based stub loader with a native executable, so you can distribute Tcl-based scripts as a single file.

oblio 8y ago
Tcl has so many amazing ideas. I just wish it had higher adoption and everything about it wouldn't feel so... old. Look at the wiki for example. It should really be an actual wiki à la Wikipedia not some sort of strange Wikipedia discussion page masquerading as documentation.
- btreecat 8y ago
  But where else would you find gems like this? https://wiki.tcl.tk/9832 https://wiki.tcl.tk/9832
  - pasbesoin 8y ago
    FINALLY, someone actually defined -- in practice -- what "(E)nterprise ready" means. Best definition yet, that I've seen.
- srean 8y ago
  I like the quirky feel of Tcl'ers wiki. It gives it an unique playful character. I would like it to stay that way.
- teraflop 8y ago
  > Look at the wiki for example. It should really be an actual wiki à la Wikipedia not some sort of strange Wikipedia discussion page masquerading as documentation. You can complain about it looking "old" if you want, but it's hardly fair to complain that it's not an "actual wiki". It follows the style of the original WikiWikiWeb which predates Wikipedia by a number of years.

hexmiles 8y ago

that's so cool, I don't understand if this is available in the standard distribution. it seem so, but when i tried to use the command-line tool i get "no such VFS", maybe i am missing something. Is there a tutorial/introduction on how to use it? is exactly what i needed for a couple of project edit: found! i need to use the '--append' option

dangerbird2 8y ago
VFSes just a c object created as part of the sqlite runtime and are registered and activated ^1 with the c api. You can recompile the sqlite command line to open with a given vfs object, or enable the URI filenames feature ^2 which allows opening a file with a given vfs. For the appendvfs in th original post, you probably need to use a new version of sqlite with it in the sourcecode ^1 https://www.sqlite.org/c3ref/vfs_find.html https://www.sqlite.org/c3ref/vfs_find.html ^2 https://www.sqlite.org/uri.html https://www.sqlite.org/uri.html

regularfry 8y ago

Heh, this reminds me of a trick I was working on right about the ruby 1.8->1.9 transition: import the tree of .rb files (including gems) into a sqlite database, mess with the `require` mechanism to read from it instead of the actual filesystem, and make a static build of ruby with sqlite itself, the ruby code database packaged as a .text area, and any binary extensions all linked up into a single file. Ta-da, single file static ruby apps and trivial deployments: a truly useful thing back when Capistrano was the latest and greatest. Unfortunately, 1.9 changed the ruby build system enough that I couldn't quite get it working, and I've not got round to looking at it since.

gandreani 8y ago
That is very neat! I always thought about expanding that idea to most programming languages. Most of the startup time is probably spend stating and reading files. Just from a quick experiment time python -c ''; time python -S -c '' real 0m0.079s user 0m0.021s sys 0m0.030s real 0m0.017s user 0m0.007s sys 0m0.007s From the manpage: -S Disable the import of the module site and the site-dependent manipulations of sys.path that it entails. I also considered doing the same thing for a linux distro. Just out of pure curiousity to what would happen to the boot up times. Wish I could have time to try it one day :(
- namibj 8y ago
  What about going all the way and using an unikernel (or something approaching one)? Statically link suitable readonly data/code into a single global address space (to make use of optimizations possible by combining binaries, as apparent in e.g. busybox, and remove the overhead of code duplication and dynamic linking, if possible even skipping some overhead with syscalls, by allowing the compiler to reduce spilling registers to stack in an effort to prevent clobbering), and just make sensitive and modifiable data unavailable to or at least not writable by other/unprivileged processes. This would probably require some modification to GCC or LLVM, if the latter even supports building a normal Linux.
  - gandreani 8y ago

pdw 8y ago

I encourage everybody to check out sqlite's ext/misc directory. There are lots of interesting extensions there, and most people don't know about it.