larkery / zsh-histdb

A slightly better history for zsh

ZSH History Database

News

13/10/21

Thanks to Aloxaf some subshell invocations have been removed which should make things quicker. Thanks to m42e (again) histdb-sync uses the remote database IDs as the canonical ones which should make syncing a bit less thrashy. Thanks to Chad Transtrum we use builtin which rather than which, for systems which have an unusual which (?!), and an improvement to examples below in the README. Thanks to Klaus Ethgen the invocation of sqlite3 is now unaffected by some potential confusions in your sqlite rc files.

30/06/20

Thanks to rolandwalker, add-zsh-hook is used so histdb is a better citizen. Thanks to GreenArchon and phiresky the sqlite helper process is terminated on exit better, and the WAL is truncated before doing histdb sync. This should make things behave a bit better. Thanks to gabreal (and others, I think), some things have been changed to declare -ga which helps when using antigen or somesuch? Thanks to sheperdjerred and fuero there is now a file which might make antigen and oh-my-zsh work.

There is a breaking change, which is that you no longer need to add-zsh-hook precmd histdb-update-outcome in your rc file. This now happens when you source sqlite-history.zsh.

11/03/20

Thanks to phiresky (https://github.com/phiresky) history appends within a shell session are performed through a single long-running sqlite process rather than by starting a new process per history append. This reduces contention between shells that are trying to write, as sqlite always fsyncs on exit.

29/05/19

Thanks to Matthias Bilger (https://github.com/m42e/) a bug has been removed which would have broken the database if the vacuum command were used. Turns out, you can’t use rowid as a foreign key unless you’ve given it a name. As a side-effect your database will need updating, in a non-backwards compatible way, so you’ll need to update on all your installations at once if you share a history file. Also, it’s not impossible that this change will make a problem for someone somewhere, so be careful with this update.

Also thanks to Matthias, the exit status of long-running commands is handled better.

05/04/18

I’ve done a bit of work to make a replacement reverse-isearch function, which is in a usable state now.

If you want to use it, see the Reverse isearch section below which now covers it.

09/09/17

If you have already installed and you want to get the right timings in the database, see the installation section again. Fix to issue #18.

What is this

This is a small bit of zsh code that stores your history into a sqlite3 database. It improves on the normal history by storing, for each history command:

• The start and stop times of the command
• The working directory where the command was run
• The hostname of the machine
• A unique per-host session ID, so history from several sessions is not confused
• The exit status of the command

It is also possible to merge multiple history databases together without conflict, so long as all your machines have different hostnames.

Installation

You will need sqlite3 and the usual coreutils commands installed on your PATH. To load and activate history recording you need to source sqlite-history.zsh from your shell in your zsh startup files.

Example for installing in $HOME/.oh-my-zsh/custom/plugins/zsh-histdb (note that oh-my-zsh is not required):

mkdir -p $HOME/.oh-my-zsh/custom/plugins/
git clone https://github.com/larkery/zsh-histdb $HOME/.oh-my-zsh/custom/plugins/zsh-histdb

Add this to your $HOME/.zshrc:

source $HOME/.oh-my-zsh/custom/plugins/zsh-histdb/sqlite-history.zsh
autoload -Uz add-zsh-hook

in your zsh startup files.

Note for OS X users

Add the following line before you source `sqlite-history.zsh`. See #31 for details.

HISTDB_TABULATE_CMD=(sed -e $'s/\x1f/\t/g')

Importing your old history

go-histdbimport and ts-histdbimport are useful tools for doing this! Note that the imported history will not include metadata such as the working directory or the exit status, since that is not stored in the normal history file format, so queries using --in DIR, etc. will not work as expected.

Configuration

histdb can be configured exactly as zsh:

• HISTORY_IGNORE: If set, is treated as a single glob pattern to match the commands that should be ignored. Ignored commands are not saved to the database. Example: (ls|cd|top|htop).

Querying history

You can query the history with the histdb command. With no arguments it will print one screenful of history on the current host.

With arguments, it will print history lines matching their concatenation.

For wildcards within a history line, you can use the % character, which is like the shell glob *, so histdb this%that will match any history line containing this followed by that with zero or more characters in-between.

To search on particular hosts, directories, sessions, or time periods, see the help with histdb --help.

You can also run histdb-top to see your most frequent commands, and histdb-top dir to show your favourite directory for running commands in, but these commands are really a bit useless.

Example:

$ histdb strace
time   ses  dir  cmd
17/03  438  ~    strace conkeror
22/03  522  ~    strace apropos cake
22/03  522  ~    strace -e trace=file s
22/03  522  ~    strace -e trace=file ls
22/03  522  ~    strace -e trace=file cat temp/people.vcf
22/03  522  ~    strace -e trace=file cat temp/gammu.log
22/03  522  ~    run-help strace
24/03  547  ~    man strace

These are all the history entries involving strace in my history. If there was more than one screenful, I would need to say --limit 1000 or some other large number. The command does not warn you if you haven’t seen all the results. The ses column contains a unique session number, so all the 522 rows are from the same shell session.

To see all hosts, add --host after the query terms. To see a specific host, add --host hostname. To see all of a specific session say e.g. -s 522 --limit 10000.

Integration with zsh-autosuggestions

If you use zsh-autosuggestions you can configure it to search the history database instead of the zsh history file thus:

_zsh_autosuggest_strategy_histdb_top_here() {
    local query="select commands.argv from
history left join commands on history.command_id = commands.rowid
left join places on history.place_id = places.rowid
where places.dir LIKE '$(sql_escape $PWD)%'
and commands.argv LIKE '$(sql_escape $1)%'
group by commands.argv order by count(*) desc limit 1"
    suggestion=$(_histdb_query "$query")
}

ZSH_AUTOSUGGEST_STRATEGY=histdb_top_here

This query will find the most frequently issued command that is issued in the current directory or any subdirectory. You can get other behaviours by changing the query, for example

_zsh_autosuggest_strategy_histdb_top() {
    local query="
        select commands.argv from history
        left join commands on history.command_id = commands.rowid
        left join places on history.place_id = places.rowid
        where commands.argv LIKE '$(sql_escape $1)%'
        group by commands.argv, places.dir
        order by places.dir != '$(sql_escape $PWD)', count(*) desc
        limit 1
    "
    suggestion=$(_histdb_query "$query")
}

ZSH_AUTOSUGGEST_STRATEGY=histdb_top

This will find the most frequently issued command issued exactly in this directory, or if there are no matches it will find the most frequently issued command in any directory. You could use other fields like the hostname to restrict to suggestions on this host, etc.

Reverse isearch

If you want a history-reverse-isearch type feature there is one defined in histdb-interactive.zsh. If you source that file you will get a new widget called _histdb-isearch which you can bind to a key, e.g.

source histdb-interactive.zsh
bindkey '^r' _histdb-isearch

This is like normal history-reverse-isearch except:

• The search will start with the buffer contents automatically
• The editing keys are all standard (because it does not really use the minibuffer).

  This means pressing C-a or C-e or similar will not exit the search like normal history-reverse-isearch

• The accept key (RET) does not cause the command to run immediately but instead lets you edit it

There are also a few extra keybindings:

• M-j will cd to the directory for the history entry you’re looking at. This means you can search for ./run-this-command and then M-j to go to the right directory before running.
• M-h will toggle limiting the search to the current host’s history.
• M-d will toggle limiting the search to the current directory and subdirectories’ histories

Database schema

The database lives by default in $HOME/.histdb/zsh-history.db. You can look in it easily by running _histdb_query, as this actually just fires up sqlite with the database.

For inspiration you can also use histdb with the -d argument and it will print the SQL it’s running.

Synchronising history

You should be able to synchronise the history using git; a 3-way merge driver is supplied in histdb-merge.

The 3-way merge will only work properly if all the computers on which you use the repository have different hostnames.

The histdb-sync function will initialize git in the histdb directory and configure the merge driver for you first time you run it. Subsequent times it will commit all changes, pull all changes, force a merge, and push all changes back again. The commit message is useless, so if you find that kind of thing upsetting you will need to fix it.

The reason for using histdb-sync instead of doing it by hand is that if you are running the git steps in your shell the history database will be changed each command, and so you will never be able to do a pull / merge.

Completion

None, and I’ve used the names with underscores to mean something else.

Pull requests / missing features

Happy to look at changes. I did at one point have a reverse-isearch thing in here for searching the database interactively, but it didn’t really make my life any better so I deleted it.

zsh-histdb's Issues

sqlite3 prevents Terminal from closing on macOS

Reproduction

1. open Terminal.app in macOS
2. close the terminal window you just opened
3. there's this popup:
   

Of course, I prefer running exit instead of clicking the close button through the GUI. However, this is an inconvenience because when I shutdown my computer while leaving a terminal window open, I get the popup which disturbs the shutdown. Further, my quit alias also doesn't work because of the popup.

Is there a way to label sqlite3 as a background process or something like that so the terminal can end the sqlite3 process on its own?

Can't make zsh-histdb to work?

I use antibody to install this plugin, after install it will source in zsh_plugins.sh

source /home/user/.cache/antibody/https-COLON--SLASH--SLASH-github.com-SLASH-larkery-SLASH-zsh-histdb/sqlite-history.zsh

then I add:

autoload -Uz add-zsh-hook
add-zsh-hook precmd histdb-update-outcome

to ~/.zshrc

after that when open new shell I got these errors that happen only one time, open another new shell wouldn't echo these erros.:

Error: database is locked
History database /home/user/.histdb/zsh-history.db is using an older schema () and will be updated to version 2.
There is no migration script from version  to 2.

and histdb strace gives no result.

What should I do to make this plugin work?

Why place_id and duration columns in history table are NULL?

When I queried for history table, I fonud place_id, duration columuns are sometimes NULL.

SELECT
  *
FROM
  history
WHERE
  exit_status IS NULL OR
  duration IS NULL
LIMIT
  5

session	command_id	place_id	exit_status	start_time	duration
1	11	2	NULL	1557661462	NULL
9	61	2	NULL	1557662265	NULL
12	89	12	NULL	1557665488	NULL
12	89	12	NULL	1557666301	NULL
10	104	12	NULL	1557666919	NULL

Do you have any idea why these columns are NULL?
This seems to be same issue #18.

Performance metrics

Hi! I really love how well put together this all looks - from the merging driver to even describing with clear code examples how to wire this up to zsh-autosuggestions, I must say, this looks very well made. But I'm very interested in what the performance of it is - e.g. what is the performance impact on running a command due to this zshaddhistory given X items of history, or what happens to zsh-autosuggestions with this SQLite DB vs the plain old .zsh_history

I would be happy to record some of these metrics on my computer and send a pull request, but I'm wondering if you think this is a good idea or not really important.

Anyway, thanks for making this!

Feature request: Custom field widths and wrapping of histdb output

It would be nice if histdb allowed the user to specify the maximum width of each of the fields in histdb's output, and then wrap long lines to fit in to those fields.

Currently, the "column" command through which histdb pipes its output to only wrap once the entire line exceeds the screen width, and the wrapping only happens where the line exceeds the screen width.

This causes really awkward displays of histdb output which contains some really long cwd and/or commands. Just one such long line with, say, a really long cwd, will cause the cmd field to be displayed far to the right with lots of wasted whitespace in between, making visually connecting most of the commands with the cwd's more difficult than it could be if field widths were limited to something narrower.

The other problem is that when the "column" command does wrap, it doesn't keep the wrapping inside the relevant field, but starts prints the excess starting with the left-most column of the next line. This behavior is helpful when one needs to copy the long line using a terminal that can't narrow the selection to just between certain columns on the screen, or where preserving of the original whitespace in the command matters. But it visually messes up the screen and makes it hard to read through lots of histdb output.

All these use cases could be accommodated if there was an option to set field widths to arbitrary values (with perhaps some way of setting a value of "unlimited" for each field -- which appears to be the current default behavior) and wrap properly within those field widths.

Respect cursorshape?

I have set cursorshape in my window manager to _ instead of square block, but this plugin restore it to square block and blinking.

How to set it back to _? and keep it static -- not blinking.

Do we really care a lot about duplicate commands in `histdb` call? Improves histdb perf by 100x

(semi-related to #2)

When you remove the group by command and add an index on time:

create index hist_time on history(start_time);

For example compare this (slow):

-- explain query plan
select session, dir, replace(commands.argv, '
', '\n') as argv, max(start_time) as max_start
from
  history
  left join commands on history.command_id = commands.id
  left join places on history.place_id = places.id
where not (commands.argv like 'histdb%') and places.host='phirearch'
group by history.command_id, history.place_id
order by max_start desc
limit 62

to this:

-- explain query plan
select session, dir, replace(commands.argv, '
', '\n') as argv, start_time
from
  history
  left join commands on history.command_id = commands.id
  left join places on history.place_id = places.id
where not (commands.argv like 'histdb%') and places.host='phirearch'
order by history.start_time desc
limit 62

For me, this reduces the duration of simple calls like histdb or histdb borg from 1100ms to 10ms.

It would even be possible to wrap this in another call to remove duplicates with the only caveat that then you may get somewhat less results than you requested with --limit

Track tty in histdb

Can we have tty tracking in histdb as well?

Sometimes I want to find the current tab I'm using based on the command. If I know what tab I'm in, I can send a bell to it, and easily find it.

wildcards search with % didn't work.

Hi

For wildcards within a history line, you can use the % character, which is like the shell glob *, so histdb this%that will match any history line containing this followed by that with zero or more characters in-between.

I tried both this%that and this*that with -d to debug.

Turns out that this%that didn't work.
And this one histdb this\*that works for me.

Feature request: A way to import pre-zsh-histdb history

I'd like to be able to import in to zsh-histdb's sqlite3 database all the shell history that existed before I started using zsh-histdb, so it can be searched along with the new history I generate after I start using zsh-histdb.

This is important to me as I have tens of thousands of commands in my old history that I'd like to be able to search with histdb rather than having to resort to the old "history 0 | grep" method.

I understand that the timing information and the pwd of the old history will not be available, which is alright, as it's not available to the old history command anyway, so I wouldn't be losing anything by using histdb instead, as long as the history can be imported and the order of the commands is preserved.

More informative commit message

Hi! I've been using histdb synced with a git repository for a few days now, and I've noticed that every commit message just has been 'history'. I think that should be improved, for example a count how many messages where added, or from which days new commands where added.

I am prepared to make a pull request to change this, but I think we should first discuss on how commit messages should look(if you agree that the current commit messages are not very helpful).

Unicode chars in histdb -d output.

I ran the histdb -d command and found there are some unicode chars in the call. History is not functioning. Below is the histdb -d call. I am using zplug to install the zsh-histdb project. I think it may be related to my prompt, which is Powerlevel9k with NerdFonts. I have included my Powerlevel9k config as well if that might help.

histdb -d
select strftime(case when datetime(max_start, 'unixepoch') > datetime('now', 'start of day') then '%H:%M' else '%d/%m' end, max_start, 'unixepoch', 'localtime') as time, session as ses, dir, argv as cmd from (select session, replace(places.dir, '/home/dschaper', '~') as dir, replace(commands.argv, '
', '
���') as argv, max(start_time) as max_start
from
  history
  left join commands on history.command_id = commands.rowid
  left join places on history.place_id = places.rowid
where not (commands.argv like 'histdb%') and places.host='europa'
group by history.command_id, history.place_id
order by max_start desc
limit 34) order by max_start asc

    POWERLEVEL9K_LEFT_PROMPT_ELEMENTS=(os_icon ssh virtualenv docker_machine context dir vcs)
    POWERLEVEL9K_RIGHT_PROMPT_ELEMENTS=(status root_indicator background_jobs load ram)
    POWERLEVEL9K_SHORTEN_STRATEGY=truncate_middle
    POWERLEVEL9K_DIR_SHOW_WRITABLE=true
    DEFAULT_USER=dschaper
    POWERLEVEL9K_MODE='nerdfont-complete'
    POWERLEVEL9K_PROMPT_ON_NEWLINE=true
    POWERLEVEL9K_RPROMPT_ON_NEWLINE=false
    POWERLEVEL9K_OS_ICON_BACKGROUND="white"
    POWERLEVEL9K_OS_ICON_FOREGROUND="blue"
    POWERLEVEL9K_MULTILINE_FIRST_PROMPT_PREFIX=""

`--in` option doesn't distinguish between subdir and partial path match

If I call histdb --in while in ~/dev/vim, it yields results from ~/dev/vim as well as ~/dev/vimrc. I assume the logic that is meant to handle the "current dir or below" logic is using some kind of simple substring matching, therefore seeing ~/dev/vimrc as including the substring ~/dev/vim and returning a match.

In fact, it seems that's exactly what's happening:

zsh-histdb/sqlite-history.zsh

Line 220 in 0a7a52a

dirwhere="${dirwhere}${dirwhere:+ or }places.dir like '$(sql_escape $dir)%'"

Feature request: disable path

Exclude commands from db?

how can I exclude some commands from saving into sqlite3 db?

Feature request: Ability to delete entries from database

I'd like to be able to delete entries from the database.

It's rare, but on occasion I have been known to do something really stupid like type my password on the command line, and then for security reasons need to delete it out of the history.

This is easy to do when history is stored in plain text, but not so easy when it's in a database (without using specialized tools and taking special care not to screw anything up while doing it).

It's be nice if histdb gave me an easy and safe way to do delete entries.

histdb tries to migrate when db is locked on shell open

When a shell is started while the histdb database is locked, the shell interprets that as the current schema version being "" so it thinks the schema is outdated:

Error: database is locked        
History database ~/.histdb/zsh-history.db is using an older schema () and will be updated to version 2.
There is no migration script from version  to 2.

histdb-sync commits to git repo in parent dir

If a parent directory of $hist_dir contains a git repo, the first time histdb-sync runs, it'll try to commit to that repo, since the "git rev-parse --is-inside-work-tree" test will return true even if there's no git repo in $hist_dir itself.

Perhaps a simple test for the non-existence of a .git directory in $hist_dir would be more appropriate.

histdb --in overwrites the current directory in the prompt with `~dir`

Hi,
Awesome utility, really love it. One small issue: the current directory in my prompt gets overwritten with ~dir if I use histdb --in without any arguments.

I use prezto and here's my current prompt: %F{223}%m%f %F{69}${%2~}%(!. %B%F{1}#%f%b.).

Do you have any suggestions?

Performance issue?

I try to figure out what is consume time at startup of my z-shell configs, histdb took 1st place.

for i in $(seq 1 10); do time zsh -i -c exit; done

num  calls                time                       self            name
-----------------------------------------------------------------------------------
 1)    1          13.32    13.32   34.12%     13.32    13.32   34.12%  _histdb_start_sqlite_pipe

How to speed up histdb?

HISTDB_FILE seemingly not set

fresh install install using the suggested oh-my-zsh plugin dir and after adding commands to ~/.zshrc:

Error: incomplete SQL: PRAGMA user_version = 2
error in
History database /home/max/.histdb/zsh-history.db is using an older schema (0) and will be updated to version 2.
Backing up database to /home/max/.histdb/zsh-history.db-1563991992.bak before migration
cp: missing destination file operand after '/home/max/.histdb/zsh-history.db-1563991992.bak'
Try 'cp --help' for more information.
Update successful (you may want to remove the backup)

it looks like HISTDB_FILE isn't being set correctly - but i'm not sure.

there's also no backup present, so that flow looks incorrect.

Referencing rowid is illegal and can cause data loss

It is not possible to use rowid as a foreign key, because the rowid may arbitrarily change on VACUUM:

If the rowid is not aliased by INTEGER PRIMARY KEY then it is not persistent and might change. In particular the VACUUM command will change rowids for tables that do not declare an INTEGER PRIMARY KEY. Therefore, applications should not normally access the rowid directly, but instead use an INTEGER PRIMARY KEY. https://www.sqlite.org/rowidtable.html

In fact, the history insert query fails with Result: foreign key mismatch - "history" referencing "places" when run with PRAGMA foreign_keys = ON;. No idea why foreign key checks are not on by default.

You can verify by running PRAGMA foreign_key_check;

To solve this, a int primary key needs to be defined on each table which you can then use for the foreign key references.

Possible to configure HISTDB_FILE ?

Would like to move it like this:
export HISTDB_FILE="~/.local/zsh-histdb/zsh-history.db"

histdb-sync fails with: "fatal: No remote repository specified"

When I run histdb-sync, it fails with:

fatal: No remote repository specified. Please, specify either a URL or a
remote name from which new revisions should be fetched.

I think this error comes from the "git pull --no-edit" command in histdb-sync, but I'm not sure why it's happening. The full log of my histdb-sync run is:

Initialized empty Git repository in /home/me/.zsh-private/histdb/.git/
[master (root-commit) 2d84036] history
2 files changed, 1 insertion(+)
create mode 100644 .gitattributes
create mode 100644 histdb.sqlite3
fatal: No remote repository specified. Please, specify either a URL or a
remote name from which new revisions should be fetched.

histdb-sync fails to append to .gitattributes if NO_CLOBBER is set

When the "NO_CLOBBER" zsh option is set, the first time histdb-sync runs, it will fail to append to the .gitattributes file, because that file does not yet exist and the "NO_CLOBBER" zsh option prevents appending to a non-existent file.

The solution would probably be to test for the existence of .gitattributes first, then use > if it doesn't exist, and >> if it does.

Fail to run after sourcing the script

I received this error message on every command:

zshaddhistory:4: failed to compile regex: empty (sub)expression

Database locked when using tmux's `synchronize-panes`

Hi,
Recently I discovered tmux's synchronize-panes command and when I turn it on and I execute a command I get a lock error in all the other panes:

Error: database is locked
error in insert into commands (argv) values (...);
insert into places   (host, dir) values (..., ...);
insert into history
  (session, command_id, place_id, exit_status, start_time, duration)
select
  381,
  commands.rowid,
  places.rowid,
  0,
  1518591321,
  1518591321 - 1518591321
from
  commands, places
where
  commands.argv = ... and
  places.host = ... and
  places.dir = ...
;

I know this is an edge case that shouldn't be handled by this project but is there a way to turn off histdb in a session?

zsh: histdb-update-outcome: function definition file not found

os: ubuntu 18.04
zsh 5.1.1 (x86_64-ubuntu-linux-gnu), 5.4.2
zsh-histdb git sha: 882e7b6debaea5293357df888b98700e1f49f6a0
oh-my-zsh git sha: 77ad69e

First, I have been using histdb for about a year and I LOVE it. I started noticing issues when provisioning new systems.

I get zsh: histdb-update-outcome: function definition file not found on execution of the line add-zsh-hook precmd histdb-update-outcome. I have another system which has been working fine for months, but now whenever I set up a new system, I get this error on every prompt. I even tried grafting my working system's .oh-my-zsh to a new system, to no avail.

Also, strangely, on the new systems, histdb command cannot be found. This is even after running source $HOME/.oh-my-zsh/custom/plugins/zsh-histdb/sqlite-history.zsh. On the working system, type histdb yields

histdb is a shell function from /home/mike/.oh-my-zsh/custom/plugins/zsh-histdb/sqlite-history.zsh

as expected.

Multiline commands should appear with \n

Currently it shows the actual new line character

histdb:211: bad math expression: operator expected at `in select ...

Just started getting this error on fresh install of Ubuntu 18.04.

Linux 4.18.0-15-generic #16~18.04.1-Ubuntu SMP Thu Feb 7 14:06:04 UTC 2019 x86_64 x86_64 x86_64 GNU/Linux
zsh 5.4.2 (x86_64-ubuntu-linux-gnu)
sqlite3 3.22.0 2018-01-22 18:45:57 0c55d179733b46d8d0ba4d88e01a25e10677046ee3da1d5b1581e86726f2alt1

Currently at master 873610f. Typing histdb shows

Error: no such column: true
Error: no such column: true
error in -header -separator                          select strftime(case when datetime(max_start, 'unixepoch') > datetime('now', 'start of day') then '%H:%M' else '%d/%m' end, max_start, 'unixepoch', 'localtime') as time, session as ses, dir, argv as cmd from (select session, replace(places.dir, '/home/user', '~') as dir, replace(commands.argv, '
', '
') as argv, max(start_time) as max_start
from
  commands 
  join history on history.command_id = commands.id
  join places on history.place_id = places.id
where true and places.host='guibox'
group by history.command_id, history.place_id
order by max_start desc
limit 20) order by max_start asc
histdb:211: bad math expression: operator expected at `in select ...'

If I checkout 9598f2f and restart zsh, the error goes away.

zshaddhistory() should not use a hardcoded "$HOME/.history"

zshaddhistory() should use a hardcoded "$HOME/.history", as not everyone stores their history there.

Perhaps using the zsh $HISTFILE variable would be more appropriate.

When fsync is slow race condition can occur

I'm currently running a very disk intense task, which means that sync on my root disk takes around 2 - 3 seconds.

This means that the histdb insertion and update also takes that amount of time since a sqlite3 command always syncs to disk when it exits. I tried experimenting with options like pragma synchronous=off but I guess when an sqlite process exits it will force fsync regardless of those settings.

Every time I run a command in this state, the UPDATE query it will always fail after a few seconds with database is locked, because the INSERT has not yet completed.

I managed to solve this by making histdb run all sqlite commands that do not have output in the same sqlite3 connection and keeping it open: phiresky@6b836ea

This approach has two problems that might need to be fixed for this to be merged back:

1. Uses coproc which I've never seen anywhere. Should be replacable with just starting the process and redirecting stdin from a named pipe...
2. Can't exit shell with ctrl+d (zsh: you have running jobs.)

I guess this is a regression caused by #51.

Error: database is locked

While troubleshooting histdb today, I did a "histdb --limit 21255 |& less" in one tmux pane, and in another I did a "man column" and got this error:

Error: database is locked      
error in insert into commands (argv) values ('man column');
insert into places   (host, dir) values ('myhost', '/home/me');
insert into history              
  (session, command_id, place_id, exit_status, start_time, duration)
select     
  580,                                                                                                                                                                                                
  commands.rowid,
  places.rowid,
  0,                      
  1504294750,                                                                  
  1504294750 - 1504294750
from                                
  commands, places                                                                   
where
  commands.argv = 'man column' and
  places.host = 'myhost' and                                                 
  places.dir = '/home/me'
;                 

I'm guessing I got this error because histdb was running. But, since histdb was not actually writing to the database, I'm not sure why it should lock it -- especially if that will keep other shells from writing their history to the database.

Recover history to zsh_history

Hi, for some strange reason, my zsh_history disappears randomly. There is no disk errors, no corruptions on it, it simply goes away. It is important to say, this is absolutly not related with zsh-histdb. This problem occours even after a fresh install of Manjaro, and seems related with the size of zsh_history with oh_my_zsh.

But... the question here is:

Thanks to zsh-histdb, I have a fresh copy of it, but how to recover the history back to zsh_history file?

Thanks in advance.

migration backup is broken

No backup is created. I think the migration output is also wrong since my db size doubled from 17M to 34M

zsh-histdb/histdb-migrate

Line 13 in 0a7a52a

sqlite3 "${TARGET_FILE}" < "${MIGRATION_FILENAME}"

should probably be sqlite3 "${BACKUP_FILE}" < "${MIGRATION_FILENAME}" ? @m42e

Consider running sqlite inserts in background

(semi-relevant to #2)

I've just benchmarked a bit by adding time in front of the sqlite3 command in _histdb_query, and with a ~15MB history file, it takes ~50-100ms before a command is executed, and ~30ms for the time update.

Currently, these insert/updates are run synchronously, but it seems to me that (since the output of these commands is not used), it should work the same way to just run them in the background, which makes the shell noticeably more responsive:

diff --git a/sqlite-history.zsh b/sqlite-history.zsh
index 033199c..acbace7 100644
--- a/sqlite-history.zsh
+++ b/sqlite-history.zsh
@@ -60,7 +60,7 @@ histdb-update-outcome () {
     local finished=$(date +%s)
 
     _histdb_init
-    _histdb_query <<EOF
+    (_histdb_query <<EOF &
 update history set 
       exit_status = ${retval}, 
       duration = ${finished} - start_time
@@ -68,6 +68,7 @@ where id = (select max(id) from history) and
       session = ${HISTDB_SESSION} and
       exit_status is NULL;
 EOF
+    )
 }
 
 zshaddhistory () {
@@ -85,7 +86,7 @@ zshaddhistory () {
     _histdb_init
 
     if [[ "$cmd" != "''" ]]; then
-        _histdb_query \
+        (_histdb_query \
 "insert into commands (argv) values (${cmd});
 insert into places   (host, dir) values (${HISTDB_HOST}, ${pwd});
 insert into history
@@ -101,7 +102,7 @@ where
   commands.argv = ${cmd} and
   places.host = ${HISTDB_HOST} and
   places.dir = ${pwd}
-;"
+;" & )
     fi
     return 0
 }

The only problem I can see here is that it maybe could cause histdb-update-outcome to run before the insert in zshaddhistory?

Integration with fzf's CTRL+R and up

I would really like it it had the following features:

• Integration with fzf history search (CTRL+R)
• Ability to press "up" to go back in history from histdb and maybe only from this session.

Plugin manager support

I'd love to add zplug support and other plugin manager support to this. Is this something that PRs would be welcomed for?

Feature request: Search backward/forward

I'm using these ZSH widgets for history search:

bindkey "^[[5~" history-beginning-search-backward # page up
bindkey "^[[6~" history-beginning-search-forward # page down

I'd like to use zsh-histdb the same way.

stat: illegal option -- c on macOS

I've loaded the library via antibody. When I run any command, the output is followed by:

stat: illegal option -- c
usage: stat [-FlLnqrsx] [-f format] [-t timefmt] [file ...]
stat: illegal option -- c
usage: stat [-FlLnqrsx] [-f format] [-t timefmt] [file ...]
stat: illegal option -- c                                                                                                       
usage: stat [-FlLnqrsx] [-f format] [-t timefmt] [file ...]

https://stackoverflow.com/a/10666606/7127932 proposes some solutions. We could:

• require macOS users to install stat via homebrew. in this library, detect if the user is on macOS or not, and set the stat executable to either gstat or stat
• don't require macOS users to install stat via homebrew. in this library, detect if the user is on macOS or not, and call either stat -f or stat -c
• use perl 5, which I believe is installed by default on macOS

zsh-histdb/sqlite-history.zsh

Lines 48 to 68 in 8a7ae6f

	_histdb_start_sqlite_pipe () {
	local PIPE=$(mktemp -u)
	setopt local_options no_notify no_monitor
	mkfifo $PIPE
	sysopen -rw -o cloexec -u HISTDB_FD -- $PIPE
	command rm $PIPE
	HISTDB_INODE=$(stat -c %i ${HISTDB_FILE})
	sqlite3 -batch "${HISTDB_FILE}" <&$HISTDB_FD >/dev/null &|
	HISTDB_SQLITE_PID=$!
	}
	_histdb_query_batch () {
	local CUR_INODE=$(stat -c %i ${HISTDB_FILE})
	if [[ $CUR_INODE != $HISTDB_INODE ]]; then
	exec {HISTDB_FD}>&-;
	_histdb_start_sqlite_pipe
	fi
	cat >&$HISTDB_FD
	echo ';' >&$HISTDB_FD # make sure last command is executed
	}

Add a license file

Please add a license file describing the license for this code.

bad math expression on first installation

I am having some issues installing this. Note that I don't use o-m-z.

To reproduce:

1. git clone [email protected]:larkery/zsh-histdb.git ~/z
2. add

. ~/z/zsh-histdb/sqlite-history.zsh
autoload -Uz add-zsh-hook
add-zsh-hook precmd histdb-update-outcome

to my ~/.zshrc. this reproduces without anything else there
3. Open new shell

What I expect to see:
A functional history replacement

What I actually see:

-- Loading resources from /Users/eax/.sqliterc
/Users/eax/z/zsh-histdb/histdb-migrate:8: bad math expression: operator expected at `2'
-- Loading resources from /Users/eax/.sqliterc
Error: near line 8: misuse of aggregate function max()
BuyingEagle%

Doesn't work on MacOS?

Hi,

I give a try to your plugin but didn't get it working.

I have the following error after each command:

zshaddhistory:4: failed to compile regex: empty (sub)expression

and historydb returns

histdb:207: permission denied:

Do you have any clue to resolve this ?

Thanks

fatal: pathspec 'zsh-history.db' did not match any files

If histdb-sync is the first command that's run after sourcing sqlite-history.zsh then histdb-sync will error out with "fatal: pathspec 'zsh-history.db' did not match any files" because the $HISTDB_FILE file will only be created after the first command has been run.

histdb-sync should check to make sure that $HISTDB_FILE exists instead of assuming it does, and act accordingly (probably by creating that file if it doesn't already exist).

Duration and exit status is not recorded correctly

Duration/timing and exit status is offset one command in the past due to the ordering of precmd, preexec and zshhistoryadd

To reproduce:

echo this command takes 0 seconds
sleep 10
echo this command will stored with 10s duration
false
true
sqlite3 --column --header  ~/.histdb/zsh-history.db \
     "select exit_status, start_time, duration, argv from history, commands where commands.rowid = history.command_id order by start_time desc LIMIT 6;"

From README:

autoload -Uz add-zsh-hook
add-zsh-hook preexec _start_timer
add-zsh-hook precmd  _stop_timer

The history is recorded using zshhistoryadd which is called just before preexec

Timeline tracing command-1 and command-2:

 command-1 is typed and executed by user
 zshhistoryadd
   Neither _STARTED or _FINISHED set. command-1 saved with 0 duration
 preexec
   _STARTED set
 command-1 finishes
 precmd
  _FINISHED set
 command-2 is typed and executed by user
 zshhistoryadd
   command-2 is saved with the duration of command-1

Doc reference:

precmd Executed before each prompt.  Note that precommand functions are not re-executed simply
      because the command line is redrawn, as happens, for example, when a notification about
      an exiting job is displayed.

preexec
      Executed  just  after a command has been read and is about to be executed.  If the his‐
      tory mechanism is active (regardless of whether the line was discarded from the history
      buffer),  the  string that the user typed is passed as the first argument, otherwise it
      is an empty string.  The actual command  that  will  be  executed  (including  expanded
      aliases)  is  passed  in  two  different  forms:  the second argument is a single-line,
      size-limited version of the command (with things  like  function  bodies  elided);  the
      third argument contains the full text that is being executed.

Disclaimer: I do have inc_append_history_time set and haven't tested without it, but looking at the code and doc I don't see how it would affect things.

Integration with zsh-autosuggestions

Hi,
I added the related function and set the env var for integration with zsh-autosuggestions. I works, but not correctly. It searches history but adds a new line after each suggestion as the screenshot below.

Share history across machines?

I have a linux box that spin up multiple vms, I want to share history between them, how to?

If I don't use this repo, I can easily mont an nfs system under vm machine and have it work out of box, but this software would require sqlite db on host if I am correct.

That means it would require to also share access to sqlite db to vm. idk if it's possible?

bindkey '^r' _histdb-isearch

I'm not exactly sure how to describe the behavior, but I have ^r bound to _histdb-isearch, but not sure if its a display issue, a fill-in issue, or something else... Let me describe the behavior...

I ^r at the start of the prompt and start typing the command I want to search. The first match it comes up with displays fine. If I hit enter, it puts in the command as far as I had typed, but then duplicates the arguments. For example:

^r
type: vi
finds my last vi command vi dir_colors
If I hit enter, my result is: vi dir_colors dir_colors with the cursor right after vi.

I could just go to the end of the first dir_colors and be happy, but its not what I would expect.

Now if I do the following:
^r
type: vi
^r
It brings up the previous command to the last vi command, displaying it fine.
If I hit enter, my result is: vi cluster.yaml dir_colors with the prompt right after vi.

Again I could go to the end of cluster.yaml and be happy, but its not what I would expect.

This is zsh version 5.7.1 on OS X 10.15.5

I am attaching an output of functions and bindkey, let me know if you need other items please.

functions.txt
bindkey.txt

column: read failed: Invalid or incomplete multibyte or wide character

When I run "histdb --limit 19577" I get an error saying "column: read failed: Invalid or incomplete multibyte or wide character"

I have nearly 20000 entries in my history, and as my history grows, the number I can pass to histdb's --limit argument without issues also increases. For instance, some entries back I was limited to only "histdb --limit 19564" but after running some other commands, now histdb displays history fine up to "histdb --limit 19576". Also, passing any number greater than 19577 also results in the same error.

To me this looks like this error is triggered when I specify a limit that exceeds (or maybe reaches) the number of entries in the histdb database. But I have no way of knowing if that's the case because I don't know how many entries are actually in my the database.

When I pass the -d flag to histdb (as "histdb -d --limit 19577") the result is:

select strftime(case when datetime(max_start, 'unixepoch') > datetime('now', 'start of day') then '%H:%M' else '%d/%m' end, max_start, 'unixepoch') as time, session as ses, dir, argv as cmd from (select session, replace(places.dir, '/home/me', '~') as dir, replace(commands.argv, '
', '
^_^_^_') as argv, max(start_time) as max_start
from
  history
  left join commands on history.command_id = commands.rowid
  left join places on history.place_id = places.rowid
where not (commands.argv like 'histdb%') and places.host='myhost'
group by history.command_id, history.place_id
order by max_start desc
limit 19577) order by max_start asc

buffer:3: file exists: /tmp/tmp.KzWP3NvU39

After upgrading to the newest version of histdb, whenever I pipe histdb or histdb --limit X (where X is any integer) through another command like more or less I get a buffer:3: file exists error.

For example:

> histdb --limit 10 | more         
buffer:3: file exists: /tmp/tmp.TEUwD26gtS
(showing 10 of 27797 results)

The actual tmp file name varies from run to run. One run it's /tmp/tmp.TEUwD26gtS another it's /tmp/tmp.NTzAMhH1TY

Piping just histdb without any argument through more or less gives the same error.

Running histdb (with or without the --limit argument) without piping it through another program works normally.

Here is the output of histdb --limit 10 -d | less

select strftime(case when datetime(max_start, 'unixepoch') > datetime('now', 'start of day') then '%H:%M' else '%d/%m' end, max_start, 'unixepoch', 'localtime') as time, session as ses, dir, argv as cmd from (select session, replace(places.dir, '/home/me', '~') as dir, replace(commands.argv, '
', '
^_^_^_') as argv, max(start_time) as max_start
from
  history
  left join commands on history.command_id = commands.rowid
  left join places on history.place_id = places.rowid
where not (commands.argv like 'histdb%') and places.host='myhost'
group by history.command_id, history.place_id
order by max_start desc
limit 10) order by max_start asc