// Copyright (c) 2001, 2003, Oracle Corporation.  All rights reserved.  
// File: DataPrepDemo.java

/**
 * This sample program illustartes different types of data preparation
 * methodologies supported by ODM Java API. ODM supports three types of data
 * preparation i.e., automated discretization, external discretization and 
 * user supplied bin boundaries (aka embedded binning).
 * In case of automated discretization, user specifies the attributes which 
 * are not prepared and Data Mining Server(DMS) will perform the data preparation 
 * as part of the model build operation. Bin boundary tables will be persisted
 * as part of the model tables and they are used for all operations like apply, 
 * test and lift done using this model.
 * In case of external data preparation, user does all the data preparation
 * required for the data and DMS will not perform any data preparation and 
 * model does not store any bin boundary details. In this case user is 
 * responsible for making sure the data is prepared.
 * In case of user supplied bin boundary tables, user can create bin boundaries
 * tables and associate the model build operation with those tables. In this 
 * case model build operation uses the boundary tables and prepares the input
 * data and make a copy of the boundary tables and stores them as part of the
 * model. Just like in automated case, these bin boundary tables will be used 
 * for all operations done using this model.
 * 
 * This program creates bin boundary tables for the "MINING_DATA_BUILD_V" 
 * dataset and discretize the dataset. It the builds three NB model using 
 * the external discretization, user supplied bin boundaries and 
 * automated discretization techniques.
 * 
 * NOTE:
 *   Before executing this program you need to provide DB URL, username 
 *   and password in login method and a valid data schema name
 *   by changing the DATA_SCHEMA_NAME constant value in the program.
 */

// Generic Java Imports
import java.io.*;
import java.util.*;
import java.sql.SQLException;
import java.sql.Statement;

// ODM Imports
import oracle.dmt.odm.*;
import oracle.dmt.odm.data.*;
import oracle.dmt.odm.settings.function.*;
import oracle.dmt.odm.settings.algorithm.*;
import oracle.dmt.odm.task.*;
import oracle.dmt.odm.model.*;
import oracle.dmt.odm.result.*;
import oracle.dmt.odm.rule.*;
import oracle.dmt.odm.transformation.*;

public class DataPrepDemo extends Object {

  private static DataMiningServer m_dms = null;
  private static oracle.dmt.odm.Connection m_dmsConn = null;
  //provide schema name that has the data tables/views
  private static final String DATA_SCHEMA_NAME = "schema name";  
  
  public static void main( String args[] ) { 
    try {      
      //1. Login to the Data Mining Server
          //Create a DMS object
          m_dms = new DataMiningServer(
            "put DB URL here", //JDBC URL jdbc:oracle:thin:@<Host name>:<Port>:<SID>
            "user name", //User Name
            "password" //Password 
          );
          //Login to the DMS and create a DMS Connection
          m_dmsConn = m_dms.login();
          //Remove the objects to avoid name conflicts
          cleanup();
      //1. Create bin boundaries tables
      createBinBoundaryTables();
      //2. Perform discretization (aka binning)
      performDiscretization();
      //3. Build a model using discretized data
      buildNBModelUsingExternalDataPrep();
      //4.Build model with the user supplied
      // bin boundaries tables
      buildNBModelUsingUserSuppliedBoundaries();
      //5.Build model with automated data prepartion
      buildNBModelUsingAutomatedDataPrep();
    } catch(Exception anyExp) {
      anyExp.printStackTrace(System.out);
    } finally {
      try {
      //6. Logout from the Data Mining Server
      m_dms.logout(m_dmsConn);
      } catch(Exception anyExp1) { }//Ignore
    }
  }


  /**
   * This method illustrates the the creation of bin boundaries table. 
   * Table MINING_DATA_BUILD_V has the following columns:
   * <ul>
   * <li>CUST_ID                                   NOT NULL NUMBER</li>
   * <li>CUST_GENDER                               NOT NULL CHAR(1)</li>
   * <li>AGE                                                NUMBER</li>
   * <li>CUST_MARITAL_STATUS                                VARCHAR2(20)</li>
   * <li>COUNTRY_NAME                                NOT NULL VARCHAR2(40)</li>
   * <li>CUST_INCOME_LEVEL                                  VARCHAR2(30)</li>
   * <li>EDUCATION                                          VARCHAR2(21)</li>
   * <li>OCCUPATION                                         VARCHAR2(21)</li>
   * <li>HOUSEHOLD_SIZE                                     VARCHAR2(21)</li>
   * <li>YRS_RESIDENCE                                      NUMBER</li>
   * <li>AFFINITY_CARD                                      NUMBER(10)</li>
   * <li>BULK_PACK_DISKETTES                                NUMBER(10)</li>
   * <li>FLAT_PANEL_MONITOR                                 NUMBER(10)</li>
   * <li>HOME_THEATER_PACKAGE                               NUMBER(10)</li>
   * <li>BOOKKEEPING_APPLICATION                            NUMBER(10)</li>
   * <li>PRINTER_SUPPLIES                                   NUMBER(10)</li>
   * <li>Y_BOX_GAMES                                        NUMBER(10)</li>
   * <li>OS_DOC_SET_KANJI                                   NUMBER(10)</li> 
   * </ul>
   * In this method we will bin some of the columns, using diffenet
   * techniques supported by the API.
   * 
   */
  public static void createBinBoundaryTables() 
  throws InvalidArgumentException, ODMException, SQLException
  {
        //1. Create collection of DiscretizationSpecification
        Vector binningDetails = new Vector();
    
        // CUST_GENDER - equal width transformation
        CategoricalDiscretization binCategorical = 
          new CategoricalDiscretization(2,"OTHER_GENDER");
        DiscretizationSpecification discretizationSpec = 
          new DiscretizationSpecification("CUST_GENDER", binCategorical);
        binningDetails.add(discretizationSpec);
    
        // AGE - Winsorize transformation
        float ageTailPercentage = 1.0f; //tail percentage value
        NumericalDiscretization binNumeric = 
          new NumericalDiscretization(10, ageTailPercentage);
        discretizationSpec = new DiscretizationSpecification("AGE",binNumeric);
        binningDetails.add(discretizationSpec);
    
        // CUST_MARITAL_STATUS: - equal width transformation, equaivalent to to TopN 
        binCategorical = new CategoricalDiscretization(5,"OTHER_MS");
        discretizationSpec = 
          new DiscretizationSpecification("CUST_MARITAL_STATUS", binCategorical);
        binningDetails.add(discretizationSpec);

        // COUNTRY_NAME: - equal width transformation, equaivalent to to TopN 
        binCategorical = new CategoricalDiscretization(30,"OTHER_COUNTRY");
        discretizationSpec = 
          new DiscretizationSpecification("COUNTRY_NAME", binCategorical);
        binningDetails.add(discretizationSpec);
    
        // CUST_INCOME_LEVEL: - equal width transformation, equaivalent to to TopN 
        binCategorical = new CategoricalDiscretization(12,"OTHER_INCOME");
        discretizationSpec = 
          new DiscretizationSpecification("CUST_INCOME_LEVEL", binCategorical);
        binningDetails.add(discretizationSpec);
    
        // EDUCATION: - equal width transformation, equaivalent to to TopN 
        binCategorical = new CategoricalDiscretization(15,"OTHER_EDUCATION");
        discretizationSpec = 
          new DiscretizationSpecification("EDUCATION", binCategorical);
        binningDetails.add(discretizationSpec);
    
        //OCCUPATION: - use explicit category names, instead of TopN
        CategoryGroup[] groups = new CategoryGroup[]{
          new CategoryGroup(1, "White collar", // group 1
            new Category[]{
              new Category("Cleric."),
              new Category("Exec."),
              new Category("Prof."),
              new Category("Sales"),
              new Category("TechSup"),
            }
          ),
          new CategoryGroup(2, "Blue collar", // group 2
            new Category[]{
              new Category("Farming"),
              new Category("Handler"),
              new Category("Machine"),
              new Category("Transp."),
            }
          ),
          new CategoryGroup(3,"Others", // All other distinct values will belong to this bin
            new Category[]{
              new Category("?"),
              new Category("Armed-F"),
              new Category("Crafts"),
              new Category("House-s"),
              new Category("Protec."),
              new Category("Other"),
            }
          )
        };
        
        binCategorical = new CategoricalDiscretization(groups);
        discretizationSpec = 
          new DiscretizationSpecification("OCCUPATION", binCategorical);
        binningDetails.add(discretizationSpec);
    
        // HOUSEHOLD_SIZE
        binCategorical = new CategoricalDiscretization(6,"OTHER_HS");
        discretizationSpec = 
          new DiscretizationSpecification("HOUSEHOLD_SIZE", binCategorical);
        binningDetails.add(discretizationSpec);
    
        // YRS_RESIDENCE: custom binning
        binNumeric = new NumericalDiscretization(new NumericalBin[]{
          new NumericalBin(1, .0f, 4.f),
          new NumericalBin(2, 4.f, 10.f),
          new NumericalBin(3, 10.f,14.f),
        });
        discretizationSpec = 
          new DiscretizationSpecification("YRS_RESIDENCE", binNumeric);
        binningDetails.add(discretizationSpec);
    
        // 2.Create PhysicalDataSpecification
        LocationAccessData lad = new LocationAccessData( 
                                     "MINING_DATA_BUILD_V", //Test table/view Name
                                     DATA_SCHEMA_NAME ); //Schema Name
        PhysicalDataSpecification pds = 
          new NonTransactionalDataSpecification( lad );
    
        // 3, Create bin boundaries tables
        Transformation.createDiscretizationTables(
          m_dmsConn, lad,  pds,
          (DiscretizationSpecification[])binningDetails.toArray(
            new DiscretizationSpecification[binningDetails.size()]),
          "MINING_DATA_BUILD_V_NUM", // numeric bin boundaries
          "MINING_DATA_BUILD_V_CAT", // categorical bin boundaries
          DATA_SCHEMA_NAME);//Schema Name
  }
  
  /**
   * This method performs discretization of the data in "MINING_DATA_BUILD_V" 
   * using the bin boundaries tables created in "createBinBoundaryTables" method. 
   * It creates a discretized view called "MINING_DATA_BUILD_BINNED".
   */
  public static void performDiscretization() throws ODMException, SQLException
  {
        // input data location
        LocationAccessData inputDataLocation = new LocationAccessData( 
           "MINING_DATA_BUILD_V", //Test table/view Name
           DATA_SCHEMA_NAME ); //Schema Name
        PhysicalDataSpecification pds = 
          new NonTransactionalDataSpecification( inputDataLocation );
                                     
        // resulting discretized view location
        LocationAccessData resultViewLocation = new LocationAccessData( 
           "MINING_DATA_BUILD_BINNED", //discretized view Name 
           DATA_SCHEMA_NAME ); //Schema Name
    
        // perform binning
        Transformation.discretize(
            m_dmsConn,
            inputDataLocation,
            pds,
            "MINING_DATA_BUILD_V_NUM", // numeric bin boundaries
            "MINING_DATA_BUILD_V_CAT", // categorical bin boundaries
            DATA_SCHEMA_NAME,         // schema Name
            resultViewLocation,       // location of the resulting binned view
            true                      // open ended binning
            );
  }
  
  /**
   * This method builds an NB model based on the discretized view 
   * "MINING_DATA_BUILD_BINNED".
   */
  public static void buildNBModelUsingExternalDataPrep() 
  throws ODMException, SQLException, IOException
  {
    //1. Create PhysicalDataSpecification
        LocationAccessData resultViewLocation = 
          new LocationAccessData( 
                 "MINING_DATA_BUILD_BINNED", //resulting discretized table name 
                 DATA_SCHEMA_NAME ); //Schema Name
        PhysicalDataSpecification pds = 
          new NonTransactionalDataSpecification( resultViewLocation );
    //2. Create & Store Mining Function Settings with data marked as prebinned 
    //   and no embedded binning is required
        NaiveBayesSettings nbAlgo = new NaiveBayesSettings(0.01f, 0.01f);
        ClassificationFunctionSettings mfs =
          ClassificationFunctionSettings.create(
                   m_dmsConn,       //DMS Connection
                   nbAlgo,          //NB algorithm settings
                   pds,             //Build data specification
                   "AFFINITY_CARD", //Target column
                   AttributeType.categorical, //Target attribute type
                   DataPreparationStatus.prepared); //All attributes are marked as
                                                  //discretized to disable internal binning
                   
        mfs.store( m_dmsConn, "ExternalPrepDemo_MFS");

    //3. Create, store & execute Build Task
        MiningBuildTask buildTask = new MiningBuildTask(
                     pds, //Build data specification
                     "ExternalPrepDemo_MFS", //Mining function settings name
                     "ExternalPrepDemo_Model" //Mining model name
                     );
        executeTask(buildTask, "ExternalPrepDemo_Task" );
  }
  
  /**
   * This function builds an NB model based on the original data 
   * MINING_DATA_BUILD_V using user supplied bin boundary tables.
   */
  public static void buildNBModelUsingUserSuppliedBoundaries() 
  throws ODMException, SQLException, IOException
  {
    //1. Create PhysicalDataSpecification
        LocationAccessData srcDataLocation = new LocationAccessData( 
                                     "MINING_DATA_BUILD_V", //original data 
                                     DATA_SCHEMA_NAME ); //Schema Name
        PhysicalDataSpecification pds = 
          new NonTransactionalDataSpecification( srcDataLocation );
    //2. Create & Store Mining Function Settings with data marked as prebinned 
    //   and no embedded binning is required
        NaiveBayesSettings nbAlgo = new NaiveBayesSettings(0.01f, 0.01f);
        ClassificationFunctionSettings mfs =
          ClassificationFunctionSettings.create(
                   m_dmsConn,       //DMS Connection
                   nbAlgo,          //NB algorithm settings
                   pds,             //Build data specification
                   "AFFINITY_CARD", //Target column
                   AttributeType.categorical, //Target attribute type
                   DataPreparationStatus.unprepared); //All attributes are 
                                                      //marked as not binned
    // 3. Set the bin boundaries table to the LogicalDataSpecification
    //    of the function settings. These tables will be used for
    //    discretization of the input data and they are copied and stored as
    //    model tables in the model.
        LocationAccessData numBBLocation = new LocationAccessData( 
                             "MINING_DATA_BUILD_V_NUM", //discretized view Name 
                             DATA_SCHEMA_NAME ); //Schema Name
        LocationAccessData catBBLocation = new LocationAccessData( 
                             "MINING_DATA_BUILD_V_CAT", //discretized view Name 
                             DATA_SCHEMA_NAME ); //Schema Name
                                     
        mfs.getLogicalDataSpecification().setUserSuppliedDiscretizationTables(
          numBBLocation, catBBLocation);
        mfs.store( m_dmsConn, "EmbeddedPrepDemo_MFS");

    //3. Create, store & execute Build Task
        MiningBuildTask buildTask = new MiningBuildTask(
                     pds, //Build data specification
                     "EmbeddedPrepDemo_MFS", //Mining function settings name
                     "EmbeddedPrepDemo_Model" //Mining model name
                     );
        executeTask(buildTask, "EmbeddedPrepDemo_Task" );
  }
  
  /**
   * This method builds a NB model based on the MINING_DATA_BUILD_V. 
   * In this method all the attributes are specified as unprepared
   * and DMS will perform the automated data preparation as part of 
   * the build operation.   
   */
  public static void buildNBModelUsingAutomatedDataPrep() 
  throws ODMException, SQLException, IOException
  {
    //1. Create PhysicalDataSpecification
        LocationAccessData srcDataLocation = new LocationAccessData( 
                                     "MINING_DATA_BUILD_V", //original data 
                                     DATA_SCHEMA_NAME ); //Schema Name
        PhysicalDataSpecification pds = 
          new NonTransactionalDataSpecification( srcDataLocation );
    //2. Create & Store Mining Function Settings with data marked as prebinned 
    //   and no embedded binning is required
        NaiveBayesSettings nbAlgo = new NaiveBayesSettings(0.01f, 0.01f);
        ClassificationFunctionSettings mfs =
          ClassificationFunctionSettings.create(
                   m_dmsConn,       //DMS Connection
                   nbAlgo,          //NB algorithm settings
                   pds,             //Build data specification
                   "AFFINITY_CARD", //Target column
                   AttributeType.categorical, //Target attribute type
                   DataPreparationStatus.unprepared); //All attributes are marked as
                                                      // not binned
        mfs.store( m_dmsConn, "AutomatedPrepDemo_MFS");

    //3. Create, store & execute Build Task
        MiningBuildTask buildTask = new MiningBuildTask(
                     pds, //Build data specification
                     "AutomatedPrepDemo_MFS", //Mining function settings name
                     "AutomatedPrepDemo_Model" //Mining model name
                     );
        executeTask(buildTask, "AutomatedPrepDemo_Task" );
  }
  
  
  /**
   * This method stores the given task with the specified name in the DMS 
   * and submits the task for asynchronous execution in the DMS. After 
   * completing the task successfully it returns true. If there is a task 
   * failure, then it prints error description and returns false.
   * 
   * @param taskObj task object
   * @param taskName name of the task
   * 
   * @return boolean returns true when the task is successful
   */
  public static boolean executeTask(MiningTask taskObj, String taskName)
  throws ODMException, SQLException {
        boolean isTaskSuccess = false;
        taskObj.store(m_dmsConn, taskName); //Store the task
        taskObj.execute(m_dmsConn); //Submit the task for asynchronous execution
        System.out.print(taskName + " is started, please wait. ");
        //Wait for completion of the task
        MiningTaskStatus taskStatus = taskObj.waitForCompletion(m_dmsConn);
        //Check the status of the task after completion
        isTaskSuccess = taskStatus.getTaskState().equals(MiningTaskState.success);
        if( isTaskSuccess ) {
          //Task completed successfully
          System.out.println("It is successful.");
        } else {//Task failed
          throw new ODMException("It is failed.\nFailure Description: " + 
            taskStatus.getStateDescription() );
        }
        return isTaskSuccess;
    }

  /**
   * Cleanup the previously created objects if any. 
   */
  public static void cleanup() throws ODMException, SQLException
  {
        // Get the sql connection
        Statement stmt = null;
        java.sql.Connection conn = m_dmsConn.getDBConnection();
        //Remove numeric bin boundary table
        String sqlDrop = "DROP TABLE ";
        try {
          stmt = conn.createStatement();
          stmt.execute(sqlDrop + "MINING_DATA_BUILD_V_NUM");
        }catch(Exception anyExp1) { }//Ignore
        
        try {
          stmt.execute(sqlDrop + "MINING_DATA_BUILD_V_CAT");
        }catch(Exception anyExp1) { }//Ignore
        
        try {
          sqlDrop = "DROP VIEW ";
          stmt.execute(sqlDrop + "MINING_DATA_BUILD_BINNED");
        }catch(Exception anyExp1) { }//Ignore
    
        finally{
          try{
            stmt.close();
          }catch(Exception anyExp1) { }//Ignore
        }
        //Remove the MiningFunctionSettings
        try {
          ClassificationFunctionSettings.remove(m_dmsConn, "ExternalPrepDemo_MFS");
        }catch(Exception anyExp1) { }//Ignore
        //Remove the MiningBuildTask
        try {
          MiningBuildTask.remove(m_dmsConn, "ExternalPrepDemo_Task");
        }catch(Exception anyExp2) { }//Ignore
        //Remove the MiningModel
        try {
          MiningModel.remove(m_dmsConn, "ExternalPrepDemo_Model");
        }catch(Exception anyExp3) { }//Ignore
        //Remove the MiningFunctionSettings
        try {
          ClassificationFunctionSettings.remove(m_dmsConn, "EmbeddedPrepDemo_MFS");
        }catch(Exception anyExp1) { }//Ignore
        //Remove the MiningBuildTask
        try {
          MiningBuildTask.remove(m_dmsConn, "EmbeddedPrepDemo_Task");
        }catch(Exception anyExp2) { }//Ignore
        //Remove the MiningModel
        try {
          MiningModel.remove(m_dmsConn, "EmbeddedPrepDemo_Model");
        }catch(Exception anyExp3) { }//Ignore
        try {
          ClassificationFunctionSettings.remove(m_dmsConn, "AutomatedPrepDemo_MFS");
        }catch(Exception anyExp1) { }//Ignore
        //Remove the MiningBuildTask
        try {
          MiningBuildTask.remove(m_dmsConn, "AutomatedPrepDemo_Task");
        }catch(Exception anyExp2) { }//Ignore
        //Remove the MiningModel
        try {
          MiningModel.remove(m_dmsConn, "AutomatedPrepDemo_Model");
        }catch(Exception anyExp3) { }//Ignore
  }
}


