やってみる

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

📌 ？
トップ > WebApi > NewsApiで得たニュースを保存するSQLite3テーブルを考える

NewsApiで得たニュースを保存するSQLite3テーブルを考える

WebApi SQLite3

　何をどう保存すべきか。

成果物

テーブル

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テーブルは無くてもいい。ただ、情報源がどこであるかを意識できたほうが嬉しい
    • もしsourceテーブルを作れば、news.urlのドメイン名部分を省略できる（要外部キー結合）
      • ただしテーブル結合してドメイン部分の取得が必要になる

　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);
  • ドメイン名の辞書順（newsのURLからこれをキーに検索するだろうから）
  • 最新順（情報源名が改名されたとき最新を表示する）
  • 情報源名が欲しいだろうから入れる（カバリングインデックス）

　imagesテーブルにインデックスは不要。強いて作るなら以下。役に立たなそう。

create index idx_images on images(news_id);

対象環境

$ uname -a
Linux raspberrypi 4.19.42-v7+ #1218 SMP Tue May 14 00:48:17 BST 2019 armv7l GNU/Linux

前回まで