From f793d88a96b9495172f5dd0c342f2036c902902d Mon Sep 17 00:00:00 2001 From: Justin Wind Date: Wed, 23 Feb 2022 15:09:47 -0800 Subject: [PATCH] track topic update history --- src/db/postgres/index.js | 6 ++++- src/db/postgres/sql/schema/1.0.3/apply.sql | 13 ++++++++++ src/db/postgres/sql/schema/1.0.3/revert.sql | 7 +++++ .../sql/topic-set-content-history.sql | 5 ++++ src/db/sqlite/index.js | 6 ++++- src/db/sqlite/sql/schema/1.0.3/apply.sql | 13 ++++++++++ src/db/sqlite/sql/schema/1.0.3/revert.sql | 7 +++++ .../sqlite/sql/topic-set-content-history.sql | 5 ++++ test/src/db/postgres.js | 26 +++++++++++++++++-- test/src/db/sqlite.js | 25 ++++++++++++++++-- 10 files changed, 107 insertions(+), 6 deletions(-) create mode 100644 src/db/postgres/sql/schema/1.0.3/apply.sql create mode 100644 src/db/postgres/sql/schema/1.0.3/revert.sql create mode 100644 src/db/postgres/sql/topic-set-content-history.sql create mode 100644 src/db/sqlite/sql/schema/1.0.3/apply.sql create mode 100644 src/db/sqlite/sql/schema/1.0.3/revert.sql create mode 100644 src/db/sqlite/sql/topic-set-content-history.sql diff --git a/src/db/postgres/index.js b/src/db/postgres/index.js index a90e0cb..d950d4a 100644 --- a/src/db/postgres/index.js +++ b/src/db/postgres/index.js @@ -30,7 +30,7 @@ const schemaVersionsSupported = { max: { major: 1, minor: 0, - patch: 2, + patch: 3, }, }; @@ -904,6 +904,10 @@ class DatabasePostgres extends Database { if (result.rowCount != 1) { throw new DBErrors.UnexpectedResult('did not set topic content'); } + result = await dbCtx.result(this.statement.topicSetContentHistory, { topicId: data.topicId, contentHash: data.contentHash, contentSize: data.content.length }); + if (result.rowCount != 1) { + throw new DBErrors.UnexpectedResult('did not set topic content history'); + } this.logger.debug(_scope, 'success', { ...logData }); return this._engineInfo(result); } catch (e) { diff --git a/src/db/postgres/sql/schema/1.0.3/apply.sql b/src/db/postgres/sql/schema/1.0.3/apply.sql new file mode 100644 index 0000000..04b49c7 --- /dev/null +++ b/src/db/postgres/sql/schema/1.0.3/apply.sql @@ -0,0 +1,13 @@ +BEGIN; + -- Track all content updates over time. + CREATE TABLE topic_content_history ( + topic_id UUID NOT NULL REFERENCES topic(id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED, + content_updated TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(), + content_size INTEGER NOT NULL, + content_hash TEXT NOT NULL + ); + CREATE INDEX topic_content_history_topic_id_idx ON topic_content_history(topic_id); + CREATE INDEX topic_content_history_content_updated_idx ON topic_content_history(content_updated); + + INSERT INTO _meta_schema_version (major, minor, patch) VALUES (1, 0, 3); +COMMIT; diff --git a/src/db/postgres/sql/schema/1.0.3/revert.sql b/src/db/postgres/sql/schema/1.0.3/revert.sql new file mode 100644 index 0000000..a838ade --- /dev/null +++ b/src/db/postgres/sql/schema/1.0.3/revert.sql @@ -0,0 +1,7 @@ +BEGIN; + DROP INDEX topic_content_history_topic_id_idx; + DROP INDEX topic_content_history_content_updated_idx; + DROP TABLE topic_content_history; + + DELETE FROM _meta_schema_version WHERE major = 1 AND minor = 0 AND patch = 3; +COMMIT; diff --git a/src/db/postgres/sql/topic-set-content-history.sql b/src/db/postgres/sql/topic-set-content-history.sql new file mode 100644 index 0000000..d24f844 --- /dev/null +++ b/src/db/postgres/sql/topic-set-content-history.sql @@ -0,0 +1,5 @@ +-- +INSERT INTO topic_content_history + (topic_id, content_size, content_hash) +VALUES + ($(topicId), $(contentSize), $(contentHash)) diff --git a/src/db/sqlite/index.js b/src/db/sqlite/index.js index 3fae300..b8826a4 100644 --- a/src/db/sqlite/index.js +++ b/src/db/sqlite/index.js @@ -20,7 +20,7 @@ const schemaVersionsSupported = { max: { major: 1, minor: 0, - patch: 2, + patch: 3, }, }; @@ -869,6 +869,10 @@ class DatabaseSQLite extends Database { if (result.changes != 1) { throw new DBErrors.UnexpectedResult('did not set topic content'); } + result = this.statement.topicSetContentHistory.run({ topicId: data.topicId, contentHash: data.contentHash, contentSize: data.content.length }); + if (result.changes != 1) { + throw new DBErrors.UnexpectedResult('did not set topic content history'); + } return this._engineInfo(result); } catch (e) { this.logger.error(_scope, 'failed', { error: e, ...logData }); diff --git a/src/db/sqlite/sql/schema/1.0.3/apply.sql b/src/db/sqlite/sql/schema/1.0.3/apply.sql new file mode 100644 index 0000000..0a642c2 --- /dev/null +++ b/src/db/sqlite/sql/schema/1.0.3/apply.sql @@ -0,0 +1,13 @@ +BEGIN; + -- Track all content updates over time. + CREATE TABLE topic_content_history ( + topic_id INTEGER NOT NULL REFERENCES topic(id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED, + content_updated INTEGER NOT NULL DEFAULT (strftime('%s', 'now')), + content_size INTEGER NOT NULL, + content_hash TEXT NOT NULL + ); + CREATE INDEX topic_content_history_topic_id_idx ON topic_content_history(topic_id); + CREATE INDEX topic_content_history_content_updated_idx ON topic_content_history(content_updated); + + INSERT INTO _meta_schema_version (major, minor, patch) VALUES (1, 0, 3); +COMMIT; diff --git a/src/db/sqlite/sql/schema/1.0.3/revert.sql b/src/db/sqlite/sql/schema/1.0.3/revert.sql new file mode 100644 index 0000000..a838ade --- /dev/null +++ b/src/db/sqlite/sql/schema/1.0.3/revert.sql @@ -0,0 +1,7 @@ +BEGIN; + DROP INDEX topic_content_history_topic_id_idx; + DROP INDEX topic_content_history_content_updated_idx; + DROP TABLE topic_content_history; + + DELETE FROM _meta_schema_version WHERE major = 1 AND minor = 0 AND patch = 3; +COMMIT; diff --git a/src/db/sqlite/sql/topic-set-content-history.sql b/src/db/sqlite/sql/topic-set-content-history.sql new file mode 100644 index 0000000..8afe3a4 --- /dev/null +++ b/src/db/sqlite/sql/topic-set-content-history.sql @@ -0,0 +1,5 @@ +-- +INSERT INTO topic_content_history + (topic_id, content_size, content_hash) +VALUES + (:topicId, :contentSize, :contentHash) diff --git a/test/src/db/postgres.js b/test/src/db/postgres.js index 4a0ecd9..da071f2 100644 --- a/test/src/db/postgres.js +++ b/test/src/db/postgres.js @@ -1392,6 +1392,7 @@ describe('DatabasePostgres', function () { contentType: 'text/plain', contentHash: 'abc123', }; + sinon.stub(db.db, 'result'); }); it('success', async function() { const dbResult = { @@ -1404,7 +1405,7 @@ describe('DatabasePostgres', function () { lastInsertRowid: undefined, duration: 10, }; - sinon.stub(db.db, 'result').resolves(dbResult); + db.db.result.resolves(dbResult); const result = await db.topicSetContent(dbCtx, data); assert.deepStrictEqual(result, expected); }); @@ -1414,7 +1415,28 @@ describe('DatabasePostgres', function () { rows: [], duration: 10, }; - sinon.stub(db.db, 'result').resolves(dbResult); + db.db.result.resolves(dbResult); + try { + await db.topicSetContent(dbCtx, data); + assert.fail(noExpectedException); + } catch (e) { + assert(e instanceof DBErrors.UnexpectedResult); + } + }); + it('failure 2', async function () { + const dbResultSuccess = { + rowCount: 1, + rows: [], + duration: 10, + }; + const dbResultFail = { + rowCount: 0, + rows: [], + duration: 10, + }; + db.db.result + .onCall(0).resolves(dbResultSuccess) + .onCall(1).resolves(dbResultFail); try { await db.topicSetContent(dbCtx, data); assert.fail(noExpectedException); diff --git a/test/src/db/sqlite.js b/test/src/db/sqlite.js index bd66120..be63994 100644 --- a/test/src/db/sqlite.js +++ b/test/src/db/sqlite.js @@ -1220,6 +1220,8 @@ describe('DatabaseSQLite', function () { contentType: 'text/plain', contentHash: 'abc123', }; + sinon.stub(db.statement.topicSetContent, 'run'); + sinon.stub(db.statement.topicSetContentHistory, 'run'); }); it('success', async function() { const dbResult = { @@ -1230,7 +1232,8 @@ describe('DatabaseSQLite', function () { changes: 1, lastInsertRowid: undefined, }; - sinon.stub(db.statement.topicSetContent, 'run').returns(dbResult); + db.statement.topicSetContent.run.returns(dbResult); + db.statement.topicSetContentHistory.run.returns(dbResult); const result = await db.topicSetContent(dbCtx, data); assert.deepStrictEqual(result, expected); }); @@ -1239,7 +1242,25 @@ describe('DatabaseSQLite', function () { changes: 0, lastInsertRowid: undefined, }; - sinon.stub(db.statement.topicSetContent, 'run').returns(dbResult); + db.statement.topicSetContent.run.returns(dbResult); + try { + await db.topicSetContent(dbCtx, data); + assert.fail(noExpectedException); + } catch (e) { + assert(e instanceof DBErrors.UnexpectedResult); + } + }); + it('failure 2', async function () { + const dbResultSuccess = { + changes: 1, + lastInsertRowid: undefined, + }; + const dbResultFail = { + changes: 0, + lastInsertRowid: undefined, + }; + db.statement.topicSetContent.run.returns(dbResultSuccess); + db.statement.topicSetContentHistory.run.returns(dbResultFail); try { await db.topicSetContent(dbCtx, data); assert.fail(noExpectedException); -- 2.43.2