This forum post records a crazy idea I am working on for using SQLite as the transport container for content in a REST-type client/server environment.

The purpose of this post is to record my idea so that I don't forget about it, as I need to focus on a different problem right this minute and come back to the idea later.

The Problem

There is a diverse collection of worker machines on the internet performing some task, and single server that records the progress of the work being done, and/or assigns tasks to workers. Workers are "diverse" in the sense that they are running on various different hardware platforms and operating systems. The workers communicate with the central server request using HTTPS - TLS encrypted HTTP requests.

All communication is initiated by the workers. Workers contact the server to receive a work assignment, send periodic status reports back to the server as the work progresses, and report completion of the task when done. Each communication is a round-trip HTTPS message initiated by the worker.

The worker is an ordinary TCL script. The server is also TCL, though implemented with the help of Wapp.

The Crazy Idea

The idea I have is that the HTTPS payload for each request and each reply is an SQLite database file. Each message and each reply contains, at a minimum, a single table as follows:

 CREATE TABLE sota(key TEXT PRIMARY KEY, value ANY);

Other tables might be added, as needed, depending on the particulars of the request.

The use of SQLite as the transport container facilitates transfer of binary data (which is difficult to move using a more conventional encoding like JSON). SQLite also make it easy to encode/decode the transfer as SQLite is readily at hand and so no external JSON or XML encoder/decoder libraries need to be sourced.

Worker-side Processing

The worker TCL script is single threaded, and needs to attend to the work at hand. This, plus the fact that TLS is difficult to do in TCL means that the HTTPS messaging is delegated to an external program, "sota" or "sota.exe".

The worker first constructs an in-memory SQLite database which is the request message. Call this in-memory database "db-outbound". The worker then invokes the external "sota" command using "open" and sends the outbound database:

 set comm [open "!sota" r+b]
 puts $comm [db-outbound serialize]
 flush $comm

The worker then sets up a fileevent that will read the response on the $comm socket. The response is the reply database, and is imported into a new in-memory database using the "deserialize" method of the SQLite TCL interface. After receiving the database reply, the socket is closed and the reply is processed.

The URL is contained in the outbound database. Perhaps like this:

 db-outbound eval {
    INSERT INTO sota VALUES('url','https://somewhere.com/method');
 }

Authentication information (a password) might be similarly encoded using well-known keys of the sota table.

The reply is always an SQLite database. Even if an error occurs (example: "unknown host") the external sota command constructs an appropriate reply database, perhaps with the error message in the "error" key of the sota table.

Transport

Both the request and the reply database are sent as HTTP data with a mimetype of "binary/x-sota".

Server Side Processing (Using Wapp)

Wapp would be enhanced to recognize the binary/x-sota payload type on the HTTP request and to use "deserialize" to go ahead and load the database into an in-memory database accessible to the Wapp TCL logic. The details on the access method are yet to be worked out.

For security, Wapp may choose to run "PRAGMA integrity_check" on the database after it has been deserialized and reject any request for which the check does not pass.