/*
 * @(#)SQLConnection.java	0.2 00/08/04
 *
 * Copyright 2000 by Carnegie Mellon, All rights reserved.
 */

package lmt;

import java.sql.*;
import java.util.*;

/**
 * Connection to an SQL Database. Connects to a remote SQL server
 *
 * @author  Eric Nyberg
 * @version 0.2 2000/08/04
 */
public class SQLConnection {  
    
  // Doesn't connect automatically. Create one with
  // the constructor and use the connect() method to
  // initiate the connection.
  //
  // It's a good idea to call close() on the object
  // when you're done with it. (Side-effects of not
  // doing so haven't been tested.)


  /**
   * the actual connection
   */
  private Connection myConnection;

  /**
   * the driver
   */    
  private String myDriverName;

  /**
   * Where the database lives
   */
  private String myURL;

  /**
   * the username to access the Oracle database
   */
  private String username;

  /**
   * the password to access the Oracle database
   */
  private String password;
  
  /**
   * Set to true when connection is established
   */
  private boolean connected = false;

  /**
   * makes a connection possible (doesn't actually connect)
   * @param driver driver to use
   * @param url    Where database lives
   */    
 
  // -- add by jko for Oracle connection
  public SQLConnection(String url) {
    myURL = "jdbc:oracle:thin:lmtuser/lmt@" + url;
    username = "lmtuser";
    password = "lmt";
    myConnection = null;
  }  
  /*      
  // -- add by jko for Oracle connection
  public SQLConnection(String url, String user, String passwd) {
  myURL = "jdbc:oracle:thin:@" + url;
  username = user;
  password = passwd;
  myConnection = null;
  }  
  */    
  /**
   * Closes this database connection. Call when done accessing the database
   */    
  public void close() {
    if (connected)
      try {
	myConnection.close();
	myConnection = null;
	connected = false;
      } catch (Exception e) {
	//-- ReportError.bug(e);
      }
  }
        

  /**
   * Open the database connection
   * @return true if connection was established successfully
   */
  public boolean connect() {
    // If we're already connected, return right away.
    if (connected) return true;        
    // Otherwise, returns true if connection succeeded, false otherwise.
    try {
      // Try to load the driver class as specified by the String
      // passed to the constructor.
      //Class.forName(myDriverName).newInstance(); 
      // add by jko
      DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
      Debug.debug("-- registerDriver: success");
    }
    catch (Exception e) {
      //--      ReportError.bug(e);
      Debug.debug("-- registerDriver fail");
      System.out.println("SQLException: " + e.getMessage());
      connected = false;
      return false;
    }
    try {
      // Try getting a connection to the specified database URL.
      //-- myConnection = DriverManager.getConnection(myURL);
      // added by jko
		
      /*		myURL = "jdbc:oracle:thin:@(DESCRIPTION ="+
	" (ADDRESS_LIST ="+
	" (ADDRESS = (PROTOCOL = TCP)(HOST = " + 
	" 128.2.211.17)(PORT = 1521))"+ 
	")"+
	"(CONNECT_DATA ="+
	"(SERVICE_NAME = lmt2.lti.cs.cmu.edu)"+
	")"+
	")";
      */		
      myConnection = DriverManager.getConnection(myURL, username, password);
      Debug.debug("-- getConnection: success");
      connected = true;
      return true;
    }
    catch (SQLException e) {
      //--      ReportError.warning(e);
      Debug.debug("-- getConnection fail");
      System.out.println("SQLException: " + e.getMessage());
      connected = false;
      return false;
    }
  }

  /**
   * Query the database
   * @param query Query command to send to database
   * @return      database results
   */
  // synchronized : added by jko
  public synchronized ResultSet query2(String query) {
    if (!connected) {
      if (!connect()) {
	System.out.println("ResultSet is null because db is not connected");
	return null;
      }
    }
	  
    try {
      Statement s = myConnection.createStatement();
      ResultSet rs = s.executeQuery(query);
      //s.close();
      return rs;
    } catch (SQLException e) {
      System.out.println("Error in JDBC connection");
      System.out.println("SQLException: " + e.getMessage());
      return null;
    }
  }

  public class Result {
    private Statement s;
    public ResultSet rs;
	
    public boolean hasData() {
      try {
	return ( rs != null && rs.next());	
      } catch (SQLException e) {
	return false;
      }		
    }
	
    public void close() {
      try {
	if ( rs!=null ) rs.close();
	if ( s!=null ) s.close();
      }  catch (SQLException e) {}
    }
  }
  
  public synchronized Result query(String query) {
    if (!connected) {
      if (!connect()) {
	System.out.println("ResultSet is null because db is not connected");
	return null;
      }
    }
	  
    try {
      Result r = new Result();
      r.s = myConnection.createStatement();
      r.rs = r.s.executeQuery(query);
      //s.close();
      return r;
    } catch (SQLException e) {
      System.out.println("Error in JDBC connection");
      System.out.println("SQLException: " + e.getMessage());
      return null;
    }
  }

  public synchronized boolean callProcedure(String name, String id) {
    try {	
      String sql = "{call " + name + "(?)}";
      CallableStatement cs = myConnection.prepareCall(sql);
      cs.setString(1, id);
      cs.execute();
      cs.close();
      return true; 
    } catch (SQLException e) {
      System.out.println("Error in stored procedure");
      System.err.println("ERROR: " + e);
      //e.printStackTrace(System.out);		
      rollback();
      return false;
    }
  }
  
  public synchronized boolean transaction(String[] query) {
    try {	
      myConnection.setAutoCommit(false);	
      Statement s = myConnection.createStatement();
      if ( query != null ) {
	for ( int i=0; i<query.length; i++)  {
	  int ret = s.executeUpdate(query[i]);
	  if ( ret == 0 ) {
	    s.close();
	    rollback();
	    myConnection.setAutoCommit(true);	
	    return false;
	  }
	}				
      }	
      myConnection.commit();				
      myConnection.setAutoCommit(true);	
      s.close();
      return true; 
    } catch (SQLException e) {
      System.out.println("Error in transaction");
      System.out.println("SQLException: " + e.getMessage());
      rollback();
      setAutoCommit(true);	
      return false;
    }
  }
  
  public synchronized boolean save(Vector query) {
    try {	
      myConnection.setAutoCommit(false);	
      Statement s = myConnection.createStatement();
      if ( query != null ) {
	for ( int i=0; i<query.size(); i++) 
	  s.executeUpdate((String)query.elementAt(i));
      }	
      myConnection.commit();				
      myConnection.setAutoCommit(true);	
      s.close();
      return true; 
    } catch (SQLException e) {
      System.out.println("Error in transaction");
      System.out.println("SQLException: " + e.getMessage());
      rollback();
      setAutoCommit(true);			
      return false;
    }
  }  

  public void setAutoCommit(boolean value) {
    try {		  
      myConnection.setAutoCommit(value);
    } catch (SQLException e) {
      System.out.println("Error in setAutoCommit");
      System.out.println("SQLException: " + e.getMessage());
    }	  
  }

  public void commit() {
    try {		  	  
      myConnection.commit();
    } catch (SQLException e) {
      System.out.println("Error in commit");
      System.out.println("SQLException: " + e.getMessage());
    }	  	  
  }  
  
  public void rollback() {
    try {		  	  
      myConnection.rollback();
      Debug.debug("-- rollback");
    } catch (SQLException e) {
      System.out.println("Error in rollback");
      System.out.println("SQLException: " + e.getMessage());
    }	  	  
  }    
  
  public void setTransactionIsolation(int level) {
    try {		  	  
      myConnection.setTransactionIsolation(level);		
    } catch (SQLException e) {
      System.out.println("Error in setTransactionIsolation");
      System.out.println("SQLException: " + e.getMessage());
    }	  			
  }
  
  public int insert(String sql){
    return update(sql);
  }
  public int insert(String[] sql){
    int ret = 0;
    for (int i=0; i<sql.length; i++) {
      ret = updateQuery(sql[i]);
      if ( ret != 1) 
	return Constants.FAIL;
    }
    return Constants.SUCCESS;
  }  
  public int delete(String sql){
    return update(sql);
  }

  public synchronized int update(String query) {

    if (!connected) {
      if (!connect()) return Constants.DATABASE_ERROR;
    }
	
    try {
      Statement s = myConnection.createStatement();
      Debug.debug("-- " + query);
      int ret = s.executeUpdate(query);
      s.close();
      if (ret==1)
	return Constants.SUCCESS;
      else
	return Constants.FAIL;
    } catch (SQLException e) {
      System.err.println("update: Error in JDBC connection");
      System.err.println("message: " + e.getMessage());
      return Constants.FAIL;
    }
  }
  
  // update each object
  public synchronized int update(Vector query) {
    if (!connected) {
      if (!connect()) return Constants.DATABASE_ERROR;
    }

    try {		
      // start transaction
      myConnection.setAutoCommit(false);	
      myConnection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
      Statement s = myConnection.createStatement();
      if ( query != null ) {
	for ( int i=0; i<query.size(); i++)  {
	  Debug.debug("-- " + query.elementAt(i));
	  int ret = s.executeUpdate((String)query.elementAt(i));
	  if ( ret != 1 ) {
	    myConnection.rollback();
	    myConnection.setAutoCommit(true);	
	    s.close();
	    return Constants.FAIL;
	  }
	}				
      }	
      myConnection.commit();				
      myConnection.setAutoCommit(true);	
      s.close();
      return Constants.SUCCESS; 
    } catch (SQLException e) {
      System.out.println("Error in transaction");
      System.out.println("SQLException: " + e.getMessage());
      rollback();
      setAutoCommit(true);	
      return Constants.FAIL;
    }
  }  

  // update each object
  public synchronized int updateQuery(String query) {
    if (!connected) {
      if (!connect()) return Constants.DATABASE_ERROR;
    }
	
    try {
      Statement s = myConnection.createStatement();
      //Debug.debug("-- " + query);
      int ret = s.executeUpdate(query);
      s.close();
      return ret;
    } catch (SQLException e) {
      System.out.println("update: Error in JDBC connection");
      System.out.println("SQLException: " + e.getMessage());
      return Constants.ERROR;
    }
  }
  
  public synchronized boolean isExistent(String sql) {
    if (!connected) {
      if (!connect()) return false;
    }
	
    try {
      Statement s = myConnection.createStatement();
      if ( s==null ) return false;
		
      ResultSet rs = s.executeQuery(sql);
      boolean ret = (rs!=null &&rs.next());
      if (rs!=null) rs.close();
      s.close();
      return ret;
    } catch (SQLException E) {
      System.out.println("Error in transaction");
      System.out.println("SQLException: " + E.getMessage());
      return false;
    }	
  }

  /** create a new id
   * @return string representing new comment id
   **/	
  public synchronized String getNewID(String sequence) {
    String id = "";
    String sql = "SELECT " + sequence + ".NEXTVAL FROM DUAL"; 
  
    if (!connected) {
      if (!connect()) {
	System.out.println("Error in generating a new id because the socket is not connected");
	return id;
      }
    }
		
    try {
      Statement s = myConnection.createStatement();
      if ( s==null ) return "";
		
      ResultSet rs = s.executeQuery(sql);
      if (rs!=null &&rs.next())		
	id = rs.getString(1);
      if (rs!=null) rs.close();
      s.close();
    } catch (SQLException e) {
      System.out.println("Error in transaction");
      System.out.println("SQLException: " + e.getMessage());
    }		
    Debug.debug("-- " + sql);		
    Debug.debug("-- new " + sequence + ":" + id);			
    return id;
  }		  
}