/*
 *  $Id$
 *
 *  This file is part of the OpenLink Software Virtuoso Open-Source (VOS)
 *  project.
 *
 *  Copyright (C) 1998-2018 OpenLink Software
 *
 *  This project is free software; you can redistribute it and/or modify it
 *  under the terms of the GNU General Public License as published by the
 *  Free Software Foundation; only version 2 of the License, dated June 1991.
 *
 *  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.
 *
 *  You should have received a copy of the GNU General Public License along
 *  with this program; if not, write to the Free Software Foundation, Inc.,
 *  51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
 *
 */

package virtuoso.hibernate;

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

import org.hibernate.Hibernate;
import org.hibernate.LockMode;
import org.hibernate.MappingException;
import org.hibernate.dialect.Dialect;
import org.hibernate.dialect.function.NoArgSQLFunction;
import org.hibernate.dialect.function.VarArgsSQLFunction;
import org.hibernate.dialect.function.SQLFunctionTemplate;
import org.hibernate.dialect.function.StandardSQLFunction;
import org.hibernate.dialect.function.AnsiTrimEmulationFunction;
import org.hibernate.util.StringHelper;
import org.hibernate.exception.TemplatedViolatedConstraintNameExtracter;
import org.hibernate.exception.ViolatedConstraintNameExtracter;
import org.hibernate.type.StandardBasicTypes;


/**
 * A dialect for Virtuoso DBMS
 *
 */
public class VirtuosoDialect extends Dialect {

	public VirtuosoDialect() {
		super();

		registerColumnType( Types.BIT, "SMALLINT" );
		registerColumnType( Types.TINYINT, "SMALLINT" );
		registerColumnType( Types.SMALLINT, "SMALLINT" );
		registerColumnType( Types.INTEGER, "INTEGER" );

		registerColumnType( Types.BIGINT, "DECIMAL(20,0)" );

		registerColumnType( Types.REAL, "REAL" );
		registerColumnType( Types.FLOAT, "FLOAT" );
		registerColumnType( Types.DOUBLE, "DOUBLE PRECISION" );
		registerColumnType( Types.NUMERIC, "DECIMAL($p, $s)" );
		registerColumnType( Types.DECIMAL, "DECIMAL($p, $s)" );
		registerColumnType( Types.BINARY, 2000, "BINARY($l)" );
		registerColumnType( Types.VARBINARY, 2000, "VARBINARY($l)" );
		registerColumnType( Types.LONGVARBINARY, "LONG VARBINARY" );
		registerColumnType( Types.CHAR, 2000, "CHARACTER($l)" );
		registerColumnType( Types.VARCHAR, 2000, "VARCHAR($l)" );
		registerColumnType( Types.LONGVARCHAR, "LONG VARCHAR" );
		registerColumnType( Types.DATE, "DATE" );
		registerColumnType( Types.TIME, "TIME" );
		registerColumnType( Types.TIMESTAMP, "DATETIME" );

		registerColumnType( Types.BLOB, "LONG VARBINARY" );
		registerColumnType( Types.CLOB, "LONG VARCHAR" );

///===================

		registerFunction("iszero", new StandardSQLFunction( "iszero", StandardBasicTypes.INTEGER ) );
		registerFunction("atod", new StandardSQLFunction( "atod", StandardBasicTypes.DOUBLE ) );
		registerFunction("atof", new StandardSQLFunction( "atof", StandardBasicTypes.FLOAT ) );
		registerFunction("atoi", new StandardSQLFunction( "atoi", StandardBasicTypes.INTEGER ) );

		registerFunction("mod", new StandardSQLFunction( "mod" ) );
		registerFunction("abs", new StandardSQLFunction( "abs" ) );
		registerFunction("sign", new StandardSQLFunction( "sign", StandardBasicTypes.DOUBLE ) );
		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("sin", new StandardSQLFunction( "sin", StandardBasicTypes.DOUBLE ) );
		registerFunction("tan", new StandardSQLFunction( "tan", StandardBasicTypes.DOUBLE ) );
		registerFunction("cot", new StandardSQLFunction( "cot", StandardBasicTypes.DOUBLE ) );
		registerFunction("frexp", new StandardSQLFunction( "frexp", StandardBasicTypes.DOUBLE ) );
		registerFunction("degrees", new StandardSQLFunction( "degrees", StandardBasicTypes.DOUBLE ) );
		registerFunction("radians", new StandardSQLFunction( "radians", StandardBasicTypes.DOUBLE ) );
		registerFunction("exp", new StandardSQLFunction( "exp", StandardBasicTypes.DOUBLE ) );
		registerFunction("log", new StandardSQLFunction( "log", StandardBasicTypes.DOUBLE ) );
		registerFunction("log10", new StandardSQLFunction( "log10", StandardBasicTypes.DOUBLE ) );
		registerFunction("sqrt", new StandardSQLFunction( "sqrt", StandardBasicTypes.DOUBLE ) );
		registerFunction("atan2", new StandardSQLFunction( "atan2", StandardBasicTypes.DOUBLE ) );
		registerFunction("power", new StandardSQLFunction( "power", StandardBasicTypes.DOUBLE ) );
		registerFunction("ceiling", new StandardSQLFunction( "ceiling", StandardBasicTypes.INTEGER ) );
		registerFunction("floor", new StandardSQLFunction( "floor", StandardBasicTypes.INTEGER ) );
		registerFunction("pi", new NoArgSQLFunction( "pi", StandardBasicTypes.DOUBLE, true ) );
		registerFunction("round", new StandardSQLFunction("round", StandardBasicTypes.DOUBLE) );
		registerFunction("rand", new StandardSQLFunction( "rand") );
		registerFunction("rnd", new StandardSQLFunction( "rnd") );
		registerFunction("randomize", new StandardSQLFunction( "randomize") );


		registerFunction("hash", new StandardSQLFunction( "hash", StandardBasicTypes.INTEGER ) );
		registerFunction("md5_box", new StandardSQLFunction( "md5_box", StandardBasicTypes.STRING ) );
		registerFunction("box_hash", new StandardSQLFunction( "box_hash", StandardBasicTypes.INTEGER ) );
/* Bitwise: */
		registerFunction("bit_and", new StandardSQLFunction( "bit_and", StandardBasicTypes.INTEGER ) );
		registerFunction("bit_or", new StandardSQLFunction( "bit_or", StandardBasicTypes.INTEGER ) );
		registerFunction("bit_xor", new StandardSQLFunction( "bit_xor", StandardBasicTypes.INTEGER ) );
		registerFunction("bit_not", new StandardSQLFunction( "bit_not", StandardBasicTypes.INTEGER ) );
		registerFunction("bit_shift", new StandardSQLFunction( "bit_shift", StandardBasicTypes.INTEGER ) );

// undef=>TRUNCATE
		registerFunction("length", new StandardSQLFunction( "length", StandardBasicTypes.INTEGER ) );
		registerFunction("char_length", new StandardSQLFunction( "char_length", StandardBasicTypes.INTEGER ) );
		registerFunction("character_length", new StandardSQLFunction( "character_length", StandardBasicTypes.INTEGER ) );
		registerFunction("octet_length", new StandardSQLFunction( "octet_length", StandardBasicTypes.INTEGER ) );

		registerFunction("ascii", new StandardSQLFunction("ascii", StandardBasicTypes.INTEGER) );
		registerFunction("chr", new StandardSQLFunction("chr", StandardBasicTypes.CHARACTER) );
		registerFunction("chr1", new StandardSQLFunction("chr1", StandardBasicTypes.CHARACTER) );
		registerFunction("subseq", new StandardSQLFunction("subseq", StandardBasicTypes.STRING ) );
		registerFunction("substring", new StandardSQLFunction("substring", StandardBasicTypes.STRING ) );
		registerFunction("left", new StandardSQLFunction( "left", StandardBasicTypes.STRING ) );
		registerFunction("right", new StandardSQLFunction( "right", StandardBasicTypes.STRING ) );
		registerFunction("ltrim", new StandardSQLFunction("ltrim", StandardBasicTypes.STRING ) );
		registerFunction("rtrim", new StandardSQLFunction("rtrim", StandardBasicTypes.STRING ) );
		registerFunction("trim", new StandardSQLFunction("trim", StandardBasicTypes.STRING ) );

		registerFunction("repeat", new StandardSQLFunction( "repeat", StandardBasicTypes.STRING ) );
		registerFunction("space", new StandardSQLFunction("space", StandardBasicTypes.STRING) );

		registerFunction("make_string", new StandardSQLFunction("make_string", StandardBasicTypes.STRING) );
		registerFunction("make_wstring", new StandardSQLFunction("make_wstring", StandardBasicTypes.STRING) );
		registerFunction("make_bin_string", new StandardSQLFunction("make_bin_string", StandardBasicTypes.BINARY) );
		registerFunction("concatenate", new StandardSQLFunction("concatenate", StandardBasicTypes.STRING) );

		registerFunction("concat", new StandardSQLFunction( "concat", StandardBasicTypes.STRING ) );
		registerFunction("replace", new StandardSQLFunction( "replace", StandardBasicTypes.STRING ) );

		registerFunction("sprintf", new StandardSQLFunction( "sprintf", StandardBasicTypes.STRING ) );
		registerFunction("sprintf_or_null", new StandardSQLFunction( "sprintf_or_null", StandardBasicTypes.STRING ) );
		registerFunction("sprintf_iri", new StandardSQLFunction( "sprintf_iri", StandardBasicTypes.STRING ) );
		registerFunction("sprintf_iri_or_null", new StandardSQLFunction( "sprintf_iri_or_null", StandardBasicTypes.STRING ) );
		
		registerFunction("strchr", new StandardSQLFunction("strchr", StandardBasicTypes.INTEGER) );
		registerFunction("strrchr", new StandardSQLFunction("strrchr", StandardBasicTypes.INTEGER) );
		registerFunction("strstr", new StandardSQLFunction("strstr", StandardBasicTypes.INTEGER) );
		registerFunction("strindex", new StandardSQLFunction("strindex", StandardBasicTypes.INTEGER) );
		registerFunction("strcasestr", new StandardSQLFunction("strcasestr", StandardBasicTypes.INTEGER) );
		registerFunction("locate", new StandardSQLFunction("locate", StandardBasicTypes.INTEGER ) );
		registerFunction("matches_like", new StandardSQLFunction("matches_like", StandardBasicTypes.INTEGER ) );


		registerFunction("__like_min", new StandardSQLFunction( "__like_min", StandardBasicTypes.STRING ) );
		registerFunction("__like_max", new StandardSQLFunction( "__like_max", StandardBasicTypes.STRING ) );
		registerFunction("fix_identifier_case", new StandardSQLFunction( "fix_identifier_case", StandardBasicTypes.STRING ) );
		registerFunction("casemode_strcmp", new StandardSQLFunction("casemode_strcmp", StandardBasicTypes.INTEGER ) );


		registerFunction("lcase", new StandardSQLFunction("lcase", StandardBasicTypes.STRING ) );
		registerFunction("lower", new StandardSQLFunction("lower", StandardBasicTypes.STRING ) );
		registerFunction("ucase", new StandardSQLFunction("ucase", StandardBasicTypes.STRING ) );
		registerFunction("upper", new StandardSQLFunction("upper", StandardBasicTypes.STRING ) );
		registerFunction("initcap", new StandardSQLFunction("initcap", StandardBasicTypes.STRING ) );


		registerFunction("table_type", new StandardSQLFunction("table_type", StandardBasicTypes.STRING ) );
		registerFunction("internal_type_name", new StandardSQLFunction("internal_type_name", StandardBasicTypes.STRING ) );
		registerFunction("internal_type", new StandardSQLFunction("internal_type", StandardBasicTypes.INTEGER ) );
		registerFunction("isinteger", new StandardSQLFunction("isinteger", StandardBasicTypes.INTEGER ) );
		registerFunction("isnumeric", new StandardSQLFunction("isnumeric", StandardBasicTypes.INTEGER ) );
		registerFunction("isfloat", new StandardSQLFunction("isfloat", StandardBasicTypes.INTEGER ) );
		registerFunction("isdouble", new StandardSQLFunction("isdouble", StandardBasicTypes.INTEGER ) );
		registerFunction("isnull", new StandardSQLFunction("isnull", StandardBasicTypes.INTEGER ) );
		registerFunction("isnotnull", new StandardSQLFunction("isnotnull", StandardBasicTypes.INTEGER ) );
		registerFunction("isblob", new StandardSQLFunction("isblob", StandardBasicTypes.INTEGER ) );
		registerFunction("isentity", new StandardSQLFunction("isentity", StandardBasicTypes.INTEGER ) );
		registerFunction("isstring", new StandardSQLFunction("isstring", StandardBasicTypes.INTEGER ) );
		registerFunction("isbinary", new StandardSQLFunction("isbinary", StandardBasicTypes.INTEGER ) );
		registerFunction("isarray", new StandardSQLFunction("isarray", StandardBasicTypes.INTEGER ) );
		registerFunction("isiri_id", new StandardSQLFunction("isiri_id", StandardBasicTypes.INTEGER ) );
		registerFunction("is_named_iri_id", new StandardSQLFunction("is_named_iri_id", StandardBasicTypes.INTEGER ) );
		registerFunction("is_bnode_iri_id", new StandardSQLFunction("is_bnode_iri_id", StandardBasicTypes.INTEGER ) );
		registerFunction("isuname", new StandardSQLFunction("isuname", StandardBasicTypes.INTEGER ) );


		registerFunction("username", new NoArgSQLFunction( "username", StandardBasicTypes.STRING, true ) );
		registerFunction("dbname", new NoArgSQLFunction( "dbname", StandardBasicTypes.STRING, true ) );
		registerFunction("ifnull", new VarArgsSQLFunction( "ifnull(", ",", ")" ) );
		registerFunction("get_user", new NoArgSQLFunction( "get_user", StandardBasicTypes.STRING, true ) );


		registerFunction("dayname", new StandardSQLFunction( "dayname", StandardBasicTypes.STRING ) );
		registerFunction("monthname", new StandardSQLFunction( "monthname", StandardBasicTypes.STRING ) );
		registerFunction("now", new NoArgSQLFunction( "now", StandardBasicTypes.TIMESTAMP ) );
		registerFunction("curdate", new NoArgSQLFunction( "curdate", StandardBasicTypes.DATE ) );
		registerFunction("dayofmonth", new StandardSQLFunction( "dayofmonth", StandardBasicTypes.INTEGER ) );
		registerFunction("dayofweek", new StandardSQLFunction( "dayofweek", StandardBasicTypes.INTEGER ) );
		registerFunction("dayofyear", new StandardSQLFunction( "dayofyear", StandardBasicTypes.INTEGER ) );
		registerFunction("quarter", new StandardSQLFunction( "quarter", StandardBasicTypes.INTEGER ) );
		registerFunction("week", new StandardSQLFunction( "week", StandardBasicTypes.INTEGER ) );
		registerFunction("month", new StandardSQLFunction( "month", StandardBasicTypes.INTEGER ) );
		registerFunction("year", new StandardSQLFunction( "year", StandardBasicTypes.INTEGER ) );
		registerFunction("hour", new StandardSQLFunction( "hour", StandardBasicTypes.INTEGER ) );
		registerFunction("minute", new StandardSQLFunction( "minute", StandardBasicTypes.INTEGER ) );
		registerFunction("second", new StandardSQLFunction( "second", StandardBasicTypes.INTEGER ) );
		registerFunction("timezone", new StandardSQLFunction( "timezone", StandardBasicTypes.INTEGER ) );
		registerFunction("curtime", new StandardSQLFunction( "curtime", StandardBasicTypes.TIME ) );
		registerFunction("getdate", new NoArgSQLFunction( "getdate", StandardBasicTypes.TIMESTAMP ) );
		registerFunction("curdatetime", new NoArgSQLFunction( "curdatetime", StandardBasicTypes.TIMESTAMP ) );

		registerFunction( "datediff", new StandardSQLFunction( "datediff", StandardBasicTypes.INTEGER ) );
		registerFunction( "dateadd", new StandardSQLFunction( "dateadd", StandardBasicTypes.TIMESTAMP ) );
		registerFunction( "timestampdiff", new StandardSQLFunction( "timestampdiff", StandardBasicTypes.INTEGER ) );
		registerFunction( "timestampadd", new StandardSQLFunction( "timestampadd", StandardBasicTypes.TIMESTAMP ) );


//============================
		registerKeyword( "top" );
		registerKeyword( "char" );
		registerKeyword( "int" );
		registerKeyword( "name" );
		registerKeyword( "string" );
		registerKeyword( "intnum" );
		registerKeyword( "approxnum" );
		registerKeyword( "ammsc" );
		registerKeyword( "parameter" );
		registerKeyword( "as" );
		registerKeyword( "or" );
		registerKeyword( "and" );
		registerKeyword( "not" );
		registerKeyword( "uminus" );
		registerKeyword( "all" );
		registerKeyword( "ammsc" );
		registerKeyword( "any" );
		registerKeyword( "attach" );
		registerKeyword( "asc" );
		registerKeyword( "authorization" );
		registerKeyword( "between" );
		registerKeyword( "by" );
		registerKeyword( "character" );
		registerKeyword( "check" );
		registerKeyword( "close" );
		registerKeyword( "commit" );
		registerKeyword( "continue" );
		registerKeyword( "create" );
		registerKeyword( "current" );
		registerKeyword( "cursor" );
		registerKeyword( "decimal" );
		registerKeyword( "declare" );
		registerKeyword( "default" );
		registerKeyword( "delete" );
		registerKeyword( "desc" );
		registerKeyword( "distinct" );
		registerKeyword( "double" );
		registerKeyword( "drop" );
		registerKeyword( "escape" );
		registerKeyword( "exists" );
		registerKeyword( "fetch" );
		registerKeyword( "float" );
		registerKeyword( "for" );
		registerKeyword( "foreign" );
		registerKeyword( "found" );
		registerKeyword( "from" );
		registerKeyword( "goto" );
		registerKeyword( "go" );
		registerKeyword( "grant " );
		registerKeyword( "group" );
		registerKeyword( "having" );
		registerKeyword( "in" );
		registerKeyword( "index" );
		registerKeyword( "indicator" );
		registerKeyword( "insert" );
		registerKeyword( "integer" );
		registerKeyword( "into" );
		registerKeyword( "is" );
		registerKeyword( "key" );
		registerKeyword( "language" );
		registerKeyword( "like" );
		registerKeyword( "nullx" );
		registerKeyword( "numeric" );
		registerKeyword( "of" );
		registerKeyword( "on" );
		registerKeyword( "open" );
		registerKeyword( "option" );
		registerKeyword( "order" );
		registerKeyword( "precision" );
		registerKeyword( "primary" );
		registerKeyword( "privileges" );
		registerKeyword( "procedure" );
		registerKeyword( "public" );
		registerKeyword( "real" );
		registerKeyword( "references" );
		registerKeyword( "rollback" );
		registerKeyword( "schema" );
		registerKeyword( "select" );
		registerKeyword( "set" );
		registerKeyword( "smallint" );
		registerKeyword( "some" );
		registerKeyword( "sqlcode" );
		registerKeyword( "sqlerror" );
		registerKeyword( "table" );
		registerKeyword( "to" );
		registerKeyword( "union" );
		registerKeyword( "unique" );
		registerKeyword( "update" );
		registerKeyword( "user" );
		registerKeyword( "values" );
		registerKeyword( "view" );
		registerKeyword( "whenever" );
		registerKeyword( "where" );
		registerKeyword( "with" );
		registerKeyword( "work" );
		registerKeyword( "continues" );
		registerKeyword( "object_id" );
		registerKeyword( "under" );
		registerKeyword( "clustered" );
		registerKeyword( "varchar" );
		registerKeyword( "varbinary" );
		registerKeyword( "long" );
		registerKeyword( "replacing" );
		registerKeyword( "soft" );
		registerKeyword( "shutdown" );
		registerKeyword( "checkpoint" );
		registerKeyword( "backup" );
		registerKeyword( "replication" );
		registerKeyword( "sync" );
		registerKeyword( "alter" );
		registerKeyword( "add" );
		registerKeyword( "rename" );
		registerKeyword( "disconnect" );
		registerKeyword( "before" );
		registerKeyword( "after" );
		registerKeyword( "instead" );
		registerKeyword( "trigger" );
		registerKeyword( "referencing" );
		registerKeyword( "old" );
		registerKeyword( "procedure" );
		registerKeyword( "function" );
		registerKeyword( "out" );
		registerKeyword( "inout" );
		registerKeyword( "handler" );
		registerKeyword( "if" );
		registerKeyword( "then" );
		registerKeyword( "else" );
		registerKeyword( "elseif" );
		registerKeyword( "while" );
		registerKeyword( "beginx" );
		registerKeyword( "endx" );
		registerKeyword( "equals" );
		registerKeyword( "return" );
		registerKeyword( "call" );
		registerKeyword( "returns" );
		registerKeyword( "do" );
		registerKeyword( "exclusive" );
		registerKeyword( "prefetch" );
		registerKeyword( "sqlstate" );
		registerKeyword( "found" );
		registerKeyword( "revoke" );
		registerKeyword( "password" );
		registerKeyword( "off" );
		registerKeyword( "logx" );
		registerKeyword( "sqlstate" );
		registerKeyword( "timestamp" );
		registerKeyword( "date" );
		registerKeyword( "datetime" );
		registerKeyword( "time" );
		registerKeyword( "execute" );
		registerKeyword( "owner" );
		registerKeyword( "begin_fn_x" );
		registerKeyword( "begin_oj_x" );
		registerKeyword( "convert" );
		registerKeyword( "case" );
		registerKeyword( "when" );
		registerKeyword( "then" );
		registerKeyword( "identity" );
		registerKeyword( "left" );
		registerKeyword( "right" );
		registerKeyword( "full" );
		registerKeyword( "outer" );
		registerKeyword( "join" );
		registerKeyword( "use" );

	}

//???
//	public String getAddColumnString() {
//		return " add";
//	}
//	public String getNullColumnString() {
//		return " null";
//	}


	// IDENTITY support ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
	/**
	 * Does this dialect support identity column key generation?
	 *
	 * @return True if IDENTITY columns are supported; false otherwise.
	 */
	public boolean supportsIdentityColumns() {
		return true;
	}

	/**
	 * Get the select command to use to retrieve the last generated IDENTITY
	 * value.
	 *
	 * @return The appropriate select command
	 * @throws MappingException If IDENTITY generation is not supported.
	 */
	protected String getIdentitySelectString() throws MappingException{
		return "select identity_value()";
	}

	/**
	 * The syntax used during DDL to define a column as being an IDENTITY.
	 *
	 * @return The appropriate DDL fragment.
	 * @throws MappingException If IDENTITY generation is not supported.
	 */
	protected String getIdentityColumnString() throws MappingException {
		// The keyword used to specify an identity column, if identity column key generation is supported.
		return " identity";
	}

	/**
	 * Does the dialect support some form of inserting and selecting
	 * the generated IDENTITY value all in the same statement.
	 *
	 * @return True if the dialect supports selecting the just
	 * generated IDENTITY in the insert statement.
	 */
	public boolean supportsInsertSelectIdentity() {
		return false;
	}


	// SEQUENCE support ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
	/**
	 * Does this dialect support sequences?
	 *
	 * @return True if sequences supported; false otherwise.
	 */
	public boolean supportsSequences() {
		return true;
	}

	/**
	 * Does this dialect support "pooled" sequences.  Not aware of a better
	 * name for this.  Essentially can we specify the initial and increment values?
	 *
	 * @return True if such "pooled" sequences are supported; false otherwise.
	 * @see #getCreateSequenceString(String, int, int)
	 */
	public boolean supportsPooledSequences() {
	    return true;
	}


	/**
	 * Typically dialects which support sequences can create a sequence
	 * with a single command.  This is convenience form of
	 * {@link #getCreateSequenceStrings} to help facilitate that.
	 * <p/>
	 * Dialects which support sequences and can create a sequence in a
	 * single command need *only* override this method.  Dialects
	 * which support sequences but require multiple commands to create
	 * a sequence should instead override {@link #getCreateSequenceStrings}.
	 *
	 * @param sequenceName The name of the sequence
	 * @return The sequence creation command
	 * @throws MappingException If sequences are not supported.
	 */
	protected String getCreateSequenceString(String sequenceName) throws MappingException {
		return "sequence_set('" + sequenceName+"', 0, 1)";
	}

//??NOT SUPPORTED
//	public String getDropSequenceString(String sequenceName) {
//		return "drop sequence " + sequenceName;
//	}

	/**
	 * Generate the select expression fragment that will retreive the next
	 * value of a sequence as part of another (typically DML) statement.
	 * <p/>
	 * This differs from {@link #getSequenceNextValString(String)} in that this
	 * should return an expression usable within another statement.
	 *
	 * @param sequenceName the name of the sequence
	 * @return The "nextval" fragment.
	 * @throws MappingException If sequences are not supported.
	 */
	public String getSelectSequenceNextValString(String sequenceName) throws MappingException {
		return "sequence_next('" + sequenceName +"')";
	}

	/**
	 * Generate the appropriate select statement to to retreive the next value
	 * of a sequence.
	 * <p/>
	 * This should be a "stand alone" select statement.
	 *
	 * @param sequenceName the name of the sequence
	 * @return String The "nextval" select string.
	 * @throws MappingException If sequences are not supported.
	 */
	public String getSequenceNextValString(String sequenceName) throws MappingException {
		return "select sequence_next('" + sequenceName +"')";
	}



	// limit/offset support ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

	/**
	 * Does this dialect support some form of limiting query results
	 * via a SQL clause?
	 *
	 * @return True if this dialect supports some form of LIMIT.
	 */
	public boolean supportsLimit() {
		return true;
	}


	/**
	 * Does the <tt>LIMIT</tt> clause come at the start of the
	 * <tt>SELECT</tt> statement, rather than at the end?
	 *
	 * @return true if limit parameters should come before other parameters
	 */
	public boolean bindLimitParametersFirst() {
		return true;
	}


	/**
	 * Given a limit and an offset, apply the limit clause to the query.
	 *
	 * @param query The query to which to apply the limit.
	 * @param offset The offset of the limit
	 * @param limit The limit of the limit ;)
	 * @return The modified query statement with the limit applied.
	 */
	public String getLimitString(String sql, int offset, int limit) {
		int insertionPoint = sql.toLowerCase().startsWith( "select distinct" ) ? 15 : 6;
		StringBuffer ret = new StringBuffer(sql.length() + 64);

		ret.append(sql);

		if (offset > 0)
		  ret.insert(insertionPoint, " TOP "+offset+","+limit+" ");
		else
		  ret.insert(insertionPoint, " TOP "+limit+" ");

		return ret.toString();
	}

	/**
	 * Apply s limit clause to the query.
	 * <p/>
	 * Typically dialects utilize {@link #supportsVariableLimit() variable}
	 * limit caluses when they support limits.  Thus, when building the
	 * select command we do not actually need to know the limit or the offest
	 * since we will just be using placeholders.
	 * <p/>
	 * Here we do still pass along whether or not an offset was specified
	 * so that dialects not supporting offsets can generate proper exceptions.
	 * In general, dialects will override one or the other of this method and
	 * {@link #getLimitString(String, int, int)}.
	 *
	 * @param query The query to which to apply the limit.
	 * @param hasOffset Is the query requesting an offset?
	 * @return the modified SQL
	 */
	protected String getLimitString(String sql, boolean hasOffset) {
		int insertionPoint = sql.toLowerCase().startsWith( "select distinct" ) ? 15 : 6;

		return new StringBuffer( sql.length() + 16 )
				.append( sql )
				.insert( insertionPoint, (hasOffset ? " TOP ? " : " TOP ?,? " ))
				.toString();

	}


	// callable statement support ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

	/**
	 * Registers an OUT parameter which will be returing a
	 * {@link java.sql.ResultSet}.  How this is accomplished varies greatly
	 * from DB to DB, hence its inclusion (along with {@link #getResultSet}) here.
	 *
	 * @param statement The callable statement.
	 * @param position The bind position at which to register the OUT param.
	 * @return The number of (contiguous) bind positions used.
	 * @throws SQLException Indicates problems registering the OUT param.
	 */
	public int registerResultSetOutParameter(CallableStatement statement, int position) throws SQLException {
		return position;
	}

	/**
	 * Given a callable statement previously processed by {@link #registerResultSetOutParameter},
	 * extract the {@link java.sql.ResultSet} from the OUT parameter.
	 *
	 * @param statement The callable statement.
	 * @return The extracted result set.
	 * @throws SQLException Indicates problems extracting the result set.
	 */
	public ResultSet getResultSet(CallableStatement ps) throws SQLException {
		boolean isResultSet = ps.execute();
		// This assumes you will want to ignore any update counts
		while ( !isResultSet && ps.getUpdateCount() != -1 ) {
			isResultSet = ps.getMoreResults();
		}
		// You may still have other ResultSets or update counts left to process here
		// but you can't do it now or the ResultSet you just got will be closed
		return ps.getResultSet();
	}


	// current timestamp support ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

	/**
	 * Does this dialect support a way to retrieve the database's current
	 * timestamp value?
	 *
	 * @return True if the current timestamp can be retrieved; false otherwise.
	 */
	public boolean supportsCurrentTimestampSelection() {
		return true;
	}

	/**
	 * Should the value returned by {@link #getCurrentTimestampSelectString}
	 * be treated as callable.  Typically this indicates that JDBC escape
	 * sytnax is being used...
	 *
	 * @return True if the {@link #getCurrentTimestampSelectString} return
	 * is callable; false otherwise.
	 */
	public boolean isCurrentTimestampSelectStringCallable() {
		return false;
	}

	/**
	 * Retrieve the command used to retrieve the current timestammp from the
	 * database.
	 *
	 * @return The command.
	 */
	public String getCurrentTimestampSelectString() {
		return "select getdate()";
	}

	/**
	 * The name of the database-specific SQL function for retrieving the
	 * current timestamp.
	 *
	 * @return The function name.
	 */
	public String getCurrentTimestampSQLFunctionName() {
		// the standard SQL function name is current_timestamp...
		return "getdate";
	}



	// SQLException support ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
	public ViolatedConstraintNameExtracter getViolatedConstraintNameExtracter() {
		return EXTRACTER;
	}



	private static ViolatedConstraintNameExtracter EXTRACTER = new TemplatedViolatedConstraintNameExtracter() {
		public String extractConstraintName(SQLException sqle) {
			int er = sqle.getErrorCode();
			String mess = sqle.getMessage();
			if (er == -8) {
				if (mess.startsWith("SR304:")) //DELETE
					return extractUsingTemplate("statement conflicted with COLUMN REFERENCE constraint \"","\"", mess);
				else if (mess.startsWith("SR305:")) //UPDATE
					return extractUsingTemplate("statement conflicted with COLUMN REFERENCE constraint \"","\"", mess);
				else if (mess.startsWith("SR306:")) //INSERT
					return extractUsingTemplate("__03 => 'SR306',\n",":", mess);

				else if (mess.startsWith("SR363:")) //CHECK
					return extractUsingTemplate("__03 => 'SR363',\n",":", mess);

				else if (mess.startsWith("SR175:")) //UNIQUE
					return extractUsingTemplate(": Violating unique index "," on", mess);

				else
					return null;
			} else {
				return null;
			}
		}
	};


	// union subclass support ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

	/**
	 * Given a {@link java.sql.Types} type code, determine an appropriate
	 * null value to use in a select clause.
	 * <p/>
	 * One thing to consider here is that certain databases might
	 * require proper casting for the nulls here since the select here
	 * will be part of a UNION/UNION ALL.
	 *
	 * @param sqlType The {@link java.sql.Types} type code.
	 * @return The appropriate select clause value fragment.
	 */
	public String getSelectClauseNullString(int sqlType) {

		switch ( sqlType ) {
			case Types.BIT:
			case Types.TINYINT:
			case Types.SMALLINT:
				return "cast(null as smallint)";

			case Types.INTEGER:
				return "cast(null as int)";
			case Types.BIGINT:
				return "cast(null as smallint)";
			case Types.FLOAT:
				return "cast(null as float)";
			case Types.REAL:
				return "cast(null as real)";
			case Types.DOUBLE:
				return "cast(null as double precision)";
			case Types.NUMERIC:
			case Types.DECIMAL:
				return "cast(null as decimal)";
			case Types.CHAR:
			case Types.VARCHAR:
				return "cast(null as varchar)";
			case Types.LONGVARCHAR:
			case Types.CLOB:
				return "cast(null as long varchar)";
			case Types.DATE:
				return "cast(null as date)";
			case Types.TIME:
				return "cast(null as time)";
			case Types.TIMESTAMP:
				return "cast(null as datetime)";
			case Types.BINARY:
			case Types.VARBINARY:
				return "cast(null as varbinary)";
			case Types.LONGVARBINARY:
			case Types.BLOB:
				return "cast(null as long varbinary)";
			default:
				return "null";
		}
	}

	/**
	 * Does this dialect support UNION ALL, which is generally a faster
	 * variant of UNION?
	 *
	 * @return True if UNION ALL is supported; false otherwise.
	 */
	public boolean supportsUnionAll() {
		return true;
	}


	// miscellaneous support ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


	/**
	 * The fragment used to insert a row without specifying any column values.
	 * This is not possible on some databases.
	 *
	 * @return The appropriate empty values clause.
	 */
	public String getNoColumnsInsertString() {
		throw new UnsupportedOperationException( "Database can not insert a row without specifying any column values" );
	}


	/**
	 * What is the maximum length Hibernate can use for generated aliases?
	 *
	 * @return The maximum length.
	 */
	public int getMaxAliasLength() {
		return 100;
	}

	/**
	 * The SQL literal value to which this database maps boolean values.
	 *
	 * @param bool The boolean value
	 * @return The appropriate SQL literal.
	 */
	public String toBooleanValueString(boolean bool) {
		return bool ? "1" : "0";
	}


	// DDL support ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


	/**
	 * Do we need to drop constraints before dropping tables in this dialect?
	 *
	 * @return True if constraints must be dropped prior to dropping
	 * the table; false otherwise.
	 */
	public boolean dropConstraints() {
		return false;
	}


        /**
         * Does this dialect support adding Unique constraints via create and alter table ?
         * @return boolean
         */
	public boolean supportsUniqueConstraintInCreateAlterTable() {
	    return true;
	}

	/**
	 * The syntax used to add a column to a table (optional).
	 *
	 * @return The "add column" fragment.
	 */
	public String getAddColumnString() {
		return "add";
	}

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

	/**
	 * The syntax used to add a foreign key constraint to a table.
	 *
	 * @param constraintName The FK constraint name.
	 * @param foreignKey The names of the columns comprising the FK
	 * @param referencedTable The table referenced by the FK
	 * @param primaryKey The explicit columns in the referencedTable referenced
	 * by this FK.
	 * @param referencesPrimaryKey if false, constraint should be
	 * explicit about which column names the constraint refers to
	 *
	 * @return the "add FK" fragment
	 */
	public String getAddForeignKeyConstraintString(
			String constraintName,
			String[] foreignKey,
			String referencedTable,
			String[] primaryKey,
			boolean referencesPrimaryKey) {
		StringBuffer res = new StringBuffer( 300 );

		res.append( " add foreign key (" )
				.append( StringHelper.join( ", ", foreignKey ) )
				.append( ") references " )
				.append( referencedTable );

		if ( !referencesPrimaryKey ) {
			res.append( " (" )
					.append( StringHelper.join( ", ", primaryKey ) )
					.append( ')' );
		}

		return res.toString();
	}

	/**
	 * The syntax used to add a primary key constraint to a table.
	 *
	 * @param constraintName The name of the PK constraint.
	 * @return The "add PK" fragment
	 */
	public String getAddPrimaryKeyConstraintString(String constraintName) {
		return " modify primary key ";
	}

	public boolean hasSelfReferentialForeignKeyBug() {
		return true;
	}

	/**
	 * The keyword used to specify a nullable column.
	 *
	 * @return String
	 */
	public String getNullColumnString() {
		return " null";
	}

	public boolean supportsIfExistsBeforeTableName() {
		return false;
	}

	public boolean supportsIfExistsAfterTableName() {
		return false;
	}



	// Informational metadata ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

	/**
	 * Does this dialect support empty IN lists?
	 * <p/>
	 * For example, is [where XYZ in ()] a supported construct?
	 *
	 * @return True if empty in lists are supported; false otherwise.
	 * @since 3.2
	 */
	public boolean supportsEmptyInList() {
		return false;
	}

	/**
	 * Are string comparisons implicitly case insensitive.
	 * <p/>
	 * In other words, does [where 'XYZ' = 'xyz'] resolve to true?
	 *
	 * @return True if comparisons are case insensitive.
	 * @since 3.2
	 */
	public boolean areStringComparisonsCaseInsensitive() {
		return false;
	}

	/**
	 * Should LOBs (both BLOB and CLOB) be bound using stream operations (i.e.
	 * {@link java.sql.PreparedStatement#setBinaryStream}).
	 *
	 * @return True if BLOBs and CLOBs should be bound using stream operations.
	 * @since 3.2
	 */
	public boolean useInputStreamToInsertBlob() {
		return false;
	}


	/**
	 * Does this dialect support definition of cascade delete constraints
	 * which can cause circular chains?
	 *
	 * @return True if circular cascade delete constraints are supported; false
	 * otherwise.
	 * @since 3.2
	 */
//??
	public boolean supportsCircularCascadeDeleteConstraints() {
		return true;
	}


	/**
	 * Does the dialect support propogating changes to LOB
	 * values back to the database?  Talking about mutating the
	 * internal value of the locator as opposed to supplying a new
	 * locator instance...
	 * <p/>
	 * For BLOBs, the internal value might be changed by:
	 * {@link java.sql.Blob#setBinaryStream},
	 * {@link java.sql.Blob#setBytes(long, byte[])},
	 * {@link java.sql.Blob#setBytes(long, byte[], int, int)},
	 * or {@link java.sql.Blob#truncate(long)}.
	 * <p/>
	 * For CLOBs, the internal value might be changed by:
	 * {@link java.sql.Clob#setAsciiStream(long)},
	 * {@link java.sql.Clob#setCharacterStream(long)},
	 * {@link java.sql.Clob#setString(long, String)},
	 * {@link java.sql.Clob#setString(long, String, int, int)},
	 * or {@link java.sql.Clob#truncate(long)}.
	 * <p/>
	 * NOTE : I do not know the correct answer currently for
	 * databases which (1) are not part of the cruise control process
	 * or (2) do not {@link #supportsExpectedLobUsagePattern}.
	 *
	 * @return True if the changes are propogated back to the
	 * database; false otherwise.
	 * @since 3.2
	 */
	public boolean supportsLobValueChangePropogation() {
		return false;
	}

	/**
	 * Is it supported to materialize a LOB locator outside the transaction in
	 * which it was created?
	 * <p/>
	 * Again, part of the trickiness here is the fact that this is largely
	 * driver dependent.
	 * <p/>
	 * NOTE: all database I have tested which {@link #supportsExpectedLobUsagePattern()}
	 * also support the ability to materialize a LOB outside the owning transaction...
	 *
	 * @return True if unbounded materialization is supported; false otherwise.
	 * @since 3.2
	 */
	public boolean supportsUnboundedLobLocatorMaterialization() {
		return false;
	}

}