Open Source Repository

Home /hibernate/hibernate-3.2.4.ga | Repository Home


org/hibernate/dialect/MySQLDialect.java
//$Id: MySQLDialect.java 10964 2006-12-08 16:06:49Z [email protected] $
package org.hibernate.dialect;

import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

import org.hibernate.Hibernate;
import org.hibernate.cfg.Environment;
import org.hibernate.dialect.function.NoArgSQLFunction;
import org.hibernate.dialect.function.StandardSQLFunction;
import org.hibernate.util.StringHelper;

/**
 * An SQL dialect for MySQL (prior to 5.x).
 *
 @author Gavin King
 */
public class MySQLDialect extends Dialect {

  public MySQLDialect() {
    super();
    registerColumnTypeTypes.BIT, "bit" );
    registerColumnTypeTypes.BIGINT, "bigint" );
    registerColumnTypeTypes.SMALLINT, "smallint" );
    registerColumnTypeTypes.TINYINT, "tinyint" );
    registerColumnTypeTypes.INTEGER, "integer" );
    registerColumnTypeTypes.CHAR, "char(1)" );
    registerColumnTypeTypes.FLOAT, "float" );
    registerColumnTypeTypes.DOUBLE, "double precision" );
    registerColumnTypeTypes.DATE, "date" );
    registerColumnTypeTypes.TIME, "time" );
    registerColumnTypeTypes.TIMESTAMP, "datetime" );
    registerColumnTypeTypes.VARBINARY, "longblob" );
    registerColumnTypeTypes.VARBINARY, 16777215"mediumblob" );
    registerColumnTypeTypes.VARBINARY, 65535"blob" );
    registerColumnTypeTypes.VARBINARY, 255"tinyblob" );
    registerColumnTypeTypes.NUMERIC, "numeric($p,$s)" );
    registerColumnTypeTypes.BLOB, "longblob" );
    registerColumnTypeTypes.BLOB, 16777215"mediumblob" );
    registerColumnTypeTypes.BLOB, 65535"blob" );
    registerColumnTypeTypes.CLOB, "longtext" );
    registerColumnTypeTypes.CLOB, 16777215"mediumtext" );
    registerColumnTypeTypes.CLOB, 65535"text" );
    registerVarcharTypes();

    registerFunction("ascii"new StandardSQLFunction("ascii", Hibernate.INTEGER) );
    registerFunction("bin"new StandardSQLFunction("bin", Hibernate.STRING) );
    registerFunction("char_length"new StandardSQLFunction("char_length", Hibernate.LONG) );
    registerFunction("character_length"new StandardSQLFunction("character_length", Hibernate.LONG) );
    registerFunction("lcase"new StandardSQLFunction("lcase") );
    registerFunction("lower"new StandardSQLFunction("lower") );
    registerFunction("length"new StandardSQLFunction("length", Hibernate.LONG) );
    registerFunction("ltrim"new StandardSQLFunction("ltrim") );
    registerFunction("ord"new StandardSQLFunction("ord", Hibernate.INTEGER) );
    registerFunction("quote"new StandardSQLFunction("quote") );
    registerFunction("reverse"new StandardSQLFunction("reverse") );
    registerFunction("rtrim"new StandardSQLFunction("rtrim") );
    registerFunction("soundex"new StandardSQLFunction("soundex") );
    registerFunction("space"new StandardSQLFunction("space", Hibernate.STRING) );
    registerFunction("ucase"new StandardSQLFunction("ucase") );
    registerFunction("upper"new StandardSQLFunction("upper") );
    registerFunction("unhex"new StandardSQLFunction("unhex", Hibernate.STRING) );

    registerFunction("abs"new StandardSQLFunction("abs") );
    registerFunction("sign"new StandardSQLFunction("sign", Hibernate.INTEGER) );

    registerFunction("acos"new StandardSQLFunction("acos", Hibernate.DOUBLE) );
    registerFunction("asin"new StandardSQLFunction("asin", Hibernate.DOUBLE) );
    registerFunction("atan"new StandardSQLFunction("atan", Hibernate.DOUBLE) );
    registerFunction("cos"new StandardSQLFunction("cos", Hibernate.DOUBLE) );
    registerFunction("cot"new StandardSQLFunction("cot", Hibernate.DOUBLE) );
    registerFunction("crc32"new StandardSQLFunction("crc32", Hibernate.LONG) );
    registerFunction("exp"new StandardSQLFunction("exp", Hibernate.DOUBLE) );
    registerFunction("ln"new StandardSQLFunction("ln", Hibernate.DOUBLE) );
    registerFunction("log"new StandardSQLFunction("log", Hibernate.DOUBLE) );
    registerFunction("log2"new StandardSQLFunction("log2", Hibernate.DOUBLE) );
    registerFunction("log10"new StandardSQLFunction("log10", Hibernate.DOUBLE) );
    registerFunction("pi"new NoArgSQLFunction("pi", Hibernate.DOUBLE) );
    registerFunction("rand"new NoArgSQLFunction("rand", Hibernate.DOUBLE) );
    registerFunction("sin"new StandardSQLFunction("sin", Hibernate.DOUBLE) );
    registerFunction("sqrt"new StandardSQLFunction("sqrt", Hibernate.DOUBLE) );
    registerFunction("tan"new StandardSQLFunction("tan", Hibernate.DOUBLE) );

    registerFunction("radians"new StandardSQLFunction("radians", Hibernate.DOUBLE) );
    registerFunction("degrees"new StandardSQLFunction("degrees", Hibernate.DOUBLE) );

    registerFunction("ceiling"new StandardSQLFunction("ceiling", Hibernate.INTEGER) );
    registerFunction("ceil"new StandardSQLFunction("ceil", Hibernate.INTEGER) );
    registerFunction("floor"new StandardSQLFunction("floor", Hibernate.INTEGER) );
    registerFunction("round"new StandardSQLFunction("round", Hibernate.INTEGER) );

    registerFunction("datediff"new StandardSQLFunction("datediff", Hibernate.INTEGER) );
    registerFunction("timediff"new StandardSQLFunction("timediff", Hibernate.TIME) );
    registerFunction("date_format"new StandardSQLFunction("date_format", Hibernate.STRING) );

    registerFunction("curdate"new NoArgSQLFunction("curdate", Hibernate.DATE) );
    registerFunction("curtime"new NoArgSQLFunction("curtime", Hibernate.TIME) );
    registerFunction("current_date"new NoArgSQLFunction("current_date", Hibernate.DATE, false) );
    registerFunction("current_time"new NoArgSQLFunction("current_time", Hibernate.TIME, false) );
    registerFunction("current_timestamp"new NoArgSQLFunction("current_timestamp", Hibernate.TIMESTAMP, false) );
    registerFunction("date"new StandardSQLFunction("date", Hibernate.DATE) );
    registerFunction("day"new StandardSQLFunction("day", Hibernate.INTEGER) );
    registerFunction("dayofmonth"new StandardSQLFunction("dayofmonth", Hibernate.INTEGER) );
    registerFunction("dayname"new StandardSQLFunction("dayname", Hibernate.STRING) );
    registerFunction("dayofweek"new StandardSQLFunction("dayofweek", Hibernate.INTEGER) );
    registerFunction("dayofyear"new StandardSQLFunction("dayofyear", Hibernate.INTEGER) );
    registerFunction("from_days"new StandardSQLFunction("from_days", Hibernate.DATE) );
    registerFunction("from_unixtime"new StandardSQLFunction("from_unixtime", Hibernate.TIMESTAMP) );
    registerFunction("hour"new StandardSQLFunction("hour", Hibernate.INTEGER) );
    registerFunction("last_day"new StandardSQLFunction("last_day", Hibernate.DATE) );
    registerFunction("localtime"new NoArgSQLFunction("localtime", Hibernate.TIMESTAMP) );
    registerFunction("localtimestamp"new NoArgSQLFunction("localtimestamp", Hibernate.TIMESTAMP) );
    registerFunction("microseconds"new StandardSQLFunction("microseconds", Hibernate.INTEGER) );
    registerFunction("minute"new StandardSQLFunction("minute", Hibernate.INTEGER) );
    registerFunction("month"new StandardSQLFunction("month", Hibernate.INTEGER) );
    registerFunction("monthname"new StandardSQLFunction("monthname", Hibernate.STRING) );
    registerFunction("now"new NoArgSQLFunction("now", Hibernate.TIMESTAMP) );
    registerFunction("quarter"new StandardSQLFunction("quarter", Hibernate.INTEGER) );
    registerFunction("second"new StandardSQLFunction("second", Hibernate.INTEGER) );
    registerFunction("sec_to_time"new StandardSQLFunction("sec_to_time", Hibernate.TIME) );
    registerFunction("sysdate"new NoArgSQLFunction("sysdate", Hibernate.TIMESTAMP) );
    registerFunction("time"new StandardSQLFunction("time", Hibernate.TIME) );
    registerFunction("timestamp"new StandardSQLFunction("timestamp", Hibernate.TIMESTAMP) );
    registerFunction("time_to_sec"new StandardSQLFunction("time_to_sec", Hibernate.INTEGER) );
    registerFunction("to_days"new StandardSQLFunction("to_days", Hibernate.LONG) );
    registerFunction("unix_timestamp"new StandardSQLFunction("unix_timestamp", Hibernate.LONG) );
    registerFunction("utc_date"new NoArgSQLFunction("utc_date", Hibernate.STRING) );
    registerFunction("utc_time"new NoArgSQLFunction("utc_time", Hibernate.STRING) );
    registerFunction("utc_timestamp"new NoArgSQLFunction("utc_timestamp", Hibernate.STRING) );
    registerFunction("week"new StandardSQLFunction("week", Hibernate.INTEGER) );
    registerFunction("weekday"new StandardSQLFunction("weekday", Hibernate.INTEGER) );
    registerFunction("weekofyear"new StandardSQLFunction("weekofyear", Hibernate.INTEGER) );
    registerFunction("year"new StandardSQLFunction("year", Hibernate.INTEGER) );
    registerFunction("yearweek"new StandardSQLFunction("yearweek", Hibernate.INTEGER) );

    registerFunction("hex"new StandardSQLFunction("hex", Hibernate.STRING) );
    registerFunction("oct"new StandardSQLFunction("oct", Hibernate.STRING) );

    registerFunction("octet_length"new StandardSQLFunction("octet_length", Hibernate.LONG) );
    registerFunction("bit_length"new StandardSQLFunction("bit_length", Hibernate.LONG) );

    registerFunction("bit_count"new StandardSQLFunction("bit_count", Hibernate.LONG) );
    registerFunction("encrypt"new StandardSQLFunction("encrypt", Hibernate.STRING) );
    registerFunction("md5"new StandardSQLFunction("md5", Hibernate.STRING) );
    registerFunction("sha1"new StandardSQLFunction("sha1", Hibernate.STRING) );
    registerFunction("sha"new StandardSQLFunction("sha", Hibernate.STRING) );

    registerFunction"concat"new StandardSQLFunction"concat", Hibernate.STRING ) );

    getDefaultProperties().setProperty(Environment.MAX_FETCH_DEPTH, "2");
    getDefaultProperties().setProperty(Environment.STATEMENT_BATCH_SIZE, DEFAULT_BATCH_SIZE);
  }

  protected void registerVarcharTypes() {
    registerColumnTypeTypes.VARCHAR, "longtext" );
    registerColumnTypeTypes.VARCHAR, 16777215"mediumtext" );
    registerColumnTypeTypes.VARCHAR, 65535"text" );
    registerColumnTypeTypes.VARCHAR, 255"varchar($l)" );
  }

  public String getAddColumnString() {
    return "add column";
  }

  public boolean qualifyIndexName() {
    return false;
  }

  public boolean supportsIdentityColumns() {
    return true;
  }

  public String getIdentitySelectString() {
    return "select last_insert_id()";
  }

  public String getIdentityColumnString() {
    return "not null auto_increment"//starts with 1, implicitly
  }

  public String getAddForeignKeyConstraintString(
      String constraintName,
      String[] foreignKey,
      String referencedTable,
      String[] primaryKey, boolean referencesPrimaryKey
  ) {
    String cols = StringHelper.join(", ", foreignKey);
    return new StringBuffer(30)
      .append(" add index ")
      .append(constraintName)
      .append(" (")
      .append(cols)
      .append("), add constraint ")
      .append(constraintName)
      .append(" foreign key (")
      .append(cols)
      .append(") references ")
      .append(referencedTable)
      .append(" (")
      .appendStringHelper.join(", ", primaryKey) )
      .append(')')
      .toString();
  }

  public boolean supportsLimit() {
    return true;
  }

  public String getDropForeignKeyString() {
    return " drop foreign key ";
  }

  public String getLimitString(String sql, boolean hasOffset) {
    return new StringBuffersql.length()+20 )
      .append(sql)
      .appendhasOffset ? " limit ?, ?" " limit ?")
      .toString();
  }

  /*
   * Temporary, until MySQL fix Connector/J bug
   */
  /*public String getLimitString(String sql, int offset, int limit) {
    StringBuffer buf = new StringBuffer( sql.length()+20 )
      .append(sql);
    if (offset>0) {
      buf.append(" limit ")
        .append(offset)
        .append(", ")
        .append(limit);
    }
    else {
      buf.append(" limit ")
        .append(limit);
    }
    return buf.toString();
  }*/

  /*
   * Temporary, until MySQL fix Connector/J bug
   */
  /*public boolean supportsVariableLimit() {
    return false;
  }*/

  public char closeQuote() {
    return '`';
  }

  public char openQuote() {
    return '`';
  }

  public boolean supportsIfExistsBeforeTableName() {
    return true;
  }

  public String getSelectGUIDString() {
    return "select uuid()";
  }

  public boolean supportsCascadeDelete() {
    return false;
  }

  public String getTableComment(String comment) {
    return " comment='" + comment + "'";
  }

  public String getColumnComment(String comment) {
    return " comment '" + comment + "'";
  }

  public boolean supportsTemporaryTables() {
    return true;
  }

  public String getCreateTemporaryTableString() {
    return "create temporary table if not exists";
  }

  public String getCastTypeName(int code) {
    if code==Types.INTEGER ) {
      return "signed";
    }
    else if code==Types.VARCHAR ) {
      return "char";
    }
    else if code==Types.VARBINARY ) {
      return "binary";
    }
    else {
      return super.getCastTypeNamecode );
    }
  }

  public boolean supportsCurrentTimestampSelection() {
    return true;
  }

  public boolean isCurrentTimestampSelectStringCallable() {
    return false;
  }

  public String getCurrentTimestampSelectString() {
    return "select now()";
  }

  public int registerResultSetOutParameter(CallableStatement statement, int colthrows SQLException {
    return col;
  }

  public ResultSet getResultSet(CallableStatement psthrows SQLException {
    boolean isResultSet = ps.execute();
    while (!isResultSet && ps.getUpdateCount() != -1) {
      isResultSet = ps.getMoreResults();
    }
    return ps.getResultSet();
  }

  public boolean supportsRowValueConstructorSyntax() {
    return true;
  }

  public Boolean performTemporaryTableDDLInIsolation() {
    return Boolean.FALSE;
  }


  // Overridden informational metadata ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

  public boolean supportsEmptyInList() {
    return false;
  }

  public boolean areStringComparisonsCaseInsensitive() {
    return true;
  }

  public boolean supportsLobValueChangePropogation() {
    // note: at least my local MySQL 5.1 install shows this not working...
    return false;
  }

  public boolean supportsSubqueryOnMutatingTable() {
    return false;
  }
}