package hirondelle.web4j.database;

import java.util.logging.*;
import java.sql.Connection;
import java.sql.SQLException;

import hirondelle.web4j.util.Util;

/**
 Type-safe enumeration for transaction isolation levels.
 
 <P>For more information on transaction isolation levels, see {@link Connection} and the
 <a href="http://en.wikipedia.org/wiki/Isolation_%28computer_science%29">wikipedia<a/> 
 article.

 <P>See {@link TxTemplate}, which is closely related to this class. 

 <a name="PermittedValues"></a><h3>Permitted Values</h3>
 <P>In order of decreasing strictness (and increasing performance), the levels are :
<ul>
 <li><tt>SERIALIZABLE</tt> (most strict, slowest)
 <li><tt>REPEATABLE_READ</tt>
 <li><tt>READ_COMMITTED</tt>
 <li><tt>READ_UNCOMMITTED</tt> (least strict, fastest)
</ul>

<P>In addition, this class includes another item called <tt>DATABASE_DEFAULT</tt>. It 
 indicates to the WEB4J data layer that, unless instructed otherwise,
 the default isolation level defined by the database instance is to be used. 

 <h3>Differences In The Top 3 Levels</h3>
 It is important to understand that the top 3 levels 
 listed above differ only in one principal respect : behavior for any 
 <em>re</em>-SELECTs performed in a transaction.  <span class="highlight">If no re-SELECT is 
 performed in a given transaction, then the there is no difference in the 
 behavior of the top three levels</span> (except for performance).

<P>If a SELECT is repeated in a given transaction, it may see a different 
 <tt>ResultSet</tt>, since some second transaction may have committed changes 
 to the underlying data. Three questions can be asked of the second <tt>ResultSet</tt>, 
 and each isolation level responds to these three questions in a different way :
<P><table border=1 cellspacing="0" cellpadding="3"  width="75%">
 <tr valign="top">
  <th>Level</th>
  <th>1. Can a new record appear?</th>
  <th>2. Can an old record disappear?</th>
  <th>3. Can an old record change?</th>
 </tr>
 <tr><td><tt>SERIALIZABLE</tt></td><td>Never</td><td>Never</td><td>Never</td></tr>
 <tr><td><tt>REPEATABLE_READ</tt></td><td>Possibly</td><td>Never</td><td>Never</td></tr>
 <tr><td><tt>READ_COMMITTED</tt></td><td>Possibly</td><td>Possibly</td><td>Possibly</td></tr>
</table>
 <P>(Note : 1 is called a <em>phantom read</em>, while both 2 and 3 are called a 
 <em>non-repeatable read</em>.)
 
 <h3>Configuration In <tt>web.xml</tt></h3>
 <em>When no external <tt>Connection</tt> is passed by the application</em>, then  
 the WEB4J data layer will use an internal <tt>Connection</tt>
 set to the isolation level configured in <tt>web.xml</tt>. 

<h3>General Guidelines</h3>
<ul>
 <li>consult both your database administrator and your database documentation 
 for guidance regarding these levels
 <li><span class="highlight">since support for these levels is highly variable, 
 setting the transaction isolation level explicitly has low portability</span> 
 (see {@link #set} for some help in this regard). The <tt>DATABASE_DEFAULT</tt> 
 setting is an attempt to hide these variations in support
 <li>for a WEB4J application, it is likely a good choice to use the 
 <tt>DATABASE_DEFAULT</tt>, and to alter that level only under special circumstances 
 <li>selecting a specific level is always a trade-off between level of data 
 integrity and execution speed
</ul>

<h3>Support For Some Popular Databases</h3>
 (Taken from <em>
 <a href="http://www.amazon.com/exec/obidos/ASIN/0596004818/ref=nosim/javapractices-20">SQL
 in a Nutshell</a></em>, by Kline, 2004. <span class="highlight">Please confirm with 
 your database documentation</span>).<P>
<table border=1 cellspacing="0" cellpadding="3"  width="60%">
<tr valign="top">
 <td>&nbsp;</td>
 <td>DB2</td>
 <td>MySQL</td>
 <td>Oracle</td>
 <td>PostgreSQL</td>
 <td>SQL Server</td>
</tr>
<tr>
 <td><tt>SERIALIZABLE</tt></td>
 <td>Y</td>
 <td>Y</td>
 <td>Y</td>
 <td>Y</td>
 <td>Y</td>
</tr>
<tr>
 <td><tt>REPEATABLE_READ</tt></td>
 <td>Y</td>
 <td>Y*</td>
 <td>N</td>
 <td>N</td>
 <td>Y</td>
</tr>
<tr>
 <td><tt>READ_COMMITTED</tt></td>
 <td>Y</td>
 <td>Y</td>
 <td>Y*</td>
 <td>Y*</td>
 <td>Y*</td>
</tr>
<tr>
 <td><tt>READ_UNCOMMITTED</tt></td>
 <td>Y</td>
 <td>Y</td>
 <td>N</td>
 <td>N</td>
 <td>Y</td>
</tr>
</table>
 &#8727; Database Default<br>
*/
public enum TxIsolationLevel {
  
  SERIALIZABLE("SERIALIZABLE", Connection.TRANSACTION_SERIALIZABLE),
  REPEATABLE_READ("REPEATABLE_READ", Connection.TRANSACTION_REPEATABLE_READ),
  READ_COMMITTED("READ_COMMITTED", Connection.TRANSACTION_READ_COMMITTED),
  READ_UNCOMMITTED("READ_UNCOMMITTED", Connection.TRANSACTION_READ_UNCOMMITTED),
  DATABASE_DEFAULT ("DATABASE_DEFAULT", -1);
  
  /**
   Return the same underlying <tt>int</tt> value used by {@link Connection} to identify the isolation level.
  
   <P>For {@link #DATABASE_DEFAULT}, return <tt>-1</tt>.
  */
  public int getInt(){
    return fIntValue;
  }
  
  /**  Return one of the <a href="#PermittedValues">permitted values</a>, including <tt>'DATABASE_DEFAULT'</tt>.  */
  public String toString(){
    return fText;
  }
  
  /**
   Set a particular isolation level for <tt>aConnection</tt>.
  
   <P>This method exists because database support for isolation levels varies 
   widely.<span class="highlight"> If any error occurs because <tt>aLevel</tt> is not supported, then 
   the error will be logged at a <tt>SEVERE</tt> level, but 
   the application will continue to run</span>. This policy treats isolation levels 
   as important, but non-critical. Porting an application to a database which 
   does not support all levels will not cause an application to fail. The transaction 
   will simply execute at the database's default isolation level.
  
   <P>Passing in the special value {@link #DATABASE_DEFAULT} will cause a no-operation.
  */
  public static void set(TxIsolationLevel aLevel, Connection aConnection){
    if( aLevel != DATABASE_DEFAULT ) {
      try {
          aConnection.setTransactionIsolation(aLevel.getInt());
      }
      catch (SQLException ex) {
        fLogger.severe(
          "Cannot set transaction isolation level. Database does " + 
          "not apparently support '" + aLevel + 
          "'. You will likely need to choose a different isolation level. " + 
          "Please see your database documentation, and the javadoc for TxIsolationLevel."
        );
      }
    }
  }
  
  // PRIVATE //
  private TxIsolationLevel(String aText, int aIntValue){
    fText = aText;
    fIntValue = aIntValue;
  }
  private String fText;
  private int fIntValue;
  private static final Logger fLogger = Util.getLogger(TxIsolationLevel.class);  
}
