何をどう保存すべきか。
成果物
テーブル
news.db
create table news( id integer primary key, published text, url text, title text, body text, -- URL先から本文だけを抽出したプレーンテキスト );
パラメータ | 意味 | 保存是非 | 理由 |
---|---|---|---|
content |
HTMLの一部 | ☓ | 何の意味がある? |
title |
タイトル | ○ | 最初に目視するもの。概要。 |
publishedAt |
公開日時 | ○ | 最新・一意・記事時点を確認する |
url |
記事へのURL | ○ | 一意確認。本文参照。 |
source.name |
情報源名 | ☓ | 目視の必要ない。URLで代用可。 |
source.id |
情報源ID | ☓ | null の場合もある。URLで代用可。 |
description |
説明か本文の一部 | ☓ | 本文の全文が欲しい |
author |
著者 | ☓ | 情報源より低優先度。URLで代用。 |
urlToImage |
画像 | ○ | 本文の詳細や補足となる |
- 列名はできるだけスネークでもキャメルでもない1単語にする(
publishedAt
→published
) - タイトルに情報源名が含まれている場合がある。末尾に
- 情報源名
という形で。これは不要なので削除したい。 - 本文はHTML形式でないと正確な表現ができない可能性がある。だが、今回はプレーンテキストを対象とする。HTMLの
<p>
タグ内を抽出する形になるはず。 source
テーブルは無くてもいい。ただ、情報源がどこであるかを意識できたほうが嬉しい
category
どうしよう。JSON項目には存在しないのだが……。面倒なので列を増やすのでなく、DBファイルを分けてしまうのも手か。あるいはカテゴリを無視して全部入れるか。とりあえず保留。
オプションテーブル
url
ドメイン名と情報源名を紐付ける。news
テーブルから情報源名を得るために。情報源名が改名されたり、ドメイン名が変更されることもあるだろうから考慮する。
create table sources( id integer primary key, domain text, -- URLのドメイン名 name text, -- 情報源名 created text -- 登録日時(同一ドメイン名が複数あるとき新しいほうを表示する) );
画像データ。できればpngquant
で圧縮してから保存したい。バイナリは検索効率を下げてしまうらしいので、別DBファイルにする。
news_images.db
create table images( news_id integer, -- どの記事に対応した画像か url text, -- 拡張子も含めているはず。これ重要 image blob -- バイナリ );
全文検索するときは以下。
news.db
create table news( id integer primary key, published text, url text, title text, ); create virtual table news_fts5 using fts5(body,content='news',content_rowid='id');
本当はtitle
も全文検索に入れたいのだが、インデックスが貼れなくなるので断念。
インデックス
create index idx_news on news(published desc, id desc, url, title);
- 最新日時順
- 一覧取得用としてタイトルも入れる(カバリングインデックス)
- 本文は不要(サイズ肥大化防止)
create index idx_sources on sources(domain, created desc, id desc, name);
images
テーブルにインデックスは不要。強いて作るなら以下。役に立たなそう。
create index idx_images on images(news_id);
対象環境
- Raspbierry pi 3 Model B+
- Raspbian stretch 9.0 2018-11-13
- bash 4.4.12(1)-release ※
- SQLite 3.29.0 ※
- MeCab 0.996ユーザ辞書
$ uname -a Linux raspberrypi 4.19.42-v7+ #1218 SMP Tue May 14 00:48:17 BST 2019 armv7l GNU/Linux