Examples with Insert org.h2.command.dml.Insert used on opensource projects

Example 51 with Insert

use of org.h2.command.dml.Insert in project h2database by h2database.

the class TestConcurrentUpdate method test.

@Override
public void test() throws Exception {
    deleteDb("concurrent");
    final String url = getURL("concurrent;MULTI_THREADED=TRUE", true);
    Connection conn = getConnection(url);
    Statement stat = conn.createStatement();
    stat.execute("create table test(id int primary key, name varchar)");
    Task[] tasks = new Task[THREADS];
    for (int i = 0; i < THREADS; i++) {
        final int threadId = i;
        Task t = new Task() {

            @Override
            public void call() throws Exception {
                Random r = new Random(threadId);
                Connection conn = getConnection(url);
                PreparedStatement insert = conn.prepareStatement("insert into test values(?, ?)");
                PreparedStatement update = conn.prepareStatement("update test set name = ? where id = ?");
                PreparedStatement delete = conn.prepareStatement("delete from test where id = ?");
                PreparedStatement select = conn.prepareStatement("select * from test where id = ?");
                while (!stop) {
                    try {
                        int x = r.nextInt(ROW_COUNT);
                        String data = "x" + r.nextInt(ROW_COUNT);
                        switch(r.nextInt(3)) {
                            case 0:
                                insert.setInt(1, x);
                                insert.setString(2, data);
                                insert.execute();
                                break;
                            case 1:
                                update.setString(1, data);
                                update.setInt(2, x);
                                update.execute();
                                break;
                            case 2:
                                delete.setInt(1, x);
                                delete.execute();
                                break;
                            case 4:
                                select.setInt(1, x);
                                ResultSet rs = select.executeQuery();
                                while (rs.next()) {
                                    rs.getString(2);
                                }
                                break;
                        }
                    } catch (SQLException e) {
                        handleException(e);
                    }
                }
                conn.close();
            }
        };
        tasks[i] = t;
        t.execute();
    }
    // test 2 seconds
    for (int i = 0; i < 200; i++) {
        Thread.sleep(10);
        for (Task t : tasks) {
            if (t.isFinished()) {
                i = 1000;
                break;
            }
        }
    }
    for (Task t : tasks) {
        t.get();
    }
    conn.close();
}

Example 52 with Insert

use of org.h2.command.dml.Insert 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 53 with Insert

use of org.h2.command.dml.Insert 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");
}

Example 54 with Insert

use of org.h2.command.dml.Insert in project h2database by h2database.

the class TestDiskSpaceLeak method main.

/**
 * Run just this test.
 *
 * @param args ignored
 */
public static void main(String... args) throws Exception {
    DeleteDbFiles.execute("data", null, true);
    Class.forName("org.h2.Driver");
    Connection conn;
    long before = 0;
    for (int i = 0; i < 10; i++) {
        conn = DriverManager.getConnection("jdbc:h2:data/test");
        ResultSet rs;
        rs = conn.createStatement().executeQuery("select count(*) from information_schema.lobs");
        rs.next();
        System.out.println("lobs: " + rs.getInt(1));
        rs = conn.createStatement().executeQuery("select count(*) from information_schema.lob_map");
        rs.next();
        System.out.println("lob_map: " + rs.getInt(1));
        rs = conn.createStatement().executeQuery("select count(*) from information_schema.lob_data");
        rs.next();
        System.out.println("lob_data: " + rs.getInt(1));
        conn.close();
        Recover.execute("data", "test");
        new File("data/test.h2.sql").renameTo(new File("data/test." + i + ".sql"));
        conn = DriverManager.getConnection("jdbc:h2:data/test");
        // ((JdbcConnection) conn).setPowerOffCount(i);
        ((JdbcConnection) conn).setPowerOffCount(28);
        String last = "connect";
        try {
            conn.createStatement().execute("drop table test if exists");
            last = "drop";
            conn.createStatement().execute("create table test(id identity, b blob)");
            last = "create";
            conn.createStatement().execute("insert into test values(1, space(10000))");
            last = "insert";
            conn.createStatement().execute("delete from test");
            last = "delete";
            conn.createStatement().execute("insert into test values(1, space(10000))");
            last = "insert2";
            conn.createStatement().execute("delete from test");
            last = "delete2";
        } catch (SQLException e) {
        // ignore
        } finally {
            JdbcUtils.closeSilently(conn);
        }
        long now = new File("data/test.h2.db").length();
        long diff = now - before;
        before = now;
        System.out.println(now + " " + diff + " " + i + " " + last);
    }
}

Example 55 with Insert

use of org.h2.command.dml.Insert in project h2database by h2database.

the class TestTempTableCrash method test.

private static void test() throws Exception {
    Connection conn;
    Statement stat;
    System.setProperty("h2.delayWrongPasswordMin", "0");
    System.setProperty("h2.check2", "false");
    FilePathRec.register();
    System.setProperty("reopenShift", "4");
    TestReopen reopen = new TestReopen();
    FilePathRec.setRecorder(reopen);
    String url = "jdbc:h2:rec:memFS:data;PAGE_SIZE=64;ANALYZE_AUTO=100";
    // String url = "jdbc:h2:" + RecordingFileSystem.PREFIX +
    // "data/test;PAGE_SIZE=64";
    Class.forName("org.h2.Driver");
    DeleteDbFiles.execute("data", "test", true);
    conn = DriverManager.getConnection(url, "sa", "sa");
    stat = conn.createStatement();
    Random random = new Random(1);
    long start = System.nanoTime();
    for (int i = 0; i < 10000; i++) {
        long now = System.nanoTime();
        if (now > start + TimeUnit.SECONDS.toNanos(1)) {
            System.out.println("i: " + i);
            start = now;
        }
        int x;
        x = random.nextInt(100);
        stat.execute("drop table if exists test" + x);
        String type = random.nextBoolean() ? "temp" : "";
        // String type = "";
        stat.execute("create " + type + " table test" + x + "(id int primary key, name varchar)");
        if (random.nextBoolean()) {
            stat.execute("create index idx_" + x + " on test" + x + "(name, id)");
        }
        if (random.nextBoolean()) {
            stat.execute("insert into test" + x + " select x, x " + "from system_range(1, " + random.nextInt(100) + ")");
        }
        if (random.nextInt(10) == 1) {
            conn.close();
            conn = DriverManager.getConnection(url, "sa", "sa");
            stat = conn.createStatement();
        }
    }
    conn.close();
}