KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > smallsql > junit > TestDBMetaData


1 /* =============================================================
2  * SmallSQL : a free Java DBMS library for the Java(tm) platform
3  * =============================================================
4  *
5  * (C) Copyright 2004-2006, by Volker Berlin.
6  *
7  * Project Info: http://www.smallsql.de/
8  *
9  * This library is free software; you can redistribute it and/or modify it
10  * under the terms of the GNU Lesser General Public License as published by
11  * the Free Software Foundation; either version 2.1 of the License, or
12  * (at your option) any later version.
13  *
14  * This library is distributed in the hope that it will be useful, but
15  * WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY
16  * or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public
17  * License for more details.
18  *
19  * You should have received a copy of the GNU Lesser General Public
20  * License along with this library; if not, write to the Free Software
21  * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301,
22  * USA.
23  *
24  * [Java is a trademark or registered trademark of Sun Microsystems, Inc.
25  * in the United States and other countries.]
26  *
27  * ---------------
28  * TestDBMetaData.java
29  * ---------------
30  * Author: Volker Berlin
31  *
32  */

33 package smallsql.junit;
34
35 import java.sql.*;
36 import java.text.*;
37 import java.util.Locale JavaDoc;
38 /**
39  * @author Volker Berlin
40  *
41  */

42 public class TestDBMetaData extends BasicTestCase {
43
44     public TestDBMetaData(){
45         super();
46     }
47     
48     
49     public TestDBMetaData(String JavaDoc arg0) {
50         super(arg0);
51     }
52
53     
54     public void testGetURL() throws Exception JavaDoc{
55         Connection con = AllTests.getConnection();
56         DatabaseMetaData md = con.getMetaData();
57         assertEquals( "URL", AllTests.jdbCURL, md.getURL());
58     }
59     
60     
61     public void testVersions() throws Exception JavaDoc{
62         Connection con = AllTests.getConnection();
63         DatabaseMetaData md = con.getMetaData();
64         assertEquals( "DriverVersion", md.getDriverVersion(), md.getDatabaseProductVersion());
65         Driver driver = DriverManager.getDriver(AllTests.jdbCURL);
66         assertEquals( "MajorVersion", driver.getMajorVersion(), md.getDatabaseMajorVersion());
67         assertEquals( "MajorVersion", driver.getMajorVersion(), md.getDriverMajorVersion());
68         assertEquals( "MinorVersion", driver.getMinorVersion(), md.getDatabaseMinorVersion());
69         assertEquals( "MinorVersion", driver.getMinorVersion(), md.getDriverMinorVersion());
70         assertEquals( "Version", new DecimalFormat("###0.00", new DecimalFormatSymbols(Locale.US)).format(driver.getMajorVersion()+driver.getMinorVersion()/100.0), md.getDriverVersion());
71     }
72     
73     
74     public void testFunctions() throws Exception JavaDoc{
75         Connection con = AllTests.getConnection();
76         DatabaseMetaData md = con.getMetaData();
77         assertEquals( "getNumericFunctions", "ABS,ACOS,ASIN,ATAN,ATAN2,CEILING,COS,COT,DEGREES,EXP,FLOOR,LOG,LOG10,MOD,PI,POWER,RADIANS,RAND,ROUND,SIGN,SIN,SQRT,TAN,TRUNCATE",
78                 md.getNumericFunctions());
79         assertEquals( "getStringFunctions", "ASCII,CHAR,CONCAT,DIFFERENCE,INSERT,LCASE,LEFT,LENGTH,LOCATE,LTRIM,REPEAT,REPLACE,RIGHT,RTRIM,SOUNDEX,SPACE,SUBSTRING,TRIM,UCASE",
80                 md.getStringFunctions());
81         assertEquals( "getStringFunctions", "IFNULL,USER,CONVERT,CAST,IIF",
82                 md.getSystemFunctions());
83         assertEquals( "getStringFunctions", "CURDATE,CURTIME,DAYNAME,DAYOFMONTH,DAYOFWEEK,DAYOFYEAR,DAY,HOUR,MILLISECOND,MINUTE,MONTH,MONTHNAME,NOW,QUARTER,SECOND,TIMESTAMPADD,TIMESTAMPDIFF,WEEK,YEAR",
84                 md.getTimeDateFunctions());
85     }
86     
87     
88     public void testGetProcedures() throws Exception JavaDoc{
89         Connection con = AllTests.getConnection();
90         DatabaseMetaData md = con.getMetaData();
91         ResultSet rs = md.getProcedures( null, null, "*");
92         String JavaDoc[] colNames = {"PROCEDURE_CAT", "PROCEDURE_SCHEM", "PROCEDURE_NAME", "", "", "", "REMARKS", "PROCEDURE_TYPE"};
93         int[] colTypes = {Types.NULL, Types.NULL, Types.NULL, Types.NULL, Types.NULL, Types.NULL, Types.NULL, Types.NULL };
94         assertRSMetaData( rs, colNames, colTypes);
95     }
96     
97     
98     public void testGetProcedureColumns() throws Exception JavaDoc{
99         Connection con = AllTests.getConnection();
100         DatabaseMetaData md = con.getMetaData();
101         ResultSet rs = md.getProcedureColumns( null, null, "*", null);
102         String JavaDoc[] colNames = {"PROCEDURE_CAT", "PROCEDURE_SCHEM", "PROCEDURE_NAME", "COLUMN_NAME", "COLUMN_TYPE", "DATA_TYPE", "TYPE_NAME", "PRECISION", "LENGTH", "SCALE", "RADIX", "NULLABLE", "REMARKS" };
103         int[] colTypes = {Types.NULL, Types.NULL, Types.NULL, Types.NULL, Types.NULL, Types.NULL, Types.NULL, Types.NULL, Types.NULL, Types.NULL, Types.NULL, Types.NULL, Types.NULL };
104         assertRSMetaData( rs, colNames, colTypes);
105     }
106     
107     
108     public void testGetTables() throws Exception JavaDoc{
109         String JavaDoc[] colNames = {"TABLE_CAT","TABLE_SCHEM","TABLE_NAME","TABLE_TYPE","REMARKS","TYPE_CAT","TYPE_SCHEM","TYPE_NAME","SELF_REFERENCING_COL_NAME","REF_GENERATION"};
110         int[] types = {Types.VARCHAR, Types.NULL, Types.VARCHAR, Types.VARCHAR, Types.NULL, Types.NULL, Types.NULL, Types.NULL, Types.NULL, Types.NULL};
111         
112         //First test the function without a database connection
113
Connection con = DriverManager.getConnection("jdbc:smallsql");
114         DatabaseMetaData md = con.getMetaData();
115         ResultSet rs = md.getTables(null, null, null, null);
116         super.assertRSMetaData(rs, colNames, new int[colNames.length]); //All types are NULL, because no row.
117
assertFalse(rs.next());
118         con.close();
119         
120         //Then test it with a database
121
con = AllTests.getConnection();
122         md = con.getMetaData();
123         rs = md.getTables(null, null, null, null);
124         super.assertRSMetaData(rs, colNames, types);
125     }
126     
127     
128     public void testGetSchemas() throws Exception JavaDoc{
129         Connection con = AllTests.getConnection();
130         DatabaseMetaData md = con.getMetaData();
131         ResultSet rs = md.getSchemas();
132         String JavaDoc[] colNames = {"TABLE_SCHEM"};
133         int[] colTypes = {Types.NULL};
134         assertRSMetaData( rs, colNames, colTypes);
135         assertFalse(rs.next());
136     }
137     
138     
139     public void testGetCatalogs() throws Exception JavaDoc{
140         Connection con = AllTests.getConnection();
141         try{
142             con.createStatement().execute("drop database test2\n\r\t");
143         }catch(SQLException e){/* ignore it if the database already exists */}
144         con.createStatement().execute("create database test2");
145         DatabaseMetaData md = con.getMetaData();
146         ResultSet rs = md.getCatalogs();
147         assertRSMetaData( rs, new String JavaDoc[]{"TABLE_CAT"}, new int[]{Types.VARCHAR});
148         while(rs.next()){
149             System.out.println( "testCatalogs:"+rs.getObject(1) );
150         }
151     }
152     
153     
154     public void testGetTableTypes() throws Exception JavaDoc{
155         Connection con = AllTests.getConnection();
156         DatabaseMetaData md = con.getMetaData();
157         ResultSet rs = md.getTableTypes();
158         String JavaDoc[] colNames = {"TABLE_TYPE"};
159         int[] colTypes = {Types.VARCHAR};
160         assertRSMetaData( rs, colNames, colTypes);
161         String JavaDoc type = "";
162         int count = 0;
163         while(rs.next()){
164             String JavaDoc type2 = rs.getString("TABLE_TYPE");
165             assertTrue( type+"-"+type2, type.compareTo(type2)<0);
166             type = type2;
167             count++;
168         }
169         assertEquals("Table Type Count", 3, count);
170     }
171     
172     
173     public void testGetColumn() throws Exception JavaDoc{
174         Connection con = AllTests.getConnection();
175         dropTable(con,"tableColumns");
176         dropView( con, "viewColumns");
177         con.createStatement().execute("create table tableColumns(a int default 5)");
178         DatabaseMetaData md = con.getMetaData();
179         
180         ResultSet rs = md.getColumns(null, null, "tableColumns", null);
181         String JavaDoc[] colNames = {"TABLE_CAT", "TABLE_SCHEM", "TABLE_NAME", "COLUMN_NAME", "DATA_TYPE", "TYPE_NAME", "COLUMN_SIZE", "BUFFER_LENGTH", "DECIMAL_DIGITS", "NUM_PREC_RADIX", "NULLABLE", "REMARKS", "COLUMN_DEF", "SQL_DATA_TYPE", "SQL_DATETIME_SUB", "CHAR_OCTET_LENGTH", "ORDINAL_POSITION", "IS_NULLABLE"};
182         int[] colTypes = {Types.VARCHAR, Types.NULL, Types.VARCHAR, Types.VARCHAR, Types.SMALLINT, Types.VARCHAR, Types.INTEGER, Types.NULL, Types.INTEGER, Types.INTEGER, Types.INTEGER, Types.NULL, Types.VARCHAR, Types.NULL, Types.NULL, Types.INTEGER, Types.INTEGER, Types.VARCHAR};
183         assertRSMetaData( rs, colNames, colTypes);
184         assertTrue( "No row", rs.next() );
185         assertEquals( "a", rs.getObject("COLUMN_NAME") );
186         assertEquals( "INT", rs.getObject("TYPE_NAME") );
187         assertEquals( "5", rs.getObject("COLUMN_Def") );
188         
189         con.createStatement().execute("create view viewColumns as Select * from tableColumns");
190         
191         rs = md.getColumns(null, null, "viewColumns", null);
192         assertRSMetaData( rs, colNames, colTypes);
193         assertTrue( "No row", rs.next() );
194         assertEquals( "a", rs.getObject("COLUMN_NAME") );
195         assertEquals( "INT", rs.getObject("TYPE_NAME") );
196         assertEquals( "5", rs.getObject("COLUMN_Def") );
197
198         dropView( con, "viewColumns");
199         dropTable( con, "tableColumns");
200     }
201     
202     
203     public void testGetTypeInfo() throws Exception JavaDoc{
204         Connection con = AllTests.getConnection();
205         DatabaseMetaData md = con.getMetaData();
206         
207         ResultSet rs = md.getTypeInfo();
208         
209         String JavaDoc[] colNames = {"TYPE_NAME", "DATA_TYPE", "PRECISION", "LITERAL_PREFIX", "LITERAL_SUFFIX", "CREATE_PARAMS", "NULLABLE", "CASE_SENSITIVE", "SEARCHABLE", "UNSIGNED_ATTRIBUTE", "FIXED_PREC_SCALE", "AUTO_INCREMENT", "LOCAL_TYPE_NAME", "MINIMUM_SCALE", "MAXIMUM_SCALE", "SQL_DATA_TYPE", "SQL_DATETIME_SUB", "NUM_PREC_RADIX"};
210         int[] colTypes = {Types.VARCHAR, Types.SMALLINT, Types.INTEGER, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.SMALLINT, Types.BOOLEAN, Types.SMALLINT, Types.BOOLEAN, Types.BOOLEAN, Types.BOOLEAN, Types.NULL, Types.INTEGER, Types.INTEGER, Types.NULL, Types.NULL, Types.NULL };
211         assertRSMetaData(rs, colNames, colTypes);
212         
213         assertTrue(rs.next());
214         int lastDataType = rs.getInt("data_type");
215         while(rs.next()){
216             int dataType = rs.getInt("data_type");
217             assertTrue("Wrong sorting order", dataType>=lastDataType );
218             lastDataType = dataType;
219         }
220     }
221     
222     
223     public void testGetCrossReference() throws Exception JavaDoc{
224         Connection con = AllTests.getConnection();
225         dropTable(con,"tblCross1");
226         dropTable(con,"tblCross2");
227         DatabaseMetaData md = con.getMetaData();
228         
229         Statement st = con.createStatement();
230         st.execute("Create Table tblCross1(id1 counter primary key, v nvarchar(100))");
231         //st.execute("Create Table tblCross2(id2 counter foreign key REFERENCES tblCross1(id1), v nvarchar(100))");
232
st.execute("Create Table tblCross2(id2 int , v nvarchar(100), foreign key (id2) REFERENCES tblCross1(id1))");
233         String JavaDoc[] colNames = {"PKTABLE_CAT", "PKTABLE_SCHEM", "PKTABLE_NAME", "PKCOLUMN_NAME", "FKTABLE_CAT", "FKTABLE_SCHEM", "FKTABLE_NAME", "FKCOLUMN_NAME", "KEY_SEQ", "UPDATE_RULE", "DELETE_RULE", "FK_NAME", "PK_NAME", "DEFERRABILITY"};
234         int[] colTypes = {Types.VARCHAR, Types.NULL, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.NULL, Types.VARCHAR, Types.VARCHAR, Types.SMALLINT, Types.SMALLINT, Types.SMALLINT, Types.VARCHAR, Types.VARCHAR, Types.SMALLINT };
235         
236         ResultSet rs = md.getCrossReference(null,null,"tblCross1",null,null,"tblCross2");
237         assertRSMetaData(rs, colNames, colTypes);
238         assertTrue(rs.next());
239         assertFalse(rs.next());
240         
241         rs = md.getImportedKeys(null,null,"tblCross2");
242         assertRSMetaData(rs, colNames, colTypes);
243         assertTrue(rs.next());
244         assertFalse(rs.next());
245         
246         rs = md.getExportedKeys(null,null,"tblCross1");
247         assertRSMetaData(rs, colNames, colTypes);
248         assertTrue(rs.next());
249         assertFalse(rs.next());
250         
251         dropTable(con,"tblCross1");
252         dropTable(con,"tblCross2");
253     }
254     
255     
256     public void testGetBestRowIdentifier() throws Exception JavaDoc{
257         Connection con = AllTests.getConnection();
258         dropTable(con,"tblBestRow1");
259         DatabaseMetaData md = con.getMetaData();
260         Statement st = con.createStatement();
261         st.execute("Create Table tblBestRow1(id1 counter primary key, v nvarchar(100))");
262         String JavaDoc[] colNames = {"SCOPE", "COLUMN_NAME", "DATA_TYPE", "TYPE_NAME", "COLUMN_SIZE", "BUFFER_LENGTH", "DECIMAL_DIGITS", "PSEUDO_COLUMN"};
263         int[] colTypes = {Types.SMALLINT, Types.VARCHAR, Types.INTEGER, Types.VARCHAR, Types.INTEGER, Types.NULL, Types.SMALLINT, Types.SMALLINT};
264         
265         ResultSet rs = md.getBestRowIdentifier(null, null, "tblBestRow1", DatabaseMetaData.bestRowSession, true);
266         assertRSMetaData(rs, colNames, colTypes);
267         assertTrue(rs.next());
268         assertEquals("Columnname:", "id1", rs.getString("COLUMN_NAME"));
269         assertFalse(rs.next());
270         
271         String JavaDoc[] colNames2 = {"TABLE_CAT", "TABLE_SCHEM", "TABLE_NAME", "COLUMN_NAME", "KEY_SEQ", "PK_NAME"};
272         int[] colTypes2 = {Types.VARCHAR, Types.NULL, Types.VARCHAR, Types.VARCHAR, Types.SMALLINT, Types.VARCHAR};
273         rs = md.getPrimaryKeys(null, null, "tblBestRow1");
274         assertRSMetaData(rs, colNames2, colTypes2);
275         assertTrue(rs.next());
276         assertEquals("Columnname:", "id1", rs.getString("COLUMN_NAME"));
277         assertFalse(rs.next());
278         
279         String JavaDoc[] colNames3 = {"TABLE_CAT", "TABLE_SCHEM", "TABLE_NAME", "NON_UNIQUE", "INDEX_QUALIFIER", "INDEX_NAME", "TYPE", "ORDINAL_POSITION", "COLUMN_NAME", "ASC_OR_DESC", "CARDINALITY", "PAGES", "FILTER_CONDITION"};
280         int[] colTypes3 = {Types.VARCHAR, Types.NULL, Types.VARCHAR, Types.BOOLEAN, Types.NULL, Types.VARCHAR, Types.SMALLINT, Types.SMALLINT, Types.VARCHAR, Types.NULL, Types.NULL, Types.NULL, Types.NULL};
281         rs = md.getIndexInfo(null, null, "tblBestRow1", true, true);
282         assertRSMetaData(rs, colNames3, colTypes3);
283         assertTrue(rs.next());
284         assertEquals("Columnname:", "id1", rs.getString("COLUMN_NAME"));
285         assertFalse(rs.next());
286         
287         dropTable(con,"tblBestRow1");
288     }
289     
290     
291     public void testGetgetUDTs() throws Exception JavaDoc{
292         Connection con = AllTests.getConnection();
293         DatabaseMetaData md = con.getMetaData();
294         ResultSet rs = md.getUDTs(null, null, null, null);
295         String JavaDoc[] colNames = {"TYPE_CAT", "TYPE_SCHEM", "TYPE_NAME", "CLASS_NAME", "DATA_TYPE", "REMARKS"};
296         int[] colTypes = new int[colNames.length];
297         assertRSMetaData( rs, colNames, colTypes);
298         assertFalse(rs.next());
299     }
300     
301     
302     public void testGetConnection() throws Exception JavaDoc{
303         Connection con = AllTests.getConnection();
304         DatabaseMetaData md = con.getMetaData();
305         assertEquals(con, md.getConnection());
306     }
307
308     
309 }
310
Popular Tags