com.syndiceo.PostgreSQLDialect.java Source code

Java tutorial

Introduction

Here is the source code for com.syndiceo.PostgreSQLDialect.java

Source

/*
 * Hibernate, Relational Persistence for Idiomatic Java
 *
 * Copyright (c) 2010, Red Hat Inc. or third-party contributors as
 * indicated by the @author tags or express copyright attribution
 * statements applied by the authors.  All third-party contributions are
 * distributed under license by Red Hat Inc.
 *
 * This copyrighted material is made available to anyone wishing to use, modify,
 * copy, or redistribute it subject to the terms and conditions of the GNU
 * Lesser General Public License, as published by the Free Software Foundation.
 *
 * 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 Lesser General Public License
 * for more details.
 *
 * You should have received a copy of the GNU Lesser General Public License
 * along with this distribution; if not, write to:
 * Free Software Foundation, Inc.
 * 51 Franklin Street, Fifth Floor
 * Boston, MA  02110-1301  USA
 */
package com.syndiceo;

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

import org.hibernate.HibernateException;
import org.hibernate.LockOptions;
import org.hibernate.cfg.Environment;
import org.hibernate.dialect.Dialect;
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.exception.spi.TemplatedViolatedConstraintNameExtracter;
import org.hibernate.exception.spi.ViolatedConstraintNameExtracter;
import org.hibernate.id.SequenceGenerator;
import org.hibernate.internal.util.JdbcExceptionHelper;
import org.hibernate.type.StandardBasicTypes;
import org.hibernate.type.descriptor.sql.BlobTypeDescriptor;
import org.hibernate.type.descriptor.sql.ClobTypeDescriptor;
import org.hibernate.type.descriptor.sql.SqlTypeDescriptor;

/**
 * An SQL dialect for Postgres
 * <p/>
 * For discussion of BLOB support in Postgres, as of 8.4, have a peek at
 * <a href="http://jdbc.postgresql.org/documentation/84/binary-data.html">http://jdbc.postgresql.org/documentation/84/binary-data.html</a>.
 * For the effects in regards to Hibernate see <a href="http://in.relation.to/15492.lace">http://in.relation.to/15492.lace</a>
 *
 * @author Gavin King
 */
public class PostgreSQLDialect extends Dialect {

    @Override
    public String getHibernateTypeName(int code, int length, int precision, int scale) throws HibernateException {
        if (code == Types.BIGINT)
            return "long";
        return super.getHibernateTypeName(code, length, precision, scale);
    }

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

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

        registerFunction("acos", new StandardSQLFunction("acos", StandardBasicTypes.DOUBLE));
        registerFunction("asin", new StandardSQLFunction("asin", StandardBasicTypes.DOUBLE));
        registerFunction("atan", new StandardSQLFunction("atan", StandardBasicTypes.DOUBLE));
        registerFunction("cos", new StandardSQLFunction("cos", StandardBasicTypes.DOUBLE));
        registerFunction("cot", new StandardSQLFunction("cot", StandardBasicTypes.DOUBLE));
        registerFunction("exp", new StandardSQLFunction("exp", StandardBasicTypes.DOUBLE));
        registerFunction("ln", new StandardSQLFunction("ln", StandardBasicTypes.DOUBLE));
        registerFunction("log", new StandardSQLFunction("log", StandardBasicTypes.DOUBLE));
        registerFunction("sin", new StandardSQLFunction("sin", StandardBasicTypes.DOUBLE));
        registerFunction("sqrt", new StandardSQLFunction("sqrt", StandardBasicTypes.DOUBLE));
        registerFunction("cbrt", new StandardSQLFunction("cbrt", StandardBasicTypes.DOUBLE));
        registerFunction("tan", new StandardSQLFunction("tan", StandardBasicTypes.DOUBLE));
        registerFunction("radians", new StandardSQLFunction("radians", StandardBasicTypes.DOUBLE));
        registerFunction("degrees", new StandardSQLFunction("degrees", StandardBasicTypes.DOUBLE));

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

        registerFunction("random", new NoArgSQLFunction("random", StandardBasicTypes.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", StandardBasicTypes.CHARACTER));
        registerFunction("lower", new StandardSQLFunction("lower"));
        registerFunction("upper", new StandardSQLFunction("upper"));
        registerFunction("substr", new StandardSQLFunction("substr", StandardBasicTypes.STRING));
        registerFunction("initcap", new StandardSQLFunction("initcap"));
        registerFunction("to_ascii", new StandardSQLFunction("to_ascii"));
        registerFunction("quote_ident", new StandardSQLFunction("quote_ident", StandardBasicTypes.STRING));
        registerFunction("quote_literal", new StandardSQLFunction("quote_literal", StandardBasicTypes.STRING));
        registerFunction("md5", new StandardSQLFunction("md5"));
        registerFunction("ascii", new StandardSQLFunction("ascii", StandardBasicTypes.INTEGER));
        registerFunction("char_length", new StandardSQLFunction("char_length", StandardBasicTypes.LONG));
        registerFunction("bit_length", new StandardSQLFunction("bit_length", StandardBasicTypes.LONG));
        registerFunction("octet_length", new StandardSQLFunction("octet_length", StandardBasicTypes.LONG));

        registerFunction("age", new StandardSQLFunction("age"));
        registerFunction("current_date", new NoArgSQLFunction("current_date", StandardBasicTypes.DATE, false));
        registerFunction("current_time", new NoArgSQLFunction("current_time", StandardBasicTypes.TIME, false));
        registerFunction("current_timestamp",
                new NoArgSQLFunction("current_timestamp", StandardBasicTypes.TIMESTAMP, false));
        registerFunction("date_trunc", new StandardSQLFunction("date_trunc", StandardBasicTypes.TIMESTAMP));
        registerFunction("localtime", new NoArgSQLFunction("localtime", StandardBasicTypes.TIME, false));
        registerFunction("localtimestamp",
                new NoArgSQLFunction("localtimestamp", StandardBasicTypes.TIMESTAMP, false));
        registerFunction("now", new NoArgSQLFunction("now", StandardBasicTypes.TIMESTAMP));
        registerFunction("timeofday", new NoArgSQLFunction("timeofday", StandardBasicTypes.STRING));

        registerFunction("current_user", new NoArgSQLFunction("current_user", StandardBasicTypes.STRING, false));
        registerFunction("session_user", new NoArgSQLFunction("session_user", StandardBasicTypes.STRING, false));
        registerFunction("user", new NoArgSQLFunction("user", StandardBasicTypes.STRING, false));
        registerFunction("current_database",
                new NoArgSQLFunction("current_database", StandardBasicTypes.STRING, true));
        registerFunction("current_schema", new NoArgSQLFunction("current_schema", StandardBasicTypes.STRING, true));

        registerFunction("to_char", new StandardSQLFunction("to_char", StandardBasicTypes.STRING));
        registerFunction("to_date", new StandardSQLFunction("to_date", StandardBasicTypes.DATE));
        registerFunction("to_timestamp", new StandardSQLFunction("to_timestamp", StandardBasicTypes.TIMESTAMP));
        registerFunction("to_number", new StandardSQLFunction("to_number", StandardBasicTypes.BIG_DECIMAL));

        registerFunction("concat", new VarArgsSQLFunction(StandardBasicTypes.STRING, "(", "||", ")"));

        registerFunction("locate", new PositionSubstringFunction());

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

        getDefaultProperties().setProperty(Environment.STATEMENT_BATCH_SIZE, DEFAULT_BATCH_SIZE);
        getDefaultProperties().setProperty(Environment.NON_CONTEXTUAL_LOB_CREATION, "true");
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public SqlTypeDescriptor getSqlTypeDescriptorOverride(int sqlCode) {
        SqlTypeDescriptor descriptor;
        switch (sqlCode) {
        case Types.BLOB: {
            descriptor = BlobTypeDescriptor.BLOB_BINDING;
            break;
        }
        case Types.CLOB: {
            descriptor = ClobTypeDescriptor.CLOB_BINDING;
            break;
        }
        default: {
            descriptor = super.getSqlTypeDescriptorOverride(sqlCode);
            break;
        }
        }
        return descriptor;
    }

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

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

    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 StringBuilder(sql.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 StringBuilder().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 String getCaseInsensitiveLike() {
        return "ilike";
    }

    @Override
    public boolean supportsCaseInsensitiveLike() {
        return true;
    }

    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, 1, 1, 0);
        //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 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 boolean supportsTupleDistinctCounts() {
        return false;
    }

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

    public ViolatedConstraintNameExtracter getViolatedConstraintNameExtracter() {
        return EXTRACTER;
    }

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

    public int registerResultSetOutParameter(CallableStatement statement, int col) throws SQLException {
        // Register the type of the out param - PostgreSQL uses Types.OTHER
        statement.registerOutParameter(col++, Types.OTHER);
        return col;
    }

    public ResultSet getResultSet(CallableStatement ps) throws SQLException {
        ps.execute();
        return (ResultSet) ps.getObject(1);
    }

    public boolean supportsPooledSequences() {
        return true;
    }

    //only necessary for postgre < 7.4
    //http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/doc/src/sgml/ref/create_sequence.sgml
    protected String getCreateSequenceString(String sequenceName, int initialValue, int incrementSize) {
        return getCreateSequenceString(sequenceName) + " start " + initialValue + " increment " + incrementSize;
    }

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

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

    @Override
    public boolean supportsIfExistsBeforeTableName() {
        return true;
    }

    public boolean supportsEmptyInList() {
        return false;
    }

    @Override
    public boolean supportsExpectedLobUsagePattern() {
        return true;
    }

    @Override
    public boolean supportsLobValueChangePropogation() {
        return false;
    }

    @Override
    public boolean supportsUnboundedLobLocatorMaterialization() {
        return false;
    }

    // locking support
    public String getForUpdateString() {
        return " for update";
    }

    public String getWriteLockString(int timeout) {
        if (timeout == LockOptions.NO_WAIT)
            return " for update nowait";
        else
            return " for update";
    }

    public String getReadLockString(int timeout) {
        if (timeout == LockOptions.NO_WAIT)
            return " for share nowait";
        else
            return " for share";
    }

}