Logo Search packages:      
Sourcecode: mysql-connector-java version File versions  Download package

MetadataTest.java

/*
 Copyright (c) 2002, 2010, 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$
 */
00050 public class MetadataTest extends BaseTestCase {
      // ~ Constructors
      // -----------------------------------------------------------

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

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

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

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

      /**
       * DOCUMENT ME!
       * 
       * @throws SQLException
       *             DOCUMENT ME!
       */
00092       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!
       */
00178       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.
       */
00299       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.
       */
00363       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.
     */
00531     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.
     */
00555     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.
     */
00582     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.
     */
00608     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 tableNames = new HashSet();
            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.
     */
00639     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 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.
     */
00709     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.
     */
00733     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.
     */
00766     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.
     */
00799     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();
                        }
            }
        }
    }
}

Generated by  Doxygen 1.6.0   Back to index