ViewDdlBuilder { public static String getODataViewJoinDdl(UnitOfWork uow, String viewName, Table lhTable, String lhTableAlias, List<String> lhColNames, Table rhTable, String rhTableAlias, List<String> rhColNames, String joinType, List<ViewBuilderCriteriaPredicate> criteriaPredicates) throws KException { StringBuilder sb = new StringBuilder(); TeiidVersion teiidVersion = TeiidVersionProvider.getInstance().getTeiidVersion(); String lhVdbName = lhTable.getParent(uow).getParent(uow).getName(uow); String rhVdbName = rhTable.getParent(uow).getParent(uow).getName(uow); String lhTableNameAliased = lhVdbName+StringConstants.DOT+lhTable.getName(uow)+" AS "+lhTableAlias; String rhTableNameAliased = rhVdbName+StringConstants.DOT+rhTable.getName(uow)+" AS "+rhTableAlias; 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; List<String> allLeftColNames = new ArrayList<String>(); 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++) { String lName = lhCols[i].getName(uow); allLeftColNames.add(lName); if(includeAllLHColumns || includedLHColumnNames.contains(lName)) { leftColNames.add(lName); leftColTypes.add(lhCols[i].getDatatypeName(uow)); } } List<String> allRightColNames = new ArrayList<String>(); 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++) { String rName = rhCols[i].getName(uow); allRightColNames.add(rName); if(includeAllRHColumns || includedRHColumnNames.contains(rName)) { rightColNames.add(rName); rightColTypes.add(rhCols[i].getDatatypeName(uow)); } } sb.append("CREATE VIEW "); sb.append(viewName); sb.append(StringConstants.SPACE+StringConstants.OPEN_BRACKET); sb.append("RowId integer PRIMARY KEY, "); List<String> duplicateColNames = getDuplicateColumnNames(leftColNames, rightColNames); sb.append(getAliasedColWithTypeString(teiidVersion, leftColNames, leftColTypes, lhTableAlias, duplicateColNames)); sb.append(StringConstants.COMMA+StringConstants.SPACE); sb.append(getAliasedColWithTypeString(teiidVersion, rightColNames, rightColTypes, rhTableAlias, duplicateColNames)); sb.append(") AS \nSELECT "); sb.append("ROW_NUMBER() OVER (ORDER BY "); sb.append(getAliasedFirstColName(teiidVersion, leftColNames, lhTableAlias, rightColNames, rhTableAlias)); sb.append("), "); 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"); sb.append(lhTableNameAliased+StringConstants.SPACE); if(JOIN_INNER.equals(joinType)) { sb.append("\nINNER JOIN \n").append(rhTableNameAliased+StringConstants.SPACE); } else if(JOIN_LEFT_OUTER.equals(joinType)) { sb.append("\nLEFT OUTER JOIN \n").append(rhTableNameAliased+StringConstants.SPACE); } else if(JOIN_RIGHT_OUTER.equals(joinType)) { sb.append("\nRIGHT OUTER JOIN \n").append(rhTableNameAliased+StringConstants.SPACE); } else if(JOIN_FULL_OUTER.equals(joinType)) { sb.append("\nFULL OUTER JOIN \n").append(rhTableNameAliased+StringConstants.SPACE); } else { sb.append("\nINNER JOIN \n").append(rhTableNameAliased+StringConstants.SPACE); } if(criteriaPredicates!=null && criteriaPredicates.size()>0) { sb.append("\nON \n"); int nPredicates = criteriaPredicates.size(); for(int i=0; i < nPredicates; i++) { String lhCol = criteriaPredicates.get(i).getLhColumn(); String rhCol = criteriaPredicates.get(i).getRhColumn(); String oper = criteriaPredicates.get(i).getOperator(); String keyword = criteriaPredicates.get(i).getCombineKeyword(); sb.append(lhTableAlias+StringConstants.DOT).append(escapeSQLName(teiidVersion,lhCol)) .append(StringConstants.SPACE+oper+StringConstants.SPACE) .append(rhTableAlias+StringConstants.DOT).append(escapeSQLName(teiidVersion,rhCol)); if( i != nPredicates-1 ) { sb.append(StringConstants.SPACE+keyword+StringConstants.SPACE); } } } sb.append(StringConstants.SEMI_COLON); return sb.toString(); }  static String getODataViewDdl(UnitOfWork uow,
                                         String viewName,
                                         Table table,
                                         List<String> columnNames); static String getODataViewJoinDdl(UnitOfWork uow, String viewName, 
                                             Table lhTable, String lhTableAlias, List<String> lhColNames, 
                                             Table rhTable, String rhTableAlias, List<String> rhColNames, 
                                             String joinType, List<ViewBuilderCriteriaPredicate> criteriaPredicates); static final String JOIN_INNER; static final String JOIN_LEFT_OUTER; static final String JOIN_RIGHT_OUTER; static final String JOIN_FULL_OUTER; }
@Test public void shouldGeneratedODataViewInnerJoinDDL() throws Exception { String EXPECTED_DDL = "CREATE VIEW MyView (RowId integer PRIMARY KEY, LHCol1 string, LHCol2 string, RHCol1 string, RHCol2 string) AS \n" + "SELECT ROW_NUMBER() OVER (ORDER BY A.LHCol1), A.LHCol1, A.LHCol2, B.RHCol1, B.RHCol2 \n" + "FROM \n" + "MyVDB.lhTable AS A \n" + "INNER JOIN \n" + "MyVDB.rhTable AS B \n" + "ON \n" + "A.LHCol1 = B.RHCol2;"; String lhTableAlias = "A"; String rhTableAlias = "B"; Table lhTable = createTable("MyVDB", VDB_PATH, "MyModel", "lhTable"); Column lhCol1 = lhTable.addColumn(getTransaction(), "LHCol1"); lhCol1.setDatatypeName(getTransaction(), "string"); Column lhCol2 = lhTable.addColumn(getTransaction(), "LHCol2"); lhCol2.setDatatypeName(getTransaction(), "string"); Table rhTable = createTable("MyVDB", VDB_PATH, "MyModel", "rhTable"); Column rhCol1 = rhTable.addColumn(getTransaction(), "RHCol1"); rhCol1.setDatatypeName(getTransaction(), "string"); Column rhCol2 = rhTable.addColumn(getTransaction(), "RHCol2"); rhCol2.setDatatypeName(getTransaction(), "string"); List<String> lhColNames = new ArrayList<String>(); lhColNames.add("LHCol1"); lhColNames.add("LHCol2"); List<String> rhColNames = new ArrayList<String>(); rhColNames.add("RHCol1"); rhColNames.add("RHCol2"); String lhCriteriaCol = "LHCol1"; String rhCriteriaCol = "RHCol2"; List<ViewBuilderCriteriaPredicate> criteriaPredicates = new ArrayList<ViewBuilderCriteriaPredicate>(); ViewBuilderCriteriaPredicate predicate = new ViewBuilderCriteriaPredicate(); predicate.setLhColumn(lhCriteriaCol); predicate.setRhColumn(rhCriteriaCol); predicate.setOperator("="); criteriaPredicates.add(predicate); String viewDdl = ViewDdlBuilder.getODataViewJoinDdl(getTransaction(), "MyView", lhTable, lhTableAlias, lhColNames, rhTable, rhTableAlias, rhColNames, ViewDdlBuilder.JOIN_INNER, criteriaPredicates); assertThat(viewDdl, is(EXPECTED_DDL)); }
@Test public void shouldGeneratedODataViewLeftOuterJoinDDL() throws Exception { String EXPECTED_DDL = "CREATE VIEW MyView (RowId integer PRIMARY KEY, LHCol1 string, LHCol2 string, RHCol1 string, RHCol2 string) AS \n" + "SELECT ROW_NUMBER() OVER (ORDER BY A.LHCol1), A.LHCol1, A.LHCol2, B.RHCol1, B.RHCol2 \n" + "FROM \n" + "MyVDB.lhTable AS A \n" + "LEFT OUTER JOIN \n" + "MyVDB.rhTable AS B \n" + "ON \n" + "A.LHCol1 > B.RHCol2;"; String lhTableAlias = "A"; String rhTableAlias = "B"; Table lhTable = createTable("MyVDB", VDB_PATH, "MyModel", "lhTable"); Column lhCol1 = lhTable.addColumn(getTransaction(), "LHCol1"); lhCol1.setDatatypeName(getTransaction(), "string"); Column lhCol2 = lhTable.addColumn(getTransaction(), "LHCol2"); lhCol2.setDatatypeName(getTransaction(), "string"); Table rhTable = createTable("MyVDB", VDB_PATH, "MyModel", "rhTable"); Column rhCol1 = rhTable.addColumn(getTransaction(), "RHCol1"); rhCol1.setDatatypeName(getTransaction(), "string"); Column rhCol2 = rhTable.addColumn(getTransaction(), "RHCol2"); rhCol2.setDatatypeName(getTransaction(), "string"); List<String> lhColNames = new ArrayList<String>(); lhColNames.add("LHCol1"); lhColNames.add("LHCol2"); List<String> rhColNames = new ArrayList<String>(); rhColNames.add("RHCol1"); rhColNames.add("RHCol2"); String lhCriteriaCol = "LHCol1"; String rhCriteriaCol = "RHCol2"; List<ViewBuilderCriteriaPredicate> criteriaPredicates = new ArrayList<ViewBuilderCriteriaPredicate>(); ViewBuilderCriteriaPredicate predicate = new ViewBuilderCriteriaPredicate(); predicate.setLhColumn(lhCriteriaCol); predicate.setRhColumn(rhCriteriaCol); predicate.setOperator(">"); criteriaPredicates.add(predicate); String viewDdl = ViewDdlBuilder.getODataViewJoinDdl(getTransaction(), "MyView", lhTable, lhTableAlias, lhColNames, rhTable, rhTableAlias, rhColNames, ViewDdlBuilder.JOIN_LEFT_OUTER, criteriaPredicates); assertThat(viewDdl, is(EXPECTED_DDL)); }
@Test public void shouldGeneratedODataViewRightOuterJoinDDL() throws Exception { String EXPECTED_DDL = "CREATE VIEW MyView (RowId integer PRIMARY KEY, LHCol1 string, LHCol2 string, RHCol1 string, RHCol2 string) AS \n" + "SELECT ROW_NUMBER() OVER (ORDER BY A.LHCol1), A.LHCol1, A.LHCol2, B.RHCol1, B.RHCol2 \n" + "FROM \n" + "MyVDB.lhTable AS A \n" + "RIGHT OUTER JOIN \n" + "MyVDB.rhTable AS B \n" + "ON \n" + "A.LHCol1 < B.RHCol2;"; String lhTableAlias = "A"; String rhTableAlias = "B"; Table lhTable = createTable("MyVDB", VDB_PATH, "MyModel", "lhTable"); Column lhCol1 = lhTable.addColumn(getTransaction(), "LHCol1"); lhCol1.setDatatypeName(getTransaction(), "string"); Column lhCol2 = lhTable.addColumn(getTransaction(), "LHCol2"); lhCol2.setDatatypeName(getTransaction(), "string"); Table rhTable = createTable("MyVDB", VDB_PATH, "MyModel", "rhTable"); Column rhCol1 = rhTable.addColumn(getTransaction(), "RHCol1"); rhCol1.setDatatypeName(getTransaction(), "string"); Column rhCol2 = rhTable.addColumn(getTransaction(), "RHCol2"); rhCol2.setDatatypeName(getTransaction(), "string"); List<String> lhColNames = new ArrayList<String>(); lhColNames.add("LHCol1"); lhColNames.add("LHCol2"); List<String> rhColNames = new ArrayList<String>(); rhColNames.add("RHCol1"); rhColNames.add("RHCol2"); String lhCriteriaCol = "LHCol1"; String rhCriteriaCol = "RHCol2"; List<ViewBuilderCriteriaPredicate> criteriaPredicates = new ArrayList<ViewBuilderCriteriaPredicate>(); ViewBuilderCriteriaPredicate predicate = new ViewBuilderCriteriaPredicate(); predicate.setLhColumn(lhCriteriaCol); predicate.setRhColumn(rhCriteriaCol); predicate.setOperator("<"); criteriaPredicates.add(predicate); String viewDdl = ViewDdlBuilder.getODataViewJoinDdl(getTransaction(), "MyView", lhTable, lhTableAlias, lhColNames, rhTable, rhTableAlias, rhColNames, ViewDdlBuilder.JOIN_RIGHT_OUTER, criteriaPredicates); assertThat(viewDdl, is(EXPECTED_DDL)); }
@Test public void shouldGeneratedODataViewFullOuterJoinDDL() throws Exception { String EXPECTED_DDL = "CREATE VIEW MyView (RowId integer PRIMARY KEY, LHCol1 string, LHCol2 string, RHCol1 string, RHCol2 string) AS \n" + "SELECT ROW_NUMBER() OVER (ORDER BY A.LHCol1), A.LHCol1, A.LHCol2, B.RHCol1, B.RHCol2 \n" + "FROM \n" + "MyVDB.lhTable AS A \n" + "FULL OUTER JOIN \n" + "MyVDB.rhTable AS B \n" + "ON \n" + "A.LHCol1 <= B.RHCol2;"; String lhTableAlias = "A"; String rhTableAlias = "B"; Table lhTable = createTable("MyVDB", VDB_PATH, "MyModel", "lhTable"); Column lhCol1 = lhTable.addColumn(getTransaction(), "LHCol1"); lhCol1.setDatatypeName(getTransaction(), "string"); Column lhCol2 = lhTable.addColumn(getTransaction(), "LHCol2"); lhCol2.setDatatypeName(getTransaction(), "string"); Table rhTable = createTable("MyVDB", VDB_PATH, "MyModel", "rhTable"); Column rhCol1 = rhTable.addColumn(getTransaction(), "RHCol1"); rhCol1.setDatatypeName(getTransaction(), "string"); Column rhCol2 = rhTable.addColumn(getTransaction(), "RHCol2"); rhCol2.setDatatypeName(getTransaction(), "string"); List<String> lhColNames = new ArrayList<String>(); lhColNames.add("LHCol1"); lhColNames.add("LHCol2"); List<String> rhColNames = new ArrayList<String>(); rhColNames.add("RHCol1"); rhColNames.add("RHCol2"); String lhCriteriaCol = "LHCol1"; String rhCriteriaCol = "RHCol2"; List<ViewBuilderCriteriaPredicate> criteriaPredicates = new ArrayList<ViewBuilderCriteriaPredicate>(); ViewBuilderCriteriaPredicate predicate = new ViewBuilderCriteriaPredicate(); predicate.setLhColumn(lhCriteriaCol); predicate.setRhColumn(rhCriteriaCol); predicate.setOperator("<="); criteriaPredicates.add(predicate); String viewDdl = ViewDdlBuilder.getODataViewJoinDdl(getTransaction(), "MyView", lhTable, lhTableAlias, lhColNames, rhTable, rhTableAlias, rhColNames, ViewDdlBuilder.JOIN_FULL_OUTER, criteriaPredicates); assertThat(viewDdl, is(EXPECTED_DDL)); }
@Test public void shouldGeneratedODataViewJoinNoCriteriaDDL() throws Exception { String EXPECTED_DDL = "CREATE VIEW MyView (RowId integer PRIMARY KEY, LHCol1 string, LHCol2 string, RHCol1 string, RHCol2 string) AS \n" + "SELECT ROW_NUMBER() OVER (ORDER BY A.LHCol1), A.LHCol1, A.LHCol2, B.RHCol1, B.RHCol2 \n" + "FROM \n" + "MyVDB.lhTable AS A \n" + "INNER JOIN \n" + "MyVDB.rhTable AS B ;"; String lhTableAlias = "A"; String rhTableAlias = "B"; Table lhTable = createTable("MyVDB", VDB_PATH, "MyModel", "lhTable"); Column lhCol1 = lhTable.addColumn(getTransaction(), "LHCol1"); lhCol1.setDatatypeName(getTransaction(), "string"); Column lhCol2 = lhTable.addColumn(getTransaction(), "LHCol2"); lhCol2.setDatatypeName(getTransaction(), "string"); Table rhTable = createTable("MyVDB", VDB_PATH, "MyModel", "rhTable"); Column rhCol1 = rhTable.addColumn(getTransaction(), "RHCol1"); rhCol1.setDatatypeName(getTransaction(), "string"); Column rhCol2 = rhTable.addColumn(getTransaction(), "RHCol2"); rhCol2.setDatatypeName(getTransaction(), "string"); List<String> lhColNames = new ArrayList<String>(); lhColNames.add("LHCol1"); lhColNames.add("LHCol2"); List<String> rhColNames = new ArrayList<String>(); rhColNames.add("RHCol1"); rhColNames.add("RHCol2"); String viewDdl = ViewDdlBuilder.getODataViewJoinDdl(getTransaction(), "MyView", lhTable, lhTableAlias, lhColNames, rhTable, rhTableAlias, rhColNames, ViewDdlBuilder.JOIN_INNER, null); assertThat(viewDdl, is(EXPECTED_DDL)); }
@Test public void shouldGeneratedODataViewInnerJoinMulipleCriteriaDDL() throws Exception { String EXPECTED_DDL = "CREATE VIEW MyView (RowId integer PRIMARY KEY, LHCol1 string, LHCol2 string, RHCol1 string, RHCol2 string) AS \n" + "SELECT ROW_NUMBER() OVER (ORDER BY A.LHCol1), A.LHCol1, A.LHCol2, B.RHCol1, B.RHCol2 \n" + "FROM \n" + "MyVDB.lhTable AS A \n" + "INNER JOIN \n" + "MyVDB.rhTable AS B \n" + "ON \n" + "A.LHCol1 = B.RHCol2 AND A.LHCol3 > B.RHCol4;"; String lhTableAlias = "A"; String rhTableAlias = "B"; Table lhTable = createTable("MyVDB", VDB_PATH, "MyModel", "lhTable"); Column lhCol1 = lhTable.addColumn(getTransaction(), "LHCol1"); lhCol1.setDatatypeName(getTransaction(), "string"); Column lhCol2 = lhTable.addColumn(getTransaction(), "LHCol2"); lhCol2.setDatatypeName(getTransaction(), "string"); Table rhTable = createTable("MyVDB", VDB_PATH, "MyModel", "rhTable"); Column rhCol1 = rhTable.addColumn(getTransaction(), "RHCol1"); rhCol1.setDatatypeName(getTransaction(), "string"); Column rhCol2 = rhTable.addColumn(getTransaction(), "RHCol2"); rhCol2.setDatatypeName(getTransaction(), "string"); List<String> lhColNames = new ArrayList<String>(); lhColNames.add("LHCol1"); lhColNames.add("LHCol2"); List<String> rhColNames = new ArrayList<String>(); rhColNames.add("RHCol1"); rhColNames.add("RHCol2"); List<ViewBuilderCriteriaPredicate> criteriaPredicates = new ArrayList<ViewBuilderCriteriaPredicate>(); ViewBuilderCriteriaPredicate predicate1 = new ViewBuilderCriteriaPredicate(); predicate1.setLhColumn("LHCol1"); predicate1.setRhColumn("RHCol2"); predicate1.setOperator("="); predicate1.setCombineKeyword("AND"); criteriaPredicates.add(predicate1); ViewBuilderCriteriaPredicate predicate2 = new ViewBuilderCriteriaPredicate(); predicate2.setLhColumn("LHCol3"); predicate2.setRhColumn("RHCol4"); predicate2.setOperator(">"); criteriaPredicates.add(predicate2); String viewDdl = ViewDdlBuilder.getODataViewJoinDdl(getTransaction(), "MyView", lhTable, lhTableAlias, lhColNames, rhTable, rhTableAlias, rhColNames, ViewDdlBuilder.JOIN_INNER, criteriaPredicates); assertThat(viewDdl, is(EXPECTED_DDL)); }