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   static _almanacErrorThrow() {
 271     throw new DBErrors.UnexpectedResult('did not update almanac');
 272   }
 273
 274
 275   almanacGetAll(dbCtx) { // eslint-disable-line no-unused-vars
 276     const _scope = _fileScope('almanacGetAll');
 277     this.logger.debug(_scope, 'called');
 278
 279     try {
 280       const entries = this.statement.almanacGetAll.all();
 281       return entries.map((entry) => DatabaseSQLite._almanacToNative(entry));
 282     } catch (e) {
 283       this.logger.error(_scope, 'failed', { error: e });
 284       throw e;
 285     }
 286   }
 287
 288
 289   almanacUpsert(dbCtx, event, date) {
 290     const _scope = _fileScope('almanacUpsert');
 291     this.logger.debug(_scope, 'called', { event, date });
 292
 293     try {
 294       const epoch = common.dateToEpoch(date);
 295       const result = this.statement.almanacUpsert.run({ event, epoch });
 296       if (result.changes != 1) {
 297         this.constructor._almanacErrorThrow();
 298       }
 299     } catch (e) {
 300       this.logger.error(_scope, 'failed', { error: e, event, date });
 301       throw e;
 302     }
 303   }
 304
 305
 306   static _authenticationToNative(authentication) {
 307     if (authentication) {
 308       authentication.created = new Date(Number(authentication.created) * 1000);
 309       authentication.lastAuthentication = new Date(Number(authentication.lastAuthentication) * 1000);
 310     }
 311     return authentication;
 312   }
 313
 314
 315   authenticationGet(dbCtx, identifier) {
 316     const _scope = _fileScope('authenticationGet');
 317     this.logger.debug(_scope, 'called', { identifier });
 318
 319     try {
 320       const authentication = this.statement.authenticationGet.get({ identifier });
 321       return DatabaseSQLite._authenticationToNative(authentication);
 322     } catch (e) {
 323       this.logger.error(_scope, 'failed', { error: e, identifier });
 324       throw e;
 325     }
 326   }
 327
 328
 329   authenticationSuccess(dbCtx, identifier) {
 330     const _scope = _fileScope('authenticationSuccess');
 331     this.logger.debug(_scope, 'called', { identifier });
 332
 333     try {
 334       const result = this.statement.authenticationSuccess.run({ identifier });
 335       if (result.changes != 1) {
 336         throw new DBErrors.UnexpectedResult('did not update authentication success');
 337       }
 338     } catch (e) {
 339       this.logger.error(_scope, 'failed', { error: e, identifier });
 340       throw e;
 341     }
 342   }
 343
 344
 345   authenticationUpsert(dbCtx, identifier, credential) {
 346     const _scope = _fileScope('authenticationUpsert');
 347     const scrubbedCredential = '*'.repeat((credential || '').length);
 348     this.logger.debug(_scope, 'called', { identifier, scrubbedCredential });
 349
 350     let result;
 351     try {
 352       result = this.statement.authenticationUpsert.run({ identifier, credential });
 353       if (result.changes != 1) {
 354         throw new DBErrors.UnexpectedResult('did not upsert authentication');
 355       }
 356     } catch (e) {
 357       this.logger.error(_scope, 'failed', { error: e, identifier, scrubbedCredential });
 358       throw e;
 359     }
 360   }
 361
 362
 363   profileIdentifierInsert(dbCtx, profile, identifier) {
 364     const _scope = _fileScope('profileIdentifierInsert');
 365     this.logger.debug(_scope, 'called', { profile, identifier });
 366
 367     try {
 368       const result = this.statement.profileIdentifierInsert.run({ profile, identifier });
 369       if (result.changes != 1) {
 370         throw new DBErrors.UnexpectedResult('did not insert profile identifier relationship');
 371       }
 372     } catch (e) {
 373       this.logger.error(_scope, 'failed', { error: e, profile, identifier });
 374       throw e;
 375     }
 376   }
 377
 378
 379   profileIsValid(dbCtx, profile) {
 380     const _scope = _fileScope('profileIsValid');
 381     this.logger.debug(_scope, 'called', { profile });
 382
 383     try {
 384       const profileResponse = this.statement.profileGet.get({ profile });
 385       return !!profileResponse;
 386     } catch (e) {
 387       this.logger.error(_scope, 'failed', { error: e, profile });
 388       throw e;
 389     }
 390   }
 391
 392
 393   profileScopeInsert(dbCtx, profile, scope) {
 394     const _scope = _fileScope('profileScopeInsert');
 395     this.logger.debug(_scope, 'called', { profile, scope });
 396
 397     try {
 398       const result = this.statement.profileScopeInsert.run({ profile, scope });
 399       // Duplicate inserts get ignored
 400       if (result.changes != 1 && result.changes != 0) {
 401         throw new DBErrors.UnexpectedResult('did not insert profile scope');
 402       }
 403     } catch (e) {
 404       this.logger.error(_scope, 'failed', { error: e, profile, scope });
 405       throw e;
 406     }
 407   }
 408
 409
 410   profileScopesSetAll(dbCtx, profile, scopes) {
 411     const _scope = _fileScope('profileScopesSetAll');
 412     this.logger.debug(_scope, 'called', { profile, scopes });
 413
 414     try {
 415       this.transaction(dbCtx, () => {
 416         this.statement.profileScopesClear.run({ profile });
 417         if (scopes.length) {
 418           scopes.forEach((scope) => {
 419             this.statement.profileScopeInsert.run({ profile, scope });
 420           });
 421         }
 422       }); // transaction
 423     } catch (e) {
 424       this.logger.error(_scope, 'failed', { error: e, profile, scopes });
 425       throw e;
 426     }
 427   }
 428
 429
 430   profilesScopesByIdentifier(dbCtx, identifier) {
 431     const _scope = _fileScope('profilesScopesByIdentifier');
 432     this.logger.debug(_scope, 'called', { identifier });
 433
 434     try {
 435       const profileScopesRows = this.statement.profilesScopesByIdentifier.all({ identifier });
 436       return Database._profilesScopesBuilder(profileScopesRows);
 437     } catch (e) {
 438       this.logger.error(_scope, 'failed', { error: e, identifier });
 439       throw e;
 440     }
 441   }
 442
 443
 444   redeemCode(dbCtx, { codeId, created, isToken, clientId, profile, identifier, scopes, lifespanSeconds, refreshLifespanSeconds, profileData }) {
 445     const _scope = _fileScope('redeemCode');
 446     this.logger.debug(_scope, 'called', { codeId, created, isToken, clientId, profile, identifier, scopes, lifespanSeconds, refreshLifespanSeconds, profileData });
 447
 448     let result, ret = false;
 449     try {
 450       if (profileData) {
 451         profileData = JSON.stringify(profileData);
 452       }
 453       this.transaction(dbCtx, () => {
 454         result = this.statement.redeemCode.get({ codeId, created: common.dateToEpoch(created), isToken: DatabaseSQLite._booleanToNumeric(isToken), clientId, profile, identifier, lifespanSeconds, refreshLifespanSeconds, profileData });
 455         if (!result) {
 456           this.logger.error(_scope, 'failed', { result });
 457           throw new DBErrors.UnexpectedResult('did not redeem code');
 458         }
 459         // Abort and return false if redemption resulted in revocation.
 460         if (result.isRevoked) {
 461           return;
 462         }
 463
 464         // Ensure there are entries for all scopes, and associate with token.
 465         scopes.forEach((scope) => {
 466           this.statement.scopeInsert.run({ scope });
 467           this.statement.tokenScopeSet.run({ codeId, scope });
 468         });
 469         ret = true;
 470       }); // tx
 471     } catch (e) {
 472       this.logger.error(_scope, 'failed', { error: e, codeId, isToken, clientId, profile, identifier, scopes, lifespanSeconds, refreshLifespanSeconds, profileData });
 473       throw e;
 474     }
 475     return ret;
 476   }
 477
 478
 479   static _refreshCodeResponseToNative(refreshResponse) {
 480     if (refreshResponse) {
 481       ['expires', 'refreshExpires'].forEach((epochField) => {
 482         if (refreshResponse[epochField]) { // eslint-disable-line security/detect-object-injection
 483           refreshResponse[epochField] = new Date(Number(refreshResponse[epochField]) * 1000); // eslint-disable-line security/detect-object-injection
 484         }
 485       });
 486     }
 487     return refreshResponse;
 488   }
 489
 490
 491   refreshCode(dbCtx, codeId, refreshed, removeScopes) {
 492     const _scope = _fileScope('refreshCode');
 493     this.logger.debug(_scope, 'called', { codeId, refreshed, removeScopes });
 494
 495     try {
 496       return this.transaction(dbCtx, () => {
 497         const refreshResponse = this.statement.refreshCode.get({ codeId, refreshed: common.dateToEpoch(refreshed) });
 498         if (refreshResponse) {
 499           removeScopes.forEach((scope) => {
 500             const result = this.statement.tokenScopeRemove.run({ codeId, scope });
 501             if (result?.changes != 1) {
 502               this.logger.error(_scope, 'failed to remove token scope', { codeId, scope });
 503               throw new DBErrors.UnexpectedResult('did not remove scope from token');
 504             }
 505           });
 506           if (removeScopes.length) {
 507             refreshResponse.scopes = (this.statement.tokenScopesGetByCodeId.all({ codeId }) || [])
 508               .map((row) => row.scope);
 509           }
 510         } else {
 511           this.logger.debug(_scope, 'did not refresh token', {});
 512         }
 513         return DatabaseSQLite._refreshCodeResponseToNative(refreshResponse);
 514       }); // tx
 515     } catch (e) {
 516       this.logger.error(_scope, 'failed', { error: e, codeId, refreshed });
 517       throw e;
 518     }
 519   }
 520
 521
 522   static _resourceToNative(resource) {
 523     if (resource) {
 524       resource.created = new Date(Number(resource.created) * 1000);
 525     }
 526     return resource;
 527   }
 528
 529
 530   resourceGet(dbCtx, resourceId) {
 531     const _scope = _fileScope('resourceGet');
 532     this.logger.debug(_scope, 'called', { resourceId });
 533
 534     try {
 535       const resource = this.statement.resourceGet.get({ resourceId });
 536       return DatabaseSQLite._resourceToNative(resource);
 537     } catch (e) {
 538       this.logger.error(_scope, 'failed', { error: e, resourceId });
 539       throw e;
 540     }
 541   }
 542
 543
 544   resourceUpsert(dbCtx, resourceId, secret, description) {
 545     const _scope = _fileScope('resourceUpsert');
 546     this.logger.debug(_scope, 'called', { resourceId });
 547
 548     try {
 549       if (!resourceId) {
 550         resourceId = uuid.v4();
 551       }
 552       const result = this.statement.resourceUpsert.run({ resourceId, secret, description });
 553       if (result.changes != 1) {
 554         throw new DBErrors.UnexpectedResult('did not upsert resource');
 555       }
 556       const resource = this.statement.resourceGet.get({ resourceId });
 557       return DatabaseSQLite._resourceToNative(resource);
 558     } catch (e) {
 559       this.logger.error(_scope, 'failed', { error: e, resourceId, secret, description });
 560       throw e;
 561     }
 562   }
 563
 564
 565   scopeCleanup(dbCtx, atLeastMsSinceLast) {
 566     const _scope = _fileScope('scopeCleanup');
 567     this.logger.debug(_scope, 'called', { atLeastMsSinceLast });
 568
 569     const almanacEvent = Enum.AlmanacEntry.ScopeCleanup;
 570     try {
 571       return this.db.transaction(() => {
 572
 573         // Check that enough time has passed since last cleanup
 574         const nowEpoch = BigInt(common.dateToEpoch());
 575         const { epoch: lastCleanupEpoch } = this.statement.almanacGet.get({ event: almanacEvent }) || { epoch: 0n };
 576         const elapsedMs = (nowEpoch - lastCleanupEpoch) * 1000n;
 577         if (elapsedMs < atLeastMsSinceLast) {
 578           this.logger.debug(_scope, 'skipping token cleanup, too soon', { lastCleanupEpoch, elapsedMs, atLeastMsSinceLast });
 579           return;
 580         }
 581
 582         // Do the cleanup
 583         const { changes: scopesRemoved } = this.statement.scopeCleanup.run();
 584
 585         // Update the last cleanup time
 586         const result = this.statement.almanacUpsert.run({ event: almanacEvent, epoch: nowEpoch });
 587         if (result.changes != 1) {
 588           this.constructor._almanacErrorThrow();
 589         }
 590
 591         this.logger.debug(_scope, 'finished', { scopesRemoved, atLeastMsSinceLast });
 592         return scopesRemoved;
 593       }).exclusive();
 594     } catch (e) {
 595       this.logger.error(_scope, 'failed', { error: e, atLeastMsSinceLast });
 596       throw e;
 597     }
 598   }
 599
 600
 601   scopeDelete(dbCtx, scope) {
 602     const _scope = _fileScope('scopeDelete');
 603     this.logger.debug(_scope, 'called', { scope });
 604
 605     try {
 606       return this.transaction(dbCtx, () => {
 607         const { inUse } = this.statement.scopeInUse.get({ scope });
 608         if (inUse) {
 609           this.logger.debug(_scope, 'not deleted, in use', { scope });
 610           return false;
 611         }
 612         const result = this.statement.scopeDelete.run({ scope });
 613         if (result.changes == 0) {
 614           this.logger.debug(_scope, 'no such scope', { scope });
 615         } else {
 616           this.logger.debug(_scope, 'deleted', { scope });
 617         }
 618         return true;
 619       });
 620     } catch (e) {
 621       this.logger.error(_scope, 'failed', { error: e, scope });
 622       throw e;
 623     }
 624   }
 625
 626
 627   scopeUpsert(dbCtx, scope, application, description, manuallyAdded) {
 628     const _scope = _fileScope('scopeUpsert');
 629     this.logger.debug(_scope, 'called', { scope, application, description, manuallyAdded });
 630
 631     try {
 632       const result = this.statement.scopeUpsert.run({ scope, application, description, manuallyAdded: DatabaseSQLite._booleanToNumeric(manuallyAdded) });
 633       if (result.changes != 1) {
 634         throw new DBErrors.UnexpectedResult('did not upsert scope');
 635       }
 636     } catch (e) {
 637       this.logger.error(_scope, 'failed', { error: e, scope, application, description, manuallyAdded });
 638       throw e;
 639     }
 640   }
 641
 642
 643   tokenCleanup(dbCtx, codeLifespanSeconds, atLeastMsSinceLast) {
 644     const _scope = _fileScope('tokenCleanup');
 645     this.logger.debug(_scope, 'called', { codeLifespanSeconds, atLeastMsSinceLast });
 646
 647     const almanacEvent = Enum.AlmanacEntry.TokenCleanup;
 648     try {
 649       return this.db.transaction(() => {
 650
 651         // Check that enough time has passed since last cleanup
 652         const nowEpoch = BigInt(common.dateToEpoch());
 653         const { epoch: lastCleanupEpoch } = this.statement.almanacGet.get({ event: almanacEvent }) || { epoch: 0n };
 654         const elapsedMs = (nowEpoch - lastCleanupEpoch) * 1000n;
 655         if (elapsedMs < atLeastMsSinceLast) {
 656           this.logger.debug(_scope, 'skipping token cleanup, too soon', { lastCleanupEpoch, elapsedMs, atLeastMsSinceLast });
 657           return;
 658         }
 659
 660         // Do the cleanup
 661         const { changes: tokensRemoved } = this.statement.tokenCleanup.run({ codeLifespanSeconds });
 662
 663         // Update the last cleanup time
 664         const result = this.statement.almanacUpsert.run({ event: almanacEvent, epoch: nowEpoch });
 665         if (result.changes != 1) {
 666           this.constructor._almanacErrorThrow();
 667         }
 668
 669         this.logger.debug(_scope, 'finished', { tokensRemoved, codeLifespanSeconds, atLeastMsSinceLast });
 670         return tokensRemoved;
 671       }).exclusive();
 672     } catch (e) {
 673       this.logger.error(_scope, 'failed', { error: e, codeLifespanSeconds, atLeastMsSinceLast });
 674       throw e;
 675     }
 676   }
 677
 678
 679   static _tokenToNative(token) {
 680     if (token) {
 681       token.created = new Date(Number(token.created) * 1000);
 682       if (token.expires || token.expires == 0) {
 683         token.expires = new Date(Number(token.expires) * 1000);
 684       }
 685       if (token.refreshExpires || token.refreshExpires == 0) {
 686         token.refreshExpires = new Date(Number(token.refreshExpires) * 1000);
 687       }
 688       if (token.refreshed || token.refreshed == 0) {
 689         token.refreshed = new Date(Number(token.refreshed) * 1000);
 690       }
 691       token.isRevoked = !!token.isRevoked;
 692       token.isToken = !!token.isToken;
 693       if (token.profileData) {
 694         token.profileData = JSON.parse(token.profileData);
 695       }
 696     }
 697     return token;
 698   }
 699
 700
 701   tokenGetByCodeId(dbCtx, codeId) {
 702     const _scope = _fileScope('tokenGetByCodeId');
 703     this.logger.debug(_scope, 'called', { codeId });
 704
 705     try {
 706       return this.transaction(dbCtx, () => {
 707         const token = this.statement.tokenGetByCodeId.get({ codeId });
 708         token.scopes = (this.statement.tokenScopesGetByCodeId.all({ codeId }) || [])
 709           .map((row) => row.scope);
 710         return DatabaseSQLite._tokenToNative(token);
 711       });
 712     } catch (e) {
 713       this.logger.error(_scope, 'failed', { error: e, codeId });
 714       throw e;
 715     }
 716   }
 717
 718
 719   tokenRefreshRevokeByCodeId(dbCtx, codeId) {
 720     const _scope = _fileScope('tokenRefreshRevokeByCodeId');
 721     this.logger.debug(_scope, 'called', { codeId });
 722
 723     try {
 724       const result = this.statement.tokenRefreshRevokeByCodeId.run({ codeId });
 725       if (result.changes != 1) {
 726         throw new DBErrors.UnexpectedResult('did not revoke refresh');
 727       }
 728     } catch (e) {
 729       this.logger.error(_scope, 'failed', { error: e, codeId });
 730       throw e;
 731     }
 732   }
 733
 734
 735   tokenRevokeByCodeId(dbCtx, codeId) {
 736     const _scope = _fileScope('tokenRevokeByCodeId');
 737     this.logger.debug(_scope, 'called', { codeId });
 738
 739     try {
 740       const result = this.statement.tokenRevokeByCodeId.run({ codeId });
 741       if (result.changes != 1) {
 742         throw new DBErrors.UnexpectedResult('did not revoke token');
 743       }
 744     } catch (e) {
 745       this.logger.error(_scope, 'failed', { error: e, codeId });
 746       throw e;
 747     }
 748   }
 749
 750
 751   tokensGetByIdentifier(dbCtx, identifier) {
 752     const _scope = _fileScope('tokensGetByIdentifier');
 753     this.logger.debug(_scope, 'called', { identifier });
 754
 755     try {
 756       const tokens = this.statement.tokensGetByIdentifier.all({ identifier });
 757       return tokens.map(DatabaseSQLite._tokenToNative);
 758     } catch (e) {
 759       this.logger.error(_scope, 'failed', { error: e, identifier });
 760       throw e;
 761     }
 762   }
 763
 764
 765   ticketRedeemed(dbCtx, redeemedData) {
 766     const _scope = _fileScope('ticketRedeemed');
 767     this.logger.debug(_scope, 'called', { ...redeemedData });
 768
 769     try {
 770       const result = this.statement.ticketRedeemed.run(redeemedData);
 771       if (result.changes != 1) {
 772         throw new DBErrors.UnexpectedResult('did not store redeemed ticket');
 773       }
 774     } catch (e) {
 775       this.logger.error(_scope, 'failed', { error: e });
 776       throw e;
 777     }
 778   }
 779
 780
 781   ticketTokenPublished(dbCtx, redeemedData) {
 782     const _scope = _fileScope('ticketRedeemed');
 783     this.logger.debug(_scope, 'called', { ...redeemedData });
 784
 785     const almanacEvent = Enum.AlmanacEntry.TicketPublished;
 786     try {
 787       const result = this.statement.ticketTokenPublished.run(redeemedData);
 788       if (result.changes != 1) {
 789         throw new DBErrors.UnexpectedResult('did not store redeemed ticket');
 790       }
 791       const epoch = common.dateToEpoch();
 792       const almanacResult = this.statement.almanacUpsert.run({ event: almanacEvent, epoch });
 793       if (almanacResult.changes != 1) {
 794         this.constructor._almanacErrorThrow();
 795       }
 796
 797     } catch (e) {
 798       this.logger.error(_scope, 'failed', { error: e });
 799       throw e;
 800     }
 801   }
 802
 803   static _redeemedTicketToNative(redeemedTicket) {
 804     redeemedTicket.created = new Date(Number(redeemedTicket.created) * 1000);
 805     if (redeemedTicket.published) {
 806       redeemedTicket.published = new Date(Number(redeemedTicket.published) * 1000);
 807     }
 808     return redeemedTicket;
 809   }
 810
 811   ticketTokenGetUnpublished() {
 812     const _scope = _fileScope('ticketTokenGetUnpublished');
 813     this.logger.debug(_scope, 'called');
 814
 815     try {
 816       const unpublished = this.statement.ticketTokenGetUnpublished.all();
 817       return unpublished.map((x) => DatabaseSQLite._redeemedTicketToNative(x));
 818     } catch (e) {
 819       this.logger.error(_scope, 'failed', { error: e });
 820       throw e;
 821     }
 822   }
 823
 824 }
 825
 826 module.exports = DatabaseSQLite;