zsh-histdb

Check-in [beada4156b]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:copy source from elsewhere
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | master | trunk
Files: files | file ages | folders
SHA3-256: beada4156b9e2415749dd3f7dec2741d60672c6da41294e247b51a752f056c18
User & Date: tom.hinton@cse.org.uk 2017-04-13 16:09:27
Context
2017-04-13
16:48
clean up for people to use check-in: 575d7a36ce user: tom.hinton@cse.org.uk tags: master, trunk
16:09
copy source from elsewhere check-in: beada4156b user: tom.hinton@cse.org.uk tags: master, trunk
16:08
readme? check-in: a4070a8bfa user: tom.hinton@cse.org.uk tags: master, trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Added histdb-merge.































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
#!/usr/bin/env zsh

local ancestor=${1:?three databases required}; shift
local ours=${1:?three databases required}; shift
local theirs=${1:?three databases required}

# for reasons I cannot use the encryption filter here.
# most annoying.

sqlite3 "${ours}" <<EOF
ATTACH DATABASE '${theirs}' AS o;
ATTACH DATABASE '${ancestor}' AS a;

-- copy missing commands and places
INSERT INTO commands (argv) SELECT argv FROM o.commands;
INSERT INTO places (host, dir) SELECT host, dir FROM o.places;

-- insert missing history, rewriting IDs
-- could uniquify sessions by host in this way too

INSERT INTO history (session, command_id, place_id, exit_status, start_time, duration)
SELECT HO.session, C.rowid, P.rowid, HO.exit_status, HO.start_time, HO.duration
FROM o.history HO
LEFT JOIN o.places PO ON HO.place_id = PO.rowid
LEFT JOIN o.commands CO ON HO.command_id = CO.rowid
LEFT JOIN commands C ON C.argv = CO.argv
LEFT JOIN places P ON (P.host = PO.host
AND P.dir = PO.dir)
WHERE HO.rowid > (SELECT MAX(rowid) FROM a.history)
;
EOF

Added sqlite-history.zsh.





























































































































































































































































































































































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
which sqlite3 >/dev/null 2>&1 || return;

typeset -g HISTDB_QUERY=""
typeset -g HISTDB_FILE="${HOME}/.histdb/zsh-history.db"
typeset -g HISTDB_SESSION=""
typeset -g HISTDB_MAX_ROWID=""
typeset -g HISTDB_HOST=""

typeset -gA HISTDB_RESULT

sql_escape () {
    sed -e "s/'/''/g" <<< "$@"
}

_histdb () {
    sqlite3 "${HISTDB_FILE}" "$@"
    [[ "$?" -ne 0 ]] && echo "error in $@"
}

_histdb_init () {
    if ! [[ -e "${HISTDB_FILE}" ]]; then
        local DN="$(dirname ${HISTDB_FILE})"
        if ! [[ -d $DN ]]; then
            mkdir -p -- $DN
            pushd $DN
            git init
            git config merge.histdb.driver "$HOME/.zsh/histdb-merge %O %A %B"
            popd
        fi
        _histdb <<-EOF
create table commands (argv text, unique(argv) on conflict ignore);
create table places   (host text, dir text, unique(host, dir) on conflict ignore);
create table history  (session int,
                       command_id int references commands (rowid),
                       place_id int references places (rowid),
                       exit_status int,
                       start_time int,
                       duration int);
EOF
    fi
    if [[ -z "${HISTDB_SESSION}" ]]; then
        HISTDB_HOST="'$(sql_escape ${HOST})'"
        HISTDB_SESSION=$(_histdb "select 1+max(session) from history inner join places on places.rowid=history.place_id where places.host = ${HISTDB_HOST}")
        HISTDB_SESSION="${HISTDB_SESSION:-0}"
        readonly HISTDB_SESSION
    fi
}

declare -a _BORING_COMMANDS
_BORING_COMMANDS=("^ls$" "^cd$" "^ " "^histdb" "^top$" "^htop$")

zshaddhistory () {
    local retval=$?
    local cmd="${1[0, -2]}"

    for boring ($_BORING_COMMANDS); do
        if [[ "$cmd" =~ $boring ]]; then
            return 0
        fi
    done

    if [[ $PWD = "$HOME/.history" ]]; then
        return 0
    fi

    local cmd="'$(sql_escape $cmd)'"
    local pwd="'$(sql_escape ${PWD})'"
    local now="${_FINISHED:-$(date +%s)}"
    local started=${_STARTED:-${now}}
    _histdb_init
    if [[ "$cmd" != "''" ]]; then
        _histdb \
"insert into commands (argv) values (${cmd});
insert into places   (host, dir) values (${HISTDB_HOST}, ${pwd});
insert into history
  (session, command_id, place_id, exit_status, start_time, duration)
select
  ${HISTDB_SESSION},
  commands.rowid,
  places.rowid,
  ${retval},
  ${started},
  ${now} - ${started}
from
  commands, places
where
  commands.argv = ${cmd} and
  places.host = ${HISTDB_HOST} and
  places.dir = ${pwd}
;"

    fi
    return 0
}

histdb-top () {
    _histdb_init
    local sep=$'\x1f'
    local field
    local join
    local table
    1=${1:-cmd}
    case "$1" in
        dir)
            field=places.dir
            join='places.rowid = history.place_id'
            table=places
            ;;
        cmd)
            field=commands.argv
            join='commands.rowid = history.command_id'
            table=commands
            ;;;
    esac
    _histdb -separator "$sep" \
            -header \
            "select count(*) as count, places.host, replace($field, '
', '
$sep$sep') as ${1:-cmd} from history left join commands on history.command_id=commands.rowid left join places on history.place_id=places.rowid group by places.host, $field order by count(*)" |
        column -t -s "$sep"
}

histdb () {
    _histdb_init
    local -a opts
    local -a hosts
    local -a indirs
    local -a atdirs
    local -a sessions

    zparseopts -E -D -a opts \
               -host+::=hosts \
               -in+::=indirs \
               -at+::=atdirs \
               d h -help \
               s+::=sessions \
               -from:- -until:- -limit:-

    usage="usage:$0 [--host] [--in] [--at] [-s n]+* [--from] [--until] [--limit] query
    --host    print the host column and show all hosts (otherwise current host)
    --host=x  find entries from host x
    --in      find only entries run in the current dir or below
    --in=x    find only entries in directory x or below
    --at      like --in, but excluding subdirectories
    -s n      only show session n
    -d        debug output query that will be run
    --from=x  only show commands after date x (sqlite date parser)
    --until=x only show commands before date x (sqlite date parser)
    --limit=n only show n rows. defaults to $LINES or 25"

    local selcols="session as ses, dir"
    local cols="session, replace(places.dir, '$HOME', '~') as dir"
    local where="not (commands.argv like 'histdb%')"
    local limit="${LINES:-25}"

    if [[ -n "$*" ]]; then
        where="${where} and commands.argv like '%$(sql_escape $@)%'"
    fi

    if (( ${#hosts} )); then
        local hostwhere=""
        for host ($hosts); do
            host="${${host#--host}#=}"
            hostwhere="${hostwhere}${host:+${hostwhere:+ or }places.host='$(sql_escape ${host})'}"
        done
        where="${where}${hostwhere:+ and (${hostwhere})}"
        cols="${cols}, places.host as host"
        selcols="${selcols}, host"
    else
        where="${where} and places.host=${HISTDB_HOST}"
    fi

    if (( ${#indirs} + ${#atdirs} )); then
        local dirwhere=""
        for dir ($indirs); do
            dir="${${${dir#--in}#=}:-$PWD}"
            dirwhere="${dirwhere}${dirwhere:+ or }places.dir like '$(sql_escape $dir)%'"
        done
        for dir ($atdirs); do
            dir="${${${dir#--at}#=}:-$PWD}"
            dirwhere="${dirwhere}${dirwhere:+ or }places.dir = '$(sql_escape $dir)'"
        done
        where="${where}${dirwhere:+ and (${dirwhere})}"
    fi

    if (( ${#sessions} )); then
        local sin=""
        for ses ($sessions); do
            ses="${${${ses#-s}#=}:-${HISTDB_SESSION}}"
            sin="${sin}${sin:+, }$ses"
        done
        where="${where}${sin:+ and session in ($sin)}"
    fi

    local debug=0
    for opt ($opts); do
        case $opt in
            --from=*)
                local from=${opt#--from=}
                case $from in
                    -*)
                        from="datetime('now', '$from')"
                        ;;
                    today)
                        from="datetime('now', 'start of day')"
                        ;;
                    yesterday)
                        from="datetime('now', 'start of day', '-1 day')"
                        ;;
                esac
                where="${where} and datetime(start_time, 'unixepoch') >= $from"
            ;;
            --until=*)
                local until=${opt#--until=}
                case $until in
                    -*)
                        until="datetime('now', '$until')"
                        ;;
                    today)
                        until="datetime('now', 'start of day')"
                        ;;
                    yesterday)
                        until="datetime('now', 'start of day', '-1 day')"
                        ;;
                esac
                where="${where} and datetime(start_time, 'unixepoch') <= $until"
                ;;
            -d)
                debug=1
                ;;
            -h|--help)
                echo "$usage"
                return 0
                ;;
            --limit=*)
                limit=${opt#--limit=}
                ;;
        esac
    done

    sep=$'\x1f'
    cols="${cols}, replace(commands.argv, '
', '
$sep$sep$sep') as argv, max(start_time) as max_start"

    local mst="datetime(max_start, 'unixepoch')"
    local dst="datetime('now', 'start of day')"
    local timecol="strftime(case when $mst > $dst then '%H:%M' else '%d/%m' end, max_start, 'unixepoch') as time"

    selcols="${timecol}, ${selcols}, argv as cmd"

    query="select ${selcols} from (select ${cols}
from
  history
  left join commands on history.command_id = commands.rowid
  left join places on history.place_id = places.rowid
where ${where}
group by history.command_id, history.place_id
order by max_start desc
limit $limit) order by max_start asc"

    if [[ $debug = 1 ]]; then
        echo "$query"
    else
        _histdb -header -separator $sep "$query" | column -t -s $sep
    fi
}

# merge encrypted history databases