`scrapers/db.js`

73 lines
'use strict';
const Database = require('better-sqlite3');
const path = require('path');

const DB_PATH = path.join(__dirname, '..', 'data', 'headlines.db');

let _db = null;

function getDb() {
  if (_db) return _db;
  _db = new Database(DB_PATH);
  _db.pragma('journal_mode = WAL');
  _db.pragma('foreign_keys = ON');

  _db.exec(`
    CREATE TABLE IF NOT EXISTS headlines (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      uid TEXT UNIQUE NOT NULL,
      title TEXT NOT NULL,
      url TEXT NOT NULL,
      source TEXT NOT NULL,
      category TEXT NOT NULL,
      fetched_at INTEGER NOT NULL DEFAULT (unixepoch()),
      pub_date INTEGER,
      is_breaking INTEGER NOT NULL DEFAULT 0,
      is_developing INTEGER NOT NULL DEFAULT 0,
      is_top INTEGER NOT NULL DEFAULT 0
    );
    CREATE INDEX IF NOT EXISTS idx_headlines_category ON headlines(category);
    CREATE INDEX IF NOT EXISTS idx_headlines_fetched ON headlines(fetched_at DESC);
    CREATE INDEX IF NOT EXISTS idx_headlines_source ON headlines(source);

    CREATE TABLE IF NOT EXISTS scrape_log (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      source TEXT NOT NULL,
      ran_at INTEGER NOT NULL DEFAULT (unixepoch()),
      count_added INTEGER NOT NULL DEFAULT 0,
      error TEXT
    );
  `);

  return _db;
}

function upsertHeadline(h) {
  const db = getDb();
  const stmt = db.prepare(`
    INSERT OR IGNORE INTO headlines (uid, title, url, source, category, pub_date, is_breaking, is_developing, is_top)
    VALUES (@uid, @title, @url, @source, @category, @pub_date, @is_breaking, @is_developing, @is_top)
  `);
  const info = stmt.run(h);
  return info.changes;
}

function getHeadlines(opts = {}) {
  const db = getDb();
  const { category, limit = 200, since = 0 } = opts;
  let q = 'SELECT * FROM headlines WHERE fetched_at > ?';
  const params = [since];
  if (category) { q += ' AND category = ?'; params.push(category); }
  q += ' ORDER BY is_top DESC, fetched_at DESC LIMIT ?';
  params.push(limit);
  return db.prepare(q).all(...params);
}

function logScrape(source, count, error = null) {
  const db = getDb();
  db.prepare('INSERT INTO scrape_log (source, count_added, error) VALUES (?, ?, ?)')
    .run(source, count, error);
}

module.exports = { getDb, upsertHeadline, getHeadlines, logScrape };