[Squirrel-sql-commits] SF.net SVN: squirrel-sql:[6343] trunk/sql12/plugins/graph/src/main/java/net /sourceforge/squirrel_sql/plugins/graph/querybuilder/sqlgen

[<ger...@us...>]

Revision: 6343
          http://squirrel-sql.svn.sourceforge.net/squirrel-sql/?rev=6343&view=rev
Author:   gerdwagner
Date:     2011-08-07 09:45:18 +0000 (Sun, 07 Aug 2011)

Log Message:
-----------
Query Builder: Fixed/improved handling of circular joins in SQL generation through aliases

Modified Paths:
--------------
    trunk/sql12/plugins/graph/src/main/java/net/sourceforge/squirrel_sql/plugins/graph/querybuilder/sqlgen/FromClauseGenerator.java
    trunk/sql12/plugins/graph/src/main/java/net/sourceforge/squirrel_sql/plugins/graph/querybuilder/sqlgen/FromClauseRes.java

Modified: trunk/sql12/plugins/graph/src/main/java/net/sourceforge/squirrel_sql/plugins/graph/querybuilder/sqlgen/FromClauseGenerator.java
===================================================================
--- trunk/sql12/plugins/graph/src/main/java/net/sourceforge/squirrel_sql/plugins/graph/querybuilder/sqlgen/FromClauseGenerator.java	2011-08-06 20:30:55 UTC (rev 6342)
+++ trunk/sql12/plugins/graph/src/main/java/net/sourceforge/squirrel_sql/plugins/graph/querybuilder/sqlgen/FromClauseGenerator.java	2011-08-07 09:45:18 UTC (rev 6343)
@@ -75,8 +75,12 @@
       bauf = findConstraintViews(tfcTo, tfcFrom.getTableInfo().getSimpleName());
       constraints.addAll(Arrays.asList(bauf));
 
-      writeConstraints(fromClauseRes, constraints, tableNameFrom, tableNameTo);
+      String aliasFrom = fromClauseRes.getCurrentAlias(tfcFrom);
+      String aliasTo = fromClauseRes.getAliasForNextJoinIfNeeded(tfcTo);
 
+
+      writeConstraints(fromClauseRes, constraints, tableNameFrom, tableNameTo, aliasFrom, aliasTo);
+
       fromClauseRes.addTable(tfcFrom);
       fromClauseRes.addTable(tfcTo);
       HashSet<TableFrameController> relatives = getRelatives(tableFramesModel, tfcTo);
@@ -92,10 +96,11 @@
 
    }
 
-   private void writeConstraints(FromClauseRes fromClauseRes, ArrayList<ConstraintView> constrains, String tableNameFrom, String tableNameTo)
+   private void writeConstraints(FromClauseRes fromClauseRes, ArrayList<ConstraintView> constrains, String tableNameFrom, String tableNameTo, String aliasFrom, String aliasTo)
    {
 
-      int aliasCount = 0;
+      // see ConstraintViewsModel.checkMerges()
+      int multipleJoinsBetweenTwoTablesAliasCount = 0;
 
       for (ConstraintView constraint : constrains)
       {
@@ -105,16 +110,23 @@
             continue;
          }
 
-         String tableNameTo_aliased = tableNameTo;
+         String tableNameTo_aliased = getNameOrAlias(tableNameTo, aliasTo);
 
-         if( 0 < aliasCount++)
+         if( 0 < multipleJoinsBetweenTwoTablesAliasCount++)
          {
-            tableNameTo_aliased = tableNameTo + "_" + aliasCount;
+            tableNameTo_aliased = getNameOrAlias(tableNameTo, aliasTo) + "_" + multipleJoinsBetweenTwoTablesAliasCount;
             fromClauseRes.append(" " + getJoinType(data, tableNameFrom, tableNameTo) + " JOIN " + tableNameTo + " " + tableNameTo_aliased + " ON ");
          }
          else
          {
-            fromClauseRes.append(" " + getJoinType(data, tableNameFrom, tableNameTo) + " JOIN " + tableNameTo + " ON ");
+            if (null == aliasTo)
+            {
+               fromClauseRes.append(" " + getJoinType(data, tableNameFrom, tableNameTo) + " JOIN " + tableNameTo + " ON ");
+            }
+            else
+            {
+               fromClauseRes.append(" " + getJoinType(data, tableNameFrom, tableNameTo) + " JOIN " + tableNameTo + " AS " + aliasTo +" ON ");
+            }
          }
 
 
@@ -142,7 +154,7 @@
                fromCol = fkCol.getColumnName();
             }
 
-            fromClauseRes.append(tableNameFrom + "." + fromCol);
+            fromClauseRes.append(getNameOrAlias(tableNameFrom, aliasFrom) + "." + fromCol);
             fromClauseRes.append(" = ");
             fromClauseRes.append(tableNameTo_aliased + "." + toCol);
 
@@ -150,6 +162,11 @@
       }
    }
 
+   private String getNameOrAlias(String tableName, String alias)
+   {
+      return null == alias ? tableName : alias;
+   }
+
    private String getJoinType(ConstraintData data, String tableNameFrom, String tableNameTo)
    {
       if(data.getConstraintQueryData().isOuterJoinFor(tableNameTo))

Modified: trunk/sql12/plugins/graph/src/main/java/net/sourceforge/squirrel_sql/plugins/graph/querybuilder/sqlgen/FromClauseRes.java
===================================================================
--- trunk/sql12/plugins/graph/src/main/java/net/sourceforge/squirrel_sql/plugins/graph/querybuilder/sqlgen/FromClauseRes.java	2011-08-06 20:30:55 UTC (rev 6342)
+++ trunk/sql12/plugins/graph/src/main/java/net/sourceforge/squirrel_sql/plugins/graph/querybuilder/sqlgen/FromClauseRes.java	2011-08-07 09:45:18 UTC (rev 6343)
@@ -3,12 +3,15 @@
 import net.sourceforge.squirrel_sql.plugins.graph.TableFrameController;
 
 import java.util.ArrayList;
+import java.util.HashMap;
 
 public class FromClauseRes
 {
    private StringBuffer _from;
    private ArrayList<TableFrameController> _tables = new ArrayList<TableFrameController>();
 
+   private HashMap<TableFrameController, Integer> _aliasNrByTfc = new HashMap<TableFrameController, Integer>();
+
    public FromClauseRes(TableFrameController tfcStart)
    {
       _from = new StringBuffer(" FROM " + tfcStart.getTableInfo().getSimpleName());
@@ -43,4 +46,42 @@
    {
       return _tables;
    }
+
+   public String getAliasForNextJoinIfNeeded(TableFrameController tfc)
+   {
+      if(false == _tables.contains(tfc))
+      {
+         return null;
+      }
+
+      Integer nr = _aliasNrByTfc.get(tfc);
+      if(null == nr)
+      {
+         nr = 1;
+      }
+      else
+      {
+         ++nr;
+      }
+      _aliasNrByTfc.put(tfc, nr);
+
+      return generateAliasName(tfc, nr);
+   }
+
+   private String generateAliasName(TableFrameController tfc, Integer nr)
+   {
+      return tfc.getTableInfo().getSimpleName() + "_" + nr;
+   }
+
+   public String getCurrentAlias(TableFrameController tfc)
+   {
+      Integer nr = _aliasNrByTfc.get(tfc);
+      if(null == nr)
+      {
+         return null;
+      }
+
+      return generateAliasName(tfc, nr);
+
+   }
 }


This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.