001: /*
002: *
003: * Derby - Class org.apache.derbyTesting.functionTests.util.JDBC
004: *
005: * Licensed to the Apache Software Foundation (ASF) under one or more
006: * contributor license agreements. See the NOTICE file distributed with
007: * this work for additional information regarding copyright ownership.
008: * The ASF licenses this file to You under the Apache License, Version 2.0
009: * (the "License"); you may not use this file except in compliance with
010: * the License. You may obtain a copy of the License at
011: *
012: * http://www.apache.org/licenses/LICENSE-2.0
013: *
014: * Unless required by applicable law or agreed to in writing,
015: * software distributed under the License is distributed on an
016: * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND,
017: * either express or implied. See the License for the specific
018: * language governing permissions and limitations under the License.
019: */
020: package org.apache.derbyTesting.junit;
021:
022: import java.sql.*;
023:
024: import junit.framework.Assert;
025:
026: /**
027: * JDBC utility methods for the JUnit tests.
028: *
029: */
030: public class JDBC {
031:
032: /**
033: * Tell if we are allowed to use DriverManager to create database
034: * connections.
035: */
036: private static final boolean HAVE_DRIVER = haveClass("java.sql.Driver");
037:
038: /**
039: * Does the Savepoint class exist, indicates
040: * JDBC 3 (or JSR 169).
041: */
042: private static final boolean HAVE_SAVEPOINT = haveClass("java.sql.Savepoint");
043:
044: /**
045: * Does the java.sql.SQLXML class exist, indicates JDBC 4.
046: */
047: private static final boolean HAVE_SQLXML = haveClass("java.sql.SQLXML");
048:
049: /**
050: * Can we load a specific class, use this to determine JDBC level.
051: * @param className Class to attempt load on.
052: * @return true if class can be loaded, false otherwise.
053: */
054: private static boolean haveClass(String className) {
055: try {
056: Class.forName(className);
057: return true;
058: } catch (Exception e) {
059: return false;
060: }
061: }
062:
063: /**
064: * <p>
065: * Return true if the virtual machine environment
066: * supports JDBC4 or later.
067: * </p>
068: */
069: public static boolean vmSupportsJDBC4() {
070: return HAVE_DRIVER && HAVE_SQLXML;
071: }
072:
073: /**
074: * <p>
075: * Return true if the virtual machine environment
076: * supports JDBC3 or later.
077: * </p>
078: */
079: public static boolean vmSupportsJDBC3() {
080: return HAVE_DRIVER && HAVE_SAVEPOINT;
081: }
082:
083: /**
084: * <p>
085: * Return true if the virtual machine environment
086: * supports JDBC2 or later.
087: * </p>
088: */
089: public static boolean vmSupportsJDBC2() {
090: return HAVE_DRIVER;
091: }
092:
093: /**
094: * <p>
095: * Return true if the virtual machine environment
096: * supports JSR169 (JDBC 3 subset).
097: * </p>
098: */
099: public static boolean vmSupportsJSR169() {
100: return !HAVE_DRIVER && HAVE_SAVEPOINT;
101: }
102:
103: /**
104: * Rollback and close a connection for cleanup.
105: * Test code that is expecting Connection.close to succeed
106: * normally should just call conn.close().
107: *
108: * <P>
109: * If conn is not-null and isClosed() returns false
110: * then both rollback and close will be called.
111: * If both methods throw exceptions
112: * then they will be chained together and thrown.
113: * @throws SQLException Error closing connection.
114: */
115: public static void cleanup(Connection conn) throws SQLException {
116: if (conn == null)
117: return;
118: if (conn.isClosed())
119: return;
120:
121: SQLException sqle = null;
122: try {
123: conn.rollback();
124: } catch (SQLException e) {
125: sqle = e;
126: }
127:
128: try {
129: conn.close();
130: } catch (SQLException e) {
131: if (sqle == null)
132: sqle = e;
133: else
134: sqle.setNextException(e);
135: throw sqle;
136: }
137: }
138:
139: /**
140: * Drop a database schema by dropping all objects in it
141: * and then executing DROP SCHEMA. If the schema is
142: * APP it is cleaned but DROP SCHEMA is not executed.
143: *
144: * TODO: Handle dependencies by looping in some intelligent
145: * way until everything can be dropped.
146: *
147:
148: *
149: * @param dmd DatabaseMetaData object for database
150: * @param schema Name of the schema
151: * @throws SQLException database error
152: */
153: public static void dropSchema(DatabaseMetaData dmd, String schema)
154: throws SQLException {
155: Connection conn = dmd.getConnection();
156: Assert.assertFalse(conn.getAutoCommit());
157: Statement s = dmd.getConnection().createStatement();
158:
159: // Functions - not supported by JDBC meta data until JDBC 4
160: PreparedStatement psf = conn
161: .prepareStatement("SELECT ALIAS FROM SYS.SYSALIASES A, SYS.SYSSCHEMAS S"
162: + " WHERE A.SCHEMAID = S.SCHEMAID "
163: + " AND A.ALIASTYPE = 'F' "
164: + " AND S.SCHEMANAME = ?");
165: psf.setString(1, schema);
166: ResultSet rs = psf.executeQuery();
167: dropUsingDMD(s, rs, schema, "ALIAS", "FUNCTION");
168: psf.close();
169:
170: // Procedures
171: rs = dmd.getProcedures((String) null, schema, (String) null);
172:
173: dropUsingDMD(s, rs, schema, "PROCEDURE_NAME", "PROCEDURE");
174:
175: // Views
176: rs = dmd.getTables((String) null, schema, (String) null,
177: new String[] { "VIEW" });
178:
179: dropUsingDMD(s, rs, schema, "TABLE_NAME", "VIEW");
180:
181: // Tables
182: rs = dmd.getTables((String) null, schema, (String) null,
183: new String[] { "TABLE" });
184:
185: dropUsingDMD(s, rs, schema, "TABLE_NAME", "TABLE");
186:
187: // Synonyms - need work around for DERBY-1790 where
188: // passing a table type of SYNONYM fails.
189: rs = dmd.getTables((String) null, schema, (String) null,
190: new String[] { "AA_DERBY-1790-SYNONYM" });
191:
192: dropUsingDMD(s, rs, schema, "TABLE_NAME", "SYNONYM");
193:
194: // Finally drop the schema if it is not APP
195: if (!schema.equals("APP")) {
196: s.execute("DROP SCHEMA " + JDBC.escape(schema)
197: + " RESTRICT");
198: }
199: conn.commit();
200: s.close();
201: }
202:
203: /**
204: * DROP a set of objects based upon a ResultSet from a
205: * DatabaseMetaData call.
206: *
207: * TODO: Handle errors to ensure all objects are dropped,
208: * probably requires interaction with its caller.
209: *
210: * @param s Statement object used to execute the DROP commands.
211: * @param rs DatabaseMetaData ResultSet
212: * @param schema Schema the objects are contained in
213: * @param mdColumn The column name used to extract the object's
214: * name from rs
215: * @param dropType The keyword to use after DROP in the SQL statement
216: * @throws SQLException database errors.
217: */
218: private static void dropUsingDMD(Statement s, ResultSet rs,
219: String schema, String mdColumn, String dropType)
220: throws SQLException {
221: String dropLeadIn = "DROP " + dropType + " ";
222:
223: s.clearBatch();
224: int batchCount = 0;
225: while (rs.next()) {
226: String objectName = rs.getString(mdColumn);
227: s.addBatch(dropLeadIn + JDBC.escape(schema, objectName));
228: batchCount++;
229: }
230: rs.close();
231: int[] results;
232: try {
233: results = s.executeBatch();
234: Assert.assertNotNull(results);
235: Assert.assertEquals(
236: "Incorrect result length from executeBatch",
237: batchCount, results.length);
238: } catch (BatchUpdateException batchException) {
239: results = batchException.getUpdateCounts();
240: Assert.assertNotNull(results);
241: Assert.assertTrue(
242: "Too many results in BatchUpdateException",
243: results.length <= batchCount);
244: }
245:
246: boolean hadError = false;
247: boolean didDrop = false;
248: for (int i = 0; i < results.length; i++) {
249: int result = results[i];
250: if (result == -3 /* Statement.EXECUTE_FAILED*/)
251: hadError = true;
252: else if (result == -2/*Statement.SUCCESS_NO_INFO*/)
253: didDrop = true;
254: else if (result >= 0)
255: didDrop = true;
256: else
257: Assert.fail("Negative executeBatch status");
258: }
259:
260: // Commit any work we did do.
261: s.getConnection().commit();
262: s.clearBatch();
263: }
264:
265: /**
266: * Assert all columns in the ResultSetMetaData match the
267: * table's defintion through DatabaseMetadDta. Only works
268: * if the complete select list correspond to columns from
269: * base tables.
270: * <BR>
271: * Does not require that the complete set of any table's columns are
272: * returned.
273: * @throws SQLException
274: *
275: */
276: public static void assertMetaDataMatch(DatabaseMetaData dmd,
277: ResultSetMetaData rsmd) throws SQLException {
278: for (int col = 1; col <= rsmd.getColumnCount(); col++) {
279: // Only expect a single column back
280: ResultSet column = dmd.getColumns(rsmd.getCatalogName(col),
281: rsmd.getSchemaName(col), rsmd.getTableName(col),
282: rsmd.getColumnName(col));
283:
284: Assert.assertTrue("Column missing "
285: + rsmd.getColumnName(col), column.next());
286:
287: Assert.assertEquals(column.getInt("DATA_TYPE"), rsmd
288: .getColumnType(col));
289:
290: Assert.assertEquals(column.getInt("NULLABLE"), rsmd
291: .isNullable(col));
292:
293: Assert.assertEquals(column.getString("TYPE_NAME"), rsmd
294: .getColumnTypeName(col));
295:
296: column.close();
297: }
298: }
299:
300: /**
301: * Drain a single ResultSet by reading all of its
302: * rows and columns. Each column is accessed using
303: * getString() and asserted that the returned value
304: * matches the state of ResultSet.wasNull().
305: * Provides simple testing of the ResultSet when the contents
306: * are not important.
307: * @param rs
308: * @throws SQLException
309: */
310: public static void assertDrainResults(ResultSet rs)
311: throws SQLException {
312: ResultSetMetaData rsmd = rs.getMetaData();
313:
314: while (rs.next()) {
315: for (int col = 1; col <= rsmd.getColumnCount(); col++) {
316: String s = rs.getString(col);
317: Assert.assertEquals(s == null, rs.wasNull());
318: }
319: }
320: rs.close();
321: }
322:
323: /**
324: * Escape a non-qualified name so that it is suitable
325: * for use in a SQL query executed by JDBC.
326: */
327: public static String escape(String name) {
328: return "\"" + name + "\"";
329: }
330:
331: /**
332: * Escape a schama-qualified name so that it is suitable
333: * for use in a SQL query executed by JDBC.
334: */
335: public static String escape(String schema, String name) {
336: return "\"" + schema + "\".\"" + name + "\"";
337: }
338: }
|