Changeset 28210 for main/trunk/greenstone3/src/java/org/greenstone/gsdl3/util/DerbyWrapper.java

Timestamp:

2013-09-04T15:19:34+12:00 (11 years ago)

Author:

sjm84

Message:

Some fixes for when using the derby database as well as some improvements

File:

• main/trunk/greenstone3/src/java/org/greenstone/gsdl3/util/DerbyWrapper.java (modified) (6 diffs)

main/trunk/greenstone3/src/java/org/greenstone/gsdl3/util/DerbyWrapper.java

@@ -197 +197 @@
         try
         {
-            String query = "SELECT username, action FROM usertracker WHERE site = '" + site + "' and collection = '" + collection + "' and oid = '" + oid + "' ORDER BY time";
+            String query = "SELECT username, action, time FROM usertracker WHERE site = '" + site + "' and collection = '" + collection + "' and oid = '" + oid + "' ORDER BY time";
             Statement state = conn.createStatement();
             ResultSet rs = state.executeQuery(query);
@@ -203 +203 @@
 
             HashSet<String> usernamesSeen = new HashSet<String>();
-
             while (rs.next())
             {
+                String timeStr = rs.getString("time");
+                long time = Long.parseLong(timeStr);
+
+                if (System.currentTimeMillis() - time > 6000)
+                {
+                    continue;
+                }
+
                 HashMap<String, String> action = new HashMap<String, String>();
                 if (!usernamesSeen.contains(rs.getString("username")))
@@ -217 +224 @@
             }
             state.close();
+
+            clearOldUserActions();
         }
         catch (Exception ex)
@@ -223 +232 @@
         }
         return actions;
+    }
+
+    public void clearOldUserActions()
+    {
+        try
+        {
+            Statement state = conn.createStatement();
+            state.execute("DELETE FROM usertracker WHERE (CAST (time AS BIGINT)) < " + (System.currentTimeMillis() - 20000));
+            conn.commit();
+            state.close();
+        }
+        catch (Exception ex)
+        {
+            ex.printStackTrace();
+        }
     }
 
@@ -572 +596 @@
         Statement state = conn.createStatement();
         ResultSet rs = state.executeQuery(sql_find_user);
+        conn.commit();
         while (rs.next())
         {
@@ -583 +608 @@
             users.add(user);
         }
-        conn.commit();
-
+        state.close();
+
+        state = conn.createStatement();
         for (HashMap<String, String> user : users)
         {
             ResultSet gs = state.executeQuery("SELECT role FROM " + ROLES + " WHERE username = '" + user.get("username") + "'");
+            conn.commit();
 
             String group = "";