initial commit

[squeep-indie-auther] / src / db / sqlite / sql / token-cleanup.sql

diff --git a/src/db/sqlite/sql/token-cleanup.sql b/src/db/sqlite/sql/token-cleanup.sql
new file mode 100644 (file)
index 0000000..45f16b7
--- /dev/null
+++ b/src/db/sqlite/sql/token-cleanup.sql
@@ -0,0 +1,32 @@
+-- Remove tokens no longer in use.
+WITH
+-- reduce verbosity of using epoch timestamps
+times AS (
+       SELECT
+               (strftime('%s', 'now') - :codeLifespanSeconds) AS code_valid_offset,
+               strftime('%s', 'now') AS now
+),
+-- only clean after code has expired
+cleanable_codes AS (
+       SELECT t.code_id, t.is_token, t.is_revoked, t.expires, t.refresh_expires FROM token t, times
+               WHERE t.created < times.code_valid_offset
+)
+DELETE FROM token WHERE code_id IN (
+       SELECT code_id FROM cleanable_codes c, times
+       WHERE
+               NOT c.is_token -- profile-only redemption
+       OR (
+               c.is_token AND (
+                       c.is_revoked -- revoked
+                       OR (
+                               c.expires < times.now AND (
+                                       -- expired and unrefreshable
+                                       refresh_expires IS NULL
+                                       OR
+                                       -- expired and refresh expired
+                                       (refresh_expires IS NOT NULL AND refresh_expires < times.now)
+                               )
+                       )
+               )
+       )
+)
\ No newline at end of file