databaseutils.java

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

   * @param rp 		the ResultProducer who will generate the result if 
   * 			required
   * @param key 	the key for the result
   * @param result 	the result to store
   * @throws Exception 	if an error occurs
   */
  public void putResultInTable(String tableName,
			       ResultProducer rp,
			       Object [] key,
			       Object [] result)
    throws Exception {
    
    String query = "INSERT INTO " + tableName
      + " VALUES ( ";
    // Add the results to the table
    for (int i = 0; i < key.length; i++) {
      if (i != 0) {
	query += ',';
      }
      if (key[i] != null) {
	if (key[i] instanceof String) {
	  query += "'" + processKeyString(key[i].toString()) + "'";
	} else if (key[i] instanceof Double) {
	  query += safeDoubleToString((Double)key[i]);
	} else {
	  query += key[i].toString();
	}
      } else {
	query += "NULL";
      }
    }
    for (int i = 0; i < result.length; i++) {
      query +=  ',';
      if (result[i] != null) {
	if (result[i] instanceof String) {
	  query += "'" + result[i].toString() + "'";
	} else  if (result[i] instanceof Double) {
	  query += safeDoubleToString((Double)result[i]);
	} else {
	  query += result[i].toString();
	  //!!
	  //System.err.println("res: "+ result[i].toString());
	}
      } else {
	query += "NULL";
      }
    }
    query += ')';
    
    if (m_Debug) {
      System.err.println("Submitting result: " + query);
    }
    if (execute(query)) {
      if (m_Debug) {
	System.err.println("...acceptResult returned resultset");
      }
    }
  }
  
  /**
   * Inserts a + if the double is in scientific notation.
   * MySQL doesn't understand the number otherwise.
   * 
   * @param number	the number to convert
   * @return		the number as string
   */
  private String safeDoubleToString(Double number) {
    // NaN is treated as NULL
    if (number.isNaN())
      return "NULL";

    String orig = number.toString();

    int pos = orig.indexOf('E');
    if ((pos == -1) || (orig.charAt(pos + 1) == '-')) {
      return orig;
    } else {
      StringBuffer buff = new StringBuffer(orig);
      buff.insert(pos + 1, '+');
      return new String(buff);
    }
  }

  /**
   * Returns true if the experiment index exists.
   *
   * @return 		true if the index exists
   * @throws Exception 	if an error occurs
   */
  public boolean experimentIndexExists() throws Exception {
    return tableExists(EXP_INDEX_TABLE);
  }
  
  /**
   * Attempts to create the experiment index table
   *
   * @throws Exception 	if an error occurs.
   */
  public void createExperimentIndex() throws Exception {
    if (m_Debug) {
      System.err.println("Creating experiment index table...");
    }
    String query;

    // Workaround for MySQL (doesn't support LONGVARBINARY)
    // Also for InstantDB which attempts to interpret numbers when storing
    // in LONGVARBINARY
    /* if (m_Connection.getMetaData().getDriverName().
	equals("Mark Matthews' MySQL Driver")
	|| (m_Connection.getMetaData().getDriverName().
	indexOf("InstantDB JDBC Driver") != -1)) {
      query = "CREATE TABLE " + EXP_INDEX_TABLE 
	+ " ( " + EXP_TYPE_COL + " TEXT,"
	+ "  " + EXP_SETUP_COL + " TEXT,"
	+ "  " + EXP_RESULT_COL + " INT )";
	} else { */
    
      query = "CREATE TABLE " + EXP_INDEX_TABLE 
	+ " ( " + EXP_TYPE_COL + " "+ m_stringType+","
	+ "  " + EXP_SETUP_COL + " "+ m_stringType+","
	+ "  " + EXP_RESULT_COL + " "+ m_intType+" )";
      // }
    // Other possible fields:
    //   creator user name (from System properties)
    //   creation date
    if (execute(query)) {
      if (m_Debug) {
	System.err.println("...create returned resultset");
      }
    }
  }

  /**
   * Attempts to insert a results entry for the table into the
   * experiment index.
   *
   * @param rp 		the ResultProducer generating the results
   * @return 		the name of the created results table
   * @throws Exception 	if an error occurs.
   */
  public String createExperimentIndexEntry(ResultProducer rp)
    throws Exception {

    if (m_Debug) {
      System.err.println("Creating experiment index entry...");
    }

    // Execute compound transaction
    int numRows = 0;
    
    // Workaround for MySQL (doesn't support transactions)
    /*  if (m_Connection.getMetaData().getDriverName().
	equals("Mark Matthews' MySQL Driver")) {
      m_Statement.execute("LOCK TABLES " + EXP_INDEX_TABLE + " WRITE");
      System.err.println("LOCKING TABLE");
      } else {*/
      
      //}

    // Get the number of rows
    String query = "SELECT COUNT(*) FROM " + EXP_INDEX_TABLE;
    if (execute(query)) {
      if (m_Debug) {
	System.err.println("...getting number of rows");
      }
      ResultSet rs = m_PreparedStatement.getResultSet();
      if (rs.next()) {
	numRows = rs.getInt(1);
      }
      rs.close();
    }

    // Add an entry in the index table
    String expType = rp.getClass().getName();
    String expParams = rp.getCompatibilityState();
    query = "INSERT INTO " + EXP_INDEX_TABLE
      +" VALUES ('"
      + expType + "', '" + expParams
      + "', " + numRows + " )"; 
    if (execute(query)) {
      if (m_Debug) {
	System.err.println("...create returned resultset");
      }
    }
    
    // Finished compound transaction
    // Workaround for MySQL (doesn't support transactions)
    /* if (m_Connection.getMetaData().getDriverName().
	equals("Mark Matthews' MySQL Driver")) {
      m_Statement.execute("UNLOCK TABLES");
      System.err.println("UNLOCKING TABLE");
      } else { */
    if (!m_setAutoCommit) {
      m_Connection.commit();
      m_Connection.setAutoCommit(true);
    }
      //}

    String tableName = getResultsTableName(rp);
    if (tableName == null) {
      throw new Exception("Problem adding experiment index entry");
    }

    // Drop any existing table by that name (shouldn't occur unless
    // the experiment index is destroyed, in which case the experimental
    // conditions of the existing table are unknown)
    try {
      query = "DROP TABLE " + tableName;
      if (m_Debug) {
	System.err.println(query);
      }
      execute(query);
    } catch (SQLException ex) {
      System.err.println(ex.getMessage());
    }
    return tableName;
  }

  /**
   * Gets the name of the experiment table that stores results from a
   * particular ResultProducer.
   *
   * @param rp 		the ResultProducer
   * @return 		the name of the table where the results for this 
   * 			ResultProducer are stored, or null if there is no 
   * 			table for this ResultProducer.
   * @throws Exception 	if an error occurs
   */
  public String getResultsTableName(ResultProducer rp) throws Exception {
    // Get the experiment table name, or create a new table if necessary.
    if (m_Debug) {
      System.err.println("Getting results table name...");
    }
    String expType = rp.getClass().getName();
    String expParams = rp.getCompatibilityState();
    String query = "SELECT " + EXP_RESULT_COL 
      + " FROM " + EXP_INDEX_TABLE
       + " WHERE " + EXP_TYPE_COL + "='" + expType 
      + "' AND " + EXP_SETUP_COL + "='" + expParams + "'";
    String tableName = null;
    if (execute(query)) {
      ResultSet rs = m_PreparedStatement.getResultSet();
      if (rs.next()) {
	tableName = rs.getString(1);
	if (rs.next()) {
	  throw new Exception("More than one index entry "
			      + "for experiment config: " + query);
	}
      }
      rs.close();
    }
    if (m_Debug) {
      System.err.println("...results table = " + ((tableName == null) 
						  ? "<null>" 
						  : EXP_RESULT_PREFIX
						  + tableName));
    }
    return (tableName == null) ? tableName : EXP_RESULT_PREFIX + tableName;
  }

  /**
   * Creates a results table for the supplied result producer.
   *
   * @param rp 		the ResultProducer generating the results
   * @param tableName 	the name of the resultsTable
   * @return 		the name of the created results table
   * @throws Exception 	if an error occurs.
   */
  public String createResultsTable(ResultProducer rp, String tableName)
    throws Exception {

    if (m_Debug) {
      System.err.println("Creating results table " + tableName + "...");
    }
    String query = "CREATE TABLE " + tableName + " ( ";
    // Loop over the key fields
    String [] names = rp.getKeyNames();
    Object [] types = rp.getKeyTypes();
    if (names.length != types.length) {
      throw new Exception("key names types differ in length");
    }
    for (int i = 0; i < names.length; i++) {
      query += "Key_" + names[i] + " ";
      if (types[i] instanceof Double) {
	query += m_doubleType;
      } else if (types[i] instanceof String) {

	// Workaround for MySQL (doesn't support LONGVARCHAR)
	// Also for InstantDB which attempts to interpret numbers when storing
	// in LONGVARBINARY
	/*if (m_Connection.getMetaData().getDriverName().
	    equals("Mark Matthews' MySQL Driver")
	    || (m_Connection.getMetaData().getDriverName().
		indexOf("InstantDB JDBC Driver")) != -1) {
	  query += "TEXT ";
	  } else { */
	//query += "LONGVARCHAR ";
	  query += m_stringType+" ";
	  //}
      } else {
	throw new Exception("Unknown/unsupported field type in key");
      }
      query += ", ";
    }
    // Loop over the result fields
    names = rp.getResultNames();
    types = rp.getResultTypes();
    if (names.length != types.length) {
      throw new Exception("result names and types differ in length");
    }
    for (int i = 0; i < names.length; i++) {
      query += names[i] + " ";
      if (types[i] instanceof Double) {
	query += m_doubleType;
      } else if (types[i] instanceof String) {
	
	// Workaround for MySQL (doesn't support LONGVARCHAR)
	// Also for InstantDB which attempts to interpret numbers when storing
	// in LONGVARBINARY
	/*if (m_Connection.getMetaData().getDriverName().
	    equals("Mark Matthews' MySQL Driver")
	    || (m_Connection.getMetaData().getDriverName().
		equals("InstantDB JDBC Driver"))) {
	  query += "TEXT ";
	  } else {*/
	//query += "LONGVARCHAR ";
	query += m_stringType+" ";
	  //}
      } else {
	throw new Exception("Unknown/unsupported field type in key");
      }
      if (i < names.length - 1) {
	query += ", ";
      }
    }
    query += " )";
    
    if (execute(query)) {
      if (m_Debug) {
	System.err.println("...create returned resultset");
      }
    }
    if (m_Debug) 
      System.err.println("table created");


    if (m_createIndex) {
      query = "CREATE UNIQUE INDEX Key_IDX ON "+ tableName +" (";

      String [] keyNames = rp.getKeyNames();
    
      boolean first = true;
      for (int i = 0; i < keyNames.length; i++) {
	if (keyNames[i] != null) {
	  if (first) {
	    first = false;
	    query += "Key_" + keyNames[i];
	  } else {
	    query += ",Key_" + keyNames[i];
	  } 
	}
      }
      query += ")";
    
      if (execute(query)) {
	if (m_Debug) {
	  System.err.println("...create index returned resultset");
	}
      }
    }
    return tableName;
  }
}