aswi2021mr-proper-gitlab/merge_db/src/Starter.java @ efca2344

import java.io.File;
import java.sql.*;
import java.util.ArrayList;
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){
        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 of dalimil1
        ResultSet dalimil1Wordform = retrieveWordformDalimilX(dalimil1Con, "dalimil1");
        //start process of finding whats missing in dalimil2 (AND is present in dalimil1)
        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");
    }

    /**
     * Go through entries present in dalimil1.dd_wordform and check which items are missing in dalimil2. Then perform respective steps (add wordform and possibly entries to other tables - dd_lemma etc).
     * @param dalimil1Con Connection obj, represents connection to dalimil1 DB
     * @param dalimil2Con Connection obj, represents connection to dalimil2 DB
     * @param dalimil1Wordform ResultSet which represents entries in dalimil1.wordform
     */
    private static void checkMissingWordformDalimil2(Connection dalimil1Con, Connection dalimil2Con, ResultSet dalimil1Wordform){
        ArrayList<Integer>[] doNotDeleteID = parseConflictsFile("shody - okomentované_rem_VYHODIT.txt"); //load ids, which should NOT be deleted (according to db admins)

        ArrayList<String> dalimil1Var = new ArrayList<>();
        ArrayList<String> dalimil2Var = new ArrayList<>();

        try{
            while(dalimil1Wordform.next()){ //go through dalimil1/dd_wordform
                //get all fields dalimil1/dd_wordform - START
                String id = dalimil1Wordform.getString("id");
                String context = dalimil1Wordform.getString("context");
                String date = dalimil1Wordform.getString("date");
                String description = dalimil1Wordform.getString("description");
                String description2 = dalimil1Wordform.getString("description2");
                String description3 = dalimil1Wordform.getString("description3");
                String ending = dalimil1Wordform.getString("ending");
                String finished = dalimil1Wordform.getString("finished");
                String namedentity = dalimil1Wordform.getString("namedentity");
                String position1 = dalimil1Wordform.getString("position1");
                String position2 = dalimil1Wordform.getString("position2");
                String positiondetail = dalimil1Wordform.getString("positiondetail");
                String prefix = dalimil1Wordform.getString("prefix");
                String suffix = dalimil1Wordform.getString("suffix");
                String word = dalimil1Wordform.getString("word");
                String lemma_id = dalimil1Wordform.getString("lemma_id");
                String tag_id = dalimil1Wordform.getString("tag_id");
                //get all fields dalimil1/dd_wordform - END

                //look where points lemma_id and tag_id
                String[] lemma_id_dd_lemma = retrieveLemmaContForId(dalimil1Con, "dalimil1", lemma_id);
                String tag_id_dd_tag = retrieveTagContForId(dalimil1Con, "dalimil1", tag_id);
                ArrayList<String> manuscript_cont = retrieveManuscriptNumsForId(dalimil1Con, "dalimil1", id);

                String dal1Print = "";
                dal1Print += "Dal1: id: " + id + " context: " + context + " date: " + date + " description: " + description + " description2: " + description2 + " description3: " + description3 + " ending: " + ending + " finished: " + finished + " namedentity: " + namedentity + " position1: " + position1 + " position2: " + position2 + " positiondetail: " + positiondetail + " prefix: " + prefix + " suffix: " + suffix + " word: " + word + " lemma_id: " + lemma_id + " tag_id: " + tag_id + " dd_lemma lemma: " + lemma_id_dd_lemma[0] + " dd_lemma pos: " + lemma_id_dd_lemma[1] + " dd_tag tag: " + tag_id_dd_tag;
                dal1Print += " in manuscripts: ";
                for(String manuscriptId : manuscript_cont){
                    dal1Print += manuscriptId + ", ";
                }

                //check if item already in dalimil2/dd_wordform
                Statement statement = dalimil2Con.createStatement();
                String sqlQuery = "SELECT * FROM public.dd_wordform WHERE position1='" + position1 + "' AND position2='" + position2 + "' AND positiondetail='" + positiondetail + "' AND word='" + word + "'" + ";";
                ResultSet results = statement.executeQuery(sqlQuery);

                int matchCountSec = 0; //number of matches found in second DB
                while(results.next()){
                    matchCountSec++;
                    String idSec = results.getString("id");
                    String contextSec = results.getString("context");
                    String dateSec = results.getString("date");
                    String descriptionSec = results.getString("description");
                    String description2Sec = results.getString("description2");
                    String description3Sec = results.getString("description3");
                    String endingSec = results.getString("ending");
                    String finishedSec = results.getString("finished");
                    String namedentitySec = results.getString("namedentity");
                    String position1Sec = results.getString("position1");
                    String position2Sec = results.getString("position2");
                    String positiondetailSec = results.getString("positiondetail");
                    String prefixSec = results.getString("prefix");
                    String suffixSec = results.getString("suffix");
                    String wordSec = results.getString("word");
                    String lemma_idSec = results.getString("lemma_id");
                    String tag_idSec = results.getString("tag_id");

                    ArrayList<String> manuscript_cont2 = retrieveManuscriptNumsForId(dalimil2Con, "dalimil2", idSec);
                    String[] lemma_id_dd_lemma2 = retrieveLemmaContForId(dalimil2Con, "dalimil2", lemma_idSec);
                    String tag_id_dd_tag2 = retrieveTagContForId(dalimil2Con, "dalimil2", tag_idSec);

                    String dal2Print = "";
                    dal2Print += "Dal2: id: " + idSec + " context: " + contextSec + " date: " + dateSec + " description: " + descriptionSec + " description2: " + description2Sec + " description3: " + description3Sec + " ending: " + endingSec + " finished: " + finishedSec + " namedentity: " + namedentitySec + " position1: " + position1Sec + " position2: " + position2Sec + " positiondetail: " + positiondetailSec + " prefix: " + prefixSec + " suffix: " + suffixSec + " word: " + wordSec + " lemma_id: " + lemma_idSec + " tag_id: " + tag_idSec + " dd_lemma lemma: " + lemma_id_dd_lemma2[0] + " dd_lemma pos: " + lemma_id_dd_lemma2[1] + " dd_tag tag: " + tag_id_dd_tag2;
                    dal2Print += " in manuscripts: ";
                    for(String manuscriptId : manuscript_cont2){
                        dal2Print += manuscriptId + ", ";
                    }

                    if(finished.equals("f") && finishedSec.equals("t")){
                        //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..");
                        }
                    }else{
                        dalimil1Var.add(dal1Print);
                        dalimil2Var.add(dal2Print);
                        //drop items which are same across two given DBs
                        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..");
                        }
                        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..");
                        }
                    }
                }
                if(matchCountSec > 1){
                    //System.out.println("Found more equivalents, might be OK " + matchCountSec);
                }
            }
            addRemainingFromFirstToSecDB(dalimil1Con, dalimil2Con); //add data remaining in first DB to second DB
            //printConflicts(dalimil1Var, dalimil2Var);
        } catch (SQLException throwables) {
            System.out.println("err, working with dbs");
            throwables.printStackTrace();
        }
    }

    /**
     * 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)
     * @return ArrayList<Integer>[] - on first index is list of ids, which should stay in first DB (Dal1) and on index 1 is list of ids, which should stay in the second DB
     */
    private static ArrayList<Integer>[] parseConflictsFile(String pathToFile){
        ArrayList<String> fileCont = new ArrayList<>(); //content of the file

        ArrayList<Integer>[] toStayInDB = new ArrayList[2]; //init outer array (0 = first DB, 1 = second DB)
        toStayInDB[0] = new ArrayList<>(); //represents indexes which should stay in first DB (Dal1)
        toStayInDB[1] = new ArrayList<>(); //represents indexes which should stay in second DB (Dal2)

        try{
            File conflictsFile = new File(pathToFile);
            Scanner conflictsSc = new Scanner(conflictsFile);
            while(conflictsSc.hasNextLine()){
                String line = conflictsSc.nextLine();
                fileCont.add(line);
            }
            conflictsSc.close();
        }catch(Exception e){
            System.out.println("Err, reading conflicts file");
            e.printStackTrace();
        }

        //content should be loaded by now, parse it
        for(String line : fileCont){
            if(line.startsWith("Dal1: id: ")){ //line contains id of item in first DB, Dal1
                String[] splitted = line.split("Dal1: id: ");
                splitted = splitted[1].split(" ");
                toStayInDB[0].add(Integer.valueOf(splitted[0]));
            }else if(line.startsWith("Dal2: id: ")){ //line contains id of item in second DB, Dal2
                String[] splitted = line.split("Dal2: id: ");
                splitted = splitted[1].split(" ");
                toStayInDB[1].add(Integer.valueOf(splitted[0]));
            }else{
                System.out.println("Err, file is in invalid format...");
            }
        }

        //System.out.println("size is: " + toStayInDB[0].size() + " and " + toStayInDB[1].size());

        return toStayInDB;
    }

    /**
     * Used for deleting conflicting entries from database.
     * @param dalimilXCon Connection obj which represents connection to DB
     * @param whichDB name of DB (dalimil1 or dalimil2)
     * @param id id of entry which should be deleted (in dd_wordform)
     * @param textRepres text representation of deleted item, used for debug print
     */
    private static void deleteConflict(Connection dalimilXCon, String whichDB, String id, String textRepres){
        try {
            Statement statement = dalimilXCon.createStatement();
            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();
        }
    }

    /**
     * Adds remaining data presesent in Dalimil1 to Dalimil2
     * @param dalimil1Con Connection obj, represents connection to dalimil1 DB
     * @param dalimil2Con Connection obj, represents connection to dalimil2 DB
     */
    private static void addRemainingFromFirstToSecDB(Connection dalimil1Con, Connection dalimil2Con){
        ResultSet dalimil1Wordform = retrieveWordformDalimilX(dalimil1Con, "dalimil1"); //retrieve all data from dd_wordform, first DB (Dalimil 1)

        try {
            while(dalimil1Wordform.next()){ //go through remaining items present in first DB and put them to second DB
                //get all fields dalimil1/dd_wordform - START
                String id = dalimil1Wordform.getString("id");
                String lemma_id = dalimil1Wordform.getString("lemma_id");
                String tag_id = dalimil1Wordform.getString("tag_id");
                //System.out.println("Got remaining: " + id + " " + lemma_id + " " + tag_id);
                //get all fields dalimil1/dd_wordform - END

                //ok, got lemma + tag + manuscript data - now add them to respective tables before adding dd_wordform entry
                int lemma_idSecondDB = addLemmaToSecDB(dalimil1Con, dalimil2Con, lemma_id);
                int tag_idSecondDB = addTagToSecDB(dalimil1Con, dalimil2Con, tag_id);
                int wordform_idSecondDB = addWordformToSecDB(dalimil1Con, dalimil2Con, id, lemma_idSecondDB, tag_idSecondDB);
                //System.out.println("added as: " + wordform_idSecondDB + " " + lemma_idSecondDB + " " + tag_idSecondDB);
                addManuscriptToSecDB(dalimil1Con, dalimil2Con, String.valueOf(id), String.valueOf(wordform_idSecondDB));
            }
        } catch (SQLException throwables) {
            System.out.println("Error while adding remaining from first to sec DB.");
            throwables.printStackTrace();
        }
    }

    /**
     * Used for adding wordform to table dd_wordform (from first DB to second DB ; Dalimil1 -> Dalimil2).
     * @param dalimil1Con Connection obj, represents connection to dalimil1 DB
     * @param dalimil2Con Connection obj, represents connection to dalimil2 DB
     * @param idWordformFirstDB id of the entry in first DB, table dd_wordform, which should be inserted to second DB, same table
     * @param lemma_idSecondDB lemma_id attribute which should be used in SECOND DB for given entry
     * @param tag_idSecondDB tag_id attribute which should be used in SECOND DB for given entry
     * @return id of the newly inserted wordform
     */
    private static int addWordformToSecDB(Connection dalimil1Con, Connection dalimil2Con, String idWordformFirstDB, int lemma_idSecondDB, int tag_idSecondDB){
        ResultSet wordformDataFirstDB = retrieveWordformIdDalimilX(dalimil1Con, "dalimil1", idWordformFirstDB);

        int newWordformId = -1;
        try{
            Statement secondDBWordformMaxState = dalimil2Con.createStatement();
            ResultSet secondDBWordformMaxRes = secondDBWordformMaxState.executeQuery("SELECT MAX(id) FROM public.dd_wordform;"); //get max id, currently in dd_wordform (sec DB, Dalimil2) - newly added item has to be value+1
            while(secondDBWordformMaxRes.next()){
                int maxId = secondDBWordformMaxRes.getInt("max"); //get max id currently in wordform (second DB, Dalimil2) - newly added item has to have retrieved value+1
                newWordformId = maxId + 1; //newly inserted item : current_id + 1 for new id
            }

            //now retrieve data from first DB
            while(wordformDataFirstDB.next()){
                //get relevant fields dalimil1/dd_wordform - START
                String context = wordformDataFirstDB.getString("context");
                String date = wordformDataFirstDB.getString("date");
                String description = wordformDataFirstDB.getString("description");
                String description2 = wordformDataFirstDB.getString("description2");
                String description3 = wordformDataFirstDB.getString("description3");
                String ending = wordformDataFirstDB.getString("ending");
                String finished = wordformDataFirstDB.getString("finished");
                String namedentity = wordformDataFirstDB.getString("namedentity");
                String position1 = wordformDataFirstDB.getString("position1");
                String position2 = wordformDataFirstDB.getString("position2");
                String positiondetail = wordformDataFirstDB.getString("positiondetail");
                String prefix = wordformDataFirstDB.getString("prefix");
                String suffix = wordformDataFirstDB.getString("suffix");
                String word = wordformDataFirstDB.getString("word");
                //get relevant fields dalimil1/dd_wordform - END

                if(newWordformId == -1){
                    System.out.println("err, cannot retrieve wordform id!");
                    return newWordformId;
                }

                //now try to insert data to second DB
                Statement secondDBWordformInsert = dalimil2Con.createStatement();

                String insertIntoSql = "INSERT INTO public.dd_wordform (id, context, date, description, description2, description3, ending, finished, namedentity, position1, position2, positiondetail, prefix, suffix, word, lemma_id, tag_id) VALUES (";
                insertIntoSql += newWordformId + ",'" + context + "',TO_DATE('"+date+"', 'YYYY-MM-DD HH24:MI:ss.fff' ),'"+description+"','"+description2+"','"+description3+"','"+ending+"','"+finished+"','"+namedentity+"','"+position1+"','"+position2+"','"+positiondetail+"','"+prefix+"','"+suffix+"','"+word+"',"+lemma_idSecondDB+","+tag_idSecondDB+");";
                int secondDBWordformInsertRes = secondDBWordformInsert.executeUpdate(insertIntoSql);
                //System.out.println("Inserted wordform with id: " + newWordformId + " RES: " + secondDBWordformInsertRes);
            }
            return newWordformId;
        }catch (SQLException throwables){
            System.out.println("err, inserting wordform");
            throwables.printStackTrace();
            return newWordformId;
        }
    }

    /**
     * Used for adding manuscripts associated with the word to table dd_manuscript (from first to second DB).
     * @param dalimil1Con Connection obj, represents connection to dalimil1 DB
     * @param dalimil2Con Connection obj, represents connection to dalimil2 DB
     * @param wordformIdFirstDB id of the word (in first DB, table dd_wordform), used for retrieving data
     * @param wordformIdSecondDB id of the wordform in second DB
     */
    private static void addManuscriptToSecDB(Connection dalimil1Con, Connection dalimil2Con, String wordformIdFirstDB, String wordformIdSecondDB){
        ArrayList<String> manuscriptNumsFirstDB = retrieveManuscriptNumsForId(dalimil1Con, "dalimil1", wordformIdFirstDB);
        //got numbers of manuscripts in first DB, now edit them to match with the second one -> wordform_id = newly assigned id in second DB, manuscript = stays the same

        try{
            for(int i = 0; i < manuscriptNumsFirstDB.size(); i++){ //go through all manuscripts for each item and add them to second database
                String oneManuscriptNum = manuscriptNumsFirstDB.get(i); //get one manuscript num from retrieved manuscript nums (first DB)
                //now try to insert the data to second DB
                Statement secondDBManuscriptInsert = dalimil2Con.createStatement();
                int secondDBManuscriptInsertRes = secondDBManuscriptInsert.executeUpdate("INSERT INTO public.dd_manuscript (wordform_id, manuscript) VALUES (" + wordformIdSecondDB + "," + oneManuscriptNum + ");");
                //System.out.println("Inserted manuscript with wordform_id: " + wordformIdSecondDB + ", manuscript: " + oneManuscriptNum + " RES: " + secondDBManuscriptInsertRes);
            }

        }catch (SQLException throwables) {
            System.out.println("err, inserting manuscript");
            throwables.printStackTrace();
        }
    }

    /**
     * Used for adding lemma associated with the word to table dd_lemma (from first to second DB).
     * @param dalimil1Con Connection obj, represents connection to dalimil1 DB
     * @param dalimil2Con Connection obj, represents connection to dalimil2 DB
     * @param lemma_idFirstDB lemma_id of respective item (from first DB, dd_wordform! ie. Dalimil1)
     * @return id of the newly inserted lemma
     */
    private static int addLemmaToSecDB(Connection dalimil1Con, Connection dalimil2Con, String lemma_idFirstDB){
        String[] lemmaFirstDB = retrieveLemmaContForId(dalimil1Con, "dalimil1", lemma_idFirstDB); //retrieve lemma data from first DB (from table dd_lemma)
        String lemma = lemmaFirstDB[0];
        String pos = lemmaFirstDB[1];

        int newLemmaId = -1;
        try {
            Statement secondDBLemmaMaxState = dalimil2Con.createStatement();
            ResultSet secondDBLemmaMaxRes = secondDBLemmaMaxState.executeQuery("SELECT MAX(id) FROM public.dd_lemma;"); //get max id currently in lemma (second DB, Dalimil2) - newly added item has to have retrieved value+1
            while(secondDBLemmaMaxRes.next()){
                int maxId = secondDBLemmaMaxRes.getInt("max"); //get max id currently in lemma (second DB, Dalimil2) - newly added item has to have retrieved value+1
                newLemmaId = maxId + 1; //newly inserted item : current_id + 1 for new id
            }

            if(newLemmaId == -1){
                System.out.println("err, cannot retrieve lemma id!");
                return newLemmaId;
            }
            //now try to insert data to second DB
            Statement secondDBLemmaInsert = dalimil2Con.createStatement();
            int secondDBLemmaInsertRes = secondDBLemmaInsert.executeUpdate("INSERT INTO public.dd_lemma (id, lemma, pos) VALUES (" + newLemmaId + ",'" + lemma + "'," + pos + ");"); //insert lemma data retrieved from first DB to second DB (table dd_lemma)
            //System.out.print("Inserted lemma with id: " + newLemmaId + ", lemma: " + lemma + ", pos: " + pos + " RES: " + secondDBLemmaInsertRes);
            return newLemmaId;
        } catch (SQLException throwables) {
            System.out.println("err, inserting lemma");
            throwables.printStackTrace();
            return newLemmaId;
        }
    }

    /**
     * Used for adding tag associated with the word to table dd_tag (from first to second DB).
     * @param dalimil1Con Connection obj, represents connection to dalimil1 DB
     * @param dalimil2Con Connection obj, represents connection to dalimil2 DB
     * @param tag_idFirstDB tag_id of respective item (from first DB, dd_wordform! ie. Dalimil1)
     * @return id of the newly inserted tag
     */
    private static int addTagToSecDB(Connection dalimil1Con, Connection dalimil2Con, String tag_idFirstDB){
        String tagFirstDB = retrieveTagContForId(dalimil1Con, "dalimil1", tag_idFirstDB); //retrieve tag data from first DB (from table dd_tag)

        //tags in first DB are shorter than the ones in second DB (sec is 12char length) -> make it match
        while(tagFirstDB.length() < 12){
            tagFirstDB += "-";
        }

        //try to insert tag data to dd_tag table located in second DB
        int newTagId = -1;
        try{
            Statement secondDBTagMaxState = dalimil2Con.createStatement();
            ResultSet secondDBTagMaxRes = secondDBTagMaxState.executeQuery("SELECT MAX(id) FROM public.dd_tag;"); //get max id currently in tag (second DB, Dalimil2) - newly added item has to have retrieved value+1
            while(secondDBTagMaxRes.next()){
                int maxId = secondDBTagMaxRes.getInt("max"); //get max id currently in tag (second DB, Dalimil2) - newly added item has to have retrieved value+1
                newTagId = maxId + 1; //newly inserted item : current_id + 1 for new id
            }

            if(newTagId == -1){
                System.out.println("err, cannot retrieve tag id!");
                return newTagId;
            }
            //now try to insert tag data retrieved from first DB to second DB
            Statement secondDBTagInsert = dalimil2Con.createStatement();
            int secondDBTagInsertRes = secondDBTagInsert.executeUpdate("INSERT INTO public.dd_tag (id, tag) VALUES (" + newTagId + ",'" + tagFirstDB + "');");
            //System.out.println("Inserted tag with id: " + newTagId + ", tag: " + tagFirstDB + " RES: " + secondDBTagInsertRes);
            return newTagId;
        }catch (SQLException throwables){
            System.out.println("err, inserting tag");
            throwables.printStackTrace();
            return newTagId;
        }
    }

    /**
     * Prints conflicting rows.
     * @param dal1 list with dalimil1 content
     * @param dal2 list with dalimil2 content
     */
    private static void printConflicts(ArrayList<String> dal1, ArrayList<String> dal2){
        if(dal1.size() != dal2.size()){
            System.out.println("Err, size must be same...");
            return;
        }else{
            for(int i = 0; i < dal1.size(); i++){
                System.out.println(dal1.get(i));
                System.out.println(dal2.get(i));
            }
        }
    }

    /**
     * Retrieve manuscript numbers from table dd_manuscript for specific wordform_id (typically id from table dd_wordform).
     * @param dalimilXCon Connection obj which represents connection to DB
     * @param whichDB name of DB (dalimil1 or dalimil2)
     * @param wordform_id id from table dd_wordform
     * @return string arr which contains data from dd_manuscript (each index = manuscript assigned to wordform_id)
     */
    private static ArrayList<String> retrieveManuscriptNumsForId(Connection dalimilXCon, String whichDB, String wordform_id){
        ArrayList<String> manuscriptNumsList = new ArrayList<>();

        try {
            Statement statement = dalimilXCon.createStatement();
            ResultSet results = statement.executeQuery("SELECT * FROM public.dd_manuscript WHERE wordform_id=" + wordform_id + ";");
            while(results.next()){
                String manuscript = results.getString("manuscript");
                manuscriptNumsList.add(manuscript);
            }

            //System.out.println("OK, " + whichDB + " - retrieving data from dd_manuscript.");
        } catch (SQLException throwables) {
            System.out.println("ERR, " + whichDB + " - retrieving data from dd_manuscript.");
            throwables.printStackTrace();
        }

        return manuscriptNumsList;
    }

    /**
     * Retrieve content from table dd_lemma for specific id (typically lemma_id from table dd_wordform).
     * @param dalimilXCon Connection obj which represents connection to DB
     * @param whichDB name of DB (dalimil1 or dalimil2)
     * @param id lemma_id from table dd_wordform
     * @return String arr which contains data from dd_lemma (index 0 = lemma, index 1 = pos)
     */
    private static String[] retrieveLemmaContForId(Connection dalimilXCon, String whichDB, String id){
        String[] lemmaCont = new String[2];

        try {
            Statement statement = dalimilXCon.createStatement();
            ResultSet results = statement.executeQuery("SELECT * FROM public.dd_lemma WHERE id=" + id + ";");
            while(results.next()){
                String lemma = results.getString("lemma");
                String pos = results.getString("pos");

                lemmaCont[0] = lemma;
                lemmaCont[1] = pos;
            }
        } catch (SQLException throwables) {
            System.out.println("ERR, " + whichDB + " - retrieving data from dd_lemma.");
            throwables.printStackTrace();
        }

        return lemmaCont;
    }

    /**
     * Retrieve tag from table dd_lemma for specific id (typically tag_id from table dd_wordform).
     * @param dalimilXCon Connection obj which represents connection to DB
     * @param whichDB name of DB (dalimil1 or dalimil2)
     * @param id tag_id from table dd_wordform
     * @return
     */
    private static String retrieveTagContForId(Connection dalimilXCon, String whichDB, String id){
        String tag = null;

        try {
            Statement statement = dalimilXCon.createStatement();
            ResultSet results = statement.executeQuery("SELECT * FROM public.dd_tag WHERE id=" + id + ";");
            while(results.next()){
                tag = results.getString("tag");
            }
        } catch (SQLException throwables) {
            System.out.println("ERR, " + whichDB + " - retrieving data from dd_tag.");
            throwables.printStackTrace();
        }

        return tag;
    }

    /**
     * Get data from table dd_wordform (from dalimilX DB)
     * @param dalimilXCon Connection obj which represents connection to DB
     * @param whichDB name of DB (dalimil1 or dalimil2)
     * @return ResultSet, which represents data retrieved from dd_wordform table
     */
    private static ResultSet retrieveWordformDalimilX(Connection dalimilXCon, String whichDB){
        ResultSet results = null;

        try {
            Statement statement = dalimilXCon.createStatement();
            results = statement.executeQuery("SELECT * FROM public.dd_wordform;");
            //System.out.println("OK, " + whichDB + " - retrieving data from dd_wordform.");
        } catch (SQLException throwables) {
            System.out.println("ERR, " + whichDB + " - retrieving data from dd_wordform.");
            throwables.printStackTrace();
        }

        return results;
    }

    /**
     * Used for retrieving wordform with given id from DB (table dd_wordform).
     * @param dalimilXCon Connection obj which represents connection to DB
     * @param whichDB name of DB (dalimil1 or dalimil2)
     * @param id id of the entry in table dd_wordform which should be retrieved
     * @return ResultSet contains data about object with the specified id
     */
    private static ResultSet retrieveWordformIdDalimilX(Connection dalimilXCon, String whichDB, String id){
        ResultSet res = null;

        try{
            Statement statement = dalimilXCon.createStatement();
            res = statement.executeQuery("SELECT * FROM public.dd_wordform WHERE id=" + id + ";");
        } catch (SQLException throwables) {
            System.out.println("ERR, " + whichDB + " - retrieving data from dd_wordform.");
            throwables.printStackTrace();
        }

        return res;
    }



    /**
     * Establish connection to dalimilX DB.
     * @param whichDB name of DB (dalimil1 or dalimil2)
     * @return Connection obj, represents connection to dalimilX DB
     */
    private static Connection establishDalimilX(String whichDB){
        String jdbcDal1 = "jdbc:postgresql://localhost:5432/" + whichDB;
        String username = "postgres";
        String password = "YOUR_ADMIN_PASS"; //obviously - replace this with your admin password

        Connection connection = null;

        try {
            connection = DriverManager.getConnection(jdbcDal1, username, password);
            //System.out.println("OK, " + whichDB + " - connect to PostgreSQL server.");
        } catch (SQLException throwables) {
            System.out.println("ERR, " + whichDB + " - connect to PostgreSQL server.");
            throwables.printStackTrace();
        }

        return connection;
    }

    /**
     * Close connection to dalimilX DB.
     * @param dalimilXCon Connection obj which represents connection to DB
     * @param whichDB name of DB (dalimil1 or dalimil2)
     * @return true if closed successfully, else false
     */
    private static boolean closeDalimilX(Connection dalimilXCon, String whichDB){
        try {
            dalimilXCon.close();
            //System.out.println("OK, " + whichDB + " - disconnect from PostgreSQL server.");
            return true;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            System.out.println("ERR, " + whichDB + " - disconnect from PostgreSQL server.");
            return false;
        }
    }
}