Return-Path: Delivered-To: apmail-db-derby-commits-archive@www.apache.org Received: (qmail 74959 invoked from network); 13 Dec 2005 18:24:51 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199) by minotaur.apache.org with SMTP; 13 Dec 2005 18:24:51 -0000 Received: (qmail 21519 invoked by uid 500); 13 Dec 2005 18:24:50 -0000 Delivered-To: apmail-db-derby-commits-archive@db.apache.org Received: (qmail 21265 invoked by uid 500); 13 Dec 2005 18:24:48 -0000 Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: Reply-To: "Derby Development" List-Id: Delivered-To: mailing list derby-commits@db.apache.org Received: (qmail 21098 invoked by uid 99); 13 Dec 2005 18:24:46 -0000 Received: from asf.osuosl.org (HELO asf.osuosl.org) (140.211.166.49) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 13 Dec 2005 10:24:46 -0800 X-ASF-Spam-Status: No, hits=-9.4 required=10.0 tests=ALL_TRUSTED,NO_REAL_NAME,UPPERCASE_25_50 X-Spam-Check-By: apache.org Received: from [209.237.227.194] (HELO minotaur.apache.org) (209.237.227.194) by apache.org (qpsmtpd/0.29) with SMTP; Tue, 13 Dec 2005 10:24:41 -0800 Received: (qmail 74554 invoked by uid 65534); 13 Dec 2005 18:24:20 -0000 Message-ID: <20051213182420.74553.qmail@minotaur.apache.org> Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Subject: svn commit: r356562 [1/6] - in /db/derby/code/trunk/java: engine/org/apache/derby/iapi/reference/ engine/org/apache/derby/iapi/sql/ engine/org/apache/derby/iapi/sql/conn/ engine/org/apache/derby/iapi/sql/dictionary/ engine/org/apache/derby/impl/jdbc/ e... Date: Tue, 13 Dec 2005 18:24:11 -0000 To: derby-commits@db.apache.org From: bandaram@apache.org X-Mailer: svnmailer-1.0.5 X-Virus-Checked: Checked by ClamAV on apache.org X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N Author: bandaram Date: Tue Dec 13 10:23:42 2005 New Revision: 356562 URL: http://svn.apache.org/viewcvs?rev=356562&view=rev Log: DERBY-572: Add support for optimizer hints, provided by the user as SQL comments. Also add upgrade infrastructure for 10.2 release and support new optimizer mechanism to work correctly under soft-upgrade for databases at 10.1 level. I have the patch for optimizer overrides support in Derby. Alongwith the patch, I have attached the updated functional spec to the JIRA entry Derby-573. Majority of the changes went into the sqlgrammar.jj because Derby engine already has support for them internally. It is the parser that needs to recognize these overrides and pass it on to through the query nodes. The parser now looks for character sequence -- DERBY-PROPERTIES (case insensitive and space between -- and D is optional) and once it finds that, it looks for propertyName=value pairs on that same comment line in parser's propertyList method. The parser does the basic check to make sure that the same property is not used more than once for a given table. The remaining checks on the properties like checking the existence of user specified index etc are done in the bind phase. I also changed the metadata.properties file to use --DERBY-PROPERTIES rather than old PROPERTIES clause to supply optimizer overrides. In addition, added \n at the end of the optimier override comment lines to make sure the comment line does not get concatenated with the next line of the sql. Import.java had to be changed to user --DERBY-PROPERTIES rather than PROPERTIES. Added a new test optimizerOverrides.sql which runs in both embedded and network server mode. Rerunning all the tests after syncing the codeline to make sure nothing has broken. An earlier run of the tests before the sync came out clean. I plan to next work on exposing these overrides through runtime statistics so that user can verify that the optimizer overrides are getting used. Submitted by Mamta Satoor (msatoor@gmail.com) Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/optimizerOverrides.out (with props) db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/optimizerOverrides.out (with props) db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/optimizerOverrides.out (with props) db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/phaseTester.out (with props) db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/optimizerOverrides.sql (with props) Removed: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/metadataJdbc20.out db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/metadataJdbc20.out db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/j9_foundation/metadataJdbc20.out db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/metadataJdbc20.out db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/metadataJdbc20.java Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/reference/SQLState.java db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/Statement.java db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/conn/LanguageConnectionContext.java db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc/EmbedDatabaseMetaData.java db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc/EmbedPreparedStatement.java db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc/EmbedStatement.java db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc/metadata.properties db/derby/code/trunk/java/engine/org/apache/derby/impl/load/Import.java db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/GenericStatement.java db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DD_Version.java db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/conn/GenericLanguageConnectionContext.java db/derby/code/trunk/java/engine/org/apache/derby/loc/messages_en.properties db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/metadata.out db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/odbc_metadata.out db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/metadata.out db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/odbc_metadata.out db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/access.out db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/checkConstraint.out db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/db2Compatibility.out db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/metadata.out db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/odbc_metadata.out db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbynetmats.runall db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/jdbc20.runall db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/metadata.java db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/metadata_test.java db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/checkConstraint.sql db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/copyfiles.ant db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/db2Compatibility.sql db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/store/access.sql db/derby/code/trunk/java/testing/org/apache/derbyTesting/upgradeTests/phaseTester.java db/derby/code/trunk/java/testing/org/apache/derbyTesting/upgradeTests/runphases.ksh Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/reference/SQLState.java URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/engine/org/apache/derby/iapi/reference/SQLState.java?rev=356562&r1=356561&r2=356562&view=diff ============================================================================== --- db/derby/code/trunk/java/engine/org/apache/derby/iapi/reference/SQLState.java (original) +++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/reference/SQLState.java Tue Dec 13 10:23:42 2005 @@ -233,6 +233,7 @@ String PROPERTY_INVALID_VALUE = "XCY00.S"; String PROPERTY_UNSUPPORTED_CHANGE = "XCY02.S"; String PROPERTY_MISSING = "XCY03.S"; + String PROPERTY_SYNTAX_INVALID = "XCY04.S"; /* ** LockManager Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/Statement.java URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/Statement.java?rev=356562&r1=356561&r2=356562&view=diff ============================================================================== --- db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/Statement.java (original) +++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/Statement.java Tue Dec 13 10:23:42 2005 @@ -55,7 +55,22 @@ * relies on compilation). */ PreparedStatement prepare(LanguageConnectionContext lcc) throws StandardException; - + /** + * Generates an execution plan without executing it. + * + * @param lcc the language connection context + * @param allowInternalSyntax If this statement is for a metadata call then + * we will allow internal sql syntax on such statement. This internal + * sql syntax is not available to a user sql statement. + * + * @return A PreparedStatement that allows execution of the execution + * plan. + * @exception StandardException Thrown if this is an + * execution-only version of the module (the prepare() method + * relies on compilation). + */ + PreparedStatement prepare(LanguageConnectionContext lcc, boolean allowInternalSyntax) throws StandardException; + /** * Generates an execution plan given a set of named parameters. * For generating a storable prepared statement (which Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/conn/LanguageConnectionContext.java URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/conn/LanguageConnectionContext.java?rev=356562&r1=356561&r2=356562&view=diff ============================================================================== --- db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/conn/LanguageConnectionContext.java (original) +++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/conn/LanguageConnectionContext.java Tue Dec 13 10:23:42 2005 @@ -863,7 +863,7 @@ /** Get the AccessFactory cached in this LanguageConnectionContext */ AccessFactory getAccessFactory(); - /** + /** * Return a PreparedStatement object for the query. * This method first tries to locate the PreparedStatement object from a statement * cache. If the statement is not found in the cache, the query will be compiled and @@ -872,8 +872,12 @@ * @param sqlText sql query string * @param isForReadOnly read only status for resultset. Set to true if the concurrency mode for the resultset * is CONCUR_READ_ONLY + * @param allowInternalSyntax If true, then this query is allowed to use internal + * sql syntax. One instance where this will be true is if a + * metadata query is getting executed. */ - public PreparedStatement prepareInternalStatement(SchemaDescriptor compilationSchema, String sqlText, boolean isForReadOnly) + public PreparedStatement prepareInternalStatement(SchemaDescriptor compilationSchema, + String sqlText, boolean isForReadOnly, boolean allowInternalSyntax) throws StandardException; /** Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java?rev=356562&r1=356561&r2=356562&view=diff ============================================================================== --- db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java (original) +++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java Tue Dec 13 10:23:42 2005 @@ -63,7 +63,28 @@ { String MODULE = "org.apache.derby.iapi.sql.dictionary.DataDictionary"; - /** Special version indicating the database must be upgraded to or created at the current engine level */ + /** Special version indicating the database must be upgraded to or created at the current engine level + * + * DatabaseMetaData will use this to determine if the data dictionary + * is at the latest System Catalog version number. A data dictionary version + * will not be at latest System Catalog version when the database is getting + * booted in soft upgrade mode. In soft upgrade mode, engine should goto + * metadata.properties to get the sql for the metadata calls rather + * than going to the system tables (and using stored versions of these queries). + * This is required because if the metadata sql has changed between the + * releases, we want to use the latest metadata sql rather than what is + * stored in the system catalogs. Had to introduce this behavior for + * EmbeddedDatabaseMetaData in 10.2 release where optimizer overrides + * syntax was changed. If 10.2 engine in soft upgrade mode for a pre-10.2 + * database went to system tables for stored metadata queires, the metadata + * calls would fail because 10.2 release doesn't recognize the pre-10.2 + * optimizer overrides syntax. To get around this, the 10.2 engine in + * soft upgrade mode should get the sql from metata.properties which has + * been changed to 10.2 syntax for optimizer overrides. To make this + * approach more generic for all soft upgrades, from 10.2 release onwards, + * DatabaseMetaData calls will always look at metadata.properties so it + * will get the compatible syntax for that release. + */ public static final int DD_VERSION_CURRENT = -1; /** Cloudscape 5.0 System Catalog version */ public static final int DD_VERSION_CS_5_0 = 80; @@ -78,6 +99,9 @@ /** Derby 10.1 System Catalog version */ public static final int DD_VERSION_DERBY_10_1 = 130; + + /** Derby 10.2 System Catalog version */ + public static final int DD_VERSION_DERBY_10_2 = 140; // general info public static final String DATABASE_ID = "derby.databaseID"; Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc/EmbedDatabaseMetaData.java URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc/EmbedDatabaseMetaData.java?rev=356562&r1=356561&r2=356562&view=diff ============================================================================== --- db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc/EmbedDatabaseMetaData.java (original) +++ db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc/EmbedDatabaseMetaData.java Tue Dec 13 10:23:42 2005 @@ -2109,6 +2109,35 @@ } /** + * check if the dictionary is at the same version as the engine. If not, + * then that means stored versions of the JDBC database metadata queries + * may not be compatible with this version of the software. + * This can happen if we are in soft upgrade mode. Since in soft upgrade + * mode, we can't change these stored metadata queries in a backward + * incompatible way, engine needs to read the metadata sql from + * metadata.properties file rather than rely on system tables. + * + * @return true if we are not in soft upgrade mode + * @throws SQLException + */ + private boolean notInSoftUpgradeMode() + throws SQLException { + if ( getEmbedConnection().isClosed()) + throw Util.noCurrentConnection(); + + boolean notInSoftUpgradeMode; + try { + notInSoftUpgradeMode = + getLanguageConnectionContext().getDataDictionary().checkVersion( + DataDictionary.DD_VERSION_CURRENT,null); + } catch (Throwable t) { + throw handleException(t); + } + return notInSoftUpgradeMode; + } + + + /** * Get a description of a table's primary key columns. They * are ordered by COLUMN_NAME. * @@ -2131,7 +2160,7 @@ * @exception SQLException thrown on failure. */ public ResultSet getPrimaryKeys(String catalog, String schema, - String table) throws SQLException { + String table) throws SQLException { return doGetPrimaryKeys(catalog, schema, table, "getPrimaryKeys"); } @@ -2161,9 +2190,9 @@ s.setString(2, swapNull(schema)); s.setString(3, swapNull(table)); return s.executeQuery(); - } + } - /** + /** * Get a description of the primary key columns that are * referenced by a table's foreign key columns (the primary keys * imported by a table). They are ordered by PKTABLE_CAT, @@ -3083,7 +3112,7 @@ return ps.executeQuery(); } - private PreparedStatement getPreparedQuery(String nameKey) throws SQLException + private PreparedStatement getPreparedQueryUsingSystemTables(String nameKey) throws SQLException { synchronized (getConnectionSynchronization()) { @@ -3114,6 +3143,40 @@ return ps; } } + + /** + * Either get the prepared query for the metadata call from the + * system tables or from the metadata.properties file. + * In soft upgrade mode, the queries stored in the system tables + * might not be upto date with the Derby engine release because + * system tables can't be modified in backward incompatible way in + * soft upgrade mode. Because of this, if the database is in + * soft upgrade mode, get the queries from metadata.properties + * file rather than from the system tables. + * @param queryName Name of the metadata query for which we need + * a prepared statement + * @return PreparedStatement + */ + private PreparedStatement getPreparedQuery(String queryName) + throws SQLException { + PreparedStatement s; + //We can safely goto system table since we are not in soft upgrade + //mode and hence metadata sql in system tables are uptodate + //with this Derby release. + if (notInSoftUpgradeMode()) + s = getPreparedQueryUsingSystemTables(queryName); + else { + try { + //Can't use stored prepared statements because we are in soft upgrade + //mode and hence need to get metadata sql from metadata.properties file + String queryText = getQueryDescriptions().getProperty(queryName); + s = getEmbedConnection().prepareMetaDataStatement(queryText); + } catch (Throwable t) { + throw handleException(t); + } + } + return s; + } /* ** Given a SPS name and a query text it returns a Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc/EmbedPreparedStatement.java URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc/EmbedPreparedStatement.java?rev=356562&r1=356561&r2=356562&view=diff ============================================================================== --- db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc/EmbedPreparedStatement.java (original) +++ db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc/EmbedPreparedStatement.java Tue Dec 13 10:23:42 2005 @@ -116,7 +116,7 @@ try { preparedStatement = lcc.prepareInternalStatement - (lcc.getDefaultSchema(), sql, resultSetConcurrency==JDBC20Translation.CONCUR_READ_ONLY); + (lcc.getDefaultSchema(), sql, resultSetConcurrency==JDBC20Translation.CONCUR_READ_ONLY, forMetaData); getWarnings(preparedStatement.getCompileTimeWarnings()); Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc/EmbedStatement.java URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc/EmbedStatement.java?rev=356562&r1=356561&r2=356562&view=diff ============================================================================== --- db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc/EmbedStatement.java (original) +++ db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc/EmbedStatement.java Tue Dec 13 10:23:42 2005 @@ -531,7 +531,7 @@ Activation activation; try { PreparedStatement preparedStatement = lcc.prepareInternalStatement - (lcc.getDefaultSchema(), sql, resultSetConcurrency==JDBC20Translation.CONCUR_READ_ONLY); + (lcc.getDefaultSchema(), sql, resultSetConcurrency==JDBC20Translation.CONCUR_READ_ONLY, false); activation = preparedStatement.getActivation(lcc, resultSetType == JDBC20Translation.TYPE_SCROLL_INSENSITIVE); checkRequiresCallableStatement(activation); Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc/metadata.properties URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc/metadata.properties?rev=356562&r1=356561&r2=356562&view=diff ============================================================================== --- db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc/metadata.properties (original) +++ db/derby/code/trunk/java/engine/org/apache/derby/impl/jdbc/metadata.properties Tue Dec 13 10:23:42 2005 @@ -314,13 +314,13 @@ COLS.COLUMNNAME AS COLUMN_NAME, \ CONGLOMS.DESCRIPTOR.getKeyColumnPosition(COLS.COLUMNNUMBER) AS KEY_SEQ, \ CONS.CONSTRAINTNAME AS PK_NAME \ - FROM PROPERTIES joinOrder=FIXED \ - SYS.SYSTABLES T PROPERTIES index='SYSTABLES_INDEX1', \ - SYS.SYSSCHEMAS S PROPERTIES joinStrategy=NESTEDLOOP, index ='SYSSCHEMAS_INDEX1', \ - SYS.SYSCONSTRAINTS CONS PROPERTIES joinStrategy=NESTEDLOOP, index ='SYSCONSTRAINTS_INDEX3', \ - SYS.SYSKEYS KEYS PROPERTIES joinStrategy=NESTEDLOOP, index ='SYSKEYS_INDEX1', \ - SYS.SYSCONGLOMERATES CONGLOMS PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSCONGLOMERATES_INDEX1', \ - SYS.SYSCOLUMNS COLS PROPERTIES joinStrategy=NESTEDLOOP, index ='SYSCOLUMNS_INDEX1' \ + FROM --DERBY-PROPERTIES joinOrder=FIXED \n \ + SYS.SYSTABLES T --DERBY-PROPERTIES index='SYSTABLES_INDEX1' \n\ + , SYS.SYSSCHEMAS S --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index ='SYSSCHEMAS_INDEX1' \n\ + , SYS.SYSCONSTRAINTS CONS --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index ='SYSCONSTRAINTS_INDEX3' \n\ + , SYS.SYSKEYS KEYS --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index ='SYSKEYS_INDEX1' \n\ + , SYS.SYSCONGLOMERATES CONGLOMS --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSCONGLOMERATES_INDEX1' \n\ + , SYS.SYSCOLUMNS COLS --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index ='SYSCOLUMNS_INDEX1' \n\ WHERE ((1=1) OR ? IS NOT NULL) AND S.SCHEMANAME LIKE ? AND T.TABLENAME LIKE ? AND \ T.SCHEMAID = S.SCHEMAID AND \ T.TABLEID = COLS.REFERENCEID AND T.TABLEID = CONGLOMS.TABLEID AND \ @@ -381,7 +381,7 @@ PK_NAME, \ CAST (java.sql.DatabaseMetaData::importedKeyNotDeferrable \ AS SMALLINT) AS DEFERRABILITY \ - FROM PROPERTIES joinOrder=FIXED \ + FROM --DERBY-PROPERTIES joinOrder=FIXED \n\ (SELECT C.CONSTRAINTID AS PK_ID, \ CONSTRAINTNAME AS PK_NAME, \ PKTB_SCHEMA AS PKTABLE_SCHEM, \ @@ -389,23 +389,23 @@ COLS.COLUMNNAME AS PKCOLUMN_NAME, \ CONGLOMS.DESCRIPTOR.getKeyColumnPosition( \ COLS.COLUMNNUMBER) AS KEY_SEQ \ - FROM PROPERTIES joinOrder=FIXED \ + FROM --DERBY-PROPERTIES joinOrder=FIXED \n\ (SELECT T.TABLEID AS PKTB_ID, \ S.SCHEMANAME AS PKTB_SCHEMA, \ T.TABLENAME AS PKTB_NAME \ FROM \ - SYS.SYSTABLES t PROPERTIES index = 'SYSTABLES_INDEX1', \ - SYS.SYSSCHEMAS s PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSSCHEMAS_INDEX1' \ + SYS.SYSTABLES t --DERBY-PROPERTIES index = 'SYSTABLES_INDEX1' \n\ + , SYS.SYSSCHEMAS s --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSSCHEMAS_INDEX1' \n\ WHERE \ ((1=1) OR ? IS NOT NULL) \ AND S.SCHEMANAME LIKE ? \ AND T.TABLENAME LIKE ? \ AND S.SCHEMAID = T.SCHEMAID \ ) AS PKTB (PKTB_ID, PKTB_SCHEMA, PKTB_NAME), \ - SYS.SYSCONSTRAINTS C PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSCONSTRAINTS_INDEX3', \ - SYS.SYSKEYS K PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSKEYS_INDEX1', \ - SYS.SYSCONGLOMERATES CONGLOMS PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSCONGLOMERATES_INDEX1', \ - SYS.SYSCOLUMNS COLS PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSCOLUMNS_INDEX1' \ + SYS.SYSCONSTRAINTS C --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSCONSTRAINTS_INDEX3' \n\ + , SYS.SYSKEYS K --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSKEYS_INDEX1' \n\ + , SYS.SYSCONGLOMERATES CONGLOMS --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSCONGLOMERATES_INDEX1' \n\ + , SYS.SYSCOLUMNS COLS --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSCOLUMNS_INDEX1' \n\ WHERE \ PKTB.PKTB_ID = C.TABLEID \ AND K.CONSTRAINTID = C.CONSTRAINTID \ @@ -421,12 +421,12 @@ PKTABLE_NAME, \ PKCOLUMN_NAME, \ KEY_SEQ), \ - SYS.SYSFOREIGNKEYS F2 PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSFOREIGNKEYS_INDEX1', \ - SYS.SYSCONSTRAINTS c2 PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSCONSTRAINTS_INDEX1', \ - SYS.SYSTABLES T2 PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSTABLES_INDEX2', \ - SYS.SYSSCHEMAS S2 PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSSCHEMAS_INDEX2', \ - SYS.SYSCONGLOMERATES CONGLOMS2 PROPERTIES joinStrategy = NESTEDLOOP, index = 'SYSCONGLOMERATES_INDEX1', \ - SYS.SYSCOLUMNS COLS2 PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSCOLUMNS_INDEX1' \ + SYS.SYSFOREIGNKEYS F2 --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSFOREIGNKEYS_INDEX1' \n\ + , SYS.SYSCONSTRAINTS c2 --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSCONSTRAINTS_INDEX1' \n\ + , SYS.SYSTABLES T2 --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSTABLES_INDEX2' \n\ + , SYS.SYSSCHEMAS S2 --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSSCHEMAS_INDEX2' \n\ + , SYS.SYSCONGLOMERATES CONGLOMS2 --DERBY-PROPERTIES joinStrategy = NESTEDLOOP, index = 'SYSCONGLOMERATES_INDEX1' \n\ + , SYS.SYSCOLUMNS COLS2 --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSCOLUMNS_INDEX1' \n\ WHERE F2.keyCONSTRAINTID = PKINFO.PK_ID \ AND PKINFO.KEY_SEQ = CONGLOMS2.DESCRIPTOR.getKeyColumnPosition( \ COLS2.COLUMNNUMBER) \ @@ -484,7 +484,7 @@ CONSTRAINTNAME AS PK_NAME, \ CAST (java.sql.DatabaseMetaData::importedKeyNotDeferrable \ AS SMALLINT) AS DEFERRABILITY \ - FROM PROPERTIES joinOrder=FIXED \ + FROM --DERBY-PROPERTIES joinOrder=FIXED \n\ (SELECT F2.keyCONSTRAINTID AS FK_ID, \ FKTB_SCHEMA AS FKTABLE_SCHEM, \ FKTB_NAME AS FKTABLE_NAME, \ @@ -494,23 +494,23 @@ C2.CONSTRAINTNAME AS FK_NAME, \ F2.DELETERULE AS FK_UPDATERULE, \ F2.DELETERULE AS FK_DELETERULE \ - FROM PROPERTIES joinOrder=FIXED \ + FROM --DERBY-PROPERTIES joinOrder=FIXED \n\ (SELECT T2.TABLEID AS FKTB_ID, \ S2.SCHEMANAME AS FKTB_SCHEMA, \ T2.TABLENAME AS FKTB_NAME \ FROM \ - SYS.SYSTABLES T2 PROPERTIES index = 'SYSTABLES_INDEX1', \ - SYS.SYSSCHEMAS S2 PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSSCHEMAS_INDEX1' \ + SYS.SYSTABLES T2 --DERBY-PROPERTIES index = 'SYSTABLES_INDEX1' \n\ + , SYS.SYSSCHEMAS S2 --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSSCHEMAS_INDEX1' \n\ WHERE \ ((1=1) OR ? IS NOT NULL) \ AND S2.SCHEMANAME LIKE ? \ AND T2.TABLENAME LIKE ? \ AND S2.SCHEMAID = T2.SCHEMAID \ ) AS FKTB (FKTB_ID, FKTB_SCHEMA, FKTB_NAME), \ - SYS.SYSCONSTRAINTS c2 PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSCONSTRAINTS_INDEX3', \ - SYS.SYSFOREIGNKEYS F2 PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSFOREIGNKEYS_INDEX1', \ - SYS.SYSCONGLOMERATES CONGLOMS2 PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSCONGLOMERATES_INDEX1', \ - SYS.SYSCOLUMNS COLS2 PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSCOLUMNS_INDEX1' \ + SYS.SYSCONSTRAINTS c2 --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSCONSTRAINTS_INDEX3' \n\ + , SYS.SYSFOREIGNKEYS F2 --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSFOREIGNKEYS_INDEX1' \n\ + , SYS.SYSCONGLOMERATES CONGLOMS2 --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSCONGLOMERATES_INDEX1' \n\ + , SYS.SYSCOLUMNS COLS2 --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSCOLUMNS_INDEX1' \n\ WHERE \ FKTB.FKTB_ID = C2.TABLEID \ AND F2.CONSTRAINTID = C2.CONSTRAINTID \ @@ -528,12 +528,12 @@ FK_NAME, \ FK_UPDATERULE, \ FK_DELETERULE), \ - SYS.SYSCONSTRAINTS c PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSCONSTRAINTS_INDEX1', \ - SYS.SYSTABLES T PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSTABLES_INDEX2', \ - SYS.SYSSCHEMAS S PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSSCHEMAS_INDEX2', \ - SYS.SYSKEYS K PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSKEYS_INDEX1', \ - SYS.SYSCONGLOMERATES CONGLOMS PROPERTIES joinStrategy = NESTEDLOOP, index = 'SYSCONGLOMERATES_INDEX1', \ - SYS.SYSCOLUMNS COLS PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSCOLUMNS_INDEX1' \ + SYS.SYSCONSTRAINTS c --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSCONSTRAINTS_INDEX1' \n\ + , SYS.SYSTABLES T --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSTABLES_INDEX2' \n\ + , SYS.SYSSCHEMAS S --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSSCHEMAS_INDEX2' \n\ + , SYS.SYSKEYS K --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSKEYS_INDEX1' \n\ + , SYS.SYSCONGLOMERATES CONGLOMS --DERBY-PROPERTIES joinStrategy = NESTEDLOOP, index = 'SYSCONGLOMERATES_INDEX1' \n\ + , SYS.SYSCOLUMNS COLS --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSCOLUMNS_INDEX1' \n\ WHERE T.TABLEID = C.TABLEID \ AND C.CONSTRAINTID = FKINFO.FK_ID \ AND FKINFO.KEY_SEQ = CONGLOMS.DESCRIPTOR.getKeyColumnPosition( \ Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/load/Import.java URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/engine/org/apache/derby/impl/load/Import.java?rev=356562&r1=356561&r2=356562&view=diff ============================================================================== --- db/derby/code/trunk/java/engine/org/apache/derby/impl/load/Import.java (original) +++ db/derby/code/trunk/java/engine/org/apache/derby/impl/load/Import.java Tue Dec 13 10:23:42 2005 @@ -223,7 +223,7 @@ else insertColumnNames = ""; String insertSql = "INSERT INTO " + entityName + insertColumnNames + - " PROPERTIES insertMode=" + insertModeValue + + " --DERBY-PROPERTIES insertMode=" + insertModeValue + "\n" + " SELECT " + cNamesWithCasts + " from " + importvti + " AS importvti" ; Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/GenericStatement.java URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/GenericStatement.java?rev=356562&r1=356561&r2=356562&view=diff ============================================================================== --- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/GenericStatement.java (original) +++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/GenericStatement.java Tue Dec 13 10:23:42 2005 @@ -99,7 +99,7 @@ * Statement interface */ - + /* RESOLVE: may need error checking, debugging code here */ public PreparedStatement prepare(LanguageConnectionContext lcc) throws StandardException { @@ -108,6 +108,14 @@ ** a recompilation of an already prepared statement. */ return prepMinion(lcc, true, (Object[]) null, (SchemaDescriptor) null, false); + } + public PreparedStatement prepare(LanguageConnectionContext lcc, boolean forMetaData) throws StandardException + { + /* + ** Note: don't reset state since this might be + ** a recompilation of an already prepared statement. + */ + return prepMinion(lcc, true, (Object[]) null, (SchemaDescriptor) null, forMetaData); } private PreparedStatement prepMinion(LanguageConnectionContext lcc, boolean cacheMe, Object[] paramDefaults, Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DD_Version.java URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DD_Version.java?rev=356562&r1=356561&r2=356562&view=diff ============================================================================== --- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DD_Version.java (original) +++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DD_Version.java Tue Dec 13 10:23:42 2005 @@ -145,6 +145,8 @@ return "10.0"; case DataDictionary.DD_VERSION_DERBY_10_1: return "10.1"; + case DataDictionary.DD_VERSION_DERBY_10_2: + return "10.2"; default: return null; } @@ -285,11 +287,17 @@ Do full upgrade. Apply changes that can NOT be safely made in soft upgrade.
+ Upgrade items for every new release +
    +
  • Drop and recreate the stored versions of the JDBC database metadata queries +
+ +
Upgrade items for 10.1
  • None.
- + * * @param tc transaction controller * @param fromMajorVersionNumber version of the on-disk database @@ -305,6 +313,12 @@ throw StandardException.newException(SQLState.UPGRADE_UNSUPPORTED, DD_Version.majorToString(fromMajorVersionNumber), this); } + + //Drop and recreate the stored versions of the JDBC database metadata queries + //This is to make sure that we have the stored versions of JDBC database + //metadata queries matching with this release of the engine. + dropJDBCMetadataSPSes(tc, false); + bootingDictionary.createSPSSet(tc, false, bootingDictionary.getSystemSchemaDescriptor().getUUID()); /* * OLD Cloudscape 5.1 upgrade code, Derby does not support Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java?rev=356562&r1=356561&r2=356562&view=diff ============================================================================== --- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java (original) +++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java Tue Dec 13 10:23:42 2005 @@ -422,7 +422,7 @@ public void boot(boolean create, Properties startParams) throws StandardException { - softwareVersion = new DD_Version(this, DataDictionary.DD_VERSION_DERBY_10_1); + softwareVersion = new DD_Version(this, DataDictionary.DD_VERSION_DERBY_10_2); /* There is a bootstrapping problem here. We would like to use * a language connection context to find the name of the system and default @@ -8186,7 +8186,7 @@ Create a set of stored prepared statements from a properties file. Key is the statement name, value is the SQL statement. */ - private void createSPSSet(TransactionController tc, boolean net, UUID schemaID) + protected void createSPSSet(TransactionController tc, boolean net, UUID schemaID) throws StandardException { Properties p = getQueryDescriptions(net); Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj?rev=356562&r1=356561&r2=356562&view=diff ============================================================================== --- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj (original) +++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj Tue Dec 13 10:23:42 2005 @@ -158,6 +158,7 @@ import java.util.ArrayList; import java.util.Hashtable; import java.util.Properties; +import java.util.StringTokenizer; import java.util.Vector; import java.lang.Character; @@ -1296,25 +1297,18 @@ } /** - * Determine whether the next sequence of tokens can be the beginning - * of a propertyList(). A properties list is the word "properties" - * followed by a dot-separated list, followed by an =, - * followed by a value. This means that the first token must be the word - * "properties", and the third must be a period (from the dot-separated - * list) or an =. + * Determine whether the next token is the beginning of a propertyList(). + * A properties list is the comment "--derby-properties" followed by a + * dot-separated list, followed by an =, followed by a value all on that + * comment line. This means that the comment should start with the word + * "derby-properties". * - * @return TRUE iff the next set of tokens is the beginning of a - * propertyList(). + * @return TRUE iff the next token is derby-properties */ - private boolean propertyListFollows() + private boolean derbyPropertiesListFollows() { - int tokKind; - return - getToken(1).kind == PROPERTIES && - ((tokKind = getToken(3).kind) == PERIOD || - tokKind == EQUALS_OPERATOR - ); + getToken(1).kind == DERBYDASHPROPERTIES; } /** @@ -1727,7 +1721,200 @@ SKIP : { /* comments */ - + "--" : IN_COMMENT +} + + SKIP : +{ /* white space */ + " " + | "\t" +} + +/* + Check if the comment characters -- are followed by DERBY-PROPERTIES + token. + + If yes, then this comment is providing user-supplied optimizer + overrides. There should be keyname=value [,keyname=value] pairs + after -- DERBY-PROPERTIES otherwise throw an exception. These + optimier overrides should be used in correct context only, + otherwise, the parser will throw an exception. + + If this comment does not start with DERBY-PROPERTOIES, then it is + just a regular comment. Skip the comment and move on to the next line. + */ + MORE [IGNORE_CASE]: +{ +"D" : LOOKFOR_DE +| +<("\n"|"\r"|"\r\n")?> : DEFAULT +| + : IT_IS_NOT_DERBYPROPERTIES_COMMENT +} + + MORE [IGNORE_CASE]: +{ +"E" : LOOKFOR_DER +| +<("\n"|"\r"|"\r\n")?> : DEFAULT +| + : IT_IS_NOT_DERBYPROPERTIES_COMMENT +} + + MORE [IGNORE_CASE]: +{ +"R" : LOOKFOR_DERB +| +<("\n"|"\r"|"\r\n")?> : DEFAULT +| + : IT_IS_NOT_DERBYPROPERTIES_COMMENT +} + + MORE [IGNORE_CASE]: +{ +"B" : LOOKFOR_DERBY +| +<("\n"|"\r"|"\r\n")?> : DEFAULT +| + : IT_IS_NOT_DERBYPROPERTIES_COMMENT +} + + MORE [IGNORE_CASE]: +{ +"Y" : LOOKFOR_DERBYDASH +| +<("\n"|"\r"|"\r\n")?> : DEFAULT +| + : IT_IS_NOT_DERBYPROPERTIES_COMMENT +} + + MORE [IGNORE_CASE]: +{ +"-" : LOOKFOR_DERBYDASHP +| +<("\n"|"\r"|"\r\n")?> : DEFAULT +| + : IT_IS_NOT_DERBYPROPERTIES_COMMENT +} + + MORE [IGNORE_CASE]: +{ +"P" : LOOKFOR_DERBYDASHPR +| +<("\n"|"\r"|"\r\n")?> : DEFAULT +| + : IT_IS_NOT_DERBYPROPERTIES_COMMENT +} + + MORE [IGNORE_CASE]: +{ +"R" : LOOKFOR_DERBYDASHPRO +| +<("\n"|"\r"|"\r\n")?> : DEFAULT +| + : IT_IS_NOT_DERBYPROPERTIES_COMMENT +} + + MORE [IGNORE_CASE]: +{ +"O" : LOOKFOR_DERBYDASHPROP +| +<("\n"|"\r"|"\r\n")?> : DEFAULT +| + : IT_IS_NOT_DERBYPROPERTIES_COMMENT +} + + MORE [IGNORE_CASE]: +{ +"P" : LOOKFOR_DERBYDASHPROPE +| +<("\n"|"\r"|"\r\n")?> : DEFAULT +| + : IT_IS_NOT_DERBYPROPERTIES_COMMENT +} + + MORE [IGNORE_CASE]: +{ +"E" : LOOKFOR_DERBYDASHPROPER +| +<("\n"|"\r"|"\r\n")?> : DEFAULT +| + : IT_IS_NOT_DERBYPROPERTIES_COMMENT +} + + MORE [IGNORE_CASE]: +{ +"R" : LOOKFOR_DERBYDASHPROPERT +| +<("\n"|"\r"|"\r\n")?> : DEFAULT +| + : IT_IS_NOT_DERBYPROPERTIES_COMMENT +} + + MORE [IGNORE_CASE]: +{ +"T" : LOOKFOR_DERBYDASHPROPERTI +| +<("\n"|"\r"|"\r\n")?> : DEFAULT +| + : IT_IS_NOT_DERBYPROPERTIES_COMMENT +} + + MORE [IGNORE_CASE]: +{ +"I" : LOOKFOR_DERBYDASHPROPERTIE +| +<("\n"|"\r"|"\r\n")?> : DEFAULT +| + : IT_IS_NOT_DERBYPROPERTIES_COMMENT +} + + MORE [IGNORE_CASE]: +{ +"E" : LOOKFOR_DERBYDASHPROPERTIES +| +<("\n"|"\r"|"\r\n")?> : DEFAULT +| + : IT_IS_NOT_DERBYPROPERTIES_COMMENT +} + + TOKEN [IGNORE_CASE] : +{ + +} + +//once we find comment starting with -- DERBY-PROPERTIES, we want to switch +//from SKIP mode to the TOKEN mode in the parser so the rest of the characters +//on the comment line can be treated as part of one big token so we can get +//keyname=value pairs from that token. The reason for treating them as one big +//token is that the existing code in parser allows newline characters between +//individual token but with optimizer overrides, all the properties have to +//be specified on the -- DERBY-PROPERTIES comment line. + TOKEN [IGNORE_CASE]: +{ +"S" { matchedToken.kind = DERBYDASHPROPERTIES; } : PROPERTIES_LIST +} + +//We found -- DERBY-PROPERTIE? where ? is a character other than S +//and hence we should the rest of the characters on this line as +//a regular comment + SKIP : +{ +<("\n"|"\r"|"\r\n")?> : DEFAULT +| + : IT_IS_NOT_DERBYPROPERTIES_COMMENT +} + + SKIP : +{ + : DEFAULT +} + +//found -- DERBY-PROPERTIES. Treat rest of the characters on the line +//as one big token and then fetch keyname=value pairs from that token. + TOKEN : +{ + : DEFAULT } /* @@ -2779,10 +2966,10 @@ tableName = qualifiedName(Limits.MAX_IDENTIFIER_LENGTH) [ LOOKAHEAD - ( { (getToken(1).kind != EOF) && (getToken(1).kind != WHERE) && !propertyListFollows() } ) + ( { (getToken(1).kind != EOF) && (getToken(1).kind != WHERE) && !derbyPropertiesListFollows() } ) [ ] correlationName = identifier(Limits.MAX_IDENTIFIER_LENGTH, true) ] - [targetProperties = propertyList() ] + [targetProperties = propertyList(false) ] [ whereToken = ( @@ -2974,9 +3161,7 @@ } { // NOTE: It only makes sense to have a property list if there is a - // table name, so I moved it into the [] for qualifiedName(). This removes - // a grammar ambiguity, because PROPERTIES is a non-reserved keyword, - // so there can be a table named PROPERTIES + // table name, so I moved it into the [] for qualifiedName(). qtn = updateBody() { @@ -3032,11 +3217,11 @@ | tableName = qualifiedName(Limits.MAX_IDENTIFIER_LENGTH) [ - LOOKAHEAD( { (getToken(1).kind != SET) && !propertyListFollows() } ) + LOOKAHEAD( { (getToken(1).kind != SET) && !derbyPropertiesListFollows() } ) [ ] correlationName = identifier(Limits.MAX_IDENTIFIER_LENGTH, true) ] [ - targetProperties = propertyList() + targetProperties = propertyList(false) ] columnList = setClauseList() [ @@ -7380,7 +7565,7 @@ LOOKAHEAD( { getToken(1).kind == LEFT_PAREN && ! subqueryFollows() } ) columnList = insertColumnList() ] - [ targetProperties = propertyList() ] + [ targetProperties = propertyList(false) ] queryExpression = queryExpression(null, NO_SET_OP) { return (QueryTreeNode) nodeFactory.getNode( @@ -7393,7 +7578,7 @@ } /* RESOLVE: Ignoring default values for now | - [ targetProperties = propertyList() ] + [ targetProperties = propertyList(false) ] <_DEFAULT> { return null; @@ -7970,14 +8155,6 @@ { {beginToken = getToken(1);} [ - // This LOOKAHEAD is required because PROPERTIES is not a reserved - // word, so we have to look ahead to see if it looks like a real - // properties clause. A properties clause is the word "properties" - // followed by a dot-separated list, followed by an =, followed by - // a value. This means that the first token must be the word - // "properties", and the third must be a period (from the dot-separated - // list) or an =. - LOOKAHEAD( { propertyListFollows() } ) fromListProperties(fromList) ] dummyTableReferenceRule(fromList) @@ -7999,7 +8176,7 @@ Properties properties; } { - properties = propertyList() + properties = propertyList(true) { fromList.setProperties(properties); } @@ -8059,14 +8236,6 @@ String correlationName = null; } { - // This LOOKAHEAD is required because PROPERTIES is not a reserved - // word, so we have to look ahead to see if it looks like a real - // properties clause. A properties clause is the word "properties" - // followed by a dot-separated list, followed by an =, followed by - // a value. This means that the first token must be the word - // "properties", and the third must be a period (from the dot-separated - // list) or an =. - LOOKAHEAD( { propertyListFollows() } ) otc = optionalTableProperties() { otc[OPTIONAL_TABLE_CLAUSES_DERIVED_RCL] = derivedRCL; @@ -8077,7 +8246,7 @@ [ [ ] correlationName = identifier(Limits.MAX_IDENTIFIER_LENGTH, true) [ derivedRCL = derivedColumnList() ] - [tableProperties = propertyList() ] ] + [tableProperties = propertyList(true) ] ] { otc = new Object[OPTIONAL_TABLE_CLAUSES_SIZE]; otc[OPTIONAL_TABLE_CLAUSES_TABLE_PROPERTIES] = tableProperties; @@ -8094,7 +8263,7 @@ Properties tableProperties = null; } { - tableProperties = propertyList() + tableProperties = propertyList(true) { otc = new Object[OPTIONAL_TABLE_CLAUSES_SIZE]; otc[OPTIONAL_TABLE_CLAUSES_TABLE_PROPERTIES] = tableProperties; @@ -8960,7 +9129,7 @@ { tableName = qualifiedName(Limits.MAX_IDENTIFIER_LENGTH) tableElementList = tableElementList() - [ properties = propertyList()] + [ properties = propertyList(false)] { return (QueryTreeNode) nodeFactory.getNode( C_NodeTypes.CREATE_TABLE_NODE, @@ -8973,60 +9142,61 @@ } } +/* + * This method is called when a comment starting with --derby-properties is found. + * Such a comment is a special directive to Derby and allows a sql to pass optimizer + * overrides. Derby looks for propertyName=value [,propertyName=value]* after + * --derby-properties and returns these properties in a Properties object as a return + * value of this method. + * The param propertiesUseAllowed true indicates that users are allowed to + * specify optimizer overrides in the given context. + * False means optimizer overrides in the given context are allowed internally + * only eg impl/load/import.java specifies property insertMode=replace/bulkInsert + * in the insert statement. This same property will not be acceptable from an + * insert statement from a user sql. + */ Properties -propertyList() throws StandardException : +propertyList(boolean propertiesUseAllowed) throws StandardException : { Properties properties = new FormatableProperties(); + StringTokenizer commaSeparatedProperties; + StringTokenizer equalOperatorSeparatedProperty; } { - property(properties) - // This LOOKAHEAD is required because a property() and a tableReference() - // can both start with ", dot.separated.list". We need to look ahead - // to the EQUALS_OPERATOR to ensure we really have another property. - ( LOOKAHEAD({ anotherPropertyFollows() }) property(properties) )* - { - checkInternalFeature("PROPERTIES"); - return properties; - } -} - -void -property(Properties properties) throws StandardException : -{ - String key; - String value; -} -{ - key = javaDSL() value = propertyValue() - { - // Do not allow user to specify multiple values for the same key - if (properties.put(key, value) != null) - { - throw StandardException.newException(SQLState.LANG_DUPLICATE_PROPERTY, key); + { + //first use StringTokenizer to get tokens which are delimited by ,s + commaSeparatedProperties = new StringTokenizer(getToken(1).image,","); + while (commaSeparatedProperties.hasMoreTokens()) { + //Now verify that tokens delimited by ,s follow propertyName=value pattern + String currentProperty = commaSeparatedProperties.nextToken(); + equalOperatorSeparatedProperty = new StringTokenizer(currentProperty,"=", true); + if (equalOperatorSeparatedProperty.countTokens() != 3) + throw StandardException.newException(SQLState.PROPERTY_SYNTAX_INVALID); + else { + String key = equalOperatorSeparatedProperty.nextToken().trim(); + if (!equalOperatorSeparatedProperty.nextToken().equals("=")) + throw StandardException.newException(SQLState.PROPERTY_SYNTAX_INVALID); + String value = equalOperatorSeparatedProperty.nextToken().trim(); + verifyImageLength(value); + /* Trim off the leading and trailing ', and compress all '' to ' */ + if (value.startsWith("'") && value.endsWith("'")) + value = compressQuotes(value.substring(1, value.length() - 1), SINGLEQUOTES); + /* Trim off the leading and trailing ", and compress all "" to " */ + else if (value.startsWith("\"") && value.endsWith("\"")) + value = compressQuotes(value.substring(1, value.length() - 1), DOUBLEQUOTES); + else + value = value.toUpperCase(); + // Do not allow user to specify multiple values for the same key + if (properties.put(key, value) != null) + { + throw StandardException.newException(SQLState.LANG_DUPLICATE_PROPERTY, key); + } } } -} - -String -propertyValue() throws StandardException : -{ - int intValue; - String value; -} -{ - value = caseInsensitiveIdentifierPlusReservedWords() - { - return value; - } -| - intValue = uint_value() - { - return String.valueOf(intValue); - } -| - value = string() - { - return value; + //if this property override is supported in internal mode only, then do that verification here. + if (!propertiesUseAllowed) + checkInternalFeature("DERBY-PROPERTIES"); + return properties; } } @@ -9084,7 +9254,7 @@ [ unique = unique() ] indexName = qualifiedName(Limits.MAX_IDENTIFIER_LENGTH) tableName = qualifiedName(Limits.MAX_IDENTIFIER_LENGTH) indexColumnList(indexColumnList) - [ properties = propertyList()] + [ properties = propertyList(false) ] { /* User allowed to specify schema name on table and index. * If no schema name specified for index, then it "inherits" @@ -10745,7 +10915,7 @@ { [ constraintName = constraintNameDefinition() ] tcdn = tableConstraint(constraintName) - [ properties = propertyList()] + [ properties = propertyList(false) ] { if (properties != null) { @@ -11038,7 +11208,7 @@ //exception if null constraint is defined for this column-level primary //key constraint constraintType = uniqueSpecification(dataTypeDescriptor,columnName) - [ properties = propertyList()] + [ properties = propertyList(false) ] { ResultColumnList uniqueColumnList = (ResultColumnList) nodeFactory.getNode( @@ -11064,7 +11234,7 @@ } | referencedTable = referencesSpecification(refRcl, refActions) - [ properties = propertyList()] + [ properties = propertyList(false)] { ResultColumnList fkRcl = (ResultColumnList) nodeFactory.getNode( C_NodeTypes.RESULT_COLUMN_LIST, Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/conn/GenericLanguageConnectionContext.java URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/conn/GenericLanguageConnectionContext.java?rev=356562&r1=356561&r2=356562&view=diff ============================================================================== --- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/conn/GenericLanguageConnectionContext.java (original) +++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/conn/GenericLanguageConnectionContext.java Tue Dec 13 10:23:42 2005 @@ -698,21 +698,20 @@ /** * @see LanguageConnectionContext#prepareInternalStatement */ - public PreparedStatement prepareInternalStatement(SchemaDescriptor compilationSchema, String sqlText, boolean isForReadOnly) + public PreparedStatement prepareInternalStatement(SchemaDescriptor compilationSchema, String sqlText, boolean isForReadOnly, boolean forMetaData) throws StandardException { - return connFactory.getStatement(compilationSchema, sqlText, isForReadOnly).prepare(this); - } - + return connFactory.getStatement(compilationSchema, sqlText, isForReadOnly).prepare(this, forMetaData); + } + /** - * @see LanguageConnectionContext#prepareInternalStatement - */ + * @see LanguageConnectionContext#prepareInternalStatement + */ public PreparedStatement prepareInternalStatement(String sqlText) throws StandardException { - return connFactory.getStatement(sd, sqlText, true).prepare(this); - } - + return connFactory.getStatement(sd, sqlText, true).prepare(this); + } /** * Remove the activation to those known about by this connection. Modified: db/derby/code/trunk/java/engine/org/apache/derby/loc/messages_en.properties URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/engine/org/apache/derby/loc/messages_en.properties?rev=356562&r1=356561&r2=356562&view=diff ============================================================================== --- db/derby/code/trunk/java/engine/org/apache/derby/loc/messages_en.properties (original) +++ db/derby/code/trunk/java/engine/org/apache/derby/loc/messages_en.properties Tue Dec 13 10:23:42 2005 @@ -58,6 +58,8 @@ XCY00.S=Invalid value for property ''{0}''=''{1}''. XCY02.S=The requested property change is not supported ''{0}''=''{1}''. XCY03.S=Required property ''{0}'' has not been set. +XCY04.S=Invalid syntax for optimizer overrides. The syntax should be -- DERBY-PROPERTIES propertyName = value [, propertyName = value]* + # Cryptography XBCX0.S=Exception from Cryptography provider. See next exception for details. Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/metadata.out URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/metadata.out?rev=356562&r1=356561&r2=356562&view=diff ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/metadata.out (original) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/metadata.out Tue Dec 13 10:23:42 2005 @@ -40,6 +40,16 @@ ,APP,GETPCTEST4A,null,null,null,org.apache.derbyTesting.functionTests.tests.jdbcapi.metadata.getpc4a,1 ,APP,GETPCTEST4B,null,null,null,org.apache.derbyTesting.functionTests.tests.jdbcapi.metadata.getpc4b,1 ,APP,GETPCTEST4BX,null,null,null,org.apache.derbyTesting.functionTests.tests.jdbcapi.metadata.getpc4b,1 +getUDTs() with user-named types null : +TYPE_CAT[CHAR],TYPE_SCHEM[CHAR],TYPE_NAME[VARCHAR],CLASS_NAME[LONG VARCHAR],DATA_TYPE[INTEGER],REMARKS[CHAR] +getUDTs() with user-named types in ('JAVA_OBJECT') : +TYPE_CAT[CHAR],TYPE_SCHEM[CHAR],TYPE_NAME[VARCHAR],CLASS_NAME[LONG VARCHAR],DATA_TYPE[INTEGER],REMARKS[CHAR] +getUDTs() with user-named types in ('STRUCT') : +TYPE_CAT[CHAR],TYPE_SCHEM[CHAR],TYPE_NAME[VARCHAR],CLASS_NAME[LONG VARCHAR],DATA_TYPE[INTEGER],REMARKS[CHAR] +getUDTs() with user-named types in ('DISTINCT') : +TYPE_CAT[CHAR],TYPE_SCHEM[CHAR],TYPE_NAME[VARCHAR],CLASS_NAME[LONG VARCHAR],DATA_TYPE[INTEGER],REMARKS[CHAR] +getUDTs() with user-named types in ('JAVA_OBJECT', 'STRUCT') : +TYPE_CAT[CHAR],TYPE_SCHEM[CHAR],TYPE_NAME[VARCHAR],CLASS_NAME[LONG VARCHAR],DATA_TYPE[INTEGER],REMARKS[CHAR] allProceduresAreCallable(): true getUserName(): APP isReadOnly(): false @@ -151,6 +161,20 @@ supportsDataManipulationTransactionsOnly(): false dataDefinitionCausesTransactionCommit(): false dataDefinitionIgnoredInTransactions(): false +Test the metadata calls related to visibility of changes made by others for different resultset types +Since Derby materializes a forward only ResultSet incrementally, it is possible to see changes +made by others and hence following 3 metadata calls will return true for forward only ResultSets. +othersUpdatesAreVisible(ResultSet.TYPE_FORWARD_ONLY)? true +othersDeletesAreVisible(ResultSet.TYPE_FORWARD_ONLY)? true +othersInsertsAreVisible(ResultSet.TYPE_FORWARD_ONLY)? true +Scroll insensitive ResultSet by their definition do not see changes made by others and hence following metadata calls return false +othersUpdatesAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE)? false +othersDeletesAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE)? false +othersInsertsAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE)? false +Derby does not yet implement scroll sensitive resultsets and hence following metadata calls return false +othersUpdatesAreVisible(ResultSet.TYPE_SCROLL_SENSITIVE)? false +othersDeletesAreVisible(ResultSet.TYPE_SCROLL_SENSITIVE)? false +othersInsertsAreVisible(ResultSet.TYPE_SCROLL_SENSITIVE)? false getConnection(): same connection getProcedureColumns(): PROCEDURE_CAT[VARCHAR],PROCEDURE_SCHEM[VARCHAR],PROCEDURE_NAME[VARCHAR],COLUMN_NAME[VARCHAR],COLUMN_TYPE[SMALLINT],DATA_TYPE[SMALLINT],TYPE_NAME[VARCHAR],PRECISION[INTEGER],LENGTH[INTEGER],SCALE[SMALLINT],RADIX[SMALLINT],NULLABLE[SMALLINT],REMARKS[VARCHAR],METHOD_ID[SMALLINT],PARAMETER_ID[SMALLINT] @@ -427,6 +451,119 @@ ,SYS,SYSTABLES,TABLETYPE,1,CHAR,1,null,null,null,0,,null,null,null,2,3,NO ,SYS,SYSTABLES,SCHEMAID,1,CHAR,36,null,null,null,0,,null,null,null,72,4,NO ,SYS,SYSTABLES,LOCKGRANULARITY,1,CHAR,1,null,null,null,0,,null,null,null,2,5,NO +Test escaped numeric functions - JDBC 3.0 C.1 +TEST FUNCTIONS DECLARED IN DATABASEMETADATA LIST +Executing VALUES { fn ABS(-25.67) } -- = >25.67< << +Executing VALUES { fn MOD(124, 7) } -- = >5< << +Executing VALUES { fn SQRT(6.22) } -- = >2.49< << +TEST FUNCTIONS NOT DECLARED IN DATABASEMETADATA LIST +Executing VALUES { fn ACOS(1.34) } -- +SQLSTATE(42X01): Syntax error: Encountered "ACOS" at line 1, column 13. +Executing VALUES { fn ASIN(1.21) } -- +SQLSTATE(42X01): Syntax error: Encountered "ASIN" at line 1, column 13. +Executing VALUES { fn ATAN(0.34) } -- +SQLSTATE(42X01): Syntax error: Encountered "ATAN" at line 1, column 13. +Executing VALUES { fn ATAN2(0.56, 1.2) } -- +SQLSTATE(42X01): Syntax error: Encountered "ATAN2" at line 1, column 13. +Executing VALUES { fn CEILING(3.45) } -- +SQLSTATE(42X01): Syntax error: Encountered "CEILING" at line 1, column 13. +Executing VALUES { fn COS(1.2) } -- +SQLSTATE(42X01): Syntax error: Encountered "COS" at line 1, column 13. +Executing VALUES { fn COT(3.4) } -- +SQLSTATE(42X01): Syntax error: Encountered "COT" at line 1, column 13. +Executing VALUES { fn DEGREES(2.1) } -- +SQLSTATE(42X01): Syntax error: Encountered "DEGREES" at line 1, column 13. +Executing VALUES { fn EXP(2.3) } -- +SQLSTATE(42X01): Syntax error: Encountered "EXP" at line 1, column 13. +Executing VALUES { fn FLOOR(3.22) } -- +SQLSTATE(42X01): Syntax error: Encountered "FLOOR" at line 1, column 13. +Executing VALUES { fn LOG(34.1) } -- +SQLSTATE(42X01): Syntax error: Encountered "LOG" at line 1, column 13. +Executing VALUES { fn LOG10(18.7) } -- +SQLSTATE(42X01): Syntax error: Encountered "LOG10" at line 1, column 13. +Executing VALUES { fn PI() } -- +SQLSTATE(42X01): Syntax error: Encountered "PI" at line 1, column 13. +Executing VALUES { fn POWER(2, 3) } -- +SQLSTATE(42X01): Syntax error: Encountered "POWER" at line 1, column 13. +Executing VALUES { fn RADIANS(54) } -- +SQLSTATE(42X01): Syntax error: Encountered "RADIANS" at line 1, column 13. +Executing VALUES { fn RAND(17) } -- +SQLSTATE(42X01): Syntax error: Encountered "RAND" at line 1, column 13. +Executing VALUES { fn ROUND(345.345, 1) } -- +SQLSTATE(42X01): Syntax error: Encountered "ROUND" at line 1, column 13. +Executing VALUES { fn SIGN(-34) } -- +SQLSTATE(42X01): Syntax error: Encountered "SIGN" at line 1, column 13. +Executing VALUES { fn SIN(0.32) } -- +SQLSTATE(42X01): Syntax error: Encountered "SIN" at line 1, column 13. +Executing VALUES { fn TAN(0.57) } -- +SQLSTATE(42X01): Syntax error: Encountered "TAN" at line 1, column 13. +Executing VALUES { fn TRUNCATE(345.395, 1) } -- +SQLSTATE(42X01): Syntax error: Encountered "TRUNCATE" at line 1, column 13. +Test escaped string functions - JDBC 3.0 C.2 +TEST FUNCTIONS DECLARED IN DATABASEMETADATA LIST +Executing VALUES { fn CONCAT('hello', 'there') } -- = >hellothere< << +Executing VALUES { fn LENGTH('four ') } -- = >4< << +Executing VALUES { fn LCASE('Fernando Alonso') } -- = >fernando alonso< << +Executing VALUES { fn LOCATE('jour', 'Bonjour') } -- = >4< << +Executing VALUES { fn LTRIM(' left trim ') } -- = >left trim < << +Executing VALUES { fn RTRIM(' right trim ') } -- = > right trim< << +Executing VALUES { fn SUBSTRING('Ruby the Rubicon Jeep', 10, 7) } -- = >Rubicon< << +Executing VALUES { fn UCASE('Fernando Alonso') } -- = >FERNANDO ALONSO< << +TEST FUNCTIONS NOT DECLARED IN DATABASEMETADATA LIST +Executing VALUES { fn ASCII('Yellow') } -- +SQLSTATE(42X01): Syntax error: Encountered "ASCII" at line 1, column 13. +Executing VALUES { fn CHAR(65) } -- = >65 < << +FAIL function works but not declared in list: CHAR +Executing VALUES { fn DIFFERENCE('Pires', 'Piers') } -- +SQLSTATE(42X01): Syntax error: Encountered "DIFFERENCE" at line 1, column 13. +Executing VALUES { fn INSERT('Bill Clinton', 4, 'William') } -- +SQLSTATE(42X01): Syntax error: Encountered "INSERT" at line 1, column 13. +Executing VALUES { fn LEFT('Bonjour', 3) } -- +SQLSTATE(42X01): Syntax error: Encountered "LEFT" at line 1, column 13. +Executing VALUES { fn REPEAT('echo', 3) } -- +SQLSTATE(42X01): Syntax error: Encountered "REPEAT" at line 1, column 13. +Executing VALUES { fn REPLACE('to be or not to be', 'be', 'England') } -- +SQLSTATE(42X01): Syntax error: Encountered "REPLACE" at line 1, column 13. +Executing VALUES { fn SOUNDEX('Derby') } -- +SQLSTATE(42X01): Syntax error: Encountered "SOUNDEX" at line 1, column 13. +Executing VALUES { fn SPACE(12) } -- +SQLSTATE(42X01): Syntax error: Encountered "SPACE" at line 1, column 13. +Test escaped date time functions - JDBC 3.0 C.3 +TEST FUNCTIONS DECLARED IN DATABASEMETADATA LIST +Executing VALUES CASE WHEN { fn CURDATE()} = CURRENT_DATE THEN 'OK' ELSE 'wrong' END -- = >OK< << +Executing VALUES CASE WHEN { fn CURTIME()} = CURRENT_TIME THEN 'OK' ELSE 'wrong' END -- = >OK< << +Executing VALUES { fn HOUR({t '16:13:03'}) } -- = >16< << +Executing VALUES { fn MINUTE({t '16:13:03'}) } -- = >13< << +Executing VALUES { fn MONTH({d '1995-12-19'}) } -- = >12< << +Executing VALUES { fn SECOND({t '16:13:03'}) } -- = >3< << +Executing VALUES { fn TIMESTAMPADD(SQL_TSI_DAY, 7, {ts 'xxxxxxFILTERED-TIMESTAMPxxxxx}) } -- = >xxxxxxFILTERED-TIMESTAMPxxxxx< << +Executing VALUES { fn TIMESTAMPDIFF(SQL_TSI_DAY, {ts 'xxxxxxFILTERED-TIMESTAMPxxxxx}, {ts 'xxxxxxFILTERED-TIMESTAMPxxxxx}) } -- = >683< << +Executing VALUES { fn YEAR({d '1995-12-19'}) } -- = >1995< << +TEST FUNCTIONS NOT DECLARED IN DATABASEMETADATA LIST +Executing VALUES { fn DAYNAME({d '1995-12-19'h}) } -- +SQLSTATE(42X01): Syntax error: Encountered "DAYNAME" at line 1, column 13. +Executing VALUES { fn DAYOFMONTH({d '1995-12-19'}) } -- +SQLSTATE(42X01): Syntax error: Encountered "DAYOFMONTH" at line 1, column 13. +Executing VALUES { fn DAYOFWEEK({d '1995-12-19'}) } -- +SQLSTATE(42X01): Syntax error: Encountered "DAYOFWEEK" at line 1, column 13. +Executing VALUES { fn DAYOFYEAR({d '1995-12-19'}) } -- +SQLSTATE(42X01): Syntax error: Encountered "DAYOFYEAR" at line 1, column 13. +Executing VALUES { fn MONTHNAME({d '1995-12-19'}) } -- +SQLSTATE(42X01): Syntax error: Encountered "MONTHNAME" at line 1, column 13. +Executing VALUES CASE WHEN { fn NOW()} = CURRENT_TIMESTAMP THEN 'OK' ELSE 'wrong' END -- +SQLSTATE(42X01): Syntax error: Encountered "NOW" at line 1, column 23. +Executing VALUES { fn QUARTER({d '1995-12-19'}) } -- +SQLSTATE(42X01): Syntax error: Encountered "QUARTER" at line 1, column 13. +Executing VALUES { fn WEEK({d '1995-12-19'}) } -- +SQLSTATE(42X01): Syntax error: Encountered "WEEK" at line 1, column 13. +Test escaped system functions - JDBC 3.0 C.4 +TEST FUNCTIONS DECLARED IN DATABASEMETADATA LIST +Executing VALUES { fn USER() } -- = >APP< << +TEST FUNCTIONS NOT DECLARED IN DATABASEMETADATA LIST +Executing VALUES { fn DATABASE() } -- +SQLSTATE(42X01): Syntax error: Encountered "DATABASE" at line 1, column 13. +Executing VALUES { fn IFNULL('this', 'that') } -- +SQLSTATE(42X01): Syntax error: Encountered "IFNULL" at line 1, column 13. ----- Referential action values RESTRICT = 1 Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/odbc_metadata.out URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/odbc_metadata.out?rev=356562&r1=356561&r2=356562&view=diff ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/odbc_metadata.out (original) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/odbc_metadata.out Tue Dec 13 10:23:42 2005 @@ -70,6 +70,16 @@ ,APP,GETPCTEST4A,null,null,null,org.apache.derbyTesting.functionTests.tests.jdbcapi.metadata.getpc4a,1 ,APP,GETPCTEST4B,null,null,null,org.apache.derbyTesting.functionTests.tests.jdbcapi.metadata.getpc4b,1 ,APP,GETPCTEST4BX,null,null,null,org.apache.derbyTesting.functionTests.tests.jdbcapi.metadata.getpc4b,1 +getUDTs() with user-named types null : +TYPE_CAT[CHAR],TYPE_SCHEM[CHAR],TYPE_NAME[VARCHAR],CLASS_NAME[LONG VARCHAR],DATA_TYPE[INTEGER],REMARKS[CHAR] +getUDTs() with user-named types in ('JAVA_OBJECT') : +TYPE_CAT[CHAR],TYPE_SCHEM[CHAR],TYPE_NAME[VARCHAR],CLASS_NAME[LONG VARCHAR],DATA_TYPE[INTEGER],REMARKS[CHAR] +getUDTs() with user-named types in ('STRUCT') : +TYPE_CAT[CHAR],TYPE_SCHEM[CHAR],TYPE_NAME[VARCHAR],CLASS_NAME[LONG VARCHAR],DATA_TYPE[INTEGER],REMARKS[CHAR] +getUDTs() with user-named types in ('DISTINCT') : +TYPE_CAT[CHAR],TYPE_SCHEM[CHAR],TYPE_NAME[VARCHAR],CLASS_NAME[LONG VARCHAR],DATA_TYPE[INTEGER],REMARKS[CHAR] +getUDTs() with user-named types in ('JAVA_OBJECT', 'STRUCT') : +TYPE_CAT[CHAR],TYPE_SCHEM[CHAR],TYPE_NAME[VARCHAR],CLASS_NAME[LONG VARCHAR],DATA_TYPE[INTEGER],REMARKS[CHAR] allProceduresAreCallable(): true getUserName(): APP isReadOnly(): false @@ -181,6 +191,20 @@ supportsDataManipulationTransactionsOnly(): false dataDefinitionCausesTransactionCommit(): false dataDefinitionIgnoredInTransactions(): false +Test the metadata calls related to visibility of changes made by others for different resultset types +Since Derby materializes a forward only ResultSet incrementally, it is possible to see changes +made by others and hence following 3 metadata calls will return true for forward only ResultSets. +othersUpdatesAreVisible(ResultSet.TYPE_FORWARD_ONLY)? true +othersDeletesAreVisible(ResultSet.TYPE_FORWARD_ONLY)? true +othersInsertsAreVisible(ResultSet.TYPE_FORWARD_ONLY)? true +Scroll insensitive ResultSet by their definition do not see changes made by others and hence following metadata calls return false +othersUpdatesAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE)? false +othersDeletesAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE)? false +othersInsertsAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE)? false +Derby does not yet implement scroll sensitive resultsets and hence following metadata calls return false +othersUpdatesAreVisible(ResultSet.TYPE_SCROLL_SENSITIVE)? false +othersDeletesAreVisible(ResultSet.TYPE_SCROLL_SENSITIVE)? false +othersInsertsAreVisible(ResultSet.TYPE_SCROLL_SENSITIVE)? false getConnection(): same connection getProcedureColumns(): PROCEDURE_CAT[VARCHAR],PROCEDURE_SCHEM[VARCHAR],PROCEDURE_NAME[VARCHAR],COLUMN_NAME[VARCHAR],COLUMN_TYPE[SMALLINT],DATA_TYPE[SMALLINT],TYPE_NAME[VARCHAR],COLUMN_SIZE[INTEGER],BUFFER_LENGTH[INTEGER],DECIMAL_DIGITS[SMALLINT],NUM_PREC_RADIX[SMALLINT],NULLABLE[SMALLINT],REMARKS[VARCHAR],COLUMN_DEF[VARCHAR],SQL_DATA_TYPE[SMALLINT],SQL_DATETIME_SUB[SMALLINT],CHAR_OCTET_LENGTH[INTEGER],ORDINAL_POSITION[INTEGER],IS_NULLABLE[VARCHAR],METHOD_ID[SMALLINT],PARAMETER_ID[SMALLINT] @@ -458,6 +482,119 @@ ,SYS,SYSTABLES,SCHEMAID,1,CHAR,36,72,null,null,0,,null,1,null,72,4,NO ,SYS,SYSTABLES,LOCKGRANULARITY,1,CHAR,1,2,null,null,0,,null,1,null,2,5,NO OK -- user result set closed by intervening OBDC getColumns request; this was expected because of the way the test works. +Test escaped numeric functions - JDBC 3.0 C.1 +TEST FUNCTIONS DECLARED IN DATABASEMETADATA LIST +Executing VALUES { fn ABS(-25.67) } -- = >25.67< << +Executing VALUES { fn MOD(124, 7) } -- = >5< << +Executing VALUES { fn SQRT(6.22) } -- = >2.49< << +TEST FUNCTIONS NOT DECLARED IN DATABASEMETADATA LIST +Executing VALUES { fn ACOS(1.34) } -- +SQLSTATE(42X01): Syntax error: Encountered "ACOS" at line 1, column 13. +Executing VALUES { fn ASIN(1.21) } -- +SQLSTATE(42X01): Syntax error: Encountered "ASIN" at line 1, column 13. +Executing VALUES { fn ATAN(0.34) } -- +SQLSTATE(42X01): Syntax error: Encountered "ATAN" at line 1, column 13. +Executing VALUES { fn ATAN2(0.56, 1.2) } -- +SQLSTATE(42X01): Syntax error: Encountered "ATAN2" at line 1, column 13. +Executing VALUES { fn CEILING(3.45) } -- +SQLSTATE(42X01): Syntax error: Encountered "CEILING" at line 1, column 13. +Executing VALUES { fn COS(1.2) } -- +SQLSTATE(42X01): Syntax error: Encountered "COS" at line 1, column 13. +Executing VALUES { fn COT(3.4) } -- +SQLSTATE(42X01): Syntax error: Encountered "COT" at line 1, column 13. +Executing VALUES { fn DEGREES(2.1) } -- +SQLSTATE(42X01): Syntax error: Encountered "DEGREES" at line 1, column 13. +Executing VALUES { fn EXP(2.3) } -- +SQLSTATE(42X01): Syntax error: Encountered "EXP" at line 1, column 13. +Executing VALUES { fn FLOOR(3.22) } -- +SQLSTATE(42X01): Syntax error: Encountered "FLOOR" at line 1, column 13. +Executing VALUES { fn LOG(34.1) } -- +SQLSTATE(42X01): Syntax error: Encountered "LOG" at line 1, column 13. +Executing VALUES { fn LOG10(18.7) } -- +SQLSTATE(42X01): Syntax error: Encountered "LOG10" at line 1, column 13. +Executing VALUES { fn PI() } -- +SQLSTATE(42X01): Syntax error: Encountered "PI" at line 1, column 13. +Executing VALUES { fn POWER(2, 3) } -- +SQLSTATE(42X01): Syntax error: Encountered "POWER" at line 1, column 13. +Executing VALUES { fn RADIANS(54) } -- +SQLSTATE(42X01): Syntax error: Encountered "RADIANS" at line 1, column 13. +Executing VALUES { fn RAND(17) } -- +SQLSTATE(42X01): Syntax error: Encountered "RAND" at line 1, column 13. +Executing VALUES { fn ROUND(345.345, 1) } -- +SQLSTATE(42X01): Syntax error: Encountered "ROUND" at line 1, column 13. +Executing VALUES { fn SIGN(-34) } -- +SQLSTATE(42X01): Syntax error: Encountered "SIGN" at line 1, column 13. +Executing VALUES { fn SIN(0.32) } -- +SQLSTATE(42X01): Syntax error: Encountered "SIN" at line 1, column 13. +Executing VALUES { fn TAN(0.57) } -- +SQLSTATE(42X01): Syntax error: Encountered "TAN" at line 1, column 13. +Executing VALUES { fn TRUNCATE(345.395, 1) } -- +SQLSTATE(42X01): Syntax error: Encountered "TRUNCATE" at line 1, column 13. +Test escaped string functions - JDBC 3.0 C.2 +TEST FUNCTIONS DECLARED IN DATABASEMETADATA LIST +Executing VALUES { fn CONCAT('hello', 'there') } -- = >hellothere< << +Executing VALUES { fn LENGTH('four ') } -- = >4< << +Executing VALUES { fn LCASE('Fernando Alonso') } -- = >fernando alonso< << +Executing VALUES { fn LOCATE('jour', 'Bonjour') } -- = >4< << +Executing VALUES { fn LTRIM(' left trim ') } -- = >left trim < << +Executing VALUES { fn RTRIM(' right trim ') } -- = > right trim< << +Executing VALUES { fn SUBSTRING('Ruby the Rubicon Jeep', 10, 7) } -- = >Rubicon< << +Executing VALUES { fn UCASE('Fernando Alonso') } -- = >FERNANDO ALONSO< << +TEST FUNCTIONS NOT DECLARED IN DATABASEMETADATA LIST +Executing VALUES { fn ASCII('Yellow') } -- +SQLSTATE(42X01): Syntax error: Encountered "ASCII" at line 1, column 13. +Executing VALUES { fn CHAR(65) } -- = >65 < << +FAIL function works but not declared in list: CHAR +Executing VALUES { fn DIFFERENCE('Pires', 'Piers') } -- +SQLSTATE(42X01): Syntax error: Encountered "DIFFERENCE" at line 1, column 13. +Executing VALUES { fn INSERT('Bill Clinton', 4, 'William') } -- +SQLSTATE(42X01): Syntax error: Encountered "INSERT" at line 1, column 13. +Executing VALUES { fn LEFT('Bonjour', 3) } -- +SQLSTATE(42X01): Syntax error: Encountered "LEFT" at line 1, column 13. +Executing VALUES { fn REPEAT('echo', 3) } -- +SQLSTATE(42X01): Syntax error: Encountered "REPEAT" at line 1, column 13. +Executing VALUES { fn REPLACE('to be or not to be', 'be', 'England') } -- +SQLSTATE(42X01): Syntax error: Encountered "REPLACE" at line 1, column 13. +Executing VALUES { fn SOUNDEX('Derby') } -- +SQLSTATE(42X01): Syntax error: Encountered "SOUNDEX" at line 1, column 13. +Executing VALUES { fn SPACE(12) } -- +SQLSTATE(42X01): Syntax error: Encountered "SPACE" at line 1, column 13. +Test escaped date time functions - JDBC 3.0 C.3 +TEST FUNCTIONS DECLARED IN DATABASEMETADATA LIST +Executing VALUES CASE WHEN { fn CURDATE()} = CURRENT_DATE THEN 'OK' ELSE 'wrong' END -- = >OK< << +Executing VALUES CASE WHEN { fn CURTIME()} = CURRENT_TIME THEN 'OK' ELSE 'wrong' END -- = >OK< << +Executing VALUES { fn HOUR({t '16:13:03'}) } -- = >16< << +Executing VALUES { fn MINUTE({t '16:13:03'}) } -- = >13< << +Executing VALUES { fn MONTH({d '1995-12-19'}) } -- = >12< << +Executing VALUES { fn SECOND({t '16:13:03'}) } -- = >3< << +Executing VALUES { fn TIMESTAMPADD(SQL_TSI_DAY, 7, {ts 'xxxxxxFILTERED-TIMESTAMPxxxxx}) } -- = >xxxxxxFILTERED-TIMESTAMPxxxxx< << +Executing VALUES { fn TIMESTAMPDIFF(SQL_TSI_DAY, {ts 'xxxxxxFILTERED-TIMESTAMPxxxxx}, {ts 'xxxxxxFILTERED-TIMESTAMPxxxxx}) } -- = >683< << +Executing VALUES { fn YEAR({d '1995-12-19'}) } -- = >1995< << +TEST FUNCTIONS NOT DECLARED IN DATABASEMETADATA LIST +Executing VALUES { fn DAYNAME({d '1995-12-19'h}) } -- +SQLSTATE(42X01): Syntax error: Encountered "DAYNAME" at line 1, column 13. +Executing VALUES { fn DAYOFMONTH({d '1995-12-19'}) } -- +SQLSTATE(42X01): Syntax error: Encountered "DAYOFMONTH" at line 1, column 13. +Executing VALUES { fn DAYOFWEEK({d '1995-12-19'}) } -- +SQLSTATE(42X01): Syntax error: Encountered "DAYOFWEEK" at line 1, column 13. +Executing VALUES { fn DAYOFYEAR({d '1995-12-19'}) } -- +SQLSTATE(42X01): Syntax error: Encountered "DAYOFYEAR" at line 1, column 13. +Executing VALUES { fn MONTHNAME({d '1995-12-19'}) } -- +SQLSTATE(42X01): Syntax error: Encountered "MONTHNAME" at line 1, column 13. +Executing VALUES CASE WHEN { fn NOW()} = CURRENT_TIMESTAMP THEN 'OK' ELSE 'wrong' END -- +SQLSTATE(42X01): Syntax error: Encountered "NOW" at line 1, column 23. +Executing VALUES { fn QUARTER({d '1995-12-19'}) } -- +SQLSTATE(42X01): Syntax error: Encountered "QUARTER" at line 1, column 13. +Executing VALUES { fn WEEK({d '1995-12-19'}) } -- +SQLSTATE(42X01): Syntax error: Encountered "WEEK" at line 1, column 13. +Test escaped system functions - JDBC 3.0 C.4 +TEST FUNCTIONS DECLARED IN DATABASEMETADATA LIST +Executing VALUES { fn USER() } -- = >APP< << +TEST FUNCTIONS NOT DECLARED IN DATABASEMETADATA LIST +Executing VALUES { fn DATABASE() } -- +SQLSTATE(42X01): Syntax error: Encountered "DATABASE" at line 1, column 13. +Executing VALUES { fn IFNULL('this', 'that') } -- +SQLSTATE(42X01): Syntax error: Encountered "IFNULL" at line 1, column 13. ----- Referential action values RESTRICT = 1