1. 14
    1. 1

      It looks like at the moment this treats each SQL query as a separate process - it runs both ssh and the sqlite3 command over that SSH connection to completion for each query it executes.

      Evidently this works OK - and maybe it performs well enough that it’s not a problem for an application with just one user. But I wonder if it could instead maintain a persistent SSH connection with the sqlite3 shell running on the remote machine as a persistent process, then feed it commands one by one and read the responses?

      The implementation would be quite a bit more complex so this would only be worthwhile if the performance improvement made things materially better for the user.

      1. 3

        Hey it’s a big improvement on how remote SQLite works in DataStation right now which is to copy the entire file locally before querying. I’m going to have to port this idea.

      2. 1

        I also thought about having a persistent sqlite3 shell. In the past, I’ve tried playing with persistent subprocesses reading and writing data and always had problems with stdin/out getting locked, buffered writes and other issues which haven’t let me have a working implementation (I tried this with a background ripgrep process). At some point, I plan to try again using asyncio subprocesses. I think most systems could support a few 100s of ssh subprocesses as long as the connections are multiplexed and the initial authentication process is only done by the first connection.

        Having a persistent shell also has the “clean-up problem”. If the litexplore app dies, I didn’t want to leave the shell running. I’m considering offering an option by which a user can upload a temporary sqlite3 binary compiled with the -json flag, this makes me be more cautious about what I do in the remote VM.

        I’ve also been doing some (promising, for now) tests with sshfs.