　SQLite3のDBに取り込む。

情報源

Powerd by News API

前提

　NewsAPIにてJSONを取得し、ファイル保存したものとする。

JSON取得例

{
    "status": "ok",
    "totalResults": 70,
    "articles": [
        {
            "content": null,
            "title": "iPhone 11やGalaxy S10が対応する「Wi-Fi 6」の公式認定プログラムが開始 - GIGAZINE",
            "publishedAt": "2019-09-17T01:50:00Z",
            "url": "https://gigazine.net/news/20190917-wi-fi-certified-6/",
            "source": {
                "name": "Gigazine.net",
                "id": null
            },
            "description": "無線LAN製品の標準化および普及促進を図る団体Wi-Fi Allianceが次世代無線LAN規格「Wi-Fi 6」の認定プログラムを開始しました。",
            "author": "@GIGAZINE",
            "urlToImage": "https://i.gzn.jp/img/2019/09/17/wi-fi-certified-6/00.png"
        },
    ...
}

重複しない要素

title
url
publishedAt

　上記すべてが一致すると重複したとみなす。

再取得

　2回目以降、どの記事から取得すればいいか。

　これを識別できなければ重複した記事を何度も取り込んでしまう。

ソート順

　取得されたJSONは新しい日付順である。

　ただし、同日時の記事も複数ある

最新日時: publishedAt
URL: url

　面倒なので、last_insert_rowid()にしたいところ。だが、この関数はその接続時点に限られる。前回の最後のrowidは取得できない。そこで、max(rowid)とすることになる。だが、rowid自体はautoincrementしないかぎり増加が保証されるわけではない。レコード削除すると以前の小さい番号が再利用される場合もある。そこで、日付を第1キーにする。

　イメージとしては以下。

select * 
from news
where publishedAt=max(publishedAt) and rowid=max(rowid)
order by publishedAt desc, rowid desc

　おそらく大体はurlで一意確認できると思う。だが、ニュースサイトのURLは一定期間でクリアされ、再利用されている可能性も考えられる。よって、日時を主軸にする。

取得するニュースの決定

　上記にて取得されたレコードを、DB内最新とする。これよりも新しいニュースだけを取り込む。

日時publishedAtがDB内より新しいこと
同日時なら、urlが異なること（DB内にあるものは同日時における最新のはず。それと違うなら、同日時における未取得）

方法まとめ

既存DBの最新ニュースを取得しておく
新たに入手したニュースの中から既存DBより新しいものを探す

1. 既存DBの最新ニュースを取得しておく

　以下で取得できるrowidのレコードが最新ニュースである。

with newest(publishedAt) as (select max(publishedAt) from news)
  select max(rowid) 
  from news,newest 
  where news.publishedAt=newest.publishedAt;

　JSONとの同一確認に必要な全項目を取得するなら以下。

with newest(publishedAt) as (select max(publishedAt) from news)
    ,lastest(rowid) as (
       select max(rowid) 
       from news,newest 
       where news.publishedAt=newest.publishedAt
    )
select publishedAt,url,title;

　これはなんとなくのイメージ。

2. 新たに入手したニュースの中から既存DBより新しいものを探す

JSONを読み込む
$.status=okなら継続。否ならエラー
$.totalResultsの値を取得する
for i in ${totalResults}; doループする
1. $.articles[i].publishedAtが、DB既存最新ニュースのpublishedAtと同じかそれより古い
  1. $.articles[i].urlが、DB既存最新ニュースのurlと同じ
    1. [i]までのレコードを取り込む
2. 上記レコードが見つからなかったら、すべて取り込む

所感

　NewsApiでfromパラメータが使えたらこんな苦労せずに済むのだろうが……。前回のとおり、日本のニュースを取得できるエンドポイントではfromパラメータが使えない。

対象環境

2019-09-17
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

やってみる

アウトプットすべく己を導くためのブログ。その試行錯誤すらたれ流す。

NewsApiで得たニュースを重複なく取り込む方法を考える

情報源

前提