/*
 * MiningMart Version 1.0
 * 
 * Copyright (C) 2006 Martin Scholz, Timm Euler, 
 *                    Daniel Hakenjos, Katharina Morik
 *
 * Contact: miningmart@ls8.cs.uni-dortmund.de
 *
 * A list of contributing developers (other than the copyright 
 * holders) can be found at
 * http://mmart.cs.uni-dortmund.de/downloads/download.html
 * 
 * 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, see the file MM_HOME/LICENSE; if not, write
 * to the Free Software Foundation, Inc., 51 Franklin Street, Fifth
 * Floor, Boston, MA 02110-1301, USA.
 */
package edu.udo.cs.miningmart.compiler.wrapper;

import java.sql.SQLException;
import java.util.Iterator;
import java.util.StringTokenizer;
import java.util.Vector;

import edu.udo.cs.miningmart.db.CompilerDatabaseService;
import edu.udo.cs.miningmart.db.DB;
import edu.udo.cs.miningmart.db.DbCoreOracle;
import edu.udo.cs.miningmart.exception.DbConnectionClosed;
import edu.udo.cs.miningmart.exception.M4CompilerError;
import edu.udo.cs.miningmart.exception.M4Exception;
import edu.udo.cs.miningmart.m4.Column;
import edu.udo.cs.miningmart.m4.Columnset;
import edu.udo.cs.miningmart.m4.ConceptualDatatypes;
import edu.udo.cs.miningmart.m4.RelationalDatatypes;
import edu.udo.cs.miningmart.m4.Value;
import edu.udo.cs.miningmart.m4.utils.Print;

/**
 * Abstract superclass for the wrappers for the Support Vector Machine
 * algorithms mySVM and mySVM/db.
 * 
 * @author Timm Euler
 * @version $Id: SVM_Wrapper.java,v 1.4 2006/04/11 14:10:17 euler Exp $
 */
public abstract class SVM_Wrapper
{
    public static final long DEFAULTSAMPLESIZE = 10000;
    
	// Prefix for the name of the table holding the sampled source data:
    protected static final String SAMPLE_TABLE_PREFIX = "tmp1_";
    
    // Prefix for the name of the temporary table used by the class DrawSample:
    protected static final String TEMP_TABLE_PREFIX   = "tmp2_";
	
    protected long sampleSize;
    
	private final CompilerDatabaseService databaseObj;
	private final Print pr;
	private final String dbPrefix;
	
	protected final boolean useOracle, usePostgres; // add more to adapt to more DMBS
	                                                // exactly ONE of them must be true!
	
	protected String lossPos, lossNeg;
	
    // two attributes needed for data conversion:
    // protected String[][] symbolsForBooleanValues;   // needed to convert Binary/String datatypes back and forth
    // protected Vector implementationalColumnTypes;   // needed for resulting SQL function (DBMS dependent datatypes!)
    
    protected ColumnInfo[] theColumnInfos; // needed for conversion of nominal or data to numeric
    
    private Vector theKernelParams;                 // contains default Strings 
    												// (set in this.getKernelParams())

    protected String targetColSQLDefinition;    // when SQL Def. for target column is 
    											// found, this is stored here
    protected String schema;          // schema name of input table
    protected String inputTableName;  // name of input table without schema
    protected long myStepId; 
    
	protected String targetPositive = null;     // must be set to define positive 
											    // target value in classification mode  
    
    protected boolean forClassification; // set by subclasses:
                                         // TRUE means that this wrapper wraps an
                                         // SVM for classification. FALSE means it
                                         // wraps an SVM for regression.

    protected double b;     // additive constant for the decision function of SVM,
                            // needed to create SQL output function                            

	protected double xiAlphaEstimation;  // holds the XiAlpha-Estimation after training
	protected int noSV;  				 // holds the number of support vectors after training
                            
    /**
     * Constructor. The connections to the databases are needed because the
     * SVMs need to read metadata as well as data and create an
     * intermediate table in the database.
     *
     * @param databaseObj the DB object to be used for database connections
     * @param printObj the Print object to be used for log messages
     * @param nameForDatabaseUse Prefix for all functions, view and tables that
     * 	      this wrapper creates in the database
     * @param nameOfDatabaseSchema Name of the database schema that holds the business 
     *        views and tables
     * @param stepId Id of the step where this wrapper is executed
     *
     * @throws M4CompilerError A simple exception object with an error message.
     */
    public SVM_Wrapper( CompilerDatabaseService databaseObj, 
    					Print printObj, 
    					String nameForDatabaseUse,
    					String nameOfDatabaseSchema,
    					long stepId)
    	throws M4CompilerError
    {
    	this.databaseObj = databaseObj;
    	
    	// exactly ONE of the following boolean variables must be true.    	
    	this.useOracle = (this.databaseObj.getBusinessDbms() == DB.ORACLE);
    	this.usePostgres = (this.databaseObj.getBusinessDbms() == DB.POSTGRES);
    	
        this.pr = printObj;
        this.dbPrefix = nameForDatabaseUse;
        this.schema = nameOfDatabaseSchema;
        this.myStepId = stepId;
		this.xiAlphaEstimation = 0.0;
		
        if (this.getDatabaseObj() == null)
        {   throw new M4CompilerError("Error in Support Vector Machine: got invalid db connection (null).");  }
        
        this.lossPos = null;
        this.lossNeg = null;
    }    
    
    /**
     * This abstract method defines how to call the external mySVM
     * algorithm for training with the specified parameters. After successful
     * execution, an SQL-Function exists in the database that implements the
     * learned SVM model. 
     *
     * @param inputColumnset The ColumnSet in the database that belongs to the 
     *        input Concept of the operator that uses this wrapper. The Columns that the SVM learns
     *        from belong to this ColumnSet.
     * @param targetColumn the Column in the database that
     *        belongs to the target attribute of the operator that uses this
     *        wrapper.
     * @param conceptId Unique M4 Id of the input Concept of the operator that
     *        uses this wrapper.
     * @param c The parameter C for the support vector machine.
     * @param kernelType One of a few possible kernel types, depending on the 
     * 		  actual SVM algorithm used.
     * @param epsilon The parameter epsilon for the support vector machine.
     * @param thePredictingColumns A Vector containing the Columns in the database that the support
     *        vector machine will use as learning attributes.
     * 
     * @throws M4CompilerError A simple exception object with an error message.
     */
    public abstract void callSVM( Columnset inputColumnset,
			             	  	  Column targetColumn,
			 	                  long conceptId,
	                              String c,
			             		  String kernelType,
			             		  String epsilon,
			             		  Vector thePredictingColumns
			                    ) 
			                    throws M4CompilerError;
    
    /**
     * This method returns the XiAlpha-Estimation that was computed
     * by the SVM algorithm during training. Should not be called 
     * before calling <code>callSVM()</code>.
     * 
     * @return the XiAlpha value
     */
    public double getXiAlphaEstimation()
    {
    	return this.xiAlphaEstimation;
    }
    /**
     * This method returns the number of support vectors computed
     * by the SVM algorithm during training. Should not be called 
     * before calling <code>callSVM()</code>.
     * 
     * @return the number of support vectors
     */
    public int getNumberOfSupportVectors()
    {
    	return this.noSV;
    }
    
    /**
     * Must be implemented by subclasses. Extracts the XiAlpha estimation
     * from the output of the SVM algorithm so that the public method
     * <code>getXiAlphaEstimation()</code> can return the right value.
     */
    protected abstract void extractXiAlpha() throws M4CompilerError;
    
    /**
     * Must be implemented by subclasses. Extracts the number of support vectors
     * from the output of the SVM algorithm so that the public method
     * <code>getNumberOfSupportVectors()</code> can return the right value.
     */
    protected abstract void extractNumberSV() throws M4CompilerError;
    
    // This method reads the metadata about the columns to be used for learning,
    // and initialises ColumnInfo objects about each column 
    // (stored in this.theColumnInfos).
    protected void checkForConversion( Columnset inputCS,
    								   Vector predictingColumns,
                                       Column targetColumn) 
                       throws M4CompilerError
    {    	
		if (predictingColumns.isEmpty())
		{  throw new M4CompilerError("SVM wrapper: set of attributes to predict from is empty!");  }

        String conceptualType, columnType;    	   
        int numberOfLearnAttribs = predictingColumns.size();
        // this.inputTableName = inputCS.getSQLDefinition();
        this.inputTableName = inputCS.getSchemaPlusName();
        
        // we want to check ALL columns for conversion; so make an array
        // that includes all:
        this.theColumnInfos = new ColumnInfo[numberOfLearnAttribs + 1];
        for (int i = 0; i < predictingColumns.size(); i++) {
        	Column theCol = (Column) predictingColumns.get(i);
        	this.theColumnInfos[i] = new ColumnInfo(theCol, this.missingValuesOccur(theCol));  
        }
        this.theColumnInfos[numberOfLearnAttribs] = new ColumnInfo(targetColumn, this.missingValuesOccur(targetColumn));
        
        // the following Vector will indicate for each column whether it is
        // binary conceptually but string technically, and thus needs to be
        // converted:
        // convertThisColumn.setSize(numberOfLearnAttribs + 1); // +1 for target column
        
        // the following Vector will contain the database-specific datatype of each column
        // this.implementationalColumnTypes = new Vector(numberOfLearnAttribs + 1);
        
        //  String[] stringsToSelectColumns = new String[numberOfLearnAttribs + 1];
        // this.symbolsForBooleanValues = new String[2][numberOfLearnAttribs + 1];        
        // initialise the vectors and arrays:
/*        for (int i = 0; i < numberOfLearnAttribs + 1; i++)
	    {   convertThisColumn.set(i, null);
            implementationalColumnTypes.add(null);
            stringsToSelectColumns[i] = null;
            symbolsForBooleanValues[0][i] = "-1";
            symbolsForBooleanValues[1][i] = "1";
	    }
        convertThisColumn.trimToSize();
        implementationalColumnTypes.trimToSize();*/
        
        // check the input columnset:
		this.checkColSetType(inputCS.getType());
		
        // check the target column:
        this.targetColSQLDefinition = targetColumn.getSQLDefinition();
        try {
	        
	        if (forClassification && ( ! targetColumn.getTheBaseAttribute().getConceptualDataTypeName().equalsIgnoreCase("BINARY")))
			{   throw new M4CompilerError("SVM wrapper: Error with target attribute: must be binary for SVM_CL!");  }
		
			// check all columns for datatype conversion:
			for (int i = 0; i < theColumnInfos.length; i++) {
				
		    	conceptualType = theColumnInfos[i].getColumn().getTheBaseAttribute().getConceptualDataTypeName();
				columnType = theColumnInfos[i].getColumn().getColumnDataTypeName();
				
				// check compatibility of conceptual and relational datatypes:
				if (conceptualType.equals(ConceptualDatatypes.CDT_SCALAR) || 
					conceptualType.equals(ConceptualDatatypes.CDT_TIME) ||
		            conceptualType.equals(ConceptualDatatypes.CDT_NUMERIC)) {
					
					if (columnType.equals(RelationalDatatypes.RELATIONAL_DATATYPE_DATE)) {   
				    	if (this.useOracle)	{ 
				    		// we can use the column name in this statement because sampling was applied before:
				    		this.theColumnInfos[i].setSelectString("to_number(to_char(" + this.theColumnInfos[i].getColumnName() + ", 'J'))");  
				        }
				    	if (this.usePostgres) {  
				    		// temporary solution:
				    		throw new M4CompilerError("SVM Wrapper: cannot handle columns of type DATE under Postgres;" +
				    		                          " found column '" + this.theColumnInfos[i].getColumnName() + "' of type DATE!");
				    	}
				    }
					else {  
						this.theColumnInfos[i].setSelectString(this.theColumnInfos[i].getColumnName());  
					}
		
					// convertThisColumn.set(i, null); // no conversion in this column
					this.theColumnInfos[i].setDatatype(this.getDBMS_Datatype(columnType, this.theColumnInfos[i].getColumnName()));
			    }
				else {
					if (conceptualType.equals(ConceptualDatatypes.CDT_BINARY) ||				
					    conceptualType.equals(ConceptualDatatypes.CDT_NOMINAL)) {
			    
						if (columnType.equalsIgnoreCase(RelationalDatatypes.RELATIONAL_DATATYPE_STRING)) {   
							this.storeValueMappings(this.theColumnInfos[i]);  
						}
						this.theColumnInfos[i].setSelectString(this.theColumnInfos[i].getColumnName());
						this.theColumnInfos[i].setDatatype(this.getDBMS_Datatype(columnType, this.theColumnInfos[i].getColumnName()));
					}
				}
	    	} // end loop through all columns
        }
   		catch (M4Exception m4e) {   
   			throw new M4CompilerError("M4 interface error in SVM Wrapper: " + m4e.getMessage());  
   		}   		
    } // end protected String[] checkForConversion    
    
    private void storeValueMappings(ColumnInfo ci) throws M4CompilerError {
    	try {
    		String[] vs = this.getDatabaseObj().getDistinctElements(ci.getColumn().getId());
    		boolean thisIsTheTargetColumn = ci.getColumn().getSQLDefinition().equalsIgnoreCase(this.targetColSQLDefinition);
    		// check that the target is binary for classification:
    		if (thisIsTheTargetColumn && this.forClassification && (vs.length != 2)) {
    			throw new M4CompilerError("SVM_Wrapper: Classification SVM needs a binary target; found " +
    					                  vs.length + " distinct values in target column '" +
    					                  this.targetColSQLDefinition + "'!");
    		}
    		boolean positiveTargetValueFound = false;
    		for (int i = 0; i < vs.length; i++) {
    			ci.setValueMapping(vs[i], "" + i);
    			if (thisIsTheTargetColumn &&
    			    this.forClassification &&
                    vs[i].equalsIgnoreCase(this.getPositiveTargetValue())) {
    				positiveTargetValueFound = true;
    			}
    		}
    		if (thisIsTheTargetColumn && 
    			this.forClassification && 
				( ! positiveTargetValueFound)) {
    			throw new M4CompilerError("SVM_Wrapper: the positive target value '" +
    					                  this.getPositiveTargetValue() + 
										  "' was not found in the target column '" +
										  this.targetColSQLDefinition + "'!");
    		}
      	}
    	catch (M4CompilerError mce) {
    		throw new M4CompilerError("SVM_Wrapper: could not set mapped values for column '" +
    				ci.getColumnName() + "': " + mce.getMessage());
    	}
    }
    
    private boolean missingValuesOccur(Column col) throws M4CompilerError {
    	String sql = "SELECT COUNT(*) FROM " + this.inputTableName + " WHERE " + 
					 col.getSQLDefinition() + " IS NULL";
    	try {
    		Long l = this.getDatabaseObj().executeBusinessSingleValueSqlReadL(sql);
    		return (l.longValue() > 0);
    	}
    	catch (SQLException sqle) {
    		throw new M4CompilerError("SVM_Wrapper: Sql exception caught when trying to find out if column '" +
    				col.getSQLDefinition() + "' in table '" + this.inputTableName + "' has NULL values: " +
					sqle.getMessage());
    	}
    	catch (DbConnectionClosed db) {
    		throw new M4CompilerError("Error trying to find out if column '" +
    				col.getName() + "' in table '" + this.inputTableName + "' has NULL values: " +
					"connection to DB was closed: " + db.getMessage());
    	}
    }
    
    protected String getCompleteSelectString() {
    	if (this.theColumnInfos == null) {
    		return null;
    	}
    	String select = "";
    	for (int i = 0; i < this.theColumnInfos.length; i++) {
			select += this.theColumnInfos[i].getSelectString() + ", ";
		}
    	select = select.substring(0, select.length() - 2);
    	return select;
    }
    
    // returns the DBMS-specific datatype that is associated 
    // with the given M4 relational column datatype
    protected String getDBMS_Datatype(String m4ColumnDatatype, String columnName) throws M4CompilerError
    {
    	if (m4ColumnDatatype.equals(RelationalDatatypes.RELATIONAL_DATATYPE_DATE)
    	    &&
    		this.usePostgres)
    	{   
    			// temporary solution, since Postgres uses different formats
    			// for date and time of day:
    			throw new M4CompilerError("SVM_Wrapper.java#getDBMS_Datatype: " +
    		  	                          "can't support M4 column datatype 'DATE' under Postgres! Column: " + columnName);   
    	}
    	
    	if (m4ColumnDatatype.equals(RelationalDatatypes.RELATIONAL_DATATYPE_KEY)) {  
    		// don't know what to do!
    		throw new M4CompilerError("SVM_Wrapper.java#getDBMS_Datatype(): found M4 column datatype 'KEY' " +
    		                          "for column '" + columnName + "'. Not supported.");
    	}
    	
    	boolean useBusinessDBMS = true;
    	String ret = this.getDatabaseObj().getDbNameOfM4Datatype(m4ColumnDatatype, 0, useBusinessDBMS);
    	
    	if (ret == null) {
    		throw new M4CompilerError("SVM_Wrapper: found unknown M4 column datatype '" + m4ColumnDatatype + 
    		                          "' for column '" + columnName);  
    	}
    	else {
    		return ret;
    	}
    } // end private String getDBMS_Datatype
    
    // This method reads a sample of the specified maximum sample size from the business data.
    protected String getSampleRatio(long maxSample, String tableName) throws M4CompilerError
    {
        if (maxSample <= 0)
	    {   throw new M4CompilerError("Wrapper for SupportVectorMachine: Sample Size must be positive! Found: " + maxSample + ".");  }

        String sql_getNumberOfRecords = "SELECT count(*) " +
                                        "FROM " + tableName +
                                        " WHERE " + this.targetColSQLDefinition + " is not null";
        long count = 0;
        try
        {
            Long countL = this.getDatabaseObj().executeBusinessSingleValueSqlReadL(sql_getNumberOfRecords);
            if (countL != null) {
            	count = countL.longValue();
            }
            else throw new M4CompilerError("Wrapper for SVM: Could not read result from query: " + sql_getNumberOfRecords);
        }
        catch (SQLException sqle)
        {  throw new M4CompilerError("Wrapper for SVM: Could not read result from query: " + sql_getNumberOfRecords +
                                     ", Database problem: " + sqle.getMessage());
        }
        if (maxSample > count)
	    {   maxSample = count;  }

        double sampleRatio = (double) maxSample / (double) count;
        // new M4RandomSelect function:
        sampleRatio = sampleRatio * 1000000;

        String integerSampleRatio = Double.toString(sampleRatio);
        if (integerSampleRatio.indexOf(".") > -1)
        {  integerSampleRatio = integerSampleRatio.substring(0, integerSampleRatio.indexOf("."));  }

        return integerSampleRatio;
    } // end protected String getSampleRatio
    
	/*
     * Enters an SQL-Function that implements the decision function of mySVM
     * into the database. The last parameter indicates for each attribute
     * whether it had to be converted from Binary/String into
     * Binary/Number (-1/1) for mySVM. If an entry is not null, it
     * contains the indexed attribute's column-ID and conversion must be done
     * using the symbolForBooleanValues-map (global variable).
     * For null entries no conversion is needed.
     */
    protected void createDecisionFunctionAsSQL_Function(String kernelType) 
                   throws M4CompilerError
    {
        String theFunction = createDecisionFunctionTemplate();

        String declaration = createDeclaration(kernelType);

        if (declaration == null)
	    {   throw new M4CompilerError("Error: null-declaration in SQL decision function; check kernel type!");  }

        String body = createBody(kernelType);

        if (body == null)
	    {   throw new M4CompilerError("Error trying to create body of SQL decision function; check kernel type!");   }

        theFunction = replace(theFunction, "$DECLARATION", declaration);
        theFunction = replace(theFunction, "$BODY", body);

        insertFunctionIntoDB(theFunction);
    } // end protected void createDecisionFunctionAsSQL_Function    

    protected String createDeclaration( String kernelType) 
              throws M4CompilerError
    {
		// depending on the input form, get the input column names of the svm:
		Vector colNamesInput = this.getColNamesInput();

        String declaration = "";
        if (this.useOracle)
        {
	        // need a cursor to go through the table of support vectors:
    	    declaration += "    CURSOR supportvectors IS\n      SELECT ";
        	for (int i = 0; i < colNamesInput.size(); i++)
		    {
				declaration += ((String) colNamesInput.get(i)) + ", ";
		    }
    	    declaration += "Alpha FROM " + this.getModelTablePlusCondition() + ";\n";     
            declaration += "    currentrow supportvectors%ROWTYPE;\n";
        }
        if (this.usePostgres)
        {
        	// the input arguments of the function are aliased here,
        	// so that they can be used in the same way as for Oracle in the function body.
        	// Use original column names prefixed with 'X_' for internal use in the function.
        	for (int i = 1; i <= colNamesInput.size(); i++) {
				declaration += "    X_" + this.theColumnInfos[i-1].getColumnName() + " ALIAS FOR $" + i + ";\n";
		    }
        	
        	declaration += "    currentrow " + this.getModelTableName() + "%ROWTYPE;\n";
        }
        String numberTypeName = this.getDBMS_Datatype(RelationalDatatypes.RELATIONAL_DATATYPE_NUMBER, null);
        declaration += "    kernel " + numberTypeName + ";\n";
		declaration += "    inner " + numberTypeName + ";\n";
		
		return declaration;
    } // end protected String createDeclaration

    protected String createBody( String kernelType) 
                     throws M4CompilerError
    {
        String body = null;
        String param1 = null;
        String param2 = null;
        int dimension = this.theColumnInfos.length - 1;

        // learn and check mySVM kernel parameters:
        if (
	    	(kernelType.equalsIgnoreCase("dot") && (this.theKernelParams != null))
	    	||
	    	(
	     	  (kernelType.equalsIgnoreCase("polynomial") || kernelType.equalsIgnoreCase("radial"))
	     	  &&
	     	  (this.theKernelParams.size() != 1)
            )
	    	||
	    	(
	     	  (kernelType.equalsIgnoreCase("neural") || kernelType.equalsIgnoreCase("anova"))
	     	  &&
	     	  (this.theKernelParams.size() != 2)
            )
	       )
	    {   throw new M4CompilerError("SVM Wrapper error: Found wrong number of kernel parameters!");
	    }
        if ( ! kernelType.equalsIgnoreCase("dot"))
	    {
			String params = (String) this.theKernelParams.get(0);
			StringTokenizer st = new StringTokenizer(params);
			if ( ! st.hasMoreTokens())
		    {   throw new M4CompilerError("Error: wrong parameter declaration for mySVM.");		    }
			params  = st.nextToken();  // dummy
			if ( ! st.hasMoreTokens())
		    {   throw new M4CompilerError("Error: wrong parameter declaration for mySVM.");}
			param1 = st.nextToken();
			try
		    {   Double.parseDouble(param1);  }
			catch (NumberFormatException nfe)
		    {   throw new M4CompilerError("Error: wrong parameter declaration for mySVM.");}
		    
			if (kernelType.equalsIgnoreCase("neural") || kernelType.equalsIgnoreCase("anova"))
		    {
				params = (String) this.theKernelParams.get(1);
				st = new StringTokenizer(params);
				if ( ! st.hasMoreTokens())
			    {   throw new M4CompilerError("Error: wrong parameter declaration for mySVM."); }
				params = st.nextToken();  // dummy
				if ( ! st.hasMoreTokens())
			    {   throw new M4CompilerError("Error: wrong parameter declaration for mySVM.");   }
				param2 = st.nextToken();
				try
			    {   Double.parseDouble(param2);  }
				catch (NumberFormatException nfe)
			    {   throw new M4CompilerError("Error: wrong parameter declaration for mySVM.");    }
		    }
	    } // end if (kernel not linear)

		String operator = null;
		
		// depending on the input form, get the input column names of the svm:
		Vector colNamesInput = getColNamesInput();
		
        // body for linear, polynomial or neural kernel function
        if ( kernelType.equalsIgnoreCase("polynomial") || 
             kernelType.equalsIgnoreCase("neural") ||
             kernelType.equalsIgnoreCase("dot"))
	    {  	operator = " * ";   }
	    else
	    {   operator = " - ";   }
	    	
		body = "    retValue := 0;\n";
		if (this.useOracle)
		{
			body += "    FOR currentrow IN supportvectors\n    LOOP\n";
		}
		if (this.usePostgres)
		{
			body += "    FOR currentrow IN\n";
			body += "        SELECT ";
        	for (int i = 0; i < colNamesInput.size(); i++)
		    {
				body += ((String) colNamesInput.get(i)) + ", ";
		    }
    	    body += "Alpha\n";  
			body += "        FROM " + this.getModelTablePlusCondition() + "\n";
			body += "    LOOP\n";
		}
		body += "      inner := ";

		// collect arguments:
		String component = null;
		for (int i = 0; i < dimension; i++)
		{
			if (this.theColumnInfos[i].mappingIsUsed())
			{
				component = "(currentrow." + ((String) colNamesInput.get(i)) + operator + "help" + i + ")";
			}
			else
			{
				// if the database level datatype is DATE, the DBMS we are using can't be postgres 
				// (see getDBMS_Datatype())
				if (this.theColumnInfos[i].getDatatype().equals(DbCoreOracle.ORACLE_TYPE_DATE)) 
				{  component = "(currentrow." + ((String) colNamesInput.get(i)) + operator + "help" + i + i + ")";  }
				else
				{  
        	        // Use original column names prefixed with 'X_' for internal use in the function.
					component = "(currentrow." + ((String) colNamesInput.get(i)) + operator +
						   "X_" + this.theColumnInfos[i].getColumnName() + ")"; 
				}
			}
			if (kernelType.equalsIgnoreCase("radial"))
			{   body += component + " * " + component;  }
			else if (kernelType.equalsIgnoreCase("anova"))
				 {   body += "EXP( -" + param1 + " * " + component + ")";  }
				 else
				 {   body += component;   }
			if (i < dimension - 1)
		    {  body += "\n               + ";  }
		} // end collection of arguments
		
		if (kernelType.equalsIgnoreCase("polynomial"))
		{
			body += ";\n      kernel := POWER(inner + 1, " + param1 + ") ";
		}
		if (kernelType.equalsIgnoreCase("neural"))
	    {
			body += ";\n      kernel := TANH(inner * " + param1 + " + " + param2 + ") ";
	    }
	    if (kernelType.equalsIgnoreCase("dot"))
	    {   
	    	body += ";\n      kernel := inner ";  
	    }
		if (kernelType.equalsIgnoreCase("radial"))
		{	
			body += ";\n      kernel := EXP( -(" + param1 + ") * inner) ";  
		}
		if (kernelType.equalsIgnoreCase("anova"))
		{   
			body += ";\n      kernel := POWER(inner, " + param2 + ") ";  
		}
	    
	    // body += "* currentrow.Alpha + (" + this.b + ");\n";
	    body += "* currentrow.Alpha;\n";
		    
		body += "      retValue := retValue + kernel;\n";
		body += "    END LOOP;\n";

		body += "    retValue := retValue + (" + this.b + ");\n";

        return body;
    } // end protected String createBody

    protected void insertFunctionIntoDB(String theFunction) 
    			   throws M4CompilerError
    {
        try
	    {
	    	final CompilerDatabaseService db = this.getDatabaseObj();
	    	if (false) // debugging
	    	{
	    		this.pr.doPrint( Print.COMPILER_STEP_CONTROL, 
	    		                 "\n" + this.getClass().getName() + ": Adding the following function" + 
	    		                 " to the business data:\n" + theFunction);	
	    	}
		    db.executeBusinessSqlWrite(theFunction);
		    db.commitBusinessTransactions();
		    String decisionFunction = null;
		    if (this.useOracle) {
		    	decisionFunction = this.getDecisionFunctionName();
		    }
		    if (this.usePostgres) {	
		    	decisionFunction = this.getDecisionFunctionNameWithArgumentTypes();
		    }
	        db.addFunctionToTrash(decisionFunction, this.schema, this.myStepId);
	    }
        catch (SQLException sqle)
	    {   throw new M4CompilerError("SVM wrapper: Error trying to insert SQL result function into database: " + sqle.getMessage()); }
    } // end protected void insertFunctionIntoDB

    /*
     * Replaces in the first given string all occurrences of the second given string
     * by the third given string.
     *
     * @param theString The String to operate on.
     * @param searchFor The String to be replaced.
     * @param replaceBy The String to replace with.
     * @return The first String with the replacements done.
     */
    protected String replace(String theString, String searchFor, String replaceBy)
    {
        int i = theString.indexOf(searchFor);
        String newString = "";
        int b = 0;

        while (i > -1)
	    {
		newString += theString.substring(b, i);
		newString += replaceBy;
		b = i + searchFor.length();
		i = theString.indexOf(searchFor, b);
	    }

        if (b < theString.length())
	    {   newString += theString.substring(b);  }

        return newString;
    } // end protected String replace
    
    // This method creates a template that is common for all SQL decision functions
    // that this SVM-wrapper produces. In the template, the string "$DECLARATION"
    // has to be replaced by an empty string or a valid variable declaration.
    // The string "$BODY" has to be replaced by the function body. The body
    // must assign the final computed value to the variable "retValue" (which is
    // already declared in this template), whose sign will be the return value
    // of the function (as included in this template).
    protected String createDecisionFunctionTemplate() 
                     throws M4CompilerError
    {
    	// dimension is the number of predicting attributes:
        int dimension = this.theColumnInfos.length - 1;
        
        /*
        // check (for safety) if things are the way they should be:
        if ((columnsToConvert.size() != colNamesPredictingAttribs.size() + 1)
            ||
            (columnsToConvert.size() != implementationalColumnTypes.size()))
	    {   throw new M4CompilerError("Error: could not create decision function in SQL: parameter mismatch.");   }
	    */
        
        // Function header with input parameters:
        String theFunction = "CREATE OR REPLACE function ";
        if (this.useOracle)
        {   theFunction += this.getDecisionFunctionName();  }
        if (this.usePostgres)
        {   theFunction += this.getDecisionFunctionNameWithSchema();  }
        theFunction += "\n(\n";
        
        for (int i = 0; i < dimension; i++)
	    {
	    	if (this.useOracle) {
        	    // Use original column names prefixed with 'X_' for internal use in the function.
				theFunction += "X_" + this.theColumnInfos[i].getColumnName() + " IN " +
			    			   this.theColumnInfos[i].getDatatype();
	    	}
	    	if (this.usePostgres) {	
	    		// For Postgres, only the datatypes occur in the function head.
	    		// Aliases are declared in the declaration section such that the use 
	    		// of the function's arguments in the body is the same as for Oracle.
	    		theFunction += this.theColumnInfos[i].getDatatype();  
	    	}
	    	
			if (i < (dimension - 1)) {  theFunction += ",\n";  }
	    }
        // return value is always numeric (-1 or 1):
        if (this.useOracle)
        {   theFunction += "\n)\nRETURN NUMBER\nAS\nBEGIN\n";   }
        if (this.usePostgres)
        {   theFunction += "\n)\nRETURNS NUMERIC\nAS '\n";  } // important: single quote!
        	
		String numbertype = this.getDBMS_Datatype(RelationalDatatypes.RELATIONAL_DATATYPE_NUMBER, null);
		
		theFunction += "  DECLARE\n";
        theFunction += "    retValue " + numbertype + ";\n";
        for (int i = 0; i < dimension; i++)
	    {
			if (this.theColumnInfos[i].mappingIsUsed()) {   
				theFunction += "    help" + i + " " + numbertype + ";\n";  
			}
			if (this.theColumnInfos[i].getDatatype().equals(DbCoreOracle.ORACLE_TYPE_DATE))
		    {   theFunction += "    help" + i + i + " " + numbertype + ";\n";  }
	    }
        // insert placeholder for further declarations:
        theFunction += "$DECLARATION\n";

        theFunction += "  BEGIN\n";
        
        // initialize variables for conversion of attributes:
        for (int i = 0; i < dimension; i++) {
			if (this.theColumnInfos[i].mappingIsUsed()) {
				Iterator it = this.theColumnInfos[i].getValuesIterator();
				if (it.hasNext()) {
					String value = (String) it.next();
					theFunction += "    IF X_" + this.theColumnInfos[i].getColumnName() + " = '" +
					               value + "'\n" +
								   "      THEN help" + i + " := " +
								   this.theColumnInfos[i].getMappedValue(value) + ";\n";
				}
				while (it.hasNext()) {
					String value = (String) it.next();
					theFunction += "    ELSIF X_" + this.theColumnInfos[i].getColumnName() + " = '" +
					               value + "'\n" +
								   "      THEN help" + i + " := " + 
								   this.theColumnInfos[i].getMappedValue(value) + ";\n";
					               
				}
				theFunction += "    END IF;\n";
				/*
		    	theFunction += "    IF X_" + ((String) colNamesPredictingAttribs.get(i)) + " = '" + 
		    	               symbolsForBooleanValues[0][i] + "'\n" + 
		    	               "      THEN help" + i + " := -1;\n";
		    	theFunction += "    ELSIF X_" + ((String) colNamesPredictingAttribs.get(i)) + " = '" +
		    	               symbolsForBooleanValues[1][i] + "'\n" +
							   "      THEN help" + i + " := 1;\n    END IF;\n";
				*/
		    }
			if (this.theColumnInfos[i].getDatatype().equals(DbCoreOracle.ORACLE_TYPE_DATE)) {   
		    	// postgres does not have functions 'to_number' or 'to_char',
		    	// but an exception must have been thrown earlier, in the method
		    	// 'checkForConversion'.
		    	theFunction += "    help" + i + i + " := to_number(to_char(X_" +
			    this.theColumnInfos[i].getColumnName() + ", 'J'));\n";
		    }
	    }

        // insert placeholder for function body, which computes the return value 'retValue':
        theFunction += "\n$BODY\n";

        if (this.forClassification)
        {  theFunction += "    IF (retValue > 0)\n      THEN RETURN 1;\n    END IF;\n    RETURN -1;\n";  }
        else
        {  theFunction += "    RETURN retValue;\n";  }

        theFunction += "  END;\n"; // end body
        if (this.useOracle)
        {
	        theFunction += "END;\n"; // end function (declaration + body)
        }
		if (this.usePostgres)
		{
			// in postgres PL/Sql, only the body has a BEGIN;
			// so we need no second END but other funny stuff
			theFunction += "' LANGUAGE 'plpgsql';";	 // ending the single quote!
		}
        return theFunction;
    } // end protected String createDecisionFunctionTemplate


    /* Tests if the given String contains a double, and changes funny oracle-formats
     * to java convention. 
     */
    protected String checkDouble(String test) throws M4CompilerError
    {
        int a;
        if ((a = test.indexOf(",")) > -1)
	    {
		String pre = test.substring(0, a);
		String post = test.substring(a + 1);
		test = pre + "." + post;
	    }
        if (test.startsWith("."))
	    {  test = "0" + test;  }

        try
	    {   Double.parseDouble(test);  }
        catch (NumberFormatException nfe)
	    {   throw new M4CompilerError("SVM wrapper: Error trying to convert '" + test + "' into double.");   }
        return test;
    } // end private String checkDouble

    protected void setPositiveTargetValue(String posValue) 
    {   
    	// this makes sense only when "this.forClassification" is TRUE,
    	// but to throw an exception is unnecessary
    	this.targetPositive = posValue;   
    }  
    
    protected String getPositiveTargetValue()
    {   return this.targetPositive;  }

    /* Tests if the given String is an allowed kernel type for this SVM. 
     */
    protected abstract void checkKernel(String kern) throws M4CompilerError;
    
    /* Get the query (after "from") to read from the model table
     */
    protected abstract String getModelTablePlusCondition() throws M4CompilerError;
    
    /* if a special input view was constructed, the column names may be different.
     */
    protected abstract Vector getColNamesInput();
    
    /* When input concept is a view, there may have to be checks
     */
    protected abstract void checkColSetType(String type) throws M4CompilerError;

	/**
	 * Gets the databaseObj.
	 * @return Returns a DB
	 */
	protected CompilerDatabaseService getDatabaseObj() {
		return this.databaseObj;
	}
	
    protected String getParTableName()
    {   return this.dbPrefix + "_PAR";  }
    
    protected String getLogTableName()
    {   return this.dbPrefix + "_LOG";  }
    
    protected String getOutputViewName()
    {   return this.dbPrefix + "_OUT";  }
    
    protected String getInputViewName()
    {   return this.dbPrefix + "_IN";  }
    
    protected String getModelTableName()
    {   return this.dbPrefix + "_MOD";  }
    
    /**
     * Returns the name of the model function that the SVM
     * installed in the database (business schema).
     * 
     * @return name of the SVM function
     */
    public String getDecisionFunctionName()
    {   return this.dbPrefix + "_F";  }
    
    /**
     * Returns the name of the decision function that the SVM
     * installed in the database (business schema) together with
     * the function's argument types. This is needed for the entry
     * in the trash table for postgres.
     * 
     * @return name(type1, type2, ..., typeN)
     */
    protected String getDecisionFunctionNameWithArgumentTypes()
    {   
    	String theName = this.getDecisionFunctionName() + "(";
    	for (int i = 0; i < this.theColumnInfos.length - 1; i++) 
    	// (minus 1 because the label is not an argument for the decision function)
    	{
    		theName += this.theColumnInfos[i].getColumnName()	+ ",";
    	}
    	theName = theName.substring(0, theName.length() - 1) + ")";    	
    	return theName;
    }    
    
    /**
     * Returns the name of the decision function that the SVM
     * installed in the database (business schema), prefixed
     * by the name of the schema in which it is installed.
     * 
     * @return schema.name
     */
    public String getDecisionFunctionNameWithSchema() throws M4CompilerError {
    	if ((this.schema == null) || this.schema.equals("")) {
    		throw new M4CompilerError("SVM Wrapper: Postgres needs the fully qualified Function name, but schema is not available!");
    	}
    	return this.schema + "." + this.getDecisionFunctionName();  	
    }
    
    protected Print getPrint() {
    	return this.pr;	
    }
    
    protected Vector getKernelParams(String kern)
    {    	
		this.theKernelParams = new Vector();
        if (kern.toLowerCase().equals("dot"))
        {   
        	this.theKernelParams = null;
        	return null;   
        }
        if (kern.toLowerCase().equals("polynomial"))
        {
            theKernelParams.add("degree 2");
        }
        if (kern.toLowerCase().equals("neural"))
        {
            theKernelParams.add("a 0.5");
            theKernelParams.add("b 0.5");
        }
        if (kern.toLowerCase().equals("radial"))
        {
            theKernelParams.add("gamma 0.1");
        }
        if (kern.toLowerCase().equals("anova"))
        {
            theKernelParams.add("gamma 0.1");
            theKernelParams.add("degree 2");
        }
        theKernelParams.trimToSize();
        
        return theKernelParams;
    } // end protected Vector getKernelParams()
}
/*
 * Historie
 * --------
 *
 * $Log: SVM_Wrapper.java,v $
 * Revision 1.4  2006/04/11 14:10:17  euler
 * Updated license text.
 *
 * Revision 1.3  2006/04/06 16:31:17  euler
 * Prepended license remark.
 *
 * Revision 1.2  2006/01/06 16:29:17  euler
 * Small bugfix
 *
 * Revision 1.1  2006/01/03 09:54:37  hakenjos
 * Initial version!
 *
 */
