Open Source Repository

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


org/hibernate/dialect/PostgreSQLDialect.java
//$Id: PostgreSQLDialect.java 11366 2007-03-29 13:26:25Z [email protected] $
package org.hibernate.dialect;

import java.sql.Types;
import java.sql.SQLException;

import org.hibernate.Hibernate;
import org.hibernate.exception.ViolatedConstraintNameExtracter;
import org.hibernate.exception.TemplatedViolatedConstraintNameExtracter;
import org.hibernate.exception.JDBCExceptionHelper;
import org.hibernate.cfg.Environment;
import org.hibernate.dialect.function.NoArgSQLFunction;
import org.hibernate.dialect.function.PositionSubstringFunction;
import org.hibernate.dialect.function.SQLFunctionTemplate;
import org.hibernate.dialect.function.StandardSQLFunction;
import org.hibernate.dialect.function.VarArgsSQLFunction;
import org.hibernate.id.SequenceGenerator;

/**
 * An SQL dialect for Postgres
 @author Gavin King
 */
public class PostgreSQLDialect extends Dialect {

  public PostgreSQLDialect() {
    super();
    registerColumnTypeTypes.BIT, "bool" );
    registerColumnTypeTypes.BIGINT, "int8" );
    registerColumnTypeTypes.SMALLINT, "int2" );
    registerColumnTypeTypes.TINYINT, "int2" );
    registerColumnTypeTypes.INTEGER, "int4" );
    registerColumnTypeTypes.CHAR, "char(1)" );
    registerColumnTypeTypes.VARCHAR, "varchar($l)" );
    registerColumnTypeTypes.FLOAT, "float4" );
    registerColumnTypeTypes.DOUBLE, "float8" );
    registerColumnTypeTypes.DATE, "date" );
    registerColumnTypeTypes.TIME, "time" );
    registerColumnTypeTypes.TIMESTAMP, "timestamp" );
    registerColumnTypeTypes.VARBINARY, "bytea" );
    registerColumnTypeTypes.CLOB, "text" );
    registerColumnTypeTypes.BLOB, "oid" );
    registerColumnTypeTypes.NUMERIC, "numeric($p, $s)" );

    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"exp"new StandardSQLFunction("exp", Hibernate.DOUBLE) );
    registerFunction"ln"new StandardSQLFunction("ln", Hibernate.DOUBLE) );
    registerFunction"log"new StandardSQLFunction("log", Hibernate.DOUBLE) );
    registerFunction"sin"new StandardSQLFunction("sin", Hibernate.DOUBLE) );
    registerFunction"sqrt"new StandardSQLFunction("sqrt", Hibernate.DOUBLE) );
    registerFunction"cbrt"new StandardSQLFunction("cbrt", Hibernate.DOUBLE) );
    registerFunction"tan"new StandardSQLFunction("tan", Hibernate.DOUBLE) );
    registerFunction"radians"new StandardSQLFunction("radians", Hibernate.DOUBLE) );
    registerFunction"degrees"new StandardSQLFunction("degrees", Hibernate.DOUBLE) );

    registerFunction"stddev"new StandardSQLFunction("stddev", Hibernate.DOUBLE) );
    registerFunction"variance"new StandardSQLFunction("variance", Hibernate.DOUBLE) );

    registerFunction"random"new NoArgSQLFunction("random", Hibernate.DOUBLE) );

    registerFunction"round"new StandardSQLFunction("round") );
    registerFunction"trunc"new StandardSQLFunction("trunc") );
    registerFunction"ceil"new StandardSQLFunction("ceil") );
    registerFunction"floor"new StandardSQLFunction("floor") );

    registerFunction"chr"new StandardSQLFunction("chr", Hibernate.CHARACTER) );
    registerFunction"lower"new StandardSQLFunction("lower") );
    registerFunction"upper"new StandardSQLFunction("upper") );
    registerFunction"substr"new StandardSQLFunction("substr", Hibernate.STRING) );
    registerFunction"initcap"new StandardSQLFunction("initcap") );
    registerFunction"to_ascii"new StandardSQLFunction("to_ascii") );
    registerFunction"quote_ident"new StandardSQLFunction("quote_ident", Hibernate.STRING) );
    registerFunction"quote_literal"new StandardSQLFunction("quote_literal", Hibernate.STRING) );
    registerFunction"md5"new StandardSQLFunction("md5") );
    registerFunction"ascii"new StandardSQLFunction("ascii", Hibernate.INTEGER) );
    registerFunction"length"new StandardSQLFunction("length", Hibernate.LONG) );
    registerFunction"char_length"new StandardSQLFunction("char_length", Hibernate.LONG) );
    registerFunction"bit_length"new StandardSQLFunction("bit_length", Hibernate.LONG) );
    registerFunction"octet_length"new StandardSQLFunction("octet_length", Hibernate.LONG) );

    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"localtime"new NoArgSQLFunction("localtime", Hibernate.TIME, false) );
    registerFunction"localtimestamp"new NoArgSQLFunction("localtimestamp", Hibernate.TIMESTAMP, false) );
    registerFunction"now"new NoArgSQLFunction("now", Hibernate.TIMESTAMP) );
    registerFunction"timeofday"new NoArgSQLFunction("timeofday", Hibernate.STRING) );
    registerFunction"age"new StandardSQLFunction("age") );

    registerFunction"current_user"new NoArgSQLFunction("current_user", Hibernate.STRING, false) );
    registerFunction"session_user"new NoArgSQLFunction("session_user", Hibernate.STRING, false) );
    registerFunction"user"new NoArgSQLFunction("user", Hibernate.STRING, false) );
    registerFunction"current_database"new NoArgSQLFunction("current_database", Hibernate.STRING, true) );
    registerFunction"current_schema"new NoArgSQLFunction("current_schema", Hibernate.STRING, true) );
    
    registerFunction"to_char"new StandardSQLFunction("to_char", Hibernate.STRING) );
    registerFunction"to_date"new StandardSQLFunction("to_date", Hibernate.DATE) );
    registerFunction"to_timestamp"new StandardSQLFunction("to_timestamp", Hibernate.TIMESTAMP) );
    registerFunction"to_number"new StandardSQLFunction("to_number", Hibernate.BIG_DECIMAL) );

    registerFunction"concat"new VarArgsSQLFunctionHibernate.STRING, "(","||",")" ) );

    registerFunction"locate"new PositionSubstringFunction() );

    registerFunction"str"new SQLFunctionTemplate(Hibernate.STRING, "cast(?1 as varchar)") );

    getDefaultProperties().setProperty(Environment.STATEMENT_BATCH_SIZE, DEFAULT_BATCH_SIZE);
  }

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

  public String getSequenceNextValString(String sequenceName) {
    return "select " + getSelectSequenceNextValStringsequenceName );
  }

  public String getSelectSequenceNextValString(String sequenceName) {
    return "nextval ('" + sequenceName + "')";
  }

  public String getCreateSequenceString(String sequenceName) {
    return "create sequence " + sequenceName; //starts with 1, implicitly
  }

  public String getDropSequenceString(String sequenceName) {
    return "drop sequence " + sequenceName;
  }

  public String getCascadeConstraintsString() {
    return "";//" cascade";
  }
  public boolean dropConstraints() {
    return true;
  }

  public boolean supportsSequences() {
    return true;
  }

  public String getQuerySequencesString() {
    return "select relname from pg_class where relkind='S'";
  }

  public boolean supportsLimit() {
    return true;
  }

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

  public boolean bindLimitParametersInReverseOrder() {
    return true;
  }

  public boolean supportsIdentityColumns() {
    return true;
  }

  public String getForUpdateString(String aliases) {
    return getForUpdateString() " of " + aliases;
  }

  public String getIdentitySelectString(String table, String column, int type) {
    return new StringBuffer().append("select currval('")
      .append(table)
      .append('_')
      .append(column)
      .append("_seq')")
      .toString();
  }

  public String getIdentityColumnString(int type) {
    return type==Types.BIGINT ?
      "bigserial not null" :
      "serial not null";
  }

  public boolean hasDataTypeInIdentityColumn() {
    return false;
  }

  public String getNoColumnsInsertString() {
    return "default values";
  }

  public Class getNativeIdentifierGeneratorClass() {
    return SequenceGenerator.class;
  }

  public boolean supportsOuterJoinForUpdate() {
    return false;
  }
  
  public boolean useInputStreamToInsertBlob() {
    return false;
  }

  public boolean supportsUnionAll() {
    return true;
  }

  /**
   * Workaround for postgres bug #1453
   */
  public String getSelectClauseNullString(int sqlType) {
    String typeName = getTypeName(sqlType, 110);
    //trim off the length/precision/scale
    int loc = typeName.indexOf('(');
    if (loc>-1) {
      typeName = typeName.substring(0, loc);
    }
    return "null::" + typeName;
  }

  public boolean supportsCommentOn() {
    return true;
  }

  public boolean supportsTemporaryTables() {
    return true;
  }

  public String getCreateTemporaryTableString() {
    return "create local temporary table";
  }

  public String getCreateTemporaryTablePostfix() {
    return "on commit drop";
  }

  /*public boolean dropTemporaryTableAfterUse() {
    //we have to, because postgres sets current tx
    //to rollback only after a failed create table
    return true;
  }*/

  public boolean supportsCurrentTimestampSelection() {
    return true;
  }

  public boolean isCurrentTimestampSelectStringCallable() {
    return false;
  }

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

  public String toBooleanValueString(boolean bool) {
    return bool ? "true" "false";
  }

  public ViolatedConstraintNameExtracter getViolatedConstraintNameExtracter() {
    return EXTRACTER;
  }

  /**
   * Constraint-name extractor for Postgres contraint violation exceptions.
   * Orginally contributed by Denny Bartelt.
   */
  private static ViolatedConstraintNameExtracter EXTRACTER = new TemplatedViolatedConstraintNameExtracter() {
    public String extractConstraintName(SQLException sqle) {
      try {
        int sqlState = Integer.valueOfJDBCExceptionHelper.extractSqlState(sqle)).intValue();
        switch (sqlState) {
          // CHECK VIOLATION
          case 23514return extractUsingTemplate("violates check constraint \"","\"", sqle.getMessage());
          // UNIQUE VIOLATION
          case 23505return extractUsingTemplate("violates unique constraint \"","\"", sqle.getMessage());
          // FOREIGN KEY VIOLATION
          case 23503return extractUsingTemplate("violates foreign key constraint \"","\"", sqle.getMessage());
          // NOT NULL VIOLATION
          case 23502return extractUsingTemplate("null value in column \"","\" violates not-null constraint", sqle.getMessage());
          // TODO: RESTRICT VIOLATION
          case 23001return null;
          // ALL OTHER
          defaultreturn null;
        }
      catch (NumberFormatException nfe) {
        return null;
      }
    }
  };


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

// seems to not really...
//  public boolean supportsRowValueConstructorSyntax() {
//    return true;
//  }

  public boolean supportsEmptyInList() {
    return false;
  }

  public boolean supportsExpectedLobUsagePattern() {
    // PostgreSQL seems to have spotty LOB suppport
    return false;
  }
}