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);
}
}