/*
 * The contents of this file are subject to the Mozilla Public License Version 1.1
 * (the "License"); you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at <http://www.mozilla.org/MPL/>.
 * 
 * Software distributed under the License is distributed on an "AS IS" basis, WITHOUT
 * WARRANTY OF ANY KIND, either express or implied. See the License for the specific
 * language governing rights and limitations under the License.
 * 
 * The Original Code is the Venice Web Communities System.
 * 
 * The Initial Developer of the Original Code is Eric J. Bowersox <erbo@silcom.com>,
 * for Silverwrist Design Studios.  Portions created by Eric J. Bowersox are
 * Copyright (C) 2002-03 Eric J. Bowersox/Silverwrist Design Studios.  All Rights Reserved.
 * 
 * Contributor(s): 
 */
package com.silverwrist.dynamo.db;

import java.sql.*;
import java.util.*;
import com.silverwrist.util.SQLUtils;
import com.silverwrist.dynamo.iface.DBUtilities;

public class DBUtilitiesImpl_mysql extends DBUtilitiesImpl
{
  /*--------------------------------------------------------------------------------
   * Static data members
   *--------------------------------------------------------------------------------
   */

  private static DBUtilitiesImpl_mysql _self = null;

  // used to convert dates and times to UTC for sending to SQL
  private static SimpleTimeZone utc = new SimpleTimeZone(0,"UTC");

  /*--------------------------------------------------------------------------------
   * Constructor
   *--------------------------------------------------------------------------------
   */

  private DBUtilitiesImpl_mysql()
  {
    super();

  } // end constructor

  /*--------------------------------------------------------------------------------
   * Internal operations
   *--------------------------------------------------------------------------------
   */

  /**
   * Converts a date-and-time string (an SQL DATETIME value) to a standard Java date.
   *
   * @param dstr The date-and-time string to convert, presumed to be in UTC.
   * @return The converted date and time.
   * @exception java.sql.SQLException The date and time string was in an invalid format.
   */
  private static final java.util.Date convertDateTimeString(String dstr) throws SQLException
  {
    if (dstr==null)
      return null;  // null values are the same

    try
    { // do almost the reverse process of formatting it into a string
      GregorianCalendar cal = new GregorianCalendar(utc);
      cal.set(Calendar.YEAR,Integer.parseInt(dstr.substring(0,4)));
      cal.set(Calendar.MONTH,Integer.parseInt(dstr.substring(5,7)) - 1 + Calendar.JANUARY);
      cal.set(Calendar.DAY_OF_MONTH,Integer.parseInt(dstr.substring(8,10)));
      cal.set(Calendar.HOUR_OF_DAY,Integer.parseInt(dstr.substring(11,13)));
      cal.set(Calendar.MINUTE,Integer.parseInt(dstr.substring(14,16)));
      cal.set(Calendar.SECOND,Integer.parseInt(dstr.substring(17,19)));
      return cal.getTime();

    } // end try
    catch (NumberFormatException e)
    { // the NumberFormatException becomes an SQLException
      throw new SQLException("invalid DATETIME field format");

    } // end catch

  } // end convertDateString

  /**
   * Encodes a date as an SQL date/time string, expressed in UTC.
   *
   * @param d The date to be encoded.
   * @return The string equivalent of that date.
   */
  private static final String encodeDate(java.util.Date d)
  {
    // Break down the date as a UTC value.
    GregorianCalendar cal = new GregorianCalendar(utc);
    cal.setTime(d);

    // Create the two string buffers converting the date.
    StringBuffer rc = new StringBuffer();
    StringBuffer conv = new StringBuffer();
    String c;
    
    // Encode the year first.
    conv.append("0000").append(cal.get(Calendar.YEAR));
    c = conv.toString();
    rc.append(c.substring(c.length()-4)).append('-');

    // Now the month...
    conv.setLength(0);
    conv.append("00").append(cal.get(Calendar.MONTH) - Calendar.JANUARY + 1);
    c = conv.toString();
    rc.append(c.substring(c.length()-2)).append('-');

    // And the day...
    conv.setLength(0);
    conv.append("00").append(cal.get(Calendar.DAY_OF_MONTH));
    c = conv.toString();
    rc.append(c.substring(c.length()-2)).append(' ');

    // And the hour...
    conv.setLength(0);
    conv.append("00").append(cal.get(Calendar.HOUR_OF_DAY));
    c = conv.toString();
    rc.append(c.substring(c.length()-2)).append(':');

    // And the minute...
    conv.setLength(0);
    conv.append("00").append(cal.get(Calendar.MINUTE));
    c = conv.toString();
    rc.append(c.substring(c.length()-2)).append(':');

    // And the second...
    conv.setLength(0);
    conv.append("00").append(cal.get(Calendar.SECOND));
    c = conv.toString();
    rc.append(c.substring(c.length()-2));

    // This is the resulting date/time value.
    return rc.toString();

  } // end encodeDate

  /*--------------------------------------------------------------------------------
   * Abstract implementations from class DBUtilitiesImpl
   *--------------------------------------------------------------------------------
   */

  public java.util.Date getDateTime(ResultSet rs, String column) throws SQLException
  {
    return convertDateTimeString(rs.getString(column));

  } // end getDateTime

  public java.util.Date getDateTime(ResultSet rs, int column) throws SQLException
  {
    return convertDateTimeString(rs.getString(column));

  } // end getDateTime

  public void setDateTime(PreparedStatement stmt, int index, java.util.Date date) throws SQLException
  {
    stmt.setString(index,encodeDate(date));

  } // end setDateTime

  public void testDatabase(Connection conn) throws SQLException
  {
    Statement stmt = null;
    ResultSet rs = null;
    try
    { // execute a simple SQL statement
      stmt = conn.createStatement();
      rs = stmt.executeQuery("SELECT 1;");
      if (!rs.next())
	throw new SQLException("MySQL database test failed(1)");
      if (rs.getInt(1)!=1)
	throw new SQLException("MySQL database test failed(2)");

    } // end try
    finally
    { // close down statement and result set
      SQLUtils.shutdown(rs);
      SQLUtils.shutdown(conn);

    } // end finally

  } // end testDatabase

  /*--------------------------------------------------------------------------------
   * External static operations
   *--------------------------------------------------------------------------------
   */

  public static DBUtilities get()
  {
    if (_self==null)
      _self = new DBUtilitiesImpl_mysql();
    return _self;

  } // end get

} // end class DBUtilitiesImpl_mysql
