Examples with DatabasePropertyTestSetup org.apache.derbyTesting.junit.DatabasePropertyTestSetup used on opensource projects

Example 16 with DatabasePropertyTestSetup

use of org.apache.derbyTesting.junit.DatabasePropertyTestSetup in project derby by apache.

the class SelectivityTest method suite.

public static Test suite() {
    Properties props = new Properties();
    // first disable the automatic statistics gathering so we get
    // clean statistics
    // then switch the statement cache size to 0, so that doesn't
    // interfere and previous tests' left-overs are gone.
    props.setProperty("derby.storage.indexStats.auto", "false");
    props.setProperty("derby.language.statementCacheSize", "0");
    // set the props, and boot the db
    Test test = new DatabasePropertyTestSetup(new BaseTestSuite(SelectivityTest.class), props, true);
    return new CleanDatabaseTestSetup(test) {

        protected void decorateSQL(Statement s) throws SQLException {
            s.executeUpdate("create table two (x int)");
            s.executeUpdate("insert into two values (1),(2)");
            s.executeUpdate("create table ten (x int)");
            s.executeUpdate("insert into ten values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)");
            s.executeUpdate("create table twenty (x int)");
            s.executeUpdate("insert into twenty values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20)");
            s.executeUpdate("create table hundred (x int generated always as identity, dc int)");
            s.executeUpdate("insert into hundred (dc) select t1.x from ten t1, ten t2");
            s.executeUpdate("create table template (id int not null generated always as identity, two int, twenty int, hundred int)");
            // 4000 rows
            s.executeUpdate("insert into template (two, twenty, hundred) select two.x, twenty.x, hundred.x from two, twenty, hundred");
            s.executeUpdate("create index template_two on template(two)");
            s.executeUpdate("create index template_twenty on template(twenty)");
            // 20 distinct values
            s.executeUpdate("create index template_22 on template(twenty,two)");
            s.executeUpdate("create unique index template_id on template(id)");
            s.executeUpdate("create index template_102 on template(hundred,two)");
            s.executeUpdate("create table test (id int, two int, twenty int, hundred int)");
            s.executeUpdate("create index test_id on test(id)");
            s.executeUpdate("insert into test select * from template");
            s.executeUpdate("create view showstats as " + "select cast (conglomeratename as varchar(60)) indexname, " + "cast (statistics as varchar(60)) stats, " + "creationtimestamp createtime, " + "colcount ncols " + "from sys.sysstatistics, sys.sysconglomerates " + "where conglomerateid = referenceid");
            ResultSet statsrs = s.executeQuery("select indexname, stats, ncols from showstats order by indexname, stats, createtime, ncols");
            JDBC.assertFullResultSet(statsrs, new String[][] { { "TEMPLATE_102", "numunique= 100 numrows= 4000", "1" }, { "TEMPLATE_102", "numunique= 200 numrows= 4000", "2" }, { "TEMPLATE_22", "numunique= 20 numrows= 4000", "1" }, { "TEMPLATE_22", "numunique= 40 numrows= 4000", "2" }, { "TEMPLATE_TWENTY", "numunique= 20 numrows= 4000", "1" }, { "TEMPLATE_TWO", "numunique= 2 numrows= 4000", "1" } });
            s.executeUpdate("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP','TEMPLATE',null)");
            s.executeUpdate("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP','TEST',null)");
            statsrs = s.executeQuery("select indexname, stats, ncols from showstats order by indexname, stats, createtime, ncols");
            JDBC.assertFullResultSet(statsrs, new String[][] { { "TEMPLATE_102", "numunique= 100 numrows= 4000", "1" }, { "TEMPLATE_102", "numunique= 200 numrows= 4000", "2" }, { "TEMPLATE_22", "numunique= 20 numrows= 4000", "1" }, { "TEMPLATE_22", "numunique= 40 numrows= 4000", "2" }, { "TEMPLATE_TWENTY", "numunique= 20 numrows= 4000", "1" }, { "TEMPLATE_TWO", "numunique= 2 numrows= 4000", "1" }, { "TEST_ID", "numunique= 4000 numrows= 4000", "1" } });
            s.executeUpdate("create table t1 " + "(id int generated always as identity, " + "two int, twenty int, hundred varchar(3))");
            s.executeUpdate("insert into t1 (hundred, twenty, two) " + "select CAST(CHAR(hundred.x) AS VARCHAR(3)), " + "twenty.x, two.x from hundred, twenty, two");
            s.executeUpdate("create table t2 " + "(id int generated always as identity, " + "two int, twenty int, hundred varchar(3))");
            s.executeUpdate("insert into t2 (hundred, twenty, two) " + "select CAST(CHAR(hundred.x) AS VARCHAR(3)) , " + "twenty.x, two.x from hundred, twenty, two");
            s.executeUpdate("create table t3 " + "(id int generated always as identity, " + "two int, twenty int, hundred varchar(3))");
            s.executeUpdate("insert into t3 (hundred, twenty, two) " + "select CAST(CHAR(hundred.x) AS VARCHAR(3)), " + "twenty.x, two.x from hundred, twenty, two");
            s.executeUpdate("create index t1_hundred on t1(hundred)");
            s.executeUpdate("create index t1_two_twenty on t1(two,twenty)");
            s.executeUpdate("create index " + "t1_twenty_hundred on t1(twenty, hundred)");
            s.executeUpdate("create index t2_hundred on t2(hundred)");
            s.executeUpdate("create index t2_two_twenty on t2(two,twenty)");
            s.executeUpdate("create index t2_twenty_hundred on t2(twenty, hundred)");
            s.executeUpdate("create index t3_hundred on t3(hundred)");
            s.executeUpdate("create index t3_two_twenty on t3(two,twenty)");
            s.executeUpdate("create index t3_twenty_hundred on t3(twenty, hundred)");
            s.executeUpdate("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS" + "('APP','T1',null)");
            s.executeUpdate("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS" + "('APP','T2',null)");
            s.executeUpdate("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS" + "('APP','T3',null)");
            statsrs = s.executeQuery("select indexname, stats, ncols from showstats " + "where indexname like 'T1%' " + "order by indexname, stats");
            JDBC.assertFullResultSet(statsrs, new String[][] { { "T1_HUNDRED", "numunique= 100 numrows= 4000", "1" }, { "T1_TWENTY_HUNDRED", "numunique= 20 numrows= 4000", "1" }, { "T1_TWENTY_HUNDRED", "numunique= 2000 numrows= 4000", "2" }, { "T1_TWO_TWENTY", "numunique= 2 numrows= 4000", "1" }, { "T1_TWO_TWENTY", "numunique= 40 numrows= 4000", "2" } });
            statsrs = s.executeQuery("select indexname, stats, ncols from showstats " + "where indexname like 'T2%' order by indexname, stats");
            JDBC.assertFullResultSet(statsrs, new String[][] { { "T2_HUNDRED", "numunique= 100 numrows= 4000", "1" }, { "T2_TWENTY_HUNDRED", "numunique= 20 numrows= 4000", "1" }, { "T2_TWENTY_HUNDRED", "numunique= 2000 numrows= 4000", "2" }, { "T2_TWO_TWENTY", "numunique= 2 numrows= 4000", "1" }, { "T2_TWO_TWENTY", "numunique= 40 numrows= 4000", "2" } });
            statsrs = s.executeQuery("select indexname, stats, ncols from showstats " + "where indexname like 'T3%' order by indexname, stats");
            JDBC.assertFullResultSet(statsrs, new String[][] { { "T3_HUNDRED", "numunique= 100 numrows= 4000", "1" }, { "T3_TWENTY_HUNDRED", "numunique= 20 numrows= 4000", "1" }, { "T3_TWENTY_HUNDRED", "numunique= 2000 numrows= 4000", "2" }, { "T3_TWO_TWENTY", "numunique= 2 numrows= 4000", "1" }, { "T3_TWO_TWENTY", "numunique= 40 numrows= 4000", "2" } });
            s.executeUpdate("create table scratch_table" + "(id int, two int, twenty int, hundred int)");
            s.executeUpdate("insert into scratch_table select " + "id, two, twenty, CAST(CHAR(hundred) AS INTEGER) " + "from t1");
            s.executeUpdate("create index st_all on scratch_table" + "(two, twenty, hundred)");
            s.executeUpdate("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS" + "('APP','SCRATCH_TABLE',null)");
            s.executeUpdate("create table complex" + "(id int generated always as identity, " + "two int, twenty int, hundred int, a int, b int)");
            s.executeUpdate("insert into complex (two, twenty, hundred, a, b) " + "select two.x, twenty.x, hundred.x, two.x, twenty.x " + "from two, twenty, hundred");
            s.executeUpdate("create index complexind on complex" + "(two, twenty, hundred, a, b)");
            s.executeUpdate("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS" + "('APP','COMPLEX',null)");
        }
    };
}