001: /*
002: Copyright (C) 2002-2004 MySQL AB
003:
004: This program is free software; you can redistribute it and/or modify
005: it under the terms of version 2 of the GNU General Public License as
006: published by the Free Software Foundation.
007:
008: There are special exceptions to the terms and conditions of the GPL
009: as it is applied to this software. View the full text of the
010: exception in file EXCEPTIONS-CONNECTOR-J in the directory of this
011: software distribution.
012:
013: This program is distributed in the hope that it will be useful,
014: but WITHOUT ANY WARRANTY; without even the implied warranty of
015: MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
016: GNU General Public License for more details.
017:
018: You should have received a copy of the GNU General Public License
019: along with this program; if not, write to the Free Software
020: Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
021:
022:
023:
024: */
025: package testsuite.simple;
026:
027: import java.sql.Connection;
028: import java.sql.DatabaseMetaData;
029: import java.sql.ResultSet;
030: import java.sql.ResultSetMetaData;
031: import java.sql.SQLException;
032: import java.sql.Statement;
033: import java.sql.Types;
034: import java.util.HashSet;
035: import java.util.List;
036: import java.util.Properties;
037: import java.util.Set;
038:
039: import com.mysql.jdbc.StringUtils;
040:
041: import testsuite.BaseTestCase;
042:
043: /**
044: * Tests DatabaseMetaData methods.
045: *
046: * @author Mark Matthews
047: * @version $Id: MetadataTest.java 5828 2006-10-05 16:51:31Z mmatthews $
048: */
049: public class MetadataTest extends BaseTestCase {
050: // ~ Constructors
051: // -----------------------------------------------------------
052:
053: /**
054: * Creates a new MetadataTest object.
055: *
056: * @param name
057: * DOCUMENT ME!
058: */
059: public MetadataTest(String name) {
060: super (name);
061: }
062:
063: // ~ Methods
064: // ----------------------------------------------------------------
065:
066: /**
067: * Runs all test cases in this test suite
068: *
069: * @param args
070: */
071: public static void main(String[] args) {
072: junit.textui.TestRunner.run(MetadataTest.class);
073: }
074:
075: /**
076: * DOCUMENT ME!
077: *
078: * @throws Exception
079: * DOCUMENT ME!
080: */
081: public void setUp() throws Exception {
082: super .setUp();
083: createTestTable();
084: }
085:
086: /**
087: * DOCUMENT ME!
088: *
089: * @throws SQLException
090: * DOCUMENT ME!
091: */
092: public void testForeignKeys() throws SQLException {
093: try {
094: DatabaseMetaData dbmd = this .conn.getMetaData();
095: this .rs = dbmd.getImportedKeys(null, null, "child");
096:
097: while (this .rs.next()) {
098: String pkColumnName = this .rs
099: .getString("PKCOLUMN_NAME");
100: String fkColumnName = this .rs
101: .getString("FKCOLUMN_NAME");
102: assertTrue("Primary Key not returned correctly ('"
103: + pkColumnName + "' != 'parent_id')",
104: pkColumnName.equalsIgnoreCase("parent_id"));
105: assertTrue("Foreign Key not returned correctly ('"
106: + fkColumnName + "' != 'parent_id_fk')",
107: fkColumnName.equalsIgnoreCase("parent_id_fk"));
108: }
109:
110: this .rs.close();
111: this .rs = dbmd.getExportedKeys(null, null, "parent");
112:
113: while (this .rs.next()) {
114: String pkColumnName = this .rs
115: .getString("PKCOLUMN_NAME");
116: String fkColumnName = this .rs
117: .getString("FKCOLUMN_NAME");
118: String fkTableName = this .rs.getString("FKTABLE_NAME");
119: assertTrue("Primary Key not returned correctly ('"
120: + pkColumnName + "' != 'parent_id')",
121: pkColumnName.equalsIgnoreCase("parent_id"));
122: assertTrue(
123: "Foreign Key table not returned correctly for getExportedKeys ('"
124: + fkTableName + "' != 'child')",
125: fkTableName.equalsIgnoreCase("child"));
126: assertTrue(
127: "Foreign Key not returned correctly for getExportedKeys ('"
128: + fkColumnName + "' != 'parent_id_fk')",
129: fkColumnName.equalsIgnoreCase("parent_id_fk"));
130: }
131:
132: this .rs.close();
133:
134: this .rs = dbmd.getCrossReference(null, null,
135: "cpd_foreign_3", null, null, "cpd_foreign_4");
136:
137: assertTrue(this .rs.next());
138:
139: String pkColumnName = this .rs.getString("PKCOLUMN_NAME");
140: String pkTableName = this .rs.getString("PKTABLE_NAME");
141: String fkColumnName = this .rs.getString("FKCOLUMN_NAME");
142: String fkTableName = this .rs.getString("FKTABLE_NAME");
143: String deleteAction = cascadeOptionToString(this .rs
144: .getInt("DELETE_RULE"));
145: String updateAction = cascadeOptionToString(this .rs
146: .getInt("UPDATE_RULE"));
147:
148: assertEquals(pkColumnName, "cpd_foreign_1_id");
149: assertEquals(pkTableName, "cpd_foreign_3");
150: assertEquals(fkColumnName, "cpd_foreign_1_id");
151: assertEquals(fkTableName, "cpd_foreign_4");
152: assertEquals(deleteAction, "NO ACTION");
153: assertEquals(updateAction, "CASCADE");
154:
155: this .rs.close();
156: this .rs = null;
157: } finally {
158: if (this .rs != null) {
159: this .rs.close();
160: this .rs = null;
161: }
162: }
163:
164: }
165:
166: /**
167: * DOCUMENT ME!
168: *
169: * @throws SQLException
170: * DOCUMENT ME!
171: */
172: public void testGetPrimaryKeys() throws SQLException {
173: try {
174: DatabaseMetaData dbmd = this .conn.getMetaData();
175: this .rs = dbmd.getPrimaryKeys(this .conn.getCatalog(), "",
176: "multikey");
177:
178: short[] keySeqs = new short[4];
179: String[] columnNames = new String[4];
180: int i = 0;
181:
182: while (this .rs.next()) {
183: this .rs.getString("TABLE_NAME");
184: columnNames[i] = this .rs.getString("COLUMN_NAME");
185:
186: this .rs.getString("PK_NAME");
187: keySeqs[i] = this .rs.getShort("KEY_SEQ");
188: i++;
189: }
190:
191: if ((keySeqs[0] != 3) && (keySeqs[1] != 2)
192: && (keySeqs[2] != 4) && (keySeqs[4] != 1)) {
193: fail("Keys returned in wrong order");
194: }
195: } finally {
196: if (this .rs != null) {
197: try {
198: this .rs.close();
199: } catch (SQLException sqlEx) {
200: /* ignore */
201: }
202: }
203: }
204: }
205:
206: private static String cascadeOptionToString(int option) {
207: switch (option) {
208: case DatabaseMetaData.importedKeyCascade:
209: return "CASCADE";
210:
211: case DatabaseMetaData.importedKeySetNull:
212: return "SET NULL";
213:
214: case DatabaseMetaData.importedKeyRestrict:
215: return "RESTRICT";
216:
217: case DatabaseMetaData.importedKeyNoAction:
218: return "NO ACTION";
219: }
220:
221: return "SET DEFAULT";
222: }
223:
224: private void createTestTable() throws SQLException {
225: this .stmt.executeUpdate("DROP TABLE IF EXISTS child");
226: this .stmt.executeUpdate("DROP TABLE IF EXISTS parent");
227: this .stmt.executeUpdate("DROP TABLE IF EXISTS multikey");
228: this .stmt.executeUpdate("DROP TABLE IF EXISTS cpd_foreign_4");
229: this .stmt.executeUpdate("DROP TABLE IF EXISTS cpd_foreign_3");
230: this .stmt.executeUpdate("DROP TABLE IF EXISTS cpd_foreign_2");
231: this .stmt.executeUpdate("DROP TABLE IF EXISTS cpd_foreign_1");
232: this .stmt.executeUpdate("DROP TABLE IF EXISTS fktable2");
233: this .stmt.executeUpdate("DROP TABLE IF EXISTS fktable1");
234:
235: this .stmt
236: .executeUpdate("CREATE TABLE parent(parent_id INT NOT NULL, PRIMARY KEY (parent_id)) TYPE=INNODB");
237: this .stmt
238: .executeUpdate("CREATE TABLE child(child_id INT, parent_id_fk INT, INDEX par_ind (parent_id_fk), "
239: + "FOREIGN KEY (parent_id_fk) REFERENCES parent(parent_id)) TYPE=INNODB");
240: this .stmt
241: .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))");
242:
243: // Test compound foreign keys
244: this .stmt.executeUpdate("create table cpd_foreign_1("
245: + "id int(8) not null auto_increment primary key,"
246: + "name varchar(255) not null unique," + "key (id)"
247: + ") type=InnoDB");
248: this .stmt.executeUpdate("create table cpd_foreign_2("
249: + "id int(8) not null auto_increment primary key,"
250: + "key (id)," + "name varchar(255)" + ") type=InnoDB");
251: this .stmt
252: .executeUpdate("create table cpd_foreign_3("
253: + "cpd_foreign_1_id int(8) not null,"
254: + "cpd_foreign_2_id int(8) not null,"
255: + "key(cpd_foreign_1_id),"
256: + "key(cpd_foreign_2_id),"
257: + "primary key (cpd_foreign_1_id, cpd_foreign_2_id),"
258: + "foreign key (cpd_foreign_1_id) references cpd_foreign_1(id),"
259: + "foreign key (cpd_foreign_2_id) references cpd_foreign_2(id)"
260: + ") type=InnoDB");
261: this .stmt
262: .executeUpdate("create table cpd_foreign_4("
263: + "cpd_foreign_1_id int(8) not null,"
264: + "cpd_foreign_2_id int(8) not null,"
265: + "key(cpd_foreign_1_id),"
266: + "key(cpd_foreign_2_id),"
267: + "primary key (cpd_foreign_1_id, cpd_foreign_2_id),"
268: + "foreign key (cpd_foreign_1_id, cpd_foreign_2_id) "
269: + "references cpd_foreign_3(cpd_foreign_1_id, cpd_foreign_2_id) "
270: + "ON DELETE RESTRICT ON UPDATE CASCADE"
271: + ") type=InnoDB");
272:
273: this .stmt
274: .executeUpdate("create table fktable1 (TYPE_ID int not null, TYPE_DESC varchar(32), primary key(TYPE_ID)) TYPE=InnoDB");
275: this .stmt
276: .executeUpdate("create table fktable2 (KEY_ID int not null, COF_NAME varchar(32), PRICE float, TYPE_ID int, primary key(KEY_ID), "
277: + "index(TYPE_ID), foreign key(TYPE_ID) references fktable1(TYPE_ID)) TYPE=InnoDB");
278: }
279:
280: /**
281: * Tests the implementation of metadata for views.
282: *
283: * This test automatically detects whether or not the server it is running
284: * against supports the creation of views.
285: *
286: * @throws SQLException
287: * if the test fails.
288: */
289: public void testViewMetaData() throws SQLException {
290: try {
291: this .rs = this .conn.getMetaData().getTableTypes();
292:
293: while (this .rs.next()) {
294: if ("VIEW".equalsIgnoreCase(this .rs.getString(1))) {
295:
296: this .stmt
297: .executeUpdate("DROP VIEW IF EXISTS vTestViewMetaData");
298: this .stmt
299: .executeUpdate("DROP TABLE IF EXISTS testViewMetaData");
300: this .stmt
301: .executeUpdate("CREATE TABLE testViewMetaData (field1 INT)");
302: this .stmt
303: .executeUpdate("CREATE VIEW vTestViewMetaData AS SELECT field1 FROM testViewMetaData");
304:
305: ResultSet tablesRs = null;
306:
307: try {
308: tablesRs = this .conn.getMetaData().getTables(
309: this .conn.getCatalog(), null,
310: "%ViewMetaData",
311: new String[] { "TABLE", "VIEW" });
312: assertTrue(tablesRs.next());
313: assertTrue("testViewMetaData"
314: .equalsIgnoreCase(tablesRs.getString(3)));
315: assertTrue(tablesRs.next());
316: assertTrue("vTestViewMetaData"
317: .equalsIgnoreCase(tablesRs.getString(3)));
318:
319: } finally {
320: if (tablesRs != null) {
321: tablesRs.close();
322: }
323: }
324:
325: try {
326: tablesRs = this .conn.getMetaData().getTables(
327: this .conn.getCatalog(), null,
328: "%ViewMetaData",
329: new String[] { "TABLE" });
330: assertTrue(tablesRs.next());
331: assertTrue("testViewMetaData"
332: .equalsIgnoreCase(tablesRs.getString(3)));
333: assertTrue(!tablesRs.next());
334: } finally {
335: if (tablesRs != null) {
336: tablesRs.close();
337: }
338: }
339: break;
340: }
341: }
342:
343: } finally {
344: if (this .rs != null) {
345: this .rs.close();
346: }
347: }
348: }
349:
350: /**
351: * Tests detection of read-only fields when the server is 4.1.0 or newer.
352: *
353: * @throws Exception
354: * if the test fails.
355: */
356: public void testRSMDIsReadOnly() throws Exception {
357: try {
358: this .rs = this .stmt.executeQuery("SELECT 1");
359:
360: ResultSetMetaData rsmd = this .rs.getMetaData();
361:
362: if (versionMeetsMinimum(4, 1)) {
363: assertTrue(rsmd.isReadOnly(1));
364:
365: try {
366: this .stmt
367: .executeUpdate("DROP TABLE IF EXISTS testRSMDIsReadOnly");
368: this .stmt
369: .executeUpdate("CREATE TABLE testRSMDIsReadOnly (field1 INT)");
370: this .stmt
371: .executeUpdate("INSERT INTO testRSMDIsReadOnly VALUES (1)");
372:
373: this .rs = this .stmt
374: .executeQuery("SELECT 1, field1 + 1, field1 FROM testRSMDIsReadOnly");
375: rsmd = this .rs.getMetaData();
376:
377: assertTrue(rsmd.isReadOnly(1));
378: assertTrue(rsmd.isReadOnly(2));
379: assertTrue(!rsmd.isReadOnly(3));
380: } finally {
381: this .stmt
382: .executeUpdate("DROP TABLE IF EXISTS testRSMDIsReadOnly");
383: }
384: } else {
385: assertTrue(rsmd.isReadOnly(1) == false);
386: }
387: } finally {
388: if (this .rs != null) {
389: this .rs.close();
390: }
391: }
392: }
393:
394: public void testBitType() throws Exception {
395: if (versionMeetsMinimum(5, 0, 3)) {
396: try {
397: this .stmt
398: .executeUpdate("DROP TABLE IF EXISTS testBitType");
399: this .stmt
400: .executeUpdate("CREATE TABLE testBitType (field1 BIT, field2 BIT, field3 BIT)");
401: this .stmt
402: .executeUpdate("INSERT INTO testBitType VALUES (1, 0, NULL)");
403: this .rs = this .stmt
404: .executeQuery("SELECT field1, field2, field3 FROM testBitType");
405: this .rs.next();
406:
407: assertTrue(((Boolean) this .rs.getObject(1))
408: .booleanValue());
409: assertTrue(!((Boolean) this .rs.getObject(2))
410: .booleanValue());
411: assertEquals(this .rs.getObject(3), null);
412:
413: System.out.println(this .rs.getObject(1) + ", "
414: + this .rs.getObject(2) + ", "
415: + this .rs.getObject(3));
416:
417: this .rs = this .conn
418: .prepareStatement(
419: "SELECT field1, field2, field3 FROM testBitType")
420: .executeQuery();
421: this .rs.next();
422:
423: assertTrue(((Boolean) this .rs.getObject(1))
424: .booleanValue());
425: assertTrue(!((Boolean) this .rs.getObject(2))
426: .booleanValue());
427:
428: assertEquals(this .rs.getObject(3), null);
429: byte[] asBytesTrue = this .rs.getBytes(1);
430: byte[] asBytesFalse = this .rs.getBytes(2);
431: byte[] asBytesNull = this .rs.getBytes(3);
432:
433: assertEquals(asBytesTrue[0], 1);
434: assertEquals(asBytesFalse[0], 0);
435: assertEquals(asBytesNull, null);
436:
437: this .stmt
438: .executeUpdate("DROP TABLE IF EXISTS testBitField");
439: this .stmt
440: .executeUpdate("CREATE TABLE testBitField(field1 BIT(9))");
441: this .rs = this .stmt
442: .executeQuery("SELECT field1 FROM testBitField");
443: System.out.println(this .rs.getMetaData()
444: .getColumnClassName(1));
445: } finally {
446: this .stmt
447: .executeUpdate("DROP TABLE IF EXISTS testBitType");
448: }
449: }
450: }
451:
452: public void testSupportsSelectForUpdate() throws Exception {
453: boolean supportsForUpdate = this .conn.getMetaData()
454: .supportsSelectForUpdate();
455:
456: if (this .versionMeetsMinimum(4, 0)) {
457: assertTrue(supportsForUpdate);
458: } else {
459: assertTrue(!supportsForUpdate);
460: }
461: }
462:
463: public void testTinyint1IsBit() throws Exception {
464: String tableName = "testTinyint1IsBit";
465: // Can't use 'BIT' or boolean
466: createTable(tableName, "(field1 TINYINT(1))");
467: this .stmt.executeUpdate("INSERT INTO " + tableName
468: + " VALUES (1)");
469:
470: Properties props = new Properties();
471: props.setProperty("tinyint1IsBit", "true");
472: props.setProperty("transformedBitIsBoolean", "true");
473: Connection boolConn = getConnectionWithProps(props);
474:
475: this .rs = boolConn.createStatement().executeQuery(
476: "SELECT field1 FROM " + tableName);
477: checkBitOrBooleanType(false);
478:
479: this .rs = boolConn.prepareStatement(
480: "SELECT field1 FROM " + tableName).executeQuery();
481: checkBitOrBooleanType(false);
482:
483: this .rs = boolConn.getMetaData().getColumns(
484: boolConn.getCatalog(), null, tableName, "field1");
485: assertTrue(this .rs.next());
486:
487: if (versionMeetsMinimum(4, 1)) {
488: assertEquals(Types.BOOLEAN, this .rs.getInt("DATA_TYPE"));
489: } else {
490: assertEquals(Types.BIT, this .rs.getInt("DATA_TYPE"));
491: }
492:
493: if (versionMeetsMinimum(4, 1)) {
494: assertEquals("BOOLEAN", this .rs.getString("TYPE_NAME"));
495: } else {
496: assertEquals("BIT", this .rs.getString("TYPE_NAME"));
497: }
498:
499: props.clear();
500: props.setProperty("transformedBitIsBoolean", "false");
501: props.setProperty("tinyint1IsBit", "true");
502:
503: Connection bitConn = getConnectionWithProps(props);
504:
505: this .rs = bitConn.createStatement().executeQuery(
506: "SELECT field1 FROM " + tableName);
507: checkBitOrBooleanType(true);
508:
509: this .rs = bitConn.prepareStatement(
510: "SELECT field1 FROM " + tableName).executeQuery();
511: checkBitOrBooleanType(true);
512:
513: this .rs = bitConn.getMetaData().getColumns(
514: boolConn.getCatalog(), null, tableName, "field1");
515: assertTrue(this .rs.next());
516:
517: assertEquals(Types.BIT, this .rs.getInt("DATA_TYPE"));
518:
519: assertEquals("BIT", this .rs.getString("TYPE_NAME"));
520: }
521:
522: private void checkBitOrBooleanType(boolean usingBit)
523: throws SQLException {
524:
525: assertTrue(this .rs.next());
526: assertEquals("java.lang.Boolean", this .rs.getObject(1)
527: .getClass().getName());
528: if (!usingBit) {
529: if (versionMeetsMinimum(4, 1)) {
530: assertEquals(Types.BOOLEAN, this .rs.getMetaData()
531: .getColumnType(1));
532: } else {
533: assertEquals(Types.BIT, this .rs.getMetaData()
534: .getColumnType(1));
535: }
536: } else {
537: assertEquals(Types.BIT, this .rs.getMetaData()
538: .getColumnType(1));
539: }
540:
541: assertEquals("java.lang.Boolean", this .rs.getMetaData()
542: .getColumnClassName(1));
543: }
544:
545: /**
546: * Tests the implementation of Information Schema for primary keys.
547: */
548: public void testGetPrimaryKeysUsingInfoShcema() throws Exception {
549: if (versionMeetsMinimum(5, 0, 7)) {
550: this .stmt.executeUpdate("DROP TABLE IF EXISTS t1");
551: this .stmt
552: .executeUpdate("CREATE TABLE t1 (c1 int(1) primary key)");
553: Properties props = new Properties();
554: props.put("useInformationSchema", "true");
555: Connection conn1 = null;
556: try {
557: conn1 = getConnectionWithProps(props);
558: DatabaseMetaData metaData = conn1.getMetaData();
559: this .rs = metaData.getPrimaryKeys(null, null, "t1");
560: this .rs.next();
561: assertEquals("t1", this .rs.getString("TABLE_NAME"));
562: assertEquals("c1", this .rs.getString("COLUMN_NAME"));
563: } finally {
564: if (conn1 != null) {
565: conn1.close();
566: }
567: }
568: }
569: }
570:
571: /**
572: * Tests the implementation of Information Schema for index info.
573: */
574: public void testGetIndexInfoUsingInfoSchema() throws Exception {
575: if (versionMeetsMinimum(5, 0, 7)) {
576: this .stmt.executeUpdate("DROP TABLE IF EXISTS t1");
577: this .stmt.executeUpdate("CREATE TABLE t1 (c1 int(1))");
578: this .stmt.executeUpdate("CREATE INDEX index1 ON t1 (c1)");
579: Properties props = new Properties();
580: props.put("useInformationSchema", "true");
581: Connection conn1 = null;
582: try {
583: conn1 = getConnectionWithProps(props);
584: DatabaseMetaData metaData = conn1.getMetaData();
585: this .rs = metaData.getIndexInfo("test", null, "t1",
586: false, true);
587: this .rs.next();
588: assertEquals("t1", this .rs.getString("TABLE_NAME"));
589: assertEquals("c1", this .rs.getString("COLUMN_NAME"));
590: assertEquals("1", this .rs.getString("NON_UNIQUE"));
591: assertEquals("index1", this .rs.getString("INDEX_NAME"));
592: } finally {
593: if (conn1 != null) {
594: conn1.close();
595: }
596: }
597: }
598: }
599:
600: /**
601: * Tests the implementation of Information Schema for columns.
602: */
603: public void testGetColumnsUsingInfoSchema() throws Exception {
604: if (versionMeetsMinimum(5, 0, 7)) {
605: this .stmt.executeUpdate("DROP TABLE IF EXISTS t1");
606: this .stmt.executeUpdate("CREATE TABLE t1 (c1 char(1))");
607: Properties props = new Properties();
608: props.put("useInformationSchema", "true");
609: Connection conn1 = null;
610: try {
611: conn1 = getConnectionWithProps(props);
612: DatabaseMetaData metaData = conn1.getMetaData();
613: this .rs = metaData.getColumns(null, null, "t1", null);
614: this .rs.next();
615: assertEquals("t1", this .rs.getString("TABLE_NAME"));
616: assertEquals("c1", this .rs.getString("COLUMN_NAME"));
617: assertEquals("char", this .rs.getString("TYPE_NAME"));
618: assertEquals("1", this .rs.getString("COLUMN_SIZE"));
619: } finally {
620: if (conn1 != null) {
621: conn1.close();
622: }
623: }
624: }
625: }
626:
627: /**
628: * Tests the implementation of Information Schema for tables.
629: */
630: public void testGetTablesUsingInfoSchema() throws Exception {
631: if (versionMeetsMinimum(5, 0, 7)) {
632: this .stmt.executeUpdate("DROP TABLE IF EXISTS `t1-1`");
633: this .stmt.executeUpdate("CREATE TABLE `t1-1` (c1 char(1))");
634: this .stmt.executeUpdate("DROP TABLE IF EXISTS `t1-2`");
635: this .stmt.executeUpdate("CREATE TABLE `t1-2` (c1 char(1))");
636: this .stmt.executeUpdate("DROP TABLE IF EXISTS `t2`");
637: this .stmt.executeUpdate("CREATE TABLE `t2` (c1 char(1))");
638: Set tableNames = new HashSet();
639: tableNames.add("t1-1");
640: tableNames.add("t1-2");
641: Properties props = new Properties();
642: props.put("useInformationSchema", "true");
643: Connection conn1 = null;
644: try {
645: conn1 = getConnectionWithProps(props);
646: DatabaseMetaData metaData = conn1.getMetaData();
647: // pattern matching for table name
648: this .rs = metaData.getTables(null, null, "t1-_", null);
649: while (this .rs.next()) {
650: assertTrue(tableNames.remove(this .rs
651: .getString("TABLE_NAME")));
652: }
653: assertTrue(tableNames.isEmpty());
654: } finally {
655: if (conn1 != null) {
656: conn1.close();
657: }
658: }
659: }
660: }
661:
662: /**
663: * Tests the implementation of Information Schema for column privileges.
664: */
665: public void testGetColumnPrivilegesUsingInfoSchema()
666: throws Exception {
667: String dontRunPropertyName = "com.mysql.jdbc.testsuite.cantGrant";
668:
669: if (!runTestIfSysPropDefined(dontRunPropertyName)) {
670: if (versionMeetsMinimum(5, 0, 7)) {
671: Properties props = new Properties();
672:
673: props.put("useInformationSchema", "true");
674: Connection conn1 = null;
675: Statement stmt1 = null;
676: String userHostQuoted = null;
677:
678: boolean grantFailed = true;
679:
680: try {
681: conn1 = getConnectionWithProps(props);
682: stmt1 = conn1.createStatement();
683: stmt1.executeUpdate("DROP TABLE IF EXISTS t1");
684: stmt1.executeUpdate("CREATE TABLE t1 (c1 int)");
685: this .rs = stmt1.executeQuery("SELECT USER()");
686: this .rs.next();
687: String user = this .rs.getString(1);
688: List userHost = StringUtils.split(user, "@", false);
689: userHostQuoted = "'" + userHost.get(0) + "'@'"
690: + userHost.get(1) + "'";
691:
692: try {
693: stmt1
694: .executeUpdate("GRANT update (c1) on t1 to "
695: + userHostQuoted);
696:
697: grantFailed = false;
698:
699: } catch (SQLException sqlEx) {
700: logDebug("This testcase needs to be run with a URL that allows the user to issue GRANTs "
701: + " in the current database. You can skip this test by setting the system property \""
702: + dontRunPropertyName + "\".");
703:
704: grantFailed = true;
705: }
706:
707: if (!grantFailed) {
708: DatabaseMetaData metaData = conn1.getMetaData();
709: this .rs = metaData.getColumnPrivileges(null,
710: null, "t1", null);
711: this .rs.next();
712: assertEquals("t1", this .rs
713: .getString("TABLE_NAME"));
714: assertEquals("c1", this .rs
715: .getString("COLUMN_NAME"));
716: assertEquals(userHostQuoted, this .rs
717: .getString("GRANTEE"));
718: assertEquals("UPDATE", this .rs
719: .getString("PRIVILEGE"));
720: }
721: } finally {
722: if (stmt1 != null) {
723:
724: stmt1.executeUpdate("DROP TABLE IF EXISTS t1");
725:
726: if (!grantFailed) {
727: stmt1
728: .executeUpdate("REVOKE UPDATE (c1) ON t1 FROM "
729: + userHostQuoted);
730: }
731:
732: stmt1.close();
733: }
734:
735: if (conn1 != null) {
736: conn1.close();
737: }
738: }
739: }
740: }
741: }
742:
743: /**
744: * Tests the implementation of Information Schema for description
745: * of stored procedures available in a catalog.
746: */
747: public void testGetProceduresUsingInfoSchema() throws Exception {
748: if (versionMeetsMinimum(5, 0, 7)) {
749: this .stmt.executeUpdate("DROP PROCEDURE IF EXISTS sp1");
750: this .stmt.executeUpdate("CREATE PROCEDURE sp1()\n BEGIN\n"
751: + "SELECT 1;" + "end\n");
752: Properties props = new Properties();
753: props.put("useInformationSchema", "true");
754: Connection conn1 = null;
755: try {
756: conn1 = getConnectionWithProps(props);
757: DatabaseMetaData metaData = conn1.getMetaData();
758: this .rs = metaData.getProcedures(null, null, "sp1");
759: this .rs.next();
760: assertEquals("sp1", this .rs.getString("PROCEDURE_NAME"));
761: assertEquals("1", this .rs.getString("PROCEDURE_TYPE"));
762: } finally {
763: if (conn1 != null) {
764: conn1.close();
765: }
766: this .stmt.executeUpdate("DROP PROCEDURE sp1");
767: }
768: }
769: }
770:
771: /**
772: * Tests the implementation of Information Schema for foreign key.
773: */
774: public void testGetCrossReferenceUsingInfoSchema() throws Exception {
775: if (versionMeetsMinimum(5, 0, 7)) {
776: this .stmt.executeUpdate("DROP TABLE IF EXISTS child");
777: this .stmt.executeUpdate("DROP TABLE If EXISTS parent");
778: this .stmt
779: .executeUpdate("CREATE TABLE parent(id INT NOT NULL, "
780: + "PRIMARY KEY (id)) ENGINE=INNODB");
781: this .stmt
782: .executeUpdate("CREATE TABLE child(id INT, parent_id INT, "
783: + "FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE SET NULL) ENGINE=INNODB");
784: Properties props = new Properties();
785: props.put("useInformationSchema", "true");
786: Connection conn1 = null;
787: try {
788: conn1 = getConnectionWithProps(props);
789: DatabaseMetaData metaData = conn1.getMetaData();
790: this .rs = metaData.getCrossReference(null, null,
791: "parent", null, null, "child");
792: this .rs.next();
793: assertEquals("parent", this .rs
794: .getString("PKTABLE_NAME"));
795: assertEquals("id", this .rs.getString("PKCOLUMN_NAME"));
796: assertEquals("child", this .rs.getString("FKTABLE_NAME"));
797: assertEquals("parent_id", this .rs
798: .getString("FKCOLUMN_NAME"));
799: } finally {
800: this .stmt.executeUpdate("DROP TABLE IF EXISTS child");
801: this .stmt.executeUpdate("DROP TABLE If EXISTS parent");
802: if (conn1 != null) {
803: conn1.close();
804: }
805: }
806: }
807: }
808:
809: /**
810: * Tests the implementation of Information Schema for foreign key.
811: */
812: public void testGetExportedKeysUsingInfoSchema() throws Exception {
813: if (versionMeetsMinimum(5, 0, 7)) {
814: this .stmt.executeUpdate("DROP TABLE IF EXISTS child");
815: this .stmt.executeUpdate("DROP TABLE If EXISTS parent");
816: this .stmt
817: .executeUpdate("CREATE TABLE parent(id INT NOT NULL, "
818: + "PRIMARY KEY (id)) ENGINE=INNODB");
819: this .stmt
820: .executeUpdate("CREATE TABLE child(id INT, parent_id INT, "
821: + "FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE SET NULL) ENGINE=INNODB");
822: Properties props = new Properties();
823: props.put("useInformationSchema", "true");
824: Connection conn1 = null;
825: try {
826: conn1 = getConnectionWithProps(props);
827: DatabaseMetaData metaData = conn1.getMetaData();
828: this .rs = metaData
829: .getExportedKeys(null, null, "parent");
830: this .rs.next();
831: assertEquals("parent", this .rs
832: .getString("PKTABLE_NAME"));
833: assertEquals("id", this .rs.getString("PKCOLUMN_NAME"));
834: assertEquals("child", this .rs.getString("FKTABLE_NAME"));
835: assertEquals("parent_id", this .rs
836: .getString("FKCOLUMN_NAME"));
837: } finally {
838: this .stmt.executeUpdate("DROP TABLE IF EXISTS child");
839: this .stmt.executeUpdate("DROP TABLE If EXISTS parent");
840: if (conn1 != null) {
841: conn1.close();
842: }
843: }
844: }
845: }
846:
847: /**
848: * Tests the implementation of Information Schema for foreign key.
849: */
850: public void testGetImportedKeysUsingInfoSchema() throws Exception {
851: if (versionMeetsMinimum(5, 0, 7)) {
852: this .stmt.executeUpdate("DROP TABLE IF EXISTS child");
853: this .stmt.executeUpdate("DROP TABLE If EXISTS parent");
854: this .stmt
855: .executeUpdate("CREATE TABLE parent(id INT NOT NULL, "
856: + "PRIMARY KEY (id)) ENGINE=INNODB");
857: this .stmt
858: .executeUpdate("CREATE TABLE child(id INT, parent_id INT, "
859: + "FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE SET NULL) ENGINE=INNODB");
860: Properties props = new Properties();
861: props.put("useInformationSchema", "true");
862: Connection conn1 = null;
863: try {
864: conn1 = getConnectionWithProps(props);
865: DatabaseMetaData metaData = conn1.getMetaData();
866: this .rs = metaData.getImportedKeys(null, null, "child");
867: this .rs.next();
868: assertEquals("parent", this .rs
869: .getString("PKTABLE_NAME"));
870: assertEquals("id", this .rs.getString("PKCOLUMN_NAME"));
871: assertEquals("child", this .rs.getString("FKTABLE_NAME"));
872: assertEquals("parent_id", this .rs
873: .getString("FKCOLUMN_NAME"));
874: } finally {
875: this .stmt.executeUpdate("DROP TABLE IF EXISTS child");
876: this .stmt.executeUpdate("DROP TABLE If EXISTS parent");
877: if (conn1 != null) {
878: conn1.close();
879: }
880: }
881: }
882: }
883: }
|