Merge branch 'v1.1-dev' as v1.1.5

[websub-hub] / src / db / sqlite / sql / schema / 1.0.1 / apply.sql

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 (file)
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;
+