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

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