For instance when moving around between an existing Plex set of databases I would find it useful to know which db file I have open, as seen in .show Is there any string variable or command system available that allows this sort of information to be shown? eg desired output

sqlite myTest.db> -->

But without my having to go and set the command by hand with .prompt "sqlite myTest.db > " "-->" I would see that as a rather Stone Age solution.

Are there other useful possibilities? I did not find one that I would find useful on web so far!

Right now the prompt is just a literal string. However, it is an interesting suggestion to add escapes to the prompt, as is done for PS1 for the unix shells.

Supposing I did enhance the CLI to interpret and expand special character patterns in the prompt string, what should those character patterns be?

Initial thoughts would include:

• database name/file (indexed by seq, say %Dn{0}, %Df{0}?)
• read/write status of database (also indexed... %Dr{0})
• strftime() substitutions (%t{%Y-%M-%D})
• transaction / autocommit status (%T)
• savepoint depth (%S)
• list of savepoint names (indexed by (reverse?) depth, say %Sn{0}?)
• defensive mode?
• foreign_keys status? (%F)
• journal mode (%J)
• sync mode?
• output of any other pragma (perhaps some kind of %P{pragma_name}?)

Or, perhaps just define a .prompt_sql line and evaluate that for the prompt output.

And perhaps at the top of the list:

• total attached database count (%D)

Since the most likely usage is something in a .sqliterc or script file, just specify a printf() call where the arguments can be 'functions' and or constant symbols. Fewer arguments about what single characters are most deserving of your favorite interpretation.

Well we have the .system and .show facilities...

Moderation reminder: please keep in mind that every time a post is edited, all subscribers get a notification. Making a series of many edits in a row is a surefire way to annoy many hundreds of users and to possibly prompt some to flag mails from this forum as spam, which can accumulate and cause mail providers to stop delivering them.

This is not a request to never edit a post, but is a request that edits be made thoughtfully and in moderation. The "preview" button will show the post without saving it (or notifying anyone), so there's never a true need to make 5+ edits in less than 10 minutes.

Apologies all, I just kept thinking of one more thing to try - oblivious to the email storm. Maybe I half expected only OP gets emails by default unless opt-ins are taken - mostly I just was not thinking about that at all!

Prototype code for prompt-escapes is now on the cli-prompt-redo. It is far from complete, but it does work. As of the latest check-in you can, for example, run:

.prompt '\e[1;\x31\:34\;m\f>\e[0m '

And then your prompt will be the current database filename, colored blue normally, or red if you are inside of a transaction.

The only documentation about the recognized backslash-escapes (at the moment) is comments in the code. You can make improvement suggestions here, if you like.

The new prompt logic is now on trunk. (And trunk was subsequently loaded into Fossil so that the "fossil sql" command also uses the new prompt logic.) Features:

• The escape character is changed from "\" to "/"
• The CLI uses environment variables SQLITE_PS1 (main prompt) and SQLITE_PS2 (continuation prompt) if they exist. If not, reasonable defaults are used. The ".prompt" command always overrides both of those.
• You can restore start-up prompts by using ".prompt --reset"
• Run ".prompt --show" to see the current prompt strings.

Currently supported substitutions:

• // → /
• //nnn → where nnn is octal, a byte with the value nnn.
• /e → Escape. U+001b. Shorthand for /033.
• /f → The "tail" of the current database file
• /F → Full pathname of the current database file
• /~ → Like /F except substitute ~ for the home directory
• /H → In a continuation prompt, the characters needed to complete the statement
• /B → In a continuation prompt, expand to zero or more space so that the total continuation prompt is at least as long as the main prompt.
• /x → Show subsequent text only if within a transaction
• /: → Toggle text suppression from /x or similar
• /; → Disable text suppression from /x or similar

Example: Use the "~"-abbreviated pathname, making the color change from blue to red when inside of a BEGIN...COMMIT transaction:

• /e[1;32mSQLite /e[1;3/x1/:4/;m/~/e[0m>/040

Example: As before, except don't use ANSI escape codes (as they do not play well with linenoise) and instead add "!!" to the end of the prompt when inside a transaction:

• SQLite/040/~>/x!!/;/040