001: /*
002: * Copyright 2006-2007, Unitils.org
003: *
004: * Licensed under the Apache License, Version 2.0 (the "License");
005: * you may not use this file except in compliance with the License.
006: * You may obtain a copy of the License at
007: *
008: * http://www.apache.org/licenses/LICENSE-2.0
009: *
010: * Unless required by applicable law or agreed to in writing, software
011: * distributed under the License is distributed on an "AS IS" BASIS,
012: * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
013: * See the License for the specific language governing permissions and
014: * limitations under the License.
015: */
016: package org.unitils.core.dbsupport;
017:
018: import static org.unitils.core.util.StoredIdentifierCase.LOWER_CASE;
019: import static org.unitils.core.util.StoredIdentifierCase.MIXED_CASE;
020: import static org.unitils.core.util.StoredIdentifierCase.UPPER_CASE;
021: import static org.unitils.thirdparty.org.apache.commons.dbutils.DbUtils.closeQuietly;
022:
023: import java.sql.Connection;
024: import java.sql.DatabaseMetaData;
025: import java.sql.SQLException;
026: import java.util.Properties;
027: import java.util.Set;
028:
029: import org.unitils.core.UnitilsException;
030: import org.unitils.core.util.StoredIdentifierCase;
031: import org.unitils.util.PropertyUtils;
032:
033: /**
034: * Helper class that implements a number of common operations on a database schema. Operations that can be implemented
035: * using general JDBC or ANSI SQL constructs, are impelemented in this base abstract class. Operations that are DBMS
036: * specific are abstract, and their implementation is left to DBMS specific subclasses.
037: * <p/>
038: * todo add getIdentityColumns
039: *
040: * @author Filip Neven
041: * @author Frederick Beernaert
042: * @author Tim Ducheyne
043: */
044: abstract public class DbSupport {
045:
046: /**
047: * Property key for the default identifier casing (lower_case, upper_case, mixed_case, auto)
048: */
049: public static final String PROPKEY_STORED_IDENTIFIER_CASE = "database.storedIndentifierCase";
050:
051: /**
052: * Property key for the default identifier quote string (empty value for not supported, auto)
053: */
054: public static final String PROPKEY_IDENTIFIER_QUOTE_STRING = "database.identifierQuoteString";
055:
056: /* The name of the DBMS implementation that is supported by this implementation */
057: private String databaseDialect;
058:
059: /* The name of the database schema */
060: private String schemaName;
061:
062: /* Gives access to the database */
063: private SQLHandler sqlHandler;
064:
065: /* Indicates whether database identifiers are stored in lowercase, uppercase or mixed case */
066: private StoredIdentifierCase storedIdentifierCase;
067:
068: /* The string that is used to quote identifiers to make them case sensitive, e.g. ", null means quoting not supported*/
069: private String identifierQuoteString;
070:
071: /**
072: * Creates a new, unconfigured instance. To have a instance that can be used, the {@link #init} method must be
073: * called first.
074: *
075: * @param databaseDialect The name of the DBMS implementation that is supported by this implementation, not null
076: */
077: protected DbSupport(String databaseDialect) {
078: this .databaseDialect = databaseDialect;
079: }
080:
081: /**
082: * Initializes this DbSupport object with the given schemaName and dataSource.
083: * If the storedIdentifierCase or identifierQuoteString is set to null, the metadata of the connection will be used to determine the
084: * correct value.
085: *
086: * @param configuration The config, not null
087: * @param sqlHandler The sql handler, not null
088: * @param schemaName The name of the database schema
089: */
090: public void init(Properties configuration, SQLHandler sqlHandler,
091: String schemaName) {
092: this .sqlHandler = sqlHandler;
093:
094: String identifierQuoteStringProperty = PropertyUtils.getString(
095: PROPKEY_IDENTIFIER_QUOTE_STRING + "."
096: + getDatabaseDialect(), configuration);
097: String storedIdentifierCaseValue = PropertyUtils.getString(
098: PROPKEY_STORED_IDENTIFIER_CASE + "."
099: + getDatabaseDialect(), configuration);
100:
101: this .identifierQuoteString = determineIdentifierQuoteString(identifierQuoteStringProperty);
102: this .storedIdentifierCase = determineStoredIdentifierCase(storedIdentifierCaseValue);
103:
104: this .schemaName = toCorrectCaseIdentifier(schemaName);
105: }
106:
107: /**
108: * Gets the database dialect.
109: *
110: * @return the supported dialect, not null
111: */
112: public String getDatabaseDialect() {
113: return databaseDialect;
114: }
115:
116: /**
117: * Gets the schema name.
118: *
119: * @return the schema name, not null
120: */
121: public String getSchemaName() {
122: return schemaName;
123: }
124:
125: /**
126: * Gets the identifier quote string.
127: *
128: * @return the quote string, null if not supported
129: */
130: public String getIdentifierQuoteString() {
131: return identifierQuoteString;
132: }
133:
134: /**
135: * Gets the stored identifier case.
136: *
137: * @return the case, not null
138: */
139: public StoredIdentifierCase getStoredIdentifierCase() {
140: return storedIdentifierCase;
141: }
142:
143: /**
144: * Gets the sql handler.
145: *
146: * @return the data source, not null
147: */
148: public SQLHandler getSQLHandler() {
149: return sqlHandler;
150: }
151:
152: /**
153: * Returns the names of all tables in the database.
154: *
155: * @return The names of all tables in the database
156: */
157: public abstract Set<String> getTableNames();
158:
159: /**
160: * Gets the names of all columns of the given table.
161: *
162: * @param tableName The table, not null
163: * @return The names of the columns of the table with the given name
164: */
165: public abstract Set<String> getColumnNames(String tableName);
166:
167: /**
168: * Gets the names of all primary columns of the given table.
169: *
170: * @param tableName The table, not null
171: * @return The names of the primary key columns of the table with the given name
172: */
173: public abstract Set<String> getPrimaryKeyColumnNames(
174: String tableName);
175:
176: /**
177: * Returns the names of all columns that have a 'not-null' constraint on them
178: *
179: * @param tableName The table, not null
180: * @return The set of column names, not null
181: */
182: public abstract Set<String> getNotNullColummnNames(String tableName);
183:
184: /**
185: * Retrieves the names of all the views in the database schema.
186: *
187: * @return The names of all views in the database
188: */
189: public abstract Set<String> getViewNames();
190:
191: /**
192: * Retrieves the names of all the synonyms in the database schema.
193: *
194: * @return The names of all synonyms in the database
195: */
196: public Set<String> getSynonymNames() {
197: throw new UnsupportedOperationException(
198: "Synonyms not supported.");
199: }
200:
201: /**
202: * Retrieves the names of all the sequences in the database schema.
203: *
204: * @return The names of all sequences in the database
205: */
206: public Set<String> getSequenceNames() {
207: throw new UnsupportedOperationException(
208: "Sequences not supported.");
209: }
210:
211: /**
212: * Retrieves the names of all the triggers in the database schema.
213: *
214: * @return The names of all triggers in the database
215: */
216: public Set<String> getTriggerNames() {
217: throw new UnsupportedOperationException(
218: "Triggers not supported.");
219: }
220:
221: /**
222: * Retrieves the names of all the types in the database schema.
223: *
224: * @return The names of all types in the database
225: */
226: public Set<String> getTypeNames() {
227: throw new UnsupportedOperationException("Types not supported.");
228: }
229:
230: /**
231: * Removes the table with the given name from the database.
232: * Note: the table name is surrounded with quotes, making it case-sensitive.
233: *
234: * @param tableName The table to drop (case-sensitive), not null
235: */
236: public void dropTable(String tableName) {
237: getSQLHandler().executeUpdate(
238: "drop table " + qualified(tableName) + " cascade");
239: }
240:
241: /**
242: * Removes the view with the given name from the database
243: * Note: the view name is surrounded with quotes, making it case-sensitive.
244: *
245: * @param viewName The view to drop (case-sensitive), not null
246: */
247: public void dropView(String viewName) {
248: getSQLHandler().executeUpdate(
249: "drop view " + qualified(viewName) + " cascade");
250: }
251:
252: /**
253: * Removes the synonym with the given name from the database
254: * Note: the synonym name is surrounded with quotes, making it case-sensitive.
255: *
256: * @param synonymName The synonym to drop (case-sensitive), not null
257: */
258: public void dropSynonym(String synonymName) {
259: getSQLHandler().executeUpdate(
260: "drop synonym " + qualified(synonymName));
261: }
262:
263: /**
264: * Drops the sequence with the given name from the database
265: * Note: the sequence name is surrounded with quotes, making it case-sensitive.
266: *
267: * @param sequenceName The sequence to drop (case-sensitive), not null
268: */
269: public void dropSequence(String sequenceName) {
270: getSQLHandler().executeUpdate(
271: "drop sequence " + qualified(sequenceName));
272: }
273:
274: /**
275: * Drops the trigger with the given name from the database
276: * Note: the trigger name is surrounded with quotes, making it case-sensitive.
277: *
278: * @param triggerName The trigger to drop (case-sensitive), not null
279: */
280: public void dropTrigger(String triggerName) {
281: getSQLHandler().executeCodeUpdate(
282: "drop trigger " + qualified(triggerName));
283: }
284:
285: /**
286: * Drops the type with the given name from the database
287: * Note: the type name is surrounded with quotes, making it case-sensitive.
288: *
289: * @param typeName The type to drop (case-sensitive), not null
290: */
291: public void dropType(String typeName) {
292: throw new UnsupportedOperationException(
293: "Types are not supported for " + getDatabaseDialect());
294: }
295:
296: /**
297: * Returns the value of the sequence with the given name.
298: * <p/>
299: * Note: this can have the side-effect of increasing the sequence value.
300: *
301: * @param sequenceName The sequence, not null
302: * @return The value of the sequence with the given name
303: */
304: public long getSequenceValue(String sequenceName) {
305: throw new UnsupportedOperationException(
306: "Sequences not supported for " + getDatabaseDialect());
307: }
308:
309: /**
310: * Sets the next value of the sequence with the given sequence name to the given sequence value.
311: *
312: * @param sequenceName The sequence, not null
313: * @param newSequenceValue The value to set
314: */
315: public void incrementSequenceToValue(String sequenceName,
316: long newSequenceValue) {
317: throw new UnsupportedOperationException(
318: "Sequences not supported for " + getDatabaseDialect());
319: }
320:
321: /**
322: * Increments the identity value for the specified identity column on the specified table to the given value. If there
323: * is no identity specified on the given primary key, the method silently finishes without effect.
324: *
325: * @param tableName The table with the identity column, not null
326: * @param identityColumnName The column, not null
327: * @param identityValue The new value
328: */
329: public void incrementIdentityColumnToValue(String tableName,
330: String identityColumnName, long identityValue) {
331: throw new UnsupportedOperationException(
332: "Identity columns not supported for "
333: + getDatabaseDialect());
334: }
335:
336: /**
337: * Removes the not-null constraint on the specified column and table
338: *
339: * @param tableName The table with the column, not null
340: * @param columnName The column to remove constraints from, not null
341: */
342: public void removeNotNullConstraint(String tableName,
343: String columnName) {
344: throw new UnsupportedOperationException(
345: "Remove not null constraints not supported for "
346: + getDatabaseDialect());
347: }
348:
349: /**
350: * Returns the foreign key constraint names that are enabled/enforced for the table with the given name
351: *
352: * @param tableName The table, not null
353: * @return The set of constraint names, not null
354: */
355: public Set<String> getForeignKeyConstraintNames(String tableName) {
356: throw new UnsupportedOperationException(
357: "Retrieval of table constraints not supported for "
358: + getDatabaseDialect());
359: }
360:
361: /**
362: * Disables the constraint with the given name on table with the given name.
363: *
364: * @param tableName The table with the constraint, not null
365: * @param constraintName The constraint, not null
366: */
367: public void removeForeignKeyConstraint(String tableName,
368: String constraintName) {
369: throw new UnsupportedOperationException(
370: "Disabling of constraints not supported for "
371: + getDatabaseDialect());
372: }
373:
374: /**
375: * Gets the column type suitable to store values of the Java <code>java.lang.Long</code> type.
376: *
377: * @return The column type
378: */
379: public String getLongDataType() {
380: return "BIGINT";
381: }
382:
383: /**
384: * Qualifies the given database object name with the name of the database schema. Quotes are put around both
385: * schemaname and object name. If the schemaName is not supplied, the database object is returned surrounded with
386: * quotes. If the DBMS doesn't support quoted database object names, no quotes are put around neither schema name
387: * nor database object name.
388: *
389: * @param databaseObjectName The database object name to be qualified
390: * @return The qualified database object name
391: */
392: public String qualified(String databaseObjectName) {
393: return quoted(schemaName) + "." + quoted(databaseObjectName);
394: }
395:
396: /**
397: * Put quotes around the given databaseObjectName, if the underlying DBMS supports quoted database object names.
398: * If not, the databaseObjectName is returned unchanged.
399: *
400: * @param databaseObjectName The name, not null
401: * @return Quoted version of the given databaseObjectName, if supported by the underlying DBMS
402: */
403: public String quoted(String databaseObjectName) {
404: if (identifierQuoteString == null) {
405: return databaseObjectName;
406: }
407: return identifierQuoteString + databaseObjectName
408: + identifierQuoteString;
409: }
410:
411: /**
412: * Converts the given identifier to uppercase/lowercase depending on the DBMS. If a value is surrounded with double
413: * quotes (") and the DBMS supports quoted database object names, the case is left untouched and the double quotes
414: * are stripped. These values are treated as case sensitive names.
415: * <p/>
416: * Identifiers can be prefixed with schema names. These schema names will be converted in the same way as described
417: * above. Quoting the schema name will make it case sensitive.
418: * Examples:
419: * <p/>
420: * mySchema.myTable -> MYSCHEMA.MYTABLE
421: * "mySchema".myTable -> mySchema.MYTABLE
422: * "mySchema"."myTable" -> mySchema.myTable
423: *
424: * @param identifier The identifier, not null
425: * @return The name converted to correct case if needed, not null
426: */
427: public String toCorrectCaseIdentifier(String identifier) {
428: identifier = identifier.trim();
429:
430: int index = identifier.indexOf('.');
431: if (index != -1) {
432: String schemaNamePart = identifier.substring(0, index);
433: String identifierPart = identifier.substring(index + 1);
434: return toCorrectCaseIdentifier(schemaNamePart) + "."
435: + toCorrectCaseIdentifier(identifierPart);
436: }
437:
438: if (identifier.startsWith(identifierQuoteString)
439: && identifier.endsWith(identifierQuoteString)) {
440: return identifier.substring(1, identifier.length() - 1);
441: }
442: if (storedIdentifierCase == UPPER_CASE) {
443: return identifier.toUpperCase();
444: } else if (storedIdentifierCase == LOWER_CASE) {
445: return identifier.toLowerCase();
446: } else {
447: return identifier;
448: }
449: }
450:
451: /**
452: * Determines the case the database uses to store non-quoted identifiers. This will use the connections
453: * database metadata to determine the correct case.
454: *
455: * @param storedIdentifierCase The stored case: possible values 'lower_case', 'upper_case', 'mixed_case' and 'auto'
456: * @return The stored case, not null
457: */
458: private StoredIdentifierCase determineStoredIdentifierCase(
459: String storedIdentifierCase) {
460: if ("lower_case".equals(storedIdentifierCase)) {
461: return StoredIdentifierCase.LOWER_CASE;
462: } else if ("upper_case".equals(storedIdentifierCase)) {
463: return StoredIdentifierCase.UPPER_CASE;
464: } else if ("mixed_case".equals(storedIdentifierCase)) {
465: return StoredIdentifierCase.MIXED_CASE;
466: } else if (!"auto".equals(storedIdentifierCase)) {
467: throw new UnitilsException(
468: "Unknown value "
469: + storedIdentifierCase
470: + " for property "
471: + PROPKEY_STORED_IDENTIFIER_CASE
472: + ". It should be one of lower_case, upper_case, mixed_case or auto.");
473: }
474:
475: Connection connection = null;
476: try {
477: connection = getSQLHandler().getDataSource()
478: .getConnection();
479:
480: DatabaseMetaData databaseMetaData = connection
481: .getMetaData();
482: if (databaseMetaData.storesUpperCaseIdentifiers()) {
483: return UPPER_CASE;
484: } else if (databaseMetaData.storesLowerCaseIdentifiers()) {
485: return LOWER_CASE;
486: } else {
487: return MIXED_CASE;
488: }
489: } catch (SQLException e) {
490: throw new UnitilsException(
491: "Unable to determine stored identifier case.", e);
492: } finally {
493: closeQuietly(connection, null, null);
494: }
495: }
496:
497: /**
498: * Determines the string used to quote identifiers to make them case-sensitive. This will use the connections
499: * database metadata to determine the quote string.
500: *
501: * @param identifierQuoteStringProperty The string to quote identifiers, 'none' if quoting is not supported, 'auto' for auto detection
502: * @return The quote string, null if quoting is not supported
503: */
504: private String determineIdentifierQuoteString(
505: String identifierQuoteStringProperty) {
506: if ("none".equals(identifierQuoteStringProperty)) {
507: return null;
508: } else if (!"auto".equals(identifierQuoteStringProperty)) {
509: return identifierQuoteStringProperty;
510: }
511:
512: Connection connection = null;
513: try {
514: connection = getSQLHandler().getDataSource()
515: .getConnection();
516:
517: DatabaseMetaData databaseMetaData = connection
518: .getMetaData();
519: String quoteString = databaseMetaData
520: .getIdentifierQuoteString();
521: if (quoteString == null || "".equals(quoteString.trim())) {
522: return null;
523: }
524: return quoteString;
525:
526: } catch (SQLException e) {
527: throw new UnitilsException(
528: "Unable to determine identifier quote string.", e);
529: } finally {
530: closeQuietly(connection, null, null);
531: }
532: }
533:
534: /**
535: * Indicates whether the underlying DBMS supports synonyms
536: *
537: * @return True if synonyms are supported, false otherwise
538: */
539: public boolean supportsSynonyms() {
540: return false;
541: }
542:
543: /**
544: * Indicates whether the underlying DBMS supports sequences
545: *
546: * @return True if sequences are supported, false otherwise
547: */
548: public boolean supportsSequences() {
549: return false;
550: }
551:
552: /**
553: * Indicates whether the underlying DBMS supports triggers
554: *
555: * @return True if triggers are supported, false otherwise
556: */
557: public boolean supportsTriggers() {
558: return false;
559: }
560:
561: /**
562: * Indicates whether the underlying DBMS supports database types
563: *
564: * @return True if types are supported, false otherwise
565: */
566: public boolean supportsTypes() {
567: return false;
568: }
569:
570: /**
571: * Indicates whether the underlying DBMS supports identity columns
572: *
573: * @return True if identity is supported, false otherwise
574: */
575: public boolean supportsIdentityColumns() {
576: return false;
577: }
578:
579: }
|