Search in sources :

Example 21 with ValueMetaDate

use of org.pentaho.di.core.row.value.ValueMetaDate in project pentaho-kettle by pentaho.

the class DimensionLookup method dimPunchThrough.

// This updates all versions of a dimension entry.
// 
public void dimPunchThrough(RowMetaInterface rowMeta, Object[] row) throws KettleDatabaseException {
    if (data.prepStatementPunchThrough == null) {
        // first time: construct prepared statement
        DatabaseMeta databaseMeta = meta.getDatabaseMeta();
        data.punchThroughRowMeta = new RowMeta();
        /*
       * UPDATE table SET punchv1 = fieldx, ... , last_updated = <now> WHERE keylookup[] = keynrs[] ;
       */
        String sql_upd = "UPDATE " + data.schemaTable + Const.CR;
        sql_upd += "SET ";
        boolean first = true;
        for (int i = 0; i < meta.getFieldLookup().length; i++) {
            if (meta.getFieldUpdate()[i] == DimensionLookupMeta.TYPE_UPDATE_DIM_PUNCHTHROUGH) {
                if (!first) {
                    sql_upd += ", ";
                } else {
                    sql_upd += "  ";
                }
                first = false;
                sql_upd += databaseMeta.quoteField(meta.getFieldLookup()[i]) + " = ?" + Const.CR;
                data.punchThroughRowMeta.addValueMeta(rowMeta.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_UPDATED:
                    valueMeta = new ValueMetaDate(meta.getFieldLookup()[i]);
                    break;
                default:
                    break;
            }
            if (valueMeta != null) {
                sql_upd += ", " + databaseMeta.quoteField(valueMeta.getName()) + " = ?" + Const.CR;
                data.punchThroughRowMeta.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;
            data.punchThroughRowMeta.addValueMeta(rowMeta.getValueMeta(data.keynrs[i]));
        }
        try {
            data.prepStatementPunchThrough = data.db.getConnection().prepareStatement(meta.getDatabaseMeta().stripCR(sql_upd));
        } catch (SQLException ex) {
            throw new KettleDatabaseException("Unable to prepare dimension punchThrough update statement : " + Const.CR + sql_upd, ex);
        }
    }
    Object[] punchThroughRow = new Object[data.punchThroughRowMeta.size()];
    int punchIndex = 0;
    for (int i = 0; i < meta.getFieldLookup().length; i++) {
        if (meta.getFieldUpdate()[i] == DimensionLookupMeta.TYPE_UPDATE_DIM_PUNCHTHROUGH) {
            punchThroughRow[punchIndex++] = 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_UPDATED:
                punchThroughRow[punchIndex++] = new Date();
                break;
            default:
                break;
        }
    }
    for (int i = 0; i < data.keynrs.length; i++) {
        punchThroughRow[punchIndex++] = row[data.keynrs[i]];
    }
    // UPDATE VALUES
    // set values for
    data.db.setValues(data.punchThroughRowMeta, punchThroughRow, data.prepStatementPunchThrough);
    // update
    // do the actual punch through update
    data.db.insertRow(data.prepStatementPunchThrough);
}
Also used : RowMeta(org.pentaho.di.core.row.RowMeta) SQLException(java.sql.SQLException) KettleDatabaseException(org.pentaho.di.core.exception.KettleDatabaseException) DatabaseMeta(org.pentaho.di.core.database.DatabaseMeta) ValueMetaDate(org.pentaho.di.core.row.value.ValueMetaDate) Date(java.util.Date) ValueMetaDate(org.pentaho.di.core.row.value.ValueMetaDate) ValueMetaInterface(org.pentaho.di.core.row.ValueMetaInterface)

Example 22 with ValueMetaDate

use of org.pentaho.di.core.row.value.ValueMetaDate in project pentaho-kettle by pentaho.

the class DimensionLookup method dimInsert.

/**
 * This inserts new record into dimension Optionally, if the entry already exists, update date range from previous
 * version of the entry.
 */
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) {
        // first time: construct prepared statement
        RowMetaInterface insertRowMeta = new RowMeta();
        /*
       * Construct the SQL statement...
       *
       * INSERT INTO d_customer(keyfield, versionfield, datefrom, dateto, key[], fieldlookup[], last_updated,
       * last_inserted, last_version) VALUES (val_key ,val_version , val_datfrom, val_datto, keynrs[], fieldnrs[],
       * last_updated, last_inserted, last_version) ;
       */
        String sql = "INSERT INTO " + data.schemaTable + "( ";
        if (!isAutoIncrement()) {
            // NO
            sql += databaseMeta.quoteField(meta.getKeyField()) + ", ";
            // AUTOINCREMENT
            // the first return value
            insertRowMeta.addValueMeta(data.outputRowMeta.getValueMeta(inputRowMeta.size()));
        // after the input
        } else {
            if (databaseMeta.needsPlaceHolder()) {
                // placeholder on informix!
                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));
            }
        // pstmt=con.prepareStatement(sql, new String[] { "klant_tk" } );
        } catch (SQLException ex) {
            throw new KettleDatabaseException("Unable to prepare dimension insert :" + Const.CR + sql, ex);
        }
        /*
       * UPDATE d_customer SET dateto = val_datnow, last_updated = <now> last_version = false WHERE keylookup[] =
       * keynrs[] AND versionfield = val_version - 1 ;
       */
        RowMetaInterface updateRowMeta = new RowMeta();
        String sql_upd = "UPDATE " + data.schemaTable + Const.CR;
        // The end of the date range
        // 
        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;
    }
    // Caller is responsible for setting proper version number depending
    // on if newEntry == true
    insertRow[insertIndex++] = versionNr;
    switch(data.startDateChoice) {
        case DimensionLookupMeta.START_DATE_ALTERNATIVE_NONE:
            insertRow[insertIndex++] = dateFrom;
            break;
        case DimensionLookupMeta.START_DATE_ALTERNATIVE_SYSDATE:
            // use the time the step execution begins as the date from (passed in as dateFrom).
            // before, the current system time was used. this caused an exclusion of the row in the
            // lookup portion of the step that uses this 'valueDate' and not the current time.
            // the result was multiple inserts for what should have been 1 [PDI-4317]
            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) {
            // Ignore last_version, last_updated, etc. These are handled below...
            // 
            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;
                // Always the last version on insert.
                break;
            default:
                break;
        }
    }
    if (isDebug()) {
        logDebug("rins, size=" + data.insertRowMeta.size() + ", values=" + data.insertRowMeta.getString(insertRow));
    }
    // INSERT NEW VALUE!
    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) {
        // we have to update the previous version in the dimension!
        /*
       * UPDATE d_customer SET dateto = val_datfrom , last_updated = <now> , last_version = false WHERE keylookup[] =
       * keynrs[] AND versionfield = val_version - 1 ;
       */
        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;
                    // Never the last version on this update
                    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));
        }
        // UPDATE VALUES
        // set values for update
        // 
        data.db.setValues(data.updateRowMeta, updateRow, data.prepStatementUpdate);
        if (isDebug()) {
            logDebug("Values set for update (" + data.updateRowMeta.size() + ")");
        }
        // do the actual update
        data.db.insertRow(data.prepStatementUpdate);
        if (isDebug()) {
            logDebug("Row updated!");
        }
    }
    return technicalKey;
}
Also used : KettleStepException(org.pentaho.di.core.exception.KettleStepException) RowMeta(org.pentaho.di.core.row.RowMeta) SQLException(java.sql.SQLException) KettleDatabaseException(org.pentaho.di.core.exception.KettleDatabaseException) RowMetaInterface(org.pentaho.di.core.row.RowMetaInterface) ValueMetaBoolean(org.pentaho.di.core.row.value.ValueMetaBoolean) DatabaseMeta(org.pentaho.di.core.database.DatabaseMeta) Date(java.util.Date) ValueMetaDate(org.pentaho.di.core.row.value.ValueMetaDate) KettleException(org.pentaho.di.core.exception.KettleException) KettleDatabaseException(org.pentaho.di.core.exception.KettleDatabaseException) SQLException(java.sql.SQLException) KettleValueException(org.pentaho.di.core.exception.KettleValueException) KettleStepException(org.pentaho.di.core.exception.KettleStepException) ValueMetaInterface(org.pentaho.di.core.row.ValueMetaInterface) RowMetaAndData(org.pentaho.di.core.RowMetaAndData) ValueMetaInteger(org.pentaho.di.core.row.value.ValueMetaInteger) ValueMetaDate(org.pentaho.di.core.row.value.ValueMetaDate)

Example 23 with ValueMetaDate

use of org.pentaho.di.core.row.value.ValueMetaDate in project pentaho-kettle by pentaho.

the class ExcelInputMeta method getFields.

@Override
public void getFields(RowMetaInterface row, String name, RowMetaInterface[] info, StepMeta nextStep, VariableSpace space, Repository repository, IMetaStore metaStore) throws KettleStepException {
    for (int i = 0; i < field.length; i++) {
        int type = field[i].getType();
        if (type == ValueMetaInterface.TYPE_NONE) {
            type = ValueMetaInterface.TYPE_STRING;
        }
        try {
            ValueMetaInterface v = ValueMetaFactory.createValueMeta(field[i].getName(), type);
            v.setLength(field[i].getLength());
            v.setPrecision(field[i].getPrecision());
            v.setOrigin(name);
            v.setConversionMask(field[i].getFormat());
            v.setDecimalSymbol(field[i].getDecimalSymbol());
            v.setGroupingSymbol(field[i].getGroupSymbol());
            v.setCurrencySymbol(field[i].getCurrencySymbol());
            row.addValueMeta(v);
        } catch (Exception e) {
            throw new KettleStepException(e);
        }
    }
    if (fileField != null && fileField.length() > 0) {
        ValueMetaInterface v = new ValueMetaString(fileField);
        v.setLength(250);
        v.setPrecision(-1);
        v.setOrigin(name);
        row.addValueMeta(v);
    }
    if (sheetField != null && sheetField.length() > 0) {
        ValueMetaInterface v = new ValueMetaString(sheetField);
        v.setLength(250);
        v.setPrecision(-1);
        v.setOrigin(name);
        row.addValueMeta(v);
    }
    if (sheetRowNumberField != null && sheetRowNumberField.length() > 0) {
        ValueMetaInterface v = new ValueMetaInteger(sheetRowNumberField);
        v.setLength(ValueMetaInterface.DEFAULT_INTEGER_LENGTH, 0);
        v.setOrigin(name);
        row.addValueMeta(v);
    }
    if (rowNumberField != null && rowNumberField.length() > 0) {
        ValueMetaInterface v = new ValueMetaInteger(rowNumberField);
        v.setLength(ValueMetaInterface.DEFAULT_INTEGER_LENGTH, 0);
        v.setOrigin(name);
        row.addValueMeta(v);
    }
    if (getShortFileNameField() != null && getShortFileNameField().length() > 0) {
        ValueMetaInterface v = new ValueMetaString(space.environmentSubstitute(getShortFileNameField()));
        v.setLength(100, -1);
        v.setOrigin(name);
        row.addValueMeta(v);
    }
    if (getExtensionField() != null && getExtensionField().length() > 0) {
        ValueMetaInterface v = new ValueMetaString(space.environmentSubstitute(getExtensionField()));
        v.setLength(100, -1);
        v.setOrigin(name);
        row.addValueMeta(v);
    }
    if (getPathField() != null && getPathField().length() > 0) {
        ValueMetaInterface v = new ValueMetaString(space.environmentSubstitute(getPathField()));
        v.setLength(100, -1);
        v.setOrigin(name);
        row.addValueMeta(v);
    }
    if (getSizeField() != null && getSizeField().length() > 0) {
        ValueMetaInterface v = new ValueMetaInteger(space.environmentSubstitute(getSizeField()));
        v.setOrigin(name);
        v.setLength(9);
        row.addValueMeta(v);
    }
    if (isHiddenField() != null && isHiddenField().length() > 0) {
        ValueMetaInterface v = new ValueMetaBoolean(space.environmentSubstitute(isHiddenField()));
        v.setOrigin(name);
        row.addValueMeta(v);
    }
    if (getLastModificationDateField() != null && getLastModificationDateField().length() > 0) {
        ValueMetaInterface v = new ValueMetaDate(space.environmentSubstitute(getLastModificationDateField()));
        v.setOrigin(name);
        row.addValueMeta(v);
    }
    if (getUriField() != null && getUriField().length() > 0) {
        ValueMetaInterface v = new ValueMetaString(space.environmentSubstitute(getUriField()));
        v.setLength(100, -1);
        v.setOrigin(name);
        row.addValueMeta(v);
    }
    if (getRootUriField() != null && getRootUriField().length() > 0) {
        ValueMetaInterface v = new ValueMetaString(space.environmentSubstitute(getRootUriField()));
        v.setLength(100, -1);
        v.setOrigin(name);
        row.addValueMeta(v);
    }
}
Also used : ValueMetaString(org.pentaho.di.core.row.value.ValueMetaString) KettleStepException(org.pentaho.di.core.exception.KettleStepException) ValueMetaInteger(org.pentaho.di.core.row.value.ValueMetaInteger) ValueMetaBoolean(org.pentaho.di.core.row.value.ValueMetaBoolean) ValueMetaDate(org.pentaho.di.core.row.value.ValueMetaDate) KettleException(org.pentaho.di.core.exception.KettleException) KettleXMLException(org.pentaho.di.core.exception.KettleXMLException) KettlePluginException(org.pentaho.di.core.exception.KettlePluginException) KettleStepException(org.pentaho.di.core.exception.KettleStepException) ValueMetaInterface(org.pentaho.di.core.row.ValueMetaInterface)

Example 24 with ValueMetaDate

use of org.pentaho.di.core.row.value.ValueMetaDate in project pentaho-kettle by pentaho.

the class MondrianHelper method createRectangularOutput.

/**
 * Outputs one row per tuple on the rows axis.
 *
 * @throws KettleDatabaseException
 *           in case some or other error occurs
 */
public void createRectangularOutput() throws KettleDatabaseException {
    final Axis[] axes = result.getAxes();
    if (axes.length != 2) {
        throw new KettleDatabaseException(BaseMessages.getString(PKG, "MondrianInputErrorOnlyTabular"));
    }
    headings = new ArrayList<>();
    rows = new ArrayList<>();
    final Axis rowsAxis = axes[1];
    final Axis columnsAxis = axes[0];
    int rowOrdinal = -1;
    int[] coords = { 0, 0 };
    for (Position rowPos : rowsAxis.getPositions()) {
        ++rowOrdinal;
        coords[1] = rowOrdinal;
        if (rowOrdinal == 0) {
            // First headings are for the members on the rows axis.
            for (Member rowMember : rowPos) {
                headings.add(rowMember.getHierarchy().getUniqueName());
            }
            // concatenate the unique names.
            for (Position columnPos : columnsAxis.getPositions()) {
                String heading = "";
                for (Member columnMember : columnPos) {
                    if (!heading.equals("")) {
                        heading += ", ";
                    }
                    heading += columnMember.getUniqueName();
                }
                headings.add(heading);
            }
        }
        List<Object> rowValues = new ArrayList<>();
        // The first row values describe the members on the rows axis.
        for (Member rowMember : rowPos) {
            rowValues.add(rowMember.getUniqueName());
        }
        // NOTE: Could also output all properties of each cell.
        for (int columnOrdinal = 0; columnOrdinal < columnsAxis.getPositions().size(); ++columnOrdinal) {
            coords[0] = columnOrdinal;
            final Cell cell = result.getCell(coords);
            rowValues.add(cell.getValue());
        }
        rows.add(rowValues);
    }
    outputRowMeta = new RowMeta();
    // column, keep scanning until we find one line that has an actual value
    if (rows.size() > 0) {
        int columnCount = rows.get(0).size();
        HashMap<Integer, ValueMetaInterface> valueMetaHash = new HashMap<>();
        for (int i = 0; i < rows.size(); i++) {
            List<Object> rowValues = rows.get(i);
            for (int c = 0; c < rowValues.size(); c++) {
                if (valueMetaHash.containsKey(new Integer(c))) {
                    // we have this value already
                    continue;
                }
                Object valueData = rowValues.get(c);
                if (valueData == null) {
                    // skip this row and look for the metadata in a new one
                    continue;
                }
                String valueName = headings.get(c);
                ValueMetaInterface valueMeta;
                if (valueData instanceof String) {
                    valueMeta = new ValueMetaString(valueName);
                } else if (valueData instanceof Date) {
                    valueMeta = new ValueMetaDate(valueName);
                } else if (valueData instanceof Boolean) {
                    valueMeta = new ValueMetaBoolean(valueName);
                } else if (valueData instanceof Integer) {
                    valueMeta = new ValueMetaInteger(valueName);
                    valueData = Long.valueOf(((Integer) valueData).longValue());
                } else if (valueData instanceof Short) {
                    valueMeta = new ValueMetaInteger(valueName);
                    valueData = Long.valueOf(((Short) valueData).longValue());
                } else if (valueData instanceof Byte) {
                    valueMeta = new ValueMetaInteger(valueName);
                    valueData = Long.valueOf(((Byte) valueData).longValue());
                } else if (valueData instanceof Long) {
                    valueMeta = new ValueMetaInteger(valueName);
                } else if (valueData instanceof Double) {
                    valueMeta = new ValueMetaNumber(valueName);
                } else if (valueData instanceof Float) {
                    valueMeta = new ValueMetaNumber(valueName);
                    valueData = Double.valueOf(((Float) valueData).doubleValue());
                } else if (valueData instanceof BigDecimal) {
                    valueMeta = new ValueMetaBigNumber(valueName);
                } else {
                    throw new KettleDatabaseException(BaseMessages.getString(PKG, "MondrianInputErrorUnhandledType", valueData.getClass().toString()));
                }
                valueMetaHash.put(c, valueMeta);
            }
            if (valueMetaHash.size() == columnCount) {
                // we're done
                break;
            }
        }
        // Build the list of valueMetas
        List<ValueMetaInterface> valueMetaList = new ArrayList<>();
        for (int c = 0; c < columnCount; c++) {
            if (valueMetaHash.containsKey(new Integer(c))) {
                valueMetaList.add(valueMetaHash.get(new Integer(c)));
            } else {
                // If the entire column is null, assume the missing data as String.
                // Irrelevant, anyway
                ValueMetaInterface valueMeta = new ValueMetaString(headings.get(c));
                valueMetaList.add(valueMeta);
            }
        }
        outputRowMeta.setValueMetaList(valueMetaList);
    }
    // Now that we painstakingly found the meta data that comes out of the
    // Mondrian database, cache it please...
    // 
    DBCacheEntry cacheEntry = new DBCacheEntry(databaseMeta.getName(), queryString);
    DBCache.getInstance().put(cacheEntry, outputRowMeta);
}
Also used : RowMeta(org.pentaho.di.core.row.RowMeta) HashMap(java.util.HashMap) KettleDatabaseException(org.pentaho.di.core.exception.KettleDatabaseException) ArrayList(java.util.ArrayList) ValueMetaBoolean(org.pentaho.di.core.row.value.ValueMetaBoolean) ValueMetaString(org.pentaho.di.core.row.value.ValueMetaString) ValueMetaNumber(org.pentaho.di.core.row.value.ValueMetaNumber) ValueMetaBigNumber(org.pentaho.di.core.row.value.ValueMetaBigNumber) ValueMetaInteger(org.pentaho.di.core.row.value.ValueMetaInteger) ValueMetaBoolean(org.pentaho.di.core.row.value.ValueMetaBoolean) Member(mondrian.olap.Member) Cell(mondrian.olap.Cell) ValueMetaDate(org.pentaho.di.core.row.value.ValueMetaDate) Axis(mondrian.olap.Axis) ValueMetaString(org.pentaho.di.core.row.value.ValueMetaString) Position(mondrian.olap.Position) DBCacheEntry(org.pentaho.di.core.DBCacheEntry) Date(java.util.Date) ValueMetaDate(org.pentaho.di.core.row.value.ValueMetaDate) BigDecimal(java.math.BigDecimal) ValueMetaInterface(org.pentaho.di.core.row.ValueMetaInterface) ValueMetaInteger(org.pentaho.di.core.row.value.ValueMetaInteger)

Example 25 with ValueMetaDate

use of org.pentaho.di.core.row.value.ValueMetaDate in project pentaho-kettle by pentaho.

the class MonetDBBulkLoader method init.

@Override
public boolean init(StepMetaInterface smi, StepDataInterface sdi) {
    meta = (MonetDBBulkLoaderMeta) smi;
    data = (MonetDBBulkLoaderData) sdi;
    if (super.init(smi, sdi)) {
        data.quote = environmentSubstitute(meta.getFieldEnclosure());
        data.separator = environmentSubstitute(meta.getFieldSeparator());
        String nulls = environmentSubstitute(meta.getNULLrepresentation());
        if (nulls == null) {
            data.nullrepresentation = new String();
        } else {
            data.nullrepresentation = nulls;
        }
        data.newline = Const.CR;
        String encoding = environmentSubstitute(meta.getEncoding());
        data.monetDateMeta = new ValueMetaDate("dateMeta");
        data.monetDateMeta.setConversionMask("yyyy/MM/dd");
        data.monetDateMeta.setStringEncoding(encoding);
        data.monetTimestampMeta = new ValueMetaDate("timestampMeta");
        data.monetTimestampMeta.setConversionMask("yyyy/MM/dd HH:mm:ss");
        data.monetTimestampMeta.setStringEncoding(encoding);
        data.monetTimeMeta = new ValueMetaDate("timeMeta");
        data.monetTimeMeta.setConversionMask("HH:mm:ss");
        data.monetTimeMeta.setStringEncoding(encoding);
        data.monetNumberMeta = new ValueMetaNumber("numberMeta");
        data.monetNumberMeta.setConversionMask("#.#");
        data.monetNumberMeta.setGroupingSymbol(",");
        data.monetNumberMeta.setDecimalSymbol(".");
        data.monetNumberMeta.setStringEncoding(encoding);
        data.bufferSize = Const.toInt(environmentSubstitute(meta.getBufferSize()), 100000);
        // Allocate the buffer
        // 
        // new byte[data.bufferSize][];
        data.rowBuffer = new String[data.bufferSize];
        data.bufferIndex = 0;
        // Make sure our database connection settings are consistent with our dialog settings by
        // altering the connection with an updated answer depending on the dialog setting.
        meta.getDatabaseMeta().setQuoteAllFields(meta.isFullyQuoteSQL());
        // Support parameterized database connection names
        String connectionName = meta.getDbConnectionName();
        if (!Utils.isEmpty(connectionName) && connectionName.startsWith("${") && connectionName.endsWith("}")) {
            meta.setDatabaseMeta(localTransMeta.findDatabase(environmentSubstitute(connectionName)));
        }
        // Schema-table combination...
        data.schemaTable = meta.getDatabaseMeta(this).getQuotedSchemaTableCombination(environmentSubstitute(meta.getSchemaName()), environmentSubstitute(meta.getTableName()));
        return true;
    }
    return false;
}
Also used : ValueMetaNumber(org.pentaho.di.core.row.value.ValueMetaNumber) ValueMetaDate(org.pentaho.di.core.row.value.ValueMetaDate)

Aggregations

ValueMetaDate (org.pentaho.di.core.row.value.ValueMetaDate)104 ValueMetaInteger (org.pentaho.di.core.row.value.ValueMetaInteger)63 ValueMetaString (org.pentaho.di.core.row.value.ValueMetaString)59 Test (org.junit.Test)50 ValueMetaBoolean (org.pentaho.di.core.row.value.ValueMetaBoolean)44 RowMeta (org.pentaho.di.core.row.RowMeta)43 ValueMetaNumber (org.pentaho.di.core.row.value.ValueMetaNumber)34 ValueMetaInterface (org.pentaho.di.core.row.ValueMetaInterface)33 RowMetaInterface (org.pentaho.di.core.row.RowMetaInterface)25 ValueMetaBigNumber (org.pentaho.di.core.row.value.ValueMetaBigNumber)23 ValueMetaTimestamp (org.pentaho.di.core.row.value.ValueMetaTimestamp)21 Calendar (java.util.Calendar)17 Date (java.util.Date)17 KettleException (org.pentaho.di.core.exception.KettleException)15 ValueMetaInternetAddress (org.pentaho.di.core.row.value.ValueMetaInternetAddress)15 RowMetaAndData (org.pentaho.di.core.RowMetaAndData)14 ValueMetaBinary (org.pentaho.di.core.row.value.ValueMetaBinary)13 KettleDatabaseException (org.pentaho.di.core.exception.KettleDatabaseException)10 KettleStepException (org.pentaho.di.core.exception.KettleStepException)10 SQLException (java.sql.SQLException)8