/**
* 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();
}
/**
* 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();
}
