public SQLStatement getSQLStatements( TransMeta transMeta, StepMeta stepMeta, RowMetaInterface prev) { SQLStatement retval = new SQLStatement(stepMeta.getName(), databaseWriteMeta, null); // default: nothing to do! int i; if (databaseWriteMeta != null) { if (prev != null && prev.size() > 0) { if (!Const.isEmpty(tablename)) { String schemaTable = databaseWriteMeta.getQuotedSchemaTableCombination(schemaName, tablename); Database db = new Database(databaseWriteMeta); try { boolean doHash = false; String cr_table = null; db.connect(); // OK, what do we put in the new table?? RowMetaInterface fields = new RowMeta(); ValueMetaInterface vkeyfield = null; if (!Const.isEmpty(technicalKeyField)) { // First, the new technical key... vkeyfield = new ValueMeta(technicalKeyField, ValueMetaInterface.TYPE_INTEGER); vkeyfield.setLength(10); vkeyfield.setPrecision(0); } // Then the hashcode (optional) ValueMetaInterface vhashfield = null; if (useHash && !Const.isEmpty(hashField)) { vhashfield = new ValueMeta(hashField, ValueMetaInterface.TYPE_INTEGER); vhashfield.setLength(15); vhashfield.setPrecision(0); doHash = true; } // Then the last update field (optional) ValueMetaInterface vLastUpdateField = null; if (!Const.isEmpty(lastUpdateField)) { vLastUpdateField = new ValueMeta(lastUpdateField, ValueMetaInterface.TYPE_DATE); } if (!db.checkTableExists(schemaTable)) { if (vkeyfield != null) { // Add technical key field. fields.addValueMeta(vkeyfield); } // Add the keys only to the table if (keyField != null && keyLookup != null) { int cnt = keyField.length; for (i = 0; i < cnt; i++) { String error_field = ""; // $NON-NLS-1$ // Find the value in the stream ValueMetaInterface v = prev.searchValueMeta(keyField[i]); if (v != null) { String name = keyLookup[i]; ValueMetaInterface newValue = v.clone(); newValue.setName(name); if (vkeyfield != null) { if (name.equals(vkeyfield.getName()) || (doHash == true && name.equals(vhashfield.getName()))) { error_field += name; } } if (error_field.length() > 0) { retval.setError( Messages.getString( "ConcurrentCombinationLookupMeta.ReturnValue.NameCollision", error_field)); //$NON-NLS-1$ } else { fields.addValueMeta(newValue); } } } } if (doHash == true) { fields.addValueMeta(vhashfield); } if (vLastUpdateField != null) { fields.addValueMeta(vLastUpdateField); } } else { // Table already exists // Get the fields that are in the table now: RowMetaInterface tabFields = db.getTableFields(schemaTable); // Don't forget to quote these as well... databaseWriteMeta.quoteReservedWords(tabFields); if (vkeyfield != null && tabFields.searchValueMeta(vkeyfield.getName()) == null) { // Add technical key field if it didn't exist yet fields.addValueMeta(vkeyfield); } // Add the already existing fields int cnt = tabFields.size(); for (i = 0; i < cnt; i++) { ValueMetaInterface v = tabFields.getValueMeta(i); fields.addValueMeta(v); } // Find the missing fields in the real table String keyLookup[] = getKeyLookup(); String keyField[] = getKeyField(); if (keyField != null && keyLookup != null) { cnt = keyField.length; for (i = 0; i < cnt; i++) { // Find the value in the stream ValueMetaInterface v = prev.searchValueMeta(keyField[i]); if (v != null) { ValueMetaInterface newValue = v.clone(); newValue.setName(keyLookup[i]); // Does the corresponding name exist in the table if (tabFields.searchValueMeta(newValue.getName()) == null) { fields.addValueMeta(newValue); // nope --> add } } } } if (doHash == true && tabFields.searchValueMeta(vhashfield.getName()) == null) { // Add hash field fields.addValueMeta(vhashfield); } if (vLastUpdateField != null && tabFields.searchValueMeta(vLastUpdateField.getName()) == null) { fields.addValueMeta(vLastUpdateField); } } cr_table = db.getDDL( schemaTable, fields, (CREATION_METHOD_SEQUENCE.equals(getTechKeyCreation()) && sequenceFrom != null && sequenceFrom.length() != 0) ? null : technicalKeyField, CREATION_METHOD_AUTOINC.equals(getTechKeyCreation()), null, true); // // OK, now let's build the index // // What fields do we put int the index? // Only the hashcode or all fields? String cr_index = ""; // $NON-NLS-1$ String cr_uniq_index = ""; // $NON-NLS-1$ String idx_fields[] = null; if (useHash) { if (hashField != null && hashField.length() > 0) { idx_fields = new String[] {hashField}; } else { retval.setError( Messages.getString( "ConcurrentCombinationLookupMeta.ReturnValue.NotHashFieldSpecified")); //$NON-NLS-1$ } } else // index on all key fields... { if (!Const.isEmpty(keyLookup)) { int nrfields = keyLookup.length; if (nrfields > 32 && databaseWriteMeta.getDatabaseType() == DatabaseMeta.TYPE_DATABASE_ORACLE) { nrfields = 32; // Oracle indexes are limited to 32 fields... } idx_fields = new String[nrfields]; for (i = 0; i < nrfields; i++) idx_fields[i] = keyLookup[i]; } else { retval.setError( Messages.getString( "ConcurrentCombinationLookupMeta.ReturnValue.NotFieldsSpecified")); //$NON-NLS-1$ } } // OK, now get the create index statement... if (!Const.isEmpty(technicalKeyField)) { String techKeyArr[] = new String[] {technicalKeyField}; if (!db.checkIndexExists(schemaName, tablename, techKeyArr)) { String indexname = "idx_" + tablename + "_pk"; // $NON-NLS-1$ //$NON-NLS-2$ cr_uniq_index = db.getCreateIndexStatement( schemaName, tablename, indexname, techKeyArr, true, true, false, true); cr_uniq_index += Const.CR; } } // OK, now get the create lookup index statement... if (!Const.isEmpty(idx_fields) && !db.checkIndexExists(schemaName, tablename, idx_fields)) { String indexname = "idx_" + tablename + "_lookup"; // $NON-NLS-1$ //$NON-NLS-2$ cr_index = db.getCreateIndexStatement( schemaName, tablename, indexname, idx_fields, false, false, false, true); cr_index += Const.CR; } // // Don't forget the sequence (optional) // String cr_seq = ""; // $NON-NLS-1$ if (databaseWriteMeta.supportsSequences() && !Const.isEmpty(sequenceFrom)) { if (!db.checkSequenceExists(schemaName, sequenceFrom)) { cr_seq += db.getCreateSequenceStatement(schemaName, sequenceFrom, 1L, 1L, -1L, true); cr_seq += Const.CR; } } retval.setSQL(cr_table + cr_uniq_index + cr_index + cr_seq); } catch (KettleException e) { retval.setError( Messages.getString("ConcurrentCombinationLookupMeta.ReturnValue.ErrorOccurred") + Const.CR + e.getMessage()); // $NON-NLS-1$ } } else { retval.setError( Messages.getString( "ConcurrentCombinationLookupMeta.ReturnValue.NotTableDefined")); //$NON-NLS-1$ } } else { retval.setError( Messages.getString( "ConcurrentCombinationLookupMeta.ReturnValue.NotReceivingField")); //$NON-NLS-1$ } } else { retval.setError( Messages.getString( "ConcurrentCombinationLookupMeta.ReturnValue.NotConnectionDefined")); //$NON-NLS-1$ } return retval; }
private void check( List<CheckResultInterface> remarks, StepMeta stepMeta, RowMetaInterface prev, String[] input, DatabaseMeta daMeta) { CheckResult cr; String error_message; if (daMeta != null) { Database db = new Database(daMeta); try { db.connect(); if (!Const.isEmpty(tablename)) { boolean first = true; boolean error_found = false; error_message = ""; // $NON-NLS-1$ String schemaTable = daMeta.getQuotedSchemaTableCombination(schemaName, tablename); RowMetaInterface r = db.getTableFields(schemaTable); if (r != null) { 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 += Messages.getString( "ConcurrentCombinationLookupMeta.CheckResult.MissingCompareFields") + Const.CR; // $NON-NLS-1$ } error_found = true; error_message += "\t\t" + lufield + Const.CR; // $NON-NLS-1$ } } if (error_found) { cr = new CheckResult(CheckResultInterface.TYPE_RESULT_ERROR, error_message, stepMeta); } else { cr = new CheckResult( CheckResultInterface.TYPE_RESULT_OK, Messages.getString( "ConcurrentCombinationLookupMeta.CheckResult.AllFieldsFound"), stepMeta); //$NON-NLS-1$ } remarks.add(cr); /* Also, check the fields: tk, version, from-to, ... */ if (!Const.isEmpty(technicalKeyField) && r.indexOfValue(technicalKeyField) < 0) { error_message = Messages.getString( "ConcurrentCombinationLookupMeta.CheckResult.TechnicalKeyNotFound", technicalKeyField) + Const.CR; // $NON-NLS-1$ //$NON-NLS-2$ cr = new CheckResult(CheckResultInterface.TYPE_RESULT_ERROR, error_message, stepMeta); } else { error_message = Messages.getString( "ConcurrentCombinationLookupMeta.CheckResult.TechnicalKeyFound", technicalKeyField) + Const.CR; // $NON-NLS-1$ //$NON-NLS-2$ cr = new CheckResult(CheckResultInterface.TYPE_RESULT_OK, error_message, stepMeta); } remarks.add(cr); } else { error_message = Messages.getString( "ConcurrentCombinationLookupMeta.CheckResult.CouldNotReadTableInfo"); //$NON-NLS-1$ cr = new CheckResult(CheckResultInterface.TYPE_RESULT_ERROR, error_message, stepMeta); remarks.add(cr); } } // Look up fields in the input stream <prev> if (prev != null && prev.size() > 0) { boolean first = true; error_message = ""; // $NON-NLS-1$ boolean error_found = false; for (int i = 0; i < keyField.length; i++) { ValueMetaInterface v = prev.searchValueMeta(keyField[i]); if (v == null) { if (first) { first = false; error_message += Messages.getString("ConcurrentCombinationLookupMeta.CheckResult.MissingFields") + Const.CR; // $NON-NLS-1$ } error_found = true; error_message += "\t\t" + keyField[i] + Const.CR; // $NON-NLS-1$ } } if (error_found) { cr = new CheckResult(CheckResultInterface.TYPE_RESULT_ERROR, error_message, stepMeta); } else { cr = new CheckResult( CheckResultInterface.TYPE_RESULT_OK, Messages.getString( "ConcurrentCombinationLookupMeta.CheckResult.AllFieldsFoundInInputStream"), stepMeta); //$NON-NLS-1$ } remarks.add(cr); } else { error_message = Messages.getString("ConcurrentCombinationLookupMeta.CheckResult.CouldNotReadFields") + Const.CR; // $NON-NLS-1$ cr = new CheckResult(CheckResultInterface.TYPE_RESULT_ERROR, error_message, stepMeta); remarks.add(cr); } // Check sequence if (daMeta.supportsSequences() && CREATION_METHOD_SEQUENCE.equals(getTechKeyCreation())) { if (Const.isEmpty(sequenceFrom)) { error_message += Messages.getString( "ConcurrentCombinationLookupMeta.CheckResult.ErrorNoSequenceName") + "!"; //$NON-NLS-1$ //$NON-NLS-2$ cr = new CheckResult(CheckResultInterface.TYPE_RESULT_ERROR, error_message, stepMeta); remarks.add(cr); } else { // It doesn't make sense to check the sequence name // if it's not filled in. if (db.checkSequenceExists(sequenceFrom)) { error_message = Messages.getString( "ConcurrentCombinationLookupMeta.CheckResult.ReadingSequenceOK", sequenceFrom); //$NON-NLS-1$ //$NON-NLS-2$ cr = new CheckResult(CheckResultInterface.TYPE_RESULT_OK, error_message, stepMeta); remarks.add(cr); } else { error_message += Messages.getString( "ConcurrentCombinationLookupMeta.CheckResult.ErrorReadingSequence") + sequenceFrom + "!"; //$NON-NLS-1$ //$NON-NLS-2$ cr = new CheckResult(CheckResultInterface.TYPE_RESULT_ERROR, error_message, stepMeta); remarks.add(cr); } } } if (techKeyCreation != null) { // post 2.2 version if (!(CREATION_METHOD_AUTOINC.equals(techKeyCreation) || CREATION_METHOD_SEQUENCE.equals(techKeyCreation) || CREATION_METHOD_TABLEMAX.equals(techKeyCreation))) { error_message += Messages.getString( "ConcurrentCombinationLookupMeta.CheckResult.ErrorTechKeyCreation") + ": " + techKeyCreation + "!"; //$NON-NLS-1$ //$NON-NLS-2$ cr = new CheckResult(CheckResultInterface.TYPE_RESULT_ERROR, error_message, stepMeta); remarks.add(cr); } } } catch (KettleException e) { error_message = Messages.getString("ConcurrentCombinationLookupMeta.CheckResult.ErrorOccurred") + e.getMessage(); // $NON-NLS-1$ cr = new CheckResult(CheckResultInterface.TYPE_RESULT_ERROR, error_message, stepMeta); remarks.add(cr); } finally { db.disconnect(); } } else { error_message = Messages.getString( "ConcurrentCombinationLookupMeta.CheckResult.InvalidConnection"); //$NON-NLS-1$ cr = new CheckResult(CheckResultInterface.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( CheckResultInterface.TYPE_RESULT_OK, Messages.getString( "ConcurrentCombinationLookupMeta.CheckResult.ReceivingInfoFromOtherSteps"), stepMeta); //$NON-NLS-1$ remarks.add(cr); } else { cr = new CheckResult( CheckResultInterface.TYPE_RESULT_ERROR, Messages.getString("ConcurrentCombinationLookupMeta.CheckResult.NoInputReceived"), stepMeta); //$NON-NLS-1$ remarks.add(cr); } }