X-Git-Url: http://git.squeep.com/?p=squeep-indie-auther;a=blobdiff_plain;f=src%2Fdb%2Fsqlite%2Fsql%2Ftoken-cleanup.sql;fp=src%2Fdb%2Fsqlite%2Fsql%2Ftoken-cleanup.sql;h=45f16b7e2152fd6470c4948411e06c34b503104c;hp=0000000000000000000000000000000000000000;hb=b0103b0d496262c438b40bc20304081dbfe41e73;hpb=8ed81748bce7cea7904cac7225b20a60cafdfc16

diff --git a/src/db/sqlite/sql/token-cleanup.sql b/src/db/sqlite/sql/token-cleanup.sql
new file mode 100644
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