예제 #1
0
  /**
   * Generated View DDL that supports the Teiid OData requirement - that views must have a Primary
   * Key - to get auto-generated. A list of columnNames may be provided of columns to include in the
   * view. If columnNames is null or empty, then all columns are included.
   *
   * @param uow the transaction
   * @param viewName the view name
   * @param table the table for generating the view
   * @param columnNames the list of table columns to include in the view (if <code>null</code> or
   *     empty, all columns will be included)
   * @return the View DDL
   * @throws KException if problem occurs
   */
  public static String getODataViewDdl(
      UnitOfWork uow, String viewName, Table table, List<String> columnNames) throws KException {

    List<String> includedColumnNames = new ArrayList<String>();
    if (columnNames != null && !columnNames.isEmpty()) {
      includedColumnNames.addAll(columnNames);
    }
    boolean includeAllColumns = (includedColumnNames.isEmpty()) ? true : false;

    StringBuilder sb = new StringBuilder();
    TeiidVersion teiidVersion = TeiidVersionProvider.getInstance().getTeiidVersion();

    // Determine constraints from table if available
    String constraintStr = getPkConstraint(uow, teiidVersion, table);
    if (StringUtils.isEmpty(constraintStr)) {
      constraintStr = getUcConstraint(uow, teiidVersion, table);
    }

    // If the table has constraints, make sure the constraint columns for the table will be included
    if (constraintStr.length() > 0 && !includeAllColumns) {
      Set<String> constrColNames = getConstraintColumnNames(uow, table);
      for (String constrColName : constrColNames) {
        if (!includedColumnNames.contains(constrColName)) {
          includedColumnNames.add(constrColName);
        }
      }
    }

    // Get table column names and types
    List<String> colNames = new ArrayList<String>();
    List<String> colTypes = new ArrayList<String>();
    Column[] columns = table.getColumns(uow);
    for (int i = 0; i < columns.length; i++) {
      if (includeAllColumns || includedColumnNames.contains(columns[i].getName(uow))) {
        colNames.add(columns[i].getName(uow));
        colTypes.add(columns[i].getDatatypeName(uow));
      }
    }

    // Generate the View DDL
    sb.append("CREATE VIEW "); // $NON-NLS-1$
    sb.append(viewName);
    sb.append(StringConstants.SPACE + StringConstants.OPEN_BRACKET);

    // Use generated table constraints if available
    if (constraintStr.length() > 0) {
      sb.append(getColWithTypeString(teiidVersion, colNames, colTypes));
      sb.append(StringConstants.COMMA + StringConstants.SPACE);
      sb.append(constraintStr);
      sb.append(") AS \n"); // $NON-NLS-1$
      sb.append("SELECT "); // $NON-NLS-1$
      sb.append(getColString(teiidVersion, colNames));
      // No table constraint found - generate a primary key
    } else {
      sb.append("RowId integer PRIMARY KEY,"); // $NON-NLS-1$
      sb.append(getColWithTypeString(teiidVersion, colNames, colTypes));
      sb.append(") AS \nSELECT ROW_NUMBER() OVER (ORDER BY "); // $NON-NLS-1$
      sb.append(escapeSQLName(teiidVersion, colNames.get(0)));
      sb.append(StringConstants.CLOSE_BRACKET + StringConstants.COMMA);
      sb.append(getColString(teiidVersion, colNames));
    }
    sb.append(" \n"); // $NON-NLS-1$
    sb.append("FROM "); // $NON-NLS-1$
    sb.append(escapeSQLName(teiidVersion, table.getName(uow)));
    sb.append(StringConstants.SEMI_COLON);

    return sb.toString();
  }
예제 #2
0
  /**
   * Generated View DDL that supports the Teiid OData requirement - that views must have a Primary
   * Key - to get auto-generated.
   *
   * @param uow the transaction
   * @param viewName the view name
   * @param lhTable the left side table
   * @param lhTableAlias the alias to use for the left table
   * @param lhColNames the left side column names
   * @param rhTable the right side table
   * @param rhTableAlias the alias to use for the right table
   * @param rhColNames the right side column names
   * @param lhCriteriaCol the left side criteria column
   * @param rhCriteriaCol the right side criteria column
   * @param joinType the join type
   * @return the View DDL
   * @throws KException if problem occurs
   */
  public static String getODataViewJoinDdl(
      UnitOfWork uow,
      String viewName,
      Table lhTable,
      String lhTableAlias,
      List<String> lhColNames,
      Table rhTable,
      String rhTableAlias,
      List<String> rhColNames,
      String lhCriteriaCol,
      String rhCriteriaCol,
      String joinType)
      throws KException {

    StringBuilder sb = new StringBuilder();
    TeiidVersion teiidVersion = TeiidVersionProvider.getInstance().getTeiidVersion();

    // Left and Right table names
    String lhTableNameAliased = lhTable.getName(uow) + " AS " + lhTableAlias; // $NON-NLS-1$
    String rhTableNameAliased = rhTable.getName(uow) + " AS " + rhTableAlias; // $NON-NLS-1$

    List<String> includedLHColumnNames = new ArrayList<String>();
    if (lhColNames != null && !lhColNames.isEmpty()) {
      includedLHColumnNames.addAll(lhColNames);
    }
    boolean includeAllLHColumns = (includedLHColumnNames.isEmpty()) ? true : false;

    List<String> includedRHColumnNames = new ArrayList<String>();
    if (rhColNames != null && !rhColNames.isEmpty()) {
      includedRHColumnNames.addAll(rhColNames);
    }
    boolean includeAllRHColumns = (includedRHColumnNames.isEmpty()) ? true : false;

    // Get LHS table column names and types
    List<String> leftColNames = new ArrayList<String>();
    List<String> leftColTypes = new ArrayList<String>();
    Column[] lhCols = lhTable.getColumns(uow);
    for (int i = 0; i < lhCols.length; i++) {
      if (includeAllLHColumns || includedLHColumnNames.contains(lhCols[i].getName(uow))) {
        leftColNames.add(lhCols[i].getName(uow));
        leftColTypes.add(lhCols[i].getDatatypeName(uow));
      }
    }

    // Get RHS table column names and types
    List<String> rightColNames = new ArrayList<String>();
    List<String> rightColTypes = new ArrayList<String>();
    Column[] rhCols = rhTable.getColumns(uow);
    for (int i = 0; i < rhCols.length; i++) {
      if (includeAllRHColumns || includedRHColumnNames.contains(rhCols[i].getName(uow))) {
        rightColNames.add(rhCols[i].getName(uow));
        rightColTypes.add(rhCols[i].getDatatypeName(uow));
      }
    }

    sb.append("CREATE VIEW "); // $NON-NLS-1$
    sb.append(viewName);
    sb.append(StringConstants.SPACE + StringConstants.OPEN_BRACKET);

    sb.append("RowId integer PRIMARY KEY, "); // $NON-NLS-1$
    sb.append(getColWithTypeString(teiidVersion, leftColNames, leftColTypes));
    sb.append(StringConstants.COMMA + StringConstants.SPACE);
    sb.append(getColWithTypeString(teiidVersion, rightColNames, rightColTypes));
    sb.append(") AS \nSELECT "); // $NON-NLS-1$
    sb.append("ROW_NUMBER() OVER (ORDER BY "); // $NON-NLS-1$
    sb.append(
        getAliasedFirstColName(
            teiidVersion, leftColNames, lhTableAlias, rightColNames, rhTableAlias));
    sb.append("), "); // $NON-NLS-1$
    if (leftColNames.size() > 0) {
      sb.append(getAliasedColString(teiidVersion, leftColNames, lhTableAlias));
      sb.append(StringConstants.COMMA + StringConstants.SPACE);
    }
    sb.append(getAliasedColString(teiidVersion, rightColNames, rhTableAlias));
    sb.append(" \nFROM \n"); // $NON-NLS-1$
    sb.append(lhTableNameAliased + StringConstants.SPACE);
    if (JOIN_INNER.equals(joinType)) {
      sb.append("\nINNER JOIN \n")
          .append(rhTableNameAliased + StringConstants.SPACE); // $NON-NLS-1$
    } else if (JOIN_LEFT_OUTER.equals(joinType)) {
      sb.append("\nLEFT OUTER JOIN \n")
          .append(rhTableNameAliased + StringConstants.SPACE); // $NON-NLS-1$
    } else if (JOIN_RIGHT_OUTER.equals(joinType)) {
      sb.append("\nRIGHT OUTER JOIN \n")
          .append(rhTableNameAliased + StringConstants.SPACE); // $NON-NLS-1$
    } else if (JOIN_FULL_OUTER.equals(joinType)) {
      sb.append("\nFULL OUTER JOIN \n")
          .append(rhTableNameAliased + StringConstants.SPACE); // $NON-NLS-1$
    } else {
      sb.append("\nINNER JOIN \n")
          .append(rhTableNameAliased + StringConstants.SPACE); // $NON-NLS-1$
    }
    sb.append("\nON \n"); // $NON-NLS-1$
    sb.append(lhTableAlias + StringConstants.DOT)
        .append(escapeSQLName(teiidVersion, lhCriteriaCol))
        .append(StringConstants.SPACE + StringConstants.EQUALS + StringConstants.SPACE)
        .append(rhTableAlias + StringConstants.DOT)
        .append(escapeSQLName(teiidVersion, rhCriteriaCol));
    sb.append(StringConstants.SEMI_COLON);

    return sb.toString();
  }