initial commit

[squeep-indie-auther] / src / db / postgres / sql / profiles-scopes-by-identifier.sql

diff --git a/src/db/postgres/sql/profiles-scopes-by-identifier.sql b/src/db/postgres/sql/profiles-scopes-by-identifier.sql
new file mode 100644 (file)
index 0000000..525ced6
--- /dev/null
+++ b/src/db/postgres/sql/profiles-scopes-by-identifier.sql
@@ -0,0 +1,10 @@
+--
+SELECT p.profile, s.*
+       FROM profile p
+               INNER JOIN authentication a USING (identifier_id)
+               FULL JOIN profile_scope ps USING (profile_id)
+               FULL JOIN scope s USING (scope_id)
+       WHERE a.identifier = $(identifier)
+UNION ALL SELECT NULL AS profile, *
+       FROM scope
+       WHERE is_manually_added OR is_permanent