InsertUpdate.java

/*! ******************************************************************************
 *
 * Pentaho Data Integration
 *
 * Copyright (C) 2002-2013 by Pentaho : http://www.pentaho.com
 *
 *******************************************************************************
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with
 * the License. You may obtain a copy of the License at
 *
 *    http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 *
 ******************************************************************************/

package org.pentaho.di.trans.steps.insertupdate;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang.ArrayUtils;
import org.pentaho.di.core.Const;
import org.pentaho.di.core.database.Database;
import org.pentaho.di.core.database.DatabaseMeta;
import org.pentaho.di.core.exception.KettleDatabaseException;
import org.pentaho.di.core.exception.KettleException;
import org.pentaho.di.core.exception.KettleStepException;
import org.pentaho.di.core.row.RowMeta;
import org.pentaho.di.core.row.RowMetaInterface;
import org.pentaho.di.core.row.ValueMetaInterface;
import org.pentaho.di.i18n.BaseMessages;
import org.pentaho.di.trans.Trans;
import org.pentaho.di.trans.TransMeta;
import org.pentaho.di.trans.step.BaseStep;
import org.pentaho.di.trans.step.StepDataInterface;
import org.pentaho.di.trans.step.StepInterface;
import org.pentaho.di.trans.step.StepMeta;
import org.pentaho.di.trans.step.StepMetaInterface;

/**
 * Performs a lookup in a database table. If the key doesn't exist it inserts values into the table, otherwise it
 * performs an update of the changed values. If nothing changed, do nothing.
 *
 * @author Matt
 * @since 26-apr-2003
 */
public class InsertUpdate extends BaseStep implements StepInterface {
  private static Class<?> PKG = InsertUpdateMeta.class; // for i18n purposes, needed by Translator2!!

  private InsertUpdateMeta meta;
  private InsertUpdateData data;

  public InsertUpdate( StepMeta stepMeta, StepDataInterface stepDataInterface, int copyNr, TransMeta transMeta,
    Trans trans ) {
    super( stepMeta, stepDataInterface, copyNr, transMeta, trans );
  }

  private synchronized void lookupValues( RowMetaInterface rowMeta, Object[] row ) throws KettleException {
    // OK, now do the lookup.
    // We need the lookupvalues for that.
    Object[] lookupRow = new Object[data.lookupParameterRowMeta.size()];
    int lookupIndex = 0;

    for ( int i = 0; i < data.keynrs.length; i++ ) {
      if ( data.keynrs[i] >= 0 ) {
        lookupRow[lookupIndex] = row[data.keynrs[i]];
        lookupIndex++;
      }
      if ( data.keynrs2[i] >= 0 ) {
        lookupRow[lookupIndex] = row[data.keynrs2[i]];
        lookupIndex++;
      }
    }

    data.db.setValues( data.lookupParameterRowMeta, lookupRow, data.prepStatementLookup );

    if ( log.isDebug() ) {
      logDebug( BaseMessages.getString( PKG, "InsertUpdate.Log.ValuesSetForLookup" )
        + data.lookupParameterRowMeta.getString( lookupRow ) );
    }
    Object[] add = data.db.getLookup( data.prepStatementLookup );
    incrementLinesInput();

    if ( add == null ) {
      /*
       * nothing was found:
       *
       * INSERT ROW
       */
      if ( log.isRowLevel() ) {
        logRowlevel( BaseMessages.getString( PKG, "InsertUpdate.InsertRow" ) + rowMeta.getString( row ) );
      }

      // The values to insert are those in the update section (all fields should be specified)
      // For the others, we have no definite mapping!
      //
      Object[] insertRow = new Object[data.valuenrs.length];
      for ( int i = 0; i < data.valuenrs.length; i++ ) {
        insertRow[i] = row[data.valuenrs[i]];
      }

      // Set the values on the prepared statement...
      data.db.setValuesInsert( data.insertRowMeta, insertRow );

      // Insert the row
      data.db.insertRow();

      incrementLinesOutput();
    } else {
      if ( !meta.isUpdateBypassed() ) {
        if ( log.isRowLevel() ) {
          logRowlevel( BaseMessages.getString( PKG, "InsertUpdate.Log.FoundRowForUpdate" )
            + rowMeta.getString( row ) );
        }

        /*
         * Row was found:
         *
         * UPDATE row or do nothing?
         */
        boolean update = false;
        for ( int i = 0; i < data.valuenrs.length; i++ ) {
          if ( meta.getUpdate()[i].booleanValue() ) {
            ValueMetaInterface valueMeta = rowMeta.getValueMeta( data.valuenrs[i] );
            ValueMetaInterface retMeta = data.db.getReturnRowMeta().getValueMeta( i );

            Object rowvalue = row[data.valuenrs[i]];
            Object retvalue = add[i];

            if ( retMeta.compare( retvalue, valueMeta, rowvalue ) != 0 ) {
              update = true;
            }
          }
        }
        if ( update ) {
          // Create the update row...
          Object[] updateRow = new Object[data.updateParameterRowMeta.size()];
          int j = 0;
          for ( int i = 0; i < data.valuenrs.length; i++ ) {
            if ( meta.getUpdate()[i].booleanValue() ) {
              updateRow[j] = row[data.valuenrs[i]]; // the setters
              j++;
            }
          }
          // add the where clause parameters, they are exactly the same for lookup and update
          for ( int i = 0; i < lookupRow.length; i++ ) {
            updateRow[j + i] = lookupRow[i];
          }

          if ( log.isRowLevel() ) {
            logRowlevel( BaseMessages.getString( PKG, "InsertUpdate.Log.UpdateRow" )
              + data.lookupParameterRowMeta.getString( lookupRow ) );
          }
          data.db.setValues( data.updateParameterRowMeta, updateRow, data.prepStatementUpdate );
          data.db.insertRow( data.prepStatementUpdate );
          incrementLinesUpdated();
        } else {
          incrementLinesSkipped();
        }
      } else {
        if ( log.isRowLevel() ) {
          logRowlevel( BaseMessages.getString( PKG, "InsertUpdate.Log.UpdateBypassed" ) + rowMeta.getString( row ) );
        }
        incrementLinesSkipped();
      }
    }
  }

  public boolean processRow( StepMetaInterface smi, StepDataInterface sdi ) throws KettleException {
    meta = (InsertUpdateMeta) smi;
    data = (InsertUpdateData) sdi;

    boolean sendToErrorRow = false;
    String errorMessage = null;

    Object[] r = getRow(); // Get row from input rowset & set row busy!
    if ( r == null ) {
      // no more input to be expected...

      setOutputDone();
      return false;
    }

    if ( first ) {
      first = false;

      data.outputRowMeta = getInputRowMeta().clone();
      meta.getFields( data.outputRowMeta, getStepname(), null, null, this, repository, metaStore );

      data.schemaTable =
        meta.getDatabaseMeta().getQuotedSchemaTableCombination(
          environmentSubstitute( meta.getSchemaName() ), environmentSubstitute( meta.getTableName() ) );

      // lookup the values!
      if ( log.isDebug() ) {
        logDebug( BaseMessages.getString( PKG, "InsertUpdate.Log.CheckingRow" ) + getInputRowMeta().getString( r ) );
      }

      ArrayList<Integer> keynrs = new ArrayList<Integer>( meta.getKeyStream().length );
      ArrayList<Integer> keynrs2 = new ArrayList<Integer>( meta.getKeyStream().length );

      for ( int i = 0; i < meta.getKeyStream().length; i++ ) {
        int keynr = getInputRowMeta().indexOfValue( meta.getKeyStream()[i] );

        if ( keynr < 0 && // couldn't find field!
          !"IS NULL".equalsIgnoreCase( meta.getKeyCondition()[i] ) && // No field needed!
          !"IS NOT NULL".equalsIgnoreCase( meta.getKeyCondition()[i] ) // No field needed!
        ) {
          throw new KettleStepException( BaseMessages.getString( PKG, "InsertUpdate.Exception.FieldRequired", meta
            .getKeyStream()[i] ) );
        }
        keynrs.add( keynr );

        // this operator needs two bindings
        if ( "= ~NULL".equalsIgnoreCase( meta.getKeyCondition()[i] ) ) {
          keynrs.add( keynr );
          keynrs2.add( -1 );
        }

        int keynr2 = getInputRowMeta().indexOfValue( meta.getKeyStream2()[i] );
        if ( keynr2 < 0 && // couldn't find field!
          "BETWEEN".equalsIgnoreCase( meta.getKeyCondition()[i] ) // 2 fields needed!
        ) {
          throw new KettleStepException( BaseMessages.getString( PKG, "InsertUpdate.Exception.FieldRequired", meta
            .getKeyStream2()[i] ) );
        }
        keynrs2.add( keynr2 );

        if ( log.isDebug() ) {
          logDebug( BaseMessages.getString( PKG, "InsertUpdate.Log.FieldHasDataNumbers", meta.getKeyStream()[i] )
            + "" + keynrs.get( keynrs.size() - 1 ) );
        }
      }

      data.keynrs = ArrayUtils.toPrimitive( keynrs.toArray( new Integer[0] ) );
      data.keynrs2 = ArrayUtils.toPrimitive( keynrs2.toArray( new Integer[0] ) );

      // Cache the position of the compare fields in Row row
      //
      data.valuenrs = new int[meta.getUpdateLookup().length];
      for ( int i = 0; i < meta.getUpdateLookup().length; i++ ) {
        data.valuenrs[i] = getInputRowMeta().indexOfValue( meta.getUpdateStream()[i] );
        if ( data.valuenrs[i] < 0 ) {
          // couldn't find field!

          throw new KettleStepException( BaseMessages.getString( PKG, "InsertUpdate.Exception.FieldRequired", meta
            .getUpdateStream()[i] ) );
        }
        if ( log.isDebug() ) {
          logDebug( BaseMessages
            .getString( PKG, "InsertUpdate.Log.FieldHasDataNumbers", meta.getUpdateStream()[i] )
            + data.valuenrs[i] );
        }
      }

      setLookup( getInputRowMeta() );

      data.insertRowMeta = new RowMeta();

      // Insert the update fields: just names. Type doesn't matter!
      for ( int i = 0; i < meta.getUpdateLookup().length; i++ ) {
        ValueMetaInterface insValue = data.insertRowMeta.searchValueMeta( meta.getUpdateLookup()[i] );
        if ( insValue == null ) {
          // Don't add twice!

          // we already checked that this value exists so it's probably safe to ignore lookup failure...
          ValueMetaInterface insertValue = getInputRowMeta().searchValueMeta( meta.getUpdateStream()[i] ).clone();
          insertValue.setName( meta.getUpdateLookup()[i] );
          data.insertRowMeta.addValueMeta( insertValue );
        } else {
          throw new KettleStepException( "The same column can't be inserted into the target row twice: "
            + insValue.getName() ); // TODO i18n
        }
      }
      data.db.prepareInsert(
        data.insertRowMeta, environmentSubstitute( meta.getSchemaName() ), environmentSubstitute( meta
          .getTableName() ) );

      if ( !meta.isUpdateBypassed() ) {
        List<String> updateColumns = new ArrayList<String>();
        for ( int i = 0; i < meta.getUpdate().length; i++ ) {
          if ( meta.getUpdate()[i].booleanValue() ) {
            updateColumns.add( meta.getUpdateLookup()[i] );
          }
        }
        prepareUpdate( getInputRowMeta() );
      }
    }

    try {
      lookupValues( getInputRowMeta(), r ); // add new values to the row in rowset[0].
      putRow( data.outputRowMeta, r ); // Nothing changed to the input, return the same row, pass a "cloned" metadata
                                       // row.

      if ( checkFeedback( getLinesRead() ) ) {
        if ( log.isBasic() ) {
          logBasic( BaseMessages.getString( PKG, "InsertUpdate.Log.LineNumber" ) + getLinesRead() );
        }
      }
    } catch ( KettleException e ) {
      if ( getStepMeta().isDoingErrorHandling() ) {
        sendToErrorRow = true;
        errorMessage = e.toString();
      } else {
        throw new KettleStepException( BaseMessages.getString( PKG, "InsertUpdate.Log.ErrorInStep" ), e );
      }

      if ( sendToErrorRow ) {
        // Simply add this row to the error row
        putError( getInputRowMeta(), r, 1, errorMessage, null, "ISU001" );
      }
    }

    return true;
  }

  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 );
    }
  }

  // Lookup certain fields in a table
  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 boolean init( StepMetaInterface smi, StepDataInterface sdi ) {
    meta = (InsertUpdateMeta) smi;
    data = (InsertUpdateData) sdi;

    if ( super.init( smi, sdi ) ) {
      try {
        if ( meta.getDatabaseMeta() == null ) {
          logError( BaseMessages.getString( PKG, "InsertUpdate.Init.ConnectionMissing", getStepname() ) );
          return false;
        }
        data.db = new Database( this, meta.getDatabaseMeta() );
        data.db.shareVariablesWith( this );
        if ( getTransMeta().isUsingUniqueConnections() ) {
          synchronized ( getTrans() ) {
            data.db.connect( getTrans().getTransactionId(), getPartitionID() );
          }
        } else {
          data.db.connect( getPartitionID() );
        }
        data.db.setCommit(  meta.getCommitSize( this ) );

        return true;
      } catch ( KettleException ke ) {
        logError( BaseMessages.getString( PKG, "InsertUpdate.Log.ErrorOccurredDuringStepInitialize" )
          + ke.getMessage() );
      }
    }
    return false;
  }

  public void dispose( StepMetaInterface smi, StepDataInterface sdi ) {
    meta = (InsertUpdateMeta) smi;
    data = (InsertUpdateData) sdi;

    if ( data.db != null ) {
      try {
        if ( !data.db.isAutoCommit() ) {
          if ( getErrors() == 0 ) {
            data.db.commit();
          } else {
            data.db.rollback();
          }
        }
        data.db.closeUpdate();
        data.db.closeInsert();
      } catch ( KettleDatabaseException e ) {
        logError( BaseMessages.getString( PKG, "InsertUpdate.Log.UnableToCommitConnection" ) + e.toString() );
        setErrors( 1 );
      } finally {
        data.db.disconnect();
      }
    }
    super.dispose( smi, sdi );
  }

}