Java Code Examples for org.pentaho.di.core.database.DatabaseMeta#getQuotedSchemaTableCombination()

void processTruncate() throws Exception {
  Connection connection = data.db.getConnection();

  String loadAction = environmentSubstitute( meta.getLoadAction() );

  if ( loadAction.equalsIgnoreCase( "truncate" ) ) {
    DatabaseMeta dm = meta.getDatabaseMeta();
    String tableName =
      dm.getQuotedSchemaTableCombination( environmentSubstitute( meta.getSchemaName() ),
        environmentSubstitute( meta.getTableName() ) );
    logBasic( "Launching command: " + "TRUNCATE " + tableName );

    Statement statement = connection.createStatement();

    try {
      statement.executeUpdate( "TRUNCATE " + tableName );
    } catch ( Exception ex ) {
      throw new KettleException( "Error while truncating " + tableName, ex );
    } finally {
      statement.close();
    }
  }
}
 

public void showLayout() {

    DatabaseMeta databaseMeta = model.getDatabaseMeta();
    String schemaTable = databaseMeta.getQuotedSchemaTableCombination( model.getSchema(), model.getTable() );

    String theSql = databaseMeta.getSQLQueryFields( schemaTable );
    GetQueryFieldsProgressDialog theProgressDialog =
      new GetQueryFieldsProgressDialog( this.shell, databaseMeta, theSql );
    RowMetaInterface fields = theProgressDialog.open();

    StepFieldsDialog stepFieldsDialog =
        new StepFieldsDialog( this.dbExplorerDialog.getShell(), databaseMeta, SWT.NONE, schemaTable, fields );
    stepFieldsDialog.setShellText( BaseMessages.getString( PKG, "DatabaseExplorerDialog.TableLayout.ShellText" ) );
    stepFieldsDialog
      .setOriginText( BaseMessages.getString( PKG, "DatabaseExplorerDialog.TableLayout.OriginText" ) );
    stepFieldsDialog.setShowEditButton( false );
    stepFieldsDialog.open();
  }
 

/**
 * Creates a new dialog that will handle the wait while we're doing the hard work.
 */
public GetPreviewTableProgressDialog( Shell shell, DatabaseMeta dbInfo, String schemaName, String tableName,
  int limit ) {
  this.shell = shell;
  this.dbMeta = dbInfo;
  this.tableName = dbInfo.getQuotedSchemaTableCombination( schemaName, tableName );
  this.limit = limit;
}
 

public XulStepFieldsDialog( Shell aShell, int aStyle, DatabaseMeta aDatabaseMeta, String aTableName,
  RowMetaInterface anInput, String schemaName ) {
  this.shell = aShell;
  this.schemaTableCombo = aDatabaseMeta.getQuotedSchemaTableCombination( schemaName, aTableName );
  this.databaseMeta = aDatabaseMeta;
  this.rowMeta = anInput;
}
 

private String getSchemaAndTable( XulDatabaseExplorerModel model, DatabaseMeta meta ) {
  if ( model.getSchema() != null ) {
    return meta.getQuotedSchemaTableCombination( model.getSchema(), model.getTable() );
  } else {
    return meta.getQuotedSchemaTableCombination( null, model.getTable() );
  }
}
 

private void loadAllTableDataIntoTheCache() throws KettleException {
  DatabaseMeta dbMeta = meta.getDatabaseMeta();

  Database db = getDatabase( dbMeta );
  connectDatabase( db );

  try {
    // We only want to get the used table fields...
    //
    String sql = "SELECT ";

    for ( int i = 0; i < meta.getStreamKeyField1().length; i++ ) {
      if ( i > 0 ) {
        sql += ", ";
      }
      sql += dbMeta.quoteField( meta.getTableKeyField()[ i ] );
    }

    // Also grab the return field...
    //
    for ( int i = 0; i < meta.getReturnValueField().length; i++ ) {
      sql += ", " + dbMeta.quoteField( meta.getReturnValueField()[ i ] );
    }
    // The schema/table
    //
    sql += " FROM "
      + dbMeta.getQuotedSchemaTableCombination(
        environmentSubstitute( meta.getSchemaName() ),
        environmentSubstitute( meta.getTablename() ) );

    // order by?
    if ( meta.getOrderByClause() != null && meta.getOrderByClause().length() != 0 ) {
      sql += " ORDER BY " + meta.getOrderByClause();
    }

    // Now that we have the SQL constructed, let's store the rows...
    //
    List<Object[]> rows = db.getRows( sql, 0 );
    if ( rows != null && rows.size() > 0 ) {
      if ( data.allEquals ) {
        putToDefaultCache( db, rows );
      } else {
        putToReadOnlyCache( db, rows );
      }
    }
  } catch ( Exception e ) {
    throw new KettleException( e );
  } finally {
    if ( db != null ) {
      db.disconnect();
    }
  }
}
 

/**
 * Get the contents of the control file as specified in the meta object
 *
 * @return a string containing the control file contents
 */
public String getCopyCommand( ) throws KettleException {
  DatabaseMeta dm = meta.getDatabaseMeta();

  StringBuilder contents = new StringBuilder( 500 );

  String tableName =
    dm.getQuotedSchemaTableCombination(
      environmentSubstitute( meta.getSchemaName() ), environmentSubstitute( meta.getTableName() ) );

  // Set the date style...
  //
  // contents.append("SET DATESTYLE ISO;"); // This is the default but we set it anyway...
  // contents.append(Const.CR);

  // Create a Postgres / Greenplum COPY string for use with a psql client
  contents.append( "COPY " );
  // Table name

  contents.append( tableName );

  // Names of columns

  contents.append( " ( " );

  String[] streamFields = meta.getFieldStream();
  String[] tableFields = meta.getFieldTable();

  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( ", " );
    }
    contents.append( dm.quoteField( tableFields[i] ) );
  }

  contents.append( " ) " );

  // The "FROM" filename
  contents.append( " FROM STDIN" ); // FIFO file

  // The "FORMAT" clause
  contents.append( " WITH CSV DELIMITER AS '" ).append( environmentSubstitute( meta.getDelimiter() ) )
      .append( "' QUOTE AS '" ).append(
    environmentSubstitute( meta.getEnclosure() ) ).append( "'" );
  contents.append( ";" ).append( Const.CR );

  return contents.toString();
}
 

/**
 * Create the command line for a sql process depending on the meta information supplied.
 *
 * @param meta
 *          The meta data to create the command line from
 *
 * @return The string to execute.
 *
 * @throws KettleException
 *           Upon any exception
 */
public String createCommandLine( IngresVectorwiseLoaderMeta meta ) throws KettleException {
  StringBuilder sb = new StringBuilder( 300 );

  if ( !Utils.isEmpty( meta.getSqlPath() ) ) {
    try {
      FileObject fileObject = KettleVFS.getFileObject( environmentSubstitute( meta.getSqlPath() ), getTransMeta() );
      String sqlexec = Const.optionallyQuoteStringByOS( KettleVFS.getFilename( fileObject ) );
      sb.append( sqlexec );
      // sql @tc-dwh-test.timocom.net,tcp_ip,VW[ingres,pwd]::dwh
    } catch ( KettleFileException ex ) {
      throw new KettleException( "Error retrieving command string", ex );
    }
  } else {
    if ( meta.isUsingVwload() ) {
      if ( isDetailed() ) {
        logDetailed( "vwload defaults to system path" );
      }
      sb.append( "vwload" );
    } else {
      if ( isDetailed() ) {
        logDetailed( "sql defaults to system path" );
      }
      sb.append( "sql" );
    }
  }

  DatabaseMeta dm = meta.getDatabaseMeta();
  if ( dm != null ) {
    String databaseName = environmentSubstitute( Const.NVL( dm.getDatabaseName(), "" ) );
    String password =
      Encr.decryptPasswordOptionallyEncrypted( environmentSubstitute( Const.NVL( dm.getDatabaseInterface()
        .getPassword(), "" ) ) );
    String port = environmentSubstitute( Const.NVL( dm.getDatabasePortNumberString(), "" ) ).replace( "7", "" );
    String username = environmentSubstitute( Const.NVL( dm.getDatabaseInterface().getUsername(), "" ) );
    String hostname = environmentSubstitute( Const.NVL( dm.getDatabaseInterface().getHostname(), "" ) );
    String schemaTable = dm.getQuotedSchemaTableCombination( null, environmentSubstitute( meta.getTableName() ) );
    String encoding = environmentSubstitute( Const.NVL( meta.getEncoding(), "" ) );
    String fifoFile =
      Const.optionallyQuoteStringByOS( environmentSubstitute( Const.NVL( meta.getFifoFileName(), "" ) ) );
    String errorFile =
      Const.optionallyQuoteStringByOS( environmentSubstitute( Const.NVL( meta.getErrorFileName(), "" ) ) );
    int maxNrErrors = Const.toInt( environmentSubstitute( Const.NVL( meta.getMaxNrErrors(), "0" ) ), 0 );

    if ( meta.isUsingVwload() ) {
      sb.append( " -u " ).append( username );
      sb.append( " -P " ).append( password );
      sb.append( " -f " ).append( meta.getDelimiter() ).append( "" );
      sb.append( " -t " ).append( schemaTable );

      if ( !Utils.isEmpty( encoding ) ) {
        sb.append( " -C " ).append( encoding );
      }
      if ( !Utils.isEmpty( errorFile ) ) {
        sb.append( " -l " ).append( errorFile );
      }
      if ( maxNrErrors > 0 ) {
        // need multiplication for two because every wrong rows
        // provide 2 errors that is not evident
        sb.append( " -x " ).append( maxNrErrors * 2 );
      }
      sb.append( " " ).append( databaseName );
      sb.append( " " ).append( fifoFile );

    } else if ( meta.isUseDynamicVNode() ) {
      // logical portname in JDBC use a 7

      sb.append( " @" ).append( hostname ).append( "," ).append( port ).append( "[" ).append( username ).append( "," )
        .append( password ).append( "]::" ).append( databaseName );
    } else {
      // Database Name
      //
      sb.append( " " ).append( databaseName );
      if ( meta.isUseAuthentication() ) {
        sb.append( "-P" ).append( password );
      }
    }
  } else {
    throw new KettleException( "No connection specified" );
  }

  return sb.toString();
}
 

/**
 * Get a list of columns, comma separated, allow the user to select from it.
 */
private void getListColumns() {
  if ( !Utils.isEmpty( wTablename.getText() ) ) {
    DatabaseMeta databaseMeta = jobMeta.findDatabase( wConnection.getText() );
    if ( databaseMeta != null ) {
      Database database = new Database( loggingObject, databaseMeta );
      database.shareVariablesWith( jobMeta );
      try {
        database.connect();
        String schemaTable =
          databaseMeta.getQuotedSchemaTableCombination( wSchemaname.getText(), wTablename.getText() );
        RowMetaInterface row = database.getTableFields( schemaTable );
        String[] available = row.getFieldNames();

        String[] source = wListattribut.getText().split( "," );
        for ( int i = 0; i < source.length; i++ ) {
          source[i] = Const.trim( source[i] );
        }
        int[] idxSource = Const.indexsOfStrings( source, available );
        EnterSelectionDialog dialog = new EnterSelectionDialog( shell, available,
          BaseMessages.getString( PKG, "JobMysqlBulkLoad.SelectColumns.Title" ),
          BaseMessages.getString( PKG, "JobMysqlBulkLoad.SelectColumns.Message" ) );
        dialog.setMulti( true );
        dialog.setAvoidQuickSearch();
        dialog.setSelectedNrs( idxSource );
        if ( dialog.open() != null ) {
          String columns = "";
          int[] idx = dialog.getSelectionIndeces();
          for ( int i = 0; i < idx.length; i++ ) {
            if ( i > 0 ) {
              columns += ", ";
            }
            columns += available[idx[i]];
          }
          wListattribut.setText( columns );
        }
      } catch ( KettleDatabaseException e ) {
        new ErrorDialog( shell, BaseMessages.getString( PKG, "System.Dialog.Error.Title" ), BaseMessages
          .getString( PKG, "JobMysqlBulkLoad.ConnectionError2.DialogMessage" ), e );
      } finally {
        database.disconnect();
      }
    }
  }
}
 

public GetTableSizeProgressDialog( Shell shell, DatabaseMeta dbInfo, String tableName, String schemaName ) {
  this.shell = shell;
  this.dbMeta = dbInfo;
  this.tableName = dbInfo.getQuotedSchemaTableCombination( schemaName, tableName );
}
 

public static String getLastExecutionResult( LogChannelInterface log, LoggingObjectInterface parentObject,
  ReportSubjectLocation filename ) throws KettleException {

  LogTableInterface logTable = null;
  if ( filename.isTransformation() ) {
    TransMeta transMeta = TransformationInformation.getInstance().getTransMeta( filename );
    logTable = transMeta.getTransLogTable();
  } else {
    JobMeta jobMeta = JobInformation.getInstance().getJobMeta( filename );
    logTable = jobMeta.getJobLogTable();
  }
  if ( logTable != null && logTable.isDefined() ) {
    DatabaseMeta dbMeta = logTable.getDatabaseMeta();
    Database database = new Database( parentObject, dbMeta );
    try {
      database.connect();
      String sql = "SELECT ";
      sql += dbMeta.quoteField( logTable.getStatusField().getFieldName() ) + ", ";
      sql += dbMeta.quoteField( logTable.getLogDateField().getFieldName() ) + ", ";
      sql += dbMeta.quoteField( logTable.getErrorsField().getFieldName() ) + "";
      sql += " FROM ";
      sql += dbMeta.getQuotedSchemaTableCombination( logTable.getSchemaName(), logTable.getTableName() );
      sql += " ORDER BY " + dbMeta.quoteField( logTable.getLogDateField().getFieldName() ) + " DESC";

      RowMetaAndData oneRow = database.getOneRow( sql );
      String status = oneRow.getString( 0, "?" );
      Date date = oneRow.getDate( 1, null );
      Long nrErrors = oneRow.getInteger( 2 );

      String evaluation;
      if ( status.equalsIgnoreCase( LogStatus.END.getStatus() ) ) {
        evaluation = "Ended";
      } else if ( status.equalsIgnoreCase( LogStatus.START.getStatus() ) ) {
        evaluation = "Started";
      } else if ( status.equalsIgnoreCase( LogStatus.STOP.getStatus() ) ) {
        evaluation = "Stopped";
      } else if ( status.equalsIgnoreCase( LogStatus.RUNNING.getStatus() ) ) {
        evaluation = "Running";
      } else if ( status.equalsIgnoreCase( LogStatus.PAUSED.getStatus() ) ) {
        evaluation = "Paused";
      } else if ( status.equalsIgnoreCase( LogStatus.ERROR.getStatus() ) ) {
        evaluation = "Failed";
      } else {
        evaluation = "Unknown";
      }
      if ( nrErrors > 0 ) {
        evaluation += " with errors";
      } else {
        evaluation += " with success";
      }

      return evaluation + " at " + XMLHandler.date2string( date );

    } catch ( Exception e ) {
      log.logBasic( "Unable to get logging information from log table" + logTable );
    } finally {
      database.disconnect();
    }
  }
  return null;
}
 

private StepMeta generateTableInputStepFromLogicalTable(LogicalTable logicalTable) {

    String name = ConceptUtil.getName(logicalTable, locale);
    String description = ConceptUtil.getDescription(logicalTable, locale);

    TableInputMeta meta = new TableInputMeta();

    // Source database, retain first
    // Source table, retain first
    // Source columns, retain all
    //
    DatabaseMeta sourceDatabaseMeta = null;
    String sourceTable = null;
    List<String> sourceColumns = new ArrayList<String>();
    for (LogicalColumn column : logicalTable.getLogicalColumns()) {
      String phDb = ConceptUtil.getString(column, DefaultIDs.LOGICAL_COLUMN_SOURCE_DB);
      String phTable = ConceptUtil.getString(column, DefaultIDs.LOGICAL_COLUMN_SOURCE_TABLE);
      String phCol = ConceptUtil.getString(column, DefaultIDs.LOGICAL_COLUMN_SOURCE_COLUMN);
      if (!Utils.isEmpty(phDb) && sourceDatabaseMeta==null) {
        sourceDatabaseMeta = DatabaseMeta.findDatabase(databases, phDb);
      }
      if (!Utils.isEmpty(phTable)) {
        sourceTable = phDb;
      }
      if (!Utils.isEmpty(phCol)) {
        sourceColumns.add(phCol);
      }
    }
    String sql = "SELECT * FROM --< Source query for dimension '"+name+"'";

    meta.setDatabaseMeta(sourceDatabaseMeta);

    if (sourceDatabaseMeta!=null && !Utils.isEmpty(sourceTable)) {
      sql = "SELECT ";
      if (sourceColumns.isEmpty()) {
        sql+=" * ";
      } else {
        sql+=Const.CR;
      }
      boolean first=true;
      for (String sourceColumn : sourceColumns) {
        if (first) {
          first=false;
        } else {
          sql+="      , ";
        }
        sql+=sourceDatabaseMeta.quoteField(sourceColumn)+Const.CR;
      }
      sql+="FROM "+sourceDatabaseMeta.getQuotedSchemaTableCombination(null, sourceTable);
    }
    meta.setSQL(sql);

    // Wrap it up...
    //
    StepMeta stepMeta = new StepMeta("Source data for '"+name+"'", meta);
    stepMeta.drawStep();
    stepMeta.setDescription("Reads data for '"+name+"' : "+description);

    return stepMeta;
  }
 

protected StepMeta generateCombinationLookupStepFromLogicalTable(DatabaseMeta databaseMeta, LogicalTable logicalTable) {
  String name = ConceptUtil.getName(logicalTable, locale);
  String description = ConceptUtil.getDescription(logicalTable, locale);
  String phTable = ConceptUtil.getString(logicalTable, DefaultIDs.LOGICAL_TABLE_PHYSICAL_TABLE_NAME);
  String schemaTable = databaseMeta.getQuotedSchemaTableCombination(null, Const.NVL(phTable, name));

  CombinationLookupMeta meta = new CombinationLookupMeta();
  meta.setDatabaseMeta(databaseMeta);
  meta.setSchemaName(null); // TODO
  meta.setTablename(schemaTable);
  meta.setUseAutoinc(databaseMeta.supportsAutoinc());
  meta.setCacheSize(5000);
  meta.setCommitSize(500);
  meta.setReplaceFields(true); // replace attribute fields with a TK

  // Find the technical key (if any defined)
  //
  LogicalColumn keyColumn = ConceptUtil.findLogicalColumn(logicalTable, AttributeType.TECHNICAL_KEY);
  if (keyColumn!=null) {
    ValueMetaInterface keyValue = getValueForLogicalColumn(databaseMeta, keyColumn);
    meta.setTechnicalKeyField(keyValue.getName());
  }

  // Simply add all the attributes as key columns...
  //
  List<LogicalColumn> attributes = ConceptUtil.findLogicalColumns(logicalTable, AttributeType.ATTRIBUTE);
  meta.setKeyLookup(new String[attributes.size()]);
  meta.setKeyField(new String[attributes.size()]);
  for (int i=0;i<attributes.size();i++) {
    LogicalColumn logicalColumn = attributes.get(i);
    ValueMetaInterface valueMeta = getValueForLogicalColumn(databaseMeta, logicalColumn);
    meta.getKeyLookup()[i] = valueMeta.getName();
    meta.getKeyField()[i] = valueMeta.getName();
  }

  StepMeta stepMeta = new StepMeta(name, meta);
  stepMeta.drawStep();
  stepMeta.setDescription(description);

  return stepMeta;
}