# HG changeset patch # User Sascha L. Teichmann # Date 1299087805 0 # Node ID 5369582d4fbfabfd6db14e4287d891be570ec98d # Parent 7e20702a90ed04b33f0542dcd81f89e0f86abeb0 Enable backend to delete users artifacts/trunk@1369 c6561f87-3c4e-4783-a992-168aeb5c3f6f diff -r 7e20702a90ed -r 5369582d4fbf ChangeLog --- a/ChangeLog Wed Mar 02 16:02:47 2011 +0000 +++ b/ChangeLog Wed Mar 02 17:43:25 2011 +0000 @@ -1,3 +1,15 @@ +2011-03-02 Sascha L. Teichmann + + * artifact-database/src/main/java/de/intevation/artifactdatabase/Backend.java: + Added code to delete a user, his/her collections and quarantee that + the artifacts exclusivly owner by the user are going to be deleted + from the artifact database. + + * artifact-database/src/main/resources/sql/org-h2-driver.properties, + artifact-database/src/main/resources/sql/org-postgresql-driver.properties: + Various SQL statements to cope with outdating artifacts and deleting + users and collections. + 2011-03-02 Ingo Weinzierl * artifact-database/src/main/java/de/intevation/artifactdatabase/rest/BaseOutResource.java: diff -r 7e20702a90ed -r 5369582d4fbf artifact-database/src/main/java/de/intevation/artifactdatabase/Backend.java --- a/artifact-database/src/main/java/de/intevation/artifactdatabase/Backend.java Wed Mar 02 16:02:47 2011 +0000 +++ b/artifact-database/src/main/java/de/intevation/artifactdatabase/Backend.java Wed Mar 02 17:43:25 2011 +0000 @@ -94,6 +94,9 @@ public static final String SQL_USERS_INSERT = SQL.get("users.insert"); + public static final String SQL_USERS_SELECT_ID_BY_GID = + SQL.get("users.select.id.by.gid"); + public static final String SQL_USERS_SELECT_GID = SQL.get("users.select.gid"); @@ -106,6 +109,27 @@ public static final String SQL_USER_SELECT_ALL = SQL.get("users.select.all"); + public static final String SQL_USERS_COLLECTIONS = + SQL.get("users.collections"); + + public static final String SQL_USERS_COLLECTION_IDS = + SQL.get("users.collection.ids"); + + public static final String SQL_USERS_DELETE_ALL_COLLECTIONS = + SQL.get("users.delete.all.collections"); + + public static final String SQL_ARTIFACTS_IN_ONLY_COLLECTION_ONLY = + SQL.get("artifacts.in.one.collection.only"); + + public static final String SQL_OUTDATE_ARTIFACTS_COLLECTION = + SQL.get("outdate.artifacts.collection"); + + public static final String SQL_OUTDATE_ARTIFACTS_USER = + SQL.get("outdate.artifacts.user"); + + public static final String SQL_DELETE_USER_COLLECTION_ITEMS = + SQL.get("delete.user.collection.items"); + /** The singleton.*/ protected static Backend instance; @@ -870,7 +894,87 @@ } public void deleteUser(User user) { - // TODO: implement me! + + Connection conn = null; + ResultSet result = null; + PreparedStatement stmnt = null; + + String identifier = user.identifier(); + + DataSource dataSource = DBConnection.getDataSource(); + try { + conn = dataSource.getConnection(); + try { + conn.setAutoCommit(false); + + // fetch user id + stmnt = conn.prepareStatement(SQL_USERS_SELECT_ID_BY_GID); + + stmnt.setString(1, identifier); + result = stmnt.executeQuery(); + + if (!result.next()) { // No such user + return; + } + + int id = result.getInt(1); + + result.close(); result = null; + stmnt.close(); stmnt = null; + + // outdate the artifacts exclusively used by the user + + stmnt = conn.prepareStatement(SQL_OUTDATE_ARTIFACTS_USER); + stmnt.setInt(1, id); + stmnt.setInt(2, id); + stmnt.execute(); + + stmnt.close(); stmnt = null; + + // delete the collection items of the user + + stmnt = conn.prepareStatement(SQL_DELETE_USER_COLLECTION_ITEMS); + stmnt.setInt(1, id); + stmnt.execute(); + stmnt.close(); stmnt = null; + + // delete the collections of the user + + stmnt = conn.prepareStatement(SQL_USERS_COLLECTIONS); + stmnt.setInt(1, id); + stmnt.execute(); + stmnt.close(); stmnt = null; + + // delete the user + + stmnt = conn.prepareStatement(SQL_USERS_DELETE_ID); + stmnt.setInt(1, id); + stmnt.execute(); + + conn.commit(); + } + catch (SQLException sqle) { + conn.rollback(); + throw sqle; + } + } + catch (SQLException sqle) { + logger.error(sqle.getLocalizedMessage(), sqle); + } + finally { + if (result != null) { + try { result.close(); } + catch (SQLException sqle) {} + } + if (stmnt != null) { + try { stmnt.close(); } + catch (SQLException sqle) {} + } + if (conn != null) { + try { conn.close(); } + catch (SQLException sqle) {} + } + } } public User getUser(String identifier) { diff -r 7e20702a90ed -r 5369582d4fbf artifact-database/src/main/resources/sql/org-h2-driver.properties --- 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 <> ?))) diff -r 7e20702a90ed -r 5369582d4fbf artifact-database/src/main/resources/sql/org-postgresql-driver.properties --- 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 <> ?))) +