1 import Database from 'better-sqlite3';
2 import { v4 as uuidv4 } from 'uuid';
7 const LOGIN_CODE_TIMEOUT = 1000 * 60 * 10;
9 export const writer = new Database(DB, {
12 export const reader = new Database(DB, {
17 export const query = {
19 createAccount: writer.prepare('insert into accounts (id, email, signup_date) values (?, ?, ?)'),
20 createLoginCode: writer.prepare('update accounts set login_code = ?, login_code_expires = ? where id = ?'),
21 isValidLoginCode: reader.prepare('select * from accounts where id = ? and login_code = ?'),
22 getAccountByEmail: reader.prepare('select * from accounts where email = ?'),
23 addFeed: writer.prepare('insert into feedlist (id, title, link, account_id) values (?, ?, ?, ?)'),
24 getFeed: reader.prepare('select * from feedlist where account_id = ? and id = ?'),
25 getFeedList: reader.prepare('select * from feedlist where account_id = ?'),
26 ingest_getFeedList: reader.prepare('select * from feedlist'),
27 getUnreadCountForAll: reader.prepare('select count(fi.id) as unread, feed_id, fl.title from feed_items fi join feedlist fl on fl.id = feed_id where fi.read_at = 0 and fl.account_id = ? group by feed_id;'),
28 readAllItems: writer.prepare(`update feed_items set read_at = datetime('now') where feed_id = ?`),
30 addFeed: (title: string, link: string, account_id: string) => {
32 query._.addFeed.run(id, title, link, account_id);
37 readAllItems: (feed_id: string) => {
38 return query._.readAllItems.run(feed_id);
40 isFeedOwnedBy: (account_id: string, feed_id: string): boolean => {
41 const res = query._.getFeed.get(account_id, feed_id);
42 console.log('OUTPUT', res);
45 getFeedList: (account_id: string) => {
46 return query._.getFeedList.all(account_id);
48 getUnreadCountForAll: (account_id: string) => {
49 return query._.getUnreadCountForAll.all(account_id);
51 getFeedInfo: reader.prepare('select * from feedlist where id = ?'),
52 getFeedItemInfo: reader.prepare('select * from feed_items where id = ? and feed_id = ?'),
53 addFeedItem: writer.prepare('insert into feed_items (id, feed_id, guid, title, link, pub_date, content) values (?, ?, ?, ?, ?, ?, ?)'),
54 getFeedsFor: reader.prepare('select id, feed_id, guid, title, link, pub_date,read_at from feed_items where feed_id = ? and pub_date > ? order by pub_date desc'),
55 readItem: writer.prepare('update feed_items set read_at = ? where id = ? and read_at = 0'),
56 _deleteFeedItems: writer.prepare(`delete from feed_items where feed_id = ?`),
57 _deleteFeed: writer.prepare(`delete from feedlist where id = ?`),
59 run: (feed_id: string) => {
60 query._deleteFeedItems.run(feed_id);
61 query._deleteFeed.run(feed_id)
64 createAccount: (email: string) => {
66 const signup_date = Date.now();
68 query._.createAccount.run(id, email, signup_date);
72 signup_date: signup_date,
76 createLoginCode: (email: string, code: string) => {
78 const expires = Date.now() + LOGIN_CODE_TIMEOUT;
81 acct = query.getAccountByEmail(email);
84 acct = query.createAccount(email);
88 const output = query._.createLoginCode.run(code, expires, acct.id);
93 login_code_expires: expires
100 validateLoginCode: (id: string, code: string): boolean => {
101 const validCode = query._.isValidLoginCode.get(id, code);
102 // code is valid for 10 minues
103 const now = Date.now();
104 return (validCode && now <= validCode.login_code_expires);
106 getAccountByEmail: (email: string) => {
107 const account = query._.getAccountByEmail.get(email);
110 throw new Error('Invalid Account');