diff artifact-database/src/main/resources/sql/org-postgresql-driver.properties @ 144:5369582d4fbf

Enable backend to delete users artifacts/trunk@1369 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Wed, 02 Mar 2011 17:43:25 +0000
parents 2950c6011afa
children db0d20440b92
line wrap: on
line diff
--- a/artifact-database/src/main/resources/sql/org-postgresql-driver.properties	Wed Mar 02 16:02:47 2011 +0000
+++ b/artifact-database/src/main/resources/sql/org-postgresql-driver.properties	Wed Mar 02 17:43:25 2011 +0000
@@ -29,6 +29,7 @@
 
 users.insert=INSERT INTO users (id, gid, name, role) VALUES (?, ?, ?, ?)
 
+users.select.id.by.gid=SELECT id FROM users WHERE gid = ?::uuid
 users.select.gid=SELECT id, name, role FROM users WHERE gid = ?::uuid
 
 users.delete.id=DELETE FROM users WHERE id = ?
@@ -36,3 +37,34 @@
 users.delete.collections=DELETE FROM collections where owner_id = ?
 
 users.select.all=SELECT id, gid, name, role FROM users
+
+# COLLECTIONS
+
+delete.user.collection.items= \
+    DELETE FROM collection_items WHERE collection_id IN \
+    (SELECT id FROM collections WHERE owner_id = ?)
+
+users.collections=SELECT collection_id, gid, name FROM collections WHERE owner_id = ?
+users.collection.ids=SELECT collection_id FROM collections WHERE owner_id = ?
+
+artifacts.in.one.collection.only=\
+    SELECT artifact_id FROM collection_items\
+    WHERE collection_id = ? AND\
+    artifact_id NOT IN (SELECT DISTINCT artifact_id FROM collection_items WHERE collection_id <> ?)
+
+outdate.artifacts.collection=UPDATE artifacts \
+    SET last_access = CURRENT_TIMESTAMP - '2 microseconds'::interval, ttl = 1 \
+    WHERE id IN \
+    (SELECT artifact_id FROM collection_items \
+        WHERE collection_id = ? AND \
+        artifact_id NOT IN (SELECT DISTINCT artifact_id FROM collection_items WHERE collection_id <> ?))
+
+outdate.artifacts.user=UPDATE artifacts \
+    SET last_access = CURRENT_TIMESTAMP - '2 microseconds'::interval, ttl = 1 \
+    WHERE id IN \
+    (SELECT artifact_id FROM collection_items WHERE \
+        collection_id IN (SELECT id FROM collections WHERE owner_id = ?) \
+        AND artifact_id NOT IN \
+            (SELECT artifact_id FROM collection_items WHERE collection_id IN \
+                (SELECT id FROM collections WHERE owner_id <> ?)))
+

http://dive4elements.wald.intevation.org