databaseexamplesource.java

著名的开源仿真软件yale

/*
 *  YALE - Yet Another Learning Environment
 *  Copyright (C) 2002, 2003
 *      Simon Fischer, Ralf Klinkenberg, Ingo Mierswa, 
 *          Katharina Morik, Oliver Ritthoff
 *      Artificial Intelligence Unit
 *      Computer Science Department
 *      University of Dortmund
 *      44221 Dortmund,  Germany
 *  email: [e-mail]
 *  web:   http://yale.cs.uni-dortmund.de/
 *
 *  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., 59 Temple Place, Suite 330, Boston, MA 02111-1307
 *  USA.
 */
package edu.udo.cs.yale.operator;

import edu.udo.cs.yale.Yale;
import edu.udo.cs.yale.operator.parameter.*;
import edu.udo.cs.yale.example.Attribute;
import edu.udo.cs.yale.example.ExampleTable;
import edu.udo.cs.yale.example.DatabaseExampleTable;
import edu.udo.cs.yale.tools.LogService;
import edu.udo.cs.yale.tools.DatabaseHandler;
import edu.udo.cs.yale.tools.Tools;

import java.io.File;
import java.io.IOException;
import java.io.BufferedReader;
import java.io.FileReader;
import java.util.List;
import java.sql.ResultSet;
import java.sql.SQLException;

/** This operator reads an {@link edu.udo.cs.yale.example.ExampleSet} from an SQL database.
 *  The SQL query can be 
 *  passed to Yale via a parameter or, in case of long SQL statements, in a separate file.
 *  Please note that column names are case sensitive! Databases may behave differently here!
 *
 *  <h5>Warning</h5> As the java <code>ResultSetMetaData</code> interface
 *  does not provide information about the possible values of nominal attributes, 
 *  the internal indices the nominal values are mapped to, will be dependent on the
 *  ordering they appear in the table. This may cause problems only when experiments
 *  are split up into training a experiment and an application or testing experiment.
 *  For learning schemes which are capable of handling nominal attributes, this is not a problem.
 *  If a learning scheme like a SVM is used with nominal data, Yale pretends that nominal
 *  attributes are numerical and uses indices for the nominal values as their numerical value.
 *  A SVM may perform well if there are only two possible values. If a test set is read in another
 *  experiment, the nominal values may be assigned different indices, and hence the SVM trained is 
 *  useless. This is not a problem for label attributes, since the classes
 *  can be specified using the <code>classes</code> parameter and hence, all leraning schemes 
 *  intended to use with nominal data are safe to use.
 *
 *  @yale.todo Fix the above problem. This may not be possible effeciently since it is not 
 *        supported by the Java ResultSet interface.
 *  @yale.xmlclass DatabaseExampleSource
 *  @author Simon, Ingo, Timm
 *  @version $Id: DatabaseExampleSource.java,v 2.14 2003/08/14 10:24:57 fischer Exp $
 *
 */
public class DatabaseExampleSource extends ResultSetExampleSource {

    private DatabaseHandler dbAccess;

    public void setNominalValues(List attributeList, ResultSet resultSet, Attribute label) {
	setNominalValuesForLabel(label);
    }

    public void setNominalValuesForLabel(Attribute label) {
	if (label != null) {
	    String classes = getParameterAsString("classes");
	    if (classes != null) {
		String labelClasses[] = classes.split(" ");
		for (int i = 0; i < labelClasses.length; i++) {
		    label.mapString(labelClasses[i].trim());
		}
	    }
	}
    }
    
    public IOObject[] apply() throws OperatorException {

	if (!getParameterAsBoolean("work_on_database")) {
	    IOObject[] output = super.apply();
	    
	    // disconnect the connection to the database:
	    try {
		dbAccess.disconnect();   
	    } catch (SQLException sqle) {
		LogService.logException("Could not disconnect from Database!", sqle);  
	    }
	    return output;

	} else {

	    try {
		String tableName = getParameterAsString("table_name");
		if (tableName == null) {
		    throw new UserError(this, 201, new Object[] { "work_on_database", "true", "table_name" });
		}
		ExampleTable table = DatabaseExampleTable.createDatabaseExampleTable(getConnectedDatabaseHandler(),
										     tableName);
		return new IOObject[] { createExampleSet(table) };
	    } catch (SQLException e) {
		throw new UserError(this, e, 304, e.getMessage());
	    }
	}
    }

    private String getQuery() throws OperatorException {
	String query = getParameterAsString("query");

	String parameterUsed = null;
	boolean warning = false;

	if (query == null) {
	    String queryFileName = getParameterAsString("query_file");
	    if (queryFileName != null) {
		try {
		    query = Tools.readTextFile(new File(queryFileName));
		    parameterUsed = "query_file";
		} catch (IOException ioe) {
		    throw new UserError(this, ioe, 302, new Object[] { queryFileName, ioe.getMessage() });  
		}
		if (query.length() == 0) {
		    throw new UserError(this, 205, queryFileName);
		}
	    } 
	} else {
	    parameterUsed = "query";
	    if (isParameterSet("query_file")) {
		warning = true;
	    }
	}

	if (query == null) {
	    if (isParameterSet("table_name")) {
		query = "select * from " + getParameterAsString("table_name");
		parameterUsed = "table_name";
	    }
	} else if (isParameterSet("table_name")) {
	    warning = true;
	}

	if (query == null) {
	    throw new UserError(this, 202, new Object[] { "query", "query_file", "table_name" });
	}

	if (warning) {
	    LogService.logMessage("Only one of the parameters 'query', 'query_file', and 'table_name' have to be set. Using value of '"+parameterUsed+"'.", 
				  LogService.WARNING);
	}

	return query;
    }

    public DatabaseHandler getConnectedDatabaseHandler() throws OperatorException, SQLException  {
	String driver       = getParameterAsString("driver");
	if ((driver == null) || (driver.length() == 0))
	    throw new UserError(this, 203, new Object[] { "yale.tools.jdbc.driver", "driver" });
        String urlprefix    = getParameterAsString("urlprefix");
        String databasename = getParameterAsString("databasename");
        String username     = getParameterAsString("username");

        String password = getParameterAsString("password");
	if (password == null) {
	    password = Yale.getInputHandler().inputPassword(databasename+": Password for user '"+username+"' required");
	}

	DatabaseHandler databaseHandler = new DatabaseHandler(databasename, urlprefix, driver);
	LogService.logMessage("Connecting to '"+urlprefix+"' ("+databasename+").", LogService.MINIMUM);
	databaseHandler.connect(username, password, true);
	return databaseHandler;
    }

    /** This method reads the file whose name is given, extracts the database
     *  access information and the query from it and executes the query. The
     *  query result is returned as a ResultSet. */
    public ResultSet getResultSet() throws OperatorException {
        // 2. Connect to database:
        ResultSet rs = null;
        try {
	    dbAccess = getConnectedDatabaseHandler();
	    String query = getQuery();
	    LogService.logMessage("Executing query: '"+query+"'", LogService.MINIMUM);
	    rs = dbAccess.query(query);
	    LogService.logMessage("Query executed.", LogService.MINIMUM);
	} catch (SQLException sqle) {
	    throw new UserError(this, sqle, 304, sqle.getMessage());  
	}
	
        if (rs == null) {
	    throw new FatalException("Empty result after querying the database!");  
	}
        return rs;
    }

    public void experimentFinished() {
	if (dbAccess != null) {
	    try {
		dbAccess.disconnect();
	    } catch (SQLException e) {
		LogService.logMessage("Cannot disconnect from database: "+e, LogService.ERROR);
	    }
	}
    }

    public List getParameterTypes() {
	List types = super.getParameterTypes();
	types.add(new ParameterTypeBoolean("work_on_database", "(EXPERIMENTAL!) If set to true, the data read from the database is NOT copied to main memory. All operations that change data will modify the database.", false));
	types.add(new ParameterTypeString("driver", "Name of the database driver.", System.getProperty("yale.tools.jdbc.driver")));
	types.add(new ParameterTypeString("urlprefix", "URL prefix of the database, e.g. 'jdbc:oracle:thin:@foo.bar:portnr:'", false));
	types.add(new ParameterTypeString("databasename", "Name of the database.", false));
	types.add(new ParameterTypeString("username", "Database username.", false));
	types.add(new ParameterTypePassword("password", "Password for the database."));
	types.add(new ParameterTypeString("classes", "Whitespace separated list of possible class values of the label attribute.", 
					  true));
	types.add(new ParameterTypeString("query", "SQL query. If not set, the query is read from the file specified by 'query_file'.", 
					  true));
	types.add(new ParameterTypeFile("query_file", "File containing the query. Only evaluated if 'query' is not set.", true));
	types.add(new ParameterTypeString("table_name", "Use this table if work_on_database is true or no other query is specified.", true));
	return types;
    }
}