Still working on fk hanling. went back to Cascade

6 files changed, 145 insertions, 102 deletions

diff --git a/src/main/java/org/schemaspy/DBFuzzer.java b/src/main/java/org/schemaspy/DBFuzzer.java
index 05cdaba..825db44 100644
--- a/src/main/java/org/schemaspy/DBFuzzer.java
+++ b/src/main/java/org/schemaspy/DBFuzzer.java
@@ -81,7 +81,7 @@ public class DBFuzzer
         int maxDepth = Integer.parseInt(analyzer.getCommandLineArguments().getMaxDepth());
         int mark = 0;
         //adding CASCADE to all foreign key tableColumns.
-        //settingTemporaryCascade(false); // need to drop and recreate database
+        settingTemporaryCascade(false); // need to drop and recreate database
 
         LOGGER.info("Starting Database Fuzzing");
 
@@ -170,7 +170,7 @@ public class DBFuzzer
 
       System.out.println("success");
       printMutationTree();
-      //removeTemporaryCascade();
+      removeTemporaryCascade();
       return returnStatus;
     }
 
@@ -192,7 +192,7 @@ public class DBFuzzer
           try {
               stmt = analyzer.getSqlService().prepareStatement(theQuery);
               rs = stmt.executeQuery();
-              res = qrp.parse(rs, analyzer.getDb().getTablesMap().get(randomTable.getName())).getRows().get(0); //randomTable should be set there
+              res = qrp.parse(rs, analyzer.getDb().getTablesMap().get(randomTable.getName())).getRows().get(0);
           } catch (Exception e) {
               LOGGER.info("This query threw an error" + e);
           }
@@ -231,12 +231,12 @@ public class DBFuzzer
                 dropSetCascade = "ALTER TABLE "+currentFK.getChildTable().getName()+" DROP CONSTRAINT "+currentFK.getName()+ " CASCADE";
                 try
                 {
-                         PreparedStatement stmt = analyzer.getSqlService().prepareStatement(dropSetCascade, analyzer.getDb(),null);
-                         stmt.execute();
+                    PreparedStatement stmt = analyzer.getSqlService().prepareStatement(dropSetCascade, analyzer.getDb(),null);
+                    stmt.execute();
                 }
                 catch(Exception e)
                 {
-                  System.out.println("Dans le catch erreur :"+e);
+                    e.printStackTrace();
                 }
               }
 
@@ -261,7 +261,7 @@ public class DBFuzzer
                   }
                   catch(Exception e)
                   {
-                    System.out.println("Dans le catch 2 erreur :"+e);
+                      e.printStackTrace();
                   }
                 }
 
diff --git a/src/main/java/org/schemaspy/model/FkGenericTreeNode.java b/src/main/java/org/schemaspy/model/FkGenericTreeNode.java
index 19cda15..cdad44a 100644
--- a/src/main/java/org/schemaspy/model/FkGenericTreeNode.java
+++ b/src/main/java/org/schemaspy/model/FkGenericTreeNode.java
@@ -10,12 +10,14 @@ public class FkGenericTreeNode {
     private Row initial_state_row;
     private Row post_change_row;
     private SingleChange fkChange;
+    private boolean targetsMultipleRows;
 
-    public FkGenericTreeNode(Row initial_state_row,GenericTreeNode parent, SingleChange sg)
+    public FkGenericTreeNode(Row initial_state_row,GenericTreeNode parent, SingleChange sg,boolean multipleRows)
     {
-        this.parent = null;
+        this.targetsMultipleRows = multipleRows;
+        this.parent = parent;
         this.initial_state_row = initial_state_row;
-        this.fkChange = fkChange;
+        this.fkChange = sg;
         initPostChangeRow();
     }
 
diff --git a/src/main/java/org/schemaspy/model/GenericTreeNode.java b/src/main/java/org/schemaspy/model/GenericTreeNode.java
index f4a319b..8839d82 100644
--- a/src/main/java/org/schemaspy/model/GenericTreeNode.java
+++ b/src/main/java/org/schemaspy/model/GenericTreeNode.java
@@ -20,7 +20,7 @@ public class GenericTreeNode {
     private Integer weight;
     private Integer subTreeWeight;
     private int depth;
-    private final Row initial_state_row;
+    private Row initial_state_row;
     private Row post_change_row;
     private ArrayList<SingleChange> potential_changes = new ArrayList<SingleChange>();
     //private ArrayList<SingleChange> cascadeFK = new ArrayList<SingleChange>(); // a integrer
@@ -340,11 +340,10 @@ public class GenericTreeNode {
             System.out.println("UNDOING");
         else
             System.out.println("INJECT");
+        if(checkIfHasParentFk(db))
+            transferMutationToParent(db,sqlService);
 
-        if(checkIfHasFk(db))
-            theQuery = updateQueryBuilderWrapper(undo,db,sqlService);
-        else
-            theQuery = updateQueryBuilder(undo,db,sqlService);
+        theQuery = updateQueryBuilder(undo,db,sqlService);
         try
         {
             Statement stmt = sqlService.getConnection().createStatement();
@@ -624,101 +623,136 @@ public class GenericTreeNode {
     }
 
 
-    public String updateQueryBuilderWrapper(boolean undo,Database db, SqlService sqlService)
+//    public String updateQueryBuilderWrapper(boolean undo,Database db, SqlService sqlService)
+//    {
+//        String theQuery = "";
+//
+//            theQuery = "START TRANSACTION; SET CONSTRAINTS ALL DEFERRED;";
+//
+//                for (ForeignKeyConstraint fk : db.getLesForeignKeys().get(chosenChange.getParentTableColumn().getTable().getName().toUpperCase()))
+//                {
+//                        for (TableColumn tb : fk.getChildColumns())
+//                        {
+//                                String semiQuery = "SELECT * FROM " + tb.getTable() + " WHERE " + tb.getName() + "=";
+//                                if (chosenChange.getParentTableColumn().getTypeName().equals("varchar")
+//                                        || chosenChange.getParentTableColumn().getTypeName().equals("bool")
+//                                        || chosenChange.getParentTableColumn().getTypeName().equals("timestamp")
+//                                        || chosenChange.getParentTableColumn().getTypeName().equals("date")
+//                                        || chosenChange.getParentTableColumn().getTypeName().equals("_text")
+//                                        || chosenChange.getParentTableColumn().getTypeName().equals("text")
+//                                        || chosenChange.getParentTableColumn().getTypeName().equals("fulltext"))
+//                                    semiQuery = semiQuery + "' " + chosenChange.getNewValue() + " ' ORDER BY RANDOM() LIMIT 1";
+//                                else
+//                                    semiQuery = semiQuery + chosenChange.getNewValue() + " ORDER BY RANDOM() LIMIT 1";
+//
+//                                QueryResponseParser qrp;
+//                                QueryResponse response = null;
+//                                try {
+//                                    Statement stmt = sqlService.getConnection().createStatement();
+//                                    ResultSet res = stmt.executeQuery(semiQuery);
+//                                    qrp = new QueryResponseParser();
+//                                    ArrayList<Row> rows = new ArrayList<Row>(qrp.parse(res, tb.getTable()).getRows());
+//                                    System.out.println(rows);
+//                                    response = new QueryResponse(rows);
+//                                } catch (Exception e) {
+//                                    e.printStackTrace();
+//                                }
+//                                if (response.getNbRows() == 0) {
+//                                    int tmpIndex = semiQuery.lastIndexOf("=") + 1;
+//                                    if (chosenChange.getParentTableColumn().getTypeName().equals("varchar")
+//                                            || chosenChange.getParentTableColumn().getTypeName().equals("bool")
+//                                            || chosenChange.getParentTableColumn().getTypeName().equals("timestamp")
+//                                            || chosenChange.getParentTableColumn().getTypeName().equals("date")
+//                                            || chosenChange.getParentTableColumn().getTypeName().equals("_text")
+//                                            || chosenChange.getParentTableColumn().getTypeName().equals("text")
+//                                            || chosenChange.getParentTableColumn().getTypeName().equals("fulltext"))
+//                                        semiQuery = semiQuery.substring(0, tmpIndex) + "' " + chosenChange.getOldValue() + "' ";
+//                                    else
+//                                        semiQuery = semiQuery.substring(0, tmpIndex) + chosenChange.getOldValue();
+//
+//                                    try {
+//                                        Statement stmt = sqlService.getConnection().createStatement();
+//                                        ResultSet res = stmt.executeQuery(semiQuery);
+//                                        qrp = new QueryResponseParser();
+//                                        ArrayList<Row> rows = new ArrayList<Row>(qrp.parse(res, tb.getTable()).getRows());
+//                                        response = new QueryResponse(rows);
+//                                    } catch (Exception e) {
+//                                        e.printStackTrace();
+//                                    }
+//
+//                                    if (response.getRows().size() > 1) {
+//                                        FkGenericTreeNode tmp = new FkGenericTreeNode(response.getRows().get(0), this, new SingleChange(tb, this, chosenChange.getOldValue(), chosenChange.getNewValue()), true);
+//                                        fkMutations.put(tb, tmp);
+//                                        theQuery = theQuery + tmp.updateQueryBuilder(false, db, sqlService) + ";"; // adding semicolon between fk updates inside the transaction
+//
+//                                    } else if (response.getRows().size() == 1) {
+//                                        FkGenericTreeNode tmp = new FkGenericTreeNode(response.getRows().get(0), this, new SingleChange(tb, this, chosenChange.getOldValue(), chosenChange.getNewValue()), false);
+//                                        fkMutations.put(tb, tmp);
+//                                        theQuery = theQuery + tmp.updateQueryBuilder(false, db, sqlService) + ";"; // adding semicolon between fk updates inside the transaction
+//
+//                                    }
+//                                }
+//                        }
+//                }
+//
+//
+//
+//        theQuery = theQuery + updateQueryBuilder(undo,db,sqlService);
+//        theQuery = theQuery + " ; COMMIT TRANSACTION;";
+//
+//        System.out.println("Total query = "+theQuery);
+//        return theQuery;
+//    }
+
+    public void transferMutationToParent( Database db,SqlService sqlService)
     {
-        String theQuery = "";
+        TableColumn sgParentColumn = chosenChange.getParentTableColumn();
 
-            theQuery = "START TRANSACTION; SET CONSTRAINTS ALL DEFERRED;";
-
-                System.out.println(db.getLesForeignKeys().get(chosenChange.getParentTableColumn().getTable().getName().toUpperCase()).isEmpty());
-
-                    for (ForeignKeyConstraint fk : db.getLesForeignKeys().get(chosenChange.getParentTableColumn().getTable().getName().toUpperCase())) {
-                        for (TableColumn tb : fk.getChildColumns())
-                        {
-                            String semiQuery = "SELECT * FROM " + tb.getTable() + " WHERE " + tb.getName() + "=";
-                            if (chosenChange.getParentTableColumn().getTypeName().equals("varchar")
-                                    || chosenChange.getParentTableColumn().getTypeName().equals("bool")
-                                    || chosenChange.getParentTableColumn().getTypeName().equals("timestamp")
-                                    || chosenChange.getParentTableColumn().getTypeName().equals("date")
-                                    || chosenChange.getParentTableColumn().getTypeName().equals("_text")
-                                    || chosenChange.getParentTableColumn().getTypeName().equals("text")
-                                    || chosenChange.getParentTableColumn().getTypeName().equals("fulltext"))
-                                semiQuery = semiQuery + "' " + chosenChange.getNewValue() + " ' ORDER BY RANDOM() LIMIT 1";
-                            else
-                                semiQuery = semiQuery + chosenChange.getNewValue() + " ORDER BY RANDOM() LIMIT 1";
-
-                            QueryResponseParser qrp;
-                            QueryResponse response = null;
-                            try
-                            {
-                                Statement stmt = sqlService.getConnection().createStatement();
-                                ResultSet res = stmt.executeQuery(semiQuery);
-                                qrp = new QueryResponseParser();
-                                ArrayList<Row> rows = new ArrayList<Row>(qrp.parse(res, tb.getTable()).getRows());
-                                response = new QueryResponse(rows);
-                            }
-                            catch (Exception e)
-                            {
-                                e.printStackTrace();
-                            }
-
-                            if (response != null)
-                            {
-                                if (response.getNbRows() == 0)
-                                {
-                                    int tmpIndex = semiQuery.lastIndexOf("=");
-                                    if (chosenChange.getParentTableColumn().getTypeName().equals("varchar")
-                                            || chosenChange.getParentTableColumn().getTypeName().equals("bool")
-                                            || chosenChange.getParentTableColumn().getTypeName().equals("timestamp")
-                                            || chosenChange.getParentTableColumn().getTypeName().equals("date")
-                                            || chosenChange.getParentTableColumn().getTypeName().equals("_text")
-                                            || chosenChange.getParentTableColumn().getTypeName().equals("text")
-                                            || chosenChange.getParentTableColumn().getTypeName().equals("fulltext"))
-                                        semiQuery = semiQuery.substring(tmpIndex) + "' " + chosenChange.getOldValue()+"' ";
-                                    else
-                                        semiQuery = semiQuery.substring(tmpIndex) + chosenChange.getOldValue();
-
-                                    try
-                                    {
-                                        Statement stmt = sqlService.getConnection().createStatement();
-                                        ResultSet res = stmt.executeQuery(semiQuery);
-                                        qrp = new QueryResponseParser();
-                                        ArrayList<Row> rows = new ArrayList<Row>(qrp.parse(res, tb.getTable()).getRows());
-                                        response = new QueryResponse(rows);
-                                    }
-                                    catch (Exception e)
-                                    {
-                                        e.printStackTrace();
-                                    }
-
-                                    if(!response.getRows().isEmpty())
-                                    {
-                                        for (int i = 0; i < response.getNbRows(); i++)
-                                        {
-                                            FkGenericTreeNode tmp = new FkGenericTreeNode(response.getRows().get(i), this, new SingleChange(chosenChange.getParentTableColumn(), this, chosenChange.getOldValue(), chosenChange.getNewValue()));
-                                            fkMutations.put(tb,tmp);
-                                            theQuery = theQuery + tmp.updateQueryBuilder(false, db, sqlService);
-                                        }
-                                    }
-                                }
-                            }
-                        }
-                    }
+        QueryResponseParser qrp;
+        QueryResponse response = null;
 
+        Collection<ForeignKeyConstraint> lesFk= db.getLesForeignKeys().get(sgParentColumn.getTable().getName().toUpperCase());
+        for(ForeignKeyConstraint fk : lesFk)
+        {
+            if(fk.getChildColumns().contains(sgParentColumn))
+            {
+                chosenChange.setParentTableColumn(fk.getParentColumns().get(0)); // might require some change if there are multiple parents to one field
+            }
+        }
 
-        theQuery = theQuery + updateQueryBuilder(undo,db,sqlService);
-        theQuery = theQuery + " ; COMMIT TRANSACTION;";
+        String semiQuery = "SELECT * FROM " + chosenChange.getParentTableColumn().getTable().getName() + " WHERE " + chosenChange.getParentTableColumn().getName() + "=";
+        if (chosenChange.getParentTableColumn().getTypeName().equals("varchar")
+                || chosenChange.getParentTableColumn().getTypeName().equals("bool")
+                || chosenChange.getParentTableColumn().getTypeName().equals("timestamp")
+                || chosenChange.getParentTableColumn().getTypeName().equals("date")
+                || chosenChange.getParentTableColumn().getTypeName().equals("_text")
+                || chosenChange.getParentTableColumn().getTypeName().equals("text")
+                || chosenChange.getParentTableColumn().getTypeName().equals("fulltext"))
+            semiQuery = semiQuery + chosenChange.getOldValue() + "' ";
+        else
+            semiQuery = semiQuery + chosenChange.getOldValue();
 
-        System.out.println("Total query = "+theQuery);
-        return theQuery;
+
+
+        try {
+            Statement stmt = sqlService.getConnection().createStatement();
+            ResultSet res = stmt.executeQuery(semiQuery);
+            qrp = new QueryResponseParser();
+            ArrayList<Row> rows = new ArrayList<Row>(qrp.parse(res, chosenChange.getParentTableColumn().getTable()).getRows());
+            response = new QueryResponse(rows);
+        } catch (Exception e) {
+            e.printStackTrace();
+        }
+
+        initial_state_row = response.getRows().get(0);
     }
 
-    public boolean checkIfHasFk(Database db)
+    public boolean checkIfHasParentFk(Database db)
     {
         Collection<ForeignKeyConstraint> lesFk= db.getLesForeignKeys().get(chosenChange.getParentTableColumn().getTable().getName().toUpperCase());
         for(ForeignKeyConstraint fk : lesFk)
         {
-            if(fk.getChildColumns().contains(chosenChange.getParentTableColumn()))
+            if(!fk.getParentColumns().isEmpty())
                 return true;
         }
         return false;
diff --git a/src/main/java/org/schemaspy/model/QueryResponseParser.java b/src/main/java/org/schemaspy/model/QueryResponseParser.java
index e25956a..e24b825 100644
--- a/src/main/java/org/schemaspy/model/QueryResponseParser.java
+++ b/src/main/java/org/schemaspy/model/QueryResponseParser.java
@@ -68,7 +68,6 @@ public class QueryResponseParser
             Row currentRow = new Row(parentTable,mapOfTheRow,resultMeta.getColumnCount());
             queryResponse.getRows().add(currentRow);
           }
-          assert(!queryResponse.getRows().get(0).getContent().containsValue(null));
           return queryResponse;
         }
 
diff --git a/src/main/java/org/schemaspy/model/Row.java b/src/main/java/org/schemaspy/model/Row.java
index 53f7d80..a5858d7 100644
--- a/src/main/java/org/schemaspy/model/Row.java
+++ b/src/main/java/org/schemaspy/model/Row.java
@@ -48,7 +48,11 @@ public class Row
         this.nbKeys = nbKeys;
 	}
 
-  public Table getParentTable()
+    public void setParentTable(Table parentTable) {
+        this.parentTable = parentTable;
+    }
+
+    public Table getParentTable()
   {
     return this.parentTable;
   }
diff --git a/src/main/java/org/schemaspy/model/SingleChange.java b/src/main/java/org/schemaspy/model/SingleChange.java
index b0be256..897b517 100644
--- a/src/main/java/org/schemaspy/model/SingleChange.java
+++ b/src/main/java/org/schemaspy/model/SingleChange.java
@@ -39,7 +39,11 @@ public class SingleChange
     }
   }
 
-   @Override
+    public void setParentTableColumn(TableColumn parentTableColumn) {
+        this.parentTableColumn = parentTableColumn;
+    }
+
+    @Override
    public String toString()
    {
      return "\n[SG - attachedToMutation : "+this.getAttachedToMutation().getId()+"| OV :"+oldValue+" | NV :"+newValue+" ]\n";