Index: apichanges.xml =================================================================== RCS file: /cvs/db/apichanges.xml,v retrieving revision 1.6 diff -u -u -r1.6 apichanges.xml --- apichanges.xml 18 Jun 2007 17:40:42 -0000 1.6 +++ apichanges.xml 11 Sep 2007 21:12:08 -0000 @@ -84,6 +84,21 @@ + Added utility class for working with SQL identifiers + + + + + + This change adds a SQLIdentifier class which + provides utility methods for working with SQL identifiers, such + as quoting the identifier if needed. + + + + + + Added support for dragging and dropping nodes from the Database Explorer Index: arch.xml =================================================================== RCS file: /cvs/db/arch.xml,v retrieving revision 1.15 diff -u -u -r1.15 arch.xml --- arch.xml 11 Jul 2007 20:48:15 -0000 1.15 +++ arch.xml 11 Sep 2007 21:12:08 -0000 @@ -296,6 +296,14 @@ encapsulating those database objects during a drag and drop transfer.

+ +

+ A component might need support for working with SQL identifiers. In particular, + it's important to know when to quote a SQL identifier. The + + SQLIdentifier class is provided for this. +

+ Index: src/org/netbeans/api/db/explorer/support/SQLIdentifiers.java =================================================================== RCS file: src/org/netbeans/api/db/explorer/support/SQLIdentifiers.java diff -N src/org/netbeans/api/db/explorer/support/SQLIdentifiers.java --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ src/org/netbeans/api/db/explorer/support/SQLIdentifiers.java 11 Sep 2007 21:12:08 -0000 @@ -0,0 +1,239 @@ +/* + * The contents of this file are subject to the terms of the Common Development + * and Distribution License (the License). You may not use this file except in + * compliance with the License. + * + * You can obtain a copy of the License at http://www.netbeans.org/cddl.html + * or http://www.netbeans.org/cddl.txt. + * + * When distributing Covered Code, include this CDDL Header Notice in each file + * and include the License file at http://www.netbeans.org/cddl.txt. + * If applicable, add the following below the CDDL Header, with the fields + * enclosed by brackets [] replaced by your own identifying information: + * "Portions Copyrighted [year] [name of copyright owner]" + * + * Portions Copyrighted 2007 Sun Microsystems, Inc. + */ +package org.netbeans.api.db.explorer.support; + +import java.sql.DatabaseMetaData; +import java.sql.SQLException; +import java.util.logging.Level; +import java.util.logging.Logger; +import org.openide.util.Parameters; + +/** + * This class provides utility methods for working with SQL identifiers + */ +public final class SQLIdentifiers { + private static final Logger LOGGER = + Logger.getLogger(SQLIdentifiers.class.getName()); + + /** + * Construct an instance of SQLIdentifier. + * + * @param dbmd The DatabaseMetaData to use when working with identifiers. + * The metadata object is used to determine when an identifier needs + * to be quoted and what the quote string should be. + */ + public static Quoter createQuoter(DatabaseMetaData dbmd) { + return new Quoter(dbmd); + } + + + /** + * This is a utility class that is used to quote identifiers. + * + * This class is immutable and thus thread-safe + */ + public static class Quoter { + private static final Logger LOGGER = + Logger.getLogger(Quoter.class.getName()); + + // Rules for what happens to the casing of a character in an identifier + // when it is not quoted + private static final int LC_RULE = 0; // everything goes to lower case + private static final int UC_RULE = 1; // everything goes to upper case + private static final int MC_RULE = 2; // mixed case remains mixed case + + private final String extraNameChars; + private final String quoteString; + private final int caseRule; + + private Quoter(DatabaseMetaData dbmd) { + extraNameChars = getExtraNameChars(dbmd); + quoteString = getQuoteString(dbmd); + caseRule = getCaseRule(dbmd); + } + + /** + * Quote an existing identifier to be used in a SQL command, + * if needed. + *

+ * Anyone generating SQL that will be + * visible and/or editable by the user should use this method. + * This helps to avoid unecessary quoting, which affects the + * readability and clarity of the resulting SQL. + *

+ * An identifier needs to be quoted if one of the following is true: + *

    + *
  • any character in the + * string is not within the set of characters that do + * not need to be quoted in a SQL identifier. + * + *
  • any character in the string is not of the + * expected casing (e.g. lower case when the database upper-cases + * all non-quoted identifiers). + *
+ * + * @param identifier a SQL identifier. Can not be null. + * + * @return the identifier, quoted if needed + */ + public final String quoteIfNeeded(String identifier) { + Parameters.notNull("identifier", identifier); + + if ( needToQuote(identifier) ) { + return quoteString + identifier + quoteString; + } + + return identifier; + } + + /** + * Determine if we need to quote this identifier + */ + private boolean needToQuote(String identifier) { + if ( identifier == null ) { + throw new NullPointerException("identifier can not be null"); + } + + // No need to quote if it's already quoted + if ( identifier.startsWith(quoteString) && + identifier.endsWith(quoteString)) { + return false; + } + + int length = identifier.length(); + for ( int i = 0 ; i < length ; i++ ) { + if ( charNeedsQuoting(identifier.charAt(i))) { + return true; + } + } + + // Next, check to see if any characters are in the wrong casing + // (for example, if the db upper cases all non-quoted identifiers, + // and we have a lower-case character, then we need to quote + if ( caseRule == UC_RULE && containsLowerCase(identifier)) { + return true; + } else if ( caseRule == LC_RULE && containsUpperCase(identifier)) { + return true; + } + + return false; + } + + private boolean charNeedsQuoting(char ch) { + // Standard set of characters for SQL identifiers + if ( isUpperCase(ch) || isLowerCase(ch) || + isNumber(ch) || ch == '_') { + return false; + } + + // Check if this database accepts some extra characters + if ( extraNameChars.indexOf(ch) >= 0 ) { + return false; + } + + return true; + } + + private static boolean isUpperCase(char ch) { + return ch >= 'A' && ch <= 'Z'; + } + + private static boolean isLowerCase(char ch) { + return ch >= 'a' && ch <= 'z'; + } + + private static boolean isNumber(char ch) { + return ch >= '0' && ch <= '9'; + } + + private static boolean containsLowerCase(String identifier) { + int length = identifier.length(); + for ( int i = 0 ; i < length ; i++ ) { + if ( isLowerCase(identifier.charAt(i)) ) { + return true; + } + } + + return false; + } + + private static boolean containsUpperCase(String identifier) { + + int length = identifier.length(); + for ( int i = 0 ; i < length ; i++ ) { + if ( isUpperCase(identifier.charAt(i)) ) { + return true; + } + } + + return false; + } + + private String getExtraNameChars(DatabaseMetaData dbmd) { + String chars = ""; + try { + chars = dbmd.getExtraNameCharacters(); + } catch ( SQLException e ) { + LOGGER.log(Level.WARNING, "DatabaseMetaData.getExtraNameCharacters()" + + " failed (" + e.getMessage() + "). " + + "Using standard set of characters"); + LOGGER.log(Level.FINE, null, e); + } + + return chars; + } + + private String getQuoteString(DatabaseMetaData dbmd) { + String quoteStr = "\""; + + try { + quoteStr = dbmd.getIdentifierQuoteString().trim(); + } catch ( SQLException e ) { + LOGGER.log(Level.WARNING, "DatabaseMetaData.getIdentifierQuoteString()" + + " failed (" + e.getMessage() + "). " + + "Using '\"' for quoting SQL identifiers"); + LOGGER.log(Level.FINE, null, e); + } + + return quoteStr; + } + + private int getCaseRule(DatabaseMetaData dbmd) { + int rule = UC_RULE; + + try { + if ( dbmd.storesUpperCaseIdentifiers() ) { + rule = UC_RULE; + } else if ( dbmd.storesLowerCaseIdentifiers() ) { + rule = LC_RULE; + } else if ( dbmd.storesMixedCaseIdentifiers() ) { + rule = MC_RULE; + } else { + rule = UC_RULE; + } + } catch ( SQLException sqle ) { + LOGGER.log(Level.WARNING, "Exception trying to find out how " + + "the database stores unquoted identifiers, assuming " + + "upper case: " + sqle.getMessage()); + LOGGER.log(Level.FINE, null, sqle); + } + + return rule; + } + } + +} Index: test/unit/src/org/netbeans/api/db/explorer/support/QuoterTest.java =================================================================== RCS file: test/unit/src/org/netbeans/api/db/explorer/support/QuoterTest.java diff -N test/unit/src/org/netbeans/api/db/explorer/support/QuoterTest.java --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ test/unit/src/org/netbeans/api/db/explorer/support/QuoterTest.java 11 Sep 2007 21:12:08 -0000 @@ -0,0 +1,120 @@ +/* + * The contents of this file are subject to the terms of the Common Development + * and Distribution License (the License). You may not use this file except in + * compliance with the License. + * + * You can obtain a copy of the License at http://www.netbeans.org/cddl.html + * or http://www.netbeans.org/cddl.txt. + * + * When distributing Covered Code, include this CDDL Header Notice in each file + * and include the License file at http://www.netbeans.org/cddl.txt. + * If applicable, add the following below the CDDL Header, with the fields + * enclosed by brackets [] replaced by your own identifying information: + * "Portions Copyrighted [year] [name of copyright owner]" + * + * Portions Copyrighted 2007 Sun Microsystems, Inc. + */ +package org.netbeans.api.db.explorer.support; + +import org.netbeans.modules.db.util.DBTestBase; + +/** + * @author David Van Couvering + * + * This class is a set of tests to make sure we're quoting identifiers + * correctly + */ +public class QuoterTest extends DBTestBase { + + private SQLIdentifiers.Quoter quoter; + + public QuoterTest(String testName) { + super(testName); + } + + public void setUp() throws Exception { + super.setUp(); + quoter = SQLIdentifiers.createQuoter(conn.getMetaData()); + } + + public void testNoQuoting() { + String identifier = "YOUDONTNEEDTOQUOTEME2334252__1451"; + String expResult = identifier; + String result = quoter.quoteIfNeeded(identifier); + assertEquals(expResult, result); + } + + public void testSpaces() throws Exception { + String identifier = "YesYou Need to quote me"; + String expResult = quote(identifier); + + String result = quoter.quoteIfNeeded(identifier); + + assertEquals(expResult, result); + } + + public void testCasing() throws Exception { + String identifier; + + // First, find out what kind of casing is done with unquoted + // identifiers for this connection + int caseRule = getUnquotedCaseRule(); + + switch (caseRule) { + case LC_RULE: + identifier = "ABCDEFG"; + break; + case UC_RULE: + identifier = "abcdefg"; + break; + default: + // Nothing to test here + return; + } + + String expResult = quote(identifier); + + String result = quoter.quoteIfNeeded(identifier); + + assertEquals(expResult, result); + } + + public void testNonAscii() throws Exception { + // borrowed translated message from Derby message file :) + String identifier = "abcdABCD0934" + + "\u4f8b\u5916\u306e\u305f\u3081\u3001\u59cb\u52d5" + + "\u306b\u5931\u6557\u3057\u307e\u3057\u305f\u3002 \u8a73\u7d30" + + "\u306b\u3064\u3044\u3066\u306f\u3001\u6b21\u306e\u4f8b\u5916" + + "\u3092\u53c2\u7167\u3057\u3066\u304f\u3060\u3055\u3044\u3002" + + "09298719871"; + + String expResult = quote(identifier); + + String result = quoter.quoteIfNeeded(identifier); + + assertEquals(expResult, result); + } + + public void testDontQuoteQuoted() throws Exception { + String identifier = quote("I am already quoted"); + + String expResult = identifier; + + String result = quoter.quoteIfNeeded(identifier); + + assertEquals(expResult, result); + } + + public void testNullIdentifier() throws Exception { + boolean gotException = false; + try { + quoter.quoteIfNeeded(null); + } catch ( NullPointerException npe ) { + // expected + gotException = true; + } + + assertTrue(gotException); + } + +} Index: test/unit/src/org/netbeans/modules/db/util/DBTestBase.java =================================================================== RCS file: /cvs/db/test/unit/src/org/netbeans/modules/db/util/DBTestBase.java,v retrieving revision 1.2 diff -u -u -r1.2 DBTestBase.java --- test/unit/src/org/netbeans/modules/db/util/DBTestBase.java 31 Aug 2007 17:48:12 -0000 1.2 +++ test/unit/src/org/netbeans/modules/db/util/DBTestBase.java 11 Sep 2007 21:12:08 -0000 @@ -78,8 +78,8 @@ public static final int MC_RULE = 2; // mixed case remains mixed case public static final int QUOTE_RETAINS_CASE = 3; // quoted idents retain case - private static int identRule = RULE_UNDEFINED; - private static int quotedIdentRule = RULE_UNDEFINED; + private static int unquotedCaseRule = RULE_UNDEFINED; + private static int quotedCaseRule = RULE_UNDEFINED; protected static SpecificationFactory specfactory; @@ -442,12 +442,12 @@ * require identifiers to be in proper case */ public String fixIdentifier(String ident) throws Exception { - if ( identRule == RULE_UNDEFINED ) { - getIdentRules(); + if ( unquotedCaseRule == RULE_UNDEFINED ) { + getCaseRules(); } if ( isQuoted(ident) ) { - switch ( quotedIdentRule ) { + switch ( quotedCaseRule ) { case QUOTE_RETAINS_CASE: break; case UC_RULE: @@ -460,12 +460,12 @@ break; default: LOGGER.log(Level.WARNING, "Unexpected identifier rule: +" + - identRule + ", assuming case is retained"); + unquotedCaseRule + ", assuming case is retained"); } return ident.substring(1, ident.length() -1); } else { - switch ( identRule ) { + switch ( unquotedCaseRule ) { case UC_RULE: return ident.toUpperCase(); case LC_RULE: @@ -474,7 +474,7 @@ return ident; default: LOGGER.log(Level.WARNING, "Unexpected identifier rule: +" + - identRule + ", assuming upper case"); + unquotedCaseRule + ", assuming upper case"); return ident.toUpperCase(); } } @@ -486,7 +486,12 @@ return ident.startsWith(quoteString) && ident.endsWith(quoteString); } - private void getIdentRules() throws Exception { + public int getUnquotedCaseRule() throws Exception { + getCaseRules(); + return unquotedCaseRule; + } + + private void getCaseRules() throws Exception { assert conn != null; DatabaseMetaData md; @@ -494,13 +499,13 @@ try { md = conn.getMetaData(); if ( md.storesUpperCaseIdentifiers() ) { - identRule = UC_RULE; + unquotedCaseRule = UC_RULE; } else if ( md.storesLowerCaseIdentifiers() ) { - identRule = LC_RULE; + unquotedCaseRule = LC_RULE; } else if ( md.storesMixedCaseIdentifiers() ) { - identRule = MC_RULE; + unquotedCaseRule = MC_RULE; } else { - identRule = UC_RULE; + unquotedCaseRule = UC_RULE; } } catch ( SQLException sqle ) { LOGGER.log(Level.INFO, "Exception trying to find out how " + @@ -508,20 +513,20 @@ sqle.getMessage()); LOGGER.log(Level.FINE, null, sqle); - identRule = UC_RULE; + unquotedCaseRule = UC_RULE; } try { md = conn.getMetaData(); if ( md.storesLowerCaseQuotedIdentifiers() ) { - quotedIdentRule = LC_RULE; + quotedCaseRule = LC_RULE; } else if ( md.storesUpperCaseQuotedIdentifiers() ) { - quotedIdentRule = UC_RULE; + quotedCaseRule = UC_RULE; } else if ( md.storesMixedCaseQuotedIdentifiers() ) { - quotedIdentRule = MC_RULE; + quotedCaseRule = MC_RULE; } else { - quotedIdentRule = QUOTE_RETAINS_CASE; + quotedCaseRule = QUOTE_RETAINS_CASE; } } catch ( SQLException sqle ) { LOGGER.log(Level.INFO, "Exception trying to find out how " + @@ -529,7 +534,7 @@ sqle.getMessage()); LOGGER.log(Level.FINE, null, sqle); - quotedIdentRule = QUOTE_RETAINS_CASE; + quotedCaseRule = QUOTE_RETAINS_CASE; } }