package com.approximatrix.draco.io.sql;

import java.awt.Color;
import java.sql.*;
import java.util.Iterator;

import com.approximatrix.charting.model.ChartDataModel;
import com.approximatrix.charting.model.MultiScatterDataModel;
import com.approximatrix.charting.render.AbstractChartRenderer;
import com.approximatrix.charting.render.RowColorModel;
import com.approximatrix.charting.swing.AbstractChartPanel;
import com.approximatrix.draco.analysis.ui.AnalysisChildDialog;
import com.approximatrix.draco.datamodel.Constant;
import com.approximatrix.draco.datamodel.DateObject;
import com.approximatrix.draco.datamodel.StatTableModel;
import com.approximatrix.draco.fit.RunNLLS;
import com.approximatrix.draco.fit.RunTSLS;
import com.approximatrix.draco.fit.SimpleMLEFit;
import com.approximatrix.draco.fit.ui.FitChildDialog;
import com.approximatrix.draco.general.ui.InfoWindow;
import com.approximatrix.draco.plot.ui.PlotDialog;

public class DracoSQLWrite extends DracoSQL {
	
	private int keyCount = -1;
	
	public DracoSQLWrite(String filename) {
		super(filename);
		
		initialize();
	}
	
	private boolean initialize() {
		boolean ret = true;
		
		if(conn == null) return false;
		
		ret = ret && createParametersTable();
		ret = ret && createAnalysisDataTable();
		ret = ret && createMatrixDataTable();
		ret = ret && createPlotAxisDataTable();
		ret = ret && createPlotSeriesTable();
		ret = ret && createPlotDataTable();
		
		return ret;
	}

	private boolean createAnalysisDataTable() {
		try {
			dropTable(DracoSQLTables.ANALYSISDATA);	
		} catch(SQLException se) {
			//System.err.println("Error dropping db tables - probably fine");
			//se.printStackTrace();
		} 
		
		try {
			StringBuffer statPrepare = new StringBuffer("CREATE TABLE ");
			statPrepare.append(DracoSQLTables.ANALYSISDATA);
			statPrepare.append(" (");
			
			statPrepare.append(DracoSQLTables.AD_OWNER);
			statPrepare.append(" integer not null, ");
			
			statPrepare.append(DracoSQLTables.AD_POSITION);
			statPrepare.append(" integer, ");
			
			statPrepare.append(DracoSQLTables.AD_TYPE);
			statPrepare.append(" integer, ");
			
			statPrepare.append(DracoSQLTables.AD_VALUE);
			statPrepare.append(" real);");
			
			Statement stat = conn.createStatement();
			stat.execute(statPrepare.toString());
			
		} catch(SQLException se) {
			System.err.println("Error writing db");
			se.printStackTrace();
			return false;
		}
		return true;
	}
	
	private boolean createPlotSeriesTable() {
		try {
			dropTable(DracoSQLTables.PLOTSERIES);	
		} catch(SQLException se) {
			//System.err.println("Error dropping db tables - probably fine");
			//se.printStackTrace();
		} 
		
		try {
			StringBuffer statPrepare = new StringBuffer("CREATE TABLE ");
			statPrepare.append(DracoSQLTables.PLOTSERIES);
			statPrepare.append(" (");
			
			statPrepare.append(DracoSQLTables.PS_NAME);
			statPrepare.append(" text not null, ");
			
			statPrepare.append(DracoSQLTables.PS_OWNER);
			statPrepare.append(" integer not null, ");
			
			statPrepare.append(DracoSQLTables.PS_XDATA);
			statPrepare.append(" integer, ");
			
			statPrepare.append(DracoSQLTables.PS_YDATA);
			statPrepare.append(" integer, ");
			
			statPrepare.append(DracoSQLTables.PS_COLOR);
			statPrepare.append(" integer, ");
			
			statPrepare.append(DracoSQLTables.PS_MARKER);
			statPrepare.append(" integer, ");
			
			statPrepare.append(DracoSQLTables.PS_LINE);
			statPrepare.append(" integer);");
			
			Statement stat = conn.createStatement();
			stat.execute(statPrepare.toString());
			
		} catch(SQLException se) {
			System.err.println("Error writing db");
			se.printStackTrace();
			return false;
		}
		return true;
	}	
	
	private boolean createPlotDataTable() {
		try {
			dropTable(DracoSQLTables.PLOTDATA);	
		} catch(SQLException se) {
			//System.err.println("Error dropping db tables - probably fine");
			//se.printStackTrace();
		} 
		
		try {
			StringBuffer statPrepare = new StringBuffer("CREATE TABLE ");
			statPrepare.append(DracoSQLTables.PLOTDATA);
			statPrepare.append(" (");
			
			statPrepare.append(DracoSQLTables.PD_OWNER);
			statPrepare.append(" integer not null, ");
			
			statPrepare.append(DracoSQLTables.PD_POSITION);
			statPrepare.append(" integer not null, ");
			
			statPrepare.append(DracoSQLTables.PD_VALUE);
			statPrepare.append(" text); ");
					
			Statement stat = conn.createStatement();
			stat.execute(statPrepare.toString());
			
		} catch(SQLException se) {
			System.err.println("Error writing db");
			se.printStackTrace();
			return false;
		}
		return true;
	}
	
	private boolean createParametersTable() {
		try {
			dropTable(DracoSQLTables.PARAMETERS);	
		} catch(SQLException se) {
			//System.err.println("Error dropping db tables - probably fine");
			//se.printStackTrace();
		} 
		
		try {
			StringBuffer statPrepare = new StringBuffer("CREATE TABLE ");
			statPrepare.append(DracoSQLTables.PARAMETERS);
			statPrepare.append(" (");
			
			statPrepare.append(DracoSQLTables.PARAM_NAME);
			statPrepare.append(" text not null, ");
			
			statPrepare.append(DracoSQLTables.PARAM_OWNER);
			statPrepare.append(" integer not null, ");
			
			statPrepare.append(DracoSQLTables.PARAM_TYPE);
			statPrepare.append(" integer, ");
			
			statPrepare.append(DracoSQLTables.PARAM_VALUE);
			statPrepare.append(" real);");
			
			Statement stat = conn.createStatement();
			stat.execute(statPrepare.toString());
			
		} catch(SQLException se) {
			System.err.println("Error writing db");
			se.printStackTrace();
			return false;
		}
		return true;
	}
	
	private boolean createPlotAxisDataTable() {
		try {
			dropTable(DracoSQLTables.PLOTAXISDATA);	
		} catch(SQLException se) {
			//System.err.println("Error dropping db tables - probably fine");
			//se.printStackTrace();
		} 
		
		try {
			StringBuffer statPrepare = new StringBuffer("CREATE TABLE ");
			statPrepare.append(DracoSQLTables.PLOTAXISDATA);
			statPrepare.append(" (");
			
			statPrepare.append(DracoSQLTables.PAD_OWNER);
			statPrepare.append(" integer not null, ");
			
			statPrepare.append(DracoSQLTables.PAD_LABEL);
			statPrepare.append(" text, ");
			
			statPrepare.append(DracoSQLTables.PAD_TYPE);
			statPrepare.append(" integer not null, ");
			
			statPrepare.append(DracoSQLTables.PAD_GRID);
			statPrepare.append(" integer not null, ");
			
			statPrepare.append(DracoSQLTables.PAD_SCALETYPE);
			statPrepare.append(" integer not null, ");
			
			statPrepare.append(DracoSQLTables.PAD_SCALEMODE);
			statPrepare.append(" integer not null, ");
			
			statPrepare.append(DracoSQLTables.PAD_SCALEMIN);
			statPrepare.append(" real, ");
			
			statPrepare.append(DracoSQLTables.PAD_SCALEMAX);
			statPrepare.append(" real);");
			
			Statement stat = conn.createStatement();
			stat.execute(statPrepare.toString());
			
		} catch(SQLException se) {
			System.err.println("Error writing db");
			se.printStackTrace();
			return false;
		}
		
		return true;
	}
	
	private boolean createMatrixDataTable() {
		try {
			dropTable(DracoSQLTables.MATRIXDATA);	
		} catch(SQLException se) {
			//System.err.println("Error dropping db tables - probably fine");
			//se.printStackTrace();
		} 
		
		try {
			StringBuffer statPrepare = new StringBuffer("CREATE TABLE ");
			statPrepare.append(DracoSQLTables.MATRIXDATA);
			statPrepare.append(" (");
			
			statPrepare.append(DracoSQLTables.MD_OWNER);
			statPrepare.append(" integer not null, ");
			
			statPrepare.append(DracoSQLTables.MD_X);
			statPrepare.append(" integer not null, ");
			
			statPrepare.append(DracoSQLTables.MD_Y);
			statPrepare.append(" integer, ");

			statPrepare.append(DracoSQLTables.MD_TYPE);
			statPrepare.append(" integer, ");
			
			statPrepare.append(DracoSQLTables.MD_VALUE);
			statPrepare.append(" real);");
			
			Statement stat = conn.createStatement();
			stat.execute(statPrepare.toString());
			
		} catch(SQLException se) {
			System.err.println("Error writing db");
			se.printStackTrace();
			return false;
		}
		return true;
	}

	public boolean saveWorksheet(StatTableModel model) {
		if(conn == null) return false;
		
		Savepoint sp = setSavepoint();
		try {
			
			dropTable(DracoSQLTables.VARIABLES);
			dropTable(DracoSQLTables.DATA);
			
		} catch(SQLException se) {
			System.err.println("Error dropping db tables - probably fine");
			//se.printStackTrace();
		} 
		
		try {
			createAndFillVarTable(model);
			createAndFillDataTable(model);
		} catch(SQLException se) {
			rollback(sp);
			System.err.println("Error writing db");
			se.printStackTrace();
		} finally {
			releaseSavepoint(sp);
			try {
				if(!conn.getAutoCommit())
					conn.commit();
			} catch(SQLException se) {
				System.err.println("Commit failed in saveWorksheet");
				se.printStackTrace();
			}
		}
		
		return true;
	}
	
	public boolean saveConstants(StatTableModel model) {
		
		try {
			
			for(Constant value : model.getConstants())
				insertParameterValue(DracoSQLTables.OWNER_CONSTANTS,value.getName(),DracoSQLTables.TYPE_CONSTANT,value.getValue());
			
			
		} catch(SQLException se) {
			System.err.println("Error writing constants");
			//se.printStackTrace();
		} 
		
		return true;
	}
	
	public boolean saveTexts(Object[] textWindows) {
		if(conn == null) return false;
		
		try {
			dropTable(DracoSQLTables.TEXT);
		} catch(SQLException se) {
			System.err.println("Error dropping db tables - probably fine");
			//se.printStackTrace();
		} 
		
		if(textWindows == null) return true;
		if(textWindows.length == 0) return true;
		
		Savepoint sp = setSavepoint();
		
		try {

			// Create the texts table
			Statement stat = conn.createStatement();
			stat.execute("CREATE TABLE "+DracoSQLTables.TEXT+" ("+DracoSQLTables.TEXT_NAME+" text not null, "+DracoSQLTables.TEXT_CONTENT+" text, "+DracoSQLTables.TEXT_EDITABLE+" integer not null, "+DracoSQLTables.TEXT_CONTENTTYPE+" text);");
			
			for(int i=0;i<textWindows.length;i++) {
				if(!(textWindows[i] instanceof InfoWindow)) continue;
				
				InfoWindow window = (InfoWindow)textWindows[i];
				
				PreparedStatement pstat = conn.prepareStatement("INSERT INTO "+DracoSQLTables.TEXT+" VALUES(?, ?, ?, ?);");
				
				pstat.setString(1, window.getTitle());
				if(window.getText() == null)
					pstat.setNull(2, Types.VARCHAR);
				else
					pstat.setString(2, window.getText());
				
				if(window.isEditable())
					pstat.setInt(3, DracoSQLTables.FLAG_TRUE);
				else
					pstat.setInt(3, DracoSQLTables.FLAG_FALSE);
				
				pstat.setString(4,window.getContentType());
				
				pstat.execute();
			}
			
			
		} catch(SQLException se) {
			rollback(sp);
			System.err.println("Error writing db");
			se.printStackTrace();
		} finally {
			releaseSavepoint(sp);
			try {
				if(!conn.getAutoCommit())
					conn.commit();
			} catch(SQLException se) {
				System.err.println("Commit failed in saveWorksheet");
				se.printStackTrace();
			}
		}
		
		return true;
	}
	
	public boolean saveRegressions(Object[] aWindows) {
		if(conn == null) return false;
		
		try {
			dropTable(DracoSQLTables.REGRESSION);
		} catch(SQLException se) {
			System.err.println("Error dropping db tables - probably fine");
			//se.printStackTrace();
		} 
		
		if(aWindows == null) return true;
		if(aWindows.length == 0) return true;
		
		Savepoint sp = setSavepoint();
		
		try {

			// Create the regression table
			StringBuffer statPrepare = new StringBuffer("CREATE TABLE ");
			statPrepare.append(DracoSQLTables.REGRESSION);
			statPrepare.append(" (");
			
			statPrepare.append(DracoSQLTables.REGR_KEY);
			statPrepare.append(" integer not null unique, ");
			
			statPrepare.append(DracoSQLTables.REGR_NAME);
			statPrepare.append(" text not null, ");
			
			statPrepare.append(DracoSQLTables.REGR_RESULTS);
			statPrepare.append(" text, ");
			
			statPrepare.append(DracoSQLTables.REGR_TYPE);
			statPrepare.append(" text not null, ");
			
			statPrepare.append(DracoSQLTables.REGR_EQUATION);
			statPrepare.append(" text, ");
			
			statPrepare.append(DracoSQLTables.REGR_SUBTYPE);
			statPrepare.append(" text);");
			
			Statement stat = conn.createStatement();
			stat.execute(statPrepare.toString());
			
			for(int i=0;i<aWindows.length;i++) {
				if(!(aWindows[i] instanceof FitChildDialog)) continue;
				
				FitChildDialog window = (FitChildDialog)aWindows[i];
				int key = getKey();
				
				PreparedStatement pstat = conn.prepareStatement("INSERT INTO "+DracoSQLTables.REGRESSION+" VALUES(?, ?, ?, ?, ?, ?);");
				
				// Key
				pstat.setInt(1,key);
				
				// Name
				pstat.setString(2,window.getTitle());
				
				// Results
				if(window.getAnalysis().getResults() == null)
					pstat.setNull(3,Types.VARCHAR);
				else
					pstat.setString(3,window.getAnalysis().getResults());
				
				// Type
				String type = window.getAnalysis().getClass().getName();
				pstat.setString(4,type);
				
				// Equation
				if(window.getAnalysis() instanceof RunNLLS)
					pstat.setString(5,((RunNLLS)window.getAnalysis()).getEquation());
				else
					pstat.setNull(5, Types.VARCHAR);
				
				// Subtype
				if(window.getAnalysis() instanceof SimpleMLEFit)
					pstat.setString(6,((SimpleMLEFit)window.getAnalysis()).getFunction().getClass().getName());
				else
					pstat.setNull(6, Types.VARCHAR);
				
				
				pstat.execute();
				
				// Independent variables
				String[] indep = window.getAnalysis().getIndependentVariables();
				String dep = window.getAnalysis().getDependentVariable();
				String[] instr = null;
				if(window.getAnalysis() instanceof RunTSLS)
					instr = ((RunTSLS)window.getAnalysis()).getInstruments();
				if(indep != null) {
					for(int j=0;j<indep.length;j++)
						insertParameter(key,indep[j],DracoSQLTables.TYPE_INDEPENDENT);
				}
				insertParameter(key,dep,DracoSQLTables.TYPE_DEPENDENT);
				if(instr != null) {
					for(int j=0;j<instr.length;j++)
						insertParameter(key,instr[j],DracoSQLTables.TYPE_INSTRUMENT);
				}
				
				// Now save estimates and covariance
				for(int j=0;j<window.getAnalysis().getCoefLabels().length;j++) {
					insertParameterValue(key,window.getAnalysis().getCoefLabels()[j],DracoSQLTables.TYPE_ESTIMATE,window.getAnalysis().getCoef()[j]);
				}
				
				double[][] covariance = window.getAnalysis().getCovarianceMatrix();
				for(int j=0;j<covariance.length;j++) {
					for(int k=0;k<covariance[j].length;k++) {
						insertMatrixValue(key,j,k,DracoSQLTables.TYPE_COVARIANCE,covariance[j][k]);
					}
				}
				
			}
			
			
		} catch(SQLException se) {
			rollback(sp);
			System.err.println("Error writing db");
			se.printStackTrace();
		} finally {
			releaseSavepoint(sp);
			try {
				if(!conn.getAutoCommit())
					conn.commit();
			} catch(SQLException se) {
				System.err.println("Commit failed in saveWorksheet");
				se.printStackTrace();
			}
		}
		
		return true;
	}
	
	public boolean saveAnalyses(Object[] aWindows) {
		if(conn == null) return false;
		
		try {
			dropTable(DracoSQLTables.ANALYSIS);
		} catch(SQLException se) {
			System.err.println("Error dropping db tables - probably fine");
			//se.printStackTrace();
		} 
		
		if(aWindows == null) return true;
		if(aWindows.length == 0) return true;
		
		Savepoint sp = setSavepoint();
		
		try {

			// Create the regression table
			StringBuffer statPrepare = new StringBuffer("CREATE TABLE ");
			statPrepare.append(DracoSQLTables.ANALYSIS);
			statPrepare.append(" (");
			
			statPrepare.append(DracoSQLTables.AN_KEY);
			statPrepare.append(" integer not null unique, ");
			
			statPrepare.append(DracoSQLTables.AN_NAME);
			statPrepare.append(" text not null, ");
			
			statPrepare.append(DracoSQLTables.AN_RESULTS);
			statPrepare.append(" text, ");
			
			statPrepare.append(DracoSQLTables.AN_TYPE);
			statPrepare.append(" text not null);");
			
			Statement stat = conn.createStatement();
			stat.execute(statPrepare.toString());
			
			for(int i=0;i<aWindows.length;i++) {
				if(!(aWindows[i] instanceof AnalysisChildDialog)) continue;
				
				AnalysisChildDialog window = (AnalysisChildDialog)aWindows[i];
				int key = getKey();
				
				PreparedStatement pstat = conn.prepareStatement("INSERT INTO "+DracoSQLTables.ANALYSIS+" VALUES(?, ?, ?, ?);");
				
				// Key
				pstat.setInt(1,key);
				
				// Name
				pstat.setString(2,window.getTitle());
				
				// Results
				if(window.getAnalysis().getResults() == null)
					pstat.setNull(3, Types.VARCHAR);
				else
					pstat.setString(3,window.getAnalysis().getResults());
				
				// Type
				String type = window.getAnalysis().getClass().getName();
				pstat.setString(4,type);
				pstat.execute();
				
				// Now save estimates
				for(int j=0;j<window.getAnalysis().getVariables().length;j++) {
					insertParameter(key,window.getAnalysis().getVariables()[j],DracoSQLTables.TYPE_INDEPENDENT);
				}
				
			}
			
			
		} catch(SQLException se) {
			rollback(sp);
			System.err.println("Error writing db");
			se.printStackTrace();
		} finally {
			releaseSavepoint(sp);
			try {
				if(!conn.getAutoCommit())
					conn.commit();
			} catch(SQLException se) {
				System.err.println("Commit failed in saveWorksheet");
				se.printStackTrace();
			}
		}
		
		return true;
	}
	
	public boolean savePlots(Object[] aWindows) {
		if(conn == null) return false;
		
		try {
			dropTable(DracoSQLTables.PLOT);
		} catch(SQLException se) {
			System.err.println("Error dropping db tables - probably fine");
			//se.printStackTrace();
		}
		
		if(aWindows == null) return true;
		if(aWindows.length == 0) return true;
		
		boolean ret = true;
		
		Savepoint sp = setSavepoint(); 
		
		try {

			// Create the regression table
			StringBuffer statPrepare = new StringBuffer("CREATE TABLE ");
			statPrepare.append(DracoSQLTables.PLOT);
			statPrepare.append(" (");
			
			statPrepare.append(DracoSQLTables.PLOT_KEY);
			statPrepare.append(" integer not null unique, ");
			
			statPrepare.append(DracoSQLTables.PLOT_NAME);
			statPrepare.append(" text not null, ");
			
			statPrepare.append(DracoSQLTables.PLOT_TITLE);
			statPrepare.append(" text, ");
			
			statPrepare.append(DracoSQLTables.PLOT_TYPE);
			statPrepare.append(" text not null);");
			
			Statement stat = conn.createStatement();
			stat.execute(statPrepare.toString());
			
			for(int i=0;i<aWindows.length;i++) {
				if(!(aWindows[i] instanceof PlotDialog)) continue;
				
				PlotDialog window = (PlotDialog)aWindows[i];
				int key = getKey();
				
				PreparedStatement pstat = conn.prepareStatement("INSERT INTO "+DracoSQLTables.PLOT+" VALUES(?, ?, ?, ?);");
				
				AbstractChartPanel panel = window.getPlotter().getPlotPanel();
				
				// Key
				pstat.setInt(1,key);
				
				// Name
				pstat.setString(2,window.getTitle());
				
				// Results
				if(panel.getTitle() == null)
					pstat.setNull(3, Types.VARCHAR);
				else
					pstat.setString(3,panel.getTitle().getText());
				
				// Type
				String type = null;
			    RowColorModel rcm = null;
				for(Iterator<AbstractChartRenderer>iter = panel.getChartRenderer().values().iterator();iter.hasNext();) {
				    AbstractChartRenderer r = iter.next();
		        	type = r.getClass().getName();
		        	if(rcm == null) rcm = r.getRowColorModel();
		        }
				if(type == null) {
					ret = false;
					continue;
				} else
		        	pstat.setString(4,type);
				
				pstat.execute();
				
				int scaling = DracoSQLTables.TYPE_AXIS_SCALE_NONE;
				if(panel.getChartDataModel().isAutoScale())
					scaling = DracoSQLTables.TYPE_AXIS_SCALE_AUTO;
				else if(panel.getChartDataModel().isManualScale())
					scaling = DracoSQLTables.TYPE_AXIS_SCALE_MANUAL;
				
				// Save the axes
				insertPlotAxis(key, panel.getCoordSystem().getXAxisUnit(), 
						DracoSQLTables.TYPE_AXIS_X, 
						panel.getCoordSystem().getPaintGrid() ? DracoSQLTables.FLAG_TRUE : DracoSQLTables.FLAG_FALSE, 
						DracoSQLTables.TYPE_AXIS_LINEAR, 
						scaling, 
						panel.getChartDataModel().getManualMinimumX(), panel.getChartDataModel().getManualMaximumX());
				
				insertPlotAxis(key, panel.getCoordSystem().getYAxisUnit(),
						DracoSQLTables.TYPE_AXIS_Y, 
						panel.getCoordSystem().getPaintGrid() ? DracoSQLTables.FLAG_TRUE : DracoSQLTables.FLAG_FALSE, 
						DracoSQLTables.TYPE_AXIS_LINEAR, 
						scaling,
						panel.getChartDataModel().getManualMinimumY(), panel.getChartDataModel().getManualMaximumY());
				
				// Now save plot data
				ChartDataModel model = panel.getChartDataModel();
				for(int j=0;j<model.getDataSetNumber();j++) {
					insertSeries(key,model,rcm.getColor(j),j);
				}
			}
			
			
		} catch(SQLException se) {
			rollback(sp);
			System.err.println("Error writing db");
			se.printStackTrace();
		} finally {
			releaseSavepoint(sp);
			try {
				if(!conn.getAutoCommit())
					conn.commit();
			} catch(SQLException se) {
				System.err.println("Commit failed in saveWorksheet");
				se.printStackTrace();
			}
		}
		
		return ret;
	}
	
	private boolean createAndFillVarTable(StatTableModel model) throws SQLException {
		boolean ret = true;
		
		// Create the variables table
		Statement stat = conn.createStatement();
		stat.execute("CREATE TABLE "+DracoSQLTables.VARIABLES+" ("+DracoSQLTables.VAR_NAME+" text not null unique, "+ 
				                                                   DracoSQLTables.VAR_DESC+" text, "+
				                                                   DracoSQLTables.VAR_TYPE+" text, "+
				                                                   DracoSQLTables.VAR_UNIT+" text, "+
				                                                   DracoSQLTables.VAR_BASE+" text );");
		
		// Fill the table
		for(int i=0;i<model.getColumnCount() && ret;i++) {

			PreparedStatement varstat = conn.prepareStatement("INSERT INTO "+DracoSQLTables.VARIABLES+" VALUES(?, ?, ?, ?, ?);");
			varstat.setString(1, model.getColumnName(i));
			varstat.setString(3, model.getVariable(i).getContentsClass().getCanonicalName());
			
			if(model.getDescription(i) == null)
				varstat.setNull(2,Types.VARCHAR);
			else
				varstat.setString(2, model.getDescription(i));
			
			if(i == model.getDateColumn()) {
				varstat.setString(4, DateObject.getTypeString(model.getDateType()));
				varstat.setString(5, model.getDateBase().toString().replace('/', ':'));
			} else {
				varstat.setNull(4,Types.VARCHAR);
				varstat.setNull(5,Types.VARCHAR);
			}
			
			varstat.execute();
		}

		return ret;
	}
	
	private boolean createAndFillDataTable(StatTableModel model) throws SQLException {
		boolean ret = true;
		
		StringBuffer statementPrep = new StringBuffer("CREATE TABLE "+DracoSQLTables.DATA+" (");
		for(int i=0;i<model.getColumnCount() && ret;i++) {
			statementPrep.append(model.getColumnName(i));
			statementPrep.append(" text, ");
		}
		statementPrep.deleteCharAt(statementPrep.length()-1);
		statementPrep.deleteCharAt(statementPrep.length()-1);
		statementPrep.append(");");
		
		// Create the variables table
		Statement stat = conn.createStatement();
		stat.execute(statementPrep.toString());
		
		// Fill the table
		for(int i=0;i<model.getRowCount() && ret;i++) {
			
			StringBuffer insertStatement = new StringBuffer("INSERT INTO "+DracoSQLTables.DATA+" VALUES(");
			
			for(int j=0;j<model.getColumnCount();j++) {
				Object valueAt = model.getValueAt(i, j);
				if(valueAt != null) {
					
					// For dates, just save the integer representation of it.  We we can rebuild
					// this fine since the base is stored in the Variables table
					if(valueAt instanceof DateObject)
						insertStatement.append(Integer.toString(((DateObject)valueAt).intValue()));
					else 
						insertStatement.append(valueAt.toString().replace("/",":"));
				} else
					insertStatement.append("NULL");
				insertStatement.append(", ");
			}
			insertStatement.deleteCharAt(insertStatement.length()-1);
			insertStatement.deleteCharAt(insertStatement.length()-1);
			insertStatement.append(");");
			
			Statement varstat = conn.createStatement();
			varstat.execute(insertStatement.toString());
		}

		return ret;
	}
	
	private void rollback(Savepoint sp) {
		if(sp == null) return;
		try {
			conn.rollback(sp);
			conn.releaseSavepoint(sp);
		} catch(SQLException se) {
			System.err.println("Rollback failed");
			return;
		}
	}
	
	private void releaseSavepoint(Savepoint sp) {
		if(sp == null) return;
		try {
			conn.releaseSavepoint(sp);
		} catch(SQLException se) {
			System.err.println("Rollback failed");
			return;
		}
	}
	
	private Savepoint setSavepoint() {		
		try {
			return conn.setSavepoint();
		} catch(SQLException se) {
			System.err.println("Could not create a savepoint");
			return null;
		}
	}
	
	private boolean dropTable(String table) throws SQLException {
		Statement stat = conn.createStatement();
		return stat.execute("DROP TABLE "+table+";");
	}

	private int getKey() {
		if(this.keyCount < 0)
			keyCount = DracoSQLTables.KEY_START_COUNT;
		
		return ++this.keyCount;
	}
	
	private boolean insertParameterValue(int owner, String name, int type, double value) throws SQLException {
		return insertParameterValue(owner,name,type,new Double(value));
	}
	
	private boolean insertParameter(int owner, String name, int type) throws SQLException {
		return insertParameterValue(owner,name,type,null);
	}
	
	private boolean insertParameterValue(int owner, String name, int type, Number value) throws SQLException {
		
		PreparedStatement paramstat = conn.prepareStatement("INSERT INTO "+DracoSQLTables.PARAMETERS+" VALUES(?, ?, ?, ?);");
		paramstat.setString(1,name);
		paramstat.setInt(2,owner);
		paramstat.setInt(3,type);
		if(value == null)
			paramstat.setNull(4, Types.VARCHAR);
		else
			paramstat.setDouble(4, value.doubleValue());
		paramstat.execute();
		
		return true;
	}
	
	private boolean insertMatrixValue(int owner, int x, int y, int type, double value) throws SQLException {
		
		PreparedStatement paramstat = conn.prepareStatement("INSERT INTO "+DracoSQLTables.MATRIXDATA+" VALUES(?, ?, ?, ?, ?);");
		paramstat.setInt(1, owner);
		paramstat.setInt(2, x);
		paramstat.setInt(3, y);
		paramstat.setInt(4, type);
		paramstat.setDouble(5, value);
		paramstat.execute();
		
		return true;
	}
	
	private boolean insertPlotDataValue(int owner, Object value, int pos) throws SQLException {
		PreparedStatement istat = conn.prepareStatement("INSERT INTO "+DracoSQLTables.PLOTDATA+" VALUES(?,?,?);");

		istat.setInt(1, owner);
		istat.setInt(2, pos);
		istat.setString(3, value.toString());

		istat.execute();
		return true;
	}
	
	private boolean insertSeries(int owner, ChartDataModel model, Color seriesColor, int series) throws SQLException {
		PreparedStatement pstat = conn.prepareStatement("INSERT INTO "+DracoSQLTables.PLOTSERIES+" VALUES(?,?,?,?,?,?,?);");
		
		int xkey = getKey();
		int ykey = getKey();
		
		pstat.setString(1, model.getDataSetName(series));
		pstat.setInt(2, owner);
		pstat.setInt(3, xkey); 
		pstat.setInt(4, ykey);
		pstat.setInt(5, seriesColor.getRGB());
		if(model instanceof MultiScatterDataModel) {
			pstat.setInt(7,((MultiScatterDataModel)model).getSeriesLine(series) ? DracoSQLTables.FLAG_TRUE : DracoSQLTables.FLAG_FALSE);
			pstat.setInt(6,((MultiScatterDataModel)model).getSeriesMarker(series) ? DracoSQLTables.FLAG_TRUE : DracoSQLTables.FLAG_FALSE);
		} else {
			pstat.setInt(7,DracoSQLTables.FLAG_FALSE);
			pstat.setInt(6,DracoSQLTables.FLAG_FALSE);
		}
		
		pstat.execute();
		
		for(int i=0; i<model.getDataSetLength(series); i++) {
			insertPlotDataValue(xkey,model.getColumnValueAt(series, i), i);
			insertPlotDataValue(ykey,model.getValueAt(series, i), i);
		}
		
		return true;
	}

	private boolean insertPlotAxis(int owner, String label, int type, int grid, int scaletype, int scalemode, Number min, Number max) throws SQLException {
		PreparedStatement pastat = conn.prepareStatement("INSERT INTO "+DracoSQLTables.PLOTAXISDATA+" VALUES(?, ?, ?, ?, ?, ?, ?, ?);");
		
		pastat.setInt(1,owner);
		pastat.setString(2,label);
		pastat.setInt(3,type);
		pastat.setInt(4,grid);
		pastat.setInt(5, scaletype);
		pastat.setInt(6, scalemode);
		
		if(min == null)
			pastat.setNull(7,Types.REAL);
		else
			pastat.setDouble(7,min.doubleValue());
		
		if(max == null)
			pastat.setNull(8,Types.REAL);
		else
			pastat.setDouble(8,max.doubleValue());
		
		pastat.execute();
		return true;
	}

	@Override
	public String getError() {
		return null;
	}
	
}
