Beispiel #1
0
 /**
  * @clonedesc com.servoy.j2db.querybuilder.IQueryBuilderJoins#add(String, int, String)
  * @sampleas add(String, int)
  * @param dataSource data source
  * @param joinType join type, one of {@link IQueryBuilderJoin#LEFT_OUTER_JOIN}, {@link
  *     IQueryBuilderJoin#INNER_JOIN}, {@link IQueryBuilderJoin#RIGHT_OUTER_JOIN}, {@link
  *     IQueryBuilderJoin#FULL_JOIN}
  * @param alias the alias for joining table
  */
 @JSFunction
 public QBJoin add(String dataSource, int joinType, String alias) throws RepositoryException {
   String name;
   QBJoin join;
   if (alias == null) {
     name = UUID.randomUUID().toString();
     join = null;
   } else {
     name = alias;
     join = joins.get(name);
   }
   if (join == null) {
     Table foreignTable = root.getTable(dataSource);
     join =
         addJoin(
             new QueryJoin(
                 name,
                 parent.getQueryTable(),
                 new QueryTable(
                     foreignTable.getSQLName(),
                     foreignTable.getDataSource(),
                     foreignTable.getCatalog(),
                     foreignTable.getSchema(),
                     alias),
                 new AndCondition(),
                 joinType),
             dataSource,
             name);
   }
   return join;
 }
Beispiel #2
0
  private QBJoin getOrAddRelation(IRelation relation, String relationName, String alias) {
    if (relation == null || !parent.getDataSource().equals(relation.getPrimaryDataSource())) {
      if (relation == null) {
        Debug.log("relation '" + relationName + "' not found");
      } else {
        Debug.log(
            "relation '"
                + relationName
                + "' does not match parent data source: "
                + parent.getDataSource()
                + '/'
                + relation.getPrimaryDataSource());
      }
      return null;
    }

    String name = alias == null ? relationName : alias;
    QBJoin join = joins.get(name);
    if (join == null) {
      try {
        Table foreignTable = root.getTable(relation.getForeignDataSource());
        if (foreignTable == null) {
          Debug.log("foreign table for relation '" + relationName + "' not found");
          return null;
        }
        join =
            addJoin(
                SQLGenerator.createJoin(
                    root.getDataProviderHandler(),
                    relation,
                    parent.getQueryTable(),
                    new QueryTable(
                        foreignTable.getSQLName(),
                        foreignTable.getDataSource(),
                        foreignTable.getCatalog(),
                        foreignTable.getSchema(),
                        alias),
                    root.getGlobalScopeProvider()),
                relation.getForeignDataSource(),
                name);
      } catch (RepositoryException e) {
        Debug.error("could not load relation '" + relationName + "'", e);
      }
    }
    return join;
  }
    private String createInversedFKRelationName(Table table, List<String[]> rel_items_list) {
      if (rel_items_list.size() == 0) {
        return null;
      }

      String[] element = rel_items_list.get(0);
      String ftableName = element[0];
      String fcolumnName = element[1];
      String ptableName = element[2];
      String pcolumnName = element[3];

      String relationName =
          EditorUtil.getRelationName(ptableName, ftableName, pcolumnName, fcolumnName);
      if (rel_items_list.size() > 1 || relationName == null || relationName.trim().length() == 0) {
        return table.getSQLName() + "_to_" + ptableName;
      }

      return relationName;
    }
  private List getSortColumns(Table table, String sortColumnProvider) {
    List sortColumns = null;

    if (sortColumnProvider != null) {
      sortColumnProvider = sortColumnProvider.trim();

      StringTokenizer sortColumnProviderTokenizer = new StringTokenizer(sortColumnProvider, ",");

      String sortColumnToken;
      while (sortColumnProviderTokenizer.hasMoreTokens()) {
        sortColumnToken = sortColumnProviderTokenizer.nextToken().trim();
        StringTokenizer sortColumnTokenizer = new StringTokenizer(sortColumnToken, " ");

        String sortColumnName = null;
        int sortOrder = SortColumn.ASCENDING;

        if (sortColumnTokenizer.hasMoreTokens()) sortColumnName = sortColumnTokenizer.nextToken();
        if (sortColumnTokenizer.hasMoreTokens()) {
          String sOrder = sortColumnTokenizer.nextToken().toLowerCase();

          if (sOrder.equals("desc")) sortOrder = SortColumn.DESCENDING;
        }

        if (sortColumnName != null) {
          IColumn sortColumn = table.getColumn(sortColumnName);
          if (sortColumn != null) {
            SortColumn sCol = new SortColumn(sortColumn);
            sCol.setSortOrder(sortOrder);

            if (sortColumns == null) sortColumns = new ArrayList();

            sortColumns.add(sCol);
          }
        }
      }
    }

    return sortColumns;
  }
    public List<RelationData> loadData(IServerInternal server, IProgressMonitor monitor) {
      List<RelationData> relations = new ArrayList<RelationData>();
      try {
        List<String> tableNames = server.getTableAndViewNames(true, true);
        // plus 1 for sorting at the end
        monitor.beginTask("Loading relations", tableNames.size() + 1);
        for (String tableName : tableNames) {
          if (isCanceled()) {
            return null;
          }
          Table table = server.getTable(tableName);
          Connection connection = null;
          ResultSet resultSet = null;
          try {
            connection = server.getConnection();
            DatabaseMetaData dbmd = connection.getMetaData();
            Map<String, List<List<String[]>>> relationInfo =
                new HashMap<String, List<List<String[]>>>();

            resultSet =
                dbmd.getExportedKeys(
                    server.getConfig().getCatalog(),
                    server.getConfig().getSchema(),
                    table.getSQLName());
            while (resultSet.next()) {
              String pcolumnName = resultSet.getString("PKCOLUMN_NAME");
              String ftableName = resultSet.getString("FKTABLE_NAME");
              String fcolumnName = resultSet.getString("FKCOLUMN_NAME");
              String fkname = resultSet.getString("FK_NAME");

              String relname = fkname;
              if (relname == null) relname = table.getSQLName() + "_to_" + ftableName;

              int keySeq = resultSet.getInt("KEY_SEQ");
              Debug.trace(
                  "Found (export) rel: name: "
                      + relname
                      + "  keyseq = "
                      + keySeq
                      + ' '
                      + table.getSQLName()
                      + ' '
                      + pcolumnName
                      + " -> "
                      + ftableName
                      + ' '
                      + fcolumnName);

              List<List<String[]>> rel_items_list = relationInfo.get(relname);
              if (rel_items_list == null) {
                rel_items_list = new ArrayList<List<String[]>>();
                relationInfo.put(relname, rel_items_list);
                rel_items_list.add(new ArrayList<String[]>());
              }
              // rel_items_list is a list of items-lists, we are adding items to the last of this
              // list
              rel_items_list
                  .get(rel_items_list.size() - 1)
                  .add(
                      new String[] {
                        table.getSQLName(), pcolumnName, ftableName, fcolumnName, fkname
                      });
            }
            resultSet = Utils.closeResultSet(resultSet);

            resultSet =
                dbmd.getImportedKeys(
                    server.getConfig().getCatalog(),
                    server.getConfig().getSchema(),
                    table.getSQLName());
            int lastKeySeq = Integer.MAX_VALUE;
            List<List<String[]>> fk_rel_items_list = new ArrayList<List<String[]>>();
            while (resultSet.next()) {
              String pcolumnName = resultSet.getString("PKCOLUMN_NAME");
              String ptableName = resultSet.getString("PKTABLE_NAME");
              String fcolumnName = resultSet.getString("FKCOLUMN_NAME");

              int keySeq = resultSet.getInt("KEY_SEQ");
              Debug.trace(
                  "Found (import) rel: name: "
                      + table.getSQLName()
                      + "_to_"
                      + ptableName
                      + " keyseq = "
                      + keySeq
                      + ' '
                      + table.getSQLName()
                      + ' '
                      + pcolumnName
                      + " -> "
                      + ptableName
                      + ' '
                      + fcolumnName);

              // assume KEY_SEQ ascending ordered, do not assume 0 or 1 based (jdbc spec is not
              // clear on this).
              // when KEY_SEQ is not increasing, we have a separate constraint between the same
              // tables.
              if (fk_rel_items_list.size() == 0 || keySeq <= lastKeySeq) {
                fk_rel_items_list.add(new ArrayList<String[]>());
              }
              lastKeySeq = keySeq;

              // add the item to the last list of rel_items_list
              fk_rel_items_list
                  .get(fk_rel_items_list.size() - 1)
                  .add(
                      new String[] {
                        table.getSQLName(), fcolumnName, ptableName, pcolumnName, null
                      });
            }

            // generate relation names for the inversed fk constraints
            for (List<String[]> rel_items_list : fk_rel_items_list) {
              String relationName = createInversedFKRelationName(table, rel_items_list);
              List<List<String[]>> rel_items = relationInfo.get(relationName);
              if (rel_items == null) {
                relationInfo.put(relationName, rel_items = new ArrayList<List<String[]>>());
              }
              rel_items.add(rel_items_list);
            }
            resultSet = Utils.closeResultSet(resultSet);

            for (Map.Entry<String, List<List<String[]>>> entry : relationInfo.entrySet()) {
              String rname = entry.getKey();
              List<List<String[]>> rel_items_list = entry.getValue();
              // we may have multiple lists of items defined for the same relation name
              for (int l = 0; l < rel_items_list.size(); l++) {
                List<Column> primaryColumns = new ArrayList<Column>();
                List<Column> foreignColumns = new ArrayList<Column>();

                for (String[] element : rel_items_list.get(l)) {
                  //									String ptableName = element[0];
                  String pcolumnName = element[1];
                  String ftableName = element[2];
                  String fcolumnName = element[3];
                  //									String fkname = element[4];

                  Table foreignTable = server.getTable(ftableName);
                  if (foreignTable == null || foreignTable.isMarkedAsHiddenInDeveloper()) continue;

                  Column primaryColumn = table.getColumn(pcolumnName);
                  Column foreignColumn = foreignTable.getColumn(fcolumnName);

                  if (primaryColumn == null || foreignColumn == null) continue;
                  primaryColumns.add(primaryColumn);
                  foreignColumns.add(foreignColumn);
                }

                if (primaryColumns.size() != 0) {
                  // postfix the relation name when there are multiple
                  String relationName = rname;
                  if (rel_items_list.size() > 1) {
                    relationName += "_" + (l + 1);
                  }

                  boolean defaultAdd =
                      ServoyModelManager.getServoyModelManager()
                              .getServoyModel()
                              .getFlattenedSolution()
                              .getRelation(relationName)
                          == null;

                  relations.add(
                      new RelationData(
                          relationName, table, primaryColumns, foreignColumns, defaultAdd));
                }
              }
            }
          } catch (RepositoryException e) {
            ServoyLog.logError(e);
          } catch (SQLException e) {
            ServoyLog.logError(e);
          } finally {
            Utils.closeResultSet(resultSet);
            Utils.closeConnection(connection);
          }
          monitor.worked(1);
        }
        monitor.done();
      } catch (RepositoryException ex) {
        ServoyLog.logError(ex);
      }
      return relations;
    }