track topic update history

diff --git a/src/db/postgres/index.js b/src/db/postgres/index.js
index a90e0cbb4277c02ae2a35d16ef68d9e3434ca6ed..d950d4a8488924ea2999a9c03b03d93f38ab4591 100644 (file)
--- 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 (file)
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 (file)
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 (file)
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 3fae300eec6b1d02a746f32aae1b3d978bf35fda..b8826a4f15ee424113e72fd790b41b8212bf3cf8 100644 (file)
--- 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 (file)
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 (file)
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 (file)
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 4a0ecd98516372ecc1adada38595c1ce4c63fa61..da071f2ab28566b53b73ba3ff25858c7e96e0355 100644 (file)
--- 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 bd6612030b3cae08e5d1e1858e8234644033d598..be6399445bcb01279ca2c2b9392c58cbf94e934c 100644 (file)
--- 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);