public RowMetaInterface getRequiredFields(VariableSpace space) throws KettleException {
String realTableName = space.environmentSubstitute(tableName);
String realSchemaName = space.environmentSubstitute(schemaName);
if (databaseMeta != null) {
Database db = new Database(loggingObject, databaseMeta);
try {
db.connect();
if (!Const.isEmpty(realTableName)) {
String schemaTable =
databaseMeta.getQuotedSchemaTableCombination(realSchemaName, realTableName);
// Check if this table exists...
if (db.checkTableExists(schemaTable)) {
return db.getTableFields(schemaTable);
} else {
throw new KettleException(
BaseMessages.getString(PKG, "GPBulkLoaderMeta.Exception.TableNotFound"));
}
} else {
throw new KettleException(
BaseMessages.getString(PKG, "GPBulkLoaderMeta.Exception.TableNotSpecified"));
}
} catch (Exception e) {
throw new KettleException(
BaseMessages.getString(PKG, "GPBulkLoaderMeta.Exception.ErrorGettingFields"), e);
} finally {
db.disconnect();
}
} else {
throw new KettleException(
BaseMessages.getString(PKG, "GPBulkLoaderMeta.Exception.ConnectionNotDefined"));
}
}
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;
}
/**
* Given a name of a non-existing table to drop. <br>
* When StagingTransformGenerator is called to drop this table, then it shouldn't execute drop
* statement.
*/
@Test
public void shouldNotDropTableIfNotExists() throws Exception {
String nonExistingTable = "nonExistingTable";
when(database.checkTableExists(nonExistingTable)).thenReturn(false);
when(databaseMeta.getQuotedSchemaTableCombination((String) isNull(), eq(nonExistingTable)))
.thenReturn(nonExistingTable);
stagingTransformGenerator.dropTable(nonExistingTable);
verify(database, never()).execStatement(anyString());
}
/**
* Given a name of an existing table to drop. <br>
* When StagingTransformGenerator is called to drop this table, then it should execute drop
* statement.
*/
@Test
public void shouldDropTableIfExists() throws Exception {
String existingTable = "existingTable";
when(database.checkTableExists(existingTable)).thenReturn(true);
when(databaseMeta.getQuotedSchemaTableCombination((String) isNull(), eq(existingTable)))
.thenReturn(existingTable);
stagingTransformGenerator.dropTable(existingTable);
verify(database).execStatement("DROP TABLE existingTable");
}
/** Get a list of columns, comma separated, allow the user to select from it. */
private void getListColumns() {
if (!Const.isEmpty(wTablename.getText())) {
DatabaseMeta databaseMeta = jobMeta.findDatabase(wConnection.getText());
if (databaseMeta != null) {
Database database = new Database(loggingObject, databaseMeta);
database.shareVariablesWith(jobMeta);
try {
database.connect();
String schemaTable =
databaseMeta.getQuotedSchemaTableCombination(
wSchemaname.getText(), wTablename.getText());
RowMetaInterface row = database.getTableFields(schemaTable);
String[] available = row.getFieldNames();
String[] source = wListattribut.getText().split(",");
for (int i = 0; i < source.length; i++) {
source[i] = Const.trim(source[i]);
}
int[] idxSource = Const.indexsOfStrings(source, available);
EnterSelectionDialog dialog =
new EnterSelectionDialog(
shell,
available,
BaseMessages.getString(PKG, "JobMysqlBulkLoad.SelectColumns.Title"),
BaseMessages.getString(PKG, "JobMysqlBulkLoad.SelectColumns.Message"));
dialog.setMulti(true);
dialog.setAvoidQuickSearch();
dialog.setSelectedNrs(idxSource);
if (dialog.open() != null) {
String columns = "";
int[] idx = dialog.getSelectionIndeces();
for (int i = 0; i < idx.length; i++) {
if (i > 0) {
columns += ", ";
}
columns += available[idx[i]];
}
wListattribut.setText(columns);
}
} catch (KettleDatabaseException e) {
new ErrorDialog(
shell,
BaseMessages.getString(PKG, "System.Dialog.Error.Title"),
BaseMessages.getString(PKG, "JobMysqlBulkLoad.ConnectionError2.DialogMessage"),
e);
} finally {
database.disconnect();
}
}
}
}
public RowMetaInterface getTableFields() {
LogWriter log = LogWriter.getInstance();
RowMetaInterface fields = null;
if (databaseMeta != null) {
Database db = new Database(databaseMeta);
databases = new Database[] {db}; // Keep track of this one for cancelQuery
try {
db.connect();
String schemaTable = databaseMeta.getQuotedSchemaTableCombination(schemaName, tablename);
fields = db.getTableFields(schemaTable);
} catch (KettleDatabaseException dbe) {
log.logError(
toString(),
Messages.getString("DatabaseLookupMeta.ERROR0004.ErrorGettingTableFields")
+ dbe.getMessage()); // $NON-NLS-1$
} finally {
db.disconnect();
}
}
return fields;
}
/**
* Get the contents of the control file as specified in the meta object
*
* @param meta the meta object to model the control file after
* @return a string containing the control file contents
*/
public String getControlFileContents(GPLoadMeta meta, RowMetaInterface rm, Object[] r)
throws KettleException {
DatabaseMeta dm = meta.getDatabaseMeta();
StringBuffer contents = new StringBuffer(500);
// Source: GP Admin Guide 3.3.6, page 635:
//
contents.append("VERSION: 1.0.0.1").append(Const.CR);
contents
.append("DATABASE: ")
.append(environmentSubstitute(dm.getDatabaseName()))
.append(Const.CR);
contents.append("USER: "******"HOST: ").append(environmentSubstitute(dm.getHostname())).append(Const.CR);
contents
.append("PORT: ")
.append(environmentSubstitute(dm.getDatabasePortNumberString()))
.append(Const.CR);
contents.append("GPLOAD:").append(Const.CR);
contents.append(" INPUT:").append(Const.CR);
contents.append(" - SOURCE: ").append(Const.CR);
// TODO: Stream to a temporary file and then bulk load OR optionally stream to a named pipe
// (like MySQL bulk loader)
// TODO: allow LOCAL_HOSTNAME/PORT/PORT_RANGE to be specified
//
String inputName = "'" + environmentSubstitute(meta.getDataFile()) + "'";
contents.append(" FILE: ").append('[').append(inputName).append(']').append(Const.CR);
// COLUMNS is optional, takes the existing fields in the table
// contents.append(" - COLUMNS:").append(Const.CR);
// See also page 155 for formatting information & escaping
//
contents.append(" - FORMAT: TEXT").append(Const.CR);
contents
.append(" - DELIMITER: '")
.append(environmentSubstitute(meta.getDelimiter()))
.append("'")
.append(Const.CR);
// TODO: implement escape character, null_as
//
// contents.append(" - ESCAPE:
// '").append(environmentSubstitute(meta.getEscapeCharacter)).append("'").append(Const.CR);
contents
.append(" - QUOTE: '")
.append(environmentSubstitute(meta.getEnclosure()))
.append("'")
.append(Const.CR);
contents.append(" - HEADER: FALSE").append(Const.CR);
// TODO: implement database encoding support
// contents.append(" - ENCODING: ").append(Const.CR);
contents.append(" - ERROR_LIMIT: ").append(meta.getMaxErrors()).append(Const.CR);
if (!Const.isEmpty(meta.getErrorTableName())) {
contents.append(" - ERROR_TABLE: ").append(meta.getErrorTableName()).append(Const.CR);
}
contents.append(" OUTPUT:").append(Const.CR);
String tableName =
dm.getQuotedSchemaTableCombination(
environmentSubstitute(meta.getSchemaName()),
environmentSubstitute(meta.getTableName()));
contents.append(" - TABLE: ").append(tableName).append(Const.CR);
contents.append(" - MODE: ").append(meta.getLoadAction()).append(Const.CR);
// TODO: add support for MATCH_COLUMNS, UPDATE_COLUMN, UPDATE_CONDITION, MAPPING
// TODO: add suport for BEFORE and AFTER SQL
/*
String streamFields[] = meta.getFieldStream();
String tableFields[] = meta.getFieldTable();
if ( streamFields == null || streamFields.length == 0 )
{
throw new KettleException("No fields defined to load to database");
}
for (int i = 0; i < streamFields.length; i++)
{
if ( i!=0 ) contents.append(", ");
contents.append(dm.quoteField(tableFields[i]));
}
*/
return contents.toString();
}
public void check(
List<CheckResultInterface> remarks,
TransMeta transMeta,
StepMeta stepinfo,
RowMetaInterface prev,
String input[],
String output[],
RowMetaInterface info) {
CheckResult cr;
String error_message = ""; // $NON-NLS-1$
if (databaseMeta != null) {
Database db = new Database(databaseMeta);
db.shareVariablesWith(transMeta);
databases = new Database[] {db}; // Keep track of this one for cancelQuery
try {
db.connect();
if (!Const.isEmpty(tablename)) {
boolean first = true;
boolean error_found = false;
error_message = ""; // $NON-NLS-1$
String schemaTable = databaseMeta.getQuotedSchemaTableCombination(schemaName, tablename);
RowMetaInterface r = db.getTableFields(schemaTable);
if (r != null) {
// Check the keys used to do the lookup...
for (int i = 0; i < tableKeyField.length; i++) {
String lufield = tableKeyField[i];
ValueMetaInterface v = r.searchValueMeta(lufield);
if (v == null) {
if (first) {
first = false;
error_message +=
Messages.getString(
"DatabaseLookupMeta.Check.MissingCompareFieldsInLookupTable")
+ 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, stepinfo);
} else {
cr =
new CheckResult(
CheckResultInterface.TYPE_RESULT_OK,
Messages.getString("DatabaseLookupMeta.Check.AllLookupFieldsFoundInTable"),
stepinfo); //$NON-NLS-1$
}
remarks.add(cr);
// Also check the returned values!
for (int i = 0; i < returnValueField.length; i++) {
String lufield = returnValueField[i];
ValueMetaInterface v = r.searchValueMeta(lufield);
if (v == null) {
if (first) {
first = false;
error_message +=
Messages.getString(
"DatabaseLookupMeta.Check.MissingReturnFieldsInLookupTable")
+ 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, stepinfo);
} else {
cr =
new CheckResult(
CheckResultInterface.TYPE_RESULT_OK,
Messages.getString("DatabaseLookupMeta.Check.AllReturnFieldsFoundInTable"),
stepinfo); //$NON-NLS-1$
}
remarks.add(cr);
} else {
error_message =
Messages.getString("DatabaseLookupMeta.Check.CouldNotReadTableInfo"); // $NON-NLS-1$
cr = new CheckResult(CheckResultInterface.TYPE_RESULT_ERROR, error_message, stepinfo);
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 < streamKeyField1.length; i++) {
ValueMetaInterface v = prev.searchValueMeta(streamKeyField1[i]);
if (v == null) {
if (first) {
first = false;
error_message +=
Messages.getString("DatabaseLookupMeta.Check.MissingFieldsNotFoundInInput")
+ Const.CR; // $NON-NLS-1$
}
error_found = true;
error_message += "\t\t" + streamKeyField1[i] + Const.CR; // $NON-NLS-1$
}
}
if (error_found) {
cr = new CheckResult(CheckResultInterface.TYPE_RESULT_ERROR, error_message, stepinfo);
} else {
cr =
new CheckResult(
CheckResultInterface.TYPE_RESULT_OK,
Messages.getString("DatabaseLookupMeta.Check.AllFieldsFoundInInput"),
stepinfo); //$NON-NLS-1$
}
remarks.add(cr);
} else {
error_message =
Messages.getString("DatabaseLookupMeta.Check.CouldNotReadFromPreviousSteps")
+ Const.CR; // $NON-NLS-1$
cr = new CheckResult(CheckResultInterface.TYPE_RESULT_ERROR, error_message, stepinfo);
remarks.add(cr);
}
} catch (KettleDatabaseException dbe) {
error_message =
Messages.getString("DatabaseLookupMeta.Check.DatabaseErrorWhileChecking")
+ dbe.getMessage(); // $NON-NLS-1$
cr = new CheckResult(CheckResultInterface.TYPE_RESULT_ERROR, error_message, stepinfo);
remarks.add(cr);
} finally {
db.disconnect();
}
} else {
error_message =
Messages.getString("DatabaseLookupMeta.Check.MissingConnectionError"); // $NON-NLS-1$
cr = new CheckResult(CheckResultInterface.TYPE_RESULT_ERROR, error_message, stepinfo);
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("DatabaseLookupMeta.Check.StepIsReceivingInfoFromOtherSteps"),
stepinfo); //$NON-NLS-1$
remarks.add(cr);
} else {
cr =
new CheckResult(
CheckResultInterface.TYPE_RESULT_ERROR,
Messages.getString("DatabaseLookupMeta.Check.NoInputReceivedFromOtherSteps"),
stepinfo); //$NON-NLS-1$
remarks.add(cr);
}
}
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;
}
/**
* Create the command line for a sql process depending on the meta information supplied.
*
* @param meta The meta data to create the command line from
* @return The string to execute.
* @throws KettleException Upon any exception
*/
public String createCommandLine(IngresVectorwiseLoaderMeta meta) throws KettleException {
StringBuffer sb = new StringBuffer(300);
if (!Const.isEmpty(meta.getSqlPath())) {
try {
FileObject fileObject =
KettleVFS.getFileObject(environmentSubstitute(meta.getSqlPath()), getTransMeta());
String sqlexec = Const.optionallyQuoteStringByOS(KettleVFS.getFilename(fileObject));
sb.append(sqlexec);
// sql @tc-dwh-test.timocom.net,tcp_ip,VW[ingres,pwd]::dwh
} catch (KettleFileException ex) {
throw new KettleException("Error retrieving command string", ex);
}
} else {
if (meta.isUsingVwload()) {
if (isDetailed()) logDetailed("vwload defaults to system path");
sb.append("vwload");
} else {
if (isDetailed()) logDetailed("sql defaults to system path");
sb.append("sql");
}
}
DatabaseMeta dm = meta.getDatabaseMeta();
if (dm != null) {
String databaseName = environmentSubstitute(Const.NVL(dm.getDatabaseName(), ""));
String password =
Encr.decryptPasswordOptionallyEncrypted(
environmentSubstitute(Const.NVL(dm.getDatabaseInterface().getPassword(), "")));
String port =
environmentSubstitute(Const.NVL(dm.getDatabasePortNumberString(), "")).replace("7", "");
String username =
environmentSubstitute(Const.NVL(dm.getDatabaseInterface().getUsername(), ""));
String hostname =
environmentSubstitute(Const.NVL(dm.getDatabaseInterface().getHostname(), ""));
String schemaTable =
dm.getQuotedSchemaTableCombination(null, environmentSubstitute(meta.getTablename()));
String encoding = environmentSubstitute(Const.NVL(meta.getEncoding(), ""));
String fifoFile =
Const.optionallyQuoteStringByOS(
environmentSubstitute(Const.NVL(meta.getFifoFileName(), "")));
String errorFile =
Const.optionallyQuoteStringByOS(
environmentSubstitute(Const.NVL(meta.getErrorFileName(), "")));
int maxNrErrors =
Const.toInt(environmentSubstitute(Const.NVL(meta.getMaxNrErrors(), "0")), 0);
if (meta.isUsingVwload()) {
sb.append(" -u ").append(username);
sb.append(" -P ").append(password);
sb.append(" -f ").append(meta.getDelimiter()).append("");
sb.append(" -t ").append(schemaTable);
if (!Const.isEmpty(encoding)) {
sb.append(" -C ").append(encoding);
}
if (!Const.isEmpty(errorFile)) {
sb.append(" -l ").append(errorFile);
}
if (maxNrErrors > 0) {
sb.append(" -x ").append(maxNrErrors);
}
sb.append(" ").append(databaseName);
sb.append(" ").append(fifoFile);
} else if (meta.isUseDynamicVNode()) {
// logical portname in JDBC use a 7
sb.append(" @")
.append(hostname)
.append(",")
.append(port)
.append("[")
.append(username)
.append(",")
.append(password)
.append("]::")
.append(databaseName);
} else {
// Database Name
//
sb.append(" ").append(databaseName);
if (meta.isUseAuthentication()) {
sb.append("-P").append(password);
}
}
} else {
throw new KettleException("No connection specified");
}
return sb.toString();
}
public void check(
List<CheckResultInterface> remarks,
TransMeta transMeta,
StepMeta stepMeta,
RowMetaInterface prev,
String input[],
String output[],
RowMetaInterface info) {
CheckResult cr;
String error_message = ""; // $NON-NLS-1$
if (databaseMeta != null) {
Database db = new Database(loggingObject, databaseMeta);
db.shareVariablesWith(transMeta);
try {
db.connect();
if (!Const.isEmpty(tableName)) {
cr =
new CheckResult(
CheckResultInterface.TYPE_RESULT_OK,
BaseMessages.getString(PKG, "GPBulkLoaderMeta.CheckResult.TableNameOK"),
stepMeta); //$NON-NLS-1$
remarks.add(cr);
boolean first = true;
boolean error_found = false;
error_message = ""; // $NON-NLS-1$
// Check fields in table
String schemaTable =
databaseMeta.getQuotedSchemaTableCombination(
transMeta.environmentSubstitute(schemaName),
transMeta.environmentSubstitute(tableName));
RowMetaInterface r = db.getTableFields(schemaTable);
if (r != null) {
cr =
new CheckResult(
CheckResultInterface.TYPE_RESULT_OK,
BaseMessages.getString(PKG, "GPBulkLoaderMeta.CheckResult.TableExists"),
stepMeta); //$NON-NLS-1$
remarks.add(cr);
// How about the fields to insert/dateMask in the table?
first = true;
error_found = false;
error_message = ""; // $NON-NLS-1$
for (int i = 0; i < fieldTable.length; i++) {
String field = fieldTable[i];
ValueMetaInterface v = r.searchValueMeta(field);
if (v == null) {
if (first) {
first = false;
error_message +=
BaseMessages.getString(
PKG, "GPBulkLoaderMeta.CheckResult.MissingFieldsToLoadInTargetTable")
+ Const.CR; // $NON-NLS-1$
}
error_found = true;
error_message += "\t\t" + field + 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,
BaseMessages.getString(
PKG, "GPBulkLoaderMeta.CheckResult.AllFieldsFoundInTargetTable"),
stepMeta); //$NON-NLS-1$
}
remarks.add(cr);
} else {
error_message =
BaseMessages.getString(
PKG, "GPBulkLoaderMeta.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) {
cr =
new CheckResult(
CheckResultInterface.TYPE_RESULT_OK,
BaseMessages.getString(
PKG, "GPBulkLoaderMeta.CheckResult.StepReceivingDatas", prev.size() + ""),
stepMeta); //$NON-NLS-1$ //$NON-NLS-2$
remarks.add(cr);
boolean first = true;
error_message = ""; // $NON-NLS-1$
boolean error_found = false;
for (int i = 0; i < fieldStream.length; i++) {
ValueMetaInterface v = prev.searchValueMeta(fieldStream[i]);
if (v == null) {
if (first) {
first = false;
error_message +=
BaseMessages.getString(PKG, "GPBulkLoaderMeta.CheckResult.MissingFieldsInInput")
+ Const.CR; // $NON-NLS-1$
}
error_found = true;
error_message += "\t\t" + fieldStream[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,
BaseMessages.getString(
PKG, "GPBulkLoaderMeta.CheckResult.AllFieldsFoundInInput"),
stepMeta); //$NON-NLS-1$
}
remarks.add(cr);
} else {
error_message =
BaseMessages.getString(PKG, "GPBulkLoaderMeta.CheckResult.MissingFieldsInInput3")
+ Const.CR; // $NON-NLS-1$
cr = new CheckResult(CheckResultInterface.TYPE_RESULT_ERROR, error_message, stepMeta);
remarks.add(cr);
}
} catch (KettleException e) {
error_message =
BaseMessages.getString(PKG, "GPBulkLoaderMeta.CheckResult.DatabaseErrorOccurred")
+ e.getMessage(); // $NON-NLS-1$
cr = new CheckResult(CheckResultInterface.TYPE_RESULT_ERROR, error_message, stepMeta);
remarks.add(cr);
} finally {
db.disconnect();
}
} else {
error_message =
BaseMessages.getString(
PKG, "GPBulkLoaderMeta.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,
BaseMessages.getString(
PKG, "GPBulkLoaderMeta.CheckResult.StepReceivingInfoFromOtherSteps"),
stepMeta); //$NON-NLS-1$
remarks.add(cr);
} else {
cr =
new CheckResult(
CheckResultInterface.TYPE_RESULT_ERROR,
BaseMessages.getString(PKG, "GPBulkLoaderMeta.CheckResult.NoInputError"),
stepMeta); //$NON-NLS-1$
remarks.add(cr);
}
}
/**
* This method first traverses the set of included business tables and renders those tables to the
* SQL string buffer. Second, it traverses the list of joins and renders those in the WHERE
* clause. Finally, it traverses the constraints and adds them to the where or having clauses.
*
* @param query sql query model
* @param usedBusinessTables used business tables in query
* @param model the current business model
* @param path the join path
* @param conditions the where conditions
* @param databaseMeta database metadata
* @param locale locale string
*/
protected void generateFromAndWhere(
SQLQueryModel query,
List<LogicalTable> usedBusinessTables,
LogicalModel model,
Path path,
List<Constraint> conditions,
Map<LogicalTable, String> tableAliases,
Map<Constraint, SqlOpenFormula> constraintFormulaMap,
Map<String, Object> parameters,
boolean genAsPreparedStatement,
DatabaseMeta databaseMeta,
String locale)
throws PentahoMetadataException {
// Boolean delayConditionOnOuterJoin = null;
// Object val = null;
// FROM TABLES
for (int i = 0; i < usedBusinessTables.size(); i++) {
LogicalTable businessTable = usedBusinessTables.get(i);
String schemaName = null;
if (businessTable.getProperty(SqlPhysicalTable.TARGET_SCHEMA) != null) {
schemaName =
databaseMeta.quoteField(
(String) businessTable.getProperty(SqlPhysicalTable.TARGET_SCHEMA));
}
// ToDo: Allow table-level override of delaying conditions.
// val = businessTable.getProperty("delay_table_outer_join_conditions");
// if ( (val != null) && (val instanceof Boolean) ) {
// delayConditionOnOuterJoin = (Boolean)val;
// } else {
// delayConditionOnOuterJoin = null;
// }
// this code allows subselects to drive the physical model.
// TODO: make this key off a metadata flag vs. the
// beginning of the table name.
String tableName = (String) businessTable.getProperty(SqlPhysicalTable.TARGET_TABLE);
TargetTableType type =
(TargetTableType) businessTable.getProperty(SqlPhysicalTable.TARGET_TABLE_TYPE);
if (type == TargetTableType.INLINE_SQL) {
tableName = "(" + tableName + ")"; // $NON-NLS-1$ //$NON-NLS-2$
} else {
tableName = databaseMeta.getQuotedSchemaTableCombination(schemaName, tableName);
}
query.addTable(tableName, databaseMeta.quoteField(tableAliases.get(businessTable)));
}
// JOIN CONDITIONS
if (path != null) {
for (int i = 0; i < path.size(); i++) {
LogicalRelationship relation = path.getRelationship(i);
String joinFormula =
getJoin(
model,
relation,
tableAliases,
parameters,
genAsPreparedStatement,
databaseMeta,
locale);
String joinOrderKey = relation.getJoinOrderKey();
JoinType joinType;
switch (RelationshipType.getJoinType(relation.getRelationshipType())) {
case LEFT_OUTER:
joinType = JoinType.LEFT_OUTER_JOIN;
break;
case RIGHT_OUTER:
joinType = JoinType.RIGHT_OUTER_JOIN;
break;
case FULL_OUTER:
joinType = JoinType.FULL_OUTER_JOIN;
break;
default:
joinType = JoinType.INNER_JOIN;
break;
}
String leftTableName =
databaseMeta.getQuotedSchemaTableCombination(
(String) relation.getFromTable().getProperty(SqlPhysicalTable.TARGET_SCHEMA),
(String) relation.getFromTable().getProperty(SqlPhysicalTable.TARGET_TABLE));
String leftTableAlias = tableAliases.get(relation.getFromTable());
String rightTableName =
databaseMeta.getQuotedSchemaTableCombination(
(String) relation.getToTable().getProperty(SqlPhysicalTable.TARGET_SCHEMA),
(String) relation.getToTable().getProperty(SqlPhysicalTable.TARGET_TABLE));
String rightTableAlias = tableAliases.get(relation.getToTable());
query.addJoin(
leftTableName,
leftTableAlias,
rightTableName,
rightTableAlias,
joinType,
joinFormula,
joinOrderKey);
// query.addWhereFormula(joinFormula, "AND"); //$NON-NLS-1$
}
}
// WHERE CONDITIONS
if (conditions != null) {
boolean first = true;
for (Constraint condition : conditions) {
SqlOpenFormula formula = constraintFormulaMap.get(condition);
// configure formula to use table aliases
formula.setTableAliases(tableAliases);
// The ones with aggregates in it are for the HAVING clause
if (!formula.hasAggregate()) {
String sqlFormula = formula.generateSQL(locale);
String[] usedTables = formula.getLogicalTableIDs();
query.addWhereFormula(sqlFormula, condition.getCombinationType().toString(), usedTables);
first = false;
} else {
query.addHavingFormula(
formula.generateSQL(locale), condition.getCombinationType().toString());
}
}
}
}
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;
}
/**
* Get the contents of the control file as specified in the meta object
*
* @param meta the meta object to model the control file after
* @return a string containing the control file contents
*/
public String getControlFileContents(OraBulkLoaderMeta meta, RowMetaInterface rm, Object[] r)
throws KettleException {
DatabaseMeta dm = meta.getDatabaseMeta();
String inputName = "'" + environmentSubstitute(meta.getDataFile()) + "'";
String loadAction = meta.getLoadAction();
StringBuilder contents = new StringBuilder(500);
contents.append("OPTIONS(").append(Const.CR);
contents.append(" ERRORS=\'").append(meta.getMaxErrors()).append("\'").append(Const.CR);
if (meta.getCommitSizeAsInt(this) != 0
&& !(meta.isDirectPath() && getStepMeta().getCopies() > 1)) {
// For the second part of the above expressions: ROWS is not supported
// in parallel mode (by sqlldr).
contents.append(" , ROWS=\'").append(meta.getCommitSize()).append("\'").append(Const.CR);
}
if (meta.getBindSizeAsInt(this) != 0) {
contents.append(" , BINDSIZE=\'").append(meta.getBindSize()).append("\'").append(Const.CR);
}
if (meta.getReadSizeAsInt(this) != 0) {
contents.append(" , READSIZE=\'").append(meta.getReadSize()).append("\'").append(Const.CR);
}
contents.append(")").append(Const.CR);
contents.append("LOAD DATA").append(Const.CR);
if (!Utils.isEmpty(meta.getCharacterSetName())) {
contents.append("CHARACTERSET ").append(meta.getCharacterSetName()).append(Const.CR);
}
if (!OraBulkLoaderMeta.METHOD_AUTO_CONCURRENT.equals(meta.getLoadMethod())
|| !Utils.isEmpty(meta.getAltRecordTerm())) {
String infile = inputName;
if (OraBulkLoaderMeta.METHOD_AUTO_CONCURRENT.equals(meta.getLoadMethod())) {
infile = "''";
}
// For concurrent input, data command line argument must be specified
contents.append("INFILE ").append(infile);
if (!Utils.isEmpty(meta.getAltRecordTerm())) {
contents
.append(" \"STR x'")
.append(encodeRecordTerminator(meta.getAltRecordTerm(), meta.getEncoding()))
.append("'\"");
}
contents.append(Const.CR);
}
contents
.append("INTO TABLE ")
.append(
dm.getQuotedSchemaTableCombination(
environmentSubstitute(meta.getSchemaName()),
environmentSubstitute(meta.getTableName())))
.append(Const.CR)
.append(loadAction)
.append(Const.CR)
.append("FIELDS TERMINATED BY ',' ENCLOSED BY '\"'")
.append(Const.CR)
.append("(");
String[] streamFields = meta.getFieldStream();
String[] tableFields = meta.getFieldTable();
String[] dateMask = meta.getDateMask();
if (streamFields == null || streamFields.length == 0) {
throw new KettleException("No fields defined to load to database");
}
for (int i = 0; i < streamFields.length; i++) {
if (i != 0) {
contents.append(", ").append(Const.CR);
}
contents.append(dm.quoteField(tableFields[i]));
int pos = rm.indexOfValue(streamFields[i]);
if (pos < 0) {
throw new KettleException("Could not find field " + streamFields[i] + " in stream");
}
ValueMetaInterface v = rm.getValueMeta(pos);
switch (v.getType()) {
case ValueMetaInterface.TYPE_STRING:
if (v.getLength() > 255) {
contents.append(" CHAR(").append(v.getLength()).append(")");
} else {
contents.append(" CHAR");
}
break;
case ValueMetaInterface.TYPE_INTEGER:
case ValueMetaInterface.TYPE_NUMBER:
case ValueMetaInterface.TYPE_BIGNUMBER:
break;
case ValueMetaInterface.TYPE_DATE:
if (OraBulkLoaderMeta.DATE_MASK_DATE.equals(dateMask[i])) {
contents.append(" DATE 'yyyy-mm-dd'");
} else if (OraBulkLoaderMeta.DATE_MASK_DATETIME.equals(dateMask[i])) {
contents.append(" TIMESTAMP 'yyyy-mm-dd hh24:mi:ss.ff'");
} else {
// If not specified the default is date.
contents.append(" DATE 'yyyy-mm-dd'");
}
break;
case ValueMetaInterface.TYPE_BINARY:
contents.append(" ENCLOSED BY '<startlob>' AND '<endlob>'");
break;
case ValueMetaInterface.TYPE_TIMESTAMP:
contents.append(" TIMESTAMP 'yyyy-mm-dd hh24:mi:ss.ff'");
break;
default:
break;
}
}
contents.append(")");
return contents.toString();
}
public void check(
List<CheckResultInterface> remarks,
TransMeta transMeta,
StepMeta stepinfo,
RowMetaInterface prev,
String input[],
String output[],
RowMetaInterface info) {
CheckResult cr;
String error_message = ""; // $NON-NLS-1$
if (databaseMeta != null) {
Database db = new Database(databaseMeta);
db.shareVariablesWith(transMeta);
try {
db.connect();
if (!Const.isEmpty(tableName)) {
cr =
new CheckResult(
CheckResultInterface.TYPE_RESULT_OK,
Messages.getString("DeleteMeta.CheckResult.TablenameOK"),
stepinfo); //$NON-NLS-1$
remarks.add(cr);
boolean first = true;
boolean error_found = false;
error_message = ""; // $NON-NLS-1$
// Check fields in table
String schemaTable = databaseMeta.getQuotedSchemaTableCombination(schemaName, tableName);
RowMetaInterface r = db.getTableFields(schemaTable);
if (r != null) {
cr =
new CheckResult(
CheckResultInterface.TYPE_RESULT_OK,
Messages.getString("DeleteMeta.CheckResult.VisitTableSuccessfully"),
stepinfo); //$NON-NLS-1$
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 +=
Messages.getString("DeleteMeta.CheckResult.MissingCompareFieldsInTargetTable")
+ 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, stepinfo);
} else {
cr =
new CheckResult(
CheckResultInterface.TYPE_RESULT_OK,
Messages.getString("DeleteMeta.CheckResult.FoundLookupFields"),
stepinfo); //$NON-NLS-1$
}
remarks.add(cr);
} else {
error_message =
Messages.getString("DeleteMeta.CheckResult.CouldNotReadTableInfo"); // $NON-NLS-1$
cr = new CheckResult(CheckResultInterface.TYPE_RESULT_ERROR, error_message, stepinfo);
remarks.add(cr);
}
}
// Look up fields in the input stream <prev>
if (prev != null && prev.size() > 0) {
cr =
new CheckResult(
CheckResultInterface.TYPE_RESULT_OK,
Messages.getString(
"DeleteMeta.CheckResult.ConnectedStepSuccessfully",
String.valueOf(prev.size())),
stepinfo); //$NON-NLS-1$ //$NON-NLS-2$
remarks.add(cr);
boolean first = true;
error_message = ""; // $NON-NLS-1$
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 +=
Messages.getString("DeleteMeta.CheckResult.MissingFields")
+ Const.CR; // $NON-NLS-1$
}
error_found = true;
error_message += "\t\t" + keyStream[i] + Const.CR; // $NON-NLS-1$
}
}
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 +=
Messages.getString("DeleteMeta.CheckResult.MissingFields2")
+ Const.CR; // $NON-NLS-1$
}
error_found = true;
error_message += "\t\t" + keyStream[i] + Const.CR; // $NON-NLS-1$
}
}
}
if (error_found) {
cr = new CheckResult(CheckResultInterface.TYPE_RESULT_ERROR, error_message, stepinfo);
} else {
cr =
new CheckResult(
CheckResultInterface.TYPE_RESULT_OK,
Messages.getString("DeleteMeta.CheckResult.AllFieldsFound"),
stepinfo); //$NON-NLS-1$
}
remarks.add(cr);
// How about the fields to insert/update the table with?
first = true;
error_found = false;
error_message = ""; // $NON-NLS-1$
} else {
error_message =
Messages.getString("DeleteMeta.CheckResult.MissingFields3") + Const.CR; // $NON-NLS-1$
cr = new CheckResult(CheckResultInterface.TYPE_RESULT_ERROR, error_message, stepinfo);
remarks.add(cr);
}
} catch (KettleException e) {
error_message =
Messages.getString("DeleteMeta.CheckResult.DatabaseError")
+ e.getMessage(); // $NON-NLS-1$
cr = new CheckResult(CheckResultInterface.TYPE_RESULT_ERROR, error_message, stepinfo);
remarks.add(cr);
} finally {
db.disconnect();
}
} else {
error_message = Messages.getString("DeleteMeta.CheckResult.InvalidConnection"); // $NON-NLS-1$
cr = new CheckResult(CheckResultInterface.TYPE_RESULT_ERROR, error_message, stepinfo);
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("DeleteMeta.CheckResult.StepReceivingInfo"),
stepinfo); //$NON-NLS-1$
remarks.add(cr);
} else {
cr =
new CheckResult(
CheckResultInterface.TYPE_RESULT_ERROR,
Messages.getString("DeleteMeta.CheckResult.NoInputReceived"),
stepinfo); //$NON-NLS-1$
remarks.add(cr);
}
}
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;
}
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);
}
}
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 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 (!Const.isEmpty(tableName)) {
cr =
new CheckResult(
CheckResultInterface.TYPE_RESULT_OK,
BaseMessages.getString(PKG, "InsertUpdateMeta.CheckResult.TableNameOK"),
stepMeta);
remarks.add(cr);
boolean first = true;
boolean error_found = false;
error_message = "";
// Check fields in table
String schemaTable = databaseMeta.getQuotedSchemaTableCombination(schemaName, tableName);
RowMetaInterface r = db.getTableFields(schemaTable);
if (r != null) {
cr =
new CheckResult(
CheckResultInterface.TYPE_RESULT_OK,
BaseMessages.getString(PKG, "InsertUpdateMeta.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, "InsertUpdateMeta.CheckResult.MissingCompareFieldsInTargetTable")
+ Const.CR;
}
error_found = true;
error_message += "\t\t" + lufield + Const.CR;
}
}
if (error_found) {
cr = new CheckResult(CheckResultInterface.TYPE_RESULT_ERROR, error_message, stepMeta);
} else {
cr =
new CheckResult(
CheckResultInterface.TYPE_RESULT_OK,
BaseMessages.getString(
PKG, "InsertUpdateMeta.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,
"InsertUpdateMeta.CheckResult.MissingFieldsToUpdateInTargetTable")
+ Const.CR;
}
error_found = true;
error_message += "\t\t" + lufield + Const.CR;
}
}
if (error_found) {
cr = new CheckResult(CheckResultInterface.TYPE_RESULT_ERROR, error_message, stepMeta);
} else {
cr =
new CheckResult(
CheckResultInterface.TYPE_RESULT_OK,
BaseMessages.getString(
PKG, "InsertUpdateMeta.CheckResult.AllFieldsToUpdateFoundInTargetTable"),
stepMeta);
}
remarks.add(cr);
} else {
error_message =
BaseMessages.getString(PKG, "InsertUpdateMeta.CheckResult.CouldNotReadTableInfo");
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) {
cr =
new CheckResult(
CheckResultInterface.TYPE_RESULT_OK,
BaseMessages.getString(
PKG, "InsertUpdateMeta.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, "InsertUpdateMeta.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, "InsertUpdateMeta.CheckResult.MissingFieldsInInput")
+ Const.CR;
}
error_found = true;
error_message += "\t\t" + keyStream[i] + Const.CR;
}
}
}
if (error_found) {
cr = new CheckResult(CheckResultInterface.TYPE_RESULT_ERROR, error_message, stepMeta);
} else {
cr =
new CheckResult(
CheckResultInterface.TYPE_RESULT_OK,
BaseMessages.getString(
PKG, "InsertUpdateMeta.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, "InsertUpdateMeta.CheckResult.MissingInputStreamFields")
+ Const.CR;
}
error_found = true;
error_message += "\t\t" + lufield + Const.CR;
}
}
if (error_found) {
cr = new CheckResult(CheckResultInterface.TYPE_RESULT_ERROR, error_message, stepMeta);
} else {
cr =
new CheckResult(
CheckResultInterface.TYPE_RESULT_OK,
BaseMessages.getString(
PKG, "InsertUpdateMeta.CheckResult.AllFieldsFoundInInput2"),
stepMeta);
}
remarks.add(cr);
} else {
error_message =
BaseMessages.getString(PKG, "InsertUpdateMeta.CheckResult.MissingFieldsInInput3")
+ Const.CR;
cr = new CheckResult(CheckResultInterface.TYPE_RESULT_ERROR, error_message, stepMeta);
remarks.add(cr);
}
} catch (KettleException e) {
error_message =
BaseMessages.getString(PKG, "InsertUpdateMeta.CheckResult.DatabaseErrorOccurred")
+ e.getMessage();
cr = new CheckResult(CheckResultInterface.TYPE_RESULT_ERROR, error_message, stepMeta);
remarks.add(cr);
} finally {
db.disconnect();
}
} else {
error_message = BaseMessages.getString(PKG, "InsertUpdateMeta.CheckResult.InvalidConnection");
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,
BaseMessages.getString(
PKG, "InsertUpdateMeta.CheckResult.StepReceivingInfoFromOtherSteps"),
stepMeta);
remarks.add(cr);
} else {
cr =
new CheckResult(
CheckResultInterface.TYPE_RESULT_ERROR,
BaseMessages.getString(PKG, "InsertUpdateMeta.CheckResult.NoInputError"),
stepMeta);
remarks.add(cr);
}
}
private void loadAllTableDataIntoTheCache() throws KettleException {
DatabaseMeta dbMeta = meta.getDatabaseMeta();
try {
// We only want to get the used table fields...
//
String sql = "SELECT ";
for (int i = 0; i < meta.getStreamKeyField1().length; i++) {
if (i > 0) {
sql += ", ";
}
sql += dbMeta.quoteField(meta.getTableKeyField()[i]);
}
// Also grab the return field...
//
for (int i = 0; i < meta.getReturnValueField().length; i++) {
sql += ", " + dbMeta.quoteField(meta.getReturnValueField()[i]);
}
// The schema/table
//
sql +=
" FROM "
+ dbMeta.getQuotedSchemaTableCombination(
environmentSubstitute(meta.getSchemaName()),
environmentSubstitute(meta.getTablename()));
// order by?
if (meta.getOrderByClause() != null && meta.getOrderByClause().length() != 0) {
sql += " ORDER BY " + meta.getOrderByClause();
}
// Now that we have the SQL constructed, let's store the rows...
//
List<Object[]> rows = data.db.getRows(sql, 0);
if (rows != null && rows.size() > 0) {
RowMetaInterface returnRowMeta = data.db.getReturnRowMeta();
// Copy the data into 2 parts: key and value...
//
for (Object[] row : rows) {
int index = 0;
RowMeta keyMeta = new RowMeta();
Object[] keyData = new Object[meta.getStreamKeyField1().length];
for (int i = 0; i < meta.getStreamKeyField1().length; i++) {
keyData[i] = row[index];
keyMeta.addValueMeta(returnRowMeta.getValueMeta(index++));
}
// RowMeta valueMeta = new RowMeta();
Object[] valueData = new Object[data.returnMeta.size()];
for (int i = 0; i < data.returnMeta.size(); i++) {
valueData[i] = row[index++];
// valueMeta.addValueMeta(returnRowMeta.getValueMeta(index++));
}
// Store the data...
//
storeRowInCache(keyMeta, keyData, valueData);
incrementLinesInput();
}
}
} catch (Exception e) {
throw new KettleException(e);
}
}
private String buildCopyStatementSqlString() {
final DatabaseMeta databaseMeta = data.db.getDatabaseMeta();
StringBuilder sb = new StringBuilder(150);
sb.append("COPY ");
sb.append(
databaseMeta.getQuotedSchemaTableCombination(
environmentSubstitute(meta.getSchemaName()),
environmentSubstitute(meta.getTablename())));
if (meta.specifyFields()) {
final RowMetaInterface fields = data.insertRowMeta;
sb.append(" (");
for (int i = 0; i < fields.size(); i++) {
if (i > 0) sb.append(", ");
sb.append(databaseMeta.quoteField(fields.getValueMeta(i).getName()));
}
sb.append(")");
}
sb.append(" FROM STDIN NATIVE ");
if (!Const.isEmpty(meta.getExceptionsFileName())) {
sb.append("EXCEPTIONS E'")
.append(meta.getExceptionsFileName().replace("'", "\\'"))
.append("' ");
}
if (!Const.isEmpty(meta.getRejectedDataFileName())) {
sb.append("REJECTED DATA E'")
.append(meta.getRejectedDataFileName().replace("'", "\\'"))
.append("' ");
}
// TODO: Should eventually get a preference for this, but for now, be backward compatible.
sb.append("ENFORCELENGTH ");
if (meta.isAbortOnError()) {
sb.append("ABORT ON ERROR ");
}
if (meta.isDirect()) {
sb.append("DIRECT ");
}
if (!Const.isEmpty(meta.getStreamName())) {
sb.append("STREAM NAME E'")
.append(environmentSubstitute(meta.getStreamName()).replace("'", "\\'"))
.append("' ");
}
// XXX: I believe the right thing to do here is always use NO COMMIT since we want Kettle's
// configuration to drive.
// NO COMMIT does not seem to work even when the transformation setting 'make the transformation
// database transactional' is on
// sb.append("NO COMMIT");
return sb.toString();
}
