Mercurial > dive4elements > framework
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