/*
   XBN Java:  Generically useful, non-GUI Java code.
   http://sourceforge.net/projects/xbnjava

   Copyright (C) 1997-2003, Jeff Epstein
   All rights reserved.

   Modifications: No

   Redistribution in binary form, with or without modifications, are permitted provided that the following conditions are met:

      * Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution.
      * If modifications are made to source code then this license should indicate that fact in the "Modifications" section above.
      * Neither the author, nor the contributors may be used to endorse or promote products derived from this software without specific prior written permission.

   THIS SOFTWARE IS PROVIDED BY THE AUTHOR AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

   [NOTE:  This license contains NO advertising clause.]
 */
package xbn.db;
   import xbn.XBNObject;
   import xbn.AssertException;
   import xbn.output.Debuggable;
   import xbn.output.Outputter;

   import java.sql.PreparedStatement;
   import java.sql.Connection;
   import java.sql.ResultSet;
   import java.sql.SQLException;


/**
   <P>Convenience class for handling a single database connection.  See java.sql.<A HREF="~JD~conn~EJD~">Connection</A>.</P>

   <P><CODE><I><B>Source code:</B> &nbsp;<A HREF="../../../CODE/xbn/db/DBConnDirect.java">DBConnDirect.java</A></I></CODE></P>

   <P>This class most importantly provides functionality for automatic and dynamic debugging output.  It also provides automatic and accurate detection of whether the database connection is truly inaccessible, or if you just made a silly sql error.</P>

   @version  0.9b
   @author  Jeff Epstein, <A HREF="http://sourceforge.net/projects/xbnjava">http://sourceforge.net/projects/xbnjava</A>.
 **/
public class DBConnDirect extends XBNObject implements Debuggable  {

   private Outputter optr = null;
   private Connection conn = null;

   private String sVerificationSelect = null;
   private PreparedStatement psVerificationSelect = null;

   /**
      <P>Create a DBConnDirect.</P>

      <P>Equal to <CODE><A HREF="~JD~dbcd(s,optr)~EJD~">DBConnDirect</A>(s_verificationSelect, (new <A HREF="~JD~optr#optr()~EJD~">Outputter</A>()))</CODE></P>
    **/
   public DBConnDirect(String s_verificationSelect)  {
      this(s_verificationSelect, (new Outputter()));
   }

   /**
      <P>Create a DBConnDirect.</P>

      <P>It is expected that you <A HREF="~JD~setConnection(conn)~EJD~">set the connection</A> before doing anything else with this class.</P>

      @param  s_verificationSelect  Trivial select query used for verification of the connection, used in <A HREF="#isVerified()">isVerified</A>.  This query is executed to verify both the connection and itself.  If it throws an SQLException for any reason, this is thrown, and the connection is disconnected.
      @param  optr_dbg  The Outputter to use for debugging output.  May not be null.
    **/
   public DBConnDirect(String s_verificationSelect, Outputter optr_dbg)  {
      if(s_verificationSelect == null  &&
         	s_verificationSelect.length() < 1)  {
         throwAXDbg("constructor:  s_verificationSelect must be non-null and at least one chararacter in length.");
      }

      sVerificationSelect = s_verificationSelect;
      setOptrDbg(optr_dbg);
   }

   /**
      <P>Create a DBConnDirect.</P>

      @param  c_onnection  The database connection.  Must be non-null, and actually connected to the database.  See <A HREF="#setConnection(java.sql.Connection)">setConnection</A>.
      @param  s_verificationSelect  Passed directly to the <A HREF="~JD~dbcd(s,optr)~EJD~">two-parameter constructor</A>.
      @param  optr_dbg  Passed directly to the two-parameter constructor.
    **/
   public DBConnDirect(Connection c_onnection, String s_verificationSelect, Outputter optr_dbg) throws SQLException  {
      this(s_verificationSelect, optr_dbg);
      setConnection(c_onnection);
   }



   /**
      <P>Disconnect from the database.  To reconnect again, provide a new connection to <A HREF="#setConnection(java.sql.Connection)">setConnection</A>.</P>
    **/
   public synchronized final void disconnect() throws SQLException  {

      dbg("Disconnecting...");
      if(getConnection() == null)  {
         throwAXDbg("disconnect:  getConnection() is null.  Already disconnected.  Try connect().");
      }

      closeVerificationSelectStmt();

      SQLException sqlx = null;
      try  {
         if(conn != null)  {
            conn.close();
         }
      }  catch(SQLException sqlx2)  {
         sqlx = sqlx2;
      }  finally  {
         conn = null;
         if(sqlx != null)  {
            throw new SQLException("DBConnDirect disconnect:  " + sqlx.toString());
         }
      }

      dbg("...SUCCESS.  Disconnected.");
   }



   /**
      <P>Get the java.sql.Connection for direct manipulation.  See java.sql.<A HREF="~JD~conn~EJD~">Connection</A>.</P>

      <P><I>Use with caution.</I>  You can screw things up if you know what you're doing.</P>
    **/
   public synchronized Connection getConnection()  {
      return conn;
   }

   /**
      <P>Get the java.sql.Connection for direct manipulation, and eleminate all references in this class to it.</P>

      <P>Use this when you want the DBConnDirect to create the connection (perhaps for debugging purposes), but nothing else.  Normally, when setting <I>this</I> to null, the <A HREF="#finalize()">finalize</A> function is called, which disconnects the connection.  Eliminating all internal references to this connection will prevent this, and you can now safely null out this DBConnDirect object, and continue to use the returned connection.</P>

      <P>If you still want the connection to be referenced by this object, use <A HREF="#getConnection()">getConnection</A>.</P>

      <P>After this function is called, this class is in such a state, as if you called <A HREF="#disconnect()">disconnect</A>.</P>
    **/
   public synchronized Connection getConnectionAndSeparate()  {
      Connection c = conn;
      closeVerificationSelectStmt();
      conn = null;
      dbg("getConnectionAndSeparate:  Internal references eliminated.");
      return c;
   }

   /**
      <P>Set the internal java.sql.Connection to the one provided.</P>

      @param  c_onnection  The database connection.  Must be non-null and connected.  Also, the <A HREF="#getVerificationSelect()">verification select</A> query must be <A HREF="#isVerified()">verified</A> in it.
    **/
   public final void setConnection(Connection c_onnection) throws SQLException  {
      if(isConnected())  {
         dbg("\tPrevious connection exists.  Disconnecting...");
         disconnect();
      }

      if(c_onnection == null)  {
         throwAXDbg("constructor:  c_onnection is null.");
      }

      dbg("setConnection...");

      dbg("\tAccepting c_onnection constructor parameter...");
      conn = c_onnection;

      prepareVerificationSelect("setConnection");

      dbg("setConnection...SUCCESS");
   }

   /**
      <P>Is the connection currently and actually open to the database?</P>

      @return <B>true</B> If the connection object is not null and <A HREF="#isVerified()">isVerified</A> is true.
      <BR><B>false</B> If the connection is null, or if the connection is not null, but isVerified returns false.
    **/
   public final boolean isConnected()  {
      if(getConnection() == null)  {
         return false;
      }
      //The connection is not null.

      return isVerified();
   }

   /**
      <P>Get the trivial sql that verifies the connection is currently and truly connected to the database.</P>

      <P>See <A HREF="#isVerified()">isVerified</A>, <A HREF="~JD~isVerified(s)~EJD~">isVerified</A> and <A HREF="~JD~crashIfNotVerified(s)~EJD~">crashIfNotVerified</A>.</P>

      <P>This should be a trivial select query, only used to verify whether or not the connection is still active.  <I>The result of this query will never be seen</I>.  Therefore, it should be the most un-complicated, speediest, least-memory-hogging, trivial select query you can think of.</P>

      <P>For example, in the Oracle platform, you might consider the following, in descending order of goodness:</P>

      <PRE>select 'x' from dual
select sysdate from dual
select count(*) from tab</PRE>

      <P><I>Examples for other platforms...?</I>

      @return The sql of the verification select query.  This is equal to s_verificationSelect, exactly as provided to the <A HREF="~JD~dbcd(s,optr)~EJD~">constructor</A>.
    **/
   public String getVerificationSelect()  {
      return sVerificationSelect;
   }

   /**
      <P>Verify connection with a trivial select query.  If the query throws an SQLException for any reason, the connection is to be considered <I>un-</I>verified.  Equal to</P>

      <PRE><A HREF="~JD~isVerified(s)~EJD~">isVerified</A>(null);</PRE>

      <P><B>What if java is connected to the database, but then the connection is shut down, or restarted <I>from within the database</I>, outside of java?</B>  In this case, java still thinks that it's connections are valid.  There's no way that this shutdown or restart can be communicated to java, so the connection remains &quot;open&quot; even though it's now pointing to...nothing.  Literally, null.</P>

      <P>So, when this situation occurs, how is it possible to tell the difference between an SQLException being thrown because of a genuine problem with your sql (or code), and one being thrown simply because the database is not accessible?  It is possible by running a select query so trivial, that, <I>most likely</I>, any exception being thrown is not because of a problem with the query, but probably something more fundamental.</P>

      <P>That's the purpose of this function.  It's not an exact science, but it'll do.</P>

      @return <B>true</B> if the query does not throw new an SQLException.
      <BR><B>false</B> if the query throws an SQLException for any reason.
    **/
   public boolean isVerified()  {
      return isVerified(null);
   }

   /**
      <P>Verify connection with a trivial select query, but, if desired (and only in the case of an SQLException), a AssertException is thrown to display a descriptive error message.  This should only be used for the sake of debugging, in the small case that the database is properly connected, but you have supplied a faulty query.</P>

      <P>In most cases, use <A HREF="#isVerified()">isVerified</A> or <A HREF="~JD~crashIfNotVerified(s)~EJD~">crashIfNotVerified</A>;</P>

      @param  s_funcForAX  If you want an AssertException to be thrown when the connection cannot be verified, pass in a function name to be displayed in the potential error message.  If null, then no exception will be thrown, rather false will be returned.
      @return  <B>true</B>  If the query does not throw an SQLException.
      <BR><B>false</B>  If the query causes an SQLException for any reason <I>and</I> s_funcForAX is null.
      @exception  AssertException If the query throws an SQLException for any reason.
      <BR><B>AssertException</B>  If the connection is not connected, as determined by <A HREF="#isConnected()">isConnected</A>
    **/
   public boolean isVerified(String s_funcForAX)  {
      //JTest is saying that "JDBC resources should be closed" in this
      //function.  Probably psVerification select...

      if(getConnection() == null)  {
         throwAXDbg("isVerified:  getConnection() is null.");
      }

      ResultSet rs = null;
      try  {
         dbg("isVerified:  " + getVerificationSelect() + "...");
//			int iStatus = psVerificationSelect.executeUpdate();

         //We don't care what it returns, this is just to indicate that it *does* return something.
         boolean bStatusUNRTRND = psVerificationSelect.execute();
      }  catch(SQLException sqlx)  {
         dbg("...ERROR:  " + sqlx.toString());

         if(s_funcForAX != null)  {
            throwAXDbg(s_funcForAX + ".  Error while attempting to run the verification select query:  " + sqlx.toString());
         }

         return false;

      }  finally  {
         rs = null;
      }


      //SUCCESS!
      //(We don't care what the status is, just that it didn't
      //outright crash.)
      return true;
   }

   /**
      <P>If the connection cannot be verified, crash with a descriptive AssertException.</P>

      <P>Equal to <CODE><A HREF="~JD~isVerified(s)~EJD~">isVerified</A>(s_callingFunc)</CODE></P>
    **/
   public void crashIfNotVerified(String s_callingFunc)  {
      boolean bUNRTRND = isVerified(s_callingFunc);
   }

   //REQUIRED BY xbn.output.Debuggable...START
      public final Outputter getOptrDbg()  {
         return optr;
      }
      public final void setOptrDbg(Outputter optr_dbg)  {
         throwAXIfNull(optr_dbg, "optr_dbg", "setOutputter");
         optr = optr_dbg;
      }
      public final void dbg(String s_message)  {
         getOptrDbg().write(s_message);
      }
      public final void dbgnl(String s_message)  {
         getOptrDbg().writeNoln(s_message);
      }
   //REQUIRED BY xbn.output.Debuggable...END


   /**
      <P>Get some information about this DBConnDirect.</P>
    **/
   public String toString()  {
      return this.getClass().getName() + ":  isConnected()=" + isConnected() + ", getVerificationSelect()=" + getVerificationSelect();
   }


   /**
      Throws an SQLException (and logs it).  If configured, it is confirmed whether the DBConnDirect is connected and verified, according to <A HREF="#isConnected()">isConnected</A> and <A HREF="#isVerified()">isVerified</A>.  If either of these return false, then add a message onto the end of the exception message.
    **/
   public void throwSQLX(String s_classFuncError) throws SQLException  {
      String sError = "ERROR in " + s_classFuncError.trim() + "...  getConnection() is ";
      if(getConnection() == null)  {
         sError += "null, meaning the connection is currently disconnected from the database (cannot verify a null connection).";

      //Else:  The connection is not null.
      }  else if(!isVerified())  {
         sError += "not null, but isVerified() is false.";

      }  else  {
         sError += "not null, and isVerified() is true.";
      }

      dbg(sError);
      throw new SQLException(sError);
   }

   /**
      <P>For internal use only.</P>

      <P>If <A HREF="#isConnected()">isConnected</A> equals true, then <A HREF="#disconnect()">disconnect</A>.  Then call java.lang.<A HREF="~JD~o~EJD~">Object</A>.<A HREF="~JD~o~EJD~#finalize()">finalize</A>.</P>

      <P>Automatically called by the Java Runtime Environment when <I>this</I> is set to null.</P>
    **/
   protected void finalize() throws Throwable  {
      if(isConnected())  {
         disconnect();
      }
      super.finalize();
   }

   /**
      <P>Throw an AssertException, but debug it first.</P>
    **/
   protected final void throwAXDbg(String s_funcMsg)  {
      String sError = getXMsgPrefix() + s_funcMsg;
      dbg(sError);
      throw new AssertException(sError);
   }

   /**
      <P>For internal use only.</P>

      <P>Attempts to create the java.sql.PreparedStatement for the verification select query, and then execute it.  If it fails with an SQLException, the connection is disconnected.  This is called by setConnection and DBConnDirect.connect().</P>
    **/
   protected final void prepareVerificationSelect(String s_callingFunc)  {
      dbg("\tAttempting verification select query...");

      if(conn == null)  {
         throwAX("prepareVerificationSelect:  Must setConnection.");
      }

      try  {
         psVerificationSelect = conn.prepareStatement(getVerificationSelect());
      }  catch(SQLException sqlx)  {
         throwAXDbg(s_callingFunc + ":  getVerificationSelect() does not seem to be legal:  " + sqlx.toString());
      }

      boolean bUNRTRND = isVerified("setConnection");
   }

   private void closeVerificationSelectStmt()  {
      try  {
         if(getConnection() != null)  {
            psVerificationSelect.close();
         }
         psVerificationSelect = null;

      }  catch(SQLException sqlx)  {
         throwAXDbg("closeVerificationSelectStmt:  SHOULD NEVER OCCUR:  " + sqlx.toString());
      }
   }


}