metadataregressiontest.java

MySql Java Connector

            this.stmt.executeUpdate("CREATE TABLE testGetColumnsBug1099(" +
                types.toString() + ")");

            dbmd.getColumns(null, this.conn.getCatalog(),
                "testGetColumnsBug1099", "%");
        } finally {
            this.stmt.executeUpdate(
                "DROP TABLE IF EXISTS testGetColumnsBug1099");
        }
    }

    /**
     * Tests whether or not unsigned columns are reported correctly in
     * DBMD.getColumns
     *
     * @throws Exception
     */
    public void testGetColumnsUnsigned() throws Exception {
        try {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS testGetUnsignedCols");
            this.stmt.executeUpdate(
                "CREATE TABLE testGetUnsignedCols (field1 SMALLINT, field2 SMALLINT UNSIGNED)");

            DatabaseMetaData dbmd = this.conn.getMetaData();

            this.rs = dbmd.getColumns(this.conn.getCatalog(), null,
                    "testGetUnsignedCols", "%");

            while (this.rs.next()) {
                System.out.println(rs.getString(6));
            }
        } finally {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS testGetUnsignedCols");
        }
    }

    /**
     * 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");
        }
    }

    /**
     * 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 {
            stmt.execute("DROP TABLE IF EXISTS typesRegressTest");
            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)");

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

            ResultSetMetaData rsmd = 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 {
            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 = 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");
    	}
    }
}