Attachment #48607 for bug #115075

This Bugzilla instance is a read-only archive of historic NetBeans bug reports. To report a bug in NetBeans please follow the project's instructions for reporting issues.

View | Details | Raw Unified | Return to bug 115075
Collapse All | Expand All




    <changes>
        <change>
            <api name="database_explorer_api"/>
            <summary>Added utility class for working with SQL identifiers</summary>
            <version major="1" minor="22"/>
            <date day="10" month="9" year="2007"/>
            <author login="davidvc"/>
            <compatibility addition="yes"/>
            <description>
                This change adds a <code>SQLIdentifier</code> class which
                provides utility methods for working with SQL identifiers, such
                as quoting the identifier if needed.
            </description>
            <class package="org.netbeans.api.db.explorer.support" name="SQLIdentifier"/>
            <issue number="115075"/>
        </change>
        <change>
            <api name="database_explorer_api"/>
            <summary>Added support for dragging and dropping nodes from the Database Explorer</summary>
            <version major="1" minor="21"/>
            <date day="17" month="6" year="2007"/>

Lines 296-301 Link Here

(-)arch.xml (+8 lines)
296	encapsulating those database objects during a drag and drop transfer.	296	encapsulating those database objects during a drag and drop transfer.
297	</p>	297	</p>
298	</usecase>	298	</usecase>
		299	<usecase id="sql-identifier-support" name="Get support for working with SQL identifiers">
		300	<p>
		301	A component might need support for working with SQL identifiers. In particular,
		302	it's important to know when to quote a SQL identifier. The
		303	<a href="@TOP@org/netbeans/api/db/explorer/support/SQLIdentifier.html">
		304	SQLIdentifier</a> class is provided for this.
		305	</p>
		306	</usecase>
299	</answer>	307	</answer>
300		308
301		309




/*
 * 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 <b>existing</b> identifier to be used in a SQL command, 
         * if needed.
         * <p>
         * 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.
         * <p>
         * An identifier needs to be quoted if one of the following is true:
         * <ul>
         * <li>any character in the
         * string is not within the set of characters that do
         * not need to be quoted in a SQL identifier.
         * 
         * <li>any character in the string is not of the
         * expected casing (e.g. lower case when the database upper-cases
         * all non-quoted identifiers).
         * </ul>
         * 
         * @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;        
        }
    }

}




/*
 * 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 <a href="mailto:david@vancouvering.com">David Van Couvering</a>
 * 
 * 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);
    }

}




    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    unquotedCaseRule = RULE_UNDEFINED;
    private static int    quotedCaseRule = RULE_UNDEFINED;

    protected static SpecificationFactory specfactory;
    

     * require identifiers to be in proper case
     */
    public String fixIdentifier(String ident) throws Exception {
        if ( unquotedCaseRule == RULE_UNDEFINED ) {
            getCaseRules();
        }
        
        if ( isQuoted(ident) ) {
            switch ( quotedCaseRule ) {
                case QUOTE_RETAINS_CASE:
                    break;
                case UC_RULE:

                    break;
                default:
                    LOGGER.log(Level.WARNING, "Unexpected identifier rule: +" +
                            unquotedCaseRule + ", assuming case is retained");
            }
            
            return ident.substring(1, ident.length() -1);
        } else {
            switch ( unquotedCaseRule ) {
                case UC_RULE:
                    return ident.toUpperCase();
                case LC_RULE:

                    return ident;
                default:
                    LOGGER.log(Level.WARNING, "Unexpected identifier rule: +" +
                            unquotedCaseRule + ", assuming upper case");
                    return ident.toUpperCase();
            }            
        }

        return ident.startsWith(quoteString) && ident.endsWith(quoteString);
    }
    
    public int getUnquotedCaseRule() throws Exception {
        getCaseRules();
        return unquotedCaseRule;
    }
    
    private void getCaseRules() throws Exception {
        assert conn != null;

        DatabaseMetaData md;

        try {
            md = conn.getMetaData();
            if ( md.storesUpperCaseIdentifiers() ) {
                unquotedCaseRule = UC_RULE;
            } else if ( md.storesLowerCaseIdentifiers() ) {
                unquotedCaseRule = LC_RULE;
            } else if ( md.storesMixedCaseIdentifiers() ) {
                unquotedCaseRule = MC_RULE;
            } else {
                unquotedCaseRule = UC_RULE;
            }
        } catch ( SQLException sqle ) {
            LOGGER.log(Level.INFO, "Exception trying to find out how " +

                    sqle.getMessage());
            LOGGER.log(Level.FINE, null, sqle);
            
            unquotedCaseRule = UC_RULE;
        }

        try {
            md = conn.getMetaData();
            
            if ( md.storesLowerCaseQuotedIdentifiers() ) {
                quotedCaseRule = LC_RULE;
            } else if ( md.storesUpperCaseQuotedIdentifiers() ) {
                quotedCaseRule = UC_RULE;
            } else if ( md.storesMixedCaseQuotedIdentifiers() ) {
                quotedCaseRule = MC_RULE;
            } else {
                quotedCaseRule = QUOTE_RETAINS_CASE;
            }
        } catch ( SQLException sqle ) {
            LOGGER.log(Level.INFO, "Exception trying to find out how " +

                    sqle.getMessage());
            LOGGER.log(Level.FINE, null, sqle);
            
            quotedCaseRule = QUOTE_RETAINS_CASE;
        }
    }
    

Return to bug 115075

Lines 78-85 Link Here

(-)test/unit/src/org/netbeans/modules/db/util/DBTestBase.java (-19 / +24 lines)
78	public static final int MC_RULE = 2; // mixed case remains mixed case	78	public static final int MC_RULE = 2; // mixed case remains mixed case
79	public static final int QUOTE_RETAINS_CASE = 3; // quoted idents retain case	79	public static final int QUOTE_RETAINS_CASE = 3; // quoted idents retain case
80		80
81	private static int identRule = RULE_UNDEFINED;	81	private static int unquotedCaseRule = RULE_UNDEFINED;
82	private static int quotedIdentRule = RULE_UNDEFINED;	82	private static int quotedCaseRule = RULE_UNDEFINED;
83		83
84	protected static SpecificationFactory specfactory;	84	protected static SpecificationFactory specfactory;
85		85
Lines 442-453 Link Here
442	* require identifiers to be in proper case	442	* require identifiers to be in proper case
443	*/	443	*/
444	public String fixIdentifier(String ident) throws Exception {	444	public String fixIdentifier(String ident) throws Exception {
445	if ( identRule == RULE_UNDEFINED ) {	445	if ( unquotedCaseRule == RULE_UNDEFINED ) {
446	getIdentRules();	446	getCaseRules();
447	}	447	}
448		448
449	if ( isQuoted(ident) ) {	449	if ( isQuoted(ident) ) {
450	switch ( quotedIdentRule ) {	450	switch ( quotedCaseRule ) {
451	case QUOTE_RETAINS_CASE:	451	case QUOTE_RETAINS_CASE:
452	break;	452	break;
453	case UC_RULE:	453	case UC_RULE:
Lines 460-471 Link Here
460	break;	460	break;
461	default:	461	default:
462	LOGGER.log(Level.WARNING, "Unexpected identifier rule: +" +	462	LOGGER.log(Level.WARNING, "Unexpected identifier rule: +" +
463	identRule + ", assuming case is retained");	463	unquotedCaseRule + ", assuming case is retained");
464	}	464	}
465		465
466	return ident.substring(1, ident.length() -1);	466	return ident.substring(1, ident.length() -1);
467	} else {	467	} else {
468	switch ( identRule ) {	468	switch ( unquotedCaseRule ) {
469	case UC_RULE:	469	case UC_RULE:
470	return ident.toUpperCase();	470	return ident.toUpperCase();
471	case LC_RULE:	471	case LC_RULE:
Lines 474-480 Link Here
474	return ident;	474	return ident;
475	default:	475	default:
476	LOGGER.log(Level.WARNING, "Unexpected identifier rule: +" +	476	LOGGER.log(Level.WARNING, "Unexpected identifier rule: +" +
477	identRule + ", assuming upper case");	477	unquotedCaseRule + ", assuming upper case");
478	return ident.toUpperCase();	478	return ident.toUpperCase();
479	}	479	}
480	}	480	}
Lines 486-492 Link Here
486	return ident.startsWith(quoteString) && ident.endsWith(quoteString);	486	return ident.startsWith(quoteString) && ident.endsWith(quoteString);
487	}	487	}
488		488
489	private void getIdentRules() throws Exception {	489	public int getUnquotedCaseRule() throws Exception {
		490	getCaseRules();
		491	return unquotedCaseRule;
		492	}
		493
		494	private void getCaseRules() throws Exception {
490	assert conn != null;	495	assert conn != null;
491		496
492	DatabaseMetaData md;	497	DatabaseMetaData md;
Lines 494-506 Link Here
494	try {	499	try {
495	md = conn.getMetaData();	500	md = conn.getMetaData();
496	if ( md.storesUpperCaseIdentifiers() ) {	501	if ( md.storesUpperCaseIdentifiers() ) {
497	identRule = UC_RULE;	502	unquotedCaseRule = UC_RULE;
498	} else if ( md.storesLowerCaseIdentifiers() ) {	503	} else if ( md.storesLowerCaseIdentifiers() ) {
499	identRule = LC_RULE;	504	unquotedCaseRule = LC_RULE;
500	} else if ( md.storesMixedCaseIdentifiers() ) {	505	} else if ( md.storesMixedCaseIdentifiers() ) {
501	identRule = MC_RULE;	506	unquotedCaseRule = MC_RULE;
502	} else {	507	} else {
503	identRule = UC_RULE;	508	unquotedCaseRule = UC_RULE;
504	}	509	}
505	} catch ( SQLException sqle ) {	510	} catch ( SQLException sqle ) {
506	LOGGER.log(Level.INFO, "Exception trying to find out how " +	511	LOGGER.log(Level.INFO, "Exception trying to find out how " +
Lines 508-527 Link Here
508	sqle.getMessage());	513	sqle.getMessage());
509	LOGGER.log(Level.FINE, null, sqle);	514	LOGGER.log(Level.FINE, null, sqle);
510		515
511	identRule = UC_RULE;	516	unquotedCaseRule = UC_RULE;
512	}	517	}
513		518
514	try {	519	try {
515	md = conn.getMetaData();	520	md = conn.getMetaData();
516		521
517	if ( md.storesLowerCaseQuotedIdentifiers() ) {	522	if ( md.storesLowerCaseQuotedIdentifiers() ) {
518	quotedIdentRule = LC_RULE;	523	quotedCaseRule = LC_RULE;
519	} else if ( md.storesUpperCaseQuotedIdentifiers() ) {	524	} else if ( md.storesUpperCaseQuotedIdentifiers() ) {
520	quotedIdentRule = UC_RULE;	525	quotedCaseRule = UC_RULE;
521	} else if ( md.storesMixedCaseQuotedIdentifiers() ) {	526	} else if ( md.storesMixedCaseQuotedIdentifiers() ) {
522	quotedIdentRule = MC_RULE;	527	quotedCaseRule = MC_RULE;
523	} else {	528	} else {
524	quotedIdentRule = QUOTE_RETAINS_CASE;	529	quotedCaseRule = QUOTE_RETAINS_CASE;
525	}	530	}
526	} catch ( SQLException sqle ) {	531	} catch ( SQLException sqle ) {
527	LOGGER.log(Level.INFO, "Exception trying to find out how " +	532	LOGGER.log(Level.INFO, "Exception trying to find out how " +
Lines 529-535 Link Here
529	sqle.getMessage());	534	sqle.getMessage());
530	LOGGER.log(Level.FINE, null, sqle);	535	LOGGER.log(Level.FINE, null, sqle);
531		536
532	quotedIdentRule = QUOTE_RETAINS_CASE;	537	quotedCaseRule = QUOTE_RETAINS_CASE;
533	}	538	}
534	}	539	}
535		540

Lines 84-89 Link Here

(-)apichanges.xml (+15 lines)
84	<changes>	84	<changes>
85	<change>	85	<change>
86	<api name="database_explorer_api"/>	86	<api name="database_explorer_api"/>
		87	<summary>Added utility class for working with SQL identifiers</summary>
		88	<version major="1" minor="22"/>
		89	<date day="10" month="9" year="2007"/>
		90	<author login="davidvc"/>
		91	<compatibility addition="yes"/>
		92	<description>
		93	This change adds a <code>SQLIdentifier</code> class which
		94	provides utility methods for working with SQL identifiers, such
		95	as quoting the identifier if needed.
		96	</description>
		97	<class package="org.netbeans.api.db.explorer.support" name="SQLIdentifier"/>
		98	<issue number="115075"/>
		99	</change>
		100	<change>
		101	<api name="database_explorer_api"/>
87	<summary>Added support for dragging and dropping nodes from the Database Explorer</summary>	102	<summary>Added support for dragging and dropping nodes from the Database Explorer</summary>
88	<version major="1" minor="21"/>	103	<version major="1" minor="21"/>
89	<date day="17" month="6" year="2007"/>	104	<date day="17" month="6" year="2007"/>

Added Link Here

(-)src/org/netbeans/api/db/explorer/support/SQLIdentifiers.java (+239 lines)
1	/*
2	* The contents of this file are subject to the terms of the Common Development
3	* and Distribution License (the License). You may not use this file except in
4	* compliance with the License.
5	*
6	* You can obtain a copy of the License at http://www.netbeans.org/cddl.html
7	* or http://www.netbeans.org/cddl.txt.
8	*
9	* When distributing Covered Code, include this CDDL Header Notice in each file
10	* and include the License file at http://www.netbeans.org/cddl.txt.
11	* If applicable, add the following below the CDDL Header, with the fields
12	* enclosed by brackets [] replaced by your own identifying information:
13	* "Portions Copyrighted [year] [name of copyright owner]"
14	*
15	* Portions Copyrighted 2007 Sun Microsystems, Inc.
16	*/
17	package org.netbeans.api.db.explorer.support;
18
19	import java.sql.DatabaseMetaData;
20	import java.sql.SQLException;
21	import java.util.logging.Level;
22	import java.util.logging.Logger;
23	import org.openide.util.Parameters;
24
25	/**
26	* This class provides utility methods for working with SQL identifiers
27	*/
28	public final class SQLIdentifiers {
29	private static final Logger LOGGER =
30	Logger.getLogger(SQLIdentifiers.class.getName());
31
32	/**
33	* Construct an instance of SQLIdentifier.
34	*
35	* @param dbmd The DatabaseMetaData to use when working with identifiers.
36	* The metadata object is used to determine when an identifier needs
37	* to be quoted and what the quote string should be.
38	*/
39	public static Quoter createQuoter(DatabaseMetaData dbmd) {
40	return new Quoter(dbmd);
41	}
42
43
44	/**
45	* This is a utility class that is used to quote identifiers.
46	*
47	* This class is immutable and thus thread-safe
48	*/
49	public static class Quoter {
50	private static final Logger LOGGER =
51	Logger.getLogger(Quoter.class.getName());
52
53	// Rules for what happens to the casing of a character in an identifier
54	// when it is not quoted
55	private static final int LC_RULE = 0; // everything goes to lower case
56	private static final int UC_RULE = 1; // everything goes to upper case
57	private static final int MC_RULE = 2; // mixed case remains mixed case
58
59	private final String extraNameChars;
60	private final String quoteString;
61	private final int caseRule;
62
63	private Quoter(DatabaseMetaData dbmd) {
64	extraNameChars = getExtraNameChars(dbmd);
65	quoteString = getQuoteString(dbmd);
66	caseRule = getCaseRule(dbmd);
67	}
68
69	/**
70	* Quote an <b>existing</b> identifier to be used in a SQL command,
71	* if needed.
72	* <p>
73	* Anyone generating SQL that will be
74	* visible and/or editable by the user should use this method.
75	* This helps to avoid unecessary quoting, which affects the
76	* readability and clarity of the resulting SQL.
77	* <p>
78	* An identifier needs to be quoted if one of the following is true:
79	* <ul>
80	* <li>any character in the
81	* string is not within the set of characters that do
82	* not need to be quoted in a SQL identifier.
83	*
84	* <li>any character in the string is not of the
85	* expected casing (e.g. lower case when the database upper-cases
86	* all non-quoted identifiers).
87	* </ul>
88	*
89	* @param identifier a SQL identifier. Can not be null.
90	*
91	* @return the identifier, quoted if needed
92	*/
93	public final String quoteIfNeeded(String identifier) {
94	Parameters.notNull("identifier", identifier);
95
96	if ( needToQuote(identifier) ) {
97	return quoteString + identifier + quoteString;
98	}
99
100	return identifier;
101	}
102
103	/**
104	* Determine if we need to quote this identifier
105	*/
106	private boolean needToQuote(String identifier) {
107	if ( identifier == null ) {
108	throw new NullPointerException("identifier can not be null");
109	}
110
111	// No need to quote if it's already quoted
112	if ( identifier.startsWith(quoteString) &&
113	identifier.endsWith(quoteString)) {
114	return false;
115	}
116
117	int length = identifier.length();
118	for ( int i = 0 ; i < length ; i++ ) {
119	if ( charNeedsQuoting(identifier.charAt(i))) {
120	return true;
121	}
122	}
123
124	// Next, check to see if any characters are in the wrong casing
125	// (for example, if the db upper cases all non-quoted identifiers,
126	// and we have a lower-case character, then we need to quote
127	if ( caseRule == UC_RULE && containsLowerCase(identifier)) {
128	return true;
129	} else if ( caseRule == LC_RULE && containsUpperCase(identifier)) {
130	return true;
131	}
132
133	return false;
134	}
135
136	private boolean charNeedsQuoting(char ch) {
137	// Standard set of characters for SQL identifiers
138	if ( isUpperCase(ch) \|\| isLowerCase(ch) \|\|
139	isNumber(ch) \|\| ch == '_') {
140	return false;
141	}
142
143	// Check if this database accepts some extra characters
144	if ( extraNameChars.indexOf(ch) >= 0 ) {
145	return false;
146	}
147
148	return true;
149	}
150
151	private static boolean isUpperCase(char ch) {
152	return ch >= 'A' && ch <= 'Z';
153	}
154
155	private static boolean isLowerCase(char ch) {
156	return ch >= 'a' && ch <= 'z';
157	}
158
159	private static boolean isNumber(char ch) {
160	return ch >= '0' && ch <= '9';
161	}
162
163	private static boolean containsLowerCase(String identifier) {
164	int length = identifier.length();
165	for ( int i = 0 ; i < length ; i++ ) {
166	if ( isLowerCase(identifier.charAt(i)) ) {
167	return true;
168	}
169	}
170
171	return false;
172	}
173
174	private static boolean containsUpperCase(String identifier) {
175
176	int length = identifier.length();
177	for ( int i = 0 ; i < length ; i++ ) {
178	if ( isUpperCase(identifier.charAt(i)) ) {
179	return true;
180	}
181	}
182
183	return false;
184	}
185
186	private String getExtraNameChars(DatabaseMetaData dbmd) {
187	String chars = "";
188	try {
189	chars = dbmd.getExtraNameCharacters();
190	} catch ( SQLException e ) {
191	LOGGER.log(Level.WARNING, "DatabaseMetaData.getExtraNameCharacters()"
192	+ " failed (" + e.getMessage() + "). " +
193	"Using standard set of characters");
194	LOGGER.log(Level.FINE, null, e);
195	}
196
197	return chars;
198	}
199
200	private String getQuoteString(DatabaseMetaData dbmd) {
201	String quoteStr = "\"";
202
203	try {
204	quoteStr = dbmd.getIdentifierQuoteString().trim();
205	} catch ( SQLException e ) {
206	LOGGER.log(Level.WARNING, "DatabaseMetaData.getIdentifierQuoteString()"
207	+ " failed (" + e.getMessage() + "). " +
208	"Using '\"' for quoting SQL identifiers");
209	LOGGER.log(Level.FINE, null, e);
210	}
211
212	return quoteStr;
213	}
214
215	private int getCaseRule(DatabaseMetaData dbmd) {
216	int rule = UC_RULE;
217
218	try {
219	if ( dbmd.storesUpperCaseIdentifiers() ) {
220	rule = UC_RULE;
221	} else if ( dbmd.storesLowerCaseIdentifiers() ) {
222	rule = LC_RULE;
223	} else if ( dbmd.storesMixedCaseIdentifiers() ) {
224	rule = MC_RULE;
225	} else {
226	rule = UC_RULE;
227	}
228	} catch ( SQLException sqle ) {
229	LOGGER.log(Level.WARNING, "Exception trying to find out how " +
230	"the database stores unquoted identifiers, assuming " +
231	"upper case: " + sqle.getMessage());
232	LOGGER.log(Level.FINE, null, sqle);
233	}
234
235	return rule;
236	}
237	}
238
239	}

Added Link Here

(-)test/unit/src/org/netbeans/api/db/explorer/support/QuoterTest.java (+120 lines)
1	/*
2	* The contents of this file are subject to the terms of the Common Development
3	* and Distribution License (the License). You may not use this file except in
4	* compliance with the License.
5	*
6	* You can obtain a copy of the License at http://www.netbeans.org/cddl.html
7	* or http://www.netbeans.org/cddl.txt.
8	*
9	* When distributing Covered Code, include this CDDL Header Notice in each file
10	* and include the License file at http://www.netbeans.org/cddl.txt.
11	* If applicable, add the following below the CDDL Header, with the fields
12	* enclosed by brackets [] replaced by your own identifying information:
13	* "Portions Copyrighted [year] [name of copyright owner]"
14	*
15	* Portions Copyrighted 2007 Sun Microsystems, Inc.
16	*/
17	package org.netbeans.api.db.explorer.support;
18
19	import org.netbeans.modules.db.util.DBTestBase;
20
21	/**
22	* @author <a href="mailto:david@vancouvering.com">David Van Couvering</a>
23	*
24	* This class is a set of tests to make sure we're quoting identifiers
25	* correctly
26	*/
27	public class QuoterTest extends DBTestBase {
28
29	private SQLIdentifiers.Quoter quoter;
30
31	public QuoterTest(String testName) {
32	super(testName);
33	}
34
35	public void setUp() throws Exception {
36	super.setUp();
37	quoter = SQLIdentifiers.createQuoter(conn.getMetaData());
38	}
39
40	public void testNoQuoting() {
41	String identifier = "YOUDONTNEEDTOQUOTEME2334252__1451";
42	String expResult = identifier;
43	String result = quoter.quoteIfNeeded(identifier);
44	assertEquals(expResult, result);
45	}
46
47	public void testSpaces() throws Exception {
48	String identifier = "YesYou Need to quote me";
49	String expResult = quote(identifier);
50
51	String result = quoter.quoteIfNeeded(identifier);
52
53	assertEquals(expResult, result);
54	}
55
56	public void testCasing() throws Exception {
57	String identifier;
58
59	// First, find out what kind of casing is done with unquoted
60	// identifiers for this connection
61	int caseRule = getUnquotedCaseRule();
62
63	switch (caseRule) {
64	case LC_RULE:
65	identifier = "ABCDEFG";
66	break;
67	case UC_RULE:
68	identifier = "abcdefg";
69	break;
70	default:
71	// Nothing to test here
72	return;
73	}
74
75	String expResult = quote(identifier);
76
77	String result = quoter.quoteIfNeeded(identifier);
78
79	assertEquals(expResult, result);
80	}
81
82	public void testNonAscii() throws Exception {
83	// borrowed translated message from Derby message file :)
84	String identifier = "abcdABCD0934" +
85	"\u4f8b\u5916\u306e\u305f\u3081\u3001\u59cb\u52d5" +
86	"\u306b\u5931\u6557\u3057\u307e\u3057\u305f\u3002 \u8a73\u7d30" +
87	"\u306b\u3064\u3044\u3066\u306f\u3001\u6b21\u306e\u4f8b\u5916" +
88	"\u3092\u53c2\u7167\u3057\u3066\u304f\u3060\u3055\u3044\u3002" +
89	"09298719871";
90
91	String expResult = quote(identifier);
92
93	String result = quoter.quoteIfNeeded(identifier);
94
95	assertEquals(expResult, result);
96	}
97
98	public void testDontQuoteQuoted() throws Exception {
99	String identifier = quote("I am already quoted");
100
101	String expResult = identifier;
102
103	String result = quoter.quoteIfNeeded(identifier);
104
105	assertEquals(expResult, result);
106	}
107
108	public void testNullIdentifier() throws Exception {
109	boolean gotException = false;
110	try {
111	quoter.quoteIfNeeded(null);
112	} catch ( NullPointerException npe ) {
113	// expected
114	gotException = true;
115	}
116
117	assertTrue(gotException);
118	}
119
120	}