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