databasemetadatatest.java

第三方的SQL Server and Sybase的jdbc dirver,速度更快

            assertEquals("getMaxColumnsInOrderBy",0, dbmd.getMaxColumnsInOrderBy());
            assertEquals("getMaxColumnsInTable",1024, dbmd.getMaxColumnsInTable());
            assertEquals("getMaxIndexLength", 900, dbmd.getMaxIndexLength());
            assertEquals("getMaxRowSize",8060, dbmd.getMaxRowSize());
            assertEquals("getMaxTablesInSelect",256, dbmd.getMaxTablesInSelect());
        } else {
            assertEquals("getMaxColumnsInGroupBy",16, dbmd.getMaxColumnsInGroupBy());
            assertEquals("getMaxColumnsInOrderBy",16, dbmd.getMaxColumnsInOrderBy());
            assertEquals("getMaxColumnsInTable", 250, dbmd.getMaxColumnsInTable());
            assertEquals("getMaxIndexLength", 255, dbmd.getMaxIndexLength());
            assertEquals("getMaxRowSize",1962, dbmd.getMaxRowSize());
            assertEquals("getMaxTablesInSelect",16, dbmd.getMaxTablesInSelect());
        }
    }

    /**
     * Test meta data functions that return result sets.
     * @throws Exception
     */
    public void testResultSets() throws Exception
    {
        try {
            DatabaseMetaData dbmd = con.getMetaData();
            ResultSet rs;
            Statement stmt = con.createStatement();
            dropTable("jTDS_META2");
            dropTable("jTDS_META");
            dropProcedure("jtds_spmeta");
            //
            // Create test data
            //
            stmt.execute("CREATE PROC jtds_spmeta @p1 int, @p2 varchar(30) output AS SELECT @p2 = 'test'");
            stmt.execute("CREATE TABLE jTDS_META (id int NOT NULL primary key , data nvarchar(255) NULL, ts timestamp)");
            stmt.execute("CREATE TABLE jTDS_META2 (id int NOT NULL, data2 varchar(255) NULL "+
                            ",  FOREIGN KEY (id) REFERENCES jTDS_META(id)) ");
            //
            rs = dbmd.getBestRowIdentifier(null, null, "jTDS_META", DatabaseMetaData.bestRowUnknown, true);
            assertTrue(checkColumnNames(rs, new String[]{"SCOPE", "COLUMN_NAME", "DATA_TYPE",
                                                "TYPE_NAME", "COLUMN_SIZE", "BUFFER_LENGTH",
                                                "DECIMAL_DIGITS","PSEUDO_COLUMN"}));
            assertTrue(rs.next());
            assertEquals("id", rs.getString(2));
            //
            rs = dbmd.getCatalogs();
            assertTrue(checkColumnNames(rs, new String[]{"TABLE_CAT"}));
            boolean fail = true;
            while (rs.next()) {
                if (rs.getString(1).equalsIgnoreCase("master")) {
                    fail=false;
                    break;
                }
            }
            assertTrue(!fail);
            //
            rs = dbmd.getColumnPrivileges(null, null, "jTDS_META", "id");
            assertTrue(checkColumnNames(rs, new String[]{"TABLE_CAT","TABLE_SCHEM","TABLE_NAME",
                                        "COLUMN_NAME","GRANTOR","GRANTEE","PRIVILEGE","IS_GRANTABLE"}));
            assertTrue(rs.next());
            assertTrue(rs.getString(7).equals("INSERT") ||
                       rs.getString(7).equals("UPDATE") ||
                       rs.getString(7).equals("DELETE") ||
                       rs.getString(7).equals("SELECT"));
            //
            rs = dbmd.getColumns(null, null, "jTDS_META", "%");
            assertTrue(checkColumnNames(rs, new String[]{"TABLE_CAT", "TABLE_SCHEM", "TABLE_NAME",
                    "COLUMN_NAME", "DATA_TYPE", "TYPE_NAME", "COLUMN_SIZE", "BUFFER_LENGTH",
                    "DECIMAL_DIGITS","NUM_PREC_RADIX", "NULLABLE","REMARKS","COLUMN_DEF",
                    "SQL_DATA_TYPE","SQL_DATETIME_SUB","CHAR_OCTET_LENGTH","ORDINAL_POSITION",
                    "IS_NULLABLE","SCOPE_CATALOG","SCOPE_SCHEMA","SCOPE_TABLE","SOURCE_DATA_TYPE"}));
            assertTrue(rs.next());
            assertEquals("id", rs.getString(4));
            assertEquals(java.sql.Types.INTEGER, rs.getInt(5));
            assertTrue(rs.next());
            assertEquals("data", rs.getString(4));
            assertEquals(java.sql.Types.VARCHAR, rs.getInt(5));
            //
            rs = dbmd.getCrossReference(null, null, "jTDS_META", null, null, "jTDS_META2");
            assertTrue(checkColumnNames(rs, new String[]{"PKTABLE_CAT", "PKTABLE_SCHEM", "PKTABLE_NAME","PKCOLUMN_NAME",
                    "FKTABLE_CAT", "FKTABLE_SCHEM", "FKTABLE_NAME","FKCOLUMN_NAME",
                    "KEY_SEQ","UPDATE_RULE","DELETE_RULE","FK_NAME","PK_NAME","DEFERRABILITY"}));
            assertTrue(rs.next());
            assertEquals("id", rs.getString(4));
            //
            rs = dbmd.getExportedKeys(null, null, "jTDS_META");
            assertTrue(checkColumnNames(rs, new String[]{"PKTABLE_CAT", "PKTABLE_SCHEM", "PKTABLE_NAME","PKCOLUMN_NAME",
                    "FKTABLE_CAT", "FKTABLE_SCHEM", "FKTABLE_NAME","FKCOLUMN_NAME",
                    "KEY_SEQ","UPDATE_RULE","DELETE_RULE","FK_NAME","PK_NAME","DEFERRABILITY"}));
            assertTrue(rs.next());
            assertEquals("id", rs.getString(4));
            //
            rs = dbmd.getImportedKeys(null, null, "jTDS_META2");
            assertTrue(checkColumnNames(rs, new String[]{"PKTABLE_CAT", "PKTABLE_SCHEM", "PKTABLE_NAME","PKCOLUMN_NAME",
                    "FKTABLE_CAT", "FKTABLE_SCHEM", "FKTABLE_NAME","FKCOLUMN_NAME",
                    "KEY_SEQ","UPDATE_RULE","DELETE_RULE","FK_NAME","PK_NAME","DEFERRABILITY"}));
            assertTrue(rs.next());
            assertEquals("id", rs.getString(4));
            //
            rs = dbmd.getIndexInfo(null, null, "jTDS_META", false, true);
            assertTrue(checkColumnNames(rs, new String[]{"TABLE_CAT","TABLE_SCHEM","TABLE_NAME","NON_UNIQUE",
                      "INDEX_QUALIFIER","INDEX_NAME","TYPE","ORDINAL_POSITION", "COLUMN_NAME",
                      "ASC_OR_DESC","CARDINALITY","PAGES","FILTER_CONDITION"}));
            assertTrue(rs.next());
            assertEquals("jTDS_META", rs.getString(3));
            //
            rs = dbmd.getPrimaryKeys(null, null, "jTDS_META");
            assertTrue(checkColumnNames(rs, new String[]{"TABLE_CAT","TABLE_SCHEM","TABLE_NAME","COLUMN_NAME","KEY_SEQ", "PK_NAME"}));
            assertTrue(rs.next());
            assertEquals("id", rs.getString(4));
            //
            rs = dbmd.getProcedureColumns(null, null, "jtds_spmeta", "@p1");
            assertTrue(checkColumnNames(rs, new String[]{"PROCEDURE_CAT", "PROCEDURE_SCHEM", "PROCEDURE_NAME",
                    "COLUMN_NAME", "COLUMN_TYPE","DATA_TYPE","TYPE_NAME","PRECISION",
                    "LENGTH","SCALE","RADIX","NULLABLE","REMARKS"}));
            assertTrue(rs.next());
            assertEquals("jtds_spmeta", rs.getString(3));
            assertEquals("@p1", rs.getString(4));
            //
            rs = dbmd.getProcedures(null, null, "jtds_spmeta%");
            assertTrue(checkColumnNames(rs, new String[]{"PROCEDURE_CAT", "PROCEDURE_SCHEM", "PROCEDURE_NAME",
                    "","","","REMARKS","PROCEDURE_TYPE"}));
            assertTrue(rs.next());
            assertEquals("jtds_spmeta", rs.getString(3));
            //
            rs = dbmd.getSchemas();
            if (net.sourceforge.jtds.jdbc.Driver.JDBC3) {
                assertTrue(checkColumnNames(rs, new String[]{"TABLE_SCHEM","TABLE_CATALOG"}));
            } else {
                assertTrue(checkColumnNames(rs, new String[]{"TABLE_SCHEM"}));
            }
            assertTrue(rs.next());
            //
            rs = dbmd.getTablePrivileges(null, null, "jTDS_META");
            assertTrue(checkColumnNames(rs, new String[]{"TABLE_CAT", "TABLE_SCHEM", "TABLE_NAME",
                        "GRANTOR", "GRANTEE","PRIVILEGE", "IS_GRANTABLE"}));
            assertTrue(rs.next());
            assertTrue(rs.getString(6).equals("INSERT") ||
                       rs.getString(6).equals("UPDATE") ||
                       rs.getString(6).equals("DELETE") ||
                       rs.getString(6).equals("SELECT"));
            //
            rs = dbmd.getTables(null, null, "jTDS_META", new String[]{"TABLE"});
            assertTrue(checkColumnNames(rs, new String[]{"TABLE_CAT", "TABLE_SCHEM", "TABLE_NAME",
                    "TABLE_TYPE","REMARKS","TYPE_CAT","TYPE_SCHEM","TYPE_NAME",
                    "SELF_REFERENCING_COL_NAME","REF_GENERATION"}));
            assertTrue(rs.next());
            assertEquals("jTDS_META", rs.getString(3));
            //
            rs = dbmd.getTableTypes();
            assertTrue(checkColumnNames(rs, new String[]{"TABLE_TYPE"}));
            assertTrue(rs.next());
            assertEquals("SYSTEM TABLE", rs.getString(1));
            //
            rs = dbmd.getTypeInfo();
            assertTrue(checkColumnNames(rs, new String[]{"TYPE_NAME","DATA_TYPE","PRECISION","LITERAL_PREFIX",
                    "LITERAL_SUFFIX", "CREATE_PARAMS","NULLABLE","CASE_SENSITIVE","SEARCHABLE",
                    "UNSIGNED_ATTRIBUTE","FIXED_PREC_SCALE","AUTO_INCREMENT","LOCAL_TYPE_NAME",
                    "MINIMUM_SCALE","MAXIMUM_SCALE","SQL_DATA_TYPE","SQL_DATETIME_SUB","NUM_PREC_RADIX"}));
            while (rs.next()) {
                if (rs.getString(1).equalsIgnoreCase("nvarchar")) {
                    assertEquals(java.sql.Types.VARCHAR, rs.getInt(2));
                }
            }
            //
            rs = dbmd.getUDTs(null, null, "%", null);
            assertTrue(checkColumnNames(rs, new String[]{"TYPE_CAT", "TYPE_SCHEM", "TYPE_NAME", "CLASS_NAME",
                    "DATA_TYPE","REMARKS","BASE_TYPE"}));
            assertFalse(rs.next());
            //
            rs = dbmd.getVersionColumns(null, null, "jTDS_META");
            assertTrue(checkColumnNames(rs, new String[]{"SCOPE", "COLUMN_NAME","DATA_TYPE","TYPE_NAME",
                      "COLUMN_SIZE","BUFFER_LENGTH","DECIMAL_DIGITS","PSEUDO_COLUMN"}));
            assertTrue(rs.next());
            assertEquals("ts", rs.getString(2));
        } finally {
            dropTable("jTDS_META2");
            dropTable("jTDS_META");
            dropProcedure("jtds_spmeta");
        }
    }

    /**
     * Test for bug [974036] Bug in 0.8rc1 DatabaseMetaData method getTableTypes()
     */
    public void testGetTableTypesOrder() throws Exception {
        DatabaseMetaData dmd = con.getMetaData();
        ResultSet rs = dmd.getTableTypes();
        String previousType = "";

        while (rs.next()) {
            String type = rs.getString(1);

            assertTrue(type.compareTo(previousType) >= 0);
            previousType = type;
        }

        rs.close();
    }

    /**
     * Test for bug [998765] Exception with Sybase and metaData.getTables()
     */
    public void testGetTables() throws Exception {
        DatabaseMetaData dmd = con.getMetaData();
        ResultSet rs = dmd.getTables(null, null, null, null);

        assertNotNull(rs);

        rs.close();
    }

    /**
     * Test for bug [1120168] jTDS 101 - TDS data type 0 invalid.
     */
    public void testGetColumnsMetaData() throws Exception {
        DatabaseMetaData dmd = con.getMetaData();
        ResultSet rs = dmd.getColumns(null, null, "Table doesn't exist", null);

        assertNotNull(rs);

        // Obtain the ResultSetMetaData for the dummy CachedResultSet
        ResultSetMetaData rsmd = rs.getMetaData();

        // Now call all methods and make sure they don't crash
        // For some of them also make simple tests
        assertNotNull(rsmd.getCatalogName(1));
        assertNotNull(rsmd.getColumnClassName(1));