Examples with Index org.h2.index.Index used on opensource projects

Example 81 with Index

use of org.h2.index.Index in project h2database by h2database.

the class TestMVTableEngine method testSecondaryIndex.

private void testSecondaryIndex() throws SQLException {
    Connection conn;
    Statement stat;
    deleteDb(getTestName());
    String url = getTestName() + ";MV_STORE=TRUE";
    url = getURL(url, true);
    conn = getConnection(url);
    stat = conn.createStatement();
    stat.execute("create table test(id int)");
    int size = 8 * 1024;
    stat.execute("insert into test select mod(x * 111, " + size + ") " + "from system_range(1, " + size + ")");
    stat.execute("create index on test(id)");
    ResultSet rs = stat.executeQuery("select count(*) from test inner join " + "system_range(1, " + size + ") where " + "id = mod(x * 111, " + size + ")");
    rs.next();
    assertEquals(size, rs.getInt(1));
    conn.close();
}

Example 82 with Index

use of org.h2.index.Index in project h2database by h2database.

the class TestMVTableEngine method testMinMaxWithNull.

private void testMinMaxWithNull() throws Exception {
    Connection conn;
    Connection conn2;
    Statement stat;
    Statement stat2;
    deleteDb(getTestName());
    String url = getTestName() + ";MV_STORE=TRUE;MVCC=TRUE";
    url = getURL(url, true);
    conn = getConnection(url);
    stat = conn.createStatement();
    stat.execute("create table test(data int)");
    stat.execute("create index on test(data)");
    stat.execute("insert into test values(null), (2)");
    conn2 = getConnection(url);
    stat2 = conn2.createStatement();
    conn.setAutoCommit(false);
    conn2.setAutoCommit(false);
    stat.execute("insert into test values(1)");
    ResultSet rs;
    rs = stat.executeQuery("select min(data) from test");
    rs.next();
    assertEquals(1, rs.getInt(1));
    rs = stat2.executeQuery("select min(data) from test");
    rs.next();
    // not yet committed
    assertEquals(2, rs.getInt(1));
    conn2.close();
    conn.close();
}

Example 83 with Index

use of org.h2.index.Index in project h2database by h2database.

the class TestRandomMapOps method testOps.

private void testOps(String fileName, int size, int seed) {
    FileUtils.delete(fileName);
    MVStore s = openStore(fileName);
    MVMap<Integer, byte[]> m = s.openMap("data");
    Random r = new Random(seed);
    op = 0;
    TreeMap<Integer, byte[]> map = new TreeMap<>();
    for (; op < size; op++) {
        int k = r.nextInt(100);
        byte[] v = new byte[r.nextInt(10) * 10];
        int type = r.nextInt(12);
        switch(type) {
            case 0:
            case 1:
            case 2:
            case 3:
                log(op, k, v, "m.put({0}, {1})");
                m.put(k, v);
                map.put(k, v);
                break;
            case 4:
            case 5:
                log(op, k, v, "m.remove({0})");
                m.remove(k);
                map.remove(k);
                break;
            case 6:
                log(op, k, v, "s.compact(90, 1024)");
                s.compact(90, 1024);
                break;
            case 7:
                log(op, k, v, "m.clear()");
                m.clear();
                map.clear();
                break;
            case 8:
                log(op, k, v, "s.commit()");
                s.commit();
                break;
            case 9:
                log(op, k, v, "s.commit()");
                s.commit();
                log(op, k, v, "s.close()");
                s.close();
                log(op, k, v, "s = openStore(fileName)");
                s = openStore(fileName);
                log(op, k, v, "m = s.openMap(\"data\")");
                m = s.openMap("data");
                break;
            case 10:
                log(op, k, v, "s.commit()");
                s.commit();
                log(op, k, v, "s.compactMoveChunks()");
                s.compactMoveChunks();
                break;
            case 11:
                log(op, k, v, "m.getKeyIndex({0})");
                ArrayList<Integer> keyList = new ArrayList<>(map.keySet());
                int index = Collections.binarySearch(keyList, k, null);
                int index2 = (int) m.getKeyIndex(k);
                assertEquals(index, index2);
                if (index >= 0) {
                    int k2 = m.getKey(index);
                    assertEquals(k2, k);
                }
                break;
        }
        assertEqualsMapValues(map.get(k), m.get(k));
        assertEquals(map.ceilingKey(k), m.ceilingKey(k));
        assertEquals(map.floorKey(k), m.floorKey(k));
        assertEquals(map.higherKey(k), m.higherKey(k));
        assertEquals(map.lowerKey(k), m.lowerKey(k));
        assertEquals(map.isEmpty(), m.isEmpty());
        assertEquals(map.size(), m.size());
        if (!map.isEmpty()) {
            assertEquals(map.firstKey(), m.firstKey());
            assertEquals(map.lastKey(), m.lastKey());
        }
    }
    s.close();
}

Example 84 with Index

use of org.h2.index.Index in project h2database by h2database.

the class TestFuzzOptimizations method testInSelect.

private void testInSelect() {
    Db db = new Db(conn);
    db.execute("CREATE TABLE TEST(A INT, B INT)");
    db.execute("CREATE INDEX IDX ON TEST(A)");
    db.execute("INSERT INTO TEST SELECT X/4, MOD(X, 4) " + "FROM SYSTEM_RANGE(1, 16)");
    db.execute("UPDATE TEST SET A = NULL WHERE A = 0");
    db.execute("UPDATE TEST SET B = NULL WHERE B = 0");
    Random random = new Random();
    long seed = random.nextLong();
    println("seed: " + seed);
    for (int i = 0; i < 100; i++) {
        String column = random.nextBoolean() ? "A" : "B";
        String value = new String[] { "NULL", "0", "A", "B" }[random.nextInt(4)];
        String compare = random.nextBoolean() ? "A" : "B";
        int x = random.nextInt(3);
        String sql1 = "SELECT * FROM TEST T WHERE " + column + "+0 " + "IN(SELECT " + value + " FROM TEST I WHERE I." + compare + "=?) ORDER BY 1, 2";
        String sql2 = "SELECT * FROM TEST T WHERE " + column + " " + "IN(SELECT " + value + " FROM TEST I WHERE I." + compare + "=?) ORDER BY 1, 2";
        List<Map<String, Object>> a = db.prepare(sql1).set(x).query();
        List<Map<String, Object>> b = db.prepare(sql2).set(x).query();
        assertTrue("seed: " + seed + " sql: " + sql1 + " a: " + a + " b: " + b, a.equals(b));
    }
    db.execute("DROP TABLE TEST");
}

Example 85 with Index

use of org.h2.index.Index in project h2database by h2database.

the class TestFuzzOptimizations method testGroupSorted.

private void testGroupSorted() {
    Db db = new Db(conn);
    db.execute("CREATE TABLE TEST(A INT, B INT, C INT)");
    Random random = new Random();
    long seed = random.nextLong();
    println("seed: " + seed);
    for (int i = 0; i < 100; i++) {
        Prepared p = db.prepare("INSERT INTO TEST VALUES(?, ?, ?)");
        p.set(new String[] { null, "0", "1", "2" }[random.nextInt(4)]);
        p.set(new String[] { null, "0", "1", "2" }[random.nextInt(4)]);
        p.set(new String[] { null, "0", "1", "2" }[random.nextInt(4)]);
        p.execute();
    }
    int len = getSize(1000, 3000);
    for (int i = 0; i < len / 10; i++) {
        db.execute("CREATE TABLE TEST_INDEXED AS SELECT * FROM TEST");
        int jLen = 1 + random.nextInt(2);
        for (int j = 0; j < jLen; j++) {
            String x = "CREATE INDEX IDX" + j + " ON TEST_INDEXED(";
            int kLen = 1 + random.nextInt(2);
            for (int k = 0; k < kLen; k++) {
                if (k > 0) {
                    x += ",";
                }
                x += new String[] { "A", "B", "C" }[random.nextInt(3)];
            }
            db.execute(x + ")");
        }
        for (int j = 0; j < 10; j++) {
            String x = "SELECT ";
            for (int k = 0; k < 3; k++) {
                if (k > 0) {
                    x += ",";
                }
                x += new String[] { "SUM(A)", "MAX(B)", "AVG(C)", "COUNT(B)" }[random.nextInt(4)];
                x += " S" + k;
            }
            x += " FROM ";
            String group = " GROUP BY ";
            int kLen = 1 + random.nextInt(2);
            for (int k = 0; k < kLen; k++) {
                if (k > 0) {
                    group += ",";
                }
                group += new String[] { "A", "B", "C" }[random.nextInt(3)];
            }
            group += " ORDER BY 1, 2, 3";
            List<Map<String, Object>> a = db.query(x + "TEST" + group);
            List<Map<String, Object>> b = db.query(x + "TEST_INDEXED" + group);
            assertEquals(a.toString(), b.toString());
            assertTrue(a.equals(b));
        }
        db.execute("DROP TABLE TEST_INDEXED");
    }
    db.execute("DROP TABLE TEST");
}