git.squeep.com Git - urlittler/blob - src/db/sqlite/index.js

   1 'use strict';
   2
   3 const SQLite = require('better-sqlite3');
   4 // XXX: const NodeCleanup = require('node-cleanup');
   5 const BaseDatabase = require('../base');
   6 const common = require('../../common');
   7 const DBErrors = require('../errors');
   8
   9 const _fileScope = common.fileScope(__filename);
  10
  11 const defaultOptions = {
  12   connectionString: '',
  13   optimizeAfterChanges: 1000,
  14   queryLogLevel: 'debug',
  15 };
  16
  17 const EPOCH_NOW = '(strftime(\'%s\', \'now\'))';
  18
  19 class SQLiteDatabase extends BaseDatabase {
  20   constructor(logger, options) {
  21     const _scope = _fileScope('constructor');
  22
  23     super(logger);
  24     common.setOptions(this, defaultOptions, options);
  25
  26     this.dbFilename = this.connectionString.slice('sqlite://'.length) || ':memory:';
  27
  28     this.logger.debug(_scope, 'connecting', { dbFilename: this.dbFilename });
  29
  30     this.db = new SQLite(this.dbFilename, {
  31       verbose: (query) => this.queryLogLevel && this.logger[this.queryLogLevel](_fileScope('statement'), 'executing', { query }),
  32     });
  33
  34     this.changesSinceLastOptimize = BigInt(0);
  35
  36     /*
  37     NodeCleanup(() => {
  38       this._optimize();
  39       this._closeConnection();
  40     });
  41     */
  42
  43     this.db.pragma('foreign_keys = on');
  44     this.db.pragma('journal_mode = WAL');
  45     this.db.defaultSafeIntegers(true);
  46
  47     this._initTables();
  48     this._initStatements();
  49   }
  50
  51   _initTables() {
  52     const _scope = _fileScope('_initTables');
  53     const tableExists = this.db.prepare('SELECT name FROM sqlite_master WHERE type=\'table\' AND name=:tableName').pluck(true);
  54
  55     const tables = [
  56       {
  57         name: 'auth',
  58         statements: [
  59           `CREATE TABLE IF NOT EXISTS auth (
  60             id TEXT NOT NULL PRIMARY KEY,
  61             secret TEXT NOT NULL,
  62             password TEXT
  63           )`,
  64           `INSERT INTO auth (id, secret, password)
  65             VALUES
  66             ('foo', 'secret', 'quux')`,
  67         ],
  68       },
  69       {
  70         name: 'link',
  71         statements: [
  72           `CREATE TABLE IF NOT EXISTS link (
  73             id TEXT NOT NULL PRIMARY KEY,
  74             url TEXT NOT NULL UNIQUE,
  75             created INTEGER NOT NULL DEFAULT ${EPOCH_NOW},
  76             last_access INTEGER NOT NULL DEFAULT 0,
  77             accesses INTEGER NOT NULL DEFAULT 0,
  78             expires INTEGER,
  79             auth_token TEXT
  80           )`,
  81           'CREATE INDEX IF NOT EXISTS link_url_idx ON link(url)',
  82         ],
  83       },
  84     ];
  85
  86     tables.forEach((t) => {
  87       const table = tableExists.get({ tableName: t.name });
  88       if (table === undefined) {
  89         t.statements.forEach((s) => {
  90           this.logger.info(_scope, 'creating table', { tableName: t.name });
  91           this.db.prepare(s).run();
  92         });
  93       }
  94     });
  95   }
  96
  97   _initStatements() {
  98     this.statement = {
  99       getAuthById: this.db.prepare('SELECT * FROM auth WHERE id = :id'),
 100       getLinkById: this.db.prepare('SELECT * FROM link WHERE id = :id'),
 101       getLinkByUrl: this.db.prepare('SELECT * FROM link WHERE url = :url'),
 102       insertLink: this.db.prepare('INSERT INTO link (id, url, auth_token) VALUES (:id, :url, :authToken)'),
 103       updateLink: this.db.prepare('UPDATE link SET url = :url WHERE id = :id'),
 104       incrementLinkAccess: this.db.prepare(`UPDATE link SET accesses = accesses + 1, last_access = ${EPOCH_NOW} WHERE id = :id`),
 105       expireLink: this.db.prepare('UPDATE link SET expires = :expires WHERE id = :id'),
 106       linkGetAll: this.db.prepare('SELECT * FROM link'),
 107     };
 108   }
 109
 110   _optimize() {
 111     const _scope = _fileScope('_optimize');
 112     this.logger.debug(_scope, 'called', {});
 113     const optimizations = this.db.prepare('SELECT * FROM pragma_optimize(0x03)').all();
 114     this.logger.debug(_scope, 'pragma preview', { optimizations });
 115     this.db.pragma('optimize');
 116     this.changesSinceLastOptimize = BigInt(0);
 117   }
 118
 119   _maybeOptimize() {
 120     if (this.changesSinceLastOptimize >= this.optimizeAfterChanges) {
 121       this._optimize();
 122     }
 123   }
 124
 125   _sqliteInfo(info) {
 126     if (info.changes) {
 127       this.changesSinceLastOptimize += BigInt(info.changes);
 128       this._maybeOptimize();
 129     }
 130     return {
 131       changes: Number(info.changes),
 132       lastInsertRowid: Number(info.lastInsertRowid),
 133     };
 134   }
 135
 136   static _deOphidiate(row) {
 137     return row && Object.keys(row).reduce((snakelessRow, k) => Object.assign(snakelessRow, {
 138       // eslint-disable-next-line security/detect-object-injection
 139       [BaseDatabase._camelfy(k)]: row[k],
 140     }), {});
 141   }
 142
 143
 144   async context(fn) {
 145     const dbCtx = this.db;
 146     return await fn(dbCtx);
 147   }
 148
 149   async transaction(dbCtx, fn, ...rest) {
 150     dbCtx = dbCtx || this.db;
 151     return await dbCtx.transaction(async (...args) => await fn(...args))(dbCtx, ...rest);
 152   }
 153
 154   async getAuthById(dbCtx, id) {
 155     const _scope = _fileScope('getAuthById');
 156     this.logger.debug(_scope, 'called', { id });
 157
 158     let auth;
 159     auth = this.statement.getAuthById.get({ id });
 160     auth = SQLiteDatabase._deOphidiate(auth);
 161
 162     this.logger.debug(_scope, 'get', { auth });
 163     return auth;
 164   }
 165
 166   async insertLink(dbCtx, id, url, authToken) {
 167     const _scope = _fileScope('insertLink');
 168     this.logger.debug(_scope, 'called', { id, url });
 169
 170     let info;
 171     try {
 172       info = this.statement.insertLink.run({ id, url, authToken });
 173     } catch (e) {
 174       switch (e.code) {
 175         case 'SQLITE_CONSTRAINT_UNIQUE':
 176         case 'SQLITE_CONSTRAINT_PRIMARYKEY': {
 177           this.logger.debug(_scope, 'updating existing id', { id, url });
 178           info = this.statement.updateLink.run({ id, url });
 179           break;
 180         }
 181
 182         default: {
 183           this.logger.error(_scope, 'failed to insert link', { error: e, id, url });
 184           throw e;
 185         }
 186       }
 187     }
 188     this.logger.debug(_scope, 'run', { info });
 189     if (info.changes != 1) {
 190       this.logger.error(_scope, 'failed to insert link', { id, url, info });
 191       throw new DBErrors.UnexpectedResult();
 192     }
 193
 194     return this._sqliteInfo(info);
 195   }
 196
 197   static _linkToNative(link) {
 198     return link && {
 199       id: link.id,
 200       url: link.url,
 201       created: Number(link.created),
 202       lastAccess: Number(link.lastAccess),
 203       accesses: Number(link.accesses),
 204       expires: ('expires' in link) ? Number(link.expires) : undefined,
 205       authToken: link.authToken,
 206     };
 207   }
 208
 209   async getLinkById(dbCtx, id) {
 210     const _scope = _fileScope('getLinkById');
 211     this.logger.debug(_scope, 'called', { id });
 212
 213     let link = this.statement.getLinkById.get({ id });
 214     link = SQLiteDatabase._deOphidiate(link);
 215
 216     this.logger.debug(_scope, 'get', { link });
 217     return SQLiteDatabase._linkToNative(link);
 218   }
 219
 220   async getLinkByUrl(dbCtx, url) {
 221     const _scope = _fileScope('getLinkByUrl');
 222     this.logger.debug(_scope, 'called', { url });
 223
 224     let link = this.statement.getLinkByUrl.get({ url });
 225     link = SQLiteDatabase._deOphidiate(link);
 226
 227     this.logger.debug(_scope, 'get', { link });
 228     return SQLiteDatabase._linkToNative(link);
 229   }
 230
 231   async accessLink(dbCtx, id) {
 232     const _scope = _fileScope('accessLink');
 233     this.logger.debug(_scope, 'called', { id });
 234
 235     let link = this.statement.getLinkById.get({ id });
 236     link = SQLiteDatabase._deOphidiate(link);
 237
 238     this.logger.debug(_scope, 'get', { id, link });
 239
 240     if (link) {
 241       const info = this.statement.incrementLinkAccess.run({ id });
 242       this.logger.debug(_scope, 'increment', { id, info });
 243       if (info.changes != 1) {
 244         this.logger.error(_scope, 'failed to increment link access', { id, link, info });
 245         throw new DBErrors.UnexpectedResult();
 246       }
 247     }
 248     return SQLiteDatabase._linkToNative(link);
 249   }
 250
 251   async expireLink(dbCtx, id, expires) {
 252     const _scope = _fileScope('expireLink');
 253     this.logger.debug(_scope, 'called', { id });
 254
 255     const info = this.statement.expireLink.run({ id, expires });
 256     if (info.changes != 1) {
 257       throw new DBErrors.UnexpectedResult();
 258     }
 259     return this._sqliteInfo(info);
 260   }
 261
 262   async updateLink(dbCtx, id, url) {
 263     const _scope = _fileScope('updateLink');
 264     this.logger.debug(_scope, 'called', { id, url });
 265
 266     const info = this.statement.updateLink.run({ id, url });
 267     if (info.changes != 1) {
 268       throw new DBErrors.UnexpectedResult();
 269     }
 270     return this._sqliteInfo(info);
 271   }
 272
 273   // eslint-disable-next-line no-unused-vars
 274   async getAllLinks(dbCtx) {
 275     const _scope = _fileScope('getAllLinks');
 276     this.logger.debug(_scope, 'called', { });
 277
 278     let links;
 279     links = this.statement.linkGetAll.all({});
 280     links = links.map((l) => SQLiteDatabase._deOphidiate(l));
 281     this.logger.debug(_scope, 'get', { links });
 282     links = links.map((l) => SQLiteDatabase._linkToNative(l));
 283     return links;
 284   }
 285
 286 }
 287
 288 module.exports = SQLiteDatabase;