metadataregressiontest.java

在资料浩瀚的互联网中

     * Tests whether bogus parameters break Driver.getPropertyInfo().
     *
     * @throws Exception if an error occurs.
     */
    public void testGetPropertyInfo() throws Exception {
        new Driver().getPropertyInfo("", null);
    }

    /**
     * Tests whether ResultSetMetaData returns correct info for CHAR/VARCHAR
     * columns.
     *
     * @throws Exception if the test fails
     */
    public void testIsCaseSensitive() throws Exception {
        try {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS testIsCaseSensitive");
            this.stmt.executeUpdate(
                "CREATE TABLE testIsCaseSensitive (bin_char CHAR(1) BINARY, bin_varchar VARCHAR(64) BINARY, ci_char CHAR(1), ci_varchar VARCHAR(64))");
            this.rs = this.stmt.executeQuery(
                    "SELECT bin_char, bin_varchar, ci_char, ci_varchar FROM testIsCaseSensitive");

            ResultSetMetaData rsmd = this.rs.getMetaData();
            assertTrue(rsmd.isCaseSensitive(1));
            assertTrue(rsmd.isCaseSensitive(2));
            assertTrue(!rsmd.isCaseSensitive(3));
            assertTrue(!rsmd.isCaseSensitive(4));
        } finally {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS testIsCaseSensitive");
        }

        if (versionMeetsMinimum(4, 1)) {
            try {
                this.stmt.executeUpdate(
                    "DROP TABLE IF EXISTS testIsCaseSensitiveCs");
                this.stmt.executeUpdate("CREATE TABLE testIsCaseSensitiveCs ("
                    + "bin_char CHAR(1) CHARACTER SET latin1 COLLATE latin1_general_cs,"
                    + "bin_varchar VARCHAR(64) CHARACTER SET latin1 COLLATE latin1_general_cs,"
                    + "ci_char CHAR(1) CHARACTER SET latin1 COLLATE latin1_general_ci,"
                    + "ci_varchar VARCHAR(64) CHARACTER SET latin1 COLLATE latin1_general_ci, "
                    + "bin_tinytext TINYTEXT CHARACTER SET latin1 COLLATE latin1_general_cs,"
                    + "bin_text TEXT CHARACTER SET latin1 COLLATE latin1_general_cs,"
                    + "bin_med_text MEDIUMTEXT CHARACTER SET latin1 COLLATE latin1_general_cs,"
                    + "bin_long_text LONGTEXT CHARACTER SET latin1 COLLATE latin1_general_cs,"
                    + "ci_tinytext TINYTEXT CHARACTER SET latin1 COLLATE latin1_general_ci,"
                    + "ci_text TEXT CHARACTER SET latin1 COLLATE latin1_general_ci,"
                    + "ci_med_text MEDIUMTEXT CHARACTER SET latin1 COLLATE latin1_general_ci,"
                    + "ci_long_text LONGTEXT CHARACTER SET latin1 COLLATE latin1_general_ci)");

                this.rs = this.stmt.executeQuery(
                        "SELECT bin_char, bin_varchar, ci_char, ci_varchar, bin_tinytext, bin_text, bin_med_text, bin_long_text, ci_tinytext, ci_text, ci_med_text, ci_long_text FROM testIsCaseSensitiveCs");

                ResultSetMetaData rsmd = this.rs.getMetaData();
                assertTrue(rsmd.isCaseSensitive(1));
                assertTrue(rsmd.isCaseSensitive(2));
                assertTrue(!rsmd.isCaseSensitive(3));
                assertTrue(!rsmd.isCaseSensitive(4));

                assertTrue(rsmd.isCaseSensitive(5));
                assertTrue(rsmd.isCaseSensitive(6));
                assertTrue(rsmd.isCaseSensitive(7));
                assertTrue(rsmd.isCaseSensitive(8));

                assertTrue(!rsmd.isCaseSensitive(9));
                assertTrue(!rsmd.isCaseSensitive(10));
                assertTrue(!rsmd.isCaseSensitive(11));
                assertTrue(!rsmd.isCaseSensitive(12));
            } finally {
                this.stmt.executeUpdate(
                    "DROP TABLE IF EXISTS testIsCaseSensitiveCs");
            }
        }
    }

    /**
     * Tests whether or not DatabaseMetaData.getColumns() returns the correct
     * java.sql.Types info.
     *
     * @throws Exception if the test fails.
     */
    public void testLongText() throws Exception {
        try {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS testLongText");
            this.stmt.executeUpdate(
                "CREATE TABLE testLongText (field1 LONGTEXT)");

            this.rs = this.conn.getMetaData().getColumns(this.conn.getCatalog(),
                    null, "testLongText", "%");

            this.rs.next();

            assertTrue(this.rs.getInt("DATA_TYPE") == java.sql.Types.LONGVARCHAR);
        } finally {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS testLongText");
        }
    }

    /**
     * Tests for types being returned correctly
     *
     * @throws Exception if an error occurs.
     */
    public void testTypes() throws Exception {
        try {
        	this.stmt.execute("DROP TABLE IF EXISTS typesRegressTest");
        	this.stmt.execute("CREATE TABLE typesRegressTest ("
                + "varcharField VARCHAR(32)," + "charField CHAR(2),"
                + "enumField ENUM('1','2')," + "setField  SET('1','2','3'),"
                + "tinyblobField TINYBLOB," + "mediumBlobField MEDIUMBLOB,"
                + "longblobField LONGBLOB," + "blobField BLOB)");

        	this.rs = this.stmt.executeQuery("SELECT * from typesRegressTest");

            ResultSetMetaData rsmd = this.rs.getMetaData();

            int numCols = rsmd.getColumnCount();

            for (int i = 0; i < numCols; i++) {
                String columnName = rsmd.getColumnName(i + 1);
                String columnTypeName = rsmd.getColumnTypeName(i + 1);
                System.out.println(columnName + " -> " + columnTypeName);
            }
        } finally {
        	this.stmt.execute("DROP TABLE IF EXISTS typesRegressTest");
        }
    }
    
    /**
     * Tests fix for BUG#4742, 'DOUBLE' mapped twice in getTypeInfo().
     * 
     * @throws Exception if the test fails.
     */
    public void testBug4742() throws Exception {
        HashMap clashMap = new HashMap();

        this.rs = this.conn.getMetaData().getTypeInfo();

        while (this.rs.next()) {
            String name = this.rs.getString(1);
            assertTrue("Type represented twice in type info, '" + name + "'.",
                !clashMap.containsKey(name));
            clashMap.put(name, name);
        }
    }

	/**
	 * Tests fix for BUG#4138, getColumns() returns incorrect JDBC 
	 * type for unsigned columns.
	 * 
	 * @throws Exception if the test fails.
	 */
	public void testBug4138() throws Exception {
		try {
			String[] typesToTest = new String[] {"TINYINT", "SMALLINT", "MEDIUMINT", "INTEGER",
					"BIGINT", "FLOAT", "DOUBLE", "DECIMAL"};
			
			short[] jdbcMapping = new short[] { Types.TINYINT, Types.SMALLINT, 
					Types.INTEGER, Types.INTEGER, Types.BIGINT, Types.REAL, Types.DOUBLE, Types.DECIMAL};
			
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug4138");
			
			StringBuffer createBuf = new StringBuffer();
			
			createBuf.append("CREATE TABLE testBug4138 (");
			
			boolean firstColumn = true;
			
			for (int i = 0; i < typesToTest.length; i++) {
				if (!firstColumn) {
					createBuf.append(", ");
				} else {
					firstColumn = false;
				}
				
				createBuf.append("field");
				createBuf.append((i + 1));
				createBuf.append(" ");
				createBuf.append(typesToTest[i]);
				createBuf.append(" UNSIGNED");
			}
			createBuf.append(")");
			this.stmt.executeUpdate(createBuf.toString());
			
			DatabaseMetaData dbmd = this.conn.getMetaData();
			this.rs = dbmd.getColumns(this.conn.getCatalog(), null, "testBug4138", "field%");
			
			assertTrue(this.rs.next());
			
			for (int i = 0; i < typesToTest.length; i++) {
				assertTrue("JDBC Data Type of " + this.rs.getShort("DATA_TYPE") + 
						" for MySQL type '" + this.rs.getString("TYPE_NAME") + 
						"' from 'DATA_TYPE' column does not match expected value of " + 
						jdbcMapping[i] + ".",
						jdbcMapping[i] == this.rs.getShort("DATA_TYPE"));
				this.rs.next();
			}
			
			this.rs.close();
			
			StringBuffer queryBuf = new StringBuffer("SELECT ");
			firstColumn = true;
			
			for (int i = 0; i < typesToTest.length; i++) {
				if (!firstColumn) {
					queryBuf.append(", ");
				} else {
					firstColumn = false;
				}
				
				queryBuf.append("field");
				queryBuf.append((i + 1));
			}
			
			queryBuf.append(" FROM testBug4138");
			
			this.rs = this.stmt.executeQuery(queryBuf.toString());
			
			ResultSetMetaData rsmd = this.rs.getMetaData();
			
			for (int i = 0; i < typesToTest.length; i++) {
			
				assertTrue(jdbcMapping[i] == rsmd.getColumnType( i + 1));
				String desiredTypeName = typesToTest[i] + " unsigned";
				
				assertTrue(rsmd.getColumnTypeName((i + 1)) + " != " + desiredTypeName, desiredTypeName.equalsIgnoreCase(rsmd.getColumnTypeName(i + 1)));
			}
		} finally {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug4138");
		}
	}

	/**
	 * Here for housekeeping only, the test is actually in testBug4138().
	 * 
	 * @throws Exception if the test fails.
	 */
	public void testBug4860() throws Exception {
		testBug4138();
	}

	/**
	 * Tests fix for BUG#4880 - RSMD.getPrecision() returns '0' for 
	 * non-numeric types.
	 * 
	 * Why-oh-why is this not in the spec, nor the api-docs, but in 
	 * some 'optional' book, _and_ it is a variance from both ODBC and
	 * the ANSI SQL standard :p
	 * 
	 * (from the CTS testsuite)....
	 * 
	 * The getPrecision(int colindex) method returns an integer value
	 * representing the number of decimal digits for number types,maximum
	 * length in characters for character types,maximum length in bytes
	 * for JDBC binary datatypes.
	 * 
	 * (See Section 27.3 of JDBC 2.0 API Reference & Tutorial 2nd edition)
	 *
	 * @throws Exception if the test fails.
	 */
	
	public void testBug4880() throws Exception {
		try {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug4880");
			this.stmt.executeUpdate("CREATE TABLE testBug4880 (field1 VARCHAR(80), field2 TINYBLOB, field3 BLOB, field4 MEDIUMBLOB, field5 LONGBLOB)");
			this.rs = this.stmt.executeQuery("SELECT field1, field2, field3, field4, field5 FROM testBug4880");
			ResultSetMetaData rsmd = this.rs.getMetaData();
			
			assertTrue(80 == rsmd.getPrecision(1));
			assertTrue(Types.VARCHAR == rsmd.getColumnType(1));
			assertTrue(80 == rsmd.getColumnDisplaySize(1));
	
			assertTrue(255 == rsmd.getPrecision(2));
			assertTrue(Types.VARBINARY == rsmd.getColumnType(2));
			assertTrue("TINYBLOB".equalsIgnoreCase(rsmd.getColumnTypeName(2)));
			assertTrue(255 == rsmd.getColumnDisplaySize(2));
	
			assertTrue(65535 == rsmd.getPrecision(3));
			assertTrue(Types.LONGVARBINARY == rsmd.getColumnType(3));
			assertTrue("BLOB".equalsIgnoreCase(rsmd.getColumnTypeName(3)));
			assertTrue(65535 == rsmd.getColumnDisplaySize(3));
	
			assertTrue(16777215 == rsmd.getPrecision(4));
			assertTrue(Types.LONGVARBINARY == rsmd.getColumnType(4));
			assertTrue("MEDIUMBLOB".equalsIgnoreCase(rsmd.getColumnTypeName(4)));
			assertTrue(16777215 == rsmd.getColumnDisplaySize(4));
	
			// Server doesn't send us enough information to detect LONGBLOB type
			assertTrue(16777215 == rsmd.getPrecision(5));
			assertTrue(Types.LONGVARBINARY == rsmd.getColumnType(5));
			assertTrue("MEDIUMBLOB".equalsIgnoreCase(rsmd.getColumnTypeName(5)));
			assertTrue(16777215 == rsmd.getColumnDisplaySize(5));
		} finally {
			this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug4880");
		}
	}
	
	/** 
	 * Tests fix for BUG#6399, ResultSetMetaData.getDisplaySize() is wrong
	 * for multi-byte charsets.
	 * 
	 * @throws Exception if the test fails
	 */
	public void testBug6399() throws Exception {
		if (versionMeetsMinimum(4, 1)) {
			try {
				this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug6399");
				this.stmt.executeUpdate("CREATE TABLE testBug6399 (field1 CHAR(3) CHARACTER SET UTF8, field2 CHAR(3) CHARACTER SET LATIN1, field3 CHAR(3) CHARACTER SET SJIS)");
				this.stmt.executeUpdate("INSERT INTO testBug6399 VALUES ('a', 'a', 'a')");
				
				this.rs = this.stmt.executeQuery("SELECT field1, field2, field3 FROM testBug6399");
				ResultSetMetaData rsmd = this.rs.getMetaData();
				
				assertTrue(3 == rsmd.getColumnDisplaySize(1));
				assertTrue(3 == rsmd.getColumnDisplaySize(2));
				assertTrue(3 == rsmd.getColumnDisplaySize(3));
			} finally {
				this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug6399");
			}
		}
	}

	/**
	 * Tests fix for BUG#7081, DatabaseMetaData.getIndexInfo() ignoring
	 * 'unique' parameters.
	 * 
	 * @throws Exception if the test fails.
	 */
	public void testBug7081() throws Exception {
		String tableName = "testBug7081";
		
		try {	
			createTable(tableName,"(field1 INT, INDEX(field1))");
			
			DatabaseMetaData dbmd = this.conn.getMetaData();
			this.rs = dbmd.getIndexInfo(this.conn.getCatalog(), null, tableName, true, false);
			assertTrue(!this.rs.next()); // there should be no rows that meet this requirement
			
			this.rs = dbmd.getIndexInfo(this.conn.getCatalog(), null, tableName, false, false);
			assertTrue(this.rs.next()); // there should be one row that meets this requirement
			assertTrue(!this.rs.next());
			
		} finally {
			dropTable(tableName);
		}
	}

	/**
	 * Tests fix for BUG#7033 - PreparedStatements don't encode
	 * Big5 (and other multibyte) character sets correctly in static
	 * SQL strings.
	 * 
	 * @throws Exception if the test fails.
	 */
	public void testBug7033() throws Exception {
		Connection big5Conn = null;
		Statement big5Stmt = null;
		PreparedStatement big5PrepStmt = null;
		
		String testString = "\u5957 \u9910";
		
		try {
			Properties props = new Properties();
			props.setProperty("useUnicode", "true");
			props.setProperty("characterEncoding", "Big5");
			
			big5Conn = getConnectionWithProps(props);
			big5Stmt = big5Conn.createStatement();
			
			byte[] foobar = testString.getBytes("Big5");
			System.out.println(foobar);
			
			this.rs = big5Stmt.executeQuery("select 1 as '\u5957 \u9910'"); 
			String retrString = this.rs.getMetaData().getColumnName(1);
			assertTrue(testString.equals(retrString));
			
			big5PrepStmt = big5Conn.prepareStatement("select 1 as '\u5957 \u9910'");
			this.rs = big5PrepStmt.executeQuery();
			retrString = this.rs.getMetaData().getColumnName(1);
	 		assertTrue(testString.equals(retrString));
		} finally {
			if (this.rs != null) {
				this.rs.close();
				this.rs = null;
			}
			
			if (big5Stmt != null) {
				big5Stmt.close();
				
			}
			
			if (big5PrepStmt != null) {
				big5PrepStmt.close();
			}
			
			if (big5Conn != null) {
				big5Conn.close();
			}
		}
	}
}