Examples with Database org.pentaho.di.core.database.Database used on opensource projects

Example 81 with Database

use of org.pentaho.di.core.database.Database in project pentaho-kettle by pentaho.

the class SynchronizeAfterMergeMeta method check.

public void check(List<CheckResultInterface> remarks, TransMeta transMeta, StepMeta stepMeta, RowMetaInterface prev, String[] input, String[] output, RowMetaInterface info, VariableSpace space, Repository repository, IMetaStore metaStore) {
    CheckResult cr;
    String error_message = "";
    if (databaseMeta != null) {
        Database db = new Database(loggingObject, databaseMeta);
        db.shareVariablesWith(transMeta);
        try {
            db.connect();
            if (!Utils.isEmpty(tableName)) {
                cr = new CheckResult(CheckResult.TYPE_RESULT_OK, BaseMessages.getString(PKG, "SynchronizeAfterMergeMeta.CheckResult.TableNameOK"), stepMeta);
                remarks.add(cr);
                boolean first = true;
                boolean error_found = false;
                error_message = "";
                // Check fields in table
                RowMetaInterface r = db.getTableFieldsMeta(schemaName, tableName);
                if (r != null) {
                    cr = new CheckResult(CheckResult.TYPE_RESULT_OK, BaseMessages.getString(PKG, "SynchronizeAfterMergeMeta.CheckResult.TableExists"), stepMeta);
                    remarks.add(cr);
                    for (int i = 0; i < keyLookup.length; i++) {
                        String lufield = keyLookup[i];
                        ValueMetaInterface v = r.searchValueMeta(lufield);
                        if (v == null) {
                            if (first) {
                                first = false;
                                error_message += BaseMessages.getString(PKG, "SynchronizeAfterMergeMeta.CheckResult.MissingCompareFieldsInTargetTable") + Const.CR;
                            }
                            error_found = true;
                            error_message += "\t\t" + lufield + Const.CR;
                        }
                    }
                    if (error_found) {
                        cr = new CheckResult(CheckResult.TYPE_RESULT_ERROR, error_message, stepMeta);
                    } else {
                        cr = new CheckResult(CheckResult.TYPE_RESULT_OK, BaseMessages.getString(PKG, "SynchronizeAfterMergeMeta.CheckResult.AllLookupFieldsFound"), stepMeta);
                    }
                    remarks.add(cr);
                    // How about the fields to insert/update in the table?
                    first = true;
                    error_found = false;
                    error_message = "";
                    for (int i = 0; i < updateLookup.length; i++) {
                        String lufield = updateLookup[i];
                        ValueMetaInterface v = r.searchValueMeta(lufield);
                        if (v == null) {
                            if (first) {
                                first = false;
                                error_message += BaseMessages.getString(PKG, "SynchronizeAfterMergeMeta.CheckResult.MissingFieldsToUpdateInTargetTable") + Const.CR;
                            }
                            error_found = true;
                            error_message += "\t\t" + lufield + Const.CR;
                        }
                    }
                    if (error_found) {
                        cr = new CheckResult(CheckResult.TYPE_RESULT_ERROR, error_message, stepMeta);
                    } else {
                        cr = new CheckResult(CheckResult.TYPE_RESULT_OK, BaseMessages.getString(PKG, "SynchronizeAfterMergeMeta.CheckResult.AllFieldsToUpdateFoundInTargetTable"), stepMeta);
                    }
                    remarks.add(cr);
                } else {
                    error_message = BaseMessages.getString(PKG, "SynchronizeAfterMergeMeta.CheckResult.CouldNotReadTableInfo");
                    cr = new CheckResult(CheckResult.TYPE_RESULT_ERROR, error_message, stepMeta);
                    remarks.add(cr);
                }
            }
            // Look up fields in the input stream <prev>
            if (prev != null && prev.size() > 0) {
                cr = new CheckResult(CheckResult.TYPE_RESULT_OK, BaseMessages.getString(PKG, "SynchronizeAfterMergeMeta.CheckResult.StepReceivingDatas", prev.size() + ""), stepMeta);
                remarks.add(cr);
                boolean first = true;
                error_message = "";
                boolean error_found = false;
                for (int i = 0; i < keyStream.length; i++) {
                    ValueMetaInterface v = prev.searchValueMeta(keyStream[i]);
                    if (v == null) {
                        if (first) {
                            first = false;
                            error_message += BaseMessages.getString(PKG, "SynchronizeAfterMergeMeta.CheckResult.MissingFieldsInInput") + Const.CR;
                        }
                        error_found = true;
                        error_message += "\t\t" + keyStream[i] + Const.CR;
                    }
                }
                for (int i = 0; i < keyStream2.length; i++) {
                    if (keyStream2[i] != null && keyStream2[i].length() > 0) {
                        ValueMetaInterface v = prev.searchValueMeta(keyStream2[i]);
                        if (v == null) {
                            if (first) {
                                first = false;
                                error_message += BaseMessages.getString(PKG, "SynchronizeAfterMergeMeta.CheckResult.MissingFieldsInInput") + Const.CR;
                            }
                            error_found = true;
                            error_message += "\t\t" + keyStream[i] + Const.CR;
                        }
                    }
                }
                if (error_found) {
                    cr = new CheckResult(CheckResult.TYPE_RESULT_ERROR, error_message, stepMeta);
                } else {
                    cr = new CheckResult(CheckResult.TYPE_RESULT_OK, BaseMessages.getString(PKG, "SynchronizeAfterMergeMeta.CheckResult.AllFieldsFoundInInput"), stepMeta);
                }
                remarks.add(cr);
                // How about the fields to insert/update the table with?
                first = true;
                error_found = false;
                error_message = "";
                for (int i = 0; i < updateStream.length; i++) {
                    String lufield = updateStream[i];
                    ValueMetaInterface v = prev.searchValueMeta(lufield);
                    if (v == null) {
                        if (first) {
                            first = false;
                            error_message += BaseMessages.getString(PKG, "SynchronizeAfterMergeMeta.CheckResult.MissingInputStreamFields") + Const.CR;
                        }
                        error_found = true;
                        error_message += "\t\t" + lufield + Const.CR;
                    }
                }
                if (error_found) {
                    cr = new CheckResult(CheckResult.TYPE_RESULT_ERROR, error_message, stepMeta);
                } else {
                    cr = new CheckResult(CheckResult.TYPE_RESULT_OK, BaseMessages.getString(PKG, "SynchronizeAfterMergeMeta.CheckResult.AllFieldsFoundInInput2"), stepMeta);
                    remarks.add(cr);
                }
                // --------------------------> check fields in stream and tables (type)
                // Check fields in table
                String errorMsgDiffField = "";
                boolean errorDiffLenField = false;
                String errorMsgDiffLenField = "";
                boolean errorDiffField = false;
                RowMetaInterface r = db.getTableFieldsMeta(schemaName, tableName);
                if (r != null) {
                    for (int i = 0; i < updateStream.length; i++) {
                        String lufieldstream = updateStream[i];
                        String lufieldtable = updateLookup[i];
                        // get value from previous
                        ValueMetaInterface vs = prev.searchValueMeta(lufieldstream);
                        // get value from table fields
                        ValueMetaInterface vt = r.searchValueMeta(lufieldtable);
                        if (vs != null && vt != null) {
                            if (!vs.getTypeDesc().equalsIgnoreCase(vt.getTypeDesc())) {
                                errorMsgDiffField += Const.CR + "The input field [" + vs.getName() + "] ( Type=" + vs.getTypeDesc() + ") is not the same as the type in the target table (Type=" + vt.getTypeDesc() + ")" + Const.CR;
                                errorDiffField = true;
                            } else {
                                // check Length
                                if ((vt.getLength() < vs.getLength() || vs.getLength() == -1) && vt.getLength() != -1) {
                                    errorMsgDiffLenField += Const.CR + "The input field [" + vs.getName() + "] " + "(" + vs.getTypeDesc() + ")" + " has a length (" + vs.getLength() + ")" + " that is higher than that in the target table (" + vt.getLength() + ")." + Const.CR;
                                    errorDiffLenField = true;
                                }
                            }
                        }
                    }
                    // add error/Warning
                    if (errorDiffField) {
                        errorMsgDiffField = BaseMessages.getString(PKG, "SynchronizeAfterMergeMeta.CheckResult.FieldsTypeDifferent") + Const.CR + errorMsgDiffField;
                        cr = new CheckResult(CheckResult.TYPE_RESULT_ERROR, errorMsgDiffField, stepMeta);
                    }
                    if (errorDiffLenField) {
                        errorMsgDiffLenField = BaseMessages.getString(PKG, "SynchronizeAfterMergeMeta.CheckResult.FieldsLenDifferent") + Const.CR + errorMsgDiffLenField;
                        cr = new CheckResult(CheckResult.TYPE_RESULT_WARNING, errorMsgDiffLenField, stepMeta);
                    }
                    remarks.add(cr);
                }
            // --------------------------> check fields in stream and tables (type)
            } else {
                error_message = BaseMessages.getString(PKG, "SynchronizeAfterMergeMeta.CheckResult.MissingFieldsInInput3") + Const.CR;
                cr = new CheckResult(CheckResult.TYPE_RESULT_ERROR, error_message, stepMeta);
                remarks.add(cr);
            }
        } catch (KettleException e) {
            error_message = BaseMessages.getString(PKG, "SynchronizeAfterMergeMeta.CheckResult.DatabaseErrorOccurred") + e.getMessage();
            cr = new CheckResult(CheckResult.TYPE_RESULT_ERROR, error_message, stepMeta);
            remarks.add(cr);
        } finally {
            db.disconnect();
        }
    } else {
        error_message = BaseMessages.getString(PKG, "SynchronizeAfterMergeMeta.CheckResult.InvalidConnection");
        cr = new CheckResult(CheckResult.TYPE_RESULT_ERROR, error_message, stepMeta);
        remarks.add(cr);
    }
    // See if we have input streams leading to this step!
    if (input.length > 0) {
        cr = new CheckResult(CheckResult.TYPE_RESULT_OK, BaseMessages.getString(PKG, "SynchronizeAfterMergeMeta.CheckResult.StepReceivingInfoFromOtherSteps"), stepMeta);
        remarks.add(cr);
    } else {
        cr = new CheckResult(CheckResult.TYPE_RESULT_ERROR, BaseMessages.getString(PKG, "SynchronizeAfterMergeMeta.CheckResult.NoInputError"), stepMeta);
        remarks.add(cr);
    }
}

Example 82 with Database

use of org.pentaho.di.core.database.Database in project pentaho-kettle by pentaho.

the class SynchronizeAfterMergeMeta method getSQLStatements.

public SQLStatement getSQLStatements(TransMeta transMeta, StepMeta stepMeta, RowMetaInterface prev, Repository repository, IMetaStore metaStore) throws KettleStepException {
    // default: nothing to do!
    SQLStatement retval = new SQLStatement(stepMeta.getName(), databaseMeta, null);
    if (databaseMeta != null) {
        if (prev != null && prev.size() > 0) {
            // Copy the row
            RowMetaInterface tableFields = new RowMeta();
            // the key fields
            if (keyLookup != null) {
                for (int i = 0; i < keyLookup.length; i++) {
                    ValueMetaInterface v = prev.searchValueMeta(keyStream[i]);
                    if (v != null) {
                        ValueMetaInterface tableField = v.clone();
                        tableField.setName(keyLookup[i]);
                        tableFields.addValueMeta(tableField);
                    } else {
                        throw new KettleStepException("Unable to find field [" + keyStream[i] + "] in the input rows");
                    }
                }
            }
            // the lookup fields
            for (int i = 0; i < updateLookup.length; i++) {
                ValueMetaInterface v = prev.searchValueMeta(updateStream[i]);
                if (v != null) {
                    ValueMetaInterface vk = tableFields.searchValueMeta(updateStream[i]);
                    if (vk == null) {
                        // do not add again when already added as key fields
                        ValueMetaInterface tableField = v.clone();
                        tableField.setName(updateLookup[i]);
                        tableFields.addValueMeta(tableField);
                    }
                } else {
                    throw new KettleStepException("Unable to find field [" + updateStream[i] + "] in the input rows");
                }
            }
            if (!Utils.isEmpty(tableName)) {
                Database db = new Database(loggingObject, databaseMeta);
                try {
                    db.connect();
                    String schemaTable = databaseMeta.getQuotedSchemaTableCombination(schemaName, tableName);
                    String cr_table = db.getDDL(schemaTable, tableFields, null, false, null, true);
                    String cr_index = "";
                    String[] idx_fields = null;
                    if (keyLookup != null && keyLookup.length > 0) {
                        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, "SynchronizeAfterMergeMeta.CheckResult.MissingKeyFields"));
                    }
                    // Key lookup dimensions...
                    if (idx_fields != null && idx_fields.length > 0 && !db.checkIndexExists(schemaName, tableName, idx_fields)) {
                        String indexname = "idx_" + tableName + "_lookup";
                        cr_index = db.getCreateIndexStatement(schemaTable, indexname, idx_fields, false, false, false, true);
                    }
                    String sql = cr_table + cr_index;
                    if (sql.length() == 0) {
                        retval.setSQL(null);
                    } else {
                        retval.setSQL(sql);
                    }
                } catch (KettleException e) {
                    retval.setError(BaseMessages.getString(PKG, "SynchronizeAfterMergeMeta.ReturnValue.ErrorOccurred") + e.getMessage());
                }
            } else {
                retval.setError(BaseMessages.getString(PKG, "SynchronizeAfterMergeMeta.ReturnValue.NoTableDefinedOnConnection"));
            }
        } else {
            retval.setError(BaseMessages.getString(PKG, "SynchronizeAfterMergeMeta.ReturnValue.NotReceivingAnyFields"));
        }
    } else {
        retval.setError(BaseMessages.getString(PKG, "SynchronizeAfterMergeMeta.ReturnValue.NoConnectionDefined"));
    }
    return retval;
}

Example 83 with Database

use of org.pentaho.di.core.database.Database in project pentaho-kettle by pentaho.

the class TableCompare method init.

@Override
public boolean init(StepMetaInterface smi, StepDataInterface sdi) {
    meta = (TableCompareMeta) smi;
    data = (TableCompareData) sdi;
    if (super.init(smi, sdi)) {
        try {
            data.referenceDb = new Database(this, meta.getReferenceConnection());
            data.referenceDb.connect();
        } catch (Exception e) {
            logError(BaseMessages.getString(PKG, "TableCompare.Exception.UnexpectedErrorConnectingToReferenceDatabase", meta.getReferenceConnection().getName()), e);
            return false;
        }
        try {
            data.compareDb = new Database(this, meta.getCompareConnection());
            data.compareDb.connect();
        } catch (Exception e) {
            logError(BaseMessages.getString(PKG, "TableCompare.Exception.UnexpectedErrorConnectingToCompareDatabase", meta.getCompareConnection().getName()), e);
            return false;
        }
        return true;
    }
    return false;
}

Example 84 with Database

use of org.pentaho.di.core.database.Database in project pentaho-kettle by pentaho.

the class DatabaseLookupUTest method getCreatedData.

private DatabaseLookupData getCreatedData(boolean allEquals) throws Exception {
    Database db = mock(Database.class);
    when(db.getRows(anyString(), anyInt())).thenReturn(Collections.singletonList(new Object[] { 1L }));
    RowMeta returnRowMeta = new RowMeta();
    returnRowMeta.addValueMeta(new ValueMetaInteger());
    when(db.getReturnRowMeta()).thenReturn(returnRowMeta);
    DatabaseLookupMeta meta = createTestMeta();
    DatabaseLookupData data = new DatabaseLookupData();
    DatabaseLookup step = createSpiedStep(db, mockHelper, meta);
    step.init(meta, data);
    data.db = db;
    data.keytypes = new int[] { ValueMetaInterface.TYPE_INTEGER };
    if (allEquals) {
        data.allEquals = true;
        data.conditions = new int[] { DatabaseLookupMeta.CONDITION_EQ };
    } else {
        data.allEquals = false;
        data.conditions = new int[] { DatabaseLookupMeta.CONDITION_LT };
    }
    step.processRow(meta, data);
    return data;
}

Example 85 with Database

use of org.pentaho.di.core.database.Database in project pentaho-kettle by pentaho.

the class PDI5436Test method mockDatabase.

private Database mockDatabase() throws KettleDatabaseException {
    Database databaseMock = mock(Database.class);
    RowMeta databaseRowMeta = new RowMeta();
    databaseRowMeta.addValueMeta(new ValueMetaString("id"));
    databaseRowMeta.addValueMeta(new ValueMetaString("value"));
    doReturn(databaseRowMeta).when(databaseMock).getTableFields(anyString());
    doReturn(databaseRowMeta).when(databaseMock).getTableFieldsMeta(anyString(), anyString());
    doReturn(Arrays.asList(new Object[][] { { "1", "value" } })).when(databaseMock).getRows(anyString(), anyInt());
    doReturn(databaseRowMeta).when(databaseMock).getReturnRowMeta();
    return databaseMock;
}