How to provide the legacy flags for sqlcmd back-compat and for the modern subcommand style CLI

[stuartpa]

The purpose of this discussions is to come up with a plan on how to implement #180, which is the first of what will be several PRs, that wire up the old ODBC sqlcmd flags to the new cobra based modern sub-command approach to the sqlcmd CLI, while still maintaining backward compatibility with the command-line.

With the new sub-command based cobra based CLI, e.g:

sqlcmd query "SELECT @@version"
sqlcmd bulk export Person.Address

And the new "install" (create is synonym) sub-command, so end users can get going with a T/SQL endpoint directly from sqlcmd, which also populates a ~/sqlcmd/sqlconfig file which provides a current context, which provides default parameters for hostname/port/credentials etc. (which otherwise would have been provided by parameters like -S, -U, -P etc.).

sqlcmd install mssql
sqlcmd create mssql

We have also implemented all the back-compat flags from the old ODBC based sqlcmd, which does not use sub-commands at all, and instead only uses command line flags (no sub-commands), so if a user provides a sqlcmd command-line with no sub-commands (and only flags), we run in back-compat mode.

A user may run in back-compat mode (no sub-command), but may have a current context (from sqlconfig), which can populate values like -S, -U etc.

Also a user may have a current-context, which would by default populate the equivalent of -S, -U etc., but the user may also provide the -S, -U parameters with the intention of override the values in the sqlconfig (current context).

Cobra also has the concept of global flags, what flags should we make global. Does a global flag have to apply all sub-commands, e.g. -U (username) or -E (trusted) doesn't apply to sqlcmd install, but does apply to sqlcmd query and sqlcmd bulk

• The only flags that should be "Global" are ones that apply to other commands than "query"
• Therefore, flags that establish the connection parameters to a SQL instance are usually good to be global, since many commands are likely to connect to an endpoint
• but flags specific to running a sqlcmd script should not be global, like DisableCmdAndWarn or BatchTerminator

Some Options (non-exhaustive):

1. We could decide not to bother adding overrides for every connection property like trust certificate/login timeout/application intent for the "query" command and leave them completely in the legacy implementation. Folks using the modern interface would set them in the connection configuration only.
2. We could define some new way to provide connection overrides like a set of name value pairs instead of separate switches for each override, then the legacy implementation can keep using vanilla cobra and we would have zero risk of changes to the modern api breaking the legacy api and vice versa

What should --help display and when:
Another angle to consider is the command line help to display. Currently the kong based go-sqlcmd implementation produces several console pages of help with the standard sqlcmd -?.

A modern cobra CLI would usually produce a single level description for the top level subcommands and describe only the global flags with a --help (with no other args provided). Also, a modern cobra CLI would normally produce the same help if no sub-command is entered, however, the older ODBC based sqlcmd.exe makes a Trusted connection (-E) to the localhost (-S .) if no command line options are provided, we need to solve for these two approaches to providing no command options/sub-commands.

We could consider a different behavior if a environment variable is present e.g. if SQLCMD_BACKCOMPAT_MODE=TRUE we could behave in an old way, and behave in the standard modern way if env var is not present

The purpose of this discussion is to write down a plan on how we are going to make this all work together and implement it in a consistent manner.

[shueybubbles]

Thx Stuart for writing it down! I apologize this post is using English and not shell code examples but I am in a bit of a hurry and playing catch up from the long weekend.

Back compat

User experience

How sqlcmd behaves without being passed an explicit command like query could depend on prior user behavior. If the user has no existing config file with a stored connection, we can assume they want the old compatibility mode. If they've already set up a config file (evidence they have used the modern CLI already) then we can use the new query command as the default command.

We can allow the user to force a default behavior with an environment variable, possibly combined with the presence of the config file to control the help behavior. EG if the user just runs sqlcmd with no arguments it would have several possible behaviors:

• With no config file present and no env var set, assume they want legacy mode and connect to localhost
• With a config file present and no env var set, assume modern mode and connect to the default connection in the config file for query command
• With no config file and an env var set to force modern mode (SQLCMD_BACKCOMPAT_MODE=FALSE), print the help with the command list.
• With a config file present and an env var set to force modern mode, connect to the default connection in the config file for query verb.
• With an env var set to force legacy mode (SQLCMD_BACKCOMPAT_MODE=TRUE), connect to localhost regardless of the presence of the config file.

Implementation

I like the idea of keeping the legacy interface completely separate from the modern CLI implementation. No inheritance of commands, no inheritance of flags from the root, etc. We can replace Kong with a vanilla Cobra implementation in cmd/sqlcmd. That way we isolate one implementation from side effects in the other. We will still need to be careful to architect pkg/sqlcmd changes in a way that preserves the legacy behaviours. Typically, we can achieve such compatibility by refactoring current behaviour into the implementation of an interface such as we defined for Formatter

Open issues

• The legacy sqlcmd doesn't know about all the possible connection parameters available today, and we plan to add new features to the driver like Always Encrypted. Is there any value to enabling access to those new driver features in legacy mode? It could make sense to at least add a string parameter with extra connection string values (settable via env var and command line) so legacy mode can take advantage of AE and TDS 8 for example.

Global flags and subcommands

This section leaves the legacy behaviours behind and focuses on the modern experience.

User experience

We prefer users rely on the stored configuration for the bulk of connection parameters. There are certain connection string parameters that are not desirable to expose as overrides, particularly database and server which are essentially the primary keys to the config dictionary. There are plenty of other parameters that are likely to be tuned based on the current operating environment, such as packet size, trust server certificate, user id, authentication, keep alive, and application intent. We can allow users to save those values in the config but they aren't meant to be set in stone.

The legacy sqlcmd required many of those transient parameters to have individual switches, which is very limiting for future compatibility. The driver will continuously evolve to support new parameters and we don't want to have to add new distinct flags for all of them.

Furthermore, modern sqlcmd is going to connect to more endpoint types than just a SQL TDS endpoint. In that model, server can mean many things. The set of transient parameters to tune the connection becomes endpoint-type specific.

I propose we eliminate the old flag-per-setting model and move to a generic name-value dictionary for connection overrides as a global parameter. It could be similar to how docker uses the -e flag for setting environment variables. It could also read the values from response files. We would have a minimal set of global flags that are applicable to most commands. Using some of these global flags would tell sqlcmd to bypass the use of the connection data in the config file altogether.

Potential global flags:

--server, -S - the name or address of the server/endpoint. When provided, no saved connection is used. The syntax of the server name is command-specific.
--database, -D (d?) - the name of the database or store instance on the server. We could alias this with --store to be more generic. Again, the syntax of the name is command-specific. If a particular subcommand doesn't support a store name it can either ignore it or throw an error.
--port, -p - the port number. Again if a particular subcommand doesn't need it, it can ignore it or throw an error.
--save - Saves the final combination of connection parameters as an entry in the config file upon successful connection and sets it as the default.

Note that we can still allow SQL-specific commands like query and bulk copy to implement some identical sets of flags for connection overrides like --user. Such flags may be helpful for people who only use sqlcmd for basic SQL Server operations and don't want to deal with the generic name-value pair global flags. Such flags can be declared in an internal package shared by the two command implementations.

Open issues

• If different endpoint types support different parameters, do we need to validate them on input and inform the user of what they can set? The go-mssqldb driver doesn't have a way to enumerate valid properties and it allows any arbitrary value to be set. It just ignores ones it doesn't care about, unlike the .Net or ODBC driver.