initial commit

[squeep-indie-auther] / src / db / postgres / sql / tokens-get-by-identifier.sql

diff --git a/src/db/postgres/sql/tokens-get-by-identifier.sql b/src/db/postgres/sql/tokens-get-by-identifier.sql
new file mode 100644 (file)
index 0000000..f8ec90e
--- /dev/null
+++ b/src/db/postgres/sql/tokens-get-by-identifier.sql
@@ -0,0 +1,26 @@
+--
+SELECT 
+       t.code_id,
+       p.profile,
+       t.created,
+       t.expires,
+       t.refresh_expires,
+       t.refreshed,
+       t.duration,
+       t.refresh_duration,
+       t.refresh_count,
+       t.is_revoked,
+       t.is_token,
+       t.client_id,
+       t.resource,
+       t.profile_data,
+       a.identifier,
+       ARRAY(
+               SELECT s.scope FROM token_scope ts INNER JOIN scope s USING (scope_id)
+               WHERE ts.code_id = t.code_id
+       ) AS scopes
+FROM token t
+       INNER JOIN profile p USING (profile_id)
+       INNER JOIN authentication a USING (identifier_id)
+       WHERE a.identifier = $(identifier)
+       ORDER BY GREATEST(t.created, t.refreshed) DESC