Java tutorial
// ============================================================================ // // Copyright (C) 2006-2017 Talend Inc. - www.talend.com // // This source code is available under agreement available at // %InstallDIR%\features\org.talend.rcp.branding.%PRODUCTNAME%\%PRODUCTNAME%license.txt // // You should have received a copy of the agreement // along with this program; if not, write to Talend SA // 9 rue Pages 92150 Suresnes, France // // ============================================================================ package org.talend.dq.dbms; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.Arrays; import java.util.Collections; import java.util.List; import org.apache.commons.lang.StringUtils; import org.apache.log4j.Logger; import org.eclipse.emf.common.util.EList; import org.talend.core.IRepositoryContextService; import org.talend.core.database.EDatabaseTypeName; import org.talend.core.model.metadata.builder.connection.DatabaseConnection; import org.talend.core.model.metadata.builder.connection.MetadataColumn; import org.talend.core.model.metadata.builder.connection.MetadataTable; import org.talend.core.model.metadata.builder.database.dburl.SupportDBUrlType; import org.talend.core.runtime.CoreRuntimePlugin; import org.talend.cwm.db.connection.ConnectionUtils; import org.talend.cwm.helper.CatalogHelper; import org.talend.cwm.helper.ColumnHelper; import org.talend.cwm.helper.ColumnSetHelper; import org.talend.cwm.helper.ConnectionHelper; import org.talend.cwm.helper.ResourceHelper; import org.talend.cwm.helper.SchemaHelper; import org.talend.cwm.helper.SwitchHelpers; import org.talend.cwm.relational.TdColumn; import org.talend.cwm.relational.TdExpression; import org.talend.dataquality.PluginConstant; import org.talend.dataquality.analysis.ExecutionLanguage; import org.talend.dataquality.domain.Domain; import org.talend.dataquality.domain.pattern.Pattern; import org.talend.dataquality.domain.pattern.PatternComponent; import org.talend.dataquality.domain.pattern.PatternPackage; import org.talend.dataquality.domain.pattern.RegularExpression; import org.talend.dataquality.domain.sql.SqlPredicate; import org.talend.dataquality.helpers.IndicatorCategoryHelper; import org.talend.dataquality.indicators.DataminingType; import org.talend.dataquality.indicators.DateGrain; import org.talend.dataquality.indicators.Indicator; import org.talend.dataquality.indicators.IndicatorParameters; import org.talend.dataquality.indicators.PatternMatchingIndicator; import org.talend.dataquality.indicators.definition.CharactersMapping; import org.talend.dataquality.indicators.definition.IndicatorDefinition; import org.talend.dataquality.indicators.sql.UserDefIndicator; import org.talend.dataquality.rules.JoinElement; import org.talend.metadata.managment.ui.i18n.Messages; import org.talend.utils.ProductVersion; import org.talend.utils.sql.Java2SqlType; import orgomg.cwm.objectmodel.core.CoreFactory; import orgomg.cwm.objectmodel.core.Expression; import orgomg.cwm.objectmodel.core.ModelElement; import orgomg.cwm.objectmodel.core.Package; import orgomg.cwm.resource.relational.Catalog; import orgomg.cwm.resource.relational.ColumnSet; import orgomg.cwm.resource.relational.Schema; /** * @author scorreia * * This class handle DBMS specific SQL terms and functions. It provides methods to handle SQL clause and evrything * related to a specific DBMS. * * this class provides a default implementation. Subclasses exist for each DBMS so that specific implementation can be * written. */ public class DbmsLanguage { private static Logger log = Logger.getLogger(DbmsLanguage.class); // TODO scorreia put this into its own class and offer simple methods to replace tokens. // --- add here other supported systems (always in uppercase) // DBMS_SUPPORT static final String ORACLE = SupportDBUrlType.ORACLEWITHSIDDEFAULTURL.getLanguage(); static final String MYSQL = SupportDBUrlType.MYSQLDEFAULTURL.getLanguage(); static final String POSTGRESQL = SupportDBUrlType.POSTGRESQLEFAULTURL.getLanguage(); static final String MSSQL = SupportDBUrlType.MSSQLDEFAULTURL.getLanguage(); static final String DB2 = SupportDBUrlType.DB2DEFAULTURL.getLanguage(); static final String AS400 = SupportDBUrlType.AS400DEFAULTURL.getLanguage(); static final String DB2ZOS = SupportDBUrlType.DB2ZOSDEFAULTURL.getLanguage(); static final String SYBASE = SupportDBUrlType.SYBASEDEFAULTURL.getLanguage(); static final String SQLITE3 = SupportDBUrlType.SQLITE3DEFAULTURL.getLanguage(); static final String TERADATA = SupportDBUrlType.TERADATADEFAULTURL.getLanguage(); static final String INGRES = SupportDBUrlType.INGRESDEFAULTURL.getLanguage(); static final String JAVA = SupportDBUrlType.JAVADEFAULTURL.getLanguage(); static final String INFOMIX = SupportDBUrlType.INFORMIXDEFAULTURL.getLanguage(); static final String NETEZZA = SupportDBUrlType.NETEZZADEFAULTURL.getLanguage(); static final String DELIMITEDFILE = SupportDBUrlType.DELIMITEDFILE.getLanguage(); static final String HIVE = SupportDBUrlType.HIVEDEFAULTURL.getLanguage(); static final String VERTICA = EDatabaseTypeName.VERTICA.getXmlName(); static final String IMPALA = SupportDBUrlType.IMPALA.getLanguage(); static final String REDSHIFT = SupportDBUrlType.REDSHIFT.getLanguage(); static final String EXASOLUTION = SupportDBUrlType.EXASOL.getLanguage(); /** * Ansi SQL. */ public static final String SQL = "SQL"; //$NON-NLS-1$ private static final String DOT = "."; //$NON-NLS-1$ private static final String ASTERISK = "*"; //$NON-NLS-1$ private static final String COUNT_ASTERISK = "COUNT(*)"; //$NON-NLS-1$ /** * End of Statement: ";". */ protected static final String EOS = ";"; //$NON-NLS-1$ protected static final String JOIN_LEFT = "LEFT"; //$NON-NLS-1$ protected static final String JOIN_RIGHT = "RIGHT"; //$NON-NLS-1$ protected static final String JOIN_FULL = "FULL"; //$NON-NLS-1$ static final java.util.regex.Pattern SELECT_PATTERN = java.util.regex.Pattern.compile("SELECT", //$NON-NLS-1$ java.util.regex.Pattern.CASE_INSENSITIVE); /** * in upper case. */ private final String dbmsName; /** * version for current database. */ private final ProductVersion dbVersion; /** * the quoting string or an empty string if quoting is not supported. */ private String dbQuoteString = ""; //$NON-NLS-1$ private String regularExpressionFunction = ""; //$NON-NLS-1$ // default soundex string private final String SOUNDEX_PREFIX = "SOUNDEX";//$NON-NLS-1$ private String regularfunctionReturnValue = ""; //$NON-NLS-1$ /** * DbmsLanguage constructor for generic ANSI SQL (independent of any DBMS). */ DbmsLanguage() { this(SQL); } /** * DbmsLanguage constructor. * * @param dbmsType the name of the DBMS (MySQL, Oracle,...) */ DbmsLanguage(String dbmsType) { assert dbmsType != null : "DBMS type must not be null!!"; //$NON-NLS-1$ this.dbmsName = dbmsType; this.dbVersion = null; } /** * DbmsLanguage constructor. Use this constructor when functions are specific to a given release of the DBMS. * * @param dbmsType the database name * @param dbVersion the database version number */ DbmsLanguage(String dbmsType, ProductVersion dbVersion) { this.dbmsName = dbmsType; this.dbVersion = dbVersion; } /** * Method "quote". * * @param sqlIdentifier the SQL identifier to quote * @return the sqlIdentifier quoted. */ public String quote(String sqlIdentifier) { if (sqlIdentifier == null || sqlIdentifier.equals(PluginConstant.EMPTY_STRING)) { return PluginConstant.EMPTY_STRING; } String quotedSqlIdentifier = sqlIdentifier; if (!quotedSqlIdentifier.startsWith(dbQuoteString)) { quotedSqlIdentifier = dbQuoteString + quotedSqlIdentifier; } if (!quotedSqlIdentifier.endsWith(dbQuoteString)) { quotedSqlIdentifier = quotedSqlIdentifier + dbQuoteString; } return quotedSqlIdentifier; } public String and() { return surroundWithSpaces(SqlPredicate.AND.getLiteral()); } public String or() { return surroundWithSpaces(SqlPredicate.OR.getLiteral()); } public String greater() { return surroundWithSpaces(SqlPredicate.GREATER.getLiteral()); } public String greaterOrEqual() { return surroundWithSpaces(SqlPredicate.GREATER_EQUAL.getLiteral()); } public String less() { return surroundWithSpaces(SqlPredicate.LESS.getLiteral()); } public String lessOrEqual() { return surroundWithSpaces(SqlPredicate.LESS_EQUAL.getLiteral()); } public String between() { return surroundWithSpaces(SqlPredicate.BETWEEN.getLiteral()); } /** * Method "isNull". * * @return IS NULL surrounded with spaces. */ public String isNull() { return surroundWithSpaces(SqlPredicate.IS_NULL.getLiteral()); } /** * Method "isNotNull". * * @return " IS NOT NULL " surrounded with spaces. */ public String isNotNull() { return surroundWithSpaces(SqlPredicate.IS_NOT_NULL.getLiteral()); } /** * Method "equal". * * @return the = sign */ public String equal() { return surroundWithSpaces(SqlPredicate.EQUAL.getLiteral()); } /** * Method "notEqual". * * @return "<>" by default or "!=" on some specific DBMS */ public String notEqual() { // --- add the DBMS that do not allow <> operator // ANSI SQL, MySQL, return surroundWithSpaces(SqlPredicate.NOT_EQUAL.getLiteral()); } public String unionAll() { return union() + all(); } public String all() { return surroundWithSpaces(SqlPredicate.ALL.getLiteral()); } public String union() { return surroundWithSpaces(SqlPredicate.UNION.getLiteral()); } public String like() { return surroundWithSpaces(SqlPredicate.LIKE.getLiteral()); } public String from() { return surroundWithSpaces("FROM"); //$NON-NLS-1$ } /** * Method "eos". * * @return the end of SQL statement token (";") */ public String eos() { return EOS; } public String getDbmsName() { return this.dbmsName; } public ProductVersion getDbVersion() { return dbVersion; } /** * Method "getDefaultLanguage". * * @return the default String to use when no dbms is defined. */ public static String getDefaultLanguage() { return SQL; } public String toQualifiedName(String catalog, String schema, String table) { StringBuffer qualName = new StringBuffer(); if (catalog != null && catalog.trim().length() > 0) { qualName.append(this.handleContextModeOrAddQuotes(catalog)); qualName.append(getCatalogDelimiter()); } if (schema != null && schema.trim().length() > 0) { qualName.append(this.handleContextModeOrAddQuotes(schema)); qualName.append(getDelimiter()); } qualName.append(this.handleContextModeOrAddQuotes(table)); if (log.isDebugEnabled()) { log.debug(String.format("%s.%s.%s -> %s", catalog, schema, table, qualName)); //$NON-NLS-1$ } return qualName.toString(); } /** * Getter for delimiter between catalog and schema * * This may be different from the default delimiter for some databases, ex: Informix. * * @return catalog delimiter */ protected String getCatalogDelimiter() { return getDelimiter(); } /** * wrap context mode parameters by tags. otherwise, add quotes database identifier quotes. * * @param param * @return */ private String handleContextModeOrAddQuotes(String param) { if (param.startsWith("context.")) { //$NON-NLS-1$ return "<%=" + param + "%>"; //$NON-NLS-1$ //$NON-NLS-2$ } return this.quote(param); } /** * Getter for default SQL delimiter of the database. * * For the delimiter between catalog and schema, we should use {@link #getCatalogDelimiter()} instead of this. * * @return default SQL delimiter */ public String getDelimiter() { return DOT; } /** * Method "getPatternFinderDefaultFunction". * * @param expression a column name or a string * @return a default SQL expression which can be used as Pattern Frequency Statistics or null * @deprecated use {@link #getPatternFinderFunction(String, String, String)} instead */ @Deprecated public String getPatternFinderDefaultFunction(String expression) { return null; } /** * Method "getPatternFinderFunction". * * @param expression a column name or a string * @param charsToReplace the list of characters to remove * @param replacementChars the replacement characters * @return a default SQL expression which can be used as Pattern Frequency Statistics or null */ protected String getPatternFinderFunction(String expression, String charsToReplace, String replacementChars) { return null; } /** * Method "getPatternFinderFunction". * * @param colName a column name or a string * @param charactersMapping the mapping of the character to replace * @return an SQL expression which can be used as Pattern Frequency Statistics or null */ public String getPatternFinderFunction(String colName, EList<CharactersMapping> charactersMapping) { CharactersMapping charactersMap = adaptCharactersMapping(charactersMapping); if (charactersMap == null) { return null; } return this.getPatternFinderFunction(colName, charactersMap.getCharactersToReplace(), charactersMap.getReplacementCharacters()); } /** * * Get CharactersMapping from charactersMappingList, if not found, use the default "SQL" CharactersMapping. * * @param charactersMappingList all of charactersMapping * @return if there is CharactersMapping return it else if there is default "SQL" CharactersMapping else return * null. */ private CharactersMapping adaptCharactersMapping(EList<CharactersMapping> charactersMappingList) { CharactersMapping defaultCharactersMapping = null; for (CharactersMapping charactersMap : charactersMappingList) { if (this.is(charactersMap.getLanguage())) { if (validCharactersMapping(charactersMap)) { return charactersMap; } else { // current CharactersMapping is invalid so needn't care about default language case continue; } } else if (defaultCharactersMapping == null && DbmsLanguageFactory.isAllDatabaseType(charactersMap.getLanguage())) { if (validCharactersMapping(charactersMap)) { defaultCharactersMapping = charactersMap; } // else go to next character mapping } } return defaultCharactersMapping; } private boolean validCharactersMapping(CharactersMapping charactersMap) { final String charactersToReplace = charactersMap.getCharactersToReplace(); final String replacementCharacters = charactersMap.getReplacementCharacters(); if (!StringUtils.isEmpty(charactersToReplace) && !StringUtils.isEmpty(replacementCharacters) && charactersToReplace.length() == replacementCharacters.length()) { return true; } return false; } /** * Method "replaceOneChar". * * @param partialExpression * @param toReplace * @param replacement * @return the string REPLACE(partialExpression,'toReplace','replacement') */ protected String replaceOneChar(String partialExpression, char toReplace, char replacement) { return "REPLACE(" + partialExpression + ",'" + toReplace + "','" + replacement + "')"; //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ } /** * Method "translateUsingPattern". * * @param expression * @param charsToReplace * @param replacementChars * @return the string "TRANSLATE(expression,charsToReplace,replacementChars)" */ protected String translateUsingPattern(String expression, String charsToReplace, String replacementChars) { return "TRANSLATE(" + expression + " , '" + charsToReplace + "' , '" + replacementChars + "')"; //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ } public String replaceNullsWithString(String colName, String replacement) { return " CASE WHEN " + colName + isNull() + " THEN " + replacement + " ELSE " + colName + " END "; //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ } /** * Method "isNotBlank". * * @param colName a column name * @return the expression saying that the given column is not blank. */ public String isNotBlank(String colName) { // default is OK for MySQL return trim(colName) + notEqual() + " '' "; //$NON-NLS-1$ } public String trim(String colName) { // default is OK for MySQL return " TRIM(" + colName + ") "; //$NON-NLS-1$ //$NON-NLS-2$ } public String toUpperCase(String colName) { // default is OK for MySQL, Oracle return " UPPER(" + colName + ")"; //$NON-NLS-1$ //$NON-NLS-2$ } public String toLowerCase(String colName) { // default is OK for MySQL, Oracle return " LOWER(" + colName + ")"; //$NON-NLS-1$ //$NON-NLS-2$ } public String extractYear(String colName) { return extract(DateGrain.YEAR, colName); } public String extractQuarter(String colName) { return extract(DateGrain.QUARTER, colName); } public String extractMonth(String colName) { return extract(DateGrain.MONTH, colName); } public String extractWeek(String colName) { return extract(DateGrain.WEEK, colName); } public String extractDay(String colName) { return extract(DateGrain.DAY, colName); } /** * Method "getTopNQuery". * * @param query * @param n * @return the n first row of the given query */ public String getTopNQuery(String query, int n) { return query + " LIMIT " + n; //$NON-NLS-1$ } /** * Method "countRowInSubquery". * * @param subquery * @param alias the mandatory alias for the subquery * @return the select count(*) from aliased subquery */ public String countRowInSubquery(String subquery, String alias) { // ANSI SQL, MySQL return " SELECT COUNT(*) FROM (" + subquery + ") AS " + alias; //$NON-NLS-1$ //$NON-NLS-2$ } public String sumRowInSubquery(String colToSum, String subquery, String alias) { // ANSI SQL, MySQL return " SELECT SUM(" + colToSum + ") FROM (" + subquery + ") AS " + alias; //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ } public String selectAllRowsWhereColumnIn(String column, String table, String subquery) { return " SELECT * FROM " + table + where() + column + in() + "( SELECT " + column + from() + "(" + subquery //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ + ") AS mysubquery )"; //$NON-NLS-1$ } public String selectColumnsFromTable(List<String> columns, String table) { return " SELECT " + StringUtils.join(columns.iterator(), ',') + from() + table; //$NON-NLS-1$ } public String in() { return " IN "; //$NON-NLS-1$ } public String notIn() { return " NOT IN "; //$NON-NLS-1$ } public String not() { return " NOT "; //$NON-NLS-1$ } /** * Method "getDbQuoteString". * * @return the quote identifier string set in this object. */ public String getDbQuoteString() { return this.dbQuoteString; } public void setDbQuoteString(String dbQuoteString) { if (log.isDebugEnabled()) { log.debug("Database SQL quote: " + dbQuoteString); //$NON-NLS-1$ } this.dbQuoteString = dbQuoteString; } protected String extract(DateGrain dateGrain, String colName) { if (ConnectionUtils.isSybaseeDBProducts(getDbmsName())) { return "DATEPART(" + dateGrain + "," + colName + ") ";//$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ } // ANSI SQL, MySQL, Oracle return " EXTRACT(" + dateGrain + from() + colName + ") "; //$NON-NLS-1$ //$NON-NLS-2$ } /** * Method "is". * * @param dbName a DBMS name * @return true if this DBMS is given string */ private boolean is(String dbName) { return DbmsLanguageFactory.compareDbmsLanguage(dbName, this.dbmsName); } protected String surroundWithSpaces(String toSurround) { return surroundWith(' ', toSurround, ' '); } protected String surroundWith(char left, String toSurround, char right) { return left + toSurround + right; } /** * Method "addWhereToSqlStringStatement". The list * * @param completedSqlString a generic SQL expression in which the where clause variable will be replaced. * @param whereExpressions the list of where expressions to concatenate (must not be null) * @return the SQL statement with the where clause */ public String addWhereToSqlStringStatement(String completedSqlString, List<String> whereExpressions) { return addWhereToSqlStringStatement(completedSqlString, whereExpressions, true); } /** * add the where clause to the sql statement. * * @param completedSqlString a generic SQL expression in which the where clause variable will be replaced. * @param whereExpressions the list of where expressions to concatenate (must not be null) * @param valid if false add ! before where clause * @return the SQL statement with the where clause */ public String addWhereToSqlStringStatement(String completedSqlString, List<String> whereExpressions, boolean valid) { String query = completedSqlString; String where = this.buildWhereExpression(whereExpressions); if (where != null) { if (!valid) { where = '!' + this.surroundWith('(', where, ')'); } query = this.addWhereToStatement(query, where); } return query; } /** * Method "addWhereToStatement". * * @param statement a statement already prepared for parsing * @param whereClause (must not be null, but can be empty when there is no where clause) * @return the new statement */ public String addWhereToStatement(String statement, String whereClause) { String tempStatement = statement; String tempWhereClause = whereClause; final GenericSQLHandler genericSQLHandler = new GenericSQLHandler(tempStatement); if (genericSQLHandler.containsWhereClause()) { tempWhereClause = tempWhereClause.length() != 0 ? where() + tempWhereClause : tempWhereClause; tempStatement = genericSQLHandler.replaceWhere(tempWhereClause).getSqlString(); } if (genericSQLHandler.containsAndClause()) { tempWhereClause = tempWhereClause.length() != 0 ? and() + tempWhereClause : tempWhereClause; tempStatement = genericSQLHandler.replaceAndClause(tempWhereClause).getSqlString(); } if (genericSQLHandler.containsUDIWhere()) { tempStatement = genericSQLHandler.replaceUDIWhere(tempWhereClause).getSqlString(); } return tempStatement; } public String where() { return " WHERE "; //$NON-NLS-1$ } /** * Method "buildWhereExpression". * * @param whereExpression a list of boolean clauses * @return clauses between parentheses and concatenated with "AND" */ public String buildWhereExpression(List<String> whereExpression) { StringBuffer buf = new StringBuffer(); for (int i = 0; i < whereExpression.size(); i++) { String exp = whereExpression.get(i); buf.append(this.surroundWith('(', exp, ')')); if (i != whereExpression.size() - 1) { buf.append(and()); } } return buf.toString(); } public String desc() { return " DESC "; //$NON-NLS-1$ } public String orderBy() { return " ORDER BY "; //$NON-NLS-1$ } public String groupBy() { return " GROUP BY "; //$NON-NLS-1$ } /** * Method "getSqlExpression". * * @param indicatorDefinition contains a list of possible expression (one for each supported database) * @return the expression for this database language or for the default SQL or null when not found */ public Expression getSqlExpression(IndicatorDefinition indicatorDefinition) { EList<TdExpression> sqlGenericExpression = indicatorDefinition.getSqlGenericExpression(); // MOD xqliu 2010-02-25 feature 11201 MOD by zshen for bug 10630 boolean excelToAccess = "Distinct Count".equals(indicatorDefinition.getName()) //$NON-NLS-1$ && "EXCEL".equals(this.dbmsName);//$NON-NLS-1$ String dbms = excelToAccess ? "Access" : this.dbmsName;//$NON-NLS-1$ return getSqlExpression(indicatorDefinition, dbms, sqlGenericExpression, this.getDbVersion()); // ~11201 } /** * Method "getChartacterMappingExpression". * * @param indicatorDefinition contains a list of possible expression (one for each supported database) * @return the expression for this database language or for the default SQL or null when not found */ public CharactersMapping getChartacterMappingExpression(IndicatorDefinition indicatorDefinition) { EList<CharactersMapping> charactersMappingExpression = indicatorDefinition.getCharactersMapping(); // MOD xqliu 2010-02-25 feature 11201 MOD by zshen for bug 10630 boolean excelToAccess = "Distinct Count".equals(indicatorDefinition.getName()) //$NON-NLS-1$ && "EXCEL".equals(this.dbmsName);//$NON-NLS-1$ String dbms = excelToAccess ? "Access" : this.dbmsName;//$NON-NLS-1$ return getCharacterMappingExpression(indicatorDefinition, dbms, charactersMappingExpression, this.getDbVersion()); // ~11201 } /** * * Get the query Expression for one column * * @param column * @param where * @return */ public Expression getColumnQueryExpression(TdColumn column, String where) { ModelElement columnSet = ColumnHelper.getColumnOwnerAsColumnSet(column); Schema parentSchema = SchemaHelper.getParentSchema(columnSet); Catalog parentCatalog = CatalogHelper.getParentCatalog(columnSet); if (parentSchema != null) { parentCatalog = CatalogHelper.getParentCatalog(parentSchema); } String schemaName = parentSchema == null ? null : parentSchema.getName(); String catalogName = parentCatalog == null ? null : parentCatalog.getName(); String qualifiedName = this.toQualifiedName(catalogName, schemaName, columnSet.getName()); Expression queryExpression = CoreFactory.eINSTANCE.createExpression(); String expressionBody = getQuerySql(column.getName(), qualifiedName, where); queryExpression.setBody(expressionBody); queryExpression.setLanguage(this.getDbmsName()); return queryExpression; } /** * * Get the query Expression for one table of column * * @param column * @param where * @return */ public Expression getTableQueryExpression(MetadataTable metadataTable, String where) { Schema parentSchema = SchemaHelper.getParentSchema(metadataTable); Catalog parentCatalog = CatalogHelper.getParentCatalog(metadataTable); if (parentSchema != null) { parentCatalog = CatalogHelper.getParentCatalog(parentSchema); } String schemaName = parentSchema == null ? null : parentSchema.getName(); String catalogName = parentCatalog == null ? null : parentCatalog.getName(); String qualifiedName = this.toQualifiedName(catalogName, schemaName, metadataTable.getName()); Expression queryExpression = CoreFactory.eINSTANCE.createExpression(); String expressionBody = getQuerySql(getSelectColumnsStr(metadataTable), qualifiedName, where); queryExpression.setBody(expressionBody); queryExpression.setLanguage(this.getDbmsName()); return queryExpression; } /** * * Get select column string * * @param metadataTable * @return if columns size is zero will return * else return look like a,b,c */ protected String getSelectColumnsStr(MetadataTable metadataTable) { StringBuffer strBuff = new StringBuffer(); EList<MetadataColumn> filterColumns = metadataTable.getColumns(); if (filterColumns.size() <= 0) { return ASTERISK; } for (MetadataColumn column : filterColumns) { if (strBuff.length() > 0) { strBuff.append(getSeparatedCharacter()); } strBuff.append(this.quote(column.getName())); } return strBuff.toString(); } protected String getSeparatedCharacter() { return PluginConstant.COMMA_STRING; } /** * * Get the query Expression for one table of column * * @param column * @param where * @return */ public Expression getTableCountQueryExpression(MetadataTable metadataTable, String where) { Schema parentSchema = SchemaHelper.getParentSchema(metadataTable); Catalog parentCatalog = CatalogHelper.getParentCatalog(metadataTable); if (parentSchema != null) { parentCatalog = CatalogHelper.getParentCatalog(parentSchema); } String schemaName = parentSchema == null ? null : parentSchema.getName(); String catalogName = parentCatalog == null ? null : parentCatalog.getName(); String qualifiedName = this.toQualifiedName(catalogName, schemaName, metadataTable.getName()); Expression queryExpression = CoreFactory.eINSTANCE.createExpression(); String expressionBody = getQuerySql(COUNT_ASTERISK, qualifiedName, where); queryExpression.setBody(expressionBody); queryExpression.setLanguage(this.getDbmsName()); return queryExpression; } /** * * Get the query Expression for one table * * @param column * @param where * @return */ public Expression getTableQueryExpression(TdColumn column, String where) { ModelElement columnSet = ColumnHelper.getColumnOwnerAsColumnSet(column); Schema parentSchema = SchemaHelper.getParentSchema(columnSet); Catalog parentCatalog = CatalogHelper.getParentCatalog(columnSet); if (parentSchema != null) { parentCatalog = CatalogHelper.getParentCatalog(parentSchema); } String schemaName = parentSchema == null ? null : parentSchema.getName(); String catalogName = parentCatalog == null ? null : parentCatalog.getName(); String qualifiedName = this.toQualifiedName(catalogName, schemaName, columnSet.getName()); Expression queryExpression = CoreFactory.eINSTANCE.createExpression(); String expressionBody = getQuerySql(ASTERISK, qualifiedName, where); queryExpression.setBody(expressionBody); queryExpression.setLanguage(this.getDbmsName()); return queryExpression; } /** * DOC talend Comment method "getQuerySql". * * @param name * @param qualifiedName */ protected String getQuerySql(String name, String qualifiedName, String where) { String returnStr = "select " + name + " from " + qualifiedName;//$NON-NLS-1$ //$NON-NLS-2$ if (where != null && where.length() > 0) { returnStr += where() + where; } return returnStr; } /** * * DOC zshen Comment method "getsoundexFunction". * * @param table, the name of table. * @param colName, the name of column. * @return sub Select Statement which instead the function of soundex().And if the database support for soundex() * then return old table name. */ public String getSoundexFunction(String table, String colName) { return table; } /** * * DOC zshen Comment method "getsoundexFunction". * * @param table, the name of table. * @param colName, the name of column. * @param key, the condition of sql query. * @return the sql query for rows Statement. */ public String getFreqRowsStatement(String colName, String table, String key) { return null; } /** * Method "getAggregate1argFunctions". * * @param indicatorDefinition * @return the ordered list of aggregate functions */ public List<String> getAggregate1argFunctions(IndicatorDefinition indicatorDefinition) { final EList<TdExpression> aggregate1argFunctions = indicatorDefinition.getAggregate1argFunctions(); return getFunctions(indicatorDefinition, aggregate1argFunctions); } /** * Method "getDate1argFunctions". * * @param indicatorDefinition * @return the ordered list of functions applicable to date columns */ public List<String> getDate1argFunctions(IndicatorDefinition indicatorDefinition) { final EList<TdExpression> date1argFunctions = indicatorDefinition.getDate1argFunctions(); return getFunctions(indicatorDefinition, date1argFunctions); } private List<String> getFunctions(IndicatorDefinition indicatorDefinition, final EList<TdExpression> functions) { // MOD xqliu 2010-02-25 feature 11201 TdExpression sqlGenExpr = getSqlExpression(indicatorDefinition, this.dbmsName, functions, this.getDbVersion()); // ~11201 if (sqlGenExpr != null) { final String body = sqlGenExpr.getBody(); final String[] fonc = body.split(";"); //$NON-NLS-1$ return Arrays.asList(fonc); // language found } return Collections.emptyList(); } /** * Method "getRegexPatternString". * * @param indicator * @return the regular expression or null if none was found */ public String getRegexPatternString(Indicator indicator) { if (indicator instanceof PatternMatchingIndicator || (indicator instanceof UserDefIndicator && IndicatorCategoryHelper.isUserDefMatching( IndicatorCategoryHelper.getCategory(indicator.getIndicatorDefinition())))) { IndicatorParameters parameters = indicator.getParameters(); if (parameters == null) { return null; } Domain dataValidDomain = parameters.getDataValidDomain(); if (dataValidDomain == null) { return null; } EList<Pattern> patterns = dataValidDomain.getPatterns(); for (Pattern pattern : patterns) { Expression expression = this.getRegexp(pattern); return expression == null ? null : expression.getBody(); } } return null; } /** * get language Sql Expression from TdExpression list with dbVersion, if not found, use the default "SQL" language. * * @param language * @param sqlGenericExpression * @param dbVersion * @return */ public static CharactersMapping getCharacterMappingExpression(IndicatorDefinition indicatorDefinition, String language, EList<CharactersMapping> characterMappingExpression, ProductVersion dbVersion) { CharactersMapping defaultExpression = null; if (characterMappingExpression == null || characterMappingExpression.size() == 0) { return defaultExpression; } List<CharactersMapping> tempExpressions = new ArrayList<CharactersMapping>(); // exact match the language first, if don't match then use fuzzy matching boolean matchingFlag = false; for (CharactersMapping cmExpr : characterMappingExpression) { if (DbmsLanguageFactory.equalsDbmsLanguage(language, cmExpr.getLanguage())) { tempExpressions.add(cmExpr); matchingFlag = true; } } if (!matchingFlag) { // if the language contain the key word, it is considered to be equals for (CharactersMapping sqlGenExpr : characterMappingExpression) { if (DbmsLanguageFactory.compareDbmsLanguage(language, sqlGenExpr.getLanguage())) { tempExpressions.add(sqlGenExpr); } } } for (CharactersMapping exp : tempExpressions) { defaultExpression = exp; } if (defaultExpression != null) { // return the found default version expression return defaultExpression; } // else try with default language (ANSI SQL) String defaultLanguage = getDefaultLanguage(); // if can not find the expression of default language, return null if (language.equals(defaultLanguage)) { return null; } if (log.isDebugEnabled()) { log.warn("The indicator SQL expression has not been found for the database type " + language //$NON-NLS-1$ + " for the indicator" + indicatorDefinition.getName() //$NON-NLS-1$ + ". This is not necessarily a problem since the default SQL expression will be used. " //$NON-NLS-1$ + "Nevertheless, if an SQL error during the analysis, this could be the cause."); //$NON-NLS-1$ log.info("Trying to compute the indicator with the default language " + defaultLanguage); //$NON-NLS-1$ } return getCharacterMappingExpression(indicatorDefinition, defaultLanguage, characterMappingExpression, dbVersion); } /** * get language Sql Expression from TdExpression list with dbVersion, if not found, use the default "SQL" language. * * @param language * @param sqlGenericExpression * @param dbVersion * @return */ public static TdExpression getSqlExpression(IndicatorDefinition indicatorDefinition, String language, EList<TdExpression> sqlGenericExpression, ProductVersion dbVersion) { TdExpression defaultExpression = null; if (sqlGenericExpression == null || sqlGenericExpression.size() == 0) { return defaultExpression; } List<TdExpression> tempExpressions = new ArrayList<TdExpression>(); // exact match the language first, if don't match then use fuzzy matching boolean matchingFlag = false; for (TdExpression sqlGenExpr : sqlGenericExpression) { if (DbmsLanguageFactory.equalsDbmsLanguage(language, sqlGenExpr.getLanguage())) { tempExpressions.add(sqlGenExpr); matchingFlag = true; } } if (!matchingFlag) { // if the language contain the key word, it is considered to be equals for (TdExpression sqlGenExpr : sqlGenericExpression) { if (DbmsLanguageFactory.compareDbmsLanguage(language, sqlGenExpr.getLanguage())) { tempExpressions.add(sqlGenExpr); } } } List<TdExpression> tempExpressions2 = new ArrayList<TdExpression>(); for (TdExpression exp : tempExpressions) { if (exp.getVersion() == null || PluginConstant.EMPTY_STRING.equals(exp.getVersion())) { defaultExpression = exp; } else { if (dbVersion.toString().equals(exp.getVersion())) { // find the identical version return exp; } else { tempExpressions2.add(exp); } } } for (TdExpression exp : tempExpressions2) { if (dbVersion.toString().startsWith(exp.getVersion()) || exp.getVersion().startsWith(dbVersion.toString())) { // find the same major version, example: the sql expression's version is 5.1, the db version is 5.1.2 or // opposite return exp; } } if (defaultExpression != null) { // return the found default version expression return defaultExpression; } // TDQ-11558: added by msjian when there is no REDSHIFT definition, we use postgresql first if (language.equals(SupportDBUrlType.REDSHIFT.getLanguage())) { return getSqlExpression(indicatorDefinition, SupportDBUrlType.POSTGRESQLEFAULTURL.getLanguage(), sqlGenericExpression, dbVersion); } // TDQ-11558~ // else try with default language (ANSI SQL) String defaultLanguage = getDefaultLanguage(); // if can not find the expression of default language, return null if (language.equals(defaultLanguage)) { return null; } if (log.isDebugEnabled()) { log.warn("The indicator SQL expression has not been found for the database type " + language //$NON-NLS-1$ + " for the indicator" + indicatorDefinition.getName() //$NON-NLS-1$ + ". This is not necessarily a problem since the default SQL expression will be used. " //$NON-NLS-1$ + "Nevertheless, if an SQL error during the analysis, this could be the cause."); //$NON-NLS-1$ log.info("Trying to compute the indicator with the default language " + defaultLanguage); //$NON-NLS-1$ } return getSqlExpression(indicatorDefinition, defaultLanguage, sqlGenericExpression, dbVersion); } /** * Method "getRegexp". * * When in SQL engine, retrieve the regex with the matching Dbms Language. If null, return the regex with default * SQL (even if it's null) * * @param pattern a pattern * @return the body of the regular expression applicable to this dbms or null */ public Expression getRegexp(Pattern pattern) { // MOD by zhsne for bug 17172 2010.12.10 Expression expression = null; Expression defaultExpression = null; EList<PatternComponent> components = pattern.getComponents(); for (PatternComponent patternComponent : components) { if (patternComponent != null) { expression = this.getExpression(patternComponent); if (expression != null && DbmsLanguageFactory.compareDbmsLanguage(this.dbmsName, expression.getLanguage())) { return expression;// return this db's expression } else if (expression != null && DbmsLanguageFactory .compareDbmsLanguage(ExecutionLanguage.SQL.getName(), expression.getLanguage())) { defaultExpression = expression; // remember the default expression } } } return defaultExpression;// if can not find itself, return the default one. } public Expression getExpression(ModelElement element) { if (element == null) { return null; } Expression expression = null; if (element instanceof Pattern) { expression = getRegexp(((Pattern) element)); } else if (element instanceof Indicator) { Indicator indicator = ((Indicator) element); expression = this.getSqlExpression(indicator.getIndicatorDefinition()); } return expression; } public String getBackSlashForRegex() { return "\\"; //$NON-NLS-1$ } /** * Method "getExpression". * * @param patternComponent * @return the expression for the correct language or null */ public Expression getExpression(PatternComponent patternComponent) { if (patternComponent != null && patternComponent.eClass().equals(PatternPackage.eINSTANCE.getRegularExpression())) { RegularExpression regExp = (RegularExpression) patternComponent; Expression expression = regExp.getExpression(); if (expression != null) { return getApplicable(expression); } } // not a regular expression return null; } private Expression getApplicable(Expression expression) { return isApplicable(expression) ? expression : null; } /** * Method "getApplicableExpression". * * @param expression an expression (not null) * @return */ public boolean isApplicable(Expression expression) { // handle database specific patterns if (is(expression.getLanguage())) { return true; } // try generic language if (SQL.equals(expression.getLanguage())) { return true; } // try Java language if (JAVA.equals(expression.getLanguage())) { return true; } // else no correct language found return false; } /** * Method "getRegexpTestString" returns the SQL SELECT statement that can be used to check a string against a * regular expression. * * @param stringToCheck a string to check (not a column name) * @param regularExpression * @return the appropriate SQL SELECT statement that can be used to check a string against a regular expression. */ public String getSelectRegexpTestString(String stringToCheck, Expression regularExpression) { if (!isApplicable(regularExpression)) { return null; } String regex = regularExpression.getBody(); if (regex == null) { return null; } return getSelectRegexpTestString(stringToCheck, regex); } /** * Method "getSelectRegexpTestString". * * @param stringToCheck a string to check against the regular expression (can contain quotes at start and end) * @param regex a regular expression * @return the appropriate SQL SELECT statement that can be used to check a string against a regular expression. */ public String getSelectRegexpTestString(String stringToCheck, String regex) { String surroundedTestString = (stringToCheck.startsWith("'") && stringToCheck.endsWith("'")) ? stringToCheck //$NON-NLS-1$ //$NON-NLS-2$ : surroundWith('\'', stringToCheck, '\''); String regexLikeExpression = regexLike(surroundedTestString, regex); // else if (regexLikeExpression == null) { return null; } return getSelectRegexp(regexLikeExpression); } protected String getSelectRegexp(String regexLikeExpression) { return "SELECT " + regexLikeExpression + EOS; //$NON-NLS-1$ } /** * Method "regexLike". By default, it will try to extract the function name from user defined regular expression. If * the database support the regex like regular expression function , the sub-class will have to override this method * in order to return the correct regex like name. * * @param element * @param regex * @return get the String of Regular function(for example : "regex_like(element,regex)"). * */ public String regexLike(String element, String regex) { // TDQ-8637 UDF as a default case,if the database type has regular expression function,should overide this // method. if (null == regularExpressionFunction || PluginConstant.EMPTY_STRING.equals(regularExpressionFunction) || existEmptyInParameter(element, regex)) { return null; } String functionNameSQL = regularExpressionFunction + "( " + element + "," + regex + " )";//$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ return surroundWithSpaces(functionNameSQL); } /** * * @param element * @param regex * @return false if every one is not empty else return true */ private boolean existEmptyInParameter(String element, String regex) { return null == element || PluginConstant.EMPTY_STRING.equals(element) || null == regex || PluginConstant.EMPTY_STRING.equals(regex); } /** * * method "regexNotLike" * * @param element * @param regex * @return get the String of Regular function which is not match(for example :"not regex_like(element,regex)") * */ public String regexNotLike(String element, String regex) { // TDQ-8637 UDF as a default case,if the database type has regular expression function,should overide this // method. String functionNameSQL = regexLike(element, regex); if (functionNameSQL == null) { return null; } return this.not() + functionNameSQL; } /** * Method "getHardCodedQuoteIdentifier" returns the hard coded quote identifier string. You should call * {@link #getDbQuoteString()} instead. * * @return hard coded quote identifier string */ public String getHardCodedQuoteIdentifier() { return PluginConstant.EMPTY_STRING; } /** * Method "supportAliasesInGroupBy". * * @return true when the DB supports aliases in group by clause */ public boolean supportAliasesInGroupBy() { // else Oracle, MSSQL, DB2, Postgresql return false; } /** * Method "isPkIndexSupported". * * @return true when the driver supports primary key and index */ public boolean isPkIndexSupported() { return true; } /** * Method "supportCatalogSelection". See {@link java.sql.Connection#setCatalog(String catalog)} * * @return true when the driver can select a catalog as a subspace of the connection */ public boolean supportCatalogSelection() { return true; } /** * Method "supportNonIntegerConstantInGroupBy". * * @return true if expression like "GROUP BY 'toto'" are supported */ public boolean supportNonIntegerConstantInGroupBy() { // DBMS_SUPPORT // MySQL, Oracle return true; } /** * Method "getSelectRemarkOnTable". * * @param tableName a table name * @return the select statement to execute to get the comment on the table, or null */ public String getSelectRemarkOnTable(String tableName) { return null; } /** * Method "getSelectRemarkOnColumn". * * @param columnName a column name * @return the select statement to execute to get the comment on the column, or null */ public String getSelectRemarkOnColumn(String columnName) { return null; } /** * Method "getSelectOrderedAggregate". * * @param distinct whether to add distinct keyword in select statement * @param columns the list of columns to select * @param table the table * @param whereClause the where clause * @param groupByIndexes the indexes of the columns on which the aggregation is done * @param orderByIndexes the indexes of the columns on which the order is computed * @return the SQL statement */ public String getSelectOrderedAggregate(boolean distinct, List<String> columns, String table, String whereClause, List<Integer> groupByIndexes, String havingClause, List<Integer> orderByIndexes) { StringBuilder builder = new StringBuilder(); builder.append("SELECT "); //$NON-NLS-1$ builder.append(StringUtils.join(columns.iterator(), ',')); builder.append(", COUNT(*) "); //$NON-NLS-1$ builder.append(from()); builder.append(table); if (whereClause != null) { builder.append(whereClause); } List<String> groupByColumns = new ArrayList<String>(); for (int i = 0; i < groupByIndexes.size(); i++) { groupByColumns.add(columns.get(i)); } if (!groupByColumns.isEmpty()) { builder.append(" GROUP BY "); //$NON-NLS-1$ builder.append(StringUtils.join(groupByColumns.iterator(), ',')); } if (havingClause != null) { builder.append(havingClause); } List<String> orderByColumns = new ArrayList<String>(); for (int i = 0; i < orderByIndexes.size(); i++) { orderByColumns.add(columns.get(i)); } if (!orderByColumns.isEmpty()) { builder.append(" ORDER BY "); //$NON-NLS-1$ builder.append(StringUtils.join(orderByColumns.iterator(), ',')); } return builder.toString(); } public String orderBy(List<String> columns, boolean ascending) { return orderBy() + StringUtils.join(columns.iterator(), ',') + (ascending ? " ASC " : " DESC "); //$NON-NLS-1$ //$NON-NLS-2$ } /** * Method "getGenericInvalidDetailedValues". * * @return the generic query to get the invalid detailed values in the functional dependency analysis */ public String getFDGenericInvalidDetailedValues() { return "SELECT <%=__COLUMN_NAME_A__%> , <%=__COLUMN_NAME_B__%> , count(*) AS countNum FROM <%=__TABLE_NAME__%> JOIN (SELECT DISTINCT A FROM (SELECT DISTINCT <%=__COLUMN_NAME_A__%> AS A , <%=__COLUMN_NAME_B__%> AS B FROM <%=__TABLE_NAME__%> C <%=__WHERE_CLAUSE__%> ) T GROUP BY A HAVING COUNT(*) > 1 ) J on (J.A = <%=__TABLE_NAME__%>.<%=__COLUMN_NAME_A__%>) <%=__WHERE_CLAUSE__%> GROUP BY <%=__COLUMN_NAME_A__%> , <%=__COLUMN_NAME_B__%> ORDER BY <%=__COLUMN_NAME_A__%> ASC"; //$NON-NLS-1$ } public String getFDGenericValidDetailedValues() { return "SELECT <%=__COLUMN_NAME_A__%> , <%=__COLUMN_NAME_B__%> , count(*) AS countNum FROM <%=__TABLE_NAME__%> JOIN (SELECT DISTINCT A FROM (SELECT DISTINCT <%=__COLUMN_NAME_A__%> AS A , <%=__COLUMN_NAME_B__%> AS B FROM <%=__TABLE_NAME__%> C <%=__WHERE_CLAUSE__%> ) T GROUP BY A HAVING COUNT(*) = 1 ) J on (J.A = <%=__TABLE_NAME__%>.<%=__COLUMN_NAME_A__%>) <%=__WHERE_CLAUSE__%> GROUP BY <%=__COLUMN_NAME_A__%> , <%=__COLUMN_NAME_B__%> ORDER BY <%=__COLUMN_NAME_A__%> ASC"; //$NON-NLS-1$ } public String getFDGenericInvalidValues() { return "SELECT <%=__COLUMN_NAME_A__%> , count(*) AS countNum FROM <%=__TABLE_NAME__%> JOIN (SELECT DISTINCT A FROM (SELECT DISTINCT <%=__COLUMN_NAME_A__%> AS A , <%=__COLUMN_NAME_B__%> AS B FROM <%=__TABLE_NAME__%> C <%=__WHERE_CLAUSE__%> ) T GROUP BY A HAVING COUNT(*) > 1 ) J on (J.A = <%=__TABLE_NAME__%>.<%=__COLUMN_NAME_A__%>) <%=__WHERE_CLAUSE__%> GROUP BY <%=__COLUMN_NAME_A__%> ORDER BY <%=__COLUMN_NAME_A__%> ASC"; //$NON-NLS-1$ } public String getFDGenericValidValues() { return "SELECT <%=__COLUMN_NAME_A__%> , count(*) AS countNum FROM <%=__TABLE_NAME__%> JOIN (SELECT DISTINCT A FROM (SELECT DISTINCT <%=__COLUMN_NAME_A__%> AS A , <%=__COLUMN_NAME_B__%> AS B FROM <%=__TABLE_NAME__%> C <%=__WHERE_CLAUSE__%> ) T GROUP BY A HAVING COUNT(*) = 1 ) J on (J.A = <%=__TABLE_NAME__%>.<%=__COLUMN_NAME_A__%>) <%=__WHERE_CLAUSE__%> GROUP BY <%=__COLUMN_NAME_A__%> , <%=__COLUMN_NAME_B__%> ORDER BY <%=__COLUMN_NAME_A__%> ASC"; //$NON-NLS-1$ } public String getFDGenericValidRows() { return "SELECT * FROM <%=__TABLE_NAME__%> JOIN (SELECT DISTINCT A , COUNT(*) as countNum FROM (SELECT DISTINCT <%=__COLUMN_NAME_A__%> AS A , <%=__COLUMN_NAME_B__%> AS B FROM <%=__TABLE_NAME__%> C <%=__WHERE_CLAUSE__%> ) T GROUP BY A HAVING COUNT(*) = 1 ) J on (J.A = <%=__TABLE_NAME__%>.<%=__COLUMN_NAME_A__%>) <%=__WHERE_CLAUSE__%> ORDER BY <%=__COLUMN_NAME_A__%> ASC"; //$NON-NLS-1$ } public String getFDGenericInvalidRows() { return "SELECT * FROM <%=__TABLE_NAME__%> JOIN (SELECT DISTINCT A , COUNT(*) as countNum FROM (SELECT DISTINCT <%=__COLUMN_NAME_A__%> AS A , <%=__COLUMN_NAME_B__%> AS B FROM <%=__TABLE_NAME__%> C <%=__WHERE_CLAUSE__%> ) T GROUP BY A HAVING COUNT(*) > 1 ) J on (J.A = <%=__TABLE_NAME__%>.<%=__COLUMN_NAME_A__%>) <%=__WHERE_CLAUSE__%> ORDER BY <%=__COLUMN_NAME_A__%> ASC"; //$NON-NLS-1$ } /** * Method "getInstantiatedExpression". * * @param indicator * @return the appropriate expression or the default one (or null when it does not exists) */ public Expression getInstantiatedExpression(Indicator indicator) { Expression query = indicator.getInstantiatedExpressions(this.getDbmsName()); if (query == null) { // try to get a default sql expression query = indicator.getInstantiatedExpressions(getDefaultLanguage()); } return query; } /** * DOC scorreia Comment method "fillGenericQueryWithColumnTableAndAlias". * * @param genericQuery * @param columns * @param table * @param groupByAliases */ public String fillGenericQueryWithColumnTableAndAlias(String genericQuery, String columns, String table, String groupByAliases) { return new GenericSQLHandler(genericQuery).replaceColumnTableAlias(columns, table, groupByAliases) .getSqlString(); } public String fillGenericQueryWithColumnsAndTable(String genericQuery, String columns, String table) { return new GenericSQLHandler(genericQuery).replaceColumnTable(columns, table).getSqlString(); } public String fillGenericQueryWithColumnsABAndTable(String genericQuery, String columnA, String columnB, String table) { return new GenericSQLHandler(genericQuery).replaceColumnA(columnA).replaceColumnB(columnB) .replaceTable(table).getSqlString(); } public String fillGenericQueryWithColumnTablePattern(String genericQuery, String columns, String table, String regexp) { return new GenericSQLHandler(genericQuery).replaceColumnTablePattern(columns, table, regexp).getSqlString(); } /** * DOC scorreia Comment method "fillGenericQueryWithColumnTableLimitOffset". * * @param genericQuery * @param colName * @param table * @param limitRow * @param offset * @param limitRowPlusOffset * @return */ public String fillGenericQueryWithColumnTableLimitOffset(String genericQuery, String colName, String table, String limitRow, String offset, String limitRowPlusOffset) { return new GenericSQLHandler(genericQuery) .replaceLimitOffset(colName, table, limitRow, offset, limitRowPlusOffset).getSqlString(); } public String fillGenericQueryWithJoin(String genericSQL, String tableNameA, String tableNameB, String joinClause, String whereClause) { return new GenericSQLHandler(genericSQL).replaceWithJoin(tableNameA, tableNameB, joinClause, whereClause) .getSqlString(); } /** * DOC scorreia Comment method "fillGenericQueryWithJoin". * * @param body * @param tableName * @param joinclause * @return */ public String fillGenericQueryWithJoin(String genericSQL, String tableName, String joinclause) { return new GenericSQLHandler(genericSQL).replaceTable(tableName).replaceJoinClause(joinclause) .getSqlString(); } /** * Method "charLength". * * @param columnName * @return CHAR_LENGTH(columnName) */ public String charLength(String columnName) { return " CHAR_LENGTH(" + columnName + ") "; //$NON-NLS-1$ //$NON-NLS-2$ } /** * DOC bzhou Comment method "supportRegexp". * * check if the database surpport the Regexp. * * @return false by default. */ public boolean supportRegexp() { return false; } // MOD mzhao 2010-2-24 bug 11753. Add prefix catalog or schema in case of join tables. public String createJoinConditionAsString(ModelElement leftTable, List<JoinElement> joinElements, String catalogName, String schemaName) { return createJoinConditionAsString(leftTable, joinElements, catalogName, schemaName, null); } /** * create left join condiction string. * * @param leftTable * @param joinElements * @param catalogName * @param schemaName * @return */ public String createLeftJoinConditionAsString(ModelElement leftTable, List<JoinElement> joinElements, String catalogName, String schemaName) { return createJoinConditionAsString(leftTable, joinElements, catalogName, schemaName, JOIN_LEFT); } /** * create right join condiction string. * * @param leftTable * @param joinElements * @param catalogName * @param schemaName * @return */ public String createRightJoinConditionAsString(ModelElement leftTable, List<JoinElement> joinElements, String catalogName, String schemaName) { return createJoinConditionAsString(leftTable, joinElements, catalogName, schemaName, JOIN_RIGHT); } /** * create full join condiction string. * * @param leftTable * @param joinElements * @param catalogName * @param schemaName * @return */ public String createFullJoinConditionAsString(ModelElement leftTable, List<JoinElement> joinElements, String catalogName, String schemaName) { return createJoinConditionAsString(leftTable, joinElements, catalogName, schemaName, JOIN_FULL); } /** * 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(); } private void buildJoinClause(StringBuilder builder, String tableA, String tableAliasA, String columnAName, boolean hasTableAliasA, String tableB, String tableAliasB, String columnBName, boolean hasTableAliasB, String operator, String joinType) { boolean hasAlreadyOneJoin = builder.toString().contains(this.join()); // begin of query is built ouside this method and should be: // SELECT count(*) FROM leftTableName // tableAliasA JOIN if (hasTableAliasA && !hasAlreadyOneJoin) { builder.append(surroundWithSpaces(tableAliasA)); } // ~MOD mzhao 2010-2-24 bug 11753. Add prefix catalog or schema in case of join tables. join(builder, quote(tableA), tableAliasA, quote(columnAName), hasTableAliasA, tableB, tableAliasB, quote(columnBName), hasTableAliasB, operator, joinType); } /** * Method "innerJoin". * * @param tableA * @param tableAliasA * @param columnAName * @param hasTableAliasA * @param tableB * @param tableAliasB * @param columnBName * @param hasTableAliasB * @return "JOIN tableB tableAliasB ON (tableAliasA.columnAName = tableAliasB.columnBName) */ public String innerJoin(String tableA, String tableAliasA, String columnAName, boolean hasTableAliasA, String tableB, String tableAliasB, String columnBName, boolean hasTableAliasB) { StringBuilder builder = new StringBuilder(); return this.join(builder, tableA, tableAliasA, columnAName, hasTableAliasA, tableB, tableAliasB, columnBName, hasTableAliasB, this.equal()); } public String join(StringBuilder builder, String tableA, String tableAliasA, String columnAName, boolean hasTableAliasA, String tableB, String tableAliasB, String columnBName, boolean hasTableAliasB, String operator) { return join(builder, tableA, tableAliasA, columnAName, hasTableAliasA, tableB, tableAliasB, columnBName, hasTableAliasB, operator, null); } public String join(StringBuilder builder, String tableA, String tableAliasA, String columnAName, boolean hasTableAliasA, String tableB, String tableAliasB, String columnBName, boolean hasTableAliasB, String operator, String joinType) { String join = joinType == null ? join() : " " + joinType + join(); //$NON-NLS-1$ builder.append(join); // tableB tableAliasB ON builder.append(surroundWithSpaces(tableB)); if (hasTableAliasB) { builder.append(surroundWithSpaces(tableAliasB)); } builder.append(" ON "); //$NON-NLS-1$ String tA = hasTableAliasA ? tableAliasA : tableA; String tB = hasTableAliasB ? tableAliasB : tableB; String cA = columnAName; String cB = columnBName; createJoinClause(builder, tA, cA, tB, cB, operator); return builder.toString(); } public String join() { return " JOIN "; //$NON-NLS-1$ } /** * DOC scorreia Comment method "joinClauseStartsWithWrongTable". * * @param leftTable * @param table * @return */ private boolean joinClauseStartsWithWrongTable(ModelElement leftTable, ModelElement table) { return ResourceHelper.areSame(leftTable, table); } /** * Method "createJoinClause" appends a join condition to the builder. * * @param builder * @param tableA the name of the table or null * @param columnAName a column name (or an alias) * @param tableB the name of the second table or null * @param columnBName the column name (or an alias) * @param operator the operator used in the join */ private void createJoinClause(StringBuilder builder, String tableA, String columnAName, String tableB, String columnBName, String operator) { // (tablealiasA.colA = tablealiasB.colB) builder.append('('); if (tableA != null) { builder.append(tableA + DOT); } builder.append(columnAName); builder.append(operator); if (tableB != null) { builder.append(tableB + DOT); } builder.append(columnBName); builder.append(')'); } public String getColumnName(ModelElement col) { TdColumn column = col != null ? SwitchHelpers.COLUMN_SWITCH.doSwitch(col) : null; return column != null ? column.getName() : null; } public String getTableName(ModelElement col) { TdColumn column = col != null ? SwitchHelpers.COLUMN_SWITCH.doSwitch(col) : null; return (column != null) ? ColumnHelper.getTableFullName(column) : null; } public ModelElement getTable(ModelElement col) { TdColumn column = col != null ? SwitchHelpers.COLUMN_SWITCH.doSwitch(col) : null; return (column != null) ? ColumnHelper.getColumnOwnerAsColumnSet(column) : null; } /** * DOC jet adapt to {@link GenericSQLHandler}("").createGenericSqlWithRegexFunction() method. * <p> * * @see GenericSQLHandler * @param function UDF function name. * @return special sql statement */ public String createGenericSqlWithRegexFunction(String function) { return new GenericSQLHandler(PluginConstant.EMPTY_STRING).createGenericSqlWithRegexFunction(function); } /** * DOC yyi 2011-06-14 22246:view rows for aveagge length * * @return average length sql statement */ public String getAverageLengthRows() { return "SELECT * FROM <%=__TABLE_NAME__%> WHERE LENGTH(<%=__COLUMN_NAMES__%>) BETWEEN (SELECT FLOOR(SUM(LENGTH(<%=__COLUMN_NAMES__%>)) / COUNT(<%=__COLUMN_NAMES__%>)) FROM <%=__TABLE_NAME__%>) AND (SELECT CEIL(SUM(LENGTH(<%=__COLUMN_NAMES__%>)) / COUNT(<%=__COLUMN_NAMES__%>)) FROM <%=__TABLE_NAME__%>)"; //$NON-NLS-1$ } /** * @return if this DbmsLanage hasn't been inited return true, else return false */ public boolean isSql() { return SQL.equals(getDbmsName()); } public String getRegularExpressionFunction() { return this.regularExpressionFunction; } public void setRegularExpressionFunction(String functionName) { this.regularExpressionFunction = functionName; } public String trimIfBlank(String colName) { // just trim for blank data return " CASE WHEN " + charLength(trim(colName)) + "=0 THEN '' ELSE " + colName + " END"; //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ } /** * DOC qiongli TDQ-2474: view rows for average length with blank. * * @return average length with blank sql statement */ public String getAverageLengthWithBlankRows() { String whereExpression = "WHERE <%=__COLUMN_NAMES__%> IS NOT NULL "; //$NON-NLS-1$ return "SELECT * FROM <%=__TABLE_NAME__%> WHERE LENGTH(" + trimIfBlank("<%=__COLUMN_NAMES__%>") //$NON-NLS-1$//$NON-NLS-2$ + ") BETWEEN (SELECT FLOOR(SUM(LENGTH(" + trimIfBlank("<%=__COLUMN_NAMES__%>") //$NON-NLS-1$//$NON-NLS-2$ + ")) / COUNT(*)) FROM <%=__TABLE_NAME__%> " + whereExpression + ") AND (SELECT CEIL(SUM(LENGTH(" //$NON-NLS-1$//$NON-NLS-2$ + trimIfBlank("<%=__COLUMN_NAMES__%>") + " )) / COUNT(* )) FROM <%=__TABLE_NAME__%> " //$NON-NLS-1$//$NON-NLS-2$ + whereExpression + ")"; //$NON-NLS-1$ } /** * DOC qiongli TDQ-2474 :view rows for average length with null blank. * * @return average length with null blank sql statement */ public String getAverageLengthWithNullBlankRows() { return "SELECT * FROM <%=__TABLE_NAME__%> WHERE LENGTH(" + trimIfBlank("<%=__COLUMN_NAMES__%>") //$NON-NLS-1$//$NON-NLS-2$ + ") BETWEEN (SELECT FLOOR(SUM(LENGTH(" + trimIfBlank("<%=__COLUMN_NAMES__%>") //$NON-NLS-1$//$NON-NLS-2$ + ")) / COUNT(*)) FROM <%=__TABLE_NAME__%>) AND (SELECT CEIL(SUM(LENGTH(" //$NON-NLS-1$ + trimIfBlank("<%=__COLUMN_NAMES__%>") + " )) / COUNT(* )) FROM <%=__TABLE_NAME__%>)"; //$NON-NLS-1$ //$NON-NLS-2$ } /** * DOC qiongli TDQ-2474:view rows for average length with null. * * @return average length with null sql statement */ public String getAverageLengthWithNullRows() { String whereExpression = "WHERE(<%=__COLUMN_NAMES__%> IS NULL OR " + isNotBlank("<%=__COLUMN_NAMES__%>") //$NON-NLS-1$//$NON-NLS-2$ + ")"; //$NON-NLS-1$ return "SELECT * FROM <%=__TABLE_NAME__%> " + whereExpression //$NON-NLS-1$ + "AND LENGTH(<%=__COLUMN_NAMES__%>) BETWEEN (SELECT FLOOR(SUM(LENGTH(<%=__COLUMN_NAMES__%> )) / COUNT( * )) FROM <%=__TABLE_NAME__%> " //$NON-NLS-1$ + whereExpression + ") AND (SELECT CEIL(SUM(LENGTH(<%=__COLUMN_NAMES__%> )) / COUNT(*)) FROM <%=__TABLE_NAME__%> " //$NON-NLS-1$ + whereExpression + ")"; //$NON-NLS-1$ } /** * DOC xqliu Comment method "createStatement". * * @param connection * @return * @throws SQLException */ public Statement createStatement(java.sql.Connection connection, int fetchSize) throws SQLException { Statement statement = createStatement(connection); statement.setFetchSize(fetchSize); return statement; } /** * DOC msjian Comment method "createStatement". * * @param connection * @return * @throws SQLException */ public Statement createStatement(java.sql.Connection connection) throws SQLException { return connection.createStatement(); } /** * * 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; } /** * * move this method from ColumnSetNameHelper.getColumnSetQualifiedName(). * * @param columnset * @return */ public String getQueryColumnSetWithPrefix(ColumnSet columnset) { Catalog catalog = getCatalog(columnset); Schema schema = getSchema(columnset); String catalogName = null; String schemaName = null; if (catalog != null) { catalogName = catalog.getName(); } if (schema != null) { schemaName = schema.getName(); } return getQualifiedColumnSetName(columnset, catalogName, schemaName); } /** * DOC qiongli Comment method "getQualifiedColumnSetName". * * @param columnset * @param catalogName * @param schemaName * @return */ protected String getQualifiedColumnSetName(ColumnSet columnset, String catalogName, String schemaName) { DatabaseConnection dbConn = (DatabaseConnection) ConnectionHelper.getConnection(columnset); if (dbConn != null && dbConn.isContextMode()) { return getQueryColumnSetWithPrefixFromContext(dbConn, catalogName, schemaName, columnset.getName()); } return toQualifiedName(catalogName, schemaName, columnset.getName()); } protected String getQueryColumnSetWithPrefixFromContext(DatabaseConnection dbConn, String catalogName, String schemaName, String tableName) { String catalogNameFromContext = getCatalogNameFromContext(dbConn); String schemaNameFromContext = getSchemaNameFromContext(dbConn); String cName = catalogNameFromContext != null && catalogNameFromContext.trim().length() > 0 ? catalogNameFromContext : catalogName; String sName = schemaNameFromContext != null && schemaNameFromContext.trim().length() > 0 ? schemaNameFromContext : schemaName; return toQualifiedName(cName, sName, tableName); } /** * * move this method from ColumnSetNameHelper.getColumnSetQualifiedName(). * * @param columnset * @return */ public String getQueryColumnSetWithPrefix(TdColumn tdColumn) { ColumnSet columnSet = ColumnHelper.getColumnOwnerAsColumnSet(tdColumn); if (columnSet == null) { log.error("Can not find table by column"); //$NON-NLS-1$ return PluginConstant.EMPTY_STRING; } return getQueryColumnSetWithPrefix(columnSet); } protected String getQueryColumns(TdColumn[] columns) { String columnClause = PluginConstant.EMPTY_STRING; for (TdColumn column : columns) { columnClause += quote(column.getName()) + getSeparatedCharacter(); } columnClause = columnClause.substring(0, columnClause.length() - 1); return columnClause; } /** * * Get invalid clause for Benford indicator. * * @param columnName * @return */ public String getInvalidClauseBenFord(String columnName) { return columnName + " is null or " + columnName + " not REGEXP '^[0-9]'"; //$NON-NLS-1$ //$NON-NLS-2$ } /** * * Cast column name to char type. .e.g.vertica column name is to_char(columnName) in query clause. * * @param columnName * @return */ public String castColumnNameToChar(String columnName) { return columnName; } /** * get the catalog name from the context in the DatabaseConnection(the database connection must be context mode). * * @param dbConn * @return catalog name or null */ public String getCatalogNameFromContext(DatabaseConnection dbConn) { return cloneOriginalValueConnection(dbConn).getSID(); } /** * get the schema name from the context in the DatabaseConnection(the database connection must be context mode). * * @param dbConn * @return schema name or null */ public String getSchemaNameFromContext(DatabaseConnection dbConn) { return cloneOriginalValueConnection(dbConn).getUiSchema(); } /** * clone the database connection with original value according to the context(the database connection must be * context mode). * * @param dbConn * @return */ private DatabaseConnection cloneOriginalValueConnection(DatabaseConnection dbConn) { IRepositoryContextService repositoryContextService = CoreRuntimePlugin.getInstance() .getRepositoryContextService(); if (repositoryContextService != null) { String contextName = dbConn.getContextName(); if (contextName == null) { String msg = Messages.getString("DbmsLanguage.ContextNameIsNull"); //$NON-NLS-1$ RuntimeException exp = new RuntimeException(msg); log.error(msg, exp); throw exp; } return repositoryContextService.cloneOriginalValueConnection(dbConn, false, contextName); } else { String msg = Messages.getString("DbmsLanguage.IRepositoryContextServiceIsNull"); //$NON-NLS-1$ RuntimeException exp = new RuntimeException(msg); log.error(msg, exp); throw exp; } } /** * get the catalog or schema name according to the analyzed column. * * @param analyzedColumn * @return if the catalog is not null, return catalog's name, else if schema is not null, return schema's name, else * return null */ public String getCatalogOrSchemaName(TdColumn tdColumn) { String name = null; // get the catalog/schema name from the context DatabaseConnection dbConn = ConnectionHelper.getTdDataProvider(tdColumn); if (dbConn != null && dbConn.isContextMode()) { name = getCatalogNameFromContext(dbConn); if (!StringUtils.isEmpty(name)) { return name; } name = getSchemaNameFromContext(dbConn); if (!StringUtils.isEmpty(name)) { return name; } } // if the catalog/schema name from the context is empty, get it by the column if (StringUtils.isEmpty(name)) { ColumnSet columnSet = ColumnHelper.getColumnOwnerAsColumnSet(tdColumn); // Get catalog Catalog catalog = getCatalog(columnSet); if (catalog != null) { return catalog.getName(); } // Get schema Schema schema = getSchema(columnSet); if (schema != null) { return schema.getName(); } // no catalog and schema log.error(Messages.getString("DbmsLanguage.NoCatalogOrSchema", columnSet.getName()));//$NON-NLS-1$ } return name; } /** * get the schema from the columnSetOwner. * * @param columnSetOwner * @return */ protected Schema getSchema(ModelElement columnSetOwner) { Package pack = ColumnSetHelper.getParentCatalogOrSchema(columnSetOwner); if (pack instanceof Schema) { return (Schema) pack; } return null; } /** * get the catalog from the columnSetOwner. * * @param columnSetOwner it should be a table or schema * @return */ protected Catalog getCatalog(ModelElement columnSetOwner) { Package pack = ColumnSetHelper.getParentCatalogOrSchema(columnSetOwner); if (pack instanceof Catalog) { return (Catalog) pack; } return null; } /** * Getter for soundexPrefix. * * @return the soundexPrefix */ public String getSoundexPrefix() { return SOUNDEX_PREFIX; } /** * * Extract the name of regular Expression Function If current database type need to use UDF deal regular expression, * the expresssion which will definition on "Regular Expression Matching.definition" should like below: * * "* + when REGULAR_FUNCTION(+ * +) + *". else this method will not return correct result which you want * * @param expression * @return the name of regular Expression Function or empty string when the expression is invalid * */ public String extractRegularExpressionFunction(Expression expression, String regexp) { String functionName = null; try { String tempString = splictExpression(expression); functionName = tempString.split("\\(").length > 1 ? tempString.split("\\(")[0] //$NON-NLS-1$//$NON-NLS-2$ : PluginConstant.EMPTY_STRING; functionName = functionName.trim(); } catch (NullPointerException e) { log.error(e, e); } return functionName; } /** * DOC talend Comment method "splictExpression". * * @param expression * @return */ protected String splictExpression(Expression expression) { if (expression == null || expression.getBody() == null) { return PluginConstant.EMPTY_STRING; } String body = expression.getBody().toUpperCase(); String tempString = body.split("WHEN").length > 1 ? body.split("WHEN")[1] : PluginConstant.EMPTY_STRING;//$NON-NLS-1$//$NON-NLS-2$ return tempString; } /** * * Extract the return value of regular Expression Function If current database type need to use UDF deal regular * expression, the expresssion which will definition on "Regular Expression Matching.definition" should like below: * * "* + when REGULAR_FUNCTION(+ * +) + return value + then". else this method will not return correct result which * you want * * @param expression * @return the return value of regular Expression Function or empty string when the expression is invalid * */ public String extractRegularExpressionFunctionReturnValue(Expression expression, String regexp) { String tempString = splictExpression(expression); if (regexp == null) { return tempString; } String splitKey = regexp.toUpperCase() + ")"; //$NON-NLS-1$ int keyIndex = tempString.indexOf(splitKey) + splitKey.length(); tempString = tempString.indexOf(splitKey) > -1 ? tempString.substring(keyIndex) : PluginConstant.EMPTY_STRING; tempString = tempString.split("THEN").length > 1 ? tempString.split("THEN")[0] //$NON-NLS-1$//$NON-NLS-2$ : PluginConstant.EMPTY_STRING; return tempString.trim(); } /** * * remember the result value for regular expression.So that we can get complete expression and it should be "=1" * always * * @param expression */ public void setFunctionReturnValue(String returnValue) { regularfunctionReturnValue = returnValue.trim(); } /** * Getter for regularfunctionReturnValue. * * @return the regularfunctionReturnValue if it is null then return "" */ public String getFunctionReturnValue() { if (regularfunctionReturnValue == null) { return StringUtils.EMPTY; } return this.regularfunctionReturnValue; } /** * Method "castColumn". * * @param indicator * @param tdColumn * @param colName the name of the given column (tdColumn.getName() ) (could contain quotes) * @return */ public String castColumn4ColumnAnalysisSqlExecutor(Indicator indicator, TdColumn tdColumn, String colName) { int javaType = tdColumn.getSqlDataType().getJavaDataType(); boolean isText = Java2SqlType.isTextInSQL(javaType); String contentType = tdColumn.getContentType(); DataminingType dataminingType = DataminingType.get(contentType); if (DataminingType.INTERVAL.equals(dataminingType) && (isText)) { // cast is needed // MOD qiongli 2011-4-18,bug 16723(data cleansing),handle date boolean isDate = Java2SqlType.isDateInSQL(javaType); if (isDate) { return "CAST(" + colName + " AS DATE)";//$NON-NLS-1$//$NON-NLS-2$ } boolean isNumeric = Java2SqlType.isNumbericInSQL(javaType); if (isNumeric) { // TODO scorreia user should tell the expected format return "CAST(" + colName + " AS DECIMAL)"; //$NON-NLS-1$ //$NON-NLS-2$ } } return colName; } /** * DOC msjian Comment method "getRandomQuery". * * @param query * @return query with random method */ public String getRandomQuery(String query) { // this method can be used for as400, db2, hive, ingres, mysql, exasol return query + orderBy() + "RAND() "; //$NON-NLS-1$ } }