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"));
}
}
/**
* 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");
}
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 Result execute(Result previousResult, int nr) {
String LimitNbrLignes = "";
String ListOfColumn = "*";
String strHighPriority = "";
String OutDumpText = "";
String OptionEnclosed = "";
String FieldSeparator = "";
String LinesTerminated = "";
Result result = previousResult;
result.setResult(false);
// Let's check the filename ...
if (filename != null) {
// User has specified a file, We can continue ...
String realFilename = getRealFilename();
File file = new File(realFilename);
if (file.exists() && iffileexists == 2) {
// the file exists and user want to Fail
result.setResult(false);
result.setNrErrors(1);
logError(
BaseMessages.getString(PKG, "JobMysqlBulkFile.FileExists1.Label")
+ realFilename
+ BaseMessages.getString(PKG, "JobMysqlBulkFile.FileExists2.Label"));
} else if (file.exists() && iffileexists == 1) {
// the file exists and user want to do nothing
result.setResult(true);
if (log.isDetailed())
logDetailed(
BaseMessages.getString(PKG, "JobMysqlBulkFile.FileExists1.Label")
+ realFilename
+ BaseMessages.getString(PKG, "JobMysqlBulkFile.FileExists2.Label"));
} else {
if (file.exists() && iffileexists == 0) {
// File exists and user want to renamme it with unique name
// Format Date
// Try to clean filename (without wildcard)
String wildcard =
realFilename.substring(realFilename.length() - 4, realFilename.length());
if (wildcard.substring(0, 1).equals(".")) {
// Find wildcard
realFilename =
realFilename.substring(0, realFilename.length() - 4)
+ "_"
+ StringUtil.getFormattedDateTimeNow(true)
+ wildcard;
} else {
// did not find wildcard
realFilename = realFilename + "_" + StringUtil.getFormattedDateTimeNow(true);
}
logDebug(
BaseMessages.getString(PKG, "JobMysqlBulkFile.FileNameChange1.Label")
+ realFilename
+ BaseMessages.getString(PKG, "JobMysqlBulkFile.FileNameChange1.Label"));
}
// User has specified an existing file, We can continue ...
if (log.isDetailed())
logDetailed(
BaseMessages.getString(PKG, "JobMysqlBulkFile.FileExists1.Label")
+ realFilename
+ BaseMessages.getString(PKG, "JobMysqlBulkFile.FileExists2.Label"));
if (connection != null) {
// User has specified a connection, We can continue ...
Database db = new Database(this, connection);
db.shareVariablesWith(this);
try {
db.connect(parentJob.getTransactionId(), null);
// Get schemaname
String realSchemaname = environmentSubstitute(schemaname);
// Get tablename
String realTablename = environmentSubstitute(tablename);
if (db.checkTableExists(realTablename)) {
// The table existe, We can continue ...
if (log.isDetailed())
logDetailed(
BaseMessages.getString(PKG, "JobMysqlBulkFile.TableExists1.Label")
+ realTablename
+ BaseMessages.getString(PKG, "JobMysqlBulkFile.TableExists2.Label"));
// Add schemaname (Most the time Schemaname.Tablename)
if (schemaname != null) {
realTablename = realSchemaname + "." + realTablename;
}
// Set the Limit lines
if (Const.toInt(getRealLimitlines(), 0) > 0) {
LimitNbrLignes = "LIMIT " + getRealLimitlines();
}
// Set list of Column, if null get all columns (*)
if (getRealListColumn() != null) {
ListOfColumn = MysqlString(getRealListColumn());
}
// Fields separator
if (getRealSeparator() != null && outdumpvalue == 0) {
FieldSeparator =
"FIELDS TERMINATED BY '" + Const.replace(getRealSeparator(), "'", "''") + "'";
}
// Lines Terminated by
if (getRealLineterminated() != null && outdumpvalue == 0) {
LinesTerminated =
"LINES TERMINATED BY '"
+ Const.replace(getRealLineterminated(), "'", "''")
+ "'";
}
// High Priority ?
if (isHighPriority()) {
strHighPriority = "HIGH_PRIORITY";
}
if (getRealEnclosed() != null && outdumpvalue == 0) {
if (isOptionEnclosed()) {
OptionEnclosed = "OPTIONALLY ";
}
OptionEnclosed =
OptionEnclosed
+ "ENCLOSED BY '"
+ Const.replace(getRealEnclosed(), "'", "''")
+ "'";
}
// OutFile or Dumpfile
if (outdumpvalue == 0) {
OutDumpText = "INTO OUTFILE";
} else {
OutDumpText = "INTO DUMPFILE";
}
String FILEBulkFile =
"SELECT "
+ strHighPriority
+ " "
+ ListOfColumn
+ " "
+ OutDumpText
+ " '"
+ realFilename
+ "' "
+ FieldSeparator
+ " "
+ OptionEnclosed
+ " "
+ LinesTerminated
+ " FROM "
+ realTablename
+ " "
+ LimitNbrLignes
+ " LOCK IN SHARE MODE";
try {
if (log.isDetailed()) logDetailed(FILEBulkFile);
// Run the SQL
PreparedStatement ps = db.prepareSQL(FILEBulkFile);
ps.execute();
// Everything is OK...we can disconnect now
db.disconnect();
if (isAddFileToResult()) {
// Add filename to output files
ResultFile resultFile =
new ResultFile(
ResultFile.FILE_TYPE_GENERAL,
KettleVFS.getFileObject(realFilename, this),
parentJob.getJobname(),
toString());
result.getResultFiles().put(resultFile.getFile().toString(), resultFile);
}
result.setResult(true);
} catch (SQLException je) {
db.disconnect();
result.setNrErrors(1);
logError(
BaseMessages.getString(PKG, "JobMysqlBulkFile.Error.Label")
+ " "
+ je.getMessage());
} catch (KettleFileException e) {
logError(
BaseMessages.getString(PKG, "JobMysqlBulkFile.Error.Label") + e.getMessage());
result.setNrErrors(1);
}
} else {
// Of course, the table should have been created already before the bulk load
// operation
db.disconnect();
result.setNrErrors(1);
if (log.isDetailed())
logDetailed(
BaseMessages.getString(PKG, "JobMysqlBulkFile.TableNotExists1.Label")
+ realTablename
+ BaseMessages.getString(PKG, "JobMysqlBulkFile.TableNotExists2.Label"));
}
} catch (KettleDatabaseException dbe) {
db.disconnect();
result.setNrErrors(1);
logError(
BaseMessages.getString(PKG, "JobMysqlBulkFile.Error.Label")
+ " "
+ dbe.getMessage());
}
} else {
// No database connection is defined
result.setNrErrors(1);
logError(BaseMessages.getString(PKG, "JobMysqlBulkFile.Nodatabase.Label"));
}
}
} else {
// No file was specified
result.setNrErrors(1);
logError(BaseMessages.getString(PKG, "JobMysqlBulkFile.Nofilename.Label"));
}
return result;
}
