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.text.MessageFormat; 029import java.util.Collection; 030import java.util.Collections; 031import java.util.LinkedList; 032import java.util.List; 033import java.util.regex.Matcher; 034import java.util.regex.Pattern; 035 036import net.sf.hajdbc.ColumnProperties; 037import net.sf.hajdbc.Dialect; 038import net.sf.hajdbc.ForeignKeyConstraint; 039import net.sf.hajdbc.QualifiedName; 040import net.sf.hajdbc.SequenceProperties; 041import net.sf.hajdbc.TableProperties; 042import net.sf.hajdbc.UniqueConstraint; 043import net.sf.hajdbc.util.Strings; 044 045/** 046 * @author Paul Ferraro 047 * @since 1.1 048 */ 049@SuppressWarnings("nls") 050public class StandardDialect implements Dialect 051{ 052 private Pattern selectForUpdatePattern = this.compile(this.selectForUpdatePattern()); 053 private Pattern insertIntoTablePattern = this.compile(this.insertIntoTablePattern()); 054 private Pattern sequencePattern = this.compile(this.sequencePattern()); 055 private Pattern currentTimestampPattern = this.compile(this.currentTimestampPattern()); 056 private Pattern currentDatePattern = this.compile(this.currentDatePattern()); 057 private Pattern currentTimePattern = this.compile(this.currentTimePattern()); 058 private Pattern randomPattern = this.compile(this.randomPattern()); 059 060 private Pattern compile(String pattern) 061 { 062 return Pattern.compile(pattern, Pattern.CASE_INSENSITIVE); 063 } 064 065 protected String selectForUpdatePattern() 066 { 067 return "SELECT\\s+.+\\s+FOR\\s+UPDATE"; 068 } 069 070 protected String insertIntoTablePattern() 071 { 072 return "INSERT\\s+(?:INTO\\s+)?'?([^'\\s\\(]+)"; 073 } 074 075 protected String sequencePattern() 076 { 077 return "NEXT\\s+VALUE\\s+FOR\\s+'?([^',\\s\\(\\)]+)"; 078 } 079 080 protected String currentDatePattern() 081 { 082 return "(?<=\\W)CURRENT_DATE(?=\\W)"; 083 } 084 085 protected String currentTimePattern() 086 { 087 return "(?<=\\W)CURRENT_TIME(?:\\s*\\(\\s*\\d+\\s*\\))?(?=\\W)|(?<=\\W)LOCALTIME(?:\\s*\\(\\s*\\d+\\s*\\))?(?=\\W)"; 088 } 089 090 protected String currentTimestampPattern() 091 { 092 return "(?<=\\W)CURRENT_TIMESTAMP(?:\\s*\\(\\s*\\d+\\s*\\))?(?=\\W)|(?<=\\W)LOCALTIMESTAMP(?:\\s*\\(\\s*\\d+\\s*\\))?(?=\\W)"; 093 } 094 095 protected String randomPattern() 096 { 097 return "(?<=\\W)RAND\\s*\\(\\s*\\)"; 098 } 099 100 /** 101 * @see net.sf.hajdbc.Dialect#getSimpleSQL() 102 */ 103 @Override 104 public String getSimpleSQL() 105 { 106 return this.executeFunctionSQL(this.currentTimestampFunction()); 107 } 108 109 protected String executeFunctionFormat() 110 { 111 StringBuilder builder = new StringBuilder("SELECT {0}"); 112 113 String dummyTable = this.dummyTable(); 114 115 if (dummyTable != null) 116 { 117 builder.append(" FROM ").append(dummyTable); 118 } 119 120 return builder.toString(); 121 } 122 123 protected String executeFunctionSQL(String function) 124 { 125 return MessageFormat.format(this.executeFunctionFormat(), function); 126 } 127 128 protected String currentTimestampFunction() 129 { 130 return "CURRENT_TIMESTAMP"; 131 } 132 133 protected String dummyTable() 134 { 135 return null; 136 } 137 138 /** 139 * @see net.sf.hajdbc.Dialect#getTruncateTableSQL(net.sf.hajdbc.TableProperties) 140 */ 141 @Override 142 public String getTruncateTableSQL(TableProperties properties) 143 { 144 return MessageFormat.format(this.truncateTableFormat(), properties.getName()); 145 } 146 147 protected String truncateTableFormat() 148 { 149 return "DELETE FROM {0}"; 150 } 151 152 /** 153 * @see net.sf.hajdbc.Dialect#getCreateForeignKeyConstraintSQL(net.sf.hajdbc.ForeignKeyConstraint) 154 */ 155 @Override 156 public String getCreateForeignKeyConstraintSQL(ForeignKeyConstraint key) 157 { 158 return MessageFormat.format(this.createForeignKeyConstraintFormat(), key.getName(), key.getTable(), Strings.join(key.getColumnList(), Strings.PADDED_COMMA), key.getForeignTable(), Strings.join(key.getForeignColumnList(), Strings.PADDED_COMMA), key.getDeleteRule(), key.getUpdateRule(), key.getDeferrability()); 159 } 160 161 protected String createForeignKeyConstraintFormat() 162 { 163 return "ALTER TABLE {1} ADD CONSTRAINT {0} FOREIGN KEY ({2}) REFERENCES {3} ({4}) ON DELETE {5,choice,0#CASCADE|1#RESTRICT|2#SET NULL|3#NO ACTION|4#SET DEFAULT} ON UPDATE {6,choice,0#CASCADE|1#RESTRICT|2#SET NULL|3#NO ACTION|4#SET DEFAULT} {7,choice,5#DEFERRABLE INITIALLY DEFERRED|6#DEFERRABLE INITIALLY IMMEDIATE|7#NOT DEFERRABLE}"; 164 } 165 166 /** 167 * @see net.sf.hajdbc.Dialect#getDropForeignKeyConstraintSQL(net.sf.hajdbc.ForeignKeyConstraint) 168 */ 169 @Override 170 public String getDropForeignKeyConstraintSQL(ForeignKeyConstraint key) 171 { 172 return MessageFormat.format(this.dropForeignKeyConstraintFormat(), key.getName(), key.getTable()); 173 } 174 175 protected String dropForeignKeyConstraintFormat() 176 { 177 return this.dropConstraintFormat(); 178 } 179 180 protected String dropConstraintFormat() 181 { 182 return "ALTER TABLE {1} DROP CONSTRAINT {0}"; 183 } 184 185 /** 186 * @see net.sf.hajdbc.Dialect#getCreateUniqueConstraintSQL(net.sf.hajdbc.UniqueConstraint) 187 */ 188 @Override 189 public String getCreateUniqueConstraintSQL(UniqueConstraint constraint) 190 { 191 return MessageFormat.format(this.createUniqueConstraintFormat(), constraint.getName(), constraint.getTable(), Strings.join(constraint.getColumnList(), Strings.PADDED_COMMA)); 192 } 193 194 protected String createUniqueConstraintFormat() 195 { 196 return "ALTER TABLE {1} ADD CONSTRAINT {0} UNIQUE ({2})"; 197 } 198 199 /** 200 * @see net.sf.hajdbc.Dialect#getDropUniqueConstraintSQL(net.sf.hajdbc.UniqueConstraint) 201 */ 202 @Override 203 public String getDropUniqueConstraintSQL(UniqueConstraint constraint) 204 { 205 return MessageFormat.format(this.dropUniqueConstraintFormat(), constraint.getName(), constraint.getTable()); 206 } 207 208 protected String dropUniqueConstraintFormat() 209 { 210 return this.dropConstraintFormat(); 211 } 212 213 /** 214 * @see net.sf.hajdbc.Dialect#isSelectForUpdate(java.lang.String) 215 */ 216 @Override 217 public boolean isSelectForUpdate(String sql) 218 { 219 return this.selectForUpdatePattern.matcher(sql).find(); 220 } 221 222 /** 223 * @see net.sf.hajdbc.Dialect#parseInsertTable(java.lang.String) 224 */ 225 @Override 226 public String parseInsertTable(String sql) 227 { 228 return this.parse(this.insertIntoTablePattern, sql); 229 } 230 231 /** 232 * @see net.sf.hajdbc.Dialect#getDefaultSchemas(java.sql.DatabaseMetaData) 233 */ 234 @Override 235 public List<String> getDefaultSchemas(DatabaseMetaData metaData) throws SQLException 236 { 237 return Collections.singletonList(metaData.getUserName()); 238 } 239 240 protected String executeFunction(Connection connection, String function) throws SQLException 241 { 242 Statement statement = connection.createStatement(); 243 244 ResultSet resultSet = statement.executeQuery(this.executeFunctionSQL(function)); 245 246 resultSet.next(); 247 248 String value = resultSet.getString(1); 249 250 resultSet.close(); 251 statement.close(); 252 253 return value; 254 } 255 256 protected List<String> executeQuery(Connection connection, String sql) throws SQLException 257 { 258 List<String> resultList = new LinkedList<String>(); 259 260 Statement statement = connection.createStatement(); 261 262 ResultSet resultSet = statement.executeQuery(sql); 263 264 while (resultSet.next()) 265 { 266 resultList.add(resultSet.getString(1)); 267 } 268 269 resultSet.close(); 270 statement.close(); 271 272 return resultList; 273 } 274 275 /** 276 * @see net.sf.hajdbc.Dialect#parseSequence(java.lang.String) 277 */ 278 @Override 279 public String parseSequence(String sql) 280 { 281 return this.parse(this.sequencePattern, sql); 282 } 283 284 /** 285 * @see net.sf.hajdbc.Dialect#getColumnType(net.sf.hajdbc.ColumnProperties) 286 */ 287 @Override 288 public int getColumnType(ColumnProperties properties) 289 { 290 return properties.getType(); 291 } 292 293 /** 294 * @see net.sf.hajdbc.Dialect#getSequences(java.sql.DatabaseMetaData) 295 */ 296 @Override 297 public Collection<QualifiedName> getSequences(DatabaseMetaData metaData) throws SQLException 298 { 299 List<QualifiedName> sequenceList = new LinkedList<QualifiedName>(); 300 301 ResultSet resultSet = metaData.getTables(Strings.EMPTY, null, Strings.ANY, new String[] { this.sequenceTableType() }); 302 303 while (resultSet.next()) 304 { 305 sequenceList.add(new QualifiedName(resultSet.getString("TABLE_SCHEM"), resultSet.getString("TABLE_NAME"))); 306 } 307 308 resultSet.close(); 309 310 return sequenceList; 311 } 312 313 protected String sequenceTableType() 314 { 315 return "SEQUENCE"; 316 } 317 318 /** 319 * @see net.sf.hajdbc.Dialect#getNextSequenceValueSQL(net.sf.hajdbc.SequenceProperties) 320 */ 321 @Override 322 public String getNextSequenceValueSQL(SequenceProperties sequence) 323 { 324 return this.executeFunctionSQL(MessageFormat.format(this.nextSequenceValueFormat(), sequence.getName())); 325 } 326 327 protected String nextSequenceValueFormat() 328 { 329 return "NEXT VALUE FOR {0}"; 330 } 331 332 /** 333 * @see net.sf.hajdbc.Dialect#getAlterSequenceSQL(net.sf.hajdbc.SequenceProperties, long) 334 */ 335 @Override 336 public String getAlterSequenceSQL(SequenceProperties sequence, long value) 337 { 338 return MessageFormat.format(this.alterSequenceFormat(), sequence.getName(), String.valueOf(value)); 339 } 340 341 protected String alterSequenceFormat() 342 { 343 return "ALTER SEQUENCE {0} RESTART WITH {1}"; 344 } 345 346 @Override 347 public String getAlterIdentityColumnSQL(TableProperties table, ColumnProperties column, long value) throws SQLException 348 { 349 return MessageFormat.format(this.alterIdentityColumnFormat(), table.getName(), column.getName(), String.valueOf(value)); 350 } 351 352 protected String alterIdentityColumnFormat() 353 { 354 return "ALTER TABLE {0} ALTER COLUMN {1} RESTART WITH {2}"; 355 } 356 357 /** 358 * @see net.sf.hajdbc.Dialect#getIdentifierPattern(java.sql.DatabaseMetaData) 359 */ 360 @Override 361 public Pattern getIdentifierPattern(DatabaseMetaData metaData) throws SQLException 362 { 363 return Pattern.compile(MessageFormat.format("[a-zA-Z][\\w{0}]*", Pattern.quote(metaData.getExtraNameCharacters()))); 364 } 365 366 protected String parse(Pattern pattern, String string) 367 { 368 Matcher matcher = pattern.matcher(string); 369 370 return matcher.find() ? matcher.group(1) : null; 371 } 372 373 /** 374 * @see net.sf.hajdbc.Dialect#evaluateCurrentDate(java.lang.String, java.sql.Date) 375 */ 376 @Override 377 public String evaluateCurrentDate(String sql, java.sql.Date date) 378 { 379 return this.evaluateTemporal(sql, this.currentDatePattern, date, this.dateLiteralFormat()); 380 } 381 382 protected String dateLiteralFormat() 383 { 384 return "DATE ''{0}''"; 385 } 386 387 /** 388 * @see net.sf.hajdbc.Dialect#evaluateCurrentTime(java.lang.String, java.sql.Time) 389 */ 390 @Override 391 public String evaluateCurrentTime(String sql, java.sql.Time time) 392 { 393 return this.evaluateTemporal(sql, this.currentTimePattern, time, this.timeLiteralFormat()); 394 } 395 396 protected String timeLiteralFormat() 397 { 398 return "TIME ''{0}''"; 399 } 400 401 /** 402 * @see net.sf.hajdbc.Dialect#evaluateCurrentTimestamp(java.lang.String, java.sql.Timestamp) 403 */ 404 @Override 405 public String evaluateCurrentTimestamp(String sql, java.sql.Timestamp timestamp) 406 { 407 return this.evaluateTemporal(sql, this.currentTimestampPattern, timestamp, this.timestampLiteralFormat()); 408 } 409 410 protected String timestampLiteralFormat() 411 { 412 return "TIMESTAMP ''{0}''"; 413 } 414 415 private String evaluateTemporal(String sql, Pattern pattern, java.util.Date date, String format) 416 { 417 return pattern.matcher(sql).replaceAll(MessageFormat.format(format, date.toString())); 418 } 419 420 /** 421 * @see net.sf.hajdbc.Dialect#evaluateRand(java.lang.String) 422 */ 423 @Override 424 public String evaluateRand(String sql) 425 { 426 StringBuffer buffer = new StringBuffer(); 427 Matcher matcher = this.randomPattern.matcher(sql); 428 429 while (matcher.find()) 430 { 431 matcher.appendReplacement(buffer, Double.toString(Math.random())); 432 } 433 434 return matcher.appendTail(buffer).toString(); 435 } 436}