| /* |
| |
| 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 org.apache.derbyTesting.functionTests.util.PrivilegedFileOpsForTests; |
| import org.apache.derbyTesting.junit.BaseJDBCTestCase; |
| import org.apache.derbyTesting.junit.BaseTestSuite; |
| import org.apache.derbyTesting.junit.CleanDatabaseTestSetup; |
| 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.TimeZoneTestSetup; |
| 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 { |
| |
| // used also by Derby5582AutomaticIndexStatisticsTest so just protected |
| protected 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() { |
| Test test = new BaseTestSuite(AutomaticIndexStatisticsTest.class); |
| test = new CleanDatabaseTestSetup(test); |
| test = TestConfiguration.additionalDatabaseDecorator(test, MASTERDB); |
| |
| // DERBY-5964: The test needs to check the timestamp stored in |
| // SYSSTATISTICS, which is in the local timezone. Since those |
| // timestamps may be ambiguous around the transition to or from DST, |
| // run this test in a timezone that doesn't observe DST. The |
| // TimeZoneTestSetup can probably be removed once DERBY-5974 is fixed. |
| return new TimeZoneTestSetup(test, "GMT"); |
| } |
| |
| /** 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 db = "singleUse/newCleanDb"; |
| DataSource ds = JDBCDataSource.getDataSource(); |
| JDBCDataSource.setBeanProperty(ds, "databaseName", db); |
| JDBCDataSource.setBeanProperty(ds, "createDatabase", "create"); |
| Connection con = ds.getConnection(); |
| String TAB = "TEST_GROWTH_EMPTY"; |
| createAndInsertSimple(con, TAB, 300); |
| // This should trigger creation of statistics. |
| PreparedStatement ps = con.prepareStatement( |
| "select * from " + TAB + " where id = ?"); |
| ps.close(); |
| |
| // Get statistics for the non-unique index. |
| IdxStats[] myStats = new IndexStatsUtil( |
| ds.getConnection(), DEFAULT_TIMEOUT).getStatsTable(TAB, 1); |
| assertEquals(1, myStats.length); |
| assertTrue(myStats[0].rows == 300); |
| |
| // Shutdown database and try to delete it. |
| JDBCDataSource.shutdownDatabase(ds); |
| assertDirectoryDeleted(constructDbPath(db)); |
| } |
| |
| /** 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); |
| 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 afterwards. |
| */ |
| 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); |
| con.prepareStatement("select * from " + TAB2 + " where id = ?"); |
| myStats.assertTableStats(TAB2, 1); |
| myStats.release(); |
| |
| // Shutdown database and try to delete it. |
| JDBCDataSource.shutdownDatabase(ds); |
| assertDirectoryDeleted(constructDbPath(db)); |
| } |
| |
| /** |
| * 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); |
| |
| // DERBY-6144 |
| // Stats cannot have been created after the current time (future). |
| assertFalse( |
| "expected stat created in past:now = " + now + |
| ";s.created = " + s.created, |
| s.created.compareTo(now) > 0); |
| |
| 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)); |
| // Stats cannot have been created after the current time (future). |
| assertFalse( |
| "expected stat created in past:now = " + now + |
| ";s.created = " + s.created, |
| s.created.compareTo(now) > 0); |
| |
| 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 non-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); |
| prepareStatement("select * from " + view + " where vcol2 = 7"); |
| stats.assertNoStatsTable(table); |
| // Trigger update of the base table. |
| prepareStatement("select * from " + table + " where col2 = 7"); |
| stats.assertTableStats(table, 1); |
| } |
| |
| /** |
| * Verifies that queries on a table with single-column unique indexes only |
| * don't trigger a statistics update with the istat daemon. |
| */ |
| public void testNoUpdateTriggeredBySingleColumnUniqueIndex() |
| throws SQLException { |
| // Create table. |
| String TAB = "STAT_SCUI"; |
| dropTable(TAB); |
| Statement stmt = createStatement(); |
| stmt.executeUpdate("create table " + TAB + |
| " (id int primary key, val int unique not null)"); |
| stats.assertNoStatsTable(TAB); |
| PreparedStatement ps = prepareStatement( |
| "insert into " + TAB + " values (?,?)"); |
| setAutoCommit(false); |
| for (int i=0; i < 2000; i++) { |
| ps.setInt(1, i); |
| ps.setInt(2, i); |
| ps.executeUpdate(); |
| } |
| commit(); |
| // The queries below would trigger a stats update in earlier releases. |
| PreparedStatement psSel1 = prepareStatement( |
| "select id from " + TAB + " where id = ?"); |
| psSel1.setInt(1, 98); |
| JDBC.assertSingleValueResultSet(psSel1.executeQuery(), "98"); |
| PreparedStatement psSel2 = prepareStatement( |
| "select val from " + TAB + " where val = ?"); |
| psSel2.setInt(1, 1573); |
| JDBC.assertSingleValueResultSet(psSel2.executeQuery(), "1573"); |
| Utilities.sleep(100); |
| stats.assertNoStatsTable(TAB); |
| |
| // Try again after inserting more data. |
| for (int i=2000; i < 4000; i++) { |
| ps.setInt(1, i); |
| ps.setInt(2, i); |
| ps.executeUpdate(); |
| } |
| commit(); |
| forceRowCountEstimateUpdate(TAB); |
| psSel1 = prepareStatement( |
| "select id from " + TAB + " where id = ?"); |
| psSel1.setInt(1, 117); |
| JDBC.assertSingleValueResultSet(psSel1.executeQuery(), "117"); |
| psSel2 = prepareStatement( |
| "select val from " + TAB + " where val = ?"); |
| psSel2.setInt(1, 1); |
| JDBC.assertSingleValueResultSet(psSel2.executeQuery(), "1"); |
| Utilities.sleep(100); |
| stats.assertNoStatsTable(TAB); |
| |
| // Cleanup |
| dropTable(TAB); |
| } |
| |
| // 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<Void>() { |
| |
| public Void 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 two integer columns, where the first is the |
| * primary key of the table and the second is a value with a non-unique |
| * index on it. |
| * |
| * @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 two integer columns, where the first is the |
| * primary key of the table and the second is a value with a non-unique |
| * index on it. |
| * |
| * @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, val int)"); |
| s.executeUpdate("create index NON_UNIQUE_INDEX_" + table + " on " + |
| table + "(val)"); |
| |
| 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"); |
| myStats.assertNoStatsTable(table); |
| } |
| |
| /** |
| * 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.setInt(2, i % 20); |
| 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(); |
| } |
| } |
