Examples with ValueMetaDate org.pentaho.di.core.row.value.ValueMetaDate used on opensource projects

Example 56 with ValueMetaDate

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

the class DimensionLookup method dimUpdate.

public void dimUpdate(RowMetaInterface rowMeta, Object[] row, Long dimkey, Date valueDate) throws KettleDatabaseException {
    if (data.prepStatementDimensionUpdate == null) {
        // first time: construct prepared statement
        // 
        data.dimensionUpdateRowMeta = new RowMeta();
        // Construct the SQL statement...
        /*
       * UPDATE d_customer SET fieldlookup[] = row.getValue(fieldnrs) , last_updated = <now> WHERE returnkey = dimkey ;
       */
        String sql = "UPDATE " + data.schemaTable + Const.CR + "SET ";
        boolean comma = false;
        for (int i = 0; i < meta.getFieldLookup().length; i++) {
            if (!DimensionLookupMeta.isUpdateTypeWithoutArgument(meta.isUpdate(), meta.getFieldUpdate()[i])) {
                if (comma) {
                    sql += ", ";
                } else {
                    sql += "  ";
                }
                comma = true;
                sql += meta.getDatabaseMeta().quoteField(meta.getFieldLookup()[i]) + " = ?" + Const.CR;
                data.dimensionUpdateRowMeta.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) {
                if (comma) {
                    sql += ", ";
                } else {
                    sql += "  ";
                }
                comma = true;
                sql += meta.getDatabaseMeta().quoteField(valueMeta.getName()) + " = ?" + Const.CR;
                data.dimensionUpdateRowMeta.addValueMeta(valueMeta);
            }
        }
        sql += "WHERE  " + meta.getDatabaseMeta().quoteField(meta.getKeyField()) + " = ?";
        data.dimensionUpdateRowMeta.addValueMeta(// The tk
        new ValueMetaInteger(meta.getKeyField()));
        try {
            if (isDebug()) {
                logDebug("Preparing statement: [" + sql + "]");
            }
            data.prepStatementDimensionUpdate = data.db.getConnection().prepareStatement(meta.getDatabaseMeta().stripCR(sql));
        } catch (SQLException ex) {
            throw new KettleDatabaseException("Couldn't prepare statement :" + Const.CR + sql, ex);
        }
    }
    // Assemble information
    // New
    Object[] dimensionUpdateRow = new Object[data.dimensionUpdateRowMeta.size()];
    int updateIndex = 0;
    for (int i = 0; i < data.fieldnrs.length; i++) {
        // 
        if (data.fieldnrs[i] >= 0) {
            dimensionUpdateRow[updateIndex++] = 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:
                dimensionUpdateRow[updateIndex++] = valueDate;
                break;
            default:
                break;
        }
    }
    dimensionUpdateRow[updateIndex++] = dimkey;
    data.db.setValues(data.dimensionUpdateRowMeta, dimensionUpdateRow, data.prepStatementDimensionUpdate);
    data.db.insertRow(data.prepStatementDimensionUpdate);
}

Example 57 with ValueMetaDate

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

the class DimensionLookupMeta method getSQLStatements.

@Override
public SQLStatement getSQLStatements(TransMeta transMeta, StepMeta stepMeta, RowMetaInterface prev, Repository repository, IMetaStore metaStore) {
    // default: nothing to do!
    SQLStatement retval = new SQLStatement(stepMeta.getName(), databaseMeta, null);
    if (update) {
        // Only bother in case of update, not lookup!
        logDebug(BaseMessages.getString(PKG, "DimensionLookupMeta.Log.Update"));
        if (databaseMeta != null) {
            if (prev != null && prev.size() > 0) {
                String schemaTable = databaseMeta.getQuotedSchemaTableCombination(schemaName, tableName);
                if (!Utils.isEmpty(schemaTable)) {
                    Database db = createDatabaseObject();
                    db.shareVariablesWith(transMeta);
                    try {
                        db.connect();
                        String sql = "";
                        // How does the table look like?
                        // 
                        RowMetaInterface fields = new RowMeta();
                        // First the technical key
                        // 
                        ValueMetaInterface vkeyfield = new ValueMetaInteger(keyField);
                        vkeyfield.setLength(10);
                        fields.addValueMeta(vkeyfield);
                        // The the version
                        // 
                        ValueMetaInterface vversion = new ValueMetaInteger(versionField);
                        vversion.setLength(5);
                        fields.addValueMeta(vversion);
                        // The date from
                        // 
                        ValueMetaInterface vdatefrom = new ValueMetaDate(dateFrom);
                        fields.addValueMeta(vdatefrom);
                        // The date to
                        // 
                        ValueMetaInterface vdateto = new ValueMetaDate(dateTo);
                        fields.addValueMeta(vdateto);
                        String errors = "";
                        // 
                        for (int i = 0; i < keyLookup.length; i++) {
                            ValueMetaInterface vprev = prev.searchValueMeta(keyStream[i]);
                            if (vprev != null) {
                                ValueMetaInterface field = vprev.clone();
                                field.setName(keyLookup[i]);
                                fields.addValueMeta(field);
                            } else {
                                if (errors.length() > 0) {
                                    errors += ", ";
                                }
                                errors += keyStream[i];
                            }
                        }
                        // 
                        for (int i = 0; i < fieldLookup.length; i++) {
                            ValueMetaInterface vprev = prev.searchValueMeta(fieldStream[i]);
                            if (vprev != null) {
                                ValueMetaInterface field = vprev.clone();
                                field.setName(fieldLookup[i]);
                                fields.addValueMeta(field);
                            } else {
                                if (errors.length() > 0) {
                                    errors += ", ";
                                }
                                errors += fieldStream[i];
                            }
                        }
                        // 
                        for (int i = 0; i < fieldUpdate.length; i++) {
                            ValueMetaInterface valueMeta = null;
                            switch(fieldUpdate[i]) {
                                case TYPE_UPDATE_DATE_INSUP:
                                case TYPE_UPDATE_DATE_INSERTED:
                                case TYPE_UPDATE_DATE_UPDATED:
                                    valueMeta = new ValueMetaDate(fieldLookup[i]);
                                    break;
                                case TYPE_UPDATE_LAST_VERSION:
                                    valueMeta = new ValueMetaBoolean(fieldLookup[i]);
                                    break;
                                default:
                                    break;
                            }
                            if (valueMeta != null) {
                                fields.addValueMeta(valueMeta);
                            }
                        }
                        if (errors.length() > 0) {
                            retval.setError(BaseMessages.getString(PKG, "DimensionLookupMeta.ReturnValue.UnableToFindFields") + errors);
                        }
                        logDebug(BaseMessages.getString(PKG, "DimensionLookupMeta.Log.GetDDLForTable") + schemaTable + "] : " + fields.toStringMeta());
                        sql += db.getDDL(schemaTable, fields, (sequenceName != null && sequenceName.length() != 0) ? null : keyField, autoIncrement, null, true);
                        logDebug("sql =" + sql);
                        String[] idx_fields = null;
                        // Key lookup dimensions...
                        if (!Utils.isEmpty(keyLookup)) {
                            idx_fields = new String[keyLookup.length];
                            for (int i = 0; i < keyLookup.length; i++) {
                                idx_fields[i] = keyLookup[i];
                            }
                        } else {
                            retval.setError(BaseMessages.getString(PKG, "DimensionLookupMeta.ReturnValue.NoKeyFieldsSpecified"));
                        }
                        if (!Utils.isEmpty(idx_fields) && !db.checkIndexExists(schemaTable, idx_fields)) {
                            String indexname = "idx_" + tableName + "_lookup";
                            sql += db.getCreateIndexStatement(schemaTable, indexname, idx_fields, false, false, false, true);
                        }
                        // (Bitmap) index on technical key
                        idx_fields = new String[] { keyField };
                        if (!Utils.isEmpty(keyField)) {
                            if (!db.checkIndexExists(schemaTable, idx_fields)) {
                                String indexname = "idx_" + tableName + "_tk";
                                sql += db.getCreateIndexStatement(schemaTable, indexname, idx_fields, true, false, true, true);
                            }
                        } else {
                            retval.setError(BaseMessages.getString(PKG, "DimensionLookupMeta.ReturnValue.TechnicalKeyFieldRequired"));
                        }
                        // The optional Oracle sequence
                        if (CREATION_METHOD_SEQUENCE.equals(getTechKeyCreation()) && !Utils.isEmpty(sequenceName)) {
                            if (!db.checkSequenceExists(schemaName, sequenceName)) {
                                sql += db.getCreateSequenceStatement(schemaName, sequenceName, 1L, 1L, -1L, true);
                            }
                        }
                        if (sql.length() == 0) {
                            retval.setSQL(null);
                        } else {
                            retval.setSQL(transMeta.environmentSubstitute(sql));
                        }
                    } catch (KettleDatabaseException dbe) {
                        retval.setError(BaseMessages.getString(PKG, "DimensionLookupMeta.ReturnValue.ErrorOccurred") + dbe.getMessage());
                    } finally {
                        db.disconnect();
                    }
                } else {
                    retval.setError(BaseMessages.getString(PKG, "DimensionLookupMeta.ReturnValue.NoTableDefinedOnConnection"));
                }
            } else {
                retval.setError(BaseMessages.getString(PKG, "DimensionLookupMeta.ReturnValue.NotReceivingAnyFields"));
            }
        } else {
            retval.setError(BaseMessages.getString(PKG, "DimensionLookupMeta.ReturnValue.NoConnectionDefiendInStep"));
        }
    }
    return retval;
}

Example 58 with ValueMetaDate

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

the class MySQLBulkLoader method init.

@Override
public boolean init(StepMetaInterface smi, StepDataInterface sdi) {
    meta = (MySQLBulkLoaderMeta) smi;
    data = (MySQLBulkLoaderData) sdi;
    if (super.init(smi, sdi)) {
        if (Utils.isEmpty(meta.getEnclosure())) {
            data.quote = new byte[] {};
        } else {
            data.quote = environmentSubstitute(meta.getEnclosure()).getBytes();
        }
        if (Utils.isEmpty(meta.getDelimiter())) {
            data.separator = "\t".getBytes();
        } else {
            data.separator = environmentSubstitute(meta.getDelimiter()).getBytes();
        }
        data.newline = Const.CR.getBytes();
        String realEncoding = environmentSubstitute(meta.getEncoding());
        data.bulkTimestampMeta = new ValueMetaDate("timestampMeta");
        data.bulkTimestampMeta.setConversionMask("yyyy-MM-dd HH:mm:ss");
        data.bulkTimestampMeta.setStringEncoding(realEncoding);
        data.bulkDateMeta = new ValueMetaDate("dateMeta");
        data.bulkDateMeta.setConversionMask("yyyy-MM-dd");
        data.bulkDateMeta.setStringEncoding(realEncoding);
        data.bulkNumberMeta = new ValueMetaNumber("numberMeta");
        data.bulkNumberMeta.setConversionMask("#.#");
        data.bulkNumberMeta.setGroupingSymbol(",");
        data.bulkNumberMeta.setDecimalSymbol(".");
        data.bulkNumberMeta.setStringEncoding(realEncoding);
        data.bulkSize = Const.toLong(environmentSubstitute(meta.getBulkSize()), -1L);
        // Schema-table combination...
        data.schemaTable = meta.getDatabaseMeta().getQuotedSchemaTableCombination(environmentSubstitute(meta.getSchemaName()), environmentSubstitute(meta.getTableName()));
        return true;
    }
    return false;
}

Example 59 with ValueMetaDate

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

the class BaseStep method getLogFields.

/**
 * Gets the log fields.
 *
 * @param comm the comm
 * @return the log fields
 */
public static final RowMetaInterface getLogFields(String comm) {
    RowMetaInterface r = new RowMeta();
    ValueMetaInterface sname = new ValueMetaString(BaseMessages.getString(PKG, "BaseStep.ColumnName.Stepname"));
    sname.setLength(256);
    r.addValueMeta(sname);
    r.addValueMeta(new ValueMetaNumber(BaseMessages.getString(PKG, "BaseStep.ColumnName.Copy")));
    r.addValueMeta(new ValueMetaNumber(BaseMessages.getString(PKG, "BaseStep.ColumnName.LinesReaded")));
    r.addValueMeta(new ValueMetaNumber(BaseMessages.getString(PKG, "BaseStep.ColumnName.LinesWritten")));
    r.addValueMeta(new ValueMetaNumber(BaseMessages.getString(PKG, "BaseStep.ColumnName.LinesUpdated")));
    r.addValueMeta(new ValueMetaNumber(BaseMessages.getString(PKG, "BaseStep.ColumnName.LinesSkipped")));
    r.addValueMeta(new ValueMetaNumber(BaseMessages.getString(PKG, "BaseStep.ColumnName.Errors")));
    r.addValueMeta(new ValueMetaDate(BaseMessages.getString(PKG, "BaseStep.ColumnName.StartDate")));
    r.addValueMeta(new ValueMetaDate(BaseMessages.getString(PKG, "BaseStep.ColumnName.EndDate")));
    for (int i = 0; i < r.size(); i++) {
        r.getValueMeta(i).setOrigin(comm);
    }
    return r;
}

Example 60 with ValueMetaDate

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

the class GetFileNamesMeta method getFields.

@Override
public void getFields(RowMetaInterface row, String name, RowMetaInterface[] info, StepMeta nextStep, VariableSpace space, Repository repository, IMetaStore metaStore) throws KettleStepException {
    // the filename
    ValueMetaInterface filename = new ValueMetaString("filename");
    filename.setLength(500);
    filename.setPrecision(-1);
    filename.setOrigin(name);
    row.addValueMeta(filename);
    // the short filename
    ValueMetaInterface short_filename = new ValueMetaString("short_filename");
    short_filename.setLength(500);
    short_filename.setPrecision(-1);
    short_filename.setOrigin(name);
    row.addValueMeta(short_filename);
    // the path
    ValueMetaInterface path = new ValueMetaString("path");
    path.setLength(500);
    path.setPrecision(-1);
    path.setOrigin(name);
    row.addValueMeta(path);
    // the type
    ValueMetaInterface type = new ValueMetaString("type");
    type.setLength(500);
    type.setPrecision(-1);
    type.setOrigin(name);
    row.addValueMeta(type);
    // the exists
    ValueMetaInterface exists = new ValueMetaBoolean("exists");
    exists.setOrigin(name);
    row.addValueMeta(exists);
    // the ishidden
    ValueMetaInterface ishidden = new ValueMetaBoolean("ishidden");
    ishidden.setOrigin(name);
    row.addValueMeta(ishidden);
    // the isreadable
    ValueMetaInterface isreadable = new ValueMetaBoolean("isreadable");
    isreadable.setOrigin(name);
    row.addValueMeta(isreadable);
    // the iswriteable
    ValueMetaInterface iswriteable = new ValueMetaBoolean("iswriteable");
    iswriteable.setOrigin(name);
    row.addValueMeta(iswriteable);
    // the lastmodifiedtime
    ValueMetaInterface lastmodifiedtime = new ValueMetaDate("lastmodifiedtime");
    lastmodifiedtime.setOrigin(name);
    row.addValueMeta(lastmodifiedtime);
    // the size
    ValueMetaInterface size = new ValueMetaInteger("size");
    size.setOrigin(name);
    row.addValueMeta(size);
    // the extension
    ValueMetaInterface extension = new ValueMetaString("extension");
    extension.setOrigin(name);
    row.addValueMeta(extension);
    // the uri
    ValueMetaInterface uri = new ValueMetaString("uri");
    uri.setOrigin(name);
    row.addValueMeta(uri);
    // the rooturi
    ValueMetaInterface rooturi = new ValueMetaString("rooturi");
    rooturi.setOrigin(name);
    row.addValueMeta(rooturi);
    if (includeRowNumber) {
        ValueMetaInterface v = new ValueMetaInteger(space.environmentSubstitute(rowNumberField));
        v.setLength(ValueMetaInterface.DEFAULT_INTEGER_LENGTH, 0);
        v.setOrigin(name);
        row.addValueMeta(v);
    }
}