001/*
002 * HA-JDBC: High-Availability JDBC
003 * Copyright (C) 2012  Paul Ferraro
004 *
005 * This program is free software: you can redistribute it and/or modify
006 * it under the terms of the GNU Lesser General Public License as published by
007 * the Free Software Foundation, either version 3 of the License, or
008 * (at your option) any later version.
009 *
010 * This program is distributed in the hope that it will be useful,
011 * but WITHOUT ANY WARRANTY; without even the implied warranty of
012 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
013 * GNU Lesser General Public License for more details.
014 *
015 * You should have received a copy of the GNU Lesser General Public License
016 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
017 */
018package net.sf.hajdbc.dialect.postgresql;
019
020import java.io.File;
021import java.sql.Connection;
022import java.sql.DatabaseMetaData;
023import java.sql.ResultSet;
024import java.sql.SQLException;
025import java.sql.Statement;
026import java.sql.Types;
027import java.util.ArrayList;
028import java.util.Arrays;
029import java.util.HashSet;
030import java.util.List;
031import java.util.Set;
032import java.util.regex.Pattern;
033
034import net.sf.hajdbc.ColumnProperties;
035import net.sf.hajdbc.Database;
036import net.sf.hajdbc.DumpRestoreSupport;
037import net.sf.hajdbc.IdentityColumnSupport;
038import net.sf.hajdbc.SequenceSupport;
039import net.sf.hajdbc.TriggerSupport;
040import net.sf.hajdbc.codec.Decoder;
041import net.sf.hajdbc.dialect.ConnectionProperties;
042import net.sf.hajdbc.dialect.StandardDialect;
043import net.sf.hajdbc.util.Processes;
044import net.sf.hajdbc.util.Resources;
045import net.sf.hajdbc.util.Strings;
046
047/**
048 * Dialect for <a href="http://postgresql.org">PostgreSQL</a>.
049 * @author  Paul Ferraro
050 * @since   1.1
051 */
052@SuppressWarnings("nls")
053public class PostgreSQLDialect extends StandardDialect implements DumpRestoreSupport
054{
055        // Taken from: http://www.postgresql.org/docs/9.2/static/sql-keywords-appendix.html
056        public static final String[] RESERVED_KEY_WORDS = new String[] {
057                "ALL", "ANALYSE", "ANALYZE", "AND", "ANY", "ARRAY", "AS", "ASC", "ASYMMETRIC", "AUTHORIZATION",
058                "BINARY", "BOTH",
059                "CASE", "CAST", "CHECK", "COLLATE", "COLLATION", "COLUMN", "CONCURRENTLY", "CONSTRAINT", "CREATE", "CROSS", "CURRENT_CATALOG", "CURRENT_DATE", "CURRENT_ROLE", "CURRENT_SCHEMA", "CURRENT_TIME", "CURRENT_TIMESTAMP", "CURRENT_USER",
060                "DEFAULT", "DEFERRABLE", "DESC", "DISTINCT", "DO",
061                "ELSE", "END", "EXCEPT",
062                "FALSE", "FETCH", "FOR", "FOREIGN", "FREEZE", "FROM", "FULL",
063                "GRANT", "GROUP",
064                "HAVING",
065                "ILIKE", "IN", "INITIALLY", "INNER", "INTERSECT", "INTO", "IS", "ISNULL",
066                "JOIN",
067                "LEADING", "LEFT", "LIKE", "LIMIT", "LOCALTIME", "LOCALTIMESTAMP",
068                "NATURAL", "NOT", "NOTNULL", "NULL",
069                "OFFSET", "ON", "ONLY", "OR", "ORDER", "OUTER", "OVER", "OVERLAPS",
070                "PLACING", "PRIMARY",
071                "REFERENCES", "RETURNING", "RIGHT",
072                "SELECT", "SESSION_USER", "SIMILAR", "SOME", "SYMMETRIC",
073                "TABLE", "THEN", "TO", "TRAILING", "TRUE",
074                "UNION", "UNIQUE", "USER", "USING",
075                "VARIADIC", "VERBOSE",
076                "WHEN", "WHERE", "WINDOW", "WITH",
077        };
078
079        private static final File PASSWORD_FILE = new File(String.format("%s%s.pgpass", Strings.USER_HOME, Strings.FILE_SEPARATOR));
080        
081        /**
082         * {@inheritDoc}
083         * @see net.sf.hajdbc.dialect.StandardDialect#vendorPattern()
084         */
085        @Override
086        protected String vendorPattern()
087        {
088                return "postgresql";
089        }
090
091        /**
092         * PostgreSQL uses a schema search path to locate unqualified table names.
093         * The default search path is [$user,public], where $user is the current user.
094         * @see net.sf.hajdbc.dialect.StandardDialect#getDefaultSchemas(java.sql.DatabaseMetaData)
095         */
096        @Override
097        public List<String> getDefaultSchemas(DatabaseMetaData metaData) throws SQLException
098        {
099                Connection connection = metaData.getConnection();
100                Statement statement = connection.createStatement();
101                
102                try
103                {
104                        ResultSet resultSet = statement.executeQuery("SHOW search_path");
105                        
106                        resultSet.next();
107                        
108                        String[] schemas = resultSet.getString(1).split(Strings.COMMA);
109                        
110                        List<String> schemaList = new ArrayList<String>(schemas.length);
111                        
112                        for (String schema: schemas)
113                        {
114                                schemaList.add(schema.equals("$user") ? metaData.getUserName() : schema);
115                        }
116                        
117                        return schemaList;
118                }
119                finally
120                {
121                        Resources.close(statement);
122                }
123        }
124
125        /**
126         * PostgreSQL uses the native type OID to identify BLOBs.
127         * However the JDBC driver incomprehensibly maps OIDs to INTEGERs.
128         * The PostgreSQL JDBC folks claim this intentional.
129         * @see net.sf.hajdbc.dialect.StandardDialect#getColumnType(net.sf.hajdbc.ColumnProperties)
130         */
131        @Override
132        public int getColumnType(ColumnProperties properties)
133        {
134                return properties.getNativeType().equalsIgnoreCase("oid") ? Types.BLOB : properties.getType();
135        }
136
137        @Override
138        protected Pattern identifierPattern(DatabaseMetaData metaData) throws SQLException
139        {
140                if ((metaData.getDriverMajorVersion() >= 8) && (metaData.getDriverMinorVersion() >= 1))
141                {
142                        return Pattern.compile("[A-Za-z\\0200-\\0377_][A-Za-z\\0200-\\0377_0-9\\$]*");
143                }
144                return super.identifierPattern(metaData);
145        }
146
147        @Override
148        protected Set<String> reservedIdentifiers(DatabaseMetaData metaData)
149        {
150                return new HashSet<String>(Arrays.asList(RESERVED_KEY_WORDS));
151        }
152
153        /**
154         * {@inheritDoc}
155         * @see net.sf.hajdbc.dialect.StandardDialect#getSequenceSupport()
156         */
157        @Override
158        public SequenceSupport getSequenceSupport()
159        {
160                return this;
161        }
162
163        /**
164         * {@inheritDoc}
165         * @see net.sf.hajdbc.dialect.StandardDialect#getIdentityColumnSupport()
166         */
167        @Override
168        public IdentityColumnSupport getIdentityColumnSupport()
169        {
170                return this;
171        }
172
173        /**
174         * @see net.sf.hajdbc.dialect.StandardDialect#truncateTableFormat()
175         */
176        @Override
177        protected String truncateTableFormat()
178        {
179                return "TRUNCATE TABLE {0}";
180        }
181
182        /**
183         * @see net.sf.hajdbc.dialect.StandardDialect#sequencePattern()
184         */
185        @Override
186        protected String sequencePattern()
187        {
188                return "(?:CURR|NEXT)VAL\\s*\\(\\s*'([^']+)'\\s*\\)";
189        }
190
191        /**
192         * @see net.sf.hajdbc.dialect.StandardDialect#nextSequenceValueFormat()
193         */
194        @Override
195        protected String nextSequenceValueFormat()
196        {
197                return "NEXTVAL(''{0}'')";
198        }
199
200        /**
201         * @see net.sf.hajdbc.dialect.StandardDialect#alterIdentityColumnFormat()
202         */
203        @Override
204        protected String alterIdentityColumnFormat()
205        {
206                return "ALTER SEQUENCE {0}_{1}_seq RESTART WITH {2}";
207        }
208
209        /**
210         * @see net.sf.hajdbc.dialect.StandardDialect#currentTimestampPattern()
211         */
212        @Override
213        protected String currentTimestampPattern()
214        {
215                return super.currentTimestampPattern() + "|(?<=\\W)NOW\\s*\\(\\s*\\)|(?<=\\W)TRANSACTION_TIMESTAMP\\s*\\(\\s*\\)|(?<=\\W)STATEMENT_TIMESTAMP\\s*\\(\\s*\\)|(?<=\\W)CLOCK_TIMESTAMP\\s*\\(\\s*\\)";
216        }
217
218        /**
219         * @see net.sf.hajdbc.dialect.StandardDialect#randomPattern()
220         */
221        @Override
222        protected String randomPattern()
223        {
224                return "(?<=\\W)RANDOM\\s*\\(\\s*\\)";
225        }
226
227        /**
228         * Recognizes FOR SHARE and FOR UPDATE.
229         * @see net.sf.hajdbc.dialect.StandardDialect#selectForUpdatePattern()
230         */
231        @Override
232        protected String selectForUpdatePattern()
233        {
234                return "SELECT\\s+.+\\s+FOR\\s+(SHARE|UPDATE)";
235        }
236
237        /**
238         * {@inheritDoc}
239         * @see net.sf.hajdbc.dialect.StandardDialect#getDumpRestoreSupport()
240         */
241        @Override
242        public DumpRestoreSupport getDumpRestoreSupport()
243        {
244                return this;
245        }
246
247        @Override
248        public <Z, D extends Database<Z>> void dump(D database, Decoder decoder, File file, boolean dataOnly) throws Exception
249        {
250                ConnectionProperties properties = this.getConnectionProperties(database, decoder);
251                ProcessBuilder builder = new ProcessBuilder("pg_dump");
252                List<String> args = builder.command();
253                args.add("--host=" + properties.getHost());
254                args.add("--port=" + properties.getPort());
255                args.add("--username=" + properties.getUser());
256                args.add("--no-password");
257                args.add("--file=" + file.getPath());
258                args.add("--format=tar");
259                args.add(properties.getDatabase());
260                Processes.run(setPassword(builder, properties));
261        }
262
263        @Override
264        public <Z, D extends Database<Z>> void restore(D database, Decoder decoder, File file, boolean dataOnly) throws Exception
265        {
266                ConnectionProperties properties = this.getConnectionProperties(database, decoder);
267                ProcessBuilder builder = new ProcessBuilder("pg_restore");
268                List<String> args = builder.command();
269                args.add("--host=" + properties.getHost());
270                args.add("--port=" + properties.getPort());
271                args.add("--username=" + properties.getUser());
272                args.add("--no-password");
273                args.add("--dbname=" + properties.getDatabase());
274                args.add("--clean");
275                if (dataOnly)
276                {
277                        args.add("--data-only");
278                        args.add("--disable-triggers"); // Used to prevent integrity constraints during restoration of data
279                        args.add("--superuser=" + properties.getUser()); // Required by --disable-triggers
280                }
281                args.add(file.getPath());
282                Processes.run(setPassword(builder, properties));
283        }
284        
285        private static ProcessBuilder setPassword(ProcessBuilder builder, ConnectionProperties properties)
286        {
287                String password = properties.getPassword();
288                if ((password != null) && !PASSWORD_FILE.exists())
289                {
290                        Processes.environment(builder).put("PGPASSWORD", properties.getPassword());
291                }
292                return builder;
293        }
294
295        /**
296         * {@inheritDoc}
297         * @see net.sf.hajdbc.dialect.StandardDialect#getTriggerSupport()
298         */
299        @Override
300        public TriggerSupport getTriggerSupport()
301        {
302                return this;
303        }
304
305        /**
306         * {@inheritDoc}
307         * @see net.sf.hajdbc.dialect.StandardDialect#createForeignKeyConstraintFormat()
308         */
309        @Override
310        protected String createTriggerFormat()
311        {
312                return "CREATE FUNCTION {0}_action() BEGIN {4} END; CREATE TRIGGER {0} {1} {2} ON {3} FOR EACH ROW EXECUTE PROCEDURE {0}_action()";
313        }
314
315        /**
316         * {@inheritDoc}
317         * @see net.sf.hajdbc.dialect.StandardDialect#dropTriggerFormat()
318         */
319        @Override
320        protected String dropTriggerFormat()
321        {
322                return "DROP TRIGGER {0} ON {1}; DROP FUNCTION {0}_action()";
323        }
324}