testcases.java

非常棒的java数据库

/*
 * Copyright 2004-2008 H2 Group. Licensed under the H2 License, Version 1.0
 * (license2)
 * Initial Developer: H2 Group
 */
package org.h2.test.db;

import java.io.File;
import java.io.StringReader;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Timestamp;
import java.util.Random;

import org.h2.test.TestBase;

/**
 * Various test cases.
 */
public class TestCases extends TestBase {

    public void test() throws Exception {
        testDeleteGroup();
        testDisconnect();
        testExecuteTrace();
        if (config.memory || config.logMode == 0) {
            return;
        }
        testSpecialSQL();
        testUpperCaseLowerCaseDatabase();
        testManualCommitSet();
        testSchemaIdentityReconnect();
        testAlterTableReconnect();
        testPersistentSettings();
        testInsertSelectUnion();
        testViewReconnect();
        testDefaultQueryReconnect();
        testBigString();
        testRenameReconnect();
        testAllSizes();
        testCreateDrop();
        testPolePos();
        testQuick();
        testMutableObjects();
        testSelectForUpdate();
        testDoubleRecovery();
        testConstraintReconnect();
        testCollation();
    }

    private void testDeleteGroup() throws Exception {
        deleteDb("cases");
        Connection conn = getConnection("cases");
        Statement stat = conn.createStatement();
        stat.execute("set max_memory_rows 2");
        stat.execute("create table test(id int primary key, x int)");
        stat.execute("insert into test values(0, 0), (1, 1), (2, 2)");
        stat.execute("delete from test where id not in (select min(x) from test group by id)");
        conn.close();
    }

    private void testSpecialSQL() throws Exception {
        deleteDb("cases");
        Connection conn = getConnection("cases");
        Statement stat = conn.createStatement();
        try {
            stat.execute("create table address(id identity, name varchar check? instr(value, '@') > 1)");
        } catch (SQLException e) {
            checkNotGeneralException(e);
        }
        stat.execute("SET AUTOCOMMIT OFF; \n//create sequence if not exists object_id;\n");
        stat.execute("SET AUTOCOMMIT OFF;\n//create sequence if not exists object_id;\n");
        stat.execute("SET AUTOCOMMIT OFF; //create sequence if not exists object_id;");
        stat.execute("SET AUTOCOMMIT OFF;//create sequence if not exists object_id;");
        stat.execute("SET AUTOCOMMIT OFF \n//create sequence if not exists object_id;");
        stat.execute("SET AUTOCOMMIT OFF\n//create sequence if not exists object_id;");
        stat.execute("SET AUTOCOMMIT OFF //create sequence if not exists object_id;");
        stat.execute("SET AUTOCOMMIT OFF//create sequence if not exists object_id;");
        stat.execute("SET AUTOCOMMIT OFF; \n///create sequence if not exists object_id;");
        stat.execute("SET AUTOCOMMIT OFF;\n///create sequence if not exists object_id;");
        stat.execute("SET AUTOCOMMIT OFF; ///create sequence if not exists object_id;");
        stat.execute("SET AUTOCOMMIT OFF;///create sequence if not exists object_id;");
        stat.execute("SET AUTOCOMMIT OFF \n///create sequence if not exists object_id;");
        stat.execute("SET AUTOCOMMIT OFF\n///create sequence if not exists object_id;");
        stat.execute("SET AUTOCOMMIT OFF ///create sequence if not exists object_id;");
        stat.execute("SET AUTOCOMMIT OFF///create sequence if not exists object_id;");
        conn.close();
    }

    private void testUpperCaseLowerCaseDatabase() throws Exception {
        if (File.separatorChar != '\\') {
            return;
        }
        deleteDb("cases");
        deleteDb("CaSeS");
        Connection conn, conn2;
        ResultSet rs;
        conn = getConnection("cases");
        Statement stat = conn.createStatement();
        stat.execute("CHECKPOINT");
        stat.execute("CREATE TABLE TEST(ID INT)");
        stat.execute("INSERT INTO TEST VALUES(1)");
        stat.execute("CHECKPOINT");

        conn2 = getConnection("CaSeS");
        rs = conn.createStatement().executeQuery("SELECT * FROM TEST");
        check(rs.next());
        conn2.close();

        conn.close();

        conn = getConnection("cases");
        rs = conn.createStatement().executeQuery("SELECT * FROM TEST");
        check(rs.next());
        conn.close();

        conn = getConnection("CaSeS");
        rs = conn.createStatement().executeQuery("SELECT * FROM TEST");
        check(rs.next());
        conn.close();

        deleteDb("cases");
        deleteDb("CaSeS");

    }

    private void testManualCommitSet() throws Exception {
        deleteDb("cases");
        Connection conn = getConnection("cases");
        Connection conn2 = getConnection("cases");
        conn.setAutoCommit(false);
        conn2.setAutoCommit(false);
        conn.createStatement().execute("SET MODE REGULAR");
        conn2.createStatement().execute("SET MODE REGULAR");
        conn.close();
        conn2.close();
    }

    private void testSchemaIdentityReconnect() throws Exception {
        deleteDb("cases");
        Connection conn = getConnection("cases");
        Statement stat = conn.createStatement();
        stat.execute("create schema s authorization sa");
        stat.execute("create table s.test(id identity)");
        conn.close();
        conn = getConnection("cases");
        ResultSet rs = conn.createStatement().executeQuery("SELECT * FROM S.TEST");
        while (rs.next()) {
            // ignore
        }
        conn.close();
    }

    private void testDisconnect() throws Exception {
        if (config.networked || config.codeCoverage) {
            return;
        }
        deleteDb("cases");
        Connection conn = getConnection("cases");
        final Statement stat = conn.createStatement();
        stat.execute("CREATE TABLE TEST(ID IDENTITY)");
        for (int i = 0; i < 1000; i++) {
            stat.execute("INSERT INTO TEST() VALUES()");
        }
        final SQLException[] stopped = new SQLException[1];
        Thread t = new Thread(new Runnable() {
            public void run() {
                try {
                    long time = System.currentTimeMillis();
                    ResultSet rs = stat
                            .executeQuery("SELECT MAX(T.ID) FROM TEST T, TEST, TEST, TEST, TEST, TEST, TEST, TEST, TEST, TEST, TEST");
                    rs.next();
                    time = System.currentTimeMillis() - time;
                    TestBase.logError("query was too quick; result: " + rs.getInt(1) + " time:" + time, null);
                } catch (SQLException e) {
                    stopped[0] = e;
                    // ok
                }
            }
        });
        t.start();
        Thread.sleep(300);
        long time = System.currentTimeMillis();
        conn.close();
        t.join(5000);
        if (stopped[0] == null) {
            error("query still running");
        } else {
            checkNotGeneralException(stopped[0]);
        }
        time = System.currentTimeMillis() - time;
        if (time > 5000) {
            error("closing took " + time);
        }
        deleteDb("cases");
    }

    private void testExecuteTrace() throws Exception {
        deleteDb("cases");
        Connection conn = getConnection("cases");
        Statement stat = conn.createStatement();
        ResultSet rs = stat.executeQuery("SELECT ? FROM DUAL {1: 'Hello'}");
        rs.next();
        check("Hello", rs.getString(1));
        checkFalse(rs.next());

        rs = stat.executeQuery("SELECT ? FROM DUAL UNION ALL SELECT ? FROM DUAL {1: 'Hello', 2:'World' }");
        rs.next();
        check("Hello", rs.getString(1));
        rs.next();
        check("World", rs.getString(1));
        checkFalse(rs.next());

        conn.close();
    }

    private void testAlterTableReconnect() throws Exception {
        deleteDb("cases");
        Connection conn = getConnection("cases");
        Statement stat = conn.createStatement();
        stat.execute("create table test(id identity);");
        stat.execute("insert into test values(1);");
        try {
            stat.execute("alter table test add column name varchar not null;");
            error();
        } catch (SQLException e) {
            checkNotGeneralException(e);
        }
        conn.close();
        conn = getConnection("cases");
        ResultSet rs = conn.createStatement().executeQuery("SELECT * FROM TEST");
        rs.next();
        check(rs.getString(1), "1");
        checkFalse(rs.next());
        stat = conn.createStatement();
        stat.execute("drop table test");
        stat.execute("create table test(id identity)");
        stat.execute("insert into test values(1)");
        stat.execute("alter table test alter column id set default 'x'");
        conn.close();
        conn = getConnection("cases");
        stat = conn.createStatement();
        rs = conn.createStatement().executeQuery("SELECT * FROM TEST");
        rs.next();
        check(rs.getString(1), "1");
        checkFalse(rs.next());
        stat.execute("drop table test");
        stat.execute("create table test(id identity)");
        stat.execute("insert into test values(1)");
        try {
            stat.execute("alter table test alter column id date");
            error();
        } catch (SQLException e) {
            checkNotGeneralException(e);
        }
        conn.close();
        conn = getConnection("cases");
        rs = conn.createStatement().executeQuery("SELECT * FROM TEST");
        rs.next();
        check(rs.getString(1), "1");
        checkFalse(rs.next());
        conn.close();
    }

    private void testCollation() throws Exception {
        deleteDb("cases");
        Connection conn = getConnection("cases");
        Statement stat = conn.createStatement();
        stat.execute("SET COLLATION ENGLISH STRENGTH PRIMARY");
        stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255))");
        stat.execute("INSERT INTO TEST VALUES(1, 'Hello'), (2, 'World'), (3, 'WORLD'), (4, 'HELLO')");
        stat.execute("create index idxname on test(name)");
        ResultSet rs;
        rs = stat.executeQuery("select name from test order by name");
        rs.next();
        check(rs.getString(1), "Hello");
        rs.next();
        check(rs.getString(1), "HELLO");
        rs.next();
        check(rs.getString(1), "World");
        rs.next();
        check(rs.getString(1), "WORLD");
        rs = stat.executeQuery("select name from test where name like 'He%'");
        rs.next();
        check(rs.getString(1), "Hello");
        rs.next();
        check(rs.getString(1), "HELLO");
        conn.close();
    }

    private void testPersistentSettings() throws Exception {
        deleteDb("cases");
        Connection conn = getConnection("cases");
        Statement stat = conn.createStatement();
        stat.execute("SET COLLATION de_DE");
        stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR)");
        stat.execute("CREATE INDEX IDXNAME ON TEST(NAME)");
        // \u00f6 = oe
        stat.execute("INSERT INTO TEST VALUES(1, 'B\u00f6hlen'), (2, 'Bach'), (3, 'Bucher')");
        conn.close();
        conn = getConnection("cases");
        ResultSet rs = conn.createStatement().executeQuery("SELECT NAME FROM TEST ORDER BY NAME");
        rs.next();
        check(rs.getString(1), "Bach");
        rs.next();
        check(rs.getString(1), "B\u00f6hlen");
        rs.next();
        check(rs.getString(1), "Bucher");
        conn.close();
    }

    private void testInsertSelectUnion() throws Exception {
        deleteDb("cases");
        Connection conn = getConnection("cases");
        Statement stat = conn.createStatement();
        stat.execute("CREATE TABLE TEST(ORDER_ID INT PRIMARY KEY, ORDER_DATE DATETIME, USER_ID INT ,"
                + "DESCRIPTION VARCHAR, STATE VARCHAR, TRACKING_ID VARCHAR)");
        Timestamp orderDate = Timestamp.valueOf("2005-05-21 17:46:00");
        String sql = "insert into TEST (ORDER_ID,ORDER_DATE,USER_ID,DESCRIPTION,STATE,TRACKING_ID) "
                + "select cast(? as int),cast(? as date),cast(? as int),cast(? as varchar),cast(? as varchar),cast(? as varchar) union all select ?,?,?,?,?,?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setInt(1, 5555);
        ps.setTimestamp(2, orderDate);
        ps.setInt(3, 2222);
        ps.setString(4, "test desc");
        ps.setString(5, "test_state");
        ps.setString(6, "testid");
        ps.setInt(7, 5556);
        ps.setTimestamp(8, orderDate);
        ps.setInt(9, 2222);
        ps.setString(10, "test desc");
        ps.setString(11, "test_state");
        ps.setString(12, "testid");
        check(ps.executeUpdate(), 2);
        ps.close();
        conn.close();
    }

    private void testViewReconnect() throws Exception {
        trace("testViewReconnect");
        deleteDb("cases");
        Connection conn = getConnection("cases");
        Statement stat = conn.createStatement();
        stat.execute("create table test(id int)");
        stat.execute("create view abc as select * from test");
        stat.execute("drop table test");
        conn.close();
        conn = getConnection("cases");
        stat = conn.createStatement();
        try {