--- a/artifact-database/src/main/resources/sql/org-h2-driver.properties	Wed Mar 02 16:02:47 2011 +0000
+++ b/artifact-database/src/main/resources/sql/org-h2-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 = ?
 users.select.gid=SELECT id, name, role FROM users WHERE gid = ?
 
 users.delete.id=DELETE FROM users WHERE id = ?
@@ -36,3 +37,35 @@
 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 = ?
+
+users.delete.all.collections=DELETE 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 = DATEADD('MILLISECOND', -2, CURRENT_TIMESTAMP), 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 = DATEADD('MILLISECOND', -2, CURRENT_TIMESTAMP), 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 <> ?)))