initial commit

[squeep-indie-auther] / src / db / postgres / sql / scope-cleanup.sql

diff --git a/src/db/postgres/sql/scope-cleanup.sql b/src/db/postgres/sql/scope-cleanup.sql
new file mode 100644 (file)
index 0000000..120e3d6
--- /dev/null
+++ b/src/db/postgres/sql/scope-cleanup.sql
@@ -0,0 +1,8 @@
+-- Remove any un-used, non-permament, non-manually-created scopes.
+DELETE FROM scope WHERE scope_id NOT IN (
+       SELECT scope_id FROM scope s
+               INNER JOIN profile_scope ps USING (scope_id)
+       UNION All
+       SELECT scope_id FROM scope s
+               INNER JOIN token_scope ts USING (scope_id)
+) AND NOT (is_permanent OR is_manually_added)