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

   1 'use strict';
   2
   3 const common = require('../../common');
   4 const Database = require('../abstract');
   5 const DBErrors = require('../errors');
   6 const { unappliedSchemaVersions } = require('../schema-version-helper');
   7 const SQLite = require('better-sqlite3');
   8 const fs = require('fs');
   9 const path = require('path');
  10 const uuid = require('uuid');
  11 const { performance } = require('perf_hooks');
  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.sqliteOptimizeAfterChanges || 0; // Default to no periodic optimization.
  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    * Boolean to 0/1 representation for SQLite params.
  62    * @param {Boolean} bool
  63    * @returns {Number}
  64    */
  65   static _booleanToNumeric(bool) {
  66     // eslint-disable-next-line security/detect-object-injection
  67     return {
  68       true: 1,
  69       false: 0,
  70     }[bool];
  71   }
  72
  73
  74   /**
  75    * SQLite cannot prepare its statements without a schema, ensure such exists.
  76    */
  77   _initTables() {
  78     const _scope = _fileScope('_initTables');
  79
  80     // Migrations rely upon this table, ensure it exists.
  81     const metaVersionTable = '_meta_schema_version';
  82     const tableExists = this.db.prepare('SELECT name FROM sqlite_master WHERE type=:type AND name=:name').pluck(true).bind({ type: 'table', name: metaVersionTable });
  83     let metaExists = tableExists.get();
  84     if (metaExists === undefined) {
  85       const fPath = path.join(__dirname, 'sql', 'schema', 'init.sql');
  86       // eslint-disable-next-line security/detect-non-literal-fs-filename
  87       const fSql = fs.readFileSync(fPath, { encoding: 'utf8' });
  88       this.db.exec(fSql);
  89       metaExists = tableExists.get();
  90       /* istanbul ignore if */
  91       if (metaExists === undefined) {
  92         throw new DBErrors.UnexpectedResult(`did not create ${metaVersionTable} table`);
  93       }
  94       this.logger.info(_scope, 'created schema version table', { metaVersionTable });
  95     }
  96
  97     // Apply migrations
  98     const currentSchema = this._currentSchema();
  99     const migrationsWanted = unappliedSchemaVersions(__dirname, currentSchema, this.schemaVersionsSupported);
 100     this.logger.debug(_scope, 'schema migrations wanted', { migrationsWanted });
 101     migrationsWanted.forEach((v) => {
 102       const fPath = path.join(__dirname, 'sql', 'schema', v, 'apply.sql');
 103       // eslint-disable-next-line security/detect-non-literal-fs-filename
 104       const fSql = fs.readFileSync(fPath, { encoding: 'utf8' });
 105       this.logger.info(_scope, 'applying migration', { version: v });
 106       this.db.exec(fSql);
 107     });
 108   }
 109
 110
 111   _initStatements() {
 112     const _scope = _fileScope('_initStatements');
 113     const sqlDir = path.join(__dirname, 'sql');
 114     this.statement = {};
 115
 116     // Decorate the statement calls we use with timing and logging.
 117     const wrapFetch = (logName, statementName, fn) => {
 118       const _wrapScope = _fileScope(logName);
 119       return (...args) => {
 120         const startTimestampMs = performance.now();
 121         const rows = fn(...args);
 122         DatabaseSQLite._deOphidiate(rows);
 123         const elapsedTimeMs = performance.now() - startTimestampMs;
 124         this.logger.debug(_wrapScope, 'complete', { statementName, elapsedTimeMs });
 125         return rows;
 126       };
 127     };
 128     const wrapRun = (logName, statementName, fn) => {
 129       const _wrapScope = _fileScope(logName);
 130       return (...args) => {
 131         const startTimestampMs = performance.now();
 132         const result = fn(...args);
 133         const elapsedTimeMs = performance.now() - startTimestampMs;
 134         this._updateChanges(result);
 135         this.logger.debug(_wrapScope, 'complete', { ...result, statementName, elapsedTimeMs });
 136         result.duration = elapsedTimeMs;
 137         return result;
 138       };
 139     };
 140
 141     // eslint-disable-next-line security/detect-non-literal-fs-filename
 142     for (const f of fs.readdirSync(sqlDir)) {
 143       const fPath = path.join(sqlDir, f);
 144       const { name: fName, ext: fExt } = path.parse(f);
 145       // eslint-disable-next-line security/detect-non-literal-fs-filename
 146       const stat = fs.statSync(fPath);
 147       if (!stat.isFile()
 148       ||  fExt.toLowerCase() !== '.sql') {
 149         continue;
 150       }
 151       // eslint-disable-next-line security/detect-non-literal-fs-filename
 152       const fSql = fs.readFileSync(fPath, { encoding: 'utf8' });
 153       const statementName = common.camelfy(fName.toLowerCase(), '-');
 154       let statement;
 155       try {
 156         statement = this.db.prepare(fSql);
 157       } catch (e) /* istanbul ignore next */ {
 158         this.logger.error(_scope, 'failed to prepare statement', { error: e, file: f });
 159         throw e;
 160       }
 161       // eslint-disable-next-line security/detect-object-injection
 162       this.statement[statementName] = statement;
 163       const { get: origGet, all: origAll, run: origRun } = statement;
 164       statement.get = wrapFetch('SQLite:get', statementName, origGet.bind(statement));
 165       statement.all = wrapFetch('SQLite:all', statementName, origAll.bind(statement));
 166       statement.run = wrapRun('SQLite:run', statementName, origRun.bind(statement));
 167     }
 168     this.statement._optimize = this.db.prepare('SELECT * FROM pragma_optimize(0xffff)');
 169
 170     this.logger.debug(_scope, 'statements initialized', { statements: Object.keys(this.statement).length });
 171   }
 172
 173
 174   static _deOphidiate(rows) {
 175     const rowsIsArray = Array.isArray(rows);
 176     if (!rowsIsArray) {
 177       rows = [rows];
 178     }
 179     const exemplaryRow = rows[0];
 180     for (const prop in exemplaryRow) {
 181       const camel = common.camelfy(prop);
 182       if (!(camel in exemplaryRow)) {
 183         for (const d of rows) {
 184           d[camel] = d[prop]; // eslint-disable-line security/detect-object-injection
 185           delete d[prop]; // eslint-disable-line security/detect-object-injection
 186         }
 187       }
 188     }
 189     return rowsIsArray ? rows : rows[0];
 190   }
 191
 192
 193   _currentSchema() {
 194     return this.db.prepare('SELECT major, minor, patch FROM _meta_schema_version ORDER BY major DESC, minor DESC, patch DESC LIMIT 1').get();
 195   }
 196
 197
 198   healthCheck() {
 199     const _scope = _fileScope('healthCheck');
 200     this.logger.debug(_scope, 'called', {});
 201     if (!this.db.open) {
 202       throw new DBErrors.UnexpectedResult('database is not open');
 203     }
 204     return { open: this.db.open };
 205   }
 206
 207
 208   _closeConnection() {
 209     this.db.close();
 210   }
 211
 212
 213   _optimize() {
 214     const _scope = _fileScope('_optimize');
 215
 216     const optimize = this.statement._optimize.all();
 217     this.logger.debug(_scope, 'optimize', { optimize, changes: this.changesSinceLastOptimize });
 218     this.db.pragma('optimize');
 219     this.changesSinceLastOptimize = BigInt(0);
 220   }
 221
 222
 223   _updateChanges(dbResult) {
 224     if (this.optimizeAfterChanges) {
 225       this.changesSinceLastOptimize += BigInt(dbResult.changes);
 226       if (this.changesSinceLastOptimize >= this.optimizeAfterChanges) {
 227         this._optimize();
 228       }
 229     }
 230   }
 231
 232
 233   _purgeTables(really) {
 234     if (really) {
 235       [
 236         'authentication',
 237         'profile',
 238         'token',
 239       ].map((table) => {
 240         const result = this.db.prepare(`DELETE FROM ${table}`).run();
 241         this.logger.debug(_fileScope('_purgeTables'), 'success', { table, result });
 242       });
 243     }
 244   }
 245
 246
 247   context(fn) {
 248     return fn(this.db);
 249   }
 250
 251
 252   transaction(dbCtx, fn) {
 253     dbCtx = dbCtx || this.db;
 254     return dbCtx.transaction(fn)();
 255   }
 256
 257
 258   static _almanacToNative(entry) {
 259     return {
 260       event: entry.event,
 261       date: new Date(Number(entry.epoch) * 1000),
 262     };
 263   }
 264
 265   almanacGetAll(dbCtx) { // eslint-disable-line no-unused-vars
 266     const _scope = _fileScope('almanacGetAll');
 267     this.logger.debug(_scope, 'called');
 268
 269     try {
 270       const entries = this.statement.almanacGetAll.all();
 271       return entries.map((entry) => DatabaseSQLite._almanacToNative(entry));
 272     } catch (e) {
 273       this.logger.error(_scope, 'failed', { error: e });
 274       throw e;
 275     }
 276   }
 277
 278
 279   static _authenticationToNative(authentication) {
 280     if (authentication) {
 281       authentication.created = new Date(Number(authentication.created) * 1000);
 282       authentication.lastAuthentication = new Date(Number(authentication.lastAuthentication) * 1000);
 283     }
 284     return authentication;
 285   }
 286
 287
 288   authenticationGet(dbCtx, identifier) {
 289     const _scope = _fileScope('authenticationGet');
 290     this.logger.debug(_scope, 'called', { identifier });
 291
 292     try {
 293       const authentication = this.statement.authenticationGet.get({ identifier });
 294       return DatabaseSQLite._authenticationToNative(authentication);
 295     } catch (e) {
 296       this.logger.error(_scope, 'failed', { error: e, identifier });
 297       throw e;
 298     }
 299   }
 300
 301
 302   authenticationSuccess(dbCtx, identifier) {
 303     const _scope = _fileScope('authenticationSuccess');
 304     this.logger.debug(_scope, 'called', { identifier });
 305
 306     try {
 307       const result = this.statement.authenticationSuccess.run({ identifier });
 308       if (result.changes != 1) {
 309         throw new DBErrors.UnexpectedResult('did not update authentication success');
 310       }
 311     } catch (e) {
 312       this.logger.error(_scope, 'failed', { error: e, identifier });
 313       throw e;
 314     }
 315   }
 316
 317
 318   authenticationUpsert(dbCtx, identifier, credential) {
 319     const _scope = _fileScope('authenticationUpsert');
 320     const scrubbedCredential = '*'.repeat((credential || '').length);
 321     this.logger.debug(_scope, 'called', { identifier, scrubbedCredential });
 322
 323     let result;
 324     try {
 325       result = this.statement.authenticationUpsert.run({ identifier, credential });
 326       if (result.changes != 1) {
 327         throw new DBErrors.UnexpectedResult('did not upsert authentication');
 328       }
 329     } catch (e) {
 330       this.logger.error(_scope, 'failed', { error: e, identifier, scrubbedCredential });
 331       throw e;
 332     }
 333   }
 334
 335
 336   profileIdentifierInsert(dbCtx, profile, identifier) {
 337     const _scope = _fileScope('profileIdentifierInsert');
 338     this.logger.debug(_scope, 'called', { profile, identifier });
 339
 340     try {
 341       const result = this.statement.profileIdentifierInsert.run({ profile, identifier });
 342       if (result.changes != 1) {
 343         throw new DBErrors.UnexpectedResult('did not insert profile identifier relationship');
 344       }
 345     } catch (e) {
 346       this.logger.error(_scope, 'failed', { error: e, profile, identifier });
 347       throw e;
 348     }
 349   }
 350
 351
 352   profileIsValid(dbCtx, profile) {
 353     const _scope = _fileScope('profileIsValid');
 354     this.logger.debug(_scope, 'called', { profile });
 355
 356     try {
 357       const profileResponse = this.statement.profileGet.get({ profile });
 358       return !!profileResponse;
 359     } catch (e) {
 360       this.logger.error(_scope, 'failed', { error: e, profile });
 361       throw e;
 362     }
 363   }
 364
 365
 366   profileScopeInsert(dbCtx, profile, scope) {
 367     const _scope = _fileScope('profileScopeInsert');
 368     this.logger.debug(_scope, 'called', { profile, scope });
 369
 370     try {
 371       const result = this.statement.profileScopeInsert.run({ profile, scope });
 372       // Duplicate inserts get ignored
 373       if (result.changes != 1 && result.changes != 0) {
 374         throw new DBErrors.UnexpectedResult('did not insert profile scope');
 375       }
 376     } catch (e) {
 377       this.logger.error(_scope, 'failed', { error: e, profile, scope });
 378       throw e;
 379     }
 380   }
 381
 382
 383   profileScopesSetAll(dbCtx, profile, scopes) {
 384     const _scope = _fileScope('profileScopesSetAll');
 385     this.logger.debug(_scope, 'called', { profile, scopes });
 386
 387     try {
 388       this.transaction(dbCtx, () => {
 389         this.statement.profileScopesClear.run({ profile });
 390         if (scopes.length) {
 391           scopes.forEach((scope) => {
 392             this.statement.profileScopeInsert.run({ profile, scope });
 393           });
 394         }
 395       }); // transaction
 396     } catch (e) {
 397       this.logger.error(_scope, 'failed', { error: e, profile, scopes });
 398       throw e;
 399     }
 400   }
 401
 402
 403   profilesScopesByIdentifier(dbCtx, identifier) {
 404     const _scope = _fileScope('profilesScopesByIdentifier');
 405     this.logger.debug(_scope, 'called', { identifier });
 406
 407     try {
 408       const profileScopesRows = this.statement.profilesScopesByIdentifier.all({ identifier });
 409       return Database._profilesScopesBuilder(profileScopesRows);
 410     } catch (e) {
 411       this.logger.error(_scope, 'failed', { error: e, identifier });
 412       throw e;
 413     }
 414   }
 415
 416
 417   redeemCode(dbCtx, { codeId, created, isToken, clientId, profile, identifier, scopes, lifespanSeconds, refreshLifespanSeconds, profileData }) {
 418     const _scope = _fileScope('redeemCode');
 419     this.logger.debug(_scope, 'called', { codeId, created, isToken, clientId, profile, identifier, scopes, lifespanSeconds, refreshLifespanSeconds, profileData });
 420
 421     let result, ret = false;
 422     try {
 423       if (profileData) {
 424         profileData = JSON.stringify(profileData);
 425       }
 426       this.transaction(dbCtx, () => {
 427         result = this.statement.redeemCode.get({ codeId, created: common.dateToEpoch(created), isToken: DatabaseSQLite._booleanToNumeric(isToken), clientId, profile, identifier, lifespanSeconds, refreshLifespanSeconds, profileData });
 428         if (!result) {
 429           this.logger.error(_scope, 'failed', { result });
 430           throw new DBErrors.UnexpectedResult('did not redeem code');
 431         }
 432         // Abort and return false if redemption resulted in revocation.
 433         if (result.isRevoked) {
 434           return;
 435         }
 436
 437         // Ensure there are entries for all scopes, and associate with token.
 438         scopes.forEach((scope) => {
 439           this.statement.scopeInsert.run({ scope });
 440           this.statement.tokenScopeSet.run({ codeId, scope });
 441         });
 442         ret = true;
 443       }); // tx
 444     } catch (e) {
 445       this.logger.error(_scope, 'failed', { error: e, codeId, isToken, clientId, profile, identifier, scopes, lifespanSeconds, refreshLifespanSeconds, profileData });
 446       throw e;
 447     }
 448     return ret;
 449   }
 450
 451
 452   static _refreshCodeResponseToNative(refreshResponse) {
 453     if (refreshResponse) {
 454       ['expires', 'refreshExpires'].forEach((epochField) => {
 455         if (refreshResponse[epochField]) { // eslint-disable-line security/detect-object-injection
 456           refreshResponse[epochField] = new Date(Number(refreshResponse[epochField]) * 1000); // eslint-disable-line security/detect-object-injection
 457         }
 458       });
 459     }
 460     return refreshResponse;
 461   }
 462
 463
 464   refreshCode(dbCtx, codeId, refreshed, removeScopes) {
 465     const _scope = _fileScope('refreshCode');
 466     this.logger.debug(_scope, 'called', { codeId, refreshed, removeScopes });
 467
 468     try {
 469       return this.transaction(dbCtx, () => {
 470         const refreshResponse = this.statement.refreshCode.get({ codeId, refreshed: common.dateToEpoch(refreshed) });
 471         if (refreshResponse) {
 472           removeScopes.forEach((scope) => {
 473             const result = this.statement.tokenScopeRemove.run({ codeId, scope });
 474             if (result?.changes != 1) {
 475               this.logger.error(_scope, 'failed to remove token scope', { codeId, scope });
 476               throw new DBErrors.UnexpectedResult('did not remove scope from token');
 477             }
 478           });
 479           if (removeScopes.length) {
 480             refreshResponse.scopes = (this.statement.tokenScopesGetByCodeId.all({ codeId }) || [])
 481               .map((row) => row.scope);
 482           }
 483         } else {
 484           this.logger.debug(_scope, 'did not refresh token', {});
 485         }
 486         return DatabaseSQLite._refreshCodeResponseToNative(refreshResponse);
 487       }); // tx
 488     } catch (e) {
 489       this.logger.error(_scope, 'failed', { error: e, codeId, refreshed });
 490       throw e;
 491     }
 492   }
 493
 494
 495   static _resourceToNative(resource) {
 496     if (resource) {
 497       resource.created = new Date(Number(resource.created) * 1000);
 498     }
 499     return resource;
 500   }
 501
 502
 503   resourceGet(dbCtx, resourceId) {
 504     const _scope = _fileScope('resourceGet');
 505     this.logger.debug(_scope, 'called', { resourceId });
 506
 507     try {
 508       const resource = this.statement.resourceGet.get({ resourceId });
 509       return DatabaseSQLite._resourceToNative(resource);
 510     } catch (e) {
 511       this.logger.error(_scope, 'failed', { error: e, resourceId });
 512       throw e;
 513     }
 514   }
 515
 516
 517   resourceUpsert(dbCtx, resourceId, secret, description) {
 518     const _scope = _fileScope('resourceUpsert');
 519     this.logger.debug(_scope, 'called', { resourceId });
 520
 521     try {
 522       if (!resourceId) {
 523         resourceId = uuid.v4();
 524       }
 525       const result = this.statement.resourceUpsert.run({ resourceId, secret, description });
 526       if (result.changes != 1) {
 527         throw new DBErrors.UnexpectedResult('did not upsert resource');
 528       }
 529       const resource = this.statement.resourceGet.get({ resourceId });
 530       return DatabaseSQLite._resourceToNative(resource);
 531     } catch (e) {
 532       this.logger.error(_scope, 'failed', { error: e, resourceId, secret, description });
 533       throw e;
 534     }
 535   }
 536
 537
 538   scopeCleanup(dbCtx, atLeastMsSinceLast) {
 539     const _scope = _fileScope('scopeCleanup');
 540     this.logger.debug(_scope, 'called', { atLeastMsSinceLast });
 541
 542     const almanacEvent = 'scopeCleanup';
 543     try {
 544       return this.db.transaction(() => {
 545
 546         // Check that enough time has passed since last cleanup
 547         const nowEpoch = BigInt(common.dateToEpoch());
 548         const { epoch: lastCleanupEpoch } = this.statement.almanacGet.get({ event: almanacEvent }) || { epoch: 0n };
 549         const elapsedMs = (nowEpoch - lastCleanupEpoch) * 1000n;
 550         if (elapsedMs < atLeastMsSinceLast) {
 551           this.logger.debug(_scope, 'skipping token cleanup, too soon', { lastCleanupEpoch, elapsedMs, atLeastMsSinceLast });
 552           return;
 553         }
 554
 555         // Do the cleanup
 556         const { changes: scopesRemoved } = this.statement.scopeCleanup.run();
 557
 558         // Update the last cleanup time
 559         const result = this.statement.almanacUpsert.run({ event: almanacEvent, epoch: nowEpoch });
 560         if (result.changes != 1) {
 561           throw new DBErrors.UnexpectedResult('did not update almanac');
 562         }
 563
 564         this.logger.debug(_scope, 'finished', { scopesRemoved, atLeastMsSinceLast });
 565         return scopesRemoved;
 566       }).exclusive();
 567     } catch (e) {
 568       this.logger.error(_scope, 'failed', { error: e, atLeastMsSinceLast });
 569       throw e;
 570     }
 571   }
 572
 573
 574   scopeDelete(dbCtx, scope) {
 575     const _scope = _fileScope('scopeDelete');
 576     this.logger.debug(_scope, 'called', { scope });
 577
 578     try {
 579       return this.transaction(dbCtx, () => {
 580         const { inUse } = this.statement.scopeInUse.get({ scope });
 581         if (inUse) {
 582           this.logger.debug(_scope, 'not deleted, in use', { scope });
 583           return false;
 584         }
 585         const result = this.statement.scopeDelete.run({ scope });
 586         if (result.changes == 0) {
 587           this.logger.debug(_scope, 'no such scope', { scope });
 588         } else {
 589           this.logger.debug(_scope, 'deleted', { scope });
 590         }
 591         return true;
 592       });
 593     } catch (e) {
 594       this.logger.error(_scope, 'failed', { error: e, scope });
 595       throw e;
 596     }
 597   }
 598
 599
 600   scopeUpsert(dbCtx, scope, application, description, manuallyAdded) {
 601     const _scope = _fileScope('scopeUpsert');
 602     this.logger.debug(_scope, 'called', { scope, application, description, manuallyAdded });
 603
 604     try {
 605       const result = this.statement.scopeUpsert.run({ scope, application, description, manuallyAdded: DatabaseSQLite._booleanToNumeric(manuallyAdded) });
 606       if (result.changes != 1) {
 607         throw new DBErrors.UnexpectedResult('did not upsert scope');
 608       }
 609     } catch (e) {
 610       this.logger.error(_scope, 'failed', { error: e, scope, application, description, manuallyAdded });
 611       throw e;
 612     }
 613   }
 614
 615
 616   tokenCleanup(dbCtx, codeLifespanSeconds, atLeastMsSinceLast) {
 617     const _scope = _fileScope('tokenCleanup');
 618     this.logger.debug(_scope, 'called', { codeLifespanSeconds, atLeastMsSinceLast });
 619
 620     const almanacEvent = 'tokenCleanup';
 621     try {
 622       return this.db.transaction(() => {
 623
 624         // Check that enough time has passed since last cleanup
 625         const nowEpoch = BigInt(common.dateToEpoch());
 626         const { epoch: lastCleanupEpoch } = this.statement.almanacGet.get({ event: almanacEvent }) || { epoch: 0n };
 627         const elapsedMs = (nowEpoch - lastCleanupEpoch) * 1000n;
 628         if (elapsedMs < atLeastMsSinceLast) {
 629           this.logger.debug(_scope, 'skipping token cleanup, too soon', { lastCleanupEpoch, elapsedMs, atLeastMsSinceLast });
 630           return;
 631         }
 632
 633         // Do the cleanup
 634         const { changes: tokensRemoved } = this.statement.tokenCleanup.run({ codeLifespanSeconds });
 635
 636         // Update the last cleanup time
 637         const result = this.statement.almanacUpsert.run({ event: almanacEvent, epoch: nowEpoch });
 638         if (result.changes != 1) {
 639           throw new DBErrors.UnexpectedResult('did not update almanac');
 640         }
 641
 642         this.logger.debug(_scope, 'finished', { tokensRemoved, codeLifespanSeconds, atLeastMsSinceLast });
 643         return tokensRemoved;
 644       }).exclusive();
 645     } catch (e) {
 646       this.logger.error(_scope, 'failed', { error: e, codeLifespanSeconds, atLeastMsSinceLast });
 647       throw e;
 648     }
 649   }
 650
 651
 652   static _tokenToNative(token) {
 653     if (token) {
 654       token.created = new Date(Number(token.created) * 1000);
 655       if (token.expires || token.expires == 0) {
 656         token.expires = new Date(Number(token.expires) * 1000);
 657       }
 658       if (token.refreshExpires || token.refreshExpires == 0) {
 659         token.refreshExpires = new Date(Number(token.refreshExpires) * 1000);
 660       }
 661       if (token.refreshed || token.refreshed == 0) {
 662         token.refreshed = new Date(Number(token.refreshed) * 1000);
 663       }
 664       token.isRevoked = !!token.isRevoked;
 665       token.isToken = !!token.isToken;
 666       if (token.profileData) {
 667         token.profileData = JSON.parse(token.profileData);
 668       }
 669     }
 670     return token;
 671   }
 672
 673
 674   tokenGetByCodeId(dbCtx, codeId) {
 675     const _scope = _fileScope('tokenGetByCodeId');
 676     this.logger.debug(_scope, 'called', { codeId });
 677
 678     try {
 679       return this.transaction(dbCtx, () => {
 680         const token = this.statement.tokenGetByCodeId.get({ codeId });
 681         token.scopes = (this.statement.tokenScopesGetByCodeId.all({ codeId }) || [])
 682           .map((row) => row.scope);
 683         return DatabaseSQLite._tokenToNative(token);
 684       });
 685     } catch (e) {
 686       this.logger.error(_scope, 'failed', { error: e, codeId });
 687       throw e;
 688     }
 689   }
 690
 691
 692   tokenRefreshRevokeByCodeId(dbCtx, codeId) {
 693     const _scope = _fileScope('tokenRefreshRevokeByCodeId');
 694     this.logger.debug(_scope, 'called', { codeId });
 695
 696     try {
 697       const result = this.statement.tokenRefreshRevokeByCodeId.run({ codeId });
 698       if (result.changes != 1) {
 699         throw new DBErrors.UnexpectedResult('did not revoke refresh');
 700       }
 701     } catch (e) {
 702       this.logger.error(_scope, 'failed', { error: e, codeId });
 703       throw e;
 704     }
 705   }
 706
 707
 708   tokenRevokeByCodeId(dbCtx, codeId) {
 709     const _scope = _fileScope('tokenRevokeByCodeId');
 710     this.logger.debug(_scope, 'called', { codeId });
 711
 712     try {
 713       const result = this.statement.tokenRevokeByCodeId.run({ codeId });
 714       if (result.changes != 1) {
 715         throw new DBErrors.UnexpectedResult('did not revoke token');
 716       }
 717     } catch (e) {
 718       this.logger.error(_scope, 'failed', { error: e, codeId });
 719       throw e;
 720     }
 721   }
 722
 723
 724   tokensGetByIdentifier(dbCtx, identifier) {
 725     const _scope = _fileScope('tokensGetByIdentifier');
 726     this.logger.debug(_scope, 'called', { identifier });
 727
 728     try {
 729       const tokens = this.statement.tokensGetByIdentifier.all({ identifier });
 730       return tokens.map(DatabaseSQLite._tokenToNative);
 731     } catch (e) {
 732       this.logger.error(_scope, 'failed', { error: e, identifier });
 733       throw e;
 734     }
 735   }
 736
 737 }
 738
 739 module.exports = DatabaseSQLite;