もう何度も考えているのに。楽をしようとするから無駄に難しくなる。本末転倒。
「ニュースを重複なく全件する」の失敗ログ
published,url,titleをキーにするpublishedがDBより新しいフィードのみ取り込む- ☓: 取りこぼしが生じうる。複数のフィードから取得するとき、別のフィードで日時が少し古く未取得のものがありうる。
published,urlを降順ソートして重複しないもののみ取り込む- ☓: 重複が発生しうる。同一ソースの別カテゴリフィードから取得するとき、
url,titleが重複するがpublishが異なる同一ニュースを取り込んでしまうときがある。
- ☓: 重複が発生しうる。同一ソースの別カテゴリフィードから取得するとき、
重複チェックキーの再考
というわけで、以下がいいかもしれない。
- ソートキー:
published - IDキー:
url,title
本当なら重複チェックにpublishedも入れたかった。先述の通りpublishedは同一ソースの別カテゴリフィードから取得するとき異なるときがある。それは数十分くらいの差と思われる。おそらく24時間以上の誤差はないと思われる。よって、重複チェックは以下のようにしたかった。
published += 24h && url==url && title==title
だが、このように厳密に重複チェックをするのは非常に面倒。SQLite3の表制約では不可能。Pythonコードによって作り込むしかない。面倒。よって、表制約UNIQUE(url,title)で判定するよう妥協するのがマシか。
統合によるニュース取りこぼしの可能性
同一ソースの別カテゴリフィードを1つのDBに統合することにした。だが、これによりニュースを取りこぼす可能性がある。たとえばカテゴリAの最新日時がカテゴリBの最新日時より新しいとき、カテゴリBの新しいニュースを取りこぼしてしまう恐れがある。
フィードから取り込むニュースは、DB既存ニュースの最新日時より新しいものに限る。これが問題だ。別カテゴリフィードを統合してしまったことで、DB側の最新日時は全カテゴリ中の最新日時を返す。そのせいで、カテゴリAとBの最新日時における期間中に、Bの新しいニュースがあったとき、その日時はAの最新日時より古いため取り込まれないことになってしまう。
統合による取りこぼしを解決する方法
フィードごとにDBを分ける。または同一DBにするなら、フィードURLごとにグループ化できるようにする。
簡単なのは前者。ただし同一ソース別カテゴリを統合するプロセスが別に必要となってしまう。
後者は実装が大変だし、データも増えてしまう。そこまでして1つのDBにこだわるべきか判断が難しい。
- フィードごとにDBを分ける
- 統合DBにてフィード列を設けグループ化できるようにする
- 統合DBにて公開日時が古くても
urlとtitleの両方が違えば挿入する
1. フィードごとにDBを分ける
たとえば以下の3フィードがあったとする。
http://A/A_category1.xmlhttp://A/A_category2.xmlhttp://B/B.rdf
これらをすべて別DBに分ける。問題は以下。
DB名
- フィードURLから一意のDB名をどうやって決めるか
最も簡単なのは、自動化をあきらめて手動で管理すること。
echo "http://A/A_category1.xml" | ./run.sh ./A1.db echo "http://A/A_category2.xml" | ./run.sh ./A2.db echo "http://B/B.rdf" | ./run.sh ./B.db
自動化したいなら、フィードマスターテーブルを作るのがいいだろう。DBファイル名にidを使えば重複することはない。
create table feeds( id integer primary key, url text unique not null );
フィード統合とHTML取得
問題は統合とHTML取得である。同一ソース別カテゴリのフィードにおいて、ニュースが重複することがある。publishedが少し違い、urlとtitleが同一ものだ。フィード取得時点でそれを見つけたら、HTML取得を1回だけにした上、DBへの保存も1箇所だけにしたい。
問題がむずかしくなる原因は以下2点。
- フィードごとにおける最新公開日時
- 別フィード間における重複記事
日時に関する解法は、最後に取得した時点での最新公開日時をフィードごとに記録することだ。
create table feeds( id integer primary key, url text not null unique, latest_published text not null default '', );
次回、そのフィードを取得するときは、latest_publishedより新しい記事のみを対象に取り込めばいい。
以下のUPSERTコードでフィードの追加と日時更新ができるだろう。
insert or replace into feeds(url,latest_published) values(?,?);
重複記事に関しては、ハードリンクしたい。だが、別DB間でレコードをハードリンクすることなどできない。
- 別DBでも同一ニュースを取得したい
- でもデータ保存スペースは1つ分にしたい
ここでDB統合することになる。だが、統合すればそれが別DBとなり、データ重複が生じてしまう。解法案として、統合DBはインメモリDBとすること。
attach './A1.db' as 'A1'; attach './A2.db' as 'A2'; create table news( ... UNIQUE(url,title) ); insert or ignore into news(published,url,title,body) select published,url,title,body from A1.news; insert or ignore into news(published,url,title,body) select published,url,title,body from A2.news;
これでurlとtitleが重複したレコードを無視した統合DBがメモリ上にできる。
だが、解決できていない問題が以下。
- データ重複保存（カテゴリ間における重複記事は、それぞれのDBで保存されている）
異なるDB保存という方法をとる以上、DB間レコードでハードリンクでもできない限り、解決できない。
2. 統合DBにてフィード列を設けグループ化できるようにする
フィードマスターテーブルを作ることになる。
create table feeds( id integer primary key, url text unique not null );
newsテーブルにfeed_id列を追加する。
create table if not exists news( feed_id integer references feeds(id), id integer primary key, published text, url text, title text, body text, -- URL先から本文だけを抽出したプレーンテキスト UNIQUE(published,url) -- 記事の一意確認 );
窓関数を使って、フィードごとにおける最新日時を取得する。
select feed_id, max(published) as latest_published over( partition by feed_id order by published desc ) from news;
たとえばnews.db内が以下のようなデータだったとする。
select feed_id, published from news;
feed_id|published 1|2000-01-01T00:00:00Z 1|2000-01-31T00:00:00Z 2|2000-02-01T00:00:00Z 2|2000-02-28T00:00:00Z
このとき、窓関数による最新日時取得は以下になる。
feed_id|latest_published 1|2000-01-31T00:00:00Z 2|2000-02-28T00:00:00Z
feed_idが1,2それぞれのときの最新日時が取得される。そして、それぞれのフィードに対し、最新日時より新しいニュースのみ取り込むようにする。
問題は、同ソース別カテゴリの重複記事。url,titleが同一だが、feed_id,publishedが異なる。保存先は1箇所にしたい。だが、そうしてしまうと問題が生じる。重複記事が最新公開日時のときだ。どちらかのfeed_idの最新日時が登録されないことになってしまう。次回、重複記事が登録されることになってしまうだろう。
news表でフィードごとの最新日時を管理するには不都合があるようだ。feeds表にlatest_published列を追加して管理すべきか。その場合、重複記事のデータはnews表においてどちらか一方のfeed_idと紐づくレコードに挿入されることになる。
問題は、feed_idに紐づくはずの記事がなくなってしまうことだ。重複を省くためなのはいいとしても、feed_idに紐づく記事として検索できるようにはしたい。すると「feed_idは1記事あたりで重複しうる」ことになる。それを反映させたテーブルは以下。
create table if not exists news( id integer primary key, published text, url text, title text, body text, -- URL先から本文だけを抽出したプレーンテキスト feed_ids text, -- [0,1,2,...]のようなJSON配列形式 UNIQUE(published,url) -- 記事の一意確認 );
だが、コストに見合わなそう。おそらくほとんどが1つのfeed_idだろう。
これなら、取得したフィード中にDB未登録のものがあるかどうかを探したほうが早そうだ。事前にHTML取得してしまうならそれすら不要でinsert or ignore ...でOK。ただ、HTML取得が遅いため重複した実行は避けたい。
以下3でDB未登録を探す方法について考える。
3. 統合DBにて公開日時が古くてもurlとtitleの両方が違えば挿入する
取得したフィードのエントリ1件ずつ確認する。DB内にurlとtitleの両方が完全一致するものがあれば取り込まない。
select count(*) from news where url=? and title=?;
ただし、あまり古いものまで確認する必要はない。最新公開日時から24時間以内くらいの範囲で十分だろう。URLが再利用され、タイトルが偶然かぶる可能性もあるため。
select count(*) from news where 最新日時-24時間 < published and url=? and title=?;
もし結果が0なら、HTML取得し、本文抽出し、それをDBへ挿入する。もし結果が1以上なら何もせず次のニュースへ。
これはフィードで取得された全件で行わなければならない。なぜならフィードが統合されたことにより、単純に最新日時だけをみると、同一ソース別カテゴリにて、最新日時より古い未登録ニュースがあるかもしれないから。
ただし、同一ソース別カテゴリ単位で統合されたDBを持っていれば、最新日時以降は中断できる。だが、DBを全ソースで統合するなら、フィードで取得された全件をチェックするしかない。欠点は、フィードの取得頻度が高いほど無駄なチェック処理が多発すること。
重複チェックの重複を避けるために、フィードごとに取り込んだ最新日時を保持しておくと良いかもしれない。
create table feeds( id integer primary key, url text not null unique, latest_published text not null default '', );
つまり、3つの表を用いて3段構えで「重複なく全ニュースを取得する」ことを実現する。
| テーブル | 管理内容 |
|---|---|
fees |
フィードごとの最新公開日時 |
news_summary |
統合したフィードのデータ |
news |
ニュース（抽出した本文を含む） |
feesのデータを用いてnews_summaryの登録を公開日時で限定するnews_summaryでソースを統合する（UNIQUE(url,title),insert or ignore ...）news_summaryのURLから本文を抽出して、news表へ登録する（UNIQUE(url,title),insert or ignore ...）
手順2のinsertにおけるコンフリクトはignoreであるべき。日時順であっても同一ソース別カテゴリで古い日付の未登録データがあるかもしれないから。
手順3のinsertにおけるコンフリクトはignoreかfail。基本的に重複しないはず。feedsの日時以降だけなので。ignoreでもよいが、万一重複したときはその時点で終了すればいい。その時点でおかしいから。でもおかしいなら何が起こっているかわからない。ignoreのほうがいいかも？
フィード取得するたびfeedsの更新日時をupdateしてファイルに書き込むとディスクI/Oが増えてしまう。そこでRAMディスクにDBを配置する。これにより、全件チェックはPC電源をつけた後の初回のみとなる。以降、PC電源を切るまでの間、最新日時チェックだけで済ませることができる。
また、PC電源OFFしてONしたとき、前回の更新日時を保ちたい。そこで、PC電源OFFしたときだけfeedsの更新日時をupdateするようにする。これなら電源ONの間にフィード取得しても、RAMディスクに書き込まれるだけで済む。つまりfeedsの記録はPC起動1回あたり1度だけ。たとえ何度フィード取得しようとも。
結局どうする？
3がもっとも妥当か。フィードのようにDBとの整合性を保てない特性がある中、ニュースを重複せず全件取得し、データ重複して保存せず（ファイルサイズを最小化して）、重複チェックを最小化する方法。
ただし応答速度への悪影響がある。1件ずつ挿入するから。そこまでして、統合への取りこぼしを気にするべきか？ コストのほうが大きいかもしれない。
前回のは取りこぼしうるが、フィードを統合しなければいい話。仮に統合したところで、取りこぼすのはわずかだろう。具体的には、PC電源OFF〜ONまでの間に生じた統合フィード間の最新公開日時の時間差中に公開されたニュースだけ。電源ON中なら取りこぼしは生じない。パレートの法則からみて、ここで妥協するのも候補のうちだろう。
もしやるなら3。ただし大改編になるだろうから、前回とは別リポジトリにしたほうがいい。失敗したら前回リポジトリを使えばいい。
対象環境
- Raspbierry pi 3 Model B+
- Raspbian stretch 9.0 2018-11-13 ※
- bash 4.4.12(1)-release ※
- Python 3.5.3
- 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
