DatabaseMetaDataTest.java revision 49965c1dc9da104344f4893a05e45795a5740d20 
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 */
16package tests.java.sql;
17
18import dalvik.annotation.KnownFailure;
19
20import junit.extensions.TestSetup;
21import junit.framework.Test;
22import junit.framework.TestCase;
23import junit.framework.TestSuite;
24
25import tests.support.DatabaseCreator;
26import tests.support.Support_SQL;
27
28import java.sql.Connection;
29import java.sql.DatabaseMetaData;
30import java.sql.DriverManager;
31import java.sql.PreparedStatement;
32import java.sql.ResultSet;
33import java.sql.ResultSetMetaData;
34import java.sql.SQLException;
35import java.sql.Statement;
36import java.sql.Types;
37import java.util.ArrayList;
38import java.util.Arrays;
39import java.util.List;
40import java.util.Random;
41import java.util.StringTokenizer;
42
43public 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
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
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
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
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     */
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 %")
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: '', '', '%', '%' ")
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.")
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     */
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")
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")
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")
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")
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")
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")
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     */
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     */
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")
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")
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")
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     */
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")
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")
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")
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")
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")
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")
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")
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")
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")
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")
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")
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")
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")
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")
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")
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")
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")
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")
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")
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")
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")
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")
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     */
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")
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")
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")
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")
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")
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")
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")
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")
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")
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     */
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     */
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
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     */
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     */
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     */
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
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     */
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     */
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     */
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
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      */
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      */
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
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      */
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      */
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      */
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