Java Code Examples for org.pentaho.di.core.row.ValueMetaInterface#TYPE_TIMESTAMP

private ValueMetaInterface getValueMetaInterface( String fieldName, int fieldType ) {
  switch ( fieldType ) {
    case ValueMetaInterface.TYPE_INET:
      return new ValueMetaInternetAddress( fieldName );
    case ValueMetaInterface.TYPE_STRING:
      return new ValueMetaString( fieldName );
    case ValueMetaInterface.TYPE_INTEGER:
      return new ValueMetaInteger( fieldName );
    case ValueMetaInterface.TYPE_NUMBER:
      return new ValueMetaNumber( fieldName );
    case ValueMetaInterface.TYPE_BIGNUMBER:
      return new ValueMetaBigNumber( fieldName );
    case ValueMetaInterface.TYPE_TIMESTAMP:
      return new ValueMetaTimestamp( fieldName );
    case ValueMetaInterface.TYPE_DATE:
      return new ValueMetaDate( fieldName );
    case ValueMetaInterface.TYPE_BOOLEAN:
      return new ValueMetaBoolean( fieldName );
    case ValueMetaInterface.TYPE_BINARY:
      return new ValueMetaBinary( fieldName );
  }
  return null;
}
 

/**
 * This method allows a database dialect to convert database specific data types to Kettle data types.
 *
 * @param rs
 *          The result set to use
 * @param val
 *          The description of the value to retrieve
 * @param index
 *          the index on which we need to retrieve the value, 0-based.
 * @return The correctly converted Kettle data type corresponding to the valueMeta description.
 * @throws KettleDatabaseException
 */
@Override
public Object getValueFromResultSet( ResultSet rs, ValueMetaInterface val, int index ) throws KettleDatabaseException {
  Object data;

  try {
    switch ( val.getType() ) {
      case ValueMetaInterface.TYPE_TIMESTAMP:
      case ValueMetaInterface.TYPE_DATE:
        if ( val.getOriginalColumnType() == java.sql.Types.TIMESTAMP ) {
          data = rs.getTimestamp( index + 1 );
          break; // Timestamp extends java.util.Date
        } else if ( val.getOriginalColumnType() == java.sql.Types.TIME ) {
          data = rs.getTime( index + 1 );
          break;
        } else {
          data = rs.getDate( index + 1 );
          break;
        }
      default:
        return super.getValueFromResultSet( rs, val, index );
    }
    if ( rs.wasNull() ) {
      data = null;
    }
  } catch ( SQLException e ) {
    throw new KettleDatabaseException( "Unable to get value '"
      + val.toStringMeta() + "' from database resultset, index " + index, e );
  }

  return data;
}
 

private void addValueMeta( int pdiType, String pentahoFieldName ) {
  switch ( pdiType ) {
    case ValueMetaInterface.TYPE_BINARY:
      fields.addValueMeta( new ValueMetaBinary( pentahoFieldName ) );
      break;
    case ValueMetaInterface.TYPE_BIGNUMBER:
      fields.addValueMeta( new ValueMetaBigNumber( pentahoFieldName ) );
      break;
    case ValueMetaInterface.TYPE_BOOLEAN:
      fields.addValueMeta( new ValueMetaBoolean( pentahoFieldName ) );
      break;
    case ValueMetaInterface.TYPE_DATE:
      fields.addValueMeta( new ValueMetaDate( pentahoFieldName ) );
      break;
    case ValueMetaInterface.TYPE_INET:
      fields.addValueMeta( new ValueMetaInternetAddress( pentahoFieldName ) );
      break;
    case ValueMetaInterface.TYPE_INTEGER:
      fields.addValueMeta( new ValueMetaInteger( pentahoFieldName ) );
      break;
    case ValueMetaInterface.TYPE_NUMBER:
      fields.addValueMeta( new ValueMetaNumber( pentahoFieldName ) );
      break;
    case ValueMetaInterface.TYPE_STRING:
      fields.addValueMeta( new ValueMetaString( pentahoFieldName ) );
      break;
    case ValueMetaInterface.TYPE_TIMESTAMP:
      fields.addValueMeta( new ValueMetaTimestamp( pentahoFieldName ) );
      break;
  }
}
 

private void addValueMeta( int pdiType, String pentahoFieldName ) {
  switch ( pdiType ) {
    case ValueMetaInterface.TYPE_BINARY:
      fields.addValueMeta( new ValueMetaBinary( pentahoFieldName ) );
      break;
    case ValueMetaInterface.TYPE_BIGNUMBER:
      fields.addValueMeta( new ValueMetaBigNumber( pentahoFieldName ) );
      break;
    case ValueMetaInterface.TYPE_BOOLEAN:
      fields.addValueMeta( new ValueMetaBoolean( pentahoFieldName ) );
      break;
    case ValueMetaInterface.TYPE_DATE:
      fields.addValueMeta( new ValueMetaDate( pentahoFieldName ) );
      break;
    case ValueMetaInterface.TYPE_INET:
      fields.addValueMeta( new ValueMetaInternetAddress( pentahoFieldName ) );
      break;
    case ValueMetaInterface.TYPE_INTEGER:
      fields.addValueMeta( new ValueMetaInteger( pentahoFieldName ) );
      break;
    case ValueMetaInterface.TYPE_NUMBER:
      fields.addValueMeta( new ValueMetaNumber( pentahoFieldName ) );
      break;
    case ValueMetaInterface.TYPE_STRING:
      fields.addValueMeta( new ValueMetaString( pentahoFieldName ) );
      break;
    case ValueMetaInterface.TYPE_TIMESTAMP:
      fields.addValueMeta( new ValueMetaTimestamp( pentahoFieldName ) );
      break;
  }
}
 

@Override
public String getFieldDefinition( ValueMetaInterface v, String tk, String pk, boolean useAutoinc,
                                  boolean addFieldName, boolean addCr ) {
  String retval = "";

  String fieldname = v.getName();
  int length = v.getLength();
  int precision = v.getPrecision();

  if ( addFieldName ) {
    if ( Const.indexOfString( fieldname, getReservedWords() ) >= 0 ) {
      retval += getStartQuote() + fieldname + getEndQuote();
    } else {
      retval += fieldname + " ";
    }
  }

  int type = v.getType();
  switch ( type ) {
    case ValueMetaInterface.TYPE_TIMESTAMP:
    case ValueMetaInterface.TYPE_DATE:
      retval += "TIMESTAMP";
      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
      ) {
        retval += "BIGINT NOT NULL PRIMARY KEY";
      } else {
        if ( length > 0 ) {
          if ( precision > 0 || length > 18 ) {
            retval += "DECIMAL(" + length;
            if ( precision > 0 ) {
              retval += ", " + precision;
            }
            retval += ")";
          } else {
            if ( length > 9 ) {
              retval += "BIGINT";
            } else {
              if ( length < 5 ) {
                retval += "SMALLINT";
              } else {
                retval += "INTEGER";
              }
            }
          }
        } else {
          retval += "DOUBLE";
        }
      }
      break;
    case ValueMetaInterface.TYPE_STRING:
      if ( length < 32720 ) {
        retval += "VARCHAR";
        if ( length > 0 ) {
          retval += "(" + length + ")";
        } else {
          retval += "(8000)"; // Maybe use some default DB String length?
        }
      } else {
        retval += "BLOB SUB_TYPE TEXT";
      }
      break;
    case ValueMetaInterface.TYPE_BINARY:
      retval += "BLOB";
      break;
    default:
      retval += "UNKNOWN";
      break;
  }

  if ( addCr ) {
    retval += Const.CR;
  }

  return retval;
}
 

@Test
public void testHashCodes() throws Exception {
  ValueMetaBase valueMetaString = new ValueMetaBase( );

  valueMetaString.type = ValueMetaInterface.TYPE_BOOLEAN;
  assertEquals( valueMetaString.hashCode( true ),  1231 );

  SimpleDateFormat sdf = new SimpleDateFormat( "dd/M/yyyy" );
  String dateInString = "1/1/2018";
  Date dateObj = sdf.parse( dateInString );
  valueMetaString.type = ValueMetaInterface.TYPE_DATE;
  assertEquals( valueMetaString.hashCode( dateObj ),  -1358655136 );

  Double numberObj = Double.valueOf( 5.1 );
  valueMetaString.type = ValueMetaInterface.TYPE_NUMBER;
  assertEquals( valueMetaString.hashCode( numberObj ),  645005312 );

  valueMetaString.type = ValueMetaInterface.TYPE_STRING;
  assertEquals( valueMetaString.hashCode( "test" ),  3556498 );

  Long longObj = 123L;
  valueMetaString.type = ValueMetaInterface.TYPE_INTEGER;
  assertEquals( valueMetaString.hashCode( longObj ), 123 );

  BigDecimal bDecimalObj = new BigDecimal( 123.1 );
  valueMetaString.type = ValueMetaInterface.TYPE_BIGNUMBER;
  assertEquals( valueMetaString.hashCode( bDecimalObj ),  465045870 );

  byte[] bBinary = new byte[2];
  bBinary[0] = 1;
  bBinary[1] = 0;
  valueMetaString.type = ValueMetaInterface.TYPE_BINARY;
  assertEquals( valueMetaString.hashCode( bBinary ),  992 );

  Timestamp timestampObj = Timestamp.valueOf( "2018-01-01 10:10:10.000000000" );
  valueMetaString.type = ValueMetaInterface.TYPE_TIMESTAMP;
  assertEquals( valueMetaString.hashCode( timestampObj ),  -1322045776 );

  byte[] ipAddr = new byte[]{127, 0, 0, 1};
  InetAddress addrObj = InetAddress.getByAddress( ipAddr );
  valueMetaString.type = ValueMetaInterface.TYPE_INET;
  assertEquals( valueMetaString.hashCode( addrObj ),  2130706433 );

  valueMetaString.type = ValueMetaInterface.TYPE_NONE;
  assertEquals( valueMetaString.hashCode( "any" ),  0 );
}
 

protected static StringBuilder rowsToCSV( RowMetaInterface meta, List<Object[]> rows ) throws KettleValueException {
  StringBuilder builder = new StringBuilder();
  // header row
  int i = 0;
  for ( ValueMetaInterface v : meta.getValueMetaList() ) {
    String name = quote( v.getName() );
    builder.append( i > 0 ? "," : "" ).append( name );
    i++;
  }
  builder.append( "\n" );
  for ( Object[] row : rows ) {
    for ( i = 0; i < meta.size(); i++ ) {
      String value;
      ValueMetaInterface vm = meta.getValueMeta( i );
      if ( row[i] == null || Const.isEmpty( vm.getString( row[i] ) ) ) {
        value = "?";
      } else {
        //switch ( meta.getValueMetaList().get( i ).getType() ) {
        switch ( vm.getType() ) {
          case ValueMetaInterface.TYPE_NUMBER:
          case ValueMetaInterface.TYPE_INTEGER:
          case ValueMetaInterface.TYPE_BIGNUMBER:
            value = vm.getString( row[i] );
            break;
          case ValueMetaInterface.TYPE_DATE:
            int offset = TZ.getOffset( vm.getDate( row[i] ).getTime() );
            value = "" + ( vm.getDate( row[i] ).getTime() + offset );
            break;
          case ValueMetaInterface.TYPE_TIMESTAMP:
            offset = TZ.getOffset( vm.getDate( row[i] ).getTime() );
            value = "" + ( vm.getDate( row[i] ).getTime() + offset );
            break;
          case ValueMetaInterface.TYPE_BOOLEAN:
            value = "" + ( vm.getBoolean( row[i] ) ? "1" : "0" );
            break;
          // TODO throw an exception for Serializable/Binary
          default:
            value = quote( vm.getString( row[i] ) );
        }
      }
      builder.append( i > 0 ? "," : "" ).append( value );
    }
    builder.append( "\n" );
  }

  return builder;
}
 

public List<? extends IAvroInputField> getDefaultFields() throws Exception {
  ArrayList<AvroInputField> fields = new ArrayList<>();

  Schema avroSchema = readAvroSchema();
  for ( Schema.Field f : avroSchema.getFields() ) {
    AvroSpec.DataType actualAvroType = findActualDataType( f );
    AvroSpec.DataType supportedAvroType = null;
    if ( actualAvroType != null && isSupported( actualAvroType ) ) {
      supportedAvroType = actualAvroType;
    }

    if ( supportedAvroType == null ) {
      // Todo: log a message about skipping unsupported fields
      continue;
    }

    int pentahoType = 0;
    switch ( supportedAvroType ) {
      case DATE:
        pentahoType = ValueMetaInterface.TYPE_DATE;
        break;
      case DOUBLE:
        pentahoType = ValueMetaInterface.TYPE_NUMBER;
        break;
      case FLOAT:
        pentahoType = ValueMetaInterface.TYPE_NUMBER;
        break;
      case LONG:
        pentahoType = ValueMetaInterface.TYPE_INTEGER;
        break;
      case BOOLEAN:
        pentahoType = ValueMetaInterface.TYPE_BOOLEAN;
        break;
      case INTEGER:
        pentahoType = ValueMetaInterface.TYPE_INTEGER;
        break;
      case STRING:
        pentahoType = ValueMetaInterface.TYPE_STRING;
        break;
      case BYTES:
        pentahoType = ValueMetaInterface.TYPE_BINARY;
        break;
      case DECIMAL:
        pentahoType = ValueMetaInterface.TYPE_BIGNUMBER;
        break;
      case TIMESTAMP_MILLIS:
        pentahoType = ValueMetaInterface.TYPE_TIMESTAMP;
        break;
    }

    // If this is a Pentaho 8 Avro field name, use the ValueMetaInterface type encoded in the Avro field name instead
    FieldName fieldName = parseFieldName( f.name() );
    if ( fieldName != null ) {
      pentahoType = fieldName.type;
    }

    AvroInputField avroInputField = new AvroInputField();
    avroInputField.setFormatFieldName( f.name() );
    avroInputField.setPentahoFieldName( avroInputField.getDisplayableAvroFieldName() );
    avroInputField.setFormatFieldName( f.name() );
    avroInputField.setPentahoType( pentahoType );
    avroInputField.setAvroType( actualAvroType );
    fields.add( avroInputField );
  }

  return fields;
}
 

@Override
public String getFieldDefinition( ValueMetaInterface v, String tk, String pk, boolean useAutoinc,
                                  boolean addFieldName, boolean addCr ) {
  StringBuilder retval = new StringBuilder( 128 );

  String fieldname = v.getName();
  int length = v.getLength();
  int precision = v.getPrecision();

  if ( addFieldName ) {
    retval.append( fieldname ).append( ' ' );
  }

  int type = v.getType();
  switch ( type ) {
    case ValueMetaInterface.TYPE_TIMESTAMP:
      if ( supportsTimestampDataType() ) {
        retval.append( "TIMESTAMP" );
      } else {
        retval.append( "DATE" );
      }
      break;
    case ValueMetaInterface.TYPE_DATE:
      retval.append( "DATE" );
      break;
    case ValueMetaInterface.TYPE_BOOLEAN:
      retval.append( "CHAR(1)" );
      break;
    case ValueMetaInterface.TYPE_NUMBER:
    case ValueMetaInterface.TYPE_BIGNUMBER:
      retval.append( "NUMBER" );
      if ( length > 0 ) {
        retval.append( '(' ).append( length );
        if ( precision > 0 ) {
          retval.append( ", " ).append( precision );
        }
        retval.append( ')' );
      }
      break;
    case ValueMetaInterface.TYPE_INTEGER:
      retval.append( "INTEGER" );
      break;
    case ValueMetaInterface.TYPE_STRING:
      if ( length >= DatabaseMeta.CLOB_LENGTH ) {
        retval.append( "CLOB" );
      } else {
        if ( length == 1 ) {
          retval.append( "CHAR(1)" );
        } else if ( length > 0 && length <= getMaxVARCHARLength() ) {
          retval.append( "VARCHAR2(" ).append( length ).append( ')' );
        } else {
          if ( length <= 0 ) {
            retval.append( "VARCHAR2(2000)" ); // We don't know, so we just use the maximum...
          } else {
            retval.append( "CLOB" );
          }
        }
      }
      break;
    case ValueMetaInterface.TYPE_BINARY: // the BLOB can contain binary data.
      retval.append( "BLOB" );
      break;
    default:
      retval.append( " UNKNOWN" );
      break;
  }

  if ( addCr ) {
    retval.append( Const.CR );
  }

  return retval.toString();
}
 

protected static List<Object> rowsToCSVNew( RowMetaInterface meta, List<Object[]> rows ) throws KettleException {
  List<Object> results = new ArrayList<>( 2 );
  boolean needsBase64;
  CharsetEncoder encoder = Charset.forName( "US-ASCII" ).newEncoder();
  Charset utf8 = Charset.forName( "UTF-8" );

  ByteArrayOutputStream bos = new ByteArrayOutputStream();
  BufferedOutputStream buf = new BufferedOutputStream( bos );

  // header row
  StringBuilder builder = new StringBuilder();
  int i = 0;
  for ( ValueMetaInterface v : meta.getValueMetaList() ) {
    String name = quote( v.getName() );
    builder.append( i > 0 ? "," : "" ).append( name );
    i++;
  }
  builder.append( "\n" );

  // We look for non-ascii characters and, if found, encode to ascii base64 first. For some reason,
  // encoding directly to utf-8 on the Java side (Mac OS X; Java 8) causes the python utf-8 decoder to hang
  // when there are non-ascii characters (such as in Mayagüez) present. Encoding to base64 first, then decoding
  // this on the python side seems to fix the issue.
  needsBase64 = !encoder.canEncode( builder.toString() );
  try {
    buf.write( builder.toString().getBytes( utf8 ) );

    for ( Object[] row : rows ) {
      builder.setLength( 0 );
      for ( i = 0; i < meta.size(); i++ ) {
        String value;
        ValueMetaInterface vm = meta.getValueMeta( i );
        if ( row[i] == null || Const.isEmpty( vm.getString( row[i] ) ) ) {
          value = "?";
        } else {
          //switch ( meta.getValueMetaList().get( i ).getType() ) {
          switch ( vm.getType() ) {
            case ValueMetaInterface.TYPE_NUMBER:
            case ValueMetaInterface.TYPE_INTEGER:
            case ValueMetaInterface.TYPE_BIGNUMBER:
              value = vm.getString( row[i] );
              break;
            case ValueMetaInterface.TYPE_DATE:
              int offset = TZ.getOffset( vm.getDate( row[i] ).getTime() );
              value = "" + ( vm.getDate( row[i] ).getTime() + offset );
              break;
            case ValueMetaInterface.TYPE_TIMESTAMP:
              offset = TZ.getOffset( vm.getDate( row[i] ).getTime() );
              value = "" + ( vm.getDate( row[i] ).getTime() + offset );
              break;
            case ValueMetaInterface.TYPE_BOOLEAN:
              value = "" + ( vm.getBoolean( row[i] ) ? "1" : "0" );
              break;
            // TODO throw an exception for Serializable/Binary
            default:
              value = quote( vm.getString( row[i] ) );
          }
        }
        builder.append( i > 0 ? "," : "" ).append( value );
      }
      builder.append( "\n" );

      if ( !needsBase64 ) {
        needsBase64 = !encoder.canEncode( builder.toString() );
      }
      buf.write( builder.toString().getBytes( utf8 ) );
    }

    buf.flush();
    buf.close();
  } catch ( IOException e ) {
    throw new KettleException( e );
  }

  byte[] bytes = bos.toByteArray();
  if ( needsBase64 ) {
    bytes = Base64.encodeBase64( bytes );
  }
  results.add( bytes );
  results.add( needsBase64 );

  return results;
}
 

@Test
public void testGetValueFromNode() throws Exception {

  ValueMetaBase valueMetaBase = null;
  Node xmlNode = null;

  valueMetaBase = new ValueMetaBase( "test", ValueMetaInterface.TYPE_STRING );
  xmlNode = XMLHandler.loadXMLString( "<value-data>String val</value-data>" ).getFirstChild();
  assertEquals( "String val", valueMetaBase.getValue( xmlNode ) );

  valueMetaBase = new ValueMetaBase( "test", ValueMetaInterface.TYPE_NUMBER );
  xmlNode = XMLHandler.loadXMLString( "<value-data>689.2</value-data>" ).getFirstChild();
  assertEquals( 689.2, valueMetaBase.getValue( xmlNode ) );

  valueMetaBase = new ValueMetaBase( "test", ValueMetaInterface.TYPE_NUMBER );
  xmlNode = XMLHandler.loadXMLString( "<value-data>689.2</value-data>" ).getFirstChild();
  assertEquals( 689.2, valueMetaBase.getValue( xmlNode ) );

  valueMetaBase = new ValueMetaBase( "test", ValueMetaInterface.TYPE_INTEGER );
  xmlNode = XMLHandler.loadXMLString( "<value-data>68933</value-data>" ).getFirstChild();
  assertEquals( 68933l, valueMetaBase.getValue( xmlNode ) );

  valueMetaBase = new ValueMetaBase( "test", ValueMetaInterface.TYPE_DATE );
  xmlNode = XMLHandler.loadXMLString( "<value-data>2017/11/27 08:47:10.000</value-data>" ).getFirstChild();
  assertEquals( XMLHandler.stringToDate( "2017/11/27 08:47:10.000" ), valueMetaBase.getValue( xmlNode ) );

  valueMetaBase = new ValueMetaBase( "test", ValueMetaInterface.TYPE_TIMESTAMP );
  xmlNode = XMLHandler.loadXMLString( "<value-data>2017/11/27 08:47:10.123456789</value-data>" ).getFirstChild();
  assertEquals( XMLHandler.stringToTimestamp( "2017/11/27 08:47:10.123456789" ), valueMetaBase.getValue( xmlNode ) );

  valueMetaBase = new ValueMetaBase( "test", ValueMetaInterface.TYPE_BOOLEAN );
  xmlNode = XMLHandler.loadXMLString( "<value-data>Y</value-data>" ).getFirstChild();
  assertEquals( true, valueMetaBase.getValue( xmlNode ) );

  valueMetaBase = new ValueMetaBase( "test", ValueMetaInterface.TYPE_BINARY );
  byte[] bytes = { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 };
  String s = XMLHandler.encodeBinaryData( bytes );
  xmlNode = XMLHandler.loadXMLString( "<value-data>test<binary-value>" + s + "</binary-value></value-data>" ).getFirstChild();
  assertArrayEquals( bytes, (byte[]) valueMetaBase.getValue( xmlNode ) );

  valueMetaBase = new ValueMetaBase( "test", ValueMetaInterface.TYPE_STRING );
  xmlNode = XMLHandler.loadXMLString( "<value-data></value-data>" ).getFirstChild();
  assertNull( valueMetaBase.getValue( xmlNode ) );
}
 

@Override
public String getFieldDefinition( ValueMetaInterface v, String tk, String pk, boolean useAutoinc,
                                  boolean addFieldName, boolean addCr ) {
  StringBuilder retval = new StringBuilder( 128 );

  String fieldname = v.getName();
  int length = v.getLength();
  int precision = v.getPrecision();

  if ( addFieldName ) {
    retval.append( fieldname ).append( ' ' );
  }

  int type = v.getType();
  switch ( type ) {
    case ValueMetaInterface.TYPE_TIMESTAMP:
    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( "LONGVARCHAR" );
      } 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 ( addCr ) {
    retval.append( Const.CR );
  }

  return retval.toString();
}
 

@Override
public String getFieldDefinition( ValueMetaInterface v, String tk, String pk, boolean useAutoinc,
                                  boolean addFieldName, boolean addCr ) {
  String retval = "";

  String fieldname = v.getName();
  int length = v.getLength();
  int precision = v.getPrecision();

  if ( addFieldName ) {
    retval += fieldname + " ";
  }

  int type = v.getType();
  switch ( type ) {
    case ValueMetaInterface.TYPE_TIMESTAMP:
    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 ( addCr ) {
    retval += Const.CR;
  }

  return retval;
}
 

@Override
public String getFieldDefinition( ValueMetaInterface v, String tk, String pk, boolean useAutoinc,
                                  boolean addFieldName, boolean addCr ) {
  String retval = "";

  String fieldname = v.getName();
  int length = v.getLength();
  int precision = v.getPrecision();

  if ( addFieldName ) {
    retval += fieldname + " ";
  }

  int type = v.getType();
  switch ( type ) {
    case ValueMetaInterface.TYPE_TIMESTAMP:
    case ValueMetaInterface.TYPE_DATE:
      retval += "TIMESTAMP";
      break;
    case ValueMetaInterface.TYPE_BOOLEAN:
      if ( supportsBooleanDataType() ) {
        retval += "BOOLEAN";
      } 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
      ) {
        retval += "BIGSERIAL";
      } else {
        if ( length > 0 ) {
          if ( precision > 0 || length > 18 ) {
            retval += "NUMERIC(" + length + ", " + precision + ")";
          } else {
            if ( length > 9 ) {
              retval += "BIGINT";
            } else {
              if ( length < 5 ) {
                retval += "SMALLINT";
              } else {
                retval += "INTEGER";
              }
            }
          }

        } else {
          retval += "DOUBLE PRECISION";
        }
      }
      break;
    case ValueMetaInterface.TYPE_STRING:
      if ( length < 1 || length >= DatabaseMeta.CLOB_LENGTH ) {
        retval += "TEXT";
      } else {
        retval += "VARCHAR(" + length + ")";
      }
      break;
    default:
      retval += " UNKNOWN";
      break;
  }

  if ( addCr ) {
    retval += Const.CR;
  }

  return retval;
}
 

/**
 * This method allows a database dialect to convert database specific data types to Kettle data types.
 *
 * @param resultSet
 *          The result set to use
 * @param valueMeta
 *          The description of the value to retrieve
 * @param index
 *          the index on which we need to retrieve the value, 0-based.
 * @return The correctly converted Kettle data type corresponding to the valueMeta description.
 * @throws KettleDatabaseException
 */
@Override
public Object getValueFromResultSet( ResultSet rs, ValueMetaInterface val, int i ) throws KettleDatabaseException {
  Object data = null;

  try {
    switch ( val.getType() ) {
      case ValueMetaInterface.TYPE_BOOLEAN:
        data = Boolean.valueOf( rs.getBoolean( i + 1 ) );
        break;
      case ValueMetaInterface.TYPE_NUMBER:
        data = new Double( rs.getDouble( i + 1 ) );
        break;
      case ValueMetaInterface.TYPE_BIGNUMBER:
        data = rs.getBigDecimal( i + 1 );
        break;
      case ValueMetaInterface.TYPE_INTEGER:
        data = Long.valueOf( rs.getLong( i + 1 ) );
        break;
      case ValueMetaInterface.TYPE_STRING:
        if ( val.isStorageBinaryString() ) {
          data = rs.getBytes( i + 1 );
        } else {
          data = rs.getString( i + 1 );
        }
        break;
      case ValueMetaInterface.TYPE_BINARY:
        if ( supportsGetBlob() ) {
          Blob blob = rs.getBlob( i + 1 );
          if ( blob != null ) {
            data = blob.getBytes( 1L, (int) blob.length() );
          } else {
            data = null;
          }
        } else {
          data = rs.getBytes( i + 1 );
        }
        break;
      case ValueMetaInterface.TYPE_TIMESTAMP:
      case ValueMetaInterface.TYPE_DATE:
        if ( val.getOriginalColumnType() == java.sql.Types.TIME ) {
          // Neoview can not handle getDate / getTimestamp for a Time column
          data = rs.getTime( i + 1 );
          break; // Time is a subclass of java.util.Date, the default date
                 // will be 1970-01-01
        } else if ( val.getPrecision() != 1 && supportsTimeStampToDateConversion() ) {
          data = rs.getTimestamp( i + 1 );
          break; // Timestamp extends java.util.Date
        } else {
          data = rs.getDate( i + 1 );
          break;
        }
      default:
        break;
    }
    if ( rs.wasNull() ) {
      data = null;
    }
  } catch ( SQLException e ) {
    throw new KettleDatabaseException( "Unable to get value '"
      + val.toStringMeta() + "' from database resultset, index " + i, e );
  }

  return data;
}
 

@Override
public String getFieldDefinition( ValueMetaInterface v, String tk, String pk, boolean useAutoinc,
                                  boolean addFieldName, boolean addCr ) {
  String retval = "";

  String fieldname = v.getName();
  int length = v.getLength();
  int precision = v.getPrecision();

  if ( addFieldName ) {
    retval += fieldname + " ";
  }

  int type = v.getType();
  switch ( type ) {
    case ValueMetaInterface.TYPE_TIMESTAMP:
    case ValueMetaInterface.TYPE_DATE:
      retval += "TIMESTAMP";
      break;
    case ValueMetaInterface.TYPE_BOOLEAN:
      if ( supportsBooleanDataType() ) {
        retval += "BOOLEAN";
      } 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 ( length > 9 ) {
          retval += "BIGSERIAL";
        } else {
          retval += "SERIAL";
        }
      } else {
        if ( length > 0 ) {
          if ( precision > 0 || length > 18 ) {
            retval += "NUMERIC(" + length + ", " + precision + ")";
          } else {
            if ( length > 9 ) {
              retval += "BIGINT";
            } else {
              if ( length < 5 ) {
                retval += "SMALLINT";
              } else {
                retval += "INTEGER";
              }
            }
          }

        } else {
          retval += "DOUBLE PRECISION";
        }
      }
      break;
    case ValueMetaInterface.TYPE_STRING:
      retval += "VARCHAR";
      if ( length > 0 ) {
        retval += "(" + length;
      } else {
        retval += "("; // Maybe use some default DB String length?
      }
      retval += ")";
      break;
    default:
      retval += " UNKNOWN";
      break;
  }

  if ( addCr ) {
    retval += Const.CR;
  }

  return retval;
}
 

public static String getGetSignature( String accessor, ValueMetaInterface v ) {
  StringBuilder sb = new StringBuilder();

  switch ( v.getType() ) {
    case ValueMetaInterface.TYPE_BIGNUMBER:
      sb.append( "BigDecimal " );
      break;
    case ValueMetaInterface.TYPE_BINARY:
      sb.append( "byte[] " );
      break;
    case ValueMetaInterface.TYPE_BOOLEAN:
      sb.append( "Boolean " );
      break;
    case ValueMetaInterface.TYPE_DATE:
      sb.append( "Date " );
      break;
    case ValueMetaInterface.TYPE_INTEGER:
      sb.append( "Long " );
      break;
    case ValueMetaInterface.TYPE_NUMBER:
      sb.append( "Double " );
      break;
    case ValueMetaInterface.TYPE_STRING:
      sb.append( "String " );
      break;
    case ValueMetaInterface.TYPE_INET:
      sb.append( "InetAddress " );
      break;
    case ValueMetaInterface.TYPE_TIMESTAMP:
      sb.append( "Timestamp " );
      break;
    case ValueMetaInterface.TYPE_SERIALIZABLE:
    default:
      sb.append( "Object " );
      break;
  }

  if ( validJavaIdentifier.matcher( v.getName() ).matches() ) {
    sb.append( v.getName() );
  } else {
    sb.append( "value" );
  }
  String name = getNativeDataTypeSimpleName( v );
  sb
    .append( " = " ).append( accessor ).append( ".get" ).append( "-".equals( name ) ? "Object" : name )
    .append( "(r);" );

  return sb.toString();
}
 

@Test
public void testGetDataXML() throws IOException {
  BigDecimal bigDecimal = BigDecimal.ONE;
  ValueMetaBase valueDoubleMetaBase =
    new ValueMetaBase( String.valueOf( bigDecimal ), ValueMetaInterface.TYPE_BIGNUMBER );
  assertEquals(
    "<value-data>" + Encode.forXml( String.valueOf( bigDecimal ) ) + "</value-data>" + SystemUtils.LINE_SEPARATOR,
    valueDoubleMetaBase.getDataXML( bigDecimal ) );

  boolean valueBoolean = Boolean.TRUE;
  ValueMetaBase valueBooleanMetaBase =
    new ValueMetaBase( String.valueOf( valueBoolean ), ValueMetaInterface.TYPE_BOOLEAN );
  assertEquals(
    "<value-data>" + Encode.forXml( String.valueOf( valueBoolean ) ) + "</value-data>" + SystemUtils.LINE_SEPARATOR,
    valueBooleanMetaBase.getDataXML( valueBoolean ) );

  Date date = new Date( 0 );
  ValueMetaBase dateMetaBase =
    new ValueMetaBase( date.toString(), ValueMetaInterface.TYPE_DATE );
  SimpleDateFormat formaterData = new SimpleDateFormat( ValueMetaBase.DEFAULT_DATE_FORMAT_MASK );
  assertEquals(
    "<value-data>" + Encode.forXml( formaterData.format( date ) ) + "</value-data>" + SystemUtils.LINE_SEPARATOR,
    dateMetaBase.getDataXML( date ) );

  InetAddress inetAddress = InetAddress.getByName( "127.0.0.1" );
  ValueMetaBase inetAddressMetaBase =
    new ValueMetaBase( inetAddress.toString(), ValueMetaInterface.TYPE_INET );
  assertEquals( "<value-data>" + Encode.forXml( inetAddress.toString() ) + "</value-data>" + SystemUtils.LINE_SEPARATOR,
    inetAddressMetaBase.getDataXML( inetAddress ) );

  long value = Long.MAX_VALUE;
  ValueMetaBase integerMetaBase = new ValueMetaBase( String.valueOf( value ), ValueMetaInterface.TYPE_INTEGER );
  assertEquals( "<value-data>" + Encode.forXml( String.valueOf( value ) ) + "</value-data>" + SystemUtils.LINE_SEPARATOR,
    integerMetaBase.getDataXML( value ) );

  String stringValue = "TEST_STRING";
  ValueMetaBase valueMetaBase = new ValueMetaString( stringValue );
  assertEquals( "<value-data>" + Encode.forXml( stringValue ) + "</value-data>" + SystemUtils.LINE_SEPARATOR,
    valueMetaBase.getDataXML( stringValue ) );

  Timestamp timestamp = new Timestamp( 0 );
  ValueMetaBase valueMetaBaseTimeStamp = new ValueMetaBase( timestamp.toString(), ValueMetaInterface.TYPE_TIMESTAMP );
  SimpleDateFormat formater = new SimpleDateFormat( ValueMetaBase.DEFAULT_TIMESTAMP_FORMAT_MASK );
  assertEquals(
    "<value-data>" + Encode.forXml( formater.format( timestamp ) ) + "</value-data>" + SystemUtils.LINE_SEPARATOR,
    valueMetaBaseTimeStamp.getDataXML( timestamp ) );

  byte[] byteTestValues = { 0, 1, 2, 3 };
  ValueMetaBase valueMetaBaseByteArray = new ValueMetaBase( byteTestValues.toString(), ValueMetaInterface.TYPE_STRING );
  valueMetaBaseByteArray.setStorageType( ValueMetaInterface.STORAGE_TYPE_BINARY_STRING );
  assertEquals(
    "<value-data><binary-string>" + Encode.forXml( XMLHandler.encodeBinaryData( byteTestValues ) )
      + "</binary-string>" + Const.CR + "</value-data>",
    valueMetaBaseByteArray.getDataXML( byteTestValues ) );
}
 

@Override
public String getFieldDefinition( ValueMetaInterface v, String tk, String pk, boolean useAutoinc,
                                  boolean addFieldName, boolean addCr ) {
  String retval = "";

  String fieldname = v.getName();
  int length = v.getLength();
  // Unused in vertica
  // int precision = v.getPrecision();

  if ( addFieldName ) {
    retval += fieldname + " ";
  }

  int type = v.getType();
  switch ( type ) {
    case ValueMetaInterface.TYPE_DATE:
    case ValueMetaInterface.TYPE_TIMESTAMP:
      retval += "TIMESTAMP";
      break;
    case ValueMetaInterface.TYPE_BOOLEAN:
      retval += "BOOLEAN";
      break;
    case ValueMetaInterface.TYPE_NUMBER:
    case ValueMetaInterface.TYPE_BIGNUMBER:
      retval += "FLOAT";
      break;
    case ValueMetaInterface.TYPE_INTEGER:
      retval += "INTEGER";
      break;
    case ValueMetaInterface.TYPE_STRING:
      retval += ( length < 1 ) ? "VARCHAR" : "VARCHAR(" + length + ")";
      break;
    case ValueMetaInterface.TYPE_BINARY:
      retval += ( length < 1 ) ? "VARBINARY" : "VARBINARY(" + length + ")";
      break;
    default:
      retval += " UNKNOWN";
      break;
  }

  if ( addCr ) {
    retval += Const.CR;
  }

  return retval;
}
 

@Test
public void test() throws Exception {
  check( "SALESFORCE_URL", () ->  meta.getTargetURL() );
  check( "SALESFORCE_USERNAME", () ->  meta.getUsername() );
  check( "SALESFORCE_PASSWORD", () ->  meta.getPassword() );
  check( "TIME_OUT", () ->  meta.getTimeout() );
  check( "USE_COMPRESSION", () ->  meta.isCompression() );
  check( "MODULE", () ->  meta.getModule() );
  check( "INCLUDE_SQL_IN_OUTPUT", () ->  meta.includeSQL() );
  check( "SQL_FIELDNAME", () ->  meta.getSQLField() );
  check( "INCLUDE_TIMESTAMP_IN_OUTPUT", () ->  meta.includeTimestamp() );
  check( "TIMESTAMP_FIELDNAME", () ->  meta.getTimestampField() );
  check( "INCLUDE_URL_IN_OUTPUT", () ->  meta.includeTargetURL() );
  check( "URL_FIELDNAME", () ->  meta.getTargetURLField() );
  check( "INCLUDE_MODULE_IN_OUTPUT", () ->  meta.includeModule() );
  check( "MODULE_FIELDNAME", () ->  meta.getModuleField() );
  check( "INCLUDE_DELETION_DATE_IN_OUTPUT", () ->  meta.includeDeletionDate() );
  check( "DELETION_DATE_FIELDNAME", () ->  meta.getDeletionDateField() );
  check( "INCLUDE_ROWNUM_IN_OUTPUT", () ->  meta.includeRowNumber() );
  check( "ROWNUM_FIELDNAME", () ->  meta.getRowNumberField() );
  check( "QUERY_CONDITION", () ->  meta.getCondition() );
  check( "LIMIT", () ->  meta.getRowLimit() );
  check( "USE_SPECIFIED_QUERY", () ->  meta.isSpecifyQuery() );
  check( "SPECIFY_QUERY", () ->  meta.getQuery() );
  check( "END_DATE", () ->  meta.getReadTo() );
  check( "START_DATE", () ->  meta.getReadFrom() );
  check( "QUERY_ALL", () ->  meta.isQueryAll() );
  checkStringToInt( "RETRIEVE", () ->  meta.getRecordsFilter(),
    SalesforceConnectionUtils.recordsFilterCode,
    new int[]{ RECORDS_FILTER_ALL, RECORDS_FILTER_UPDATED, RECORDS_FILTER_DELETED } );
  check( "NAME", () ->  meta.getInputFields()[0].getName() );
  check( "FIELD", () ->  meta.getInputFields()[0].getField() );
  check( "LENGTH", () ->  meta.getInputFields()[0].getLength() );
  check( "FORMAT", () ->  meta.getInputFields()[0].getFormat() );
  check( "PRECISION", () ->  meta.getInputFields()[0].getPrecision() );
  check( "CURRENCY", () ->  meta.getInputFields()[0].getCurrencySymbol() );
  check( "DECIMAL", () ->  meta.getInputFields()[0].getDecimalSymbol() );
  check( "GROUP", () ->  meta.getInputFields()[0].getGroupSymbol() );
  check( "REPEAT", () ->  meta.getInputFields()[0].isRepeated() );
  check( "ISIDLOOKUP", () ->  meta.getInputFields()[0].isIdLookup() );
  checkStringToInt( "TRIM_TYPE", () ->  meta.getInputFields()[0].getTrimType(),
    SalesforceInputField.trimTypeCode,
    new int[]{ TYPE_TRIM_NONE, TYPE_TRIM_LEFT, TYPE_TRIM_RIGHT, TYPE_TRIM_BOTH });
  int[] types = new int[]{
    ValueMetaInterface.TYPE_NONE,
    ValueMetaInterface.TYPE_NUMBER,
    ValueMetaInterface.TYPE_STRING,
    ValueMetaInterface.TYPE_DATE,
    ValueMetaInterface.TYPE_BOOLEAN,
    ValueMetaInterface.TYPE_INTEGER,
    ValueMetaInterface.TYPE_BIGNUMBER,
    ValueMetaInterface.TYPE_SERIALIZABLE,
    ValueMetaInterface.TYPE_BINARY,
    ValueMetaInterface.TYPE_TIMESTAMP,
    ValueMetaInterface.TYPE_INET
  };
  ValueMetaString valueMeta = new ValueMetaString();
  checkStringToInt("TYPE", () ->  meta.getInputFields()[0].getType(),
    valueMeta.typeCodes, types );
}