/* Copyright (c) 1997 Oracle Corporation */
import sqlj.runtime.profile.util.DataCustomizer;
import sqlj.runtime.profile.util.CustomizerHarness;
import sqlj.runtime.profile.util.ProfileCustomizer;
import sqlj.runtime.profile.util.ProfileIOContext;
import sqlj.runtime.profile.Customization;
import sqlj.runtime.profile.Profile;
import sqlj.runtime.profile.DefaultLoader;
import sqlj.runtime.profile.util.ProfilePrinter;
import sqlj.runtime.profile.ProfileData;
import sqlj.runtime.profile.EntryInfo;
import sqlj.runtime.profile.TypeInfo;
import sqlj.runtime.ConnectionContext;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Types;
import java.sql.DriverManager;
import java.io.FileOutputStream;
import java.io.ObjectOutputStream;
import java.io.FileInputStream;
import java.util.Enumeration;
import sqlj.framework.error.ErrorLog;
import sqlj.framework.error.JSError;
import sqlj.framework.error.Info;

/**
  The PLSQL customizer is a profile customization utility.
  It creates and installs PLSQL stored procedures in the database for 
  each SQL operation in a profile.  It also creates and installs a
  customization that will make calls to the created stored procedures rather
  than dynamic SQL calls to the original operations.

  <p>This class derives from the data customizer class, which is a common
  pattern when only the profile data object needs to be customized.  Note
  that <code>customizeSQLString<code> is the only method that this class
  overrides, so the rest of the profile data attributes will be unchanged. 

  <p>Note: This implementation is meant as an example of how profile
  customization might be done.  As sample code, it omits many
  implementation details and is not meant for use as production-quality
  code.
  **/
public class PLSQLCustomizer extends DataCustomizer 
implements ProfileCustomizer
{
  private ConnectionContext m_ctx = null;
  private ErrorLog m_log = null;


  public boolean acceptsConnection(Connection conn)
  {
    if (conn == null) return false;
    try {
      DatabaseMetaData dbmd = conn.getMetaData();
      String dbName = dbmd.getDatabaseProductName();
      return dbName.equalsIgnoreCase("ORACLE");
    } catch (SQLException e) {
      return false;
    }
  }

  /**
    Installs a profile customization for the provided profile.  If the
    profile does not contain any customizable entries, a customization is
    not created, and false is returned.  PLSQL stored procedures are
    created for the non-query entries of the profile.  The customization
    installed creates connected profiles which call the procedures created
    by this routine. Any previous installations of PLSQL customizations
    are deregistered.

    @return true if the profile was customized, false otherwise
    **/
  public boolean customize(Profile profile, ConnectionContext ctx,
                           ErrorLog log,    ProfileIOContext io)
  {
    m_ctx = ctx;
    m_log = log;
    Enumeration enum = profile.getCustomizations();
    while (enum.hasMoreElements()) {
      Customization c = (Customization)enum.nextElement();
      if (c instanceof PLSQLCustomization) {
	profile.deregisterCustomization(c);
      }
    }
    String oldFilename = log.getFileName();
    log.setFileName(profile.getProfileData().getSourceFile());
    ProfileData customData = customizeData(profile.getProfileData());
    log.setFileName(oldFilename);
    if (customData != profile.getProfileData()) {
      Customization cust = new PLSQLCustomization(customData);
      profile.registerCustomization(cust);
      return true;
    } else {
      return false;
    }
  }

  /**
    Adds an informational message to the log using the line number of the
    passed entry info object.
    **/
  private void addInfo(String mesg, EntryInfo entry)
  {
    m_log.addEntry(new Info(mesg), entry.getLineNumber(), 0);
  } 
  /**
    Adds an error message to the log using the line number of the
    passed entry info object.
    **/
  private void addError(String mesg, EntryInfo entry)
  {
    m_log.addEntry(new JSError(mesg), entry.getLineNumber(), 0);
  } 

  /**
    Creates a custom version of the sql string which will call a PLSQL
    stored procedure instead of executing the original operation.  Only
    non-query operations are customized.  This method first attempts to
    create and install a PLSQL stored procedure whose body is the
    operation originally stored in the profile entry to customize.  If the
    procedure is successfully created and installed, the customized SQL
    string that calls the new procedure is created and returned.
    **/
  public String customizeSQLString(ProfileData data,
				   EntryInfo entry, int entryNum)
  {
    if (entry.getExecuteType() != EntryInfo.EXECUTE_UPDATE) {
      return entry.getSQLString();
    }
    String procName = data.getProfile().getProfileName() + "_" + entryNum;
    String sqlText = entry.getSQLString();
    String procText = createProcedureText(procName, entry, sqlText); 

    try {
      addInfo("Creating procedure: " + procText, entry);
      PreparedStatement stmt = m_ctx.getConnection().prepareStatement(procText);
      stmt.executeUpdate();
      stmt.close();
      sqlText = createProcedureCall(procName, entry);
      addInfo("called using: " + sqlText, entry);      
    } catch (SQLException e) {
      addError(e.toString(), entry);
    }
    return sqlText;
  }

  private static final String PARAM_PREFIX = "jSP";

  /**
    Returns a string whose contents represent a PLSQL stored procedure
    whose body is the original SQL operation, as given by "origText".  The
    number, type and modality of the procedure parameters is determined by
    the type info object for each parameter in the passed entry info
    object.
    **/
  private String createProcedureText(String procName, EntryInfo entry,
				    String origText)
  {
    StringBuffer proc = new StringBuffer();
    proc.append("CREATE OR REPLACE PROCEDURE ");
    proc.append(procName);
    int numParams = entry.getParamCount();
    if (numParams > 0) {
      proc.append("(");
      for (int i = 1; ; i++) {
	proc.append(PARAM_PREFIX);
	proc.append(i);
	proc.append(" ");
	TypeInfo paramInfo = entry.getParamInfo(i);
	addMode(proc, paramInfo.getMode());
	addType(proc,paramInfo.getSQLType());
	if (i == numParams) break;
	proc.append(", ");
      }
      proc.append(")");
    }
    proc.append(" AS BEGIN ");
    addProcBody(proc, origText, entry); 
    proc.append(" END ");
    proc.append(procName);
    proc.append(";");
    return proc.toString();
  }
  
  /**
    Appends a string representation of the passed modality to the 
    passed procedure string buffer.
    **/
  private void addMode(StringBuffer proc, int mode)
  {
    switch(mode) {
    case TypeInfo.IN:
      proc.append("IN ");
      break;
    case TypeInfo.OUT:
      proc.append("OUT ");
      break;
    case TypeInfo.INOUT:
      proc.append("IN OUT ");
      break;
    }
  }

  /**
    Appends a string representation of the passed sqlType to the 
    passed procedure string buffer.  Note that the passed sqlType
    must be mapped from a JDBC type to a type known by Oracle PLSQL.
    **/
  private void addType(StringBuffer proc, int sqlType)
  {
    switch(sqlType) {
    case Types.TINYINT:
    case Types.SMALLINT:
    case Types.INTEGER:
    case Types.BIGINT:
    case Types.REAL:
    case Types.FLOAT:
    case Types.DOUBLE:
    case Types.DECIMAL:
    case Types.NUMERIC:
    case Types.BIT:
      proc.append("NUMBER");
      break;
    case Types.CHAR:
      proc.append("CHAR");
      break;
    case Types.VARCHAR:
      proc.append("VARCHAR");
      break;
    case Types.LONGVARCHAR:
      proc.append("LONG");
      break;
    case Types.BINARY:
    case Types.VARBINARY:
    case Types.LONGVARBINARY:
      proc.append("RAW");
      break;
    case Types.DATE:
    case Types.TIME:
    case Types.TIMESTAMP:
      proc.append("DATE");
      break;
    }
  }

  /**
    Appends the procedure body to the passed procedure string buffer.  The
    procedure body is created by replacing all bind variable references
    (?) in the passed original sql text with references to stored
    procedure parameters.  
    **/
  private void addProcBody(StringBuffer proc, String origText, 
			   EntryInfo entry)
  {
    int ind = 0;
    int lastInd = 0;
    int numParams = entry.getParamCount();
    for (int i = 1; i <= numParams; i++) {
      ind = entry.getParamInfo(i).getMarkerIndex();
      proc.append(origText.substring(lastInd, ind));
      proc.append(PARAM_PREFIX);
      proc.append(i);
      lastInd = ind + 1;
    }
    proc.append(origText.substring(lastInd));
    if (!origText.trim().endsWith(";")) {
      proc.append(";");
    }
  }

  /**
    Returns a string whose text represents a call to a stored procedure
    with the passed name.  The number of parameters to the procedure is
    found in the passed entry info object.
    **/
  private String createProcedureCall(String procName, EntryInfo entry)
  {
    StringBuffer call = new StringBuffer();
    call.append("{ CALL ");
    call.append(procName);
    int numParams = entry.getParamCount();
    if (numParams > 0) {
      call.append("(");
      for (int i = 1; ; i++) {
	call.append("?");
	if (i == numParams) break;
	call.append(",");
      }
      call.append(")");
    }
    call.append(" }");
    return call.toString();
  }

  /**
    Main entry point for the customization installer.  
    This routine defers to the main method of the CustomizerHarness
    class, passing itself as the hardcoded customizer option.
    **/
  public static void main(String[] args) 
  {
    String[] newArgs = new String[args.length+1];
    newArgs[0] = "-customizer=PLSQLCustomizer";
    System.arraycopy(args,0,newArgs,1,args.length);
    CustomizerHarness.main(newArgs);
  }
}

