--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/src/main/java/org/mpi/openmind/scripts/FixRelationNonCurrent.java	Thu Apr 09 17:31:55 2015 +0000
@@ -0,0 +1,140 @@
+/**
+ * 
+ */
+package org.mpi.openmind.scripts;
+
+import java.sql.Connection;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.util.ArrayList;
+import java.util.List;
+
+/**
+ * @author casties
+ *
+ */
+public class FixRelationNonCurrent {
+    
+    public static void show(String dbUser, String dbPw) {
+        Connection conn = DBUtils.getConn(dbUser, dbPw);
+        List<Long> relIds = getRelIds(conn);
+        for (Long id : relIds) {
+            System.out.println("RELATION "+id+" target is not CURRENT_VERSION");
+            int ncv = getCurrentVersionTime(id, conn).size();
+            if (ncv == 1) {
+                System.out.println("  RELATION "+id+" can be fixed");
+            } else {
+                System.out.println("  ERROR: RELATION "+id+" has " + ncv + " CURRENT_VERSIONs");                
+            }
+        }
+        System.out.println(relIds.size() + " RELATION targets are not CURRENT_VERSION");
+    }
+
+    public static void repair(String dbUser, String dbPw) {
+        Connection conn = DBUtils.getConn(dbUser, dbPw);
+        List<Long> relIds = getRelIds(conn);
+        int fixed = 0;
+        for (Long id : relIds) {
+            System.out.println("RELATION "+id+" target is not CURRENT_VERSION");
+            if (updateCurrentRelationTarget(id, conn)) {
+                System.out.println("  RELATION "+id+" target updated to current version");
+                fixed += 1;
+            } else {
+                System.out.println("  ERROR: RELATION "+id+" was not fixed!");
+            }
+        }
+        System.out.println(relIds.size() + " RELATION targets are not CURRENT_VERSION");
+        System.out.println(fixed + " RELATION targets were fixed.");
+    }
+
+    public static List<Long> getRelIds(Connection conn) {
+        List<Long> relIds = new ArrayList<Long>();
+        String qTargetRels = "select rel.id from openmind.node rel, openmind.node ent "
+                + "where rel.target_id = ent.id "
+                + "and rel.target_modif = ent.modification_time "
+                + "and rel.node_type = 'RELATION' "
+                + "and rel.system_status = 'CURRENT_VERSION' "
+                + "and ent.system_status != 'CURRENT_VERSION' ";
+        
+        try {
+            Statement sTargetRels = conn.createStatement();
+            ResultSet rTargetRels = sTargetRels.executeQuery(qTargetRels);
+            while (rTargetRels.next()) {
+                long id = rTargetRels.getLong(1);
+                relIds.add(id);
+            }
+            
+        } catch (SQLException e) {
+            e.printStackTrace();
+        }
+        return relIds;
+    }
+
+    public static List<Long> getCurrentVersionTime(Long id, Connection conn) {
+        List<Long> times = new ArrayList<Long>();
+        String query = "select modification_time from openmind.node "
+                + "where id = " + id.toString() + " "
+                + "and system_status = 'CURRENT_VERSION' ";
+        try {
+            Statement statement = conn.createStatement();
+            ResultSet results = statement.executeQuery(query);
+            while (results.next()) {
+                long mtime = results.getLong(1);
+                times.add(mtime);
+            }
+        } catch (SQLException e) {
+            e.printStackTrace();
+        }
+        return times;
+    }
+
+    public static boolean updateCurrentRelationTarget(Long id, Connection conn) {
+        // get target_id of relation
+        String qTargetId = "select target_id from openmind.node "
+                + "where id = " + id.toString() + " "
+                + "and system_status = 'CURRENT_VERSION' ";
+        try {
+            Statement sTargetId = conn.createStatement();
+            ResultSet rTargetId = sTargetId.executeQuery(qTargetId);
+            if (rTargetId.next()) {
+                long target_id = rTargetId.getLong(1);
+                // get mtime of current version of target
+                List<Long> target_mtimes = getCurrentVersionTime(target_id, conn);
+                if (target_mtimes.size() == 1) {
+                    // update target_mtime of relation
+                    String qUpdate = "update openmind.node "
+                            + "set target_modif = " + target_mtimes.get(0).toString() + " "
+                            + "where id = " + id.toString() + " "
+                            + "and system_status = 'CURRENT_VERSION' ";
+                    Statement sUpdate = conn.createStatement();
+                    int rUpdate = sUpdate.executeUpdate(qUpdate);
+                    return true;
+                } else {
+                    System.out.println("ERROR: relation target " + target_id + " has " + target_mtimes.size() + " CURRENT_VERSIONs");
+                }
+            }
+        } catch (SQLException e) {
+            e.printStackTrace();
+        }
+        return false;
+    }
+
+    /**
+     * @param args
+     */
+    public static void main(String[] args) {
+        if (args.length == 3) {
+            String user = args[1];
+            String pw = args[2];
+            if (args[0].equalsIgnoreCase("fix")) {
+                repair(user, pw);
+            } else {
+                show(user, pw);
+            }
+        } else {
+            System.out.println("Parameter/s not found! Should be: mode(SHOW/FIX), mysql_user, mysql_password");
+        }
+    }
+
+}