bash interactive command history in an sqlite database

bash_history.sh

#------------------------------------------------------------------------------
#
#     Record bash interactive command history in an sqlite database
#     Requires: https://github.com/rcaloras/bash-preexec
#     Usage: source $BASH_HOME/.bash_history.sh
#
#------------------------------------------------------------------------------

compress_bash_history()
{
   pushd $BASH_HOME
   sqlite3 bash_history.db <<EOS

-- transaction begins here

BEGIN;
DROP TABLE IF EXISTS backup;
CREATE TABLE backup AS SELECT * FROM commands;
DELETE FROM commands;

INSERT INTO commands
SELECT * FROM backup 
WHERE (command_dt, command) IN
(
   SELECT MAX(command_dt), command
   FROM backup
   WHERE return_val=0
   GROUP BY command
);
   
DROP TABLE backup;
END;

-- transaction ends here

EOS
   popd
}

export_bash_history()
{
   pushd $BASH_HOME
   sqlite3 bash_history.db <<EOS

.headers off
.mode list
.output .bash_history

SELECT command FROM commands 
WHERE (command_dt, command) IN
(
   SELECT MAX(command_dt), command
   FROM commands
   WHERE return_val=0
   GROUP BY command
);

EOS

   popd
}

migrate_bash_history()
{
   pushd $BASH_HOME
   sqlite3 bash_history.db <<EOS

-- transaction begins here

BEGIN;
DROP TABLE IF EXISTS backup;
CREATE TABLE backup AS SELECT * FROM commands;
DROP TABLE commands;

.read bash_history.sql

INSERT INTO commands
SELECT 
   command_dt, 
   pid, 
   return_val, 
   path, 
   command
FROM 
   backup;
   
DROP TABLE backup;
END;

-- transaction ends here

EOS
   popd
}


preexec()
{  
   last_command="$this_command"; this_command="$BASH_COMMAND"
}

precmd()
{  
   return_code=$?
   if [[ -n $last_command ]]; then

   printf -v datetime "%(%FT%T)T"
   # in sqlite, single quotes (') inside literal strings must be doubled ('')
   q="'"; printf -v cmd "%s" "${last_command//$q/$q$q}"
   printf -v values "(\'%s\',%d,%d,\'%s\',\'%s\')" $datetime $$ $return_code $PWD "$cmd"
   # echo "$values"
   sqlite3 $BASH_HOME/bash_history.db "INSERT INTO commands VALUES $values" 2> /dev/null
   last_command=""

   fi
}

# bash-preexec.sh installs a debug trap and updates PROMPT_COMMAND
[[ -f $BASH_HOME/bash-preexec.sh ]] && source $BASH_HOME/bash-preexec.sh

if [[ -n "${bash_preexec_imported:-}" ]]; then
   echo "bash-preexec is loaded."
fi

# start each interactive bash session with a clean history
history -c
export_bash_history
history -r

bash_history.sql

CREATE TABLE IF NOT EXISTS commands
(
   command_dt timestamp,      -- printf -v datetime "%(%FT%T)T"
   pid int,                   -- kernel-assigned process ID
   return_val int,            -- non-zero if command was unsucessful
   path text,                 -- working directory
   command text               -- the command string
);