Actually Queryable Executables | Hacker News Reader

Actually Queryable Executables

fzakaria.com

341 pointsrguiscard85 comments

>I’m amazed how much collapses into a single domain: SQL.

perhaps is more correct to say "all data, including code, is table-representable, even though being a graph" or "everything falls back to tables" or even "relational algebra is all u need", but I strongly disagree SQL being a domain on its all, and that it (all) collapses into such domain.

One can collapse segment tables likewise into DATALOG, which is also a PROLOG-derivate. So then the thing demonstrated here is - "all collapses into grammars perhaps". which is not new, but there are plenty of engineering details, and whatnots to consider, to make such model viable for large-scale deployment. And trouble is it is not so easy to infer stuff about grammars before you expound/infer on them.

don't get me wrong - I love SQL, and respect SQLite and DuckDB for what they are. what we see here is one very curious approach and great demonstration.

It's interesting how different people fall into different "everything is a hammer" perspectives. I have a bunch of people around who do the same, everything collapses into table-like structures, but personally I always end collapsing everything into a tree, one way or another. Any problem I encounter, my brain seems to just default into "Yeah, arranged this way, this is clearly just a tree", and it keeps happening for stuff.

And for me, tables are just trees, but for them, trees are just tables.

A tree encodes a mandatory path or navigation to get to some piece of data. But not every piece of knowledge has a "true" path.

Whereas a relation ("table" in its crude form) can be queried and projected into many forms (including as a tree) but with multiple paths to the same data and ensure future uses are not trapped in a mandatory hierarchy. This was Codd's entire discovery and it still holds 50 years later.

And eventually tis all the same in the end, and in the beginning. To me all is grammars and program states eventually, and recursion, and prolog and all are diffrent perspective of the same concept/phenomenon.

Is there a higher-level abstraction than parametric grammars nagivating program states...?

As a sidenote, it seems think Stephen Wolfram has similar take on things, but like taken to extremes and applied to dynamic systems. His idea is that all that we experience is a byproduct of some ever-unfolding energies governed by parametric rules. Thus Ruliad. Though his thing sounds less plausible than what we (already) have (applied and working) in classic computing.

idk...

btw, table rows ar facts from PROLOG perspective and much of what SQL stands on is PROLOG. the parametric rules with conjunctions part is in the WHERE clause if one ever wonders... took a while for me to figure it out.

p.s. graphs are represented very often by means of tables, relations if u want. trees are graphs. then, on the other hand, we use trees to index table-storages. so the connection/mapping is there all the time.

I'm sure you've stumbled upon graphs that are not trees, which can be represented with tables.

But on the other hand table are represented with trees![1]

[1]: https://en.wikipedia.org/wiki/B-tree#B-tree_usage_in_databas...

And those trees are usually represented as tables via an array of structs or similar. IMHO, the architecture of memory and memory access should push us more towards tables as a default data structure than trees.

As long as there are no self loops, then you can represent a graph with a set of spanning trees that cover the edges. This is particularly efficient when using a depth or parent vector tree representation and is seen in HPC applications.

Everything can be a tree with a single node that stores a blob of the data in whatever format you want.

Data-oriented programming suggests if you can structure your program as a series of transformations over tables, it'll be fast, but if it's a series of transformations over trees, it'll spend all its time waiting for cache misses.

Something like that was very popular in early 2000 in Tcl community and used at what then considered "scale." I posted a comment here with links: https://news.ycombinator.com/item?id=49445681

Tabular representation is like the RISC for machine code architecture, but for data.

> We can collapse not only a complete distribution but all the state for every application into a single file, alleviating the need for /var/ or /tmp/ or /home/ or any other filesystem. The program can store its own state in the same file it is running from, and it can do so transactionally.

On the one hand: I don't think I want that. Including static content with the binary makes sense, certainly. However, storing writable run-time data there feels messy; I prefer a read only binary which is handed a writable state directory (it is worth saying that I've spent a lot of time with nix and other immutable distros).

On the other hand: This is the coolest, most fun thing I've seen in a good while, and I absolutely want to see it taken 1000% further. Who cares about perfectly operationalized immutable deployments when the hacker spirit is in the air?

I'll bet you could use this to run with another thing APE does: fat binaries. If program text lives in a database, what's one more row? Just

  SELECT text FROM executable WHERE arch = $(uname -m)

and off we go:)

Edit: actually on further consideration this feels perfect for smalltalk; you can put the VM and image in a single file

I think its really cute that SQlite is used as the container format - but my mind wanders to other things used in the same way.

One of my favourite ways to develop apps is to use Lua - for everything. Construct an efficient core application framework, then embed the Lua VM in it, and then do all program logic and control flow in Lua, then put the Lua bytecode in the binary with luastatic, and off we go.

So this technique could be used to tack on state as Lua bytecode, meaning I could then accomplish something I’ve wanted for my Lua apps for decades - migration. I could have the app save its state safely, then simply transfer the binary itself to another machine, and recover gracefully.

Of course this could be used with any tools - not just Lua - but the idea of having the entire runtime binary included in the Lua state table is just so delicious I wanna try it ..

> One of my favourite ways to develop apps is to use Lua - for everything. Construct an efficient core application framework, then embed the Lua VM in it, and then do all program logic and control flow in Lua, then put the Lua bytecode in the binary with luastatic, and off we go.

Okay, you have to expand on this. Do you have a blog post or anything that goes into more detail?

I don't have an article or blog post, alas.

Basically, take your application framework of choice - in my case its JUCE (C/C++) - and embed a Lua VM in it. Put the UI in the framework, and then encode all of the program logic and application control flow in Lua, which marshals the UI and the rest of the system through the VM interface.

Sure, you can just 'do everything in C/C++', but I've found that for long-term projects, doing app logic in Lua, a simpler language to wrangle, has been very fruitful. And, it puts the framework in an interesting context - serving the VM - which reduces bugs and keeps things pretty tidy. Most of the C/C++ code is just UI and system-level scaffolding - which I then write Lua tests for - and then once this is in place, the app logic is very productively developed and shipped as byte code. The App becomes a host for the logic.

Isn’t this similar to what Redbean does (which inspired this queriable executable idea), except that in Redbean the application is web server based?

Actually its a core feature of the Lua VM as an applicable technology - Lua is literally embedded all over the place.

My favourite other example is turboLua:

https://turbo.readthedocs.io/

I am not sure this is the same kind of thing. In Redbean, you have a single executable that runs on any Operating System (that's right, the same executable file, this is enabled by CosmopolitanC) and that executable has a SQLite DB inside that can be used by the Lua code, which is also itself inside the executable file, and it can be modified at will and still run. I think most people don't realize just how crazy this is. It's a lot more than LuaVM embedded in a "normal" C executable.

    $ luastatic —help

Do you have any problems with antivirus or Defender complaining about the embedded VM?

Never. But I deploy in controlled environments where my .exe is well known before install.

I think there are false negatives in some of the lesser-maintained antivirus tools - but I have never had issues with it.

What if I'm running multiple instances of the same binary?

As another comment points out down in the thread, that's already a problem today with programs storing their config and data in a fixed location.

On the other hand, sqlite supports concurrency and ACID transactions, so it might actually be better at handling concurrent access to shared state

Yes, that is one of the particular reasons I'd much prefer a single ro binary that gets handed one data dir per instance at runtime:) There's also (at least) a security angle and the question of how you reset to a known-working state if it mutates itself.

SQLite doesn't have system-versioned / temporal tables, but a quick search turns up a fairly straightforward approach. Instead of overwriting existing rows, always write new rows with timestamps. https://www.ohnekontur.de/2024/02/19/unlocking-time-harnessi...

I'm thinking now of the hoops you have to jump through to edit a package.json file to update your dependencies, and thinking yeah, what if you could do: "UPDATE dependencies SET version='1.2' where name='madlib';"

So something like `npm i madlib@1.2` then?

A single SQLite ‘connection’ can be ATTACHed to multiple database files (which appear as separate prefixed schemata, but you can do things like joins across them) and it seems to work properly if some are read-only and some are not!

> the question of how you reset to a known-working state if it mutates itself

I have this problem on my computers already, because configuration files and other program data is spewn across all kinds of directories and files all over the place.

The only two options I have is a) do a factory reset and reinstall software from scratch and spend time configuring things again, or b) live with all of the extra garbage that has been excreted by the software on my systems just so that I can keep the data that I actually want to keep.

Whereas to pick up all of my data that I do care about and copy only that and nothing extra to another place, is extremely time consuming to the point that it’s not realistically doable :(

Of course, cramming absolutely everything including user documents into the program won’t solve that either.

My ideal system would separate data that was written because of me (non-default config values, bookmarks, etc) from documents etc made by me, from garbage that the program wants to write that I absolutely do not care about and do not want to know about.

Look, I try to avoid bringing up nix in unrelated conversations. But.

> The only two options I have is a) do a factory reset and reinstall software from scratch and spend time configuring things again, or b) live with all of the extra garbage that has been excreted by the software on my systems just so that I can keep the data that I actually want to keep.

NixOS solves this.

> My ideal system would separate data that was written because of me (non-default config values, bookmarks, etc) from documents etc made by me, from garbage that the program wants to write that I absolutely do not care about and do not want to know about.

And NixOS with https://github.com/nix-community/impermanence does this, forcefully. Actually normal nix might solve it depending on how far you want to go.

Again, I wouldn't usually try to evangelize it, but you have described your ideal system, and it exists.

While way too restrictive, I believe android actually has the correct idea here, having separate data folders per app. The problem is that you can't really touch this per app data folders - even backup is a special operation only doable by the OS (or you root the phone).

Spamming my home folder on the other hand is criminal..

afaik Plan9 already solved this issue - you had 'layers' of filesystems, not unlike Docker images/OverlayFS, with each process getting its own view of the filesystem. The last layer was non-persistent scratch. Of course you'd still need to reconcile writes to things like config files, which would be used by multiple processes, but the general approach is sound imo.

$ cp /original/binary.app /new/binary.app && /new/binary.app

Makes sense to me. Also for production, I would have the app change its name by adding a datetime to the filename, indicating when it was last synchronized.

This is a very appealing idea, I too hope it gets 1000% more attention and adoption.

There are applications where you want to run multiple instances but also want to have them use shared state, e.g. for their preferences (editable via their UI).

Or think of an editor or something like Excel. You probably don’t want the files you edit to all be stored in the binary and require separate export/import functions for sharing, or for editing using alternative applications.

And in the end, that goes for pretty much all data. The applications are the tools you use to view or modify the data, but the data remains independent.

    local appConfig[myUniqueArgv0] = { .. some config data .. }

Seems viable to me. I have no problem with different apps having access to each instance' unique app configuration - could be handy for a lot of things. "Copy system config", "Reset to config <blah>", etc.

On phones we do all use the model that the data is part of the app.

I think it makes sense for usability and it's bad for security. Imagine you put a program file on a USB stick and it just magically remembers your settings and data when transferred to other computers.

This is deranged, and perilously close to dumb, which makes it one of the best things I’ve seen on hacker news this year.

Absolutely wonderful stuff.

Yeah. Somehow the rate of such and other cool, trippy topics seems to have declined on HN in favor of more and more AI topics.

This project was only possible due to AI helping the author with the toil, as mentioned in the docs.

I'd say its perilously close to brilliant and dumb at the same time.

Oh I’m sorry to bring such a presentist idea to such a cool project, but if we ever get LLMs inferencing cheaply on consumer hardware and capable of efficient continuous learning, this insane format might be the perfect way to share your unique tamagotchi of expertise in a specific area

Instead of post-processing the binary to add the application (non-SELF) schema, you could run database migrations before servicing requests. Thus, every time you start the process, the app creates and/or upgrades its own schema.

The SELF upgrade (heh, self upgrade) and rollback processes could benefit from some... fancier... footwork.

Your example has a new binary copying old data into it, but then you have to move the new binary to the deployed location. Which means an outage through stop service, data migration, replace file, start service.

What if the upgrade process was more like... write the new SELF data into the old binary, send SIGHUP, and then the service fork+execs itself, while doing haproxy-like zero downtime FD handover?

Replacing the SELF data in the existing file is safe right now, because you can't mmap segments into memory. But if you do end up figuring out some clever BLOB alignment mmap stuff, you could do the SELF upgrade like a data migration! INSERT segments/symbols, fork+exec, and the data migration cleans out the old code. :-D

Updating the SELF schema to allow multiple sets of segments and symbols would allow for this upgrade trick, but could do other fancy things... thin multi-arch binaries where only the code segments differ.

BLOB alignment should also mean more efficient static asset serving and a bunch of other niceties... definitely worthy of investigation.

However -- very strong however -- as fun as this is, I would never, ever, ever allow an internet-facing service binary to be self-writable. :-)

"internet-facing service binary to be self-writable", yeah, this elevates sql injections to a whole new levels!!!

So like a Lisp, APL, or Smalltalk program image, but with SQL as the driving force.

Everything old is new again. And I don't mean it in a disparaging way. There's lots of "old" ideas that are simply great ideas that did not win on their own time but might come back with force in the future.

Right! As I was reading I was thinking of ways to evolve this, and one idea kept coming back: What if we don't store compiled code in the SQLite database, but something primitive like s-expressions representing code? Then we could update definitions live as regular INSERTs. Then I realised I'd reinvented Lisp.

The self-modifying executable that can modify itself on disk is kinda horrifying to me. In that kind of "ok yes, you have proven you can, but I really think you shouldn't" kind of way.

Incredibly impressive on a conceptual level though. If someone proposed doing this while having the executable only grab a read-only reference to itself, I think that would be a legitimately solid idea.

Between this and actually portable executable I'm not convinced someone hasn't made a PNG thats a spreadsheet, or an audio file the somehow renders DOOM across the room. HN amazes me with the absolutely cursed ideas of implementing a minecraft in pure css (or showing whatever other nightmares one can do with CSS). It shows the most incredible creativity in what one can do with the freedom of arranging bits however one wants. I'm in love with all these cursed projects and hope they never stop.

PoC || GTFO has an issue that is a PDF that is also a valid NES rom which will render the md5sum of the PDF itself, and other crazy tricks of that type over the years.

https://dl.packetstormsecurity.net/mag/pocgtfo/pocorgtfo14.p...

I consider PoC || GTFO the forefront of OS research, personally .. so many usability advances in that scene.

It took me several minutes to understand how this even works. Brilliant hack of using binfmt_misc rules under hood which is like a shebang for scripts but a custom interpreter. The idea of moving the ELF byte code to the segments table and executing the web server from there is next level crazy.

Good for deployment but an accidental deletion of the binary can cause loss of both data and code. Good approach for AI harness and agents though.

> All state is updated in the same SQLite file as the program itself.

And now all SQL injection bugs are RCE!

It's a fun idea, though.

This for some reason reminds me of OS/400 libraries. Basically on AS/400 everything is an object, libraries are basically like DB tables but are first class OS objects (like files in unix). You might want to read up on it, they took the concept incredibly far and it’s of course still a part of i series to this day. You basically can use SQL right on the command line.

Fantastic ideas. The SQL Injection to ACE pipeline is incredible.

Reminds me of starkit[2]/tclkit[3]. Directly queryable [4], but these programs were ZIP files with ZIP file VFS, they contain shared libraries and so on. One would add most, if not all, functionality from article into starkit-based application.

  [1] https://en.wikipedia.org/wiki/Metakit - base tech
  [2] https://wiki.tcl-lang.org/page/Starkit
  [3] https://wiki.tcl-lang.org/page/Tclkit
  [4] https://wiki.tcl-lang.org/page/Starkit+Meet+Zip

I wonder if the interpreter could create a mount namespace and mount virtual filesystems backed by the SQLite database itself, so you wouldn’t need source changes to self-contain (ha!) file accesses.

People have created FUSE filesystems backed by SQLite before, but I don’t see how you’d get much unique value out of an RDBMS as filesystem if you’re only interacting with is through a traditional non-transactional byte stream API.

Vendors who have done this like IBM with the AS/400 have typically put the database APIs front and center in their sales pitches, and POSIX on AS/400 is more akin to WSL than something well-integrated into the traditional single-level store (or at least was a few years ago when I last took a look at it).

If you’re _only_ interacting with it through a traditional filesystem API, the benefits are small — you could just embed an existing format like qcow2 into the file probably.

But if a “legacy” application is only interacting with the filesystem through filesystem APIs, but you as a developer can interact with it as if it were a database (with transactional semantics that extend beyond the virtual inode boundary), I think it can still be very valuable.

Neat stuff. I’m sold!

Could the webserver receive a code segment from the web and add it to itself (like a plugin upload)?

You can think further, you can build a no-server server; Example let Nginx be running, then forward requests to the database, running the code, query data and responding then shutting down again. Need the service to do something regularly, just trigger that part of the database through crontab.

And yes you can let authorized users add custom code, make it run in the database like a micro-micro-vm and do stuff on triggers etc in the database as well.

This makes it even more powerful, because users can switch code during runtime and you can have multiple users "programming" against the same database at the same time working against the same data.

I build a SQLite clone in Rust with this analogy in mind, and unlike SQLite it support multiple writers by giving them separate segment in a file. Calles the programming "language" PySpell, it converts Python into AST then into RUST with a lot of cool features you can enable when the database and programming language is living in the same place.

http://github.com/punnerud/mpedb

This is AI slop (until proven otherwise).

Already using it as the primary DB in multiple off my apps and dev pipelines. I don't trust my own "slop", but the all the tests from SQLite, Python, PHP etc agains the DB

I looked at some code. The CLI has comments about a feature where you can access a nonexistent database file without creating it but I doubt that actually works, as it seems to create the database file before processing commands. And I wonder if multi database actually works because it handles prefixes like "SELECT * FROM db.foo" by splitting it into db selector "db" and unprefixed statement "SELECT * FROM foo" which obviously wouldn't work if the statement accessed more than one database.

I also found comments about replacing identifiers in SQL (used to implement ALTER statements probably?) which say that it won't work right if two things in the database have the same name and it'll get caught by a sanity check later.

That's just the parts I looked at.

Where this approach might shine right now is .o/.obj files for assemblers/compilers/linkers. Instead of using COFF/ELF/whatever binary voodoo, a relational DB container makes most of the previously hard things trivial, including optional vendor extensions.

This would be a super pragmatic approach for a modern compiler suite. This is the brilliant part of the whole idea.

Is it just me or does this create a huge potential security vulnerability where it’s a lot easier to trick the application into mutating itself. There’s also the alternate problem where if the file is placed in a privileged location, you won’t be able to store any state. And the final problem that each user needs their own copy of the application if it’s a multi-user application.

The biggest concern for me would be the security angle - if there would be some way to seal the executable itself and descriptor tables so that an application can be guaranteed to never touch that and only ever modify the other “runtime” tables. Not doable with raw sqlite though since it has no kind of ACL mechanism, but would be a neat extension so that the interpreter handed the handle to the process directly with the privileged tables cordoned off from writing.

Simple -- you just add a custom SQLite VFS that ensures particular SQLite pages are mapped into underlying OS pages that are appropriately mprotect()ed. Try to modify the executable pages and you crash (W^X). Or you know, don't try to use a hack like this where security matters.

SQLite's unix VFS is actually using a mixture of mmap and write() by default[1] and you'd need to combine that with mseal() and some more pieces to actually pull it off. It would probably be possible.

(There's prior art here; although done differently: https://sqlite.org/src/file/ext/misc/appendvfs.c).

[1]: https://sqlite.org/mmap.html

So the next logical step is to make the whole OS a self modifying queryable sqlite file :)

Between this and the nix/guix stuff, Farid is definitely becoming one of my favourite mad computer scientist bloggers, right up there with Justine.

(Or computer mad scientist? Whatever (mad scientist) && (computer scientist) is)

SQLite is >< close to becoming the lingua franca for all next-generation ideas

https://archive.is/eMRTd

Somebody else mentioned OS/400, I’ll mention MUMPS and its “globals”… specifically ^rOBJ

Everything old is new again.

TandemOS says hi.

SQL over ELF headers is clever. Debug symbol lookups alone would save me a ton of grep piping.

Yeah, for practical use I'm more interested in the author's sqlelf tool for querying actual ELF binaries than the use of sqlite for the runnable binary itself. The latter is super cool but sounds like too much of a performance hit to be practical.

The whole thing is brilliant anyway. Up with this sort of thing!

Worth mentioning the windows msi installer format is based around SQL

SQLar - https://sqlite.org/sqlar/doc/trunk/README.md

...basically `s/zip/sqlar/g` and you're not that far off.

SQLite as an application file format - https://sqlite.org/appfileformat.html

...basically instead of `*.docx`, you'd do something like `UPDATE pages SET content='...' WHERE number=1` (or something). It's a compelling idea!

CouchDB/CouchApp - https://couchapp.readthedocs.io/en/latest/intro/what-is-couc...

...about 2 decades before its time. Think mongo/redis document store with a built-in concept of offline sync + replication. Survived a bit by `pouchdb` which was slightly simplified and focused on mobile apps.

Views and indexes were `*.js` functions run on every insert/update, and "apps" were basically `*.html` + `*.js` served out of the database directly. Think `address-book.couchdb` which contained `index.html` and `index.js` (and `/admin/...` routes) which "knew" how to render + edit the data itself.

Prior to that was the glorious moment when PHP began including sqlite drivers by default and you could basically do: `php something.php mydatabase.db` and have a full/hermetic browser-app experience without needing to stand up an apache server or mysql database to connect to.

"Data that knows how to edit itself" (what was that weird new-fangled parquet data encoding format or whatever) is an AWESOME idea! Maybe Mr. McCarthy was on to something when he kept bringing up "homoiconic" (or is this "monomorphism"...)

If you're listening, I propose: `*.sqlitexe`

A quote from Jurassic Park comes to mind...

Reinventing Smalltalk from first principle

i still don't understand what it does. Can someone ELI5? I've read both articles, still clueless.

I'll take a shot at this.

Native applications ship as executable files. These files are basically a combination of machine code instructions (the program logic to be run) + a bunch of extra data that needs to be loaded into memory for the program to run + metadata so the operating system knows how to combine it all.

The first article noted that the file format for this can be thought of as a very specialised, antiquated database format. The author then managed to convert some real applications of theirs into sqlite databases of the necessary program data, and then taught the operating system how to treat those sqlite databases as programs to run.

The second article builds on this, by creating a program shipped as an sqlite database, and then making that program read and write itself (through sqlite code) to store its application state. So instead of having a web server application that loads an sqlite db table, it is just a database file that the operating system can run as a native application, that also stores user data in itself.

these are useful primitives for VM/Sandboxes

extremely creative stuff!

When I read program like this, I ask myself: Am I the only one that do not dream to make skynet?

interesting

Reply on news.ycombinator.com