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

   1 'use strict';
   2
   3 const common = require('../../common');
   4 const Database = require('../base');
   5 const DBErrors = require('../errors');
   6 const svh = require('../schema-version-helper');
   7 const SQLite = require('better-sqlite3');
   8 const fs = require('fs');
   9 const path = require('path');
  10 const { performance } = require('perf_hooks');
  11 const uuid = require('uuid');
  12
  13 const _fileScope = common.fileScope(__filename);
  14
  15 const schemaVersionsSupported = {
  16   min: {
  17     major: 1,
  18     minor: 0,
  19     patch: 0,
  20   },
  21   max: {
  22     major: 1,
  23     minor: 0,
  24     patch: 0,
  25   },
  26 };
  27
  28 // max of signed int64 (2^63 - 1), should be enough
  29 // const EPOCH_FOREVER = BigInt('9223372036854775807');
  30
  31 class DatabaseSQLite extends Database {
  32   constructor(logger, options) {
  33     super(logger, options);
  34
  35     const connectionString = options.db.connectionString || 'sqlite://:memory:';
  36     const csDelim = '://';
  37     const dbFilename = connectionString.slice(connectionString.indexOf(csDelim) + csDelim.length);
  38
  39     const queryLogLevel = options.db.queryLogLevel;
  40
  41     const sqliteOptions = {
  42       ...(queryLogLevel && {
  43         // eslint-disable-next-line security/detect-object-injection
  44         verbose: (query) => this.logger[queryLogLevel](_fileScope('SQLite:verbose'), '', { query }),
  45       }),
  46     };
  47     this.db = new SQLite(dbFilename, sqliteOptions);
  48     this.schemaVersionsSupported = schemaVersionsSupported;
  49     this.changesSinceLastOptimize = BigInt(0);
  50     this.optimizeAfterChanges = options.db.connectionString.optimizeAfterChanges;
  51     this.db.pragma('foreign_keys = on'); // Enforce consistency.
  52     this.db.pragma('journal_mode = WAL'); // Be faster, expect local filesystem.
  53     this.db.defaultSafeIntegers(true); // This probably isn't necessary, but by using these BigInts we keep weird floats out of the query logs.
  54
  55     this._initTables();
  56     this._initStatements();
  57   }
  58
  59
  60   /**
  61    * SQLite cannot prepare its statements without a schema, ensure such exists.
  62    */
  63   _initTables() {
  64     const _scope = _fileScope('_initTables');
  65
  66     // Migrations rely upon this table, ensure it exists.
  67     const metaVersionTable = '_meta_schema_version';
  68     const tableExists = this.db.prepare('SELECT name FROM sqlite_master WHERE type=:type AND name=:name').pluck(true).bind({ type: 'table', name: metaVersionTable });
  69     let metaExists = tableExists.get();
  70     if (metaExists === undefined) {
  71       const fPath = path.join(__dirname, 'sql', 'schema', 'init.sql');
  72       // eslint-disable-next-line security/detect-non-literal-fs-filename
  73       const fSql = fs.readFileSync(fPath, { encoding: 'utf8' });
  74       this.db.exec(fSql);
  75       metaExists = tableExists.get();
  76       /* istanbul ignore if */
  77       if (metaExists === undefined) {
  78         throw new DBErrors.UnexpectedResult(`did not create ${metaVersionTable} table`);
  79       }
  80       this.logger.info(_scope, 'created schema version table', { metaVersionTable });
  81     }
  82
  83     // Apply migrations
  84     const currentSchema = this._currentSchema();
  85     const migrationsWanted = svh.unappliedSchemaVersions(__dirname, currentSchema, this.schemaVersionsSupported);
  86     this.logger.debug(_scope, 'schema migrations wanted', { migrationsWanted });
  87     migrationsWanted.forEach((v) => {
  88       const fPath = path.join(__dirname, 'sql', 'schema', v, 'apply.sql');
  89       // eslint-disable-next-line security/detect-non-literal-fs-filename
  90       const fSql = fs.readFileSync(fPath, { encoding: 'utf8' });
  91       this.logger.info(_scope, 'applying migration', { version: v });
  92       this.db.exec(fSql);
  93     });
  94   }
  95
  96
  97   _initStatements() {
  98     const _scope = _fileScope('_initStatements');
  99     const sqlDir = path.join(__dirname, 'sql');
 100     this.statement = {};
 101
 102     // Decorate the statement calls we use with timing and logging.
 103     const wrapFetch = (logName, statementName, fn) => {
 104       const _wrapScope = _fileScope(logName);
 105       return (...args) => {
 106         const startTimestampMs = performance.now();
 107         const rows = fn(...args);
 108         DatabaseSQLite._deOphidiate(rows);
 109         const elapsedTimeMs = performance.now() - startTimestampMs;
 110         this.logger.debug(_wrapScope, 'complete', { statementName, elapsedTimeMs });
 111         return rows;
 112       };
 113     };
 114     const wrapRun = (logName, statementName, fn) => {
 115       const _wrapScope = _fileScope(logName);
 116       return (...args) => {
 117         const startTimestampMs = performance.now();
 118         const result = fn(...args);
 119         const elapsedTimeMs = performance.now() - startTimestampMs;
 120         this.logger.debug(_wrapScope, 'complete', { ...result, statementName, elapsedTimeMs });
 121         result.duration = elapsedTimeMs;
 122         return result;
 123       };
 124     };
 125
 126     // eslint-disable-next-line security/detect-non-literal-fs-filename
 127     for (const f of fs.readdirSync(sqlDir)) {
 128       const fPath = path.join(sqlDir, f);
 129       const { name: fName, ext: fExt } = path.parse(f);
 130       // eslint-disable-next-line security/detect-non-literal-fs-filename
 131       const stat = fs.statSync(fPath);
 132       if (!stat.isFile()
 133       ||  fExt.toLowerCase() !== '.sql') {
 134         continue;
 135       }
 136       // eslint-disable-next-line security/detect-non-literal-fs-filename
 137       const fSql = fs.readFileSync(fPath, { encoding: 'utf8' });
 138       const statementName = Database._camelfy(fName.toLowerCase(), '-');
 139       let statement;
 140       try {
 141         statement = this.db.prepare(fSql);
 142       } catch (e) {
 143         /* istanbul ignore next */
 144         this.logger.error(_scope, 'failed to prepare statement', { error: e, file: f });
 145         /* istanbul ignore next */
 146         throw e;
 147       }
 148       // eslint-disable-next-line security/detect-object-injection
 149       this.statement[statementName] = statement;
 150       const { get: origGet, all: origAll, run: origRun } = statement;
 151       statement.get = wrapFetch('SQLite:get', statementName, origGet.bind(statement));
 152       statement.all = wrapFetch('SQLite:all', statementName, origAll.bind(statement));
 153       statement.run = wrapRun('SQLite:run', statementName, origRun.bind(statement));
 154     }
 155     this.statement._optimize = this.db.prepare('SELECT * FROM pragma_optimize(0x03)');
 156
 157     this.logger.debug(_scope, 'statements initialized', { statements: Object.keys(this.statement).length });
 158   }
 159
 160
 161   static _deOphidiate(rows) {
 162     const rowsIsArray = Array.isArray(rows);
 163     if (!rowsIsArray) {
 164       rows = [rows];
 165     }
 166     const exemplaryRow = rows[0];
 167     for (const prop in exemplaryRow) {
 168       const camel = Database._camelfy(prop);
 169       if (!(camel in exemplaryRow)) {
 170         for (const d of rows) {
 171           // eslint-disable-next-line security/detect-object-injection
 172           d[camel] = d[prop];
 173           // eslint-disable-next-line security/detect-object-injection
 174           delete d[prop];
 175         }
 176       }
 177     }
 178     return rowsIsArray ? rows : rows[0];
 179   }
 180
 181
 182   _currentSchema() {
 183     return this.db.prepare('SELECT major, minor, patch FROM _meta_schema_version ORDER BY major DESC, minor DESC, patch DESC LIMIT 1').get();
 184   }
 185
 186
 187   healthCheck() {
 188     const _scope = _fileScope('healthCheck');
 189     this.logger.debug(_scope, 'called', {});
 190     if (!this.db.open) {
 191       throw new DBErrors.UnexpectedResult('database is not open');
 192     }
 193     return { open: this.db.open };
 194   }
 195
 196
 197   _closeConnection() {
 198     this.db.close();
 199   }
 200
 201
 202   _optimize() {
 203     const _scope = _fileScope('_optimize');
 204
 205     if (this.optimizeAfterChanges
 206     &&  this.changesSinceLastOptimize >= this.optimizeAfterChanges) {
 207       const optimize = this.statement._optimize.all();
 208       this.logger.debug(_scope, 'optimize', { optimize });
 209       this.db.pragma('optimize');
 210       this.changesSinceLastOptimize = BigInt(0);
 211     }
 212   }
 213
 214
 215   _purgeTables(really) {
 216     if (really) {
 217       [
 218         'account',
 219         'post',
 220       ].map((table) => {
 221         const result = this.db.prepare(`DELETE FROM ${table}`).run();
 222         this.logger.debug(_fileScope('_purgeTables'), 'success', { table, result });
 223       });
 224     }
 225   }
 226
 227
 228   context(fn) {
 229     return fn(this.db);
 230   }
 231
 232
 233   transaction(dbCtx, fn) {
 234     dbCtx = dbCtx || this.db;
 235     return dbCtx.transaction(fn)();
 236   }
 237
 238
 239   static _dateFromEpoch(epoch) {
 240     return new Date(Number(epoch * 1000n));
 241   }
 242
 243
 244   static _accountToNative(account) {
 245     const result = {
 246       created: DatabaseSQLite._dateFromEpoch(account.created),
 247       accountId: Number(account.accountId),
 248       profile: Database._URLFromParts({
 249         protocol: account.profile_protocol,
 250         username: account.profile_username,
 251         password: account.profile_password,
 252         host: account.profile_host,
 253         pathname: account.profile_pathname,
 254         search: account.profile_search,
 255         hash: account.profile_hash,
 256       }),
 257     };
 258     return result;
 259   }
 260
 261
 262   accountGetByProfile(dbCtx, profile) {
 263     const _scope = _fileScope('accountGetByProfile');
 264     this.logger.debug(_scope, 'called', { profile });
 265
 266     try {
 267       const {
 268         protocol: profileProtocol,
 269         username: profileUsername,
 270         password: profilePassword,
 271         host: profileHost,
 272         pathname: profilePathname,
 273         search: profileSearch,
 274         hash: profileHash,
 275       } = new URL(profile);
 276       const account = this.statement.accountGetByProfile.get({
 277         profileProtocol,
 278         profileUsername,
 279         profilePassword,
 280         profileHost,
 281         profilePathname,
 282         profileSearch,
 283         profileHash,
 284       });
 285       return DatabaseSQLite._accountToNative(account);
 286     } catch (e) {
 287       this.logger.error(_scope, 'failed', { error: e, profile });
 288       throw e;
 289     }
 290   }
 291
 292
 293   accountGetByProfilePrefix(dbCtx, targetURL) {
 294     const _scope = _fileScope('accountGetByProfilePrefix');
 295     this.logger.debug(_scope, 'called', { targetURL });
 296
 297     try {
 298
 299     } catch (e) {
 300       this.logger.error(_scope, 'failed', { error: e, targetURL });
 301       throw e;
 302     }
 303   }
 304
 305   accountInsert(dbCtx, profile) {
 306     const _scope = _fileScope('authenticationUpsert');
 307     this.logger.debug(_scope, 'called', { profile });
 308
 309     try {
 310       const accountId = uuid.v4();
 311       const result = this.statement.accountInsert.run({ accountId, profile });
 312       if (result.changes != 1) {
 313         throw new DBErrors.UnexpectedResult('did not insert account');
 314       }
 315     } catch (e) {
 316       this.logger.error(_scope, 'failed', { error: e, profile })
 317       throw e;
 318     }
 319   }
 320
 321
 322
 323
 324 }
 325
 326 module.exports = DatabaseSQLite;