Revize efca2344

#8645 - zadavatelky požadovaly seznam smazaných rows -> dodal jsem. Skript nyní pracuje ve dvou modech - 1) merge, 2) otestování přítomnosti všech finished záznamů (finální podoba DB: dal_MERGED_AFTER_FINISHED_CHECK.sql)

merge_db/src/Starter.java

@@ -4 +4 @@
 import java.util.Scanner;
 
 public class Starter {
+    public static ArrayList<String> deletedRedundantItIds; //contains ids of duplicated items which were deleted (duplicated items existed mefore merge...)
+
     public static void main(String[] args){
-        //establish connection to DBs
+        deletedRedundantItIds = new ArrayList<>();
+
+        //establish connection to DBs with unmodified content (dalimil1, dalimil2)
         Connection dalimil1Con = establishDalimilX("dalimil1");
         Connection dalimil2Con = establishDalimilX("dalimil2");
-        //retrieve content of "dd_wordform"s
+        //retrieve content of "dd_wordform"s of dalimil1
         ResultSet dalimil1Wordform = retrieveWordformDalimilX(dalimil1Con, "dalimil1");
         //start process of finding whats missing in dalimil2 (AND is present in dalimil1)
-        checkMissingWordformDalimil2(dalimil1Con, dalimil2Con, dalimil1Wordform);
+        checkMissingWordformDalimil2(dalimil1Con, dalimil2Con, dalimil1Wordform); //UNCOMMENT THIS TO MERGE
+
+        //UNCOMMENT THIS TO PERFORM COMPLETE CHECK - START
+        /*System.out.println("MERGED DB CHECK mergedDBCheck - START");
+        mergedDBCheck(dalimil2Con);
+        System.out.println("MERGED DB CHECK mergedDBCheck - END");
+        System.out.println("REVIEW DELETED ITEMS MERGE reviewDeletedItemsMerge - START");
+        reviewDeletedItemsMerge(dalimil2Con);
+        System.out.println("REVIEW DELETED ITEMS MERGE reviewDeletedItemsMerge - END");*/
+        //UNCOMMENT THIS TO PERFORM COMPLETE CHECK - END
+
         //close connection to DBs
         closeDalimilX(dalimil1Con, "dalimil1");
         closeDalimilX(dalimil2Con, "dalimil2");
@@ -101 +115 @@
                     }
 
                     if(finished.equals("f") && finishedSec.equals("t")){
-                        System.out.println("OK, found finished equivalent to unfinished item.. From first drop item: " + dal1Print);
+                        //System.out.println("OK, found finished equivalent to unfinished item.. From first drop item: " + dal1Print);
                         //if unfinished item from first db has finished equivalent in second db, then drop item from first db and keep entry in second
                         if(!doNotDeleteID[0].contains(Integer.valueOf(id))){ //drop from first only if supervisor doesnt want to keep
                             deleteConflict(dalimil1Con, "dalimil1", id, dal1Print); //drop from first db
                         }else{
-                            System.out.println("Conflicting ID: " + id + " kept..");
+                            //System.out.println("Conflicting ID: " + id + " kept..");
                         }
                     }else{
                         dalimil1Var.add(dal1Print);
@@ -115 +129 @@
                         if(!doNotDeleteID[0].contains(Integer.valueOf(id))){ //drop from first only if supervisor doesnt want to keep
                             deleteConflict(dalimil1Con, "dalimil1", id, dal1Print); //delete conflicting item from first db
                         }else{
-                            System.out.println("Conflicting ID: " + id + " kept..");
+                            //System.out.println("Conflicting ID: " + id + " kept..");
                         }
                         if(!doNotDeleteID[1].contains(Integer.valueOf(idSec))){ //drop from second only if supervisor doesnt want to keep
                             deleteConflict(dalimil2Con, "dalimil2", idSec, dal2Print); //delete conflicting item from second db
                         }else{
-                            System.out.println("Conflicting ID: " + idSec + " kept..");
+                            //System.out.println("Conflicting ID: " + idSec + " kept..");
                         }
                     }
                 }
@@ -136 +150 @@
         }
     }
 
+    /**
+     * Checks whether all finished entries present in dalimil1original and dalimil2original (original, not modified DBs) are present in dalimil2 (merged DB)
+     * @param dalimil2Con connection to dalimil2 DB (merged DB with all finished rows from dalimil1original and dalimil2original - except the ones, which supervisor wanted to delete)
+     */
+    private static void mergedDBCheck(Connection dalimil2Con){
+        Connection dalimil1origCon = establishDalimilX("dalimil1original"); //connection to db which contains unmodified dalimil1
+        Connection dalimil2origCon = establishDalimilX("dalimil2original"); //connection to db which contains unmodified dalimil2
+
+        ResultSet dalimil1origWordform = retrieveWordformDalimilX(dalimil1origCon, "dalimil1original"); //content of dalimil1 original dd_wordform
+        ResultSet dalimil2origWordform = retrieveWordformDalimilX(dalimil2origCon, "dalimil2original"); //content of dalimil2 original dd_wordform
+
+        //go through dalimil1original + dalimil2original and check presence of each finished item in dalimil2 (merged DB)
+        try{
+            while(dalimil1origWordform.next()){
+                //get all fields dalimil1original/dd_wordform - START
+                String id = dalimil1origWordform.getString("id");
+                String context = dalimil1origWordform.getString("context");
+                String date = dalimil1origWordform.getString("date");
+                String description = dalimil1origWordform.getString("description");
+                String description2 = dalimil1origWordform.getString("description2");
+                String description3 = dalimil1origWordform.getString("description3");
+                String ending = dalimil1origWordform.getString("ending");
+                String finished = dalimil1origWordform.getString("finished");
+                String namedentity = dalimil1origWordform.getString("namedentity");
+                String position1 = dalimil1origWordform.getString("position1");
+                String position2 = dalimil1origWordform.getString("position2");
+                String positiondetail = dalimil1origWordform.getString("positiondetail");
+                String prefix = dalimil1origWordform.getString("prefix");
+                String suffix = dalimil1origWordform.getString("suffix");
+                String word = dalimil1origWordform.getString("word");
+                String lemma_id = dalimil1origWordform.getString("lemma_id");
+                String tag_id = dalimil1origWordform.getString("tag_id");
+                //get all fields dalimil1original/dd_wordform - END
+
+                //look where points lemma_id and tag_id
+                String[] lemma_id_dd_lemma = retrieveLemmaContForId(dalimil1origCon, "dalimil1original", lemma_id);
+                String tag_id_dd_tag = retrieveTagContForId(dalimil1origCon, "dalimil1original", tag_id);
+                ArrayList<String> manuscript_cont = retrieveManuscriptNumsForId(dalimil1origCon, "dalimil1original", id);
+
+                if(finished.equals("t")){ //entry is considered as finished, continue with check of presence in merged DB
+                    boolean isPresentMerge = checkPresenceEntryMergedDB(id, "dalimil1", dalimil2Con, context, date, description, description2, description3,
+                            ending, finished, namedentity, position1, position2, positiondetail, prefix,
+                            suffix, word, tag_id_dd_tag, lemma_id_dd_lemma[0], lemma_id_dd_lemma[1], manuscript_cont);
+                    if(!isPresentMerge){ //finished entry is not present in merged DB, that should not ever occur :(!!!
+                        System.out.println("ERR, finished item from FIRST DB is not present in merged DB!!!" + id);
+                    }else{
+                        //System.out.println("Ok, item " + word + " (FIRST DB) found in merged DB " + id);
+                    }
+                }
+            }
+
+            while(dalimil2origWordform.next()){
+                //get all fields dalimil2original/dd_wordform - START
+                String id = dalimil2origWordform.getString("id");
+                String context = dalimil2origWordform.getString("context");
+                String date = dalimil2origWordform.getString("date");
+                String description = dalimil2origWordform.getString("description");
+                String description2 = dalimil2origWordform.getString("description2");
+                String description3 = dalimil2origWordform.getString("description3");
+                String ending = dalimil2origWordform.getString("ending");
+                String finished = dalimil2origWordform.getString("finished");
+                String namedentity = dalimil2origWordform.getString("namedentity");
+                String position1 = dalimil2origWordform.getString("position1");
+                String position2 = dalimil2origWordform.getString("position2");
+                String positiondetail = dalimil2origWordform.getString("positiondetail");
+                String prefix = dalimil2origWordform.getString("prefix");
+                String suffix = dalimil2origWordform.getString("suffix");
+                String word = dalimil2origWordform.getString("word");
+                if(word.equals("viz'u")){ //DB contains just one word, which throws exception -> edit the word
+                    word = "viz''u";
+                }
+
+                String lemma_id = dalimil2origWordform.getString("lemma_id");
+                String tag_id = dalimil2origWordform.getString("tag_id");
+                //get all fields dalimil2original/dd_wordform - END
+
+                //look where points lemma_id and tag_id
+                String[] lemma_id_dd_lemma = retrieveLemmaContForId(dalimil2origCon, "dalimil2original", lemma_id);
+                String tag_id_dd_tag = retrieveTagContForId(dalimil2origCon, "dalimil2original", tag_id);
+                ArrayList<String> manuscript_cont = retrieveManuscriptNumsForId(dalimil2origCon, "dalimil2original", id);
+
+                if(finished.equals("t")){ //entry is considered as finished, continue with check of presence in merged DB
+                    boolean isPresentMerge = checkPresenceEntryMergedDB(id, "dalimil2",dalimil2Con, context, date, description, description2, description3,
+                            ending, finished, namedentity, position1, position2, positiondetail, prefix,
+                            suffix, word, tag_id_dd_tag, lemma_id_dd_lemma[0], lemma_id_dd_lemma[1], manuscript_cont);
+                    if(!isPresentMerge){ //finished entry is not present in merged DB, that should not ever occur :(!!!
+                        System.out.println("ERR, finished item from SECOND DB is not present in merged DB!!!" + id);
+                    }else{
+                        //System.out.println("Ok, item " + word + " (FIRST DB) found in merged DB " + id);
+                    }
+                }
+            }
+        }catch (SQLException throwables) {
+            System.out.println("err, working with dbs");
+            throwables.printStackTrace();
+        }
+
+        closeDalimilX(dalimil1origCon, "dalimil1original");
+        closeDalimilX(dalimil2origCon, "dalimil2original");
+    }
+
+    /**
+     * Checks whether entry specified by given parameters is present in merged DB (dalimil2).
+     * @param id id the entry in given DB
+     * @param nameDB name of the checked DB (dalimil1 or dalimil2 expected)
+     * @param dalimil2mergedCon connection to merged database (dalimil2)
+     * @param context original DB - table dd_wordform - context
+     * @param date original DB - table dd_wordform - date
+     * @param description original DB - table dd_wordform - description
+     * @param description2 original DB - table dd_wordform - description2
+     * @param description3 original DB - table dd_wordform - description3
+     * @param ending original DB - table dd_wordform - ending
+     * @param finished original DB - table dd_wordform - finished
+     * @param namedentity original DB - table dd_wordform - namedentity
+     * @param position1 original DB - table dd_wordform - position1
+     * @param position2 original DB - table dd_wordform - position2
+     * @param positiondetail original DB - table dd_wordform - positiondetail
+     * @param prefix original DB - table dd_wordform - prefix
+     * @param suffix original DB - table dd_wordform - suffix
+     * @param word original DB - table dd_wordform - word
+     * @param tag original DB - table dd_tag - tag
+     * @param lemma original DB - table dd_lemma - lemma
+     * @param pos original DB - table dd_lemma - pos
+     * @param manuscriptNums original DB - table dd_manuscript - numbers of all manuscript assigned to doc
+     * @return true if specified entry is present in second DB, else false
+     */
+    private static boolean checkPresenceEntryMergedDB(String id, String nameDB, Connection dalimil2mergedCon, String context, String date, String description, String description2, String description3,
+                                                      String ending, String finished, String namedentity, String position1, String position2, String positiondetail, String prefix,
+                                                      String suffix, String word, String tag, String lemma, String pos, ArrayList<String> manuscriptNums){
+        ResultSet retrievedResMerge = null; //result retrieved from merged DB (dalimil2)
+        Statement statementMerge = null; //statement to be executed on merged DB (dalimil2)
+
+        //tags in first DB are shorter than the ones in second DB (sec is 12char length) -> make it match
+        while(tag.length() < 12){
+            tag += "-";
+        }
+
+        try{
+            //retrieve all rows from dd_lemma which match desired lemma + pos (then one of the retrieved ids has to be assigned to the wordform) - START
+            Statement statementLemma = dalimil2mergedCon.createStatement();
+            ArrayList<String> possibleLemmaIdsList = new ArrayList<>();
+
+            ResultSet statementLemmaRes = statementLemma.executeQuery("SELECT * FROM public.dd_lemma WHERE lemma='" + lemma + "' AND pos='" + pos + "';");
+
+            while(statementLemmaRes.next()){
+                possibleLemmaIdsList.add(statementLemmaRes.getString("id"));
+            }
+            //retrieve all rows from dd_lemma which match desired lemma + pos (then one of the retrieved ids has to be assigned to the wordform) - END
+
+            //retrieve all rows from dd_tag which match desired tag (then one of the retrieved ids has to be assigned to the wordform) - START
+            Statement statementTag = dalimil2mergedCon.createStatement();
+            ArrayList<String> possibleTagIdsList = new ArrayList<>();
+
+            ResultSet statementTagRes = statementTag.executeQuery("SELECT * FROM public.dd_tag WHERE tag='" + tag + "';");
+            while(statementTagRes.next()){
+                possibleTagIdsList.add(statementTagRes.getString("id"));
+            }
+            //retrieve all rows from dd_tag which match desired tag (then one of the retrieved ids has to be assigned to the wordform) - END
+
+            boolean dd_wordformPresent = false;
+
+            //retrieve all items from dd_wordform which match all attributes except for tag_id and lemma_id (then one one of retrieved ids must have assigned required manuscripts) - START
+            statementMerge = dalimil2mergedCon.createStatement();
+            ArrayList<String> wordformIds = new ArrayList<>();
+
+            retrievedResMerge = statementMerge.executeQuery("SELECT * FROM public.dd_wordform WHERE context='" + context + "' AND date='" + date + "' AND description='" + description + "' AND description2='" + description2 + "' AND description3='" + description3 + "' AND ending='" + ending + "' AND finished='" + finished + "' AND namedentity='" + namedentity + "' AND position1='" + position1 + "' AND position2='" + position2 + "' AND positiondetail='" + positiondetail + "' AND prefix='" + prefix + "' AND suffix='" + suffix + "' AND word='" + word + "';");
+            while(retrievedResMerge.next()){
+                if(possibleTagIdsList.contains(retrievedResMerge.getString("tag_id")) && possibleLemmaIdsList.contains(retrievedResMerge.getString("lemma_id"))){ //found item with suitable tag id and lemma id
+                    wordformIds.add(retrievedResMerge.getString("id"));
+                }
+            }
+            //retrieve all items from dd_wordform which match all attributes except for tag_id and lemma_id (then one one of retrieved ids must have assigned required manuscripts) - END
+
+            int matchCountWordform = 0; //number of matches found in merged DB, table dd_wordform
+            ArrayList<String> matchingWordformLemmaTagManucriptIds = new ArrayList<>(); //wordform ids of items WHICH HAVE SAME: wordform, lemma, tag, assigned manuscript = totally equiv entry
+
+            //got suitable dd_lemma + dd_tag -> from that deducted possible dd_wordform ids. now check if any of possible dd_wordform ids have assigned required manuscripts
+            for(String suitableId : wordformIds){ //go through possible wordform ids and check if they have required entries in dd_manuscript
+                Statement statementManuscript = dalimil2mergedCon.createStatement();
+                ArrayList<String> assignedManuscriptNumsList = new ArrayList<>(); //manuscript numbers assigned to traversed id
+                ResultSet statementManuscriptRes = statementManuscript.executeQuery("SELECT * FROM public.dd_manuscript WHERE wordform_id=" + suitableId + ";");
+
+                while(statementManuscriptRes.next()){ //add all manuscripts assigned to specified id to list
+                    String manuscript = statementManuscriptRes.getString("manuscript");
+                    assignedManuscriptNumsList.add(manuscript);
+                }
+
+                int traversedManuscriptCount = 0; //number of already checked assigned manuscripts
+                for(String manuscriptNum : assignedManuscriptNumsList){
+                    if(manuscriptNums.contains(manuscriptNum)){
+                        traversedManuscriptCount++;
+                    }
+                }
+
+                if(traversedManuscriptCount == assignedManuscriptNumsList.size()){ //number of checked items equals to total number of assigned manuscripts, items MATCH (same wordform, lemma, tag and assigned manuscripts...)
+                    matchingWordformLemmaTagManucriptIds.add(suitableId);
+                    matchCountWordform++;
+                }
+            }
+
+            if(matchCountWordform == 1){ //found exactly one matching entry in merged DB (dalimil2), ok (if > 1 is not nice -> db contains redundant information, but doesnt matter that much...)
+                dd_wordformPresent = true;
+            }else if(matchCountWordform == 0){
+                System.out.println("Not FOUND: db" + nameDB + ", id: " + id + "context='" + context + "' AND date='" + date + "' AND description='" + description + "' AND description2='" + description2 + "' AND description3='" + description3 + "' AND ending='" + ending + "' AND finished='" + finished + "' AND namedentity='" + namedentity + "' AND position1='" + position1 + "' AND position2='" + position2 + "' AND positiondetail='" + positiondetail + "' AND prefix='" + prefix + "' AND suffix='" + suffix + "' AND word='" + word + "';");
+            }else if(matchCountWordform > 0){
+                System.out.println("Found more than once! - START");
+
+                for(int i = 0; i < wordformIds.size(); i++){
+                    String wfid = wordformIds.get(i);
+                    System.out.println("In merged db " + nameDB + " id is: " + wfid + ", id from original: " + id);
+
+                    if(i != 0){ //do not delete the first one, must keep one equiv
+                        deleteConflict(dalimil2mergedCon, "dalimil2merged", wfid, "DELETE AFTER MERGE"); //drop from first db
+                        deletedRedundantItIds.add(wfid);
+                    }
+                }
+                System.out.println("Found more than once! - END");
+                dd_wordformPresent = true;
+            }
+
+            if(dd_wordformPresent){ //found entry with matching content - dd_wordform + dd_lemma + dd_tag + dd_manuscript (except for ids, thats obvious)
+                return true;
+            }else{
+                return false;
+            }
+        } catch (SQLException throwables) {
+            System.out.println("ERR, checking data in merged DB. id in original: " + id);
+            throwables.printStackTrace();
+            return false;
+        }
+    }
+
+    /**
+     * Checks whether all deleted items really had equivavelents.
+     * @param dalimil2mergedCon connection to DB which does not contains redundant items (final DB to be used)
+     */
+    private static void reviewDeletedItemsMerge(Connection dalimil2mergedCon){
+        Connection dalimil2mergedWOdeletedredundantCon = establishDalimilX("dalimil2mergedWOdeletedredundant"); //connection to DB which still contains redundant items
+
+        for(String removedId : deletedRedundantItIds){ //go through removed items in dalimil2mergedWOdeletedredundant and check if equiv is really present in dalimil2
+            ResultSet wordformDeletedMerge = retrieveWordformIdDalimilX(dalimil2mergedWOdeletedredundantCon, "dalimil2mergedWOdeletedredundant", removedId); //retrieve content of dd_wordform for deleted id
+
+            int matchCount = 0; //number of items found in dalimil2mergedWOdeletedredundant with the specified id (SHOULD NOT EVER BE > 1!)
+
+            try{
+                while(wordformDeletedMerge.next()){ //go through items found with specified id
+                    matchCount++;
+
+                    //get all fields dalimil2mergedWOdeletedredundant/dd_wordform - START
+                    String id = wordformDeletedMerge.getString("id");
+                    String context = wordformDeletedMerge.getString("context");
+                    String date = wordformDeletedMerge.getString("date");
+                    String description = wordformDeletedMerge.getString("description");
+                    String description2 = wordformDeletedMerge.getString("description2");
+                    String description3 = wordformDeletedMerge.getString("description3");
+                    String ending = wordformDeletedMerge.getString("ending");
+                    String finished = wordformDeletedMerge.getString("finished");
+                    String namedentity = wordformDeletedMerge.getString("namedentity");
+                    String position1 = wordformDeletedMerge.getString("position1");
+                    String position2 = wordformDeletedMerge.getString("position2");
+                    String positiondetail = wordformDeletedMerge.getString("positiondetail");
+                    String prefix = wordformDeletedMerge.getString("prefix");
+                    String suffix = wordformDeletedMerge.getString("suffix");
+                    String word = wordformDeletedMerge.getString("word");
+                    String lemma_id = wordformDeletedMerge.getString("lemma_id");
+                    String tag_id = wordformDeletedMerge.getString("tag_id");
+                    //get all fields dalimil2mergedWOdeletedredundant/dd_wordform - END
+
+                    String[] lemma_id_dd_lemma = retrieveLemmaContForId(dalimil2mergedWOdeletedredundantCon, "dalimil2mergedWOdeletedredundant", lemma_id); //retrieve lemma from dd_lemma for deleted id
+                    String tag_id_dd_tag = retrieveTagContForId(dalimil2mergedWOdeletedredundantCon, "dalimil2mergedWOdeletedredundant", tag_id); //retrieve tag from dd_tag for deleted id
+                    ArrayList<String> manuscript_cont = retrieveManuscriptNumsForId(dalimil2mergedWOdeletedredundantCon, "dalimil2mergedWOdeletedredundant", id); //retrieve manuscripts from dd_manuscript for deleted id
+
+                    boolean isEquivPresent = checkPresenceEntryMergedDB(id, "dalimil2", dalimil2mergedCon, context, date, description, description2, description3,
+                            ending, finished, namedentity, position1, position2, positiondetail, prefix,
+                            suffix, word, tag_id_dd_tag, lemma_id_dd_lemma[0], lemma_id_dd_lemma[1], manuscript_cont);
+
+                    if(!isEquivPresent){ //if equivalent is not present in DB with removed duplicates, something is wrong...
+                        System.out.println("ERR, duplicated item with id " + id + " has no equivalents in final DB!!!");
+                    }else{
+                        System.out.println("Ok, found equivalent to removed id: " + removedId);
+                    }
+                }
+            }catch (SQLException throwables) {
+                System.out.println("err, working with dbs");
+                throwables.printStackTrace();
+            }
+
+            if(matchCount != 1){
+                System.out.println("err, match count for deleted id is not 1!");
+            }
+        }
+
+        closeDalimilX(dalimil2mergedWOdeletedredundantCon, "dalimil2mergedWOdeletedredundant");
+    }
+
     /**
      * Parses content of the file which contains only valid entries and returns ArrayLists which contains ids of entries which should stay in DB. Each entry begins with "Dal1:" or "Dal2:" and ends with ", OK"
      * @param pathToFile path to the file which contains valid entries (checked by contracting authority)
@@ -176 +485 @@
             }
         }
 
-        System.out.println("size is: " + toStayInDB[0].size() + " and " + toStayInDB[1].size());
+        //System.out.println("size is: " + toStayInDB[0].size() + " and " + toStayInDB[1].size());
 
         return toStayInDB;
     }
@@ -194 +503 @@
             int affectedManuscript = statement.executeUpdate("DELETE FROM public.dd_manuscript WHERE wordform_id=" + id); //delete entry which is associated with the word from dd_manuscript
             int affectedWordform = statement.executeUpdate("DELETE FROM public.dd_wordform WHERE id=" + id); //delete entry from dd_wordform
             //System.out.println("Removing affected wordform num:  " + affectedWordform + ", affected manuscript num: " + affectedManuscript + " info line: " + whichDB + " " + textRepres);
+            System.out.println("Z " + whichDB + " odstraneno: " + textRepres);
         } catch (SQLException throwables) {
             System.out.println("ERR, " + whichDB + " - deleting conflicts from database.");
             throwables.printStackTrace();
@@ -543 +853 @@
     private static Connection establishDalimilX(String whichDB){
         String jdbcDal1 = "jdbc:postgresql://localhost:5432/" + whichDB;
         String username = "postgres";
-        String password = "PASS_TO_DB"; //obviously - replace this with your admin password
+        String password = "YOUR_ADMIN_PASS"; //obviously - replace this with your admin password
 
         Connection connection = null;