/*
Copyright (c) 2002, 2012, Oracle and/or its affiliates. All rights reserved.
The MySQL Connector/J is licensed under the terms of the GPLv2
<http://www.gnu.org/licenses/old-licenses/gpl-2.0.html>, like most MySQL Connectors.
There are special exceptions to the terms and conditions of the GPLv2 as it is applied to
this software, see the FLOSS License Exception
<http://www.mysql.com/about/legal/licensing/foss-exception.html>.
This program is free software; you can redistribute it and/or modify it under the terms
of the GNU General Public License as published by the Free Software Foundation; version 2
of the License.
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., 51 Franklin St, Fifth
Floor, Boston, MA 02110-1301 USA
*/
package testsuite.simple;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.HashSet;
import java.util.List;
import java.util.Properties;
import java.util.Set;
import testsuite.BaseTestCase;
import com.mysql.jdbc.StringUtils;
/**
* Tests DatabaseMetaData methods.
*
* @author Mark Matthews
* @version $Id$
*/
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();
}
/**
* DOCUMENT ME!
*
* @throws SQLException
* DOCUMENT ME!
*/
public void testForeignKeys() throws SQLException {
try {
createTestTable();
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;
}
this.stmt.executeUpdate("DROP TABLE IF EXISTS child");
this.stmt.executeUpdate("DROP TABLE IF EXISTS parent");
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");
}
}
/**
* DOCUMENT ME!
*
* @throws SQLException
* DOCUMENT ME!
*/
public void testGetPrimaryKeys() throws SQLException {
try {
createTable("multikey", "(d INT NOT NULL, b INT NOT NULL, a INT NOT NULL, c INT NOT NULL, PRIMARY KEY (d, b, a, c))");
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 {
//Needed for previous runs that did not clean-up
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");
createTable("parent", "(parent_id INT NOT NULL, PRIMARY KEY (parent_id))", "INNODB");
createTable("child", "(child_id INT, parent_id_fk INT, INDEX par_ind (parent_id_fk), "
+ "FOREIGN KEY (parent_id_fk) REFERENCES parent(parent_id)) ", "INNODB");
// Test compound foreign keys
try{
createTable("cpd_foreign_1", "("
+ "id int(8) not null auto_increment primary key,"
+ "name varchar(255) not null unique," + "key (id)"
+ ")", "InnoDB");
} catch (SQLException sqlEx) {
if (sqlEx.getMessage().indexOf("max key length") != -1) {
createTable("cpd_foreign_1", "("
+ "id int(8) not null auto_increment primary key,"
+ "name varchar(180) not null unique," + "key (id)"
+ ")", "InnoDB");
}
}
createTable("cpd_foreign_2", "("
+ "id int(8) not null auto_increment primary key,"
+ "key (id)," + "name varchar(255)" + ") ", "InnoDB");
createTable("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)"
+ ") ", "InnoDB");
createTable("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) "
+ "ON DELETE RESTRICT ON UPDATE CASCADE"
+ ") ", "InnoDB");
createTable("fktable1", "(TYPE_ID int not null, TYPE_DESC varchar(32), primary key(TYPE_ID))", "InnoDB");
createTable("fktable2", "(KEY_ID int not null, COF_NAME varchar(32), PRICE float, TYPE_ID int, primary key(KEY_ID), "
+ "index(TYPE_ID), foreign key(TYPE_ID) references fktable1(TYPE_ID)) ", "InnoDB");
}
/**
* Tests the implementation of metadata for views.
*
* This test automatically detects whether or not the server it is running
* against supports the creation of views.
*
* @throws SQLException
* if the test fails.
*/
public void testViewMetaData() throws SQLException {
try {
this.rs = this.conn.getMetaData().getTableTypes();
while (this.rs.next()) {
if ("VIEW".equalsIgnoreCase(this.rs.getString(1))) {
this.stmt
.executeUpdate("DROP VIEW IF EXISTS vTestViewMetaData");
createTable("testViewMetaData", "(field1 INT)");
this.stmt
.executeUpdate("CREATE VIEW vTestViewMetaData AS SELECT field1 FROM testViewMetaData");
ResultSet tablesRs = null;
try {
tablesRs = this.conn.getMetaData().getTables(
this.conn.getCatalog(), null, "%ViewMetaData",
new String[] { "TABLE", "VIEW" });
assertTrue(tablesRs.next());
assertTrue("testViewMetaData".equalsIgnoreCase(tablesRs
.getString(3)));
assertTrue(tablesRs.next());
assertTrue("vTestViewMetaData"
.equalsIgnoreCase(tablesRs.getString(3)));
} finally {
if (tablesRs != null) {
tablesRs.close();
}
}
try {
tablesRs = this.conn.getMetaData().getTables(
this.conn.getCatalog(), null, "%ViewMetaData",
new String[] { "TABLE" });
assertTrue(tablesRs.next());
assertTrue("testViewMetaData".equalsIgnoreCase(tablesRs
.getString(3)));
assertTrue(!tablesRs.next());
} finally {
if (tablesRs != null) {
tablesRs.close();
}
}
break;
}
}
} finally {
if (this.rs != null) {
this.rs.close();
}
this.stmt
.executeUpdate("DROP VIEW IF EXISTS vTestViewMetaData");
}
}
/**
* Tests detection of read-only fields when the server is 4.1.0 or newer.
*
* @throws Exception
* if the test fails.
*/
public void testRSMDIsReadOnly() throws Exception {
try {
this.rs = this.stmt.executeQuery("SELECT 1");
ResultSetMetaData rsmd = this.rs.getMetaData();
if (versionMeetsMinimum(4, 1)) {
assertTrue(rsmd.isReadOnly(1));
try {
createTable("testRSMDIsReadOnly", "(field1 INT)");
this.stmt
.executeUpdate("INSERT INTO testRSMDIsReadOnly VALUES (1)");
this.rs = this.stmt
.executeQuery("SELECT 1, field1 + 1, field1 FROM testRSMDIsReadOnly");
rsmd = this.rs.getMetaData();
assertTrue(rsmd.isReadOnly(1));
assertTrue(rsmd.isReadOnly(2));
assertTrue(!rsmd.isReadOnly(3));
} finally {
}
} else {
assertTrue(rsmd.isReadOnly(1) == false);
}
} finally {
if (this.rs != null) {
this.rs.close();
}
}
}
public void testBitType() throws Exception {
if (versionMeetsMinimum(5, 0, 3)) {
try {
createTable("testBitType", "(field1 BIT, field2 BIT, field3 BIT)");
this.stmt
.executeUpdate("INSERT INTO testBitType VALUES (1, 0, NULL)");
this.rs = this.stmt
.executeQuery("SELECT field1, field2, field3 FROM testBitType");
this.rs.next();
assertTrue(((Boolean) this.rs.getObject(1)).booleanValue());
assertTrue(!((Boolean) this.rs.getObject(2)).booleanValue());
assertEquals(this.rs.getObject(3), null);
System.out.println(this.rs.getObject(1) + ", "
+ this.rs.getObject(2) + ", " + this.rs.getObject(3));
this.rs = this.conn.prepareStatement(
"SELECT field1, field2, field3 FROM testBitType")
.executeQuery();
this.rs.next();
assertTrue(((Boolean) this.rs.getObject(1)).booleanValue());
assertTrue(!((Boolean) this.rs.getObject(2)).booleanValue());
assertEquals(this.rs.getObject(3), null);
byte[] asBytesTrue = this.rs.getBytes(1);
byte[] asBytesFalse = this.rs.getBytes(2);
byte[] asBytesNull = this.rs.getBytes(3);
assertEquals(asBytesTrue[0], 1);
assertEquals(asBytesFalse[0], 0);
assertEquals(asBytesNull, null);
createTable("testBitField", "(field1 BIT(9))");
this.rs = this.stmt
.executeQuery("SELECT field1 FROM testBitField");
System.out.println(this.rs.getMetaData().getColumnClassName(1));
} finally {
}
}
}
public void testSupportsSelectForUpdate() throws Exception {
boolean supportsForUpdate = this.conn.getMetaData()
.supportsSelectForUpdate();
if (this.versionMeetsMinimum(4, 0)) {
assertTrue(supportsForUpdate);
} else {
assertTrue(!supportsForUpdate);
}
}
public void testTinyint1IsBit() throws Exception {
String tableName = "testTinyint1IsBit";
// Can't use 'BIT' or boolean
createTable(tableName, "(field1 TINYINT(1))");
this.stmt.executeUpdate("INSERT INTO " + tableName + " VALUES (1)");
Properties props = new Properties();
props.setProperty("tinyint1IsBit", "true");
props.setProperty("transformedBitIsBoolean", "true");
Connection boolConn = getConnectionWithProps(props);
this.rs = boolConn.createStatement().executeQuery(
"SELECT field1 FROM " + tableName);
checkBitOrBooleanType(false);
this.rs = boolConn.prepareStatement("SELECT field1 FROM " + tableName)
.executeQuery();
checkBitOrBooleanType(false);
this.rs = boolConn.getMetaData().getColumns(boolConn.getCatalog(),
null, tableName, "field1");
assertTrue(this.rs.next());
if (versionMeetsMinimum(4, 1)) {
assertEquals(Types.BOOLEAN, this.rs.getInt("DATA_TYPE"));
} else {
assertEquals(Types.BIT, this.rs.getInt("DATA_TYPE"));
}
if (versionMeetsMinimum(4, 1)) {
assertEquals("BOOLEAN", this.rs.getString("TYPE_NAME"));
} else {
assertEquals("BIT", this.rs.getString("TYPE_NAME"));
}
props.clear();
props.setProperty("transformedBitIsBoolean", "false");
props.setProperty("tinyint1IsBit", "true");
Connection bitConn = getConnectionWithProps(props);
this.rs = bitConn.createStatement().executeQuery(
"SELECT field1 FROM " + tableName);
checkBitOrBooleanType(true);
this.rs = bitConn.prepareStatement("SELECT field1 FROM " + tableName)
.executeQuery();
checkBitOrBooleanType(true);
this.rs = bitConn.getMetaData().getColumns(boolConn.getCatalog(), null,
tableName, "field1");
assertTrue(this.rs.next());
assertEquals(Types.BIT, this.rs.getInt("DATA_TYPE"));
assertEquals("BIT", this.rs.getString("TYPE_NAME"));
}
private void checkBitOrBooleanType(boolean usingBit) throws SQLException {
assertTrue(this.rs.next());
assertEquals("java.lang.Boolean", this.rs.getObject(1).getClass()
.getName());
if (!usingBit) {
if (versionMeetsMinimum(4, 1)) {
assertEquals(Types.BOOLEAN, this.rs.getMetaData()
.getColumnType(1));
} else {
assertEquals(Types.BIT, this.rs.getMetaData().getColumnType(1));
}
} else {
assertEquals(Types.BIT, this.rs.getMetaData().getColumnType(1));
}
assertEquals("java.lang.Boolean", this.rs.getMetaData()
.getColumnClassName(1));
}
/**
* Tests the implementation of Information Schema for primary keys.
*/
public void testGetPrimaryKeysUsingInfoShcema() throws Exception {
if (versionMeetsMinimum(5, 0, 7)) {
createTable("t1", "(c1 int(1) primary key)");
Properties props = new Properties();
props.put("useInformationSchema", "true");
Connection conn1 = null;
try {
conn1 = getConnectionWithProps(props);
DatabaseMetaData metaData = conn1.getMetaData();
this.rs = metaData.getPrimaryKeys(null, null, "t1");
this.rs.next();
assertEquals("t1", this.rs.getString("TABLE_NAME"));
assertEquals("c1", this.rs.getString("COLUMN_NAME"));
} finally {
if (conn1 != null) {
conn1.close();
}
}
}
}
/**
* Tests the implementation of Information Schema for index info.
*/
public void testGetIndexInfoUsingInfoSchema() throws Exception {
if (versionMeetsMinimum(5, 0, 7)) {
createTable("t1", "(c1 int(1))");
this.stmt.executeUpdate("CREATE INDEX index1 ON t1 (c1)");
Connection conn1 = null;
try {
conn1 = getConnectionWithProps("useInformationSchema=true");
DatabaseMetaData metaData = conn1.getMetaData();
this.rs = metaData.getIndexInfo(conn1.getCatalog(), null, "t1", false, true);
this.rs.next();
assertEquals("t1", this.rs.getString("TABLE_NAME"));
assertEquals("c1", this.rs.getString("COLUMN_NAME"));
assertEquals("1", this.rs.getString("NON_UNIQUE"));
assertEquals("index1", this.rs.getString("INDEX_NAME"));
} finally {
if (conn1 != null) {
conn1.close();
}
}
}
}
/**
* Tests the implementation of Information Schema for columns.
*/
public void testGetColumnsUsingInfoSchema() throws Exception {
if (versionMeetsMinimum(5, 0, 7)) {
createTable("t1", "(c1 char(1))");
Properties props = new Properties();
props.put("useInformationSchema", "true");
Connection conn1 = null;
try {
conn1 = getConnectionWithProps(props);
DatabaseMetaData metaData = conn1.getMetaData();
this.rs = metaData.getColumns(null, null, "t1", null);
this.rs.next();
assertEquals("t1", this.rs.getString("TABLE_NAME"));
assertEquals("c1", this.rs.getString("COLUMN_NAME"));
assertEquals("CHAR", this.rs.getString("TYPE_NAME"));
assertEquals("1", this.rs.getString("COLUMN_SIZE"));
} finally {
if (conn1 != null) {
conn1.close();
}
}
}
}
/**
* Tests the implementation of Information Schema for tables.
*/
public void testGetTablesUsingInfoSchema() throws Exception {
if (versionMeetsMinimum(5, 0, 7)) {
createTable("`t1-1`", "(c1 char(1))");
createTable("`t1-2`", "(c1 char(1))");
createTable("`t2`", "(c1 char(1))");
Set<String> tableNames = new HashSet<String>();
tableNames.add("t1-1");
tableNames.add("t1-2");
Properties props = new Properties();
props.put("useInformationSchema", "true");
Connection conn1 = null;
try {
conn1 = getConnectionWithProps(props);
DatabaseMetaData metaData = conn1.getMetaData();
// pattern matching for table name
this.rs = metaData.getTables(null, null, "t1-_", null);
while (this.rs.next()) {
assertTrue(tableNames.remove(this.rs.getString("TABLE_NAME")));
}
assertTrue(tableNames.isEmpty());
} finally {
if (conn1 != null) {
conn1.close();
}
}
}
}
/**
* Tests the implementation of Information Schema for column privileges.
*/
public void testGetColumnPrivilegesUsingInfoSchema() throws Exception {
String dontRunPropertyName = "com.mysql.jdbc.testsuite.cantGrant";
if (!runTestIfSysPropDefined(dontRunPropertyName)) {
if (versionMeetsMinimum(5, 0, 7)) {
Properties props = new Properties();
props.put("useInformationSchema", "true");
Connection conn1 = null;
Statement stmt1 = null;
String userHostQuoted = null;
boolean grantFailed = true;
try {
conn1 = getConnectionWithProps(props);
stmt1 = conn1.createStatement();
createTable("t1", "(c1 int)");
this.rs = stmt1.executeQuery("SELECT USER()");
this.rs.next();
String user = this.rs.getString(1);
List<String> userHost = StringUtils.split(user, "@", false);
if (userHost.size() < 2) {
fail("This test requires a JDBC URL with a user, and won't work with the anonymous user. " +
"You can skip this test by setting the system property " + dontRunPropertyName);
}
userHostQuoted = "'" + userHost.get(0) + "'@'" + userHost.get(1) + "'";
try {
stmt1.executeUpdate("GRANT update (c1) on t1 to " + userHostQuoted);
grantFailed = false;
} catch (SQLException sqlEx) {
fail("This testcase needs to be run with a URL that allows the user to issue GRANTs "
+ " in the current database. You can skip this test by setting the system property \""
+ dontRunPropertyName + "\".");
}
if (!grantFailed) {
DatabaseMetaData metaData = conn1.getMetaData();
this.rs = metaData.getColumnPrivileges(null, null, "t1", null);
this.rs.next();
assertEquals("t1", this.rs.getString("TABLE_NAME"));
assertEquals("c1", this.rs.getString("COLUMN_NAME"));
assertEquals(userHostQuoted, this.rs.getString("GRANTEE"));
assertEquals("UPDATE", this.rs.getString("PRIVILEGE"));
}
} finally {
if (stmt1 != null) {
if (!grantFailed) {
stmt1.executeUpdate("REVOKE UPDATE (c1) ON t1 FROM " + userHostQuoted);
}
stmt1.close();
}
if (conn1 != null) {
conn1.close();
}
}
}
}
}
/**
* Tests the implementation of Information Schema for description
* of stored procedures available in a catalog.
*/
public void testGetProceduresUsingInfoSchema() throws Exception {
if (versionMeetsMinimum(5, 0, 7)) {
createProcedure("sp1", "()\n BEGIN\n" + "SELECT 1;" + "end\n");
Properties props = new Properties();
props.put("useInformationSchema", "true");
Connection conn1 = null;
try {
conn1 = getConnectionWithProps(props);
DatabaseMetaData metaData = conn1.getMetaData();
this.rs = metaData.getProcedures(null, null, "sp1");
this.rs.next();
assertEquals("sp1", this.rs.getString("PROCEDURE_NAME"));
assertEquals("1", this.rs.getString("PROCEDURE_TYPE"));
} finally {
if (conn1 != null) {
conn1.close();
}
}
}
}
/**
* Tests the implementation of Information Schema for foreign key.
*/
public void testGetCrossReferenceUsingInfoSchema() throws Exception {
if (versionMeetsMinimum(5, 0, 7)) {
this.stmt.executeUpdate("DROP TABLE IF EXISTS child");
this.stmt.executeUpdate("DROP TABLE If EXISTS parent");
this.stmt.executeUpdate("CREATE TABLE parent(id INT NOT NULL, "
+ "PRIMARY KEY (id)) ENGINE=INNODB");
this.stmt.executeUpdate("CREATE TABLE child(id INT, parent_id INT, "
+ "FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE SET NULL) ENGINE=INNODB");
Properties props = new Properties();
props.put("useInformationSchema", "true");
Connection conn1 = null;
try {
conn1 = getConnectionWithProps(props);
DatabaseMetaData metaData = conn1.getMetaData();
this.rs = metaData.getCrossReference(null, null, "parent", null, null, "child");
this.rs.next();
assertEquals("parent", this.rs.getString("PKTABLE_NAME"));
assertEquals("id", this.rs.getString("PKCOLUMN_NAME"));
assertEquals("child", this.rs.getString("FKTABLE_NAME"));
assertEquals("parent_id", this.rs.getString("FKCOLUMN_NAME"));
} finally {
this.stmt.executeUpdate("DROP TABLE IF EXISTS child");
this.stmt.executeUpdate("DROP TABLE If EXISTS parent");
if (conn1 != null) {
conn1.close();
}
}
}
}
/**
* Tests the implementation of Information Schema for foreign key.
*/
public void testGetExportedKeysUsingInfoSchema() throws Exception {
if (versionMeetsMinimum(5, 0, 7)) {
this.stmt.executeUpdate("DROP TABLE IF EXISTS child");
this.stmt.executeUpdate("DROP TABLE If EXISTS parent");
this.stmt.executeUpdate("CREATE TABLE parent(id INT NOT NULL, "
+ "PRIMARY KEY (id)) ENGINE=INNODB");
this.stmt.executeUpdate("CREATE TABLE child(id INT, parent_id INT, "
+ "FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE SET NULL) ENGINE=INNODB");
Properties props = new Properties();
props.put("useInformationSchema", "true");
Connection conn1 = null;
try {
conn1 = getConnectionWithProps(props);
DatabaseMetaData metaData = conn1.getMetaData();
this.rs = metaData.getExportedKeys(null, null, "parent");
this.rs.next();
assertEquals("parent", this.rs.getString("PKTABLE_NAME"));
assertEquals("id", this.rs.getString("PKCOLUMN_NAME"));
assertEquals("child", this.rs.getString("FKTABLE_NAME"));
assertEquals("parent_id", this.rs.getString("FKCOLUMN_NAME"));
} finally {
this.stmt.executeUpdate("DROP TABLE IF EXISTS child");
this.stmt.executeUpdate("DROP TABLE If EXISTS parent");
if (conn1 != null) {
conn1.close();
}
}
}
}
/**
* Tests the implementation of Information Schema for foreign key.
*/
public void testGetImportedKeysUsingInfoSchema() throws Exception {
if (versionMeetsMinimum(5, 0, 7)) {
this.stmt.executeUpdate("DROP TABLE IF EXISTS child");
this.stmt.executeUpdate("DROP TABLE If EXISTS parent");
this.stmt.executeUpdate("CREATE TABLE parent(id INT NOT NULL, "
+ "PRIMARY KEY (id)) ENGINE=INNODB");
this.stmt.executeUpdate("CREATE TABLE child(id INT, parent_id INT, "
+ "FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE SET NULL) ENGINE=INNODB");
Properties props = new Properties();
props.put("useInformationSchema", "true");
Connection conn1 = null;
try {
conn1 = getConnectionWithProps(props);
DatabaseMetaData metaData = conn1.getMetaData();
this.rs = metaData.getImportedKeys(null, null, "child");
this.rs.next();
assertEquals("parent", this.rs.getString("PKTABLE_NAME"));
assertEquals("id", this.rs.getString("PKCOLUMN_NAME"));
assertEquals("child", this.rs.getString("FKTABLE_NAME"));
assertEquals("parent_id", this.rs.getString("FKCOLUMN_NAME"));
} finally {
this.stmt.executeUpdate("DROP TABLE IF EXISTS child");
this.stmt.executeUpdate("DROP TABLE If EXISTS parent");
if (conn1 != null) {
conn1.close();
}
}
}
}
}