private byte[] convertStringToBinaryString(ValueMetaInterface v, String string)
throws KettleValueException {
int length = v.getLength();
if (string == null) return new byte[] {};
if (length > -1 && length < string.length()) {
// we need to truncate
String tmp = string.substring(0, length);
if (Const.isEmpty(v.getStringEncoding())) {
return tmp.getBytes();
} else {
try {
return tmp.getBytes(v.getStringEncoding());
} catch (UnsupportedEncodingException e) {
throw new KettleValueException(
"Unable to convert String to Binary with specified string encoding ["
+ v.getStringEncoding()
+ "]",
e);
}
}
} else {
byte text[];
if (Const.isEmpty(v.getStringEncoding())) {
text = string.getBytes();
} else {
try {
text = string.getBytes(v.getStringEncoding());
} catch (UnsupportedEncodingException e) {
throw new KettleValueException(
"Unable to convert String to Binary with specified string encoding ["
+ v.getStringEncoding()
+ "]",
e);
}
}
if (length > string.length()) {
// we need to pad this
// Also for PDI-170: not all encoding use single characters, so we need to cope
// with this.
byte filler[] = " ".getBytes();
int size = text.length + filler.length * (length - string.length());
byte bytes[] = new byte[size];
System.arraycopy(text, 0, bytes, 0, text.length);
if (filler.length == 1) {
java.util.Arrays.fill(bytes, text.length, size, filler[0]);
} else {
// need to copy the filler array in lots of times
// TODO: this was not finished.
}
return bytes;
} else {
// do not need to pad or truncate
return text;
}
}
}
@Override
public String getFieldDefinition(
ValueMetaInterface v,
String tk,
String pk,
boolean use_autoinc,
boolean add_fieldname,
boolean add_cr) {
String retval = "";
String fieldname = v.getName();
int length = v.getLength();
int precision = v.getPrecision();
if (add_fieldname) retval += fieldname + " ";
int type = v.getType();
switch (type) {
case ValueMetaInterface.TYPE_DATE:
retval += "DATETIME";
break;
case ValueMetaInterface.TYPE_BOOLEAN:
retval += "CHAR(1)";
break;
case ValueMetaInterface.TYPE_NUMBER:
case ValueMetaInterface.TYPE_INTEGER:
case ValueMetaInterface.TYPE_BIGNUMBER:
retval += "DECIMAL";
if (length > 0) {
retval += "(" + length;
if (precision > 0) {
retval += ", " + precision;
}
retval += ")";
}
break;
case ValueMetaInterface.TYPE_STRING:
if (length >= DatabaseMeta.CLOB_LENGTH) {
retval += "CLOB";
} else {
retval += "VARCHAR";
if (length > 0) {
retval += "(" + length;
} else {
retval += "("; // Maybe use some default DB String length?
}
retval += ")";
}
break;
default:
retval += " UNKNOWN";
break;
}
if (add_cr) retval += Const.CR;
return retval;
}
private ColumnSpec getColumnSpecFromField(
ValueMetaInterface inputValueMeta,
ValueMetaInterface insertValueMeta,
ValueMetaInterface targetValueMeta) {
logBasic(
"Mapping input field "
+ inputValueMeta.getName()
+ " ("
+ inputValueMeta.getTypeDesc()
+ ")"
+ " to target column "
+ insertValueMeta.getName()
+ " ("
+ targetValueMeta.getOriginalColumnTypeName()
+ ") ");
String targetColumnTypeName = targetValueMeta.getOriginalColumnTypeName();
if (targetColumnTypeName.equals("INTEGER") || targetColumnTypeName.equals("BIGINT")) {
return new ColumnSpec(ColumnSpec.ConstantWidthType.INTEGER_64);
} else if (targetColumnTypeName.equals("BOOLEAN")) {
return new ColumnSpec(ColumnSpec.ConstantWidthType.BOOLEAN);
} else if (targetColumnTypeName.equals("FLOAT")
|| targetColumnTypeName.equals("DOUBLE PRECISION")) {
return new ColumnSpec(ColumnSpec.ConstantWidthType.FLOAT);
} else if (targetColumnTypeName.equals("CHAR")) {
return new ColumnSpec(ColumnSpec.UserDefinedWidthType.CHAR, targetValueMeta.getLength());
} else if (targetColumnTypeName.equals("VARCHAR")) {
return new ColumnSpec(ColumnSpec.VariableWidthType.VARCHAR);
} else if (targetColumnTypeName.equals("DATE")) {
return new ColumnSpec(ColumnSpec.ConstantWidthType.DATE);
} else if (targetColumnTypeName.equals("TIME")) {
return new ColumnSpec(ColumnSpec.ConstantWidthType.TIME);
} else if (targetColumnTypeName.equals("TIMETZ")) {
return new ColumnSpec(ColumnSpec.ConstantWidthType.TIMETZ);
} else if (targetColumnTypeName.equals("TIMESTAMP")) {
return new ColumnSpec(ColumnSpec.ConstantWidthType.TIMESTAMP);
} else if (targetColumnTypeName.equals("TIMESTAMPTZ")) {
return new ColumnSpec(ColumnSpec.ConstantWidthType.TIMESTAMPTZ);
} else if (targetColumnTypeName.equals("INTERVAL")
|| targetColumnTypeName.equals("INTERVAL DAY TO SECOND")) {
return new ColumnSpec(ColumnSpec.ConstantWidthType.INTERVAL);
} else if (targetColumnTypeName.equals("BINARY")) {
return new ColumnSpec(ColumnSpec.VariableWidthType.VARBINARY);
} else if (targetColumnTypeName.equals("VARBINARY")) {
return new ColumnSpec(ColumnSpec.VariableWidthType.VARBINARY);
} else if (targetColumnTypeName.equals("NUMERIC")) {
return new ColumnSpec(ColumnSpec.ConstantWidthType.NUMERIC);
}
throw new IllegalArgumentException(
"Column type " + targetColumnTypeName + " not supported."); // $NON-NLS-1$
}
public static Object[] createObjectsForRow(RowMetaInterface rowMeta, Object[] rowData)
throws KettleValueException {
Object[] values = new Object[rowMeta.size()];
for (int i = 0; i < rowMeta.size(); i++) {
ValueMetaInterface valueMeta = rowMeta.getValueMeta(i);
Object valueData = rowData[i];
// Prevent a NullPointerException below
if (valueData == null || valueMeta == null) {
values[i] = null;
continue;
}
int length = valueMeta.getLength();
switch (valueMeta.getType()) {
case ValueMetaInterface.TYPE_INTEGER:
if (length < 3) {
values[i] = new Byte(valueMeta.getInteger(valueData).byteValue());
} else {
if (length < 5) {
values[i] = new Short(valueMeta.getInteger(valueData).shortValue());
} else {
values[i] = valueMeta.getInteger(valueData);
}
}
break;
case ValueMetaInterface.TYPE_NUMBER:
values[i] = valueMeta.getNumber(valueData);
break;
case ValueMetaInterface.TYPE_DATE:
values[i] = valueMeta.getDate(valueData);
break;
case ValueMetaInterface.TYPE_STRING:
values[i] = valueMeta.getString(valueData);
break;
case ValueMetaInterface.TYPE_BINARY:
values[i] = valueMeta.getBinary(valueData);
break;
case ValueMetaInterface.TYPE_BOOLEAN:
values[i] = valueMeta.getBoolean(valueData);
break;
case ValueMetaInterface.TYPE_BIGNUMBER:
values[i] = valueMeta.getNumber(valueData);
break;
default:
break;
}
}
return values;
}
/** Copy information from the meta-data input to the dialog fields. */
public void getData() {
int i;
for (i = 0; i < input.size(); i++) {
TableItem item = wFields.table.getItem(i);
ValueMetaInterface v = input.getValueMeta(i);
int idx = 1;
if (v.getName() != null) {
item.setText(idx++, v.getName());
}
item.setText(idx++, v.getTypeDesc());
item.setText(idx++, v.getLength() < 0 ? "-" : "" + v.getLength());
item.setText(idx++, v.getPrecision() < 0 ? "-" : "" + v.getPrecision());
item.setText(idx++, Const.NVL(v.getOrigin(), ""));
item.setText(idx++, ValueMeta.getStorageTypeCode(v.getStorageType()));
item.setText(idx++, Const.NVL(v.getConversionMask(), ""));
item.setText(idx++, Const.NVL(v.getCurrencySymbol(), ""));
item.setText(idx++, Const.NVL(v.getDecimalSymbol(), ""));
item.setText(idx++, Const.NVL(v.getGroupingSymbol(), ""));
item.setText(idx++, ValueMeta.getTrimTypeDesc(v.getTrimType()));
item.setText(idx++, Const.NVL(v.getComments(), ""));
}
wFields.optWidth(true);
}
public static ValueMetaInterface cloneValueMeta(ValueMetaInterface source, int targetType)
throws KettlePluginException {
ValueMetaInterface target = null;
// If we're Cloneable and not changing types, call clone()
if (source instanceof Cloneable && source.getType() == targetType) {
target = source.clone();
} else {
target =
createValueMeta(source.getName(), targetType, source.getLength(), source.getPrecision());
}
target.setConversionMask(source.getConversionMask());
target.setDecimalSymbol(source.getDecimalSymbol());
target.setGroupingSymbol(source.getGroupingSymbol());
target.setStorageType(source.getStorageType());
if (source.getStorageMetadata() != null) {
target.setStorageMetadata(
cloneValueMeta(source.getStorageMetadata(), source.getStorageMetadata().getType()));
}
target.setStringEncoding(source.getStringEncoding());
target.setTrimType(source.getTrimType());
target.setDateFormatLenient(source.isDateFormatLenient());
target.setDateFormatLocale(source.getDateFormatLocale());
target.setDateFormatTimeZone(source.getDateFormatTimeZone());
target.setLenientStringToNumber(source.isLenientStringToNumber());
target.setLargeTextField(source.isLargeTextField());
target.setComments(source.getComments());
target.setCaseInsensitive(source.isCaseInsensitive());
target.setIndex(source.getIndex());
target.setOrigin(source.getOrigin());
target.setOriginalAutoIncrement(source.isOriginalAutoIncrement());
target.setOriginalColumnType(source.getOriginalColumnType());
target.setOriginalColumnTypeName(source.getOriginalColumnTypeName());
target.setOriginalNullable(source.isOriginalNullable());
target.setOriginalPrecision(source.getOriginalPrecision());
target.setOriginalScale(source.getOriginalScale());
target.setOriginalSigned(source.isOriginalSigned());
return target;
}
@Override
public String getFieldDefinition(
ValueMetaInterface v,
String tk,
String pk,
boolean use_autoinc,
boolean add_fieldname,
boolean add_cr) {
String retval = "";
String fieldname = v.getName();
int length = v.getLength();
int precision = v.getPrecision();
if (add_fieldname) {
retval += fieldname + " ";
}
int type = v.getType();
switch (type) {
case ValueMetaInterface.TYPE_DATE:
retval += "DATETIME";
break;
case ValueMetaInterface.TYPE_BOOLEAN:
if (supportsBooleanDataType()) {
retval += "BIT";
} else {
retval += "CHAR(1)";
}
break;
case ValueMetaInterface.TYPE_NUMBER:
case ValueMetaInterface.TYPE_INTEGER:
case ValueMetaInterface.TYPE_BIGNUMBER:
if (fieldname.equalsIgnoreCase(tk)
|| // Technical key
fieldname.equalsIgnoreCase(pk) // Primary key
) {
if (use_autoinc) {
retval += "BIGINT PRIMARY KEY IDENTITY(0,1)";
} else {
retval += "BIGINT PRIMARY KEY";
}
} else {
if (precision == 0) {
if (length > 18) {
retval += "DECIMAL(" + length + ",0)";
} else {
if (length > 9) {
retval += "BIGINT";
} else {
retval += "INT";
}
}
} else {
if (precision > 0) {
if (length > 0) {
retval += "DECIMAL(" + length + "," + precision + ")";
}
} else {
retval += "FLOAT(53)";
}
}
}
break;
case ValueMetaInterface.TYPE_STRING:
if (length < getMaxVARCHARLength()) {
// Maybe use some default DB String length in case length<=0
if (length > 0) {
retval += "VARCHAR(" + length + ")";
} else {
retval += "VARCHAR(100)";
}
} else {
retval += "TEXT"; // Up to 2bilion characters.
}
break;
default:
retval += " UNKNOWN";
break;
}
if (add_cr) {
retval += Const.CR;
}
return retval;
}
@Override
public String getFieldDefinition(
ValueMetaInterface v,
String tk,
String pk,
boolean use_autoinc,
boolean add_fieldname,
boolean add_cr) {
String retval = "";
String fieldname = v.getName();
int length = v.getLength();
int precision = v.getPrecision();
if (add_fieldname) {
retval += fieldname + " ";
}
int type = v.getType();
switch (type) {
case ValueMetaInterface.TYPE_DATE:
retval += "TIMESTAMP";
break;
case ValueMetaInterface.TYPE_BOOLEAN:
retval += "CHAR(1)";
break;
case ValueMetaInterface.TYPE_NUMBER:
case ValueMetaInterface.TYPE_INTEGER:
case ValueMetaInterface.TYPE_BIGNUMBER:
if (fieldname.equalsIgnoreCase(tk)
|| // Technical key
fieldname.equalsIgnoreCase(pk) // Primary key
) {
if (use_autoinc) {
retval += "BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1)";
} else {
retval += "BIGINT NOT NULL PRIMARY KEY";
}
} else {
// Integer values...
if (precision == 0) {
if (length > 9) {
retval += "BIGINT";
} else {
if (length > 4) {
retval += "INTEGER";
} else {
retval += "SMALLINT";
}
}
} else {
// Floating point values...
if (length > 18) {
retval += "DECIMAL(" + length;
if (precision > 0) {
retval += ", " + precision;
}
retval += ")";
} else {
retval += "FLOAT";
}
}
}
break;
case ValueMetaInterface.TYPE_STRING:
if (length >= DatabaseMeta.CLOB_LENGTH || length > 32700) {
retval += "CLOB";
} else {
retval += "VARCHAR";
if (length > 0) {
retval += "(" + length;
} else {
retval += "("; // Maybe use some default DB String length?
}
retval += ")";
}
break;
case ValueMetaInterface.TYPE_BINARY:
retval += "BLOB";
break;
default:
retval += "UNKNOWN";
break;
}
if (add_cr) {
retval += Const.CR;
}
return retval;
}
public static final List<Column> getColumns(RowMetaInterface row) {
List<Column> list = new ArrayList<Column>();
for (int i = 0; i < row.size(); i++) {
ValueMetaInterface value = row.getValueMeta(i);
Column column = new Column();
column.setName(value.getName());
int length = value.getLength();
switch (value.getType()) {
case ValueMetaInterface.TYPE_INTEGER:
if (length < 3) {
column.setType(DataType.BYTE);
length = DataType.BYTE.getFixedSize();
} else {
if (length < 5) {
column.setType(DataType.INT);
length = DataType.INT.getFixedSize();
} else {
column.setType(DataType.LONG);
length = DataType.LONG.getFixedSize();
}
}
break;
case ValueMetaInterface.TYPE_NUMBER:
column.setType(DataType.DOUBLE);
length = DataType.DOUBLE.getFixedSize();
break;
case ValueMetaInterface.TYPE_DATE:
column.setType(DataType.SHORT_DATE_TIME);
length = DataType.SHORT_DATE_TIME.getFixedSize();
break;
case ValueMetaInterface.TYPE_STRING:
if (length < 255) {
column.setType(DataType.TEXT);
length *= DataType.TEXT.getUnitSize();
} else {
column.setType(DataType.MEMO);
length *= DataType.MEMO.getUnitSize();
}
break;
case ValueMetaInterface.TYPE_BINARY:
column.setType(DataType.BINARY);
break;
case ValueMetaInterface.TYPE_BOOLEAN:
column.setType(DataType.BOOLEAN);
length = DataType.BOOLEAN.getFixedSize();
break;
case ValueMetaInterface.TYPE_BIGNUMBER:
column.setType(DataType.NUMERIC);
length = DataType.NUMERIC.getFixedSize();
break;
default:
break;
}
if (length >= 0) {
column.setLength((short) length);
}
if (value.getPrecision() >= 1 && value.getPrecision() <= 28) {
column.setPrecision((byte) value.getPrecision());
}
list.add(column);
}
return list;
}
/**
* 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 String getFieldDefinition(
ValueMetaInterface v,
String tk,
String pk,
boolean use_autoinc,
boolean add_fieldname,
boolean add_cr) {
StringBuffer retval = new StringBuffer(128);
String fieldname = v.getName();
int length = v.getLength();
int precision = v.getPrecision();
if (add_fieldname) retval.append(fieldname).append(' ');
int type = v.getType();
switch (type) {
case ValueMetaInterface.TYPE_DATE:
retval.append("TIMESTAMP");
break;
case ValueMetaInterface.TYPE_BOOLEAN:
if (supportsBooleanDataType()) {
retval.append("BOOLEAN");
} else {
retval.append("CHAR(1)");
}
break;
case ValueMetaInterface.TYPE_NUMBER:
case ValueMetaInterface.TYPE_INTEGER:
case ValueMetaInterface.TYPE_BIGNUMBER:
if (fieldname.equalsIgnoreCase(tk)
|| // Technical key
fieldname.equalsIgnoreCase(pk) // Primary key
) {
retval.append(
"BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 0, INCREMENT BY 1) PRIMARY KEY");
} else {
if (length > 0) {
if (precision > 0 || length > 18) {
retval.append("NUMERIC(").append(length).append(", ").append(precision).append(')');
} else {
if (length > 9) {
retval.append("BIGINT");
} else {
if (length < 5) {
retval.append("SMALLINT");
} else {
retval.append("INTEGER");
}
}
}
} else {
retval.append("DOUBLE PRECISION");
}
}
break;
case ValueMetaInterface.TYPE_STRING:
if (length >= DatabaseMeta.CLOB_LENGTH) {
retval.append("TEXT");
} else {
retval.append("VARCHAR");
if (length > 0) {
retval.append('(').append(length);
} else {
retval.append('('); // Maybe use some default DB String length?
}
retval.append(')');
}
break;
default:
retval.append(" UNKNOWN");
break;
}
if (add_cr) retval.append(Const.CR);
return retval.toString();
}