001: /* Copyright 2004 The JA-SIG Collaborative. All rights reserved.
002: * See license distributed with this file and
003: * available online at http://www.uportal.org/license.html
004: */
005:
006: package org.jasig.portal.rdbm;
007:
008: import java.sql.Connection;
009: import java.sql.DatabaseMetaData;
010: import java.sql.PreparedStatement;
011: import java.sql.ResultSet;
012: import java.sql.SQLException;
013: import java.sql.Statement;
014: import java.sql.Timestamp;
015: import java.text.SimpleDateFormat;
016: import java.util.Date;
017:
018: import javax.sql.DataSource;
019:
020: import org.apache.commons.logging.Log;
021: import org.apache.commons.logging.LogFactory;
022: import org.jasig.portal.RDBMServices;
023: import org.springframework.dao.DataAccessResourceFailureException;
024:
025: /**
026: * @author Eric Dalquist <a href="mailto:edalquist@unicon.net">edalquist@unicon.net</a>
027: * @version $Revision: 36683 $ $Date: 2006-08-23 15:08:00 -0700 (Wed, 23 Aug 2006) $
028: */
029: public class DatabaseMetaDataImpl implements IDatabaseMetadata {
030: public static final class PostgreSQLDb extends JoinQueryString {
031: public PostgreSQLDb(final String testString) {
032: super (testString);
033: }
034: }
035:
036: public static final class OracleDb extends JoinQueryString {
037: public OracleDb(final String testString) {
038: super (testString);
039: }
040: }
041:
042: public static final class JdbcDb extends JoinQueryString {
043: public JdbcDb(final String testString) {
044: super (testString);
045: }
046: }
047:
048: private static final Log LOG = LogFactory
049: .getLog(DatabaseMetaDataImpl.class);
050:
051: /** Define the oracle TO_DATE format */
052: private static final SimpleDateFormat TO_DATE_FORMAT = new SimpleDateFormat(
053: "yyyy MM dd HH:mm:ss");
054:
055: //Define the different join queries we know about with the
056: //appropriately typed JoinQueryString implementation.
057: private static final JoinQueryString jdbcDb = new DatabaseMetaDataImpl.JdbcDb(
058: "{oj UP_USER LEFT OUTER JOIN UP_USER_LAYOUT ON UP_USER.USER_ID = UP_USER_LAYOUT.USER_ID} WHERE");
059: private static final JoinQueryString postgreSQLDb = new DatabaseMetaDataImpl.PostgreSQLDb(
060: "UP_USER LEFT OUTER JOIN UP_USER_LAYOUT ON UP_USER.USER_ID = UP_USER_LAYOUT.USER_ID WHERE");
061: private static final JoinQueryString oracleDb = new DatabaseMetaDataImpl.OracleDb(
062: "UP_USER, UP_USER_LAYOUT WHERE UP_USER.USER_ID = UP_USER_LAYOUT.USER_ID(+) AND");
063:
064: /** Array of join tests to perform. */
065: private static final JoinQueryString[] joinTests = { oracleDb,
066: postgreSQLDb, jdbcDb };
067:
068: /** The {@link DataSource} that represents the server */
069: final private DataSource dataSource;
070:
071: /** The {@link IJoinQueryString} to use for performing outer joins */
072: private IJoinQueryString joinTest = null;
073:
074: //Database meta information
075: private boolean useTSWrapper = false;
076: private boolean useToDate = false;
077: private boolean supportsTransactions = false;
078: private boolean supportsPreparedStatements = false;
079: private String transactionTestMsg = "";
080: private String databaseProductName = null;
081: private String databaseProductVersion = null;
082: private String driverName = null;
083: private String driverVersion = null;
084: private String userName = null;
085: private String dbUrl = null;
086:
087: /**
088: * Creates a new {@link DatabaseMetaDataImpl} with the specified
089: * {@link DataSource}.
090: *
091: * @param ds The {@link DataSource} to use as the base for this server interface.
092: */
093: public DatabaseMetaDataImpl(final DataSource ds) {
094: if (ds == null)
095: throw new IllegalArgumentException(
096: "DataSource cannot be null");
097:
098: this .dataSource = ds;
099:
100: this .runDatabaseTests();
101: if (LOG.isInfoEnabled())
102: LOG.info(this .toString());
103: }
104:
105: /**
106: *
107: */
108: public void releaseConnection(final Connection conn) {
109: try {
110: if (conn != null) {
111: conn.close();
112: }
113: } catch (Exception e) {
114: LOG.warn("An error occured while closing a connection.", e);
115: }
116: }
117:
118: /**
119: * @see org.jasig.portal.rdbm.IDatabaseMetadata#getJoinQuery()
120: */
121: public final IJoinQueryString getJoinQuery() {
122: return this .joinTest;
123: }
124:
125: /**
126: * @see org.jasig.portal.rdbm.IDatabaseMetadata#supportsOuterJoins()
127: */
128: public final boolean supportsOuterJoins() {
129: return (this .joinTest != null);
130: }
131:
132: /**
133: * @see org.jasig.portal.rdbm.IDatabaseMetadata#supportsTransactions()
134: */
135: public final boolean supportsTransactions() {
136: return this .supportsTransactions;
137: }
138:
139: /**
140: * @see org.jasig.portal.rdbm.IDatabaseMetadata#supportsPreparedStatements()
141: */
142: public final boolean supportsPreparedStatements() {
143: return this .supportsPreparedStatements;
144: }
145:
146: /**
147: * @see org.jasig.portal.rdbm.IDatabaseMetadata#sqlTimeStamp()
148: */
149: public String sqlTimeStamp() {
150: return this .sqlTimeStamp(System.currentTimeMillis());
151: }
152:
153: /**
154: * @see org.jasig.portal.rdbm.IDatabaseMetadata#sqlTimeStamp(long)
155: */
156: public String sqlTimeStamp(final long date) {
157: final StringBuffer sqlTS = new StringBuffer();
158:
159: if (useToDate) {
160: sqlTS.append("TO_DATE('");
161: sqlTS.append(TO_DATE_FORMAT.format(new Date(date)));
162: sqlTS.append("', 'YYYY MM DD HH24:MI:SS')");
163: } else if (useTSWrapper) {
164: sqlTS.append("{ts '");
165: sqlTS.append(new Timestamp(date).toString());
166: sqlTS.append("'}");
167: } else {
168: sqlTS.append("'");
169: sqlTS.append(new Timestamp(date).toString());
170: sqlTS.append("'");
171: }
172:
173: return sqlTS.toString();
174: }
175:
176: /**
177: * @see org.jasig.portal.rdbm.IDatabaseMetadata#sqlTimeStamp(java.util.Date)
178: */
179: public String sqlTimeStamp(final Date date) {
180: if (date == null)
181: return "NULL";
182: else
183: return this .sqlTimeStamp(date.getTime());
184: }
185:
186: public String toString() {
187: final StringBuffer dbInfo = new StringBuffer();
188:
189: dbInfo.append(this .databaseProductName);
190: dbInfo.append(" (");
191: dbInfo.append(this .databaseProductVersion);
192: dbInfo.append(") / ");
193: dbInfo.append(this .driverName);
194: dbInfo.append(" (");
195: dbInfo.append(this .driverVersion);
196: dbInfo.append(") database/driver");
197: dbInfo.append("\n");
198: dbInfo.append(" Connected To: ");
199: dbInfo.append(this .dbUrl);
200: dbInfo.append("\n");
201: dbInfo.append(" Supports:");
202: dbInfo.append("\n");
203: dbInfo.append(" Prepared Statements: ");
204: dbInfo.append(this .supportsPreparedStatements());
205: dbInfo.append("\n");
206: dbInfo.append(" Outer Joins: ");
207: dbInfo.append(this .supportsOuterJoins());
208: dbInfo.append("\n");
209: dbInfo.append(" Transactions: ");
210: dbInfo.append(this .supportsTransactions());
211: dbInfo.append(this .transactionTestMsg);
212: dbInfo.append("\n");
213: dbInfo.append(" {ts metasyntax: ");
214: dbInfo.append(this .useTSWrapper);
215: dbInfo.append("\n");
216: dbInfo.append(" TO_DATE(): ");
217: dbInfo.append(this .useToDate);
218:
219: return dbInfo.toString();
220: }
221:
222: /**
223: * Run a set of tests on the database to provide better meta data.
224: */
225: private void runDatabaseTests() {
226: Connection conn = null;
227: try {
228: conn = this .dataSource.getConnection();
229: //The order of these tests is IMPORTANT, each may depend on the
230: //results of the previous tests.
231: this .getMetaData(conn);
232: this .testPreparedStatements(conn);
233: this .testOuterJoins(conn);
234: this .testTimeStamp(conn);
235: this .testTransactions(conn);
236:
237: } catch (SQLException e) {
238: LOG.error("Error during database initialization. ", e);
239: /*
240: * We must throw a RuntimeException here to avoid starting the portal
241: * with incorrect assumptions about what the database supports.
242: */
243: throw new DataAccessResourceFailureException(
244: "Error during database initialization. ", e);
245: } finally {
246: this .releaseConnection(conn);
247: }
248: }
249:
250: /**
251: * Gets meta data about the connection.
252: *
253: * @param conn The connection to use.
254: */
255: private void getMetaData(final Connection conn) {
256: try {
257: final DatabaseMetaData dmd = conn.getMetaData();
258:
259: this .databaseProductName = dmd.getDatabaseProductName();
260: this .databaseProductVersion = dmd
261: .getDatabaseProductVersion();
262: this .driverName = dmd.getDriverName();
263: this .driverVersion = dmd.getDriverVersion();
264: this .userName = dmd.getUserName();
265: this .dbUrl = dmd.getURL();
266: } catch (SQLException sqle) {
267: LOG.error("Error getting database meta data.", sqle);
268: }
269: }
270:
271: /**
272: * Tests the database for prepared statement support.
273: *
274: * @param conn The connection to use.
275: */
276: private void testPreparedStatements(final Connection conn) {
277: try {
278: final String pStmtTestQuery = "SELECT USER_ID "
279: + "FROM UP_USER " + "WHERE USER_ID=?";
280:
281: final PreparedStatement pStmt = conn
282: .prepareStatement(pStmtTestQuery);
283:
284: try {
285: pStmt.clearParameters();
286: final int userId = 0;
287: pStmt.setInt(1, userId); //Set USER_ID=0
288: final ResultSet rs = pStmt.executeQuery();
289:
290: try {
291: if (rs.next() && userId == rs.getInt(1)) {
292: this .supportsPreparedStatements = true;
293: }
294: } finally {
295: RDBMServices.closeResultSet(rs);
296: }
297: } finally {
298: RDBMServices.closeStatement(pStmt);
299: }
300: } catch (SQLException sqle) {
301: LOG.error("PreparedStatements are not supported!", sqle);
302: }
303: }
304:
305: /**
306: * Test the database to see if it really supports outer joins.
307: * @param conn The connection to use.
308: */
309: private void testOuterJoins(final Connection conn) {
310: try {
311: if (conn.getMetaData().supportsOuterJoins()) {
312: final Statement joinTestStmt = conn.createStatement();
313:
314: try {
315: for (int index = 0; index < joinTests.length; index++) {
316: final String joinTestQuery = "SELECT COUNT(UP_USER.USER_ID) "
317: + "FROM "
318: + joinTests[index].getTestJoin()
319: + " UP_USER.USER_ID=0";
320:
321: try {
322: final ResultSet rs = joinTestStmt
323: .executeQuery(joinTestQuery);
324:
325: RDBMServices.closeResultSet(rs);
326:
327: this .joinTest = joinTests[index];
328: if (LOG.isInfoEnabled())
329: LOG.info("Using join test: "
330: + this .joinTest.getClass()
331: .getName());
332: break;
333: } catch (SQLException sqle) {
334: if (LOG.isInfoEnabled())
335: LOG.info("Join test failed: "
336: + joinTests[index].getClass()
337: .getName()
338: + " with sql error: '"
339: + sqle.getLocalizedMessage()
340: + "' on statement: '"
341: + joinTestQuery + "'");
342: }
343: }
344: } finally {
345: RDBMServices.closeStatement(joinTestStmt);
346: }
347: }
348: } catch (SQLException sqle) {
349: LOG.warn("Error running join tests.", sqle);
350: }
351: }
352:
353: /**
354: * Test the database to find the supported timestamp format
355: * @param conn The connection to use.
356: */
357: private void testTimeStamp(final Connection conn) {
358: try {
359: //Try using {ts }
360: final String timeStampTestQuery = "SELECT USER_ID "
361: + "FROM UP_USER "
362: + "WHERE LST_CHAN_UPDT_DT={ts '2001-01-01 00:00:00.0'} AND USER_ID = 0";
363:
364: final PreparedStatement timeStampTestPStmt = conn
365: .prepareStatement(timeStampTestQuery);
366:
367: try {
368: final ResultSet rs = timeStampTestPStmt.executeQuery();
369:
370: RDBMServices.closeResultSet(rs);
371:
372: this .useTSWrapper = true;
373: } finally {
374: RDBMServices.closeStatement(timeStampTestPStmt);
375: }
376: } catch (SQLException sqle1) {
377: LOG.info("Error running {ts } test.", sqle1);
378:
379: //Try using TO_DATE()
380: try {
381: final String toDateTestQuery = "SELECT USER_ID "
382: + "FROM UP_USER "
383: + "WHERE LST_CHAN_UPDT_DT=TO_DATE('2001 01 01 00:00', 'YYYY MM DD HH24:MI:SS') AND USER_ID=0";
384:
385: final PreparedStatement toDateTestPStmt = conn
386: .prepareStatement(toDateTestQuery);
387:
388: try {
389: final ResultSet rs = toDateTestPStmt.executeQuery();
390:
391: RDBMServices.closeResultSet(rs);
392:
393: this .useToDate = true;
394: } finally {
395: RDBMServices.closeStatement(toDateTestPStmt);
396: }
397: } catch (SQLException sqle2) {
398: LOG.info("Error running TO_DATE() test.", sqle2);
399: }
400: }
401: }
402:
403: /**
404: * Test the database to see if it really supports transactions
405: * @param conn The connection to use.
406: */
407: private void testTransactions(final Connection conn) {
408: try {
409: if (conn.getMetaData().supportsTransactions()) {
410: conn.setAutoCommit(false); //Not using RDBMServices here, we want to see the exception if it happens
411:
412: final Statement transTestStmt = conn.createStatement();
413:
414: try {
415: final String transTestUpdate = "UPDATE UP_USER "
416: + "SET LST_CHAN_UPDT_DT="
417: + this .sqlTimeStamp() + " "
418: + "WHERE USER_ID=0";
419:
420: transTestStmt.executeUpdate(transTestUpdate);
421: conn.rollback();
422: this .supportsTransactions = true;
423: } finally {
424: RDBMServices.closeStatement(transTestStmt);
425: }
426: }
427: } catch (SQLException sqle) {
428: LOG
429: .warn(
430: "Error running transaction test (Transactions are not supported).",
431: sqle);
432: this .transactionTestMsg = " (driver lies)";
433: }
434: }
435: }
|