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 &gt;=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}