1 33 package smallsql.junit; 34 35 import java.sql.*; 36 import java.text.*; 37 import java.util.Locale ; 38 42 public class TestDBMetaData extends BasicTestCase { 43 44 public TestDBMetaData(){ 45 super(); 46 } 47 48 49 public TestDBMetaData(String arg0) { 50 super(arg0); 51 } 52 53 54 public void testGetURL() throws Exception { 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 { 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 { 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 { 89 Connection con = AllTests.getConnection(); 90 DatabaseMetaData md = con.getMetaData(); 91 ResultSet rs = md.getProcedures( null, null, "*"); 92 String [] 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 { 99 Connection con = AllTests.getConnection(); 100 DatabaseMetaData md = con.getMetaData(); 101 ResultSet rs = md.getProcedureColumns( null, null, "*", null); 102 String [] 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 { 109 String [] 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 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]); assertFalse(rs.next()); 118 con.close(); 119 120 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 { 129 Connection con = AllTests.getConnection(); 130 DatabaseMetaData md = con.getMetaData(); 131 ResultSet rs = md.getSchemas(); 132 String [] 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 { 140 Connection con = AllTests.getConnection(); 141 try{ 142 con.createStatement().execute("drop database test2\n\r\t"); 143 }catch(SQLException e){} 144 con.createStatement().execute("create database test2"); 145 DatabaseMetaData md = con.getMetaData(); 146 ResultSet rs = md.getCatalogs(); 147 assertRSMetaData( rs, new String []{"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 { 155 Connection con = AllTests.getConnection(); 156 DatabaseMetaData md = con.getMetaData(); 157 ResultSet rs = md.getTableTypes(); 158 String [] colNames = {"TABLE_TYPE"}; 159 int[] colTypes = {Types.VARCHAR}; 160 assertRSMetaData( rs, colNames, colTypes); 161 String type = ""; 162 int count = 0; 163 while(rs.next()){ 164 String 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 { 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 [] 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 { 204 Connection con = AllTests.getConnection(); 205 DatabaseMetaData md = con.getMetaData(); 206 207 ResultSet rs = md.getTypeInfo(); 208 209 String [] 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 { 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 int , v nvarchar(100), foreign key (id2) REFERENCES tblCross1(id1))"); 233 String [] 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 { 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 [] 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 [] 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 [] 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 { 292 Connection con = AllTests.getConnection(); 293 DatabaseMetaData md = con.getMetaData(); 294 ResultSet rs = md.getUDTs(null, null, null, null); 295 String [] 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 { 303 Connection con = AllTests.getConnection(); 304 DatabaseMetaData md = con.getMetaData(); 305 assertEquals(con, md.getConnection()); 306 } 307 308 309 } 310 | Popular Tags |