Skip to content

Instantly share code, notes, and snippets.

@outcoldman
Created July 19, 2017 21:53
Show Gist options
  • Save outcoldman/daa3d9cb5c67ce18079ce93f08ea06e1 to your computer and use it in GitHub Desktop.
Save outcoldman/daa3d9cb5c67ce18079ce93f08ea06e1 to your computer and use it in GitHub Desktop.
DBHist: bash history in sqlite
# The MIT License
# SPDX short identifier: MIT
# Further resources on the MIT License
# Copyright 2017 Denis Gladkikh (https://www.outcoldman.com/en/archive/2017/07/19/dbhist/)
# Permission is hereby granted, free of charge, to any person obtaining a copy
# of this software and associated documentation files (the "Software"), to deal
# in the Software without restriction, including without limitation the rights
# to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
# copies of the Software, and to permit persons to whom the Software is
# furnished to do so, subject to the following conditions:
# The above copyright notice and this permission notice shall be included in
# all copies or substantial portions of the Software.
# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
# IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
# FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
# AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
# LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
# OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
# SOFTWARE.
################################################################################
# To use it, just source the file `source dbhist.sh`
# Verified that works with Bash 3/4
# Configuration
# DBHISTORY=true - use false to disable
# DBHISTORYFILE=$HOME/.dbhist - location of dbhist file
#
# To make it work I keep next configuration for bash, the only important is the
# time format, to make it unixtime
#
# HISTSIZE=$HOME/.bash_history
# HISTFILESIZE=1000
# HISTSIZE=1000
# HISTTIMEFORMAT="%s "
# HISTCONTROL=ignorespace:erasedups
#
# More details in https://www.outcoldman.com/en/archive/2017/07/19/dbhist/
# Or use after you sourced this file
# dbhist --help
################################################################################
if ${DBHISTORY:-true}; then
# Kind of session ID, allows to see only commands executed in this terminal session
__dbhist_salt="${RANDOM}"
# This allows us to verify existence of .dbhist file only once
__dbhist_initialized=false
# Keep the previous folder, this how we keep the PWD for commands like `cd ...`
__dbhist_oldpwd="${OLDPWD}"
# Get location of .dbhist file, default location is under `$HOME/.dbhist`
__dbhist_file() {
echo "${DBHISTORYFILE:-$HOME/.dbhist}"
}
# Execute command on sqlite with .dbhist file
__dbhist_sqlite() {
sqlite3 "$(__dbhist_file)"
}
# Initialize dbhist file, create table
__db_hist_init() {
if [[ ! -f "$(__dbhist_file)" ]]; then
local __sql='CREATE TABLE history (
id INTEGER PRIMARY KEY AUTOINCREMENT,
hist_id INTEGER,
cmd TEXT,
epoch INTEGER,
ppid INTEGER,
pwd TEXT,
salt INTEGER
);'
echo "${__sql}" | __dbhist_sqlite
fi
}
# Execute it with bash PROMPT
__db_hist_prompt() {
if ! ${__dbhist_initialized}; then
__db_hist_init
__dbhist_initialized=true
return
fi
if [[ -n "${COMP_LINE}" ]]; then
return
fi
local __formated_history=
local __num=
local __epoch=
local __pwd="${PWD}"
if [[ "${OLDPWD}" != "${__dbhist_oldpwd}" ]]; then
__pwd="${OLDPWD}"
__dbhist_oldpwd="${OLDPWD}"
fi
# 521 1492964870 history 1
__formated_history="$(history 1 | sed -E 's/[[:space:]]+([[:digit:]]+)[[:space:]]+([[:digit:]]+)[[:space:]](.+)/\1@\2@\3/')"
__num="$(echo "${__formated_history}" | sed -E 's/([^@]+)@([^@]+)@(.+)/\1/')"
__epoch="$(echo "${__formated_history}" | sed -E 's/([^@]+)@([^@]+)@(.+)/\2/')"
__command="$(echo "${__formated_history}" | sed -E 's/([^@]+)@([^@]+)@(.+)/\3/')"
__dbhist_sqlite <<-END
INSERT INTO history(hist_id, epoch, cmd, ppid, pwd, salt)
VALUES("${__num}", "${__epoch}", "${__command//\"/""}", ${PPID}, "${__pwd//\"/""}", ${__dbhist_salt});
END
}
# Use
# dbhist --help
# To learn the tool
dbhist() {
local __limit=100
local __all=false
local __self=false
local __starts=false
local __verbose=false
local __query=''
local __location=''
local __pwd=false
while ! [[ $# -eq 0 || -z "${1}" ]]; do
case "${1}" in
(--shell)
__dbhist_sqlite
return
;;
(--help)
cat<<END
Usage:
dbhist [options] [query]
Options:
--limit {number}
number of latest commands to show. Defaults to 100.
--all
include history of all sessions. Not only current.
--self
include history about dbhist
--starts
match only commands starts with query
--verbose
verbose output (shows sql)
--here
reduce search only for current pwd
--under
reduce search only for current and under pwd
--pwd
show working directory of cmd
--shell
access to shell
END
return
;;
(--limit)
shift
__limit="${1:?option require value}"
if ! [[ "${__limit}" =~ ^[0-9]+$ ]]; then
2>&1 echo "Limit should be a number"
return 1
fi
;;
(--all)
__all=true
;;
(--self)
__self=true
;;
(--starts)
__starts=true
;;
(--verbose)
__verbose=true
;;
(--here)
if [[ -n "${__location}" ]]; then
2>&1 echo "Specifier for pwd already set"
return 1
fi
__location="here"
;;
(--under)
if [[ -n "${__location}" ]]; then
2>&1 echo "Specifier for pwd already set"
return 1
fi
__location="under"
;;
(--pwd)
__pwd=true
;;
(-*)
2>&1 echo "Unknown option ${1}"
return 1
;;
(*)
if [[ -n "${__query}" ]]; then
2>&1 echo "Cannot specify more than one query"
return 1
fi
__query="${1:?requires value}"
;;
esac
shift
done
__pwd_q=''
if ${__pwd}; then
__pwd_q='pwd || " > " ||'
fi
local __sql="
SELECT
substr(' '||max(id),-6) ||
' | ' ||
datetime(max(epoch), 'unixepoch', 'localtime') ||
' | ' ||
substr(' '||count(*),-6) ||
' | ' ||
${__pwd_q}
cmd
FROM history
WHERE 1 "
if ! ${__all}; then
__sql+="AND (salt=${__dbhist_salt} AND ppid=${PPID}) "
fi
if ! ${__self}; then
__sql+="AND (cmd != 'dbhist' AND cmd NOT LIKE 'dbhist %') "
fi
if [[ -n "${__query}" ]]; then
__query=${__query//\"/""}
if ! ${__starts} && [[ "${__query}" != %* ]]; then
__query="%${__query}"
fi
if [[ "${__query}" != *% ]]; then
__query+="%"
fi
__sql+="AND cmd LIKE \"${__query}\" ESCAPE '\' "
fi
__pwd_l="${PWD//\"/""}"
case "${__location}" in
(here)
__sql+="AND (pwd == \"${__pwd_l}\") "
;;
(under)
__pwd_l="${__pwd_l//%/\%}"
__pwd_l="${__pwd_l//_/\_}"
__sql+="AND (pwd LIKE \"${__pwd_l}%\" ESCAPE '\') "
;;
esac
__sql+="GROUP BY cmd "
if ${__pwd}; then
__sql+=", pwd "
fi
__sql+="ORDER BY max(id) DESC LIMIT ${__limit} ;"
if ${__verbose}; then
echo "database:"
__dbhist_file
echo "sql:"
echo "${__sql}"
fi
echo "${__sql}" | __dbhist_sqlite
}
# Inject __db_hist_prompt in `PROMPT_COMMAND`
if ! [[ "${PROMPT_COMMAND}" =~ .*__db_hist_prompt.* ]]; then
PROMPT_COMMAND="__db_hist_prompt${PROMPT_COMMAND:+; $PROMPT_COMMAND}"
fi
fi
@shelvacu
Copy link

shelvacu commented Jun 8, 2021

On my system (GNU bash, version 5.1.8(1)-release (x86_64-pc-linux-gnu)) history needs the env var HISTTIMEFORMAT='%s ' to produce the expected output. Sorry, this is a PEBKAC. This requirement can be removed by just prepending to the history command:

__formated_history="$(HISTTIMEFORMAT='%s ' history 1 | ...)"

This uses double-quotes(") to enclose strings in SQL, but that only works when they aren't keywords, see https://sqlite.org/lang_keywords.html

Single-quotes(') should be used instead. https://sqlite.org/lang_expr.html

A string constant is formed by enclosing the string in single quotes ('). A single quote within the string can be encoded by putting two single quotes in a row - as in Pascal. C-style escapes using the backslash character are not supported because they are not standard SQL.


also the ${var//\"/""} replaces all double-quote characters with an empty string (deletes all double-quote characters) which means that when you type the command

some-command "1.mp3 2.mp3 3.mp3"

it will show in the database as

some-command 1.mp3 2.mp3 3.mp3

which are two very different commands. This can be fixed by doing (combining single-quote fix above) something like

  __dbhist_sqlite <<-END
INSERT INTO history(hist_id, epoch, cmd, ppid, pwd, salt)
VALUES(${__num}, ${__epoch}, '${__command//\'/\'\'}', ${PPID}, '${__pwd//\'/\'\'}', ${__dbhist_salt});
END

Which replaces every instance of a single-quote with two single-quotes, which is how single-quotes are escaped in sqlite for some reason.

Similar fixes are prudent for the dbhist command code.

@shelvacu
Copy link

shelvacu commented Jun 8, 2021

This doesn't handle multi-line commands. Something like

echo 'multi
line
command'

is one history item, but sed won't recognize across lines and so the db row doesn't have the right values:

122|510
line
command'|echo 'multi
line
command'|1623133404
line
command'|39|/home/shelvacu|25931

(this is all one row in the database)

@outcoldman
Copy link
Author

@shelvacu thank you for the review, but I am not planning to support dbhist.sh at this time, I have built ShellHistory app https://loshadki.app/shellhistory/, that works perfectly for me on macOS.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment