db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From krist...@apache.org
Subject svn commit: r1043680 - in /db/derby/code/trunk/java/testing/org/apache/derbyTesting: functionTests/tests/store/AutomaticIndexStatisticsMultiTest.java functionTests/tests/store/AutomaticIndexStatisticsTest.java junit/TestConfiguration.java
Date Wed, 08 Dec 2010 21:15:10 GMT
Author: kristwaa
Date: Wed Dec  8 21:15:10 2010
New Revision: 1043680

URL: http://svn.apache.org/viewvc?rev=1043680&view=rev
Log:
DERBY-4935: Add initial istat tests

Added initial tests for the automatic index statistics feature.
Since the feature hasn't been committed yet, the tests haven't been wired
into suites.All.

Patch file: derby-4935-1a-initial_tests.diff


Added:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/store/AutomaticIndexStatisticsMultiTest.java
  (with props)
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/store/AutomaticIndexStatisticsTest.java
  (with props)
Modified:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/TestConfiguration.java

Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/store/AutomaticIndexStatisticsMultiTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/store/AutomaticIndexStatisticsMultiTest.java?rev=1043680&view=auto
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/store/AutomaticIndexStatisticsMultiTest.java
(added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/store/AutomaticIndexStatisticsMultiTest.java
Wed Dec  8 21:15:10 2010
@@ -0,0 +1,287 @@
+/*
+
+   Derby - Class org.apache.derbyTesting.functionTests.tests.store.AutomaticIndexStatisticsMultiTest
+
+   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.store;
+
+import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.util.Random;
+
+import junit.framework.Test;
+import junit.framework.TestSuite;
+
+import org.apache.derbyTesting.junit.BaseJDBCTestCase;
+import org.apache.derbyTesting.junit.IndexStatsUtil;
+import org.apache.derbyTesting.junit.IndexStatsUtil.IdxStats;
+
+/**
+ * Tests that the update triggering code can handle a high amount of requests,
+ * both when the data dictionary is operating in cached mode and in DDL mode.
+ */
+public class AutomaticIndexStatisticsMultiTest
+    extends BaseJDBCTestCase {
+
+    private static final String TAB = "MTSEL";
+    private static final int _100K = 100000;
+
+    public AutomaticIndexStatisticsMultiTest(String name) {
+        super(name);
+    }
+
+    public void testMTSelect()
+            throws Exception {
+        prepareTable(true);
+
+        // Create threads compiling a select query for the table
+        int threadCount = 5;
+        long runTime = 10000;
+        MTCompileThread[] compileThreads = new MTCompileThread[threadCount];
+        for (int i=0; i < threadCount; i++) {
+            compileThreads[i] =
+                    new MTCompileThread(openDefaultConnection(), runTime);
+        }
+
+        // Start the threads and let them run for a while.
+        Thread[] threads = new Thread[threadCount];
+        for (int i=0; i < threadCount; i++) {
+            threads[i] = new Thread(compileThreads[i]);
+            threads[i].start();
+        }
+
+        // Wait for the threads to finish.
+        for (int i=0; i < threadCount; i++) {
+            threads[i].join(600*1000); // Time out after 600 seconds.
+        }
+
+        int total = 0;
+        int totalError = 0;
+        for (int i=0; i < threadCount; i++) {
+            int count = compileThreads[i].getCount();
+            int errors = compileThreads[i].getErrorCount();
+            total += count;
+            totalError += errors;
+        }
+        println("TOTAL = " + total + " (of which " + totalError + " errors)");
+        assertEquals(0, totalError);
+
+        verifyStatistics();
+        // Shutdown database to log daemon stats (if logging is enabled).
+        getTestConfiguration().shutdownDatabase();
+    }
+
+    public void testMTSelectWithDDL()
+            throws Exception {
+        prepareTable(true);
+
+        // Create threads compiling a select query for the table
+        int threadCount = 5;
+        long runTime = 10000;
+        MTCompileThread[] compileThreads = new MTCompileThread[threadCount];
+        for (int i=0; i < threadCount; i++) {
+            compileThreads[i] =
+                    new MTCompileThread(openDefaultConnection(), runTime);
+        }
+
+        // Start the drop/create thread.
+        MTCreateDropThread createThread = new MTCreateDropThread(
+                openDefaultConnection(), runTime);
+        Thread[] threads = new Thread[threadCount +1];
+        threads[0] = new Thread(createThread);
+        threads[0].start();
+
+        // Start the threads and let them run for a while.
+        for (int i=0; i < threadCount; i++) {
+            threads[i+1] = new Thread(compileThreads[i]);
+            threads[i+1].start();
+        }
+
+        // Wait for the threads to finish.
+        for (int i=0; i < threadCount; i++) {
+            threads[i].join(600*1000); // Time out after 600 seconds.
+        }
+
+        int total = 0;
+        int totalError = 0;
+        for (int i=0; i < threadCount; i++) {
+            int count = compileThreads[i].getCount();
+            int errors = compileThreads[i].getErrorCount();
+            total += count;
+            totalError += errors;
+        }
+        println("TOTAL = " + total + " (of which " + totalError +
+                " errors) CREATES = " + createThread.getCreates());
+        assertEquals(0, totalError);
+
+        verifyStatistics();
+        // Shutdown database to log daemon stats (if logging is enabled).
+        getTestConfiguration().shutdownDatabase();
+    }
+
+    private void verifyStatistics()
+            throws SQLException {
+        IndexStatsUtil stats = new IndexStatsUtil(getConnection());
+        IdxStats[] myStats = stats.getStatsTable(TAB, 2);
+        for (int i=0; i < myStats.length; i++) {
+            IdxStats s = myStats[i];
+            assertEquals(_100K, s.rows);
+            switch (s.lcols) {
+                case 1:
+                    assertEquals(10, s.card);
+                    break;
+                case 2:
+                    assertEquals(_100K, s.card);
+                    break;
+                default:
+                    fail("unexpected number of leading columns: " + s.lcols);
+            }
+        }
+    }
+
+    private void prepareTable(boolean dropIfExists)
+            throws SQLException {
+        Statement stmt = createStatement();
+        ResultSet rs = getConnection().getMetaData().getTables(
+                null, null, TAB, null);
+        if (rs.next()) {
+            assertFalse(rs.next());
+            rs.close();
+            if (dropIfExists) {
+                println("table " + TAB + " already exists, dropping");
+                stmt.executeUpdate("drop table " + TAB);
+            } else {
+                println("table " + TAB + " already exists, reusing");
+                return;
+            }
+        } else {
+            rs.close();
+        }
+
+        stmt.executeUpdate("create table " + TAB + " (val1 int, val2 int)");
+        stmt.executeUpdate("create index " +
+                "mtsel_idx on " + TAB + " (val1, val2)");
+
+        setAutoCommit(false);
+        PreparedStatement ps = prepareStatement(
+                "insert into " + TAB + " values (?,?)");
+        // Insert blocks of 10000 rows.
+        int blockCount =    10;
+        int blockSize =  10000;
+        for (int i=0; i < blockCount; i++) {
+            ps.setInt(1, i);
+            for (int j=0; j < blockSize; j++) {
+                ps.setInt(2, j);
+                ps.addBatch();
+            }
+            ps.executeBatch();
+            commit();
+            println("inserted block " + (i+1) + "/" + blockCount);
+        }
+        setAutoCommit(true);
+    }
+
+    public static Test suite() {
+        return new TestSuite(AutomaticIndexStatisticsMultiTest.class);
+    }
+
+    private static class MTCompileThread
+            implements Runnable {
+
+        private final Random rand = new Random();
+        private final Connection con;
+        private final long runTime;
+        private int count;
+        private int errorCount;
+
+        public MTCompileThread(Connection con, long runTime)
+                throws SQLException {
+            this.con = con;
+            this.runTime = runTime;
+        }
+
+        public void run() {
+            final long started = System.currentTimeMillis();
+            int counter = 0;
+            while (System.currentTimeMillis() - started < runTime) {
+                try {
+                    con.prepareStatement("select * from mtsel where " +
+                                (++counter) + " = " + counter + " AND val2 = " +
+                                (1 + rand.nextInt(10)));
+                } catch (SQLException sqle) {
+                    errorCount++;
+                }
+                count++;
+            }
+        }
+
+        public int getCount() {
+            return count;
+        }
+
+        public int getErrorCount() {
+            return errorCount;
+        }
+    }
+
+    private static class MTCreateDropThread
+            implements Runnable {
+
+        private final Connection con;
+        private final long runTime;
+        private long creates;
+
+        public MTCreateDropThread(Connection con, long runTime)
+                throws SQLException {
+            this.con = con;
+            this.runTime = runTime;
+        }
+
+        public void run() {
+            final long started = System.currentTimeMillis();
+            try {
+                ResultSet rs = con.getMetaData().getTables(
+                        null, null, "TMPTABLE", null);
+                boolean lastWasCreate = rs.next();
+                rs.close();
+                Statement stmt = con.createStatement();
+                while (System.currentTimeMillis() - started < runTime) {
+                    if (lastWasCreate) {
+                        // Drop the table
+                        stmt.executeUpdate("drop table TMPTABLE");
+                    } else {
+                        // Create the table
+                        stmt.executeUpdate("create table TMPTABLE("
+                                + "i int primary key, v varchar(30), b blob)");
+                        creates++;
+                    }
+                    lastWasCreate = !lastWasCreate;
+                }
+            } catch (SQLException sqle) {
+                fail("create drop thread crashed: " + sqle.getMessage());
+            }
+        }
+
+        public long getCreates() {
+            return creates;
+        }
+    }
+}

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

Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/store/AutomaticIndexStatisticsTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/store/AutomaticIndexStatisticsTest.java?rev=1043680&view=auto
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/store/AutomaticIndexStatisticsTest.java
(added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/store/AutomaticIndexStatisticsTest.java
Wed Dec  8 21:15:10 2010
@@ -0,0 +1,701 @@
+/*
+
+   Derby - Class org.apache.derbyTesting.functionTests.tests.store.AutomaticIndexStatisticsTest
+
+   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.store;
+
+import java.io.File;
+import java.io.IOException;
+import java.security.AccessController;
+import java.security.PrivilegedAction;
+import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.sql.Timestamp;
+import java.util.Date;
+
+import javax.sql.DataSource;
+
+import junit.framework.Test;
+import junit.framework.TestSuite;
+
+import org.apache.derbyTesting.functionTests.util.PrivilegedFileOpsForTests;
+import org.apache.derbyTesting.junit.BaseJDBCTestCase;
+import org.apache.derbyTesting.junit.IndexStatsUtil;
+import org.apache.derbyTesting.junit.IndexStatsUtil.IdxStats;
+import org.apache.derbyTesting.junit.JDBC;
+import org.apache.derbyTesting.junit.JDBCDataSource;
+import org.apache.derbyTesting.junit.TestConfiguration;
+import org.apache.derbyTesting.junit.Utilities;
+
+/**
+ * Tests various aspects of the automatic index statistics update feature,
+ * which is designed to run as a background task.
+ * <p>
+ * Some of the tests ensure that the daemon is able to recover after
+ * encountering various kinds of "expected errors", for instance if the table
+ * is dropped while being scanned by the daemon.
+ */
+public class AutomaticIndexStatisticsTest
+    extends BaseJDBCTestCase {
+
+    private static final String MASTERDB = "masterDb";
+    private static final String BIG_TABLE = "BIG_TABLE";
+    private static final long DEFAULT_TIMEOUT = 20*1000;
+
+    private static final String[] TYPES = new String[] {"TABLE", "VIEW"};
+
+    private static boolean dbCreated;
+    // Change this if running test cases in parallel...
+    private static IndexStatsUtil stats;
+
+    public AutomaticIndexStatisticsTest(String name) {
+
+        super(name);
+    }
+
+    public static Test suite() {
+        TestSuite suite = new TestSuite(AutomaticIndexStatisticsTest.class);
+        return TestConfiguration.additionalDatabaseDecorator(suite, MASTERDB);
+    }
+
+    /** Initialize the default statistics helper object. */
+    public void setUp()
+            throws SQLException {
+        if (stats != null) {
+            stats.release();
+        }
+        stats = new IndexStatsUtil(openDefaultConnection(), DEFAULT_TIMEOUT);
+    }
+
+    /** Release the default statistics helper object. */
+    public void tearDown()
+            throws Exception {
+        if (stats != null) {
+            stats.release();
+        }
+        stats = null;
+        super.tearDown();
+    }
+
+    /**
+     * Make sure stats are created when the table grows, and that the database
+     * can be deleted after automated stats create/update -
+     * that is verify that there are no open file handles left behind in the
+     * daemon.
+     */
+    public void testStatsCreatedOnGrowthThenDeleteDb()
+            throws SQLException {
+        String TAB = "TEST_GROWTH_EMPTY";
+        createAndInsertSimple(TAB, 300);
+        // This should trigger creation of statistics.
+        prepareStatement("select * from " + TAB + " where id = ?");
+
+        // Get statistics
+        IdxStats[] myStats = stats.getStatsTable(TAB, 1);
+        assertEquals(1, myStats.length);
+        assertTrue(myStats[0].rows == 300);
+        assertTrue(myStats[0].card == 300);
+
+        // Shutdown database and try to delete it.
+        TestConfiguration.getCurrent().shutdownDatabase();
+        assertDirectoryDeleted(constructDbPath(
+                getTestConfiguration().getDefaultDatabaseName()));
+    }
+
+    /** Make sure stats are updated when the table grows. */
+    public void testStatsUpdatedOnGrowth()
+            throws SQLException {
+        String TAB = "TEST_GROWTH";
+        createAndInsertSimple(TAB, 10000);
+        // This should trigger creation of statistics.
+        prepareStatement("select * from " + TAB + " where id = ?");
+
+        // Get statistics
+        IdxStats[] statsPre = stats.getStatsTable(TAB, 1);
+        assertEquals(1, statsPre.length);
+
+        // Insert more rows.
+        setAutoCommit(false);
+        insertSimple(TAB, 50000, 10000);
+        // Force a checkpoint to update the row estimates.
+        forceRowCountEstimateUpdate(TAB);
+        prepareStatement("select * from " + TAB + " where 1=1");
+        IdxStats[] statsPost = getFilteredTableStats(TAB, 1, statsPre);
+        assertEquals(1, statsPost.length);
+        assertFalse(statsPre[0].equals(statsPost[0]));
+        assertFalse(statsPre[0].after(statsPost[0]));
+        // Insert a few more rows, should not trigger a new update
+        insertSimple(TAB, 1000, 60000);
+        forceRowCountEstimateUpdate(TAB);
+        prepareStatement("select * from " + TAB + " where 2=2");
+        // Manual wait to see if the existing stats are replaced - they should
+        // not be
+        Utilities.sleep(1500);
+        IdxStats[] statsPost1 = stats.getStatsTable(TAB, 1);
+        assertTrue(statsPost[0].equals(statsPost1[0]));
+        assertFalse(statsPost1[0].after(statsPost[0]));
+    }
+
+    /**
+     * Shuts down database while the daemon is scanning a table, and then
+     * makes sure the database directory can be deleted.
+     */
+    public void testShutdownWhileScanningThenDelete()
+            throws IOException, SQLException {
+        // Need a big enough table to get the timing right.
+        String db = "singleUse/copyShutdown";
+        copyDb(db);
+        DataSource ds = JDBCDataSource.getDataSource();
+        JDBCDataSource.setBeanProperty(ds, "databaseName", db);
+        Connection con = ds.getConnection();
+        String TAB = BIG_TABLE;
+
+        // Trigger statistics creation.
+        PreparedStatement ps = con.prepareStatement(
+                "select * from " + TAB + " where id = ?");
+        ps.close();
+
+        // Wait to make sure the scan starts.
+        Utilities.sleep(150);
+        // Now shut down the database.
+        JDBCDataSource.shutdownDatabase(ds);
+        assertDirectoryDeleted(constructDbPath(db));
+    }
+
+    /**
+     * Drops table while the daemon is scanning it, then triggers update of
+     * statistics of a different table, and finally shuts down and deletes the
+     * database.
+     */
+    public void testDropWhileScanningThenDelete()
+            throws IOException, SQLException {
+        // Need a big enough table to get the timing right.
+        String TAB1 = BIG_TABLE;
+        String TAB2 = "SECONDARY_TABLE";
+        String db = "singleUse/copyDrop";
+        copyDb(db);
+        DataSource ds = JDBCDataSource.getDataSource();
+        JDBCDataSource.setBeanProperty(ds, "databaseName", db);
+        Connection con = ds.getConnection();
+        // Create secondary table.
+        createAndInsertSimple(con, TAB2, 20000);
+
+        // Trigger statistics creation.
+        PreparedStatement ps = con.prepareStatement(
+                "select * from " + TAB1 + " where id = ?");
+        ps.close();
+        // Wait to make sure the scan starts.
+        Utilities.sleep(150);
+        println("dropping table...");
+        Statement stmt = con.createStatement();
+        stmt.executeUpdate("drop table " + TAB1);
+        stmt.close();
+
+        // Trigger stats update on secondary table.
+        IndexStatsUtil myStats =
+                new IndexStatsUtil(ds.getConnection(), DEFAULT_TIMEOUT);
+        myStats.assertNoStatsTable(TAB2);
+        ps = con.prepareStatement("select * from " + TAB2 + " where id = ?");
+        myStats.assertTableStats(TAB2, 1);
+        myStats.release();
+
+        // Shutdown, then delete database directory.
+        JDBCDataSource.shutdownDatabase(ds);
+        assertDirectoryDeleted(constructDbPath(db));
+    }
+
+    /**
+     * Tests that compressing the table while scanned makes the daemon fail
+     * gracefully, and that the daemon can do other work afterwords.
+     */
+    public void testCompressWhileScanning()
+            throws IOException, SQLException {
+        String TAB1 = BIG_TABLE;
+        String TAB2 = "SECONDARY_TABLE";
+        String db = "singleUse/copyCompress";
+        copyDb(db);
+        DataSource ds = JDBCDataSource.getDataSource();
+        JDBCDataSource.setBeanProperty(ds, "databaseName", db);
+        Connection con = ds.getConnection();
+        // Create secondary table.
+        createAndInsertSimple(con, TAB2, 20000);
+
+        // Trigger statistics creation.
+        PreparedStatement ps = con.prepareStatement(
+                "select * from " + TAB1 + " where id = ?");
+        ps.close();
+        // Wait to make sure the scan starts.
+        Utilities.sleep(150);
+        println("compressing table...");
+        Statement stmt = con.createStatement();
+        stmt.executeUpdate("call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', '" +
+                TAB1 + "', 0)");
+        stmt.close();
+        // There should still be a statistics object written during the
+        // compress operation.
+        IndexStatsUtil myStats =
+                new IndexStatsUtil(ds.getConnection(), DEFAULT_TIMEOUT);
+        myStats.assertTableStats(TAB1, 1);
+
+        // Trigger stats update on secondary table, make sure the daemon can
+        // still process work.
+        myStats.assertNoStatsTable(TAB2);
+        ps = con.prepareStatement("select * from " + TAB2 + " where id = ?");
+        myStats.assertTableStats(TAB2, 1);
+        myStats.release();
+    }
+
+    /**
+     * Tests that the statistics computed are correct.
+     *
+     * @throws SQLException if something goes wrong
+     */
+    public void testStatisticsCorrectness()
+            throws SQLException {
+        // Create table.
+        String TAB = "STAT_CORR";
+        dropTable(TAB);
+        Statement stmt = createStatement();
+        stmt.executeUpdate("create table " + TAB +
+                " (id1 int, id2 int, id3 int, val int, " +
+                "primary key (id1, id2, id3))");
+        stats.assertNoStatsTable(TAB);
+        // Insert rows.
+        PreparedStatement ps = prepareStatement("insert into " + TAB +
+                " values (?,?,?,?)");
+        // # rows = 100 * 50 * 10 = 50'000
+        setAutoCommit(false);
+        final int rows = 100*50*10;
+        for (int i=1; i <= 100; i++) {
+            ps.setInt(1, i);
+            for (int j=1; j <= 50; j++) {
+                ps.setInt(2, j);
+                for (int k=1; k <= 10; k++) {
+                    ps.setInt(3, k);
+                    ps.setInt(4, i*j*k % 750);
+                    ps.executeUpdate();
+                }
+            }
+        }
+        commit();
+        setAutoCommit(true);
+
+        // Select to trigger statistics generation.
+        forceRowCountEstimateUpdate(TAB);
+        JDBC.assertDrainResults(
+                prepareStatement("select * from " + TAB + " where id1 = 10")
+                .executeQuery());
+
+        // We expect three stats objects for the single index; one per
+        // leading columns combination (c1, c1-c2, and c1-c2-c3).
+        IdxStats statsObj[] = stats.getStatsTable(TAB, 3);
+        assertEquals(3, statsObj.length);
+
+        Timestamp now = new Timestamp(new Date().getTime());
+        for (int i=0; i < statsObj.length; i++) {
+            IdxStats s = statsObj[i];
+            assertEquals(rows, s.rows);
+            assertTrue(s.created.before(now));
+            switch (s.lcols) {
+                case 1:
+                    assertEquals(100, s.card);
+                    break;
+                case 2:
+                    assertEquals(5000, s.card);
+                    break;
+                case 3:
+                    assertEquals(50000, s.card);
+                    break;
+            default:
+                fail("unexpected number of leading columns: " + s.lcols);
+            }
+        }
+
+        // Now create a second index in the opposite order, check stats.
+        stmt.executeUpdate("create index IDXREV on " + TAB + "(id3, id2, id1)");
+        statsObj = stats.getStatsIndex("IDXREV", 3);
+        assertEquals(3, statsObj.length);
+        Timestamp earlier = now;
+        now = new Timestamp(new Date().getTime());
+        for (int i=0; i < statsObj.length; i++) {
+            IdxStats s = statsObj[i];
+            assertEquals(rows, s.rows);
+            assertTrue("current stats created " + s.created +
+                    ", previous stats created " + earlier,
+                    s.created.after(earlier));
+            assertTrue(s.created.before(now));
+            switch (s.lcols) {
+                case 1:
+                    assertEquals(10, s.card);
+                    break;
+                case 2:
+                    assertEquals(500, s.card);
+                    break;
+                case 3:
+                    assertEquals(50000, s.card);
+                    break;
+            default:
+                fail("unexpected number of leading columns: " + s.lcols);
+            }
+        }
+
+        // Finally, create a unique index on the val column.
+        stmt.executeUpdate("create index IDXVAL on " + TAB + "(val)");
+        ResultSet rs = stmt.executeQuery(
+                "select val from " + TAB + " order by val");
+        // Calculate number of unique values.
+        int uniqueVals = 0;
+        int prev = -1;
+        while (rs.next()) {
+            int curr = rs.getInt(1);
+            if (curr != prev) {
+                uniqueVals++;
+                prev = curr;
+            }
+        }
+        rs.close();
+        // Get stats and check the associated values.
+        IdxStats[] valStat = stats.getStatsIndex("IDXVAL", 1);
+        assertEquals(1, valStat.length);
+        assertEquals(uniqueVals, valStat[0].card);
+        assertEquals(rows, valStat[0].rows);
+    }
+
+    public void testSelectFromSimpleView()
+            throws SQLException {
+        // First create a table with a few columns.
+        String table = "VIEW_BASE_TABLE";
+        String view = "MY_VIEW";
+
+        dropIfExists(getConnection(), view);
+        dropIfExists(getConnection(), table);
+        Statement s = createStatement();
+        s.execute("create table " + table + " (" +
+                "id int primary key, col1 int, col2 int)");
+        s.execute("create index COL2_IDX on " + table + "(col2)");
+        PreparedStatement ps = prepareStatement("insert into " + table +
+                " values (?,?,?)");
+        setAutoCommit(false);
+        for (int i=0; i < 30000; i++) {
+            ps.setInt(1, i);
+            ps.setInt(2, i % 15);
+            ps.setInt(3, i % 25);
+            ps.executeUpdate();
+            // Commit periodically
+            if (i % 5000 == 0) {
+                commit();
+            }
+        }
+        commit();
+        setAutoCommit(true);
+        ps.close();
+
+        // Create the view.
+        s.execute("create view " + view + "(vcol_1, vcol2) " +
+                "AS select id, col2 from " + table);
+
+        // Select from the view, using index.
+        stats.assertNoStatsTable(table);
+        ps = prepareStatement("select * from " + view + " where vcol2 = 7");
+        stats.assertNoStatsTable(table);
+        // Trigger update of the base table.
+        ps = prepareStatement("select * from " + table + " where col2 = 7");
+        stats.assertTableStats(table, 2);
+    }
+
+    // Utility methods
+
+    /**
+     * Creates a copy of the master db.
+     *
+     * @param newDbName name of the copy
+     * @throws IOException if something goes wrong
+     * @throws SQLException if something goes wrong
+     */
+    private void copyDb(String newDbName)
+            throws IOException, SQLException {
+        if (!dbCreated) {
+            createMasterDb();
+        }
+
+        File master = constructDbPath(
+                TestConfiguration.getCurrent().getPhysicalDatabaseName(
+                    MASTERDB));
+        final File dest = constructDbPath(newDbName);
+
+        // Make sure the directory containing the database directory exists.
+        // We expect the security manager to stop us from creating directories
+        // where we aren't supposed to.
+        if (!PrivilegedFileOpsForTests.exists(dest.getParentFile())) {
+            AccessController.doPrivileged(new PrivilegedAction() {
+
+                public Object run() {
+                    assertTrue(dest.getParentFile().mkdirs());
+                    return null;
+                }
+            });
+        }
+
+        PrivilegedFileOpsForTests.copy(master, dest);
+    }
+
+    /**
+     * Creates the default/master db with a larger number of rows.
+     *
+     * @throws SQLException if creating the database fails
+     */
+    private void createMasterDb()
+            throws SQLException {
+        long start = System.currentTimeMillis();
+        String table = BIG_TABLE;
+        int rows = 1*1000*1000;
+        DataSource ds1 = JDBCDataSource.getDataSourceLogical(MASTERDB);
+        JDBCDataSource.setBeanProperty(ds1, "createDatabase", "create");
+        Connection con = ds1.getConnection();
+        // Check if the table exists, if so, drop it.
+        dropIfExists(con, table);
+        // Create the table.
+        Statement stmt = con.createStatement();
+        stmt.executeUpdate("create table " + table + "(id int primary key)");
+        stmt.close();
+
+        // Insert data
+        con.setAutoCommit(false);
+        PreparedStatement ps = con.prepareStatement("insert into " + table +
+                " values ?");
+        for (int i=0; i < rows; i++) {
+            ps.setInt(1, i);
+            ps.addBatch();
+            if (i % 5000 == 0) {
+                ps.executeBatch();
+                con.commit();
+            }
+        }
+        ps.executeBatch();
+        con.commit();
+        con.close();
+        println("created master db with " + rows + " rows in " +
+                ((System.currentTimeMillis() - start) / 1000) + " seconds");
+
+        // Shut down the master db, we will copy it later.
+        JDBCDataSource.shutdownDatabase(
+                JDBCDataSource.getDataSourceLogical(MASTERDB));
+
+        dbCreated = true;
+
+    }
+
+    /**
+     * Forces Derby to update the row count estimate by doing a full table
+     * scan and then invoking a checkpoint.
+     *
+     * @param table target table
+     * @throws SQLException if something goes wrong
+     */
+    private void forceRowCountEstimateUpdate(String table)
+            throws SQLException {
+        Statement stmt = createStatement();
+        JDBC.assertDrainResults(
+                stmt.executeQuery("select count(*) from " + table));
+        stmt.execute("call SYSCS_UTIL.SYSCS_CHECKPOINT_DATABASE()");
+        stmt.close();
+    }
+
+    /**
+     * Constructs the path to the database base directory.
+     *
+     * @param relDbDirName the database name (relative)
+     * @return The path to the database.
+     */
+    private File constructDbPath(String relDbDirName) {
+        // Example:
+        //     "singleUse/readOnly" -> "<derby.system.home>/singleUse/readOnly"
+        File f = new File(getSystemProperty("derby.system.home"));
+        return new File(f, relDbDirName);
+    }
+
+    /**
+     * Default method to create and populate a simple test table.
+     * <p>
+     * The table consists of a single integer column, which is also the primary
+     * key of the table.
+     *
+     * @param table target table
+     * @param rows number of rows to insert
+     * @throws SQLException if creating/populating the table fails
+     */
+    private void createAndInsertSimple(String table, int rows)
+            throws SQLException {
+        createAndInsertSimple(null, table, rows);
+    }
+
+    /**
+     * Default method to create and populate a simple test table.
+     * <p>
+     * The table consists of a single integer column, which is also the primary
+     * key of the table.
+     *
+     * @param con the connection to use (may be {@code null}, in which case
+     *      the default connection will be used)
+     * @param table target table
+     * @param rows number of rows to insert
+     * @throws SQLException if creating/populating the table fails
+     */
+    private void createAndInsertSimple(Connection con, String table, int rows)
+            throws SQLException {
+        Statement s;
+        IndexStatsUtil myStats;
+        if (con == null) {
+            con = getConnection();
+            s = createStatement();
+            myStats = stats;
+        } else {
+            s = con.createStatement();
+            myStats = new IndexStatsUtil(con);
+        }
+        // See if the table exists, and if so, drop it.
+        dropIfExists(con, table);
+        // Create table.
+        s.executeUpdate("create table " + table + "(id int primary key)");
+
+        myStats.assertNoStatsTable(table);
+
+        // Insert data
+        long start = System.currentTimeMillis();
+        println("created " + table + ", inserting " + rows + " rows");
+        insertSimple(con, table, rows, 0);
+        println("completed in " + (System.currentTimeMillis() - start) + " ms");
+    }
+
+    /**
+     * Inserts the specified number of rows into the table, using an increasing
+     * integer as the value.
+     *
+     * @param table target table
+     * @param rows number of rows
+     * @param start starting value for the first inserted row
+     * @throws SQLException if something goes wrong
+     */
+    private void insertSimple(String table, int rows, int start)
+            throws SQLException {
+        // Use the default connection.
+        insertSimple(getConnection(), table, rows, start);
+    }
+
+    /**
+     * Inserts the specified number of rows into the table, using an increasing
+     * integer as the value.
+     *
+     * @param con the connection to use
+     * @param table target table
+     * @param rows number of rows
+     * @param start starting value for the first inserted row
+     * @throws SQLException if something goes wrong
+     * @throws NullPointerException if {@code con} is {@code null}
+     */
+    private void insertSimple(Connection con, String table, int rows, int start)
+            throws SQLException {
+        PreparedStatement ps = con.prepareStatement(
+                                    "insert into " + table + " values ?");
+        boolean autoCommit = con.getAutoCommit();
+        con.setAutoCommit(false);
+        for (int i=start; i < start+rows; i++) {
+            ps.setInt(1, i);
+            ps.addBatch();
+            if (i % 5000 == 0) {
+                ps.executeBatch();
+                con.commit();
+            }
+        }
+        ps.executeBatch();
+        con.commit();
+        con.setAutoCommit(autoCommit);
+    }
+
+    /**
+     * Obtains the statistics for all indexes associated with the given table in
+     * the default database.
+     *
+     * @param table base table
+     * @param expectedCount number of statistics objects to obtain
+     * @param oldStats statistics objects to ignore
+     * @return An array with the statistics objects obtained.
+     * @throws SQLException if something goes wrong
+     * @throws AssertionError if the number of statistics objects expected
+     *      can't be obtained within the time limit
+     *      ({@linkplain #DEFAULT_TIMEOUT})
+     */
+    private IdxStats[] getFilteredTableStats(String table, int expectedCount,
+                                             IdxStats[] oldStats)
+            throws SQLException {
+        long start = System.currentTimeMillis();
+        while (System.currentTimeMillis() - start < DEFAULT_TIMEOUT) {
+            IdxStats[] ret = stats.getStatsTable(table, expectedCount);
+            boolean doReturn = true;
+            // Check if we have new stats (if filtering is asked for).
+            if (oldStats != null) {
+                for (int i=0; i < ret.length; i++) {
+                    for (int j=0; j < oldStats.length; j++) {
+                        if (ret[i].equals(oldStats[j])) {
+                            doReturn = false;
+                            break;
+                        }
+                    }
+                }
+            }
+            if (doReturn) {
+                return ret;
+            }
+            Utilities.sleep(250);
+        }
+        fail("getting stats for table " + table + " timed out (#expected=" +
+                expectedCount + ", #oldStats=" +
+                (oldStats == null ? 0 : oldStats.length) + ")");
+        // Silence the compiler.
+        return null;
+    }
+
+    // Static utility methods
+
+    /**
+     * Drops the specified entity if it exists.
+     *
+     * @param con connection to the database
+     * @param entity the entity to drop (i.e. table or view)
+     */
+    private static void dropIfExists(Connection con, String entity)
+            throws SQLException {
+        ResultSet tables = con.getMetaData().getTables(
+                null, null, entity, TYPES);
+        while (tables.next()) {
+            String type = tables.getString(4);
+            if (type.equals("TABLE")) {
+                dropTable(con, entity);
+            } else if (type.equals("VIEW")) {
+                con.createStatement().executeUpdate("drop view " + entity);
+            } else {
+                fail("entity " + entity + " of unsupported type: " + type);
+            }
+        }
+        tables.close();
+    }
+}

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

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/TestConfiguration.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/TestConfiguration.java?rev=1043680&r1=1043679&r2=1043680&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/TestConfiguration.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/TestConfiguration.java
Wed Dec  8 21:15:10 2010
@@ -1389,7 +1389,7 @@ public final class TestConfiguration {
      * 
      * @return Physical name of the database.
      */
-    String getPhysicalDatabaseName(String logicalName) {
+    public String getPhysicalDatabaseName(String logicalName) {
         return (String) logicalDbMapping.get(logicalName);
     }
 



Mime
View raw message