/*
Copyright (c) 2004-2006 Joseph Gleason

Permission is hereby granted, free of charge, to any person obtaining
a copy of this software and associated documentation files (the "Software"),
to deal in the Software without restriction, including without limitation
the rights to use, copy, modify, merge, publish, distribute, sublicense,
and/or sell copies of the Software, and to permit persons to whom the
Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all
copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
SOFTWARE.

Current versions of this and other code can be downloaded at:
http://gleason.cc/

*/

package cc.glsn.v15;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;

import cc.glsn.ConfigFile;

/**
 * This class opens an SQL connection and allows you to run queries on it.
 * 
 * @author Joseph Gleason
 *
 */
public class SQLConnection
{

    private Connection db;

    
    private ConfigFile Config;
    
    private LinkedList<Statement> OpenStatements;
    

    /**
     * Create an SQLConnection using strings SQLDiver, SQLUrl, SQLUsername and SQLPassword 
     * from the given cc.glsn.ConfigFile.
     * @param C ConfigFile to grab params from
     * @throws SQLException
     */
    public SQLConnection(ConfigFile C) throws SQLException
    {
        this(C.getString("SQLDriver"),C.getString("SQLUrl"),C.getString("SQLUsername"),C.getString("SQLPassword"));
        
    }
    /**
     * 
     * @param Driver Class of SQL driver to use. Example: com.mysql.jdbc.Driver
     * @param URL URL to database. Example: jdbc:mysql://host/dbname
     * @param Username
     * @param Password
     * @throws SQLException
     */
    public SQLConnection(String Driver, String URL, String Username, String Password) throws SQLException
    {
        
        
        OpenStatements=new LinkedList<Statement>();


        
        try
        {
            Class.forName(Driver).newInstance();
        }
        catch(ClassNotFoundException e)
        {
            
            throw new SQLException("Unable to load SQL dirver: " +Driver);
            
            
        }
        catch (InstantiationException e)
        {
            e.printStackTrace();
            throw new SQLException("SQL Driver instantiation error that should not occur");
            
        }
        catch (IllegalAccessException e)
        {
            // TODO Auto-generated catch block
            e.printStackTrace();
            throw new SQLException("SQL illegal access exception error that should not occur");
        }
        
        db = DriverManager.getConnection(URL, Username, Password);


        reset();

        testConnection();






     
    }

    /**
     * Reset auto commit to true
     * Close any open statements
     * @throws SQLException
     */
    public void reset() throws SQLException
    {
        setAutoCommit(true);
        
        for(Statement S : OpenStatements)
        {
            S.close();
            
        }
        
        OpenStatements.clear();
    


    }
    
    /**
     * Close DB connection.
     * Also closes any open SQL statements.
     * This will most likely make any ResultSet objects that came from this 
     * connection cease to function.
     * @throws SQLException
     */
    public void close() throws SQLException
    {
        reset();
        db.close();
        
        
        
    }

    /**
     * Commit changes to DB.  Only needs to be done if autocommit has been set to false.
     * @throws SQLException
     */
    public void commit() throws SQLException
    {
        db.commit();
    }

    /**
     * Sets the value of autocommit.  Default is on.
     * @param B
     * @throws SQLException
     */
    public void setAutoCommit(boolean B) throws SQLException
    {
        db.setAutoCommit(B);
    }


    /**
     * Determines if the connection is ok
     * @return true if the sql connection is still connected and seems to work
     */
    public boolean ok()
    {
        try
        {
            testConnection();
        }
        catch (SQLException e)
        {
            // TODO Auto-generated catch block
            return false;
        }
        return true;
    }




    /**
     * performs a single SQL statement
     * @param Str The SQL statement to run
     * @return the number of rows effected
     * @throws SQLException
     */
    public int doStatement(String Str) throws SQLException
    {
        Statement S;
        boolean ResultType;

        S=db.createStatement();
        ResultType=S.execute(Str);


        int Updates=-1;
        if (ResultType)
        {
            Updates=0;
        }
        else
        {
            Updates=S.getUpdateCount();
        }

        S.close();
        return Updates;

    }


    /**
     * Do an SQL query.
     * Note: this ResultSet will not be updatable.  It will be read only.
     * 
     * @param Str Query
     * @return A read-only ResultSet created from the query 
     * @throws SQLException
     */
    public ResultSet doSingleQuery(String Str) throws SQLException
    {
        Statement S;
        ResultSet R;

        S=db.createStatement();
        R=S.executeQuery(Str);
            
        OpenStatements.add(S);

            

        return R;
   
    }



    /**
     * Do an SQL query.  The ResultSet returned will be updatable.
     * 
     * @param Str Quert
     * @return An updatable ResultSet
     * @throws SQLException
     */
    public ResultSet doSingleUpdatableQuery(String Str) throws SQLException
    {
        Statement S;
        ResultSet R;

        S=db.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
        R=S.executeQuery(Str);
        OpenStatements.add(S);

        return R;

    }


    /**
     * Tests the connection.  If it does not throw an exception, then the connection is probably ok.
     * the method boolean ok() should be used rather than this one.
     * @throws SQLException
     */
    private void testConnection() throws SQLException
    {
        doSingleQuery("show tables");

    }
    public Connection getDBConnection()
    {
        return db;
    }

}