Examples with TdColumn org.talend.cwm.relational.TdColumn used on opensource projects

Example 56 with TdColumn

use of org.talend.cwm.relational.TdColumn in project tdq-studio-se by Talend.

the class SoundexFrequencyExplorer method getFreqRowsStatement.

@Override
protected String getFreqRowsStatement() {
    TdColumn column = (TdColumn) indicator.getAnalyzedElement();
    // MOD zshen 11005: SQL syntax error for all analysis on Informix databases in Talend Open Profiler
    String resultSql = null;
    // MOD klliu 0013242: set soundex indicator for null field,drill down will get NPE
    if (entity.getKey() != null) {
        resultSql = dbmsLanguage.getFreqRowsStatement(this.columnName, getFullyQualifiedTableName(column), entity.getKey().toString());
        if (resultSql != null) {
            return resultSql;
        }
    }
    String clause = getInstantiatedClause();
    return // $NON-NLS-1$
    "SELECT * FROM " + getFullyQualifiedTableName(column) + dbmsLanguage.where() + inBrackets(clause) + andDataFilterClause();
}

Example 57 with TdColumn

use of org.talend.cwm.relational.TdColumn in project tdq-studio-se by Talend.

the class DbmsLanguage method getQueryColumnsWithPrefix.

/**
 * Get query string with prefix (catalog/schema.table.column) given column array.
 *
 * @param columns
 * @return
 */
public String getQueryColumnsWithPrefix(TdColumn[] columns) {
    String columnClause = PluginConstant.EMPTY_STRING;
    if (columns.length == 0) {
        return columnClause;
    }
    ColumnSet columnSet = ColumnHelper.getColumnOwnerAsColumnSet(columns[0]);
    String tableName = getQueryColumnSetWithPrefix(columnSet);
    for (TdColumn column : columns) {
        columnClause += tableName + DOT + quote(column.getName()) + getSeparatedCharacter();
    }
    columnClause = columnClause.substring(0, columnClause.length() - 1);
    return columnClause;
}

Example 58 with TdColumn

use of org.talend.cwm.relational.TdColumn in project tdq-studio-se by Talend.

the class AnalysisExecutorHelper method getTableName.

/**
 * get full name as: db.catalog.table, if has catalog/schema
 *
 * @param analyzedElement only for TdColumn and ColumnSet.
 * @param dbmsLanguage
 * @return
 * @deprecated instead of it by {@link DbmsLanguage#getQueryColumnSetWithPrefix(ColumnSet) or
 * DbmsLanguage#getQueryColumnSetWithPrefix(TdColumn)}
 */
@Deprecated
public static String getTableName(ModelElement analyzedElement, DbmsLanguage dbmsLanguage) {
    TdColumn tdColumn = SwitchHelpers.COLUMN_SWITCH.doSwitch(analyzedElement);
    if (tdColumn != null) {
        return dbmsLanguage.getQueryColumnSetWithPrefix(tdColumn);
    }
    ColumnSet columnSet = SwitchHelpers.COLUMN_SET_SWITCH.doSwitch(analyzedElement);
    if (columnSet != null) {
        return dbmsLanguage.getQueryColumnSetWithPrefix(columnSet);
    }
    // $NON-NLS-1$
    log.error(Messages.getString("AnalysisExecutorHelper.TableEmpty"));
    return PluginConstant.EMPTY_STRING;
}

Example 59 with TdColumn

use of org.talend.cwm.relational.TdColumn in project tdq-studio-se by Talend.

the class ModelElementIndicatorRule method patternRule.

public static boolean patternRule(IndicatorEnum indicatorType, ModelElement me, ExecutionLanguage language, IIndicatorNode node) {
    int javaType = 0;
    boolean isDeliFileColumn = !(me instanceof TdColumn) && me instanceof MetadataColumn;
    int isTeradataInterval = -1;
    if (me instanceof TdColumn) {
        javaType = ((TdColumn) me).getSqlDataType().getJavaDataType();
        // Added yyin 20121211 TDQ-6099:
        isTeradataInterval = Java2SqlType.isTeradataIntervalType(((TdColumn) me).getSqlDataType().getName());
    // ~
    } else if (isDeliFileColumn) {
        javaType = TalendTypeConvert.convertToJDBCType(((MetadataColumn) me).getTalendType());
    }
    DataminingType dataminingType = MetadataHelper.getDataminingType(me);
    if (dataminingType == null || isDeliFileColumn) {
        dataminingType = MetadataHelper.getDefaultDataminingType(javaType);
    }
    // MOD qiongli 2012-4-25 TDQ-2699
    Connection connection = null;
    if (me instanceof TdColumn) {
        connection = ConnectionHelper.getTdDataProvider((TdColumn) me);
    } else if (me instanceof MetadataColumn) {
        connection = ConnectionHelper.getTdDataProvider((MetadataColumn) me);
    }
    Indicator indicator = null;
    if (node != null) {
        indicator = node.getIndicatorInstance();
    }
    boolean isSQLEngine = ExecutionLanguage.SQL.equals(language);
    boolean isJavaEngine = ExecutionLanguage.JAVA.equals(language);
    DbmsLanguage dbmsLanguage = DbmsLanguageFactory.createDbmsLanguage(connection, language);
    if (javaType == Types.LONGVARCHAR && isSQLEngine) {
        if (connection != null && ConnectionHelper.isDb2(connection)) {
            return enableLongVarchar(indicatorType, dataminingType, me);
        }
    }
    // MOD qiongli 2012-8-10 TDQ-5907 need to disabled indicators for hive with sql engine.
    boolean isHiveSQL = connection == null ? false : ConnectionHelper.isHive(connection) && isSQLEngine;
    // MOD msjian 2013-5-15 TDQ-7275 need to disabled indicators for teradata with sql engine.
    boolean isTeradataSQL = connection == null ? false : ConnectionHelper.isTeradata(connection) && isSQLEngine;
    // MOD msjian 2016-8-25 TDQ-12349 need to disabled indicators for ingres with sql engine.
    boolean isIngres = connection == null ? false : ConnectionHelper.isIngress(connection) && isSQLEngine;
    // MOD msjian 2016-8-25 TDQ-12464 need to disabled indicators for ingres with sql engine.
    boolean isSybase = connection == null ? false : ConnectionHelper.isSybase(connection) && isSQLEngine;
    // MOD qiongli 2013-8-27 TDQ-2104 disabled soundex indicators for hive with sql engine.
    boolean isVerticaSQL = connection == null ? false : ConnectionHelper.isVertica(connection) && isSQLEngine;
    switch(indicatorType) {
        case CountsIndicatorEnum:
        case RowCountIndicatorEnum:
        case NullCountIndicatorEnum:
        case DistinctCountIndicatorEnum:
        case UniqueIndicatorEnum:
        case DuplicateCountIndicatorEnum:
            // if (dataminingType == DataminingType.NOMINAL) {
            return true;
        case DefValueCountIndicatorEnum:
            Expression initialValue = null;
            if (me instanceof TdColumn) {
                initialValue = ((TdColumn) me).getInitialValue();
            }
            if (initialValue != null && initialValue.getBody() != null) {
                // non nullable numeric column give a non null default value as ''
                return initialValue.getBody().length() != 0 || Java2SqlType.isTextInSQL(javaType);
            }
            break;
        case BlankCountIndicatorEnum:
            // MOD xwang 2011-07-29 bug TDQ-1731 disable blank count checkable for other data type but Text
            if (!Java2SqlType.isTextInSQL(javaType)) {
                return false;
            } else if (isTeradataInterval == Java2SqlType.TERADATA_INTERVAL_TO && isSQLEngine) {
                // Added yyin 20121212 TDQ-6099: disable for Teradata's interval_xx_to_xx type.
                return false;
            } else {
                return true;
            }
        case TextIndicatorEnum:
        case MinLengthIndicatorEnum:
        case MinLengthWithNullIndicatorEnum:
        case MinLengthWithBlankIndicatorEnum:
        case MinLengthWithBlankNullIndicatorEnum:
        case MaxLengthIndicatorEnum:
        case MaxLengthWithNullIndicatorEnum:
        case MaxLengthWithBlankIndicatorEnum:
        case MaxLengthWithBlankNullIndicatorEnum:
        case AverageLengthIndicatorEnum:
        case AverageLengthWithNullIndicatorEnum:
        case AverageLengthWithBlankIndicatorEnum:
        case AverageLengthWithNullBlankIndicatorEnum:
            if (Java2SqlType.isTextInSQL(javaType)) {
                // Added yyin 20121212 TDQ-6099: disable for Teradata's interval_xx_to_xx type.
                if (isTeradataInterval > 0) {
                    return false;
                }
                if (dataminingType == DataminingType.NOMINAL || dataminingType == DataminingType.UNSTRUCTURED_TEXT) {
                    return true;
                }
            }
            break;
        case EastAsiaPatternFreqIndicatorEnum:
        case EastAsiaPatternLowFreqIndicatorEnum:
            if (isSQLEngine && isEmpryExpression(indicator, dbmsLanguage)) {
                return false;
            } else if (isJavaEngine) {
                return true;
            }
        case BenfordLawFrequencyIndicatorEnum:
            // disable the benford for interval type: both sql and java
            if (isTeradataInterval > 0) {
                return false;
            }
        case PatternFreqIndicatorEnum:
        case PatternLowFreqIndicatorEnum:
            if (isTeradataSQL || isIngres || isSybase) {
                return false;
            }
        case ModeIndicatorEnum:
            // Added yyin 20121212 TDQ-6099: disable for Teradata's interval_xx_to_xx type.
            if (isTeradataInterval == Java2SqlType.TERADATA_INTERVAL_TO && isSQLEngine) {
                return false;
            }
        case FrequencyIndicatorEnum:
        case LowFrequencyIndicatorEnum:
            if (dataminingType == DataminingType.NOMINAL || dataminingType == DataminingType.INTERVAL) {
                return true;
            }
            break;
        // MOD zshen 2010-01-27 Date Pattern frequency indicator
        case DatePatternFreqIndicatorEnum:
            if (isJavaEngine && (Java2SqlType.isDateInSQL(javaType) || Java2SqlType.isTextInSQL(javaType))) {
                return true;
            }
            break;
        // MOD mzhao 2009-03-05 Soundex frequency indicator
        case SoundexIndicatorEnum:
        case SoundexLowIndicatorEnum:
            if (!Java2SqlType.isDateInSQL(javaType) && !Java2SqlType.isNumbericInSQL(javaType) && (dataminingType == DataminingType.NOMINAL || dataminingType == DataminingType.INTERVAL)) {
                if (isHiveSQL || isVerticaSQL) {
                    return false;
                }
                // Added yyin 20121212 TDQ-6099: disable for Teradata's interval_xx_to_xx type.
                if (isTeradataInterval > 0 && isSQLEngine) {
                    return false;
                }
                return true;
            }
            break;
        case MeanIndicatorEnum:
        case MedianIndicatorEnum:
        case IQRIndicatorEnum:
        case LowerQuartileIndicatorEnum:
        case UpperQuartileIndicatorEnum:
            // graphics and database yet.
            if (Java2SqlType.isNumbericInSQL(javaType)) /* || Java2SqlType.isDateInSQL(javaType) */
            {
                if (dataminingType == DataminingType.INTERVAL) {
                    if (isHiveSQL && !(indicatorType == IndicatorEnum.MeanIndicatorEnum)) {
                        return false;
                    }
                    return true;
                }
            }
            break;
        case BoxIIndicatorEnum:
        case RangeIndicatorEnum:
        case MinValueIndicatorEnum:
        case MaxValueIndicatorEnum:
            // the graphics and database yet.
            if (Java2SqlType.isNumbericInSQL(javaType) || Java2SqlType.isDateInSQL(javaType)) {
                if (dataminingType == DataminingType.INTERVAL) {
                    return true;
                }
            }
            break;
        case DateFrequencyIndicatorEnum:
        case WeekFrequencyIndicatorEnum:
        case MonthFrequencyIndicatorEnum:
        case QuarterFrequencyIndicatorEnum:
        case YearFrequencyIndicatorEnum:
        case DateLowFrequencyIndicatorEnum:
        case WeekLowFrequencyIndicatorEnum:
        case MonthLowFrequencyIndicatorEnum:
        case QuarterLowFrequencyIndicatorEnum:
        case YearLowFrequencyIndicatorEnum:
            // Mod yyin 20120511 TDQ-5241
            if (Java2SqlType.isDateInSQL(javaType) && !Java2SqlType.isTimeSQL(javaType) && (dataminingType == DataminingType.NOMINAL || dataminingType == DataminingType.INTERVAL)) {
                return true;
            }
            break;
        case BinFrequencyIndicatorEnum:
        case BinLowFrequencyIndicatorEnum:
            if (Java2SqlType.isNumbericInSQL(javaType) && (dataminingType == DataminingType.NOMINAL || dataminingType == DataminingType.INTERVAL)) {
                return true;
            }
            break;
        case ValidPhoneCountIndicatorEnum:
        case PossiblePhoneCountIndicatorEnum:
        case ValidRegCodeCountIndicatorEnum:
        case InvalidRegCodeCountIndicatorEnum:
        case WellFormE164PhoneCountIndicatorEnum:
        case WellFormIntePhoneCountIndicatorEnum:
        case WellFormNationalPhoneCountIndicatorEnum:
        case PhoneNumbStatisticsIndicatorEnum:
        case FormatFreqPieIndictorEnum:
            if (isJavaEngine && (dataminingType == DataminingType.NOMINAL || dataminingType == DataminingType.INTERVAL)) {
                return true;
            }
            break;
        case SqlPatternMatchingIndicatorEnum:
            if (node == null) {
                return false;
            }
            if (!isSQLEngine) {
                return false;
            }
            Pattern pattern = IndicatorHelper.getPattern(indicator);
            Expression returnExpression = dbmsLanguage.getRegexp(pattern);
            if (returnExpression != null) {
                return true;
            }
            break;
        case RegexpMatchingIndicatorEnum:
            if (node == null) {
                return false;
            }
            pattern = IndicatorHelper.getPattern(indicator);
            returnExpression = dbmsLanguage.getRegexp(pattern);
            if (returnExpression != null) {
                return true;
            }
            break;
        case UserDefinedIndicatorEnum:
            // judge language
            if (node == null) {
                return false;
            }
            Indicator judi = null;
            try {
                judi = UDIHelper.adaptToJavaUDI(indicator);
            } catch (Throwable e) {
                return false;
            }
            if (judi != null) {
                indicator = judi;
            }
            returnExpression = dbmsLanguage.getExpression(indicator);
            if (isJavaEngine && judi == null || isSQLEngine && returnExpression == null) {
                return false;
            }
            return true;
        default:
            return false;
    }
    return false;
}

Example 60 with TdColumn

use of org.talend.cwm.relational.TdColumn in project tdq-studio-se by Talend.

the class ColumnAnalysisExecutor method createSqlStatement.

/*
     * (non-Javadoc)
     * 
     * @see org.talend.dq.analysis.AnalysisExecutor#createSqlStatement(org.talend.dataquality.analysis.Analysis)
     */
@Override
public String createSqlStatement(Analysis analysis) {
    this.cachedAnalysis = analysis;
    // CwmZQuery query = new CwmZQuery();
    // $NON-NLS-1$
    StringBuilder sql = new StringBuilder("SELECT ");
    EList<ModelElement> analysedElements = analysis.getContext().getAnalysedElements();
    // so if you modify the code here, please also modify the same part.
    if (analysedElements.isEmpty()) {
        setError(// $NON-NLS-1$
        Messages.getString(// $NON-NLS-1$
        "ColumnAnalysisExecutor.CannotCreateSQLStatement", analysis.getName()));
        return null;
    }
    Set<ColumnSet> fromPart = new HashSet<ColumnSet>();
    final Iterator<ModelElement> iterator = analysedElements.iterator();
    while (iterator.hasNext()) {
        // for (ModelElement modelElement : analysedElements) {
        ModelElement modelElement = iterator.next();
        // --- preconditions
        TdColumn col = SwitchHelpers.COLUMN_SWITCH.doSwitch(modelElement);
        if (col == null) {
            // $NON-NLS-1$
            setError(Messages.getString("ColumnAnalysisExecutor.GivenElementIsNotColumn", modelElement));
            return null;
        }
        Classifier owner = col.getOwner();
        if (owner == null) {
            // $NON-NLS-1$
            setError(Messages.getString("ColumnAnalysisExecutor.NoOwnerFound", col.getName()));
        }
        ColumnSet colSet = SwitchHelpers.COLUMN_SET_SWITCH.doSwitch(owner);
        if (colSet == null) {
            // $NON-NLS-1$
            setError(Messages.getString("ColumnAnalysisExecutor.NoContainerFound", col.getName()));
            return null;
        }
        // MOD zshen feature 12919 select all the column to be prepare for drill down when user need.
        if (!analysis.getParameters().isStoreData()) {
            sql.append(this.quote(col.getName()));
            // append comma if more columns exist
            if (iterator.hasNext()) {
                sql.append(',');
            }
        }
        // add from
        fromPart.add(colSet);
    }
    if (fromPart.size() != 1) {
        // $NON-NLS-1$
        log.error(Messages.getString("ColumnAnalysisExecutor.ANALYSISMUSTRUNONONETABLE") + fromPart.size() + PluginConstant.DOT_STRING);
        // $NON-NLS-1$
        setError(Messages.getString("ColumnAnalysisExecutor.ANALYSISMUSTRUNONONETABLEERRORMESSAGE"));
        return null;
    }
    TdColumn firstColumn = SwitchHelpers.COLUMN_SWITCH.doSwitch(analysedElements.get(0));
    // MOD zshen feature 12919 select all the column to be prepare for drill down.
    if (analysis.getParameters().isStoreData()) {
        // MOD klliu 2011-06-30 bug 22523 whichever is Table or View,that finds columns should ues columnset
        EObject eContainer = firstColumn.eContainer();
        List<TdColumn> columnList = ColumnSetHelper.getColumns(SwitchHelpers.COLUMN_SET_SWITCH.doSwitch(eContainer));
        // ~
        Iterator<TdColumn> iter = columnList.iterator();
        while (iter.hasNext()) {
            TdColumn column = iter.next();
            sql.append(this.quote(column.getName()));
            // append comma if more columns exist
            if (iter.hasNext()) {
                sql.append(',');
            }
        }
    }
    // add from clause
    sql.append(dbms().from());
    sql.append(dbms().getQueryColumnSetWithPrefix(firstColumn));
    // add where clause
    // --- get data filter
    ModelElementAnalysisHandler handler = new ModelElementAnalysisHandler();
    handler.setAnalysis(analysis);
    String stringDataFilter = handler.getStringDataFilter();
    if (!(stringDataFilter == null || "".equals(stringDataFilter))) {
        // $NON-NLS-1$
        sql.append(GenericSQLHandler.WHERE_CLAUSE);
    }
    String sqlStatement = sql.toString();
    sqlStatement = dbms().addWhereToStatement(sqlStatement, stringDataFilter);
    return sqlStatement;
}