Chromeの履歴はSQLiteらしいのでいじってみる

概要

Chromeの履歴はSQLiteで管理されているらしく、容易に中身を見れそうなので試してみました。
自分のChromeの履歴を用いて、どのウェブサイトに頻繁にアクセス（回数、滞在時間）しているか見てみます。

おことわり

SQLiteの.schemaコマンドを用いることで、どのようなテーブルが定義されているかは分かりますが、その使われ方（どのようなイベントが起きた時に、レコードが積まれるのか・レコードが更新されるのか）について明確に説明している公式情報を見つけられませんでした。
そのため、今回実行したSQLが目的の情報を正しく取得するものとなっているかは微妙なところがあります。

テーブル定義を見てみる

Historyファイル*1をsqlite3コマンドがある環境にコピーして開きます。.schemaコマンドで、定義されているテーブルを確認すると以下、17のテーブルが定義されていました。

urls、visits、keyword_search_termsあたりに面白そうなデータが入っていそうです。

CREATE TABLE meta(
    key LONGVARCHAR NOT NULL UNIQUE PRIMARY KEY,
     value LONGVARCHAR);

CREATE TABLE downloads (
    id INTEGER PRIMARY KEY,
    guid VARCHAR NOT NULL,
    current_path LONGVARCHAR NOT NULL,
    target_path LONGVARCHAR NOT NULL,
    start_time INTEGER NOT NULL,
    received_bytes INTEGER NOT NULL,
    total_bytes INTEGER NOT NULL,
    state INTEGER NOT NULL,
    danger_type INTEGER NOT NULL,
    interrupt_reason INTEGER NOT NULL,
    hash BLOB NOT NULL,
    end_time INTEGER NOT NULL,
    opened INTEGER NOT NULL,
    last_access_time INTEGER NOT NULL,
    transient INTEGER NOT NULL,
    referrer VARCHAR NOT NULL,
    site_url VARCHAR NOT NULL,
    tab_url VARCHAR NOT NULL,
    tab_referrer_url VARCHAR NOT NULL,
    http_method VARCHAR NOT NULL,
    by_ext_id VARCHAR NOT NULL,
    by_ext_name VARCHAR NOT NULL,
    etag VARCHAR NOT NULL,
    last_modified VARCHAR NOT NULL,
    mime_type VARCHAR(
    255) NOT NULL,
    original_mime_type VARCHAR(
    255) NOT NULL,
     embedder_download_data VARCHAR NOT NULL DEFAULT '');

CREATE TABLE downloads_url_chains (
    id INTEGER NOT NULL,
    chain_index INTEGER NOT NULL,
    url LONGVARCHAR NOT NULL,
    PRIMARY KEY (id, chain_index) );

CREATE TABLE downloads_slices (
    download_id INTEGER NOT NULL,
    offset INTEGER NOT NULL,
    received_bytes INTEGER NOT NULL,
     finished INTEGER NOT NULL DEFAULT 0,
    PRIMARY KEY (download_id, offset) );

CREATE TABLE typed_url_sync_metadata (
    storage_key INTEGER PRIMARY KEY NOT NULL,
    value BLOB);

CREATE TABLE IF NOT EXISTS "urls"(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    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);

CREATE TABLE sqlite_sequence(
    name,
    seq);

CREATE TABLE visit_source(
    id INTEGER PRIMARY KEY,
    source INTEGER NOT NULL);

CREATE TABLE keyword_search_terms (
    keyword_id INTEGER NOT NULL,
    url_id INTEGER NOT NULL,
    term LONGVARCHAR NOT NULL,
    normalized_term LONGVARCHAR NOT NULL);

CREATE TABLE segments (
    id INTEGER PRIMARY KEY,
    name VARCHAR,
    url_id INTEGER NON NULL);

CREATE TABLE segment_usage (
    id INTEGER PRIMARY KEY,
    segment_id INTEGER NOT NULL,
    time_slot INTEGER NOT NULL,
    visit_count INTEGER DEFAULT 0 NOT NULL);

CREATE TABLE content_annotations (
    visit_id INTEGER PRIMARY KEY,
    floc_protected_score DECIMAL(
    3,
     2),
    categories VARCHAR,
    page_topics_model_version INTEGER,
    annotation_flags INTEGER DEFAULT 0 NOT NULL,
     entities VARCHAR,
     related_searches VARCHAR,
     visibility_score NUMERIC DEFAULT -1,
     search_normalized_url,
     search_terms LONGVARCHAR,
     alternative_title);

CREATE TABLE context_annotations(
    visit_id INTEGER PRIMARY KEY,
    context_annotation_flags INTEGER DEFAULT 0 NOT NULL,
    duration_since_last_visit INTEGER,
    page_end_reason INTEGER,
     total_foreground_duration NUMERIC DEFAULT -1000000);

CREATE TABLE clusters(
    cluster_id INTEGER PRIMARY KEY,
    score NUMERIC NOT NULL);

CREATE TABLE clusters_and_visits(
    cluster_id INTEGER NOT NULL,
    visit_id INTEGER NOT NULL,
    score NUMERIC NOT NULL,
    PRIMARY KEY(cluster_id, visit_id))WITHOUT ROWID;

CREATE TABLE downloads_reroute_info (
    download_id INTEGER NOT NULL,
    reroute_info_serialized  VARCHAR NOT NULL,
    PRIMARY KEY (download_id) );

CREATE TABLE IF NOT EXISTS "visits"(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    url INTEGER NOT NULL,
    visit_time INTEGER NOT NULL,
    from_visit INTEGER,
    transition INTEGER DEFAULT 0 NOT NULL,
    segment_id INTEGER,
    visit_duration INTEGER DEFAULT 0 NOT NULL,
    incremented_omnibox_typed_score BOOLEAN DEFAULT FALSE NOT NULL,
    opener_visit INTEGER,
     originator_cache_guid TEXT,
     originator_visit_id INTEGER);

集計してみる

どれくらい頻繁にウェブサイトにアクセスしているか確認したいので、urls、visitsのテーブルを用いれば良さそうです。ドメイン単位に、どれくらいの時間閲覧していたか、どれくらいの回数アクセスしたかを集計するSQLを書いてみます。

閲覧時間について

閲覧時間が長いドメイン順にソートして、上位5件を取得しました。

sqlite> WITH joined_http_url AS (
   ...> SELECT replace(replace(urls.url, 'https://', ''), 'http://', '') AS replaced,
   ...>        visits.visit_duration AS duration
   ...> FROM visits
   ...> LEFT JOIN urls
   ...> ON visits.url = urls.id
   ...> WHERE urls.url LIKE 'https://%' or urls.url LIKE 'http://%'
   ...> ), per_domain AS (
   ...> SELECT CASE
   ...>            WHEN instr(replaced, '/') > 0 THEN substr(replaced, 1, instr(replaced, '/') -1)
   ...>            ELSE replaced
   ...>        END AS domain,
   ...>        duration
   ...>     FROM joined_http_url
   ...> ), aggregation AS (
   ...> SELECT domain,
   ...>        sum(duration) AS total
   ...> FROM per_domain
   ...> GROUP BY domain
   ...> )
   ...> SELECT (total / 3600 / 1000000) || ' hours ' || strftime('%M minutes %S seconds', total / 1000000 / 86400.0) as total_duration,
   ...>        domain
   ...> FROM aggregation
   ...> ORDER BY total DESC
   ...> LIMIT 5
   ...> ;
total_duration  domain
107 hours 44 minutes 31 seconds www.google.com
99 hours 44 minutes 21 seconds  docs.python.org
82 hours 56 minutes 20 seconds  github.com
64 hours 38 minutes 15 seconds  www.rfc-editor.org
64 hours 36 minutes 36 seconds  www.ietf.org

アクセス回数について

アクセス回数が多いドメイン順にソートして、上位5件を取得しました。

sqlite> WITH http_url AS (
   ...> SELECT replace(replace(urls.url, 'https://', ''), 'http://', '') AS replaced,
   ...>            urls.visit_count
   ...> FROM urls
   ...> WHERE urls.url LIKE 'https://%' or urls.url LIKE 'http://%'
   ...> ), per_domain AS (
   ...> SELECT CASE
   ...>            WHEN instr(replaced, '/') > 0 THEN substr(replaced, 1, instr(replaced, '/') -1)
   ...>            ELSE replaced
   ...>        END AS domain,
   ...>        visit_count
   ...>     FROM http_url
   ...> ), aggregation AS (
   ...> SELECT domain,
   ...>        sum(visit_count) AS total
   ...> FROM per_domain
   ...> GROUP BY domain
   ...> )
   ...> SELECT total,
   ...>        domain
   ...> FROM aggregation
   ...> ORDER BY total DESC
   ...> LIMIT 5
   ...> ;
total   domain
3240    www.google.com
1340    github.com
1326    docs.google.com
872     twitter.com
395     www.cambly.com

それらしいデータを得ることはできましたが、どうも閲覧時間の方の集計について主観よりも長い時間になっているように思えます。ページを開きっぱなしにして、別のタブに移動していたとしても、アクセス時間として計上される仕様なのかもしれません。（そのあたりは十分に調べられていません）

*1:windowsであれば、C:\Users\ユーザー名\AppData\Local\Google\Chrome\User Data\Default配下に存在する模様

zkat’s diary

技術ブログ