1 /*
2  * Copyright (C) 2007 The Android Open Source Project
3  *
4  * Licensed under the Apache License, Version 2.0 (the "License");
5  * you may not use this file except in compliance with the License.
6  * You may obtain a copy of the License at
7  *
8  *     http://www.apache.org/licenses/LICENSE-2.0
9  *
10  * Unless required by applicable law or agreed to in writing, software
11  * distributed under the License is distributed on an "AS IS" BASIS,
12  * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13  * See the License for the specific language governing permissions and
14  * limitations under the License.
15  */
16 package tests.java.sql;
17 
18 import dalvik.annotation.KnownFailure;
19 
20 import junit.extensions.TestSetup;
21 import junit.framework.Test;
22 import junit.framework.TestCase;
23 import junit.framework.TestSuite;
24 
25 import tests.support.DatabaseCreator;
26 import tests.support.Support_SQL;
27 
28 import java.sql.Connection;
29 import java.sql.DatabaseMetaData;
30 import java.sql.DriverManager;
31 import java.sql.PreparedStatement;
32 import java.sql.ResultSet;
33 import java.sql.ResultSetMetaData;
34 import java.sql.SQLException;
35 import java.sql.Statement;
36 import java.sql.Types;
37 import java.util.ArrayList;
38 import java.util.Arrays;
39 import java.util.List;
40 import java.util.Random;
41 import java.util.StringTokenizer;
42 
43 public class DatabaseMetaDataTest extends TestCase {
44     private static String VIEW_NAME = "myView";
45 
46     private static String CREATE_VIEW_QUERY = "CREATE VIEW " + VIEW_NAME
47             + " AS SELECT * FROM " + DatabaseCreator.TEST_TABLE1;
48 
49     private static String DROP_VIEW_QUERY = "DROP VIEW " + VIEW_NAME;
50 
51     protected static Connection conn;
52 
53     protected static DatabaseMetaData meta;
54 
55     protected static Statement statement;
56 
57     protected static Statement statementForward;
58 
59     private static int id = 1;
60 
setUp()61     public void setUp() throws Exception {
62         super.setUp();
63         Support_SQL.loadDriver();
64         try {
65             conn = Support_SQL.getConnection();
66             meta = conn.getMetaData();
67             statement = conn.createStatement();
68             statementForward = conn.createStatement(
69                     ResultSet.TYPE_FORWARD_ONLY,
70                     ResultSet.CONCUR_READ_ONLY);
71             createTestTables();
72         } catch (SQLException e) {
73             System.out.println("Error in test setup: "+e.getMessage());
74         }
75     }
76 
tearDown()77     protected void tearDown() throws Exception {
78         try {
79             conn = Support_SQL.getConnection();
80             meta = conn.getMetaData();
81             statement = conn.createStatement();
82             deleteTestTables();
83         } catch (SQLException e) {
84             System.out.println("Error in teardown: "+e.getMessage());
85         } finally {
86             try {
87                 conn.close();
88             } catch (SQLException e) {
89             }
90         }
91         super.tearDown();
92     }
93 
createTestTables()94             private void createTestTables() {
95                 try {
96                     ResultSet userTab = meta.getTables(null, null, null, null);
97                     while (userTab.next()) {
98                         String tableName = userTab.getString("TABLE_NAME");
99                         if (tableName.equals(DatabaseCreator.TEST_TABLE1)) {
100                             statement.execute(DatabaseCreator.DROP_TABLE1);
101                         } else if (tableName
102                                 .equals(DatabaseCreator.TEST_TABLE3)) {
103                             statement.execute(DatabaseCreator.DROP_TABLE3);
104                         } else if (tableName.equals(VIEW_NAME)) {
105                             statement.execute(DROP_VIEW_QUERY);
106                         }
107                     }
108                     userTab.close();
109                     statement.execute(DatabaseCreator.CREATE_TABLE3);
110                     statement.execute(DatabaseCreator.CREATE_TABLE1);
111                     statement.execute(CREATE_VIEW_QUERY);
112                     meta = conn.getMetaData();
113                 } catch (SQLException e) {
114                     fail("Unexpected SQLException " + e.toString());
115                 }
116             }
117 
deleteTestTables()118             private void deleteTestTables() {
119                 try {
120                     statement.execute(DatabaseCreator.DROP_TABLE1);
121                     statement.execute(DatabaseCreator.DROP_TABLE3);
122                     statement.execute(DROP_VIEW_QUERY);
123                 } catch (SQLException e) {
124                     fail("Unexpected SQLException " + e.toString());
125                 } finally {
126                     try {
127                     if (! conn.isClosed()) {
128                         conn.close();
129                     }
130                     } catch (SQLException e) {
131 
132                     }
133                 }
134             }
135     /*
136     public void setUp() {
137         try {
138             super.setUp();
139             try {
140                 conn = Support_SQL.getConnection();
141                 statement = conn.createStatement();
142                 statementForward = conn.createStatement(
143                         ResultSet.TYPE_FORWARD_ONLY,
144                         ResultSet.CONCUR_READ_ONLY);
145                 meta = conn.getMetaData();
146 
147                 assertFalse(conn.isClosed());
148             } catch (SQLException e) {
149                 fail("Unexpected SQLException " + e.toString());
150             }
151 
152         } catch (Exception e) {
153             // TODO Auto-generated catch block
154             e.printStackTrace();
155         }
156 
157     }
158     */
159 
160     /**
161      * {@link java.sql.DatabaseMetaData #getBestRowIdentifier(java.lang.String,
162      *        java.lang.String, java.lang.String, int, boolean) }
163      */
test_getBestRowIdentifierLjava_lang_StringLjava_lang_StringLjava_lang_StringIZ()164     public void test_getBestRowIdentifierLjava_lang_StringLjava_lang_StringLjava_lang_StringIZ()
165             throws SQLException {
166         ResultSet result = statementForward.executeQuery("SELECT * FROM "
167                 + DatabaseCreator.TEST_TABLE1);
168 
169         //Updatable ResultSet not supported, converted to normal insert statement
170         statementForward.executeUpdate("INSERT INTO " + DatabaseCreator.TEST_TABLE1
171                 + " (id, field1) VALUES( 1234567, 'test1');");
172         /* not supported
173         try {
174          result.moveToInsertRow();
175          result.updateInt("id", 1234567);
176          result.updateString("field1", "test1");
177          result.insertRow();
178          } catch (SQLException e) {
179          fail("Unexpected SQLException " + e.toString());
180          }
181          */
182 
183 
184         result.close();
185 
186         ResultSet rs = meta.getBestRowIdentifier(null, null,
187                 DatabaseCreator.TEST_TABLE1, DatabaseMetaData.bestRowSession,
188                 true);
189         ResultSetMetaData rsmd = rs.getMetaData();
190         assertTrue("Rows not obtained", rs.next());
191         int col = rsmd.getColumnCount();
192         assertEquals("Incorrect number of columns", 8, col);
193         String[] columnNames = {
194                 "SCOPE", "COLUMN_NAME", "DATA_TYPE", "TYPE_NAME",
195                 "COLUMN_SIZE", "BUFFER_LENGTH", "DECIMAL_DIGITS",
196                 "PSEUDO_COLUMN"};
197         for (int c = 1; c <= col; ++c) {
198             assertEquals("Incorrect column name", columnNames[c - 1], rsmd
199                     .getColumnName(c));
200         }
201         assertEquals("Incorrect scope", DatabaseMetaData.bestRowSession, rs
202                 .getShort("SCOPE"));
203         assertEquals("Incorrect column name", "_ROWID_", rs.getString("COLUMN_NAME"));
204         assertEquals("Incorrect data type", java.sql.Types.INTEGER, rs.getInt("DATA_TYPE"));
205         assertEquals("Incorrect type name", "INTEGER", rs.getString("TYPE_NAME"));
206         rs.close();
207 
208      // Exception testing
209         conn.close();
210 
211         try {
212             meta.getColumns(null, null,
213             DatabaseCreator.TEST_TABLE1, "%");
214             fail("SQLException not thrown");
215         } catch (SQLException e) {
216             // ok
217         }
218     }
219 
220     /**
221      * java.sql.DatabaseMetaData #getColumns(java.lang.String,
222      *        java.lang.String, java.lang.String, java.lang.String)
223      *
224      */
225     @KnownFailure("Not supported : pattern with %")
test_getColumnsArbitrary()226     public void test_getColumnsArbitrary() throws SQLException {
227         ResultSet setAllNull = null;
228         ResultSet setMixed = null;
229         ResultSet allArbitrary = null;
230         String[] tablesName = {DatabaseCreator.TEST_TABLE1,
231                 DatabaseCreator.TEST_TABLE3};
232         Arrays.sort(tablesName);
233         int setSize = 0;
234         try {
235             allArbitrary = meta.getColumns("%","%","%","%");
236             assertNotNull(allArbitrary);
237             checkColumnsShape(allArbitrary);
238             setSize = crossCheckGetColumnsAndResultSetMetaData(allArbitrary, false);
239             assertEquals(6, setSize);
240 
241             setMixed = meta.getColumns(null, null,"%","%");
242             assertNotNull(setMixed);
243             checkColumnsShape(setMixed);
244             setSize = crossCheckGetColumnsAndResultSetMetaData(setMixed, false);
245             assertEquals(6, setSize);
246 
247         } catch (SQLException e) {
248             fail("Unexpected exception: " + e.getMessage());
249         }
250 
251         // Exception testing
252         conn.close();
253 
254         try {
255             meta.getColumns(null, null,
256                     DatabaseCreator.TEST_TABLE1, "%");
257             fail("SQLException not thrown");
258         } catch (SQLException e) {
259             // ok
260         }
261     }
262 
263     /**
264      * java.sql.DatabaseMetaData #getColumns(java.lang.String,
265      *        java.lang.String, java.lang.String, java.lang.String)
266      *
267      */
268     @KnownFailure("Not supported ops applied: test fails on arguments: '', '', '%', '%' ")
test_getColumnsTableWithNoCatalogSchema()269     public void test_getColumnsTableWithNoCatalogSchema() throws SQLException{
270 
271         try {
272             ResultSet noSchemaTable = meta.getColumns("", "",
273                     DatabaseCreator.TEST_TABLE1, "fkey");
274             assertNotNull(noSchemaTable);
275             noSchemaTable.last();
276             int size = noSchemaTable.getRow();
277             assertEquals(
278                     "Does not support empty string as input parameter or Wildcard %",
279                     1, size);
280 
281 
282 
283         } catch (SQLException e) {
284             fail("Unexpected exception: " + e.getMessage());
285         }
286 
287         try {
288             ResultSet noSchemaTable = meta.getColumns("", "",
289                     DatabaseCreator.TEST_TABLE1, "%");
290             assertNotNull(noSchemaTable);
291             noSchemaTable.last();
292             int size = noSchemaTable.getRow();
293             assertEquals(
294                     "Does not support empty string as input parameter or Wildcard %",
295                     5, size);
296 
297 
298 
299         } catch (SQLException e) {
300             fail("Unexpected exception: " + e.getMessage());
301         }
302 
303         try {
304             ResultSet noSchemaTable = meta.getColumns("", "", "%", "%");
305             assertNotNull(noSchemaTable);
306             noSchemaTable.last();
307             int size = noSchemaTable.getRow();
308             assertEquals(
309                     "Does not support double Wildcard '%' as input",
310                     6, size);
311 
312         } catch (SQLException e) {
313             fail("Unexpected exception: " + e.getMessage());
314         }
315 
316         // Exception checking
317         conn.close();
318 
319         try {
320             meta.getColumns(null, null,
321                     DatabaseCreator.TEST_TABLE1, "%");
322             fail("SQLException not thrown");
323         } catch (SQLException e) {
324             // ok
325         }
326     }
327 
328 
329 
330     /**
331      * java.sql.DatabaseMetaData #getColumns(java.lang.String,
332      *        java.lang.String, java.lang.String, java.lang.String)
333      *
334      */
335     @KnownFailure("Wildcard operator does not seem wo work correctly.")
test_getColumnsSpecific()336     public void test_getColumnsSpecific() throws SQLException {
337         String[] tablesName = {
338                 DatabaseCreator.TEST_TABLE1, DatabaseCreator.TEST_TABLE3};
339         String[] fields = {"id", "field1", "field2", "field3", "fkey"};
340         String[] nullable = {"YES", "NO",""};
341         int[] nullableInt = {
342                 DatabaseMetaData.columnNoNulls,
343                 DatabaseMetaData.columnNullable,
344                 DatabaseMetaData.columnNullableUnknown};
345         Arrays.sort(tablesName);
346         Arrays.sort(fields);
347         Arrays.sort(nullableInt);
348         Arrays.sort(nullable);
349         int countSingle = 0;
350         int countAll1 = 0;
351         int countAll2 = 0;
352 
353         try {
354             ResultSet rs = meta.getColumns(null, null,
355                     DatabaseCreator.TEST_TABLE1, "%");
356 
357             while (rs.next()) {
358                 assertTrue("Invalid table name", Arrays.binarySearch(
359                         tablesName, rs.getString("TABLE_NAME")) > -1);
360                 assertTrue("Invalid field name", Arrays.binarySearch(fields, rs
361                         .getString("COLUMN_NAME")) > -1);
362                 assertTrue("Invalid nullable value", Arrays.binarySearch(
363                         nullable, rs.getString("IS_NULLABLE")) > -1);
364                 assertTrue("Invalid nullable code", Arrays.binarySearch(
365                         nullableInt, rs.getInt("NULLABLE")) > -1);
366                 countSingle++;
367             }
368             assertEquals("Not all results are found", 5, countSingle);
369             rs.close();
370 
371         } catch (SQLException e) {
372             fail("Unexpected exception: " + e.getMessage());
373         }
374 
375         try {
376             ResultSet rs = meta.getColumns(null, null, "%"+DatabaseCreator.CREATE_TABLE1.substring(0, 3)+"%","%" );
377             while (rs.next()) {
378                 assertTrue("Wrong table name", Arrays.binarySearch(tablesName,
379                         rs.getString("TABLE_NAME")) > -1);
380                 countAll1++;
381             }
382             assertEquals("Not all results are found", 6, countAll1);
383             rs.close();
384 
385         } catch (SQLException e) {
386             fail("Unexpected exception: " + e.getMessage());
387         }
388 
389         try {
390             ResultSet rs = meta.getColumns(null, null, "%TEST_%", "%");
391 
392             while (rs.next()) {
393                 assertTrue("Wrong table name", Arrays.binarySearch(tablesName,
394                         rs.getString("TABLE_NAME")) > -1);
395                 countAll2++;
396             }
397             assertEquals("Not all results are found", 6, countAll2);
398             rs.close();
399 
400         } catch (SQLException e) {
401             fail("Unexpected exception: " + e.getMessage());
402         }
403 
404      // Exception checking
405         conn.close();
406 
407         try {
408             meta.getColumns(null, null,
409                     DatabaseCreator.TEST_TABLE1, "%");
410             fail("SQLException not thrown");
411         } catch (SQLException e) {
412             // ok
413         }
414 
415 
416     }
417 
418 
419 
420     /**
421      * java.sql.DatabaseMetaData#getConnection()
422      */
test_getConnection()423     public void test_getConnection() throws SQLException {
424         assertEquals("Incorrect connection value", conn, meta.getConnection());
425 
426         // Exception checking
427         conn.close();
428 
429         try {
430             Connection con = meta.getConnection();
431             assertTrue(con.isClosed());
432         } catch (SQLException e) {
433             // ok
434         }
435     }
436 
437     /**
438      * java.sql.DatabaseMetaData #getCrossReference(java.lang.String,
439      *        java.lang.String, java.lang.String, java.lang.String,
440      *        java.lang.String, java.lang.String)
441      */
442     @KnownFailure("(Ticket 91) Tables apply foreign key constraint. Catalogs not supported")
test_getCrossReferenceLjava_lang_StringLjava_lang_StringLjava_lang_StringLjava_lang_StringLjava_lang_StringLjava_lang_String()443     public void test_getCrossReferenceLjava_lang_StringLjava_lang_StringLjava_lang_StringLjava_lang_StringLjava_lang_StringLjava_lang_String()
444             throws SQLException {
445         ResultSet rs = meta.getCrossReference(conn.getCatalog(), null,
446                 DatabaseCreator.TEST_TABLE3, conn.getCatalog(), null,
447                 DatabaseCreator.TEST_TABLE1);
448         ResultSetMetaData rsmd = rs.getMetaData();
449         assertTrue("Rows do not obtained", rs.next());
450         int col = rsmd.getColumnCount();
451         assertEquals("Incorrect number of columns", 14, col);
452         String[] columnNames = { "PKTABLE_CAT", "PKTABLE_SCHEM",
453                 "PKTABLE_NAME", "PKCOLUMN_NAME", "FKTABLE_CAT",
454                 "FKTABLE_SCHEM", "FKTABLE_NAME", "FKCOLUMN_NAME", "KEY_SEQ",
455                 "UPDATE_RULE", "DELETE_RULE", "FK_NAME", "PK_NAME",
456                 "DEFERRABILITY" };
457         for (int c = 1; c <= col; ++c) {
458             assertEquals("Incorrect column name", columnNames[c - 1], rsmd
459                     .getColumnName(c));
460         }
461 //      TODO getCatalog is not supported
462         assertEquals("Incorrect primary key table catalog", conn.getCatalog(),
463                 rs.getString("PKTABLE_CAT"));
464         assertEquals("Incorrect primary key table schema", "", rs
465                 .getString("PKTABLE_SCHEM"));
466         assertEquals("Incorrect primary key table name",
467                 DatabaseCreator.TEST_TABLE3, rs.getString("PKTABLE_NAME"));
468         assertEquals("Incorrect primary key column name", "fkey", rs
469                 .getString("PKCOLUMN_NAME"));
470         // TODO getCatalog is not supported
471         assertEquals("Incorrect foreign key table catalog", conn.getCatalog(),
472                 rs.getString("FKTABLE_CAT"));
473         assertEquals("Incorrect foreign key table schema", "", rs
474                 .getString("FKTABLE_SCHEM"));
475         assertEquals("Incorrect foreign key table name",
476                 DatabaseCreator.TEST_TABLE1, rs.getString("FKTABLE_NAME"));
477         assertEquals("Incorrect foreign key column name", "fk", rs
478                 .getString("FKCOLUMN_NAME"));
479         assertEquals("Incorrect sequence number within foreign key", 1, rs
480                 .getShort("KEY_SEQ"));
481         assertEquals("Incorrect update rule value",
482                 DatabaseMetaData.importedKeyNoAction, rs
483                         .getShort("UPDATE_RULE"));
484         assertEquals("Incorrect delete rule value",
485                 DatabaseMetaData.importedKeyNoAction, rs
486                         .getShort("DELETE_RULE"));
487         assertNull("Incorrect foreign key name", rs.getString("FK_NAME"));
488         assertNull("Incorrect primary key name", rs.getString("PK_NAME"));
489         assertEquals("Incorrect deferrability",
490                 DatabaseMetaData.importedKeyNotDeferrable, rs
491                         .getShort("DEFERRABILITY"));
492         rs.close();
493 
494      // Exception checking
495         conn.close();
496 
497         try {
498             meta.getCrossReference(conn.getCatalog(), null,
499                     DatabaseCreator.TEST_TABLE3, conn.getCatalog(), null,
500                     DatabaseCreator.TEST_TABLE1);
501             fail("SQLException not thrown");
502         } catch (SQLException e) {
503             // ok
504         }
505 
506         // Exception checking
507         conn.close();
508 
509         try {
510             meta.getCrossReference(conn.getCatalog(), null,
511                     DatabaseCreator.TEST_TABLE3, conn.getCatalog(), null,
512                     DatabaseCreator.TEST_TABLE1);
513             fail("SQLException not thrown");
514         } catch (SQLException e) {
515             // ok
516         }
517     }
518 
519     /**
520      * java.sql.DatabaseMetaData#getDatabaseMajorVersion()
521      */
522     @KnownFailure("Ticket 98")
test_getDatabaseMajorVersion()523     public void test_getDatabaseMajorVersion() throws SQLException {
524         assertTrue("Incorrdct database major version", meta
525                 .getDatabaseMajorVersion() >= 0);
526 
527         // Exception checking
528         conn.close();
529 
530         try {
531             meta.getDatabaseMajorVersion();
532             fail("SQLException not thrown");
533         } catch (SQLException e) {
534             // ok
535         }
536 
537     }
538 
539     /**
540      * java.sql.DatabaseMetaData#getDatabaseMinorVersion()
541      */
542     @KnownFailure("Ticket 98")
test_getDatabaseMinorVersion()543     public void test_getDatabaseMinorVersion() throws SQLException {
544         assertTrue("Incorrect database minor version", meta
545                 .getDatabaseMinorVersion() >= 0);
546 
547      // Exception checking
548         conn.close();
549 
550         try {
551             meta.getDatabaseMinorVersion();
552             fail("SQLException not thrown");
553         } catch (SQLException e) {
554             // ok
555         }
556     }
557 
558     /**
559      * java.sql.DatabaseMetaData#getDatabaseProductName()
560      */
561     @KnownFailure("Ticket 98")
test_getDatabaseProductName()562     public void test_getDatabaseProductName() throws SQLException {
563         assertTrue("Incorrect database product name", !"".equals(meta
564                 .getDatabaseProductName().trim()));
565 
566         // Exception checking
567         conn.close();
568 
569         try {
570             meta.getDatabaseProductName();
571             fail("SQLException not thrown");
572         } catch (SQLException e) {
573             // ok
574         }
575 
576     }
577 
578     /**
579      * java.sql.DatabaseMetaData#getDatabaseProductVersion()
580      */
581     @KnownFailure("Ticket 98")
test_getDatabaseProductVersion()582     public void test_getDatabaseProductVersion() throws SQLException {
583         assertTrue("Incorrect database product version", !"".equals(meta
584                 .getDatabaseProductVersion().trim()));
585         // Exception checking
586         conn.close();
587 
588         try {
589             meta.getDatabaseProductVersion();
590             fail("SQLException not thrown");
591         } catch (SQLException e) {
592             // ok
593         }
594     }
595 
596     /**
597      * java.sql.DatabaseMetaData#getDefaultTransactionIsolation()
598      */
599     @KnownFailure("Ticket 98")
test_getDefaultTransactionIsolation()600     public void test_getDefaultTransactionIsolation() throws SQLException {
601         int defaultLevel = meta.getDefaultTransactionIsolation();
602         switch (defaultLevel) {
603         case Connection.TRANSACTION_NONE:
604         case Connection.TRANSACTION_READ_COMMITTED:
605         case Connection.TRANSACTION_READ_UNCOMMITTED:
606         case Connection.TRANSACTION_REPEATABLE_READ:
607         case Connection.TRANSACTION_SERIALIZABLE:
608             // these levels are OK
609             break;
610         default:
611             fail("Incorrect value of default transaction isolation level");
612         }
613 
614         // Exception checking
615         conn.close();
616 
617         try {
618             meta.getDefaultTransactionIsolation();
619             fail("SQLException not thrown");
620         } catch (SQLException e) {
621             // ok
622         }
623     }
624 
625     /**
626      * java.sql.DatabaseMetaData#getDriverMajorVersion()
627      */
test_getDriverMajorVersion()628     public void test_getDriverMajorVersion()  throws SQLException {
629         assertTrue("Incorrect driver major version", meta
630                 .getDriverMajorVersion() >= 0);
631     }
632 
633     /**
634      * java.sql.DatabaseMetaData#getDriverMinorVersion()
635      */
test_getDriverMinorVersion()636     public void test_getDriverMinorVersion() {
637         assertTrue("Incorrect driver minor version", meta
638                 .getDriverMinorVersion() >= 0);
639     }
640 
641     /**
642      * java.sql.DatabaseMetaData#getDriverName()
643      */
644     @KnownFailure("Ticket 98")
test_getDriverName()645     public void test_getDriverName() throws SQLException {
646         String driverName = meta.getDriverName();
647         assertTrue("Incorrect driver name", driverName.trim().startsWith(
648                 "SQLite"));
649 
650         // Exception checking
651         conn.close();
652 
653         try {
654             meta.getDriverName();
655             fail("SQLException not thrown");
656         } catch (SQLException e) {
657             // ok
658         }
659     }
660 
661     /**
662      * java.sql.DatabaseMetaData#getDriverVersion()
663      */
664     @KnownFailure("Ticket 98")
test_getDriverVersion()665     public void test_getDriverVersion() throws SQLException {
666         assertTrue("Incorrect driver version", !"".equals(meta
667                 .getDriverVersion().trim()));
668 
669       //Exception checking
670         conn.close();
671 
672          try {
673              meta.getDriverVersion();
674              fail("SQLException not thrown");
675          } catch (SQLException e) {
676              //ok
677          }
678 
679     }
680 
681 
682     @KnownFailure("Keys are not supported: Ticket 91")
test_getImportedKeysLjava_lang_StringLjava_lang_StringLjava_lang_String()683     public void test_getImportedKeysLjava_lang_StringLjava_lang_StringLjava_lang_String()
684             throws SQLException {
685         ResultSet rs = meta.getImportedKeys(conn.getCatalog(), null,
686                 DatabaseCreator.TEST_TABLE1);
687         ResultSetMetaData rsmd = rs.getMetaData();
688         assertTrue("Rows do not obtained", rs.next());
689         int col = rsmd.getColumnCount();
690         assertEquals("Incorrect number of columns", 14, col);
691         String[] columnNames = { "PKTABLE_CAT", "PKTABLE_SCHEM",
692                 "PKTABLE_NAME", "PKCOLUMN_NAME", "FKTABLE_CAT",
693                 "FKTABLE_SCHEM", "FKTABLE_NAME", "FKCOLUMN_NAME", "KEY_SEQ",
694                 "UPDATE_RULE", "DELETE_RULE", "FK_NAME", "PK_NAME",
695                 "DEFERRABILITY" };
696         for (int c = 1; c <= col; ++c) {
697             assertEquals("Incorrect column name", columnNames[c - 1], rsmd
698                     .getColumnName(c));
699         }
700 //      TODO getCatalog is not supported
701         assertEquals("Incorrect primary key table catalog", conn.getCatalog(),
702                 rs.getString("PKTABLE_CAT"));
703         assertEquals("Incorrect primary key table schema", "", rs
704                 .getString("PKTABLE_SCHEM"));
705         assertEquals("Incorrect primary key table name",
706                 DatabaseCreator.TEST_TABLE3, rs.getString("PKTABLE_NAME"));
707         assertEquals("Incorrect primary key column name", "fkey", rs
708                 .getString("PKCOLUMN_NAME"));
709         assertEquals("Incorrect foreign key table catalog", conn.getCatalog(),
710                 rs.getString("FKTABLE_CAT"));
711         assertEquals("Incorrect foreign key table schema", "", rs
712                 .getString("FKTABLE_SCHEM"));
713         assertEquals("Incorrect foreign key table name",
714                 DatabaseCreator.TEST_TABLE1, rs.getString("FKTABLE_NAME"));
715         assertEquals("Incorrect foreign key column name", "fk", rs
716                 .getString("FKCOLUMN_NAME"));
717         assertEquals("Incorrect sequence number within foreign key", 1, rs
718                 .getShort("KEY_SEQ"));
719         assertEquals("Incorrect update rule value",
720                 DatabaseMetaData.importedKeyNoAction, rs
721                         .getShort("UPDATE_RULE"));
722         assertEquals("Incorrect delete rule value",
723                 DatabaseMetaData.importedKeyNoAction, rs
724                         .getShort("DELETE_RULE"));
725  //       assertNotNull("Incorrect foreign key name", rs.getString("FK_NAME"));
726         assertEquals("Incorrect primary key name", null, rs
727                 .getString("PK_NAME"));
728         assertEquals("Incorrect deferrability",
729                 DatabaseMetaData.importedKeyNotDeferrable, rs
730                         .getShort("DEFERRABILITY"));
731         rs.close();
732 
733       //Exception checking
734         conn.close();
735 
736          try {
737              meta.getImportedKeys(conn.getCatalog(), null,
738                      DatabaseCreator.TEST_TABLE1);
739              fail("SQLException not thrown");
740          } catch (SQLException e) {
741              //ok
742          }
743     }
744 
745     /**
746      * java.sql.DatabaseMetaData#getMaxCursorNameLength()
747      */
test_getMaxCursorNameLength()748     public void test_getMaxCursorNameLength() throws SQLException {
749         int nameLength = meta.getMaxCursorNameLength();
750         if (nameLength > 0) {
751             try {
752                 statement.setCursorName(new String(new byte[nameLength + 1]));
753                 fail("Expected SQLException was not thrown");
754             } catch (SQLException e) {
755                 // expected
756             }
757         } else if (nameLength < 0) {
758             fail("Incorrect length of cursor name");
759         }
760     }
761 
762     /**
763      * java.sql.DatabaseMetaData#getJDBCMinorVersion()
764      */
765     @KnownFailure("Ticket 98")
test_getJDBCMinorVersion()766     public void test_getJDBCMinorVersion() throws SQLException {
767         assertTrue("Incorrect JDBC minor version",
768                 meta.getJDBCMinorVersion() >= 0);
769 
770       //Exception checking
771         conn.close();
772 
773          try {
774              meta.getJDBCMinorVersion();
775              fail("SQLException not thrown");
776          } catch (SQLException e) {
777              //ok
778          }
779 
780     }
781 
782     /**
783      * java.sql.DatabaseMetaData#getJDBCMajorVersion()
784      */
785     @KnownFailure("Ticket 98")
test_getJDBCMajorVersion()786     public void test_getJDBCMajorVersion() throws SQLException {
787         assertTrue("Incorrect JDBC major version",
788                 meta.getJDBCMajorVersion() >= 0);
789 
790       //Exception checking
791         conn.close();
792 
793          try {
794              meta.getJDBCMajorVersion();
795              fail("SQLException not thrown");
796          } catch (SQLException e) {
797              //ok
798          }
799 
800     }
801 
802 
803     /**
804      * java.sql.DatabaseMetaData#getNumericFunctions()
805      */
806     @KnownFailure("Not supported feature, Ticket 98. Broken because "+
807             "NUMERIC_FUNCTIONS not complete. When fixed change to @KnownFailure")
test_getNumericFunctions()808     public void test_getNumericFunctions() throws SQLException {
809         escapedFunctions(NUMERIC_FUNCTIONS, meta.getNumericFunctions());
810 
811 
812       //Exception checking
813         conn.close();
814 
815          try {
816              meta.getNumericFunctions();
817              fail("SQLException not thrown");
818          } catch (SQLException e) {
819              //ok
820          }
821 
822     }
823 
824     /**
825      * java.sql.DatabaseMetaData #getPrimaryKeys(java.lang.String,
826      *        java.lang.String, java.lang.String)
827      */
828     @KnownFailure(" Ticket 91 : relies on not supported features: getCatalog, keys")
test_getPrimaryKeysLjava_lang_StringLjava_lang_StringLjava_lang_String()829     public void test_getPrimaryKeysLjava_lang_StringLjava_lang_StringLjava_lang_String()
830             throws SQLException {
831         ResultSet rs = meta.getPrimaryKeys(conn.getCatalog(), null,
832                 DatabaseCreator.TEST_TABLE1);
833         ResultSetMetaData rsmd = rs.getMetaData();
834         assertTrue("Rows not obtained", rs.next());
835         int col = rsmd.getColumnCount();
836         assertEquals("Incorrect number of columns", 6, col);
837         String[] columnNames = { "TABLE_CAT", "TABLE_SCHEM", "TABLE_NAME",
838                 "COLUMN_NAME", "KEY_SEQ", "PK_NAME" };
839         for (int c = 1; c <= col; ++c) {
840             assertEquals("Incorrect column name", columnNames[c - 1], rsmd
841                     .getColumnName(c));
842         }
843         assertEquals("Incorrect table catalogue", conn.getCatalog(), rs
844                 .getString("TABLE_CAT").toLowerCase());
845         assertEquals("Incorrect table schema", "", rs
846                 .getString("TABLE_SCHEM"));
847         assertEquals("Incorrect table name", DatabaseCreator.TEST_TABLE1, rs
848                 .getString("TABLE_NAME").toLowerCase());
849         assertEquals("Incorrect column name", "id", rs.getString("COLUMN_NAME")
850                 .toLowerCase());
851         assertEquals("Incorrect sequence number", 1, rs.getShort("KEY_SEQ"));
852         assertEquals("Incorrect primary key name", "primary", rs.getString(
853                 "PK_NAME").toLowerCase());
854         rs.close();
855 
856       //Exception checking
857         conn.close();
858 
859          try {
860              meta.getPrimaryKeys(conn.getCatalog(), null,
861                      DatabaseCreator.TEST_TABLE1);
862              fail("SQLException not thrown");
863          } catch (SQLException e) {
864              //ok
865          }
866     }
867 
868     /**
869      * java.sql.DatabaseMetaData#getResultSetHoldability()
870      */
871     @KnownFailure("Ticket 98")
test_getResultSetHoldability()872     public void test_getResultSetHoldability() throws SQLException {
873         int hdb = meta.getResultSetHoldability();
874         switch (hdb) {
875         case ResultSet.HOLD_CURSORS_OVER_COMMIT:
876         case ResultSet.CLOSE_CURSORS_AT_COMMIT:
877             // these holdabilities are OK
878             break;
879         default:
880             fail("Incorrect value of holdability");
881         }
882         assertFalse("Incorrect result set holdability", meta
883                 .supportsResultSetHoldability(hdb));
884 
885       //Exception checking
886         conn.close();
887 
888          try {
889              meta.getResultSetHoldability();
890              fail("SQLException not thrown");
891          } catch (SQLException e) {
892              //ok
893          }
894 
895     }
896 
897     /**
898      * java.sql.DatabaseMetaData#getSQLKeywords()
899      */
900     @KnownFailure("Ticket 98")
test_getSQLKeywords()901     public void test_getSQLKeywords() throws SQLException {
902         assertTrue("Incorrect SQL keywords", !"".equals(meta.getSQLKeywords()
903                 .trim()));
904 
905       //Exception checking
906         conn.close();
907 
908          try {
909              meta.getSQLKeywords();
910              fail("SQLException not thrown");
911          } catch (SQLException e) {
912              //ok
913          }
914 
915     }
916 
917     /**
918      * java.sql.DatabaseMetaData#getSQLStateType()
919      */
920     @KnownFailure("Ticket 98")
test_getSQLStateType()921     public void test_getSQLStateType() throws SQLException {
922         int type = meta.getSQLStateType();
923         switch (type) {
924         case DatabaseMetaData.sqlStateSQL99:
925         case DatabaseMetaData.sqlStateXOpen:
926             // these types are OK
927             break;
928         default:
929             fail("Incorrect SQL state types");
930         }
931 
932 
933       //Exception checking
934         conn.close();
935 
936          try {
937              meta.getSQLStateType();
938              fail("SQLException not thrown");
939          } catch (SQLException e) {
940              //ok
941          }
942 
943     }
944 
945     /**
946      * java.sql.DatabaseMetaData#getSchemas()
947      */
948     @KnownFailure("Ticket 98")
test_getSchemas()949     public void test_getSchemas() throws SQLException {
950         ResultSet rs = meta.getSchemas();
951         ResultSetMetaData rsmd = rs.getMetaData();
952         assertTrue("Rows do not obtained", rs.next());
953         int col = rsmd.getColumnCount();
954         assertEquals("Incorrect number of columns", 1, col);
955         String[] columnNames = { "TABLE_SCHEM", "TABLE_CATALOG" };
956         for (int c = 1; c <= col; ++c) {
957             assertEquals("Incorrect column name", columnNames[c - 1], rsmd
958                     .getColumnName(c));
959         }
960         rs.close();
961 
962 
963       //Exception checking
964         conn.close();
965 
966          try {
967              meta.getSchemas();
968              fail("SQLException not thrown");
969          } catch (SQLException e) {
970              //ok
971          }
972 
973     }
974 
975     /**
976      * java.sql.DatabaseMetaData#getSearchStringEscape()
977      */
978     @KnownFailure("Ticket 98")
test_getSearchStringEscape()979     public void test_getSearchStringEscape() throws SQLException {
980         assertTrue("Incorrect search string escape", !"".equals(meta
981                 .getSearchStringEscape().trim()));
982 
983       //Exception checking
984         conn.close();
985 
986          try {
987              meta.getSearchStringEscape();
988              fail("SQLException not thrown");
989          } catch (SQLException e) {
990              //ok
991          }
992 
993     }
994 
995     /**
996      * java.sql.DatabaseMetaData#getStringFunctions()
997      */
998     @KnownFailure("not supported")
test_getStringFunctions()999     public void test_getStringFunctions() throws SQLException {
1000         escapedFunctions(STRING_FUNCTIONS, meta.getStringFunctions());
1001 
1002 
1003       //Exception checking
1004         conn.close();
1005 
1006          try {
1007              meta.getStringFunctions();
1008              fail("SQLException not thrown");
1009          } catch (SQLException e) {
1010              //ok
1011          }
1012 
1013 
1014     }
1015 
1016 
1017     /**
1018      * java.sql.DatabaseMetaData#getSystemFunctions()
1019      */
1020     @KnownFailure("not supported")
test_getSystemFunctions()1021     public void test_getSystemFunctions() throws SQLException {
1022         escapedFunctions(SYSTEM_FUNCTIONS, meta.getSystemFunctions());
1023 
1024 
1025       //Exception checking
1026         conn.close();
1027 
1028          try {
1029              meta.getSystemFunctions();
1030              fail("SQLException not thrown");
1031          } catch (SQLException e) {
1032              //ok
1033          }
1034 
1035     }
1036 
1037 
1038     /**
1039      * java.sql.DatabaseMetaData#getTableTypes()
1040      */
1041     @KnownFailure("Ticket 98")
test_getTableTypes()1042     public void test_getTableTypes() throws SQLException {
1043         String[] tableTypes = { "LOCAL TEMPORARY", "TABLE", "VIEW" };
1044         ResultSet rs = meta.getTableTypes();
1045 
1046         while (rs.next()) {
1047             assertTrue("Wrong table type", Arrays.binarySearch(tableTypes, rs
1048                     .getString("TABLE_TYPE")) > -1);
1049         }
1050         rs.close();
1051 
1052 
1053       //Exception checking
1054         conn.close();
1055 
1056          try {
1057              meta.getTableTypes();
1058              fail("SQLException not thrown");
1059          } catch (SQLException e) {
1060              //ok
1061          }
1062 
1063     }
1064 
1065     /**
1066      * java.sql.DatabaseMetaData #getTables(java.lang.String,
1067      *        java.lang.String, java.lang.String, java.lang.String[])
1068      */
1069     @KnownFailure("If no schema is associated: returns empty string where actually null be returned?. Ticket 98")
test_getTablesLjava_lang_StringLjava_lang_StringLjava_lang_String$Ljava_lang_String()1070     public void test_getTablesLjava_lang_StringLjava_lang_StringLjava_lang_String$Ljava_lang_String()
1071             throws SQLException {
1072         String[] tablesName = {
1073                 VIEW_NAME, DatabaseCreator.TEST_TABLE1,
1074                 DatabaseCreator.TEST_TABLE3};
1075         String[] tablesType = {"TABLE", "VIEW"};
1076         Arrays.sort(tablesName);
1077         Arrays.sort(tablesType);
1078 
1079         // case 1. get all tables. There are two tables and one view in the
1080         // database
1081         ResultSet rs = meta.getTables(null, null, null, null);
1082         while (rs.next()) {
1083             assertTrue("Wrong table name", Arrays.binarySearch(tablesName, rs
1084                     .getString("TABLE_NAME")) > -1);
1085         //No Schema associated
1086             assertNull("Wrong table schema: "+rs.getString("TABLE_SCHEM"), rs.getString("TABLE_SCHEM"));
1087             assertTrue("Wrong table type", Arrays.binarySearch(tablesType, rs
1088                     .getString("TABLE_TYPE")) > -1);
1089             assertEquals("Wrong parameter REMARKS", "", rs.getString("REMARKS"));
1090         }
1091         rs.close();
1092 
1093         // case 2. get tables with specified types. There are no tables of such
1094         // types
1095         rs = meta.getTables(conn.getCatalog(), null, null, new String[] {
1096                 "SYSTEM TABLE", "LOCAL TEMPORARY" });
1097         assertFalse("Some tables exist", rs.next());
1098         rs.close();
1099 
1100         // case 3. get tables with specified types. There is a table of such
1101         // types
1102         rs = meta.getTables(conn.getCatalog(), null, null, new String[] {
1103                 "VIEW", "LOCAL TEMPORARY" });
1104 
1105         assertTrue("No tables exist", rs.next());
1106         assertEquals("Wrong table name", VIEW_NAME, rs.getString("TABLE_NAME"));
1107         assertNull("Wrong table schema: "+rs.getString("TABLE_SCHEM"), rs.getString("TABLE_SCHEM"));
1108         assertEquals("Wrong table type", "VIEW", rs.getString("TABLE_TYPE"));
1109         assertEquals("Wrong parameter REMARKS", "", rs.getString("REMARKS"));
1110         assertFalse("Wrong size of result set", rs.next());
1111         assertFalse("Some tables exist", rs.next());
1112         rs.close();
1113 
1114         // case 4. get all tables using tables pattern.
1115         // There are two tables and one view in the database
1116         rs = meta.getTables(null, null, "%", null);
1117 
1118         while (rs.next()) {
1119             assertTrue("Wrong table name", Arrays.binarySearch(tablesName, rs
1120                     .getString("TABLE_NAME")) > -1);
1121             assertNull("Wrong table schema ", rs.getString("TABLE_SCHEM"));
1122             assertTrue("Wrong table type", Arrays.binarySearch(tablesType, rs
1123                     .getString("TABLE_TYPE")) > -1);
1124             assertEquals("Wrong parameter REMARKS", "", rs.getString("REMARKS"));
1125         }
1126         rs.close();
1127 
1128 
1129       //Exception checking
1130         conn.close();
1131 
1132          try {
1133              meta.getTables(null, null, null, null);
1134              fail("SQLException not thrown");
1135          } catch (SQLException e) {
1136              //ok
1137          }
1138 
1139     }
1140 
1141     /**
1142      * java.sql.DatabaseMetaData#getTimeDateFunctions()
1143      */
1144     @KnownFailure("not supported")
test_getTimeDateFunctions()1145     public void test_getTimeDateFunctions() throws SQLException {
1146 
1147         escapedFunctions(TIMEDATE_FUNCTIONS, meta.getTimeDateFunctions());
1148 
1149 
1150       //Exception checking
1151         conn.close();
1152 
1153          try {
1154              meta.getTimeDateFunctions();
1155              fail("SQLException not thrown");
1156          } catch (SQLException e) {
1157              //ok
1158          }
1159     }
1160 
1161     /**
1162      * java.sql.DatabaseMetaData#getTypeInfo()
1163      */
1164     @KnownFailure("not supported")
test_getTypeInfo()1165     public void test_getTypeInfo() throws SQLException {
1166         insertNewRecord();
1167 
1168         ResultSet rs = meta.getTypeInfo();
1169 
1170         final String[] names = { "TYPE_NAME", "DATA_TYPE", "PRECISION",
1171                 "LITERAL_PREFIX", "LITERAL_SUFFIX", "CREATE_PARAMS",
1172                 "NULLABLE", "CASE_SENSITIVE", "SEARCHABLE",
1173                 "UNSIGNED_ATTRIBUTE", "FIXED_PREC_SCALE", "AUTO_INCREMENT",
1174                 "LOCAL_TYPE_NAME", "MINIMUM_SCALE", "MAXIMUM_SCALE",
1175                 "SQL_DATA_TYPE", "SQL_DATETIME_SUB", "NUM_PREC_RADIX" };
1176         Arrays.sort(names);
1177 
1178         for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) {
1179             assertTrue("wrong column was return", Arrays.binarySearch(names, rs
1180                     .getMetaData().getColumnName(i + 1)) > -1);
1181         }
1182 
1183         int[] types = { Types.ARRAY, Types.BIGINT, Types.BINARY, Types.BIT,
1184                 Types.BLOB, Types.BOOLEAN, Types.CHAR, Types.CLOB,
1185                 Types.DATALINK, Types.DATE, Types.DECIMAL, Types.DISTINCT,
1186                 Types.DOUBLE, Types.FLOAT, Types.INTEGER, Types.JAVA_OBJECT,
1187                 Types.LONGVARBINARY, Types.LONGVARCHAR, Types.NULL,
1188                 Types.NUMERIC, Types.OTHER, Types.REAL, Types.REF,
1189                 Types.SMALLINT, Types.STRUCT, Types.TIME, Types.TIMESTAMP,
1190                 Types.TINYINT, Types.VARBINARY, Types.VARCHAR };
1191         Arrays.sort(types);
1192 
1193         while (rs.next()) {
1194             assertTrue("wrong type was return ", Arrays.binarySearch(types, rs
1195                     .getInt("DATA_TYPE")) > -1);
1196         }
1197         rs.close();
1198 
1199       //Exception checking
1200         conn.close();
1201 
1202          try {
1203              meta.getTypeInfo();
1204              fail("SQLException not thrown");
1205          } catch (SQLException e) {
1206              //ok
1207          }
1208 
1209     }
1210 
1211 
1212     /**
1213      * java.sql.DatabaseMetaData#getURL()
1214      */
1215     @KnownFailure("Ticket 98")
test_getURL()1216     public void test_getURL() throws SQLException {
1217         assertEquals("Wrong url", Support_SQL.sqlUrl, meta.getURL());
1218 
1219       //Exception checking
1220         conn.close();
1221 
1222          try {
1223              meta.getURL();
1224              fail("SQLException not thrown");
1225          } catch (SQLException e) {
1226              //ok
1227          }
1228 
1229     }
1230 
1231     @KnownFailure("Ticket 98")
s()1232     public void s() throws SQLException {
1233       assertEquals("Wrong user name", Support_SQL.sqlUser, meta.getUserName());
1234 
1235       //Exception checking
1236         conn.close();
1237 
1238          try {
1239              meta.getUserName();
1240              fail("SQLException not thrown");
1241          } catch (SQLException e) {
1242              //ok
1243          }
1244 
1245     }
1246 
1247 
1248     /**
1249      * java.sql.DatabaseMetaData#insertsAreDetected(int)
1250      */
1251     @KnownFailure("Ticket 98")
test_insertsAreDetectedI()1252     public void test_insertsAreDetectedI() throws SQLException {
1253         assertFalse(
1254                 "visible row insert can be detected for TYPE_FORWARD_ONLY type",
1255                 meta.insertsAreDetected(ResultSet.TYPE_FORWARD_ONLY));
1256         assertFalse(
1257                 "visible row insert can be detected for TYPE_SCROLL_INSENSITIVE type",
1258                 meta.insertsAreDetected(ResultSet.TYPE_SCROLL_INSENSITIVE));
1259         assertFalse(
1260                 "visible row insert can be detected for TYPE_SCROLL_SENSITIVE type",
1261                 meta.insertsAreDetected(ResultSet.TYPE_SCROLL_SENSITIVE));
1262 
1263 
1264       //Exception checking
1265         conn.close();
1266 
1267          try {
1268              meta.insertsAreDetected(ResultSet.TYPE_SCROLL_SENSITIVE);
1269              fail("SQLException not thrown");
1270          } catch (SQLException e) {
1271              //ok
1272          }
1273 
1274     }
1275 
1276     /**
1277      * java.sql.DatabaseMetaData#isReadOnly()
1278      */
1279     @KnownFailure("Ticket 98")
test_isReadOnly()1280     public void test_isReadOnly() throws SQLException {
1281         assertFalse("database is not read-only", meta.isReadOnly());
1282 
1283 
1284       //Exception checking
1285         conn.close();
1286 
1287          try {
1288              meta.isReadOnly();
1289              fail("SQLException not thrown");
1290          } catch (SQLException e) {
1291              //ok
1292          }
1293     }
1294 
1295     /**
1296      * java.sql.DatabaseMetaData#othersDeletesAreVisible(int)
1297      */
1298     @KnownFailure("Ticket 98")
test_othersDeletesAreVisibleI()1299     public void test_othersDeletesAreVisibleI() throws SQLException {
1300         assertFalse(
1301                 "deletes made by others are visible for TYPE_FORWARD_ONLY type",
1302                 meta.othersDeletesAreVisible(ResultSet.TYPE_FORWARD_ONLY));
1303         assertFalse(
1304                 "deletes made by others are visible for TYPE_SCROLL_INSENSITIVE type",
1305                 meta.othersDeletesAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE));
1306         assertFalse(
1307                 "deletes made by others are visible for TYPE_SCROLL_SENSITIVE type",
1308                 meta.othersDeletesAreVisible(ResultSet.TYPE_SCROLL_SENSITIVE));
1309 
1310 
1311       //Exception checking
1312         conn.close();
1313 
1314          try {
1315              assertFalse("inserts made by others are visible for unknown type", meta
1316                      .othersDeletesAreVisible(ResultSet.CONCUR_READ_ONLY));
1317              fail("SQLException not thrown");
1318          } catch (SQLException e) {
1319              //ok
1320          }
1321 
1322     }
1323 
1324     /**
1325      * java.sql.DatabaseMetaData#othersInsertsAreVisible(int)
1326      */
1327     @KnownFailure("Ticket 98")
test_othersInsertsAreVisibleI()1328     public void test_othersInsertsAreVisibleI() throws SQLException {
1329         assertFalse(
1330                 "inserts made by others are visible for TYPE_FORWARD_ONLY type",
1331                 meta.othersInsertsAreVisible(ResultSet.TYPE_FORWARD_ONLY));
1332         assertFalse(
1333                 "inserts made by others are visible for TYPE_SCROLL_INSENSITIVE type",
1334                 meta.othersInsertsAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE));
1335         assertFalse(
1336                 "inserts made by others are visible for TYPE_SCROLL_SENSITIVE type",
1337                 meta.othersInsertsAreVisible(ResultSet.TYPE_SCROLL_SENSITIVE));
1338 
1339 
1340       //Exception checking
1341         conn.close();
1342 
1343          try {
1344              assertFalse("inserts made by others are visible for unknown type", meta
1345                      .othersInsertsAreVisible(ResultSet.CONCUR_READ_ONLY));
1346              fail("SQLException not thrown");
1347          } catch (SQLException e) {
1348              //ok
1349          }
1350 
1351     }
1352 
1353     /**
1354      * java.sql.DatabaseMetaData#othersUpdatesAreVisible(int)
1355      */
1356     @KnownFailure("Ticket 98")
test_othersUpdatesAreVisibleI()1357     public void test_othersUpdatesAreVisibleI() throws SQLException {
1358         assertFalse(
1359                 "updates made by others are visible for TYPE_FORWARD_ONLY type",
1360                 meta.othersUpdatesAreVisible(ResultSet.TYPE_FORWARD_ONLY));
1361         assertFalse(
1362                 "updates made by others are visible for TYPE_SCROLL_INSENSITIVE type",
1363                 meta.othersUpdatesAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE));
1364         assertFalse(
1365                 "updates made by others are visible for TYPE_SCROLL_SENSITIVE type",
1366                 meta.othersUpdatesAreVisible(ResultSet.TYPE_SCROLL_SENSITIVE));
1367 
1368       //Exception checking
1369 
1370          try {
1371              assertFalse("updates made by others are visible for unknown type", meta
1372                      .othersUpdatesAreVisible(ResultSet.CONCUR_READ_ONLY));
1373              fail("SQLException not thrown");
1374          } catch (SQLException e) {
1375              //ok
1376          }
1377     }
1378 
1379     /**
1380      * java.sql.DatabaseMetaData#storesMixedCaseQuotedIdentifiers()
1381      */
test_storesMixedCaseQuotedIdentifiers()1382     public void test_storesMixedCaseQuotedIdentifiers() throws SQLException {
1383         String quote = meta.getIdentifierQuoteString();
1384 
1385 
1386 
1387         insertNewRecord();
1388 
1389         String selectQuery = "SELECT " + quote + "fieLD1" + quote + " FROM "
1390                 + DatabaseCreator.TEST_TABLE1;
1391 
1392         try {
1393             statement.executeQuery(selectQuery);
1394             if (!meta.storesMixedCaseIdentifiers()) {
1395                 fail("mixed case is supported");
1396             }
1397         } catch (SQLException e) {
1398             if (meta.storesMixedCaseQuotedIdentifiers()) {
1399                 fail("quoted case is not supported");
1400             }
1401         }
1402 
1403         //Exception checking
1404         /*
1405         conn.close();
1406 
1407          try {
1408              meta.storesMixedCaseIdentifiers();
1409              fail("SQLException not thrown");
1410          } catch (SQLException e) {
1411              //ok
1412          }
1413 
1414          conn.close();
1415 
1416          try {
1417              meta.storesMixedCaseQuotedIdentifiers();
1418              fail("SQLException not thrown");
1419          } catch (SQLException e) {
1420              //ok
1421          }
1422 
1423     }
1424 
1425     @KnownFailure("Ticket 98")
1426     public void testGetIdentifierQuoteString() throws SQLException {
1427        assertNotNull(
1428                meta.getIdentifierQuoteString()
1429                );
1430 
1431        //Exception test
1432        /*
1433        conn.close();
1434        try {
1435            meta.getIdentifierQuoteString();
1436            fail("Should throw exception");
1437        } catch (SQLException e) {
1438            //ok
1439        }
1440        */
1441 
1442     }
1443 
1444 
1445     /**
1446      * java.sql.DatabaseMetaData#supportsColumnAliasing()
1447      */
1448     @KnownFailure("not supported. SQLException checking test fails")
test_supportsColumnAliasing()1449     public void test_supportsColumnAliasing() throws SQLException {
1450         insertNewRecord();
1451 
1452         String alias = "FIELD3";
1453         String selectQuery = "SELECT field1 AS " + alias + " FROM "
1454                 + DatabaseCreator.TEST_TABLE1;
1455         ResultSet rs = statement.executeQuery(selectQuery);
1456         ResultSetMetaData rsmd = rs.getMetaData();
1457 
1458         if (meta.supportsColumnAliasing()) {
1459             // supports aliasing
1460             assertEquals("Wrong count of columns", 1, rsmd.getColumnCount());
1461             assertEquals("Aliasing is not supported", alias, rsmd
1462                     .getColumnLabel(1));
1463         } else {
1464             // doesn't support aliasing
1465             assertEquals("Aliasing is supported", 0, rsmd.getColumnCount());
1466         }
1467         rs.close();
1468 
1469       //Exception checking
1470         conn.close();
1471 
1472          try {
1473              meta.supportsColumnAliasing();
1474              fail("SQLException not thrown");
1475          } catch (SQLException e) {
1476              //ok
1477          }
1478 
1479     }
1480 
1481 
1482     /**
1483      * java.sql.DatabaseMetaData#supportsExpressionsInOrderBy()
1484      */
1485     @KnownFailure("exception test fails")
test_supportsExpressionsInOrderBy()1486     public void test_supportsExpressionsInOrderBy() throws SQLException {
1487         insertNewRecord();
1488 
1489         String selectQuery = "SELECT * FROM " + DatabaseCreator.TEST_TABLE1
1490                 + " ORDER BY id + field3";
1491 
1492         try {
1493             statement.executeQuery(selectQuery);
1494             if (!meta.supportsExpressionsInOrderBy()) {
1495                 fail("Expressions in order by are supported");
1496             }
1497         } catch (SQLException e) {
1498             if (meta.supportsExpressionsInOrderBy()) {
1499                 fail("Expressions in order by are not supported");
1500             }
1501         }
1502 
1503       //Exception checking
1504         conn.close();
1505 
1506          try {
1507              meta.supportsExpressionsInOrderBy();
1508              fail("SQLException not thrown");
1509          } catch (SQLException e) {
1510              //ok
1511          }
1512 
1513     }
1514 
1515 
1516     /**
1517      * java.sql.DatabaseMetaData#supportsGroupBy()
1518      */
1519     @KnownFailure("exception test fails")
test_supportsGroupBy()1520     public void test_supportsGroupBy() throws SQLException {
1521         insertNewRecord();
1522 
1523         String selectQuery = "SELECT * FROM " + DatabaseCreator.TEST_TABLE1
1524                 + " GROUP BY field3";
1525 
1526         try {
1527             statement.executeQuery(selectQuery);
1528             if (!meta.supportsGroupBy()) {
1529                 fail("group by are supported");
1530             }
1531         } catch (SQLException e) {
1532             if (meta.supportsGroupBy()) {
1533                 fail("group by are not supported");
1534             }
1535         }
1536 
1537       //Exception checking
1538         conn.close();
1539 
1540          try {
1541              meta.supportsGroupBy();
1542              fail("SQLException not thrown");
1543          } catch (SQLException e) {
1544              //ok
1545          }
1546 
1547     }
1548 
1549 
1550     /**
1551      * java.sql.DatabaseMetaData#supportsGroupByUnrelated()
1552      */
1553     @KnownFailure("exception test fails")
test_supportsGroupByUnrelated()1554     public void test_supportsGroupByUnrelated() throws SQLException {
1555         insertNewRecord();
1556 
1557         String selectQuery = "SELECT field1, field2 FROM "
1558                 + DatabaseCreator.TEST_TABLE1 + " GROUP BY field3";
1559 
1560         try {
1561             statement.executeQuery(selectQuery);
1562             if (!meta.supportsGroupByUnrelated()) {
1563                 fail("unrelated columns in group by are supported");
1564             }
1565         } catch (SQLException e) {
1566             if (meta.supportsGroupByUnrelated()) {
1567                 fail("unrelated columns in group by are not supported");
1568             }
1569         }
1570 
1571         //Exception checking
1572         conn.close();
1573 
1574          try {
1575              meta.supportsGroupByUnrelated();
1576              fail("SQLException not thrown");
1577          } catch (SQLException e) {
1578              //ok
1579          }
1580 
1581     }
1582 
1583     /**
1584      * java.sql.DatabaseMetaData#supportsNonNullableColumns()
1585      */
1586     @KnownFailure("Ticket 98")
test_supportsNonNullableColumns()1587     public void test_supportsNonNullableColumns() throws SQLException {
1588         assertTrue(
1589                 "columns in this database may not be defined as non-nullable",
1590                 meta.supportsNonNullableColumns());
1591         statementForward.execute("create table companies(id integer not null);");
1592         statementForward.execute("drop table companies");
1593 
1594 
1595       //Exception checking
1596         conn.close();
1597 
1598          try {
1599              meta.supportsNonNullableColumns();
1600              fail("SQLException not thrown");
1601          } catch (SQLException e) {
1602              //ok
1603          }
1604 
1605     }
1606 
1607     /**
1608      * java.sql.DatabaseMetaData#supportsOrderByUnrelated()
1609      */
1610     @KnownFailure("exception test fails")
test_supportsOrderByUnrelated()1611     public void test_supportsOrderByUnrelated() throws SQLException {
1612         insertNewRecord();
1613 
1614         String selectQuery = "SELECT field1, field2 FROM "
1615                 + DatabaseCreator.TEST_TABLE1 + " ORDER BY id + field3";
1616 
1617         try {
1618             statement.executeQuery(selectQuery);
1619             if (!meta.supportsOrderByUnrelated()) {
1620                 fail("unrelated columns in order by are supported");
1621             }
1622         } catch (SQLException e) {
1623             if (meta.supportsOrderByUnrelated()) {
1624                 fail("unrelated columns in order by are not supported");
1625             }
1626         }
1627 
1628       //Exception checking
1629         conn.close();
1630 
1631          try {
1632              meta.supportsOrderByUnrelated();
1633              fail("SQLException not thrown");
1634          } catch (SQLException e) {
1635              //ok
1636          }
1637 
1638     }
1639 
1640     /**
1641      * java.sql.DatabaseMetaData#supportsSelectForUpdate()
1642      */
1643     @KnownFailure("exception test fails")
test_supportsSelectForUpdate()1644     public void test_supportsSelectForUpdate() throws SQLException {
1645         insertNewRecord();
1646 
1647         String selectQuery = "SELECT field1 FROM "
1648                 + DatabaseCreator.TEST_TABLE1 + " FOR UPDATE";
1649 
1650         try {
1651             statement.executeQuery(selectQuery);
1652             if (!meta.supportsSelectForUpdate()) {
1653                 fail("select for update are supported");
1654             }
1655         } catch (SQLException e) {
1656             if (!meta.supportsSelectForUpdate()) {
1657                 fail("select for update are not supported");
1658             }
1659         }
1660 
1661 
1662       //Exception checking
1663         conn.close();
1664 
1665          try {
1666              meta.supportsSelectForUpdate();
1667              fail("SQLException not thrown");
1668          } catch (SQLException e) {
1669              //ok
1670          }
1671 
1672     }
1673 
1674     /**
1675      * java.sql.DatabaseMetaData#supportsSubqueriesInExists()
1676      */
1677     @KnownFailure("exception test fails")
test_supportsSubqueriesInExists()1678     public void test_supportsSubqueriesInExists() throws SQLException {
1679         insertNewRecord();
1680 
1681         String selectQuery = "SELECT field1 FROM "
1682                 + DatabaseCreator.TEST_TABLE1
1683                 + " WHERE EXISTS(SELECT field2 FROM "
1684                 + DatabaseCreator.TEST_TABLE1 + ")";
1685 
1686         try {
1687             statement.executeQuery(selectQuery);
1688             if (!meta.supportsSubqueriesInExists()) {
1689                 fail("Subqueries in exists are supported");
1690             }
1691         } catch (SQLException e) {
1692             if (meta.supportsSubqueriesInExists()) {
1693                 fail("Subqueries in exists are not supported");
1694             }
1695         }
1696 
1697 
1698       //Exception checking
1699         conn.close();
1700 
1701          try {
1702              meta.supportsSubqueriesInExists();
1703              fail("SQLException not thrown");
1704          } catch (SQLException e) {
1705              //ok
1706          }
1707 
1708     }
1709 
1710     /**
1711      * java.sql.DatabaseMetaData#supportsTableCorrelationNames()
1712      */
1713     @KnownFailure("exception test fails")
test_supportsTableCorrelationNames()1714     public void test_supportsTableCorrelationNames() throws SQLException {
1715 
1716         insertNewRecord();
1717         assertFalse(conn.isClosed());
1718 
1719         String corelationName = "TABLE_NAME";
1720         String selectQuery = "SELECT * FROM " + DatabaseCreator.TEST_TABLE1
1721                 + " AS " + corelationName;
1722         ResultSet rs = statementForward.executeQuery(selectQuery);
1723         ResultSetMetaData rsmd = rs.getMetaData();
1724         int numOfColumn = rsmd.getColumnCount();
1725 
1726         for (int i = 0; i < numOfColumn; i++) {
1727             if (meta.supportsTableCorrelationNames()) {
1728                 assertEquals("Corelation names is now supported",
1729                         corelationName, rsmd.getTableName(i + 1));
1730             } else {
1731                 assertEquals("Corelation names is supported",
1732                         DatabaseCreator.TEST_TABLE1, rsmd.getTableName(i + 1));
1733             }
1734         }
1735 
1736 
1737       //Exception checking
1738         conn.close();
1739 
1740          try {
1741              meta.supportsTableCorrelationNames();
1742              fail("SQLException not thrown");
1743          } catch (SQLException e) {
1744              //ok
1745          }
1746 
1747     }
1748 
1749     /**
1750      * java.sql.DatabaseMetaData#supportsTransactionIsolationLevel(int)
1751      */
test_supportsTransactionIsolationLevelI()1752     public void test_supportsTransactionIsolationLevelI() throws SQLException {
1753         assertFalse("database supports TRANSACTION_NONE isolation level", meta
1754                 .supportsTransactionIsolationLevel(Connection.TRANSACTION_NONE));
1755         // TODO only Connection.TRANSACTION_SERIALIZABLE is supported
1756 //        assertTrue(
1757 //                "database doesn't supports TRANSACTION_READ_COMMITTED isolation level",
1758 //                meta
1759 //                        .supportsTransactionIsolationLevel(Connection.TRANSACTION_READ_COMMITTED));
1760 //        assertTrue(
1761 //                "database doesn't supports TRANSACTION_READ_UNCOMMITTED isolation level",
1762 //                meta
1763 //                        .supportsTransactionIsolationLevel(Connection.TRANSACTION_READ_UNCOMMITTED));
1764 //        assertTrue(
1765 //               "database doesn't supports TRANSACTION_REPEATABLE_READ isolation level",
1766 //                meta
1767 //                        .supportsTransactionIsolationLevel(Connection.TRANSACTION_REPEATABLE_READ));
1768         assertTrue(
1769                 "database doesn't supports TRANSACTION_SERIALIZABLE isolation level",
1770                 meta
1771                         .supportsTransactionIsolationLevel(Connection.TRANSACTION_SERIALIZABLE));
1772 
1773 
1774       //Exception checking
1775 
1776          try {
1777              assertFalse("database supports unknown isolation level", meta
1778                      .supportsTransactionIsolationLevel(Integer.MAX_VALUE));;
1779          } catch (SQLException e) {
1780              //ok
1781          }
1782     }
1783 
1784     /**
1785      * java.sql.DatabaseMetaData#updatesAreDetected(int)
1786      */
test_updatesAreDetectedI()1787     public void test_updatesAreDetectedI() throws SQLException {
1788         assertFalse(
1789                 "visible row update can be detected for TYPE_FORWARD_ONLY type",
1790                 meta.updatesAreDetected(ResultSet.TYPE_FORWARD_ONLY));
1791         assertFalse(
1792                 "visible row update can be detected for TYPE_SCROLL_INSENSITIVE type",
1793                 meta.updatesAreDetected(ResultSet.TYPE_SCROLL_INSENSITIVE));
1794         assertFalse(
1795                 "visible row update can be detected for TYPE_SCROLL_SENSITIVE type",
1796                 meta.updatesAreDetected(ResultSet.TYPE_SCROLL_SENSITIVE));
1797         assertFalse("visible row update can be detected for unknown type", meta
1798                 .updatesAreDetected(100));
1799 
1800         //Exception checking
1801        conn.close();
1802 
1803         try {
1804             meta.updatesAreDetected(ResultSet.CLOSE_CURSORS_AT_COMMIT);
1805             assertFalse("visible row update can be detected for unknown type", meta
1806                     .updatesAreDetected(ResultSet.CLOSE_CURSORS_AT_COMMIT));
1807 
1808         } catch (SQLException e) {
1809             //ok
1810         }
1811     }
1812 
1813 
insertNewRecord()1814     protected static void insertNewRecord() throws SQLException {
1815         if (conn.isClosed()) {
1816             System.out.println("DatabaseMetaDataTest.insertNewRecord() : closed");
1817         }
1818 
1819         String insertQuery = "INSERT INTO " + DatabaseCreator.TEST_TABLE1
1820                 + " (id, field1, field2, field3) VALUES(" + id + ", '"
1821                 + "value" + id + "', " + id + ", " + id + ")";
1822         id++;
1823         statement.execute(insertQuery);
1824     }
1825 
1826     //BEGIN APACHE-DERBY
1827 
1828     /**
1829      * Test Method from Apache Derby Project
1830      * Class
1831      * org.apache.derbyTesting.functionTests.tests.jdbcapi.DatabaseMetaDataTest
1832      *
1833      * Compare a ResultSet from getColumns() with ResultSetMetaData returned
1834      * from a SELECT * against the table. This method handles situations where a
1835      * full set of the columns are in the ResultSet. The first action is to call
1836      * rs.next(). The ResultSet will be closed by this method.
1837      *
1838      * @param rs
1839      *            resultset to crossCheck
1840      * @param partial
1841      *            used to indicate if ordinal position should get checked
1842      * @return the number of rows in the resultSet
1843      * @throws SQLException
1844      */
crossCheckGetColumnsAndResultSetMetaData(ResultSet rs, boolean partial)1845     private int crossCheckGetColumnsAndResultSetMetaData(ResultSet rs,
1846             boolean partial)
1847     throws SQLException
1848     {
1849         Statement s = conn.createStatement();
1850         while (rs.next())
1851         {
1852             String schema = rs.getString("TABLE_SCHEM");
1853             String table = rs.getString("TABLE_NAME");
1854 
1855             ResultSet rst = s.executeQuery(
1856                 "SELECT * FROM " + schema+"."+table);
1857             ResultSetMetaData rsmdt = rst.getMetaData();
1858 
1859 
1860             for (int col = 1; col <= rsmdt.getColumnCount() ; col++)
1861             {
1862                 if (!partial) {
1863                     if (col != 1)
1864                         assertTrue(rs.next());
1865 
1866                     assertEquals("ORDINAL_POSITION",
1867                             col, rs.getInt("ORDINAL_POSITION"));
1868                 }
1869 
1870                 assertEquals("TABLE_CAT",
1871                         "", rs.getString("TABLE_CAT"));
1872                 assertEquals("TABLE_SCHEM",
1873                         schema, rs.getString("TABLE_SCHEM"));
1874                 assertEquals("TABLE_NAME",
1875                         table, rs.getString("TABLE_NAME"));
1876 
1877                 crossCheckGetColumnRowAndResultSetMetaData(rs, rsmdt);
1878                 if (partial)
1879                     break;
1880 
1881             }
1882             rst.close();
1883 
1884 
1885         }
1886         int count = rs.getRow();
1887         rs.close();
1888         s.close();
1889         return count;
1890     }
1891 
1892     /**
1893      * * Test Method from Apache Derby Project
1894      * Class
1895      * org.apache.derbyTesting.functionTests.tests.jdbcapi.DatabaseMetaDataTest
1896      *
1897      * Cross check a single row from getColumns() with ResultSetMetaData
1898      * for a SELECT * from the same table.
1899      * @param rs ResultSet from getColumns already positioned on the row.
1900      * @param rsmdt ResultSetMetaData for the SELECT *
1901      * @param odbc 0 for JDBC call, 1 for ODBC. Needed to allow for difference
1902      *    in using BUFFER_LENGTH (ODBC) or no(JDBC).
1903      * @throws SQLException
1904      */
crossCheckGetColumnRowAndResultSetMetaData( ResultSet rs, ResultSetMetaData rsmdt)1905     public static void crossCheckGetColumnRowAndResultSetMetaData(
1906             ResultSet rs, ResultSetMetaData rsmdt)
1907         throws SQLException
1908     {
1909         int col = rs.getInt("ORDINAL_POSITION");
1910 
1911         assertEquals("RSMD.getCatalogName",
1912                 rsmdt.getCatalogName(col), rs.getString("TABLE_CAT"));
1913         assertEquals("RSMD.getSchemaName",
1914                 rsmdt.getSchemaName(col), rs.getString("TABLE_SCHEM"));
1915         assertEquals("RSMD.getTableName",
1916                 rsmdt.getTableName(col), rs.getString("TABLE_NAME"));
1917 
1918         assertEquals("COLUMN_NAME",
1919                 rsmdt.getColumnName(col), rs.getString("COLUMN_NAME"));
1920 
1921         // DERBY-2285 BOOLEAN columns appear different on
1922         // network client.
1923         // meta returns BOOLEAN
1924         // RSMD returns SMALLINT
1925         int metaColumnType = rs.getInt("DATA_TYPE");
1926         if (metaColumnType == Types.BOOLEAN )
1927         {
1928             assertEquals("TYPE_NAME",
1929                     "BOOLEAN", rs.getString("TYPE_NAME"));
1930             assertEquals("TYPE_NAME",
1931                     "SMALLINT", rsmdt.getColumnTypeName(col));
1932 
1933             assertEquals("DATA_TYPE",
1934                     Types.SMALLINT, rsmdt.getColumnType(col));
1935         }
1936         else if (metaColumnType == Types.JAVA_OBJECT)
1937         {
1938             // meta returns JAVA_OBJECT
1939             // RSMD returns LONGVARBINARY!
1940             assertEquals("DATA_TYPE",
1941                     Types.LONGVARBINARY, rsmdt.getColumnType(col));
1942         }
1943         else if (metaColumnType == Types.VARBINARY )
1944         {
1945             // meta returns different type name to RSMD
1946             assertEquals("DATA_TYPE",
1947                     Types.VARBINARY, rsmdt.getColumnType(col));
1948         }
1949         else if (metaColumnType == Types.BINARY )
1950         {
1951             // meta returns different type name to RSMD
1952             assertEquals("DATA_TYPE",
1953                     Types.BINARY, rsmdt.getColumnType(col));
1954         }
1955         else if (metaColumnType == Types.NUMERIC )
1956         {
1957             // DERBY-584 inconsistency in numeric & decimal
1958             assertEquals("DATA_TYPE",
1959                     Types.DECIMAL, rsmdt.getColumnType(col));
1960 
1961             assertEquals("TYPE_NAME",
1962                     "DECIMAL", rsmdt.getColumnTypeName(col));
1963 
1964             assertEquals("TYPE_NAME",
1965                     "NUMERIC", rs.getString("TYPE_NAME"));
1966         }
1967         else
1968         {
1969             assertEquals("DATA_TYPE",
1970                 rsmdt.getColumnType(col), rs.getInt("DATA_TYPE"));
1971             assertEquals("TYPE_NAME",
1972                 rsmdt.getColumnTypeName(col), rs.getString("TYPE_NAME"));
1973         }
1974 
1975         /*
1976         if (metaColumnType != Types.JAVA_OBJECT) {
1977         System.out.println("TYPE " + rs.getInt("DATA_TYPE"));
1978         System.out.println(JDBC.escape(schema, table) + " " + rs.getString("COLUMN_NAME"));
1979         assertEquals("COLUMN_SIZE",
1980                 rsmdt.getPrecision(col), rs.getInt("COLUMN_SIZE"));
1981         }
1982         */
1983 
1984         /*
1985         assertEquals("DECIMAL_DIGITS",
1986                 rsmdt.getScale(col), rs.getInt("DECIMAL_DIGITS"));
1987         */
1988 
1989         // This assumes the constants defined by meta and ResultSet
1990         // for nullability are equal. They are by inspection
1991         // and since they are static final and part of a defined
1992         // api by definition they cannot change. We also
1993         // check statically this is true in the testConstants fixture.
1994         assertEquals("NULLABLE",
1995                 rsmdt.isNullable(col), rs.getInt("NULLABLE"));
1996 
1997         // REMARKS set to empty string by Derby
1998         assertEquals("REMARKS", "", rs.getString("REMARKS"));
1999 
2000 
2001         // IS_NULLABLE
2002         switch (rsmdt.isNullable(col))
2003         {
2004         case ResultSetMetaData.columnNoNulls:
2005             assertEquals("IS_NULLABLE", "NO", rs.getString("IS_NULLABLE"));
2006             break;
2007         case ResultSetMetaData.columnNullable:
2008             assertEquals("IS_NULLABLE", "YES", rs.getString("IS_NULLABLE"));
2009             break;
2010         case ResultSetMetaData.columnNullableUnknown:
2011             assertEquals("IS_NULLABLE", "", rs.getString("IS_NULLABLE"));
2012             break;
2013         default:
2014             fail("invalid return from rsmdt.isNullable(col)");
2015         }
2016 
2017         // SCOPE not supported
2018         assertNull("SCOPE_CATLOG", rs.getString("SCOPE_CATLOG"));
2019         assertNull("SCOPE_SCHEMA", rs.getString("SCOPE_SCHEMA"));
2020         assertNull("SCOPE_TABLE", rs.getString("SCOPE_TABLE"));
2021 
2022         // DISTINCT not supported
2023         assertEquals("SOURCE_DATA_TYPE", 0, rs.getShort("SOURCE_DATA_TYPE"));
2024         assertTrue(rs.wasNull());
2025 
2026         // IS_AUTOINCREMENT added in JDBC 4.0
2027        assertEquals("IS_AUTOINCREMENT",
2028                rsmdt.isAutoIncrement(col) ? "YES" : "NO",
2029                rs.getString("IS_AUTOINCREMENT"));
2030        assertFalse(rs.wasNull());
2031     }
2032 
2033     /*
2034      * Check the shape of the ResultSet from any getColumns call.
2035      */
checkColumnsShape(ResultSet rs)2036     private void checkColumnsShape(ResultSet rs) throws SQLException {
2037         int[] columnTypes = new int[] {
2038                 Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR,
2039                 Types.SMALLINT, Types.VARCHAR, Types.INTEGER, Types.INTEGER,
2040                 Types.INTEGER, Types.INTEGER, Types.INTEGER, Types.VARCHAR,
2041                 Types.VARCHAR, Types.INTEGER, Types.INTEGER, Types.INTEGER,
2042                 Types.INTEGER, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR,
2043                 Types.VARCHAR, Types.SMALLINT, Types.VARCHAR};
2044 
2045         assertMetaDataResultSet(rs, new String[] {
2046                 "TABLE_CAT", "TABLE_SCHEM", "TABLE_NAME", "COLUMN_NAME",
2047                 "DATA_TYPE", "TYPE_NAME", "COLUMN_SIZE", "BUFFER_LENGTH",
2048                 "DECIMAL_DIGITS", "NUM_PREC_RADIX", "NULLABLE", "REMARKS",
2049                 "COLUMN_DEF", "SQL_DATA_TYPE", "SQL_DATETIME_SUB",
2050                 "CHAR_OCTET_LENGTH", "ORDINAL_POSITION", "IS_NULLABLE",
2051                 "SCOPE_CATLOG", "SCOPE_SCHEMA", "SCOPE_TABLE",
2052                 "SOURCE_DATA_TYPE", "IS_AUTOINCREMENT"}, columnTypes, null);
2053 
2054     }
2055 
assertMetaDataResultSet(ResultSet rs, String[] columnNames, int[] columnTypes, boolean[] nullability)2056     public static void assertMetaDataResultSet(ResultSet rs,
2057             String[] columnNames, int[] columnTypes,
2058             boolean[] nullability) throws SQLException
2059     {
2060      // see ResultSetGetterTest, getType() -> this test fails currently
2061         assertEquals(ResultSet.TYPE_FORWARD_ONLY, rs.getType());
2062         assertEquals(ResultSet.CONCUR_READ_ONLY, rs.getConcurrency());
2063 
2064         if (columnNames != null)
2065             assertColumnNames(rs, columnNames);
2066         if (columnTypes != null)
2067             assertColumnTypes(rs, columnTypes);
2068         if (nullability != null)
2069             assertNullability(rs, nullability);
2070     }
2071 
2072     /**
2073      * * Test Method from Apache Derby Project
2074      *   Class
2075      * org.apache.derbyTesting.functionTests.tests.jdbcapi.DatabaseMetaDataTest
2076      *
2077      * Takes a result set and an array of expected colum names (as
2078      * Strings)  and asserts that the column names in the result
2079      * set metadata match the number, order, and names of those
2080      * in the array.
2081      *
2082      * @param rs ResultSet for which we're checking column names.
2083      * @param expectedColNames Array of expected column names.
2084      */
assertColumnNames(ResultSet rs, String [] expectedColNames)2085     public static void assertColumnNames(ResultSet rs,
2086         String [] expectedColNames) throws SQLException
2087     {
2088         ResultSetMetaData rsmd = rs.getMetaData();
2089         int actualCols = rsmd.getColumnCount();
2090 
2091         for (int i = 0; i < actualCols; i++)
2092         {
2093         assertEquals("Column names do not match:",
2094                 expectedColNames[i], rsmd.getColumnName(i+1));
2095         }
2096 
2097         assertEquals("Unexpected column count:",
2098         expectedColNames.length, rsmd.getColumnCount());
2099     }
2100 
2101     /**
2102      * Test Method from Apache Derby Project
2103      * Class
2104      * org.apache.derbyTesting.functionTests.tests.jdbcapi.DatabaseMetaDataTest
2105      *
2106      * Takes a result set and an array of expected column types
2107      * from java.sql.Types
2108      * and asserts that the column types in the result
2109      * set metadata match the number, order, and names of those
2110      * in the array.
2111      *
2112      * No length information for variable length types
2113      * can be passed. For ResultSets from JDBC DatabaseMetaData
2114      * the specification only indicates the types of the
2115      * columns, not the length.
2116      *
2117      * @param rs ResultSet for which we're checking column names.
2118      * @param expectedTypes Array of expected column types.
2119      */
assertColumnTypes(ResultSet rs, int[] expectedTypes)2120     public static void assertColumnTypes(ResultSet rs,
2121         int[] expectedTypes) throws SQLException
2122     {
2123         ResultSetMetaData rsmd = rs.getMetaData();
2124         int actualCols = rsmd.getColumnCount();
2125 
2126         assertEquals("Unexpected column count:",
2127                 expectedTypes.length, rsmd.getColumnCount());
2128 
2129         for (int i = 0; i < actualCols; i++)
2130         {
2131        assertEquals("Column types do not match for column " + (i+1),
2132                     expectedTypes[i], rsmd.getColumnType(i+1));
2133         }
2134     }
2135 
2136     /**
2137      * Check the nullability of the column definitions for
2138      * the ResultSet matches the expected values.
2139      * @param rs
2140      * @param nullability
2141      * @throws SQLException
2142      */
assertNullability(ResultSet rs, boolean[] nullability)2143     public static void assertNullability(ResultSet rs,
2144             boolean[] nullability) throws SQLException
2145     {
2146         ResultSetMetaData rsmd = rs.getMetaData();
2147         int actualCols = rsmd.getColumnCount();
2148 
2149         assertEquals("Unexpected column count:",
2150                 nullability.length, rsmd.getColumnCount());
2151 
2152         for (int i = 0; i < actualCols; i++)
2153         {
2154             int expected = nullability[i] ?
2155                ResultSetMetaData.columnNullable : ResultSetMetaData.columnNoNulls;
2156        assertEquals("Column nullability do not match for column " + (i+1),
2157                     expected, rsmd.isNullable(i+1));
2158         }
2159     }
2160 
2161     //BEGIN Apache Derby DatabaseMetaDataTest
2162 
2163      /*
2164       * Escaped function testing TODO complete this list
2165       */
2166      private static final String[][] NUMERIC_FUNCTIONS = {
2167              // Section C.1 JDBC 3.0 spec.
2168              {"ABS", "-25.67"},
2169 
2170 //             {"ACOS", "0.0707"}, {"ASIN", "0.997"},
2171 //             {"ATAN", "14.10"}, {"ATAN2", "0.56", "1.2"}, {"CEILING", "3.45"},
2172 //             {"COS", "1.2"}, {"COT", "3.4"}, {"DEGREES", "2.1"}, {"EXP", "2.3"},
2173 //             {"FLOOR", "3.22"}, {"LOG", "34.1"}, {"LOG10", "18.7"},
2174 //             {"MOD", "124", "7"}, {"PI"}, {"POWER", "2", "3"},
2175 //             {"RADIANS", "54"}, {"RAND", "17"},
2176 
2177              {"ROUND", "345.345", "1"}
2178 
2179 //             {"SIGN", "-34"}, {"SIN", "0.32"}, {"SQRT", "6.22"},
2180 //             {"TAN", "0.57",}, {"TRUNCATE", "345.395", "1"}
2181 
2182              };
2183 
2184      private static final String[][] TIMEDATE_FUNCTIONS = {
2185              // Section C.3 JDBC 3.0 spec.
2186 
2187              {"date","'now'"}
2188 
2189              //TODO Complete list
2190 
2191      };
2192 
2193      private static final String[][] SYSTEM_FUNCTIONS = {
2194      // Section C.4 JDBC 3.0 spec.
2195              {"IFNULL", "'this'", "'that'"}, {"USER"}
2196              };
2197 
2198      /*
2199       * TODO complete or check this list
2200       */
2201      private static final String[][] STRING_FUNCTIONS = {
2202              // Section C.2 JDBC 3.0 spec.
2203 //             {"ASCII", "'Yellow'"}, {"CHAR", "65"},
2204 //             {"CONCAT", "'hello'", "'there'"},
2205 //             {"DIFFERENCE", "'Pires'", "'Piers'"},
2206 //             {"INSERT", "'Bill Clinton'", "4", "'William'"},
2207 //             {"LCASE", "'Fernando Alonso'"}, {"LEFT", "'Bonjour'", "3"},
2208 //             {"LENGTH", "'four    '"}, {"LOCATE", "'jour'", "'Bonjour'"},
2209              {"LTRIM", "'   left trim   '"},
2210 //               {"REPEAT", "'echo'", "3"},
2211 //             {"REPLACE", "'to be or not to be'", "'be'", "'England'"},
2212 //             {"RTRIM", "'  right trim   '"}, {"SOUNDEX", "'Derby'"},
2213 //             {"SPACE", "12"},
2214 //             {"SUBSTRING", "'Ruby the Rubicon Jeep'", "10", "7",},
2215 //             {"UCASE", "'Fernando Alonso'"}
2216      };
2217 
2218      /**
2219       * Six combinations of valid identifiers with mixed case, to see how the
2220       * various pattern matching and returned values handle them. This test only
2221       * creates objects in these schemas.
2222       */
2223      private static final String[] IDS = {
2224              "one_meta_test", "TWO_meta_test", "ThReE_meta_test",
2225              "\"four_meta_test\"", "\"FIVE_meta_test\"", "\"sIx_meta_test\""};
2226 
2227      /**
2228       * All the builtin schemas.
2229       */
2230      private static final String[] BUILTIN_SCHEMAS = {
2231              //TODO: Are there any other built in schemas?
2232 
2233      };
2234 
getStoredIdentifier(String sqlIdentifier)2235      public static String getStoredIdentifier(String sqlIdentifier) {
2236          if (sqlIdentifier.charAt(0) == '"')
2237              return sqlIdentifier.substring(1, sqlIdentifier.length() - 1);
2238          else
2239              return sqlIdentifier.toUpperCase();
2240      }
2241 
2242      /**
2243       * Test getSchemas() without modifying the database.
2244       *
2245       * @throws SQLException
2246       */
testGetSchemasReadOnly()2247      public void testGetSchemasReadOnly() throws SQLException {
2248 
2249          ResultSet rs = meta.getSchemas();
2250          checkSchemas(rs, new String[0]);
2251      }
2252 
2253 
2254      /**
2255       * Check the returned information from a getSchemas(). The passed in
2256       * String[] expected is a list of the schemas expected to be present in the
2257       * returned set. The returned set may contain additional schemas which will
2258       * be ignored, thus this test can be used regardless of the database state.
2259       * The builtin schemas are automatically checked and must not be part of the
2260       * passed in list.
2261       */
checkSchemas(ResultSet rs, String[] userExpected)2262      public static void checkSchemas(ResultSet rs, String[] userExpected)
2263              throws SQLException {
2264 
2265          // Add in the system schemas
2266          String[] expected = new String[BUILTIN_SCHEMAS.length
2267                  + userExpected.length];
2268 
2269          System.arraycopy(BUILTIN_SCHEMAS, 0, expected, 0,
2270                  BUILTIN_SCHEMAS.length);
2271          System.arraycopy(userExpected, 0, expected, BUILTIN_SCHEMAS.length,
2272                  userExpected.length);
2273 
2274          // Remove any quotes from user schemas and upper case
2275          // those without quotes.
2276          for (int i = BUILTIN_SCHEMAS.length; i < expected.length; i++) {
2277              expected[i] = getStoredIdentifier(expected[i]);
2278          }
2279 
2280          // output is ordered by TABLE_SCHEM
2281          Arrays.sort(expected);
2282 
2283          int nextMatch = 0;
2284 
2285          while (rs.next()) {
2286              String schema = rs.getString("TABLE_SCHEM");
2287              assertNotNull(schema);
2288 
2289              // Catalogs not supported
2290 //             assertNull(rs.getString("TABLE_CATALOG"));
2291 
2292              if (nextMatch < expected.length) {
2293                  if (expected[nextMatch].equals(schema)) nextMatch++;
2294              }
2295          }
2296          rs.close();
2297          assertEquals("Schemas missing ", expected.length, nextMatch);
2298      }
2299 
assertMatchesPattern(String pattern, String result)2300      private void assertMatchesPattern(String pattern, String result) {
2301          if (!doesMatch(pattern, 0, result, 0)) {
2302              fail("Bad pattern matching:" + pattern + " result:" + result);
2303          }
2304 
2305      }
2306 
2307      /**
2308       * See if a string matches the pattern as defined by DatabaseMetaData. By
2309       * passing in non-zero values can check sub-sets of the pattern against the
2310       * sub strings of the result. <BR>
2311       * _ matches a single character <BR>
2312       * % matches zero or more characters <BR>
2313       * Other characters match themselves.
2314       *
2315       * @param pattern
2316       *            Pattern
2317       * @param pp
2318       *            Position in pattern to start the actual pattern from
2319       * @param result
2320       *            result string
2321       * @param rp
2322       *            position in result to starting checking
2323       * @return true if a match is found
2324       */
doesMatch(String pattern, int pp, String result, int rp)2325      private boolean doesMatch(String pattern, int pp, String result, int rp) {
2326          // Find a match
2327          for (;;) {
2328              if (pp == pattern.length() && rp == result.length()) return true;
2329 
2330              // more characters to match in the result but
2331              // no more pattern.
2332              if (pp == pattern.length()) return false;
2333 
2334              char pc = pattern.charAt(pp);
2335              if (pc == '_') {
2336                  // need to match a single character but
2337                  // exhausted result, so no match.
2338                  if (rp == result.length()) return false;
2339 
2340                  pp++;
2341                  rp++;
2342              } else if (pc == '%') {
2343                  // % at end, complete match regardless of
2344                  // position of result since % matches zero or more.
2345                  if (pp == pattern.length() - 1) {
2346                      return true;
2347                  }
2348 
2349                  // Brut force, we have a pattern like %X
2350                  // and we are say in the third character of
2351                  // abCdefgX
2352                  // then start a 'CdefgX' and look for a match,
2353                  // then 'defgX' etc.
2354                  for (int sp = rp; sp < result.length(); sp++) {
2355                      if (doesMatch(pattern, pp + 1, result, sp)) {
2356                          // Have a match for the pattern after the %
2357                          // which means we have a match for the pattern
2358                          // with the % since we can match 0 or mor characters
2359                          // with %.
2360                          return true;
2361                      }
2362                  }
2363 
2364                  // Could not match the pattern after the %
2365                  return false;
2366              } else {
2367                  // need to match a single character but
2368                  // exhausted result, so no match.
2369                  if (rp == result.length()) return false;
2370 
2371                  // Single character, must match exactly.
2372                  if (pc != result.charAt(rp)) {
2373                      // Computer says no.
2374                      return false;
2375                  }
2376                  pp++;
2377                  rp++;
2378              }
2379 
2380          }
2381 
2382      }
2383 
2384 
2385      /**
2386       * Check that the list of escaped functions provided by the driver is a
2387       * strict subet of the specified set, the list does not contain duplicates,
2388       * all the functions listed can be executed and that if a function is not in
2389       * the list but is specified it cannot be executed.
2390       */
escapedFunctions(String[][] specList, String metaDataList)2391      private void escapedFunctions(String[][] specList, String metaDataList)
2392              throws SQLException {
2393 
2394          boolean[] seenFunction = new boolean[specList.length];
2395 
2396          StringTokenizer st = new StringTokenizer(metaDataList, ",");
2397          int counter = 0;
2398          while (st.hasMoreTokens()) {
2399              counter++;
2400              String function = st.nextToken();
2401 
2402              // find this function in the list
2403              boolean isSpecFunction = false;
2404              for (int f = 0; f < specList.length; f++) {
2405                  String[] specDetails = specList[f];
2406                  if (function.equals(specDetails[0])) {
2407                      // Matched spec.
2408                      if (seenFunction[f])
2409                          fail("Function in list twice: " + function);
2410                      seenFunction[f] = true;
2411                      isSpecFunction = true;
2412                      executeEscaped(specDetails);
2413                      break;
2414                  }
2415              }
2416 
2417              if (!isSpecFunction) {
2418                  fail("Non-JDBC spec function in list: " + function);
2419              }
2420          }
2421 
2422          // Now see if any speced functions are not in the metadata list
2423          assertSame("Function missing in metadata impl",specList.length, counter);
2424          for (int f = 0; f < specList.length; f++) {
2425              if (seenFunction[f]) continue;
2426              String[] specDetails = specList[f];
2427 
2428              // bug DERBY-723 CHAR maps to wrong function
2429              if ("CHAR".equals(specDetails[0])) continue;
2430              try {
2431                  executeEscaped(specDetails);
2432                  fail("function works but not declared in list: "
2433                          + specDetails[0]);
2434              } catch (SQLException e) {
2435                  //ok
2436              }
2437          }
2438      }
2439 
2440      /**
2441       * Test we can execute a function listed as a supported
2442       * JDBC escaped function. We don't care about the actual
2443       * return value, that should be tested elsewhere in
2444       * the specific test of a function.
2445       */
executeEscaped(String[] specDetails)2446      private void executeEscaped(String[] specDetails)
2447          throws SQLException
2448      {
2449 
2450          String sql = "SELECT " + specDetails[0] + "(";
2451 
2452          for (int p = 0; p < specDetails.length - 1; p++)
2453          {
2454              if (p != 0)
2455                  sql = sql + ", ";
2456 
2457              sql = sql + specDetails[p + 1];
2458          }
2459 
2460          sql = sql + ") ;";
2461 
2462          System.out.println("DatabaseMetaDataTest.executeEscaped() "+sql);
2463          Statement st = conn.createStatement();
2464          ResultSet rs = st.executeQuery(sql);
2465 
2466          assertNotNull("not supported function: "+sql,rs);
2467 
2468          rs.close();
2469          st.close();
2470      }
2471 
2472   //END APACHE-DERBY
2473 }
2474