The CLI is great, but most SQL command line tooling is shell based

[sanderpick]

Most DB drivers have an interactive shell. This allows the user to "just type SQL" (or mongo or whatever) without having to prepend statements with a command like tableland create | write | read.

With https://github.com/tablelandnetwork/wasm-sqlparser now available, we can determine if statements are creates, writes, or reads and target to correct RPC method / contract function.

---

Have a shell almost means you get state. This opens the door to dynamically switching between networks, e.g., type something like use chain ethereum, use validator <url>, etc.

Some other ideas off the top of my head:

• A local notions of a "dataset" (one or more tables) grouped by some namespace
• Table aliases

While these state-related ideas would be fun to explore, I think "just type SQL" is compelling enough on its own to want a shell.

[joewagner]

Big plus one, lots of potential for fun ideas here!

I like the idea of enabling dynamic changing the Validator you're connected, but my intuition here is that the shell would remain agnostic on network. As a use-case, once I connect to a Validator (use validator <url>) I can then do JOINs across different networks. The shell would need to know what wallet to use and what Validator to connect to.
When opening the shell, or after, we could have a means to choose the wallet via an ENV var, or use one of the 25 publicly available hardhat wallets.
e.g.

$ tableland-shell
> wallet hardhat1
WARNING: This account, and its private key, are publicly known.
Any funds sent to them on Mainnet or any other live network WILL BE LOST.
Account #1: 0x70997970C51812dc3A010C7d01b50e0d17dc79C8 (10000 ETH)
Private Key: 0x59c6995e998f97a5a0044966f0945389dc9e86dae88c7a8412f4603b6b78690d

or something like

$ export MY_ENV_VAR=0x59c6995e998f97a5a0044966f0945389dc9e86dae88c7a8412f4603b6b78690d
$ tableland-shell
> wallet MY_ENV_VAR
Using Public Key: 0x70997970C51812dc3A010C7d01b50e0d17dc79C8

Maybe typing just wallet could show the pubkey...

[sanderpick]

sounds pretty great 👍

[sanderpick]

@carsonfarmer do you have thoughts here related to your research / usage of the CLI?

[carsonfarmer]

I think this is certainly a useful feature! There are some things to consider:

With https://github.com/tablelandnetwork/wasm-sqlparser now available, we can determine if statements are creates, writes, or reads and target to correct RPC method / contract function.

That is actually not the case yet. Since we don't have access to the AST from JS-land yet (see tablelandnetwork/go-sqlparser#13) we don't have a way to differentiate the statement types. So that would be something we'd beed to figure out first. Once we have that, everything else can for sure be done. With this work now in tablelandnetwork/js-tableland-cli#84, I think things like wallet selection etc is totally doable.

With all of that in mind, I'm wondering if there is any way we can leverage an existing SQL shell? For instance, it could be possible to create a tableland VFS for SQLite that might make it possible to use Tableland locally from _within_SQLite?

[sanderpick]

I think this is certainly a useful feature! There are some things to consider:

With https://github.com/tablelandnetwork/wasm-sqlparser now available, we can determine if statements are creates, writes, or reads and target to correct RPC method / contract function.

That is actually not the case yet. Since we don't have access to the AST from JS-land yet (see tablelandnetwork/go-sqlparser#13) we don't have a way to differentiate the statement types. So that would be something we'd beed to figure out first. Once we have that, everything else can for sure be done. With this work now in tablelandnetwork/js-tableland-cli#84, I think things like wallet selection etc is totally doable.

Ah, I see. How is @awmuncy doing the read / write selection in the admin UI?

With all of that in mind, I'm wondering if there is any way we can leverage an existing SQL shell? For instance, it could be possible to create a tableland VFS for SQLite that might make it possible to use Tableland locally from _within_SQLite?

Sounds interesting. From 1st principals, I'd want a shell that can attach local, tableland, and d1 databases. Something like this chicken scratch:

attach 'database1.db' as db1;
attach 'database2.db' as db2 from tableland;
attach 'database3.db' as db3 from d1;


.databases // see all connected

select
  *
from
  db1.SomeTable a
    join 
  db2.SomeTable b on b.SomeColumn = a.SomeColumn;

The best way to do that? Maybe it's the VFS route, or maybe something hacky to start (exec sqlite3 from within a JS shell and proxy calls to the process).

[carsonfarmer]

Ah, I see. How is @awmuncy doing the read / write selection in the admin UI?

Ha, I just realized I was being lazy when I said that before... we didn't have a way to check that via the AST on the Javascript side... but of course we can still access the AST from the Go side!

tablelandnetwork/wasm-sqlparser#11

[sanderpick]

Something to look at: https://github.com/isaac-mcfadyen/d1-console