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.
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.
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.
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.
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.
I also thought about having a persistent
sqlite3shell. 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 backgroundripgrepprocess). At some point, I plan to try again using asyncio subprocesses. I think most systems could support a few 100s ofsshsubprocesses 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
litexploreapp dies, I didn’t want to leave the shell running. I’m considering offering an option by which a user can upload a temporarysqlite3binary compiled with the-jsonflag, 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.