Something went wrong. Try again.
Something went wrong. Try again.
9.4 kB · 314 lines
JavaScript
at main
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315import { readFileSync } from 'node:fs';import Database from 'better-sqlite3';
const SUBS_PER_ACCOUNT_LIMIT = 5;const SECONDARY_FILTERS_LIMIT = 100;
const SCHEMA_FNAME = './schema.sql';
export class DB { #stmt_insert_account; #stmt_get_account; #stmt_delete_account; #stmt_insert_push_sub; #stmt_get_all_sub_dids; #stmt_get_push_subs; #stmt_get_push_sub; #stmt_update_push_sub; #stmt_delete_push_sub; #stmt_get_push_info; #stmt_set_role; #stmt_get_notify_account_globals; #stmt_set_notify_account_globals; #stmt_set_notification_filter; #stmt_get_notification_filter; #stmt_count_notification_filters; #stmt_rm_notification_filter;
#stmt_admin_add_secret; #stmt_admin_expire_secret; #stmt_admin_get_secrets; #stmt_admin_secret_accounts; #stmt_admin_nonsecret_accounts;
#transactionally; #db;
constructor(filename, init = false, handleExit = true) { const db = new Database(filename);
db.pragma('journal_mode = WAL'); db.pragma('foreign_keys = ON');
if (init) { const createSchema = readFileSync(SCHEMA_FNAME, 'utf8'); db.exec(createSchema); }
if (handleExit) { // probably a better way to do this 🤷♀️ process.on('exit', () => db.close()); process.on('SIGHUP', () => process.exit(128 + 1)); process.on('SIGINT', () => process.exit(128 + 2)); process.on('SIGTERM', () => process.exit(128 + 15)); }
this.#stmt_insert_account = db.prepare( `insert into accounts (did) values (?) on conflict do nothing`);
this.#stmt_get_account = db.prepare( `select a.first_seen, a.role, count(*) as total_subs from accounts a left outer join push_subs p on (p.account_did = a.did) where a.did = ? group by a.did`);
this.#stmt_delete_account = db.prepare( `delete from accounts where did = ?`);
this.#stmt_insert_push_sub = db.prepare( `insert into push_subs (account_did, session, subscription) values (?, ?, ?) on conflict do update set subscription = excluded.subscription`);
this.#stmt_get_all_sub_dids = db.prepare( `select distinct account_did from push_subs`);
this.#stmt_get_push_subs = db.prepare( `select session, subscription, (julianday(CURRENT_TIMESTAMP) - julianday(last_push)) * 24 * 60 * 60 as 'since_last_push' from push_subs where account_did = ?`);
this.#stmt_get_push_sub = db.prepare( `select session, subscription, (julianday(CURRENT_TIMESTAMP) - julianday(last_push)) * 24 * 60 * 60 as 'since_last_push' from push_subs where session = ?`);
this.#stmt_update_push_sub = db.prepare( `update push_subs set last_push = CURRENT_TIMESTAMP, total_pushes = total_pushes + 1 where session = ?`);
this.#stmt_delete_push_sub = db.prepare( `delete from push_subs where session = ?`);
this.#stmt_get_push_info = db.prepare( `select created, last_push, total_pushes from push_subs where account_did = ?`);
this.#stmt_set_role = db.prepare( `update accounts set role = :role, secret_password = :secret_password where did = :did and :secret_password in (select password from top_secret_passwords)`);
this.#stmt_get_notify_account_globals = db.prepare( `select notify_enabled, notify_self from accounts where did = :did`);
this.#stmt_set_notify_account_globals = db.prepare( `update accounts set notify_enabled = :notify_enabled, notify_self = :notify_self where did = :did`);
this.#stmt_set_notification_filter = db.prepare( `insert into notification_filters (account_did, selector, selection, notify) values (:did, :selector, :selection, :notify) on conflict do update set notify = excluded.notify`);
this.#stmt_get_notification_filter = db.prepare( `select notify from notification_filters where account_did = :did and selector = :selector and selection = :selection`);
this.#stmt_count_notification_filters = db.prepare( `select count(*) as n from notification_filters where account_did = :did`);
this.#stmt_rm_notification_filter = db.prepare( `delete from notification_filters where account_did = :did and selector = :selector and selection = :selection`);
this.#stmt_admin_add_secret = db.prepare( `insert into top_secret_passwords (password) values (?)`);
this.#stmt_admin_expire_secret = db.prepare( `update top_secret_passwords set expired = CURRENT_TIMESTAMP where expired is null and password = ?`);
this.#stmt_admin_get_secrets = db.prepare( `select password, unixepoch(added) * 1000 as 'added', unixepoch(expired) * 1000 as 'expired' from top_secret_passwords order by expired, added desc`);
this.#stmt_admin_secret_accounts = db.prepare( `select did, unixepoch(first_seen) * 1000 as 'first_seen', role, count(*) as 'active_subs', sum(p.total_pushes) as 'total_pushes', unixepoch(max(p.last_push)) * 1000 as 'last_push' from accounts left outer join push_subs p on (p.account_did = did) where secret_password = :password group by did order by first_seen desc`);
this.#stmt_admin_nonsecret_accounts = db.prepare( `select did, unixepoch(first_seen) * 1000 as 'first_seen', role, count(*) as 'active_subs', sum(p.total_pushes) as 'total_pushes', unixepoch(max(p.last_push)) * 1000 as 'last_push' from accounts left outer join push_subs p on (p.account_did = did) left outer join top_secret_passwords s on (s.password = secret_password) where s.password is null group by did order by first_seen desc`);
this.#transactionally = t => db.transaction(t).immediate(); }
addAccount(did) { this.#stmt_insert_account.run(did); }
getAccount(did) { return this.#stmt_get_account.get(did); }
addPushSub(did, session, sub) { this.#transactionally(() => { const res = this.#stmt_get_account.get(did); if (!res) { throw new Error(`Could not find account for ${did}`); } if (res.total_subs >= SUBS_PER_ACCOUNT_LIMIT) { throw new Error(`Too many subscriptions for ${did}`); } this.#stmt_insert_push_sub.run(did, session, sub); }); }
getSubscribedDids() { return this.#stmt_get_all_sub_dids.all().map(r => r.account_did); }
getSubsByDid(did) { return this.#stmt_get_push_subs.all(did); }
getSubBySession(session) { return this.#stmt_get_push_sub.get(session); }
updateLastPush(session) { this.#stmt_update_push_sub.run(session); }
deleteSub(session) { this.#stmt_delete_push_sub.run(session); }
setRole(params) { let res = this.#stmt_set_role.run(params); return res.changes > 0; }
getNotifyAccountGlobals(did) { return this.#stmt_get_notify_account_globals.get({ did }); }
setNotifyAccountGlobals(did, globals) { this.#transactionally(() => { const update = this.getNotifyAccountGlobals(did); if (globals.notify_enabled !== undefined) update.notify_enabled = +globals.notify_enabled; if (globals.notify_self !== undefined) update.notify_self = +globals.notify_self; update.did = did; this.#stmt_set_notify_account_globals.run(update); }); }
getNotificationFilter(did, selector, selection) { const res = this.#stmt_get_notification_filter.get({ did, selector, selection }); const dbNotify = res?.notify; if (dbNotify === 1) return true; else if (dbNotify === 0) return false; else return null; }
setNotificationFilter(did, selector, selection, notify) { if (notify === null) { this.#stmt_rm_notification_filter.run({ did, selector, selection }); } else { this.#transactionally(() => { const { n } = this.#stmt_count_notification_filters.get({ did }); if (n >= SECONDARY_FILTERS_LIMIT) { throw new Error('max filters set for account'); } let dbNotify = null; if (notify === true) dbNotify = 1; else if (notify === false) dbNotify = 0; this.#stmt_set_notification_filter.run({ did, selector, selection, notify: dbNotify }); }); } }
addTopSecret(secretPassword) { this.#stmt_admin_add_secret.run(secretPassword); }
expireTopSecret(secretPassword) { let res = this.#stmt_admin_expire_secret.run(secretPassword); return res.changes > 0; }
getSecrets() { return this.#stmt_admin_get_secrets.all(); }
getSecretAccounts(secretPassword) { return this.#stmt_admin_secret_accounts.all({ password: secretPassword }); }
getNonSecretAccounts() { return this.#stmt_admin_nonsecret_accounts.all(); }}