// Copyright 2003 Nokia Corporation.
//
// THIS SOURCE CODE IS PROVIDED 'AS IS', WITH NO WARRANTIES WHATSOEVER,
// EXPRESS OR IMPLIED, INCLUDING ANY WARRANTY OF MERCHANTABILITY, FITNESS
// FOR ANY PARTICULAR PURPOSE, OR ARISING FROM A COURSE OF DEALING, USAGE
// OR TRADE PRACTICE, RELATING TO THE SOURCE CODE OR ANY WARRANTY OTHERWISE
// ARISING OUT OF ANY PROPOSAL, SPECIFICATION, OR SAMPLE AND WITH NO
// OBLIGATION OF NOKIA TO PROVIDE THE LICENSEE WITH ANY MAINTENANCE OR
// SUPPORT. FURTHERMORE, NOKIA MAKES NO WARRANTY THAT EXERCISE OF THE
// RIGHTS GRANTED HEREUNDER DOES NOT INFRINGE OR MAY NOT CAUSE INFRINGEMENT
// OF ANY PATENT OR OTHER INTELLECTUAL PROPERTY RIGHTS OWNED OR CONTROLLED
// BY THIRD PARTIES
//
// Furthermore, information provided in this source code is preliminary,
// and may be changed substantially prior to final release. Nokia Corporation
// retains the right to make changes to this source code at
// any time, without notice. This source code is provided for informational
// purposes only.
//
// Nokia and Nokia Connecting People are registered trademarks of Nokia
// Corporation.
// Java and all Java-based marks are trademarks or registered trademarks of
// Sun Microsystems, Inc.
// Other product and company names mentioned herein may be trademarks or
// trade names of their respective owners.
//
// A non-exclusive, non-transferable, worldwide, limited license is hereby
// granted to the Licensee to download, print, reproduce and modify the
// source code. The licensee has the right to market, sell, distribute and
// make available the source code in original or modified form only when
// incorporated into the programs developed by the Licensee. No other
// license, express or implied, by estoppel or otherwise, to any other
// intellectual property rights is granted herein.
package databaseproxy;

import java.io.*;
import java.net.*;
import java.sql.*;
import java.util.*;
import javax.servlet.*;
import javax.servlet.http.*;

public class DatabaseProxyServlet
   extends HttpServlet
{
  private String jdbcDriver = null;
  private String jdbcUrl = null;
  private String jdbcUsername = null;
  private String jdbcPassword = null;
  private String errorMsg = null;


  public void init()
  {
    jdbcDriver = getInitParameter("jdbc.driver");
    jdbcUrl = getInitParameter("jdbc.url");
    jdbcUsername = getInitParameter("jdbc.username");
    jdbcPassword = getInitParameter("jdbc.password");
    Connection c = null;
    if (jdbcDriver != null)
    {
      try
      {
        Class driverClass = Class.forName(jdbcDriver);
        if (driverClass.isAssignableFrom(Driver.class))
        {
          DriverManager.registerDriver(
            (Driver)driverClass.newInstance());
        }
      }
      catch (ClassNotFoundException e)
      {
        errorMsg = "Driver " + jdbcDriver + " not found ";
      }
      catch (ClassCastException e)
      {
        errorMsg = "Driver class " + jdbcDriver +
          " not a valid driver ";
      }
      catch (InstantiationException e)
      {
        errorMsg = "Driver class " + jdbcDriver +
          " could not be instantiated";
      }
      catch (IllegalAccessException e)
      {
        errorMsg = "Driver class " + jdbcDriver + 
          " could not be accesed";
      }
      catch (SQLException e)
      {
        errorMsg = "Exception when initializing driver "
                           + e.getMessage();
      }
    }
  }


  protected void doPost(HttpServletRequest req,
                        HttpServletResponse resp)
    throws ServletException, java.io.IOException
  {
    // check if the JDBC driver could be loaded
    if (errorMsg == null)
    {
      System.err.println("error msg was not null");
      // check that at least query and type are always present
      if (req.getParameter("query") != null)
      {
        String query = req.getParameter("query").toLowerCase();
        int maxrows = Integer.MAX_VALUE;
        try
        {
          if (req.getParameter("maxrows") != null)
          {
            maxrows = Integer.parseInt(req.getParameter("maxrows"));
          }
        }
        catch (NumberFormatException e)
        {
          // ignore. Not an appropriate number
        }
        Connection c = null;
        PreparedStatement s = null;
        StringBuffer response = null;
        try
        {
          // this should probably be implemented using a connection pool
          c = DriverManager.getConnection(jdbcUrl,
                                          jdbcUsername,
                                          jdbcPassword);
          s = c.prepareStatement(query);
          // check if max rows was set
          if (maxrows > 0)
          {
            s.setMaxRows(maxrows);
          }
         
          // parse parameters
          int counter = 1;
          do
          {
            // read parameters
            String value = req.getParameter("value" + counter);
            String type = req.getParameter("type" + counter);
            if (value == null || type == null)
            {
              break;
            }
            
            int typeNumber = -1;
            try
            {
              typeNumber = Integer.parseInt(type);
            }
            catch (NumberFormatException e)
            {
              // not possible to parse the type number
              resp.sendError(HttpServletResponse.SC_BAD_REQUEST,
                             "parameter type" + counter
                             + " not understood");
              return;
            }
            if (!setStatementParameter(s, counter, value, typeNumber))
            {
              // the parameter type is not supported
              resp.sendError(HttpServletResponse.SC_BAD_REQUEST,
                            "parameter type " + counter
                            + " not supported");
              return;
            }
            counter++;
          } while (true);
          
          // check query type
          if (query.startsWith("insert"))
          {
            int result = s.executeUpdate();
            response = new StringBuffer("OK\nINSERT\n").append(result);
          }
          else if (query.startsWith("delete"))
          {
            int result = s.executeUpdate();
            response = new StringBuffer("OK\nDELETE\n").append(result);
          }
          else if (query.startsWith("update"))
          {
            int result = s.executeUpdate();
            response = new StringBuffer("OK\nDELETE\n").append(result);
          }
          else if (query.startsWith("select"))
          {
            ResultSet rs = s.executeQuery();
            response = new StringBuffer("OK\nSELECT\n");
            ResultSetMetaData queryMetadata = rs.getMetaData();
            
            // write column names
            int columnCount = queryMetadata.getColumnCount();
            for (int i = 1; i <= columnCount; i++)
            {
              response.append(queryMetadata.getColumnName(i));
              if (i < columnCount)
              {
                response.append(",");
              }
            }
            response.append("\n");
            
            // write column types
            for (int i = 1; i <= columnCount; i++)
            {
              response.append(queryMetadata.getColumnType(i));
              if (i < columnCount)
              {
                response.append(",");
              }
            }
            response.append("\n");
            
            // write the content
            int rowCount = 0;
            while (rs.next() && rowCount < maxrows)
            {
              for (int i = 1; i <= columnCount; i++)
              {
                if (rs.wasNull())
                {
                  // put a special marker since %0 cannot be produced
                  // in UTF-8 encoding
                  response.append("%0");
                }
                else
                {
                  response.append(URLEncoder.encode(
                    new String(rs.getString(i).getBytes(), "UTF-8")));
                }
                if (i < columnCount)
                {
                  response.append(",");
                }
              }
              response.append("\n");
              rowCount++;
            }
          }
          else
          {
            // respond an error
            response = new StringBuffer("ERROR\n")
                          .append("query type not supported");
          }
        }
        catch (SQLException e)
        {
          // print the error message
          response = new StringBuffer("ERROR\n")
                          .append(e.getMessage());
        }
        finally
        {
          try
          {
            if (c != null)
            {
              c.close();
            }
          }
          catch (SQLException e)
          {
            System.err.println("Exception closing DB connection "
                               + e.getMessage());
          }
        }
        resp.setContentType("text/plain");
        PrintStream out = new PrintStream(resp.getOutputStream());
        out.print(response.toString());
      }
      else
      {
        // respond an error
        resp.sendError(HttpServletResponse.SC_BAD_REQUEST,
                      "parameter query missing");
      }
    }
    else
    {
      // respond a loading message error
      resp.sendError(HttpServletResponse.SC_BAD_REQUEST,
                    errorMsg);
    }
  }


  // parses and set the value of a paramter
  private boolean setStatementParameter(PreparedStatement s,
                                        int index,
                                        String value,
                                        int type)
    throws SQLException
  {
    try
    {
      switch (type)
      {
        case Types.CHAR:
        case Types.VARCHAR:
            s.setString(index, value);
            break;
        case Types.BIGINT:
            s.setLong(index,
            Long.parseLong(value));
            break;
        case Types.TINYINT:
        case Types.SMALLINT:
            s.setShort(index,
            Short.parseShort(value));
            break;
        case Types.BIT:
            s.setBoolean(index,
            Boolean.valueOf(value).booleanValue());
            break;
        case Types.INTEGER:
            s.setInt(index,
            Integer.parseInt(value));
            break;
        default:
            return false;
      }
    }
    catch (NumberFormatException e)
    {
      return false;
    }
    // unsupported types return false
    return true;
  }

}