twitter2rss/store.ts

import { DB } from "https://deno.land/x/sqlite@v3.1.1/mod.ts";
import { User, Tweet, TweetSchema } from "./twitter.ts";

export abstract class Store {
  saveTweet(tweet: Tweet) {
    this.saveSingleTweet(tweet);
    this.saveUser(tweet.user);
    if (tweet.quoted_status !== undefined) {
      this.saveTweet(tweet.quoted_status);
    }
    if (tweet.retweeted_status !== undefined) {
      this.saveTweet(tweet.retweeted_status);
    }
  }

  abstract saveSingleTweet(tweet: Tweet): void;
  abstract saveUser(user: User): void;
  abstract getLatestTweet(authorId: string): Tweet | null;
  abstract getLatestTweets(authorId: string, olderThanId: string, newerThan: Date): Tweet[];
  abstract getAuthorId(username: string): string | null;
}

function dateToEpoch(dateStr: string | Date) {
  const date = typeof dateStr === 'string' ? new Date(dateStr) : dateStr;
  return Math.floor(date.getTime() / 1000);
}

export class SqliteStore extends Store {
  private db: DB;

  constructor(path: string) {
    super();
    this.db = new DB(path);
    this.db.query(`
      CREATE TABLE IF NOT EXISTS tweets (
        id INTEGER PRIMARY KEY,
        author_id INTEGER NOT NULL,
        created_at INTEGER NOT NULL,
        tweet TEXT NOT NULL,
        fetched_at INTEGER NOT NULL
      );
    `);
    this.db.query(`
      CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY,
        username TEXT NOT NULL UNIQUE
      );
    `);
  }

  saveSingleTweet(tweet: Tweet) {
    this.db.query("INSERT OR IGNORE INTO tweets VALUES (?, ?, ?, ?, ?)", [
      BigInt(tweet.id_str),
      BigInt(tweet.user.id_str),
      dateToEpoch(tweet.created_at),
      JSON.stringify(tweet),
      dateToEpoch(new Date()),
    ]);
  }

  saveUser(user: User) {
    this.db.query("INSERT OR REPLACE INTO users VALUES (?, ?)", [
      BigInt(user.id_str),
      user.screen_name,
    ]);
  }

  getLatestTweet(authorId: string): Tweet | null {
    const result = this.db.query<[string]>(`
      SELECT tweet FROM tweets
      WHERE author_id = ?
      ORDER BY id DESC
      LIMIT 1
    `, [ BigInt(authorId) ]);

    if (result.length === 0) {
      return null;
    } else {
      const tweet = JSON.parse(result[0][0]);
      return TweetSchema.parse(tweet);
    }
  }

  getLatestTweets(authorId: string, olderThanId: string, newerThan: Date): Tweet[] {
    const result = this.db.query<[string]>(`
      SELECT tweet FROM tweets
      WHERE author_id = ?
        AND id < ?
        AND created_at > ?
      ORDER BY id DESC
    `, [ BigInt(authorId), BigInt(olderThanId), dateToEpoch(newerThan) ]);

    return result.map(([ tweetStr ]) => {
      const tweet = JSON.parse(tweetStr);
      return TweetSchema.parse(tweet);
    });
  }

  getAuthorId(username: string): string | null {
    const result = this.db.query<[BigInt]>(`
      SELECT id FROM users
      WHERE username = ?
      ORDER BY id DESC
      LIMIT 1
    `, [ username ]);


    if (result.length === 0) {
      return null;
    } else {
      return result[0][0].toString();
    }
  }
}