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; 019 020import java.sql.Connection; 021import java.sql.DatabaseMetaData; 022import java.sql.Driver; 023import java.sql.DriverManager; 024import java.sql.ResultSet; 025import java.sql.ResultSetMetaData; 026import java.sql.SQLException; 027import java.sql.SQLNonTransientConnectionException; 028import java.sql.SQLTransientConnectionException; 029import java.sql.Statement; 030import java.text.MessageFormat; 031import java.util.AbstractMap; 032import java.util.Arrays; 033import java.util.Collection; 034import java.util.Collections; 035import java.util.HashMap; 036import java.util.HashSet; 037import java.util.LinkedList; 038import java.util.List; 039import java.util.Map; 040import java.util.Map.Entry; 041import java.util.Set; 042import java.util.regex.Matcher; 043import java.util.regex.Pattern; 044 045import javax.transaction.xa.XAException; 046 047import net.sf.hajdbc.ColumnProperties; 048import net.sf.hajdbc.ColumnPropertiesFactory; 049import net.sf.hajdbc.Database; 050import net.sf.hajdbc.DumpRestoreSupport; 051import net.sf.hajdbc.ForeignKeyConstraint; 052import net.sf.hajdbc.ForeignKeyConstraintFactory; 053import net.sf.hajdbc.IdentifierNormalizer; 054import net.sf.hajdbc.IdentityColumnSupport; 055import net.sf.hajdbc.QualifiedName; 056import net.sf.hajdbc.QualifiedNameFactory; 057import net.sf.hajdbc.SequenceProperties; 058import net.sf.hajdbc.SequencePropertiesFactory; 059import net.sf.hajdbc.SequenceSupport; 060import net.sf.hajdbc.TableProperties; 061import net.sf.hajdbc.TriggerEvent; 062import net.sf.hajdbc.TriggerSupport; 063import net.sf.hajdbc.TriggerTime; 064import net.sf.hajdbc.UniqueConstraint; 065import net.sf.hajdbc.UniqueConstraintFactory; 066import net.sf.hajdbc.codec.Decoder; 067import net.sf.hajdbc.util.Resources; 068import net.sf.hajdbc.util.Strings; 069 070/** 071 * @author Paul Ferraro 072 * @since 1.1 073 */ 074public class StandardDialect implements Dialect, SequenceSupport, IdentityColumnSupport, TriggerSupport 075{ 076 // Taken from SQL:2003 column of: http://www.postgresql.org/docs/9.1/static/sql-keywords-appendix.html 077 // Only includes reserved keywords, since we only care about those keywords that require quoting to be used as an identifier 078 protected static final String[] SQL_2003_RESERVED_KEY_WORDS = new String[] { 079 "ABS", "ALL", "ALLOCATE", "ALTER", "AND", "ANY", "ARE", "ARRAY", "AS", "ASENSITIVE", "ASYMMETRIC", "AT", "ATOMIC", "AUTHORIZATION", "AVG", 080 "BEGIN", "BETWEEN", "BIGINT", "BINARY", "BLOB", "BOOLEAN", "BOTH", "BY", 081 "CALL", "CALLED", "CARDINALITY", "CASCADED", "CASE", "CAST", "CEIL", "CEILING", "CHAR", "CHARACTER", "CHARACTER_LENGTH", "CHAR_LENGTH", "CHECK", "CLOB", "CLOSE", "COALESCE", "COLLATE", "COLLECT", "COLUMN", "COMMIT", "CONDITION", "CONNECT", "CONSTRAINT", "CONVERT", "CORR", "CORRESPONDING", "COUNT", "COVAR_POP", "COVAR_SAMP", "CREATE", "CROSS", "CUBE", "CUME_DIST", "CURRENT", "CURRENT_DATE", "CURRENT_DEFAULT_TRANSFORM_GROUP", "CURRENT_PATH", "CURRENT_ROLE", "CURRENT_TIME", "CURRENT_TIMESTAMP", "CURRENT_TRANSFORM_GROUP_FOR_TYPE", "CURRENT_USER", "CURSOR", "CYCLE", 082 "DATALINK", "DATE", "DAY", "DEALLOCATE", "DEC", "DECIMAL", "DECLARE", "DEFAULT", "DELETE", "DENSE_RANK", "DEREF", "DESCRIBE", "DETERMINISTIC", "DISCONNECT", "DISTINCT", "DLNEWCOPY", "DLPREVIOUSCOPY", "DLURLCOMPLETE", "DLURLCOMPLETEONLY", "DLURLCOMPLETEWRITE", "DLURLPATH", "DLURLPATHONLY", "DLURLPATHWRITE", "DLURLSCHEME", "DLURLSERVER", "DLVALUE", "DOUBLE", "DROP", "DYNAMIC", 083 "EACH", "ELEMENT", "ELSE", "END", "END-EXEC", "ESCAPE", "EVERY", "EXCEPT", "EXEC", "EXECUTE", "EXISTS", "EXP", "EXTERNAL", "EXTRACT", 084 "FALSE", "FETCH", "FILTER", "FLOAT", "FLOOR", "FOR", "FOREIGN", "FREE", "FROM", "FULL", "FUNCTION", "FUSION", 085 "GET", "GLOBAL", "GRANT", "GROUP", "GROUPING", 086 "HAVING", "HOLD", "HOUR", 087 "IDENTITY", "IMPORT", "IN", "INDICATOR", "INNER", "INOUT", "INSENSITIVE", "INSERT", "INT", "INTEGER", "INTERSECT", "INTERSECTION", "INTERVAL", "INTO", "IS", 088 "JOIN", 089 "LANGUAGE", "LARGE", "LATERAL", "LEADING", "LEFT", "LIKE", "LN", "LOCAL", "LOCALTIME", "LOCALTIMESTAMP", "LOWER", 090 "MATCH", "MAX", "MEMBER", "MERGE", "METHOD", "MIN", "MINUTE", "MOD", "MODIFIES", "MODULE", "MONTH", "MULTISET", 091 "NATIONAL", "NATURAL", "NCHAR", "NCLOB", "NEW", "NO", "NONE", "NORMALIZE", "NOT", "NULL", "NULLIF", "NUMERIC", 092 "OCTET_LENGTH", "OF", "OLD", "ON", "ONLY", "OPEN", "OR", "ORDER", "OUT", "OUTER", "OVER", "OVERLAPS", "OVERLAY", 093 "PARAMETER", "PARTITION", "PERCENTILE_CONT", "PERCENTILE_DISC", "PERCENT_RANK", "POSITION", "POWER", "PRECISION", "PREPARE", "PRIMARY", "PROCEDURE", 094 "RANGE", "RANK", "READS", "REAL", "RECURSIVE", "REF", "REFERENCES", "REFERENCING", "REGR_AVGX", "REGR_AVGY", "REGR_COUNT", "REGR_INTERCEPT", "REGR_R2", "REGR_SLOPE", "REGR_SXX", "REGR_SXY", "REGR_SYY", "RELEASE", "RESULT", "RETURN", "RETURNS", "REVOKE", "RIGHT", "ROLLBACK", "ROLLUP", "ROW", "ROWS", "ROW_NUMBER", 095 "SAVEPOINT", "SCOPE", "SCROLL", "SEARCH", "SECOND", "SELECT", "SENSITIVE", "SESSION_USER", "SET", "SIMILAR", "SMALLINT", "SOME", "SPECIFIC", "SPECIFICTYPE", "SQL", "SQLEXCEPTION", "SQLSTATE", "SQLWARNING", "SQRT", "START", "STATIC", "STDDEV_POP", "STDDEV_SAMP", "SUBMULTISET", "SUBSTRING", "SUM", "SYMMETRIC", "SYSTEM", "SYSTEM_USER", 096 "TABLE", "TABLESAMPLE", "THEN", "TIME", "TIMESTAMP", "TIMEZONE_HOUR", "TIMEZONE_MINUTE", "TO", "TRAILING", "TRANSLATE", "TRANSLATION", "TREAT", "TRIGGER", "TRIM", "TRUE", 097 "UESCAPE", "UNION", "UNIQUE", "UNKNOWN", "UNNEST", "UPDATE", "UPPER", "USER", "USING", 098 "VALUE", "VALUES", "VARCHAR", "VARYING", "VAR_POP", "VAR_SAMP", 099 "WHEN", "WHENEVER", "WHERE", "WIDTH_BUCKET", "WINDOW", "WITH", "WITHIN", "WITHOUT", 100 "XML", "XMLAGG", "XMLATTRIBUTES", "XMLBINARY", "XMLCOMMENT", "XMLCONCAT", "XMLELEMENT", "XMLFOREST", "XMLNAMESPACES", "XMLPARSE", "XMLPI", "XMLROOT", "XMLSERIALIZE", 101 "YEAR", 102 }; 103 104 private final Pattern selectForUpdatePattern = compile(this.selectForUpdatePattern()); 105 private final Pattern insertIntoTablePattern = compile(this.insertIntoTablePattern()); 106 private final Pattern sequencePattern = compile(this.sequencePattern()); 107 private final Pattern currentTimestampPattern = compile(this.currentTimestampPattern()); 108 private final Pattern currentDatePattern = compile(this.currentDatePattern()); 109 private final Pattern currentTimePattern = compile(this.currentTimePattern()); 110 private final Pattern randomPattern = compile(this.randomPattern()); 111 private final Pattern urlPattern = Pattern.compile(String.format("jdbc\\:%s\\:%s", this.vendorPattern(), this.locatorPattern())); 112 113 protected String vendorPattern() 114 { 115 return "[^\\:]+"; 116 } 117 118 protected String locatorPattern() 119 { 120 return "//([^\\:/]+)(?:\\:(\\d+))?/([^\\?]+)"; 121 } 122 123 private static Pattern compile(String pattern) 124 { 125 return Pattern.compile(pattern, Pattern.CASE_INSENSITIVE); 126 } 127 128 protected String selectForUpdatePattern() 129 { 130 return "SELECT\\s+.+\\s+FOR\\s+UPDATE"; 131 } 132 133 protected String insertIntoTablePattern() 134 { 135 return "INSERT\\s+(?:INTO\\s+)?'?([^'\\s\\(]+)"; 136 } 137 138 protected String sequencePattern() 139 { 140 return "NEXT\\s+VALUE\\s+FOR\\s+'?([^',\\s\\(\\)]+)"; 141 } 142 143 protected String currentDatePattern() 144 { 145 return "(?<=\\W)CURRENT_DATE(?=\\W)"; 146 } 147 148 protected String currentTimePattern() 149 { 150 return "(?<=\\W)CURRENT_TIME(?:\\s*\\(\\s*\\d+\\s*\\))?(?=\\W)|(?<=\\W)LOCALTIME(?:\\s*\\(\\s*\\d+\\s*\\))?(?=\\W)"; 151 } 152 153 protected String currentTimestampPattern() 154 { 155 return "(?<=\\W)CURRENT_TIMESTAMP(?:\\s*\\(\\s*\\d+\\s*\\))?(?=\\W)|(?<=\\W)LOCALTIMESTAMP(?:\\s*\\(\\s*\\d+\\s*\\))?(?=\\W)"; 156 } 157 158 protected String randomPattern() 159 { 160 return "(?<=\\W)RAND\\s*\\(\\s*\\)"; 161 } 162 163 @SuppressWarnings("unused") 164 protected String schemaPattern(DatabaseMetaData metaData) throws SQLException 165 { 166 return null; 167 } 168 169 protected String executeFunctionFormat() 170 { 171 StringBuilder builder = new StringBuilder("SELECT {0}"); 172 173 String dummyTable = this.dummyTable(); 174 175 if (dummyTable != null) 176 { 177 builder.append(" FROM ").append(dummyTable); 178 } 179 180 return builder.toString(); 181 } 182 183 protected String executeFunctionSQL(String function) 184 { 185 return MessageFormat.format(this.executeFunctionFormat(), function); 186 } 187 188 protected String dummyTable() 189 { 190 return null; 191 } 192 193 /** 194 * @see net.sf.hajdbc.dialect.Dialect#getTruncateTableSQL(net.sf.hajdbc.TableProperties) 195 */ 196 @Override 197 public String getTruncateTableSQL(TableProperties properties) 198 { 199 return MessageFormat.format(this.truncateTableFormat(), properties.getName().getDMLName()); 200 } 201 202 protected String truncateTableFormat() 203 { 204 return "DELETE FROM {0}"; 205 } 206 207 /** 208 * @see net.sf.hajdbc.dialect.Dialect#getCreateForeignKeyConstraintSQL(net.sf.hajdbc.ForeignKeyConstraint) 209 */ 210 @Override 211 public String getCreateForeignKeyConstraintSQL(ForeignKeyConstraint key) 212 { 213 return MessageFormat.format(this.createForeignKeyConstraintFormat(), key.getName(), key.getTable().getDDLName(), Strings.join(key.getColumnList(), Strings.PADDED_COMMA), key.getForeignTable().getDDLName(), Strings.join(key.getForeignColumnList(), Strings.PADDED_COMMA), key.getDeleteRule(), key.getUpdateRule(), key.getDeferrability()); 214 } 215 216 protected String createForeignKeyConstraintFormat() 217 { 218 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}"; 219 } 220 221 /** 222 * @see net.sf.hajdbc.dialect.Dialect#getDropForeignKeyConstraintSQL(net.sf.hajdbc.ForeignKeyConstraint) 223 */ 224 @Override 225 public String getDropForeignKeyConstraintSQL(ForeignKeyConstraint key) 226 { 227 return MessageFormat.format(this.dropForeignKeyConstraintFormat(), key.getName(), key.getTable().getDDLName()); 228 } 229 230 protected String dropForeignKeyConstraintFormat() 231 { 232 return this.dropConstraintFormat(); 233 } 234 235 protected String dropConstraintFormat() 236 { 237 return "ALTER TABLE {1} DROP CONSTRAINT {0}"; 238 } 239 240 /** 241 * @see net.sf.hajdbc.dialect.Dialect#getCreateUniqueConstraintSQL(net.sf.hajdbc.UniqueConstraint) 242 */ 243 @Override 244 public String getCreateUniqueConstraintSQL(UniqueConstraint constraint) 245 { 246 return MessageFormat.format(this.createUniqueConstraintFormat(), constraint.getName(), constraint.getTable().getDDLName(), Strings.join(constraint.getColumnList(), Strings.PADDED_COMMA)); 247 } 248 249 protected String createUniqueConstraintFormat() 250 { 251 return "ALTER TABLE {1} ADD CONSTRAINT {0} UNIQUE ({2})"; 252 } 253 254 /** 255 * @see net.sf.hajdbc.dialect.Dialect#getDropUniqueConstraintSQL(net.sf.hajdbc.UniqueConstraint) 256 */ 257 @Override 258 public String getDropUniqueConstraintSQL(UniqueConstraint constraint) 259 { 260 return MessageFormat.format(this.dropUniqueConstraintFormat(), constraint.getName(), constraint.getTable().getDDLName()); 261 } 262 263 protected String dropUniqueConstraintFormat() 264 { 265 return this.dropConstraintFormat(); 266 } 267 268 /** 269 * @see net.sf.hajdbc.dialect.Dialect#isSelectForUpdate(java.lang.String) 270 */ 271 @Override 272 public boolean isSelectForUpdate(String sql) 273 { 274 return this.selectForUpdatePattern.matcher(sql).find(); 275 } 276 277 /** 278 * @see net.sf.hajdbc.dialect.Dialect#getDefaultSchemas(java.sql.DatabaseMetaData) 279 */ 280 @Override 281 public List<String> getDefaultSchemas(DatabaseMetaData metaData) throws SQLException 282 { 283 return Collections.singletonList(metaData.getUserName()); 284 } 285 286 protected String executeFunction(Connection connection, String function) throws SQLException 287 { 288 Statement statement = connection.createStatement(); 289 290 try 291 { 292 ResultSet resultSet = statement.executeQuery(this.executeFunctionSQL(function)); 293 294 resultSet.next(); 295 296 return resultSet.getString(1); 297 } 298 finally 299 { 300 Resources.close(statement); 301 } 302 } 303 304 protected List<String> executeQuery(Connection connection, String sql) throws SQLException 305 { 306 Statement statement = connection.createStatement(); 307 308 try 309 { 310 ResultSet resultSet = statement.executeQuery(sql); 311 312 List<String> resultList = new LinkedList<String>(); 313 314 while (resultSet.next()) 315 { 316 resultList.add(resultSet.getString(1)); 317 } 318 319 return resultList; 320 } 321 finally 322 { 323 Resources.close(statement); 324 } 325 } 326 327 /** 328 * {@inheritDoc} 329 * @see net.sf.hajdbc.dialect.Dialect#getSequenceSupport() 330 */ 331 @Override 332 public SequenceSupport getSequenceSupport() 333 { 334 return null; 335 } 336 337 /** 338 * @see net.sf.hajdbc.SequenceSupport#parseSequence(java.lang.String) 339 */ 340 @Override 341 public String parseSequence(String sql) 342 { 343 return this.parse(this.sequencePattern, sql); 344 } 345 346 /** 347 * @see net.sf.hajdbc.dialect.Dialect#getColumnType(net.sf.hajdbc.ColumnProperties) 348 */ 349 @Override 350 public int getColumnType(ColumnProperties properties) 351 { 352 return properties.getType(); 353 } 354 355 /** 356 * {@inheritDoc} 357 * @see net.sf.hajdbc.SequenceSupport#getSequences(java.sql.DatabaseMetaData, net.sf.hajdbc.SequencePropertiesFactory) 358 */ 359 @Override 360 public Collection<SequenceProperties> getSequences(DatabaseMetaData metaData, SequencePropertiesFactory factory) throws SQLException 361 { 362 ResultSet resultSet = metaData.getTables(Strings.EMPTY, null, Strings.ANY, new String[] { this.sequenceTableType() }); 363 364 try 365 { 366 List<SequenceProperties> sequences = new LinkedList<SequenceProperties>(); 367 368 while (resultSet.next()) 369 { 370 sequences.add(factory.createSequenceProperties(resultSet.getString("TABLE_SCHEM"), resultSet.getString("TABLE_NAME"), 1)); 371 } 372 373 return sequences; 374 } 375 finally 376 { 377 Resources.close(resultSet); 378 } 379 } 380 381 protected String sequenceTableType() 382 { 383 return "SEQUENCE"; 384 } 385 386 /** 387 * @see net.sf.hajdbc.SequenceSupport#getNextSequenceValueSQL(net.sf.hajdbc.SequenceProperties) 388 */ 389 @Override 390 public String getNextSequenceValueSQL(SequenceProperties sequence) 391 { 392 return this.executeFunctionSQL(MessageFormat.format(this.nextSequenceValueFormat(), sequence.getName().getDMLName())); 393 } 394 395 protected String nextSequenceValueFormat() 396 { 397 return "NEXT VALUE FOR {0}"; 398 } 399 400 /** 401 * @see net.sf.hajdbc.SequenceSupport#getAlterSequenceSQL(net.sf.hajdbc.SequenceProperties, long) 402 */ 403 @Override 404 public String getAlterSequenceSQL(SequenceProperties sequence, long value) 405 { 406 return MessageFormat.format(this.alterSequenceFormat(), sequence.getName().getDDLName(), String.valueOf(value), String.valueOf(sequence.getIncrement())); 407 } 408 409 protected String alterSequenceFormat() 410 { 411 return "ALTER SEQUENCE {0} RESTART WITH {1}"; 412 } 413 414 /** 415 * {@inheritDoc} 416 * @see net.sf.hajdbc.dialect.Dialect#getIdentityColumnSupport() 417 */ 418 @Override 419 public IdentityColumnSupport getIdentityColumnSupport() 420 { 421 return null; 422 } 423 424 @Override 425 public String parseInsertTable(String sql) 426 { 427 return this.parse(this.insertIntoTablePattern, sql); 428 } 429 430 @Override 431 public String getAlterIdentityColumnSQL(TableProperties table, ColumnProperties column, long value) 432 { 433 return MessageFormat.format(this.alterIdentityColumnFormat(), table.getName().getDDLName(), column.getName(), String.valueOf(value)); 434 } 435 436 protected String alterIdentityColumnFormat() 437 { 438 return "ALTER TABLE {0} ALTER COLUMN {1} RESTART WITH {2}"; 439 } 440 441 protected String parse(Pattern pattern, String string) 442 { 443 Matcher matcher = pattern.matcher(string); 444 445 return matcher.find() ? matcher.group(1) : null; 446 } 447 448 /** 449 * {@inheritDoc} 450 * @see net.sf.hajdbc.dialect.Dialect#evaluateCurrentDate(java.lang.String, java.sql.Date) 451 */ 452 @Override 453 public String evaluateCurrentDate(String sql, java.sql.Date date) 454 { 455 return evaluateTemporal(sql, this.currentDatePattern, date, this.dateLiteralFormat()); 456 } 457 458 protected String dateLiteralFormat() 459 { 460 return "DATE ''{0}''"; 461 } 462 463 /** 464 * {@inheritDoc} 465 * @see net.sf.hajdbc.dialect.Dialect#evaluateCurrentTime(java.lang.String, java.sql.Time) 466 */ 467 @Override 468 public String evaluateCurrentTime(String sql, java.sql.Time time) 469 { 470 return evaluateTemporal(sql, this.currentTimePattern, time, this.timeLiteralFormat()); 471 } 472 473 protected String timeLiteralFormat() 474 { 475 return "TIME ''{0}''"; 476 } 477 478 /** 479 * {@inheritDoc} 480 * @see net.sf.hajdbc.dialect.Dialect#evaluateCurrentTimestamp(java.lang.String, java.sql.Timestamp) 481 */ 482 @Override 483 public String evaluateCurrentTimestamp(String sql, java.sql.Timestamp timestamp) 484 { 485 return evaluateTemporal(sql, this.currentTimestampPattern, timestamp, this.timestampLiteralFormat()); 486 } 487 488 protected String timestampLiteralFormat() 489 { 490 return "TIMESTAMP ''{0}''"; 491 } 492 493 private static String evaluateTemporal(String sql, Pattern pattern, java.util.Date date, String format) 494 { 495 return pattern.matcher(sql).replaceAll(MessageFormat.format(format, date.toString())); 496 } 497 498 /** 499 * {@inheritDoc} 500 * @see net.sf.hajdbc.dialect.Dialect#evaluateRand(java.lang.String) 501 */ 502 @Override 503 public String evaluateRand(String sql) 504 { 505 StringBuffer buffer = new StringBuffer(); 506 Matcher matcher = this.randomPattern.matcher(sql); 507 508 while (matcher.find()) 509 { 510 matcher.appendReplacement(buffer, Double.toString(Math.random())); 511 } 512 513 return matcher.appendTail(buffer).toString(); 514 } 515 516 /** 517 * {@inheritDoc} 518 * @see net.sf.hajdbc.dialect.Dialect#indicatesFailure(java.sql.SQLException) 519 */ 520 @Override 521 public boolean indicatesFailure(SQLException e) 522 { 523 if ((e instanceof SQLNonTransientConnectionException) || (e instanceof SQLTransientConnectionException)) 524 { 525 return true; 526 } 527 528 String sqlState = e.getSQLState(); 529 if ((sqlState != null) && this.indicatesFailure(sqlState)) 530 { 531 return true; 532 } 533 534 return this.indicatesFailure(e.getErrorCode()); 535 } 536 537 /** 538 * Indicates whether the specified SQLState indicates a database failure that should result in a database deactivation. 539 * @param sqlState a SQL:2003 or X/Open SQLState 540 * @return true if the SQLState indicate a failure, false otherwise 541 */ 542 protected boolean indicatesFailure(String sqlState) 543 { 544 // 08 class SQLStates indicate connection failures 545 return sqlState.startsWith("08"); 546 } 547 548 /** 549 * Indicates whether the specified vendor-specific error code indicates a database failure that should result in a database deactivation. 550 * @param code a vendor-specific error code 551 * @return true if the error code indicate a failure, false otherwise 552 */ 553 protected boolean indicatesFailure(int code) 554 { 555 return false; 556 } 557 558 /** 559 * {@inheritDoc} 560 * @see net.sf.hajdbc.dialect.Dialect#indicatesFailure(javax.transaction.xa.XAException) 561 */ 562 @Override 563 public boolean indicatesFailure(XAException e) 564 { 565 return this.failureXAErrorCodes().contains(e.errorCode); 566 } 567 568 protected Set<Integer> failureXAErrorCodes() 569 { 570 return Collections.singleton(XAException.XAER_RMFAIL); 571 } 572 573 /** 574 * {@inheritDoc} 575 * @see net.sf.hajdbc.dialect.Dialect#getDumpRestoreSupport() 576 */ 577 @Override 578 public DumpRestoreSupport getDumpRestoreSupport() 579 { 580 return null; 581 } 582 583 /** 584 * {@inheritDoc} 585 * @see net.sf.hajdbc.dialect.Dialect#getTriggerSupport() 586 */ 587 @Override 588 public TriggerSupport getTriggerSupport() 589 { 590 return null; 591 } 592 593 /** 594 * {@inheritDoc} 595 * @see net.sf.hajdbc.TriggerSupport#getCreateTriggerSQL(java.lang.String, net.sf.hajdbc.TableProperties, net.sf.hajdbc.TriggerEvent, java.lang.String) 596 */ 597 @Override 598 public String getCreateTriggerSQL(String name, TableProperties table, TriggerEvent event, String action) 599 { 600 return MessageFormat.format(this.createTriggerFormat(), name, event.getTime().toString(), event.toString(), table.getName().getDDLName(), action); 601 } 602 603 protected String createTriggerFormat() 604 { 605 return "CREATE TRIGGER {0} {1} {2} ON {3} FOR EACH ROW BEGIN {4} END"; 606 } 607 608 /** 609 * {@inheritDoc} 610 * @see net.sf.hajdbc.TriggerSupport#getDropTriggerSQL(String, net.sf.hajdbc.TableProperties) 611 */ 612 @Override 613 public String getDropTriggerSQL(String name, TableProperties table) 614 { 615 return MessageFormat.format(this.dropTriggerFormat(), name, table.getName().getDDLName()); 616 } 617 618 protected String dropTriggerFormat() 619 { 620 return "DROP TRIGGER {1} ON {2}"; 621 } 622 623 /** 624 * {@inheritDoc} 625 * @see net.sf.hajdbc.TriggerSupport#getTriggerRowAlias(net.sf.hajdbc.TriggerTime) 626 */ 627 @Override 628 public String getTriggerRowAlias(TriggerTime time) 629 { 630 return time.getAlias(); 631 } 632 633 /** 634 * {@inheritDoc} 635 * @see net.sf.hajdbc.dialect.Dialect#getCreateSchemaSQL(java.lang.String) 636 */ 637 @Override 638 public String getCreateSchemaSQL(String schema) 639 { 640 return MessageFormat.format(this.createSchemaFormat(), schema); 641 } 642 643 protected String createSchemaFormat() 644 { 645 return "CREATE SCHEMA {0}"; 646 } 647 648 /** 649 * {@inheritDoc} 650 * @see net.sf.hajdbc.dialect.Dialect#getDropSchemaSQL(java.lang.String) 651 */ 652 @Override 653 public String getDropSchemaSQL(String schema) 654 { 655 return MessageFormat.format(this.dropSchemaFormat(), schema); 656 } 657 658 protected String dropSchemaFormat() 659 { 660 return "DROP SCHEMA {0}"; 661 } 662 663 protected boolean meetsRequirement(int minMajor, int minMinor) 664 { 665 Driver driver = this.findDriver(); 666 667 if (driver != null) 668 { 669 int major = driver.getMajorVersion(); 670 int minor = driver.getMinorVersion(); 671 return (major > minMajor) || ((major == minMajor) && (minor >= minMinor)); 672 } 673 674 return false; 675 } 676 677 private Driver findDriver() 678 { 679 String url = String.format("jdbc:%s:test", this.vendorPattern()); 680 681 List<Driver> drivers = Collections.list(DriverManager.getDrivers()); 682 for (Driver driver: drivers) 683 { 684 try 685 { 686 if (driver.acceptsURL(url)) 687 { 688 return driver; 689 } 690 } 691 catch (SQLException e) 692 { 693 // Skip 694 } 695 } 696 return null; 697 } 698 699 /** 700 * Returns all tables in this database mapped by schema. 701 * @param metaData a DatabaseMetaData implementation 702 * @return a Map of schema name to Collection of table names 703 * @throws SQLException if an error occurs access DatabaseMetaData 704 */ 705 @Override 706 public Collection<QualifiedName> getTables(DatabaseMetaData metaData, QualifiedNameFactory factory) throws SQLException 707 { 708 ResultSet resultSet = metaData.getTables(getCatalog(metaData), this.schemaPattern(metaData), Strings.ANY, new String[] { "TABLE" }); 709 710 try 711 { 712 List<QualifiedName> list = new LinkedList<QualifiedName>(); 713 714 while (resultSet.next()) 715 { 716 list.add(factory.createQualifiedName(resultSet.getString("TABLE_SCHEM"), resultSet.getString("TABLE_NAME"))); 717 } 718 719 return list; 720 } 721 finally 722 { 723 Resources.close(resultSet); 724 } 725 } 726 727 /** 728 * Returns the columns of the specified table. 729 * @param metaData a DatabaseMetaData implementation 730 * @param table a schema qualified table name 731 * @return a Map of column name to column properties 732 * @throws SQLException if an error occurs access DatabaseMetaData 733 */ 734 @Override 735 public Map<String, ColumnProperties> getColumns(DatabaseMetaData metaData, QualifiedName table, ColumnPropertiesFactory factory) throws SQLException 736 { 737 Statement statement = metaData.getConnection().createStatement(); 738 739 try 740 { 741 Map<String, ColumnProperties> map = new HashMap<String, ColumnProperties>(); 742 743 ResultSetMetaData resultSet = statement.executeQuery(String.format("SELECT * FROM %s WHERE 0=1", table.getDMLName())).getMetaData(); 744 745 for (int i = 1; i <= resultSet.getColumnCount(); ++i) 746 { 747 String column = resultSet.getColumnName(i); 748 int type = resultSet.getColumnType(i); 749 String nativeType = resultSet.getColumnTypeName(i); 750 boolean autoIncrement = resultSet.isAutoIncrement(i); 751 752 ColumnProperties properties = factory.createColumnProperties(column, type, nativeType, null, null, autoIncrement); 753 map.put(properties.getName(), properties); 754 } 755 756 return map; 757 } 758 finally 759 { 760 Resources.close(statement); 761 } 762 } 763 764 /** 765 * Returns the primary key of the specified table. 766 * @param metaData a DatabaseMetaData implementation 767 * @param table a schema qualified table name 768 * @return a unique constraint 769 * @throws SQLException if an error occurs access DatabaseMetaData 770 */ 771 @Override 772 public UniqueConstraint getPrimaryKey(DatabaseMetaData metaData, QualifiedName table, UniqueConstraintFactory factory) throws SQLException 773 { 774 ResultSet resultSet = metaData.getPrimaryKeys(getCatalog(metaData), table.getSchema(), table.getName()); 775 776 try 777 { 778 UniqueConstraint constraint = null; 779 780 while (resultSet.next()) 781 { 782 if (constraint == null) 783 { 784 constraint = factory.createUniqueConstraint(resultSet.getString("PK_NAME"), table); 785 } 786 787 constraint.getColumnList().add(resultSet.getString("COLUMN_NAME")); 788 } 789 790 return constraint; 791 } 792 finally 793 { 794 Resources.close(resultSet); 795 } 796 } 797 798 /** 799 * Returns the foreign key constraints on the specified table. 800 * @param metaData a DatabaseMetaData implementation 801 * @param table a schema qualified table name 802 * @return a Collection of foreign key constraints. 803 * @throws SQLException if an error occurs access DatabaseMetaData 804 */ 805 @Override 806 public Collection<ForeignKeyConstraint> getForeignKeyConstraints(DatabaseMetaData metaData, QualifiedName table, ForeignKeyConstraintFactory factory) throws SQLException 807 { 808 ResultSet resultSet = metaData.getImportedKeys(getCatalog(metaData), table.getSchema(), table.getName()); 809 810 try 811 { 812 Map<String, ForeignKeyConstraint> foreignKeyMap = new HashMap<String, ForeignKeyConstraint>(); 813 814 while (resultSet.next()) 815 { 816 String name = resultSet.getString("FK_NAME"); 817 818 ForeignKeyConstraint foreignKey = foreignKeyMap.get(name); 819 820 if (foreignKey == null) 821 { 822 foreignKey = factory.createForeignKeyConstraint(name, table, factory.getQualifiedNameFactory().createQualifiedName(resultSet.getString("PKTABLE_SCHEM"), resultSet.getString("PKTABLE_NAME")), resultSet.getInt("DELETE_RULE"), resultSet.getInt("UPDATE_RULE"), resultSet.getInt("DEFERRABILITY")); 823 824 foreignKeyMap.put(name, foreignKey); 825 } 826 827 foreignKey.getColumnList().add(resultSet.getString("FKCOLUMN_NAME")); 828 foreignKey.getForeignColumnList().add(resultSet.getString("PKCOLUMN_NAME")); 829 } 830 831 return foreignKeyMap.values(); 832 } 833 finally 834 { 835 Resources.close(resultSet); 836 } 837 } 838 839 /** 840 * Returns the unique constraints on the specified table - excluding the primary key of the table. 841 * @param metaData a schema qualified table name 842 * @param table a qualified table name 843 * @param primaryKey the primary key of this table 844 * @return a Collection of unique constraints. 845 * @throws SQLException if an error occurs access DatabaseMetaData 846 */ 847 @Override 848 public Collection<UniqueConstraint> getUniqueConstraints(DatabaseMetaData metaData, QualifiedName table, UniqueConstraint primaryKey, UniqueConstraintFactory factory) throws SQLException 849 { 850 ResultSet resultSet = metaData.getIndexInfo(getCatalog(metaData), table.getSchema(), table.getName(), true, false); 851 852 try 853 { 854 Map<String, UniqueConstraint> keyMap = new HashMap<String, UniqueConstraint>(); 855 856 while (resultSet.next()) 857 { 858 if (resultSet.getShort("TYPE") == DatabaseMetaData.tableIndexHashed) 859 { 860 String name = resultSet.getString("INDEX_NAME"); 861 862 UniqueConstraint key = keyMap.get(name); 863 864 if (key == null) 865 { 866 key = factory.createUniqueConstraint(name, table); 867 868 // Don't include the primary key 869 if (key.equals(primaryKey)) continue; 870 871 keyMap.put(name, key); 872 } 873 874 key.getColumnList().add(resultSet.getString("COLUMN_NAME")); 875 } 876 } 877 return keyMap.values(); 878 } 879 finally 880 { 881 Resources.close(resultSet); 882 } 883 } 884 885 private static String getCatalog(DatabaseMetaData metaData) throws SQLException 886 { 887 String catalog = metaData.getConnection().getCatalog(); 888 889 return (catalog != null) ? catalog : Strings.EMPTY; 890 } 891 892 /** 893 * Identifies any identity columns from the from the specified collection of columns 894 * @param columns the columns of a table 895 * @return a collection of column names 896 * @throws SQLException 897 */ 898 @Override 899 public Collection<String> getIdentityColumns(Collection<ColumnProperties> columns) throws SQLException 900 { 901 List<String> columnList = new LinkedList<String>(); 902 903 for (ColumnProperties column: columns) 904 { 905 if (column.isAutoIncrement()) 906 { 907 columnList.add(column.getName()); 908 } 909 } 910 911 return columnList; 912 } 913 914 /** 915 * {@inheritDoc} 916 * @see net.sf.hajdbc.dialect.Dialect#getTypes(java.sql.DatabaseMetaData) 917 */ 918 @Override 919 public Map<Integer, Entry<String, Integer>> getTypes(DatabaseMetaData metaData) throws SQLException 920 { 921 ResultSet resultSet = metaData.getTypeInfo(); 922 923 try 924 { 925 Map<Integer, Map.Entry<String, Integer>> types = new HashMap<Integer, Map.Entry<String, Integer>>(); 926 927 while (resultSet.next()) 928 { 929 int type = resultSet.getInt("DATA_TYPE"); 930 if (!types.containsKey(type)) 931 { 932 String name = resultSet.getString("TYPE_NAME"); 933 String params = resultSet.getString("CREATE_PARAMS"); 934 types.put(type, new AbstractMap.SimpleImmutableEntry<String, Integer>(name, (params != null) ? resultSet.getInt("PRECISION") : null)); 935 } 936 } 937 938 return types; 939 } 940 finally 941 { 942 Resources.close(resultSet); 943 } 944 } 945 946 @Override 947 public IdentifierNormalizer createIdentifierNormalizer(DatabaseMetaData metaData) throws SQLException 948 { 949 return new StandardIdentifierNormalizer(metaData, this.identifierPattern(metaData), this.reservedIdentifiers(metaData)); 950 } 951 952 protected Pattern identifierPattern(DatabaseMetaData metaData) throws SQLException 953 { 954 return Pattern.compile(MessageFormat.format("[a-zA-Z][\\w{0}]*", Pattern.quote(metaData.getExtraNameCharacters()))); 955 } 956 957 protected Set<String> reservedIdentifiers(DatabaseMetaData metaData) throws SQLException 958 { 959 Set<String> set = new HashSet<String>(Arrays.asList(SQL_2003_RESERVED_KEY_WORDS)); 960 961 for (String word: metaData.getSQLKeywords().split(Strings.COMMA)) 962 { 963 set.add(word.toUpperCase()); 964 } 965 966 return set; 967 } 968 969 @Override 970 public QualifiedNameFactory createQualifiedNameFactory(DatabaseMetaData metaData, IdentifierNormalizer normalizer) throws SQLException 971 { 972 return new StandardQualifiedNameFactory(metaData, normalizer); 973 } 974 975 @Override 976 public ColumnPropertiesFactory createColumnPropertiesFactory(IdentifierNormalizer normalizer) 977 { 978 return new StandardColumnPropertiesFactory(normalizer); 979 } 980 981 @Override 982 public SequencePropertiesFactory createSequencePropertiesFactory(QualifiedNameFactory factory) 983 { 984 return new StandardSequencePropertiesFactory(factory); 985 } 986 987 @Override 988 public ForeignKeyConstraintFactory createForeignKeyConstraintFactory(QualifiedNameFactory factory) 989 { 990 return new StandardForeignKeyConstraintFactory(factory); 991 } 992 993 @Override 994 public UniqueConstraintFactory createUniqueConstraintFactory(IdentifierNormalizer normalizer) 995 { 996 return new StandardUniqueConstraintFactory(normalizer); 997 } 998 999 @Override 1000 public boolean isValid(Connection connection) throws SQLException 1001 { 1002 return connection.isValid(0); 1003 } 1004 1005 @Override 1006 public <Z, D extends Database<Z>> ConnectionProperties getConnectionProperties(D database, Decoder decoder) throws SQLException 1007 { 1008 final String password = database.decodePassword(decoder); 1009 Connection connection = database.connect(database.getConnectionSource(), password); 1010 try 1011 { 1012 DatabaseMetaData metaData = connection.getMetaData(); 1013 String url = metaData.getURL(); 1014 1015 if (url == null) 1016 { 1017 throw new UnsupportedOperationException(); 1018 } 1019 1020 Matcher matcher = this.urlPattern.matcher(url); 1021 1022 if (!matcher.find() || (matcher.groupCount() != 3)) 1023 { 1024 throw new UnsupportedOperationException(url); 1025 } 1026 1027 final String host = matcher.group(1); 1028 final String port = matcher.group(2); 1029 final String databaseName = matcher.group(3); 1030 final String user = metaData.getUserName(); 1031 1032 return new ConnectionProperties() 1033 { 1034 @Override 1035 public String getHost() 1036 { 1037 return host; 1038 } 1039 1040 @Override 1041 public String getPort() 1042 { 1043 return port; 1044 } 1045 1046 @Override 1047 public String getDatabase() 1048 { 1049 return databaseName; 1050 } 1051 1052 @Override 1053 public String getUser() 1054 { 1055 return user; 1056 } 1057 1058 @Override 1059 public String getPassword() 1060 { 1061 return password; 1062 } 1063 }; 1064 } 1065 finally 1066 { 1067 connection.close(); 1068 } 1069 } 1070}