databaseutils.java

一个数据挖掘软件ALPHAMINERR的整个过程的JAVA版源代码

/*
 *    This program is free software; you can redistribute it and/or modify
 *    it under the terms of the GNU General Public License as published by
 *    the Free Software Foundation; either version 2 of the License, or
 *    (at your option) any later version.
 *
 *    This program is distributed in the hope that it will be useful,
 *    but WITHOUT ANY WARRANTY; without even the implied warranty of
 *    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *    GNU General Public License for more details.
 *
 *    You should have received a copy of the GNU General Public License
 *    along with this program; if not, write to the Free Software
 *    Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
 */

/*
 *    DatabaseUtils.java
 *    Copyright (C) 1999 Len Trigg
 *
 */


package weka.experiment;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Properties;
import java.util.StringTokenizer;
import java.util.Vector;

import weka.core.Utils;

/**
 * DatabaseUtils provides utility functions for accessing the experiment
 * database. The jdbc
 * driver and database to be used default to "jdbc.idbDriver" and
 * "jdbc:idb=experiments.prp". These may be changed by creating
 * a java properties file called DatabaseUtils.props in user.home or
 * the current directory. eg:<p>
 *
 * <code><pre>
 * jdbcDriver=jdbc.idbDriver
 * jdbcURL=jdbc:idb=experiments.prp
 * </pre></code><p>
 *
 * @author Len Trigg ([e-mail])
 * @version $Revision$
 */
public class DatabaseUtils implements Serializable {

  /** The name of the table containing the index to experiments */
  public static final String EXP_INDEX_TABLE = "Experiment_index";

  /** The name of the column containing the experiment type (ResultProducer) */
  public static final String EXP_TYPE_COL    = "Experiment_type";

  /** The name of the column containing the experiment setup (parameters) */
  public static final String EXP_SETUP_COL   = "Experiment_setup";
  
  /** The name of the column containing the results table name */
  public static final String EXP_RESULT_COL  = "Result_table";

  /** The prefix for result table names */
  public static final String EXP_RESULT_PREFIX = "Results";

  /** The name of the properties file */
  protected static String PROPERTY_FILE
    = "weka/experiment/DatabaseUtils.props";

  /** Holds the jdbc drivers to be used (only to stop them being gc'ed) */
  protected static Vector DRIVERS = new Vector();

  /** Properties associated with the database connection */
  protected static Properties PROPERTIES;



  /* Type mapping used for reading experiment results */
  public static final int STRING = 0;
  public static final int BOOL = 1;
  public static final int DOUBLE = 2;
  public static final int BYTE = 3;
  public static final int SHORT = 4;
  public static final int INTEGER = 5;
  public static final int LONG = 6;
  public static final int FLOAT = 7;
  public static final int DATE = 8; 
 

  /* Load the database drivers -- the properties files only get consulted
   * when the class is initially loaded, not for every object instantiated
   */
  static {

    try {
      PROPERTIES = Utils.readProperties(PROPERTY_FILE);
   
      // Register the drivers in jdbc DriverManager
      String drivers = PROPERTIES.getProperty("jdbcDriver",
					    "jdbc.idbDriver");

      if (drivers == null) {
	throw new Exception("No jdbc drivers specified");
      }
      // The call to newInstance() is necessary on some platforms
      // (with some java VM implementations)
      StringTokenizer st = new StringTokenizer(drivers, ", ");
      while (st.hasMoreTokens()) {
	String driver = st.nextToken();
	try {
	  DRIVERS.addElement(Class.forName(driver).newInstance());
	  System.err.println("Loaded driver: " + driver);
	} catch (Exception ex) {
	  // Drop through
	}
      }
    } catch (Exception ex) {
      System.err.println("Problem reading properties. Fix before continuing.");
      System.err.println(ex);
    }
  }
  
  /** Database URL */
  protected String m_DatabaseURL;

  
  /* returns key column headings in their original case. Used for
     those databases that create uppercase column names. */
  protected String attributeCaseFix(String columnName){
    if (m_checkForUpperCaseNames==false){
      return(columnName);
    }
    String ucname = columnName.toUpperCase();
    if (ucname.equals(EXP_TYPE_COL.toUpperCase())){
      return (EXP_TYPE_COL);
    } else if (ucname.equals(EXP_SETUP_COL.toUpperCase())){
      return (EXP_SETUP_COL);
    } else if (ucname.equals(EXP_RESULT_COL.toUpperCase())){
      return (EXP_RESULT_COL);
    } else {
      return(columnName);
    }
    
    
  }

  /** Set the database username 
   *
   * @param username Username for Database.
   */
  public void setUsername(String username){
    m_userName=username; 
  }
  
  /** Get the database username 
   *
   * @return Database username
   */
  public String getUsername(){
    return(m_userName);
  }

  /** Set the database password
   *
   * @param password Password for Database.
   */
  public void setPassword(String password){
    m_password=password;
  }
  
  /** Get the database password
   *
   * @return  Password for Database.
   */
  public String getPassword(){
    return(m_password);
  }

 
  /**
   * translates the column data type string to an integer value that indicates
   * which data type / get()-Method to use in order to retrieve values from the
   * database (see DatabaseUtils.Properties, InstanceQuery())
   * @param type the column type as retrieved with java.sql.MetaData.getColumnTypeName(int)
   * @return an integer value that indicates
   * which data type / get()-Method to use in order to retrieve values from the
   */
  int translateDBColumnType(String type) {
    return Integer.parseInt(PROPERTIES.getProperty(type));
  }
 
  /** The prepared statement used for database queries. */
  protected PreparedStatement m_PreparedStatement;
 
   
  /** The database connection */
  protected Connection m_Connection;


  /** True if debugging output should be printed */
  protected boolean m_Debug = true;

 
  
  /** Database username */
  protected String m_userName="";

  /** Database Password */
  protected String m_password="";

  /** mappings used for creating Tables. Can be overridden in DatabaseUtils.props*/
  protected String m_stringType="LONGVARCHAR";
  protected String m_intType="INT";
  protected String m_doubleType="DOUBLE";
  

  /* For databases where Tables and Columns are created in upper case */
  protected boolean m_checkForUpperCaseNames=false;

  /* setAutoCommit on the database? */
  protected boolean m_setAutoCommit=true;

  /* create index on the database? */
  protected boolean m_createIndex=false;


  /**
   * Sets up the database drivers
   *
   * @exception Exception if an error occurs
   */
  public DatabaseUtils() throws Exception {

    m_DatabaseURL = PROPERTIES.getProperty("jdbcURL",
					   "jdbc:idb=experiments.prp");
    m_stringType=PROPERTIES.getProperty("CREATE_STRING");
    m_intType=PROPERTIES.getProperty("CREATE_INT");
    m_doubleType=PROPERTIES.getProperty("CREATE_DOUBLE");
    String uctn=PROPERTIES.getProperty("checkUpperCaseNames");
    if (uctn.equals("true")) {
      m_checkForUpperCaseNames=true;
    }else {
      m_checkForUpperCaseNames=false;
    }
    uctn=PROPERTIES.getProperty("setAutoCommit");
    if (uctn.equals("true")) {
      m_setAutoCommit=true;
    } else {
      m_setAutoCommit=false;
    }
    uctn=PROPERTIES.getProperty("createIndex");
    if (uctn.equals("true")) {
      m_createIndex=true;
    } else {
      m_createIndex=false;
    }
  }

  /**
   * Converts an array of objects to a string by inserting a space
   * between each element. Null elements are printed as ?
   *
   * @param array the array of objects
   * @return a value of type 'String'
   */
  public static String arrayToString(Object [] array) {

    String result = "";
    if (array == null) {
      result = "<null>";
    } else {
      for (int i = 0; i < array.length; i++) {
	if (array[i] == null) {
	  result += " ?";
	} else {
	  result += " " + array[i];
	}
      }
    }
    return result;
  }

  /**
   * Returns the name associated with a SQL type.
   *
   * @param type the SQL type
   * @return the name of the type
   */
  public static String typeName(int type) {
  
    switch (type) {
    case Types.BIGINT :
      return "BIGINT ";
    case Types.BINARY:
      return "BINARY";
    case Types.BIT:
      return "BIT";
    case Types.CHAR:
      return "CHAR";
    case Types.DATE:
      return "DATE";
    case Types.DECIMAL:
      return "DECIMAL";
    case Types.DOUBLE:
      return "DOUBLE";
    case Types.FLOAT:
      return "FLOAT";
    case Types.INTEGER:
      return "INTEGER";
    case Types.LONGVARBINARY:
      return "LONGVARBINARY";
    case Types.LONGVARCHAR:
      return "LONGVARCHAR";
    case Types.NULL:
      return "NULL";
    case Types.NUMERIC:
      return "NUMERIC";
    case Types.OTHER:
      return "OTHER";
    case Types.REAL:
      return "REAL";
    case Types.SMALLINT:
      return "SMALLINT";
    case Types.TIME:
      return "TIME";
    case Types.TIMESTAMP:
      return "TIMESTAMP";
    case Types.TINYINT:
      return "TINYINT";
    case Types.VARBINARY:
      return "VARBINARY";
    case Types.VARCHAR:
      return "VARCHAR";
    default:
      return "Unknown";
    }
  }

  /**
   * Returns the tip text for this property
   * @return tip text for this property suitable for
   * displaying in the explorer/experimenter gui
   */
  public String databaseURLTipText() {
    return "Set the URL to the database.";
  }

  /**
   * Get the value of DatabaseURL.
   *
   * @return Value of DatabaseURL.
   */
  public String getDatabaseURL() {
    
    return m_DatabaseURL;
  }
  
  /**
   * Set the value of DatabaseURL.
   *
   * @param newDatabaseURL Value to assign to DatabaseURL.
   */
  public void setDatabaseURL(String newDatabaseURL) {
    
    m_DatabaseURL = newDatabaseURL;
  }

  /**
   * Opens a connection to the database
   *
   * @exception Exception if an error occurs
   */
  public void connectToDatabase() throws Exception {
    
    if (m_Debug) {
      System.err.println("Connecting to " + m_DatabaseURL);
    }
    if (m_Connection == null) {
      if (m_userName.equals("")) {
	m_Connection = DriverManager.getConnection(m_DatabaseURL);
      } else {
	m_Connection = DriverManager.getConnection(m_DatabaseURL,m_userName,m_password);
      }
    }
    if (m_setAutoCommit){
      m_Connection.setAutoCommit(true);
    } else {
      m_Connection.setAutoCommit(false);
    }
  }

  /**
   * Closes the connection to the database.
   *
   * @exception Exception if an error occurs
   */
  public void disconnectFromDatabase() throws Exception {

    if (m_Debug) {
      System.err.println("Disconnecting from " + m_DatabaseURL);
    }
    if (m_Connection != null) {
      m_Connection.close();
      m_Connection = null;
    }
  }
  
  /**
   * Returns true if a database connection is active.
   *
   * @return a value of type 'boolean'
   */
  public boolean isConnected() {

    return (m_Connection != null);
  }

  /**
   * Executes a SQL query.
   *
   * @param query the SQL query
   * @return true if the query generated results
   * @exception SQLException if an error occurs
   */
  public boolean execute(String query) throws SQLException {
 
    m_PreparedStatement = m_Connection.prepareStatement(query);
    return(m_PreparedStatement.execute());
  }

  /**
   * Gets the results generated by a previous query.
   *
   * @return the result set.
   * @exception SQLException if an error occurs
   */
  public ResultSet getResultSet() throws SQLException {

    return m_PreparedStatement.getResultSet();
  }
  
  /**
   * Checks that a given table exists.
   *
   * @param tableName the name of the table to look for.
   * @return true if the table exists.
   * @exception Exception if an error occurs.
   */
  public boolean tableExists(String tableName) throws Exception {

    if (m_Debug) {
      System.err.println("Checking if table " + tableName + " exists...");
    }
    DatabaseMetaData dbmd = m_Connection.getMetaData();
    ResultSet rs;
    if (m_checkForUpperCaseNames == true){
      rs = dbmd.getTables (null, null, tableName.toUpperCase(), null);
    } else {
      rs = dbmd.getTables (null, null, tableName, null);
    }
    boolean tableExists = rs.next();
    if (rs.next()) {
      throw new Exception("This table seems to exist more than once!");
    }
    rs.close();
    if (m_Debug) {
      if (tableExists) {
	System.err.println("... " + tableName + " exists");
      } else {
	System.err.println("... " + tableName + " does not exist");
      }
    }
    return tableExists;
  }
  
  /**
   * Executes a database query to see whether a result for the supplied key
   * is already in the database.           
   *
   * @param tableName the name of the table to search for the key in
   * @param rp the ResultProducer who will generate the result if required
   * @param key the key for the result
   * @return true if the result with that key is in the database already
   * @exception Exception if an error occurs
   */
  protected boolean isKeyInTable(String tableName,
				 ResultProducer rp,
				 Object[] key)
    throws Exception {

    String query = "SELECT Key_Run"
      + " FROM " + tableName;
    String [] keyNames = rp.getKeyNames();
    if (keyNames.length != key.length) {
      throw new Exception("Key names and key values of different lengths");