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