MySQL.java

/*
 * $Source$
 * $Revision$
 *
 * Copyright (C) 2002 Peter Kehl
 * 
 * Part of Melati (http://melati.org), a framework for the rapid
 * development of clean, maintainable web applications.
 *
 * Melati is free software; Permission is granted to copy, distribute
 * and/or modify this software under the terms either:
 *
 * a) the GNU General Public License as published by the Free Software
 *    Foundation; either version 2 of the License, or (at your option)
 *    any later version,
 *
 *    or
 *
 * b) any version of the Melati Software License, as published
 *    at http://melati.org
 *
 * You should have received a copy of the GNU General Public License and
 * the Melati Software License along with this program;
 * if not, write to the Free Software Foundation, Inc.,
 * 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA to obtain the
 * GNU General Public License and visit http://melati.org to obtain the
 * Melati Software License.
 *
 * Feel free to contact the Developers of Melati (http://melati.org),
 * if you would like to work out a different arrangement than the options
 * outlined here.  It is our intention to allow Melati to be used by as
 * wide an audience as possible.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 *
 */


package org.melati.poem.dbms;

import java.util.Enumeration;
import java.sql.SQLException;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import org.melati.poem.SizedAtomPoemType;
import org.melati.poem.Table;
import org.melati.poem.Column;
import org.melati.poem.PoemType;
import org.melati.poem.SQLPoemType;
import org.melati.poem.IntegerPoemType;
import org.melati.poem.BinaryPoemType;
import org.melati.poem.BooleanPoemType;
import org.melati.poem.StringPoemType;
import org.melati.poem.ParsingPoemException;
import org.melati.poem.SQLPoemException;
import org.melati.poem.TimestampPoemType;
import org.melati.poem.util.StringUtils;

 /**
  * A Driver for MySQL.
  * See http://www.mysql.com.
  *
  * <b>Notes</b>
  * <ol>
  * <li>
  *  Use JDBC URL of type jdbc:mysql://[host][:port]/dbname[?param=value[...]]
  *  ie. the simpliest one has 3 slashes: jdbc:mysql:///melatitest
  * </li>
  * <li>
  *  Don't use asterix * for password, leave it empty (end of line), as:
  * <pre>
  *  org.Melati.LogicalDatabase.melatitest.pass=
  * </pre>
  * 
  * or use explicit username and password and 
  * <pre>
  *   GRANT ALL PRIVILEGES ON dbname
  *  TO username@localhost IDENTIFIED BY 'password';
  * </pre> 
  * </li>
  * <li>
  * If you want to use double quotes to delimit table and column names then 
  * start MySQL in ANSI mode and modify getQuotedName(String name).
  * </li>
  * <li>   
  * Start MySQL with transactioned tables as default. InnoDB is stable,
  *  BDB nearly stable.
  *  <code>getConnection</code> now returns a <code>Connection</code> 
  *  with <code>autocommit</code> turned off through JDBC.
  * 
  *  BDB tables of MySQL-Max 3.23.49 don't support full transactions
  *  - they lock whole table instead, until commit/rollback is called.
  *  According to MySQL 4.0.2-alpha doc, interface between MySQL and
  *  BDB tables is still improved.
  *
  *  As I tested MySQL-Max 3.23.49, InnoDB has correct transactions,
  *  however database size must be specified and reserved in advance
  *  in one file, that is share by all InnoDB tables.
  *  Set in /etc/my.cnf by line like:
  *     innodb_data_file_path=ibdata1:30M
  * <pre>
  *  run
  *  safe_mysqld --user=mysql --ansi --default-table-type=InnoDB
  * </pre>
  * After it created and initialised dB file /var/lib/mysql/ibdata1
  * of 30MB, it creates 2 own log files  /var/lib/mysql/ib_logfile0
  * and ib_logfile1, both of size 5MB.
  * <br>
  * The table type is currently hardcoded in <tt>createTableOptions</tt>.
  *
  * </li>
  * <li>
  *  <tt>boolean</tt> type works (both applications melatitest and contacts).
  *  Because MySQL returns metainfo about BOOL as TINYINT.
  * </li>
  * </ol>
  */
public class MySQL extends AnsiStandard {

  /** Size of indexes. */
  public static final int indexSize = 30;
  /** Size of MySQL text fields. */
  public static final int mysqlTextSize = 65535;

  /** Constructor - sets driver. */
  public MySQL() {
    setDriverClassName("org.gjt.mm.mysql.Driver");
  }

  /**
   * {@inheritDoc}
   * @see org.melati.poem.dbms.AnsiStandard#createTableOptionsSql()
   */
  public String createTableOptionsSql() {
    //MySQL 5.5 doesn't support "type" table option, instead it uses ENGINE option.
    //return " TYPE='InnoDB' "; 

    return " ENGINE='InnoDB' ";
  }

/**
  * Retrieve an SQL type keyword used by the DBMS 
  * for the given Melati type name.
  *
  * @param sqlTypeName the Melati internal type name
  * @return this dbms specific type keyword
  */
  public String getSqlDefinition(String sqlTypeName) {
    if(sqlTypeName.equals("BOOLEAN")) return "bool"; 
    return super.getSqlDefinition(sqlTypeName);
  }

   @Override
  public String getStringSqlDefinition(int size) throws SQLException {
    if (size < 0) { 
      return "text";
    }
    return super.getStringSqlDefinition(size); //VARCHAR(size) is OK
  }

  /**
   * Ignores size.
   */
  @Override
  public String getBinarySqlDefinition(int size) {
    return "BLOB"; 
  }

   @Override
  public String getQuotedName(String name) {
    return unreservedName(name);
  }

 /**
  * Translates a MySQL String into a Poem <code>StringPoemType</code>.
  */ 
  public static class MySQLStringPoemType extends StringPoemType {

    /**
     * Constructor.
     * @param nullable whether nullable
     * @param size size
     */
    public MySQLStringPoemType(boolean nullable, int size) {
      super(nullable, size);
    }

    //MySQL returns metadata info size 65535 for TEXT type
    protected boolean _canRepresent(SQLPoemType<?> other) {
      return
             sqlTypeCode() == other.sqlTypeCode() 
             && other instanceof StringPoemType 
             && (getSize()<0 || getSize()==mysqlTextSize || getSize()>=((StringPoemType)other).getSize());
    }

    /**
     * @see org.melati.poem.SizedAtomPoemType#withSize(int)
     */
    public SizedAtomPoemType<String> withSize(int newSize) {
      if (newSize==mysqlTextSize)
        return super.withSize(-1);
      return super.withSize(newSize);
    }
  }

 /**
  * Translates a MySQL Boolean into a Poem <code>BooleanType</code>.
  */ 
  public static class MySQLBooleanPoemType extends BooleanPoemType {
    /**
     * Constructor.
     * @param nullable whether nullable
     */
    public MySQLBooleanPoemType(boolean nullable) {
      super(nullable);
    }

    protected Boolean _getRaw(ResultSet rs, int col) throws SQLException {
      synchronized (rs) {
        int i = rs.getInt(col);
          return rs.wasNull() ? null :
            (i==1 ? Boolean.TRUE : Boolean.FALSE);
      }
    }
/*
    protected Object _getRaw(ResultSet rs, int col) throws SQLException {
      synchronized (rs) {
        String v = rs.getString(col);
        return rs.wasNull() ? null :
         (v.equals("t") ? Boolean.TRUE : Boolean.FALSE);
      }
    }
*/

    protected void _setRaw(PreparedStatement ps, int col, Object bool)
        throws SQLException {
      ps.setInt(col, ((Boolean)bool).booleanValue() ? 1 : 0);
    }
    /*
    protected void _setRaw(PreparedStatement ps, int col, Object bool)
        throws SQLException {
      if (bool instanceof Boolean && bool == Boolean.TRUE) 
        ps.setString(col, "t");
      else
        ps.setString(col, "f");
    }
    */ 
        
    /**
     * We could use original method from BooleanPoemType,
     * it too recognizes 0/1.
     * {@inheritDoc}
     * @see org.melati.poem.BooleanPoemType#_rawOfString(java.lang.String)
     */
    protected Boolean _rawOfString(String rawString)
        throws ParsingPoemException {
      rawString = rawString.trim();
      switch (rawString.charAt(0)) {
        case '1': return Boolean.TRUE;
        case '0': return Boolean.FALSE;
        default: throw new ParsingPoemException(this, rawString);
      }
    }
  }

 /**
  * Translates a MySQL Blob into a Poem <code>IntegerPoemType</code>.
  */ 
  public static class BlobPoemType extends BinaryPoemType {
 
   /**
    * Constructor.
    * @param nullable whether nullable
    * @param size size
    */
    public BlobPoemType(boolean nullable, int size) {
      super(nullable, size);
    }

    protected boolean _canRepresent(SQLPoemType<?> other) {
      return other instanceof BinaryPoemType;
    }

    @Override
    public <O>PoemType<O> canRepresent(PoemType<O> other) {
      return other instanceof BinaryPoemType &&
          !(!getNullable() && ((BinaryPoemType)other).getNullable()) ?
                       other : null;
    }
  }

   @Override
  public <S,O>PoemType<O> canRepresent(PoemType<S> storage, PoemType<O> other) {
    if (storage instanceof IntegerPoemType &&
        other instanceof BooleanPoemType
        && !(!storage.getNullable() && other.getNullable())  // Nullable may represent not nullable
    ) {
      return other;
    } else {
      return storage.canRepresent(other);
    }
  }

   @Override
  public SQLPoemType<?> defaultPoemTypeOfColumnMetaData(ResultSet md)
      throws SQLException {
    boolean nullable = md.getInt("NULLABLE") == DatabaseMetaData.columnNullable;
    String typeName = md.getString("TYPE_NAME").toLowerCase();
    if(typeName.equals("blob"))
      return new BlobPoemType(nullable, md.getInt("COLUMN_SIZE"));
    else if(typeName.equals("text"))
      return new MySQLStringPoemType(nullable, md.getInt("COLUMN_SIZE"));
    else if(typeName.equals("smallint"))
      return new IntegerPoemType(nullable);
    // See http://www.postgresql.org/docs/current/interactive/datatype-datetime.html
    else if(typeName.equals("datetime"))
      return new TimestampPoemType(nullable);
    else if(typeName.equals("set"))
      return new StringPoemType(nullable, md.getInt("COLUMN_SIZE"));
    
    else if(typeName.equals("char"))
      return new StringPoemType(nullable, md.getInt("COLUMN_SIZE"));
    // MySQL:BOOL --> MySQL:TINYINT --> Melati:boolean backward mapping
    else if(typeName.equals("tinyint"))
      return new MySQLBooleanPoemType(nullable);
    else
      return super.defaultPoemTypeOfColumnMetaData(md);
  }


  @Override
  public SQLPoemException exceptionForUpdate(
        Table<?> table, String sql, boolean insert, SQLException e) {

    String m = e.getMessage();

    // MySQL's duplicate key (or any unique field) message is:
    // "ERROR 1062: Duplicate entry '106' for key 1"
    //  Duplicate index value      <--|           |
    //                                            |
    //  Which 'index' (unique field) it is, in <--|
    //  order as table was defined, starting from 1.

    if (m != null &&
        m.indexOf("1062") >= 0) {

  // It's not simple as in Postgres. This duplicated 'index' is one
  // of possibly more unique columns. That involves searching for its
  // column. For error "Duplicate entry '106' for key 4"
  // we search 4th unique field = we loop over columns, skip first 3 that
  // are unique and return 4th unique.

      try { //Try parsing error message.

        int preIndex, postIndex; //Places of apostrophes around index value
        int preColumn; //Place of "key ", which is in front of column number
    
        preIndex= m.indexOf('\'');
        postIndex= m.lastIndexOf('\'');
        preColumn= m.indexOf("key ");
  
        String indexValue= m.substring(preIndex+1, postIndex);
        String indexColumn= m.substring(preColumn+4);

        System.err.println("Duplicated value " + indexValue +
              " of " + indexColumn + "th unique field."); 
  
        int indexNum= Integer.parseInt(indexColumn);
        Column<?> column = table.troidColumn(); //Just to satisfy compiler.
         //At the end, it will (should) be our column anyway.
      
        for(Enumeration<Column<?>> columns = table.columns(); columns.hasMoreElements();) {
          column = columns.nextElement();
          if(column.getUnique() && (--indexNum == 0))
            break; //We found it!
        }
        //Now, it's found & indexNum==0.
        if(indexNum==0)
          return new DuplicateKeySQLPoemException(column, sql, insert, e);
      } catch(NumberFormatException f) {
          throw new RuntimeException(
              "Number format exception parsing dbms error.");  
      }
      return new DuplicateKeySQLPoemException(table, sql, insert, e);
    }
    return super.exceptionForUpdate(table, sql, insert, e);
  }

  @Override
  public String unreservedName(String name) {
    if(name.equalsIgnoreCase("group")) name = "poem_" + name;
    if(name.equalsIgnoreCase("precision")) name = "poem_" + name;
    if(name.equalsIgnoreCase("unique")) name = "poem_" + name;
    return name;
  }

  @Override
  public String melatiName(String name) {
    if (name == null) return name;
    if(name.equalsIgnoreCase("poem_group")) name = "group";
    if(name.equalsIgnoreCase("poem_precision")) name = "precision";
    if(name.equalsIgnoreCase("poem_unique")) name = "unique";
    return name;
  }

 /**
  * MySQL requires TEXT and BLOB field indices to have an 
  * explicit length, 30 should be fine.
  *
  * @return a snippet of sql to insert into an SQL statement.
  */
  @Override
  public String getIndexLength(Column<?> column) {
    PoemType<?> t = column.getType();
    if (t instanceof StringPoemType && 
        ((StringPoemType)t).getSize() < 0) return "(" + indexSize + ")";
    if (t instanceof BinaryPoemType) return "(" + indexSize + ")";
    return "";
  }

   @Override
  public String givesCapabilitySQL(Integer userTroid, String capabilityExpr) {
    return
        "SELECT groupMembership.* " + 
        "FROM groupMembership LEFT JOIN groupCapability " +
        "ON groupMembership." + getQuotedName("group") +
        " =  groupCapability." + getQuotedName("group") + " " +
        "WHERE " + getQuotedName("user") + " = " + userTroid + " " +
        "AND groupCapability." + getQuotedName("group") + " IS NOT NULL " +
        "AND capability = " + capabilityExpr;
  }

   @Override
  public String caseInsensitiveRegExpSQL(String term1, String term2) {
    if (StringUtils.isQuoted(term2)) {
      term2 = term2.substring(1, term2.length() - 1);
    } 
    term2 = StringUtils.quoted(StringUtils.quoted(term2, '%'), '\'');
    
    return term1 + " LIKE " + term2;
  }

   @Override
  public String alterColumnNotNullableSQL(String tableName, Column<?> column) {
    return "ALTER TABLE " + getQuotedName(tableName) +
    " CHANGE " + getQuotedName(column.getName()) + " " + getQuotedName(column.getName()) +
    " " + 
    column.getSQLType().sqlDefinition(this);
  }
  
 /**
  * MySQL does not do the normal thing. 
  */
  @Override
  public String alterColumnAddCommentSQL(Column<?> column, String comment) {
    return null;
  }

  /**
  * MySQL does not do the normal thing. 
   */
  @Override
  public String alterTableAddCommentSQL(Table<?> table, String comment) {
    return null;
  }
  
}