db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From d..@apache.org
Subject svn commit: r508186 - in /db/derby/code/trunk/java/testing/org/apache/derbyTesting: functionTests/tests/lang/StatementPlanCacheTest.java functionTests/tests/lang/_Suite.java junit/DatabasePropertyTestSetup.java
Date Thu, 15 Feb 2007 22:02:54 GMT
Author: djd
Date: Thu Feb 15 14:02:54 2007
New Revision: 508186

URL: http://svn.apache.org/viewvc?view=rev&rev=508186
Log:
DERBY-2332 (partial) Initial JUnit test for statement caching. New utility decorator
method for setting a single database property and re-booting.

Added:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/StatementPlanCacheTest.java
  (with props)
Modified:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/DatabasePropertyTestSetup.java

Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/StatementPlanCacheTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/StatementPlanCacheTest.java?view=auto&rev=508186
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/StatementPlanCacheTest.java
(added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/StatementPlanCacheTest.java
Thu Feb 15 14:02:54 2007
@@ -0,0 +1,307 @@
+/*
+*
+* Derby - Class org.apache.derbyTesting.functionTests.lang.StatementPlanCacheTest
+*
+* Licensed to the Apache Software Foundation (ASF) under one or more
+* contributor license agreements.  See the NOTICE file distributed with
+* this work for additional information regarding copyright ownership.
+* The ASF licenses this file to You under the Apache License, Version 2.0
+* (the "License"); you may not use this file except in compliance with
+* the License.  You may obtain a copy of the License at
+*
+*    http://www.apache.org/licenses/LICENSE-2.0
+*
+* Unless required by applicable law or agreed to in writing, 
+* software distributed under the License is distributed on an 
+* "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, 
+* either express or implied. See the License for the specific 
+* language governing permissions and limitations under the License.
+*/
+package org.apache.derbyTesting.functionTests.tests.lang;
+
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
+
+import junit.framework.Test;
+import junit.framework.TestSuite;
+
+import org.apache.derbyTesting.junit.BaseJDBCTestCase;
+import org.apache.derbyTesting.junit.CleanDatabaseTestSetup;
+import org.apache.derbyTesting.junit.DatabasePropertyTestSetup;
+
+/**
+ * Tests statement plan caching.
+ * <BR>
+ * Size of the cache can be configured by derby.language.statementCacheSize.
+ * derby.language.statementCacheSize.
+ * <BR>
+ * The statement cache can be viewed using the diagnostic table
+ * SYSCS_DIAG.STATEMENT_CACHE
+ * <BR>
+ * The test also uses the fact that toString() for a Derby embedded
+ * prepared statement returns a string identifier that matches the
+ * ID column of SYSCS_DIAG.STATEMENT_CACHE.
+ *
+ */
+public class StatementPlanCacheTest extends BaseJDBCTestCase {
+    
+    private static int CACHE_SIZE;
+    
+    private int statmentCacheSize;
+    private PreparedStatement cacheInPlanPS_ID;
+    private PreparedStatement cacheInPlanPS_TEXT;
+  
+    public StatementPlanCacheTest(String name) {
+        super(name);
+        statmentCacheSize = CACHE_SIZE;
+    }
+    
+    /**
+     * Runs in embedded only since it's testing the server side cache.
+     */
+    public static Test suite() {
+        TestSuite suite = new TestSuite("StatementPlanCacheTest");
+        
+        CACHE_SIZE = 100; // default cache size
+        suite.addTest(baseSuite("default"));
+        suite.addTest(suiteWithSizeSet(5));
+        suite.addTest(suiteWithSizeSet(140));
+        
+        // no caching
+        suite.addTest(DatabasePropertyTestSetup.singleProperty(
+                new StatementPlanCacheTest("noCachingTest"),
+                "derby.language.statementCacheSize", "0", true));
+        
+        
+        return new CleanDatabaseTestSetup(suite) {
+            protected void decorateSQL(Statement s) throws SQLException {
+                s.execute("CREATE PROCEDURE EMPTY_STATEMENT_CACHE() " +
+                        "LANGUAGE JAVA PARAMETER STYLE JAVA " +
+                        "NO SQL " +
+                        "EXTERNAL NAME 'org.apache.derby.diag.StatementCache.emptyCache'");
+            }
+            
+        };
+    }
+    
+    private static Test suiteWithSizeSet(int cacheSize)
+    {
+        // Sets up the cache size picked up by the constructor of this class
+        CACHE_SIZE = cacheSize;
+        String cs = Integer.toString(cacheSize);
+        return DatabasePropertyTestSetup.singleProperty(
+                baseSuite(cs),
+                "derby.language.statementCacheSize", cs, true);
+    }
+    
+    private static Test baseSuite(String name) {
+        TestSuite suite = new TestSuite("StatementPlanCacheTest:derby.language.statementCacheSize="
+ name);
+        suite.addTestSuite(StatementPlanCacheTest.class);
+        return suite;
+    }
+    
+    /**
+     * Prepare the statement that sees if a statement given its
+     * ID is in the cache. We hold onto it so that the statement
+     * cache can be emptied and executing this will not alter
+     * the state of cache.
+     * Empty the statement cache so that each fixture starts
+     * out with an empty cache.
+     */
+    protected void setUp() throws SQLException  {
+        cacheInPlanPS_ID = prepareStatement(
+                "SELECT COUNT(ID) FROM SYSCS_DIAG.STATEMENT_CACHE WHERE ID = ?");
+        cacheInPlanPS_TEXT = prepareStatement(
+                "SELECT COUNT(ID) FROM SYSCS_DIAG.STATEMENT_CACHE WHERE " +
+                "SCHEMANAME = ? AND SQL_TEXT = ?");
+        Statement s = createStatement();
+        s.execute("CALL EMPTY_STATEMENT_CACHE()");
+        s.close();
+       
+    }
+    
+    protected void tearDown() throws Exception {
+        cacheInPlanPS_ID.close();
+        cacheInPlanPS_TEXT.close();
+        super.tearDown();
+    }
+    
+    /**
+     * Check that when the cache size is set to zero that
+     * no caching takes place. Tests with Statement, PreparedStatement
+     * and CallableStatement.
+     */
+    public void noCachingTest() throws SQLException
+    {
+        String schema = this.getTestConfiguration().getUserName();
+        
+        String sql = "VALUES 1";
+        Statement s = createStatement();
+        s.executeQuery(sql).close();
+        s.close();
+        
+        assertFalse(sql, isPlanInCache(schema, sql));
+        
+        prepareStatement(sql).close();
+        assertFalse(sql, isPlanInCache(schema, sql));
+        
+        sql = "CALL SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(0)";
+        prepareCall(sql).close();
+        assertFalse(sql, isPlanInCache(schema, sql));
+    }
+    
+    /**
+     * Test that statements that fail to compile do not end up in the cache.
+     */
+    public void testCompileFailuresNotInCache() throws SQLException
+    {
+        String schema = this.getTestConfiguration().getUserName();
+        
+        // Parse error
+        String sql = "TO BE OR NOT TO BE";
+        assertCompileError("42X01", sql);
+        assertFalse(sql, isPlanInCache(schema, sql));
+        
+        // Valid tokens but missing elements
+        sql = "CREATE PROCEDURE BAD_PROC() EXTERNAL NAME 'lll' LANGUAGE JAVA";
+        assertCompileError("42X01", sql);
+        assertFalse(sql, isPlanInCache(schema, sql));
+        
+        // Bind error
+        sql = "SELECT * FROM NO_SUCH_TABLE_EXISTS";
+        assertCompileError("42X05", sql);
+        assertFalse(sql, isPlanInCache(schema, sql));  
+    } 
+    
+    /**
+     * Test statement caching according to the size of the cache
+     * using PreparedStatement.
+     * 
+     */
+    public void testPreparedStatementPlanCaching() throws SQLException
+    {
+        checkPreparedPlanInCache(statmentCacheSize, "VALUES &");
+    }
+    
+    /**
+     * Test statement caching according to the size of the cache
+     * using CallableStatement.
+     * 
+     */
+    public void testCallableStatementPlanCaching() throws SQLException
+    {
+        checkPreparedPlanInCache(statmentCacheSize,
+                "CALL SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(&)");
+    }    
+    
+   
+    /**
+     * Compile a number of statements based upon the sqlbase
+     * replacing the & with a number increasing from 0 to number - 1
+     * 
+     * Uses PreparedStatement unless sqlbase starts with CALL then
+     * CallableStatement is used.
+     * 
+     * Asserts that the statements appear in the cache and that recompiling
+     * it picks up the same plan.
+     */
+    private void checkPreparedPlanInCache(int number,
+            String sqlBase) throws SQLException
+    {
+        boolean isCall = sqlBase.startsWith("CALL ");
+        
+        String[] sql = new String[number];
+        String[] firstCompileID = new String[sql.length];
+        
+        for (int i = 0; i < firstCompileID.length; i++ )
+        {
+           
+            sql[i] = getSQL(sqlBase, i);
+            PreparedStatement ps = isCall ?
+                    prepareCall(sql[i]) : prepareStatement(sql[i]);
+            assertTrue(sql[i], isPlanInCache(ps));
+            firstCompileID[i] = ps.toString();
+            ps.close();
+        }
+        
+        
+        for (int i = 0; i < firstCompileID.length; i++ )
+        {
+            PreparedStatement ps = isCall ?
+                    prepareCall(sql[i]) : prepareStatement(sql[i]);
+            PreparedStatement ps2 = isCall ?
+                    prepareCall(sql[i]) : prepareStatement(sql[i]);
+            assertTrue(sql[i], isPlanInCache(ps));
+            assertEquals(sql[i], firstCompileID[i], ps.toString());
+            assertEquals(sql[i], ps.toString(), ps2.toString());
+            ps.close();
+            ps2.close();
+        }
+        
+        String schema = this.getTestConfiguration().getUserName();
+        
+        // Now check that futher statements throw out existing cache entries.
+        for (int n = firstCompileID.length; n < firstCompileID.length*2; n++)
+        {
+            // Compile a new statement, ends up in cache.
+            String sqlN = getSQL(sqlBase, n);
+            PreparedStatement ps = isCall ?
+                    prepareCall(sqlN) : prepareStatement(sqlN);
+            assertTrue(sqlN, isPlanInCache(ps));
+            
+            ps.close();
+        }
+        
+        // Can only assume some of the original statements will
+        // have been thrown out.
+        boolean thrownOut = false;
+        for (int i = 0; i < sql.length; i++)
+        {
+            if (isPlanInCache(schema, sql[i]))
+                continue;
+            
+           thrownOut = true;
+           break;             
+        }
+        
+        
+
+        assertTrue("Expect a plan to thrown out", thrownOut);
+    }
+    
+    private static String getSQL(String sqlBase, int i)
+    {
+        StringBuffer sb = new StringBuffer();
+        int rp = sqlBase.indexOf('&');
+        sb.append(sqlBase.substring(0, rp));
+        sb.append(i);
+        if (rp+1 < sqlBase.length())
+            sb.append(sqlBase.substring(rp+1));
+        
+        
+        return sb.toString();       
+    }
+    
+    private boolean isPlanInCache(PreparedStatement ps) throws SQLException {
+        cacheInPlanPS_ID.setString(1, ps.toString());
+        ResultSet rs = cacheInPlanPS_ID.executeQuery();
+        rs.next();
+        int count = rs.getInt(1);
+        rs.close();
+        assertTrue("Statement in cache multiple times ", count <= 1);
+        return count == 1;
+    }
+    private boolean isPlanInCache(String schema, String sql) throws SQLException {
+        cacheInPlanPS_TEXT.setString(1, schema);
+        cacheInPlanPS_TEXT.setString(2, sql);
+        ResultSet rs = cacheInPlanPS_TEXT.executeQuery();
+        rs.next();
+        int count = rs.getInt(1);
+        rs.close();
+        assertTrue("Statement in cache multiple times ", count <= 1);
+        cacheInPlanPS_TEXT.clearParameters();
+        return count == 1;
+    }
+}

Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/StatementPlanCacheTest.java
------------------------------------------------------------------------------
    svn:eol-style = native

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java?view=diff&rev=508186&r1=508185&r2=508186
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
Thu Feb 15 14:02:54 2007
@@ -69,6 +69,7 @@
         suite.addTest(RoutineSecurityTest.suite());
         suite.addTest(RoutineTest.suite());
         suite.addTest(SQLAuthorizationPropTest.suite());
+        suite.addTest(StatementPlanCacheTest.suite());
         suite.addTest(StreamsTest.suite());
         suite.addTest(TimeHandlingTest.suite());
         suite.addTest(VTITest.suite());

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/DatabasePropertyTestSetup.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/DatabasePropertyTestSetup.java?view=diff&rev=508186&r1=508185&r2=508186
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/DatabasePropertyTestSetup.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/DatabasePropertyTestSetup.java
Thu Feb 15 14:02:54 2007
@@ -140,10 +140,21 @@
      */
     public static Test singleProperty(Test test, String property, String value)
     {
+        return singleProperty(test, property, value, false);
+    }
+    /**
+     * Decorate a test so that it sets a single database property
+     * at setUp and resets it at tearDown. Shorthand for
+     * using DatabasePropertyTestSetup when only a single property is needed.
+     * Optinally reboots the database.
+     */
+    public static Test singleProperty(Test test, String property, String value,
+            boolean reboot)
+    {
         final Properties properties = new Properties();
         properties.setProperty(property, value);
 
-        return new DatabasePropertyTestSetup(test, properties, false);
+        return new DatabasePropertyTestSetup(test, properties, reboot);
     }    
 	
 	/**



Mime
View raw message