[PATCHES] Hibernate Dialects for PostgreSQL

[Simon Riggs]

I enclose 3 Dialects for PostgreSQL, as discussed on -hackers.

The PostgreSQL83Dialect is essentially identical to 82Dialect, but the
principle of having one file per release seems easiest way of doing
things.

Further additions very welcome; I don't suppose at all this is the final
version.

-- 
  Simon Riggs
  2ndQuadrant  http://www.2ndQuadrant.com
//$Id: PostgreSQLDialect.java 11367 2007-03-29 13:26:40Z [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
 * 
 * Dialect reorganized to follow sequence in base Dialect, few
 * additions and corrections from earlier PostgreSQLDialect
 *
 * This is the base class for PostgreSQL 8.x versions
 * PostgreSQL 8.0 and 8.1 can use the unmodified, later releases provide
 * further extensions and so are provided as separate Dialects
 * 			[EMAIL PROTECTED] 	12 Nov 2007
 */
public class PostgreSQL8Dialect extends Dialect {

	public PostgreSQL8Dialect() {
		super();

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

		// database type mapping support ~~

		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.CLOB, text );
		registerColumnType( Types.BLOB, oid );
		registerColumnType( Types.NUMERIC, numeric($p, $s) );

		// function support ~~~

		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, 

Re: [PATCHES] Hibernate Dialects for PostgreSQL

[Peter Eisentraut]

Simon Riggs wrote:
 I enclose 3 Dialects for PostgreSQL, as discussed on -hackers.

There is no such thing as PostgreSQL 8.  Please don't do that; it would 
confuse users about the versioning scheme.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [PATCHES] Hibernate Dialects for PostgreSQL

[Simon Riggs]

On Mon, 2007-11-12 at 14:28 +0100, Peter Eisentraut wrote:
 Simon Riggs wrote:
  I enclose 3 Dialects for PostgreSQL, as discussed on -hackers.
 
 There is no such thing as PostgreSQL 8.  Please don't do that; it would 
 confuse users about the versioning scheme.

Would 8x be the right thing then?

So PostgreSQL8xDialect or PostgreSQL80Dialect or ...

-- 
  Simon Riggs
  2ndQuadrant  http://www.2ndQuadrant.com


---(end of broadcast)---
TIP 6: explain analyze is your friend

[PATCHES] Doc patch to clarify text search example

[Simon Riggs]

Short patch to clarify the name of the column used for the text search
index example, so everybody is clear it is a column name.

Unrelated to other discussions on hackers

-- 
  Simon Riggs
  2ndQuadrant  http://www.2ndQuadrant.com
Index: doc/src/sgml/textsearch.sgml
===
RCS file: /home/sriggs/pg/REPOSITORY/pgsql/doc/src/sgml/textsearch.sgml,v
retrieving revision 1.31
diff -c -r1.31 textsearch.sgml
*** doc/src/sgml/textsearch.sgml	10 Nov 2007 15:39:34 -	1.31
--- doc/src/sgml/textsearch.sgml	12 Nov 2007 16:18:25 -
***
*** 538,552 
  indexed when the other is literalNULL/:
  
  programlisting
! ALTER TABLE pgweb ADD COLUMN textsearch_index tsvector;
! UPDATE pgweb SET textsearch_index =
   to_tsvector('english', coalesce(title,'') || coalesce(body,''));
  /programlisting
  
  Then we create a acronymGIN/acronym index to speed up the search:
  
  programlisting
! CREATE INDEX textsearch_idx ON pgweb USING gin(textsearch_index);
  /programlisting
  
  Now we are ready to perform a fast full text search:
--- 538,552 
  indexed when the other is literalNULL/:
  
  programlisting
! ALTER TABLE pgweb ADD COLUMN textsearchable_index_col tsvector;
! UPDATE pgweb SET textsearchable_index_col =
   to_tsvector('english', coalesce(title,'') || coalesce(body,''));
  /programlisting
  
  Then we create a acronymGIN/acronym index to speed up the search:
  
  programlisting
! CREATE INDEX textsearch_idx ON pgweb USING gin(textsearchable_index_col);
  /programlisting
  
  Now we are ready to perform a fast full text search:
***
*** 554,560 
  programlisting
  SELECT title
  FROM pgweb
! WHERE to_tsquery('create amp; table') @@ textsearch_index
  ORDER BY last_mod_date DESC LIMIT 10;
  /programlisting
 /para
--- 554,560 
  programlisting
  SELECT title
  FROM pgweb
! WHERE textsearchable_index_col @@ to_tsquery('create amp; table')
  ORDER BY last_mod_date DESC LIMIT 10;
  /programlisting
 /para

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [PATCHES] Hibernate Dialects for PostgreSQL

[David Fetter]

On Mon, Nov 12, 2007 at 01:41:49PM +, Simon Riggs wrote:
 On Mon, 2007-11-12 at 14:28 +0100, Peter Eisentraut wrote:
  Simon Riggs wrote:
   I enclose 3 Dialects for PostgreSQL, as discussed on -hackers.
  
  There is no such thing as PostgreSQL 8.  Please don't do that; it would 
  confuse users about the versioning scheme.
 
 Would 8x be the right thing then?
 
 So PostgreSQL8xDialect or PostgreSQL80Dialect or ...

I'd say 80 so it's easy to separate off 81, 82, 83, etc. :)

Cheers,
David.
-- 
David Fetter [EMAIL PROTECTED] http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: [EMAIL PROTECTED]

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match