Examples with JoinElement org.talend.dataquality.rules.JoinElement used on opensource projects

Example 6 with JoinElement

use of org.talend.dataquality.rules.JoinElement in project tdq-studio-se by Talend.

the class DbmsLanguage method createJoinConditionAsString.

/**
 * create join condiction string.
 *
 * @param leftTable
 * @param joinElements
 * @param catalogName
 * @param schemaName
 * @param joinType (null: JOIN) (left: LEFT JOIN) (right: RIGHT JOIN) (full: FULL JOIN)
 * @return
 */
public String createJoinConditionAsString(ModelElement leftTable, List<JoinElement> joinElements, String catalogName, String schemaName, String joinType) {
    if (joinElements.isEmpty()) {
        return PluginConstant.EMPTY_STRING;
    }
    // else
    String tempSchemaName = schemaName;
    StringBuilder builder = new StringBuilder();
    for (JoinElement joinElement : joinElements) {
        ModelElement colA = joinElement.getColA();
        String tableA = getTableName(colA);
        String tableAliasA = joinElement.getTableAliasA();
        String columnAName = getColumnName(colA);
        boolean hasTableAliasA = !StringUtils.isEmpty(tableAliasA);
        ModelElement colB = joinElement.getColB();
        String tableB = getTableName(colB);
        String tableAliasB = joinElement.getTableAliasB();
        String columnBName = getColumnName(colB);
        boolean hasTableAliasB = !StringUtils.isEmpty(tableAliasB);
        String operator = joinElement.getOperator();
        // MOD by klliu bug 20926 #c82152
        if (joinClauseStartsWithWrongTable(leftTable, getTable(colB)) && hasTableAliasA && hasTableAliasB) {
            // we need to exchange the table names otherwise we could get "tableA join tableA" which would cause
            // an SQL exception.
            // ~MOD mzhao 2010-2-24 bug 11753. Add prefix catalog or schema in case of join tables.
            tableA = toQualifiedName(catalogName, tempSchemaName, tableA);
            // ~
            buildJoinClause(builder, tableB, tableAliasB, columnBName, hasTableAliasB, tableA, tableAliasA, columnAName, hasTableAliasA, operator, joinType);
        } else {
            // ~MOD mzhao 2010-2-24 bug 11753. Add prefix catalog or schema in case of join tables.
            tableB = toQualifiedName(catalogName, tempSchemaName, tableB);
            // ~
            buildJoinClause(builder, tableA, tableAliasA, columnAName, hasTableAliasA, tableB, tableAliasB, columnBName, hasTableAliasB, operator, joinType);
        }
    }
    return builder.toString();
}

Example 7 with JoinElement

use of org.talend.dataquality.rules.JoinElement in project tdq-studio-se by Talend.

the class AnalysisExecutorHelper method copyWhereRule.

/**
 * When deep copy, if the where rule contains some joins, it will copy all related tables(extended in related db),
 * but this is not what we want, so we use a temp list to store the joins, and then clear the joins before deep copy
 * to avoid copy many useless things, and restore the joins after deep copy.
 *
 * @param dependentDefinition
 */
private static IndicatorDefinition copyWhereRule(WhereRule dependentDefinition) {
    // firstly clear the dependency of the analysis
    dependentDefinition.getSupplierDependency().clear();
    // then , record the joins in a temp list
    EList<JoinElement> joins = dependentDefinition.getJoins();
    List<JoinElement> copyJoins = new ArrayList<JoinElement>();
    if (!joins.isEmpty()) {
        for (JoinElement element : joins) {
            copyJoins.add(element);
        }
        dependentDefinition.getJoins().clear();
    }
    IndicatorDefinition deepCopiedDefinition = EObjectHelper.deepCopy(dependentDefinition);
    // after deep copy, restore the joins.
    if (!copyJoins.isEmpty()) {
        ((WhereRule) deepCopiedDefinition).getJoins().addAll(copyJoins);
        dependentDefinition.getJoins().addAll(copyJoins);
    }
    return deepCopiedDefinition;
}

Example 8 with JoinElement

use of org.talend.dataquality.rules.JoinElement in project tdq-studio-se by Talend.

the class TableAnalysisSqlExecutorTest method testCreateSqlStatementCase2.

/**
 * Test method for
 * {@link org.talend.dq.analysis.TableAnalysisSqlExecutor#createSqlStatement(org.talend.dataquality.analysis.Analysis)}
 * . case 1:the where rule have join conditions
 */
@Test
public void testCreateSqlStatementCase2() {
    // $NON-NLS-1$
    String sql = "select count(*) from table where 1=1";
    TdExpression expression = RelationalFactory.eINSTANCE.createTdExpression();
    expression.setBody(sql);
    // $NON-NLS-1$
    expression.setLanguage("SQL");
    testWhereRuleIndicatorDefinition.getSqlGenericExpression().add(expression);
    JoinElement createJoinElement = RulesFactory.eINSTANCE.createJoinElement();
    // $NON-NLS-1$
    createJoinElement.setColumnAliasA("colA");
    // $NON-NLS-1$
    createJoinElement.setColumnAliasB("colB");
    // $NON-NLS-1$
    createJoinElement.setTableAliasA("tabA");
    // $NON-NLS-1$
    createJoinElement.setTableAliasB("tabB");
    TdColumn createTdColumnA = RelationalFactory.eINSTANCE.createTdColumn();
    // $NON-NLS-1$
    createTdColumnA.setName("colA");
    TdTable createTdTableA = RelationalFactory.eINSTANCE.createTdTable();
    // $NON-NLS-1$
    createTdTableA.setName("tableA");
    createTdColumnA.setOwner(createTdTableA);
    TdColumn createTdColumnB = RelationalFactory.eINSTANCE.createTdColumn();
    // $NON-NLS-1$
    createTdColumnB.setName("colB");
    TdTable createTdTableB = RelationalFactory.eINSTANCE.createTdTable();
    // $NON-NLS-1$
    createTdTableB.setName("tableB");
    createTdColumnB.setOwner(createTdTableB);
    createJoinElement.setColA(createTdColumnA);
    createJoinElement.setColB(createTdColumnB);
    testWhereRuleIndicatorDefinition.getJoins().add(createJoinElement);
    TableAnalysisSqlExecutor tableAnalysisSqlExecutor = new TableAnalysisSqlExecutor();
    String actualSqlStatement = tableAnalysisSqlExecutor.createSqlStatement(testAnalysis);
    Assert.assertEquals(StringUtils.EMPTY, actualSqlStatement);
    EList<Expression> instantiatedExpressions = testWhereRuleIndicator.getInstantiatedExpressions();
    Assert.assertNotNull(instantiatedExpressions);
    Assert.assertEquals(1, instantiatedExpressions.size());
    Assert.assertEquals(sql, instantiatedExpressions.get(0).getBody());
}

Example 9 with JoinElement

use of org.talend.dataquality.rules.JoinElement in project tdq-studio-se by Talend.

the class TableAnalysisSqlExecutor method createSqlQuery.

private boolean createSqlQuery(String dataFilterAsString, Indicator indicator, boolean withWhereOfRule) throws AnalysisExecutionException {
    if (!isAnalyzedElementValid(indicator)) {
        return Boolean.FALSE;
    }
    IndicatorDefinition indicatorDefinition = indicator.getIndicatorDefinition();
    if (!isIndicatorDefinitionValid(indicatorDefinition, AnalysisExecutorHelper.getIndicatorName(indicator))) {
        return Boolean.FALSE;
    }
    Expression sqlGenericExpression = dbms().getSqlExpression(indicatorDefinition);
    if (!isExpressionValid(sqlGenericExpression, indicator)) {
        return Boolean.FALSE;
    }
    // --- get indicator parameters and convert them into sql expression
    List<String> whereExpressionDQRule = new ArrayList<String>();
    final EList<JoinElement> joinConditions = indicator.getJoinConditions();
    if (RulesPackage.eINSTANCE.getWhereRule().equals(indicatorDefinition.eClass())) {
        WhereRule wr = (WhereRule) indicatorDefinition;
        if (withWhereOfRule) {
            whereExpressionDQRule.add(wr.getWhereExpression());
        }
        // MOD scorreia 2009-03-13 copy joins conditions into the indicator
        joinConditions.clear();
        if (!isJoinConditionEmpty(indicator)) {
            for (JoinElement joinelt : wr.getJoins()) {
                JoinElement joinCopy = EcoreUtil.copy(joinelt);
                joinConditions.add(joinCopy);
            }
        }
    }
    NamedColumnSet set = SwitchHelpers.NAMED_COLUMN_SET_SWITCH.doSwitch(indicator.getAnalyzedElement());
    String schemaName = getQuotedSchemaName(set);
    // --- normalize table name
    String catalogName = getQuotedCatalogName(set);
    if (catalogName == null && schemaName != null) {
        // try to get catalog above schema
        final Schema parentSchema = SchemaHelper.getParentSchema(set);
        final Catalog parentCatalog = CatalogHelper.getParentCatalog(parentSchema);
        catalogName = parentCatalog != null ? parentCatalog.getName() : null;
    }
    // --- default case
    // allow join
    String joinclause = (!joinConditions.isEmpty()) ? dbms().createJoinConditionAsString(set, joinConditions, catalogName, schemaName) : PluginConstant.EMPTY_STRING;
    String setName = dbms().toQualifiedName(catalogName, schemaName, quote(set.getName()));
    String completedSqlString = dbms().fillGenericQueryWithJoin(sqlGenericExpression.getBody(), setName, joinclause);
    // ~
    List<String> whereExpressionAnalysis = new ArrayList<String>();
    if (StringUtils.isNotBlank(dataFilterAsString)) {
        whereExpressionAnalysis.add(dataFilterAsString);
    }
    completedSqlString = addWhereToSqlStringStatement(whereExpressionAnalysis, whereExpressionDQRule, completedSqlString, true);
    // completedSqlString is the final query
    String finalQuery = completedSqlString;
    TdExpression instantiateSqlExpression = BooleanExpressionHelper.createTdExpression(dbms().getDbmsName(), finalQuery);
    indicator.setInstantiatedExpression(instantiateSqlExpression);
    return true;
}

Example 10 with JoinElement

use of org.talend.dataquality.rules.JoinElement in project tdq-studio-se by Talend.

the class JoinConditionTableViewer method dropModelElements.

@Override
public void dropModelElements(List<? extends IRepositoryNode> modelElements, int index) {
    List<TdColumn> columns = new ArrayList<TdColumn>();
    for (IRepositoryNode repNode : modelElements) {
        if (repNode.getObject() instanceof MetadataColumnRepositoryObject) {
            TdColumn column = (TdColumn) ((MetadataColumnRepositoryObject) repNode.getObject()).getTdColumn();
            if (column != null) {
                columns.add(column);
            }
        }
    }
    JoinElementColumnDialog joinElementColumnDialog = new JoinElementColumnDialog(null);
    if (joinElementColumnDialog.open() == Window.OK) {
        JoinElement join = (JoinElement) this.myTableViewer.getElementAt(index);
        if (join == null) {
            join = this.addJoinElement();
        }
        if (join != null) {
            boolean dirty = false;
            for (TdColumn column : columns) {
                if (column != null) {
                    if (!updateColumnSetPackage(column)) {
                        break;
                    }
                    if (COLUMN_A.equals(joinElementColumnDialog.getAb())) {
                        join.setColA(column);
                        join.setColumnAliasA(column.getName());
                        join.setTableAliasA(ColumnHelper.getColumnSetFullName(column));
                        dirty = true;
                    } else {
                        join.setColB(column);
                        join.setColumnAliasB(column.getName());
                        join.setTableAliasB(ColumnHelper.getColumnSetFullName(column));
                        dirty = true;
                    }
                }
            }
            if (dirty) {
                this.masterPage.setDirty(true);
                this.myTableViewer.update(join, null);
            }
        }
    }
}