From 009a27af38e750695d11e6699c1d20928ed5f73d Mon Sep 17 00:00:00 2001 From: Justin Wind Date: Mon, 23 Aug 2021 14:56:09 -0700 Subject: [PATCH] verifications will not be processed until their topic is active Updates db schema to 1.0.1, replaces the view of needed verification work. --- src/db/postgres/index.js | 2 +- src/db/postgres/sql/schema/1.0.0/apply.sql | 2 +- src/db/postgres/sql/schema/1.0.1/apply.sql | 18 ++++++++++++++++++ src/db/postgres/sql/schema/1.0.1/revert.sql | 15 +++++++++++++++ src/db/sqlite/index.js | 2 +- src/db/sqlite/sql/schema/1.0.1/apply.sql | 18 ++++++++++++++++++ src/db/sqlite/sql/schema/1.0.1/revert.sql | 16 ++++++++++++++++ 7 files changed, 70 insertions(+), 3 deletions(-) create mode 100644 src/db/postgres/sql/schema/1.0.1/apply.sql create mode 100644 src/db/postgres/sql/schema/1.0.1/revert.sql create mode 100644 src/db/sqlite/sql/schema/1.0.1/apply.sql create mode 100644 src/db/sqlite/sql/schema/1.0.1/revert.sql diff --git a/src/db/postgres/index.js b/src/db/postgres/index.js index 1c5d1d1..2559097 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: 0, + patch: 1, }, }; diff --git a/src/db/postgres/sql/schema/1.0.0/apply.sql b/src/db/postgres/sql/schema/1.0.0/apply.sql index 2665882..24e955d 100644 --- a/src/db/postgres/sql/schema/1.0.0/apply.sql +++ b/src/db/postgres/sql/schema/1.0.0/apply.sql @@ -174,7 +174,7 @@ BEGIN; SELECT * FROM verification WHERE - (topic_id, callback, created) IN (SELECT topic_id, callback, max(created) AS created FROM verification GROUP BY (topic_id, callback)) + (topic_id, callback, created) IN (SELECT topic_id, callback, max(created) AS created FROM verification GROUP BY topic_id, callback) AND (topic_id, callback) NOT IN (SELECT topic_id, callback FROM verification_in_progress_active) AND diff --git a/src/db/postgres/sql/schema/1.0.1/apply.sql b/src/db/postgres/sql/schema/1.0.1/apply.sql new file mode 100644 index 0000000..32eef6a --- /dev/null +++ b/src/db/postgres/sql/schema/1.0.1/apply.sql @@ -0,0 +1,18 @@ +BEGIN; + -- Ignore verifications with topics which are not yet active. + CREATE OR REPLACE VIEW verification_needed AS + SELECT v.* + FROM verification v JOIN topic t ON v.topic_id = t.id + WHERE + t.is_active + AND + (v.topic_id, v.callback, v.created) IN (SELECT topic_id, callback, max(created) AS created FROM verification GROUP BY topic_id, callback) + AND + (v.topic_id, v.callback) NOT IN (SELECT topic_id, callback FROM verification_in_progress_active) + AND + v.next_attempt <= now() + ; + + INSERT INTO _meta_schema_version (major, minor, patch) VALUES (1, 0, 1); +COMMIT; + diff --git a/src/db/postgres/sql/schema/1.0.1/revert.sql b/src/db/postgres/sql/schema/1.0.1/revert.sql new file mode 100644 index 0000000..37813c6 --- /dev/null +++ b/src/db/postgres/sql/schema/1.0.1/revert.sql @@ -0,0 +1,15 @@ +BEGIN; + CREATE OR REPLACE VIEW verification_needed AS + SELECT * + FROM verification + WHERE + (topic_id, callback, created) IN (SELECT topic_id, callback, max(created) AS created FROM verification GROUP BY topic_id, callback) + AND + (topic_id, callback) NOT IN (SELECT topic_id, callback FROM verification_in_progress_active) + AND + next_attempt <= now() + ; + + DELETE FROM _meta_schema_version WHERE major = 1 AND minor = 0 AND patch = 1; +COMMIT; + diff --git a/src/db/sqlite/index.js b/src/db/sqlite/index.js index 9ef1fe5..fba4e7c 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: 0, + patch: 1, }, }; diff --git a/src/db/sqlite/sql/schema/1.0.1/apply.sql b/src/db/sqlite/sql/schema/1.0.1/apply.sql new file mode 100644 index 0000000..6a6552c --- /dev/null +++ b/src/db/sqlite/sql/schema/1.0.1/apply.sql @@ -0,0 +1,18 @@ +BEGIN; + DROP VIEW verification_needed; + CREATE VIEW verification_needed AS + SELECT v.* + FROM verification v JOIN topic t ON v.topic_id = t.id + WHERE + t.is_active + AND + (v.topic_id, v.callback, v.created) IN (SELECT topic_id, callback, max(created) AS created FROM verification GROUP BY topic_id, callback) + AND + (v.topic_id, v.callback) NOT IN (SELECT topic_id, callback FROM verification_in_progress_active) + AND + v.next_attempt <= (strftime('%s', 'now')) + ; + + INSERT INTO _meta_schema_version (major, minor, patch) VALUES (1, 0, 1); +COMMIT; + diff --git a/src/db/sqlite/sql/schema/1.0.1/revert.sql b/src/db/sqlite/sql/schema/1.0.1/revert.sql new file mode 100644 index 0000000..bff18bf --- /dev/null +++ b/src/db/sqlite/sql/schema/1.0.1/revert.sql @@ -0,0 +1,16 @@ +BEGIN; + DROP VIEW verification_needed; + CREATE VIEW verification_needed AS + SELECT * + FROM verification + WHERE + (topic_id, callback, created) IN (SELECT topic_id, callback, max(created) AS created FROM verification GROUP BY topic_id, callback) + AND + (topic_id, callback) NOT IN (SELECT topic_id, callback FROM verification_in_progress_active) + AND + next_attempt <= (strftime('%s', 'now')) + ; + + DELETE FROM _meta_schema_version WHERE major = 1 AND minor = 0 AND patch = 1; +COMMIT; + -- 2.45.2