jdbcdatabasecontainer.java

一个java程序编写的svm支持向量机小程序

	read_trainset_meta_info();

	Statement stmt = conn.createStatement();
	ResultSet rset;
	int i;
	// read keys
	// if parameter is set, keys are read from table
	keys = null;
	if(get_param("read_keys_from_model") != null){
	    if(get_param("read_keys_from_model").equals("true")){
		// read keys from model
		rset = stmt.executeQuery("select count(*) from "+model_name);
		rset.next();
		train_size = rset.getInt(1);
		keys = new String[train_size];
		rset = stmt.executeQuery("select key from "+model_name);
		i=0;
		while(rset.next()){
		    keys[i] = rset.getString(1);
		    i++;
		};
		rset.close();
	    };
	};
	if(keys == null){
	    // read keys from trainset
	    rset = stmt.executeQuery("select count(*) from "+examples_name);
	    rset.next();
	    train_size = rset.getInt(1);
	    rset.close();
	    keys = new String[train_size];
	    //	    rset = stmt.executeQuery("select "+key_column+", "+y_column+" from "+examples_name);
	    rset = stmt.executeQuery("select "+key_column+" from "+examples_name);
	    i=0;
	    while(rset.next()){
		keys[i] = rset.getString(1);
		i++;
	    };
	    rset.close();
	    stmt.clearBatch();
	    stmt.addBatch("delete from "+model_name);
	    stmt.addBatch("insert into "+model_name+" (key, alpha) select "+key_column+", null from "+examples_name);
	    // statistics!
	    //	    stmt.addBatch("analyze table "+model_name+" compute statistics");
	    stmt.executeBatch();
	};
	// init alpha and y
	alphas = new double[train_size];
	ys = new double[train_size];
	// read ys
	PreparedStatement y_stmt = conn.prepareStatement("select "+y_column+" from "+examples_name+" where "+key_column+" = ?");
	target_concept = get_param("target_concept");
	if(target_concept != null){
	    // translation of y to {-1,1}
	    for(i=0;i<train_size;i++){
		alphas[i] = 0.0;
		y_stmt.setString(1,keys[i]);
		rset = y_stmt.executeQuery();
		rset.next();
		if(target_concept.equals(rset.getString(1))){
		    ys[i] = 1;
		}
		else{
		    ys[i] = -1;
		};
		rset.close();
	    };
	}
	else if((Dev[dim] == 0) || 
		((Exp[dim] == 0) && (Dev[dim] == 1))){
		    // read without scaling
		    for(i=0;i<train_size;i++){
			alphas[i] = 0.0;
			y_stmt.setString(1,keys[i]);
			rset = y_stmt.executeQuery();
			rset.next();
			ys[i] = rset.getDouble(1);
			rset.close();
		    };
		}
	else{
	    // read and scale
	    for(i=0;i<train_size;i++){
		alphas[i] = 0.0;
		y_stmt.setString(1,keys[i]);
		rset = y_stmt.executeQuery();
		rset.next();
		ys[i] = (rset.getDouble(1)-Exp[dim])/Dev[dim];
		rset.close();
	    };
	};
	y_stmt.close();

	// statement for examples
	get_example_statement = conn.prepareStatement("select "+x_columns+" from "+examples_name+" where "+key_column+" = ?");

	stmt.close();
    };


    /**
     * Read testset from given location.
     * @exception Exception if an error opening the container occurred
     */
    public void read_testset(String location)
	throws Exception
    {
	test_examples_name = location;
	// read meta information (table format is supposed to be equal to trainset)
	Statement stmt = conn.createStatement();
	stmt.setFetchSize(1); // now only one row needed for meta data
	ResultSet rset;
	rset = stmt.executeQuery("select count(*) from "+test_examples_name);
	rset.next();
	test_size = rset.getInt(1);
	rset.close();
	test_keys = new String[test_size];
	rset = stmt.executeQuery("select "+key_column+" from "+test_examples_name);
	int i=0;
	while(rset.next()){
	    test_keys[i] = rset.getString(1);
	    i++;
	};
	rset.close();
	// read data

	stmt.close();
    };


    /**
     * Read model from given location.
     * @exception Exception if an error opening the container occurred
     */
    public void read_model(String location)
	throws Exception
    {
	model_name = location;
	// check if table exists
    };


    /**
     * creates a temporary table with given name as prefix
     */
    protected String create_temp_table(String name, String definition)
	throws Exception
    {
	Statement stmt = conn.createStatement();
	stmt.executeUpdate("create table "+name+" ("+definition+")");
	stmt.close();

	return name;
    };


    /**
     * Counts the training examples.
     * @return Number of examples
     */
    public int count_examples()
    {
	return train_size;
    };


    /**
     * Counts the positive training examples
     * @return Number of positive examples
     */
    public int count_pos_examples()
	throws Exception
    {
	Statement stmt = conn.createStatement();
	ResultSet rset = stmt.executeQuery("select * from "+examples_name);
	rset = stmt.executeQuery("select count(*) from "+examples_name+" where "+y_column+" > 0");
	rset.next();

	int result = rset.getInt(1);

	rset.close();
	stmt.close();


	return result;
    };


    /**
     * Gets the dimension of the examples
     * @return dim
     */
    public int get_dim()
    {
	return dim;
    };


    /**
     * Counts the test examples
     * @return Number of test examples
     */
    public int count_test_examples()
    {
	return test_size;
    };


    /**
     * Gets an example.
     * @param pos Number of example
     * @return Array of example attributes in their default order
     */
    public double[] get_example(int pos)
	throws Exception
    {
	double[] x = new double[dim];
	get_example_statement.setString(1,keys[pos]);
	ResultSet rset = get_example_statement.executeQuery();
	int i;
	rset.next();
	if(Exp != null){
	    for(i=0;i<dim;i++){
		x[i] = (rset.getDouble(i+1)-Exp[i])/Dev[i]; // columns start at 1
	    };
	}
	else{
	    for(i=0;i<dim;i++){
		x[i] = rset.getDouble(i+1); // columns start at 1
	    };
	};
	rset.close();
	return x;
    };


    /**
     * Gets a test example.
     * @param pos Number of example
     * @return Array of example attributes in their default order
     */
    public double[] get_test_example(int pos)
	throws Exception
    {
	double[] x = new double[dim];
	String key = test_keys[pos];
	Statement stmt = conn.createStatement();
	ResultSet rset = stmt.executeQuery("select "+x_columns+" from "+test_examples_name+" where "+key_column+" = '"+key+"'");
	int i;
	rset.next();
	if(Exp != null){
	    for(i=0;i<dim;i++){
		x[i] = (rset.getDouble(i+1)-Exp[i])/Dev[i]; // columns start at 1
	    };
	}
	else{
	    for(i=0;i<dim;i++){
		x[i] = rset.getDouble(i+1);
	    };
	};
	rset.close();
	stmt.close();
	return x;
    };


    /**
     * Gets an y-value.
     * @param pos Number of example
     * @return y
     */
    public double get_y(int pos)
    {
	return ys[pos];
    };


    /**
     * Gets the y array
     * @return y
     */
    public double[] get_ys()
    {
	return ys;
    };


    /**
     * Gets an alpha-value.
     * @param pos Number of example
     * @return alpha
     */
    public double get_alpha(int pos)
    {
	return alphas[pos];
    };


    /**
     * Gets the alpha array
     * @return alpha
     */
    public double[] get_alphas()
    {
	return alphas;
    };


    /**
     * swap two training examples
     * @param pos1
     * @param pos2
     */
    public void swap(int pos1, int pos2)
    {
	String dummy = keys[pos1];
	keys[pos1] = keys[pos2];
	keys[pos2] = dummy;
	double dummyd = alphas[pos1];
	alphas[pos1] = alphas[pos2];
	alphas[pos2] = dummyd;
	dummyd = ys[pos1];
	ys[pos1] = ys[pos2];
	ys[pos2] = dummyd;
    };


    /**
     * get b
     * @return b
     */
    public double get_b()
    {
	return b;
    };


    /**
     * set b
     * @param b
     */
    public void set_b(double new_b)
	throws Exception
    {
	b = new_b;
	Statement stmt = conn.createStatement();
	try{
	    int res =  stmt.executeUpdate("update "+model_name+" set alpha = "+b+" where key is null");
	    if(res == 0){
		stmt.executeUpdate("insert into "+model_name+" values (NULL, "+b+")");
	    };
	}
	catch(SQLException e){
	    stmt.executeUpdate("insert into "+model_name+" values (NULL, "+b+")");
	};
	stmt.close();

    };


    /**
     * sets a test y value.
     * @param pos Number of example
     * @param y New value
     */
    public void set_test_y(int pos, double y)
	throws Exception
    {
	Statement stmt = conn.createStatement();
	if(target_concept != null){
	    if(y > 0){
		stmt.executeUpdate("insert into "+predictions_name+"+ values ("+test_keys[pos]+", "+target_concept+")");
	    }
	    else{
		stmt.executeUpdate("insert into "+predictions_name+"+ values ("+test_keys[pos]+", 0)");
	    };
	}
	else{
	    stmt.executeUpdate("insert into "+predictions_name+"+ values ("+test_keys[pos]+", "+y+")");
	};
	stmt.close();
    };


    /**
     * sets an alpha value, sets has_alpha too.
     * @param pos Number of example
     * @param alpha New value
     */
    public void set_alpha(int pos, double alpha)
    {
	alphas[pos] = alpha;
    };


    /**
     * sets a kernel row
     * @param i example index
     * @param K_row Array of new value K(example i, example j) for all j
     */
    public void set_K_row(int i, double[] K_row)
    {

    };
   

    /**
     * checks if alphas are initialised
     * @return has_alphas
     */
    public boolean initialised_alpha()
    {
	return false;
    };


    /**
     * Get parameter value
     * @exception Exception on SQL error
     */
    public String get_param(String param)
	throws Exception
    {
	String result=null;
	get_param_statement.setString(1,param);
	ResultSet rset = get_param_statement.executeQuery();
	if(rset.next()){
	    result = rset.getString(1);
	};
	rset.close();
	return result;
    };


    public void set_select_text(String new_select_text)
    {
	select_text = new_select_text;
    };


    public PreparedStatement prepareKijStatement()
	throws Exception
    {
	PreparedStatement ps = conn.prepareStatement(
	    "select "+select_text+" as K"
	    +" from "+examples_name+" x, "
	    +examples_name+" y"
	    +" where x."+key_column+" = ? and y."+key_column+" = ?");
	return ps;
    };


    public PreparedStatement prepareKiStatement()
	throws Exception
    {
	PreparedStatement ps = conn.prepareStatement(
	    "select /*+ ORDERED ALL_ROWS */ "+select_text
	    +", y."+key_column
	    +" from "
	    +examples_name+" x, "
	    +examples_name+" y,"
	    +model_name+" t"
	    +" where x."+key_column+" = ?"
	    +" and t.key = y."+key_column,ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);
	return ps;
    };


    public PreparedStatement prepareKisStatement()
	throws Exception
    {
	int working_set_size;
	try{
	  working_set_size = (new Integer(get_param("working_set_size"))).intValue();
	}
	catch(Exception e){
	    working_set_size = 10; // has to be identical to SVM::init
	};

	String pstext = 
	    "select /*+ ORDERED ALL_ROWS */ "+select_text
	    +", x."+key_column
	    +", y."+key_column+" from "
	    +model_name+" t,"
	    +examples_name+" y,"
	    +examples_name+" x where y."+key_column
	    +" = t.key and x."+key_column+" in (?";
	for(int i=1;i<working_set_size;i++){
	    pstext += ",?";
	};
	pstext += ") order by x."+key_column;
	PreparedStatement ps = conn.prepareStatement(pstext,ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);
	return ps;
    };


    public void shrink(int from, int to)
	throws Exception
    {
	Statement stmt = conn.createStatement();
	stmt.executeQuery("drop table "+model_name);
	stmt.executeQuery("create global temporary table "+model_name+" (key varchar(60), alpha number)");
	stmt.close();


	PreparedStatement pstmt = conn.prepareStatement("insert into "+model_name+" values (?,null)");
	pstmt.clearBatch();
	int i;
	for(i=0;i<to;i++){
	    pstmt.setString(1,keys[i]);
	    pstmt.addBatch();
	};
	pstmt.executeBatch();
	pstmt.close();	
    };
};