Could sqlite check at least $HOME/.config/sqlite3/sqliterc even without the environment variable being set, like other software does?

FWIW, we don't claim to support that spec. Rather, we apply it if it's specifically requested and do not if it's not. On systems which do not give any indication that they support or desire XDG semantics, we don't apply them (and should not do so, IMO).

True, the man page doesn’t actually claim to follow the spec. That said, looking for an environment variable called XDG_CONFIG_HOME but not following the spec that defines said variable seems like an odd choice. Every other program I’ve encountered that understands the XDG_CONFIG_HOME variable follows the spec, at least to some degree.

That said, looking for an environment variable called XDG_CONFIG_HOME but not following the spec that defines said variable seems like an odd choice.

Similarly (IMO), assuming a spec when the environment does not indicate any desire to seems odd.

The XDG dir feature was added at the request of users and needed to be done in a way which did not impact users who do not explicitly indicate a desire for it. Therefore, we assume that if there's no environment vars suggesting we should use it, we don't. That the XDG spec expects otherwise is more than a little bit curious.

That said: that's all my personal opinion, not a flat-out rejection of your request. i will discuss this with the team and extend that feature if that's the team's consensus.

needed to be done in a way which did not impact users who do not explicitly indicate a desire for it.

I’m afraid I don’t understand what negative impact this would have. As far as I know SQLite never writes its config file, only reads it, so intentionally creating the file ~/.config/sqlite3/sqliterc, in the absence of the environment variable, is explicitly indicating a desire, isn’t it? Or is there a situation in which a user creates a file by that name, but expects SQLite not to use it?

I will consider two options:

1. Keep things as they are now.

2. We revert SQLite to its original design in which XDG_CONFIG_HOME is utterly ignored and the only configuration file considered ~/.sqlite

Which of these do you prefer?

Our common enemy here is complexity. Why do you want to make an already complex algorithm even more complicated? What problem does that solve? Note that I do not consider "all the other kids are doing it differently" to be a problem.

In my opinion, the benefits of the XDG spec in general are:

• Config files are kept tidy and organized. This bullet point is subjective, so feel free to ignore it if you disagree that XDG is tidy.
• Config files for all programs are kept in one place, which means if you want to share your configuration across machines, you can turn that one place into an SCM repo.
• Config files are kept separate from files of other types (especially user-specific persistent temporary and cache files), making it easy to set up backup jobs, disk snapshots, etc. which back up config files without wasting space also backing up temp/cache files. SQLite AFAIK doesn’t have temp/cache files that live in the home directory, but many programs do, and those programs historically (pre-XDG) used to put them in ~/.appname; XDG means one doesn’t have to investigate every program separately to break apart these different file types.

Separately, in my opinion, a good objective is to avoid surprising users. SQLite’s behaviour surprised me. To me, because it’s the XDG spec that defines the meaning of the variable, ${XDG_CONFIG_HOME} (in documentation) is essentially a short form for “${XDG_CONFIG_HOME}, or ${HOME}/.config if the former is unset or empty”. I obviously haven’t done a survey of other Linux users to check whether they also think that way, but that is my interpretation, and therefore it was surprising to me.

That said, you’ve rejected that as an option, so I guess there’s no point discussing it further. Of the two options you offered, I assume in the past some users who are setting the environment variable asked for it to be respected, so I would prefer not to harm them by taking their choice away, even if I don’t get to benefit from it.

Could some text be added to the man page explaining that ${XDG_CONFIG_HOME}/sqlite3/sqliterc is only considered if the XDG_CONFIG_HOME variable is actually set? Something like this, but obviously the final wording is up to you:

o If the file ${XDG_CONFIG_HOME}/sqlite3/sqliterc (only if ${XDG_CONFIG_HOME} is set explicitly, its default value is not considered) or ˜/.sqliterc exists, the first of those to be found is processed during startup.  It should generally only contain meta-commands.

There needs to be a more definitive marker that the user/OS has adopted XDG conventions than some environment variables which may or may not be present. If such a marker exists, I'd argue for a compile-time option that selects one pathway or the other.

In my opinion, the very existence of $HOME/.config/sqlite3/sqlite is a clear and unambiguous signal that the user does in fact expect sqlite3 to read that file. So I think it can do no harm to try reading that file first – and if it does not exist, try the old style dot file instead.

I'm deeply sorry that I ever created the ~/.sqliterc file. I now understand that the .sqliterc file is a misfeature, a mistake, but one that due to backwards compatibility constraints I am now burdened to support for the next 26 years. It would have been so much better, and simpler, for the CLI to just start running and not try to read any kind of "user preferences" file such as "~/.sqliterc".

The user-preferences file is rarely used. And in cases where it is wanted by a sophisticated user, the same effect could be achieved by creating a shell alias or script substitute for the "sqlite3" command that added the "--init FILENAME" command-line option. The ~/.sqliterc file is not necessary. It is just an extra complication that is likely to cause confusion, and that leads to complaints about "standards", such as we see in this thread, all without adding any new capabilities.

Maybe Remove All ~/.sqliterc Support And Take The Compatibility Hit?

I'm sitting here wondering how much disruption would be caused by just dropping all support for ~/.sqliterc, ~/.config/sqlite/sqliterc, and XDG_CONFIG_HOME in the next release. Would that really cause significant trouble? Surely the 10 or 12 people who actually use this feature would not be unduly burdened to create a new alias for the "sqlite3" command that adds the "--init ~/.sqliterc" or "--init ~/.config/sqlite/sqliterc" option, would they? Would that really break anybody's scripts? Seems like if there are scripts that already depend on specific settings in the ~/.sqliterc file, those scripts are already pretty fragile, and forcing those scripts explicitly set up their environment and not depend on a external "configuration" file would go a long way toward improving portability of those scripts and preventing future bugs. Am I wrong?

As I type the previous paragraph, I have about talked myself into removing all support for ~/.sqliterc and/or ~/.config/sqlite/sqliterc.

Maybe for a year or two, ~/.sqliterc should still be supported, but if the ~/.sqliterc file is found, SQLite should print a warning messages something like this:

Automatic reading of the ~/.sqliterc user-preferences file is deprecated. Future versions of SQLite may omit reading of the ~/.sqliterc file. If you want to continue using the ~/.sqliterc file, or if you just want this warning message to stop, create an alias for the "sqlite3" command that adds the "--init ~/.sqliterc" option.

To me at least, that sounds like a perfectly fine resolution. I already have my init file (that I use most of the time) in a nonstandard location, with a shell alias to invoke sqlite3 with it. (It only changes the prompt, sets box mode and a nullvalue, and runs pragma foreign_keys=1.)

I imagine there may be howls of ourtrage, though. But not from me.

Edited to add: Surely, the compatibillity promsse applies to the sqlite library, not (or not as much) to the shell?

Surely, the compatibility promise applies to the sqlite library, not (or not as much) to the shell?

Yes, that is true.

I would argue those who use the shell in their own scripts are unlikely to depend upon initialization files in some default place(s), eliminating compatibility issues for them. Also, those who use the shell enough to have some preferred setup for it in a ~/sqliterc file will have little trouble moving it or creating an alias.

Since sqlite3 already ignores lines starting with a # character, you can avoid extra shell invocations by creating a script executable looking something like this (adjust for local conditions):

#! /usr/local/bin/sqlite3 --init
.mode table --quote

In my opinion, an RC file read automatically by the program itself on startup gives a better user experience than any of the alternatives. I’ll give a few examples.

I’ll assume that the options for user customization, in the absence of an RC file, are bash aliases (alias sqlite3=sqlite3 -init ~/something) or a wrapper executable named sqlite3 that appears earlier in PATH and executes the real sqlite3 with a similar -init option. If there’s some other way I’m not aware of, some of these examples may not apply.

My team has a Python script that creates an SQLite database in a temporary file, then invokes the CLI on it so the user can interactively explore the data. This is a human-facing, interactive usage of the CLI (not a scripted usage), so the user’s personal preferences (.mode, .headers, …) should apply. However, because the sqlite3 binary is being invoked from Python, not bash, a bash alias won’t work. SQLite’s existing behaviour works fine here, as does a wrapper executable.

A wrapper executable would need to find the real sqlite3 binary to execute. The simple way would be to write exec /usr/bin/sqlite3 -init ~/something "$@". The simple way is wrong: if I run my wrapper on a machine where the CLI is at /usr/local/bin/sqlite3, it fails; if I run it on a machine with an older distro-packaged CLI at /usr/bin/sqlite3 and a newer admin-installed version at /usr/local/bin/sqlite3, it fails silently (it runs the wrong version). The correct way would be to have the wrapper search PATH for the real executable. However AFAIK there’s no reasonable way to search “the rest of PATH after the entry that was used to execute myself”—consider that the wrapper may or may not even be on PATH, there could be symlinks involved, and PATH could even contain non-contiguous duplicate entries (e.g. PATH=~/bin:/usr/local/bin:~/bin:/usr/bin is legal, if odd). I’m not sure if it’s even possible to write a wrapper executable that reliably, in all possible cases, finds the next program of the same name on PATH. SQLite’s existing behaviour doesn’t have to deal with this. A bash alias also doesn’t have this problem because bash aliases aren’t recursively expanded.

Sometimes I might want to test different versions of SQLite side by side. To do this, I would probably grab the release archive of the version I want to test, compile it without installing it, and run with ./sqlite3 from the build directory. I would want my personal preferences to apply, since I’m using the CLI interactively as a human. Neither a bash alias nor a wrapper script work, because ./sqlite3 skips both alias expansion and PATH search. Only SQLite’s existing behaviour works here.

''In my opinion, an RC file read automatically by the program itself on startup gives a better user experience than any of the alternatives.''

Another way is to have the sqlite3 CLI check for an environment variable, e.g. SQLITE_INIT_FILE. On Windows it could use a registry entry. Other OSes have other possibilities.

The contents of my .sqlite3rc is a single ".mode csv" command since I import and export a lot of .csv files.

Another way is to have the sqlite3 CLI check for an environment variable, e.g. SQLITE_INIT_FILE.

I am a big fan of this option.

On Windows it could use a registry entry.

I would prefer an environment variable as well for Windows. Playing with registry is too Windowsish. :-) There are plenty of environment variables on Windows and they work fine.

Just thinking out-loud. :-)

Our common enemy here is complexity.

This is a perspective that I would like to see more widespread. However, the burden of complexity is much mitigated by adherence to widely followed conventions. Moving toward that, where the complexity is only in how some defaults are derived, seems like an improvement to me.

If I may go off on a tangent for a little while, I now consider compiling sqlite3 without readline support and running it under rlwrap (available in every fine software package manager) instead. It seems to work well. Another win for simplicity and separation of concerns!

Edit much later: That will lose me some handy tab completions. I'll try it anyway and see how much that bothers me.

I notice that a fix for this has landed on trunk without fanfare.

Yes, but I'm still grumbling under my breath. :-)