0001: /**********************************************************************************
0002: * $URL: https://source.sakaiproject.org/svn/db/tags/sakai_2-4-1/db-impl/impl/src/java/org/sakaiproject/db/impl/BasicSqlService.java $
0003: * $Id: BasicSqlService.java 30215 2007-05-09 18:20:55Z ajpoland@iupui.edu $
0004: ***********************************************************************************
0005: *
0006: * Copyright (c) 2003, 2004, 2005, 2006, 2007 The Sakai Foundation.
0007: *
0008: * Licensed under the Educational Community License, Version 1.0 (the "License");
0009: * you may not use this file except in compliance with the License.
0010: * You may obtain a copy of the License at
0011: *
0012: * http://www.opensource.org/licenses/ecl1.php
0013: *
0014: * Unless required by applicable law or agreed to in writing, software
0015: * distributed under the License is distributed on an "AS IS" BASIS,
0016: * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
0017: * See the License for the specific language governing permissions and
0018: * limitations under the License.
0019: *
0020: **********************************************************************************/package org.sakaiproject.db.impl;
0021:
0022: import java.io.BufferedReader;
0023: import java.io.ByteArrayInputStream;
0024: import java.io.IOException;
0025: import java.io.InputStream;
0026: import java.io.InputStreamReader;
0027: import java.io.OutputStream;
0028: import java.io.StringReader;
0029: import java.io.UnsupportedEncodingException;
0030: import java.lang.reflect.InvocationTargetException;
0031: import java.lang.reflect.Method;
0032: import java.sql.Connection;
0033: import java.sql.PreparedStatement;
0034: import java.sql.ResultSet;
0035: import java.sql.ResultSetMetaData;
0036: import java.sql.SQLException;
0037: import java.sql.Statement;
0038: import java.sql.Timestamp;
0039: import java.util.GregorianCalendar;
0040: import java.util.List;
0041: import java.util.TimeZone;
0042: import java.util.Vector;
0043:
0044: import javax.sql.DataSource;
0045:
0046: import org.apache.commons.logging.Log;
0047: import org.apache.commons.logging.LogFactory;
0048: import org.sakaiproject.db.api.SqlReader;
0049: import org.sakaiproject.db.api.SqlService;
0050: import org.sakaiproject.db.api.SqlServiceDeadlockException;
0051: import org.sakaiproject.db.api.SqlServiceUniqueViolationException;
0052: import org.sakaiproject.event.api.UsageSessionService;
0053: import org.sakaiproject.exception.ServerOverloadException;
0054: import org.sakaiproject.thread_local.api.ThreadLocalManager;
0055: import org.sakaiproject.time.api.Time;
0056:
0057: /**
0058: * <p>
0059: * BasicSqlService implements the SqlService.
0060: * </p>
0061: */
0062: public abstract class BasicSqlService implements SqlService {
0063: private static final Log LOG = LogFactory
0064: .getLog(BasicSqlService.class);
0065:
0066: private static final Log SWC_LOG = LogFactory
0067: .getLog(StreamWithConnection.class);
0068:
0069: /** Key name in thread local to find the current transaction connection. */
0070: protected static final String TRANSACTION_CONNECTION = "sqlService:transaction_connection";
0071:
0072: /** The "shared", "common" database connection pool */
0073: protected DataSource defaultDataSource;
0074:
0075: /** The "slow" connection pool for file uploads/downloads */
0076: protected DataSource longDataSource;
0077:
0078: /** Should we do a commit after a single statement read? */
0079: protected boolean m_commitAfterRead = false;
0080:
0081: /**********************************************************************************************************************************************************************************************************************************************************
0082: * Dependencies
0083: *********************************************************************************************************************************************************************************************************************************************************/
0084:
0085: /**
0086: * @return the UsageSessionService collaborator.
0087: */
0088: protected abstract UsageSessionService usageSessionService();
0089:
0090: /**
0091: * @return the ThreadLocalManager collaborator.
0092: */
0093: protected abstract ThreadLocalManager threadLocalManager();
0094:
0095: /**********************************************************************************************************************************************************************************************************************************************************
0096: * Configuration
0097: *********************************************************************************************************************************************************************************************************************************************************/
0098:
0099: /**
0100: * Configuration: should we do a commit after each single SQL read?
0101: *
0102: * @param value
0103: * the setting (true of false) string.
0104: */
0105: public void setCommitAfterRead(String value) {
0106: if (LOG.isDebugEnabled()) {
0107: LOG.debug("setCommitAfterRead(String " + value + ")");
0108: }
0109:
0110: m_commitAfterRead = new Boolean(value).booleanValue();
0111: }
0112:
0113: /** Database vendor used; possible values are oracle, mysql, hsqldb (default). */
0114: protected String m_vendor = "hsqldb";
0115:
0116: /**
0117: * Configuration: Database vendor used; possible values are oracle, mysql, hsqldb.
0118: *
0119: * @param value
0120: * the Database vendor used.
0121: */
0122: public void setVendor(String value) {
0123: if (LOG.isDebugEnabled()) {
0124: LOG.debug("setVendor(String " + value + ")");
0125: }
0126:
0127: m_vendor = (value != null) ? value.toLowerCase().trim() : null;
0128: }
0129:
0130: /**
0131: * @inheritDoc
0132: */
0133: public String getVendor() {
0134: return m_vendor;
0135: }
0136:
0137: /** if true, debug each sql command with timing. */
0138: protected boolean m_showSql = false;
0139:
0140: /**
0141: * Configuration: to show each sql command in the logs or not.
0142: *
0143: * @param value
0144: * the showSql setting.
0145: */
0146: public void setShowSql(String value) {
0147: if (LOG.isDebugEnabled()) {
0148: LOG.debug("setShowSql(String " + value + ")");
0149: }
0150:
0151: m_showSql = new Boolean(value).booleanValue();
0152: }
0153:
0154: /** Configuration: number of on-deadlock retries for save. */
0155: protected int m_deadlockRetries = 5;
0156:
0157: /**
0158: * Configuration: number of on-deadlock retries for save.
0159: *
0160: * @param value
0161: * the number of on-deadlock retries for save.
0162: */
0163: public void setDeadlockRetries(String value) {
0164: m_deadlockRetries = Integer.parseInt(value);
0165: }
0166:
0167: /** Configuration: to run the ddl on init or not. */
0168: protected boolean m_autoDdl = false;
0169:
0170: /**
0171: * Configuration: to run the ddl on init or not.
0172: *
0173: * @param value
0174: * the auto ddl value.
0175: */
0176: public void setAutoDdl(String value) {
0177: if (LOG.isDebugEnabled()) {
0178: LOG.debug("setAutoDdl(String " + value + ")");
0179: }
0180:
0181: m_autoDdl = new Boolean(value).booleanValue();
0182: }
0183:
0184: /**********************************************************************************************************************************************************************************************************************************************************
0185: * Init and Destroy
0186: *********************************************************************************************************************************************************************************************************************************************************/
0187:
0188: /**
0189: * Final initialization, once all dependencies are set.
0190: */
0191: public void init() {
0192: // if we are auto-creating our schema, check and create
0193: if (m_autoDdl) {
0194: ddl(getClass().getClassLoader(), "sakai_locks");
0195: }
0196:
0197: LOG.info("init(): vendor: " + m_vendor + " autoDDL: "
0198: + m_autoDdl + " deadlockRetries: " + m_deadlockRetries);
0199: }
0200:
0201: /**
0202: * Final cleanup.
0203: */
0204: public void destroy() {
0205: LOG.info("destroy()");
0206: }
0207:
0208: /**********************************************************************************************************************************************************************************************************************************************************
0209: * Work interface methods: org.sakaiproject.sql.SqlService
0210: *********************************************************************************************************************************************************************************************************************************************************/
0211:
0212: /**
0213: * {@inheritDoc}
0214: */
0215: public Connection borrowConnection() throws SQLException {
0216: LOG.debug("borrowConnection()");
0217:
0218: if (defaultDataSource != null) {
0219: return defaultDataSource.getConnection();
0220: } else {
0221: throw new SQLException("no default pool.");
0222: }
0223: }
0224:
0225: /**
0226: * {@inheritDoc}
0227: */
0228: public void returnConnection(Connection conn) {
0229: if (LOG.isDebugEnabled()) {
0230: LOG.debug("returnConnection(Connection " + conn + ")");
0231: }
0232:
0233: if (conn != null) {
0234: try {
0235: conn.close();
0236: } catch (SQLException e) {
0237: throw new Error(e);
0238: }
0239: }
0240: }
0241:
0242: /**
0243: * {@inheritDoc}
0244: */
0245: public boolean transact(Runnable callback, String tag) {
0246: // if we are already in a transaction, stay in it (don't start a new one), and just run the callback (no retries, let the outside transaction code handle that)
0247: if (threadLocalManager().get(TRANSACTION_CONNECTION) != null) {
0248: callback.run();
0249: return true;
0250: }
0251:
0252: // in case of deadlock we might retry
0253: for (int i = 0; i <= m_deadlockRetries; i++) {
0254: if (i > 0) {
0255: // make a little fuss
0256: LOG.warn("transact: deadlock: retrying (" + i + " / "
0257: + m_deadlockRetries + "): " + tag);
0258:
0259: // do a little wait, longer for each retry
0260: // TODO: randomize?
0261: try {
0262: Thread.sleep(i * 100L);
0263: } catch (Exception ignore) {
0264: }
0265: }
0266:
0267: Connection connection = null;
0268: boolean wasCommit = true;
0269: try {
0270: connection = borrowConnection();
0271: wasCommit = connection.getAutoCommit();
0272: connection.setAutoCommit(false);
0273:
0274: // store the connection in the thread
0275: threadLocalManager().set(TRANSACTION_CONNECTION,
0276: connection);
0277:
0278: callback.run();
0279:
0280: connection.commit();
0281:
0282: return true;
0283: } catch (SqlServiceDeadlockException e) {
0284: // rollback
0285: if (connection != null) {
0286: try {
0287: connection.rollback();
0288: LOG.warn("transact: deadlock: rolling back: "
0289: + tag);
0290: } catch (Exception ee) {
0291: LOG.warn("transact: (deadlock: rollback): "
0292: + tag + " : " + ee);
0293: }
0294: }
0295:
0296: // if this was the last attempt, throw to abort
0297: if (i == m_deadlockRetries) {
0298: LOG.warn("transact: deadlock: retry failure: "
0299: + tag);
0300: throw e;
0301: }
0302: } catch (RuntimeException e) {
0303: // rollback
0304: if (connection != null) {
0305: try {
0306: connection.rollback();
0307: LOG.warn("transact: rolling back: " + tag);
0308: } catch (Exception ee) {
0309: LOG.warn("transact: (rollback): " + tag + " : "
0310: + ee);
0311: }
0312: }
0313: LOG.warn("transact: failure: " + e);
0314: throw e;
0315: } catch (SQLException e) {
0316: // rollback
0317: if (connection != null) {
0318: try {
0319: connection.rollback();
0320: LOG.warn("transact: rolling back: " + tag);
0321: } catch (Exception ee) {
0322: LOG.warn("transact: (rollback): " + tag + " : "
0323: + ee);
0324: }
0325: }
0326: LOG.warn("transact: failure: " + e);
0327: throw new RuntimeException(
0328: "SqlService.transact failure", e);
0329: }
0330:
0331: finally {
0332: if (connection != null) {
0333: // clear the connection from the thread
0334: threadLocalManager().set(TRANSACTION_CONNECTION,
0335: null);
0336:
0337: try {
0338: connection.setAutoCommit(wasCommit);
0339: } catch (Exception e) {
0340: LOG.warn("transact: (setAutoCommit): " + tag
0341: + " : " + e);
0342: }
0343: returnConnection(connection);
0344: }
0345: }
0346: }
0347:
0348: return false;
0349: }
0350:
0351: /** Used to work with dates in GMT in the db. */
0352: protected final GregorianCalendar m_cal = new GregorianCalendar(
0353: TimeZone.getTimeZone("GMT"));
0354:
0355: /**
0356: * {@inheritDoc}
0357: */
0358: public GregorianCalendar getCal() {
0359: return m_cal;
0360: }
0361:
0362: /**
0363: * Read a single field from the db, from multiple records, returned as string[], one per record.
0364: *
0365: * @param sql
0366: * The sql statement.
0367: * @return The List of Strings of single fields of the record found, or empty if none found.
0368: */
0369: public List dbRead(String sql) {
0370: if (LOG.isDebugEnabled()) {
0371: LOG.debug("dbRead(String " + sql + ")");
0372: }
0373:
0374: return dbRead(sql, null, null);
0375: }
0376:
0377: /**
0378: * Process a query, filling in with fields, and return the results as a List, one per record read. If a reader is provided, it will be called for each record to prepare the Object placed into the List. Otherwise, the first field of each record, as a
0379: * String, will be placed in the list.
0380: *
0381: * @param sql
0382: * The sql statement.
0383: * @param fields
0384: * The array of fields for parameters.
0385: * @param reader
0386: * The reader object to read each record.
0387: * @return The List of things read, one per record.
0388: */
0389: public List dbRead(String sql, Object[] fields, SqlReader reader) {
0390: if (LOG.isDebugEnabled()) {
0391: LOG.debug("dbRead(String " + sql + ", Object[] " + fields
0392: + ", SqlReader " + reader + ")");
0393: }
0394:
0395: return dbRead(null, sql, fields, reader);
0396: }
0397:
0398: /**
0399: * Process a query, filling in with fields, and return the results as a List, one per record read. If a reader is provided, it will be called for each record to prepare the Object placed into the List. Otherwise, the first field of each record, as a
0400: * String, will be placed in the list.
0401: *
0402: * @param callerConn
0403: * The db connection object to use (if not null).
0404: * @param sql
0405: * The sql statement.
0406: * @param fields
0407: * The array of fields for parameters.
0408: * @param reader
0409: * The reader object to read each record.
0410: * @return The List of things read, one per record.
0411: */
0412: public List dbRead(Connection callerConn, String sql,
0413: Object[] fields, SqlReader reader) {
0414: // check for a transaction conncetion
0415: if (callerConn == null) {
0416: callerConn = (Connection) threadLocalManager().get(
0417: TRANSACTION_CONNECTION);
0418: }
0419:
0420: if (LOG.isDebugEnabled()) {
0421: LOG.debug("dbRead(Connection " + callerConn + ", String "
0422: + sql + ", Object[] " + fields + ", SqlReader "
0423: + reader + ")");
0424: }
0425:
0426: // for DEBUG
0427: long start = 0;
0428: long connectionTime = 0;
0429: int lenRead = 0;
0430: long stmtTime = 0;
0431: long resultsTime = 0;
0432: int count = 0;
0433:
0434: if (LOG.isDebugEnabled()) {
0435: String userId = usageSessionService().getSessionId();
0436: StringBuffer buf = new StringBuffer();
0437: if (fields != null) {
0438: buf.append(fields[0]);
0439: for (int i = 1; i < fields.length; i++) {
0440: buf.append(", ");
0441: buf.append(fields[i]);
0442: }
0443: }
0444: LOG
0445: .debug("Sql.dbRead: " + userId + "\n" + sql + "\n"
0446: + buf);
0447: }
0448:
0449: Connection conn = null;
0450: PreparedStatement pstmt = null;
0451: ResultSet result = null;
0452: ResultSetMetaData meta = null;
0453: List rv = new Vector();
0454:
0455: try {
0456: if (m_showSql)
0457: start = System.currentTimeMillis();
0458:
0459: // borrow a new connection if we are not provided with one to use
0460: if (callerConn != null) {
0461: conn = callerConn;
0462: } else {
0463: conn = borrowConnection();
0464: }
0465: if (m_showSql)
0466: connectionTime = System.currentTimeMillis() - start;
0467: if (m_showSql)
0468: start = System.currentTimeMillis();
0469:
0470: pstmt = conn.prepareStatement(sql);
0471:
0472: // put in all the fields
0473: prepareStatement(pstmt, fields);
0474:
0475: result = pstmt.executeQuery();
0476:
0477: if (m_showSql)
0478: stmtTime = System.currentTimeMillis() - start;
0479: if (m_showSql)
0480: start = System.currentTimeMillis();
0481:
0482: while (result.next()) {
0483: if (m_showSql)
0484: count++;
0485:
0486: try {
0487: // without a reader, we read the first String from each record
0488: if (reader == null) {
0489: String s = result.getString(1);
0490: if (s != null)
0491: rv.add(s);
0492: } else {
0493: Object obj = reader.readSqlResultRecord(result);
0494: if (obj != null)
0495: rv.add(obj);
0496: }
0497: } catch (Throwable t) {
0498: LOG
0499: .warn("Sql.dbRead: unable to read a result from sql: "
0500: + sql
0501: + debugFields(fields)
0502: + " row: " + result.getRow());
0503: }
0504: }
0505: } catch (Exception e) {
0506: LOG
0507: .warn("Sql.dbRead: sql: " + sql
0508: + debugFields(fields), e);
0509: } finally {
0510: if (m_showSql)
0511: resultsTime = System.currentTimeMillis() - start;
0512:
0513: try {
0514: if (null != result)
0515: result.close();
0516: if (null != pstmt)
0517: pstmt.close();
0518:
0519: // return the connection only if we have borrowed a new one for this call
0520: if (callerConn == null) {
0521: if (null != conn) {
0522: // if we commit on read
0523: if (m_commitAfterRead) {
0524: conn.commit();
0525: }
0526:
0527: returnConnection(conn);
0528: }
0529: }
0530: } catch (Exception e) {
0531: LOG.warn("Sql.dbRead: sql: " + sql
0532: + debugFields(fields), e);
0533: }
0534: }
0535:
0536: if (m_showSql)
0537: debug("Sql.dbRead: time: " + connectionTime + " / "
0538: + stmtTime + " / " + resultsTime + " #: " + count,
0539: sql, fields);
0540:
0541: return rv;
0542: }
0543:
0544: /**
0545: * Read a single field from the db, from multiple record - concatenating the binary values into value.
0546: *
0547: * @param sql
0548: * The sql statement.
0549: * @param fields
0550: * The array of fields for parameters.
0551: * @param value
0552: * The array of bytes to fill with the value read from the db.
0553: */
0554: public void dbReadBinary(String sql, Object[] fields, byte[] value) {
0555: if (LOG.isDebugEnabled()) {
0556: LOG.debug("dbReadBinary(String " + sql + ", Object[] "
0557: + fields + ", byte[] " + value + ")");
0558: }
0559:
0560: dbReadBinary(null, sql, fields, value);
0561: }
0562:
0563: /**
0564: * Read a single field from the db, from multiple record - concatenating the binary values into value.
0565: *
0566: * @param callerConn
0567: * The optional db connection object to use.
0568: * @param sql
0569: * The sql statement.
0570: * @param fields
0571: * The array of fields for parameters.
0572: * @param value
0573: * The array of bytes to fill with the value read from the db.
0574: */
0575: public void dbReadBinary(Connection callerConn, String sql,
0576: Object[] fields, byte[] value) {
0577: // check for a transaction conncetion
0578: if (callerConn == null) {
0579: callerConn = (Connection) threadLocalManager().get(
0580: TRANSACTION_CONNECTION);
0581: }
0582:
0583: if (LOG.isDebugEnabled()) {
0584: LOG.debug("dbReadBinary(Connection " + callerConn
0585: + ", String " + sql + ", Object[] " + fields
0586: + ", byte[] " + value + ")");
0587: }
0588:
0589: // for DEBUG
0590: long start = 0;
0591: long connectionTime = 0;
0592: int lenRead = 0;
0593:
0594: if (LOG.isDebugEnabled()) {
0595: String userId = usageSessionService().getSessionId();
0596: LOG.debug("Sql.dbReadBinary(): " + userId + "\n" + sql);
0597: }
0598:
0599: Connection conn = null;
0600: PreparedStatement pstmt = null;
0601: ResultSet result = null;
0602: ResultSetMetaData meta = null;
0603:
0604: try {
0605: if (m_showSql)
0606: start = System.currentTimeMillis();
0607: if (callerConn != null) {
0608: conn = callerConn;
0609: } else {
0610: conn = borrowConnection();
0611: }
0612: if (m_showSql)
0613: connectionTime = System.currentTimeMillis() - start;
0614: if (m_showSql)
0615: start = System.currentTimeMillis();
0616:
0617: pstmt = conn.prepareStatement(sql);
0618:
0619: // put in all the fields
0620: prepareStatement(pstmt, fields);
0621:
0622: result = pstmt.executeQuery();
0623:
0624: int index = 0;
0625: while (result.next() && (index < value.length)) {
0626: InputStream stream = result.getBinaryStream(1);
0627: int len = stream.read(value, index, value.length
0628: - index);
0629: stream.close();
0630: index += len;
0631: if (m_showSql)
0632: lenRead += len;
0633: }
0634: } catch (Exception e) {
0635: LOG.warn("Sql.dbReadBinary(): " + e);
0636: } finally {
0637: try {
0638: if (null != result)
0639: result.close();
0640: if (null != pstmt)
0641: pstmt.close();
0642:
0643: // return the connection only if we have borrowed a new one for this call
0644: if (callerConn == null) {
0645: if (null != conn) {
0646: // if we commit on read
0647: if (m_commitAfterRead) {
0648: conn.commit();
0649: }
0650:
0651: returnConnection(conn);
0652: }
0653: }
0654: } catch (Exception e) {
0655: LOG.warn("Sql.dbReadBinary(): " + e);
0656: }
0657: }
0658:
0659: if (m_showSql)
0660: debug("sql read binary: len: " + lenRead + " time: "
0661: + connectionTime + " / "
0662: + (System.currentTimeMillis() - start), sql, fields);
0663: }
0664:
0665: /**
0666: * Read a single field / record from the db, returning a stream on the result record / field. The stream holds the conection open - so it must be closed or finalized quickly!
0667: *
0668: * @param sql
0669: * The sql statement.
0670: * @param fields
0671: * The array of fields for parameters.
0672: * @param big
0673: * If true, the read is expected to be potentially large.
0674: * @throws ServerOverloadException
0675: * if the read cannot complete due to lack of a free connection (if wait is false)
0676: */
0677: public InputStream dbReadBinary(String sql, Object[] fields,
0678: boolean big) throws ServerOverloadException {
0679: // Note: does not support TRANSACTION_CONNECTION -ggolden
0680:
0681: if (LOG.isDebugEnabled()) {
0682: LOG.debug("dbReadBinary(String " + sql + ", Object[] "
0683: + fields + ", boolean " + big + ")");
0684: }
0685:
0686: InputStream rv = null;
0687:
0688: // for DEBUG
0689: long start = 0;
0690: long connectionTime = 0;
0691: int lenRead = 0;
0692:
0693: if (LOG.isDebugEnabled()) {
0694: String userId = usageSessionService().getSessionId();
0695: LOG.debug("Sql.dbReadBinary(): " + userId + "\n" + sql);
0696: }
0697:
0698: Connection conn = null;
0699: PreparedStatement pstmt = null;
0700: ResultSet result = null;
0701: ResultSetMetaData meta = null;
0702:
0703: try {
0704: if (m_showSql)
0705: start = System.currentTimeMillis();
0706: if (!big) {
0707: conn = borrowConnection();
0708: } else {
0709: // get a connection if it's available, else throw
0710: conn = borrowConnection();
0711: if (conn == null) {
0712: throw new ServerOverloadException(null);
0713: }
0714: }
0715: if (m_showSql)
0716: connectionTime = System.currentTimeMillis() - start;
0717: if (m_showSql)
0718: start = System.currentTimeMillis();
0719:
0720: pstmt = conn.prepareStatement(sql);
0721:
0722: // put in all the fields
0723: prepareStatement(pstmt, fields);
0724:
0725: result = pstmt.executeQuery();
0726:
0727: if (result.next()) {
0728: InputStream stream = result.getBinaryStream(1);
0729: rv = new StreamWithConnection(stream, result, pstmt,
0730: conn);
0731: }
0732: } catch (ServerOverloadException e) {
0733: throw e;
0734: } catch (Exception e) {
0735: LOG.warn("Sql.dbReadBinary(): " + e);
0736: } finally {
0737: // ONLY if we didn't make the rv - else let the rv hold these OPEN!
0738: if (rv == null) {
0739: try {
0740: if (null != result)
0741: result.close();
0742: if (null != pstmt)
0743: pstmt.close();
0744: if (null != conn) {
0745: // if we commit on read
0746: if (m_commitAfterRead) {
0747: conn.commit();
0748: }
0749:
0750: // return to the proper pool!
0751: if (big) {
0752: returnConnection(conn);
0753: } else {
0754: returnConnection(conn);
0755: }
0756: }
0757: } catch (Exception e) {
0758: LOG.warn("Sql.dbReadBinary(): " + e);
0759: }
0760: }
0761: }
0762:
0763: if (m_showSql)
0764: debug("sql read binary: len: " + lenRead + " time: "
0765: + connectionTime + " / "
0766: + (System.currentTimeMillis() - start), sql, fields);
0767:
0768: return rv;
0769: }
0770:
0771: /**
0772: * Execute the "write" sql - no response.
0773: *
0774: * @param sql
0775: * The sql statement.
0776: * @return true if successful, false if not.
0777: */
0778: public boolean dbWrite(String sql) {
0779: if (LOG.isDebugEnabled()) {
0780: LOG.debug("dbWrite(String " + sql + ")");
0781: }
0782:
0783: return dbWrite(sql, null, null, null, false);
0784:
0785: } // dbWrite
0786:
0787: /**
0788: * Execute the "write" sql - no response. a long field is set to "?" - fill it in with var
0789: *
0790: * @param sql
0791: * The sql statement.
0792: * @param var
0793: * The value to bind to the first parameter in the sql statement.
0794: * @return true if successful, false if not.
0795: */
0796: public boolean dbWrite(String sql, String var) {
0797: if (LOG.isDebugEnabled()) {
0798: LOG
0799: .debug("dbWrite(String " + sql + ", String " + var
0800: + ")");
0801: }
0802:
0803: return dbWrite(sql, null, var, null, false);
0804: }
0805:
0806: /**
0807: * Execute the "write" sql - no response. a long binary field is set to "?" - fill it in with var
0808: *
0809: * @param sql
0810: * The sql statement.
0811: * @param fields
0812: * The array of fields for parameters.
0813: * @param var
0814: * The value to bind to the last parameter in the sql statement.
0815: * @param offset
0816: * The start within the var to write
0817: * @param len
0818: * The number of bytes of var, starting with index, to write
0819: * @return true if successful, false if not.
0820: */
0821: public boolean dbWriteBinary(String sql, Object[] fields,
0822: byte[] var, int offset, int len) {
0823: // Note: does not support TRANSACTION_CONNECTION -ggolden
0824:
0825: if (LOG.isDebugEnabled()) {
0826: LOG.debug("dbWriteBinary(String " + sql + ", Object[] "
0827: + fields + ", byte[] " + var + ", int " + offset
0828: + ", int " + len + ")");
0829: }
0830:
0831: // for DEBUG
0832: long start = 0;
0833: long connectionTime = 0;
0834:
0835: if (LOG.isDebugEnabled()) {
0836: String userId = usageSessionService().getSessionId();
0837: LOG.debug("Sql.dbWriteBinary(): " + userId + "\n" + sql
0838: + " size:" + var.length);
0839: }
0840:
0841: Connection conn = null;
0842: PreparedStatement pstmt = null;
0843: boolean autoCommit = false;
0844: boolean resetAutoCommit = false;
0845:
0846: // stream from the var
0847: InputStream varStream = new ByteArrayInputStream(var, offset,
0848: len);
0849:
0850: boolean success = false;
0851:
0852: try {
0853: if (m_showSql)
0854: start = System.currentTimeMillis();
0855: conn = borrowConnection();
0856: if (m_showSql)
0857: connectionTime = System.currentTimeMillis() - start;
0858:
0859: // make sure we do not have auto commit - will change and reset if needed
0860: autoCommit = conn.getAutoCommit();
0861: if (autoCommit) {
0862: conn.setAutoCommit(false);
0863: resetAutoCommit = true;
0864: }
0865:
0866: if (m_showSql)
0867: start = System.currentTimeMillis();
0868: pstmt = conn.prepareStatement(sql);
0869:
0870: // put in all the fields
0871: int pos = prepareStatement(pstmt, fields);
0872:
0873: // last, put in the binary
0874: pstmt.setBinaryStream(pos, varStream, len);
0875:
0876: int result = pstmt.executeUpdate();
0877:
0878: // commit and indicate success
0879: conn.commit();
0880: success = true;
0881: } catch (SQLException e) {
0882: // this is likely due to a key constraint problem...
0883: return false;
0884: } catch (Exception e) {
0885: LOG.warn("Sql.dbWriteBinary(): " + e);
0886: return false;
0887: } finally {
0888: try {
0889: if (null != pstmt)
0890: pstmt.close();
0891: varStream.close();
0892: if (null != conn) {
0893: // rollback on failure
0894: if (!success) {
0895: conn.rollback();
0896: }
0897:
0898: // if we changed the auto commit, reset here
0899: if (resetAutoCommit) {
0900: conn.setAutoCommit(autoCommit);
0901: }
0902: returnConnection(conn);
0903: }
0904: } catch (Exception e) {
0905: LOG.warn("Sql.dbWriteBinary(): " + e);
0906: }
0907: }
0908:
0909: if (m_showSql)
0910: debug("sql write binary: len: " + len + " time: "
0911: + connectionTime + " / "
0912: + (System.currentTimeMillis() - start), sql, fields);
0913:
0914: return true;
0915: }
0916:
0917: /**
0918: * Execute the "write" sql - no response, using a set of fields from an array plus one more as params.
0919: *
0920: * @param sql
0921: * The sql statement.
0922: * @param fields
0923: * The array of fields for parameters.
0924: * @return true if successful, false if not.
0925: */
0926: public boolean dbWrite(String sql, Object[] fields) {
0927: if (LOG.isDebugEnabled()) {
0928: LOG.debug("dbWrite(String " + sql + ", Object[] " + fields
0929: + ")");
0930: }
0931:
0932: return dbWrite(sql, fields, null, null, false);
0933: }
0934:
0935: /**
0936: * Execute the "write" sql - no response, using a set of fields from an array and a given connection.
0937: *
0938: * @param connection
0939: * The connection to use.
0940: * @param sql
0941: * The sql statement.
0942: * @param fields
0943: * The array of fields for parameters.
0944: * @return true if successful, false if not.
0945: */
0946: public boolean dbWrite(Connection connection, String sql,
0947: Object[] fields) {
0948: if (LOG.isDebugEnabled()) {
0949: LOG.debug("dbWrite(Connection " + connection + ", String "
0950: + sql + ", Object[] " + fields + ")");
0951: }
0952:
0953: return dbWrite(sql, fields, null, connection, false);
0954: }
0955:
0956: /**
0957: * Execute the "write" sql - no response, using a set of fields from an array and a given connection logging no errors on failure.
0958: *
0959: * @param connection
0960: * The connection to use.
0961: * @param sql
0962: * The sql statement.
0963: * @param fields
0964: * The array of fields for parameters.
0965: * @return true if successful, false if not.
0966: */
0967: public boolean dbWriteFailQuiet(Connection connection, String sql,
0968: Object[] fields) {
0969: if (LOG.isDebugEnabled()) {
0970: LOG.debug("dbWriteFailQuiet(Connection " + connection
0971: + ", String " + sql + ", Object[] " + fields + ")");
0972: }
0973:
0974: return dbWrite(sql, fields, null, connection, true);
0975: }
0976:
0977: /**
0978: * Execute the "write" sql - no response, using a set of fields from an array plus one more as params.
0979: *
0980: * @param sql
0981: * The sql statement.
0982: * @param fields
0983: * The array of fields for parameters.
0984: * @param lastField
0985: * The value to bind to the last parameter in the sql statement.
0986: * @return true if successful, false if not.
0987: */
0988: public boolean dbWrite(String sql, Object[] fields, String lastField) {
0989: if (LOG.isDebugEnabled()) {
0990: LOG.debug("dbWrite(String " + sql + ", Object[] " + fields
0991: + ", String " + lastField + ")");
0992: }
0993:
0994: return dbWrite(sql, fields, lastField, null, false);
0995: }
0996:
0997: /**
0998: * Execute the "write" sql - no response, using a set of fields from an array plus one more as params and connection.
0999: *
1000: * @param sql
1001: * The sql statement.
1002: * @param fields
1003: * The array of fields for parameters.
1004: * @param lastField
1005: * The value to bind to the last parameter in the sql statement.
1006: * @param callerConnection
1007: * The connection to use.
1008: * @param failQuiet
1009: * If true, don't log errors from statement failure
1010: * @return true if successful, false if not due to unique constraint violation or duplicate key (i.e. the record already exists) OR we are instructed to fail quiet.
1011: */
1012: protected boolean dbWrite(String sql, Object[] fields,
1013: String lastField, Connection callerConnection,
1014: boolean failQuiet) {
1015: // check for a transaction conncetion
1016: if (callerConnection == null) {
1017: callerConnection = (Connection) threadLocalManager().get(
1018: TRANSACTION_CONNECTION);
1019: }
1020:
1021: if (LOG.isDebugEnabled()) {
1022: LOG
1023: .debug("dbWrite(String " + sql + ", Object[] "
1024: + fields + ", String " + lastField
1025: + ", Connection " + callerConnection
1026: + ", boolean " + failQuiet + ")");
1027: }
1028:
1029: // for DEBUG
1030: long start = 0;
1031: long connectionTime = 0;
1032:
1033: if (LOG.isDebugEnabled()) {
1034: String userId = usageSessionService().getSessionId();
1035: StringBuffer buf = new StringBuffer();
1036: if (fields != null) {
1037: buf.append(fields[0]);
1038: for (int i = 1; i < fields.length; i++) {
1039: buf.append(", ");
1040: buf.append(fields[i]);
1041: }
1042: if (lastField != null) {
1043: buf.append(", ");
1044: buf.append(lastField);
1045: }
1046: } else if (lastField != null) {
1047: buf.append(lastField);
1048: }
1049: LOG.debug("Sql.dbWrite(): " + userId + "\n" + sql + "\n"
1050: + buf);
1051: }
1052:
1053: Connection conn = null;
1054: PreparedStatement pstmt = null;
1055: boolean autoCommit = false;
1056: boolean resetAutoCommit = false;
1057:
1058: boolean success = false;
1059:
1060: try {
1061: if (callerConnection != null) {
1062: conn = callerConnection;
1063: } else {
1064: if (m_showSql)
1065: start = System.currentTimeMillis();
1066: conn = borrowConnection();
1067: if (m_showSql)
1068: connectionTime = System.currentTimeMillis() - start;
1069:
1070: // make sure we have do not have auto commit - will change and reset if needed
1071: autoCommit = conn.getAutoCommit();
1072: if (autoCommit) {
1073: conn.setAutoCommit(false);
1074: resetAutoCommit = true;
1075: }
1076: }
1077:
1078: if (m_showSql)
1079: start = System.currentTimeMillis();
1080: pstmt = conn.prepareStatement(sql);
1081:
1082: // put in all the fields
1083: int pos = prepareStatement(pstmt, fields);
1084:
1085: // last, put in the string value
1086: if (lastField != null) {
1087: if ("mysql".equals(m_vendor)) {
1088: // see http://bugs.sakaiproject.org/jira/browse/SAK-1737
1089: // MySQL setCharacterStream() is broken and truncates UTF-8
1090: // international characters sometimes. So use setBytes()
1091: // instead (just for MySQL).
1092: pstmt.setBytes(pos, lastField.getBytes("UTF-8"));
1093: pos++;
1094:
1095: } else {
1096: pstmt.setCharacterStream(pos, new StringReader(
1097: lastField), lastField.length());
1098: pos++;
1099: }
1100: }
1101:
1102: int result = pstmt.executeUpdate();
1103:
1104: // commit unless we are in a transaction (provided with a connection)
1105: if (callerConnection == null) {
1106: conn.commit();
1107: }
1108:
1109: // indicate success
1110: success = true;
1111: } catch (SQLException e) {
1112: // is this due to a key constraint problem?... check each vendor's error codes
1113: boolean recordAlreadyExists = false;
1114: if ("hsqldb".equals(m_vendor)) {
1115: recordAlreadyExists = e.getErrorCode() == -104;
1116: } else if ("mysql".equals(m_vendor)) {
1117: recordAlreadyExists = e.getErrorCode() == 1062;
1118: } else if ("oracle".equals(m_vendor)) {
1119: recordAlreadyExists = e.getErrorCode() == 1;
1120: }
1121:
1122: if (m_showSql) {
1123: LOG.warn("Sql.dbWrite(): error code: "
1124: + e.getErrorCode() + " sql: " + sql
1125: + " binds: " + debugFields(fields) + " " + e);
1126: }
1127:
1128: // if asked to fail quietly, just return false if we find this error.
1129: if (recordAlreadyExists || failQuiet)
1130: return false;
1131:
1132: // perhaps due to a mysql deadlock?
1133: if (("mysql".equals(m_vendor))
1134: && (e.getErrorCode() == 1213)) {
1135: // just a little fuss
1136: LOG.warn("Sql.dbWrite(): deadlock: error code: "
1137: + e.getErrorCode() + " sql: " + sql
1138: + " binds: " + debugFields(fields) + " "
1139: + e.toString());
1140: throw new SqlServiceDeadlockException(e);
1141: }
1142:
1143: else if (recordAlreadyExists) {
1144: // just a little fuss
1145: LOG
1146: .warn("Sql.dbWrite(): unique violation: error code: "
1147: + e.getErrorCode()
1148: + " sql: "
1149: + sql
1150: + " binds: "
1151: + debugFields(fields)
1152: + " " + e.toString());
1153: throw new SqlServiceUniqueViolationException(e);
1154: } else {
1155: // something ELSE went wrong, so lest make a fuss
1156: LOG.warn("Sql.dbWrite(): error code: "
1157: + e.getErrorCode() + " sql: " + sql
1158: + " binds: " + debugFields(fields) + " ", e);
1159: throw new RuntimeException(
1160: "SqlService.dbWrite failure", e);
1161: }
1162: } catch (Exception e) {
1163: LOG.warn("Sql.dbWrite(): " + e);
1164: throw new RuntimeException("SqlService.dbWrite failure", e);
1165: } finally {
1166: try {
1167: if (null != pstmt)
1168: pstmt.close();
1169: if ((null != conn) && (callerConnection == null)) {
1170: // rollback on failure
1171: if (!success) {
1172: conn.rollback();
1173: }
1174:
1175: // if we changed the auto commit, reset here
1176: if (resetAutoCommit) {
1177: conn.setAutoCommit(autoCommit);
1178: }
1179: returnConnection(conn);
1180: }
1181: } catch (Exception e) {
1182: LOG.warn("Sql.dbWrite(): " + e);
1183: throw new RuntimeException(
1184: "SqlService.dbWrite failure", e);
1185: }
1186: }
1187:
1188: if (m_showSql)
1189: debug("Sql.dbWrite(): len: "
1190: + ((lastField != null) ? "" + lastField.length()
1191: : "null") + " time: " + connectionTime
1192: + " / " + (System.currentTimeMillis() - start),
1193: sql, fields);
1194:
1195: return true;
1196: }
1197:
1198: /**
1199: * Execute the "insert" sql, returning a possible auto-update field Long value
1200: *
1201: * @param sql
1202: * The sql statement.
1203: * @param fields
1204: * The array of fields for parameters.
1205: * @param callerConnection
1206: * The connection to use.
1207: * @param autoColumn
1208: * The name of the db column that will have auto-update - we will return the value used (leave null to disable this feature).
1209: * @return The auto-update value, or null
1210: */
1211: public Long dbInsert(Connection callerConnection, String sql,
1212: Object[] fields, String autoColumn) {
1213: return dbInsert(callerConnection, sql, fields, autoColumn,
1214: null, 0);
1215: }
1216:
1217: /**
1218: * Execute the "insert" sql, returning a possible auto-update field Long value
1219: *
1220: * @param sql
1221: * The sql statement.
1222: * @param fields
1223: * The array of fields for parameters.
1224: * @param callerConnection
1225: * The connection to use.
1226: * @param autoColumn
1227: * The name of the db column that will have auto-update - we will return the value used (leave null to disable this feature).
1228: * @param last
1229: * A stream to set as the last field.
1230: * @return The auto-update value, or null
1231: */
1232: public Long dbInsert(Connection callerConnection, String sql,
1233: Object[] fields, String autoColumn, InputStream last,
1234: int lastLength) {
1235: // check for a transaction conncetion
1236: if (callerConnection == null) {
1237: callerConnection = (Connection) threadLocalManager().get(
1238: TRANSACTION_CONNECTION);
1239: }
1240:
1241: if (LOG.isDebugEnabled()) {
1242: LOG.debug("dbInsert(String " + sql + ", Object[] " + fields
1243: + ", Connection " + callerConnection + ")");
1244: }
1245:
1246: // for DEBUG
1247: long start = 0;
1248: long connectionTime = 0;
1249:
1250: if (LOG.isDebugEnabled()) {
1251: String userId = usageSessionService().getSessionId();
1252: StringBuffer buf = new StringBuffer();
1253: if (fields != null) {
1254: buf.append(fields[0]);
1255: for (int i = 1; i < fields.length; i++) {
1256: buf.append(", ");
1257: buf.append(fields[i]);
1258: }
1259: }
1260: LOG.debug("Sql.dbInsert(): " + userId + "\n" + sql + "\n"
1261: + buf);
1262: }
1263:
1264: Connection conn = null;
1265: PreparedStatement pstmt = null;
1266: boolean autoCommit = false;
1267: boolean resetAutoCommit = false;
1268:
1269: boolean success = false;
1270: Long rv = null;
1271:
1272: try {
1273: if (callerConnection != null) {
1274: conn = callerConnection;
1275: } else {
1276: if (m_showSql)
1277: start = System.currentTimeMillis();
1278: conn = borrowConnection();
1279: if (m_showSql)
1280: connectionTime = System.currentTimeMillis() - start;
1281:
1282: // make sure we have do not have auto commit - will change and reset if needed
1283: autoCommit = conn.getAutoCommit();
1284: if (autoCommit) {
1285: conn.setAutoCommit(false);
1286: resetAutoCommit = true;
1287: }
1288: }
1289:
1290: if (m_showSql)
1291: start = System.currentTimeMillis();
1292:
1293: if (autoColumn != null) {
1294: String[] autoColumns = new String[1];
1295: autoColumns[0] = autoColumn;
1296: pstmt = conn.prepareStatement(sql, autoColumns);
1297: } else {
1298: pstmt = conn.prepareStatement(sql);
1299: }
1300:
1301: // put in all the fields
1302: int pos = prepareStatement(pstmt, fields);
1303:
1304: // and the last one
1305: if (last != null) {
1306: pstmt.setBinaryStream(pos, last, lastLength);
1307: }
1308:
1309: int result = pstmt.executeUpdate();
1310:
1311: ResultSet keys = pstmt.getGeneratedKeys();
1312: if (keys != null) {
1313: if (keys.next()) {
1314: rv = new Long(keys.getLong(1));
1315: }
1316: }
1317:
1318: // commit unless we are in a transaction (provided with a connection)
1319: if (callerConnection == null) {
1320: conn.commit();
1321: }
1322:
1323: // indicate success
1324: success = true;
1325: } catch (SQLException e) {
1326: // is this due to a key constraint problem... check each vendor's error codes
1327: boolean recordAlreadyExists = false;
1328: if ("hsqldb".equals(m_vendor)) {
1329: recordAlreadyExists = e.getErrorCode() == -104;
1330: } else if ("mysql".equals(m_vendor)) {
1331: recordAlreadyExists = e.getErrorCode() == 1062;
1332: } else if ("oracle".equals(m_vendor)) {
1333: recordAlreadyExists = e.getErrorCode() == 1;
1334: }
1335:
1336: if (m_showSql) {
1337: LOG.warn("Sql.dbInsert(): error code: "
1338: + e.getErrorCode() + " sql: " + sql
1339: + " binds: " + debugFields(fields) + " " + e);
1340: }
1341:
1342: if (recordAlreadyExists)
1343: return null;
1344:
1345: // perhaps due to a mysql deadlock?
1346: if (("mysql".equals(m_vendor))
1347: && (e.getErrorCode() == 1213)) {
1348: // just a little fuss
1349: LOG.warn("Sql.dbInsert(): deadlock: error code: "
1350: + e.getErrorCode() + " sql: " + sql
1351: + " binds: " + debugFields(fields) + " "
1352: + e.toString());
1353: throw new SqlServiceDeadlockException(e);
1354: }
1355:
1356: else if (recordAlreadyExists) {
1357: // just a little fuss
1358: LOG
1359: .warn("Sql.dbInsert(): unique violation: error code: "
1360: + e.getErrorCode()
1361: + " sql: "
1362: + sql
1363: + " binds: "
1364: + debugFields(fields)
1365: + " " + e.toString());
1366: throw new SqlServiceUniqueViolationException(e);
1367: }
1368:
1369: else {
1370: // something ELSE went wrong, so lest make a fuss
1371: LOG.warn("Sql.dbInsert(): error code: "
1372: + e.getErrorCode() + " sql: " + sql
1373: + " binds: " + debugFields(fields) + " ", e);
1374: throw new RuntimeException(
1375: "SqlService.dbInsert failure", e);
1376: }
1377: } catch (Exception e) {
1378: LOG.warn("Sql.dbInsert(): " + e);
1379: throw new RuntimeException("SqlService.dbInsert failure", e);
1380: } finally {
1381: try {
1382: if (null != pstmt)
1383: pstmt.close();
1384: if ((null != conn) && (callerConnection == null)) {
1385: // rollback on failure
1386: if (!success) {
1387: conn.rollback();
1388: }
1389:
1390: // if we changed the auto commit, reset here
1391: if (resetAutoCommit) {
1392: conn.setAutoCommit(autoCommit);
1393: }
1394: returnConnection(conn);
1395: }
1396: } catch (Exception e) {
1397: LOG.warn("Sql.dbInsert(): " + e);
1398: throw new RuntimeException(
1399: "SqlService.dbInsert failure", e);
1400: }
1401: }
1402:
1403: if (m_showSql)
1404: debug("Sql.dbWrite(): len: " + " time: " + connectionTime
1405: + " / " + (System.currentTimeMillis() - start),
1406: sql, fields);
1407:
1408: return rv;
1409: }
1410:
1411: /**
1412: * Read a single field BLOB from the db from one record, and update it's bytes with content.
1413: *
1414: * @param sql
1415: * The sql statement to select the BLOB.
1416: * @param content
1417: * The new bytes for the BLOB.
1418: */
1419: public void dbReadBlobAndUpdate(String sql, byte[] content) {
1420: // Note: does not support TRANSACTION_CONNECTION -ggolden
1421:
1422: if (LOG.isDebugEnabled()) {
1423: LOG.debug("dbReadBlobAndUpdate(String " + sql + ", byte[] "
1424: + content + ")");
1425: }
1426:
1427: if (!"oracle".equals(getVendor())) {
1428: throw new UnsupportedOperationException(
1429: "BasicSqlService.dbReadBlobAndUpdate() only works with an Oracle DB");
1430: }
1431:
1432: // for DEBUG
1433: long start = 0;
1434: long connectionTime = 0;
1435: int lenRead = 0;
1436:
1437: if (LOG.isDebugEnabled()) {
1438: String userId = usageSessionService().getSessionId();
1439: LOG.debug("Sql.dbReadBlobAndUpdate(): " + userId + "\n"
1440: + sql);
1441: }
1442:
1443: Connection conn = null;
1444: Statement stmt = null;
1445: ResultSet result = null;
1446: ResultSetMetaData meta = null;
1447: Object blob = null;
1448: OutputStream os = null;
1449:
1450: try {
1451: if (m_showSql)
1452: start = System.currentTimeMillis();
1453: conn = borrowConnection();
1454: if (m_showSql)
1455: connectionTime = System.currentTimeMillis() - start;
1456: if (m_showSql)
1457: start = System.currentTimeMillis();
1458: stmt = conn.createStatement();
1459: result = stmt.executeQuery(sql);
1460: if (result.next()) {
1461: blob = result.getBlob(1);
1462: }
1463: if (blob != null) {
1464: // %%% not supported? b.truncate(0);
1465: // int len = b.setBytes(0, content);
1466: try {
1467: // Use reflection to remove compile time dependency on oracle driver
1468: Class[] paramsClasses = new Class[0];
1469: Method getBinaryOutputStreamMethod = blob
1470: .getClass().getMethod(
1471: "getBinaryOutputStream",
1472: paramsClasses);
1473: Object[] params = new Object[0];
1474: os = (OutputStream) getBinaryOutputStreamMethod
1475: .invoke(blob, params);
1476: os.write(content);
1477: os.close();
1478: } catch (NoSuchMethodException ex) {
1479: LOG.warn("Oracle driver error: " + ex);
1480: } catch (IllegalAccessException ex) {
1481: LOG.warn("Oracle driver error: " + ex);
1482: } catch (InvocationTargetException ex) {
1483: LOG.warn("Oracle driver error: " + ex);
1484: }
1485: }
1486: } catch (Exception e) {
1487: LOG.warn("Sql.dbReadBlobAndUpdate(): " + e);
1488: } finally {
1489: try {
1490: if (null != result)
1491: result.close();
1492: if (null != stmt)
1493: stmt.close();
1494: if (null != conn) {
1495: // if we commit on read
1496: if (m_commitAfterRead) {
1497: conn.commit();
1498: }
1499:
1500: returnConnection(conn);
1501: }
1502: } catch (Exception e) {
1503: LOG.warn("Sql.dbRead(): " + e);
1504: }
1505: }
1506:
1507: if (m_showSql)
1508: debug("sql dbReadBlobAndUpdate: len: " + lenRead
1509: + " time: " + connectionTime + " / "
1510: + (System.currentTimeMillis() - start), sql, null);
1511: }
1512:
1513: /**
1514: * Read a single field from the db, from a single record, return the value found, and lock for update.
1515: *
1516: * @param sql
1517: * The sql statement.
1518: * @param field
1519: * A StringBuffer that will be filled with the field.
1520: * @return The Connection holding the lock.
1521: */
1522: public Connection dbReadLock(String sql, StringBuffer field) {
1523: // Note: does not support TRANSACTION_CONNECTION -ggolden
1524:
1525: if (LOG.isDebugEnabled()) {
1526: LOG.debug("dbReadLock(String " + sql + ", StringBuffer "
1527: + field + ")");
1528: }
1529:
1530: Connection conn = null;
1531: Statement stmt = null;
1532: ResultSet result = null;
1533: boolean autoCommit = false;
1534: boolean resetAutoCommit = false;
1535: boolean closeConn = false;
1536:
1537: try {
1538: // get a new conncetion
1539: conn = borrowConnection();
1540:
1541: // adjust to turn off auto commit - we need a transaction
1542: autoCommit = conn.getAutoCommit();
1543: if (autoCommit) {
1544: conn.setAutoCommit(false);
1545: resetAutoCommit = true;
1546: }
1547:
1548: if (LOG.isDebugEnabled())
1549: LOG.debug("Sql.dbReadLock():\n" + sql);
1550:
1551: // create a statement and execute
1552: stmt = conn.createStatement();
1553: result = stmt.executeQuery(sql);
1554:
1555: // if we have a result record
1556: if (result.next()) {
1557: // get the result and pack into the return buffer
1558: String rv = result.getString(1);
1559: if ((field != null) && (rv != null))
1560: field.append(rv);
1561: }
1562:
1563: // otherwise we fail
1564: else {
1565: closeConn = true;
1566: }
1567: }
1568:
1569: // this is likely the error when the record is otherwise locked - we fail
1570: catch (SQLException e) {
1571: // Note: ORA-00054 gives an e.getErrorCode() of 54, if anyone cares...
1572: // LOG.warn("Sql.dbUpdateLock(): " + e.getErrorCode() + " - " + e);
1573: closeConn = true;
1574: }
1575:
1576: catch (Exception e) {
1577: LOG.warn("Sql.dbReadLock(): " + e);
1578: closeConn = true;
1579: }
1580:
1581: finally {
1582: try {
1583: // close the result and statement
1584: if (null != result)
1585: result.close();
1586: if (null != stmt)
1587: stmt.close();
1588:
1589: // if we are failing, restore and release the connectoin
1590: if ((closeConn) && (conn != null)) {
1591: // just in case we got a lock
1592: conn.rollback();
1593: if (resetAutoCommit)
1594: conn.setAutoCommit(autoCommit);
1595: returnConnection(conn);
1596: conn = null;
1597: }
1598: } catch (Exception e) {
1599: LOG.warn("Sql.dbReadLock(): " + e);
1600: }
1601: }
1602:
1603: return conn;
1604: }
1605:
1606: /**
1607: * Commit the update that was locked on this connection.
1608: *
1609: * @param sql
1610: * The sql statement.
1611: * @param fields
1612: * The array of fields for parameters.
1613: * @param var
1614: * The value to bind to the last parameter in the sql statement.
1615: * @param conn
1616: * The database connection on which the lock was gained.
1617: */
1618: public void dbUpdateCommit(String sql, Object[] fields, String var,
1619: Connection conn) {
1620: // Note: does not support TRANSACTION_CONNECTION -ggolden
1621:
1622: if (LOG.isDebugEnabled()) {
1623: LOG.debug("dbUpdateCommit(String " + sql + ", Object[] "
1624: + fields + ", String " + var + ", Connection "
1625: + conn + ")");
1626: }
1627:
1628: PreparedStatement pstmt = null;
1629:
1630: try {
1631: if (LOG.isDebugEnabled())
1632: LOG.debug("Sql.dbUpdateCommit():\n" + sql);
1633:
1634: pstmt = conn.prepareStatement(sql);
1635:
1636: // put in all the fields
1637: int pos = prepareStatement(pstmt, fields);
1638:
1639: // prepare the update statement and fill with the last variable (if any)
1640: if (var != null) {
1641: if ("mysql".equals(m_vendor)) {
1642: // see http://bugs.sakaiproject.org/jira/browse/SAK-1737
1643: // MySQL setCharacterStream() is broken and truncates UTF-8
1644: // international characters sometimes. So use setBytes()
1645: // instead (just for MySQL).
1646: pstmt.setBytes(pos, var.getBytes("UTF-8"));
1647: pos++;
1648: } else {
1649: pstmt.setCharacterStream(pos,
1650: new StringReader(var), var.length());
1651: pos++;
1652: }
1653: }
1654:
1655: // run the SQL statement
1656: int result = pstmt.executeUpdate();
1657: pstmt.close();
1658: pstmt = null;
1659:
1660: // commit
1661: conn.commit();
1662: } catch (Exception e) {
1663: LOG.warn("Sql.dbUpdateCommit(): " + e);
1664: } finally {
1665: try {
1666: // close the statemenet and restore / release the connection
1667: if (null != pstmt)
1668: pstmt.close();
1669: if (null != conn) {
1670: // we don't really know what this should be, but we assume the default is not
1671: conn.setAutoCommit(false);
1672: returnConnection(conn);
1673: }
1674: } catch (Exception e) {
1675: LOG.warn("Sql.dbUpdateCommit(): " + e);
1676: }
1677: }
1678: }
1679:
1680: /**
1681: * Cancel the update that was locked on this connection.
1682: *
1683: * @param conn
1684: * The database connection on which the lock was gained.
1685: */
1686: public void dbCancel(Connection conn) {
1687: // Note: does not support TRANSACTION_CONNECTION -ggolden
1688:
1689: if (LOG.isDebugEnabled()) {
1690: LOG.debug("dbCancel(Connection " + conn + ")");
1691: }
1692:
1693: try {
1694: // cancel any changes, release any locks
1695: conn.rollback();
1696:
1697: // we don't really know what this should be, but we assume the default is not
1698: conn.setAutoCommit(false);
1699: returnConnection(conn);
1700: } catch (Exception e) {
1701: LOG.warn("Sql.dbCancel(): " + e);
1702: }
1703: }
1704:
1705: /**
1706: * {@inheritDoc}
1707: */
1708: public void ddl(ClassLoader loader, String resource) {
1709: if (LOG.isDebugEnabled()) {
1710: LOG.debug("ddl(ClassLoader " + loader + ", String "
1711: + resource + ")");
1712: }
1713:
1714: // add the vender string path, and extension
1715: resource = m_vendor + '/' + resource + ".sql";
1716:
1717: // find the resource from the loader
1718: InputStream in = loader.getResourceAsStream(resource);
1719: if (in == null) {
1720: LOG.warn("Sql.ddl: missing resource: " + resource);
1721: return;
1722: }
1723:
1724: try {
1725: BufferedReader r = new BufferedReader(
1726: new InputStreamReader(in));
1727: try {
1728: // read the first line, skipping any '--' comment lines
1729: boolean firstLine = true;
1730: StringBuffer buf = new StringBuffer();
1731: for (String line = r.readLine(); line != null; line = r
1732: .readLine()) {
1733: line = line.trim();
1734: if (line.startsWith("--"))
1735: continue;
1736: if (line.length() == 0)
1737: continue;
1738:
1739: // add the line to the buffer
1740: buf.append(' ');
1741: buf.append(line);
1742:
1743: // process if the line ends with a ';'
1744: boolean process = line.endsWith(";");
1745:
1746: if (!process)
1747: continue;
1748:
1749: // remove trailing ';'
1750: buf.setLength(buf.length() - 1);
1751:
1752: // run the first line as the test - if it fails, we are done
1753: if (firstLine) {
1754: firstLine = false;
1755: if (!dbWriteFailQuiet(null, buf.toString(),
1756: null)) {
1757: return;
1758: }
1759: }
1760:
1761: // run other lines, until done - any one can fail (we will report it)
1762: else {
1763: dbWrite(null, buf.toString(), null);
1764: }
1765:
1766: // clear the buffer for next
1767: buf.setLength(0);
1768: }
1769: } catch (IOException any) {
1770: LOG
1771: .warn("Sql.ddl: resource: " + resource + " : "
1772: + any);
1773: } finally {
1774: try {
1775: r.close();
1776: } catch (IOException any) {
1777: LOG.warn("Sql.ddl: resource: " + resource + " : "
1778: + any);
1779: }
1780: }
1781: } finally {
1782: try {
1783: in.close();
1784: } catch (IOException any) {
1785: LOG
1786: .warn("Sql.ddl: resource: " + resource + " : "
1787: + any);
1788: }
1789: }
1790: }
1791:
1792: /**
1793: * Prepare a prepared statement with fields.
1794: *
1795: * @param pstmt
1796: * The prepared statement to fill in.
1797: * @param fields
1798: * The Object array of values to fill in.
1799: * @return the next pos that was not filled in.
1800: * @throws UnsupportedEncodingException
1801: */
1802: protected int prepareStatement(PreparedStatement pstmt,
1803: Object[] fields) throws SQLException,
1804: UnsupportedEncodingException {
1805: if (LOG.isDebugEnabled()) {
1806: LOG.debug("prepareStatement(PreparedStatement " + pstmt
1807: + ", Object[] " + fields + ")");
1808: }
1809:
1810: // put in all the fields
1811: int pos = 1;
1812: if ((fields != null) && (fields.length > 0)) {
1813: for (int i = 0; i < fields.length; i++) {
1814: if (fields[i] == null
1815: || (fields[i] instanceof String && ((String) fields[i])
1816: .length() == 0)) {
1817: // treat a Java null as an SQL null,
1818: // and ALSO treat a zero-length Java string as an SQL null
1819: // This makes sure that Oracle vs MySQL use the same value
1820: // for null.
1821: pstmt.setObject(pos, null);
1822: pos++;
1823: } else if (fields[i] instanceof Time) {
1824: Time t = (Time) fields[i];
1825: if ("hsqldb".equals(getVendor())) {
1826: pstmt.setTimestamp(pos, new Timestamp(t
1827: .getTime()), null);
1828: } else {
1829: pstmt.setTimestamp(pos, new Timestamp(t
1830: .getTime()), m_cal);
1831: }
1832: pos++;
1833: } else if (fields[i] instanceof Long) {
1834: long l = ((Long) fields[i]).longValue();
1835: pstmt.setLong(pos, l);
1836: pos++;
1837: } else if (fields[i] instanceof Integer) {
1838: int n = ((Integer) fields[i]).intValue();
1839: pstmt.setInt(pos, n);
1840: pos++;
1841: } else if (fields[i] instanceof Float) {
1842: float f = ((Float) fields[i]).floatValue();
1843: pstmt.setFloat(pos, f);
1844: pos++;
1845: } else if (fields[i] instanceof Boolean) {
1846: pstmt.setBoolean(pos, ((Boolean) fields[i])
1847: .booleanValue());
1848: pos++;
1849: }
1850: // %%% support any other types specially?
1851: else {
1852: String value = fields[i].toString();
1853: if ("mysql".equals(m_vendor)) {
1854: // see http://bugs.sakaiproject.org/jira/browse/SAK-1737
1855: // MySQL setCharacterStream() is broken and truncates UTF-8
1856: // international characters sometimes. So use setBytes()
1857: // instead (just for MySQL).
1858: pstmt.setBytes(pos, value.getBytes("UTF-8"));
1859: pos++;
1860: } else {
1861: pstmt.setCharacterStream(pos, new StringReader(
1862: value), value.length());
1863: pos++;
1864: }
1865: }
1866: }
1867: }
1868:
1869: return pos;
1870: }
1871:
1872: /**
1873: * Append a message about this SQL statement to the DEBUG string in progress, if any
1874: *
1875: * @param str
1876: * The SQL statement.
1877: * @param fields
1878: * The bind fields.
1879: */
1880: protected void debug(String str, String sql, Object[] fields) {
1881: // no error will mess us up!
1882: try {
1883: // StringBuffer buf = (StringBuffer) CurrentService.getInThread("DEBUG");
1884: // if (buf == null) return;
1885: StringBuffer buf = new StringBuffer(2048);
1886:
1887: // skip some chatter
1888: // if (str.indexOf("SAKAI_CLUSTER") != -1) return;
1889: // if (str.indexOf("dual") != -1) return;
1890:
1891: // buf.append("\n\t");
1892: buf.append(str);
1893: buf.append(" binds: ");
1894: buf.append(debugFields(fields));
1895: buf.append(" sql: ");
1896: buf.append(sql);
1897:
1898: LOG.info(buf.toString());
1899: } catch (Throwable ignore) {
1900: if (LOG.isDebugEnabled()) {
1901: LOG.debug("Ignored Exception: " + ignore.getMessage(),
1902: ignore);
1903: }
1904: }
1905: }
1906:
1907: protected String debugFields(Object[] fields) {
1908: StringBuffer buf = new StringBuffer();
1909: if (fields != null) {
1910: for (int i = 0; i < fields.length; i++) {
1911: if (fields[i] != null) {
1912: buf.append(" ");
1913: buf.append(fields[i].toString());
1914: } else {
1915: buf.append(" null");
1916: }
1917: }
1918: }
1919: return buf.toString();
1920: }
1921:
1922: /**
1923: * <p>
1924: * StreamWithConnection is a cover over a stream that comes from a statmenet result in a connection, holding all these until closed.
1925: * </p>
1926: */
1927: public class StreamWithConnection extends InputStream {
1928: protected Connection m_conn = null;
1929:
1930: protected PreparedStatement m_pstmt = null;
1931:
1932: protected ResultSet m_result = null;
1933:
1934: protected InputStream m_stream;
1935:
1936: public StreamWithConnection(InputStream stream,
1937: ResultSet result, PreparedStatement pstmt,
1938: Connection conn) {
1939: if (SWC_LOG.isDebugEnabled()) {
1940: SWC_LOG.debug("new StreamWithConnection(InputStream "
1941: + stream + ", ResultSet " + result
1942: + ", PreparedStatement " + pstmt
1943: + ", Connection " + conn + ")");
1944: }
1945:
1946: m_conn = conn;
1947: m_result = result;
1948: m_pstmt = pstmt;
1949: m_stream = stream;
1950: }
1951:
1952: public void close() throws IOException {
1953: SWC_LOG.trace("close()");
1954:
1955: if (m_stream != null)
1956: m_stream.close();
1957: m_stream = null;
1958:
1959: try {
1960: if (null != m_result) {
1961: m_result.close();
1962: }
1963: m_result = null;
1964: } catch (SQLException any) {
1965: }
1966:
1967: try {
1968: if (null != m_pstmt) {
1969: m_pstmt.close();
1970: }
1971: m_pstmt = null;
1972: } catch (SQLException any) {
1973: }
1974:
1975: if (null != m_conn) {
1976: returnConnection(m_conn);
1977: m_conn = null;
1978: }
1979: }
1980:
1981: protected void finalize() {
1982: SWC_LOG.debug("finalize()");
1983:
1984: try {
1985: close();
1986: } catch (IOException any) {
1987: LOG.error(any.getMessage(), any);
1988: }
1989: }
1990:
1991: public int read() throws IOException {
1992: SWC_LOG.trace("read()");
1993:
1994: return m_stream.read();
1995: }
1996:
1997: public int read(byte b[]) throws IOException {
1998: if (SWC_LOG.isDebugEnabled()) {
1999: SWC_LOG.debug("read(byte " + b + ")");
2000: }
2001:
2002: return m_stream.read(b);
2003: }
2004:
2005: public int read(byte b[], int off, int len) throws IOException {
2006: if (SWC_LOG.isDebugEnabled()) {
2007: SWC_LOG.debug("read(byte " + b + ", int " + off
2008: + ", int " + len + ")");
2009: }
2010:
2011: return m_stream.read(b, off, len);
2012: }
2013:
2014: public long skip(long n) throws IOException {
2015: if (SWC_LOG.isDebugEnabled()) {
2016: SWC_LOG.debug("skip(long " + n + ")");
2017: }
2018:
2019: return m_stream.skip(n);
2020: }
2021:
2022: public int available() throws IOException {
2023: SWC_LOG.trace("available()");
2024:
2025: return m_stream.available();
2026: }
2027:
2028: public synchronized void mark(int readlimit) {
2029: if (SWC_LOG.isDebugEnabled()) {
2030: SWC_LOG.debug("mark(int " + readlimit + ")");
2031: }
2032:
2033: m_stream.mark(readlimit);
2034: }
2035:
2036: public synchronized void reset() throws IOException {
2037: SWC_LOG.trace("reset()");
2038:
2039: m_stream.reset();
2040: }
2041:
2042: public boolean markSupported() {
2043: SWC_LOG.trace("markSupported()");
2044:
2045: return m_stream.markSupported();
2046: }
2047: }
2048:
2049: /**
2050: * @param defaultDataSource
2051: * The defaultDataSource to set.
2052: */
2053: public void setDefaultDataSource(DataSource defaultDataSource) {
2054: if (LOG.isDebugEnabled()) {
2055: LOG.debug("setDefaultDataSource(DataSource "
2056: + defaultDataSource + ")");
2057: }
2058:
2059: this .defaultDataSource = defaultDataSource;
2060: }
2061:
2062: /**
2063: * @param slowDataSource
2064: * The slowDataSource to set.
2065: */
2066: public void setLongDataSource(DataSource slowDataSource) {
2067: if (LOG.isDebugEnabled()) {
2068: LOG.debug("setLongDataSource(DataSource " + slowDataSource
2069: + ")");
2070: }
2071:
2072: this .longDataSource = slowDataSource;
2073: }
2074:
2075: /**
2076: * {@inheritDoc}
2077: */
2078: public Long getNextSequence(String tableName, Connection conn) {
2079: if ("hsqldb".equals(m_vendor)) {
2080: String sql = "SELECT NEXT VALUE FOR " + tableName
2081: + " FROM DUAL"; // TODO: dual for hsql?
2082: return new Long((String) (dbRead(conn, sql, null, null)
2083: .get(0)));
2084: }
2085:
2086: if ("oracle".equals(m_vendor)) {
2087: String sql = "SELECT " + tableName + ".NEXTVAL FROM DUAL";
2088: return new Long((String) (dbRead(conn, sql, null, null)
2089: .get(0)));
2090: }
2091:
2092: return null;
2093: }
2094:
2095: /**
2096: * {@inheritDoc}
2097: */
2098: public String getBooleanConstant(boolean value) {
2099: if ("mysql".equals(m_vendor)) {
2100: return value ? "true" : "false";
2101: }
2102:
2103: return value ? "1" : "0";
2104: }
2105: }
|