databaseopen.java

一个数据挖掘系统的源码

/**
 *
 *   AgentAcademy - an open source Data Mining framework for
 *   training intelligent agents
 *
 *   Copyright (C)   2001-2003 AA Consortium.
 *
 *   This library is open source software; you can redistribute it
 *   and/or modify it under the terms of the GNU Lesser General
 *   Public License as published by the Free Software Foundation;
 *   either version 2.0 of the License, or (at your option) any later
 *   version.
 *
 *   This library 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 Lesser General Public
 *   License along with this library; if not, write to the Free
 *   Software Foundation, Inc., 59 Temple Place, Suite 330, Boston,
 *   MA  02111-1307 USA
 *
 */

package org.agentacademy.modules.dataminer.main;

/**
 * <p>Title: The Data Miner prototype</p>
 * <p>Description: A prototype for the DataMiner (DM), the Agent Academy (AA) module responsible for performing data mining on the contents of the Agent Use Repository (AUR). The extracted knowledge is to be sent back to the AUR in the form of a PMML document.</p>
 * <p>Copyright: Copyright (c) 2002</p>
 * <p>Company: CERTH</p>
 * @author asymeon
 * @version 0.3
 */

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import org.apache.log4j.Logger;
import weka.experiment.ResultProducer;

/**
 * <p>Title: The DataMiner Prototype (draft)</p>
 * <p>Description: A prototype for the DataMiner (DM), the Agent Academy (AA) module responsible for performing data mining on the contents of the Agent Use Repository (AUR). The extracted knowledge is to be sent back to the AUR in the form of a FIPA-SL message. This is a draft version of the DM, developed for the first Project Review, July 4-5.</p>
 * <p>Copyright: Copyright (c) 2002</p>
 * <p>Company: CERTH</p>
 * @author Andreas L. Symeonidis, Dionysis D. Kechagias
 * @version 1.0
 */

/**
 * DatabaseOpen provides utility functions for accessing the experiment
 * database. The jdbc driver and database to be used default to "jdbc.postgresql.Driver" and
 * "jdbc:postgresql://localhost:5432/new". These may be changed by creating
 * a java properties file called DatabaseOpen.props in user.home or
 * the current directory. eg:<p>
 *
 * <code><pre>
 * jdbcDriver=jdbc.postgresql.Driver
 * jdbcURL=jdbc:postgresql://localhost:5432/new
 * </pre></code><p>
 *
 * @author Andreas Symeonidis ([e-mail])
 */
  public class DatabaseOpen {

      /** 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";

    private static final String DEFAULT_DATABASE_NAME = "new";
    private static final String DEFAULT_URL = "jdbc:postgresql://localhost:5432/" + DEFAULT_DATABASE_NAME;
    private static final String DEFAULT_USER = "postgres";//"username";
    private static final String DEFAULT_PASS = "asymeon";//"password";

    private Connection con;
    private Statement stmt;
    private String url, user, pass;
    public static Logger                log = Logger.getLogger(DatabaseOpen.class);
    protected boolean Debug = true;

     public void connectToDatabase (){
        try {
            Class.forName("org.postgresql.Driver");
            con = DriverManager.getConnection("jdbc:postgresql://localhost:5432/new" , "postgres", "asymeon");
            log.debug("Connection successful");
            stmt = con.createStatement();
            log.debug("Create statement successful");

        }
        catch (SQLException e) {
            log.error("*** DatabaseOpen.<init>: SQL Exception> "+e);
        }
        catch (ClassNotFoundException e) {
            log.error("*** DatabaseOpen.<init>: "+e);
        }
    }
    public ResultSet getResultset () throws SQLException {
      return stmt.getResultSet();
    }

    public void DatabaseClose() throws Exception {
      if (con != null) {
          con.close();
          con = null;
          stmt = null;
      }
    }

public Object [] getResultFromTable(String tableName,
					 ResultProducer rp,
					 Object [] key)
    throws Exception {

    String query = "SELECT ";
    String [] resultNames = rp.getResultNames();
    for (int i = 0; i < resultNames.length; i++) {
      if (i != 0) {
	query += ", ";
      }
      query += resultNames[i];
    }
    query += " FROM " + tableName;
    String [] keyNames = rp.getKeyNames();
    if (keyNames.length != key.length) {
      throw new Exception("Key names and key values of different lengths");
    }
    boolean first = true;
    for (int i = 0; i < key.length; i++) {
      if (key[i] != null) {
	if (first) {
	  query += " WHERE ";
	  first = false;
	} else {
	  query += " AND ";
	}
	query += "Key_" + keyNames[i] + '=';
	if (key[i] instanceof String) {
	  query += '"' + key[i].toString() + '"';
	} else {
	  query += key[i].toString();
	}
      }
    }
    if (!stmt.execute(query)) {
      throw new Exception("Couldn't execute query: " + query);
    }
    ResultSet rs = stmt.getResultSet();
    ResultSetMetaData md = rs.getMetaData();
    int numAttributes = md.getColumnCount();
    if (!rs.next()) {
      throw new Exception("No result for query: " + query);
    }
    // Extract the columns for the result
    Object [] result = new Object [numAttributes];
    for(int i = 1; i <= numAttributes; i++) {
      switch (md.getColumnType(i)) {
      case Types.CHAR:
      case Types.VARCHAR:
      case Types.LONGVARCHAR:
      case Types.BINARY:
      case Types.VARBINARY:
      case Types.LONGVARBINARY:
	result[i - 1] = rs.getString(i);
	if (rs.wasNull()) {
	  result[i - 1] = null;
	}
	break;
      case Types.FLOAT:
      case Types.DOUBLE:
	result[i - 1] = new Double(rs.getDouble(i));
	if (rs.wasNull()) {
	  result[i - 1] = null;
	}
	break;
      default:
	throw new Exception("Unhandled SQL result type (field " + (i + 1)
			    + "): "
			    + DatabaseOpen.typeName(md.getColumnType(i)));
      }
    }
    if (rs.next()) {
      throw new Exception("More than one result entry "
			  + "for result key: " + query);
    }
    rs.close();
    return result;
  }

public String getResultsTableName(ResultProducer rp) throws Exception {

    // Get the experiment table name, or create a new table if necessary.
    if (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 (stmt.execute(query)) {
      ResultSet rs = stmt.getResultSet();
      int numAttributes = rs.getMetaData().getColumnCount();
      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 (Debug) {
      System.err.println("...results table = " + ((tableName == null)
						  ? "<null>"
						  : EXP_RESULT_PREFIX
						  + tableName));
    }
    return (tableName == null) ? tableName : EXP_RESULT_PREFIX + tableName;
  }

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 += '\'' + key[i].toString() + '\'';