@Test public void testDimInsert() throws Exception { RowMetaInterface rowMetaInterface = mock( RowMetaInterface.class ); Object[] objects = mock( List.class ).toArray(); Date date = mock( Date.class ); dimensionLookupSpy.dimInsert( rowMetaInterface, objects, new Long( "132323" ), true, null, date, date ); verify( databaseMeta, times( 0 ) ).supportsAutoGeneratedKeys(); dimensionLookupSpy.dimInsert( rowMetaInterface, objects, null, true, null, date, date ); verify( databaseMeta, times( 2 ) ).supportsAutoGeneratedKeys(); }
public Long dimInsert( RowMetaInterface inputRowMeta, Object[] row, Long technicalKey, boolean newEntry, Long versionNr, Date dateFrom, Date dateTo ) throws KettleException { DatabaseMeta databaseMeta = meta.getDatabaseMeta(); if ( data.prepStatementInsert == null && data.prepStatementUpdate == null ) { RowMetaInterface insertRowMeta = new RowMeta(); String sql = "INSERT INTO " + data.schemaTable + "( "; if ( !isAutoIncrement() ) { sql += databaseMeta.quoteField( meta.getKeyField() ) + ", "; insertRowMeta.addValueMeta( data.outputRowMeta.getValueMeta( inputRowMeta.size() ) ); } else { if ( databaseMeta.needsPlaceHolder() ) { sql += "0, "; } } sql += databaseMeta.quoteField( meta.getVersionField() ) + ", " + databaseMeta.quoteField( meta.getDateFrom() ) + ", " + databaseMeta.quoteField( meta.getDateTo() ); insertRowMeta.addValueMeta( new ValueMetaInteger( meta.getVersionField() ) ); insertRowMeta.addValueMeta( new ValueMetaDate( meta.getDateFrom() ) ); insertRowMeta.addValueMeta( new ValueMetaDate( meta.getDateTo() ) ); for ( int i = 0; i < meta.getKeyLookup().length; i++ ) { sql += ", " + databaseMeta.quoteField( meta.getKeyLookup()[i] ); insertRowMeta.addValueMeta( inputRowMeta.getValueMeta( data.keynrs[i] ) ); } for ( int i = 0; i < meta.getFieldLookup().length; i++ ) { if ( !DimensionLookupMeta.isUpdateTypeWithoutArgument( meta.isUpdate(), meta.getFieldUpdate()[i] ) ) { sql += ", " + databaseMeta.quoteField( meta.getFieldLookup()[i] ); insertRowMeta.addValueMeta( inputRowMeta.getValueMeta( data.fieldnrs[i] ) ); } } for ( int i = 0; i < meta.getFieldUpdate().length; i++ ) { ValueMetaInterface valueMeta = null; switch ( meta.getFieldUpdate()[i] ) { case DimensionLookupMeta.TYPE_UPDATE_DATE_INSUP: case DimensionLookupMeta.TYPE_UPDATE_DATE_INSERTED: valueMeta = new ValueMetaDate( meta.getFieldLookup()[i] ); break; case DimensionLookupMeta.TYPE_UPDATE_LAST_VERSION: valueMeta = new ValueMetaBoolean( meta.getFieldLookup()[i] ); break; default: break; } if ( valueMeta != null ) { sql += ", " + databaseMeta.quoteField( valueMeta.getName() ); insertRowMeta.addValueMeta( valueMeta ); } } sql += ") VALUES ("; if ( !isAutoIncrement() ) { sql += "?, "; } sql += "?, ?, ?"; for ( int i = 0; i < data.keynrs.length; i++ ) { sql += ", ?"; } for ( int i = 0; i < meta.getFieldLookup().length; i++ ) { if ( !DimensionLookupMeta.isUpdateTypeWithoutArgument( meta.isUpdate(), meta.getFieldUpdate()[i] ) ) { sql += ", ?"; } } for ( int i = 0; i < meta.getFieldUpdate().length; i++ ) { switch ( meta.getFieldUpdate()[i] ) { case DimensionLookupMeta.TYPE_UPDATE_DATE_INSUP: case DimensionLookupMeta.TYPE_UPDATE_DATE_INSERTED: case DimensionLookupMeta.TYPE_UPDATE_LAST_VERSION: sql += ", ?"; break; default: break; } } sql += " )"; try { if ( technicalKey == null && databaseMeta.supportsAutoGeneratedKeys() ) { logDetailed( "SQL w/ return keys=[" + sql + "]" ); data.prepStatementInsert = data.db.getConnection().prepareStatement( databaseMeta.stripCR( sql ), Statement.RETURN_GENERATED_KEYS ); } else { logDetailed( "SQL=[" + sql + "]" ); data.prepStatementInsert = data.db.getConnection().prepareStatement( databaseMeta.stripCR( sql ) ); } } catch ( SQLException ex ) { throw new KettleDatabaseException( "Unable to prepare dimension insert :" + Const.CR + sql, ex ); } RowMetaInterface updateRowMeta = new RowMeta(); String sql_upd = "UPDATE " + data.schemaTable + Const.CR; sql_upd += "SET " + databaseMeta.quoteField( meta.getDateTo() ) + " = ?" + Const.CR; updateRowMeta.addValueMeta( new ValueMetaDate( meta.getDateTo() ) ); for ( int i = 0; i < meta.getFieldUpdate().length; i++ ) { ValueMetaInterface valueMeta = null; switch ( meta.getFieldUpdate()[i] ) { case DimensionLookupMeta.TYPE_UPDATE_DATE_INSUP: case DimensionLookupMeta.TYPE_UPDATE_DATE_UPDATED: valueMeta = new ValueMetaDate( meta.getFieldLookup()[i] ); break; case DimensionLookupMeta.TYPE_UPDATE_LAST_VERSION: valueMeta = new ValueMetaBoolean( meta.getFieldLookup()[i] ); break; default: break; } if ( valueMeta != null ) { sql_upd += ", " + databaseMeta.quoteField( valueMeta.getName() ) + " = ?" + Const.CR; updateRowMeta.addValueMeta( valueMeta ); } } sql_upd += "WHERE "; for ( int i = 0; i < meta.getKeyLookup().length; i++ ) { if ( i > 0 ) { sql_upd += "AND "; } sql_upd += databaseMeta.quoteField( meta.getKeyLookup()[i] ) + " = ?" + Const.CR; updateRowMeta.addValueMeta( inputRowMeta.getValueMeta( data.keynrs[i] ) ); } sql_upd += "AND " + databaseMeta.quoteField( meta.getVersionField() ) + " = ? "; updateRowMeta.addValueMeta( new ValueMetaInteger( meta.getVersionField() ) ); try { logDetailed( "Preparing update: " + Const.CR + sql_upd + Const.CR ); data.prepStatementUpdate = data.db.getConnection().prepareStatement( databaseMeta.stripCR( sql_upd ) ); } catch ( SQLException ex ) { throw new KettleDatabaseException( "Unable to prepare dimension update :" + Const.CR + sql_upd, ex ); } data.insertRowMeta = insertRowMeta; data.updateRowMeta = updateRowMeta; } Object[] insertRow = new Object[data.insertRowMeta.size()]; int insertIndex = 0; if ( !isAutoIncrement() ) { insertRow[insertIndex++] = technicalKey; } insertRow[insertIndex++] = versionNr; switch ( data.startDateChoice ) { case DimensionLookupMeta.START_DATE_ALTERNATIVE_NONE: insertRow[insertIndex++] = dateFrom; break; case DimensionLookupMeta.START_DATE_ALTERNATIVE_SYSDATE: insertRow[insertIndex++] = dateFrom; break; case DimensionLookupMeta.START_DATE_ALTERNATIVE_START_OF_TRANS: insertRow[insertIndex++] = getTrans().getStartDate(); break; case DimensionLookupMeta.START_DATE_ALTERNATIVE_NULL: insertRow[insertIndex++] = null; break; case DimensionLookupMeta.START_DATE_ALTERNATIVE_COLUMN_VALUE: insertRow[insertIndex++] = inputRowMeta.getDate( row, data.startDateFieldIndex ); break; default: throw new KettleStepException( BaseMessages.getString( PKG, "DimensionLookup.Exception.IllegalStartDateSelection", Integer.toString( data.startDateChoice ) ) ); } insertRow[insertIndex++] = dateTo; for ( int i = 0; i < data.keynrs.length; i++ ) { insertRow[insertIndex++] = row[data.keynrs[i]]; } for ( int i = 0; i < data.fieldnrs.length; i++ ) { if ( data.fieldnrs[i] >= 0 ) { insertRow[insertIndex++] = row[data.fieldnrs[i]]; } } for ( int i = 0; i < meta.getFieldUpdate().length; i++ ) { switch ( meta.getFieldUpdate()[i] ) { case DimensionLookupMeta.TYPE_UPDATE_DATE_INSUP: case DimensionLookupMeta.TYPE_UPDATE_DATE_INSERTED: insertRow[insertIndex++] = new Date(); break; case DimensionLookupMeta.TYPE_UPDATE_LAST_VERSION: insertRow[insertIndex++] = Boolean.TRUE; break; default: break; } } if ( isDebug() ) { logDebug( "rins, size=" + data.insertRowMeta.size() + ", values=" + data.insertRowMeta.getString( insertRow ) ); } data.db.setValues( data.insertRowMeta, insertRow, data.prepStatementInsert ); data.db.insertRow( data.prepStatementInsert ); if ( isDebug() ) { logDebug( "Row inserted!" ); } if ( technicalKey == null && databaseMeta.supportsAutoGeneratedKeys() ) { try { RowMetaAndData keys = data.db.getGeneratedKeys( data.prepStatementInsert ); if ( keys.getRowMeta().size() > 0 ) { technicalKey = keys.getRowMeta().getInteger( keys.getData(), 0 ); } else { throw new KettleDatabaseException( "Unable to retrieve value of auto-generated technical key : no value found!" ); } } catch ( Exception e ) { throw new KettleDatabaseException( "Unable to retrieve value of auto-generated technical key : unexpected error: ", e ); } } if ( !newEntry ) { Object[] updateRow = new Object[data.updateRowMeta.size()]; int updateIndex = 0; switch ( data.startDateChoice ) { case DimensionLookupMeta.START_DATE_ALTERNATIVE_NONE: updateRow[updateIndex++] = dateFrom; break; case DimensionLookupMeta.START_DATE_ALTERNATIVE_SYSDATE: updateRow[updateIndex++] = new Date(); break; case DimensionLookupMeta.START_DATE_ALTERNATIVE_START_OF_TRANS: updateRow[updateIndex++] = getTrans().getCurrentDate(); break; case DimensionLookupMeta.START_DATE_ALTERNATIVE_NULL: updateRow[updateIndex++] = null; break; case DimensionLookupMeta.START_DATE_ALTERNATIVE_COLUMN_VALUE: updateRow[updateIndex++] = inputRowMeta.getDate( row, data.startDateFieldIndex ); break; default: throw new KettleStepException( BaseMessages.getString( "DimensionLookup.Exception.IllegalStartDateSelection", Integer.toString( data.startDateChoice ) ) ); } for ( int i = 0; i < meta.getFieldUpdate().length; i++ ) { switch ( meta.getFieldUpdate()[i] ) { case DimensionLookupMeta.TYPE_UPDATE_DATE_INSUP: updateRow[updateIndex++] = new Date(); break; case DimensionLookupMeta.TYPE_UPDATE_LAST_VERSION: updateRow[updateIndex++] = Boolean.FALSE; break; case DimensionLookupMeta.TYPE_UPDATE_DATE_UPDATED: updateRow[updateIndex++] = new Date(); break; default: break; } } for ( int i = 0; i < data.keynrs.length; i++ ) { updateRow[updateIndex++] = row[data.keynrs[i]]; } updateRow[updateIndex++] = versionNr - 1; if ( isRowLevel() ) { logRowlevel( "UPDATE using rupd=" + data.updateRowMeta.getString( updateRow ) ); } data.db.setValues( data.updateRowMeta, updateRow, data.prepStatementUpdate ); if ( isDebug() ) { logDebug( "Values set for update (" + data.updateRowMeta.size() + ")" ); } data.db.insertRow( data.prepStatementUpdate ); if ( isDebug() ) { logDebug( "Row updated!" ); } } return technicalKey; }
DimensionLookup extends BaseStep implements StepInterface { public Long dimInsert( RowMetaInterface inputRowMeta, Object[] row, Long technicalKey, boolean newEntry, Long versionNr, Date dateFrom, Date dateTo ) throws KettleException { DatabaseMeta databaseMeta = meta.getDatabaseMeta(); if ( data.prepStatementInsert == null && data.prepStatementUpdate == null ) { RowMetaInterface insertRowMeta = new RowMeta(); String sql = "INSERT INTO " + data.schemaTable + "( "; if ( !isAutoIncrement() ) { sql += databaseMeta.quoteField( meta.getKeyField() ) + ", "; insertRowMeta.addValueMeta( data.outputRowMeta.getValueMeta( inputRowMeta.size() ) ); } else { if ( databaseMeta.needsPlaceHolder() ) { sql += "0, "; } } sql += databaseMeta.quoteField( meta.getVersionField() ) + ", " + databaseMeta.quoteField( meta.getDateFrom() ) + ", " + databaseMeta.quoteField( meta.getDateTo() ); insertRowMeta.addValueMeta( new ValueMetaInteger( meta.getVersionField() ) ); insertRowMeta.addValueMeta( new ValueMetaDate( meta.getDateFrom() ) ); insertRowMeta.addValueMeta( new ValueMetaDate( meta.getDateTo() ) ); for ( int i = 0; i < meta.getKeyLookup().length; i++ ) { sql += ", " + databaseMeta.quoteField( meta.getKeyLookup()[i] ); insertRowMeta.addValueMeta( inputRowMeta.getValueMeta( data.keynrs[i] ) ); } for ( int i = 0; i < meta.getFieldLookup().length; i++ ) { if ( !DimensionLookupMeta.isUpdateTypeWithoutArgument( meta.isUpdate(), meta.getFieldUpdate()[i] ) ) { sql += ", " + databaseMeta.quoteField( meta.getFieldLookup()[i] ); insertRowMeta.addValueMeta( inputRowMeta.getValueMeta( data.fieldnrs[i] ) ); } } for ( int i = 0; i < meta.getFieldUpdate().length; i++ ) { ValueMetaInterface valueMeta = null; switch ( meta.getFieldUpdate()[i] ) { case DimensionLookupMeta.TYPE_UPDATE_DATE_INSUP: case DimensionLookupMeta.TYPE_UPDATE_DATE_INSERTED: valueMeta = new ValueMetaDate( meta.getFieldLookup()[i] ); break; case DimensionLookupMeta.TYPE_UPDATE_LAST_VERSION: valueMeta = new ValueMetaBoolean( meta.getFieldLookup()[i] ); break; default: break; } if ( valueMeta != null ) { sql += ", " + databaseMeta.quoteField( valueMeta.getName() ); insertRowMeta.addValueMeta( valueMeta ); } } sql += ") VALUES ("; if ( !isAutoIncrement() ) { sql += "?, "; } sql += "?, ?, ?"; for ( int i = 0; i < data.keynrs.length; i++ ) { sql += ", ?"; } for ( int i = 0; i < meta.getFieldLookup().length; i++ ) { if ( !DimensionLookupMeta.isUpdateTypeWithoutArgument( meta.isUpdate(), meta.getFieldUpdate()[i] ) ) { sql += ", ?"; } } for ( int i = 0; i < meta.getFieldUpdate().length; i++ ) { switch ( meta.getFieldUpdate()[i] ) { case DimensionLookupMeta.TYPE_UPDATE_DATE_INSUP: case DimensionLookupMeta.TYPE_UPDATE_DATE_INSERTED: case DimensionLookupMeta.TYPE_UPDATE_LAST_VERSION: sql += ", ?"; break; default: break; } } sql += " )"; try { if ( technicalKey == null && databaseMeta.supportsAutoGeneratedKeys() ) { logDetailed( "SQL w/ return keys=[" + sql + "]" ); data.prepStatementInsert = data.db.getConnection().prepareStatement( databaseMeta.stripCR( sql ), Statement.RETURN_GENERATED_KEYS ); } else { logDetailed( "SQL=[" + sql + "]" ); data.prepStatementInsert = data.db.getConnection().prepareStatement( databaseMeta.stripCR( sql ) ); } } catch ( SQLException ex ) { throw new KettleDatabaseException( "Unable to prepare dimension insert :" + Const.CR + sql, ex ); } RowMetaInterface updateRowMeta = new RowMeta(); String sql_upd = "UPDATE " + data.schemaTable + Const.CR; sql_upd += "SET " + databaseMeta.quoteField( meta.getDateTo() ) + " = ?" + Const.CR; updateRowMeta.addValueMeta( new ValueMetaDate( meta.getDateTo() ) ); for ( int i = 0; i < meta.getFieldUpdate().length; i++ ) { ValueMetaInterface valueMeta = null; switch ( meta.getFieldUpdate()[i] ) { case DimensionLookupMeta.TYPE_UPDATE_DATE_INSUP: case DimensionLookupMeta.TYPE_UPDATE_DATE_UPDATED: valueMeta = new ValueMetaDate( meta.getFieldLookup()[i] ); break; case DimensionLookupMeta.TYPE_UPDATE_LAST_VERSION: valueMeta = new ValueMetaBoolean( meta.getFieldLookup()[i] ); break; default: break; } if ( valueMeta != null ) { sql_upd += ", " + databaseMeta.quoteField( valueMeta.getName() ) + " = ?" + Const.CR; updateRowMeta.addValueMeta( valueMeta ); } } sql_upd += "WHERE "; for ( int i = 0; i < meta.getKeyLookup().length; i++ ) { if ( i > 0 ) { sql_upd += "AND "; } sql_upd += databaseMeta.quoteField( meta.getKeyLookup()[i] ) + " = ?" + Const.CR; updateRowMeta.addValueMeta( inputRowMeta.getValueMeta( data.keynrs[i] ) ); } sql_upd += "AND " + databaseMeta.quoteField( meta.getVersionField() ) + " = ? "; updateRowMeta.addValueMeta( new ValueMetaInteger( meta.getVersionField() ) ); try { logDetailed( "Preparing update: " + Const.CR + sql_upd + Const.CR ); data.prepStatementUpdate = data.db.getConnection().prepareStatement( databaseMeta.stripCR( sql_upd ) ); } catch ( SQLException ex ) { throw new KettleDatabaseException( "Unable to prepare dimension update :" + Const.CR + sql_upd, ex ); } data.insertRowMeta = insertRowMeta; data.updateRowMeta = updateRowMeta; } Object[] insertRow = new Object[data.insertRowMeta.size()]; int insertIndex = 0; if ( !isAutoIncrement() ) { insertRow[insertIndex++] = technicalKey; } insertRow[insertIndex++] = versionNr; switch ( data.startDateChoice ) { case DimensionLookupMeta.START_DATE_ALTERNATIVE_NONE: insertRow[insertIndex++] = dateFrom; break; case DimensionLookupMeta.START_DATE_ALTERNATIVE_SYSDATE: insertRow[insertIndex++] = dateFrom; break; case DimensionLookupMeta.START_DATE_ALTERNATIVE_START_OF_TRANS: insertRow[insertIndex++] = getTrans().getStartDate(); break; case DimensionLookupMeta.START_DATE_ALTERNATIVE_NULL: insertRow[insertIndex++] = null; break; case DimensionLookupMeta.START_DATE_ALTERNATIVE_COLUMN_VALUE: insertRow[insertIndex++] = inputRowMeta.getDate( row, data.startDateFieldIndex ); break; default: throw new KettleStepException( BaseMessages.getString( PKG, "DimensionLookup.Exception.IllegalStartDateSelection", Integer.toString( data.startDateChoice ) ) ); } insertRow[insertIndex++] = dateTo; for ( int i = 0; i < data.keynrs.length; i++ ) { insertRow[insertIndex++] = row[data.keynrs[i]]; } for ( int i = 0; i < data.fieldnrs.length; i++ ) { if ( data.fieldnrs[i] >= 0 ) { insertRow[insertIndex++] = row[data.fieldnrs[i]]; } } for ( int i = 0; i < meta.getFieldUpdate().length; i++ ) { switch ( meta.getFieldUpdate()[i] ) { case DimensionLookupMeta.TYPE_UPDATE_DATE_INSUP: case DimensionLookupMeta.TYPE_UPDATE_DATE_INSERTED: insertRow[insertIndex++] = new Date(); break; case DimensionLookupMeta.TYPE_UPDATE_LAST_VERSION: insertRow[insertIndex++] = Boolean.TRUE; break; default: break; } } if ( isDebug() ) { logDebug( "rins, size=" + data.insertRowMeta.size() + ", values=" + data.insertRowMeta.getString( insertRow ) ); } data.db.setValues( data.insertRowMeta, insertRow, data.prepStatementInsert ); data.db.insertRow( data.prepStatementInsert ); if ( isDebug() ) { logDebug( "Row inserted!" ); } if ( technicalKey == null && databaseMeta.supportsAutoGeneratedKeys() ) { try { RowMetaAndData keys = data.db.getGeneratedKeys( data.prepStatementInsert ); if ( keys.getRowMeta().size() > 0 ) { technicalKey = keys.getRowMeta().getInteger( keys.getData(), 0 ); } else { throw new KettleDatabaseException( "Unable to retrieve value of auto-generated technical key : no value found!" ); } } catch ( Exception e ) { throw new KettleDatabaseException( "Unable to retrieve value of auto-generated technical key : unexpected error: ", e ); } } if ( !newEntry ) { Object[] updateRow = new Object[data.updateRowMeta.size()]; int updateIndex = 0; switch ( data.startDateChoice ) { case DimensionLookupMeta.START_DATE_ALTERNATIVE_NONE: updateRow[updateIndex++] = dateFrom; break; case DimensionLookupMeta.START_DATE_ALTERNATIVE_SYSDATE: updateRow[updateIndex++] = new Date(); break; case DimensionLookupMeta.START_DATE_ALTERNATIVE_START_OF_TRANS: updateRow[updateIndex++] = getTrans().getCurrentDate(); break; case DimensionLookupMeta.START_DATE_ALTERNATIVE_NULL: updateRow[updateIndex++] = null; break; case DimensionLookupMeta.START_DATE_ALTERNATIVE_COLUMN_VALUE: updateRow[updateIndex++] = inputRowMeta.getDate( row, data.startDateFieldIndex ); break; default: throw new KettleStepException( BaseMessages.getString( "DimensionLookup.Exception.IllegalStartDateSelection", Integer.toString( data.startDateChoice ) ) ); } for ( int i = 0; i < meta.getFieldUpdate().length; i++ ) { switch ( meta.getFieldUpdate()[i] ) { case DimensionLookupMeta.TYPE_UPDATE_DATE_INSUP: updateRow[updateIndex++] = new Date(); break; case DimensionLookupMeta.TYPE_UPDATE_LAST_VERSION: updateRow[updateIndex++] = Boolean.FALSE; break; case DimensionLookupMeta.TYPE_UPDATE_DATE_UPDATED: updateRow[updateIndex++] = new Date(); break; default: break; } } for ( int i = 0; i < data.keynrs.length; i++ ) { updateRow[updateIndex++] = row[data.keynrs[i]]; } updateRow[updateIndex++] = versionNr - 1; if ( isRowLevel() ) { logRowlevel( "UPDATE using rupd=" + data.updateRowMeta.getString( updateRow ) ); } data.db.setValues( data.updateRowMeta, updateRow, data.prepStatementUpdate ); if ( isDebug() ) { logDebug( "Values set for update (" + data.updateRowMeta.size() + ")" ); } data.db.insertRow( data.prepStatementUpdate ); if ( isDebug() ) { logDebug( "Row updated!" ); } } return technicalKey; } }
DimensionLookup extends BaseStep implements StepInterface { public Long dimInsert( RowMetaInterface inputRowMeta, Object[] row, Long technicalKey, boolean newEntry, Long versionNr, Date dateFrom, Date dateTo ) throws KettleException { DatabaseMeta databaseMeta = meta.getDatabaseMeta(); if ( data.prepStatementInsert == null && data.prepStatementUpdate == null ) { RowMetaInterface insertRowMeta = new RowMeta(); String sql = "INSERT INTO " + data.schemaTable + "( "; if ( !isAutoIncrement() ) { sql += databaseMeta.quoteField( meta.getKeyField() ) + ", "; insertRowMeta.addValueMeta( data.outputRowMeta.getValueMeta( inputRowMeta.size() ) ); } else { if ( databaseMeta.needsPlaceHolder() ) { sql += "0, "; } } sql += databaseMeta.quoteField( meta.getVersionField() ) + ", " + databaseMeta.quoteField( meta.getDateFrom() ) + ", " + databaseMeta.quoteField( meta.getDateTo() ); insertRowMeta.addValueMeta( new ValueMetaInteger( meta.getVersionField() ) ); insertRowMeta.addValueMeta( new ValueMetaDate( meta.getDateFrom() ) ); insertRowMeta.addValueMeta( new ValueMetaDate( meta.getDateTo() ) ); for ( int i = 0; i < meta.getKeyLookup().length; i++ ) { sql += ", " + databaseMeta.quoteField( meta.getKeyLookup()[i] ); insertRowMeta.addValueMeta( inputRowMeta.getValueMeta( data.keynrs[i] ) ); } for ( int i = 0; i < meta.getFieldLookup().length; i++ ) { if ( !DimensionLookupMeta.isUpdateTypeWithoutArgument( meta.isUpdate(), meta.getFieldUpdate()[i] ) ) { sql += ", " + databaseMeta.quoteField( meta.getFieldLookup()[i] ); insertRowMeta.addValueMeta( inputRowMeta.getValueMeta( data.fieldnrs[i] ) ); } } for ( int i = 0; i < meta.getFieldUpdate().length; i++ ) { ValueMetaInterface valueMeta = null; switch ( meta.getFieldUpdate()[i] ) { case DimensionLookupMeta.TYPE_UPDATE_DATE_INSUP: case DimensionLookupMeta.TYPE_UPDATE_DATE_INSERTED: valueMeta = new ValueMetaDate( meta.getFieldLookup()[i] ); break; case DimensionLookupMeta.TYPE_UPDATE_LAST_VERSION: valueMeta = new ValueMetaBoolean( meta.getFieldLookup()[i] ); break; default: break; } if ( valueMeta != null ) { sql += ", " + databaseMeta.quoteField( valueMeta.getName() ); insertRowMeta.addValueMeta( valueMeta ); } } sql += ") VALUES ("; if ( !isAutoIncrement() ) { sql += "?, "; } sql += "?, ?, ?"; for ( int i = 0; i < data.keynrs.length; i++ ) { sql += ", ?"; } for ( int i = 0; i < meta.getFieldLookup().length; i++ ) { if ( !DimensionLookupMeta.isUpdateTypeWithoutArgument( meta.isUpdate(), meta.getFieldUpdate()[i] ) ) { sql += ", ?"; } } for ( int i = 0; i < meta.getFieldUpdate().length; i++ ) { switch ( meta.getFieldUpdate()[i] ) { case DimensionLookupMeta.TYPE_UPDATE_DATE_INSUP: case DimensionLookupMeta.TYPE_UPDATE_DATE_INSERTED: case DimensionLookupMeta.TYPE_UPDATE_LAST_VERSION: sql += ", ?"; break; default: break; } } sql += " )"; try { if ( technicalKey == null && databaseMeta.supportsAutoGeneratedKeys() ) { logDetailed( "SQL w/ return keys=[" + sql + "]" ); data.prepStatementInsert = data.db.getConnection().prepareStatement( databaseMeta.stripCR( sql ), Statement.RETURN_GENERATED_KEYS ); } else { logDetailed( "SQL=[" + sql + "]" ); data.prepStatementInsert = data.db.getConnection().prepareStatement( databaseMeta.stripCR( sql ) ); } } catch ( SQLException ex ) { throw new KettleDatabaseException( "Unable to prepare dimension insert :" + Const.CR + sql, ex ); } RowMetaInterface updateRowMeta = new RowMeta(); String sql_upd = "UPDATE " + data.schemaTable + Const.CR; sql_upd += "SET " + databaseMeta.quoteField( meta.getDateTo() ) + " = ?" + Const.CR; updateRowMeta.addValueMeta( new ValueMetaDate( meta.getDateTo() ) ); for ( int i = 0; i < meta.getFieldUpdate().length; i++ ) { ValueMetaInterface valueMeta = null; switch ( meta.getFieldUpdate()[i] ) { case DimensionLookupMeta.TYPE_UPDATE_DATE_INSUP: case DimensionLookupMeta.TYPE_UPDATE_DATE_UPDATED: valueMeta = new ValueMetaDate( meta.getFieldLookup()[i] ); break; case DimensionLookupMeta.TYPE_UPDATE_LAST_VERSION: valueMeta = new ValueMetaBoolean( meta.getFieldLookup()[i] ); break; default: break; } if ( valueMeta != null ) { sql_upd += ", " + databaseMeta.quoteField( valueMeta.getName() ) + " = ?" + Const.CR; updateRowMeta.addValueMeta( valueMeta ); } } sql_upd += "WHERE "; for ( int i = 0; i < meta.getKeyLookup().length; i++ ) { if ( i > 0 ) { sql_upd += "AND "; } sql_upd += databaseMeta.quoteField( meta.getKeyLookup()[i] ) + " = ?" + Const.CR; updateRowMeta.addValueMeta( inputRowMeta.getValueMeta( data.keynrs[i] ) ); } sql_upd += "AND " + databaseMeta.quoteField( meta.getVersionField() ) + " = ? "; updateRowMeta.addValueMeta( new ValueMetaInteger( meta.getVersionField() ) ); try { logDetailed( "Preparing update: " + Const.CR + sql_upd + Const.CR ); data.prepStatementUpdate = data.db.getConnection().prepareStatement( databaseMeta.stripCR( sql_upd ) ); } catch ( SQLException ex ) { throw new KettleDatabaseException( "Unable to prepare dimension update :" + Const.CR + sql_upd, ex ); } data.insertRowMeta = insertRowMeta; data.updateRowMeta = updateRowMeta; } Object[] insertRow = new Object[data.insertRowMeta.size()]; int insertIndex = 0; if ( !isAutoIncrement() ) { insertRow[insertIndex++] = technicalKey; } insertRow[insertIndex++] = versionNr; switch ( data.startDateChoice ) { case DimensionLookupMeta.START_DATE_ALTERNATIVE_NONE: insertRow[insertIndex++] = dateFrom; break; case DimensionLookupMeta.START_DATE_ALTERNATIVE_SYSDATE: insertRow[insertIndex++] = dateFrom; break; case DimensionLookupMeta.START_DATE_ALTERNATIVE_START_OF_TRANS: insertRow[insertIndex++] = getTrans().getStartDate(); break; case DimensionLookupMeta.START_DATE_ALTERNATIVE_NULL: insertRow[insertIndex++] = null; break; case DimensionLookupMeta.START_DATE_ALTERNATIVE_COLUMN_VALUE: insertRow[insertIndex++] = inputRowMeta.getDate( row, data.startDateFieldIndex ); break; default: throw new KettleStepException( BaseMessages.getString( PKG, "DimensionLookup.Exception.IllegalStartDateSelection", Integer.toString( data.startDateChoice ) ) ); } insertRow[insertIndex++] = dateTo; for ( int i = 0; i < data.keynrs.length; i++ ) { insertRow[insertIndex++] = row[data.keynrs[i]]; } for ( int i = 0; i < data.fieldnrs.length; i++ ) { if ( data.fieldnrs[i] >= 0 ) { insertRow[insertIndex++] = row[data.fieldnrs[i]]; } } for ( int i = 0; i < meta.getFieldUpdate().length; i++ ) { switch ( meta.getFieldUpdate()[i] ) { case DimensionLookupMeta.TYPE_UPDATE_DATE_INSUP: case DimensionLookupMeta.TYPE_UPDATE_DATE_INSERTED: insertRow[insertIndex++] = new Date(); break; case DimensionLookupMeta.TYPE_UPDATE_LAST_VERSION: insertRow[insertIndex++] = Boolean.TRUE; break; default: break; } } if ( isDebug() ) { logDebug( "rins, size=" + data.insertRowMeta.size() + ", values=" + data.insertRowMeta.getString( insertRow ) ); } data.db.setValues( data.insertRowMeta, insertRow, data.prepStatementInsert ); data.db.insertRow( data.prepStatementInsert ); if ( isDebug() ) { logDebug( "Row inserted!" ); } if ( technicalKey == null && databaseMeta.supportsAutoGeneratedKeys() ) { try { RowMetaAndData keys = data.db.getGeneratedKeys( data.prepStatementInsert ); if ( keys.getRowMeta().size() > 0 ) { technicalKey = keys.getRowMeta().getInteger( keys.getData(), 0 ); } else { throw new KettleDatabaseException( "Unable to retrieve value of auto-generated technical key : no value found!" ); } } catch ( Exception e ) { throw new KettleDatabaseException( "Unable to retrieve value of auto-generated technical key : unexpected error: ", e ); } } if ( !newEntry ) { Object[] updateRow = new Object[data.updateRowMeta.size()]; int updateIndex = 0; switch ( data.startDateChoice ) { case DimensionLookupMeta.START_DATE_ALTERNATIVE_NONE: updateRow[updateIndex++] = dateFrom; break; case DimensionLookupMeta.START_DATE_ALTERNATIVE_SYSDATE: updateRow[updateIndex++] = new Date(); break; case DimensionLookupMeta.START_DATE_ALTERNATIVE_START_OF_TRANS: updateRow[updateIndex++] = getTrans().getCurrentDate(); break; case DimensionLookupMeta.START_DATE_ALTERNATIVE_NULL: updateRow[updateIndex++] = null; break; case DimensionLookupMeta.START_DATE_ALTERNATIVE_COLUMN_VALUE: updateRow[updateIndex++] = inputRowMeta.getDate( row, data.startDateFieldIndex ); break; default: throw new KettleStepException( BaseMessages.getString( "DimensionLookup.Exception.IllegalStartDateSelection", Integer.toString( data.startDateChoice ) ) ); } for ( int i = 0; i < meta.getFieldUpdate().length; i++ ) { switch ( meta.getFieldUpdate()[i] ) { case DimensionLookupMeta.TYPE_UPDATE_DATE_INSUP: updateRow[updateIndex++] = new Date(); break; case DimensionLookupMeta.TYPE_UPDATE_LAST_VERSION: updateRow[updateIndex++] = Boolean.FALSE; break; case DimensionLookupMeta.TYPE_UPDATE_DATE_UPDATED: updateRow[updateIndex++] = new Date(); break; default: break; } } for ( int i = 0; i < data.keynrs.length; i++ ) { updateRow[updateIndex++] = row[data.keynrs[i]]; } updateRow[updateIndex++] = versionNr - 1; if ( isRowLevel() ) { logRowlevel( "UPDATE using rupd=" + data.updateRowMeta.getString( updateRow ) ); } data.db.setValues( data.updateRowMeta, updateRow, data.prepStatementUpdate ); if ( isDebug() ) { logDebug( "Values set for update (" + data.updateRowMeta.size() + ")" ); } data.db.insertRow( data.prepStatementUpdate ); if ( isDebug() ) { logDebug( "Row updated!" ); } } return technicalKey; } DimensionLookup( StepMeta stepMeta, StepDataInterface stepDataInterface, int copyNr, TransMeta transMeta,
    Trans trans ); }
DimensionLookup extends BaseStep implements StepInterface { public Long dimInsert( RowMetaInterface inputRowMeta, Object[] row, Long technicalKey, boolean newEntry, Long versionNr, Date dateFrom, Date dateTo ) throws KettleException { DatabaseMeta databaseMeta = meta.getDatabaseMeta(); if ( data.prepStatementInsert == null && data.prepStatementUpdate == null ) { RowMetaInterface insertRowMeta = new RowMeta(); String sql = "INSERT INTO " + data.schemaTable + "( "; if ( !isAutoIncrement() ) { sql += databaseMeta.quoteField( meta.getKeyField() ) + ", "; insertRowMeta.addValueMeta( data.outputRowMeta.getValueMeta( inputRowMeta.size() ) ); } else { if ( databaseMeta.needsPlaceHolder() ) { sql += "0, "; } } sql += databaseMeta.quoteField( meta.getVersionField() ) + ", " + databaseMeta.quoteField( meta.getDateFrom() ) + ", " + databaseMeta.quoteField( meta.getDateTo() ); insertRowMeta.addValueMeta( new ValueMetaInteger( meta.getVersionField() ) ); insertRowMeta.addValueMeta( new ValueMetaDate( meta.getDateFrom() ) ); insertRowMeta.addValueMeta( new ValueMetaDate( meta.getDateTo() ) ); for ( int i = 0; i < meta.getKeyLookup().length; i++ ) { sql += ", " + databaseMeta.quoteField( meta.getKeyLookup()[i] ); insertRowMeta.addValueMeta( inputRowMeta.getValueMeta( data.keynrs[i] ) ); } for ( int i = 0; i < meta.getFieldLookup().length; i++ ) { if ( !DimensionLookupMeta.isUpdateTypeWithoutArgument( meta.isUpdate(), meta.getFieldUpdate()[i] ) ) { sql += ", " + databaseMeta.quoteField( meta.getFieldLookup()[i] ); insertRowMeta.addValueMeta( inputRowMeta.getValueMeta( data.fieldnrs[i] ) ); } } for ( int i = 0; i < meta.getFieldUpdate().length; i++ ) { ValueMetaInterface valueMeta = null; switch ( meta.getFieldUpdate()[i] ) { case DimensionLookupMeta.TYPE_UPDATE_DATE_INSUP: case DimensionLookupMeta.TYPE_UPDATE_DATE_INSERTED: valueMeta = new ValueMetaDate( meta.getFieldLookup()[i] ); break; case DimensionLookupMeta.TYPE_UPDATE_LAST_VERSION: valueMeta = new ValueMetaBoolean( meta.getFieldLookup()[i] ); break; default: break; } if ( valueMeta != null ) { sql += ", " + databaseMeta.quoteField( valueMeta.getName() ); insertRowMeta.addValueMeta( valueMeta ); } } sql += ") VALUES ("; if ( !isAutoIncrement() ) { sql += "?, "; } sql += "?, ?, ?"; for ( int i = 0; i < data.keynrs.length; i++ ) { sql += ", ?"; } for ( int i = 0; i < meta.getFieldLookup().length; i++ ) { if ( !DimensionLookupMeta.isUpdateTypeWithoutArgument( meta.isUpdate(), meta.getFieldUpdate()[i] ) ) { sql += ", ?"; } } for ( int i = 0; i < meta.getFieldUpdate().length; i++ ) { switch ( meta.getFieldUpdate()[i] ) { case DimensionLookupMeta.TYPE_UPDATE_DATE_INSUP: case DimensionLookupMeta.TYPE_UPDATE_DATE_INSERTED: case DimensionLookupMeta.TYPE_UPDATE_LAST_VERSION: sql += ", ?"; break; default: break; } } sql += " )"; try { if ( technicalKey == null && databaseMeta.supportsAutoGeneratedKeys() ) { logDetailed( "SQL w/ return keys=[" + sql + "]" ); data.prepStatementInsert = data.db.getConnection().prepareStatement( databaseMeta.stripCR( sql ), Statement.RETURN_GENERATED_KEYS ); } else { logDetailed( "SQL=[" + sql + "]" ); data.prepStatementInsert = data.db.getConnection().prepareStatement( databaseMeta.stripCR( sql ) ); } } catch ( SQLException ex ) { throw new KettleDatabaseException( "Unable to prepare dimension insert :" + Const.CR + sql, ex ); } RowMetaInterface updateRowMeta = new RowMeta(); String sql_upd = "UPDATE " + data.schemaTable + Const.CR; sql_upd += "SET " + databaseMeta.quoteField( meta.getDateTo() ) + " = ?" + Const.CR; updateRowMeta.addValueMeta( new ValueMetaDate( meta.getDateTo() ) ); for ( int i = 0; i < meta.getFieldUpdate().length; i++ ) { ValueMetaInterface valueMeta = null; switch ( meta.getFieldUpdate()[i] ) { case DimensionLookupMeta.TYPE_UPDATE_DATE_INSUP: case DimensionLookupMeta.TYPE_UPDATE_DATE_UPDATED: valueMeta = new ValueMetaDate( meta.getFieldLookup()[i] ); break; case DimensionLookupMeta.TYPE_UPDATE_LAST_VERSION: valueMeta = new ValueMetaBoolean( meta.getFieldLookup()[i] ); break; default: break; } if ( valueMeta != null ) { sql_upd += ", " + databaseMeta.quoteField( valueMeta.getName() ) + " = ?" + Const.CR; updateRowMeta.addValueMeta( valueMeta ); } } sql_upd += "WHERE "; for ( int i = 0; i < meta.getKeyLookup().length; i++ ) { if ( i > 0 ) { sql_upd += "AND "; } sql_upd += databaseMeta.quoteField( meta.getKeyLookup()[i] ) + " = ?" + Const.CR; updateRowMeta.addValueMeta( inputRowMeta.getValueMeta( data.keynrs[i] ) ); } sql_upd += "AND " + databaseMeta.quoteField( meta.getVersionField() ) + " = ? "; updateRowMeta.addValueMeta( new ValueMetaInteger( meta.getVersionField() ) ); try { logDetailed( "Preparing update: " + Const.CR + sql_upd + Const.CR ); data.prepStatementUpdate = data.db.getConnection().prepareStatement( databaseMeta.stripCR( sql_upd ) ); } catch ( SQLException ex ) { throw new KettleDatabaseException( "Unable to prepare dimension update :" + Const.CR + sql_upd, ex ); } data.insertRowMeta = insertRowMeta; data.updateRowMeta = updateRowMeta; } Object[] insertRow = new Object[data.insertRowMeta.size()]; int insertIndex = 0; if ( !isAutoIncrement() ) { insertRow[insertIndex++] = technicalKey; } insertRow[insertIndex++] = versionNr; switch ( data.startDateChoice ) { case DimensionLookupMeta.START_DATE_ALTERNATIVE_NONE: insertRow[insertIndex++] = dateFrom; break; case DimensionLookupMeta.START_DATE_ALTERNATIVE_SYSDATE: insertRow[insertIndex++] = dateFrom; break; case DimensionLookupMeta.START_DATE_ALTERNATIVE_START_OF_TRANS: insertRow[insertIndex++] = getTrans().getStartDate(); break; case DimensionLookupMeta.START_DATE_ALTERNATIVE_NULL: insertRow[insertIndex++] = null; break; case DimensionLookupMeta.START_DATE_ALTERNATIVE_COLUMN_VALUE: insertRow[insertIndex++] = inputRowMeta.getDate( row, data.startDateFieldIndex ); break; default: throw new KettleStepException( BaseMessages.getString( PKG, "DimensionLookup.Exception.IllegalStartDateSelection", Integer.toString( data.startDateChoice ) ) ); } insertRow[insertIndex++] = dateTo; for ( int i = 0; i < data.keynrs.length; i++ ) { insertRow[insertIndex++] = row[data.keynrs[i]]; } for ( int i = 0; i < data.fieldnrs.length; i++ ) { if ( data.fieldnrs[i] >= 0 ) { insertRow[insertIndex++] = row[data.fieldnrs[i]]; } } for ( int i = 0; i < meta.getFieldUpdate().length; i++ ) { switch ( meta.getFieldUpdate()[i] ) { case DimensionLookupMeta.TYPE_UPDATE_DATE_INSUP: case DimensionLookupMeta.TYPE_UPDATE_DATE_INSERTED: insertRow[insertIndex++] = new Date(); break; case DimensionLookupMeta.TYPE_UPDATE_LAST_VERSION: insertRow[insertIndex++] = Boolean.TRUE; break; default: break; } } if ( isDebug() ) { logDebug( "rins, size=" + data.insertRowMeta.size() + ", values=" + data.insertRowMeta.getString( insertRow ) ); } data.db.setValues( data.insertRowMeta, insertRow, data.prepStatementInsert ); data.db.insertRow( data.prepStatementInsert ); if ( isDebug() ) { logDebug( "Row inserted!" ); } if ( technicalKey == null && databaseMeta.supportsAutoGeneratedKeys() ) { try { RowMetaAndData keys = data.db.getGeneratedKeys( data.prepStatementInsert ); if ( keys.getRowMeta().size() > 0 ) { technicalKey = keys.getRowMeta().getInteger( keys.getData(), 0 ); } else { throw new KettleDatabaseException( "Unable to retrieve value of auto-generated technical key : no value found!" ); } } catch ( Exception e ) { throw new KettleDatabaseException( "Unable to retrieve value of auto-generated technical key : unexpected error: ", e ); } } if ( !newEntry ) { Object[] updateRow = new Object[data.updateRowMeta.size()]; int updateIndex = 0; switch ( data.startDateChoice ) { case DimensionLookupMeta.START_DATE_ALTERNATIVE_NONE: updateRow[updateIndex++] = dateFrom; break; case DimensionLookupMeta.START_DATE_ALTERNATIVE_SYSDATE: updateRow[updateIndex++] = new Date(); break; case DimensionLookupMeta.START_DATE_ALTERNATIVE_START_OF_TRANS: updateRow[updateIndex++] = getTrans().getCurrentDate(); break; case DimensionLookupMeta.START_DATE_ALTERNATIVE_NULL: updateRow[updateIndex++] = null; break; case DimensionLookupMeta.START_DATE_ALTERNATIVE_COLUMN_VALUE: updateRow[updateIndex++] = inputRowMeta.getDate( row, data.startDateFieldIndex ); break; default: throw new KettleStepException( BaseMessages.getString( "DimensionLookup.Exception.IllegalStartDateSelection", Integer.toString( data.startDateChoice ) ) ); } for ( int i = 0; i < meta.getFieldUpdate().length; i++ ) { switch ( meta.getFieldUpdate()[i] ) { case DimensionLookupMeta.TYPE_UPDATE_DATE_INSUP: updateRow[updateIndex++] = new Date(); break; case DimensionLookupMeta.TYPE_UPDATE_LAST_VERSION: updateRow[updateIndex++] = Boolean.FALSE; break; case DimensionLookupMeta.TYPE_UPDATE_DATE_UPDATED: updateRow[updateIndex++] = new Date(); break; default: break; } } for ( int i = 0; i < data.keynrs.length; i++ ) { updateRow[updateIndex++] = row[data.keynrs[i]]; } updateRow[updateIndex++] = versionNr - 1; if ( isRowLevel() ) { logRowlevel( "UPDATE using rupd=" + data.updateRowMeta.getString( updateRow ) ); } data.db.setValues( data.updateRowMeta, updateRow, data.prepStatementUpdate ); if ( isDebug() ) { logDebug( "Values set for update (" + data.updateRowMeta.size() + ")" ); } data.db.insertRow( data.prepStatementUpdate ); if ( isDebug() ) { logDebug( "Row updated!" ); } } return technicalKey; } DimensionLookup( StepMeta stepMeta, StepDataInterface stepDataInterface, int copyNr, TransMeta transMeta,
    Trans trans ); @Override boolean processRow( StepMetaInterface smi, StepDataInterface sdi ); Long dimInsert( RowMetaInterface inputRowMeta, Object[] row, Long technicalKey, boolean newEntry,
    Long versionNr, Date dateFrom, Date dateTo ); @Override boolean isRowLevel(); @Override boolean isDebug(); void dimUpdate( RowMetaInterface rowMeta, Object[] row, Long dimkey, Date valueDate ); void dimPunchThrough( RowMetaInterface rowMeta, Object[] row ); void checkDimZero(); @Override boolean init( StepMetaInterface smi, StepDataInterface sdi ); @Override void dispose( StepMetaInterface smi, StepDataInterface sdi ); }
DimensionLookup extends BaseStep implements StepInterface { public Long dimInsert( RowMetaInterface inputRowMeta, Object[] row, Long technicalKey, boolean newEntry, Long versionNr, Date dateFrom, Date dateTo ) throws KettleException { DatabaseMeta databaseMeta = meta.getDatabaseMeta(); if ( data.prepStatementInsert == null && data.prepStatementUpdate == null ) { RowMetaInterface insertRowMeta = new RowMeta(); String sql = "INSERT INTO " + data.schemaTable + "( "; if ( !isAutoIncrement() ) { sql += databaseMeta.quoteField( meta.getKeyField() ) + ", "; insertRowMeta.addValueMeta( data.outputRowMeta.getValueMeta( inputRowMeta.size() ) ); } else { if ( databaseMeta.needsPlaceHolder() ) { sql += "0, "; } } sql += databaseMeta.quoteField( meta.getVersionField() ) + ", " + databaseMeta.quoteField( meta.getDateFrom() ) + ", " + databaseMeta.quoteField( meta.getDateTo() ); insertRowMeta.addValueMeta( new ValueMetaInteger( meta.getVersionField() ) ); insertRowMeta.addValueMeta( new ValueMetaDate( meta.getDateFrom() ) ); insertRowMeta.addValueMeta( new ValueMetaDate( meta.getDateTo() ) ); for ( int i = 0; i < meta.getKeyLookup().length; i++ ) { sql += ", " + databaseMeta.quoteField( meta.getKeyLookup()[i] ); insertRowMeta.addValueMeta( inputRowMeta.getValueMeta( data.keynrs[i] ) ); } for ( int i = 0; i < meta.getFieldLookup().length; i++ ) { if ( !DimensionLookupMeta.isUpdateTypeWithoutArgument( meta.isUpdate(), meta.getFieldUpdate()[i] ) ) { sql += ", " + databaseMeta.quoteField( meta.getFieldLookup()[i] ); insertRowMeta.addValueMeta( inputRowMeta.getValueMeta( data.fieldnrs[i] ) ); } } for ( int i = 0; i < meta.getFieldUpdate().length; i++ ) { ValueMetaInterface valueMeta = null; switch ( meta.getFieldUpdate()[i] ) { case DimensionLookupMeta.TYPE_UPDATE_DATE_INSUP: case DimensionLookupMeta.TYPE_UPDATE_DATE_INSERTED: valueMeta = new ValueMetaDate( meta.getFieldLookup()[i] ); break; case DimensionLookupMeta.TYPE_UPDATE_LAST_VERSION: valueMeta = new ValueMetaBoolean( meta.getFieldLookup()[i] ); break; default: break; } if ( valueMeta != null ) { sql += ", " + databaseMeta.quoteField( valueMeta.getName() ); insertRowMeta.addValueMeta( valueMeta ); } } sql += ") VALUES ("; if ( !isAutoIncrement() ) { sql += "?, "; } sql += "?, ?, ?"; for ( int i = 0; i < data.keynrs.length; i++ ) { sql += ", ?"; } for ( int i = 0; i < meta.getFieldLookup().length; i++ ) { if ( !DimensionLookupMeta.isUpdateTypeWithoutArgument( meta.isUpdate(), meta.getFieldUpdate()[i] ) ) { sql += ", ?"; } } for ( int i = 0; i < meta.getFieldUpdate().length; i++ ) { switch ( meta.getFieldUpdate()[i] ) { case DimensionLookupMeta.TYPE_UPDATE_DATE_INSUP: case DimensionLookupMeta.TYPE_UPDATE_DATE_INSERTED: case DimensionLookupMeta.TYPE_UPDATE_LAST_VERSION: sql += ", ?"; break; default: break; } } sql += " )"; try { if ( technicalKey == null && databaseMeta.supportsAutoGeneratedKeys() ) { logDetailed( "SQL w/ return keys=[" + sql + "]" ); data.prepStatementInsert = data.db.getConnection().prepareStatement( databaseMeta.stripCR( sql ), Statement.RETURN_GENERATED_KEYS ); } else { logDetailed( "SQL=[" + sql + "]" ); data.prepStatementInsert = data.db.getConnection().prepareStatement( databaseMeta.stripCR( sql ) ); } } catch ( SQLException ex ) { throw new KettleDatabaseException( "Unable to prepare dimension insert :" + Const.CR + sql, ex ); } RowMetaInterface updateRowMeta = new RowMeta(); String sql_upd = "UPDATE " + data.schemaTable + Const.CR; sql_upd += "SET " + databaseMeta.quoteField( meta.getDateTo() ) + " = ?" + Const.CR; updateRowMeta.addValueMeta( new ValueMetaDate( meta.getDateTo() ) ); for ( int i = 0; i < meta.getFieldUpdate().length; i++ ) { ValueMetaInterface valueMeta = null; switch ( meta.getFieldUpdate()[i] ) { case DimensionLookupMeta.TYPE_UPDATE_DATE_INSUP: case DimensionLookupMeta.TYPE_UPDATE_DATE_UPDATED: valueMeta = new ValueMetaDate( meta.getFieldLookup()[i] ); break; case DimensionLookupMeta.TYPE_UPDATE_LAST_VERSION: valueMeta = new ValueMetaBoolean( meta.getFieldLookup()[i] ); break; default: break; } if ( valueMeta != null ) { sql_upd += ", " + databaseMeta.quoteField( valueMeta.getName() ) + " = ?" + Const.CR; updateRowMeta.addValueMeta( valueMeta ); } } sql_upd += "WHERE "; for ( int i = 0; i < meta.getKeyLookup().length; i++ ) { if ( i > 0 ) { sql_upd += "AND "; } sql_upd += databaseMeta.quoteField( meta.getKeyLookup()[i] ) + " = ?" + Const.CR; updateRowMeta.addValueMeta( inputRowMeta.getValueMeta( data.keynrs[i] ) ); } sql_upd += "AND " + databaseMeta.quoteField( meta.getVersionField() ) + " = ? "; updateRowMeta.addValueMeta( new ValueMetaInteger( meta.getVersionField() ) ); try { logDetailed( "Preparing update: " + Const.CR + sql_upd + Const.CR ); data.prepStatementUpdate = data.db.getConnection().prepareStatement( databaseMeta.stripCR( sql_upd ) ); } catch ( SQLException ex ) { throw new KettleDatabaseException( "Unable to prepare dimension update :" + Const.CR + sql_upd, ex ); } data.insertRowMeta = insertRowMeta; data.updateRowMeta = updateRowMeta; } Object[] insertRow = new Object[data.insertRowMeta.size()]; int insertIndex = 0; if ( !isAutoIncrement() ) { insertRow[insertIndex++] = technicalKey; } insertRow[insertIndex++] = versionNr; switch ( data.startDateChoice ) { case DimensionLookupMeta.START_DATE_ALTERNATIVE_NONE: insertRow[insertIndex++] = dateFrom; break; case DimensionLookupMeta.START_DATE_ALTERNATIVE_SYSDATE: insertRow[insertIndex++] = dateFrom; break; case DimensionLookupMeta.START_DATE_ALTERNATIVE_START_OF_TRANS: insertRow[insertIndex++] = getTrans().getStartDate(); break; case DimensionLookupMeta.START_DATE_ALTERNATIVE_NULL: insertRow[insertIndex++] = null; break; case DimensionLookupMeta.START_DATE_ALTERNATIVE_COLUMN_VALUE: insertRow[insertIndex++] = inputRowMeta.getDate( row, data.startDateFieldIndex ); break; default: throw new KettleStepException( BaseMessages.getString( PKG, "DimensionLookup.Exception.IllegalStartDateSelection", Integer.toString( data.startDateChoice ) ) ); } insertRow[insertIndex++] = dateTo; for ( int i = 0; i < data.keynrs.length; i++ ) { insertRow[insertIndex++] = row[data.keynrs[i]]; } for ( int i = 0; i < data.fieldnrs.length; i++ ) { if ( data.fieldnrs[i] >= 0 ) { insertRow[insertIndex++] = row[data.fieldnrs[i]]; } } for ( int i = 0; i < meta.getFieldUpdate().length; i++ ) { switch ( meta.getFieldUpdate()[i] ) { case DimensionLookupMeta.TYPE_UPDATE_DATE_INSUP: case DimensionLookupMeta.TYPE_UPDATE_DATE_INSERTED: insertRow[insertIndex++] = new Date(); break; case DimensionLookupMeta.TYPE_UPDATE_LAST_VERSION: insertRow[insertIndex++] = Boolean.TRUE; break; default: break; } } if ( isDebug() ) { logDebug( "rins, size=" + data.insertRowMeta.size() + ", values=" + data.insertRowMeta.getString( insertRow ) ); } data.db.setValues( data.insertRowMeta, insertRow, data.prepStatementInsert ); data.db.insertRow( data.prepStatementInsert ); if ( isDebug() ) { logDebug( "Row inserted!" ); } if ( technicalKey == null && databaseMeta.supportsAutoGeneratedKeys() ) { try { RowMetaAndData keys = data.db.getGeneratedKeys( data.prepStatementInsert ); if ( keys.getRowMeta().size() > 0 ) { technicalKey = keys.getRowMeta().getInteger( keys.getData(), 0 ); } else { throw new KettleDatabaseException( "Unable to retrieve value of auto-generated technical key : no value found!" ); } } catch ( Exception e ) { throw new KettleDatabaseException( "Unable to retrieve value of auto-generated technical key : unexpected error: ", e ); } } if ( !newEntry ) { Object[] updateRow = new Object[data.updateRowMeta.size()]; int updateIndex = 0; switch ( data.startDateChoice ) { case DimensionLookupMeta.START_DATE_ALTERNATIVE_NONE: updateRow[updateIndex++] = dateFrom; break; case DimensionLookupMeta.START_DATE_ALTERNATIVE_SYSDATE: updateRow[updateIndex++] = new Date(); break; case DimensionLookupMeta.START_DATE_ALTERNATIVE_START_OF_TRANS: updateRow[updateIndex++] = getTrans().getCurrentDate(); break; case DimensionLookupMeta.START_DATE_ALTERNATIVE_NULL: updateRow[updateIndex++] = null; break; case DimensionLookupMeta.START_DATE_ALTERNATIVE_COLUMN_VALUE: updateRow[updateIndex++] = inputRowMeta.getDate( row, data.startDateFieldIndex ); break; default: throw new KettleStepException( BaseMessages.getString( "DimensionLookup.Exception.IllegalStartDateSelection", Integer.toString( data.startDateChoice ) ) ); } for ( int i = 0; i < meta.getFieldUpdate().length; i++ ) { switch ( meta.getFieldUpdate()[i] ) { case DimensionLookupMeta.TYPE_UPDATE_DATE_INSUP: updateRow[updateIndex++] = new Date(); break; case DimensionLookupMeta.TYPE_UPDATE_LAST_VERSION: updateRow[updateIndex++] = Boolean.FALSE; break; case DimensionLookupMeta.TYPE_UPDATE_DATE_UPDATED: updateRow[updateIndex++] = new Date(); break; default: break; } } for ( int i = 0; i < data.keynrs.length; i++ ) { updateRow[updateIndex++] = row[data.keynrs[i]]; } updateRow[updateIndex++] = versionNr - 1; if ( isRowLevel() ) { logRowlevel( "UPDATE using rupd=" + data.updateRowMeta.getString( updateRow ) ); } data.db.setValues( data.updateRowMeta, updateRow, data.prepStatementUpdate ); if ( isDebug() ) { logDebug( "Values set for update (" + data.updateRowMeta.size() + ")" ); } data.db.insertRow( data.prepStatementUpdate ); if ( isDebug() ) { logDebug( "Row updated!" ); } } return technicalKey; } DimensionLookup( StepMeta stepMeta, StepDataInterface stepDataInterface, int copyNr, TransMeta transMeta,
    Trans trans ); @Override boolean processRow( StepMetaInterface smi, StepDataInterface sdi ); Long dimInsert( RowMetaInterface inputRowMeta, Object[] row, Long technicalKey, boolean newEntry,
    Long versionNr, Date dateFrom, Date dateTo ); @Override boolean isRowLevel(); @Override boolean isDebug(); void dimUpdate( RowMetaInterface rowMeta, Object[] row, Long dimkey, Date valueDate ); void dimPunchThrough( RowMetaInterface rowMeta, Object[] row ); void checkDimZero(); @Override boolean init( StepMetaInterface smi, StepDataInterface sdi ); @Override void dispose( StepMetaInterface smi, StepDataInterface sdi );  }