User configuration file for sqlline

[stoty]

Use case:
Being able to override the defaults for sqlline parameters globally

i.e, If I want sqlline to behave like mysql, and not truncate records, I want to have a global way of setting
maxwidth=100000
for all connections, which gets applied anytime I use sqlline.

I think that with SQLLINE-386 and #389 sqlline is actually very close to being able to do that.

One way to implement it would be

• Define a default canonical connection config file location that is always read if present, unless overridden. (i.e $HOME/.sqlline/configuration)
• Define a 'global:' or similar section in the connection config file, and use the properties defined there to to override the sqlline default settings (the connection-specific settings would be applied on top if it)

[julianhyde]

To clarify: when you say 'override the defaults for sqlline parameters globally', you mean set the defaults globally. People could still override this setting, yes?

What other systems are you aware of that do this well, and that we should be emulating? For example, ssh has /etc/ssh/ssh_config (shared among all users) and ~/.ssh/config (per-user config file).

[joshelser]

Yep, ssh is a great example.

The use-case we had here is that SQLLINE-66 caused some grief down in Apache Phoenix. Phoenix does an (incorrect per the JDBC spec) thing around DATE/TIMESTAMP which has caused Phoenix users to expect time components on date columns. SQLLINE-66 had (per JDBC) correctly formatted DATE columns as just "yyyy-MM-dd" whereas Phoenix would have previously returned "yyyy-MM-dd HH:mm:ss".

Now, they have a workaround via

!set dateFormat "yyyy-MM-dd HH:mm:ss"
!set timeFormat "yyyy-MM-dd HH:mm:ss"

However, the reporter (to Istvan and I) of this issue can't get all of their users to change their code to set this first. Thus, if we had a way that could "default" any sqlline session to use these configurations, that would go a long way.

[stoty]

To clarify: when you say 'override the defaults for sqlline parameters globally', you mean set the defaults globally. People could still override this setting, yes?

Yes, I meant "override the built-in defaults", all existing methods to set the parameters should continue to work.

[stoty]

What other systems are you aware of that do this well, and that we should be emulating? For example, ssh has /etc/ssh/ssh_config (shared among all users) and ~/.ssh/config (per-user config file).

I was thinking of the way mysql handles my.cnf on Unix, but that's pretty much the same the logic that ssh has.

[snuyanzin]

I think in this case there are options how !set and !reset could work.

1. no current connection => !set, !reset will apply globally
2. there is a current connection => !set, !reset could be applied either on current connection level or globally

similar thoughts about !save command

[richardantal]

I've created a PR for this issue: #437
Please review it.

[richardantal]

This change uses the ConnectionConfigParser to load properties created by #386
If connectionConfig path is defined it will use that configuration file, otherwise it will use the DEFAULT_CONNECTION_CONFIG_FILE (~/.sqlline/configuration)
It loads properties from the global-conf: section and sets those.

Simple example for the configuration file:

global-conf:
  maxwidth: 100000
  dateFormat: yyyy-MM-dd HH:mm:ss

You can still change the dateFormat later on via the !set dateFormat "yyyy-MM-dd" command.

If a property is defined in both sqlline.properties and configuration files, value in configuration file will be used.

[snuyanzin]

merged at 27bab8f
thanks @richardantal