databaseutils.java

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

/*
 *    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 weka.core.Utils;

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;

/**
 * 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: 1.1 $
 */
public class DatabaseUtils
  implements Serializable {

  /** for serialization */
  static final long serialVersionUID = -8252351994547116729L;
  
  /** 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 */
  public final static String PROPERTY_FILE = "weka/experiment/DatabaseUtils.props";

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

  /** keeping track of drivers that couldn't be loaded */
  protected static Vector DRIVERS_ERRORS;

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

  /* Type mapping used for reading experiment results */
  /** Type mapping for STRING used for reading experiment results */
  public static final int STRING = 0;
  /** Type mapping for BOOL used for reading experiment results */
  public static final int BOOL = 1;
  /** Type mapping for DOUBLE used for reading experiment results */
  public static final int DOUBLE = 2;
  /** Type mapping for BYTE used for reading experiment results */
  public static final int BYTE = 3;
  /** Type mapping for SHORT used for reading experiment results */
  public static final int SHORT = 4;
  /** Type mapping for INTEGER used for reading experiment results */
  public static final int INTEGER = 5;
  /** Type mapping for LONG used for reading experiment results */
  public static final int LONG = 6;
  /** Type mapping for FLOAT used for reading experiment results */
  public static final int FLOAT = 7;
  /** Type mapping for DATE used for reading experiment results */
  public static final int DATE = 8; 
  
  /** Database URL */
  protected String m_DatabaseURL;
 
  /** 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 = false;
  
  /** Database username */
  protected String m_userName = "";

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

  /* mappings used for creating Tables. Can be overridden in DatabaseUtils.props*/
  /** string type for the create table statement */
  protected String m_stringType = "LONGVARCHAR";
  /** integer type for the create table statement */
  protected String m_intType = "INT";
  /** double type for the create table statement */
  protected String m_doubleType = "DOUBLE";

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

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

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

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

  /**
   * Reads properties and sets up the database drivers
   *
   * @throws Exception 	if an error occurs
   */
  public DatabaseUtils() throws Exception {
    if (DRIVERS_ERRORS == null)
      DRIVERS_ERRORS = new Vector();

    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();
        boolean result;
        try {
          Class.forName(driver);
          DRIVERS.addElement(driver);
          result = true;
        }
        catch (Exception e) {
          result = false;
        }
        if (!result && !DRIVERS_ERRORS.contains(driver))
          System.err.println(
              "Trying to add JDBC driver: " + driver 
              + " - " + (result ? "Success!" : "Error, not in CLASSPATH?"));
        if (!result)
          DRIVERS_ERRORS.add(driver);
      }
    } catch (Exception ex) {
      System.err.println("Problem reading properties. Fix before continuing.");
      System.err.println(ex);
    }

    m_DatabaseURL = PROPERTIES.getProperty("jdbcURL", "jdbc:idb=experiments.prp");
    m_stringType  = PROPERTIES.getProperty("CREATE_STRING", "LONGVARCHAR");
    m_intType     = PROPERTIES.getProperty("CREATE_INT", "INT");
    m_doubleType  = PROPERTIES.getProperty("CREATE_DOUBLE", "DOUBLE");
    m_checkForUpperCaseNames = PROPERTIES.getProperty(
				"checkUpperCaseNames", "false").equals("true");
    m_checkForLowerCaseNames = PROPERTIES.getProperty(
				"checkLowerCaseNames", "false").equals("true");
    m_setAutoCommit = PROPERTIES.getProperty(
			"setAutoCommit", "false").equals("true");
    m_createIndex   = PROPERTIES.getProperty(
			"createIndex", "false").equals("true");
  }
  
  /** 
   * returns key column headings in their original case. Used for
   * those databases that create uppercase column names.
   * 
   * @param columnName	the column to retrieve the original case for
   * @return		the original case
   */
  protected String attributeCaseFix(String columnName){
    if (m_checkForUpperCaseNames) {
      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;
      }
    }
    else if (m_checkForLowerCaseNames) {
      String ucname = columnName.toLowerCase();
      if (ucname.equals(EXP_TYPE_COL.toLowerCase())) {
	return EXP_TYPE_COL;
      } else if (ucname.equals(EXP_SETUP_COL.toLowerCase())) {
	return EXP_SETUP_COL;
      } else if (ucname.equals(EXP_RESULT_COL.toLowerCase())) {
	return EXP_RESULT_COL;
      } else {
	return columnName;
      }
    }
    else {
      return columnName;
    }
  }
 
  /**
   * 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()). Blanks in the type 
   * are replaced with underscores "_", since Java property names can't contain blanks.
   * 
   * @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
   */
  public int translateDBColumnType(String type) {
    try {
      // Oracle, e.g., has datatypes like "DOUBLE PRECISION"
      // BUT property names can't have blanks in the name (unless escaped with
      // a backslash), hence also check for names where the blanks are 
      // replaced with underscores "_":
      String value = PROPERTIES.getProperty(type);
      String typeUnderscore = type.replaceAll(" ", "_");
      if (value == null)
	value = PROPERTIES.getProperty(typeUnderscore);
      return Integer.parseInt(value);
    } catch (NumberFormatException e) {
      throw new IllegalArgumentException(
	  "Unknown data type: " + type + ". "
	  + "Add entry in " + PROPERTY_FILE + ".\n"
	  + "If the type contains blanks, either escape them with a backslash "
	  + "or use underscores instead of blanks.");
    }
  }

  /**
   * 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) {