--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/src/main/java/org/mpi/openmind/scripts/FixAttributeIdMissing.java	Thu Aug 04 21:15:33 2016 +0200
@@ -0,0 +1,310 @@
+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;
+
+/**
+ * Fixes attributes where the current version has no src_id (but older versions have). 
+ * 
+ * @author casties
+ *
+ */
+public class FixAttributeIdMissing {
+    
+    /**
+     * Print information about relations pointing to non-current entities to stdout.
+     * 
+     * Returns 0 if there are no such relations, 1 otherwise.
+     * 
+     * @param dbUser
+     * @param dbPw
+     * @return
+     */
+    public static int show(String dbUser, String dbPw) {
+        Connection conn = DBUtils.getConn(dbUser, dbPw);
+        int fixableCnt = 0;
+        int replacedCnt = 0;
+        int missingSrcCnt = 0;
+        int multiSrcCnt = 0;
+        List<Long> brokenAttIds = getBrokenAttIds(conn);
+        System.out.println(brokenAttIds.size() + " ATTRIBUTEs without source...");
+        for (Long id : brokenAttIds) {
+            //System.out.println("ATTRIBUTE "+id+" source is missing");
+            List<Long> srcIds = getAttSourceIds(id, conn);
+            int ncv = srcIds.size();
+            if (ncv == 1) {
+                fixableCnt += 1;
+                System.out.println("  ATTRIBUTE "+id+" has source (in other version)");
+                List<Long> sameAttIds = getSameAttIds(id, srcIds.get(0), conn);
+                if (sameAttIds.size() > 0) {
+                    replacedCnt += 1;
+                    System.out.println("  ATTRIBUTE "+id+" has has been replaced");
+                }
+            } else if (ncv == 0) {
+                missingSrcCnt += 1;
+            } else {
+                multiSrcCnt += 1;
+                System.out.println("  ERROR: ATTRIBUTE " + id + " has " + ncv + " sources!");                
+            }
+        }
+        System.out.println();
+        System.out.println(brokenAttIds.size() + " ATTRIBUTES have no source_id");
+        System.out.println(fixableCnt + " ATTRIBUTES are fixable (source_id in other version)");
+        System.out.println(replacedCnt + " ATTRIBUTES have been replaced");
+        System.out.println(missingSrcCnt + " ATTRIBUTES are not fixable (no source_id)");
+        System.out.println(multiSrcCnt + " ATTRIBUTES have too many source ids");
+        try {
+            conn.close();
+        } catch (SQLException e) {
+        }
+        if (brokenAttIds.size()  > 0) {
+            return 1;
+        }
+        return 0;
+    }
+
+    /**
+     * Repair relations pointing to non-current entities.
+     * 
+     * Sets the version (*_modif) of the source or target id to the current version of this entity. 
+     * 
+     * Returns 0 if all relations were fixed, 1 otherwise.
+     * 
+     * @param dbUser
+     * @param dbPw
+     * @return
+     */
+    public static int repair(String dbUser, String dbPw) {
+        Connection conn = DBUtils.getConn(dbUser, dbPw);
+        int fixableCnt = 0;
+        int fixedCnt = 0;
+        int unfixableCnt = 0;
+        int replacedCnt = 0;
+        int missingSrcCnt = 0;
+        int multiSrcCnt = 0;
+        List<Long> brokenAttIds = getBrokenAttIds(conn);
+        System.out.println(brokenAttIds.size() + " ATTRIBUTEs without source...");
+        for (Long id : brokenAttIds) {
+            List<Long> srcIds = getAttSourceIds(id, conn);
+            int ncv = srcIds.size();
+            if (ncv == 1) {
+                fixableCnt += 1;
+                Long srcId = srcIds.get(0);
+                System.out.println("  ATTRIBUTE "+id+" has source (in other version)");
+                List<Long> sameAttIds = getSameAttIds(id, srcId, conn);
+                if (sameAttIds.size() > 0) {
+                    replacedCnt += 1;
+                    System.out.println("  ATTRIBUTE "+id+" has has been replaced");
+                } else {
+                    if (updateAttributeSrc(id, srcId, conn)) {
+                        fixedCnt += 1;
+                        System.out.println("  ATTRIBUTE "+id+" source updated");
+                    } else {
+                        unfixableCnt += 1;
+                        System.out.println("  ERROR: ATTRIBUTE "+id+" could not be fixed");
+                    }
+                }
+            } else if (ncv == 0) {
+                missingSrcCnt += 1;
+            } else {
+                multiSrcCnt += 1;
+                System.out.println("  ERROR: ATTRIBUTE " + id + " has " + ncv + " sources!");                
+            }
+        }
+        System.out.println();
+        System.out.println(brokenAttIds.size() + " ATTRIBUTES had no source_id");
+        System.out.println(fixableCnt + " ATTRIBUTES were fixable (source_id in other version)");
+        System.out.println(replacedCnt + " ATTRIBUTES have been replaced");
+        System.out.println(missingSrcCnt + " ATTRIBUTES are not fixable (no source_id)");
+        System.out.println(multiSrcCnt + " ATTRIBUTES have too many source ids");
+        System.out.println();
+        System.out.println(fixedCnt + " ATTRIBUTES have been fixed");
+        System.out.println(unfixableCnt + " ATTRIBUTES could not be fixed");
+
+        
+        try {
+            conn.close();
+        } catch (SQLException e) {
+            e.printStackTrace();
+        }
+        return 0;
+    }
+
+    /**
+     * Return a list of ids of attributes with missing source_id.
+     * 
+     * @param conn
+     * @return
+     */
+    public static List<Long> getBrokenAttIds(Connection conn) {
+        List<Long> ids = new ArrayList<Long>();
+        String query = "SELECT DISTINCT id FROM openmind.node"
+                + " WHERE node_type = 'ATTRIBUTE'"
+                + " AND system_status = 'CURRENT_VERSION'"
+                + " AND source_id IS NULL";
+        
+        try {
+            Statement s = conn.createStatement();
+            ResultSet r = s.executeQuery(query);
+            while (r.next()) {
+                long id = r.getLong(1);
+                ids.add(id);
+            }
+            s.close();
+        } catch (SQLException e) {
+            e.printStackTrace();
+        }
+        return ids;
+    }
+
+    /**
+     * Return a list of source_ids of an attribute.
+     * 
+     * @param attId
+     * @param conn
+     * @return
+     */
+    public static List<Long> getAttSourceIds(Long attId, Connection conn) {
+        List<Long> ids = new ArrayList<Long>();
+        String query = "SELECT DISTINCT source_id FROM openmind.node "
+                + "WHERE node_type = 'ATTRIBUTE' "
+                + "AND source_id IS NOT NULL "
+                + "AND id = " + attId.toString();
+        
+        try {
+            Statement s = conn.createStatement();
+            ResultSet r = s.executeQuery(query);
+            while (r.next()) {
+                long id = r.getLong(1);
+                ids.add(id);
+            }
+            s.close();
+        } catch (SQLException e) {
+            e.printStackTrace();
+        }
+        return ids;
+    }
+
+    /**
+     * Check if another (current) attribute of the same type exists on the source.
+     * 
+     * @param attId
+     * @param srcId
+     * @param conn
+     * @return
+     */
+    public static List<Long> getSameAttIds(Long attId, Long srcId, Connection conn) {
+        List<Long> ids = new ArrayList<Long>();
+        String query = "SELECT DISTINCT id FROM openmind.node"
+                + " WHERE node_type = 'ATTRIBUTE'"
+                + " AND system_status = 'CURRENT_VERSION'"
+                + " AND id != " + attId.toString()
+                + " AND source_id = " + srcId.toString()
+                + " AND object_class IN ("
+                + "  SELECT DISTINCT object_class FROM openmind.node"
+                + "  WHERE id = " + attId.toString() + ")";
+        
+        try {
+            Statement s = conn.createStatement();
+            ResultSet r = s.executeQuery(query);
+            while (r.next()) {
+                long id = r.getLong(1);
+                ids.add(id);
+            }
+            s.close();
+        } catch (SQLException e) {
+            e.printStackTrace();
+        }
+        return ids;
+    }
+
+    /**
+     * Return the modification_date of the current version of the entity.
+     * 
+     * @param id
+     * @param conn
+     * @return
+     */
+    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);
+            }
+            statement.close();
+        } catch (SQLException e) {
+            e.printStackTrace();
+        }
+        return times;
+    }
+
+    /**
+     * Update the attribute with the source_id.
+     * 
+     * Returns true if the relation was fixed.
+     * 
+     * @param id of the attribute
+     * @param id of the source
+     * @param conn
+     * @return
+     */
+    public static boolean updateAttributeSrc(Long attId, Long srcId, Connection conn) {
+        // get mtime of current version of target
+        List<Long> target_mtimes = getCurrentVersionTime(srcId, conn);
+        if (target_mtimes.size() == 1) {
+            // update target_mtime of relation
+            long target_mtime = target_mtimes.get(0);
+            String query = "UPDATE openmind.node"
+                    + " SET source_id = " + srcId.toString()
+                    + ", source_modif = " + target_mtime
+                    + " WHERE id = " + attId.toString()
+                    + " AND source_id IS NULL";
+            Statement s;
+            try {
+                s = conn.createStatement();
+                s.executeUpdate(query);
+                s.close();
+            } catch (SQLException e) {
+                // TODO Auto-generated catch block
+                e.printStackTrace();
+            }
+            System.out.println("  ATTRIBUTE " + attId + " was updated to source " + srcId + "/"+ target_mtime);
+            return true;
+        } else {
+            System.out.println("ERROR: source " + srcId + " has " + target_mtimes.size() + " CURRENT_VERSIONs");
+        }
+        return false;
+    }
+
+    /**
+     * @param args
+     */
+    public static void main(String[] args) {
+        int rc = 0;
+        if (args.length > 1 && args.length < 4) {
+            String user = args[1];
+            String pw = (args.length == 3) ? args[2] : null;
+            if (args[0].equalsIgnoreCase("fix")) {
+                rc = repair(user, pw);
+            } else {
+                rc = show(user, pw);
+            }
+        } else {
+            System.out.println("Parameter/s not found! Should be: mode(SHOW/FIX), mysql_user, mysql_password");
+            System.out.println("  got: "+args.toString() + "("+args.length+")");
+            System.exit(1);
+        }
+        System.exit(rc);
+    }
+
+}