Java Code Examples for org.pentaho.di.core.database.DatabaseMeta#quoteField()

/**
 * This method traverses the set of selections and renders those selections to the SQL string buffer. This method
 * determines the SQL column aliases. It also calls getBusinessColumnSQL() which renders each individual business
 * column in three different ways. Either as an MQL Formula, an aggregate function, or as a standard SQL column.
 *
 * @param sql             sql string buffer
 * @param model           business model
 * @param databaseMeta    database metadata
 * @param selections      sql selections
 * @param disableDistinct if true, disable distinct rendering
 * @param group           if true, disable distinct rendering
 * @param locale          locale string
 * @param columnsMap      map of column aliases to populate
 */
public void generateSelect( SQLQueryModel query, BusinessModel model, DatabaseMeta databaseMeta,
                            List<Selection> selections, boolean disableDistinct, int limit, boolean group,
                            String locale,
                            Map<BusinessTable, String> tableAliases, Map<String, String> columnsMap ) {
  query.setDistinct( !disableDistinct && !group );
  query.setLimit( limit );
  for ( int i = 0; i < selections.size(); i++ ) {
    // in some database implementations, the "as" name has a finite length;
    // for instance, oracle cannot handle a name longer than 30 characters.
    // So, we map a short name here to the longer id, and replace the id
    // later in the resultset metadata.
    String alias = null;
    if ( columnsMap != null ) {
      alias = databaseMeta.generateColumnAlias( i, selections.get( i ).getBusinessColumn().getId() );
      columnsMap.put( alias, selections.get( i ).getBusinessColumn().getId() );
      alias = databaseMeta.quoteField( alias );
    } else {
      alias = databaseMeta.quoteField( selections.get( i ).getBusinessColumn().getId() );
    }
    SQLAndTables sqlAndTables =
      getBusinessColumnSQL( model, selections.get( i ), tableAliases, databaseMeta, locale );
    query.addSelection( sqlAndTables.getSql(), alias );
  }
}
 

public String getAliasColumn( String tableAlias, String formula, DatabaseMeta databaseMeta ) {

    String retval;

    if ( getTable() != null && formula != null ) {
      if ( !isExact() ) {
        retval = databaseMeta.quoteField( tableAlias ) + "." + databaseMeta.quoteField( formula ); //$NON-NLS-1$
      } else {
        retval = getFormula();
      }
    } else {
      retval = "??"; //$NON-NLS-1$
    }

    return retval;
  }
 

public String getDeleteStatement( RowMetaInterface rowMeta ) throws KettleDatabaseException {
  DatabaseMeta databaseMeta = meta.getDatabaseMeta();
  data.deleteParameterRowMeta = new RowMeta();

  String sql = "DELETE FROM " + data.realSchemaTable + Const.CR;

  sql += "WHERE ";

  for ( int i = 0; i < meta.getKeyLookup().length; i++ ) {
    if ( i != 0 ) {
      sql += "AND   ";
    }
    sql += databaseMeta.quoteField( meta.getKeyLookup()[i] );
    if ( "BETWEEN".equalsIgnoreCase( meta.getKeyCondition()[i] ) ) {
      sql += " BETWEEN ? AND ? ";
      data.deleteParameterRowMeta.addValueMeta( rowMeta.searchValueMeta( meta.getKeyStream()[i] ) );
      data.deleteParameterRowMeta.addValueMeta( rowMeta.searchValueMeta( meta.getKeyStream2()[i] ) );
    } else if ( "IS NULL".equalsIgnoreCase( meta.getKeyCondition()[i] ) || "IS NOT NULL".equalsIgnoreCase( meta
        .getKeyCondition()[i] ) ) {
      sql += " " + meta.getKeyCondition()[i] + " ";
    } else {
      sql += " " + meta.getKeyCondition()[i] + " ? ";
      data.deleteParameterRowMeta.addValueMeta( rowMeta.searchValueMeta( meta.getKeyStream()[i] ) );
    }
  }
  return sql;
}
 

public String getLookupStatement( RowMetaInterface rowMeta ) throws KettleDatabaseException {
  data.lookupParameterRowMeta = new RowMeta();
  data.lookupReturnRowMeta = new RowMeta();

  DatabaseMeta databaseMeta = meta.getDatabaseMeta();

  String sql = "SELECT ";

  for ( int i = 0; i < meta.getUpdateLookup().length; i++ ) {
    if ( i != 0 ) {
      sql += ", ";
    }
    sql += databaseMeta.quoteField( meta.getUpdateLookup()[i] );
    data.lookupReturnRowMeta.addValueMeta( rowMeta.searchValueMeta( meta.getUpdateStream()[i] ).clone() );
  }

  sql += " FROM " + data.realSchemaTable + " WHERE ";

  for ( int i = 0; i < meta.getKeyLookup().length; i++ ) {
    if ( i != 0 ) {
      sql += " AND ";
    }
    sql += databaseMeta.quoteField( meta.getKeyLookup()[i] );
    if ( "BETWEEN".equalsIgnoreCase( meta.getKeyCondition()[i] ) ) {
      sql += " BETWEEN ? AND ? ";
      data.lookupParameterRowMeta.addValueMeta( rowMeta.searchValueMeta( meta.getKeyStream()[i] ) );
      data.lookupParameterRowMeta.addValueMeta( rowMeta.searchValueMeta( meta.getKeyStream2()[i] ) );
    } else {
      if ( "IS NULL".equalsIgnoreCase( meta.getKeyCondition()[i] ) || "IS NOT NULL".equalsIgnoreCase( meta
          .getKeyCondition()[i] ) ) {
        sql += " " + meta.getKeyCondition()[i] + " ";
      } else {
        sql += " " + meta.getKeyCondition()[i] + " ? ";
        data.lookupParameterRowMeta.addValueMeta( rowMeta.searchValueMeta( meta.getKeyStream()[i] ) );
      }
    }
  }
  return sql;
}
 

public void prepareDelete( RowMetaInterface rowMeta ) throws KettleDatabaseException {
  DatabaseMeta databaseMeta = meta.getDatabaseMeta();
  data.deleteParameterRowMeta = new RowMeta();

  String sql = "DELETE FROM " + data.schemaTable + Const.CR;

  sql += "WHERE ";

  for ( int i = 0; i < meta.getKeyLookup().length; i++ ) {
    if ( i != 0 ) {
      sql += "AND   ";
    }
    sql += databaseMeta.quoteField( meta.getKeyLookup()[i] );
    if ( "BETWEEN".equalsIgnoreCase( meta.getKeyCondition()[i] ) ) {
      sql += " BETWEEN ? AND ? ";
      data.deleteParameterRowMeta.addValueMeta( rowMeta.searchValueMeta( meta.getKeyStream()[i] ) );
      data.deleteParameterRowMeta.addValueMeta( rowMeta.searchValueMeta( meta.getKeyStream2()[i] ) );
    } else if ( "IS NULL".equalsIgnoreCase( meta.getKeyCondition()[i] )
      || "IS NOT NULL".equalsIgnoreCase( meta.getKeyCondition()[i] ) ) {
      sql += " " + meta.getKeyCondition()[i] + " ";
    } else {
      sql += " " + meta.getKeyCondition()[i] + " ? ";
      data.deleteParameterRowMeta.addValueMeta( rowMeta.searchValueMeta( meta.getKeyStream()[i] ) );
    }
  }

  try {
    if ( log.isDetailed() ) {
      logDetailed( "Setting delete preparedStatement to [" + sql + "]" );
    }
    data.prepStatementDelete = data.db.getConnection().prepareStatement( databaseMeta.stripCR( sql ) );
  } catch ( SQLException ex ) {
    throw new KettleDatabaseException( "Unable to prepare statement for SQL statement [" + sql + "]", ex );
  }
}
 

private ValueMetaInterface getValueForLogicalColumn(DatabaseMeta databaseMeta, LogicalColumn column) {
  String columnName = ConceptUtil.getName(column, locale);
  String phColumnName = ConceptUtil.getString(column, DefaultIDs.LOGICAL_COLUMN_PHYSICAL_COLUMN_NAME);
  DataType columnType = column.getDataType();
  String lengthString = ConceptUtil.getString(column, DefaultIDs.LOGICAL_COLUMN_LENGTH);
  int length = Const.toInt(lengthString, -1);
  String precisionString = ConceptUtil.getString(column, DefaultIDs.LOGICAL_COLUMN_PRECISION);
  int precision = Const.toInt(precisionString, -1);

  int type=ValueMetaInterface.TYPE_STRING;
  switch(columnType) {
  case UNKNOWN:
  case URL:
  case STRING: precision=-1; break;
  case IMAGE:
  case BINARY: type = ValueMetaInterface.TYPE_BINARY; precision=-1; break;
  case BOOLEAN: type = ValueMetaInterface.TYPE_BOOLEAN; length=-1; precision=-1; break;
  case DATE: type = ValueMetaInterface.TYPE_DATE; length=-1; precision=-1; break;
  case NUMERIC:
    if (precision<=0 && length<15) {
      type = ValueMetaInterface.TYPE_INTEGER;
    } else {
      if (length>=15) {
        type = ValueMetaInterface.TYPE_BIGNUMBER;
      } else {
        type = ValueMetaInterface.TYPE_NUMBER;
      }
    }
    break;
    default:
      break;
  }
  ValueMetaInterface value = new ValueMeta(databaseMeta.quoteField(Const.NVL(phColumnName, columnName)), type);
  value.setLength(length, precision);
  return value;
}
 

/**
 * This method traverses the set of selections and renders those selections to the SQL string buffer. This method
 * determines the SQL column aliases. It also calls getBusinessColumnSQL() which renders each individual business
 * column in three different ways. Either as an MQL Formula, an aggregate function, or as a standard SQL column.
 * 
 * @param query
 *          SQL model of the data. It will be updated during method execution.
 * @param model
 *          Business model.
 * @param databaseMeta
 *          Database metadata.
 * @param selections
 *          SQL selections.
 * @param disableDistinct
 *          If true, disables distinct rendering.
 * @param limit 
 *          Maximum number of the rows to be returned.
 * @param group
 *          If true, disables distinct rendering.
 * @param locale
 *          Locale string.
 * @param tableAliases
 *          Aliases of the tables to be used during query generation.
 * @param columnsMap
 *          Map of column aliases to populate.
 * @param parameters
 *          Parameters to be used during query generation.
 * @param genAsPreparedStatement
 *          Tells the method generate query as prepared statement.
 *  
 */
protected void generateSelect( SQLQueryModel query, LogicalModel model, DatabaseMeta databaseMeta,
    List<Selection> selections, boolean disableDistinct, int limit, boolean group, String locale,
    Map<LogicalTable, String> tableAliases, Map<String, String> columnsMap, Map<String, Object> parameters,
    boolean genAsPreparedStatement ) {
  query.setDistinct( !disableDistinct && !group );
  query.setLimit( limit );
  for ( int i = 0; i < selections.size(); i++ ) {
    // In some database implementations, the "as" name has a finite length;
    // for instance, oracle cannot handle a name longer than 30 characters.
    // So, we map a short name here to the longer id, and replace the id
    // later in the resultset metadata.
    String alias = null;
    if ( columnsMap != null ) {
      alias = databaseMeta.generateColumnAlias( i, selections.get( i ).getLogicalColumn().getId() );
      columnsMap.put( alias, selections.get( i ).getLogicalColumn().getId() );
      alias = databaseMeta.quoteField( alias );
    } else {
      alias = databaseMeta.quoteField( selections.get( i ).getLogicalColumn().getId() );
    }
    SqlAndTables sqlAndTables =
        getBusinessColumnSQL( model, selections.get( i ), tableAliases, parameters, genAsPreparedStatement,
            databaseMeta, locale );
    query.addSelection( sqlAndTables.getSql(), alias );
  }
}
 

/**
 * Generates sql for the specified business column.
 * @param businessModel
 *          The business model to reference.
 * @param column
 *          The column to build SQL for.
 * @param tableAliases
 *          Aliases of the tables to be used during query generation.
 * @param parameters
 *          Parameters to be used during query generation.
 * @param genAsPreparedStatement
 *          Forces the method generate query as prepared statement.
 * @param databaseMeta
 *          The database to reference.
 * @param locale
 *          The locale to use.
 * @return Returns sql for specified business column.
 */
public static SqlAndTables getBusinessColumnSQL( LogicalModel businessModel, Selection column,
    Map<LogicalTable, String> tableAliases, Map<String, Object> parameters, boolean genAsPreparedStatement,
    DatabaseMeta databaseMeta, String locale ) {
  String targetColumn = (String) column.getLogicalColumn().getProperty( SqlPhysicalColumn.TARGET_COLUMN );
  LogicalTable logicalTable = column.getLogicalColumn().getLogicalTable();
  if ( column.getLogicalColumn().getProperty( SqlPhysicalColumn.TARGET_COLUMN_TYPE ) == TargetColumnType.OPEN_FORMULA ) {
    // convert to sql using libformula subsystem

    try {
      // we'll need to pass in some context to PMSFormula so it can resolve aliases if necessary
      SqlOpenFormula formula =
          new SqlOpenFormula( businessModel, logicalTable, databaseMeta, targetColumn, tableAliases, parameters,
              genAsPreparedStatement );
      formula.parseAndValidate();

      String formulaSql = formula.generateSQL( locale );

      // check for old style, where function is hardcoded in the model.
      if ( column.hasAggregate() && !hasAggregateDefinedAlready( formulaSql, databaseMeta ) ) {
        formulaSql = getFunctionExpression( column, formulaSql, databaseMeta );
      }

      return new SqlAndTables( formulaSql, formula.getLogicalTables(), formula.getSelections() );
    } catch ( PentahoMetadataException e ) {
      // this is for backwards compatibility.
      // eventually throw any errors
      logger.warn( Messages.getErrorString( "SqlGenerator.ERROR_0001_FAILED_TO_PARSE_FORMULA", targetColumn ), e ); //$NON-NLS-1$

      // Report just this table and column as being used along with the formula.
      //
      return new SqlAndTables( targetColumn, logicalTable, column );
    }
  } else {
    String tableColumn = ""; //$NON-NLS-1$

    // this step is required because this method is called in two contexts. The first
    // call determines all the tables involved, making it impossible to guarantee
    // unique aliases.

    String tableAlias = null;
    if ( tableAliases != null ) {
      tableAlias = tableAliases.get( logicalTable );
    } else {
      tableAlias = logicalTable.getId();
    }
    tableColumn += databaseMeta.quoteField( tableAlias );
    tableColumn += "."; //$NON-NLS-1$

    // TODO: WPG: instead of using formula, shouldn't we use the physical column's name?
    tableColumn += databaseMeta.quoteField( targetColumn );

    // For the having clause, for example: HAVING sum(turnover) > 100
    if ( column.hasAggregate() ) {
      return new SqlAndTables( getFunctionExpression( column, tableColumn, databaseMeta ), logicalTable, column );
    } else {
      return new SqlAndTables( tableColumn, logicalTable, column );
    }
  }
}
 

/**
 * Generates joins of the specified relation.
 * 
 * @param businessModel
 *          The business model to reference.
 * @param relation
 *          Relation describing the join.
 * @param column
 *          The column to build SQL for.
 * @param tableAliases
 *          Aliases of the tables to be used during query generation.
 * @param parameters
 *          Parameters to be used during query generation.
 * @param genAsPreparedStatement
 *          Forces the method generate query as prepared statement.
 * @param databaseMeta
 *          The database to reference.
 * @param locale
 *          The locale to use.
 * @return  string Query of the join created for specified relations.
 * @throws PentahoMetadataException
 */
protected String getJoin( LogicalModel businessModel, LogicalRelationship relation,
    Map<LogicalTable, String> tableAliases, Map<String, Object> parameters, boolean genAsPreparedStatement,
    DatabaseMeta databaseMeta, String locale ) throws PentahoMetadataException {
  String join = ""; //$NON-NLS-1$
  if ( relation.isComplex() ) {
    try {
      // parse join as MQL
      SqlOpenFormula formula =
          new SqlOpenFormula( businessModel, databaseMeta, relation.getComplexJoin(), tableAliases, parameters,
              genAsPreparedStatement );
      formula.parseAndValidate();
      join = formula.generateSQL( locale );
    } catch ( PentahoMetadataException e ) {
      // backward compatibility, deprecate
      // FIXME: we need to get rid of this and just throw an exception
      logger.warn( Messages.getErrorString(
          "SqlGenerator.ERROR_0017_FAILED_TO_PARSE_COMPLEX_JOIN", relation.getComplexJoin() ), e ); //$NON-NLS-1$
      join = relation.getComplexJoin();
    }
  } else if ( relation.getFromTable() != null && relation.getToTable() != null && relation.getFromColumn() != null
      && relation.getToColumn() != null ) {
    // Left side
    String leftTableAlias = null;
    if ( tableAliases != null ) {
      leftTableAlias = tableAliases.get( relation.getFromColumn().getLogicalTable() );
    } else {
      leftTableAlias = relation.getFromColumn().getLogicalTable().getId();
    }

    join = databaseMeta.quoteField( leftTableAlias );
    join += "."; //$NON-NLS-1$
    join +=
        databaseMeta.quoteField( (String) relation.getFromColumn().getProperty( SqlPhysicalColumn.TARGET_COLUMN ) );

    // Equals
    join += " = "; //$NON-NLS-1$

    // Right side
    String rightTableAlias = null;
    if ( tableAliases != null ) {
      rightTableAlias = tableAliases.get( relation.getToColumn().getLogicalTable() );
    } else {
      rightTableAlias = relation.getToColumn().getLogicalTable().getId();
    }

    join += databaseMeta.quoteField( rightTableAlias );
    join += "."; //$NON-NLS-1$
    join += databaseMeta.quoteField( (String) relation.getToColumn().getProperty( SqlPhysicalColumn.TARGET_COLUMN ) );
  } else {
    throw new PentahoMetadataException( Messages.getErrorString(
        "SqlGenerator.ERROR_0003_INVALID_RELATION", relation.toString() ) ); //$NON-NLS-1$
  }

  return join;
}
 

/**
 * Pre-load the cache by reading the whole dimension table from disk...
 *
 * @throws KettleException in case there is a database or cache problem.
 */
private void preloadCache() throws KettleException {
  try {
    DatabaseMeta databaseMeta = meta.getDatabaseMeta();

    // tk, version, from, to, natural keys, retrieval fields...
    //
    String sql = "SELECT " + databaseMeta.quoteField( meta.getKeyField() );
    // sql+=", "+databaseMeta.quoteField(meta.getVersionField());
    for ( int i = 0; i < meta.getKeyLookup().length; i++ ) {
      sql += ", " + meta.getKeyLookup()[ i ]; // the natural key field in the table
    }
    for ( int i = 0; i < meta.getFieldLookup().length; i++ ) {
      sql += ", " + meta.getFieldLookup()[ i ]; // the extra fields to retrieve...
    }
    sql += ", " + databaseMeta.quoteField( meta.getDateFrom() ); // extra info in cache
    sql += ", " + databaseMeta.quoteField( meta.getDateTo() ); // extra info in cache

    sql += " FROM " + data.schemaTable;
    logDetailed( "Pre-loading cache by reading from database with: " + Const.CR + sql + Const.CR );

    List<Object[]> rows = data.db.getRows( sql, -1 );
    RowMetaInterface rowMeta = data.db.getReturnRowMeta();

    data.preloadKeyIndexes = new int[ meta.getKeyLookup().length ];
    for ( int i = 0; i < data.preloadKeyIndexes.length; i++ ) {
      data.preloadKeyIndexes[ i ] = rowMeta.indexOfValue( meta.getKeyLookup()[ i ] ); // the field in the table
    }
    data.preloadFromDateIndex = rowMeta.indexOfValue( meta.getDateFrom() );
    data.preloadToDateIndex = rowMeta.indexOfValue( meta.getDateTo() );

    data.preloadCache =
      new DimensionCache( rowMeta, data.preloadKeyIndexes, data.preloadFromDateIndex, data.preloadToDateIndex );
    data.preloadCache.setRowCache( rows );

    logDetailed( "Sorting the cache rows..." );
    data.preloadCache.sortRows();
    logDetailed( "Sorting of cached rows finished." );

    // Also see what indexes to take to populate the lookup row...
    // We only ever compare indexes and the lookup date in the cache, the rest is not needed...
    //
    data.preloadIndexes = new ArrayList<Integer>();
    for ( int i = 0; i < meta.getKeyStream().length; i++ ) {
      int index = data.inputRowMeta.indexOfValue( meta.getKeyStream()[ i ] );
      if ( index < 0 ) {
        // Just to be safe...
        //
        throw new KettleStepException( BaseMessages.getString(
          PKG, "DimensionLookup.Exception.KeyFieldNotFound", meta.getFieldStream()[ i ] ) );
      }
      data.preloadIndexes.add( index );
    }

    // This is all for now...
  } catch ( Exception e ) {
    throw new KettleException( "Error encountered during cache pre-load", e );
  }
}
 

/**
 * CombinationLookup table: dimension table keys[]: which dim-fields do we use to look up key? retval: name of the key
 * to return
 */
public void setCombiLookup( RowMetaInterface inputRowMeta ) throws KettleDatabaseException {
  DatabaseMeta databaseMeta = meta.getDatabaseMeta();

  String sql = "";
  boolean comma;
  data.lookupRowMeta = new RowMeta();

  /*
   * SELECT <retval> FROM <table> WHERE ( ( <key1> = ? ) OR ( <key1> IS NULL AND ? IS NULL ) ) AND ( ( <key2> = ? ) OR
   * ( <key1> IS NULL AND ? IS NULL ) ) ... ;
   *
   * OR
   *
   * SELECT <retval> FROM <table> WHERE <crcfield> = ? AND ( ( <key1> = ? ) OR ( <key1> IS NULL AND ? IS NULL ) ) AND
   * ( ( <key2> = ? ) OR ( <key1> IS NULL AND ? IS NULL ) ) ... ;
   */

  sql += "SELECT " + databaseMeta.quoteField( meta.getTechnicalKeyField() ) + Const.CR;
  sql += "FROM " + data.schemaTable + Const.CR;
  sql += "WHERE ";
  comma = false;

  if ( meta.useHash() ) {
    sql += databaseMeta.quoteField( meta.getHashField() ) + " = ? " + Const.CR;
    comma = true;
    data.lookupRowMeta.addValueMeta( new ValueMetaInteger( meta.getHashField() ) );
  } else {
    sql += "( ( ";
  }

  for ( int i = 0; i < meta.getKeyLookup().length; i++ ) {
    if ( comma ) {
      sql += " AND ( ( ";
    } else {
      comma = true;
    }
    sql +=
      databaseMeta.quoteField( meta.getKeyLookup()[ i ] )
        + " = ? ) OR ( " + databaseMeta.quoteField( meta.getKeyLookup()[ i ] );
    data.lookupRowMeta.addValueMeta( inputRowMeta.getValueMeta( data.keynrs[ i ] ) );

    sql += " IS NULL AND ";
    if ( databaseMeta.requiresCastToVariousForIsNull() ) {
      sql += "CAST(? AS VARCHAR(256)) IS NULL";
    } else {
      sql += "? IS NULL";
    }
    // Add the ValueMeta for the null check, BUT cloning needed.
    // Otherwise the field gets renamed and gives problems when referenced by previous steps.
    data.lookupRowMeta.addValueMeta( inputRowMeta.getValueMeta( data.keynrs[ i ] ).clone() );

    sql += " ) )";
    sql += Const.CR;
  }

  try {
    if ( log.isDebug() ) {
      logDebug( "preparing combi-lookup statement:" + Const.CR + sql );
    }
    data.prepStatementLookup = data.db.getConnection().prepareStatement( databaseMeta.stripCR( sql ) );
    if ( databaseMeta.supportsSetMaxRows() ) {
      data.prepStatementLookup.setMaxRows( 1 ); // alywas get only 1 line back!
    }
  } catch ( SQLException ex ) {
    throw new KettleDatabaseException( "Unable to prepare combi-lookup statement", ex );
  }
}
 

public void setLookup( RowMetaInterface rowMeta ) throws KettleDatabaseException {
  data.lookupParameterRowMeta = new RowMeta();
  data.lookupReturnRowMeta = new RowMeta();

  DatabaseMeta databaseMeta = meta.getDatabaseMeta();

  String sql = "SELECT ";

  for ( int i = 0; i < meta.getUpdateLookup().length; i++ ) {
    if ( i != 0 ) {
      sql += ", ";
    }
    sql += databaseMeta.quoteField( meta.getUpdateLookup()[i] );
    data.lookupReturnRowMeta.addValueMeta( rowMeta.searchValueMeta( meta.getUpdateStream()[i] ).clone() );
  }

  sql += " FROM " + data.schemaTable + " WHERE ";

  for ( int i = 0; i < meta.getKeyLookup().length; i++ ) {
    if ( i != 0 ) {
      sql += " AND ";
    }

    sql += " ( ( ";

    sql += databaseMeta.quoteField( meta.getKeyLookup()[i] );
    if ( "BETWEEN".equalsIgnoreCase( meta.getKeyCondition()[i] ) ) {
      sql += " BETWEEN ? AND ? ";
      data.lookupParameterRowMeta.addValueMeta( rowMeta.searchValueMeta( meta.getKeyStream()[i] ) );
      data.lookupParameterRowMeta.addValueMeta( rowMeta.searchValueMeta( meta.getKeyStream2()[i] ) );
    } else {
      if ( "IS NULL".equalsIgnoreCase( meta.getKeyCondition()[i] )
        || "IS NOT NULL".equalsIgnoreCase( meta.getKeyCondition()[i] ) ) {
        sql += " " + meta.getKeyCondition()[i] + " ";
      } else if ( "= ~NULL".equalsIgnoreCase( meta.getKeyCondition()[i] ) ) {

        sql += " IS NULL AND ";

        if ( databaseMeta.requiresCastToVariousForIsNull() ) {
          sql += " CAST(? AS VARCHAR(256)) IS NULL ";
        } else {
          sql += " ? IS NULL ";
        }
        // null check
        data.lookupParameterRowMeta.addValueMeta( rowMeta.searchValueMeta( meta.getKeyStream()[i] ) );
        sql += " ) OR ( " + databaseMeta.quoteField( meta.getKeyLookup()[i] ) + " = ? ";
        // equality check, cloning so auto-rename because of adding same fieldname does not cause problems
        data.lookupParameterRowMeta.addValueMeta( rowMeta.searchValueMeta( meta.getKeyStream()[i] ).clone() );

      } else {
        sql += " " + meta.getKeyCondition()[i] + " ? ";
        data.lookupParameterRowMeta.addValueMeta( rowMeta.searchValueMeta( meta.getKeyStream()[i] ) );
      }
    }
    sql += " ) ) ";
  }

  try {
    if ( log.isDetailed() ) {
      logDetailed( "Setting preparedStatement to [" + sql + "]" );
    }
    data.prepStatementLookup = data.db.getConnection().prepareStatement( databaseMeta.stripCR( sql ) );
  } catch ( SQLException ex ) {
    throw new KettleDatabaseException( "Unable to prepare statement for SQL statement [" + sql + "]", ex );
  }
}
 

public void prepareUpdate( RowMetaInterface rowMeta ) throws KettleDatabaseException {
  DatabaseMeta databaseMeta = meta.getDatabaseMeta();
  data.updateParameterRowMeta = new RowMeta();

  String sql = "UPDATE " + data.schemaTable + Const.CR;
  sql += "SET ";

  boolean comma = false;

  for ( int i = 0; i < meta.getUpdateLookup().length; i++ ) {
    if ( meta.getUpdate()[i].booleanValue() ) {
      if ( comma ) {
        sql += ",   ";
      } else {
        comma = true;
      }

      sql += databaseMeta.quoteField( meta.getUpdateLookup()[i] );
      sql += " = ?" + Const.CR;
      data.updateParameterRowMeta.addValueMeta( rowMeta.searchValueMeta( meta.getUpdateStream()[i] ).clone() );
    }
  }

  sql += "WHERE ";

  for ( int i = 0; i < meta.getKeyLookup().length; i++ ) {
    if ( i != 0 ) {
      sql += "AND   ";
    }
    sql += " ( ( ";
    sql += databaseMeta.quoteField( meta.getKeyLookup()[i] );
    if ( "BETWEEN".equalsIgnoreCase( meta.getKeyCondition()[i] ) ) {
      sql += " BETWEEN ? AND ? ";
      data.updateParameterRowMeta.addValueMeta( rowMeta.searchValueMeta( meta.getKeyStream()[i] ) );
      data.updateParameterRowMeta.addValueMeta( rowMeta.searchValueMeta( meta.getKeyStream2()[i] ) );
    } else if ( "IS NULL".equalsIgnoreCase( meta.getKeyCondition()[i] )
      || "IS NOT NULL".equalsIgnoreCase( meta.getKeyCondition()[i] ) ) {
      sql += " " + meta.getKeyCondition()[i] + " ";
    } else if ( "= ~NULL".equalsIgnoreCase( meta.getKeyCondition()[i] ) ) {

      sql += " IS NULL AND ";

      if ( databaseMeta.requiresCastToVariousForIsNull() ) {
        sql += "CAST(? AS VARCHAR(256)) IS NULL";
      } else {
        sql += "? IS NULL";
      }
      // null check
      data.updateParameterRowMeta.addValueMeta( rowMeta.searchValueMeta( meta.getKeyStream()[i] ) );
      sql += " ) OR ( " + databaseMeta.quoteField( meta.getKeyLookup()[i] ) + " = ?";
      // equality check, cloning so auto-rename because of adding same fieldname does not cause problems
      data.updateParameterRowMeta.addValueMeta( rowMeta.searchValueMeta( meta.getKeyStream()[i] ).clone() );

    } else {
      sql += " " + meta.getKeyCondition()[i] + " ? ";
      data.updateParameterRowMeta.addValueMeta( rowMeta.searchValueMeta( meta.getKeyStream()[i] ).clone() );
    }
    sql += " ) ) ";
  }

  try {
    if ( log.isDetailed() ) {
      logDetailed( "Setting update preparedStatement to [" + sql + "]" );
    }
    data.prepStatementUpdate = data.db.getConnection().prepareStatement( databaseMeta.stripCR( sql ) );
  } catch ( SQLException ex ) {
    throw new KettleDatabaseException( "Unable to prepare statement for SQL statement [" + sql + "]", ex );
  }
}
 

public static SQLAndAliasedTables getSelectionSQL( LogicalModel LogicalModel, AliasedSelection selection,
                                                   DatabaseMeta databaseMeta, String locale ) {
  String columnStr = (String) selection.getLogicalColumn().getProperty( SqlPhysicalColumn.TARGET_COLUMN );
  if ( selection.getLogicalColumn().getProperty( SqlPhysicalColumn.TARGET_COLUMN_TYPE )
    == TargetColumnType.OPEN_FORMULA ) {
    // convert to sql using libformula subsystem
    try {
      // we'll need to pass in some context to PMSFormula so it can resolve aliases if necessary
      AliasAwareSqlOpenFormula formula =
        new AliasAwareSqlOpenFormula( LogicalModel, selection.getLogicalColumn().getLogicalTable(), databaseMeta,
          columnStr, selection.getAlias() );
      formula.parseAndValidate();
      // return formula.generateSQL(locale);
      return new SQLAndAliasedTables( formula.generateSQL( locale ), formula.getUsedAliasedTables() );
    } catch ( PentahoMetadataException e ) {
      // this is for backwards compatibility.
      // eventually throw any errors
      throw new RuntimeException( Messages.getErrorString(
        "SqlGenerator.ERROR_0001_FAILED_TO_PARSE_FORMULA", columnStr ) ); //$NON-NLS-1$
    }
  } else {
    String tableColumn = ""; //$NON-NLS-1$
    String tblName = selection.getLogicalColumn().getLogicalTable().getId();
    if ( !selection.getAlias().equals( DEFAULT_ALIAS ) ) {
      tblName += "_" + selection.getAlias(); //$NON-NLS-1$
    }
    tableColumn += databaseMeta.quoteField( tblName );
    tableColumn += "."; //$NON-NLS-1$

    // TODO: WPG: instead of using formula, shouldn't we use the physical column's name?
    tableColumn += databaseMeta.quoteField( columnStr );

    // For the having clause, for example: HAVING sum(turnover) > 100
    if ( selection.hasAggregate() ) {
      // return getFunctionExpression(selection.getLogicalColumn(), tableColumn, databaseMeta);
      return new SQLAndAliasedTables( getFunctionExpression( selection, tableColumn, databaseMeta ),
        new AliasedPathLogicalTable( tblName, selection.getLogicalColumn().getLogicalTable() ) );
    } else {
      return new SQLAndAliasedTables( tableColumn, new AliasedPathLogicalTable( tblName, selection.getLogicalColumn()
        .getLogicalTable() ) );
    }
  }
}
 

public String getJoin( BusinessModel businessModel, RelationshipMeta relation,
                       Map<BusinessTable, String> tableAliases, DatabaseMeta databaseMeta, String locale ) {
  String join = ""; //$NON-NLS-1$
  if ( relation.isComplex() ) {
    try {
      // parse join as MQL
      PMSFormula formula = new PMSFormula( businessModel, databaseMeta, relation, tableAliases );
      formula.parseAndValidate();
      join = formula.generateSQL( locale );
    } catch ( PentahoMetadataException e ) {
      // backward compatibility, deprecate
      // FIXME: we need to get rid of this and just throw an exception
      logger.error( Messages.getErrorString(
        "MQLQueryImpl.ERROR_0017_FAILED_TO_PARSE_COMPLEX_JOIN", relation.getComplexJoin() ), e ); //$NON-NLS-1$
      join = relation.getComplexJoin();
    }
  } else if ( relation.getTableFrom() != null && relation.getTableTo() != null && relation.getFieldFrom() != null
    && relation.getFieldTo() != null ) {

    // Left side
    String leftTableAlias = null;
    if ( tableAliases != null ) {
      leftTableAlias = tableAliases.get( relation.getFieldFrom().getBusinessTable() );
    } else {
      leftTableAlias = relation.getFieldFrom().getBusinessTable().getId();
    }

    join = databaseMeta.quoteField( leftTableAlias );
    join += "."; //$NON-NLS-1$
    join += databaseMeta.quoteField( relation.getFieldFrom().getFormula() );

    // Equals
    join += " = "; //$NON-NLS-1$

    // Right side
    String rightTableAlias = null;
    if ( tableAliases != null ) {
      rightTableAlias = tableAliases.get( relation.getFieldTo().getBusinessTable() );
    } else {
      rightTableAlias = relation.getFieldTo().getBusinessTable().getId();
    }

    join += databaseMeta.quoteField( rightTableAlias );
    join += "."; //$NON-NLS-1$
    join += databaseMeta.quoteField( relation.getFieldTo().getFormula() );
  } else {
    logger.error(
      Messages.getErrorString( "SQLGenerator.ERROR_0001_INVALID_RELATION", relation.toString() ) ); //$NON-NLS-1$
  }

  return join;
}
 

public void setLookup( RowMetaInterface rowMeta ) throws KettleDatabaseException {
  data.lookupParameterRowMeta = new RowMeta();
  data.lookupReturnRowMeta = new RowMeta();

  DatabaseMeta databaseMeta = meta.getDatabaseMeta();

  String sql = "SELECT ";

  for ( int i = 0; i < meta.getUpdateLookup().length; i++ ) {
    if ( i != 0 ) {
      sql += ", ";
    }
    sql += databaseMeta.quoteField( meta.getUpdateLookup()[i] );
    data.lookupReturnRowMeta.addValueMeta( rowMeta.searchValueMeta( meta.getUpdateStream()[i] ) );
  }

  sql += " FROM " + data.schemaTable + " WHERE ";

  for ( int i = 0; i < meta.getKeyLookup().length; i++ ) {
    if ( i != 0 ) {
      sql += " AND ";
    }

    sql += " ( ( ";

    sql += databaseMeta.quoteField( meta.getKeyLookup()[i] );
    if ( "BETWEEN".equalsIgnoreCase( meta.getKeyCondition()[i] ) ) {
      sql += " BETWEEN ? AND ? ";
      data.lookupParameterRowMeta.addValueMeta( rowMeta.searchValueMeta( meta.getKeyStream()[i] ) );
      data.lookupParameterRowMeta.addValueMeta( rowMeta.searchValueMeta( meta.getKeyStream2()[i] ) );
    } else {
      if ( "IS NULL".equalsIgnoreCase( meta.getKeyCondition()[i] )
        || "IS NOT NULL".equalsIgnoreCase( meta.getKeyCondition()[i] ) ) {
        sql += " " + meta.getKeyCondition()[i] + " ";
      } else if ( "= ~NULL".equalsIgnoreCase( meta.getKeyCondition()[i] ) ) {

        sql += " IS NULL AND ";

        if ( databaseMeta.requiresCastToVariousForIsNull() ) {
          sql += "CAST(? AS VARCHAR(256)) IS NULL";
        } else {
          sql += "? IS NULL";
        }
        // null check
        data.lookupParameterRowMeta.addValueMeta( rowMeta.searchValueMeta( meta.getKeyStream()[i] ) );
        sql += " ) OR ( " + databaseMeta.quoteField( meta.getKeyLookup()[i] ) + " = ?";
        // equality check, cloning so auto-rename because of adding same fieldname does not cause problems
        data.lookupParameterRowMeta.addValueMeta( rowMeta.searchValueMeta( meta.getKeyStream()[i] ).clone() );

      } else {
        sql += " " + meta.getKeyCondition()[i] + " ? ";
        data.lookupParameterRowMeta.addValueMeta( rowMeta.searchValueMeta( meta.getKeyStream()[i] ) );
      }
    }
    sql += " ) ) ";
  }

  try {
    if ( log.isDetailed() ) {
      logDetailed( "Setting preparedStatement to [" + sql + "]" );
    }
    data.prepStatementLookup = data.db.getConnection().prepareStatement( databaseMeta.stripCR( sql ) );
  } catch ( SQLException ex ) {
    throw new KettleDatabaseException( "Unable to prepare statement for SQL statement [" + sql + "]", ex );
  }

}
 

public void prepareUpdate( RowMetaInterface rowMeta ) throws KettleDatabaseException {
  DatabaseMeta databaseMeta = meta.getDatabaseMeta();
  data.updateParameterRowMeta = new RowMeta();

  String sql = "UPDATE " + data.schemaTable + Const.CR;
  sql += "SET ";

  for ( int i = 0; i < meta.getUpdateLookup().length; i++ ) {
    if ( i != 0 ) {
      sql += ",   ";
    }
    sql += databaseMeta.quoteField( meta.getUpdateLookup()[i] );
    sql += " = ?" + Const.CR;
    data.updateParameterRowMeta.addValueMeta( rowMeta.searchValueMeta( meta.getUpdateStream()[i] ) );
  }

  sql += "WHERE ";

  for ( int i = 0; i < meta.getKeyLookup().length; i++ ) {
    if ( i != 0 ) {
      sql += "AND   ";
    }
    sql += " ( ( ";
    sql += databaseMeta.quoteField( meta.getKeyLookup()[i] );
    if ( "BETWEEN".equalsIgnoreCase( meta.getKeyCondition()[i] ) ) {
      sql += " BETWEEN ? AND ? ";
      data.updateParameterRowMeta.addValueMeta( rowMeta.searchValueMeta( meta.getKeyStream()[i] ) );
      data.updateParameterRowMeta.addValueMeta( rowMeta.searchValueMeta( meta.getKeyStream2()[i] ) );
    } else if ( "IS NULL".equalsIgnoreCase( meta.getKeyCondition()[i] )
      || "IS NOT NULL".equalsIgnoreCase( meta.getKeyCondition()[i] ) ) {
      sql += " " + meta.getKeyCondition()[i] + " ";
    } else if ( "= ~NULL".equalsIgnoreCase( meta.getKeyCondition()[i] ) ) {

      sql += " IS NULL AND ";

      if ( databaseMeta.requiresCastToVariousForIsNull() ) {
        sql += "CAST(? AS VARCHAR(256)) IS NULL";
      } else {
        sql += "? IS NULL";
      }
      // null check
      data.updateParameterRowMeta.addValueMeta( rowMeta.searchValueMeta( meta.getKeyStream()[i] ) );
      sql += " ) OR ( " + databaseMeta.quoteField( meta.getKeyLookup()[i] ) + " = ?";
      // equality check, cloning so auto-rename because of adding same fieldname does not cause problems
      data.updateParameterRowMeta.addValueMeta( rowMeta.searchValueMeta( meta.getKeyStream()[i] ).clone() );

    } else {
      sql += " " + meta.getKeyCondition()[i] + " ? ";
      data.updateParameterRowMeta.addValueMeta( rowMeta.searchValueMeta( meta.getKeyStream()[i] ) );
    }
    sql += " ) ) ";
  }
  try {
    if ( log.isDetailed() ) {
      logDetailed( "Setting update preparedStatement to [" + sql + "]" );
    }
    data.prepStatementUpdate = data.db.getConnection().prepareStatement( databaseMeta.stripCR( sql ) );
  } catch ( SQLException ex ) {
    throw new KettleDatabaseException( "Unable to prepare statement for SQL statement [" + sql + "]", ex );
  }
}
 

public static SQLAndTables getBusinessColumnSQL( BusinessModel businessModel, Selection column,
                                                 Map<BusinessTable, String> tableAliases, DatabaseMeta databaseMeta,
                                                 String locale, boolean isComplexJoin ) {
  if ( column.getBusinessColumn().isExact() ) {
    // convert to sql using libformula subsystem
    try {
      // we'll need to pass in some context to PMSFormula so it can resolve aliases if necessary
      PMSFormula formula =
        new PMSFormula( businessModel, column.getBusinessColumn().getBusinessTable(), databaseMeta, column
          .getBusinessColumn().getFormula(), tableAliases );
      formula.parseAndValidate();

      String formulaSql = formula.generateSQL( locale );

      // check for old style, where function is hardcoded in the model.
      if ( column.hasAggregate() && !hasAggregateDefinedAlready( formulaSql, databaseMeta ) ) {
        formulaSql = getFunctionExpression( column, formulaSql, databaseMeta );
      }

      return new SQLAndTables( formulaSql, formula.getBusinessTables(), formula.getBusinessColumns() );
    } catch ( PentahoMetadataException e ) {
      // this is for backwards compatibility.
      // eventually throw any errors
      logger.error( Messages.getErrorString(
        "BusinessColumn.ERROR_0001_FAILED_TO_PARSE_FORMULA", column.getBusinessColumn().getFormula() ),
        e ); //$NON-NLS-1$

      // Report just this table and column as being used along with the formula.
      //
      return new SQLAndTables( column.getBusinessColumn().getFormula(),
        column.getBusinessColumn().getBusinessTable(), column );
    }
  } else {
    String tableColumn = ""; //$NON-NLS-1$

    // this step is required because this method is called in two contexts. The first
    // call determines all the tables involved, making it impossible to guarantee
    // unique aliases.

    String tableAlias = null;
    if ( tableAliases != null ) {
      tableAlias = tableAliases.get( column.getBusinessColumn().getBusinessTable() );
    } else {
      tableAlias = column.getBusinessColumn().getBusinessTable().getId();
    }
    tableColumn += databaseMeta.quoteField( tableAlias );
    tableColumn += "."; //$NON-NLS-1$

    // TODO: WPG: instead of using formula, shouldn't we use the physical column's name?
    tableColumn += databaseMeta.quoteField( column.getBusinessColumn().getFormula() );

    // complex join allowed only in where clause
    if ( column.hasAggregate() && !isComplexJoin ) {
      // For the having clause, for example: HAVING sum(turnover) > 100
      return new SQLAndTables( getFunctionExpression( column, tableColumn, databaseMeta ), column.getBusinessColumn()
        .getBusinessTable(), column );
    } else {
      return new SQLAndTables( tableColumn, column.getBusinessColumn().getBusinessTable(), column );
    }
  }
}
 

public String getUpdateStatement( RowMetaInterface rowMeta ) throws KettleDatabaseException {
  DatabaseMeta databaseMeta = meta.getDatabaseMeta();
  data.updateParameterRowMeta = new RowMeta();

  String sql = "UPDATE " + data.realSchemaTable + Const.CR;
  sql += "SET ";

  boolean comma = false;

  for ( int i = 0; i < meta.getUpdateLookup().length; i++ ) {
    if ( meta.getUpdate()[i].booleanValue() ) {
      if ( comma ) {
        sql += ",   ";
      } else {
        comma = true;
      }

      sql += databaseMeta.quoteField( meta.getUpdateLookup()[i] );
      sql += " = ?" + Const.CR;
      data.updateParameterRowMeta.addValueMeta( rowMeta.searchValueMeta( meta.getUpdateStream()[i] ).clone() );
    }
  }

  sql += "WHERE ";

  for ( int i = 0; i < meta.getKeyLookup().length; i++ ) {
    if ( i != 0 ) {
      sql += "AND   ";
    }
    sql += databaseMeta.quoteField( meta.getKeyLookup()[i] );
    if ( "BETWEEN".equalsIgnoreCase( meta.getKeyCondition()[i] ) ) {
      sql += " BETWEEN ? AND ? ";
      data.updateParameterRowMeta.addValueMeta( rowMeta.searchValueMeta( meta.getKeyStream()[i] ) );
      data.updateParameterRowMeta.addValueMeta( rowMeta.searchValueMeta( meta.getKeyStream2()[i] ) );
    } else if ( "IS NULL".equalsIgnoreCase( meta.getKeyCondition()[i] ) || "IS NOT NULL".equalsIgnoreCase( meta
        .getKeyCondition()[i] ) ) {
      sql += " " + meta.getKeyCondition()[i] + " ";
    } else {
      sql += " " + meta.getKeyCondition()[i] + " ? ";
      data.updateParameterRowMeta.addValueMeta( rowMeta.searchValueMeta( meta.getKeyStream()[i] ).clone() );
    }
  }
  return sql;
}
 

public static SQLAndAliasedTables getSelectionSQL( BusinessModel businessModel, AliasedSelection selection,
                                                   DatabaseMeta databaseMeta, String locale ) {
  if ( selection.getBusinessColumn().isExact() ) {
    // convert to sql using libformula subsystem
    try {
      // we'll need to pass in some context to PMSFormula so it can resolve aliases if necessary
      AliasAwarePMSFormula formula =
        new AliasAwarePMSFormula( businessModel, selection.getBusinessColumn().getBusinessTable(), databaseMeta,
          selection.getBusinessColumn().getFormula(), selection.getAlias() );
      formula.parseAndValidate();
      // return formula.generateSQL(locale);
      return new SQLAndAliasedTables( formula.generateSQL( locale ), formula.getUsedAliasedTables() );
    } catch ( PentahoMetadataException e ) {
      // this is for backwards compatibility.
      // eventually throw any errors
      throw new RuntimeException( Messages.getErrorString(
        "BusinessColumn.ERROR_0001_FAILED_TO_PARSE_FORMULA",
        selection.getBusinessColumn().getFormula() ) ); //$NON-NLS-1$
    }
  } else {
    String tableColumn = ""; //$NON-NLS-1$
    String tblName = selection.getBusinessColumn().getBusinessTable().getId();
    if ( !selection.getAlias().equals( DEFAULT_ALIAS ) ) {
      tblName += "_" + selection.getAlias(); //$NON-NLS-1$
    }
    tableColumn += databaseMeta.quoteField( tblName );
    tableColumn += "."; //$NON-NLS-1$

    // TODO: WPG: instead of using formula, shouldn't we use the physical column's name?
    tableColumn += databaseMeta.quoteField( selection.getBusinessColumn().getFormula() );

    // For the having clause, for example: HAVING sum(turnover) > 100
    if ( selection.hasAggregate() ) {
      // return getFunctionExpression(selection.getBusinessColumn(), tableColumn, databaseMeta);
      return new SQLAndAliasedTables( getFunctionExpression( selection, tableColumn, databaseMeta ),
        new AliasedPathBusinessTable( tblName, selection.getBusinessColumn().getBusinessTable() ) );
    } else {
      return new SQLAndAliasedTables( tableColumn, new AliasedPathBusinessTable( tblName, selection
        .getBusinessColumn().getBusinessTable() ) );
    }
  }
}