SQLite Roadmap

[Rotario]

I'm just thinking about the possibilities of adding SQLite to this project, allowing for fully distributable persistent apps that can be shared around.

I have a feeling this may have applications in sharing research data? I know someone who does research in optimisation algorithms, and wants to be able to share her work done in Plotly Dash which I think is just a drag+drop interface for building React frontends. If she could build her no-code frontend in Dash, and there be some easy Lua API backend to return data from something like SQLite, then she might be able to easily release her actual research data in an accessible format, and know that it "just works" for anyone on any system.

This is just one "User story" but I think there's probably so many out there, I mean, Electron has massive uptake for devs and otherwise, even though the binaries are huge and unneccessary, so the potential here is huge.
Anyway - to the matter in hand, the lua sqlite library comes with the sqlite3 engine already in a single source file. Reading #97 I think @jart mentioned that there might be trouble writing to the sqlite file since it's zipped (and compressed?). Is there anything I can help with, bar just trying to get these lua bindings working to open, write read and close a db? Does anyone know where the barriers might lay in the zipped FS? May the sqlite file need decompressing to access, then recompressing?

[jart]

Enabling scientific analytics is one of the use cases I had in mind, since I used to be the tech lead of the TensorBoard team. In terms of data storage, you're in luck. I just pushed bf03b2e a few minutes ago which clears out the biggest blocker, which is that it enables us to have the executable open itself in read/write mode so it can use its internal ZIP file structure as a document store, similar to MongoDB. It furthermore means that we're able to restore the original APE header on startup, so if you run the thing and it makes changes to itself, those changes can be immediately distributed as soon as soon as you'd like. You don't even have to do anything. It's like a little Docker container, except you don't have to ask people to install something like Docker in order to run it. All they need is a single file.

SQLite is totally possible. But it might be more appropriately left to a separate file. If we integrate it then you'd likely need to distribute a db.sqlite to accompany your redbean.com in the same directory. That might be better for some people, even though naturally the SQLite API is going to be more complex for simple use-cases than the ZIP document store API will be.

Best way you can help is simply by (a) testing, and (b) helping me get the word out. This software is a permissively licensed gift to anyone who might find it useful and I want to see as many people gain the benefit of its value as possible. If you'd like to do engineering work, please refer to CONTRIBUTING.md which should help you get started. This codebase has examples. Helping me clear out issues is good way to get started making contributions.

[Rotario]

its internal ZIP file structure as a document store, similar to MongoDB.

That's cool, I guess we have to do that using Lua's I/O library? I guess keeping it simple server-side and maybe using a single endpoint that takes application/json and saves it to a file, done by AJAX on the frontend would be the quickest win? I've got a direct application for this right now.

[Rotario]

It'd be nice to have the Lua saving endpoint allow extra slashes after it for the document store collection name (to use MongoDB lingo), for example
/db.lua is the endpoint but
POSTing json to /db.lua/users would allow saving a new user to a users.json collection in the db folder for exampl:

[Rotario]

Also - this would not present a security problem (the "all data sent from the user must be validated on the backend" rule) since this is fully encapsulated on the user's machine, so if they want to mess with the POSTed data and mess up schema or add malicious data, then go right ahead. Although this last point might need some thinking about re: malicious data...

[jart]

One thing I've been thinking about doing is allowing HTTP PUT requests if the mode of a folder is the zip archive is chmod o+w. It'd be interesting. What do you think of that?

[ahgamut]

@Rotario I forked the SQLite Github repo so that I could compile the _sqlite internal module for Python:

https://github.com/ahgamut/sqlite/tree/cosmopolitan

Pretty easy to compile - run superconfigure.
It requires Tcl to create the amalgamation sqlite3.c and sqlite3.h though.

@jart would you accept a PR containing sqlite3.c (200k lines), all the necessary SQLite headers, and a sqlite3.mk ie Makefile-only recipe?

[jart]

Yes I would @ahgamut but I would have a slight preference for the sqlite-preprocessed version if possible, so it won't slam the build system as hard. If you submit a PR like this, be sure to do it in two commits:

1. First commit checks in the upstream files, byte for byte identical to the upstream source. Example: 1fbfbb3
2. Second commit changes whatever you need to change. Example: 8f52c0d

[ahgamut]

Um, I don't understand what you mean by sqlite-preprocessed version. Do you mean a non-amalgamation version of sqlite (multiple source files instead of a single sqlite3.c)?

I submitted a single commit because I thought it needed to build successfully when part of the monorepo.
Does 1fbfbb3 not add QuickJS to the overall build? Ok, I will submit another PR in the required 2-commit form.

[jart]

• https://www.sqlite.org/2021/sqlite-preprocessed-3350500.zip
• https://www.sqlite.org/download.html

[ahgamut]

I just pushed a 2-commit update with the single-file sqlite3.c.... should've checked Github first.
The SQLite README recommends using sqlite3.c because it can be optimized by the compiler better.
Anyway, I'll try it with the preprocessed files soon.

[ahgamut]

#162 uses the preprocessed version of SQLite.

[Rotario]

Yeah, that would be cool, are we talking about saving the raw request string as www-urlencoded or json or both? I think the least friction system would be the best. There's no programmatic way to discover existing documents right now though right? I guess the directory listing could be returned in json if the client accepts json.

…

On Sun, 2 May 2021 21:36 Justine Tunney, ***@***.***> wrote: One thing I've been thinking about doing is allowing HTTP PUT requests if the mode of a folder is the zip archive is chmod o+w. It'd be interesting. What do you think of that? — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#156 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AHA6QAZ2ZN6OG5Y24FRE5YTTLWZUDANCNFSM43EGJ6HA> .

[Rotario]

Also - before I forget, saw this on HN yesterday.
Would allow us to access sqlite with some standard JS Api without anything extra on the backend
https://news.ycombinator.com/item?id=27016630

[jart]

There's also LevelDB which is built into the browser. I'm not sure if I'd recommend a webassembly approach with sqlite.

[mskyttner]

If scientific analytics is an important use case, I think https://duckdb.org is an interesting alternative to sqlite, also with portability and small footprint. Looks like it offers better performance for analytics due to column oriented design and has support for some new formats, like support for parquet files and arrow. The https://github.com/duckdb/duckdb/releases has builds but it would be interesting to understand if it could be built as an "ape" :). It is possible to build as an AppImage (I tried a while ago at https://github.com/mskyttner/duckdb-cli-builds). It also has a Full Text Search extension. This db does not yet have a on-disk storage format that is finalized, but in the meanwhile parquet and compressed csv can be used instead.

[jart]

Business intelligence type stuff is usually done with full table scans. For example, if you wanted to make SQLite behave the same way as a columnar database, you'd have to change something like this:

create table people (
  id integer,
  name string,
  age integer
)

Into this:

create table people (
  id integer,
)
create table people_name (
  pid integer foreign key (people, id),
  name string
)
create table people_age (
  pid integer foreign key (people, id),
  age integer
)

Which is probably a pain if your goal is to do one-offs.

[mskyttner]

I prefer duckdb but love sqlite3 too. I think some data science and "reproducible open science" people like it because of its vectorized column-oriented database engine allowing faster queries. For me most use cases with databases involve more reading than writing, except when loading data.

And it is not like duckdb doesn't write data well or fast, or is limited to do only one column or table. To me, it does the same things that sqlite3 does, in pretty much the same way, but mostly it does it faster and it is way smoother to work off when starting off with parquet and CSV files (due to built-in read_csv_auto('test.csv.gz')).

The group behind MonetDB is working on it, a video here with more background: https://www.youtube.com/watch?v=PFUZlNQIndo. And a benchmark: https://github.com/jtommaney/blog/blob/blog/posts/DuckDB%20Profiling%201:%20%20vs%20SQLite3.md.

I also like to use it as a kind of sed or stream editor for datasets, kind of like read -> sql -> write from stdin to stdout, and formats like csv and parquet can go in and out. There is a nice tool for sqlite3 for that does this too: https://github.com/tobimensch/termsql/

[jart]

I prefer Dremel because it's web scale. I wrote a blog post once called Operation Roeshub where I used it to search GitHub using SQL and try to figure out who was impacted by a nasty serialization bug so I could send them 1 line pull requests. You can do that too.

[mskyttner]

Very interesting, thanks for sharing!

I was trying to come up with some small demo app for redbean + sqlite making use of FTS5 and thinking of a dataset to use for this. So I created this R script to generate a cosmosources.db with the content of the .c files searchable. The resulting db is attached here: cosmosources.db.gz

Next step would be to figure out how to make a redbean webape with a search function which can search this db with MATCH syntax.

User input such as "arm NOT abi*" would get translated to sql statements such as select file, highlight(fts, 0, '<b>', '</b>') location, highlight(fts, 3, '<b>', '</b>') code, snippet(fts, 3, '<b>', '</b>', '', 6) as snip, rank as score from fts where fts match 'arm NOT ABI*' order by rank; in a .lua page.

But I would need to become more aquainted with lua first. And then maybe try to see how it can be turned into a more dynamic autocomplete search function in the redbean webapp.

[decalek]

@mskyttner, I believe that Sourcetrail (source analysis tool) produces code index in SQLlite DB, such that one can render full and correct codebase browser for Cosmopolitan + real code queries ala CodeQL. Not tried yet, though :-(.