databasesaver.java

MacroWeka扩展了著名数据挖掘工具weka

  public void connectToDatabase() {
   
      try{
        if(!m_DataBaseConnection.isConnected())
            m_DataBaseConnection.connectToDatabase();
      } catch(Exception ex) {
	printException(ex);
       }    
  }
  
  /** Writes the structure (header information) to a database by creating a new table.*/
  private void writeStructure() throws Exception{
  
      StringBuffer query = new StringBuffer();
      Instances structure = getInstances();
      query.append("CREATE TABLE ");
      if(m_tabName || m_tableName.equals(""))
        m_tableName = structure.relationName();
      if(m_DataBaseConnection.getUpperCase()){
        m_tableName = m_tableName.toUpperCase();
        m_createInt = m_createInt.toUpperCase(); 
        m_createDouble = m_createDouble.toUpperCase(); 
        m_createText = m_createText.toUpperCase(); 
      }
      m_tableName = m_tableName.replaceAll("[^\\w]","_");
      query.append(m_tableName);
      if(structure.numAttributes() == 0)
          throw new Exception("Instances have no attribute.");
      query.append(" ( ");
      if(m_id){
        if(m_DataBaseConnection.getUpperCase())
              m_idColumn = m_idColumn.toUpperCase();
        query.append(m_idColumn);
        query.append(" ");
        query.append(m_createInt);
        query.append(" PRIMARY KEY,");
      }
      for(int i = 0;i < structure.numAttributes(); i++){
          Attribute att = structure.attribute(i);
          String attName = att.name();
          attName = attName.replaceAll("[^\\w]","_");
          if(m_DataBaseConnection.getUpperCase())
              query.append(attName.toUpperCase());
          else
              query.append(attName);
          if(att.isDate())
              query.append(" DATE");
          else{
              if(att.isNumeric())
                  query.append(" "+m_createDouble);
              else
                  query.append(" "+m_createText);
          }
          if(i != structure.numAttributes()-1)
              query.append(", ");
      }
      query.append(" )");
      //System.out.println(query.toString());
      m_DataBaseConnection.execute(query.toString());
      if(!m_DataBaseConnection.tableExists(m_tableName)){
          throw new IOException("Table cannot be built.");
      }
  }
  
  private void writeInstance(Instance inst) throws Exception{
  
      StringBuffer insert = new StringBuffer();
      insert.append("INSERT INTO ");
      insert.append(m_tableName);
      insert.append(" VALUES ( ");
      if(m_id){
        insert.append(m_count);
        insert.append(", ");
        m_count++;
      }
      for(int j = 0; j < inst.numAttributes(); j++){
        if(inst.isMissing(j))
            insert.append("NULL");
        else{
            if((inst.attribute(j)).isNumeric())
                insert.append(inst.value(j));
            else{
                String stringInsert = "'"+inst.stringValue(j)+"'";
                stringInsert = stringInsert.replaceAll("''","'");
                insert.append(stringInsert);
            }
        }
        if(j != inst.numAttributes()-1)
            insert.append(", ");
      }
      insert.append(" )");
      //System.out.println(insert.toString());
      if (m_DataBaseConnection.execute(insert.toString()) == false && m_DataBaseConnection.getUpdateCount() < 1) {
        throw new IOException("Tuple cannot be inserted.");
      }
  }
  
  /** Saves an instances incrementally. Structure has to be set by using the
   * setStructure() method or setInstances() method. When a structure is set, a table is created. 
   * @param inst the instance to save
   * @throws IOException throws IOEXception.
   */  
  public void writeIncremental(Instance inst) throws IOException{
  
      int writeMode = getWriteMode();
      Instances structure = getInstances();
      
      if(m_DataBaseConnection == null)
           throw new IOException("No database has been set up.");
      if(getRetrieval() == BATCH)
          throw new IOException("Batch and incremental saving cannot be mixed.");
      setRetrieval(INCREMENTAL);
      
      try{
        if(!m_DataBaseConnection.isConnected())
            connectToDatabase();
        if(writeMode == WAIT){
            if(structure == null){
                setWriteMode(CANCEL);
                if(inst != null)
                    throw new Exception("Structure(Header Information) has to be set in advance");
            }
            else
                setWriteMode(STRUCTURE_READY);
            writeMode = getWriteMode();
        }
        if(writeMode == CANCEL){
          cancel();
        }
        if(writeMode == STRUCTURE_READY){
          setWriteMode(WRITE);
          writeStructure();
          writeMode = getWriteMode();
        }
        if(writeMode == WRITE){
          if(structure == null)
              throw new IOException("No instances information available.");
          if(inst != null){
            //write instance 
            writeInstance(inst);  
          }
          else{
          //close
              m_DataBaseConnection.disconnectFromDatabase();
              resetStructure();
              m_count = 1;
          }
        }
      }catch(Exception ex) {
            printException(ex);
       }    
  }
  
  /** Writes a Batch of instances
   * @throws IOException throws IOException
   */
  public void writeBatch() throws IOException {
  
      Instances instances = getInstances();
      if(instances == null)
          throw new IOException("No instances to save");
      if(getRetrieval() == INCREMENTAL)
          throw new IOException("Batch and incremental saving cannot be mixed.");
      if(m_DataBaseConnection == null)
           throw new IOException("No database has been set up.");
      setRetrieval(BATCH);
      try{
          if(!m_DataBaseConnection.isConnected())
              connectToDatabase();
          setWriteMode(WRITE);
          writeStructure();
          for(int i = 0; i < instances.numInstances(); i++){
            writeInstance(instances.instance(i));
          }
          m_DataBaseConnection.disconnectFromDatabase();
          setWriteMode(WAIT);
          resetStructure();
          m_count = 1;
      } catch(Exception ex) {
            printException(ex);
       }    
  }

    /**Prints an exception
   * @param ex the exception to print
   */  
  private void printException(Exception ex){
  
      System.out.println("\n--- Exception caught ---\n");
	while (ex != null) {
		System.out.println("Message:   "
                                   + ex.getMessage ());
                if(ex instanceof SQLException){
                    System.out.println("SQLState:  "
                                   + ((SQLException)ex).getSQLState ());
                    System.out.println("ErrorCode: "
                                   + ((SQLException)ex).getErrorCode ());
                    ex = ((SQLException)ex).getNextException();
                }
                else
                    ex = null;
		System.out.println("");
	}
      
  }
  
  /** Gets the setting
   * @return the current setting
   */  
  public String[] getOptions() {
    Vector options = new Vector();

    if ( (m_tableName != null) && (m_tableName.length() != 0) ) {
      options.add("-T"); 
      options.add(m_tableName);
    }
    
    if (m_id)
        options.add("-P");

    if ( (m_inputFile != null) && (m_inputFile.length() != 0) ) {
      options.add("-i"); 
      options.add(m_inputFile);
    }
    
    return (String[]) options.toArray(new String[options.size()]);
  }
  
  /** Lists the available options
   * @return an enumeration of the available options
   */  
  public java.util.Enumeration listOptions() {
      
     FastVector newVector = new FastVector(3);

     newVector.addElement(new Option("\tThe name of the table (default: the relation name).",
				     "T",1,"-T <table name>"));
     newVector.addElement(new Option("\tAdd an ID column as primary key. The name is specified in the DatabaseUtils file. The DatabaseLoader won't load this column.",
				     "P",0,"-P"));
     newVector.addElement(new Option("\tInput file in arff format that should be saved in database.",
				     "i",1,"-i<input file name>"));
     
     return  newVector.elements();
  }
  
  /** Sets the options.
   *
   * Available options are:
   * -T <table name> <br>
   * Sets the name of teh table (default: the name of the relation)<p>
   *
   * -P <br>
   * If set, a primary key column is generated automatically (containing the row number as INTEGER)<p>
   *
   * -i <input-file> <br>
   * Specifies an ARFF file as input (for command line use) <p>
   *
   * @param options the options
   * @throws Exception if options cannot be set
   */  
  public void setOptions(String[] options) throws Exception {
      
    String tableString, inputString;
    tableString = Utils.getOption('T',options);
    inputString = Utils.getOption('i',options);
    resetOptions();
    if(tableString.length() != 0){
        m_tableName = tableString;
        m_tabName = false;
    }
    m_id = Utils.getFlag('P', options);
    if(inputString.length() != 0){
        try{
            m_inputFile = inputString;
            ArffLoader al = new ArffLoader();
            File inputFile = new File(inputString);
            al.setSource(inputFile);
            setInstances(al.getDataSet());
            //System.out.println(getInstances());
            if(tableString.length() == 0)
                m_tableName = getInstances().relationName();
        }catch(Exception ex) {
            printException(ex);
            ex.printStackTrace();
      }    
    }
  }
  
  /**
   * Main method.
   *
   * @param options should contain the options of a Saver.
   */
  public static void main(String [] options) {
      
      StringBuffer text = new StringBuffer();
      text.append("\n\nDatabaseSaver options:\n");
      try {
	DatabaseSaver asv = new DatabaseSaver();
        try {
          Enumeration enumi = asv.listOptions();
          while (enumi.hasMoreElements()) {
            Option option = (Option)enumi.nextElement();
            text.append(option.synopsis()+'\n');
            text.append(option.description()+'\n');
        }  
          asv.setOptions(options);
          asv.setDestination();
        } catch (Exception ex) {
            ex.printStackTrace();
	}
        //incremental
        
        /*asv.setRetrieval(INCREMENTAL);
        Instances instances = asv.getInstances();
        asv.setStructure(instances);
        for(int i = 0; i < instances.numInstances(); i++){ //last instance is null and finishes incremental saving
            asv.writeIncremental(instances.instance(i));
        }
        asv.writeIncremental(null);*/
        
        
        //batch
        asv.writeBatch();
      } catch (Exception ex) {
	ex.printStackTrace();
        System.out.println(text);
	}
      
    }
}