3 const common
= require('../../common');
4 const Database
= require('../abstract');
5 const DBErrors
= require('../errors');
6 const svh
= require('../schema-version-helper');
7 const SQLite
= require('better-sqlite3');
8 const fs
= require('fs');
9 const path
= require('path');
10 const uuid
= require('uuid');
11 const { performance
} = require('perf_hooks');
13 const _fileScope
= common
.fileScope(__filename
);
15 const schemaVersionsSupported
= {
28 // max of signed int64 (2^63 - 1), should be enough
29 // const EPOCH_FOREVER = BigInt('9223372036854775807');
31 class DatabaseSQLite
extends Database
{
32 constructor(logger
, options
) {
33 super(logger
, options
);
35 const connectionString
= options
.db
.connectionString
|| 'sqlite://:memory:';
36 const csDelim
= '://';
37 const dbFilename
= connectionString
.slice(connectionString
.indexOf(csDelim
) + csDelim
.length
);
39 const queryLogLevel
= options
.db
.queryLogLevel
;
41 const sqliteOptions
= {
42 ...(queryLogLevel
&& {
43 // eslint-disable-next-line security/detect-object-injection
44 verbose: (query
) => this.logger
[queryLogLevel
](_fileScope('SQLite:verbose'), '', { query
}),
47 this.db
= new SQLite(dbFilename
, sqliteOptions
);
48 this.schemaVersionsSupported
= schemaVersionsSupported
;
49 this.changesSinceLastOptimize
= BigInt(0);
50 this.optimizeAfterChanges
= options
.db
.sqliteOptimizeAfterChanges
|| 0; // Default to no periodic optimization.
51 this.db
.pragma('foreign_keys = on'); // Enforce consistency.
52 this.db
.pragma('journal_mode = WAL'); // Be faster, expect local filesystem.
53 this.db
.defaultSafeIntegers(true); // This probably isn't necessary, but by using these BigInts we keep weird floats out of the query logs.
56 this._initStatements();
61 * Boolean to 0/1 representation for SQLite params.
62 * @param {Boolean} bool
65 static _booleanToNumeric(bool
) {
66 // eslint-disable-next-line security/detect-object-injection
75 * SQLite cannot prepare its statements without a schema, ensure such exists.
78 const _scope
= _fileScope('_initTables');
80 // Migrations rely upon this table, ensure it exists.
81 const metaVersionTable
= '_meta_schema_version';
82 const tableExists
= this.db
.prepare('SELECT name FROM sqlite_master WHERE type=:type AND name=:name').pluck(true).bind({ type: 'table', name: metaVersionTable
});
83 let metaExists
= tableExists
.get();
84 if (metaExists
=== undefined) {
85 const fPath
= path
.join(__dirname
, 'sql', 'schema', 'init.sql');
86 // eslint-disable-next-line security/detect-non-literal-fs-filename
87 const fSql
= fs
.readFileSync(fPath
, { encoding: 'utf8' });
89 metaExists
= tableExists
.get();
90 /* istanbul ignore if */
91 if (metaExists
=== undefined) {
92 throw new DBErrors
.UnexpectedResult(`did not create ${metaVersionTable} table`);
94 this.logger
.info(_scope
, 'created schema version table', { metaVersionTable
});
98 const currentSchema
= this._currentSchema();
99 const migrationsWanted
= svh
.unappliedSchemaVersions(__dirname
, currentSchema
, this.schemaVersionsSupported
);
100 this.logger
.debug(_scope
, 'schema migrations wanted', { migrationsWanted
});
101 migrationsWanted
.forEach((v
) => {
102 const fPath
= path
.join(__dirname
, 'sql', 'schema', v
, 'apply.sql');
103 // eslint-disable-next-line security/detect-non-literal-fs-filename
104 const fSql
= fs
.readFileSync(fPath
, { encoding: 'utf8' });
105 this.logger
.info(_scope
, 'applying migration', { version: v
});
112 const _scope
= _fileScope('_initStatements');
113 const sqlDir
= path
.join(__dirname
, 'sql');
116 // Decorate the statement calls we use with timing and logging.
117 const wrapFetch
= (logName
, statementName
, fn
) => {
118 const _wrapScope
= _fileScope(logName
);
119 return (...args
) => {
120 const startTimestampMs
= performance
.now();
121 const rows
= fn(...args
);
122 DatabaseSQLite
._deOphidiate(rows
);
123 const elapsedTimeMs
= performance
.now() - startTimestampMs
;
124 this.logger
.debug(_wrapScope
, 'complete', { statementName
, elapsedTimeMs
});
128 const wrapRun
= (logName
, statementName
, fn
) => {
129 const _wrapScope
= _fileScope(logName
);
130 return (...args
) => {
131 const startTimestampMs
= performance
.now();
132 const result
= fn(...args
);
133 const elapsedTimeMs
= performance
.now() - startTimestampMs
;
134 this._updateChanges(result
);
135 this.logger
.debug(_wrapScope
, 'complete', { ...result
, statementName
, elapsedTimeMs
});
136 result
.duration
= elapsedTimeMs
;
141 // eslint-disable-next-line security/detect-non-literal-fs-filename
142 for (const f
of fs
.readdirSync(sqlDir
)) {
143 const fPath
= path
.join(sqlDir
, f
);
144 const { name: fName
, ext: fExt
} = path
.parse(f
);
145 // eslint-disable-next-line security/detect-non-literal-fs-filename
146 const stat
= fs
.statSync(fPath
);
148 || fExt
.toLowerCase() !== '.sql') {
151 // eslint-disable-next-line security/detect-non-literal-fs-filename
152 const fSql
= fs
.readFileSync(fPath
, { encoding: 'utf8' });
153 const statementName
= common
.camelfy(fName
.toLowerCase(), '-');
156 statement
= this.db
.prepare(fSql
);
157 } catch (e
) /* istanbul ignore next */ {
158 this.logger
.error(_scope
, 'failed to prepare statement', { error: e
, file: f
});
161 // eslint-disable-next-line security/detect-object-injection
162 this.statement
[statementName
] = statement
;
163 const { get: origGet
, all: origAll
, run: origRun
} = statement
;
164 statement
.get = wrapFetch('SQLite:get', statementName
, origGet
.bind(statement
));
165 statement
.all
= wrapFetch('SQLite:all', statementName
, origAll
.bind(statement
));
166 statement
.run
= wrapRun('SQLite:run', statementName
, origRun
.bind(statement
));
168 this.statement
._optimize
= this.db
.prepare('SELECT * FROM pragma_optimize(0xffff)');
170 this.logger
.debug(_scope
, 'statements initialized', { statements: Object
.keys(this.statement
).length
});
174 static _deOphidiate(rows
) {
175 const rowsIsArray
= Array
.isArray(rows
);
179 const exemplaryRow
= rows
[0];
180 for (const prop
in exemplaryRow
) {
181 const camel
= common
.camelfy(prop
);
182 if (!(camel
in exemplaryRow
)) {
183 for (const d
of rows
) {
184 d
[camel
] = d
[prop
]; // eslint-disable-line security/detect-object-injection
185 delete d
[prop
]; // eslint-disable-line security/detect-object-injection
189 return rowsIsArray
? rows : rows
[0];
194 return this.db
.prepare('SELECT major, minor, patch FROM _meta_schema_version ORDER BY major DESC, minor DESC, patch DESC LIMIT 1').get();
199 const _scope
= _fileScope('healthCheck');
200 this.logger
.debug(_scope
, 'called', {});
202 throw new DBErrors
.UnexpectedResult('database is not open');
204 return { open: this.db
.open
};
214 const _scope
= _fileScope('_optimize');
216 const optimize
= this.statement
._optimize
.all();
217 this.logger
.debug(_scope
, 'optimize', { optimize
, changes: this.changesSinceLastOptimize
});
218 this.db
.pragma('optimize');
219 this.changesSinceLastOptimize
= BigInt(0);
223 _updateChanges(dbResult
) {
224 if (this.optimizeAfterChanges
) {
225 this.changesSinceLastOptimize
+= BigInt(dbResult
.changes
);
226 if (this.changesSinceLastOptimize
>= this.optimizeAfterChanges
) {
233 _purgeTables(really
) {
240 const result
= this.db
.prepare(`DELETE FROM ${table}`).run();
241 this.logger
.debug(_fileScope('_purgeTables'), 'success', { table
, result
});
252 transaction(dbCtx
, fn
) {
253 dbCtx
= dbCtx
|| this.db
;
254 return dbCtx
.transaction(fn
)();
258 static _almanacToNative(entry
) {
261 date: new Date(Number(entry
.epoch
) * 1000),
265 almanacGetAll(dbCtx
) { // eslint-disable-line no-unused-vars
266 const _scope
= _fileScope('almanacGetAll');
267 this.logger
.debug(_scope
, 'called');
270 const entries
= this.statement
.almanacGetAll
.all();
271 return entries
.map((entry
) => DatabaseSQLite
._almanacToNative(entry
));
273 this.logger
.error(_scope
, 'failed', { error: e
});
279 static _authenticationToNative(authentication
) {
280 if (authentication
) {
281 authentication
.created
= new Date(Number(authentication
.created
) * 1000);
282 authentication
.lastAuthentication
= new Date(Number(authentication
.lastAuthentication
) * 1000);
284 return authentication
;
288 authenticationGet(dbCtx
, identifier
) {
289 const _scope
= _fileScope('authenticationGet');
290 this.logger
.debug(_scope
, 'called', { identifier
});
293 const authentication
= this.statement
.authenticationGet
.get({ identifier
});
294 return DatabaseSQLite
._authenticationToNative(authentication
);
296 this.logger
.error(_scope
, 'failed', { error: e
, identifier
});
302 authenticationSuccess(dbCtx
, identifier
) {
303 const _scope
= _fileScope('authenticationSuccess');
304 this.logger
.debug(_scope
, 'called', { identifier
});
307 const result
= this.statement
.authenticationSuccess
.run({ identifier
});
308 if (result
.changes
!= 1) {
309 throw new DBErrors
.UnexpectedResult('did not update authentication success');
312 this.logger
.error(_scope
, 'failed', { error: e
, identifier
});
318 authenticationUpsert(dbCtx
, identifier
, credential
) {
319 const _scope
= _fileScope('authenticationUpsert');
320 const scrubbedCredential
= '*'.repeat((credential
|| '').length
);
321 this.logger
.debug(_scope
, 'called', { identifier
, scrubbedCredential
});
325 result
= this.statement
.authenticationUpsert
.run({ identifier
, credential
});
326 if (result
.changes
!= 1) {
327 throw new DBErrors
.UnexpectedResult('did not upsert authentication');
330 this.logger
.error(_scope
, 'failed', { error: e
, identifier
, scrubbedCredential
});
336 profileIdentifierInsert(dbCtx
, profile
, identifier
) {
337 const _scope
= _fileScope('profileIdentifierInsert');
338 this.logger
.debug(_scope
, 'called', { profile
, identifier
});
341 const result
= this.statement
.profileIdentifierInsert
.run({ profile
, identifier
});
342 if (result
.changes
!= 1) {
343 throw new DBErrors
.UnexpectedResult('did not insert profile identifier relationship');
346 this.logger
.error(_scope
, 'failed', { error: e
, profile
, identifier
});
352 profileIsValid(dbCtx
, profile
) {
353 const _scope
= _fileScope('profileIsValid');
354 this.logger
.debug(_scope
, 'called', { profile
});
357 const profileResponse
= this.statement
.profileGet
.get({ profile
});
358 return !!profileResponse
;
360 this.logger
.error(_scope
, 'failed', { error: e
, profile
});
366 profileScopeInsert(dbCtx
, profile
, scope
) {
367 const _scope
= _fileScope('profileScopeInsert');
368 this.logger
.debug(_scope
, 'called', { profile
, scope
});
371 const result
= this.statement
.profileScopeInsert
.run({ profile
, scope
});
372 // Duplicate inserts get ignored
373 if (result
.changes
!= 1 && result
.changes
!= 0) {
374 throw new DBErrors
.UnexpectedResult('did not insert profile scope');
377 this.logger
.error(_scope
, 'failed', { error: e
, profile
, scope
});
383 profileScopesSetAll(dbCtx
, profile
, scopes
) {
384 const _scope
= _fileScope('profileScopesSetAll');
385 this.logger
.debug(_scope
, 'called', { profile
, scopes
});
388 this.transaction(dbCtx
, () => {
389 this.statement
.profileScopesClear
.run({ profile
});
391 scopes
.forEach((scope
) => {
392 this.statement
.profileScopeInsert
.run({ profile
, scope
});
397 this.logger
.error(_scope
, 'failed', { error: e
, profile
, scopes
});
403 profilesScopesByIdentifier(dbCtx
, identifier
) {
404 const _scope
= _fileScope('profilesScopesByIdentifier');
405 this.logger
.debug(_scope
, 'called', { identifier
});
408 const profileScopesRows
= this.statement
.profilesScopesByIdentifier
.all({ identifier
});
409 return Database
._profilesScopesBuilder(profileScopesRows
);
411 this.logger
.error(_scope
, 'failed', { error: e
, identifier
});
417 redeemCode(dbCtx
, { codeId
, created
, isToken
, clientId
, profile
, identifier
, scopes
, lifespanSeconds
, refreshLifespanSeconds
, profileData
}) {
418 const _scope
= _fileScope('redeemCode');
419 this.logger
.debug(_scope
, 'called', { codeId
, created
, isToken
, clientId
, profile
, identifier
, scopes
, lifespanSeconds
, refreshLifespanSeconds
, profileData
});
421 let result
, ret
= false;
424 profileData
= JSON
.stringify(profileData
);
426 this.transaction(dbCtx
, () => {
427 result
= this.statement
.redeemCode
.get({ codeId
, created: common
.dateToEpoch(created
), isToken: DatabaseSQLite
._booleanToNumeric(isToken
), clientId
, profile
, identifier
, lifespanSeconds
, refreshLifespanSeconds
, profileData
});
429 this.logger
.error(_scope
, 'failed', { result
});
430 throw new DBErrors
.UnexpectedResult('did not redeem code');
432 // Abort and return false if redemption resulted in revocation.
433 if (result
.isRevoked
) {
437 // Ensure there are entries for all scopes, and associate with token.
438 scopes
.forEach((scope
) => {
439 this.statement
.scopeInsert
.run({ scope
});
440 this.statement
.tokenScopeSet
.run({ codeId
, scope
});
445 this.logger
.error(_scope
, 'failed', { error: e
, codeId
, isToken
, clientId
, profile
, identifier
, scopes
, lifespanSeconds
, refreshLifespanSeconds
, profileData
});
452 static _refreshCodeResponseToNative(refreshResponse
) {
453 if (refreshResponse
) {
454 ['expires', 'refreshExpires'].forEach((epochField
) => {
455 if (refreshResponse
[epochField
]) { // eslint-disable-line security/detect-object-injection
456 refreshResponse
[epochField
] = new Date(Number(refreshResponse
[epochField
]) * 1000); // eslint-disable-line security/detect-object-injection
460 return refreshResponse
;
464 refreshCode(dbCtx
, codeId
, refreshed
, removeScopes
) {
465 const _scope
= _fileScope('refreshCode');
466 this.logger
.debug(_scope
, 'called', { codeId
, refreshed
, removeScopes
});
469 return this.transaction(dbCtx
, () => {
470 const refreshResponse
= this.statement
.refreshCode
.get({ codeId
, refreshed: common
.dateToEpoch(refreshed
) });
471 if (refreshResponse
) {
472 removeScopes
.forEach((scope
) => {
473 const result
= this.statement
.tokenScopeRemove
.run({ codeId
, scope
});
474 if (result
?.changes
!= 1) {
475 this.logger
.error(_scope
, 'failed to remove token scope', { codeId
, scope
});
476 throw new DBErrors
.UnexpectedResult('did not remove scope from token');
479 if (removeScopes
.length
) {
480 refreshResponse
.scopes
= (this.statement
.tokenScopesGetByCodeId
.all({ codeId
}) || [])
481 .map((row
) => row
.scope
);
484 this.logger
.debug(_scope
, 'did not refresh token', {});
486 return DatabaseSQLite
._refreshCodeResponseToNative(refreshResponse
);
489 this.logger
.error(_scope
, 'failed', { error: e
, codeId
, refreshed
});
495 static _resourceToNative(resource
) {
497 resource
.created
= new Date(Number(resource
.created
) * 1000);
503 resourceGet(dbCtx
, resourceId
) {
504 const _scope
= _fileScope('resourceGet');
505 this.logger
.debug(_scope
, 'called', { resourceId
});
508 const resource
= this.statement
.resourceGet
.get({ resourceId
});
509 return DatabaseSQLite
._resourceToNative(resource
);
511 this.logger
.error(_scope
, 'failed', { error: e
, resourceId
});
517 resourceUpsert(dbCtx
, resourceId
, secret
, description
) {
518 const _scope
= _fileScope('resourceUpsert');
519 this.logger
.debug(_scope
, 'called', { resourceId
});
523 resourceId
= uuid
.v4();
525 const result
= this.statement
.resourceUpsert
.run({ resourceId
, secret
, description
});
526 if (result
.changes
!= 1) {
527 throw new DBErrors
.UnexpectedResult('did not upsert resource');
529 const resource
= this.statement
.resourceGet
.get({ resourceId
});
530 return DatabaseSQLite
._resourceToNative(resource
);
532 this.logger
.error(_scope
, 'failed', { error: e
, resourceId
, secret
, description
});
538 scopeCleanup(dbCtx
, atLeastMsSinceLast
) {
539 const _scope
= _fileScope('scopeCleanup');
540 this.logger
.debug(_scope
, 'called', { atLeastMsSinceLast
});
542 const almanacEvent
= 'scopeCleanup';
544 return this.db
.transaction(() => {
546 // Check that enough time has passed since last cleanup
547 const nowEpoch
= BigInt(common
.dateToEpoch());
548 const { epoch: lastCleanupEpoch
} = this.statement
.almanacGet
.get({ event: almanacEvent
}) || { epoch: 0n
};
549 const elapsedMs
= (nowEpoch
- lastCleanupEpoch
) * 1000n
;
550 if (elapsedMs
< atLeastMsSinceLast
) {
551 this.logger
.debug(_scope
, 'skipping token cleanup, too soon', { lastCleanupEpoch
, elapsedMs
, atLeastMsSinceLast
});
556 const { changes: scopesRemoved
} = this.statement
.scopeCleanup
.run();
558 // Update the last cleanup time
559 const result
= this.statement
.almanacUpsert
.run({ event: almanacEvent
, epoch: nowEpoch
});
560 if (result
.changes
!= 1) {
561 throw new DBErrors
.UnexpectedResult('did not update almanac');
564 this.logger
.debug(_scope
, 'finished', { scopesRemoved
, atLeastMsSinceLast
});
565 return scopesRemoved
;
568 this.logger
.error(_scope
, 'failed', { error: e
, atLeastMsSinceLast
});
574 scopeDelete(dbCtx
, scope
) {
575 const _scope
= _fileScope('scopeDelete');
576 this.logger
.debug(_scope
, 'called', { scope
});
579 return this.transaction(dbCtx
, () => {
580 const { inUse
} = this.statement
.scopeInUse
.get({ scope
});
582 this.logger
.debug(_scope
, 'not deleted, in use', { scope
});
585 const result
= this.statement
.scopeDelete
.run({ scope
});
586 if (result
.changes
== 0) {
587 this.logger
.debug(_scope
, 'no such scope', { scope
});
589 this.logger
.debug(_scope
, 'deleted', { scope
});
594 this.logger
.error(_scope
, 'failed', { error: e
, scope
});
600 scopeUpsert(dbCtx
, scope
, application
, description
, manuallyAdded
) {
601 const _scope
= _fileScope('scopeUpsert');
602 this.logger
.debug(_scope
, 'called', { scope
, application
, description
, manuallyAdded
});
605 const result
= this.statement
.scopeUpsert
.run({ scope
, application
, description
, manuallyAdded: DatabaseSQLite
._booleanToNumeric(manuallyAdded
) });
606 if (result
.changes
!= 1) {
607 throw new DBErrors
.UnexpectedResult('did not upsert scope');
610 this.logger
.error(_scope
, 'failed', { error: e
, scope
, application
, description
, manuallyAdded
});
616 tokenCleanup(dbCtx
, codeLifespanSeconds
, atLeastMsSinceLast
) {
617 const _scope
= _fileScope('tokenCleanup');
618 this.logger
.debug(_scope
, 'called', { codeLifespanSeconds
, atLeastMsSinceLast
});
620 const almanacEvent
= 'tokenCleanup';
622 return this.db
.transaction(() => {
624 // Check that enough time has passed since last cleanup
625 const nowEpoch
= BigInt(common
.dateToEpoch());
626 const { epoch: lastCleanupEpoch
} = this.statement
.almanacGet
.get({ event: almanacEvent
}) || { epoch: 0n
};
627 const elapsedMs
= (nowEpoch
- lastCleanupEpoch
) * 1000n
;
628 if (elapsedMs
< atLeastMsSinceLast
) {
629 this.logger
.debug(_scope
, 'skipping token cleanup, too soon', { lastCleanupEpoch
, elapsedMs
, atLeastMsSinceLast
});
634 const { changes: tokensRemoved
} = this.statement
.tokenCleanup
.run({ codeLifespanSeconds
});
636 // Update the last cleanup time
637 const result
= this.statement
.almanacUpsert
.run({ event: almanacEvent
, epoch: nowEpoch
});
638 if (result
.changes
!= 1) {
639 throw new DBErrors
.UnexpectedResult('did not update almanac');
642 this.logger
.debug(_scope
, 'finished', { tokensRemoved
, codeLifespanSeconds
, atLeastMsSinceLast
});
643 return tokensRemoved
;
646 this.logger
.error(_scope
, 'failed', { error: e
, codeLifespanSeconds
, atLeastMsSinceLast
});
652 static _tokenToNative(token
) {
654 token
.created
= new Date(Number(token
.created
) * 1000);
655 if (token
.expires
|| token
.expires
== 0) {
656 token
.expires
= new Date(Number(token
.expires
) * 1000);
658 if (token
.refreshExpires
|| token
.refreshExpires
== 0) {
659 token
.refreshExpires
= new Date(Number(token
.refreshExpires
) * 1000);
661 if (token
.refreshed
|| token
.refreshed
== 0) {
662 token
.refreshed
= new Date(Number(token
.refreshed
) * 1000);
664 token
.isRevoked
= !!token
.isRevoked
;
665 token
.isToken
= !!token
.isToken
;
666 if (token
.profileData
) {
667 token
.profileData
= JSON
.parse(token
.profileData
);
674 tokenGetByCodeId(dbCtx
, codeId
) {
675 const _scope
= _fileScope('tokenGetByCodeId');
676 this.logger
.debug(_scope
, 'called', { codeId
});
679 return this.transaction(dbCtx
, () => {
680 const token
= this.statement
.tokenGetByCodeId
.get({ codeId
});
681 token
.scopes
= (this.statement
.tokenScopesGetByCodeId
.all({ codeId
}) || [])
682 .map((row
) => row
.scope
);
683 return DatabaseSQLite
._tokenToNative(token
);
686 this.logger
.error(_scope
, 'failed', { error: e
, codeId
});
692 tokenRefreshRevokeByCodeId(dbCtx
, codeId
) {
693 const _scope
= _fileScope('tokenRefreshRevokeByCodeId');
694 this.logger
.debug(_scope
, 'called', { codeId
});
697 const result
= this.statement
.tokenRefreshRevokeByCodeId
.run({ codeId
});
698 if (result
.changes
!= 1) {
699 throw new DBErrors
.UnexpectedResult('did not revoke refresh');
702 this.logger
.error(_scope
, 'failed', { error: e
, codeId
});
708 tokenRevokeByCodeId(dbCtx
, codeId
) {
709 const _scope
= _fileScope('tokenRevokeByCodeId');
710 this.logger
.debug(_scope
, 'called', { codeId
});
713 const result
= this.statement
.tokenRevokeByCodeId
.run({ codeId
});
714 if (result
.changes
!= 1) {
715 throw new DBErrors
.UnexpectedResult('did not revoke token');
718 this.logger
.error(_scope
, 'failed', { error: e
, codeId
});
724 tokensGetByIdentifier(dbCtx
, identifier
) {
725 const _scope
= _fileScope('tokensGetByIdentifier');
726 this.logger
.debug(_scope
, 'called', { identifier
});
729 const tokens
= this.statement
.tokensGetByIdentifier
.all({ identifier
});
730 return tokens
.map(DatabaseSQLite
._tokenToNative
);
732 this.logger
.error(_scope
, 'failed', { error: e
, identifier
});
739 module
.exports
= DatabaseSQLite
;