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;
      }
    }
  }
Пример #2
0
  @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;
  }
Пример #8
0
  @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;
  }
Пример #10
0
  /**
   * 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();
  }