Mercurial > dive4elements > framework

changeset 144:5369582d4fbf

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Enable backend to delete users artifacts/trunk@1369 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Wed, 02 Mar 2011 17:43:25 +0000
parents 7e20702a90ed
children c0d025df722d
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, 182 insertions(+), 1 deletions(-) [+]
line wrap: on
line diff
--- 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	<sascha.teichmann@intevation.de>
+
+	* 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 <ingo@intevation.de>
 
 	* artifact-database/src/main/java/de/intevation/artifactdatabase/rest/BaseOutResource.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) {
--- 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 <> ?)))
--- 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 <> ?)))
+
http://dive4elements.wald.intevation.org