Shell: Support for WITH clauses (or more complex queries)

[SimonRichardson]

The shell only supports queries that start with SELECT statements:

go-dqlite/internal/shell/shell.go

Lines 88 to 92 in 251e831

	if strings.HasPrefix(strings.ToUpper(strings.TrimLeft(line, " ")), "SELECT") {
	return s.processSelect(ctx, line)
	} else {
	return "", s.processExec(ctx, line)
	}

Other queries can be made that don't start with a SELECT. It would be advantageous to add those to the shell. With dqlite being rolled out into production for Juju relatively soon, we're using the shell to inspect or remedy potential problems in the field. So using more of the sqlite SQL standard would be a good move forward.

[manadart]

Of course there's WITH. Anything else a query can begin with?

[SimonRichardson]

Transactions at a query level might be useful at some point (BEGIN, COMMIT, ROLLBACK).

There is VACUUM but I'm unsure what will happen if you actually run that in dqlite?

[MathieuBordere]

Every query performed with the shell is also wrapped in a transaction right now, that's something that's also unwanted in many cases, we could add a startup option to disable that behavior.

[MathieuBordere]

There is VACUUM but I'm unsure what will happen if you actually run that in dqlite?

VACUUM is currently not supported, see canonical/dqlite#435 (comment)

[jameinel]

Presumably INSERT would be very useful, and UPDATE on a live system when you need to correct bad data.

[cole-miller]

Ideally we'd avoid this kind of ad-hoc parsing of the SQL string entirely in go-dqlite and let the dqlite server take care of it. Since canonical/dqlite#477 dqlite's QUERY_SQL request can accomodate SQL strings that modify the database, which makes that potentially achievable.

[freeekanayaka]

Ideally we'd avoid this kind of ad-hoc parsing of the SQL string entirely in go-dqlite and let the dqlite server take care of it. Since canonical/dqlite#477 dqlite's QUERY_SQL request can accomodate SQL strings that modify the database, which makes that potentially achievable.

+1

The shell should only use QUERY_SQL, via Go's Tx.Query() or another equivalent method.

As long as things like RETURNING are not used, this should already work without any other modification to the dqlite server.

Bonus points for fixing RETURNING as well, so we can close #192 as well.