changeset 180:38fbbeffe8fe

Added code to remove an artifact from a collection artifacts/trunk@1406 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Sun, 06 Mar 2011 11:49:00 +0000
parents 644fd11ddd9f
children 116482169b44
files ChangeLog artifact-database/src/main/java/de/intevation/artifactdatabase/Backend.java artifact-database/src/main/resources/sql/org-h2-driver.properties artifact-database/src/main/resources/sql/org-postgresql-driver.properties
diffstat 4 files changed, 103 insertions(+), 5 deletions(-) [+]
line wrap: on
line diff
--- a/ChangeLog	Sun Mar 06 10:55:37 2011 +0000
+++ b/ChangeLog	Sun Mar 06 11:49:00 2011 +0000
@@ -1,3 +1,13 @@
+2011-03-06	Sascha L. Teichmann	<sascha.teichmann@intevation.de>
+
+	* artifact-database/src/main/java/de/intevation/artifactdatabase/Backend.java:
+	  Added code to delete a collection item. Fixed typo in SQL lookup.
+
+	* artifact-database/src/main/resources/sql/org-h2-driver.properties,
+	  artifact-database/src/main/resources/sql/org-postgresql-driver.properties:
+	  Added SQL statements to removed an artifact from a collection given
+	  a collection id and an artifact id.
+
 2011-03-06	Sascha L. Teichmann	<sascha.teichmann@intevation.de>
 
 	* artifact-database/src/main/java/de/intevation/artifactdatabase/Backend.java:
--- a/artifact-database/src/main/java/de/intevation/artifactdatabase/Backend.java	Sun Mar 06 10:55:37 2011 +0000
+++ b/artifact-database/src/main/java/de/intevation/artifactdatabase/Backend.java	Sun Mar 06 11:49:00 2011 +0000
@@ -171,7 +171,19 @@
         SQL.get("collection.item.set.attribute");
 
     public static final String SQL_COLLECTIONS_TOUCH_BY_GID =
-        SQL.get("collections.touch.by,gid");
+        SQL.get("collections.touch.by.gid");
+
+    public static final String SQL_COLLECTION_ITEM_ID_CID_AID =
+        SQL.get("collection.item.id.cid.aid");
+
+    public static final String SQL_COLLECTION_ITEM_OUTDATE_ARTIFACT =
+        SQL.get("collection.item.outdate.artifact");
+
+    public static final String SQL_COLLECTION_ITEM_DELETE =
+        SQL.get("collection.item.delete");
+
+    public static final String SQL_COLLECTIONS_TOUCH_BY_ID =
+        SQL.get("collections.touch.by.id");
 
     /** The singleton.*/
     protected static Backend instance;
@@ -1278,11 +1290,52 @@
     }
 
     public boolean removeCollectionArtifact(
-        String collectionId,
-        String artifactId
+        final String collectionId,
+        final String artifactId
     ) {
-        // TODO: Implement me!
-        return false;
+        if (!isValidIdentifier(collectionId)) {
+            logger.debug("Invalid collection id: '" + collectionId + "'");
+            return false;
+        }
+        return new SQLExecutor() {
+            public boolean doIt() throws SQLException {
+
+                // fetch id, collection id and artitfact id
+                prepareStatement(SQL_COLLECTION_ITEM_ID_CID_AID);
+                stmnt.setString(1, collectionId);
+                stmnt.setString(2, artifactId);
+                result = stmnt.executeQuery();
+                if (!result.next()) {
+                    logger.debug("No such collection item");
+                    return false;
+                }
+                int  id = result.getInt(1);
+                int cid = result.getInt(2);
+                int aid = result.getInt(3);
+                reset();
+
+                // outdate artifact iff it is only in this collection
+                prepareStatement(SQL_COLLECTION_ITEM_OUTDATE_ARTIFACT);
+                stmnt.setInt(1, aid);
+                stmnt.setInt(2, cid);
+                stmnt.execute();
+                reset();
+
+                // delete collection item
+                prepareStatement(SQL_COLLECTION_ITEM_DELETE);
+                stmnt.setInt(1, id);
+                stmnt.execute();
+                reset();
+
+                // touch collection
+                prepareStatement(SQL_COLLECTIONS_TOUCH_BY_ID);
+                stmnt.setInt(1, cid);
+                stmnt.execute();
+
+                conn.commit();
+                return true;
+            }
+        }.runWrite();
     }
 
     public ArtifactCollection [] listCollectionArtifacts(String collectionId) {
--- a/artifact-database/src/main/resources/sql/org-h2-driver.properties	Sun Mar 06 10:55:37 2011 +0000
+++ b/artifact-database/src/main/resources/sql/org-h2-driver.properties	Sun Mar 06 11:49:00 2011 +0000
@@ -61,11 +61,29 @@
             INNER JOIN attributes a ON ci.artifact_id = a.id \
             WHERE c.gid = ? AND a.gid = ?)
 
+collection.item.id.cid.aid= \
+    SELECT ci.id, c.collection_id, c.artifact_id FROM collections_items ci \
+        INNER JOIN collection c ON ci.collection_id = c.id \
+        INNER JOIN attributes a ON ci.artifact_id = a.id \
+        WHERE c.gid = ? AND a.gid = ?
+
+collection.item.outdate.artifact= \
+    UPDATE artifacts \
+    SET last_access = DATEADD('MILLISECOND', -2, CURRENT_TIMESTAMP), ttl = 1 \
+    WHERE id = ? AND \
+    NOT EXSITS (SELECT id FROM collections_items WHERE <> collection_id = ?)
+
+collection.item.delete=DELETE FROM collections_items WHERE id = ?
+
 # COLLECTIONS
 collections.touch.by.gid =\
     UPDATE collection SET last_access = CURRENT_TIMESTAMP \
         WHERE gid = ?
 
+collections.touch.by.id =\
+    UPDATE collection SET last_access = CURRENT_TIMESTAMP \
+        WHERE id = ?
+
 collections.id.nextval=SELECT NEXTVAL('COLLECTIONS_ID_SEQ')
 
 collections.id.by.gid=SELECT id FROM collections WHERE gid = ?
--- a/artifact-database/src/main/resources/sql/org-postgresql-driver.properties	Sun Mar 06 10:55:37 2011 +0000
+++ b/artifact-database/src/main/resources/sql/org-postgresql-driver.properties	Sun Mar 06 11:49:00 2011 +0000
@@ -61,11 +61,28 @@
             INNER JOIN attributes a ON ci.artifact_id = a.id \
             WHERE c.gid = ?::uuid AND a.gid = ?::uuid)
 
+collection.item.id.cid.aid= \
+    SELECT ci.id, c.collection_id, c.artifact_id FROM collections_items ci \
+        INNER JOIN collection c ON ci.collection_id = c.id \
+        INNER JOIN attributes a ON ci.artifact_id = a.id \
+        WHERE c.gid = ?::uuid AND a.gid = ?::uuid
+
+collection.item.outdate.artifact= \
+    UPDATE artifacts \
+    SET last_access = CURRENT_TIMESTAMP - '2 microseconds'::interval, ttl = 1 \
+    WHERE id = ? AND \
+    NOT EXSITS (SELECT id FROM collections_items WHERE <> collection_id = ?)
+
+collection.item.delete=DELETE FROM collections_items WHERE id = ?
+
 # COLLECTIONS
 collections.touch.by.gid =\
     UPDATE collection SET last_access = CURRENT_TIMESTAMP \
         WHERE gid = ?::uuid
 
+collections.touch.by.id =\
+    UPDATE collection SET last_access = CURRENT_TIMESTAMP \
+        WHERE id = ?
 collections.id.nextval=SELECT NEXTVAL('COLLECTIONS_ID_SEQ')
 
 collections.id.by.gid=SELECT id FROM collections WHERE gid = ?::uuid

http://dive4elements.wald.intevation.org