Attachment #48678 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="12" month="9" year="2007"/>
            <author login="davidvc"/>
            <compatibility addition="yes"/>
            <description>
                This change adds a <code>SQLIdentifiers.Quoter</code> class which
                provides a utility method for quoting SQL identifiers.
            </description>
            <class package="org.netbeans.api.db.sql.support" name="SQLIdentifiers"/>
            <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 46-52 Link Here

(-)arch.xml (-1 / +14 lines)
46	-->	46	-->
47	<answer id="arch-overall">	47	<answer id="arch-overall">
48	<p>	48	<p>
49	The Database Explorer module provides the	49	The Database module provides the
50	<api group="java" name="DatabaseExplorerAPI" type="export" category="devel" url="@TOP@index.html"/>	50	<api group="java" name="DatabaseExplorerAPI" type="export" category="devel" url="@TOP@index.html"/>
51	which allows access to the database connections and drivers	51	which allows access to the database connections and drivers
52	defined in the Database Explorer. It allows a client to retrieve the	52	defined in the Database Explorer. It allows a client to retrieve the
Lines 77-82 Link Here
77	<code>Loaders/text/x-sql/Actions</code> folder. Note that this folder is actually provided by the	77	<code>Loaders/text/x-sql/Actions</code> folder. Note that this folder is actually provided by the
78	<code>org-netbeans-modules-db-core.jar</code> module.	78	<code>org-netbeans-modules-db-core.jar</code> module.
79	</p>	79	</p>
		80	<p>
		81	This module also provides a <api group="java" name="SQLSupportAPI" type="export"
		82	category="devel" url="@TOP@org/netbeans/api/db/sql/support/package-summary.html"/>
		83	which provides utilities for working with SQL such as quoting identifiers.
		84	</p>
80	</answer>	85	</answer>
81		86
82		87
Lines 295-300 Link Here
295	containing <code>DataFlavor</code>s for database objects and nested classes	300	containing <code>DataFlavor</code>s for database objects and nested classes
296	encapsulating those database objects during a drag and drop transfer.	301	encapsulating those database objects during a drag and drop transfer.
297	</p>	302	</p>
		303	</usecase>
		304	<usecase id="sql-identifier-support" name="Get support for working with SQL identifiers">
		305	<p>
		306	A component might need support for working with SQL identifiers. In particular,
		307	it's important to know when to quote a SQL identifier. The
		308	<a href="@TOP@org/netbeans/api/db/sql/support/SQLIdentifiers.Quoter.html">
		309	SQLIdentifiers.Quoter</a> class is provided for this.
		310	</p>
298	</usecase>	311	</usecase>
299	</answer>	312	</answer>
300		313

Lines 188-193 Link Here

(-)nbproject/project.xml (+1 lines)
188	<package>org.netbeans.api.db.explorer</package>	188	<package>org.netbeans.api.db.explorer</package>
189	<package>org.netbeans.api.db.explorer.support</package>	189	<package>org.netbeans.api.db.explorer.support</package>
190	<package>org.netbeans.spi.db.explorer</package>	190	<package>org.netbeans.spi.db.explorer</package>
		191	<package>org.netbeans.api.db.sql.support</package>
191	</public-packages>	192	</public-packages>
192	<class-path-extension>	193	<class-path-extension>
193	<runtime-relative-path>ext/ddl.jar</runtime-relative-path>	194	<runtime-relative-path>ext/ddl.jar</runtime-relative-path>




/*
 * 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.sql.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 {

    /** To prevent direct construction of this class... */
    private SQLIdentifiers() {
        
    }
    
    /**
     * 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) {
            assert identifier != 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), i == 0) ) {
                    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, boolean isFirstChar) {
            if ( isUpperCase(ch) || isLowerCase(ch) ) {
                return false;
            }
            
            if ( isNumber(ch) || ch == '_' ) {
                // If this the first character in the identifier, need to quote
                // '_' and numbers.  Maybe not always true, but we're being
                // conservative here
                return isFirstChar;
            }
                        
            // Check if it's in the list of extra characters for this db
            return extraNameChars.indexOf(ch) == -1; 
        }
        
        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 static 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 static 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 static 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.sql.support;

import org.netbeans.api.db.sql.support.SQLIdentifiers;
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 {
        try {
            quoter.quoteIfNeeded(null);
            fail("Expected a NullPointerException");
        } catch ( NullPointerException npe ) {
            // expected
        }
    }
    
    public void testFirstCharIsUnderbar() throws Exception {
        String identifier = "_NO_UNDERBAR_AS_FIRST_CHAR";
        
        String expResult = quote(identifier);

        String result = quoter.quoteIfNeeded(identifier);
        
        assertEquals(expResult, result);
    }

    public void testFirstCharIsNumber() throws Exception {
        String identifier = "1NO_NUMBER123_AS_FIRST_CHAR";
        
        String expResult = quote(identifier);

        String result = quoter.quoteIfNeeded(identifier);
        
        assertEquals(expResult, result);
    }
}




    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 (+14 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="12" month="9" year="2007"/>
		90	<author login="davidvc"/>
		91	<compatibility addition="yes"/>
		92	<description>
		93	This change adds a <code>SQLIdentifiers.Quoter</code> class which
		94	provides a utility method for quoting SQL identifiers.
		95	</description>
		96	<class package="org.netbeans.api.db.sql.support" name="SQLIdentifiers"/>
		97	<issue number="115075"/>
		98	</change>
		99	<change>
		100	<api name="database_explorer_api"/>
87	<summary>Added support for dragging and dropping nodes from the Database Explorer</summary>	101	<summary>Added support for dragging and dropping nodes from the Database Explorer</summary>
88	<version major="1" minor="21"/>	102	<version major="1" minor="21"/>
89	<date day="17" month="6" year="2007"/>	103	<date day="17" month="6" year="2007"/>

Added Link Here

(-)src/org/netbeans/api/db/sql/support/SQLIdentifiers.java (+242 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.sql.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
30	/** To prevent direct construction of this class... */
31	private SQLIdentifiers() {
32
33	}
34
35	/**
36	* Construct an instance of SQLIdentifier.
37	*
38	* @param dbmd The DatabaseMetaData to use when working with identifiers.
39	* The metadata object is used to determine when an identifier needs
40	* to be quoted and what the quote string should be.
41	*/
42	public static Quoter createQuoter(DatabaseMetaData dbmd) {
43	return new Quoter(dbmd);
44	}
45
46
47	/**
48	* This is a utility class that is used to quote identifiers.
49	*
50	* This class is immutable and thus thread-safe
51	*/
52	public static class Quoter {
53	private static final Logger LOGGER =
54	Logger.getLogger(Quoter.class.getName());
55
56	// Rules for what happens to the casing of a character in an identifier
57	// when it is not quoted
58	private static final int LC_RULE = 0; // everything goes to lower case
59	private static final int UC_RULE = 1; // everything goes to upper case
60	private static final int MC_RULE = 2; // mixed case remains mixed case
61
62	private final String extraNameChars;
63	private final String quoteString;
64	private final int caseRule;
65
66	private Quoter(DatabaseMetaData dbmd) {
67	extraNameChars = getExtraNameChars(dbmd);
68	quoteString = getQuoteString(dbmd);
69	caseRule = getCaseRule(dbmd);
70	}
71
72	/**
73	* Quote an <b>existing</b> identifier to be used in a SQL command,
74	* if needed.
75	* <p>
76	* Anyone generating SQL that will be
77	* visible and/or editable by the user should use this method.
78	* This helps to avoid unecessary quoting, which affects the
79	* readability and clarity of the resulting SQL.
80	* <p>
81	* An identifier needs to be quoted if one of the following is true:
82	* <ul>
83	* <li>any character in the
84	* string is not within the set of characters that do
85	* not need to be quoted in a SQL identifier.
86	*
87	* <li>any character in the string is not of the
88	* expected casing (e.g. lower case when the database upper-cases
89	* all non-quoted identifiers).
90	* </ul>
91	*
92	* @param identifier a SQL identifier. Can not be null.
93	*
94	* @return the identifier, quoted if needed
95	*/
96	public final String quoteIfNeeded(String identifier) {
97	Parameters.notNull("identifier", identifier);
98
99	if ( needToQuote(identifier) ) {
100	return quoteString + identifier + quoteString;
101	}
102
103	return identifier;
104	}
105
106	/**
107	* Determine if we need to quote this identifier
108	*/
109	private boolean needToQuote(String identifier) {
110	assert identifier != null;
111
112	// No need to quote if it's already quoted
113	if ( identifier.startsWith(quoteString) &&
114	identifier.endsWith(quoteString)) {
115	return false;
116	}
117
118
119	int length = identifier.length();
120	for ( int i = 0 ; i < length ; i++ ) {
121	if ( charNeedsQuoting(identifier.charAt(i), i == 0) ) {
122	return true;
123	}
124	}
125
126	// Next, check to see if any characters are in the wrong casing
127	// (for example, if the db upper cases all non-quoted identifiers,
128	// and we have a lower-case character, then we need to quote
129	if ( caseRule == UC_RULE && containsLowerCase(identifier)) {
130	return true;
131	} else if ( caseRule == LC_RULE && containsUpperCase(identifier)) {
132	return true;
133	}
134
135	return false;
136	}
137
138	private boolean charNeedsQuoting(char ch, boolean isFirstChar) {
139	if ( isUpperCase(ch) \|\| isLowerCase(ch) ) {
140	return false;
141	}
142
143	if ( isNumber(ch) \|\| ch == '_' ) {
144	// If this the first character in the identifier, need to quote
145	// '_' and numbers. Maybe not always true, but we're being
146	// conservative here
147	return isFirstChar;
148	}
149
150	// Check if it's in the list of extra characters for this db
151	return extraNameChars.indexOf(ch) == -1;
152	}
153
154	private static boolean isUpperCase(char ch) {
155	return ch >= 'A' && ch <= 'Z';
156	}
157
158	private static boolean isLowerCase(char ch) {
159	return ch >= 'a' && ch <= 'z';
160	}
161
162	private static boolean isNumber(char ch) {
163	return ch >= '0' && ch <= '9';
164	}
165
166	private static boolean containsLowerCase(String identifier) {
167	int length = identifier.length();
168	for ( int i = 0 ; i < length ; i++ ) {
169	if ( isLowerCase(identifier.charAt(i)) ) {
170	return true;
171	}
172	}
173
174	return false;
175	}
176
177	private static boolean containsUpperCase(String identifier) {
178
179	int length = identifier.length();
180	for ( int i = 0 ; i < length ; i++ ) {
181	if ( isUpperCase(identifier.charAt(i)) ) {
182	return true;
183	}
184	}
185
186	return false;
187	}
188
189	private static String getExtraNameChars(DatabaseMetaData dbmd) {
190	String chars = "";
191	try {
192	chars = dbmd.getExtraNameCharacters();
193	} catch ( SQLException e ) {
194	LOGGER.log(Level.WARNING, "DatabaseMetaData.getExtraNameCharacters()"
195	+ " failed (" + e.getMessage() + "). " +
196	"Using standard set of characters");
197	LOGGER.log(Level.FINE, null, e);
198	}
199
200	return chars;
201	}
202
203	private static String getQuoteString(DatabaseMetaData dbmd) {
204	String quoteStr = "\"";
205
206	try {
207	quoteStr = dbmd.getIdentifierQuoteString().trim();
208	} catch ( SQLException e ) {
209	LOGGER.log(Level.WARNING, "DatabaseMetaData.getIdentifierQuoteString()"
210	+ " failed (" + e.getMessage() + "). " +
211	"Using '\"' for quoting SQL identifiers");
212	LOGGER.log(Level.FINE, null, e);
213	}
214
215	return quoteStr;
216	}
217
218	private static int getCaseRule(DatabaseMetaData dbmd) {
219	int rule = UC_RULE;
220
221	try {
222	if ( dbmd.storesUpperCaseIdentifiers() ) {
223	rule = UC_RULE;
224	} else if ( dbmd.storesLowerCaseIdentifiers() ) {
225	rule = LC_RULE;
226	} else if ( dbmd.storesMixedCaseIdentifiers() ) {
227	rule = MC_RULE;
228	} else {
229	rule = UC_RULE;
230	}
231	} catch ( SQLException sqle ) {
232	LOGGER.log(Level.WARNING, "Exception trying to find out how " +
233	"the database stores unquoted identifiers, assuming " +
234	"upper case: " + sqle.getMessage());
235	LOGGER.log(Level.FINE, null, sqle);
236	}
237
238	return rule;
239	}
240	}
241
242	}

Added Link Here

(-)test/unit/src/org/netbeans/api/db/sql/support/QuoterTest.java (+137 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.sql.support;
18
19	import org.netbeans.api.db.sql.support.SQLIdentifiers;
20	import org.netbeans.modules.db.util.DBTestBase;
21
22	/**
23	* @author <a href="mailto:david@vancouvering.com">David Van Couvering</a>
24	*
25	* This class is a set of tests to make sure we're quoting identifiers
26	* correctly
27	*/
28	public class QuoterTest extends DBTestBase {
29
30	private SQLIdentifiers.Quoter quoter;
31
32	public QuoterTest(String testName) {
33	super(testName);
34	}
35
36	public void setUp() throws Exception {
37	super.setUp();
38	quoter = SQLIdentifiers.createQuoter(conn.getMetaData());
39	}
40
41	public void testNoQuoting() {
42	String identifier = "YOUDONTNEEDTOQUOTEME2334252__1451";
43	String expResult = identifier;
44	String result = quoter.quoteIfNeeded(identifier);
45	assertEquals(expResult, result);
46	}
47
48	public void testSpaces() throws Exception {
49	String identifier = "YesYou Need to quote me";
50	String expResult = quote(identifier);
51
52	String result = quoter.quoteIfNeeded(identifier);
53
54	assertEquals(expResult, result);
55	}
56
57	public void testCasing() throws Exception {
58	String identifier;
59
60	// First, find out what kind of casing is done with unquoted
61	// identifiers for this connection
62	int caseRule = getUnquotedCaseRule();
63
64	switch (caseRule) {
65	case LC_RULE:
66	identifier = "ABCDEFG";
67	break;
68	case UC_RULE:
69	identifier = "abcdefg";
70	break;
71	default:
72	// Nothing to test here
73	return;
74	}
75
76	String expResult = quote(identifier);
77
78	String result = quoter.quoteIfNeeded(identifier);
79
80	assertEquals(expResult, result);
81	}
82
83	public void testNonAscii() throws Exception {
84	// borrowed translated message from Derby message file :)
85	String identifier = "abcdABCD0934" +
86	"\u4f8b\u5916\u306e\u305f\u3081\u3001\u59cb\u52d5" +
87	"\u306b\u5931\u6557\u3057\u307e\u3057\u305f\u3002 \u8a73\u7d30" +
88	"\u306b\u3064\u3044\u3066\u306f\u3001\u6b21\u306e\u4f8b\u5916" +
89	"\u3092\u53c2\u7167\u3057\u3066\u304f\u3060\u3055\u3044\u3002" +
90	"09298719871";
91
92	String expResult = quote(identifier);
93
94	String result = quoter.quoteIfNeeded(identifier);
95
96	assertEquals(expResult, result);
97	}
98
99	public void testDontQuoteQuoted() throws Exception {
100	String identifier = quote("I am already quoted");
101
102	String expResult = identifier;
103
104	String result = quoter.quoteIfNeeded(identifier);
105
106	assertEquals(expResult, result);
107	}
108
109	public void testNullIdentifier() throws Exception {
110	try {
111	quoter.quoteIfNeeded(null);
112	fail("Expected a NullPointerException");
113	} catch ( NullPointerException npe ) {
114	// expected
115	}
116	}
117
118	public void testFirstCharIsUnderbar() throws Exception {
119	String identifier = "_NO_UNDERBAR_AS_FIRST_CHAR";
120
121	String expResult = quote(identifier);
122
123	String result = quoter.quoteIfNeeded(identifier);
124
125	assertEquals(expResult, result);
126	}
127
128	public void testFirstCharIsNumber() throws Exception {
129	String identifier = "1NO_NUMBER123_AS_FIRST_CHAR";
130
131	String expResult = quote(identifier);
132
133	String result = quoter.quoteIfNeeded(identifier);
134
135	assertEquals(expResult, result);
136	}
137	}