testmetadata.java

非常棒的java数据库

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

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.Driver;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.sql.Types;

import org.h2.engine.Constants;
import org.h2.test.TestBase;
import org.h2.value.DataType;

/**
 * Test for the DatabaseMetaData implementation.
 */
public class TestMetaData extends TestBase {

    Connection conn;
    DatabaseMetaData meta;
    Statement stat;
    String catalog = "METADATA";

    public void test() throws Exception {
        deleteDb("metaData");
        conn = getConnection("metaData");

        testColumnPrecision();
        testColumnDefault();
        testCrossReferences();
        testProcedureColumns();

        stat = conn.createStatement();
        meta = conn.getMetaData();
        testStatic();
        // TODO test remaining meta data

        stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255))");
        stat.execute("CREATE INDEX IDXNAME ON TEST(NAME)");

        ResultSet rs;

        rs = meta.getCatalogs();
        rs.next();
        check(rs.getString(1), catalog);
        checkFalse(rs.next());

        rs = meta.getSchemas();
        rs.next();
        check(rs.getString("TABLE_SCHEM"), "INFORMATION_SCHEMA");
        rs.next();
        check(rs.getString("TABLE_SCHEM"), "PUBLIC");
        checkFalse(rs.next());

        rs = meta.getTableTypes();
        rs.next();
        check(rs.getString("TABLE_TYPE"), "SYSTEM TABLE");
        rs.next();
        check(rs.getString("TABLE_TYPE"), "TABLE");
        rs.next();
        check(rs.getString("TABLE_TYPE"), "TABLE LINK");
        rs.next();
        check(rs.getString("TABLE_TYPE"), "VIEW");
        checkFalse(rs.next());

        rs = meta.getTables(null, Constants.SCHEMA_MAIN, null, new String[] { "TABLE" });
        check(rs.getStatement() == null);
        rs.next();
        check(rs.getString("TABLE_NAME"), "TEST");
        checkFalse(rs.next());

        rs = meta.getTables(null, "INFORMATION_SCHEMA", null, new String[] { "TABLE", "SYSTEM TABLE" });
        rs.next();
        check("CATALOGS", rs.getString("TABLE_NAME"));
        rs.next();
        check("COLLATIONS", rs.getString("TABLE_NAME"));
        rs.next();
        check("COLUMNS", rs.getString("TABLE_NAME"));
        rs.next();
        check("COLUMN_PRIVILEGES", rs.getString("TABLE_NAME"));
        rs.next();
        check("CONSTANTS", rs.getString("TABLE_NAME"));
        rs.next();
        check("CONSTRAINTS", rs.getString("TABLE_NAME"));
        rs.next();
        check("CROSS_REFERENCES", rs.getString("TABLE_NAME"));
        rs.next();
        check("DOMAINS", rs.getString("TABLE_NAME"));
        rs.next();
        check("FUNCTION_ALIASES", rs.getString("TABLE_NAME"));
        rs.next();
        check("FUNCTION_COLUMNS", rs.getString("TABLE_NAME"));
        rs.next();
        check("HELP", rs.getString("TABLE_NAME"));
        rs.next();
        check("INDEXES", rs.getString("TABLE_NAME"));
        rs.next();
        check("IN_DOUBT", rs.getString("TABLE_NAME"));
        rs.next();
        check("LOCKS", rs.getString("TABLE_NAME"));
        rs.next();
        check("RIGHTS", rs.getString("TABLE_NAME"));
        rs.next();
        check("ROLES", rs.getString("TABLE_NAME"));
        rs.next();
        check("SCHEMATA", rs.getString("TABLE_NAME"));
        rs.next();
        check("SEQUENCES", rs.getString("TABLE_NAME"));
        rs.next();
        check("SESSIONS", rs.getString("TABLE_NAME"));
        rs.next();
        check("SETTINGS", rs.getString("TABLE_NAME"));
        rs.next();
        check("TABLES", rs.getString("TABLE_NAME"));
        rs.next();
        check("TABLE_PRIVILEGES", rs.getString("TABLE_NAME"));
        rs.next();
        check("TABLE_TYPES", rs.getString("TABLE_NAME"));
        rs.next();
        check("TRIGGERS", rs.getString("TABLE_NAME"));
        rs.next();
        check("TYPE_INFO", rs.getString("TABLE_NAME"));
        rs.next();
        check("USERS", rs.getString("TABLE_NAME"));
        rs.next();
        check("VIEWS", rs.getString("TABLE_NAME"));
        checkFalse(rs.next());

        rs = meta.getColumns(null, null, "TEST", null);
        rs.next();
        check(rs.getString("COLUMN_NAME"), "ID");
        rs.next();
        check(rs.getString("COLUMN_NAME"), "NAME");
        checkFalse(rs.next());

        rs = meta.getPrimaryKeys(null, null, "TEST");
        rs.next();
        check(rs.getString("COLUMN_NAME"), "ID");
        checkFalse(rs.next());

        rs = meta.getBestRowIdentifier(null, null, "TEST", DatabaseMetaData.bestRowSession, false);
        rs.next();
        check(rs.getString("COLUMN_NAME"), "ID");
        checkFalse(rs.next());

        rs = meta.getIndexInfo(null, null, "TEST", false, false);
        rs.next();
        String index = rs.getString("INDEX_NAME");
        check(index.startsWith("PRIMARY_KEY"));
        check(rs.getString("COLUMN_NAME"), "ID");
        rs.next();
        check(rs.getString("INDEX_NAME"), "IDXNAME");
        check(rs.getString("COLUMN_NAME"), "NAME");
        checkFalse(rs.next());

        rs = meta.getIndexInfo(null, null, "TEST", true, false);
        rs.next();
        index = rs.getString("INDEX_NAME");
        check(index.startsWith("PRIMARY_KEY"));
        check(rs.getString("COLUMN_NAME"), "ID");
        checkFalse(rs.next());

        rs = meta.getVersionColumns(null, null, "TEST");
        checkFalse(rs.next());

        stat.execute("DROP TABLE TEST");

        rs = stat.executeQuery("SELECT * FROM INFORMATION_SCHEMA.SETTINGS");
        while (rs.next()) {
            String name = rs.getString("NAME");
            String value = rs.getString("VALUE");
            trace(name + "=" + value);
        }

        test(conn);

        // meta.getTablePrivileges()

        // meta.getAttributes()
        // meta.getColumnPrivileges()
        // meta.getSuperTables()
        // meta.getSuperTypes()
        // meta.getTypeInfo()
        // meta.getUDTs()

        conn.close();
        testTempTable();

    }

    private void testColumnPrecision() throws Exception {
        Statement stat = conn.createStatement();
        stat.execute("CREATE TABLE ONE(X NUMBER(12,2), Y FLOAT)");
        stat.execute("CREATE TABLE TWO AS SELECT * FROM ONE");
        ResultSet rs;
        ResultSetMetaData meta;
        rs = stat.executeQuery("SELECT * FROM ONE");
        meta = rs.getMetaData();
        check(12, meta.getPrecision(1));
        check(17, meta.getPrecision(2));
        check(Types.DECIMAL, meta.getColumnType(1));
        check(Types.DOUBLE, meta.getColumnType(2));
        rs = stat.executeQuery("SELECT * FROM TWO");
        meta = rs.getMetaData();
        check(12, meta.getPrecision(1));
        check(17, meta.getPrecision(2));
        check(Types.DECIMAL, meta.getColumnType(1));
        check(Types.DOUBLE, meta.getColumnType(2));
        stat.execute("DROP TABLE ONE, TWO");
    }

    private void testColumnDefault() throws Exception {
        DatabaseMetaData meta = conn.getMetaData();
        ResultSet rs;
        Statement stat = conn.createStatement();
        stat.execute("CREATE TABLE TEST(A INT, B INT DEFAULT NULL)");
        rs = meta.getColumns(null, null, "TEST", null);
        rs.next();
        check("A", rs.getString("COLUMN_NAME"));
        check(null, rs.getString("COLUMN_DEF"));
        rs.next();
        check("B", rs.getString("COLUMN_NAME"));
        check("NULL", rs.getString("COLUMN_DEF"));