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}