SQLite Forum

CLI shell history

CLI shell history

(1) By anonymous on 2020-11-29 15:36:06 [link]

When using the CLI is there a way to clear previous commands. Sometimes I get dozens of queries in the history while doing a particular task and it is helpful to be able to clear that history.

(2) By anonymous on 2020-11-29 17:24:22 in reply to 1 [link]

These work for me with the CLI running on Windows.

1. Clear the CLI screen

.shell cls

2. Clear the CLI history (i.e. history = previous commands, recallable using Up Arrow)

Press ALt+F7

(4) By Trudge on 2020-11-30 17:24:07 in reply to 2 [link]

Thank you for the suggestion. I'm on a Mac so neither of those worked. I can and have done

    .shell clear

which clears the screen (assuming bash is your shell), but not the history.

(3) By Warren Young (wyoung) on 2020-11-29 17:24:28 in reply to 1 [link]

Removing `~/.sqlite_history` should do it.

(5) By Trudge on 2020-11-30 17:28:53 in reply to 3 [link]

Ah, yes that did it. Some caveats: You have to quit the shell (in my case Terminal on a Mac). Delete the file. Then open a new shell and start sqlite3 CLI again. 

Otherwise .sqliite_history does get deleted, but reappears.

Is there a way just to 'clear' the current history session without deleting the file?

(6) By Larry Brasfield (LarryBrasfield) on 2020-11-30 17:54:07 in reply to 5 [link]

The SQLite CLI shell provides no way to clear the history. It keeps the history internally (in process memory), so nothing you do to its persistent form will affect that for the readline library written as it is today.

You may want to consider using one of the many excellent GUI tools for manipulating and querying SQLite databases. Maintaining a set of save queries is an extremely common feature in such tools.  The CLI shell is not really meant to be a deluxe tool. It is more of a bare-metal interface to the SQLite library, (although less so than in years past.)

(7.1) By Warren Young (wyoung) on 2020-11-30 18:40:24 edited from 7.0 in reply to 5

> Otherwise .sqliite_history does get deleted, but reappears.

Of course: the history is read into RAM on program initialization, then written from RAM on exit. Removing the history file while the program runs just means that *another* instance started in parallel will start with an empty history. Exiting the first instance rewrites the history from RAM.

> Is there a way just to 'clear' the current history session without deleting the file?

After the "cookie" line (`_HiStOrY_V2_`) each line is a typed line with whitespace and other special characters replaced with octal escape sequences. See `history_save()` in [`history.c`][1] from [Apple's version of libedit][2].

Other versions of libedit ([example][3]) do without the cookie line entirely, and they don't do any special character escaping, that I can see.

Then there's GNU readline, which is yet another ball of wax.

They're all lumped together because they implement the same API, and the SQLite shell can be built against any of them. The stock macOS version of `sqlite3` just happens to be built against Apple's fork of `libedit`, so that's the history file flavor you get with that build of the SQLite shell.

**EDIT:** All of this means that given the file format details, you can open that file in a text editor and delete the bits of history you don't want to reappear in a later call to `sqlite3`, so long as your saved history file continues to follow the rules for the particular line editor library that wrote the file.

[1]: https://opensource.apple.com/source/libedit/libedit-55/src/history.c.auto.html
[2]: https://opensource.apple.com/source/libedit/libedit-55/
[3]: https://github.com/troglobit/editline

(8) By Trudge on 2020-11-30 19:19:38 in reply to 7.1 [link]

What a good explanation. Thank you. I will use the '.shell clear' for now. I've used a GUI (DB Browser) occasionally but had some issues with UTF-8. Most of what I do is from a Perl script to manage music and book libraries, but having direct access to the DB is essential for debugging.

Many thanks to all who offered advice and suggestions.