Examples with SelectValuesMeta org.pentaho.di.trans.steps.selectvalues.SelectValuesMeta used on opensource projects

Example 1 with SelectValuesMeta

use of org.pentaho.di.trans.steps.selectvalues.SelectValuesMeta in project pentaho-kettle by pentaho.

the class SpoonDBDelegate method copyTable.

public boolean copyTable(DatabaseMeta sourceDBInfo, DatabaseMeta targetDBInfo, String tablename) {
    try {
        // 
        // Create a new transformation...
        // 
        TransMeta meta = new TransMeta();
        meta.addDatabase(sourceDBInfo);
        meta.addDatabase(targetDBInfo);
        // 
        // Add a note
        // 
        String note = BaseMessages.getString(PKG, "Spoon.Message.Note.ReadInformationFromTableOnDB", tablename, sourceDBInfo.getDatabaseName()) + // "Reads information from table ["+tablename+"]
        Const.CR;
        // on database ["+sourceDBInfo+"]"
        note += BaseMessages.getString(PKG, "Spoon.Message.Note.WriteInformationToTableOnDB", tablename, targetDBInfo.getDatabaseName());
        // the information to table
        // ["+tablename+"] on
        // database
        // ["+targetDBInfo+"]"
        NotePadMeta ni = new NotePadMeta(note, 150, 10, -1, -1);
        meta.addNote(ni);
        // 
        // create the source step...
        // 
        // "read
        String fromstepname = BaseMessages.getString(PKG, "Spoon.Message.Note.ReadFromTable", tablename);
        // from
        // ["+tablename+"]";
        TableInputMeta tii = new TableInputMeta();
        tii.setDatabaseMeta(sourceDBInfo);
        tii.setSQL("SELECT * FROM " + tablename);
        PluginRegistry registry = PluginRegistry.getInstance();
        String fromstepid = registry.getPluginId(StepPluginType.class, tii);
        StepMeta fromstep = new StepMeta(fromstepid, fromstepname, tii);
        fromstep.setLocation(150, 100);
        fromstep.setDraw(true);
        fromstep.setDescription(BaseMessages.getString(PKG, "Spoon.Message.Note.ReadInformationFromTableOnDB", tablename, sourceDBInfo.getDatabaseName()));
        meta.addStep(fromstep);
        // 
        // add logic to rename fields in case any of the field names contain
        // reserved words...
        // Use metadata logic in SelectValues, use SelectValueInfo...
        // 
        Database sourceDB = new Database(loggingObject, sourceDBInfo);
        sourceDB.shareVariablesWith(meta);
        sourceDB.connect();
        try {
            // Get the fields for the input table...
            RowMetaInterface fields = sourceDB.getTableFields(tablename);
            // See if we need to deal with reserved words...
            int nrReserved = targetDBInfo.getNrReservedWords(fields);
            if (nrReserved > 0) {
                SelectValuesMeta svi = new SelectValuesMeta();
                svi.allocate(0, 0, nrReserved);
                int nr = 0;
                // CHECKSTYLE:Indentation:OFF
                for (int i = 0; i < fields.size(); i++) {
                    ValueMetaInterface v = fields.getValueMeta(i);
                    if (targetDBInfo.isReservedWord(v.getName())) {
                        if (svi.getMeta()[nr] == null) {
                            svi.getMeta()[nr] = new SelectMetadataChange(svi);
                        }
                        svi.getMeta()[nr].setName(v.getName());
                        svi.getMeta()[nr].setRename(targetDBInfo.quoteField(v.getName()));
                        nr++;
                    }
                }
                String selstepname = BaseMessages.getString(PKG, "Spoon.Message.Note.HandleReservedWords");
                String selstepid = registry.getPluginId(StepPluginType.class, svi);
                StepMeta selstep = new StepMeta(selstepid, selstepname, svi);
                selstep.setLocation(350, 100);
                selstep.setDraw(true);
                selstep.setDescription(BaseMessages.getString(PKG, "Spoon.Message.Note.RenamesReservedWords", // 
                targetDBInfo.getPluginId()));
                meta.addStep(selstep);
                TransHopMeta shi = new TransHopMeta(fromstep, selstep);
                meta.addTransHop(shi);
                fromstep = selstep;
            }
            // 
            // Create the target step...
            // 
            // 
            // Add the TableOutputMeta step...
            // 
            String tostepname = BaseMessages.getString(PKG, "Spoon.Message.Note.WriteToTable", tablename);
            TableOutputMeta toi = new TableOutputMeta();
            toi.setDatabaseMeta(targetDBInfo);
            toi.setTableName(tablename);
            toi.setCommitSize(200);
            toi.setTruncateTable(true);
            String tostepid = registry.getPluginId(StepPluginType.class, toi);
            StepMeta tostep = new StepMeta(tostepid, tostepname, toi);
            tostep.setLocation(550, 100);
            tostep.setDraw(true);
            tostep.setDescription(BaseMessages.getString(PKG, "Spoon.Message.Note.WriteInformationToTableOnDB2", tablename, targetDBInfo.getDatabaseName()));
            meta.addStep(tostep);
            // 
            // Add a hop between the two steps...
            // 
            TransHopMeta hi = new TransHopMeta(fromstep, tostep);
            meta.addTransHop(hi);
            // OK, if we're still here: overwrite the current transformation...
            // Set a name on this generated transformation
            // 
            String name = "Copy table from [" + sourceDBInfo.getName() + "] to [" + targetDBInfo.getName() + "]";
            String transName = name;
            int nr = 1;
            if (spoon.delegates.trans.getTransformation(transName) != null) {
                nr++;
                transName = name + " " + nr;
            }
            meta.setName(transName);
            spoon.delegates.trans.addTransGraph(meta);
            spoon.refreshGraph();
            spoon.refreshTree();
        } finally {
            sourceDB.disconnect();
        }
    } catch (Exception e) {
        new ErrorDialog(spoon.getShell(), BaseMessages.getString(PKG, "Spoon.Dialog.UnexpectedError.Title"), BaseMessages.getString(PKG, "Spoon.Dialog.UnexpectedError.Message"), new KettleException(e.getMessage(), e));
        return false;
    }
    return true;
}

Example 2 with SelectValuesMeta

use of org.pentaho.di.trans.steps.selectvalues.SelectValuesMeta in project pentaho-kettle by pentaho.

the class Spoon method generateFieldMapping.

/**
 * Create a new SelectValues step in between this step and the previous. If the previous fields are not there, no
 * mapping can be made, same with the required fields.
 *
 * @param stepMeta
 *          The target step to map against.
 */
// retry of required fields acquisition
public void generateFieldMapping(TransMeta transMeta, StepMeta stepMeta) {
    try {
        if (stepMeta != null) {
            StepMetaInterface smi = stepMeta.getStepMetaInterface();
            RowMetaInterface targetFields = smi.getRequiredFields(transMeta);
            RowMetaInterface sourceFields = transMeta.getPrevStepFields(stepMeta);
            // Build the mapping: let the user decide!!
            String[] source = sourceFields.getFieldNames();
            for (int i = 0; i < source.length; i++) {
                ValueMetaInterface v = sourceFields.getValueMeta(i);
                source[i] += EnterMappingDialog.STRING_ORIGIN_SEPARATOR + v.getOrigin() + ")";
            }
            String[] target = targetFields.getFieldNames();
            EnterMappingDialog dialog = new EnterMappingDialog(shell, source, target);
            List<SourceToTargetMapping> mappings = dialog.open();
            if (mappings != null) {
                // OK, so we now know which field maps where.
                // This allows us to generate the mapping using a
                // SelectValues Step...
                SelectValuesMeta svm = new SelectValuesMeta();
                svm.allocate(mappings.size(), 0, 0);
                // CHECKSTYLE:Indentation:OFF
                for (int i = 0; i < mappings.size(); i++) {
                    SourceToTargetMapping mapping = mappings.get(i);
                    svm.getSelectFields()[i].setName(sourceFields.getValueMeta(mapping.getSourcePosition()).getName());
                    svm.getSelectFields()[i].setRename(target[mapping.getTargetPosition()]);
                    svm.getSelectFields()[i].setLength(-1);
                    svm.getSelectFields()[i].setPrecision(-1);
                }
                // a new comment. Sincerely yours CO ;)
                // Now that we have the meta-data, create a new step info object
                String stepName = stepMeta.getName() + " Mapping";
                // if
                stepName = transMeta.getAlternativeStepname(stepName);
                // it's already there, rename it.
                StepMeta newStep = new StepMeta("SelectValues", stepName, svm);
                newStep.setLocation(stepMeta.getLocation().x + 20, stepMeta.getLocation().y + 20);
                newStep.setDraw(true);
                transMeta.addStep(newStep);
                addUndoNew(transMeta, new StepMeta[] { newStep }, new int[] { transMeta.indexOfStep(newStep) });
                // Redraw stuff...
                refreshTree();
                refreshGraph();
            }
        } else {
            throw new KettleException("There is no target to do a field mapping against!");
        }
    } catch (KettleException e) {
        new ErrorDialog(shell, "Error creating mapping", "There was an error when Kettle tried to generate a field mapping against the target step", e);
    }
}

Example 3 with SelectValuesMeta

use of org.pentaho.di.trans.steps.selectvalues.SelectValuesMeta in project pentaho-metaverse by pentaho.

the class MetaverseValidationIT method testSelectValuesStep.

@Test
public void testSelectValuesStep() throws Exception {
    // this tests a specific select values step. the one in trans "Populate Table From File"
    SelectValuesTransStepNode selectValues = root.getSelectValuesStepNode();
    assertNotNull(selectValues);
    int countUses = getIterableSize(selectValues.getStreamFieldNodesUses());
    int countOutputs = getIterableSize(selectValues.getOutputStreamFields());
    int countInputs = getIterableSize(selectValues.getInputStreamFields());
    assertEquals(9, countUses);
    SelectValuesMeta meta = (SelectValuesMeta) getStepMeta(selectValues);
    assertEquals(getExpectedOutputFieldCount(meta), countOutputs);
    assertEquals(9, countInputs);
    assertEquals("Select values", selectValues.getStepType());
    for (StreamFieldNode node : selectValues.getOutputStreamFields()) {
        // check for operations
        if (node.getOperations() != null) {
            Operations ops = MetaverseUtil.convertOperationsStringToMap(node.getOperations());
            assertNotNull(ops);
            List<IOperation> metadataOps = ops.get(ChangeType.METADATA);
            assertNotNull(metadataOps);
            assertTrue(metadataOps.size() > 0);
            // there should not be any data operations on nodes touched by this step
            assertNull(ops.get(ChangeType.DATA));
        }
        // check the created node is derived from something
        Iterable<StreamFieldNode> deriveNodes = node.getFieldNodesThatDeriveMe();
        for (StreamFieldNode deriveNode : deriveNodes) {
            assertNotNull(deriveNode);
        }
    }
}

Example 4 with SelectValuesMeta

use of org.pentaho.di.trans.steps.selectvalues.SelectValuesMeta in project pdi-dataservice-server-plugin by pentaho.

the class SqlTransGenerator method generateSelectStep.

private StepMeta generateSelectStep() {
    List<SQLField> fields = sql.getSelectFields().getFields();
    SelectValuesMeta meta = new SelectValuesMeta();
    meta.allocate(fields.size(), 0, 0);
    String[] selectNames = new String[fields.size()];
    String[] selectRename = new String[fields.size()];
    for (int i = 0; i < fields.size(); i++) {
        SQLField sqlField = fields.get(i);
        if (sqlField.getAggregation() == null) {
            selectNames[i] = sqlField.getField();
            selectRename[i] = sqlField.getAlias();
        } else {
            // agg field names are assigned in the group by
            selectNames[i] = Const.NVL(sqlField.getAlias(), sqlField.getField());
        }
    }
    meta.setSelectName(selectNames);
    meta.setSelectRename(selectRename);
    StepMeta stepMeta = new StepMeta("Select values", meta);
    stepMeta.setLocation(xLocation, 50);
    xLocation += 100;
    stepMeta.setDraw(true);
    return stepMeta;
}

Example 5 with SelectValuesMeta

use of org.pentaho.di.trans.steps.selectvalues.SelectValuesMeta in project pdi-dataservice-server-plugin by pentaho.

the class SqlTransGenerator method generateConversionStep.

private StepMeta generateConversionStep() {
    // Set conversion masks for each column, modify type where required.
    SelectValuesMeta meta = new SelectValuesMeta();
    meta.allocate(0, 0, serviceFields.size());
    meta.setMeta(serviceFields.getValueMetaList().stream().map(valueMeta -> getSelectMetadataChange(meta, valueMeta)).collect(Collectors.toList()).toArray(new SelectMetadataChange[serviceFields.size()]));
    StepMeta stepMeta = new StepMeta("Set Conversion", meta);
    stepMeta.setLocation(xLocation, 50);
    xLocation += 100;
    stepMeta.setDraw(true);
    return stepMeta;
}