1 /* Copyright (c) 2001-2011, The HSQL Development Group 2 * All rights reserved. 3 * 4 * Redistribution and use in source and binary forms, with or without 5 * modification, are permitted provided that the following conditions are met: 6 * 7 * Redistributions of source code must retain the above copyright notice, this 8 * list of conditions and the following disclaimer. 9 * 10 * Redistributions in binary form must reproduce the above copyright notice, 11 * this list of conditions and the following disclaimer in the documentation 12 * and/or other materials provided with the distribution. 13 * 14 * Neither the name of the HSQL Development Group nor the names of its 15 * contributors may be used to endorse or promote products derived from this 16 * software without specific prior written permission. 17 * 18 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" 19 * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE 20 * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE 21 * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG, 22 * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, 23 * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, 24 * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; 25 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND 26 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT 27 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS 28 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. 29 */ 30 31 32 package org.hsqldb.test; 33 34 import java.sql.Connection; 35 import java.sql.DatabaseMetaData; 36 import java.sql.PreparedStatement; 37 import java.sql.ResultSet; 38 import java.sql.ResultSetMetaData; 39 import java.sql.Types; 40 import java.util.ArrayList; 41 import java.util.Iterator; 42 import java.util.List; 43 44 import junit.framework.TestCase; 45 import junit.framework.TestResult; 46 47 public class TestDatabaseMetaData extends TestBase { 48 TestDatabaseMetaData(String name)49 public TestDatabaseMetaData(String name) { 50 super(name); 51 } 52 testOne()53 public void testOne() throws Exception { 54 55 Connection conn = newConnection(); 56 PreparedStatement pstmt; 57 int updateCount; 58 59 try { 60 pstmt = conn.prepareStatement("DROP TABLE t1 IF EXISTS"); 61 62 pstmt.executeUpdate(); 63 pstmt.close(); 64 65 pstmt = conn.prepareStatement( 66 "CREATE TABLE t1 (cha CHARACTER, dec DECIMAL, doub DOUBLE, lon BIGINT, \"IN\" INTEGER, sma SMALLINT, tin TINYINT, " 67 + "dat DATE DEFAULT CURRENT_DATE, tim TIME DEFAULT CURRENT_TIME, timest TIMESTAMP DEFAULT CURRENT_TIMESTAMP );"); 68 updateCount = pstmt.executeUpdate(); 69 70 assertTrue("expected update count of zero", updateCount == 0); 71 72 pstmt = conn.prepareStatement("CREATE INDEX t1 ON t1 (cha );"); 73 updateCount = pstmt.executeUpdate(); 74 pstmt = conn.prepareStatement("DROP TABLE t2 IF EXISTS"); 75 updateCount = pstmt.executeUpdate(); 76 pstmt = conn.prepareStatement( 77 "CREATE TABLE t2 (cha CHARACTER, dec DECIMAL, doub DOUBLE, lon BIGINT, \"IN\" INTEGER, sma SMALLINT, tin TINYINT, " 78 + "dat DATE DEFAULT CURRENT_DATE, tim TIME DEFAULT CURRENT_TIME, timest TIMESTAMP DEFAULT CURRENT_TIMESTAMP );"); 79 updateCount = pstmt.executeUpdate(); 80 pstmt = conn.prepareStatement("CREATE INDEX t2 ON t2 (cha );"); 81 updateCount = pstmt.executeUpdate(); 82 83 DatabaseMetaData dbmd = conn.getMetaData(); 84 ResultSet rsp = dbmd.getTablePrivileges(null, null, "T1"); 85 86 while (rsp.next()) { 87 System.out.println("Table: " + rsp.getString(3) + " priv: " 88 + rsp.getString(6)); 89 } 90 91 rsp = dbmd.getIndexInfo(null, null, "T1", false, false); 92 93 while (rsp.next()) { 94 System.out.println("Table: " + rsp.getString(3) 95 + " IndexName: " + rsp.getString(6)); 96 } 97 98 rsp = dbmd.getIndexInfo(null, null, "T2", false, false); 99 100 while (rsp.next()) { 101 System.out.println("Table: " + rsp.getString(3) 102 + " IndexName: " + rsp.getString(6)); 103 } 104 105 pstmt = conn.prepareStatement("DROP INDEX t2;"); 106 updateCount = pstmt.executeUpdate(); 107 rsp = dbmd.getIndexInfo(null, null, "T2", false, false); 108 109 assertTrue("expected getIndexInfo returns empty resultset", 110 rsp.next() == false); 111 112 ResultSet rs = dbmd.getTables(null, null, "T1", 113 new String[]{ "TABLE" }); 114 ArrayList tablesarr = new ArrayList(); 115 int i; 116 117 for (i = 0; rs.next(); i++) { 118 String tempstr = 119 rs.getString("TABLE_NAME").trim().toLowerCase(); 120 121 tablesarr.add(tempstr); 122 } 123 124 rs.close(); 125 assertTrue("expected table t1 count of 1", i == 1); 126 127 Iterator it = tablesarr.iterator(); 128 129 for (; it.hasNext(); ) { 130 131 // create new ArrayList and HashMap for the table 132 String tablename = ((String) it.next()).trim(); 133 List collist = new ArrayList(30); 134 135 rs = dbmd.getColumns(null, null, tablename.toUpperCase(), 136 null); 137 138 for (i = 0; rs.next(); i++) { 139 collist.add( 140 rs.getString("COLUMN_NAME").trim().toLowerCase()); 141 } 142 143 rs.close(); 144 } 145 146 pstmt = conn.prepareStatement("DROP TABLE t_1 IF EXISTS"); 147 148 pstmt.executeUpdate(); 149 pstmt.close(); 150 151 pstmt = conn.prepareStatement( 152 "CREATE TABLE t_1 (cha CHARACTER(10), deci DECIMAL(10,2), doub DOUBLE, lon BIGINT, \"IN\" INTEGER, sma SMALLINT, tin TINYINT, " 153 + "dat DATE DEFAULT CURRENT_DATE, tim TIME DEFAULT CURRENT_TIME, timest TIMESTAMP DEFAULT CURRENT_TIMESTAMP, bool BOOLEAN );"); 154 updateCount = pstmt.executeUpdate(); 155 156 assertTrue("expected update count of zero", updateCount == 0); 157 158 rs = dbmd.getTables(null, null, "T\\_1", new String[]{ "TABLE" }); 159 160 for (i = 0; rs.next(); i++) { 161 String tempstr = 162 rs.getString("TABLE_NAME").trim().toLowerCase(); 163 164 tablesarr.add(tempstr); 165 } 166 167 rs.close(); 168 assertTrue("expected table t_1 count of 1", i == 1); 169 170 // test various methods 171 dbmd.getPrimaryKeys(null, null, "T_1"); 172 dbmd.getImportedKeys(null, null, "T_1"); 173 dbmd.getCrossReference(null, null, "T_1", null, null, "T_1"); 174 175 // test ResultSetMetaData 176 pstmt = conn.prepareStatement( 177 "INSERT INTO T_1 (cha, deci, doub) VALUES ('name', 10.23, 0)"); 178 179 pstmt.executeUpdate(); 180 pstmt.close(); 181 182 pstmt = conn.prepareStatement("SELECT * FROM T_1"); 183 rs = pstmt.executeQuery(); 184 185 ResultSetMetaData md = rs.getMetaData(); 186 int x = md.getColumnDisplaySize(1); 187 int y = md.getColumnDisplaySize(2); 188 int b = md.getPrecision(2); 189 int c = md.getScale(1); 190 int d = md.getScale(2); 191 String e = md.getColumnClassName(10); 192 boolean testresult = (x == 10) && (y == 12) && (b == 10) 193 && (c == 0) && (d == 2) 194 && e.equals("java.sql.Timestamp"); 195 196 assertTrue("wrong result metadata", testresult); 197 198 e = md.getColumnClassName(11); 199 testresult = e.equals("java.lang.Boolean"); 200 201 assertTrue("wrong result metadata", testresult); 202 pstmt.close(); 203 204 // 205 } catch (Exception e) { 206 assertTrue("unable to prepare or execute DDL", false); 207 } finally { 208 conn.close(); 209 } 210 } 211 212 /** 213 * Basic test of DatabaseMetaData functions that access system tables 214 */ testTwo()215 public void testTwo() throws Exception { 216 217 Connection conn = newConnection(); 218 int updateCount; 219 220 try { 221 TestUtil.testScript(conn, "testrun/hsqldb/TestSelf.txt"); 222 223 DatabaseMetaData dbmeta = conn.getMetaData(); 224 225 dbmeta.allProceduresAreCallable(); 226 dbmeta.getBestRowIdentifier(null, null, "T_1", 227 DatabaseMetaData.bestRowTransaction, 228 true); 229 dbmeta.getCatalogs(); 230 dbmeta.getColumnPrivileges(null, "PUBLIC", "T_1", "%"); 231 dbmeta.getColumns("PUBLIC", "PUBLIC", "T_1", "%"); 232 dbmeta.getCrossReference(null, null, "T_1", null, null, "T_1"); 233 dbmeta.getExportedKeys(null, null, "T_1"); 234 dbmeta.getFunctionColumns(null, "%", "%", "%"); 235 dbmeta.getFunctions(null, "%", "%"); 236 dbmeta.getImportedKeys("PUBLIC", "PUBLIC", "T_1"); 237 dbmeta.getIndexInfo("PUBLIC", "PUBLIC", "T1", true, true); 238 dbmeta.getPrimaryKeys("PUBLIC", "PUBLIC", "T_1"); 239 dbmeta.getProcedureColumns(null, null, "%", "%"); 240 dbmeta.getProcedures("PUBLIC", "%", "%"); 241 dbmeta.getSchemas(null, "#"); 242 dbmeta.getTablePrivileges(null, "%", "%"); 243 dbmeta.getUDTs(null, "%", "%", new int[]{ Types.DISTINCT }); 244 } catch (Exception e) { 245 assertTrue("unable to prepare or execute DDL", false); 246 } finally { 247 conn.close(); 248 } 249 } 250 251 /** 252 * Basic test of DatabaseMetaData functions that access functions 253 */ testThree()254 public void testThree() throws Exception { 255 256 Connection conn = newConnection(); 257 int updateCount; 258 259 try { 260 TestUtil.testScript(conn, "testrun/hsqldb/TestSelf.txt"); 261 262 DatabaseMetaData dbmeta = conn.getMetaData(); 263 int txIsolation = dbmeta.getDefaultTransactionIsolation(); 264 String userName = dbmeta.getUserName(); 265 boolean isReadOnly = dbmeta.isReadOnly(); 266 } catch (Exception e) { 267 assertTrue("unable to prepare or execute DDL", false); 268 } finally { 269 conn.close(); 270 } 271 } 272 main(String[] args)273 public static void main(String[] args) throws Exception { 274 275 TestResult result; 276 TestCase test; 277 java.util.Enumeration failures; 278 int count; 279 280 result = new TestResult(); 281 test = new TestDatabaseMetaData("test"); 282 283 test.run(result); 284 285 count = result.failureCount(); 286 287 System.out.println("TestDatabaseMetaData failure count: " + count); 288 289 failures = result.failures(); 290 291 while (failures.hasMoreElements()) { 292 System.out.println(failures.nextElement()); 293 } 294 } 295 } 296