This is yet another post on fzf, a command-line fuzzy finder. Recently I've been trying to integrate fzf with everything I can think of, and this time, it's the browsing history of Google Chrome.
Chrome database
So how do we access the browsing history? Turns out that Chrome stores the information in its SQLite database file locally stored on your disk drive. The location of the file depends on your operating system. As of now, it's
~/Library/Application Support/Google/Chrome
on OS X and~/.config/google-chrome/Default/databases
on Linux.
In the following, we'll assume OS X, but you shouldn't have any difficulty applying the same strategy on Linux.
Anyway, now we know the location of the database, we can access the
information with "sqlite3" command. However, if you're running Chrome at the
moment, the database file is locked by the process and you'll see Error:
database is locked
message. An easy workaround is to simply make a copy of
the file.
cp -f ~/Library/Application\ Support/Google/Chrome/Default/History /tmp/h
Let's examine the database. First we see the list of the tables in it.
sqlite3 /tmp/h .tables
downloads meta urls
downloads_url_chains segment_usage visit_source
keyword_search_terms segments visits
Okay, looks like urls
is the table we're looking for. Now we check the
schema of the table with .schema
command.
sqlite3 /tmp/h '.schema urls'
CREATE TABLE urls(
id INTEGER PRIMARY KEY,
url LONGVARCHAR,
title LONGVARCHAR,
visit_count INTEGER DEFAULT 0 NOT NULL,
typed_count INTEGER DEFAULT 0 NOT NULL,
last_visit_time INTEGER NOT NULL,
hidden INTEGER DEFAULT 0 NOT NULL,
favicon_id INTEGER DEFAULT 0 NOT NULL);
CREATE INDEX urls_url_index ON urls (url);
Notice that I reformatted the output so that it's easier to read. The names of the columns are very straightforward, and we're ready to run some SQLs.
sqlite3 /tmp/h 'select title, url from urls order by last_visit_time desc'
Formatting
You'll notice that two fields in each line of the output are delimited by a
|
character.
Issues · junegunn/fzf|https://github.com/junegunn/fzf/issues
junegunn/fzf|https://github.com/junegunn/fzf
junegunn.kr|http://junegunn.kr/
This is the default format. However, the delimiter is not good enough as the character is often found in titles and URLs and SQLite does not do any escaping.
sqlite3 /tmp/h "select '|', '|'"
|||
To avoid possible conflicts, we're going to change the delimiter with
-separator
option. Let's pick some obscure string that'll probably never
appear in the text. Also, as our screen estate is limited, we'll going to
truncate the title field using substr()
function of SQLite.
sqlite3 -separator {::} /tmp/h \
"select substr(title, 1, $(( COLUMNS / 3 ))), url
from urls order by last_visit_time desc"
Instead of hard-coding the width, we used $COLUMNS
whose value is
automatically set to the width of the terminal.
Since we now know the maximum length of the first field, we can use printf
of awk
command to align the fields.
cols=$(( COLUMNS / 3 ))
sep={::}
sqlite3 -separator $sep /tmp/h \
"select substr(title, 1, $cols), url
from urls order by last_visit_time desc" |
awk -F $sep '{printf "%-'$cols's %s\n", $1, $2}'
Issues · junegunn/fzf https://github.com/junegunn/fzf/issues
junegunn/fzf https://github.com/junegunn/fzf
junegunn.kr http://junegunn.kr/
Much better. Let's further decorate the output with colors. We put ANSI escape code for cyan foreground color before the second URL field.
# Print URLs in cyan color (36)
awk -F $sep '{printf "%-'$cols's \x1b[36m%s\x1b[m\n", $1, $2}'
Putting it all together
Finally time for fzf integration. We feed what we've prepared into fzf for
interactive filtering. Since the input has ANSI escape codes, we need to pass
--ansi
option to fzf so that it correctly interprets the codes. Also, we
pass --multi
option so that the user can select multiple entries with TAB or
shift-TAB key and open them at once. Once fzf is completed with the
selections, we strip the title part with sed
command (strip everything
before "http"), and use open
command of OS X to open the selected URLs with
the default browser.
# c - browse chrome history
c() {
local cols sep
cols=$(( COLUMNS / 3 ))
sep='{::}'
cp -f ~/Library/Application\ Support/Google/Chrome/Default/History /tmp/h
sqlite3 -separator $sep /tmp/h \
"select substr(title, 1, $cols), url
from urls order by last_visit_time desc" |
awk -F $sep '{printf "%-'$cols's \x1b[36m%s\x1b[m\n", $1, $2}' |
fzf --ansi --multi | sed 's#.*\(https*://\)#\1#' | xargs open
}
Now we can just type in c
from terminal to browse the entire Chrome history,
fuzzy-search for links, and open them in the default browser. Quite handy.
Although the code shown here is for Chrome on OS X, it won't be difficult to
adapt it for FireFox which also has its own SQLite database, and for Linux,
by replacing open
with something like xdg-open
.