# HG changeset patch # User Sascha L. Teichmann # Date 1299261075 0 # Node ID a0eff22275886984c54402f43d16d4cef0c8a847 # Parent 16e6e661e6bfff0687637313d7edd22016bd569a Added code to add artifacts to collections. artifacts/trunk@1402 c6561f87-3c4e-4783-a992-168aeb5c3f6f diff -r 16e6e661e6bf -r a0eff2227588 ChangeLog --- a/ChangeLog Fri Mar 04 17:11:40 2011 +0000 +++ b/ChangeLog Fri Mar 04 17:51:15 2011 +0000 @@ -1,3 +1,16 @@ +2011-03-04 Sascha L. Teichmann + + * artifact-database/src/main/java/de/intevation/artifactdatabase/ArtifactDatabaseImpl.java: + TODO: Parse incoming document for an initial attribute of an + collection item. + + * artifact-database/src/main/java/de/intevation/artifactdatabase/Backend.java: + Added code to add a collection item to a collection. + + * artifact-database/src/main/resources/sql/org-h2-driver.properties, + artifact-database/src/main/resources/sql/org-postgresql-driver.properties: + Added SQL statements to handle creation of collection items. + 2011-03-04 Sascha L. Teichmann * artifact-database/src/main/java/de/intevation/artifactdatabase/Backend.java: diff -r 16e6e661e6bf -r a0eff2227588 artifact-database/src/main/java/de/intevation/artifactdatabase/ArtifactDatabaseImpl.java --- a/artifact-database/src/main/java/de/intevation/artifactdatabase/ArtifactDatabaseImpl.java Fri Mar 04 17:11:40 2011 +0000 +++ b/artifact-database/src/main/java/de/intevation/artifactdatabase/ArtifactDatabaseImpl.java Fri Mar 04 17:51:15 2011 +0000 @@ -1249,7 +1249,9 @@ "Add artifact '" + artifactId + "' collection '" +collectionId+"'"); boolean success = backend.addCollectionArtifact( - collectionId, artifactId); + collectionId, + artifactId, + null); // XXX: Needed to be extracted from input Document result = XMLUtils.newDocument(); diff -r 16e6e661e6bf -r a0eff2227588 artifact-database/src/main/java/de/intevation/artifactdatabase/Backend.java --- a/artifact-database/src/main/java/de/intevation/artifactdatabase/Backend.java Fri Mar 04 17:11:40 2011 +0000 +++ b/artifact-database/src/main/java/de/intevation/artifactdatabase/Backend.java Fri Mar 04 17:51:15 2011 +0000 @@ -155,6 +155,15 @@ public static final String SQL_DELETE_COLLECTION = SQL.get("delete.collection"); + public static final String SQL_COLLECTION_CHECK_ARTIFACT = + SQL.get("collection.check.artifact"); + + public static final String SQL_COLLECTION_ITEMS_ID_NEXTVAL = + SQL.get("collection.check.artifact"); + + public static final String SQL_COLLECTION_ITEMS_INSERT = + SQL.get("collection.items.insert"); + /** The singleton.*/ protected static Backend instance; @@ -1113,11 +1122,87 @@ } public boolean addCollectionArtifact( - String collectionId, - String artifactId + final String collectionId, + final String artifactId, + final Document attribute ) { - // TODO: Implement me! - return false; + if (!StringUtils.checkUUID(collectionId)) { + logger.debug("Invalid collection id: '" + collectionId + "'"); + return false; + } + + if (!StringUtils.checkUUID(artifactId)) { + logger.debug("Invalid artifact id: '" + artifactId + "'"); + return false; + } + + SQLExecutor exec = new SQLExecutor() { + public boolean doIt() throws SQLException { + // fetch artifact id + prepareStatement(SQL_GET_ID); + stmnt.setString(1, artifactId); + result = stmnt.executeQuery(); + if (!result.next()) { + logger.debug("No such artifact: " + artifactId); + return false; + } + int aid = result.getInt(1); + reset(); + + // fetch collection id + prepareStatement(SQL_COLLECTIONS_ID_BY_GID); + stmnt.setString(1, collectionId); + result = stmnt.executeQuery(); + if (!result.next()) { + logger.debug("No such collection: " + collectionId); + } + int cid = result.getInt(1); + reset(); + + // check if artifact is already in collection + prepareStatement(SQL_COLLECTION_CHECK_ARTIFACT); + stmnt.setInt(1, aid); + stmnt.setInt(2, cid); + result = stmnt.executeQuery(); + if (result.next()) { + logger.debug("artifact already in collection"); + return false; + } + reset(); + + // fetch fresh id for new collection item + prepareStatement(SQL_COLLECTION_ITEMS_ID_NEXTVAL); + result = stmnt.executeQuery(); + if (!result.next()) { + logger.debug("no collection item id generated"); + return false; + } + int ci_id = result.getInt(1); + reset(); + + // insert new collection item + prepareStatement(SQL_COLLECTION_ITEMS_INSERT); + stmnt.setInt(1, ci_id); + stmnt.setInt(2, cid); + stmnt.setInt(3, aid); + + byte [] data = attribute != null + ? XMLUtils.toByteArray(attribute) + : null; + + if (data == null) { + stmnt.setNull(4, Types.BINARY); + } + else { + stmnt.setBytes(4, data); + } + stmnt.execute(); + conn.commit(); + + return true; + } + }; + return exec.runWrite(); } public boolean removeCollectionArtifact( diff -r 16e6e661e6bf -r a0eff2227588 artifact-database/src/main/resources/sql/org-h2-driver.properties --- a/artifact-database/src/main/resources/sql/org-h2-driver.properties Fri Mar 04 17:11:40 2011 +0000 +++ b/artifact-database/src/main/resources/sql/org-h2-driver.properties Fri Mar 04 17:51:15 2011 +0000 @@ -38,6 +38,16 @@ users.select.all=SELECT id, gid, name, role FROM users +collection.check.artifact=SELECT id FROM collections_items \ + WHERE artifact_id = ? AND collection_id = ? + +# COLLECTION ITEMS +collection.items.id.nextval=SELECT NEXTVAL('COLLECTION_ITEMS_ID_SEQ') + +collection.items.insert=INSERT INTO collections_items \ + (id, collection_id, artifact_id, attribute) \ + VALUES (?, ?, ?, ?) + # COLLECTIONS collections.id.nextval=SELECT NEXTVAL('COLLECTIONS_ID_SEQ') diff -r 16e6e661e6bf -r a0eff2227588 artifact-database/src/main/resources/sql/org-postgresql-driver.properties --- a/artifact-database/src/main/resources/sql/org-postgresql-driver.properties Fri Mar 04 17:11:40 2011 +0000 +++ b/artifact-database/src/main/resources/sql/org-postgresql-driver.properties Fri Mar 04 17:51:15 2011 +0000 @@ -38,6 +38,16 @@ users.select.all=SELECT id, gid, name, role FROM users +collection.check.artifact=SELECT id FROM collections_items \ + WHERE artifact_id = ? AND collection_id = ? + +# COLLECTION ITEMS +collection.items.id.nextval=SELECT NEXTVAL('COLLECTION_ITEMS_ID_SEQ') + +collection.items.insert=INSERT INTO collections_items \ + (id, collection_id, artifact_id, attribute) \ + VALUES (?, ?, ?, ?) + # COLLECTIONS collections.id.nextval=SELECT NEXTVAL('COLLECTIONS_ID_SEQ')