AccessOutputMeta.java

/*! ******************************************************************************
 *
 * Pentaho Data Integration
 *
 * Copyright (C) 2002-2016 by Pentaho : http://www.pentaho.com
 *
 *******************************************************************************
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with
 * the License. You may obtain a copy of the License at
 *
 *    http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 *
 ******************************************************************************/

package org.pentaho.di.trans.steps.accessoutput;

import java.io.File;
import java.io.IOException;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.vfs2.FileObject;
import org.pentaho.di.core.CheckResult;
import org.pentaho.di.core.CheckResultInterface;
import org.pentaho.di.core.Const;
import org.pentaho.di.core.util.Utils;
import org.pentaho.di.core.database.DatabaseMeta;
import org.pentaho.di.core.exception.KettleException;
import org.pentaho.di.core.exception.KettlePluginException;
import org.pentaho.di.core.exception.KettleStepException;
import org.pentaho.di.core.exception.KettleValueException;
import org.pentaho.di.core.exception.KettleXMLException;
import org.pentaho.di.core.row.RowMeta;
import org.pentaho.di.core.row.RowMetaInterface;
import org.pentaho.di.core.row.ValueMetaInterface;
import org.pentaho.di.core.row.value.ValueMetaFactory;
import org.pentaho.di.core.variables.VariableSpace;
import org.pentaho.di.core.vfs.KettleVFS;
import org.pentaho.di.core.xml.XMLHandler;
import org.pentaho.di.i18n.BaseMessages;
import org.pentaho.di.repository.ObjectId;
import org.pentaho.di.repository.Repository;
import org.pentaho.di.resource.ResourceDefinition;
import org.pentaho.di.resource.ResourceNamingInterface;
import org.pentaho.di.trans.Trans;
import org.pentaho.di.trans.TransMeta;
import org.pentaho.di.trans.step.BaseStepMeta;
import org.pentaho.di.trans.step.StepDataInterface;
import org.pentaho.di.trans.step.StepInterface;
import org.pentaho.di.trans.step.StepMeta;
import org.pentaho.di.trans.step.StepMetaInterface;
import org.pentaho.metastore.api.IMetaStore;
import org.w3c.dom.Node;

import com.healthmarketscience.jackcess.Column;
import com.healthmarketscience.jackcess.DataType;
import com.healthmarketscience.jackcess.Database;
import com.healthmarketscience.jackcess.Table;

/*
 * Created on 2-jun-2003
 *
 */
public class AccessOutputMeta extends BaseStepMeta implements StepMetaInterface {
  private static Class<?> PKG = AccessOutputMeta.class; // for i18n purposes, needed by Translator2!!

  private String filename;
  private boolean fileCreated;
  private String tablename;
  private boolean tableCreated;
  private boolean tableTruncated;
  private int commitSize;
  /** Flag: add the filenames to result filenames */
  private boolean addToResultFilenames;
  /** Flag : Do not open new file when transformation start */
  private boolean doNotOpeNnewFileInit;

  public AccessOutputMeta() {
    super(); // allocate BaseStepMeta
  }

  public void loadXML( Node stepnode, List<DatabaseMeta> databases, IMetaStore metaStore ) throws KettleXMLException {
    readData( stepnode, databases );
  }

  public Object clone() {
    AccessOutputMeta retval = (AccessOutputMeta) super.clone();
    return retval;
  }

  /**
   * @return Returns the tablename.
   */
  public String getTablename() {
    return tablename;
  }

  /**
   * @param tablename
   *          The tablename to set.
   */
  public void setTablename( String tablename ) {
    this.tablename = tablename;
  }

  /**
   * @return Returns the truncate table flag.
   */
  public boolean truncateTable() {
    return tableTruncated;
  }

  /**
   * @param truncateTable
   *          The truncate table flag to set.
   */
  public void setTableTruncated( boolean truncateTable ) {
    this.tableTruncated = truncateTable;
  }

  private void readData( Node stepnode, List<DatabaseMeta> databases ) throws KettleXMLException {
    try {
      filename = XMLHandler.getTagValue( stepnode, "filename" );
      tablename = XMLHandler.getTagValue( stepnode, "table" );
      tableTruncated = "Y".equalsIgnoreCase( XMLHandler.getTagValue( stepnode, "truncate" ) );
      fileCreated = "Y".equalsIgnoreCase( XMLHandler.getTagValue( stepnode, "create_file" ) );
      tableCreated = "Y".equalsIgnoreCase( XMLHandler.getTagValue( stepnode, "create_table" ) );
      commitSize = Const.toInt( XMLHandler.getTagValue( stepnode, "commit_size" ), AccessOutput.COMMIT_SIZE );
      String addToResultFiles = XMLHandler.getTagValue( stepnode, "add_to_result_filenames" );
      if ( Utils.isEmpty( addToResultFiles ) ) {
        addToResultFilenames = true;
      } else {
        addToResultFilenames = "Y".equalsIgnoreCase( addToResultFiles );
      }

      doNotOpeNnewFileInit = "Y".equalsIgnoreCase( XMLHandler.getTagValue( stepnode, "do_not_open_newfile_init" ) );

    } catch ( Exception e ) {
      throw new KettleXMLException( "Unable to load step info from XML", e );
    }
  }

  public void setDefault() {
    fileCreated = true;
    tableCreated = true;
    tableTruncated = false;
    commitSize = AccessOutput.COMMIT_SIZE;
    doNotOpeNnewFileInit = false;
    addToResultFilenames = true;
  }

  public String getXML() {
    StringBuilder retval = new StringBuilder( 300 );

    retval.append( "    " ).append( XMLHandler.addTagValue( "filename", filename ) );
    retval.append( "    " ).append( XMLHandler.addTagValue( "table", tablename ) );
    retval.append( "    " ).append( XMLHandler.addTagValue( "truncate", tableTruncated ) );
    retval.append( "    " ).append( XMLHandler.addTagValue( "create_file", fileCreated ) );
    retval.append( "    " ).append( XMLHandler.addTagValue( "create_table", tableCreated ) );
    retval.append( "    " ).append( XMLHandler.addTagValue( "commit_size", commitSize ) );
    retval.append( "    " ).append( XMLHandler.addTagValue( "add_to_result_filenames", addToResultFilenames ) );
    retval.append( "    " ).append( XMLHandler.addTagValue( "do_not_open_newfile_init", doNotOpeNnewFileInit ) );

    return retval.toString();
  }

  public void readRep( Repository rep, IMetaStore metaStore, ObjectId id_step, List<DatabaseMeta> databases ) throws KettleException {
    try {
      filename = rep.getStepAttributeString( id_step, "filename" );
      tablename = rep.getStepAttributeString( id_step, "table" );
      tableTruncated = rep.getStepAttributeBoolean( id_step, "truncate" );
      fileCreated = rep.getStepAttributeBoolean( id_step, "create_file" );
      tableCreated = rep.getStepAttributeBoolean( id_step, "create_table" );
      commitSize = (int) rep.getStepAttributeInteger( id_step, "commit_size" );
      String addToResultFiles = rep.getStepAttributeString( id_step, "add_to_result_filenames" );
      if ( Utils.isEmpty( addToResultFiles ) ) {
        addToResultFilenames = true;
      } else {
        addToResultFilenames = rep.getStepAttributeBoolean( id_step, "add_to_result_filenames" );
      }
      doNotOpeNnewFileInit = rep.getStepAttributeBoolean( id_step, "do_not_open_newfile_init" );

    } catch ( Exception e ) {
      throw new KettleException( "Unexpected error reading step information from the repository", e );
    }
  }

  public void saveRep( Repository rep, IMetaStore metaStore, ObjectId id_transformation, ObjectId id_step ) throws KettleException {
    try {
      rep.saveStepAttribute( id_transformation, id_step, "filename", filename );
      rep.saveStepAttribute( id_transformation, id_step, "table", tablename );
      rep.saveStepAttribute( id_transformation, id_step, "truncate", tableTruncated );
      rep.saveStepAttribute( id_transformation, id_step, "create_file", fileCreated );
      rep.saveStepAttribute( id_transformation, id_step, "create_table", tableCreated );
      rep.saveStepAttribute( id_transformation, id_step, "commit_size", commitSize );
      rep.saveStepAttribute( id_transformation, id_step, "add_to_result_filenames", addToResultFilenames );
      rep.saveStepAttribute( id_transformation, id_step, "do_not_open_newfile_init", doNotOpeNnewFileInit );

    } catch ( Exception e ) {
      throw new KettleException( "Unable to save step information to the repository for id_step=" + id_step, e );
    }
  }

  public void check( List<CheckResultInterface> remarks, TransMeta transMeta, StepMeta stepMeta,
    RowMetaInterface prev, String[] input, String[] output, RowMetaInterface info, VariableSpace space,
    Repository repository, IMetaStore metaStore ) {

    // TODO: add file checking in case we don't create a table.

    // See if we have input streams leading to this step!
    if ( input.length > 0 ) {
      CheckResult cr =
        new CheckResult( CheckResult.TYPE_RESULT_OK, BaseMessages.getString(
          PKG, "AccessOutputMeta.CheckResult.ExpectedInputOk" ), stepMeta );
      remarks.add( cr );
    } else {
      CheckResult cr =
        new CheckResult( CheckResult.TYPE_RESULT_ERROR, BaseMessages.getString(
          PKG, "AccessOutputMeta.CheckResult.ExpectedInputError" ), stepMeta );
      remarks.add( cr );
    }
  }

  public StepInterface getStep( StepMeta stepMeta, StepDataInterface stepDataInterface, int cnr,
    TransMeta transMeta, Trans trans ) {
    return new AccessOutput( stepMeta, stepDataInterface, cnr, transMeta, trans );
  }

  public StepDataInterface getStepData() {
    return new AccessOutputData();
  }

  public RowMetaInterface getRequiredFields( VariableSpace space ) throws KettleException {
    String realFilename = space.environmentSubstitute( filename );
    File file = new File( realFilename );
    Database db = null;
    try {
      if ( !file.exists() || !file.isFile() ) {
        throw new KettleException( BaseMessages.getString(
          PKG, "AccessOutputMeta.Exception.FileDoesNotExist", realFilename ) );
      }

      // open the database and get the table
      db = Database.open( file );
      String realTablename = space.environmentSubstitute( tablename );
      Table table = db.getTable( realTablename );
      if ( table == null ) {
        throw new KettleException( BaseMessages.getString(
          PKG, "AccessOutputMeta.Exception.TableDoesNotExist", realTablename ) );
      }

      RowMetaInterface layout = getLayout( table );
      return layout;
    } catch ( Exception e ) {
      throw new KettleException( BaseMessages.getString( PKG, "AccessOutputMeta.Exception.ErrorGettingFields" ), e );
    } finally {
      try {
        if ( db != null ) {
          db.close();
        }
      } catch ( IOException e ) {
        throw new KettleException(
          BaseMessages.getString( PKG, "AccessOutputMeta.Exception.ErrorClosingDatabase" ), e );
      }
    }
  }

  public static final RowMetaInterface getLayout( Table table ) throws SQLException, KettleStepException {
    RowMetaInterface row = new RowMeta();
    List<Column> columns = table.getColumns();
    for ( int i = 0; i < columns.size(); i++ ) {
      Column column = columns.get( i );

      int valtype = ValueMetaInterface.TYPE_STRING;
      int length = -1;
      int precision = -1;

      int type = column.getType().getSQLType();
      switch ( type ) {
        case java.sql.Types.CHAR:
        case java.sql.Types.VARCHAR:
        case java.sql.Types.LONGVARCHAR: // Character Large Object
          valtype = ValueMetaInterface.TYPE_STRING;
          length = column.getLength();
          break;

        case java.sql.Types.CLOB:
          valtype = ValueMetaInterface.TYPE_STRING;
          length = DatabaseMeta.CLOB_LENGTH;
          break;

        case java.sql.Types.BIGINT:
          valtype = ValueMetaInterface.TYPE_INTEGER;
          precision = 0; // Max 9.223.372.036.854.775.807
          length = 15;
          break;

        case java.sql.Types.INTEGER:
          valtype = ValueMetaInterface.TYPE_INTEGER;
          precision = 0; // Max 2.147.483.647
          length = 9;
          break;

        case java.sql.Types.SMALLINT:
          valtype = ValueMetaInterface.TYPE_INTEGER;
          precision = 0; // Max 32.767
          length = 4;
          break;

        case java.sql.Types.TINYINT:
          valtype = ValueMetaInterface.TYPE_INTEGER;
          precision = 0; // Max 127
          length = 2;
          break;

        case java.sql.Types.DECIMAL:
        case java.sql.Types.DOUBLE:
        case java.sql.Types.FLOAT:
        case java.sql.Types.REAL:
        case java.sql.Types.NUMERIC:
          valtype = ValueMetaInterface.TYPE_NUMBER;
          length = column.getLength();
          precision = column.getPrecision();
          if ( length >= 126 ) {
            length = -1;
          }
          if ( precision >= 126 ) {
            precision = -1;
          }

          if ( type == java.sql.Types.DOUBLE || type == java.sql.Types.FLOAT || type == java.sql.Types.REAL ) {
            if ( precision == 0 ) {
              precision = -1; // precision is obviously incorrect if the type if Double/Float/Real
            }
          } else {
            if ( precision == 0 && length < 18 && length > 0 ) { // Among others Oracle is affected here.
              valtype = ValueMetaInterface.TYPE_INTEGER;
            }
          }
          if ( length > 18 || precision > 18 ) {
            valtype = ValueMetaInterface.TYPE_BIGNUMBER;
          }

          break;

        case java.sql.Types.DATE:
        case java.sql.Types.TIME:
        case java.sql.Types.TIMESTAMP:
          valtype = ValueMetaInterface.TYPE_DATE;
          break;

        case java.sql.Types.BOOLEAN:
        case java.sql.Types.BIT:
          valtype = ValueMetaInterface.TYPE_BOOLEAN;
          break;

        case java.sql.Types.BINARY:
        case java.sql.Types.BLOB:
        case java.sql.Types.VARBINARY:
        case java.sql.Types.LONGVARBINARY:
          valtype = ValueMetaInterface.TYPE_BINARY;
          break;

        default:
          valtype = ValueMetaInterface.TYPE_STRING;
          length = column.getLength();
          break;
      }

      ValueMetaInterface v;
      try {
        v = ValueMetaFactory.createValueMeta( column.getName(), valtype );
      } catch ( KettlePluginException e ) {
        throw new KettleStepException( e );
      }
      v.setLength( length, precision );
      row.addValueMeta( v );
    }

    return row;
  }

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

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

  /**
   * @return the fileCreated
   */
  public boolean isFileCreated() {
    return fileCreated;
  }

  /**
   * @param fileCreated
   *          the fileCreated to set
   */
  public void setFileCreated( boolean fileCreated ) {
    this.fileCreated = fileCreated;
  }

  /**
   * @return the filename
   */
  public String getFilename() {
    return filename;
  }

  /**
   * @param filename
   *          the filename to set
   */
  public void setFilename( String filename ) {
    this.filename = filename;
  }

  /**
   * @return the tableCreated
   */
  public boolean isTableCreated() {
    return tableCreated;
  }

  /**
   * @param tableCreated
   *          the tableCreated to set
   */
  public void setTableCreated( boolean tableCreated ) {
    this.tableCreated = tableCreated;
  }

  /**
   * @return the tableTruncated
   */
  public boolean isTableTruncated() {
    return tableTruncated;
  }

  /**
   * @return the commitSize
   */
  public int getCommitSize() {
    return commitSize;
  }

  /**
   * @param commitSize
   *          the commitSize to set
   */
  public void setCommitSize( int commitSize ) {
    this.commitSize = commitSize;
  }

  /**
   * @return Returns the add to result filesname.
   */
  public boolean isAddToResultFiles() {
    return addToResultFilenames;
  }

  /**
   * @param addtoresultfilenamesin
   *          The addtoresultfilenames to set.
   */
  public void setAddToResultFiles( boolean addtoresultfilenamesin ) {
    this.addToResultFilenames = addtoresultfilenamesin;
  }

  /**
   * @return Returns the "do not open new file init" flag
   */
  public boolean isDoNotOpenNewFileInit() {
    return doNotOpeNnewFileInit;
  }

  /**
   * @param doNotOpenNewFileInit
   *          The "do not open new file init" flag to set.
   */
  public void setDoNotOpenNewFileInit( boolean doNotOpenNewFileInit ) {
    this.doNotOpeNnewFileInit = doNotOpenNewFileInit;
  }

  public String[] getUsedLibraries() {
    return new String[] {
      "jackcess-1.1.13.jar", "commons-collections-3.1.jar", "commons-logging.jar", "commons-lang-2.2.jar",
      "commons-dbcp-1.2.1.jar", "commons-pool-1.3.jar", };
  }

  /**
   * @param space
   *          the variable space to use
   * @param definitions
   * @param resourceNamingInterface
   * @param repository
   *          The repository to optionally load other resources from (to be converted to XML)
   * @param metaStore
   *          the metaStore in which non-kettle metadata could reside.
   *
   * @return the filename of the exported resource
   */
  public String exportResources( VariableSpace space, Map<String, ResourceDefinition> definitions,
    ResourceNamingInterface resourceNamingInterface, Repository repository, IMetaStore metaStore ) throws KettleException {
    try {
      // The object that we're modifying here is a copy of the original!
      // So let's change the filename from relative to absolute by grabbing the file object...
      //
      if ( !Utils.isEmpty( filename ) ) {
        FileObject fileObject = KettleVFS.getFileObject( space.environmentSubstitute( filename ), space );
        filename = resourceNamingInterface.nameResource( fileObject, space, true );
      }

      return null;
    } catch ( Exception e ) {
      throw new KettleException( e );
    }
  }

}