//
// DataMapDatabase.java - Query database table
//
// Use JDBC to query database.
//

package nyangau.jmsadapter;

//...simports:0:
import java.util.Set;
import java.util.Map;
import java.util.Vector;
import java.util.StringTokenizer;
import java.sql.*;
import org.w3c.dom.Element;
import org.w3c.dom.NodeList;
import org.w3c.dom.NamedNodeMap;
import org.w3c.dom.Node;
import org.w3c.dom.Text;
//...e

public class DataMapDatabase extends DataMap
  {
  protected String url;
  protected String userid;
  protected String password;
  protected String split;
  protected Vector<String> scolumns;
  protected String dcolumn;
  protected String table;
  protected int retryCount;
  protected int retryDelay;
  protected String retryFail;
//...sDataMapDatabase:2:
public DataMapDatabase(Node eDataMap)
  throws FlowProcessorException
  {
  super(eDataMap);
  NamedNodeMap nnmDataMap = eDataMap.getAttributes();
  Node aDriver = nnmDataMap.getNamedItem("driver");
  if ( aDriver == null )
    throw new FlowProcessorException("unexpected missing driver, bad jmsadapter.xsd?");
  String driver = aDriver.getNodeValue();
  try
    {
    Class.forName(driver);
    }
  catch ( ClassNotFoundException e )
    {
    throw new FlowProcessorException("cannot load JDBC driver "+driver+" in data map "+name, e);
    }
  Node aUrl = nnmDataMap.getNamedItem("url");
  if ( aUrl == null )
    throw new FlowProcessorException("unexpected missing url, bad jmsadapter.xsd?");
  url = aUrl.getNodeValue();
  Node aUserid = nnmDataMap.getNamedItem("userid");
  if ( aUserid == null )
    throw new FlowProcessorException("unexpected missing userid, bad jmsadapter.xsd?");
  userid = aUserid.getNodeValue();
  Node aPassword = nnmDataMap.getNamedItem("password");
  if ( aPassword == null )
    throw new FlowProcessorException("unexpected missing password, bad jmsadapter.xsd?");
  password = aPassword.getNodeValue();
  Node aSplit = nnmDataMap.getNamedItem("split");
  split = ( aSplit != null ) ? aSplit.getNodeValue() : "";
  Node aScolumns = nnmDataMap.getNamedItem("scolumns");
  if ( aScolumns == null )
    throw new FlowProcessorException("unexpected missing scolumns, bad jmsadapter.xsd?");
  scolumns = new Vector<String>();
  StringTokenizer st = new StringTokenizer(aScolumns.getNodeValue(), ",");
  while ( st.hasMoreTokens() )
    scolumns.add(st.nextToken());
  Node aDcolumn = nnmDataMap.getNamedItem("dcolumn");
  if ( aDcolumn == null )
    throw new FlowProcessorException("unexpected missing dcolumn, bad jmsadapter.xsd?");
  dcolumn = aDcolumn.getNodeValue();
  Node aTable = nnmDataMap.getNamedItem("table");
  if ( aTable == null )
    throw new FlowProcessorException("unexpected missing table, bad jmsadapter.xsd?");
  table = aTable.getNodeValue();
  Node aRetryCount = nnmDataMap.getNamedItem("retrycount");
  retryCount = ( aRetryCount != null ) ? Integer.parseInt(aRetryCount.getNodeValue()) : 0;
  Node aRetryDelay = nnmDataMap.getNamedItem("retrydelay");
  retryDelay = ( aRetryDelay != null ) ? Integer.parseInt(aRetryDelay.getNodeValue()) : 0;
  Node aRetryFail = nnmDataMap.getNamedItem("retryfail");
  retryFail = ( aRetryFail != null ) ? aRetryFail.getNodeValue() : "throw";
  }
//...e
//...smap:2:
//...sgetQuery:2:
// Return something like this :-
//   SELECT "country","site","location" FROM CSL_TABLE WHERE
//     ( "country"='UK' AND "site"='London' ) OR
//     ( "country"='US' AND "site"='Paris' )
// There can be fewer terms in the query than unmapped strings,
// because not all unmapped strings may tokenize correctly.
// If we end up with zero terms, then we return null.

//...sgetTerm:2:
// If the input string splits into the same number of tokens as we have
// scolumns, then return something like this :-
//   ( "country"='UK' AND "site"='London' )
// else return null

//...ssqlEscaped:2:
// We need to protect against SQL injection attacks.
// Ordinarily we'd use ?'s in the query and then set query parameters.
// The JDBC driver would do the necessary escaping for us.
// But when we are assembling the peices, we don't know the parameter indexes.
// So for now, we fall back to doing the escaping ourselves.

protected static String sqlEscaped(String s)
  {
  StringBuilder sb = new StringBuilder();
    // we use StringBuilder as it is only accessed on this thread
  for ( int i = 0; i < s.length(); i++ )
    {
    char c = s.charAt(i);
    if ( c == '\'' )
      {
      sb.append('\'');
      sb.append('\'');
      }
    else
      sb.append(c);
    }
  return sb.toString();
  }
//...e

protected String getTerm(String s)
  {
  StringBuffer sql = new StringBuffer("( ");
  String sep = "";
  StringTokenizer st = new StringTokenizer(s, split);
  for ( int i = 0; i < scolumns.size(); i++ )
    {
    sql.append(sep);
    sql.append("\"");
    sql.append(scolumns.elementAt(i));
    sql.append("\"='");
    if ( ! st.hasMoreTokens() )
      return null; // Too few tokens
    sql.append(sqlEscaped(st.nextToken()));
    sql.append("'");
    sep = " AND ";
    }
  if ( st.hasMoreTokens() )
    return null; // Too many tokens
  sql.append(" )");
  return sql.toString();
  }
//...e

protected String getQuery(Set<String> unmapped)
  {
  StringBuffer sql = new StringBuffer("SELECT ");
  String sepComma = "";
  for ( int i = 0; i < scolumns.size(); i++ )
    {
    sql.append(sepComma);
    sql.append("\"");
    sql.append(scolumns.elementAt(i));
    sql.append("\"");
    sepComma = ",";
    }
  sql.append(sepComma);
  sql.append("\"");
  sql.append(dcolumn);
  sql.append("\" FROM ");
  sql.append(table);
  sql.append(" WHERE ");
  String sepOR = "";
  for ( String u : unmapped )
    {
    String sqlTerm = getTerm(u);
    if ( sqlTerm != null )
      {
      sql.append(sepOR);
      sql.append(sqlTerm);
      sepOR = " OR ";
      }
    }
  return sepOR.equals("") ? null : sql.toString();
  }
//...e

public void map(Set<String> unmapped, Map<String,String> mapped)
  throws FlowProcessorException
  {
  String sql = getQuery(unmapped);
  if ( sql == null )
    // No work to do, due to badly formed unmapped inputs
    // Note that this is not a reason to throw an exception
    return;

  for ( int r = 0;; r++ )
    {
    Connection c = null;
    try
      {
      c = DriverManager.getConnection(url, userid, password);
      Statement s = c.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
      ResultSet rs = s.executeQuery(sql);
      ResultSetMetaData rsmd = rs.getMetaData();
      int n = rsmd.getColumnCount();
      while ( rs.next() )
        {
        String sep = "";
        String u = "";
        for ( int i = 1; i <= n-1; i++ )
          {
          u += ( sep + rs.getString(i) );
          sep = split;
          }  
        String m = rs.getString(n);
        unmapped.remove(u);
        mapped.put(u, m);
        }
      break;
      }
    catch ( SQLException e )
      {
      if ( r == retryCount )
        {
        if ( retryFail.equals("throw") )
          throw new FlowProcessorException("error querying database in data map "+name+" ("+retryCount+" retries)", e);
        else
          break;
        }
      try
        {
        Thread.sleep(retryDelay);
        }
      catch ( InterruptedException ei )
        {
        }
      }
    finally
      {
      if ( c != null )
        try
          {
          c.close();
          }
        catch ( SQLException e )
          {
          throw new FlowProcessorException("can't close JDBC connection in data map "+name, e);
          }
      }
    }
  }
//...e
  }
