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.cache; 022 023import java.sql.DatabaseMetaData; 024import java.sql.ResultSet; 025import java.sql.ResultSetMetaData; 026import java.sql.SQLException; 027import java.sql.Statement; 028import java.util.ArrayList; 029import java.util.Collection; 030import java.util.HashMap; 031import java.util.HashSet; 032import java.util.LinkedList; 033import java.util.List; 034import java.util.Map; 035import java.util.Set; 036import java.util.regex.Pattern; 037 038import net.sf.hajdbc.ColumnProperties; 039import net.sf.hajdbc.Dialect; 040import net.sf.hajdbc.ForeignKeyConstraint; 041import net.sf.hajdbc.Messages; 042import net.sf.hajdbc.QualifiedName; 043import net.sf.hajdbc.SequenceProperties; 044import net.sf.hajdbc.UniqueConstraint; 045import net.sf.hajdbc.util.Strings; 046 047/** 048 * Processes database meta data into useful structures. 049 * @author Paul Ferraro 050 */ 051@SuppressWarnings("nls") 052public class DatabaseMetaDataSupportImpl implements DatabaseMetaDataSupport 053{ 054 // As defined in SQL-92 specification: http://www.andrew.cmu.edu/user/shadow/sql/sql1992.txt 055 private static final String[] SQL_92_RESERVED_WORDS = new String[] { 056 "absolute", "action", "add", "all", "allocate", "alter", "and", "any", "are", "as", "asc", "assertion", "at", "authorization", "avg", 057 "begin", "between", "bit", "bit_length", "both", "by", 058 "cascade", "cascaded", "case", "cast", "catalog", "char", "character", "char_length", "character_length", "check", "close", "coalesce", "collate", "collation", "column", "commit", "connect", "connection", "constraint", "constraints", "continue", "convert", "corresponding", "count", "create", "cross", "current", "current_date", "current_time", "current_timestamp", "current_user", "cursor", 059 "date", "day", "deallocate", "dec", "decimal", "declare", "default", "deferrable", "deferred", "delete", "desc", "describe", "descriptor", "diagnostics", "disconnect", "distinct", "domain", "double", "drop", 060 "else", "end", "end-exec", "escape", "except", "exception", "exec", "execute", "exists", "external", "extract", 061 "false", "fetch", "first", "float", "for", "foreign", "found", "from", "full", 062 "get", "global", "go", "goto", "grant", "group", 063 "having", "hour", 064 "identity", "immediate", "in", "indicator", "initially", "inner", "input", "insensitive", "insert", "int", "integer", "intersect", "interval", "into", "is", "isolation", 065 "join", 066 "key", 067 "language", "last", "leading", "left", "level", "like", "local", "lower", 068 "match", "max", "min", "minute", "module", "month", 069 "names", "national", "natural", "nchar", "next", "no", "not", "null", "nullif", "numeric", 070 "octet_length", "of", "on", "only", "open", "option", "or", "order", "outer", "output", "overlaps", 071 "pad", "partial", "position", "precision", "prepare", "preserve", "primary", "prior", "privileges", "procedure", "public", 072 "read", "real", "references", "relative", "restrict", "revoke", "right", "rollback", "rows", 073 "schema", "scroll", "second", "section", "select", "session", "session_user", "set", "size", "smallint", "some", "space", "sql", "sqlcode", "sqlerror", "sqlstate", "substring", "sum", "system_user", 074 "table", "temporary", "then", "time", "timestamp", "timezone_hour", "timezone_minute", "to", "trailing", "transaction", "translate", "translation", "trim", "true", 075 "union", "unique", "unknown", "update", "upper", "usage", "user", "using", 076 "value", "values", "varchar", "varying", "view", 077 "when", "whenever", "where", "with", "work", "write", 078 "year", 079 "zone" 080 }; 081 082 private static final Pattern UPPER_CASE_PATTERN = Pattern.compile("[A-Z]"); 083 private static final Pattern LOWER_CASE_PATTERN = Pattern.compile("[a-z]"); 084 085 private Dialect dialect; 086 private Set<String> reservedIdentifierSet = new HashSet<String>(); 087 private Pattern identifierPattern; 088 private String quote; 089 private boolean supportsMixedCaseIdentifiers; 090 private boolean supportsMixedCaseQuotedIdentifiers; 091 private boolean storesLowerCaseIdentifiers; 092 private boolean storesLowerCaseQuotedIdentifiers; 093 private boolean storesUpperCaseIdentifiers; 094 private boolean storesUpperCaseQuotedIdentifiers; 095 private boolean supportsSchemasInDDL; 096 private boolean supportsSchemasInDML; 097 098 /** 099 * Constructs a new DatabaseMetaDataSupport using the specified DatabaseMetaData implementation. 100 * @param metaData a DatabaseMetaData implementation 101 * @param dialect the vendor-specific dialect of the cluster 102 * @throws SQLException if an error occurs access DatabaseMetaData 103 */ 104 public DatabaseMetaDataSupportImpl(DatabaseMetaData metaData, Dialect dialect) throws SQLException 105 { 106 this.dialect = dialect; 107 108 this.identifierPattern = dialect.getIdentifierPattern(metaData); 109 this.quote = metaData.getIdentifierQuoteString(); 110 this.supportsMixedCaseIdentifiers = metaData.supportsMixedCaseIdentifiers(); 111 this.supportsMixedCaseQuotedIdentifiers = metaData.supportsMixedCaseQuotedIdentifiers(); 112 this.storesLowerCaseIdentifiers = metaData.storesLowerCaseIdentifiers(); 113 this.storesLowerCaseQuotedIdentifiers = metaData.storesLowerCaseQuotedIdentifiers(); 114 this.storesUpperCaseIdentifiers = metaData.storesUpperCaseIdentifiers(); 115 this.storesUpperCaseQuotedIdentifiers = metaData.storesUpperCaseQuotedIdentifiers(); 116 this.supportsSchemasInDML = metaData.supportsSchemasInDataManipulation(); 117 this.supportsSchemasInDDL = metaData.supportsSchemasInTableDefinitions(); 118 119 for (String word: SQL_92_RESERVED_WORDS) 120 { 121 this.reservedIdentifierSet.add(this.normalizeCase(word)); 122 } 123 124 for (String word: metaData.getSQLKeywords().split(Strings.COMMA)) 125 { 126 this.reservedIdentifierSet.add(this.normalizeCase(word)); 127 } 128 } 129 130 /** 131 * Returns all tables in this database mapped by schema. 132 * @param metaData a DatabaseMetaData implementation 133 * @return a Map of schema name to Collection of table names 134 * @throws SQLException if an error occurs access DatabaseMetaData 135 */ 136 @Override 137 public Collection<QualifiedName> getTables(DatabaseMetaData metaData) throws SQLException 138 { 139 List<QualifiedName> list = new LinkedList<QualifiedName>(); 140 141 ResultSet resultSet = metaData.getTables(this.getCatalog(metaData), null, Strings.ANY, new String[] { "TABLE" }); 142 143 while (resultSet.next()) 144 { 145 list.add(new QualifiedName(resultSet.getString("TABLE_SCHEM"), resultSet.getString("TABLE_NAME"))); 146 } 147 148 resultSet.close(); 149 150 return list; 151 } 152 153 /** 154 * Returns the columns of the specified table. 155 * @param metaData a DatabaseMetaData implementation 156 * @param table a schema qualified table name 157 * @return a Map of column name to column properties 158 * @throws SQLException if an error occurs access DatabaseMetaData 159 */ 160 @Override 161 public Map<String, ColumnProperties> getColumns(DatabaseMetaData metaData, QualifiedName table) throws SQLException 162 { 163 Map<String, ColumnProperties> columnMap = new HashMap<String, ColumnProperties>(); 164 165 Statement statement = metaData.getConnection().createStatement(); 166 167 try 168 { 169 ResultSetMetaData resultSet = statement.executeQuery("SELECT * FROM " + this.qualifyNameForDML(table) + " WHERE 0=1").getMetaData(); 170 171 for (int i = 1; i <= resultSet.getColumnCount(); ++i) 172 { 173 String column = this.quote(resultSet.getColumnName(i)); 174 int type = resultSet.getColumnType(i); 175 String nativeType = resultSet.getColumnTypeName(i); 176 boolean autoIncrement = resultSet.isAutoIncrement(i); 177 178 columnMap.put(column, new ColumnPropertiesImpl(column, type, nativeType, null, null, autoIncrement)); 179 } 180 } 181 finally 182 { 183 statement.close(); 184 } 185 186 return columnMap; 187 } 188 189 /** 190 * Returns the primary key of the specified table. 191 * @param metaData a DatabaseMetaData implementation 192 * @param table a schema qualified table name 193 * @return a unique constraint 194 * @throws SQLException if an error occurs access DatabaseMetaData 195 */ 196 @Override 197 public UniqueConstraint getPrimaryKey(DatabaseMetaData metaData, QualifiedName table) throws SQLException 198 { 199 UniqueConstraint constraint = null; 200 201 ResultSet resultSet = metaData.getPrimaryKeys(this.getCatalog(metaData), this.getSchema(table), table.getName()); 202 203 while (resultSet.next()) 204 { 205 String name = this.quote(resultSet.getString("PK_NAME")); 206 207 if (constraint == null) 208 { 209 constraint = new UniqueConstraintImpl(name, this.qualifyNameForDDL(table)); 210 } 211 212 String column = this.quote(resultSet.getString("COLUMN_NAME")); 213 214 constraint.getColumnList().add(column); 215 } 216 217 resultSet.close(); 218 219 return constraint; 220 } 221 222 /** 223 * Returns the foreign key constraints on the specified table. 224 * @param metaData a DatabaseMetaData implementation 225 * @param table a schema qualified table name 226 * @return a Collection of foreign key constraints. 227 * @throws SQLException if an error occurs access DatabaseMetaData 228 */ 229 @Override 230 public Collection<ForeignKeyConstraint> getForeignKeyConstraints(DatabaseMetaData metaData, QualifiedName table) throws SQLException 231 { 232 Map<String, ForeignKeyConstraint> foreignKeyMap = new HashMap<String, ForeignKeyConstraint>(); 233 234 ResultSet resultSet = metaData.getImportedKeys(this.getCatalog(metaData), this.getSchema(table), table.getName()); 235 236 while (resultSet.next()) 237 { 238 String name = this.quote(resultSet.getString("FK_NAME")); 239 240 ForeignKeyConstraint foreignKey = foreignKeyMap.get(name); 241 242 if (foreignKey == null) 243 { 244 foreignKey = new ForeignKeyConstraintImpl(name, this.qualifyNameForDDL(table)); 245 246 String foreignSchema = this.quote(resultSet.getString("PKTABLE_SCHEM")); 247 String foreignTable = this.quote(resultSet.getString("PKTABLE_NAME")); 248 249 foreignKey.setForeignTable(this.qualifyNameForDDL(new QualifiedName(foreignSchema, foreignTable))); 250 foreignKey.setDeleteRule(resultSet.getInt("DELETE_RULE")); 251 foreignKey.setUpdateRule(resultSet.getInt("UPDATE_RULE")); 252 foreignKey.setDeferrability(resultSet.getInt("DEFERRABILITY")); 253 254 foreignKeyMap.put(name, foreignKey); 255 } 256 257 String column = this.quote(resultSet.getString("FKCOLUMN_NAME")); 258 String foreignColumn = this.quote(resultSet.getString("PKCOLUMN_NAME")); 259 260 foreignKey.getColumnList().add(column); 261 foreignKey.getForeignColumnList().add(foreignColumn); 262 } 263 264 resultSet.close(); 265 266 return foreignKeyMap.values(); 267 } 268 269 /** 270 * Returns the unique constraints on the specified table - excluding the primary key of the table. 271 * @param metaData a schema qualified table name 272 * @param table a qualified table name 273 * @param primaryKey the primary key of this table 274 * @return a Collection of unique constraints. 275 * @throws SQLException if an error occurs access DatabaseMetaData 276 */ 277 @Override 278 public Collection<UniqueConstraint> getUniqueConstraints(DatabaseMetaData metaData, QualifiedName table, UniqueConstraint primaryKey) throws SQLException 279 { 280 Map<String, UniqueConstraint> keyMap = new HashMap<String, UniqueConstraint>(); 281 282 ResultSet resultSet = metaData.getIndexInfo(this.getCatalog(metaData), this.getSchema(table), table.getName(), true, false); 283 284 while (resultSet.next()) 285 { 286 if (resultSet.getShort("TYPE") == DatabaseMetaData.tableIndexHashed) 287 { 288 String name = this.quote(resultSet.getString("INDEX_NAME")); 289 290 // Don't include the primary key 291 if ((primaryKey != null) && name.equals(primaryKey.getName())) continue; 292 293 UniqueConstraint key = keyMap.get(name); 294 295 if (key == null) 296 { 297 key = new UniqueConstraintImpl(name, this.qualifyNameForDDL(table)); 298 299 keyMap.put(name, key); 300 } 301 302 String column = this.quote(resultSet.getString("COLUMN_NAME")); 303 304 key.getColumnList().add(column); 305 } 306 } 307 308 resultSet.close(); 309 310 return keyMap.values(); 311 } 312 313 /** 314 * Returns the schema qualified name of the specified table suitable for use in a data modification language (DML) statement. 315 * @param name a schema qualified name 316 * @return a Collection of unique constraints. 317 */ 318 @Override 319 public String qualifyNameForDML(QualifiedName name) 320 { 321 return this.qualifyName(name, this.supportsSchemasInDML); 322 } 323 324 /** 325 * Returns the schema qualified name of the specified table suitable for use in a data definition language (DDL) statement. 326 * @param name a schema qualified name 327 * @return a Collection of unique constraints. 328 */ 329 @Override 330 public String qualifyNameForDDL(QualifiedName name) 331 { 332 return this.qualifyName(name, this.supportsSchemasInDDL); 333 } 334 335 private String qualifyName(QualifiedName name, boolean supportsSchemas) 336 { 337 StringBuilder builder = new StringBuilder(); 338 339 String schema = name.getSchema(); 340 341 if (supportsSchemas && (schema != null)) 342 { 343 builder.append(this.quote(schema)).append(Strings.DOT); 344 } 345 346 return builder.append(this.quote(name.getName())).toString(); 347 } 348 349 private String getCatalog(DatabaseMetaData metaData) throws SQLException 350 { 351 String catalog = metaData.getConnection().getCatalog(); 352 353 return (catalog != null) ? catalog : Strings.EMPTY; 354 } 355 356 private String getSchema(QualifiedName name) 357 { 358 String schema = name.getSchema(); 359 360 return (schema != null) ? schema : Strings.EMPTY; 361 } 362 363 private String quote(String identifier) 364 { 365 if (identifier == null) return null; 366 367 int quoteLength = this.quote.length(); 368 369 // Strip any existing quoting 370 String raw = (identifier.startsWith(this.quote) && identifier.endsWith(this.quote)) ? identifier.substring(quoteLength, identifier.length() - quoteLength) : identifier; 371 372 String normal = this.normalizeCase(raw); 373 374 // Quote reserved identifiers 375 boolean requiresQuoting = this.reservedIdentifierSet.contains(normal); 376 377 // Quote identifiers containing special characters 378 requiresQuoting |= !this.identifierPattern.matcher(raw).matches(); 379 380 // Quote mixed-case identifiers if detected and supported by DBMS 381 requiresQuoting |= !this.supportsMixedCaseIdentifiers && this.supportsMixedCaseQuotedIdentifiers && ((this.storesLowerCaseIdentifiers && !this.storesLowerCaseQuotedIdentifiers && UPPER_CASE_PATTERN.matcher(raw).find()) || (this.storesUpperCaseIdentifiers && !this.storesUpperCaseQuotedIdentifiers && LOWER_CASE_PATTERN.matcher(raw).find())); 382 383 return requiresQuoting ? this.quote + this.normalizeCaseQuoted(raw) + this.quote : normal; 384 } 385 386 private String normalizeCase(String identifier) 387 { 388 if (this.storesLowerCaseIdentifiers) return identifier.toLowerCase(); 389 390 if (this.storesUpperCaseIdentifiers) return identifier.toUpperCase(); 391 392 return identifier; 393 } 394 395 private String normalizeCaseQuoted(String identifier) 396 { 397 if (this.storesLowerCaseQuotedIdentifiers) return identifier.toLowerCase(); 398 399 if (this.storesUpperCaseQuotedIdentifiers) return identifier.toUpperCase(); 400 401 return identifier; 402 } 403 404 private String normalize(String qualifiedName, String defaultSchema) 405 { 406 String parts[] = qualifiedName.split(Pattern.quote(Strings.DOT)); 407 408 String name = parts[parts.length - 1]; 409 String schema = (parts.length > 1) ? parts[parts.length - 2] : defaultSchema; 410 411 return this.qualifyNameForDML(new QualifiedName(schema, name)); 412 } 413 414 /** 415 * Returns a collection of sequences using dialect specific logic. 416 * @param metaData database meta data 417 * @return a collection of sequences 418 * @throws SQLException 419 */ 420 @Override 421 public Collection<SequenceProperties> getSequences(DatabaseMetaData metaData) throws SQLException 422 { 423 Collection<QualifiedName> sequences = this.dialect.getSequences(metaData); 424 425 List<SequenceProperties> sequenceList = new ArrayList<SequenceProperties>(sequences.size()); 426 427 for (QualifiedName sequence: sequences) 428 { 429 sequenceList.add(new SequencePropertiesImpl(this.qualifyNameForDML(sequence))); 430 } 431 432 return sequenceList; 433 } 434 435 /** 436 * Locates an object from a map keyed by schema qualified name. 437 * @param <T> an object 438 * @param map a map of database 439 * @param name the name of the object to locate 440 * @param defaultSchemaList a list of default schemas 441 * @return the object with the specified name 442 * @throws SQLException 443 */ 444 @Override 445 public <T> T find(Map<String, T> map, String name, List<String> defaultSchemaList) throws SQLException 446 { 447 T properties = map.get(this.normalize(name, null)); 448 449 if (properties == null) 450 { 451 for (String schema: defaultSchemaList) 452 { 453 if (properties == null) 454 { 455 properties = map.get(this.normalize(name, schema)); 456 } 457 } 458 } 459 460 if (properties == null) 461 { 462 throw new SQLException(Messages.getMessage(Messages.SCHEMA_LOOKUP_FAILED, name, defaultSchemaList, this.dialect.getClass().getName() + ".getDefaultSchemas()")); 463 } 464 465 return properties; 466 } 467 468 /** 469 * Identifies any identity columns from the from the specified collection of columns 470 * @param columns the columns of a table 471 * @return a collection of column names 472 * @throws SQLException 473 */ 474 @Override 475 public Collection<String> getIdentityColumns(Collection<ColumnProperties> columns) throws SQLException 476 { 477 List<String> columnList = new LinkedList<String>(); 478 479 for (ColumnProperties column: columns) 480 { 481 if (column.isAutoIncrement()) 482 { 483 columnList.add(column.getName()); 484 } 485 } 486 487 return columnList; 488 } 489}