public SQLStatement getSQLStatements( TransMeta transMeta, StepMeta stepMeta, RowMetaInterface prev) throws KettleStepException { SQLStatement retval = new SQLStatement(stepMeta.getName(), databaseMeta, null); // default: nothing to do! if (databaseMeta != null) { if (prev != null && prev.size() > 0) { // Copy the row RowMetaInterface tableFields = new RowMeta(); // Now change the field names for (int i = 0; i < fieldTable.length; i++) { ValueMetaInterface v = prev.searchValueMeta(fieldStream[i]); if (v != null) { ValueMetaInterface tableField = v.clone(); tableField.setName(fieldTable[i]); tableFields.addValueMeta(tableField); } else { throw new KettleStepException( "Unable to find field [" + fieldStream[i] + "] in the input rows"); } } if (!Const.isEmpty(tableName)) { Database db = new Database(loggingObject, databaseMeta); db.shareVariablesWith(transMeta); try { db.connect(); String schemaTable = databaseMeta.getQuotedSchemaTableCombination( transMeta.environmentSubstitute(schemaName), transMeta.environmentSubstitute(tableName)); String sql = db.getDDL(schemaTable, tableFields, null, false, null, true); if (sql.length() == 0) retval.setSQL(null); else retval.setSQL(sql); } catch (KettleException e) { retval.setError( BaseMessages.getString(PKG, "GPBulkLoaderMeta.GetSQL.ErrorOccurred") + e.getMessage()); // $NON-NLS-1$ } } else { retval.setError( BaseMessages.getString( PKG, "GPBulkLoaderMeta.GetSQL.NoTableDefinedOnConnection")); // $NON-NLS-1$ } } else { retval.setError( BaseMessages.getString( PKG, "GPBulkLoaderMeta.GetSQL.NotReceivingAnyFields")); // $NON-NLS-1$ } } else { retval.setError( BaseMessages.getString( PKG, "GPBulkLoaderMeta.GetSQL.NoConnectionDefined")); // $NON-NLS-1$ } return retval; }
public SQLStatement getSQLStatements( TransMeta transMeta, StepMeta stepMeta, RowMetaInterface prev) { SQLStatement retval = new SQLStatement(stepMeta.getName(), databaseMeta, null); // default: nothing to do! if (databaseMeta != null) { if (prev != null && prev.size() > 0) { if (!Const.isEmpty(tableName)) { Database db = new Database(databaseMeta); db.shareVariablesWith(transMeta); try { db.connect(); String schemaTable = databaseMeta.getQuotedSchemaTableCombination(schemaName, tableName); String cr_table = db.getDDL(schemaTable, prev, null, false, null, true); String cr_index = ""; // $NON-NLS-1$ 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( Messages.getString("DeleteMeta.CheckResult.KeyFieldsRequired")); // $NON-NLS-1$ } // Key lookup dimensions... if (idx_fields != null && idx_fields.length > 0 && !db.checkIndexExists(schemaTable, 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); } String sql = cr_table + cr_index; if (sql.length() == 0) retval.setSQL(null); else retval.setSQL(sql); } catch (KettleException e) { retval.setError( Messages.getString("DeleteMeta.Returnvalue.ErrorOccurred") + e.getMessage()); // $NON-NLS-1$ } } else { retval.setError( Messages.getString( "DeleteMeta.Returnvalue.NoTableDefinedOnConnection")); //$NON-NLS-1$ } } else { retval.setError( Messages.getString("DeleteMeta.Returnvalue.NoReceivingAnyFields")); // $NON-NLS-1$ } } else { retval.setError( Messages.getString("DeleteMeta.Returnvalue.NoConnectionDefined")); // $NON-NLS-1$ } return retval; }
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; }
public SQLStatement getSQLStatements( TransMeta transMeta, StepMeta stepMeta, RowMetaInterface prev, Repository repository, IMetaStore metaStore) throws KettleStepException { SQLStatement retval = new SQLStatement(stepMeta.getName(), databaseMeta, null); // default: nothing to do! if (databaseMeta != null) { if (prev != null && prev.size() > 0) { // Copy the row RowMetaInterface tableFields = new RowMeta(); // Now change the field names // 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 (!Const.isEmpty(tableName)) { Database db = new Database(loggingObject, databaseMeta); db.shareVariablesWith(transMeta); 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, "InsertUpdateMeta.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, "InsertUpdateMeta.ReturnValue.ErrorOccurred") + e.getMessage()); } } else { retval.setError( BaseMessages.getString( PKG, "InsertUpdateMeta.ReturnValue.NoTableDefinedOnConnection")); } } else { retval.setError( BaseMessages.getString(PKG, "InsertUpdateMeta.ReturnValue.NotReceivingAnyFields")); } } else { retval.setError( BaseMessages.getString(PKG, "InsertUpdateMeta.ReturnValue.NoConnectionDefined")); } return retval; }
public SQLStatement getSQLStatements( TransMeta transMeta, StepMeta stepMeta, RowMetaInterface prev, Repository repository, IMetaStore metaStore) { SQLStatement retval = new SQLStatement(stepMeta.getName(), databaseMeta, null); // default: // nothing // to // do! if (databaseMeta != null) { if (prev != null && prev.size() > 0) { if (!Const.isEmpty(tablename)) { Database db = new Database(loggingObject, databaseMeta); db.shareVariablesWith(transMeta); try { db.connect(); String schemaTable = databaseMeta.getQuotedSchemaTableCombination(null, tablename); String cr_table = db.getDDL(schemaTable, prev); // Squeeze in the VECTORWISE col store clause... // TODO: move this to the database dialog and make it user // configurable. // String VW_CLAUSE = "WITH STRUCTURE=VECTORWISE"; if (cr_table.toUpperCase().contains("CREATE TABLE")) { int scIndex = cr_table.indexOf(';'); if (scIndex < 0) { cr_table += VW_CLAUSE; } else { cr_table = cr_table.substring(0, scIndex) + VW_CLAUSE + cr_table.substring(scIndex); } } // Empty string means: nothing to do: set it to null... if (cr_table == null || cr_table.length() == 0) { cr_table = null; } retval.setSQL(cr_table); } catch (KettleDatabaseException dbe) { retval.setError( BaseMessages.getString( PKG, "IngresVectorWiseLoaderMeta.Error.ErrorConnecting", dbe.getMessage())); } finally { db.disconnect(); } } else { retval.setError(BaseMessages.getString(PKG, "IngresVectorWiseLoaderMeta.Error.NoTable")); } } else { retval.setError(BaseMessages.getString(PKG, "IngresVectorWiseLoaderMeta.Error.NoInput")); } } else { retval.setError(BaseMessages.getString(PKG, "IngresVectorWiseLoaderMeta.Error.NoConnection")); } return retval; }