001/* 002 * HA-JDBC: High-Availability JDBC 003 * Copyright (c) 2004-2007 Paul Ferraro 004 * 005 * This library is free software; you can redistribute it and/or modify it 006 * under the terms of the GNU Lesser General Public License as published by the 007 * Free Software Foundation; either version 2.1 of the License, or (at your 008 * option) any later version. 009 * 010 * This library is distributed in the hope that it will be useful, but WITHOUT 011 * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or 012 * FITNESS FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License 013 * for more details. 014 * 015 * You should have received a copy of the GNU Lesser General Public License 016 * along with this library; if not, write to the Free Software Foundation, 017 * Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA 018 * 019 * Contact: ferraro@users.sourceforge.net 020 */ 021package net.sf.hajdbc.dialect; 022 023import java.sql.Connection; 024import java.sql.DatabaseMetaData; 025import java.sql.ResultSet; 026import java.sql.SQLException; 027import java.sql.Statement; 028import java.sql.Types; 029import java.util.ArrayList; 030import java.util.List; 031import java.util.regex.Pattern; 032 033import net.sf.hajdbc.ColumnProperties; 034import net.sf.hajdbc.util.Strings; 035 036/** 037 * Dialect for <a href="http://postgresql.org">PostgreSQL</a>. 038 * @author Paul Ferraro 039 * @since 1.1 040 */ 041@SuppressWarnings("nls") 042public class PostgreSQLDialect extends StandardDialect 043{ 044 /** 045 * PostgreSQL uses a schema search path to locate unqualified table names. 046 * The default search path is [$user,public], where $user is the current user. 047 * @see net.sf.hajdbc.dialect.StandardDialect#getDefaultSchemas(java.sql.DatabaseMetaData) 048 */ 049 @Override 050 public List<String> getDefaultSchemas(DatabaseMetaData metaData) throws SQLException 051 { 052 Connection connection = metaData.getConnection(); 053 Statement statement = connection.createStatement(); 054 055 ResultSet resultSet = statement.executeQuery("SHOW search_path"); 056 057 resultSet.next(); 058 059 String[] schemas = resultSet.getString(1).split(Strings.COMMA); 060 061 resultSet.close(); 062 statement.close(); 063 064 List<String> schemaList = new ArrayList<String>(schemas.length); 065 066 for (String schema: schemas) 067 { 068 schemaList.add(schema.equals("$user") ? metaData.getUserName() : schema); 069 } 070 071 return schemaList; 072 } 073 074 /** 075 * PostgreSQL uses the native type OID to identify BLOBs. 076 * However the JDBC driver incomprehensibly maps OIDs to INTEGERs. 077 * The PostgreSQL JDBC folks claim this intentional. 078 * @see net.sf.hajdbc.dialect.StandardDialect#getColumnType(net.sf.hajdbc.ColumnProperties) 079 */ 080 @Override 081 public int getColumnType(ColumnProperties properties) 082 { 083 return properties.getNativeType().equalsIgnoreCase("oid") ? Types.BLOB : properties.getType(); 084 } 085 086 /** 087 * Versions >=8.1 of the PostgreSQL JDBC driver return incorrect values for DatabaseMetaData.getExtraNameCharacters(). 088 * @see net.sf.hajdbc.dialect.StandardDialect#getIdentifierPattern(java.sql.DatabaseMetaData) 089 */ 090 @Override 091 public Pattern getIdentifierPattern(DatabaseMetaData metaData) throws SQLException 092 { 093 if ((metaData.getDriverMajorVersion() >= 8) && (metaData.getDriverMinorVersion() >= 1)) 094 { 095 return Pattern.compile("[A-Za-z\\0200-\\0377_][A-Za-z\\0200-\\0377_0-9\\$]*"); 096 } 097 098 return super.getIdentifierPattern(metaData); 099 } 100 101 /** 102 * @see net.sf.hajdbc.dialect.StandardDialect#truncateTableFormat() 103 */ 104 @Override 105 protected String truncateTableFormat() 106 { 107 return "TRUNCATE TABLE {0}"; 108 } 109 110 /** 111 * @see net.sf.hajdbc.dialect.StandardDialect#sequencePattern() 112 */ 113 @Override 114 protected String sequencePattern() 115 { 116 return "(?:CURR|NEXT)VAL\\s*\\(\\s*'([^']+)'\\s*\\)"; 117 } 118 119 /** 120 * @see net.sf.hajdbc.dialect.StandardDialect#nextSequenceValueFormat() 121 */ 122 @Override 123 protected String nextSequenceValueFormat() 124 { 125 return "NEXTVAL(''{0}'')"; 126 } 127 128 /** 129 * @see net.sf.hajdbc.dialect.StandardDialect#alterIdentityColumnFormat() 130 */ 131 @Override 132 protected String alterIdentityColumnFormat() 133 { 134 return "ALTER SEQUENCE {0}_{1}_seq RESTART WITH {2}"; 135 } 136 137 /** 138 * @see net.sf.hajdbc.dialect.StandardDialect#currentTimestampPattern() 139 */ 140 @Override 141 protected String currentTimestampPattern() 142 { 143 return super.currentTimestampPattern() + "|(?<=\\W)NOW\\s*\\(\\s*\\)|(?<=\\W)TRANSACTION_TIMESTAMP\\s*\\(\\s*\\)|(?<=\\W)STATEMENT_TIMESTAMP\\s*\\(\\s*\\)|(?<=\\W)CLOCK_TIMESTAMP\\s*\\(\\s*\\)"; 144 } 145 146 /** 147 * @see net.sf.hajdbc.dialect.StandardDialect#randomPattern() 148 */ 149 @Override 150 protected String randomPattern() 151 { 152 return "(?<=\\W)RANDOM\\s*\\(\\s*\\)"; 153 } 154 155 /** 156 * Recognizes FOR SHARE and FOR UPDATE. 157 * @see net.sf.hajdbc.dialect.StandardDialect#selectForUpdatePattern() 158 */ 159 @Override 160 protected String selectForUpdatePattern() 161 { 162 return "SELECT\\s+.+\\s+FOR\\s+(SHARE|UPDATE)"; 163 } 164}