3 const common
= require('../../common');
4 const Database
= require('../base');
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 { performance
} = require('perf_hooks');
12 const _fileScope
= common
.fileScope(__filename
);
14 const schemaVersionsSupported
= {
27 // max of signed int64 (2^63 - 1), should be enough
28 const EPOCH_FOREVER
= BigInt('9223372036854775807');
29 const epochToDate
= (epoch
) => new Date(Number(epoch
) * 1000);
30 const dateToEpoch
= (date
) => Math
.round(date
.getTime() / 1000);
32 class DatabaseSQLite
extends Database
{
33 constructor(logger
, options
) {
34 super(logger
, options
);
36 const connectionString
= options
.db
.connectionString
|| 'sqlite://:memory:';
37 const csDelim
= '://';
38 const dbFilename
= connectionString
.slice(connectionString
.indexOf(csDelim
) + csDelim
.length
);
40 const queryLogLevel
= options
.db
.queryLogLevel
;
42 const sqliteOptions
= {
43 ...(queryLogLevel
&& {
44 // eslint-disable-next-line security/detect-object-injection
45 verbose: (query
) => this.logger
[queryLogLevel
](_fileScope('SQLite:verbose'), '', { query
}),
48 this.db
= new SQLite(dbFilename
, sqliteOptions
);
49 this.schemaVersionsSupported
= schemaVersionsSupported
;
50 this.changesSinceLastOptimize
= BigInt(0);
51 this.optimizeAfterChanges
= options
.db
.optimizeAfterChanges
;
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.
57 this._initStatements();
62 * SQLite cannot prepare its statements without a schema, ensure such exists.
65 const _scope
= _fileScope('_initTables');
67 // Migrations rely upon this table, ensure it exists.
68 const metaVersionTable
= '_meta_schema_version';
69 const tableExists
= this.db
.prepare('SELECT name FROM sqlite_master WHERE type=:type AND name=:name').pluck(true).bind({ type: 'table', name: metaVersionTable
});
70 let metaExists
= tableExists
.get();
71 if (metaExists
=== undefined) {
72 const fPath
= path
.join(__dirname
, 'sql', 'schema', 'init.sql');
74 const fSql
= fs
.readFileSync(fPath
, { encoding: 'utf8' });
76 metaExists
= tableExists
.get();
77 /* istanbul ignore if */
78 if (metaExists
=== undefined) {
79 throw new DBErrors
.UnexpectedResult(`did not create ${metaVersionTable} table`);
81 this.logger
.info(_scope
, 'created schema version table', { metaVersionTable
});
85 const currentSchema
= this._currentSchema();
86 const migrationsWanted
= svh
.unappliedSchemaVersions(__dirname
, currentSchema
, this.schemaVersionsSupported
);
87 this.logger
.debug(_scope
, 'schema migrations wanted', { migrationsWanted
});
88 migrationsWanted
.forEach((v
) => {
89 const fPath
= path
.join(__dirname
, 'sql', 'schema', v
, 'apply.sql');
91 // eslint-disable-next-line security/detect-non-literal-fs-filename
92 const fSql
= fs
.readFileSync(fPath
, { encoding: 'utf8' });
93 this.logger
.debug(_scope
, 'applying migration', { version: v
});
94 const results
= this.db
.exec(fSql
);
95 this.logger
.debug(_scope
, 'migration results', { results
});
96 this.logger
.info(_scope
, 'applied migration', { version: v
});
98 this.logger
.error(_scope
, 'migration failed', { error: e
, fPath
, version: v
});
106 const _scope
= _fileScope('_initStatements');
107 const sqlDir
= path
.join(__dirname
, 'sql');
110 // Decorate the statement calls we use with timing and logging.
111 const wrapFetch
= (logName
, statementName
, fn
) => {
112 const _wrapScope
= _fileScope(logName
);
113 return (...args
) => {
114 const startTimestampMs
= performance
.now();
115 const rows
= fn(...args
);
116 DatabaseSQLite
._deOphidiate(rows
);
117 const elapsedTimeMs
= performance
.now() - startTimestampMs
;
118 this.logger
.debug(_wrapScope
, 'complete', { statementName
, elapsedTimeMs
});
122 const wrapRun
= (logName
, statementName
, fn
) => {
123 const _wrapScope
= _fileScope(logName
);
124 return (...args
) => {
125 const startTimestampMs
= performance
.now();
126 const result
= fn(...args
);
127 const elapsedTimeMs
= performance
.now() - startTimestampMs
;
128 this.logger
.debug(_wrapScope
, 'complete', { ...result
, statementName
, elapsedTimeMs
});
129 result
.duration
= elapsedTimeMs
;
135 for (const f
of fs
.readdirSync(sqlDir
)) {
136 const fPath
= path
.join(sqlDir
, f
);
137 const { name: fName
, ext: fExt
} = path
.parse(f
);
138 // eslint-disable-next-line security/detect-non-literal-fs-filename
139 const stat
= fs
.statSync(fPath
);
141 || fExt
.toLowerCase() !== '.sql') {
144 // eslint-disable-next-line security/detect-non-literal-fs-filename
145 const fSql
= fs
.readFileSync(fPath
, { encoding: 'utf8' });
146 const statementName
= Database
._camelfy(fName
.toLowerCase(), '-');
149 statement
= this.db
.prepare(fSql
);
151 /* istanbul ignore next */
152 this.logger
.error(_scope
, 'failed to prepare statement', { error: e
, file: f
});
153 /* istanbul ignore next */
156 // eslint-disable-next-line security/detect-object-injection
157 this.statement
[statementName
] = statement
;
158 const { get: origGet
, all: origAll
, run: origRun
} = statement
;
159 statement
.get = wrapFetch('SQLite:get', statementName
, origGet
.bind(statement
));
160 statement
.all
= wrapFetch('SQLite:all', statementName
, origAll
.bind(statement
));
161 statement
.run
= wrapRun('SQLite:run', statementName
, origRun
.bind(statement
));
163 this.statement
._optimize
= this.db
.prepare('SELECT * FROM pragma_optimize(0x03)');
165 this.logger
.debug(_scope
, 'statements initialized', { statements: Object
.keys(this.statement
).length
});
169 static _deOphidiate(rows
) {
170 const rowsIsArray
= Array
.isArray(rows
);
174 const exemplaryRow
= rows
[0];
175 for (const prop
in exemplaryRow
) {
176 const camel
= Database
._camelfy(prop
);
177 if (!(camel
in exemplaryRow
)) {
178 for (const d
of rows
) {
179 // eslint-disable-next-line security/detect-object-injection
181 // eslint-disable-next-line security/detect-object-injection
186 return rowsIsArray
? rows : rows
[0];
191 return this.db
.prepare('SELECT major, minor, patch FROM _meta_schema_version ORDER BY major DESC, minor DESC, patch DESC LIMIT 1').get();
196 const _scope
= _fileScope('healthCheck');
197 this.logger
.debug(_scope
, 'called', {});
199 throw new DBErrors
.UnexpectedResult('database is not open');
201 return { open: this.db
.open
};
205 _engineInfo(result
) {
206 if (result
.changes
) {
207 this.changesSinceLastOptimize
+= BigInt(result
.changes
);
211 changes: Number(result
.changes
),
212 lastInsertRowid: result
.lastInsertRowid
,
223 const _scope
= _fileScope('_optimize');
225 if (this.optimizeAfterChanges
226 && this.changesSinceLastOptimize
>= this.optimizeAfterChanges
) {
227 const optimize
= this.statement
._optimize
.all();
228 this.logger
.debug(_scope
, 'optimize', { optimize
});
229 this.db
.pragma('optimize');
230 this.changesSinceLastOptimize
= BigInt(0);
235 _purgeTables(really
) {
239 'topic_fetch_in_progress',
241 'verification_in_progress',
243 'subscription_delivery_in_progress',
244 ].forEach((table
) => {
245 const result
= this.db
.prepare(`DELETE FROM ${table}`).run();
246 this.logger
.debug(_fileScope('_purgeTables'), 'success', { table
, result
});
257 transaction(dbCtx
, fn
) {
258 dbCtx
= dbCtx
|| this.db
;
259 return dbCtx
.transaction(fn
)();
263 authenticationSuccess(dbCtx
, identifier
) {
264 const _scope
= _fileScope('authenticationSuccess');
265 this.logger
.debug(_scope
, 'called', { identifier
});
269 result
= this.statement
.authenticationSuccess
.run({ identifier
});
270 if (result
.changes
!= 1) {
271 throw new DBErrors
.UnexpectedResult('did not update authentication success');
274 this.logger
.error(_scope
, 'failed', { error: e
, identifier
});
280 authenticationGet(dbCtx
, identifier
) {
281 const _scope
= _fileScope('authenticationGet');
282 this.logger
.debug(_scope
, 'called', { identifier
});
285 return this.statement
.authenticationGet
.get({ identifier
});
287 this.logger
.error(_scope
, 'failed', { error: e
, identifier
});
293 authenticationUpsert(dbCtx
, identifier
, credential
, otpKey
) {
294 const _scope
= _fileScope('authenticationUpsert');
295 const scrubbedCredential
= '*'.repeat((credential
|| '').length
);
296 const scrubbedOTPKey
= '*'.repeat((otpKey
|| '').length
) || null;
297 this.logger
.debug(_scope
, 'called', { identifier
, scrubbedCredential
, scrubbedOTPKey
});
301 result
= this.statement
.authenticationUpsert
.run({ identifier
, credential
, otpKey
});
302 if (result
.changes
!= 1) {
303 throw new DBErrors
.UnexpectedResult('did not upsert authentication');
306 this.logger
.error(_scope
, 'failed', { error: e
, identifier
, scrubbedCredential
, scrubbedOTPKey
});
312 authenticationUpdateOTPKey(dbCtx
, identifier
, otpKey
) {
313 const _scope
= _fileScope('authenticationUpdateOTPKey');
314 const scrubbedOTPKey
= '*'.repeat((otpKey
|| '').length
) || null;
315 this.logger
.debug(_scope
, 'called', { identifier
, scrubbedOTPKey
});
319 result
= this.statement
.authenticationUpdateOtpKey
.run({ identifier
, otpKey
});
320 if (result
.changes
!= 1) {
321 throw new DBErrors
.UnexpectedResult('did not update authentication otp key');
324 this.logger
.error(_scope
, 'failed', { error: e
, identifier
, scrubbedOTPKey
});
330 authenticationUpdateCredential(dbCtx
, identifier
, credential
) {
331 const _scope
= _fileScope('authenticationUpdateCredential');
332 const scrubbedCredential
= '*'.repeat((credential
|| '').length
);
333 this.logger
.debug(_scope
, 'called', { identifier
, scrubbedCredential
});
337 result
= this.statement
.authenticationUpdateCredential
.run({ identifier
, credential
});
338 if (result
.changes
!= 1) {
339 throw new DBErrors
.UnexpectedResult('did not update authentication credential');
342 this.logger
.error(_scope
, 'failed', { error: e
, identifier
, scrubbedCredential
});
349 * Converts engine subscription fields to native types.
350 * @param {object} data subscription data
351 * @returns {object} data
353 static _subscriptionDataToNative(data
) {
355 ['created', 'verified', 'expires', 'contentDelivered'].forEach((field
) => {
356 // eslint-disable-next-line security/detect-object-injection
357 data
[field
] = epochToDate(data
[field
]);
364 subscriptionsByTopicId(dbCtx
, topicId
) {
365 const _scope
= _fileScope('subscriptionsByTopicId');
366 this.logger
.debug(_scope
, 'called', { topicId
});
369 const subscriptions
= this.statement
.subscriptionsByTopicId
.all({ topicId
});
370 return subscriptions
.map((s
) => DatabaseSQLite
._subscriptionDataToNative(s
));
372 this.logger
.error(_scope
, 'failed', { error: e
, topicId
});
378 subscriptionCountByTopicUrl(dbCtx
, topicUrl
) {
379 const _scope
= _fileScope('subscriptionCountByTopicUrl');
380 this.logger
.debug(_scope
, 'called', { topicUrl
});
383 return this.statement
.subscriptionCountByTopicUrl
.get({ topicUrl
});
385 this.logger
.error(_scope
, 'failed', { error: e
, topicUrl
});
391 subscriptionDelete(dbCtx
, callback
, topicId
) {
392 const _scope
= _fileScope('subscriptionDelete');
393 this.logger
.debug(_scope
, 'called', { callback
, topicId
});
396 const result
= this.statement
.subscriptionDelete
.run({ callback
, topicId
});
397 if (result
.changes
!= 1) {
398 throw new DBErrors
.UnexpectedResult('did not delete subscription');
400 return this._engineInfo(result
);
402 this.logger
.error(_scope
, 'failed', { error: e
, callback
, topicId
});
408 subscriptionDeleteExpired(dbCtx
, topicId
) {
409 const _scope
= _fileScope('subscriptionDeleteExpired');
410 this.logger
.debug(_scope
, 'called', { topicId
});
413 const result
= this.statement
.subscriptionDeleteExpired
.run({ topicId
});
414 this.logger
.debug(_scope
, 'success', { topicId
, deleted: result
.changes
});
415 return this._engineInfo(result
);
417 this.logger
.error(_scope
, 'failed', { error: e
, topicId
});
423 subscriptionDeliveryClaim(dbCtx
, wanted
, claimTimeoutSeconds
, claimant
) {
424 const _scope
= _fileScope('subscriptionDeliveryClaim');
425 this.logger
.debug(_scope
, 'called', { wanted
, claimTimeoutSeconds
, claimant
});
429 this.db
.transaction(() => {
430 subscriptionIds
= this.statement
.subscriptionDeliveryNeeded
.all({ wanted
}).map((claim
) => claim
.id
);
431 subscriptionIds
.forEach((subscriptionId
) => {
432 const result
= this.statement
.subscriptionDeliveryClaimById
.run({ subscriptionId
, claimTimeoutSeconds
, claimant
});
433 if (result
.changes
!= 1) {
434 throw new DBErrors
.UnexpectedResult('did not claim subscription delivery');
438 return subscriptionIds
;
440 this.logger
.error(_scope
, 'failed', { error: e
, wanted
, claimTimeoutSeconds
, claimant
, subscriptionIds
});
446 subscriptionDeliveryClaimById(dbCtx
, subscriptionId
, claimTimeoutSeconds
, claimant
) {
447 const _scope
= _fileScope('subscriptionDeliveryClaimById');
448 this.logger
.debug(_scope
, 'called', { subscriptionId
, claimTimeoutSeconds
, claimant
});
451 const result
= this.statement
.subscriptionDeliveryClaimById
.run({ subscriptionId
, claimTimeoutSeconds
, claimant
});
452 if (result
.changes
!= 1) {
453 throw new DBErrors
.UnexpectedResult('did not claim subscription delivery');
455 return this._engineInfo(result
);
457 this.logger
.error(_scope
, 'failed', { error: e
, subscriptionId
, claimTimeoutSeconds
, claimant
});
463 subscriptionDeliveryComplete(dbCtx
, callback
, topicId
, topicContentUpdated
) {
464 const _scope
= _fileScope('subscriptionDeliveryComplete');
465 this.logger
.debug(_scope
, 'called', { callback
, topicId
, topicContentUpdated
});
469 this.db
.transaction(() => {
470 topicContentUpdated
= dateToEpoch(topicContentUpdated
);
471 result
= this.statement
.subscriptionDeliverySuccess
.run({ callback
, topicId
, topicContentUpdated
});
472 if (result
.changes
!= 1) {
473 throw new DBErrors
.UnexpectedResult('did not set subscription delivery success');
475 result
= this.statement
.subscriptionDeliveryDone
.run({ callback
, topicId
});
476 if (result
.changes
!= 1) {
477 throw new DBErrors
.UnexpectedResult('did not complete subscription delivery');
480 return this._engineInfo(result
);
482 this.logger
.error(_scope
, 'failed', { error: e
, callback
, topicId
, topicContentUpdated
});
488 subscriptionDeliveryGone(dbCtx
, callback
, topicId
) {
489 const _scope
= _fileScope('subscriptionDeliveryGone');
490 this.logger
.debug(_scope
, 'called', { callback
, topicId
});
494 this.db
.transaction(() => {
495 result
= this.statement
.subscriptionDelete
.run({ callback
, topicId
});
496 if (result
.changes
!= 1) {
497 throw new DBErrors
.UnexpectedResult('did not delete subscription');
499 // Delete cascades to delivery
500 // result = this.statement.subscriptionDeliveryDone.run({ callback, topicId });
501 // if (result.changes != 1) {
502 // throw new DBErrors.UnexpectedResult('did not complete subscription delivery');
505 return this._engineInfo(result
);
507 this.logger
.error(_scope
, 'failed', { error: e
, callback
, topicId
});
513 subscriptionDeliveryIncomplete(dbCtx
, callback
, topicId
, retryDelays
= [60]) {
514 const _scope
= _fileScope('subscriptionDeliveryIncomplete');
515 this.logger
.debug(_scope
, 'called', { callback
, topicId
, retryDelays
});
519 this.db
.transaction(() => {
520 const { currentAttempt
} = this.statement
.subscriptionDeliveryAttempts
.get({ callback
, topicId
});
521 const nextAttemptDelaySeconds
= common
.attemptRetrySeconds(currentAttempt
, retryDelays
);
522 result
= this.statement
.subscriptionDeliveryFailure
.run({ nextAttemptDelaySeconds
, callback
, topicId
});
523 if (result
.changes
!= 1) {
524 throw new DBErrors
.UnexpectedResult('did not set delivery failure');
526 result
= this.statement
.subscriptionDeliveryDone
.run({ callback
, topicId
});
527 if (result
.changes
!= 1) {
528 throw new DBErrors
.UnexpectedResult('did not complete subscription delivery');
532 this.logger
.error(_scope
, 'failed', { error: e
, callback
, topicId
});
538 subscriptionGet(dbCtx
, callback
, topicId
) {
539 const _scope
= _fileScope('subscriptionGet');
540 this.logger
.debug(_scope
, 'called', { callback
, topicId
});
544 subscription
= this.statement
.subscriptionGet
.get({ callback
, topicId
});
545 return DatabaseSQLite
._subscriptionDataToNative(subscription
);
547 this.logger
.error(_scope
, 'failed', { error: e
, callback
, topicId
});
553 subscriptionGetById(dbCtx
, subscriptionId
) {
554 const _scope
= _fileScope('subscriptionGetById');
555 this.logger
.debug(_scope
, 'called', { subscriptionId
});
559 subscription
= this.statement
.subscriptionGetById
.get({ subscriptionId
});
560 return DatabaseSQLite
._subscriptionDataToNative(subscription
);
562 this.logger
.error(_scope
, 'failed', { error: e
, subscriptionId
});
568 subscriptionUpdate(dbCtx
, data
) {
569 const _scope
= _fileScope('subscriptionUpdate');
570 this.logger
.debug(_scope
, 'called', { data
});
572 const subscriptionData
= {
576 this._subscriptionUpdateDataValidate(subscriptionData
);
579 const result
= this.statement
.subscriptionUpdate
.run(subscriptionData
);
580 if (result
.changes
!= 1) {
581 throw new DBErrors
.UnexpectedResult('did not update subscription');
584 this.logger
.error(_scope
, 'failed', { error: e
, subscriptionData
});
590 subscriptionUpsert(dbCtx
, data
) {
591 const _scope
= _fileScope('subscriptionUpsert');
592 this.logger
.debug(_scope
, 'called', { ...data
});
594 const subscriptionData
= {
596 httpRemoteAddr: null,
600 this._subscriptionUpsertDataValidate(subscriptionData
);
604 result
= this.statement
.subscriptionUpsert
.run(subscriptionData
);
605 if (result
.changes
!= 1) {
606 throw new DBErrors
.UnexpectedResult('did not upsert subscription');
608 return this._engineInfo(result
);
610 this.logger
.error(_scope
, 'failed', { error: e
, subscriptionData
});
616 topicDeleted(dbCtx
, topicId
) {
617 const _scope
= _fileScope('topicDeleted');
618 this.logger
.debug(_scope
, 'called', { topicId
});
622 result
= this.statement
.topicDeleted
.run({ topicId
});
623 if (result
.changes
!= 1) {
624 throw new DBErrors
.UnexpectedResult('did not update topic as deleted');
627 this.logger
.error(_scope
, 'failed', { error: e
, topicId
});
633 topicFetchClaim(dbCtx
, wanted
, claimTimeoutSeconds
, claimant
) {
634 const _scope
= _fileScope('topicFetchClaim');
635 this.logger
.debug(_scope
, 'called', { wanted
, claimTimeoutSeconds
});
639 this.db
.transaction(() => {
640 topicIds
= this.statement
.topicContentFetchNeeded
.all({ wanted
}).map((claim
) => claim
.id
);
641 topicIds
.forEach((topicId
) => {
642 const result
= this.statement
.topicContentFetchClaimById
.run({ topicId
, claimTimeoutSeconds
, claimant
});
643 if (result
.changes
!= 1) {
644 throw new DBErrors
.UnexpectedResult('did not claim topic fetch');
650 this.logger
.error(_scope
, 'failed to claim topics for fetch', { error: e
, wanted
, claimTimeoutSeconds
, claimant
, topicIds
});
656 topicFetchClaimById(dbCtx
, topicId
, claimTimeoutSeconds
, claimant
) {
657 const _scope
= _fileScope('topicFetchClaimById');
658 this.logger
.debug(_scope
, 'called', { topicId
, claimTimeoutSeconds
, claimant
});
662 result
= this.statement
.topicContentFetchClaimById
.run({ topicId
, claimTimeoutSeconds
, claimant
});
663 if (result
.changes
!= 1) {
664 throw new DBErrors
.UnexpectedResult('did not claim topic fetch');
666 return this._engineInfo(result
);
668 this.logger
.error(_scope
, 'failed to claim topics for fetch', { error: e
, topicId
, claimTimeoutSeconds
, claimant
});
674 topicFetchComplete(dbCtx
, topicId
) {
675 const _scope
= _fileScope('topicFetchComplete');
676 this.logger
.debug(_scope
, 'called', { topicId
});
680 this.db
.transaction(() => {
681 result
= this.statement
.topicAttemptsReset
.run({ topicId
, forever: EPOCH_FOREVER
});
682 if (result
.changes
!= 1) {
683 throw new DBErrors
.UnexpectedResult('did not reset topic attempts');
685 result
= this.statement
.topicContentFetchDone
.run({ topicId
});
686 if (result
.changes
!= 1) {
687 throw new DBErrors
.UnexpectedResult('did not release topic fetch');
690 return this._engineInfo(result
);
692 this.logger
.error(_scope
, 'failed', { error: e
, result
, topicId
});
698 topicFetchIncomplete(dbCtx
, topicId
, retryDelays
= [60]) {
699 const _scope
= _fileScope('topicFetchIncomplete');
700 this.logger
.debug(_scope
, 'called', { topicId
});
704 this.db
.transaction(() => {
705 const { contentFetchAttemptsSinceSuccess: currentAttempt
} = this.statement
.topicAttempts
.get({ topicId
});
706 const nextAttemptDelaySeconds
= common
.attemptRetrySeconds(currentAttempt
, retryDelays
);
707 result
= this.statement
.topicAttemptsIncrement
.run({ topicId
, nextAttemptDelaySeconds
});
708 if (result
.changes
!= 1) {
709 throw new DBErrors
.UnexpectedResult('did not set topic attempts');
711 result
= this.statement
.topicContentFetchDone
.run({ topicId
});
712 if (result
.changes
!= 1) {
713 throw new DBErrors
.UnexpectedResult('did not release topic fetch');
717 return this._engineInfo(result
);
719 this.logger
.error(_scope
, 'failed', { error: e
, result
, topicId
});
725 topicFetchRequested(dbCtx
, topicId
) {
726 const _scope
= _fileScope('topicFetchRequested');
727 this.logger
.debug(_scope
, 'called', { topicId
});
731 result
= this.statement
.topicContentFetchRequested
.run({ topicId
});
732 if (result
.changes
!= 1) {
733 throw new DBErrors
.UnexpectedResult('did not set topic fetch requested');
735 return this._engineInfo(result
);
737 this.logger
.error(_scope
, 'failed', { error: e
, topicId
});
744 * Converts engine topic fields to native types.
745 * @param {object} data topic
746 * @returns {object} topic data
748 static _topicDataToNative(data
) {
750 data
.isActive
= !!data
.isActive
;
751 data
.isDeleted
= !!data
.isDeleted
;
752 ['created', 'lastPublish', 'contentFetchNextAttempt', 'contentUpdated'].forEach((field
) => {
753 // eslint-disable-next-line security/detect-object-injection
754 data
[field
] = epochToDate(data
[field
]);
761 // eslint-disable-next-line no-unused-vars
763 const _scope
= _fileScope('topicGetAll');
764 this.logger
.debug(_scope
, 'called');
768 topics
= this.statement
.topicGetInfoAll
.all();
770 this.logger
.error(_scope
, 'failed', { error: e
, topics
});
775 .map(DatabaseSQLite
._topicDataToNative
)
776 .map(this._topicDefaults
.bind(this));
782 topicGetById(dbCtx
, topicId
, applyDefaults
= true) {
783 const _scope
= _fileScope('topicGetById');
784 this.logger
.debug(_scope
, 'called', { topicId
});
788 topic
= this.statement
.topicGetById
.get({ topicId
});
789 DatabaseSQLite
._topicDataToNative(topic
);
791 topic
= this._topicDefaults(topic
);
795 this.logger
.error(_scope
, 'failed', { error: e
, topic
, topicId
});
801 topicGetByUrl(dbCtx
, topicUrl
, applyDefaults
= true) {
802 const _scope
= _fileScope('topicGetByUrl');
803 this.logger
.debug(_scope
, 'called', { topicUrl
});
807 topic
= this.statement
.topicGetByUrl
.get({ topicUrl
});
808 DatabaseSQLite
._topicDataToNative(topic
);
810 topic
= this._topicDefaults(topic
);
814 this.logger
.error(_scope
, 'failed', { error: e
, topic
, topicUrl
});
820 topicGetContentById(dbCtx
, topicId
) {
821 const _scope
= _fileScope('topicGetContentById');
822 this.logger
.debug(_scope
, 'called', { topicId
});
826 topic
= this.statement
.topicGetContentById
.get({ topicId
});
827 DatabaseSQLite
._topicDataToNative(topic
);
828 return this._topicDefaults(topic
);
830 this.logger
.error(_scope
, 'failed', { error: e
, topic
, topicId
});
836 topicPendingDelete(dbCtx
, topicId
) {
837 const _scope
= _fileScope('topicPendingDelete');
838 this.logger
.debug(_scope
, 'called', { topicId
});
841 this.db
.transaction(() => {
842 const topic
= this.statement
.topicGetById
.get({ topicId
});
843 if (!topic
.isDeleted
) {
844 this.logger
.debug(_scope
, 'topic not set deleted, not deleting', { topicId
});
848 const { count: subscriberCount
} = this.statement
.subscriptionCountByTopicUrl
.get({ topicUrl: topic
.url
});
849 if (subscriberCount
) {
850 this.logger
.debug(_scope
, 'topic has subscribers, not deleting', { topicId
, subscriberCount
});
854 const result
= this.statement
.topicDeleteById
.run({ topicId
});
855 if (result
.changes
!== 1) {
856 throw new DBErrors
.UnexpectedResult('did not delete topic');
859 this.logger
.debug(_scope
, 'success', { topicId
});
861 this.logger
.error(_scope
, 'failed', { error: e
, topicId
});
867 topicPublishHistory(dbCtx
, topicId
, days
) {
868 const _scope
= _fileScope('topicPublishHistory');
869 this.logger
.debug(_scope
, 'called', { topicId
, days
});
871 const events
= this.statement
.topicPublishHistory
.all({ topicId
, daysAgo: days
});
872 const history
= Array
.from({ length: days
}, () => 0);
873 // eslint-disable-next-line security/detect-object-injection
874 events
.forEach(({ daysAgo
, contentUpdates
}) => history
[daysAgo
] = Number(contentUpdates
));
880 topicSet(dbCtx
, data
) {
881 const _scope
= _fileScope('topicSet');
882 this.logger
.debug(_scope
, 'called', data
);
884 const topicSetData
= {
885 publisherValidationUrl: null,
886 leaseSecondsPreferred: null,
887 leaseSecondsMin: null,
888 leaseSecondsMax: null,
894 this._topicSetDataValidate(topicSetData
);
895 result
= this.statement
.topicUpsert
.run(topicSetData
);
896 if (result
.changes
!= 1) {
897 throw new DBErrors
.UnexpectedResult('did not set topic data');
899 return this._engineInfo(result
);
901 this.logger
.error(_scope
, 'failed', { error: e
, result
});
907 topicSetContent(dbCtx
, data
) {
908 const _scope
= _fileScope('topicSetContent');
909 const topicSetContentData
= {
912 httpLastModified: null,
916 ...topicSetContentData
,
917 content: common
.logTruncate(topicSetContentData
.content
, 100),
919 this.logger
.debug(_scope
, 'called', logData
);
923 this._topicSetContentDataValidate(topicSetContentData
);
924 result
= this.statement
.topicSetContent
.run(topicSetContentData
);
925 logData
.result
= result
;
926 if (result
.changes
!= 1) {
927 throw new DBErrors
.UnexpectedResult('did not set topic content');
929 result
= this.statement
.topicSetContentHistory
.run({
930 topicId: data
.topicId
,
931 contentHash: data
.contentHash
,
932 contentSize: data
.content
.length
,
934 if (result
.changes
!= 1) {
935 throw new DBErrors
.UnexpectedResult('did not set topic content history');
937 return this._engineInfo(result
);
939 this.logger
.error(_scope
, 'failed', { error: e
, ...logData
});
945 topicUpdate(dbCtx
, data
) {
946 const _scope
= _fileScope('topicUpdate');
947 this.logger
.debug(_scope
, 'called', { data
});
950 leaseSecondsPreferred: null,
951 leaseSecondsMin: null,
952 leaseSecondsMax: null,
953 publisherValidationUrl: null,
957 this._topicUpdateDataValidate(topicData
);
960 const result
= this.statement
.topicUpdate
.run(topicData
);
961 if (result
.changes
!= 1) {
962 throw new DBErrors
.UnexpectedResult('did not update topic');
965 this.logger
.error(_scope
, 'failed', { error: e
, topicData
});
971 verificationClaim(dbCtx
, wanted
, claimTimeoutSeconds
, claimant
) {
972 const _scope
= _fileScope('verificationClaim');
973 this.logger
.debug(_scope
, 'called', { wanted
, claimTimeoutSeconds
});
977 this.db
.transaction(() => {
978 verificationIds
= this.statement
.verificationNeeded
.all({ wanted
}).map((claim
) => claim
.id
);
979 verificationIds
.forEach((verificationId
) => {
980 const result
= this.statement
.verificationClaimById
.run({ verificationId
, claimTimeoutSeconds
, claimant
});
981 if (result
.changes
!= 1) {
982 throw new DBErrors
.UnexpectedResult('did not claim verification');
986 return verificationIds
;
988 this.logger
.error(_scope
, 'failed to claim verifications', { wanted
, claimTimeoutSeconds
});
994 verificationClaimById(dbCtx
, verificationId
, claimTimeoutSeconds
, claimant
) {
995 const _scope
= _fileScope('verificationClaimById');
996 this.logger
.debug(_scope
, 'called', { verificationId
, claimTimeoutSeconds
, claimant
});
1000 result
= this.statement
.verificationClaimById
.run({ verificationId
, claimTimeoutSeconds
, claimant
});
1001 if (result
.changes
!= 1) {
1002 throw new DBErrors
.UnexpectedResult('did not claim verification');
1004 return this._engineInfo(result
);
1006 this.logger
.error(_scope
, 'failed to claim verification', { error: e
, verificationId
, claimTimeoutSeconds
, claimant
});
1012 verificationComplete(dbCtx
, verificationId
, callback
, topicId
) {
1013 const _scope
= _fileScope('verificationComplete');
1014 this.logger
.debug(_scope
, 'called', { verificationId
});
1018 this.db
.transaction(() => {
1019 result
= this.statement
.verificationScrub
.run({ verificationId
, callback
, topicId
});
1020 if (result
.changes
< 1) {
1021 throw new DBErrors
.UnexpectedResult('did not remove verifications');
1025 this.logger
.error(_scope
, 'failed', { verificationId
});
1028 return this._engineInfo(result
);
1033 * Converts engine verification fields to native types.
1034 * @param {object} data verification
1036 static _verificationDataToNative(data
) {
1038 data
.isPublisherValidated
= !!data
.isPublisherValidated
;
1043 verificationGetById(dbCtx
, verificationId
) {
1044 const _scope
= _fileScope('verificationGetById');
1045 this.logger
.debug(_scope
, 'called', { verificationId
});
1049 verification
= this.statement
.verificationGetById
.get({ verificationId
});
1050 DatabaseSQLite
._verificationDataToNative(verification
);
1051 return verification
;
1053 this.logger
.error(_scope
, 'failed', { error: e
, verificationId
});
1059 verificationIncomplete(dbCtx
, verificationId
, retryDelays
= [60]) {
1060 const _scope
= _fileScope('verificationIncomplete');
1061 this.logger
.debug(_scope
, 'called', { verificationId
});
1065 this.db
.transaction(() => {
1066 const { attempts: currentAttempt
} = this.statement
.verificationAttempts
.get({ verificationId
});
1067 const nextAttemptDelaySeconds
= common
.attemptRetrySeconds(currentAttempt
, retryDelays
);
1068 result
= this.statement
.verificationAttemptsIncrement
.run({ verificationId
, nextAttemptDelaySeconds
});
1069 if (result
.changes
!= 1) {
1070 throw new DBErrors
.UnexpectedResult('did not increment verification attempts');
1072 result
= this.statement
.verificationDone
.run({ verificationId
});
1073 if (result
.changes
!= 1) {
1074 throw new DBErrors
.UnexpectedResult('did not release verification in progress');
1078 return this._engineInfo(result
);
1080 this.logger
.error(_scope
, 'failed', { error: e
, result
, verificationId
});
1087 * Convert native verification fields to engine types.
1088 * @param {object} data verification
1090 static _verificationDataToEngine(data
) {
1092 data
.isPublisherValidated
= data
.isPublisherValidated
? 1 : 0;
1097 verificationInsert(dbCtx
, verification
) {
1098 const _scope
= _fileScope('verificationInsert');
1099 this.logger
.debug(_scope
, 'called', { verification
});
1101 const verificationData
= {
1103 httpRemoteAddr: null,
1109 let result
, verificationId
;
1111 this._verificationDataValidate(verificationData
);
1112 DatabaseSQLite
._verificationDataToEngine(verificationData
);
1113 result
= this.statement
.verificationInsert
.run(verificationData
);
1114 if (result
.changes
!= 1) {
1115 throw new DBErrors
.UnexpectedResult('did not insert verification');
1117 verificationId
= result
.lastInsertRowid
;
1118 this.logger
.debug(_scope
, 'inserted verification', { verificationId
});
1120 return verificationId
;
1122 this.logger
.error(_scope
, 'failed', { error: e
, verificationData
});
1128 verificationRelease(dbCtx
, verificationId
) {
1129 const _scope
= _fileScope('verificationRelease');
1130 this.logger
.debug(_scope
, 'called', { verificationId
});
1134 result
= this.statement
.verificationDone
.run({ verificationId
});
1135 if (result
.changes
!= 1) {
1136 throw new DBErrors
.UnexpectedResult('did not release verification');
1139 this.logger
.error(_scope
, 'failed', { error: e
, verificationId
});
1145 verificationUpdate(dbCtx
, verificationId
, data
) {
1146 const _scope
= _fileScope('verificationUpdate');
1147 this.logger
.debug(_scope
, 'called', { verificationId
, data
});
1149 const verificationData
= {
1157 this._verificationUpdateDataValidate(verificationData
);
1158 DatabaseSQLite
._verificationDataToEngine(verificationData
);
1159 result
= this.statement
.verificationUpdate
.run(verificationData
);
1160 if (result
.changes
!= 1) {
1161 throw new DBErrors
.UnexpectedResult('did not update verification');
1164 this.logger
.error(_scope
, 'failed', { error: e
, verificationData
});
1170 verificationValidated(dbCtx
, verificationId
) {
1171 const _scope
= _fileScope('verificationValidated');
1172 this.logger
.debug(_scope
, 'called', { verificationId
});
1176 result
= this.statement
.verificationValidate
.run({ verificationId
});
1177 if (result
.changes
!= 1) {
1178 throw new DBErrors
.UnexpectedResult('did not set verification validation');
1181 this.logger
.error(_scope
, 'failed', { error: e
, verificationId
});
1189 module
.exports
= DatabaseSQLite
;