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