metadatatest.java

mysql的jdbc驱动

/*
 Copyright (C) 2002-2004 MySQL AB

 This program is free software; you can redistribute it and/or modify
 it under the terms of version 2 of the GNU General Public License as 
 published by the Free Software Foundation.

 There are special exceptions to the terms and conditions of the GPL 
 as it is applied to this software. View the full text of the 
 exception in file EXCEPTIONS-CONNECTOR-J in the directory of this 
 software distribution.

 This program is distributed in the hope that it will be useful,
 but WITHOUT ANY WARRANTY; without even the implied warranty of
 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 GNU General Public License for more details.

 You should have received a copy of the GNU General Public License
 along with this program; if not, write to the Free Software
 Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA



 */
package testsuite.simple;

import testsuite.BaseTestCase;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.util.BitSet;
import java.util.Properties;

/**
 * Tests DatabaseMetaData methods.
 * 
 * @author Mark Matthews
 * @version $Id: MetadataTest.java 4124 2005-08-23 16:10:21Z mmatthews $
 */
public class MetadataTest extends BaseTestCase {
	// ~ Constructors
	// -----------------------------------------------------------

	/**
	 * Creates a new MetadataTest object.
	 * 
	 * @param name
	 *            DOCUMENT ME!
	 */
	public MetadataTest(String name) {
		super(name);
	}

	// ~ Methods
	// ----------------------------------------------------------------

	/**
	 * Runs all test cases in this test suite
	 * 
	 * @param args
	 */
	public static void main(String[] args) {
		junit.textui.TestRunner.run(MetadataTest.class);
	}

	/**
	 * DOCUMENT ME!
	 * 
	 * @throws Exception
	 *             DOCUMENT ME!
	 */
	public void setUp() throws Exception {
		super.setUp();
		createTestTable();
	}

	/**
	 * DOCUMENT ME!
	 * 
	 * @throws SQLException
	 *             DOCUMENT ME!
	 */
	public void testForeignKeys() throws SQLException {
		try {
			DatabaseMetaData dbmd = this.conn.getMetaData();
			this.rs = dbmd.getImportedKeys(null, null, "child");

			while (this.rs.next()) {
				String pkColumnName = this.rs.getString("PKCOLUMN_NAME");
				String fkColumnName = this.rs.getString("FKCOLUMN_NAME");
				assertTrue("Primary Key not returned correctly ('"
						+ pkColumnName + "' != 'parent_id')", pkColumnName
						.equalsIgnoreCase("parent_id"));
				assertTrue("Foreign Key not returned correctly ('"
						+ fkColumnName + "' != 'parent_id_fk')", fkColumnName
						.equalsIgnoreCase("parent_id_fk"));
			}

			this.rs.close();
			this.rs = dbmd.getExportedKeys(null, null, "parent");

			while (this.rs.next()) {
				String pkColumnName = this.rs.getString("PKCOLUMN_NAME");
				String fkColumnName = this.rs.getString("FKCOLUMN_NAME");
				String fkTableName = this.rs.getString("FKTABLE_NAME");
				assertTrue("Primary Key not returned correctly ('"
						+ pkColumnName + "' != 'parent_id')", pkColumnName
						.equalsIgnoreCase("parent_id"));
				assertTrue(
						"Foreign Key table not returned correctly for getExportedKeys ('"
								+ fkTableName + "' != 'child')", fkTableName
								.equalsIgnoreCase("child"));
				assertTrue(
						"Foreign Key not returned correctly for getExportedKeys ('"
								+ fkColumnName + "' != 'parent_id_fk')",
						fkColumnName.equalsIgnoreCase("parent_id_fk"));
			}

			this.rs.close();

			this.rs = dbmd.getCrossReference(null, null, "cpd_foreign_3", null,
					null, "cpd_foreign_4");

			assertTrue(this.rs.next());

			String pkColumnName = this.rs.getString("PKCOLUMN_NAME");
			String pkTableName = this.rs.getString("PKTABLE_NAME");
			String fkColumnName = this.rs.getString("FKCOLUMN_NAME");
			String fkTableName = this.rs.getString("FKTABLE_NAME");
			String deleteAction = cascadeOptionToString(this.rs
					.getInt("DELETE_RULE"));
			String updateAction = cascadeOptionToString(this.rs
					.getInt("UPDATE_RULE"));

			assertEquals(pkColumnName, "cpd_foreign_1_id");
			assertEquals(pkTableName, "cpd_foreign_3");
			assertEquals(fkColumnName, "cpd_foreign_1_id");
			assertEquals(fkTableName, "cpd_foreign_4");
			assertEquals(deleteAction, "NO ACTION");
			assertEquals(updateAction, "CASCADE");

			this.rs.close();
			this.rs = null;
		} finally {
			if (this.rs != null) {
				this.rs.close();
				this.rs = null;
			}
		}

	}

	/**
	 * DOCUMENT ME!
	 * 
	 * @throws SQLException
	 *             DOCUMENT ME!
	 */
	public void testGetPrimaryKeys() throws SQLException {
		try {
			DatabaseMetaData dbmd = this.conn.getMetaData();
			this.rs = dbmd.getPrimaryKeys(this.conn.getCatalog(), "",
					"multikey");

			short[] keySeqs = new short[4];
			String[] columnNames = new String[4];
			int i = 0;

			while (this.rs.next()) {
				this.rs.getString("TABLE_NAME");
				columnNames[i] = this.rs.getString("COLUMN_NAME");

				this.rs.getString("PK_NAME");
				keySeqs[i] = this.rs.getShort("KEY_SEQ");
				i++;
			}

			if ((keySeqs[0] != 3) && (keySeqs[1] != 2) && (keySeqs[2] != 4)
					&& (keySeqs[4] != 1)) {
				fail("Keys returned in wrong order");
			}
		} finally {
			if (this.rs != null) {
				try {
					this.rs.close();
				} catch (SQLException sqlEx) {
					/* ignore */
				}
			}
		}
	}

	private static String cascadeOptionToString(int option) {
		switch (option) {
		case DatabaseMetaData.importedKeyCascade:
			return "CASCADE";

		case DatabaseMetaData.importedKeySetNull:
			return "SET NULL";

		case DatabaseMetaData.importedKeyRestrict:
			return "RESTRICT";

		case DatabaseMetaData.importedKeyNoAction:
			return "NO ACTION";
		}

		return "SET DEFAULT";
	}

	private void createTestTable() throws SQLException {
		this.stmt.executeUpdate("DROP TABLE IF EXISTS child");
		this.stmt.executeUpdate("DROP TABLE IF EXISTS parent");
		this.stmt.executeUpdate("DROP TABLE IF EXISTS multikey");
		this.stmt.executeUpdate("DROP TABLE IF EXISTS cpd_foreign_4");
		this.stmt.executeUpdate("DROP TABLE IF EXISTS cpd_foreign_3");
		this.stmt.executeUpdate("DROP TABLE IF EXISTS cpd_foreign_2");
		this.stmt.executeUpdate("DROP TABLE IF EXISTS cpd_foreign_1");
		this.stmt.executeUpdate("DROP TABLE IF EXISTS fktable2");
		this.stmt.executeUpdate("DROP TABLE IF EXISTS fktable1");

		this.stmt
				.executeUpdate("CREATE TABLE parent(parent_id INT NOT NULL, PRIMARY KEY (parent_id)) TYPE=INNODB");
		this.stmt
				.executeUpdate("CREATE TABLE child(child_id INT, parent_id_fk INT, INDEX par_ind (parent_id_fk), "
						+ "FOREIGN KEY (parent_id_fk) REFERENCES parent(parent_id)) TYPE=INNODB");
		this.stmt
				.executeUpdate("CREATE TABLE multikey(d INT NOT NULL, b INT NOT NULL, a INT NOT NULL, c INT NOT NULL, PRIMARY KEY (d, b, a, c))");

		// Test compound foreign keys
		this.stmt.executeUpdate("create table cpd_foreign_1("
				+ "id int(8) not null auto_increment primary key,"
				+ "name varchar(255) not null unique," + "key (id)"
				+ ") type=InnoDB");
		this.stmt.executeUpdate("create table cpd_foreign_2("
				+ "id int(8) not null auto_increment primary key,"
				+ "key (id)," + "name varchar(255)" + ") type=InnoDB");
		this.stmt
				.executeUpdate("create table cpd_foreign_3("
						+ "cpd_foreign_1_id int(8) not null,"
						+ "cpd_foreign_2_id int(8) not null,"
						+ "key(cpd_foreign_1_id),"
						+ "key(cpd_foreign_2_id),"
						+ "primary key (cpd_foreign_1_id, cpd_foreign_2_id),"
						+ "foreign key (cpd_foreign_1_id) references cpd_foreign_1(id),"
						+ "foreign key (cpd_foreign_2_id) references cpd_foreign_2(id)"
						+ ") type=InnoDB");
		this.stmt
				.executeUpdate("create table cpd_foreign_4("
						+ "cpd_foreign_1_id int(8) not null,"
						+ "cpd_foreign_2_id int(8) not null,"
						+ "key(cpd_foreign_1_id),"
						+ "key(cpd_foreign_2_id),"
						+ "primary key (cpd_foreign_1_id, cpd_foreign_2_id),"
						+ "foreign key (cpd_foreign_1_id, cpd_foreign_2_id) "
						+ "references cpd_foreign_3(cpd_foreign_1_id, cpd_foreign_2_id) "